Центр Компьютерного Обучения " Специалист ", 2012 Microsoft Excel 2010. Расширенные возможности решение
 Скачать 4.3 Mb.
|
|
Данные [Data], в группе Работа с данными [Data Tools], выбрать Консолидация [Consolidate]. 3. В поле Функция [Function]выбрать функцию, которая будет применена к объединяемым данным. 4. Поставить курсор в поле Ссылка [Reference] и выделить первый диапазон консолидации вместе с заголовками, нажать Добавить [Add], и т.д. повторить для всех исходных диапазонов. Чаще всего исходные диапазоны выделяются вместе с заголовками, особенно если порядок расположения данных может отличаться. В случае, если в источнике присутствуют объединенные ячейки в строке заголовка, то следует выделять без заголовков (заголовки потом копируются дополнительно). 5. В группе Использовать в качестве имен [Use labels in]поставить флажки подписи верхней строки [Top row] (если исходные диапазоны были выделены с заголовками) и значения левого столбца [Left column]. 6. OK. Если необходимо консолидировать таблицы, сохранив связь с исходными данными, то выбрать флажок Создавать связи с исходными данными [Create links to source data]. При изменении данных в исходных таблицах, консолидированная таблица будет также изменяться. Связь работает, если консолидированная таблица находится на отдельном листе от исходных данных. Если исходные данные находятся в других файлах, то предварительно требуется открыть все эти файлы, а потом выполнять команды консолидации. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 54 ПРАКТИКУМ: Открыть файл 02_6 Консолидация. На листе Задание, Итоги, Отчет выполнить задания. Сохранить изменения в файле и закрыть его. САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листах 12-1 и 11-2. Импорт данных Существует множество видов внешних данных: бухгалтерские программы, базы данных, которые можно импортировать в Excel для последующего анализа. Возможность импорта данных избавляет от необходимости вводить их в Excel вручную. Импорт возможен из текстовых файлов, баз данных Microsoft Access, Web-страниц. Импорт с Web-страниц 1. Выделить пустую ячейку листа. 2. На вкладке Данные [Data], в группе Получение внешних данных [Get External Data], выбрать Из Интернета [From Web]. 3. В окне Создание веб–запроса [New Web Query] в строке Адрес [Address] ввести адрес веб- страницы (например, www.cbr.ru), нажать Пуск [Go] или клавишу Enter Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 55 4. Щелкнуть по значку–индикатору таблицы, которую нужно выбрать (значок меняет вид на ), а затем нажать кнопку Импорт [Import]. 5. В окне Импорт данных [Import Data] выбрать место расположения импортируемых данных, нажать OK. ПРАКТИКУМ: Открыть файл 02_7 Импорт данных. На листе ИмпортКурсы загрузить с начала листа данные о курсах валют из интернет-ресурса www.cbr.ru Импорт из текстового файла Некоторые внешние данные могут содержаться в текстовых файлах — это широко распространенный формат файлов, доступный для чтения в Excel. Например, предположим, что вам требуются данные, хранящиеся в одной из таблиц базы данных вашего коллеги, который, не хочет предоставлять вам доступ ко всей базе данных. Достаточно преобразовать нужные данные в текстовый файл, и их можно будет легко импортировать в Excel. 1. Выделить ячейку листа. 2. На вкладке Данные [Data], в группе Получение внешних данных [Get External Data], выбрать Из текста [From Text]. 3. В окне Импорт текстового файла [Import Text File], открыть текстовый файл с источником данных. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 56 4. В диалоговом окне Мастер текстов (импорт) – шаг 1 из 3 [Text Import Wizard – Step 1 of 3] выполнить ряд настроек Способ организации данных в файле: с разделителями [Delimited] - содержимое одного столбца от другого отделено знаком (пробел, табуляция, точка с запятой, запятая и др.). фиксированной ширины [Fixed width]: выбираем, если в тексте каждый столбец состоит из одинакового количества символов. Начать импорт со строки [Start import at row] – обозначить номер строки, с которой начинать импорт в поле. Формат файла [File Original] – выбрать формат файла (кодировка). и нажать Далее [Next]. 5. На следующем шаге окна Мастер текстов (импорт) – шаг 2 из 3 [Text Import Wizard – Step 2 of 3] в зависимости от формата исходных данных, выбранных на шаге 1 работы мастера, сделать следующие настройки: С разделителями – надо выбрать символ-разделитель из предлагаемых или, если нужного нет, то вписать его в поле другой [other]. Фиксированной ширины – щелкать левой кнопкой мыши в области просмотра в месте разделения столбцов. Для удаления разделяющей линии, щелкнуть дважды левой кнопкой мыши по линии. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 57 Нажать Далее [Next]. 6. На следующем шаге окна Мастер текстов (импорт) – шаг 3 из 3 [Text Import Wizard – Step 3 of 3] настроить формат данных для каждого столбца. Для этого выделить столбец в Образце разбора данных [Data preview] и выбрать Формат данных столбца [Column data format]: общий [General] – автоопределение типа данных. текстовый [Text] – значения будет текстовыми данными. дата [Date] – выбрать для дат нужный вариант: ДМГ, ГМД МДГ и т.д., если порядок расположения составляющих даты отличается от используемого. Подробнее [Advanced] – установить Разделитель целой и дробной части [Decimal Separator], а так же Разделитель разрядов [Thousands separator], если они отличаются от ваших региональных стандартов. После завершения импорта, разделители чисел и форматы дат будут заменены на разделители и форматы, используемые в текущих региональных настройках . Нажать Готово [Finish]. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 58 7. В окне Импорт данных [Import Data] выбрать место расположения импортируемых данных, нажать OK. ПРАКТИКУМ: В открытом файле 02_7 Импорт данных, на лист ИмпортСотрудники импортировать данные из текстового файла Сотрудники компании.txt. Расположить указанные данные, начиная с ячейки A1. Импорт из баз данных MS Access 1. Выделить ячейку листа. 2. На вкладке Данные [Data], в группе Получение внешних данных [Get External Data], выбрать Из Access [From Access]. 3. В окне Выбор источника данных [Select Date Source] открыть файл базы данных, из которого импортируются данные. 4. В окне Выделить таблицу [Select table] выбрать таблицу и нажать ОК. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 59 5. Выбрать способ представления данных в книге и место расположения импортируемых данных в окне Импорт данных [Import Data], OK. Импортируются все поля и записи из указанной таблицы файла Access. ПРАКТИКУМ: В открытом файле 02_7 Импорт данных, на лист ИмпортТовары импортировать данные из таблицы Товары файла базы данных Microsoft Access Товары и поставщики.mdb. Расположить результат с начала листа. Сохранить сделанные изменения в файле. Работа с импортируемыми данными 1. На вкладке Данные [Data], в группе Подключения [Connections], выбрать Подключения [Connections]. 2. Выделить имя источника данных в окне Подключения к книге [Workbook connections]. 3. Выбрать команду: Удалить [Remove]– удаление связи с источником. Обновить [Refresh]– обновление импортируемых данных. Свойства [Properties]– свойства подключения. Можно задать Обновление экрана [Refresh control]: Фоновое обновление [], Обновлять каждые (указать мин) [], Обновлять при открытие файла [Refresh data when opening the file]. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 60 Для настройки Свойств внешних данных [External Data Range Properties] 1. Выделить ячейку с импортируемыми данными. 2. На вкладке Данные [Data], в группе Подключения [Connections], выбрать Свойства [Properties]. 3. Выбрать свойства в группе Формат и разметка данных [Data formatting and layout] и действие Если количество строк в диапазоне изменится [If the number of rows in the data range changes upon refresh], ОК. ПРАКТИКУМ: В открытом файле 02_7 Импорт данных выполнить ряд настроек для работы с внешними данными: Удалить связь с источником «Сотрудники компании». Настроить обновление с интернет-ресурса при открытии файла. Сохранить изменения в файле и закрыть его. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 61 Модуль 3. А НАЛИЗ ДАННЫХ С ПОМОЩЬЮ С ВОДНЫХ ТАБЛИЦ Наиболее удобным встроенным инструментом для всестороннего и эффективного анализа больших табличных данных является сводная таблица. Отчет сводной таблицы позволяет обобщать, анализировать, изучать и представлять данные из больших табличных данных. Отчет сводной таблицы может оказаться особенно полезным в ситуации, когда необходимо просуммировать большой набор чисел, а объединенные данные и промежуточные итоги позволяют взглянуть на данные под разными углами и сравнить однотипные показатели из разных групп данных. Создание сводных таблиц При создании отчета сводной таблицы каждый из столбцов исходных данных становится полем, которое можно использовать в отчете. Следует убедиться, что в первой строке списка, используемого в качестве источника данных, для каждого из столбцов указано имя. 1. Выделить любую ячейку таблицу. 2. На вкладке Вставка[Insert], в группе Таблицы [Table], выбрать Сводная таблица [PivotTable]. 3. В диалоговом окне Создание сводной таблицы [Create PivotTable] проверить правильность выделения диапазона данных или выделить новый источник данных в поле Таблица или диапазон [Table/Range], определить место размещения сводной таблицы: на новый лист [New Worksheet] или На существующий лист [Existing Worksheet], нажать OK. Диапазон исходных данных, на основе которого строится отчет сводной таблицы, должен быть выделен с заголовками столбцов и не должен включать итоговые данные. 4. Сформировать отчет сводной таблицы. На листе появилась графическая область с указанием имени сводной таблицы - Сводная таблица 1 [PivotTable 1]. По умолчанию имени сводной таблицы присваивается нумерация. Если выделена произвольная ячейка на листе, то в графической области появляется надпись Чтобы начать работу с отчетом сводной таблицы, щелкните в этой области [Click in this area to work with the PivotTable report], при Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 62 активной ячейке в области отчета надпись заменяется на Чтобы построить отчет, выберите поля из списка полей сводной таблицы [To build a report, choose fields the PivotTable Field List]. Каждая область макета, куда помещаются поля исходной таблицы, имеет свое назначение, определяющее внешний вид сводной таблицы и ее функции. Макет отчета состоит из 4 частей: Фильтр отчета [Report Filter] – фильтр отчета сводной таблицы. Если установлен фильтр, то построение и расчет данных сводной таблицы ведется для заданного значения. Названия строк [Row Labels] – формируют заголовки строк сводной таблицы, если размещено несколько полей, то они размещаются в макете сверху вниз, обеспечивая группирование данных сводной таблицы по иерархии полей (для каждого элемента внешнего поля, элементы внутреннего поля повторяются). Названия столбцов [Column Labels] – формируют заголовки столбцов сводной таблицы, если таких полей несколько, то они в макете размещаются слева направо, обеспечивая группирование данных сводной таблицы по иерархии полей. Σ Значения [ΣValues] – обязательная область макета для размещения полей, по которым подводятся итоги, согласно выбранной функции. Размещаемые здесь поля могут быть произвольных типов. Если в расчетной области расположено несколько полей, то в области макета Названия столбцов [Column Labels] автоматически появляется поле Σ Значения [ΣValues], которое можно при необходимости переместить в область Названия строк [Row Labels]. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 63 Чтобы поместить поле в область макета по умолчанию, установите флажок напротив имени данного поля в разделе полей. Чтобы поместить поле в определенную область раздела макета, можно щелкнуть правой кнопкой мыши по имени соответствующего поля в разделе полей и выбрать команду: Добавить в фильтр отчета [Add to Report Filter], Добавить в названия строк [Add to Row Labels], Добавить в названия столбцов [Add to Column Labels], Добавить в значения [Add to Values] или перетащить поле в нужную область макета. Преобразование сводных таблиц В любой момент макет сводной таблицы можно изменить. Для этого необходимо наличие области Список полей сводной таблицы [PivotTable Field List]. Если при активной ячейке отчета сводной таблицы, список полей сводной таблицы не отображается, то его необходимо вернуть – щелкнуть правой кнопкой мыши по ячейке сводной таблицы и выбрать Показать список полей [Show Field List] или на вкладке Параметры [Options], в группе Показать [Show], выбрать Список полей [Field List]. Добавить поле в нужный раздел макета – перетащить поле из списка полей в нужную область раздела макета сводной таблицы. Переместить поле из одного раздела макета – перетащить поле в нужную область раздела макета. Удалить поле из отчета - убрать флажок в списке полей, щелкнуть по полю в разделе макета и выбрать Удалить поле [Delete Field] или перетащить поле из раздела макета в список полей. Фильтрация данных. Срезы Улучшенная функция фильтрации позволяет анализировать большие объемы данных. Помимо фильтров, которые находят в фильтре отчета, а так же в названиях строк и столбцов, в Excel 2010 для фильтрации данных в сводных таблицах можно также использовать срезы. Срезы – это графическое представление интерактивных фильтров отчета для сводной таблицы или диаграммы. Срезы не только позволяют быстро выполнять фильтрацию, но и указывают ее текущее состояние, что облегчает понимание содержимого отфильтрованного отчета сводной таблицы, т.е. срезы ускоряют и упрощают фильтрацию данных. Для работы со срезами нужно; 1. На вкладке Параметры [Options], в группе Сортировка и фильтр [Sort & Filter], выбрать Вставить срез [Insert Slicer]. 2. Выделить нужные поля, нажать ОК. Срез выглядит как отдельный графический объект, расположенный над листом, поэтому его легко перемещать по листу. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 64 В срезе можно выбирать как один, так и несколько элементов (при использовании клавиш Ctrl и Shift ), а в сводной таблице будут отображаться только отфильтрованные данные по отобранным элементам. Для анализа данных удобно, что срез разными цветами отображает не только выделенные, но еще и пустые элементы, для которых нет ни одного значения в исходной таблице. При использовании срезов по нескольким полям, можно быстро и наглядно отобразить взаимосвязи между элементами данных. Срезы можно форматировать. Для этого необходимо выделить срез, и на вкладке Конструктор [Design] выбрать Стили срезов [Slicer Styles]. Для удаления среза – щелкнуть по нему правой кнопкой мыши и выбрать Удалить «имя поля» [Delete «имя поля»]. Настройка полей сводной таблицы Данные полей, размещенных в области Σ Значения [ΣValues] подвергаются действию функций: по умолчанию, числовые данные суммируются, а для текстовых и дат происходит расчет их количества. Для использования других функций надо настроить параметры поля. Существует несколько способов изменения параметров поля. 1-й способ: щелкнуть правой кнопкой мыши по нужному полю и выбрать Параметры полей значений [Value Field Settings]. На вкладке Операция[Summarize by]: Выбрать в поле Операция [Summarize value field by] нужную функцию для обработки данных. |