Блог

Я пытаюсь создать перекрестные pivot tables таблицы, используя Google Query function для расчета сотрудников First Time Resolution (FTR) rate на основе number of Issues received while booking Opportunities vs Total Opportunities booked .

  --------- ------- --------- ----------- -------- ------- 
| OppName | OppID | EmpName | MonthYear | Status | Issue |
 ========= ======= ========= =========== ======== ======= 
| abc     | 1000  | alex    | 2020-Jan  | active | yes   |
 --------- ------- --------- ----------- -------- ------- 
| def     | 1001  | alex    | 2020-Jan  | won    | yes   |
 --------- ------- --------- ----------- -------- ------- 
| ghi     | 1002  | alex    | 2020-Feb  | active | no    |
 --------- ------- --------- ----------- -------- ------- 
| jkl     | 1004  | mini    | 2020-Feb  | lost   | yes   |
 --------- ------- --------- ----------- -------- ------- 
| mno     | 1005  | mini    | 2020-Feb  | won    | yes   |
 --------- ------- --------- ----------- -------- ------- 
| pqr     | 1006  | mini    | 2020-Mar  | active | no    |
 --------- ------- --------- ----------- -------- ------- 
| stu     | 1007  | mini    | 2020-Mar  | won    | yes   |
 --------- ------- --------- ----------- -------- ------- 
| vwx     | 1008  | joe     | 2020-Jan  | won    | no    |
 --------- ------- --------- ----------- -------- ------- 
| yza     | 1009  | joe     | 2020-Mar  | lost   | yes   |
 --------- ------- --------- ----------- -------- ------- 
  

ВЫВОДИТ:

 1. NO OF ISSUES:
 -------------- ---------- ---------- ---------- 
| NO OF ISSUES | 2020-Jan | 2020-Feb | 2020-Mar |
 -------------- ---------- ---------- ---------- 
| alex         |        2 |          |          |
 -------------- ---------- ---------- ---------- 
| mini         |          |        1 |        1 |
 -------------- ---------- ---------- ---------- 
| TOTAL        |        2 |        1 |        1 |
 -------------- ---------- ---------- ---------- 
  

ФОРМУЛА:

 =TRANSPOSE(QUERY($A$2:$F,"select D,count(B) WHERE D IS NOT NULL AND E!='lost' AND F = 'yes' Group by D PIVOT C LABEL D 'NO OF ISSUES'",0))
  

…

 2. TOTAL OPPORTUNITIES:
 --------------------- ---------- ---------- ---------- 
| TOTAL OPPORTUNITIES | 2020-Jan | 2020-Feb | 2020-Mar |
 --------------------- ---------- ---------- ---------- 
| alex                |        2 |        1 |          |
 --------------------- ---------- ---------- ---------- 
| joe                 |        1 |          |          |
 --------------------- ---------- ---------- ---------- 
| mini                |          |        1 |        2 |
 --------------------- ---------- ---------- ---------- 
| TOTAL               |        3 |        2 |        2 |
 --------------------- ---------- ---------- ---------- 
  

ФОРМУЛА:

 =TRANSPOSE(QUERY($A$2:$F,"select D,count(B) WHERE D IS NOT NULL AND E!='lost' Group by D PIVOT C LABEL D 'TOTAL OPPORTUNITIES'",0))
  

…

 3. FTR%:
 -------------- ---------- ---------- ---------- 
| FTR%         | 2020-Jan | 2020-Feb | 2020-Mar |
 -------------- ---------- ---------- ---------- 
| alex         |          |  100.00% |          |
 -------------- ---------- ---------- ---------- 
| joe          |  100.00% |          |          |
 -------------- ---------- ---------- ---------- 
| mini         |          |          |   50.00% |
 -------------- ---------- ---------- ---------- 
| AVERAGE FTR% |  100.00% |  100.00% |   50.00% |
 -------------- ---------- ---------- ---------- 
  

ФОРМУЛА:

 NEED QUERY FORMULA TO CALCULATE FTR AND GENERATE THIS TABLE.
  

Поэтому я планирую объединить все 3 таблицы в одну формулу запроса, подобную этой, как только будет определена формула 3-й таблицы:

 ={

{TRANSPOSE(QUERY($A$2:$F,"select COUNT(B) WHERE D IS NOT NULL AND E!='lost' AND F = 'yes' Group by D LABEL COUNT(B) 'FTR CALC' FORMAT COUNT(B) ' '",0))};
{TRANSPOSE(QUERY($A$2:$F,"select D,COUNT(B) WHERE D IS NOT NULL AND E!='lost' AND F = 'yes' Group by D PIVOT C LABEL D 'NO OF ISSUES'",0))};
{TRANSPOSE(QUERY($A$2:$F,"select COUNT(B) WHERE D IS NOT NULL AND E!='lost' AND F = 'yes' Group by D LABEL COUNT(B) 'TOTAL'",0))};

{TRANSPOSE(QUERY($A$2:$F,"select COUNT(B) WHERE D IS NOT NULL AND E!='lost' Group by D LABEL COUNT(B) ' ' FORMAT COUNT(B) ' '",0))};
{TRANSPOSE(QUERY($A$2:$F,"select D,COUNT(B) WHERE D IS NOT NULL AND E!='lost' Group by D PIVOT C LABEL D 'TOTAL OPPORTUNITIES'",0))};
{TRANSPOSE(QUERY($A$2:$F,"select COUNT(B) WHERE D IS NOT NULL AND E!='lost' Group by D LABEL COUNT(B) 'TOTAL'",0))}

}
  

Формула в каждой ячейке 3-й таблицы будет:

 =IFERROR(IF(100%-(I2/I7)*100%=0,"",100%-(I2/I7)*100%),"")
  

или просто,

 =100%-(I2/I7)*100%   'above 2 crosstabs starting from column H
  

Может кто-нибудь помочь мне сгенерировать 3-ю таблицу?

Ну, я нашел это немного запутанным, но, если я правильно понял, у меня есть ответ, который может делать то, что вы хотите. Я не совсем понимаю, хотите ли вы, чтобы все это делалось «на лету», или вы допускаете отображение промежуточных результатов табуляции в вашем листе — вы ссылались на таблицы 1, 2 и 3. У меня есть формула для третьей таблицы, но она основана на двух промежуточных таблицах. Возможно, формулы можно объединить, чтобы исключить промежуточные таблицы.

Рассмотрим эту формулу в разделе H9 на моем примере листа:

 ={
  {"FTR%",TRANSPOSE(SORT(UNIQUE(FILTER(D3:D,D3:D<>""))))}; 
  {                 SORT(UNIQUE(FILTER(C3:C,C3:C<>""))),
   ARRAYFORMULA(IFERROR(IF((N3:P5/I3:K5)=0,"",N3:P5/I3:K5),""))}
 }
  

При первом транспонировании / сортировке месяцы отображаются сверху, при второй сортировке имена всех агентов помещаются в столбец 1, а arrayformula делит количество разрешений на количество возможностей из двух промежуточных таблиц, чтобы заполнить значения FTR.

Обратите внимание, что я ограничил диапазон промежуточных таблиц, но только из-за включения элементов под ними для целей отображения. Эти диапазоны можно сделать неограниченными, как и в таблице входных данных.

Если это не на правильном пути, сообщите нам, что вам еще нужно.

Смотрите Мой примерный лист здесь. https://docs.google.com/spreadsheets/d/16otcDfnmWMEL0YpgH3aH0vn4Gip6XEY7yPDLQ9Y-83A/edit?usp=sharing

Решение для скриптов приложений

Поскольку у вас уже есть ответ для функций, который, я полагаю, приведет вас к желаемому результату, я хочу представить альтернативное решение вашей проблемы. Помимо того, что мне намного проще (для меня) использовать Apps Script, он кажется гораздо более подходящим инструментом для такого рода заданий.

^{Хотя я знаю, что есть много любителей делать все с помощью формул, сила вам.}

Я основал пример на образце электронной таблицы, приведенной в ответе @kirkg13, поскольку ему было намного легче следовать, чем в вопросе

Сначала следуйте этому руководству, если вы не знаете, как создать проект скрипта приложений.

Затем заполните свои данные на листе, подобном этому:

С именем листа «inputData». Столбцы должны быть одинаковыми, хотя объем данных зависит от вас.

Создайте еще один лист под названием «Отчет» и оставьте его пустым.

Скопируйте этот код в свой редактор сценариев:

 function main() {
  var dataRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("InputData").getDataRange()
  
  var data = dataRange.getValues()
  var headers = data.shift()
  
  var dateRange = getDateRange(data)
  var agentData = buildAgentData(data)
  
  var reportData = buildReport(dateRange, agentData, headers)
  
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Report").getRange(1,1,reportData.length, reportData[0].length).setValues(reportData)
}

function getDateRange(data) {
  
  var dates = data.map( (record) => {return record[3]} )
  var minDate = new Date(Math.min.apply(null, dates))
  var maxDate = new Date(Math.max.apply(null, dates))
  
  var counter = minDate
  var dateRange = []
  while (counter <= maxDate) {
    dateRange.push(new Date(counter))
    counter.setDate(counter.getDate()   1);
  }
  return dateRange
}

function buildAgentData(data) {
  
  agentData = {}
  
  data.forEach( (record) => {
    var name = record[2]
    var date = record[3]
    var resolved = record[6]
   
    if (!(name in agentData)) {
      agentData[name] = {[date]:{"cases":1, "resolved":resolved}}
    } else if (date in agentData[name]) {
      agentData[name][date]["cases"]  = 1
      if (resolved == 1) {
        agentData[name][date]["resolved"]  = 1
      }
    } else {
      agentData[name][date] = {"cases":1, "resolved":resolved}
    }
  })
  
  return agentData
}

function buildReport(dateRange, agentData, headers) {
  
  // BUILDING DATA ROWS
  
  var opportunities = [];
  var resolutions = [];
  var ftr = [];
    
  for(agent in agentData) {
    
    var rowOpp = [agent];
    var rowRes = [agent];
    var rowFtr = [agent];
    
    dateRange.forEach( (date) => {
    
      try {rowOpp.push(agentData[agent][date]["cases"])}
        catch(e){rowOpp.push("0")};
      try {rowRes.push(agentData[agent][date]["resolved"])}
        catch(e){rowRes.push("0")};
      try {rowFtr.push(agentData[agent][date]["resolved"] / agentData[agent][date]["cases"])}
        catch(e){rowFtr.push("0")};
    
    });
    
    opportunities.push(rowOpp);
    resolutions.push(rowRes);
    ftr.push(rowFtr);
  }
  
  
  // BUILDING HEADERS
  
  var headerOpp = ["Opportunities"]
  var headerRes = ["Resolutions"]
  var headerFtr = ["FTR"]
  
  dateRange.forEach( (date) => {
                    headerOpp.push("");
                    headerRes.push("");
                    headerFtr.push("");
                    });
  
  dateRange.unshift("");
  
  // BUILDING FINAL REPORT
  
  var report = [dateRange,headerOpp]
  opportunities.forEach((row) => {report.push(row)})
  report.push(headerRes)
  resolutions.forEach((row) => {report.push(row)})
  report.push(headerFtr)
  ftr.forEach((row) => {report.push(row)})
  
  return report
}
  

Вкладка «Отчет» будет заполнена этими динамическими таблицами.

Это всего лишь основа для использования в качестве учебного пособия или для адаптации к вашим конкретным потребностям.

Справочный материал для скрипта приложений

• Главная страница
• Ссылка на электронную таблицу