Перейти до основного матеріалу

Як знайти 5 або n найчастіших текстів або чисел у Excel?

Для більшості з нас звичайним завданням є отримання тексту зі списку комірок, який найчастіше зустрічається. У певних випадках вам також може знадобитися знайти та витягнути 3 або n найпоширеніших текстів зі списку, як показано на знімку екрана нижче. Щоб вирішити цю проблему, у цій статті ми розповімо вам про кілька хитрощів.


Знайдіть і витягніть 3 або n найчастіших текстів або чисел із формулами

Ви можете виконати це завдання, використовуючи наведені нижче формули, виконайте наступне:

Формула 1: звичайна формула + формула масиву

1. Застосуйте наведену нижче формулу, щоб отримати значення, яке з’являється найчастіше:

=IFERROR(INDEX($A$2:$A$18,MODE(MATCH($A$2:$A$18,$A$2:$A$18,0)+{0,0})),"")

примітки:

1. У цій формулі A2: A18 це список даних, з якого потрібно знайти значення.

2. Якщо ви використовуєте версії Excel, які передують Microsoft 365, вам слід натиснути Ctrl + Shift + Enter щоб отримати результат. Для користувачів Microsoft 365 і новіших версій Excel можна просто ввести формулу в клітинку результату, а потім натиснути Що натомість? Створіть віртуальну версію себе у ключ

2. Потім ви повинні використати наступну формулу масиву, щоб отримати друге за частотою значення, введіть цю формулу під першою клітинкою формули, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати правильний результат, див. знімок екрана:

=IFERROR(INDEX(A$2:A$18,MODE(IF(COUNTIF(C$2:C2,A$2:A$18)=0,MATCH(A$2:A$18,A$2:A$18,0)+{0,0}))),"")
примітки: У цій формулі, A2: A18 це список даних, з якого ви хочете знайти значення, C2 це комірка формули найчастішого значення, яке ви щойно вилучили.

3. Вилучивши друге за частотою значення, перетягніть цю комірку формули вниз, щоб отримати інші n значень, які вам потрібні. Перегляньте скріншот:

4. Нарешті, ви можете використати таку формулу, щоб отримати кількість входжень тексту, що витягується, див. знімок екрана:

=COUNTIF($A$2:$A$18, C2)


Формула 2: формула масиву

Фактично, ви також можете використовувати одну формулу масиву, щоб отримати результат, виконайте наступне:

1. Введіть або скопіюйте наведену нижче формулу в порожню клітинку, де ви хочете отримати результат, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати перший результат, див. скріншот:

=INDEX($A$2:$A$18,MATCH(MIN(MODE(IF(NOT(COUNTIF(C$1:C1,$A$2:$A$18)),(COUNTIF($A$2:$A$18,"<"& $A$2:$A$18)+1)*{1,1}))),COUNTIF($A$2:$A$18,"<"& $A$2:$A$18)+{1},0))
примітки: У формулі, A2: A18 це список даних, з якого ви хочете знайти значення, C1 це клітинка вище вашої формули.

2. А потім перетягніть цю формулу вниз, щоб отримати інші значення. Якщо ви хочете отримати 3 найпоширеніші значення, вам просто потрібно перетягнути вниз до інших двох клітинок, див. знімок екрана:

3. Крім того, ви також можете отримати кількість випадків вилучення тексту за допомогою наведеної нижче формули:

=COUNTIF($A$2:$A$18, C2)


Знайдіть і витягніть 3 або n найпоширеніших текстів або чисел за допомогою зведеної таблиці

Якщо ви знайомі зі зведеною таблицею, ви можете швидко отримати таблицю, яка впорядковує дані від найбільш часто зустрічаються до найменш часто зустрічаються. Виконайте наведені нижче дії.

1. Виберіть список даних, який потрібно використовувати, а потім клацніть Insert > PivotTable, див. скріншот:

2. В вискочив Створіть зведену таблицю у діалоговому вікні виберіть місце, куди потрібно розмістити зведену таблицю Виберіть, де потрібно розмістити звіт зведеної таблиці розділ, див. знімок екрана:

3. Потім натисніть кнопку OK кнопку, a Поля зведеної таблиці відображається панель. Перетягніть назву стовпця в ряди та Цінності області окремо, і ви отримаєте зведену таблицю, як показано на знімку екрана нижче:

4. Клацніть будь-яку комірку в стовпці чисел, потім клацніть правою кнопкою миші та виберіть сортувати > Сортувати від найбільшого до найменшого, див. скріншот:

5. А потім кількість значень у зведеній таблиці сортується від найбільшого до найменшого, і ви можете відразу побачити 3 найпоширеніші тексти, див. знімок екрана:


Більше відносних статей:

  • Знайдіть спільні значення в 3 колонках
  • Як правило, вам може знадобитися знайти і вибрати однакові значення між двома стовпцями в Excel, але чи намагалися ви коли-небудь знаходити загальні значення серед трьох стовпців, що означає, що значення існують у 3 стовпцях одночасно, як показано на наступному скріншоті . У цій статті я познайомлюсь із деякими методами для завершення цього завдання в Excel.
  • Знайдіть найчастіший текст із критеріями
  • У цій статті буде розказано про те, як знайти найбільш поширений текст на основі конкретних критеріїв із ряду комірок у Excel. Одночасно я також представлю формулу для вилучення тексту, що найчастіше зустрічається, у стовпці.
  • Сортування даних за найбільш частим значенням
  • Припустимо, у вас є довгий список даних на вашому аркуші, і тепер ви хотіли б відсортувати цей список за частотою зустрічальності кожного слова. Тобто спочатку перелічується найпоширеніше значення (наприклад, зустрічається чотири рази у стовпці), а потім слідують слова, які трапляються тричі, двічі та один раз, як показано на наступних скріншотах. Як ви могли вирішити це завдання в Excel?
  • Знайдіть найменше або найбільш поширене/часте значення
  • Під час роботи над книгою Excel іноді може знадобитися знайти найменше загальне або часте значення у стовпці або рядку аркуша. За винятком знаходження його по одному вручну, існує формула, яка поєднує функції Index, Match, Min і Countif, щоб швидко повернути найменш часто зустрічається значення.

Найкращі інструменти продуктивності офісу

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями    Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул    Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок    Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу.  Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...

Опис


Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Comments (2)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
this is horrible
This comment was minimized by the moderator on the site
Thank you for this. I have been struggling to make a formula to identify the top 5 zipcodes and this formula has been the only one that worked. Would you have any thoughts on adding multiple search criteria for this formula? I thought adding & to the match would do the trick, however adding this is returning 0 values despite what the data represents. Any suggestestions would be welcomed. Thank you for your time.

=IFERROR(INDEX(MonthlyData[Zip],MODE(MATCH(MonthlyData[Zip]&$D$3&$G$3&$C15&$R$5,MonthlyData[Zip]&MonthlyData[Group_Name]&MonthlyData[Report Date]&MonthlyData[Sub]&MonthlyData[R],0)+{0,0})),"")
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations