Оффисные приложения Ms Excel 2010

Организация электронного документооборота

Уровень 2 содержит сумму продаж по каждому месяцу в каждом регионе.

Уровень 3 содержит строки с подробными данными (отображаются только строки данных с 11 по 13).

Для отображения или скрытия данных в многоуровневой структуре щелкайте символы структурыи.

Создание многоуровневой структуры из строк

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

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

3.Для сортировки столбца, содержащего данные, по которым требуется выполнить группировку, выделите этот столбец и на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка от А до Я или Сортировка от Я до А.

4.Вставьте собственные итоговые строки с формулами непосредственно под или над каждой группой строк с подробными данными.

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

Структура.

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

7.Структурируйте данные.

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

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

1.Структурируйте внешнюю группу. Выделите все подчиненные итоговые строки и соответствующие им строки с подробными данными.

2.На вкладке Данные в группе Структура щелкните Группировать.

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

4.На вкладке Данные в группе Структура щелкните Группировать.

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

6.Для разгруппировки строк выделите эти строки, а затем на вкладке Данные в группе Структура выберите команду Разгруппировать. Выделенные элементы многоуровневой структуры также можно разгруппировать без удаления всей

61

Организация электронного документооборота

структуры. Удерживая клавишу SHIFT, щелкните соответствующую группе кнопку или , а затем на вкладке Данные в группе Структура выберите команду

Разгруппировать.

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

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

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

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

Отображение или скрытие подробных данных для группы

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

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

Разворачивание или сворачивание структуры до определенного уровня

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

Отображение или скрытие всех структурированных данных

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

Для скрытия всех подробных данных нажмите кнопку .

Создание итогового отчета с диаграммой

Создание итогового отчета.

1.Структурируйте данные.

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

Создание диаграммы для итогового отчета

1.Выделите итоговые данные, которые требуется представить в виде диаграммы.

2.Создайте диаграмму.

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

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

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

Вставка промежуточных итогов

Промежуточные итоги подсчитываются с помощью итоговой функции, например Сумма или Среднее, с использованием функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Для каждого столбца можно отобразить несколько типов итоговой функции.

62

Организация электронного документооборота

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

2.Выделите ячейку в диапазоне.

3.Для сортировки столбца, содержащего данные, по которым требуется выполнить группировку, выделите этот столбец и на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка от А до Я или Сортировка от Я до А.

4.На вкладке Данные в группе Структура выберите Промежуточный итог.

Появится диалоговое окно Промежуточные итоги.

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

6.В поле Операция выберите итоговую функцию для вычисления промежуточных итогов. Например, функцию Сумма

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

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

9.Чтобы расположить итоговую строку над строкой данных, снимите флажок Итоги под данными. Чтобы расположить итоговую строку под строкой данных, установите флажок Итоги под данными.

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

Вставка вложенных уровней промежуточных итогов

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

Сортировка и фильтр нажмите кнопку Сортировка от А до Я или Сортировка от Я до А.

2.Вставьте внешние итоги.

Удаление промежуточных итогов

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

2.На вкладке Данные в группе Структура выберите Промежуточный итог.

3.В диалоговом окне Промежуточные итоги нажмите кнопку Удалить все.

УПРАЖНЕНИЕ 15

1.Откройте файл Книги.

2.Выделите диапазон ячеек (В1:Н59).

3.Отсортируйте книги по городу по алфавиту: на вкладке Данные в группе Сортировка и фильтр нажмите Сортировка, укажите столбец сортировки Город в поле

Сортировать по.

4.Вставьте строку после строчки 9, в ячейке D10 наберите Итого Киев и в ячейке H10 просуммируйте все книги изданные в городе Киев. Точно также вставьте итоговые строки после городов Минск, Москва, Санкт-Петербург, Харьков.

63

Организация электронного документооборота

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

Структура и в окне Настройка нажмите кнопку Создать.

6.Выделите строку 63 и на вкладке Данные в группе Структура щелкните Группировать.

7.Нажмите кнопку . для скрытия всех подробных данных.

8.Нажмите среди символов структуры кнопку для отображения всех подробных данных.

9.Уберите структуру данных: на вкладке Данные в группе Структура выберите команду

Разгруппировать и затем Удалить структуру.

10.Сохраните файл с именем Книги-Структура.

ЗАДАНИЕ 15

1.Откройте файл Тарифные коэффициенты и перейдите на лист Таблица рассчетов.

2.Создайте структуру данных (Сумма) по Окладу при изменении должностей.

3.Скройте все подробные данные.

4.Уберите структуру данных.

5.Закройте файл, не сохраняя.

УПРАЖНЕНИЕ 16

1.Откройте файл Книги.

2.Выделите диапазон ячеек (В1:Н59).

3.Отсортируйте книги по городу по алфавиту: на вкладке Данные в группе Сортировка и фильтр нажмите Сортировка, укажите столбец сортировки Город в поле

Сортировать по.

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

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

5.В диалоговом окне Промежуточные итоги заполните следующие поля:

.

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

7.Уберите промежуточные итоги: на вкладке Данные в группе Структура выберите

Промежуточный итог и в диалоговом окне Промежуточные итоги нажмите кнопку

Удалить все.

8.Закройте файл.

64

Организация электронного документооборота

ЗАДАНИЕ 16

6.Откройте файл Тарифные коэффициенты и перейдите на лист Таблица рассчетов.

7.Проставьте промежуточные итоги (Сумма) по Тарифному окладу и по Окладу при изменении должностей.

8.Оставьте на экране только промежуточные итоги и общий итог.

9.Уберите промежуточные итоги.

10.Закройте файл.

4.4.СОЗДАНИЕ ИЛИ УДАЛЕНИЕ ОТЧЕТА СВОДНОЙ ТАБЛИЦЫ ИЛИ СВОДНОЙ ДИАГРАММЫ.

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

В Microsoft Excel 2010 мастер сводных таблиц и диаграмм, доступный в предыдущих версиях Excel, был заменен командами Сводная таблица и Сводная диаграмма на вкладке

Вставить в группе Таблицы. Мастер сводных таблиц и диаграмм все еще доступен, и его при необходимости можно использовать, добавив на панель быстрого доступа или запустив с помощью сочетания клавиш ALT + D + P.

Общие сведения об отчетах сводной диаграммы и сводной таблицы

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

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

Запрос больших объемов данных различными понятными способами.

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

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

Перемещение строк в столбцы или столбцов в строки ("сведение") для просмотра различных сводок на основе исходных данных.

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

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

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

65

Организация электронного документооборота

Исходные данные, в данном случае — из листа

Исходные значения для итога "Кв3 Гольф" в отчете сводной таблицы

Отчет сводной таблицы целиком

Итог исходных данных в ячейках C2 и C8

Пример исходных данных и результирующего отчета сводной таблицы

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

Поле значений, например, Сумма продаж, содержит обобщаемые значения. Итог Кв3 Гольф в предыдущем примере содержит сумму значений по столбцу "Продажи" для всех строк исходных данных, которые содержат в столбце Спорт значение Гольф, а столбце Квартал — значение Кв3. По умолчанию для получения данных в области значений исходные данные в отчете сводной таблицы обобщаются следующим образом: для сложения числовых значений используется функция СУММ, а для подсчета числа текстовых значений

— СЧЁТ.

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

Сведения об отчетах сводной диаграммы

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

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

66

Организация электронного документооборота

Квартальный отчет сводной диаграммы по продажам спортивных товаров

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

Создание отчета сводной таблицы на основе данных листа

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

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

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

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

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

2.На вкладке Вставка в группе Таблицы выберите пункт Сводная таблица или щелкните стрелку под пунктом Сводная таблица и выберите команду Сводная таблица. Откроется диалоговое окно Создание сводной таблицы.

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

Создать сводную таблицу и сводную диаграмму.

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

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

Если данные находятся на другом листе или в другой книге, введите имя книги и листа, используя следующий синтаксис: [имя_книги]имя_листа!диапазон.

67

Организация электронного документооборота

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

кнопкуРазвернуть диалоговое окно .

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

Чтобы поместить отчет сводной таблицы на новый лист, начиная с ячейки A1, щелкните пункт На новый лист.

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

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

6.Для добавления полей к отчету выполните одно или несколько из указанных ниже действий.

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

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

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

Изменения исходных данных, выполненные после создания отчета сводной таблицы, отражаются в отчете после его обновления (Работа со сводными таблицами, вкладка

Параметры, группа Данные, кнопка Обновить).

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

Создание отчета сводной таблицы на основе внешнего источника данных

При создании отчета сводной таблицы на основе внешних данных создается подключение к внешним данным, которые хранятся вне Excel, например, в программе управления базами данных (такой как Microsoft Access или Microsoft SQL Server).

1.Щелкните ячейку на листе.

2.На вкладке Вставка в группе Таблицы выберите пункт Сводная таблица или щелкните стрелку под пунктом Сводная таблица и выберите команду Сводная таблица.

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

Использовать внешний источник данных.

4.Выберите вариант Выбрать подключение.

68

Организация электронного документооборота

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

6.Выберите подключение из списка Выберите подключение и нажмите кнопку

Открыть.

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

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

Создание отчета сводной диаграммы на основе отчета сводной таблицы

1.Выберите отчет сводной таблицы. Откроется панель Работа со сводными таблицами с дополнительными вкладками Параметры и Конструктор

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

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

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

Преобразование отчета сводной диаграммы в обычную диаграмму

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

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

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

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

a)Щелкните отчет сводной диаграммы, который требуется изменить. Откроется панель Работа со сводными диаграммами с дополнительными вкладками Конструктор, Макет, Формат и Анализ.

b)Чтобы найти имя связанного отчета сводной таблицы, на вкладке

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

c)Чтобы найти связанный отчет сводной таблицы, щелкните отчет сводной таблицы в книге. На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры и выберите пункт Параметры. Просмотрите, совпадает ли имя в поле Имя с именем связанного отчета сводной таблицы. Повторяйте действия для каждого отчета сводной таблицы в книге, пока не будет найден отчет с таким же именем в поле Имя.

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

69

Организация электронного документооборота

2.На вкладке Параметры в группе Действия нажмите кнопку Выделить и

выберите команду Всю сводную таблицу.

3.Нажмите клавишу DELETE.

Создание обычной диаграммы на основе части или всех данных в отчете сводной таблицы

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

2.На вкладке Главная в группе Буфер обмена нажмите кнопку Копировать.

3.Выберите пустую ячейку вне отчета сводной таблицы.

4.На вкладке Главная в группе Буфер обмена щелкните стрелку под кнопкой

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

5.На вкладке Вставка в группе Диаграммы выберите нужный тип и подтип диаграммы.

Удаление отчета сводной таблицы

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

2.На вкладке Параметры в группе Действия щелкните стрелку под надписью

Выделить и выберите команду Всю сводную таблицу.

3.Нажмите клавишу DELETE.

Удаление отчета сводной диаграммы

1.Щелкните в любом месте отчета сводной диаграммы, который требуется удалить.

2.Нажмите клавишу DELETE.

Удаление сводной диаграммы не приводит к автоматическому удалению связанного с ним отчета сводной таблицы.

УПРАЖНЕНИЕ 17

1.Откройте файл Книги.

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

3.Выберите источник данных, вид отчета и место для сводной таблицы и нажмите ОК.

70