☰

Mesa cruzada de jogos conjuntos

tag-icon • tag-icon microsoft-excel worksheet-function countif
Mesa cruzada de jogos conjuntos

Eu tenho uma lista de jogos de badminton que foram disputados durante um período de tempo:

dado disponível

Cada linha contém o ID do jogo e um ID do jogador. Cada jogo foi disputado com quatro jogadores. Assim, para cada jogo existem quatro linhas (uma para cada jogador). Como temos muitos jogos, existem muitas duplicatas na lista de jogadores.

Desejo criar uma tabela cruzada que me diga quantas vezes dois jogadores jogaram juntos.

mesa desejada

Isso pode ser feito com uma fórmula? Qual seria essa fórmula?

--- EDITAR --- Para responder às perguntas de Scott:

(1) Uma rodada é igual a um jogo?

Eu cometi um erro. Cada rodada tem vários jogos; um em cada quadra disponível. Eu deveria ter dito 'jogo' em vez de 'rodada'. Eu corrigi isso acima.

(2) Qual é a relação entre a rodada X_1 e a rodada X_2?

O número indica a quadra onde o jogo foi disputado. O exemplo tem dois tribunais, na realidade temos nove.

(3) Mostre-nos o resultado desejado, não apenas o formato do resultado desejado.

Eu substituí a imagem. À mão fiz a mesa do exemplo. ESK e GEN jogaram juntos os dois jogos. YUG e PED jogaram apenas um jogo. Espero que seja isso que você quis dizer.

Responder1

Gráfico de grade deEm itens comunsContar entre unidades

Exibe números de grade para a contagem de itens que duas unidades têm em comum.

Gráfico de frequência mútua de itens.

[Amostra

A Fórmula da Grade (CSE):

=IF(OR(E$2=$D3,E$2="",$D3=""),"",SUM(IFNA(MATCH(IF(E$2=$B$2:$B$102,$A$2:$A$102,NA()),IF($D3=$B$2:$B$102,$A$2:$A$102),0)^0,0)))
  • Colar em: E3
  • Esta é uma fórmula CSE, então pressione Ctrl- Shift-Enter
  • Copiar Arraste para a esquerda, solte e com todas as células copiadas ainda selecionadas, copie e arraste para baixo.
    Ok para arrastar além dos cabeçalhos (preencher com espaços em branco até que seja necessário).
  • Ajustes de cabeçalho: linha 2 do título da coluna E$2e coluna D do título da linha $D3. Duas ocorrências cada.
  • Intervalos de itens e unidades, $A$2:$A$102 e $B$2:$B$102, respectivamente.

Fórmulas de cabeçalho:

Linha=IFERROR(INDEX($B$2:$B$17,MATCH(0,COUNTIF($D$2:D$2,$B$2:$B$17),0)),"")

  • Colar no E2

Coluna=IFERROR(INDEX($B$2:$B$17,MATCH(0,COUNTIF($D$2:$D2,$B$2:$B$17),0)),"")

  • Colar em D3
  • Ambos são CSE, então pressione Ctrl- Shift- Entere copie e arraste até que haja itens de cabeçalho adicionais futuros (exibe em branco após valores distintos).
  • Não coloque um valorD2que pode aparecer na coluna Unidades.
    • D2 é a posição ZERO dos intervalos de expansão.
    • Ambos $D$2:D$2e estão expandindo os intervalos para as entradas anteriores $D$2:$D2do cabeçalho correspondente ;
      usado para criar itens de cabeçalho distintos.
  • As fórmulas de cabeçalho são opcionais, mas os cabeçalhos não. Tenha cuidado ao inserir manualmente.
  • Esses cabeçalhos estão em ordem de aparição pela primeira vez. Use uma SMALLfórmula distinta para classificação.

Formatação condicional:

Fórmula diagonal =AND(E$2<>"",$D3=E$2)
Aplica-se a: $E$3:$V$20onde$V$20é o canto inferior direito da grade.
Aplique preenchimento de formatação ou algo assim.

  • Escala de cores graduada (mostrada na imagem).
  • Liste a regra diagonal primeiro. (A fórmula pode ser editada para mostrar uma frequência unitária na diagonal.)
  • Esconda Zeros com issoFormatar célula#;;"";""pincel de formato personalizado sobre a grade.

Mostre a frequência das unidades na diagonal:

Remova a proteção na fórmula da grade que mostraem branco ""quando os valores do cabeçalho da coluna e da linha são iguais.

=IF(OR(E$2=$D3,E$2="",$D3=""),""...(see entire formula above)
.      ^^^^^^^^
  • Exclua E$2=$D3,da fórmula da grade (incluindo a vírgula final).
  • CSE após a edição e copie novamente e arraste.

informação relacionada