Построение диаграммы по данным сводной таблицы

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

· удалить из таблицы промежуточные и общие итоги;

· убедиться, что таблица имеет не более двух полей в области столбцов и строк;

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

· удалить в таблице Общий итог и Итоговые строки;

· по полученной сводной таблице построить диаграмму.

· На панели инструментов щёлкнуть по кнопке Сводная диаграмма

Получим:

 

 

 

Все данные полученной сводной таблицы отражаются на диаграмме, что не очень наглядно. При желании можно отразить только те данные, которые необходимы в данный момент. Например, пусть требуется показать на графике из сводной таблицы Сумма к выплате и Задолженность по 2-ому и 3-ему кварталам для заказчиков – Антонов и Волкова. Такой выбор можно сделать на диаграмме с помощью списков, которые видны на диаграмме:

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

 

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

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

рассчитаем Долг в % по каждому городу внутри квартала. Для этого на панели Работа со сводными таблицами/ Параметры/ Вычисления выбрать из списка Поля, элементы и наборы, а затем Вычисляемое поле. В окне Вставка вычисляемого поля ввести имя и формулу. Нажать ОК.

В области данных макета появится еще одно поле Сумма по полю Долг в %. Изменим название поля на %_долга. В этом жеокне нажмем кнопку Формат.

Установить формат Процентный и два десятичных знака.

Нажать ОК в этом и следующем окне. Затем нажать Готово. Получим сводную таблицу:

Группировка элементов по временным диапазонам

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

Пример 3. Скопируем лист Рабочая ведомость на лист Группировка. Добавим столбцы Дата и Месяц в конце таблицы и заполним их данными (смотри
таблицу ниже). Столбец Период удалим.

 

На основе полученной таблицы создадим сводную таблицу с получением сумм Сумма к выплате и Оплачено по месяцам и заказчикам.

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

Фрагмент сводной таблицы выглядит так:

 

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

 

В появившемся окне Группирование никаких установок делать не надо. Нажать ОК. Получим сводную таблицу, в которой даты сгруппированы по месяцам и суммируются по каждому заказчику:

Пример 4. На основе исходной таблицы, скопированной на лист Группировка, создадим сводную таблицу, в которой по месяцам выведем Сумму к выплате и Оплачено. Сгруппируем данные по месяцам с шагом 3, т.е. по кварталам. Отметим нужные поля сводной таблицы:

Получим сводную таблицу:

Выделим ячейку в столбце месяц на сводной таблице и выполним команду Группировать/Группа по выделенному. В появившемся окне Группирование установим шаг – 3 в окне с шагом:

 

Получим в сводной таблице группы:

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

Слияние. Письма и рассылки

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

Операцию можно выполнять, используя Мастер слияния. Рассмотрим слияние на результатах примера листа Если.

Последовательность выполнения работы:

1. подготовка источника данных в Excel;

2. создание постоянной части основного документа в Word;

3. вставка в основной документ полей слияния и полей Word;

4. предварительный просмотр результатов слияния;

5. получение составного документа.

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

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

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

 

Сохраним файл под именем Учебная задача и закроем его.

Порядок создания писем:

· Создать в Word основной документ (например, Сообщение). Внести в него постоянный текст.

Сообщение

Адрес:

Заказчик

Предлагаем немедленно погасить задолженность сумме

 

Бухгалтер Петров В.В.

· Сохранить документ в своей папке (папке студента), под именем Сообщение. Документ Сообщение не закрывать.

· Для выбора типа основного документа на панели инструментов нажать кнопку Рассылки/ Начать слияние

· Установить флажок Письма.

·
Для подключения к источнику данных нажать на панели инструментов кнопку – Использовать существующий список.

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

· В следующем окне выделить лист Слияние - источник данных и нажать ОК.

·
Установить курсор в основной документ после пробела за словом Адрес и нажать на панели инструментов кнопку – Вставить поля слияния. Выбрать название первого поля Адрес и нажать кнопку Вставить.

· Эти же операции повторить для полей Наименование заказчика и поля Долг+Пеня.

· После того, как вставлены все поля, на панели инструментов нажать кнопку – Просмотр результатов.

· Заполнится данными одно письмо. С помощью кнопки Перейти к записи можно просмотреть другие письма.

·
Для создания всех писем, надо выполнить слияние с помощью кнопки – Найти и объединить.

· Откроется окно Составные новые документы. Установить флажок Все и нажать ОК. Все письма создаются на отдельных страницах Их можно просмотреть и распечатать.

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

Для изменения обращения необходимо:

· Установить курсор в начало той строки, в которой должно быть обращение к клиенту.

·
Нажать кнопку Добавить поле Word на панели Слияние и выбрать из списка Правила строку IF…THEN…ELSE.

· В появившемся диалоговом окне в списке поле выбрать слово “Пол”.

· В списке оператор выбрать слово “Равно” и в списке значение следует ввести М.

· В окне Вставить следующий текст: Уважаемый господин.

· В окне В противном случае вставить следующий текст: Уважаемая госпожа.

· Нажать кнопку ОК.