Выделение серий повторов в MS EXCEL

history

Пусть имеется столбец с числами, которые могут повторяться. Идущие подряд одинаковые числа - это серия. Например, на рисунке ниже в столбце выделено 2 серии повторов (серия длиной из 5 повторов (число 6) и серия длиной из 4 повторов (число 3).

Выделим с помощью правил Условного форматирования серии повторов определенной длины.

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

Совет: Как выделить заданное количество повторов (не обязательно идущих подряд) показано в статье https://excel2.ru/articles/vydelenie-v-ms-excel-dublikatov-vstrechayushchihsya-3-ili-4-raza

Серия повторов длиной "не менее"

Выделим заливкой только те ячейки в столбце, которые содержат серии повторов с длиной не менее 4 (длина задана в ячейке H7 в файле примера на листе "Пример (не менее)").

Как видно на рисунке ниже, выделена только последовательность длиной не менее 5, хотя имеются и другие последовательности повторов: в диапазоне А18:А21 четыре подряд идущих тройки, а в диапазоне А26:А28 три подряд семерки.

Прежде чем настроить правила условного форматирования создадим 2 столбца со вспомогательными формулами. Сначала определим длину серии с помощью рекурсивной формулы =ЕСЛИ(A7=A8;B7+1;1) в столбце В. Формула рекурсивная, т.к. для расчета значения в ячейке ей нужен результат такой же формулы из ячейки выше (на предыдущем шаге).  

Если предыдущее значение в столбце А совпадает с текущим, то это продолжение серии и формула возвращает результат выражения B7+1 (текущая длина серии увеличивается на 1). Если значения разные, то это начало серии, т.е. формула возвращает 1.

Формула в следующем столбце С чуть сложнее для понимания:

=ЕСЛИ(B8>=$H$7;1;
ЕСЛИ(И(C9=1;B9>1);1;
0))

В ячейке $H$7 введена минимальная длина серии, которая должна быть выделена. Если в столбце В в той же строке указана длина серии равная или более заданной, то, понятно, соответствующее число из столбца А принадлежит нужной серии. За эти вычисления отвечает "первое ЕСЛИ": ЕСЛИ(B8>=$H$7;1;... Первый раз выражение B8>=$H$7 истинно в ячейке С13.

Если это не так (значение длины серии менее заданного), то соответствующее число из столбца А может быть из серии нужной длины, а может быть и нет. Проверка этого осуществляется "вторым ЕСЛИ": ЕСЛИ(И(C9=1;B9>1);1;0) Чтобы понять как это "второе ЕСЛИ" работает, нужно начать с последней строки (номер 31). В последней строке выражение ЕСЛИ(И(C32=1;B32>1);1;0) возвращает всегда 0, т.к. заведомо нижеследующее значение в С32 не равно 1 (ячейка пуста). Вся формула целиком, конечно, может вернуть и 0 и 1 (1 возвращается, когда длина серии больше или равна заданной. За это отвечает "первое ЕСЛИ": ЕСЛИ(B31>=$H$7;1;...). В строках выше "второе ЕСЛИ" возвращает 1 только когда одновременно выполняется 2 условия: в предыдущей ячейке столбца С содержится 1 и в столбце В той же строки значение более 1 (серия продолжается). Работа "второго ЕСЛИ"  хорошо видна в ячейке С26: Условие B26>=$H$7 (т.е. "Первое ЕСЛИ") будет ложным. Начнет проверяться второе условие И(C27=1;B27>1), которое вернет значение ИСТИНА, и, следовательно, вся формула вернет 1.

Совет: о работе функции И() см. статью https://excel2.ru/articles/funkciya-i-v-ms-excel-i, в которой приведена таблица истинности.

Очевидно, что формула в столбце С тоже рекурсивная, ее результат зависит от результата формул в последующих ячейках. Действительно, второе условие И(C27=1;B27>1) в ячейке С26 истинно только потому, что в ячейке С27 содержится 1 (что так же является результатом "второго ЕСЛИ"). "Второе ЕСЛИ" вернет ЛОЖЬ только в ячейке С30, но зато в С30 будет истинным "первое ЕСЛИ", которое и послужит изначальным триггером для "второго ЕСЛИ".

Правило Условного форматирования элементарно. Это просто ссылка на столбец С.

Серия повторов с точно заданной длиной

На листе "Пример (точно)" приведено решение другой задачи - выделить серии повторов с точно заданной длиной. Не смотря на схожесть с предыдущей задачей формулы другие.

Пусть нужная длина последовательности равна 3 (ячейка Н7). Сначала определим начало серии (столбец В):

=И(И(A8=СМЕЩ(A8;;;$H$7));НЕ(И(A8=СМЕЩ(A9;;;$H$7)));НЕ(И(A8=СМЕЩ(A7;;;$H$7))))

Разберем эту формулу массива:

  • Выражение СМЕЩ(A8;;;$H$7) - это ссылка на диапазон с размером, равным заданной длине.  
  • Выражение A8=СМЕЩ(A8;;;$H$7) последовательно сравнивает текущее значение из столбца А с диапазоном, который предположительно содержит последовательность повторов нужной длины. Например, в ячейке В8 имеем массив значений типа {ИСТИНА:ЛОЖЬ:ЛОЖЬ}, который говорит, что в А8:А10 не все значения равны А8. В следующей ячейке В9 выражение вернет {ИСТИНА:ИСТИНА:ИСТИНА} - т.к. в А9:А11 все значения равны между собой.
  • Выражение И(A8=СМЕЩ(A8;;;$H$7)) сворачивает массив из предыдущего шага до одного значения: ИСТИНА, если все значения равны между собой или ЛОЖЬ, если хотя бы одно значение отличается.
  • Не смотря на то, что в диапазоне А9:А11 все значения равны между собой, найденная нами последовательность другой длины (5 повторов, а не 3). Чтобы отсечь слишком длинные последовательности в формулу введены еще 2 однотипных выражения: НЕ(И(A8=СМЕЩ(A9;;;$H$7))) и НЕ(И(A8=СМЕЩ(A7;;;$H$7))), которые проверяют диапазоны смещенные на одну ячейку вниз и вверх относительно текущей строки. Если оба этих выражений вернут ИСТИНА, то это означает, что нами найдена серия нужной длины, точнее первое значение в серии.
  • Наконец, обрамляющая эти 3 составляющих функция И() возвращает ИСТИНА, если все 3 выражения возвращают ИСТИНА: И(A8=СМЕЩ(A8;;;$H$7));   НЕ(И(A8=СМЕЩ(A9;;;$H$7)));   НЕ(И(A8=СМЕЩ(A7;;;$H$7)))

В столбце С мы найдем все элементы серий с длиной 3:

=СЧЁТЕСЛИ(СМЕЩ(B8;;;-$H$7);ИСТИНА)

Если формула возвращает 1, то это значит, что в диапазоне длиной 3 имеется начало последовательности, что значит только одно - текущий элемент - это член последовательности повторов нужно длины.

Например, формула в ячейке С18 возвращает 1, т.к. в диапазоне В16:В18 есть значение ИСТИНА. В последующих 2-х ячейках просматриваются соответственно диапазоны  В17:В19 и  В18:В20. В ячейке С20 содержится 0, т.к. в В19:В21 нет значения ИСТИНА, означающего начало нужной серии.

Правило Условного форматирования элементарно. Это просто ссылка на столбец С.

 



Комментарии

Только для авторизованных пользователей

(только для авторизованных пользователей)

© Copyright 2013 - 2021 Excel2.ru. All Rights Reserved