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

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

Для построения круговой диаграммы надо выделить на свёрнутой таблице листа Итоги столбцы Период и Сумма к выплате (диапазон D1:Е15).

В меню Вставка, в разделе Диаграммы выбрать Круговая

в появившемся окне выбрать тип диаграммы – Круговая.

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

· Щёлкнуть правой кнопкой мыши по диаграмме и выбрать в списке Добавить подписи данных. На диаграмме появляются значения данных в рублях.

· Снова щёлкнуть правой кнопкой мыши по диаграмме и выбрать в списке Формат подписей данных. В открывшемся окне установить:

- «Параметры подписи»- доли,

- «Положение подписи»- У вершины, снаружи.

Чтобы расположить эту диаграмму на отдельном листе, надо в меню Вставка в разделе Расположение щёлкнуть Переместить диаграмму. В открывшемся окне отметить «на отдельном листе», нажать ОК. Назвать лист Круговая.

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

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

 

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

 

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

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

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

В открывшемся окне: открыть закладку Параметры ряда и установить флажок по вспомогательной оси. После этого на графике появится дополнительная ось Y – (справа). Нажать кнопку ОК.

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

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

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

На листе Фильтр для выводарезультата фильтрации создадим шапку таблицы копированием заголовков из таблицы Рабочая_ведомость и расположив, начиная с ячейки А5:

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

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

Долг+Пеня

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

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

Выполним команду: Данные/Сортировка и Фильтр/ Дополнительно.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример 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 раза по другому полю Сумма по полю Долг+Пеня и заменить это название на % задолженности.

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

 

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

Для той же цели для активной сводной таблицы служит команда: группа Работа со сводными таблицами Параметры/ Вычисления/ Дополнительные вычисления или Вычисления/ / Итоги по/ Дополнительно.

 

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

Курсо
р должен находиться в сводной таблице.

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