Вот общее решение. Я специально разместил его в середине листа, чтобы проиллюстрировать обработку пустых столбцов до или после диапазона.
Я использовал несколько вспомогательных столбцов, чтобы упростить просмотр решения и устранение неполадок. Формулы становятся громоздкими, если вы пытаетесь сделать все в одной формуле, хотя вы можете объединить это в единую формулу, если вы обжора для наказания.
Вместо того, чтобы повторно вводить ваши данные, я использовал четыре столбца (от C до F) чисел от 1 до 4, с различными шаблонами пропущенных элементов в строках, чтобы проверить различные условия.
Входные данные
Первый и последний столбцы данных вводятся как переменные, поэтому вы можете использовать столько столбцов, сколько захотите, расположив их где угодно. Я произвольно выбрал I1 и K1 для хранения первого и последнего букв столбца.
Определить первый заполненный столбец
Для каждой строки необходимо найти первое и последнее фактическое значение, поскольку "дыры" учитываются только между ними. Я использовал столбец H, чтобы идентифицировать первый столбец со значением в строке. Я начал свои данные в строке 3, поэтому H3 содержит:
=MATCH(TRUE,LEN(C3:F3)>0,0)+COLUMN(INDIRECT($I$1&":"&$I$1))-1
РЕДАКТИРОВАТЬ: Обратите внимание, что я протестировал это LO Calc, который меняет значение ИСТИНА в этой формуле на 1 . В Calc эта формула становится:
=MATCH(1,LEN(C3:F3)>0,0)+COLUMN(INDIRECT($I$1&":"&$I$1))-1
который был оригинальной формулой в этом ответе, и оказалось, что 1 дает расстройство желудка Excel. Для Excel вам понадобится первая формула.
Это формула массива, поэтому ее нужно вводить, используя Ctrl + Shift + Enter.
Внедрение функции INDIRECT в функцию LEN не работает, поэтому мне пришлось жестко кодировать диапазон. Вам нужно будет изменить диапазон столбцов, если вы используете другое количество столбцов.
Функция COLUMN и -1 регулируют начальную позицию диапазона.
Количество значений, если нет отверстий
Столбец I - это число столбцов от первого значения до последнего (потенциальное количество значений, если нет отверстий). Ячейка I3 содержит:
=LOOKUP(9.99E+307,INDIRECT($I$1&ROW()&":"&$K$1&ROW()))+COLUMN(INDIRECT($I$1&":"&$I$1))-H3
Функция LOOKUP идентифицирует последний столбец, содержащий значение. Опять же, это добавляет корректировку для местоположения диапазона данных на листе. Вычитает номер первого заполненного столбца из последнего.
Количество заполненных значений
Столбец J содержит количество значений в заполненном диапазоне. Ячейка J3 содержит:
=COUNT(INDIRECT($I$1&ROW()&":"&$K$1&ROW()))
Это дает вам то, что вам нужно, чтобы определить свои дыры.
Определить строки с отверстиями
Желаемый результат - это то, что находится в столбце G. Ячейка G3 содержит:
=IF(I3>J3,"hole","")
РЕДАКТИРОВАТЬ: я сделал некоторые консолидации и получил это в одну формулу. Вы можете использовать это в ячейке G3:
=LOOKUP(9.99E+307,INDIRECT($I$1&ROW()&":"&$K$1&ROW()))
+COLUMN(INDIRECT($I$1&":"&$I$1))-MATCH(TRUE,LEN(C3:F3)>0,0)
-COLUMN(INDIRECT($I$1&":"&$I$1))+1
>COUNT(INDIRECT($I$1&ROW()&":"&$K$1&ROW()))
Я добавил разрывы строк для удобства чтения. Если вы хотите скопировать и вставить, вам нужно будет удалить те и лишние пробелы.
Это формула массива, поэтому вам нужно ввести ее с помощью Ctrl + Shift + Enter. Результат просто ИСТИНА или ЛОЖЬ для того, есть ли отверстие:
