Окно программы ms excel 2 Основные понятия ms excel. 2 Адреса ячеек 3 Типы данных в ячейках электронной таблицы 5

Вид материала

Анализ

Содержание Автоматическое вычисление общих и промежуточных итогов Сумма; в списке Добавить итоги по Консолидация данных Консолидация данных по расположению. Консолидация данных по категориям. Список диапазонов Создавать связи с исходными данными Список диапазонов. Анализ и обобщение данных с помощью сводных таблиц Сводные таблицы. Мастера сводных таблиц. Столбец помещают поля, которые должны быть представлены в сводной таблице. В области Страница, Строка Формат) и выбрать функцию для выполнения дополнительных вычислений (с помощью кнопки Дополнительно>>

Подобный материал:

• Программа дисциплины Анализ данных средствами ms excel для направления 080102. 65 Мировая, 121.98kb.
• Урок информатики. 11 класс. Тема: Электронные таблицы, 31.72kb.
• Электронные таблицы в Microsoft Excel, 156.17kb.
• Microsoft Office Excel. Интерфейс программы. Заполнение и редактирование ячейки. Форматирование, 1488.42kb.
• Запуск программы ms excel ms excel- стандартное приложение Windows. Ссылка на ms excel, 117.16kb.
• Применение Microsoft Excel для обработки табличных данных. Выполнение расчетов в таблицах, 14.68kb.
• Как создать тесты в Microsoft Excel Открыть программу Microsoft Excel, 26.73kb.
• Практикум по Excel Занятие 1 Решение задач прикладной информатики в менеджменте. Практическое, 136.8kb.
• Microsoft Office Excel 2007, 13.85kb.
• Программа повышения квалификации «Использование Microsoft Excel для автоматизации бухгалтерского, 14.19kb.

Автоматическое вычисление общих и промежуточных итогов

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

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

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

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

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

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

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

При подведении промежуточных итогов автоматически могут быть вычислены:

Сумма Количество чисел

Количество значений Смещенное отклонение

Среднее Несмещенное отклонение

Максимум Смещенная дисперсия

Минимум Несмещенная дисперсия

Произведение

Для автоматического подведения итогов следует выполнить следующую последовательность действий:

1. отсортировать список по столбцу, для которого необходимо вычислить промежуточные итоги;
   
2. выделить какую-либо ячейку таблицы или требуемый диапазон;
   
3. ввести команду меню ДАННЫЕИтоги;
   
4. в диалоговом окне Промежуточные итоги из списка При каждом изменении в: выбрать столбец, содержащий группы, по которым необходимо подвести итоги. Это должен быть тот столбец, по которому проводилась сортировка списка;
   
5. из списка Операция выбрать функцию, необходимую для подведения итогов, например Сумма;
   
6. в списке Добавить итоги по: выбрать столбцы, содержащие значения, по которым необходимо подвести итоги.
   

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

Команда ДАННЫЕИтоги для одной и той же таблицы может выполняться многократно. При этом ранее созданные итоги могут как заменяться новыми, так и оставаться неизменными, если в диалоговом окне Промежуточные итоги снят переключатель Заменить текущие итоги.

Команда ДАННЫЕИтоги используется для обобщения данных, которые находятся на одном рабочем листе, и в том случае, если эти данные расположены в смежных ячейках. Итоги выводятся на том же рабочем листе в структурированной таблице с исходными данными.

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

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

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

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

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

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

Если консолидированная информация выводится на том же рабочем листе, что и исходные данные, то для итоговой таблицы с консолидированными данными не создается структуры, поэтому для вычисления итоговых данных на одном и том же рабочем листе лучше использовать команду ДАННЫЕИтоги.

В Excel имеется несколько способов консолидации данных:

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

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

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

Для выполнения указанного способа консолидации вводят команду ДАННЫЕКонсолидация, а затем в диалоговом окне Консолидация выбирают функцию и указывают области консолидируемых данных.

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

Для выполнения консолидации данных по категориям следует:

1. Активизировать лист, на котором будут создаваться итоговые данные, и установить курсор в верхнюю левую ячейку области расположения консолидируемых данных.
   
2. Ввести команду меню ДАННЫЕКонсолидация.
   
3. В диалоговом окне Консолидация выбрать из раскрывающегося списка функцию, которую следует использовать для обработки данных, например Сумма.
   
4. Щелкнуть мышью в поле Ссылка, указать первый диапазон ячеек, данные из которых должны быть консолидированы.
   

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

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

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

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

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

5. Далее для включения выбранного диапазона в поле Список диапазонов следует щелкнуть по кнопке Добавить. Кнопка Добавить используется в тех случаях, когда в консолидации участвует несколько областей-источников данных.
   
6. Далее следует повторить п. 4 и 5 для всех консолидируемых исходных областей - ввести адреса всех диапазонов, подлежащих консолидации.
   

Для поиска данных, расположенных в других рабочих книгах (файлах), следует использовать кнопку Обзор.

Если область назначения и исходные данные находятся в разных книгах, но в одной и той же папке, следует указать имя книги, имя листа, а затем - имя или ссылку на диапазон, например: [Итоги.xls]Юпитер!D4:G22.

Если исходные области и область назначения находятся в разных книгах и в разных папках диска, нужно использовать полный путь к файлу книги, имя книги, имя листа, а затем - имя или ссылку на диапазон, например: [H:\Works\Итоги.хls]Сатурн!D4:G13.

7. После ввода адресов всех исходных областей следует установить нужные переключатели:

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

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

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

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

Если исходные данные расположены в других рабочих книгах, для обновления данных нужно использовать команду ПРАВКАСвязи.

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

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

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

Для выполнения консолидации данных с использованием других функций, например для вычисления средних значений в тех же исходных диапазонах, для которых ранее использовалась функция Сумма, следует установить курсор в новом месте (для вставки новой области консолидированных данных), ввести команду ДАННЫЕКонсолидация и в диалоговом окне Консолидация выбрать нужную функцию. В этом случае нет необходимости снова вводить адреса исходных диапазонов.

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

Консолидации данных по категориям и модификации консолидированных данных посвящено задание 6 практической работы 3.

Анализ и обобщение данных с помощью сводных таблиц

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

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

Кроме того, сводные таблицы можно создавать:

• на основе таблиц, полученных в результате консолидации данных;
  
• на основе других сводных таблиц;
  
• на основе внешних источников данных.
  

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

Создание и модификация сводных таблиц выполняются с помощью Мастера сводных таблиц, окно которого появляется на экране после ввода команды меню ДАННЫЕСводная таблица

или щелчка по кнопке Мастер сводных таблиц на панели инструментов Сводные таблицы.

Мастер сводных таблиц имеет четыре окна (4 шага).

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

Структура сводной таблицы, ее вид (макет) формируется в третьем окне Мастера сводных таблиц.

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

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

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

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

В области Страница, Строка и Столбец каждое поле может помещаться только один раз.

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

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

• Сумма (по умолчанию);
  
• Количество значений;
  
• Среднее;
  
• Максимум и др.
  

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

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

выбрать нужную функцию.

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

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

Сводные таблицы Excel являются гибким инструментом для обобщения, анализа и наглядного представления данных. Их можно модифицировать, добавлять новые поля, удалять существующие поля, изменять местонахождение полей, например превращать строки в столбцы и наоборот. Поэтому английское название сводных таблиц "pilot table" иногда переводят как "таблица-флюгер".

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

Сводные таблицы служат только для отображения информации, поэтому ручная правка данных в них невозможна. Для изменения структуры сводной таблицы нужно установить курсор в области сводной таблицы, снова ввести команду меню ДАННЫЕСводная таблица или команду контекстного меню Мастер и выполнить необходимые преобразования в окне Мастер сводных таблиц, шаг 3.

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

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

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

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

Для удаления всей сводной таблицы нужно:

• установить курсор в любую ячейку сводной таблицы;
  
• открыть на панели инструментов Сводные таблицы список Сводная таблица;
  
• выбрать команду ВыделитьТаблица целиком при нажатой кнопке Разрешить выделение;
  
• ввести команду меню ПРАВКАОчиститьВсе.
  

При удалении сводной таблицы исходные данные остаются без изменений.

На основе сводных таблиц можно строить диаграммы, наглядно отображающие представленные в них итоговые данные.