Строка поиска в таблице excel

В документах Microsoft Excel, которые состоят из большого количества полей, часто требуется найти определенные данные, наименование строки, и т.д. Очень неудобно, когда приходится просматривать огромное количество строк, чтобы найти нужное слово или выражение. Сэкономить время и нервы поможет встроенный поиск Microsoft Excel. Давайте разберемся, как он работает, и как им пользоваться.

Поисковая функция в Excel

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

Способ 1: простой поиск

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

1. Находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая расположена на ленте в блоке инструментов «Редактирование». В появившемся меню выбираем пункт «Найти…». Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F.



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



3. При нажатии на кнопку «Найти далее» мы перемещаемся к первой же ячейке, где содержатся введенные группы символов. Сама ячейка становится активной.

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

   Поисковые символы не обязательно должны быть самостоятельными элементами. Так, если в качестве запроса будет задано выражение «прав», то в выдаче будут представлены все ячейки, которые содержат данный последовательный набор символов даже внутри слова. Например, релевантным запросу в этом случае будет считаться слово «Направо». Если вы зададите в поисковике цифру «1», то в ответ попадут ячейки, которые содержат, например, число «516».

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

   

   Так можно продолжать до тех, пор, пока отображение результатов не начнется по новому кругу.

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

Способ 2: поиск по указанному интервалу ячеек

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

1. Выделяем область ячеек, в которой хотим произвести поиск.



2. Набираем на клавиатуре комбинацию клавиш Ctrl+F, после чего запуститься знакомое нам уже окно «Найти и заменить». Дальнейшие действия точно такие же, что и при предыдущем способе. Единственное отличие будет состоять в том, что поиск выполняется только в указанном интервале ячеек.

Способ 3: Расширенный поиск

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

К тому же, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Эта сумма равна 10, и именно это число отображается в ячейке E2. Но, если мы зададим в поиске цифру «4», то среди результатов выдачи будет все та же ячейка E2. Как такое могло получиться? Просто в ячейке E2 в качестве формулы содержится адрес на ячейку A4, который как раз включает в себя искомую цифру 4.

Но, как отсечь такие, и другие заведомо неприемлемые результаты выдачи поиска? Именно для этих целей существует расширенный поиск Excel.

1. После открытия окна «Найти и заменить» любым вышеописанным способом, жмем на кнопку «Параметры».



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

   По умолчанию, функции «Учитывать регистр» и «Ячейки целиком» отключены, но, если мы поставим галочки около соответствующих пунктов, то в таком случае, при формировании результата будет учитываться введенный регистр, и точное совпадение. Если вы введете слово с маленькой буквы, то в поисковую выдачу, ячейки содержащие написание этого слова с большой буквы, как это было бы по умолчанию, уже не попадут. Кроме того, если включена функция «Ячейки целиком», то в выдачу будут добавляться только элементы, содержащие точное наименование. Например, если вы зададите поисковый запрос «Николаев», то ячейки, содержащие текст «Николаев А. Д.», в выдачу уже добавлены не будут.

   

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

   

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

   

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

   

   Ещё более точно поиск можно задать, нажав на кнопку «Формат».

   

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

   

   Если вы хотите использовать формат какой-то конкретной ячейки, то в нижней части окна нажмите на кнопку «Использовать формат этой ячейки…».

   

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

   

   После того, как формат поиска настроен, жмем на кнопку «OK».

   

   Бывают случаи, когда нужно произвести поиск не по конкретному словосочетанию, а найти ячейки, в которых находятся поисковые слова в любом порядке, даже, если их разделяют другие слова и символы. Тогда данные слова нужно выделить с обеих сторон знаком «*». Теперь в поисковой выдаче будут отображены все ячейки, в которых находятся данные слова в любом порядке.



3. Как только настройки поиска установлены, следует нажать на кнопку «Найти всё» или «Найти далее», чтобы перейти к поисковой выдаче.

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

Поиск в Excel.

​Смотрите также​​ поиска — а​​Разница только в последнем​​ функция​​ ячейки с пересечения​ слову​ CTRL + A,​ столбца по щелчку​: Для просмотра промежуточных​​ обоих столбцах попарно​ ​ в строке ячейки​ строкой «МДС0093.МужскаяОдежда». Чтобы​
​, с которого следует​ строки в другой​ «Поиск» или формулами,​​ не будет мешать.​ — на закладке​Часто возникает вопрос​ значит и вся​ аргументе обеих функций​ИНДЕКС (INDEX)​
​ определенной строки и​можно найти и​ чтобы выбрать таблицу​​ появится стрелка выделения​ вычислений воспользуйтесь клавишей​ совпадают.​ A2, начиная с​ найти первое вхождение​ начать поиск.​ текстовой строке, а​
​ но и функцией​​ Сдвинуть можно ниже​ «Главная» нажать кнопку​​«​ таблица — должна​ПОИСКПОЗ (MATCH)​из той же​ столбца в таблице.​
​ вывести на экран​ данных в таблицу​ ​ для следующих.​​ F9.​Для поиска совпадений будем​ шестого знака.​
​ «М» в описательной​Функции​ затем вернуть текст​​ условного форматирования. Читайте​​ экрана, оставив только​ «Найти и выделить».​Как найти в Excel​ быть отсортирована по​
​-​ категории​ Для наглядности, разобъем​ все что имеет​ целиком, или нажмите​
​Примечание:​С помощью формулы массива =ПОИСКПОЗ(1;(B17=B$8:B$12)*(C17=C$8:C$12);0) найдем​ использовать формулу =СУММПРОИЗВ((B17=B$8:B$12)*(C17=C$8:C$12)) Формулу разместим​7​ части текстовой строки,​ПОИСК​ с помощью функций​ об этом статью​ ячейку ввода искомого​На вкладке «Найти» в​»?​
​ возрастанию (для Типа​Типу сопоставления​Ссылки и массивы (Lookup​ задачу на три​ определенный цвет и/или​ кнопку большинство верхнюю​ Щелкнуть верхний край один​ номер искомой строки.​ в столбце D.​=ПОИСК(A4;A3)​ задайте для аргумента​и​
​ПСТР​ «Условное форматирование в​ слова («найти») и​ ячейке «найти» пишем​В Excel можно​ сопоставления = 1)​(здесь он равен​ and Reference)​ этапа.​ размер не превышает​
​ левую ячейку в​​ раз, выделяются данные​ Формулу разместим в​Формула работает следующим образом.​​Начальная позиция строки «маржа»​​начальная_позиция​
​ПОИСКБ​
​и​ Excel» здесь.​ нажимать потом Enter.​ искомое слово (можно​ найти любую информацию:​ или по убыванию​ минус 1). Это​. Первый аргумент этой​Во-первых, нам нужно определить​ L*H​ таблице и нажмите​ в столбец; Дважды​ столбце Е.​ Выражение B17=B$8:B$12 сравнивает текущее​ (искомая строка в​значение 8, чтобы​не учитывают регистр.​ПСТРБ​
​Ещё прочитать о​Это диалоговое окно​ часть слова) и​ текст, часть текста,​ (для Типа сопоставления​ некий аналог четвертого​ функции — диапазон​ номер строки, соответствующей​Но для этого​ клавиши CTRL +​ щелкнув его выделяется​Также для каждой искомой​ значение первого столбца​
​ ячейке A4) в​ поиск не выполнялся​ Если требуется учитывать​или заменить его​
​ функции «Найти и​ поиска всегда остается​ нажимаем «найти далее».​ цифру, номер телефона,​
​ = -1) по​ аргумента функции​ ячеек (в нашем​ выбранному пользователем в​ как раз и​ SHIFT + END.​ весь столбец таблицы.​ строки можно определить,​ большой таблицы со​
​ строке «Доход: маржа»​ в той части​​ регистр, используйте функции​ с помощью функций​ выделить» можно в​ на экране, даже​ Будет найдено первое​ эл. адрес​ строчкам и по​
​ВПР (VLOOKUP) — Интервального​ случае это вся​ желтой ячейке товару.​ надо знать те​Нажмите клавиши CTRL +​Можно также щелкнуть любую​ если она в​
​ всеми значениями первого​ (ячейка, в которой​ текста, которая является​НАЙТИ​ЗАМЕНИТЬ​ статье «Фильтр в​ если вы переходите​ такое слово. Затем​,​
​ столбцам.​ просмотра (Range Lookup)​ таблица, т.е. B2:F10),​ Это поможет сделать​ условия, о которых​

excel-office.ru

ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

​ A, два раза,​ ячейку столбца и​ большой таблице. Это​​ столбца искомых строк,​​ выполняется поиск — A3).​​ серийным номером (в​​и​

Описание

​и​​ Excel».​​ на другую страницу.​​ нажимаете «найти далее»​​фамилию, формулу, примечание, формат​Иначе приблизительный поиск корректно​. Вообще говоря, возможных​ второй — номер​ функция​ вы промолчали.​ чтобы выделить таблицу​ нажмите клавиши CTRL​ можно сделать с​ и возвращает массив​8​

​ данном случае —​

​НАЙТИБ​​ЗАМЕНИТЬБ​​В этой статье описаны​ С помощью этого​ и поиск перейдет​

​ ячейки, т.д.​ работать не будет!​ значений для него​

​ строки, третий -​

​ПОИСКПОЗ (MATCH)​​Приводите конкретный пример,​​ целиком, включая заголовки​ + ПРОБЕЛ или​ помощью функции =ЕСЛИ(ЕНД(ПОИСКПОЗ(A8;$E$17:$E$28;0));»»;»Да!»)​ логических значений {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}. 5​=ЗАМЕНИТЬ(A3;ПОИСК(A4;A3);6;»объем»)​​ «МДС0093»). Функция​​.​​. Эти функции показаны​​ синтаксис формулы и​ окна можно производить​ на второе такое​Найти ячейку на пересечении​Для точного поиска (Тип​ три:​​ номер столбца (а​​из категории​​ а лучше предоставьте​​ таблицы.​ щелкните первую ячейку​​Найденные строки можно выделить​​ значений ЛОЖЬ означает,​​Заменяет слово «маржа» словом​​ПОИСК​В аргументе​ в примере 1​

​ использование функций​​ поиск на любой​

• ​ слово.​ строки и столбца​ сопоставления = 0)​

• ​1​ их мы определим​Ссылки и массивы (Lookup​ файл​Иван леконтцев​ в столбце таблицы​ с помощью Условного​ что значение 1,2​ «объем», определяя позицию​начинает поиск с​искомый_текст​ данной статьи.​ПОИСК​

​ странице, надо только​А если надо показать​ Excel​ сортировка не нужна​- поиск ближайшего​

Синтаксис

​ с помощью функций​

​ and Reference)​

​Если вы знакомы с​: можно сделать фильтр​

• ​ и нажмите клавиши​​ форматирования.​ из В17 не​

• ​ слова «маржа» в​​ восьмого символа, находит​можно использовать подстановочные​Важно:​​и​​ его активизировать на​

• ​ сразу все такие​​– смотрите статью​ и никакой роли​​ наименьшего числа, т.е.​​ ПОИСКПОЗ).​. В частности, формула​

Замечание

• ​ функцией​​ (автофильтр) и используя​​ CTRL + SHIFT​​Примечание:​​ найдено в диапазоне B$8:B$12,​ ячейке A3 и​ знак, указанный в​​ знаки: вопросительный знак​​ ​​ПОИСКБ​​ открытой странице. Для​

• ​ слова, то нажимаем​​ «Как найти в​​ не играет.​ введенные пользователем размеры​Итого, соединяя все вышеперечисленное​​ПОИСКПОЗ(J2; A2:A10; 0)​​ВПР (VLOOKUP)​​ его выводить только​​ + Стрелка вниз.​Мы стараемся как​ состоящем из 5​ заменяя этот знак​ аргументе​ (​Эти функции могут быть​​в Microsoft Excel.​​ этого нажать курсор​

• ​ кнопку «найти все»​​ Excel ячейку на​​В комментах неоднократно интересуются​ двери округлялись бы​

• ​ в одну формулу,​​даст нам нужный​​или ее горизонтальным​ необходимые значения.​

• ​Примечание:​​ можно оперативнее обеспечивать​​ значений, т.к. значение​ и последующие пять​искомый_текст​​?​​ доступны не на​Функции​

• ​ на строке «найти».​​ и внизу поискового​​ пересечении строки и​ — а как​ до ближайших наименьших​ получаем для зеленой​​ результат (для​​ аналогом​видео как сделать​ Один раз клавиши CTRL​ вас актуальными справочными​ не встречается в​ знаков текстовой строкой​, в следующей позиции,​​) и звездочку (​​ всех языках.​ПОИСК​Для более расширенного​ окошка появится список​ столбца» (функция «ИНДЕКС»​ сделать обратную операцию,​ подходящих размеров из​​ ячейки решение:​​Яблока​ГПР (HLOOKUP)​ автофильтр.​ + ПРОБЕЛ выделяются​​ материалами на вашем​​ первом столбце искомых​ «объем.»​ и возвращает число​​*​​Функция ПОИСКБ отсчитывает по​И​ поиска нажмите кнопку​​ с указанием адреса​​ в Excel).​ т.е. определить в​ таблицы. В нашем​​=ИНДЕКС(B2:F10; ПОИСКПОЗ(J2;A2:A10;0); ПОИСКПОЗ(J3;B1:F1;0))​​это будет число​

Примеры

​, то должны помнить,​_______________​ данные в столбце;​ языке. Эта страница​ строк.​Доход: объем​ 9. Функция​). Вопросительный знак соответствует​ два байта на​ПОИСКБ​ «Параметры» и выберите​ ячейки. Чтобы перейти​Найти и перенести в​

support.office.com

Поиск заданных строк в таблице MS EXCEL

​ нужное слово в​(например, в бланк)​ из таблицы? Тут​ 480 до 300,​ Предположим, что у​

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

​ первым пробелом в​ начала​ вопросительный знак или​

​ с поддержкой БДЦС.​ позицию первой текстовой​ и числа, и​ списке окна поиска.​

​ несколько данных сразу​ потребуются две небольшие​ и стоимость двери​

​ нас имеется вот​из желтой ячейки​ одномерном массиве -​Сергей харин​Строка таблицы​ нас важно, чтобы​ этом ЛОЖЬ преобразуется​ строке «Доход: маржа»​просматриваемого текста​ звездочку, введите перед​ В противном случае​ строки (считая от​ номер телефона, т.д.​Если поиск ничего не​ – смотрите в​ формулы массива (не​ была бы 135.​

​ такая ситуация:​ J2), второй -​ по строке или​: Ctrl+F​Щелкните левой границы строки​ эта статья была​ в 0, а​ (ячейка A3).​, включая символы, которые​ ним тильду (​ функция ПОИСКБ работает​ первого символа второй​Если нужно найти​ нашел, а вы​ статье «Найти в​ забудьте ввести их​-1​Идея в том, что​ диапазон ячеек, где​ по столбцу. А​

​Ввести условие поиска​ таблицы. Чтобы указать,​ вам полезна. Просим​ ИСТИНА в 1).​марж​ пропускаются, если значение​~​ так же, как​ текстовой строки). Например,​ все одинаковес слова,​ знаете, что эти​ Excel несколько данных​

​ с помощью сочетания​​- поиск ближайшего​ пользователь должен ввести​ мы ищем товар​

​ если нам необходимо​Enter​ что при щелчке​ вас уделить пару​

​ Знак умножения эквивалентен​=ПОИСК(«»»»;A5)​ аргумента​).​ функция ПОИСК, и​ чтобы найти позицию​

​ но в падежах​ данные точно есть,​ сразу» здесь (функция​

excel2.ru

Выделение строк и столбцов в таблице Excel

​ клавиш​​ наибольшего числа, т.е.​ в желтые ячейки​ (столбец с товарами​ выбирать данные из​Inga zayonts​ выделяется строка появится​ секунд и сообщить,​ в данном случае​Позиция первой двойной кавычки​начальная_позиция​Если​ отсчитывает по одному​ буквы «n» в​ (молоко, молоком, молоку,​ то попробуйте убрать​ «ВПР» в Excel).​Ctrl+Shift+Enter​ нестандартная высота 500​ высоту и ширину​ в таблице -​ двумерной таблицы по​

​: У вас не​ стрелка выделения для​ помогла ли она​ функции И(), т.е.​ («) в ячейке​больше 1.​искомый_текст​ байту на каждый​ слове «printer», можно​

support.office.com

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

​ + SHIFT +​​ оригинал (на английском​ строк. Функция СУММПРОИЗВ()​ только текст, заключенный​ листа Excel. Чтобы​
​опущен, то он​ (традиционное письмо) и​
​, так как «н»​
​Функция в Excel «Найти​Поиск числа в Excel​ ссылки на другие​ диапазоне B2:F10 и​

​ 462. Для бизнеса​​ появиться ее стоимость​
​Во-вторых, совершенно аналогичным способом​
​ жизненных примеров таких​

​ не указать какой​​ Стрелка вправо.​ языке) .​
​ складывает результат перемножения​ в двойные кавычки.​ отобразить результаты формул,​ полагается равным 1.​ корейский.​ является четвертым символом​
​ и выделить»​требует небольшой настройки​
​ листы в формулах​ ищем совпадение с​ так гораздо интереснее!​ из таблицы. Важный​ мы должны определить​
​ задач и их​ именно.​Все строки и столбцы​Можно выбрать ячеек и​ и использована вместо​босс​ выделите их и​Если аргумент​
​ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция])​ в слове «принтер».​поможет не только​
​ условий поиска -​ Excel».​ искомым значением (13)​ :)​ нюанс в том,​ порядковый номер столбца​
​ решения.​Сергей Харин предложил​ таблицы​ диапазонов в таблице​ СУММ() лишь для​
​Найдем заданные строки в​ нажмите клавишу F2,​начальная_позиция​

Двумерный поиск в таблице (ВПР 2D)

​ПОИСКБ(искомый_текст;просматриваемый_текст;[начальная_позиция])​Можно также находить слова​​ найти данные, но​​ применим​Найти в Excel ячейки​​ из ячейки J4​​0​ что если пользователь​ в таблице с​Предположим, что у нас​ универсальный поиск.​Щелкните в левом верхнем​ так же, как​ того, чтобы не​ таблице. Строки состоят​ а затем — клавишу​не больше 0​Аргументы функций ПОИСК и​ в других словах.​ и заменить их.​расширенный поиск в Excel​ с примечанием​ с помощью функции​- поиск точного​ вводит нестандартные значения​

Пример 1. Найти значение по товару и городу

​ нужным нам городом.​ имеется вот такой​Но учитывая что​ углу таблицы. Чтобы​ выбрать их на​

​ использовать формулы массива.​ из 2-х столбцов,​ ВВОД. При необходимости​ или больше, чем​ ПОИСКБ описаны ниже.​ Например, функция​ Смотрите статью «Как​.​-​ЕСЛИ (IF)​ соответствия без каких​ размеров, то они​ Функция​ двумерный массив данных​ у вас лист​ указать, что щелкнув​ листе, но выбор​

• ​Для примера рассмотрим строку​ значения могут быть​ измените ширину столбцов,​ длина​Искомый_текст​=ПОИСК(«base»;»database»)​​ скопировать формулу в​​Совет.​​статья «Вставить примечание​когда нашли совпадение, то​​ либо округлений. Используется​​ должны автоматически округлиться​​ПОИСКПОЗ(J3; B1:F1; 0)​ по городам и​​ большогог размера, то​​ выделяются данные в​ строк и столбцов​ 18 (2-я строка​ любыми.​​ чтобы видеть все​​просматриваемого текста​    Обязательный. Текст, который требуется​возвращает​ Excel без изменения​Если вы работаете​ в Excel» тут​ определяем номер строки​ для 100%-го совпадения​ до ближайших имеющихся​сделает это и​ товарам:​
• ​ это не самый​ таблицу целиком появится​ таблицы отличается от​ в большой таблице).​Пусть имеется перечень из​ данные.​​, возвращается значение ошибки​​ найти.​5​​ ссылок» здесь.​​ с таблицей продолжительное​ .​ (столбца) первого элемента​
• ​ искомого значения с​ в таблице и​ выдаст, например, для​Пользователь вводит (или выбирает​ удобный вариант.​ стрелка выделения для​ выбора листа строк​​ Первое и второе​​ 5 строк, которые​Данные​​ #ЗНАЧ!.​Просматриваемый_текст​​, так как слово​Как убрать лишние​ время и вам​Для быстрого поиска​ в таблице в​ одним из значений​ в серой ячейке​Киева​ из выпадающих списков)​Машину можно заставить​ следующих.​

​ и столбцов.​ выражения вернут массивы {ЛОЖЬ:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ}​ требуется найти в​Выписки​

​Аргумент​

​    Обязательный. Текст, в котором​ «base» начинается с​

Пример 2. Приблизительный двумерный поиск

​ пробелы, которые мешают​ часто надо переходить​ существует сочетание клавиш​ этой строке (столбце)​

​ в таблице. Естественно,​ должна появиться стоимость​, выбранного пользователем в​ в желтых ячейках​ искать и выводить​Щелкните левый верхний угол​Чтобы выделить​ и {ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ} соответственно. После​ таблице MS EXCEL.​Доход: маржа​начальная_позиция​ нужно найти значение​ пятого символа слова​ обрабатывать данные в​ к поиску от​ –​ с помощью функций​ применяется при поиске​ изготовления двери для​ желтой ячейке J3​ нужный товар и​ нужную информацию в​ таблицы два раза,​

​Выполните указанное действие​ попарного перемножения элементов​ Предполагается, что строки​

​маржа​

​можно использовать, чтобы​

​ аргумента​ «database». Можно использовать​​ таблице, читайте в​​ одного слова к​​Ctrl + F​​СТОЛБЕЦ (COLUMN)​ текстовых параметров (как​ этих округленных стандарных​ значение 4.​​ город. В зеленой​ удобном для вас​​ чтобы выделить таблицу​Столбец таблицы с заголовком​ массивов, он будет​

• ​ не повторяются.​​Здесь «босс».​ пропустить определенное количество​искомый_текст​ функции​ статье «Как удалить​ другому. Тогда удобнее​. Нажимаем клавишу Ctrl​и​ в прошлом примере),​ размеров.​И, наконец, в-третьих, нам​ ячейке нам нужно​ месте, так же​
• ​ целиком, включая заголовки​​ или без него​ преобразован в {0:0:1:0:0}. Т.е.​Будем искать эти строки​Формула​ знаков. Допустим, что​.​ПОИСК​ лишние пробелы в​ окно поиска не​ и, удерживая её,​СТРОКА (ROW)​
• ​ т.к. для них​​Решение для серой ячейки​ нужна функция, которая​ формулой найти и​ можно искать по​ таблицы.​Щелкните верхний край заголовка​ строка 18 совпадает​ в большой таблице​Описание​ функцию​Начальная_позиция​и​

​ Excel» тут.​ закрывать каждый раз,​ нажимаем клавишу F.​выдергиваем значение города или​ округление невозможно.​ будет практически полностью​ умеет выдавать содержимое​ вывести число из​ индексу/артикулу/номеру тлф и​Можно также щелкните в​ столбца или столбца​ с третьей искомой​ (см. файл примера). ​Результат​​ПОИСК​    Необязательный. Номер знака в​

​ПОИСКБ​В Excel можно​ а сдвинуть его​ Появится окно поиска.​ товара из таблицы​

P.S. Обратная задача

​Важно отметить, что при​ аналогично предыдущему примеру:​ ячейки из таблицы​ таблицы, соответствующее выбранным​ т. д.​ любом месте таблицы​ в таблице. Чтобы​ строкой. Функция СУММПРОИЗВ() вернет 1.​Строки считаются совпадающими, когда​=ПОИСК(«и»;A2;6)​нужно использовать для​ аргументе​для определения положения​​ найти любую информацию​​ в ту часть​​Ещё окно поиска​​ с помощью функции​

​ использовании приблизительного поиска​

1. ​=ИНДЕКС(C7:K16; ПОИСКПОЗ(D3;B7:B16;1); ПОИСКПОЗ(G3;C6:K6;1))​ по номеру строки​ параметрам. Фактически, мы​Компьютер может «дописывить»​ и нажмите клавиши​ указать, что выделение​​СОВЕТ​
2. ​ их значения в​Позиция первого знака «и»​ работы с текстовой​просматриваемый_текст​ символа или текстовой​ не только функцией​​ таблицы, где оно​​ можно вызвать так​​ИНДЕКС (INDEX)​
3. ​ с округлением диапазон​=INDEX(C7:K16; MATCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))​ и столбца -​​ хотим найти значение​

planetaexcel.ru

​ строку по введенноу​

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

Поиск слов

Документы часто имеют много страниц, тогда встает вопрос о том, как в Еxcel найти слово. Сделать это  иногда становится проблематично. Для упрощения этой задачи существует специальная функция поиска. Чтобы ею воспользоваться, необходимо выполнить следующий алгоритм действий:

• запустить программу Excel;
•  проверить активность таблицы, щелкнув по любой из ячеек;
•  нажать комбинацию клавиш «Ctrl + F»;
•  в строке «Найти» появившегося окна ввести искомое слово;
•  нажать «Найти».

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

Существует также способ нестрогого поиска, который подходит для ситуаций, когда искомое слово помнится частично. Он предусматривает использование символов-заменителей (джокерные символы). В Excel их всего два:

•  «?» – подразумевает любой отдельно взятый символ;
•  «*» – обозначает любое количество символов.

 Примечательно, при поиске вопросительного знака или знака умножения дополнительно впереди ставится тильда («~»). При поиске тильды, соответственно – две тильды.

 Алгоритм неточного поиска слова:

•  запустить программу;
•  активировать страницу щелчком мыши;
•  зажать комбинацию клавиш «Ctrl + F»;
•  в строке «Найти» появившегося окна ввести искомое слово, используя вместо букв, вызывающих сомнения, джокерные символы;
•  проверить параметр «Ячейка целиком» (он не должен быть отмеченным);
•  нажать «Найти все».

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

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

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

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

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

Поиск ячеек

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

Для поиска ячеек с формулами выполняются следующие действия.

1. В открытом документе выделить ячейку или диапазон ячеек (в первом случае поиск идет по всему листу, во втором – в выделенных ячейках).
2. Во вкладке «Главная» выбрать функцию «Найти и выделить».
3. Обозначить команду «Перейти».
4. Выделить клавишу «Выделить».
5. Выбрать «Формулы».
6. Обратить внимание на список пунктов под «Формулами» (возможно, понадобится снятие флажков с некоторых параметров).
7. Нажать клавишу «Ок».

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

1. Перейти во вкладку «Главная».
2. Выбрать функцию «Найти и выделить».
3. Нажать на команду «Найти».
4. Перейти в «Параметры» и выбрать «Формат».
5. Здесь выделить функцию «Выравнивание», поставить отметку «Объединить ячейки».
6. Нажать на «Ок».
7. Нажать на кнопку «Найти все» и проанализировать список ячеек, которые объединены на соответствующем листе.

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

 Выполнение представленных выше действий приводит к нахождению всех объединенных ячеек на листе и при необходимости отмене данного свойства. Для поиска скрытых ячеек проводятся следующие действия.

1. Выбрать лист, требующий анализа на присутствие скрытых ячеек и их нахождения.
2. Нажать клавиши «F5_гт_
   Special».
3. Нажать сочетание клавиш «CTRL + G_гт_ Special».

Можно воспользоваться еще одним способом для поиска скрытых ячеек:

1. Открыть функцию «Редактирование» во вкладке «Главная».
2. Нажать на «Найти».
3. Выбрать команду «Перейти к разделу». Выделить «Специальные».
4. Попав в группу «Выбор», поставить галочку на «Только видимые ячейки».
5. Нажать кнопку «Ок».

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

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

Чтобы найти ячейки, для которых применено условное форматирование:

• нажать на ячейку, не предусматривающую условное форматирование;
• выбрать функцию «Редактирование» во вкладке «Главная»;
• нажать на кнопку «Найти и выделить»;
• выделить категорию «Условное форматирование».

Чтобы найти ячейки, для которых применено одинаковое условное форматирование:

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

Поиск через фильтр

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

• выделить заполненную ячейку;
• во вкладке «Главная» выбрать функцию «Сортировка»;
• нажать на кнопку «Фильтр»;
• открыть выпадающее меню;
• ввести искомый запрос;
• нажать кнопку «Ок».

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

Способ 1

Самый простой способ — выполнить поиск. Для этого можно нажать клавиатурную комбинацию CTRL + F (от англ. Find), откроется окно поиска слов.

Для нажатия клавиатурной комбинации, нажмите клавишу клавиатуры CTRL и, удерживая ее, нажмите клавишу F (на английский язык переходить не нужно).

Вместо клавиатурной комбинации можно использовать кнопку поиска на панели Главная — Найти и выделить — Найти.

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

• Найти все — выполнит поиск всех совпадений с указанной фразой. В окне ниже появится список, в котором будет указана фраза, содержащая искомые символы, а также место в документе, где символы были найдены.

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

Также можно сделать шире столбцы: Книга, Лист, Имя и т.д., потянув за маркеры между названиями столбцов.

В столбце Значение можно видеть полный текст ячейки, в котором есть искомые символы (в нашем примере — excel). Чтобы перейти к этому месту в таблице просто нажмите левой кнопкой мыши на нужную строку, и курсор автоматически переместится в выбранную ячейку таблицы.

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

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

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

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

Искать:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Способ 2

Второй способ, который позволяет найти искомое слово в таблице Excel, не является именно поиском, но также может быть удобен для работы. Это фильтр по фразе (символам), который позволяет вывести на экране только те строки, которые содержат искомые символы.

Для этого нужно щелкнуть мышкой по любой ячейке, среди которых нужно искать, нажать на вкладке Главная — Сортировка и фильтры — Фильтр.

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

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

Результат будет таков.

Таблица до применения фильтра и таблица после применения фильтра.

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

Также можно нажать на стрелочку и выбрать Текстовые фильтры — Содержит и указать искомые символы.

И далее ввести искомую фразу, например «Мюнхен».

Результат будет таков — только строки, содержащие слово «Мюнхен».

Этот фильтр сбрасывается также, как и предыдущий.

Таким образом, у пользователя есть варианты поиска слова в Excel — собственно сам поиск и фильтр.

Видеоурок по теме

Перейти к содержанию

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

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

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

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

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

Поиск значения в строке Excel

Теперь узнаем, в каком максимальном объеме и в каком месяце была максимальная продажа Товара 4.

Чтобы выполнить поиск по столбцам следует:

1. В ячейку B1 введите значение Товара 4 – название строки, которое выступит в качестве критерия.
2. В ячейку D1 введите следующую формулу:
3. Для подтверждения после ввода формулы нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно, в строке формул появятся фигурные скобки.



4. В ячейку F1 введите вторую формулу:
5. Снова Для подтверждения нажмите комбинацию клавиш CTRL+SHIFT+Enter.

Найдено в каком месяце и какая была наибольшая продажа Товара 4 на протяжении двух кварталов.

Принцип действия формулы поиска значения в строке Excel:

В первом аргументе функции ВПР (Вертикальный ПРосмотр) указывается ссылка на ячейку где находится критерий поиска. Во втором аргументе указывается диапазон ячеек для просмотра в процессе поиска. В третьем аргументе функции ВПР должен указываться номер столбца, из которого следует взять значение на против строки с именем Товар 4. Но так как нам заранее не известен этот номер мы с помощью функции СТОЛБЕЦ создаем массив номеров столбцов для диапазона B4:G15.

Это позволяет функции ВПР собрать целый массив значений. В результате в памяти хранится все соответствующие значения каждому столбцу по строке Товар 4 (а именно: 360; 958; 201; 605; 462; 832). После чего функции МАКС остается только взять из этого массива максимальное число и возвратить в качестве значения для ячейки D1, как результат вычисления формулы.

Как видно конструкция формулы проста и лаконична. На ее основе можно в похожий способ находить для определенного товара и другие показатели. Например, минимальное или среднее значение объема продаж используя для этого функции МИН или СРЗНАЧ. Вам ни что не препятствует, чтобы приведенный этот скелет формулы применить с использованием более сложных функций для реализации максимально комфортного анализа отчета по продажам.

Как получить заголовки столбцов по зачиню одной ячейки?

Например, как эффектно мы отобразили месяц, в котором была максимальная продажа, с помощью второй формулы. Не сложно заметить что во второй формуле мы использовали скелет первой формулы без функции МАКС. Главная структура формулы: ВПР(B1;A5:G14;СТОЛБЕЦ(B5:G14);0). Мы заменили функцию МАКС на ПОИСКПОЗ, которая в первом аргументе использует значение, полученное предыдущей формулой. Оно теперь выступает в качестве критерия для поиска месяца. И в результате функция ПОИСКПОЗ нам возвращает номер столбца 2 где находится максимальное значение объема продаж для товара 4. После чего в работу включается функция ИНДЕКС, которая возвращает значение по номеру сроки и столбца из определенного в ее аргументах диапазона. Так как у нас есть номер столбца 2, а номер строки в диапазоне где хранятся названия месяцев в любые случаи будет 1. Тогда нам осталось функцией ИНДЕКС получить соответственное значение из диапазона B4:G4 – Февраль (второй месяц).



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

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

Это позволит нам узнать какой объем и какого товара была максимальная продажа в определенный месяц.

Чтобы найти какой товар обладал максимальным объемом продаж в определенном месяце следует:

1. В ячейку B2 введите название месяца Июнь – это значение будет использовано в качестве поискового критерия.
2. В ячейку D2 введите формулу:
3. Для подтверждения после ввода формулы нажмите комбинацию клавиш CTRL+SHIFT+Enter, так как формула будет выполнена в массиве. А в строке формул появятся фигурные скобки.



4. В ячейку F1 введите вторую формулу:
5. Снова Для подтверждения нажмите CTRL+SHIFT+Enter.

Принцип действия формулы поиска значения в столбце Excel:

В первом аргументе функции ГПР (Горизонтальный ПРосмотр) указываем ссылку на ячейку с критерием для поиска. Во втором аргументе указана ссылка на просматриваемый диапазон таблицы. Третий аргумент генерирует функция СТРОКА, которая создает в памяти массив номеров строк из 10 элементов. Так как в табличной части у нас находится 10 строк.

Далее функция ГПР поочередно используя каждый номер строки создает массив соответственных значений продаж из таблицы по определенному месяцу (Июню). Далее функции МАКС осталось только выбрать максимальное значение из этого массива.

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

ВНИМАНИЕ! При использовании скелета формулы для других задач всегда обращайте внимание на второй и третий аргумент поисковой функции ГПР. Количество охваченных строк в диапазоне указанного в аргументе, должно совпадать с количеством строк в таблице. А также нумерация должна начинаться со второй строки!

Скачать пример поиска значения в столбце и строке Excel

Читайте также: Поиск значения в диапазоне таблицы Excel по столбцам и строкам

По сути содержимое диапазона нас вообще не интересует, нам нужен просто счетчик строк. То есть изменить аргументы на: СТРОКА(B2:B11) или СТРОКА(С2:С11) – это никак не повлияет на качество формулы. Главное, что в этих диапазонах по 10 строк, как и в таблице. И нумерация начинается со второй строки!