Створення рейтингових списків у Excel Експерт таблиць
Швидше Вище:
Незалежно від того, йдеться про спортивні змагання чи про визначення найкращого продавця в компанії, існує багато випадків використання, в яких потрібно створювати рейтинг або рейтинговий список.
Саме для цього Excel пропонує просту і практичну функцію, яку я хотів би представити сьогодні:
- РІВНИЙ РАНГ (з Excel 2010)
- RANK (до 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, РІВНИЙ РЕЙТИНГ та SUMPRODUCT ... Я відмовляюсь ... Роки:
Привіт місіс Теш,
З найкращими побажаннями,
Мартін
Привіт містер Вайт,
велике спасибі за допомогу.
Я ввів таке:
= СУМСКИЙ ПРОДУКТ (($ F $ 4: $ F $ 300 = F4) * ($ C $ 4: $ C $ 300 = C4) * ($ K $ 4: $ K $ 300
Зараз я вирішив це так:
= IF ((K4 = K4)))
Я створив таблицю чемпіонатів для симуляції, як описано, ім'я, ранг, очки, де ми 14 водії, і перший отримує 28 очок, другий 26 тощо за спаданням. Перше місце з 28 і друге місце з 26 балами вказується на те, що обидва знаходяться на першому місці, є рішення ?
Я не зовсім розумію, чому вас обох показують як номер 1, хоча бали різні. Як виглядає ваша формула?
З найкращими побажаннями,
Мартін
Привіт містер Вайт,
так, я проблема перед комп'ютером. Маленька ідея Excel.
Я впевнений, що ви можете мені допомогти (мабуть, без особливих зусиль).
У березні я прийму турнір у Найфлі, де братимуть участь близько 20 осіб. Протягом року зіграно 10 турів. Моя проблема полягає в тому, що ігри з однаковим результатом повинні отримувати однаковий рейтинг. Точніше: ми граємо не проти суперника, а за найбільшу кількість очок за раунд. Потім це слід застосувати у рейтингу.
І якщо, наприклад, два рази є четверте місце, то місце 5 потрібно відмовитись, і воно продовжується з місця 6.
Моє запитання: чи є зразки таблиць для чогось подібного? Або: на що я повинен звертати увагу, будуючи столи ?
Буду дуже вдячний за коротку і чітку відповідь
З повагою
Райнер Мюнстерманн
Привіт, пане Мюнстерманн,
ми всі іноді стоїмо перед комп’ютером 😉
Функція EQUAL RANK, описана в статті, виконує саме те, що ви хочете: Якщо ранг присутній двічі, наступний ранг автоматично опускається. Ви також можете побачити це на скріншотах.
З найкращими побажаннями,
Мартін
Привіт містер Вайт,
Вибачте за пізнє спасибі від мене. Після кількох невдалих спроб, у вас є
також працював з вашою допомогою! Чудово ... з невеликим уловом:
На вашому прикладі малюнка "відсортований рейтинговий список" функція rank (0) є саме правильною для мене. (Червоне поле)
Також означає: 1x золото та 2x срібло. Гаразд Але функція рангу (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
Ганс Команда 2 Команда 3 Команда 5 Команда 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-е місце - маршрут із найбільшим часом проїзду тощо. Приклад:
Р1 Нюрбургрінг 43 рази
Р2 Хоккенхаймрінг 41 раз
тощо.
Якщо є кілька маршрутів з однаковою кількістю разів, Google Docs покаже однаковий рейтинг для всіх них. Поки що добре. Однак щоразу відображається одна і та ж назва маршруту.
Приклад:
P17 Фалькенберг 4 рази
P17 Фалькенберг 4 рази
P17 Фалькенберг 4 рази
Звичайно, є 3 різні маршрути, де 4 рази їздили. Я довгий час розмірковував над рішенням щодо способу відображення інших розміщених маршрутів за іменами.
Я сподіваюся, це не схоже на занадто багато дурниць.
Це буде формула, яка визначає назву маршруту:
= ІНДЕКС ($ A $ 4: $ A $ 136; ПОРІВНЯТЬ (H22; $ B $ 4: $ B $ 136; 0))
У вас є ідея?
Дуже дякую!
Йорг
Привіт пане Ункельбах,
може бути рішення для формул у поєднанні з LARGE, подібне до того, що описано у статті “Краще, ніж VLOOKUP”. Але більшість із них є складними і важкими для розуміння. У таких випадках я завжди рекомендую переглядати зведену таблицю. Такі речі зазвичай набагато легше там нанести на карту і без складних формул.
Але я не можу судити, що можливо в Документах Google.
З найкращими побажаннями,
Мартін
Привіт Мартіне, дякую за поради. Я створив кілька стержнів з точками та відповідними рангами на аркуші (2 стовпці). Кожного разу, коли я оновлюю дані, рейтинг зникає в стовпці Rank в зведенні - натомість основний бал знову з’являється. Я вставив це як стовпець у зведеність, а потім відобразив у порядку зменшення із налаштуванням поля значення та функцією ранжування. Оскільки на одному аркуші є 8 стержнів, мені доведеться встановлювати їх знову після кожного оновлення. Ви уявляєте, як я можу запобігти цьому?
ця поведінка мене сильно дратує, бо до цього часу я ніколи не бачив, щоб налаштування поля значень скидалися при оновленні зведеної таблиці. Тому я трохи збентежений ...
З якою версією Excel ви працюєте?
З найкращими побажаннями,
Мартін
Слідкуйте ненав’язливо.
Хто тут пише?
Привіт, мене звати Мартін Вайс, і це все про мою улюблену тему: Microsoft Excel!
Типова реакція багатьох людей: закрутіть очима, невірно похитайте головою, співчутливо посміхніться, а іноді майже здивуйте.
Я хочу позбавити усіх цих людей страху чи поваги до Excel.
Мій девіз: Excel не кусається!
Якщо ви тут новачок, читайте далі »
Електронна книга: умовне форматування
Електронна книга: Вступ до Power Query
Зведені таблиці Excel для чайників®
Книга для початківців та професіоналів:
Якщо ви хочете зрозуміти зведені таблиці з нуля та оптимально їх використовувати, ви потрапили в потрібне місце. Вивчайте більше…
Електронна книга: VLOOKUP & Co.
Електронна книга: випадаючі списки
Безкоштовні поради
Ніколи більше не пропустити підказку Excel? Тоді підпишіться на мій безкоштовний список розсилки бюлетенів!