Блог

У меня есть эта огромная, но красивая формула, которая была умно составлена уважаемым участником Stackoverflow @player0, которая работает очень хорошо.

• Мне нужно добавить общие строки в 3 сгенерированные таблицы запросов. Поскольку я не совсем понимаю формулу, у меня возникают трудности с добавлением полных строк. Каждая итоговая строка будет суммировать значения в столбцах, за исключением последней таблицы, которая будет принимать среднее значение процентов в столбцах.
• Кроме того, если данные недоступны за определенный год, это приводит к ошибке #VALUE. Мне нужно показать какое-либо сообщение или Blank данные, если они недоступны, например Data not available for this year .

Вот FTRTable с доступом к редактированию.

Я разработал формулу для добавления общих строк для 1-й таблицы 2, но не знаю, куда ее вставить, например:

 {TRANSPOSE(QUERY('Study Report'!$A:$T,"SELECT COUNT(B) WHERE YEAR(S) = "amp; $C$1 amp;" AND O != 'NO - all fine' AND L != 'Cancelled' GROUP BY S LABEL COUNT(B) 'TOTAL'",0))}
 

и

 {TRANSPOSE(QUERY('Study Report'!$A:$T,"SELECT COUNT(B) WHERE YEAR(S) = "amp; $C$1 amp;" AND L != 'Cancelled' GROUP BY S LABEL COUNT(B) 'TOTAL'",0))}
 

————РЕДАКТИРОВАТЬ:————

Обновлена формула в разделе Clubbed Tables Добавить Totals для ALL Tables . Это хорошо работает на моем рабочем листе, но не в электронной таблице FTR Table. The only thing remaining:

• Как мне удалить ошибку за определенные годы, когда данные могут быть недоступны, #VALUE и отобразить пустой или какой-либо другой текст?

Обновленная формула, в которой все 3 таблицы имеют свои итоговые строки:

 =ARRAYFORMULA(IF(C1="", "select Year in C1", {TRANSPOSE(QUERY(TRANSPOSE(QUERY(IFNA(
 HLOOKUP(INDEX(TRANSPOSE(QUERY('Study Report'!$A:$T, 
 "select S,count(B) 
  where S is not null 
    and year(S)="amp;C1amp;" 
    and L!='Cancelled' 
    and O!='NO - all fine' 

  group by S 
  pivot N 
  label S 'NO OF ISSUES'")), 1), TRANSPOSE(QUERY({'Study Report'!$A:$T}, 
 "select Col19,count(Col2) 
  where Col19 is not null
    and year(Col19)="amp;C1amp;" 
    and Col12!='Cancelled' 
    and Col15 != 'NO - all fine' 
  group by Col19 
  pivot Col14 
  label Col19 'NO OF ISSUES'")), ROW(A:A), 0)), 
 "where Col1 <> '#REF!'")), 
 " format Col1 'yyyy-mmm'")); TRANSPOSE(QUERY('Study Report'!$A:$T,"SELECT COUNT(B) WHERE YEAR(S) = "amp; $C$1 amp;" AND O != 'NO - all fine' AND L != 'Cancelled' GROUP BY S LABEL COUNT(B) 'TOTAL'",0));
 SPLIT(REPT(" ♠", COLUMNS(TRANSPOSE(QUERY('Study Report'!$A:$T, 
 "select S,count(B) 
  where S is not null
    and year(S)="amp;C1amp;" 
    and L!='Cancelled' 
  group by S 
  pivot N 
  label S 'TOTAL OPPORTUNITIES'")))), "♠"); 
 TRANSPOSE(QUERY('Study Report'!$A:$T, 
 "select S,count(B) 
  where S is not null
    and year(S)="amp;C1amp;" 
    and L!='Cancelled' 
  group by S 
  pivot N 
  label S 'TOTAL OPPORTUNITIES'")); TRANSPOSE(QUERY('Study Report'!$A:$T,"SELECT COUNT(B) WHERE YEAR(S) = "amp; $C$1 amp;" AND L != 'Cancelled' GROUP BY S LABEL COUNT(B) 'TOTAL'",0));
 SPLIT(REPT(" ♠", COLUMNS(TRANSPOSE(QUERY('Study Report'!$A:$T, 
 "select S,count(B) 
  where S is not null
    and year(S)="amp;C1amp;" 
    and L!='Cancelled' 
  group by S 
  pivot N 
  label S 'TOTAL OPPORTUNITIES'")))), "♠"); 

 REGEXREPLACE(TO_TEXT(QUERY(QUERY({QUERY(
 {'Study Report'!S1:Samp;"♦"amp;'Study Report'!N1:N, 'Study Report'!B1:T, 'Study Report'!S1:Samp;
 IF('Study Report'!S1:S="",,"♥"amp;TEXT('Study Report'!S1:S, "yyyy-mmm"))}, 
 "select Col1,Col19,Col14,Col21,count(Col2) where Col19 is not null and year(Col19)="amp;C1amp;" and Col12 != 'Cancelled' group by Col1,Col19,Col14,Col21"),
 IFNA(VLOOKUP(INDEX(QUERY({'Study Report'!S1:Samp;"♦"amp;'Study Report'!N1:N, 'Study Report'!B1:T}, 
 "select Col1,Col19,Col14,count(Col2) where Col19 is not null and year(Col19)="amp;C1amp;" and Col12 != 'Cancelled' group by Col1,Col19,Col14"),,1), 
 QUERY({'Study Report'!S1:Samp;"♦"amp;'Study Report'!N1:N, 'Study Report'!B1:T}, 
 "select Col1,count(Col2) where Col19 is not null and year(Col19)="amp;C1amp;" and Col12 != 'Cancelled' and Col15!= 'NO - all fine' group by Col1"), 2, 0))}, 
 "select Col4,Col3,Col6/Col5"), 
 "select Col2,max(Col3) group by Col2 pivot Col1 label Col2'FTR%' format max(Col3)'#.00%'")), "(d ♥)", );

regexreplace(to_text(transpose(query(query(QUERY({QUERY(
 {'Study Report'!S1:Samp;"♦"amp;'Study Report'!N1:N, 'Study Report'!B1:T, 'Study Report'!S1:Samp;
 IF('Study Report'!S1:S="",,"♥"amp;TEXT('Study Report'!S1:S, "yyyy-mmm"))}, 
 "select Col1,Col19,Col14,Col21,count(Col2) where Col19 is not null and year(Col19)="amp;C1amp;" and Col12 != 'Cancelled' group by Col1,Col19,Col14,Col21"),
 IFNA(VLOOKUP(INDEX(QUERY({'Study Report'!S1:Samp;"♦"amp;'Study Report'!N1:N, 'Study Report'!B1:T}, 
 "select Col1,Col19,Col14,count(Col2) where Col19 is not null and year(Col19)="amp;C1amp;" and Col12 != 'Cancelled' group by Col1,Col19,Col14"),,1), 
 QUERY({'Study Report'!S1:Samp;"♦"amp;'Study Report'!N1:N, 'Study Report'!B1:T}, 
 "select Col1,count(Col2) where Col19 is not null and year(Col19)="amp;C1amp;" and Col12 != 'Cancelled' and Col15!= 'NO - all fine' group by Col1"), 2, 0))}, 
 "select Col4,Col3,Col6/Col5"),"select Col1, avg(Col3) where Col1 is not null group by Col1 format avg(Col3) '0.00%'"),"Select Col2 label Col2 'TOTAL'"))),"(d ♥)",)
}))