Как сложить и вычесть на основе нескольких условий?

Question

Этот вопрос относится к системе отслеживания запасов, которую я создаю в Google Sheets.

Просто быть чистым:

• Любые входящие товары имеют положительное количество
• Любые исходящие товары имеют отрицательное количество

Требование заключается в том, что при подсчете конечного запаса ставится количество в наличии, что является положительным числом. Это положительное число является то, что в настоящее время доступно на складе.

И наоборот, если конечный запас ставится как отрицательное число, это означает, что это использованная сумма (отрицательное число). Здесь оно будет вычтено из общего количества предыдущих запасов. Поэтому задача состоит в том, чтобы рассчитать это:

Запас для позиции X = [Разница между его общим кол-во и кол-во (в наличии) кол-во] - [предыдущий запас]

Я использовал SUMIFS, чтобы получить запас для каждого предмета. Это не учитывает дату, так как нам нужно рассчитать текущий запас, независимо от дней.

Проблема: Это работает только до тех пор, пока я не добавлю новый входящий запас, после чего весь расчет идет на бросок. Например, если я добавлю 10 000 на 3-й день, инвентарь достигнет -4300, тогда как должно было быть 15 700. См. Изображения для лучшего понимания.

Раньше: в третий день новые акции не добавлялись.

После:10 000 кол-во добавлено в день 3 -> ломает суффи.

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

Был бы признателен за любую помощь .. Потратил слишком много времени уже на это. Благодарю.

PS - Я надеюсь, что выкладываю на правильный стек. Должен ли я опубликовать это в веб-приложениях тоже?

Answer 1

Джит, добро пожаловать. Вот несколько принципов управления запасами в Excel (и баланс банковского счета!) и отладка этого вопроса.

Отделите ваши транзакции от вашего текущего баланса.

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

Никогда не допускайте отрицательного количества инвентаря.

Столбец вашего инвентаря никогда не должен опускаться ниже нуля, если ваш инвентарь - это физические объекты. Вместо этого не допускайте, чтобы в столбце транзакции было разрешено вычитать больше предметов, чем доступно в инвентаре, и требуйте добавления большего количества инвентаря до его изъятия.

Есть несколько способов сделать это, но один из них - создать дополнительный столбец для "отложенных заказов", "с чрезмерным спросом" или "невыполненных" запросов инвентаризации, которые адресуются перед дополнительными транзакциями.

Вы должны делать это, даже если ваш "инвентарь" состоит из предметов, которые легко доступны за пределами вашей компании, независимо от того, насколько просто их получить. (Даже батарейки АА можно купить в продуктовом магазине по соседству, который открыт 24 часа в сутки).

Поскольку вы моделируете физический процесс, вы не должны позволять своей электронной таблице моделировать то, что невозможно. Смоделируйте, что должно произойти (в приведенном выше примере приобретите батареи по соседству, а затем отправьте их клиенту).

Для отладки создайте свои "большие формулы" из формул из кусочков, которые работают.

Существует соблазн создавать большие формулы с первого взгляда. Это делает их очень сложными для отладки. Гораздо лучше создавать небольшие формулы (по одному предложению за раз), которые работают - тестируйте на них разные случаи, например, положительные транзакции, отрицательные транзакции, транзакции с нулевым балансом и т.д. их в большие мега-формулы.

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