Поиск в программе Microsoft Excel

Поиск в программе Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Источник:
    http://lumpics.ru/how-to-search-in-excel/

    Как искать в Excel: поиск слов и ячеек в таблицах

    Программа Excel ориентирована на ускоренные расчеты. Зачастую документы здесь состоят из большого ко.

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

    Читайте также  Создание папок с именами ячеек Excel - VBA

    Поиск слов

    Документы часто имеют много страниц, тогда встает вопрос о том, как в Е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 найти слово при использовании фильтра, потребуется изучить следующий алгоритм действий:

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

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

    Источник:
    http://freesoft.ru/blog/kak-iskat-v-excel-poisk-slov-i-yacheek-v-tablitsakh

    Поиск по всем книгам

    Данная функция является частью надстройки MulTEx

    • Описание, установка, удаление и обновление
    • Полный список команд и функций MulTEx
    • Часто задаваемые вопросы по MulTEx
    • Скачать MulTEx

    Вызов команды:
    MulTEx -группа Ячейки/ДиапазоныПоиск по всем книгам

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

    Найти: — указывается значение для поиска. Может быть любым символом или словом. Если в текущем сеансе Excel было введено для поиска более одного значения, то поле будет выпадающим списком, из которого можно выбрать любое из ранее искомых значений или ввести новое. Небольшая особенность — при указании пустого значения в поле «Найти:» и выполнения поиска появится предупреждение:

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

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

    Примечание: при произведении замены по Гиперссылкам не допускается применение подстановочных символов(звездочка и вопросительный знак). Для всех остальных значений Области поиска их применение допускается и корректно обрабатывается.

    Искать:

    • на листе — поиск указанного значения осуществляется только на активном листе;
    • в книге — поиск осуществляется во всех листах активной книги(скрытые листы не просматриваются);
    • во всех книгах — поиск осуществляется по всем открытым книгам на видимых листах(скрытые листы не просматриваются).

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

      • по строкам — поиск ведется слева-направо сверху-вниз — т.е. сначала просматривается каждая ячейка строки, а потом осуществляется переход на следующую строку;
      • по столбцам — поиск ведется сверху-вниз слева-направо — сначала просматривается каждая ячейка столбца, а потом осуществляется переход на следующий столбец.

    Выбранный метод влияет на вывод результатов поиска(при выборе Найти все) и на переход по найденным ячейкам(Найти далее).

    Область поиска:

    • формулы — поиск будет осуществляться по действительному содержимому ячейки(то, которое отображается в строке формул при активации ячейки), а не по отображаемому значению;
    • значения — поиск будет осуществляться отображаемому содержимому ячейки;
    • примечания — поиск осуществляется исключительно в тексте примечаний в ячейках;
    • гиперссылки — поиск/замена осуществляется исключительно в тексте гиперссылок, созданных через меню(Правая кнопка мыши на ячейке-Гиперссылка). >>Читать подробнее про гиперссылки

    Везде(по умолчанию) — поиск будет осуществляться по всему листу.

    Только выделенный диапазон — поиск будет осуществляться по выделенному диапазону. Диапазон задается один для всех листов, исходя из выделенного на активном листе диапазона.

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

    Найти далее — будет активироваться каждое найденное значение по кругу.

    Читайте также  Как написать изогнутый текст в Microsoft Word

    Выделить все — будут выделены все ячейки с найденными значениями на всех листах всех книг. Если в списке Искать выбрано на листе — будут выделены значения только на активном листе; если выбрано в книге — то на всех листах книги; если во всех книгах — значения будут выделены на всех листах всех открытых книг. Стандартно Excel не позволяет сделать такое. Для чего это может быть нужно: если необходимо проделать какое-либо однотипное действие(удалить все данных, изменить заливку, шрифт и т.п.) в найденных ячейках.

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

    Источник:
    http://www.excel-vba.ru/multex/poisk-po-vsem-knigam/

    Как создать поиск значений в Excel функцией ВПР по нескольким листам?

    Добрый день!

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

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

    =ВПР (C3 ;ЕСЛИ (ЕНД (ВПР (C3 ;Таблица2!C3:D7 ;2; 0)); Таблица3! C3:D7 ;Таблица2! C3:D7 );2; 0).

    Но такой вариант работает только с 2 таблицами, а в случае, когда листов больше, нужно увеличивать количество вложений для функции ЕСЛИ. Но при этом:

    • во-первых, если много листов, то есть огромный шанс что длина формулы будет больше допустимого размера и перестанет работать;
    • во-вторых, это просто непрактично, так как при работе такой мега-формулы возникает значительно риск ошибок и при изменениях придётся переделывать формулу.

    Но, как всегда, выход есть. Рассмотрим небольшую хитрость с помощью, которой и будем искать в нужных листах. Начнем работу с создания перечня листов нашей книги, где будем производить поиск значений. В нашем случае это диапазон $E$3:$E$7. Теперь для получения значения в столбик «Найденная стоимость» согласно условию в столбике «Номенклатуру которую ищем» нам нужна формула:

    Как видите, формула выделена фигурными скобками, это означает, что её необходимо вводить как формулу массива с помощью горячего сочетания клавиш Ctrl+Shift+Enter. Это самое главное условие правильной работы этой формулы в других случаях она не будет работать. Формула объемная и требует объяснения принципа её работы. Функция ДВССЫЛ необходима, что бы конвертировать текстовые отображения ссылок на листы нашей книги в действительные. Сам принцип работы функции ДВССЫЛ, я описывать не буду, рассмотрим только необходимую формулу для этапа нашего вычисления: СЧЁТЕСЛИ (ДВССЫЛ («’»&$E$3:$E$7 &»‘! C1:C50″); A3).

    Как следствие, при вычислении этого блока у нас формируется массив из некоторого количества значений, которые мы ищем, и которые повторяются на листах нашего списка, и имеет вид: СЧЁТЕСЛИ(<2;0;0;0>;A3). О работе функции СЧЁТЕСЛИ я писал отдельно и более подробно.

    Следующим рассматриваемым блоком нашей композиции будет формула: ПОИСКПОЗ (ИСТИНА; СЧЁТЕСЛИ (ДВССЫЛ («’»&$E$3:$E$7 &»‘! C1:C50″); A3)>0;0), которая и работает с указанным выше блоком такого вида: ПОИСКПОЗ (ИСТИНА; СЧЁТЕСЛИ (<2;0;0;0>; A3)>0;0). Вследствие чего мы узнаем, какую позицию занимает имя листа в нашем массиве списке листов $E$3:$E$7. Теперь же при помощи функции ИНДЕКС мы получаем название листа, и можем применить его имя в структуре функции ДВССЫЛ, а она передаст полученное значение уже далее функции ВПР. Пошагово это будет выглядеть так:

    Ну, вот мы и получили универсальную формулу, которая производит поиск значений в Excel и является очень гибкой и удобной. В случаях, когда возникнет необходимость добавить в рабочую книгу еще листы с таблицами, то необходимо всего на всего прописать их в списке рабочих листов $E$3:$E$7, изменив предварительно ее размер или попросту изначально сделать ее динамическим диапазоном, и править формулу будет не нужно.

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

    <=ИНДЕКС ($E$3:$E$7; ПОИСКПОЗ (ИСТИНА; СЧЁТЕСЛИ (ДВССЫЛ («'»&$E$3 :$E$7&"'! C1:C50"); A3) >0;0))>

    Источник:
    http://topexcel.ru/kak-sozdat-poisk-znachenij-v-excel-funkciej-vpr-po-neskolkim-listam/

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

    Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

    В этой статье приведены пошаговые инструкции по поиску данных в таблице (или диапазоне ячеек) с помощью различных встроенных функций Microsoft Excel. Для получения одного и того же результата можно использовать разные формулы.

    Создание образца листа

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

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

    Определения терминов

    В этой статье для описания встроенных функций Excel используются указанные ниже условия.

    Вся таблица подстановки

    Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».

    Просматриваемый_массив
    -или-
    Лукуп_вектор

    Диапазон ячеек, которые содержат возможные значения подстановки.

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

    3 (третий столбец в инфо_таблица)

    Ресулт_аррай
    -или-
    Ресулт_вектор

    Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.

    Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.

    Это ссылка, на основе которой вы хотите основать смещение. Топ_целл должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция СМЕЩ возвращает #VALUE! значение ошибки #ИМЯ?.

    Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение «5» в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).

    Функция Просмотр находит значение в одной строке или столбце и сопоставляет его со значением в той же позицией в другой строке или столбце.

    Ниже приведен пример синтаксиса формулы подСТАНОВКи.

    = Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)

    Следующая формула находит возраст Марии на листе «образец».

    = ПРОСМОТР (E2; A2: A5; C2: C5)

    Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как «Мария» находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).

    Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.

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

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

    Ниже приведен пример синтаксиса формулы ВПР :

    = ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)

    Следующая формула находит возраст Марии на листе «образец».

    = ВПР (E2; A2: C5; 3; ЛОЖЬ)

    Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется «3» в качестве Колумн_индекс (столбец C). Так как «Мария» находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).

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

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

    INDEX () и MATCH ()

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

    Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:

    = Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)

    Следующая формула находит возраст Марии на листе «образец».

    = ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)

    Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как «Мария» находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).

    Обратите внимание Если ни одна из ячеек в аргументе «число» не соответствует искомому значению («Мария»), эта формула будет возвращать #N/А.
    Чтобы получить дополнительные сведения о функции индекс , щелкните следующий номер статьи базы знаний Майкрософт:

    СМЕЩ () и MATCH ()

    Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.

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

    = СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)

    Эта формула находит возраст Марии на листе «образец».

    = СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)

    Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как «Мария» находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).

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

    Источник:
    http://support.microsoft.com/ru-ru/office/%D0%BF%D0%BE%D0%B8%D1%81%D0%BA-%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85-%D0%B2-%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%D0%B5-%D0%B8%D0%BB%D0%B8-%D0%B4%D0%B8%D0%B0%D0%BF%D0%B0%D0%B7%D0%BE%D0%BD%D0%B5-%D1%8F%D1%87%D0%B5%D0%B5%D0%BA-%D1%81-%D0%BF%D0%BE%D0%BC%D0%BE%D1%89%D1%8C%D1%8E-%D0%B2%D1%81%D1%82%D1%80%D0%BE%D0%B5%D0%BD%D0%BD%D1%8B%D1%85-%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D0%B9-excel-6777ec9b-6191-426a-8d45-196ecbf2a186

    Функции поиска данных: разыскивается информация!

    Привет, друзья. Как часто вам приходится для какого-то значения искать соответствие в таблице Эксель? Например, нужно в справочнике найти адрес человека, или в прайсе – цену товара. Если такие задачи встречаются – этот пост именно для вас!

    Я выполняю подобные процедуры каждый день и без описанных ниже функций мне действительно пришлось бы туго. Берите на заметку и применяйте их в работе!

    Поиск в таблице Эксель, функции ВПР и ГПР

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

    Синтаксис функции ВПР такой: =ВПР( Искомое_значение; таблица_для_поиска; номер_выводимого_столбца; [тип_сопоставления]) . Рассмотрим аргументы:

    • Искомое значение – значение, которое будем искать. Это обязательный аргумент;
    • Таблица для поиска – тот массив ячеек, в котором будет поиск. Столбец с искомыми значениями должен быть первым в этом массиве. Это тоже обязательный аргумент;
    • Номер выводимого столбца – порядковый номер столбца (начиная с первого в массиве), из которого функция выведет данные при совпадении искомых значений. Обязательный аргумент;
    • Тип сопоставления – выберите «1» (или «ИСТИНА») для нестрогого совпадения, «0» («ЛОЖЬ») – для полного совпадения. Аргумент необязателен, если его упустить – будет выполнен поиск нестрогого совпадения.

    Поиск точного совпадения с помощью ВПР

    Посмотрим на примере, как работает функция ВПР, когда выбран тип сопоставления «ЛОЖЬ», поиск точного совпадения. В массиве В5:Е10 указаны основные средства некой компании, их балансовая стоимость, инвентарный номер и место расположения. В ячейке В2 указано наименование, для которого нужно в таблице найти инвентарный номер и поместить его в ячейку С2 .

    Функция ВПР в Excel

    Запишем формулу: =ВПР(B2;B5:E10;3;ЛОЖЬ) .

    Здесь первый аргумент указывает, что в таблице нужно искать значение из ячейки В2 , т.е. слово «Факс». Второй аргумент говорит, что таблица для поиска — в диапазоне В5:Е10 , а искать слово «Факс» нужно в первом столбце, т.е. в массиве В5:В10 . Третий аргумент сообщает программе, что результат расчета содержится в третьем столбце массива, т.е. D5:D10 . Четвёртый аргумент равен «ЛОЖЬ», т.е. требуется полное совпадение.

    И так, функция получит строку «Факс» из ячейки В2 и будет искать его в массиве В5:В10 сверху вниз. Как только совпадение будет найдено (строка 8), функция вернёт соответствующее значение из столбца D , т.е. содержимое D8 . Именно это нам и требовалось, задача решена.

    Если искомое значение не будет найдено, функция вернёт ошибку #Н/Д.

    Поиск неточного совпадения с помощью ВПР

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

    В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:

    Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА) . Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

    Нестрогий поиск ВПР в Excel

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

    Функция ГПР имеет такой же синтаксис, как и ВПР, но ищет результат не в столбцах, а в строках. То есть, просматривает таблицы не сверху вниз, а слева направо и выводит заданный номер строки, а не столбца.

    Поиск данных с помощью функции ПРОСМОТР

    Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения) :

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

    При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10) . Задача успешно решена!

    Функция «ПРОСМОТР» в Microsoft Excel

    Поиск по относительным координатам. Функции ПОИСКПОЗ и ИНДЕКС

    Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

    Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [Тип сопоставления]). Аргументы функции:

    • Искомое значение – обязательный аргумент
    • Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
    • Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.

    Позиция необходимого значения найдена, теперь можно вывести его на экран с помощью функции ИНДЕКС(Массив; Номер_строки; [Номер_столбца]) :

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

    Теперь скомбинируем эти функции, чтобы получить результат:

    Функции ПОИСКПОЗ и ИНДЕКС в Эксель

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

    Источник:
    http://officelegko.com/2016/05/06/funktsii-poiska-dannyih-razyiskivaetsya-informatsiya/