Получить значение из самой последней предыдущей записи, где какой-либо текст появляется в любом из двух столбцов

Question

Я пытаюсь более полно автоматизировать вычисление значений и сводных данных в электронной таблице, которую я храню о результатах матчей в лиге пула.

У меня есть таблица с большим количеством информации о каждом матче, с соответствующими полями: Дата матча, Победитель, Гандикап на старте победителя, Гандикап на исходе победителя, Проигравший, Гандикап при старте проигравшего, Гандикап при проигрыше, Время начала матча.

Гандикапы корректируются в конце каждого матча и перед следующим. Трудно найти самую последнюю прошлую запись для игрока (мог быть Победителем или Проигравшим) и скопировать его конечный гандикап из этой записи в Начальный гандикап (победитель или проигравший) для того, который я сейчас ввожу.

Я хотел бы получить формулу, в которой можно найти самую последнюю запись (наибольшая дата и время начала, если он сыграл дважды за один день), где он был победителем или проигравшим, а затем получить конечный гандикап (от соответствующего победителя или проигравшего). ).

По предложению Тейлина, вот ссылка Dropbox на файл. Соответствующая вкладка - Результаты матча: https://www.dropbox.com/s/1j9c6zsxjd3q4dt/Sample%20for%20Excel%20Question%20on%20Superuser.xlsx?dl=0

Я добавил пустой столбец L, чтобы проверить вещи, сравнив результаты с тем, что в K, чтобы увидеть, работают ли они, поэтому он там есть. Забыл удалить его, когда я положил его в Dropbox.

Answer 1

Постановка задачи

Рабочий лист имеет имена в столбцах E и X Для каждой строки n En ≠ Xn .  В столбце M есть номера, соответствующие именам в столбце E , а номера в столбце AG соответствуют именам в столбце X Для любой строки после первой (скажем, строки 42) мы хотим получить значения для K42 и AF42 из предыдущих строк, если это возможно.

• Если E42 - «Джон», найдите самую последнюю строку, содержащую «Джон» (в столбце E или X).  Назовите этот ряд n.  Если En = «Джон», установите K42 равным Mn .  Если Xn = «Джон», установите K42 равным AGn .

• Если X42 - «Скотт», найдите самую последнюю строку, содержащую «Скотт» (в столбце E или X).  Назовите этот ряд n.  Если En = «Скотт», установите AF42 равным Mn .  Если Xn = «Скотт», установите AF42 равным AGn .

                 

Решение

В надежде сохранить здравый смысл, давайте использовать вспомогательные столбцы; скажем, AR и AS . Предположим, что (как в файле примера) данные начинаются со строки 2.  Войти

=MAX(($E$2:$E2=$E3)*(100*ROW($E$2:$E2)+COLUMN($M:$M)), ($X$2:$X2=$E3)*(100*ROW($X$2:$X2)+COLUMN($AG:$AG)))

в AR3 (пропуская AR2).  Конец Ctrl+Shift+Enter, чтобы сделать его формулой массива.  Аналогично, установите AS3 в

=MAX(($E$2:$E2=$X3)*(100*ROW($E$2:$E2)+COLUMN($M:$M)), ($X$2:$X2=$X3)*(100*ROW($X$2:$X2)+COLUMN($AG:$AG)))

как формула массива.  (Это то же самое, что и AR3 за исключением того, что два вхождения $E3 были заменены на $X3 .)

Установите K3 в

=IF($AR3=0, "?", INDEX($A$1:$BG$999, INT($AR3/100), MOD($AR3,100)))

и AF3 для

=IF($AS3=0, "?", INDEX($A$1:$BG$999, INT($AS3/100), MOD($AS3,100)))

(не как формулы массива).  Это то же самое, за исключением того, что три вхождения $AR3 были заменены на $AS3 .

И, конечно же, перетащите / заполните.

Вспомогательные столбцы находят самые последние предыдущие вхождения имен - ARn находит самое последнее предыдущее вхождение En , а ASn находит самое последнее предыдущее вхождение Xn - в основном путем нахождения максимума во всех предыдущих строках

(previous_value=this_value) * ROW())

т.е. самый высокий номер строки, где имя совпадает.  Затем он кодирует место, где имя было найдено как

100*ROW() + COLUMN(data_we_want_to_copy)

Обе формулы смотрят в столбцы E и X и возвращают закодированные координаты соответствующих столбцов M или AG .  Затем формулы K и AF просто декодируют адрес ячейки и получают значение.

               

Таким образом, AR6 равен 213, потому что «Джон» (E6) был недавно замечен в строке 2, и, поскольку он был замечен в E2 (а не в X2), мы хотим скопировать значение из столбца 13 (столбец M).