1. Як отримати список унікальних (не повторюються) значень?
2. Пошук по мітках
3. Як отримати список унікальних (не повторюються) значень?
4. Пошук по мітках
5. Як отримати список унікальних (не повторюються) значень?
6. Пошук по мітках
7. Як отримати список унікальних (не повторюються) значень?
8. Пошук по мітках
9. Як отримати список унікальних (не повторюються) значень?
10. Пошук по мітках

Як отримати список унікальних (не повторюються) значень?

Уявімо собі великий список різних найменувань, ПІБ, табельних номерів і т.п. А необхідно з цього списку залишити список все тих же найменувань, але щоб вони не повторювалися - тобто видалити з цього списку все дублюючі записи. Як це інакше називають: створити список унікальних елементів, список неповторяющихся, без дублікатів. Для цього існує кілька способів: вбудованими засобами Excel, вбудованими формулами і, нарешті, за допомогою коду Visual Basic for Application (VBA) і зведених таблиць. У цій статті розглянемо кожен з варіантів.

за допомогою вбудованих можливостей Excel 2007 і вище
В Excel 2007 і 2010 це зробити простіше простого - є спеціальна команда, яка так і називається - Видалити дублікати (Remove Duplicates). Розташована вона на вкладці Дані (Data) підрозділ Робота з даними (Data tools)

Як використовувати дану команду. Виділяєте стовпець (або кілька) з тими даними, в яких треба видалити дублюючі записи. Йдете на вкладку Дані (Data) - Видалити дублікати (Remove Duplicates).

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

З'явиться вікно з параметрами видалення дублікатів

Ставите галочки навпроти тих стовпців, дублікати в яких треба видалити і тиснете Ок. Якщо у виділеному діапазоні так само розташовані заголовки даних, то краще поставити прапор Мої дані містять заголовки, щоб випадково не видалити дані в таблиці (якщо вони раптом повністю збігаються зі значенням в заголовку).

• Обробка: Вибираємо Скопіювати результат в інше місце (Copy to another location).
• Вихідний діапазон (List range): Вибираємо діапазон з даними (в нашому випадку це А1: А51).
• Діапазон критеріїв (Criteria range): в даному випадку залишаємо порожнім.
• Помістити результат в діапазон (Copy to): вказуємо перший осередок для виведення даних - будь-яку порожню (на картинці - E2).
• Ставимо галочку Тільки унікальні записи (Unique records only).
• Тиснемо Ок.

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

Так само можна не виносити результат в інші осередки, а відфільтрувати дані на місці. Дані від цього ніяк не постраждають - це буде звичайна фільтрація даних.

Для цього треба просто в пункті Обробка вибрати Фільтрувати список на місці (Filter the list, in-place).

Трохи докладніше про відмінності і нюанси формул ЕСЛІОШІБКА і ЯКЩО (ЕОШ можна прочитати в цій статті: Як в осередку з формулою замість помилки показати 0

Обидва наведених нижче коду слід поміщати в стандартний модуль. Макроси повинні бути дозволені.

Вихідні дані залишимо в тому ж порядку - список з даними розташований в стовпці "А" (А1: А51, де А1 - заголовок). Тільки виводити список ми будемо не в стовпець С, а в стовпець Е, починаючи з комірки Е2:

Sub Extract_Unique () Dim vItem, avArr, li As Long ReDim avArr (1 To Rows.Count, 1 To 1) With New Collection On Error Resume Next For Each vItem In Range ( "A2", Cells (Rows.Count, 1) .End (xlUp)). Value 'Cells (Rows.Count, 1) .End (xlUp) - визначає останню заповнену комірку у стовпчику А .Add vItem, CStr (vItem) If Err = 0 Then li = li + 1: avArr (li, 1) = vItem Else: Err.Clear End If Next End With If li Then [E2] .Resize (li) .Value = avArr End Sub

За допомогою даного коду можна витягти унікальні не тільки з одного стовпчика, а й з будь-якого діапазону стовпців і рядків. Якщо замість рядка
Range ( "A2", Cells (Rows.Count, 1) .End (xlUp)). Value
вказати Selection.Value, то результатом роботи коду буде список унікальних елементів з виділеного на активному аркуші діапазону. Тільки тоді непогано б і осередок виведення значень змінити - замість [E2] поставити ту, в якій даних немає.
Так само можна вказати конкретний діапазон:

Або інший стовпець:

Range ( "C2", Cells (Rows.Count, 3) .End (xlUp)). Value

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

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

Sub Extract_Unique () Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next 'запитуємо адресу осередків для вибору унікальних значень Set rVals = Application.InputBox ( "Вкажіть діапазон комірок для вибірки унікальних значень" , "Запит даних", "A2: A51", Type: = 8) If rVals Is Nothing Then 'якщо натиснута кнопка Скасування Exit Sub End If' якщо вказана тільки одна комірка - немає сенсу вибирати If rVals.Count = 1 Then MsgBox " для відбору унікальних значень потрібно вказати більш одного осередку ", vbInformation," www.excel-vba.ru "Exit Sub End If 'відсікаємо порожні рядки і стовпці поза робочим діапазону Set rVals = Intersect (rVals, rVals.Parent.UsedRange)' якщо вказані тол до порожні клітинки поза робочим діапазону If rVals Is Nothing Then MsgBox "Недостатньо даних для вибору значень", vbInformation, "www.excel-vba.ru" Exit Sub End If avVals = rVals.Value 'запитуємо осередок для виведення результату Set rResultCell = Application .InputBox ( "Вкажіть осередок для вставки відібраних унікальних значень", "Запит даних", "E2", Type: = 8) If rResultCell Is Nothing Then 'якщо натиснута кнопка Скасування Exit Sub End If' визначаємо максимально можливу розмірність масиву для результату ReDim avArr (1 To Rows.Count, 1 To 1) 'за допомогою об'єкта Колекції (Collection)' відбираємо тільки унікальні записи, 'тому що Колекції не можуть містити повторювані значення With New Collection On Error Resume Next For Each x In avVals If Len (CStr (x)) Then 'пропускаємо порожні клітинки .Add x, CStr (x)' якщо елемент, вже є в колекції - виникне помилка 'якщо ж помилки немає - таке значення ще не внесено,' додаємо в результуючий масив If Err = 0 Then li = li + 1 avArr (li, 1) = x Else 'обов'язково очищаємо об'єкт помилки Err.Clear End If End If Next End With 'записуємо результат на лист, починаючи з зазначеної осередку If li Then rResultCell.Cells (1, 1) .Resize (li) .Value = avArr End Sub

• Виділяємо один або декілька стовпців в таблиці, переходимо на вкладку Вставка (Insert) -група Таблиця (Table) - Зведена таблиця (PivotTable)
• У діалоговому вікні Створення зведеної таблиці (Create PivotTable) перевіряємо правильність виділення діапазону даних (або встановити нове джерело даних)
• вказуємо місце розміщення Зведеної таблиці:
  • На новий лист (New Worksheet)
  • На існуючий лист (Existing Worksheet)
• підтверджуємо створення натисканням кнопки OK

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

У чому незручність роботи зі зведеними в даному випадку: при зміні в вихідних даних зведену таблицю доведеться оновлювати вручну: Виділити будь-яку клітинку зведеної таблиці -Права кнопка миші - Відновити (Refresh) або вкладка Дані (Data) - Оновити все (Refresh all) - Оновити (Refresh). А якщо вихідні дані поповнюються динамічно і того гірше - треба буде заново вказувати діапазон вихідних даних. І ще один мінус - дані всередині зведеної таблиці можна змінювати. Тому якщо з отриманим списком необхідно буде працювати надалі, то після створення потрібного списку за допомогою зведеної його треба скопіювати і вставити на потрібний лист.

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

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

Завантажити приклад:

Tips_All_ExtractUnique.xls (108,0 KiB, 14 138 завантажень)

Також див .:
Робота з дублікатами
Як підрахувати кількість повторень
Загальні відомості про зведених таблицях

{ "Bottom bar": { "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1" 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Пошук по мітках

Як отримати список унікальних (не повторюються) значень?

Уявімо собі великий список різних найменувань, ПІБ, табельних номерів і т.п. А необхідно з цього списку залишити список все тих же найменувань, але щоб вони не повторювалися - тобто видалити з цього списку все дублюючі записи. Як це інакше називають: створити список унікальних елементів, список неповторяющихся, без дублікатів. Для цього існує кілька способів: вбудованими засобами Excel, вбудованими формулами і, нарешті, за допомогою коду Visual Basic for Application (VBA) і зведених таблиць. У цій статті розглянемо кожен з варіантів.

за допомогою вбудованих можливостей Excel 2007 і вище
В Excel 2007 і 2010 це зробити простіше простого - є спеціальна команда, яка так і називається - Видалити дублікати (Remove Duplicates). Розташована вона на вкладці Дані (Data) підрозділ Робота з даними (Data tools)

Як використовувати дану команду. Виділяєте стовпець (або кілька) з тими даними, в яких треба видалити дублюючі записи. Йдете на вкладку Дані (Data) - Видалити дублікати (Remove Duplicates).

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

З'явиться вікно з параметрами видалення дублікатів

Ставите галочки навпроти тих стовпців, дублікати в яких треба видалити і тиснете Ок. Якщо у виділеному діапазоні так само розташовані заголовки даних, то краще поставити прапор Мої дані містять заголовки, щоб випадково не видалити дані в таблиці (якщо вони раптом повністю збігаються зі значенням в заголовку).

• Обробка: Вибираємо Скопіювати результат в інше місце (Copy to another location).
• Вихідний діапазон (List range): Вибираємо діапазон з даними (в нашому випадку це А1: А51).
• Діапазон критеріїв (Criteria range): в даному випадку залишаємо порожнім.
• Помістити результат в діапазон (Copy to): вказуємо перший осередок для виведення даних - будь-яку порожню (на картинці - E2).
• Ставимо галочку Тільки унікальні записи (Unique records only).
• Тиснемо Ок.

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

Так само можна не виносити результат в інші осередки, а відфільтрувати дані на місці. Дані від цього ніяк не постраждають - це буде звичайна фільтрація даних.

Для цього треба просто в пункті Обробка вибрати Фільтрувати список на місці (Filter the list, in-place).

Трохи докладніше про відмінності і нюанси формул ЕСЛІОШІБКА і ЯКЩО (ЕОШ можна прочитати в цій статті: Як в осередку з формулою замість помилки показати 0

Обидва наведених нижче коду слід поміщати в стандартний модуль. Макроси повинні бути дозволені.

Вихідні дані залишимо в тому ж порядку - список з даними розташований в стовпці "А" (А1: А51, де А1 - заголовок). Тільки виводити список ми будемо не в стовпець С, а в стовпець Е, починаючи з комірки Е2:

Sub Extract_Unique () Dim vItem, avArr, li As Long ReDim avArr (1 To Rows.Count, 1 To 1) With New Collection On Error Resume Next For Each vItem In Range ( "A2", Cells (Rows.Count, 1) .End (xlUp)). Value 'Cells (Rows.Count, 1) .End (xlUp) - визначає останню заповнену комірку у стовпчику А .Add vItem, CStr (vItem) If Err = 0 Then li = li + 1: avArr (li, 1) = vItem Else: Err.Clear End If Next End With If li Then [E2] .Resize (li) .Value = avArr End Sub

За допомогою даного коду можна витягти унікальні не тільки з одного стовпчика, а й з будь-якого діапазону стовпців і рядків. Якщо замість рядка
Range ( "A2", Cells (Rows.Count, 1) .End (xlUp)). Value
вказати Selection.Value, то результатом роботи коду буде список унікальних елементів з виділеного на активному аркуші діапазону. Тільки тоді непогано б і осередок виведення значень змінити - замість [E2] поставити ту, в якій даних немає.
Так само можна вказати конкретний діапазон:

Або інший стовпець:

Range ( "C2", Cells (Rows.Count, 3) .End (xlUp)). Value

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

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

Sub Extract_Unique () Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next 'запитуємо адресу осередків для вибору унікальних значень Set rVals = Application.InputBox ( "Вкажіть діапазон комірок для вибірки унікальних значень" , "Запит даних", "A2: A51", Type: = 8) If rVals Is Nothing Then 'якщо натиснута кнопка Скасування Exit Sub End If' якщо вказана тільки одна комірка - немає сенсу вибирати If rVals.Count = 1 Then MsgBox " для відбору унікальних значень потрібно вказати більш одного осередку ", vbInformation," www.excel-vba.ru "Exit Sub End If 'відсікаємо порожні рядки і стовпці поза робочим діапазону Set rVals = Intersect (rVals, rVals.Parent.UsedRange)' якщо вказані тол до порожні клітинки поза робочим діапазону If rVals Is Nothing Then MsgBox "Недостатньо даних для вибору значень", vbInformation, "www.excel-vba.ru" Exit Sub End If avVals = rVals.Value 'запитуємо осередок для виведення результату Set rResultCell = Application .InputBox ( "Вкажіть осередок для вставки відібраних унікальних значень", "Запит даних", "E2", Type: = 8) If rResultCell Is Nothing Then 'якщо натиснута кнопка Скасування Exit Sub End If' визначаємо максимально можливу розмірність масиву для результату ReDim avArr (1 To Rows.Count, 1 To 1) 'за допомогою об'єкта Колекції (Collection)' відбираємо тільки унікальні записи, 'тому що Колекції не можуть містити повторювані значення With New Collection On Error Resume Next For Each x In avVals If Len (CStr (x)) Then 'пропускаємо порожні клітинки .Add x, CStr (x)' якщо елемент, вже є в колекції - виникне помилка 'якщо ж помилки немає - таке значення ще не внесено,' додаємо в результуючий масив If Err = 0 Then li = li + 1 avArr (li, 1) = x Else 'обов'язково очищаємо об'єкт помилки Err.Clear End If End If Next End With 'записуємо результат на лист, починаючи з зазначеної осередку If li Then rResultCell.Cells (1, 1) .Resize (li) .Value = avArr End Sub

• Виділяємо один або декілька стовпців в таблиці, переходимо на вкладку Вставка (Insert) -група Таблиця (Table) - Зведена таблиця (PivotTable)
• У діалоговому вікні Створення зведеної таблиці (Create PivotTable) перевіряємо правильність виділення діапазону даних (або встановити нове джерело даних)
• вказуємо місце розміщення Зведеної таблиці:
  • На новий лист (New Worksheet)
  • На існуючий лист (Existing Worksheet)
• підтверджуємо створення натисканням кнопки OK

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

У чому незручність роботи зі зведеними в даному випадку: при зміні в вихідних даних зведену таблицю доведеться оновлювати вручну: Виділити будь-яку клітинку зведеної таблиці -Права кнопка миші - Відновити (Refresh) або вкладка Дані (Data) - Оновити все (Refresh all) - Оновити (Refresh). А якщо вихідні дані поповнюються динамічно і того гірше - треба буде заново вказувати діапазон вихідних даних. І ще один мінус - дані всередині зведеної таблиці можна змінювати. Тому якщо з отриманим списком необхідно буде працювати надалі, то після створення потрібного списку за допомогою зведеної його треба скопіювати і вставити на потрібний лист.

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

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

Завантажити приклад:

Tips_All_ExtractUnique.xls (108,0 KiB, 14 138 завантажень)

Також див .:
Робота з дублікатами
Як підрахувати кількість повторень
Загальні відомості про зведених таблицях

{ "Bottom bar": { "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1" 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Пошук по мітках

Як отримати список унікальних (не повторюються) значень?

Уявімо собі великий список різних найменувань, ПІБ, табельних номерів і т.п. А необхідно з цього списку залишити список все тих же найменувань, але щоб вони не повторювалися - тобто видалити з цього списку все дублюючі записи. Як це інакше називають: створити список унікальних елементів, список неповторяющихся, без дублікатів. Для цього існує кілька способів: вбудованими засобами Excel, вбудованими формулами і, нарешті, за допомогою коду Visual Basic for Application (VBA) і зведених таблиць. У цій статті розглянемо кожен з варіантів.

за допомогою вбудованих можливостей Excel 2007 і вище
В Excel 2007 і 2010 це зробити простіше простого - є спеціальна команда, яка так і називається - Видалити дублікати (Remove Duplicates). Розташована вона на вкладці Дані (Data) підрозділ Робота з даними (Data tools)

Як використовувати дану команду. Виділяєте стовпець (або кілька) з тими даними, в яких треба видалити дублюючі записи. Йдете на вкладку Дані (Data) - Видалити дублікати (Remove Duplicates).

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

З'явиться вікно з параметрами видалення дублікатів

Ставите галочки навпроти тих стовпців, дублікати в яких треба видалити і тиснете Ок. Якщо у виділеному діапазоні так само розташовані заголовки даних, то краще поставити прапор Мої дані містять заголовки, щоб випадково не видалити дані в таблиці (якщо вони раптом повністю збігаються зі значенням в заголовку).

• Обробка: Вибираємо Скопіювати результат в інше місце (Copy to another location).
• Вихідний діапазон (List range): Вибираємо діапазон з даними (в нашому випадку це А1: А51).
• Діапазон критеріїв (Criteria range): в даному випадку залишаємо порожнім.
• Помістити результат в діапазон (Copy to): вказуємо перший осередок для виведення даних - будь-яку порожню (на картинці - E2).
• Ставимо галочку Тільки унікальні записи (Unique records only).
• Тиснемо Ок.

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

Так само можна не виносити результат в інші осередки, а відфільтрувати дані на місці. Дані від цього ніяк не постраждають - це буде звичайна фільтрація даних.

Для цього треба просто в пункті Обробка вибрати Фільтрувати список на місці (Filter the list, in-place).

Трохи докладніше про відмінності і нюанси формул ЕСЛІОШІБКА і ЯКЩО (ЕОШ можна прочитати в цій статті: Як в осередку з формулою замість помилки показати 0

Обидва наведених нижче коду слід поміщати в стандартний модуль. Макроси повинні бути дозволені.

Вихідні дані залишимо в тому ж порядку - список з даними розташований в стовпці "А" (А1: А51, де А1 - заголовок). Тільки виводити список ми будемо не в стовпець С, а в стовпець Е, починаючи з комірки Е2:

Sub Extract_Unique () Dim vItem, avArr, li As Long ReDim avArr (1 To Rows.Count, 1 To 1) With New Collection On Error Resume Next For Each vItem In Range ( "A2", Cells (Rows.Count, 1) .End (xlUp)). Value 'Cells (Rows.Count, 1) .End (xlUp) - визначає останню заповнену комірку у стовпчику А .Add vItem, CStr (vItem) If Err = 0 Then li = li + 1: avArr (li, 1) = vItem Else: Err.Clear End If Next End With If li Then [E2] .Resize (li) .Value = avArr End Sub

За допомогою даного коду можна витягти унікальні не тільки з одного стовпчика, а й з будь-якого діапазону стовпців і рядків. Якщо замість рядка
Range ( "A2", Cells (Rows.Count, 1) .End (xlUp)). Value
вказати Selection.Value, то результатом роботи коду буде список унікальних елементів з виділеного на активному аркуші діапазону. Тільки тоді непогано б і осередок виведення значень змінити - замість [E2] поставити ту, в якій даних немає.
Так само можна вказати конкретний діапазон:

Або інший стовпець:

Range ( "C2", Cells (Rows.Count, 3) .End (xlUp)). Value

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

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

Sub Extract_Unique () Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next 'запитуємо адресу осередків для вибору унікальних значень Set rVals = Application.InputBox ( "Вкажіть діапазон комірок для вибірки унікальних значень" , "Запит даних", "A2: A51", Type: = 8) If rVals Is Nothing Then 'якщо натиснута кнопка Скасування Exit Sub End If' якщо вказана тільки одна комірка - немає сенсу вибирати If rVals.Count = 1 Then MsgBox " для відбору унікальних значень потрібно вказати більш одного осередку ", vbInformation," www.excel-vba.ru "Exit Sub End If 'відсікаємо порожні рядки і стовпці поза робочим діапазону Set rVals = Intersect (rVals, rVals.Parent.UsedRange)' якщо вказані тол до порожні клітинки поза робочим діапазону If rVals Is Nothing Then MsgBox "Недостатньо даних для вибору значень", vbInformation, "www.excel-vba.ru" Exit Sub End If avVals = rVals.Value 'запитуємо осередок для виведення результату Set rResultCell = Application .InputBox ( "Вкажіть осередок для вставки відібраних унікальних значень", "Запит даних", "E2", Type: = 8) If rResultCell Is Nothing Then 'якщо натиснута кнопка Скасування Exit Sub End If' визначаємо максимально можливу розмірність масиву для результату ReDim avArr (1 To Rows.Count, 1 To 1) 'за допомогою об'єкта Колекції (Collection)' відбираємо тільки унікальні записи, 'тому що Колекції не можуть містити повторювані значення With New Collection On Error Resume Next For Each x In avVals If Len (CStr (x)) Then 'пропускаємо порожні клітинки .Add x, CStr (x)' якщо елемент, вже є в колекції - виникне помилка 'якщо ж помилки немає - таке значення ще не внесено,' додаємо в результуючий масив If Err = 0 Then li = li + 1 avArr (li, 1) = x Else 'обов'язково очищаємо об'єкт помилки Err.Clear End If End If Next End With 'записуємо результат на лист, починаючи з зазначеної осередку If li Then rResultCell.Cells (1, 1) .Resize (li) .Value = avArr End Sub

• Виділяємо один або декілька стовпців в таблиці, переходимо на вкладку Вставка (Insert) -група Таблиця (Table) - Зведена таблиця (PivotTable)
• У діалоговому вікні Створення зведеної таблиці (Create PivotTable) перевіряємо правильність виділення діапазону даних (або встановити нове джерело даних)
• вказуємо місце розміщення Зведеної таблиці:
  • На новий лист (New Worksheet)
  • На існуючий лист (Existing Worksheet)
• підтверджуємо створення натисканням кнопки OK

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

У чому незручність роботи зі зведеними в даному випадку: при зміні в вихідних даних зведену таблицю доведеться оновлювати вручну: Виділити будь-яку клітинку зведеної таблиці -Права кнопка миші - Відновити (Refresh) або вкладка Дані (Data) - Оновити все (Refresh all) - Оновити (Refresh). А якщо вихідні дані поповнюються динамічно і того гірше - треба буде заново вказувати діапазон вихідних даних. І ще один мінус - дані всередині зведеної таблиці можна змінювати. Тому якщо з отриманим списком необхідно буде працювати надалі, то після створення потрібного списку за допомогою зведеної його треба скопіювати і вставити на потрібний лист.

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

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

Завантажити приклад:

Tips_All_ExtractUnique.xls (108,0 KiB, 14 138 завантажень)

Також див .:
Робота з дублікатами
Як підрахувати кількість повторень
Загальні відомості про зведених таблицях

{ "Bottom bar": { "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1" 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Пошук по мітках

Як отримати список унікальних (не повторюються) значень?

Уявімо собі великий список різних найменувань, ПІБ, табельних номерів і т.п. А необхідно з цього списку залишити список все тих же найменувань, але щоб вони не повторювалися - тобто видалити з цього списку все дублюючі записи. Як це інакше називають: створити список унікальних елементів, список неповторяющихся, без дублікатів. Для цього існує кілька способів: вбудованими засобами Excel, вбудованими формулами і, нарешті, за допомогою коду Visual Basic for Application (VBA) і зведених таблиць. У цій статті розглянемо кожен з варіантів.

за допомогою вбудованих можливостей Excel 2007 і вище
В Excel 2007 і 2010 це зробити простіше простого - є спеціальна команда, яка так і називається - Видалити дублікати (Remove Duplicates). Розташована вона на вкладці Дані (Data) підрозділ Робота з даними (Data tools)

Як використовувати дану команду. Виділяєте стовпець (або кілька) з тими даними, в яких треба видалити дублюючі записи. Йдете на вкладку Дані (Data) - Видалити дублікати (Remove Duplicates).

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

З'явиться вікно з параметрами видалення дублікатів

Ставите галочки навпроти тих стовпців, дублікати в яких треба видалити і тиснете Ок. Якщо у виділеному діапазоні так само розташовані заголовки даних, то краще поставити прапор Мої дані містять заголовки, щоб випадково не видалити дані в таблиці (якщо вони раптом повністю збігаються зі значенням в заголовку).

• Обробка: Вибираємо Скопіювати результат в інше місце (Copy to another location).
• Вихідний діапазон (List range): Вибираємо діапазон з даними (в нашому випадку це А1: А51).
• Діапазон критеріїв (Criteria range): в даному випадку залишаємо порожнім.
• Помістити результат в діапазон (Copy to): вказуємо перший осередок для виведення даних - будь-яку порожню (на картинці - E2).
• Ставимо галочку Тільки унікальні записи (Unique records only).
• Тиснемо Ок.

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

Так само можна не виносити результат в інші осередки, а відфільтрувати дані на місці. Дані від цього ніяк не постраждають - це буде звичайна фільтрація даних.

Для цього треба просто в пункті Обробка вибрати Фільтрувати список на місці (Filter the list, in-place).

Трохи докладніше про відмінності і нюанси формул ЕСЛІОШІБКА і ЯКЩО (ЕОШ можна прочитати в цій статті: Як в осередку з формулою замість помилки показати 0

Обидва наведених нижче коду слід поміщати в стандартний модуль. Макроси повинні бути дозволені.

Вихідні дані залишимо в тому ж порядку - список з даними розташований в стовпці "А" (А1: А51, де А1 - заголовок). Тільки виводити список ми будемо не в стовпець С, а в стовпець Е, починаючи з комірки Е2:

Sub Extract_Unique () Dim vItem, avArr, li As Long ReDim avArr (1 To Rows.Count, 1 To 1) With New Collection On Error Resume Next For Each vItem In Range ( "A2", Cells (Rows.Count, 1) .End (xlUp)). Value 'Cells (Rows.Count, 1) .End (xlUp) - визначає останню заповнену комірку у стовпчику А .Add vItem, CStr (vItem) If Err = 0 Then li = li + 1: avArr (li, 1) = vItem Else: Err.Clear End If Next End With If li Then [E2] .Resize (li) .Value = avArr End Sub

За допомогою даного коду можна витягти унікальні не тільки з одного стовпчика, а й з будь-якого діапазону стовпців і рядків. Якщо замість рядка
Range ( "A2", Cells (Rows.Count, 1) .End (xlUp)). Value
вказати Selection.Value, то результатом роботи коду буде список унікальних елементів з виділеного на активному аркуші діапазону. Тільки тоді непогано б і осередок виведення значень змінити - замість [E2] поставити ту, в якій даних немає.
Так само можна вказати конкретний діапазон:

Або інший стовпець:

Range ( "C2", Cells (Rows.Count, 3) .End (xlUp)). Value

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

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

Sub Extract_Unique () Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next 'запитуємо адресу осередків для вибору унікальних значень Set rVals = Application.InputBox ( "Вкажіть діапазон комірок для вибірки унікальних значень" , "Запит даних", "A2: A51", Type: = 8) If rVals Is Nothing Then 'якщо натиснута кнопка Скасування Exit Sub End If' якщо вказана тільки одна комірка - немає сенсу вибирати If rVals.Count = 1 Then MsgBox " для відбору унікальних значень потрібно вказати більш одного осередку ", vbInformation," www.excel-vba.ru "Exit Sub End If 'відсікаємо порожні рядки і стовпці поза робочим діапазону Set rVals = Intersect (rVals, rVals.Parent.UsedRange)' якщо вказані тол до порожні клітинки поза робочим діапазону If rVals Is Nothing Then MsgBox "Недостатньо даних для вибору значень", vbInformation, "www.excel-vba.ru" Exit Sub End If avVals = rVals.Value 'запитуємо осередок для виведення результату Set rResultCell = Application .InputBox ( "Вкажіть осередок для вставки відібраних унікальних значень", "Запит даних", "E2", Type: = 8) If rResultCell Is Nothing Then 'якщо натиснута кнопка Скасування Exit Sub End If' визначаємо максимально можливу розмірність масиву для результату ReDim avArr (1 To Rows.Count, 1 To 1) 'за допомогою об'єкта Колекції (Collection)' відбираємо тільки унікальні записи, 'тому що Колекції не можуть містити повторювані значення With New Collection On Error Resume Next For Each x In avVals If Len (CStr (x)) Then 'пропускаємо порожні клітинки .Add x, CStr (x)' якщо елемент, вже є в колекції - виникне помилка 'якщо ж помилки немає - таке значення ще не внесено,' додаємо в результуючий масив If Err = 0 Then li = li + 1 avArr (li, 1) = x Else 'обов'язково очищаємо об'єкт помилки Err.Clear End If End If Next End With 'записуємо результат на лист, починаючи з зазначеної осередку If li Then rResultCell.Cells (1, 1) .Resize (li) .Value = avArr End Sub

• Виділяємо один або декілька стовпців в таблиці, переходимо на вкладку Вставка (Insert) -група Таблиця (Table) - Зведена таблиця (PivotTable)
• У діалоговому вікні Створення зведеної таблиці (Create PivotTable) перевіряємо правильність виділення діапазону даних (або встановити нове джерело даних)
• вказуємо місце розміщення Зведеної таблиці:
  • На новий лист (New Worksheet)
  • На існуючий лист (Existing Worksheet)
• підтверджуємо створення натисканням кнопки OK

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

У чому незручність роботи зі зведеними в даному випадку: при зміні в вихідних даних зведену таблицю доведеться оновлювати вручну: Виділити будь-яку клітинку зведеної таблиці -Права кнопка миші - Відновити (Refresh) або вкладка Дані (Data) - Оновити все (Refresh all) - Оновити (Refresh). А якщо вихідні дані поповнюються динамічно і того гірше - треба буде заново вказувати діапазон вихідних даних. І ще один мінус - дані всередині зведеної таблиці можна змінювати. Тому якщо з отриманим списком необхідно буде працювати надалі, то після створення потрібного списку за допомогою зведеної його треба скопіювати і вставити на потрібний лист.

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

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

Завантажити приклад:

Tips_All_ExtractUnique.xls (108,0 KiB, 14 138 завантажень)

Також див .:
Робота з дублікатами
Як підрахувати кількість повторень
Загальні відомості про зведених таблицях

{ "Bottom bar": { "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1" 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Пошук по мітках

Як отримати список унікальних (не повторюються) значень?

Уявімо собі великий список різних найменувань, ПІБ, табельних номерів і т.п. А необхідно з цього списку залишити список все тих же найменувань, але щоб вони не повторювалися - тобто видалити з цього списку все дублюючі записи. Як це інакше називають: створити список унікальних елементів, список неповторяющихся, без дублікатів. Для цього існує кілька способів: вбудованими засобами Excel, вбудованими формулами і, нарешті, за допомогою коду Visual Basic for Application (VBA) і зведених таблиць. У цій статті розглянемо кожен з варіантів.

за допомогою вбудованих можливостей Excel 2007 і вище
В Excel 2007 і 2010 це зробити простіше простого - є спеціальна команда, яка так і називається - Видалити дублікати (Remove Duplicates). Розташована вона на вкладці Дані (Data) підрозділ Робота з даними (Data tools)

Як використовувати дану команду. Виділяєте стовпець (або кілька) з тими даними, в яких треба видалити дублюючі записи. Йдете на вкладку Дані (Data) - Видалити дублікати (Remove Duplicates).

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

З'явиться вікно з параметрами видалення дублікатів

Ставите галочки навпроти тих стовпців, дублікати в яких треба видалити і тиснете Ок. Якщо у виділеному діапазоні так само розташовані заголовки даних, то краще поставити прапор Мої дані містять заголовки, щоб випадково не видалити дані в таблиці (якщо вони раптом повністю збігаються зі значенням в заголовку).

• Обробка: Вибираємо Скопіювати результат в інше місце (Copy to another location).
• Вихідний діапазон (List range): Вибираємо діапазон з даними (в нашому випадку це А1: А51).
• Діапазон критеріїв (Criteria range): в даному випадку залишаємо порожнім.
• Помістити результат в діапазон (Copy to): вказуємо перший осередок для виведення даних - будь-яку порожню (на картинці - E2).
• Ставимо галочку Тільки унікальні записи (Unique records only).
• Тиснемо Ок.

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

Так само можна не виносити результат в інші осередки, а відфільтрувати дані на місці. Дані від цього ніяк не постраждають - це буде звичайна фільтрація даних.

Для цього треба просто в пункті Обробка вибрати Фільтрувати список на місці (Filter the list, in-place).

Трохи докладніше про відмінності і нюанси формул ЕСЛІОШІБКА і ЯКЩО (ЕОШ можна прочитати в цій статті: Як в осередку з формулою замість помилки показати 0

Обидва наведених нижче коду слід поміщати в стандартний модуль. Макроси повинні бути дозволені.

Вихідні дані залишимо в тому ж порядку - список з даними розташований в стовпці "А" (А1: А51, де А1 - заголовок). Тільки виводити список ми будемо не в стовпець С, а в стовпець Е, починаючи з комірки Е2:

Sub Extract_Unique () Dim vItem, avArr, li As Long ReDim avArr (1 To Rows.Count, 1 To 1) With New Collection On Error Resume Next For Each vItem In Range ( "A2", Cells (Rows.Count, 1) .End (xlUp)). Value 'Cells (Rows.Count, 1) .End (xlUp) - визначає останню заповнену комірку у стовпчику А .Add vItem, CStr (vItem) If Err = 0 Then li = li + 1: avArr (li, 1) = vItem Else: Err.Clear End If Next End With If li Then [E2] .Resize (li) .Value = avArr End Sub

За допомогою даного коду можна витягти унікальні не тільки з одного стовпчика, а й з будь-якого діапазону стовпців і рядків. Якщо замість рядка
Range ( "A2", Cells (Rows.Count, 1) .End (xlUp)). Value
вказати Selection.Value, то результатом роботи коду буде список унікальних елементів з виділеного на активному аркуші діапазону. Тільки тоді непогано б і осередок виведення значень змінити - замість [E2] поставити ту, в якій даних немає.
Так само можна вказати конкретний діапазон:

Або інший стовпець:

Range ( "C2", Cells (Rows.Count, 3) .End (xlUp)). Value

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

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

Sub Extract_Unique () Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next 'запитуємо адресу осередків для вибору унікальних значень Set rVals = Application.InputBox ( "Вкажіть діапазон комірок для вибірки унікальних значень" , "Запит даних", "A2: A51", Type: = 8) If rVals Is Nothing Then 'якщо натиснута кнопка Скасування Exit Sub End If' якщо вказана тільки одна комірка - немає сенсу вибирати If rVals.Count = 1 Then MsgBox " для відбору унікальних значень потрібно вказати більш одного осередку ", vbInformation," www.excel-vba.ru "Exit Sub End If 'відсікаємо порожні рядки і стовпці поза робочим діапазону Set rVals = Intersect (rVals, rVals.Parent.UsedRange)' якщо вказані тол до порожні клітинки поза робочим діапазону If rVals Is Nothing Then MsgBox "Недостатньо даних для вибору значень", vbInformation, "www.excel-vba.ru" Exit Sub End If avVals = rVals.Value 'запитуємо осередок для виведення результату Set rResultCell = Application .InputBox ( "Вкажіть осередок для вставки відібраних унікальних значень", "Запит даних", "E2", Type: = 8) If rResultCell Is Nothing Then 'якщо натиснута кнопка Скасування Exit Sub End If' визначаємо максимально можливу розмірність масиву для результату ReDim avArr (1 To Rows.Count, 1 To 1) 'за допомогою об'єкта Колекції (Collection)' відбираємо тільки унікальні записи, 'тому що Колекції не можуть містити повторювані значення With New Collection On Error Resume Next For Each x In avVals If Len (CStr (x)) Then 'пропускаємо порожні клітинки .Add x, CStr (x)' якщо елемент, вже є в колекції - виникне помилка 'якщо ж помилки немає - таке значення ще не внесено,' додаємо в результуючий масив If Err = 0 Then li = li + 1 avArr (li, 1) = x Else 'обов'язково очищаємо об'єкт помилки Err.Clear End If End If Next End With 'записуємо результат на лист, починаючи з зазначеної осередку If li Then rResultCell.Cells (1, 1) .Resize (li) .Value = avArr End Sub

• Виділяємо один або декілька стовпців в таблиці, переходимо на вкладку Вставка (Insert) -група Таблиця (Table) - Зведена таблиця (PivotTable)
• У діалоговому вікні Створення зведеної таблиці (Create PivotTable) перевіряємо правильність виділення діапазону даних (або встановити нове джерело даних)
• вказуємо місце розміщення Зведеної таблиці:
  • На новий лист (New Worksheet)
  • На існуючий лист (Existing Worksheet)
• підтверджуємо створення натисканням кнопки OK

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

У чому незручність роботи зі зведеними в даному випадку: при зміні в вихідних даних зведену таблицю доведеться оновлювати вручну: Виділити будь-яку клітинку зведеної таблиці -Права кнопка миші - Відновити (Refresh) або вкладка Дані (Data) - Оновити все (Refresh all) - Оновити (Refresh). А якщо вихідні дані поповнюються динамічно і того гірше - треба буде заново вказувати діапазон вихідних даних. І ще один мінус - дані всередині зведеної таблиці можна змінювати. Тому якщо з отриманим списком необхідно буде працювати надалі, то після створення потрібного списку за допомогою зведеної його треба скопіювати і вставити на потрібний лист.

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

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

Завантажити приклад:

Tips_All_ExtractUnique.xls (108,0 KiB, 14 138 завантажень)

Також див .:
Робота з дублікатами
Як підрахувати кількість повторень
Загальні відомості про зведених таблицях

{ "Bottom bar": { "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1" 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Пошук по мітках