📌 Ошибки в формулах. Проблемы с ВПР и как их решить

Ребята, всем привет! 👋 Сегодня расскажу о небольшой хитрости 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))

Альтернативное решение: комбинация функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH)

🔔 Почему так происходит:

ПОИСКПОЗ (MATCH) возвращает относительные позиции, а ИНДЕКС (INDEX) возвращает значение в этом пересечении ячеек.

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

Продолжение следует...

Подписывайтесь на канал, чтобы не пропустить новые уроки и полезные фишки Excel. Следите за нашими новостями и вы узнаете больше о VBA и Excel в частности.

В следующих уроках более подробно рассмотрим:

☑ форматирование сводной таблицы;

☑ настройка макета сводной таблицы по умолчанию

☑ условное форматирование в сводных таблицах

☑ обновление сводных таблиц и сводных диаграмм

и др.

За лайк 👍  и репост 🔁 данного поста благодарочка 💖 и респект 🤝 каждому!

📝 Нам очень важна Ваша обратная связь. Отзыв по уроку и любые вопросы пишите в комментариях.

Подписывайтесь на канал, чтобы не пропустить новые уроки и полезные фишки Excel.

#excel #индекс excel #ВПР excel #эксель #фишки excel #примеры excel #ПОИСКПОЗ excel #примеры excel #ошибки в формулах excel #формулы excel