Мобр в excel как использовать

Решение системы уравнений в Excel

Возможно вы слышали о нобелевском лауреате, психологе и исследователе по имени Дэниель Канеман. Канеман занимался наукой, которую называют термином «поведенческая экономика», т.е. изучал реакции, поведение и суждения людей в типовых жизненных (и экономических) ситуациях и условиях неопределенности.

В его книге, которая называется «Думай медленно — решай быстро» (очень рекомендую, кстати) в качестве одного из примеров когнитивных искажений — несознательной автоматической реакции — приводится следующая задача:

Бейсбольная бита и мяч стоят вместе 1 доллар 10 центов.
Бита дороже мяча на 1 доллар.
Сколько стоит мяч?

Подозреваю, что вашей первой рефлекторной мыслью, скорее всего, будет «10 центов!» 🙂 Но весьма скоро, я уверен, вы сообразите, что на самом деле всё не так примитивно и для получения ответа нужно решить простую систему уравнений (здесь b — это бита, а m — это мяч):

Конечно можно «тряхнуть стариной» и решить всё вручную на бумажке через подстановку переменных — как-то так:

Но, во-первых, на практике уравнения могут быть сложнее и переменных может оказаться сильно больше двух и, во-вторых, у нас с вами есть Microsoft Excel — универсальный мега-инструмент, величайшее изобретение человечества. Так что давайте-ка лучше разберём как решить нашу задачу с его помощью.

Способ 1. Матричные функции МУМНОЖ и МОБР

Само собой, изобретать велосипед тут не надо — прогрессивное человечество в лице математиков давным-давно придумало кучу способов для решения подобных задач. В частности, если уравнения в нашей системе линейные (т.е. не используют степени, логарифмы, тригонометрические функции типа sin, cos и т.д.), то можно использовать метод Крамера.

Сначала записываем числовые коэффициенты, стоящие перед нашими переменными в виде матрицы (в нашем случае — размером 2х2, в общем случае — может быть и больше).

Затем находим для неё так называемую обратную матрицу , т.е. матрицу, при умножении которой на исходную матрицу коэффициентов получается единица. В Excel это легко сделать с помощью стандартной математической функции МОБР (MINVERSE) :

Здесь важно отметить, что если у вас свежая версия Excel 2021 или Excel 365, то достаточно ввести эту функцию обычным образом в первую ячейку (G7) — сразу получится динамический массив с обратной матрицей 2х2. Если же у вас более старая версия Excel, то эту функцию нужно обязательно вводить как формулу массива, а именно:

1. Выделить диапазон для результатов — G7:H8
2. Ввести функцию =МОБР(B7:C8) в строку формул
3. Нажать на клавиатуре сочетание клавиш Ctrl + Shift + Enter

Замечательное свойство обратной матрицы состоит в том, что если умножить её на значения правых частей наших уравнений (свободные члены), то мы получим значения переменных, при которых левые и правые части уравнений будут равны, т.е. решения нашей задачи. Выполнить такое матричное умножение можно с помощью ещё одной стандартной экселевской функции МУМНОЖ (MMULT) :

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

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

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

Способ 2. Подбор надстройкой «Поиск решения» (Solver)

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

В Microsoft Excel некоторые из этих методов реализованы в стандартной надстройке Поиск решения (Solver) . Её можно подключить через Файл — Параметры — Надстройки — Перейти (File — Options — Add-ins — Go to) или на вкладке Разработчик — Надстройки (Developer — Add-ins) .

Давайте рассмотрим её использование на следующей задаче. Предположим, что нам с вами нужно решить вот такую систему из двух нелинейных уравнений:

Подготавливаем основу для оптимизации в Excel:

• В жёлтых ячейках C9:C10 лежат текущие значения наших переменных, которые и будут подбираться в процессе оптимизации. В качестве стартовых можно взять любые значения, например, нули или единицы — роли не играет. Для удобства, кстати, этим ячейкам можно дать имена, назвав их именами переменных x и y, — для этого выделите диапазон C9:C10 и выберите команду Формулы — Создать из выделенного — Слева (Formulas — Create from selection — Left column) .
• В зелёных ячейках E9:E10 введены наши уравнения с использованием либо прямых ссылок на жёлтые ячейки переменных, либо созданных имён (так нагляднее). В результате мы видим, чему равны наши уравнения при текущих значениях переменных.
• В синих ячейках F9:F10 введены значения правых частей наших уравнений, к которым мы должны стремиться.

Теперь запускаем нашу надстройку на вкладке Данные — Поиск решения (Data — Solver) и вводим в появившемся диалоговом окне следующие параметры:

• Оптимизировать целевую функцию (Set target cell) — любая из двух наших зелёных ячеек с уравнениями, например E9.
• Изменяя ячейки переменных (By changing cells) — жёлтые ячейки с текущими значениями переменных, которыми мы «играем».
• Добавляем ограничение с помощью кнопки Добавить (Add) и задаём равенство левой и правой части наших уравнений, т.е. зелёного и голубого диапазонов.
• В качестве метода решения выбираем Поиск решения нелинейных задач методом ОПГ, т.к. уравнения у нас нелинейные. Для линейных можно смело выбирать симплекс-метод.

После нажатия на кнопку Найти решение (Solve) через пару мгновений (или не пару — это зависит от сложности задачи) мы должны увидеть окно с результатами. Если решение найдено, то в жёлтых ячейках отобразятся подобранные значения наших переменных:

Обратите внимание, что поскольку мы здесь используем итерационные, а не аналитические методы, то зеленые ячейки не совсем равны голубым, т.е. найденное решение не абсолютно точно. На практике, конечно же, такой точности вполне достаточно для большинства задач, и если необходимо, её можно настроить, вернувшись в окно Поиск решения и нажав кнопку Параметры (Options) .

Основные сведения об использовании функций мобр, мопред, мумнож

Понятие матрицы и основанный на нем раздел математики – матричная алгебра – имеют чрезвычайно важное значение для экономистов. Объясняется это тем, что значительная часть математических моделей экономических объектов и процессов записывается в матричной форме.

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

1. Функция МОБР возвращает обратную матрицу для мат­рицы, хранящейся в массиве.

Массив – это числовой массив с равным количеством строк и столбцов.

Массив может быть задан как диапазон ячеек, например А1:С3, или как имя диапазона или массива.

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

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

2. Функция МОПРЕД возвращает определитель матрицы (мат­рица хранится в массиве).

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

где массив – см. п. 1.

3. Функция МУМНОЖ возвращает произведение матриц (мат­рицы хранятся в массивах). Результатом является массив с таким же числом строк, как массив1, и с таким же числом столбцов, как массив2.

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

Массив1, массив2 – это перемножаемые массивы.

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

Массив1 и массив2 могут быть заданы как интервалы, массивы констант или ссылки.

Если хотя бы одна ячейка в аргументах пуста, или если число столбцов в аргументе массив1 отличается от числа строк в аргументе массив2, то функция МУМНОЖ возвращает значение ошибки #ЗНАЧ!.

Основные сведения о макросах

В EXCEL VBA-макрос может быть двух типов: подпрограммой и функцией.

Макрос-подпрограмма может быть выполнена любым пользователем, либо другим макросом. Она начинается ключевым словом SUB и заканчивается END SUB. Строки, заключенные между этими операторами, составляют текст макроса.

С помощью макрорекордера можно записать только макрос-подпрограмму.

Макрорекордер записывает действия пользователя, которые можно потом многократно воспроизводить. Текст макроса может быть записан как с абсолютными, так и с относительными ссылками.

Содержание лабораторной работы

Выполнение данной лабораторной работы включает в себя:

использование встроенных математических функций МОБР, МОПРЕД и МУМНОЖ для вычисления обратной матрицы, определителя матрицы и перемножения матриц;

запись указанных последовательностей действий макрорекордером в виде VBA-макросов с абсолютными и относительными ссылками;

запуск созданных макросов с помощью кнопок и меню.

Выполнение лабораторной работы

Использование функций МОБР, МОПРЕД и МУМНОЖ

1. Найдите матрицу, обратную данной:

введите элементы матрицы в диапазон ячеек А1:С3;

для получения обратной матрицы выделите несмежный диапазон ячеек такого же размера, например E1:G3, и введите формулу массива <=МОБР(А1:С3)>. Для заключения формулы в фигурные скобки после ввода формулы нажмите клавиши CTRL+Shift+Enter.

2. Вычислите определитель матрицы А. Для этого выделите любую свободную ячейку, например А5, и введите формулу

3. Вычислите произведение матрицы А на матрицу В, где

; .

введите элементы матрицы А в диапазон ячеек А10:С11;

введите элементы матрицы В в диапазон ячеек А13:С15;

выделите диапазон ячеек с таким же числом строк, как массив А, и с таким же числом столбцов, как массив В, например, E10:G11 и введите формулу

нажмите CTRL+Shift+Enter.

4. Решите систему линейных уравнений с 3-мя неизвестными

(1)

методом обратной матрицы.

; (2)

; .

Решение системы (1) в матричной форме имеет вид АХ = В,

где: А – матрица коэффициентов;

Х – столбец неизвестных;

В – столбец свободных членов.

При условии, что квадратная матрица (2) системы (1) невырожденная, т.е. ее определитель А  0, существует обратная матрица А. Тогда решением системы методом обратной матрицы будет матрица-столбец X = AB. Найдем это решение. Для этого:

Найдем определитель А = 5 (см. п. 2). Для этого активизируем новый рабочий лист и введем элементы матрицы коэффициентов А в диапазон ячеек А1:С3. Выделим любую свободную ячейку, например А5, и введем формулу

Так как А  0, то матрица А – невырожденная, и существует обратная матрица А. Найдем обратную матрицу. Для этого выделим несмежный диапазон ячеек такого же размера, что и матрица А, например E1:G3, и введем формулу массива <=МОБР(А1:С3)>.

Найдем решение системы в виде матрицы-столбца

X = AB.. Для этого введем элементы матрицы В в диапазон ячеек E6:E8, выделим диапазон ячеек с таким же числом строк, как массив А, и с таким же числом столбцов, как массив В, например, G6:G8 и введем формулу массива

,

т.е. решение системы (4; 2; 1).

Запись макросов с помощью макрорекордера

5. Активизируйте новый рабочий лист.

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

в ячейки А1:А12 введите: January, February, March, April, May, June, July, August, September, October, November, December;

выделите на листе список элементов, которые требуется включить в список автозаполнения (диапазон A1:A12);

щелкните значок Кнопка Microsoft Office , а затем щелкните Параметры Excel;

выберите Основные, и затем в группе Основные параметры для работы в Excel в строке Создавать списки для сортировки и заполнения нажмите кнопку Изменить списки;

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

два раза нажмите кнопку ОК.

7. Для создания макросов с помощью макрорекордера необходимо:

Если вкладка Разработчик недоступна, выполните следующие действия для ее отображения:

щелкните значок Кнопка Microsoft Office , а затем щелкните Параметры Excel;

в группе Основные параметры работы с Excel установите флажок Показывать вкладку «Разработчик» на ленте, а затем нажмите кнопку ОК.

Для установки уровня безопасности, временно разрешающего выполнение всех макросов, выполните следующие действия:

на вкладке Разработчик в группе Код нажмите кнопку Безопасность макросов;

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

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

8. Запишите макрос в режиме с абсолютными ссылками. Для этого:

на вкладке Разработчик в группе Код нажмите кнопку Запись макроса;

в поле Имя макроса введите имя макроса (по умолчанию Макрос1);

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

в списке Сохранить в выберите книгу, в которой необходимо сохранить макрос (по умолчанию Эта книга);

введите описание макроса в поле Описание;

для начала записи макроса нажмите кнопку ОК;

введите в ячейку C1 слово January, затем создайте ряд (установите курсор на черный квадратик в правом нижнем углу активной ячейки C1 и протяните его, не отпуская кнопку мыши, до ячейки C12);

выделите сформированный ряд и задайте розовый цвет для выделенных ячеек (на вкладке Главная в группе Шрифт);

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

Совет. Можно также нажать кнопку Остановить запись слева от строки состояния.

9. Просмотрите последовательность команд Visual Basic, записанную макрорекордером. Для этого на вкладке Разработчик в группе Код нажмите кнопку Макросы, в диалоговом окне Макрос выделите имя макроса (Макрос1) и нажмите кнопку Изменить. По окончании просмотра программы, записанной макрорекордером, вернитесь в экран Microsoft Excel щелчком по кнопке панели задач.

10. Выполните макрос. Для этого:

активизируйте новый рабочий лист;

на вкладке Разработчик в группе Код нажмите кнопку Макросы, в диалоговом окне Макрос выделите имя макроса (Макрос1) и нажмите кнопку Выполнить;

11. Очистите область рабочего листа, нажав на кнопку Выделить все на пересечении заголовков строк и заголовков столбцов, затем на кнопку Delete на клавиатуре и на кнопку Нет заливки пиктографического меню Цвет заливки на вкладке Главная в группе Шрифт.

12. Запишите новый макрос в режиме с относительными ссылками. Для этого:

на вкладке Разработчик в группе Код нажмите кнопку Относительные ссылки, а затем кнопку Запись макроса;

в поле Имя макроса введите имя макроса (по умолчанию Макрос2) и нажмите кнопку ОК;

введите в активную в данный момент ячейку листа слово January, затем создайте ряд (установите курсор на черный квадратик в правом нижнем углу активной ячейки и протяните его, не отпуская кнопку мыши, на 11 ячеек вниз);

выделите сформированный ряд и задайте голубой цвет для выделенных ячеек;

на вкладке Разработчик в группе Код нажмите кнопку Остановить запись и отожмите кнопку Относительные ссылки.

13. Очистите область рабочего листа.

14. Выполните второй макрос. Для этого:

выделите произвольную ячейку;

на вкладке Разработчик в группе Код нажмите кнопку Макросы, в диалоговом окне Макрос выделите имя макроса (Макрос2) и нажмите кнопку Выполнить;

15. Сравните тексты программ Макрос1 и Макрос2, расположенные в Модуле1. Для этого на вкладке Разработчик в группе Код нажмите кнопку Макросы, в диалоговом окне Макрос выделите имя макроса (Макрос1 или Макрос2) и нажмите кнопку Изменить. По окончании просмотра программ, записанных макрорекордером, вернитесь в экран Microsoft Excel щелчком по кнопке панели задач.

16. Запишите самостоятельно новый макрос (Макрос3), очи­щаю­щий области рабочего листа, занятые результатами работы макросов, и проверьте его выполнение.

Запуск макросов с помощью кнопок и меню

17. Создайте кнопку для вызова Макрос1. Для этого:

на вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем в разделе Элементы управления формы выберите элемент Кнопка;

щелкните на листе место, где должен быть расположен левый верхний угол кнопки, и растяните кнопку до нужного размера;

в диалоговом окне Назначить макрос объекту выберите в списке макросов Макрос1 и щелкните кнопку OK;

откорректируйте название кнопки (назовите, например, «Месяцы»);

Примечание. Чтобы указать свойства кнопки, щелкните ее правой кнопкой мыши и выберите пункт Формат объекта.

18. Выполните Макрос1 с помощью кнопки.

19. Создайте кнопку для вызова Макрос3 и выполните этот макрос с помощью кнопки.

20. Добавьте команду запуска макроса на панель быстрого доступа. Для этого:

нажмите кнопку Microsoft Office, затем кнопку Параметры Excel и выберите команду Настройка;

Примечание. Диалоговое окно Настройка панели быстрого доступа можно также вызвать щелчком по кнопке Настройка панели быстрого доступа справа от панели и выбором из списка команды Другие команды.

в списке Выбрать команды из выберите Макросы, из появившегося списка выберите нужный макрос, а затем нажмите кнопку Добавить;

нажмите ОК.

Примечание. Для перемещения панели быстрого доступа щелкните кнопку Настройка панели быстрого доступа и выберите в списке Разместить под лентой.

Запуск макросов с помощью командной кнопки в форме

21. Создайте электронную форму для ввода данных в таблицу сведений о студентах. Форма должна содержать:

заголовок «Сведения о студенте»;

поле для ввода фамилии с инициалами;

поле со списком для выбора номера группы;

список для выбора наименования специальности;

2 переключателя для выбора пола;

счетчик для выбора года рождения (1990—2010);

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

Для этого выполните следующие действия:

переименуйте один из листов книги Excel в «Формы»;

разместите на листе «Форма» в ячейках А30:А39 список номеров 10 групп, например, 8271-8280. Разместите в ячейках С30-С39 список названий специальностей;

введите в ячейку D2 заголовок формы: “Сведения о студенте”. Введите в ячейки В4, В5, В7, В12, В15 следующие названия: ФИО, Группа, Специальность, Пол, Год рождения;

в ячейку D4 введите фамилию;

на вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем в разделе Элементы управления формы выберите элемент Поле со списком и очертите прямоугольный контур в области ячейки F5;

щелкнув правой клавишей мыши по элементу Поле со списком, вызовите контекстное меню. Выберите пункт Формат объекта;

установите вкладку Элемент управления. Щелкните по кнопке сворачивания в поле Формировать список по диапазону и выделите диапазон ячеек с номерами групп. Разверните вкладку. Щелкните по кнопке сворачивания в поле Связь с ячейкой, затем щелкните по ячейке H5 и разверните вкладку. В поле Количество строк введите значение 5. Включите флажок Объемное затемнение, нажмите ОК;

убедитесь в возможности выбора номера группы из списка с полем и изменении порядкового номера в ячейке H5;

введите в ячейку D5 формулу для расшифровки порядкового номера группы в списке: =ИНДЕКС($А$30:$А$39;$Н$5). Используйте вариант функции со ссылкой. Убедитесь в правильности вывода номера группы в ячейке D5;

на вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем в разделе Элементы управления формы выберите элемент Список и очертите прямоугольный контур в области ячеек G7:I10. Вызовите контекстное меню элемента Список и выберите пункт Формат объекта;

щелкните по кнопке сворачивания в поле Формировать список по диапазону и выделите диапазон ячеек с названиями специальностей. Разверните вкладку. Включите флажок выбора только одинарного значения, затем щелкните по кнопке сворачивания в поле Связь с ячейкой и введите адрес ячейки щелчком по кнопке K7. Разверните вкладку и включите флажок Объемное затемнение. Нажмите ОК;

убедитесь в возможности выбора названия специальности из списка и изменении порядкового номера в ячейке К7;

введите в ячейку D7 формулу для расшифровки порядкового номера группы в списке: =ИНДЕКС($С$30:$С$39;$K$7). Убедитесь в правильности названия специальности в ячейке D7;

на вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем в разделе Элементы управления формы выберите элемент Переключатель и очертите прямоугольный контур в области ячейки F12. Вызовите контекстное меню элемента Переключатель и выберите пункт Формат объекта;

на вкладке Элемент управления щелчком по ячейке D12 введите в поле Связь с ячейкой ее абсолютный адрес и включите флажок Значение установлен. Замените название флажка на «М»;

аналогично расположите значок переключателя в области ячейки F13 и замените его название на «Ж», при этом повторного связывания с ячейкой не требуется;

в разделе Элементы управления формы выберите элемент Счетчик и очертите прямоугольный контур в области ячеек F15:F16. Вызовите контекстное меню элемента Счетчик и выберите пункт Формат объекта;

на вкладке Элемент управления введите в поле Текущее значение: 1990. Введите в поле Минимальное значение: 1990. Введите в поле Максимальное значение: 2010. Введите в поле Шаг изменения: 1. Введите в поле Связь с ячейкой абсолютный адрес ячейки D15, нажмите ОК;

проверьте работу счетчика;

в разделе Элементы управления формы выберите элемент Кнопка и очертите прямоугольный контур в области ячеек C18:D18. Появится окно Назначить макрос объекту. Закройте окно, не назначая макрос. Замените название кнопки на «Запись в таблицу».

22. Создайте на новом листе с именем Список студентов во 2-ой строке шапку таблицы с названиями столбцов: ФИО, Группа, Специальность, Пол, Год рождения. Отрегулируйте ширину столбцов.

23. На листе Форма в ячейки B25, С25, D25, E25, F25 вставьте формулы, ссылающиеся на ячейки D4, D5, D7, D12 и D15. Проверьте формулы в ячейках B25:F25:

В ячейке В25 должна быть формула: =$D$4

В ячейке С25 должна быть формула: =ИНДЕКС($A$30:$A$39;$H$5)

В ячейке D25 должна быть формула: =ИНДЕКС($C$30:$C$39;$K$7)

В ячейке Е25 должна быть формула: =$D$12

В ячейке F25 должна быть формула: =$D$15

24. Осуществите запись начального макроса макрорекордером. Для этого:

на вкладке Разработчик в группе Код нажмите кнопку Запись макроса;

в поле Имя макроса введите имя макроса (по умолчанию);

для начала записи макроса нажмите кнопку ОК;

на листе Форма выделите ячейки B25:F25;

на вкладке Главная в группе Буфер обмена нажмите кнопку Копировать;

перейдите на лист Список студентов и выделите ячейку А3;

на вкладке Главная в группе Буфер обмена раскройте список Вставить и выберите команду Вставить значения;

на вкладке Разработчик в группе Код нажмите кнопку Остановить запись;

25. Проверьте работу созданного макроса. Для этого на листе «Список студентов» очистите диапазон ячеек А3:Е3, перейдите на лист «Формы», на вкладке Разработчик в группе Код нажмите кнопку Макросы, в диалоговом окне Макрос выделите имя созданного макроса и нажмите кнопку Выполнить. Строка сведений будет вставлена на то же место.

26. Для того, чтобы новые сведения вставлялись в таблицу в следующие по порядку строки, необходимо откорректировать текст макроса. Для этого на вкладке Разработчик в группе Код нажмите кнопку Макросы, в диалоговом окне Макрос выделите имя созданного макроса и нажмите кнопку Изменить. Откроется окно редактора Visual Basic.

27. В окне редактора Visual Basic внесите изменения в текст программы после строки Sheets(«Список студентов»).Select

При этом должны быть следующие строки:

If Cells(3, 1).Value <> «» Then

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

28. Закройте окно редактора, щелкнув по самому левому значку на инструментальной панели редактора с изображением логотипа Excel. Повторно выполните макрос.

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

30. Выполните макрос щелчком по кнопке.

31. С помощью созданного макроса заполните список студентов данными о принятых в университет студентах (10-15 человек).

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

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

Для этого выполните следующие действия:

Разместите на листе «Справка» в ячейках A1:G10 постоянный текст справки так, чтобы для ввода фамилии использовалась ячейка D4, для ввода года рождения – E4, для ввода № группы – В7, наименования специальности – D7.

На вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем в разделе Элементы управления формы выберите элемент Поле со списком и очертите указателем мыши прямоугольный контур в зоне ячеек A1:В2. Вызовите контекстное меню элемента Поле со списком и выберите пункт Формат объекта;

Установите вкладку Элемент управления. Щелкните по кнопке сворачивания в поле Формировать список по диапазону и выделите диапазон ячеек с фамилиями студентов без заголовка на листе Список студентов. Разверните вкладку. Щелкните по кнопке сворачивания в поле Связь с ячейкой. Щелкните по ячейке А20. В поле Количество строк введите значение 6;

Перейдите на вкладку Свойства. Снимите флажок Выводить объект на печать. Закройте окно Форматирование объекта кнопкой ОК.

Проверьте правильность работы поля со списком, наблюдая за номером элемента, отображаемого в ячейке А20 при выборе фамилии в списке;

Присвойте диапазону ячеек, в котором находится список, имя Список. Для этого выделите диапазон ячеек, содержащий все данные о студентах без заголовков на листе Список студентов, введите в поле имен имя Список и нажмите клавишу Enter;

Введите в ячейку D4 формулу для отображения выбранной фамилии:

=ИНДЕКС(Список;$A$20;1)

Примечание. Для ввода в качестве аргумента имени диапазона выберите имя Список на вкладке Формулы в группе Определенные имена из списка Использовать в формуле.

Введите в ячейку Е4 формулу для отображения года рождения:

=ИНДЕКС(Список;$A$20;5);

Аналогично введите в ячейку В7 формулу для отображения номера группы, а в ячейку D7 – формулу для вывода наименования специальности.

Окончательно проверьте работу поля со списком. Выполните предварительный просмотр справки. Для этого щелкните значок Кнопка Microsoft Office, щелкните стрелку рядом с командой Печать, а затем выберите в списке команду Предварительный просмотр. При просмотре на справке не должно быть видно поле со списком для выбора студента.

32. Сохраните рабочую книгу на диске в файле с именем lab6.xlsm, причем в окне Сохранение документа в списке Тип файла выберите тип файла Книга Excel с поддержкой макросов.

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

на вкладке Разработчик в группе Код нажмите кнопку Безопасность макросов;

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

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

Три способа умножения матриц в Excel — fastai part-2, lesson-8

Часть-2 изФастай «Из фондов»наконец-то вышел. Я не помню, когда в последний раз я был так взволнован по поводу MOOC. Как только курс был обнародован, я начал копаться в нем, чтобы углубить свое понимание глубокого обучения и основополагающих концепций.

На этот раз я решил последовать совету Джереми и Рэйчел и написать блоги. Эта конкретная статья объясняетразличные методы умножения матриц,что Джереми реализует в Уроке-8. Я буду использоватьМайкрософт Эксельдля иллюстрации.

Умножение матриц

Отличное и интерактивное место для понимания умножения матриц, на которое ссылается Джереми, доступно по адресу:

По сути, как видно из изображения, мы берем транспонирование второй матрицы, умножаем и складываем элементы вместе, чтобы получить результат. Как пример, самый первый пункт 15 в результирующей матрице происходит от 1*2 + 6*2 + 1*1 = 2 + 12 + 1 = 15 ,

Хотя это объяснение визуально приятно, по моему скромному мнению, его трудно преобразовать в код. Так что давайте использовать Excel и понимать матричное умножение по-другому!

Умножение матриц в EXCEL

Рассмотрим две матрицы а такжеВразмера 4×3 а также 3×4 ,
Примечание: я буду использовать жирный шрифт а такжеВсослаться на матрицы в этой статье. Обычная практика — ссылаться на векторы и матрицы, используя жирный шрифт.

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

Поэтому строка 0 из умножается на столбец 0Впоэлементные и результирующие поэлементные продукты добавляются для получения первого элемента в результирующей матрицеСв положении [0][0] ,

Точно так же мы получаем C[0][3] поэлементно умножая строку 0 из и кол 3 изB.В Excel это выглядит так:

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

Вот и мы! Вот так мы делаем матричное умножение! Это было легко, не так ли? Мы теперь не только поняли умножение матриц, но и внедрили его в Excel! Как это круто?

Я лично считаю, что теперь вы готовы работать с некоторыми правилами, связанными с умножением матрицMatMul):

1. Позволять ar,ac быть количеством строк и столбцов вA.Точно так же, пусть br, bc быть количеством строк и столбцов вB.Тогда для выполнения умножения матриц необходимо, чтобы ac == br , Зачем? Итак, как вы видели в Excel, мы умножили строки и столбцы поэлементно, а затем добавили промежуточные продукты, чтобы получить конечный результат. Если есть несоответствие в размерах, и один вектор длиннее другого, мы больше не можем выполнять поэлементное умножение!
2. Размеры результирующей матрицы всегда будут ar,bc , То есть количество строк исходит от и количество столбцов исходит отB.

Способ 1: использование 3-х циклов FOR

Вот еще один отказ от ответственности, при реализации умножения матриц в Excel, мы не только поняли это, но и повторили первый метод Джереми, который использовал 3 цикла FOR. Вот как это выглядит в коде:

Надеюсь, теперь вы сможете лучше понять это. Если нет, то сейчас самое время сделать паузу, поэкспериментировать с кодом и посмотреть, что на самом деле происходит. Я обещаю вам, это так же, как наша версия Excel!

Хорошо, я предполагаю, что вы потратили некоторое время на размышления о коде, поэтому давайте обсудим это!

• Почему первые две петли в range(ar) а также range(bc) ? Ну, как вы помните изПравило-2наша результирующая матрица будет иметь размеры ar,bc , И из Excel мы увидели, что мы пересекаем каждый элементСследовательно, один за другим, чтобы пройти ar строки и bc столбцы нам нужно 2 цикла в range(ar) а также range(bc) ,
• Итак, почему третий цикл FOR в range(ac) #or br ? Ну изПравило-1, мы знаем это ac==br так что на самом деле не имеет значения, ставим ли мы range(ac) или range(br) в третьем цикле. По сути, именно здесь отдельные элементы умножаются вместе и добавляются. Из нашего предыдущего примера Excel, для C[0][0] это где шаг C[0][0] = 1*1 + 10*5 + 100*9 = 1 + 50 + 900 = 951 произойдет, и, наконец, мы перейдем к следующей коробке. Количество предметов, которые умножаются и складываются вместе, равно ac or br ,

Это действительно так! Это метод-1 для вас! Вот песня, чтобы помнить Матричное Умножение. (Я узнал об этом первым из fast.ai, оригинальный автор неизвестен)

Надеюсь, теперь вы понимаете, как две матрицы умножаются друг на друга. Если не,Вотэто еще один учебник Хан Академии

Метод 2: Использование поэлементного умножения

До сих пор мы находили отдельные продукты 10, 20, 30 и сложить их вместе 10 + 20 + 30 = 60 , Но, действительно, все это можно заменить на:

Если вы помните, самый внутренний цикл в range(ac) #or br находил отдельные продукты и добавлял их. Ну, мы можем заменить этот цикл, чтобы выполнить векторное поэлементное произведение и положить .sum() в конце концов, и PyTorch / NumPy имеют возможность выполнять поэлементные операции для нас!

Точно так же мы получаем C[0][3] путем умножения вектора в строке 0 с вектором на цв 3Ви суммируя полученный вектор.

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

Обратите внимание, как это отличается отМетод-1? На этот раз мы просто говорим Excel или PyTorch: умножьте строку 0 с col 0 изВи суммируем полученный вектор, чтобы дать нам ответ. Вместо того чтобы делать 1*1 + 10*5 + 100*9 , Например, для C[0][0] этот метод умножает Vector на строку 0 tensor([1,10,100]) с вектором в столбце 0 изВ tensor<[1,5,9]) чтобы получить промежуточный продукт Вектор tensor([1,50,900]) и суммирует это, чтобы дать результат в положении C[0][0] как tensor(951) ,

Таким образом, мы просто анализируем каждую позициюC,используя две петли FOR и введите соответствующий результат в этой позиции. Если i,j представляют строки и столбцы вСэто выглядит примерно так .

И так далее .. пока мы не получим полную матрицуС,

Метод 3: вещание

Заметили общую тему здесь? Каждый раз мы должны умножить каждый ряд с каждым столбцомВполучитьС, Вы замечаете повторение? Мы умножаем один и тот же вектор строки в bc раз! И мы повторяем этот процесс ar раз!

Есть ли способ умножить вектор строки со всеми столбцамиВполучить соответствующий ряд вC?Да, есть! Войдите в трансляцию .
Примечание: я не буду объяснять вещание, Джереми делает это очень хорошоВот,

Вместо этого давайте посмотрим, что это за волшебный кусок кода

Как обычно, давайте повторим в Excel! Это сделает вещи действительно легкими. Давайте сделаем это поэтапно.

Шаг 1)Выберите i-й ряд a[i,:] , Мы выбираем строку 0 в качестве примера.

Шаг 2)Возьмите егоТранспонирование a[i,:].unsqueeze(-1)

Шаг 3)Развернуть матрицу столбца какВ a[i,:].unsqueeze(-1).expand_as(b)

Шаг-4)Элемент мудрый умножить наВ a[i,:].unsqueeze(-1).expand_as(b)*b

Шаг-5)Суммируйте по строкам, чтобы получить строку 0 C

Полный процесс вещания для каждого ряда выглядит примерно так:

Я надеюсь, что теперь вы получитевещания! Итак, давайте посмотрим, как это выглядит в коде ..

Это именно то, как мы ожидаем, что это будет так же, как нашиВерсия Excel!

Вот и все! Мы успешно рассмотрели три метода умножения матриц, включая вещание.

[expert_bq большом числе наблюдений, когда коэффициенты корреляции необходимо последовательно вычислять из нескольких рядов числовых данных, для удобства получаемые коэффициенты сводят в таблицы, называемые корреляционными матрицами. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Интерпретация результатов. Рассматривается отдельно каждый коэффициент корреляции между соответствующими параметрами. Его числовое значение оценивается по эмпирическим правилам, изложенным в соответствующей лекции.

Матрица межфакторных корреляций в excel — IT Новости из мира ПК

1. Позволять ar,ac быть количеством строк и столбцов вA.Точно так же, пусть br, bc быть количеством строк и столбцов вB.Тогда для выполнения умножения матриц необходимо, чтобы ac == br , Зачем? Итак, как вы видели в Excel, мы умножили строки и столбцы поэлементно, а затем добавили промежуточные продукты, чтобы получить конечный результат. Если есть несоответствие в размерах, и один вектор длиннее другого, мы больше не можем выполнять поэлементное умножение!
2. Размеры результирующей матрицы всегда будут ar,bc , То есть количество строк исходит от и количество столбцов исходит отB.

Есть ли способ умножить вектор строки со всеми столбцамиВполучить соответствующий ряд вC?Да, есть! Войдите в трансляцию .
Примечание: я не буду объяснять вещание, Джереми делает это очень хорошоВот,

Как сделать проверку обратной матрицы в excel?

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

СОВЕТ: О нахождении определителя матрицы читайте статью Вычисление определителя матрицы в MS EXCEL

Матрица А -1 называется обратной для исходной матрицы А порядка n, если справедливы равенства А -1 *А=Е и А*А -1 =Е, где Е единичная матрица порядка n.

Для вычисления обратной матрицы в MS EXCEL существует специальная функция МОБР() .

Если элементы исходной матрицы 2 х 2 расположены в диапазоне А8:В9, то для получения транспонированной матрицы нужно (см. файл примера ):

• выделить диапазон 2 х 2, который не пересекается с исходным диапазономА8:В9, например, Е8:F9
• в Cтроке формул ввести формулу = МОБР (A8:B9) и нажать комбинацию клавиш CTRL+SHIFT+ENTER, т.е. нужно ввести ее как формулу массива (формулу можно ввести прямо в ячейку, предварительно нажав клавишу F2)

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

Ссылка на массив также может быть указана как ссылка на именованный диапазон.

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

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

Вычисление обратной матрицы с помощью матрицы из алгебраических дополнений

СОВЕТ: Этот раздел стоит читать только продвинутым пользователям MS EXCEL. Кроме того материал представляет только академический интерес, т.к. есть функция МОБР() .

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

• Вычисляем определитель матрицы А (далее – Det(A)) и убеждаемся, что он отличен от нуля (в противном случае матрица А необратима)
• Строим матрицу из алгебраических дополнений элементов исходной матрицы
• Транспонируем матрицу из алгебраических дополнений
• Умножаем каждый элемент транспонированной матрицы из алгебраических дополнений на 1/Det(A) и получаем обратную матрицу

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

Приложение Excel выполняет целый ряд вычислений, связанных с матричными данными. Программа обрабатывает их, как диапазон ячеек, применяя к ним формулы массива. Одно из таких действий – это нахождение обратной матрицы. Давайте выясним, что представляет собой алгоритм данной процедуры.

Выполнение расчетов

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

Расчет определителя

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

Запускается Мастер функций. В перечне записей, который он представляет, ищем «МОПРЕД», выделяем этот элемент и жмем на кнопку «OK».

Открывается окно аргументов. Ставим курсор в поле «Массив». Выделяем весь диапазон ячеек, в котором расположена матрица. После того, как его адрес появился в поле, жмем на кнопку «OK».

Расчет обратной матрицы

Теперь можно преступить к непосредственному расчету обратной матрицы.

В открывшемся списке выбираем функцию МОБР. Жмем на кнопку «OK».

Как видим, после этих действий обратная матрица вычислена в выделенных ячейках.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Подробно рассмотрим особенности вычисления обратной матрицы в Excel и примеры использования функции МОБР.

В первую очередь освежим в памяти, что обратная матрица — это матрица (записывается как A -1 ), при умножении которой на исходную матрицу (A) дает единичную матрицу (E), другими словами выполняется формула:

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

Как найти обратную матрицу в Excel?

Функция МОБР

МОБР(массив)
Возвращает обратную матрицу (матрица хранится в массиве).

Рассмотрим расчет обратной матрицы посредством функции МОБР на конкретном примере.
Предположим у нас имеется следующая квадратная матрица 3-го порядка:

Выделяем диапазон пустых ячеек E2:G4, куда мы в дальнейшем поместим обратную матрицу.
Не снимая выделения ячеек вводим формулу =МОБР(A2:C4) и нажимаем комбинацию клавиш Ctrl + Shift + Ввод для расчета формулы массива по данному диапазону:

При работе с функцией МОБР могут возникнуть следующие ошибки:

[expert_bq только курс был обнародован, я начал копаться в нем, чтобы углубить свое понимание глубокого обучения и основополагающих концепций. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Теперь можно переходить непосредственно к расчету множественного коэффициента корреляции. Давайте на примере представленной ниже таблицы показателей производительности труда, фондовооруженности и энерговооруженности на различных предприятиях рассчитаем множественный коэффициент корреляции указанных факторов.

Транспонирование матрицы в программе Microsoft Excel

• выделить диапазон 2 х 2, который не пересекается с исходным диапазономА8:В9, например, Е8:F9
• в Cтроке формул ввести формулу = МОБР (A8:B9) и нажать комбинацию клавиш CTRL+SHIFT+ENTER, т.е. нужно ввести ее как формулу массива (формулу можно ввести прямо в ячейку, предварительно нажав клавишу F2)

Для того, чтобы составить корреляционную матрицу в Экселе, используется один инструмент, входящий в пакет «Анализ данных». Он так и называется – «Корреляция». Давайте узнаем, как с помощью него можно вычислить показатели множественной корреляции.

Матрица ковариаций в Excel

Ковариационная матрица — это квадратная матрица, показывающая ковариацию между столбцами и дисперсию в столбцах. В Excel представлен встроенный инструмент «Анализ данных» для определения ковариации между различными наборами данных. В данной статье объясняется расчет ковариационной матрицы в Excel, охватывая следующие темы, в том числе

Объяснение

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

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

Как использовать матрицу ковариации в Excel?

Ковариационная матрица используется в различных приложениях, в том числе

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

Примеры ковариационной матрицы в Excel

Ниже приведены некоторые примеры использования ковариационной матрицы в Excel.

Вы можете скачать этот шаблон Excel для матрицы ковариаций здесь — Шаблон для Excel для матрицы ковариаций

Пример # 1

Выполнение ковариационного анализа оценок, полученных студентами по разным предметам.

Шаг 1: Следующие данные, включая оценки учащихся по математике, английскому языку и естествознанию, считаются такими, как показано на рисунке.

Шаг 2: Перейдите на вкладку «Данные» на ленте и найдите в правом углу набор инструментов «Анализ данных».

Если пакет инструментов «Анализ данных» недоступен, выполните следующие действия.

Шаг А: Перейдите на вкладку «Файл» и выберите «Параметры».

Шаг B: Зайдите в Надстройки. В разделе «Параметры управления» убедитесь, что выбран «Надстройки Excel», и нажмите кнопку «Перейти», как показано на рисунке.

Шаг C: Выберите «Analysis-Tool Pak» и «Analysis-ToolPak VBA», как показано на снимке экрана.

После выполнения этих шагов пакет инструментов «Анализ данных» добавляется на вкладку «Данные».

Шаг 3: Щелкните Анализ данных. Откроется диалоговое окно «Анализ данных». Выберите «Ковариацию», прокрутив вверх, и нажмите «ОК».

Шаг 5: Выберите диапазон ввода, включая имена субъектов, отметьте «метки в первой строке» и укажите «диапазон вывода» на существующем листе. И нажмите «ОК».

Шаг 6: Мы получим следующий результат —

Верхняя часть диагонали пуста, так как ковариационная матрица Excel симметрична относительно диагонали.

Пример # 2

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

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

Шаг 2: Открывает диалоговое окно «Анализ данных» и выбирает «Ковариацию», прокручивая вверх и нажимая «ОК».

Шаг 3: Выберите диапазон ввода, включая заголовки, отметьте «метки в первой строке» и укажите «диапазон вывода» на существующем листе. И нажмите «ОК».

Шаг 4: Мы получим следующий результат —

Верхняя часть диагонали пуста, поскольку ковариационная матрица симметрична по отношению к диагонали.

Пример # 3

Расчет ковариационной матрицы котировок акций корпоративных компаний

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

Шаг 2: Открывает диалоговое окно «Анализ данных», выбирает «Ковариацию», прокручивая вверх, и нажимает «ОК».

Шаг 3: Выберите диапазон ввода, включая заголовки, отметьте «метки в первой строке» и укажите «диапазон вывода» на существующем листе и нажмите «ОК».

Матрица ковариаций в Excel.

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

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

Матрица в Excel

Excel – это созданная корпорацией Microsof программа, предназначенная для работы с электронными таблицами.

Введение

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

Формулы массива

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

1. Осуществить выделение участка ячеек, куда следует вывести значения.
2. Задать требуемую формулу для вычислений.
3. Нажать клавишный набор Ctrl + Shift +Enter.

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

Операции с матрицами

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

1. Нужно выполнить выделение матрицы и сделать её копию.
2. Выполнить выделение диапазона ячеек для вставки транспонируемого диапазона.
3. Открыть окно «Специальная вставка».
4. Выбрать кнопку «Транспонировать» и нажать ОК.

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

Рисунок 1. Окно программы. Автор24 — интернет-биржа студенческих работ

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

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

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

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

Рисунок 2. Пример. Автор24 — интернет-биржа студенческих работ

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

= Начальный компонент первой матрицы + Начальный компонент второй матрицы

Затем следует подтвердить задание формулы клавишей Enter и применить функцию авто заполнения (квадрат в нижнем правом углу) для копирования всех величин в новую матрицу. Итог приведён на рисунке ниже:

Рисунок 3. Итог. Автор24 — интернет-биржа студенческих работ

Далее рассмотрим операцию умножения. Имеется следующая таблица, все элементы которой необходимо умножить на двенадцать:

Рисунок 4. Таблица. Автор24 — интернет-биржа студенческих работ

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

И результирующую матрицу:

Рисунок 5. Результирующая матрица. Автор24 — интернет-биржа студенческих работ

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

Рисунок 6. Перемножение матриц. Автор24 — интернет-биржа студенческих работ

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

Далее следует нажать комбинацию клавиш Ctrl + Shift + Enter, чтобы увидеть итог:

Рисунок 7. Итог. Автор24 — интернет-биржа студенческих работ

Далее рассмотрим пример обратной матрицы. Если матрица (её диапазон) квадратной формы, то есть число ячеек по вертикали равно числу ячеек по горизонтали, то значит, при необходимости, можно определить обратную матрицу. Это можно сделать при помощи функции МОБР. Сначала нужно сделать выделение первой ячейки матрицы, куда будет вставлена обратная матрица. В неё нужно ввести формулу:

В качестве аргумента нужно указать диапазон, для которого следует сформировать обратную матрицу. Далее нужно использовать комбинацию клавиш Ctrl + Shift + Enter.

Рисунок 8. Окно программы. Автор24 — интернет-биржа студенческих работ

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

Далее рассмотрим ещё один пример вычислений. Имеется матрица А, размером три на четыре. Есть, так же, некоторое число k, записанное вне матрицы. Когда будет выполнена операция умножения матрицы на это число, возникнет диапазон величин, который имеет такие же размеры, но все его компоненты умножены на k:

Рисунок 9. Окно программы. Автор24 — интернет-биржа студенческих работ

Диапазон B3:E5 является исходной матрицей, подлежащей умножению на число k, расположенному в клетке H4. Итоговая матрица будет располагаться в диапазоне K3:N5. Исходная матрица обозначается как А, а итоговая как В. Итоговая матрица В будет образована умножением матрицы А на величину k. Формула для вычислений записывается в ячейку К3: