Как пометить строки с шаблоном непустым, пустым, непустым с помощью Excel

Question

Я хотел бы, чтобы вы помогли с чем-то, что я пытался выяснить в течение последних двух дней.

У меня есть данные с тысячами строк и нескольких столбцов. Я хочу вставить столбец с формулой (массив), которая помечает все строки, которые имеют "дыры"; это любая строка, имеющая один или несколько пустых столбцов между непустыми столбцами, как в следующих примерах:

Мой фактический набор данных может иметь от 3 до 7 столбцов. Для примера выше (4 столбца) я попробовал следующую формулу:

IF(OR(AND(SUM(B13:C13)=0,COUNT(A13,D13)>1),AND(B13=0,COUNT(A13,C13)>1),AND(C13=0,COUNT(B13,D13)>1)),"Issue","OK") 

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

Answer 1

Я придумал новую формулу, которая работает лучше, чем оригинальная формула, которую я придумал. Это новая формула:

SUMPRODUCT(--NOT(ISBLANK(OFFSET(B2:E2,0,1))),--ISBLANK(B2:E2),--NOT(ISBLANK(OFFSET(B2:E2,0,-1))))+SUMPRODUCT(--(SUM(C2:D2)=0),--(SUM(B2,E2)>0))

Я чувствую себя более уверенно, что при использовании 4 столбцов подобраны все непустые, пустые {, пустые}, непустые шаблоны. Но вот две основные проблемы:

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

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

Я делюсь этим решением в надежде мотивировать других людей придумать более идеальное решение.

Answer 2

Вот общее решение. Я специально разместил его в середине листа, чтобы проиллюстрировать обработку пустых столбцов до или после диапазона.

Я использовал несколько вспомогательных столбцов, чтобы упростить просмотр решения и устранение неполадок. Формулы становятся громоздкими, если вы пытаетесь сделать все в одной формуле, хотя вы можете объединить это в единую формулу, если вы обжора для наказания.

Вместо того, чтобы повторно вводить ваши данные, я использовал четыре столбца (от 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. Результат просто ИСТИНА или ЛОЖЬ для того, есть ли отверстие: