☰

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

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

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

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

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

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

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

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

решение1

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

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

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

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

                   

Решение

В надежде сохранить здравомыслие, давайте используем вспомогательные столбцы; скажем, 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.

И, конечно же, перетаскиваем/заполняем вниз.

Вспомогательные столбцы находят самые последние предыдущие появления имен — находят самые последние предыдущие появления и находят самые последние предыдущие появления — в основном путем нахождения максимального значения среди предыдущих строкARnEnASnXn

(предыдущее_значение"="это_значение) * РЯД())

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

100*СТРОКА() + СТОЛБЕЦ(данные_мы_хотим_копировать)

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

               

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

Связанный контент