Лабораторная работа № 6. Работа с массивами. Матричные вычисления. Подбор параметра.

1. Запустите Microsoft Excel 2007.

2. Создайте книгу и сохраните ее под названием работа_6.xlsx

3. Переименуйте рабочий Лист 1 на «Массивы_1».

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

Сведения о формулах массива

Здесь будут рассмотрены формулы массива, а также объяснено, как вводить их, изменять, а также устранять связанные с ними проблемы.

Формулы массива позволяют выполнять сложные задачи, например следующие:

• подсчет числа знаков в диапазоне ячеек;

• суммирование только тех чисел, которые отвечают определенным условиям, например наименьших значений в диапазоне чисел, определенном верхней и нижней границами;

• суммирование всех n-ных значений в диапазоне значений.

Для ввода формул массива в рабочие книги используется сочетание клавиш CTRL+SHIFT+ENTER.

Краткое введение в массивы и формулы массива

Массив можно представить как набор элементов. В Excel эти элементы могут находиться в отдельной строке (в этом случае речь идет об одномерном горизонтальном массиве), в столбце (одномерный вертикальный массив) либо в нескольких строках и столбцах (двумерный массив). В Excel нельзя создавать трехмерные массивы или формулы массивов.

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

Далее рассмотрим примеры, как создавать формулы массива с одной или несколькими ячейками.

4. Введите в ячейку А1 число 3, в А2 – число 1, в В1 – число 4, в В2 – число 3.

5. Произведем умножение диапазона А1:В2 на число 5. Для этого выделите на рабочем листе область, например, D1:E2, такого же размера, как и массив-множимое.

6. В строку формулы введите формулу =А1:В2*5 и закончите ввод не как обычно, нажатием клавиши Enter, а нажатием клавиш Ctrl+Shift+Enter. Формула будет заключена в фигурные скобки ({ }), экземпляр формулы будет подставлен в каждую ячейку выделенного диапазона. В итоге формула будет выглядеть таким образом: {=А1:В2*5}.

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

7. Подсчитаем сумму массивов.

   1. Выделите диапазон G1:H2.

   2. В строке формулы введите формулу: =А1:В2+D1:E2 и нажмите сочетание клавиш Ctrl+Shift+Enter.

8. Произведем поэлементное произведение массивов.

   1. Выделите диапазон J1:K2.

   2. В строке формулы введите формулу: =А1:В2*D1:E2 и нажмите сочетание клавиш Ctrl+Shift+Enter.

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

   1. Выделите диапазон M1:N2.

   2. В строке формулы с помощью мастера функций введите формулу: =SIN(А1:В2) и нажмите сочетание клавиш Ctrl+Shift+Enter.

10. В диапазон А4:Е14 введите следующие данные:

    Продавец	Тип автомобиля	Число проданных единиц	Цена единицы	Итоги продаж
    Кравцов	седан	5	2200
    	купе	4	1800
    Игольников	седан	6	2300
    	купе	8	1700
    Мазурова	седан	3	2000
    	купе	1	1600
    Шевцова	седан	9	2150
    	купе	5	1950
    Полев	седан	6	2250
    	купе	8	2000

    1. Выделите диапазон Е5:Е14, в строку формулы введите формулу: =С5:С14*D5:D14 и нажмите сочетание клавиш Ctrl+Shift+Enter.

    2. В ячейке А16 введите текст Итоги продаж.

    3. В ячейке В16 введите формулу =СУММ(С5:С14*D5:D14). В этом случае будет выполнено перемножение значений массива (диапазон ячеек C5 – D14), а затем – при помощи функции СУММ – сложение полученных результатов. Общий итог продаж составит 111 800 рублей. В данном примере демонстрируется, какие широкие возможности предоставляет данный тип формул. Например, предположим, что у вас есть 15 000 строк с данными. Вы можете частично или полностью сложить их значения, создав формулу массива в отдельной ячейке.

Преимущества использования формул массива:

• Гибкость. Формула с одной ячейкой (в ячейке B16) полностью независима от формулы с несколькими ячейками (формула в ячейках E5 – E14). Можно выполнять с данными любые действия, например, изменить формулы в столбце E или вообще удалить этот столбец, и это не повлияет на формулу с одной ячейкой.

• Согласованность. В формуле с несколькими ячейками все ячейки данного массива содержат одну и ту же формулу. Чтобы убедиться в этом, щелкните несколько ячеек ниже ячейки E5, и вы увидите, что все они содержат одну и ту же формулу. Такая согласованность помогает обеспечить более высокую точность результатов

• Безопасность.  Компонент формулы массива с несколькими ячейками нельзя переписать. Например, щелкните ячейку E6 и нажмите клавишу DELETE. Вам придется выделить весь диапазон ячеек (E5 – E14) и изменить формулу для всех ячеек либо оставить массив без изменений. Помимо этого вы должны будете нажать сочетание клавиш CTRL+SHIFT+ENTER, чтобы подтвердить изменение формулы, что еще больше повышает уровень безопасности.

• Меньший размер файлов. Зачастую вместо нескольких промежуточных формул можно использовать одну формулу массива. Например, в созданной для этого упражнения рабочей книге для вычисления результатов в столбце E применяется всего лишь одна формула. Если бы вы прибегли к обычным формулам (таким как =C5*D5), вам понадобилось бы 10 разных формул для получения таких же результатов.

По большей части для формул массива используется синтаксис обычных формул. Они все начинаются со знака равенства и могут содержать встроенные функции Excel. Ключевое отличие заключается в том, что при использовании формулы массива для ввода такой формулы необходимо нажать сочетание клавиш CTRL+SHIFT+ENTER. При этом формула массива будет автоматически заключена в фигурные скобки. Если подставить скобки вручную, формула будет преобразована в текстовую строку и перестанет работать.

Еще одна особенность формул массива состоит в том, что функции массива являются разновидностью сокращенной записи. Например, функция с несколькими ячейками, которая была указана в предыдущем примере, является эквивалентом функций =С5*D5

=C6*D6 и т.д.

Формула с одной ячейкой, находящаяся в ячейке B16, содержит в кратком виде все эти операции умножения, а также арифметические действия, требуемые для сложения промежуточных итогов: =E5+E6+E7 и т.д.

При работе с формулами с несколькими ячейками требуется соблюдать следующие правила:

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

• Содержимое отдельной ячейки в формуле массива изменить нельзя.

• Формулу массива можно переместить или удалить только целиком. Другими словами, чтобы уменьшить формулу массива, сначала нужно удалить существующую формулу, а затем создать новую формулу с самого начала. Чтобы удалить формулу массива, выделите формулу целиком (например =C5:C14*D5:D14), нажмите клавишу DELETE, а затем – сочетание клавиш CTRL+SHIFT+ENTER.

• В формулу массива с несколькими строками нельзя вставить пустые строки или удалить строки из нее.

Иногда возникает необходимость увеличить диапазон формулы массива (не забывайте, что уменьшить диапазон формулы массива невозможно). Эта процедура не представляет сложности, однако при ее выполнении следует соблюдать правила, изложенные выше. Т.е. для увеличения диапазона формулы массива необходимо выполнить диапазон ячеек, содержащий текущую формулу массива (например, Е5:Е14), а также пустые ячейки, расположенные рядом с новыми данными (например, Е15:Е20; т.е. можно сказать, выделить диапазон Е5:Е20). После этого нажать клавишу F2, чтобы переключиться в режим правки, и в строке формулы произвести необходимые изменения (в данном случае изменить значения диапазона с С14 на С20 и с D14 на D20). Далее нажать сочетание клавиш CTRL+SHIFT+ENTER.

11. Переименуйте Лист 2 на «Массивы_2»

12. Скопируйте с рабочего листа Массивы_1 в диапазон А1:Е11 таблицу с подсчитанными итогами продаж.

13. Увеличение диапазона.

    1. Начиная с ячейки А12, вставьте дополнительные строки формул, представленные в таблице:

       Томин	седан	6	2 500
       	купе	7	1 900
       Вонг	седан	4	2 200
       	купе	3	2 000
       Янов	седан	8	2 300
       	купе	8	2 100

    2. Выделите диапазон ячеек, содержащий текущую формулу массива (E2:E11), а также пустые ячейки (E12:E17), расположенные рядом с новыми данными. Другими словами, выделите ячейки E2:E17.

    3. Нажмите клавишу F2, чтобы переключиться в режим правки.

    4. В строке формулы измените значение C12 на C17, измените значение D12 на значение D17, а затем нажмите сочетание клавиш CTRL+SHIFT+ВВОД. Формула в ячейках E2 – E11 будет обновлена, при этом экземпляры новой формулы будут вставлены в новые ячейки, E12 – E17.

Недостатки использования формул массива:

• Можно случайно забыть нажать сочетание клавиш CTRL+SHIFT+ENTER. Не забывайте нажимать это сочетание клавиш каждый раз при вводе или изменении формулы массива.

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

• Большие формулы массива могут замедлять вычисления на компьютерах с невысокой скоростью процессора или небольшой памятью.

Сведения о константах

Константы массива являются компонентами формул массива. Константы массива создаются путем ввода списка элементов с последующей вставкой фигурных скобок ({ }) вокруг списка, следующим образом:

={1,2,3,4,5}

Ранее мы подчеркивали необходимость нажимать сочетание клавиш CTRL+SHIFT+ENTER при создании формул массива. Поскольку константы массива являются составляющей формул массива, необходимо вручную вставить скобки вокруг этих констант. Затем нажмите сочетание клавиш CTRL+SHIFT+ENTER, чтобы ввести формулу целиком.

Если элементы списка разделяются запятыми, будет создан горизонтальный массив (строка). Если элементы разделяются точками с запятой, будет создан вертикальный массив (столбец). Чтобы создать двумерный массив, следует разделить элементы строк запятыми, а строки – точками с запятой.

Как и формулы массива, константы массива можно использовать с любыми встроенными функциями Excel.

14. Создание горизонтальной константы.

    1. Выделите ячейки от A20 до E20 включительно.

    2. В строке формул введите указанную ниже формулу, а затем

нажмите сочетание клавиш CTRL+SHIFT+ENTER:

={1,2,3,4,5}

В этом случае следует ввести открывающую и закрывающие фигурные скобки ({ }). Вы должны получить результат, представленный на рисунке.

15. Создание вертикальной константы.

    1. Выделите столбец из 5 ячеек.

    2. В строке формул введите указанную ниже формулу, а затем нажмите сочетание клавиш CTRL+SHIFT+ENTER:

={1;2;3;4;5}

Вы должны получить результат, представленный на рисунке.

16. Создание двумерного массива.

    1. Выделите блок ячеек из 4-х столбцов и 3-х строк.

    2. В строке формул введите указанную ниже формулу, а затем нажмите сочетание клавиш CTRL+SHIFT+ENTER:

={1,2,3,4;5,6,7,8;9,10,11,12}

Вы должны получить результат, представленный на рисунке.

17. Использование констант в формулах.

    1. Введите в диапазон, например, А30:Е30 следующие данные:

3 4 5 6 7

2. В ячейке A33 введите указанную ниже формулу, а затем нажмите сочетание клавиш CTRL+SHIFT+ENTER.

=СУММ(A30:E30*{1,2,3,4,5})

Формула, которой вы только что воспользовались, состоит из нескольких частей.

 Функция

 Сохраненный массив

 Оператор

 Константа массива

Последний элемент в скобках представляет собой константу массива: {1,2,3,4,5}. Следует учитывать, что в Excel константы массива не заключаются в скобки автоматически. Это необходимо сделать вручную. Кроме того, не забывайте, что после добавления константы в формулу массива требуется нажать сочетание клавиш CTRL+SHIFT+ ENTER, чтобы ввести эту формулу.

Поскольку в первую очередь выполняются операции с элементами, заключенными в скобки, далее будут использоваться значения, хранящиеся в книге ( диапазон A30:E30), и соответствующий оператор. На этом этапе формула выполнит умножение значений в сохраненном массиве на соответствующие значения в константе. Это эквивалент следующей формулы:

=СУММ(A30*1, B30*2, C30*3, D30*4, E30*5)

В последнюю очередь выполняется сложение значений при помощи функции СУММ, итоговое значение 85 отображается в ячейки A33.

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

=СУММ({3,4,5,6,7}*{1,2,3,4,5})

Для этого скопируйте функцию, выделите пустую ячейку в книге, вставьте формулу в строку формул, а затем нажмите сочетание клавиш CTRL+SHIFT+ ENTER. Вы получите тот же результат, что и в предыдущем упражнении, где использовалась формула массива =СУММ(A30:E30*{1,2,3,4,5}).

Элементы, которые можно использовать в константах

Константы массива могут содержать числа, текст, логические значения (такие как ИСТИНА и ЛОЖЬ), а также значения ошибок (например, #N/A). Можно использовать числа в целочисленном, десятичном или экспоненциальном формате. При включении текста необходимо заключить его в двойные кавычки (").

Константы массива не могут содержать дополнительные массивы, формулы или функции. Другими словами, они могут содержать только текст или числа, разделенные запятыми или точками с запятой. При вводе такой формулы, как {1,2,A1:D4} или {1,2,СУММ(Q2:Z8)}, выводится предупреждение. Кроме того, числовые значения не могут содержать знаки процента, знаки валюты, запятые или кавычки.

Именование констант массива

Возможно, лучший способ использовать константы массива – присвоить им имена. Именованные константы проще использовать, они позволяют частично скрыть сложность формул массива от начинающих пользователей. Чтобы присвоить имя константе массива, а затем использовать ее в качестве формулы, необходимо на вкладке Формулы в группе Присвоенные имена выберите команду Присвоить имя. В открывшемся диалоговом окне Присвоение имени в поле Имя ввести имя, например, Квартал1. В поле Объект ссылки необходимо ввести константу (не забудьте ввести скобки вручную), например:

={"Январь","Февраль","Март"}

Содержимое этого диалогового окна должно иметь следующий вид:

Далее нажмите кнопку ОК. В рабочем листе выделите строку из трех пустых ячеек. Введите указанную ниже формулу, а затем нажмите сочетание клавиш CTRL+SHIFT+ENTER:

=Квартал1

Вы получите следующий результат.

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

Устранение проблем, связанных с константами массива

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

• Возможно, некоторые элементы разделены неверным знаком. Если запятая или точка с запятой опущена или указана в неверном месте, создание константы может завершиться неудачей либо может быть выведено предупреждение.

• Возможно, выделен диапазон ячеек, не соответствующий числу элементов в константе. Например, если выделен столбец из шести ячеек для использования в константе с пятью ячейками, в пустой ячейке будет выведено значение ошибки «#N/A». Наоборот, если выделено слишком мало ячеек, значения, не имеющие соответствующей ячейки, будут пропущены.

Использование констант массива.

В следующих ниже примерах демонстрируется несколько способов, при помощи которых можно применять константы массива в формулах массива. В некоторых из примеров используется функция ТРАНСП, которая выполняет преобразование строк в столбцы и наоборот.

18. Умножение каждого из элементов массива

1. Выделите блок пустых ячеек из 4-х столбцов и 3-х строк.

2. Введите указанную ниже формулу, а затем нажмите сочетание клавиш CTRL+SHIFT+ENTER.

={1,2,3,4;5,6,7,8;9,10,11,12}*2

19. Возведение в квадрат элементов массива

1. Выделите блок пустых ячеек из 4-х столбцов и 3-х строк.

2. Введите указанную ниже формулу массива, а затем нажмите сочетание клавиш CTRL+SHIFT+ENTER.

={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

Можно также ввести следующую формулу массива, где используется оператор возведения в степень (^):

={1,2,3,4;5,6,7,8;9,10,11,12}^2

20. Пример 3. Транспонирование одномерной строки

1. Выделите столбец из 5 пустых ячеек.

2. Введите указанную ниже формулу, а затем нажмите сочетание клавиш CTRL+SHIFT+ENTER.

=ТРАНСП({1,2,3,4,5})

Даже если была введена горизонтальная константа массива, функция ТРАНСП преобразует константу массива в столбец.

21. Пример 4. Транспонирование одномерного столбца

1. Выделите строку из 5 пустых ячеек.

2. Введите указанную ниже формулу, а затем нажмите сочетание клавиш CTRL+SHIFT+ENTER.

=ТРАНСП({1;2;3;4;5})

Даже если была введена вертикальная константа массива, функция ТРАНСП преобразует константу массива в строку.

22. Пример 5. Транспонирование двумерного массива

1. Выделите блок ячеек из трех столбцов и четырех строк.

2. Введите следующую константу, а затем нажмите сочетание клавиш CTRL+SHIFT+ENTER.

=ТРАНСП({1,2,3,4;5,6,7,8;9,10,11,12})

Функция ТРАНСП преобразует каждую из строк в последовательность столбцов.

Создание константы массива на основе существующих значений

Для того, чтобы формула массива была заменена константой массива необходимо сначала в рабочем листе выделить диапазон, где записана формула массива (например, в рабочем листе «Массивы_2» записана формула массива =Массивы_1!А1:В2). Затем необходимо нажать клавишу F2, чтобы переключиться в режим правки, а затем нажать клавишу F9, чтобы преобразовать ссылки на ячейки в значения. Значения будут преобразованы в константы массива. Далее нужно нажать сочетание клавиш CTRL+SHIFT+ENTER, чтобы ввести константу массива в качестве формулы массива.

Формула массива = Массивы_1!А1:В2 будет заменена следующей константой массива:

={3,4;1,3}

Связь между листами «Массивы_1» и «Массивы_2» будет разорвана, а формула массива будет заменена константой массива.

Подсчет знаков в диапазоне ячеек

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

Предположим, на листе в диапазоне А1:А5 введены такие значения:

быстрый

бурая лиса

перепрыгнувший

ленивый

опытный пользователь

Введите указанную ниже формулу в ячейке А7, а затем нажмите сочетание клавиш CTRL+SHIFT+ ENTER:

=СУММ(ДЛСТР(C1:C5))

В ячейку А7 будет подставлено значение47.

В данном случае функция ДЛСТР возвращает длину каждой текстовой строки в каждой из ячеек диапазона. Затем функция СУММ складывает эти значения и выводит полученный результат в ячейке C7, которая содержит формулу.

23. Перейдите на Лист 3 и переименуйте его на «Матрицы»

Встроенные функции для работы с матрицами.

В Excel имеются следующие специальные функции для работы с матрицами:

МОБР	Обратная матрица
МОПРЕД	Определитель матрицы
МУМНОЖ	Матричное произведение двух матриц
ТРАНСП	Транспонированная матрица

Функция МОБР

Скрыть все

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

Синтаксис:

=МОБР(массив)

где массив   — числовой массив с равным количеством строк и столбцов.

Массив может быть задан как диапазон ячеек, например A1:C3 как массив констант, например {1;2;3: 4;5;6: 7;8;9} или как имя диапазона или массива.

Если какая-либо из ячеек в массиве пуста или содержит текст, функция МОБР возвращает значение ошибки #ЗНАЧ!.

Функция МОБР также возвращает значение ошибки #ЗНАЧ!, если число строк в массиве не равно числу столбцов.

Формулы, возвращающие массивы, должны быть введены как формулы массива.

Обратные матрицы, как и определители, обычно используются для решения систем уравнений с несколькими неизвестными. Произведение матрицы на ее обратную — это единичная матрица, т. е. квадратный массив, у которого диагональные элементы равны 1, а все остальные — 0.

В качестве примера вычисления обратной матрицы, рассмотрим массив из двух строк и двух столбцов A1:B2, который содержит буквы a, b, c и d, представляющие любые четыре числа. В таблице приведена обратная матрица для массива A1:B2.

	Столбец A	Столбец B
Строка 1	d/(a*d-b*c)	b/(b*c-a*d)
Строка 2	c/(b*c-a*d)	a/(a*d-b*c)

Функция МОБР производит вычисления с точностью до 16 значащих цифр, что может привести к незначительным ошибкам округления.

Некоторые квадратные матрицы не могут быть обращены: в таких случаях функция МОБР возвращает значение ошибки #ЧИСЛО!. Определитель такой матрицы равен 0.

Функция МОПРЕД

Скрыть все

Возвращает определитель матрицы (матрица хранится в массиве).

Синтаксис:

=МОПРЕД(массив)

где массив   — числовой массив с равным количеством строк и столбцов.

Массив может быть задан как интервал ячеек, например A1:C3, как массив констант, например {1;2;3:4;5;6:7;8;9}, как имя для интервала или массива.

Функция МОПРЕД возвращает значение ошибки #ЗНАЧ! в случаях:

• если какая-либо ячейка в массиве пуста или содержит текст;

• если количество строк в массиве не равно количеству столбцов.

Определитель матрицы — это число, вычисляемое на основе значений элементов массива. Для массива A1:C3, состоящего из трех строк и трех столбцов, определитель вычисляется следующим образом:

=МОПРЕД(A1:C3) равно A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*C1)

Определители матриц обычно используются при решении систем уравнений с несколькими неизвестными.

Функция МОПРЕД производит вычисления с точностью примерно 16 значащих цифр, что может в некоторых случаях приводить к незначительным ошибкам. Например, определитель сингулярной матрицы отличается от нуля на 1E-16.

Функция МУМНОЖ Скрыть все

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

Синтаксис:

=МУМНОЖ(массив1;массив2)

где массив1, массив2   — перемножаемые массивы.

Количество столбцов аргумента массив1 должно совпадать с количеством строк аргумента массив2; при этом оба массива должны содержать только числа. Массив1 и массив2 могут быть заданы как интервалы, массивы констант или ссылки.

Функция МУМНОЖ возвращает значение ошибки #ЗНАЧ! в следующих случаях:

• если какая-либо ячейка пуста или содержит текст;

• если число столбцов в аргументе «массив1» отличается от числа строк в аргументе «массив2».

Массив a, который является произведением двух массивов b и c, определяется следующим образом:

где i — номер строки, а j — номер столбца.

Формулы, которые возвращают массивы, должны быть введены как формулы массива.

Функция ТРАНСП Скрыть все

Возвращает вертикальный диапазон ячеек в виде горизонтального и наоборот. Эту функцию необходимо вводить как формулу массива (формулы массива заключены в фигурные скобки { } и вводятся нажатием клавиш CTRL+SHIFT+ENTER) в диапазон, который имеет столько же строк и столбцов, сколько столбцов и строк имеет аргумент массив.

Функция ТРАНСП используется для изменения ориентации массива или диапазона на листе с вертикальной на горизонтальную и наоборот.

Синтаксис:

=ТРАНСП(массив)

где массив – массив (диапазон ячеек) на листе, который нужно транспонировать. Транспонирование массива заключается в том, что первая строка массива становится первым столбцом нового массива, вторая — вторым столбцом и т. д.

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

Решим в качестве примера систему линейных уравнений с двумя неизвестными.

24. Введите в диапазон А1:В2 матрицу коэффициентов: в ячейку А1 введите число 2, в А2 – число 4, в В1 – число 1, в В2 – число 5.

25. Введите в диапазон D1:D2 свободные члены: в D1 – число 3, в D2 – число 2.

Для решения этой задачи вспомним, что решение линейной системы АХ=В, где А – матрица коэффициентов, В – столбец (вектор) свободных членов, Х – столбец (вектор) неизвестных, имеет вид Х=А^-1В, где А^-1 – матрица, обратная по отношению к А.

26. Выделите диапазон F1:F2.

27. В строку формулы введите формулу: =МУМНОЖ(МОБР(A1:B2); D1:D2) и нажмите сочетание клавиш CTRL+SHIFT+ENTER.

28. Решить систему линейных уравнений А²Х=В, где

А= , В=

1. Введите в диапазон ячеек А4:В5 элементы матрицы А, а в диапазон ячейки D4:D5 – элементы столбца свободных членов В.

2. Выделите диапазон F4:F5 и введите следующую формулу:

=МУМНОЖ(МОБР(МУМНОЖ(А4:В5; А4:В5)); D4:D5)

и нажмите сочетание клавиш CTRL+SHIFT+ENTER.

29. Пример вычисления квадратичной формулы z=X^TAX, где А – квадратичная матрица, введенная в диапазон А4:В5, Х – вектор, введенный в диапазон D4:D5, а символ (^T) обозначает операцию транспонирования.

    1. Для вычисления z выделите ячейку H4 и введите следующую формулу: =МУМНОЖ(МУМНОЖ(ТРАНСП(D4:D5); А4:В5); D4:D5)

и нажмите сочетание клавиш CTRL+SHIFT+ENTER.

Хотя результатом этой формулы является число, не забудьте для ее ввода нажать клавиши CTRL+SHIFT+ENTER. Если вы этого не сделаете, в ячейке Н4 появится сообщение #ЗНАЧ!.

30. Вычислить значение квадратичной формы z=Y^TA^TAY, где

,

1. Введите в диапазон А8:В9 элементы матрица А, а в диапазон D8:D9 – элементы столбца Y.

2. Для вычисления квадратичной формы введите в ячейку Н8 следующую формулу:

=МУМНОЖ(ТРАНСП(D8:D9); МУМНОЖ(ТРАНСП(А8:В9); МУМНОЖ(А8:В9; D8:D9)))

и нажмите сочетание клавиш CTRL+SHIFT+ENTER.

Пошаговое решение системы линейных уравнений методом Гаусса.

Хорошим упражнением по работе с массивами является пошаговое программирование на рабочем листе решения системы линейных уравнений методом Гаусса.

31. Решить методом Гаусса следующую систему линейных уравнений:

2х₁+3х₂+7х₃+6х₄=1

3х₁+5х₂+3х₃+ х₄=3

5х₁+3х₂+ х₃+3х₄=4

3х₁+3х₂+ х₃+6х₄=5

1. В диапазон А33:D36 введите коэффициенты матрицы, а в диапазон Е33:Е36 – вектор свободных членов.

2. Содержимое ячеек А33:Е33 скопируйте в ячейки А38:Е38, А43:Е43, А48:Е48.

3. В диапазон ячеек А39:Е39 введите формулу: =А34:Е34-$A$33:$E$33*(A34/$A$33) и нажмите сочетание клавиш CTRL+SHIFT+ENTER. Эта формула обращает в нуль коэффициент при х₁ во втором уравнении системы.

4. Выделите диапазон А39:Е39 и протащите маркер заполнения этого диапазона так, чтобы заполнить диапазон А39:Е41. Это обратит в нуль коэффициент при х₁ в третьем и четвертом уравнениях системы.

5. Скопируйте только значения из диапазона А39:Е39 в диапазоны А44:Е44 и А49:Е49.

Для копирования значений без формул воспользуйтесь с помощью контекстного меню командой Специальная вставка и в открывшемся диалоговом окне в группе Вставить установите переключатель в положение Значения (см. рис.)

6. В диапазон А45:Е45 введите формулу: =А40:Е40-$A$39:$E$39*(В40/$В$39) и нажмите сочетание клавиш CTRL+SHIFT+ENTER.

7. Выделите диапазон А45:Е45 и протащите маркер заполнения этого диапазона так, чтобы заполнить диапазон А45:Е46. Это обратит в нуль коэффициент при х₂ в третьем и четвертом уравнениях системы.

8. Скопируйте значения из диапазона ячеек А45:Е45 в диапазон А50:Е50. В диапазон А51:Е51 введите формулу: =А46:Е46-$A$45:$E$45*(С46/$С$45) и нажмите сочетание клавиш CTRL+SHIFT+ENTER. Эта формула обращает в нуль коэффициент при х₃ четвертого уравнения системы.

Прямая прогонка метода Гаусса завершена.

9. Обратная прогонка заключается в вводе в диапазоны G36:K36, G35:K35, G34:K34 и G33:K33, соответственно, следующих формул:

=А51:Е51/D51

=(A50:E50-G36:K36*D50)/C50

=(A49:E49-G36:K36*D49-G35:K35*C49)/B49

=(A48:E48-G36:K36*D48-G35:K35*C48-G34:K34*B48)/A48

В конце каждой формулы не забывайте нажимать сочетание клавиш CTRL+SHIFT+ENTER.

В диапазоне К33:К36 получено решение системы.

Нахождение корней уравнения

Рассмотрим пример нахождения всех корней уравнения х³-0,01х²-0,7044х+0,139104=0

Отметим, что у полинома третьей степени имеется не более трех вещественных корней. Для нахождения корней их предварительно нужно локализовать. С этой целью необходимо построить график функции или ее протабулировать. Например, протабулируем наш полином на отрезке [-1; 1] с шагом 0,2.

32. Добавьте в рабочую книгу новый лист и назовите его «Подбор параметра»

    1. Введите в ячейку А1 название х, а в ячейку В1 – название у.В ячейку А2 введите число -1 и заполните диапазон А2:А12 до числа 1 с шагом 0,2.

    2. Протабулируем полином на отрезке [-1; 1]. Для этого введите в ячейку В2 следующую формулу: =А2^3-0.01*A2^2-0.7044*A2+0.139104 и с помощью маркера автозаполнения заполните диапазон В2:В12.

Из рис. видно, что полином меняет знак на интервалах [-1; -0,8], [0,2; 0,4] и [0,6; 0,8]. Это означает, что на каждом из них имеется корень данного полинома. Поскольку полином третьей степени имеет не более трех действительных корней, значит мы локализовали все его корни.

Найдем корни полинома методом последовательных приближений с помощью на вкладке Данные в группе Средства обработки данных выберите команду Анализ условия, а затем выберите в списке пункт Подбор параметра. Относительная погрешность вычислений и предельное число итераций задаются на вкладке Формулы в группе Вычисление нажать кнопку Параметры вычислений.

В качестве начальных значений приближений к корням можно взять любые точки из отрезков локализации корней.

3. Перейдите на вкладку Формулы в группе Вычисление и нажмите кнопку Параметры вычислений. Задайте относительную погрешность и предельное число итераций, равными 0,00001 и 1000, соответственно.

4. В качестве начальных значений приближений к корням возьмем любые точки из отрезков локализации корней: -0,9, 0,3 и 0,7. Введите эти точки в диапазон С2:С4. А в С1 введите название «Приближение».

5. В ячейку D1 введите название «Значение функции», а в D2 введите следующую формулу: =С2^3-0,01*C2^2-0,7044*C2+0,139104

Выделите эту ячейку и с помощью маркера автозаполнения протащите введенную в нее формулу на диапазон D2:D4. Таким образом, в ячейках D2:D4 вычисляются значения полинома при значениях аргумента, введенного в ячейки С2:С4.

6. Перейдите на вкладку Данные в группе Средства обработки данных выберите команду Анализ условия, а затем выберите в списке пункт Подбор параметра. Появившееся диалоговое окно заполните следующим образом: в поле Установить в ячейке нужно указать ячейку, в которой необходимо подобрать конкретное значение, которое указывается в поле Значение, а в поле Изменяя значение ячейки - выбрать ячейку, где нужно подобрать значение. В нашем случае это будет выглядеть таким образом: в поле Установить в ячейке введем D2, в поле Значение вводим 0, а в поле Изменяя значение ячейки введем С2.

Рис. Диалоговое окно Подбор параметра

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

В поле Значение указывается правая часть уравнения.

В поле Изменяя значение ячейки указывается ссылка на ячейку, отведенную под переменную.

Вводить ссылки на ячейки в поля диалогового окна Подбор параметра удобнее не с клавиатуры, а щелчком на соответствующей ячейке. При этом Excel автоматически будет превращать их в абсолютные ссылки (в нашем примере $D$2 и $C$2).

После нажатия ОК средство подбора параметров находит приближенное значение корня, которое помещает в ячейку, которая была указана в поле Изменяя значение ячейки (в нашем случае это ячейка С2). В нашем примере оно равно -0,919999. Как выглядит диалоговое окно Результат подбора параметра после успешного завершения поиска решения, показано на рисунке.

Рис. Диалоговое окно Результат подбора параметра

7. После того, как средством подбора параметра будет подобрано решение, в диалоговом окне Результат подбора параметра нажмите ОК. В результате в ячейке будет подобрано приближенное значение корня, а в ячейке D2 будет установлено значение 0 (либо приближенное к 0).

8. Аналогично в ячейках С3 и С4 находим два оставшихся корня. Они равны 0,20999 и 0,71999.

Задание.