Pregunta Excel: ¿Qué pasaría si un equipo en particular no hubiera ingresado en un torneo de liga?


enter image description here

Imagine un torneo donde cuatro equipos ingresaron como se muestra en la imagen de arriba. (El puntaje en la Tabla 1 muestra el puntaje para ambos equipos, para el Equipo 2 el signo más / menos está invertido).

¿Cómo puedo realizar el siguiente análisis?

¿Cuál sería la nueva posición final si TeamC no ingresara al torneo? (Todas las coincidencias de TeamC deben ignorarse / descartarse).

(Por si acaso, el archivo de ejemplo está aquí)


0


origen


Sí Excel puede hacerlo, Excel puede hacer muchas cosas, para una respuesta exacta, aunque necesitaría una pregunta exacta para un problema específico, pedir una forma de abordar un tema solo puede dar como resultado respuestas basadas en opiniones - PeterH
Siento que esa edición aclara la pregunta lo suficiente como para calificar: necesitamos una manera de decirle a la tabla Team / TotalScore de la izquierda solo para sumar los puntajes de la Tabla2 si el número de la ronda y el nombre del equipo no aparecen en una fila desde Table1 con TeamC. O déle a la hoja una forma de descontar los datos en la Tabla 1 que aparece en una fila que contiene la cadena "TeamC" ... - Isaac Reefman
Importante para ayudarme a formular una respuesta: ¿qué fórmulas está utilizando para mover datos de una tabla a la siguiente? ¿Se ingresan manualmente todos los datos en las tablas 1 y 2, o los datos en la Tabla 2 se generan automáticamente en base a los datos en la Tabla 1? - Isaac Reefman


Respuestas:


Aquí hay una solución integral que utiliza una Tabla dinámica actualizada automáticamente para mostrar la posición final alternativa dinámicamente cuando un equipo se excluye a través de una lista desplegable.

Elimina al equipo excluido de la tabla dinámica y Table2 completamente, con las tablas autoajustadas para eliminar cualquier espacio en blanco.

por Table1, atenúa las filas que contienen el equipo excluido con el formato condicional. Lo mejor que puede hacerse con esta tabla es ocultar las rondas por completo, dejando filas en blanco.

Aquí está su hoja de trabajo que muestra las modificaciones adicionales:

Worksheet Screenshot


Fórmulas:

Tenga en cuenta que todas las fórmulas siguientes utilizan referencias estructuradas de Excel 2007, pero también funcionarán en todas las versiones posteriores de Excel.


Fórmula 1: ingresado en F1:F14:

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


Fórmula 2 - Array ingresado (Ctrl+Cambio+Entrar) en G3 y copiar-pegar / rellenar 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)),"")

La Fórmula 2 embellecida es la siguiente:

=
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 - La fórmula en la columna H permanece sin cambios, se repite aquí 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]))


Tabla dinámica:

  • La fuente de datos de la tabla dinámica debe cambiarse a Table2.
  • los Team campo necesita tener valores en blanco deseleccionados.

Pivot Table Setup Screenshot


Mesas:

Table1 - Aplicar la siguiente fórmula de formato condicional al rango $A$3:$D$8:

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

Table1 - Aplicar el siguiente formato numérico a la columna Score:

0;-0;

Exclude - Establecer la validación de datos de K9 a una lista desplegable con la siguiente fuente:

=$J$3:$J$6


VBA:

Agregue el siguiente código al módulo de hoja de la hoja de trabajo:

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 simplemente actualiza la tabla dinámica cada vez que cambia el valor en el equipo excluido. No es estrictamente necesario que la solución funcione, pero debería actualizar manualmente después de cambiar el equipo (haciendo clic con el botón derecho en la tabla dinámica y seleccionando "Actualizar", por ejemplo).


2





Con su diseño actual, puede mostrar los puntajes ajustados en una tercera tabla regular (en lugar de la tabla dinámica que tiene actualmente) como esta:

Dupliqué tu hoja con una tabla normal en lugar de la tabla dinámica, y agregué una celda con nombre llamada "Equipo Excluido" para poder agregar un criterio a una SUMIFS función para ir en la última tabla.

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

En inglés normal, esto dice "agregue la suma de los puntajes de la Tabla 1 (donde Team1 coincide con este equipo Y Team2 no coincide con el equipo excluido) con el inverso de la suma de los puntajes de la Tabla 1 (donde Team2 coincide con este equipo Y el equipo 1 no coincide con el equipo excluido) ".

Por supuesto, siempre se puede codificar el nombre del equipo excluido en la fórmula, pero eso no se escala en absoluto ...

enter image description here

Si quisieras que el puntaje de TeamC fuera 0, deberías especificar que tanto Team1 como Team2 <> ExcludedTeam en ambas mitades de la fórmula.


Editar: se necesitaba más funcionalidad según los comentarios, describo cómo llegar a continuación.

Para eliminar cualquier mención del equipo excluido de todas las tablas, debe establecer las tablas una encima de la otra, de modo que ocultar las columnas en la Tabla 1 no ocultará ninguna columna en Table2 y viceversa, o alternativamente ponerlas en hojas separadas .

Una vez que hayas hecho eso, crea una macro que se active cuando cambies la opción seleccionada en ExcludedTeam que establece el .EntireRow.Hidden propiedad de cualquier celda en cualquiera de las tablas cuya .Value = ExcludedTeam.Value a true. Todo lo que queda es cambiar las especificaciones de tu tabla dinámica para filtrar ese equipo, y lo tienes.


-1



En caso de que no sepa cómo nombrar una celda, el lugar en la esquina superior izquierda de la imagen adjunta muestra el nombre de la celda. Actualmente se selecciona K3. Simplemente haga clic en la celda que desea nombrar (B10 en la imagen) luego haga clic en este espacio y escriba lo que quiere que sea el nombre de la celda. Sin embargo, cabe destacar: volver a nombrarlo o eliminar el nombre puede ser un problema. - Isaac Reefman
Gracias. (1) No pude hacer que tu solución funcione. (2) Para eliminar o cambiar el nombre de un golpe de celda CTRL+F3y Name Manager aparece. (3) No obstante, estoy buscando una solución en la que cuando un equipo esté excluido, no se muestre en la clasificación. - blackened
"No se muestran en la clasificación" - ¿Qué quieres decir con esto? ¿Desea que su puntaje total sea 0 o quiere eliminar cualquier mención de ellos de todas las tablas? - Isaac Reefman
"No pude hacer que tu solución funcione", ¿qué pasa si no funciona? ¿No aparece como se muestra en la imagen, o simplemente dice que los datos en la imagen no son el resultado que está buscando? - Isaac Reefman
Correcto: (1) Quiero poder eliminar la mención de ellos en la tabla de posiciones finales. (2) Cuando aplico su fórmula, obtengo figuras diferentes a las que se muestran en la imagen. - blackened