Excel: E se uma equipe específica não tivesse entrado em um torneio baseado em liga?

Question

Imagineumtorneioemquequatroequipesentraramcomomostradonaimagemacima.(ApontuaçãonaTabela1mostraapontuaçãodeambasasequipes;paraaEquipe2,osinaldemaisoumenoséinvertido).

Comopossorealizaraseguinteanálise:

WhatwouldthenewfinalstandingbehadTeamCnotenteredthetournament?(AllthematchesofTeamCshouldbeignored/discarded.)

(Apenasnocaso, o arquivo de exemplo está aqui )

    

Answer 1

Esta é uma solução abrangente que usa uma Tabela Dinâmica atualizada automaticamente para exibir a posição final alternativa dinamicamente quando uma equipe é excluída por meio de uma lista suspensa.

Remove a equipe excluída da Tabela Dinâmica e Table2 completamente, com as tabelas sendo ajustadas automaticamente para remover quaisquer espaços em branco.

Para Table1 , escurece as linhas que contêm a equipe excluída com formatação condicional. O melhor que pode ser para baixo com esta tabela é esconder as rodadas completamente, deixando linhas em branco.

Aqui está sua planilha mostrando as modificações adicionais:

Fórmulas:

ObservequetodasasfórmulasaseguirusamreferênciasestruturadasdoExcel2007,mastambémfuncionarãoemtodasasversõesposterioresdoExcel.

Fórmula1-inseridaemF1:F14:

=1+INT((ROW()-ROW(Table1[Round]))/2/COUNTIFS(Table1[Round],1,Table1[Team1],"<>"&$K$9,Table1[Team2],"<>"&$K$9))

Fórmula 2 - Array inserido ( Ctrl + Shift + Enter ) em G3 e copiado-colado / preenchido em G3:G14 :

=IFERROR(INDEX(Table1[[Team1]:[Team2]],SMALL(IFERROR(1/(1/N(IF(1,(ROW(Table1[Round])-ROW(Table1[[#Headers],[Round]]))*(Table2[[#This Row],[Round]]=Table1[Round])*($K$9<>Table1[Team1])*($K$9<>Table1[Team2])))),FALSE),1+MOD(INT((ROW()-ROW(Table1[Round]))/COUNTIFS(Table1[Round],1,Table1[Team1],"<>"&$K$9,Table1[Team2],"<>"&$K$9)),COUNTIFS(Table1[Round],1,Table1[Team1],"<>"&$K$9,Table1[Team2],"<>"&$K$9))),1+MOD(ROW()-ROW(Table2[[#Headers],[Round]])-1,2)),"")

A Fórmula 2 prettificada é a seguinte:

=
IFERROR(
  INDEX(
    Table1[[Team1]:[Team2]],
    SMALL(
      IFERROR(1/(1/N(IF(1,
         (ROW(Table1[Round])-ROW(Table1[[#Headers],[Round]]))
        *(Table2[[#This Row],[Round]]=Table1[Round])
        *($K$9<>Table1[Team1])
        *($K$9<>Table1[Team2])
      ))),FALSE),
      1+MOD(
        INT((ROW()-ROW(Table1[Round]))/COUNTIFS(Table1[Round],1,Table1[Team1],"<>"&$K$9,Table1[Team2],"<>"&$K$9)),
        COUNTIFS(Table1[Round],1,Table1[Team1],"<>"&$K$9,Table1[Team2],"<>"&$K$9)
      )
    ),
    1+MOD(ROW()-ROW(Table2[[#Headers],[Round]])-1,2)
  ),
  ""
)

Fórmula 3 - A fórmula na coluna H permanece inalterada, repetida aqui para completar:

=SUMPRODUCT(Table1[Score]*(Table2[[#This Row],[Round]]=Table1[Round])*(Table2[[#This Row],[Team]]=Table1[Team1])-Table1[Score]*(Table2[[#This Row],[Round]]=Table1[Round])*(Table2[[#This Row],[Team]]=Table1[Team2]))

Tabela dinâmica:

• A fonte de dados da tabela dinâmica precisa ser alterada para Table2 .
• O campo Team precisa ter valores em branco desmarcados.

Tabelas:

Table1-Apliqueaseguintefórmuladeformataçãocondicionalaointervalo$A$3:$D$8:

=OR($B3=$K$9,$C3=$K$9)

Table1 - Aplique o seguinte formato numérico à coluna Score :

0;-0;

Exclude - Defina a validação de dados de K9 para uma lista suspensa com a seguinte fonte:

=$J$3:$J$6

VBA:

Adicione o seguinte código ao módulo de folha da planilha:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Address <> "$K$9" Then Exit Sub

  With Application
    .EnableEvents = False
    Me.PivotTables(1).RefreshTable
    .EnableEvents = True
  End With

End Sub

Este código simplesmente atualiza a Tabela Dinâmica sempre que o valor na equipe excluída é alterado. Não é estritamente necessário que a solução funcione, mas você teria que fazer uma atualização manualmente após alterar a equipe (clicando com o botão direito na Tabela Dinâmica e selecionando "Atualizar", por exemplo).

    

Answer 2

Com seu layout atual, você pode mostrar as pontuações ajustadas em uma terceira tabela regular (em vez da tabela dinâmica que você tem atualmente) assim:

Eu dupliquei sua planilha com uma tabela regular em vez da tabela dinâmica e adicionei uma célula nomeada chamada "ExcludedTeam" para poder adicionar critérios à função SUMIFS na última tabela.

=SUMIFS(Table1[Score],Table1[Team1],[@Team],Table1[Team2],"<>"&ExcludedTeam)-SUMIFS(Table1[Score],Table1[Team2],[@Team],Table1[Team1],"<>"&ExcludedTeam)

Em inglês normal, isso diz "adicione a soma das Pontuações da Tabela 1 (onde a Equipe 1 corresponde a essa Equipe E a Equipe 2 não corresponde à Equipe Excluída) ao inverso da soma das Pontuações da Tabela 1 (onde a Equipa2 corresponde a esta Equipa E à Equipa1 não corresponde à Equipa Excluída.) "

É claro, você pode codificar permanentemente o nome da equipe excluída na fórmula, mas isso não é escalável ...

Se você quisesse que a pontuação do TeamC fosse 0, teria que especificar que tanto a Equipe1 quanto a Equipe2 < > ExcludedTeam nas duas metades da fórmula .

Editar: foi necessária mais funcionalidade, conforme comentários, descrevo como chegar lá abaixo.

Para remover qualquer menção da equipe excluída de todas as tabelas, você deve definir as tabelas uma acima da outra, para que as colunas ocultas na Tabela 1 não ocultem nenhuma coluna na Tabela 2 e vice-versa ou, em alternativa, as coloquem em folhas separadas.

Depois de fazer isso, crie uma macro que seja acionada quando você alterar a opção selecionada em ExcludedTeam , que define a propriedade .EntireRow.Hidden de qualquer célula em qualquer das tabelas cujo .Value = ExcludedTeam.Value to true . Tudo o que resta é mudar as especificações da sua tabela dinâmica para filtrar essa equipe, e você conseguiu.