Анализ данных и их оптимизация в Excel

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

Формулы и функции в Excel автоматически пересчитывают результат при изменении содержимого ячеек, на которые имеются ссылки в данной формуле или функции. Другими словами, можно отвечать на вопросы типа «что-если». Например, при анализе финансовой функции ПЛТ ответить на вопрос, что будет, если первый взнос при получении ипотечной ссуды будет составлять не 20% от цены, а 15%.

Итак, проиллюстрируем проведение анализа данных «что-если» на примере работы функции ПЛТ, которая вычисляет величину выплаты по ссуде на основе постоянных выплат и постоянной процентной ставки.

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

Бс — значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если этот аргумент опущен, предполагается, что он равен 0 (например, значение «бс» для займа равно 0).

Тип — число 0 (ноль) или 1, обозначающее, когда должна производиться выплата.

Рассмотрим пример использования функции ПЛТ в Exceel.

Итак, требуется определить ежемесячные выплаты по займу в 20 000 руб., взятому на 16 месяцев под 11% годовых.

Для решения задачи выделяем ячейку на рабочем листе Excel (в нашел случаи ячейка А1) и в строку формул вводим следующее выражение: =ПЛТ(11%/12; 16; 20000) (Рис.1.1)

Нажав на клавишу Enter , мы получаем величину ежемесячных выплат по ссуде, которая составит -1350 руб. Рис.1.2

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

Рис. 1.3 — Пример расчета Excel, в котором исходные данные в отдельные ячейки

При изменении любых значений на рис.3 результаты расчета автоматически обновляются в разделе Результат расчета.

Вывод: Рассмотренный выше примеры показывают, что размещение исходных данных в отдельные ячейки упрощает анализ зависимости выходного результата от изменения исходных данных с использованием анализа данных «Что если» в Exceel.

Сформируем столбце С массив верхних границ диапазонов (интервалов). Для наглядности в столбце D сформируем текстовые значения соответствующие границам интервалов (1-10; 11-20; . 91-100).

Как посчитать в Excel (эксель)

• Выполнить команду меню Сервис > Поиск решения, чтобы вызвать диалоговое окно Поиск решения (рис. 4.2)
• Установить курсор в поле Установить целевую ячейку диалогового окна и щелкнуть мышкой на целевой ячейке Е7 (рис. 4.2).
• Установить курсор в поле Изменяя ячейки диалогового окна и выделить диапазон изменяемых ячеек С3:С6.
• Установить курсор в поле Ограничения и щелкнуть на кнопке Добавить . В появившееся диалоговое окно, показанное на рис. 4.3, вводить поочередно все ограничения (рис. 4.4).
• Щелкнуть на кнопке Выполнить диалогового окна Поиск решения.

Метод наименьших квадратов (англ. Ordinary Least Squares, OLS) является одним из базовых методов регрессионного анализа в части оценки неизвестных параметров регрессионных моделей по выборочным данным. Основная статья про МНК – МНК: Метод Наименьших Квадратов в MS EXCEL.

ГПР

Функция ГПР выполняет туже задачу, что и ВПР, только она просматривает первую строку в поиске искомого значения и для получения результата сдвигается на указанное количество строк вниз.

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

— Интервальный просмотр — ставьте всегда 0, тогда Эксель будет искать точное совпадение, что нам и нужно в большинстве случаев.

Если вы хотите более подробно изучить, как пользоваться функцией ГПР — прочитайте статью на нашем сайте «Функция ГПР в Excel».

Две функции: y = 2 в степени x и y = e в степени x , где x = время, к примеру. Мы видим, что скорость роста экспоненциального графика увеличивается быстрее. А все почему? Потому, что производная (скорость роста или уменьшения) функции равна самой функции, т.е. скорость увеличения функции равна значению функции.

Функция частота в excel примеры

Пример 1. В заводском цехе производят деталь, длина которой должна составлять 200 мм. Стандартное отклонение от длины – 3,6 мм. Для контроля качества деталей из партии (генеральная совокупность) делают выборку из 25 деталей. Определить интервал с доверительный уровнем 95%.

Как правильно считать

Только что мы научились считать простейшим способом. Конечно, таким образом можно вычислить и более сложные вещи. Главное, не забывать ставить скобки, где нужно. Например: =((375*230)+(1263-455))/(120*33)

Но, как правило, этот способ «счета» используется не так часто. Существует более продвинутый вариант.

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

Начну с первой позиции «Сыр». Щелкаю в ячейке D2 и печатаю знак равно.

Затем нажимаю на ячейку B2, так как нужно ее значение умножить на C2.

И, наконец, нажимаю кнопку Enter на клавиатуре. Все! В ячейке D2 получился нужный результат.

Щелкнув по этой ячейке (D2) и посмотрев в строку формул, можно увидеть, как получилось данное значение.

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

Объясню на примере этой же таблицы. Сейчас в ячейке B2 введено число 213. Удаляю его, печатаю другое число и нажимаю Enter.

Результат изменился. Это произошло из-за того, что поменялось значение в B2. Ведь формула у нас следующая: =B2*C2

Это означает, что программа Microsoft Excel умножает содержимое ячейки B2 на содержимое ячейки C2, каким бы оно не было. Выводы делайте сами

Попробуйте составить такую же таблицу и вычислить сумму в оставшихся ячейках (D3, D4, D5).

Устанавливаем курсор в поле «Число1» и, как всегда, зажав левую кнопку мыши, выделяем совокупность. После того, как координаты попали в поле, не спешим жать на кнопку «OK», так как результат получится некорректным. Прежде нам нужно вернуться к окну аргументов оператора ДОВЕРИТ.СТЮДЕНТ, чтобы внести последний аргумент. Для этого кликаем по соответствующему наименованию в строке формул.

Функция интервал в excel

Пример 1. Студенты одной из групп в университете сдали экзамен по физике. При оценке качества сдачи экзамена используется 100-бальная система. Для определения окончательной оценки по 5-бальной системе используют следующие критерии:

Пример использования функции ЧАСТОТА в Excel

Пример 1. Студенты одной из групп в университете сдали экзамен по физике. При оценке качества сдачи экзамена используется 100-бальная система. Для определения окончательной оценки по 5-бальной системе используют следующие критерии:

Для статистики необходимо определить, сколько студентов получили 5, 4, 3 баллов и количество тех, кому не удалось сдать экзамен.

Для решения выделим области из 4 ячеек и введем следующую функцию:

Выделяем диапазон F3:F6 жмем сначала клавишу F2, а потом комбинацию клавиш Ctrl+Shift+Enter, чтобы функция ЧАСТОТА была выполнена в массиве. Подтверждением того что все сделано правильно будут служить фигурные скобки в строке формул по краям. Это значит, что формула выполняется в массиве. В результате получим:

То есть, 6 студентов не сдали экзамен, оценки 3, 4 и 5 получили 3, 4 и 5 студентов соответственно.

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

Экспоненциальный график в excel

• в Е2 — будет содержаться количество значений из А2:А101, которые меньше или равны 10;
• в Е3 — количество значений из А2:А101, которые меньше или равны 20, но больше 10;
• в Е11 — количество значений из А2:А101, которые меньше или равны 100, но больше 90;
• в Е12 — количество значений из А2:А101, которые больше 100 (таких нет, т.к. исходный массив содержит числа от 1 до 100).

При работе с формулами в Excel, можно время от времени сталкиваться с различными ошибками. Так в примере ниже функция ВПР вернула ошибку #Н/Д из-за того, что в базе данных по ФИО нет искомой нами фамилии (более подробно об ошибке #Н/Д вы можете прочитать в этой статье: «Как исправить ошибку #Н/Д в Excel»)

Excel works!

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

В студенческие годы часто приходилось слышать, фразы типа: «Зачем мы вообще учим ‘это’, в жизни нам ‘это’ никогда не пригодиться». Одним из таких ‘это’ часто была экспонента или, например, факториал . У меня была слабая высшая математика при первом образовании, о чем я жалею. И вот сейчас приходиться догонять, темы что упустил раньше. Делюсь пересказом своих знаний.

Экспонента. Что это такое?

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

Число e — это т.н. число Эйлера, приблизительно равное 2,72. Примечательно оно тем, что производная от этой функции равна самой функции exp(x)` = exp(x).

Лучше всего, действие экспоненты показывают графики ниже:

Две функции: y = 2 в степени x и y = e в степени x , где x = время, к примеру. Мы видим, что скорость роста экспоненциального графика увеличивается быстрее. А все почему? Потому, что производная (скорость роста или уменьшения) функции равна самой функции, т.е. скорость увеличения функции равна значению функции.

Если грубо, то в природе, это действительно встречается часто — чем больше клеток делятся, тем быстрее их становиться больше. Чем больше у вас денег в банке, тем большую прибыль они приносят. Например:

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

Кстати население на планете Земля тоже растет по экспоненте;)

Принцип Парето и экспонента

Слышали о таком принципе? Думаю да. «20% усилий приносят 80% результата». Это он. Лучшее определение для запоминания, мне кажется:

На принципе Парето построен и ABC анализ запасов, например.

Кстати очень справедливый закон в реальной жизни, подтверждаю своим опытом.Когда-то на первом своем проекте я заметил, что примерно за 20% времени ты создаешь 80% продукта (в количественном эквиваленте), далее работаешь на качество. Т.е. еще 80% времени допиливаешь, ищешь ошибки, настраиваешь. Я даже слышал, что говорят «разработка в стадии экспоненты» — т.е. в стадии приближения к идеалу.

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

Экспонента в Excel

Когда по смыслу функции более-менее объяснил, напишу как считать ее и есть ли функция экспонента в Excel. Само собой есть.

Дополнение. Как запомнить 15 знаков числа е?

В качестве отвлечения привожу способ, как запомнить число e c точностью до 15 знаков

• запишите 2,7,
• затем дважды год рождения Льва Толстого – 1828,
• затем величины углов равнобедренного прямоугольного треугольника – 45°, 90°, 45°,
• в итоге получается: 2,718281828459045.

Как фанат Льва Николаевича, я не смог забыть этот способ 🙂 Кстати отличная книга о том как запоминать много информации и как работает память здесь .

Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью экспоненциальной функции.

Метод наименьших квадратов (англ. Ordinary Least Squares, OLS) является одним из базовых методов регрессионного анализа в части оценки неизвестных параметров регрессионных моделей по выборочным данным. Основная статья про МНК – МНК: Метод Наименьших Квадратов в MS EXCEL.

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

В свою очередь экспоненциальную зависимость y=a*EXP(b*x) при a>0 можно свести к случаю линейной зависимости с помощью замены переменных (см. файл примера ).

После замены переменных Y=ln(y) и A=ln(a) вычисления полностью аналогичны линейному случаю Y=b*x+A. Для нахождения коэффициента a необходимо выполнить обратное преобразование a= EXP(A) .

Если при росте х значения y уменьшаются по экспоненциальной кривой, т.е. a файл примера лист Экспонента3 ).

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

Как построить график функции в Excel – База знаний Timeweb Community

Выделяем диапазон, в котором представлены экспоненты. Переходим во вкладку «Вставка». На ленте в группе настроек «Диаграммы» нажимаем на кнопку «График». Открывается список графиков. Выбирайте тот тип, который считаете более подходящим для выполнения конкретных задач.

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

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

Рассматриваемая функция была заменена функцией ДОВЕРИТ.НОРМ с версии Excel 2010. Функция ДОВЕРИТ была оставлена для обеспечения совместимости с документами, созданными в более ранних версиях табличного редактора.

Пример 1. В заводском цехе производят деталь, длина которой должна составлять 200 мм. Стандартное отклонение от длины – 3,6 мм. Для контроля качества деталей из партии (генеральная совокупность) делают выборку из 25 деталей. Определить интервал с доверительный уровнем 95%.

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

То есть, границы доверительного интервала соответствуют: (Xср-1,4112;Xср+1,4112). Допустим, было определено среднее значение выборки – 199,5 мм. Тогда доверительный интервал примерно определяется как (198,1;200,9), при этом номинальная длина детали (200 мм) находится в доверительном диапазоне, то есть производственный процесс не нарушен.

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

Как найти границы доверительного интервала в Excel

• альфа – обязательный, принимает числовое значение, характеризующее уровень значимости – вероятность отклонения нулевой (неверной) гипотезы в том случае, когда она на самом деле верна. Определяется как 1-, где  – уровень доверия (вероятность нахождения истинного значения некоторой оцениваемой величины в определенном интервале, называемом доверительным).
• стандартное_откл – обязательный, принимает значение стандартного отклонения величины для генеральной совокупности значений (в Excel предусмотрена функция для определения этой величины – СТАНДОТКЛОН.Г).
• размер – обязательный, принимает числовое значение, характеризующее количество точек данных в анализируемой выборке (ее размер).

1. Все аргументы функции должны указываться в виде числовых значений или данных, которые могут быть преобразованы в числа (например, текстовые строки с числами, логические ИСТИНА, ЛОЖЬ). В противном случае результатом выполнения функции ДОВЕРИТ будет код ошибки #ЧИСЛО!
2. Аргумент альфа должен быть указан числовым значением из диапазона от 0 до 1 (оба включительно). Иначе функция ДОВЕРИТ вернет код ошибки #ЧИСЛО! Аналогичная ошибка возникает в случаях, когда аргумент стандартное_откл задан числом, взятым из диапазона отрицательных значений или нулем.
3. Диапазон допустимых значений для аргумента размер – от 1 до бесконечности со знаком плюс.

Функция ЧАСТОТА используется для определения количества вхождения определенных величин в заданный интервал и возвращает данные в виде массива значений. Используя функцию ЧАСТОТА, мы узнаем, как посчитать частоту в Excel.

График функции y=sin(x)

y=sin(x) – вторая функция, которую мы возьмем за пример. Может показаться, что ее составление осуществляется сложнее, хотя на самом деле это не так. Дело в том, что Excel сам посчитает значения, а вам останется только задать известные числа и вставить простой линейный график для вывода результатов на экран.

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

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

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

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

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

Чтобы получить значения функции в таблице, выделяем диапазон ячеек F2:J7, который содержит исходные значения процентных ставок, исходные значения срока погашения ссуды и расчетную функцию. После этого нужно выполнить команду меню Данные > Анализ «что если» > Таблица подстановки. В результате появится диалоговое окно (рис. 3.5).

Ежемесячные выплаты по ссуде

Устанавливаем курсор в поле «Число1» и, как всегда, зажав левую кнопку мыши, выделяем совокупность. После того, как координаты попали в поле, не спешим жать на кнопку «OK», так как результат получится некорректным. Прежде нам нужно вернуться к окну аргументов оператора ДОВЕРИТ.СТЮДЕНТ, чтобы внести последний аргумент. Для этого кликаем по соответствующему наименованию в строке формул.