Как сделать геометрическую прогрессию в excel 2010?

Прогрессии и числовые последовательности в Excel

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

Перед построением последовательностей и различных прогрессий, как обычно, вспомним их детальные определения.
Числовая последовательность — это упорядоченный набор произвольных чисел a1, a2, a3, …, an, … .
Арифметической прогрессией называется такая числовая последовательность, в которой каждый член, начиная со второго, получается из предыдущего добавлением постоянной величины d (также называют шагом или разностью):


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


С определениями закончили, теперь самое время перейти от теории к практике.

Арифметическая прогрессия в Excel

Рассмотрим 2 способа задания прогрессии в Excel — с помощью стандартного инструмента Прогрессия и через формулы.
В первом случае на панели вкладок выбираем Главная -> Редактирование -> Заполнить -> Прогрессия:


Далее мы увидим диалоговое окно с настройками параметров:


В данных настройках мы можем выбрать дополнительные параметры, которые позволят нам более детально настроить и заполнить прогрессию в Excel:

  • Расположение — расположение заполнения (по столбцам или строкам);
  • Тип — тип (арифметическая, геометрическая, даты и автозаполнение);
  • Единицы — вид данных (при выборе даты в качестве типа);
  • Шаг — шаг (для арифметической) или знаменатель (для геометрической);
  • Автоматическое определение шага — автоматическое определение шага, если заданы несколько значений последовательности;
  • Предельное значение — ограничение по значению последнего элемента последовательности.

Разберем как сделать арифметическую прогрессию в Excel на конкретном примере.

Создадим набор чисел 3, 7, 11, … , то есть первый элемент равняется 3, а шаг равен 4.
Выделяем диапазон (к примеру, A1:J1) в котором мы хотим разместить набор чисел (диапазон можно и не выделять, однако в этом случае в настройках будет необходимо указать предельное значение), где в первой ячейке будет указан первый элемент (в нашем примере это 3 в ячейке A1), и указываем параметры (расположение, тип, шаг и т.д.):


В результате мы получим заполненный диапазон с заданным набором чисел:


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

Геометрическая прогрессия в Excel

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

Например, создадим набор чисел 4, 8, 16, … , то есть первое число равно 4, а каждое последующее в 2 раза больше предыдущего.
Также задаем начальный элемент (4 в ячейке A1), выделяем диапазон данных (например, A1:J1) и указываем параметры:


В итоге получаем:


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

Числовая последовательность в Excel

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

  • Непосредственное (прямое) перечисление элементов;
  • Через общую формулу n-го члена;
  • С помощью рекуррентного соотношения, которое выражает произвольный член через предыдущие.

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

Рассмотрим создание числовой последовательности на примере построения обратных чисел к натуральным, то есть набора чисел 1, 1/2, 1/3, … , в котором общая формула n-го члена принимает вид Fn=1/n.
Создадим дополнительный ряд в отдельной строчке, куда для удобства расчета поместим порядковые номера (1, 2, 3 и т.д.), на которые будут ссылаться формулы:

В варианте с рекуррентной формулой рассмотрим пример с набором чисел Фибоначчи, в котором первые два числа равны 1 и 1, а каждый последующее число равно сумме двух предыдущих.
В итоге произвольный член можно представить в виде рекуррентного соотношения Fn = Fn-1 + Fn-2 при n > 2.
Определяем начальные элементы (две единицы) в двух ячейках, а остальные задаем с помощью формулы:

БЛОК 4: Моделирование роста числовой последовательности в таблице

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как правильно сортировать и фильтровать ячейки таблиц Excel 2010?

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

Рис. 2.47. Группа Редактирование

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

Рис. 2.48. Исходная таблица (а) и отсортированные данные (б)

Выделили нужные ячейки, нажали кнопку — данные волшебным образом отсортировались. Если вы откроете список кнопки Сортировка и фильтр, то обнаружите там возможность настройки сортировки и включения фильтра. Если вы включите фильтр для выделенной вами области (я включила его для первого столбца таблицы, показанной на рис. 2.48), то увидите в заголовке столбца стрелочку. Щелкните на ней, чтобы открыть список (рис. 2.49).

Рис. 2.49. Список сортировки

Вы можете задать условие, и в выделенной области будут показываться только данные, которые удовлетворяют выбранному условию. Например, вы можете сделать так, чтобы показывались только ячейки, которые содержат или не содержат определенный текст, ячейки, числовое значение которых больше (меньше, равно) определенного значения. На рис. 2.50 приведена таблица, в которой я включила фильтр. Я попросила показывать мне во втором столбце только ячейки, в которых числовое значение меньше 500. Сразу видно, кто не сдал деньги в родительский комитет!

Рис. 2.50. Отфильтрованные данные

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

Как это сделать?

    Выделяете ячейку, содержимым которой хотите заполнить другие ячейки, вместе с диапазоном, на который вы хотите распространить ее содержимое. Например, я выделила ячейку А1 и диапазон, на который я хочу ее распространить (рис. 2.51).

Рис. 2.51. Выделяем диапазон

Рис. 2.52. Результат заполнения

Если вы на этапе 1 выделите не одну, а несколько ячеек, то с помощью кнопки Заполнить сможете распространить весь «рисунок», как написано во всплывающей подсказке. Я попробую объяснить на примере.

На рис. 2.53 вы видите, как выделенный диапазон (столбец D) заполнен Влево.

Рис. 2.53. Выделенный столбец D продублирован влево

Влево вы должны выбрать из списка кнопки Заполнение (рис. 2.54).

Рис. 2.54. Список кнопки Заполнение

В этом же списке есть замечательная строка Прогрессия. При ее выборе открывается одноименное окно (рис. 2.55).

Рис. 2.55. Окно Прогрессия

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

Рис. 2.56. Прогрессия

Арифметическая прогрессия — это когда последующее число получаете из предыдущего, если вы прибавите к нему определенное число. Это число — шаг прогрессии.
Геометрическая прогрессия — это когда последующее число вы получается из предыдущего, умножая его на определенное число (шаг прогрессии).

Первый столбец на рис. 2.56 заполнен арифметической прогрессией с шагом 1. Второй столбец — геометрической прогрессией с шагом 2. Третий столбец заполнен со следующими установками, которые нужно ввести в окне Прогрессия, показанном на рис. 2.55: тип — даты, единицы — месяц, шаг — 1. И хоть единицы заполнения — месяцы, столбец заполнен в днях. То есть к предыдущему значению столбца прибавляется количество дней в месяце. (Только почему-то каждый месяц по версии Exсel содержит 31 день.) Об автозаполнении я вам уже рассказывала. Как видите, это всего лишь частный случай Прогрессии.

Автозаполнение ячеек в Excel

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

Автоматическое заполнение ячеек также используют для продления последовательности чисел c заданным шагом (арифметическая прогрессия). Чтобы сделать список нечетных чисел, нужно в двух ячейках указать 1 и 3, затем выделить обе ячейки и протянуть вниз.

Эксель также умеет распознать числа среди текста. Так, легко создать перечень кварталов. Введем в ячейку «1 квартал» и протянем вниз.

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

Автозаполнение в Excel из списка данных

Ясно, что кроме дней недели и месяцев могут понадобиться другие списки. Допустим, часто приходится вводить перечень городов, где находятся сервисные центры компании: Минск, Гомель, Брест, Гродно, Витебск, Могилев, Москва, Санкт-Петербург, Воронеж, Ростов-на-Дону, Смоленск, Белгород. Вначале нужно создать и сохранить (в нужном порядке) полный список названий. Заходим в Файл – Параметры – Дополнительно – Общие – Изменить списки.

В следующем открывшемся окне видны те списки, которые существуют по умолчанию.

Как видно, их не много. Но легко добавить свой собственный. Можно воспользоваться окном справа, где либо через запятую, либо столбцом перечислить нужную последовательность. Однако быстрее будет импортировать, особенно, если данных много. Для этого предварительно где-нибудь на листе Excel создаем перечень названий, затем делаем на него ссылку и нажимаем Импорт.

Жмем ОК. Список создан, можно изпользовать для автозаполнения.

Помимо текстовых списков чаще приходится создавать последовательности чисел и дат. Один из вариантов был рассмотрен в начале статьи, но это примитивно. Есть более интересные приемы. Вначале нужно выделить одно или несколько первых значений серии, а также диапазон (вправо или вниз), куда будет продлена последовательность значений. Далее вызываем диалоговое окно прогрессии: Главная – Заполнить – Прогрессия.

В левой части окна с помощью переключателя задается направление построения последовательности: вниз (по строкам) или вправо (по столбцам).

Посередине выбирается нужный тип:

  • арифметическая прогрессия – каждое последующее значение изменяется на число, указанное в поле Шаг
  • геометрическая прогрессия – каждое последующее значение умножается на число, указанное в поле Шаг
  • даты – создает последовательность дат. При выборе этого типа активируются переключатели правее, где можно выбрать тип единицы измерения. Есть 4 варианта:
      • день – перечень календарных дат (с указанным ниже шагом)
      • рабочий день – последовательность рабочих дней (пропускаются выходные)
      • месяц – меняются только месяцы (число фиксируется, как в первой ячейке)
      • год – меняются только годы
  • автозаполнение – эта команда равносильная протягиванию с помощью левой кнопки мыши. То есть эксель сам определяет: то ли ему продолжить последовательность чисел, то ли продлить список. Если предварительно заполнить две ячейки значениями 2 и 4, то в других выделенных ячейках появится 6, 8 и т.д. Если предварительно заполнить больше ячеек, то Excel рассчитает приближение методом линейной регрессии, т.е. прогноз по прямой линии тренда (интереснейшая функция – подробнее см. ниже).

Нижняя часть окна Прогрессия служит для того, чтобы создать последовательность любой длины на основании конечного значения и шага. Например, нужно заполнить столбец последовательностью четных чисел от 2 до 1000. Мышкой протягивать не удобно. Поэтому предварительно нужно выделить только ячейку с одним первым значением. Далее в окне Прогрессия указываем Расположение, Шаг и Предельное значение.

Результатом будет заполненный столбец от 2 до 1000. Аналогичным образом можно сделать последовательность рабочих дней на год вперед (предельным значением нужно указать последнюю дату, например 31.12.2016). Возможность заполнять столбец (или строку) с указанием последнего значения очень полезная штука, т.к. избавляет от кучи лишних действий во время протягивания. На этом настройки автозаполнения заканчиваются. Идем далее.

Автозаполнение чисел с помощью мыши

Автозаполнение в Excel удобнее делать мышкой, у которой есть правая и левая кнопка. Понадобятся обе.

Допустим, нужно сделать порядковые номера чисел, начиная с 1. Обычно заполняют две ячейки числами 1 и 2, а далее левой кнопкой мыши протягивают арифметическую прогрессию. Можно сделать по-другому. Заполняем только одну ячейку с 1. Протягиваем ее и получим столбец с единицами. Далее открываем квадратик, который появляется сразу после протягивания в правом нижнем углу и выбираем Заполнить.

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

Этот трюк работает только с последовательностью чисел. В других ситуациях удерживание Ctrl приводит к копированию данных вместо автозаполнения.

Если при протягивании использовать правую кнопку мыши, то контекстное меню открывается сразу после отпускания кнопки.

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

Чтобы произвести автозаполнение до необходимого предельного значения (числа или даты), можно проделать следующий трюк. Берем правой кнопкой мыши за маркер чуть оттягиваем вниз, сразу возвращаем назад и отпускаем кнопку – открывается контекстное меню автозаполнения. Выбираем прогрессию. На этот раз выделена только одна ячейка, поэтому указываем направление, шаг, предельное значение и создаем нужную последовательность.

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

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

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

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

Если данные имеют ускоряющийся рост (как счет на депозите), то можно использовать экспоненциальную модель. Вновь, чтобы не мучиться с вычислениями, можно воспользоваться автозаполнением, выбрав Экспоненциальное приближение.

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

Автозаполнение дат с помощью мыши

Довольно часто требуется продлить список дат. Берем дату и тащим левой кнопкой мыши. Открываем квадратик и выбираем способ заполнения.

По рабочим дням – отличный вариант для бухгалтеров, HR и других специалистов, кто имеет дело с составлением различных планов. А вот другой пример. Допустим, платежи по графику наступают 15-го числа и в последний день каждого месяца. Укажем первые две даты, протянем вниз и заполним по месяцам (любой кнопкой мыши).

Обратите внимание, что 15-е число фиксируется, а последний день месяца меняется, чтобы всегда оставаться последним.

Используя правую кнопку мыши, можно воспользоваться настройками прогрессии. Например, сделать список рабочих дней до конца года. В перечне команд через правую кнопку есть еще Мгновенное заполнение. Эта функция появилась в Excel 2013. Используется для заполнения ячеек по образцу. Но об этом уже была статья, рекомендую ознакомиться. Также поможет сэкономить не один час работы.

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

Mobile apps — просто как 2х2!

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

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

Аналитические данные

Для иллюстрации разнообразных аналитических и научных данных довольно удобно использовать красивые диаграммы. Табличный редактор от Microsoft Excel 2010 предлагает огромное количество как стандартных так и довольно редких диаграмм и позволяет создавать сложные комбинации из уже существующих шаблонов.

Создать диаграмму в программе Excel 2010 довольно просто, так как, открывая эту программу, вам потребуется нажать всголишь пару кнопок. Остается всего лишь их заполнить таблицу с данными. Как же это сделать?Вы узнаете из статей нашего сайта.

E-mail и рассылки

Не секрет, что на сегодняшний день E-mail рассылка всё еще является эффективным рекламным средством, признанным во всей интернет среде, по соотношению цена качество. Охват массовой рассылки соизмерим, разве, что с рекламой по телефону

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

Мобильные устройства

Новости, обзоры и тесты портативной техники и мобильных устройств. Современные смартфоны, КПК и планшеты содержат в себе довольно взрослый функционал, аналогичный такому же у своих «старших братьев». Удаленное администрирование, браузеры с технологиями flash и java-script, синхронизация электронной почты, заметок, обмен различными файлами. Обо всем этом и не только подробнее на нашем сайте.

Прогрессии

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

Упражнение

Создадим простейшую арифметическую прогрессию на основе заданных параметров.

  • 1. Создайте новую книгу Excel.
  • 2. Введите в ячейку K10 значение 1.
  • 3. Выделите мышью ячейки K10. K20.
  • 4. Выберите команду Главная►Редактирование►Заполнить►Прогрессия. На экран будет выведено окно настройки параметров прогрессии (рис. 9.1).
  • 5. Как видите, в этом окне можно задавать разные типы прогрессий, не только арифметическую и геометрическую, но и линию времени. Догадливая электронная таблица, базируясь на выделенном нами фрагменте столбца, сама выбрала расположение прогрессии, установив переключатель По столбцам. Шаг, который предлагается нам, по умолчанию выбран равным 1, а тип прогрессии — Арифметическая. Оставьте все эти параметры без изменений и щелкните на кнопке OK. Выделенный нами фрагмент столбца заполнится значениями от 1 до 11, образовав арифметическую прогрессию с шагом 1 (каждое последующее значение больше предыдущего на единицу).
  • 6. Снова выберите команду Главная►Редактирование►Заполнить►• Прогрессия (ячейки K10. K20 должны быть выделены) и введите величину шага 2, оставив остальные параметры без изменения. Щелкните на кнопке OK и посмотрите на результат. Да, шаг прогрессии изменился, и ячейки заполнились цифрами от 1 до 21.
  • 7. Выделите ячейки K0. K10. Вновь вернитесь к команде Прогрессия и, не меняя ничего, щелкните на кнопке OK. Ничего и не происходит. И снова выберите команду Прогрессия, только на этот раз установив флажок Автоматическое определение шага. После щелчка на кнопке OK ячейки с K9 по K0 будут заполнены убывающими вверх значениями от 0 до –8. То есть электронная таблица «понимает», что направление вправо и вниз от ячейки — это направление положительных приращений, а влево и вверх — отрицательных.
  • 8. Но это далеко не все, что может сама «сообразить» электронная таблица. Продолжим наши исследования. В ячейке К10 у вас сейчас уже есть значение 1. Введите в ячейку L10 значение 2. Выделите ячейки K10. T10, затем выберите команду Прогрессия и, ничего не меняя в настройке, щелкните на кнопке OK. Выделенный диапазон заполнится значениями арифметической прогрессии от 1 до 10.
  • 9. В ячейке K11 у вас в данный момент находится значение 3. Введите в ячейку L11 значение 6, выделите диапазон K11. T11, выберите команду Прогрессия, установите флажок Автоматическое определение шага и щелкните на кнопке OK. Результат покажет вам, что Excel автоматически различает шаг, если он задан в двух последовательных ячейках, и выделенный диапазон заполнится арифметической прогрессией с шагом 2.
  • 10. Удалите в диапазоне K11. T11 все значения, кроме двух первых (3 и 6). Введите в ячейку M11 значение 7. Выделите диапазон

Рис. 9.1. Окно настройки параметров прогрессии

K11. T11, выберите команду Прогрессия, установите флажок Автоматическое определение шага и щелкните на кнопке OK. Вы увидите, что диапазон заполнен арифметической прогрессией с шагом 2,33333…. Таким образом, Excel автоматически определяет шаг не только для явных случаев, но и для случаев, когда в начале диапазона находится несколько ячеек с разным шагом. Тогда шаг прогрессии из этих значений находится способом наименьших квадратов с последующей линейной аппроксимацией.

  • 11. И еще несколько штрихов к вопросу о применении прогрессий. Введите в ячейку M13 значение 1. Удерживая нажатую клавишу Ctrl, щелкните последовательно на ячейках M15, M17, M19 и M21. Эти ячейки будут выделены. Затем выберите уже ставшую нам привычной команду Прогрессия, установите переключатель По столбцам и щелкните на кнопке OK. Результат покажет вам, что для ввода прогрессии диапазон ячеек не обязательно должен быть непрерывным.
  • 12. Выделите диапазон ячеек M13. T21, выберите команду Прогрессия, установите переключатель По строкам и щелкните на кнопке OK. Так вы получите пример того, что прогрессию можно вводить одновременно в нескольких диапазонах.
Читать еще:  Как сделать dll для excel?
Ссылка на основную публикацию
Adblock
detector