VBA user tried using Python / R: Matrix

Introduction

I am a VBA user who started studying machine learning. As a memorandum, I would like to summarize the Python / R grammar while comparing it with VBA.

table of contents

-Matrix -Create Matrix -mxn matrix -Identity matrix -Zero Matrix -Diagonal matrix -Other -Matrix Attributes -Get Matrix Elements -Calculation of matrix -Calculation of Linear Algebra -Calculation for each element -Summary -List -Whole program

queue

This time, I will talk about matrix calculation, which is indispensable for machine learning.

Creating a matrix

mxn matrix

A = \begin{pmatrix}
1 & 2 & 3 & 4 \\
5 & 6 & 7 & 8 \\
9 & 10 & 11 & 12
\end{pmatrix}

Python In Python, we use the numerical calculation library NumPy (import numpy). Numpy is often abbreviated as np (import numpy as np). Use a matrix as a two-dimensional array of Numpy.

Python3


import numpy as np

A = np.array(range(1,12+1))
print(A)
# [ 1  2  3  4  5  6  7  8  9 10 11 12]
A = np.array(range(1,12+1)).reshape(3,4)
A = np.array(range(1,12+1)).reshape(3,-1)
A = np.array(range(1,12+1)).reshape(-1,4)
print(A)
# [[ 1  2  3  4]
#  [ 5  6  7  8]
#  [ 9 10 11 12]]

A = np.array([[1,2,3,4],
              [5,6,7,8],
              [9,10,11,12]])
A = np.array([np.array(range(1,4+1)),
              np.array(range(5,8+1)),
              np.array(range(9,12+1))])
A = np.array([np.array(range(1,4+1)),
              np.array(range(1,4+1))+4,
              np.array(range(1,4+1))+8])
print(A)
# [[ 1  2  3  4]
#  [ 5  6  7  8]
#  [ 9 10 11 12]]

R

R


A <- matrix(c(1,2,3,4,5,6,7,8,9,10,11,12), 3, 4, byrow=TRUE)
A <- matrix(1:12, nrow=3, ncol=4, byrow=TRUE)
A <- matrix(1:12, nr=3, nc=4, b=T)
A <- matrix(1:12, 3, 4, byrow=TRUE)
A
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8
# [3,]    9   10   11   12

A <- rbind(c(1,2,3,4),c(5,6,7,8),c(9,10,11,12))
A <- rbind(1:4,5:8,9:12)
A <- rbind(1:4,1:4+4,1:4+8)
A
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8
# [3,]    9   10   11   12

A <- cbind(c(1,5,9),c(2,6,10),c(3,7,11),c(4,8,12))
A <- cbind(c(1,5,9),c(1,5,9)+1,c(1,5,9)+2,c(1,5,9)+3)
A
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8
# [3,]    9   10   11   12

VBA In VBA, for example, when the matrix A is entered in the B3: E5 cell range of an Excel worksheet, you can populate the array A as follows. In this case, the array A is two-dimensional and the indexes are 1 to 3 and 1 to 4.

VBA


Dim A As Variant
A = Range("B3:E5").Value

Identity matrix

I_3 = \begin{pmatrix}
1 & 0 & 0 \\
0 & 1 & 0 \\
0 & 0 & 1
\end{pmatrix}

Python

Python3


print(np.eye(3))
# [[1. 0. 0.]
#  [0. 1. 0.]
#  [0. 0. 1.]]

eye is probably the identity matrix $ I $.

R

R


diag(3)
diag(1, 3)
diag(1, 3, 3)
#      [,1] [,2] [,3]
# [1,]    1    0    0
# [2,]    0    1    0
# [3,]    0    0    1

VBA In VBA, you can use Munit of WorksheetFunction (Excel worksheet function).

VBA


Dim Eye3 As Variant
Eye3 = WorksheetFunction.Munit(3)

Zero matrix

O = \begin{pmatrix}
0 & 0 & 0 & 0 \\
0 & 0 & 0 & 0 \\
0 & 0 & 0 & 0
\end{pmatrix}

Python

Python3


print(np.zeros((3,4)))
# [[0. 0. 0. 0.]
#  [0. 0. 0. 0.]
#  [0. 0. 0. 0.]]

R

R


matrix(0, 3, 4)
diag(0, 3, 4)
#      [,1] [,2] [,3] [,4]
# [1,]    0    0    0    0
# [2,]    0    0    0    0
# [3,]    0    0    0    0
diag(0, 3)
#      [,1] [,2] [,3]
# [1,]    0    0    0
# [2,]    0    0    0
# [3,]    0    0    0

VBA

VBA


Diagonal matrix

D = \begin{pmatrix}
1 & 0 & 0 \\
0 & 2 & 0 \\
0 & 0 & 3
\end{pmatrix}

Python

Python3


print(np.diag([1,2,3]))
# [[1 0 0]
#  [0 2 0]
#  [0 0 3]]

R

R


diag(c(1,2,3))
#      [,1] [,2] [,3]
# [1,]    1    0    0
# [2,]    0    2    0
# [3,]    0    0    3

VBA

VBA


Other

Python In Python, there are also functions that create such matrices.

Python3


print(np.ones((3,4)))
# [[1. 1. 1. 1.]
#  [1. 1. 1. 1.]
#  [1. 1. 1. 1.]]

print(np.full((3,4), 5))
# [[5 5 5 5]
#  [5 5 5 5]
#  [5 5 5 5]]

Matrix attributes

A = \begin{pmatrix}
1 & 2 & 3 & 4 \\
5 & 6 & 7 & 8 \\
9 & 10 & 11 & 12
\end{pmatrix}

Python

Python3


A = np.array(range(1,12+1)).reshape(3,4)
print(A)
# [[ 1  2  3  4]
#  [ 5  6  7  8]
#  [ 9 10 11 12]]

print(A.shape)    # (Number of lines,Number of columns)
# (3, 4)
print(len(A))
print(A.shape[0]) #Number of lines
# 3
print(A.shape[1]) #Number of columns
# 4
print(A.ndim)     #Dimension (matrix is ​​two-dimensional)
# 2
print(type(A))
# <class 'numpy.ndarray'>
print(A.dtype)
# int32

R

R


A <- matrix(1:12, 3, 4, byrow=TRUE)
A
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8
# [3,]    9   10   11   12

nrow(A)   #Number of lines
# 3
ncol(A)   #Number of columns
# 4
dim(A)    #Number of lines,Number of columns
# 3 4
class(A)
# "matrix"
typeof(A)
# "integer"

VBA For example, suppose matrix A is entered in the B3: E5 cell range of an Excel worksheet. The array A is two-dimensional and has indexes 1 to 3 and 1 to 4.

VBA


Dim A As Variant
A = Range("B3:E5").Value
'Number of rows in the matrix
Debug.Print LBound(A, 1), UBound(A, 1), UBound(A, 1) - LBound(A, 1) + 1
'Number of columns in the matrix
Debug.Print LBound(A, 2), UBound(A, 2), UBound(A, 2) - LBound(A, 2) + 1

Get matrix elements

A = \begin{pmatrix}
1 & 2 & 3 & 4 \\
5 & 6 & 7 & 8 \\
9 & 10 & 11 & 12
\end{pmatrix}

Python

Python3


A = np.array(range(1,12+1)).reshape(3,4)
print(A)
# [[ 1  2  3  4]
#  [ 5  6  7  8]
#  [ 9 10 11 12]]

#component
print(A[0,1])
print(A[1-1,2-1])
print(A[0][1])
# 2

#Row vector
print(A[0,:])
# [1 2 3 4]

#Column vector
print(A[:,1])
# [ 2  6 10]

#Partially taken out
print(A[[0],:])
# [[1 2 3 4]]

print(A[:,[1]])
# [[ 2]
#  [ 6]
#  [10]]

print(A[0:2,])
# [[1 2 3 4]
#  [5 6 7 8]]

print(A[:,1:4])
# [[ 2  3  4]
#  [ 6  7  8]
#  [10 11 12]]

print(A[-2:,:])
# [[ 5  6  7  8]
#  [ 9 10 11 12]]

print(A[:,-2:])
# [[ 3  4]
#  [ 7  8]
#  [11 12]]

print(A[1:3, 2:4])
# [[ 7  8]
#  [11 12]]

print(A[[0,2],:])
# [[ 1  2  3  4]
#  [ 9 10 11 12]]

print(A[:,[1,3]])
# [[ 2  4]
#  [ 6  8]
#  [10 12]]

print(A[[0,2],[1,3]])   #This doesn't work
# [ 2 12]
print(A[np.ix_([0,2],[1,3])])
# [[ 2  4]
#  [10 12]]
print(np.ix_([0,2],[1,3]))
# (array([[0],
#        [2]]), array([[1, 3]]))

print(A[:,:])
# [[ 1  2  3  4]
#  [ 5  6  7  8]
#  [ 9 10 11 12]]

R

R


A <- matrix(1:12, 3, 4, byrow=TRUE)
A
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8
# [3,]    9   10   11   12

#component
A[1,2]
# [1] 2

#Row vector
A[1,]
# [1] 1 2 3 4

#Column vector
A[,2]
# [1]  2  6 10

#Partially taken out
A[1:2,]
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8

A[,2:3]
#      [,1] [,2]
# [1,]    2    3
# [2,]    6    7
# [3,]   10   11

A[2:3,3:4]
#      [,1] [,2]
# [1,]    7    8
# [2,]   11   12

A[c(1,3),]
# 
# [1,]    1    2    3    4
# [2,]    9   10   11   12

A[,c(2,4)]
#      [,1] [,2]
# [1,]    2    4
# [2,]    6    8
# [3,]   10   12

A[c(1,3),c(2,4)]
#      [,1] [,2]
# [1,]    2    4
# [2,]   10   12

A[,]
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8
# [3,]    9   10   11   12

head(A)
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8
# [3,]    9   10   11   12
head(A, 2)
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8
tail(A, 2)
#      [,1] [,2] [,3] [,4]
# [2,]    5    6    7    8
# [3,]    9   10   11   12

VBA For example, suppose matrix A is entered in the B3: E5 cell range of an Excel worksheet.

VBA


Dim A As Variant
A = Range("B3:E5").Value
'Of the procession(1, 2)component
Debug.Print A(1, 2)

Matrix calculation

Calculation of linear algebra

A = \begin{pmatrix}
1 & 2  \\
3 & 4
\end{pmatrix}
,
B = \begin{pmatrix}
5 & 6  \\
7 & 8
\end{pmatrix}
k = 2,
kA = \begin{pmatrix}
2 & 4 \\
6 & 8
\end{pmatrix}
A + B = \begin{pmatrix}
6 & 8 \\
10 & 12
\end{pmatrix}
,
AB = \begin{pmatrix}
19 & 22 \\
43 & 50
\end{pmatrix}
A^T = \begin{pmatrix}
1 & 3 \\
2 & 4
\end{pmatrix}
,
A^{-1} = \begin{pmatrix}
-2 & 1 \\
1.5 & -0.5
\end{pmatrix}
detA = -2
,
trA = 5

Python

Python3


A = np.array(range(1,4+1)).reshape(2,2)
print(A)
# [[1 2]
#  [3 4]]
B = np.array(range(5,8+1)).reshape(2,2)
print(B)
# [[5 6]
#  [7 8]]

#Matrix scalar times
k = 2
print(A * k)
# [[2 4]
#  [6 8]]

#Matrix sum
print(A + B)
# [[ 6  8]
#  [10 12]]

#Matrix multiplication
print(A @ B)
# [[19 22]
#  [43 50]]
print(np.dot(A, B))
# [[19 22]
#  [43 50]]

#Transpose matrix
print(A.transpose())
# [[1 3]
#  [2 4]]
print(A.T)
# [[1 3]
#  [2 4]]

#Inverse matrix
print(np.linalg.inv(A))
# [[-2.   1. ]
#  [ 1.5 -0.5]]

#Identity matrix
print(np.eye(2))
# [[1. 0.]
#  [0. 1.]]

#Determinant detA
print(np.linalg.det(A))
# -2.0000000000000004

#Diagonal component
print(np.diag(A))
# [1 4]

#Trace (sum of diagonal components) trA
print(sum(np.diag(A)))
# 5

#Matrix flattening (vectorization)
print(A.flatten())
# [1 2 3 4]

#Matrix transformation
print(A.reshape(1,4))
# [[1 2 3 4]]
print(A.reshape(4,1))
# [[1]
#  [2]
#  [3]
#  [4]]

R

R


A <- matrix(1:4, 2, 2, TRUE)
A
#      [,1] [,2]
# [1,]    1    2
# [2,]    3    4

B <- matrix(5:8, 2, 2, T)
B
#      [,1] [,2]
# [1,]    5    6
# [2,]    7    8

#Matrix scalar times
k <- 2
A * k
#      [,1] [,2]
# [1,]    2    4
# [2,]    6    8

#Matrix sum
A + B
#      [,1] [,2]
# [1,]    6    8
# [2,]   10   12

#Matrix multiplication
A %*% B
#      [,1] [,2]
# [1,]   19   22
# [2,]   43   50

#Transpose matrix
t(A)
#      [,1] [,2]
# [1,]    1    3
# [2,]    2    4

#Inverse matrix
solve(A)
#      [,1] [,2]
# [1,] -2.0  1.0
# [2,]  1.5 -0.5

#Identity matrix
diag(2)
#      [,1] [,2]
# [1,]    1    0
# [2,]    0    1

#Determinant detA
det(A)
# -2

#Diagonal component
diag(A)
# [1] 1 4

#Trace (sum of diagonal components) trA
sum(diag(A))
# 5

VBA For example, suppose you have a matrix A in the B3: C5 cell range of an Excel worksheet and a matrix B in the B8: C9 cell range.

VBA


Dim A As Variant
Dim B As Variant
Dim k As Double
Dim kA As Variant
Dim ApB As Variant
Dim AB As Variant
Dim At As Variant
Dim Ainv As Variant
Dim Eye2 As Variant
Dim detA As Double
Dim trA As Double
Dim i As Integer
Dim j As Integer

'queue
A = Range("B3:E5").Value
B = Range("B8:C9").Value

'Number of rows in the matrix
Debug.Print LBound(A, 1), UBound(A, 1), UBound(A, 1) - LBound(A, 1) + 1

'Number of columns in the matrix
Debug.Print LBound(A, 2), UBound(A, 2), UBound(A, 2) - LBound(A, 2) + 1

'Matrix element A(1, 2)
Debug.Print A(1, 2)

'Matrix scalar times kA
k = 2
ReDim kA(LBound(A, 1) To UBound(A, 1), LBound(A, 2) To UBound(A, 2))
For i = LBound(A, 1) To UBound(A, 1)
    For j = LBound(A, 2) To UBound(A, 2)
        kA(i, j) = A(i, j) * k
    Next j
Next i
Range("E13:F14").Value = kA

'Matrix sum A+B
ReDim ApB(LBound(A, 1) To UBound(A, 1), LBound(A, 2) To UBound(A, 2))
For i = LBound(A, 1) To UBound(A, 1)
    For j = LBound(A, 2) To UBound(A, 2)
        ApB(i, j) = A(i, j) + B(i, j)
    Next j
Next i
Range("E18:F19").Value = ApB

'Matrix product AB
AB = WorksheetFunction.MMult(A, B)
Range("E18:F19").Value = AB

'Transpose matrix A^T
At = WorksheetFunction.Transpose(A)
Range("E28:F29").Value = At

'Inverse matrix A^(-1)
Ainv = WorksheetFunction.MInverse(A)
Range("E33:F34").Value = Ainv

'Identity matrix
Eye2 = WorksheetFunction.Munit(2)
Range("E38:F39").Value = Eye2

'Determinant detA
detA = WorksheetFunction.MDeterm(A)
Range("E42").Value = detA

'Trace trA
trA = WorksheetFunction.SumProduct(A, Eye2)
Range("E45").Value = trA

Operation for each element

In Python and R, apart from the calculation of linear algebra, you can calculate each element in the same way as the scalar operation.

Python

Python3


print(A + B)
# [[ 6  8]
#  [10 12]]

print(A - B)
# [[-4 -4]
#  [-4 -4]]

print(A * B)
# [[ 5 12]
#  [21 32]]

print(A / B)
# [[0.2        0.33333333]
#  [0.42857143 0.5       ]]

print(A * 2)
# [[2 4]
#  [6 8]]

print(A ** 2)
# [[ 1  4]
#  [ 9 16]]

print(-A)
# [[-1 -2]
#  [-3 -4]]

print(A == B)
# [[False False]
#  [False False]]

print(A > 2)
# [[False False]
#  [ True  True]]

print(A % 2 == 0)
# [[False  True]
#  [False  True]]

print(np.exp(A))
# [[ 2.71828183  7.3890561 ]
#  [20.08553692 54.59815003]]

print(np.log(A))
# [[0.         0.69314718]
#  [1.09861229 1.38629436]]

R

R


A + B
#      [,1] [,2]
# [1,]    6    8
# [2,]   10   12

A - B
#      [,1] [,2]
# [1,]   -4   -4
# [2,]   -4   -4

A * B
#      [,1] [,2]
# [1,]    5   12
# [2,]   21   32

A / B
#           [,1]      [,2]
# [1,] 0.2000000 0.3333333
# [2,] 0.4285714 0.5000000

A * 2
#      [,1] [,2]
# [1,]    2    4
# [2,]    6    8

A ^ 2
#      [,1] [,2]
# [1,]    1    4
# [2,]    9   16

-A
#      [,1] [,2]
# [1,]   -1   -2
# [2,]   -3   -4

A == B
#       [,1]  [,2]
# [1,] FALSE FALSE
# [2,] FALSE FALSE

A < B
#      [,1] [,2]
# [1,] TRUE TRUE
# [2,] TRUE TRUE

A > 2
#       [,1]  [,2]
# [1,] FALSE FALSE
# [2,]  TRUE  TRUE

A %% 2 == 0
#       [,1] [,2]
# [1,] FALSE TRUE
# [2,] FALSE TRUE

exp(A)
#           [,1]      [,2]
# [1,]  2.718282  7.389056
# [2,] 20.085537 54.598150

log(A)
#          [,1]      [,2]
# [1,] 0.000000 0.6931472
# [2,] 1.098612 1.3862944

VBA In VBA, you only have to calculate for each (i, j) component in the for statement.

VBA


Summary

List

List the functions used in each language. For comparison, the calculation in EXCEL is also shown. In Python, use Numpy (import numpy as np).

*** Creating a matrix ***

A = \begin{pmatrix}
1 & 2 & 3 & 4 \\
5 & 6 & 7 & 8 \\
9 & 10 & 11 & 12
\end{pmatrix}
,
I_3 = \begin{pmatrix}
1 & 0 & 0 \\
0 & 1 & 0 \\
0 & 0 & 1
\end{pmatrix}
O = \begin{pmatrix}
0 & 0 & 0 & 0 \\
0 & 0 & 0 & 0 \\
0 & 0 & 0 & 0
\end{pmatrix}
,
D = \begin{pmatrix}
1 & 0 & 0 \\
0 & 2 & 0 \\
0 & 0 & 3
\end{pmatrix}
queue Python R VBA EXCEL
mxn matrix
A
np.array(range(1,12+1)).
reshape(3,4)
np.array([
 [1,2,3,4],
 [5,6,7,8],
 [9,10,11,12]])
matrix(1:12,3,4,TRUE)
rbind(c(1,2,3,4),
    c(5,6,7,8),
    c(9,10,11,12))
cbind(c(1,5,9),
    c(2,6,10),
    c(3,7,11),
    c(4,8,12))
Identity matrix
I_3
np.eye(3) diag(3)
diag(1, 3)
diag(1, 3, 3)
WorksheetFunction.
Munit(3)
MUNIT(3)
Zero matrix
O
np.zeros((3,4)) matrix(0, 3, 4)
diag(0, 3, 4)
diag(0, 3)
Diagonal matrix
D
np.diag([1,2,3] diag(c(1,2,3))
1 padded matrix np.ones((3,4))
Numeric padded matrix np.full((3,4), 5)

*** Matrix attributes ***

A = \begin{pmatrix}
1 & 2 & 3 & 4 \\
5 & 6 & 7 & 8 \\
9 & 10 & 11 & 12
\end{pmatrix}
Python R VBA EXCEL result
form A.shape dim(A) 3, 4
Number of lines len(A)
A.shape[0]
nrow(A) UBound(A, 1) 3
Number of columns A.shape[1] ncol(A) UBound(A, 2) 4
dimension(=2) A.ndim 2
type type(A) class(A)
Data type A.dtype typeof(A)

*** Matrix elements ***

A = \begin{pmatrix}
1 & 2 & 3 & 4 \\
5 & 6 & 7 & 8 \\
9 & 10 & 11 & 12
\end{pmatrix}
Python R VBA EXCEL
(1,2)component A[0,1]
A[0][1]
A[1,2] A(1,2) =INDEX(A,1,2)
1st row vector A[0,:]
A[[0],:]
A[1,]
Second column vector A[:,1]
A[:,[1]]
A[,2]
1~2 lines A[0:2,] A[1:2,]
3~4 rows A[:,2:4]
A[:,-2:]
A[,3:4]
1~2 lines,3~4 rows A[0:2,2:4]
A[:2,-2:]
A[1:2,3:4]
Lines 1 and 3 A[[0,2],:] A[c(1,3),]
2 and 4 rows A[:,[1,3]] A[,c(2,4)]
Lines 1 and 3,2 and 4 rows A[np.ix_([0,2],[1,3])] A[c(1,3),c(2,4)]
All lines,All rows A[:,:] A[,]
head head(A)
head(A, 2)
tail tail(A)
tail(A, 2)

*** Linear algebra calculation ***

A = \begin{pmatrix}
1 & 2  \\
3 & 4
\end{pmatrix}
,
B = \begin{pmatrix}
5 & 6  \\
7 & 8
\end{pmatrix}
k = 2,
kA = \begin{pmatrix}
2 & 4 \\
6 & 8
\end{pmatrix}
A + B = \begin{pmatrix}
6 & 8 \\
10 & 12
\end{pmatrix}
,
AB = \begin{pmatrix}
19 & 22 \\
43 & 50
\end{pmatrix}
A^T = \begin{pmatrix}
1 & 3 \\
2 & 4
\end{pmatrix}
,
A^{-1} = \begin{pmatrix}
-2 & 1 \\
1.5 & -0.5
\end{pmatrix}
detA = -2
,
trA = 5
Python R VBA EXCEL
Scalar times
kA
A * k A * k =cell*k
Matrix sum
A+B
A + B A + B =cell+cell
Matrix multiplication
AB
A @ B
np.dot(A, B)
A %*% B WorksheetFunction.
MMult(A, B)
=MMULT(A,B)
Transpose matrix
A^T
A.transpose()
A.T
t(A) WorksheetFunction.
Transpose(A)
=Transpose(A)
Inverse matrix
A^{-1}
np.linalg.inv(A) solve(A) WorksheetFunction.
MInverse(A)
=MINVERSE(A)
Identity matrix
I_2
np.eye(2) diag(2) WorksheetFunction.
Munit(2)
=MUNIT(2)
Determinant
detA
np.linalg.det(A) det(A) WorksheetFunction.
MDeterm(A)
=MDETERM(A)
trace
trA
sum(np.diag(A)) sum(diag(A)) WorksheetFunction.
SumProduct(A,Eye2)
Diagonal component np.diag(A) diag(A)
Matrix flattening A.flatten()
Matrix transformation A.reshape(1,4)

*** Arithmetic for each element of the matrix ***

Python R VBA EXCEL
sum A + B A + B =cell+cell
difference A - B A - B =cell-cell
product A * B A * B =cell*cell
quotient A / B A / B =cell/cell
Scalar times A * 2 A * 2 =cell*2
Exponentiation A ** 2 A ^ 2 =cell^2
Sign inversion -A -A =-cell
equal sign A == B A == B =cell=cell
Inequality sign A < B A < B =cell<cell
Inequality sign A > 2 A > 2 =cell>2
Remainder 0 A % 2 == 0 A %% 2 == 0 =MOD(cell,2)=0
square root np.sqrt(A) sqrt(A) =SQRT(cell)
exp np.exp(A) exp(A) =EXP(cell)
log np.log(A) log(A) =LN(cell)

Whole program

The whole program used for reference is shown.

Python

Python3


import numpy as np

#Creating a matrix
#mxn matrix
A = np.array(range(1,12+1))
print(A)
# [ 1  2  3  4  5  6  7  8  9 10 11 12]
A = np.array(range(1,12+1)).reshape(3,4)
A = np.array(range(1,12+1)).reshape(3,-1)
A = np.array(range(1,12+1)).reshape(-1,4)
print(A)
# [[ 1  2  3  4]
#  [ 5  6  7  8]
#  [ 9 10 11 12]]

A = np.array([[1,2,3,4],
              [5,6,7,8],
              [9,10,11,12]])
A = np.array([np.array(range(1,4+1)),
              np.array(range(5,8+1)),
              np.array(range(9,12+1))])
A = np.array([np.array(range(1,4+1)),
              np.array(range(1,4+1))+4,
              np.array(range(1,4+1))+8])
print(A)
# [[ 1  2  3  4]
#  [ 5  6  7  8]
#  [ 9 10 11 12]]

#Identity matrix
print(np.eye(3))
# [[1. 0. 0.]
#  [0. 1. 0.]
#  [0. 0. 1.]]

#Zero matrix
print(np.zeros((3,4)))
# [[0. 0. 0. 0.]
#  [0. 0. 0. 0.]
#  [0. 0. 0. 0.]]
print(np.zeros((3,3)))
# [[0. 0. 0.]
#  [0. 0. 0.]
#  [0. 0. 0.]]

#Diagonal matrix
print(np.diag([1,2,3]))
# [[1 0 0]
#  [0 2 0]
#  [0 0 3]]

#Other
print(np.ones((3,4)))
# [[1. 1. 1. 1.]
#  [1. 1. 1. 1.]
#  [1. 1. 1. 1.]]

print(np.full((3,4), 5))
# [[5 5 5 5]
#  [5 5 5 5]
#  [5 5 5 5]]


#Matrix attributes
A = np.array(range(1,12+1)).reshape(3,4)
print(A)
# [[ 1  2  3  4]
#  [ 5  6  7  8]
#  [ 9 10 11 12]]

print(A.shape)    # (Number of lines,Number of columns)
# (3, 4)
print(len(A))
print(A.shape[0]) #Number of lines
# 3
print(A.shape[1]) #Number of columns
# 4
print(A.ndim)     #Dimension (matrix is ​​two-dimensional)
# 2
print(type(A))
# <class 'numpy.ndarray'>
print(A.dtype)
# int32


#Get matrix elements
A = np.array(range(1,12+1)).reshape(3,4)
print(A)
# [[ 1  2  3  4]
#  [ 5  6  7  8]
#  [ 9 10 11 12]]

#component
print(A[0,1])
print(A[1-1,2-1])
print(A[0][1])
# 2

#Row vector
print(A[0,:])
# [1 2 3 4]

#Column vector
print(A[:,1])
# [ 2  6 10]

print(A[[0],:])
# [[1 2 3 4]]

print(A[:,[1]])
# [[ 2]
#  [ 6]
#  [10]]

print(A[0:2,])
print(A[:2,])
# [[1 2 3 4]
#  [5 6 7 8]]

print(A[:,2:4])
print(A[:,-2:])
# [[ 3  4]
#  [ 7  8]
#  [11 12]]

print(A[0:2,2:4])
print(A[:2,-2:])
# [[3 4]
#  [7 8]]

print(A[[0,2],:])
# [[ 1  2  3  4]
#  [ 9 10 11 12]]

print(A[:,[1,3]])
# [[ 2  4]
#  [ 6  8]
#  [10 12]]

print(A[[0,2],[1,3]])   #This doesn't work
# [ 2 12]
print(A[np.ix_([0,2],[1,3])])
# [[ 2  4]
#  [10 12]]
print(np.ix_([0,2],[1,3]))
# (array([[0],
#        [2]]), array([[1, 3]]))

print(A[:,:])
# [[ 1  2  3  4]
#  [ 5  6  7  8]
#  [ 9 10 11 12]]


#Calculation of linear algebra
A = np.array(range(1,4+1)).reshape(2,2)
print(A)
# [[1 2]
#  [3 4]]
B = np.array(range(5,8+1)).reshape(2,2)
print(B)
# [[5 6]
#  [7 8]]

#Matrix scalar times
k = 2
print(A * k)
# [[2 4]
#  [6 8]]

#Matrix sum
print(A + B)
# [[ 6  8]
#  [10 12]]

#Matrix multiplication
print(A @ B)
# [[19 22]
#  [43 50]]
print(np.dot(A, B))
# [[19 22]
#  [43 50]]

#Transpose matrix
print(A.transpose())
# [[1 3]
#  [2 4]]
print(A.T)
# [[1 3]
#  [2 4]]

#Inverse matrix
print(np.linalg.inv(A))
# [[-2.   1. ]
#  [ 1.5 -0.5]]

#Identity matrix
print(np.eye(2))
# [[1. 0.]
#  [0. 1.]]

#Determinant detA
print(np.linalg.det(A))
# -2.0000000000000004

#Diagonal component
print(np.diag(A))
# [1 4]

#Trace (sum of diagonal components) trA
print(sum(np.diag(A)))
# 5

#Matrix flattening (vectorization)
print(A.flatten())
# [1 2 3 4]

#Matrix transformation
print(A.reshape(1,4))
# [[1 2 3 4]]
print(A.reshape(4,1))
# [[1]
#  [2]
#  [3]
#  [4]]


#Operations for each element of the matrix
print(A + B)
# [[ 6  8]
#  [10 12]]

print(A - B)
# [[-4 -4]
#  [-4 -4]]

print(A * B)
# [[ 5 12]
#  [21 32]]

print(A / B)
# [[0.2        0.33333333]
#  [0.42857143 0.5       ]]

print(A * 2)
# [[2 4]
#  [6 8]]

print(A ** 2)
# [[ 1  4]
#  [ 9 16]]

print(-A)
# [[-1 -2]
#  [-3 -4]]

print(A == B)
# [[False False]
#  [False False]]

print(A < B)
# [[ True  True]
#  [ True  True]]

print(A > 2)
# [[False False]
#  [ True  True]]

print(A % 2 == 0)
# [[False  True]
#  [False  True]]

print(np.sqrt(A))
# [[1.         1.41421356]
#  [1.73205081 2.        ]]

print(np.exp(A))
# [[ 2.71828183  7.3890561 ]
#  [20.08553692 54.59815003]]

print(np.log(A))
# [[0.         0.69314718]
#  [1.09861229 1.38629436]]

R

R


#Creating a matrix
#mxn matrix
A <- matrix(c(1,2,3,4,5,6,7,8,9,10,11,12), 3, 4, byrow=TRUE)
A <- matrix(1:12, nrow=3, ncol=4, byrow=TRUE)
A <- matrix(1:12, nr=3, nc=4, b=T)
A <- matrix(1:12, 3, 4, byrow=TRUE)
A
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8
# [3,]    9   10   11   12
A <- rbind(c(1,2,3,4),c(5,6,7,8),c(9,10,11,12))
A <- rbind(1:4,5:8,9:12)
A <- rbind(1:4,1:4+4,1:4+8)
A
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8
# [3,]    9   10   11   12
A <- cbind(c(1,5,9),c(2,6,10),c(3,7,11),c(4,8,12))
A <- cbind(c(1,5,9),c(1,5,9)+1,c(1,5,9)+2,c(1,5,9)+3)
A
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8
# [3,]    9   10   11   12

#Identity matrix
diag(3)
diag(1, 3)
diag(1, 3, 3)
#      [,1] [,2] [,3]
# [1,]    1    0    0
# [2,]    0    1    0
# [3,]    0    0    1

#Zero matrix
matrix(0, 3, 4)
diag(0, 3, 4)
#      [,1] [,2] [,3] [,4]
# [1,]    0    0    0    0
# [2,]    0    0    0    0
# [3,]    0    0    0    0
diag(0, 3)
#      [,1] [,2] [,3]
# [1,]    0    0    0
# [2,]    0    0    0
# [3,]    0    0    0

#Diagonal matrix
diag(c(1,2,3))
#      [,1] [,2] [,3]
# [1,]    1    0    0
# [2,]    0    2    0
# [3,]    0    0    3


#Matrix attributes
A <- matrix(1:12, 3, 4, byrow=TRUE)
A
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8
# [3,]    9   10   11   12

nrow(A)   #Number of lines
# 3
ncol(A)   #Number of columns
# 4
dim(A)    #Number of lines,Number of columns
# 3 4
class(A)
# "matrix"
typeof(A)
# "integer"


#Get matrix elements
A <- matrix(1:12, 3, 4, byrow=TRUE)
A
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8
# [3,]    9   10   11   12

A[1,2]
# [1] 2

#Row vector
A[1,]
# [1] 1 2 3 4

#Column vector
A[,2]
# [1]  2  6 10

#part
A[1:2,]
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8

A[,3:4]
#      [,1] [,2]
# [1,]    3    4
# [2,]    7    8
# [3,]   11   12

A[1:2,3:4]
#      [,1] [,2]
# [1,]    3    4
# [2,]    7    8

A[c(1,3),]
# 
# [1,]    1    2    3    4
# [2,]    9   10   11   12

A[,c(2,4)]
#      [,1] [,2]
# [1,]    2    4
# [2,]    6    8
# [3,]   10   12

A[c(1,3),c(2,4)]
#      [,1] [,2]
# [1,]    2    4
# [2,]   10   12

A[,]
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8
# [3,]    9   10   11   12

head(A)
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8
# [3,]    9   10   11   12
head(A, 2)
#      [,1] [,2] [,3] [,4]
# [1,]    1    2    3    4
# [2,]    5    6    7    8
tail(A, 2)
#      [,1] [,2] [,3] [,4]
# [2,]    5    6    7    8
# [3,]    9   10   11   12


#Calculation of linear algebra
A <- matrix(1:4, 2, 2, TRUE)
A
#      [,1] [,2]
# [1,]    1    2
# [2,]    3    4

B <- matrix(5:8, 2, 2, T)
B
#      [,1] [,2]
# [1,]    5    6
# [2,]    7    8

#Matrix scalar times
k <- 2
A * k
#      [,1] [,2]
# [1,]    2    4
# [2,]    6    8

#Matrix sum
A + B
#      [,1] [,2]
# [1,]    6    8
# [2,]   10   12

#Matrix multiplication
A %*% B
#      [,1] [,2]
# [1,]   19   22
# [2,]   43   50

#Transpose matrix
t(A)
#      [,1] [,2]
# [1,]    1    3
# [2,]    2    4

#Inverse matrix
solve(A)
#      [,1] [,2]
# [1,] -2.0  1.0
# [2,]  1.5 -0.5

#Identity matrix
diag(2)
#      [,1] [,2]
# [1,]    1    0
# [2,]    0    1

#Determinant detA
det(A)
# -2

#Diagonal component
diag(A)
# [1] 1 4

#Trace (sum of diagonal components) trA
sum(diag(A))
# 5


#Operations for each element of the matrix
A + B
#      [,1] [,2]
# [1,]    6    8
# [2,]   10   12

A - B
#      [,1] [,2]
# [1,]   -4   -4
# [2,]   -4   -4

A * B
#      [,1] [,2]
# [1,]    5   12
# [2,]   21   32

A / B
#           [,1]      [,2]
# [1,] 0.2000000 0.3333333
# [2,] 0.4285714 0.5000000

A * 2
#      [,1] [,2]
# [1,]    2    4
# [2,]    6    8

A ^ 2
#      [,1] [,2]
# [1,]    1    4
# [2,]    9   16

-A
#      [,1] [,2]
# [1,]   -1   -2
# [2,]   -3   -4

A == B
#       [,1]  [,2]
# [1,] FALSE FALSE
# [2,] FALSE FALSE

A < B
#      [,1] [,2]
# [1,] TRUE TRUE
# [2,] TRUE TRUE

A > 2
#       [,1]  [,2]
# [1,] FALSE FALSE
# [2,]  TRUE  TRUE

A %% 2 == 0
#       [,1] [,2]
# [1,] FALSE TRUE
# [2,] FALSE TRUE

sqrt(A)
#          [,1]     [,2]
# [1,] 1.000000 1.414214
# [2,] 1.732051 2.000000

exp(A)
#           [,1]      [,2]
# [1,]  2.718282  7.389056
# [2,] 20.085537 54.598150

log(A)
#          [,1]      [,2]
# [1,] 0.000000 0.6931472
# [2,] 1.098612 1.3862944

VBA

VBA


Sub test_matrix()

Dim A As Variant
Dim B As Variant
Dim k As Double
Dim kA As Variant
Dim ApB As Variant
Dim AB As Variant
Dim At As Variant
Dim Ainv As Variant
Dim Eye2 As Variant
Dim detA As Double
Dim trA As Double
Dim i As Integer
Dim j As Integer

'queue
A = Range("B3:E5").Value
print_matrix (A)

B = Range("B8:C9").Value
print_matrix (B)

'Number of rows in the matrix
Debug.Print LBound(A, 1), UBound(A, 1), UBound(A, 1) - LBound(A, 1) + 1

'Number of columns in the matrix
Debug.Print LBound(A, 2), UBound(A, 2), UBound(A, 2) - LBound(A, 2) + 1

'Matrix element A(1, 2)
Debug.Print A(1, 2)

'Matrix scalar times kA
k = 2
ReDim kA(LBound(A, 1) To UBound(A, 1), LBound(A, 2) To UBound(A, 2))
For i = LBound(A, 1) To UBound(A, 1)
    For j = LBound(A, 2) To UBound(A, 2)
        kA(i, j) = A(i, j) * k
    Next j
Next i
Call print_matrix(kA)
Range("E13:F14").Value = kA

'Matrix sum A+B
ReDim ApB(LBound(A, 1) To UBound(A, 1), LBound(A, 2) To UBound(A, 2))
For i = LBound(A, 1) To UBound(A, 1)
    For j = LBound(A, 2) To UBound(A, 2)
        ApB(i, j) = A(i, j) + B(i, j)
    Next j
Next i
Call print_matrix(ApB)
Range("E18:F19").Value = ApB

'Matrix product AB
AB = WorksheetFunction.MMult(A, B)
Call print_matrix(AB)
Range("E18:F19").Value = AB

'Transpose matrix A^T
At = WorksheetFunction.Transpose(A)
Call print_matrix(At)
Range("E28:F29").Value = At

'Inverse matrix A^(-1)
Ainv = WorksheetFunction.MInverse(A)
Call print_matrix(Ainv)
Range("E33:F34").Value = Ainv

'Identity matrix
Eye2 = WorksheetFunction.Munit(2)
Call print_matrix(Eye2)
Range("E38:F39").Value = Eye2

'Determinant detA
detA = WorksheetFunction.MDeterm(A)
Debug.Print detA
Range("E42").Value = detA

'Trace trA
trA = WorksheetFunction.SumProduct(A, Eye2)
Debug.Print trA
Range("E45").Value = trA

End Sub

Sub print_matrix(M As Variant)
'For matrix display
Dim i As Integer
Dim j As Integer
Dim s As String

If IsArray(M) Then
    s = ""
    For i = LBound(M, 1) To UBound(M, 1)
        For j = LBound(M, 2) To UBound(M, 2)
            s = s & vbTab & M(i, j)
        Next j
        s = s & vbCrLf
    Next i
    Debug.Print s
End If

End Sub

reference

Recommended Posts

VBA user tried using Python / R: Matrix
VBA user tried using Python / R: Iterative processing
VBA user tried using Python / R: conditional branching
VBA user tried using Python / R: string manipulation
VBA user tried using Python / R: basic grammar
VBA user tried using Python / R: String manipulation (continued)
VBA users tried using Python / R: basic arithmetic operations
VBA users tried using Python / R: logical operations and comparison operations
[Python] I tried using OpenPose
I tried using Thonny (Python / IDE)
[Python] I tried using YOLO v3
[Python] Matrix multiplication processing time using NumPy
I tried using Bayesian Optimization in Python
I tried using UnityCloudBuild API from Python
vprof --I tried using the profiler for Python
I tried web scraping using python and selenium
I tried object detection using Python and OpenCV
Start using Python
I tried using mecab with python2.7, ruby2.3, php7
I tried reading a CSV file using Python
I tried using the Datetime module by Python
[Python] Matrix operation
Scraping using Python
[Python] I immediately tried using Pylance's VS Code extension.
I tried using TradeWave (BitCoin system trading in Python)
[Python] I tried running a local server using flask
I tried drawing a pseudo fractal figure using Python
I tried using Python (3) instead of a scientific calculator
I tried to access Google Spread Sheets using Python
I tried using parameterized
I tried using argparse
I tried using mimesis
Operate Redmine using Python Redmine
Run Python with VBA
I tried using aiomysql
I tried using Summpy
I tried Python> autopep8
I tried using Pipenv
I tried using matplotlib
Data cleaning using Python
I tried using ESPCN
Implemented Matrix Factorization (python)
I tried using openpyxl
Using Python #external packages
I tried using Ipython
I tried using PyCaret
I tried using cron
WiringPi-SPI communication using Python
Age calculation using python
I tried using ngrok
I tried using face_recognition
I tried using Jupyter
Search Twitter using Python
I tried using PyCaret
I tried using Heapq
Name identification using python
Notes using Python subprocesses
I tried using doctest
I tried Python> decorator
I tried using folium
I tried using jinja2