Использование мастера условного форматирования

Диалоговое окно мастера условного форматирования вызывается через меню ФОРМАТ, пункт Условное форматирование. На рис.4.7 представлено диалоговое окно для задания первого условия и создания формата. Формат будет применен к содержимому ячейки в том случае, если содержимое ячейки удовлетворяет ограничениям условия. Может быть задано до трех условий, для этого следует изменить вид диалогового окна, щелкнув по кнопке А также>>.

Создадим и запишем в ячейку А1 условный формат, решающий следующую задачу: изменять цвет шрифта и фона в зависимости от того, в каком из трех диапазонов значений оказывается вводимое в ячейку число:

- для диапазона от 5 до 15 – цвет шрифта красный, цвет фона зеленый;

- для диапазона от 16 до 29 – цвет шрифта желтый, цвет фона синий;

- для диапазона от 30 до 44 – цвет шрифта зеленый, цвет фона черный.

 

 

 

 

Рис. 4.7. Диалоговое окно мастера условного форматирования

На рис.4.8 представлено диалоговое окно Условное форматирование в развернутом виде под три условия, заданные как диапазон типа между, а также содержащее готовые форматы для цвета шрифта и фона. Если в ячейку А1 будет введено число, не попадающее ни в один из трех диапазонов, оно будет представлено в ячейке в формате по умолчанию – черным шрифтом на белом фоне.

Рис.4.8. Окно условного форматирования под три цвета

Копирование форматов

Для копирования форматов на Панели инструментов имеется пиктограмма инструмента Формат по образцу .

Обрамление таблиц

1. Выделите таблицу.

2. Войдите в меню ФОРМАТ и выберите пункт Ячейки... В диалоговом окне ФОРМАТ ЯЧЕЕК (рис.4.9) выберите вкладку Граница.

3. Задайте тип линий, цвет и вид обрамления. Щелкните по кнопке ОК.

Рис.4.9. Вкладка Граница диалогового окна Формат ячеек

Заливка ячеек

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

1. Выделите ячейку или таблицу.

2. Войдите в меню ФОРМАТ и выберите пункт Ячейки... В диалоговом окне ФОРМАТ ЯЧЕЕК (рис.4.9) выберите вкладку Вид.

3. Задайте цвет и узор заливки. Щелкните по кнопке ОК.

Защита данныхот изменения

1. Войдите в меню ФОРМАТ, выберите пункт Ячейки... и в открывшемся диалоговом окне перейдите на вкладку Защита.

2. Поставьте флажок Защищаемая ячейка.

3. Войдите в меню СЕРВИС и выберите пункт Защита, затем Защитить лист. Будет предложено ввести пароль. После подтверждения ячейка станет недоступной для ввода новых значений и изменения старых. При попытке внести изменения или записать новую информацию будет выводиться соответствующее окно предупреждения (рис.4.10).

Рис.4.10.Вид окна предупреждения

Создание примечаний к ячейкам

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

2. Войдите в меню СЕРВИС, выберите пункт Параметры, вкладку Вид, группу Примечания и одну из трех опций режима отображения примечания Только индикатор.

3. Войдите в меню ВСТАВКА и выберите пункт Примечание или щелкните правой кнопкой мыши по ячейке и в контекстном меню выберите пункт Добавить примечание.

4. Убедитесь, что рядом с ячейкой появилось текстовое поле; введите в поле текст примечания, например разъяснение к записанному в ячейке формату (рис.4.11).

Рис.4.11. Введение текста примечания

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

6. Чтобы отредактировать или удалить примечание, щелкните правой кнопкой мыши на ячейке с примечанием и в появившемся контекстном меню выберите соответствующие пункты Изменить примечание или Удалить примечание.

КОНТРОЛЬНЫЕ ВОПРОСЫ К РАЗДЕЛУ 4

1. Какие операции относятся к форматированию?

2. Как выполнить перенос слов в ячейке?

3. Какие форматы числовых данных Вы знаете?

4. Что собой представляет пользовательский формат?

5. Как защитить данные от изменения?

6. Как осуществить обрамление таблицы?

7. Как создать примечание к ячейкам?

8. Что представляет собой экспоненциальный формат числа?

ПОСТРОЕНИЕ ДИАГРАММ

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

Рассмотрим кратко основные характеристики типов диаграмм, которые приведены в таблице 5.1. [5]

Таблица 5.1

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

Тип	Характеристика
Гистограмма	подчеркивает характер изменения данных во времени. Имеет 7 подтипов.
Линейчатая	подчеркивает сопоставление значений данных. Имеет 6 подтипов.
График	отражает изменение значений данных за равные промежутки времени. Имеет 7 подтипов.
Круговая	может представлять только один ряд данных и позволяет наглядно оценить вклад каждого элемента ряда в общую сумму. Имеет 6 подтипов.
Точечная	представляет две группы чисел в виде одного ряда точек в прямоугольных координатах. Может отображать взаимосвязь между несколькими рядами данных. По оси X отображаются интервалы, а не конкретные значения параметра. Имеет 5 подтипов.
С областями	показывает сумму значений и вклад отдельных значений в общую сумму. Имеет 6 подтипов.
Кольцевая	аналогично круговой показывает вклад каждого элемента в общую сумму, но в отличие от круговой позволяет обрабатывать несколько рядов данных. Каждое кольцо представляет отдельный ряд данных. Имеет 2 подтипа.
Лепестковая	предоставляет каждому элементу свою ось, исходящую из начала координат. Линиями соединяются значения из одного ряда. Позволяет сравнить значения из нескольких рядов данных. Имеет 3 подтипа.
Поверхностная (поверхность)	должна содержать не менее двух рядов данных. Области с одинаковым значением элементов рядов выделяются одинаковым цветом и узором. Имеет 4 подтипа.
Пузырьковая	является разновидностью точечной диаграммы.
Биржевая	отображает наборы данных из трех значений: самый высокий, самый низкий и курс закрытия. Применяется для демонстрации цен акций на бирже.
Цилиндрическая, коническая и пирамидальная	служат для создания визуальных эффектов восприятия объемных гистограмм и объемных линейчатых диаграмм.

На вкладке Нестандартные представлено 20 типов нестандартных диаграмм.

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

Рассмотрим создание диаграммы на примере 5.1.

Пример 5.1.

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

Рис. 5.1. Исходная таблица

2. Вызовите Мастера диаграмм, щелкнув по пиктограмме на панели инструментов или основное меню Вид – Диаграмма.

3. На экране появится диалоговое окно Мастера диаграмм (шаг 1 из 4) (рис.5.2).

На вкладке Стандартные в списке Тип приведены 14 типов диаграмм, для каждой из которых в списке Вид указаны возможные подтипы. Чтобы просмотреть, как будет выглядеть выбранный подтип диаграмм, нажмите кнопку Просмотр результата (рис.5.2).

Рис.5.2. Мастер диаграмм (шаг 1 из 4)

Выберите из перечисленных типов наиболее подходящий. Пусть это будет гистограмма. В диалоговом окне Мастер диаграмм (шаг 1 из 4) щелкните по кнопке Далее.

На экране появляется диалоговое окно Мастер диаграмм (шаг 2 из 4) (рис.5.3). В центре окна приведено изображение диаграммы, построенной в соответствии с выбранным вами типом диаграммы. Справа в прямоугольнике приведена информация о легенде. Если надо изменить данные, на основании которых построена диаграмма, это можно сделать в поле Диапазон. Переключатели Ряды в позволяют изменить ориентацию данных (выбор из строк или из столбцов).

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

На вкладке Ряд (рис.5.3), кроме диаграммы, предусмотрен список Ряд, в котором отображаются названия обрабатываемых рядов данных (Объем продаж). Ряды можно добавить (кнопка Добавить) или удалить (кнопка Удалить). В поле Имя указывается адрес ячейки, в которой записано имя ряда данных Объем продаж – это В2. В поле Значения указан диапазон ячеек, в котором записаны числовые значения ряда Объем продаж – это ВЗ:В8. Указана полная (с номером рабочего листа) абсолютная (со знаком $) адресация. В поле Подписи оси X указаны адреса ячеек исходной таблицы – диапазон АЗ:А8, из которых берутся значения дней недели для подписей оси X. Щелкните по кнопке Далее.

5. На экране появляется диалоговое окно Мастер диаграмм (шаг 3 из 4) (рис.5.4), с помощью которого возможно добавить к диаграмме различные элементы, совершенствующие ее вид: линии сетки, подписи данных на диаграмме, надписи к осям, заголовок и др. Интерфейс диалогового окна интуитивно понятен – переходя с вкладки на вкладку, делаем свой выбор. Щелкните по кнопке Далее.

 

 

 

Рис.5.3. Вкладки Диапазон данных и ряд мастера диаграмм (шаг 2 из 4)

Рис.5.4. Мастер диаграмм (шаг 3 из 4)

6. В появившемся окне следует указать, на каком листе вы хотите разместить диаграмму (рис.5.5). Обратите внимание, что на всех диалоговых окнах, начиная со второго шага, имеется кнопка Назад, что позволяет вернуться назад и внести необходимые коррективы. Также на всех диалоговых окнах, начиная с первого шага, имеется кнопка Готово, что позволяет вам прервать работу по подготовке диаграммы на любом шаге и получить то, что получится на основе неполной информации.

В результате вы получаете следующую диаграмму (рис.5.6). Если вам после построения вид диаграммы не понравился, то его можно изменить, вызвав панель инструментов Диаграммы (рис.5.7). Войдите в главное меню ВИД, выберите пункт ПАНЕЛИ ИНСТРУМЕНТОВ, затем Диаграммы. Измените вид диаграммы, выбрав другой тип с помощью кнопки Изменить тип диаграммы. Внесите по желанию и другие изменения.

Рис.5.5. Мастер диаграмм (шаг 4 из 4)

Рис.5.6. Диаграмма «Объем продаж»

 

 

 

Рис.5.7. Панель инструментов «Диаграммы»

Пример 5.2. Построить график функции (рис.5.8).

Решение

При построении графика следует обратить внимание на область определения функции. В данном случае функция не существует при обращении знаменателя в ноль. Решим уравнение: 4 x + 8 ≠ 0; 4 x ≠ −8; x ≠ −2. Следовательно, при определении значений аргумента следует помнить, что при x = −2 функция не определена. На рис.5.8 видно, что значение аргумента задано в два этапа, не включая (-2) с шагом 0,2.

 

Рис. 5.8. График функции

Пример 5.3.

Построить график функции (рис.5.9).

Решение

ОДЗ: x²−1≥0 Þ x² ≥1 Þ x=±1 Þ x Î (−¥;−1)È(1;+¥). Определение значения аргумента следует провести в два этапа. Например, от -5 до -1, а затем от 1 до 5,с шагом 0,5.

Пример 5.4.

Построить график функции (рис.5.10).

Решение

При построении этого графика следует использовать функцию ЕСЛИ(). Например, в ячейке А7 (рис.5.10) находится начальное значение аргумента, тогда в ячейку В7 необходимо ввести формулу: =ЕСЛИ(A7<0;1+A7;ЕСЛИ(A7>=1;A7^2;EXP(A7))).

Рис. 5.9. График функции

Рис. 5.10. График функции

Пример 5.5.

Изобразите линию, заданную неявно уравнением: 4y² +5x² –20=0.

Решение

Заметим, что заданная уравнением f(x,y)=0функция описывает кривую линию под названием эллипс. Это можно доказать, если произвести элементарные математические операции:

.

В связи с тем, что линия задана неявно, для ее построения необходимо разрешить заданное уравнение относительно переменной У:

.

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

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

20 − 5 x² ≥ 0 Þ −5 x² ≥ −20 Þ x² ≤ 4 Þ x ≤ ±2 Þ −2 ≤ x ≤ 2 Þ xÎ [−2,2].

Теперь перейдем к построению графика. Для этого в диапазон А2:А42 введем значения аргумента (от -2 до 2 с шагом 0,1). В ячейку В2 введем формулу для вычисления значений функции f₁(x):

=КОРЕНЬ(20-5*$A3^2)/2,

а в С2 для вычисления f₂(x):

=-КОРЕНЬ(20-5*$A3^2)/2.

Далее скопируем эти формулы до В42 и С42 соответственно (рис.5.11). Затем выделим диапазон А2:С42 и, воспользовавшись Мастером диаграмм, построим графики функций f₁(x) и f₂(x) в одной графической области.

Затем выделим диапазон А2:С42 и, воспользовавшись Мастером диаграмм, построим графики функций f₁(x) и f₂(x) в одной графической области (рис. 5.12).

 

 

Рис. 5.11. Создание таблицы в ячейках A2:C42

Рис.5.12. График функции 4y2 +5x2 –20=0

Пример 5.6.

Изобразите линию, заданную неявно: .

Решение

Уравнение описывает линию под названием гипербола. Разрешим его относительно переменной У:

.

Найдем ОДЗ функций f₁(x) и f₂(x): x² − 4 ≥ 0 Þ x Î (− ¥, − 2] и[2,+¥).

Проведенные исследования показывают, что для построения графика необходимо значения аргумента задавать в два этапа, так как в диапазоне от -2 до 2 функция неопределенна (см. Пример 5.2 и 5.3). Задание значений функций f₁(x) и f₂(x) и построение графика выполняется так же, как в Примере 5.5. Результаты представлены на рис. 5.13 и 5.14.

Рис. 5.13. Создание таблицы в ячейках А2:С52

Рис. 5.14. График функции

КОНТРОЛЬНЫЕ ВОПРОСЫ К РАЗДЕЛУ 5

1. Как построить диаграмму?

2. Какие типы диаграмм можно построить в Excel?

3. Как можно вызвать Мастера диаграмм?

4. За сколько шагов можно построить диаграмму с помощью Мастер диаграмм?

5. На каком шаге задается диапазон ячеек для построения диаграммы?

6. На каком шаге можно задается название диаграммы?

7. Что такое легенда в диаграммах?

8. Как выполнить форматирование диаграммы?

БАЗЫ ДАННЫХ

Электронные таблицы Excel позволяют размещать в связанном виде и обрабатывать большие объемы информации. Поэтому их можно рассматривать как базы данных. [1], [6]

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

- ввод данных;

- поиск данных;

- сортировка и фильтрация данных;

- подведение итогов.

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

Основные операции с базой данных доступны через пункт меню ДАННЫЕ (рис.6.1) (предварительно необходимо выделить одну из ячеек таблицы).

Ввод и фильтрация записей

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

1. Выделите ячейку в таблице.

2. Войдите в главное меню ДАННЫЕ и выберите в выпадающем меню пункт Форма. Будет предъявлена форма, структура окна которой приведена на рис.6.1.

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

Для перемещения по полям формы:

- двойной щелчок левой кнопкой мыши по любому полю делает поле активным;

- для перехода к следующему полю нажать клавишу TAB;

- для возврата к предыдущему полю одновременно нажать две клавиши SHIFT и TAB.

Для перемещения по записям формы:

- для перехода к предыдущей/последующей записи служат клавиши стрелка вниз/стрелка вверх;

- для перехода к самой первой записи нажмите две клавиши Ctrl и стрелку вверх;

- для перехода к самой последней записи — Ctrl и стрелку вниз;

- для перехода к предыдущей/последующей записи кнопки на форме Назад и Далее;

- вертикальная полоса прокрутки;

- для перехода к следующей записи клавиша на клавиатуре Enter.

Для добавления или удаления записей:

- для добавления новой записи служит кнопка на форме Добавить (новая запись размещается в конце таблицы);

- для удаления текущей записи служит кнопка на форме Удалить (невозможно восстановить удаленную запись с помощью кнопки Отменить на панели инструментов);

- для копирования данных из поля предыдущей записи нажмите клавиши Ctrl и кавычки (клавиша с буквой Э).

 

Кнопки для работы в режиме формы

Номер текущей ячейки и общее количество записей

Поля текущей записи

Заголовки полей

Имя окна дано по имени ярлычка листа Excel

Рис.6.1. Окно Формы

Поиск записей по критерию позволяет просматривать на форме только записи, удовлетворяющие этому критерию поиска. Для перехода в режим поиска записей по критерию надо щелкнуть по кнопке на форме Критерии и задать в полях условия поиска. Можно пользоваться следующими символами-заменителями:? заменяет один символ, * заменяет любое количество символов. Для записи условий можно пользоваться знаками логических операций <, >, <=, >=, =. После задания критерия поиска перемещение по записям осуществляется с помощью кнопок на форме Назад и Далее.

Рассмотрим поиск записей по критерию на примере 6.1.

Пример 6.1. В базе данных Табель организуйте поиск записей, в которых фамилия оканчивается на ов, приход позже 9:00, а уход позже 17:00.

Решение

1. Щелкните на форме по кнопке Критерии.

2. Введите в поля заданные критерии поиска (рис.6.2).

3. Щелкая по кнопкам формы Назад и Далее, вы будете просматривать только те записи, которые удовлетворяют заданному критерию. Чтобы вернуться к условиям критерия поиска, снова щелкните по кнопке Критерии.

Рис.6.2. Кнопка Критерии

Для упорядочения записей в базе данных служит операция сортировка. Сортировку можно производить по трем критериям одновременно. Выделите любую запись в базе данных Табель, в основном меню ДАННЫЕ выберите Сортировка. Откроется диалоговое окно Сортировка диапазона, в котором в списках Сортировать по, Затем по, В последнюю очередь по задаются названия сортируемых полей. Справа размещены переключатели направления сортировки по возрастанию – от первой буквы алфавита к последней, от более раннего времени/даты к более позднему, от меньшего числа к большему, по убыванию – в обратную сторону. Переключатели Идентифицировать поля по позволяют включать (переключатель в позиции обозначениям столбцов листа) или не включать (переключатель в положении подписям) верхнюю строку базы данных (таблицы) в процессе сортировки. Кнопка Параметры открывает диалоговое окно, позволяющее установить последовательность нестандартной сортировки.

Автофильтр

Команда Автофильтр размещает кнопки раскрывающихся списков в строке названий полей. С помощью этих кнопок можно задавать критерии отбора строк. Если критерии заданы сразу в нескольких полях, то они будут объединены по принципу логического И. Чтобы вызвать автофильтр, в меню ДАННЫЕ выберите Фильтр, затем Автофильтр (рис.6.3).

Рис.6.3. Автофильтр

Рассмотрим применение автофильтра на примере 6.2.

Пример 6.2. Сделайте так, чтобы в базе данных Табель были видны записи со временем ухода, равным 18:00. Затем отмените фильтрацию.

Решение

1. Примените автофильтр к базе данных Табель, выберите в раскрывающемся списке заголовка Уход время 18:00. В результате останется только одна запись.

2. Для отмены фильтрации откройте выпадающий список и выберите Все.

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

Для сложных запросов применяется Расширенный фильтр. Рассмотрим применение этого фильтра на примере 6.3.

Пример 6.3. Отфильтровать содержимое базы данных Табель, отобразив записи полей Фамилия, Уход для сотрудников, фамилии которых заканчиваются на ов, а время их прихода на работу позже 9:00.

Решение

1. Создайте область критерия. Для этого в таблице Табель выделите ячейки А2:В2, по которым будет сформирован критерий фильтрации, и скопируйте их в диапазон A11:В11 (рис.6.4).

2. Запишите в ячейки А12:В12 критерии фильтрации *ов >9:00. Если критерии записаны в одну строку, то они объединяются логическим И (логическое умножение). Если критерии записаны в разных строках, то они объединяются логическим ИЛИ (логическое сложение). В данном случае надо предусмотреть совпадение обоих условий (логическое И), поэтому их надо записать в одной строке.

 

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

3. Создайте диапазон ячеек, где будет отображен результат фильтрации. Для этого в таблице Табель выделите ячейки А2 и С2 и скопируйте их содержание в ячейки А14:В14.

4. Вызовите диалоговое окно Расширенный фильтр. Для этого выделите любую ячейку в таблице Табель, в меню ДАННЫЕ выберите Фильтр и затем Расширенный фильтр.

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

6. Щелкните по кнопке ОК и убедитесь в корректности результата.

7. Чтобы отфильтровать одновременно и тех, чья фамилия оканчивается на ов, и тех, кто приходит позже 9:00, надо записать критерии в разных строчках (т.е. применить к ним операцию логического сложения ИЛИ). Сделайте это и сверьте полученный вами результат с результатом на рис.6.5.

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

 

Рис.6.5. Результат отбора фильтра

Итоги

Для подведения промежуточных итогов в базе данных в меню Данные необходимо выбрать пункт Итоги. [1], [5] Рассмотрим выполнение промежуточных итогов на примере 6.4.

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

Решение

1. Отсортируйте данные в таблице по полю Фирма. Для этого выделите таблицу, в основном меню ДАННЫЕ выберите Сортировка и укажите в списке Сортировать по диалогового окна Сортировка диапазона название поля Фирма (рис.6.6).

Рис.6.6. Сортировка диапазона

2. Вызовите диалоговое окно Промежуточные итоги. Для этого выделите таблицу, в основном меню ДАННЫЕ выберите пункт Итоги... В диалоговом окне Промежуточные итоги впишите данные, как показано на рис.6.7. В поле Операция задайте Сумма, так как нас интересует сумма продаж товара фирмой. Выберите поле Выручка для добавления итогов и щелкните по кнопке ОК. Результат приведен на рис.6.7.

\

Рис.6.7. Промежуточные итоги

3. Если вам надо подсчитать еще и число самих продаж, то повторно вызовите диалоговое окно Промежуточные итоги и в списке Операция укажите Количество, в списке Добавить итоги по поставьте флажок рядом с наименованием Товар, а флажок Заменить текущие итоги снимите. Щелкните по кнопке ОК и убедитесь в наличии результата (рис.6.8).

Рис.6.8. Результат выполнения промежуточных итогов

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

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

Сводные таблицы являются средством трансформации и анализа исходных данных. [1], [6] Сводные таблицы создаются с помощью Мастера сводных таблиц за три шага. Рассмотрим создание сводной таблицы на примере 6.5.

Пример 6.5. Создать на основе таблицы рис.6.9 сводную таблицу.

Решение

1. Выделите любую ячейку таблицы (рис.6.9). В меню ДАННЫЕ выберите Сводная таблица. Появится окно Мастера сводных таблиц и диаграмм (рис.6.12). Установите переключатели в списке или базе данных Excel и сводная таблица. Щелкните на кнопке Далее.

2. В диалоговом окне Мастера сводных таблиц и диаграмм – шаг 2 (рис.6.13) проверьте, правильно ли указан диапазон, в котором размещается исходная таблица. Щелкните по кнопке Далее.

Рис.6.12. Окно мастера сводных таблиц (шаг 1 из 3)

Рис.6.13. Окно мастера сводных таблиц (шаг 2 из 3)

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

Рис.6.14. Окно мастера сводных таблиц (шаг 3 из 3)

4. В диалоговом окне Мастера сводных таблиц и диаграмм (рис.6.15) перетащите кнопки с наименованиями полей исходной таблицы на план-макет новой таблицы. Например, перетащите кнопку Наименование в Столбцы, Артикул – в Строка, Цена в Данные (рис.6.16). Щелкните по кнопке ОК и, вернувшись в окно Мастера сводных таблиц и диаграмм, щелкните по кнопке Готово.

Рис.6.15. План-макет сводной таблицы

5. Проанализируйте полученный вид исходной таблицы (рис.6.16). Стала ли ваша база данных удобнее для работы? Если это не то, что вам нужно, выделите любую ячейку сводной таблицы, войдите в основное меню ДАННЫЕ, выберите Сводная таблица. Попав снова в окно Мастера сводных таблиц и диаграмм, щелкните по кнопке Макет, войдите в макет и измените размещение названий полей, перемещая их левой кнопкой мыши. Подберите тот вид сводной таблицы, который бы вас полностью устроил.

 

Рис.6.16. Сводная таблица

6. Сводная таблица является управляемым пользователем объектом. Дважды щелкните левой кнопкой мыши на любой ячейке, содержащей числовые данные, например на В7. В ответ будет создан новый рабочий лист с частной таблицей, содержащей данные, поясняющие происхождение числа в ячейке В7 (рис.6.17). То есть это часть исходной таблицы.

Рис.6.17. Частная таблица

7. Для работы с данными сводной таблицы, помимо мыши, пункта основного меню ДАННЫЕ и контекстного меню, предусмотрена специальная панель управления Сводные таблицы (рис.6.18). Например, в исходной таблице происходят изменения в значениях некоторых ячеек: введите в ячейку С8 2550 вместо 2500 (это цена кровати с артикулом К443). Обратите внимание, что в сводной таблице содержимое ячейки В7, в которой записана цена кровати с артикулом К443, не изменилось. Чтобы изменения произошли, щелкните по кнопке Обновить данные на панели управления Сводные таблицы (или в контекстном меню выберите команду Обновить данные. Убедитесь, что в ячейке В7 теперь записано 2550.

	Формат отчета		Скрыть детали		Показать детали		Обновить данные		Параметры поля

 

Рис.6.18. Панель управления Сводная таблица

8. Если вы хотите иметь более детализованное представление некоторых параметров в сводной таблице, воспользуйтесь кнопками Показать детали и Скрыть детали на панели инструментов Сводные таблицы. Выделите в сводной таблице ячейку D4, содержащую наименование поля Шкаф. В этом поле приведены цены товара шкаф для трех различных артикулов. Вы хотите иметь для цены каждого артикула отдельный столбец. Щелкните по кнопке Показать детали и убедитесь в результате (рис.6.19). Отмените созданную детализацию таблицы.

Рис.6.19. Детализация сводной таблицы

9. Если вы хотите придать сводной таблице вид из имеющихся шаблонов, щелкните по кнопке Формат отчета (рис.6.20) и выберите в окне Автоформат шаблон оформления.

Рис.6.20. Шаблон автоформата отчета

10. Если вы хотите изменить вид сводной таблицы, то необязательно вызывать Мастера сводных таблиц и диаграмм. Выделите ячейку в сводной таблице, например В7 и щелкните по кнопке Параметры поля (рис.6.18). Появится диалоговое окно Вычисление поля сводной таблицы (рис.6.21). В списке Операция: выберите, что вы хотите иметь в Общем итоге (строка внизу таблицы). Выберите Максимум и щелкните по кнопке ОК. Убедитесь, что в строке Общие итоги выводятся максимальные значения цен на изделия, а в ячейке A3 появилась запись Максимум по полю цена. Сделайте так, чтобы в строке Общий итог выводилась сумма.

Рис.6.21. Выбор операции по полю вычисления сводной таблицы

11. Если вы хотите построить диаграмму, то щелкните на кнопке Мастер диаграмм и выберите тип диаграммы (рис. 6.22).

Рис.6.22. Диаграмма сводной таблицы

КОНТРОЛЬНЫЕ ВОПРОСЫ К РАЗДЕЛУ 6

1. Какие основные задачи выполняются при работе с базами данных?

2. Как создать Форму в Excel?

3. Какие операции можно выполнять в Форме?

4. Как выполнить поиск по заданному критерию?

5. Как выполнить сортировку записей?

6. Какие фильтры существуют в Excel?

7. Как выполнить промежуточные итоги?

8. Каково назначение сводных таблиц?

9. Как создать сводную таблицу?