Управление данными в Excel (Занятие № 1)

Управление данными в Excel (Занятие № 1)

Создание списка клиентов

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

Создание рабочей книги

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

Ввод заголовков столбцов

После открытия окна активной является ячейка А1. С этой ячейки можно начать ввод данных. Введите в ячейку А1 заголовок первого столбца — Название фирмы. Чтобы ввести заголовок второго столбца, следует переместить указатель ячейки в первую ячейку второго столбца. Для этого нужно нажать клавишу [Стрелка вправо]. Введите в ячейки А1 – G1 следующие заголовки: Название фирмы, Код, Контактная персона, Индекс, Город, Улица, Телефакс, Телефон, Скидка (%).

Рисунок 1.1 – Заголовок таблицы

Заголовок первого столбца вряд ли вызовет вопросы. Во втором столбце указываются коды, которые присваиваются клиентам с целью автоматизации некоторых операций. Такой код может иметь какой угодно вид, например: 101, 102, 103, R-O1, RIO1 и т.д, однако он должен быть уникальным. В следующих столбцах указываются: представитель фирмы, ответственный за контакты с клиентами, адрес фирмы, телефакс и телефон. В последнем столбце нашего списка указывается скидка (поскольку с некото­рыми постоянными клиентами у нас сложились особые отноше­ния, мы готовы пойти даже на предоставление им определенной скидки).

Ввод записей

Электронная таблица имеет структуру, которая делает создание списка в Excel максимально простым. Данные можно вводить непосредственно в ячейки таблицы, однако лучше воспользо­ваться специальным диалоговым окном (Формой данных), в котором можно выполнять все операции с записями: ввод, редактирование, поиск. Чтобы открыть диалоговое окно фор­мы данных, необходимо выбрать в меню Данные команду Форма. Введите в соответствующие поля данные о клиентах, завершая ввод каждой записи нажатием кнопкиДоба­вить. Для перехода из одного поля ввода в другое помимо мыши можно использовать клавишу [Tab]. После ввода последней запи­си щелкните на кнопкеЗакрыть.

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

Поиск записей

С помощью диалогового окна Формы данных можно выполнить поиск нужных записей, задавая различные критерии поиска. После нажатия кнопки Критерии на экране появляется неза­полненная форма с заголовками столбцов. Задайте в соответ­ствующем поле ввода критерий и начните поиск нажатием кнопки Далее. Например, чтобы найти записи с данными о клиентах в Одессе, выберите в поле Город значение Одесса. Вскоре на экране появится первая найденная запись, соответствующая заданному критерию. У Вас есть возможность выбора направле­ния поиска, которая реализуется посредством кнопок Далее и Назад. При задании критерия можно использовать символы под­становки. Символ звездочка (*) применяется для обозначения любого количества неизвестных символов, а символ вопроси­тельный знак (?) — для обозначения одного символа.

Рисунок 1.2 – Диалоговое окно формы

Форматирование таблицы

После ввода всех данных следует отформатировать таблицу, а также задать для ячеек I2—I14 процентный стиль отображения данных (для этого необходимо выделить ячейки I2—I4 и акти­визировать кнопку Процентный формат в панели инструментов Форматирование ). В процессе форматирования таблицы может понадобиться увели­чить ширину некоторых столбцов, чтобы данные в них полностью отображались. Эту операцию можно выполнить, используя мышь. Например, если необходимо увеличить ширину первого столбца, переместите указатель мыши в область заголовков, установите его на разделительной линии между столбцами А и В (указатель мыши примет вид двунаправленной стрелки) и выполните двойной щелчок. Программа приведет ширину первого столбца в соответ­ствие с длиной записей. Изменить ширину столбца можно также путем перемещения разделительной линии между заголовками столбцов. Число слева от строки формул показывает среднее количество символов установленного по умолчанию шрифта, необходимое для заполнения активной ячейки. Чтобы не тратить время на прямое форматирование, используйте Автоформаты, например Список 2. Для этого выделите ячейки, подлежащие форматированию (выполните щелчок на первой ячейке и, удерживая нажатой левую кнопку мыши, переместите указатель манипулятора по подлежащим выделению ячейкам таблицы), активизируйте команду Автоформат меню Формат и выберите в списке Список форматов формат Список 2.

Рисунок 1.3 – Таблицы до форматирования

Рисунок 1.4 – Таблицы после форматирования

Сортировка данных

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

Рисунок 1.5 – Окно сортировки данных

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

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

Фильтры

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

Автофильтр

Наиболее простой и удобный способ фильтрации заключается в использованииавтофильтра. В случае применения автофильтра на экране отображаются только те записи, которые соответствуют критериям отбора. Кроме того, как следует из названия, программа автоматически выполняет определенные операции. Чтобы убедиться в справедливости сказанного, попытайтесь применить автофильтр при обработке нашего списка. Для этого воспользуйтесь командой Фильтр/Автофильтр меню Данные. После щелчка на имени этой команды в первой строке рядом с заголовком каждого столбца появляется кнопка со стрелкой. С ее помощью можно открыть список, содержащий все значения полей в столбце. Выберите в списке нужное значение. В результате на экране будут видны только те записи, которые соответствуют заданному критерию отбора. Кроме значений полей, каждый список содержит еще три эле­мента: (Все), (Первые 10...) и (Условие...). Элемент (Все) предна­значен для восстановления отображения на экране всех записей после применения фильтра. Элемент (Первые 10...) обеспечивает автоматическое представление на экране десяти первых записей списка. Это одна из немногих новинок в седьмой версии про­граммы Excel. Если Вы занимаетесь составлением всевозможных рейтингов, главная задача которых состоит в определении лучшей десятки, воспользуйтесь этой новой функцией. Десятка записей может быть составлена на основе данных, представленных в списке в отдельном столбце. Последний элемент — (Условие...) — используется для формиро­вания более сложного критерия отбора, в котором можно приме­нять условные операторы И и ИЛИ. В нашем примере мы присвоили клиентам, специализирующимся на продаже принте­ров, код, начиная с цифры 3. Предположим, мы хотим просмот­реть записи о распространителях принтеров, чтобы разослать им информацию о поступлении новых моделей и о выгодных усло­виях продажи. Чтобы исключить ненужные в данном случае записи, выберите элемент (Условие...) из списка в поле Код. В результате откроется диалоговое окно Пользовательский Авто­фильтр. Укажите в этом окне критерии, которым должны соот­ветствовать записи. Выберите в первом списке оператор сравнения >== и введите в поле ввода значение 3000. Затем установите опцию И, во втором списке выберите оператор срав­нения < и укажите в поле ввода значение 4000. После нажатия кнопки ОК в списке будут представлены только значения, соот­ветствующие заданному критерию. Списки, полученные вследствие фильтрации, а также столбцы, в которых был применен автофильтр, выделяются: номера ото­бранных записей (строк) и стрелка на кнопке окрашиваются в синий цвет.

Рисунок 1.6 – Автофильтр

Рисунок 1.7 – Применение автофильтра

КОНТРОЛЬНЫЕ ПРАКТИЧЕСКИЕ ВОПРОСЫ

1. Создание рабочей книги;

2. Ввод записей;

3. Поиск записей;

4. Форматирование таблиц;

5. Сортировка данных;

6. Создание фильтра, пользовательского автофильтра.

Создание списка товаров

Второй список будет содержать данные о предлагаемых нами товарах. Каждому товару следует присвоить номер, что в даль­нейшем позволит автоматизировать выполнение некоторых операций. Перейдите на второй лист рабочей книги, щелкнув на ярлычке Лист2, и присвойте ему имя Товары. Список, создаваемый на рабочем листе Товары, должен состоять из трех столбцов: Номер, Наименование товара и Цена. Введите указан­ные заголовки в ячейки А1—С1 и присвойте имена ячейкам столбцов А, В, и С: Номер, Товар и Цена. Теперь можно приступить к вводу данных. Желательно, чтобы номера были расположены по возрастанию. В противном случае после ввода данных упорядочите элементы списка по номерам товаров. Вы можете ввести собственные данные или воспользоваться дан­ными, представленными на рисунке. Аббревиатуры после на­званий принтеров означают: Ц— цветной, ЧБ— черно-белый.

Рисунок 2.1 – Таблица списка товаров

Создание списка заказов

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

Начнем с определения структуры списка. В ячейки A 1— L1 вве­дите заголовки столбцов: Месяц, Дата, Номер заказа, Номер товара, Наименование товара, Количество, Цена за ед., Код заказчика, Название фирмы, Сумма заказа, Скидка, Уплачено

Форматирование таблицы

Выделите строку с заголовками столбцов, установите параметры шрифта, выровняйте заголовки столбцов по центру, щелкнув на кнопке По центру в панели инструментов Форматирование, и разрешите перенос слов в пределах одной ячейки (для этого вызовите команду Ячейки меню Формат и в открывшемся диало­говом окне в панели Выравнивание активизируйте опцию Пере­носить по словам). Как и на предыдущих рабочих листах, мы присвоим ячейкам некоторых столбцов имена. Выделите по очереди столбцы В, С, D, Е, F, G, Н, I, J, К, L и введите в поле имени имена Дата, Заказ, Номер2, Товар2, Количество, Цена2, Код2, Фирма2, Сумма, Скидка2 и Оплата. Возможно, Вам покажется чрезмерным количество введенных имен, однако смеем заверить, что в дальнейшем они помогут сэкономить время. Вводить значения мы пока не будем. На данном этапе мы установим параметры и определим, какие значения, в каких полях должны быть указаны.

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

Заданне формул

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

=(В2+ВЗ+В4)/3 [1]

Вы полу­чите среднее арифметическое значение чисел, находящихся в ячейках В2, ВЗ и В4.

Использование функций

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

=ИМЯ ФУНКЦИИ (Аргументы) [2]

Так, для вычисления суммы предназначена функция с вырази­тельным названием СУММ. Если Вы запомнили название функ­ции, то можете смело вводить его в ячейку с клавиатуры. Аргументы функции должны быть указаны после ее названия в круглых скобках (например, СУММ(А1;С4)). Введите открываю­щую круглую скобку и выделите с помощью мыши ячейки, содержимое которых должно использоваться в качестве аргумен­тов. Адрес выделенного диапазона ячеек сразу отобразится в позиции курсора ввода. Завершите задание функции вводом закрывающей скобки и нажатием клавиши [Enter]. Ячейки, содержимое которых используется в качестве аргумен­тов, могут принадлежать как к смежным, так и к несмежным диапазонам. Адрес диапазона смежных ячеек задается путем указания адресов первой и последней ячеек (адреса двух ячеек разделяются двоеточием). Адреса несмежных ячеек разделяются точкой с запятой. Символы разделения вставляются в формулу автоматически при указании диапазона ячеек с помощью мыши.

Мастер функций

С функциями наиболее удобно работать, используя Мастер функ­ций. В этом случае аргументы задаются в диалоговом окне (при вводе функции с клавиатуры можно допустить ошибку, например, пропустить обязательный аргумент). При работе с Мастером функций сначала указывается функция, а затем — ее аргументы. Для запуска Мастера функций предна­значены команда Функция из меню Вставка и комбинация клавиш [Shift+РЗ].

В программе Мастер функций функции разбиты на категории. Полный список категорий находится в поле Категория. К катего­рии Полный алфавитный перечень относятся все доступные в программе функции, а к категории 10 недавно использовавшихся функций, применявшихся последними.

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

Если краткой информации недостаточно, нажмите в диалоговом окне кнопкуСправка (или воспользуйтесь клавишей [FI]). В резуль­тате на экране отобразится страница справочной подсистемы с информацией о выделенной функции.

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

= ЕСЛИ($D2=""; ""; ПРОСМОТР ($D2; Номер; Товар) [3]

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

Столбец F предназначен для указания количества товара, зака­занного клиентом, а столбец G (поле Цена за ед.) — для указания цены единицы товара. Значения цен можно вставить с помощью формулы, аналогичной формуле в ячейке Е2. Формула в ячейке G2 должна иметь такой вид:

= ЕСЛИ($D2=""; ""; ПРОСМОТР ($D2; Номер; Цена) [4]

Целесообразно скопировать формулу из ячейки Е2 в ячейку G2 и затем только изменить имя диапазона. Значение (пробел), пред­ставленное в ячейке как результат применения формулы, убедит Вас в ее правильности. Наверное, Вы уже заметили, что иногда при указании адреса ячейки в ссылке используется символ $. О том, в каких случаях следует применять данный символ, читайте в следующих строках.

Копирование формул

При копировании формул содержащиеся в них ссылки автомати­чески заменяются новыми только в том случае, если они являются относительными. По умолчанию в Excel применяются относи­тельные ссылки. Они используются в формулах для указания адреса ячейки относительно позиции ячейки, содержащей фор­мулу. При изменении позиции формулы относительные ссылки будут содержать адреса других ячеек. Если при перемещении или копировании формулы адреса ячеек не должны изменяться, следует использовать абсолютные ссылки. Абсолютную ссылку можно установить, задав имя или вставив символ $ в адрес ячейки (например $А$1). Можно создать ком­бинацию абсолютной и относительной ссылок, например $А1. В этом случае при копировании формулы в ссылке изменяется только строка, а столбец все время остается тем же. Итак, продолжим выполнять наш пример. В ячейке Н2 следует указать код фирмы-заказчика. Код придется ввести с клавиатуры, поскольку невозможно предвидеть, какой будет очередность за­казов, и задать автоматическое (с помощью одной из формул Excel) заполнение ячеек этого столбца. Автоматически мы запол­ним ячейки столбцов Название фирмы и Скидка. Теперь анализи­руемой ячейкой будет ячейка Н2. Введите в ячейку I2 формулу:

= ЕСЛИ($Н2=""; ""; ПРОСМОТР ($Н2; Код; Фирма) [5]

В столбце Сумма заказа следует указать общую стоимость заказа без учета скидок. Для этого умножьте значения в полях Количество и Цена. С помощью логической функции ЕСЛИ можно задать отмену заполнения ячеек в том случае, если запись не введена, что позволит избежать появления значений ошибки. Таким об­разом, формула в ячейке J2 должна иметь следующий вид:

= ЕСЛИ(F2=""; ""; F2*G2) [6]

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

Величину скидки (столбец Скидка) также можно определять автоматически. Для этого достаточно ввести в ячейку К2 формулу:

= ЕСЛИ($Н2=""; ""; ПРОСМОТР ($Н2; Код; Скидка) [7]

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

= ЕСЛИ($J2=""; ""; J2-J2*K2) [8]

Значительная часть работы по заполнению таблицы уже сделана. Осталось выполнить форматирование: центрировать значения, назначить стили (денежный и процентный), а также заполнить ячейки, в которые должны быть введены значения, голубым цветом, а ячейки, в которых значения будут определены на основе формул, — желтым. Выделите ячейки В2— L2 и выберите в менюПравка команду Заполнить/Вниз. Таким образом мы задали копирование значений ячеек сроки 2 в остальные ячейки. Теперь все готово к вводу записей. Введите несколько записей и проверьте правильность определения значений. После ввода нескольких записей для первых трех месяцев (дней, недель, лет) может возникнуть необходимость подвергнуть дан­ные списка фильтрации, например, чтобы определить, какой клиент пользовался услугами фирмы наиболее часто или насколь­ко популярным был тот или иной товар. Используйте для этого функцию автоматической фильтрации. После вставки автофильт­ра выбор нужных записей не составит труда.

Рисунок 2.2 – Таблица списка заказов

КОНТРОЛЬНЫЕ ПРАКТИЧЕСКИЕ ВОПРОСЫ

1. Форматирование ячеек таблицы;

2. Задание формул;

3. Задание функций;

4. Мастер функций;

5. Функции СУММ, ПРОСМОТР, ЕСЛИ

Создание Бланка заказа

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

Рисунок 3.1 – Таблица списка заказов

Печать документа

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

Таблица, распечатанная в таком виде, вряд ли украсит Ваш архив. Чтобы красиво расположить таблицу на странице, нажмите кноп­ку Страница и в появившемся диалоговом окне выберите раздел Поля. В нем можно установить новые значения полей, задать способ расположения таблицы по вертикали и горизонтали, а также расстояние от краев бумаги до нижнего и верхнего колон­титулов. В разделе Страница можно задать ориентацию страницы, размер бумаги, качество печати и печать номера первой страницы. В разделе Колонтитулы определяется содержимое верхнего и нижнего колонтитулов. Вы можете выбрать один из встроенных колонтитулов. В завершение в разделе Лист укажите, нужно ли печатать сетку, а также определите качество и последовательность печати. Установите все необходимые параметры и закройте диа­логовое окно Параметры страницы нажатием кнопки ОК. Для запуска процесса печати нажмите кнопку Печать. Программа попросит Вас установить в появившемся диалоговом окне неко­торые параметры печати: печатаемые страницы, количество ко­пий. Параметры для принтера задаются в диалоговом окне, которое открывается вследствие нажатия кнопки Свойства. Подождите немного, пока компьютер и принтер справятся с поставленной перед ними задачей, и приготовьтесь принимать поздравления с выполнением первого примера.

КОНТРОЛЬНЫЕ ПРАКТИЧЕСКИЕ ВОПРОСЫ

1. Форматирование ячеек таблицы;

2. Задание формул;

3. Задание функций;

4. Мастер функций;

5. Функции СУММ, ПРОСМОТР, ЕСЛИ

6. Печать документа

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

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

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

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

Поскольку мы создали список заказов для практического приме­нения, его данные подлежат анализу. Поможет нам выполнить анализ Мастер сводных таблиц. Главное отличие сводных таблиц от обычных состоит в интерак­тивности. Сводные таблицы создаются на основе списка или базы данных. Итак, перейдите в пятый рабочий лист и присвойте ему имя, например, Сводная таблица по месяцам. В этом рабочем листе мы разместим нашу сводную таблицу. Затем откройте меню Данные и выберите команду Сводная таблица. Поскольку после имени команды следует троеточие, после ее активизации откроется первое диалоговое окно Мастера сводных таблиц. В этом окне Вам предлагается выбрать один из четырех источников данных для сводной таблицы. Одоб­рите выбор мастера В списке или базе данных Microsoft Excel — и нажмите кнопку Далее.

Первый шаг сделан. Теперь укажите диапазон ячеек выбранного источника данных, которые будут использованы в сводной таблице. Если бы в момент вызова Мастера сводных таблиц указатель ячейки находился внутри списка заказов (в рабочем листе Заказы), то Excel автоматически указал бы выделенный диапазон в поле ввода Диа­пазон. Однако поскольку мы уже перешли в рабочий лист Сводная таблица по месяцам, то теперь, поместив курсор ввода в поле Диапазон, следует возвратиться в рабочий лист Заказы и выделить диапазон A1-L15.

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

Третье диалоговое окно Мастера сводных таблиц является наиболее важным. В нем определяется структура сводной таблицы. В центре этого диалогового окна находится область сведения, которая разде­лена на области полей строк, столбцов, страниц и область данных. Справа от области сведения отображаются все имена полей, исполь­зуемые в списке заказов. Справочная информация в верхней части диалогового окна поможет Вам правильно определить структуру таблицы. Разместить поле данных в подходящей позиции области сведения можно способом drag and drop. Однако перед этим следует хорошо продумать структуру создаваемой таблицы, например, какую информацию нужно проанализировать с ее помощью. В данном примере мы попытаемся проследить динамику сбыта отдельных товаров по месяцам и за весь квартал.

Рисунок 4.1 – Мастер сводных таблиц

Поместите в область страниц поле данных Месяц, а в область строк - поля Наименование товара. Количество и Сумма заказа. Таким образом, в строках после наименования товара у нас будут указаны количество проданных единиц и общая сумма заказа на данный товар в указанном месяце. Теперь следует определить, по какому полю мы будем подсчитывать сумму. Поместите в область данных поле Сумма заказа, вследствие чего в области данных появится кнопка Сумма по полю Сумма заказа. Если Вы хотите задать выполнение другой операции, выберите ее в диалоговом окне, которое открывается при выполнении двойного щелчка на кнопке в области данных.

Рисунок 4.2 – Создание сводных таблиц

Рисунок 4.3 – Результат сводной таблицы

Подготовка данных для диаграмм

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

Рисунок 5.1 –Сводная таблица

Мастер сводной таблицы

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

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

Мастер диаграмм

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

Рисунок 5.1 – Мастер диаграмм

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

Во втором диалоговом окне Мастера диаграмм определите, где расположены ряды данных, на основе которых будут созданы различные фрагменты диаграммы: в строках или в столбцах. В нашем примере ряды данных расположены в столбцах, а названия категорий — в столбце сводной таблицы. Нажмите кнопку Далее для перехода в последнее диалоговое окно Мастера диаграмм.

В третьем диалоговом окне завершите оформление диаграммы: задайте легенду, введите заголовок диаграммы и названия на осях. Перечисленные установки выполняются в полях Название диаграммы, Категорий (X) и Значений (Y). Для нашей диаграммы введите заголовок Объем продаж по месяцам и надписи на осях Наименование товара и Объем продаж (USD). Внесенные изменения сразу отразятся на изображении в поле Образец, и Вы сможете оценить свою работу. Нажмите кнопку Готово и полюбуйтесь созданной диаграммой. Эта диаграмма демонстрирует, какой товар пользовался наибольшей популярно­стью на протяжении, как отдельных месяцев, так и всего периода.

Рисунок 5.2 – Сводная диаграмма

Печать диаграмм

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

6 Требования к оформлению отчета

Текст работы должен быть выполнен на листах формата А4 (210х297 мм по ГОСТ 8327-20) в <