Operações com Matrizes no Excel

Prévia do material em texto

Excel INTERMEDIÁRIO
Matrizes
Prof. Cassiano Isler
2017.1 - Turma 3
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Agenda
Matrizes no Excel
Notação Matricial
Operações com Matrizes
Matriz Transposta
Determinante
Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 2 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Bibliografia
GÓMEZ, Luis Alberto. Excel para engenheiros. Visual Books,
2009. Caṕıtulo 4. Dispońıvel na biblioteca UFSC-Joinville.
Slides das aulas e material complementar dispońıveis em:
Curso Intermediário Excel
Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 3 / 21
http://cursosextensao.paginas.ufsc.br/intermediario/
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Matrizes no Excel
A propriedade do Excel de representar uma tabela por um
conjunto de células individuais pode ser utilizada para o
cálculo matricial através de operadores matemáticos básicos
e de funções predefinidas pelo desenvolvedor do software.
Uma matriz pode ser representada em uma planilha do Excel
como um conjunto cont́ıguo de células que contém valores
provenientes da inserção manual do usuário ou como resul-
tado de funções que têm como parâmetros os valores de
outras células.
Será apresentada uma revisão sobre representação de matri-
zes antes de abordar as possibilidades de cálculo matricial
no software.
Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 4 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Notação Matricial
Vetor Linha (1 x n): é uma matriz com uma linha e
n colunas.
A =
[
a11 a12 · · · a1n
]
Vetor Coluna (m x 1): é uma matriz com m linhas e
1 coluna.
A =

a11
a21
...
am1

Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 5 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Notação Matricial
Matriz Quadrada (m x m): é uma matriz com m
linhas e m colunas.
A =

a11 a12 · · · a1m
a21 a22 · · · a2m
...
...
. . .
...
am1 am2 · · · amm

Matriz Retangular (m x n): é uma matriz com m
linhas e n colunas tal que m 6= n.
A =

a11 a12 · · · a1n
a21 a22 · · · a2n
...
...
. . .
...
am1 am2 · · · amn

Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 6 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Notação Matricial
Matriz Diagonal: é uma matriz quadrada em que os ele-
mentos da diagonal principal são diferentes de zero e todos
os demais elementos são nulos.
A =

a11 0 · · · 0
0 a22 · · · 0
...
...
. . .
...
0 0 · · · amm

Matriz Identidade: é uma matriz diagonal cujos elementos
da diagonal principal têm valor igual a 1.
I =

1 0 · · · 0
0 1 · · · 0
...
...
. . .
...
0 0 · · · 1

Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 7 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Operações com Matrizes
Ao executar cálculos com matrizes é necessário saber:
A dimensão da matriz resultante da operação que será exe-
cutada, a partir das dimensões das matrizes originais envol-
vidas nos cálculos.
Que uma função que utiliza matrizes no Excel é executada
após a inserção do śımbolo de igualdade (=), seguido do
nome da função e dos seus parâmetros na célula que resul-
tará no elemento da primeira e da primeira coluna da matriz.
Em seguida, um intervalo de células equivalente à matriz
resultante deverá ser selecionado, e a tecla “F2” deve ser
pressionada, seguida das teclas “Ctrl + Shift + Enter”.
Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 8 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Operações com Matrizes
Soma com escalar: é a soma de todos os elementos de
uma matriz de qualquer dimensão com um valor numérico.
B =

a11 a12 · · · a1n
a21 a22 · · · a2n
.
.
.
.
.
.
. . .
.
.
.
am1 am2 · · · amn
 + e =

a11 + e a12 + e · · · a1n + e
a21 + e a22 + e · · · a2n + e
.
.
.
.
.
.
. . .
.
.
.
am1 + e am2 + e · · · amn + e

Subtração de escalar: é a subtração de todos os elementos
de uma matriz de qualquer dimensão de um valor numérico.
B =

a11 a12 · · · a1n
a21 a22 · · · a2n
.
.
.
.
.
.
. . .
.
.
.
am1 am2 · · · amn
− e =

a11 − e a12 − e · · · a1n − e
a21 − e a22 − e · · · a2n − e
.
.
.
.
.
.
. . .
.
.
.
am1 − e am2 − e · · · amn − e

Para ambos os casos é necessário travar a célula no Excel
que contém “e” para garantir a operação por elemento.
Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 9 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Operações com Matrizes
Multiplicação por escalar: é a multiplicação dos elementos
de uma matriz de qualquer dimensão por um valor numérico.
B =

a11 a12 · · · a1n
a21 a22 · · · a2n
.
.
.
.
.
.
. . .
.
.
.
am1 am2 · · · amn
 · e =

a11 · e a12 · e · · · a1n · e
a21 · e a22 · e · · · a2n · e
.
.
.
.
.
.
. . .
.
.
.
am1 · e am2 · e · · · amn · e

Divisão por escalar: é a divisão dos elementos de uma
matriz de qualquer dimensão por um valor numérico.
B =

a11 a12 · · · a1n
a21 a22 · · · a2n
.
.
.
.
.
.
. . .
.
.
.
am1 am2 · · · amn
 /e =

a11/e a12/e · · · a1n/e
a21/e a22/e · · · a2n/e
.
.
.
.
.
.
. . .
.
.
.
am1/e am2/e · · · amn/e

Para ambos os casos é necessário travar a célula no Excel
que contém “e” para garantir a operação por elemento.
Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 10 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Operações com Matrizes
Soma de matrizes: é a soma de todos elementos, um a
um, de duas matrizes de mesma dimensão.
C = A + B
A =

a11 a12 · · · a1n
a21 a22 · · · a2n
.
.
.
.
.
.
. . .
.
.
.
am1 am2 · · · amn
 B =

b11 b12 · · · b1n
b21 b22 · · · b2n
.
.
.
.
.
.
. . .
.
.
.
bm1 bm2 · · · bmn

C =

a11 + b11 a12 + b12 · · · a1n + b1n
a21 + b21 a22 + b22 · · · a2n + b2n
.
.
.
.
.
.
. . .
.
.
.
am1 + bm1 am2 + bm2 · · · amn + bmn

Para duas matrizes de dimensão m x n o resultado da soma
é uma matriz m x n.
Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 11 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Operações com Matrizes
Subtração de matrizes: é a subtração de todos elementos,
um a um, de duas matrizes de mesma dimensão.
C = A− B
A =

a11 a12 · · · a1n
a21 a22 · · · a2n
.
.
.
.
.
.
. . .
.
.
.
am1 am2 · · · amn
 B =

b11 b12 · · · b1n
b21 b22 · · · b2n
.
.
.
.
.
.
. . .
.
.
.
bm1 bm2 · · · bmn

C =

a11 − b11 a12 − b12 · · · a1n − b1n
a21 − b21 a22 − b22 · · · a2n − b2n
.
.
.
.
.
.
. . .
.
.
.
am1 − bm1 am2 − bm2 · · · amn − bmn

Para duas matrizes de dimensão m x n o resultado da sub-
tração é uma matriz m x n.
Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 12 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Operações com Matrizes
Multiplicação de matrizes: correspondeà soma do pro-
duto entre os elementos da m-ésima linha da primeira matriz
e da n-ésima coluna da segunda matriz.
C = A · B
A =

a11 · · · a1n
.
.
.
. . .
.
.
.
am1 · · · amn
 B =

b11 · · · b1m
.
.
.
. . .
.
.
.
bn1 · · · bnm

C =

a11 · b11 + · · · + a1n · bn1 · · · a11 · b1m + · · · + a1n · bnm
.
.
.
. . .
.
.
.
am1 · b11 + · · · + amn · bn1 · · · am1 · b1m + · · · + amn · bnm

Para duas matrizes de dimensões A = m x n e B = n x m
o resultado da multiplicação de A por B é uma matriz de
dimensão m x m.
Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 13 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Operações com Matrizes
“Divisão” de matrizes: a operação de divisão de matrizes
efetivamente não existe, o que ocorre é a identificação de
uma matriz inversa, que multiplicada por outra resulta em
uma terceira.
A ·B = C ⇒ B = C ·A−1
O conceito de matriz inversa está associado à identificação
da matriz A−1 que multiplicada pela matriz A resulta em
uma matriz identidade I.
A−1 ·A = I

a11
−1 · · · a1n
−1
.
.
.
. . .
.
.
.
am1
−1 · · · amn
−1
 ·

a11 · · · a1n
.
.
.
. . .
.
.
.
am1 · · · amn
 =

1 · · · 0
.
.
.
. . .
.
.
.
0 · · · 1

Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 14 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Operações com Matrizes
O Excel tem uma função para a multiplicação de matrizes,
dada pela expressão “MATRIZ.MULT(matriz1;matriz2)”,
onde “matriz1” e “matriz2” são as matrizes que se deseja
aplicar a operação, respeitando a condição de dimensões
estabelecidas no slide 8 para essa operação.
Lembre-se que, ao executar a multiplicação, o intervalo
de células selecionadas na planilha deve ser exatamente o
mesmo da dimensão da matriz esperada como resultado da
operação.
O software também tem um uma função espećıfica para ob-
tenção da matriz inversa de uma matriz original, dada pelo
comando “MATRIZ.INVERSO(matriz)”, onde “matriz” é a
matriz que se deseja obter a inversa.
Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 15 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Matriz Transposta
Uma matriz transposta é a conversão das colunas de uma
matriz em linhas e das linhas em colunas.
A =

a11 a12 · · · a1n
a21 a22 · · · a2n
...
...
. . .
...
am1 am2 · · · amn

AT =

a11 a21 · · · am1
a12 a22 · · · am2
...
...
. . .
...
a1n a2n · · · amn

A função “TRANSPOR(matriz)” realiza essa operação,
onde “matriz” é a matriz original que se deseja obter a
transposta.
Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 16 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Determinante
O determinante de uma matriz é uma função que asso-
cia um escalar a uma matriz, necessariamente, quadrada,
transformando-a em um número real.
det
a b c
d e f
g h i
 = (aei+ bfg + cdh)− (afh+ bdi+ ceg).
O Excel tem a função “MATRIZ.DETERM(matriz)”, que
resulta no valor do determinante de uma matriz quadrada,
onde “matriz” é o parâmetro de entrada.
Se o determinante de uma matriz é diferente de zero, então
essa matriz tem uma inversa posśıvel de ser calculada.
Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 17 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Exerćıcios
(1) Considere uma matriz A indicada no arquivo da aula
disponibilizado no site do curso.
Calcule a transposta AT .
Calcule o determinante da matriz original A para
verificar se tem inversa.
Calcule a inversa A−1.
Calcule a inversa da transposta (AT )−1.
Verifique se o cálculo da inversa da transposta está
correto pelo resultado de (AT )−1 ·AT .
Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 18 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Exerćıcios
(2) Os métodos de regressão linear são utilizados para es-
timar parâmetros de uma função para representar o va-
lor de uma variável dependente y e função de diferentes
variáveis independentes x1, x2, · · · , xn.
y = β0 + β1 · x1 + β2 · x2 + · · ·+ βn · xn
Dado um conjunto de k obervações de y para diferentes
valores de x1, x2, · · · , xn é posśıvel estimar os valores de
β1, β2, · · · , βn. Na forma matricial esses valores podem
ser representados por:
y =

y1
y2
.
.
.
yk
 x =

1 x11 · · · x1n
1 x21 · · · x2n
.
.
.
.
.
.
.
.
.
1 xk1 · · · xkn
 B =

β0
β1
.
.
.
βn

Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 19 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Exerćıcios
(2) Continuação...
Resumidamente, o cálculo dos valores do vetor B que mi-
nimizam o erro entre os valores observados de y e aqueles
estimados pelo modelo de regressão linear são:
B =
(
xT · x
)−1 · xT · y
Considere sete observações de valores de uma variável y
segundo os valores de três variáveis x1 x2 e x3 conforme
indicado no arquivo da aula disponibilizado no site do curso.
Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 20 / 21
Agenda
Bibliografia
Matrizes no
Excel
Notação
Matricial
Operações
com Matrizes
Matriz
Transposta
Determinante
Exerćıcios
Exerćıcios
(2) Continuação...
Represente as matrizes y, x e B nas células da planilha.
Calcule xT com o resultado em uma nova matriz.
Calcule
(
xT · x
)
com o resultado em uma nova matriz.
Calcule
(
xT · x
)−1
com o resultado em uma nova matriz.
Calcule B com o resultado em uma nova matriz.
Calcule B como uma função única ds cálculos anteriores, tal
que a matriz resultante é a dos parâmetros da regressão.
Calcule o valor de y para cada valor de x1, x2 e x3 e verifique
os desvios entre os valores observados e calculados daquela
variável. Tente usar a função “SOMARPRODUTO”.
Prof. Cassiano Isler Excel INTERMEDIÁRIO - Aula 3 21 / 21
	Agenda
	Bibliografia
	Matrizes no Excel
	Notação Matricial
	Operações com Matrizes
	Matriz Transposta
	Determinante
	Exercícios