Представьте себе турнир, в котором приняли участие четыре команды, как показано на рисунке выше. (Счет в Таблице 1 показывает счет для обеих команд; для Команды 2 знак плюс / минус инвертируется.)
Как я могу выполнить следующий анализ:
Каким был бы новый финальный зачет, если бы TeamC не участвовал в турнире? (Все матчи TeamC следует игнорировать / отбрасывать.)
(На всякий случай файл примера здесь)
Вот комплексное решение, которое использует автоматически обновляемую сводную таблицу для динамического отображения альтернативного окончательного положения, когда команда исключается из выпадающего списка.
Она удаляет без команды из сводной таблицы и Table2 полностью, с таблицами автоматической регулировки , чтобы удалить любые пробелы.
Для Table1 он затемняет строки, содержащие исключенную команду с условным форматированием. Лучшее, что можно сделать с этой таблицей, - полностью скрыть раунды, оставляя пустые строки.
Вот ваш рабочий лист с добавленными изменениями:
Формулы:
Обратите внимание, что все следующие формулы используют структурированные ссылки Excel 2007, но будут работать и во всех более поздних версиях Excel.
Формула 1 - Введена в F1:F14:
=1+INT((ROW()-ROW(Table1[Round]))/2/COUNTIFS(Table1[Round],1,Table1[Team1],"<>"&$K$9,Table1[Team2],"<>"&$K$9))
Формула 2 - Массив введен (Ctrl+Shift+Enter) в G3 и скопирован / вставлен в 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)),"")
Предварительно подтвержденная формула 2 выглядит следующим образом:
=
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)
),
""
)
Формула 3 - Формула в столбце H остается неизменной, повторяется здесь для полноты:
=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]))
Сводная таблица:
Table2 .Team необходимо отменить выбор пустых значений.
Таблицы:
Table1 Примените следующую формулу условного форматирования к диапазону $A$3:$D$8:
=OR($B3=$K$9,$C3=$K$9)
Table1 - Применить следующий числовой формат для столбца Score:
0;-0;
Exclude - установите проверку данных K9 в раскрывающемся списке со следующим источником:
=$J$3:$J$6
VBA:
Добавьте следующий код в модуль Sheet на листе:
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
Этот код просто обновляет сводную таблицу всякий раз, когда изменяется значение исключенной команды. Это не является обязательным требованием, чтобы решение работало, но тогда вам придется выполнить обновление вручную после смены команды (например, щелкнув правой кнопкой мыши сводную таблицу и выбрав "Обновить").
С вашим текущим макетом вы можете отобразить скорректированные оценки в обычной третьей таблице (а не в сводной таблице, которая у вас есть в настоящее время), например:
Я дублировал ваш лист с обычной таблицей вместо сводной таблицы и добавил именованную ячейку с именем "ExcludedTeam", чтобы я мог добавить критерий к функции SUMIFS чтобы перейти в последнюю таблицу.
=SUMIFS(Table1[Score],Table1[Team1],[@Team],Table1[Team2],"<>"&ExcludedTeam)-SUMIFS(Table1[Score],Table1[Team2],[@Team],Table1[Team1],"<>"&ExcludedTeam)
В обычном английском языке это говорит «добавить сумму баллов из таблицы 1 (где команда 1 соответствует этой команде, а команда 2 не соответствует исключенной команде) к обратной сумме баллов из таблицы 1 (где команда 2 соответствует этой команде и команде 1» не соответствует исключенной команде.) "
Конечно, вы всегда можете жестко закодировать название исключенной команды в формулу, но это совсем не масштабируется ...
Если вы хотите, чтобы счет TeamC читался как 0, вам нужно будет указать, что Team1 и Team2 <> ExcludedTeam в обеих половинах формулы.
Изменить: больше комментариев было необходимо в соответствии с комментариями, я опишу, как туда добраться ниже.
Чтобы удалить упоминание об исключенной команде из всех таблиц, вам нужно установить таблицы одну над другой, чтобы скрытые столбцы в таблице 1 не скрывали столбцы в таблице 2 и наоборот, или, наоборот, разместите их на отдельных листах. ,
Как только вы это сделаете, создайте макрос, который будет срабатывать при изменении параметра, выбранного в ExcludedTeam который устанавливает .EntireRow.Hidden свойство любой ячейки в любой таблице, чья .Value = ExcludedTeam.Value к true . Осталось только изменить спецификации вашей сводной таблицы, чтобы отфильтровать эту команду, и вы получили ее.