スポンサーリンク

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

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

こんにちは、とりっぷぼうるです。

元々エクセルとは全く縁のない生活をしていましたが、人員不足などで少しでもデータ入力作業や集計作業を効率化するしか方法が無く、仕方がなくエクセルに取り組み始まました。

波乗りアヒル
波乗りアヒル

作業効率化にはユーザーフォームを使いますが、様々な計算などはエクセル関数が必須です。

エクセル関数を使うことができないと、VBAどころじゃありませんし、成績表も作る事ができないのです。

当然ながらエクセルの専門知識もなく、学校に通うこともできずにただ単にエクセルの書籍とにらめっこしながら取り組んでいます。

波乗りアヒル
波乗りアヒル

でも書籍のままだと実用的でないことが多く、エクセル関数を組み合わせてみたら動いた!という実用例・体験談を書いています。

Excelの専門学校通学や・資格があるわけではありませんので、エクセル関数を組み合わせてみたらエラーにならずに動いてくれているだけなので、掲載の数式などが同じように動く保証はできません。
実際に動いているエクセルは2009と2016です。
スポンサーリンク

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

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

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

波乗りアヒル
波乗りアヒル

だいだい何点ぐらいのハンデキャップが多いの?

女性だけに1ゲームあたり20ピンとか30ピンが一番多いですね

波乗りアヒル
波乗りアヒル

じゃあ、リーグ戦も同じでいいんじゃないの?

そうそう、それが一番簡単なんだけど、スポーツ競技としてのボウリング!そうはいきません!

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

リーグのルールにより単純な数式でHDCPが出せるものから、様々な条件がある為に複雑になる場合がありますので、リーグ戦ごとにルールでのHDCPがあるとっても過言ではありません。

それでもエクセル数式の組み合わせでなんとかやりくりできるのです

こんな条件のリーグ戦ありませんか?

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

こんなにルールがあるの?

もちろん全て使うものもあれば、この中からいくつかだけ使うのもありますね。

最初の頃は、どうしてもエクセル数式の組み合わせが分からなくて、一部を電卓!そして手入力なんてやってたかな。

時々間違ってしまうことも・・・

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

波乗りアヒル
波乗りアヒル

わーすごい!これで完璧な数式できたんだね。

実は正解が分かりませんが、7つ順番に話すわよ!

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

波乗りアヒル
波乗りアヒル

そんなのでいいのですか?

HDCPの計算が自動でできているからいいんじゃないの

スポンサーリンク

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も計算できるようになります。

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の上限が決まっている場合でも対応できるようになります。

マイナスHDCPは使わない

ボウリングが上手な方いますよね。

基準点が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の最小が決まっている場合でも対応できるようになります。

男女でHDCPが違う

条件を提示します!

  • 200ベース男性90%・女性100%HDCP
  • 上限50ピン
  • AVE170

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

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

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

先ほどまでの数式に「男性」はそのままの計算式で、「女性」の場合は掛け率を100%に。

エクセルの関数や数式を組み合わせて考えてね

 

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

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

IF関数の使い方についてはIF関数ページに詳細を書いているので、ここでは省略します。

エクセルIF関数もし~だったら使い方
ボウリングのリーグ成績表にかかわらず、エクセル関数IFを使うと非常に便利です。もし~だったら○、そうでなかったら×、というような条件って多いです。利用頻度が高いのでぜひマスターして業務効率化をしましょうね。

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

=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列に性別が記載してあるとします。
  • 名前をアヒルとします。
  • 性別は男性

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

=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の欄に数式を書かなくてはならないので大変です。ひと工夫しましょう。

セルのコピー可能にしてね

先ほどのスッキリさせたエクセル数式には共通している箇所がありますよね。

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

まずは、基準点の200が一緒よ!

参加者全員が基準点は一緒なので、毎回200と記入するのは時間のムダで非効率。

=を使ってさらに固定しましょう。

基準点200をセルB2に記載しておく

そうするとこうなります。

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

まず、基準点の200はセルB2に記載という事なので、200をB2に代えます。

そして、$マークを入れてコピーしてもセルB2が変わらないように固定します。

書籍などでは絶対参照って書いてあります。

ファンクションキー「F4」で簡単に指定できるわよ

ふたつめが、HDCPの掛け率も一緒よ!

男性HDCPをセルD2へ、女性HDCPをセルE2に記載します

男性は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をセルC2に記載します

上限HDCPは50と条件づけられているので、変更しましょう。

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

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

4つ目、最後に参加者個人のアベレージを指定しておくと便利よ

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

アヒルさんのアベレージをセルH5に記載します

アヒルさんのアベレージは170なので、170の数字の代わりにセルH5にしましょう。

そうすると、

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

こうなります。

今までのように絶対参照にしちゃダメよ!

この式が、今、セルI5に入っているとます。

この式を他の選手にコピーすると、今まで記載した通り、絶対参照になっている基準点・上限・男女HDCPのところは固定されて、アベレージのセルだけ変わります。

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

小数点以下のAVEの対処

ボウリングのリーグ戦でのアベレージが小数点以下は、切り捨てして整数にしてからHDCP付けるのが一般的なのに・・・

波乗りアヒル
波乗りアヒル

切り捨てが基本なのに四捨五入される!

HDCPの計算方法について記載してきましたが、アベレージが必ず整数ということはありません!

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

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

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

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

INT関数を使ってください!

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

とっても簡単に使えるわよ

先ほどまでで完成した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()で挟んだだけです。

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

プロはどんなスコアーでもHDCPなし

プロボウラーにはHDCPなしが当たり前

ボウリングのリーグ戦ではプロボウラーが一緒に投球する場合もあります。

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

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

そんなレベルのプロは要らん!野球やサッカーなら引退しているぞ

現実を見て!HDCP解決して!

ひとつめは、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関数を使用したリーグ成績表使用例もあわせて、専用のページを用意します。

スタートから何週目までHDCP固定させる

3週目までHDCP固定して4週目から変動させる!

リーグ戦で初日は逆算HDCPとか、前回最終日のHDCPを持ち越して初日を迎えるなどはよくあります。

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

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

波乗りアヒル
波乗りアヒル

ここまで出来たんだから、何とかしてよ!

3週目まで固定で4週目から変動する条件を付け加えてしまえば解決するわ

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

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

3週目(を含む)よりも小さい週。

こうすると、何週目<=3週目という数式になります。

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

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

ただ、このままだとエクセル数式を書くときに、半角英数と日本語全角をいちいち切り替えるのが面倒なので、半角英数にしましょう。

セルA13に固定する週、セルB13に今回の回戦数を入力

そこでセルA13固定する週を、セルB13に今回の回戦数を数字で記入します。

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

=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週目まで固定にできますよ。

先投げの場合はHDCPが変わる

ボウリングのHDCPのルールってっやこしいなあ。まだ条件が欲しいのかい?

ボウリングリーグ戦では先投げや後投げというルールを設定するリーグが多いと思いますが、その時に時々先投げや後投げにペナルティーっぽく減額したHDCP付けます。

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

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

簡単よ!条件を付けるIF関数また付けちゃうだけよ!

条件!HDCP27の人が先投げ時70%に減額

この条件だとさきほどまでの計算式だと、本来の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)

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

セルK6に先投げの時に「1」を入力する条件

=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)

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

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

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

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

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

ただこれで、ある程度の数のリーグ戦が対応できるHDCPの計算式です。

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

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

コメント