Выполнение вычислений по формулам. Создание формул в Excel

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

Введение

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

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

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

Выполнение базовых арифметических операций

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

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

Например, давайте представим, что нам необходимо сложить два числа - «12» и «7». Установите курсор мыши в любую ячейку и напечатайте следующее выражение: «=12+7». По окончании ввода нажмите клавишу «Enter» и в ячейке отобразится результат вычисления - «19».

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

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

Чтобы это исправить, выделите ячейку с числом, у которого необходимо изменить количество десятичных знаков после запятой и на вкладке Главная в группе Число выберите команду Уменьшить разрядность . Каждое нажатие на эту кнопку убирает один знак.

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

Составление формул

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

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

Чтобы посчитать суммарный расход за январь в ячейке B7 можно написать следующее выражение: «=18250+5100+6250+2500+3300» и нажать Enter, после чего вы увидите результат вычисления. Это является примером применения простейшей формулы, составление которой ничем не отличается от вычислений на калькуляторе. Разве что знак равно ставится вначале выражения, а не в конце.

А теперь представьте, что при указании значений одной или нескольких статей расходов вы допустили ошибку. В этом случае, вам придется скорректировать не только данные в ячейках с указанием расходов, но и формулу вычисления суммарных трат. Конечно, это очень неудобно и поэтому в Excel при составлении формул часто используются не конкретные числовые значения, а адреса и диапазоны ячеек .

С учетом этого давайте изменим нашу формулу вычисления суммарных ежемесячных расходов.

В ячейку B7, введите знак равно (=) и… вместо того, чтобы вручную вбивать значение клетки B2, щелкните по ней левой кнопкой мыши. После этого вокруг ячейки появится пунктирная выделительная рамка, которая показывает, что ее значение попало в формулу. Теперь введите знак «+» и щелкните по ячейке B3. Далее проделайте тоже самое с ячейками B4, B5 и B6, а затем нажмите клавишу ВВОД (Enter), после чего появится то же значение суммы, что и в первом случае.

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

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

Установите курсор на пустой клетке сбоку нужной строки (в нашем случае это H2). Затем нажмите кнопку Сумма на закладке Главная в группе Редактирование . Теперь, вернемся к таблице и посмотрим, что же произошло.

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

Теперь посмотрим на саму формулу. Как и раньше, вначале идет знак равенства, но на этот раз за ним следует функция «СУММ» - заранее определенная формула, которая выполнит сложение значений указанных ячеек. Сразу за функцией идут скобки расположенные вокруг адресов клеток, значения которых нужно просуммировать, называемые аргументом формулы . Обратите внимание, что в формуле не указаны все адреса суммируемых ячеек, а лишь первой и последней. Двоеточие между ними обозначает, что указан диапазон клеток от B2 до G2.

После нажатия Enter, в выбранной ячейке появится результат, но на этом возможности кнопки Сумма не заканчиваются. Щелкните на стрелочку рядом с ней и откроется список, содержащий функции для вычисления средних значений (Среднее), количества введенных данных (Число), максимальных (Максимум) и минимальных (Минимум) значений.

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

Автозаполнение

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

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

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

На экране появится всплывающая подсказка, которая сообщит вам то значение, которое программа собирается вставить в следующую клетку. В нашем случае это «Февраль». По мере перемещения маркера вниз она будет меняться на названия других месяцев, что поможет вам понять, где нужно остановиться. После того как кнопка будет отпущена, список заполнится автоматически.

Конечно, Excel не всегда верно «понимает», как нужно заполнить последующие клетки, так как последовательности могут быть довольно разнообразными. Представим себе, что нам необходимо заполнить строку четными числовыми значениями: 2, 4, 6, 8 и так далее. Если мы введем число «2» и попробуем переместить маркер автозаполнения вправо, то окажется, что программа предлагает, как в следующую, так и в другие ячейки вставить опять значение «2».

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

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

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

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

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

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

Выделяем диапазон, который следует заполнить, начиная с ячейки с уже введенными данными. Затем на вкладке Главная в группе Редактирование нажимаем кнопку Заполнить и выбираем направление заполнения.

Добавление строк, столбцов и объединение ячеек

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

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

Щелкните в любой ячейке второй строки правой кнопкой мыши и в открывшемся меню выберите команду Вставить… , а затем в окне - Добавить строку .

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

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

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

Кстати, если в итоге строка или столбец после вставки оказались на ненужном месте, их легко можно удалить. Щелкните правой кнопкой мыши на любой ячейке, принадлежащей удаляемому объекту и в открывшемся меню выберите команду Удалить . В завершении укажите, что именно необходимо удалить: строку, столбец или отдельную ячейку.

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

В нашем случае нам необходимо вставить пять новых строк в верхнюю часть таблицы сразу после шапки. Для этого можно повторить операцию добавления несколько раз, а можно выполнив ее единожды использовать клавишу «F4», которая повторяет самую последнюю операцию.

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

Белые неформатированные ряды в таблице мы оставили специально, что бы отделить доходную, расходную и итоговую часть друг от друга, написав в них соответствующие заголовки. Но перед тем как это сделать, мы изучим еще одну операцию в Excel - объединение ячеек .

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

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

Вернемся к нашей таблице. Для того, что бы написать заголовки в белых строчках нам понадобится лишь одна ячейка, в то время как сейчас они состоят из восьми. Давайте исправим это. Выделите все восемь ячеек второго ряда таблицы и на вкладке Главная в группе Выравнивание щелкните на кнопку Объединить и поместить в центре .

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

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

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

Заключение

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

Теперь переведем эти расчеты в формулы понятные Excel. Для января (ячейки B14) формула очень проста и будет выглядеть так: «=B5-B12». А вот для ячейки С14 (февраль) выражение можно записать двумя разными способами: «=(B5-B12)+(C5-C12)» или «=B14+C5-C12». В первом случае мы опять проводим расчет баланса предыдущего месяца и затем прибавляем к нему баланс текущего, а во втором в формулу включается уже рассчитанный результат по предыдущему месяцу. Конечно, использование второго варианта для построения формулы в нашем случае гораздо предпочтительнее. Ведь если следовать логике первого варианта, то в выражении для мартовского расчета будет фигурировать уже 6 адресов ячеек, в апреле - 8, в мае - 10 и так далее, а при использовании второго варианта их всегда будет три.

Для заполнения оставшихся ячеек с D14 по G14 применим возможность их автоматического заполнения, так же как мы это делали в случае с суммами.

Кстати, для проверки значения итоговых накоплений на июнь, находящегося в клетке G14, в ячейке H14 можно вывести разницу между общей суммой ежемесячных доходов (H5) и ежемесячных расходов (H12). Как вы понимаете, они должны быть равны.

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

А вот и наша итоговая таблица с выполненными расчётами:

Теперь, при желании, вы уже самостоятельно сможете продолжать ее наполнение, вставляя как дополнительные статьи расходов или доходов (строки), так и добавляя новые месяцы (столбцы).

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

Формулы в Excel - это его основная суть, то, ради чего и была создана эта программа компанией Microsoft. Формулы позволяют произвести расчеты значений ячеек на основе данных других ячеек, причем если исходные данные поменяются, то результат вычислений в ячейке, где стоит формула пересчитается автоматически!

Создание формул в Excel

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

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

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

Фомулы в Excel могут содержать арифметические операции (сложение +, вычитание -, умножение *, деление /), координаты ячеек исходных данных (как по отдельности, так и диапазон) и функции вычисления.

Рассмотрим формулу для суммы чисел в примере выше:

СУММ(A2;B2)

Каждая формула начинается со знака «равно». Если Вы хотите добавить в ячейку формулу, написав ее вручную, то именно этот знак следует написать первым.

Далее в примере идет функция СУММ, которая означает что необходимо произвести суммирование некоторых данных, а уже в скобках у функции, разделенные точкой с запятой, указываются некоторые аргументы, в данном случае координаты ячеек (A2 и B2), значения которых необходимо сложить и поместить результат в ту ячейку, где написана формула. Если бы Вам требовалось сложить три ячейки, то можно было бы написать три аргумента у функции СУММ, разделяя их точкой с запятой, например:

СУММ(А4;B4;C4)

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

СУММ(B2:B7)

Диапазон ячеек в Экселе указывается с помощью координат первой и последней ячеек, разделенных знаком «двоеточие». В данном примере производится сложение значений ячеек, начиная с ячейки B2 до ячейки B7.

Функции в формулах можно соединять и комбинировать как Вам необходимо для получение требуемого результата. Например, стоит задача сложить три числа и в зависимости от того, меньше ли результат числа 100 или больше, домножить сумму на коэффициент 1.2 или 1.3. Решить задачу поможет следующая формула:

ЕСЛИ(СУММ(А2:С2)

Разберем решение задачи подробнее. Использовалось две функции ЕСЛИ и СУММ. Функция ЕСЛИ всегда имеет три аргумента: первый - условие, второй - действие в случае, если условие верно, третий - действие в случае, если условие неверно. Напоминаем, что аргументы разделяются знаком «точка с запятой».

ЕСЛИ(условие; верно; неверно)

В качестве условия указано, что сумма диапазона ячеек A2:C2 меньше 100. Если при расчете, условие выполнится и сумма ячеек диапазона будет равна, например, 98, то Эксель выполнить действие указанное во втором аргументе функции ЕСЛИ, т.е. СУММ(А2:С2)*1,2. В случае же, если сумма превысит число 100, то выполнится уже действие в третьем аргументе функции ЕСЛИ, т.е. СУММ(А2:С2)*1,3.

Встроенные функции в Excel

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

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

Чтобы вставить функцию в Excel 2007 выберите в главном меню пункт «Формулы» и кликните на значок «Вставить функцию», либо нажмите на клавиатуре комбинацию клавиш Shift+F3.

В Excel 2003 функция вставляется через меню «Вставка»->«Функция». Так же работает и комбинация клавиш Shift+F3.

В ячейке на которой стоял курсор появится знак равенства, а поверх листа отобразится окно «Мастер функций».

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

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

В окне аргументов имеются поля с названиями «Число 1», «Число 2» и т.д. Их необходимо заполнить координатами ячеек (либо диапазонами) в которых требуется взять данные. Заполнять можно вручную, но гораздо удобнее нажать в конце поля на значок таблицы для того, чтобы указать исходную ячейку или диапазон.

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

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

Заполнив все аргументы, Вы уже можете предварительно посмотреть результат расчета полученной формулы. Чтобы он появился в ячейке на листе, нажмите кнопку «OK». В рассмотренном примере в ячейку D2 помещено произведение чисел в ячейках B2 и C2.

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

Приближенные вычисления с помощью дифференциала

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

Кроме того, на странице присутствуют формулы нахождения абсолютной и относительной погрешность вычислений. Материал очень полезный, поскольку погрешности приходится рассчитывать и в других задачах. Физики, где ваши аплодисменты? =)

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

Практикум состоит из двух частей:

– Приближенные вычисления с помощью дифференциала функции одной переменной.

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

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

Приближенные вычисления
с помощью дифференциала функции одной переменной

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

В первом параграфе рулит функция одной переменной. Как все знают, она обозначается через или через . Для данной задачи намного удобнее использовать второе обозначение. Сразу перейдем к популярному примеру, который часто встречается на практике:

Пример 1

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

Начинаем разбираться, здесь всё просто!

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

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

В качестве подбираем «хорошее» значение, чтобы корень извлекался нацело . Естественно, это значение должно быть как можно ближе к 67. В данном случае: . Действительно: .

Примечание: Когда с подбором всё равно возникает затруднение, просто посмотрите на скалькулированное значение (в данном случае ), возьмите ближайшую целую часть (в данном случае 4) и возведите её нужную в степень (в данном случае ). В результате и будет выполнен нужный подбор: .

Если , то приращение аргумента: .

Итак, число 67 представлено в виде суммы

Сначала вычислим значение функции в точке . Собственно, это уже сделано ранее:

Дифференциал в точке находится по формуле:
– тоже можете переписать к себе в тетрадь.

Из формулы следует, что нужно взять первую производную:

И найти её значение в точке :

Таким образом:

Всё готово! Согласно формуле :

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

Ответ:

Пример 2

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

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

У некоторых, возможно, возник вопрос, зачем нужна эта задача, если можно всё спокойно и более точно подсчитать на калькуляторе? Согласен, задача глупая и наивная. Но попытаюсь немного её оправдать. Во-первых, задание иллюстрирует смысл дифференциала функции. Во-вторых, в древние времена, калькулятор был чем-то вроде личного вертолета в наше время. Сам видел, как из местного политехнического института году где-то в 1985-86 выбросили компьютер размером с комнату (со всего города сбежались радиолюбители с отвертками, и через пару часов от агрегата остался только корпус). Антиквариат водился и у нас на физмате, правда, размером поменьше – где-то с парту. Вот так вот и мучились наши предки с методами приближенных вычислений. Конная повозка – тоже транспорт.

Так или иначе, задача осталась в стандартном курсе высшей математики, и решать её придётся. Это основной ответ на ваш вопрос =)

Пример 3

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

Фактически то же самое задание, его запросто можно переформулировать так: «Вычислить приближенное значение с помощью дифференциала»

Решение: Используем знакомую формулу:
В данном случае уже дана готовая функция: . Ещё раз обращаю внимание, что для обозначения функции вместо «игрека» удобнее использовать .

Значение необходимо представить в виде . Ну, тут легче, мы видим, что число 1,97 очень близко к «двойке», поэтому напрашивается . И, следовательно: .

Используя формулу , вычислим дифференциал в этой же точке.

Находим первую производную:

И её значение в точке :

Таким образом, дифференциал в точке:

В результате, по формуле :

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

Абсолютная и относительная погрешность вычислений

Абсолютная погрешность вычислений находится по формуле:

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

Относительная погрешность вычислений находится по формуле:
, или, то же самое:

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

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

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

Вычислим абсолютную погрешность:

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

Ответ: , абсолютная погрешность вычислений , относительная погрешность вычислений

Следующий пример для самостоятельного решения:

Пример 4

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

Примерный образец чистового оформления и ответ в конце урока.

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

Но для страждущих читателей я раскопал небольшой пример с арксинусом:

Пример 5

Вычислить приближенно с помощью дифференциала значение функции в точке

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

Пример 6

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

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

Алгоритм решения принципиально сохраняется, то есть необходимо, как и в предыдущих примерах, применить формулу

Записываем очевидную функцию

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

Анализируя таблицу, замечаем «хорошее» значение тангенса, которое близко располагается к 47 градусам:

Таким образом:

После предварительного анализа градусы необходимо перевести в радианы . Так, и только так!

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

Дальнейшее шаблонно:

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

Ответ:

Пример 7

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

Это пример для самостоятельного решения. Полное решение и ответ в конце урока.

Как видите, ничего сложного, градусы переводим в радианы и придерживаемся обычного алгоритма решения.

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

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

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

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

Пример 8

Решение: Как бы ни было записано условие, в самом решении для обозначения функции, повторюсь, лучше использовать не букву «зет», а .

А вот и рабочая формула:

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

По условию требуется найти приближенное значение функции в точке .

Число 3,04 представим в виде . Колобок сам просится, чтобы его съели:
,

Число 3,95 представим в виде . Дошла очередь и до второй половины Колобка:
,

И не смотрите на всякие лисьи хитрости, Колобок есть – надо его съесть.

Вычислим значение функции в точке :

Дифференциал функции в точке найдём по формуле:

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

Вычислим частные производные первого порядка в точке :

Полный дифференциал в точке :

Таким образом, по формуле приближенное значение функции в точке :

Вычислим точное значение функции в точке :

Вот это значение является абсолютно точным.

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

Абсолютная погрешность:

Относительная погрешность:

Ответ: , абсолютная погрешность: , относительная погрешность:

Пример 9

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

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

Данная особенность справедлива и для случая функции одной переменной (первая часть урока).

Пример 10

Решение : Вычислим данное выражение приближенно с помощью полного дифференциала функции двух переменных:

Отличие от Примеров 8-9 состоит в том, что нам сначала необходимо составить функцию двух переменных: . Как составлена функция, думаю, всем интуитивно понятно.

Значение 4,9973 близко к «пятерке», поэтому: , .
Значение 0,9919 близко к «единице», следовательно, полагаем: , .

Вычислим значение функции в точке :

Дифференциал в точке найдем по формуле:

Для этого вычислим частные производные первого порядка в точке .

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

;

.

Полный дифференциал в точке :

Таким образом, приближенное значение данного выражения:

Вычислим более точное значение с помощью микрокалькулятора: 2,998899527

Найдем относительную погрешность вычислений:

Ответ: ,

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

Пример 11

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

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

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

Пример 12

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

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

Если честно, немного утомился, поскольку материал был нудноватый. Непедагогично это было говорить в начале статьи, но сейчас-то уже можно =) Действительно, задачи вычислительной математики обычно не очень сложны, не очень интересны, самое важное, пожалуй, не допустить ошибку в обычных расчётах.

Да не сотрутся клавиши вашего калькулятора!

Решения и ответы:

Пример 2: Решение: Используем формулу:
В данном случае: , ,

Таким образом:
Ответ:

Пример 4: Решение: Используем формулу:
В данном случае: , ,

Проценты — удобная относительная мера, позволяющая оперировать с числами в привычном для человека формате не зависимо от размера самих чисел. Это своего рода масштаб, к которому можно привести любое число. Один процент — это одна сотая доля. Само слово процент происходит от латинского «pro centum», что означает «сотая доля».

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

1. Формула расчета доли в процентном отношении.

Пусть задано два числа: A 1 и A 2 . Надо определить, какую долю в процентном отношении составляет число A 1 от A 2 .

P = A 1 / A 2 * 100.

В финансовых расчетах часто пишут

P = A 1 / A 2 * 100%.

P = 10 / 200 * 100 = 5 (процентов).

2. Формула расчета процента от числа.

Пусть задано число A 2 . Надо вычислить число A 1 , составляющее заданный процент P от A 2 .

A 1 = A 2 * P / 100.

Пример. Банковский кредит 10 000 рублей под 5 процентов. Сумма процентов составит.

P = 10000 * 5 / 100 = 500.

3. Формула увеличения числа на заданный процент. Сумма с НДС.

Пусть задано число A 1 . Надо вычислить число A 2 , которое больше числа A 1 на заданный процент P. Используя формулу расчета процента от числа, получаем:

A 2 = A 1 + A 1 * P / 100.

A 2 = A 1 * (1 + P / 100).

Пример 1. Банковский кредит 10 000 рублей под 5 процентов. Общая сумма долга составит.

A 2 = 10000 * (1 + 5 / 100) = 10000 * 1.05 = 10500.

Пример 2. Сумма без НДС равна 1000 рублей, НДС 18 процентов. Сумма с НДС составляет:

A 2 = 1000 * (1 + 18 / 100) = 1000 * 1.18 = 1180.

style="center">

4. Формула уменьшения числа на заданный процент.

Пусть задано число A 1 . Надо вычислить число A 2 , которое меньше числа A 1 на заданный процент P. Используя формулу расчета процента от числа, получаем:

A 2 = A 1 - A 1 * P / 100.

A 2 = A 1 * (1 - P / 100).

Пример. Денежная сумма к выдаче за минусом подоходного налога (13 процентов). Пусть оклад составляет 10 000 рублей. Тогда сумма к выдаче составляет:

A 2 = 10000 * (1 - 13 / 100) = 10000 * 0.87 = 8700.

5. Формула вычисления исходной суммы. Сумма без НДС.

Пусть задано число A 1 , равное некоторому исходному числу A 2 с прибавленным процентом P. Надо вычислить число A 2 . Иными словами: знаем денежную сумму с НДС, надо вычислить сумму без НДС.

Обозначим p = P / 100, тогда:

A 1 = A 2 + p * A 2 .

A 1 = A 2 * (1 + p).

Тогда

A 2 = A 1 / (1 + p).

Пример. Сумма с НДС равна 1180 рублей, НДС 18 процентов. Стоимость без НДС составляет:

A 2 = 1180 / (1 + 0.18) = 1000.

style="center">

6. Расчет процентов на банковский депозит. Формула расчета простых процентов.

Если проценты на депозит начисляются один раз в конце срока депозита, то сумма процентов вычисляется по формуле простых процентов.

S = K + (K*P*d/D)/100
Sp = (K*P*d/D)/100

Где:
S — сумма банковского депозита с процентами,
Sp — сумма процентов (доход),
K — первоначальная сумма (капитал),

d — количество дней начисления процентов по привлеченному вкладу,
D — количество дней в календарном году (365 или 366).

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

S = 100000 + 100000*20*365/365/100 = 120000
Sp = 100000 * 20*365/365/100 = 20000

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

S = 100000 + 100000*20*30/365/100 = 101643.84
Sp = 100000 * 20*30/365/100 = 1643.84

7. Расчет процентов на банковский депозит при начислении процента на процент. Формула расчета сложных процентов.

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

S = K * (1 + P*d/D/100) N

Где:


P — годовая процентная ставка,

При расчете сложных процентов проще вычислить общую сумму с процентами, а потом вычислить сумму процентов (доход):

Sp = S - K = K * (1 + P*d/D/100) N - K

Sp = K * ((1 + P*d/D/100) N - 1)

Пример 1. Принят депозит в сумме 100 тыс. рублей сроком на 90 дней по ставке 20 процентов годовых с начислением процентов каждые 30 дней.

S = 100000 * (1 + 20*30/365/100) 3 = 105 013.02
Sp = 100000 * ((1 + 20*30/365/100) N - 1) = 5 013.02

style="center">

Пример 2. Проверим формулу начисления сложных процентов для случая из предыдущего примера.

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

S 1 = 100000 + 100000*20*30/365/100 = 101643.84
Sp 1 = 100000 * 20*30/365/100 = 1643.84

S 2 = 101643.84 + 101643.84*20*30/365/100 = 103314.70
Sp 2 = 101643.84 * 20*30/365/100 = 1670.86

S 3 = 103314.70 + 103314.70*20*30/365/100 = 105013.02
Sp 3 = 103314.70 * 20*30/365/100 = 1698.32

Общая сумма процентов с учетом начисления процентов на проценты (сложные проценты)

Sp = Sp 1 + Sp 2 + Sp 3 = 5013.02

Таким образом, формула вычисления сложных процентов верна.

8. Еще одна формула сложных процентов.

Если процентная ставка дана не в годовом исчислении, а непосредственно для периода начисления, то формула сложных процентов выглядит так.

S = K * (1 + P/100) N

Где:
S — сумма депозита с процентами,
К — сумма депозита (капитал),
P — процентная ставка,
N — число периодов начисления процентов.

Пример. Принят депозит в сумме 100 тыс. рублей сроком на 3 месяца с ежемесячным начислением процентов по ставке 1.5 процента в месяц.

S = 100000 * (1 + 1.5/100) 3 = 104 567.84
Sp = 100000 * ((1 + 1.5/100) 3 - 1) = 4 567.84

Квартиль - одна из статистик, используемая при описании выборок (подробнее о различных статистиках см. ). В то время как медиана разделяет упорядоченный массив пополам, квартили разбивают набор данных на четыре части. Первый квартиль – это число, разделяющее выборку на две части: 25% элементов меньше, а 75% - больше значения первого квартиля. Третий квартиль - это число, разделяющее выборку также на две части: 75% элементов меньше, а 25% - больше третьего квартиля.

Рис. 1. 5-числовые сводки: М – медиана, Н1 и Н2 – сгибы (они же квартили)

Скачать заметку в формате или , примеры в формате (файл содержит код VBA).

Для расчета квартилей в Excel2007 и более ранних версиях использовалась функция КВАРТИЛЬ . Начиная с версии Excel2010 применяются две функции: КВАРТИЛЬ.ВКЛ и КВАРТИЛЬ.ИСКЛ (функция КВАРТИЛЬ оставлена для совмещения с более ранними версиями Excel; эта функция возвращает те же значения, что и КВАРТИЛЬ.ВКЛ). Эти две функции возвращают различные значения, но я нигде не нашел, какой алгоритм они используют при расчетах. Замечу, что для корректной работы функций данные можно не упорядочивать.

Изучение литературы показало, что в отличие от большинства других статистик, единодушия в методике расчета квартилей нет)) Я нашел упоминание о девяти различных подходах…

Начнем с метода Джона Тьюки , описанного им в, уже ставшем классическом, труде , изданном в 1977 г. Он начинает с введения трех сводок, характеризующих выборку: минимальное, максимальное значения и медиана. Далее он продолжает: «Если мы хотим добавить еще два числа, чтобы образовать 5-числовую сводку, то естественно определять их подсчетом до половины расстояния от каждого из концов к медиане. Процесс нахождения медианы, а затем и этих новых значений можно представить себе, как складывание листа бумаги. Поэтому эти новые значения естественно назвать сгибами» (англ. – hinge; рис. 1). Мы их называем квартилями.

Такие рисунки выглядят очень аккуратно, если число элементов выборки N = 4k + 1, например, 9, 13, 17… Но как быть, если в выборке 12 или 19 элементов? Наглядную картину представил Jon Peltier в серии заметок в своем блоге. Упорядочим элементы случайной выборки и разместим их над линейкой (рис. 2; случайная выборка, элементы которой упорядочены называется вариационным рядом). Серые числа под линейкой – индекс ряда (Джон зачем-то в качестве выборки – над линейкой – взял ряд целых чисел; наверное, чтобы запутать нас). Красное число над рядом – значение сводки; если оно дробное, значит полученное значение является интерполяцией между соседними значениями. Мы определяем медиану, как среднее значение набора данных, а первую квартиль – как медиану нижней половины данных.

Рис. 2. Инклюзивные квартили

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

Эксклюзивный подход. Некоторым статистикам не нравится, что медиана учитывается дважды. Они решили, что сгибы должны быть определены как медианы верхней и нижней половин набора данных, из которых срединное значение исключено (рис. 3). Такой взгляд отстаивали Moore и McCabe, или кратко M&M. Если набор данных содержит четное количество значений, инклюзивные и эксклюзивные квартили равны, так как нет элемента выборки (соответствующего центральной медиане), который можно было бы включить или исключить из рассмотрения. Для нечетного числа элементов, инклюзивные сгибы всегда ближе к медиане.

Рис. 3. Эксклюзивные квартили

Третий подход – компромисс между Тьюки и М&M – называется Эмпирическая функции распределения или Интегральная функция распределения (английская аббревиатура CDF ). В случае нечетного числа значений в наборе данных, следует включить или исключить медиану, ориентируясь на то, чтобы оставшиеся половинки содержали нечетное число элементов. Например, если в выборке 9 элементов, медиану следует включить, а при 11 элементах – исключить. В обоих случаях половинки будут содержать по 5 элементов. Преимущество этого компромисса заключается в том, что в качестве значения квартиля всегда получается один из элементов набора данных (а не среднее значение двух соседних элементов). CDF является методом по умолчанию в статистическом пакете SAS.

Все возможные случаи N. Мы не всегда можем изобразить данные в W-образной форме, как на рис. 1, поэтому удобнее пользоваться линейкой. В общем случае возможны четыре варианта по числу элементов в выборке: N = 4k, N = 4k + 1, N = 4k + 2, N = 4k + 3… и три подхода к расчету квартилей: Тьюки, M&M, CDF (рис. 4–7).

Рис. 4. Число элементов в выборке N = 4k; все три метода дают одинаковые значения квартилей

Рис. 5. Число элементов в выборке N = 4k + 1; M&M дает значения, отстоящие дальше от медианы

Рис. 6. Число элементов в выборке N = 4k + 2; все три метода дают одинаковые значения квартилей

Рис. 7. Число элементов в выборке N = 4k + 3

Методы интерполяции. Помимо трех описанных выше методов, применяют и целый ряд индексных алгоритмов. Мы рассмотрим три из них. Первый индекс во всех методах равен 0, а последний – N–1, N, N + 1. Например, для N=8 индексированные ряды представлены на рис. 8.

Положение перцентиля р – доля длины индексной линии, или р(N–1), рN, р(N+1), соответственно. р = 0,25 соответствует первому квартилю, а р = 0,75 – третьему. Ниже наглядно представлен расчет квартилей при различном числе элементов в выборке и трех методах интерполяции на основе N–1, N и N + 1 (рис. 9, 11–13). Обратите внимание, что рассчитанные числа (по формулам справа от линеек) являются не значениями квартилей, а значениями индексов квартилей. Над линейками показано значение квартилей для ряда значений {1, 2, 3, 4, 5, 6, 7, 8}.

Рис. 9. Число элементов в выборке N = 4k

Если, например, наша выборка {2, 3, 5, 8, 11, 12, 14, 17}, то расчет квартилей на основе N–1-метода даст индексы 1,75, 3,5 и 5,25, и значения квартилей 4,5, 9,5 и 12,5 (рис. 10).

Рис. 10. От индексов к значениям квартилей для N–1-метода и N = 4k

Рис. 11. Число элементов в выборке N = 4k + 1

Рис. 12. Число элементов в выборке N = 4k + 2

Рис. 13. Число элементов в выборке N = 4k + 3

Какой алгоритм считать стандартным для вычисления квартилей?

В 1996 году Роб Дж. Хиндман и Янан Фан опубликовали статью в American Statistician под названием Квантили выборок в статистических пакетах . В ней они рассматривали различные алгоритмы расчета квантилей (квартили – это частный случай квантилей). Их целью было указать методологию, которая могла бы стать стандартом для поставщиков статистического программного обеспечения, чтобы расчет квартилей не зависел от типа пакета. В статье они описали девять методов для расчета квантилей. Таблица показывает некоторые статистические пакеты и используемые в них алгоритмы (рис. 14; таблица, этот раздел заметки и код VBA ниже базируются на тексте с сайта Bacon Bits). Обратите внимание, что R и Maple применяют весь спектр алгоритмов.

Рис. 14. Алгоритмы, используемые в статистических пакетах

Кстати, Хиндман и Фан в завершении своей статьи рекомендовали метод 8 в качестве стандарта для статистических пакетов. По их мнению, этот метод оценки квантиля не зависит от распределения, что делает его наиболее приемлемым для расчета.

Расчет квартилей в Excel

Функция Excel КВАРТИЛЬ.ИСКЛ использует следующую формулу для расчета квартилей:

где Q p – p -й квантиль: p = 0 – для минимального значения, 0,25 – для первого квартиля, 0,5 – для медианы, 0,75 – для третьего квартиля, 1 – для максимального значения;

x – индекс квантиля (может быть дробным); x = (n+1)p , где n – число элементов в выборке; обратите внимание на (n +1) , поэтому метод и называется N+1-интерполяция;

i – индекс элемента в упорядоченной выборке; самое большое целое всё еще меньшее, чем x ;

A 1 , A 2 , …, A i , A i +1 , …, A n – элементы случайной выборки, упорядоченной по возрастанию.

Формула для КВАРТИЛЬ.ВКЛ отличается только методом расчета х: x = (n-1)p+1 ; обратите внимание на (n –1) , поэтому метод называется N–1-интерполяция. Подробнее с работой формул можно ознакомиться в приложенном Excel-файле на листе Формулы .

Расчет квартилей в R и SAS

Функция quantile в R использует все девять алгоритмов расчета квантилей, в соответствии с нумерацией, предложенной Hyndman and Fan в работе 1996 г. (рис. 15; если вы не знакомы с R, рекомендую начать с ). Квантиль при i-м методе расчета:

Рис. 15. Расчет квартилей в R девятью способами

Расчет квартилей в Excel любым методом с помощью VBA

Ниже представлен код пользовательской функции, которая позволяет воспроизвести любой из шести методов, перечисленных в таблице на рис. 14. Даже если у вас Excel 2007, и вам недоступна функция КВАРТИЛЬ.ИСКЛ, вы сможете рассчитать квартиль шестым методом с помощью этой функции.

Function Quantile(MyRange As Range, p As Double, Optional m As Variant) "Mike Alexander: www.datapigtechnologies.com "Based on Code originally posted by Jerry W. Lewis (former Excel MVP) "********************************************************************* "This function will replicate various quantile calcuations found "in statistical software packages. "Calculation is determined by the Hyndman-Fan method used. "Hyndman-Fan Method 4 Replicates: "SAS(PCTLDEF=1), R(type=4), Maple(method=3) "Hyndman-Fan Method 5 Replicates: R(type=5), Maple(method=4) "Hyndman-Fan Method 6 Replicates: Excel(QUARTILE.EXC), SAS(PCTLDEF=4), "R(type=6), Minitab, SPSS, BMDP, JMP, Maple(method=5) "Hyndman-Fan Method 7 Replicates: Excel (QUARTILE and QUARTILE.INC), "R(type=7), S-Plus, Maxima, Maple(method=6) "Hyndman-Fan Method 8 Replicates: R(type=8), Maple(method=7) "Hyndman-Fan Method 9 Replicates: R(type=9), Maple(method=8) "********************************************************************** "Call function from Excel Spreadhseet by entering "=Quantile(Range, p, m) "Enter p as the fraction of the population "(.25 for quartile 1, .75 for quartile 3, etc....) "Enter m as the Hyndman-Fan Quantile method number (4, 5, 6, 7, 8 or 9) "If m is left blank, the function will use method 6 by default "********************************************************************** Dim n As Long Dim i As Long Dim QDef As Double Dim x As Double "Identify method and set the interpolation basis used Select Case m Case Is = 4 QDef = 0 Case Is = 5 QDef = 0.5 Case Is = 6 QDef = p Case Is = 7 QDef = 1 - p Case Is = 8 QDef = (p + 1) / 3 Case Is = 9 QDef = (p + 1.5) / 4 Case Else "Use Hyndman-Fan 6 by default QDef = p End Select "Count values within MyRange and calculate the required position index n = WorksheetFunction.Count(MyRange) x = n * p + QDef i = WorksheetFunction.Max(WorksheetFunction.Min(Fix(x), n), 1) "Perform interpolation and return answer If (x - i) >= 0 And i < n Then Quantile = (1 - (x - i)) * WorksheetFunction.Small(MyRange, i) + _ (x - i) * WorksheetFunction.Small(MyRange, i + 1) Else Quantile = WorksheetFunction.Small(MyRange, i) End If End Function

После того, как вставите код в стандартный модуль книги, вы сможете использовать функцию (рис. 16): =Quantile(MyRange; P; M), где MyRange – диапазон, включающий выборку (можно оставить его неупорядоченным); Р – статистика: 0 – минимум, 0,25 – 1-й квартиль, 0,5 – медиана, 0,75 – 3-й квартиль, 1 – максимум; возможно введение иных значений в диапазоне от 0 до 1; М – номер метода из таблицы на рис. 14.

Рис. 16. Синтаксис пользовательской функции Quantile

В таблице (рис. 17) приведен расчет квартилей по всем методам. Обратите внимание, как метод 8 (который Хиндман и Фан рекомендуют в качестве стандарта) вычисляет квартили, которые ложатся между значениями, вычисляемыми по методам 6 и 7. Действительно, метод 8 дает наиболее сбалансированный набор квартилей.

Рис. 17. Значения квартилей, вычисленные различными методами

Сравнение алгоритмов вычисления квартилей

Стандартом де-факто вычисления квартилей в статистических пакетах и Excel является метод 6 на основе N+1-интерполяции. Если вы хотите, чтобы ваши данные были одинаковыми при использовании различных инструментов, используйте именно этот метод. В Excel он лежит в основе работы функции КВАРТИЛЬ.ИСКЛ. К сожалению, этот метод приводит к увеличению межквартильного интервала. Для нашего примера (рис. 17) с 13,0 до 15,5. Если сравнить все пять методов расчета (рис. 18), то видно, что минимальный межквартильный интервал соответствует методу 7, а максимальный – методу 6. На что это влияет мы рассмотрим в заметке . Если же вы используете только Excel рекомендую метод 7 на основе N–1-интерполяции. Это позволит вам оперировать с самым узким межквартильным интервалом.

Рис. 18. Влияние алгоритма расчета квартилей на межквартильный интервал; цифры от 5 до 9 – номера методов