CC BY
61
|шмущ
Златополъский Дмитрий Михайлович
РЕКУРРЕНТНЫЕ СООТНОШЕНИЯ В MICROSOFT EXCEL
В [1] публиковались материалы, посвященные последовательностям чисел, построенным на основе так называемых «рекуррентных соотношений» - формул, выражающих очередной член последовательности через один или несколько предыдущих членов. Например, для арифметической прогрессии такая формула: ai = ai _ i + d (каждый последующий член равен предыдущему, увеличенному на разность прогрессии). Были рассмотрены вопросы, связанные с вычислением n-го члена последовательности, заданной рекуррентным соотношением, средствами программирования. А можно ли решать подобные задачи в электронной таблице Microsoft Excel? Можно, и делается это следующим образом.
Если для расчета n-го члена последовательности есть формула (например, для
арифметической прогрессии она имеет вид: ап = а + й(п - 1)), то задача решается довольно просто (см. таблицу 1).
Формула в ячейке В5: =В2 + В3*( В4-1).
Чаще, однако, такой формулы нет или она неизвестна. В этом случае члены последовательности вычисляют по рекуррентному соотношению один за другим от г = 1 до г = п. Рассмотрим задачу: «Последовательность чисел 2, 3, 5, 9, 17, ... образуется по закону: а1 = 2; ак = 2 • ак - 1 - 1, (к = 2, 3, ...). Определить а50». Здесь решение можно оформить следующим образом1 (см. таблицу 2).
Для расчетов в ячейку В3 вводится формула: =В2*2-1, которая затем распространяется (копируется) на ячейки диапазона В4:В51 (в ячейке В2 записывается известное значение 2). В ячейках с числами в столбце В установлен так называ-
Таблица 1.
А В С D
1 Расчет л-го члена авиФметической поогоессии
2 Задайте первый член прогрессии:
3 Задайте разность прогрессии:
4 Задайте номер искомого члена прогрессии:
5 Искомое значение равно:
6
1 При необходимости можно так оформить лист, чтобы можно было получать значение любого члена последовательности (см. [2]).
СЦЕНАРИИ УРОКОВ
33
Таблица 2.
А В С
1 k a
2 1 2
3 2 3
4 3 5
5 4 9
50 49 281 474 976 710 657
51 50 562 949 953 421 313
52
Таблица 3.
1 Для начала расчетов введите любой текст ->
2 3 Искомое значение =
Таблица 4.
А В С
1 Для начала расчетов введите любой текст-> Д
2 Искомое значение = 562 949 953 421 313
3
Рисунок 1.
Рисунок 2.
емый «числовой формат значений с разделителем в виде пробела между тройками чисел»1.
Однако такое оформление листа нерационально - для определения единственного значения используются 50 (!) ячеек (без учета ячеек в столбце А). Можно значительно сократить размеры используемой части листа.
1. Для начала запишем на лист следующие (вспомогательные) данные (таблица 3).
В ячейке B2 попытаемся получить искомый результат путем использования всех «предыдущих» 49 значений. Смысл текста в ячейке B1 раскроем чуть позже.
2. В ячейку B2 введем формулу =B2*2-1 и ... - да, конечно, сразу же появится сообщение об ошибке (рисунок 1).
Смысл этого сообщения в том, что в заданной формуле имеется так называемая «циклическая ссылка» -результат расчетов в ячейке B2 зависит от значения в этой же ячейке. И хотя нам нужно именно это - ведь в этом, по определению, заключается смысл рекуррентных формул, Microsoft Excel такого не допускает (пока!).
В появившемся окне с сообщением об ошибке щелкаем на кнопке «Отмена», а затем очищаем ячейку B2.
3. Для того чтобы можно было использовать формулы с циклической ссылкой (рекуррентным соотношением), следует:
- в пункте меню Сервис выбрать подпункт Параметры и в появившемся окне - вкладку Вычисления (рисунок 2);
- поставить «галочку» рядом с надписью итерации, а в поле Предельное число итераций записать значение 49 (напомним, что нам надо определить 50-й член последовательности), после чего щелкнуть на кнопке «ОК».
4. В ячейке B2 запишем такую формулу: =ЕСЛИ(В1=""; 1; B2*2-1) - и
1 Для установки такого формата следует использовать пункт меню Формат, подпункт Ячейки, вкладку Число, и в разделе Форматы выбрать из списка Числовые форматы строку Числовой, указав при этом в поле Число десятичных знаков значение 0 и поставив «галочку» рядом с надписью Разделитель групп разрядов().
Рекуррентные соотношения в Microsoft Excel
теперь Microsoft Excel «промолчит». Смысл этой формулы достаточно ясен: пока ячейка B1 пустая, в ячейке B2 должно отражаться начальное значение, равное 1, а после заполнения ячейки B1 в ячейке B2 начнут выполняться расчеты по рекуррентному соотношению (расчеты по заданной формуле будут проведены 49 раз).
5. В результате, если все оформлено правильно, на листе появится искомое значение (таблица 4). Эффектно, не правда ли?
При необходимости можно получить не один, а несколько членов последовательности. Для этого следует в поле Предельное число итераций (см. рисунок выше) задать значение 1. В этом случае после ввода текста в ячейку B1 в ячейке B2 появится значение второго члена последовательности - 3. Нажимая затем функциональную клавишу <F9>, можно получать в ячейке третий, четвертый и другие члены последовательности. Так удобно проводить расчеты, когда искомых значений немного. Если же нужно получить одно число, а итераций (расчетов по рекуррентной формуле) много, то лучше установить необходимое значение числа итераций в окне Параметры, вызвав его с помощью меню, как это описано выше.
Задания для самостоятельной работы учащихся
1. Подготовить лист для расчета 100-го члена последовательности, заданной рекуррентным соотношением ak = ak _ г + 1/k (k = 2, 3, ...) при al = 1.
2. Получить первые 15 членов последовательности 2, 5, 14, 41, ... (закон по-
С
строения последовательности установите самостоятельно).
3. Начав тренировки, лыжник в первый день пробежал 10 км. Каждый следующий день он увеличивал пробег на 10% от пробега предыдущего дня. Определить, какой суммарный путь он пробежал за первые 7 дней тренировок.
4. В некотором году (назовем его условно первым) на участке в 100 гектар средняя урожайность ячменя составила 20 центнеров с гектара. После этого каждый год площадь участка увеличивалась на 5%, а средняя урожайность на 2%. Определить, какой урожай будет собран за первые шесть лет.
5. Найти 10-й член последовательности, начинающейся с числа 2,5, в которой каждый следующий член равен сумме обратных величин всех предыдущих.
6. Найти 10-й член последовательности, начинающейся с числа 2,5, в которой каждый следующий член равен обратной сумме всех предыдущих членов.
Описанную методику можно также использовать, когда очередной член последовательности зависит от двух и более предыдущих членов. Примером такой последовательности является последовательность чисел 1, 1, 2, 3, 5, 8, 13, ..., в которой каждый следующий член, начиная с третьего, равен сумме двух предыдущих (так называемая «последовательность Фибоначчи»). Для нее рекуррентное соотношение имеет вид: а1 = а1 _ 2 + а1 _ р а1 = 1, а2 = 1. Вот как выглядит лист, оформлен-
Таблица 5.
А В С
1 Для начала расчетов введите любой текст ->
Член последовательности,
предшествующий предыду- 1
2 щему
Предыдущий член последова- 1
3 тельности
Очередной член последова- 2
4 тельности
5
35
ныи для определения n-го члена последовательности Фибоначчи, в исходном состоянии (таблица 5).
Формулы в ячеИках:
- в ячеИке B2: =ЕСЛИ(В1="";1;Б3);
- в ячеИке B3: =ЕСЛИ(В1="";1;Б4);
- в ячеИке B4: =В2+Б3.
Обратим внимание на то, что предельное число итерациИ, устанавливаемое с помощью меню (Сервис ® Параметры, вкладка Вычисления), должно быть равно n - 3, где n - номер искомого члена последовательности.
Раз уж речь зашла о предельном числе итерациИ (расчетов по рекуррентной формуле), то заметим, что оно может быть ограничено не только числом - количеством итерациИ, но и так называемоИ «от-носительноИ погрешностью» расчетов. Смысл этого параметра состоит в том, что Excel будет выполнять расчеты до тех пор, пока рассчитываемое значение не изменится меньше, чем заданная погрешность. ТакоИ способ расчетов используется тогда, когда каждыИ очередноИ член последовательности отличается от предыдущего все меньше и меньше и известна по-
Таблица 6.
А В С
1 Задайте число, из которого необходимо извлечь корень ->
2 Корень из этого числа =
3
Литература
грешность измерении, которой можно пренебречь (и прекратить вычисления).
Задания для самостоятельной работы учащихся
1. Последовательность чисел у2, у3, ... образуется по закону: у1 = 3, v2=5,
= у-2' 5 - Уг-1 '3 (г = 3, 4, ...). Получить 20-И член этоИ последовательности.
2. Последовательность чисел у1, у2, V 3, ... образуется по закону: у1 = у2 = 0; V 3 = 1,5,
' + 1 -АС
,, г = 4, 5, ...
23-И члены
v. = -
.2
+1
Получить 14-И, 15-И, этоИ последовательности.
3. При положительном а рекуррентное соотношение
1 а
х + 2—
2 2 хг _1
можно использовать для вычисления 4а , так как элементы последовательности, по-строенноИ на таком соотношении, при увеличении г очень быстро приближаются к 4а [2]. Вот, например, как выглядит начало этоИ последовательности при а = 2: х1 = 1;
«2
х2 = 1,5;
х3 = 1,4166666667 х4 = 1,4142156863 х5 = 1,4142135624 х6 = 1,4142155624
Подготовить лист для определения 4а для заданного а с погрешностью 0,0001 в виде таблицы (см. таблицу 6).
Принять х1 = 1 (на самом деле, это может быть любое число).
1. Златополъский Д.М. Рекуррентные соотношения // Компьютерные инструменты в образовании, 2006, № 5.
2. Корнилов B.C. Как ЭВМ извлекает квадратныИ корень // «Информатика», 2004, № 10.
© Наши авторы, 2006 Our authors, 2006.
Златополъский Дмитрий Михайлович,
кандидат технических наук, доцент Московского городского педагогического университета.
