Какую формулу использовать в Excel?

Содержание

Вопрос или проблема
Ответ или решение
Проблема:
Решение:
Формула:
Применение:

Вопрос или проблема

Мне нужно создать формулу в Excel, которая может выполнять следующее:

КОД	ОПИСАНИЕ	КОЛ-ВО ЗАКАЗА	Модельный запас	Текущий запас	Проданное кол-во
КОД1	КОД 1 ТОВАР	24, 48, 72, 96, 120, 144, 168	120	78	15
КОД2	КОД 2 ТОВАР	6, 10, 15, 20, 25, 30, 35, 40	20	8	8
КОД3	КОД 3 ТОВАР	10, 20, 30, 40, 50, 60, 70	10	1	1

У меня есть следующие столбцы:

КОД
ОПИСАНИЕ
КОЛ-ВО ЗАКАЗА
Модельный запас
Текущий запас
Кол-во на складе
Кол-во заказа

На том же листе у разных товаров разные количества заказа, поэтому я не могу использовать универсальную формулу, которая просто округляет до определенной группы чисел.

Мне нужно, чтобы столбец QTY рассчитывал и вводил количество заказа, используя количество из столбца проданного количества, но введенное в определенном диапазоне чисел в столбце количества заказа.

Например, для КОД1 проданное количество равно 15, количество заказа — 24, 48, 72 и т.д. Необходимо ввести 24 в столбец количества заказа.

Товар КОД2, проданное количество равно 8, количество заказа — 6, 10, 15 и т.д. Необходимо ввести 10 в столбец количества заказа и округлить до большего числа в столбце количества заказа, чтобы не использовать 6, а именно 10.

Сначала нужно разделить столбец КОЛ-ВО ЗАКАЗА, используя TEXTSPLIT с ", " в качестве разделителя. Затем используйте XLOOKUP с MatchMode=1 для поиска ‘Следующего большего элемента’.

Формула будет следующей:

=ЕСЛИ(E2>D2,0,LET(orderQuantities, --TEXTSPLIT(C2,", "), XLOOKUP(F2,orderQuantities,orderQuantities,,1)))

Ответ или решение

Вопрос о выборе формулы в Excel затрагивает довольно специфическую задачу, и для ее решения важны все детали из описания. Вам необходимо автоматизировать вычисление колонки "Order QTY" на основе данных из колонки "Sold QTY", учитывая заданный диапазон возможных значений для каждой номенклатуры в "ORDER QTY". Давайте разберем решение этой задачи по шагам.

Проблема:

У вас есть следующие колонки:

CODE – код товара
DESCRIPTION – описание
ORDER QTY – допустимые количества для заказа
Model Stock – модельный запас
Stock on Hand – запас на руках
Sold QTY – проданное количество

Вы хотите вычислять "Order QTY" таким образом, чтобы оно соответствовало наименьшему возможному числу из допустимого набора, превышающему "Sold QTY".

Решение:

Разделение чисел в "ORDER QTY" на отдельные элементы:
Используйте функцию TEXTSPLIT для разделения строки с возможными количествами заказа. Делитель – запятая и пробел ", ". Таким образом, числовые значения будут обработаны как массив.
Поиск наименьшего возможного количества заказа:
Функция XLOOKUP с параметром MatchMode=1 позволит найти следующее большее число в списке количеств для заказа, соответствующее проданному количеству.
Проверка запасов:
Если запас на руках превышает модельный запас, заказывать ничего не нужно, поэтому результат будет равен нулю для "Order QTY".

Формула:

Формула для выполнения задания выглядит следующим образом:

=IF(E2>D2, 0, LET(orderQuantities, --TEXTSPLIT(C2, ", "), XLOOKUP(F2, orderQuantities, orderQuantities, , 1)))

Рассмотрим детали формулы:

E2>D2: проверяет, если остаток превышает модельный запас, то нужды в дозакупке нет.
LET позволяет назначить переменной массив возможных количеств заказа.
XLOOKUP(F2, orderQuantities, orderQuantities, , 1) ищет и возвращает наименьшее значение, большее или равное "Sold QTY".

Применение:

Вставьте данную формулу в ячейки колонки "Order QTY". Она будет автоматически адаптироваться для каждой из позиций вашей таблицы, исходя из предоставленных диапазонов, следовательно, обеспечит выполнение требования по автоматическому определению нужного количества для заказа.

Использование в Excel таких функций, как TEXTSPLIT и XLOOKUP, упрощает сложные вычисления, делая процесс анализа данных более эффективным. Эти функции особенно полезны для работы с большими наборами данных и помогают поддерживать точность и скорость бизнес-аналитики.