Анализ данных в MS Excel2010

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

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

Сведения о  сортировке

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

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

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

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

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

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

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

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

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

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

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

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

Чтобы отсортировать числа  по возрастанию, нажмите кнопку  Сортировка от минимального к максимальному.

Чтобы отсортировать числа  по убыванию, нажмите кнопку  Сортировка от максимального к минимальному.

Сортировка значений даты и времени

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

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

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

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

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

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

Сортировка по цвету ячейки, цвету шрифта или  значку

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

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

На вкладке Данные в  группе Сортировка и фильтр нажмите  кнопку Сортировка.

Появится диалоговое окно Сортировка.

В группе Столбец в поле Сортировать по выберите столбец  для сортировки.

В поле Сортировка выберите тип сортировки. Выполните одно из указанных ниже действий.

Для сортировки по цвету  ячейки выберите пункт Цвет ячейки.

Для сортировки по цвету  шрифта выберите пункт Цвет шрифта.

Для сортировки по набору значков  выберите пункт Значок ячейки.

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

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

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

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

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

Убедитесь в том, что в  поле Затем по выбран тот же столбец  и что был сделан тот же выбор  в поле Порядок.

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

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

Сортировка по настраиваемым спискам

Для сортировки в определенном пользователем порядке можно  использовать настраиваемые списки. Например, пусть столбец, по которому нужно выполнить сортировку, содержит значения «Высокий», «Средний» и  «Низкий». Как выполнить сортировку таким образом, чтобы сначала  шли строки со значением «Высокий», затем — строки со значением «Средний», и, наконец, строки со значением «Низкий»? Если сортировка выполняется в алфавитном порядке от А до Я, сначала будут  идти строки со значением «Высокий», потом — со значением «Низкий», и в конце — со значением  «Средний». Если же выполнить сортировку от Я до А, строки будут идти в  порядке «Средний», «Низкий» и «Высокий». Чтобы значение «Средний» было в  середине независимо от порядка сортировки, придется создать настраиваемый  список.

В Excel имеются и встроенные настраиваемые списки: дней недели и месяцев.

При необходимости создайте настраиваемый список.

В диапазоне ячеек введите  значения, по которым необходимо производить  сортировку, от верхней ячейки к  нижней. Например:  A

1 Высокий

2 Средний

3 Низкий

Выделите диапазон ячеек, в которые были введены значения. В описанном выше примере выделите ячейки A1:A3.

Откройте вкладку Файл, нажмите кнопку Параметры и выберите категорию Дополнительно.

В разделе Общие нажмите  Изменить списки.

В диалоговом окне Списки нажмите  кнопку Импорт, а затем дважды нажмите  кнопку ОК.

Максимальная длина настраиваемого списка составляет 255 знаков, и первое значение не может начинаться с цифры.

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

На вкладке Данные в  группе Сортировка и фильтр нажмите  кнопку Сортировка.

Появится диалоговое окно Сортировка.

В группе Столбец в поле Сортировать по или Затем по укажите  столбец для сортировки по настраиваемому списку.

В поле Порядок выберите пункт Настраиваемый список.

Выберите необходимый  список в диалоговом окне Списки. В  предыдущем примере следовало бы выбрать пункт Высокий, Средний, Низкий.

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

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

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

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

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

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

Сортировка от минимального к максимальному и Сортировка от максимального к минимальному, Сортировка по цвету (Пользовательская сортировка) которые рассматриваются  здесь;

Фильтр по цвету;

Снять фильтр с;

Установка фильтрации: Числовые фильтры (если в столбце числа), Текстовые  фильтры (если в столбце текст), Фильтры  по дате (если в столбце даты);

Флажок Выделить все. Если снять этот флажок, то все столбцы  перестанут отображаться;

Флажки с названиями столбцов. Можно снимать флажки с разных столбцов, тогда они перестают  отображаться.

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

Розширений фільтр

Допустим, что нужно выполнить  фильтрацию по условию, но условие является объединением условий фильтрации для  двух и более столбцов. Такое объединение  условий выполняется при помощи логических функций ИЛИ или И. Фильтрацию же по этим условиям можно  осуществить, применив расширенный  фильтр.

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

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

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

На вкладке Данные, в  группе Сортировка и фильтр выбираем кнопку Дополнительно.

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

В поле диапазон условий  указываем адрес диапазона ячеек, в котором записаны условия и  названия столбцов. В нашем примере  это B10:C12.

Если выше был выбран вариант  скопировать результат в другое место то в поле Поместить результат  в диапазон указываем адрес диапазона  ячеек, куда будет размещен полученный результат. В нашем примере указаны  ячейки А27:С27.

Нажимаем кнопку ОК и, если записали все без ошибок и условия  выполнимы, видим результат.

Промежуточные итоги

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

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