Preskoči na glavno vsebino

Kako razvrstiti vrednosti po skupinah v Excelu?

V tem članku bom govoril o razvrščanju vrednosti po skupinah v Excelu, kot je prikazano spodaj.
doc uvrstitev po skupini 1

Razvrsti vrednosti po skupinah


puščica modri desni mehurček Razvrsti vrednosti po skupinah

Obstaja formula za hitro razvrščanje vrednosti glede na skupino.

Izberite prazno celico poleg podatkov, na primer C2, vnesite to formulo, =SUMPRODUCT(($A$2:$A$11=A2)*(B2<$B$2:$B$11))+1 nato povlecite ročico za samodejno izpolnjevanje navzdol, da to formulo uporabite za celice, ki jih potrebujete.

V formuli je A2: A11 obseg, ki vsebuje vrednosti skupin, A2 je prva celica na seznamu skupinskih vrednosti, B2 je prva celica na seznamu, ki je bila potrebna za uvrstitev, B2: B11 je seznam, ki vsebuje potrebne vrednosti uvrstiti.
doc uvrstitev po skupini 2

Najboljša pisarniška orodja za produktivnost

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

zavihek kte 201905


Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Comments (12)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How to get unique ranks for Ex: Group Record RankA 100 1
If the same record repeat again I want rank 2 instead of 1.
This comment was minimized by the moderator on the site
Hi Sunny, I will be so grateful for suggestions. I conducted a survey of stakeholders for our nonprofit. Our dataset of 53 responses now includes (1)type of stakeholder WHICH CAN INCLUDE MORE THAN ONE TYPE (ex: volunteer and donor); (2) rank order of top 3 skills (from a list) you feel are needed for your job; (3) rank order top 3 skills young people need today (from a list that is similar to but not exactly the same as the one used in the prior question). The format was to select from drop down list.Is it possible to analyze to see if Donors (for example) as a group prioritize similarly?
Thanks very much!
This comment was minimized by the moderator on the site
Thanks for this solution! Is there a alteration that would allow me to omit blanks from the ranking?
This comment was minimized by the moderator on the site
Hi, Nick, you can use the Filter feature in Excel to filter out blank cells firstly, and hide them or remove them, then use above formula to rank.
This comment was minimized by the moderator on the site
hi sunny, i have a complex query here but need to visualize it to better understanding. how can i contact u with visualization?v
This comment was minimized by the moderator on the site
How can I get it to rank in reverse order, from smallest to largest?
This comment was minimized by the moderator on the site
Hi, Brian Q, you can use this formula =SUMPRODUCT(($A$2:$A$11=A2)*(B2<$B$2:$B$11))+1 to ranke from smallest to largest
This comment was minimized by the moderator on the site
I'm sorry, but I don't see a difference between this formula and the one above.
This comment was minimized by the moderator on the site
B2> ; just reverse from less than to greater than.
This comment was minimized by the moderator on the site
This is EXCEL-lent (see what I did there?)! Now that you have the rankings by group, how do you find out % from #1 rank within the group? For example, Group A has record 100 as #1, and record 93 as #3 ranked. If I wanted to add another column to find out how far off in percentage record #3 (93) is from record #1 (100), what is the formula to achieve this? The result should be rank #1 is 0%, and rank #3 is 7% off ((100/93)-1) from rank #1
This comment was minimized by the moderator on the site
Devide the subject by the max of the group using a max if.

Like this:
=C2/MAXIFS(C:C,B:B,B2)
This comment was minimized by the moderator on the site
Sorry I have not found a formula or other solution can handle ur question.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations