スポンサーリンク

日付を入力するとデータが差し替わる表を作るにはDGET関数

エクセル数式

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

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

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

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

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

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

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

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

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

作業効率化には必須のDGET関数

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

日付を入力するとデータが差し替わる表があったら便利だと思いませんか?

この表があれば、表のひな形はたったひとつでデータ元は別に用意すればいいのですから、エクセルデータファイル自体を作るのも早くなるし実際使うのも変更点は日付だけなので、業務作業効率化できます。

そこで「エクセルDGET関数」を使えば作れます!

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

意味分かります?何かの呪文?

最初の頃は、エクセルでそもそも「関数」って聞いただけで、頭を抱えて拒否反応していましたが、何度かチャレンジしてみるととても便利なエクセルDGET関数です。

ここでは、ボウリングリーグ成績表を作った時の実例でDGET関数の使い方を書いていきます。

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

ここでは、日付を変える=ボウリングリーグ戦(総当たり戦)の開催回数を変えると置き換えます。

要するに、日付の代わりに1回戦とか2回戦・・・10回戦とかですね。

ボウリングリーグ成績表一覧

上の図はボウリングリーグ戦参加者の成績表一覧(抜粋)です。

回戦数(日付)を変更すると、この表の順位と名前以外が全てデータ元から探し出してそれぞれの項目(たとえば1G目のスコアー)に表示するという事で、順位と名前以外のセルには全てDGET関数が入っています。

DGET関数は「データベースの列から指定された条件を満たす1つのレコードを抽出します」と書いてあり、使ってみようとすると、=DGET(データベース.フィールド.検索条件)ってなります。

はっきり言ってわかりづらいので、言葉をボウリング成績表に合わせてみるとこうなります。

DGET関数用語置き換えた言葉
データベースデータ元のセルの範囲(個人専用成績表)
フィールドデータ元のセルの範囲の列(1G目の列)
検索条件回数入力と回戦数
波乗りアヒル
波乗りアヒル

画像は抜粋の為、18回戦分として説明します。

先ほどの図(参加者成績一覧表)で神田さんの1ゲーム目のスコアー(1G)にDGET関数を入れる例を書きます。

下の図で置き換えた実際のセル番号を書くと、

データベースは回数入力からT.Pまでの列で18回戦までの53行目から71行目までなので【CA53:CU71】となります。

フィールドは1ゲーム目のスコアー1Gなので【CD53】になります。

最後に検索条件ですが、ユーザーフォームを使って入力をする関係上、別の位置にあります。

回数入力

このフィールドが原因でエラーになる事が多いので気をつけてくださいね。

ここでは、回数が書いてあるセル【Y3】の数字が1ならば1回戦目、2ならば、2回戦目という風に数字が変わる事により、個人専用成績表の回数入力の列【CA53】と同じ数字を探すようになっています。

その為に、Y2】の値と【CA53】の値が一緒でないとエラーになります。

フィールドのセルは【Y2:Y3】となります。

ここまでのセル番号をまとめると、
DGET(CA53:CU71,CD53,Y2:Y3)
になります。

実際はデータ元のシートからデータを集めているので、シート名をプラスしなくてはいけません。

ここではシート名を【HOME】としています。

さらに、順位を並び替えてから印刷するので、絶対番地にします。

さらに、お休みなどで投球しない時があると、空欄になって【#VALUE!】エラーが出るのを防ぐために空欄にするIFERRORを加えます。

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

完成形はこれです。

=IFERROR(DGET(HOME!$CA$53:$CU$71,HOME!$CD$53,HOME!$Y$2:$Y$3),””)

これで神田さんの1G目のスコアーが回戦数(日付)を変えることで表示が切り替わります。

大変ですが、この作業を2G目、3G目・・・と東京さんから17人目の新松戸さんまで繰り返せば、回戦数(日付)を変えるだけで表のデータが切り替わるようになります。

IFERRORに関しては、下記ページに記載しているのでここでは省略します。

エクセル計算式が#VALUE!エラーのセルを空欄にする方法
ボウリングリーグ戦の成績表をエクセルで作っていて、参加者成績一覧表で「#VALUE!」になる事があります。このエラー表示が出た時だけ空欄にしてしまう事ができれば、参加者へ配布できますね。簡単にできるのでエラー表示防止にIFERROR関数の使い方
スポンサーリンク

なぜDGET関数を使う?

DGET関数を使いこなせていない頃は、回戦数(日付)を変更して表のデータが変わることができないので、回戦数(日付)1つにシートが1枚。

同じ表がいくつもあり、最後のシートが全シートの集計だったり。

20回戦のリーグ戦ならばシートは21シート、1か月の売上ならばシートは32シートで作りました。

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

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

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

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

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

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

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

DGET無しの複数シート

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

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

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

こんな非効率な事をまだやりますか?DGET関数とVBAで作業効率化しませんか?

DGET関数とExcel-VBAを組み合わせてエクセルを作れば、もっと作業効率がアップして仕事が早く終わります。

エクセル活用術はエクセル関数とエクセルVBAの組み合わせから
ボウリングのリーグ戦での成績表をエクセルで作るのは大変ですが、エクセル関数とエクセルVBAを組み合わせて作れば、作業が自動化されて作業効率が大幅に上がりました。ここでは、活用している事例を書き出したら目次になっちゃいました。

コメント