Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше
В этой статье описаны синтаксис формулы и использование rePT
в Microsoft Excel.
Описание
Повторяет текст заданное число раз. Функция ПОВТОР используется для заполнения ячейки заданным количеством вхождений текстовой строки.
Синтаксис
ПОВТОР(текст;число_повторений)
Аргументы функции ПОВТОР описаны ниже.
-
Текст Обязательный. Повторяемый текст.
-
Число_повторений Обязательный. Положительное число, определяющее, сколько раз требуется повторить текст.
Замечания
-
Если число_повторений равно 0, функция ПОВТОР возвращает пустую строку («»).
-
Если число_повторений не является целым числом, то оно усекается.
-
Результат функции ПОВТОР не должен превышать 32 767 знаков, в противном случае функция ПОВТОР возвращает значение ошибки #ЗНАЧ!.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
|
Формула |
Описание |
Результат |
|
=ПОВТОР(«*-«; 3) |
Возвращает последовательность из трех звездочек и трех дефисов (*-). |
*-*-*- |
|
=ПОВТОР(«-«;10) |
Возвращает последовательность из 10 дефисов (-). |
———- |
Нужна дополнительная помощь?
Генерация дубликатов строк
В большинстве случаев повторы в наших данных нежелательны и мы с вами стараемся от них избавиться разными способами. Но иногда случается, что дубликаты нужны и полезны, и более того — нам необходимо их создавать!
Допустим, что у нас есть вот такая таблица с именами людей, заказавших билет в кино и количеством билетов для каждого:
Каждому билету нужно присвоить уникальный 6-значный номер, который формируется здесь простой функцией СЛУЧМЕЖДУ (RANDBETWEEN), генерирующей целое случайное число в заданном диапазоне 100000-999999. Да, я знаю, что теоретически совпадения могут быть, но вероятность очень невелика и пока нас, допустим, устраивает (кого не устраивает — см.эту статью). Проблема в другом: для тех, кто заказал больше одного билета, нужно вставить в таблицу новые строки-дубли (по количеству заказанных билетов), т.е. на выходе получить вот такое:
Руками такое делать — тоскливо, формулами — сложно. Так что остаются два наиболее удобных варианта — макросы и Power Query.
Способ 1. Создание дубликатов строк макросом
Откроем редактор макросов кнопкой Visual Basic на вкладке Разработчик (Developer) или сочетанием клавиш Alt+F11. Вставим новый модуль через меню Insert — Module и скопируем туда текст нашего макроса:
Sub Duplicate_Rows()
Dim cell As Range
Set cell = Range("B2") 'первая ячейка в столбце с кол-вом билетов
Do While Not IsEmpty(cell)
If cell > 1 Then
cell.Offset(1, 0).Resize(cell.Value - 1, 1).EntireRow.Insert 'вставляем N пустых строк
cell.Resize(cell.Value, 1).EntireRow.FillDown 'заполняем вниз из первых ячеек
End If
Set cell = cell.Offset(cell.Value, 0)
Loop
End Sub
Принцип тут не самый сложный:
- проходим сверху-вниз по столбцу начиная с B2 до первой пустой ячейки
- если число в ячейке >1, то вставляем пустых строк под ячейкой на одну меньше, чем число билетов
- заполняем пустые ячейки (метод FillDown — аналог «протягивания за черный крестик» в правом нижнем углу ячейки)
- переходим к следующей ячейке и т.д.
Способ 2. Создание дубликатов строк в Power Query
Тем, кто хотя бы немного сталкивался с Power Query, рекламировать его мощь не нужно 
Для тех, кто не знаком (если коротко), то Power Query — это бесплатная надстройка для Excel от Microsoft, умеющая делать с данными практически все, что только можно себе представить: загрузку из любых источников, очистку, трансформацию, анализ данных и т.д. Для Excel 2010-2013 ее можно скачать с сайта Microsoft (появится отдельная вкладка Power Query после установки), а в Excel 2016 она уже встроена по-умолчанию (группа Получить внешние данные на вкладке Данные).
Power Query может легко и красиво решить нашу проблему с генерацией дубликатов.
Для начала, выделим нашу таблицу и загрузим ее в Power Query кнопкой Из таблицы/диапазона (From Table/Range) на вкладке Данные (Data) или Power Query:
После окна подтверждения увидим редактор запросов и нашу таблицу. Добавим пользовательский столбец на вкладке Добавить столбец (Add Column — Custom Column):
В появившемся окне введем имя столбца и формулу, которая создает список чисел от 1 до количества билетов в каждой строке:
После нажатия на ОК появится новый столбец со списками, элементы которых можно развернуть в строки, используя кнопку в шапке таблицы:
В итоге, получаем практически то, что хотелось:
Осталось удалить ненужный больше столбец Список (правой кнопкой мыши по заголовку — Удалить столбец) и выгрузить данные обратно на лист на вкладке Главная (Home) с помощью кнопки Закрыть и загрузить — Закрыть и загрузить в… (Close&Load — Close&Load to…) и указать подходящее место для результирующей таблицы:
И останется совсем простая часть — добавить к таблице столбец с формулой СЛУЧМЕЖДУ (RANDBETWEEN) для генерации случайных номеров билетов:
Особенно приятно, что при любых изменениях в исходной (левой) таблице (добавлении новых людей или изменении количества билетов), достаточно будет просто обновить правой кнопкой мыши нашу результирующую таблицу с номерами билетов.
P.S.
Если нужно, чтобы случайные числа не генерировались каждый раз заново при пересчете листа, а формировались один раз, сохраняя потом свои значения, то придется использовать макро-функцию StaticRandBetween из надстройки PLEX или что-то аналогичное.
Также можно, для наглядности, склеивать через дефис номер билета и порядковый номер из столбца Список прямо в Power Query, используя команду Объединить столбцы на вкладке Преобразование (Transform).
Ссылки по теме
- Слияние двух списков без дубликатов
- Извлечение уникальных элементов из диапазона
- Что такое макросы, как их использовать, куда вставлять код макросов на Visual Basic
Функция REPT (ПОВТОР) в Excel. Как использовать?
Функция ПОВТОР (REPT) в Excel используется для осуществления повтора какого-либо текста заданное количество раз.
Что возвращает функция
Возвращает текстовую строку, в которой определенный текст повторяется заданное количество раз.
Синтаксис
=REPT(text, number_times) – английская версия
=ПОВТОР(текст;число_повторений) – русская версия
Как в Excel найти повторяющиеся и одинаковые значения
При совместной работе с таблицами Excel или большом числе записей накапливаются дубли строк. Ста.
Поиск одинаковых значений в Excel
- Выбрать ячейку I3 с записью «С. А. Есенин».
- Поставить задачу – выделить цветом ячейки с такими же записями.
- Выделить область поисков.
- Нажать вкладку «Главная».
- Далее группа «Стили».
- Затем «Условное форматирование»;
- Нажать команду «Равно».
- Появится диалоговое окно:
- В левом поле указать ячейку с I2, в которой записано «С. А. Есенин».
- В правом поле можно выбрать цвет шрифта.
- Нажать «ОК».
Ищем в таблицах Excel все повторяющиеся значения
- Зайти в группу «Стили».
- Далее «Условное форматирование».
- Теперь в выпадающем меню выбрать «Правила выделения ячеек».
- Затем «Повторяющиеся значения».
- Появится диалоговое окно:
- Нажать «ОК».
Удаление одинаковых значений из таблицы Excel
- Зайти во вкладку «Данные».
- Выделить столбец, в котором следует искать дублирующиеся строки.
- Опция «Удалить дубликаты».
- Выбрать вкладку «Данные».
- Перейти в раздел «Сортировка и фильтр».
- Нажать команду «Дополнительно»:
- В появившемся диалоговом окне ставим флажок «Только уникальные записи».
- Нажать «OK» – уникальный список готов.
Как найти повторяющиеся значения в таблице Excel
В этой статье мы рассмотрим разные подходы к одной из самых распространенных и, по моему мнению, важных задач в Excel — как найти в ячейках и в столбцах таблицы повторяющиеся значения.
Работая с большими наборами данных в Excel или объединяя несколько небольших электронных таблиц в более крупные, вы можете столкнуться с большим числом одинаковых строк.
И сегодня я хотел бы поделиться несколькими быстрыми и эффективными методами выявления дубликатов в одном списке. Эти решения работают во всех версиях Excel 2016, Excel 2013, 2010 и ниже. Вот о чём мы поговорим:
Самой простой в использовании и вместе с тем эффективной в данном случае будет функция СЧЁТЕСЛИ (COUNTIF). С помощью одной только неё можно определить не только неуникальные позиции, но и их первые появления в столбце. Рассмотрим разницу на примерах.
Поиск повторяющихся значений включая первые вхождения.
Предположим, что у вас в колонке А находится набор каких-то показателей, среди которых, вероятно, есть одинаковые. Это могут быть номера заказов, названия товаров, имена клиентов и прочие данные. Если ваша задача — найти их, то следующая формула для вас:
Где А2 — первая ячейка из области для поиска.
Просто введите это выражение в любую ячейку и протяните вниз вдоль всей колонки, которую нужно проверить на дубликаты.
Как вы могли заметить на скриншоте выше, формула возвращает ИСТИНА, если имеются совпадения. А для встречающихся только 1 раз значений она показывает ЛОЖЬ.
Подсказка! Если вы ищите повторы в определенной области, а не во всей колонке, обозначьте нужный диапазон и “зафиксируйте” его знаками $. Это значительно ускорит вычисления. Например, если вы ищете в A2:A8, используйте
Если вас путает ИСТИНА и ЛОЖЬ в статусной колонке и вы не хотите держать в уме, что из них означает повторяющееся, а что — уникальное, заверните свою СЧЕТЕСЛИ в функцию ЕСЛИ и укажите любое слово, которое должно соответствовать дубликатам и уникальным:
Если же вам нужно, чтобы формула указывала только на дубли, замените «Уникальное» на пустоту («»):
В этом случае Эксель отметит только неуникальные записи, оставляя пустую ячейку напротив уникальных.
Поиск неуникальных значений без учета первых вхождений
Вы наверняка обратили внимание, что в примерах выше дубликатами обозначаются абсолютно все найденные совпадения. Но зачастую задача заключается в поиске только повторов, оставляя первые вхождения нетронутыми. То есть, когда что-то встречается в первый раз, оно однозначно еще не может быть дубликатом.
Если вам нужно указать только совпадения, давайте немного изменим:
На скриншоте ниже вы видите эту формулу в деле.
Нетрудно заметить, что она не обозначает первое появление слова, а начинает отсчет со второго.
Чувствительный к регистру поиск дубликатов
Хочу обратить ваше внимание на то, что хоть формулы выше и находят 100%-дубликаты, есть один тонкий момент — они не чувствительны к регистру. Быть может, для вас это не принципиально. Но если в ваших данных абв, Абв и АБВ — это три разных параметра – то этот пример для вас.
Как вы могли уже догадаться, выражения, использованные нами ранее, с такой задачей не справятся. Здесь нужно выполнить более тонкий поиск, с чем нам поможет следующая функция массива:
Не забывайте, что формулы массива вводятся комбиинацией Ctrl + Shift + Enter.
Если вернуться к содержанию, то здесь используется функция СОВПАД для сравнения целевой ячейки со всеми остальными ячейками с выбранной области. Результат возвращается в виде ИСТИНА (совпадение) или ЛОЖЬ (не совпадение), которые затем преобразуются в массив из 1 и 0 при помощи оператора (—).
После этого, функция СУММ складывает эти числа. И если полученный результат больше 1, функция ЕСЛИ сообщает о найденном дубликате.
Если вы взглянете на следующий скриншот, вы убедитесь, что поиск действительно учитывает регистр при обнаружении дубликатов:
Смородина и арбуз, которые встречаются дважды, не отмечены в нашем поиске, так как регистр первых букв у них отличается.
Как извлечь дубликаты из диапазона.
Формулы, которые мы описывали выше, позволяют находить дубликаты в определенном столбце. Но часто речь идет о нескольких столбцах, то есть о диапазоне данных.
Рассмотрим это на примере числовой матрицы. К сожалению, с символьными значениями этот метод не работает.
При помощи формулы массива
вы можете получить упорядоченный по возрастанию список дубликатов. Для этого введите это выражение в нужную ячейку и нажмите Ctrl+Alt+Enter .
Затем протащите маркер заполнения вниз на сколько это необходимо.
Чтобы убрать сообщения об ошибке, когда дублирующиеся значения закончатся, можно использовать функцию ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ИНДЕКС(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ( НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));0)=СТРОКА($1:$100);СТРОКА($1:$100));»»);СТРОКА()-1));»»)
Также обратите внимание, что приведенное выше выражение рассчитано на то, что оно будет записано во второй строке. Соответственно выше него будет одна пустая строка.
Поэтому если вам нужно разместить его, к примеру, в ячейке K4, то выражение СТРОКА()-1 в конце замените на СТРОКА()-3.
Обнаружение повторяющихся строк
Мы рассмотрели, как обнаружить одинаковые данные в отдельных ячейках. А если нужно искать дубликаты-строки?
Есть один метод, которым можно воспользоваться, если вам нужно просто выделить одинаковые строки, но не удалять их.
Итак, имеются данные о товарах и заказчиках.
Создадим справа от наших данных формулу, объединяющую содержание всех расположенных слева от нее ячеек.
Предположим, что данные хранятся в столбцах А:C. Запишем в ячейку D2:
Добавим следующую формулу в ячейку E2. Она отобразит, сколько раз встречается значение, полученное нами в столбце D:
Скопируем вниз для всех строк данных.
В столбце E отображается количество появлений этой строки в столбце D. Неповторяющимся строкам будет соответствовать значение 1. Повторам строкам соответствует значение больше 1, указывающее на то, сколько раз такая строка была найдена.
Если вас не интересует определенный столбец, просто не включайте его в выражение, находящееся в D. Например, если вам хочется обнаружить совпадающие строки, не учитывая при этом значение Заказчик, уберите из объединяющей формулы упоминание о ячейке С2.
Обнаруживаем одинаковые ячейки при помощи встроенных фильтров Excel.
Теперь рассмотрим, как можно обойтись без формул при поиске дубликатов в таблице. Быть может, кому-то этот метод покажется более удобным, нежели написание выражений Excel.
Организовав свои данные в виде таблицы, вы можете применять к ним различные фильтры. Фильтр в таблице вы можете установить по одному либо по нескольким столбцам. Давайте рассмотрим на примере.
В первую очередь советую отформатировать наши данные как «умную» таблицу. Напомню: Меню Главная – Форматировать как таблицу.
После этого в строке заголовка появляются значки фильтра. Если нажать один из них, откроется выпадающее меню фильтра, которое содержит всю информацию по данному столбцу. Выберите любой элемент из этого списка, и Excel отобразит данные в соответствии с этим выбором.
Вы можете убрать галочку с пункта «Выделить все», а затем отметить один или несколько нужных элементов. Excel покажет только те строки, которые содержат выбранные значения. Так можно обнаружить дубликаты, если они есть. И все готово для их быстрого удаления.
Но при этом вы видите дубли только по отфильтрованному. Если данных много, то искать таким способом последовательного перебора будет несколько утомительно. Ведь слишком много раз нужно будет устанавливать и менять фильтр.
Используем условное форматирование.
Выделение цветом по условию – весьма важный инструмент Excel, о котором достаточно подробно мы рассказывали.
Но здесь мы не можем исключить первые появления – подсвечивается всё.
Но эту проблему можно решить, использовав формулу условного форматирования.
Результат работы формулы выденения повторяющихся значений вы видите выше. Они выделены зелёным цветом.
Чтобы освежить память, можете руководствоваться нашим материалом «Как изменить цвет ячейки в зависимости от значения».
Поиск совпадений при помощи команды «Найти».
Еще один простой, но не слишком технологичный способ – использование встроенного поиска.
Зайдите на вкладку Главная и кликните «Найти и выделить». Откроется диалоговое окно, в котором можно ввести что угодно для поиска в таблице. Чтобы избежать опечаток, можете скопировать искомое прямо из списка данных.
Затем нажимаем «Найти все», и видим все найденные дубликаты и места их расположения, как на рисунке чуть ниже.
В случае, когда объём информации очень велик и требуется ускорить работу поиска, предварительно выделите столбец или диапазон, в котором нужно искать, и только после этого начинайте работу. Если этого не сделать, Excel будет искать по всем имеющимся данным, что, конечно, медленнее.
Этот метод еще более трудоемкий, нежели использование фильтра. Поэтому применяют его выборочно, только для отдельных значений.
Как применить сводную таблицу для поиска дубликатов.
Многие считают сводные таблицы слишком сложным инструментом, чтобы постоянно им пользоваться. На самом деле, не все так запутано, как кажется. Для новичков рекомендую к ознакомлению наше руководство по созданию и работе со сводными таблицами.
Для более опытных – сразу переходим к сути вопроса.
Создаем новый макет сводной таблицы. А затем в качестве строк и значений используем одно и то же поле. В нашем случае – «Товар». Поскольку название товара – это текст, то для подсчета таких значений Excel по умолчанию использует функцию СЧЕТ, то есть подсчитывает количество. А нам это и нужно. Если будет больше 1, значит, имеются дубликаты.
Вы наблюдаете на скриншоте выше, что несколько товаров дублируются. И что нам это дает? А далее мы просто можем щелкнуть мышкой на любой из цифр, и на новом листе Excel покажет нам, как получилась эта цифра.
К примеру, откуда взялись 3 дубликата Sprite? Щелкаем на цифре 3, и видим такую картину:
Думаю, этот метод вполне можно использовать. Что приятно – никаких формул не требуется.
Duplicate Remover — быстрый и эффективный способ найти дубликаты в Excel
Теперь, когда вы знаете, как использовать формулы для поиска повторяющихся значений в Excel, позвольте мне продемонстрировать вам еще один быстрый, эффективный и без всяких формул способ: инструмент Duplicate Remover для Excel.
Этот универсальный инструмент может искать повторяющиеся или уникальные значения в одном столбце или же сравнивать два столбца. Он может находить, выбирать и выделять повторяющиеся записи или целые повторяющиеся строки, удалять найденные дубли, копировать или перемещать их на другой лист. Я думаю, что пример практического использования может заменить очень много слов, так что давайте перейдем к нему.
Как найти повторяющиеся строки в Excel за 2 быстрых шага
Сначала посмотрим в работе наиболее простой инструмент — быстрый поиск дубликатов Quick Dedupe. Используем уже знакомую нам таблицу, в которой мы выше искали дубликаты при помощи формул:
Как видите, в таблице несколько столбцов. Чтобы найти повторяющиеся записи в этих трех столбцах, просто выполните следующие действия:
- Выберите любую ячейку в таблице и нажмите кнопку Quick Dedupe на ленте Excel. После установки пакета Ultimate Suite для Excel вы найдете её на вкладке Ablebits Data в группе Dedupe. Это наиболее простой инструмент для поиска дубликатов.
- Интеллектуальная надстройка возьмет всю таблицу и попросит вас указать следующие две вещи:
- Выберите столбцы для проверки дубликатов (в данном примере это все 3 столбца – категория, товар и заказчик).
- Выберите действие, которое нужно выполнить с дубликатами. Поскольку наша цель — выявить повторяющиеся строки, я выбрал «Выделить цветом».
Помимо выделения цветом, вам доступен ряд других опций:
- Удалить дубликаты
- Выбрать дубликаты
- Указать их в столбце статуса
- Копировать дубликаты на новый лист
- Переместить на новый лист
Нажмите кнопку ОК и подождите несколько секунд. Готово! И никаких формул .
Как вы можете видеть на скриншоте ниже, все строки с одинаковыми значениями в первых 3 столбцах были обнаружены (первые вхождения не идентифицируются как дубликаты).
Если вам нужны дополнительные возможности для работы с дубликатами и уникальными значениями, используйте мастер удаления дубликатов Duplicate Remover, который может найти дубликаты с первыми вхождениями или без них, а также уникальные значения. Подробные инструкции приведены ниже.
Мастер удаления дубликатов — больше возможностей для поиска дубликатов в Excel.
В зависимости от данных, с которыми вы работаете, вы можете не захотеть рассматривать первые экземпляры идентичных записей как дубликаты. Одно из возможных решений — использовать разные формулы для каждого сценария, как мы обсуждали в этой статье выше. Если же вы ищете быстрый, точный метод без формул, попробуйте мастер удаления дубликатов — Duplicate Remover. Несмотря на свое название, он не только умеет удалять дубликаты, но и производит с ними другие полезные действия, о чём мы далее поговорим подробнее. Также умеет находить уникальные значения.
- Выберите любую ячейку в таблице и нажмите кнопку Duplicate Remover на вкладке Ablebits Data.
- Вам предложены 4 варианта проверки дубликатов в вашем листе Excel:
- Дубликаты без первых вхождений повторяющихся записей.
- Дубликаты с 1-м вхождением.
- Уникальные записи.
- Уникальные значения и 1-е повторяющиеся вхождения.
В этом примере выберем второй вариант, т.е. Дубликаты + 1-е вхождения:
- Теперь выберите столбцы, в которых вы хотите проверить дубликаты. Как и в предыдущем примере, мы возьмём первые 3 столбца:
- Наконец, выберите действие, которое вы хотите выполнить с дубликатами. Как и в случае с инструментом быстрого поиска дубликатов, мастер Duplicate Remover может идентифицировать, выбирать, выделять, удалять, копировать или перемещать повторяющиеся данные.
Поскольку цель этого примера – продемонстрировать различные способы определения дубликатов в Excel, давайте отметим параметр «Выделить цветом» (Highlight with color) и нажмите Готово.
Мастеру Duplicate Remover требуется всего лишь несколько секунд, чтобы проверить вашу таблицу и показать результат:
Как видите, результат аналогичен предыдущему. Но здесь мы выделили дубликаты, включая и первое появление повторяющихся записей.
Никаких формул, никакого стресса, никаких ошибок — всегда быстрые и безупречные результаты 🙂
Итак, мы с вам научились различными способами обнаруживать повторяющиеся записи в таблице Excel. В следующих статьях разберем, что мы с этим можем полезного сделать.
Если вы хотите попробовать эти инструменты для поиска дубликатов в таблицах Excel, вы можете загрузить полнофункциональную ознакомительную версию программы. Будем очень признательны за ваши отзывы в комментариях!
Подсчет уникальных значений в Excel — В этом руководстве вы узнаете, как посчитать уникальные значения в Excel с помощью формул и как это сделать в сводной таблице. Мы также разберём несколько примеров счёта уникальных текстовых и числовых… Как посчитать количество пустых и непустых ячеек в Excel — Если ваша задача — заставить Excel подсчитывать пустые ячейки на листе, прочтите эту статью, чтобы найти 3 способа для этого. Узнайте, как искать и выбирать среди них нужные с помощью стандартных…
Как сравнить два столбца на совпадения и различия — На прочтение этой статьи у вас уйдет около 10 минут, а в следующие 5 минут (или даже быстрее) вы легко сравните два столбца Excel на наличие дубликатов и выделите найденные…
Как выделить цветом повторяющиеся значения в Excel? — В этом руководстве вы узнаете, как отображать дубликаты в Excel. Мы рассмотрим различные методы затенения дублирующих ячеек, целых строк или последовательных повторений с использованием условного форматирования. Ранее мы исследовали различные…
Как посчитать количество повторяющихся значений в Excel? — Зачем считать дубликаты? Мы можем получить ответ на множество интересных вопросов. К примеру, сколько клиентов сделало покупки, сколько менеджеров занималось продажей, сколько раз работали с определённым поставщиком и т.д. Если…
Как убрать повторяющиеся значения в Excel? — В этом руководстве объясняется, как удалять повторяющиеся значения в Excel. Вы изучите несколько различных методов поиска и удаления дубликатов, избавитесь от дублирующих строк, обнаружите точные повторы и частичные совпадения. Хотя…
Функция СЧЁТЕСЛИМН в Excel с несколькими условиями — объясняем на примерах. — В этом руководстве объясняется, как использовать функцию СЧЕТЕСЛИМН с несколькими критериями в Excel на основе логики И и ИЛИ. Вы найдете примеры для разных типов данных — числа, даты, текст,…
СЧЕТЕСЛИ в Excel — примеры функции с одним и несколькими условиями — В этой статье мы сосредоточимся на функции Excel СЧЕТЕСЛИ (COUNTIF в английском варианте), которая предназначена для подсчета ячеек с определённым условием. Сначала мы кратко рассмотрим синтаксис и общее использование, а затем я…
Как проверить правильность ввода данных в Excel? — Подтверждаем правильность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность ввода в соответствии с имеющимся списком допустимых значений. В случае правильного ввода в отдельном столбце ставить…
Если пользователю Excel приходится работать с большой таблицей, и перед ним ставится задача найти уникальные значения, которые подпадают под определенный критерий, то ему нередко приходится применять такой инструмент, как фильтр. Но в некоторых случаях приходится делать другое, а именно, выделять все ряды, в которых есть определенные значения в отношении к другим рядам. Если говорить про эту ситуацию, то здесь нужно применять другую функцию – условное форматирование.
Чтобы отдача была максимальной, нужно в качестве запроса применять выпадающий перечень.
Это хорошо подходит для тех ситуаций, когда надо постоянно изменять запросы одного типа, чтобы экспонировать разные строки диапазона. Сейчас мы подробно расскажем о том, какие действия надо выполнять, чтобы создать выборку из повторяющихся ячеек, входящих в состав выпадающего перечня.
Содержание
- Как выбрать уникальные и повторяющиеся значения в Excel — пошаговая инструкция
- Подготовка содержания выпадающего списка
- Модификация исходной таблицы
- Создание выпадающего списка
- Выборка ячеек из таблицы по условию
- Скачать пример выборки из списка с условным форматированием
- 4 способа выборки данных в Excel
- Расширенный автофильтр
- Формула массива
- Выборка с несколькими условиями с помощью формулы
- Случайная выборка
Как выбрать уникальные и повторяющиеся значения в Excel — пошаговая инструкция
В первую очередь надо разобраться, что такое выборка. Это одно из самых главных статистических понятий, которое означает набор параметров, отобранных по определенному критерию. В качестве выборки может выступать что-угодно: люди для эксперимента, одежда, предприятия, ценные бумаги и так далее.
Чтобы создать выборку, необходимо сначала отобрать те результаты, которые подходят под условия из большого перечня, после чего отобразить эти значения отдельным списком или же в первоначальной таблице.
Подготовка содержания выпадающего списка
Чтобы наша работа сегодня была более наглядной, давайте возьмем историю расчетов с клиентами. Она будет такой, как на рисунке. 
Здесь нам надо выделить все операции, совершенные по отношению к каждому конкретному контрагенту, с помощью цвета. Чтобы переключаться между ними, применим выпадающий список. Следовательно, изначально нужно его сделать, а для этого надо отобрать данные, которые будут его элементами. В нашем примере нам требуются все фамилии контрагентов, которые есть в колонке А и при этом не повторяются. Для подготовки содержания выпадающего списка нам нужно выполнить следующую инструкцию:
- Выделяем первый столбец нашей таблицы.
- Используем инструмент «Данные» – «Сортировка и фильтр» – «Дополнительно».
- После этого перед нами появится окно, в котором нам надо выбрать тип обработки «скопировать результат в другое место», а также поставить флажок возле пункта «Только уникальные записи». В нашем случае диапазоном, который мы используем будет ячейка с адресом $F$1. Знак доллара означает, что ссылка абсолютная и она не будет меняться в зависимости от того, что человек копирует или вставляет содержимое ячейки, которая с ней связана.
- После того, как мы зададим все необходимые параметры, нам нужно нажать на клавишу ОК и так мы подтверждаем наши действия.
Теперь видим перечень ячеек с уникальными фамилиями, которые уже не повторяются. Это и будет наша выборка для списка. 
Модификация исходной таблицы
После этого надо внести некоторые изменения в нашу таблицу. Для этого нужно выделить первые два ряда и нажать на комбинацию клавиш Ctrl+Shift+=. Таким образом у нас вставляются две дополнительные строчки. В новосозданную ячейку A1 вставляем слово «Клиент».
Создание выпадающего списка
После этого нам нужно создать выпадающий список. Чтобы это сделать, выполните следующие действия:
- Делаем клик по ячейке B1. Переходим на вкладку «Данные» – «Работа с данными» – «Проверка данных».
- Появится диалоговое окно, в котором нам надо выбрать тип данных «Список», а в качестве источника данных выбрать наш перечень фамилий. После этого нажимаем на кнопку ОК.
После этого ячейка B1 превращается в полноценный список фамилий клиентов. Если информация, которая служит источником для выпадающего перечня, располагается на другом листе, то в этом случае лучше сделать этот диапазон именным и сослаться на него таким образом.
В случае же с нами в этом нет никакой необходимости, потому что у нас и без того вся информация располагается на одном листе.
Выборка ячеек из таблицы по условию
Теперь же давайте попробуем создать выборку ячеек по условию. Для этого надо выделить ту таблицу, в которой указана фамилия контрагента, его код, номер транзакции и сумма операции, после чего откроем окно «Условное форматирование». Чтобы его вызвать, нужно перейти на вкладку «Главная», там найти группу «Стили», и в ней будет кнопка «Условное форматирование».
Появится меню, в котором нам надо кликнуть по пункту «Создать правило», в качестве которого выбираем «Использовать формулу для определения форматируемых ячеек». 
Далее вводим формулу, указанную на скриншоте, после чего нажимаем на кнопку «Формат», чтобы сделать все ячейки, содержащие одну и ту же фамилию, цветом. Например, зеленым. После этого подтверждаем все выполненные ранее действия путем многократного нажатия на «ОК» на всех окнах, которые будут на тот момент открытыми. После этого когда мы выбираем фамилию нашего человека, все ячейки, включающие ее, выделяются тем цветом, который мы задали.
Как это работает? Когда мы выбираем какое-то значение в выпадающем списке, то формула анализирует все имеющиеся строки, и если видит совпадение, выделяет их обозначенным пользователем цветом. В том, что формула работает, можно убедиться, выбрав другую фамилию. После этого выделение поменяется. Это значительно облегчает чтение таблицы.
Принцип действия следующий: проверяется значение в столбце А. Если оно равно тому, которое выбрано в списке, находящемся в ячейке B1, то эта формула возвращает значение ИСТИНА. После этого целая строка форматируется таким образом, каким вы захотите. В принципе, можно не только выделять эту строчку отдельным цветом, но и произвольно регулировать шрифт, границы и другие параметры. Но выделение цветом – самый быстрый метод.
Как мы добились того, чтобы цветом окрашивалась целая строка, а не отдельная ячейка? Для этого мы применили ссылку на ячейку, где адрес колонки является абсолютным, а номер ряда – относительным.
Скачать пример выборки из списка с условным форматированием
Как это работает? Вы можете попробовать посмотреть наглядно, скачав пример такой таблицы, которая была нами рассмотрена ранее. Для этого нужно перейти по этой ссылке.
4 способа выборки данных в Excel
Но на этом мы не заканчиваем нашу инструкцию. На самом деле, у нас есть целых четыре доступных способа формирования выборки данных в Excel.
Расширенный автофильтр
Это самый легкий метод, который позволяет отобрать значения, которые подходят под определенные критерии. Давайте более подробно рассмотрим, что нужно для этого.
- Предположим, у нас есть таблица, содержащая наименование товара, дату и общее количество денег, которое получилось заработать на продаже конкретной позиции в определенный день. Нам надо выделить ту область, где надо отобрать выборку. Для этого переходим на вкладку «Главная», где находим кнопку «Сортировка и фильтр» и нажимаем на нее. Ее можно найти в блоке инструментов «Редактирование». После этого находим опцию «Фильтр». Приводим скриншот для наглядности.
- Есть и второй способ, как можно поступить в этом случае. Можно найти кнопку «Фильтр» в группе «Сортировка и фильтр», которая располагается на вкладке «Данные».
- После того, как мы это сделаем, сверху таблицы появятся стрелочки, с помощью которых можно отобрать данные для фильтра. Нужно нажать на одну из них (то, какая именно, зависит от того столбца, в котором нам нужно выполнить сортировку). После этого находим пункт «Числовые фильтры», и нажимаем на «Настраиваемый фильтр».
- После этого появляется окно, через которое можно настроить пользовательскую фильтрацию. С его помощью пользователь может задать критерий, исходя из которого и будут отбираться данные. В выпадающем перечне для той колонки, в какой содержатся числовые ячейки (а именно их мы используем для примера), есть возможность выбрать такие критерии, как равно, не равно, больше, больше или равно и меньше. То есть, стандартные арифметические операции сравнения.
Давайте для наглядности зададим правило, согласно которому программа должна отбирать исключительно те значения, в которых сумма выручки составляет больше, чем 10 тысяч рублей. Следовательно, нам нужно в поле, обозначенном на скриншоте цифрой 1, выставить пункт «больше», а в поле, обозначенном цифрой 2, установить значение 10 тысяч (цифрами). Далее осталось только подтвердить наши действия.
- Как мы понимаем, после того, как мы отфильтровали данные остались исключительно те строки, в которых объем заработанных денег без вычета налогов составляет больше, чем 10 тысяч рублей.
- Но у нас есть возможность еще один критерий добавить. Чтобы это сделать, надо снова вернуться к пользовательскому фильтру, в нижней части которого мы видим еще два поля, которые имеют такой же вид, как и тот, в котором мы вводили наши критерии. В нем можно задать второй параметр. Пусть, к примеру, нам надо будет отбирать только те данные, которые не превышают 14999. Для этого выбираем правило «Меньше», а в качестве значения выставляем «15000».
Также можно воспользоваться переключателем условий, который может принимать одно из двух значений: И и ИЛИ. Первоначально он настроен на первый вариант, но если человеку нужно задать соответствие одному из этих условий, то можно выбрать значение ИЛИ. Чтобы переключить тип соотношения между условиями, необходимо поставить тумблер в соответствующее положение. После того, как мы выполнили все необходимые действия, нажимаем на кнопку «ОК».
- Теперь наша таблица отображает только те значения, которые колеблются в пределах от 10 тысяч рублей до 14999 рублей.
Формула массива
Еще один вариант отбора – использование формулы массива. В этом случае результат выводится в отдельную таблицу, что может быть полезно, если первоначальные данные всегда нужно иметь перед глазами в неизменном виде. Чтобы это сделать, нам нужно следующее:
- Копируем шапку таблицы в нужное место.
- Выделяем все ячейки, которые содержатся в первой колонке новосозданной таблицы и переводим курсор в строку ввода формул. После этого туда вставляем следующую формулу (значения, естественно, меняем на свои). =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);»»);СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))
- Подтверждаем ввод с помощью комбинации клавиш Ctrl + Shift + Enter.
- Выполняем аналогичную операцию со вторым столбцом.
- Делаем то же самое с третьим столбцом.
Во всех трех ситуациях формула в целом та же самая, просто изменяются координаты.
После этого присваиваем правильный формат ячейкам, в которых появляется ошибка. Далее используем условное форматирование для выделения тех ячеек, которые содержат определенное значение.
Выборка с несколькими условиями с помощью формулы
Использование формул также позволяет выбирать значения по нескольким критериям. Для этого выполняем следующие действия:
- Задаем условия в специальной колонке таблицы.
- Записываем три формулы с правильными координатами в каждый из вспомогательных столбцов, которые предварительно нужно создать. Точно так же используем для этого формулу массива.
Преимущество этого метода в том, что нет необходимости изменять формулу, если вдруг понадобится изменить условия. Они всегда будут сохраняться в соответствующих ячейках.
Случайная выборка
Ну и наконец, последний метод формирования выборки, который подойдет не во всех ситуациях – использование генератора случайных чисел. Для этого надо использовать функцию =СЛЧИС(). Далее заполняем такое количество ячеек, которое нам нужно, с помощью маркера автозаполнения.
Далее выбираем «Настраиваемая сортировка» в меню фильтра.
Появляется меню настроек, где выставляем параметры так, как на скриншоте. 
Потом нажимаем «ОК» и получаем результат.
Видим, что нет ничего сложного. Если немного потренироваться, то все будет получаться очень легко. Главное – понять принцип, а метод можно выбрать любой, какой душе угодно.
Оцените качество статьи. Нам важно ваше мнение:
При совместной работе с таблицами Excel или большом числе записей накапливаются дубли строк. Ста…
При совместной работе с
таблицами Excel или большом числе записей
накапливаются дубли строк. Статья
посвящена тому, как выделить
повторяющиеся значения в Excel,
удалить лишние записи или сгруппировать,
получив максимум информации.
Поиск
одинаковых значений в Excel
Выберем
одну из ячеек в таблице. Рассмотрим, как
в Экселе найти повторяющиеся значения,
равные содержимому ячейки, и выделить
их цветом.
На
рисунке – списки писателей. Алгоритм
действий следующий:
- Выбрать
ячейку I3
с записью «С. А. Есенин». - Поставить
задачу – выделить цветом ячейки с
такими же записями. - Выделить
область поисков. - Нажать
вкладку «Главная». - Далее
группа «Стили». - Затем
«Условное форматирование»; - Нажать
команду «Равно».
- Появится
диалоговое окно:
- В
левом поле указать ячейку с I2,
в которой записано «С. А. Есенин». - В
правом поле можно выбрать цвет шрифта. - Нажать
«ОК».
В
таблицах отмечены цветом ячейки, значение
которых равно заданному.
Несложно
понять, как
в Экселе найти одинаковые значения в
столбце.
Просто выделить перед поиском нужную
область – конкретный столбец.
Ищем в таблицах Excel
все повторяющиеся значения
Отметим
все неуникальные записи в выделенной
области. Для этого нужно:
- Зайти
в группу «Стили». - Далее
«Условное форматирование». - Теперь
в выпадающем меню выбрать «Правила
выделения ячеек». - Затем
«Повторяющиеся значения».
- Появится
диалоговое окно:
- Нажать
«ОК».
Программа
ищет повторения во всех столбцах.
Если
в таблице много неуникальных записей,
то информативность такого поиска
сомнительна.
Удаление одинаковых значений
из таблицы Excel
Способ
удаления неуникальных записей:
- Зайти
во вкладку «Данные». - Выделить
столбец, в котором следует искать
дублирующиеся строки. - Опция
«Удалить дубликаты».
В
результате получаем список, в котором
каждое имя фигурирует только один раз.
Список
с уникальными значениями:
Расширенный фильтр: оставляем
только уникальные записи
Расширенный
фильтр – это инструмент для получения
упорядоченного списка с уникальными
записями.
- Выбрать
вкладку «Данные». - Перейти
в раздел «Сортировка и фильтр». - Нажать
команду «Дополнительно»:
- В
появившемся диалоговом окне ставим
флажок «Только уникальные записи». - Нажать
«OK»
– уникальный список готов.
Поиск дублирующихся значений
с помощью сводных таблиц
Составим
список уникальных строк, не теряя данные
из других столбцов и не меняя исходную
таблицу. Для этого используем инструмент
Сводная таблица:
Вкладка
«Вставка».
Пункт
«Сводная таблица».
В
диалоговом окне выбрать размещение
сводной таблицы на новом листе.
В
открывшемся окне отмечаем столбец, в
котором содержатся интересующие нас
значений.
Получаем
упорядоченный список уникальных строк.