Часто нужно выбрать диапазон ячеек динамически, так, чтобы формула сама нашла в столбце последнюю заполненную данными ячейку. Пример такой формулы приведён на скриншоте ниже.

Если конкретно с суммой так делать было необязательно - в конце концов, формула =СУММ(A:A) сама исключит из суммирования всё, кроме чисел, то многие другие функции, например, линейная регрессия, так сделать не смогут, а захватив пустые ячейки, начнут врать. Остаётся либо каждый раз перепрограммировать формулу, зависящую от диапазона заполненных данными ячеек, либо сделать формулу "умной" так, чтобы она умела сама останавливться на нужной ячейке.

Вторая формула позволяет проверить, заполнен ли столбец A без пропусков. Если это так, выводится количество чисел в столбце, иначе - предупреждение.

Везде предполагается, что данные вводятся, начиная с ячейки A2, а в ячейке A1 содержится заголовок столбца.

Если нужно подсчитать не количество чисел, а количество непустых ячеек вообще, вместо функции СЧЁТ применяется СЧЁТЗ. Например, формула вида

=СУММ(ЕСЛИ($B$2:ДВССЫЛ(АДРЕС(СЧЁТЗ(B:B)+1;2))="";0;1))

подсчитывает количество ячеек, заполненных подряд в столбце B при условии, что ячейка B1 отведена под заголовок столбца (формула завершается нажатием Ctrl+Shift+Enter).

 Выбираем только заполненную часть столбца - скачать пример в Excel XP/2003 (16 Кб)

Ещё один пример применения подхода - вот этот учебный сортировщик массива. Пользователь заполняет числовыми данными столбец B, а в столбце C автоматически формируется отсортированный массив. Максимальная размерность массива = 100 элементов, контроль правильности заполнения встроен в формулу, расположенную в ячейках C2:C101, вот её вид для ячейки C3:

=ЕСЛИ(ИЛИ(И(B2="";B3<>"");И(B3<>"";ЕЧИСЛО(B3)=ЛОЖЬ));"Заполните столбец B числами и подряд!";ЕСЛИ(B3="";"";НАИМЕНЬШИЙ($B$2:$B$101;A3)))

Далее формула растягивается из ячейки C3, а в ячейке C2 достаточно следующего:

=ЕСЛИ(B2="";"";НАИМЕНЬШИЙ($B$2:$B$101;A2))

Сама сортировка (по возрастанию) делается функцией НАИМЕНЬШИЙ, для сортировки по убыванию замените её на НАИБОЛЬШИЙ.

 Выбираем только заполненную часть столбца - скачать пример в Excel XP/2003 (20 Кб)

гостевая; E-mail