Формула ВПР для подстановки габаритов из справочника в таблицу заказов
Хочу себе такие же кнопки
Формула ВПР для подстановки габаритов из справочника в таблицу заказов
Что вы получите от этого урока
- Поймёте, почему ВПР (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 строк заказов. Включите режим «Вручную» и измерьте время, которое требуется на заполнение габаритов. Затем переключите в «Автоматически» и сравните. Что изменилось и почему?
Поздравляем! Вы теперь умеете быстро и безошибочно подставлять габариты из справочника в таблицу заказов, используя формулу ВПР. Это навык, который сократит время обработки заказов, уменьшит количество ошибок и сделает ваш логистический процесс более прозрачным. 🚚✨
Почему Excel без VBA быстрее внедрить в малой логистике
Структура исходных данных: что должно быть в таблице «Остатки на складах»
Структура исходных данных: таблица «Поступления товара»
Структура исходных данных: таблица «Отгрузки и продажи»
Создание умной таблицы (Ctrl+T) для автоматического расширения диапазонов
Правила именования складов и ячеек хранения
Формула СУММЕСЛИ для подсчёта остатков по конкретному товару
Формула СУММЕСЛИМН для расчёта остатков по товару + складу
Расчёт свободного объёма склада в паллето-местах
Создание справочника «Товары» с габаритами и весом единицы
Формула ВПР для подстановки габаритов из справочника в таблицу заказов
Автоматический расчёт веса партии по ВПР + умножение
Автоматический расчёт объёма партии (длинаширинавысотаколичество)
Условное форматирование для подсветки отрицательных остатков (красным)
Условное форматирование для подсветки нулевых остатков (серым)
Условное форматирование для подсветки страхового запаса (жёлтым)
Создание выпадающего списка складов через «Проверку данных»
Создание выпадающего списка товаров через «Проверку данных»
Динамическая таблица остатков с выбором склада через выпадающий список
Формула ЕСЛИ для сигнала «Срочный заказ» при остатке ниже нормы
Создание простого дашборда остатков на одном листе
Таблица «Заявки на перевозку»: номер, дата, маршрут, вес, объём
Справочник автомобилей: тип, грузоподъёмность, объём кузова, расход топлива
Формула ВПР для подбора авто по грузоподъёмности (ближайшая сверху)
Дополнительная проверка: объём груза < объёма кузова
Формула И / ИЛИ для одновременной проверки веса и объёма
Условное форматирование для авто, которые НЕ подходят (зачёркивание)
Автоматическая маркировка «Газель» / «Фура» по весу
Расчёт минимального количества автомобилей для сборного груза
Расчёт коэффициента загрузки фуры (факт/максимум) в процентах
Сводная таблица для анализа частоты заказов по каждому маршруту
Гистограмма в сводной таблице для визуализации загрузки авто
Расчёт удельной стоимости перевозки на 1 кг или 1 куб. м
Расчёт остатков на завтра: остаток сегодня минус плановая отгрузка
Формула СЕГОДНЯ для контроля сроков годности (если скоропорт)
Условное форматирование для товаров с истекающим сроком годности
Создание журнала перемещений между складами
Расчёт транзитных остатков (товар в пути) формулой СУММЕСЛИМН
Общий доступный остаток = складской + транзитный
Расчёт точки заказа (мин. остаток под заказ новой партии)
Автоматический текст: «Заказать N единиц» через ЕСЛИ и округление
Проверка дубликатов в накладных через СЧЁТЕСЛИ
Создание понятной шапки с фильтрами (Автофильтр)
Защита листов от случайного изменения формул (без пароля на старте)
Настройка вывода процентов загрузки склада в условных значках (зелёный/жёлтый/красный)
Расчёт оборачиваемости товара (отношение продаж к среднему остатку)
Создание шаблона «Заявка водителю» с автоподстановкой данных из подобранного авто
Сохранение файла как шаблона (.xltx) для ежедневного копирования
Чек-лист из 5 шагов на каждый день: проверить отрицательные остатки, загрузку авто, подсветку страхового
Бонусный пункт: как не сломать формулы при вставке новых строк
АПТЕЧКА ДЛЯ СОБАКИ С ПОМОЩЬЮ МЕДИЦИНЫ
Часы на весь экран в высоком разрешении
Чат рулетка 2026: чаты с элементом неопределенности
Чат рулетка бесплатно
Чатрулетка: чат с новой личностью
Чай и кофе: два мира вкуса
Диагностика шин: Проверка давления и износа
Excel для логистики: бесплатный курс учёта остатков и подбора авто
Фототехника в цифровую эпоху
Игрушки для развития навыков самостоятельности
Инновационные подходы в управлении грузовыми перевозками на линиях Валберис
Немецкие авто: легковые и внедорожники
Общение с Аней в чате
Онлайн генератор паролей с символами
Оптимизация динамического контента GEO
Политика конфиденциальности и пользовательское соглашение
Сервер для affiliate-маркетинга: Безопасность, Скорость, Изоляция
Смешные телевизоры
Современные тенденции в российском автопроме
Средства IP видеонаблюдения
