Ребята, всем привет! 👋 Сегодня расскажу о небольшой хитрости Excel которая поможет предотвратить ошибки в расчетах при использовании функции ВПР(VLOOKUP).
✨ А прежде, чем мы начнем 📣 напомню, теперь у нас на канале есть удобный рубрикатор 👉 Быстрый поиск решения. Путеводитель по Excel, а все видео 📽 предыдущих уроков доступны и на YouTube.
Если вы хотите найти значение в таблице в Excel, простая функция ВПР(VLOOKUP) обычно является достаточно хорошим и простым способом сделать это. Но...
📚 Итак, задача...
- Имеем таблицу с данными:
- Найдем количество товара на складе:
=ВПР(B2;Товары[[Наименование]:[количество на складе]];2;ЛОЖЬ)
🔔 Почему так происходит:
Формула ищет значение в B2 (“Монитор Huawei MateView HSN-CBA 28.2 Mystical Silver 53060256”) в массиве Товары[[Наименование]:[количество на складе]] и возвращает значение во 2-м столбце (номер индекса столбца).
ЛОЖЬ в конце формулы означает, что мы хотим получить точное совпадение.
👉 Подробнее о функции ВПР см в уроке.
⁉️ ⚠️ Но какая может быть проблема
Если мы добавим столбец (например, Стоимость за ед., руб), то функция ВПР(VLOOKUP) больше не будет работать:
🔔 Почему так происходит:
Если мы добавляем хотя бы еще один столбец, например между столбцом Наименование и столбцом количество на складе, то ссылка на массив обновится, но номер индекса столбца по-прежнему будет равен 2, а формула =ВПР(B2;Товары[[Наименование]:[количество на складе]];2;ЛОЖЬ) вернет значение равное нулю!
✅ Как избежать проблем...
⏩ Решение - динамическая ссылка на столбец
Нужно сделать ссылку на столбец динамической, чтобы она менялась всякий раз, когда мы вставляем новый столбец в таблицу.
Заменим номер индекса столбца (2️⃣) формулой:
=ПОИСКПОЗ(A3;Товары[[ # Заголовки];[Наименование]:[количество на складе]];0)
Данная формула ищет значение в A3 (“количество на складе”) в массиве Товары[[ # Заголовки];[Наименование]:[количество на складе]] и возвращает его позицию.
Ноль 0️⃣ в конце формулы означает, что мы хотим получить точное совпадение.
▶️ Теперь наша формула примет вид:
=ВПР(B2;Товары[[Наименование]:[количество на складе]];ПОИСКПОЗ(A3;Товары[[# Заголовки];[Наименование]:[количество на складе]];0);ЛОЖЬ)
🔔 Далее, сколько бы столбцов мы не добавили функция ВПР(VLOOKUP) будет давать верный результат:
⏩ Альтернативное решение:
👉 Альтернативным подходом к этой проблеме будет комбинация функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH).
=ИНДЕКС(Товары[[Наименование]:[количество на складе]];ПОИСКПОЗ(B2;Товары[Наименование];0);ПОИСКПОЗ(A3;Товары[[# Заголовки];[Наименование]:[количество на складе]];0))
🔔 Почему так происходит:
ПОИСКПОЗ (MATCH) возвращает относительные позиции, а ИНДЕКС (INDEX) возвращает значение в этом пересечении ячеек.
А на этом сегодня все. Теперь Вы знаете как предотвратить ошибки в расчетах при использовании функции ВПР(VLOOKUP) 👏.
Продолжение следует...
Подписывайтесь на канал, чтобы не пропустить новые уроки и полезные фишки Excel. Следите за нашими новостями и вы узнаете больше о VBA и Excel в частности.
В следующих уроках более подробно рассмотрим:
☑ форматирование сводной таблицы;
☑ настройка макета сводной таблицы по умолчанию
☑ условное форматирование в сводных таблицах
☑ обновление сводных таблиц и сводных диаграмм
и др.
За лайк 👍 и репост 🔁 данного поста благодарочка 💖 и респект 🤝 каждому!
📝 Нам очень важна Ваша обратная связь. Отзыв по уроку и любые вопросы пишите в комментариях.
#excel #индекс excel #ВПР excel #эксель #фишки excel #примеры excel #ПОИСКПОЗ excel #примеры excel #ошибки в формулах excel #формулы excel
