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