Лабораторная работа №9
Тема: Работа с массивами в MS Excel
Программное обеспечение: OS Windows, MS Excel
Методические указания
При работе с таблицами часто возникает необходимость применить одну и ту же операцию к целому диапазону ячеек или произвести расчеты по формулам, зависящим от большого массива данных.
Массив – прямоугольный диапазон формул или значений, которые MS Excel обрабатывает как единую группу. Прямоугольный числовой массив, состоящий из m строк и n столбцов, принято называть матрицей размерности mxn .
При работе с массивами необходимо помнить следующие два основных правила:
1. Результатом операции над массивами (матрицами) также является массив (матрица) определенного размера. Поэтому при вводе формулы необходимо заранее выделить весь диапазон рабочего листа, где будет находиться будущий результат.
2. По окончании ввода формулы нажимать комбинацию клавиш CTRL+SHIFT+ENTER.
Две матрицы одинаковой размерности можно сложить или вычесть путем простого сложения или вычитания (получится массив такой же размерности). Функции для работы с матрицами приведены в таблице 1.
Таблица 1
Функция ТРАНСП находится в категории «Ссылки и массивы», а все остальные – в категории «Математические».
З
адача
1
Выполните следующие действия:
1) Найдите сумму и разность двух матриц: и
2) Найти произведение матриц:
3) Вычислить определители матриц из п. 1)
4) Найти матрицы, обратные к матрицам из п.1)
5) Решите систему уравнений методом обратной матрицы
Задача 2
Решите следующие системы уравнений методом обратной матрицы, предварительно найдя матрицу коэффициентов при неизвестных:
Задача 3
Решить задачу, используя функции работы с массивами и матрицами.
Ателье выпускает три вида изделий: брюки, юбки, жилеты. При этом используется два вида тканей: шерстяная и подкладочная. Норма расхода тканей характеризуется матрицей A (таблица 2).
Таблица 2
Определить:
a) количество метров тканей (D), необходимое для выпуска следующих изделий (таблица 3).
Таблица 3
b) общую стоимость тканей (S), если известна их цена (С)
Лабораторная работа №10
Тема: Условное форматирование в MS Excel
Программное обеспечение: OS Windows, MS Excel
Методические указания
Условное форматирование – это один из способов визуального выделения данных, отвечающих определенным требованиям (критериям).
Для его применения необходимо выделить массив данных (без заголовков) и выполнить команду:
Главная/Стили/Условное форматирование/Правила выделения ячеек/Другие правила.
Данная команда позволяет пользователю создавать свои правила выделения ячеек. Кроме этого, имеется еще множество встроенных правил выделения, доступных по той же команде.
Задача о транспортной компании.
Обработать совокупные данные о грузоперевозках транспортной компании за 2009 год, применяя условное форматирование.
Таблица 1
Таблица 2
П о р я д о к р а б о т ы :
1. Перенести на рабочий лист таблицы 8 и 9 вместе с данными.
2. Создать формулы для заполнения данными строк «суммарно», «максимум», «минимум», «среднеквартальное».
3. Получить итоговые данные в столбце «За 2009 год». Они должны быть представлены в числовом формате с одним знаком после запятой.
4. На основании имеющихся и полученных данных построить:
a. диаграмму (тип – "график с маркерами"), сравнивающую изменение объема перевозок по направлениям за 1, 2, 3 и 4 кварталы одновременно (каждый ряд данных отображает объемы перевозок на протяжении одного квартала по всем направлениям); заголовок и подписи данных не показывать, легенда должна отражать подписи рядов: 1квартал, 2 квартал и т.д.
b. диаграмму (тип – "круговая"), отражающую долю перевозок каждого квартала 2009 года (суммарно) в сумме перевозок за год; секторы должны быть подписаны кварталами, легенду показывать не обязательно;
заголовок: "Объем перевозок за 2009 год по кварталам".
5. Задайте условный формат для верхней части таблицы 8, в результате применения которого ячейки, значение которых меньше среднего значения за год, были бы помечены светло-розовым фоном и синим жирным шрифтом, а ячейки, значение которых больше среднего значения за год, были бы помечены светло-голубым фоном и красными пунктирными границами.
6. Заполните таблицу 2 данными, полученными в результате вычисления процента перевозок, принимая за 100% суммарное количество перевозок за квартал (см. расчеты таблицы 1). При этом формула для вычисления процента должна быть введена только в одну ячейку (например, Москва-1 квартал), а в остальные скопирована. Чтобы это было возможным, примените смешанные ссылки. Формулу для вычисления создайте самостоятельно, используя известные соотношения в пропорциях.
Данные должны быть отражены в процентном формате с одним десятичным знаком после запятой.
7. Примените условный формат к полученным данным. В результате ячейки, содержащие значения менее 10%, должны быть помечены синим шрифтом, а ячейки, содержащие значения более 20%, должны быть помечены красным шрифтом.