[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『計算式の可読性を上げたい』（VBAVAA）
誰かの作ったエクセルファイルをもらった時に、どうしても数式を理解しないといけない場面があるのですが、長ければ長いほど訳が分からなくなります。
改行するくらいなら思いつくのですが、私個人としてはそれでは読みやすくなった気がしません。
他に何か可読性を上げるための手法があれば教えてもらえないでしょうか？
＜ 使用 Excel：Excel2010、使用 OS：Windows10 ＞
私はテキストエディタにコピーしたうえで、
改行したりインデントつけたりして関数のようにして読みます。
でも、これでは質問者様と似たようなやり方ですね。
何か画期的な発見・出会いがありますように。
（ぱぁ） 2020/09/17(木) 10:01
数式の検証 https://excel-master.net/worksheet-function/verification-formula/ が標準で備わっていますが、これだと足りないですか？
ほかにはセルを選択して、F2で編集モードにしたあと、結果が知りたい場所にカーソル I を当てて F9を押すと、計算結果が表示されます。 ※そのまま確定してしまうと、その場所だけ数式が消えるので注意！！
あとは内側から分解して、別のセルにコピペしていきます 配列数式だとできないですけど、大抵この3段階で見てます。 （稲葉） 2020/09/17(木) 10:46
# 余談です。
# こちらの掲示板では、数式を改行した形でやりとりすることが少ない気がします。
# もっとそう言う形で議論されたらよいのにと思います。
# 達人の皆さんは見ただけで内容がピントくるんでしょうね。
# 私には殆どGreeksに近いことが多いです。
（γ） 2020/09/17(木) 11:11
なお、数式の検証は、コチラが考えるほどよいところで検証してもらえないイメージです(-_-;)
（VBAVAA） 2020/09/17(木) 12:08
＞# こちらの掲示板では、数式を改行した形でやりとりすることが少ない気がします。 ＞# もっとそう言う形で議論されたらよいのにと思います。 主観ですが、数式に関しては改行したほうが読みにくい場合が多い気がします・・・
ネストの数、例外の有無、参照範囲は1画面に収まっているか、条件はリテラルか参照か等々でも変わってきますし、 結局数こなして、ピンとくる体質に育てていくしかないんじゃないかなと・・・
直近だと、この辺りでいくつか数式ありますが [[20200915121651]] 『【IF関数】条件が複数すぎる。男か女か、〇以上な』（りりー） Lookupの式なんかは、ピンてくる人はよっぽど勉強されている方じゃないですか？
＞なお、数式の検証は、コチラが考えるほどよいところで検証してもらえないイメージです(-_-;) ちなみにどんな式ですか？
（稲葉） 2020/09/17(木) 12:26
作業用セルをつかって、長い式にならないように意識する。
作業用セルを使わない式が「すごい」「えらい」みたいな考え方もありますが、 可読性が重要なら、可読性に振った式にすべきです。 （´・ω・`） 2020/09/17(木) 12:54
（´・ω・`）さんに賛成です。
個人的には配列数式を使ったり、長い数式を作って作業列を使わない場合も多いのですが、 それが必ずしも優れているとは思いません。 私の場合このサイトで複雑な数式を作るのは自分の勉学のためと、また質問者のニーズがあるからです。
作業列を使うことは決して恥ずかしいことではなく、むしろ積極的に活用すべきと思います。 見た目が悪ければ非表示にすれば済むことです。 とくに仕事で使う場合は担当者が変わると数式の意味が理解できなくなるなどの弊害も多いようです。 （tora） 2020/09/17(木) 13:39
（´・ω・`）さんの意見に賛成なのですがか、今回の質問は ＞誰かの作ったエクセルファイルをもらった時に、どうしても数式を理解しないといけない場面 じゃないんですか？ 私が引用したスレッドも、採用するならメジロさんの意見でしょうし、自分で作るなら別表設けると思います。
もしお二人の意見が γさんに対するものでしたら、すみません。
（稲葉） 2020/09/17(木) 14:10
ネットで検索したら、ズバリ作っている人がいました。
エクセルの神髄 https://excel-ubara.com/excelvba5/EXCELVBA266.html 【Excelシートの複雑な計算式を解析するVBA】
そこそこ動きましたが、複雑でもないこんなのもついでにやってみましたら、 ↓ '=(1+1%)^DAY(TODAY())
単に「1+1%」に変えられてしまった。
VBAVAAさんが分析したかった数式を当てはめると、どうなるんでしょうかね。。。
（半平太） 2020/09/17(木) 15:55
面白そうなので試してみました！
式内の配列にも対応してないみたいですね。
|[A] |[B] |[C] |[D] |[E]|[F]|[G]|[H]|[I]|[J]|[K]
[1]| |IF(OR(A1={"新宿","種馬"}),"xyz","")| | | | | | | | |
[2]| 2|論理式 |OR(A1={"新宿","種馬"})| | | | | | | |
[3]| 3| |論理式1 |A1={"新宿"| | | | | | |
[4]| 3| |論理式2 |"種馬"} | | | | | | |
[5]| 2|値が真の場合 |"xyz" | | | | | | | |
[6]| 2|値が偽の場合 |"" | | | | | | | |
あとは
TRIM(MID(SUBSTITUTE("aaa bbb ccc"," ",REPT(" ",100)),ROW(A1)*100-99,100))
↑のような式も、分解しても意味まではわからないと思います・・・
（空白で区切られた文字を取り出す数式）
'(10/5)+1 とか '(2*2)+(3*3) 関数が無くて、四則演算の()は無視されて、()の中だけ表示されるみたいですね
（稲葉） 2020/09/17(木) 17:16
＞# もっとそう言う形で議論されたらよいのにと思います。
印象に残ってメモしてあったのは、下記のスレッド。
[[20161027233910]]
これくらい研究すると回答者も回答し甲斐があるでしょうね。
（γ） 2020/09/17(木) 18:23
このお三方は誉め言葉で変態ですもんね 最後の鈴さんのひとことが、すごく印象的でした これは記憶に残りますね
ちなみに、わたしは「そう言う形」を取り違えていました 回答者が説明するときにまたはよく利用すべき人たちが、わかりやすいように 分解して説明する形という意味でとらえてしまいました。 すみません
（稲葉） 2020/09/17(木) 20:14
実は具体的にどの数式がというものが今あるわけではないのです。
私も丁度
[[20200915121651]] 『【IF関数】条件が複数すぎる。男か女か、〇以上な』（りりー）
を見て、（これが理解できなかったわけではないのですが）そういえば過去に10行くらいに改行された数式見たなー、アレどうにかできなかったのかなーと思った次第です。
自分で把握するように作業列的なものを作ってみたらよかったやもしれませんね。
（VBAVAA） 2020/09/18(金) 11:25
理解する上でどうでしょうね・・・ ちょっとやってみましたけど、分解すると前者の方がわかりやすいけど、 1行で見ると後者の方がわかりやすそうに感じました。
=IF(OR((B1="男")*(C1>=578),(B1="女")*(C1>=517)),"↑",IF(OR((B1="男")*(C1<=437),(B1="女")*(C1<=375)),"↓",""))
=IF(
OR(
(B1="男")*(C1>=578), //男且つC1が578以上
(B1="女")*(C1>=517) //または女且つC1が517以上
), //の場合
"↑", //↑を表示する
IF( //そうじゃない場合
OR(
(B1="男")*(C1<=437), //男且つC1が437以下
(B1="女")*(C1<=375) //または女且つC1が375以下
), //の場合
"↓" //↓表示
"") //いずれでもない場合、何も表示しない
)
=IF(B1="男",IF(C1<=437,"↓",IF(C1>=578,"↑","")),IF(C1<=375,"↓",IF(C1>=517,"↑","")))
=IF(
B1="男", //B1が男ならば
IF(
C1<=437, //C1が437以下なら
"↓", //↓表示
IF( //そうでなければ、
C1>=578, //C1が578以上ならば
"↑", //↑表示
"" //いずれでもなければ、何も表示しない
)
),
IF( //男以外ならば
C1<=375, //C1が375いかなら
"↓", //↓表示
IF( //そうでなければ
C1>=517, //C1が517以上ならば
"↑", //↑表示
"" //いずれでもなければ、何も表示しない
)
)
)
笑さんのLOOKUPも照合の仕方が理解できていればわかりやすいと思います。
↓と意味は同じですよね。
一覧表作っておけば、数値目標に改定があった場合に変更しやすいです。
=INDEX(H:H,MATCH(C1,IF(B1="男",F:F,G:G),1))
|[A]|[B]|[C]|[D]|[E]|[F]|[G]|[H]
[1]| |男 |577|○ | |男 |女 |結果
[2]| |女 |360|↓ | | 0| 0|↓
[3]| | | | | |437|375|○
[4]| | | | | |578|517|↑
条件分岐の場合は、ORとANDの組み方が肝で
配列の場合は、照合の型に理解が必要かと思います。
=INDEX(
H:H, //H列を参照し
MATCH( //行の位置をMATCH関数で指定
C1, //C1の値を
IF(
B1="男", //B1が男なら
F:F, //F列から検索し
G:G //男以外ならG列から検索
),
1 //検索は検索値以上の場合、配列の相対位置を戻す（但し、配列は昇順で並べ替え）
)
)
こんな感じで改行して文字列として出力してくれるツールがあればわかりやすいですよね
（稲葉） 2020/09/18(金) 13:06
簡易版(※)を作ってみました。
※・・と言うか、我々としては、入れ子構造の深さの関係が分かればいいので、 関数特有の情報までは必要ないと思う。 また、余り細かく分解するのも見づらくなるので、2、30文字程度ならそのまま出した方が簡潔である。
Private Const MinLen As Long = 20 '最少分割文字数目途 Private CL As Long Private rOut As Long Private RE As Object
Sub breakDown() 'メイン
Dim sFmla As String
Dim cOut As Long
'初期化
CL = 0
rOut = 1 '書き出し行
cOut = 2 '書き出し列
Set RE = CreateObject("VBScript.RegExp")
Range("A1:B2").NumberFormat = "@"
Range("B2").Value = "Dummy"
Range("A2", Me.Cells.SpecialCells(xlCellTypeLastCell)).ClearContents
'分割対象がA1セルにある前提
Range("A1") = Range("A1").FormulaLocal
sFmla = Range("A1").FormulaLocal
devicdAndShowBlocks sFmla, cOut '分解開始
End Sub
Private Sub devicdAndShowBlocks(ByVal sSRC As String, ByVal cOut As Long)
Dim Pos As Long
Dim numOfPare As Long
Dim i As Long
Dim isWatching As Boolean
Dim startFrom As Long
Dim block
Dim strEx
Dim InTx
startFrom = 1
For i = 1 To Len(sSRC)
If Mid(sSRC, i, 1) = "," Then 'カンマにぶち当たった場合の処置
If Not isWatching Then
block = Mid(sSRC, startFrom, i - startFrom + 1)
rOut = rOut + 1
Cells(rOut, cOut) = block '書き出し
startFrom = i + 1
End If
End If
If Mid(sSRC, i, 1) = "(" Then
numOfPare = numOfPare + 1
If isWatching = False Then
isWatching = True
End If
ElseIf Mid(sSRC, i, 1) = ")" Then
numOfPare = numOfPare - 1
End If
If numOfPare = 0 Then
If isWatching = True Then
rOut = rOut + 1
')以外でカンマor文末 が後方にあればそこまで延長する
strEx = innerText(Mid(sSRC, i, Len(sSRC)), "^\)[^()]*?(,|$)")
block = Mid(sSRC, startFrom, i - startFrom + 1) & strEx
i = i + Len(strEx)
Cells(rOut, cOut) = block '書き出し
If Len(block) > MinLen Then
InTx = innerText(block, "\(.*(?=\))") 'カッコの内側文字を抽出
If InStr(InTx, "(") Then
devicdAndShowBlocks InTx, cOut + 1 '再帰的にドリルダウン
Else
rOut = rOut + 1
Cells(rOut, cOut + 1) = InTx '書き出し
End If
End If
isWatching = False '振出しに戻す
startFrom = i + 1
End If
End If
Next i
If startFrom <= Len(sSRC) Then '未処理文字列処理
rOut = rOut + 1
Cells(rOut, cOut) = Mid(sSRC, startFrom, Len(sSRC) - startFrom + 1) '書き出し
End If
End Sub
Private Function innerText(sToProc As Variant, Ptn As String) As String
Dim Matches As Object
With RE
.Pattern = Ptn
.Global = True
Set Matches = .Execute(sToProc) '実行
If Matches.Count > 0 Then
innerText = Matches(0)
innerText = Right(innerText, Len(innerText) - 1)
End If
End With
End Function
（半平太） 2020/09/18(金) 18:42
↑ まっさらなシートでやると、元の数式が消えてマズいので Sub breakDown() を修正しました。
（半平太） 2020/09/19(土) 12:03
パソコンが使える環境ではなかったので、試せてませんでした。 ちょっといくつかやってみて、いい感じでした！ 四則演算の()まで抽出されてしまうので、やや読みにくい？ 自分で試行錯誤して、使ってみます！
OR((B1="男")*(C1>=578),(B1="女")*(C1>=517)), (B1="男") *(C1>=578), (B1="女") *(C1>=517)
→理想 関数でない()は,で区切るみたいな・・・ OR((B1="男")*(C1>=578),(B1="女")*(C1>=517)), OR (B1="男")*(C1>=578), OR (B1="女")*(C1>=517)
（稲葉） 2020/09/21(月) 09:08
＞四則演算の()まで抽出されてしまうので、やや読みにくい？
自分で言うのもなんですが「同感」です。
まぁ、最低、入れ子の深さの関係が見れればいいかなと・・
・・と言うか、私のレベルでは、これ以上の対応は無理です。
（半平太） 2020/09/21(月) 12:36
RegEXPで挑戦して挫折しました・・・ 半平太さんの「無理」は「やる気がおきない」ですよね・・・！
評価する文字が ( のとき、直前の文字に ({*/-+, が含まれる場合、改行しない感じでそれなりに動かしてみました。
次、数式を説明する機会があれば使ってみます。（聞いてくれる質問者が入ればですけど・・・） 今回は満足してしまったので、こういう機会があればまたよろしくお願いします！
Function UF_analysisFunc(moji As String, Optional func_search As String = "", Optional func_numof As Long = 1) As String
'計算式改行分析用ユーザー定義関数
'UF_analysisFunc(セルまたは文字,[抜き出したい関数名],[抜き出したい関数の出現順番])
'書式設定で、「折り返して全体を表示」
'A1に計算式の「文字列」がある場合
'使用例 1
'B1=UF_analysisFunc(A1)
'使用例2
'A1式=IF(OR(A1>10,A1<5),"↑",IF(OR(B1>20,B1<10),"↓","○")
'B1=UF_analysisFunc(A1,"OR",2)・・・二番目のORを抜き出す
'→OR(B1>20,B1<10)
Dim s As String '一文字ずつ分解した文字列
Dim idx As Long '文字を分解するインデックス
Dim bf As String '直前の文字を保管
Dim isArith As Boolean '四則演算中の文字か判定
Dim isString As Boolean '文字列中または配列中か判定
Dim ind(100) As Long 'インデントの配列
Dim n As Long 'インデントの配列の要素
Dim ans As String '出力する文字列
Dim cnt As Long '文字数のカウント 途中でリセットするので、インデックスとは別
Dim op_strt As Long '検索開始位置
Dim x As Long 'オプション処理のループ処理
'//オプションの処理
If func_search <> "" Then
x = 0
op_strt = 1
Do
op_strt = InStr(op_strt + IIf(op_strt > 1, Len(func_search), 0), moji, func_search)
x = x + 1
Loop Until x = func_numof
End If
op_strt = IIf(op_strt = 0, 1, op_strt)
'//オプション処理ここまで
'//抜き出し処理
For idx = op_strt To Len(moji)
s = Mid(moji, idx, 1)
cnt = cnt + 1
Select Case s
'//文字が ( の場合
Case "("
If InStr(1, "({*/-+,", bf) > 0 Then
'//直前の文字が、計算らしき場合、フラグをオン → ) の評価で使用する
isArith = True
ans = ans & s
Else
'// ( で改行した場合、インデント要素を増やし、カウントした文字数を代入する
n = n + 1
ind(n) = cnt
ans = ans & s & vbCrLf & Space(ind(n))
End If
'//文字が ) の場合
Case ")"
If isArith = True Then
'//フラグがオンの場合、改行せず、フラグをオフにする
isArith = False
ans = ans & s
Else
ans = ans & vbCrLf & Space(ind(n)) & s
'// ) で改行した場合、インデント要素を1つ減らす
n = n - 1
'// ( に対し ) が0になったら、ループを抜ける
If n = 0 Then Exit For
End If
'//文字が " { } の場合
Case """", "{", "}"
'//文字列または配列が始まる・終わるときにフラグをオンオフ → , の評価で使用
isString = IIf(isString, False, True)
ans = ans & s
'//文字が , の場合
Case ","
'//文字列または配列の中と考えられる場合、改行しない
If isString = True Then
ans = ans & s
Else
ans = ans & s & vbCrLf & Space(ind(n))
'// , で改行が発生した場合、cntを直前のインデントに戻す
cnt = ind(n)
End If
'//上記以外の文字
Case Else
ans = ans & s
End Select
bf = s '//今の文字を保存し、次のループで直前の文字として扱う
Next idx
UF_analysisFunc = ans
End Function
※13:00 変数誤り修正
（稲葉） 2020/09/21(月) 12:58
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.