Сортировка, фильтры и промежуточные итоги
В табличном процессоре Microsoft Excel команды сортировки, фильтрации и промежуточных итогов находятся на вкладке ленты Данные
(рис. 49).
Рис. 49. Вкладка ленты Данные
Рассмотрим пример обработки данных:
1. В табличном процессоре создайте таблицу (рис 50).
Рис. 50. Исходные данные
2. Для столбца Дата поступления установите формат ячеек – Дата, для столбцов Цена и Стоимость – Денежный формат.
3. Отсортируйте таблицу по столбцу Наименование товара, а затем по дате поступления. Для этого:
− выделите диапазон ячеек C4: G19;
− выполните команду: вкладка ленты Данные ► панель инструментов Сортировка и фильтр ► кнопка Сортировка;
− выберите сортировать по Наименованию товара, затем добавьте новый уровень сортировки по Дате поступления (рис. 51).
Рис. 51. Сортировка
4. Переименуйте Лист 1 в Сортировка.
5. Скопируйте таблицу на Лист 2, который переименуйте в Итоги.
6. Подведем промежуточные итоги:
− выделите диапазон B4:G19;
− выполните команду: вкладка ленты Данные ► панель инструментов Структура ► кнопка 
;
− в появившемся диалоговом окне укажите операцию суммирования по столбцу Стоимость и нажмите кнопку ОК (рис. 52).
7. После выполнения команды подведутся промежуточные итоги (рис. 53).
Рис. 52. Подведение итогов
Рис. 53. Промежуточные итоги
8. Лист 3 переименуйте в Фильтр. Скопируйте на него исходную таблицу.
9. Для включения фильтра выделите диапазон данных и выполните команду: вкладка ленты Данные ► панель инструментов Сортировка и фильтр ► кнопка 
.
|
|
|
10. После выполнения команды возле заголовков появятся кнопки фильтра 
.
11. Отфильтруйте товары, поступившие в 2009 году с ценой от 3 000 до
20 000 руб.
12. Для отбора товаров поступивших в этом году нажмите на кнопку и в раскрывающемся списке выберите команду Фильтры по дате ► укажите 2009 год.
13. Аналогичным образом отберите товары с ценой от 3 000 до 20 000 руб.
14. После выполнения фильтров в таблице останутся следующие данные:
Рис. 54. Фильтрация данных
Сводные таблицы
Сводные таблицы применяются для группировки, обобщения и анализа данных, находящихся в списках Microsoft Excel.
Рассмотрим пример:
1. В Microsoft Excel 2010 оформите таблицу (рис. 55).
Рис. 55. Исходные данные
2. Перейдите на Лист 2.
3. Выполните команду: вкладка ленты Вставка ► панель инструментов Таблицы ► кнопка 
.
4. Укажите диапазон ячеек Лист1!$A$3:$C$16 и нажмите кнопку ОК.
5. Выберите поля Исполнитель и Стоимость работ (рис. 56).
Рис. 56. Список полей сводной таблицы
6. Измените заголовки в сводной таблице (рис. 57).
Рис. 57. Сводная таблица
7. На основе сводной таблицы постройте сводную диаграмму (рис. 58).
|
|
|
Рис. 58. Сводная диаграмма
Лабораторная работа №4
Решение систем линейных уравнений
В табличном процессоре Microsoft Excel имеется возможность решать системы линейных уравнений различными способами.
Решение систем линейных уравнений методом Крамера
Пусть задана система линейных уравнений
Неизвестные x1, x2, … , xn вычисляются по формулам:
D – определитель матрицы А,
Di – определитель матрица, полученный из матрицы А путем замены i-го столбца вектором b.
, 
, 
, 
,
.
Рассмотрим пример: решить систему линейных уравнений методом Крамера.
Запишем в табличном процессоре Microsoft Office Excel 2007 матрицы, которые понадобятся нам при вычислениях (рис. 59).
Рис. 59. Исходные данные
Найдем определители D, D1, D2, и D3, используя математическую функцию МОПРЕД (рис. 60).
Рис. 60. Вычисление определителей
Корни уравнения найдем по формулам:
В результате всех вычислений должны получиться следующие данные:
Рис. 61. Вычисление корней системы уравнений
Решение систем линейных уравнений с помощью обратной матрицы
Пусть дана система линейных уравнений
Эту систему можно представить в матричном виде: А ·Х=В, где
, , .
Умножим систему линейных алгебраических уравнений А ·Х=В слева на матрицу, обратную к А. Тогда система уравнений примет вид:
|
|
|
А-1 ·А ·Х=А-1 ·В.
Так какА-1 ·А=Е(единичная матрица), то получим Е ·Х=А-1 ·В.
Таким образом, вектор неизвестных вычисляется по формуле: Х=А-1 ·В.
Рассмотрим пример: решить систему линейных уравнений с помощью обратной матрицы.
Запишем в табличном процессоре матрицу А и столбец свободных
членов В (рис. 62).
Рис. 62. Исходные данные
Нам необходимо найти обратную матрицу А-1, для этого:
1. выделите диапазон ячеек В8: D10;
2. вызовите функцию МОБР;
3. в появившемся диалоговом окне заполните поле ввода Матрица. Это поле должно содержать диапазон ячеек, в котором хранится исходная матрица, то есть В2: D4, нажмите кнопку ОК;
4. В первой ячейке выделенного диапазона появиться некоторое число. Чтобы получить всю обратную матрицу, необходимо нажать клавишу F2, для перехода в режим редактирования, а затем одновременно клавиши Ctrl+ Shift+ Enter (рис. 63).
Рис. 63. Обратная матрица
Осталось найти вектор неизвестных по формуле Х=А-1 ·В, для этого:
1. выделите диапазон ячеек G8:G10;
2. вызовите функцию МУМНОЖ;
3. в поле для первой матрицы укажите диапазон В8: D10;
4. в поле для второй матрицы укажите диапазон G2: G4;
|
|
|
5. нажмите кнопку ОК.
В результате должны получиться значения, представленные на
рисунке 64.
Рис. 64. Вычисление корней системы уравнений
Самостоятельно сделайте проверку, для этого умножьте матрицу А на Х. В результате должен получиться столбец В.
Индивидуальные задания
Решите систему линейных уравнений:
а) методом Крамера;
b) с помощью обратной матрицы.
Сделайте проверку.
| 1 | 
| 9 | 
|
| 2 | 
| 10 | 
|
| 3 | 
| 11 | 
|
| 4 | 
| 12 | 
|
| 5 | 
| 13 | 
|
| 6 | 
| 14 | 
|
| 7 | 
| 15 | 
|
| 8 | 
| 16 | 
|
| 17 | 
| 19 | 
|
| 18 | 
| 20 | 
|
Лабораторная работа №5
Решение задач оптимизации
Задачи оптимизации занимают очень важное место в бизнесе, производстве, прогнозировании. Условно эти задачи можно разделить на следующие категории:
− транспортная задача – минимизация расходов на транспортировку товаров;
− задача о назначениях – составление штатного расписания с минимизацией денежных затрат на заработную плату или времени выполнения работ;
− задачи оптимизации производства – максимизация выпуска товаров при ограничениях на сырье для производства этих товаров.
Прежде, чем искать оптимальное решение задачи необходимо построить ее математическую модель, т.е. осуществить перевод условия и решения на четкий язык математических отношений.
Задача оптимизации в общем виде формулируется следующим образом.
Найти значения переменных x1, x2, … , xn, такие, что целевая функция f(x1, x2, … , xn) примет максимальное, минимальное или заданное значения при ограничениях вида g(x1, x2, … , xn).
Таким образом, задача оптимизации содержит три основных компонента:
− переменные x1, x2, … , xn – определяемые величины;
− целевая функция – это цель, записанная математически в виде функции от переменных, принимающая максимальное, минимальное или заданное значения;
− ограничения – условия или соотношения, которым должны удовлетворять переменные.
MS Excel предоставляет возможность решения оптимизационных задач с помощью надстройки Поиск решения. При этом после создания математической модели на рабочем листе Excel создается табличная модель, где в отдельных ячейках содержаться переменные решения, в отдельные ячейки записаны формулы, по которым будут вычисляться целевая функция и функции ограничений.
Пример 1.
Цех выпускает детали А и В. На производство детали А рабочий тратит 3 часа, на производство детали В - 2 часа. От реализации детали А предприятие получает прибыль 80 ден. ед., В - 60 ден. ед. Цех должен выпустить не менее 100 штук деталей А и не менее 200 штук деталей В. Сколько деталей каждого вида надо выпустить для получения наибольшей прибыли, если фонд рабочего времени составляет 900 человеко-часов.
Дата добавления: 2018-11-24; просмотров: 1153; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!