Лабораторная работа №7. Обработка и анализ данных в Microsoft Excel 2007

Целью лабораторной работы является изучение и закрепление навыков работы по обработке и анализу данных в Microsoft Excel 2007 [15].

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

Ячейки каждого столбца (т. е. поля) содержат однотипную информацию, имеют одну размерность, поэтому, например, дата и время представлены в одном формате (ДД.ММ.ГГ и ЧЧ.ММ).

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

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

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

ФИО	Телефон	Дата рождения	Кол-во заявок
Иванова А.А.	55-55-56	01.02.1979	10
Белов П.Р.	23-56-89	05.12.1980	20
Смирнов Р.О.	21-58-89	03.06.1985	10
Ермолаева О.Д.	56-48-89	05.04.1980	15
Кривова П.Д.	44-55-77	07.10.1988	5
Дудочкина Л.А.	23-45-87	25.01.1980	8
Ивахина И.В.	26-35-64	26.05.1989	10

Рисунок 2.2.33 Пример списка данных

Сортировка данных

Для осуществления сортировки необходимо сначала выделить диапазон ячеек, содержащих данные для сортировки, либо ячейку столбца, по которому будет произведена сортировка. После выделения можно применить команды (для сортировки по возрастанию значений) и (для сортировки по убыванию значений). Команду настраиваемой сортировки можно вызвать через Главная – Редактирование – Сортировка и фильтр, либо через Данные – Сортировка и фильтр – Сортировка. В появившемся окне Сортировка (рисунок 2.2.34) нужно указать столбец, порядок и особенности сортировки списка данных.

Для добавления еще одного критерия сортировки нужно использовать кнопку Добавить уровень.

Чтобы данные первой строки списка не участвовали в сортировке, нужно поставить флажок «Мои данные содержат заголовки».

Рисунок 2.2.34. Диалоговое окно Сортировка

Чтобы задать сортировку не строк, а столбцов диапазона, нужно, нажав кнопку Параметры, указать «сортировать столбцы диапазона».

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

Замечание. Сортировка может быть произведена по настраиваемым пользовательским спискам, т. е. по определенному пользователем порядку сортировки. Для создания списка нужно внести элементы в ячейки, выделить их и занести в список по команде кнопка Office – Параметры Excel – Основные параметры работы с Excel – Создавать списки для сортировки и заполнения. После того, как список создан, данные могут быть отсортированы по возрастанию/убыванию элементов данного списка. Для этого в окне сортировке в графе Порядок нужно указать, что сортировка будет произведена по настраиваемому списку.

Структурирование данных

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

При ручном структурировании сначала надо определить нужные элементы – выделить диапазоны ячеек, которые должны быть структурированы, затем применить команду Данные – Структура – Группировать – Группировать. После выполнения команды, выделенные строки/столбцы становятся разделом. Нажав на знак +, можно увидеть детали раздела, нажав на знак –, можно скрыть лишние детали.

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

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

Удалить структурирование можно, используя команды Данные – Структура – Разгруппировать – Удаление структуры.

Фильтрация

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

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

Чтобы применить автофильтр, нужно выделить хотя бы одну ячейку списка данных и применить команду Главная – Редактирование – Сортировка и фильтр – Фильтр или Данные – Сортировка и фильтр – Фильтр. После этого в правом углу ячеек заголовков списка появятся стрелки, нажав на которые можно получить доступ к параметрам фильтрации (рисунок 2.2.35).

Рисунок 2.2.35. Выбор условий фильтрации

Можно наложить фильтр по цвету ячеек, по выбору определенных данных, а также задать различные параметры для фильтрации данных разных типов: числовых, текстовых, дат. Также в автофильтре можно задать два условия отбора данных в одном или нескольких столбцах. Для этого нужно активировать команду Настраиваемый фильтр в контекстном меню для конкретного типа фильтра. Удалить автофильтр можно, повторно нажав на копку вызова фильтра.

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

Чтобы отфильтровать список с помощью расширенного фильтра, необходимо произвести некоторые подготовительные действия:

1) проверить, чтобы столбцы списка имели заголовки;

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

Для объединения критериев с помощью условного оператора И следует указать критерии в одной и той же строке, а для объединения критериев с помощью условного оператора ИЛИ следует ввести критерии в разных строках.

Чтобы применить расширенный фильтр, нужно выделить диапазон фильтрации и вызвать команду Данные – Сортировка и фильтр –Дополнительно. В диалоговом окне Расширенный фильтр (рисунок 2.2.36) нужно указать место размещения результатов фильтрации (на этом же месте или скопировать в другое), исходный диапазон фильтруемых данных, критерии отбора (диапазон условий), отображение в результате только уникальных записей и нажать ОК. После применения данной команды на листе в указанном месте будут отображены отфильтрованные данные.

Рисунок 2.2.36. Окно Расширенный фильтр

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

Разделение данных на несколько столбцов

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

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

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

Консолидация данных

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

Чтобы выполнить консолидацию, сначала нужно выделить первую ячейку места, в котором будут расположены консолидированные данные, затем выбрать команду Данные – Работа с данными – Консолидация . В диалоговом окне (рисунок 2.2.37) необходимо выбрать диапазоны ячеек (поля ссылка, список диапазонов) и тип предстоящей консолидации (поле Функция), а также указать использование в качестве имен некоторых значений диапазона данных и установить связь с исходными данными (чтобы при изменении исходных данных производилось обновление консолидированных). После задания параметров итоговые данные будут размещены в указанных ячейках.

Рисунок 2.2.37. Диалоговое окно Консолидация

Создание промежуточных и общих итогов

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

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

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

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

Чтобы добавить итоги для списка данных нужно:

1. Выделить исходный диапазон ячеек.

2. Выбрать команду Данные – Структура – Промежуточные итоги для вызова соответствующего диалогового окна (рисунок 2.2.38).

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

4. В списке Операция выберите функцию, при помощи которой будут подводиться

итоги.

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

6. Нажмите кнопку ОК. Excel вставит в рабочий лист строки с промежуточными итогами. Общий итог по всей таблице появится в ее нижней части.

Рисунок 2.2.38. Диалоговое окно Промежуточные итоги

Размещение строк промежуточных итогов регулируется с помощью флажков в нижней части диалогового окна. По умолчанию старые промежуточные итоги должны заменяться новыми (флажок Заменить текущие итоги), также можно разделить группы итогов на страницы (флажок Конец страницы между группами) и добавить общие итоги под таблицей данных (флажок Итоги под данными).

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

Использование сводных таблиц для анализа данных

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

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

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

Использование макросов

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

Чтобы получить доступ к командам записи макросов, нужно отобразить вкладку Разработчик на ленте главного меню. Для этого нужно выбрать кнопку Office – Параметры Excel – Показывать вкладку Разработчик на ленте. На панели Код (рисунок 2.2.39) доступны основные инструменты для работы с макросами.

Рисунок 2.2.39. Панель Код

Для записи макроса на Visual Basic используйте кнопку запуска редактора – Visual Basic. Для просмотра списка имеющихся и запуска нужного макроса используйте кнопку Макросы. Кнопка Запись макроса позволяет записать последовательность действий в макрос. Кнопка Относительные ссылки включает при записи макроса режим относительных ссылок. Кнопка Безопасность макросов позволяет устанавливать различные уровни безопасности для запуска макросов из разных источников.

2.3. Microsoft Office Outlook

Microsoft Office Outlook - персональный коммуникатор. В состав Outlook входят: календарь, планировщик задач, записки, менеджер электронной почты, адресная книга. Поддерживается совместная сетевая работа. Доступен под Windows и под Apple Mac OS X [13].

2.4. Microsoft Office PowerPoint

Microsoft PowerPoint (полное название — Microsoft Office PowerPoint) — это программа для создания и проведения презентаций, являющаяся частью Microsoft Office и доступная в редакциях для операционных систем Microsoft Windows и Mac OS. PowerPoint является частью Microsoft Office. Это позволило PowerPoint стать наиболее распространенной во всем мире программой для создания презентаций. Однако, поскольку PowerPoint имеет возможность подключения элементов других приложений через OLE, некоторые презентации становятся сильно привязанными к платформе Windows, что делает невозможным открытие данных файлов, например, в версии для Mac OS. Это привело к переходу на открытые стандарты, такие как PDF и OASIS OpenDocument [16].

При первом запуске PowerPoint 2007 (2010) становятся заметными изменения в интерфейсе. Вместо команд PowerPoint появилась новая структура кнопок и вкладок. В такой структуре будет удобнее искать и использовать необходимые функции и создавать эффектные презентации.

Самые заметные изменения в PowerPoint 2007 представлены в верхней части окна приложения. Теперь там вместо меню и панелей инструментов через весь экран проходит

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

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

1. Создание презентации на основе полностью или частично готовых презентаций, которые содержатся в библиотеках (on-line). Ранее данный подход назывался «Создание презентаций из мастеров автосодержания».

2. Создание презентации на основе других готовых презентаций (т.е. творческая переработка чужого передового опыта);

3. Создание презентации на основе готовых шаблонов слайдов;

4. Основной способ –это создание презентации на основе пустых макетов слайдов – Новая презентация;

⇐ Предыдущая15161718192021222324Следующая ⇒

Поделиться:

Воспользуйтесь поиском по сайту: