Всем добра друзья! И сегодня вы узнаете как в экселе найти нужное слово. Зачастую работа с Excel подразумевает существенные информационные объемы. В подобных файлах разрабатываются поистине громадные таблицы с тысячами строчек, столбиков, а также, позиций. Найти в таком огромном массиве какие-либо конкретные значения порой бывает невероятно тяжело.
Подобную задачу возможно упростить. Зная, каким образом отыскивать необходимое слово, можно будет без труда ориентироваться в файле. Редактор таблиц предлагает несколько вариаций поиска.
Как в экселе найти нужное слово по ячейкам
Для отображения адресов тех ячеек, которые содержат то, что вы пытаетесь отыскать, следует придерживаться следующих шагов:
- Если вы являетесь пользователем программы 2010 года, стоит перейти к меню, после чего кликнуть по «Правке», и затем «Найти».
- Далее откроется окошко, в котором предстоит пропечатать искомую фразу.
- Программа предыдущей версии располагает данной кнопкой в меню под названием «Главная», расположенная на панели редактирования.
- Подобного же результата возможно достигать в любой из версий, одновременно воспользовавшись кнопками Ctrl, а также, F.
- В поле следует пропечатать фразу, искомые слова либо цифры.
- Нажав «Найти все», вы запустите поиск по абсолютно всему файлу. Кликнув «Далее», программа по одной клеточке, располагающихся под курсором-ячейкой файла, будет их выделять.
- Стоит подождать, пока процесс завершится. При этом чем объемнее документ, тем больше времени уйдет на поиск.
- Возникнет список результатов: имена и адреса клеточек, которые содержат в себе совпадения с указанным значением либо фразой.
- Кликнув на любую строчку, будет выделена соответствующая ячейка.
- С целью удобства, можно «растягивать» окно. Таким образом в нем будет виднеться больше строк.
- Для сортировки данных, необходимо кликать на названиях столбиков над найденными результатами. Нажав на «Лист», строки будут выстроены по алфавиту зависимо от наименования листа, а выбрав «Значения» — расположатся в зависимости от значения. К слову, данные столбики тоже можно «растянуть».
Поисковые параметры
Возможно задавать собственные условия. К примеру, осуществлять поиск на основании нескольких знаков. Вот каким образом отыскать слово, которое вы не помните полностью:
- Следует ввести лишь частичку надписи. Можно даже одну из букв – будут обозначены все участки, где она имеется.
- Применяйте значки «звездочка», а аткже, знак вопроса. Они способны заместить пропущенные символы.
- Вопросом обозначается одна недостающая позиция. Если, например, вы пропечатаете «А????», будут отображены ячейки, которые содержат слово из пяти символов, которое начинается с «А».
- Благодаря звездочке, замещается любое количество знаков. Для поиска всех значений, содержащих корень «раст», следует начать искать согласно ключу «раст*».
Кроме того, вы можете посещать настройки:
- В окошке поиска следует кликнуть «Параметры».
- В разделах просмотра и области поиска, необходимо будет указать, где именно и на основании каких критериев нужно отыскивать совпадения. Можно подобрать формулы, различные примечания либо значения.
- Для различия системой строчных и прописных букв, нужно выставить галочку в поле под названием «Учитывать регистр».
- Отметив пункт «Ячейка полностью», результаты отобразятся в виде клеток, содержащих исключительно заданную поисковую фразу, и ничего кроме нее.
Настройки форматирования ячеек
Для поиска значений с конкретной заливкой либо начертанием, следует применить настройки. Итак, если слово выглядит иначе, нежели остальной текст, нужно выполнить следующее:
- В окошке поиска следует кликнуть по параметрам и нажать клавишу «Формат». Будет открыто меню, содержащее несколько вкладок.
- Можно указывать тот или иной шрифт, тип рамочки, окраску фона, а также, формат вводимых данных. Системой будут просмотрены те участки, которые соответствуют обозначенным критериям.
- Для взятия информации из текущей клеточки (выделенной на данный момент), следует кликнуть «Использовать формат данной ячейки». В таком случае программой будут найдены все значения, обладающие тем же размером и типом символов, той же окраской, а также, теми же границами и т.п.
Как найти несколько слов в Excel
В файлах Эксель можно искать ячейки по целым выражениям. Однако, введя ключ, к примеру «Красный шар», системой будет выполняться работа конкретно по данному запросу. Результаты не отобразят значения, имеющие вид «Красный сверкающий шар» либо «Красный яркий шар».
Для посика не одного, а одновременно нескольких слов, необходимо выполнить следующее:
- Пропишите их в поисковой строчке.
- Расставьте звездочки между ними. Таким образом будут найдены значения, которые содержат уазанную надпись, причем независимо от того, имеются ли между ними те или иные символы либо нет.
- Подобным методом можно задавать ключ даже с раздельными буквами.
Применяем фильтр
Итак, для того, чтобы воспользоваться фильтром, необходимо:
- Выделить определенную ячейку, содержащую данные.
- Кликнуть по главной, затем – «Сортировка», и далее – «Фильтр».
- В строчке вверху клетки будут оснащены стрелочками. Это и есть меню, которое нужно открыть.
- В текстовом поле нужно пропечатать запрос и нажать подтверждение.
- В столбике будут отображаться лишь ячейки. В которых присутствует искомая фраза.
- Для сброса результатов, в выпавшем списке следует отметить «Выделить все».
- Для отключения фильтра, заново стоит нажать по нему в сортировке.
Представленный способ вряд ли пригодится, если вы не располагаете информацией, в каком именно ряду располагается искомое вами значение.
На сегодня у меня все, теперь вы знаете как в экселе найти нужное слово. Таким образом, для поиска определенной фразы либо числа следует применять возможности, встроенные в интерфейс. Возможно также выбирать дополнительные опции поиска либо прибегнуть к фильтру.
Всем добра друзья! И сегодня вы узнаете как в экселе найти нужное слово. Зачастую работа с Excel подразумевает существенные информационные объемы. В подобных файлах разрабатываются поистине громадные таблицы с тысячами строчек, столбиков, а также, позиций. Найти в таком огромном массиве какие-либо конкретные значения порой бывает невероятно тяжело.
Подобную задачу возможно упростить. Зная, каким образом отыскивать необходимое слово, можно будет без труда ориентироваться в файле. Редактор таблиц предлагает несколько вариаций поиска.
Как в экселе найти нужное слово по ячейкам
Для отображения адресов тех ячеек, которые содержат то, что вы пытаетесь отыскать, следует придерживаться следующих шагов:
- Если вы являетесь пользователем программы 2010 года, стоит перейти к меню, после чего кликнуть по «Правке», и затем «Найти».
- Далее откроется окошко, в котором предстоит пропечатать искомую фразу.
- Программа предыдущей версии располагает данной кнопкой в меню под названием «Главная», расположенная на панели редактирования.
- Подобного же результата возможно достигать в любой из версий, одновременно воспользовавшись кнопками Ctrl, а также, F.
- В поле следует пропечатать фразу, искомые слова либо цифры.
- Нажав «Найти все», вы запустите поиск по абсолютно всему файлу. Кликнув «Далее», программа по одной клеточке, располагающихся под курсором-ячейкой файла, будет их выделять.
- Стоит подождать, пока процесс завершится. При этом чем объемнее документ, тем больше времени уйдет на поиск.
- Возникнет список результатов: имена и адреса клеточек, которые содержат в себе совпадения с указанным значением либо фразой.
- Кликнув на любую строчку, будет выделена соответствующая ячейка.
- С целью удобства, можно «растягивать» окно. Таким образом в нем будет виднеться больше строк.
- Для сортировки данных, необходимо кликать на названиях столбиков над найденными результатами. Нажав на «Лист», строки будут выстроены по алфавиту зависимо от наименования листа, а выбрав «Значения» — расположатся в зависимости от значения. К слову, данные столбики тоже можно «растянуть».
Поисковые параметры
Возможно задавать собственные условия. К примеру, осуществлять поиск на основании нескольких знаков. Вот каким образом отыскать слово, которое вы не помните полностью:
- Следует ввести лишь частичку надписи. Можно даже одну из букв – будут обозначены все участки, где она имеется.
- Применяйте значки «звездочка», а аткже, знак вопроса. Они способны заместить пропущенные символы.
- Вопросом обозначается одна недостающая позиция. Если, например, вы пропечатаете «А????», будут отображены ячейки, которые содержат слово из пяти символов, которое начинается с «А».
- Благодаря звездочке, замещается любое количество знаков. Для поиска всех значений, содержащих корень «раст», следует начать искать согласно ключу «раст*».
Кроме того, вы можете посещать настройки:
- В окошке поиска следует кликнуть «Параметры».
- В разделах просмотра и области поиска, необходимо будет указать, где именно и на основании каких критериев нужно отыскивать совпадения. Можно подобрать формулы, различные примечания либо значения.
- Для различия системой строчных и прописных букв, нужно выставить галочку в поле под названием «Учитывать регистр».
- Отметив пункт «Ячейка полностью», результаты отобразятся в виде клеток, содержащих исключительно заданную поисковую фразу, и ничего кроме нее.
Настройки форматирования ячеек
Для поиска значений с конкретной заливкой либо начертанием, следует применить настройки. Итак, если слово выглядит иначе, нежели остальной текст, нужно выполнить следующее:
- В окошке поиска следует кликнуть по параметрам и нажать клавишу «Формат». Будет открыто меню, содержащее несколько вкладок.
- Можно указывать тот или иной шрифт, тип рамочки, окраску фона, а также, формат вводимых данных. Системой будут просмотрены те участки, которые соответствуют обозначенным критериям.
- Для взятия информации из текущей клеточки (выделенной на данный момент), следует кликнуть «Использовать формат данной ячейки». В таком случае программой будут найдены все значения, обладающие тем же размером и типом символов, той же окраской, а также, теми же границами и т.п.
Как найти несколько слов в Excel
В файлах Эксель можно искать ячейки по целым выражениям. Однако, введя ключ, к примеру «Красный шар», системой будет выполняться работа конкретно по данному запросу. Результаты не отобразят значения, имеющие вид «Красный сверкающий шар» либо «Красный яркий шар».
Для посика не одного, а одновременно нескольких слов, необходимо выполнить следующее:
- Пропишите их в поисковой строчке.
- Расставьте звездочки между ними. Таким образом будут найдены значения, которые содержат уазанную надпись, причем независимо от того, имеются ли между ними те или иные символы либо нет.
- Подобным методом можно задавать ключ даже с раздельными буквами.
Применяем фильтр
Итак, для того, чтобы воспользоваться фильтром, необходимо:
- Выделить определенную ячейку, содержащую данные.
- Кликнуть по главной, затем – «Сортировка», и далее – «Фильтр».
- В строчке вверху клетки будут оснащены стрелочками. Это и есть меню, которое нужно открыть.
- В текстовом поле нужно пропечатать запрос и нажать подтверждение.
- В столбике будут отображаться лишь ячейки. В которых присутствует искомая фраза.
- Для сброса результатов, в выпавшем списке следует отметить «Выделить все».
- Для отключения фильтра, заново стоит нажать по нему в сортировке.
Представленный способ вряд ли пригодится, если вы не располагаете информацией, в каком именно ряду располагается искомое вами значение.
На сегодня у меня все, теперь вы знаете как в экселе найти нужное слово. Таким образом, для поиска определенной фразы либо числа следует применять возможности, встроенные в интерфейс. Возможно также выбирать дополнительные опции поиска либо прибегнуть к фильтру.
Функция ПОИСК (SEARCH) в Excel используется для определения расположения текста внутри какого-либо текста и указания его точной позиции.
Что возвращает функция
Функция возвращает числовое значение, обозначающее стартовую позицию искомого текста внутри другого текста. Позиция обозначает порядковый номер символа, с которого начинается искомый текст.
Синтаксис
=SEARCH(find_text, within_text, ) – английская версия
=ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция]) – русская версия
Аргументы функции
- find_text (искомый_текст) – текст или текстовая строка которую вы хотите найти;
- within_text (просматриваемый_текст) – текст, внутри которого вы осуществляете поиск;
- ([начальная_позиция]) – числовое значение, обозначающее позицию, с которой вы хотите начать поиск. Если не указать этот аргумент, то функци начнет поиск с начала текста.
Дополнительная информация
- Если стартовая позиция поиска не указана, то поиск текста осуществляется сначала текста;
- Функция не чувствительна к регистру. Если вам нужна чувствительность к регистру, то используйте функцию ;
- Функцияможет обрабатывать подстановочные знаки. В Excel существует три подстановочных знака – ?, *, ~.
- знак “?” – сопоставляет любой одиночный символ;
- знак “*” – сопоставляет любые дополнительные символы;
- знак “~” – используется, если нужно найти сам вопросительный знак или звездочку.
- Функция возвращает ошибку, в случае если искомый текст не найден.
Примеры использования функции ПОИСК в Excel
Пример 1. Ищем слово внутри текстовой строки (с начала)
На примере выше видно, что когда мы ищем слово “доброе” в тексте “Доброе утро”, функция возвращает значение “1”, что соответствует позиции слова “доброе” в тексте “Доброе утро”.
Так как функция не чувствительна к регистру, нет разницы каким образом мы указываем искомое слово “доброе”, будь то “ДОБРОЕ”, “Доброе”, “дОброе” и.т.д. функция вернет одно и то же значение.
Если вам необходимо осуществить поиск чувствительный к регистру – используйте функцию в Excel.
Пример 2. Ищем слово внутри текстовой строки (с указанием стартовой позиции поиска)
Третий аргумент функцииуказывает на порядковый номер позиции внутри текста, с которой будет осуществлен поиск. На примере выше, функция возвращает значение “1” при поиске слова “доброе” в тексте “Доброе утро”, начиная свой поиск с первой позиции.
Вместе с тем, если мы указываем функции, что поиск следует начинать со второго символа текста “Доброе утро”, то есть функция в этом случае видит текст как “оброе утро” и ищет слово “доброе”, то результатом будет ошибка.
Если вы не указываете в качестве аргумента стартовую позицию для поиска, функция автоматически начнет поиск с начала текста.
Пример 3. Поиск слова при наличии нескольких совпадений в тексте
Функцияначинает искать текст со стартовой позиции которую мы можем указать в качестве аргумента, или она начнет поиск с начала текста автоматически. На примере выше, мы ищем слово “доброе ” в тексте “Доброе доброе утро” со стартовой позицией для поиска “1”. В этом случае функция возвращает “1”, так как первое найденное слово “Доброе” начинается с первого символа текста.
Если мы укажем функции начало поиска, например, со второго символа, то результатом вычисления функции будет “8”.
Пример 4. Используем подстановочные знаки при работе функции ПОИСК в Excel
При поиске функция учитывает подстановочные знаки. На примере выше мы ищем текст “c*l”. Наличие подстановочного знака “*” в данном запросе обозначает что мы ищем любо слово, которое начинается с буквы “c” и заканчивается буквой “l”, а что между этими двумя буквами не важно. Как результат, функция возвращает значение “3”, так как в слове “Excel”, расположенном в ячейке А2 буква “c” находится на третьей позиции.
Добрый день, уважаемые хаброжители!
Время от времени некоторым (а может и более, чем некоторым) из нас приходится сталкиваться с задачами по обработке небольших массивов данных, начиная от составления и анализа домашнего бюджета и заканчивая какими-либо расчетами по работе, учебе и т.д. Пожалуй, наиболее подходящим инструментом для этого является Microsoft Excel (или возможно иные его аналоги, но они менее распространены).
Поиск выдал мне всего одну статью на Хабре по схожей тематике - «Талмуд по формулам в Google SpreadSheet» . В ней дано хорошее описание базовых вещей для работы в excel (хотя он и не 100% про сам excel).
Таким образом, накопив определенный пул запросов/задач, появилась идея их типизировать и предложить возможные решения (пусть не все возможные, но быстро дающие результат).
Речь пойдет о решении наиболее распространенных задач, с которыми сталкиваются пользователи.
Описание решений построено следующим образом – дается кейс, содержащий исходное задание, которое постепенно усложняется, к каждому шагу дано развернутое решение с пояснениями. Наименования функций будут даваться на русском языке, но в скобках при первом упоминании будет приводиться оригинальное наименование на английском языке (т.к. по опыту у подавляющего большинства пользователей установлена русскоязычная версия).
Кейс_1: Логические функции и функции поиска совпадений
«У меня есть набор значений в табличке и необходимо что бы при выполнении определенного условия/набора условий выводилось определенное значение» (с) Пользователь
Данные, как правило, представлены в табличной форме:
Условие:
- если значение в столбце «Количество» больше 5,
- то нужно вывести в колонке «Результат» значение «Заказ не требуется»,
Синтаксис формулы следующий:
ЕСЛИ(лог_выражение, [значение_если_истина], [значение_если_ложь])
- Лог_выражение - выражение, дающее в результате значение ИСТИНА или ЛОЖЬ.
- Значение_если_истина - значение, которое выводится, если логическое выражение истинно
- Значение_если_ложь - значение, которое выводится, если логическое выражение ложно
=ЕСЛИ(C5>5;«Заказ не требуется»;«Необходим заказ»)
На выходе получаем результат:
Бывает, что условие носит более сложный характер, например выполнение 2-х и более условий:
- если значение в столбце «Количество» больше 5, а значение в колонке «Тип» равно «А»
Синтаксис формулы следующий:
И(логическое_значение1, [логическое_значение2], ...)
- Логическое_значение1-2 и т.д. - проверяемое условие, вычисление которого дает значение ИСТИНА или ЛОЖЬ
Вывод результата в ячейку D2:
=ЕСЛИ(И(C2>5;B2=«А»);1;0)
Таким образом, используя сочетание 2-х формул, мы находим решение нашей задачи и получаем результат:
Попробуем усложнить задачу – новое условие:
- если значение в столбце «Количество» равно 10, а значение в колонке «Тип» равно «А»
- или же значение в столбце «Количество» больше или равно 5, а значение «Тип» равен «Б»
- то нужно вывести в колонке «Результат» значение «1», в обратном случае «0».
Вывод результата в ячейку D2:
=ЕСЛИ(ИЛИ(И(C2=10;B2=«А»); И(C2>=5;B2=«Б»));1;0)
Как видно из записи, в формулу «ЕСЛИ» включено одно условие «ИЛИ» (OR) и два условия с использованием формулы «И», включенных в него. Если хотя бы одно из условий 2-го уровня имеет значение «ИСТИНА», то в колонку «Результат» будет выведен результат «1», в противном случае будет «0».
Результат:
Теперь перейдем к следующей ситуации:
Представим, что в зависимости от значения в столбце «Условие» должно выводиться определенное условие в столбце «Результат», ниже приведено соответствие значений и результата.
Условие:
- 1 = А
- 2 = Б
- 3 = В
- 4 = Г
=ЕСЛИ(A2=1;«А»; ЕСЛИ(A2=2;«Б»; ЕСЛИ(A2=3;«В»; ЕСЛИ(A2=4;«Г»;0))))
Результат:
Как видно, написание подобной формулы не только не очень удобно и громоздко, но и может занять некоторое время на ее редактирование у неопытного пользователя в случае ошибки.
Минус подобного подхода в том, что он применим для небольшого количества условий, ведь, все их придется набирать вручную и «раздувать» нашу формулу до больших размеров, однако подход отличает полная «всеядность» к значениям и универсальность использования.
Альтернативное решение_1:
Использование формулы «ВЫБОР» (CHOOSE),
Синтаксис функции:
ВЫБОР(номер_индекса, значение1, [значение2], ...)
- Номер_индекса - номер выбираемого аргумента-значения. Номер индекса должен быть числом от 1 до 254, формулой или ссылкой на ячейку, содержащую число в диапазоне от 1 до 254.
- Значение1, значение2,… - значение от 1 до 254 аргументов-значений, из которых функция «ВЫБОР», используя номер индекса, выбирает значение или выполняемое действие. Аргументы могут быть числами, ссылками на ячейки, определенными именами, формулами, функциями или текстом.
Условие:
- 1 = А
- 2 = Б
- 3 = В
- 4 = Г
=ВЫБОР(A2;«А»;«Б»;«В»;«Г»)
Результат аналогичен решению с цепочкой функций «ЕСЛИ» выше.
При применении этой формулы существуют следующие ограничения:
В ячейку «А2» (номер индекса) могут быть указаны только цифры, а значения результата будут выводиться в порядке возрастания от 1 до 254 значений.
Иными словами, функция будет работать только если в ячейке «А2» указаны цифры от 1 до 254 в порядке возрастания и это накладывает определенные ограничения при использовании этой формулы.
Т.е. если мы захотим, что бы значение «Г» выводилось при указании числа 5,
- 1 = А
- 2 = Б
- 3 = В
- 5 = Г
Вывод результата в ячейку B2:
=ВЫБОР(A31;«А»;«Б»;«В»;;«Г»)
Как видно, значение «4» в формуле нам приходится оставить пустым и перенести результат «Г» на порядковый номер «5».
Альтернативное решение_2:
Вот мы и подошли к одной из самых популярных функций Excel, овладение которой автоматически превращает любого офисного работника в «опытного пользователя excel» /sarcasm/.
Синтаксис формулы:
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
- Искомое_значение – значение, поиск которого осуществляется функцией.
- Таблица – диапазон ячеек, содержащий данные. Именно в этих ячейках будет происходить поиск. Значения могут быть текстовыми, числовыми или логическими.
- Номер_столбца - номер столбца в аргументе «Таблица», из которого будет выводиться значение в случае совпадения. Важно понимать, что отсчет столбцов происходит не по общей сетке листа (A.B,C,D и т.д.), а внутри массива, указанного в аргументе «Таблица».
- Интервальный_просмотр - определяет, какое совпадение должна найти функция - точное или приблизительное.
Суть подхода в создании «справочника» соответствия аргумента «Искомое_значение» определенному результату, отдельно от основного массива, в котором прописываются условия и соответствующие им значения:
Затем в рабочей части таблицы уже прописывается формула со ссылкой на справочник, заполненный ранее. Т.е. в справочнике в столбце «D» происходит поиск значения из столбца «А» и при нахождении соответствия выводится значение из столбца «Е» в столбец «В».
Синтаксис формулы:
Вывод результата в ячейку B2:
Результат :
Теперь представим ситуацию, когда необходимо подтянуть данные в одну таблицу из другой, при этом таблицы не идентичны. См. пример ниже
Видно, что строки в столбцах «Продукт» обеих таблиц не совпадают, однако, это не является препятствием для использования функции «ВПР».
Вывод результата в ячейку B2:
Но при решении сталкиваемся с новой проблемой – при «протягивании» написанной нами формулы вправо от столбца «В» до столбца «Е», нам придется вручную заменять аргумент «номер_столбца». Дело это трудоемкое и неблагодарное, потому, на помощь нам приходит другая функция - «СТОЛБЕЦ» (COLUMN).
Синтаксис функции:
СТОЛБЕЦ([ссылка])
- Ссылка - ячейка или диапазон ячеек, для которых требуется возвратить номер столбца.
то функция выведет номер текущего столбца (в ячейке которого написана формула).
В результате получается число, которое можно использовать в функции «ВПР», чем мы и воспользуемся и получаем следующую запись формулы:
Вывод результата в ячейку B2:
=ВПР($A3;$H$3:$M$6; СТОЛБЕЦ();0)
Функция «СТОЛБЕЦ» определит номер текущего столбца, который будет использоваться аргументом «Номер_столбца» для определения номера столбца поиска в справочнике.
Кроме того, можно использовать конструкцию:
Вместо числа «1» можно использовать любое число (а также не только вычитать его, но и прибавлять к полученному значению), для получения желаемого результата, если нет желания ссылаться на определенную ячейку в столбце с нужным нам номером.
Получившийся результат:
Продолжаем развивать тему и усложняем условие: представим, что у нас есть два справочника с разными данными по продуктам и необходимо вывести в таблицу с результатом значения в зависимости от того, какой тип справочника указан в колонке «Справочник»
Условие:
- Если в столбце «Справочник» указано число 1, данные должны тянуться из таблицы «Справочник_1», если число 2, то из таблицы «Справочник_2» в соответствии с указанным месяцем
Вариант решения, который сразу приходит на ум, следующий:
=ЕСЛИ($B3=1; ВПР($A3;$G$3:$I$6; СТОЛБЕЦ()-1;0); ВПР($A3;$K$3:$M$6; СТОЛБЕЦ()-1;0))
Плюсы : наименование справочника может быть любым (текст, цифры и их сочетание), минусы – плохо подходит, если вариантов более 3-х.
Если же номера справочников всегда представляют собой числа, имеет смысл использовать следующее решение:
Вывод результата в ячейку C3:
=ВПР($A3; ВЫБОР($B3;$G$3:$I$6;$K$3:$M$6); СТОЛБЕЦ()-1;0)
Плюсы : формула может включать до 254 наименований справочников, минусы – их наименование должно быть строго числовым.
Результат для формулы с использованием функции «ВЫБОР»:
Бонус: ВПР по двум и более признакам в аргументе «искомое_значение».
Условие:
- Представим, что у нас как всегда есть массив данных в табличной форме (если нет, то мы к нему приводим данные), из массива по определенным признакам необходимо получить значения и поместить их в другую табличную форму.
Как видно из табличных форм, каждая позиция имеет не только наименование (которое не является уникальным), но также и относится к определенному классу и имеет свой вариант фасовки.
Используя сочетание имени и класса и фасовки, мы можем создать новый признак, для этого в таблице с данными создаем дополнительный столбец «Доп.признак», который заполняем при помощи следующей формулы:
Используя символ «&», объединяем три признака в один (разделитель между словами может быть любым, как и не быть вовсе, главное использовать аналогичное правило и для поиска)
Аналогом формулы может быть функция «СЦЕПИТЬ» (CONCATENATE), в этом случае она будет выглядеть следующим образом:
=СЦЕПИТЬ(H3;"_";I3;"_";J3)
После того, как дополнительный признак создан для каждой записи в таблице с данными, приступаем к написанию функции поиска по этому признаку, которая будет иметь вид:
Вывод результата в ячейку D3:
=ЕСЛИОШИБКА(ВПР(A2&"_"&B2&"_"&C2;$G$2:$K$6;5;0);0)
В функции «ВПР» в качестве аргумента «искомое_значение» используем все ту же связку трех признаков (наименование_класс_фасовка), но берем ее уже в таблице для заполнения и заносим непосредственно в аргумент (как вариант, можно было бы выделить значение для аргумента в дополнительный столбец в таблице для заполнения, но это действие будет излишним).
Напоминаю, что использование функции «ЕСЛИОШИБКА» (IFERROR) необходимо, если искомое значение так и не будет найдено, и функция «ВПР» выведет нам значение «#Н/Д» (об этом ниже).
Результат на картинке ниже:
Данный прием можно использовать и для большего количества признаков, единственное условие – уникальность получаемых комбинаций, если она не соблюдается, то результат будет некорректным.
Кейс_3 Поиск значения в массиве, или когда ВПР не в силах нам помочь
Рассмотрим ситуацию, когда необходимо понять, есть ли в массиве ячеек нужные нам значения.
Задача:
- в столбце «Условие поиска» указано значение и необходимо определить, присутствует ли оно в столбце «Массив для поиска»
Как мы видим, функция «ВПР» тут бессильна, т.к. мы ищем не точное совпадение, а именно наличие в ячейке нужного нам значения.
Для решения задачи необходимо использовать комбинацию нескольких функций, а именно:
«ЕСЛИ»
«ЕСЛИОШИБКА»
«СТРОЧН»
«НАЙТИ»
По порядку обо всех, «ЕСЛИ» мы уже разобрали ранее, потому перейдем к функции «ЕСЛИОШИБКА» (IFERROR)
ЕСЛИОШИБКА(значение, значение_при_ошибке)
- Значение - аргумент, проверяемый на возникновение ошибок.
- Значение_при_ошибке - значение, возвращаемое при ошибке при вычислении по формуле. Возможны следующие типы ошибок: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО!.
«СТРОЧН» (LOWER)
- Текст - текст, преобразуемый в нижний регистр.
Роль в формуле: поскольку функция «НАЙТИ» (FIND) осуществляет поиск и учетом регистра текста, то необходимо привести весь текст к одному регистру, в противном случае «чАй» будет не равно «чай» и т.д. Это актуально, если значение регистра не является условием поиска и отбора значений, в противном случае формулу «СТРОЧН» можно не использовать, так поиск будет более точным.
Теперь подробнее о синтаксисе функции «НАЙТИ» (FIND).
НАЙТИ(искомый_текст, просматриваемый_текст, [нач_позиция])
- Искомый_текст - текст, который необходимо найти.
- Просматриваемый_текст - текст, в котором нужно найти искомый текст.
- Нач_позиция - знак, с которого нужно начать поиск. Первый знак в тексте «просматриваемый_текст» имеет номер 1. Если номер не указан, он по умолчанию считается равным 1.
Вывод результата в ячейку B2:
=ЕСЛИ(ЕСЛИОШИБКА(НАЙТИ(СТРОЧН(A2); СТРОЧН(E2);1);0)=0;«fail»;«bingo!»)
Разберем логику формулы по действиям:
- СТРОЧН(A2) – преобразует аргумент «Искомый_текст» в ячейке в А2 в текст с нижним регистром
- Функция «НАЙТИ» начинает поиск преобразованного аргумента «Искомый_текст» в массиве «Просматриваемый_текст», который преобразовывается функцией «СТРОЧН(E2)», также в текст с нижним регистром.
- В случае если, функция находит совпадение, т.е. возвращает порядковый номер первого символа совпадающего слова/значения, срабатывает условие ИСТИНА в формуле «ЕСЛИ», т.к. полученное значение не равно нулю. Как результат, в столбце «Результат» будет выведено значение «Bingo!»
- Если же, функция не находит совпадение т.е. порядковый номер первого символа совпадающего слова/значения не указывается и вместо значения возвращается ошибка, срабатывает условие, заложенное в формулу «ЕСЛИОШИБКА» и возвращается значение равное «0», что соответствует условию ЛОЖЬ в формуле «ЕСЛИ», т.к. полученное значение равно «0». Как результат, в столбце «Результат» будет выведено значение «fail».
Как видно из рисунка выше, благодаря функциям «СТРОЧН» и «НАЙТИ» мы находим искомые значения вне зависимости от регистра символов, и места нахождения в ячейке, но необходимо обратить внимание на строку 5.
Условие поиска задано как «111», но в массиве поиска указано значение «1111111 печенюшки», однако формула выдает результат «Bingo!». Это происходит потому, что значение «111» входит в ряд значений «1111111», как следствие находится совпадение. В обратном случае данное условие не сработает.
Кейс_4 Поиск значения в массиве по нескольким условиям, или когда ВПР тем более не в силах нам помочь
Представим ситуацию, когда необходимо найти значение из «Таблица с результатом» в двумерном массиве «Справочник» по нескольким условиям, а именно по значению «Наименование» и «Месяц».
Табличная форма задания будет иметь следующий вид:
Условие:
- В таблицу с результатом необходимо подтянуть данные в соответствии с совпадением условий «Наименование» и «Месяц».
Синтаксис функции «ИНДЕКС» (INDEX)
ИНДЕКС(массив, номер_строки, [номер_столбца])
- Массив - диапазон ячеек, из которого будут показываться значения в случае совпадения условий их поиска.
- Если массив содержит только одну строку или один столбец, аргумент «номер_строки» или «номер_столбца» соответственно не является обязательным.
- Если массив занимает больше одной строки и одного столбца, а из аргументов «номер_строки» и «номер_столбца» задан только один, то функция «ИНДЕКС» возвращает массив, состоящий из целой строки или целого столбца аргумента «массив».
- Номер_строки - номер строки в массиве, из которой требуется возвратить значение.
- Номер_столбца - номер столбца в массиве, из которого требуется возвратить значение.
Синтаксис функции «ПОИСКПОЗ» (MATCH)
ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])
- Искомое_значение - значение, которое сопоставляется со значениями в аргументе просматриваемый_массив. Аргумент искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую такое значение.
- Просматриваемый_массив - диапазон ячеек, в которых производится поиск.
- Тип_сопоставления - необязательный аргумент. Число -1, 0 или 1.
Суть использования комбинации функций «ИНДЕКС» и «ПОИСКПОЗ» в том, то мы производим поиск координат значений по их наименованию по «осям координат».
Осью Y будет столбец «Наименование», а осью X – строка «Месяцы».
Часть формулы:
ПОИСКПОЗ($A4;$I$4:$I$7;0)возвращает число по оси Y, в данном случае оно будет равно 1, т.к. значение «А» присутствует в искомом диапазоне и имеет относительную позицию «1» в этом диапазоне.
часть формулы:
ПОИСКПОЗ(B$3;$J$3:$L$3;0)возвращает значение #Н/Д, т.к. значение «1» отсутствует в просматриваемом диапазоне.
Таким образом, мы получили координаты точки (1; #Н/Д) которые функция «ИНДЕКС» использует для поиска в аргументе «Массив».
Полностью написанная функция для ячейки B4 будет иметь следующий вид:
=ИНДЕКС($J$4:$L$7; ПОИСКПОЗ($A4;$I$4:$I$7;0); ПОИСКПОЗ(B$3;$J$3:$L$3;0))
По сути, если бы мы знали координаты нужного нам значения, функция выглядела бы следующим образом:
=ИНДЕКС($J$4:$L$7;1;#Н/Д))
Поскольку, аргумент «Номер_столбца» имеет значение «#Н/Д», то результат для ячейки «B4» будет соответствующий.
Как видно из получившегося результата не все значения в таблице с результатом находят совпадение со справочником и в итоге мы видим, что часть значений в таблице выводится в виде «#Н/Д», что затрудняет использование данных для дальнейших расчетов.
Результат:
Что бы нейтрализовать этот негативный эффект используем функцию «ЕСЛИОШИБКА», о которой мы читали ранее, и заменяем значение, возвращающееся при ошибке на «0», тогда формула будет иметь вид:
=ЕСЛИОШИБКА(ИНДЕКС($J$4:$L$7; ПОИСКПОЗ($A4;$I$4:$I$7;0); ПОИСКПОЗ(B$3;$J$3:$L$3;0));0)
Демонстрация результата:
Как видно на картинке, значения «#Н/Д» более не мешают нам в последующих вычислениях с использованием значений в таблице с результатом.
Кейс_5 Поиск значения в диапазоне чисел
Представим, что нам необходимо дать определенный признак числам, входящим в определенный диапазон.
Условие:
В зависимости от стоимости продукта ему должна присваиваться определенная категория
Если значение находится в диапазоне
- От 0 до 1000 = А
- От 1001 до 1500 = Б
- От 1501 до 2000 = В
- От 2001 до 2500 = Г
- Более 2501 = Д
Функция ПРОСМОТР (LOOKUP) возвращает значение из строки, столбца или массива. Функция имеет две синтаксических формы: векторную и форму массива.
ПРОСМОТР(искомое_значение; просматриваемый_вектор; [вектор_результатов])
- Искомое_значение - значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.
- Просматриваемый_вектор - диапазон, состоящий из одной строки или одного столбца. Значения в аргументе просматриваемый_вектор могут быть текстом, числами или логическими значениями.
- Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат. Текст в нижнем и верхнем регистрах считается эквивалентным.
- Вектор_результатов - диапазон, состоящий из одной строки или столбца. Вектор_результатов должен иметь тот же размер, что и просматриваемый_вектор.
=ПРОСМОТР(E3;$A$3:$A$7;$B$3:$B$7)
Аргументы «Просматриваемый_вектор» и «Вектор_результата» можно записать в форме массива – в этом случае не придется выводить их в отдельную таблицу на листе Excel.
В этом случае функция будет выглядеть следующим образом:
Вывод результата в ячейку B3:
=ПРОСМОТР(E3;{0;1001;1501;2001;2501};{«А»;«Б»;«В»;«Г»;«Д»})
Кейс_6 Суммирование чисел по признакам
Для суммирования чисел по определенным признакам можно использовать три разных функции:
СУММЕСЛИ (SUMIF) – суммирует только по одному признаку
СУММЕСЛИМН (SUMIFS) – суммирует по множеству признаков
СУММПРОИЗВ (SUMPRODUCT) – суммирует по множеству признаков
Существует также вариант с использованием «СУММ» (SUM) и функции формулы массивов, когда формула «СУММ» возводится в массив:
({=СУММ(()*())}
но такой подход довольно неудобен и полностью перекрывается по функционалу формулой «СУММПРОИЗВ»
Теперь подробнее по синтаксису «СУММПРОИЗВ»:
СУММПРОИЗВ(массив1, [массив2], [массив3],...)
- Массив1 - первый массив, компоненты которого нужно перемножить, а затем сложить результаты.
- Массив2, массив3… - от 2 до 255 массивов, компоненты которых нужно перемножить, а затем сложить результаты.
- Найти общую сумму по стоимости отгрузок по каждому из продуктов за определенный период:
Как видно из таблицы с данными, что бы посчитать стоимость необходимо цену умножить на количество, а полученное значение, применив условия отбора переносить в таблица с результатом.
Однако, формула «СУММПРОИЗ» позволяет проводить такие расчеты внутри формулы.
Вывод результата в ячейку B4:
=СУММПРОИЗВ(($A4=$H$3:$H$11)*($K$3:$K$11>=B$3)*($K$3:$K$11
Разберем формулу по частям:
– задаем условие по отбору в столбце «Наименование» таблицы с данными по столбцу «Наименование» в таблице с результатом($K$3:$K$11>=B$3)*($K$3:$K$11– задаем условие по временным рамкам, дата больше или равна первого числа текущего месяца, но меньше первого числа месяца следующего. Аналогично – условие в таблице с результатом, массив – в таблице с данными.
– перемножаем столбцы «Количество» и «Цена» в таблице с данными.
Несомненным плюсом данной функции является свободный порядок записи условий, их можно записывать в любом порядке, на результат это не повлияет.
Результат:Теперь усложним условие и добавим требование, что бы отбор по наименованию «печеньки» происходил только по классам «малые» и «большие», а по наименованию «булки» все, кроме по классу «с джемом»:
Вывод результата в ячейку B4:
=СУММПРОИЗВ(($A4=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11
В формуле для отбора по печенькам добавилось новое условие:(($I$3:$I$11=«малые»)+($I$3:$I$11=«большие»))– как видно, два или более условия по одному столбцу выделяются в отдельную группу при помощи символа «+» и заключения условий в дополнительные скобки.
В формуле для отбора по булкам также добавилось новое условие:=СУММПРОИЗВ(($A5=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11«с джемом»);($L$3:$L$11)*($K$3:$K$11))
это:($I$3:$I$11<>«с джемом»)– на самом деле, в данной формуле можно было написать условие отбора также как и при отборе по печенькам, но тогда, пришлось бы перечислять три условия в формуле, в данном случае, проще написать исключение – не равно «с джемом» для этого используем значение «<>».
Вообще, если группы признаков/классов заранее известны, то лучше объединять их в эти группы, создавая справочники, чем записывать все условия в функцию, раздувая ее.
Результат:Что ж, вот мы и подошли к концу нашего краткого мануала, который на самом деле мог бы быть намного больше, но целью было все-таки дать решение наиболее встречающихся ситуаций, а не описывать решение частных (но гораздо более интересных случаев).
Надеюсь, что мануал поможет кому-нибудь в решении задач при помощи Excel, ведь это будет значить, что мой труд не пропал зря!Спасибо за уделенное время!
В Microsoft Excel часто работают с большими объёмами информации. В нём создаются огромные таблицы на тысячи строк , столбцов и позиций. Отыскать какие-то конкретные данные в таком массиве бывает трудно. А иногда - вообще невозможно. Эту задачу можно упростить. Разберитесь, как в Экселе найти нужное слово. Так вам легче будет ориентироваться в документе. И вы сможете быстро перейти к искомой информации.
Чтобы отобразились адреса всех ячеек , в которых есть то, что вы ищите, сделайте следующее:
- Если у вас Office 2010, перейдите в Меню - Правка - Найти.
- Откроется окно с полем для ввода. Напишите в нём фразу для поиска.
- В Excel 2007 эта кнопка есть в меню «Главная» на панели «Редактирование». Она находится справа.
- Аналогичного результата во всех версиях можно добиться, если нажать клавиши Ctrl+F.
- В поле напечатайте слово, фразу или цифры, которые надо отыскать.
- Нажмите «Найти всё», чтобы запустить поиск по целому документу. Если кликните «Далее», программа будет по одной выделять клетки, которые находятся ниже курсора-ячейки Excel.
- Подождите, пока процесс закончится. Чем больше документ, тем дольше система будет искать.
- Появится список с результатами: именами и адресами клеток, в которых есть совпадения с заданной фразой, и текстом, который в них написан.
- При клике на каждую строчку будет выделяться соответствующая ячейка.
- Для удобства можете «растянуть» окно. Так в нём будет видно больше строчек.
- Чтобы отсортировать данные , кликайте на названия столбцов над результатами поиска. Если нажмёте на «Лист», строчки выстроятся по алфавиту в зависимости от названия листа, если выберите «Значения» - встанут по значению.
- Эти столбцы также «растягиваются».
Можете задать свои условия. Например, запустить поиск по нескольким знакам. Вот как в Экселе найти слово, которое вы не помните целиком:
- Введите только часть надписи. Можно хоть одну букву - будут выделены все места, в которых она есть.
- Используйте символы * (звёздочка) и? (вопросительный знак). Они замещают пропущенные знаки.
- Вопрос обозначает одну отсутствующую позицию. Если вы напишите, к примеру, «П???», отобразятся ячейки, в которых есть слово из четырёх символов, начинающееся на «П»: «Плуг», «Поле», «Пара» и так далее.
- Звезда (*) замещает любое количество знаков. Чтобы отыскать все значения, в которых содержится корень «раст», начните поиск по ключу «*раст*».
Также вы можете зайти в настройки:
- В окне «Найти» нажмите «Параметры».
- В разделах «Просматривать» и «Область поиска», укажите, где и по каким критериям надо искать совпадения. Можно выбрать формулы, примечания или значения.
- Чтобы система различала строчные и прописные буквы, поставьте галочку в «Учитывать регистр».
- Если вы отметите пункт «Ячейка целиком», в результатах появятся клетки, в которых есть только заданная поисковая фраза и ничего больше.
Параметры формата ячеек
Чтобы отыскать значения с определённой заливкой или начертанием, используйте настройки. Вот как найти в Excel слово, если оно имеет отличный от остального текста вид:
- В окне поиска нажмите «Параметры» и кликните на кнопку «Формат». Откроется меню с несколькими вкладками.
- Можете указать определённый шрифт, вид рамки, цвет фона, формат данных. Система будет просматривать места, которые подходят к заданным критериям.
- Чтобы взять информацию из текущей клетки (выделенной в этот момент), нажмите «Использовать формат этой ячейки». Тогда программа отыщет все значения, у которых тот же размер и вид символов, тот же цвет, те же границы и тому подобное.
Поиск нескольких слов
В Excel можно отыскать клетки по целым фразам. Но если вы ввели ключ «Синий шар», система будет работать именно по этому запросу. В результатах не появятся значения с «Синий хрустальный шар» или «Синий блестящий шар».
Чтобы в Экселе найти не одно слово, а сразу несколько, сделайте следующее:
- Напишите их в строке поиска.
- Поставьте между ними звёздочки. Получится «*Текст* *Текст2* *Текст3*». Так отыщутся все значения, содержащие указанные надписи. Вне зависимости от того, есть ли между ними какие-то символы или нет.
- Этим способом можно задать ключ даже с отдельными буквами.
Фильтр
Вот как искать в Экселе, используя фильтр:
- Выделите какую-нибудь заполненную ячейку.
- Нажмите Главная - Сортировка - Фильтр.
- В верхней строке у клеток появятся стрелочки. Это выпадающее меню. Откройте его.
- В текстовом поле введите запрос и нажмите «OK».
- В столбце будут отображаться только ячейки, содержащие искомую фразу.
- Чтобы сбросить результаты, в выпадающем списке отметьте «Выделить всё».
- Чтобы отключить фильтр, повторно нажмите на него в сортировке.
Этот способ не подойдёт, если вы не знаете, в каком ряду нужное вам значение.
Чтобы найти в Excel какую-то фразу или число используйте встроенные возможности интерфейса. Можно выбрать дополнительные параметры поиска и включить фильтр.