Ваши два примера записей не соответствуют данным в связанном CSV-файле и немного неясны. Похоже, вы импортируете файл CSV в рабочую книгу и модифицируете его. (Вы забыли вставить ссылку на книгу, хотя.)
Таким образом, для моего решения я собираюсь использовать неизмененные данные файла CSV. Вам придется корректировать формулы в соответствии с вашей фактической таблицей. Кроме того, поскольку "счет" в соответствующих шести предыдущих играх на самом деле проще вычислить, чем отдельные результаты побед / поражений / ничьих, моя формула будет возвращать следующее:
Введите следующую формулу в BN2 и ctrl-enter/copy-paste/fill-down & right/auto-fill в остальные столбцы таблицы BN и BO :
=IFERROR(MOD(SUMPRODUCT(LARGE(+($C$1:$C1=C2)*(100*ROW($G$1:$G1)+3*($G$1:$G1="H")+1*($G$1:$G1="D"))+($D$1:$D1=C2)*(100*ROW($G$1:$G1)+3*($G$1:$G1="A")+1*($G$1:$G1="D")),{6,5,4,3,2,1})),100),0)
Объяснение:
Предварительно подтвержденный вариант формулы выглядит следующим образом:
=
IFERROR(
MOD(
SUMPRODUCT(
LARGE(
+($C$1:$C1=C2)*(100*ROW($G$1:$G1)+3*($G$1:$G1="H")+1*($G$1:$G1="D"))
+($D$1:$D1=C2)*(100*ROW($G$1:$G1)+3*($G$1:$G1="A")+1*($G$1:$G1="D")),
{6,5,4,3,2,1}
)
),
100
),
0
)
Для каждой предыдущей строки, содержащей целевую команду, формула создает специальное значение. (Для других строк значение равно нулю.) Две крайние справа цифры специального значения содержат итоговое значение (3 для выигрыша, 1 для ничьи и 0 для проигрыша), в то время как крайние левые цифры содержат номер строки.
Затем функция LARGE() выбирает специальные значения последних шести совпадающих строк (то есть те, которые имеют самые большие шесть номеров строк в крайних левых цифрах). Использование SUMPRODUCT() для этих шести значений приводит к единственному значению, где две правые цифры являются суммой значений результата, то есть "счетом". Функция MOD() извлекает эту оценку.
Функция IFERROR() предназначена для удаления #NUM! ошибка, которая возникает в строке 2 - строке 6 (из-за того, что LARGE() пытается получить шесть самых больших значений массива, содержащего менее шести элементов).
Обратите внимание, что если для целевой команды меньше шести предыдущих совпадений, значение элементов массива, соответствующих отсутствующим совпадениям, будет равно нулю.
Шаг за шагом по формуле в BO22 должен прояснить вышесказанное. Обратите внимание, что, поскольку TRUE и FALSE преобразуются в 1 и 0 соответственно при использовании в умножении, из соображений компактности я буду использовать эти числа в следующих оценках.
100*ROW($G$1:$G21)+3*($G$1:$G21="H")+1*($G$1:$G21="D")
→ 100*{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21}+3*({0;1;0;0;0;1;0;0;1;1;0;0;0;1;1;1;1;0;1;0;0})+1*({0;0;0;0;0;0;1;1;0;0;0;0;0;0;0;0;0;0;0;0;1})
→ {100;203;300;400;500;603;701;801;903;1003;1100;1200;1300;1403;1503;1603;1703;1800;1903;2000;2101} , специальные значения для домашние команды для всех предыдущих рядов($C$1:$C21=D22)*({100;203;300;400;500;603;701;801;903;1003;1100;1200;1300;1403;1503;1603;1703;1800;1903;2000;2101})
→ ({0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1})*({100;203;300;400;500;603;701;801;903;1003;1100;1200;1300;1403;1503;1603;1703;1800;1903;2000;2101})
→ {0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;2101} , специальные значения, если Домашняя команда соответствует целевой команде (D22) для всех предыдущих строк- Аналогично,
($D$1:$D21=D22)*(100*ROW($G$1:$G21)+3*($G$1:$G21="A")+1*($G$1:$G21="D"))
→ {0;0;303;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0} , специальные значения, если выездная команда соответствует целевой команде для всех предыдущих рядов
+{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;2101}+{0;0;303;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
→ {0;0;303;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;2101} , специальные значения, если a В матче участвовала целевая команда для всех предыдущих строк. (Обратите внимание, что, поскольку эти два массива являются "взаимоисключающими", их суммирование не вызовет никаких помех.)LARGE({0;0;303;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;2101},{6,5,4,3,2,1})
→ {0,0,0,0,303,2101} , специальные значения предыдущих шести матчей с участием целевой командыSUMPRODUCT({0,0,0,0,303,2101})
→ 2404 , где две крайние правые цифры являются "оценкой", то есть суммой значений результата, а крайние левые цифры не имеют отношения к сумме номеров строк. (Обратите внимание, что вместо SUMPRODUCT() используется SUM() поэтому внутренние подвыражения оцениваются как массивы, что исключает необходимость ввода массива в формулу.)MOD(2404,100)
→ 4 , "счет" (или форма) предыдущих шести матчей с участием целевой команды
Заметки:
- Предварительно подтвержденная формула действительно работает, если введена.
- Вам нужно будет сохранить столбец
FTR (G:G) из необработанного CSV-файла, чтобы формулы работали. (Столбец не является строго обязательным, но формулы должны быть скорректированы, чтобы работать без него. Я решил использовать столбец, так как он облегчает чтение формулы.)
- Изменить формулу так, чтобы она использовала только три предыдущих соответствующих игры, - просто заменить
{6,5,4,3,2,1} на {3,2,1} .
- Коэффициент масштабирования строки гарантированно будет работать только в том случае, если
[# relevant previous games]*[max outcome value] < [row scaling factor] . Используя значения примера, 6*3=18 < 100 , мы видим, что 100 - это минимальный коэффициент масштабирования, который будет работать. (Ну, на самом деле минимальная степень десяти масштабного коэффициента, так как 19 является абсолютным минимумом.)
Если вам действительно требуются отдельные результаты соответствующих шести предыдущих матчей, я могу добавить это к ответу. Добавлен.
Приложение № 1:
Для отдельных результатов предыдущих шести матчей команды хозяев текущей строки в массив из нескольких ячеек введите (Ctrl+Shift+Enter) следующую формулу в шесть последовательных ячеек строки 2 и скопируйте-вставьте / заполните в оставшуюся часть таблицы. столбцы (не забудьте удалить { и }):
{=
CHOOSE(1+
IFERROR(
MOD(
LARGE(
+($C$1:$C1=C2)*(100*ROW($G$1:$G1)+3*($G$1:$G1="H")+2*($G$1:$G1="A")+1*($G$1:$G1="D"))
+($D$1:$D1=C2)*(100*ROW($G$1:$G1)+3*($G$1:$G1="A")+2*($G$1:$G1="H")+1*($G$1:$G1="D")),
{6,5,4,3,2,1}
),
100
),
0
),
"-","D","L","W"
)}
Для индивидуальных результатов предыдущих шести матчей текущей команды на выезде просто измените оба =C2 на =D2 .
Чтобы вернуть результаты в виде строки в одной ячейке, просто оберните всю формулу с помощью TEXTJOIN("",TRUE,…) . (Работает только в Excel 2016. Более ранние версии Excel требуют UDF с поли-заполнением - см. Этот пост для основного.)
Объяснение:
Формула в основном такая же, как и предыдущая, только с тремя модификациями:
- Значение результата
2 добавляется для потери
- Значения результата больше не суммируются
- Вместо этого они используются в качестве первого аргумента (т. Е. В качестве индексов) функции
CHOOSE() для выбора соответствующих конечных символов.
Наконец, просто чтобы показать вам, что я не шучу о том, что счет легче вычислить, чем отдельные результаты, ниже приведена простейшая формула результатов, которая сначала не рассчитывает значения отдельных очков:
{=
IF(
ISERROR(0/LARGE(ROW($G$1:$G1)*(($C$1:$C1=C2)+($D$1:$D1=C2)),{6,5,4,3,2,1})),
"-",
IF(
"D"=INDEX($G:$G,N(IF(1,1+LARGE((ROW($G$1:$G1)-1)*(($C$1:$C1=C2)+($D$1:$D1=C2)),{6,5,4,3,2,1})))),
"D",
IF(
+("H"=INDEX($G:$G,N(IF(1,1+LARGE((ROW($G$1:$G1)-1)*(($C$1:$C1=C2)+($D$1:$D1=C2)),{6,5,4,3,2,1})))))
*(C2=INDEX($C:$C,N(IF(1,1+LARGE((ROW($G$1:$G1)-1)*(($C$1:$C1=C2)+($D$1:$D1=C2)),{6,5,4,3,2,1})))))
+("A"=INDEX($G:$G,N(IF(1,1+LARGE((ROW($G$1:$G1)-1)*(($C$1:$C1=C2)+($D$1:$D1=C2)),{6,5,4,3,2,1})))))
*(C2=INDEX($D:$D,N(IF(1,1+LARGE((ROW($G$1:$G1)-1)*(($C$1:$C1=C2)+($D$1:$D1=C2)),{6,5,4,3,2,1}))))),
"W",
"L"
)))}
Приложение № 2:
Все предыдущие формулы используют тот факт, что заголовки столбцов C , D и G не соответствуют ни одному из значений данных в этих столбцах и что значения в столбцах используются только в сравнениях. Это означает, что значение первого элемента (то есть соответствующего строки заголовка) массивов для домашней и выездной команд будет равно нулю.
Однако, если значения в столбцах фактически используются в формуле численно, как в случае получения общего количества голов, забитых каждой командой в последних шести матчах,
=
IFERROR(
MOD(
SUMPRODUCT(
LARGE(
+($C$1:$C1=C2)*(100*ROW($E$1:$E1)+$E$1:$E1)
+($D$1:$D1=C2)*(100*ROW($F$1:$F1)+$F$1:$F1),
{6,5,4,3,2,1}
)
),
100
),
0
)
тогда значением первого элемента массивов будет значение ошибки # #VALUE! функция LARGE() всегда возвращает массив {#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!} , а формула всегда возвращает ноль.
Есть несколько способов решить это.
Один из способов - ввести формулу, начиная со строки 3 вместо строки 2 и изменить ее так, чтобы диапазоны начинались со строки 2 вместо строки 1 (например, $C$2:$C2=C3 вместо $C$1:$C2=C3). Конечно, значения для строки 2 всегда будут нулями и могут быть введены вручную.
Другой способ - обернуть сумму массивов в функцию IFERROR() чтобы преобразовать ошибку в первом элементе в ноль перед передачей ее в функцию LARGE() :
{=
IFERROR(
MOD(
SUMPRODUCT(
LARGE(
IFERROR(
+($C$1:$C1=C2)*(100*ROW($E$1:$E1)+$E$1:$E1)
+($D$1:$D1=C2)*(100*ROW($F$1:$F1)+$F$1:$F1),
0
),
{6,5,4,3,2,1}
)
),
100
),
0
)}
Заметки:
- Эта формула должна быть введена в массив. Обычно выражения внутри функции
SUMPRODUCT() оцениваются как массивы, но выражения внутри вложенного IFERROR() являются одним из исключений.
- Так как формула массива введена,
SUM() может быть использован вместо SUMPRODUCT() (Помните, что он изначально использовался только для принудительного вычисления массива без необходимости ввода формулы в массив).
