Поиск значения в столбце и строке таблицы Excel
Имеем таблицу, в которой записаны объемы продаж определенных продуктов в различных месяцах. Нужно в таблице отыскать данные, а аспектом поиска будут заглавия строк и столбцов. Но поиск должен быть выполнен раздельно по спектру строчки либо столбца. Другими словами будет употребляться лишь один из критериев. Потому тут недозволено применить функцию ИНДЕКС, а нужна особая формула.
Поиск значений в таблице Excel
Для решения данной задачки проиллюстрируем пример на схематической таблице, которая соответствует выше описанным условиям.
Лист с таблицей для поиска значений по вертикали и горизонтали:
Над самой таблицей размещена строчка с плодами. В ячейку B1 водим аспект для запроса в поисковике, другими словами заголовок столбца либо заглавие строчки. А в ячейке D1 формула поиска обязана возвращать итог вычисления соответственного значения. Опосля чего же в ячейке F1 сработает 2-ая формула, которая уже будет употреблять значения ячеек B1 и D1 в качестве критериев для поиска соответственного месяца.
Поиск значения в строке Excel
Сейчас узнаем, в каком наивысшем объеме и в каком месяце была наибольшая продажа Продукта 4.
Чтоб выполнить поиск по столбцам следует:
- В ячейку B1 введите значение Продукта 4 – заглавие строчки, которое выступит в качестве аспекта.
- В ячейку D1 введите последующую формулу:
- Для доказательства опосля ввода формулы нажмите комбинацию жарких кнопок CTRL+SHIFT+Enter, потому что формула обязана быть выполнена в массиве. Если все изготовлено верно, в строке формул покажутся фигурные скобки.
- В ячейку F1 введите вторую формулу:
- Опять Для доказательства нажмите комбинацию кнопок CTRL+SHIFT+Enter.
Найдено в каком месяце и какая была большая продажа Продукта 4 в протяжении 2-ух кварталов.
Принцип деяния формулы поиска значения в строке Excel:
В первом аргументе функции ВПР (Вертикальный ПРосмотр) указывается ссылка на ячейку где находится аспект поиска. Во 2-м аргументе указывается спектр ячеек для просмотра в процессе поиска. В 3-ем аргументе функции ВПР должен указываться номер столбца, из которого следует взять значение на против строчки с именованием Продукт 4. Но потому что нам заблаговременно не известен этот номер мы при помощи функции СТОЛБЕЦ создаем массив номеров столбцов для спектра B4:G15.
Это дозволяет функции ВПР собрать целый массив значений. В итоге в памяти хранится все надлежащие значения любому столбцу по строке Продукт 4 (а конкретно: 360; 958; 201; 605; 462; 832). Опосля чего же функции МАКС остается лишь взять из этого массива наибольшее число и вернуть в качестве значения для ячейки D1, как итог вычисления формулы.
Как видно система формулы ординарна и лаконична. На ее базе можно в схожий метод отыскивать для определенного продукта и остальные характеристики. К примеру, малое либо среднее значение размера продаж используя для этого функции МИН либо СРЗНАЧ. Для вас ни что не препятствует, чтоб приведенный этот скелет формулы применить с внедрением наиболее сложных функций для реализации очень удобного анализа отчета по продажам.
Как получить заглавия столбцов по зачиню одной ячейки?
К примеру, как красиво мы показали месяц, в котором была наибольшая продажа, при помощи 2-ой формулы. Легко увидеть что во 2-ой формуле мы употребляли скелет первой формулы без функции МАКС. Основная структура формулы: ВПР(B1;A5:G14;СТОЛБЕЦ(B5:G14);0). Мы поменяли функцию МАКС на ПОИСКПОЗ, которая в первом аргументе употребляет значение, приобретенное предшествующей формулой. Оно сейчас выступает в качестве аспекта для поиска месяца. И в итоге функция ПОИСКПОЗ нам возвращает номер столбца 2 где находится наибольшее значение размера продаж для продукта 4. Опосля чего же в работу врубается функция ИНДЕКС, которая возвращает значение по номеру сроки и столбца из определенного в ее аргументах спектра. Потому что у нас есть номер столбца 2, а номер строчки в спектре где хранятся наименования месяцев в любые случаи будет 1. Тогда нам осталось функцией ИНДЕКС получить соответствующее значение из спектра B4:G4 – Февраль (2-ой месяц).
Поиск значения в столбце Excel
Вторым вариантом задачки будет поиск по таблице с внедрением наименования месяца в качестве аспекта. В такие случаи мы должны поменять скелет нашей формулы: функцию ВПР поменять ГПР, а функция СТОЛБЕЦ заменяется на СТРОКА.
Это дозволит нам выяснить какой размер и какого продукта была наибольшая продажа в определенный месяц.
Чтоб отыскать какой продукт владел наибольшим объемом продаж в определенном месяце следует:
- В ячейку B2 введите заглавие месяца Июнь – это значение будет применено в качестве поискового аспекта.
- В ячейку D2 введите формулу:
- Для доказательства опосля ввода формулы нажмите комбинацию кнопок CTRL+SHIFT+Enter, потому что формула будет выполнена в массиве. А в строке формул покажутся фигурные скобки.
- В ячейку F1 введите вторую формулу:
- Опять Для доказательства нажмите CTRL+SHIFT+Enter.
Принцип деяния формулы поиска значения в столбце Excel:
В первом аргументе функции ГПР (Горизонтальный ПРосмотр) указываем ссылку на ячейку с аспектом для поиска. Во 2-м аргументе указана ссылка на просматриваемый спектр таблицы. 3-ий аргумент генерирует функция СТРОКА, которая делает в памяти массив номеров строк из 10 частей. Потому что в табличной части у нас находится 10 строк.
Дальше функция ГПР попеременно используя любой номер строчки делает массив соответствующих значений продаж из таблицы по определенному месяцу (Июню). Дальше функции МАКС осталось лишь избрать наибольшее значение из этого массива.
Дальше незначительно изменив первую формулу при помощи функций ИНДЕКС и ПОИСКПОЗ, мы сделали вторую для вывода наименования строк таблицы по зачиню ячейки. Заглавие соответственных строк (продуктов) выводим в F2.
ВНИМАНИЕ! При использовании скелета формулы для остальных задач постоянно обращайте внимание на 2-ой и 3-ий аргумент поисковой функции ГПР. Количество охваченных строк в спектре обозначенного в аргументе, обязано совпадать с количеством строк в таблице. Также нумерация обязана начинаться со 2-ой строчки!
На самом деле содержимое спектра нас совершенно не интересует, нам нужен просто счетчик строк. Другими словами поменять аргументы на: СТРОКА(B2:B11) либо СТРОКА(С2:С11) – это никак не воздействует на свойство формулы. Основное, что в этих спектрах по 10 строк, как и в таблице. И нумерация начинается со 2-ой строчки!
excel отыскать ячейки содержащие текст
Примечание: Мы стараемся как можно оперативнее обеспечивать вас животрепещущими справочными материалами на вашем языке. Эта страничка переведена автоматом, потому ее текст может содержать некорректности и грамматические ошибки. Для нас принципиально, чтоб эта статья была для вас полезна. Просим вас уделить пару секунд и сказать, посодействовала ли она для вас, при помощи клавиш понизу странички. Для удобства также приводим ссылку на оригинал (на британском языке).
Допустим, вы желаете убедиться, что столбец имеет текст, а не числа. Либо перхапсйоу необходимо отыскать все заказы, надлежащие определенному торговцу. Если вы не желаете учесть текст верхнего либо нижнего регистра, есть несколько методов проверить, содержит ли ячейка.
Вы также сможете употреблять фильтр для поиска текста. Доп сведения можно отыскать в разделе Фильтрация данных.
Поиск ячеек, содержащих текст
Чтоб отыскать ячейки, содержащие определенный текст, сделайте обозначенные ниже деяния.
Выделите спектр ячеек, которые вы желаете отыскать.
Чтоб выполнить поиск на всем листе, щелкните всякую ячейку.
На вкладке Основная в группе Редактирование нажмите клавишу отыскать _амп_и изберите пункт отыскать.
В поле отыскать введите текст (либо числа), который необходимо отыскать. Вы также сможете избрать крайний запрос в поисковике из раскрывающегося перечня отыскать .
Примечание: В аспектах поиска можно употреблять подстановочные знаки.
Чтоб задать формат поиска, нажмите клавишу Формат и изберите нужные характеристики в всплывающем окне Отыскать формат .
Нажмите клавишу Характеристики , чтоб еще более задать условия поиска. К примеру, можно отыскать все ячейки, содержащие данные 1-го типа, к примеру формулы.
В поле снутри вы сможете избрать лист либо книжку , чтоб выполнить поиск на листе либо во всей книжке.
Нажмите клавишу отыскать все либо Отыскать дальше.
Найдите все списки всех вхождений элемента, который необходимо отыскать, и вы сможете создать ячейку активной, выбрав определенное вхождение. Вы сможете отсортировать поисковые результаты » отыскать все «, щелкнув заголовок.
Примечание: Чтоб приостановить поиск, нажмите кнопку ESC.
Проверка ячейки на наличие в ней текста
Для выполнения данной задачки используйте функцию текст .
Проверка соответствия ячейки определенному тексту
Используйте функцию Если , чтоб возвратить результаты для обозначенного условия.
Проверка соответствия части ячейки определенному тексту
Для выполнения данной задачки используйте функции Если, Поиски функция номер .
Примечание: Функция Поиск не учитывает регистр.
До этого, чем ответить на вопросец, как в таблице excel отыскать необходимое слово, необходимо осознать, каким будет формат вывода — мы желаем напротив каждой ячейки в спектре проставить статус — есть слово в ячейке, либо нет, либо желаем просто выяснить, есть ли слово в хоть какой из ячеек спектра?
Чем различается обнаружение целых слов в массиве ячеек от обнаружения обычных текстовых паттернов?
Сначала тем, что если находить недлинные слова обыденным вхождением, мы можем напороться на ситуации, когда слово находится снутри другого слова. В таковых кейсах в итог фильтрации попадут ячейки, которые нам не необходимы. Чтоб избежать таковых ситуаций, нужна наиболее непростая фильтрация.
Как отыскать слово в экселе — методы фильтрации
Excel содержит в для себя 3 метода текстовой фильтрации, другими словами, фильтровать можно по 3 аспектам вхождения.
Ячейка содержит, начинается с, или завершается на — какой то определенной паттерн.
В нашем случае для фильтрации целого слова, когда слово содержится в середине строчки — во всех таковых вариантах опосля слова и перед будут размещаться пробелы. Потому мы можем задать конкретно таковой аспект фильтрации, (пример — «* слово *») . Пробелы являются тем ограничителем, который дозволит ячейки, в которых слово середине фразы, а фильтровать вполне и исключить ситуаций, когда оно будет заходить в остальные слова. «Звездочки» необходимы для того, чтоб сказать Excel, что в строке вправду есть пробелы — по умолчанию он их просто убирает перед поиском, по-видимому, воспринимая их наличие как случайность.
Аспект номер 2. — Строчка завершается на наше слово. В этом случае она будет заканчиваться наше слово, и перед ним непременно должен быть пробел, допустим. У нас будет рот и в конце строчки не быть может слово крот. Таковая строчка не обязана фильтроваться. Необходимо чтоб перед словом рот был пробел.
И аспект номер 3 — ячейка обязана начинаться на наше слово — в таком случае пробел должен быть опосля нашего слова, по аналогичной причине.
Неувязка заключается в том, что в Excel недозволено фильтровать сходу по 3 аспектам — можно лишь по двум. Данной нам ситуации есть обычной лайфхак:
1. Перед каждой строчкой и опосля нее добавляем пробел (=» «&A1&» «)
2. Опосля этого фильтруем по приобретенному столбцу уже наше слово с пробелами перед и опосля него и особыми «звездочками» перед и опосля этих пробелов соответственно (пример — «* слово *»), для того, чтоб отфильтровать слова, которое непременно содержат перед ними и опосля их пробел. Это и будут ячейки, содержащие наше слово.
Решение задачки при помощи формулы
Конкретной формулой, возвращающей «ИСТИНА» либо «ЛОЖЬ» для проверки наличия в строке целого слова, будет:
=ПОИСК(» «&B1&» «;» «&A1&» «)>0
Поиск целых слов в ячейках в SEMTools
Пожалуй, самое резвое решение. Метод обычной — выделяем спектр, нажимаем макрос, вводим слово, нажимаем «ОК».
Функция ЕСЛИ СОДЕРЖИТ
Наверняка, почти все задавались вопросцем, как отыскать функцию в EXCEL«СОДЕРЖИТ» , чтоб применить какое-либо условие, в зависимости от того, есть ли в текстовой строке кусочек слова , либо отрицание, либо часть наименования контрагента, в особенности при нетрадиционном заполнении реестров вручную.
Таковой функционал может быть получить при помощи сочетания 2-ух обыденных обычных функций – ЕСЛИ и СЧЁТЕСЛИ .
Разглядим пример автоматизации учета операционных характеристик на основании реестров учета продаж и возвратов (выгрузки из посторониих программ автоматизации и т.п.)
У нас есть огромное количество строк с документами Реализации и Возвратов .
Все документы имеют свое наименование за счет неповторимого номера .
Нам нужно создать признак « Лишь реализация » напротив документов реализации, для того, чтоб в предстоящем включить этот признак в сводную таблицу и исключить возвраты для оценки эффективности деятельности отдела продаж.
Выражение обязано быть всепригодным , для того, чтоб обрабатывать новейшие добавляемые данные .
Для того, чтоб это создать, нужно:
-
Начинаем с ввода функции ЕСЛИ (вводим «=» , набираем наименование ЕСЛИ , избираем его из выпадающего перечня, жмем fx в строке формул).
В открывшемся окне аргументов, в поле Лог_выражение вводим СЧЁТЕСЛИ() , выделяем его и жмем 2 раза fx.
Дальше в открывшемся окне аргументов функции СЧЁТЕСЛИ в поле «Аспект» вводим кусочек искомого наименования *реализ* , добавляя в начале и в конце знак * .
Таковая запись даст возможность не мыслить о том, с какой стороны написано слово реализация (до либо опосля номера документа), также даст возможность включить в расчет сокращенные слова «реализ.» и «реализац.»
Сейчас мы можем работать и сводить данные лишь по документам реализации исключая возвраты . При дополнении таблицы новенькими данными, остается лишь протягивать строчку с нашим выражением и обновлять сводную таблицу.
Если материал Для вас приглянулся либо даже понадобился, Вы сможете поблагодарить создателя, переведя определенную сумму по кнопочке ниже:
(для перевода по карте нажмите на VISA и дальше «перевести»)
Делаем поиск в Excel
Если в вашем документе Excel очень много различных полей, то для поиска данных в их можно пользоваться интегрированным инвентарем поиска. С его помощью можно отыскать как определенный фрагмент, так и ячейку, тип данных и т.д. Это весьма упрощает работу с документом Excel, а употреблять интегрированный в программку поиск по элементам не так трудно.
Как работать с поиском в Excel
Взаимодействие с поиском по таблице Excel происходит через интерфейс инструмента «Отыскать и поменять». Как надо из наименования, с его помощью весьма просто отыскать и поменять текстовые либо числовые значения. Но, если для вас требуется выполнить лишь поиск, то этот инструмент тоже хорошо подойдет.
Дальше разглядим варианты поиска данных по таблице Excel.
Вариант 1: Резвый поиск
Это более обычной вариант поиска данных по всей таблице. В итоге он находит все ячейки, где содержится введенный набор знаков в четком совпадении. Регистр при вводе не учитывается.
Внедрение инструмента происходит по данной аннотации:
- Откройте подходящий документ в Excel. В верхнем меню переключитесь на вкладку «Основная». Совершенно она открыта постоянно по умолчанию.
- Сейчас кликните по блоку «Отыскать и выделить». В контекстном меню изберите «Отыскать». Заместо обрисованных действий можно просто пользоваться сочетанием кнопок Ctrl+F.
- В первом случае все поисковые результаты выводятся в нижней части окна в виде перечня. В этом перечне находятся информация о содержимом ячеек с данными, удовлетворяющими запросу поиска, указан их адресок расположения, также лист и книжка, к которым они относятся. Можно перейти к хоть какому результату, просто кликнув по нему левой клавишей мыши.
- Во 2-м случае вы перемещаетесь сходу же к первому отысканному совпадению. Сама ячейка с отысканным совпадением становится автоматом активной. Поисковые знаки не непременно должны быть самостоятельными элементами. Так, если в качестве запроса будет задано выражение «лев», то в выдаче будут представлены все ячейки, которые содержат данный поочередный набор знаков даже снутри слова. К примеру, слово «влево».
Вариант 2: поиск в обозначенном интервале
Если вы буквально понимаете, где приблизительно размещен разыскиваемый вами объект, то сможете пользоваться данным вариантом поиска. В этом случае для вас будут показаны лишь результаты, находящиеся лишь в выделенном спектре. Весьма комфортно, если в таблице весьма нередко встречается разыскиваемый элемент, а для вас необходимо лишь чтоб он был отображен лишь в определенной части таблицы.
{Инструкция} к этому типу поиска смотрится последующим образом:
- Выделите область ячеек, снутри которых необходимо провести поиск. Если для вас необходимо выделить несколько областей, разбитых меж собой иными ячейками, то зажмите кнопку Ctrl, чтоб добавить новейший блок к выделению.
- Опосля выделения вызовите окошко поиска, воспользовавшись сочетанием кнопок Ctrl+F.
- Раскроется такое же окно для поиска, как и в прошлом варианте с той лишь поправкой, что оно будет находить лишь в выделенной области. В поле «Отыскать» введите то, что для вас необходимо найти.
- Воспользуйтесь клавишей «Отыскать все» либо «Отыскать дальше». Тщательно о том, чем обе клавиши различаются друг от друга мы писали выше.
Вариант 3: Расширенный поиск
При обыкновенном поиске в итог выдачи будут попадать все ячейки, которые содержат поочередный набор поисковых знаков в любом виде не зависимо от регистра. Не достаточно того, в выдачу может попасть не только лишь содержимое определенной ячейки, да и адресок элемента, на который она ссылается. К примеру, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Если в поиск задать цифру 4, то все равно будет показываться ячейка E2, даже если итог сумм A4 и C3 вышел совсем остальным.
Способности расширенного поиска разрешают избежать представленных проблем. Давайте разглядим, как с ними вести взаимодействие:
- Вызовите окно обыденного поиска. Это можно создать через клавишу «Отыскать» либо воспользовавшись сочетанием кнопок Ctrl+F.
- В открывшемся окне опций воспользуйтесь клавишей «Характеристики».
- Опосля этого покажется целый ряд характеристик, которые можно настроить под себя, к примеру, можно создать так, чтоб во время поиска учитывался регистр, в поиске выводились элементы, содержащие четкое наименование. Также тут можно задать, что непосредственно просматривать, в какой части документа находить и даже формат, в котором должен быть итог.
Видите ли, в Excel представлен достаточно многофункциональный поиск по элементам. Не достаточно того, вы сможете находить нужные значения не только лишь на определенном листе, да и по всему файлу либо некий маленький области.
Поиск слов в Excel
В Microsoft Excel нередко работают с большенными объёмами инфы. В нём создаются большие таблицы на тыщи строк, столбцов и позиций. Найти какие-то определенные данные в таком массиве бывает тяжело. А время от времени — совершенно нереально. Эту задачку можно упростить. Разберитесь, как в Экселе отыскать необходимое слово. Так для вас легче будет ориентироваться в документе. И вы можете стремительно перейти к разыскиваемой инфы.
В редакторе таблиц существует несколько методов поиска
Поиск ячеек
Чтоб отобразились адреса всех ячеек, в которых есть то, что вы отыскиваете, сделайте последующее:
- Если у вас Office 2010, перейдите в Меню — Правка — Отыскать.
- Раскроется окно с полем для ввода. Напишите в нём фразу для поиска.
- В Excel 2007 эта клавиша есть в меню «Основная» на панели «Редактирование». Она находится справа.
- Аналогичного результата во всех версиях можно достигнуть, если надавить клавиши Ctrl+F.
- В поле напечатайте слово, фразу либо числа, которые нужно найти.
- Нажмите «Отыскать всё», чтоб запустить поиск по целому документу. Если кликните «Дальше», программка будет по одной выделять клеточки, которые находятся ниже курсора-ячейки Excel.
- Подождите, пока процесс завершится. Чем больше документ, тем подольше система будет находить.
- Покажется перечень с плодами: именами и адресами клеток, в которых есть совпадения с данной фразой, и текстом, который в их написан.
- При клике на каждую строку будет выделяться соответственная ячейка.
- Для удобства сможете «растянуть» окно. Так в нём будет видно больше строчек.
- Чтоб отсортировать данные, кликайте на наименования столбцов над плодами поиска. Если нажмёте на «Лист», строки выстроятся по алфавиту в зависимости от наименования листа, если изберите «Значения» — встанут по значению.
- Эти столбцы также «растягиваются».
Характеристики поиска
Сможете задать свои условия. К примеру, запустить поиск по нескольким знакам. Ах так в Экселе отыскать слово, которое вы не помните полностью:
- Введите лишь часть надписи. Можно хоть одну буковку — будут выделены все места, в которых она есть. * (звёздочка) и ? (вопросительный символ). Они замещают пропущенные знаки.
- Вопросец обозначает одну отсутствующую позицию. Если вы напишите, например, «П. », отобразятся ячейки, в которых есть слово из четырёх знаков, начинающееся на «П»: «Плуг», «Поле», «Пара» и так дальше.
- Звезда (*) замещает хоть какое количество символов. Чтоб найти все значения, в которых содержится корень «раст», начните поиск по ключу «*раст*».
Также вы сможете зайти в опции:
- В окне «Отыскать» нажмите «Характеристики».
- В разделах «Просматривать» и «Область поиска», укажите, где и по каким аспектам нужно находить совпадения. Можно избрать формулы, примечания либо значения.
- Чтоб система различала строчные и строчные буковкы, поставьте галочку в «Учесть регистр».
- Если вы отметите пункт «Ячейка полностью», в результатах покажутся клеточки, в которых есть лишь данная поисковая фраза и ничего больше.
Характеристики формата ячеек
Чтоб найти значения с определённой заливкой либо начертанием, используйте опции. Ах так отыскать в Excel слово, если оно имеет хороший от остального текста вид:
- В окне поиска нажмите «Характеристики» и кликните на клавишу «Формат». Раскроется меню с несколькими вкладками.
- Сможете указать определённый шрифт, вид рамки, цвет фона, формат данных. Система будет просматривать места, которые подступают к данным аспектам.
- Чтоб взять информацию из текущей клеточки (выделенной в этот момент), нажмите «Применять формат данной ячейки». Тогда программка найдет все значения, у каких этот же размер и вид знаков, этот же цвет, те же границы и тому схожее.
Поиск нескольких слов
В Excel можно найти клеточки по целым фразам. Но если вы ввели ключ «Голубий шар», система будет работать конкретно по этому запросу. В результатах не покажутся значения с «Голубий хрустальный шар» либо «Голубий блестящий шар».
Чтоб в Экселе отыскать не одно слово, а сходу несколько, сделайте последующее:
- Напишите их в строке поиска.
- Поставьте меж ними звёздочки. Получится «*Текст* *Текст2* *Текст3*». Так отыщутся все значения, содержащие обозначенные надписи. Вне зависимости от того, есть ли меж ними какие-то знаки либо нет.
- Сиим методом можно задать ключ даже с отдельными знаками.
Фильтр
Ах так находить в Экселе, используя фильтр:
- Выделите какую-нибудь заполненную ячейку.
- Нажмите Основная — Сортировка — Фильтр.
- В верхней строке у клеток покажутся стрелочки. Это выпадающее меню. Откройте его.
- В текстовом поле введите запрос и нажмите «OK».
- В столбце будут отображаться лишь ячейки, содержащие разыскиваемую фразу.
- Чтоб сбросить результаты, в выпадающем перечне отметьте «Выделить всё».
- Чтоб отключить фильтр, повторно нажмите на него в сортировке.
Этот метод не подойдёт, если вы не понимаете, в каком ряду необходимое для вас значение.
Чтоб отыскать в Excel какую-то фразу либо число используйте интегрированные способности интерфейса. Можно избрать доп характеристики поиска и включить фильтр.