Как сделать построчное вычитание в Гугл Таблицах?

Question

[jockasta]

Я столкнулась с двумя довольно интересными задачами, и все они, как мне кажется, решаются по похожему принципу.

Задача 1.

Я экспортирую диапазон в новую таблицу с помощью query. В одном из столбцов – суммы, в другом – статусы, в остальных – другая информация по проектам. На один проект может приходится либо статус "Акт подписан", либо два разных статуса: "Акт подписан" и "Предоплата". Возможно ли сделать так, чтобы в новой таблице динамически обновлялся диапазон с проектами, вычитая из суммы по статусу "Акт подписан" сумму "Предоплаты" (обе сделки относятся к одному проекту, на один проект может быть максимум один акт и одна предоплата).

Скрины ниже:
[1.1] — исходная таблица

[1.2] — что должно получится


Задача 2.

Есть экспортируемый диапазон с составом заказа в колонках A:B, и табличка «Комплектация» D:E, которую заполняют вручную: товары в «D» выбирают, допустим, через выпадающий список, в «E» ставят укомплектованное количество. Как сделать так, чтобы в диапазоне A:B уменьшалось количество товара, на указанное в комплектации? И соответственно, когда товар весь укомплектован, он исчезал из колонок A:B?

Скрины также ниже:
[2.1] — исходная позиция, выведен целиком состав заказа;

[2.2] — в комплектации указано 35 шт. Товара 1, в составе заказа Товар 1 уменьшился на данное количество;

[2.3] — Товар 1 укомплектован целиком, в составе заказа исчезла строка с ним (или она может просто обнулиться)


p.s. всю голову сломала как это можно решить, если кто-то сможет помочь, буду очень благодарна)

Comments

[Модератор]

См.п.3.7

[jockasta]

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

[Модератор]

jockasta, допустим.
А вы как-то пробовали решить? Рекомендую разместить хоть какие-то свои попытки, пусть неудачные, и что получилось.

[Alexander Ivanov]

Хм, может быть вам нужно заранее просчитывать эти данные? Например, ввести какую-то колонку с разницами?

Answer 1

[Alexander Ivanov]

Пример в Таблице https://docs.google.com/spreadsheets/d/1ejjSoD_NAy...

Решение второй задачи

=ARRAYFORMULA(FILTER(
  A2:B;
  MATCH(
    A2:A;
    QUERY(QUERY(
      {A2:B;G2:G\-1*H2:H};
      "select Col1, sum(Col2) where Col1<>'' group by Col1";
      0
    );"select Col1 where Col2<>0";0);
    0
  )
))

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