Consulta / consulta complicada, tem que haver uma maneira mais fácil

Question

Eu já vi essa necessidade várias vezes, e só posso concluir que tem que haver uma maneira mais fácil e escalável de fazer o que estou tentando fazer aqui.

Basicamente, eu preciso de uma tabela tridimensional que eu possa fazer totais contra ...

Dadas duas tabelas: %código% e | People | One | Two | Three | Four | Five | Six | |--------|-----|-----|-------|------|------|-----| | John | x | x | | | | | | James | | x | x | | | | | Jim | | | x | x | | | | Jean | | | | x | x | | | Jammie | | | | | x | x | | Janis | x | | | | | x |

Estou gerando totais, com base em células não vazias na primeira tabela, e os valores correspondentes na segunda tabela para produzir: | Event | Data | |-------|------| | One | 1 | | Two | 2 | | Three | 3 | | Four | 4 | | Five | 5 | | Six | 6 |

Estou usando um vlookup complicado para produzir isso: | Totals | |--------| | 3 | | 5 | | 7 | | 9 | | 11 | | 7 |

Obviamente, isso não é escalonável e deixa muito espaço para erros humanos. Na prática, os aplicativos reais envolvem adicionar ou remover usuários e "eventos". Eu sinto que já me deparei com isso o suficiente para não estar sozinha e que devo estar fazendo isso da maneira mais difícil. Existe uma opção mais fácil?

* Observe que neste exemplo estou usando uma soma, mas, em alguns casos, posso ter um texto que eu queira fazer =SUM( IF(NOT(ISBLANK(Table1[@One])),VLOOKUP(Table1[[#Headers],[One]],Table2,2,FALSE),0), IF(NOT(ISBLANK(Table1[@Two])),VLOOKUP(Table1[[#Headers],[Two]],Table2,2,FALSE),0), IF(NOT(ISBLANK(Table1[@Three])),VLOOKUP(Table1[[#Headers],[Three]],Table2,2,FALSE),0), IF(NOT(ISBLANK(Table1[@Four])),VLOOKUP(Table1[[#Headers],[Four]],Table2,2,FALSE),0), IF(NOT(ISBLANK(Table1[@Five])),VLOOKUP(Table1[[#Headers],[Five]],Table2,2,FALSE),0), IF(NOT(ISBLANK(Table1[@Six])),VLOOKUP(Table1[[#Headers],[Six]],Table2,2,FALSE),0) ) on ou algo similar.

    

Answer 1

Esta fórmula de matriz, preenchida com H2 na captura de tela abaixo, fornece os resultados mostrados.

=SUM(NOT(ISBLANK($B2:$G2))*TRANSPOSE(B$10:B$15))

Como esta é uma fórmula de matriz, ela deve ser digitada com CTRL Shift Enter , em vez de apenas Enter .

Comofunciona:NOT(ISBLANK($B2:$G2))forneceumamatrizdevaloresTrue/FalsecomFalsesemprequeumacélulanesseintervaloestivervazia(consulteaNotaabaixo).SevocêdestacaressapartedafórmulanabarradefórmulasepressionarateclaF9,veráamatriz{TRUE,TRUE,FALSE,FALSE,FALSE,FALSE}.(DigiteCTRL-Zparadesfazerisso).

(B$10:B$15)éapenasumalistadosnúmerosnacoluna"Dados" e TRANSPOSE() converte-os de uma matriz vertical para uma horizontal.

Na multiplicação, os valores True/False são tratados como 1 e 0 , portanto, o resultado é uma matriz com o valor Data onde havia um x e zero em outro lugar. Então, SUM() apenas adiciona a matriz e retorna os totais conforme eles são preenchidos.

Editar:
OP adicionou a sua pergunta, dizendo que ele poderia ter valores de texto em B6:B10 e operá-los, não com SUM() , mas outra função, possivelmente TEXTJOIN() .

Para lidar com a possibilidade de valores não numéricos, esta expressão:

IF(NOT(ISBLANK($B2:$G2)),TRANSPOSE(B$10:B$15),)

retorna uma matriz com o valor "Dados" onde quer que haja um "x" e False em outro lugar. Ele funciona corretamente para valores numéricos e de texto.

Incluindo o value_if_false no IF() (após a última vírgula), os valores de False podem ser substituídos por 0 ou blank ou o que possa ser exigido pela função que atua no array.

Notas:

1. ISBLANK() deve realmente ser nomeado ISEMPTY() , porque uma célula tem para estar realmente vazio para ISBLANK() retornar True . Se a célula contém uma fórmula, mas aparece em branco, ISBLANK() retornará %código%. False tem o mesmo problema, então se você tiver fórmulas nas células "em branco", você pode alterar o primeiro array para %código%
2. Se ISTEXT() não estivesse presente, a multiplicação (e a (($B2:$G2)="x") função) daria uma matriz 2D, que ficaria confuso.

Espero que isso ajude e boa sorte.

    

Answer 2

Depois de pensar um pouco mais sobre isso, há uma solução muito mais simples usando SUMIF() . Mas sua segunda mesa deve ser organizada horizontalmente.

=SUMIF(B2:G2,"x",B$10:G$10)