У Блэра есть рабочий лист, разделенный на две области: ввод данных и проверка данных. Область проверки данных состоит из формул, которые проверяют записи с помощью операторов IF. Если проблема обнаружена, текстовое сообщение отображается в ячейке в области проверки, в противном случае формула возвращает пустое значение. Ниже приводится типичная формула проверки:

=IF(A1<>5,"Value in A1 is not 5")

Проблема в том, что область проверки данных может быть довольно большой, а значит, одно из текстовых сообщений легко пропустить. Блэр задался вопросом, есть ли способ создать формулу, которая проверяла бы область проверки данных и возвращала бы одно сообщение, если в этой области были какие-либо другие сообщения.

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

=SUM((LEN(ValRange)>0)*1)

Убедитесь, что ValRange заменен диапазоном ячеек в области проверки данных. Кроме того, убедитесь, что вы вводите формулу, нажимая Shift + Ctrl + Enter (чтобы обозначить, что это формула массива). Формула возвращает значение, указывающее, сколько ячеек в диапазоне имеют длину больше 0. Другими словами, она подсчитывает количество ячеек, в которых отображаются сообщения.

Если вы предпочитаете не использовать формулу массива, вы можете добиться того же результата, используя следующую обычную формулу:

=SUMPRODUCT((LEN(ValRange)>0)*1)

Результатом опять же является количество ячеек, длина которых больше 0. Другой подход — использовать некоторые функции СЧЁТ, предоставляемые Excel:

=COUNTA(ValRange) - COUNTBLANK(ValRange)

Эта формула подсчитывает количество ячеек в диапазоне, а затем вычитает количество пустых ячеек в диапазоне. Результат — количество непустых ячеек или ячеек, в которых отображаются сообщения. Для определения простого ответа да / нет можно использовать другой шаблонный подход:

=IF(COUNTIF(ValRange,"?*"),"","No ") & "Verification Messages"

Если в ValRange нет сообщений, формула возвращает «Нет проверочных сообщений». Если есть сообщения, он удаляет «Нет» и просто возвращает «Проверочные сообщения».

Также было бы неплохо применить условное форматирование к области проверки данных. Хотя обсуждаемые до сих пор формулы сообщают вам, есть ли сообщения, они не выделяют, где эти сообщения находятся — условное форматирование может точно определить каждое сообщение. Выделите все ячейки в области, содержащие формулы, а затем используйте условное форматирование, чтобы проверить длину этих ячеек. Если длина больше 0, ячейка может быть отформатирована для отображения красного фона. Из-за этого будет намного сложнее пропустить любые сообщения в области проверки данных при прокрутке листа.

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

Вы можете добиться тех же результатов (проверка того, что находится в области ввода данных), используя проверку данных для каждой из ячеек ввода. При правильной настройке проверка данных гарантирует, что пользователь вводит допустимые значения в каждую ячейку, устраняя необходимость в большей части области проверки данных.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (2772) применим к Microsoft Excel 97, 2000, 2002 и 2003.