Как получить значение, соответствующее определенной дате, если дата до или после даты в таблице?

Question

У меня есть таблица Google Sheet с рядом добавлений инвентаря:

    Date    | Product | New Units | # Total Units 
 -----------|---------|-----------|--------------- 
  1/11/2017 | Coke    |        14 |            14 
  1/31/2017 | Pepsi   |         6 |             6 
  2/12/2017 | Coke    |         3 |            17 
  3/13/2017 | Coke    |        12 |            29 
  3/13/2017 | Pepsi   |        13 |            19

Например, 12 февраля 2017 года я получил 3 новых единицы кока-колы, всего 17 единиц. Я хотел бы иметь возможность сказать для любого данного продукта и любой конкретной даты, сколько единиц этого продукта у меня было на эту дату?

Например, учитывая следующий список дат на отдельном листе, основанный на данных выше, я надеюсь увидеть этот вывод:

    Date    | Coke | Pepsi 
 -----------|------|------- 
  1/10/2017 |    0 |     0 
  1/11/2017 |   14 |     0 
  2/10/2017 |   14 |     6 
  2/15/2017 |   17 |     6 
  3/15/2017 |   29 |    19 

Есть ли формула или формулы, которые я мог бы использовать для вычисления значений для B2:B6 и C2:C6?

Answer 1

Вставьте в G3 (пропустите 1-ую строку, чтобы избежать #REF!) затем перетащите вниз, вправо и вверх

=ARRAYFORMULA(IF($F3<MIN($A$2:$A), 0, IFERROR(IFERROR(
 QUERY($A$2:$D, 
 "select D where A >= date '"&TEXT($F2, "yyyy-mm-dd")&"' 
             and A <= date '"&TEXT($F3, "yyyy-mm-dd")&"' 
             and B =  '"&G$1&"' ", 0),
 QUERY($A$2:$D, 
 "select D where A >= date '"&TEXT($F1, "yyyy-mm-dd")&"' 
             and A <= date '"&TEXT($F3, "yyyy-mm-dd")&"' 
             and B =  '"&G$1&"' ", 0)), 0)))

https://st ackru.com/images/d5d6f7e51f9d1789f968f0cf93cacd827e0d7530.png

Answer 2

Вставьте в G3 (пропустите 1-ую строку, чтобы избежать #REF!) затем перетащите вниз, вправо и вверх

=ARRAYFORMULA(IF($F2<MIN($A$2:$A), 0, IFERROR(IFERROR(
 QUERY(TO_TEXT({VALUE($A$2:$A), $B$2:$D}), 
 "select Col4 where Col1 >= '"&VALUE($F1)&"' 
                and Col1 <= '"&VALUE($F2)&"' 
                and Col2 =  '"&G$1&"' ", 0),
 QUERY(TO_TEXT({VALUE($A$2:$A), $B$2:$D}), 
 "select Col4 where Col1 >= '"&VALUE(#REF!)&"' 
                and Col1 <= '"&VALUE($F2)&"' 
                and Col2 =  '"&G$1&"' ", 0)), 0)))

https://st ackru.com/images/78953d82f4ee17d3d6c16062c7c514626f2dd5e7.png