Лабораторная работа 1. Основы работы с программой ms excel
 Скачать 1.56 Mb.
|
ЛАБОРАТОРНАЯ РАБОТА №2. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Наименование | Цена | Количество | Стоимость | Процент |
| Марс | 6.5 | 150 | | |
| Сникерс | 7.5 | 100 | | |
| Баунти | 6.8 | 200 | | |
| Пикник | 10 | 250 | | |
| Твикс | 8.5 | 350 | | |
| Шок | 7 | 180 | | |
| ИТОГО: | | | | |
Важно!
После того, как вы введете число, убедитесь, что оно соответствует образцу. Если это не так, значит у вас либо ошибочный десятичный знак (вместо запятой - точка), либо другой формат ячейки. Для изменения формата ячейки выберите во вкладке Главная/ Ячейки/Формат/Формат ячеек, вкладку Число и измените формат на Числовой.
Установите курсор в ячейку D2 и наберите формулу = B2*C2 (стоимость=цена*количество). В ячейке D2 получился числовой результат (975).
Для вычисления стоимости в остальных ячейках таблицы можно продолжать набирать формулы, но проще скопировать формулу из ячейки D2 на нижележащие ячейки. Для этого можно воспользоваться маркером автозаполнения.
Важно!
При копировании формул происходит автоматическое изменение адресов ячеек. При этом копирование на нижележащую строку приводит к автоматическому увеличению номера строки в адресах ячеек, а копирование вправо – к автоматическому увеличению номера столбца.
Установите курсор в ячейку D8 и найдите на вкладке Главная/Редактирование/ кнопку Автосумма (∑). Щелкните по ней. В строке формул появится формула =СУММ(D2:D7), а указанные ячейки будут обведены пунктиром. Нажмите Enter. В ячейке появится итоговая стоимость шоколадных батончиков.
Подсчитаем, какой процент общей стоимости будет составлять стоимость отдельных товаров. Ссылка на ячейку D8 должна быть абсолютной, поэтому ее адрес будем записывать $D$8. В ячейке E2 запишите формулу =D2/$D$8. Нажмите Enter.
Важно!
Для удобства ввода абсолютного адреса можно сначала ввести относительный адрес, а затем нажать клавишу <F4>. Многократное нажатие клавиши позволяет менять формат абсолютного адреса.
Скопируйте формулу в ячейки E3-E8.
Выделите ячейки E2-E8 и на вкладке Главная/Число/выберете строку Процент или нажмите кнопку (%) на кнопочной панели Число. Нажмите ОК. В ячейках отобразятся проценты (в Е8 должно получиться 100%).
ЗАДАНИЕ 2. Форматирование таблицы
Озаглавьте составленную таблицу. Для этого вставьте пустую строку выше таблицы (при выделенной первой строке нажмите правую кнопку мыши и выберите команду Вставить).
В ячейку А1 введите текст Итоги продаж шоколадных батончиков за ноябрь 2010 г.
Отцентрируйте введенный текст по ширине составленной таблицы. Для этого выделите блок ячеек А1-Е1 и нажмите кнопку Объединить и поместить в центре
вкладка Главная/Выравнивание.Скопируйте таблицу вместе с заголовком на Лист 2 рабочей книги.
Переименуйте Лист 2 в Копию итогов продаж.
Перейдите на лист Итоги продаж. Выделите таблицу без заголовка. На вкладке Главная/Стили/Стили ячеек выберите понравившийся вам стиль оформления таблицы.
Выделите заголовок. Обратите внимание на то, что группа ячеек А1-Е1 выделяется как единая ячейка, поэтому все приемы форматирования будут относиться ко всему выделению. Установите размер шрифта 14 пт, жирный, курсив, цвет темно-синий, заливка светло-желтая. Контур двойная линия темно-синего цвета. (Используйте вкладку Главная/Шрифт..)
Если текст заголовка не помещается по ширине в ячейку, используйте вкладку Главная/Шрифт/Формат ячеек, перейдите на закладку Выравнивание, установите флажок Переносить по словам. Выберите варианты размещения текста по горизонтали и вертикали По центру. Если часть текста заголовка по-прежнему не отображается в ячейке, увеличьте высоту первой строки таблицы протяжкой за разделительную линию между ярлычками первой и второй строки.
Перейдите на лист Копия итогов продаж. Вторую таблицу оформите самостоятельно, выделяя нужные группы ячеек и используйте вкладку Главная/Шрифт/Формат ячеек. Выделите цветом ячейки, содержащие заголовки столбцов таблицы, и ячейки, содержащие названия шоколадных батончиков. Выделите жирным шрифтом стоимости. Выделите итоговый результат.
ЗАДАНИЕ 3. Построение диаграмм
Отображение данных таблиц в виде диаграмм является важным аспектом использования электронных таблиц, так как диаграммы позволяют сделать результаты расчетов более наглядными. Существует большое количество типов диаграмм, которые можно отобразить средствами MS Excel. Тип выбранной диаграммы зависит от того, что вы хотите отобразить. Наиболее популярными являются столбиковые диаграммы (гистограммы), которые используются при сравнении показателей, и круговые диаграммы, которые отображают итоги.
Построим диаграмму, на которой отобразим количество проданных шоколадных батончиков каждого вида.
Выделите ячейки А3:С8 таблицы. Далее на вкладке Вставка/Диаграммы
Выберите тип диаграммы Гистограмма. Выберите вид диаграммы: Объемная гистограмма.
Далее уточняется диапазон исходных данных. Т.к. в диапазон попал неинформативный столбец Цена, то на этом шаге его можно удалить из диаграммы. Для этого перейдите на вкладку Работа с диаграммами/Конструктор/Данные/Выбрать данные. В поле Ряд выделите Ряд1 и нажмите кнопку Удалить. Обратите внимание на то, как изменился внешний вид диаграммы.
На следующем этапе уточняется оформление диаграммы. На вкладке Работа с диаграммами/Макет/Подписи/ Название диаграммы введите Итоги продаж за ноябрь 2010 г.
На закладке Легенда выберите Не добавлять легенду.
На закладке Подписи данных установите переключатель на вариант Показать.
На последнем этапе выберите место размещения диаграммы: Работа с диаграммами/Конструктор/Расположение /Отдельный лист. Нажмите кнопку OK. Диаграмма появится на отдельном листе.
Переименуйте лист Диаграмма1 в Диаграмма продаж.
Перейдите на лист Итоги продаж. Для построения круговых диаграмм необходимо выделить два отдельных блока ячеек А3-А8 и D3-D8. Это можно сделать следующим образом: выделить блок А3-А8, затем, нажав клавишу Ctrl и удерживая её, выделить блок D3-D8.
Далее на вкладке Вставка/Диаграммы выберите вариант Круговая. Самостоятельно постройте круговую диаграмму на отдельном листе и отформатируйте её. Лист переименуйте в Круговая диаграмма.
Самостоятельно постройте еще две диаграммы: С областями и График. Разместите их на листе Итоги продаж.
Важно!
Если диаграмма не подошла по внешнему виду, ее можно удалить клавишей Delete. Можно изменить размер диаграммы, для этого нужно выполнить протяжку с нажатой левой кнопкой мыши за чёрные квадратные маркеры рамки диаграммы.
Сохраните рабочую книгу в своей папке на диске.
ЗАДАНИЕ 4. Создание таблицы с расчетами в Excel
В рабочей книге сделайте активным чистый лист или вставьте новый лист. Переименуйте его в Грузоперевозки. Заполните все клетки таблицы по образцу.
| Вид груза | Количество, млн. тонн | Динамика | |
| 2008 | 2009 | по сравнению с 2008 годом, % | |
| Каменный уголь | 141,7 | 128 | |
| Кокс | 3,8 | 4,3 | |
| Нефть и нефтепродукты | 111,6 | 113,5 | |
| Руда железная и марганцевая | 53,2 | 44,6 | |
| Черные металлы | 38,9 | 29,6 | |
| Химические и минеральные удобрения | 22,6 | 18,7 | |
| Зерно и продукты перемола | 9,6 | 11,5 | |
| Строительные грузы | 36,7 | 59 | |
| ВСЕГО: | | | |
Заполните формулами все свободные клетки таблицы.
Во вкладке Главная/Число необходимо выбрать формат числа с одним десятичным знаком.
Оформите таблицу.
Постройте на отдельном рабочем листе сравнительную диаграмму, отображающую количество перевезенного груза каждого вида за 2008 и 2009 годы.
Сохраните работу.
ЗАДАНИЕ 5. Расчеты по формулам, содержащим абсолютные ссылки
В рабочей книге сделайте активным чистый лист или вставьте новый лист. Переименуйте его в Видеокамеры. Заполните все клетки таблицы по образцу.
В ячейку В1 введите формулу: =СЕГОДНЯ(), которая возвращает текущую дату.
Заполните формулами все свободные ячейки таблицы.
Оформите таблицу по образцу.
Постройте на этом же листе диаграмму, отображающую стоимость видеокамер разного вида в долларах.
Сохраните работу.
ЛАБОРАТОРНАЯ РАБОТА №3.
Расчеты в MS Excel
Цель работы: познакомиться с вычислительными возможностями табличного процессора MS Excel, освоить работу с Мастером функций, научиться выполнять сложные расчеты.
Выполнение всех математических расчетов в Excel осуществляется с помощью формул. Формулы записываются с помощью знаков операций, чисел и ссылок на ячейки. Порядок вычислений можно менять с помощью круглых скобок. В Excel предусмотрены следующие операции (см. таблицу 2):
Таблица 2
| Символ | Операция | Выражение | Результат |
| + | сложение | =5+3 | 8 |
| - | вычитание | =6-4 | 2 |
| * | умножение | =8*4 | 32 |
| / | деление | =9/3 | 3 |
| ^ | возведение в степень | =4^2 | 16 |
| % | процент от числа | =60% | 0,6 |
Например, для вычисления выражения
на рабочем листе нужно выбрать ячейку для результата расчетов и ввести в нее формулу: =(2^2+3^3)/5. В результате в ячейке появится число 6,2. 
Для выполнения более сложных математических, инженерных или финансовых расчетов в табличном процессоре MS Excel используются встроенные функции. Функции заранее определенные формулы, которые выполняют вычисления по заданным величинам (аргументам) в указанном порядке.
Любая функция имеет следующую структуру:
<Имя функции> ( список аргументов )
Имя функции это фиксированное название функции, список аргументов это данные, над которыми будут выполняться операции. В качестве имен функций могут использоваться привычные обозначения, например SIN, LN, либо англоязычные и русскоязычные сокращения выполняемых операций, например СУММ, НЕЧЁТ, ABS и др. Имена функций записываются заглавными или малыми буквами без пробелов. Список аргументов может состоять из чисел, текста, логических величин (например, ИСТИНА или ЛОЖЬ), массивов или ссылок. Кроме того, аргументы могут быть формулами или другими функциями.
Ввод функций удобно осуществлять с помощью Мастера функций, который запускается кнопкой
вставка Формула / Функция. При этом на экране появится окно Мастер функций (шаг 1 из 2) (см. рис.2). 
Рис. 2. Окно Мастера функций
В окне Мастера функций находятся названия категорий функций. При выборе категории в нижней части появится полный список имен функций, содержащихся в данной категории. Вызов функции осуществляется щелчком на ее имени. При этом в нижней части окна Мастера функций отображается краткая справка по выбранной функции. Нажав ОК, можно перейти в окно выбора аргументов функции.
В таблице приведен перечень наиболее часто встречающихся математических функций (таблица 3):
Таблица 3
| Функция | Назначение |
| ABS | Возвращает модуль (абсолютную величину) числа |
| ACOS | Возвращает арккосинус числа. Если нужно преобразовать результат из радиан в градусы, то умножьте его на 180/ПИ() |
| ASIN | Возвращает арксинус числа |
| ATAN | Возвращает арктангенс числа |
| COS | Возвращает косинус заданного угла |
| EXP | Возвращает число е, возведённое в указанную степень |
| LN | Возвращает натуральный логарифм числа |
| LOG | Возвращает логарифм числа по заданному основанию. Если основание опущено, то оно полагается равным 10 |
| LOG10 | Возвращает десятичный логарифм числа |
| SIN | Возвращает синус заданного угла |
| TAN | Возвращает тангенс заданного угла |
| КОРЕНЬ | Возвращает положительное значение квадратного корня из неотрицательного числа |
| МОБР | Возвращает обратную матрицу |
| МОПРЕД | Возвращает определитель матрицы |
| МУМНОЖ | Возвращает произведение матриц |
| НЕЧЁТ | Возвращает число, округлённое до ближайшего нечётного целого |
| ОСТАТ | Возвращает остаток от деления |
| ОТБР | Усекает число до целого, отбрасывая дробную часть числа так, что остаётся целое число |
| ПИ() | Возвращает значение числа p. У этой функции пустой список аргументов |
| ПРОИЗВЕД | Возвращает произведение чисел, заданных в качестве аргументов |
| РАДИАНЫ | Преобразует градусы в радианы |
| СТЕПЕНЬ | Возвращает результат возведения числа в степень |
| СУММ | Возвращает сумму всех чисел, входящих в список аргументов |
| ФАКТР | Возвращает факториал числа |
| ЧЁТН | Возвращает число, округлённое до ближайшего чётного целого |
ЗАДАНИЕ 1. Простейшие математические вычисления в MS Excel
Откройте чистый рабочий лист. Переименуйте лист в Функции.
Создайте следующую таблицу (Внимание! Ячейки С4:С7 не заполняйте - в них будем вводить расчетные формулы) (рис. 4).
Рис. 4.
В ячейку С4 введите формулу для расчета квадратного корня из произведения числа из ячейки А4 на модуль числа из ячейки В4.
В ячейку С5 введите формулу для возведения числа из ячейки А5 в степень, равную числу из ячейки В5.
В ячейку С6 введите формулу для расчета целой части модуля разности чисел из ячеек А6 и В6.
В ячейку С7 запишите формулу для расчета остатка от деления числа из ячейки А7 на число из ячейки В7.
Сверьте свои результаты с данными, представленными в графе “Результат”.
Сохраните работу.
ЗАДАНИЕ 2. Построение графиков функций
Откройте чистый рабочий лист. Переименуйте лист в Синусоида. Наберите таблицу по образцу, показанному на рис. 8.
Рис. 8.
Выделите ячейку А2 правой кнопкой мыши и с помощью маркера автозаполнения выделите ячейки A2:A12. Отпустите кнопку мыши и в контекстном меню выберите Прогрессия. Выберите Арифметическая и поставьте шаг 0,5. Ячейки А2:A12 должны заполниться числами 0, 0.5, 1, 1.5, ...5.
Установите курсор в ячейку B2. Выберите вкладку Формулы нажмите кнопку
вставить функцию. Выберите категорию Математические, в нижнем окне найдите SIN. В строке формул появится =SIN() и откроется окно для выбора аргумента функции. В поле Число вставьте адрес ячейки A2 и нажмите клавишу Enter. Полученную формулу скопируйте в ячейки В3:В12. Аналогичные операции выполните для столбца COS.Установите курсор в ячейку D2. Выберите вкладку Формулы нажмите кнопку
вставить функцию. Выберите категорию Статистические и функцию СРЗНАЧ. В окне выбора аргументов функции напишите B2,C2 или укажите диапазон таблицы при помощи мыши, и у вас в ячейке D2 должно получиться среднее значение ячеек В2 и C2, равное 0,5. Постройте графики функций SIN(X), COS(X) и СРЗНАЧ(Х) на одной диаграмме. Предварительно выделите исходный диапазон ячеек А2:D12. Чтобы графики отображались корректно, выберите тип диаграммы Точечная.
ЗАДАНИЕ 3. Выполнение тестового задания
Указания:
Для построения графика функции y=f(x) на отрезке [a, b] с шагом h откройте чистый лист рабочей книги или создайте его. Переименуйте его, введя название Тест. Создайте таблицу табулирования функции, для этого в ячейку А1 введите Х, в ячейку В1 У. В ячейки А2, А3 и т.д. введите значения аргумента Х на заданном отрезке с заданным шагом. В ячейку В2 введите формулу для расчета значения функции. Затем скопируйте её в ячейки В3, В4 и т.д.
Выделите диапазон ячеек и постройте график функции на том же рабочем листе.
на отрезке [-2, 2] с шагом 0,2.
на отрезке [0, 4] с шагом 0,2.
на отрезке [0, 2] с шагом 0,1.
на отрезке [-3, 3] с шагом 0,3.
на отрезке [2, 4] с шагом 0,1.
на отрезке [-1, 1] с шагом 0,1.
на отрезке [0, 5] с шагом 0,5.
на отрезке [0, 4] с шагом 0,2.
на отрезке [1, 4] с шагом 0,2.
на отрезке [1, 10] с шагом 0,5.
на отрезке [0, 2] с шагом 0,1.
на отрезке [0, 3] с шагом 0,15.
на отрезке [-2, 4] с шагом 0,4.
на отрезке [0, 10] с шагом 0,4. ЛАБОРАТОРНАЯ РАБОТА №4.
Логические функции в MS Excel
Цель работы: познакомиться с возможностями использования логических функций и вычислений проверки условий.
В состав встроенных функций MS Excel входят логические функции, что позволяет более широко использовать табличный процессор для решения задач, требующих проверки различных условий. Основной в этой группе является функция ЕСЛИ.
Синтаксис функции:
ЕСЛИ(лог_выражение, значение_1, значение_2)
В ячейке, где записана функция ЕСЛИ, сначала находится значение логического выражения. Если оно истинно, то в ячейку помещается значение_1, если ложно, то значение_2. В качестве логического выражения чаще всего используется оператор сравнения, который позволяет сравнивать два значения. Результатом сравнения является логическое значение ИСТИНА или ЛОЖЬ.
В качестве значения_1 или значения_2 можно использовать число, формулу или другую функцию Excel, в том числе и саму функцию ЕСЛИ. Функция ЕСЛИ допускает вложенность до 7 уровней, что позволяет конструировать проверку достаточно сложных условий.
Фактически функция ЕСЛИ позволяет реализовать ветвящуюся алгоритмическую структуру (рис.9).
Рис. 9.
В таблице 4 приведены операторы сравнения, которые могут использоваться при составлении логических выражений:
Таблица 4
| Оператор сравнения | Значение |
| = | Равно |
| > | Больше |
| < | Меньше |
| >= | Больше или равно |
| <= | Меньше или равно |
| <> | Не равно |
Рассмотрим примеры записи формул с логической функцией ЕСЛИ:
1) =ЕСЛИ(А1>3,10,20) – формула возвращает число 10, если значение в ячейке А1 больше 3, и 20 в противном случае,
2) =ЕСЛИ(А1>=3,"Зачет сдал","Зачет не сдал") – если оценка, записанная в ячейке А1, окажется больше или равна 3, то формула возвращает текстовую строку Зачет сдал или Зачет не сдал в противном случае.
3) =ЕСЛИ(А1="Россия","Москва","") – если в ячейке А1 записано слово Россия, то формула вернет текст Москва, в противном случае формула вернет пустую строку.
П
ри проверке сложных условий используются логические функции И и ИЛИ:
И(лог_выражение_1, лог_выражение_2,…),
ИЛИ(лог_выражение_1, лог_выражение_2,…).
Функции И и ИЛИ позволяют проверить одновременное выполнение нескольких условий, записанных в скобках. При этом функция И возвращает значение ИСТИНА, если все выражения в списке имеют значение ИСТИНА, а функция ИЛИ возвращает значение ИСТИНА, если хотя бы одно выражение имеет значение ИСТИНА.
Рассмотрим примеры функций И и ИЛИ:
1) И(A3=0, B2 > 0) – выражение будет истинным только если в ячейке A3 находится 0, а в ячейке B2 будет записано положительное число,
2) И(5 > 0, 2 = 3, 1 < 5) – выражение будет ложным, т.к. второе условие ложно,
3) ИЛИ(5 > 0, 2 = 3) – выражение всегда будет истинным, т.к. первое условие является истинным,
4) ИЛИ(7 > 10, 4 = 0) – выражение всегда ложно, т.к. ложны оба его условия.
Функции И и ИЛИ часто используются в составе функции ЕСЛИ:
1) ЕСЛИ(И(A1>0, B2<0), 1, 0) – выражение возвращает в ячейку значение 1 только тогда, когда выполнены сразу оба условия A1>0 и B2<0, а если хотя бы одно из них не выполняется, в ячейку заносится 0,
2) ЕСЛИ(ИЛИ(A1>0, B2<0), 1, 0) – выражение возвращает в ячейку значение 1, когда выполнено хотя бы одно их двух условий, значение 0 будет занесено только тогда, когда сразу оба условия не выполняются
ЗАДАНИЕ 1. Использование функции ЕСЛИ
Рассмотрим использование функции ЕСЛИ на примере нахождения бóльшего из двух чисел.
Откройте новую Рабочую книгу. Присвойте Листу1 имя Большее_число.
Заполните таблицу по образцу (см. рис. 10)
| | A | B | С |
| 1 | Число 1 | 3 | |
| 2 | Число 2 | 6 | |
| 3 | | | |
| 4 | Большее число: | | |
Рис. 10.
В ячейку В4 введите формулу =ЕСЛИ(В1>В2, В1, В2). Нажмите клавишу <Enter>. В ячейке В4 появится значение большего из двух чисел.
Поменяйте значения исходных чисел и проанализируйте результат.
Откройте чистый рабочий лист, назовите его Зачисление.
Создайте на листе таблицу по образцу:
-
А
В
С
D
E
F
1
Проходной балл:
13
2
Фамилия
Математика
Русский
Физика
Сумма
Зачислен
3
Самостоятельно заполните таблицу данными о фамилиях абитуриентах и оценках, полученных на трех вступительных экзаменах (Внимание! В таблице должно быть не менее 10 записей!).
В ячейку Е3 введите формулу для нахождения суммы баллов за три экзамена. Скопируйте формулу на нижележащие ячейки таблицы.
В ячейку F3 введите формулу, выдающую информацию о зачислении: если сумма баллов больше или равна проходному баллу и оценка за экзамен по математике 4 или 5, то абитуриент зачислен в учебное заведение, в противном случае ячейка останется пустой. Формула будет иметь вид: =ЕСЛИ(И(Е3>=$C$1,B3>3),"Зачислен","")).
В ячейку G2 введите название нового столбца таблицы: Условно зачислен.
В ячейку G3 введите формулу, выдающую информацию об условном зачислении абитуриента: если сумма баллов равна 12 и оценки по математике и физике больше 3 баллов, то абитуриент условно зачислен в учебное заведение, в противном случае ячейка должна остаться пустой.
Оформите таблицу и сохраните работу.
ЗАДАНИЕ 2. Создание таблицы логических операций
Откройте чистый рабочий лист, переименуйте его в Таблицы_лог.
Создайте таблицу по образцу:
Самостоятельно заполните пустые клетки таблицы формулами, используя логические функции И и ИЛИ.
Сравните полученные результаты с образцом:
Сохраните работу.
ЗАДАНИЕ 3. Расчет значения сложной функции
Рассчитайте значения функции
при Х = -2, -1,5, 0, 3,7.Откройте чистый рабочий лист, переименуйте его в Расчет.
В ячейку А1 введите текст «Х», в ячейку В1 текст «У».
В ячейки А2-А5 введите заданные значения Х.
В ячейку В2 введите формулу
= ЕСЛИ(А2<=0, ATAN(A2*A2-1), LN(A2*A2+1)). Нажмите клавишу Enter.
Выполните копирование формулы из ячейки В2 в ячейки В3-В5.
ЗАДАНИЕ 4. Выполнение тестового задания №1
Откройте чистый рабочий лист. Переименуйте его в Тест №1.
Рассчитайте значения функции при заданных значениях параметров, выбрав свой вариант из табл. 5:
Таблица 5
| № варианта | y=f(x) | Исходные данные |
| |  |  |
| |  |  |
| |  |  |
| |  |  |
| |  |  |
| |  |  |
| |  |  |
| |  |  |
| |  |  |
| |  |  |
| |  |  |
| |  |  |
| |  |  |
| |  |  |
ЗАДАНИЕ 5. Выполнение тестового задания №2
Постройте график функции y=f(x), предварительно составив таблицу значений функции на отрезке [-4, 8] с шагом h = 0,2. Функцию выберите из табл. 5 в соответствии со своим вариантом.
Таблицу и график разместите на одном рабочем листе, которому присвойте имя Тест №2.
ЛАБОРАТОРНАЯ РАБОТА №5.
Решение задач линейной алгебры в MS Excel
Цель работы: познакомиться с приемами решения задач линейной алгебры.
Типичными задачами линейной алгебры являются задачи, связанные с решением систем линейных уравнений. При этом приходится работать с массивами чисел. Для их обработки в Excel предусмотрен ряд математических функций:
МОПРЕД(массив) - функция для нахождения определителя квадратной матрицы. Здесь массив — числовой массив с равным количеством строк и столбцов,
МОБР(массив) - функция для нахождения обратной матрицы. Здесь массив — числовой массив с равным количеством строк и столбцов,
МУМНОЖ(массив1,массив2) - функция для нахождения произведения массива1 на массив2. Результатом является массив с таким же числом строк, как массив1, и с таким же числом столбцов, как массив2. При этом если обозначить буквой С результат произведения двух массивов А и В, то элементы массива С определяются по формуле
, где i — номер строки, j — номер столбца,ТРАНСП(массив) - функция для транспонирования массива ячеек на рабочем листе. Транспонирование массива заключается в том, что первая строка массива становится первым столбцом нового массива, вторая строка массива становится вторым столбцом нового массива и т.д.
Особенностью вычислений, связанных с массивами, является то, что результат вычисления распространяется в виде формулы на целый блок ячеек. Такие формулы называются формулами массива. При вводе формул массива необходимо соблюдать определенную последовательность действий:
Шаг 1. Сначала формула вводится в первую ячейку результирующего массива.
Шаг 2. Выделяется весь блок ячеек результирующего массива.
Шаг 3. Нажимается клавиша <F2> клавиатуры.
Шаг 4. Нажимается комбинация клавиш Ctrl+Shift+Enter. При этом формула распознается как формула массива и заключается в фигурные скобки { }.
Важно!
При работе с массивами чисел всегда контролируйте размерность результирующего массива!
ЗАДАНИЕ 1. Решение задач линейной алгебры
Рассмотрим решение некоторых задач линейной алгебры на простейших примерах. Пусть дана квадратная матрица 3-го порядка
. Найдите матрицу А-1, обратную к данной матрице А.
Найдите определитель матрицы А.
Проверьте, что найденная матрица А-1 действительно является обратной для матрицы А.
Откройте чистый рабочий лист. Переименуйте его в Обратная матрица.
Заполните рабочий лист исходными данными, как показано на рис. 11:
Рис. 11.
Установите курсор в ячейку Е2 и введите формулу =МОБР(А2:С4). После нажатия клавиши Enter в ячейке Е2 появится число 0,4.
Для получения обратной матрицы формулу в этом примере необходимо ввести как формулу массива. Для этого выделите диапазон ячеек Е2:G4, соответствующий обратной матрице (размерность матрицы А-1 очевидно будет такая же, как и у матрицы А). Нажмите клавишу <F2>, а затем комбинацию клавиш Ctrl+Shift+Enter. В результате ячейки Е2:G4 будут заполнены элементами обратной матрицы.
В ячейку А6 введите текст: Определитель(A).
Установите курсор в ячейку В6 и введите формулу = МОПРЕД(А2:С4). Нажмите клавишу Enter. В ячейке должно получиться значение определителя матрицы А, равное 5. (Внимание! В этом случае формулу для расчета определителя не нужно вводить как формулу массива, так как определитель является не массивом, а одним числом).
Для проверки правильности нахождения обратной матрицы вспомним, что должно выполняться условие: А А-1=Е, где Е единичная матрица того же порядка, что и матрица А. Установите курсор в ячейку Е6 и введите текст: Произведение матриц.
В ячейку Е7 введите формулу = МУМНОЖ(А2:С4, Е2:G4). Нажмите клавишу Enter.
Формулу в этом случае также вводим как формулу массива. В результате ячейки Е7:G9 будут заполнены элементами единичной матрицы (по главной диагонали будут записаны 1, остальные элементы будут равны 0). Если числа имеют много десятичных знаков, то выделите ячейки Е7:G9 и уменьшите разрядность либо при помощи вкладки Главная/ Число, при помощи кнопки Уменьшить разрядность
.