Creați liste de clasare în Excel Expertul în tabel

Mai repede mai sus În continuare:
Indiferent dacă este vorba de competiții sportive sau de determinarea celui mai bun vânzător dintr-o companie, există multe cazuri de utilizare în care trebuie creat un clasament sau o listă de clasare.

Excel oferă o funcție simplă și practică exact în acest scop, pe care aș dori să o prezint astăzi:

RANK EGAL (din Excel 2010)
RANK (până la Excel 2007)

Determinați clasamentul

Am arătat deja în mai multe articole că puteți utiliza funcțiile SMALLEST sau LARGE pentru a afișa o listă sortată după dimensiune. Cu toate acestea, în unele cazuri, doriți să știți care este clasamentul unui anumit articol pe lista dvs.

Deci, să presupunem că aveți o listă de vânzări cu mulți vânzători și acum doriți să știți cine sunt primii 5 vânzători. Aici intră în joc funcția EQUAL RANK. (Notă: în Excel 2007 există în schimb funcția RANK. Ambele funcții au același rezultat în exemplele noastre)

Tabelul nostru de exemplu arată astfel:

Prezentare generală a vânzărilor de către vânzători

Funcția EQUAL RANK este utilizată pentru a determina gradul respectiv al vânzătorului individual.

= CLASIFICARE EGALĂ (număr; listă; [comandă])

Dacă introduceți valoarea 0 (zero) pentru parametrul „Comandă” sau o lăsați în afara, cel mai mare element din listă primește rangul 1, lista fiind astfel sortată în ordine descrescătoare:

Lista de clasare (versiunea 1)

Dacă folosim în schimb valoarea 1 ca ordine, rangul este determinat în ordine crescătoare, adică cea mai mică valoare din listă obține rangul 1:

Lista de clasare (versiunea 2)

Dacă acum sortăm lista noastră de vânzări, o altă particularitate devine clară. Dacă mai multe valori au același rang, următorul rang este omis pentru următoarea valoare.

În exemplul nostru, există două rang 2 și două rang 7, deci rangurile 3 și 8 nu sunt atribuite:

În acest fel, de ex. arată, de asemenea, rezultatele competițiilor sportive în clasamentul obișnuit:

Nimic nu stă în calea următoarelor Campionate Mondiale de Atletism!

De asemenea poti fi interesat de:

Înscrieți-vă la newsletter-ul gratuit și nu ratați niciodată un alt sfat Excel!

P.S. Soluția este întotdeauna simplă. Trebuie doar să o găsești.
(Alexandru Soljenitin)

P.P.S. Problema este mai ales în fața computerului.

Lasă un comentariu anulează răspunsul

32 de gânduri despre „crearea de clasamente în Excel”

Am cinci clasamente din cinci curse de schi! Se acordă puncte pentru fiecare cursă de schi! Câștigătorul primește 30 de puncte și apoi coboară un punct pe rang! Din rangul 31 sunt zero puncte

Acum vreau să creez cupa generală din aceste cinci curse individuale

Asta trebuie făcut?

O zi buna
Am făcut un clasament în jocul nostru de manager cu zece jucători. Acum aș dori să atribui puncte jucătorilor, câștigătorilor 10 puncte și învinșilor 1 punct.
Este fezabil?

da, ar trebui să fie fezabil: creați doar un tabel de referință cu două coloane. Coloana 1 = rang, coloana 2 = puncte. Și apoi utilizați VLOOKUP în lista de clasare pentru a citi punctele pentru rangul respectiv din acest tabel de referință.

Toate cele bune,
Martin

Am făcut un clasament în jocul nostru de manager cu zece jucători. Acum aș dori să aloc punctele jucătorilor pe lista clasamentului, câștigătorul cu 10 puncte ultimul 1 punct.
Se poate face asta cu Excel?

Cum se numește formula? Nu sunt tocmai ciudatul Excel. Am formula pentru lista de clasare, dar ce altceva intră în formula RANG, astfel încât să pot atribui punctele?

dacă punctele trebuie de fapt acordate în ordinea descrescătoare a rangului, puteți face acest lucru cu o a doua formulă RANK, adică fără un tabel de referință suplimentar și VLOOKUP. Ar putea arăta astfel:

Formula din coloana C nu face altceva decât să calculeze rangul invers. Și asta ți-ar oferi exact numărul de puncte. Poate că asta te va ajuta.

Toate cele bune,
Martin

Unsprezece din zi
A 8-a
1
5
A 8-a
5
A 8-a
1
5
1
1
43
Formula se potrivește dacă plasarea este de la 1 la 10 fără plasare multiplă.

În cazul mai multor destinații de plasare, totalul de 55 nu mai este corect. Unde este greșeala?

Salutări Michael

Nu pot să urmez valorile din listele dvs. acum. Dar pentru mai multe destinații de plasare, soluția cu VLOOKUP și un tabel de referință va ajuta probabil:

Toate cele bune,
Martin

Plasarea unsprezece din zi
1
Al 7-lea
Al 4-lea
1
Al 4-lea
1
Al 7-lea
Al 4-lea
Al 7-lea
Al 7-lea
Unsprezece dintre punctele zilei ar fi corecte
8 9 10 + 9 + 8 = 27/3 = 9
1 2,5 4 + 3 + 2 + 1 = 10/4 = 2,5
5 6 7 + 6 + 5 = 18/3 = 6
8 9
5 6
8 9
1 2,5
5 6
1 2,5
1 2,5
43

Dacă era corect, mă refeream la mai multe destinații de plasare și la punctele corespunzătoare.

Salutări Michael

Multumesc pentru sfat!
Dar: Cum pot afișa locurile 1-3 dintr-o listă de participanți cu rangul din coloană după numele dintr-o altă filă?

Deci, aveți lista de clasare în tabelul 1, de exemplu, cu nume în coloana A2: A10 și rang în coloana C2: C10. Și acum doriți să scoateți numele primelor trei rânduri în foaia Tabelul 2.
Dacă nu există ranguri duble, puteți utiliza combinația INDEX + COMPARAȚIE:
= INDEX (tabel1! $ A $ 2: $ A $ 10; COMPARAȚI (1; tabel1! $ C $ 2: $ C $ 10; 0))
= INDEX (tabel1! $ A $ 2: $ A $ 10; COMPARE (2; tabel1! $ C $ 2: $ C $ 10; 0))
= INDEX (tabel1! $ A $ 2: $ A $ 10; COMPARAȚI (3; tabel1! $ C $ 2: $ C $ 10; 0))

Dacă rangurile apar de mai multe ori, devine mai complicat deoarece sunt necesare formule matrice pentru a afișa toate numele.

Toate cele bune,
Martin

Bună ziua, am următoarea întrebare despre exemplul Campionatelor Mondiale de atletism: Clasamentul ar trebui să se încadreze în diferitele grupe de vârstă ȘI separate prin sex. Am o coloană C „sex”, o coloană E „vârstă” și o coloană K „puncte totale” pentru toți cei 300 de copii. Ultima coloană N ar trebui să conțină acum destinația de plasare în cursul anului. Am încercat IF, AND, VLOOKUP, RANK.EQUAL și SUMPRODUCT ... renunț ... Ani:

Bună, doamnă Tesch,

Toate cele bune,
Martin

Bună ziua domnule White,
multe multumiri pentru ajutorul tau.
Am introdus următoarele:
= SUM PRODUS (($ F $ 4: $ F $ 300 = F4) * ($ C $ 4: $ C $ 300 = C4) * ($ K $ 4: $ K $ 300

Am rezolvat-o astfel:
= IF ((K4 = K4)))

Am creat un tabel de campionat pentru simracing așa cum este descris, nume, rang, puncte în care suntem 14 piloți și primul primește 28 de puncte, al doilea 26 etc. descrescător. Primul loc cu 28 și locul doi cu 26 de puncte indică faptul că ambele sunt pe primul loc, există o soluție ?

Nu prea înțeleg de ce amândoi sunteți afișați ca numărul 1, deși există scoruri diferite. Cum arată formula ta?

Toate cele bune,
Martin

Bună ziua domnule White,
da, eu sunt problema din fața computerului. Mică idee de Excel.
Sunt sigur că mă puteți ajuta (probabil fără eforturi mari).
Voi găzdui un turneu Kniffel în martie cu aproximativ 20 de participanți. 10 runde se joacă pe parcursul anului. Problema mea este că jocurile cu același rezultat ar trebui să aibă același rang. Mai exact: nu jucăm împotriva unui adversar, ci pentru cel mai mare număr de puncte pe rundă. Acest lucru ar trebui apoi implementat în clasament.
Și dacă de ex. Dacă există un al patrulea loc de două ori, atunci locul 5 trebuie abandonat și apoi continuă cu locul 6.
Întrebarea mea: există tabele eșantion pentru așa ceva? Sau: la ce trebuie să fiu atent când construiesc mese ?
Aș fi foarte recunoscător pentru un răspuns scurt și clar
În ceea ce privește
Rainer Münstermann

Bună ziua domnule Münstermann,

cu toții suntem uneori problema în fața computerului 😉
Funcția EQUAL RANK descrisă în articol face exact ceea ce doriți: Dacă un rang este prezent de două ori, următorul rang este omis automat. O puteți vedea și în capturile de ecran.

Toate cele bune,
Martin

Bună ziua domnule White,
Îmi pare rău pentru târziu mulțumesc de la mine. După câteva încercări nereușite, îl aveți
a lucrat și cu ajutorul tău! Super ... cu puțină captură:
În imaginea dvs. de exemplu „listă de clasare sortată”, funcția rang (0) este exact cea potrivită pentru mine. (Casetă roșie)
De asemenea, înseamnă: 1x aur și 2x argint. O.K Dar funcția rang (1), de care am nevoie pentru alocarea punctelor de rating, arată „punctele de aur” pentru aur, dar numai „punctele de bronz” sunt afișate pentru argint. (cutii albastre)
Cei doi subcampioni ar trebui să obțină 11 puncte. Cum pot face asta?
Dacă am pus această întrebare prea laborios, vă pot contacta și prin telefon (număr de telefon?). Iată un exemplu de tabel din meciul 3 de anul trecut:
http://www.knoesel-kassel.de/2019/2019Matches1-4.htm
în care se acordă de 2 ori 8 puncte și de 2 ori 3 puncte. Pe atunci totul era încă făcut manual.
Poate că asta și-a dorit puțin prea mult ...
Cu privire la Kassel
Rainer Münstermann

Bună ziua domnule Münstermann,

funcția nu returnează niciun punct, ci clasamentul. Pentru a putea atribui punctele dorite pentru fiecare rang, aș menține un tabel de puncte separat care conține punctele care trebuie acordate pentru fiecare rang. Apoi puteți utiliza funcția RANK pentru a determina rangul ca de obicei și apoi utilizați un VLOOKUP la rang pentru a adăuga punctele dorite din tabelul de puncte.

Toate cele bune,
Martin

Mulțumesc pentru că. Am o listă similară.

Cu toate acestea, TOP 40 de clienți ar trebui să fie scuipați pe vânzător.

Cumva disper și nu pot veni cu o soluție.

Poate mă poate ajuta cineva 🙂

Mulțumesc și salutări Mela

într-un astfel de caz nu m-aș lupta cu formulele, ci aș folosi un tabel pivot. Pentru că există deja un filtru „Top 10” încorporat, care desigur poate fi extins și la Top 40.

Toate cele bune,
Martin

Vă mulțumesc pentru răspunsul dumneavoastră. De fapt, am venit cu asta ieri 😀

Cu toate acestea, acum am problema că am nevoie de clienții de top dintr-o listă pentru un total de 28 de vânzători. Nu am putut afla asta și acum am făcut 28 de coli individuale, am scos din nou pivotul și acum am fost nevoit să copiez toate datele din cele 28 de coli într-o singură coală.

Poate că există o soluție mai simplă aici. Probabil că voi avea nevoie de acest tip de listă mai des în viitor.

În orice caz: MULȚUMESC pentru răspunsul 🙂

Toate cele bune; Mela

contează doar că setați filtrul de valori în câmpul corect, apoi va funcționa și într-un singur tabel pivot. Să presupunem că aveți următoarele câmpuri:

Vânzător și client în zona rândurilor
Vânzări în intervalul valoric

Apoi setați top 10 filtru pe câmpul „Client”, apoi ar trebui să se potrivească.

Toate cele bune,
Martin

M-am blocat de problema mea.
Aș dori să listez toți membrii unei echipe, cu un jucător permis să fie în mai multe echipe.

Gerd Echipa 1 Echipa 2 Echipa 3 Echipa 4
Hans Echipa 2 Echipa 3 Echipa 5 Echipa 4
Peter Echipa 4 Echipa 1 Echipa 5 Echipa 3
Steve Echipa 4 Echipa 2 Echipa 1 Echipa 5
Leo Echipa 3 Echipa 5 Echipa 2 Echipa 1

Nume Nume Nume Nume
Echipa 1
Echipa 2
Echipa 3
Echipa 4
Echipa 5

Pot arăta acest lucru cu un index și o comparație? Încercările mele de până acum au eșuat.

Mă tem că cu INDEX și COMPARE acest lucru va fi foarte dificil cu structura de tabel dată. Cel puțin nu mă pot gândi la o soluție spontană. În schimb, aș folosi Power Query pentru a transforma tabelul de ieșire într-o listă plană, adică doar două coloane Nume + Echipă. Și apoi toate numele fiecărei echipe ar putea fi combinate folosind o funcție TEXT CHAIN.

Dar poate un alt cititor va avea o idee mai bună.

Toate cele bune,
Martin

Bună ziua domnule White,
Îmi evaluez timpul de tur într-o simulare de curse și am creat o listă de clasare a primelor 25 de piste. Locul 1 este traseul cu cel mai mare timp condus etc. Exemplu:
P1 Nürburgring de 43 de ori
P2 Hockenheimring de 41 de ori
etc.

Dacă acum există mai multe rute cu același număr de ori, Google Docs va afișa același clasament pentru toate. Până acum ok. Cu toate acestea, același nume de traseu este afișat de fiecare dată.
Exemplu:
P17 Falkenberg de 4 ori
P17 Falkenberg de 4 ori
P17 Falkenberg de 4 ori

Desigur, există 3 rute diferite pe care au fost conduse de 4 ori. Mă gândesc la o soluție de mult timp cu privire la modul în care pot afișa celelalte rute plasate după nume.
Sper că nu sună prea mult.

Aceasta ar fi formula care determină numele rutei:
= INDEX ($ A $ 4: $ A $ 136; COMPARAȚI (H22; $ B $ 4: $ B $ 136; 0))

Ai o idee?

Mulțumesc mult!
Jörg

Bună ziua domnule Unkelbach,

poate exista o soluție de formulă în combinație cu LARGE, similară cu cea descrisă în articolul Better Than VLOOKUP. Dar majoritatea sunt complexe și greu de înțeles. În astfel de cazuri, recomand întotdeauna să vă uitați la un tabel pivot. Astfel de lucruri sunt de obicei mult mai ușor de cartografiat acolo și fără formule complexe.
Dar nu pot judeca ce este posibil în Google Docs.

Toate cele bune,
Martin

Bună Martin, vă mulțumesc pentru sfaturi. Am creat mai multe pivote cu puncte și rangurile corespunzătoare pe o foaie de lucru (2 coloane). Ori de câte ori actualizez datele, rangul dispare în coloana de rang a pivotului - în schimb, scorul subiacent reapare. Am inserat acest lucru în pivot ca o coloană și apoi l-am afișat în ordine descrescătoare cu setarea câmpului de valoare și funcția de rang. Deoarece există 8 pivoturi pe o singură foaie, ar trebui să le reglez din nou și din nou după fiecare actualizare. Ai idee cum pot preveni asta?

acest comportament mă irită foarte mult, deoarece până acum nu am văzut niciodată setările câmpului valoric resetându-se când tabelul pivot este actualizat. Prin urmare, eu sunt un pic perplex ...
Cu ce versiune de Excel lucrați?

Toate cele bune,
Martin

Urmați discret.

Cine scrie aici?

Bună ziua, numele meu este Martin Weiß și aici este vorba despre subiectul meu preferat: Microsoft Excel!

Reacția tipică a multor oameni: da ochii peste cap, clătină din cap neîncrezător, zâmbește compătimitor și uneori aproape uimit.
Vreau să îndepărtez toată frica sau respectul acestor persoane pentru Excel.

Motto-ul meu: Excel nu mușcă!
Dacă sunteți nou aici, citiți aici »

E-Book: Formatare condiționată

E-book: Introducere în Power Query

Tabelele pivot Excel pentru manechine®

Cartea pentru începători și profesioniști:

Dacă doriți să înțelegeți tabelele pivot de la bază și să le utilizați în mod optim, ați ajuns la locul potrivit. Aflați mai multe…

E-Book: VLOOKUP & Co.

E-book: liste derulante

Sfaturi gratuite

Nu ratați niciodată un sfat Excel din nou? Apoi, abonați-vă la lista mea de e-mail gratuită!