Гистограмма и круговая диаграмма

· Выполните построение диаграммы типа “Гистограмма” для двух персон - Зайцева и Медведева (при выделении несмежных областей следует манипулировать мышкой при нажатой клавише Ctrl). Исследуйте все возможные вариации форматов диаграммы. Измените тип диаграммы Зайцева на "график" (после выделения диаграммы: Формат/Тип диаграммы/График).

· Отдельно выполните построение диаграммы типа "круговая диаграмма" для одного Медведева. Исследуйте все возможные вариации форматов диаграммы. Измените в таблице одно из значений, например, в июле Медведев изготовил только 50 рогов. Изменилась ли диаграмма?

· Увеличьте высоту столбца в гистограмме Медведева до уровня 500. Что произошло в таблице и на круговой диаграмме?

Графики функций

Часто на одном и том же графике требуется отобразить несколько графиков, представленных в виде числовых рядов y_i(x_i). Excel допускает такой режим диаграмм. При выборе диаграммы типа «Точечная» на втором шаге «Мастера диаграмм» выбирается вкладка «Ряд». Для каждой новой функции необходимо задать свои диапазоны данных. При нажатии клавиши «Добавить» открывается диалоговая панель, где в окнах «Х» и «У» указываются диапазоны параметра и функции.

Данный механизм приемлем и для построения параметрических функций Y(X), X=X(t); Y=Y(t); Здесь предварительно формируются три связанных массива данных {ti, Xi, Yi}. Ряд {Xi} рассматривается как ряд абсцисс, а {Yi} - как ряд ординат.

· Построить график Y(X): X=sin(t); Y=cos(t). Вы должны получить круг.

Работа с массивами данных

Пусть требуется решить систему линейных уравнений (СЛУ) . Ее решение в математическом виде имеет вид . Таким образом, для получения решения требуется обратная матрица. Excel в своем математическом обеспечении имеет ряд функций оперирования с матрицами, в том числе функции умножения и обращения матриц. Рассмотрим операции с массивами данных на конкретном примере. Требуется получить решение СЛУ

Рис. 2.7. Решение СЛУ

· Добавьте новый лист рабочей книги с названием «СЛУ». Введите исходные данные по форме рис. 2.7. Окрасьте блок коэффициентов (А2:С4), например, желтым цветом, блок правой части (Е2:Е4) светло-зеленым. Выделите блок (А7:С9) для обратной матрицы (имеет такую же размерность, как и матрица коэффициентов) и окрасьте его голубым. Выделите блок (G2:G4) для результирующего вектора Х и окрасьте его розовым цветом.

· Прежде всего необходимо убедиться, что система имеет решение (определитель матрицы А не равен нулю). В ячейке Е6 напечатайте «Определитель», а в Е7 сконструируйте формулу для его определения: вызов мастера функций; выбор функции расчета определителя матрицы МОПРЕД(); ссылка на желтый блок (обязательно мышкой). В результате в Е7 появляется формула =МОПРЕД(A2:C4). В нашем случае определитель матрицы не равен нулю, поэтому СЛУ имеет решение.

Для получения обратной матрицы выделите голубой блок (А7:С9): вызов мастера функций; выбор функции расчета обратной матрицы МОБР(); ссылка на желтый блок (обязательно мышкой), но закончить формулу следует не клавишей Enter, а комбинацией Сtrl+Shift+Enter. Данная комбинация позволяет заполнить результирующими данными блок ячеек. Если вы закончили работу мастера функций клавишей Enter, снова выделите результирующий блок, перейдите в режим редактирования формулы и завершите процесс комбинацией из трех клавиш.

В результате в каждой ячейке голубого блока появляется формула {МОБР(А2:С4)}. Фигурная скобка говорит о том, что была использована функция, возвращающая массив чисел.

Для получения искомого решения необходимо выполнить умножение обратной матрицы на вектор правой части СЛУ. Для этого используется функция МУМНОЖ(…,…), имеющая два параметра – левую и правую матрицы.

· Выполните умножение матриц в розовом блоке результатов {МУМНОЖ(А7:С9;Е2:Е4)}. Первой матрицей является голубой блок, а второй – зеленый. В результате будет получено решение .

Подбор параметра. Поиск решения

Подбор параметра

Microsoft Excel - это мощный инструмент для решения нелинейных уравнений и ряда оптимизационных задач.

Рис. 2.8. Квадратное уравнение

Попытаемся получить решение квадратного уравнения, например f (х)=2 х ²+3 х -600 = 0. Для этого в любой свободной зоне таблицы выберите 5 ячеек (рис. 2.8). Три из них отводятся под коэффициенты. Одна ячейка (например, D4) отводится для искомой переменной (предварительно в нее записывается начальное значение) и одна (например, H4) - для записи функции, значение которой должно быть равным 0. После ввода исходных четырех чисел в Н4 формируется нечто, отличное от нуля.

 

· Выполните Сервис/Подбор параметра. В диалоговом окне установите необходимые параметры (рис. 2.9). Завершите выбор параметров нажатием клавиши [ OK ].

Рис. 2.9. Подбор параметра

Появляется диалоговое окно " Результат подбора параметра " и, если вас там все устраивает, выполните [ OK ], иначе - [ Отмена ]. Программа подобрала такое значение для ячейки D4, что значение в ячейке H4 стало примерно равно заданному, т.е. 0.

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

Поиск Решения

Рассмотрим решение системы двух нелинейных уравнений

Рис. 2.10. Нелинейные уравнения

Данную задачу решаем как задачу нелинейного программирования (часто используемый прием для решения систем нелинейных уравнений): определить переменные х ₁, х ₂, минимизирующие суммарную квадратичную невязку

.

Поскольку мы решаем некоторую техническую задачу, будем считать, что нас интересуют лишь положительные корни.

Для получения решения необходимо выделить область переменных (С2:С3) и область функций (D2:D3). В некоторой ячейке (D4) вычисляется функционал (рис. 2.10).

· На листе «Проба» выполните построение данной таблицы расчетов. Рекомендуем упомянутые области выделить цветом.

· Выполните Сервис/Поиск Решения. В диалоговых окнах укажите: «Установить Целевую Ячейку»- (D4); «Изменяя Ячейки» - С2:С3 (ссылки на блоки указываются мышкой). Флажок цели установите на «минимизацию». В окне ограничений нажмите клавишу «Добавить» для ввода условия неотрицательности переменных. Появляется новая диалоговая панель, где в окне слева указываются адреса переменных, в среднем окне – условие, в правом - значения (рис. 2.11). - все переменные больше нуля. Закончить диалог. Вы получили решение Х=(1,2)^t? Если нет – повторите решение.

Рис. 2.11. Ограничения

· Получите решение системы нелинейных уравнений

Транспортная задача

Типичным примером задачи линейного программирования, которая также решается методом «Поиск решения» является транспортная задача, для которой, в силу ее широкого распространения, разработано достаточно большое число оригинальных алгоритмов. В типовой постановке эта задача формулируется следующим образом. Требуется определить план { x_ij } перевозок однородного груза, минимизирующий суммарную стоимость перевозок

Ф=

при наличии ограничений

(по ресурсам);	(2.1)
(по потреблению);	(2.2)
(условие замкнутости);	(2.3)
(простые ограничения).	(2.4)

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

Рис. 2.12. Транспортная задача

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

Матрица стоимости перевозок получается путем поэлементного умножения матрицы перевозок на матрицу цен перевозок и вектор цен на топливо (блок D15:F17 формируется с помощью матричной формулы {=D9:F11*I9:K11*C9:C11}).

В рассматриваемой задаче вывоз топлива из бассейна может быть ниже его максимальной производительности (условие (2.1) преобразуется в неравенство.). Для учета ограничений (2.1)-(2.2) в блоках D12:F12, G9:G11 формируются соответственно построчная и постолбцовая суммы матрицы перевозок, которые сопоставляются соответственно с блоками D5:F5, В9:В11. В ячейке G12 представлена целевая функция – сумма элементов матрицы D9:F11 стоимости перевозок.

Панель «Поиск решения» показана на рис. 2.13.

Рис. 2.13. Панель «Поиск решения» транспортной задачи

· Выполните представленные расчеты.

· Выполните расчеты по индивидуальному заданию преподавателя.

Рис. 2.14. Таблица подстановки

Таблицы подстановки

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

Таблицы подстановки чаще всего используются для математического и визуального анализов функциональных отношений. Рассмотрим нелинейную функцию f(х)=min(х²-4х+3; x-2). Хотелось бы получить ее график. Для этого необходимо задать ряд параметров и получить соответствующие значения функции. Это относительно простая задача - получение функционального ряда не представляет проблем - необходимо только записать одну формулу и распространить (скопировать) ее на все члены ряда. Однако в практике встречаются более сложные ситуации, когда практически невозможно явно выразить функцию. Здесь весьма полезным представляется аппарат таблиц подстановок. Выполним с его помощью решение поставленной задачи.

Рис. 2.16. Таблица умножения

·На листе «Проба» выделить место для параметра и функции, например, так, как представлено на рис. 2.14. Разработать и реализовать алгоритм расчета функции для заданного параметра в ячейке В4 – формула =МИН(B2:B3)). Составить варьируемый ряд параметра (столбец D). Справа от этого ряда будет сформирован ряд функций, а над ним (в ячейке Е2 записана ссылка на ячейку, где формируется функция (=В4).

·Выделить блок D2:E9. Выполнить Данные/Таблица подстановки. В окне "Ячейка ввода строки" указывается ссылка на параметр х (В1). В результате будет сформирована таблица подстановки (рис. 2.14). Формально Excel выбирает очередную величину из ряда параметров, подставляет ее в отведенную для параметра ячейку, получает значение функции (в В4) и переносит его на соответствующее место в таблице подстановки.

·Представьте полученную функцию в графическом виде (рис. 2.15).

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

С помощью инструментария Excel составим таблицу умножения.

Рассмотрим произведение двух переменных x, y, числовые значения которых хранятся в ячейках B12 и В13 (рис. 2.16). Однако нас интересуют не единичные значения переменных, а ряды данных, согласно которым изменяются переменные. Их попарные сочетания образуют таблицу, строки которой соответствуют первому сомножителю, а столбцы - второму. Запишем упомянутые ряды в столбце А16:А24 и строке В15:F15.На их пересечении, в ячейке А15, записывается формула = B12*B13, согласно которой будет выполнено заполнение блока В16: F15:F24. В результате у нас обозначились левая и верхняя границы таблицы. Теперь всё готово к созданию таблицы умножения.

Выделим всю таблицу, включая и заданные значения параметров. Выполним: Данные/Таблица подстановки. В окне " Ячейка ввода строки " укажем В12, а в окне «Ячейка ввода столбца» - В13, [ OK ]. Получилось то, что нужно? ОК!

Решите задачу. Вы вкладываете в банк под определенный процент (Пр) некоторую сумму (Вклад). Вам очень интересно знать, что вы получите через t=3 года (Выплата = Вклад*(1+Пр)^t)?. Немного помечтайте: А что было бы, если процентная ставка была бы.... Постройте таблицу выплат в зависимости:

1) от процентной ставки;

2) от процентной ставки и вклада.

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

Функции списка

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

·Перейдите на лист «Смета». С целью более эффективной иллюстрации работы со списками, а также для закрепления усвоенного между полями « Наименование» и «Количество» введите дополнительное поле (столбец) «Напряжение, кВ». Удалите строку 7 -«Всего» (суммарные результаты будут получены через функции списка). Увеличьте число строк (записей) таблицы вдвое путем копирования строк 3-6 (в первой строке у вас название таблицы, а во второй – наименование полей). Приведите в порядок номера позиций. Старой группе оборудования назначьте напряжение 110 кВ, а новой 220. Цену нового оборудования увеличьте вдвое (используйте числа, а не формулы).

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

Сортировка

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

·Установите АК внутри списка и выполните сортировку таблицы по наименованию оборудования в алфавитном порядке (команда Данные/Сортировка). При этом внимательно ознакомьтесь с диалоговой панелью «Сортировка диапазона». Выполните сортировку списка по цене, по затратам и цене, по номерам позиций (в возрастающем порядке).

Сортировку по одному полю более быстро позволяют выполнять клавиши ИМ . Для работы с ними достаточно просто установить АК внутри поля, по которому реализуется сортировка, и нажать соответствующую клавишу.

·Выполните предыдущие работы с использованием клавиш «Сортировка».

Формы данных

Формы являются прекрасным средством заполнения таблиц типа «Списки» (см. п. 1.6). Все данные, относящиеся к одной записи, представляются на одной панели, что повышает их визуализацию.

·Установите АК внутри таблицы. Выполните: Данные/Форма. Появилась диалоговая информационная панель (рис. 2.17), где можно дополнять и корректировать записи. Обратите внимание, что здесь присутствуют и вычисляемые поля (Затраты). Пролистайте записи от начала до конца. Измените количество. Как изменились затраты? Создайте новую запись, например, для 5 выключателей 35 кВ по цене 100 тыс.р.. Закончите работу с «Формой». Как изменилась таблица? Если вы не записали в «Форме» номер, введите его.

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

· Перенесите курс доллара в начало листа.

Рис. 2.17. Форма

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

· Введите через форму дополнительную запись о четырех выключателях 110 кВ. В результате таблица приобретает вид рис. 2.18

Рис. 2.18. Дополнительные записи

«Форма» имеет полезную опцию, позволяющую просматривать записи по некоторому критерию. Например, вам нужно просмотреть данные только по выключателям. Для этого:

·Вызовите «Форму» и нажмите кнопку “ Критерий ”(см. рис. 2.17). В поле “Наименование” введите “Выключатель”. Перейдите в режим формы (кнопки " Далее " или «Назад» в зависимости от направления просмотра записей). Пролистайте список. Просмотрите все выключатели напряжением меньше 220 кВ. (в критериях можно определять простейшие условия (<, >); знак равенства можно не ставить).

Фильтр

Представленные в предыдущем разделе функции фильтрации в «Формах» примитивны и не покрывают всех наших потребностей. Excel предоставляет два режима фильтрации данных: «Автофильтр» и «Расширенный фильтр». Прочитайте в теоретической части соответствующий раздел (п.1.6.2).

Автофильтр. Фильтрация осуществляется непосредственно в таблице и позволяет оставить видимыми только те записи, которые формируются заданием встроенных критериев (команда Данные/Фильтр/Автофильтр).

· Вызовите Автофильтр. Нажмите кнопку фильтра в поле "Наименование". Оставьте в таблице только выключатели. Выберите функцию "Условие". Составьте условие и выполните фильтрацию, чтобы в таблице остались все записи, кроме выключателей. Восстановите список. Оставьте в таблице оборудование напряжением 35£U£220. Оставьте в таблице оборудование с наименованием, начинающимся на букву Р, на букву Р и дальше по алфавиту. Восстановите таблицу в полном объеме. Отключите режим фильтрации (Данные/Фильтр/Автофильтр).

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

· Ниже таблицы скопируйте строку с названиями полей. В поле "Наименование" укажите «Выключатель», а в поле «Напряжение» - «<220». Установите АК внутри таблицы-списка и выполните: Данные/Фильтр/Расширенный фильтр. В диалоге укажите мышкой область критериев. Придумайте, запишите и реализуйте свой критерий фильтрации.

Вычисление итогов

Накапливаемые таблицы содержат повторяемые по отдельным позициям данные, причем разница в данных может быть столь несущественна, что даже не отражена в таблице (например, дата поступления оборудования). Для успешного анализа, как правило, требуется объединение данных по некоторому критерию. Например, необходимо знать распределение затрат по видам оборудования и суммарные затраты. Эту задачу решает функция списков «Вычисление итогов».

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

· Выполните упомянутую сортировку данных. Выполните группировку списка по наименованию: Установите курсор в поле таблицы и выполните: Данные/Итоги. Ознакомьтесь с панелью «Промежуточные итоги».

· В поле " При каждом изменениив " установите "Наименование". В поле " Операция " выберите сумму (Какие еще функции предлагает Excel?). В поле " Добавить итоги по " установите (флажок) категории «Количество», «Затраты, тыс.руб.» и «Затраты, тыс.$». Нажмите [ ОК ]. Что изменилось в таблице? Попытайтесь экспериментально определить назначение кнопок «+», «-» в поле слева.

Для следующего иерархического уровня итогов (по напряжению) предыдущая операция повторяется. Однако на данном этапе убирается флажок «" Заменить текущие итоги".

Перейдите на лист «Рога», где хранится таблица производства рогов на фирме "Рога и копыта". При взгляде на великое множество цифр невольно возникает вопрос: А нужно ли столько? Нельзя ли ограничиться только поквартальными показателями? Попробуем сделать это. Однако таблица в существующем виде не удовлетворяет требованиям списка (базы данных). Этот дефект таблицы нетрудно исправить.

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

· Дополнительно в итогах получите среднемесячное в каждом квартале производство рогов для каждого участника (для того чтобы итоги по обоим критериям были представлены в таблице, перед нажатием клавиши [OK] следует отменить опцию "Заменить текущие итоги").

Консолидация данных

Пусть требуется составить таблицу суммарных доходов наших студентов, которые работают не только на фирме "Рога и копыта", но и в артели "Эх, прокачу".

· Добавьте новый лист и назовите его «Союз». С листа «Рога» скопируйте на лист «Союз» транспонированную таблицу производства рогов (блок A2:F14) и назовите ее «Доход на фирме "Рога и копыта"» (в ячейке А1). Повторите копирование через столбец направо (блок H2:M14) и назовите таблицу «Доход на фирме "Эх, прокачу!"» (Н1). Постарайтесь, чтобы их доходы различались (для скорости заполнения сделайте помесячные доходы на новой фирме одинаковыми).

· На свободном месте (например, начиная с ячейки А22) скопируйте столбцы «Квартал» и «Месяц» (блок A22:F34)). Эта операция необходима для обозначения названий строк. Если бы мы ограничились только месяцем, то предварительное копирование было бы излишним.

· Активизируйте левую верхнюю ячейку (С22) итоговой таблицы. Выполним Данные/Консолидация. На диалоговой панели выбирите функцию СУММ. В поле “ Ссылка ” идентифицируйте (мышкой) первую исходную область (С2:F14) консолидируемых данных (вместе с именами полей). Нажмите кнопку “ Добавить ”. Идентифицируйте вторую область (J2:M14). Установите флажок " В верхней строке ". Посмотрите, что получилось.

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

2.8.6. Сводная таблица

Группировку данных по двум или даже трем параметрам позволяет выполнить "Сводная таблица" (Данные/ Сводная таблица). Покажем специфику ее использования на рассмотренном ранее примере производства на фабрике "Рога и копыта". Расширим нашу транспонированную таблицу, где столбцами являются поля "Квартал", "Месяц", "Лисицин" и др., добавлением дополнительного столбца - "Товар", где совершенно произвольно вы можете записать либо рога, либо копыта. В поле "Товар" поочередно введите "Рога» и «Копыта" (копированием пар). Укороченный вариант таблицы приведен на рис. 2.19.

Рис. 2.19. Производство рогов и копыт

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

Для решения поставленной задачи:

· Поставьте курсор внутри исходной таблицы. Выполните: Данные/Сводная таблица. В результате вызывается «Мастер сводных таблиц», который позволяет получить требуемое решение.

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

· Далее выполняется конструирование структуры сводной таблицы (рис. 2.20). В поле "Строка" переносятся из списка название поля, которое характеризуют построчное деление. Пусть это будет "Товар". В "Столбец" переносится название поля, значения которого будут формировать столбцы. В нашем случае это поле "Квартал"(рис. 2.20).

В поле "Данные" переносятся названия тех полей (не обязательно всех оставшихся), для которых выполняется операция группировки. Пусть это будут данные поля "Зайцев". В результате формируется сводная таблица (рис. 2.21).

Рис. 2.20. Конструктор сводных таблиц

·

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

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

·Замените данные Зайцева на данные Медведева (перетащите поле «Сумма по полю Зайцев» в список полей сводной таблицы и перенесите из списка в поле данных Медведева).

· Включите в поле данных остальные категории.

· Просмотрите функции кнопок на панели «Сводная таблица» (если на экране ее нет – выполните: Вид/Панели инструментов/Сводные таблицы).

·Измените функцию суммы на максимум. Для этого выделите поля, где происходит суммирование (формируются числа), в дополнительном ИМ раскройте панель «Сводная таблица»". Выберите опцию «Параметры поля» (проще это было бы сделать через контекстное меню) и далее функцию "Максимум". Что еще позволяет сделать последняя диалоговая панель?

Внешняя среда