Формула Excel для получения предпоследнего значения в строке

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

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

мне нужна формула, которая вернет значение 9.

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

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

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

26
задан nhinkle
22.12.2022 8:48 Количество просмотров материала 3224
Распечатать страницу

4 ответа

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

=INDEX(1:1,LARGE((1:1<>"")*(COLUMN(1:1)),2))
3
отвечен Doug Glancy 2022-12-23 16:36

при условии, что пустых ячеек нет:

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

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

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

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

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

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

бонус: получите 5-ю последнюю запись. Легко:

=индекс(1:1,1,отсчет(1:1)-3) или ИНДЕКС(1:1,1,COUNTA (1:1)-4)

4
отвечен mtone 2022-12-23 18:53

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

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

это формула массива, так что вы должны нажать CTRL + SHIFT + ENTER, чтобы ввести его

1
отвечен Linker3000 2022-12-23 21:10

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

=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
отвечен Hello71 2022-12-23 23:27

Постоянная ссылка на данную страницу: [ Скопировать ссылку | Сгенерировать QR-код ]

Ваш ответ

Опубликуйте как Гость или авторизуйтесь

Имя
Мы используем cookie. Это позволяет нам анализировать взаимодействие посетителей с сайтом и делать его лучше. Продолжая пользоваться сайтом, вы соглашаетесь с использованием файлов cookie. Политика конфиденциальности
Согласен
Вверх