Как сделать рекурсию в excel

Обновлено: 06.07.2024

ЕГЭ-16: решение через Excel
В этой презентации рассмотрено решение некоторых
задач из 16 задания ЕГЭ через Excel.
Презентация не подготовит Вас полностью к решению
16-го задания!
Нужно будет также научиться решать задачи
программированием: некоторые типы задач невозможно
или слишком тяжело решить с помощью Excel.

Задача 1
Алгоритм вычисления функции F(n) задан следующими
соотношениями:
F(n) = 1 при n = 1
F(n) = n + F(n–1), если n чётно,
F(n) = 2· F(n–2), если n > 1 и n нечётно.
Чему равно значение функции F(26)?

Решение задачи 1 - Excel
Решение с помощью Excel является усовершенствованным способом решения через
таблицу. Будьте очень аккуратны при решении задач таким методом: очень легко
допустить ошибку, скопировав не в ту ячейку.
Требуется создать таблицу из двух строк и 27 колонок:
Заполним ячейку B2 (значение F(1)). По условию задачи F(1) = 1.

Решение задачи 1 - Excel
Следующая ячейка, которую нужно заполнить, B3 (значение F(2)). 2 – чётное число,
поэтому:
F(n) = n + F(n–1)
Если n = 2, F(2) = 2 + F(1)
Чтобы формулу в дальнейшем можно было растиражировать, везде, там, где в
формуле стоит n и F(n -1), мы поставим ссылки на конкретные ячейки в таблице.
Конкретные значения (2 и 1) подставлять ни в коем случае не нужно!
n = 2 - это ячейка C1
F(n - 1) = F(1) – это ячейка B2
Формула в ячейка выглядит так:
= C1 + B2
Формулу требуется переписать прямо вместе с равно.

Решение задачи 1 - Excel
Для F(3):
F(n) = 2· F(n–2)
Т.е. в ячейке D2 нужно написать =2*B2

Решение задачи 1 - Excel
Получаем:
Для F(4), F(6), F(8), F(10) . F(26) будет работать та же формула, что и для F(2), а для
F(5), F(7), F(9), F(11) . F(25) – та же формула, что и для F(3).
Растиражируем формулу:
<- нужно зажать квадратик в правом нижнем углу выделения и протянуть
до числа 26

Самостоятельно
1.1) Чему равно значение функции F(10)?
F(n) = 1 при n = 1
F(n) = 2·F(n–1) + n + 3, если n > 1
1.2) Чему равно значение функции F(1)?
F(n) = 2n – 5 при n > 12
F(n) = 2·F(n+2) + n – 4, если n <= 12
1.3) Чему равно значение F(64)?
F(n) = 1 при n = 1
F(n) = 2·F(n–1), если n чётно,
F(n) = 5n + F(n–2), если n нечётно.

Задача 2
Алгоритм вычисления функции F(n) задан следующими
соотношениями:
F(n) = 5–n при n < 5
F(n) = 4· (n – 5)·F(n–5), если n делится на 3,
F(n) = 3n + 2·F(n–1) + F(n–2), если n не делится на 3.
Чему равно значение функции F(20)?

Решение задачи 2 - Excel
Решение подстановкой выходит очень сложным – слишком много возможностей
допустить арифметическую ошибку. Гораздо удобнее решать эту задачу в Excel.
Требуется создать таблицу из двух строк и 21 колонки:
По условию задачи F(n) = 5–n при n < 5. На самом деле это – выход из рекурсии
(потому что значение F(n) можно вычислить сразу же).
Определим, какую формулу нужно записать в ячейку B2:
5 – это константа, поэтому так и перепишем в формулу;
n находится в ячейке B1. Нельзя подставлять сюда значение 1: так формулу не
получится растиражировать.

Решение задачи 2 - Excel
Итоговая формула для ячейки B2:
=5 – B1
Формула F(n) = 5 – n справедлива при n < 5, т.е. для ячеек B2 – E2. Растиражируем:
<- нужно зажать квадратик в правом нижнем углу выделения и протянуть
до числа 4 (ячейка E2)

Решение задачи 2 - Excel
Получаем:
Для остальных ячеек эта формула уже не подойдёт.
Для n=5:
F(n) = 3n + 2·F(n–1) + F(n–2)
3 и 2 – константы, их просто переписываем в формулу. Значение n для n=5 находится
в ячейке F1, значение F(n-1) находится в ячейке E2, значение F(n – 2) находится в
ячейке D2, значение F(n) (т.е. F(5)) находится в ячейке F2. Итоговая формула в
ячейке F2:
=3*F1 + 2*E2 + D2

Решение задачи 2 - Excel
Для F(6) (ячейка G2):
F(n) = 4· (n – 5)·F(n–5), т.к. 6 делится на 3
4 – константа, так и переписываем, n - это ячейка G1, F(n – 5) – это F(1), т.е. ячейка
B2. Получаем:
= 4 * (G1 - 5) * B2

Решение задачи 2 - Excel
Для F(7) (ячейка H2) формула строится по такому же принципу, как и для F(5).
Получаем, что в ячейку H2 надо записать формулу:
=3*H1+2*G2+F2
Для F(9), F(12), F(15), F(18) формула строится таким же образом, как и для F(6).
Для F(8), F(11), F(14), F(17), F(20) формула строится так же, как и для F(5).
Для F(10), F(13), F(16), F(19) формула строится так же, как и для F(7).
Все значения n "покрыты". Можно тиражировать формулу.

Решение задачи 2 - Excel
<- нужно зажать квадратик в правом
нижнем углу выделения и протянуть до
числа 4 (ячейка E2)
Обратите внимание: тиражируются ячейки F2-H2, первые ячейки таблицы мы не
трогаем.
После тиражирования:
полностью значение в ячейки S2-U2 не
влезает, чтобы увидеть ответ, растяните
ячейку U2

Самостоятельно
2.1) Алгоритм вычисления функции F(n) задан следующими соотношениями:
F(n) = 2 · n · n · n + n · n при n > 25
F(n) = F(n+2) + 2 · F(n+3), если n <= 25
Чему равна сумма цифр значения функции F(2)?
2.2) Алгоритм вычисления функции F(n) задан следующими соотношениями:
F(n) = 1+2n при n < 5
F(n) = 2·(n + 1)·F(n–2), если n делится на 3,
F(n) = 2·n + 1 + F(n–1) + 2·F(n–2), если n не делится на 3.
Чему равно значение функции F(15)?
2.3) Алгоритм вычисления функции F(n) задан следующими соотношениями:
F(n) = n + 3 при n < 3
F(n) = (n + 2)·F(n–4), если n делится на 3,
F(n) = n + F(n–1) + 2·F(n–2), если n не делится на 3.
Чему равно значение функции F(20)?

Задача 3
Алгоритм вычисления функций F(n) и G(n) задан
следующими соотношениями:
F(1) = G(1) = 1
F(n) = 2·F(n–1) + G(n–1) – 2, если n > 1
G(n) = F(n–1) +2·G(n–1), если n > 1
Чему равно значение F(14) + G(14)?

Решение задачи 3 – Excel
Создадим таблицу из 3 строк и 15 колонок.
По условию задачи F(1) = 1, G(1) = 1.

Решение задачи 3 – Excel
Разберём формулу F(n) = 2·F(n–1) + G(n–1) – 2, если n > 1
Для F(2) получаем: F(2) = 2*F(1) + G(1) – 2
F(1) – это ячейка B2
G(1) – это ячейка B3
F(2) – это ячейка C2
Т.е. в ячейку C2 нужно записать формулу:
= 2*B2 + B3 – 2

Решение задачи 3 – Excel
Получаем:
Сразу же тиражируем на всю 2-ю строчку. Значения получатся
неправильные, но мы потом их исправим.

Решение задачи 3 – Excel
Разберём формулу G(n) = F(n–1) +2·G(n–1), если n > 1
Для G(2) получаем: G(2) = F(1) + 2*G(1)
F(1) – это ячейка B2
G(1) – это ячейка B3
G(2) – это ячейка C3
Т.е. в ячейку C3 нужно записать формулу:
= B2 + 2*B3

Решение задачи 3 – Excel
Получаем:
Сразу же тиражируем на всю 3-ю строчку. Теперь значения во всей
таблице будут правильными.

Решение задачи 3 – Excel
Растянем последний столбец, чтобы увидеть, чему равны
значения F(14) и G(14).
Т.к. в ответе требуется сумма F(14)+G(14), в какой-нибудь
произвольной пустой ячейке запишем:
= O2 + O3

Самостоятельно
3.1) Алгоритм вычисления функций F(n) и G(n) задан следующими соотношениями:
F(n) = G(n) = 1 при n = 1
F(n) = F(n–1) – n· G(n–1), при n > 1
G(n) = F(n–1) + 2· G(n–1), при n > 1
Чему равно значение функции G(18)?
3.2) Алгоритм вычисления функций F(n) и G(n) задан следующими соотношениями:
F(n) = G(n) = 1 при n = 1
F(n) = F(n–1) – 2 · G(n–1), при n > 1
G(n) = F(n–1) + G(n–1) + n, при n > 1
Чему равна сумма цифр значения функции G(36)?
3.3) Алгоритм вычисления функций F(n) и G(n) задан следующими соотношениями:
F(n) = G(n) = 4*n – 2 при n > 7
F(n) = F(n+1) - G(n+2) + n, при n <= 7
G(n) = 2*F(n+2) - G(n+1), при n <= 7
Чему равно значение F(-9) + G(-9)?

На данный момент в Microsoft Excel имеется почти пять сотен функций рабочего листа, доступных через окно Мастера функций - кнопка fx в строке формул. Это весьма приличный набор, но, тем не менее, почти каждый пользователь рано или поздно сталкивается с ситуацией, когда в этом списке не оказывается нужной ему функции - просто потому, что её нет в Excel.

До сих пор единственным способом решить эту проблему были макросы, т.е. написание своей пользовательской функции (UDF = User Defined Function) на Visual Basic, что требует соответствующей программистской квалификации и бывает, порой, совсем непросто. Однако, с последними обновлениями Office 365 ситуация изменилась в лучшую сторону - в Excel была добавлена специальная "обёрточная" функция LAMBDA. С её помощью задача по созданию собственных функций теперь решается легко и красиво.

Давайте рассмотрим принцип её использования на следующем примере.

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

Шаг 1. Пишем формулу

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

Шаг 2. Заворачиваем в LAMBDA и тестируем

Теперь пришло время применить новую функцию LAMBDA и завернуть в неё нашу формулу. Синтаксис у функции следующий:

=LAMBDA( Переменная1 ; Переменная2 ; . ПеременнаяN ; Выражение )

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

В нашем случае переменная будет только одна - дата, для которой мы считаем номер квартала. Переменную для неё давайте назовем, допустим, d. Тогда завернув нашу формулу в функцию LAMBDA и подменив адрес исходной ячейки A2 на придуманное имя переменной, мы получим:

Обратите внимание, что после такого преобразования наша формула (по сути - правильная!) стала выдавать ошибку, т.к. теперь в неё не передается исходная дата из ячейки А2. Для тестирования и уверенности, можно передать ей аргументы, дописав их после функции LAMBDA в круглых скобках:

Шаг 3. Создаем имя

Теперь самая легкая и приятная часть. Открываем Диспетчер имен на вкладке Формулы (Formulas - Name Manager) и создаём новое имя кнопкой Создать (Create) . Придумываем и вводим имя для нашей будущей функции (например, НОМКВАРТАЛА), а в поле Ссылка (Reference) аккуратно копируем из строки формул и вставляем нашу функцию LAMBDA, только без последнего аргумента (А2):

Всё. После нажатия на ОК созданную функцию можно использовать в любой ячейке на любом листе этой книги:

Использование в других книгах

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

LAMBDA и динамические массивы

Пользовательские функции, создаваемые с помощью функции LAMBDA успешно поддерживают работу с новыми динамическими массивами и их функциями (ФИЛЬТР, УНИК, СОРТ), добавленными в Microsoft Excel в 2020 году.

Допустим, мы хотим создать новую пользовательскую функцию, которая сравнивала бы два списка и выдавала разницу между ними - те элементы из первого списка, которые отсутствуют во втором. Жизненная задача, не правда ли? Раньше для такого использовали либо функции а-ля ВПР (VLOOKUP) , либо сводные таблицы, либо запросы Power Query. Теперь же можно обойтись одной формулой:

В английской версии это будет:

Здесь функция СЧЁТЕСЛИ подсчитывает количество вхождений каждого элемента первого списка во второй, а затем функция ФИЛЬТР отбирает только те из них, у кого этих вхождений не оказалось. Завернув эту конструкцию в LAMBDA и создав на её основе именованный диапазон с названием, например, ПОИСКОТЛИЧ - мы получим удобную функцию, выдающую результат сравнения двух списков в виде динамического массива:

Если в качестве исходных данных будут не обычные, а "умные" таблицы - наша функция тоже справится без проблем:

Другой пример - динамическое разделение текста путём превращения его в XML и последующего разбора по ячейкам с помощью функции ФИЛЬТР.XML, которое мы недавно разбирали. Чтобы не воспроизводить эту сложную формулу каждый раз вручную, проще будет завернуть её в LAMBDA и создать на её основе динамический диапазон, т.е. новую компактную и удобную функцию, назвав её, например, РАЗДТЕКСТ:

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

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

Рекурсивный перебор символов

Все предыдущие примеры демонстрировали только одну, наиболее очевидную, сторону функции LAMBDA - её применение в качестве "обёртки" для заворачивания в неё длинных формул и упрощения их ввода. На самом деле, у LAMBDA есть ещё одна, гораздо более глубокая, сторона, превращающая её почти что в полноценный язык программирования.

Дело в том, что принципиально важной особенностью LAMBDA-функций является возможность реализации в них рекурсии - логики вычислений, когда в процессе расчета функция вызывает сама себя. С непривычки, возможно, звучит жутковато, но в программировании рекурсия - обычное дело. Даже в макросах на Visual Basic можно её реализовать, а теперь, вот, как видите, дошло и до Excel. Давайте попробуем разобраться в этой технике на практическом примере.

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

Однако, по сравнению с предыдущими, нерекурсивными примерами, нас ожидают две сложности.

Нам придётся придумать название для нашей функции до того, как мы начнём писать её код, т.к. в нём это название уже будет использоваться для вызова функцией самой себя.
Ввести такую рекурсивную функцию в ячейку и отлаживать её, указав после LAMBDA аргументы в скобках (как мы делали ранее) не получится. Придётся создавать функцию сразу "с нуля" в Диспетчере Имен (Name Manager).

Назовём нашу функцию, допустим, ОЧИСТКА и хотелось бы, чтобы у неё было два аргумента - текст, который нужно почистить и список исключаемых символов в виде текстовой строки:

Создадим, как делали ранее, на вкладке Формулы в Диспетчере имён именованный диапазон, назовём его ОЧИСТКА и введём в поле Диапазон следующую конструкцию:

Здесь переменная t - это исходный очищаемый текст, а d - список символов на удаление.

Работает всё это следующим образом:

Итерация 1

Фрагмент ПОДСТАВИТЬ(t;ЛЕВСИМВ(d);""), как легко догадаться, заменяет в исходном тексте t первый слева символ из удаляемого набора d на пустую текстовую строку, т.е. удаляет букву "А". В качестве промежуточного результата получаем:

Вш зкз н 125 руб.

Итерация 2

Затем функция вызывает сама себя и в качестве входных данных (первый аргумент) получает уже то, что осталось после очистки на предыдущем шаге, а вторым аргументом задавая строку исключаемых символов начиная не с первого, а со второго символа, т.е. "БВГДЕЁЖЗИКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ.," без начальной "А" - это делает функция ПСТР. Как и до этого, функция берет первый слева символ из оставшихся (Б) и заменяет его в данном ей тексте (Зкз н 125 руб.) на пустую строку - получаем в качестве промежуточного результата:

Вш зкз н 125 ру.

Итерация 3

Функция опять вызывает сама себя, получая в качестве первого аргумента то, что осталось от зачищаемого текста на предыдущей итерации (Вш зкз н 125 ру.), а в качестве второго - урезанный слева ещё на один символ набор исключаемых знаков, т.е. "ВГДЕЁЖЗИКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ.," без начальной "Б". Затем опять берёт из этого набора первый символ слева (В) и удаляет его из текста - получаем:

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

Когда все символы закончатся нам необходимо будет выйти из цикла - эту роль как раз и выполняет функция ЕСЛИ (IF) , в которую завернута наша конструкция. Если символов для удаления не осталось (d=""), то функция не должна больше вызывать саму себя, а просто должна вернуть зачищаемый текст (переменная t) в его финальном виде.

Рекурсивный перебор ячеек

Похожим образом можно реализовать и рекурсивный перебор ячеек в заданном диапазоне. Предположим, что мы хотим создать лямбда-функцию с именем ЗАМЕНАПОСПИСКУ для оптовой замены фрагментов в исходном тексте по заданному списку-справочнику. Выглядеть всё это в результате должно так:

Т.е. у нашей функции ЗАМЕНАПОСПИСКУ будет три аргумента:

ячейка с текстом для обработки (исходный адрес)
первая ячейка столбца со значениями для поиска из справочника
первая ячейка столбца со значениями на замену из справочника

Функция должна проходить сверху-вниз по справочнику и заменять последовательно все варианты из левого столбца Найти на соответствующие варианты из правого столбца Заменить. Реализовать такое можно следующей рекурсивной лямбда-функцией:

Здесь в переменной t хранится исходный текст из очередной ячейки столбца Адрес, а переменные n и z указывают на первые ячейки в столбцах Найти и Заменить, соответственно.
Как и в предыдущем примере, сначала эта функция заменяет в исходном тексте с помощью функции ПОДСТАВИТЬ (SUBSTITUTE) данные по первой строке справочника (т.е. СПб на Санкт-Петербург ), а затем вызывает сама-себя, но со сдвигом по справочнику вниз на следующую строку (т.е. заменяет С-Пб на Санкт-Петербург ). Затем вызывает себя ещё раз со сдвигом вниз - и заменяет уже Питер на Санкт-Петербург и т.д.

Сдвиг вниз на каждой итериации реализован стандартной экселевской функцией СМЕЩ (OFFSET) , у которой в данном случае три аргумента - исходный диапазон, сдвиг по строкам (1) и сдвиг по столбцам (0).

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

Вот и всё. Никаких хитрых макросов или запросов Power Query - вся задача решается одной функцией.

Рис. 1. Пример циклической ссылки

Рис. 2. Параметры Excel

В открывшемся окне «Параметры Excel» перейдите на вкладку Формулы и отметьте «Включить итеративные вычисления» (рис. 3). Помните, что эта опция включается для приложения Excel в целом (а не для одного файла), и будет действовать, пока вы ее не отключите.

Рис. 3. Включить итеративные вычисления

На этой же вкладе, можно выбрать, как будут вестись вычисления: автоматически или вручную. При автоматическом вычислении Excel сразу рассчитает конечный результат, при вычислениях, вручную, можно будет наблюдать результат каждой итерации (простым нажатием F9 запуская каждый новый цикл вычисления).

Решим уравнение третьей степени: х 3 – 4х 2 – 4х + 5 = 0 (рис. 4). Для решения этого уравнения (и любого другого уравнения совершенно произвольного вида) понадобится всего одна ячейка Excel.

Рис. 4. График функции f(x)

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

(1) x = x – f(x)/f’(x), где

f(x) – функция, задающая уравнение, корни которого мы ищем; f(x) = х 3 – 4х 2 – 4х + 5

f’(x) – производная нашей функции f(x); f’(x) = 3х 2 – 8х – 4; производные основных элементарных функций можно посмотреть здесь.

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

Итоговая рекуррентная формула имеет вид:

(2) х = x – (х 3 – 4х 2 – 4х + 5)/(3х 2 – 8х – 4)

Выберем любую ячейку на листе Excel (рис. 5; в нашем примере это ячейка G19), присвоим ей имя х, и введем в нее формулу:

Можно вместо х использовать адрес ячейки… но согласитесь, что имя х, смотрится привлекательнее; следующую формулу я ввел в ячейку G20:

Рис. 5. Рекуррентная формула: (а) для поименованной ячейки; (б) для обычного адреса ячейки

Как только мы введем формулу и нажмем Enter, в ячейке сразу же появится ответ – значение 0,77. Это значение соответствует одному из корней уравнения, а именно второму (см. график функции f(x) на рис. 4). Поскольку начальное приближение не задавалось, итерационный вычислительный процесс начинался со значения, по умолчанию хранимого в ячейке х и равного нулю. Как же получить остальные корни уравнения?

Для изменения стартового значения, с которого рекуррентная формула начинает свои итерации, предлагается использовать функцию ЕСЛИ: [1]

Здесь значение «-5» – начальное значение для рекуррентной формулы. Изменяя его, можно выйти на все корни уравнения:

Начальное значение	Корень уравнения
1	0,77
-5	-1,40
8	4,63

[1] Идея подсмотрена здесь

7 комментариев для “Excel. Использование циклических ссылок для решения уравнений итерационным способом”

Офигенный сайт!
И как всегда когда не нужно все находишь!
Блин у меня по экономическому моделированию в Excell курсовик был в институте, вот время помню кучу потерял а тут все в одном флаконе:)
Все равно инфа пригодится, даже очень!

Тамара, если Вы строите график на основе моих данных, откройте файл Excel; если Вы используете собственные данные, пришлите мне на mail Ваш файл, попробую помочь))

Спасибо заранее за беспокойство, вот такое уравнение у^3-20у^2-158у-420=0, если не трудно объясните пожалуйста как вы определяте предел в каких знчениях надо считать корни.

Рекурсивная процедура вызывает саму себя. как правило, это не самый эффективный способ написания кода Visual Basic.

В следующей процедуре используется рекурсия для вычисления факториала исходного аргумента.