Многослойный Excel, если ВПР и формула
Я подготовил упрощенный документ, показывающий суть того, чего я пытаюсь достичь.
Изображение документа:
Есть 5 магазинов, в которых продаются яблоки, груши, бананы и апельсины.
Внутренние запасы перемещаются и покупаются очень регулярно. Отсюда и раздел «Расположение и количество запасов». Ниже представлено «Сентябрьское движение акций», которое показывает, куда и откуда перемещались акции. Также есть вариант, если будет куплено или продано больше акций - у банка.
Мне нужно создать формулу, которая отслеживает внутренние запасы в зависимости от раздела «Движение запасов за сентябрь» — я заполнил некоторые детали в этом разделе. Если формула в разделе «Расположение и количество запасов» заполнена, то данные ниже будут применимы/работают.
Если это передача запасов, то необходимо уменьшить запасы в одном магазине и увеличить запасы в другом. Если это покупка, то ему просто нужно добавить его непосредственно в местонахождение магазина, принимающего покупку, и уменьшить баланс в банке, а если это продажа, то нужно уменьшить запасы в магазине, но увеличить баланс банка.
Обратите внимание - цены на продукцию разные.
2 ответа
Мне помог кто-то на форуме Excel, и они ответили:
В C6 - перетащите вдоль и вниз =SUM(SUMIFS($D$18:$D$23,$C$18:$C$23,$A6,$F$18:$F$23,C$5,$B$18:$B$23,{"Перевести","Купить"}))-SUM(SUMIFS($D$18:$D$23,$C$18:$C$23,$A6,$E$18:$E$23,C$5,$B$18:$B$23,{"Перевод","Купить"}))
в C10 (исправлено)
=СУММИФ($D$18:$D$23,$B$18:$B$23,"Продать",$E$18:$E$23,C$5)-СУММИФ($D$18:$D$23,$B$18:$ B$23,"Купить",$F$18:$F$23,C$5)
Копировать поперек
в H6
=СУММИН($D$18:$D$23,$B$18:$B$23,"Продать",$C$18:$C$23,$A6)-СУММИФ($D$18:$D$23,$B$18:$ B$23,"Купить",$C$18:$C$23,$A6)
скопировать вниз
Это возможно, но без вспомогательного столбца это кошмар. Вам нужен еще один столбец в разделе движения запасов, содержащий значение. У него должна быть формула чего-то вроде (это будет в G15):
= [относительная ссылка на ячейку суммы (например, D15)] * ИНДЕКС( [диапазон значений (например, $B$25:$B$28)] , ПОИСКПОЗ( [относительная ссылка на ячейку элемента (например, C15)], [диапазон значений элемента (например, .$A$25:$A$28)], 0) )
Затем у вас есть формула в разделе «Яблоки Shop1» (в вашем примере ячейка C3), это что-то вроде
= SUMIFS( [диапазон ячеек значений (например, $G$15:$G$20)], [диапазон ячеек места проведения (до) (например, $F$15:$F$20)], [ячейка заголовка магазина (например, C$2)], [ Диапазон элементов (например, $C$15:$C$20)], [ТОКЕН строки (например, $A3)] ) - SUMIFS( [диапазон ячеек значений (например, $G$15:$G$20)], [диапазон ячеек места проведения (от) (например, $E$15:$E$20)], [Ячейка заголовка магазина (например, C$2)], [Диапазон элементов (например, $C$15:$C$20)], [СТРОКА ТОКЕН (например, $A3)] )
Если вам нужно сделать это без создания вспомогательного столбца, я считаю, что вам придется разбить формулы массива, а я не могу просто напечатать простой ответ.