Excel: заполняем без пропусков

И ещё об Excel. Нередко нужно, чтобы пользователь ввёл заранее неизвестное количество чисел, например, в незащищённый столбец A, а формула, находящаяся, например, в защищённом столбце B, могла обработать эти числа (независимо от того, сколько их там), и, самое главное, проверить, правильно ли заполнен лист, нет ли в нём не-чисел или пропущенных ячеек.

В качестве примера приведём пару таких формул.

Во первых, найдём сумму числовых значений от ячейки A2 до последней заполненной без пропусков ячейки в столбце A:

=СУММ(A2:ДВССЫЛ(АДРЕС(СЧЁТ(A:A)+1;1)))

...с A2, так как A1 нужна для заголовка столбца.

Во-вторых, проверим, что числа в столбце A, начиная с ячейки A2, заполнены подряд и без пропусков. Если это так, найдём их количество функцией СЧЁТ(A:A), иначе выведем сообщение об ошибке:

=ЕСЛИ(СЧЁТ(A2:ДВССЫЛ(АДРЕС(СЧЁТ(A:A)+1;1)))=СЧЁТ(A:A);
"Всего чисел: "&СЧЁТ(A:A);"Заполните ячейки без пропусков!")

Вместо последнего вызова СЧЁТ может быть вызов любой другой функции, способной обрабатывать сразу столбец, скажем, для суммирования формула изменится так:

=ЕСЛИ(СЧЁТ(A2:ДВССЫЛ(АДРЕС(СЧЁТ(A:A)+1;1)))=СЧЁТ(A:A);
"Сумма чисел: "&СУММ(A:A);"Заполните ячейки без пропусков!")

Формулы, конечно, пишутся в одну строчку, просто так в блоге перенеслось.

 Скачать этот пример в формате файла Excel .xls (17 Кб)

01.04.2014, 16:48 [10833 просмотра]