Пять последних значений в MS EXCEL

Найдем, формулу возвращающую ссылку на диапазон, содержащий 5 последних значений. Если столбец со значениями постоянно заполняется, то эта задача перестает быть тривиальной.

Создадим в столбце А список текстовых значений (см. картинку ниже). Сначала рассмотрим список без пустых ячеек, затем - с пустыми. Выведем в отдельный диапазон 5 последних значений.

Список без пустых ячеек

Если столбец не содержит пустых ячеек, то для нахождения ссылки на диапазон, содержащий 5 последних значений можно использовать формулу:
=СМЕЩ($A$5;СЧЁТЗ(Список)-5;0;5)

Динамический диапазон Список образован формулой =СМЕЩ(Лист1!$A$5;;;СЧЁТЗ(Лист1!$A$5:$A$19))

Теперь выведем 5 последних значений в отдельном диапазоне. Для этого:

  • выделим 5 ячеек, например, в столбце С;
  • в Строке формул введем =СМЕЩ($A$5;СЧЁТЗ(Список)-5;0;5)
  • нажмем CTRL + SHIFT + ENTER

Если в столбец А добавить еще значения, то функция СМЕЩ() автоматический вернет ссылку на диапазон, содержащий 5 последних значений, с учетом только что добавленного значения. Как видно из рисунка сверху, последним значением является Товар10, а в соседнем списке отображаются последние 5 значений: наименования от Товар6 до Товар10.

Добавим в исходный список новое наименование Товар11 (см. Файл примера).

Теперь в соседнем списке отображаются другие последние 5 значений: наименования от Товар7 до Товар11.

Последние 5 значений исходного списка выделены Условным форматированием (см. статью Выделение последних 5 заполненных ячеек)

СОВЕТ:
В случае числового диапазона, практическим применением примера может служить статья Среднее 5 последних значений списка.

Решение этой задачи также используется в статье Динамические диаграммы. Часть4: Выборка данных из определенного диапазона.

Список с пустыми ячейками

Пустой ячейкой будем называть ячейку, в которой ничего не введено. В некоторых случаях ячейка выглядит пустой, но в ней содержится значение или формула. Например, ячейка может содержать пробел или формулу ="". Здесь рассмотрим именно пустые (незаполненные) ячейки.

Сначала определим длину списка с пустыми ячейками (см. файл примера в конце статьи, лист "с пустыми ячейками"): 

=ПОИСКПОЗ(ПОВТОР("я";10);'с пустыми ячейками'!$A$7:$A$21;1)

Затем сформируем динамический диапазон СписокСпропусками:

=СМЕЩ('с пустыми ячейками'!$A$7;;;ДлинаСпискаСпропусками)

Список без пустых ячеек можно сформировать в столбце С формулой массива:

=ЕСЛИОШИБКА(ДВССЫЛ("A"&НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО(СписокСпропусками);"";СТРОКА(СписокСпропусками));СТРОКА()-СТРОКА($C$6)));"")

Этот список можно сделать динамическим диапазоном, который назовем СписокБезПропусков:

=СМЕЩ('с пустыми ячейками'!$C$7;;;СЧЁТЕСЛИ('с пустыми ячейками'!$C$7:$C$21;"*?"))

И, наконец, с помощью формулы массива, возвращающей несколько значений выведем 5 последних значений:

=СМЕЩ($C$7;СЧЁТЗ(СписокБезПропусков)-5;0;5)

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (1 оценка)