В этой статье описывается, как получить список уникальных значений в столбце с помощью формулы и как настроить эту формулу для разных наборов данных. Вы также узнаете, как быстро получить единый список с помощью расширенного фильтра Excel и как извлечь уникальные записи с помощью Duplicate Remover.
В нескольких недавних статьях мы обсуждали различные методы подсчета и поиска уникальных значений в Excel. Если у вас была возможность прочитать эти руководства, вы уже знаете, как получить этот список с помощью идентификации, фильтрации и копирования. Но это немного длинный и далеко не единственный способ извлечения уникальных значений в Excel. Вы можете сделать это намного быстрее, используя собственную формулу. А сейчас я покажу вам эту и многие другие техники.
Чтобы избежать путаницы, давайте сначала договоримся о том, что мы называем уникальными значениями в Excel.
Уникальные значения — это значения, которые появляются в списке только один раз. Например:
Чтобы получить список уникальных значений в Excel, используйте одну из следующих формул.
Формула для уникальных значений массива (составляется нажатием Ctrl + Shift + Enter):
= SEERROR (ИНДЕКС ($ A $ 2: $ A $ 10; ПОИСК (0; СЧЁТЕСЛИ ($ B $ 1: B1; $ A $ 2: $ A $ 10) + (СЧЁТЕСЛИ ($ A $ 2: $ A $ 10; $ A $ 2: $ A $ 10) 1); 0)); «»)
Вы также можете использовать обычную формулу (вводимую нажатием Enter):
= ЕСЛИ ОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10; ПОИСК (0; ИНДЕКС (СЧЁТЕСЛИ ($ B $ 1: B1; $ A $ 2: $ A $ 10)) + (СЧЁТЕСЛИ ($ A $ 2 : $ A $ 10; $ A $ 2: $ A $ 10) 1); 0; 0); 0)); «»)
Для приведенных выше формул используются следующие ссылки:
Примечание. Поскольку формула ссылается на ячейку над первой ячейкой в списке, который вы создаете, который обычно является заголовком столбца (B1 в этом примере), убедитесь, что заголовок имеет уникальное имя, которое не отображается в других частях этого столбца.
В этом примере мы получаем уникальные имена из столбца A (точнее из диапазона A2: A10), а на следующем снимке экрана показана формула в действии:
Вот наша процедура:
Различные значения: появляются в списке данных хотя бы один раз. Это все уникальные и первые повторяющиеся значения.
Например:
Чтобы получить их список в Excel, используйте следующие формулы.
Формула массива (нужно нажать Ctrl + Shift + Enter):
{= SEERROR (ИНДЕКС ($ A $ 2: $ A $ 13, ПОИСК (0, СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 13), 0)); «»)}
или вы можете сделать это:
{= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 13; МАЛЫЙ (ЕСЛИ (UND (ПОИСК ($ A $ 2: $ A $ 13, $ B $ 1: B1,0)))), СТРОКА ($ A $ 1: $ A $ 15); «»); 1));»»)}
Обычная формула:
= SEERROR (ИНДЕКС ($ A $ 2: $ A $ 13, ПОИСК (0, ИНДЕКС (СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 13), 0, 0), 0)); «»)
Где:
Если исходный список содержит пустые ячейки, только что описанная формула вернет ноль для каждой пустой строки, что может быть проблемой. Это то, что вы можете видеть на скриншоте чуть выше. Чтобы это исправить, внесем небольшие изменения.
Формула массива для извлечения различных значений, исключая пустые ячейки:
{= ЕСЛИ ОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 13; ПОИСК (0, СЧЁТЕСЛИ ($ C $ 1: C1; $ A $ 2: $ A $ 13 & «»)) + IF ($ A $ 2: $ A $ 13 = «»; 1; 0); 0)); «»)}
Точно так же вы можете получить список разных значений, исключая пустые ячейки и ячейки с числами:
{= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 13; ПОИСК (0; СЧЁТЕСЛИ ($ D $ 1: D1; $ A $ 2: $ A $ 13 & «»)) + SE (ETEXT ($ A $ 2: $ A $ 13) = FALSE; 1; 0); 0)); «»)}
Напоминаем, что в приведенных выше формулах A2: A13 — это исходный список, а B1 — это ячейка непосредственно над первой позицией сгенерированного списка.
Этот экран показывает результат выбора:
Возможно, кому-то будет полезна другая формула –
= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 13; АГРЕГАТ (15,6; (СТРОКА ($ A $ 2: $ A $ 13) -ЛИНИЯ ($ A $ 2) +1)) / (ПОИСК ($ A $ 2: $ A $ 13; $ A $ 2: $ A $ 13,0) = СТРОКА ($ A $ 2: $ A $ 13) — СТРОКА ($ A $ 2) +1); СТРОКА ($ A $ 2: $ A2)));»»)
Работает с числами и текстом, игнорирует пустые ячейки.
При работе с данными, чувствительными к регистру, такими как пароли, имена пользователей или имена файлов, вам может потребоваться список отдельных значений с учетом как прописных, так и строчных букв.
Для этого используйте формулу массива, где A2: A10 — это исходный список, а B1 — это ячейка над первой ячейкой разделенного списка.
Формула массива для разных значений с учетом регистра (требуется нажатие Ctrl + Shift + Enter)
{= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10; ПОИСК (0; ЧАСТОТА (ЕСЛИ (ПОИСКПОЗ ($ A $ 2: $ A $ 10; ТРАНСПОРТИРОВКА ($ B $ 1: B1))))); ПОИСК (СТРОКА ($ A $ 2: $ A $ 10); СТРОКА ($ A $ 2: $ A $ 10)); «»); ПОИСК ($ A $ 2: $ A $ 10); СТРОКА ($ A 2 доллара: 10 австралийских долларов))); 0)); «»)}
Как видите, здесь важна накрутка.
Допустим, у нас есть таблица с данными о продажах. Нам необходимо определить, какие товары заказал конкретный покупатель.
Сначала выберите в таблице только те строки, которые соответствуют указанным условиям, а затем выберите уникальные названия продуктов из этих строк.
В ячейке G2 указываем нужного нам клиента, а в H2 записываем матричную формулу:
{= ЕСЛИ ОШИБКА (ИНДЕКС ($ B $ 2: $ B $ 20; ПОИСК (0; ЕСЛИ ((($ A $ 2: $ A $ 20 = $ G $ 2))), СЧЁТЕСЛИ ($ H $ 1: H1 ; $ B $ 2: $ B $ 20); «»); 0));»»)}
Не забудьте вставить формулу массива в ячейку EXCEL, одновременно нажав CTRL + SHIFT + ENTER. Скопируйте его столбец за столбцом, используя маркер заполнения. Получаем список из четырех позиций.
Усложняем задачу. Мы определяем список не только для этого клиента, но и для конкретного менеджера.
Вот наша матричная формула:
{= ЕСЛИОШИБКА (ИНДЕКС ($ B $ 2: $ B $ 20; ПОИСК (0; ЕСЛИ ((($ A $ 2: $ A $ 20 = $ G $ 2)) + ($ D $ 2: $ D $ 20 = $ H $ 2)) = 2; СЧЁТЕСЛИ ($ I $ 1: I1, $ B $ 2: $ B $ 20), «»); 0));»»)}
Как видите, сейчас всего два продукта. В расчете участвуют только строки, удовлетворяющие одновременно двум условиям: название компании и фамилия оператора должны совпадать. Только из них мы извлекаем уникальные названия продуктов.
Если имеется несколько условий, просто добавьте соответствующий критерий к функции ЕСЛИ и измените число от 2 до 3 или более (в зависимости от количества условий).
Формулы, которые мы описали выше, позволяют сформировать список значений из данных определенного столбца. Но мы часто говорим о нескольких столбцах, то есть о диапазоне данных. Например, вы получили несколько списков продуктов из разных файлов и расположили их в соседних столбцах.
Используя формулу массива
{= КОСВЕННО (ТЕКСТ (МИН (ЕСЛИ (($ A $ 2: $ C $ 9 «») * (СЧЁТЕСЛИ ($ E $ 1: E1; $ A $ 2: $ C $ 9) = 0); ROW ($ 2: $ 9) * 100 + COLUMN ($ A: $ C); 7 ^ 8)); «R0C00″);)&»»}
Здесь A2: C9 указывает диапазон, из которого вы хотите извлечь уникальные значения. E1 — это первая ячейка столбца, в которую вы хотите поместить результат. $ 2: $ 9 указывает на строки, содержащие данные, которые вы хотите использовать. $ A: $ C указывает на столбцы, из которых вы получаете исходные данные. Пожалуйста, поменяйте их на свои.
Нажмите Shift + Ctrl + Enter, затем перетащите маркер заливки, чтобы сгенерировать уникальные значения, пока не появятся пустые ячейки.
Как видите, извлекаются все уникальные и первые вхождения дубликатов.
Начиная с Excel 2007, функция удаления дубликатов является стандартной. Вы можете найти его на вкладке «Данные»> «Удаление дубликатов.
установите флажок, чтобы указать столбцы, в которых вы хотите найти и удалить повторяющиеся значения. Если сделать как на скриншоте, в таблице останутся только уникальные пары «Клиент — Продукт». Остальные будут удалены. Если вы установите только флажок «Клиент», для каждого клиента останется только одна строка и т.д.
Если вы не хотите тратить время на понимание загадочных поворотов формул, вы можете быстро получить список уникальных значений с помощью расширенного фильтра. Подробные инструкции приведены ниже.
Как видите, мы проверили столбец B, а затем добавили список уникальных наименований продуктов, представленных в столбце K.
Обратите внимание, что хотя опция расширенного фильтра называется «Только уникальные записи», она извлекает разные значения, то есть уникальные и первые вхождения дубликатов.
Теперь немного усложним задачу.
Если вам нужно искать записи не по одному, а по нескольким столбцам, вы можете сначала «вставить» их с помощью функции СЦЕПИТЬ.
= ЦЕПЬ (A2; B2)
Записываем в столбец F и копируем. Получаем вспомогательный столбик.
В качестве исходного диапазона мы по-прежнему выбираем данные, из которых извлекаем уникальные значения. Теперь это две колонки: A и B.
Но мы все еще можем искать уникальные в одном столбце. Здесь пригодится вспомогательный столбец F с объединенными данными. Мы указываем это в поле «Диапазон условий».
В остальном все как в предыдущем примере.
В результате мы получили все доступные в таблице комбинации «Клиент — Продукт» на основе данных во вспомогательном столбце F.
Думаю, вы понимаете, что аналогичные действия можно производить с тремя столбцами (например, Фамилия — Имя — Отчество). Главное условие — исходный диапазон был непрерывным, то есть все столбцы должны были быть смежными.
Как видите, формулы здесь не нужны. Однако, если исходные данные изменятся, все манипуляции придется повторить заново.
В заключительной части этого руководства я покажу вам интересное решение для поиска и извлечения различных уникальных значений в электронных таблицах Excel. Это решение сочетает в себе универсальность формул Excel с простотой расширенного фильтра. Кроме того, здесь есть несколько уникальных особенностей:
Теперь давайте посмотрим, как работает инструмент удаления дубликатов.
Допустим, у нас есть большая таблица, созданная путем объединения данных из нескольких других таблиц. Очевидно, что он содержит много повторяющихся строк, и ваша задача — получить уникальные строки, которые появляются только один раз в таблице или в разных строках, включая уникальные вхождения и первые дубликаты. В любом случае, с надстройкой Duplicate Remover работа выполняется всего за несколько шагов.
Мастер удаления дубликатов запустится и выберет всю таблицу. Затем нажмите «Далее», чтобы перейти к следующему шагу.
В этом примере мы хотим извлечь несколько строк, которые хотя бы один раз появляются в исходной таблице, поэтому выбираем опцию Уникальные + 1-е вхождения:
В примечании. Как вы можете видеть на скриншоте выше, есть также 2 варианта поиска дубликатов. Имейте это в виду, если вам нужно искать дубликаты в таблице.
В этом примере мы хотим удалить все повторяющиеся значения на основе значений в 2 столбцах (клиент и продукт), поэтому мы выбираем только те столбцы, которые нам нужны.
В нашем случае таблица имеет заголовок, поэтому мы устанавливаем флажок Моя таблица имеет заголовки.
Думаю, нам не нужны пустые строки, которые могут случайно встретиться при объединении данных из разных таблиц. Поэтому мы также проверяем Пропускать пустые ячейки.
Если вдруг в наших журналах появились лишние пробелы, я думаю, нам следует их игнорировать. Поэтому мы также отмечаем Игнорировать лишние пробелы.
Кроме того, в нашем поиске не учитывается регистр, то есть мы не будем учитывать регистр при сравнении данных. Поэтому мы не трогаем опцию соответствия с учетом регистра.
Чтобы не изменять исходные данные, выберите «Копировать в другую позицию», затем укажите, где именно вы хотите видеть новую таблицу — на том же листе (выберите параметр «Пользовательская позиция» и укажите верхнюю ячейку целевого диапазона), на новом листе (Новый лист) или в новой книге (Новая книга).
В этом примере давайте выберем новый лист:
Нажмите кнопку «Готово» и все!
