Информатика и компьютерная техника

Тема 17. Электронные таблицы MS Excel: Сводные таблицы. Таблицы данных.

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

Одним из средств анализа данных, Microsoft Excel предоставляет пользователям, есть так называемые сводные таблицы. Сводная таблица - это таблица, которая используется для быстрого подведения итогов или объединения больших объемов данных. При этом формат (вид) таблицы, а также способ вычислений, задается пользователем. Главный выигрыш от использования сводных таблиц заключается в том, что процесс их создания и внесения в них изменений является автоматизированным, а потому простым и быстрым.

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

Рассмотрим создание сводных таблиц на основе данных, содержащихся в списке. Список или база данных Microsoft Excel - это совокупность данных, определенным образом сгруппированы. Список, как и всякая база данных состоит из записей, или строк. Каждая запись (строка) имеет одинаковую структуру: он разделен на поля, или столбцы (или, еще иначе, - категории), которые имеют одинаковую ширину. То есть, в каждой строки ширина соответствующих полей одинакова. Первая строка списка содержит названия полей.

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

Табл.1.

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

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

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

·                  После запуска на экране появляется диалоговая панель 1-го шага (рис.1). На этом этапе следует указать, на основании каких данных необходимо создать сводную таблицу. Есть четыре возможности:

- Создать таблицу на основе списка или базы данных Microsoft Excel (по умолчанию);

- Создать таблицу используя внешний источник данных;

- Создать таблицу на основе данных, находящихся в нескольких диапазонах консолидации;

- Создать таблицу на основе данных в другой сводной таблице.

Рис. 1.

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

·                  На шаге 2 следует указать диапазон данных для сводной таблицы. На экран выводится диалоговая панель, на которой находится окно Диапазон (рис. 2). Если перед запуском Мастера была выделена ячейка [2], что находится среди диапазона данных, то данные для сводной таблицы будут автоматически выделены, а в окне Диапазон будет указано их адрес. Чтобы указать другой диапазон, необходимо ввести его адрес в окно или просто выделить его на листе.

Рис. 2.

 После выделения данных, для перехода к следующему шагу, следует

 нажать клавишу Далее.

·                  На экран выводится диалоговая панель (рис. 3):

Рис. 3.

После нажатия клавиши Готово при выборе Новый лист, на новом рабочем листе выводится заготовка для сводной таблицы вместе с панелью инструментов Сводные таблицы (рис.4):

Рис.4.

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

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

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

Можно построить другую таблицу, иначе размешивая в заготовке для сводной таблицы названия полей. Так, перетаскивая в область перетащить сюда поля срок кнопку Магазин, в область перетащить сюда поля столбцов  -  кнопку Жанр, а в область Данные - кнопку Количество, получим сводную таблицу:

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

В Excel 2000, при работе с сводными таблицами активизируется панель инструментов - Сводные таблицы . [3]  Она может быть как плавающей, то есть находиться в любом месте экрана, или прикрепленной, как правило, к панелям инструментов внизу или вверху экрана. Если табличный курсор находится внутри сводной таблицы, то плавающая панель имеет вид:

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

 

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

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

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

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

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

Именование ячеек

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

Пусть в ячейке А1 находится цена одного изделия, а в ячейке А2 - закуплена количество изделий. Чтобы в ячейке А3 подсчитать суммарные затраты на закупку, следует в этой ячейке записать формулу = А1 * А2. Если смотреть на саму формулу, то из нее непосредственно нельзя установить, что за величины находятся в ячейках А1 и А2. Если формула не такая простая, а более сложная, то легко запутаться. Поэтому, в ряде случаев, более удобным является присвоение ячейкам имен. Имена ячеек можно выбирать в соответствии с содержанием величин, что в них находятся. При записи формул использование имен предоставляет формулам прозрачности. Так, присвоив в рассмотренном выше случае, ячейке А1 имя Цена, а ячейке А2 - имя Количество, можно записать формулу в ячейке А3 как = Цена * Количество. В этом случае, мы уже по виду формулы можем сказать, какие величины в них.

Чтобы присвоить имя ячейке, следует:

· Выделить нужную ячейку, установив на нее указатель мыши и щелкнув левой клавишей мыши

· В поле Имя ввести новое имя ячейки (рис. 5). На рисунке в поле Имя находится адрес ячейки, где находится табличный курсор, - А1

· Нажать клавишу Enter.

Рис. 5.

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

· Поместить курсор в ячейку, которой необходимо присвоить имя

· Из главного меню выбрать Вставка Þ Имя Þ Присвоить. В результате на экран выводится диалоговое окно Присвоение имени  (рис.6)

· Набрать новое имя в поле Имя, после чего щелкнуть на клавиши Добавить

· Нажать клавишу ОК.

Рис. 6.

Для изменения имени ячейки, сначала необходимо удалить старое имя ячейки. Для этого нужно:

·                  поместить курсор в ячейку, имя которой нужно изменить

·                  из главного меню следует выбрать Вставка Þ Имя Þ Присвоить. В результате на экран выводится диалоговое окно Присвоение имени (рис.6)

·                  щелкнуть мышью на имени, которое требуется заменить, а затем на кнопке Удалить

·                  ввести новое имя в поле Имя, после чего щелкнуть на кнопке Добавить

·                  щелкнуть на кнопке ОК.

Замечания

1. Если формула содержит имя, которое уже удалено, то в ячейке, где находится формула, выводится сообщение об ошибке  # ИМЯ? Это значит, что в формуле есть имена ячеек, которых нет на рабочем листе. Следует откорректировать формулу, исключив из нее такие имена.

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

3. Если на рабочем листе есть большое количество ссылок на имя ячейки, то для быстрой замены можно воспользоваться командой Заменить пункта Правка главного меню.

Вычисления типа "что - если" вручную

 Что случится, если увеличить разницу между себестоимостью и продажной ценой? Что случится, если увеличить зарплату сотрудникам на 1%? Такие, чисто практические задачи, что на каждом шагу возникают в малом и большом бизнесе, можно решить с помощью средств прогнозирования Excel.

 Рассмотрим следующую задачу: Предположим, что мы покупаем для своей фирмы в кредит 10 компьютеров стоимостью 5376 грн. Процентная ставка составляет 12% (годовая), а срок кредита - 24 месяца. Необходимо найти величину ежемесячных выплат.

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

Сформируем рабочий лист:

Здесь в В3 занесена общая стоимость компьютеров (10 штук), в ячейку В4 - процент за кредит, в ячейку В5 - число месяцев погашения кредита. Для получения величины ежемесячных выплат мы пользуемся встроенной в Excel финансовой функцией ППЛАТ.

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

Синтаксис:

ППЛАТ ( ставка ;  чвип ;  сумма ;  мс ;  тип ).

Здесь  ставка  - процентная ставка по ссуду (за один период);

 чвип  - число выплат по ссуде (количество периодов);

 сумма  - общая сумма (размер кредита);

 мс  - будущая сумма, то есть сумма, которую нужно достичь после последней выплаты. Если этот аргумент отсутствует, то она полагается равной нулю;

 тип  - это число 0 или 1, обозначающее, когда должна производиться выплата: 0 - если платить нужно в конце периода; 1 - если оплата производится в начале периода.Отсутствие аргумента эквивалентна тому, что его значение равно нулю.

 Выплаты, рассчитанные по функции ППЛАТ не включают в себя налогов.

После внесения данных и формул, как показано выше, следует установить соответствующие форматы для ячеек. Для ячеек В3, В7 - денежный, а для ячейки В4 - процентный. Для этого нужно поместить табличный курсор (то есть прямоугольник) в соответствующую ячейку и в главном меню выбрать Формат Þ Ячейки .... В результате на экран выводится диалоговое викноФормат ячеек, из которого и необходимо выбрать нужный формат (Денежный или Процентный).

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

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

После того, как в ячейках Е3, Е5 установлено денежный формат, рабочий лист примет вид:

Теперь, если нужно сделать расчет величины ежемесячных выплат при покупке другой количества компьютеров, то достаточно занести нужное число компьютеров в ячейку Е4.Так, поместив в ячейку число 12 иметь:

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

Устранить этот недостаток легко, используя таблицы данных. Есть несколько разновидностей таблиц данных: 1) таблицы данных, содержащие одну входную переменную и одну формулу; 2) таблицы данных, содержащие одну входную переменную и несколько формул; 3) таблицы данных, содержащие два входных переменных и одну формулу.

Таблицы данных с одной входной переменной и одной формуле

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

 Заносим в ячейку В9 текст "Количество компьютеров", в ячейку В10 - число 7, в ячейку В11 - число 8. А дальше, используя автозаполнение (Автозаполнение), чтобы вручную не вводить в каждую ячейку значение, заполняем ячейки В12: В15. Текст имеет чисто декоративное значение. Его можно и не вводить. Для формирования таблицы данных он не играет никакой роли.

 Теперь важный момент . Размер выплат будет содержаться в другом столбце (столбце С), начиная с С10 и заканчивая С15. Так вот, над этими ячейками (в ячейке С9) необходимо разместить формулу, по которой будут рассчитываться элементы столбца. В данном случае, это формула для расчета размера выплат. Она находится в ячейке В7 и содержит функцию ППЛАТ. Простое копирование не приведет к нужному результату, поскольку в формуле автоматически будут заменены адреса ячеек. И в результате, при таком копировании мы увидим

В ячейке С9, куда была скопирована формула с В7, мы видим сообщения об ошибке # ДЕЛ / 0! (Деление на 0). Устранить это можно двумя способами.

Первый - это записать в ячейке В7 вместо формулы = ППЛАТ (В4 / 12; В5; В3) формулу = ППЛАТ ($ В $ 4/12; $ В $ 5; $ В $ 3), заменив тем самым относительные ссылки на абсолютные. Тогда при копировании, те буквы и числа адреса ячейки, у которых стоит знак доллара не меняются. После такого редактирования ячейки В7 можно скопировать ее в С9.

Второй способ заключается в присвоении имен ячейкам, входящих в расчетную формулу в ячейке В7, т.е. ячейкам В3, В4, В5. Присвоим им имена Стоимость, Процент Срок соответственно. После этого, нужно записать формулу в ячейке В7 через эти имена. Это делается автоматически (то есть исправлять не нужно), если с самого начала, перед записью формулы, присвоить имена ячейкам-аргументам. Итак, формула в В7 запишется = ППЛАТ (процент / 12; Срок; Стоимость). После этого формулу копируем из ячейки В7 в ячейку С9.

После копирования, выделяем ячейки В9: С15:

Далее, выбираем из главного меню Данные Þ Таблица подстановки. На экран выводится диалоговое окно Таблица подстановки:

В этом диалоговом окне следует указать, как компьютер должен подставлять данные в формулу: по столбцам или по строкам. Довольно часто ошибаются, указывая вместо "Подставлять значения по столбцам в:" - "Подставлять значения по строкам" и наоборот. В нашем случае, данные (количество компьютеров) содержатся в столбце В.Тому компьютер должен одно за другим подставлять значение из этого столбца. Это значит, что он будет последовательно двигаться вниз по столбцу, -  подставлять значение по строкам  (т.е. брать числа, сначала с В10, затем с В11 и т.д.).

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

Далее, нужно щелкнуть мышью на той ячейке, значение которой необходимо менять. В данном случае это ячейка Е4, которая содержит количество компьютеров, закупаемых.Адрес этой ячейки появится, после щелчка на ней мышью, внутри уменьшенного окна Таблица подстановки. Опять щелкаем мышью на кнопке в правой части окна, чтобы восстановить диалоговое окно в обычного размера. В полноразмерном окне щелкаем на кнопке ОК. На экране появляется таблица, содержащая количество купленных компьютеров и величину выплат. Чтобы таблица данных должна именно так, нужно установить в ячейках С10: С15 денежный формат.

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

 Рассмотрим это на примере табулирования функции. Пусть требуется протабулировать функцию на промежутке [1,0; 2,0] с шагом 0,2.

 Формируем рабочий лист.

Сначала присваиваем имя ячейке А1. Назовем ее х. Занесем в эту ячейку первое значение, то есть 1. Поместим в ячейку В1 формулу: = x ^ 2 * sin (x). В ячейках А2 и А3 запишем соответственно 1 (опять повторяем первый значение = 1) и 1,2 (следующее значение). С помощью автозаполнения заполняем А4: А7 значениями 1,4; ...; 2. После этого выделяем блок ячеек А1: В7 и выбираем из главного меню Данные Þ Таблица подстановки. В диалоговом окне Таблица подстановки указываем, что подставлять значения нужно по строкам и ячейка, в которую нужно подставлять значение есть $ А $ 1. В результате получим следующую таблицу:

Таблицы данных с одной входной переменной и несколькими формулами

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

Рассмотрим в качестве примера табулирования функций и на интервале [0; 1] с шагом 0,2.

Как и в предыдущем случае, присвоим ячейке А1 имя х. В ячейки А2: А7 занесем значения аргумента () автозаполнением. В ячейки В1 и С1 поместим формулы = х ^ 2 i = x ^ 3.

Выделяем всю таблицу (А1: С7) и выбираем из главного меню Данные Þ Таблица подстановки. В диалоговом окне Таблица подстановки нужно, как и в предыдущем случае, указать ячейку A1, или $ A $ 1, или просто x (имя ячейки).

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

Таблицы данных с двумя входными переменными и одной формуле

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

Пусть задано функцию двух переменных. Нужно протабулировать ее в прямоугольнике [2; 3] "[1; 2] изменяя с шагом 0,2, а - с шагом 0,1.

Формируем рабочий лист.

Дадим имена ячейкам: А1 - х, В1 - в. Занесем в них начальные значения и (= 2, = 1). Выберем ячейку, где будет размещаться левый верхний угол таблицы. Пусть это ячейка В3.Записываем в эту ячейку формулу = х * у ^ 2 + КОРЕНЬ (х). Заносим в ячейки С3: Н3 значение с шагом 0,2, а в ячейки В4: В14 значение с шагом 0,1. Выделяем блок В3: Н14 и применив команду Данные  Þ Таблица подстановки, указываем в диалоговом окне появляется в поле подставлять значения по столбцам адрес ячейки х ($ А $ 1), а в поле подставлять значения по срокам адрес ячейки в ($ B $ 1). После нажатия кнопки ОК имеем таблицу:


[1] Внешняя база данных - база данных создана с помощью других программ (не Microsoft Excel), называемые системами управления базами данных (СУБД). К таким программам относятся, в частности, Microsoft Access, Borland dBase, Borland Paradox.

[2] Выделить ячейку - установить на нее курсор и щелкнуть левой клавишей. В результате вокруг ячейки появляется жирный прямоугольник.

[3] В Excel 97 создания сводных таблиц несколько отличается по форме - Мастеру нужно 4 шага вместо 3-х, но идеология их построения является такой же

Змiст

Тема 1. Основные понятия информатики.

Тема 2. Устройство компьютера и принципы его работы.

Тема 3. Периферийные устройства

Тема 4. Программное обеспечение

Тема 5. Операционная система Windows 95/98:

Тема 6. Операционная система Windows 95/98:

Тема 7. Операционная система Windows 95/98: Настройка

Тема 8. Стандартные программы Windows 95/98:

Тема 9. Стандартные программы Windows 95/98:

Тема 10. Редактор MS Word: Основы работы с редактором

Тема 11. Редактор MS Word: Форматирование документов

Тема 12. Редактор MS Word: Работа с таблицами.

Тема 13. Редактор MS Word: Дополнительные возможности

Тема 14. Электронные таблицы MS Excel: Основы работы

Тема 15. Электронные таблицы MS Excel: Работа с формулами Вот формул

Тема 16. Электронные таблицы MS Excel: Построение диаграмм. Географические карты

Тема 17. Электронные таблицы MS Excel: Сводные таблицы. Таблицы данных.

Тема 18. Электронные таблицы MS Excel:

Тема 19. Электронные таблицы MS Excel Средство Поиск развязку


Нові надходження

Всього підручників:

292