Note: The other languages of the website are Google-translated. Back to English

Як витягти унікальні значення на основі критеріїв у Excel?

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

Витяг унікальних значень на основі критеріїв за допомогою формули масиву

Витяг унікальних значень на основі кількох критеріїв за допомогою формули масиву

Витягніть унікальні значення зі списку комірок з корисною функцією

 

Витяг унікальних значень на основі критеріїв за допомогою формули масиву

Щоб вирішити цю роботу, ви можете застосувати складну формулу масиву, будь-ласка, зробіть наступне:

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

=IFERROR(INDEX($B$2:$B$15, MATCH(0, IF($D$2=$A$2:$A$15, COUNTIF($E$1:$E1, $B$2:$B$15), ""), 0)),"")

2. Потім перетягніть маркер заповнення вниз до комірок, поки не відображаються порожні комірки, і тепер усі унікальні значення на основі конкретного критерію перераховані, див. Знімок екрана:

Примітка: У наведеній вище формулі: B2: B15 - діапазон стовпців містить унікальні значення, з яких ви хочете витягти, A2: A15 - стовпець містить критерій, на основі якого ви бачите, D2 вказує критерій, за яким потрібно перерахувати унікальні значення на основі, та E1 - комірка над введеною формулою.

Витяг унікальних значень на основі кількох критеріїв за допомогою формули масиву

Якщо ви хочете витягти унікальні значення на основі двох умов, ось ще одна формула масиву може зробити вам послугу, будь ласка, зробіть так:

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

=IFERROR(INDEX($C$2:$C$15,MATCH(0,COUNTIF(G1:$G$1,$C$2:$C$15)+IF($A$2:$A$15<>$E$2,1,0)+IF($B$2:$B$15<>$F$2,1,0),0)),"")

2. Потім перетягніть маркер заповнення вниз до комірок, доки не відображатимуться порожні комірки, і тепер всі унікальні значення на основі конкретних двох умов будуть перераховані, див. Знімок екрана:

Примітка: У наведеній вище формулі: C2: C15 - діапазон стовпців містить унікальні значення, з яких ви хочете витягти, A2: A15 і E2 - це перший діапазон із критеріями, на основі яких ви хочете витягти унікальні значення, B2: B15 і F2 є другим діапазоном із критеріями, за якими ви хочете витягти унікальні значення, та G1 - комірка над введеною формулою.

Витягніть унікальні значення зі списку комірок з корисною функцією

Іноді вам просто потрібно витягти унікальні значення зі списку комірок, тут я порекомендую корисний інструмент-Kutools для Excel, З його Витяг клітинок з унікальними значеннями (включає перший дублікат) утиліта, ви можете швидко витягти унікальні значення.

Примітка:Щоб застосувати це Витяг клітинок з унікальними значеннями (включає перший дублікат), по-перше, вам слід завантажити Kutools для Excel, а потім швидко та легко застосувати функцію.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Клацніть клітинку, куди потрібно вивести результат. (примітки: Не клацайте клітинку в першому рядку.)

2. Потім натисніть Кутулс > Помічник формули > Помічник формули, див. скріншот:

3, в Помічник формул діалоговому вікні, виконайте такі дії:

  • вибрати текст опція від Formula тип випадаючий список;
  • Тоді виберіть Витяг клітинок з унікальними значеннями (включає перший дублікат) від Виберіть фромулу вікно списку;
  • У правій Введення аргументів розділ, виберіть список комірок, для яких потрібно витягти унікальні значення.

4. Потім натисніть Ok , перший результат відображається в комірці, потім виділіть комірку і перетягніть маркер заповнення до комірок, для яких потрібно перерахувати всі унікальні значення, поки не відображаються порожні комірки, див. знімок екрана:

Безкоштовно завантажте Kutools для Excel зараз!


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

  • Підрахуйте кількість унікальних та чітких значень зі списку
  • Припустимо, у вас довгий список значень з деякими повторюваними елементами, тепер ви хочете підрахувати кількість унікальних значень (значення, які відображаються у списку лише один раз) або різних значень (усі різні значення у списку, це означає унікальні значення + 1-е повторюване значення) у стовпці, як показано на екрані ліворуч. У цій статті я розповім про те, як боротися з цією роботою в Excel.
  • Сума унікальних значень на основі критеріїв у Excel
  • Наприклад, у мене є діапазон даних, який містить стовпці Ім'я та Порядок, тепер для підсумовування лише унікальних значень у стовпці Замовлення на основі стовпця Ім'я, як показано на наступному знімку екрана. Як швидко та легко вирішити це завдання в Excel?
  • Об'єднати унікальні значення в Excel
  • Якщо у мене є довгий список значень, які заповнюються деякими повторюваними даними, зараз я хочу знайти лише унікальні значення, а потім об’єднати їх в одну клітинку. Як я міг швидко та легко вирішити цю проблему в Excel?

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

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2021 і 365. Підтримує всі мови. Легке розгортання на вашому підприємстві чи в організації. 30-денна безкоштовна пробна версія повних функцій. 60-денна гарантія повернення грошей.
вкладка kte 201905

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

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (40)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Привіт, дякую за цей підручник, він працює ідеально. Я намагаюся змінити його, щоб він працював з умовою АБО, але, здається, він не працює - чи це можливо? наприклад =ІНДЕКС($B$2:$B$17, СПІПНИК(0, ЯКЩО(АБО($D$2=$A$2:$A$17,$D$2=$B$2:$B$17), COUNTIF($E$1) :$E1, $B$2:$B$17), ""), 0))
Цей коментар був мінімізований модератором на сайті
Дякую за цей підручник! Я також намагаюся змінити формулу, як у наведеному вище коментаторі, але з умовою І, щоб вона відповідала іншим умовним критеріям (наприклад, у цьому прикладі я хотів би бачити лише речі вище певного порогу). Чи можете ви порадити? Дякую!
Цей коментар був мінімізований модератором на сайті
Привіт. Один із способів зробити це: замініть формулу if на sumproduct((condition1=rng1)+(condition2=rng2))*countif(... Це спрацювало для мене. Удачі! Замінивши + на *, ви можете зробіть це умовою АБО, але ретельно доглядайте за дужками!
Цей коментар був мінімізований модератором на сайті
Дякую, це чудово!
Цей коментар був мінімізований модератором на сайті
Дякую за це. Я спробував це і, здається, працює добре з перервами. Проблема, яка постійно повторюється, полягає в тому, що іноді повертається лише перше відповідне значення, а потім дублюється, коли я перетягую вниз, щоб повернути всі відповідні значення. Як запобігти цьому? Будь-які пропозиції?
Цей коментар був мінімізований модератором на сайті
Це працює дуже добре, але щоразу, коли введене значення дублюється, воно розміщує значення лише один раз. Наприклад, якщо у вашому списку є дві Люсі, він переносить до нової таблиці лише одну Люсі. Чи є спосіб виправити це?
Цей коментар був мінімізований модератором на сайті
Використовуючи цю формулу, вона постійно повторює перше значення, як зробити це зупинкою та надати список значень, що дорівнює добутку в D2?
Цей коментар був мінімізований модератором на сайті
Привіт! Щоб перше значення не повторювалося під час перетягування вниз, ви повинні COUNTIF клітинку НАД клітинкою, в яку ви розміщуєте формулу. Наприклад, якщо формула переходить у E2, ви повинні ввести countif($E$1:$E1...
Цей коментар був мінімізований модератором на сайті
Привіт Райан. Формули чудово працюють, однак при перетягуванні вниз перше значення постійно повторюється. Я переконався, що COUNTIF посилається на комірку НАД коміркою з формулою, але все одно повторює перше значення при перетягуванні вниз? (наприклад, якщо формула масиву знаходиться в C2, тоді COUNTIF вказує на клітинку $C$1:$C$1)
Цей коментар був мінімізований модератором на сайті
Привіт Райан. Формули чудово працюють, однак при перетягуванні вниз перше значення постійно повторюється. Я переконався, що COUNTIF посилається на комірку НАД коміркою з формулою, але все одно повторює перше значення при перетягуванні вниз? (наприклад, якщо формула масиву знаходиться в C2, тоді COUNTIF вказує на клітинку $C$1:$C$1)
Цей коментар був мінімізований модератором на сайті
Можливо, не працює, тому що ви заблокували клітинки - спробуйте замінити $C$1:$C$1 на $C$1:$C1
Цей коментар був мінімізований модератором на сайті
це було дуже корисно, але я постійно отримую подвійні всі імена, як це:
Доу, Джейн
Доу, Джейн
Гувер, Том
Гувер, Том

Як я можу зупинити це?
Цей коментар був мінімізований модератором на сайті
Привіт, я отримую помилку "#N/A" у "Функції відповідності", підкажіть, будь ласка?
Цей коментар був мінімізований модератором на сайті
=IF(AL2="AP","AP",IF(AK2="AD","AD",IF(Z2>500000,"Yes","No"))) Я хочу, щоб "всі умови" були задоволені сказати так... Excel відображає помилку в цій формулі... порадьте, будь ласка
Цей коментар був мінімізований модератором на сайті
насправді я хочу, щоб клітинка відображала "ТАК", якщо (AL2="AP" і AK2="AD" і Z2>500000)
Цей коментар був мінімізований модератором на сайті
Я отримую помилку #N/A у функції зіставлення з цією формулою. Чи можете ви допомогти?
Цей коментар був мінімізований модератором на сайті
Привіт, я отримую помилку "#N/A" у "Функції відповідності", підкажіть, будь ласка?
Цей коментар був мінімізований модератором на сайті
Якщо ви отримуєте помилку #N/A, перейдіть до своєї формули та використовуйте Control + Shift + Enter замість Enter.
Цей коментар був мінімізований модератором на сайті
Я отримую 0 замість очікуваних результатів, формула чудово працює для даних на одному аркуші, чи є у вас якесь рішення для даних на іншому аркуші?

це моя формула

=IFERROR(INDEX('Switching Data'!$B$7:$B$204,MATCH(0,IF($A$2='Switching Data'!$A$7:$A$204,COUNTIF($A$4:A4,'Switching Data'!$B$7:$B$204),""),0)),0)
Цей коментар був мінімізований модератором на сайті
Привіт, Гон,
Після вставки формули потрібно натиснути разом клавіші Ctrl + Shift + Enter, а не тільки клавішу Enter.
Будь ласка, спробуйте, дякую!
Цей коментар був мінімізований модератором на сайті
Привіт, Гон, сподіваюся, що ти добре. Цікаво, чи зможете ви вирішити цю проблему. Я отримую ту саму помилку, коли формула надходить з іншого аркуша. Я буду вдячний поділитися рішенням, якщо ви його отримали.
Цей коментар був мінімізований модератором на сайті
Спасибо!
Цей коментар був мінімізований модератором на сайті
Як я можу змусити цю формулу повертати кожен із дублікатів замість одного з кожного з імен? Наприклад, у наведеному вище прикладі, як я можу змусити стовпець результатів (B:B) повернути Люсі, Рубі, Енні, Хосе, Люсі, Енні, Том? Я використовую це як бюджетний інструмент для отримання конкретних підсумків облікового запису з головної книги. Однак деякі суми та описи операцій є дублікатами в головній книзі. Після того, як перше з дубльованих значень буде витягнуто, більше з них не буде витягнуто.
Цей коментар був мінімізований модератором на сайті
Привіт, Джо,
Щоб отримати всі відповідні значення на основі певних критеріїв клітинки, вам може допомогти наступна формула масиву, див. знімок екрана:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

Після вставки формули натисніть разом клавіші Shift + Ctrl + Enter, щоб отримати правильний результат, а потім перетягніть маркер заповнення вниз, щоб отримати всі значення.
Сподіваюся, це може допомогти вам, дякую!
Цей коментар був мінімізований модератором на сайті
Все йде нормально. Я можу скопіювати результати в тестовий аркуш, внести зміни в масив, а потім виправити формулу, щоб врахувати внесені мною зміни. Сьогодні я планую перемістити це в основний аркуш і подивитися, як це працює. Дякую за допомогу!
Цей коментар був мінімізований модератором на сайті
Добре, значить, це працює в головній робочій книжці. Є один виняток, причину якого я не зміг визначити: якщо масив (у моєму випадку головна книга, яка в мене починалася в рядку 3) не починається в рядку 1, повернуті значення є неправильними. Що викликає цю проблему і який термін у формулі виправляє її? Ще раз дякую за допомогу в цьому!
Цей коментар був мінімізований модератором на сайті
Останнє запитання: якщо я хочу, щоб стовпець результатів повертав усі значення, не пов’язані з KTE або KTO (тому D:D буде Tom, Nocol, Lily, Angelina, Genna), як це зробити?
Цей коментар був мінімізований модератором на сайті
Для мене формула не працює. Я натискаю ctrl shift enter і все одно отримую помилку N/A. Я хотів би додати, що я підготував точно ті ж дані, що й у підручнику. У чому причина, що він не працює?
Цей коментар був мінімізований модератором на сайті
Це чудово спрацювало для мене з певним значенням пошуку. Однак, якби я хотів використовувати символ підстановки для пошуку часткових значень, як би я це зробив? Наприклад, якщо я хочу знайти всі імена, пов’язані з KT?

Я використовую цю функцію для пошуку клітинок, які містять кілька текстів. Наприклад, якби кожен продукт також мав допоміжний продукт у тій самій клітинці, але я шукав лише назви, пов’язані з підпродуктом «ельф».

КТЕ - ельф
КТЕ- м'яч
КТЕ - фортепіано
КТО - ельф
КТО- м'яч
КТО - фортепіано
Цей коментар був мінімізований модератором на сайті
Чи є спосіб, щоб це працювало, ДОЗВОЛЯючи для повторюваних значень? Наприклад, я хочу, щоб усі екземпляри Люсі були перераховані в результатах.
Цей коментар був мінімізований модератором на сайті
Привіт, Костянтин,
Щоб отримати всі відповідні значення, включаючи дублікати, на основі певних критеріїв клітинки, вам може допомогти наступна формула масиву, див. знімок екрана:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

Після вставки формули натисніть разом клавіші Shift + Ctrl + Enter, щоб отримати правильний результат, а потім перетягніть маркер заповнення вниз, щоб отримати всі значення.
Сподіваюся, це може допомогти вам, дякую!
There are no comments posted here yet
Load More
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL