Что можно отнести к аналитическим функциям excel
Microsoft Excel предлагает средства для анализа статистических данных. Такие встроенные функции, как СРЗНАЧ (AVERAGE), МЕДИАНА (MEDIAN) и МОДА (MODE), могут использоваться для проведения анализа данных. Если встроенных статистических функций недостаточно, необходимо обратиться к пакету Анализ данных.
Пакет Анализ данных, являющийся надстройкой, содержит коллекцию функций и инструментов, расширяющих встроенные аналитические возможности Excel. В частности, пакет Анализ данных можно использовать для создания гистограмм, ранжирования данных, извлечения случайных или периодических выборок из набора данных, проведения регрессионного анализа, получения основных статистических характеристик выборки, генерации случайных чисел с различным распределением, а также для обработки данных с помощью преобразования Фурье и других преобразований.
Пакет Анализ данных доступен при каждом запуске Excel. Функции пакета Анализ данных можно использовать точно так же, как и любые другие функции Excel, а чтобы получить к ним доступ, выполните описанные ниже действия:
1. Выберите в меню Сервис команду Анализ данных. При первом выборе этой команды Excel загружает файл с диска. Затем на экране появится окно диалога Анализ данных (рис. 2.19).
Рис. 2.19. Окно диалога Анализ данных
2. Чтобы использовать какой-либо из инструментов анализа, выберите его имя в списке и нажмите кнопку ОК.
3. Заполните открывшееся окно диалога. В большинстве случаев это означает задание входного диапазона с данными, которые вы собираетесь анализировать, задание выходного диапазона, куда должны быть помещены результаты, и выбор нужных параметров.
При анализе данных часто возникает необходимость определения различных статистических характеристик или параметров распределения. С помощью Microsoft Excel можно анализировать распределение, используя несколько инструментов: встроенные статистические функции, функции для оценки разброса данных, инструмент Описательная статистика (Descriptive Statistics), который предоставляет удобные сводные таблицы основных параметров распределения, инструменты Гистограмма (Histogram), Ранг и персентиль (Rank and Percentile).
Встроенные статистические функции Microsoft Excel применяются при проведении статистического анализа данных. В данном разделе мы ограничимся обсуждением наиболее часто используемых статистических функций. Кроме них Excel также предлагает более сложные функции ЛИНЕЙН (LINEST), ЛГРФПРИБЛ (LOGEST), ТЕНДЕНЦИЯ (TREND) и РОСТ (GROWTH), которые работают с числовыми массивами.
Описательная статистика (Descriptive Statistics) позволяет создать таблицу основных статистических характеристик для одного или нескольких множеств входных значений. Выходной диапазон содержит таблицу со статистическими характеристиками для каждой переменной входного диапазона: среднее, стандартная ошибка, медиана, мода, стандартное отклонение и дисперсия выборки, коэффициент эксцесса, коэффициент асимметрии, размах, минимальное значение, максимальное значение, сумма, количество значений, k-е наибольшее и наименьшее значения (для любого заданного k) и доверительный интервал для среднего.
Для использования Описательная статистика в меню Сервис выберите команду Анализ данных, затем в списке Инструменты анализа окна диалога Анализ данных выберите инструмент Описательная статистика и нажмите кнопку ОК. Появится окно диалога, показанное на рис. 2.20.
Рис. 2.20. Окно диалога Описательная статистика
Инструмент Описательная статистика требует задания входного диапазона, который может содержать одну или несколько переменных, и выходного диапазона. Вы должны также указать, как расположены переменные в столбцах или в строках. Установите флажок Метки в первой строке, если первая строка во входном диапазоне содержит названия столбцов. Excel использует эти метки для создания заголовков в выходной таблице.
Чтобы получить представленную выше таблицу статистических характеристик, установите флажки в области Параметры вывода.
Подобно другим инструментам пакета анализа, Описательная статистика создает таблицу констант. Если эта таблица вас не устраивает, можно получить большинство из перечисленных ниже статистических характеристик с помощью других инструментов пакета анализа или формул с использованием встроенных функций Excel.
Анализ данных с помощью диаграмм
В MS Excel имеется возможность графического представления данных в виде диаграммы. Диаграммы связаны с данными листа, на основе которых они были созданы, и изменяются каждый раз, когда изменяются данные на листе.
Диаграммы могут использовать данные несмежных ячеек. Диаграмма может также использовать данные сводной таблицы.
Можно создать либо внедренную диаграмму, либо лист диаграммы. Внедренная диаграмма – это объект, расположенный на листе и сохраняемый вместе с листом при сохранении книги. Внедренные диаграммы также связаны с данными и обновляются при изменении исходных данных. Лист диаграммы – лист книги, содержащий только диаграмму. Листы диаграммы связаны с данными таблиц и обновляются при изменении данных в таблице.
Для того чтобы построить диаграмму, выделите ячейки, содержащие данные, которые должны быть отражены на диаграмме; если необходимо, чтобы в диаграмме были отражены и названия строк или столбцов, выделите также содержащие их ячейки; нажмите кнопку Мастер диаграмм и следуйте инструкциям Мастера.
Для создания диаграмм из несмежных диапазонов нужно выделить первую группу ячеек, содержащих необходимые данные, удерживая клавишу CTRL, выделить необходимые дополнительные группы ячеек и нажать кнопку Мастер диаграмм.
Большая часть текстов диаграммы, например подписи делений оси категорий, имена рядов данных, текст легенды и подписи данных, связана с ячейками рабочего листа, используемого диаграммой. Если изменить текст этих элементов на диаграмме, они потеряют связь с ячейками листа. Чтобы сохранить связь, следует изменять текст этих элементов в исходных таблицах.
Ряд данных – группа связанных точек данных диаграммы, отображающая значение строк или столбцов листа. Каждый ряд данных отображается по-своему. На диаграмме может быть отображен один или несколько рядов данных. На круговой диаграмме отображается только один ряд данных.
Чтобы изменить текст легенды или имя ряда данных на листе, выберите ячейку, содержащую изменяемое имя ряда, введите новое имя и нажмите клавишу ENTER.
Чтобы изменить текст легенды или имя ряда данных на диаграмме, выберите нужную диаграмму, а затем выберите команду Диаграмма – Исходные данные. На вкладке Ряды выберите изменяемые имена рядов данных. В поле Имя укажите ячейку листа, которую следует использовать как легенду или имя ряда. Также можно просто ввести нужное имя. Если в поле Имя ввести имя, то текст легенды или имя ряда потеряют связь с ячейкой листа.
Чтобы изменить подписи значений на листе, необходимо выбрать ячейку, содержащую изменяемые данные, ввести новый текст или значение и нажать клавишу ENTER. Чтобы изменить подписи значений на диаграмме, надо один раз щелкнуть мышью изменяемую подпись, чтобы выбрать подписи для всего ряда, и щелкнуть еще раз, чтобы выбрать отдельную подпись значения. Ввести новый текст или значение и нажать клавишу ENTER. Если изменить текст подписи значений на диаграмме, то связь с ячейкой листа будет потеряна.
При большом диапазоне изменения значений для разных рядов данных в линейчатой диаграмме или при смещении типов данных (таких, как цена и объем) есть возможность отобразить один или несколько рядов данных на вспомогательной оси. Шкала этой оси соответствует значениям для соответствующих рядов:
– выберите ряды данных, которые нужно отобразить на вспомогательной оси, щелчком мыши;
– Формат – Ряды – вкладка Ось;
– установите переключатель в положение По вспомогательной оси.
Для большинства плоских диаграмм можно изменить диаграммный тип ряда данных или диаграммы в целом. Для объемной диаграммы изменение типа диаграммы может повлечь за собой и изменение диаграммы в целом. Порядок преобразования рядов данных в конусную, цилиндрическую или пирамидальную диаграммы:
– выберите диаграмму, которую необходимо изменить, а также ряд данных на ней. Для изменения типа диаграммы в целом на самой диаграмме ничего не нажимайте;
– Диаграмма – Тип диаграммы – на вкладках Стандартные или Нестандартные выберите необходимый тип.
Для использования типов диаграмм конус, цилиндр или пирамида в объемной диаграмме или гистограмме выберите в поле Тип диаграммы в меню Стандартные пункт Цилиндр, Конус или Пирамида, а затем установите значок в поле Применить к.
Процедура изменения цветов, узора, ширины линии или типа рамки для маркеров данных, области диаграммы, области построения, сетки, осей и подписей делений на плоских и объемных диаграммах, линий тренда и планок погрешностей на плоских диаграммах, а также стенки и основания на объемных диаграммах:
– установить указатель на изменяемый элемент диаграммы и дважды нажать кнопку мыши;
– при необходимости выбрать вкладку Узор и указать нужные параметры.
Для указания эффекта заливки необходимо выбрать соответствующую команду, а затем указать нужные параметры на вкладках Градиентная, Текстура и Узор.
Работа с таблицами формата Список
Список – это упорядоченный набор данных, состоящий из строки заголовков (описания данных) и строк данных, которые могут быть числовыми и текстовыми.
Размер списка ограничен размерами одного рабочего листа, т.е. список может иметь не более 256 полей и не более 65 535 записей. Полями принято называть столбцы списка, а записями – строки.
Excel будет считать таблицу списком, если ее формат удовлетворяет следующим условиям:
– список обязательно должен содержать строку заголовков;
– в каждом столбце должна содержаться однотипная информация. Например, не следует смешивать в одном столбце даты и обычный текст;
– в списке не должно быть пустых строк;
– рекомендуется помещать список на отдельный лист. Но если все же на лист нужно поместить еще и другую информацию, следите, чтобы список от нее отделялся хотя бы одной пустой строкой и одним пустым столбцом. В противном случае вы рискуете приобрести, например, сотрудника с фамилией «Итого».
Для удобства работы с большими таблицами, воспользуйтесь командой Окно – Разделить. После того как на экране появятся разделительные линии, буксируйте их мышью таким образом, чтобы горизонтальная линия оказалась точно под строкой заголовков (от вертикальной линии можно отказаться, оттащив ее за пределы рабочего окна). Команда Окно – Закрепить области зафиксирует деление, и заголовки будут видны при прокручивании списка.
Excel обладает мощными средствами для работы со списками. Это:
– пополнение списка с помощью формы;
– подведение промежуточных итогов;
– создание итоговой сводной таблицы на основе данных списка.
Для того чтобы воспользоваться любым из этих инструментов, нужно установить курсор на одну из ячеек списка.
При вводе данные можно добавлять непосредственно в ячейки, а можно воспользоваться специальной формой ввода (рис. 2.21).
Рис. 2.21. Форма ввода данных
Если вы выбрали первый способ, то используйте команду контекстного меню Выбрать из списка. Excel избавит вас от необходимости много раз набирать один и тот же текст.
Если вы решили прибегнуть к помощи формы ввода, поместите курсор в любое место списка и выберите команду Данные – Форма. На экране появится диалоговое окно, в котором будет отображено каждое поле списка. При этом поля, содержащие формулы, хотя и отображаются в форме ввода, их значения изменить нельзя.
Индикатор в правом верхнем углу формы показывает номер выбранной записи и общее число записей в форме.
Чтобы ввести новую запись, щелкните по кнопке Добавить. Форма очистится, и вы сможете ввести нужную информацию в соответствующие поля. После этого снова щелкните по кнопке Добавить, а если не хотите больше добавлять записи – по кнопке Закрыть.
Вновь введенные данные появятся в конце списка. Формулы, содержавшиеся в ячейках списка, автоматически будут распространены и на новую запись
Форму ввода можно использовать не только для ввода данных. Она позволяет просматривать существующие записи, редактировать их, удалять и выборочно отображать данные по определенному критерию.
Фильтрация списков
В Excel существует два типа фильтров: Автофильтр и Расширенный фильтр.
Перед тем как использовать Автофильтр, выделите любую ячейку списка. Затем выберите команду Данные – Фильтр – Автофильтр. При включении Автофильтра возле имен полей списка появятся кнопки со стрелками.
При щелчке по любой из этих кнопок раскрывается меню (рис. 2.22), содержащее команды и список значений данного поля. С помощью этого меню можно отобрать все записи с заданным значением поля.
Рис. 2.22. Вид меню, содержащего команды и список значений поля
Обратите внимание на цвет стрелок на кнопках Автофильтра: если Автофильтр включен, кнопки окрашиваются в синий цвет.
Чтобы отключить ранее заданный фильтр, в раскрывающемся меню кнопок Автофильтра следует выбрать команду Все.
Если задан сложный критерий, то придется отменять составляющие условия отбора по очереди. Иногда бывает проще отказаться от Автофильтра, выбрав команду Данные – Фильтр – Автофильтр, а потом установить Автофильтр снова.
Кроме команды Все, в раскрывающемся меню кнопок Автофильтра есть еще одна команда Первые 10. которая используется для полей числового типа или дат. Эта команда покажет «горячую десятку» вашего списка.
Пусть необходимо узнать расходы за последние три дня. Щелкните по кнопке Автофильтра в столбце Дата, выберите в раскрываемся меню команду Первые 10. в диалоговом окне сделайте установки, как на рис. 2.23.
Рис. 2.23. Диалоговое окно установки расходов за последние 3 дня
В окне Наложение условия по списку можно установить любое количество наибольших (или наименьших) элементов, которое хотите отобразить. Если вы хотите оставить процент записей (например, 10% наименьших значений), в третьем окне вместо Элементов списка установите % от количества элементов. При создании сложного условия отбора команда Первые 10. всегда применяется ко всему списку.
Иногда стандартных условий Автофильтра оказывается недостаточно. Для создания собственного Автофильтра необходимо:
– для выбранного поля (например, Менеджер) из раскрывающегося меню кнопки Автофильтра выбрать команду (Условие…);
– в диалоговом окне Пользовательский автофильтр (рис. 2.24) задать условия отбора значений списка.
Рис. 2.24. Окно Пользовательский автофильтр
Если вы применяете Пользовательский автофильтр к текстовому полю, в качестве логической функции, связывающей условия, всегда выбирайте ИЛИ.
Для полей числового типа или дат используются следующие правила:
– И, когда интересует область между двумя числами или датами;
– ИЛИ, если интересует область вне интервала, заданного двумя числами или датами.
Расширенный фильтр
Часто для отбора нужной информации из списка бывает вполне достаточно Автофильтра или пользовательского фильтра. Однако для решения сложной задачи приходится прибегать к помощи расширенной фильтрации. Расширенный фильтр гораздо гибче Автофильтра, но чтобы воспользоваться им, придется выполнить подготовительные действия.
С помощью Расширенного фильтра (рис. 2.25) можно:
– определить более сложный критерий фильтрации;
– помещать результат отбора данных на другое место и даже на новый лист рабочей книги;
– устанавливать вычисляемый критерий отбора.
Рис. 2.25. Окно Расширенный фильтр
Чтобы воспользоваться Расширенным фильтром, необходимо задать диапазон критериев.
Диапазон критериев – область рабочего листа, в которой формируется условие (условия) отбора. Диапазон критериев должен состоять, по крайней мере, из двух строк, первая из которых содержит все или некоторые названия полей списка.
Удобнее всего отвести для диапазона критериев область над списком. Названия полей, не используемых при фильтрации, можно не помещать в диапазон критериев. Но если вы предполагаете, что в дальнейшем в зависимости от обстоятельств вам может понадобиться и другая информация из списка, скопируйте строку, содержащую названия полей списка, целиком.
Условия отбора следует вносить в пустые ячейки диапазона критериев. Условия отбора, расположенные в ячейках одной строки, соединяются оператором И. Условия, расположенные на разных строках, соединяются оператором ИЛИ. Диапазон критериев может состоять из любого количества строк.
Область ячеек, содержащих критерии, должна отделяться от списка, по крайней мере, одной пустой строкой.
Для того чтобы отключить Расширенный фильтр, используют команду Данные – Фильтр – Отобразить все.
При использовании вычисляемого критерия отбор производится «по несуществующему полю». При создании формул вычисляемых критериев всегда ссылайтесь на первую строку списка, а не на строку заголовков. Если в формулу будут подставляться значения вне списка, используют абсолютные ссылки.
Если отфильтрованный список должен быть помещен на другой лист рабочей книги, сначала переходят на этот лист и только потом обращаются к команде Данные – Фильтр – Расширенный фильтр.
Функции упорядочены по категориям в зависимости от функциональной области. Щелкните категорию, чтобы просмотреть относящиеся к ней функции. Вы также можете найти функцию, нажав CTRL+F и введя первые несколько букв ее названия или слово из описания. Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.
Ниже перечислены десять функций, которыми больше всего интересуются пользователи.
Эта функция используется для суммирования значений в ячейках.
Эта функция возвращает разные значения в зависимости от того, соблюдается ли условие. Вот видео об использовании функции ЕСЛИ.
Используйте эту функцию, когда нужно взять определенную строку или столбец и найти значение, находящееся в той же позиции во второй строке или столбце.
Эта функция используется для поиска данных в таблице или диапазоне по строкам. Например, можно найти фамилию сотрудника по его номеру или его номер телефона по фамилии (как в телефонной книге). Посмотрите это видео об использовании функции ВПР.
Данная функция применяется для поиска элемента в диапазоне ячеек с последующим выводом относительной позиции этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 7 и 38, то формула =MATCH(7,A1:A3,0) возвращает значение 2, поскольку элемент 7 является вторым в диапазоне.
Эта функция позволяет выбрать одно значение из списка, в котором может быть до 254 значений. Например, если первые семь значений — это дни недели, то функция ВЫБОР возвращает один из дней при использовании числа от 1 до 7 в качестве аргумента "номер_индекса".
Эта функция возвращает порядковый номер определенной даты. Эта функция особенно полезна в ситуациях, когда значения года, месяца и дня возвращаются формулами или ссылками на ячейки. Предположим, у вас есть лист с датами в формате, который Excel не распознает, например ГГГГММДД.
Функция РАЗНДАТ вычисляет количество дней, месяцев или лет между двумя датами.
Эта функция возвращает число дней между двумя датами.
Функции НАЙТИ и НАЙТИБ находят вхождение одной текстовой строки в другую. Они возвращают начальную позицию первой текстовой строки относительно первого знака второй.
Эта функция возвращает значение или ссылку на него из таблицы или диапазона.
Эти функции в Excel 2010 и более поздних версиях были заменены новыми функциями с повышенной точностью и именами, которые лучше отражают их назначение. Их по-прежнему можно использовать для совместимости с более ранними версиями Excel, однако если обратная совместимость не является необходимым условием, рекомендуется перейти на новые разновидности этих функций. Дополнительные сведения о новых функциях см. в статьях Статистические функции (справочник) и Математические и тригонометрические функции (справочник).
Если вы используете Excel 2007, эти функции можно найти в категориях Статистические и Математические на вкладке Формулы.
Возвращает интегральную функцию бета-распределения.
Возвращает обратную интегральную функцию указанного бета-распределения.
Возвращает отдельное значение вероятности биномиального распределения.
Возвращает одностороннюю вероятность распределения хи-квадрат.
Возвращает обратное значение односторонней вероятности распределения хи-квадрат.
Возвращает тест на независимость.
Соединяет несколько текстовых строк в одну строку.
Возвращает доверительный интервал для среднего значения по генеральной совокупности.
Возвращает ковариацию, среднее произведений парных отклонений.
Возвращает наименьшее значение, для которого интегральное биномиальное распределение меньше заданного значения или равно ему.
Возвращает экспоненциальное распределение.
Возвращает F-распределение вероятности.
Возвращает обратное значение для F-распределения вероятности.
Округляет число до ближайшего меньшего по модулю значения.
Вычисляет, или прогнозирует, будущее значение по существующим значениям.
Возвращает результат F-теста.
Возвращает обратное значение интегрального гамма-распределения.
Возвращает гипергеометрическое распределение.
Возвращает обратное значение интегрального логарифмического нормального распределения.
Возвращает интегральное логарифмическое нормальное распределение.
Возвращает значение моды набора данных.
Возвращает отрицательное биномиальное распределение.
Возвращает нормальное интегральное распределение.
Возвращает обратное значение нормального интегрального распределения.
Возвращает стандартное нормальное интегральное распределение.
Возвращает обратное значение стандартного нормального интегрального распределения.
Возвращает k-ю процентиль для значений диапазона.
Возвращает процентную норму значения в наборе данных.
Возвращает распределение Пуассона.
Возвращает квартиль набора данных.
Возвращает ранг числа в списке чисел.
Оценивает стандартное отклонение по выборке.
Вычисляет стандартное отклонение по генеральной совокупности.
Возвращает t-распределение Стьюдента.
Возвращает обратное t-распределение Стьюдента.
Возвращает вероятность, соответствующую проверке по критерию Стьюдента.
Оценивает дисперсию по выборке.
Вычисляет дисперсию по генеральной совокупности.
Возвращает распределение Вейбулла.
Возвращает одностороннее P-значение z-теста.
Возвращает свойство ключевого показателя эффективности (КПЭ) и отображает его имя в ячейке. КПЭ представляет собой количественную величину, такую как ежемесячная валовая прибыль или ежеквартальная текучесть кадров, используемой для контроля эффективности работы организации.
Возвращает элемент или кортеж из куба. Используется для проверки существования элемента или кортежа в кубе.
Возвращает значение свойства элемента из куба. Используется для подтверждения того, что имя элемента внутри куба существует, и для возвращения определенного свойства для этого элемента.
Возвращает n-й, или ранжированный, элемент в множестве. Используется для возвращения одного или нескольких элементов в множестве, например лучшего продавца или 10 лучших студентов.
Определяет вычисленное множество элементов или кортежей путем пересылки установленного выражения в куб на сервере, который формирует множество, а затем возвращает его в Microsoft Office Excel.
= Мир MS Excel/Статьи об Excel
| Функции листа. Функции баз данных [1] |
| Функции листа. Функции даты и времени [5] |
| Функции листа. Функции инженерные [1] |
| Функции листа. Функции информационные [2] |
| Функции листа. Функции логические [10] |
| Функции листа. Функции математические [4] |
| Функции листа. Функции статистические [1] |
| Функции листа. Функции текстовые [1] |
| Функции листа. Функции финансовые [1] |
| Функции листа. Функции ссылок и массивов [2] |
| Функции листа. Функции аналитические [1] |
| Функции листа. Функции определённые пользователем [1] |
| Функция диаграмм [1] |
| Макрофункции [37] |
В традиционных базах данных типа OLTP (Online Transaction Processing — оперативная обработка транзакций), например Access или SQL Server, данные хранятся в нескольких таблицах, связанных между собой; каждая ячейка таблицы содержит один элемент данных. В Excel можно создать подключение к такой базе данных и получать оперативные данные из нее, например, через сводные таблицы. Однако, если в базе данных содержится несколько миллионов (или миллиардов) записей, Excel не в состоянии их обработать. В этом случае используют более эффективные средства обработки информации, например программу Microsoft SQL Server 2000, с помощью которой можно быстро обрабатывать большие объемы данных. Эту же программу можно использовать для создания хранилища данных и кубов OLAP.
Аббревиатура OLAP расшифровывается как Online Analitical Processing — Оперативный анализ данных. Сервер OLAP содержит средства, позволяющие связываться с хранилищем данных. Также сервер OLAP обрабатывает записи из хранилища данных и преобразует таблицы в куб данных. Таким образом, куб данныхпредставляет собой многомерную структуру данных. В отличие от таблиц, используемых в реляционных базах данных, каждая ячейка куба содержит множество элементов. Для работы с кубом данных необходимо подключиться к нему и создать сводную таблицу.
Microsoft Excel предлагает пользователям сотни различных функций и формул для самых разных целей. Независимо от того, нужно ли вам анализировать личные финансы или какой-либо большой набор данных, это функции, которые упрощают работу. Кроме того, это экономит много времени и сил. Однако найти подходящую функцию для вашего набора данных может быть очень непросто.
Итак, если вы изо всех сил пытались найти подходящую функцию Excel для анализа данных, то вы попали в нужное место. Вот список некоторых основных функций Microsoft Excel, которые вы можете использовать для анализа данных и повысить свою продуктивность в процессе.
Примечание: Для непосвященных, функции, упомянутые в этом посте, необходимо добавить в панель формул электронной таблицы Excel, содержащей данные, или в ячейку, в которой вы хотите получить результат.
1. СЦЕПИТЬ
В приведенном выше примере я хотел, чтобы месяц и продажи были в одном столбце. Для этого я использовал формулу = СЦЕПИТЬ (A2, B2) в ячейке C2, чтобы получить в результате 700 долларов США.
Формула: = СЦЕПИТЬ (ячейки, которые вы хотите объединить)
2. LEN
Формула: = LEN (клетка)
3. ВПР
Формула: = ВПР (lookup_value, table_array, col_index_num, [range_lookup])
4. ИНДЕКС / МАТЧ
Подобно функции ВПР, функции ИНДЕКС и ПОИСКПОЗ удобны для поиска определенных данных на основе входного значения. ИНДЕКС и ПОИСКПОЗ, когда используются вместе, могут преодолеть ограничения ВПР, связанные с выдачей неверных результатов (если вы не будете осторожны). Таким образом, когда вы объединяете эти две функции, они могут точно определять ссылку на данные и искать значение в одномерном массиве. Это возвращает координаты данных в виде числа.
Если вы хотите найти приблизительное совпадение, вам придется заменить 0 на 1 или -1. Таким образом, 1 найдет наибольшее значение, меньшее или равное искомому значению, а -1 найдет наименьшее значение, меньшее или равное искомому значению. Обратите внимание: если вы не используете 0, 1 или -1, в формуле будет использоваться 1, by.
Теперь, если вы не хотите жестко указывать название месяца, вы можете заменить его номером ячейки. Таким образом, мы можем заменить «Ян» в формуле, упомянутой выше, на F3 или A2, чтобы получить тот же результат.
Формула: = ИНДЕКС (столбец данных, которые вы хотите вернуть, MATCH (общая точка данных, которую вы пытаетесь сопоставить, столбец другого источника данных, который имеет общую точку данных, 0))
5. MINIFS / MAXIFS
= MINIFS и = MAXIFS очень похожи на функции = MIN и = MAX, за исключением того факта, что они позволяют вам брать минимальный / максимальный набор значений и также сопоставлять их по определенным критериям. По сути, функция ищет минимальные / максимальные значения и сопоставляет их с входными критериями.
Точно так же для получения максимальных баллов я использовал формулу = MAXIFS (C2: C10, B2: B10, «M») и получил результат 100.
6. СРЕДНЕЕ
Функция = СРЗНАЧЕСЛИМН позволяет найти среднее значение для определенного набора данных на основе одного или нескольких критериев. При использовании этой функции следует иметь в виду, что каждый критерий и средний диапазон могут отличаться. Однако в функции = СРЗНАЧЕСЛИ диапазон критериев и диапазон сумм должны иметь один и тот же диапазон размеров. Заметили разницу между этими функциями в единственном и множественном числе? Ну вот тут и нужно быть осторожным.
7. СЧЁТЕСЛИМН
Теперь, если вы хотите подсчитать количество экземпляров, в которых набор данных соответствует определенным критериям, вам необходимо использовать функцию = СЧЁТЕСЛИМН. Эта функция позволяет вам добавлять неограниченное количество критериев в ваш запрос и тем самым делает его самым простым способом найти счетчик на основе входных критериев.
8. СУММПРОИЗВ
Функция = СУММПРОИЗВ помогает умножать диапазоны или массивы вместе, а затем возвращает сумму произведений. Это довольно универсальная функция, которую можно использовать для подсчета и суммирования массивов, таких как СЧЁТЕСЛИМН или СУММЕСЛИМН, но с дополнительной гибкостью. Вы также можете использовать другие функции в SUMPRODUCT, чтобы еще больше расширить его функциональность.
Формула просто удалила лишние пробелы и выдала результат Коврик для мыши с одним пробелом.
Формула: = ОБРЕЗАТЬ (текст)
10. НАЙТИ / ПОИСК
Формула поиска: = НАЙТИ (find_text, внутри_text, [start_num])
Формула поиска: = ПОИСК (find_text, внутри_text, [start_num])
Овладейте анализом данных
Эти важные функции Microsoft Excel определенно помогут вам в анализе данных, но этот список лишь царапает верхушку айсберга. Excel также включает несколько других расширенных функций для достижения конкретных результатов. Если вы хотите узнать больше об этих функциях, сообщите нам об этом в разделе комментариев ниже.
Следующий: Если вы хотите использовать Excel более эффективно, вам следует ознакомиться со следующей статьей, чтобы узнать о некоторых удобных ярлыках для навигации по Excel, которые вы должны знать.
Базовый инструмент для работы с огромным количеством неструктурированных данных, из которых можно быстро сделать выводы и не возиться с фильтрацией и сортировкой вручную. Сводные таблицы можно создать с помощью нескольких действий и быстро настроить в зависимости от того, как именно вы хотите отобразить результаты.
Полезное дополнение. Вы также можете создавать сводные диаграммы на основе сводных таблиц, которые будут автоматически обновляться при их изменении. Это полезно, если вам, например, нужно регулярно создавать отчёты по одним и тем же параметрам.
Как работать
Исходные данные могут быть любыми: данные по продажам, отгрузкам, доставкам и так далее.
- Откройте файл с таблицей, данные которой надо проанализировать.
- Выделите диапазон данных для анализа.
- Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
- Должно появиться диалоговое окно «Создание сводной таблицы».
- Настройте отображение данных, которые есть у вас в таблице.
Перед нами таблица с неструктурированными данными. Мы можем их систематизировать и настроить отображение тех данных, которые есть у нас в таблице. «Сумму заказов» отправляем в «Значения», а «Продавцов», «Дату продажи» — в «Строки». По данным разных продавцов за разные годы тут же посчитались суммы. При необходимости можно развернуть каждый год, квартал или месяц — получим более детальную информацию за конкретный период.
Набор опций будет зависеть от количества столбцов. Например, у нас пять столбцов. Их нужно просто правильно расположить и выбрать, что мы хотим показать. Скажем, сумму.
Можно её детализировать, например, по странам. Переносим «Страны».
Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.
2. 3D-карты
Этот способ визуализации данных с географической привязкой позволяет анализировать данные, находить закономерности, имеющие региональное происхождение.
Полезное дополнение. Координаты нигде прописывать не нужно — достаточно лишь корректно указать географическое название в таблице.
Как работать
- Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
- Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
- Выделите диапазон данных для анализа.
- На вкладке «Вставка» есть кнопка 3D-карта.
Точки на карте — это наши города. Но просто города нам не очень интересны — интересно увидеть информацию, привязанную к этим городам. Например, суммы, которые можно отобразить через высоту столбика. При наведении курсора на столбик показывается сумма.
Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.
3. Лист прогнозов
Зачастую в бизнес-процессах наблюдаются сезонные закономерности, которые необходимо учитывать при планировании. Лист прогноза — наиболее точный инструмент для прогнозирования в Excel, чем все функции, которые были до этого и есть сейчас. Его можно использовать для планирования деятельности коммерческих, финансовых, маркетинговых и других служб.
Полезное дополнение. Для расчёта прогноза потребуются данные за более ранние периоды. Точность прогнозирования зависит от количества данных по периодам — лучше не меньше, чем за год. Вам требуются одинаковые интервалы между точками данных (например, месяц или равное количество дней).
Как работать
- Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
- Выделите два ряда данных.
- На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
- В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
- Выберите дату окончания прогноза.
В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).
Для прогноза на 2014 год вам потребуются два ряда данных: даты и соответствующие им значения показателей. Выделяем оба ряда данных.
На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза — график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия — это и есть прогноз.
4. Быстрый анализ
Эта функциональность, пожалуй, первый шаг к тому, что можно назвать бизнес-анализом. Приятно, что эта функциональность реализована наиболее дружественным по отношению к пользователю способом: желаемый результат достигается буквально в несколько кликов. Ничего не нужно считать, не надо записывать никаких формул. Достаточно выделить нужный диапазон и выбрать, какой результат вы хотите получить.
Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).
Как работать
- Откройте таблицу с данными для анализа.
- Выделите нужный для анализа диапазон.
- При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.
В быстром анализе также есть несколько вариантов форматирования. Посмотреть, какие значения больше, а какие меньше, можно в самих ячейках гистограммы.
Также можно проставить в ячейках разноцветные значки: зелёные — наибольшие значения, красные — наименьшие.
Надеемся, что эти приёмы помогут ускорить работу с анализом данных в Microsoft Excel и быстрее покорить вершины этого сложного, но такого полезного с точки зрения работы с цифрами приложения.
Читайте также: