Формула ВПР для подстановки габаритов из справочника в таблицу заказов
Хочу себе такие же кнопки
Формула ВПР для подстановки габаритов из справочника в таблицу заказов
Что вы получите от этого урока
- Поймёте, почему ВПР (VLOOKUP) – ваш лучший помощник при работе с большими списками товаров.
- Научитесь быстро «перетаскивать» длину, ширину, высоту и вес из справочника в таблицу заказов без ошибок.
- Сможете адаптировать формулу под любые изменения в структуре данных (добавление колонок, переименование товаров).
Аналогия: представьте, что у вас есть огромный склад с ящиками, каждый из которых подписан номером. Вы получаете заказ, где указаны только номера ящиков, а вам нужно знать, что внутри. ВПР – это ваш «сканер штрих‑кода», который мгновенно подскажет содержимое.
1. Основные понятия, которые нужно знать
| Термин | Что означает | Пример в логистике |
|---|---|---|
| Справочник | Таблица‑источник, где хранится информация о каждом товаре (артикул, габариты, вес и т.п.) | Товары_Справочник.xlsx |
| Таблица заказов | Таблица‑приёмник, где фиксируются конкретные строки и их параметры | Заказы_2026.xlsx |
| ВПР | Функция Excel/Google Sheets, ищет значение в первой колонке диапазона и возвращает значение из указанного столбца | =VLOOKUP(A2;Товары_Справочник!$A$2:$E$500;3;FALSE) |
| Точный поиск | Параметр FALSE в ВПР – гарантирует, что будет найдено именно совпадение |
— |
| Диапазон | Область ячеек, где ищем и откуда берём данные | $A$2:$E$500 |
2. Подготовка данных
2.1 Структура справочника
| A – Артикул | B – Наименование | C – Длина (см) | D – Ширина (см) | E – Высота (см) | F – Вес (кг) |
|---|---|---|---|---|---|
| 1010 | Коробка‑офисная | 30 | 20 | 10 | 2.5 |
| 1020 | Папка‑деловая | 25 | 15 | 5 | 0.8 |
| … | … | … | … | … | … |
Важно: первая колонка (
A) должна содержать уникальные идентификаторы (артикули). ВПР ищет только в ней.
2.2 Структура таблицы заказов
| A – № заказа | B – Артикул | C – Кол‑во | D – Длина (см) | E – Ширина (см) | F – Высота (см) | G – Вес (кг) |
|---|---|---|---|---|---|---|
| 001 | 1010 | 5 | ||||
| 002 | 1020 | 12 | ||||
| … | … | … | … | … | … | … |
Колонки D–G – «пустые» сейчас, их заполняем автоматически.
3. Как работает формула ВПР
Синтаксис:
=VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])- lookup_value – значение, которое ищем (артикул из заказа).
- table_array – диапазон в справочнике, где ищем и откуда берём данные.
- col_index_num – номер столбца внутри
table_array, из которого нужно вернуть значение (1 = первая колонка, 2 = вторая и т.д.). - range_lookup –
FALSE→ точный поиск,TRUE→ приближённый (не подходит для артикулов).
Пример:
=VLOOKUP(B2;Товары_Справочник!$A$2:$F$500;3;FALSE)
– ищет артикул изB2в колонкеAсправочника, а возвращает значение из 3‑й колонки (длина).
4. Шаг‑за‑шаг: Заполняем габариты в таблице заказов
4.1 Создаём именованный диапазон (по желанию)
- Откройте лист Справочник.
- Выделите диапазон
$A$2:$F$500. - В поле имени (слева от строки формул) введите
Товары. - Нажмите Enter.
Зачем? Именованный диапазон делает формулу короче и понятнее:
=VLOOKUP(B2;Товары;3;FALSE).
4.2 Формулы для каждой габаритной колонки
| Колонка | Формула (пример для строки 2) | Что делает |
|---|---|---|
| D – Длина | =VLOOKUP($B2;Товары;3;FALSE) |
Берёт длину из 3‑й колонки справочника |
| E – Ширина | =VLOOKUP($B2;Товары;4;FALSE) |
Берёт ширину из 4‑й колонки |
| F – Высота | =VLOOKUP($B2;Товары;5;FALSE) |
Берёт высоту из 5‑й колонки |
| G – Вес | =VLOOKUP($B2;Товары;6;FALSE) |
Берёт вес из 6‑й колонки |
Подсказка:
$B2– фиксируем колонкуB, а строку оставляем относительной, чтобы при копировании формулы вниз строка менялась автоматически.
4.3 Копирование формул вниз
- Выделите ячейки
D2:G2. - Переместите курсор к правому нижнему углу (появится «крестик»).
- Дважьте и тянуть вниз до последней строки заказов.
Все строки получат свои габариты без ручного ввода.
5. Что делать, если артикул не найден?
- Ошибка
#N/A– значит, в справочнике нет строки с таким артикулом. - Чтобы скрыть ошибку и показать, например, «—», используйте
IFERROR:
=IFERROR(VLOOKUP($B2;Товары;3;FALSE); "—")Повторите для остальных колонок.
6. Обновление справочника без изменения формул
- Добавляете новые товары → просто вставляете их в конец списка.
- Увеличиваете диапазон → если используете именованный диапазон, просто расширяете его (Меню → Формулы → Диспетчер имён).
Формулы автоматически «подхватят» новые строки, потому что они ссылаются на диапазон, а не на фиксированный набор ячеек.
7. Как ускорить расчёт в больших файлах
- Перевод в массивные функции (Excel 365):
=LET(
артикулы; $B2:$B1000,
данные; ВLOOKUP(артикулы;Товары;{3,4,5,6};FALSE),
HSTACK(артикулы; данные)
)-
Отключить автоматический пересчёт во время массовой вставки (Файл → Параметры → Формулы → Режим расчёта → «Вручную»).
-
Использовать Power Query для соединения таблиц (запрос «Merge»).
8. Частые ошибки и как их избежать
| Ошибка | Причина | Как исправить |
|---|---|---|
#REF! |
Указан неверный номер столбца (больше, чем в диапазоне) | Проверьте col_index_num. |
#N/A |
Точное совпадение не найдено | Убедитесь, что артикул записан без лишних пробелов, используйте TRIM. |
| Ошибочный диапазон | При копировании формулы диапазон сместился | Используйте абсолютные ссылки $A$2:$F$500 или именованный диапазон. |
| Дублирующиеся артикулы в справочнике | ВПР возвращает первое найденное | Удалите дубликаты, оставив уникальные записи. |
9. Практика для закрепления
-
Создайте небольшую таблицу (10 товаров) со всеми габаритами и весом. Затем сделайте таблицу заказов из 5 строк, где указаны только артикулы и количество. Заполните габариты с помощью ВПР.
-
Симулируйте ошибку: в одну из строк заказов введите артикул, которого нет в справочнике. Добавьте
IFERROR, чтобы вместо#N/Aотображалось «Товар не найден». -
Расширьте справочник: добавьте 3 новых товара в конец списка. Убедитесь, что формулы в заказах автоматически подхватили новые данные без изменения диапазона.
-
Тест на дублирование: скопируйте одну строку из справочника и вставьте её ниже, изменив только количество в заказе. Проверьте, как ВПР реагирует, и объясните, почему.
-
Оптимизация: откройте файл с 5000 строк заказов. Включите режим «Вручную» и измерьте время, которое требуется на заполнение габаритов. Затем переключите в «Автоматически» и сравните. Что изменилось и почему?
Поздравляем! Вы теперь умеете быстро и безошибочно подставлять габариты из справочника в таблицу заказов, используя формулу ВПР. Это навык, который сократит время обработки заказов, уменьшит количество ошибок и сделает ваш логистический процесс более прозрачным. 🚚✨
Адаптивный дизайн WordPress блога
CamZamZam - снимки с веб камеры и эффектом глитча
Cartoon Network games для детей
Чат-испытание
Инструкция по установке логистического программного обеспечения Logitrans.
Как предотвратить поломки оборудования в логистике.
Как улучшить логистические операции с помощью технологии RFID.
Как выбрать правильное оборудование для логистики на Урале.
Как заработать $1000 в неделю без блога и рекламы
Китайские фитинги и трубы для промышленности
Куклы LOL витрина
Лайфхаки для логистических компаний на Урале.
ЛОР болезни и аллергия: взаимосвязь
Ошибки в логистике: что делать, если груз потерян.
Основные стандарты логистики на Урале.
QR-код декодер онлайн
Регистрация ИП в Москве без посещения офиса
Русская онлайн рулетка
Специалист по доменным трендам
Сравнение логистических решений: CargoLogix vs. LogiMaster.
Стильные сумки для женщин
Субтитры — нет, уши — включены: 5 минут на английский
Важные факторы при выборе хостинга
Видеорегистраторы с функцией записи
