Пять последних значений в 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)

Комментарии
Только для авторизованных пользователей

Аноним, 27 ноября 2018 г.
А если я вывожу из диапазона значения по текстовому критерию в новый диапазон, как мне потом вывести последние 5 значений из нового диапазона, при условии, что диапазоны динамические?
Михаил, 27 ноября 2018 г.
Вместо ссылки А5:А19 укажите имя динамического диапазона, полученного с помощью текстового критерия.
Аноним, 12 апреля 2019 г.
Не работает, если есть пустые ячейки
Михаил, 28 апреля 2019 г.
Теперь работает! Дописал статью, обновил файл примера. Спасибо за хороший вопрос! Вот бы все комментарии были такими конструктивными!
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved