Использование рекуррентных соотношений в электронной таблице Microsoft Excel Текст научной статьи по специальности «Математика»

Д.М. Златопольский

ИСПОЛЬЗОВАНИЕ РЕКУРРЕНТНЫХ СООТНОШЕНИЙ В ЭЛЕКТРОННОЙ ТАБЛИЦЕ MICROSOFT EXCEL

Златопольский Дмитрий Михайлович, кандидат технических наук, доцент кафедры информатики и прикладной математики Московского городского педагогического университета e-mail: zlatoworc.ru

И в математике, и в информатике часто встречаются последовательности чисел, в которых каждый последующий член выражается через предыдущие. Например, такими последовательностями являются прогрессии: арифметическая и геометрическая. В первой из них, как известно, каждый последующий член равен предыдущему, увеличенному на разность прогрессии: ai = er ] + d. во второй - предыдущему, умноженному на знаменатель прогрессии: а, = at_rk

Формулы, выражающие очередной член последовательности через один или несколько предыдущих членов, называют "рекуррентными соотношениями".

Как вычислить п-й член последовательности, заданной рекуррентным соотношением? Иногда для расчета п-то члена есть простая формула. Например, для арифметической прогрессии: ап = al+ d(n- 1). Чаще, однако, такой простой формулы нет или она неизвестна. В этом случае члены последовательности вычисляют по рекуррентному соотношению один за другим от i = 1 до

7 = П .

При использовании программ на языках программирования высокого уровня возможны два способа таких вычислений:

1) с использованием массивов;

2) без использования массивов.

А можно ли решать подобные задачи в электронной таблице Microsoft Excel? Можно, и делается это следующим образом.

Если для расчета п-то члена последовательности есть формула (например, для арифметической прогрессии она имеет вид: ап= ах+ d(n- 1)), то задача решается довольно просто:

А В С D

Расчет л-го члена

1 арифметической прогрессии

Задайте первый

2 член прогрессии:

3 Задайте разность

Задайте номер

искомого члена

4 прогрессии

5 Искомое значение равно:

6

Формула в ячейке В5: =В2 + В3*( В4-1).

А если такой формулы нет или она неизвестна? В этом случае, как отмечалось чуть выше, члены последовательности вычисляют по рекуррентному соотношению один за другим от / = 1 до /= п. Рассмотрим задачу: «Последовательность чисел 2, 3, 5, 9, 17, ... образуется по закону:

а1= 2; ак= 2ак1 - 1 ( к = 2. 3,К ). Определить а50 ». Здесь решение можно оформить следующим образом:

А В С

1 к А

2 1 2

3 2 3

4 3 5

5 4 9

50 49 28 1 474 976 710 657

51 50 56 2 949 953 421 313

52

Для расчетов в ячейку ВЗ вводится формула: =В2*2—1, которая затем распространяется (копируется) на ячейки диапазона В4:В51 (в ячейке В2 записывается известное значение 2). В ячейках с числами в столбце В установлен так называемый «числовой формат значений с разделителем в виде пробела между тройками чисел»1.

Однако такое оформление листа нерационально - для определения единственного значения используются 50 (!) ячеек (без учета ячеек в столбце А). Можно значительно сократить размеры используемой части листа.

1. Для начала запишем на лист следующие (вспомогательные) данные:

А В С

1 Для начала расчетов введите любой текст ->

2 Искомое значение =

3

В ячейке В2 попытаемся получить искомый результат путем использования всех «предыдущих» 49 значений. Смысл текста в ячейке В1 раскроем чуть позже.

2. В ячейку В2 введем формулу =В2*2—1 и ... - да, конечно, сразу же появится сообщение об ошибке:

Microsoft Excel

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

• Если циклическая ссылка создана случайно, нажмите кнопку "ОК", Будет открыта панель инструментов "Циклические ссылки" со справкой по ее использованию.

• Для получения сведений о циклических ссылках нажмите кнопку "Справка",

• Чтобы оставить формулу без изменений, нажмите кнопку "Отмена",

OK

Отмена

Справка

Смысл этого сообщения в том, что в заданной формуле имеется так называемая «циклическая ссылка» - результат расчетов в ячейке В2 зависит от значения в этой же ячейке. И хотя нам нужно именно это - ведь в этом по определению заключается смысл рекуррентных формул, Microsoft Excel такого не допускает (пока!).

В появившемся окне с сообщением об ошибке щелкаем на кнопке Отмена, а затем очищаем ячейку В2.

1 Для установки такого формата следует использовать пункт меню Формат, подпункт Ячейки, вкладку Число и в разделе Форматы выбрать из списка Числовые форматы строку Числовой, указав при этом в поле Число десятичных знаков значение 0 и поставив «галочку» рядом с надписью Разделитель групп разрядов().

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

Параметру

Международные Сохранение Проверка ошибок Орфография Безопасность

Вид Вычисления Правка Общие Переход Списки Диаграмма Цвет

Вычисления

(*) автоматически О вручную | Вычислить (F9) ]

О автоматически кроме таблиц kJ пересчет перед сохранением

Пересчет листа

□итерации!

Предельное число итераций: Параметры книги Относительная почетность: 1

[^1 обновлять удаленные ссылки 0 сохранять значения внешних связей

0 точность как на экране О допускать названия диапазонов

1 I система дат 1904

ОК | | Отмена |

- в пункте меню Сервис выбрать подпункт Параметры и в появившемся окне - вкладку Вычисления:

- поставить «галочку» (V) рядом с надписью итерации, а в поле Предельное число итераций записать значение 49 (напомним, что нам надо определить 50-й член последовательности), после чего щелкнуть на кнопке ОК.

4. В ячейке В2 запишем такую формулу: =ЕСЛИ(В1=""; 1; 2В2*2—1) - и теперь Microsoft Excel «промолчит» (©). Смысл этой формулы достаточно ясен: пока ячейка В1 пустая, в ячейке В2 должно отражаться начальное значение, равное 1, а после заполнения ячейки В1 в ячейке В2 начнут выполняться расчеты по рекуррентному соотношению (расчеты по заданной формуле будут проведены 49 раз).

5. В результате, если все оформлено правильно, на листе появится искомое значение:

Эффектно, не правда ли? При необходимости можно получить не один, а несколько членов последовательности. Для этого следует в поле Предельное число итераций (см. выше) задать значение 1. В этом случае после ввода текста в ячейку В1 в ячейке В2 появится значение второго члена последовательности - 3. Нажимая затем функциональную клавишу <F9>, можно получать в ячейке третий, четвертый и другие члены последовательности. Так удобно проводить расчеты, когда искомых значений немного. Если же нужно получить одно число, а итераций (расчетов по рекуррентной формуле) много, то лучше установить необходимое значение числа итераций в окне Параметры (см. выше), вызвав его с помощью меню.

Задания для самостоятельной работы учащихся

1. Подготовить лист для расчета 100-го члена последовательности, заданной рекуррентным соотношением ак = ak l + И к (к = 2, 3,К ) при ах= 1.

2. Получить первые 15 членов последовательности 2,5, 14,41, К (закон построения

последовательности установите самостоятельно).

3. Начав тренировки, лыжник в первый день пробежал 10 км. Каждый следующий день он увеличивал пробег на 10 % от пробега предыдущего дня. Определить, какой суммарный путь он пробежал за первые 7 дней тренировок.

4. В некотором году (назовем его условно первым) на участке в 100 гектар средняя урожайность ячменя составила 20 центнеров с гектара. После этого каждый год площадь участка увеличивалась на 5 %, а средняя урожайность на 2 %. Определить, какой урожай будет собран за первые шесть лет.

5. Найти 10-й член последовательности, начинающейся с числа 2,5, в которой каждый следующий член равен сумме обратных величин всех предыдущих.

А В С

1 Для начала расчетов введите любой текст -> Д

562

2 Искомое значение = 949 953 421 313

3

6. Найти 10-й член последовательности, начинающейся с числа 2,5, в которой каждый следующий член равен обратной сумме всех предыдущих членов.

Описанную методику можно также использовать также, когда очередной член последовательности зависит от двух и более предыдущих членов. Примером такой последовательности является последовательность чисел 1, 1, 2, 3, 5, 8, 13, ..., в которой каждый следующий член, начиная с третьего, равен сумме двух предыдущих (так называемая «последовательность Фибоначчи»), Для нее рекуррентное соотношение имеет вид: а) = сг 2 + сг 1, а1= 1, а2= 1. Вот как выглядит лист, оформленный для определения я-го члена последовательности Фибоначчи, в исходном состоянии:

А В С

1 Для начала расчетов введите любой текст ->

2 Член последовательности, предшествующий предыдущему 1

3 Предыдущий член последовательности 1

4 Очередной член последовательности 2

5

Формулы в ячейках: в ячейке В2: = ЕСЛИ(В1 ="";1 ;ВЗ); в ячейке ВЗ: = ЕСЛИ(В1="";1 ;В4); в ячейке В4: = В2+ВЗ.

Обратим внимание на то, что предельное число итераций, устанавливаемое с помощью меню (Сервис - Параметры, вкладка Вычисления), должно быть равно п - 3 , где п - номер искомого члена последовательности.

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

Задания для самостоятельной работы учащихся

1. Последовательность чисел , v2, v3, ... образуется по закону: vl=3, v2 = 5; vi = 2 45 - v; j 43 (7 = 3, 4, ...). Получить 20-й член этой последовательности.

2. Последовательность чисел vb v 2, v 3, ... образуется по закону: = v 2= 0 ; v = 1,5, /+ 1

Ц = „— V; _ j - V; 2 V; 35 i = 4. 5, ... Получить 14-й, 15-й, ..., 23-й члены этой

Г + 1

последовательности.

X С1

3. При положительном а рекуррентное соотношение х = —1— +- можно использовать

2 2х„ j

для вычисления \[а , т.к. элементы последовательности, построенной на таком соотношении, при увеличении i очень быстро приближаются к \[а [1]. Вот, например, как выглядит начало этой последовательности при а = 2 :

Xj = 1;

х2 — 1,5 ,

х3 = 1,4166666667; х4= 1,4142156863; х5 = 1,4142135624; х6 = 1,4142155624;

Подготовить лист для определения \[а для заданного а с погрешностью 0,0001 в виде:

А В С

Задайте число, из которого необходимо

1 извлечь корень ->

2 Корень из этого числа =

3

Принять Х\ = 1 (это может быть любое число).

1. Корнилов В. С. Как ЭВМ извлекает квадратный корень // Информатика (Газета для учителей информатики). - Москва, 2004. № 10 (442). - С.25-26.