Формула Excel в зависимости от количества
Я здесь для идей и решений. Мне нужно создать формулу в Excel, я знаком с VBA и power query и знаю, как это сделать с их помощью, но здесь мне нужно создать формулу, потому что этот файл каждый день используется многими людьми, и с помощью этой формулы я буду будьте уверены, что каждый раз, когда кто-то сохраняет файл, моя формула будет иметь самое последнее значение. Потому что я работаю с другим программным обеспечением и мой код только открывает файл и берет значение из ячейки Z5. В чем моя проблема, объясню на примере. Я участвую в большом внутреннем процессе, и мне нужно создать отчет с итоговой суммой за месяц. Все клиенты простые - Количество * Цена = Сумма, кроме одного, для которого мне нужна эта формула. У этого клиента есть два продукта A и B и 4 уровня цен в зависимости от количества 0-1000, 1001-2000, 2001-3000, 3001+. Сначала уровень заполняет продукт А, а затем продукт Б.
Пример: если у продукта A имеется 1200 количеств, а у продукта B — 910. Распределение будет следующим:
ПРИМЕР
Мне нужен столбец «Итого» в одной ячейке. Я пытаюсь сделать это с помощью формулы, для которой требуется только определенное количество продукта А и продукта Б.
Я пытался сделать это, используя множество «если»… но конечным результатом стала одна большая-большая формула. Буду очень признателен, если у кого-то появится идея лучше моей
Если вы можете добавить еще два столбца, поместите рядом с ними цену для A и B каждого уровня; затем умножьте каждый столбец на его цену и просуммируйте в нижней строке.
Ответы 2
Вероятно, существует лучшее решение, но, по крайней мере, если вы хотите, чтобы общая ячейка не была огромной, если оператор if, вы можете попытаться сохранить промежуточные результаты операторов if в отдельных ячейках для каждого уровня. Например,
Level1_A in E1: =MIN(B1, 1000)
Level1_B in F1: =IF(B1 < 1000, MIN(B2, 1000 - B1), 0)
Затем позвоните им в последнем итоговом столбце. (E1*F1....) + ...
Если я правильно понял, вы пытаетесь получить значения A и B, рассчитанные по строкам, где каждая строка (level) со значениями A и B вместе не может превышать 1000.
Также значения столбцов для каждого продукта не могут превышать входные значения.
В Office 365 это можно рассчитать с помощью REDUCE:
=DROP( REDUCE({0,0},SEQUENCE(4),
LAMBDA(x,level,
LET(A, SUM(INDEX(x,,1)),
B, SUM(x),
AB,SUM(E2:F2),
n, 1000,
z, IF(A=E2,0,IF(A+n>E2,n-MOD(A+n,E2),n)),
C, B-z+n,
VSTACK(x,
HSTACK(z,
IF(B=AB, 0, IF(C>AB, n-MOD(C,AB), n-z))))))),
1)
Как вы можете видеть, это распределяет результат в зависимости от входных значений общей стоимости продукта A в E2 и продукта B в F2.
Формула начинается с начального значения — массива {0,0}; это начало x.
Затем он начинает перебирать уровни (в данном случае 4). «уровень» объявлен, но используется только для целей итерации.
Затем мы объявляем A сумму значений в первом столбце (значения продукта A) результата вычисления последней итерации x.
B — это сумма значений для обоих столбцов (значения продуктов A и B) результата вычисления последней итерации x.
AB — это общее значение суммы двух входных значений для A и B вместе.
Итак, для первого столбца мы вычисляем, равна ли сумма результата предыдущей итерации для столбца 1 (A) входному значению A в E2.
Если это правда, он возвращает 0 для текущего вычисленного результата строки для продукта A, в противном случае он проверяет, не превысит ли добавление 1000 к A входное значение для A E2. Если нет, возвращается 1000, иначе остаток. (этот расчет заявлен z).
Для второго столбца мы вычисляем сумму результата предыдущей итерации для обоих столбцов (B), равную сумме обоих входных значений (AB).
Если это так, он возвращает 0 для текущего вычисленного результата строки для продукта B, в противном случае он проверяет, прибавляется ли 1000 к B минус вычисленное значение текущей строки для A (z). Это заявлено C.
Если C превышает сумму входных значений AB, возвращается остаток 1000 минус C, в противном случае возвращается 1000.
Распространенным подходом является использование таблицы поиска цен.