Конспект лекций по Excell. 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12. Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно

• Название: Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно
• Анкор: Конспект лекций по Excell
• Дата: 15.02.2022
• Размер: 3.95 Mb.
• Имя файла: 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12.pdf
• Тип: Конспект лекций; #363174
• страница: 20 из 24

1)
просто выполнить по нему двойной щелчок мышью, или
2)
выделить его и щелкнуть по кнопке Скрыть детали на панели инстру- ментов Сводные таблицы.
В результате скрытия подробных данных, например, по Региону Восток, приведенная выше сводная таблица примет следующий вид:
Отображение скрытых данных выполняется точно также как и их скрытие:
1)
по двойному щелчку мышью на элементе данных сводной таблицы, «со- держание» которого было скрыто, а теперь необходимо отобразить, или
2)
выделив такой элемент, и выполнив щелчок по кнопке
Отобразить де-
тали на панели инструментов Сводные таблицы.
Иногда в сводных таблицах необходимо «пойти еще дальше» и отобразить в них такие подробности, которые изначально при построении их макетов не были предусмотрены. Такой вариант отображения подробностей в сводных таблицах Microsoft Excel рассмотрим на следующем примере.
Пример 8.5.
На основании представленной на рис. 8.9 сводной таблицы отобразить объемы продаж Продавца 11 по каждой дате их совершения.
Для этого необходимо:
1.
Выполнить двойной щелчок по ячейке, содержащей требуемый заголовок –
Продавец 11.
2.
В раскрывшемся в результате окне диалога Показать детали – выбрать на- звание поля исходных данных, содержащего подробности, которые требу- ется отобразить. Для нашего примера – это поле Дата.

189
В результате в область строк исходной сводной таблицы (рис. 8.9) будет добавлен еще один уровень иерархии – поле Дата, в котором будут отобра- жаться подробности только для того поля, для которого они были добавлены, т.е. – Продавец 11.
Для остальных же полей этого уровня иерархии – Продавец – такие под- робности будут скрыты. И для того чтобы их отобразить необходимо, например, выполнить двойной щелчок по имени (заголовку) соответствующего Продавца.
Еще одной особенностью группировки в сводных таблицах Microsoft
Excel является возможность отображения того подмножества исходных дан- ных, на основании которого было вычислено значение той или иной ячейки в
области данных, в том числе и итоговых. Для тог чтобы воспользоваться этой возможностью необходимо просто выполнить двойной щелчок по интересуе- мой ячейке. В результате в текущую рабочую книгу будет добавлен новый ра- бочий лист, содержащий требуемое подмножество данных. Так, например, в результате двойного щелчка по ячейке J10 – общий итог по Филиалу 1 – будет автоматически сформирован следующий рабочий лист:

190
Однако возможности сводных таблиц Microsoft Excel по группировке данных только рассмотренными средствами не ограничиваются.
Так группировку элементов сводных таблиц можно осуществить и по диапа- зонам отображаемых в них данных. Такую возможность группировки инфор- мации рассмотрим на следующем примере.
Пример 8.6.
На основе исходной информации, представленной на рис. 8.1, проанализи- ровать какой по цене товар продается лучше – более дорогой или более дешевый.
Для решения поставленной задачи необходимо:
1.
Построить сводную таблицу, разместив следующим образом поля исход- ных данных в областях:
•
столбцов – Товар,
•
строк – Цена,
•
данных – Кол-во.
В результате будет получена следующая сводная таблица:
Однако с ее помощью однозначно ответить на поставленный вопрос до- вольно трудно.
2.
Поэтому весь проданный товар необходимо разделить на две группы – де- шевый (от 0 до 25 грн.) и дорогой (от 25 до 50 грн.), выполнив такую по- следовательность действий:
а)
Выделить любую ячейку в поле Цена (диапазон A5:A9).
б)
Выполнить команду Группа и структура → Группировать:
•
из контекстного меню, или
•
меню Сводная таблица на панели инструментов Сводные таблицы.
в)
В появившемся при этом окне диалога Группиро-
вание заменить содержание полей редактирования
(которые были автоматически заполнены на осно- вании имеющейся в сводной таблице информации) так, чтобы все цены были разделены на две груп- пы – дешевые и дорогие.
То есть в имеющиеся поля редактирования занести такие значения:
•
начиная с – 0,

191
•
по – 50, и
•
с шагом – 25.
В результате будет получена следующая сводная таблица:
Теперь уже на основании ее содержания можно смело сделать однознач- ный вывод о том, что дорогой товар продается значительно лучше, чем дешевый.
Помимо группировки данных по числовым диапазонам сводные табли- цы Microsoft Excel могут выполнять также группировку и по значениям типа даты и времени, что и будет продемонстрировано в Примере 8.7.
Пример 8.7.
На основе исходной информации, представленной на рис. 8.1, проанали- зировать общие продажи различных товаров по месяцам.
Решение поставленной задачи, как и предыдущей, выполняется в такой же последовательности:
1.
Сформировать сводную таблицу так, чтобы поля исходных данных были размещены следующим образом в пределах областей:
•
столбцов – Товар,
•
строк – Дата,
•
данных – Сумма.
В результате будет получена такая сводная таблица:
Она, как и в предыдущем примере, сразу не отвечает поставленной задаче – в ней суммы продаж отдельных товаров отражаются в разрезе конкретных дат их совершения, а не по месяцам, т.е. «за деревьями леса не видно».

192
2.
Для того чтобы сформированная таким образом сводная таблица отвечала предъявленным к ней требованиям, необходимо выполнить группировку по- ля Дата по месяцам. Алгоритм выполнения такой группировки тот же, что и в предыдущем Примере 8.6. Отличие состоит лишь в структуре окна диалога
Группирование и способах указания значений его элементов управления.
Для нашего примера в списке с шагом необходимо выбрать значение
Месяцы. В результате сводная таблица будет выглядеть следующим образом:
Теперь, с ее помощью, легко можно ответить на вопрос: на какую сумму, и ка- кого товара было продано за конкретный месяц?
Дополнительно, при группировке по временным диапазонам данных мож- но указать несколько уровней иерархии. В данном примере можно выполнить группировку еще и по кварталам. Для этого в окне диалога Группирование в списке с шагом надо выбрать значение Кварталы. В результате двухуровневой группировки по полю типа дата сводная таблица примет следующий вид:
Из нее отчетливо видно, что наибольшие объемы продаж приходятся на
2-й и 3-й кварталы, а наименьшие – на 1-й и 4-й. Однако отсутствие итоговых

193 значений по кварталам, в которых количество месяцев продаж больше одного, несколько затрудняет их восприятие.
8.7.
Общие и промежуточные итоги в сводных таблицах
Во время создания сводных таблиц по умолчанию автоматически добав- ляются общие итоги для их строк и столбцов. Кроме того, если область строк или столбцов содержит более одного поля, то выводятся также промежуточные
итоги для этих полей. Как общие, так и промежуточные итоги, созданные во вре- мя формирования таблицы, затем можно свободно изменять, или удалять вовсе.
Общие итоги отображаются в самой нижней строке и самом правом столбике сводной таблицы. Они управляются при помощи следующих двух флажков в окне диалога Параметры сводной таблицы (рис. 8.6):
•
общая сумма по столбцам, и
•
общая сумма по строкам.
По умолчанию эти флажки выставлены. Для того чтобы общие итоги не отображались необходимо сбросить соответствующий флажок.
Промежуточные итоги управляются при помощи представленного далее окна диалога Вычисление поля сводной таблицы.
Это окно диалога можно активизировать несколькими способами:
1.
Двойным щелчком по заголовку соответствующего поля.
2.
По команде Параметры поля:
•
из контекстного меню, или
•
меню Сводная таблица на панели инструментов Сводные таблицы.
3.
Щелчком по кнопке
Параметры поля на панели инструментов Свод-
ные таблицы.
Его стандартные элементы управления имеют такие назначения:
Поле редактирования Имя – содержит имя того поля, по которому необхо- димо настроить подведение промежуточных итогов. Обычно в этом поле отображается имя поля, которое было выделенным перед активизацией ок- на диалога Вычисление поля сводной таблицы.
Переключатель Итоги – управляет подведением итогов следующим образом:

194
•
автоматические – вычислять промежуточные итоги, используя уста- новленные по умолчанию итоговые функции.
•
другие – вычислять промежуточные итоги, используя итоговые функ- ции из списка.
•
нет – отключить подведение промежуточных итогов.
Список итоговых функций – доступен только при установке переключателя
Итоги в положение другие, и служит для указания одной, или нескольких, итоговых функций следующим образом:
8.8.
Изменение итоговой функции
При создании сводных таблиц в качестве итоговых функций для проме- жуточных и общих итогов Мастер сводных таблиц и диаграмм по умолча- нию использует:
1).
для числовых полей – суммирование,
2).
для текстовых – подсчет числа элементов.
Однако в некоторых реальных задачах суммирование, или подсчет числа элементов, не всегда являются наилучшим способом подведения итогов. Так, например, для сводной таблицы, которая содержит классный журнал с оцен- ками учеников, использование стандартной функции подведения итогов сум- мирования вряд ли принесет какую-либо пользу. Гораздо интереснее, напри- мер, подсчитать средний балл по некоторому предмету.
Обновление сводной таблицы после изменения исходных данных, как было сказано выше, автоматически не выполняется.
Так, в Примере 8.1 после изменения Количества проданного Товара5
Продавцом22 Филиала2, например, с 2 единиц на 4 (рис. 8.1, ячейка D10), со- держание сводной таблицы не изменится.
Для того чтобы такие изменения в исходных данных отобразились в сводной таблице необходимо обновить содержимое кэш-памяти:
1)
выделив одну из ячеек сводной таблицы,
2)
щелкнуть по кнопке
Обновить данные на панели инструментов Свод-
ные таблицы, или выполнив команду Обновить данные из:
а)
контекстного, или
б)
основного меню Данные.
В результате сводная таблица пример следующий вид:

195
ЛЕКЦИЯ № 9.
СРЕДСТВА ЧИСЛЕННОГО АНАЛИЗА ДАННЫХ
9.1.
Надстройка Поиск решения
Надстройки – это программы, которые расширяют функциональные возможности базовых приложений. Они могут выпускаться как производите- лями базовых приложений, так и сторонними разработчиками.
Надстройка Поиск решения предназначена для выполнения сложных вычислений и решений задач оптимизации, которые трудно выполнить вруч- ную. Оно позволяет находить значения в целевой ячейке, изменяя до 200 пере- менных в соответствии с заданными критериями. Полученные результаты мо- гут быть представлены в виде разнообразных отчетов, помещенных в рабочие книги.
Для того чтобы воспользоваться надстройкой Поиск решения ее пред- варительно необходимо установить и загрузить.
Установка надстройки Поиск решения выполняется вместе с установкой (или обновлением) пакета Microsoft Office путем установки со- ответствующего флажка необходимых возможностей.
Для того чтобы надстройка Поиск решения загружалась сразу при за- пуске Excel необходимо:
1.
Выполнить команду Сервис → Надстройки.
2.
В появившемся окне диалога Надстройки, в списке Доступные надстрой-
ки установить флажок против пункта Поиск решения. Если в этом списке не окажется элемента Поиск решения, то необходимо нажать кнопку Об-
зор и в раскрывшемся окне диалога Обзор, аналогичному стандартному окну диалога Открыть, самостоятельно найти файл Solver.xla.
Надстройки Microsoft Office обычно располагаются в папке
\Program Files\Microsoft Office\OFFICE11\Library.
3.
Нажать кнопку OK.

196
Надстройка Поиск решения позволяет решать:
••••
задачи поиска наибольших и наименьших значений,
••••
задачи, которые могут быть к ним сведены, например, решать уравне- ния и системы уравнений.
Ее работу рассмотрим на следующем примере.
Пример 9.1
Спроектировать бак для воды в форме прямоугольного параллелепипеда так, чтобы его объем был 1000 м
3
, а на изготовление пошло минимальное ко- личество материала, т.е. чтобы площадь его поверхности была минимальной.
Для решения поставленной задачи, первое, что необходимо сделать, – это построить математическую модель объекта проектирования.
Процесс построения математической модели заключается в поиске отве- тов на следующие три вопроса:
1.
Для определения, каких величин строится модель, т.е. каковы неизвест-
ные задачи?
2.
В чем состоит цель, для достижения которой из множества всех допусти- мых значений переменных необходимо найти такие, которые оптимизиру- ют поставленную цель?
3.
Каким ограничениям должны удовлетворять переменные?
В нашем примере неизвестными, для определения которых строится мо- дель, являются длина, ширина и высота бака. Назовем их a, b и h, соответст- венно. Из рисунка и «выкройки» бака, приведенных далее, a b h h a b следует, что площадь его поверхности будет равна:
(
)
(
)
(
)
ab
h
b
a
ab
h
b
a
S
+
+
=
+
+
=
2 2
2
, а объем
1000
=
=
abh
V
К этим зависимостям добавим очевидное, но часто пропускаемое усло- вие, согласно которому длина, ширина и высота бака могут принимать только положительные значения, т.е.:
0
,
,
>
h
b
a

197
Тогда поставленная задача проектирования бака сводится к решению за-
дачи оптимизации, которая может быть сформулирована следующим образом:
(
)
(
)
min,
2
→
+
+
ab
h
b
a
(9.1)
,
1000
=
abh
(9.2)
0
,
,
>
h
b
a
(9.3)
Построенная математическая модель состоит из 3-х компонент:
(1)
Целевая функция или критерий оптимизации. Она показывает, в каком смысле решение данной задачи должно быть наилучшим, или оптимальным. При этом возможны три варианта поиска оптимума:
1)
минимизация,
2)
максимизация,
3)
заданного значения.
(2)
Ограничения устанавливают зависимости между переменными задачи.
Они препятствуют безграничному уменьшению (или увеличению) значения целевой функции.
(3)
Граничные условия показывают, в каких пределах могут изменяться зна- чения переменных задачи при оптимальном решении.
Во многих изданиях по оптимизации деление на ограничения и гранич- ные условия не производится – все вместе они трактуются как ограничения.
Решение задачи оптимизации, удовлетворяющее всем ограничениям и
граничным условиям, называется допустимым.
В общем случае таких решений может быть несколько. Для нашего при- мера некоторые из таких решений сведены в представленную далее таблицу.
Таблица 9.1 − Допустимые решения задачи проектирования бака
Решение a
b
h
V
S
1 5
10 20 1000 700 2
5 5
40 1000 850 3
10 10 10 1000 600
Однако среди всех допустимых решений может быть только одно, при
котором целевая функция принимает оптимальное (минимальное, максималь-
ное или вполне конкретное) значение. Такое решение называется оптимальным.
Здравый смысл и имеющиеся знания по математике, наверное, ужу под- сказали, что прямоугольный параллелепипед с минимальной площадью по- верхности – это куб, и в данном случае, с размером стороны 10 м.

198
Проверим это предположение с помощью надстройки Поиск решения.
Для этого необходимо:
1.
На рабочем листе подготовить исходную таблицу следующего содержания:
2.
Выполнить команду Сервис → Поиск решения.
3.
В появившемся окне диалога Поиск решения указать:
1)
В поле редактирования Установить целевую ячейку – адрес ячейки, в которой записана целевая функция – A6. При этом если до вызова окна диалога Поиск решения активной была ячейка с целевой функцией, то ссылка на нее в этом поле появится автоматически.
2)
Переключатель Равной выставить в положение:
•
максимальному значению – если необходимо найти максимальное значение для целевой ячейки,
•
минимальному значению – если необходимо найти минимальное значение для целевой ячейки,
•
значению – требуется найти конкретное значение целевой функции, которое необходимо указать в поле редактирования справа.
Для нашего примера необходимо выбрать – минимальному значению.
3)
В поле редактирования Изменяя ячейки – ссылки на ячейки, содержа- щие независимые переменные, которые в процессе поиска решения мо- гут изменять свое значение. В данном примере – это диапазон A3:C3 – длина, ширина и высота проектируемого бака, соответственно.
Если ячейки с переменными задачи содержат некоторые начальные
значения, и нажать кнопку Предположить, то в поле редактирования
Изменяя
ячейки попадут адреса всех ячеек, влияющих на формулу, ссылка
на которую указана в поле