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

Як витягти унікальні значення з декількох стовпців у Excel?

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


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

Ось формула масиву також може допомогти вам витягти унікальні значення з декількох стовпців.

1. Припускаючи ваші значення в діапазоні А2: С9, введіть наступну формулу в клітинку E2:

=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""

примітки: У наведеній вище формулі, A2: C9 вказує діапазон комірок, для яких потрібно витягти унікальні значення, E1: E1 - це перша комірка стовпця, куди потрібно помістити результат, $ 2: $ 9 підставки для рядків містять клітинки, які ви хочете використовувати, і $ A: $ C вказує, що стовпці містять клітинки, які ви хочете використовувати. Будь ласка, змініть їх на власні.

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


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

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

1. Спочатку вставте один новий порожній стовпець ліворуч від ваших даних, у цьому прикладі я вставлю стовпець A поруч із вихідними даними.

2. Клацніть одну клітинку у ваших даних і натисніть Alt + D клавіші, потім натисніть P негайно відкрити Майстер зведеної таблиці та зведеної діаграмивиберіть Кілька діапазонів консолідації у майстрі step1 див. скріншот:

3. Потім натисніть Далі кнопку, перевірити Створіть для мене одне поле сторінки параметр у майстрі step2, див. скріншот:

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

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

6. Нарешті клацніть обробка для завершення роботи майстра, а на поточному аркуші створена зведена таблиця, а потім зніміть усі поля з Виберіть поля, які потрібно додати до звіту розділ, див. знімок екрана:

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


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

За допомогою наступного коду VBA ви також можете витягти унікальні значення з декількох стовпців.

1. Утримуйте клавішу ALT + F11 і відкриває Вікно Microsoft Visual Basic для програм.

2. Клацання Insert > Модуліта вставте наступний код у вікно модуля.

VBA: Витяг унікальних значень із кількох стовпців

Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
    If rng.Value <> "" Then
        dt(rng.Value) = ""
    End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub

3. Потім натисніть F5 для запуску цього коду, і з’явиться вікно із запитом, щоб нагадати вам про вибір діапазону даних, який ви хочете використовувати. Дивіться знімок екрана:

4. А потім клацніть OK, з’явиться інше підказне вікно, яке дозволить вам вибрати місце для розміщення результату, див. знімок екрана:

5. Натисніть OK щоб закрити це діалогове вікно, і всі унікальні значення були витягнуті відразу.


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

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

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

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

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

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

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

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

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

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


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

  • Підрахуйте кількість унікальних та чітких значень зі списку
  • Припустимо, у вас довгий список значень з деякими повторюваними елементами, тепер ви хочете підрахувати кількість унікальних значень (значення, які відображаються у списку лише один раз) або різних значень (усі різні значення у списку, це означає унікальні значення + 1-е повторюване значення) у стовпці, як показано на екрані ліворуч. У цій статті я розповім про те, як боротися з цією роботою в Excel.
  • Витяг унікальних значень на основі критеріїв у Excel
  • Припустимо, у вас є такий діапазон даних, для якого ви хочете перерахувати лише унікальні імена стовпця B на основі конкретного критерію стовпця A, щоб отримати результат, як показано нижче. Як ви могли швидко та легко впоратися з цим завданням у 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% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (31)
Оцінено 5 з 5 · рейтинги 1
Цей коментар був мінімізований модератором на сайті
Is this formula complete? =INDIRECT(TEXT(MIN(IF(($A$2:$C$9"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&"",
Цей коментар був мінімізований модератором на сайті
Це ще не виправлено :sad:
Цей коментар був мінімізований модератором на сайті
яка марна трата часу..... формула НЕ працює
Цей коментар був мінімізований модератором на сайті
Дякую!!! Я витрачав години, намагаючись це зробити і з’ясувати, що сталося з Pivot Wizard (інша стаття).
Цей коментар був мінімізований модератором на сайті
Я використовую ваш код VBA, але не хочу, щоб вікно з’являлося. Натомість я хочу точно визначити, який діапазон комірок використовувати кожен раз і в яке саме поле помістити вихідні дані. Діапазон введення та вихід будуть на двох різних аркушах. як мені оновити VBA для цього? Дякую!!
Цей коментар був мінімізований модератором на сайті
Гей! Хтось знає, чому ця формула, здається, призводить до помилки після рядка 87? Мовляв, він працює ідеально, а потім у певний момент просто повертає мені помилки для кожного рядка.. що є найгіршим! Тому що я так близько до того, що мені потрібно тут...
Цей коментар був мінімізований модератором на сайті
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&"" It doesn't work
Цей коментар був мінімізований модератором на сайті
привіт, я хочу витягти унікальні клітинки з першого стовпця, коли я порівнюю його з іншими стовпцями (у мене три нерівні стовпці), як я можу це зробити?
Цей коментар був мінімізований модератором на сайті
привіт, у мене є три нерівні стовпці, і я хочу витягти унікальні клітинки першого стовпця. як я можу це зробити?? Спасибі заздалегідь
Цей коментар був мінімізований модератором на сайті
я кохаю

Витягніть унікальні значення з кількох стовпців за допомогою зведеної таблиці
Цей коментар був мінімізований модератором на сайті
Чи можете ви ввести правильну формулу... функція VBA працює нормально.
Просто для свого проекту я скоріше використовую правильну формулу.


Дякую
Цей коментар був мінімізований модератором на сайті
хтось знає, для виведення, як зробити його в кілька рядків, а не в один рядок? (наразі результат одного рядка досягається worksheetfunction.transpose, але я хочу досягти (як результат) того, що при виборі для 3 стовпців повертається результат також 3 стовпці, замість одного
Цей коментар був мінімізований модератором на сайті
Ця формула масиву ПРАВИЛЬНА. Дані в стовпцях від A до C, перша формула результату в клітинці D2... Ця формула відрізняється від інших формул масиву тим, що пізнішою є копіювання формули вниз і Ctrl+Shift+Введіть всю формулу. Однак цю формулу масиву слід виконати за допомогою Ctrl+Shift+Enter у першій клітинці та скопіювати вниз.
Цей коментар був мінімізований модератором на сайті
Muchas gracias por la macro!!! мені fue muy util
Цей коментар був мінімізований модератором на сайті
я налаштувався на свій аркуш, але повертаю лише перше значення у визначеному масиві... чого мені не вистачає?
Цей коментар був мінімізований модератором на сайті
Привіт, Коді,
Наведена вище формула добре працює в моєму робочому аркуші, чи не могли б ви надати тут знімок екрана вашої проблеми з даними?
Дякую!
Цей коментар був мінімізований модератором на сайті
Що стосується версії формули, не могли б ви пояснити докладніше, що робить ця частина? *100+COLUMN($A:$C),7^8)),"R0C00") Зокрема, які є * 100, 7 ^ 8, і "R0C000" робити? Все інше я розумію, але не можу зрозуміти, для чого це.
Цей коментар був мінімізований модератором на сайті
Трохи пізно відповів, але...
ROW($2:$9)*100 - це множення номера рядка *100, отже, якщо це в рядку 5, тепер число 500
COLUMN($A:$C) – це число додається до рядка*100, отже, якщо це рядок 5, стовпець 2, то число 502.
7^8)), - це (я думаю) має мати максимальне значення для оператора min від раніше.
"R0C00") - форматує текст на основі числа. У прикладі ми мали 502, тож це дає R5C02 (рядок 5, стовпець 02).

Якщо у вас багато стовпців, але мало рядків, ви можете змінити його на ROW($2:$9)*1000+СТОВПЕЦ($A:$C),7^8)),"R0C000")
Цей коментар був мінімізований модератором на сайті
Дякую за код. Я використовую код VBA цієї сторінки. Чи є спосіб додати код сортування після вилучення унікальних значень, щоб він сортував його автоматично?
Цей коментар був мінімізований модератором на сайті
чи можемо ми створити функцію uniqdata замість макросу?
Цей коментар був мінімізований модератором на сайті
Привіт, Ільхан! Якщо вам подобається функція, що визначає користувач, щоб створити формулу для вирішення цієї проблеми, наведений нижче код може допомогти вам: Після вставки коду виберіть список клітинок, куди ви хочете помістити результати. Потім введіть цю формулу:=Унікальні (A1:C4)  в рядку формул.Натисніть Ctrl + Shift + Enter ключі разом. 


Function Uniques(ByVal inputRange As Range)
Dim inputArray As Variant
Dim myColl As New Collection
Dim xVal As Variant
Dim outArray() As Variant
On Error Resume Next
With inputRange
inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
End With
On Error GoTo 0
On Error Resume Next
For Each xVal In inputArray
myColl.*** Item:=xVal, Key:=(CStr(xVal) & TypeName(xVal))
Next xVal
myColl.Remove "String"
On Error GoTo 0
ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
For xVal = 1 To UBound(outArray)
outArray(xVal) = vbNullString
Next xVal
For xVal = 1 To Application.Min(myColl.Count, Application.Caller.Cells.Count)
outArray(xVal) = myColl(xVal)
Next xVal
If Application.Caller.Columns.Count = 1 Then
Uniques = Application.Transpose(outArray)
Else
Uniques = outArray
End If
End Function
Цей коментар був мінімізований модератором на сайті
Czy to żart?
Цей коментар був мінімізований модератором на сайті
Формула масиву вгорі чудово працює, коли використовується з даними на тому самому аркуші, однак, коли я намагаюся використовувати її для посилання на ті самі точні дані з іншого аркуша, формула нічого не повертає. Я не можу зрозуміти, чому. Чи є обмеження для функцій масиву, які не дозволяють посилатися на діапазони на іншому аркуші?

Дякую за будь-яку інформацію, яку ви можете надати.
Цей коментар був мінімізований модератором на сайті
Привіт Ерін,

Рада допомогти. Функцію INDIRECT у цій формулі складніше використовувати під час посилань на дані в інших аркушах. Не рекомендується використовувати цю функцію під час посилань на діапазони в різних аркушах.

Наприклад: тепер дані знаходяться в Аркуші1, я хочу посилатися на вміст комірки С2 Аркуша1 в Аркуші2. По-перше, у будь-які дві клітинки в Аркуші2, наприклад D1 і D2, введіть Лист1 і С2 відповідно. На цьому етапі введіть формулу в порожню клітинку Sheet2:
=INDIRECT("'"&D1&"'!"&D2), тоді можна повернути вміст комірки C2 на Аркуші1.

Як бачите, це значно ускладнює ситуацію. Сподіваюся, моє пояснення допоможе. Гарного дня.

З повагою,
Менді
Цей коментар був мінімізований модератором на сайті
Вітаю, сер! VBA творив чудеса, велике спасибі за це! Мені було цікаво, якщо я зміню вихідні дані, чи можна автоматично оновити стовпець унікальними значеннями?
Оцінено 5 з 5
Цей коментар був мінімізований модератором на сайті
Привіт Іоанніс,

Рада допомогти. Після зміни вихідних даних VBA не може автоматично оновити результат. І найпростіший спосіб, який я можу придумати, — це натиснути Ctrl + Alt + F9, щоб оновити всі результати на робочих аркушах у всіх відкритих книгах. Гарного дня.

З повагою,
Менді
Цей коментар був мінімізований модератором на сайті
Дякую за цю чудову статтю.

Для людей, які використовують формуляр масиву в Excel не англійською мовою необхідно звернути особливу увагу на рядок текстового формату: у вашому прикладі: "R0C00".
Для німецької мови це означає "Z0S00". Однак «S» — це спеціальний символ, який позначає секунди для форматування часу. Цей символ потрібно екранувати, тому правильний рядок формату для німецького Excel – "Z0\S00".

Сподіваюся, це комусь допоможе в майбутньому :-)
There are no comments posted here yet
Load More
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

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

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