Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

Excelで筆算的プログラミング(4)

More than 1 year has passed since last update.

行列積を定義する。

(A6) =P("V_1 2","V_2 4","内積_0,1")    [10]

Function 内積(ParamArray M())
    Dim S: S = ""
    Select Case UBound(M)
        Case 1: S = WorksheetFunction.SumProduct(M(0), M(1))
        Case 2: S = WorksheetFunction.SumProduct(M(0), M(1), M(2))
    End Select
    内積 = S
End Function

2次元配列を作る関数

(A7) =M_("1 2 3, 4 5 6, 7 8 9")        [1]
(A8) =SUM(M_("1 2 3, 4 5 6, 7 8 9"))     [45]  

Function M_(D, Optional L1 = ",", Optional L2 = " ", Optional S = "")
    If D Like "M_*" Then D = Right(D, Len(D) - 2)
    D = WorksheetFunction.Trim(D)

    Dim D1: D1 = Split(D, L1)      ' ROW
    Dim D2: D2 = Split(D1(0), L2)  ' COLUMN
    Dim M1: M1 = UBound(D1)
    Dim M2: M2 = UBound(D2)
    ReDim S(M1, M2)

    Dim I: For I = 0 To M1
       D2 = Split(WorksheetFunction.Trim(D1(I)), L2)
       Dim J: For J = 0 To M2
           S(I, J) = E(Val(D2(J)), 0)
       Next J
    Next I
    M_ = S
End Function

Trim の仕様の違い

Trim だと、空白の扱いが違う事で、微妙にバグるので、WorksheetFunction.Trimに置き換えます。

Function P(ParamArray F())
    On Error Resume Next
    Dim M: M = UBound(F)
    Dim A: ReDim A(M)
    Dim I: For I = 0 To M
        Dim S: S = ""
        Select Case TypeName(F(I))
            Case "String"
                Dim D: D = WorksheetFunction.Trim(F(I)) ' D = Trim(F(I)) から置き換え
                Select Case True
                    Case D Like "V_*": S = V_(D)
                    Case D Like "R_*": S = R_(D)
                    Case D Like "M_*": S = M_(D)     ' 2次元配列の為に追加
                    Case Else

....
matumoto_onga
Excel Insider 2011年まで某航空会社子会社のネットワーク技術者 2012年以降は旋盤工
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away