前回に引き続き、いつの間にかシリーズ化しているExcel入社テストの実践編第8回目です。
前回までは以下からどうぞ。
目次
OFFSETとMATCHでシステマチックに処理する
このあたりまでくると、
「作業を自動化する処理を自ら作り出していく」
そんな職場環境に身を置いていることを想像しなければなりません。
この問題のように、毎日データを積み上げていくというのはどの業務にもあるでしょう。
Excelの1人作業で業務を効率化する場合もあれば、
ACCESS単体、もしくはデータベースサーバと連携させて、複数人で運用していくシステムを構築する場合もあるかもしれません。
Excelの関数としては、今回のように、
「集計する参照範囲が変わる」場合によく使う関数2つは、自動化処理を行う上での定番となっています。
「前日の値」を求める「VLOOKUP」関数は、第5回目で見ましたので割愛します。
目的のデータを指定して、表からその目的のデータがある行の列並びにあるデータを取得する関数でした。
さて、今回結果を求めたい最後の処理は何かというと
「合計」と「最大値」です。
しかも、日々入力するデータの「前日の値」までを取得したいので、セルの参照範囲も日々変化します。
つまり、「SUM」関数と「MAX」関数の中の引数の指定を工夫する必要がある、ということになります。
OFFSET関数
「OFFSET」関数は、特定のセルを基準に自分でセルの取得範囲を指定することができる関数です。
「OFFSET」関数はどういう動きなのか、下の画像を見るととても簡単です。
この画像では、特定のセルから5行分のセル範囲を指定しています。
これがつまり「OFFSET」関数なのです。
それでは、実際に「前日までの合計値」に関数を設定してみましょう。
まず、断っておくと
・見づらくなるので、セルの絶対参照をはずす
・2018/2/15から入力を始めて、当日が2018/2/20とする
この2点です。
「SUM」関数は見なくていいです(笑)
その次に出てくる「OFFSET」関数は、引数が少し多く、3番目の引数に「MATCH」関数が入っていますね。
とりあえず、「MATCH」関数は結果として5を返しますので、ここを「5」に置き換えて説明します。
【OFFSET(F4,1,1,5,1)】ということになります。
これは、「セルF4」を起点に1つ下、1つ右、つまり「セルG5」を指します。
そして「セルG5」から5行分、そしてG列1行分の範囲、つまり
「セルG5」から「セルG9」までをセル範囲として指定するという意味になるのです。
つらつらと関数がいっぱいあって複雑に見えますが、
結局のところ、「セルG5」から「セルG9」をSUM関数で足し算する、ということなんですね。
MATCH関数
それでは、「OFFSET」関数の引数に指定していた「MATCH」関数を見ていきましょう。
【MATCH(D4-1,F5:F65,0)】となっています。
日付が入っている「セルD4」から1引くことで前日の日付を取得できます。
つまり、「2018/4/19」を「セルF5」から「セルF65」の範囲で完全に一致するものを探す、と言う意味になります。
この「MATCH」関数で気を付けたい点は、
「結果として何を返してくれるのか」
ということです。
第2引数で検索する範囲を「F5:F65」と指定しました。
この「セルF5」を開始地点、つまり「1」として検索するデータが見つかった番号を数値として返してくれるのです。
今回の場合、検索したいデータは「セルF9」でした。
ということは、結果として「5」を返してくれる、ということになります。
OFFSET関数の移動に0を指定する
さて、元サイトの回答例を見ると、「OFFSET」関数の始まりのセルを「F4」として下と右に1個ずつ動かして起点のセルを「セルG5」と指定しています。
では、最初から「セルG5」を起点としたら第2引数と第3引数をどのように指定しますか？
「セルG5」から下にも右にも動かないので、「0」でいいわけですね。
これは、作り方次第で特に「正解はコレ」ということはありません。
「セルF4」や「セルF5」が表のタイトル行で不変なので、表のデータを修正することになっても、タイトル行を起点にしておけば安心と言う場合は回答例のように作るでしょうし、
起点を下の方のセルにすれば、「OFFSET」関数の第2引数や第3引数に「マイナス」を指定することもできるのです。
業務でどのように使うかは、使う環境次第ですので色々試してみるとよいでしょう。
元データが日々増えていく場合のセル参照方法
この問題の表は、表の最終行が65行目に入力した「2017/4/16」と決まっていて、
「MATCH」関数も参照範囲を65行目と決め打ちしています。
しかし、「セルD4」を見ても分かる通り、今日の日付が「2018/4/17」なので、明日(2018/4/18)には、66行目に入力されるであろう「4月17日」のデータは探せなくなります。
この場合、関数のみで作っていくのなら、あらかじめ表の範囲をもっとずーっと下に設定しておくしかないでしょう。
※関数だけで最終行を取得することもできますが、複雑になるので割愛します
向こう5年くらい毎日入力してもきちんと参照できるようには、次のように「MATCH」関数の引数を指定します。
表の最終行を「2000行」に見立てて、引数の範囲を指定し直しました。
当然、結果は変わりません。
これでしばらくは、この関数で毎日、前日までの各集計処理ができるようになりましたね。
INDIRECT関数も覚えておきたい
さて、最後に「OFFSET」関数の代わりに別の関数を使ってみたいと思います。
「OFFSET」関数は、参照範囲の指定をする引数の設定が多く、この関数の設定者や表を作成した人以外の人が見た時に、少し分かりづらくなっています。
そこで、「INDIRECT」関数と「MATCH」関数で代用します。
「前日までの合計値」は、最終的に「SUM(G5:G9)」のような形になればいいのでしたね。
「セルG9」の位置の変動を「OFFSET」関数で対応したのです。
ただ、日々新しいデータが増えていっても、始まりの「セルG5」は固定だし、「セルG9」の「G列」も変わることはないのです。
ということは、以下のように考えることができます。
SUM(G5:G【】)
【】の部分の変動に対応しながら、もう少し見やすくならないか、ということを考えます。
では、まず式を見てみましょう。
「SUM(G5:」までは、いつもの「SUM」関数みたいな感じで書いてありますね。
残りの「G9」にあたる部分を解説します。
「INDIRECT」関数は、引数に指定した「文字列」のセルの内容を参照します。
例えば、セル範囲に名前を付けたりする場合がありますが、この「セル範囲の名前」を指定することもできます。
そして、今回のように「G9」であれば
「INDIRECT(“G9”)」と指定することで、「セルG9」の内容を取得できるのです。
そして、「G」の列の指定は変わりませんので、
「INDIRECT(“G”&)」
と書きます。
後の「9」の行の部分が日々の新しいデータの入力により変動しますから、ここをMATCH関数で補うわけです。
ちなみに、「&」は文字列を連結する演算子で、よく使いますので覚えておくとよいでしょう。
さて、「MATCH」関数の引数の指定は、「OFFSET」関数の時から少し変えています。
「MATCH(D4-1,F1:F1000,0)」
そう、第二引数が「セルF1」から始まっています。
「MATCH」関数は、参照範囲の先頭を起点としてデータが見つかった番号を数値で返す関数でした。
ということは、表の範囲ではないですが「1行目」を起点にすれば、
見つかったデータの位置と行番号は、同じ番号ということになるのです。
そう、1行目を起点にして9行目で見つかるのですから、この「MATCH」関数では「9」という結果が返ります。
「MATCH」関数の結果は数値ですが、「”G”&MATCH(D4-1,F1:F1000,0)」と文字列で連結されると「9」という”文字”で処理してくれます。
いかがでしょうか。
セル番地がそのまま表示されたり、「INDIRECT」関数の引数に「”G”」と列番号がそのまま指定されているので、ほんの少しだけでも見やすくなった感じがしませんか(汗)
総括
このシリーズでも何回も申し上げてきましたが、
どれが正解とか、どの関数を使わなければいけないとか、そういった決まりはありません。
もちろん、自分の慣れている関数を使ってもいいわけですし、
知らない関数で「実はこういったことができるんじゃないか」と探索するのもいいでしょう。
今回は、テスト問題として出題されたものを見てきましたが、
実務で”ここぞ”と言う時にどれだけ最適な方法が思い浮かぶかです。
それは、もう経験しかありません。
少人数で、いかに効率よく情報を整理できるのか・・。
Excelの関数とは、皆さんが持っているツールの中の1選択肢でしかないのです。
とりあえず、このシリーズはここまでです。
何問か飛ばしていますので、そちらは皆さんで試してみてください。
それにしても、こういうのっておもしろいですね～。
このサイトでもなんかそういうのやろうかな～・・・。
よそ様が作り上げたテストを取り上げて、
”あ～だ”、”こ～だ”、その内、”こ～したら？”みたいな感じで色々と書き連ねてきましたが、
作成者の方が、万が一当サイトをご覧になっていたら、なにとぞご容赦頂きたいと思います。
