えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

MENU
トップ > Excel > Excelで高度な数式を構築する方法 part1

Excelで高度な数式を構築する方法 part1

Excel Excel関数

今回はExcel上級者たちが使うような、高度な数式を組むのに必要な技術を紹介します。

Excel関数は使えるようになったけど、配列数式が分からない！という方にもオススメです。

f:id:Kotori-ChunChun:20190518224059p:plain

はじめに

以前、文字列処理の様々な数式を公開しました。

https://www.excel-chunchun.com/entry/2019/01/31/011825www.excel-chunchun.com

この中には、配列を使用した難解な数式がいくつも含まれています。

配列数式はエクセルの中でも恐らく最高難易度の機能の一つであり、配列数式の構築方法について詳しく解説しているサイトはあまりありません。（本を読まないので専門書みたいなのがあるのかも知れませんが）

私自身もExcel関数はまだまだ勉強中で、特に配列数式は「理解した！！！」とは言えない状況です。

少しでも同志を増やすためにも、自分の理解を深めるためにも、一度解説してみることにしました。　←執筆中に上手く行かない事が多く、試行錯誤して想定以上に理解が深まりました。

最終的にはVBAやGoogleスプレッドシートにしか無いSPLITに相当する数式を解説したいと考えておりますが、今回は数式作成に必要な技術を一通り説明します。

尚、本記事は、2020年のスピル実装前に執筆された文章です。

スピル実装に伴い出力データが配列の場合は自動展開されるようになりましたので、環境によっては挙動が異なる場合があることをご承知おきください。


エクセルの基本

注意

本記事では、説明をしやすいように公式ではない呼称を使用しています。予めご了承ください。

呼び名 値の例 正式名
定数（数値） 1 ○
定数（文字列） "あああ" ○
参照 A1 ○
セル範囲(配列参照) A1:A10 ○
配列定数(1次元 列方向) {1,3,5,10,20} ×
配列定数(1次元 行方向) {1;3;5;10;20} ×
配列定数(2次元) {1,2;3,4;5,6} ×
配列数式 {=SUM(A1:A3*B1:B3)} ○
配列値 配列参照ではない配列データ ×
配列 何らかの数式で求められた配列データ ×


計算の順番

エクセル関数は先頭の引数から順に、入れ子の場合は内側から計算されます。

例えばこんな感じで計算されていきます。

=SUM(SUM(1,2,3),SUM(4,5,6))
=SUM(6,SUM(4,5,6))
=SUM(6,15)
=21


配列

Excelにおける配列はプログラミングで出てくる配列と根本は同じです。

Excel関数の引数のうち、（配列が使える）引数には大きく分けて「配列参照を必須としている」ものと、「配列であれば何でも良い」ものの2種類があるため、この２つは明確に意識して使わないとエラーになります。

=SUM(A1:A10) //OK
=SUM({1,2,3})   //OK

=SUMIF(A1:A10, 1)   //OK
=SUMIF({1,2,3}, 1)  //NG


Excelのセルに出力できる値は一つと決まっているため、計算結果が配列の場合でも、最終的には先頭の値だけが結果として表示されます。

例（イメージです）

  • 単一セル参照→値
  • 配列参照→先頭のセル参照→値
  • 配列定数→先頭の値
  • 配列数式→先頭の値
  • 関数の戻り値の配列→先頭の値


配列参照

例えば表のB1にA1:A5という式がありますが、A1:A5をセル範囲(配列参照)と呼びます。

行＼列 A B
1 2 =A1:A5
2 4
3 6
4 8
5 10

セルB1というセルにはA1:A5という参照配列から算出した配列値{2,4,6,8,10}が入っていることになりますが、セルとして出力できる値は一つと決まっているため、B1には自動的に配列の先頭に位置するA1の値、つまり2がB1の値となります。

=A1:A5
=A1
=2

とはいえ、普通はこのような使い方はしません。

どんなときに使うというと、Excelで最初に習うであろうSUM関数ですね。

例えば、SUM関数は=SUM(A1:A5)という書き方ができますよね。

=SUM(A1:A5)
=30

これを無理やり配列として可視化するとしたらこうなります。

=SUM(A1:A5)
=SUM({A1;A2;A3;A4;A5})
=SUM({2;4;6;8;10})
=30

というわけで、配列参照を使った関数の仕組みは以上です。


配列定数

配列参照と対極的なものとして、配列定数があります。

文字通り「定数」を使った配列のことで、中括弧の中に定数をコンマまたはセミコロン区切りで記入することで配列定数を作る事ができます。（両方使えば二次元配列）

このように、配列定数の式だけをセルに入力した場合も、先頭の値だけしかセルには出力されません。

={1,2,3,4,5}
=1

通常はSUM関数などの配列を引数と持つことの出来る関数と組み合わせて使います。

=SUM({2;4;6;8;10})
=30

※配列定数：（定数＝リテラル＝静的な値＝自動で変化しない値）の配列


配列数式

Excelをソコソコ触っている人は「配列数式」という名称を聞いたことがあるかも知れません。

「配列」を使った「数式」だから前述のような「配列参照」「配列定数」を使っているものも「配列数式」と呼ぶのか・・？と思いますが、これらを「配列数式」なんて呼ぶことはしません。

配列数式と呼ばれるか否かはCtrl+Shift+Enter確定した式かどうかで決まります。

また、Ctrl+Shift+Enterの頭文字を取ってCSE式とも呼ばれます。

配列数式では

  • 配列と配列を演算して配列を返す
  • 配列を受け取ることの出来ない関数に配列を渡して、個々の値を計算した結果の配列を返す

と言ったことが出来ます。


まずは配列と配列を演算して配列を返すについて、SUM関数で説明しましょう。

シート上のデータはこんな感じになっているものとします。

行＼列 A B
1 1 4
2 2 5
3 3 6

配列と配列を計算させるにあたってA1:A3*B1:B3という一見不思議な事をしてみます。

数式をA4に入力してみます。

=SUM(A1:A3*B1:B3)
=SUM(#VALUE!*B1:B3)
=SUM(#VALUE!*#VALUE!)
=SUM(#VALUE!)
=#VALUE!

数式をC3に入力してみます。

=SUM(A1:A3*B1:B3)
=SUM(A3*B3)
=SUM(3*6)
=SUM(18)
=18

このように、配列参照では入力したセルの行に対応した参照を使用する性質があります。

つまりは単一値×単一値しか実行できないのです。

行＼列 A B C
1 1 4 4
2 2 5 10
3 3 6 18
4 #VALUE! #VALUE! #VALUE!

f:id:Kotori-ChunChun:20190518233634g:plain

※この挙動のことを「implicit intersection」と言うそうです。「implicit intersection」については下記の記事が非常に詳しく解説されています。

https://www.excelspeedup.com/implicitintersection/


配列同士の計算をするには、配列数式であることをExcelに教えてあげる必要があります。

上の式をCtrl+Shift+Enterで確定すると、勝手に数式の前後に中括弧が付与されます。これが配列数式であることをExcelに伝えた状態です。

{=SUM(A1:A3*B1:B3)}
=SUM({1*4;2*5;3*6}) //数式の検証では見えない部分
=SUM({4;10;18})
=32

というわけで、このように配列と配列を演算してその結果を配列で返すような事が内部で起きています。


まあこの式の場合は、配列同士をかけ合わせて合計する関数SUMPRODUCT()という専用の関数があるんですが…。

=SUMPRODUCT(A1:A3,B1:B3)


次に配列を受け取ることの出来ない関数に配列を渡して配列を返すについて、ROW関数で説明しましょう。

ROW()関数は指定したセル（参照）の行番号を返す関数です。

通常、配列を受け取ることが出来ない関数は、配列の先頭しか使用しません。

=ROW(A1:A5)
=1  //ROW(A1)の結果

当然、SUMで囲ったとしても、一つしか無いので合計しても意味がありません。

=SUM(ROW(A1:A5))
=SUM(ROW(A1))  //イメージ
=SUM(1)
=1

今度はこの数式をCtrl+Shift+Enterで確定してみます。

{=SUM(ROW(A1:A5))}
=SUM({ROW(A1),ROW(A2),ROW(A3),ROW(A4),ROW(A5)}) //イメージ
=SUM({1,2,3,4,5})
=15

A1:A5は{A1,A2,A3,A4,A5}という感じで一つ一つのセルに展開されました。


尚、セルに表示できる値も配列の先頭のみなので、ROW関数だけでは配列数式になっていることを確認できません。

{=ROW(A1:A5)}
={ROW(A1),ROW(A2),ROW(A3),ROW(A4),ROW(A5)} //理想
=ROW(A1)  //現実
=1  //ROW(A1)の結果

というわけで

  • 配列を返すことのできる式

  • 配列を引数で受け取って単一の値を返すことのできる関数（SUM、MIN、MAX、SUMPRODUCTなど）

の２つを組み合わせて、初めて配列数式を使った式が完成します。


実際の現場では、ROW(INDIRECT("1:"&配列が欲しい要素数))という形で使うことが多いです。

1:10で良いのに、わざわざINDIRECT("1:10")としているのは

・行削除等による参照破壊の防止 ・参照先の表示で式が組みづらい ・最終行を変数化するための下準備

と言った目的があります。

詳しくは次回以降の記事にて。


ちなみに、Office365の2020年アップデート以降ではSEQUENCE関数がこの役割を担っているので、この記法は数年後には不要となると考えられます。


配列数式ではない配列数式

何を言っているのか分からないと思いますが、要するにCtrl+Shift+Enterで確定していないにも関わらず、配列数式と同じことをさせる方法です。

先ほどと同じデータを使います。

行＼列 A B
1 1 4
2 2 5
3 3 6

そして先程使用したこの式を

{=SUM(A1:A3*B1:B3)}

内側の配列同士の演算をINDEX(式,0)で囲い、普通にEnterで確定します。

=SUM(INDEX(A1:A3*B1:B3,0))

すると、次のように計算されます。

=SUM(INDEX(A1:A3*B1:B3,0))
=SUM(INDEX({1*4;2*5;3*6},0) //数式の検証では見えない部分
=SUM(INDEX({4;10;18},0))
=SUM({4;10;18})
=32

何故このような現象が起こるのかと言うと、INDEX()関数は第二引数を省略または0にした時、配列を返す仕様だからです。

support.office.com - INDEX 関数

計算式や大半の関数は単一値を返す仕様のため、#VALUE!とするか配列の先頭しか使用しないという事になりますが、INDEXを使えば強制的に配列を返すと指示することになるため、Ctrl+Shift+Enterで確定しなくても配列数式として計算してくれるのです。

作成時はCSEで確定しても、後日数式を見るためにF2で編集モードに入り、Enterで確定したら配列数式では無くなってしまうため、そういった事故を防ぎたい場面でも使います。※この時ESCで終わるようにすれば配列数式は壊れません。


また、複雑な数式を組んでいると、今まで動いていた部分の式の配列が解除されてしまうということがあります。

そんなときはINDEXで囲うと配列を維持することができるので、極めて重要な関数となります。

これについては難しいので次回の記事で解説する予定です。


Googleスプレッドシートも配列数式を搭載していますが、Excelとは若干異なります。

数式を配列数式として認識させるためには、ARRAYFORMULA関数を使うそうです。

＼ A B C D
1 配列1 配列2 配列1+2
2 1 4 5
3 2 5 7
4 3 6 9 
=ARRAYFORMULA(A2:A4+B2:B4)

ExcelのINDEX(配列,0)とGoogleスプレッドシートのARRAYFORMULA(配列)がほぼ同一の動きをするようです。（完全な検証はしていません）

なお、配列数式の使い方が近いうちに改良される見込みです。

（※「スピル」という機能が既にWEBで話題になっていますが、2019/7/1時点では未実装）


数式作成に必要な標準機能

数式の入力

Excelのショートカットキーは多種多様ですが、セルの編集中にのみ使用可能なショートカットキーというものが存在します。

特に重要なのがTABキーで、途中まで関数名を入力したら、リストから選択してTABキーでオートコンプリートできます。

=sub
↓  //TABキー
=SUBSTITUTE(


他にも次のようなショートカットキーがエクセルの神髄 - 関数入力のショートカット で紹介されていました。

=IF
↓ //Ctrl + Shift + A
=IF(論理式,値が真の場合,値が偽の場合)
=IF
↓  //Ctrl + A
=IF()
↓
関数の引数ダイアログが表示
=IF(
↓  //Shift + F3
=IF()
↓
関数の引数ダイアログが表示

Ctrl+AとShift+F3は同じように見えますが、(が必要になるため前者のほうが効率的です。


数式の検証

support.office.com - ネストした数式を 1 レベルずつ検証する

基本機能なので有名だと思いますが、数式の計算の過程をステップ毎にシミュレーションできます。

アクセスキーはAlt,M,Vですが、繰り返し検証する時はクイックアクセスツールバーに登録しておくと良いでしょう。

私は「Alt+3」に設定しています。

というのも、数式の検証ダイアログを開いた後の「検証」ボタンが「Alt+E」だからです。

どういうことかというと、Altを左手親指、3を中指、Eを人差し指のように手を配置して、スムーズに検証できるからです。

※最近知ったのですが、「数式の検証」ダイアログは再計算が手動の状態だと最終結果だけが再計算前の値が表示されるという、厄介な仕様があるようです。

※INDIRECTで配列参照を返すと、ダイアログ上では#VALUE!になってしまうようです。


数式を計算結果で置き換える

上記、数式の検証ダイアログを使用したほうが大抵の場合は効率的なのですが、あまり複雑な式になると繰り返しステップするのが大変です。

そんなときに使いたいのが、support.office.com - 数式を計算結果で置き換える方法です。

数式の一部の範囲を選択してからF9を押すと、選択範囲内の式を評価した結果が表示されます。

@Dev_Clipsさんのトコが分かりやすいかもです。

Excelで数式の計算過程を表示する方法


数式の分析

Excelには参照先・参照元をトレースする機能があります。

数式の入ったセルを選択した状態で、下記のショートカットキーを使うことで少しだけ分析が容易になります。

キー 名称
Ctrl+[ 参照元のセルに移動
Ctrl+] 参照先のセルに移動
Ctrl+Shift+[ すべての参照元のセルに移動
Ctrl+Shift+] すべての参照先のセルに移動
Alt,M,P 参照元のトレース矢印表示
Alt,M,D 参照先のトレース矢印表示
Alt,M,A トレース矢印を削除

巨大なワークシートで参照を張って、簡易的なハイパーリンクや名前定義の代わりに使っている変人もいるとかなんとか？


数式を表示

数式をセルに入力すると、カーソルを合わせないと式が見れなくて不便なときがありますよね。

数式を表示する方法が、ちゃんと用意されています。

一つは「数式の表示 Alt,M,H」

もう一つはFORMULATEXT()関数です。

=FORMULATEXT(A1)

自動で折り返して全体表示が働かないので、使う時はちょっと注意が必要なんですけどね。

長いのでFORMまで入力したら、TAB`キーでオートコンプリートしましょう。


数式作成に便利なテクニック

コメントを入れる方法1 (T関数とN関数)

ExcelにはT()関数とN()関数という、あまり見慣れない一文字だけの不思議な関数があります。

  • T()関数は、文字列以外の情報は全て空欄("")に消すことの出来る関数

  • N()関数は、数値以外の情報はすべて0にすることの出来る関数

これらの関数を組み合わせる事で、コメント（出力されない文字列）を埋め込む事ができます。

出力形式が数値なら

=(数式)+N("コメント")

出力形式が文字列なら

=(数式)&T(N("コメント"))


コメントを入れる方法2 (配列定数)

上記で説明したように「配列の先頭しか使用されない」という仕様を逆手に取るという方法もあります。

例えばこんな風にしておけば、画面には1番目の値しか表示されません。

={3.141592,"円周率"}
=3.141592

複雑な数式の中ではこれらが邪魔をする危険性もあるため、使用にはご注意ください。


数式解読ツール

複雑な数式を組み上げるにあたっては、Excelの標準機能だけでは貧弱でとても不便です。

内部改行(Alt+Enter)やスペースによるインデントが使えますので、読みやすくしたい人は本記事のようにインデントすると良いでしょう。

しかし、それを毎回手入力するのは面倒くさい・・・。そんな作業を効率化するマクロが、この世にはあります！！！


1.私がいつか全記事を読み尽くしたいブログにt-hom's diary - ExcelでネストしたIf関数をVBAでインデントして分析しやすくするという記事があります。

これはコンパイラ開発で使われる方法なのだそうで、このままではIFにしか使えないのですが、改造すれば数式の解読マクロを作るのも夢ではないかもしれません。

ある程度までは改造したのですが、そこで満足してしまい保留になっています。


2.VBAerの誰もが一度は拝見したことのあるであろうサイトにエクセルの神髄 - Excelシートの複雑な計算式を解析するVBAという記事があります。

シート上に解析結果を書き出してくれるので、すぐに役に立つかもしれません。

また、有り難いお言葉が書いてあるので一度は目を通しておきましょう。


皆さん凄いですね。

もちろん私も負けてはいられません！

以前、自作したものがあるので、ここでお見せしちゃいます。


3.一つ目はthom様の記事のコードを手直ししてアドイン化したもの。

アドイン公開はもうちょっとデバッグをしたいので、今回は保留で。


4.もう一つが独自アルゴリズムで書いたマクロです。

カッコの数からネストを計算して、先頭にスペースを2つ挿入するだけというシンプルなアルゴリズムです。

一部の式ではバグが出る恐れがあるため、まだまだ改善の余地アリですが大半はこれで解決できるので、あまり進んでいません。

解説とかは後日記事を立て直す予定ですが、とりあえずご紹介だけ。


//変換前
=SUM(ROW(A1:A5))

//変換式
=FormulaIndent(A1)

//変換後
=SUM(
  ROW(A1:A5)
)


//変換前
=SUM(INDEX(VALUE(IF(MID({"a1","a2","a3",""},2,1)="",0,MID({"a1","a2","a3",""},2,1))),0))

//変換式（3段インデントまで）
=FormulaIndent(A1,3)

//変換後
=SUM(
  INDEX(
    VALUE(IF(MID({""a1"",""a2"",""a3"",""""},2,1)="""",0,MID({""a1"",""a2"",""a3"",""""},2,1))),
    0
  )
)

//変換式（5段インデントまで）
=FormulaIndent(A1,5)

//変換後
=SUM(
  INDEX(
    VALUE(
      IF(
        MID({""a1"",""a2"",""a3"",""""},2,1)="""",
        0,
        MID({""a1"",""a2"",""a3"",""""},2,1)
      )
    ),
    0
  )
)


'関数式をイイ感じに改行を入れてインデントする。　※開発中
'exp         : Excelフォーミュラまたはセル
'indentLevel : 最大何回までインデントするか
Public Function FormulaIndent(exp As Variant, Optional indentLevel As Long = 2) As String

    Dim fmr As String
    If TypeName(exp) = "Range" Then
        fmr = exp.Formula
    Else
        fmr = exp
    End If
    
    '仕様
    '行ごとにトリミングして1行に合成
    '(が来たら、改行＋インクリメント
'    ',が来たら、
    ')が来たら、改行＋デクリメント
    Dim ins As Variant
    Dim ous As String
    For Each ins In Split(fmr, vbLf)
        ous = ous & Trim(ins)
    Next
    
    '文字列""に囲まれているスペースと改行は事前に置換して
    '以降のプログラムを簡素化するのが無難かと思われる。
    
    '改行挿入
    ous = Replace(ous, "(", "(" & vbLf)
    ous = Replace(ous, ")", vbLf & ")")
    ous = Replace(ous, ",", "," & vbLf)
    
    Dim i As Long, j As Long, k As Long
    Dim indent As Long
    
    '改行回数分析から
    'n以上なら([LF]から[LF])までの[LF]を消す。
    Dim ids As String
    ids = Left(ous, 1)
    indent = 0
    For i = 2 To Len(ous)
        If Mid(ous, i - 1, 2) = vbLf & ")" Then indent = indent - 1
        If Mid(ous, i - 1, 2) = "(" & vbLf Then indent = indent + 1
        If Mid(ous, i, 1) <> vbLf Or indent < indentLevel Then
            ids = ids & Mid(ous, i, 1)
        End If
    Next
    ous = ids
    
    'スペースx2挿入
    Dim S1() As String
    Dim S2() As String
    S1 = Split(ous, vbLf)
    ous = ""
    indent = 0
    For i = LBound(S1) To UBound(S1)
        If S1(i) Like ")*" Then indent = indent - 1
        If i <> LBound(S1) Then ous = ous & vbLf
        ous = ous & String(indent * 2, " ") & S1(i)
        If S1(i) Like "*(" Then indent = indent + 1
    Next
    
    FormulaIndent = ous
End Function


検索していたらこんな分析ツールも出てきましたのでリンクだけ残しておきます。

株式会社構造計画研究所 - Excel®分析ツール


これは英語サイトで良くわからないんですが、数式の参照関係を追跡することができるみたいです。結構重要な情報が記載されているので、稀によくアクセスしてます。

jkp-ads.com - RefTreeAnalyser


おわりに

今回は配列を使った高度な数式作成の方法を紹介しました。

配列数式には次のようなメリットがあります。

  • エクセル関数だけで、かなり複雑な計算を実現出来るようになります。
  • 傍から見てとてもカッコイイです！
  • ドーパミンがバンバン出て、脳みそがトロけてきます。
  • 複雑な式が完成した時、深い達成感と優越感を得ることが出来ます。

一方で次のようなデメリットがあります。

  • 作業セルを使うのに比べて、多くの時間を要します。
  • 未来の自分でも解読するのに苦労します。
  • 多くの人は解読することが出来ません。
  • 場合によってはミスの元となったりして、仲間に迷惑がかかる場合があります。

というわけで、実務では作業セルを使って段階を踏んで計算するなり、VBAやPowerQueryを使うなりして、皆の迷惑にならないように注意して使いましょう。


次回は実際にSPLIT互換関数の解説をしていきます。

www.excel-chunchun.com

https://www.excel-chunchun.com/archive/category/Excel%E9%AB%98%E5%BA%A6%E3%81%AA%E6%95%B0%E5%BC%8Fwww.excel-chunchun.com

www.excel-chunchun.com


以上

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)

プライバシーポリシー