Adelitusn.ru

ПК и Техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

ЧАСТОТА в 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>.

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

Как использовать функцию ЧАСТОТА в Excel?

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

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

Пример # 1

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

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

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

Чтобы вычислить частоту, сначала выберите пять последовательных ячеек (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.

Функции предсказания в 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:

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

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

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

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

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

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

Автоматическая загрузка котировок акций и валюты: новые функции EXCEL

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

Как это работает?

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

Тип данных EXCEL - Акции

После этого EXCEL предлагает уточнить во вкладке «Выбор данных», о какой конкретно ценной бумаге идет речь. Это необходимо, так как данные могут быть загружены с разных бирж (NYSE, NASDAQ, Лондонская биржа — LSE, Шанхайская биржа – SSE и т.п.). Важно, что в перечне рынков присутствует и Московская биржа ( полный список биржевых площадок ). Это, значит, что есть возможность анализировать наборы бумаг с разных бирж, что довольно удобно.

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

EXCEL загрузка котировок акций

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

  • Текущая Цена
  • Цена закрытия
  • Изменение цены (в %)
  • Название биржи
  • Тикер
  • Валюта бумаги
  • Время последних торгов (полезно для зарубежных бирж)

А также некоторые фундаментальные характеристики бумаг:

  • Капитализацию
  • Количество обыкновенных акций
  • Количество сотрудников компании
  • Расположение главного офиса
  • Сектор экономики
  • Год создания компании
  • P/E
  • Коэффициент бета

Важно, что кроме акций компаний доступна так же информация по ETF (в том числе по ETF и БПИФ Московской биржи).

Данные можно обновить в любой момент, нажав на «Обновить» на вкладке «Данные». Автоматическое обновление довольно просто настроить при помощи VBA.

EXCEL обновление финансовых данных

Загрузка курса валют

Загрузка данных по валютным парам очень похожа на работу с акциями. В ячейке необходимо ввести обозначение валютной пары в произвольном формате. Например, для получения курса доллара США к рублю – USD/RUB. После этого на вкладке «Данные» выбираем тип данных «Акции» (немного странно, но именно так необходимо сделать). EXCEL автоматически распознает валютную пару и поменяет ее отображение в ячейке, подставив специальный значок финансовых данных.

Автоматическая загрузка курса валют в EXCEL. Встроенные функции

Для получения данных по валютной паре в выпадающем списке выбираем необходимый параметр. Например, для получения курса валюты – Price (пока все финансовые параметры не переведены на русский язык).

Microsoft на этот раз не поскупились. Среди валют доступны даже некоторые криптовалюты. Например, для получения данных по Биткоину достаточно ввести символ валютной пары Биткоин/Доллар (BTC/USD). Кроме биткоина доступных котировки эфириума, XRP и других популярных сегодня криптовалют.

Новый синтаксис для финансовых функций EXCEL

Довольно удобен синтаксис новых финансовых функций. После того, как в ячейке выбран тип данных «Акции». В любой другой ячейке можно сделать ссылку на нее, поставить «.» и выбрать нужную функцию из выпадающего списка. Например, как в примере с Microsoft, можно в ячейке набрать B2.[P/E]

Новые финансовые функции EXCEL. Информация по ценным бумагам

Недостатки

Они тоже, на мой взгляд, имеются. Например, нельзя посмотреть дивиденды по бумаге. Нет цены типа Adjusted Close, которая бы учитывала дивидендную доходность. Это ограничивает сколько-нибудь серьезное использование новых возможностей для отслеживания доходности ценной бумаги или набора ценных бумаг (портфеля).

Кроме того нет возможности посмотреть историю изменения цены или других параметров (TimeSeries).

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

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

Файлы для скачивания

Отслеживание портфеля акций в EXCEL
Файл: stocks.xlsx
Размер: 98812 байт

Для скачивания файлов необходимо зарегистрироваться или авторизоваться

Функция Excel COUNT

Подсчет в Excel с функцией COUNT и ярлыком Подсчет чисел

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

Каждый член этой группы выполняет свою работу несколько иначе, а функция Count () состоит в том, чтобы считать только числа. Это можно сделать двумя способами:

  1. Tt будет суммировать те ячейки в выбранном диапазоне, которые содержат числа;
  2. Он будет суммировать все числа, перечисленные в качестве аргументов для функции.

Что такое число в Excel?

В дополнение к любому рациональному числу – например, 10, 11.547, -15 или 0 – существуют другие типы данных, которые хранятся в виде чисел в Excel, и поэтому они будут учитываться функцией Count (), если они включены в аргументы функции. Эти данные включают в себя:

  • Даты и время
  • функции
  • Формулы
  • Булевы значения

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

Подсчет чисел ярлык

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

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

Но поскольку функция Count () так хорошо используется, была включена и третья опция – функция Подсчет чисел .

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

Что считается и почему

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

Будут подсчитаны условные формулы, которые возвращают нули и единицы, представляющие истину и ложь. Однако чистое логическое значение, такое как TRUE или FALSE, не включается в формулу Count ().

Синтаксис и аргументы функции COUNT

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

Синтаксис для функции COUNT:

Первый аргумент, value1 , обязателен; он включает значения данных или ссылки на ячейки, включенные в счетчик. Дополнительные аргументы, разделенные запятыми, необязательно указывают дополнительные значения данных или ссылки на ячейки, которые должны быть включены в счетчик. Максимально допустимое количество записей – 255.

Каждый аргумент может содержать:

  • Числа или логические значения, введенные непосредственно как один из аргументов функции
  • Отдельные ячейки ссылаются на расположение данных на листе
  • Диапазон ссылок на ячейки
  • Именованный диапазон

Связанные функции подсчета

В дополнение к Count (), Excel поддерживает несколько дополнительных функций:

голоса
Рейтинг статьи
Читайте так же:
Как открыть документ pdf в Word
Ссылка на основную публикацию
Adblock
detector