Анализ данных

Сортировка данных в диапазоне или таблице

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

Сортировать данные можно по тексту (от А к Я или от Я к А), числам (от наименьших к наибольшим или от наибольших к наименьшим), а также датам и времени (от старых к новым или от новых к старым) в одном или нескольких столбцах. Можно также выполнять сортировку по настраиваемым спискам, которые создаете вы сами (например, списку, состоящему из элементов “Большой”, “Средний” и “Маленький”), или по формату, включая цвет ячеек и цвет шрифта, а также по значкам. Для поиска наибольших и наименьших значений в диапазоне ячеек или таблице, например 10 самых высоких оценок или 5 самых низких сумм продаж, можно использовать функцию “Автофильтр” или условное форматирование.

Сортировка текстовых значений

1. Выделите ячейку в столбце, который требуется отсортировать.

2. На вкладке Главная в группе Сортировка и фильтр выполните одно из указанных ниже действий.

   • Чтобы быстро отсортировать значения по возрастанию, нажмите кнопку    (Сортировка от А до Я).

   • Чтобы быстро отсортировать значения по убыванию, нажмите кнопку    (Сортировка от Я до А).

Сортировка чисел

1. Выделите ячейку в столбце, который требуется отсортировать.

2. На вкладке Главная в группе Сортировка и фильтр выполните одно из указанных ниже действий.

   • Для сортировки чисел по возрастанию нажмите кнопку    (Сортировка от минимального к максимальному).

   • Для сортировки чисел по убыванию нажмите кнопку    (Сортировка от максимального к минимальному).

Сортировка дат

1. Выделите ячейку в столбце, который требуется отсортировать.

2. На вкладке Главная в группе Сортировка и фильтр выполните одно из указанных ниже действий.

   • Чтобы отсортировать значения по возрастанию, щелкните    (Сортировка от старых к новым).

   • Чтобы отсортировать значения от поздних дат или значений времени к ранним, нажмите кнопку    (Сортировка от новых к старым).

Сортировка по нескольким столбцам или строкам

Возможно, вы захотите выполнить сортировку по двум или нескольким столбцам или строкам, чтобы сгруппировать данные с одинаковыми значениями в одном столбце или строке, а затем отсортировать эти группы с одинаковыми значениями по другому столбцу или строке. Например, если есть столбцы “Отдел” и “Сотрудник”, можно сначала выполнить сортировку по столбцу “Отдел” (для группировки всех сотрудников по отделам), а затем — по имени (для расположения имен сотрудников каждого отдела в алфавитном порядке). Можно одновременно выполнять сортировку по 64 столбцам.

Фильтрация данных

Фильтрация диапазона данных

1. Выберите любую ячейку в диапазоне данных.

2. Выберите Данные> Фильтр.

     

3. Выберите стрелку в столбце   .

4. Выберите пункт Текстовые фильтры или Числовые фильтры, а затем — сравнение, например пункт Между.

     

5. Введите условия фильтрации и нажмите кнопку ОК.

     

Фильтрация данных в таблице

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

2. Выберите стрелку в    столбца, который вы хотите отфильтровать.

3. Снимите флажок (Выделить все) и установите флажки для тех элементов, которые вы хотите отобразить.

     

4. Нажмите кнопку ОК.

Стрелка заглавного    изменится на   фильтра. Щелкните этот значок, чтобы изменить или очистить фильтр.

Использование расширенных условий фильтрации

Если для фильтрации данных требуются сложные условия (например, Тип = “Фрукты” ИЛИ Продавец = “Егоров”), можно использовать диалоговое окно Расширенный фильтр.

Чтобы открыть диалоговое окно Расширенный фильтр, щелкните Данные > Дополнительно.

Расширенный фильтр Обзор расширенных условий фильтра	Пример
Несколько условий, один столбец, любое из условий истинно	Продавец = “Егоров” ИЛИ Продавец = “Грачев”
Несколько условий, несколько столбцов, все условия истинны	Тип = “Фрукты” И Продажи > 1000
Несколько условий, несколько столбцов, любое из условий истинно	Тип = “Фрукты” ИЛИ Продавец = “Грачев”
Несколько наборов условий, один столбец во всех наборах	(Продажи > 6000 И Продажи < 6500) ИЛИ (Продажи < 500)
Несколько наборов условий, несколько столбцов в каждом наборе	(Продавец = “Егоров” И Продажи >3000) ИЛИ  | (Продавец = “Грачев” И Продажи > 1500)
Условия с подстановочными знаками	Продавец = имя со второй буквой “г”

Обзор расширенных условий фильтра

Действие команды Дополнительно отличается от действия команды Фильтр в некоторых важных аспектах.

• Она отображает диалоговое окно Расширенный фильтр, а не меню “Автофильтр”.

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

СВОДНЫЕ ТАБЛИЦЫ

Сводные таблицы необходимы для суммирования, анализа и представления данных, находящихся в «больших» исходных таблицах, в различных разрезах.

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

Рассмотрим процесс создания несложных сводных таблиц.

Сводные таблицы (Вставка | Таблицы |Сводная таблица) могут пригодиться, если одновременно выполняются следующие условия:

• имеется исходная таблица с множеством строк (записей). Речь идет о нескольких десятках и сотнях строк;

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

• этот анализ затруднительно провести на основе исходной таблицы с использованием других средств: фильтра (CTRL + SHIFT + L), формул, Расширенного фильтра;

• исходная таблица удовлетворяет определенным требованиям (см. ниже).

Подготовка исходной таблицы

Начнем с требований к исходной таблице.

• каждый столбец должен иметь заголовок;

• в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата; столбец «Поставщик» — названия компаний только в текстовом формате);

• в таблице должны отсутствовать незаполненные строки и столбцы;

• в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы.

В общем случае поля в таблицах могут содержать один из двух типов информации.

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

Категория. В полях этого типа содержатся данные описательного характера.

Если исходные данные представлены на рабочем листе, выберите любую ячейку из диапазона ячеек с данными, а затем на ленте выберите команду Вставка | Таблицы | Рекомендуемые сводные таблицы.

Если ни одна из рекомендованных сводных таблиц вам не подходит, остается два варианта.

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

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

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

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

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

Далее необходимо наполнить макет сводной таблицы содержимым. Это можно сделать несколькими способами.

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

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

3. Щелкните правой кнопкой мыши на имени поля на панели Поля сводной таблицы и выберите в открывшемся контекстном меню область его местоположения (например, Добавить в названия строк).

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

Группа – набор элементов, которые обрабатываются как один элемент. Элементы можно объединить в группу вручную или автоматически (например, объединить даты в группу конкретного месяца).

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

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

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

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

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

Обновление сводной таблицы – Вычисление сводной таблицы, после того как были внесены изменения в исходные данные.

Общие итоги – Строка или столбец, в котором показаны общие итоги для всех ячеек строки или столбца сводной таблицы. Всегда можно либо задать способ подведения общих итогов (для строк, столбцов или для тех и других), либо вообще не подводить никаких итогов.

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

Элемент – Элемент поля, назначенного определенной области. Элементы выполняют роль заголовков строк и/или столбцов сводной таблицы.

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

В этом диалоговом окне есть две вкладки: Операция и Дополнительные вычисления.

Вкладка Операция используется для выбора вычисляющей функции. Можно выбрать функции: Сумма, Количество, Среднее Максимум, Минимум, Произведение, Количество чисел, Стандартное отклонение, Несмещенное отклонение, Дисперсия, Несмещенная дисперсия.

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

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

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

Excel предлагает два способа группирования элементов полей сводной таблицы.

1. Вручную. После создания сводной таблицы выделите элементы, которые предполагается объединить в одну группу, и выберите команду Работа со сводными таблицами | Анализ | Группа | Группировка по выделенному. Можно также щелкнуть правой кнопкой мыши на выделенных элементах и выбрать в контекстном меню команду Группировать.

2. Автоматически. Если элементами поля являются числовые значения или значения дат или времени, то сгруппировать их можно автоматически с помощью диалогового окна Группирование. Выделите любой элемент поля, которое помещено в область Столбцы или Строки, и выберите команду Работа со сводными таблицами | Анализ | Группа | Группировка по полю. Можно также щелкнуть правой кнопкой мыши на любом элементе поля и выбрать в контекстном меню команду Группировать. В обоих случаях Excel отобразит диалоговое окно Группирование.