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





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

Конспекты

Курсы

Практикум


Скачать файл
Ссылка на курс
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

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

Функция ВПР может научиться игнорировать формат ячеек и даже высчитывать номер столбца в таблице с помощью ПОИСКПОЗ. Как настроить функцию ВПР, чтобы выполнить поиск чисел, сохранённых как текст? Что делать, если дата выгружена из учётной системы в текстовом формате? Может ли ВПР искать и строку, и столбец таблицы одновременно?

ФАЙЛЫ ЭТОГО УРОКА по Excel МОЖНО СКАЧАТЬ ВНИЗУ СТРАНИЦЫ

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

Существует несколько способов решения этой задачи. Можно исправить формат числа в таблице, а можно ничего не переделывать в ячейках, но поколдовать с параметром Искомое_значение. Разберём все варианты.

ПЕРЕДЕЛКА ТЕКСТОВЫХ ЧИСЕЛ В ЧИСЛОВОЙ ФОРМАТ

В файле [VLookup prof 1 Number As Text.xlsx] на листе Числа  в Таблицу 1 нужно подставить цены товаров из Таблицы 2 по артикулам, указанным в ячейках A3:A6. Функция ВПР не находит цены по артикулам из ячеек A4 и A6, так как номер сохранён в текстовом формате. И об этом нам подсказывает зелёный уголок в верхнем левом углу ячейки.

Рисунок 1

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

Рисунок 2

Исправить формат ячеек можно, для этого нужно выделить все исправляемые ячейки, начиная с той, где есть первый зелёный уголок (если список большой, то щёлкнуть по первой ячейке A4 и нажать CTRL+SHIFT+стрелка_вниз), далее рядом с первой ячейкой появится значок с восклицательным знаком – нажимаем его и выбираем команду Преобразовать в число. Готово – функция ВПР показывает все результаты.

Рисунок 3

 

НАСТРОЙКА ИСКОМОГО ЗНАЧЕНИЯ без переделки таблицы

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

Рисунок 4

 

ПЕРЕДЕЛКА ЧИСЛА В ТЕКСТОВЫЙ ФОРМАТ

В файле [VLookup prof 1 Number As Text.xlsx] на листе Текст  смоделирована обратная ситуация, когда указанный числом артикул нужно поискать в таблице, в которой артикулы выгружены в текстовом формате. Будем выполнять функциональную переделку искомого значения в текст, а помогут в этом кавычки. Их нужно прицепить перед ссылкой на искомое значение или после неё.
Для сцепки использован знак амперсанд &, подробнее о работе с этим символом можно прочитать в соответствующем уроке.

Рисунок 5

 

КОМБИНАЦИЯ ДВУХ ВАРИАНТОВ В ОДНОЙ ФОРМУЛЕ

Если в исходной таблице могут встретиться оба варианта, как показано на листе Числа-Текст, функция ЕСЛИОШИБКА поможет применить оба сценария. И только если искомой последовательности знаков в таблице нет ни в каком из форматов, функция ВПР выведет в ячейку ошибку #Н/Д, как в ячейке B5.
Подробнее о работе с функцией ЕСЛИОШИБКА можно прочитать в соответствующем уроке.

Рисунок 6

 

ПОИСК ВПР ДАТЫ В ТЕКСТОВОМ ФОРМАТЕ

В файле [VLookup prof 2 Date As Text.xlsx] по датам из колонки A нужно найти дежурного, полный список дат и дежурных находится в таблице E:F. Как настроить в этом случае работу функции ВПР, не прибегая в исправлению данных в исходных таблицах.

Поможет функция ТЕКСТ, которая показывает число в текстовый результат с помощью аргумента Формат (а дата для Excel является в первую очередь числом; подробнее об этих числах и о расчётах по датам можно прочитать в соответствующем уроке).

Рисунок 7

Для закрепления материала в серый столбик C попробуйте самостоятельно подставить ФИО дежурного из таблицы H:I. Будьте внимательны – в колонке H дата представлена с двумя цифрами года.

Рисунок 8

 

В СТОЛБЦЕ ТАБЛИЦЫ ДЛЯ ПОИСКА ИСКОМОГО ЗНАЧЕНИЯ ЕСТЬ ЛИШНИЕ ЗНАКИ

В файле [VLookup prof 3 Extra Signs.xlsx] на листе Остатки1 содержится таблица по остаткам товаров на складах. Это кухонная мебель. Нужно внести в ячейки F4:F8 остатки по основным товарам, названия которых указаны в E4:E8. В примере легко заметить, что в таблице данных B:C в ячейке с названием товара дополнительно вписана единица измерения [, шт.].

Можно, конечно, выделить колонку B, нажать CTRL+F и с помощью вкладки Заменить стереть эту приписку, но наша цель – не трогать по возможности исходную таблицу. Поэтому к искомому значению с помощью знака & приклеим указанную приписку и функция ВПР сможет выполнить поиск данных.

Рисунок 9

 

Если бы приписки в конце названия были бы разными, или даже в описанном случае – можно использовать символ подстановки звёздочку *, которая заменяет любую комбинацию знаков. И тогда функция ВПР могла бы быть записана так: =ВПР(F4&"*";B:C;2;0)

Рисунок 10

Если бы приписка была расположена перед названием, то сцепка символов также была бы записана в логически соответствующем порядке: =ВПР("*"&F4;B:C;2;0)

Рисунок 11

В особом случае, если имеются приписки и в начале, и в конце названия, прицепить звёздочку с обеих сторон: =ВПР("*"&F4&"*";B:C;2;0)

Рисунок 12

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

 

УБРАТЬ ИЗ ИСКОМОГО ЗНАЧЕНИЯ ЛИШНИЕ ЗНАКИ

В файле [VLookup prof 3 Extra Signs.xlsx] на листе Остатки2 ситуация меняется и теперь нужно отбросить от искомого значения лишние знаки [; шт.]. Искомое значение помогут составить текстовые функции ЛЕВСИМВ и НАЙТИ, подробнее о которых можно прочитать в соответствующем уроке.

Рисунок 13

 

ЛЕВСИМВ(F4;24) функция ЛЕВСИМВ получает из ячейки F24 первые 24 знака, а это и есть фрагмент Кухонный стол "МОСКВА-2".
Можно, конечно, посчитать вручную требуемое количество знаков до точки с запятой, но мы это сделаем только для настройки первой формулы в качестве проверки. В примере количество знаков было посчитано с помощью функции НАЙТИ(";";F4) – она находит порядковый номер символа точки с запятой, после которого расположен не имеющий отношения к поиску лишний текст. Результат поиска функции НАЙТИ равен 25. Так как сам знак точки с запятой не нужно учитывать в поиске, то отнимаем один знак от результата функции НАЙТИ(";";F4)-1.

 

ОДНОВРЕМЕННЫЙ ПОИСК И СТРОКИ, И СТОЛБЦА С ПОМОЩЬЮ ВПР

В файле [VLookup prof 4 Match.xlsx]  на листе Фрукты есть таблица данных с заказами. Нужно настроить формулу для жёлтой ячейки H4 таким образом, чтобы при изменении города H2 и товара H3 в неё выводилось количество заказов из таблицы A2:F11. Задача сводится к двухмерному поиску, то есть в таблице нужно искать и строку, и столбец, ориентируясь на совпадение названий.

ВПР настроена разработчиками искать строку, в базовом уроке мы это подробно разбирали.

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

Рисунок 14

 

 

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

Для закрепления материала на листе Тарифы настройте функции ВПР и ПОИСКПОЗ для подстановки расценки в ячейку H4 из таблицы данных по соответствующему региону из H2 и виду транспорта из H3.

Рисунок 15

Похожие материалы

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

Текстовые функции для отбора и исправления знаков в ячейке
Текстовые функции
Как взять из ячейки первые или последние несколько знаков? А из середины текста? Как правильно использовать функции ЛЕВСИМВ, ПРАВСИМВ, ПСТР и другие?

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

Наиболее используемые сочетания клавиш Excel
Горячие клавиши
Горячие клавиши ускоряют доступ ко многим командам, необходимым в ежедневной работе. Если учить хотя бы одно сочетание в 1-2 недели, результат будет заметен и ощутим!

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



Оставляя заявку, вы соглашаетесь на обработку ваших персональных данных.