Краткие сведения из теории. 2 страница

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

Ввести матрицу (по одному элементу в каждую ячейку). В пустой ячейке ввести функцию МОПРЕД (категория Математические). В качестве аргумента функции ввести область, в которую введена матрица. Нажать ОК. Ответ: 1128.

Задание 6.21. Транспонировать матрицу из задания 6.20.

Выделить область для результата. Если исходная матрица имеет размерность m ´ n, то транспонированная матрица должна иметь размерность n ´ m (в данном случае 4 ´ 4). Вызвать функцию ТРАНСП (категория Ссылки и массивы). В качестве аргумента ввести диапазон, в котором размещена транспонируемая матрица, и нажать Ctrl + Shift + Enter.

В результате столбцы станут строками, а строки – столбцами.

Задание 6.22. Найти матрицу, обратную матрице из задания 6.20.

Выделить область для результата. Если исходная матрица имеет размерность m ´ n, то обратная матрица должна иметь размерность n ´ m. Вызвать функцию МОБР (категория Математические). В качестве аргумента ввести диапазон, в котором размещена обращаемая матрица, и нажать Ctrl + Shift + Enter.

Ответ:

Задание 6.23. Умножить матрицу из задания 6.20 на число 3.

Чтобы умножить матрицу на число, следует умножить на это число каждый ее элемент.

В пустой ячейке введите формулу, в которой элемент первой строки первого столбца матрицы умножается на 3, указывая не значение элемента, а его адрес. Например, если матрица введена в А11:А14, то формула будет такая: =3*А11. Так как матрица имеет порядок 4 ´ 4, скопируйте эту формулу маркером заполнения на три строки вниз и на три строки вправо.

Задание 6.24. Сложить матрицу из задания 6.20 и результат из задания 6.21.

Чтобы сложить две матрицы, следует сложить их соответствующие элементы.

В пустой ячейке введите формулу сложения левых верхних элементов матриц. Например, если матрица из задания 6.20 введена в А11:А14, а результат выполнения задания 6.21 выведен в А16:А19, то формула будет такая: = А11+А16. Вычислите остальные элементы, скопировав формулу маркером заполнения.

Задание 6.25. Умножить матрицу из задания 6.20 на вектор-столбец (0; 4; –4; 2).

Ввести вектор в столбец. Выделить область для результата. Если первый сомножитель имеет размерность m ´ n, а второй – n ´ k, то произведение должно иметь размерность m ´ k. Поэтому предварительно выделяют область m ´ k. Для рассматриваемого примера нужно выделить 4 ´ 1. Вызвать функцию МУМНОЖ (категория Математические). В диалоговом окне Мастера функций ввести: Массив 1: диапазон, в котором размещена первая матрица; Массив 2: диапазон, в котором размещен вектор. Нажать Ctrl + Shift + Enter. Ответ: (4; –36; 88; –4).

Задание 6.26. Умножить матрицу из задания 6.20 на результат из задания 6.22.

Для рассматриваемого примера нужно выделить область 4 ´ 4. Должна получиться единичная матрица, то есть такая, что на главной диагонали ее – единицы, а во всех остальных позициях – нули. Обратите внимание на погрешность вычислений. Измените формат чисел в ячейках результата как Числовой с одним десятичным знаком.

Задание 6.27. Решение системы n линейных уравнений.

Дана система n линейных уравнений с n неизвестными:

(6.1)

Решить систему (6.1) методом Крамера.

Метод Крамера состоит в том, что вычисляют D – определитель матрицы, составленной из коэффициентов системы. Затем вычисляют определители матриц D_i (i = 1, 2, …, n), составленных из первой матрицы заменой i-го столбца на столбец свободных членов. Тогда x_i = D_i /D, i = 1, 2, …, n.

Возьмите чистый лист и назовите его «Правило Крамера». Введите в области А1:D4 матрицу коэффициентов системы. В ячейке Е1 вычислите определитель матрицы (349,18). Введите в области А6:А9 столбец свободных членов. Скопируйте значения из области В1:D4 в область B6:D9. Вы получили матрицу, в которой первый столбец заменен столбцом свободных членов. Аналогично введите в область А11:D14 матрицу, полученную из исходной заменой второго столбца свободными членами; в области А16:D19 – матрицу, полученную из исходной заменой третьего столбца; в области А21:D24 – матрицу, полученную из исходной заменой четвертого столбца. Скопируйте формулу из Е1 в Е6 (331,16), Е11 (328,38), Е16 (-89,2), Е21 (49,8).

Введите в ячейку F1 формулу: = Е6/$E$1. Вы получили значение первого неизвестного x₁ = 0,948393. Скопируйте эту формулу в F6, F11, F16. Вы получите соответственно x₂ = 0,940432, x₃ = –0,25546, x₄ = 0,14262.

Задание 6.28. Решить систему уравнений (6.1) матричным методом.

Систему уравнений (6.1) можно записать в матричном виде: А ´ Х = В. Здесь А – матрица, составленная из коэффициентов системы, В – вектор-столбец свободных членов, Х – вектор-столбец неизвестных. Умножив слева обе части матричного уравнения на А^–1 (матрицу, обратную матрице А), получают Е ´ Х = А^–1 ´ В или Х = А^–1 ´ В. Тогда первый элемент вектора-произведения равен x₁, второй равен x₂, …, n-ый элемент равен x_n.

Возьмите чистый лист. Назовите его «Матричный метод». Введите в области А1:D4 матрицу коэффициентов системы (это можно сделать копированием информации с листа «Правило Крамера»). В области Е1:Е4 введите столбец свободных членов. В области А6:D9 вычислите обратную матрицу. В области А11:А14 найдите произведение массивов из области А6:D9 и Е1:Е4. Если вы все сделали правильно, то должны получиться такие же значения неизвестных, что и методом Крамера.

Задание 6.29. Решить систему линейных уравнений (6.1) итерационным методом.

Возьмите чистый лист. Назовите его «Итерационный метод».

1. Мысленно зарезервируйте столько ячеек, сколько имеется неизвестных (уравнений). Для (6.1) – это четыре ячейки А1:А4.

2. Введите в отдельные ячейки (В1:В4) левую часть каждого уравнения минус свободный член. При этом вместо неизвестных следует вводить в формулах ссылки на зарезервированные на предыдущем шаге ячейки (вместо x₁ – на первую, вместо x₂ – на вторую и т.д.). Для первого уравнения В1: =4*А1-2*А2-0,9*А3-А4-2.

3. Вызовите Сервис – Поиск решения.

4. В диалоговом окне Поиск решения введите:

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

- Значению: 0

- Изменяя ячейки: указать адреса, зарезервированных под решение ячеек А1:А4 (обвести их мышкой).

5. Щелкнуть область ограничений и нажать кнопку Добавить. В появившемся диалоговом окне ввести:

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

- Во втором окошке нажать стрелку ниспадающего меню и выбрать знак «равно».

- В окошке Ограничение ввести значение 0. Нажать ОК и вернуться в окно Поиска.

6. Нажать Выполнить. Если все действия выполнены правильно, то в зарезервированных на первом шаге ячейках должно появиться решение системы, совпадающее с полученным ранее.

Задание 6.30. Решить систему нелинейных уравнений (6.2) итерационным методом.

(6.2)

Ответ: {1,45604; –1,12427; –0,30099}. Назвать лист «Нелинейное уравнение».

Задание 6.31. Функции, заданные составным аналитическим выражением.

Пусть необходимо табулировать функцию:

y(x) = (6.3)

Для ввода этого выражения в Excel имеется функция ЕСЛИ (условная функция), имеющая три аргумента:

ЕСЛИ (логическая функция, формула 1, формула 2)

Когда логическая функция возвращает значение ИСТИНА, используется формула 1, когда она возвращает значение ИСТИНА, – формула 2.

В данном случае для ввода функции следует использовать выражение: ЕСЛИ(А1<>0; SIN(A1)/A1; 1)

Для ввода функции вида

y(x) = (6.4)

нужно использовать вложение функций: ЕСЛИ (А1>0; А1^2; ЕСЛИ(A1=0; 1; SQRT(1 – x))

Задание 6.32. Таким образом, основной формой представления данных в Excel являются таблицы. В том случае, если таблица содержит однородную информацию, ее можно рассматривать как базу данных. При этом в таблице не должно быть пустых строк.

Таблица 6.1.

Отформатированная таблица
№ зачетной книжки	ФИО	Личные данные
Фамилия	Имя	Возраст	Пол
	Иванов	Павел		м
	Петров	Петр		м
	Кузнецова	Анна		ж

Таблицу можно рассматривать как совокупность именованных столбцов. Именами столбцов в Таблице 6.1 являются «№ зачетной книжки», «Фамилия», «Имя», «Возраст», «Пол». Столбцы часто называют полями.

Строки таблицы называются записи. Записи не имеют имен. Для идентификации записей используются значения уникального поля, т.е. поля, принимающего неповторяющиеся значения. Такое поле называется ключевым. В Таблице 6.1 поле «№ зачетной книжки» является ключевым, так как номера зачетных книжек различных студентов не могут совпадать.

Таблицы, предназначенные для использования в отчете, а не для обработки, должны быть отформатированы эстетически привлекательно. Для этого следует изменить параметры шрифта, размеры таблицы, толщину границ, ориентацию заголовков и т.п. Используя слияние ячеек можно создать достаточно сложную по структуре заголовочную часть.

Создайте лист «Данные» и напечатайте на нем таблицу, аналогичную 6.1.

Задание 6.33. Динамический подсчет результатов. Создайте лист «Подсчет».

В диапазоне А1:Е11 введите таблицу результатов тестирования.

Таблица 6.2.

№ п/п	Фамилия	Балл 1	Балл 2	Балл 3	Балл 4
	Иванов

Заполните ячейки: первый столбец – порядковый номер, второй – фамилия тестируемого субъекта, остальные ячейки таблицы – произвольно, числами от 0 до 9. В таблице должно быть десять записей.

Выделите диапазон В2:Е2 с балами для первой фамилии. Присвойте выделенному диапазону имя Иванов. Для этого активизируйте пункты меню: Вставка – Имя – Добавить. Аналогичным образом поступите с остальными записями таблицы.

В ячейку А14 поместите список имен. Для этого выделите ячейку А14 и выберите пункты меню: Данные – Проверка. В качестве типа данных укажите список. Адресом источника выберите диапазон А2:А11, содержащий фамилии.

В ячейку С14 введите формулу = СРЗНАЧ(ДВССЫЛКА(А14)).

Раскройте список в ячейке А14 и выберите фамилию. В ячейке С14 появится среднее значение баллов, набранных тестируемым с выбранной фамилией.

Задание 6.34. Логические функции. Логическими называют функции, возвращающие значения ИСТИНА и ЛОЖЬ. Примером такой функции является функция отношения, имеющая вид:

<формула><символ отношения><формула>,

где символ отношения может принимать значения: = (равно); > (больше); < (меньше); ≥ (больше или равно); ≤ (меньше или равно); <> (не равно).

Если после вычисления функций получается верное числовое неравенство, функция отношения возвращает значение ИСТИНА, в противном случае – ЛОЖЬ.

В ячейку А1 введите значение 2, в А2 – значение 5. В ячейку В1 введите формулу неравенства =А1>A2 (возвращает ЛОЖЬ), а в В2: =А1<A2 (вернет значение ИСТИНА).

Можно создавать логические формулы, используя одноместную логическую операцию НЕ и двуместные операции И и ИЛИ.

Поскольку в Excel ИСТИНА записывается как 1, а ЛОЖЬ – как 0, то логические операции можно заменить арифметическими.

Пусть А и В логические формулы, т.е. возвращают логические значения. Тогда

Логические операции	Эквивалентные арифметические операции
А И В	А · В
А ИЛИ В	А + В – А · В
НЕ А	1 – А

Например, выражение =(А1>B1)*(C1<10) вернет 1 только в том случае, если А1 содержит значение большее, чем В1, а содержимое С1 больше 10.

Задание 6.35. Контроль правильности вводимых значений.

При вводе данных не исключены ошибки. Эти ошибки можно отчасти предотвратить, если воспользоваться имеющимися в Excel средствами контроля. При их использовании предполагается, что вводимые значения должны удовлетворять некоторым наперед заданным условиям (все вводимые значения должны быть натуральными; принадлежать диапазону от 1 до 6; представлять время суток и т.п.).

В Excel пользователю предоставляется возможность контроля данных по типу, допустимым пределам, заданному списку, длине текста. В качестве контролируемых типов данных используются: целое, действительное, дата, время. При использовании заданного списка ввод значений осуществляется выбором из него нужного элемента.

Кроме того, можно контролировать данные с использованием логических функций, возвращающих значения ИСТИНА или ЛОЖЬ. Вводимое значение подставляется в формулу. Если функция возвращает значение ИСТИНА, то значение пропускается, иначе появляется сообщение об ошибке.

Чтобы установить критерий достоверности необходимо проделать следующие действия:

1. Выделить блок ячеек.

2. Выбрать команду Данные – Проверка.

3. В диалоговом окне Проверка вводимых значений щелкнуть по вкладке Параметры.

4. В списке Тип данных выбрать требуемый тип.

5. Для установки допустимых пределов необходимо использовать список Значение.

6. Щелкнуть по вкладке Сообщение для ввода и установить текст, который будет появляться при выборе ячейки. Этот текст должен указывать на то, какие данные можно вводить в ячейку.

7. Щелкнуть по вкладке Сообщение об ошибке и задать, какое сообщение будет появляться при вводе недопустимых значений.

8. Чтобы не допустить ввод неправильного значения, выбрать опцию Останов. Обратите внимание, что возможны и другие, более мягкие виды контроля (какие?).

9. Нажать кнопку ОК.

Замечание 1. При выборе типа данных Список вводится список допустимых значений. Список может быть введен непосредственно в поле Источник с использованием точки с запятой в качестве разделителя элементов списка. Если список длинный, то он создается на рабочем листе и на него устанавливается ссылка или указывается его имя.

Замечание 2. Для ввода формулы необходимо выбрать из списка Тип данных значение Другой. В поле Формула ввести ее или задать ссылку на ячейку, содержащую формулу.

Задание 6.35.1. Создайте лист «Результат» и на нем таблицу РЕЗУЛЬТАТ с полями Фамилия, Имя, Отчество, Факультет, Курс, Группа, Предмет, Дата, Оценка.

Для поля «Имя» установите контроль по списку допустимых имен. Для поля «Факультет» установите контроль по списку имеющихся в институте факультетов. Для полей «Курс» и «Оценка» установите контроль по допустимым пределам (от 1 до 5 и от 1 до 10 соответственно). Для поля «Группа» установите контроль по длине текста. Установите подходящий контроль для полей «Предмет» и «Дата».

Применение формул проверки дает значительно большие возможности, чем позволяют стандартные средства. Рассмотрим несколько примеров. В каждом из них формула вводится непосредственно в диалоговое окно. Используемая в примерах ссылка А1 при привязке к конкретной таблице должна быть заменена ссылкой на левую верхнюю ячейку этой таблицы.

Выполните следующие задания. Составьте подходящие сообщения для ввода и ошибки в каждом случае. Проверьте работоспособность контроля путем ввода правильных и ложных данных.

Задание 6.35.2. Чтобы ячейки диапазона воспринимали только текст, введите формулу: =етекст(А1). Эта функция возвращает ЛОЖЬ, если в ячейку вводится последовательность символов, которую можно рассматривать как число или другое значение. Проверьте работу формулы.

Задание 6.35.3. Формула =А2>А1 позволяет ввести только те значения, которые больше значения в ячейке строкой выше. Проверьте работу формулы.

Задание 6.35.4. Чтобы в диапазоне А1:А20 были допустимы только уникальные значения, вводят формулу: =СЧЕТЕСЛИ($A$1:$A$20;A1)=1. Используемая функция возвращает число ячеек, содержимое которых совпадает с заданным в А1. Проверьте работу формулы.

Задание 6.35.5. Чтобы вводить тексты, которые начинаются с фиксированной буквы, используют функцию =ЛЕВСИМВ(А1)=”а”. Проверьте работу формулы.

Задание 6.35.6. Формула =(ЛЕВСИМВ(А1)=”a”)*(ДЛСТР(А1)=5)=1 обеспечивает ввод текста, начинающегося с буквы «а» и содержащей ровно 5 символов. Проверьте работу формулы.

Эта формула основана на том, что логические значения можно умножать, получая результат в соответствии с таблицей из задания 6.34.

Задание 6.36. Создайте с использованием методов контроля на основе функций на листе «Поставки» таблицу ПОСТАВКИ (код поставщика, код продукта, количество, цена, дата поставки). Предполагается, что

• значения в поле «код поставщика» не повторяются;
• поле «код продукта» имеет длину 6;
• «количество» – четное число;
• «цена» не больше заданной;
• «дата поставки» может быть только прошедшей датой (для текущей даты целесообразно использовать функцию СЕГОДНЯ из категории Дата и время).

Задание 6.37. Консолидация таблиц.

Однотипными таблицами называют таблицы, имеющие одинаковые спецификации, т.е. таблицы, столбцы (поля) которых имеют одинаковые имена и тип.

Операция консолидации позволяет объединить несколько однотипных таблиц в одну. Чаще всего эти таблицы содержат входную информацию.

При консолидации можно использовать следующие функции: сумма; количество значений; среднее; максимум; минимум; произведение и т.д.

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

Задание 6.37.1. Имеются результаты опроса, проведенного несколькими сотрудниками бюро изучения общественного мнения и представленного в виде соответствующего числа таблиц, содержащих данные о профессии, возрасте и доходах опрашиваемых.

Таблица 6.3. (данные, собранные первым сотрудником)

Профессия	Возраст	Доход
Рабочий
Бухгалтер

Таблица 6.4. (данные, собранные вторым сотрудником)

Профессия	Возраст	Доход
Рабочий
Инженер

Необходимо объединить эти сведения в общую таблицу; при этом должны быть вычислены средние значения количественных данных о каждой профессии, т.е. средний возраст и средний доход.

Создайте книгу КОНСОЛИДАЦИЯ1. На отдельных листах введите данные из таблиц 6.3 и 6.4. На листе «Итоги» командой Данные – Консолидация откройте диалоговое окно. Выберите функцию Среднее. Установив курсор в поле Ссылка, выделите данные таблицы 6.3, нажмите Добавить. Аналогично добавьте ссылку на диапазон с данными таблицы 6.4. Включите опции Подписи верхней строки, Значения левого столбца. Нажмите ОК.

В результате консолидации при использовании для подсчетов среднего значения должно получиться:

Таблица 6.5. Результаты консолидации

Профессия	Возраст	Доход
Рабочий
Бухгалтер
Инженер

На листе «Итоги_Связь» выполните те же действия, но при этом включите в диалоговом окне Консолидация опцию Создавать связи с исходными данными. Изменяя данные в таблицах 6.3 и 6.4, сравните результаты в консолидированных таблицах.

Задание 6.37.2. Создайте книгу КОНСОЛИДАЦИЯ2. На трех листах введите информацию из таблиц 6.6-6.8, назвав их соответственно «Отделение 1», «Отделение 2», «Отделение 3».

Таблица 6.6. Отделение 1

Показатели	I квартал	II квартал	II квартал	IV квартал
Оборотные средства
Основные средства
Доход
Прибыль
Число сотрудников
Фонд зарплаты
Инвестиции

Таблица 6.7. Отделение 2

Показатели	I квартал	II квартал	II квартал	IV квартал
Оборотные средства
Основные средства
Доход
Прибыль
Число сотрудников
Фонд зарплаты
Инвестиции

Таблица 6.8. Отделение 3

Показатели	I квартал	II квартал	II квартал	IV квартал
Оборотные средства
Основные средства
Доход
Прибыль
Число сотрудников
Фонд зарплаты
Инвестиции

Необходимо консолидировать информацию по всем отделениям без связи с исходными данными с использованием функций «Сумма», «Среднее», «Несмещенное отклонение», «Несмещенная дисперсия», «Количество чисел».

Консолидировать данные, используя функцию «Сумма» и включив Создавать связи с исходными данными.

Изменяя исходные данные, проследить, как изменяются итоговые данные.

Задание 6.38. Группировка, сортировка и подведение итогов.

Группой называется последовательность записей таблиц, расположенных на соседних местах и объединенных общим условием. Группировка – это разбиение таблицы данных (списка) на группы. Например, список студентов требуется разбить на части, включив в каждую студентов одного и того же факультета. Данные о банковских вкладах бывает желательно сгруппировать, объединив в одной группе все данные по определенному виду вклада (текущий счет, депозит, облигация и т.д.).

Часто группы записей требуется разбить на подгруппы, подгруппы разбить на еще более мелкие части и т.д. Например, данные о заключенных контрактах на поставку нужно разбить на группы записей по каждому поставщику. Записи, относящиеся к данному поставщику, необходимо разделить на подгруппы записей, содержащих данные о поставках определенного товара. Группы записей о контрактах, заключенных с данным поставщиком на поставку фиксированного товара, можно разбить на более мелкие части с включением в них поставок в определенный день.

Группировка записей осуществляется с помощью Данные – Сортировка. Возможно одновременное задание трех ключей сортировки. Если произвести сортировку последовательно по двум системам ключей, то список окажется отсортированным по всем используемым ключам. При этом ключи, используемые во второй раз, будут старше.

При группировке с каждым уровнем группировки следует связать свой ключ. Чем выше уровень группировки, тем старше ключ сортировки.

Задание 6.38.1. Создайте книгу СОРТИРОВКА. На листе «Поставки» создайте таблицу со структурой:

Таблица 6.9.

Поставщик

Получатель

Товар

Количество

Цена

Заполните таблицу подходящими по смыслу значениями, причем в каждом поле должно встречаться одно и то же значение, но не должно быть повторяющихся записей. Пусть записи таблицы «Поставки» необходимо разбить на группы, используя три уровня деления:

Поставщик;

Потребитель;

Товар.

Для группировки выберите сортировку по ключам «Поставщик», «Потребитель», «Товар».

Скопируйте таблицу на лист Поставки2 и сгруппируйте записи по принципу:

Товар;

Поставщик;

Потребитель.

Для каждой выделенной группы можно подвести итоги, используя команду Данные – Итоги. Итоги вычисляют по полям, принимающим количественные значения. Например, в данном случае по полям «Количество» или «Цена». Для подсчета итогов допускается использовать любую из групповых функций (сумма, среднее, количество, максимум, минимум, дисперсия, квадратичное отклонения).

После подведения итогов слева от адресной полосы с номерами строк появятся символы структуры. Щелкая по кнопкам структуры (плюс и минус), легко понять ее назначение.

Подведите итоги в отсортированном списке «Поставки». Щелкните по кнопке 2 и отсортируйте по полю «Цена» в порядке убывания. Раскройте список полностью.

Задание 6.38.2. Дана таблица результатов экзаменационной сессии. Необходимо группировать ее по значениям полей «Факультет» и «Курс», подвести итоги по полученным группам и подгруппам записей, скопировать итоговые таблицы на новые листы. Здесь имеются в виду не студенческие группы, а группы записей, содержащих одинаковое значение заданного поля, в данном случае поля «Факультет». Далее группы разбиваются на подгруппы записей, относящихся к одному и тому же курсу.

Для копирования итогов в свернутом виде следует выделить таблицу, вызвать команду Правка – Перейти – Выделить – Только видимые ячейки и далее копировать как обычно.

Таблица 6.9. Итоги сессии

№ зачетной книжки	ФИО	Курс	Факультет	Информатика	Математика	История	Сумма балов
	Афанасьева		экономический
	Абрамова		экономический
	Бодрова		экономический
	Гусева		экономический
	Гусев		экономический
	Кочешов		менеджмент
	Кузьмин		менеджмент
	Кузьмина		менеджмент
	Купцов		менеджмент
	Соловьев		экономический
	Хандусь		экономический
	Титова		экономический
	Давыдов		менеджмент
	Коптенко		менеджмент
	Алексеева		менеджмент
	Алиева		менеджмент
	Бобров		менеджмент
	Буссель		менеджмент
	Вдовин		менеджмент
	Веселова		менеджмент
	Лебедев		экономический
	Попова		экономический
	Попов		экономический
	Теслин		экономический

Задание 6.39. Фильтрация данных – отбор из строк таблицы только тех записей, которые удовлетворяют заданному логическому условию.