COUNTIFS para vários critérios em várias planilhas

Question

Ajude os rapazes!

Estou tentando criar uma fórmula muito curta para computar vários dados em várias planilhas.

Eu tenho uma pasta de trabalho em que eu compilo relatórios de erro que recebo de várias pessoas. Os relatórios de erro que eles enviam são os que eles veem de 22 materiais diferentes. Gostaria de agrupar os relatórios de erros por mês a partir de dezembro de 2014 a dezembro de 2015.

Em cada planilha, criei uma coluna separada chamada "Status" para indicar se cada um dos erros relatados já foi "Enviado", se está em "Espera", se foi "Enviado para o Tradutor" etc. Em uma planilha separada , Listei os nomes de todos os 22 materiais na primeira linha e listei as datas semanais na primeira coluna.

Basicamente, o que eu gostaria de fazer é contar os erros em um material específico de De dezembro de 2014 a dezembro de 2015 se e somente se os erros caírem em "Status" específicos e < em> os erros foram enviados dentro de um prazo específico (datas semanais, por exemplo, de 23 de fevereiro a 1 de março).

Até agora, estou usando essa fórmula muito longa:

=COUNTIFS('December 2014'!$A:$A,">="&$A15,'December 2014'!$A:$A,"<="&$B15,'December 2014'!$J:$J,"Applied",'December 2014'!$G:$G,"="&C$1)+COUNTIFS(January!$A:$A,">="&$A15,January!$A:$A,"<="&$B15,January!$J:$J,"Applied",January!$G:$G,"="&C$1)+COUNTIFS(February!$A:$A,">="&$A15,February!$A:$A,"<="&$B15,February!$J:$J,"Applied",February!$G:$G,"="&C$1)+COUNTIFS(March!$A:$A,">="&$A15,March!$A:$A,"<="&$B15,March!$J:$J,"Applied",March!$G:$G,"="&C$1)+COUNTIFS('December 2014'!$A:$A,">="&$A15,'December 2014'!$A:$A,"<="&$B15,'December 2014'!$J:$J,"Sent to Translator",'December 2014'!$G:$G,"="&C$1)+COUNTIFS(January!$A:$A,">="&$A15,January!$A:$A,"<="&$B15,January!$J:$J,"Sent to Translator",January!$G:$G,"="&C$1)+COUNTIFS(February!$A:$A,">="&$A15,February!$A:$A,"<="&$B15,February!$J:$J,"Sent to Translator",February!$G:$G,"="&C$1)+COUNTIFS(March!$A:$A,">="&$A15,March!$A:$A,"<="&$B15,March!$J:$J,"Sent to Translator",March!$G:$G,"="&C$1)+COUNTIFS('December 2014'!$A:$A,">="&$A15,'December 2014'!$A:$A,"<="&$B15,'December 2014'!$J:$J,"Waiting for Upload",'December 2014'!$G:$G,"="&C$1)+COUNTIFS(January!$A:$A,">="&$A15,January!$A:$A,"<="&$B15,January!$J:$J,"Waiting for Upload",January!$G:$G,"="&C$1)+COUNTIFS(February!$A:$A,">="&$A15,February!$A:$A,"<="&$B15,February!$J:$J,"Waiting for Upload",February!$G:$G,"="&C$1)+COUNTIFS(March!$A:$A,">="&$A15,March!$A:$A,"<="&$B15,March!$J:$J,"Waiting for Upload",March!$G:$G,"="&C$1)+COUNTIFS('December 2014'!$A:$A,">="&$A15,'December 2014'!$A:$A,"<="&$B15,'December 2014'!$J:$J,"Uploaded",'December 2014'!$G:$G,"="&C$1)+COUNTIFS(January!$A:$A,">="&$A15,January!$A:$A,"<="&$B15,January!$J:$J,"Uploaded",January!$G:$G,"="&C$1)+COUNTIFS(February!$A:$A,">="&$A15,February!$A:$A,"<="&$B15,February!$J:$J,"Uploaded",February!$G:$G,"="&C$1)+COUNTIFS(March!$A:$A,">="&$A15,March!$A:$A,"<="&$B15,March!$J:$J,"Uploaded",March!$G:$G,"="&C$1)+COUNTIFS('December 2014'!$A:$A,">="&$A15,'December 2014'!$A:$A,"<="&$B15,'December 2014'!$J:$J,"Audio",'December 2014'!$G:$G,"="&C$1)+COUNTIFS(January!$A:$A,">="&$A15,January!$A:$A,"<="&$B15,January!$J:$J,"Audio",January!$G:$G,"="&C$1)+COUNTIFS(February!$A:$A,">="&$A15,February!$A:$A,"<="&$B15,February!$J:$J,"Audio",February!$G:$G,"="&C$1)+COUNTIFS(March!$A:$A,">="&$A15,March!$A:$A,"<="&$B15,March!$J:$J,"Audio",March!$G:$G,"="&C$1)+COUNTIFS('December 2014'!$A:$A,">="&$A15,'December 2014'!$A:$A,"<="&$B15,'December 2014'!$J:$J,"Standby",'December 2014'!$G:$G,"="&C$1)+COUNTIFS(January!$A:$A,">="&$A15,January!$A:$A,"<="&$B15,January!$J:$J,"Standby",January!$G:$G,"="&C$1)+COUNTIFS(February!$A:$A,">="&$A15,February!$A:$A,"<="&$B15,February!$J:$J,"Standby",February!$G:$G,"="&C$1)+COUNTIFS(March!$A:$A,">="&$A15,March!$A:$A,"<="&$B15,March!$J:$J,"Standby",March!$G:$G,"="&C$1)

Até agora, apenas de dezembro de 2014 a março de 2015. Não consigo imaginar quanto tempo vai ficar assim que eu incluir os outros meses.

Existe alguma maneira de tornar essa fórmula mais curta?

Muito obrigado antecipadamente! :)

    

Answer 1

Acho que há pelo menos duas opções viáveis. Com base nas suas circunstâncias, pode ser mais adequado que o outro. Vou te dar as duas opções que eu poderia pensar e ver se você pode trabalhar a partir daí.

Opção 1: Power Pivot
O Power Pivot da Microsoft permite combinar várias fontes de dados semelhantes e criar uma tabela dinâmica a partir dele. Se você adicionar cada planilha como fonte de dados e adicioná-la aos seus dados, poderá girar e filtrar de acordo com seus critérios.

Opção 2: usar INDIRETO
Você pode usar a função INDIRECT para aplicar seus critérios. Se eu entendi corretamente, eu faria uma matriz, contendo os meses (nomes da planilha) no eixo vertical e o status (Aplicado, Enviado, Aguardando upload, ...) no eixo horizontal. Em seguida, você pode preencher sua matriz com sua fórmula COUNTIF , usando a função INDIRECT para fazer referência às diferentes planilhas e solicitar os diferentes status. Por exemplo, se você tiver "dezembro de 2014" em A2, INDIRECT("'"&A2&"'!$A:$A") se referiria a 'December 2014'!$A:$A . Em seguida, basta adicionar os valores horizontalmente e verticalmente para criar subtotais por status e por mês. A soma da matriz inteira é o resultado desejado.

Editar:
opção 2 explicada:
Conforme solicitado pelo OP, aqui está uma explicação mais detalhada sobre a segunda opção. Para ilustrar, vou apresentar Anna, Ben e Charles, que seguem três cursos. Eles recebem várias notas (variando de 1 a 10) para cada curso. Cada série conta para um trimestre específico. O leitor do curso quer comparar o número de notas suficientes (> 5,5) para cada aluno em cada curso no primeiro trimestre. Aqui estão os diferentes cursos (cada curso está em uma planilha separada, chamada "Curso1", "Curso2" e "Curso3").

Semelhanteaoseuproblema,temosvárioscritérios(>5,5,trimester=1,contarparavárioscursos,bemcomoparaváriosalunos).Acélulainternadentrodamatriz(Curso1xAnna,naminhaplanilhaissoestáemB5)contémaseguintefórmula:

=COUNTIFS(INDIRECT($A5&"!A:A");B$4;INDIRECT($A5&"!B:B");$C$2;INDIRECT($A5&"!C:C");$C$1)

Referências de células:

• A5 aponta para a célula que contém "Course1"
• B4 aponta para a célula que contém "Anna"
• C2 aponta para a célula que contém "1" (para o trimestre)
• C1 aponta para a célula que contém "> 5,5" (para a nota)

A tradução para o seu problema é a seguinte:

• Cursos - > meses
• Nomes dos alunos - > Status
• E o resto é trivial

Basicamente, o truque é listar os nomes das folhas em uma lista e usá-las em combinação com a função INDIRECT para fazer referência aos meses diferentes. Se você fizer o mesmo para os diferentes statuses, você terá uma matriz. Conte (usando COUNTIFS ) os valores que você deseja contar. O total geral dos valores na matriz é o número total (somado para diferentes meses e diferentes estatísticas) que você está procurando.

Outra maneira de colocar isso: em vez de forjar uma fórmula complexa, tente deixar o Excel fazer o trabalho para você.