Monitorando melhor configuração experimental no EXCEL com estrutura

Question

Estou realizando um lote de experimentos e acompanho as seguintes quantidades em uma tabela:

• Assunto do experimento
• Dia da experiência
• Contexto da experiência
• Resultado esperado para esse dia
• Resultado real para esse dia
• Erro relativo para esse dia

Essas quantidades estão em uma tabela cujas colunas estão rotuladas como ASSUNTO, DIA, CONTEXTO, ESPERADO, REAL, RELATIVO. Eu quero produzir uma tabela que monitora os experimentos, resultados, onde para todas as combinações de SUBJECT * DAY, o CONTEXTO que leva ao menor ERRO relativo é exibido, assim como o correspondente EXPECTED, ACTUAL e RELATIVE.

Parece que uma combinação de INDEX, VLOOKUP, MATCH e MIN pode ser usada para conseguir isso, no entanto eu não consegui fazer isso direito usando referências estruturadas, que é importante para mim desde o estrutura das tabelas evolui muito.

Como dados de exemplo

| SUBJECT | DAY | CONTEXT | EXPECTED | ACTUAL | RELATIVE |
| A       | 1   |       x |       42 |     0  |     100% |
| A       | 1   |       y |       42 |    42  |       0% |
| B       | 1   |       x |       21 |    10  |      50% |
| B       | 1   |       x |       21 |     0  |     100% |
| A       | 2   |       x |        3 |     1  |      66% |

Eu deveria ter o seguinte na minha tabela de monitoramento:

| A       | 1   |       y |       42 |    42  |       0% |
| B       | 1   |       x |       21 |    10  |      50% |

Como uma nota lateral, eu realmente tenho várias medidas na tabela de dados e quero monitorar cada uma delas separadamente.

EDIT. Como sugerido nos comentários, tentei criar uma tabela dinâmica. Consegui produzir uma tabela dinâmica com linhas rotuladas por SUBJECT e colunas rotuladas por DAYS, nas quais o melhor erro RELATIVE é exibido. Isso ainda está muito longe da tabela de monitoramento acima.

    

Answer 1

Se você quiser fazer isso no excel, não seria tão difícil, mas usar algumas colunas auxiliares seria benéfico.

Eu adicionaria um identificador de linha exclusivo e também potencialmente outra coluna com concatena seu assunto e o dia =B3 & C3 . O que facilitaria a criação de seus valores exclusivos no Excel.

Na tabela de resultados, você encontrará primeiro o ID da experiência desejado e, em seguida, preencherá as colunas usando vlookup ou index + match .

A fórmula da ID da experiência é uma fórmula de matriz inserida usando ctrl + shift + enter e localiza o valor mínimo na coluna relativa que corresponde ao assunto e ao dia. Se houver dois com o mesmo valor, escolherá o mesmo

A fórmula é (insira com ctrl + shift + enter) na célula A10:         =INDEX($A$3:$A$7,MATCH(MIN(IF($B$3:$B$7=B10,IF($C$3:$C$7=C10,$G$3:$G$7,9999),9999)),IF($B$3:$B$7=B10,IF($C$3:$C$7=C10,$G$3:$G$7,9999),9999),0))

Para as outras colunas, basta usar uma pesquisa (ou prefiro a correspondência de índice)

A fórmula da célula D10 é =INDEX(D$3:D$7,MATCH($A10,$A$3:$A$7,0)) . Esta fórmula pode ser copiada para todas as outras células e também funcionará.

Gordon