Инструкция по применению формул Excel с примерами. Как рассчитать изменение в процентах в 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 с примерами.

Если поставить курсор мышки на любую ячейку и нажать на пункт «выбрать функцию», то появляется мастер функций.

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

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

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

Например, дана задача, найти функцию СУММЕСЛИМН. Для этого нужно зайти в категорию математических функций и там найти нужную.

Функция ВПР

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

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

Вычисление ВПР можно проследить на примере, в котором приведен список из фамилий . Задача – по предложенному номеру найти фамилию.

Применение функции ВПР

Формула показывает, что первым аргументом функции является ячейка С1.

Второй аргумент А1:В10 – это диапазон, в котором осуществляется поиск.

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

Вычисление заданной фамилии с помощью функции ВПР

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

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

Поиск фамилии с пропущенными номерами

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

Он имеет только два значения – «ложь» или «истина». Если аргумент не задается, то он устанавливается по умолчанию в позиции «истина».

Округление чисел с помощью функций

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

А полученное значение можно использовать при расчетах в других формулах.

Округление числа осуществляется с помощью формулы «ОКРУГЛВВЕРХ». Для этого нужно заполнить ячейку.

Первый аргумент – 76,375, а второй – 0.

Округление числа с помощью формулы

В данном случае округление числа произошло в большую сторону. Чтобы округлить значение в меньшую сторону, следует выбрать функцию «ОКРУГЛВНИЗ».

Округление происходит до целого числа. В нашем случае до 77 или 76.

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

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

Вся правда о формулах программы Microsoft Excel 2007

Формулы EXCEL с примерами - Инструкция по применению

Квартиль - одна из статистик, используемая при описании выборок (подробнее о различных статистиках см. ). В то время как медиана разделяет упорядоченный массив пополам, квартили разбивают набор данных на четыре части. Первый квартиль – это число, разделяющее выборку на две части: 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

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 WorksheetFunction 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 – номера методов

Доброго дня уважаемые радиолюбители!
Приветствую вас на сайте “ “

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

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

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

Соотношения закона Ома

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

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

U - напряжение (В),

I - ток (А),

Р - мощность (Вт),

R - сопротивление (Ом),

Потренируемся на следующем примере: пусть нужно найти мощность схемы. Известно, что напряжение на ее выводах составляет 100 В, а ток- 10 А. Тогда мощность согласно закону Ома будет равна 100 х 10 = 1000 Вт. Полученное значение можно использовать для расчета, скажем, номинала предохранителя, который нужно ввести в устройство, или, к примеру, для оценки счета за электричество, который вам лично принесет электрик из ЖЭК в конце месяца.

А вот другой пример: пусть нужно узнать номинал резистора в цепи с лампочкой, если известно, какой ток мы хотим пропускать через эту цепь. По закону Ома ток равен:

I = U / R

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

Что же в этой формуле есть что? Рассмотрим переменные подробнее.

> U пит (иногда также обозначается как V или Е): напряжение питания. Вследствие того, что при прохождении тока через лампочку на ней падает какое-то напряжение, величину этого падения (обычно рабочее напряжение лампочки, в нашем случае 3,5 В) нужно вычесть из напряжения источника питания. К примеру, если Uпит = 12 В, то U = 8,5 В при условии, что на лампочке падает 3,5 В.

> I : ток (измеряется в амперах), который планируется пропустить через лампочку. В нашем случае – 50 мА. Так как в формуле ток указывается в амперах, то 50 миллиампер составляет лишь малую его часть: 0,050 А.

> R : искомое сопротивление токоограничивающего резистора, в омах.

В продолжение, можно проставить в формулу расчета сопротивления реальные цифры вместо U, I и R:

R = U/I = 8,5 В / 0,050 А= 170 Ом

Расчёты сопротивления

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

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

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

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

Расчет сопротивления последовательных резисторов

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

Rобщ = Rl + R2 + R3 + … (столько раз, сколько есть элементов)

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

Так, к примеру, если имеется цепь из двух соединенных последовательно резисторов с номиналами 1,2 и 2,2 кОм, то суммарное сопротивление этого участка схемы будет равно 3,4 кОм.

Расчет сопротивления параллельных резисторов

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

R общ = R1 * R2 / (R1 ­­+ R2)

где R1 и R2 - сопротивления отдельных резисторов или других элементов цепи, а Rобщ -результирующая величина. Так, если взять те же самые резисторы с номиналами 1,2 и 2,2 кОм, но соединенные параллельно, получим

776,47 = 2640000 / 3400

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

Расчёты ёмкости

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

Расчет емкости параллельных конденсаторов

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

Собщ = CI + С2 + СЗ + …

В этой формуле CI, С2 и СЗ - емкости отдельных конденсаторов, а Собщ суммирующая величина.

Расчет емкости последовательных конденсаторов

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

Собщ = С1 * С2 /(С1+С2)

где С1 и С2 - значения емкости каждого из конденсаторов, а Собщ - общая емкость цепи

Расчет емкости трех и более последовательно соединенных конденсаторов

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

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

Расчёт энергетических уравнений

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

ватт-часы = Р х Т

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

Расчёт постоянной ёмкости RC-цепочки

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

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

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

T = RC

В этом уравнении литера Т служит для обозначения времени в секундах, R - сопротивления в омах, и С - емкости в фарадах.

Пусть, к примеру, имеется резистор 2000 Ом, подключенный к конденсатору 0,1 мкФ. Постоянная времени этой цепочки будет равна 0,002 с, или 2 мс.

Для того чтобы на первых порах облегчить вам перевод сверхмалых единиц емкостей в фарады, мы составили таблицу:

Расчёты частоты и длины волны

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

Расчет частоты сигнала

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

Частота = 300000 / длина волны

Длина волны в данной формуле выражается в миллиметрах, а не в футах, аршинах или попугаях. Частота же дана в мегагерцах.

Расчет длины волны сигнала

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

Длина волны = 300000 / Частота

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

Приведем пример расчета. Пусть радиолюбитель общается со своим другом на частоте 50 МГц (50 миллионов периодов в секунду). Подставив эти цифры в приведенную выше формулу, получим:

6000 миллиметров = 300000 / 50 МГц

Однако чаще пользуются системными единицами длины - метрами, поэтому для завершения расчета нам остается перевести длину волны в более понятную величину. Так как в 1 метре 1000 миллиметров, то в результате получим 6 м. Оказывается, радиолюбитель настроил свою радиостанцию на длину волны 6 метров. Прикольно!