☰

Поиск последней непустой ячейки в строке с определенным набором значений

tag-icon • tag-icon worksheet-function spreadsheet iwork-numbers
Поиск последней непустой ячейки в строке с определенным набором значений

У меня есть электронная таблица данных студентов, где каждая строка — это один студент. Для любого класса есть несколько столбцов 1-N, где N — наибольшее количество раз, когда любой из студентов повторял курс. Итак, некоторые примеры данных, где курс повторялся максимум 4 раза, с тем, что я хочу вычислить в столбце E:

+---+-------+-------+-------+-------+-----------+
|   |   A   |   B   |   C   |   D   |     E     |
+---+-------+-------+-------+-------+-----------+
| 1 | 225-1 | 225-2 | 225-3 | 225-4 | 225-final |
| 2 | F     | EP    | C     |       | C         |
| 3 | A     |       |       |       | A         |
| 4 | W     | D     | W     | F     | F         |
| 5 | EP    | C     |       |       | C         |
+---+-------+-------+-------+-------+-----------+

Я уже нашел lookupфункцию, которая, кажется, приближает меня к тому, что я хочу. Так, например, LOOKUP("X",A2:D2)даст мне самый высокий (т. е. последний в алфавите) непустой столбец для данного студента. Учитывая данные выше, я бы получил вывод вроде следующего:

+---+-------+-------+-------+-------+-----------+
|   |   A   |   B   |   C   |   D   |     E     |
+---+-------+-------+-------+-------+-----------+
| 1 | 225-1 | 225-2 | 225-3 | 225-4 | 225-final |
| 2 | F     | EP    | C     |       | F         |
| 3 | A     |       |       |       | A         |
| 4 | W     | D     | W     | F     | W         |
| 5 | EP    | C     |       |       | EP        |
+---+-------+-------+-------+-------+-----------+

Проблемы с этим заключаются в следующем:

  1. Вместо самого высокого непустого значения столбца, я на самом деле хочу самое низкое. Таким образом, студенты, которые получают оценку D, F, W, EP или EF, могут заменить класс и (надеюсь) справятся лучше в будущей попытке. Я хочу зафиксировать самую последнюю попытку, которая не является W, EP или EF (см. ниже для них). Обратите внимание, что это не всегда лучшая оценка — у нас были студенты, которые получали D, заменяли оценку, а затем получали F. Я бы хотел зафиксировать F.

  2. Одна из проблем, которая усложняет это, заключается в том, что студенты могут получить оценку EP или EF, которая не должна быть включена, и в конечном итоге заменить более высокие буквенные оценки с учетом моего текущего подхода, и заменила бы F, если бы я мог отменить lookup- они указывают, что у студента был экстренный отказ, либо сдав, либо не сдав курс. Я думаю, я мог бы заменить все значения EP и EF на WP или WF, чтобы упростить ситуацию. Если мои действия повлияют на ваш ответ, пожалуйста, учтите это!

решение1

Судя по вашей первой матрице, вас интересует последняя непустая ячейка в этой строке. При условии, что они не должны быть равны ни EP, EFни W. Это верное предположение? Если да, используйте следующее:

введите описание изображения здесь

Формула в E2:

=INDEX(A2:D2,,AGGREGATE(14,3,(A2:D2<>"")*(A2:D2<>"W")*(A2:D2<>"EP")*(A2:D2<>"EF")*(COLUMN(A2:D2)),1))

Тянуть вниз....

Если это не то, что вам нужно, можете ли вы сказать мне, какие ограничения? В настоящее время вы иногда объясняете, что последнее значение — это то, что вам нужно, и в конце указываете ограничения.

РЕДАКТИРОВАТЬ:

Как это работает?

AGGREGATEполучит наибольшее число (по 14 параметру), возвращенное из уравнения (A2:D2<>"")*(A2:D2<>"W")*(A2:D2<>"EP")*(A2:D2<>"EF")*(COLUMN(A2:D2), которое вернет массив единиц и нулей на основе всех указанных правил, умноженных на соответствующий номер столбца. Наибольший результат номера столбца будет квалификационным значением.

Затем это наибольшее число будет использовано в качестве параметра столбца в INDEXфункции.

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