スポンサーリンク


ボウリングハンデキャップのつけ方

エクセルで作るボウリング成績表

HDCPの種類はリーグのルールにより複雑に

ボウリングの成績表(リーグ戦)で必ず必要になるのが、ハンデキャップ(HDCP)です。

ボウリングご予約の時に一番多く使われているのが、女性にだけハンデキャップをつけてあげることが多いですね。

だいだい何点ぐらいのハンデキャップが多いの?
F支配人
女性だけに1ゲームあたり20ピンとか30ピンが一番多いかな!
じゃあ、リーグ戦も同じでいいんじゃないの?
F支配人
そうそう、それが一番簡単なんだけど、スポーツ競技としてのボウリング!そうはいきません!

と、言う事で、ボウリングのリーグ成績表で必ず必要になるのがHDCPですね。

リーグのルールにより単純な数式でHDCPが出せるものから、様々な条件がある為に複雑になる場合がありますが、西船ボウルのリーグでもHDCPの計算はひとつのものから、複雑なルールでのHDCPまでさまざまです。

それでもエクセル数式の組み合わせでなんとかやりくりできるようになりました。

F支配人
こんな条件のリーグ戦ありませんか?
  1. HDCPの上限が決まっている
  2. マイナスHDCPは使わない
  3. 男女でHDCPが違う
  4. 小数点以下の問題
  5. プロはどんなスコアーでもHDCPなし
  6. スタートから何週目までHDCP固定させる
  7. 先投げの場合はHDCPが変わる

こんな感じかな。

今、西船ボウルのリーグ戦でのHDCPのルールは・・・

もちろん全て使うものもあれば、この中からいくつかだけ使うのもありますね。最初の頃は、どうしてもエクセル数式の組み合わせが分からなくて、一部を電卓!そして手入力なんてやってたかな。時々間違ってしまうことも・・・

それでも、なんとか調べながら試行錯誤して、ようやく組み合わせを理解して全ての条件をクリアしてHDCPを計算できる数式にたどり着きました。

F支配人
7つ順番に書いていきます。
わーすごい!これで完璧な数式できたのね!
F支配人
実は正解が分かりません。

素人が作るエクセル関数の組み合わせなので、エクセルに詳しい方から見たら無駄なことをしているようなエクセル数式かもしれませんが、HDCPが計算できれば良しとしましょうね。

そんなのでいいのですか?
F支配人
計算できているのでいいのです。
スポンサーリンク

HDCPの計算

一番単純で基本的なHDCPの計算は、基準点からアベレージを引いた数字がHDCPですよね。基準点が200でHDCP掛け率が100%でアベレージが170点の人のHDCPは、リーグっぽく書くと200ベース100%になるので、200-170=30となり、HDCPは30ですよね。数式ならば、

=200-170

小学生の算数みたいになっちゃいました。では、掛け率が90%の場合は、30点×90%なので27になりますね。数式ならば、

=(200-170)*0.9

これに様々な条件を付けくわえていくと、複雑なルールのHDCPも計算できるようになります。

1、HDCPの上限が決まっている

リーグ戦ではHDCP上限なしもあると思いますが、ほとんどのリーグ戦では上限HDCPを決めている場合が多いと思います。

この場合は、エクセル数式「MIN」を付け加えてあげると、計算できるようになります。

計算されたHDCPと上限の50と比べて小さい数字を表示する。って書くと分かりやすいかな。

  • 200ベース90%HDCP
  • 上限50ピン
  • AVE170

この条件でエクセルで数式を書くと・・・

=MIN((200-170)*0.9,50)

この場合は、HDCPが27なので、上限の50よりも小さいので27が表示されます。もし、アベレージが100点だったら、HDCP90になるので、50と比べて小さい数字は50なので、計算された表示は50になります。

これで、HDCPの上限が決まっている場合でも対応できるようになります。

2、マイナスHDCPは使わない
F支配人
ボウリングが上手な方いますよね。

基準点が200点なのにアベレージが210点とか。

先ほどまでの数式だと、HDCPが-9って、マイナスになっちゃいますが、この場合は0にすることが多いと思います。

そこでエクセルの数式「MAX」と付け加えてあげると、計算できるようになります。

計算されたHDCPと最小の0と「比べて大きい数字を表示する。って書くと分かりやすいかな。

  • 200ベース90%HDCP
  • 上限50ピン
  • AVE170

この条件でエクセルで数式を書くと・・・

=MAX(MIN((200-170)*0.9,50),0)

先ほど同様、HDCPが27なので、最小の0よりも大きいので27が表示されます。

もし、アベレージが210点だったら、HDCPが‐9になるので、0と比べて大きい数字は0なので、計算された表示は0になります。

これで、HDCPの最小が決まっている場合でも対応できるようになります。

3、男女でHDCPが違う
F支配人
男性90%・女性100%

リーグ戦によっては、HDCPの掛け率が同じリーグと異なるリーグがありますよね。

こうなるとHDCPが2種類になってしまい、最初の頃はHDCPの表示欄を2つ用意したり、人数の少ない性別の方だけ、手入力しなおしていたもんですね。

間違いの原因になっていたので、何とかエクセルの数式を組み合わせて解決したかった部分でした。

イメージでは、先ほどまでの数式に「男性」はそのままの計算式で、「女性」の場合は掛け率を100%に。ってやりたかったので、エクセルの関数や数式を組み合わせてみました。

F支配人
もし、性別を入力しているセルの値が「男性」だったら、このままの数式、そうでなかったら(「女性」)、こっちの数式。って感じ!

エクセルの関数で一番最初に覚えた関数IFです。そして今でも1番多く使うエクセル関数です。

エクセルIF関数もし~だったら使い方

2018.06.02
男性の場合の数式は、先ほどの数式を使います。

MAX(MIN((200-170)*0.9,50),0)

女性の場合の数式は、掛け率が0.9(90%)ではなく100%なので(1.0)になります。もちろん1.0は数式に入れる時は小数点以下を切り捨てて整数の(1)になります。

MAX(MIN((200-170)*1,50),0)

これで、男性と女性のそれぞれのHDCPのエクセル数式が完成しました。

次に条件を付け加えます。

もし男性だったら0.9、そうでなかたら(女性)1ですね。

このもし~・・・はエクセル関数のIFをつかいます。

画像を参考にセルC列に性別が記載してますので、名前:市川のHDCPを計算するには、

もし市川さんの性別が(C5)男性だったら・・・そうでなければ・・・になります。エクセル数式にすると、

エクセル数式でHDCP

エクセルでHDCPをつけよう

=IF(C5=”男性",MAX(MIN((200-170)*0.9,50),0),MAX(MIN((200-170)*1,50),0))

となります。このままでの大丈夫ですが、MAXが2回使うので、1回にしてスッキリさせましょう。

=MAX(MIN(IF(C5="男性",(200-170)*0.9,(200-170)*1),50),0)

これでスッキリ!

しかし、このままでは毎回HDCPの欄に数式を書かなくてはならないので大変です。ひと工夫しましょう。
F支配人
セルのコピー可能にする
先ほどのスッキリさせたエクセル数式には共通している箇所がありますよね。

=MAX(MIN(IF(C5=”男性”,(200-170)*0.9,(200-170)*1),50),0)

まずひとつめが、

  • 基準点の200

参加者全員が基準点は一緒なので毎回200と記入するのは時間のムダで非効率。=を使ってさらに固定しましょう。そうするとこうなります。

=MAX(MIN(IF(C5=”男性”,($B$2-170)*0.9,($B$2-170)*1),50),0)

まず、先ほどの図を参考に基準点の200はセルB2に記載してあるので200をB2に代えます。そして、$マークを入れてコピーしてもセルB2が変わらないように固定します。書籍などでは絶対参照って書いてありますね。ファンクションキー「F4」で簡単に指定できますよね。

ふたつめが、

  • HDCPの掛け率

男性は0.9、女性は1と決めているので、先ほどと同じように代えます。

=MAX(MIN(IF(C5=”男性”,($B$2-170)*$D$2,($B$2-170)*$E$2),50),0)

男性のHDCP掛け率はセルD2に0.9と入れてあるので0.9をD2に代えます。そして先ほど同じ絶対参照にするため$マークを加えます。女性のHDCP掛け率はE2にありますので、1をE2に代えて絶対参照にします。

3つめが、

  • 上限HDCPの指定

上限HDCPは50と決まっているので、変更しましょう。

=MAX(MIN(IF(C5=”男性”,($B$2-170)*$D$2,($B$2-170)*$E$2),$C$2),0)

先ほどの図で上限HDCPはセルC2にありますので、50をC2に変更して、絶対参照にします。

最後が、

  • 各個人のアベレージの指定

アベレージは170を入れてありますが、もちろん選手によって異なるし、回戦数を重ねるたびに変わりますよね。数字ではダメですね。

エクセル数式でHDCP

エクセル数式でHDCP

市川さんのアベレージは170なので、170の数字の代わりにセルH5にしましょう。そうすると、

=MAX(MIN(IF(Q5=”男性”,($B$2-H5)*$D$2,($B$2-H5)*$E$2),$C$2),0)

こうなります。今までのように絶対参照にはしません。この式が、今、セルI5に入っています。この式を他の選手にコピーすると、今まで記載した通り、絶対参照になっている基準点・上限・男女HDCPのところは固定されて、アベレージのセルだけ変わります。本八幡さんは、H6。下佐中山さんはH7となります。もし絶対参照を忘れするとセルの位置がずれてしまいエラーになります。

これて、もし基準点が変わった時はセルB2の200を210などにすれば、HDCPも自動的に変わるので便利ですね。

4、小数点以下のAVEの対処
F支配人
切り捨てが基本なのに四捨五入される
HDCPの計算方法について記載してきましたが、アベレージが必ず整数ということはありません!

先ほどまでの数式ですと、小数点以下のアベレージでは問題が発生します。

それは、エクセル数式は基本的に四捨五入されてしまうのです。

例えば女性でアベレージが170.33の時、HDCPは200-170.33=29.67になります。本来は小数点以下切り捨てなので、アベレージは170でHDCPは30になります。

HDCPが小数点にならないように、アベレージを整数にする必要があります。

小数点以下を切り捨てるINT関数

アベレージは小数点以下になることがありますので、小数点以下を切り捨てるエクセル関数INTが便利です。

F支配人
使い方も簡単です

先ほどまでで完成したHDCPの数式のアベレージの部分は、

=MAX(MIN(IF(Q5=”男性”,($B$2-H5)*$D$2,($B$2-H5)*$E$2),$C$2),0)

H5の部分です。ここが整数であればいいので、INTを使います。そうすると、

=MAX(MIN(IF(Q5=”男性”,($B$2-INT(H5))*$D$2,($B$2-INT(H5))*$E$2),$C$2),0)

と、なります。アベレージの部分H5をINT()で挟んだだけです。

これで、小数点以下を切り捨てて整数で計算するので小数点は出なくなります。

5、プロはどんなスコアーでもHDCPなし
F支配人
プロボウラーHDCPなしが当たり前
リーグ戦ではプロボウラーが一緒に投球する場合もあります。

常に基準点以上のアベレージを維持してくれればいいのですが、困ったことに基準点以下のアベレージになってしまうことが残念ながら、出しちゃいます。

そうするとプロボウラーにHDCPがついてしまい。手直しで0にすると、数式が消えてしまうので困ります。

解決してよ!
ひとつめは、3に書いた通り男女別にさらにプロを加えてIF関数を更に加える方法があります。

ボウリング風に書くと、

もし、男性だったら、90%、そうでなかったら、もし、女性だったら100%、そうでなかったら0

ちょっとややこしくなりましたね。数式にするとこうなります。長いです。

=MAX(MIN(IF(Q5=”男性”,($B$2-INT(H5))*$D$2,IF(Q5=”女性”,($B$2-INT(H5))*$E$2,0)),$C$2),0)

数式の中にもう一度エクセル関数IFが加わりました

これで男性なら90%、女性は100%、プロは0になります。

厳密にいうとその他は0なんですけど、一応、これで対応は可能です。

最初の頃は、このようなエクセル関数や数式の組み合わせでリーグ成績表を作っていました。

ところが、ここに従業員がさらに加わって、従業員はHDCPは付くのですが、最終成績のHGなどの対象外になるのでHDCPは対応できても、他の部分で対応できなくなりました。

そこでもう一つ方法のエクセル関数を使います。

エクセル関数CHOOSE

CHOOSE関数はリストの値の中から指定した位置にある値を取り出して表示できる便利なエクセル関数です。

このCHOOSE関数が使えるようになると、HDCPだけでなく、リーグ対戦表やレコードシートまで作成可能になるので、今ではとても使用頻度の高いエクセル関数です。

その為、CHOOSE関数を使用したリーグ成績表使用例もあわせて、専用のページを用意します。

セルの値により複数の答えを探すエクセル関数CHOOSE

2018.06.02
6、スタートから何週目までHDCP固定させる
F支配人
3週目までHDCP固定して4週目から変動させる
リーグ戦で初日は逆算HDCPとか、前回最終日のHDCPを持ち越して初日を迎えるなどはよくあります。

そしてリーグ戦2週目からは前回成績により、HDCPが与えられるのが普通ですが、リーグ戦序盤はHDCPの変動が大きいので、固定してしまう方法も使われます。

先ほどまで書いてきたエクセル数式では、そんな内容は含まれていないので、毎週変動します。

ここまで出来たんだから、何とかしてよ!
F支配人
3週目まで固定で4週目から変動する条件を付け加えてしまえば解決します

もし1週目から3週目だったら、1週目開始時のHDCPを使用、そうでなかったら、変動(先ほど書いた数式)になります。

この1週目から3週目というのはエクセル関数では検索が難しいので、こういう風に言い換えます。

3週目(を含む)よりも小さい週。こうすると、何週目<=3週目という数式になります。

これで、3週目を含む小さい週なので1週目と2週目と3週目が選ばれます。

4週目は3週目よりも小さくないので(大きい)条件に当てはまらないということです。

ただ、このままだとエクセル数式を書くときに、半角英数と日本語全角をいちいち切り替えるのが面倒なので、半角英数にしましょう。そこで先ほどの図でセルA13固定する週を、セルB13に今回の回戦数を数字で記入します。

そして、先ほどまでの数式

エクセル数式でHDCP

固定HDCPの為セルA14:B15に項目を追加

=MAX(MIN(CHOOSE(C6,($B$3-INT(H6))*$D$3,($B$3-INT(H6))*$E$3,($B$3-INT(H6))*$F$3,$G$3),$C$3),0)

に固定する場合の数式を加えると下記のようになります。

=MAX(MIN(IF($B$15<=$A$15,A6,CHOOSE(C6,($B$3-INT(H6))*$D$3,($B$3-INT(H6))*$E$3,($B$3-INT(H6))*$F$3,$G$3)),$C$3),0)

これで、3週目までは初回HDCPを使用し、4週目からは変動HDCPを使用するようになりました。

また、固定週を5にすると5週目まで固定にできますよ。

7、先投げの場合はHDCPが変わる
F支配人
このルールは稀にありますね
リーグ戦では先投げや後投げというルールを設定するリーグが多いと思います。

後投げなんて聞いたことないよ
F支配人
あれ?ボウリング詳しかったっけ?
ちょっと言ってみたかった・・・
F支配人
そんなに経験があるわけではないけど、後投げまであるのは殆ど聞いた事が無いかな。

と、言うわけで、西船ボウルのリーグ戦では後投げはありません。

本題。

先投げの時にHDCPを減らすルールを設定しているリーグがあり、エクセル関数の組み合わせで、どんな数式を使えばいいのか相当悩みましたね。

その為どうしても手入力が避けられない時期が長くありました。

試行錯誤の上、やっとエクセル関数の組み合わせができました。

最初に書いた通り滅多にないので使用しないことが多いと思いますがね。

このルールの前提ですが、図のように本来HDCP35なのですが、先投げなのでHDCPが70%になってしまうというルールです。仮に4週目の場合は、本来のHDCPは27なのですが、その70%になるので19がHDCPになります。

では先ほどのエクセル数式

=MAX(MIN(IF($B$15<=$A$15,A6,CHOOSE(C6,($B$3-INT(H6))*$D$3,($B$3-INT(H6))*$E$3,($B$3-INT(H6))*$F$3,$G$3)),$C$3),0)

これに条件を加えるとすごく長い式になっちゃいました。

=MAX(MIN(IF(K6="",IF($B$15<=$A$15,A6,CHOOSE(C6,($B$3-INT(H6))*$D$3,($B$3-INT(H6))*$E$3,($B$3-INT(H6))*$F$3,$G$3)),INT(IF($B$15<=$A$15,A6,CHOOSE(C6,($B$3-INT(H6))*$D$3,($B$3-INT(H6))*$E$3,($B$3-INT(H6))*$F$3,$G$3))*K3)),$C$3),0)
先投げHDCP

先投げHDCPの為にK列に先投げ項目追加

先投げの場合は数字の1を入力するルールにしました。

そしてセルK3に先投げの場合のHDCPの掛け率を70%に設定。

するとボウリング風イメージは・・・

もし、先投げ欄が空白だったら、先投げではないので通常の計算をしたHDCPを表示、空欄でなかったら、通常の計算をしたHDCPに0.7を掛けて整数にして表示するということになります。

もう少しいいエクセル数式が見当たれば、もっと短い数式になるのですが、今のレベルではここまで長くなっちゃいました。

ただこれで、西船ボウルのリーグ戦全てが対応できるHDCPの計算式です。

あとは、セクレタリーが新しいルールを思いついてしまったら、また頭を抱えて悩むことがありそうですね。

そうすると、またこのページも追加することになるのかな?

セルの値により複数の答えを探すエクセル関数CHOOSE

2018.06.02

DGET関数でシート地獄から解放する

2018.05.31

エクセルで作るボウリング成績表

2017.09.30

スポンサーリンク


24時間以降~180日後までボウリングもネットで簡単ご予約ができます。
もちろん空き状況も15分単位で確認できます。

また、GW期間中もネットでボウリング予約が可能です。