ПРОБЛЕМЫ С ФУНКЦИЯМИ ПОИСКА
Поиск значений в списках данных
Проблема
Я знаю, что Excel не является системой управления базами данных… это не Access. Понятно, что чудес ждать не приходится, но я все же надеюсь, что Excel поможет найти нужное значение в таблице. Например, у меня есть список торговых представителей, отсортированный по коду работника. Если я получаю отчет с кодом работника, нельзя ли узнать, кому принадлежит этот код, не «водя пальцем» по списку?

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

Первая функция, используемая для поиска в листе, — ПРОСМОТР. Принцип ее работы в общих чертах выглядит так: сначала функция ищет заданное значение (допустим, в столбце A). Определив, какая запись содержит заданное значение в столбце A, функция обращается (например) к столбцу C той же записи, читает содержимое ячейки и «возвращает» его (или отображает в ячейке, содержащей формулу).

Функция ПРОСМОТР имеет следующий синтаксис:

=ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)

· искомое_значение — ячейка или конкретное значение, которое ищется в таблице;

· просматриваемый_вектор — диапазон, в котором ищется искомое_значение;

· вектор_результатов — диапазон, из которого берется ассоциированное значение.

Проще говоря, вы сообщаете функции ПРОСМОТР, что нужно искать, где искать и где находится ассоциированное значение, которое вас интересует.

Возможности функции ПРОСМОТР несколько ограничены тем, что диапазоны просматриваемый_вектор и вектор_результатов могут содержать только одну строку или один столбец. Также существует вероятность получить неверный результат: если функция ПРОСМОТР не может найти искомое значение в диапазоне просматриваемый_вектор, она использует максимальное значение, меньшее либо равное искомому...

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

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

· искомое_значение — искомые данные, которыми могут быть конкретное значение , ссылка на ячейку со значением или текстовая строка, причем функция ВПР всегда ищет искомое значение в первом столбце массива;

· таблица — диапазон, в котором Excel ищет искомое значение (на рис. 4.8 это диапазон A2:B21);

· номер_столбца — номер столбца в диапазоне таблица, в котором функция ищет ассоциированное и возвращает значение (если номер столбца равен 1, функция возвращает значение из левого столбца, если он равен 2 — из второго столбца, и т. д.; если номер столбца превышает количество столбцов в диапазоне, переданном в аргументе таблица, функция ВПР отображает код ошибки #ССЫЛКА!);

· интервальный_просмотр — необязательный последний аргумент (если он равен TRUE или не указан, ВПР ведет себя как функция ПРОСМОТР и возвращает наибольшее значение, меньшее величины искомое_значение либо равное ей; если он равен FALSE, функция ищет точное совпадение или отображает код ошибки #Н/Д!).

Функция ВПР, эквивалентная упоминавшейся выше формуле =ПРОСМОТР(C3;A2: A21;B2:B21), выглядит так:

=ВПР(C3;A2:B21;2;ИСТИНА)

Кстати говоря, ВПР игнорирует различия в регистре символов.

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

Зачем использовать номер столбца, равный 1?

Вероятно, вы заметили, что функция ВПР позволяет возвращать значения из первого столбца переданной таблицы. Но зачем это нужно, ведь именно в первом столбце мы ищем исходное значение? Приведу лишь один из примеров: допустим, первый столбец содержит список дат вида 15.3.2005, 17.3.2005, 28.3.2005 и т. д. Формула =ВПР(C3;A2:B200;1;ИСТИНА) вернет первую дату, ближайшую к дате в ячейке C3, но не позднее ее. Например, если С3 содержит дату 27.3.2005, то формула =ВПР(C3;A2:B00;1;ИСТИНА) вернет 17.3.2005.

Синтаксис вызова функции ГПР:

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

Функция работает точно так же, как ВПР, но с разворотом на 90°:

· искомое_значение — данные, которые ищутся в первой строке таблицы;

· таблица — диапазон, в котором Excel ищет искомое_значение;

· номер_строки — номер строки диапазона таблица, в котором функция ищет и возвращает ассоциированное значение, — другими словами, если в первой строке листа перечисляются коды работников, во второй строке — имена, а в третьей — зарплата, то по значению 2 будет проведен поиск имени, а по значению 3 — зарплаты (если номер строки меньше 1, функция ГПР выдает ошибку #ЗНАЧ!, а если он больше количества строк в диапазоне, переданном в аргументе таблица, выводится код ошибки #ССЫЛКА!);

· интервальный_просмотр — если аргумент равен TRUE или не указан, функции ГПР разрешается поиск приблизительных совпадений; если он равен FALSE, функция ищет точное совпадение или отображает код ошибки #Н/Д!.

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

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

Функция ИНДЕКС находит адрес ячейки, удовлетворяющей поставленному критерию, и имеет следующий синтаксис вызова:

=ИНДЕКС(ссылка;номер_строки;номер_столбца;номер_области)

· ссылка — ссылка на один или несколько диапазонов со значениями, которые функция должна найти и вернуть. Если аргумент состоит из несмежных групп ячеек, заключите ссылки в круглые скобки, например: (A1:B6;C3:D8;F1:G6);

· номер_строки — номер строки диапазона, определяемого аргументом ссылка, в которой функция должна искать значение;

· номер_столбца — номер столбца диапазона, определяемого аргументом ссылка, в котором функция должна искать значение;

· номер_области — номер диапазона, определяемого аргументом ссылка, в котором функция должна искать значение, при этом первому выделенному или введенному поддиапазону присваивается номер 1, второму — 2 и т. д., а если аргумент номер_области не указан, используется область 1 (например, если функция ИНДЕКС осуществляет поиск в диапазонах A10:B14, C12:D16 и F14:G18, то поддиапазон A10:B14 считается областью 1, C12:D16 — областью 2, а F14:G18 — областью 3).

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

Функция ПОИСКПОЗ имеет следующий синтаксис вызова:

=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления)

· искомое_значение — данные, которые ищутся в таблице;

· просматриваемый_массив — смежный диапазон ячеек, содержащий набор искомых значений;

· тип_сопоставления — число –1, 0 или 1, причем если аргумент тип_сопоставления равен 1, функция ПОИСКПОЗ ищет наибольшее значение, меньшее либо равное искомому, и в этом случае аргумент просматриваемый_массив должен быть отсортирован по возрастанию; если аргумент тип_сопоставления равен 0, функция находит первое точное совпадение (элементы массива могут следовать в произвольном порядке); если аргумент тип_сопоставления равен –1, функция ищет наименьшее значение, большее либо равное искомому (в этом случае аргумент просматриваемый_массив должен быть отсортирован по убыванию), а если аргумент тип_сопоставления не указан, Excel считает, что он равен 1.

Функции ИНДЕКС и ПОИСКПОЗ хорошо работают в сочетании друг с другом — функция ПОИСКПОЗ определяет местонахождение ячейки, необходимой функции ИНДЕКС для проведения поиска. Для примера рассмотрим набор данных на рис. 7.

Рис. 7. Просмотр справа налево? Это возможно,
но не с использованием функции ВПР

Если вы хотите найти первую запись, у которой доход превышает сумму в 500 000 долларов, сначала отсортируйте список так, чтобы столбец доходов был упорядочен по убыванию, а затем воспользуйтесь формулой =ИНДЕКС(B3:17;ПОИСКПОЗ(F3; D3:D17,–1)) для определения отдела и формулой =ИНДЕКС(B3:B17;ПОИСКПОЗ(F3;D3: D17;–1)) для определения года. Далее приводятся краткие пояснения относительно того, как работают эти составные формулы.

· Первый аргумент функции ИНДЕКС определяет диапазон с потенциальными возвращаемыми значениями C3:C17 (отделы фирмы по продаже автомобилей).

· Функция ИНДЕКС получает свой второй аргумент от функции ПОИСКПОЗ. Функ­ция ПОИСКПОЗ ищет в диапазоне D3:D17 наименьшее значение, большее значения в ячейке F3, и возвращает функции ИНДЕКС его позицию в диапазоне
(в данном случае 5).

· Функция ИНДЕКС, которая к этому моменту имеет вид =ИНДЕКС(C3:C17;5), возвращает значение из ячейки C7 — пятой ячейки отсортированного диапазона.

Исправление ошибочного результата функции ВПР в Excel 97
Проблема
Я храню информацию о заказах своей компании в книге Excel 97, преобразованной из Lotus 1-2-3. Сведения о заказах хранятся на одном листе, а сведения о продуктах — на другом, но при попытке найти продукт по номеру заказа я иногда получаю неверный результат. Что происходит?

Решение
В Excel 97 имеется ошибка, которая проявляется, когда таблица просмотра и ячейка с формулой, содержащей функцию ВПР, находятся на разных листах, и при этом используется режим вычисления формул по правилам Lotus 1-2-3 (программы Excel и Lotus вычисляют формулы ВПР по разным правилам). Чтобы решить проблему, щелкните на листе с таблицей просмотра, выполните команду Сервис4Параметры, перейдите на вкладку Переход и сбросьте флажок Производить вычисления по правилам Lotus 1-2-3.

Текст и числа при просмотре
Проблема
В фирме, где я работаю, я храню информацию о деталях к автомашинам. Конечно, коды деталей определяются производителем, причем многие коды (такие, как A3000 и T1648B) содержат буквы. Я только что импортировал свой список кодов в Excel (рис. 8.) и собирался по коду детали определить название и получить другую информацию, но тут же получил ошибку #Н/Д. Я ввожу A2000 вместо A3000 в ячейке поиска, но почему формула выдает ошибку? Ведь если ввести 9815 вместо 9816, формула ВПР возвращает строку «Свеча зажигания» — название из предыдущей ячейки таблицы. Почему же формула не останавливается на детали с кодом 9816, записи, расположенной перед строкой A3000, и не возвращает строку «Фара»? Ничего не понимаю.

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

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

Чтобы предотвратить эту ошибку, проще всего изначально отказаться от смешения текстовых и числовых значений в списке просмотра. Если это невозможно, перед импортом или вводом данных отформатируйте ячейки как текст (выполните команду Формат4Ячейки, перейдите на вкладку Число и выберите в списке Числовые форматы пункт Текстовый). Если данные уже находятся в книге, воспользуйтесь формулами массивов для текстовой интерпретации значений списка кодов деталей (изменение формата ячейки после ввода данных не поможет). В следующей формуле предполагается, что искомое значение находится в ячейке D2, а список кодов деталей — в ячейках A1:A3:

{=ВПР(ТЕКСТ(D2;"@");ТЕКСТ(A1:A3;"@");1)}

Помните, что для создания формулы массива необходимо нажать клавиши Ctrl+ Shift+Enter. Если просто нажать клавишу Enter, Excel выдаст ошибку #H/Д!.

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

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

Тем не менее комбинация функций ЕСЛИ и СОВПАД позволяет добиться желаемого результата. Допустим, вы работаете с листом, показанным на рис. 9.

Рис. 9. Иногда значения различаются только регистром символов

Попробуйте ввести в ячейке C7 формулу:

=ЕСЛИ(СОВПАД(B7;ГПР(B7;A1:E2;1;ЛОЖЬ))=ИСТИНА;

ГПР(B7;A1:E2;2;ЛОЖЬ);"Не совпадает")

Затем введите в ячейке B7 строку джо — формула вернет текст Не совпадает, потому что содержимое ячейки D1 не совпадает с введенным текстом с точностью до регистра символов. Но если ввести в ячейке B7 строку Джо, в ячейке отображается почасовая ставка Джо — значение $14.00.

Аналогичное решение может использоваться с функцией ВПР при вертикальной ориентации данных (рис. 4.13). В этом случае формула принимает вид:

=ЕСЛИ(СОВПАД(A7;ВПР(A7;A1:B5;1;ЛОЖЬ))=ИСТИНА;

ВПР(A7;A1:B5;2;ЛОЖЬ);"Не совпадает")

Рис. 10. Данные располагаются по столбцам