スポンサーリンク


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

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

20週のリーグ戦ならばシートは20以上

正直どのくらいの時間を要したかわかりませんが、セルに「=」を入れてSUMや+-/*の加減乗除やアベレージや小数点以下切り捨てなど、ボウリングのリーグ成績表が人並みにエクセルで入力できるようになった頃、問題に直面。

リーグ参加者の人数が増えると自動的に回戦週数が増えて、シートがどんどん増えていき、そのたびに数式を入れていくことが面倒に思えてきました。

更にリーグの本数が増えると同じ作業を何回も・・・

間違えた数式をコピーしたり、忘れたりで成績が間違っていたり。

さらに問題は、エクセル自体が重くなり、起動時間がかかる。

そんな大変で作業非効率な経験ありませんか?

エクセル覚えたてというよりはやっと数式や簡単な関数を覚えた頃は、エクセルで作ったボウリングリーグ成績表はこんな感じでした。

F支配人
エクセルVBAに出会う前の知識レベルの成績表
昔のエクセルリーグ成績表

30週もやったらシートもそれ以上になっちゃう

当時の成績表は今思うとゾッとしますね。詳しい方は見ただけでわかっちゃいますよね。

シートは34w(34週目)で終わっていますが、1週増えると、1w目をコピーしてシートを追加して累計スコアーから次回HDまでの横8セルの項目が1Wからの累計なので1W+2Wの作業を参加人数分やらなくてはいけないですよね。

3人BLなので10人×8セルで80か所のセルの数式を直さなければならないのです。今はこのサークルは30名いるので、このリーグ成績表使ってたら、1週増えると30×8セルで240か所も修正が必要なり、やってられないですね。

わー!すごい!時間ばっかりかかって、全然終わりそうもなくて非効率ですね。仕事全然できなそう!
F支配人
でも当時の知識ではこれが精いっぱい。

エクセル関数知らなくても一応できる

エクセル全くの素人が無理やりボウリング成績表をつくると、複雑なエクセル関数なんてわかりません。

足し算と掛け算と割り算とHGやHSの最大スコアー計算ぐらいしかできなかったです。

その為に、数式も単純で例えば1Wのゲーム数は3なので2W目のゲーム数は1W目の値+2Wの値と簡単。足し算ですもんね。

アベレージはトータル÷ゲーム数で割り算だし、HGなどは今週と先週を比べて大きい値を表示させればいいのでMAX関数です。

F支配人
とりあえずできたー!
早くエクセル関数覚えろ!そうしないとシートの数減らないぞ!

そうなんです。シートの数を減らすには、1W目、2W目・・・と回数ごとに増えていくシートを一つにまとめないといけないのです。

この問題を解決してくれるのが、DGET関数なのです。

DGET関数で成績表を表示させる

DGET関数とは、指定された条件を満たす 1 つの 値を抽出するExcelのデータベース関数って書いてあります。

F支配人
最初の頃は何かの呪文?って状態でした。

なぜなら・・・

【指定された条件を満たす 1 つの 値を抽出するExcelのデータベース関数】

って書いてあっても意味不明!

ただ、これであきらめてはいけないので試行錯誤しながら試してみた結果、呪文の呪縛から解放されました。

要するに、何週目かを指定すると表の中から表示したい項目(たとえば1G目のスコアー)を探し出して見つけてくれるって事でした。

このDGET関数が理解できたら、1枚のシートに選手の成績を入力する項目をまとめられたので、シートの数は5枚から7枚ぐらいまで少なくすることができ、シートを探す時間も大幅短縮!

シート地獄から脱出成功!

DGET関数を使おう

最初に書いた通り、このエクセル関数が理解できずというよりも使おうとした時に、データベース?フィールド?条件?

なんて表示されても意味が分からずに諦めていた為に、エクセルのシートがどんどん増えていました。

sheet1がお客様に渡す成績表、sheet2が1w、sheet3が2w、sheet4が3w・・・・sheet21が20w最終。

またはsheet1が同じくお客様に渡す成績表、sheet2がAさん、sheet3がBさん・・・sheet27がZさんなんて。

このほかに受付表やレコードシートなど複数ありますよね。

人数や対戦回数が増えれば増えるほど、シートが増えてスコアー入力するのにまず入力したいシート選びから始めなくてはいけなかったです。

また、参加人数が変わるたびに、シートのコピーやコピーした後の数式を修正しての繰り返し作業。

その為に、コピーしただけで数式変更忘れたりして間違ったりで、毎回リーグ初日から次回までの1週間はこの作業でとんでもなく時間が足りませんでしたね。

F支配人
これではもう無理!って思い。理解不能のあのDGET関数にチャレンジしました。
やっとやる気になったんだね。

このDGET関数が使えるようになるだけで成績表の毎週分、複数あったシートがたった1枚になり、エクセル自体が軽くなり読み込みも保存も早くなりました。

DGET関数は意味が理解できれば簡単に使いこなせるようになりますので次はボウリング風に書いてみます。

DGET関数

ネットやエクセルの本を見ても成績表の中から条件に合ったセルの値を取得するような探し方をするとDGET関数を使うように書いてありますね。そして理解に苦しむ用語が書いてあります。

「データベースの列から指定された条件を満たす1つのレコードを抽出します」と表示され、そして使ってみようとすると、=DGET(データベース.フィールド.検索条件)なんですが、意味が分からず当時は挫折しましたね。

これをリーグ成績表の言葉にすると下図のように、個人専用の成績表を作っておけば、必要な値は全てこの中にある状態になりますよね。

個人成績表

個人成績表の中から必要な数字を取得する

この表の範囲(A3:W40)がDGET関数でいう、データベースになります。

次にフィールドですが、単純に欲しいデータです。そのデータは3列目A~Wまで回数入力から次回HDまでありますよね。この中から、TOTALのスコアーが欲しい場合TOTALを指定します。

この列で同じ名前は使ってはいけません。

DGET関数を使うときに同じ名前がデータベース内にあるとエラーになりますので、その為にG列は「計」にして、O列は「TOTAL」にしてあります。同じ合計なんですけどね。

この表で(O3)がDGET関数でいう、フィールドになります。

最後に検索条件ですが、何週目のTOTALスコアーが欲しいかということになります。

A列の3行目(A3)に回数入力、その下が回数です。

1は1週目2は2週目・・・最大36週まで作ってあります。なぜ36週で作ってあるかというと、西船ボウルは18レーンなので仮に予備なしの18チームで総当たり2回戦をやることがあったら、対応できるようにしてあります。

後で増やすのは大変なので最初に作っておくと急にセクレタリーから言われても対応できるからね。

今までここまで使ったことはないですけどね。

この表はあくまでも個人専用の成績表なので当然51行目に2人目のメンバーの個人専用成績表があります。

そして、1751行目に36人目の個人専用成績表となってます。

41行目から50行目は使っていません。

そうすると、全て50の倍数で36人目までになるので2人目の1週目のスコアーは54行目、3人目は104行目となり覚えやすく、数式を探す場合や修正する時に便利ですよね。

では検索条件ですが、下図のように(見切れてますが、左からC列・D列・E列・F列・G列)個人専用成績表と同じ回数入力(G3)の下に数字(G4)。この部分が検索条件(G3:G4)になります。

G4の数字を変えるだけで、成績一覧表が自動的に検索してくれて表示されるようになります。

表示したいシートのセルに数式で書くと=DGET(成績入力!A3:AB40,成績入力!O3,MENU!G3:G4)ですね。

ただしこのままでは、使い物になりません。

成績一覧表は成績順に並び替えて印刷してお客様に渡すので、並び替えをしても数字が変わらないようにする(絶対参照)の処理が必要です。この処理をしておけば順位が入れ替わっても(行が変わっても)大丈夫です。

リーグ成績表

NEMUシート(エクセルVBAを使用しています)

数式は=DGET(成績入力!$A$3:$AB$40,成績入力!$O$3,MENU!$G$3:$G$4)になります。

「$」マークをセルのアルファベット前後に入れるだけです。入れ方もファンクションキー「F4」を押せば入りますよ。

成績表

成績表シート抜粋です

この数式を成績表全てに入力するのが大変なのですが、機械的に根気よくやるだけです。

データベースと検索条件は同じなので、フィールドを変えるだけで完成です。

G数のフィールドは(Q3)、AVEは(R3)・・・という具合です。

この表の数式が完成すると、MENUの回数入力を1ならば、1週目の成績表が表示、2ならば2週目となります。絶対参照を忘れると、並び替えをすると選手名とスコアーが狂っちゃいますよ!

DGET関数をマスターするとシート枚数を減らすことができるのでシートを探す時間が短縮されて、エクセルファイルも軽くなるので読み書きが遅くならず、作業時間短縮で効率よくなりますね。
スポンサーリンク


シート地獄からは脱出できたけど、ひとつ解決すると次の問題がやってくる。

ハイゲームやハイシリーズを見つけるの面倒だなあ。よーく見ないと間違っちゃう。

F支配人
これが次の課題かな?

エクセルVBAを知らなくても大丈夫

かなりアナログなのですが、成績表の印刷範囲外にHGやHSの表を別に作っています。

F支配人
名前欄には実際は参加選手名が表示されていますが、個人名はまずいので消しています

エクセルVBAがまだわからなかった時は、この表を見ながら成績表に入力していました。

これができるだけでも、いちいち成績表の中からHGのスコアーを探して該当する選手名を記載する作業がいならなくなるので、だいぶ時間の節約になりました。

作業の流れはとてもアナログです。

  1. 選手登録番号を順番に割り振っています
  2. 登録している選手名をイコール(=)で結んでいます
  3. 性別が男性ならば「1」女性ならば「2」成績表から除外するプロや従業員は「3」です
  4. 男性HG表には、男性ならばDGET関数を使ってHGスコアーを表示
  5. 男性以外ならば、スコアーあると困るので0にしています
  6. そうすれば、ハイスコアー順に並び替えれば、男性上位順になるので探しやすいです
F支配人
この作業を女性HGやHSでも繰り返せば完成です

ただし、HGやHS以外にアベレージがあったり、チーム戦だったりすると、同じ作業を必要項目ごとに繰り返すことになるので結構めんどくさい作業になってきました。

これができるようになった時はすごく楽になった気がしたのですが・・・

F支配人
ここで登場エクセルVBA

先ほど書いた通り、この面倒になってきた繰り返し作業がエクセルVBAを使うとボタンを押しただけで、なんと1秒で完了してしまいます。

エクセルVBAのユーザーフォームに配置したコマンドボタンにこのようなコードを書くだけです。

Range(“AK4:AN23”).Sort Key1:=Range(“AN3”), Order1:=xlDescending

これで先ほどの図の男性HGが大きい順に自動的に並び変わります。

このコードを女性HGなど必要分並べて書き足せば、ボタンを押しただけで一瞬で全ての項目で並び変わります

この並び替えをするエクセルVBAのSortメゾットを使えるようになると、当然ながら、個人成績表やチーム成績表の順位順の並び替えもボタンひとつで一瞬にできるの作業効率大幅アップしました。

DGET関数でシートの数が減ったのはいいけど、その前にその個人専用の成績表?の作り方が分からないよ!
F支配人
どの辺が分からない?
そだね~!
ハンデキャップかな!
F支配人
それはハンデキャップの計算をするエクセルの数式や関数の事だね

それじゃあ、ハンデキャップの事を書いておいたからこのページを見るとわかると思うよ!

標準的なHDCPから男女別など7種類も、西船ボウルでは使っているからね。

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

2018.06.02

スポンサーリンク


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

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