Создание диаграммы на рабочем листе

1. Выделить несмежные диапазоны (например, содержащие фамилии, данные за четверти и за год, включая строку- шапку таблицы, для выделения несмежных диапазонов использовать клавишу CTRL).

2. Добавить диаграмму График:

3. Поместить диаграмму на листе в нужном месте.

Редактирование диаграммы

Выполнить следующие изменения в диаграмме. Для редактирования диаграммы необходимо ее активизировать щелчком мыши. При этом появится область на ленте Работа с диаграммами.

Ввести название диаграммы, на вкладке редактирования диаграммы Макет

Изменить тип диаграммы:

1. Правой кнопкой по уже созданной диаграмме / Изменить тип диаграммы либо на вкладке Конструктор / Тип / Изменить тип диаграммы

2. Изменить местоположение легенды:

Щелчок / Перетаскивание (указатель на границе),

Изменить размер легенды.

1. Выполнить форматирование легенды:

Контекстное меню / Формат легенды / Параметры легенды… Заливка… Цвет границы… Стили границ… Тень …

Контекстное меню / Шрифт...

2. Изменить представление ряда данных:

Определить местонахождение рядов данных (элементов легенды) в строках и подписи горизонтальной оси (категории) в столбцах: правой кнопкой по диаграмме / Выбрать данные / Если необходимо поменять местами значения Строка/Столбец;

3. Задать подписи элементов легенды и оси категорий.

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

4. Познакомиться с возможностями форматирования осей. Использовать выделение, контекстное меню.

Создание диаграммы на отдельном листе

1. Выделить несмежные диапазоны на различных листах;

2. Вставить и отредактировать диаграмму;

3. Изменить расположение диаграммы: Контекстное меню для диаграммы / Переместить диаграмму или на ленте Работа с диаграммами/Конструктор/ выбрать размещение на отдельном листе;

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

Практическая часть

Задание 1. Построение графиков математических функций.

1. Создайте на Листе! таблицу для построения графиков функций и на отрезке [-3; 3] с шагом 0,5. При заполнении используйте форму­лы для вычисления у₁ и у₂.

2. Ниже таблицы вставьте диаграмму. Тип диаграммы – точечная с гладкими кривыми и маркерами.

3. Установите цвета линий графика и маркеров: для у₁ - черный, для у₂ – тем­но-синий. Измените тип маркеров на графиках (рисунке 1).

4. Добавьте название диаграммы. Отобразите вертикальные и горизонталь­ные линии сетки.

5. Установите отображение значений горизонтальной оси на отрезке от-3 до 3.

6. Сравните построенную Вами диаграмму с представленной на рисунке 1. При наличии расхождений между ними внесите в Вашу диаграмму необходи­мые изменения.

Задание 2. Построение диаграмм

1. Введите данные на Лист 2.

Скопируйте их на Лист 3.

2. На Листе 2 ниже таблицы постройте диаграмму график с маркерами.

3. Увеличьте размер диаграммы.

4. Измените для ряда Продукты питания тип диаграммы на гистограмму с группировкой (рисунок 2).

5. Установите для гистограммы ряда Продукты питания градиентную заливку «Рассвет».

Рисунок 1 – Гистограмма с группировкой

 

6. Установите для линий графика следующие цвета: коммунальные платежи – красный, обслуживание автомобиля - синий, выплата кредитов – оранже­вый, прочие расходы – зеленый.

7. Вставьте название диаграммы «Динамика расходов за первое полугодие».

8. Установите вертикальное выравнивание подписей на горизонтальной оси категорий.

9. Сравните построенную Вами диаграмму с представленной на рисунке 1. При наличии расхождений между ними внесите в Вашу диаграмму необходи­мые изменения.

10. На этом же рабочем листе для исходных данных постройте линейчатую диаграмму с накоплениями.

11. Установите размеры диаграммы: высота – 8 см., ширина – 20 см.

12. Вставьте название диаграммы и подписи данных (рисунок 3).

13. Сравните построенную Вами диаграмму с представленной на рисунок 3. При наличии расхождений между ними внесите в Вашу диаграмму необходи­мые изменения.

Рисунок 2 – Линейная диаграмма

 

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

15. Вставьте название диаграммы и подписи данных.

Рисунок 3 - Кольцевая диаграмма

 

16. Сравните построенную Вами диаграмму с представленной на рисунок 3. При наличии расхождений между ними внесите в Вашу диаграмму необходи­мые изменения.

17. В исходной таблице вычислите суммарные расходы по каждому месяцу и постройте по ним объемную круговую диаграмму.

18. С помощью команды Конструктор ® Переместить диаграмму располо­жите ее на отдельном листе.

19. Отформатируйте область диаграммы: граница – сплошная линия темно-синего цвета, шириной 2пт. с тенью.

20. Удалите легенду.

21. Измените, подписи данных: у каждого сектора диаграммы отобразите название месяца и долю в процентах от общих расходов за первое полуго­дие (рисунок 4).

22. Сектор с максимальными расходами расположите отдельно от остальных секторов.

23. Сравните построенную диаграмму с рисунок 4. Покажите результаты Вашей работы преподавателю.

Рисунок 4 – Диаграмма разрезанная круговая

Задание 3. Построение спарклайнов

1. В таблице на Листе 3 вычислите ежемесячные расходы, добавьте строку ежемесячных доходов и определите ежемесячные накопления.

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

3. Измените высоту строк и ширину столбца со спарклайнами для наглядного отображения тенденций.

4. Отметьте маркерами на графиках спарклайнов минимальные и максималь­ные значения.

5. На гистограмме спарклайна выделите цветом минимальное значение.

6. Сравните построенный Вами результат с представленным на рис. 4.6. При наличии расхождений между ними внесите необходимые изменения.

7. Покажите результаты Вашей работы преподавателю.

Контрольные вопросы

1. Создание и редактирование диаграмм.

2. Редактирование диаграмм.

3. Редактирование текстовых надписей в диаграмме.

4. Форматирование осей.

5. Оформление диаграммы различными цветами.

6. Выделение по критериям.

7. Автоматическое вычисление.

8. Вставка примечания.

9. Создание диаграммы на рабочем листе.

10. Редактирование диаграммы.

11. Создание диаграммы на отдельном листе.

 

Содержание отчета

1. Тема, цель.

2. Решение практических заданий.

3. Вывод.

Лабораторная работа №22

Тема: Относительные, абсолютные и смешанные ссылки в электронных таблицах MS Excel.

Цель: Научиться использовать относительные, абсолютные и смешанные ссылки в электронных таблицах MS Excel для вычислений.

 

Ход работы

1. Изучить теоретическую часть.

2. Выполнить практические задания.

3. Ответить на контрольные вопросы.

4. Оформить отчет.

Теоретическая часть

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

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

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

По умолчанию в новых формулах используются относительные ссылки.

Абсолютные ссылки. Если возникла необходимость указать в формуле ячейку, которую нельзя менять при автозаполнении, используется знак $. Им фиксируются как столбцы, так и строки. Например: $А$10.

Абсолютная ссылка ячейки в формуле всегда ссылается на ячейку, расположенную в определенном месте.

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

При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется.

Смешанные ссылки. Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется.

Практические задания

Задание 1

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

Задание 2

Создайте следующую таблицу. Заполните нужные ячейки формулами, воспользуйтесь относительными, абсолютными или смешанными ссылками при автозаполнении формул. Для товаров, стоимость которых с учетом их количества превышает 500$, установите скидку в 1% ([стоимость товара/100]*1), используя функцию «ЕСЛИ» (информацию о данной функции найдите в справке).

Расчет приобретенных компанией канцелярских средств оргтехники Курс $ = 26,89 руб.

 

Контрольные вопросы

1. Относительные ссылки.

2. Абсолютные ссылки.

3. Смешанные ссылки.

 

Содержание отчета

1. Тема, цель.

2. Решение практических заданий по вариантам.

3. Вывод.

 

Лабораторная работа №23

Тема: Использование встроенных функций при вычислениях в электронных таблицах MS Excel.

Цель: Научиться применять при вычислениях в электронных таблицах MS Excel встроенные функции.

Ход работы

1. Изучить теоретическую часть.

2. Выполнить практические задания.

3. Ответить на контрольные вопросы.

4. Оформить отчет.

Теоретическая часть

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

Функция может быть выбрана двумя способами.

1. В меню «Вставка» выбрать команду «Функция». В окне списка «функция» выделить нужную функцию рабочей таблицы и затем нажать кнопку «ОК».

2. Мастер функций. Работа с Мастером функций начинается с нажатия кнопки «f_х» в строке формул.

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

Количество стандартных (встроенных) функций в MS Excel около 400. Использование стандартных функций позволяет значительно упростить процесс вычислений. Для ввода функции необходимо ввести вначале знак равенства, затем название функции и диапазон ячеек с нужными данными.

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

В MS Excel широко представлены математические функции. С помощью статистических функций, возможно, проводить статистическое моделирование. Кроме того, возможно использовать элементы регрессионного анализа.

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

Логические функции находятся в MS Excel в меню вставка / функции / категория / логические.

Функция ЕСЛИ. Эта функция позволяет задать некоторое условие и определить значение, если условие истинно или, если условие ложно. Вид функции в ячейке может быть, например, следующим: =Если (А2>50;A5+C6;A5+C7).

Это означает, что, если в ячейку А2 введено число большее 50, то в данную ячейку (ячейку, в которую введена данная функция) будет помещена сумма данных из ячеек А5 и С6, иначе – сумма из ячеек А5 и С7.

Другой пример логической функции в ячейке:

= Если (и(А2>50;В2>20;C2>70);A2+B2+C2;A2-B2-C2)

Это означает, что, если будет выполнены все условия во внутренних скобках, то в данную ячейку будет помещена сумма данных из ячеек А2, B2, С2, иначе – разность этих ячеек.

Некоторые изменения в логической функции предыдущего примера дают другую формулу:

= Если (или(А2>50;В2>20;C2>70);A2+B2+C2;A2-B2-C2)

Это означает, что, если будет выполнено хотя бы одно из условий во внутренних скобках, то в данную ячейку будет помещена сумма данных из ячеек А2, B2, С2, иначе – разность этих ячеек.

Таблица 1. Типовые ошибки в формулах

Ошибка	Пояснения
#ДЕЛ/0!	Функция содержит деление на ноль
#ИМЯ?	В функции используется неопределенное либо некорректное имя ячейки (Excel не может найти путь к ячейке)
#ЗНАЧ!	Функция содержит недопустимый тип аргумента, например, пытается произвести арифметические действия не над числами, а над текстом
#ССЫЛ!	Функция ссылается на несуществующую ячейку или интервал ячеек (возможно, они были удалены)
#ЧИСЛО!	Функция содержит некорректную математическую операцию, нарушение математических правил, например, корень из отрицательного числа

 

Для облегчения поиска ошибок в формулах целесообразно перейти в режим показа формул в ячейках через Сервис/Параметры/Вид и установить флажок «Формулы». При этом ширина ячеек таблицы будет автоматически увеличена и вместо результатов и сообщений об ошибках будут показаны формулы.

Математические (арифметические и тригонометрические) функции

ABS(x) – возвращает значение модуля числа x.

ACOS(x) – возвращает значение арккосинуса числа х. Арккосинус числа – это угол, косинус которого равен числу х. Угол определяется в радианах в интервале от 0 до p.

ASIN(x) – возвращает значение арксинуса числа х. Арксинус числа – это угол, синус которого равен числу х. Угол определяется в радианах в интервале от - p /2 до p /2.

ATAN(x) – возвращает значение арктангенса числа х. Арктангенс числа – это угол, тангенс которого равен числу х. Угол определяется в радианах в интервале от - p /2 до p /2.

COS(x) – возвращает значение косинуса числа х.

EXP(x) – возвращает значение числа е, возведённого в степень х. Число е=2,71828182845904 – основание натурального логарифма.

LN(x) – возвращает значение натурального логарифма числа х.

LOG10(x) – возвращает значение десятичного логарифма числа х.

SIN(x) – возвращает значение синуса числа х.

TAN(x) – возвращает значение тангенса числа х.

КОРЕНЬ(х) – возвращает положительное значение квадратного корня из числа х.

ПИ() – возвращает значение числа p =3,14159265358979 с точностью до 15 цифр, однако в настоящее время эта точность достигнута до 3 триллионов цифр.

РАДИАНЫ(угол) – преобразует угол из градусов в радианы. РЯД.СУММ(x; n; m; коэффициенты) – возвращает значение суммы степенного ряда, где:

x – значение переменной степенного ряда;

n – показатель степени х для первого члена степенного ряда;

m – шаг, на который изменяется показатель степени n для каждого следующего члена степенного ряда;

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

Пример: =РЯД.СУММ(B2;B3;B4;B5:B10)

Здесь в ячейках B2:B10 записаны значения соответствующих параметров функции.

СТЕПЕНЬ(число; степень) – возвращает результат возведения числа в степень.

СУММ(число1;число2;…;числоN) – суммирует все числа, заданные аргументами, в качестве которых могут использоваться и интервалы ячеек.

ФАКТР(число) – возвращает факториал числа. Факториал числа n – n!=1 × 2 × 3 × … × n.

Статистические функции

МАКС(число1;число2;…;числоN) – возвращает максимальное число из списка аргументов. Допустимое количество аргументов в списке от 1 до 30.

МИН(число1;число2;…;числоN) – возвращает минимальное число из списка аргументов. Допустимое количество аргументов в списке от 1 до 30.

СРЗНАЧ(число1;число2;…;числоN) – возвращает среднее арифметическое значение своих аргументов. Допустимое количество аргументов в списке от 1 до 30.

Логические функции

И(логическое_значение1;логическое_значение2;…) – возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА. Если хотя бы один аргумент имеет значение ЛОЖЬ, тогда возвращается ЛОЖЬ.

Логическое_значение1;логическое_значение2;… – это от 1 до 30 проверяемых условий.

Примеры:

=И(2+3=5;3+4=7) равняется ИСТИНА.

=И(5 < A1;A1< 50) равняется ИСТИНА, если ячейка А1 содержит число между 5 и 50.

ИЛИ(логическое_значение1;логическое_значение2;…) – возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА. Если все аргументы имеют значение ЛОЖЬ, тогда возвращается ЛОЖЬ. Логическое_значение1; логическое_значение2; … – это от 1 до 30 проверяемых условий.

Примеры:

=ИЛИ(2+2=5;3+4=7) равняется ИСТИНА.

=ИЛИ(2+2=5;3+5=7) равняется ЛОЖЬ.

НЕ(логическое значение) – меняет на противоположное логическое значение своего аргумента.

Пример:

=НЕ(1+1=2) равняется ЛОЖЬ.

ЕСЛИ(логическое_выражение;1(если_ИСТИНА);2(если_ЛОЖЬ))

Пример:

Допустим, надо вычислить значение функции ln(x) от х = –0,5 до 1,5 с шагом изменения аргумента х, равным 0,5. Значения аргумента х записаны в ячейках A3:A7. Известно, что логарифм отрицательного аргумента и нуля не существует (не определён), тогда функция ЕСЛИ() будет иметь вид: = ЕСЛИ(A3>0;LN(A3); ”Не сущ.”)

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

Функции ссылок

СТОЛБЕЦ() – возвращает номер столбца рабочего листа, в ячейке которого введена эта функция.

СТОЛБЕЦ(ссылка) – возвращает номер столбца, определяемого ссылкой. Ссылка – это адрес ячейки или диапазона ячеек, для которых определяется номер столбца.

СТРОКА() – возвращает номер строки рабочего листа, в ячейке которой введена эта функция.

СТРОКА(ссылка) – возвращает номер строки, определяемой ссылкой. Ссылка – это адрес ячейки или диапазона ячеек, для которых определяется номер строки.

 

Практическая часть

1. Введите данные на рабочий лист.

2. Вставьте формулы для вычислений в столбцах Районный коэффициент, Начислено, Подоходный налог, Сумма к выдаче (в квадратных скобках указаны номера столбцов):

[4] = [2] * k

[5] = [2] + [4]

[6] = ([5] – [3]) * n

[7] = [5] – [6]

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

4. Ниже таблицы вставьте формулы для вычисления:

а) максимальной суммы к выдаче;

б) среднего оклада;

в) минимального налога;

г) количества рабочих, оклады которых превышают 16 000 руб. (функция СЧЁТЕСЛИ());

д) суммарный подоходный налог рабочих, имеющих налоговые вычеты (функция СУММЕСЛИ());

е) суммарный подоходный налог рабочих, оклады которых превышают 16 000 руб. и не имеющих налоговые вычеты (функция СУММЕСЛИМНО).

5. Введите поясняющую информацию к формулам.

6. Отобразите значения во всей таблице в денежном формате с двумя знака­ми после десятичной запятой.

7. Установите в итоговой строке заливку ячеек черным цветом, белый цвет шрифта, полужирное начертание.

8. Отформатируйте таблицу согласно образцу, представленному ниже.

9. Сохраните созданную Вами рабочую книгу в своей папке на рабочем диске под именем Фамилия_Работа_3.

10. Скопируйте лист с именем Лист 1.

11. Переименуйте Лист 1 на лист с именем Ведомость, а Лист 1(2) на Форму­лы.

12. На листе Формулы отобразите формулы в ячейках таблицы.

13. Скопируйте с листа Ведомость на Лист 3 столбцы Ф.И.О., Сумма к выдаче. Для вставки из буфера обмена используйте специальную вставку (команда Главная ® Буфер обмена ® Вставить ® Специальная вставка ® значения).

14. Добавьте к таблице поля Сообщение о надбавке, Величина надбавки, Ито­говая сумма. Введите заголовок таблицы Расчет надбавки. Введите нуме­рацию столбцов (рис. 3.3).

15. Введите в столбец Сообщение о надбавке формулу, которая выводит со­общение Да, если сумма к выдаче составляет менее 20 000 р., и Нет в про­тивном случае: = ЕСЛИ(В4<20000;"Да";"Нет").

16. Введите в столбец Величина надбавки формулу, которая выводит сумму надбавки равную 20% от суммы к выдаче, если данная сумма составляет менее 20 ООО р., и 0 в противном случае.

17. Вставьте формулу для вычисления значений по столбцу Итоговая сумма.

18. Сравните полученную Вами таблицу с таблицей. При расхождении откорректируйте таблицу.

19. Покажите результат Вашей работы преподавателю.

Контрольные вопросы

1. Типовые ошибки в формулах.

2. Наиболее используемые функции.

3. Математические (арифметические и тригонометрические) функции.

4. Статистические функции.

5. Логические функции.

6. Функции ссылок.

Содержание отчета

1. Тема, цель.

2. Решение практических заданий.

3. Вывод.

 

Лабораторная работа №24

Тема: Решение профессиональных задач с помощью электронных таблиц MS Ecxel.

Цель: Приобрести навыки работы с таблицами с помощью MS Ecxel.

Ход работы

1. Изучить теоретическую часть.

2. Выполнить примеры.

3. Ответить на контрольные вопросы.

4. Оформить отчет.

Теоретическая часть

Microsoft Excel (также иногда называется Microsoft Office Excel) – программа для работы с электронными таблицами, созданная корпорацией Microsoft для Microsoft Windows, Windows NT и Mac OS, а также Android, iOS и Windows Phone. Она предоставляет возможности экономико-статистических расчетов, графические инструменты и, за исключением Excel 2008 под Mac OS X, язык макропрограммирования VBA (Visual Basic for Application). Microsoft Excel входит в состав Microsoft Office и на сегодняшний день Excel является одним из наиболее популярных приложений в мире.

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

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

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

Сделать это можно так: кнопка для запуска Поиска решения появится на вкладке Данные.

 

Рисунок 1 – кнопка Поиска решения на вкладке Данные

 

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

Пример 1. Распределение премии

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

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

 

Рисунок 2 – Таблица с исходными данными

 

Теперь запускаем Поиск решения и в открывшемся диалоговом окне устанавливаем необходимые параметры.

 

Рисунок 3 – Диалоговое окно Поиск решения

 

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

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

3. Изменяемых ячеек может быть несколько: отдельные ячейки или диапазоны. Собственно, именно в них MS Excel перебирает варианты с тем, чтобы получить в целевой ячейке заданное значение

4. Ограничения задаются с помощью кнопки Добавить. Задание ограничений, пожалуй, не менее важный и сложный этап, чем построение формул. Именно ограничения обеспечивают получение правильного результата. Ограничения можно задавать как для отдельных ячеек, так и для диапазонов. Помимо всем понятных знаков =, >=, <=, при задании ограничений можно использовать варианты цел(целое), бин(бинарное или двоичное, т.е. 0 или 1), раз (все разные).

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

 

Рисунок 4 – Диалоговое окно Параметры поиска решения

 

Кнопка, включающая итеративные вычисления с заданными параметрами.

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

 

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

 

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

Решение данной задачи выглядит так

 

Рисунок 6 – Результат поиска решения

 

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

Пример 2. Мебельное производство (максимизация прибыли)

Фирма производит две модели А и В сборных книжных полок. Их производство ограничено наличием сырья (высококачественных досок) и временем машинной обработки.

Для каждого изделия модели А требуется 3 м² досок, а для изделия модели В – 4 м². Фирма может получить от своих поставщиков до 1700 м² досок в неделю.

Для каждого изделия модели А требуется 12 мин машинного времени, а для изделия модели В – 30 мин. в неделю можно использовать 160 ч машинного времени.

Сколько изделий каждой модели следует выпускать фирме в неделю для достижения максимальной прибыли, если каждое изделие модели А приносит 60 руб. прибыли, а каждое изделие модели В – 120 руб. прибыли?

Порядок действий нам уже известен.

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

 

Рисунок 7 – Таблица с исходными данными

 

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

Рисунок 8 – Диалоговое окно

 

1. Целевая ячейка B12 содержит формулу для расчёта прибыли

2. Параметр оптимизации - максимум

3. Изменяемые ячейки B9:C9

4. Ограничения: найденные значения должны быть целыми, неотрицательными; общее количество машинного времени не должно превышать 160 ч (ссылка на ячейку D16); общее количество сырья не должно превышать 1700 м² (ссылка на ячейку D15). Здесь вместо ссылок на ячейки D15 и D16 можно было указать числа, но при использовании ссылок какие-либо изменения ограничений можно производить прямо в таблице

5. Нажимаем кнопку Найти решение (Выполнить) и после подтверждения получаем результат

 

Рисунок 9 – Результат поиска решения

 

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

 

Рисунок 10 – Неожиданный результат решения

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

 

Рисунок 11 – Диалоговое окно Параметры

 

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

Пример 3. Транспортная задача (минимизация затрат)

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

Дано: запасы песка на карьерах; потребности в песке стройплощадок; затраты на транспортировку между каждой парой «поставщик-потребитель».

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

Пример расположения ячеек с исходными данными и ограничениями, искомых ячеек и целевой ячейки показан на рисунке 12.

 

Рисунок 12 – Расположения ячеек с исходными данными

 

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

Запускаем Поиск решения и устанавливаем необходимые параметры (рисунок 13)

 

Рисунок 13 – Диалоговое окно Параметры поиска решения

 

Нажимаем Найти решение (Выполнить) и получаем результат, изображенный ниже

Рисунок 14– Результат решения

 

Иногда транспортные задачи усложняются с помощью дополнительных ограничений. Например, по каким-то причинам невозможно возить песок с карьера 2 на стройплощадку №3. Добавляем ещё одно ограничение $D$13=0. И после запуска Поиска решения получаем другой результат

 

Рисунок 15 – Добавление ограничений в исходные данные

 

И последнее, на что следует обратить внимание, это выбор метода решения. Если задача достаточно сложная, то для достижения результата может потребоваться подобрать метод решения

 

Рисунок 16 – Методы решения

 

Контрольные вопросы

1. Что такое MSExcel.

2. Поиск решения с помощью формул.

3. Виды ссылок.

4. Объединение ячеек, столбцов.

 

Содержание отчета

1. Тема, цель.

2. Решение практических заданий.

3. Вывод.

 

Лабораторная работа №25

Тема: Проектирование и создание базы данных.

Цель: Ознакомиться и получить навыки работы с программой Microsoft Access.

Ход работы

1. Изучить теоретическую часть.

2. Выполнить практические задания.

3. Ответить на контрольные вопросы.

4. Оформить отчет.

 

Теоретическая часть

Приложение Access является программой, входящий в пакет MicrosoftOffice и предназначено для работы с базами данных.

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

Система управления базами данных (СУБД) – инструментальное программное обеспечение, предназначенное для создания и ведения БД.

Основные типы моделей данных в СУБД:

1. Реляционная БД – это совокупность взаимосвязанных таблиц, каждая из которых содержит информацию об объектах определенного типа.

2. Таблица – это набор данных по конкретной теме. Данные в таблице располагаются в строках (записи). Каждая строка таблицы является набором именованных полей, которые могут хранить самую разнообразную информацию. Однотипные поля разных записей образуют столбец таблицы.

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

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

Задание 1. Создание базы данных для учета книг в библиотеке.

Выполните действие: «Пуск – Все программы – Microsoft Access». Появится стартовое окно программы Microsoft Access. Введите в строке «имя файла» название базы данных. В нашем случае «Библиотека». Щелкните на пиктограмму папки и выберите путь к файлу «Библиотека». Затем дважды кликните на пиктограмме «Новая база данных».