Функции пророчества в Excel

Excel, как всепригодный табличный редактор, издавна и хорошо совладевает с большинством задач прогнозирования (см. перечень литературы в конце заметки). Но, не постоянно вычисления в Excel являются ординарными и понятными. И вот в версии 2016 года создатели Microsoft добавили семейство функций ПРЕДСКАЗ (FORECAST), которые разрешают в несколько кликов решать большенный круг задач прогнозирования на базе экспоненциального выравнивания.

Рис. 1. Прогнозирование продаж в Excel при помощи семейства функций ПРЕДСКАЗ

Скачать заметку в формате Word либо pdf, примеры в формате Excel

О экспоненциальном выравнивании

Экспоненциальное выравнивание также понятно, как способ ETS: ошибки (Errors), тренд (Trend), сезонный фактор (Seasonal). Для составления прогноза употребляются все исторические данные, но коэффициенты, определяющие вклад, убывают в прошедшее по экспоненте (отсюда и заглавие). Это дозволяет, с одной стороны, чутко реагировать на свежайшие данных, с иной стороны, сохранять информацию о историческом поведении всего временного ряда. Если данным присущ тренд, он рассчитывается в каждой точке данных (а не на базе регрессии всего временного ряда). В конце концов, при помощи автокорреляции в данных выявляется сезонность.

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

Фактически, оптимизируются три коэффициента:

α – разброс относительно среднего

Создатели Microsoft не предоставили юзерам возможность влиять на выбор коэффициентов, кроме периода сезонности (о этом ниже).

Обзор функций семейства ПРЕДСКАЗ

В Excel представлено 5 функций:

Рис. 2. Семейство функций ПРЕДСКАЗ в Excel

ПРЕДСКАЗ.ETS рассчитывает будущее значение на базе имеющихся (ретроспективных) данных способом экспоненциального выравнивания. Т.е., дает прогноз одним числом.

ПРЕДСКАЗ.ЕTS.ДОВИНТЕРВАЛ возвращает доверительный интервал для прогнозной величины. Доверительный интервал следует отложить по обе стороны от среднего значения. Совместно с ПРЕДСКАЗ.ETS дозволяет выстроить «коридор» прогноза.

ПРЕДСКАЗ.ETS.СЕЗОННОСТЬ возвращает длину циклического фрагмента, найденного в данном временном ряду. К примеру, 12, если исторические данные представляют из себя реализации в месяц.

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

ПРЕДСКАЗ.ЛИНЕЙН вычисляет будущее значение при помощи линейной регрессии исторических данных. До версии 2016 в Excel заместо семейства функций была единственная функция ПРЕДСКАЗ, которая работала также, как и ПРЕДСКАЗ.ЛИНЕЙН. Функция ПРЕДСКАЗ оставлена для оборотной сопоставимости, но скоро не станет поддерживаться. Дальше в заметке ПРЕДСКАЗ.ЛИНЕЙН не рассматривается, потому что не относится к функциям, использующим метод экспоненциального выравнивания.

ПРЕДСКАЗ.ETS

В качестве примера разглядим месячный пассажиропоток в аэропорту (пример от MS). Исторические данные были собраны за период с января 2009 по декабрь 2912 г.

Рис. 3. Исторические данные

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

Рис. 4. Прогнозные значения на базе функции ПРЕДСКАЗ.ETS

Подробнее о формуле в ячейке С50:

1-ый аргумент – целевая_дата = А50 – янв.13, т.е., в ячейке С50 ищется прогноз пассажиропотока для января 2013 г. Ссылка относительная, что дозволит при протягивании функции вниз по столбцу ссылаться на новое значение: в С51 – на А51, в С52 – на А52 и т.д.

2-ой аргумент – значения = $B$2:$B$49. Тут размещены исторические данные пассажиропотока. Ссылка абсолютная, чтоб при протягивании формулы ячейки, на которые ссылаются не поменялись.

3-ий аргумент – временная_шкала = $A$2:$A$49. Тут размещены даты временной шкалы либо номера периодов. Принципиально чтоб они отстояли друг от друга на фиксированный интервал. Если интервал не будет фиксированным, Excel всё еще будет исходить из догадки, что интервал фиксированный, а некие данные пропущены. Как обрабатываются такие ситуации описано ниже. Сортировать массив по значениям временной шкалы не непременно, потому что ПРЕДСКАЗ.ETS сама отсортирует данные до этого, чем выполнить расчеты.

4-ый аргумент – [сезонность] = 1. Это необязательный аргумент. Значение по дефлоту равно 1. Для него Excel автоматом описывает сезонность и употребляет положительные целые числа в качестве длины сезонного шаблона. Значение 0 предписывает не употреблять фактор сезонности, в итоге что прогноз будет линейным. Если для этого параметра задано положительное целое число, метод употребляет его в качестве длины шаблона сезонности. К примеру, вы понимаете, что сезонность равна 4 (квартальная периодичность), но предполагаете, что она слабенькая, и автоматический метод Excel может ее не выявить, и будет считать, что сезонности нет. Для начала я рекомендовал бы употреблять значение по дефлоту.

5-ый аргумент – [заполнение_данных] = 1. Это необязательный аргумент. Хотя временная шкала просит неизменный шаг меж точками данных, FORECAST.ETS поддерживает до 30% отсутствующих данных и автоматом настраивает их. 0 показывает, что метод учитывает отсутствующие точки в качестве нулей. Если задано значение 1 (вариант по дефлоту), функция описывает отсутствующие значения как среднее меж примыкающими точками.

6-ой аргумент – [агрегирование] – в нашем примере опущен. Это необязательный аргумент. Он нужен, если даты временной шкалы либо номера периодов содержат дубли. Функция ПРЕДСКАЗ.ETS выполнит агрегирование точек с схожей меткой времени. Параметр агрегирования — это числовое значение, определяющее метод агрегирования нескольких значений с схожей меткой времени. Для значения по дефлоту 0 употребляется способ СРЗНАЧ; также доступны варианты СУММ, СЧЁТ, СЧЁТЗ, МИН, МАКС и МЕДИАНА.

ЧАСТОТА в Excel (функция, формула, примеры) | Как воспользоваться?

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

Формула ЧАСТОТЫ в Excel

Ниже представлена ​​формула ЧАСТОТЫ в Excel.

Аргументы, применяемые для формулы ЧАСТОТА в Excel.

• Data_array Неотклонимый. Массив либо ссылка на набор значений, для которых необходимо подсчитать частоты.
• Bins_array Неотклонимый. Массив либо ссылка на интервалы, в которые должны быть сгруппированы значения в data_array .

Разъяснение функции ЧАСТОТА в Excel

Частота возвращает массив значений и, как следует, ее нужно ввести как формулу массива, другими словами надавить CTRL + Shift + Enter (либо Command + Shift + Enter для Mac). Ячейки, в которые требуется вывод, эти ячейки должны быть выбраны в первую очередь, а потом вводится формула ЧАСТОТА в excel, опосля что она вводится как формула массива.

Изберите ячейки à Введите формулу à Нажмите CTRL + Shift + Enter.

Возврат

ЧАСТОТА Функция в Excel возвращает частотное распределение data_array в интервалах bins_array . Итог постоянно на единицу больше, чем количество частей в bins_array. Доп элемент в возвращаемом массиве соответствует количеству значений, превосходящих самый высочайший элемент bins_array . Представим, что bins_array содержит три элемента <2, 4, 6>, функция возвратит четыре элемента <6>.

Если массив данных _ не содержит значений, функция ЧАСТОТА Excel возвращает массив нулей. Если bins_array не содержит значений, функция ЧАСТОТА Excel возвращает полное количество частей, обозначенных в data_array .

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

ЧАСТОТА в Excel — Иллюстрация

Представим, у вас есть числа, для которых вы желаете высчитать частоту. Числа <1, 3, 2, 4, 6, 2, 3, 4, 5>приведены в B3: B11.

Номера должны быть разбиты на интервалы: <2, 4, 6>, обозначенные в D3: D5.

Чтоб вычислить частоту, поначалу изберите четыре ячейки E3: E6, а потом последующий синтаксис:

= ЧАСТОТА (B3: B11; D3: B5)

и нажмите CTRL + Shift + Enter.

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

Он возвратит частоту.

Данный выход <3, 4, 2, 0>соответствует интервалу <6>.

Если вы выберете лишь три ячейки заместо 4, счет «больше 6» будет пропущен, как показано ниже.

Как употреблять функцию ЧАСТОТА в Excel?

Функция ЧАСТОТА в Excel весьма ординарна и комфортна в использовании. Давайте разберемся с работой FREQUENCY in excel на неких примерах.

Вы сможете скачать этот шаблон Excel с функцией ЧАСТОТА тут — Шаблон Excel с функцией ЧАСТОТА

Пример # 1

Представим, вы провели опрос и собрали данные о высоте, как показано ниже.

Сейчас вы желаете высчитать частоту роста в последующих интервалах:

Интервалы <155, 160, 165, 170>приведены в E4: E7.

Чтоб вычислить частоту, поначалу изберите 5 поочередных ячеек (4 + 1).

Потом введите последующий синтаксис:

= ЧАСТОТА (B4: B14; E4: E7)

и нажмите CTRL + Shift + Enter.

Он возвратит частоту.

Пример # 2

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

Сейчас все те, кто не прошел (по одному либо нескольким предметам), будут считаться «Лузерами». Сейчас для вас необходимо знать количество студентов, которые проиграли.

Чтоб найти это, вы сможете употреблять последующий синтаксис:

= СУММ (- (ЧАСТОТА (B4: B9; B4: B9)> 0))

Давайте тщательно разглядим синтаксис:

ЧАСТОТА (B4: B9, B4: B9) вычислит частоту данных B4: B9 с внедрением интервала B4: B9. Он возвратит

FREQUENCY (B4: B9, B4: B9)> 0 инспектирует, больше ли приобретенная частота нуля. Он возвращает логическое ИСТИНА, если оно больше нуля, по другому ЛОЖЬ. Он возвратит

СУММ (- (ЧАСТОТА (..)> 0)) потом суммирует ИСТИНА и возвращает количество неповторимых значений.

Пример # 3

Представим, у вас есть данные о клиентах, посещенных в гипермаркете за денек, также время их посещения в ячейках B4: C20, как показано ниже.

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

Давайте поначалу определимся с временным интервалом. Мы можем употреблять последующие интервалы для простоты:

• 11:00 УТРА
• 12:00 УТРА
• 1:00 ВЕЧЕРА
• 14:00
• 3:00 ВЕЧЕРА
• 4:00 ВЕЧЕРА
• 5:00 ВЕЧЕРА
• 18:00
• 7:00 ВЕЧЕРА
• 8:00 ВЕЧЕРА

Сейчас изберите ячейки в частотной таблице, чтоб получить. G4: G13 в данном случае. Так как магазин запирается в 20:00, мы не избираем ячейку для> 20:00, потому что во всех вариантах она будет равна нулю.

Сейчас введите последующий синтаксис:

= ЧАСТОТА (B4: C39, G4: G13)

и нажмите CTRL + Shift + Enter.

Он возвратит частоту посещения магазина покупателем. В этом случае большая часть посещений приходилось на период с 17:00 до 18:00.

Топ 20 самых принципиальных формул MS Excel

Microsoft Excel — один из самых фаворитных инструментов анализа данных в мире. Больше конторы употребляют это программное обеспечение и много людей раз в день пользуются Excel. Но, по моему опыту, лишь немногие из их в полной мере употребляют способности, которые может предложить эта программка. Я представляю Для вас мой перечень из топ 20 самых нужных формул Excel, которые любой должен употреблять для увеличения эффективности. Функции Excel также весьма рекомендуются для вычислений, потому что они могут существенно уменьшить количество ошибок. И крайнее, но не наименее принципиальное: исследование этих функций — красивая возможность сделать лучше Ваши познания Excel и стать уверенным юзером MS Excel.

Table of Contents

1. Sum

«Sum» — это, наверняка, самая обычная, да и важнейшая функция Exceл. Вы сможете употреблять эта формула для вычисления суммы спектра ячеек.

В этом примере 1-ая формула суммирует ячейки от A1 до A10. 2-ая суммирует лишь ячейки A1 и A10. Вы сможете испытать с ним и попробовать употреблять различные спектры, чтоб привыкнуть к данной для нас функций. Направьте внимание, что вы также сможете употреблять числа снутри данной для нас функции заместо ссылок.

2. Average

Еще одна весьма принципиальная функция. Как надо из наименования, эта формула оценивает среднее число из спектра ячеек. Представим, что мы имеем спектр чисел в столбце A.

Это возвратит среднее значение ячеек от A1 до A10.

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

Тут формула проверит если реализации в ячейке «A2» наиболее 100 €. Если это заявление правильно, формула возвращает «да». Если реализации ниже 100 евро, формула возвращает «нет».

4. Sumif

Sumif — еще одна весьма принципиальная формула Excel. Формула будет суммировать числа, если производится определенное условие.

Давайте разглядим приведенный выше пример. У нас есть торговые агенты, которые регистрируют определенное количество продаж. Мы желаем выяснить полное количество продаж для всякого агента. Функция = SUMIF (A1: A20, A2, B1: B20) суммирует значения ячеек B1: B20, которые соответствуют тексту в ячейке A2 (в случае «Ивайло»). Юноша зарегистрировал общий размер продаж 325 €. Мы можем употреблять формулу для расчета оборота остальных агентов.

5. Countif

Countif — весьма нужная функция, которая работает как sumif. Он суммируется при определенном условии. Разница меж sum и count состоит в том, что функция count суммирует количество ячеек, где определенное условие поистине. Напротив, суммирующая функция суммирует значения снутри ячеек.

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

PS: Если вы не убеждены в синтаксисе функции, вы постоянно сможете употреблять функцию «insert function» в Excel (просто нажмите символ функции в левой части многофункциональной панели). В последующем примере мы продемонстрируем, как это работает.

6. Counta

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

Это даст для вас количество строк, которые содержат значение, текст либо хоть какой иной знак.

7. Vlookup

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

Продолжим этот пример, добавив очередной столбец «А» с номерами клиентов.

Направьте внимание, что есть информация о продажах и номерах клиентов, но имена клиентов отсутствуют. Но у нас еще есть один лист Excel (Sheet2) с именами клиентов и номерами клиентов.

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

Эта формула отыскивает значение в ячейке A2 (112 в нашем случае) в листе2, столбцы A & B (знаки «$» означают, что мы постоянно желаем иметь неизменный спектр поиска). Потом функция возвращает значение из второго столбца. Крайняя часть формулы, мы пишем 0. Таковым образом, мы делаем Excel не находить совпадение в порядке чисел.

Сейчас попытайтесь сделать свою формулу vlookup, используя опцию insert function.

В нашем случае это смотрится так:

8. Left, Right, Mid

Left, right и mid функции — весьма принципиальные функции, которые разрешают извлекать определенное количество знаков из строчки

В нашем примере с формулой left мы берем 1-ый знак с левой стороны. С формулой right мы можем создать то же самое, но с знаками справа влево. Имейте в виду, что пустое место также является эмблемой.

9. Trim

Еще одна исключительная функция MS Excel. Функция trim удаляет пустые места опосля слов, когда их больше 1-го. Это может случиться достаточно нередко в Excel. Может быть, что информация извлекается из различных баз данных и заполняется ненадобными пустым местом меж словами. Это быть может большой неувязкой, поэтому что ваши формули Excel могут не работать. Чтоб избавиться от раздражающих пустых пространств, мы используем формулу trim. = TRIM(A1)

В этом примере формула удалит излишние пробелы, если она отыщет несколько.

10. Concatenate

Это комфортная функция Excel, которая помогает, когда мы желаем соединить ячейки. Тут мы желаем соединить ячейки A1, A2 и A3. Это можно создать при помощи последующей функции:

Таковым образом, у нас не будет пробелов меж словами. Чтоб добавить пробелы меж «I» и «love», нам необходимо добавить пробел. Это можно решить, добавив кавычки снутри функции:

PS: Вы также сможете соединить ячейки, просто добавив:

11. Len

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

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

12. Max

Функция max возвращает наибольшее значение из спектра.

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

13. Min

Функция min работает аналогично функции max, но она дает обратный итог. Он извлекает самое низкое значение из спектра данных.

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

14. Days

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

Формула возвратит количество прошедших дней.

15. Networkdays

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

16. SQRT

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

Это возвратит квадратный корень из 1444 (38). Вы также сможете употреблять ссылку на ячейку тут, как и в почти всех остальных формулах Excel.

17. Now

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

Это возвратит текущую дату. Не забудьте установить формат на нынешний денек. Вы сможете испытать с ним столько, сколько желаете. Вы сможете, к примеру, добавлять либо вычитать деньки. = Now () -14 возвратит дату до 2-ух недель.

18. Round

Функция Round весьма полезна, когда для вас приходится работать с округлыми номерами. Excel может показывать числа для определенного знака, но сохраняет начальный номер и употребляет его для вычислений. Но в неких вариантах это быть может неувязкой, и нам необходимо работать лишь с округлыми номерами. В таковых вариантах функция ROUND обращается к нашей помощи. = ROUND(B1,2)

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

19.Roundup

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

Тут мы желаем отыскать в каком квартале текущего года находится текущую дату. Секрет данной для нас формулы — обычная математика. Функция воспринимает номер месяца, к примеру, январь равен 1, февраль равен 2 и т. Д., А потом разделяет его на 3, округляя до наиблежайшего большего числа.

20. Rounddown

Rounddown работает аналогично Roundup, но она округляет число до наиблежайшего целого числа

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