Как в excel сделать поиск одинаковых ячеек?

Если Вы работаете с большими таблицами в Excel и регулярно добавляете в них, например, данные про учеников школы или сотрудников компании, то в таких таблицах могут появиться повторяющиеся значения, другими словами — дубликаты.

В данной статье мы рассмотрим, как найти, выделить, удалить и посчитать количество повторяющихся значений в Эксель.

Найти и выделить дубликаты в таблице можно, используя условное форматирование в Эксель. Выделите весь диапазон данных в нужной таблице. На вкладке «Главная» кликните на кнопочку «Условное форматирование», выберите из меню «Правила выделения ячеек» — «Повторяющиеся значения».

В следующем окне выберите из выпадающего списка «повторяющиеся», цвет для ячейки и текста, в который нужно закрасить найденные дубликаты. Затем нажмите «ОК» и программа выполнит поиск дубликатов.

Excel выделил повторяющиеся значения в таблице. Как видите, сравниваются не строки таблицы, а ячейки в столбцах. Поэтому выделена ячейка «Саша В.». Таких учеников может быть несколько, но с разными фамилиями. Теперь можете выполнить сортировку в Эксель по цвету ячейки и текста, и удалить найденные повторяющиеся значения.

Чтобы удалить дубликаты в Excel можно воспользоваться следующими способами. Выделяем заполненную таблицу, переходим на вкладку «Данные» и нажимаем кнопочку «Удалить дубликаты».

В следующем окне ставим галочку в пункте «Мои данные содержат заголовки», если Вы выделили таблицу вместе с заголовками. Дальше отметьте галочками столбцы таблицы, в которых нужно найти повторяющиеся значения, и нажмите «ОК».

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

Второй способ для удаления дубликатов — это использование фильтра. Выделяем нужные столбцы таблицы вместе с шапкой. Переходим на вкладку «Данные» и в группе «Сортировка и фильтр» нажимаем на кнопочку «Дополнительно».

В следующем окне в поле «Исходный диапазон» уже указаны ячейки. Отмечаем маркером пункт «скопировать результат в другое место» и в поле «Поместить результат в диапазон» указываем адрес одной ячейки, которая будет левой верхней в новой таблице. Ставим галочку в поле «Только уникальные записи» и нажимаем «ОК».

Будет создана новая таблица, в которой не будет строк с повторяющимися значениями. Если у Вас большая исходная таблица, то создать на ее основе таблицу с уникальными записями, можно на другом рабочем листе Excel. Чтобы подробнее узнать об этом, прочтите статью: фильтр в Эксель.

Если Вам нужно найти и посчитать количество повторяющихся значений в Excel, создадим для этого сводную таблицу Excel. Добавляем в исходную таблицу столбец «Код» и заполняем его «1»: ставим 1, 1 в первых двух ячейка, выделяем их и протягиваем вниз. Когда будут найдены дубликаты для строк, каждый раз значение в столбце «Код» будет увеличиваться на единицу.

Выделяем всю таблицу вместе с заголовками, переходим на вкладку «Вставка» и нажимаем кнопочку «Сводная таблица».

Чтобы более подробно узнать, как работать со сводными таблицами в Эксель, прочтите статью перейдя по ссылке.

В следующем окне уже указаны ячейки диапазона, маркером отмечаем «На новый лист» и нажимаем «ОК».

Справой стороны перетаскиваем первые три заголовка таблицы в область «Названия строк», а поле «Код» перетаскиваем в область «Значения».

В результате получим сводную таблицу без дубликатов, а в поле «Код» будут стоять числа, соответствующие повторяющимся значениям в исходной таблице — сколько раз в ней повторялась данная строка.

Для удобства, выделим все значения в столбце «Сумма по полю Код», и отсортируем их в порядке убывания.

• Думаю теперь, Вы сможете найти, выделить, удалить и даже посчитать количество дубликатов в Excel для всех строк таблицы или только для выделенных столбцов.
• Поделитесь статьёй с друзьями:

Источник: http://officeassist.ru/excel/kak-najti-odinakovye-znacheniya-v-excel/

Как быстро найти дубликаты в списке?

В одной из предыдущих статей мы рассмотрели вопрос: «Как быстро удалить дубликаты в списке?». В этом материале я расскажу о трех способах поиска дубликатов в списке.

Первый способ (сортировка):

Выделяем наш список, в котором необходимо найти повторяющиеся значения, переходим во вкладку меню «Данные», в разделе «Сортировка и фильтр» нажимаем кнопку «Сортировка по убыванию» или «Сортировка по возрастанию»:

Наш список будет отсортирован и в нем визуально будет проще найти повторяющиеся значения:

Второй способ (условное форматирование):

Выделяем наш список, в котором необходимо найти повторяющиеся значения, переходим во вкладку меню «Главная», в разделе «Стили» нажимаем «Условное форматирование» в выпавшем списке выбираем пункт «Правила выделения ячеек», в списке вариантов выбираем «Повторяющиеся значения…»:

В открывшемся диалоговом окне нажимаем «ОК»:

Все повторяющиеся значения в списке будут выделены цветом:

Третий способ (сводная таблица):

Выделяем наш список, в котором необходимо найти повторяющиеся значения, переходим во вкладку меню «Вставка», в разделе «Таблицы» нажимаем кнопку «Сводная таблица»:

• В открывшемся диалоговом окне нажимаем «ОК»:
• 
• Перетаскиваем поле со списком (в моем случае это «Фамилия») в «СТРОКИ» и в «ЗНАЧЕНИЯ»: 

Встаем в первую ячейку с данными сводной таблицы, переходи во вкладку меню «Данные», в разделе «Сортировка и фильтр» нажимаем кнопку «Сортировка по убыванию»:

В верхней части сводной таблицы получаем все повторяющиеся данные с количеством повторов:

Первый и второй варианты безусловно быстрее, но они больше подходят для небольших списков. Если список очень большой и вам важно узнать сколько раз повторяются те или иные данные лучше воспользоваться третьим способом.

Источник: https://e-xcel.ru/index.php/vozmozhnosti/kak-bystro-najti-dublikaty-v-spiske

Как найти значение в другой таблице или сила ВПР

Хитрости » 15 Май 2011       Дмитрий       389385 просмотров

На самом деле я в данной статье хочу рассказать про возможности не только функции ВПР, но так же хочу затронуть и ПОИСКПОЗ, как очень родственную с ВПР функцию. У каждой из данных функций есть как свои плюсы, так и минусы.

Если в двух словах, то ВПР ищет некое указанное нами значение среди множества значений, расположенных в одном столбце. Пожалуй наиболее часто необходимость в ВПР возникает когда надо сравнить данные, найти данные в другой таблице, из одной таблицы добавить данные в другую, опираясь на какой-либо критерий и т.д.

Чтобы чуть лучше понять принцип работы ВПР лучше начать с некоего практического примера. Имеется таблица такого вида:

рис.1

и из первой таблицы необходимо подставить во вторую дату для каждой фамилии. Для трех записей это не проблема и руками сделать — все очевидно. Но в жизни это таблицы на тысячи записей и поиск с подстановкой данных вручную может занять не один час.

Плюс еще пара ложек дегтя: мало того, что ФИО расположены совершенно в разном порядке в обеих таблицах и количество записей в таблицах разное, так еще таблицы расположены на разных листах/книгах. Я полагаю, что убедил вас в том, что подстановка данных руками вообще не вариант. Зато ВПР(VLOOKUP) здесь будет незаменима.

При этом практически ничего не надо будет делать — только записать в первую ячейку столбца С второй таблицы(туда, куда необходимо подставить даты из первой таблицы) такую формулу:
=ВПР($A2;Лист1!$A$2:$C$4;3;0)
Записать формулу можно либо непосредственно в ячейку, либо воспользовавшись диспетчером функций, выбрав в категории Ссылки и массивы ВПР и по отдельности указав нужные критерии. Теперь копируем(Ctrl+C) ячейку с формулой, выделяем все ячейки столбца С до конца данных и вставляем(Ctrl+V).

Теперь разберем поподробнее саму функцию, её аргументы и некоторые особенности. Прежде чем читать далее я бы настоятельно советовал прочитать в встроенной справке подробнее про данную функцию, а если уж будет непонятно — то дочитать статью. Т.к. я лично не уверен, что поясню лучше справки. Но постараюсь 🙂

Сначала основной принцип работы: ВПР ищет в первом столбце аргумента Таблица значение, указанное аргументом Искомое_значение. При нахождении нужного значения функция возвращает значение напротив найденного значения, но из столбца Таблицы, указанного аргументом Номер_столбца. С интервальным просмотром разберемся чуть позже.

ВПР может вернуть только одно значений — первое, подходящее под критерий. Если искомое значение не найдено(отсутствует в таблице), то результатом функции будет #Н/Д. Не надо этого бояться — это даже полезно. Вы точно будете знать, каких записей нет и таким образом можете сравнивать две таблицы друг с другом.

Так же #Н/Д будет, если критерии числа и в искомой таблице они записаны как текст(как правило в левом верхнем углу такой ячейки появляется зеленый треугольничек), а в итоговой — как числа. Или наоборот.

Описание аргументов ВПР
$A2 — аргумент Искомое_значение(назовем его Критерий для краткости). Это то, что мы ищем. Т.е. для первой записи второй таблицы это будет Петров С.А. Здесь можно указать либо непосредственно текст критерия(в этом случае он должен быть в кавычках — =ВПР(«Петров С.А»;Лист1!$A$2:$C$4;3;0), либо ссылку на ячейку, с данным текстом(как в примере функции). Есть небольшой нюанс: так же можно применять символы подстановки: «*» и «?». Это очень удобно, если Вам надо найти значения лишь по части строки. Например, Вы можете не вводить полностью «Петров С.А», а ввести лишь фамилию и знак звездочки — «Петров*». Тогда будет выведена любая запись, которая начинается на «Петров». Если же Вам надо найти запись, в которой в любом месте строки встречается фамилия «Петров», то можно указать так: «*петров*». Если хотите найти фамилию Петров и неважно какие инициалы будут у имени-отчества(если ФИО записаны в виде Иванов И.И.), то здесь в самый раз такой вид: «Иванов ?.?.».  Часто необходимо для каждой строки указать свое значение(в столбце А Фамилии и надо их все найти). В таком случае всегда указываются ссылки на ячейки столбца А. Например, в ячейке A1 записано: Иванов. Так же известно, что Иванов есть в другой таблице, но после фамилии могут быть записаны и имя и отчество(или еще что-то). Но нам нужно найти только строку, которая начинается на фамилию. Тогда необходимо записать следующим образом: A1&»*». Эта запись будет равнозначна «Иванов*». В A1 записано Иванов, амперсанд(&) используется для объединения в одну строку двух текстовых значений. Звездочка в кавычках (как и положено быть тексту внутри формулы). Таким образом и получаем:
A1&»*» =>
«Иванов»&»*» =>
«Иванов*»
Очень удобно, если значений для поиска много.

Если надо определить есть ли хоть где-то слово в строке, то звездочки ставим с обеих сторон: «*»&A1&»*»

Лист1!$A$2:$C$4 — аргумент Таблица. Указывается диапазон ячеек. Только диапазон должен содержать данные от первой ячейки с данными до самой последней. Это не обязательно должен быть указанный в примере диапазон. Если строк 100, то Лист1!$A$2:$C$100.

Важно помнить три вещи: первое, это Таблица всегда должна начинаться с того столбца, в котором ищем Критерий. И никак иначе. В противном случае ничего найдено не будет или результат будет совсем не тот, которого ожидаете. Второе: аргумент Таблица должен быть «закреплен».

Что это значит. Видите знаки доллара — $? Это и есть закрепление(если точнее, то это называется абсолютной ссылкой на диапазон). Как это делается.

Выделяете текст ссылки(только один диапазон — один критерий) и жмете F4 до тех пор, пока не увидите, что и перед обозначением имени столбца и перед номером строки не появились доллары. Если этого не сделать, то при копировании формулы аргумент Таблица будет «съезжать» и результат опять-таки будет неверным.

И последнее — таблица должна содержать столбцы от первого(в котором ищем) до последнего(из которого необходимо возвращать значения). В примере Лист1!$A$2:$C$4 — значит не получится вернуть значение из столбца D(4), т.к. в таблице только три столбца.

3 — Номер_столбца. Здесь просто указываем номер столбца в аргументе Таблица, значения из которого нам необходимо подставить в качестве результата. В примере это Дата принятия — т.е. столбец №3.

Иначе результатом формулы будет ошибка #ССЫЛКА!. Например, если в качестве Таблицы указан диапазон $B$2:$C$4 и необходимо вернуть данные из столбца С, то правильно указать 2. Т.к. аргумент Таблица($B$2:$C$4) содержит только два столбца — В и С.

Если же попытаться указать номер столбца 3(каким по счету он является на листе), то получим ошибку #ССЫЛКА!, т.к. третьего столбца в указанном диапазоне просто нет.

Практический совет: если аргумент Таблица имеет слишком большое кол-во столбцов и Вам необходимо вернуть результат из последнего столбца, то совсем необязательно высчитывать их кол-во. Можно указать так:  =ВПР($A2;Лист1!$A$2:$C$4;ЧИСЛСТОЛБ(Лист1!$A$2:$C$4);0). К слову в данном случае Лист1! тоже можно убрать, как лишнее: =ВПР($A2;Лист1!$A$2:$C$4;ЧИСЛСТОЛБ($A$2:$C$4);0).

— Интервальный_просмотр —  очень интересный аргумент. Может быть равен либо ИСТИНА либо ЛОЖЬ. Сразу возникает вопрос: а почему в моей формуле там 0? Все очень просто — Excel в формулах может воспринимать 0 как ЛОЖЬ, а 1 как ИСТИНА.

Если в ВПР указать данный параметр равный 0 или ЛОЖЬ, то будет происходить поиск точного соответствия заданному Критерию. Это не имеет никакого отношения к знакам подстановки(«*» и «?»). Если же использовать 1 или ИСТИНА(или же вообще не указывать последний аргумент, т.к. по умолчанию он равен ИСТИНА), то…Очень долгая история.

Вкратце — ВПР будет искать наиболее похожее значение, подходящее под Критерий. Иногда очень полезно. Правда, если использовать данный параметр, то необходимо, чтобы список в аргументе Таблица был отсортирован по возрастанию.

Обращаю внимание на то, что сортировка необходима только в том случае, если аргумент Интервальный_просмотр у Вас равен ИСТИНА или 1. Если же 0 или ЛОЖЬ — сортировка не нужна.

Многие наверняка заметили, что на картинке у меня попутаны отделы для ФИО. Это не ошибка записи. В прилагаемом к статье примере показано, как можно одной формулой подставить и их и даты, не меняя вручную аргумент Номер_столбца. Мне показалось, что подобный пример вполне может пригодиться.

=ЕСЛИ(ЕНД(ВПР($A2;Лист1!$A$2:$C$4;3;0));»»;ВПР($A2;Лист1!$A$2:$C$4;3;0)))

Теперь если ВПР не найдет совпадения, то ячейка будет пустой.
А пользователям версий Excel 2007 и выше можно использовать ЕСЛИОШИБКА:

• =ЕСЛИОШИБКА(ВПР($A2;Лист1!$A$2:$C$4;3;0);»»)
• Обещанная ПОИСКПОЗ

Данная функция ищет значение, указанное параметром Искомое_значение в аргументе Просматриваемый_массив. А результатом функции является номер позиции найденного значения в Просматриваемом_массиве. Именно номер позиции, а не само значение.

В принципе её я не буду расписывать так же подробно, потому как основные моменты ровно такие же. Если бы мы хотели применить её для таблицы выше, то она была бы такой:
=ПОИСКПОЗ($A2;Лист1!$A$2:$A$4;0)
$A2 — Искомое_значение. Здесь все ровно так же, как и с ВПР.

Так же допустимы символы подстановки и ровно в таком же исполнении.

Лист1!$A$2:$A$4 — Просматриваемый_массив. Основное отличие от ВПР — допускается указать массив лишь с одним столбцом. Это должен быть тот столбец, в котором мы собираемся искать Искомое_значение. Если попытаться указать более одного столбца, то функция вернет ошибку.

Тип_сопоставления(0) — то же самое, что и в ВПР Интервальный_просмотр. С теми же особенностями. Отличается разве что возможностью поиска наименьшего от искомого или наибольшего. Но на этом я не буду останавливаться в данной статье.

С основным разобрались. Но ведь нам надо вернуть не номер позиции, а само значение. Значит ПОИСКПОЗ в чистом виде нам не подходит. По крайней мере одна, сама по себе. Но если её использовать вместе с функцией ИНДЕКС — то это то, что нам нужно и даже больше.
=ИНДЕКС(Лист1!$A$2:$C$4;ПОИСКПОЗ($A2;Лист1!$A$2:$A$4;0);2)
Такая формула результатом вернет то же, что и ВПР.

Аргументы функции ИНДЕКС
Лист1!$A$2:$C$4 — Массив. В качестве этого аргумента мы указываем диапазон, из которого хотим получить значения. Может быть как один столбец, так и несколько.

В случае, если столбец один, то последний аргумент функции указывать не надо.

К слову — данный аргумент может совершенно не совпадать с тем, который мы указываем в аргументе Просматриваемый_массив функции ПОИСКПОЗ.

Далее идут Номер_строки и Номер_столбца. Именно в качестве Номера_строки мы и подставляем ПОИСКПОЗ, которая возвращает нам номер позиции в массиве. На этом все и строится. ИНДЕКС возвращает значение из Массива, которое находится в указанной строке(Номер_строки) Массива и указанном столбце(Номер_столбца), если столбцов более одного.

И обойти подобные ошибки можно так же:

Для всех версий Excel(включая 2003 и раньше):

=ЕСЛИ(ЕНД(ПОИСКПОЗ($A2;Лист1!$A$2:$A$4;0));»»;ИНДЕКС(Лист1!$A$2:$C$4;ПОИСКПОЗ($A2;Лист1!$A$2:$A$4;0);2))
Для версий 2007 и выше:
=ЕСЛИОШИБКА(ИНДЕКС(Лист1!$A$2:$C$4;ПОИСКПОЗ($A2;Лист1!$A$2:$A$4;0);2);»»)

Работа с критериями длиннее 255 символов
Есть у ИНДЕКС-ПОИСКПОЗ и еще одно преимущество перед ВПР. Дело в том, что ВПР не может искать значения, длина строки которых содержит более 255 символов. Это случается редко, но случается.

Можно, конечно, обмануть ВПР и урезать критерий:
=ВПР(ПСТР($A2;1;255);ПСТР(Лист1!$A$2:$C$4;1;255);3;0)
но это формула массива. Да и к тому же далеко не всегда такая формула вернет нужный результат. Если первые 255 символов идентичны первым 255 символам в таблице, а дальше знаки различаются — формула этого уже не увидит.

Да и возвращает формула исключительно текстовые значения, что в случаях, когда возвращаться должны числа, не очень удобно.

Поэтому лучше использовать такую хитрую формулу:
=ИНДЕКС(Лист1!$A$2:$C$4;СУММПРОИЗВ(ПОИСКПОЗ(ИСТИНА;Лист1!$A$2:$A$4=$A2;0));2)
Здесь я в формулах использовал одинаковые диапазоны для удобочитаемости, но в примере для скачивания они различаются от указанных здесь.

Сама формула построена на возможности функции СУММПРОИЗВ преобразовывать в массивные вычисления некоторых функций внутри неё. В данном случае ПОИСКПОЗ ищет позицию строки, в которой критерий равен значению в строке. Подстановочные символы здесь применить уже не получится.

1. Ну и все же я рекомендовал бы Вам прочитать подробнее про данные функции в справке.
2. В прилагаемом к статье примере Вы найдете примеры использования всех описанных случаев и пример того, почему ИНДЕКС и ПОИСКПОЗ порой предпочтительнее ВПР.
3. Скачать пример
4.   Tips_All_VLookUp.xls (26,0 KiB, 14 541 скачиваний)

Так же см.:
ВПР с поиском по нескольким листам
ВПР с возвратом всех значений
ВПР_МН
ВПР_ВСЕ_КНИГИ
Как заменить/удалить/найти звездочку?

Статья помогла? Поделись ссылкой с друзьями!

Источник: https://www.excel-vba.ru/chto-umeet-excel/kak-najti-znachenie-v-drugoj-tablice-ili-sila-vpr/

Как в экселе найти одинаковые ячейки?

Главная » Прочее »

Загрузка…

Вопрос знатокам: Точнее так — имеются несколько ячеек, в них словосочетания, в которых есть одинаковые слова, но не одинаковые словосочетания, вот мне нужно выделить эти ячейки

С уважением, Михаил Сусло

Лучшие ответы

Смотря какая задача ставится. Тебе нужно что сделать конкретнее? Выделить их цветом, или выбрать? А если есть значения частичного совпадения в трёх ячейках, то как их выделять? Напр: А1 = «красная жопа», А2 = «красная смородина», А3 = «жопа смородина». М?

В любом случае это аналитическая задача, и решается она только макросом и небольшим кусочком VBA. Макрос будет каждую ячейку сравнивать с каждой ячейкой и выплёвывать результат как тебе будет угодно, хоть цветом маркировать, хоть в отдельный столбец, хоть удалять.

Парниша, я решила твою проблему) нажимешь ctrl+f, в поле найти пишешь слово, по которому нужно выделить все ячейки — > искать все -> ctra+a -> тапаешь мышкой на цвет заливки))) все очень просто оказалось

Видео-ответ

Это видео поможет разобраться

Ответы знатоков

а версия Excel? повтор см. в 2-х столбцах или в одном? Если Excel 2007 или новее — Выделяем ячейки и на вкладке Главная жмем кнопку Условное форматирование, затем выбираем Правила выделения ячеек — Повторяющиеся значения

В появившемся затем окне можно задать желаемое форматирование (заливку, цвет шрифта и т. д. )

Если Excel 2003 и старше — Выделяем (для примера — диапазон А2:A10), и идем в меню Формат — Условное форматирование. Выбираем из выпадающего списка вариант условия Формула и вводим такую проверку: =СЧЁТЕСЛИ ($A:$A;A2)>1

Эта простая функция ищет сколько раз содержимое текущей ячейки встречается в столбце А. Если это количество повторений больше 1, т. е. у элемента есть дубликаты, то срабатывает заливка ячейки. Для выбора цвета выделения в окне Условное форматирование нажмите кнопку Формат.. . и перейдите на вкладку Вид

если нужно см. по 2-м столбам, то надо делать доп. столб, где СЦЕПИТЬ данные далее формат по формуле и та же формула, только для сравнения берётся доп. столбец

Попробуйте надстройку сравнение диапазонов vba-excel /examples/matching

я бы сделал так: например, ваши данные: первые два столбца с А1:В100, вторые два столбца О1:Р200. Первые строчки — шапки.

• формула в С2: =ВПР (В2;знакдоллараРзнакдоллара2:знакдоллараРзнакдоллара200;1;ложь)
• Продлеваете ее до конца первой таблицы.
• Там, где будет не ошибка, а МАС, и будут повторяющиеся данные.
• А если хотите вытащить какой именно айпи стоит напротив МАС во второй таблице, тогда надо поменять местами во второй таблице айпи и МАС и такая формула:
• =ВПР (В2;знакдоллараОзнакдоллара2:знакдоллараРзнакдоллара200;2;ложь)
• Блин, латиница мне еще запрещена, так что формулы не копируйте и знак доллара поставьте сами ????

в экселе есть макросы! по типу паскаля….можно там создать цикл который ищет одинаковые ячейки.

тур.компания Русский Остров:

сложная задачка если бы я знала то обязательно помогла бы прости

Написать макрос с поиском. Это если найти вообще одинаковые значения независимо от их местопложения. А если требуется сравнить два листа с одинаковыми структурами, т.е. просто что в одной колонке, одной и тойже строке, но разных файлов.

Можно сделать так:Создаешь новую книгу, затем импортируешь оба файла (можно исвользовать связку, тогда данные автоматически обновляются). И дальше сравниваешь значения на одном листе, со значениями на другом.

Например так =ЕСЛИ(Лист1!C5=Лист2!C5;»совпадает»;»не свопадает»)

Источник: https://dom-voprosov.ru/prochee/kak-v-eksele-najti-odinakovye-yachejki