Как работать с функцией ВПР в Эксель: инструкция для новичков

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

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

Первая – это список предметов, единицы их измерения и количество.

Вторая – содержит тот же список, но в ней ещё есть цена за 1 штуку.

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

Этапы работы (инструкция):

  1. Для начала в первую Excel таблицу добавляются два столбца: «Цена за 1 шт.» и «Общая сумма».
  2. Отметить верхнее поле в новом.
  3. Выбрать раздел формулы, и нажать «Вставить функцию».
  4. Из предложенных категорий Excel отметить «Ссылки и массивы».
  5. Найти ВПР, и нажать «ОК».
  6. Заполнить открывшееся окно «Аргументы».

– это товары из первой таблицы, которые необходимо будет определить во второй. Их значение выставляется таким образом: X: Y, где Х – это адрес первой ячейки столбика с товарами, а Y – последней. В рассматриваемой это А2 и А5.

– в этом поле будет стоимость из второго листа с данными. Чтобы её проставить следует кликнуть по строке, затем перейти на страницу с суммой, и выделить нужное (А2 – В5).

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

Фиксирование информации производится путём нажатия горячей клавиши F4, на выделенной строке. Если всё сделано правильно там же появится значок $.

Номер — это строка в которой должна быть информация о том, что будет переноситься из другой таблицы. В рассматриваемом случае – это второй столбец (2).

Интервальный просмотр – логическое значение Excel, где точно это ЛОЖЬ, а приближённо – ИСТИНА. Если пользователю нужны точные, он должен написать «ЛОЖЬ».

В конечном итоге, окно «Аргументы» выглядит так:

  1. Нажать «ОК».

Нужное значение появится в ячейке. Чтобы опция сработала на все товары, достаточно растянуть её.

Теперь, чтобы сосчитать общую стоимость предмета, достаточно вставить соответствующую формулу в ячейку Е2, и также растянуть её на все продукты. Конец инструкции.

Дополнительная информация

  • совпадение искомых данных может быть точным и приблизительным;
  • при сопоставлении по приблизительной точности данных убедитесь, что данные в таблицах отсортированы в убывающем порядке (от большого к малому). Иначе, результат сопоставления будет некорректным;
  • при сопоставлении данных по приблизительной точности: – если функция не найдет искомое значение, она выдаст наибольшую величину, которая будет меньше чем значения поиска; – если функция при сопоставлении выдает ошибку #N/A, то искомое значение меньше чем самая маленькая величина в искомом диапазоне; – вы можете использовать подстановочные знаки для искомых значений.

Особенности использования формулы ВПР в Excel

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

1. Первую особенность можно считать общей для функций, которые используются для многих ячеек путем прописывания формулы в одной из них и дальнейшим копированием в остальные. Здесь нужно обращать внимание на относительность и абсолютность ссылок. Конкретно в ВПР критерий (первое поле) должно иметь относительную ссылку (без знаков $), так как у каждой ячейки свой собственный критерий. А вот поле «Таблица» должно иметь абсолютную ссылку (адрес диапазона прописывается через $). Если этого не сделать, то при копировании формулы диапазон «поедет» вниз и многие значения просто не найдутся, так как искать будет негде.

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

3. Функция ВПР из диапазона с искомыми данными выдает первое сверху значение. Это значит, что, если во второй таблице, откуда мы пытаемся «подтянуть» некоторые данные, присутствуют несколько ячеек с одинаковым критерием, то в рамках выделенного диапазона ВПР захватит первое сверху значение. Об этом следует помнить. К примеру, если мы хотим к цене товара подтянуть количество из другой таблицы, а там этот товар встречается несколько раз (в нескольких строках), то к цене подтянется первое сверху количество.

4. Последний параметр формулы, который 0 (нуль), ставить нужно обязательно. Иначе формула может криво работать.

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

Разорвать связи

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

Это позволит удалить сразу все внешние ссылки.

Что такое функция ВПР в Эксель – область применения

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

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

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

Примеры для наглядности: в таблицах 1,2 – исходные данные, таблице 3 – что должно получиться.

Исходные данные таблица 1

Ф. И. О. Штраф
Петров 12 000,00 ₽
Сидоров 200,00 ₽
Иванов 38 000,00 ₽

Исходные данные таблица 2

Ф. И. О. З.П.
Иванов 20 000 ₽
Петров 19 000 ₽
Сидоров 21 000 ₽

Объединенные данные таблица 3

Ф. И. О. З.П. Штраф
Иванов 20 000 ₽ 38 000 ₽
Петров 19 000 ₽ 12 000 ₽
Сидоров 21 000 ₽ 200 ₽

Если вдруг Вы еще не знали, то советуем прочитать о том, как объединить ячейки в Exсel.

ВПР из другой таблицы в Google Таблицах с помощью IMPORTRANGE

ИМПОРТРАНЖ на помощь

Использование IMPORTRANGE с функцией VLOOKUP дает вам безграничные возможности для объединения данных в рабочих книгах Google Sheets. Как вы, надеюсь, заметили, формулы интуитивно понятны, а использование подстановочных знаков лучше, чем в Excel. Единственное, что вы можете пропустить при работе с несколькими книгами, – это параллельное представление, которое есть только в Microsoft Excel.

Читайте также:  Пример функции СУММЕСЛИ для суммирования в Excel по условию

Часто ли вам нужно получать данные из нескольких книг одновременно? Вы использовали IMPORTRANGE в других таблицах? Дайте нам знать в комментариях ниже.

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

В Google Sheets есть несколько функций, которые позволяют вам ссылаться или рисовать данные из разных мест – другого листа в одной электронной таблице, другой электронной таблицы в целом или даже файлов CSV, ссылок и страниц HTML.

Эта статья кратко объяснит необходимые функции.

Как пользоваться ВПР в Excel?

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

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

Пример №1

Имеется сводная таблица с наименованиями товаров, датами и объёмами поставок и сроками годности. Требуется, не прибегая к опции поиска, вывести в любую свободную ячейку информацию о конкретном продукте.

Решение:

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

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

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

  • Выделить нажатием ячейку, следующую за наименованием товара, и перейти на вкладку Excel «Формулы».

  • Щёлкнуть по кнопке «Вставить функцию» — самой крайней слева в открывшейся «ленте».

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

  • Пролистать перечень вниз, выделить строчку «ВПР» и щёлкнуть по кнопке «ОК».

  • Другой способ вызвать функцию VLOOKUP — на той же вкладке «Формулы» открыть выпадающий список «Ссылки и массивы» и выбрать пункт «ВПР».

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

  • Во второй строчке того же окна — зажав левую клавишу мыши, выделить исходную таблицу Excel целиком.

  • В третьей — порядковый номер столбца, в котором требуется осуществить поиск…

  • …Или сам столбец, выделив его аналогичным образом.

  • В четвёртой — указать, требуется точный поиск по таблице Excel (значение «0») или программа должна будет подобрать ближайший подходящий параметр (значение «1»). В этом примере и при решении большинства других задач, связанных с применением функции ВПР, нужны точные совпадения — следовательно. Необходимо вписать в текстовое поле «0», а затем нажать на «ОК».

  • Если значение в строке появилось, но оно не соответствует ожидаемому, тогда следует проверить правильность установленного формата ячейки.

  • В нашем примере в результате должно получиться не абсолютное значение, а дата. Чтобы привести данные в требуемый вид, необходимо щёлкнуть по «неправильной» ячейке правой кнопкой мыши и выбрать в открывшемся контекстном меню пункт «Формат ячеек»…

  • …После чего найти требуемый — «Дата» — и кликнуть по кнопке «ОК».

  • Готово! Ячейка приобрела приемлемый для работы вид — таким он и будет оставаться вплоть до нового изменения.

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

  • Чтобы добиться требуемого результата, следует или повторить для новой строчки все перечисленные манипуляции, или выделить уже готовую и скопировать в верхнем текстовом поле Excel формулу…

  • …После чего вставить её в нужную строку…

  • …Не забыв заменить номер столбца, в котором на этот раз должен быть осуществлён поиск — теперь это не «3», а «4».

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

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

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

  • После чего, вновь вызвав его, выбрать пункт «Специальная вставка», отметить кружочком в разделе «Вставить» пункт «Значения» и кликнуть по кнопке «ОК».

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

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

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

Пример №2

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

Решение:

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

  • Теперь нужно добавить к одной из таблиц Excel (обычно — более содержательной) новый столбец и назвать его в соответствии с переносимыми данными.

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

  • В первой строке диалогового окна следует указать наименование позиции из первой таблицы — той, в которую добавляются данные.
Читайте также:  Как преобразовать дату в кварталы Excel для бюджетного года

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

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

  • Щёлкнув по кнопке «ОК», пользователь получит в первой строке искомый результат.

  • Теперь не нужно подставлять формулу в каждую строку — достаточно зажать левой клавишей мыши крестик в правом нижнем углу ячейки и «растянуть» её на все позиции — данные будут подставлены автоматически и в нужном порядке.

  • Соответствия сохранятся даже при пересортировке строк в первой таблице.

  • А кроме того, в неё не будут включены «лишние» данные, отсутствующие во второй.

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

БЫСТРОЕ СРАВНЕНИЕ ДВУХ ТАБЛИЦ С ПОМОЩЬЮ ВПР

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

Новый прайс.

    1. В старом прайсе делаем столбец «Новая цена».

    Добавить колонку новая цена в стаырй прайс.

    1. Выделяем первую ячейку и выбираем функцию ВПР. Задаем аргументы (см. выше). Для нашего примера: .

    Это значит, что нужно взять наименование материала из диапазона А2:А15, посмотреть его в «Новом прайсе» в столбце А. Затем взять данные из второго столбца нового прайса (новую цену) и подставить их в ячейку С2.

    Заполнение новых цен.

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

    СУММПРОИЗВ – находит текстовые значения с учётом регистра, но возвращает только числа

    Как Вы уже поняли из заголовка, СУММПРОИЗВ (SUMPRODUCT) это ещё одна функция Excel, которая поможет выполнить поиск с учётом регистра, но возвратит только числовые значения. Если этот вариант Вам не подходит, то можете сразу переходить к связке ИНДЕКС+ПОИСКПОЗ, которая даёт решение на любой случай и для любых типов данных.

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

    Функция СУММПРОИЗВ перемножает элементы заданных массивов и возвращает сумму результатов. Синтаксис имеет такой вид:

    Раз нам необходим поиск с учётом регистра, используем функцию СОВПАД (EXACT) из предыдущего примера в качестве одного из множителей:

    Как Вы помните, СОВПАД сравнивает значение ячейки F2 со всеми элементами в столбце A. В случае, если найдено точное совпадение, возвращает ИСТИНА (TRUE), иначе – ЛОЖЬ (FALSE). В математических операциях Excel принимает ИСТИНА (TRUE) за 1, а ЛОЖЬ (FALSE) за , далее СУММПРОИЗВ перемножает эти цифры и суммирует полученные результаты.

    Нули не считаются, поскольку при умножении они всегда дают . Давайте посмотрим подробнее, что происходит, когда точное совпадение в столбце A найдено и возвращена 1. Функция СУММПРОИЗВ умножает число в столбце B на 1 и возвращает результат – точно такое же число! Так происходит потому, что результаты других произведений – нули, и они не влияют на получившуюся в итоге сумму.

    К сожалению, функция СУММПРОИЗВ не может работать с текстовыми значениями и датами, так как их нельзя перемножить. В этом случае Вы получите сообщение об ошибке #ЗНАЧ! (#VALUE!), как в ячейке F4 на рисунке ниже:

    4 способа сделать ВПР с учетом регистра в Excel - Информационные технологии

    ОГРАНИЧЕНИЯ: Возвращает только числовые значения.

    Функция впр в excel

    Для удобства просмотра я перенес Таблицу №2 на первый лист. Все совпало, протягиваем или копируем формулу в ячейки D5 и D6, Таблице №1 добавляем название столбца- Цена товара. Вот так с помощью функции впр мы совместили данные из двух таблиц, находящихся на разных листах, хотя можно и на одном листе и даже в разных книгах.

    ВПР в Excel

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

    В этом видео подробно показано, как использовать функцию ВПР в Excel:

    Рекомендуем смотреть видео в полноэкранном режиме, в настойках качества выбирайте 1080 HD, не забывайте подписываться на канал в YouTube, там Вы найдете много интересного видео, которое выходит достаточно часто. Приятного просмотра!

    Функция ВПР и выпадающий список

    Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена.

    Сначала сделаем раскрывающийся список:

    1. Ставим курсор в ячейку Е8, где и будет этот список.
    2. Заходим на вкладку «Данные». Меню «Проверка данных».
    3. Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов.

    Теперь нужно сделать так, чтобы при выборе определенного материала в графе цена появлялась соответствующая цифра. Ставим курсор в ячейку Е9 (где должна будет появляться цена).

    1. Открываем «Мастер функций» и выбираем ВПР.
    2. Первый аргумент – «Искомое значение» — ячейка с выпадающим списком. Таблица – диапазон с названиями материалов и ценами. Столбец, соответственно, 2. Функция приобрела следующий вид: .
    3. Нажимаем ВВОД и наслаждаемся результатом.

    Изменяем материал – меняется цена:

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

    Фильтрация данных

    Рассмотрим пример. Предположим, что у нас имеется список сотрудников компании и мы хотим отфильтровать только тех сотрудников, у которых фамилии начинаются на конкретную букву (к примеру, на букву «п»):

    Список сотрудников

    Для начала добавляем фильтр на таблицу (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или нажимаем сочетание клавиш Ctrl + Shift + L).
    Для фильтрации списка воспользуемся символом звездочки, а именно введем в поле для поиска «п*» (т.е. фамилия начинается на букву «п», после чего идет произвольный текст):

    Применение фильтра

    Фильтр определил 3 фамилии удовлетворяющих критерию (начинающиеся с буквы «п»), нажимаем ОК и получаем итоговый список из подходящих фамилий:

    Фильтрация данных

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

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

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

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

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

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

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

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

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

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

    Сравнение данных двух таблиц Excel

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

    На двух листах мы имеем одинаковые таблицы с разными данными.

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

    В ячейку B2 введем функцию ВПР. В качестве первого аргумента укажем ячейку с месяцем на текущем листе, а диапазон выберем с листа «Цех1». Чтобы при копировании диапазон не смещался, нажмите F4 после выбора диапазона. Это сделает ссылку абсолютной.

    Растяните формулу на весь столбец.

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

    После копирования Вы получите сводный отчет с двух листов.

    Как правильно искать в таблицах Excel

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

    Выполните команду Главная ► Редактирование ► Найти и выделить ► Найти (или нажмите Ctrl+F), чтобы открыть диалоговое окно Найти и заменить. Если вам нужно заменить данные, то выберите команду Главная ► Редактирование ► Найти и выделить ► Заменить (или нажмите Ctrl+H). От того, какую именно команду вы выполните, зависит, на какой из двух вкладок откроется диалоговое окно.

    Если в открывшемся диалоговом окне Найти и заменить нажать кнопку Параметры, то отобразятся дополнительные параметры поиска информации (рис. 21.1).

    Рис. 21.1. Вкладка Найти диалогового окна Найти и заменить

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

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

    При поиске с помощью диалогового окна Найти и заменить можно использовать два подстановочных знака:

    • ? — соответствует любому символу;
    • * — соответствует любому количеству символов.

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

    Для поиска вопросительного знака или звездочки поставьте перед ними символ тильды (

    ). Например, следующая строка поиска находит текст *NONE*: -*N0NE

    * Чтобы найти символ тильды, поставьте в строке поиска две тильды.

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

    • Флажок Учитывать регистр — установите его, чтобы регистр искомого текста совпадал с регистром заданного текста. Например, если вы зададите в поиске слово иван и установите указанный флажок, то слово Иван в результатах поиска не отобразится.
    • Флажок Ячейка целиком — установите его, чтобы найти ячейку, которая содержит в точности тот текст, который указан в строке поиска. Например, набрав в строке поиска слово Excel и установив указанный флажок, вы не найдете ячейку, содержащую словосочетание Microsoft Excel.
    • Раскрывающийся список Область поиска — список содержит три пункта: значения, формулы и примечания. Например, если в строке поиска вы зададите число 900 и в раскрывающемся списке Область поиска выберете пункт значения, то в результатах поиска вы не увидите ячейку, содержащую значение 900, если оно получено при использовании формулы.

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

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

    Работа с датами может оказаться непростой, поскольку Excel поддерживает очень много форматов дат. Если вы ищете дату, к которой применено форматирование по умолчанию, Excel находит даты, даже если они отформатированы различными способами. Например, если ваша система использует формат даты m/d/y, строка поиска 10/*/2010 находит все даты в октябре 2010 года, независимо от того, как они отформатированы.

    Используйте пустое поле Заменить на, чтобы быстро удалить какую-нибудь информацию на рабочем листе. Например, введите — * в поле Найти и оставьте поле Заменить на пустым. Затем нажмите кнопку Заменить все, чтобы Excel нашел и убрал все звездочки на листе.

Ссылка на основную публикацию