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





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

Конспекты

Курсы

Практикум


Скачать файл
Ссылка на курс
Теги: Бесплатный курс 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.

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

Оформление больших таблиц. Умная строка итогов и автоматическая нумерация. Удаление пустых строк. Быстрый ввод формул
Форматировать как таблицу - что за команда
На вкладке Главная есть кнопка Форматировать как таблицу. Как это работает? Какие задачи помогает решить данная команда и как отказаться от полосатого стиля, если он неудобен.

Заказчики

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

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

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

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



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