Metodichka_Ekonom_Informatika_Praktika
.pdf21
Рис.3.6.
7.3. Произведем расчет ежедневного объема производства, как решение системы линейных уравнений.
Ресторан специализируется на выпуске трех видов фирменных блюд: B1, B2, B3, при этом используются ингредиенты трех типов S1, S2, S3. Нормы расхода каждого из них на одно блюдо и объем расхода ингредиентов на один день заданы в ячейках B3:E5 (см. рис.3.7). Необходимо определить какое количество блюд каждого вида можно выпустить за 1 день.
Пусть ежедневно ресторан выпускает x1 блюд вида B1, x2 блюд вида B2 и x3 блюд вида B3. Тогда в соответствии с расходом ингредиентов каждого типа имеем систему:
5x1 3x2 4x3 2700
2x1 x2 x3 900
3x1 2x2 2x3 1600
Эту систему можно записать в матричном виде: A*X=B.
Решаем систему методом обратной матрицы. В качестве матрицы А будем рассматривать матрицу коэффициентов при неизвестных, расположенных в ячейках B3:D5. Координаты вектора B (правые части уравнений) находятся в ячейках E3:E5.
7.3.1.Заполним диапазоны А1:Е5 согласно рис. 3.7.
7.3.2.Найдем обратную матрицу.
7.3.2.1.Выберите диапазон B7:D9, в котором будет размещен результат.
22
7.3.2.2.Нажмите на панели инструментов Стандартная кнопку
Вставка функции.
7.3.2.3.В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите пункт Математические, а в рабочем поле Функция – имя функции МОБР. Нажмите кнопку ОК.
7.3.2.4.В появившемся диалоговом окне МОБР в рабочее поле Массив введите диапазон исходной матрицы B3:D5.
7.3.2.5.Нажмите сочетание клавиш <Ctrl> + <Shift> + <Enter>.
Врезультате в диапазоне B7:D9 появится обратная матрица.
7.3.3.Умножением обратной матрицы на вектор В найдем вектор неизвестных X.
7.3.3.1.Выберите диапазон F7:F9, в котором будет размещен результат.
7.3.3.2.Нажмите на панели инструментов Стандартная кнопку
Вставка функции.
7.3.3.3.В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите пункт Математические, а в рабочем поле Функция – имя функции МУМНОЖ. Нажмите кнопку ОК.
7.3.3.4.В появившемся диалоговом окне МУМНОЖ в рабочее поле Массив1 введите диапазон обратной матрицы B7:D9, а в рабочее поле Массив2 введите диапазон вектора B E3:E5.
Нажмите сочетание клавиш <Ctrl> + <Shift> + <Enter>.
В результате в диапазоне F7:F9 появятся координаты вектора X.
Рис. 3.7.
Рекомендуем сделать проверку, подставив найденные значения в уравнения системы.
23
7.4. Найдем значение следующего выражения:
|
n |
m m |
2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 X i |
Bij Cij |
|
|
|
|
|
|
|
|
|
|
|
|
||
s |
i 1 |
i 1 j 1 |
|
, где X – вектор из n компонентов, B и C – матрицы |
||||||||||||
|
n |
|
||||||||||||||
|
|
1 X i2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
i 1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
|
|
размерности n m, причем, n 3, m 2 |
и X |
|
1 |
|
2 |
1 |
1 |
1 |
||||||||
|
|
, B |
|
|
|
, C |
|
|
. |
|||||||
|
|
|
|
|
|
|
|
2 |
5 |
|
|
4 |
5 |
|
||
|
|
|
|
|
|
|
2 |
|
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Для решения этой задачи нам потребуется функция рабочего листа СУММ, которая суммирует все числа из диапазона ячеек. Аргументами могут быть либо ссылки на диапазоны ячеек, либо числами. Вернемся к вычислению значения s.
7.4.1.Заполните диапазоны А1:Е3 согласно рис. 3.8.
7.4.2.Введите в диапазон A2:A4 компоненты вектора X, в диапазон B2:C3 компоненты матрицы B, в диапазон D2:E3 компоненты матрицы C.
7.4.2.1.Введите в ячейку B6 следующую формулу:
=(2*СУММ(A2:A4)+ СУММ(B2:C3* D2:E3)^2)/(1+ СУММ(A2:A4^2)) 7.4.2.2. Нажмите сочетание клавиш <Ctrl> + <Shift> + <Enter>.
Рис.3.8
Лабораторная работа № 4. Условное форматирование.
Цель: Знакомство с возможностями условного форматирования таблиц. Темы: Создание и использование правил условного форматирования.
24
1.Создайте таблицу, приведенную на рис.4.1.
1.1.Примените к диапазону В3:В14 условное форматирование с помощью набора значков «три сигнала светофора без обрамления», а к диапазону С3:С14 - «пять четвертей».
1.1.1.Активизируйте команду Главная – Стили – Условное форматирование – Наборы значков.
1.1.2.Выберите команду Управление правилами и перейдите в диалоговое окно Диспетчер правил условного форматирования.
Ознакомьтесь с возможностями данного окна.
1.2.Создайте правило условного форматирования на основе формулы. Отформатируйте только те значения диапазона В3:В14, которые больше 40%, выделив их красной заливкой. Для этого активизируйте команду
Главная – Стили – Условное форматирование – Создать правило. В диалоговом окне Создание правила форматирования выберите
Использовать формулу и введите формулу =В3>$А$16. Перейдя в диалоговое окно Формат ячеек, установите нужный формат. Повторите указанные действия для диапазона С3:С14 и порога, записанного в ячейке А17.
Рис.4.1
2.Создайте таблицу, приведенную на рис.4.2.
2.1.С помощью условного форматирования определите повторяющиеся значения в диапазоне с фамилиями.
2.2.Для диапазона В2:В14 выделите значения, превышающие два заказа и значения, равные одному заказу.
2.3.Для диапазона С2:С14 выделите суммы заказов, выше среднего значения и ниже среднего, а также выделите четыре наибольших сумм заказов.
25
2.4. Вставьте новый столбец справа от столбца С и скопируйте в него столбец сумм заказов, выровняйте значения по правому краю и увеличьте ширину столбца. Примените условное форматирование Гистограммы.
2.5. К диапазону Курьер примените условное форматирование Текст содержит и выделите значение Гермес.
Рис.4.2
Лабораторная работа № 5. Функции.
Цель: Знакомство с использованием функций табличного процессора
MS Excel.
Темы: Математические и логические функции.
1. Научитесь пользоваться математическими и статистическими
функциями.
1.1.Создайте таблицу, приведенную на рис.5.1 (столбец В содержит соответствующие формулы, например ячейка В4 содержит формулу =КОРЕНЬ(А2)).
1.2.Введите в столбец B функции, указанные в столбце А (столбец А заполнять не надо) и сравните полученные результаты с данными, приведенными в столбце В на рис.5.1.
1.3.Проанализируйте результаты и сохраните созданную таблицу в книге.
2. Научитесь пользоваться логическими функциями.
2.1.Активизируйте второй лист созданной книги.
2.2.Введите таблицу, приведенную на рис.5.2.
26
Рис.5.1
Рис.5.2
2.3. В ячейку С2 введите формулу, по которой будет вычислена скидка и скопируйте ее в диапазон С3:С6:
если стоимость товара <2000 единиц, то скидка составляет 5% от стоимости товара,
в противном случае - 10%.
Формула будет содержать функцию рабочего листа ЕСЛИ.
Выделите ячейку Е2 и воспользуйтесь Мастером функций. Заполните поля функции ЕСЛИ() как показано на рисунке 5.3., нажмите <ОК>.
27
Рис.5.3.
Синтаксис функции ЕСЛИ()
ЕСЛИ(лог_выражение;значение_если_истина ;значение_если_ложь)
Лог_выражение— это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, В2<> –5 это логическое выражение проверяет условие x ≠ –5; если значение в ячейке В2 ≠ –5, то выражение принимает значние ИСТИНА. В противном случае — ЛОЖЬ.
Значение_если_истина — это значение, которое возвращается, если Лог_выражение равно ИСТИНА. Например, если В2 ≠ –5, то в ячейку Е2 помещается значение, стояще в ячейке С2. Значение_если_истина может быть формулой.
Значение_если_ложь — это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. Например, если В2 = –5, то функция ЕСЛИ отобразит текст «Функция не определена» в ячейке Е2.
Значение_если_ложь может быть формулой.
Одна из ветвей условного оператора может быть «пустой». В функции ЕСЛИ() для таких случаев предусмотрены следующие возможности:
Если лог_выражение равно ИСТИНА, а Значение_если_истина пусто, то возвращается значение 0. Чтобы отобразить слово ИСТИНА, необходимо использовать логическое значение ИСТИНА для этого аргумента.
Если лог_выражение равно ЛОЖЬ, а Значение_если_ложь опущено (то есть после значение_если_истина нет точки с запятой), то возвращается логическое значение ЛОЖЬ.
Если лог_выражение равно ЛОЖЬ, а Значение_если_ложь пусто (то есть после значение_если_истина стоит точка с запятой с последующей закрывающей скобкой), то возвращается значение 0. ►
28
2.4. В ячейку D2 введите формулу, определяющую налог и скопируйте ее в диапазон D3:D6:
если разность между стоимостью и скидкой >5000, то налог составит 5% от этой разности,
в противном случае - 2%.
2.5. Повторите п.2.3 для следующих условий:
если стоимость товара <2000, то скидка составляет 5% от стоимости товара,
если стоимость товара >5000, то скидка составляет 15% от стоимости товара,
в противном случае - 10%.
2.6.В ячейку А10 может быть занесена одна из текстовых констант: "желтый", "зеленый", "красный". В ячейку А11 введите формулу, которая в зависимости от содержимого клетки А10, будет возвращать значения: "ждите","идите" или "стойте", соответственно.
2.7.Занесите в ячейки Е8:E10 три имени: (Лена, Зина, Вера), а в клетки F8:F10 занесите даты их рождений. В клетку E4 введите одно из упомянутых имен.
Пользуясь конструкцией "вложенного" оператора ЕСЛИ, выполните следующие действия:
- проанализировав имя в клетке Е4, запишите в клетку С12 функцию ЕСЛИ, обеспечивающую:
вывод даты рождения, взятой из соответствующей клетки,
если же введено неподходящее имя, вывод сообщения: "нет такого имени".
3. Постройте график функции на интервале [ – 10; 10]
|
|
sin(x2 1) |
||||||||
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
4 |
|
x2 2x 3 |
||||||||
|
|
|
|
x 2 |
|
|
|
|
||
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
||
g( x) e |
|
2 , если |
||||||||
|
|
|
ln( x 3)2 |
|||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
( x 3) |
3 / 7 |
|
|
|
||||
|
|
|
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
, если x 1
1 x 3 . 3 x
3.1. Найдем область определения функции g(x). Очевидно, что на каждом интервале функция определена.
3.2. Воспользуемся функцией ЕСЛИ() при вычислении значений функции на интервале [ –10;10]. На новом листе введите наименование столбцов,
29
внесите нумерацию и значениия х (A1:B22) как показано на рис. 5.4. В ячейку С2 введите формулу
=ЕСЛИ(B2<-1;SIN(B2^2+1)/(B2^2-2*B2-3)^(1/4);ЕСЛИ(B2<=3; EXP(-B2^2/КОРЕНЬ(2*ПИ()));LN((B2-3)^2)/(B2-3)^(3/7))).
Рис. 5.4
Для ввода формулы используйте мастер функций. Для вложения функции ЕСЛИ в функцию ЕСЛИ используйте раскрывающийся список функций рабочего листа (см. рис.5.5).
вставка
функции
ЕСЛИ
выбрать функцию ЕСЛИ из раскрывающегося списка
Рис. 5.5.
Допускается до 7 вложений в функцию ЕСЛИ в качестве аргумента функции ЕСЛИ.
30
3.3. Постройте точечный график функции g(x) (см. рис.5.6). Выделите В2:С22, выполните команду Диаграмма-Точечная на ленточной вкладке
Вставка.
Рис.5.6. График функции g(x).
Лабораторная работа № 6. Диаграммы.
Цель: Знакомство с графическим представлением табличных данных в
MS Excel.
Темы: Работа с диаграммами. Использование основных типов диаграмм. Создание и редактирование диаграмм.
1. Введите таблицу, представленную на рис.6.1, на первый и второй листы книги.
Рис.6.1
2. Научитесь создавать диаграммы на листе Диаграмма и на рабочем листе. 2.1. Выделите рабочий диапазон таблицы А4:G6, и нажмите клавишу F11 для быстрого построения гистограммы на отдельном листе.
2.2. Познакомьтесь с командами вкладки Работа с диаграммами – Конструктор - Тип и поменяйте гистограмму на нормированную гистограмму и проанализируйте полученный результат, верните прежний тип гистограммы.