В Excel, как проверить, недействительна ли какая-либо ячейка в заданном диапазоне в соответствии с правилами проверки данных?

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

Все ячейки в диапазоне, который я хочу проверить, используют одни и те же правила для их проверки данных, но эти правила довольно сложны и зависят от значений других ячеек в каждой строке. Я уже знаю, что вы можете проверить, соответствует ли какая-либо ячейка в данном столбце определенному значению с помощью функции VLOOKUP (например,  `= IF (ISNA (VLOOKUP («Lookup Value», A1: A20, 1, FALSE)), «Не найдено», «Найдено»)` ), но, похоже, не существует способа использовать тот же метод для проверки наличия ячеек с недопустимыми данными.

Есть ли способ сделать это в Excel без использования VBA? Если нет, можете ли вы порекомендовать альтернативное решение?

Таким образом, любая строка недействительна, если она содержит Fail или N/A в столбце D, а столбец E в той же строке пуст?

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

 `= IF (SUM (COUNTIFS (D: D, {"Fail", "N/A"}, E: E, "")), "Invalid Data", "All OK")` 

или эта версия даст вам количество экземпляров

`=IF(SUM(COUNTIFS(D:D,{"Fail","N/A"},E:E,"")),SUM(COUNTIFS(D:D,{"Fail","N/A"},E:E,""))&" Row(s) of Invalid Data","All OK")`

COUNTIFS действует только в Excel 2007 или новее, в более ранних версиях вы можете подсчитать недопустимые строки с помощью SUMPRODUCT, т. Е.

<Код> = СУММПРОИЗВ ((D1: D1000 = { "Сбой", "N/A"}) * (Е1: E1000 = ""))