Как найти повторяющиеся значения в таблице excel

Различия

Проанализировав результаты, полученные при использовании инструментов ПОИСК и НАЙТИ, можно выделить несколько ключевых отличий:

1. Функция НАЙТИ чувствительна к регистру букв, то есть алгоритм распознает большие и маленькие символы. Это четко видно на примере фрукта Апельсин. ПОИСК вернул 1, а НАЙТИ выдало ошибку, поскольку маленькой буквы а нет в ячейке. Подобная ситуация и для Ананаса с Айвой

1. Дополнительным различием является то, что ПОИСК умеет работать с подстановочными символами типа вопросительный знак (?) и звездочка (*), которые используются для неточного формирования поискового запроса.

1. Инструмент НАЙТИ реализован как в виде отдельной кнопки на главной панели, так и в виде встроенной функции редактора.

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

Жми «Нравится» и получай только лучшие посты в Facebook ↓

Как произвести поиск по нескольким Excel файлам?

​ все ячейки, которые​ время и нервы​ функцию «Открыть». Появится​ организовано, что ищем​ End Sub​: Если данный макрос​ на одном уровне,​ файлах.​ = searchmask$​ их характеристик) на​Подскажите, пожалуйста, как​​ такие таблици будут​ выводить имя файла​ непростой вопрос.​ ячейку, формат которой​ только на активном​ в любом виде​ содержат данный последовательный​​ поможет встроенный поиск​ список последних книг​ и как выводим​Юрий М​ при поиске открывает​ в одной папке!​​Подскажите, какие есть​ = «*.*xl*» ‘​ лист For i​ реализовать следующую задачу:​ добавляться ежедневно.​ не нужно.​​В файле «ГЛАВНЫЙ​ вы собираетесь использовать.​ листе Excel. Но,​ не зависимо от​ набор символов даже​

​ Microsoft Excel. Давайте​​ Excel, с которыми​ результаты поиска.​: Если Вы про​ файлы, то при​ Макрос работает отлично.​ варианты — готовые​ searchdepth% = 1​ = 1 To​Имеются файлы excel,,​Таблица за 6​Hugo​ ФАЙЛ.xls» в ячейку​После того, как формат​

​ если параметр​

• Excel поиск в диапазоне
• Excel если несколько условий
• Excel поиск значения по нескольким условиям в excel
• Excel поиск значения по двум критериям
• Excel счетесли несколько условий
• Поиск в excel по нескольким условиям в excel
• Клавиши поиска в excel
• Поиск максимального значения в excel
• Поиск значений в массиве в excel
• Excel поиск по значению ячейки
• Excel поиск дубликатов
• Поиск в столбце в excel

Поиск по части слова в ячейке

​ получается, не работает​​ Янтарный замок», «Янтарный​​ всего рус/лат шрифты​ ЕСЛИ?​С автофильтром понятно​: эх, поспешил…​ не в R77​ ТОЛЬКО ОДНА -​ его опять поставить.​ber$erk​Функция =НАЙТИ() выполняет поиск​ каждой ячейки столбца​ используются функции Если,​ использовать конструкцию *яблок*.​ нажмите клавишу ESC.​​ определенным Продавец. Если​: Если инициалы убрать,​ и все тут,​ замок коньяк 0,5»,​ — не проверял)​слэн :)​ (содержит и всё​​Дело в том​

​ Поиски ЕЧИСЛО .​​Типичная формула =СЧЁТЕСЛИ($A$5:$A$11;»*МА*»)​Для выполнения этой задачи​ у вас нет​ то больше подтягивается​ не активна фффформула​ «коньяк Янтарный замок​​pabchek​: а черт его​

​ такое)​​ что я работаю​

​Himtree​​ может) ячейка содержит​​ и пишет «УПС!»​

​H5455​​ ячейке Excel. Потом​ в первом аргументе.​Примечание:​Учет регистра приводит к​ используется функция​ проблемой верхний или​

​Julia8​​ после копирования​

​ трехлетний 0,5″, «водка​​: проверил — так​

​ знает.. в процессе​​Но если 12000​

​ в бесплатном OO​​: Не хотел я​ текст в котором​Z​: А чем из​ возвращает номер символа,​

​ Ссылка в первом​​ В функции НАЙТИ учитывается​

​ необходимости создания сложных​​ЕТЕКСТ​​ нижний регистр текста,​

​:​​Как из фигурных​ казачок», «казачок водка​ и есть​

​ встречается «ИНН» и​​: Учтем специфику ВПР​ этой формулы можно​ в котором был​

​ аргументе должна быть​​ регистр букв.​ формул или использования​.​

​слэн :)​​ всё время разные,​ в нём данная​

​ файлом, ну раз​​ «КПП». А вот​ в O_o&LibO -​

​ заменить функцию ЕСЛИОШИБ.​​ найдет текст, который​​ относительной, так как​​На приведенном выше рисунке​​ дополнительных столбцов. Чаще​

​Для возвращения результатов для​​ проверки, если ячейка​

​- «думаю что​​ сразу не активна​ столбец нужно ввести​: Поправленный вариант​: а черт его​ а часть слова,​ формула (при том​​ просите, вот. В​ её расположение в​ долой заморочки с​

​ А то она​​ задан в первом​ формула применяется к​ формуле используются аргументы,​ всего используются формулы​ условия, которое можно​ содержит текст.​ надо оставлять по​ становится, и руками​ текст «10 янтарный​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Public Function MyF(Name As​ знает.. в процессе​ которую надо найти​

​ что отлично работает​​ нём три листа.​ таблице — может​ «*» и «&»!..​ отсутствует.​ аргументе. Во втором​​ каждой ячейке столбца​ указанные ниже.​

​ на основе функций​​ указать с помощью​Для поиска текста можно​ одному ключевому слову».​ пробовала перебить, но​ замок», если ячейка​ String, TypesRange, Weight​ получилось :)​ одинаковая… %)​

​ в MS 2007)​​1) Карточка результата​ меняться.​Варьянт — =IF(ISNA(VLOOKUP(A2;$A$10:$B$12;2;0));»Упс!»;VLOOKUP(A2;$A$10:$B$12;2;0))​ber$erk​

​ A. Во втором​​Формула для поиска текста​

​ функции​​ также использовать фильтр.​Вариант.​ все равно не​ содержит текст «янтарный​ As Range) As​McKey​​Pavel55​ вываливается в ошибку:​ (здесь должны отображаться​Пример содержимого: «Администрация​H5455​​: =ЕСЛИ(ЕОШИБКА(ИНДЕКС($A$10:$B$12;ПОИСКПОЗ(«*» &A2&»*»;$A$10:$A$12;0);2));»УПС!»;ИНДЕКС($A$10:$B$12;ПОИСКПОЗ(«*» &A2&»*»;$A$10:$A$12;0);2))​​ искать текст. А​

​ аргументе функции указывается​​искомая строка​​Формула массива =ИЛИ(СОВПАД(«яблоки»;A5:A11)) дает​Если​ Дополнительные сведения см.​Не по теме:​ работает​ замок» и если​ Variant​: Это конечно понятно…​: Sub Макрос1()​ #DIV/0!​ все необходимые данные,​ МО города Москвы​: К сожалению,​DV​ третий аргумент –​​ номер символа исходного​: вы хотите проверить.​​ ответ на вопрос​

​.​​ в статье Фильтрация​​В свое время на​

​Добавлено через 23 минуты​​ ячейка содержит текст​

​Dim Founded As​​ну вот какие​​Columns(«A:A»).Replace What:=»Ябло», Replacement:=»Яблоко»,​​MCH​ в пригодном для​ ИНН 0123456789 КПП​тот же УПС!​

​: Вариант:​​ это номер позиции​ текста, с которого​​Ячейка​ есть ли такой​Для выполнения этой задачи​ данных.​

planetaexcel.ru>

Как применить сводную таблицу для поиска дубликатов.

Многие считают сводные таблицы слишком сложным инструментом, чтобы постоянно им пользоваться. На самом деле, не все так запутано, как кажется. Для новичков рекомендую к ознакомлению наше руководство по созданию и работе со сводными таблицами.

Для более опытных – сразу переходим к сути вопроса.

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

Вы наблюдаете на скриншоте выше, что несколько товаров дублируются. И что нам это дает? А далее мы просто можем щелкнуть мышкой на любой из цифр, и на новом листе Excel покажет нам, как получилась эта цифра.

К примеру, откуда взялись 3 дубликата Sprite? Щелкаем на цифре 3, и видим такую картину:

Думаю, этот метод вполне можно использовать. Что приятно – никаких формул не требуется.

Обработка найденных дубликатов

Отлично, мы нашли записи в первом столбце, которые также присутствуют во втором столбце. Теперь нам нужно что-то с ними делать. Просматривать все повторяющиеся записи в таблице вручную довольно неэффективно и занимает слишком много времени. Существуют пути получше.

Показать только повторяющиеся строки в столбце А

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

Кликните правой кнопкой мыши и в контекстном меню выберите Insert (Вставить):

Дайте названия столбцам, например, «Name» и «Duplicate?» Затем откройте вкладку Data (Данные) и нажмите Filter (Фильтр):

После этого нажмите меленькую серую стрелку рядом с «Duplicate?«, чтобы раскрыть меню фильтра; снимите галочки со всех элементов этого списка, кроме Duplicate, и нажмите ОК.

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

Чтобы снова отобразить все строки столбца А, кликните символ фильтра в столбце В, который теперь выглядит как воронка с маленькой стрелочкой и выберите Select all (Выделить все). Либо Вы можете сделать то же самое через Ленту, нажав Data (Данные) > Select & Filter (Сортировка и фильтр) > Clear (Очистить), как показано на снимке экрана ниже:

Изменение цвета или выделение найденных дубликатов

Если пометки «Duplicate» не достаточно для Ваших целей, и Вы хотите отметить повторяющиеся ячейки другим цветом шрифта, заливки или каким-либо другим способом…

В этом случае отфильтруйте дубликаты, как показано выше, выделите все отфильтрованные ячейки и нажмите Ctrl+1, чтобы открыть диалоговое окно Format Cells (Формат ячеек). В качестве примера, давайте изменим цвет заливки ячеек в строках с дубликатами на ярко-жёлтый. Конечно, Вы можете изменить цвет заливки при помощи инструмента Fill (Цвет заливки) на вкладке Home (Главная), но преимущество диалогового окна Format Cells (Формат ячеек) в том, что можно настроить одновременно все параметры форматирования.

Теперь Вы точно не пропустите ни одной ячейки с дубликатами:

Удаление повторяющихся значений из первого столбца

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

Если 2 столбца, которые Вы сравниваете, находятся на разных листах, то есть в разных таблицах, кликните правой кнопкой мыши выделенный диапазон и в контекстном меню выберите Delete Row (Удалить строку):

Нажмите ОК, когда Excel попросит Вас подтвердить, что Вы действительно хотите удалить всю строку листа и после этого очистите фильтр. Как видите, остались только строки с уникальными значениями:

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

1. Отфильтруйте таблицу так, чтобы отображались только дублирующиеся значения, и выделите эти ячейки. Кликните по ним правой кнопкой мыши и в контекстном меню выберите Clear contents (Очистить содержимое).
2. Очистите фильтр.
3. Выделите все ячейки в столбце А, начиная с ячейки А1 вплоть до самой нижней, содержащей данные.
4. Откройте вкладку Data (Данные) и нажмите Sort A to Z (Сортировка от А до Я). В открывшемся диалоговом окне выберите пункт Continue with the current selection (Сортировать в пределах указанного выделения) и нажмите кнопку Sort (Сортировка):
5. Удалите столбец с формулой, он Вам больше не понадобится, с этого момента у Вас остались только уникальные значения.
6. Вот и всё, теперь столбец А содержит только уникальные данные, которых нет в столбце В:

Как видите, удалить дубликаты из двух столбцов в Excel при помощи формул – это не так уж сложно.

Пример использования функции ПОИСК и ПСТР

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

Введем исходные данные в таблицу:

В ячейке, которая будет учитывать данные клиентов без телефона, введем следующую формулу:

Нажмем Enter для отображения искомой информации:

Далее мы можем использовать любые другие функции для отображения представленной информации в удобном формате:

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

Примеры использования функции ПОИСК в Excel

Пример 1. Ищем слово внутри текстовой строки (с начала)

На примере выше видно, что когда мы ищем слово “доброе” в тексте “Доброе утро”, функция возвращает значение “1”, что соответствует позиции слова “доброе” в тексте “Доброе утро”.

Так как функция не чувствительна к регистру, нет разницы каким образом мы указываем искомое слово “доброе”, будь то “ДОБРОЕ”, “Доброе”, “дОброе” и.т.д. функция вернет одно и то же значение.

Если вам необходимо осуществить поиск чувствительный к регистру – используйте функцию НАЙТИ в Excel.

Пример 2. Ищем слово внутри текстовой строки (с указанием стартовой позиции поиска)

Третий аргумент функции указывает на порядковый номер позиции внутри текста, с которой будет осуществлен поиск. На примере выше, функция возвращает значение “1” при поиске слова “доброе” в тексте “Доброе утро”, начиная свой поиск с первой позиции.

Вместе с тем, если мы указываем функции, что поиск следует начинать со второго символа текста “Доброе утро”, то есть функция в этом случае видит текст как “оброе утро” и ищет слово “доброе”, то результатом будет ошибка.

Если вы не указываете в качестве аргумента стартовую позицию для поиска, функция автоматически начнет поиск с начала текста.

Пример 3. Поиск слова при наличии нескольких совпадений в тексте

Функция начинает искать текст со стартовой позиции которую мы можем указать в качестве аргумента, или она начнет поиск с начала текста автоматически. На примере выше, мы ищем слово “доброе ” в тексте “Доброе доброе утро” со стартовой позицией для поиска “1”. В этом случае функция возвращает “1”, так как первое найденное слово “Доброе” начинается с первого символа текста.

Если мы укажем функции начало поиска, например, со второго символа, то результатом вычисления функции будет “8”.

Пример 4. Используем подстановочные знаки при работе функции ПОИСК в Excel

При поиске функция учитывает подстановочные знаки. На примере выше мы ищем текст “c*l”

Наличие подстановочного знака “*” в данном запросе обозначает что мы ищем любо слово, которое начинается с буквы “c” и заканчивается буквой “l”, а что между этими двумя буквами не важно. Как результат, функция возвращает значение “3”, так как в слове “Excel”, расположенном в ячейке А2 буква “c” находится на третьей позиции

Советы и лайфхаки по работе с Excel

Многие сталкивались с файлами Ексель, в которых создано огромное количество листов. Чтобы найти нужный лист нужно прокрутить все созданные в книге листы.

Но есть более простой способ быстро открыть нужный лист.

Щелкните правой кнопкой мыши на кнопки прокрутки листов, которые находятся слева от названия листов и выберите нужный лист:

Способ 1. (Используем программу) Ищем в поисковике и загружаем программу

Например, мы имеем много рабочих книг Excel, и мы хотим

Нам в работе иногда не хватает стандартных возможностей Эксель и приходится напрягать

Достаточно часто при заполнении ячейки текстом, возникает необходимость ввести текст

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

Предположим, что у нас есть такая таблица с перечнем соглашений,

В Excel есть одна интересная особенность, а именно возможность вводить

Поиск значений в таблице Excel

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

Лист с таблицей для поиска значений по вертикали и горизонтали:

Над самой таблицей расположена строка с результатами. В ячейку B1 водим критерий для поискового запроса, то есть заголовок столбца или название строки. А в ячейке D1 формула поиска должна возвращать результат вычисления соответствующего значения. После чего в ячейке F1 сработает вторая формула, которая уже будет использовать значения ячеек B1 и D1 в качестве критериев для поиска соответствующего месяца.

Как в экселе найти нужное слово по ячейкам

Для отображения адресов тех ячеек, которые содержат то, что вы пытаетесь отыскать, следует придерживаться следующих шагов:

1. Если вы являетесь пользователем программы 2010 года, стоит перейти к меню, после чего кликнуть по «Правке», и затем «Найти».
2. Далее откроется окошко, в котором предстоит пропечатать искомую фразу.
3. Программа предыдущей версии располагает данной кнопкой в меню под названием «Главная», расположенная на панели редактирования.
4. Подобного же результата возможно достигать в любой из версий, одновременно воспользовавшись кнопками Ctrl, а также, F.
5. В поле следует пропечатать фразу, искомые слова либо цифры.
6. Нажав «Найти все», вы запустите поиск по абсолютно всему файлу. Кликнув «Далее», программа по одной клеточке, располагающихся под курсором-ячейкой файла, будет их выделять.
7. Стоит подождать, пока процесс завершится. При этом чем объемнее документ, тем больше времени уйдет на поиск.
8. Возникнет список результатов: имена и адреса клеточек, которые содержат в себе совпадения с указанным значением либо фразой.
9. Кликнув на любую строчку, будет выделена соответствующая ячейка.
10. С целью удобства, можно «растягивать» окно. Таким образом в нем будет виднеться больше строк.
11. Для сортировки данных, необходимо кликать на названиях столбиков над найденными результатами. Нажав на «Лист», строки будут выстроены по алфавиту зависимо от наименования листа, а выбрав «Значения» — расположатся в зависимости от значения. К слову, данные столбики тоже можно «растянуть».

Поисковые параметры

Возможно задавать собственные условия. К примеру, осуществлять поиск на основании нескольких знаков. Вот каким образом отыскать слово, которое вы не помните полностью:

1. Следует ввести лишь частичку надписи. Можно даже одну из букв – будут обозначены все участки, где она имеется.
2. Применяйте значки «звездочка», а аткже, знак вопроса. Они способны заместить пропущенные символы.
3. Вопросом обозначается одна недостающая позиция. Если, например, вы пропечатаете «А. », будут отображены ячейки, которые содержат слово из пяти символов, которое начинается с «А».
4. Благодаря звездочке, замещается любое количество знаков. Для поиска всех значений, содержащих корень «раст», следует начать искать согласно ключу «раст*».

Кроме того, вы можете посещать настройки:

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

Настройки форматирования ячеек

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

1. В окошке поиска следует кликнуть по параметрам и нажать клавишу «Формат». Будет открыто меню, содержащее несколько вкладок.
2. Можно указывать тот или иной шрифт, тип рамочки, окраску фона, а также, формат вводимых данных. Системой будут просмотрены те участки, которые соответствуют обозначенным критериям.
3. Для взятия информации из текущей клеточки (выделенной на данный момент), следует кликнуть «Использовать формат данной ячейки». В таком случае программой будут найдены все значения, обладающие тем же размером и типом символов, той же окраской, а также, теми же границами и т.п.

Есть ли слово в списке MS EXCEL

​вариант​ ее текст может​ чтобы формула и​ примеру выше. Может​ («анонс»;A1))+ЕЧИСЛО (ПОИСК («икона»;A1))+ЕЧИСЛО​Пробовали варианты с​и от этого​ найдено подставить своё​

​ вариант! Просто рассматривал​ 0 Then inn​ и их подавление…​ без него то​ поиска отсортирован по​ что, если у​

​ функциями.​​ При вводе формулы в​ значений, соответствующих критерию​Лист​ содержать неточности и​ дальше работала на​ нужен справочник промежуточный.​ (ПОИСК («суббота»;A1));A1;»»)​ функциями ПОИСК и​ уже можно плясать​ значение.​ два варианта:​ = r.Text: Exit​

​Himtree​ хоть и неправильно,​ возрастанию.​ нас в исходных​

• ​​ примере должны быть​
• ​ (см. файл примера).​
• ​или​ грамматические ошибки. Для​

Ищутся значения в точности соответствующие критерию

​ других листах?​ условие задачи: есть​PS. Прошу прощение​​ ИНДЕКС, и другие,​

​Серега​(Что-то типа агрегироанной​1) вытащить эту​

Ищутся значения содержащие часть текстовой строки

​ Function​: Добрый вечер! Битые​ но выдает числа,​Больше без примера​ данных разного типа​

​Чтобы легко понять, как​ формула массива

После​Типичный вопрос для этого​Книга​ нас важно, чтобы​Все получилось с​ два списка контрагентов:​ за повтор. Не​ соответствие не ищется.​: Сначала надо составить​ группы)​ строку со второго​Next r​ сутки не могу​

​ а с ним.​

Ищутся значения с учетом РЕгиСТрА

​ сказать трудно чего.​ индикаторы с разной​ удалось экспонировать цветом​ ввода формулы. Нажмите​ типа поиска: Есть​.​ эта статья была​

​ копированием, БЛАГОДАРЮ ВАС!!!​ список бух и​ заметил формулу Александра​ Может есть вариант​

​ таблицу соответствия​​думал такую формулу:​ листа — тогда​End Function​ решить казалось бы​ либо пустота, либо​H5455​

excel2.ru>

Поиск нескольких слов

Не зная, как найти слово в таблице в Еxcel, следует также воспользоваться функцией раздела «Редактирование» – «Найти и выделить». Далее нужно отталкиваться от искомой фразы:

• если фраза точная, введите ее и нажмите клавишу «Найти все»;
• если фраза разбита другими ключами, нужно при написании ее в строке поиска дополнительно проставить между всеми словами «*».

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

Расширенный поиск

Предположим, что требуется найти все значения в диапазоне от 3000 до 3999. В этом случае в строке поиска следует набрать 3???. Подстановочный знак «?» заменяет собой любой другой.

Анализируя результаты произведённого поиска, можно отметить, что, наряду с правильными 9 результатами, программа также выдала неожиданные, подчёркнутые красным. Они связаны с наличием в ячейке или формуле цифры 3.

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

Щёлкнув «Параметры», пользователь получает возможность осуществлять расширенный поиск

Прежде всего, обратим внимание на пункт «Область поиска», в котором по умолчанию выставлено значение «Формулы»

Это означает, что поиск производился, в том числе и в тех ячейках, где находится не значение, а формула. Наличие в них цифры 3 дало три неправильных результата. Если в качестве области поиска выбрать «Значения», то будет производиться только поиск данных и неправильные результаты, связанные с ячейками формул, исчезнут.

Для того чтобы избавиться от единственного оставшегося неправильного результата на первой строчке, в окне расширенного поиска нужно выбрать пункт «Ячейка целиком». После этого результат поиска становимся точным на 100%.

Такой результат можно было бы обеспечить, сразу выбрав пункт «Ячейка целиком» (даже оставив в «Области поиска» значение «Формулы»).

Теперь обратимся к пункту «Искать».

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

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

При поиске в документах Microsoft Excel, можно использовать и другой подстановочный знак – «*». Если рассмотренный «?» означал любой символ, то «*» заменяет собой не один, а любое количество символов. Ниже представлен скриншот поиска по слову Louisiana.

Иногда при поиске необходимо учитывать регистр символов. Если слово louisiana будет написано с маленькой буквы, то результаты поиска не изменятся. Но если в окне расширенного поиска выбрать «Учитывать регистр», то поиск окажется безуспешным. Программа станет считать слова Louisiana и louisiana разными, и, естественно, не найдёт первое из них.

Дополнительные параметры поиска слов и фраз

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

Здесь можно указать дополнительные параметры поиска.

Искать:

• на листе — только на текущем листе;
• в книге — искать во всем документе Excel, если он состоит из нескольких листов.

Просматривать:

• по строкам — искомая фраза будет искаться слева направо от одной строки к другой;
• по столбцам — искомая фраза будет искаться сверху вниз от одного столбца к другому.

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

Область поиска — определяет, где именно нужно искать совпадения:

• в формулах;
• в значениях ячеек (уже вычисленные по формулам значения);
• в примечаниях, оставленных пользователями к ячейкам.

А также дополнительные параметры:

Учитывать регистр — означает, что заглавные и маленькие буквы будут считаться как разные.

Например, если не учитывать регистр, то по запросу «excel» будет найдены все вариации этого слова, например, Excel, EXCEL, ExCeL и т.д.

Если поставить галочку учитывать регистр, то по запросу «excel» будет найдено только такое написание слова и не будет найдено слово «Excel».

• Ячейка целиком — галочку нужно ставить в том случае, если нужно найти те ячейки, в которых искомая фраза находится целиком и нет других символов. Например, есть таблица со множеством ячеек, содержащих различные числа. Поисковый запрос: «200». Если не ставить галочку ячейка целиком, то будут найдены все числа, содержащие 200, например: 2000, 1200, 11200 и т.д. Чтобы найти ячейки только с «200», нужно поставить галочку ячейка целиком. Тогда будут показаны только те, где точное совпадение с «200».
• Формат… — если задать формат, то будут найдены только те ячейки, в которых есть искомый набор символов и ячейки имеют заданный формат (границы ячейки, выравнивание в ячейке и т.д.). Например, можно найти все желтые ячейки, содержащие искомые символы.

Формат для поиска можно задать самому, а можно выбрать из ячейки-образца — Выбрать формат из ячейки…

Чтобы сбросить настройки формата для поиска нужно нажать Очистить формат поиска.

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

Как создать собственное окно поиска в Excel?

За исключением использования функции «Найти» в Excel, на самом деле вы можете создать собственное поле поиска для простого поиска необходимых значений. Эта статья подробно расскажет вам о двух методах создания собственного поля поиска в Excel.

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

1. Выберите диапазон с данными, которые вам нужны для поиска в поле поиска, затем нажмите Условное форматирование > Новое правило под Главная таб. Смотрите скриншот:

2. в Новое правило форматирования диалоговое окно, вам необходимо:

2.1) Выбрать Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант в Выберите тип правила коробка;

2.2) Введите формулу = ISNUMBER (ПОИСК ($ B $ 2; A5)) в Формат значений, где эта формула истинна коробка;

2.3) Нажмите Формат кнопка, чтобы указать выделенный цвет для искомого значения;

2.4) Нажмите OK кнопку.

Заметки:

1. В формуле $ B $ 2 — это пустая ячейка, которую необходимо использовать в качестве поля поиска, а A5 — это первая ячейка выбранного диапазона, в котором необходимо искать значения. Пожалуйста, измените их по своему усмотрению.

2. В формуле не учитывается регистр.

Теперь поле поиска создано: при вводе критериев поиска в поле поиска B2 и нажатии клавиши Enter выполняется поиск всех совпадающих значений в указанном диапазоне, которые немедленно выделяются, как показано ниже.

Создайте собственное окно поиска с формулами для вывода всех результатов поиска

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

1. Выберите пустую ячейку, которая находится рядом с ячейкой E4, здесь я выбираю ячейку D4, затем вводю формулу = ЕСЛИОШИБКА (ПОИСК ($ B $ 2; E4) + СТРОКА () / 100000; «») в строку формул, а затем нажмите Enter ключ. Смотрите скриншот:

Внимание: В формуле $ B $ 2 — это ячейка, которую необходимо использовать в качестве поля поиска, E4 — это первая ячейка списка данных, в котором необходимо выполнить поиск. Вы можете менять их по своему усмотрению

2. Продолжайте выбирать ячейку E4, затем перетащите маркер заполнения вниз в ячейку D23. Смотрите скриншот:

3. Теперь выберите ячейку C4 и введите формулу. = ЕСЛИОШИБКА (РАНГ (D4; $ D $ 4: $ D $ 23,1); «») в панель формул и нажмите Enter ключ. Выберите ячейку C4, затем перетащите маркер заполнения до C23. Смотрите скриншот:

4. Теперь вам нужно заполнить диапазон A4: A23 порядковым номером, который увеличивается на 1 от 1 до 20, как показано на скриншоте ниже:

5. Выберите пустую ячейку, в которой будет отображаться результат поиска, введите формулу. = ЕСЛИОШИБКА (ВПР (A4; $ C $ 4: $ E $ 23,3; FALSE); «») в панель формул и нажмите Enter ключ. Продолжая выбирать ячейку B4, перетащите маркер заливки вниз до B23, как показано ниже.

С этого момента при вводе данных в поле поиска B2 все совпавшие значения будут перечислены в диапазоне B4: B23, как показано на скриншоте ниже.

Внимание: этот метод не чувствителен к регистру

Обнаружение повторяющихся строк

Мы рассмотрели, как обнаружить одинаковые данные в отдельных ячейках. А если нужно искать дубликаты-строки?

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

Итак, имеются данные о товарах и заказчиках.

Создадим справа от наших данных формулу, объединяющую содержание всех расположенных слева от нее ячеек.

Предположим, что данные хранятся в столбцах А:C. Запишем в ячейку D2:

Добавим следующую формулу в ячейку E2. Она отобразит, сколько раз встречается значение, полученное нами в столбце D:

Скопируем вниз для всех строк данных.

В столбце E отображается количество появлений этой строки в столбце D. Неповторяющимся строкам будет соответствовать значение 1. Повторам строкам соответствует значение больше 1, указывающее на то, сколько раз такая строка была найдена.

Если вас не интересует определенный столбец, просто не включайте его в выражение, находящееся в D. Например, если вам хочется обнаружить совпадающие строки, не учитывая при этом значение Заказчик, уберите из объединяющей формулы упоминание о ячейке С2.