Как найти последовательность в excel

history 24 октября 2021 г.

Группы статей
• Последовательности Чисел

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

На картинке выше Искомая последовательность размещена в строке 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.

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

Функция ПОСЛЕДОВ позволяет создать список последовательных чисел в массиве, например 1, 2, 3, 4.

В приведенном ниже примере создан массив из 4 строк и 5 столбцов с помощью формулы =ПОСЛЕДОВ(4;5).

=ПОСЛЕДОВ(строки;[столбцы];[начало];[шаг])

Аргумент	Описание
строки Обязательный	Количество возвращаемых строк
[столбцы] Необязательный	Количество возвращаемых столбцов
[начало] Необязательный	Первое число в последовательности
[шаг] Необязательный	Величина приращения каждого последующего значения в массиве

Примечания: 

• Любому отсутствующему необязательному аргументу по умолчанию присваивается значение 1. Если опустить аргумент rows, необходимо указать хотя бы один другой аргумент.

• Массив может рассматриваться как строка со значениями, столбец со значениями или комбинация строк и столбцов со значениями. В приведенном выше примере массивом для формулы ПОСЛЕДОВ является диапазон C1:G4.

• Функция ПОСЛЕДОВ возвращает массив, который будет рассеиваться, если это будет конечным результатом формулы. Это означает, что Excel будет динамически создавать соответствующий по размеру диапазон массива при нажатии клавиши ВВОД. Если ваши вспомогательные данные хранятся в таблице Excel, тогда массив будет автоматически изменять размер при добавлении и удалении данных из диапазона массива, если вы используете структурированные ссылки. Дополнительные сведения см. в статье Поведение рассеянного массива.

• Приложение Excel ограничило поддержку динамических массивов в операциях между книгами, и этот сценарий поддерживается, только если открыты обе книги. Если закрыть исходную книгу, все связанные формулы динамического массива вернут ошибку #ССЫЛКА! после обновления.

Пример

Ниже приведен пример быстрого создания набора данных с использованием функции ПОСЛЕДОВ совместно с функциями ТЕКСТ, ДАТА, ГОД и СЕГОДНЯ, позволяющий получить динамический список месяцев для строки заголовка, в котором исходной датой всегда будет текущий год. Наша формула: =ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()),ПОСЛЕДОВ(1,6),1),»mmm»).

Ниже приведен пример использования в функции ПОСЛЕДОВ вложенных функций ЦЕЛОЕ и СЛЧИС для создания массива из 5 строк и 6 столбцов со случайным набором возрастающих целых чисел. Наша формула: =ПОСЛЕДОВ(5,6,ЦЕЛОЕ(СЛЧИС()*100),ЦЕЛОЕ(СЛЧИС()*100)).

Кроме того, можно использовать формулу =ПОСЛЕДОВ(5;1;1001;1000), чтобы создать последовательный список кодов Главной книги (GL) в примерах.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Функция ФИЛЬТР

Функция СЛУЧМАССИВ

Функция СОРТ

Функция СОРТПО

Функция УНИК

Ошибки #ПЕРЕНОС! в Excel

Динамические массивы и поведение массива с переносом

Оператор неявного пересечения: @

Предположим, у вас есть длинный список порядковых номеров для маркировки элементов, таких как номера чеков в банковских выписках, обычно мы прокручиваем и находим недостающие порядковые номера вручную. Иногда это довольно сложно и требует много времени. Вы можете придумать хитрые способы справиться с этим. Да, есть несколько простых способов быстро и удобно определить и найти последовательность отсутствующих чисел в Excel 2007, Excel 2010 и Excel 2013.

Определите последовательность отсутствующих чисел с помощью формулы ЕСЛИ

Определите последовательность отсутствующих чисел с помощью формулы массива

Определите последовательность пропущенных чисел с помощью Kutools for Excel быстро

---

Определите последовательность отсутствующих чисел с помощью формулы ЕСЛИ

Как мы все знаем, большинство порядковых номеров имеют фиксированное приращение 1, например, 1, 2, 3,…, N. Следовательно, если вы можете определить, что число не меньше 1, чем его следующее число, это означает, что число отсутствует. .

Мы покажем вам руководства с примером, как показано на следующем скриншоте:

1. В пустой ячейке введите формулу = ЕСЛИ (A3-A2 = 1; «»; «Отсутствует»), и нажмите Enter ключ. В этом случае мы вводим формулу в ячейку B2.

Если нет пропущенных чисел, эта формула ничего не вернет; если пропущенные числа существуют, он вернет текст «Отсутствует» в активной ячейке.

2. Выберите ячейку B2 и перетащите маркер заполнения над диапазоном ячеек, который вы хотите содержать эту формулу. Теперь он идентифицирует отсутствующие числа с текстом «Отсутствует» в соответствующих ячейках столбца B. См. Следующий снимок экрана:

---

Определите последовательность отсутствующих чисел с помощью формулы массива

Иногда требуется не только определить последовательность отсутствующих номеров, но и перечислить отсутствующие номера. Вы можете справиться с этим, выполнив следующие шаги:

1. в соседней ячейке введите формулу = SMALL(IF(ISNA(MATCH(ROW(A$1:A$30),A$1:A$30,0)),ROW(A$1:A$30)),ROW(A1))

A1: A30 = диапазон чисел, последовательность для проверки от 1 до 30

2. нажмите Shift + Ctrl + Enter Ключи вместе, чтобы закончить формулу. Скопируйте формулу, пока не получите # ЧИСЛО! ошибки, означающие, что были перечислены все отсутствующие числа. Смотрите скриншот:

---

Определите последовательность пропущенных чисел с помощью Kutools for Excel быстро

Вышеупомянутые методы могут идентифицировать только отсутствующую чистую числовую последовательность, если у вас есть такая последовательность, как AA-1001-BB, AA-1002-BB, они могут не работать успешно. Но не волнуйся, Kutools for Excelмощная функция — Найти отсутствующий порядковый номер может помочь вам быстро определить недостающую последовательность.

Примечание:Чтобы применить это Найти отсутствующий порядковый номер, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите последовательность данных, в которой вы хотите найти недостающую последовательность.

2. Нажмите Кутулс > Вставить > Найти отсутствующий порядковый номер, см. снимок экрана:

3. в Найти отсутствующий порядковый номер диалоговое окно:

(1.) Если вы выберете Вставка нового столбца со следующим отсутствующим маркером вариант, все недостающие порядковые номера отмечены текстом Отсутствующий в новом столбце рядом с вашими данными. Смотрите скриншот:

(2.) Если вы выберете Вставка отсутствующего порядкового номера вариант, все недостающие числа были вставлены в список последовательностей. Смотрите скриншот:

(3.) Если вы выберете Вставка пустых строк при включении отсутствующих порядковых номеров вариант, все пустые строки вставляются, когда отсутствуют числа. Смотрите скриншот:

(4.) Если вы выберете Цвет заливки фона вариант, расположение недостающих номеров будет выделено сразу. Смотрите скриншот:

---

Определите последовательность пропущенных чисел с помощью Kutools for Excel быстро

Функция ПОСЛЕД (на время анонса — ПОСЛЕДОВ) позволяет создать список последовательных чисел в массиве, например 1, 2, 3, 4. Фактически данная функция предлагает более широкие возможности стандартного продления ряда автозаполнением.

Описание функции ПОСЛЕД

Синтаксис

=ПОСЛЕД(строки;[столбцы];[начало];[шаг])

Аргументы

Замечания

• Любому отсутствующему необязательному аргументу по умолчанию присваивается значение 1.



• Массив может рассматриваться как строка со значениями, столбец со значениями или комбинация строк и столбцов со значениями. В приведенном выше примере массивом для формулы ПОСЛЕД является диапазон C1:G4.

Пример

Видео работы функции

Дополнительные материалы

Файл из видео.

Microsoft добавила динамические массивы в Excel и новые функции.

Как найти сумму числового и функционального ряда

Числовой ряд является некой последовательностью, которая рассматривается совместно с другой последовательностью (ее еще называют последовательностью частичных сумм). Подобные понятия применяются в математическом и комплексном анализе.

Сумму числового ряда можно легко вычислить в Excel с помощью функции РЯД.СУММ. Рассмотрим на примере, как работает данная функция, а после построим график функций. Научимся применять числовой ряд на практике при подсчете роста капитала. Но для начала немного теории.

Сумма числового ряда

Числовой ряд можно рассматривать как систему приближений к числам. Для его обозначения применяют формулу:

Здесь показана начальная последовательность чисел ряда и правило суммирования:

• ∑ — математический знак суммы;
• a_i — общий аргумент;
• _i — переменная, правило для изменения каждого последующего аргумента;
• ∞ — знак бесконечности, «предел», до которого проводится суммирование.

Запись обозначает: суммируются натуральные числа от 1 до «плюс бесконечности». Так как _i = 1, то подсчет суммы начинается с единицы. Если бы здесь стояло другое число (например, 2, 3), то суммировать мы начинали бы с него (с 2, 3).

В соответствии с переменной _i ряд можно записать развернуто:

= а₁ + а₂ + а₃ + а₄ + а₅ + … (до «плюс бесконечности).

Определение суммы числового ряда дается через «частичные суммы». В математике они обозначаются Sn. Распишем наш числовой ряд в виде частичных сумм:

S₁ = а₁

S₂ = а₁ + а₂

S₃ = а₁ + а₂ + а₃

S₄ = а₁ + а₂ + а₃ + а₄

Сумма числового ряда – это предел частичных сумм S_n. Если предел конечен, говорят о «сходящемся» ряде. Бесконечен – о «расходящемся».

Сначала найдем сумму числового ряда:

М = 10.

Теперь построим в Excel таблицу значений членов ряда:

Общий первый аргумент берем из формулы: i=3.

Все следующие значения _i находим по формуле: =B4+$B$1. Ставим курсор в нижний правый угол ячейки В5 и размножаем формулу.

Найдем значения. Делаем активной ячейку С4 и вводим формулу: =СУММ(2*B4+1). Копируем ячейку С4 на заданный диапазон.

Значение суммы аргументов получаем с помощью функции: =СУММ(C4:C11). Комбинация горячих клавиш ALT+«+» (плюс на клавиатуре).



Функция РЯД.СУММ в Excel

Для нахождения суммы числового ряда в Excel применяется математическая функция РЯД.СУММ. Программой используется следующая формула:

Аргументы функции:

• х – значение переменной;
• n – степень для первого аргумента;
• m – шаг, на который увеличивается степень для каждого последующего члена;
• а – коэффициенты при соответствующих степенях х.

Важные условия для работоспособности функции:

• все аргументы обязательные (то есть все должны быть заполнены);
• все аргументы – ЧИСЛОвые значения;
• вектор коэффициентов имеет фиксированную длину (предел в «бесконечность» не подойдет);
• количество «коэффициентов» = числу аргументов.

Вычисление суммы ряда в Excel

Та же функция РЯД.СУММ работает со степенными рядами (одним из вариантов функциональных рядов). В отличие от числовых, их аргументы являются функциями.

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

Например, положили в банк определенную сумму денег (а) на определенный период (n). Имеем ежегодную выплату х процентов. Для расчета наращенной суммы на конец первого периода используется формула:

S₁ = a (1 + x).

На конец второго и последующих периодов – вид выражений следующий:

S₂ = a (1 + x)²;
S₃ = a (1 + x)² и т.д.

Чтобы найти общую сумму:

S_n = a (1 + x) + a (1 + x)² + a (1 + x)³ + … + a (1 + x)ⁿ

Частичные суммы в Excel можно найти с помощью функции БС().

Исходные параметры для учебной задачи:

Используя стандартную математическую функцию, найдем накопленную сумму в конце срока сумму. Для этого в ячейке D2 используем формулу: =B2*СТЕПЕНЬ(1+B3;4)

Теперь в ячейке D3 решим эту же задачу с помощью встроенной функции Excel: =БС(B3;B1;;-B2)

Результаты одинаковые, как и должно быть.

Как заполнить аргументы функции БС():

1. «Ставка» — процентная ставка, под которую оформлен вклад. Так как в ячейке В3 установлен процентный формат, мы в поле аргумента просто указали ссылку на эту ячейку. Если было бы указано число, то прописывали бы его сотую долю (20/100).
2. «Кпер» — число периодов для выплат процентов. В нашем примере – 4 года.
3. «Плт» — периодические выплаты. В нашем случае их нет. Поэтому поле аргумента не заполняем.
4. «Пс» — «приведенная стоимость», сумма вклада. Так как мы на время расстаемся с этими деньгами, параметр указываем со знаком «-».

Таким образом, функция БС помогла найти нам сумму функционального ряда.

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

Построение графика функций суммы числового ряда

Построим график функций, отражающий рост капитала. Для этого нам нужно построить график функции являющейся суммой построенного ряда. За пример, возьмем те же данные по вкладу:

Дальше нам нужна функция для начисления сложных процентов — БС(). Мы узнаем будущею стоимость инвестиций при условии равных платежей и постоянной процентной ставке. Используя функцию БС(), заполним таблицу:

В первой строке показана накопленная сумма через год. Во второй – через два. И так далее.

Сделаем еще один столбец, в котором отразим прибыль:

Как мы считали – в строке формул.

На основании полученных данных построим график функций.

Выделим 2 диапазона: A5:A9 и C5:C9. Переходим на вкладку «Вставка» — инструмент «Диаграммы». Выбираем первый график:

Сделаем задачу еще более «прикладной». В примере мы использовали сложные проценты. Они начисляются на наращенную в предыдущем периоде сумму.

Возьмем для сравнения простые проценты. Формула простых процентов в Excel: =$B$2*(1+A6*B6)

Добавим полученные значения в график «Рост капитала».

Какие именно выводы сделает инвестор – очевидно.

Математическая формула частичной суммы функционального ряда (с простыми процентами): S_n = a (1 + x*n), где а – первоначальная сумма вклада, х – проценты, n – период.