4. Ошибки при работе со сценариями

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

По крайней мере в одной из изменяющихся ячеек содержится формула. При использовании сценария формулы будут заменены на постоянные значения. Это сообщение появляется тогда, когда в диалоговом окне Добавление сценария или окне Изменение сценария в поле ввода Изменяемые ячейки введены адреса ячеек, содержащих формулы. Если вы сохраните эти адреса, то при просмотре сценария формулы в этих ячейках будут заменены на те значения, которые вы укажете в диалоговом окне Значение ячеек сценариев. Если этого не­обходимо избежать, закройте сначала сообщение, а затем в открытом диалоговом окне щелкните на кнопке Отмена. После этого вы вернётесь в диалоговое окно Диспетчер сценариев, откуда можно повторить попытку создания или изменения сценария.

Имена сценариев должны быть уникальными. Это сообщение об ошибке появляется тогда, когда новому сценарию вы даете имя уже существующего сценария. Если в списке сценариев диалогового окна Диспетчер сценариев нет сценария с задаваемым именем, а описывае­мое сообщение все равно появилось, то это значит, что сценарий с таким именем все же существует, но он не отображается в списке Сценарии. Такое возможно, если этот сценарий защищен и скрыт (см. подраздел «Защита сценариев от изменений»). Чтобы разрешить эту коллизию, присвойте новому сценарию другое имя, либо снимите защиту с рабо­чего листа и сценария и удалите сценарий с таким именем.

Введенный текст не является правильной ссылкой или именем. Это сообщение об ошибке появляется тогда, когда в диалоговом окне Добавление сценария или окне Изменение сценария в поле ввода Изменяемые ячейки введено нечто, что Excel не может распознать как ссылку на ячейку. Чаще всего такая ошибка возникает тогда, когда вы вручную вводите адрес ячейки, а не указываете ячейку путем щелчка на ней. (Например, если вы вводите адрес ячейки «русскими» бук­вами.) Чтобы исправить эту ошибку, закройте сначала сообщение об ошибке, а затем введите в поле ввода правильный адрес ячейки. Затем снова щелкните на кнопке ОК.

Внимание! При работе со сценариями необходимо также учитывать следую­щие ограничения:

• Нельзя отменить удаление сценария. Если нужно восстано­вить удаленный сценарий, вы должны создать его заново.

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

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

Тема: Функции для работы с матрицами

Простые диапазоны (массивы) в Excel называются матрицами. Встроенные функции матричных действий можно вводить как из библиотеки, так и с клавиатуры. Ввод формул, возвращающих матрицы всегда завершается нажатием одновременно трёх кнопок: Ctrl+Shift+Enter. Формула автоматически заключается в фигурные скобки – признак массива.

Задание 1. Получение транспонированной матрицы. В диапазон А1:С5 введите матрицу размера 3х5 (рис. 1):

	А	В	С
1	январь	февраль	март
2	1	2	3
3	86	76	91
4	43	28	35
5	79	71	109

Рис. 1. Матрица 3х5

Необходимо получить транспонированную матрицу: строки должны стать столбцами, а столбцы – строками.

1. Выделите блок ячеек, куда будет вставляться изменённая матрица. Например А8:Е10.

2. Вызовите окно Мастера построения функций и выберите функцию ТРАНСП.

3. В рабочее поле Массив введите диапазон исходной матрицы А1:Е2. Нажмите сочетание клавиш Ctrl+Shift+Enter.

З адание 2. Вычислить определитель матрицы

А=

1. Введите матрицу в диапазон G1:J4

2. Курсор поместите в ячейку, в которую будет записано значение, например J6.

3. Вызовите окно Мастера построения функций, в поле Категория выберите Математические и далее функцию МОПРЕД. Нажмите кнопку ОК.

4. В следующем окне в поле Массив введите диапазон исходной матрицы, нажмите ОК. В ячейке J6 появится значение определителя матрицы.

Ответ: 0.

Задание 3. Получение обратной матрицы. Для этого задания в любой диапазон введите матрицу

А=

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

2. Вызовите окно Мастера построения функций и в поле Категория выберите Математические и далее функцию МОБР. Нажмите кнопку ОК.

3. В следующем окне в поле Массив введите диапазон исходной матрицы, нажмите сочетание клавиш Ctrl+Shift+Enter.

Если обратная матрица не появилась, то указатель мыши поместите в строку формул и повторите сочетание клавиш Ctrl+Shift+Enter.

О твет:

Задание 4. Найти сумму двух матриц.

1. Введите две произвольные матрицы А и В размером 2х3 каждая (одинаково расположенные).

2. Выделите блок ячеек 2х3, в который будет занесена результирующая матрица.

3. Введите формулу для вычисления суммы: диапазон 1-й матрицы + диапазон 2-й матрицы.

4. Нажмите сочетание клавиш Ctrl+Shift+Enter. В результате в выделенном диапазоне появится матрица, равная сумме исходных матриц. Аналогично вычислите разность исходных матриц.

Задание 5. Умножение матрицы на число.

1. Введите произвольную матрицу А размером 2х3.

2. Выделите блок ячеек 2х3, в который будет занесена результирующая матрица.

3. Введите формулу для вычисления результатов умножения матрицы на 3: диапазон исходной матрицы*3.

4. Нажмите сочетание клавиш Ctrl+Shift+Enter. В результате в выделенном диапазоне появится матрица, равная произведению исходной матрицы на постоянную 3.

Задание 6. Произведение двух матриц.

1. Введите две произвольные матрицы А и В размером 3х3 каждая.

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

3. Вызовите окно Мастера построения функций, в поле Категория выберите Математические и далее функцию МУМНОЖ. Нажмите кнопку ОК.

4. В следующем окне в поле Массив1 введите диапазон первой исходной матрицы, а в рабочее поле Массив2 введите диапазон второй исходной матрицы.

5. Нажмите сочетание клавиш Ctrl+Shift+Enter. В результате в выделенном диапазоне появится матрица, равная произведению исходных матриц.

Задание 7. Используя функцию МУМНОЖ рассчитать затраты сырья для планового выпуска продукции. Предприятие выпускает продукцию трёх типов из двух видов сырья. Известен расход сырья каждого типа на единицу продукции. Необходимо рассчитать затраты сырья по видам для планового выпуска всей продукции (рис.2).

расход на единицу	сырьё 1	сырьё 2		план выпуска продукции
продукция 1	4	8		продукция 1	продукция 2	продукция 3
продукция 2	3	2		130	250	160
продукция 3	1	5

Рис. 2. Таблицы данных для расчёта затрат сырья

1. Введите обе матрицы.

2. Выделите блок ячеек 1х2 (например В6:С6), в который будет записана матрица, показывающая какое количество сырья необходимо для выпуска плановой продукции.

3. Вызовите окно Мастера построения функций и в поле Категория выберите Математические и далее функцию МУМНОЖ. Нажмите кнопку ОК.

4. В следующем окне в поле Массив1 введите диапазон исходной матрицы, показывающий план выпуска продукции. В рабочее поле Массив2 введите диапазон исходной матрицы, показывающей расход сырья на единицу продукции.

5. Нажмите сочетание клавиш Ctrl+Shift+Enter. В результате в выделенном диапазоне появится матрица, показывающая необходимое количество сырья для производства запланированной продукции.

Использование матриц для решения систем уравнений.

С помощью функций работы с матрицами удобно решать системы линейных уравнений вида:

Такую систему в матричном виде можно записать как АХ=В,

где А= Решением такой системы будет: Х=А^-1В,

где А^-1 – обратная матрица

Задание 8. Решить систему уравнений

1. Введите А матрицу в диапазон А1:В2 А= Вектор В введите в диапазон С1:С2 В=

2. Найдите обратную матрицу. Для этого:

- выделите блок ячеек 2х2 (например А5:В6), в который будет записана матрица;

- вызовите окно Мастера построения функций, в поле Категория выберите Математические и далее функцию МОБР. Нажмите кнопку ОК;

- в следующем окне в поле Массив введите диапазон исходной матрицы;

- нажмите сочетание клавиш Ctrl+Shift+Enter.

3. Умножьте обратную матрицу на вектор В. Диапазон ячеек, в который будет записываться результат, должен быть аналогичен диапазону матрицы В. В первой ячейке будет записано значение для х (6), во второй – для у (12). Сделайте проверку решения системы уравнений.

Задание 9. Решить систему уравнений

1. Введите матрицу А в диапазон А1:В3. Вектор В введите в диапазон С1:С3 (аналогично предыдущему заданию).

2. Найдите транспонированную матрица А^т. её размер будет 2х3, например А4:С5.

3. Найдите произведение матрицы А^т и вектора В. Размерность результирующей матрицы будет 2х1, например Е4:Е5.

4. Найдите произведение матриц А^т и А. Размерность результирующей матрицы будет 2х2, например А7:В8.

5. Найдите обратную матрицу полученной в пункте 4.

6. Найдите произведение полученной обратной матрицы на вектор А^тВ (то, что получили в пункте 3). Размерность результирующей матрицы будет 2х1, например С6:С7.

В результате получится вектор, показывающий значения х (2) и значение у (-4).

7. Выполните проверку.