2

Представьте, что у меня есть ряд данных в Excel, например:

     [A]     [B]   [C]   [D]   [E]   [F]   [G]   [H]
[1]  Data     5     8    12    32    15     9    89

Я хочу формулу, которая будет возвращать значение 9 .

Затем, если я добавлю новое значение в I1 , оно должно обновиться, чтобы вернуть значение 89 , которое будет новым предпоследним значением.

В идеале это должно работать на произвольных диапазонах, а не только на целых строках.

Необязательные бонусные баллы, если метод может вернуть произвольное смещение от конца строки (скажем, для возврата 5-го числа из последнего элемента).

|источник

4 ответа4

4

Предполагая, что нет пустых ячеек:

= ИНДЕКС (1:1,1, COUNT (1:1))

= ИНДЕКС (1:1,1, СЧЕТЗ (1:1)-1)

INDEX возвращает определенное значение в диапазоне, указав:

  • массив: произвольный диапазон или вся строка (1: 1)
  • row_num: номер строки в диапазоне (1)
  • column_num: номер столбца в диапазоне. Это сложная часть.

В моем примере я использовал COUNT(), чтобы подсчитать, сколько числовых записей у вас есть в строке, а это 7. COUNTA() также включает в себя вашу первую нечисловую строку "Данные" и возвращает 8, так что вы должны вычесть 1.

Обратите внимание, что это не будет работать, если какая-либо ячейка останется пустой Вместо того, чтобы оставлять пустую ячейку, вы можете поставить тире (-) для обозначения пустой записи и использовать COUNTA ().

БОНУС: Получите 5-ю последнюю запись. Легко:

= ИНДЕКС (1:1,1, СЧЕТ (1:1) -3) или ИНДЕКС (1:1,1, СЧЕТ (1:1) -4)

|источник
3

Это работает как с текстом, так и с числами, а также не заботится о наличии пустых ячеек, т. Е. Возвращает 2-ю до последней непустой ячейки. Он должен быть введен в массив, что означает, что вы нажимаете ctrl-shft-enter после ввода или вставки. Настройте последний аргумент, чтобы изменить смещение:

=INDEX(1:1,LARGE((1:1<>"")*(COLUMN(1:1)),2))
|источник
1

Вы должны быть в состоянии сделать что-то вроде этого:

=OFFSET(A1, 0, COUNTA(A1:I1) - 2)

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

=OFFSET(A1, 0, COLUMNS(A1:I1) - COUNTBLANK(A1:I1) - 2)

Источник для замены COUNTA: http://excel.tips.net/Pages/T002996_Counting_NonBlank_Cells.html

То же предупреждение, что и выше.

|источник
1

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

=INDEX(A1:Z1,COUNTA(A1:Z1)-0)

Это формула массива, поэтому вы должны нажать CTRL + SHIFT + ВВОД, чтобы ввести ее

|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .