Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (60) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (35) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

Как получить последннюю заполненную ячейку формулой?

Очень часто при работе с большими таблицами возникает вопрос: как узнать последнюю заполненную ячейку в столбце? Обычно это необходимо для того, чтобы суммировать или вычислять среднее только в пределах заданной таблицы, без учета пустых строк, т.к. в случае с вычислением среднего пустые строки могут повлиять на расчеты. По сути способов не так много. Я в этой статье покажу два варианта: в первом формула проще для понимания, но менее универсальна в использовании - она требует точно знать данные какого типа хранятся в столбце: числа или текст, т.к. ориентируется исключительно на тип данных. Вторая формула более универсальна, но может дольше работать.
Формула ниже по сути будет отбирать только числа и вернет номер самой нижней строки, в которой расположено любое число, даже если это нуль:
=ПОИСКПОЗ(3E+307;A1:A100)
А эта формула вернет номер строки с последней ячейкой, в которой записан любой текст
=ПОИСКПОЗ("яяя";A1:A100)

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

  1. =МАКС(ПОИСКПОЗ({"яяя";3E+307};A1:A100))
  2. =ПРОСМОТР(2;1/(A1:A100<>"");СТРОКА(A1:A100)

Первая формула вводится как формула массива(ввод формулы в ячейку завершается нажатием не просто Enter, а сочетанием клавиш Ctrl+Shift+Enter). Но есть и еще один недостаток: если в столбце нет какого-либо типа данных - формула вернет #Н/Д. Обхитрить можно, если захватить в расчет заголовок, в котором будет текст или число, в зависимости от того, какие данные предположительно могут отсутствовать.

Вторая формула вводится в ячейку обычным методом и вроде как не имеет никаких подводных камней. Кроме одного: не стоит указывать в качестве диапазона ВЕСЬ СТОЛБЕЦ с данными - формула может очень долго пересчитываться. Особенно это сказывается в файлах версии 2007 Excel, где строк больше миллиона. Предыдущие формулы лишены этого недостатка. Хотя я в любом случае советовал бы указывать явно диапазон "с запасом".

Как видно, недостатки есть в любой из приведенных формул, так что выбор в любом случае за Вами и зависеть он будет напрямую от поставленной задачи.


Вот один из примеров, как можно применить определение последней ячейки в реальных формулах. Например, вычисление среднего значения:
=СРЗНАЧ(A2:ИНДЕКС(A1:A100;ПОИСКПОЗ(9E+307;A1:A100)))
Числовые данные начинаются с ячейки A2. В A1 заголовок, а где заканчиваются данные неизвестно - они постоянно изменяются: удаляются, дополняются.
В данном случае мы первой ячейкой указываем A2 - начало числовых данных. А вот далее уже идет вычисление последней ячейки:
ПОИСКПОЗ(9E+307;A1:A100)
В данном случае можно применить поиск последней ячейки именно с числом, т.к. СРЗНАЧ(AVERAGE) в любом случае игнорирует текст и лишние ячейки нам ни к чему. ПОИСКПОЗ(MATCH) возвращает номер последней ячейки в диапазоне A1:A100. Но чтобы получить именно ссылку на эту ячейку, а не просто её строку мы используем ИНДЕКС(INDEX):
ИНДЕКС(A1:A100;ПОИСКПОЗ(9E+307;A1:A100))
Т.е. по шагам формулу можно представить так:
=СРЗНАЧ(A2:ИНДЕКС(A1:A100;ПОИСКПОЗ(9E+307;A1:A100))) =>
=СРЗНАЧ(A2:ИНДЕКС(A1:A100;9)) =>
=СРЗНАЧ(A2:A9) =>
4,5


В приложенном к теме примере записаны все приведенные формулы, а так же пара примеров того, как эти формулы можно использовать в других формулах для определения конца диапазона.

Скачать пример

  Tips_General_Last_Cell_Formula.xls (38,5 KiB, 2 055 скачиваний)

Также см.:
Как определить последнюю ячейку на листе через VBA?
Что такое формула массива


Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Поиск по меткам

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Печать Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: есть 1 комментарий
  1. VladKomp:

    Отличная формула! Работает даже с ячейками, содержащими #ДЕЛ/0
    Воспользовался для расчета рейтинга.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2017 Excel для всех  Войти