Нахождение определителя матрицы
Это одно единственное число, которое находится для квадратной матрицы. Используемая функция – МОПРЕД.
Ставим курсор в любой ячейке открытого листа. Вводим формулу: =МОПРЕД(A1:D4).
Таким образом, мы произвели действия с матрицами с помощью встроенных возможностей Excel.
Вычислить значения корней сформированной системы уравнений двумя методами: обратной матрицы и методом Крамера.
Введем данные значения в ячейки А2:С4 – матрица А и ячейки D2:D4 – матрица В.
Решение системы уравнений методом обратной матрицы
Найдем матрицу, обратную матрице А. Для этого в ячейку А9 введем формулу =МОБР(A2:C4). После этого выделим диапазон А9:С11, начиная с ячейки, содержащей формулу. Нажмем клавишу F2, а затем нажмем клавиши CTRL+SHIFT+ENTER. Формула вставится как формула массива. =МОБР(A2:C4). Найдем произведение матриц A-1 * b. В ячейки F9:F11 введем формулу: =МУМНОЖ(A9:C11;D2:D4) как формулу массива. Получим в ячейках F9:F11
корни уравнения:
Решение системы уравнений методом Крамера
Решим систему методом Крамера, для этого найдем определитель матрицы. Найдем определители матриц, полученных заменой одного столбца на столбец b.
В ячейку В16 введем формулу =МОПРЕД(D15:F17),
В ячейку В17 введем формулу =МОПРЕД(D19:F21).
В ячейку В18 введем формулу =МОПРЕД(D23:F25).
Найдем корни уравнения, для этого в ячейку В21 введем: =B16/$B$15, в ячейку В22 введем: = =B17/$B$15, в ячейку В23 введем: ==B18/$B$15.
Получим корни уравнения:
Способ 1
Рассмотрим матрицу А размерностью 3х4. Умножим эту матрицу на число k. При умножении матрицы на число получается матрица такой же размерности, что и исходная, при этом каждый элемент матрицы А умножается на число k.
Введем элементы матрицы в диапазон В3:Е5, а число k — в ячейку Н4. В диапазоне К3:N5 вычислим матрицу В, полученную при умножении матрицы А на число k: В=А*k. Для этого введем формулу =B3*$H$4 в ячейку K3, где В3 — элемент а 11 матрицы А.
Примечание: адрес ячейки H4 вводим как абсолютную ссылку, чтобы при копировании формулы ссылка не менялась.
С помощью маркера автозаполнения копируем формулу ячейки К3 В.
Таким образом, мы умножили матрицу А в Excel и получим матрицу В.
Для деления матрицы А на число k в ячейку K3 введем формулу =B3/$H$4 В.
Способ 2
Этот способ отличается тем, что результат умножения/деления матрицы на число сам является массивом. В этом случае нельзя удалить элемент массива.
Для деления матрицы на число этим способом выделяем диапазон, в котором будет вычислен результат, вводим знак «=», выделяем диапазон, содержащий исходную матрицу А, нажимаем на клавиатуре знак умножить (*) и выделяем ячейку с числом k Ctrl+Shift+Enter
Для выполнения деления в данном примере в диапазон вводим формулу =B3:E5/H4, т.е. знак «*» меняем на «/».
Готовимся к запуску функции ЕСЛИ
Для начала рассмотрим, как функция ЕСЛИ работает в классическом виде — для проверки одного условия пользователя. Определим автомобили стоимостью до 2,5 млн рублей.
Принцип действия функции ЕСЛИ следующий. Алгоритм просматривает выбранный диапазон таблицы и проверяет, соответствуют ли данные его ячеек запросу пользователя. Затем возвращается в отдельную ячейку и оставляет там результат: ИСТИНА или ЛОЖЬ. О том, как настроить алгоритм функции под наши потребности, поговорим . А сейчас на примере покажем, как подготовить таблицу к запуску функции и как вызвать окно для её построения.
Функция ЕСЛИ приносит результаты в отдельную ячейку, поэтому создадим отдельный столбец для них. Лучше сразу назвать его так, чтобы было понятно, о чём речь. В нашем случае сделаем столбец «До 2,5 млн руб.».
Создаём столбец, в который функция ЕСЛИ принесёт результатСкриншот: Excel / Skillbox Media
Выделим первую ячейку нового столбца и откроем окно для поиска функций. Это можно сделать двумя способами:
1. Перейти во вкладку «Формулы» и нажать «Вставить функцию».
Нажимаем сюда, чтобы вызвать окно для поиска функцийСкриншот: Excel / Skillbox Media
2. Нажать на «fx» в строке ссылок на любой вкладке Excel.
Так окно поиска функции можно открыть с любой вкладки ExcelСкриншот: Excel / Skillbox Media
На экране справа появится окно «Построитель формул». В нём через поиск находим функцию ЕСЛИ и нажимаем «Вставить функцию».
Нажимаем сюда, чтобы открылось окно для построения функции ЕСЛИСкриншот: Excel / Skillbox Media
Появляется окно для заполнения аргументов функции: «Лог_выражение», «Значение_если_истина», «Значение_если_ложь». Ниже разберёмся, как их заполнить.
Как пересчитать, суммировать или найти среднее по нескольким условиям
В Excel 2003 и более ранних версиях, в которых ещё не было функций ЕСЛИ с условиями, одним из наиболее распространенных применений функции СУММПРОИЗВ было условное суммирование или подсчет ячеек с несколькими критериями. Начиная с Excel 2007, Microsoft представила серию функций, специально разработанных для таких задач, — СУММЕСЛИМН, СЧЁТЕСЛИМН и СРЗНАЧЕСЛИ.
Но даже в современных версиях Excel формула СУММПРОИЗВ может быть достойной альтернативой, например, для условного суммирования и подсчета ячеек с логикой ИЛИ. Ниже вы найдете несколько примеров формул, демонстрирующих эту способность в действии.
Формула СУММПРОИЗВ с логикой И
Предположим, у вас есть следующий набор данных, где в столбце A перечислены регионы, в столбце B — товары, а в столбце C — данные о продажах:
Вам нужно получить количество, сумму и среднее значение продаж яблок в северном регионе.
В последних версиях Excel 2016, 2013, 2010 и 2007 задачу можно легко выполнить с помощью формул СУММЕСЛИМН, СЧЁТЕСЛИМН и СРЗНАЧЕСЛИ. Если вы не ищете легких путей или все еще используете Excel 2003 или более раннюю версию, вы можете получить желаемый результат с помощью СУММПРОИЗВ.
Чтобы сделать ваши формулы СУММПРОИЗВ более гибкими, мы указываем нужный регион и товар в отдельных ячейках, а затем ссылаемся на эти ячейки в своей формуле, как показано на скриншоте ниже:
Чтобы подсчитать количество продаж яблок для Севера :
Чтобы вычислить среднее значение, мы просто делим сумму на количество следующим образом:
Формула СУММПРОИЗВ с логикой ИЛИ
Напомним, что логика ИЛИ означает, что выполняется хотя бы одно из нескольких условий. Чтобы суммировать или подсчитывать ячейки с помощью логики ИЛИ, используйте знак плюс (+) между массивами.
В формулах СУММПРОИЗВ Excel, а также в формулах массива символ плюс действует как оператор ИЛИ, который указывает Excel вернуть ИСТИНА, если ЛЮБОЕ из условий в данном выражении оценивается как ИСТИНА.
На следующем скриншоте показана такая формула в действии:
Чтобы подсчитать количество всех продаж апельсинов и лимонов независимо от региона, использована следующая формула:
Она означает следующее: подсчитывать ячейки, если B3:B13=»апельсины» ИЛИ B3:B13=»лимоны».
Чтобы найти общие продажи апельсинов и лимонов , добавьте еще один аргумент, содержащий диапазон продаж :
Формула СУММПРОИЗВ с логикой И и ИЛИ
Во многих ситуациях вам может понадобиться условно подсчитать или суммировать ячейки с помощью логики И и ИЛИ одновременно. Даже в последних версиях Excel серия функций ЕСЛИ на это не способна.
Одним из возможных решений является объединение двух или более функций СУММЕСЛИМН+СУММЕСЛИМН или СЧЁТЕСЛИМН+СЧЁТЕСЛИМН .
Другой способ — использовать функцию СУММПРОИЗВ Excel, где:
- Знак умножения (*) используется как оператор И.
- Плюс (+) используется как оператор ИЛИ.
Для лучшего понимания, рассмотрим следующие примеры.
Чтобы сделать формулы немного более компактными, вы можете записать переменные в отдельные ячейки — «Регион» в F1 и «Товары» в F2 и H2 — и ссылаться на них в своей формуле:
Сосчитаем, сколько раз яблоки и лимоны были проданы в Северном регионе. Для этого составим формулу со следующей логикой:
Чтобы суммировать продажи яблок и лимонов в Северном регионе, возьмите приведенную выше формулу и добавьте массив Продажи с логикой «И»:
Как употреблять функцию МУМНОЖ в Excel?
Разглядим последующий пример. Компания занимается созданием ролов на заказ, в состав ассортимента заходит четыре вида продукции: рол унаги, филадельфия, зеленоватый дракон. Представим нам нужно решить задачку о издержек на покупку ингредиентов (рис, мягенький сыр, лосось) для планового производства ролов. Ниже приведем таблицы А — нормы расхода ингредиентов, B — план выпуска ролов (в штуках).
Другими словами, чтоб нам получить матрицу-строку издержек ингредиентов C, нужно помножить матрицу B на матрицу А:
Итоговая размерность матрицы С равна 1×3. Для вычисления частей матрицы С и для проверки приобретенных издержек на ингредиенты можно пользоваться интегрированной функцией табличного микропроцессора MS Excel МУМНОЖ.
1.3. Адресация
A1C5Name BoxA1F=адрес=A1
Например, первая ячейка имеет абсолютный адрес – $A$1, относительный адрес –
A1, и два
смешанных адреса – $A1 и
A$1. Различие в способе адресации проявляется,
прежде всего, тогда, когда формула копируется и переносится в другое
место. Поясним это на простом примере.
Рис. 5 Абсолютная и относительная адресация
На верхней панели показан фрагмент листа с
данными, выделенными желтым цветом. В зеленых областях (столбец
F и строка 6)
приведены различные варианты адресации одной и той же ячейки –
A1 (выделена оранжевым). Тип адресации
указан рядом с соответствующей ячейкой. Скопируем каждую из зеленых
областей (по очереди) и вставим рядом – в соседних столбцах:
G и H, и в
соседних строках: 7 и
8 (средняя панель ). Видно, что
результат зависит от типа адресации. Для абсолютной адресации ссылка на
первую ячейку сохранилась. Для относительной – ссылка сдвигается вправо
или вниз, сохраняя относительное положение двух ячеек: той, где стоит
ссылка, и той, на которую ссылаются. Для смешанной адресации результат
зависит от того, куда переносится копия, и от того, какая часть адреса
фиксируется значком доллара $. На правой
панели показаны соответствующие формулы, получающиеся после копирования.
Заметим, что ссылки на ячейки могут изменяться в зависимости от способа
адресации, но при перемещении ячейки с формулой содержащиеся в формуле
ссылки не изменяются.
Для адресации ячейки, которая находится на другом листе той же книги,
надо указывать еще и имя листа, например: Data!B2.
Восклицательный знак (!) отделяет имя листа
от адреса ячейки. Если имя листа содержит пробел, тогда имя надо
заключить в одинарные кавычки, например ‘Raw
Spectra’!C6. При адресации к другой книге, ее имя указывается
впереди, в квадратных скобках, например;
Results!P24
Подробнее о способах адресации можно прочитать
здесь.
Умножение матриц в Excel
Следует отметить, что умножать матрицы можно только в том случае, если количество столбцов первой матрицы А равно количеству строк второй матрицы В.
Рассмотрим матрицы А размерностью 3х4 и В размерностью 4х2. При умножении этих матриц получится матрица С размерностью 3х2.
Вычислим произведение этих матриц С=А*В с помощью встроенной функции =МУМНОЖ(). Для этого выделим диапазон L3:M5 — в нём будут располагаться элементы матрицы С, полученной в результате умножения. На вкладке Формулы выберем Вставить функцию.
В диалоговом окне Вставка функции выберем Категория Математические — функция МУМНОЖ — ОК.
В диалоговом окне Аргументы функции выберем диапазоны, содержащие матрицы А и В. Для этого напротив массива1 щёлкнем по красной стрелке.
Выделим диапазон, содержащий элементы матрицы А (имя диапазона появится в строке аргументов), и щелкнем по красной стрелке.
Для массива2 выполним те же действия. Щёлкнем по стрелке напротив массива2.
Выделим диапазон, содержащий элементы матрицы В, и щелкнем по красной стрелке.
В диалоговом окне рядом со строками ввода диапазонов матриц появятся элементы матриц, а внизу — элементы матрицы С. После ввода значений нажимаем на клавиатуре сочетание клавиш Shift+Ctrl и щелкаем левой кнопкой мыши по кнопке ОК.
ВАЖНО. Если просто нажать ОК, то программа вычислит значение только первой ячейки диапазона матрицы С
Мы получим результат умножения матриц А и В.
Мы можем изменить значения ячеек матриц А и В, значения матрицы С поменяются автоматически.
1 .10. Построение графиков
В Excel можно строить диаграммы разных типов. Но для нас
интересны только два вида: диаграмма рассеяния (scatter) и график (line).
Пример диаграммы рассеяния приведен на Рис. 24 .
Рис.24
Диаграмма рассеяния
Диаграммы такого типа используются для построения графиков
счетов, зависимостей «измерено-предсказано», и т.п. От линейных графиков
они отличаются равноправием обеих осей. В линейных графиках ось абсцисс
предназначена только для отображения категорийных переменных, т.е.
величин, в которых важна не их величина, а порядок следования. Поэтому
линейные графики подходят для представления зависимостей от числа
главных компонент, например, для изображения того, как величины RMSEC и
RMSEP меняются при усложнении модели.
Методы построения графиков в версиях 2003 и 2007 сильно
отличаются. Поэтому мы не будем на этом останавливаться, предоставив эту
тему для
самостоятельного изучения .
Использование оператора МУМНОЖ
Основной задачей функции МУМНОЖ, как было сказано выше, является перемножение двух матриц. Она относится к категории математических операторов.
Синтаксис этой функции следующий:
Как видим, у оператора всего два аргумента – «Массив1» и «Массив2». Каждый из аргументов представляет собой ссылку на одну из матриц, которую следует перемножить. Именно это и выполняет указанный выше оператор.
Важным условием для применения МУМНОЖ является то, что количество строк первой матрицы должно совпадать с количеством столбцов второй. В обратном случае, в результате обработки будет выдаваться ошибка. Также во избежание ошибки ни один из элементов обоих массивов не должен быть пустым, а они полностью должны состоять из чисел.
Умножение матриц
Теперь давайте на конкретном примере рассмотрим, как можно умножить две матрицы, применив оператор МУМНОЖ.
- Открываем лист Excel, на котором уже располагаются две матрицы. Выделяем на нем область из пустых ячеек, которая по горизонтали имеет в своем составе количество строк первой матриц, а по вертикали количество столбцов второй матрицы. Далее жмем на значок «Вставить функцию», который размещен около строки формул.
Происходит запуск Мастера функций. Нам следует перейти в категорию «Математические» или «Полный алфавитный перечень». В списке операторов нужно отыскать наименование «МУМНОЖ», выделить его и нажать на кнопку «OK», которая размещена в нижней части данного окна.
Запускается окно аргументов оператора МУМНОЖ. Как видим, оно имеет два поля: «Массив1» и «Массив2». В первом нужно указать координаты первой матрицы, а во втором, соответственно, второй. Для того, чтобы сделать это, устанавливаем курсор в первое поле. Затем производим зажим левой кнопкой мыши и выделяем область ячеек, содержащую первую матрицу. После выполнения этой несложной процедуры координаты отобразятся в выбранном поле. Аналогичное действие проводим и со вторым полем, только в этот раз, зажав левую кнопку мыши, выделяем вторую матрицу.
После того, как адреса обеих матриц записаны, не спешим жать на кнопку «OK», размещенную в нижней части окна. Дело в том, что мы имеем дело с функцией массива. Она предусматривает то, что результат выводится не в одну ячейку, как в обычных функциях, а сразу в целый диапазон. Поэтому для вывода итога обработки данных, используя этот оператор, недостаточно нажать на клавишу Enter, расположив курсор в строке формул, или щелкнуть по кнопке «OK», находясь в окне аргументов функции, которое открыто у нас в данный момент. Нужно применить нажатие комбинации клавиш Ctrl+Shift+Enter. Выполняем данную процедуру, а кнопку «OK» не трогаем.
Но именно то, что результат обработки функции МУМНОЖ является цельным массивом, мешает дальнейшему его изменению в случае необходимости. При попытке изменить любое из чисел итогового результата пользователя будет ждать сообщение, которое информирует о том, что нельзя изменять часть массива. Чтобы устранить это неудобство и преобразовать неизменяемый массив в обычный диапазон данных, с которым можно работать, выполняем следующие действия.
Выделяем данный диапазон и, находясь во вкладке «Главная», кликаем по значку «Копировать», который размещен в блоке инструментов «Буфер обмена». Также вместо этой операции можно применить набор сочетания клавиш Ctrl+C.
После этого, не снимая выделения с диапазона, кликаем по нему правой кнопкой мыши. В открывшемся контекстном меню в блоке «Параметры вставки» выбираем пункт «Значения».
После выполнения данного действия итоговая матрица уже не будет представлена как единый неразрывный диапазон и с ней можно производить различные манипуляции.
Как видим, оператор МУМНОЖ позволяет довольно быстро и легко умножать в Экселе две матрицы друг на друга. Синтаксис этой функции довольно простой и у пользователей не должно возникнуть проблем со вводом данных в окно аргументов. Единственная проблема, которая может возникнуть при работе с этим оператором, заключается в том, что он представляет собой функцию массива, а значит, имеет определенные особенности. Для вывода результата требуется предварительно выделить соответствующий диапазон на листе, а затем уже после ввода аргументов для расчета применить специальную комбинацию клавиш, предназначенную для работы именно с таким типом данных — Ctrl+Shift+Enter.
3.6. Установка надстроек
Прежде чем начать использовать надстройку, нужно
выполнить процедуру ее установки, которая состоит из двух частей.
В первой фазе файлы, входящие в пакет надстройки
размещают на компьютере. В некоторых пакетах имеется программа
Setup.exe, которая выполняет это автоматически. В других файлы нужно
размещать самостоятельно. Объясним, как это нужно сделать. В состав
пакета обязательно входит файл с расширением XLA и несколько
вспомогательных файлов с расширениями DLL, HLP и др. Все вспомогательные
файлы должны размещаться в следующих директориях:
C:\Windows, или C:\Windows\System или
C:\Windows\System32. Основной файл (с расширением XLA) может, в
принципе, находится в любом месте, но две директории являются
предпочтительными.
Microsoft рекомендует размещать файлы XLA в директории
C:\Documents and Settings\User\Application
Data\Microsoft\AddIns, где User – это
имя, под которым происходит вход в систему. Тогда этот файл можно быстро
загрузить на второй фазе установки. Однако, если рабочие книги
используются на нескольких компьютерах, с разными именами
User, то, при смене компьютера, связи с
основным файлом надстройки теряются и их приходится
обновлять.
Поэтому мы предлагаем поместить файл
Chemometrics.xla в директорию, которая имеет одно и то же имя на
разных компьютерах, например C:\Program
Files\Chemometrics. Автоматическая установка надстройки
Chemometrics Add-In
описана здесь.
Вторая фаза проводится из открытой книги Excel. В версии
2003 нужно выполнить последовательность команд
Tools-Add-Ins, а в версии 2007 последовательность:
Office Button-Excel Options-Add-Ins-Go. В
появившемся окне (см Рис. 45) нужно нажать Browse
и найти в компьютере нужный файл XLA.
Рис.45 Установка надстройки
После того, как надстройка установлена, ее можно
активировать и деактивировать устанавливая отметку напротив имени. Для
удаления надстройки нужно снять галочку против ее имени в окне
Add-Ins, закрыть Excel и удалить все ранее
установленные файлы с компьютера.
Заключение
Мы рассмотрели основные приемы работы с матрицами в
системе Excel
За рамками пособия осталось еще много всего важного. Частично заполнить эти пробелы поможет пособие Проекционные методы в
системе Excel
Выполнение расчетов
Вычисление обратной матрицы в Excel возможно только в том случае, если первичная матрица является квадратной, то есть количество строк и столбцов в ней совпадает. Кроме того, её определитель не должен быть равен нулю. Для вычисления применяется функция массива МОБР. Давайте на простейшем примере рассмотрим подобное вычисление.
Расчет определителя
Прежде всего, вычислим определитель, чтобы понять, имеет первичный диапазон обратную матрицу или нет. Это значение рассчитывается при помощи функции МОПРЕД.
- Выделяем любую пустую ячейку на листе, куда будут выводиться результаты вычислений. Жмем на кнопку «Вставить функцию», размещенную около строки формул.
Запускается Мастер функций. В перечне записей, который он представляет, ищем «МОПРЕД», выделяем этот элемент и жмем на кнопку «OK».
Открывается окно аргументов. Ставим курсор в поле «Массив». Выделяем весь диапазон ячеек, в котором расположена матрица. После того, как его адрес появился в поле, жмем на кнопку «OK».
Программа производит расчет определителя. Как видим, для нашего конкретного случая он равен – 59, то есть не тождественен нулю. Это позволяет сказать, что у данной матрицы существует обратная.
Расчет обратной матрицы
Теперь можно преступить к непосредственному расчету обратной матрицы.
- Выделяем ячейку, которая должна стать верхней левой ячейкой обратной матрицы. Переходим в Мастер функций, кликнув по значку слева от строки формул.
В открывшемся списке выбираем функцию МОБР. Жмем на кнопку «OK».
В поле «Массив», открывшегося окна аргументов функции, устанавливаем курсор. Выделяем весь первичный диапазон. После появления его адреса в поле, жмем на кнопку «OK».
Как видим, появилось значение только в одной ячейке, в которой была формула. Но нам нужна полноценная обратная функция, поэтому следует скопировать формулу в другие ячейки. Выделяем диапазон, равнозначный по горизонтали и вертикали исходному массиву данных. Жмем на функциональную клавишу F2, а затем набираем комбинацию Ctrl+Shift+Enter. Именно последняя комбинация предназначена для обработки массивов.
Как видим, после этих действий обратная матрица вычислена в выделенных ячейках.
На этом расчет можно считать завершенным.
Если вы производите расчет определителя и обратной матрицы только при помощи ручки и бумаги, то над этим вычислением, в случае работы над сложным примером, можно ломать голову очень долго. Но, как видим, в программе Эксель данные вычисления производятся очень быстро, независимо от сложности поставленной задачи. Для человека, который знаком с алгоритмом подобных расчетов в этом приложении, все вычисление сводится к чисто механическим действиям.
Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.
1.3. Адресация
A1C5Name BoxA1F=адрес=A1
Например, первая ячейка имеет абсолютный адрес – $A$1, относительный адрес –
A1, и два
смешанных адреса – $A1 и
A$1. Различие в способе адресации проявляется,
прежде всего, тогда, когда формула копируется и переносится в другое
место. Поясним это на простом примере.
Рис. 5 Абсолютная и относительная адресация
На верхней панели показан фрагмент листа с
данными, выделенными желтым цветом. В зеленых областях (столбец
F и строка 6)
приведены различные варианты адресации одной и той же ячейки –
A1 (выделена оранжевым). Тип адресации
указан рядом с соответствующей ячейкой. Скопируем каждую из зеленых
областей (по очереди) и вставим рядом – в соседних столбцах:
G и H, и в
соседних строках: 7 и
8 (средняя панель ). Видно, что
результат зависит от типа адресации. Для абсолютной адресации ссылка на
первую ячейку сохранилась. Для относительной – ссылка сдвигается вправо
или вниз, сохраняя относительное положение двух ячеек: той, где стоит
ссылка, и той, на которую ссылаются. Для смешанной адресации результат
зависит от того, куда переносится копия, и от того, какая часть адреса
фиксируется значком доллара $. На правой
панели показаны соответствующие формулы, получающиеся после копирования.
Заметим, что ссылки на ячейки могут изменяться в зависимости от способа
адресации, но при перемещении ячейки с формулой содержащиеся в формуле
ссылки не изменяются.
Для адресации ячейки, которая находится на другом листе той же книги,
надо указывать еще и имя листа, например: Data!B2.
Восклицательный знак (!) отделяет имя листа
от адреса ячейки. Если имя листа содержит пробел, тогда имя надо
заключить в одинарные кавычки, например ‘Raw
Spectra’!C6. При адресации к другой книге, ее имя указывается
впереди, в квадратных скобках, например;
Results!P24
Подробнее о способах адресации можно прочитать
здесь.
Как множить в «Экселе» число на число, столбец на столбец
В офисном пакете от «Майкрософт» находится хорошая программка Excel. Это удачный и довольно обычной редактор для работы с табличными данными. В программке находится большенный набор функций, таковых как сортировка и поиск, математические операции умножения, деления, суммирования, поиска среднего значения. В ней же можно выстроить диаграммы и графики.
При ведении бухгалтерии либо различного рода документации нередко приходится иметь дело с простейшими математическими операциями, а заавтоматизировать их можно с помощью имеющихся функций. Чтоб разобраться, как множить в «Экселе», необходимо в первую очередь сделать документ Excel и ввести в него нужные данные.
Функции для работы с матрицами в Excel
В программе Excel с матрицей можно работать как с диапазоном. То есть совокупностью смежных ячеек, занимающих прямоугольную область.
Адрес матрицы – левая верхняя и правая нижняя ячейка диапазона, указанные черед двоеточие.
Формулы массива
Построение матрицы средствами Excel в большинстве случаев требует использование формулы массива. Основное их отличие – результатом становится не одно значение, а массив данных (диапазон чисел).
Порядок применения формулы массива:
- Выделить диапазон, где должен появиться результат действия формулы.
- Ввести формулу (как и положено, со знака «=»).
- Нажать сочетание кнопок Ctrl + Shift + Ввод.
В строке формул отобразится формула массива в фигурных скобках.
Чтобы изменить или удалить формулу массива, нужно выделить весь диапазон и выполнить соответствующие действия. Для введения изменений применяется та же комбинация (Ctrl + Shift + Enter). Часть массива изменить невозможно.
Решение матриц в Excel
С матрицами в Excel выполняются такие операции, как: транспонирование, сложение, умножение на число / матрицу; нахождение обратной матрицы и ее определителя.
Транспонирование
Транспонировать матрицу – поменять строки и столбцы местами.
Сначала отметим пустой диапазон, куда будем транспонировать матрицу. В исходной матрице 4 строки – в диапазоне для транспонирования должно быть 4 столбца. 5 колонок – это пять строк в пустой области.
- 1 способ. Выделить исходную матрицу. Нажать «копировать». Выделить пустой диапазон. «Развернуть» клавишу «Вставить». Открыть меню «Специальной вставки». Отметить операцию «Транспонировать». Закрыть диалоговое окно нажатием кнопки ОК.
- 2 способ. Выделить ячейку в левом верхнем углу пустого диапазона. Вызвать «Мастер функций». Функция ТРАНСП. Аргумент – диапазон с исходной матрицей.
Нажимаем ОК. Пока функция выдает ошибку. Выделяем весь диапазон, куда нужно транспонировать матрицу. Нажимаем кнопку F2 (переходим в режим редактирования формулы). Нажимаем сочетание клавиш Ctrl + Shift + Enter.
Преимущество второго способа: при внесении изменений в исходную матрицу автоматически меняется транспонированная матрица.
Сложение
Складывать можно матрицы с одинаковым количеством элементов. Число строк и столбцов первого диапазона должно равняться числу строк и столбцов второго диапазона.
В первой ячейке результирующей матрицы нужно ввести формулу вида: = первый элемент первой матрицы + первый элемент второй: (=B2+H2). Нажать Enter и растянуть формулу на весь диапазон.
Умножение матриц в Excel
Чтобы умножить матрицу на число, нужно каждый ее элемент умножить на это число. Формула в Excel: =A1*$E$3 (ссылка на ячейку с числом должна быть абсолютной).
Умножим матрицу на матрицу разных диапазонов. Найти произведение матриц можно только в том случае, если число столбцов первой матрицы равняется числу строк второй.
В результирующей матрице количество строк равняется числу строк первой матрицы, а количество колонок – числу столбцов второй.
Для удобства выделяем диапазон, куда будут помещены результаты умножения. Делаем активной первую ячейку результирующего поля. Вводим формулу: =МУМНОЖ(A9:C13;E9:H11). Вводим как формулу массива.
Обратная матрица в Excel
Ее имеет смысл находить, если мы имеем дело с квадратной матрицей (количество строк и столбцов одинаковое).
Размерность обратной матрицы соответствует размеру исходной. Функция Excel – МОБР.
Выделяем первую ячейку пока пустого диапазона для обратной матрицы. Вводим формулу «=МОБР(A1:D4)» как функцию массива. Единственный аргумент – диапазон с исходной матрицей. Мы получили обратную матрицу в Excel:
Нахождение определителя матрицы
Это одно единственное число, которое находится для квадратной матрицы. Используемая функция – МОПРЕД.
Ставим курсор в любой ячейке открытого листа. Вводим формулу: =МОПРЕД(A1:D4).
Таким образом, мы произвели действия с матрицами с помощью встроенных возможностей Excel.
Как работает формула СУММПРОИЗВ с одним условием
Для начала давайте разберем более простой случай. Нужно сравнить числа в двух столбцах построчно и сообщить, сколько раз числа колонки C меньше, чем B (то есть, план продаж не выполнен):
Если вы выделите часть (C2:C10<B2:B10) в строке формул и нажмете F9, чтобы просмотреть вычисленные значения, вы увидите следующую картину:
Здесь мы имеем массив логических значений ИСТИНА и ЛОЖЬ, где ИСТИНА означает, что указанное условие выполнено (т. е. значение в столбце С меньше значения в колонке В в той же строке), а ЛОЖЬ указывает, что условие не выполнено.
Двойное отрицание (—) преобразует логические значения ИСТИНА и ЛОЖЬ в единицы и нули: {0:1:0:0:1:0:1:0:0}.
Другой способ преобразовать логические значения в числа — умножить массив на 1:
В любом случае, поскольку в данном случае в формуле СУММПРОИЗВ использован только один массив, она просто складывает единицы в результирующем массиве, и мы получаем желаемое количество. Несложно, не так ли?
2.2. Создание и изменение формул массива
Для того чтобы правильно ввести формулу массива, нужно
выделить на листе область, размеры которой совпадают с ожидаемым
результатом.
Если выделить слишком большую область, то при вычислении
избыточные ячейки будут заполнены символами ошибки
#N/A. Если область вывода будет меньше, чем нужно, то часть
результатов пропадет. После выделения области, в
Formula Bar записывается
формула и нажимается CTRL+SHIFT+ENTER.
Альтернативно, сначала можно ввести формулу в одну
ячейку, затем отметить область вывода, начиная с этой ячейки (право и
вниз), потом перейти в Formula Bar и нажать
CTRL+SHIFT+ENTER.
Для того, чтобы изменить формулу массива нужно выделить
область содержащую результат. Затем нужно перейти
Formula Bar. При этом фигурные скобки вокруг формулы
{ } исчезнут. После этого формулу можно
изменить и нажать CTRL+SHIFT+ENTER.
Для того чтобы расширить область, которую занимает
формула массива, достаточно выделить для нее новую область, перейти в
Formula Bar и нажать CTRL+SHIFT+ENTER. А вот для того, чтобы уменьшить эту область
(например, чтобы избавиться от символов #N/A)
придется потратить больше сил. Сначала нужно встать на любую ячейку
области, перейти в Formula Bar и скопировать
строку формулы. Затем нужно стереть содержимое старой области и отметить
новую, меньшую область. После этого опять перейти в
Formula Bar, вставить формулу и нажать CTRL+SHIFT+ENTER.
Изменять отдельные ячейки в формуле массива нельзя. При
попытке сделать это появляется предупреждение .
Рис.27 Предупреждение о недопустимой операции с формулой массива
Как преобразовать в excel столбец в строку?
Когда возникает вопрос преобразования в excel столбца в строку, то имеется ввиду перемещение данных находящихся в столбце в строку. Или так называемое транспонирование. Весьма распространенная ситуация при перекладке данных из одного формата в другой.
Для того чтобы преобразовать в excel столбец в строку можно воспользоваться двумя способами:
Выделяем данные в столбце и копируем их. Затем правой кнопкой мыши вызываем контекстное меню, и выбираем пункт Специальная вставка. В появившемся диалоговом окне ставим галочку «транспонировать» и нажимаем ОК.
Данные из столбца в excel будут преобразованы (транспонированы) в строку.
Функция ТРАНСП преобразует вертикальный диапазон ячеек (столбец) в горизонтальный (строку).
=ТРАНСП(массив), где массив – преобразуемый из столбца в строку массив данных.
Чтобы транспонировать данные в excel из столбца в строку, при помощи этой функции необходимо:
1) Выделить горизонтальный диапазон: внимание! – с количеством ячеек соответствующий количеству ячеек в вертикальном диапазоне. 2) В первую ячейку горизонтального диапазона ввести формулу =ТРАНСП(массив), где массив – это вертикальный диапазон ячеек
2) В первую ячейку горизонтального диапазона ввести формулу =ТРАНСП(массив), где массив – это вертикальный диапазон ячеек.
3) Нажать комбинацию клавиш Ctrl + Shift + Enter. Формула будет введена как формула массива в фигурных скобках
Какой способ выбрать при преобразовании в excel столбца в строку?
В первом случае, данные будут вставлены значениями, без связи с источником. Во втором случае, данные из столбца будут преобразованы в строку с сохранением связи с источником, и при изменении данных в столбце, будут меняться данные в строке.
Как в excel преобразовать строки в столбцы?
Как вы правильно догадались, чтобы преобразовать данные в excel из строки в столбец нужно проделать аналогичные операции, что и при преобразовании столбца в строку. Можно воспользоваться как специальной вставкой, так и функцией ТРАНСП.