Как найти значение предела в excel

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

Математические функции случайных чисел и возможных комбинаций

Это функции, позволяющие работать со случайными числами. Надо сказать, что по-настоящему случайных чисел не бывает. Все они генерируются по определенным закономерностям. Тем не менее, для решения прикладных задач генератор даже не совсем случайных чисел бывает очень полезным. К математическим функциям, которые генерируют случайные числа, относятся СЛУЧМЕЖДУ, СЛЧИС, ЧИСЛКОМБ, ФАКТР. Давайте каждую из них рассмотрим более подробно.

Функция СЛУЧМЕЖДУ

Это одна из самых часто используемых функций этой категории. Она генерирует случайное число, которое вписывается в определенный предел. Важно учитывать, что если диапазон слишком узкий, числа могут быть одинаковыми. Синтаксис очень прост: =СЛУЧМЕЖДУ(нижнее значение; верхнее значение). В качестве параметров, передаваемых пользователем, могут выступать как числа, так и ячейки, в которых есть определенные цифры. Обязательный ввод каждого аргумента.

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

Величина этого числа постоянно изменяется. Каждый раз при редактировании документа значение получается другим.

Функция СЛЧИС

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

Никаких дополнительных параметров передавать этой функции не нужно. Следовательно, ее синтаксис максимально простой: =СЛЧИС(). Возможно возвращение и дробных случайных значений. Для этого необходимо воспользоваться функцией СЛЧИС. Формула будет следующей: =СЛЧИС()*(максимальный предел-минимальный предел)+минимальный предел.

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

Функция ЧИСЛКОМБ

Эта функция относится к такому разделу математики, как комбинаторика. Она определяет количество уникальных комбинаций при определенном количестве объектов в выборке. Активно используется, например, в статистических исследованиях в социономических науках.Синтаксис функции следующий: =ЧИСЛКОМБ(размер набора, количество элементов). Давайте рассмотрим эти аргументы более подробно:

1. Размер набора – это общее количество элементов в выборке. Это может быть количество людей, товара и так далее.
2. Количество элементов. Этот параметр обозначает ссылку или число, демонстрирующие полное количество объектов, которое должно в итоге получиться. Главное требование к величине этого аргумента – он всегда должен быть меньшим в сравнении с предыдущим.

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

Давайте теперь введем формулу, которая нам нужна для получения этих комбинаций: =ЧИСЛКОМБ(4;2). В результате, будет выведено 6 возможных комбинаций, состоящих из разных значений.

Функция ФАКТР

В математике есть такое понятие, как факториал. Под этим значением подразумевается число, которое получается в результате умножения всех натуральных чисел до этого числа. Например, факториалом числа 3 будет число 6, а факториалом числа 6 станет число 720. Обозначается факториал восклицательным знаком. А с помощью функции ФАКТР становится возможным нахождение факториала. Синтаксис формулы: =ФАКТР(число). Факториал соответствует количеству вероятных комбинаций значений, входящих в набор. Например, если у нас есть три элемента, то максимальное количеством комбинаций в этом случае будет 6.

Функции преобразования чисел

Преобразование чисел – это выполнение определенных операций с ними, которые не относятся к арифметическим. Например, превращение числа в римское, возвращение его модуля. Эти возможности реализуются с помощью функций ABS и РИМСКОЕ. Давайте их рассмотрим более подробно.

Функция ABS

Напоминаем, что модуль – это расстояние до нуля на оси координат. Если представить горизонтальную прямую с отмеченными на ней числами с шагом в 1, то можно увидеть что от числа 5 до нуля и от числа -5 до нуля будет одинаковое количество ячеек. Это расстояние и называется модулем. Как мы можем увидеть, модулем числа -5 является 5, поскольку необходимо пройти 5 ячеек, чтобы дойти до нуля.

Чтобы получить модуль числа, необходимо воспользоваться функцией ABS. Ее синтаксис очень простой. Достаточно в скобках написать число, после чего будет возвращено значение. Синтаксис следующий: =ABS(число). Если ввести формулу =ABS(-4), то в результате этих операций будет получено 4.

Функция РИМСКОЕ

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

1. Число. Это непосредственно число, или ссылка на содержащую значение в этом форме ячейку. Важное требование – этот параметр должен быть выше нуля. Если число содержит разряды после запятой, то после его перевода в римский формат, дробная часть попросту отсекается.
2. Формат. Этот аргумент уже не обязательно использовать. Задает формат представления. Каждому числу соответствует определенный внешний вид числа. Есть несколько возможных параметров, которые могут использоваться в качестве этого аргумента:
   1. 0. В этом случае значение показывается в своем классическом виде.
   2. 1-3 – разные виды отображения римских чисел.
   3. 4. Облегченный способ показа римского числа.
   4. Истина и Ложь. В первой ситуации число представляется в стандартном виде, а во втором – упрощенном.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

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

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

1. Номер функции. Этот аргумент – число в границах от 1 до 11. Это число показывает на ту функцию, которая будет применяться для подведения итогов определенного диапазона. Например, если нам требуется складывать числа, то нужно указать в качестве первого параметра число 9 или 109.
2. Ссылка 1. Это тоже обязательный параметр, который дает ссылку на диапазон, учитываемый для подведения итогов. Как правило, люди используют лишь один диапазон.
3. Ссылка 2, 3… Далее идет определенное количество ссылок на диапазон.

Максимальное количество аргументов, которые может содержать эта функция – 30 (номер функции + 29 ссылок).

Важное замечание! Вложенные итоги игнорируются. То есть, если в каком-то диапазоне уже применялась функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ, то она игнорируется программой.

Следует также учитывать, что использование этой функции для подведения промежуточных итогов для горизонтальных массивов с данными не рекомендуется, поскольку она не предназначена для этого. В этом случае результаты могут оказаться неправильными. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ нередко совмещается с автофильтром. Предположим, у нас есть такой набор данных.

Давайте попробуем применить к нему автофильтр и отобрать лишь ячейки, маркируемые, как «Товар1». Далее поставим задачу определить с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ промежуточную сумму этих товаров. Здесь нам нужно применять код 9, как показано на скриншоте.

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

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

1 – СРЗНАЧ;

2 – СЧЁТ;

3 – СЧЁТЗ;

4 – МАКС;

5 – МИН;

6 – ПРОИЗВЕД;

7 – СТАНДОТКЛОН;

8 – СТАНДОТКЛОНП;

9 – СУММ;

10 – ДИСП;

11 – ДИСПР.

Также можно добавить 100 к этим номерам, и функции будут те же. Но есть одна разница. Разница в том, что в первом случае скрытые ячейки не будут учитываться, а во втором – да.

Иные математические функции

Математика – непростая наука, которая включает множество формул для самых разнообразных задач. Excel включает почти все. Рассмотрим лишь три из них: ЗНАК, Число Пи, ПРОИЗВЕД.

Функция ЗНАК

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

Синтаксис функции следующий: =ЗНАК(число). Видим, что здесь только один аргумент, ввод которого обязательный. После проверки числа функция возвращает значение -1, 0 или 1 в зависимости от того, какой знак был. Если число оказалось отрицательное, то будет -1, а если положительное – 1. Если же в качестве аргумента попался ноль, то он же и возвращается. Функция используется в сочетании с функцией ЕСЛИ или в любом другом подобном случае, когда нужно осуществить проверку числа.

Функция Число ПИ

Число ПИ является самой известной математической константой, которая равняется 3,14159… С помощью данной функции можно получить округленную до 14 знаков после запятой версию данного числа. Она не имеет аргументов и у нее следующий синтаксис: =ПИ().

Функция ПРОИЗВЕД

Функция, похожая по принципу на СУММ, только вычисляет произведение всех чисел, переданных ей в качестве аргументов. Можно указать до 255 чисел или диапазонов. Важно учитывать, что функция не учитывает текстовые, логические и любые другие значения, которые не используются в арифметических операциях. Если же в качестве аргумента используется логическое значение, то значению ИСТИНА соответствует единица, а значению ЛОЖЬ – нуль. Но важно понимать, что если в диапазоне будет логическое значение, то результат будет неправильный. Синтаксис формулы следующий: =ПРОИЗВЕД(число 1; число 2…).

Видим, что здесь приводятся числа через точку с запятой. Обязательный аргумент один – первое число. В принципе, можно и не использовать эту функцию при небольшом количестве значений. Тогда нужно последовательно перемножить все числа и ячейки. Но когда их много, то в ручном режиме это будет занимать довольно много времени. Чтобы его сэкономить, и существует функция ПРОИЗВЕД.

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

В Excel есть толпа функций в категории Финансовые. Я ими никогда не пользовался, но Вы то, финансист?!  
Там наверняка есть то, что Вам нужно. Вот, например  

  ПЛПРОЦ  
Возвращает платежи по процентам за данный период на основе периодических постоянных выплат и постоянной процентной ставки.    

  ОСНПЛАТ  
Возвращает величину выплаты на данный период на основе периодических постоянных платежей и постоянной процентной ставки.  

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

Электронные
таблицы Excel фирмы Microsoft
имеют встроенные средства решения задач
поиска экстремума, оформленные в виде
так называемой надстройки. Перед началом
работы надо убедиться в том, что в составе
сгенерированного на вашей ЭВМ пакета
Excel требуемая надстройка
установлена. Для этого выберите режим
Сервис главного меню и проверьте,
есть ли в открывшемся ниспадающем меню
пункт Поиск решения (рис. 5). Если
строка меню Поиск решения отсутствует,
то выберите пункт меню Сервис /
Надстройки и в открывшейся форме
включите режим Поиск решения (рис.
6). Если и в этом окне пункт Поиск решения
отсутствует, то это означает, что на
вашей машине установлена сокращенная
версия электронных таблиц и требуется
переустановка пакета Excel.

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

В
качестве параметров режима (рис.
задаются методы поиска экстремума. Так,
при установке флажка Линейная модель
надстройка ищет экстремум симплекс-методом.
Флажок Неотрицательные значения
накладывает дополнительное ограничение
на значения переменных задачи. Его
установка эквивалентна введению
ограничения

.

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

Рис. 5. Пункт меню Поиск решения

Рис. 6. Включение надстройки Поиск
решения

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

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

Рис. 7. Главная форма надстройки Поиск
решения

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

(4)

На
рис. 9 изображен рабочий лист Excel
с данными задачи (4). Для всех ячеек,
предназначенных для хранения данных,
был задан числовой формат с двумя знаками
после запятой режимом Формат/Ячейки…/Число.
Матрица

размещена в диапазоне ячеек B8:E16. Значения
ограничений

находятся в диапазоне ячеек G8:G16. Весовые
коэффициенты целевой функции

занесены в диапазон ячеек B5:E5. Кроме
этого, для хранения переменных

зарезервирован диапазон ячеек B3:E3.
Значения

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

,

.

Рис. 8. Параметры надстройки Поиск
решения

Выражение
(3) представляет собой ничто иное, как
сумму

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

в ячейке F5. Ее программирование сводится
к заданию выражения типа (3), которое
можно рассчитать непосредственно на
основе формулы Excel
=B3*B5+C3*C5+D3*D5+E3*E5. Тем не менее, по ряду
причин, более удобно воспользоваться
встроенной функцией СУММПРОИЗВ(B5:E5;$B$3:$E$3),
которая автоматически определяет
количество слагаемых

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

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

Выражения,
определяющие расход ресурсов

программируются в ячейки F8, F9,…, F16
аналогично предыдущему с той только
разницей, что в качестве первого аргумента
функции СУММПРОИЗВ() выступает
соответствующая строка матрицы

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

.

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

Рис. 9. Вариант размещения данных на
рабочем листе

Выполненные
ранее в операции (заполнение таблиц
данными и программирование формул)
позволяют полностью подготовиться
собственно к решению задачи оптимизации.
Теперь нам необходимо вызвать режим
Сервис/Поиск решения. В открывшейся
главной форме меню режима Поиск решения
(рис. 7) надо указать адрес нашей
целевой ячейки F5 и проверить или задать
тип экстремума (в нашем случае Установить
целевую ячейку равной максимальному
значению). В окне Изменяя ячейки задаем
адреса ячеек переменных

(в нашем случае B3:F3). Нажав кнопку Добавить
в открывшейся таблице (рис. 10) Добавление
ограничения в поле Ссылка на ячейку
вводим адреса левых частей неравенств
(2) (в нашем случае F8:F16). Устанавливаем
(сохраняем) требуемые знаки неравенств
(в нашем случае

).
Войдя в окно Ограничение, задаем
адреса ячеек, содержащих значения

(в нашем случае G8:G16). Нажав кнопку
Параметры, в открывшейся таблице
(рис. задаем режим Линейная модель
и Неотрицательные значения, после
чего нажимаем кнопку OK. Результат
этих действий отображен на рис11.

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

Решение найдено. Все ограничения и
условия оптимальности выполнены
(имеет место в рассматриваемом случае).

Поиск не может найти подходящего
решения.

Значения целевой ячейки не сходятся.

Если
решение найдено, то на рабочем листе
Excel в изменяемых ячейках
находятся значения переменных

(в нашем случае 1,13; 0,00; 0,00; 3,10), обеспечивающие
максимальное значение целевой функции
(в нашем случае 33,95). Для сохранения
результатов вычислений на рабочем листе
необходимо выбрать пункт Сохранить
найденное решение.

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

,
который удовлетворял бы имеющимся
ограничениям.

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

Рис. 10. Добавление ограничений

Рис. 11. Подготовленная к решению задача
линейного программирования

По
результатам решения в случае установки
режима Линейная модель (симплекс-метод)
могут быть представлены три типа отчетов:
по результатам, по устойчивости и по
пределам. Если они требуются, то в меню
результаты поиска решения в окне Тип
отчета (рис. 12) необходимо выделить
соответствующие строки.

Отчет
по результатам (рис. 13) содержит
начальные (Исходно) и конечные
(Результат) значения целевой функции
и изменяемых ячеек, а также сводку
результатов использования ресурсов. В
этой сводке в столбце Статус символами
связанное или несвязанное
обозначаются соответственно полное
или неполное использование соответствующего
ресурса. В рассматриваемом примере
полностью израсходованы Ресурс 3 и
Ресурс 4.

Рис. 12. Результат решения задачи линейного
программирования

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

Рис. 13. Отчет по результатам

Рис. 14. Отчет по устойчивости

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

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

Рис. 15. Отчет по пределам

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

Быстрый пример

На изображении ниже выбросы довольно легко обнаружить — значение два присвоено Эрику, а значение 173 — Райану. В таком наборе данных достаточно легко обнаружить и обработать эти выбросы вручную.

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

Как найти выбросы в ваших данных

Чтобы найти выбросы в наборе данных, мы используем следующие шаги:

Вычислите 1-й и 3-й квартили (мы немного поговорим о том, что это такое).
Оцените межквартильный размах (мы также объясним это немного ниже).
Верните верхнюю и нижнюю границы нашего диапазона данных.
Используйте эти границы для определения отдаленных точек данных.

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

Давайте начнем.

Шаг 1. Рассчитайте квартили

Если вы разделите данные на кварталы, каждый из этих наборов называется квартилем. Самые низкие 25% чисел в диапазоне составляют 1-й квартиль, следующие 25% — 2-й квартиль и т. Д. Мы делаем этот шаг в первую очередь, потому что наиболее широко используемое определение выброса — это точка данных, которая более чем на 1,5 интерквартильных диапазонов (IQR) ниже 1-го квартиля и на 1,5 межквартильных диапазонов выше 3-го квартиля. Чтобы определить эти значения, мы сначала должны выяснить, каковы квартили.

Excel предоставляет функцию КВАРТИЛЬ для расчета квартилей. Для этого требуются две части информации: массив и кварта.

=QUARTILE(array, quart)

Массив — это диапазон значений, которые вы оцениваете. Кварта — это число, которое представляет квартиль, который вы хотите вернуть (например, 1 для 1-го квартиля, 2 для 2-го квартиля и т. Д.).

Примечание. В Excel 2010 Microsoft выпустила функции QUARTILE.INC и QUARTILE.EXC как усовершенствования функции QUARTILE. QUARTILE более обратно совместима при работе с несколькими версиями Excel.

Вернемся к нашему примеру таблицы.

Для вычисления 1-го квартиля мы можем использовать следующую формулу в ячейке F2.

=QUARTILE(B2:B14,1)

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

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

=QUARTILE(B2:B14,3)

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

Шаг второй: оцените межквартильный размах

Межквартильный диапазон (или IQR) — это средние 50% значений в ваших данных. Он рассчитывается как разница между значением 1-го квартиля и значением 3-го квартиля.

Мы собираемся использовать простую формулу в ячейке F4, которая вычитает 1-й квартиль из 3-го квартиля:

=F3-F2

Теперь мы можем видеть наш межквартильный размах.

Шаг третий: верните нижнюю и верхнюю границы

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

Мы рассчитаем нижний предел в ячейке F5, умножив значение IQR на 1,5, а затем вычтя его из точки данных Q1:

=F2-(1.5*F4)

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

Чтобы вычислить верхнюю границу в ячейке F6, мы снова умножим IQR на 1,5, но на этот раз добавим его к точке данных Q3:

=F3+(1.5*F4)

Шаг четвертый: выявление выбросов

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

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

=OR(B2$F$6)

Затем мы скопируем это значение в наши ячейки C3-C14. Значение ИСТИНА указывает на выброс, и, как видите, в наших данных их два.

Игнорирование выбросов при вычислении среднего среднего

Использование функции КВАРТИЛЬ позволяет нам рассчитать IQR и работать с наиболее широко используемым определением выброса. Однако при вычислении среднего среднего для диапазона значений и игнорировании выбросов существует более быстрая и простая функция. Этот метод не будет определять выбросы, как раньше, но он позволит нам быть гибкими в выборе того, что мы можем считать своей частью выбросов.

Нужная нам функция называется TRIMMEAN, синтаксис для нее вы можете увидеть ниже:

=TRIMMEAN(array, percent)

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

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

=TRIMMEAN(B2:B14, 20%)

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