ПРАКТИКУМ «Применение средств «Excel»

ПРАКТИКУМ «Применение средств «Excel»

Часть1. Введение в Excel

Рис. 8

 

Таблицы являются простым способом структурирования и хранения данных. Таблица есть набор строк и столбцов. Строки и столбцы таблицы обычно нумеруются буквами и цифрами (стиль A1, стиль R1C1):

 

 

Ячейка A2 в стиле А1 есть R2C1 в стиле R 1 C1 (R ow – ряд, C olumn - колонка). Изменение текущего стиля выполняется через пункт меню С е рвис à П а раметры...,далееàвкладка Общие àфлаг Стиль ссылок R 1 C1 (рис. 9). В этом же окне можно изменить другие основные параметры Excel.

 

Рис. 9

Ячейки таблицы организованы в рабочие листы (рис. 8). При ссылке на другой лист указывают имя листа, например Лист2!B1 – это ссылка на ячейку B1 листа 2.

Ячейка может содержать данные одного из следующих типов: а) число, дату/время; б) текстовую строку; в) формулу.

Формулы записываются со знаком = вначале, при этом формулы могут содержать имена (адреса, ссылки) других ячеек. Попробуйте самостоятельно заполнить ячейки А1-А5 (рис. 10):

 

 

Рис. 10

 

Пересчёт всех формул при изменении ячеек – одно из основных достоинств электронной таблицы. Измените любое число в ячейках А2-А4 – и сумма будет моментально пересчитана.

Аналогичный результат можно получить значительно проще, если использовать кнопку å на Панели инструментов – в ячейку А5 будет вставлена функция суммирования СУММ():

1. Выберите ячейку А5, т.е. ячейку под суммируемыми ячейками.

2. Нажмите кнопку å, а затем Enter на клавиатуре.

Изменить или просмотреть формулу (содержимое) в ячейке можно двумя способами: а) в строке формул (содержания), предварительно выбрав нужную ячейку; б) в самой ячейке, щёлкнув по ней два раза мышью.

Выполнять операции с ячейками и изменять её свойства удобно через контекстное меню (рис. 11), которое выпадает при щелчке правой кнопкой мыши по ячейке или группе выделенных ячеек. Чаще пользуйтесь этим способом доступа к свойствам ячейки.

 

Рис. 11

 

Различают относительные и абсолютные ссылки (имена, адреса) ячеек. Абсолютные ссылки записываются со знаком $:

А1 – относительная ссылка;

$A$1 – абсолютная ссылка (и на строку и на столбец);

$A1 – на столбец абсолютная, на строку – относительная;

A$1 – на столбец относительная, на строку – абсолютная.

 

Функциональное различие типов ссылок проявляется лишь при копировании ячеек: относительные ссылки корректируются при копировании, а абсолютные – нет!

 

Попробуйте самостоятельно. В качестве примера скопируйте формулу из ячейки А5 в В5. Копирование можно выполнить двумя способами:

· через буфер обмена: скопируйте А5 в буфер (Ctrl-C), затем вставьте в В5 (Ctrl-V);

· перетаскиванием: зацепитесь за правый нижний угол ячейки А5 и перетащите формулу в В5, как на рисунках ниже:

 

Поскольку в ячейке А5 формула с относительными адресами, то в ячейку В5 скопируется формула =СУММ(В2:В5), т.е. относительные адреса корректируются.

Если в ячейку А5 записать формулу с абсолютными адресами =СУММ($A$2:$A$4), то после копирования в В5 будет та же формула, что и в А5, т.е. адреса не изменятся.

В стиле R 1 C1 относительные ссылки записываются с указанием смещения от текущей ячейки. Например, для ячейки R5C2 сумма 3-х верхних ячеек записывается так:

 

=СУММ(R[-3]C:R[-1]C).

 

Рис. 12

По умолчанию копируется всё: и формула и значение. Если требуется скопировать не формулу, а только значение, то копируйте через буфер обмена, вместо команды Вставка используйте команду из меню П равка à Сп е циальная вставка…, а в окне (рис. 12) выберите радиокнопку з начения.

 

Ячейки можно именовать, т.е. присваивать абсолютные имена. Используйте пункт меню Вст а вка à И мя à П рисвоить…. В окне Присвоение имени выделенной ячейке Лист1!$A$4 присваивается имя Сумма:

 

Полезным способом быстрого заполнения последовательных ячеек значениями некоторого ряда в Excel является автозаполнение по образцу:

1. В две последовательные ячейки следует записать первые два значения числового ряда – образец.

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

 

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

 

 

Для текстовых рядов значения списка должны быть подготовлены заранее. Просмотреть имеющиеся и создать новые текстовые ряды можно через пункт меню Сервис à Параметры… àвкладка Списки.

 

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

Попробуйте самостоятельно. Для закрепления строк следует выделить первую прокручиваемую строку, как на рисунке ниже, и выбрать пункт меню О кноà З акрепить области. Здесь строки 1 и 2 будут закреплены при вертикальной прокрутке.

 

 

Аналогично, выделением столбца, закрепляются столбцы. Для одновременного закрепления и столбца и строки, следует выделить первую прокручиваемую ячейку (на рисунке выше B3) и выбрать пункт меню О кноà З акрепить области. Снять закрепления областей можно через пункт меню О кноà Снять з акрепление областей.

 

Вставка новой строки (столбца) в таблицу – пункт меню Вст а вка à Стро к и. (Вст а вка à Стол б цы) Удаление ненужной выделенной строки или столбца –пункт меню П равка à У далить….

 

Форматирование и оформление данных

Форматирование ячейки – установка правил ввода и отображения данных. Форматировать ячейку, выделенную группу ячеек, группу строк, столбцов или целый лист можно через меню Фор м ат. Но проще пользоваться командой Формат ячеек… выпадающего контекстного меню (рис. 11). В любом случае откроется окно Формат ячеек, в котором можно настроить варианты отображения содержимого ячейки (рис. 13).

Excel пытается "угадать" формат отображения вводимых данных. Например, если вы введёте дату 12-02-2002, то по умолчанию установится формат Дата, если – проценты 77%, то установится Процентный формат и т.д. Если формат не удается установить, то устанавливается Общий формат. Для большинства числовых форматов можно выбрать тип и число десятичных знаков дробной части.

 

Рис. 13

 

Рис. 14

 

На рис. 14 показано число 1500,5 в различных форматах отображения, имеющихся в окне Формат ячеек (рис. 13). Самостоятельно поэкспериментируйте с форматами отображения данных в ячейке. Часть возможностей по форматированию данных выведены в виде кнопок на Панель управления.

Для оформления активно используйте другие закладки окна Формат ячеек (рис. 13), в частности вкладку Выравнивание (рис. 15). В ней вы найдете часто необходимые возможности: перенос по словам, объединение ячеек и наклонная ориентация текста.

 

Рис. 15

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

 

 

Как видно, в ячейках А3 и В3 используется перенос по словам, пары ячеек D2-E2 и F2-F3 объединены, а в ячейках С3 и F2-F3 используется наклонная ориентация текста. Для наклона текста следует переместить красный ромбик в поле Надпись на нужный угол.

 

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

 

 

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

 

На вкладке Защита (рис. 15) Вы можете запретить/разрешить изменение конкретных ячеек с помощью флага За щ ищаемая ячейка. После установки/снятия флагов защит нужных ячеек следует выполнить защиту листа: пункт меню Сервис à За щ ита à Защитить л ист…. По умолчанию на листе все флажки За щ ищаемая ячейка включены, т.е. выполнение пункта меню Сервис à За щ ита à Защитить л ист… приводит к защите всех ячеек.

 

 

Часть 2. Функции

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

Функции можно использовать в формулах. Например, |cos(90)| в ячейке Excel запишется так: =ABS(COS(90)). Как видно, аргументом функции ABS (модуль числа) является функция COS. Аргументы функций заключаются в скобки. Функция может иметь несколько аргументов – при записи формулы в ячейке аргументы разделяются точкой с запятой (;).

 

Перечень основных функций приведен в таблице:

 

Функции по группам	Результат	Назначение
Дата и время		Всего 14
=СЕГОДНЯ()	31.03.02	Читает текущую дату из системных часов ПК
=ДАТА(2002;5;12)	12.05.02	Возвращает дату в числовом формате
=ВРЕМЯ(18;32;15)	6:32 PM	Возвращает время в числовом формате
Математические		Всего 50
=ABS(-5)		Модуль числа
=SIN(90)	0,893997	Синус числа (в радианах)
=РАДИАНЫ(170)	2,96706	Преобразует радианы в градусы
=ГРАДУСЫ(30)	1718,873	Преобразует градусы в радианы
=EXP(5)	148,4132	Экспонента (e = 2.71828182845904)
=LN(7)	1,94591	Натуральный логарифм
=LOG(7;5)	1,209062	Логарифм числа по заданному основанию
=КОРЕНЬ(256)		Квадратный корень
=ФАКТР(7)		Факториал
=ОКРУГЛ(45,827;2)	45,83	Округляет до заданного числа десятичных разрядов
=ПИ()	3,14159265358979	Число Пи, округленное до 15 разрядов
=РИМСКОЕ(454)	CDLIV	Преобразует число в римский текстовый формат
		8 функций работы с матрицами и т.д.
СУММЕСЛИ(диапазон, условие)		Суммирование ячеек диапазона по условию
Статистические		80 функций для статистического анализа диапазонов данных
СЧЕТЕСЛИ(диапазон, условие)		Подсчет числа ячеек диапазона по условию
Финансовые		53 функции для типичных финансовых расчетов
Инженерные		Устанавливаются дополнительно через пункт меню СервисàНадстройкиàПакет анализа
Ссылки и массивы		Обработка индексов и массивов. Всего 17
Работа с базой данных		Извлечение и обработка записей в базах данных. Всего 12
Текстовые		Всего 23
=ДЛСТР("Бабочка")		Длина текста
=ЗАМЕНИТЬ("Лампочка";3;2;"ст")	Ласточка	Замена символов внутри текста
=ПРАВСИМВ("Лампочка";5)	почка	Правые символы слова
=НАЙТИ("ана";"Банан и ананас")		Ищет текст и возвращает найденную позицию
=ПОДСТАВИТЬ("ананас";"ан";"с")	ссас	Заменяет один текст другим
=СЦЕПИТЬ("Само";"лет")	Самолет	Сцепляет слова
Логические		Всего 6
=ИЛИ(ИСТИНА;ЛОЖЬ;ЛОЖЬ)	ИСТИНА	Логические ИЛИ
=И(ИСТИНА;ЛОЖЬ)	ЛОЖЬ	Логические И
=НЕ(ИСТИНА)	ЛОЖЬ	Логическое НЕ
=ЕСЛИ(F1>5;10;5)	10 (здесь F1=7) 5 (здесь F1=3)	Проверяет условие и возвращает одно из двух значений
Проверка свойств и значений		Всего 15
=ЕНЕТЕКСТ(155)	ИСТИНА	Если не текст, возвращает логическое значение ИСТИНА
=ЕЧИСЛО("Текст")	ЛОЖЬ	Если число, возвращает логическое значение ИСТИНА

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

 

 

Пример 1. Проделайте самостоятельно. В колонках A, B, C даны данные по суммам отгрузки товара, по договорным и фактическим датам оплаты. Необходимо выявить суммы, оплаченные в срок, и просроченные суммы. Также требуется установить количество просроченных сумм (см. ниже):

 

 

1. Начните с записи формулы =ЕСЛИ(C2<=B2;A2;"-") в ячейку D2. Встаньте на ячейку D2 и воспользуйтесь мастером функций, нажав кнопку fx.

2. На первом шаге в окне мастера функций выберите категорию Логические, а в ней функцию ЕСЛИ и нажмите ОК.

 

 

3. В следующем окне мастера заполните поля мастера, как показано ниже… после нажатия ОК получим искомую формулу – проверьте:

 

 

4. Размножьте формулу из ячейки D2 в ячейки D3-D6, потянув за угол ячейки D2.

5. Аналогично запишите в ячейку E2 формулу =ЕСЛИ(C2>B2;A2;"-") и размножьте ее в ячейки E3-E6.

6. С помощью мастера функций в ячейку E9 запишите статистическую функцию =СЧЁТЕСЛИ(E2:E6;">0"), которая подсчитывает количество положительных чисел в колонке E2-E6. Задача решена.

Пример 2. Проделайте самостоятельно. Дан период дат, а также даты и соответствующие суммы. Требуется распределить по разным колонкам суммы попадающие и не попадающие в период дат так:

 

 

В решении используются логические функции И и НЕ:

1. Формулу =ЕСЛИ(И(A4>=$C$1;A4<=$D$1);B4;"-") записать в ячейку C4. Далее размножить ее на ячейки C5-C7.

2. Ту же формулу =ЕСЛИ(НЕ(И(A4>=$C$1;A4<=$D$1));B4;"-"), но с логической операцией НЕ, записать в ячейку D4. Далее размножить ее на ячейки D5-D7.

 

Часть 6. Сводные таблицы

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

 

 

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

 

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

Подготовьте исходные данные для построения сводной таблицы – группу ячеек таблицы Excel – как на рис. 25. Здесь приведены данные об объемах продаж телевизоров 3-х производителей в 3-х регионах за три месяца 2009 года. Важно отметить, что все столбцы имеют заголовки – наименования, которые будут активно использоваться в сводной таблице. Кроме того, данные в столбцах представлены в виде повторяющихся списков.

 

Рис. 25

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

 

Сводная таблица может иметь вид, как на рис. 26 слева. Как видно, в ней столбец Объемы продаж просуммирован по столбцам Регион и Товар по периодам/месяцам. Поля Период, Регион и Товар становятся измерениями куба и представляют собой раскрывающиеся списки, позволяют выбирать и просматривать срезы данных. Поле Объемы продаж составляет данные куба.

Справа от сводной таблицы (рис. 26) выведен список полей. Под сводной таблицей здесь выведена Панель инструментов Сводные таблицы.

Теперь создадим сводную таблицу из уже подготовленных исходных данных (рис. 25), используя Мастер сводных таблиц.

 

Рис. 26

 

Проделайте следующие действия:

1. Выделите диапазон ячеек (исходные данные) для сводной таблицы – A1:D28. Выберите пункт меню Д анныеàСвод н ая таблица…. Будет запущен Мастер.

2. В первом окне запрашиваются исходные данные для сводной таблицы – выберите пункт " в списке или базе данных Microsoft Office Excel " и нажмите кнопку Далее>.

3. Во втором окне можно скорректировать диапазон ячеек с исходными данными. Можно указать данные из другого файла. Нажмите Далее>.

4. В следующем окне (выберите опцию " новый лист ") непосредственно создается сводная таблица (рис. 27) – на макет просто перетаскиваются названия полей из списка полей сводной таблицы. Поочередно перетащите каждое название на макет, как показано на рис. 27 – сводная таблица готова на отдельном рабочем листе (рис. 26).

 

Рис. 27

 

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

 

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

Если вы случайно закрыли Панель инструментов Сводные таблицы, то для её отображения щёлкните правой кнопкой мыши по Панели инструментов Excel и в раскрывшемся списке выберите нужную панель.

 

Другим удобным инструментом компоновки макета сводной таблицы является сам Мастер. Есть два способа вызова Мастера:

· кнопкой Сводная таблица на Панели инструментов Сводные таблицы;

· правым щелчком мыши по сводной таблице вызвать меню и в нем пункт Мастер.

В обоих случаях будет вызвано окно Мастера – рис. 28. Здесь по кнопке Ма к ет будет вызвано окно компоновки макета сводной таблицы – рис. 29. Этот способ представляется более удобным для реорганизации сложных сводных таблиц с множеством полей.

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

 

Рис. 28

 

Рис. 29

 

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

1. Щелкните по сводной таблице для ее выделения. На панели инструментов Сводные таблицы (рис. 26) нажмите кнопку Мастер диаграмм. Будет построена гистограмма с накоплением.

2. Измените тип гистограммы – еще раз нажмите кнопку Мастер диаграмм в появившемся окне выберите вид гистограмму – трехмерная гистограмма и нажмите Далее.

3. В следующем окне Мастера диаграмм нажмите Далее.

4. В следующем, последнем окне укажите, куда вы хотите поместить диаграмму – выберите радиокнопку отдельном. Нажмите кнопку Готово. Гистограмма будет преобразована в трехмерный вид, как на рис. 30. Списки на диаграмме позволяют выбирать и просматривать срезы данных так же, как на сводной таблице.

 

Рис. 30

 

 

Часть 9. Поиск решения

Для численного решения уравнений со многими неизвестными и ограничениями в Excel включен инструмент Поиск решения.

 

По умолчанию инструмент не установлен и его следует установить: вставьте дистрибутивный CD-диск и выберите в списке надстроек С е рвисàНадстро й ки… соответствующий флажок.

 

Если целевая функция и ограничения линейны, то решение состоит в нахождении множества чисел (х₁, х₂, … х_n), минимизирующих (максимизирующих) линейную целевую функцию f(х₁, х₂, … х_n)= c₁х₁+c₂х₂+… +c_nх_n при m<n линейных ограничениях-равенствах а_i1х₁+а_i2х₂+… +а_inх_n (где i=1,2, … m) и n линейных ограничениях-неравенствах х_k>=0 (где k=1, 2, … n). Инструмент Поиск решения обеспечивает максимум 200 изменяемых ячеек х_i при поиске решения (n_мах=200).

В качестве содержательного примера рассмотрим задачу оптимизации туристических групп (экскурсионных пакетов). Российская туристическая фирма ежедневно отправляет в три отеля Анталии, Кемера и Мармариса (Турция) соответственно 30, 20 и 16 человек. Экскурсионная программа каждой группы состоит из рафтинга (спуск по горной реке на плоту), яхт-тура вдоль побережья и путешествия джип-сафари в турецкую глубинку. Стоимость экскурсий с трансфером на человека для отелей разных городов следующая:

 

	Рафтинг	Яхт-тур	Джип-сафари
Анталия
Кемер
Мармарис

 

При этом существуют ограничения на количество человек в экскурсии: рафтинг – 25 чел., яхт-тур – 20 чел., джип-сафари – 30 чел. От каждого отеля на каждую экскурсию должно быть послано не менее 5 чел.

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

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

 

	Рафтинг	Яхт-тур	Джип-сафари
Анталия	х1	х2	х3
Кемер	х4	х5	х6
Мармарис	х7	х8	х9

 

В процессе решения надо найти такие х₁…х₉, чтобы получить при существующих ограничениях минимум целевой функции (стоимости), которая запишется так:

 

55х₁ + 20х₂ + 35х₃ + 65х₄ + 35х₅ + 20х₆ + 60х₇ + 25х₈ + 25х₉

 

Запишем ограничения в виде формул. Ограничения на ежедневное количество человек в экскурсиях по городам:

 

х₁ + х₂ + х₃ = 30 х₄ + х₅ + х₆ = 20 х₇ + х₈ + х₉ = 16

 

Ограничения на ежедневное количество мест по видам экскурсий:

х₁ + х₄ + х₇ <= 25 х₂ + х₅ + х₈ <=20 х₃ + х₆ + х₉ <=30

 

Другие ограничения – количество туристов от каждого отеля на экскурсию неделимо и больше 5: (х₁, х₂, … х₉) >= 5 и (х₁, х₂, … х₉) – целые числа

 

Теперь введем условие задачи – оптимизируемую модель в Excel в виде, наиболее удобном для дальнейших вычислений, как показано ниже:

 

 

Здесь, в ячейках G1:G9 размещены начальные значения неизвестных (х₁, х₂, … х₉)=0. В ячейках С3:С8 записаны граничные значения числа туристов от отелей и в экскурсиях. В ячейках D3:D8 записаны формулы-заготовки для ограничений: =G1+G2+G3, =G4+G5+G6, =G7+G8+G9, =G1+G4+G7, =G2+G5+G8, =G3+G6+G9. Целевая функция записана в ячейке В1: =55*G1+20*G2+35*G3+65*G4+35*G5+20*G6+60*G7+25*G8+25*G9.

Нам осталось запустить поиск решения С е рвисàПоиск р ешения… и ввести адреса ячеек и ограничения, как на рисунке:

 

 

Результат поиска решения выглядит так:

 

 

Здесь в ячейках G1:G9 подобрано оптимальное количество туристов, дающее минимальную стоимость расходов, равную 2295$. Проанализируйте полученное решение.

Поэкспериментируйте: попробуйте вручную изменить подобранные значения, оцените значения целевой функции. Повторно вызовите инструмент Поиск решения, удалив условие (х₁, х₂, … х₉) >= 5 или добавив новое условие, и выполните подбор.

Обобщим проделанную работу и выделим этапы решения задачи оптимизации со многими неизвестными в Excel с помощью инструмента Поиск решения:

· анализ задачи, выделение свойств, параметров, ограничений;

· математическое описание оптимизируемой модели – введение обозначений, ограничений и создание целевой функции;

· грамотное размещение модели и поиск решения в Excel.

 

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

 

В состав Excel входит файл с примерами использования инструмента Поиск решения Solvsamp.xls. Он обычно расположен в папке Program Files\Microsoft Office\Office11\Samples. Каждый лист содержит один из шести примеров — "Структура производства", "Транспортная задача", "График занятости", "Управление капиталом", "Портфель ценных бумаг" и "Проектирование цепи". В примерах уже подобраны целевая и влияющие ячейки, а также ограничения. Примеры из Solvsamp.xls помогут разрешить ваши вопросы.

Отметим, что инструмент Поиск решения работает аналогичным образом в программе Calc OpenOffice.

 

 

Построение графиков

1. Постройте график функции y=е^2х на интервале х Î [0.5;2] с шагом 0.1.

2. Постройте график функции y=2^х на интервале х Î [-2;2] с шагом 0.5.

3. Постройте график функции y=lnx на интервале х Î [0.5;10] с шагом 0.5.

4. Постройте график функции y=2cos3x*sinx на интервале х Î [0.1;1.8] с шагом 0.1.

5. Постройте параболу y=x² на интервале х Î [-3;3] с шагом 0.25.

6. Постройте гиперболу y=1/2x на интервале х Î [0.1;10] с шагом 0.1.

7. Постройте верхнюю полуокружность x² + y² = 9 на интервале х Î [-3;3] с шагом 0.25.

8. Постройте верхнюю половину эллипса x²/9 + y²/4 = 1 на интервале х Î [-3.5;3.5] с шагом 0.25.

9. Постройте часть плоскости 2x + 4y –2z +2 = 0, лежащую в I квадранте: х Î [0;6] с шагом 0.5, y Î [0;6] с шагом 1.

10. Постройте верхнюю часть эллипсоида, заданного уравнением x²/9 + y²/4 + z² = 1 для ХÎ[-3;3], YÎ[-2;2] c шагом 0.5 для Х и Y.

11. Постройте верхнюю часть двухполостного гиперболоида, заданного уравнением x²/9 + y²/4 - z² = -1, где хÎ[-3;3], yÎ[-2;2] c шагом 0.5 для x и y.

12. Постройте часть гиперболического параболоида, заданного уравнением x²/9 - y²/4 = 2z, где хÎ[-3;3], yÎ[-2;2] c шагом 0.5 для x и y.

13. Постройте часть конуса, заданного уравнением x²/4 + y²/9 – z²/4 = 0, где хÎ[-2;2] с шагом 0.5, yÎ[-3;3] c шагом 1.

 

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

Исходные данные для контрольного задания приведены на рис. 51 – это данные о поступивших и выполненных заказах.

 

Рис. 51

 

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

 

 

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

1. Найдите решение уравнения х³+5х²-х+1=0

2. Найдите два решения уравнения х²-3х+2=0

3. Найдите решение уравнения е^х=20000

 

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

1. Об оптимальных перевозках – транспортная задача. Ее цель состоит в минимизации затрат на перевозки товара со складов к потребителям. Именно к этой задаче сводится множество задач распределения ресурсов. Торговое предприятие развозит с двух своих складов 4 вида товара по сети из 5-ти магазинов. Средние дневные продажи магазинов каждого вида товара в штуках следующие:

 

	Магазин 1	Магазин 2	Магазин 3	Магазин 4	Магазин 5
Товар 1
Товар 2
Товар 3
Товар 4

 

Известна также стоимость доставки единицы товара по магазинам – она задана таблицей:

 

	Магазин 1	Магазин 2	Магазин 3	Магазин 4	Магазин 5
Склад 1
Склад 2

 

Кроме того, известно, что минимальный остаток каждого товара на 1-ом складе равен 100 штук, на 2-ом складе – 150 штук. Необходимо составить план развоза товаров, обеспечивающий минимальную стоимость развоза.

 

2. О рационе питания – оптимальная смесь. Минимальный ежедневный рацион питания животного на ферме должен содержать 6 единиц белков, 8 единиц жиров и 12 единиц углеводов. Животные получают три вида кормов, стоимостью 3, 2 и 4 рубля за кг. Содержание единицы белков, жиров и углеводов в 1 кг корма приведено в таблице:

 

	Белков (ед.)	Жиров (ед.)	Углеводов (ед.)
Корм 1
Корм 2
Корм 3

 

Найти оптимальный рацион питания, минимизирующий стоимость кормов.

 

3. Об оптимальном плане – ассортимент продукции. Ателье шьет комбинезоны трех типов К1, К2, К3 и использует ткани четырех типов Т1, Т2, Т3, Т4. Нормы расхода ткани каждого типа на каждый комбинезон приведены в таблице:

 

	К1	К2	К3
Т1
Т2		1.5
Т3	0.5		0.5
Т4			0.5

 

Стоимость пошива комбинезона типа К1 равна 100 руб., К2 – 120 руб., К3 – 110 руб. (это расходы на сдельную оплату труда работников). Месячный запас тканей типа Т1 равен 1500м, Т2 – 2400м, Т3 – 900м, Т4 – 1800м. Месячный фонд зарплаты равен 100000 руб. Необходимо пошить не менее 1000 комбинезонов и обеспечить прибыль не менее 100000 руб. Прибыль от реализации комбинезона типа К1 равна 100 руб., К2 – 80 руб., К3 – 90 руб.

Необходимо определить оптимальное количество комбинезонов каждого вида (прибыль максимальна).

 

4. Об оптимальном использовании ресурсов. Составьте оптимальный план производства, чтобы стоимость всей продукции была максимальной, если:

 

	Стоимость ед. продукции	Нормы расхода ресурсов
Трудовых	Сырьевых	Материалов
Продукция 1
Продукция 2

 

Объемы имеющихся ресурсов: трудовых – 48, сырьевых – 56, материалов – 72. Цена единицы сырья – 2$, цена единицы материалов – 1.5$.

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

 

Рис. 2.33

Требуется найти ежедневный объем выпуска комбинезонов каждого вида.

Для решения задачи составим систему из 4-х уравнений с 3-мя неизвестными, где х_i есть количество пошитых комбинезонов каждого типа в день:

 

х₁ + 2х₂ + х₃ = 50

2х₁ + 1,5х₂ + 3х₃ = 80

0,5х₁ + х₂ + 0,5х₃ = 25

3х₁ + х₂ + 0,5х₃ = 60

 

Порядок вычислений согласно уравнению Х = (A^T * А)^-1 * A^T * В показан на рис. 2.34:

 

Рис. 2.34

 

В итоге в ячейках В24:В26 получено решение: ежедневный пошив комбинезонов К1 равен 14, К2 – 12 и К3 – 11. Воспроизведите порядок вычислений в Excel и проверьте найденное решение, подставив в уравнение найденные значения.

 

Задания для самостоятельного выполнения:

1. Решите в Excel следующие системы из 3-х уравнений с тремя неизвестными:

 

х₁ - 4х₂ - х₃ = -3

3х₁ + х₂ + х₃ = 5

3х₁ - 5х₂ - 6₃