Как в экселе сделать арифметическую прогрессию

Как в экселе сделать арифметическую прогрессию

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

Задачи по этой теме школьного курса математики сводятся к двум: найти n-ый член прогрессии или найти сумму n первых членов прогрессии (Sn).

Формулы для их нахождения:

На практике в Excel это означает вот что: допустим, имеется арифметическая прогрессия, первый член которой a1=5, а разность арифметической прогрессии d=2. Требуется найти сумму первых четырех членов прогрессии (n=4).

Вызовем Microsoft Excel и заполним появившуюся таблицу. В ячейки А1 и В1 занесем названия столбцов — "а" и "b". В ячейку А2 занесем число 5, в ячейку А3 — формулу =А2+2 — это формула вычисления второго члена прогрессии. Затем размножим эту формулу вниз по столбцу А, используя маркер заполнения — т. е. протянем курсором за нижний правый угол ячейки А3. Если по условию задачи нужно найти не слишком много членов прогрессии, можно просто дотянуть маркер до нужного значения, в данном случае — до шестой строки. Так же просто можно найти и сумму нескольких первых членов арифметической прогрессии. Достаточно в ячейку В2 поместить то же число 5 (сумма одного члена равна ему самому), а в ячейку В3 — формулу =В2+А3 и потянуть эту ячейку вниз до нужной суммы. В ячейках А6 и В6 получим искомые результаты — 13 и 45.

Хуже обстоит дело в следующем примере. Допустим, есть прогрессия, в которой a1=0, d= -1,4. Требуется найти сумму 202-х членов. Не хочется тянуть активную ячейку вниз на 202 клетки — удобнее воспользоваться формулами.

В первую строку снова вносим названия столбцов a1, d, n. Затем следует занести и сохранить формулы: в ячейку С3 — формулу n-го члена прогрессии =A2+(C2-1)*B2. В ячейку С4 — формулу суммы n членов арифметической прогрессии: =(A2+C3)/2*C2. И сохранить этот файл.

Теперь можно внести переменные — a1, d, n. В данном примере это число 0 в ячейку А2, число -1,4 — в ячейку B2 и число 202 — в ячейку C2.

Для задач, относящихся к арифметической прогрессии, решение существует всегда, ограничений ни на одну из переменных нет.

Результат будет выглядеть так (см. рис. 2).

Для усложнения задачи предполагается, что для последней прогрессии нужно найти сумму членов со 101 по 202. Сумма S202 = -28421,4 уже есть. Теперь необходимо найти сумму 100 первых членов и вычесть ее из суммы 202-х членов прогрессии. Найти решение 100 первых членов прогрессии (S100) проще простого — достаточно указать первым членом число 100 в ячейке С2: S100= -6930. После вычитания из суммы 202-х членов прогрессии суммы 100 первых ее членов получим сумму членов прогрессии с 101-го по 202-й (28421,4-(-6930) = -21491,4).

Геометрическая прогрессия — последовательность чисел, каждое следующее из которых получается из предыдущего умножением на постоянное число q (при этом q?1), называемое знаменателем геометрической прогрессии.

Формула нахождения n-го члена геометрической прогрессии aq=a1q-1. Формула суммы n членов геометрической прогрессии Sn=(a1-an q-1)/(1-q).

Перейдем к решению задачи нахождения n-го члена и суммы n членов геометрической прогрессии в Excel. Например, есть геометрическая прогрессия, где a1=10, q=0,3, n=4. Вызовем Excel и заполним таблицу: в первую строку занесем названия столбцов a1, q, n. В ячейку А2 запишем число 10, в ячейку В2 запишем 0,3, в С2 запишем 4. В ячейку С3 занесем формулу =A2*СТЕПЕНЬ (B2;C2-1), в ячейку С4 — формулу =(A2-C3*B2)/(1-B2). В результате вычислений получим a4=0,27, S4=14,17.

Поскольку знаменатель дроби равен (1-q), то, чтобы знаменатель не обратился в ноль, условием решения задачи является q не равное 1. Впрочем, q?1 по определению геометрической прогрессии. Других ограничений на переменные в этой задаче нет — они могут быть хоть отрицательными, хоть нулевыми.

Существует бесконечная геометрическая прогрессия (0

Возьмем, например, a1=3, q=0,25. В первой строке названия столбцов будут a1, q, S. Заполнив ячейки таблицы Excel: А2=3, В2=0,25, С2 — формула =A2/(1-B2), получим следующий результат (см. рис. 4).

Этим исчерпывается тема решения задач, связанных с геометрической прогрессией в Excel.

Моделирование в таблице роста числовой последовательности

Проработав задания этого блока на персональном компьютере, вы:

  • освоите несколько способов заполнения блока таблицы числовой последовательностью с заданным законом роста;
  • закрепите навыки построения и копирования расчетных формул;
  • научитесь использовать формулы массива и таблицы подстановки.
Читайте также:  Сколько в среднем потребляет электроэнергии однокомнатная квартира

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

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

Разность арифметической прогрессии при этом вычисляется автоматически и используется программой как постоянный шаг для заполнения блока числовыми константами. Знак шага прогрессии определяется по направлению движения мыши: от меньшего числа в сторону большего – шаг положительный, то есть рост, а от большего к меньшему – снижение. Этот способ обычно применяется для быстрого получения последовательности порядковых номеров.

Постройте таблицу порядковых номеров дней високосного года по образцу табл. 2.

  1. методом копирования формул
  2. методом автозаполнения.

Сколько дней между датами 23/02 и 08/03?

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

Рис. 26. Диалоговое окно команды П равка Заполн и ть П рогрессия.

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

Правила начисления процентов также могут быть выражены рекуррентно. Рассмотрим динамику роста денежного вклада при простых, и при сложных процентах. Выбор варианта рекуррентной формулы, с помощью которой можно представить правило начисления процентов, дающее в результате одну и ту же числовую последовательность (см. рис. 28), зависит от навыков пользователя Excel, глубины его абстрактного мышления, и потребностей решаемой задачи. Обзор простейших альтернатив организации вычислений приводится в табл. 10.

Таблица 10
Варианты рекуррентного кодирования правил начисления процентов

Рост по правилу

Процентная ставка выражена

простых процентов

сложных процентов

числовой константой 0,15

=C3+0,15*C$3

=D3+0,15*D3 или =1,15*D3

абсолютной ссылкой B$1

=C3+B$1*C$3

=D3+B$1*D3

именем ячейки ставка

=C3+ставка*C$3

=(1+ставка)*D3

Рис. 27. Модели роста стоимости вклада.

Пример расположения данных на рабочем листе и кодирования необходимых вычислений приведен на рис. 27 в режиме вывода формул. Найденные по этим формулам числовые значения даны на рис. 28.

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

Рис. 28. Числовые значения формул, предложенных на рис. 27.

Пример . Текущая стоимость потока будущих затрат.

Текущая стоимость представляет собой дисконтированную сумму будущих затрат. Дисконтирование производится делением на процентный множитель величина которого зависит от числа процентных периодов, разделяющих моменты осуществления затрат и текущей оценки. Каждый следующий процентный множитель легко рекуррентно получать из предыдущего умножением на знаменатель геометрической прогрессии (1+ R ), где ставка за один период R= 10%.

Рис. 29. Рекуррентная модель вычисления текущей стоимости потока будущих платежей.

Используя данные рис. 30 и рис. 24 как образец расчета средней ставки простых процентов, взвешенной по периодам действия, найдите среднее значение ставки рефинансирования ЦБ РФ за указанный период.

Рис. 30. Динамика ставки рефинансирования ЦБ РФ в 1996–1998 гг.

Пример . Зависимость срока удвоения вклада при начислении сложных процентов от ставки.

Известен следующий упрощенный способ вычисления срока удвоения вклада при начислении сложных процентов по постоянной ставке R: число 72 делится на число сотых долей в процентной ставке и получается срок удвоения ("правило 72" ).

Рис.31. Сравнение точной формулы срока удвоения вклада по сложным процентам и "правила 72".

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

Пример . Последовательность чисел Фибоначчи 6 задается двумя своими первыми членами, равными единице, и правилом: каждое новое число равно сумме двух предыдущих.

Читайте также:  Видео стало черно белым как исправить

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

Рис. 32. Табличная формула, моделирующая последовательность чисел Фибоначчи.

Пример . Число возможных перестановок из N элементов равно произведению всех натуральных чисел от 1 до N. Это произведение называется N-факториал и обозначается N! = 1*2*. *N.

По определению, 1!=1. Затем верно рекуррентное правило (N + 1)! = (N + 1) * N!

Рис. 33. Рекуррентная табличная модель расчета N!

Пример . Инвестор вносит сумму 5 млн руб на счет с начислением сложных процентов по ставке 8% за период., и затем дважды в конце каждого расчетного периода добавляет на счет столько же.

Какую сумму удастся таким образом нарастить за 3 периода?

Таблица 11
Будущая стоимость потока платежей (млн руб.)

Период

Сумма вклада с процентами в конце периода

Новый платеж в конце периода

Переходящий остаток на счете в конце периода

0,000 =

5,000 = 0,000 + 5,000

5,400 =

10,400 = 5,400 + 5,000

11,232 =

16,232 = 11,232 + 5,000

При какой процентной ставке те же три платежа по 5 млн руб. каждый, регулярно вносимые на счет в конце периода, позволят накопить сумму не 16,232 млн руб., а 20,000 млн руб.? Чтобы ответить на этот вопрос нужно составить уравнение и выразить из него искомую ставку R .

Рис. 34. График левой части квадратного уравнения.

В данном примере уравнение является квадратным (см. рис. 34) и имеет два решения. Значение искомой ставки процентов доставляет положительный правый корень.

Если многочлен в левой части такого уравнения имеет высокий порядок N (число элементов потока платежей), то решений может быть несколько, а единой формулы для нахождения корней не существует. Поэтому ставку подбирают итеративно. Подбор нулевого значения левой части дает ответ R = 56,16%.

Пользователю Excel для нахождения этого ответа достаточно вызвать процедуру Сервис, Подбор Параметра (см. рис. 35). Как хорошо видно на рис. 34, правый корень находится между значениями 0,50 и 0,75. В свободную клетку таблицы, например B24, помещаем начальное приближение 0,60.

В соседнюю клетку C24 вводим формулу левой части уравнения, где место неизвестной ставки R занимает ссылка на влияющую клетку B24. Процедура Подбор Параметра (Goal Seek) итеративным подбором установит (Set cell) в зависимой ячейке (клетка C24 с расчетной формулой левой части уравнения) искомое значение (To value) 0, изменяя значение (By changing cell) влияющей ячейки (клетка B24).

Рис.35 . Диалоговое окно процедуры Подбор параметра в оригинальной версии Excel.

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

В левой части окна на рис.36 использована формула массива (со ссылкой на интервал A2:A9 значений параметра N – срок), а справа – обычная техника копирования формул.

Рис. 36. Модели организации табличных вычислений по общей формуле члена прогрессии.

Дальнейшим развитием идеи организации на рабочем листе блоков данных, табулирующих влияние частного изменения параметра на значение исследуемой функции, является процедура создания таблиц "анализа чувствительности" (англ. What-If Analysis), инициируемая командой Д анные, Т аблица подстановки.

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

Рис. 37 . Определение таблицы подстановки, данное в Справочной системе Microsoft Excel.

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

До вызова этой процедуры необходимо, чтобы уже был заполнен значениями параметра интервал смежных клеток столбца (в блок B3:B10 записаны номерами периодов от 0 до 7), и на одну строку выше первого значения параметра в столбцах правее располагаются формулы табулируемых функций, ссылающиеся на ячейку ввода..

Читайте также:  Свара карточная игра правила

а) режим вывода значений

б) режим вывода формул

Рис. 38. Внешний вид таблицы подстановки значений одного параметра в две формулы.

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

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

Пусть, например, ячейкой ввода будет F8. В клетку C2 введем знакомую формулу простых процентов =1+0,15* F8, а в клетку D2 – сложных =1,15^ F8. Далее нужно выделить всю область таблицы подстановки – блок B2:D10 и применить команду Д анные Т аблица подстановки.

При необходимости изменить расчетную формулу, например поставить 20% вместо 15%, теперь достаточно отредактировать выражение только в формуле верхней строки таблицы подстановки.

Заполните блок клеток рабочего листа электронной таблицы колонками значений и постройте по ним диаграммы роста стоимости одной денежной единицы, вложенной на срок 18 месяцев по ставке 24% годовых при начислении процентов:

  1. сложных ежемесячно;
  2. простых ежемесячно;
  3. простых ежемесячно с реинвестированием каждые полгода;
  4. сложных ежеквартально;
  5. сложных за полный год и простых за дробную часть года.

Постройте искомые таблицы значений будущая стоимость разными методами:

  1. по рекуррентной формуле;
  2. по общей формуле копированием;
  3. по общей формуле массива;
  4. по общей формуле как таблицу подстановки;
  5. заполнением блока прогрессией чисел.

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

Ранее было рассмотрено построение таблицы дисконтирующих множителей PVIF(R,N) для 4% R N =1, 2, 3 методом копирования формул. Например, в клетку B3 помещается выражение =1/(1+B$2)^$A3, которое кодирует правило построения таблицы: "считаем число, обратное сумме единицы и ставки процента, возведенной в степень, заданной как срок; причем значение процента берем всегда из самой верхней строки, а срок – всегда из крайней левой колонки".

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

Начинать изготовление таблицы значений множителей наращения, показанной на рис.39, нужно с заполнения части столбца A3:A5 рядом значений срока и части строки B2:H2 последовательностью значений ставки сложных процентов. После этого в левый верхний угол области будущей таблицы подстановки (ячейка A2) вводится расчетная формула, в которой в роли ссылок на параметры выступают адреса двух ячеек ввода, внешних по отношению к области таблицы. В качестве ячейки, куда будет подставляться процентная ставка, выбрана A1, а ссылка на срок заменяется обращением к ячейке E1. Расчетная формула в клетке A2 имеет вид =(1+A1)^E1. Затем необходимо выделить блок ячеек A2:H5 и дать команду Д анные Т аблица подстановки.

Рис. 39. Множители наращения сложных процентов FVIF(R,N).

Рис. 40. Построение таблицы данных с двумя параметрами.

В первое поле ввода диалогового окна рис.41, щелчком мыши помещена абсолютная ссылка на ячейку A1, занимающую в производящей формуле таблица параметр R – процентную ставку, значения которой расположены над таблицей горизонтально , то есть меняются по столбцам внутри одной строки.

Во втором поле ввода нужно дать ссылку на ячейку E1, куда будут подставляться значения параметра N – срок, интервал изменения которого задан слева вертикально , то есть в одном столбце по строкам.

6 Итальянский монах и математик Леонардо из Пизы, более известный под именем Фибоначчи (сын Боначчи). Фибоначчи познакомил Европу с достижениями индийской (арабской) математики. Именно он ввел в обиход арабские цифры и позиционную десятичную систему счисления, чем положил начало эпохи коммерческой арифметики в средние века. См.: Соколов Я.В. Бухгалтерский учет: от истоков до наших дней. М., 1996.

Ссылка на основную публикацию
Как без пульта включить режим av
Современные технологии поражают спектром своего действия. Они активно внедряются во все сферы человеческой деятельности и упрощают жизнь. В нашей повседневной...
Инструкция как сделать слияние документов libreoffice
Откройте диалоговое окно Слияние, которое облегчает печать и сохранение стандартных писем. Вставьте в текстовый документ по крайней мере одно поле...
Использование абсолютных картинок в управляемых формах
Problem-solving in IT Thursday, July 16, 2015 1С управляемые формы и отображение картинки. В 1С работают извращенцы, полные, вот мне...
Как в ворде сделать высоту букв
Собственно как изменить сабж и возможно ли? Именно высоту, а не размер, чтобы буквы были вытянутыми. ← → sBodun (...
Adblock detector