Пусть дана некая числовая последовательность, значения которой содержатся в отдельной строке или столбце. Будем искать в столбце с данными повтор такой последовательности.

На картинке выше Искомая последовательность размещена в строке 7 и выделена зеленым (шесть чисел). Поиск производится в столбце В, начиная с ячейки В11. На картинке показана найденная последовательность, которая также выделена зеленым для наглядности.

Найти последовательность можно разными способами, в статье приведено их 2. 

Примечание: Приведенные в статье формулы будут работать во всех версиях MS EXCEL - EXCEL 365, Excel 2021/2019/2016/2013/2010/2007 и Excel 97–2003.

Способ 1

В 11-й строке (с этой строки начинается список) выведем первые 10 значений из списка. Это можно сделать формулой массиваТРАНСП() или простыми формулами. 

Совет: О транспонировании строк подробно написано в этом разделе https://excel2.ru/gruppy-statey/transponirovanie. В этих статьях показано как можно транспонировать диапазоны и без формул массива.

Чтобы ввести функцию ТРАНСП()

• выделите диапазон ячеек С11:L11,
• затем в Строке формул введите =ТРАНСП(B11:B20),
• нажмите CTRL+SHIFT+ENTER. 

После копирования указанных ячеек вниз (также нужно сначала выделить диапазон С11:L11 и протянуть вниз с помощью Маркера заполнения. В итоге будет сформирован массив чисел как на рисунке выше. В строке 12 будут размещены значения из списка, начиная со второго, в строке 13, начиная с третьего и т.д.

Теперь займемся построчным сравнением. В способе 1 это реализовано замысловатой формулой =НЕ(СУММПРОИЗВ(--(СМЕЩ(C11;;;;$A$8)<>СМЕЩ($C$8;;;;$A$8)))) в столбце N.

Разберем подробнее. Так как длина исходной последовательности может быть произвольной, то нам нужно "вырезать" из диапазона С8:L8 только те ячейки, которые содержат значения последовательности, а не пустые ячейки. Это сделано формулой СМЕЩ($C$8;;;;$A$8), где в А8 содержится длина последовательности. О функции СМЕЩ() читайте здесь.

Аналогично формируется диапазон для сравнения СМЕЩ(C11;;;;$A$8). Обратите внимание на отсутствие знаков $ абсолютной адресации. Т.к. этот диапазон должен изменяться при копировании формулы вниз (в отличие от первого диапазона).

Идем дальше - посмотрим на выражение (СМЕЩ(C11;;;;$A$8)<>СМЕЩ($C$8;;;;$A$8)). Для первого сравнения (строка 11 и исходная последовательность) это выражение даст {ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА}, т.е. ни одно из значений не совпало (этот массив можно получить выделив в Строке формул это выражение и нажать F9). Оно и понятно, исходная последовательность 5; 6; 11; 6; 8; 1 ничего не имеет общего со значениями 11-й строки 1; 2; 34; 5; 6; 11 (если сравнивать поэлементно).

Совсем другое дело, если сравнивать со строкой 14. Выражение вернет {ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ}, что означает, что первый элемент исходной последовательности (5) равен значению из ячейки С14, второй (6) равно значению из ячейки D14, и т.д.

Затем подвергаем полученный массив преобразованию с помощью двойной смены знака (--), при этом ЛОЖЬ будет переведена в число 0, а ИСТИНА - в 1 (так устроен EXCEL: он хранит ИСТИНА как 1, а ЛОЖЬ как 0). Вместо двукратного умножения на -1, можно было прибавить 0 или возвести в степень 1. Любое математическое действие со значениями логического типа данных будет преобразовывать их в число 0 или 1.

Функция СУММПРОИЗВ() просто сложит все 0 или 1. В результате получим 0 только если все значения попарно равны между собой. Можно вместо СУММПРОИЗВ() использовать функцию СУММ(), но тогда нам пришлось бы вводить ее как формулу массива.

Наконец, функция НЕ() переводит числовые значения в логический тип данных, изменяя на противоположное значение: 0 - будет переведен в ИСТИНА, а любое другое число в ЛОЖЬ. Можно обойтись и без этой функции, тогда будет выведено число от 0 (все значения совпали) до 6 (ни одно не совпало). 

Формула в ячейке N8 =ПОИСКПОЗ(ИСТИНА;N11:N55;0) вернет номер позиции первой ячейки со значением ИСТИНА (их может быть несколько, об этом ниже). Начиная с этой позиции списка будет располагаться искомая последовательность. Ее можно выделить условным форматированием.

Чтобы настроить Условное форматирование нам потребуется написать простое правило =И($A11>=$N$8;$A11<$N$8+$A$8).

Вариантов написания формулы для поиска последовательности множество, например, формула =(СУММПРОИЗВ(--(СМЕЩ(C11;;;;$A$8)=СМЕЩ($C$8;;;;$A$8))))=$A$8, которая вернет ИСТИНА в тех же строках, что и предыдущая формула.

На листе "все найденные" приведен поиск сразу всех вхождений последовательности.

Для реализации этого решения добавлено несколько формул:

• используйте формулу =СУММПРОИЗВ(--N11:N55) чтобы найти общее количество исходных последовательностей в списке (ячейка О6)
• формулы в столбце О: =СЧЁТЕСЛИ($N$11:N11;ИСТИНА), будут показывать сколько последовательностей найдено в строках выше.
• в ячейке О7 (серая ячейка) для удобства сформирован выпадающий список, это реализовано формулой =СМЕЩ($A$11;;;$O$6). Если, например, найдено 3 совпадения, то будет сформирован список 1; 2; 3. Выбирая нужный повтор в списке будут подсвечены соответствующие найденные последовательности.
• если кому не удобно пользоваться выпадающим списком можно нажимать на элемент управления Счетчик.

В этой статье разжеваны все формулы, если и теперь не все понятно, то нужно почитать статьи на нашем сайте excel2.ru, на которые ведут ссылки в этой статье.

Способ 2

На листе Вариант2 приведено другое решение этой задачи (подсвечивается только первая найденная последовательность).

В этом варианте нет формулы массива ТРАНСП(). Исходный список многократно повторяется начиная со строки 14 и ниже (при этом последовательно отбрасывается первое значение из предыдущей строки и производится смещение всего списка на 1 позицию влево). Такой подход позволяет сравнивать исходный список (столбец D с зелеными ячейками) со столбцами значений, вырезанными из исходного списка.

При увеличении длины исходного списка соответственно растет количество столбцов в этих строках. При большой длине списка это не удобно.

Количество совпавших значений выводится в строке 11. Если это число совпадений равно длине искомой последовательности, то это значение подсвечивается красным. Найденная позиция отображается в строке 13.

Вариантов решения этой задачи множество, поэтому если будете решать самостоятельно, то наверняка придете к другому варианту решения.