【Excel】=INDIRECT関数を使い出したらExcelVBAの勉強をする時期

ExcelでINDIRECT関数を使う2つの場面

ExcelでINDIRECT関数を使いだしたら、そろそろExcelVBAのお勉強の時期に来ていると言って良い。
INDIRECT関数はセル値に書いている文字列をそのまま代入する関数といて使われるので

1. 範囲指定のある関数と組合わせて利用する（例：index-match,vlookup,xlookup）
2. 別シートにコピペしたデータを纏めシートを作成する（sheet1.A2…sheet12.A2)

ほぼこの2点に集約される使用法になる。
この関数を使うということは、汎用的にデータを扱う必要性がある纏めシートを作成する
ということなので、もうプログラミングに片足突っ込んでいると言って良い。

私もこの関数はよく使うが、どういった場合に使用するのか？というと

• プログラム化するに至らないテスト段階
• 関数だけで目的のシートが完成する場合

この2点になる。
つまりExcelVBAを使う必要が他で出てくるような内容の成果物を求められている場合は
始めからExcelVBAを使う。
もしくはテスト段階で、コピペしてきたシートからデータ抽出して比較する場合によく用いる。
最終的にテストOk!となった後は、ExcelVBAを使うので、この関数を使っている場面もVBAに置換する。

今回は実例として、競馬の単勝を購入する場合に、倍率によって得をする「馬単」を見つけ出し購入馬券を
「単勝」と「馬単」に資金分配するExcelシートから

単複オッズの範囲を自動的に指定する関数を設定してみる。

仕様：単勝オッズをコピペ、馬番入力で「単複オッズを表示する」サンプル作成

例：2022.7.31 アイビスSDオッズをJRAホームページからコピペし、Excelシートに貼り付ける。

単勝・馬単　資金配分表サンプル作成手順

セルA1に単勝オッズを貼り付けると、下部の[A98:J115]に単勝オッズを発見

※今回、使うデータは「馬番と単勝オッズ」

今回、使うデータは「馬番と単勝オッズ」

この表で、毎回貼り付けたら行97に見出し「枠　馬番　馬名　単勝　複勝　・・・」となれば良いのだが
この行は毎度毎度変わるので、固定で関数の範囲指定が出来ないといった悩みが発生する！

ExcelVBAを使用すれば文字列で行数を判定する自作モジュールを作成すれば良いだけだが
Excel関数だけだとコピペされたデータだけでは自動で判定できない場合もある。

今回のコピペされたデータを見ると、B列～J列にはこのオッズ表データ以外ないので
馬番最大値[18]の行数が分かれば、必要なデータ範囲が自動で取得できそうな気はする。

A1に単勝オッズをコピペした時点で、ポン！と赤枠 [U1:X8] のデータが判定出来れば
次回コピペデータの行が変わったとしても、そのまま使えるということになる。

その関数の設定は、次の通りである。

赤枠 [U1:X8]のExcel関数設定

V1:V8の関数の設定値

ここから購入振分計算	V1～V8
出走頭数：	=MAX(B:B)
枠整列
選択馬番
	単勝倍率
	=IF(ISBLANK(U6),””,INDEX(INDIRECT($X$4),MATCH(U6,INDIRECT($W$4),0),3))
	=IF(ISBLANK(U7),””,INDEX(INDIRECT($X$4),MATCH(U7,INDIRECT($W$4),0),3))
	=IF(ISBLANK(U8),””,INDEX(INDIRECT($X$4),MATCH(U8,INDIRECT($W$4),0),3))

W1:W8の関数の設定値

W1～W8

=IF(X2<V2,0,X2-V2+1)

=”B” &W2

=”B” &W2 &”:” & “B”& X2

複勝min

=IF(ISBLANK(U6),””,VALUE(LEFT(X6,SEARCH(“-“,X6)-1)))

=IF(ISBLANK(U7),””,VALUE(LEFT(X7,SEARCH(“-“,X7)-1)))

=IF(ISBLANK(U8),””,VALUE(LEFT(X8,SEARCH(“-“,X8)-1)))

X1:X8の関数の設定値

X1～X8

=MATCH(V2,B:B)

=”E”& X2

=”B” &W2 &”:” & “E”& X2

複勝

=IF(ISBLANK(U6),””,INDEX(INDIRECT($X$4),MATCH(U6,INDIRECT($W$4),0),4))

=IF(ISBLANK(U7),””,INDEX(INDIRECT($X$4),MATCH(U7,INDIRECT($W$4),0),4))

=IF(ISBLANK(U8),””,INDEX(INDIRECT($X$4),MATCH(U8,INDIRECT($W$4),0),4))

これでJRAホームページから単勝データをコピペすると自動でデータ範囲を指定するシートが完成した。
後は、これを原紙として必要時に新しいシートを作成すれば毎レース使用できるモノとなる。

このシートのキモは、

出走頭数：=MAX(B:B) を見つけられるか否か！？というところ

あとは、index-match関数、indirect関数を一度でも使ったことあれば作れるかな？

という感じにはなる。

ここまで来ると、もうプログラミングの範疇なので、個人でExcel関数を利用するなら問題ないが
業務となると、いろいろ考えさせられる場面であるとだけ言っておく。
私ならこれを業務化してもらうか別途請求（給料交渉）してダメなら私もダメ！ってことでやらない。

「やらない！と言うと角が立つ！」って人はExcelの勉強ではなく、交渉術を勉強した方が良い。

分かりました！と言って、分からないことは全て責任者に聞くだけで「やらない！」と言わなくても
使い勝手の良い人にその仕事は、自然と振り分けられていくのである。
ただ確実に私の昇級コースからは外れるが、そこで頑張る力を他に求めるだけで「カネ」は稼げる。

編集後記

ExcelVBAおよびExcel関数を使える強みは、その企業のパソコンにもExcelはほぼ常駐しているということ！
そのパソコンで自分が作業する場合に、PythonやUWSCもしくは他のツールの場合を使わないと出来ない事は
その環境が用意されてあるマシンを常に自分で用意しておかなければいけない。

その点Excelを使えば、出来上がりのシートにVBAが添付されていなければ良いので
その過程でExcelVBAもしくはMs-Accessを使ってデータ加工して出来上がりのシートを用意すれば良いだけ。
そのお会社のパソコンにある環境だけで、さっさと仕事を終わらせることが可能になる。

いつも自分のパソコンを用意できる環境にあれば問題ないが
それを業務にするよりもスキルを活かすやり方が、今の時代には適しているように感じる。

定時に帰れる自分だけの業務改善といったところか。
Excel関数を使って組んだシートでマウント取ってくる上司・同僚がいる場合は
ExcelVBAで組み直し現行シートを使っていない「オジサマ」に配布しまくる。
こうすると使いにくい関数シートでマウント取ってくるヤツは排除できる。

また個人持ちのパソコンNGな会社の場合
余っているパソコンで既に使っていないジャンク扱いなパソコンの使用許可を貰い。
SSD化して、そこにPythonやUWSCを入れて環境を整えるといったことも出来る。

資産番号とか管理番号が活きているマシンならば
総務か経理に聞けば100％出てくるしゴミ活用なのでほぼ反対されない。
但し配布したExcelVBAシートの不具合は全て私がメンテしないといけなくなるがね。😨

SSDは自費で提供するとしよう～🤭
120GB\2000-程度の時間効率は一日で元が取れる計算になる。

• Zheino SATA SSD 120GB 内蔵SSD C3 2.5インチ 7mm厚 3D Nand 採用 SATA3 6Gb/s
• Excel VBA 逆引き大全 600の極意 Microsoft 365/Office 2021/2019/2016/2013対応

それでは、またね～😎