情報整理術の数々! PICK UP!

Excelのスキルを図る企業の入社テストを実際にやってみた【8】

前回に引き続き、いつの間にかシリーズ化している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選択肢でしかないのです。

とりあえず、このシリーズはここまでです。
何問か飛ばしていますので、そちらは皆さんで試してみてください。
それにしても、こういうのっておもしろいですね~。
このサイトでもなんかそういうのやろうかな~・・・。

よそ様が作り上げたテストを取り上げて、
”あ~だ”、”こ~だ”、その内、”こ~したら?”みたいな感じで色々と書き連ねてきましたが、
作成者の方が、万が一当サイトをご覧になっていたら、なにとぞご容赦頂きたいと思います。

関連記事

  1. 祝!ソフトバンクホークス優勝・・・で、やっぱり引っかかるあの制度の問題

  2. 2018年は生き方・働き方のセカンドソート

  3. ExcelやWordファイルに保存される個人情報を削除するには

  4. Excelのスキルを図る企業の入社テストを実際にやってみた【6】

  5. 【シリーズ:人より一歩先行くエクセル技を身につける】ピボットテーブル編[番外]

  6. 【シリーズ:人より一歩先行くエクセル技を身につける】ピボットテーブル編⑤

  7. 年末調整で求める年調年税額をエクセルの関数を交えて算出してみる!―その3―

  8. 【ちょっと休憩】運転中、画面を見続け中に税別100円未満で口にできる甘いもの5選

  9. 貧乏性

特集記事!

  1. iPadOS13で強化された「Slide Over」と「Split View」!…
  2. 【連載3】Lucidchartで作成した図面をGoogleの文書やシートに挿入す…
  3. 【連載2】Lucidchartで図面作成からプレゼンテーションまですべて完結!プ…
  4. 【連載1】Lucidchartの図をワードやエクセルに挿入する―マイクロソフトイ…
  5. 【連載序章】よい図面作成アプリが見つからない現場担当者必見!Lucidchart…
  6. 簡単設定と分かりやすい料金プランで国内・国外”どんなときも”Wi-Fiが繋がるな…
  7. マイクロソフト発StickyNotesで覚えておきたい設定・使い方8選
  8. 【OneNote for Windows10】StickyNotesが使用可能に…
  9. iPadで手書きのメモを取る!ApplePencilが使える無料のメモアプリ5選…
  10. 楽天銀行の法人ビジネス口座を持っているなら無料で使えるクラウドアプリを使ってみよ…

人気記事 PickUp!

ー文字だけで語る(あつもじ)ー

おすすめ記事

ピックアップ!

  1. 【OneNoteへGo】Office製品のOutlook連絡帳をOneNoteの…
  2. 【ちょっと休憩】検索したいわけじゃないけどつい弾きたくなる「弾いちゃお検索」とは…
  3. 【マイクロソフト To-Do】パソコンとスマホで”やること”をシンプルに管理する…
  4. 【Google ToDo リスト】Gmailからも起動OK!Googleの予定管…
  5. ワールドカップには嘘のような法則が存在する?!
  6. 【リメンバーザミルク】海外製アプリながら使いやすいインターフェースで人気のタスク…
  7. これは使ってみたい!3つのTo-doリストを比較してみた
  8. 絵心がないからデザインは苦手っ・・・そんなあなたは「Canva」があればもう大丈…
  9. 経県値―自分がどれだけの都道府県に足を踏み入れたのかが簡単に分かるスマホアプリ
  10. とうとう正式リリース!マイクロソフトの「Whiteboard」アプリの使い心地は…

↓OneDriveの便利な機能が満載!↓

icon-cogマイクロソフトのクラウドストレージである「OneDrive」の便利な使い方をご紹介しています。個人契約でしか利用できない機能もあります。

PAGE TOP