元データをコピペするだけで使える表が作成！Excel関数のメリット
ようやく競馬データで=IFSを使った簡単なサンプルが発生したので、ここに記載しておく。
発生したというのは、実際に使うということで、VBAより実践的な部分が関数にはある。
=IFS関数 Microsoftサポート公式を見て簡単に使い方を理解する
=IFS関数の具体的な使い方で、ココに来られた方はMicrosoftサポート公式を読んで頂ければ、おおよそ理解できると思う。
ここで理解不能になる方は、きっと成績＝58、結果＝Ｆの場合だと思うが、これは何で？と思うより始めて数学の公式をならった時のように、まずは公式的に当てはめてみることをお勧めする。
構文の公式
- =IFS(A2>89,”A”,A2>79,”B”,A2>69,”C”,A2>59,”D”,TRUE,”F”)
といった文が公式の使用例１の表の下あたりにある。
これを更に短くすると、
- =IFS(A2>89,”A”,A2>79,”B”,TRUE,”F”)
- =IFS(A2>89,”A”,TRUE,”F”)
こんな感じになると理解できていれば公式は理解できた。
もう使えるよw
※この文はTRUEがちょっと理解しづらい構文になっているが、プログラムなんてこんなもんである。言葉もいちいち何で？と思う前に使った方が早く喋れるようになるのと同じだね。
- =IFS(A2>89,”A”,TRUE,”F”)
セル値のＡ２が８９より上なら、”Ａ”と表示、TRUE：でなければ、”Ｆ”と表示
※でないなら通常は、False なんだけどねｗ
つまり関数前半部分を全て否定する場合は、TRUE と最後に宣言して、表示する値を入れる。
=IFS(A2>89,”A”,TRUE,”F”)
コレを構文として覚えておいたら良い。
でも忘れるので、ノートやメモ帳、ブログなど自分の使っている忘記用にメモすればOK！
私はプログラムや関数は自分のために無料ブログでもイイから簡単にまとめとおいた方が、また探さなくなるので、便利だとは思うが、自分の検索ツールとして今は他にもいろいろあるので利用した方が良い。
さて、これでIFS関数が使えるようになったので、
実際に単勝・複勝オッズをＪＲＡホームページからコピペして、購入判断する表を作成する方法を紹介しておく。
JRAホームページの単複オッズから相手馬を抽出する条件を設定
今回は私が使用している相手馬抽出条件設定をそのまま利用するので、これを参考にして自分の業務に落とし込んでもらえたら幸いである。
抽出条件
- 複勝>1.0 & <2.0 （複勝１倍台ってこと）
- 単勝>2.0 & <3.0 （単勝２倍台ってこと） & 複勝>=2.0 （複勝２倍以上ってこと）
この２つの条件に当てはまるウマがいたら、相手候補として考えるといった表を作成する。
※元データを上手くコピペするExcel表の作成手順
※コピペ用シート詳細手順は、ブログ内、下部で具体的に説明。
- 元データを上手くコピペするExcel表の作成手順
今回は「文字列」指定しない方が手順が少なくて済むのでコレは使用しなくてもよいが、”－”や”：”は日付、時刻に自動変換されたり、小数点がコピペ上の表示では上手くされているが、Excelのデータとしては整数になっているなどの問題が起きることがあるので、出来れば元データは「文字列」で保存するようにして、後でプログラム的に変換する方が、間違いがなくなる。
せっかくコピペしたデータが保存の仕方が間違っているために全てやり直し！といったことが現実に目の前で起きると、その時間が長ければ長いほど「ショック」も大きくなる。
例：抽出条件１：複勝>1.0 & <2.0 （複勝１倍台ってこと）
これは単勝オッズの列単体で抽出できるので、わざわざ関数を使うまでもない！
Excelの条件付き書式を使えば簡単にハイライトできるが、今回はIFS関数の勉強も兼ねているので関数で別の列を使用して判断する方法をとる。
※Excelの条件付き書式 の詳細手順は、ブログ内、下部で具体的に説明。
- Excelの条件付き書式を使ってセルを簡単にハイライトする手順
2022.4.24（日）現在だと、「第57回サンケイスポーツ賞フローラステークスGⅡ」
今回のオッズをコピペするとExcelで変な変換は行われないので、特に「文字列」指定しなくても問題ないが、勝手に日付変換されてしまう「タイムとか着差」などの結果データを扱う場合には、「文字列」指定が望ましい。後でデータを使うといった場合など、直ぐに気が付かない場合があるので、文字列（テキスト）で元データを落としておく必要がある。よくプログラマーがExcelやCSVでなくテキストで落とせというのは、速さだけでなく、こういった意味もある。
| 単勝・複勝オッズ（馬番順） | |||||||||
| 枠 | 馬番 | 馬名 | 単勝 | 複勝 | 性齢 | 馬体重 | 負担重量 | 騎手名 | 調教師名 |
| 枠1白 | 1 | ルージュエヴァイユ | 4.3 | 1.7-2.2 | 牝3 | 54.0 | 戸崎 圭太 | 黒岩 陽一 | |
| 枠2黒 | 2 | エリカヴィータ | 12.3 | 3.0-4.2 | 牝3 | 54.0 | 田辺 裕信 | 国枝 栄 | |
| 3 | パーソナルハイ | 7.1 | 2.4-3.3 | 牝3 | 54.0 | 吉田 豊 | 矢作 芳人 | ||
| 枠3赤 | 4 | ストキャスティーク | 38.1 | 6.0-8.7 | 牝3 | 54.0 | 石川 裕紀人 | 大竹 正博 | |
| 5 | エバーハンティング | 65.9 | 8.2-11.9 | 牝3 | 54.0 | 内田 博幸 | 小島 茂之 | ||
| 枠4青 | 6 | マイシンフォニー | 12.1 | 2.7-3.7 | 牝3 | 54.0 | 武 豊 | 松永 幹夫 | |
| 7 | ゴールデンアワー | 29.0 | 5.8-8.4 | 牝3 | 54.0 | 横山 武史 | 加藤 士津八 | ||
| 枠5黄 | 8 | キタサンシュガー | 20.0 | 4.4-6.3 | 牝3 | 54.0 | 大野 拓弥 | 清水 久詞 | |
| 9 | ラスール | 5.1 | 1.8-2.4 | 牝3 | 54.0 | C.ルメール | 宮田 敬介 | ||
| 枠6緑 | 10 | ホウオウバニラ | 17.7 | 3.6-5.1 | 牝3 | 54.0 | 横山 和生 | 安田 翔伍 | |
| 11 | トゥーサン | 113.9 | 16.4-23.8 | 牝3 | 54.0 | 津村 明秀 | 吉岡 辰弥 | ||
| 枠7橙 | 12 | モチベーション | 180.2 | 31.7-45.9 | 牝3 | 54.0 | 永野 猛蔵 | 清水 英克 | |
| 13 | ルージュスティリア | 3.8 | 1.5-1.9 | 牝3 | 54.0 | 福永 祐一 | 藤原 英昭 | ||
| 枠8桃 | 14 | シンシアウィッシュ | 24.1 | 4.3-6.1 | 牝3 | 54.0 | M.デムーロ | 吉村 圭司 | |
| 15 | ヴァンルーラー | 42.3 | 6.4-9.2 | 牝3 | 54.0 | 藤岡 佑介 | 吉岡 辰弥 | ||
※必要なデータは上記の ⇧コレ⇧
「文字列」として貼付けされたサンプル
これに式を入れると、こんな感じで変換されない。～(￣▽￣)
この場合、これから入力する列や行を「標準」に戻す必要がある。
上記表だと
- 「K列」をクリックして列を選択する
- 「CTRL+SHIFT+右矢印▶」→ 「右クリック」 → 「セルの書式設定」 → 「標準」 → 「ＯＫ」
※行もデータ加工する場合は同様に指定し直す（今回は行は必要ない）
こうしておいて
Ｋ39に書いた式をコピペして、再度、貼り付け直しEnterキーを押すと、ちゃんと数値に変換される。
これでいつものように使えるので、
セルの「K39」をクリックし、式を入力する
- =IF(ISBLANK(E39),””,VALUE(LEFT(E39,SEARCH(“-“,E39)-1)))
セルの「L39」をクリックし、式を入力する
- =IF(ISBLANK(E39),””,VALUE(RIGHT(E39,LEN(E39)-SEARCH(“-“,E39))))
この場合、=IFSを使用しても関数のネストになるので、（）の数は少なくならない。=IFでも=IFSでも、どちらでも良い。※=IFSの勉強も兼ねているので、コレも記載しておく。
セルの「K39」をクリックし、=IFSで式を入力してみる
- =IFS(ISBLANK(E39),””,TRUE,VALUE(LEFT(E39,SEARCH(“-“,E39)-1)))
TRUE が大事！
セルの「L39」をクリックし、=IFSで式を入力してみる
- =IFS(ISBLANK(E39),””,TRUE,VALUE(RIGHT(E39,LEN(E39)-SEARCH(“-“,E39))))
「K39:L39」を下に頭数分だけコピー（ドラッグ）する
コピペした複勝データを関数を使ってセルに分解し格納した結果
これで、「基礎データ」がようやく揃う！
これでも良いのだが、元データをコピペしたシート１とこれから加工するシート２を別にするのが、ちょっとしたプロ的工夫になる。
こうすると、元データはそのままコピペしたデータとして残り続けるので、何かバグがあった場合、修正加工することが簡単。データを取り直す必要がない！
元データを上手くコピペするExcel表の作成手順
シート１に元データを「文字列」でコピペする
ここまでは同じだが、次に「シート２」を用意（これは通常のままで良い）
上の表だと、使うのは「A37:J53」なので、この範囲をコピーして「シート２」の「A1」にコピペ
範囲以外の「Ｋ列」以降、「18行」以降は関数を入れると値に変換される
但し元データは「文字列」のままなので、「単勝」を数値として扱うなら、数値変換する必要がある。
- 「K3」に複勝min
- =IFS(ISBLANK(E3),””,TRUE,VALUE(LEFT(E3,SEARCH(“-“,E3)-1)))
- 「L3」に複勝max
- =IFS(ISBLANK(E3),””,TRUE,VALUE(RIGHT(E3,LEN(E3)-SEARCH(“-“,E3))))
- 「M3」に単勝を数値として格納し直す
- =VALUE(D3)
抽出条件の再確認
- 複勝>1.0 & <2.0 （複勝１倍台ってこと）
- 単勝>2.0 & <3.0 （単勝２倍台ってこと） & 複勝>=2.0 （複勝２倍以上ってこと）
複勝>1.0 & <2.0 （複勝１倍台）をｘｘ列で判定する
- =IFS(K3<1,””,K3>=2,””,TRUE,-1)
単勝>2.0 & <3.0 & 複勝>=2.0 をｘｘ列で判定する
- =IFS(M3<2,””,M3>=3,””,K3>=2,-1,TRUE,””)
完成した表
K列～O列までが関数を入れて作成した表
A列～J列までは元データをコピペしたSheet1からコピペした「文字列」なデータ
| 馬番 | 馬名 | 複勝min | 複勝max | 単勝 | 複1.0-1.9 | 単2複2以上 |
| 1 | ルージュエヴァイユ | 1.7 | 2.2 | 4.3 | -1 | |
| 2 | エリカヴィータ | 3 | 4.2 | 12.3 | ||
| 3 | パーソナルハイ | 2.4 | 3.3 | 7.1 | ||
| 4 | ストキャスティーク | 6 | 8.7 | 38.1 | ||
| 5 | エバーハンティング | 8.2 | 11.9 | 65.9 | ||
| 6 | マイシンフォニー | 2.7 | 3.7 | 12.1 | ||
| 7 | ゴールデンアワー | 5.8 | 8.4 | 29 | ||
| 8 | キタサンシュガー | 4.4 | 6.3 | 20 | ||
| 9 | ラスール | 1.8 | 2.4 | 5.1 | -1 | |
| 10 | ホウオウバニラ | 3.6 | 5.1 | 17.7 | ||
| 11 | トゥーサン | 16.4 | 23.8 | 113.9 | ||
| 12 | モチベーション | 31.7 | 45.9 | 180.2 | ||
| 13 | ルージュスティリア | 1.5 | 1.9 | 3.8 | -1 | |
| 14 | シンシアウィッシュ | 4.3 | 6.1 | 24.1 | ||
| 15 | ヴァンルーラー | 6.4 | 9.2 | 42.3 |
※必要な列だけ、取り出すとこんな感じ！
単勝が2.0～2.9＆複勝が2.0以上の場合の比較がこれだと分からないので、⑥マイシンフォニーの単勝を２と直接入力すると。。。
ちゃんと動作しているっぽい～ネ💛
このように、=IFSは=IFよりも条件を工夫して入れることで、ネストが深くならずに見やすい関数表記が出来るので、あとでメンテがしやすい表が完成させることが可能。これならVBAにわざわざしなくても、これで完成としても私ならOK!だし、実際に使っている～💗～
※ちなみに、このレースの結果は②-3-14で私の買った⑤⑥はどこにもいなかった。という情報はいらない、必要ない、全く用のない情報である😭
Excelの条件付き書式を使ってセルを簡単にハイライトする手順
上記の表のようにＮ列、Ｏ列、に式を入れて判断する方法もあるが、もっと簡単にデータをハイライトできるやり方もある。
特にＮ列のように、複勝データ単独の倍率の範囲を判断するだけなら、Excelの機能である「条件付き書式設定」を使った方が簡単で、見やすいデータが表示されるので、私は結構多用している。
条件を再設定する
- 複勝>1.0 & <2.0 （複勝１倍台）1.0-1.9
- 単勝>2.0 & <3.0 （単勝２倍台）2.0-2.9
単純に列の値を取り出して、範囲内のデータであればハイライトするという簡単なやり方だが、毎度々設定するのは面倒なので、コレもコピペで簡単に利用できるようにする。
まず条件でハイライトする表を作成するのだが・・・
上記で勉強した技術を使って、M列までを関数を入れて作成。ここではコレをSheet3とする。
Sheet3を作成したばかり「条件付き書式設定」前の表：Sheet3
現行は15頭なので、これを汎用的に使えるように18頭可能なように18番まで入れる
- 馬番18まで作成
- 複勝min、複勝max、単勝の式も１８行コピペして表を完成させる
- シートの名前を変更する
- Sheet1を「元データ」
- Sheet2を「関数判定」
- Sheet3を「条件付き書式」
複勝>1.0 & <2.0 （複勝１倍台）1.0-1.9 を「条件付き書式設定」で指定
- 上記表で「K3:K20」を選択する
- ホーム → 「条件付き書式」を選択する
- 「条件付き書式」から
- →「セルの強調表示ルール(H)」
- →「指定の範囲(B)」
- →「0.1～1.999」
- デフォルト「濃い赤の文字、明るい赤の背景」
「条件付き書式設定」をしたい範囲は選択して、ボタンを押す
「条件付き書式」から →「指定の範囲(B)」を選択する
数値を入力して、書式を選ぶ。今回はデフォの「濃い赤・・・」で良い
単勝>2.0 & <3.0 （単勝２倍台）2.0-2.9 を「条件付き書式設定」で指定
- 上記表で「M3:M20」を選択する
- ホーム → 「条件付き書式」を選択する
- 「条件付き書式」から
- →「セルの強調表示ルール(H)」
- →「指定の範囲(B)」
- →「2～2.999」
- デフォルト「濃い赤の文字、明るい赤の背景」
これで次回からは、このシートをコピペして利用すれば、「新しいレース」をコピぺすれば汎用的に使える。というもんだ。でめたし！でめたし！っと。
とりあえず、今日はこんなもんで、いや。。。こんなもんにしといてやろう。。。疲れてはないぞぉ～
それでは、またね～😎
編集後記
表計算の関数もよく使っているうちは、その構文を覚えているが、しばらくすると忘れてしまい。
あれ？ということは良くある。
そして自分が良く使う関数やプログラムは言葉と同じでそんなに多くはない。
ただ関数だけでデータ整理をすると、関数同士を組み合わせるので、どうしても見にくい。
そうなると、VBAの方が後から見直せる仕様書的な意味も含めて使い勝手はよくなるが、
MS-Office2019以降（MicroSoft365)、「IFS,SUMIFS,COUNTIFS」が使えるようになり、
かなりネスト（入れ子）の関数のメンテがやり易くなってきた。
