{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["Расчеты"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":2,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Расчеты","index":0,"n_rows":28,"n_cols":13,"rows_kept":28,"cut":false,"hidden":false,"merged":[[19,4,22,4],[23,4,26,4],[8,4,11,4],[19,9,22,9],[23,9,26,9],[7,8,7,13],[4,8,6,13],[8,8,10,13],[11,8,11,13],[13,9,16,12],[17,10,18,10],[17,12,18,12],[1,4,1,7],[13,4,16,7],[17,4,18,4],[2,4,3,4],[17,9,18,9],[2,7,3,7],[4,4,7,4],[2,5,3,5],[17,5,18,5],[17,7,18,7]]}
[]
[null,"Дата","Наименование",null,"То, что считает сейчас"]
[null,"2016-11-01","Яблоки",null,"Группа","Наименование","По","Всего в справке"]
[null,"2016-11-21","Груши",null,null,null,"2017-08-10"]
[null,"2017-02-20","Апельсины, Яблоки",null,"Фрукты","Яблоки",{"$f":"COUNTIFS($B$3:$B$28,\"<=\"&$G$4,$C$3:$C$28,\"*\"&$F5&\"*\")&\"/\"&SUMPRODUCT(($B$3:$B$28<=G$4)*ISNUMBER(SEARCH(F5,$C$3:$C$28))*IsYellow($C$3:$C$28))","v":"6/2"},{"$f":"COUNTIFS($C$3:$C$28,\"*\"&$F5&\"*\")&\"/\"&SUMPRODUCT(ISNUMBER(SEARCH(F5,$C$3:$C$28))*IsYellow($C$3:$C$28))","v":"9/3"},"Расчет для каждого наименования отдельно"]
[null,"2017-07-31","Картофель",null,null,"Груши",{"$f":"COUNTIFS($B$3:$B$28,\"<=\"&$G$4,$C$3:$C$28,\"*\"&$F6&\"*\")&\"/\"&SUMPRODUCT(($B$3:$B$28<=G$4)*ISNUMBER(SEARCH(F6,$C$3:$C$28))*IsYellow($C$3:$C$28))","v":"2/1"},{"$f":"COUNTIFS($C$3:$C$28,\"*\"&$F6&\"*\")&\"/\"&SUMPRODUCT(ISNUMBER(SEARCH(F6,$C$3:$C$28))*IsYellow($C$3:$C$28))","v":"5/1"}]
[null,"2017-07-31","Томаты, Огурцы",null,null,"Апельсины","2/0","5/2"]
[null,"2017-07-31","Огурцы",null,null,"Фрукты (всего)",{"$f":"SUM(($B$3:$B$28<($G$4+1))*(MMULT(--ISNUMBER(SEARCH(TRANSPOSE($F$5:$F$7),$C$3:$C$28)),ROW($F$5:$F$7)^0)>0))&\"/\"&SUM(IsYellow($C$3:$C$28)*($B3:$B28<($G$4+1))*(MMULT(--ISNUMBER(SEARCH(TRANSPOSE($F$5:$F$7),$C$3:$C$28)),ROW($F$5:$F$7)^0)>0))","v":"8/2"},{"$f":"SUM(--(MMULT(--ISNUMBER(SEARCH(TRANSPOSE($F$5:$F$7),$C$3:$C$28)),ROW($F$5:$F$7)^0)>0))&\"/\"&SUM(IsYellow($C$3:$C$28)*(MMULT(--ISNUMBER(SEARCH(TRANSPOSE($F$5:$F$7),$C$3:$C$28)),ROW($F$5:$F$7)^0)>0))","v":"16/4"},"Расчет для упоминания хотя бы одного наименования из всей группы"]
[null,"2017-07-31","Яблоки, Груши",null,"Овощи","Картофель",{"$f":"COUNTIFS($B$3:$B$28,\"<=\"&$G$4,$C$3:$C$28,\"*\"&$F9&\"*\")&\"/\"&SUMPRODUCT(($B$3:$B$28<=G$4)*ISNUMBER(SEARCH(F9,$C$3:$C$28))*IsYellow($C$3:$C$28))","v":"3/1"},{"$f":"COUNTIFS($C$3:$C$28,\"*\"&$F9&\"*\")&\"/\"&SUMPRODUCT(ISNUMBER(SEARCH(F9,$C$3:$C$28))*IsYellow($C$3:$C$28))","v":"5/1"},"Расчет для каждого наименования отдельно"]
[null,"2017-08-04","Яблоки",null,null,"Томаты",{"$f":"COUNTIFS($B$3:$B$28,\"<=\"&$G$4,$C$3:$C$28,\"*\"&$F10&\"*\")&\"/\"&SUMPRODUCT(($B$3:$B$28<=G$4)*ISNUMBER(SEARCH(F10,$C$3:$C$28))*IsYellow($C$3:$C$28))","v":"3/1"},{"$f":"COUNTIFS($C$3:$C$28,\"*\"&$F10&\"*\")&\"/\"&SUMPRODUCT(ISNUMBER(SEARCH(F10,$C$3:$C$28))*IsYellow($C$3:$C$28))","v":"5/2"}]
[null,"2017-08-05","Картофель",null,null,"Огурцы","3/0","6/1"]
[null,"2017-08-06","Огурцы",null,null,"Овощи (всего)",{"$f":"SUM(($B$3:$B$28<($G$4+1))*(MMULT(--ISNUMBER(SEARCH(TRANSPOSE($F$9:$F$11),$C$3:$C$28)),ROW($F$9:$F$11)^0)>0))&\"/\"&SUM(IsYellow($C$3:$C$28)*($B$3:$B$28<($G$4+1))*(MMULT(--ISNUMBER(SEARCH(TRANSPOSE($F$9:$F$11),$C$3:$C$28)),ROW($F$9:$F$11)^0)>0))","v":"7/1"},{"$f":"SUM(--(MMULT(--ISNUMBER(SEARCH(TRANSPOSE($F$9:$F$11),$C$3:$C$28)),ROW($F$9:$F$11)^0)>0))&\"/\"&SUM(IsYellow($C$3:$C$28)*(MMULT(--ISNUMBER(SEARCH(TRANSPOSE($F$9:$F$11),$C$3:$C$28)),ROW($F$9:$F$11)^0)>0))","v":"12/2"},"Расчет для упоминания хотя бы одного наименования из всей группы"]
[null,"2017-08-07","Яблоки"]
[null,"2017-08-08","Яблоки",null,"То, что нужно посчитать. К формулам из верхней таблицы добавляем еще одно условие - цвет ячейки",null,null,null,null,"То, что хочу получить в результате - в дополнение к расчету из верхней таблицы через дробь такой же расчет, но с учетом цвета ячейки"]
[null,"2017-08-10","Картофель, Томаты"]
[null,"2017-08-10","Апельсины"]
[null,"2017-08-10","Томаты"]
[null,"2017-09-01","Огурцы, Картофель",null,"Группа","Наименование","По","Всего в справке",null,"Группа","Наименование","По","Всего в справке"]
[null,"2017-09-02","Груши",null,null,null,"2017-08-10",null,null,null,null,"2017-08-10"]
[null,"2017-09-03","Апельсины",null,"Фрукты","Яблоки",2,3,null,"Фрукты","Яблоки","6 / 2","9 / 3"]
[null,"2017-09-04","Яблоки",null,null,"Груши",1,1,null,null,"Груши","2 / 1","5 / 1"]
[null,"2017-09-05","Яблоки, Апельсины, Томаты, Огурцы",null,null,"Апельсины",0,2,null,null,"Апельсины","2 / 0","5 / 2"]
[null,"2017-09-06","Яблоки",null,null,"Фрукты (всего)",2,4,null,null,"Фрукты (всего)","8 / 2","16 / 4"]
[null,"2017-09-07","Огурцы",null,"Овощи","Картофель",0,1,null,"Овощи","Картофель","3 / 0","5 / 1"]
[null,"2017-09-08","Томаты",null,null,"Томаты",0,2,null,null,"Томаты","3 / 0","5 / 2"]
[null,"2017-09-09","Картофель, Груши",null,null,"Огурцы",0,1,null,null,"Огурцы","3 / 0","6 / 1"]
[null,"2017-09-10","Апельсины",null,null,"Овощи (всего)",0,2,null,null,"Овощи (всего)","7 / 0","12 / 2"]
[null,"2017-09-11","Груши"]