Създаване на класации в Excel Експертът по таблици
По-бързо и по-високо:
Независимо дали става въпрос за спортни състезания или за определяне на най-добрия продавач в дадена компания, има много случаи на използване, при които трябва да се създаде класация или рейтингов списък.
Excel предлага проста и практична функция точно за тази цел, която бих искал да представя днес:
- ЕДИН РАНГ (от Excel 2010)
- РАНГ (до Excel 2007)
Определете класирането
Вече показах в няколко статии, че можете да използвате най-малката или най-голямата функция, за да изведете списък, сортиран по размер. В някои случаи обаче искате да знаете какъв е рангът на даден елемент във вашия списък.
Така че да предположим, че имате списък с продажби с много продавачи и сега искате да знаете кои са 5-те най-продавани. Тук влиза в действие функцията EQUAL RANK. (Забележка: в Excel 2007 вместо това има функцията RANK. И двете функции стигат до един и същ резултат в нашите примери)
Нашата примерна таблица изглежда така:
Преглед на продажбите от продавачи
Функцията EQUAL RANK се използва за определяне на съответния ранг на отделния продавач.
= ЕДИНЕН РЕЙТИНГ (номер; списък; [поръчка])
Ако въведете стойността 0 (нула) за параметъра "Поръчка" или я оставите, най-големият елемент в списъка получава ранг 1, като по този начин списъкът се сортира в низходящ ред:
Списък с класиране (версия 1)
Ако вместо това използваме стойността 1 като поръчка, рангът се определя във възходящ ред, т.е. най-малката стойност в списъка получава ранг 1:
Списък с класиране (версия 2)
Ако сега сортираме списъка си с продажби, става ясна друга особеност. Ако няколко стойности имат един и същ ранг, следващият ранг се пропуска за следващата стойност.
В нашия пример има два ранга 2 и два ранга 7, така че ранговете 3 и 8 не са определени:
По този начин напр. също така показват резултати от спортни състезания в обичайното класиране:
Така че нищо не пречи на следващото световно първенство по лека атлетика!
Може да се интересувате и от:
Регистрирайте се за безплатния бюлетин и никога не пропускайте поредния съвет на Excel!
P.S. Решението винаги е просто. Просто трябва да го намерите.
(Александър Солженицин)
P.P.S. Проблемът е най-вече пред компютъра.
Оставете коментар отмяна отговор
32 мисли за „създаване на класации в Excel“
Имам пет класации от пет ски състезания! Точки се присъждат за всяко ски състезание! Победителят получава 30 точки и след това пада надолу по една точка за ранг! От ранг 31 има нула точки
Сега искам да създам общата купа от тези пет индивидуални състезания
Това ли трябва да се направи?
Добър ден
Направих класиране в нашата мениджърска игра с десет играчи. Сега бих искал да дам точки на играчите, победителите 10 точки и губещите 1 точка.
Това възможно ли е?
да, това трябва да е осъществимо: просто създайте референтна таблица с две колони. Колона 1 = ранг, колона 2 = точки. И след това използвайте VLOOKUP в класацията, за да прочетете точките за съответния ранг от тази справочна таблица.
С Най-Добри Пожелания,
Мартин
Направих класиране в нашата мениджърска игра с десет играчи. Сега бих искал да разпределя точките на играчите в класацията, победителят 10 точки и последната 1 точка.
Може ли това да се направи с Excel?
Как се нарича формулата? Не съм точно изродът на Excel. Имам формулата за класацията, но какво друго влиза във формулата RANG, за да мога да определя точките?
ако точките действително трябва да се присъждат в низходящ ред на ранг, можете да направите това с втора формула RANK, т.е.без допълнителна референтна таблица и VLOOKUP. Може да изглежда така:
Формулата в колона C не прави нищо друго, освен да изчисли обратния ранг. И това ще ви даде точно броя точки. Може би това ще помогне.
С Най-Добри Пожелания,
Мартин
Единадесет от деня
8-ми
1
5
8-ми
5
8-ми
1
5
1
1
43
Формулата се вписва, ако разположението е от 1 до 10 без многократно поставяне.
В случай на множество разположения, общите 55 вече не са правилни. Къде е грешката?
Поздрави Майкъл
Сега не мога да следя стойностите от вашите списъци. Но за множество разположения решението с VLOOKUP и референтна таблица вероятно ще помогне:
С Най-Добри Пожелания,
Мартин
Единадесет за деня
1
7-ми
4-ти
1
4-ти
1
7-ми
4-ти
7-ми
7-ми
Единадесет точки от деня биха били правилни
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
Ако беше вярно, имах предвид множество разположения и съответните точки.
Поздрави Майкъл
Благодаря за бакшиша!
Но: Как мога да изведа местата 1-3 от списък с участници с ранга в колоната след името в друг раздел?
Така че имате класацията в таблица 1, например с имена в колона A2: A10 и ранг в колона C2: C10. И сега искаме да изведем имената на първите три реда в лист Таблица2.
Ако няма двойни рангове, можете да използвате комбинацията ИНДЕКС + СРАВНЕНИЕ:
= ИНДЕКС (таблица1! $ A $ 2: $ A $ 10; СРАВНЕНИЕ (1; таблица1! $ C $ 2: $ C $ 10; 0))
= ИНДЕКС (таблица1! $ A $ 2: $ A $ 10; СРАВНЕНИЕ (2; таблица1! $ C $ 2: $ C $ 10; 0))
= ИНДЕКС (Таблица1! $ A $ 2: $ A $ 10; СРАВНЕНИЕ (3; Таблица1! $ C $ 2: $ C $ 10; 0))
Ако ранговете се появят повече от веднъж, става по-сложно, тъй като за извеждане на всички имена се изискват матрични формули.
С Най-Добри Пожелания,
Мартин
Здравейте, имам следния въпрос относно примера на Световното първенство по лека атлетика: Класирането трябва да бъде в различните възрастови групи И разделено по пол. Имам колона C „пол“, колона E „възраст“ и колона K „общо точки“ за всички 300 деца. Последната колона N сега трябва да съдържа разположението през годината. Опитах IF, AND, VLOOKUP, EQUAL RANK и SUMPRODUCT ... Отказвам се ... Години:
Здравейте г-жо Tesch,
С Най-Добри Пожелания,
Мартин
Здравейте г-н Уайт,
много благодаря за вашата помощ.
Въведох следното:
= СУММЕН ПРОДУКТ (($ F $ 4: $ F $ 300 = F4) * ($ C $ 4: $ C $ 300 = C4) * ($ K $ 4: $ K $ 300
Сега го реших така:
= IF ((K4 = K4)))
Създадох таблица на шампионата за опростяване, както е описано, име, ранг, точки, където сме 14 пилоти и първият получава 28 точки, вторият 26 и т.н. Първото място с 28 и второто място с 26 точки е посочено, че и двете са на първо място, има решение ?
Не разбирам съвсем защо и двамата сте показани като номер 1, въпреки че има различни оценки. Как изглежда вашата формула?
С Най-Добри Пожелания,
Мартин
Здравейте г-н Уайт,
да аз съм проблемът пред компютъра. Малка идея за Excel.
Сигурен съм, че можете да ми помогнете (вероятно без много усилия).
Ще бъда домакин на турнир в Kniffel през март с около 20 участника. През годината се играят 10 кръга. Проблемът ми е, че игрите с един и същ резултат трябва да получават еднакъв ранг. По-точно: не играем срещу противник, а за най-голям брой точки на рунд. След това това трябва да бъде приложено в класирането.
И ако напр. Ако има четвърто място два пъти, тогава място 5 трябва да отпадне и след това продължава с място 6.
Въпросът ми: има ли примерни таблици за нещо подобно? Или: на какво трябва да обърна внимание при изграждането на маси ?
Ще бъда много благодарен за кратък и ясен отговор
С уважение
Райнер Мюнстерман
Здравейте г-н Мюнстерман,
всички ние понякога сме проблем пред компютъра 😉
Функцията EQUAL RANK, описана в статията, прави точно това, което искате: Ако ранг присъства два пъти, следващият ранг автоматично се пропуска. Можете да го видите и на екранните снимки.
С Най-Добри Пожелания,
Мартин
Здравейте г-н Уайт,
Извинете за късното благодаря ви от мен. След няколко неуспешни опита имате го
също работи с вашата помощ! Страхотно ... с малко улов:
Във вашата примерна снимка "сортиран списък с класиране" функцията Rank (0) е точно подходящата за мен. (Червени полета)
Означава също: 1x злато и 2x сребро. Добре Но функцията rank (1), която ми е необходима за разпределяне на рейтинговите точки, показва „златните точки“ за златото, но само „бронзовите точки“ се показват за среброто. (сини кутии)
Двамата подгласници трябва да получат 11 точки. Как мога да направя това?
Ако съм задал този въпрос твърде трудоемко, мога да се свържа и с вас по телефона (телефонен номер?). Ето пример за таблицата от Мач 3 от миналата година:
http://www.knoesel-kassel.de/2019/2019Matches1-4.htm
в които се присъждат 2 пъти по 8 точки и 2 пъти по 3 точки. Тогава всичко още се правеше на ръка.
Може би това е пожелано малко прекалено ...
С поздрави от Касел
Райнер Мюнстерман
Здравейте г-н Мюнстерман,
функцията не връща никакви точки, а класирането. За да мога да присвоя желаните точки за всеки ранг, бих поддържал отделна таблица с точки, която съдържа точките за присъждане за всеки ранг. След това можете да използвате функцията RANK, за да определите ранга както обикновено и след това да използвате VLOOKUP към ранга, за да добавите желаните точки от таблицата с точки.
С Най-Добри Пожелания,
Мартин
Благодаря за това. Имам подобен списък.
Въпреки това, ТОП 40 клиенти трябва да бъдат изплюти на продавач.
Някак си се отчайвам и не мога да намеря решение.
Може би някой може да ми помогне 🙂
Благодаря и най-добри пожелания Мела
в такъв случай не бих се борил с формули, а бих използвал обобщена таблица. Защото вече има вграден филтър "Топ 10", който разбира се може да бъде разширен и до Топ 40.
С Най-Добри Пожелания,
Мартин
С Най-Добри Пожелания,
Мартин
Благодаря ви за отговора. Всъщност го измислих вчера 😀
Сега обаче имам проблема, че имам нужда от най-добрите клиенти в списък за общо 28 продавачи. Не можах да разбера и сега направих 28 отделни листа, премахнах въртенето и сега трябваше да копирам всички данни от 28 листа в един лист.
Може би тук има и по-просто решение. Вероятно в бъдеще ще ми трябва по-често този вид списък.
Във всеки случай: БЛАГОДАРЯ за отговора 🙂
С Най-Добри Пожелания; Мела
важно е само да зададете филтъра за стойност в правилното поле, тогава той ще работи и в една обобщена таблица. Да предположим, че имате следните полета:
Продавач и клиент в областта на реда
Продажби в диапазона на стойността
След това задавате топ 10 филтъра в полето „Клиент“, след което той трябва да се побере.
С Най-Добри Пожелания,
Мартин
Просто не мога да продължа с проблема си.
Бихте искали да изброите всички членове на отбора, като играчът може да бъде в множество отбори.
Герд Екип 1 Екип 2 Екип 3 Екип 4
Hans Team 2 Team 3 Team 5 Team 4
Питър Екип 4 Екип 1 Екип 5 Екип 3
Стив Екип 4 Екип 2 Екип 1 Екип 5
Лео Екип 3 Екип 5 Екип 2 Екип 1
Име Име Име Име
Екип 1
Екип 2
Екип 3
Екип 4
Екип 5
Мога ли да покажа това с индекс и сравнение? Опитите ми досега са се провалили.
Опасявам се, че с INDEX и COMPARE това ще бъде много трудно с дадената структура на таблицата. Поне не се сещам за спонтанно решение. Вместо това бих преобразувал изходната таблица в плосък списък, използвайки Power Query, т.е. само две колони Name + Team. И тогава всички имена на всеки отбор могат да бъдат комбинирани с помощта на функция TEXT CHAIN.
Но може би друг читател ще има по-добра идея.
С Най-Добри Пожелания,
Мартин
Здравейте г-н Уайт,
Оценявам обиколките си в състезателна симулация и създадох класация на топ 25 писти. 1-во място е маршрутът с най-много пъти и т.н. Пример:
P1 Нюрбургринг 43 пъти
P2 Хокенхаймринг 41 пъти
и т.н.
Ако сега има няколко маршрута с еднакъв брой пъти, Google Docs ще покаже еднакво класиране за всички тях. Засега добре. Въпреки това, всеки път се показва едно и също име на маршрут.
Пример:
P17 Falkenberg 4 пъти
P17 Falkenberg 4 пъти
P17 Falkenberg 4 пъти
Разбира се има 3 различни маршрута, по които са били карани 4 пъти. От дълго време обмислям решение как да покажа по име останалите поставени маршрути.
Надявам се да не звучи като прекалено много глупости.
Това би била формулата, която определя името на маршрута:
= ИНДЕКС ($ A $ 4: $ A $ 136; СРАВНЕНИЕ (H22; $ B $ 4: $ B $ 136; 0))
Имате ли идея?
Много благодаря!
Йорг
Здравейте г-н Unkelbach,
може да има решение за формула в комбинация с LARGE, подобно на описаното в статията „По-добро от VLOOKUP“. Но повечето от тях са сложни и трудни за разбиране. В такива случаи винаги препоръчвам вместо това да погледнете обобщена таблица. Такива неща обикновено са много по-лесни за картографиране там и без сложни формули.
Но не мога да преценя какво е възможно в Google Docs.
С Най-Добри Пожелания,
Мартин
Здравей Мартин, благодаря ти за съветите. Създадох няколко пивота с точки и съответните редици на работен лист (2 колони). Винаги, когато актуализирам данните, рангът изчезва в колоната Rank на pivot - вместо това основният резултат се появява отново. Вмъкнах това като колона в ос и след това го показах в низходящ ред с настройката на полето за стойност и функцията за ранг. Тъй като има 8 пивота на един лист, ще трябва да ги задам отново след всяка актуализация. Имате ли представа как мога да предотвратя това?
това поведение ме дразни много, защото досега никога не съм виждал настройките на полето за стойност да се нулират при актуализиране на обобщената таблица. Следователно аз съм малко объркан ...
С коя версия на Excel работите?
С Най-Добри Пожелания,
Мартин
Следвайте ненатрапчиво.
Кой пише тук?
Здравейте, казвам се Мартин Вайс и всичко е свързано с любимата ми тема: Microsoft Excel!
Типичната реакция на много хора: завъртете очи, поклатете невярващо глава, усмихнете се състрадателно и понякога почти учудени.
Искам да премахна целия страх или уважение на тези хора към Excel.
Моят девиз: Excel не хапе!
Ако сте нов тук, прочетете тук »
Електронна книга: Условно форматиране
Електронна книга: Въведение в Power Query
Сводни таблици на Excel за манекени®
Книгата за начинаещи и професионалисти:
Ако искате да разберете обобщените таблици от нулата и да ги използвате оптимално, попаднали сте на правилното място. Научете повече…
Електронна книга: VLOOKUP & Co.
Електронна книга: падащи списъци
Безплатни съвети
Никога повече да не пропускате съвет от Excel? След това се абонирайте за моя безплатен бюлетин пощенски списък!