Построение круговой диаграмм

Построить круговую диаграмму, отобразив на ней Сумма к выплате по кварталам в процентах. Чтобы построить круговую диаграмму, надо выделить на свёрнутой таблице листа Итоги столбцы Период и Сумма к выплате.

Выполнить команду Вставка/Диаграмма или нажать кнопку Мастер диаграмм.

В появившемся окне выбрать тип диаграммы – Круговая. Нажать кнопку Далее. В следующем окне еще раз нажать кнопку Далее. В вести название диаграммы Сумма к выплате.

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

 

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

 

 

Расположить полученную диаграмму на отдельном листе Диаграмма 1. Для этого установить флажок на отдельном. Лист получит имя Диаграмма 1. Дать листу вместо Диаграмма 1 имя Круговая. Нажать кнопку Готово.

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

Построение гистограммы

Построить гистограмму на отдельном листе Смешанный график. Изобразить на нём по периодам Сумму к выплате, Оплачено и Долг (итоги получены ранее). Однако здесь удобно скрыть столбец Разница на листе Итоги. Для этого активизируем любую ячейку этого столбца. Выполним команду Формат/Столбец/Скрыть. Порядок созданиядиаграммы как в предыдущем задании. Выделим необходимые столбцы на листе Итоги:

 

Построим гистограмму:

 

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

Затем выполнить команду Диаграмма/Тип Диаграмм. Выбрать закладку Нестандартные, а после этого из списка выбрать График I гистограмма. Диаграмма примет вид смешанной диаграммы.

 

 

Для линейного графика можно создать дополнительную ось Y-ов справа на графике. Это тем более необходимо, если

значения для линейного графика несоизмеримы со значениями столбцов гистограммы. Щелкнуть по линейному графику и выполнить команду Диаграмма/Параметры диаграммы. В открывшемся окне открыть закладку Оси и установить флажок ось Y(значений) в группе по вспомогательной оси. После этого на графике появится дополнительная ось Y-ов (справа). Нажать кнопку ОК.

 

На столбцы можно нанести соответствующие им значения. Для этого дважды щелкнуть по одному из столбцов гистограммы. В появившемся окне открыть закладку Подписи данных. Установить флажок значения. Нажать ОК.

Можно изменить фон диаграммы. Для этого щелкнуть правой кнопкой мыши по полю диаграммы. Появится контекстное меню. Выбрать из списка Формат области построения… В окне выбрать заливка – обычная. Нажать ОК. Получим фон диаграммы - белый:

 

 

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

 

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

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

Получим:

Свернём таблицу щелчком по кнопке 3, оставив в ней полученные итоги:

Если необходимо построить диаграмму на основе полученных итогов для Количество, Суммы к выплате, Оплачено и Долг, нужно выполнить последовательность действий:

· Создать на двух листах копию листа Рабочая ведомость. Дать им имена Итоги1 и Итоги2.

· Получить на листе Итоги1 итоги для полей Сумма к выплате, Оплачено и Долг.

Лист Итоги1:

· Построить линейный график на основе выделенных данных.

· Получить итоги для Количество заказчиков на листе Итоги2.

Лист Итоги2:

· Выделить область первой полученной диаграммы по результатам листа Итоги1 и выполнить команду Диаграмма / Добавить данные.

· В окне Новые данные указать выделенные данные с листа Итоги2.

· Нажать ОК в этом и в следующем окне.

· Значения для Количество имеют по отношению к другим данным графика небольшие числовые значения. Поэтому для них построим вспомогательную ось У -ов. Выделим построенную диаграмму, затем щёлкнем правой кнопкой мыши по одной из точек Количество на оси Х -ов (такую точку надо найти указателем курсора).

· В меню выбрать Формат рядов данных.

· В следующем окне во вкладке Ось установить флажок на по вспомогательной оси. Нажать ОК.

Появится ещё один линейный график (на рисунке пунктирная линия), для которого установлена ось У-ов справа. На графике отражены 3 точки Количества. График примет вид:

Фильтрация (выборка) данных

Перейти на лист Автофильтр. Отфильтровать данные в поле Период по значению 1 кв и 2 кв, в поле Долг вывести значения, не равныенулю.

Сделать активной любую ячейку таблицы листа Автофильтр. Выполнить команду Данные /Фильтр/Автофильтр. У каждого столбца таблицы появится стрелка. Раскроем список в столбце Период и выберем Условие. Появится окно Пользовательский автофильтр, в котором выполним установки:

Далее требуется из выбранных строк отобрать те, в которых Долг не равеннулю.

В столбце Долг выберем из списка Условие. В окне Пользовательский автофильтр сделаем установки:

После этого получим:

 

Расширенный фильтр

Команда Расширенный фильтр, в отличие от команды Автофильтр, требует задания условий отбора строк в отдельном диапазоне рабочего листа или на другом листе. Диапазон условий включает в себя заголовки столбцов условий и строки условий. Заголовки столбцов в диапазоне условий должны точно совпадать с заголовками столбцов в исходной таблице. Поэтому заголовки столбцов для диапазона условий лучше копировать из таблицы. В диапазон условий включаются заголовки только тех столбцов, которые используются в условиях отбора. Если к одной и той же таблице надо применить несколько диапазонов условий, то диапазонам условий (как именованным блокам) удобно присвоить имена. Эти имена затем можно использовать вместо ссылок на диапазон условий. Примеры диапазонов условий (или критериев отбора):

Сумма к выплате	Адрес
>10000
	Пермь

Если условия расположены в разных строках, то это соответствует логическому оператору ИЛИ. Если Сумма к выплате больше 100000, а Адрес – любой (первая строка условия). ИЛИ если Адрес- Пермь, а Сумма к выплате – любая, то из списка будут отобраны строки, удовлетворяющие одному из условий.

Другой пример диапазона условий (или критерия отбора):

Сумма к выплате	Адрес
>10000	Пермь

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

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

Создадим новый лист Фильтр.

Пример 1. Из таблицы на листе Рабочая_ведомость с помощью расширенного фильтра отобрать записи, у которых Период – 1 кв и Долг+Пеня>0. Результат получить в виде таблицы на листе Фильтр:

Код заказчика

Наименование заказчика

Долг+Пеня

Шапку таблицы результата создать копированием из таблицы Рабочая_ведомость на листе Фильтр, начиная с ячейки А5.

На листе Фильтр создадим диапазон условий в верхней части листа Фильтр в ячейках А1:В2. Названия полей и значения периодов обязательно копировать с листа Рабочая_ведомость. Если выделяемые блоки несмежные, то при выделении применить клавишу Ctrl.

Присвоим имя этому диапазону условий Условие_долг.

Выполним команду: Данные/Фильтр/Расширенный фильтр.

Появится диалоговое окно:

Исходный диапазон и диапазон условий вставлять с помощью клавиши F3.

Установить флажок на скопировать результат в другое место. Поместить полученные результаты на листе Фильтр в диапазон А5:С5 (выделить ячейки А5:С5). Получим результат:

Пример 2. Из таблицы на листе Рабочая_ведомость с помощью расширенного фильтра отобрать строки с адресом Омск за 3 кв с суммой к выплате больше 5000 и с адресом Пермь за 1 кв с любой суммой к выплате. На листе Фильтр создадим диапазон условий в верхней части листа в ячейках D1:F3.

Присвоим имя этому диапазону условий Условие_1.

 

Названия полей и значения периодов обязательно копировать с листа Рабочая ведомость. Затем выполнить команду Данные/Фильтр/Расширенный фильтр.

В диалоговом окне сделать следующие установки:

 

Получим результат:

Пример 3. Выбрать сведения о заказчиках с кодами - К-155, К-347 и К-948, долг которых превышает 5000.

На листе Фильтр в ячейках H1:I4 cоздадим диапазон условий с именем Условие_2.

Названия полей обязательно копировать с листа Рабочая_ведомость.

После выполнения команды Данные/Фильтр/Расширенный фильтр в диалоговом окне сделать следующие установки:

Получим результат:

 

 

Вычисляемые условия

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

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

· В ячейку, где формируется критерий, вводится знак «=»(равно).

· Затем вводится формула, которая вычисляет логическую константу (ЛОЖЬ или ИСТИНА).

Пример 4. Из таблицы на листе Рабочая ведомость отобрать строки, в которых значения Оплачено больше среднего значения по этому столбцу. Результат получить на листе Фильтр в новой таблице:

 

 

· На листе Фильтр создадим «шапку » новой таблицы копированием с листа Рабочая ведомость.

· Для удобства создания вычисляемого условия расположим на экране два окна: одно – лист Рабочая ведомость, другое – лист Фильтр. Для этого выполним команду Окно/Новое. Затем команду Окно/Расположить. Установим флажок на слева направо. На экране появятся два окна, в первом из которых расположим лист Рабочая ведомость, а во втором – лист Фильтр. Благодаря этому удобно создавать формулу для критерия отбора на листе Фильтр.

· Сделаем активной ячейку E22 листа Фильтр, создадим в ней выражение:

· Введем знак = (равно), щелкнем по ячейке F2 на листе Рабочая ведомость (F2 - первая ячейка столбца Оплачено ).

· Введем знак >(больше).

· Введем функцию СРЗНАЧ с помощью мастера функций.

· В окне аргументов этой функции поместим диапазон ячеек F2:F12 (выделим его на листе Рабочая ведомость). Так как диапазон, для которого находим СРЗНАЧ, не меняется, то адреса диапазона должны быть абсолютными, то есть $ F$2:$F$12. Знак $ можно установить с помощью функциональной клавиши F4. В окне функции СРЗНАЧ нажать ОК.

Для проверки выполнения условия со средним значениемсравнивается значение каждой ячейки столбца F. Поэтому в левой части неравенства адрес F2 – относительный (он меняется). СРЗНАЧ в правой части неравенства – величина постоянная. Поэтому диапазон ячеек для этой функции имеет абсолютные адреса $F$2:$F$12.

· В ячейке E22 листа Фильтр сформируется константа Истина или Ложь:

 

 

· Сделаем активной любую свободную ячейку листа Фильтр и выполним команду Данные/Фильтр/Расширенный фильтр.

 

· В диалоговом окне сделаем установки. Исходный диапазон определим клавишей F3. Для ввода диапазона условий выделим ячейки Е21:Е22 листа Фильтр (заголовок столбца вычисляемого условия не заполняется, но выделяется вместе с условием). Для диапазона результата выделим ячейки А21:С21 на листе Фильтр.

· Получим результат:

 

Сводные таблицы

 

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

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

Макет сводной таблицы определяет ее структуру:

· Страница - для размещения полей, по которым выполняется отбор записей;

· Строка – для полей, по которым выполняется группировка;

· Столбец – для размещения полей группировки;

· Данные – для размещения полей, по которым выполняются операции: сумма, среднее значение, количество значений, максимум, проводятся вычисления и т.д. На приведённых далее примерах рассмотрим создание сводных таблиц.

 

Пример 1. Создать сводную таблицу на основе таблицы листа Рабочая ведомость следующего вида:

Выполним команду Данные/Сводная таблица… Затем Далее.

 

В окне указать имя Ведомость_список. (нажать F3 и выбрать Ведомость_список).

 

 

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

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

 

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

Адрес разместим в область страниц. Поле Наименование заказчика в область полей строк, а Период в область полей столбцов.

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

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

В окне Имя ввести Сумма_к_выплате. Нажать ОК.

 

Щелкнуть 2 раза по полю Сумма по полю Долг+Пеня и заменить его на Задолженность.

Щелкнуть 2 раза по другому полю Сумма по полю Долг+Пеня и заменить это название на % задолженности. В этом же окне для получения значения % нажать кнопку Дополнительно>>. В окне Дополнительные вычисления открыть список и выбрать Доля от суммы по столбцу.

Нажать ОК в этом и в следующем окне, а затем Готово. Получим сводную таблицу, показанную выше.

Если надо внести изменения в макет таблицы, то можно воспользоваться панелью Сводные таблицы:

Раскрыть список Сводная таблица и выбрать Мастер, а затем Макет. Илищелкнуть правой кнопкой мыши по сводной таблице. Выбрать Мастер, а затем Макет.

Если такая панель отсутствует, то её можно вызвать командой Вид/Панели инструментов/Сводные таблицы.

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

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

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

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

· Скрыть все элементы за исключением тех, которые интересуют.

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

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

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

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

 

Получим:

 

 

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

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

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

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

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

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

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

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

 

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

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

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

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

Создадим макет сводной таблицы:

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

 

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

 

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

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

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

Создадим макет для сводной таблицы:

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

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

 

После нажатия ОК получим:

 

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

 

Использование вложенных функций ЕСЛИ

Создадим копию листа Рабочая ведомость на листе Если. В таблицу добавим столбец Характер долга.

Пример 1.

Заполним столбец Характер долга с учетом указанных условий: если Долг+Пеня=0, то выдать сообщение «Долга нет». В противном случае, если 0< Долг+Пеня<=2000, то выдать «Долг небольшой». В противном случае, если 2000<Долг+Пеня<=8000, то выдать «Долг большой». В остальных случаях выдать «Долг очень большой».

Установить курсор в ячейку J2 и создать формулу:

=ЕСЛИ(I2=0;"Долга нет";

ЕСЛИ(И(I2>0;I2<=2000);

"Долг небольшой";

ЕСЛИ(И(I2>2000;I2<=8000);

"Долг большой";

"Долг очень большой")))

Ввод формулы производится по шагам:

· Выбрать функцию ЕСЛИ из списка встроенных функций.

· В появившемся окне установить курсор в поле Логическое выражение и нажать красную кнопку для свёртывания окна.

· Создать выражение I2=0 (щёлкнуть по ячейке I2, знак равно и нуль набрать вручную).

· Установить курсор в поле Значение_если _истина и ввести Долга нет.

· Установить курсор в поле Значение_если_ложь и раскрыть список рядом с функцией Если.

 

Выбрать функцию Если и заполнить аналогично параметры нового окна:

· Установить курсор в поле Логическое выражение, раскрыть список функций, показанный выше. Выбрать из него функцию И. Создать выражение И(I2>0;I2<=2000).

· Установить курсор в поле Значение_если _истина и ввести Долг небольшой.

· Установить курсор в поле Значение_если_ложь, раскрыть список функций. Выбрать из него функцию Если, заполнить параметры нового окна:

· Установить курсор в поле Логическое выражение и создать выражение И(I2>2000;I2<=8000).

· Установить курсор в поле Значение_если _истина и ввести Долг большой.

· Установить курсор в поле Значение_если_ложь и ввести Долг очень большой. Нажать ОК. Заполнить столбец Характер долга. Получим:

 

Функции И и ИЛИ позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения: =, >, <, >=, <=, <>. Эти функции имеют одинаковые аргументы, но результаты получаются различными. Например, И(I2>2000;I2<=8000) предполагает выполнение двух условий, а ИЛИ(I2>2000;I2<=8000) только какого-либо одного. Таким образом, функция ИЛИ возвращает значение ИСТИНА, если хотя бы одно из логических выражений истинно. Функция И возвращает значение ИСТИНА, только если все логические выражения истинны.

Слияние

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

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

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

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

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

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

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

Сообщение

Адрес:

Товарищ

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

 

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

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

· Для выбора типа основного документа на панели инструментов нажать кнопку – Настройка главного документа. Откроется окно Тип основного документа. Установить флажок на Письма. Нажать ОК.

Замечание: Если панель инструментов Слияние отсутствует, то её надо открыть с помощью команды Вид/Панели инструментов/Слияние.

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

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

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

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

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

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

· Полученные письма распечатать.

 

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

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

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

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

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

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

· В списке значение следует ввее М.

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

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

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