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

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

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

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

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

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

· Выделить блок данных.

· Построить линейную диаграмму.

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

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

· В окне «Новые данные» указать выделенные данные с листа Итоги2. Нажать ОК в этом окне и в следующем.

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

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

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

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

Появится ещё одна линия, на которой отражены 3 точки для Количества. Получим диаграмму:

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

Автофильтр

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

Долг+Пеня

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

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

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

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

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

Установить флажок на «скопировать результат в другое место» и провести курсором на листе Фильтр диапазон А5:С5. В «Поместить результат в диапазон» получим: Фильтр!$A$5:$C$5

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

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

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

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

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

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

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

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

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

 

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

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

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

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

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

· В формуле указываются ссылки на ячейки списка. В левой части формулы (до знака сравнения) ссылки на ячейки списка должны быть относительными, в правой части формулы – абсолютными. Ссылки на ячейки вне списка должны быть всегда абсолютными.

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

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

 

· Для удобства создания вычисляемого условия можно расположить на экране два окна: одно – лист Рабочая ведомость, другое – лист Фильтр. Для этого выбрать в меню Вид в разделе Масштаб/ Новое окно .

· Кликнуть правой кнопкой мыши по панели задач и выбрать Окна слева направо. На экране появятся два окна, в первом из которых расположится лист Рабочая ведомость, а во втором – лист Фильтр. Так удобнее создавать формулу для критерия отбора на листе Фильтр.

 

· Сделать активной ячейку E22 листа Фильтр и ввести знак
= (равно)

· Кликнуть по ячейке F2 на листе Рабочая ведомость (F2 - первая ячейка столбца Оплачено ) и на листе Рабочая ведомость в строке формул после =’Рабочая ведомость’!F2 ввести знак >(больше)

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

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

Проверить в строке формул:

 

· В окне функции СРЗНАЧ нажать ОК.

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

 

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

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

Получим:

 

Нажать ОК. Получим результат:

После выполнения четырёх задач на листе Фильтр получим:

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

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

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

1.подтвердить выбор таблицы Рабочая ведомость, как источник исходных данных;

2.указать, что отчёт сводной таблицы поместить на новый лист.

Нажать ОК.

Открывается новый лист:

 

Справа на листе создаётся структура отчёта сводной таблиц путём размещения полей в следующих областях:

 

Фильтр отчёта – для разме-щения полей, по которым выполняется отбор (фильтра-ция) данных.

Названия строк – для полей, по которым выполняется группировка.

Названия столбцов – для раз-мещения полей группировки.

∑ Значения -для размещения полей, по которым выполня-ются операции: сумма, среднее значение, количество значений, максимум,проводятся вычис-ления и т.д.

 

На приведённых далее примерах рассмотрим создание сводных таблиц.

Пример 1. Создать отчёт сводной таблицы на основе таблицы Рабочая ведомость. Поместить в фильтр отчёта Адрес, в название строки – Наименование заказчика, в название столбцов – Период, в значения – Сумма к выплате и Долг+Пеня.

 

Последовательность действий:

 

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

 

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

 

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

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

 

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

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

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

Чтобы переименовать поля в области данных, надо дважды щелкнуть по полю мышью. В окне Имя ввести Сумма_к_выплате. Нажать ОК.

 

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

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

Получим :

 

 

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

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

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

 

 

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

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

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

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

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

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