Задание для самостоятельного выполнения

1. С помощью АВТОФИЛЬТРА самостоятельно:

• найдите планеты, имеющие диаметр менее 50 тыс. км и массу менее 4*10²⁴ кг;

• н айдите планеты, находящиеся от Солнца на расстоянии не менее 500 млн. км, имеющих массу в диапазоне от 3*10²⁴ кг до 500*10²⁴ кг, а также не более 2 спутников.

2. С помощью СОРТИРОВКИ:

• Отсортируйте все планеты в порядке возрастания расстояния от Солнца.

• Отсортируйте данные в таблице в алфавитном порядке названий планет.

• Отсортируйте данные в порядке возрастания их расстояния от Солнца.

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

Задание 2. С использованием Расширенного фильтра осуществить поиск планет с периодом обращения более 10 земных лет и количеством спутников не менее 2.

Расширенный фильтр  гораздо более гибкий инструмент работы с дан­ными, чем автофильтр. Однако перед тем как использовать его, необ­ходимо создать диапазон критериев, в котором описываются все усло­вия поиска.

Диапазон критериев должен соответствовать следующим требованиям:

• Диапазон должен включать как минимум две строки. Причем в первой строке размещаются названия полей списка. Другая строка (или строки) содержит критерии отбора записей, опре­деленные пользователем. Во избежание ошибок названия полей списка следует скопировать, а не вводить вручную.

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

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

• Значения условий, заданных в разных строках, связаны опера­ тором ИЛИ.

• Пустая строка критерия означает, что условия отбора нет.

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

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

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

Установить курсор в любую ячейку списка → Команда Данные → фильтр → Расширенный фильтр

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

Рис. 1. Диалоговое окно Расширенный фильтр

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

Затем в текстовом поле Исходный диапазон следует задать адрес обра­батываемой базы данных (путем выделения всех ее ячеек, включая заго­ловки полей). Этот адрес, как правило, устанавливается по умолчанию, и пользователь может его подтвердить либо изменить по своему усмотре­нию.

В текстовом поле Диапазон условий следует задать соответствующие ссылки на ячейки, содержащие условия отбора записей (диапазон кри­териев).

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

После заполнения предложенной формы ввод параметров фильтрации подтверждается кнопкой ОК. Excel отфильтрует список в соответствии с заданными условиями, не отображая записи, не удовлетворяющие кри­териям фильтра.

Примечания

1. При использовании расширенного фильтра целесооб­разнее помещать диапазоны условий выше и правее исходной базы данных, если она содержит не более 1020 столбцов и не более 200250 записей. В про­тивном случае диапазон условий целесообразнее раз­мещать на новом листе.

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

3. После изменения значений диапазона условий резуль­тат выборки расширенного фильтра автоматически не изменяется. Требуется его повторное применение.

Рис. 2.

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

Для выполнения действий по фильтрации необходимо воспользоваться командами меню: ДАННЫЕ, ФИЛЬТР, РАСШИРЕННЫЙ ФИЛЬТР. В диалоговом окне необходимо указать координаты интервалов.

При использовании РАСШИРЕННОГО ФИЛЬТРА необходимо сначала определить (создать) три области (рис.2):

- исходный диапазон - это область базы данных (А2:F12);

- диапазон условий отбора (или интервал критериев) - это область, где задаются критерии фильтрации (А14:F15);

- диапазон, в который при желании пользователя Excel помещает результат выборки (интервал извлечения) - это та область, в которой будут появляться результаты фильтрации (А17:F21).

Если необходимо получать результаты фильтрации в интервале извлечения, нужно поставить [*] - СКОПИРОВАТЬ РЕЗУЛЬТАТ  В   ДРУГОЕ МЕСТО (рис. 3).

1. Создайте диапазон условий отбора в ячейках A14:F15 (см. рис. 2).

2. Запишите условия отбора в диапазон условий отбора (см. рис. 2).

3. Создайте интервал извлечения, куда будут помещены результаты фильтрации в ячейки A17:F17

4. Поместите курсор в область базы данных (A2:F12)

5. Выполните команды: ДАННЫЕ, ФИЛЬТР, РАСШИРЕННЫЙ ФИЛЬТР.

6. Проверьте правильность задания интервалов (см.рис. 3). Нажмите кнопку <ОК>.

Проверьте! Найдены планеты Юпитер, Сатурн, Уран, Нептун.

Рис. 3

7. Сохраните результаты в файле Планеты солнечной системы.xls

Задание для самостоятельного выполнения

С использованием Расширенного фильтра самостоятельно:

1) найдите  записи о планетах, имеющих период обращения более 2 земных лет и экваториальный диаметр менее 50 тыс. км

2) осуществите поиск планет, находящихся от Солнца на расстоянии более 1000 млн. км и имеющих 1 спутник

3) найдите записи о планетах Меркурий, Сатурн или Плутон

4) найдите записи о планете Меркурий или планетах, имеющие   более одного спутника

5)  найдите записи о планетах, имеющие период обращения более 1 года и массу менее 100 * 10²⁴ кг

6) найдите записи о планетах, которые расположены от Солнца на расстоянии более 1000 млн. км  с диаметром более 50 тыс. км.   или находящихся от Солнца на расстоянии менее 150 млн. км и имеющие диаметр более 10 тыс. км.

7) найдите записи о планетах у которых период обращения менее 10 лет и количество спутников не равно 0 или записи о планетах, у которых диаметр менее 10 тыс. км.

Задание 3. Применение вычисляемый критерий, применение критериев расширенного фильтра рассмотрим на базе данных магазина «Ирикон» (рис. 1), содержа­щей информацию о поступивших на ее склад.

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

Применяя вычисляемый критерий, необходимо соблюдать следующие правила:

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

• Ссылки на ячейки внутри списка должны быть относительны­ми, а ссылки на ячейки вне списка  абсолютными.

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

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

• Вычисляющая формула представляет собой логическую фор­мулу, которая возвращает значение Истина или Ложь.

Рис. 1. База данных магазина «Ирикон»

- Отобрать из базы данных магазина товары, стоимость партии которых находится в пределах от 15 000 до 30 000 руб. включительно.

- Отобрать из базы данных магазина товары, поступившие не раньше 10.09.2007 и не позднее 21.11.2007.

- Отобрать из базы данных товары, которые поступили на склад в коли­честве больше 50 штук, и стоимость партии которых была меньше 1 500 000 руб.

- Отобрать из базы данных магазина следующие товары: Ноутбук ASUS A3L, стоимость которого менее 29000 руб., Мобильный телефон Nokia 8800 стоимостью более 36 000 руб. и Телевизор 54-55 см Витязь 54 CTV стоимостью менее 3000 руб.

- Отобрать из базы данных товар, стоимость партии которого меньше среднего значения по магазину.

Диапазон критериев с вычисляемым полем Среднее значение и резуль­тат отбора показаны на рис. 2.

Рис. 2. Диапазон условий и результат применения расширенного фильтра для вычисляемого критерия

- Отобрать из базы данных сведения о телевизорах, поступивших на склад с 05 ноября по 21 декабря 2007 г., стоимость партии поставки кото­рых была больше среднего значения по магазину, а также о товаре, сто­имость поставки которого была максимальной. Внимание: предварительно в базе данных измените количество Мобильных телефонов Nokia 8800 на 160.

Диапазоны критериев с безымянными вычисляемыми полями и резуль­тат отбора показаны на рис. 3.

Рис. 3. Диапазон условий и результат применения расширенного фильтра для нескольких вычисляемых критериев

При формировании критериев для ограничения сроков поставки теле­визоров была применена функция работы с датами ДатаЗнач, преобра­зующая дату из текстового представления в формат даты. Для выборки информации о других товарах с максимальным объемом поставки был применен символ шаблона *. Без указания данного символа информа­ция о фотоаппарате будет отсутствовать.

Задание для самостоятельного выполнения

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

Лабораторная работа № 6. ФОРМИРОВАНИЕ ИТОГОВ. СВОДНЫЕ ТАБЛИЦЫ

Цель работы:

- научиться формировать итогов;

- научиться подведению промежуточных итогов;

- создавать и редактировать сводные таблицы;

- использование консолидации данных.

Задание 1. Создайте базу данных объема продаж по регионам. Подумайте, как ускорить данный процесс.

Рис. 1. Фрагмент таблицы объема продаж по регионам

Для получения различных итоговых сведений по определенным груп­пам данных и в целом по списку используется специальная операция, запускаемая командами: Данные → Итоги.

Аналогом операции является специальная встроенная функция ПРОМЕЖУТОЧНЫЕ ИТОГИ.

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

Технология получения промежуточных итогов заключается в выполне­нии следующих действий:

1. Установить курсор в пределах Списка → Команда Данные → Итоги.

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

Рис. 2. Диалоговое окно Промежуточные итоги

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

• Если установлен флажок Заменить текущие итоги, то автома­тически будут удалены старые существующие итоговые значе­ния и заменены новыми, рассчитанными по другим формулам.

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

• Если установлен флажок Итоги под данными, то промежуточ­ные итоги размещаются под данными.

• Кнопка Убрать все очищает заданные параметры промежуточ­ных итогов.

Таблица 1.

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

Словесное наименование операции	Номер операции	Встроенная функция – аналог операции
Среднее	1	СРЗНАЧ
Количество чисел	2	СЧЕТ
Количество	3	СЧЁТЗ
Максимум	4	МАКС
Минимум	5	МИН
Произведение	6	ПРОИЗВЕД
Смещенное отклонение	7	СТАНДОТКЛОН
Несмещенное отклонение	8	СТАНДОТКЛОНП
Сумма	9	СУММ
Смещенная дисперсия	10	ДИСП
Несмещенная дисперсия	11	ДИСПР

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

Задание 2. Имеется таблица условных объемов продаж товаров по месяцам для нескольких регионов (рис. 1).

Требуется подвести итоги по каждому региону за год.

Технология подведения промежуточных итогов по регионам заключа­ется в следующем.

1. Отсортируйте список по полю, по которому требуется подвести про­межуточный итог, в нашем примере — это поле Регион.

2. Выделите любую ячейку списка.

3. Выполните команды: Данные → Итоги.

4. В диалоговом окне Промежуточные итоги в поле При каждом изме­нении в выберите имя поля, по которому образованы группы данных и для которого необходимо подвести итог, в нашем примере таким полем будет Регион. В поле окна Операция выберите итоговую фун­кцию, в нашем случае такой операцией будет Сумма. В поле Доба­вить итоги по пометьте флажком те поля, по которым требуется под­вести итоги, в данном случае таким полем является Объем продаж. Установите флажки в нижней части диалогового окна, если это необ­ходимо.

5. Для закрытия диалогового окна щелкните по кнопке ОК. Результаты подведения итогов представлены на рис. 14.3.

Для облегчения анализа выполнения операции Промежуточные итоги числовые поля с итоговыми сведениями на рисунке снабжены приме­чаниями, содержащими сгенерированные формулы. По Краснодарскому региону текущие сведения раскрыты. По другим регионам — закрыты.

• Для их открытия выполните щелчок мышью по значку +, находящемуся левее итоговой строки; для закрытия  щелчок по значку «-» скроет соответствующие текущие сведения.

• Маленькие кнопочки «1», «2» и «3», представленные в левом верхнем углу после подведения промежуточных итогов (рис. 14.3), позво­ляют выводить на экран различные уровни детализации данных.

Рис. 3. Фрагмент окна Excel с итогами объема продаж по регионам

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

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

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

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

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

Создайте таблицу с данными о заключенных кредитных догово­рах юридических и физических лиц по нескольким филиалам банка аналогично рис. 1.

Рис. 1. Фрагмент таблицы кредитных договоров банка с юридическими и физическими лицами

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

• сколько заключено договоров в каждом филиале банка, и какова доля каждого филиала в общем объеме;

• какова общая сумма кредитования юридических и физических лиц в каждом филиале;

• в каком филиале было заключено наибольшее количество дого­воров по каждому типу и в какой валюте;

• каково распределение кредитных договоров по срокам;

• какой отрасли выдается большее количество кредитов в каж­дом из филиалов?

Создание сводной таблицы осуществляется выбором команд меню: Данные → Сводная таблица...

Шаг 1. Задание источника данных

• Н а данном этапе (рис. 2) необходимо выбрать, что является источником данных для сводной таблицы.

Рис. 2. Диалоговое окно Шага 1

Мастера сводных таблиц и диаграмм

• Установите вид созда­ваемого отчета: сводная таблица.

Выполним щелчок левой кнопкой мыши по кнопке окна Далее.

Шаг 2. Определение интервала исходных данных

В этом диалоге (рис. 3) необходимо указать диапазон исход­ных данных, предназначенный для построения сводной таблицы.

• Укажите интервал, выделив диапазон данных, воспользовавшись трехцвет­ной кнопкой.

Параметры...

Рис. 3. Диалоговое окно шага 2

Мастера сводных таблиц и диаграмм

Вид окна Мастера сводных таблиц и диаграмм на шаге 2 напрямую зависит от источника данных, который был выбран на первом шаге. Если предполагается формировать сводную таблицу из внешних источ­ников данных (в том числе созданных не на основе таблиц Excel, воз­можно, размещенных на удаленных компьютерах сети), то окно шага 2 имеет вид, представленный на рис. 4.

Рис. 4. Диалоговое окно шага 2 Мастера сводных таблиц и диаграмм

при указании на шаге 1 внешнего источника данных

Используя кнопку указанного окна Получить данные, в новом окне (рис. 14.8) можно указать в качестве источника Базы данных, Запросы, Кубы ОLAP. Используя кнопку Обзор, можно найти требуемые источ­ники, а с помощью кнопки ОК можно выбрать искомые файлы, следуя появляющимся инструкциям.

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

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

Рис. 5 Диалоговое окно шага 2а

Мастера сводных таблиц и диаграмм при указании на шаге 1 нескольких диапазонов консолидации

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

Рис. 6. Диалоговое окно шага 26

Мастера сводных таблиц и диаграмм при указании на шаге 1 нескольких диапазонов консолидации

Если сводная таблица строится на базе другой сводной таблицы или сводной диаграммы, размещенных в текущей книге, окно шага 2 имеет вид, представленный на рис. 7. В данном окне необходимо выделить требуемый лист.

Рис. 7. Диалоговое окно шага 2

Мастера сводных таблиц и диаграмм при выборе данных на шаге 1 из других сводных таблиц

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

Шаг 3. Формирование таблицы

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

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

Рис. 8. Окно шага 3 Мастера сводных таблиц и диаграмм

• Присвойте имя сводной таблице можно, нажав кноп­ку Параметры (рис. 9).

• Изучите все параметры сводной таблицы.

Рис. 9. Окно Параметры сводной таблицы

• Структура сводной таблицы определяется после нажатия кнопки Ма­кет и открытия диалогового окна настройки макета сводной таблицы (рис. 10).

Рис. 10. Диалоговое окно формирования структуры сводной таблицы

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

Макет сводной таблицы состоит из следующих фиксированных облас­тей:

• Страница. Значения в данной области выступают в качестве эле­ментов страницы сводной таблицы;

• Строка. Значения в данной области выступают в качестве эле­ментов строки в сводной таблице, т.е. в качестве заголовков строк таблицы;

• Столбец. Значения в данной области выступают в качестве эле­ментов столбца в сводной таблице, т.е. в качестве заголовков столбцов таблицы;

• Данные. В данной области отображаются детальные и итого­вые для сводной таблицы поля, которые составляют содержи­мое таблицы.

Таблица 2.

Значения параметров сводной таблицы

Параметр	Назначение (действие)
Имя	Присвоение имени. Excel автоматически задает имена в форме СводнаяТаблица1 и т.д.
Общая сумма по столбцам	Вычисление общей суммы для элементов, отображаемых в столбцах
Общая сумма по строкам	Вычисление общей суммы для элементов, отображаемых в строках
Автоформат	Установка одного из типов автоформатирования
Включать скрытые значения	Включение скрытых элементов, находящихся на странице, в промежуточные суммы
Объединять ячейки заголовков	Объединение ячеек внешней строки и столбца заголовков
Сохранять форматирование	Сохранение форматирования после обновления сводной таблицы
Повторять подписи на каждой странице печати	Расположение подписи строк на каждой стра­нице распечатанного отчета сводной таблицы
Макет страницы	Установка порядка, в котором будут отобра­жаться поля страницы
Число полей в столбце	Задание числа полей страницы, отображаемых в одной строке страницы
Для ошибок отображать	Установка значения, которое будет отобра­жаться для ячеек сводной таблицы, содержащих ошибку
Для пустых ячеек отображать	Установка значения, которое будет показано для пустых ячеек сводной таблицы
Печать заголовков	Расположение подписи столбцов на каждой странице распечатанного отчета сводной таблицы
Помечать итоги	Отображение звездочкой индикации того, что промежуточные и итоговые суммы включают и отображаемые, и скрытые элементы. Данный параметр активизирован только в том случае, когда сводная таблица создается из источника данных OLAP
Сохранять данные вместе с таблицей	Сохранение дополнительной копии данных
Развертывание разрешено	Просмотр элементов, составляющих итоговые значения
Обновить при открытии	Обновление сводной таблицы при каждом открытии рабочей книги
Обновлять каждые... минуты1	Задание интервала времени, через который происходит обновление сводной таблицы в открытой рабочей книге
Сохранить пароль1	Сохранение пароля для открытия внешней базы данных
Фоновый запрос1	Выполнение запроса базы данных в фоновом режиме, что позволяет не прерывать работу
Оптимизировать память1	Уменьшение объема памяти, используемой при обновлении запроса внешней базы данных

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

• Сформируйте структуру сводной таблицы путем пере­таскивания полей базы данных на соответствующие области макета. Размес­тите на макете только те из них, которые позволяют получить искомые итоговые данные (рис. 11).

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

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

Рис. 11. Диалоговое окно после завершения формирования структуры сводной таблицы

• По завершению формирования макета нажмите ОК, а затем и по кнопке Готово. На рабочем листе появится сводная таблица. Вид ее применительно к рассматрива­емому примеру показан на рис. 12.

Рис. 12. Фрагмент окна сформированной сводной таблицы

Как видно, поля, которые были помещены в области макета Страница, Строка и Столбец, отображаются в виде раскрывающихся списков. Так, для элемента области Страница «Отрасль» можно выбрать в списке параметр с именем «Все», что обеспечит вывод сведений по всем отрас­лям базы данных (в нашем примере: для юридических лиц – меди­цина, промышленность, торговля, для физических лиц – предприни­матель), а можно указать только определенные параметры, что обеспе­чит вывод соответствующих данных.

• Выведите информацию только о клиентах – юридических лицах.

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

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

Задание 4.

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

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

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

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

2. На панели инструментов Сводные таблицы раскройте список кнопки Сводная таблица и выберите команды: Формулы → Вычисляемое поле... В результате появится диалоговое окно Вставка вычисляемого поля (рис. 13).

3. В поле окна Имя введите название вычисляемого поля (в при­мере – Плата банку за издержки).

4. В поле Формула введите формулу расчета нового поля

(в при­мере: ='Сумма кредита'*0,5%).

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

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

5. Д ля добавления созданного поля в сводную таблицу нажмите Добавить.

6. Для закрытия диалогового окна нажмите ОК.

Рис. 13. Окно формирования формулы вычисляемого поля сводной таблицы

Сводная таблица после добавления в нее вычисляемого поля приобре­тет следующий вид (рис. 14).

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

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

Рис. 14. Фрагмент сводной таблицы с вычисляемым полем

Сформируйте вычисляемый элемент, отобража­ющий сумму кредита по Московскому региону.

Выполните следующие действия:

1. Поместите курсор на то поле, которое будет содержать новый вычисляемый элемент. В нашем примере таким полем будет поле Филиал.

2. На панели инструментов Сводные таблицы раскройте список кнопки Сводная таблица и выберите команды: Формулы → Вычисляемый объект...

В результате появится диалоговое окно Вставка вычисляемого элемента (рис. 15).

Рис. 15. Окно формирования формулы вычисляемого элемента сводной таблицы

3. В поле Имя укажите название нового элемента, в поле Фор­мула введите формулу расчета нового элемента. В нашем при­мере имя нового объекта — Итоги по Московскому региону, а формула имеет следующий вид: ='Перово'+Сокол+Фили.

4. Для добавления созданного нового элемента в поле сводной таблицы щелкните по кнопке Добавить.

5. Для закрытия диалогового окна щелкните по кнопке ОК.

Иногда требуется сгруппировать данные в соответствии с определен­ным временным интервалом. Например, на основании базы данных (рис. 1) необходимо проанализировать выдачу кредитов по меся­цам (кварталам).

• Для этого первоначально создайте сводную таблицу на отдельном листе, в которой согласно макету (рис. 10) в области строк приведены даты выдачи кредитов, в области столбцов, например, филиалы, в кото­рых выданы кредиты, в области данных – суммы кредитов.

Результат выполненных действий должен быть, как на рис. 16.

Рис. 16. Сводная таблица выдачи кредитов в филиалах по датам

Далее для создания группы по месяцам (кварталам) необходимо выпол­нить следующие операции:

1 ПКМ по ячейке в пределах столбца Дата выдачи кредита → Команда Группа и структура → Группировать...¹ → Задать интервал группировки → С шагом: Месяцы (Кварталы, Годы,...) → ОК

¹ Последующие операции выполняются в окне, представленном на рис. 17.

Рис. 17. Окно задания параметров группировки сводной таблицы

В результате будет сформирована сводная таблица с группировкой дан­ных по месяцам (рис. 18).

Рис. 18. Сгруппированная сводная таблица выдачи кредитов в филиалах по месяцам

• Разгруппируйте сводную таблицу (исполь­зуя контекстное меню) и выполните новую группировку по кварталам.

Завершив формирование сводной таблицы, представьте ее более красочно, в виде одного из 22 вариантов оформления – 10 отчетов, 10 таблиц, классической сводной таблицы и макета без форматирова­ния (рис. 18). Установите курсор в пределах сводной таблицы → Кнопка Формат отчета панели Сводные таблицы → Выбрать макет отчета → Кнопка ОК.

Рис. 18. Окно Автоформатирования сводной таблицы

Задание 5. Консолидация данных

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

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

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

Консолидировать данные можно несколькими способами:

• по расположению данных;

• по категориям;

• с помощью трехмерных ссылок в формулах.

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

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

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

Рассмотрим реализацию метода консолидации.

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

1. Создайте таблицы одинаковой структуры посещаемости стран за январь (на новом листе) и за февраль (на новом листе) (см. рис. 1).

Рис. 1 Таблица посещаемости стран за февраль

2. Выделите на новом листе одну или несколько ячеек.

3. Используя оконное меню, выберите команды: Данные → Кон­солидация. В результате на экране появится диалоговое окно Консолидация (рис. 2).

4. В диалоговом окне в поле Функция выберите функцию для обобщения данных (в нашем примере, функцию Сумма).

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

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

7. Повторите ввод других диапазонов данных, участвующих в обобщении, используя поле Ссылка и кнопку Добавить.

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

9. Закройте диалоговое окно кнопкой ОК, что приведет к объеди­нению данных в одну или несколько изначально выделенных ячеек. Консолидация будет завершена.

Рис. 2. Диалоговое окно консолидации данных

Рис. 3. Результат консолидации данных