前回、企業が入社テストとして出題した「Excel問題」のその7をやってみました。
今回はいよいよラストとなります。
普段あまり使わないかもしれない関数を組み合わせて、前回と同様少しシステマチックな使い方を操作してみたいと思います。
それでは一緒にやっていきましょう。
スポンサーリンク
スポンサーリンク
問題8を確認
今回は、毎日入力されるデータから、常に前日までのデータの合計と最大値を求めていきます。
「Excel問題」その1に書きましたが、この問題は2018年に連載記事として公開したものを今回リメイクして再記事化しています。
ダウンロードしてもらったエクセルファイルは当時の問題のままなので、一覧の日付が古くなっていますね。
「セルD4」には、「TODAY関数」が設定されているので、本日の日付が表示されます。
それに合わせて、一覧の日付もそれに合わせた形に修正したいと思います。
ちなみにこれはオートフィルのコピーだけでできます。
一覧の日付が、現在の日付の直近61日分(約2か月分)となればOKです。
さて、今回の問題は、一覧表が「日付」と「値」の2列で一見シンプルに見えます。
この一覧から取得したいのは、「前日の値」、「前日までの合計値」、「前日までの最大値」の3つです。
このように毎日データを入力していく業務は、結構あるでしょうね。
日々更新されていくデータから取得したい3つのデータは、常に流動的になるわけです。
一応この問題は、一覧の最下行を「65行目」と決め打ちしています。
しかし実際の業務であれば、2か月と言わず何年もデータを蓄積していく可能性が高く、延々と行数が増えていくかもしれません。
そのような増えていくデータに対して、常に最新のデータとそれまでの集計がいつでもできる状態を関数だけで作ってみる、というわけです。
問題は「前日の値」、「前日までの合計値」、「前日までの最大値」の順番で答えていくようになっています。
ただし今回は、以下の流れで3つの値の解答を求めていきたいと思います。
この問題は日々入力していくので、本来は「当日」と「前日」というのは毎日変わります。
しかし「OFFSET関数」の説明のために、この説明の時点では必ず当日をセルD4(2026/6/10)、前日をセルF10(2026/6/9)とします。
特定のセルの範囲を取得する「OFFSET関数」の動きを確認し、同時にスピルについても少し触れてみます。
「SUM関数」の引数に「OFFSET関数」を指定して、開始日(セルF5)から前日(セルF10)までの値の合計ができたかを確認します。
「MAX関数」の引数に「OFFSET関数」を指定して開始日(セルF5)から前日(セルF10)までの値の最大値が取得できたかを確認します。
ここで、セルF10(2026/6/9)を「前日」にする固定を取りやめます。
「MATCH関数」を使って、セルF4の「本日の日付」から一覧表の前日を判断する方法を確認します。
今日の日付が変わっても、常に前日の値が取得できているかどうかを確認します。
先ほどはセルF10(2026/6/9)を前日としていたので、ここで「SUM関数」もしくは「MAX関数」の引数に「OFFSET関数」と「MATCH関数」の両方を指定して、流動的に前日までの合計値と最大値が取得できているかを確認します。
日々データが入力されていくと、前日までのセル範囲がだんだんと大きくなっていきます。
最初から、あらかじめセル範囲の一番下を10000くらいまで見ておくのもいいでしょう。
しかし、「TRIMRANGE関数」が使えるバージョンのエクセルであれば、簡単に入力済みのセル範囲の最下行を取得できます。
データが増えた場合でも当日日付の行を取得する方法を見ていきます。
最後に、文字列で「セル番地」を指定してあげると、そのセルの内容を表示してくれる「INDIRECT関数」を使って、最終の関数を作成してみます。
これで日々入力されるデータでも、データ範囲の最下行を取得して、「前日の値」や「前日までの合計値」、「前日までの最大値」を取得できます。
それでは順番に見ていきましょう。
当日と前日を決め打ちする
まず問題の「セルD4」がTODAY関数だと、常に表の最下行の方を見なくてはなりませんので、この「セルD4」の日付を表の上の方に入力している日付付近に直します。
皆さんも一覧の日付に合わせて任意に変更してもらってもいいです(もちろんTODAY関数のままでも結構です)。
とりあえず説明用としては、「セルD4」を「2026/6/10」にしてみました。
今日の日付を「2026/6/10」として、関数を設定していきます。
今日が「2026/6/10」だとすると、それ以前に入力されたデータは、6日分あります。
今日の時点では、この6日間の「値」の合計を取得したいというわけですね。
つまり「SUM関数」の引数に「G5:G10」を設定するため、この6行を「OFFSET関数」で取得するのです。
「OFFSET関数」の動きを確認する
ここで使う関数の1つ目は「OFFSET関数」となります。
この関数は、特定のセルを基準にセルの取得範囲を指定できる関数となります。
まずは「OFFSET関数」の引数の設定や結果の返し方など、動作確認をしてみたいと思います。
「セルH5」に「OFFSET関数」をセットするため、このセルを選択し関数ボタン(fx)をクリックします。
「OFFSET関数」の最初の引数である「参照」は、これからセル範囲を取得するために、基準となるセルを指定します。
表で使う場合は、表の左上のセルを指定しておくと、後々見返した時に、基準となるセルからどれくらいの行数と列数を指定しているかが分かりやすくなります。
タイトルがある場合はタイトル行の一番左のセル、タイトルがない場合は先頭行の一番左のセルが理想的です。
表内のセルを基準にしても間違いではありませんが、行列の挿入や削除などを行うと参照範囲がずれるなど管理が面倒になる恐れがあります。
今回の表で言えば表の一番左上のセルは「セルF4」の「日付」というタイトルになります。
そのため「OFFSET関数」の最初の引数である「参照」には、基準となるセルとして「セルF4」を設定します。
合計したいセル範囲として「G5:G10」を選択したいのでしたね。
次の引数である「行数」と「列数」には、基準となるセル(セルF4)から選択したいセル範囲の最初のセルがどこにあるかを指定します。
つまり基準となる「セルF4」から見ると、この「セルG5」というのは、1行下がり、1列右に行った場所にありますね。
その場合、「行数」には「1」を列数にも「1」を指定します。
次の引数は、「高さ」と「幅」になります。
これらは、選択範囲がどれくらいあるかを指定します。
今回は、「G5:G10」を範囲選択したいので、「高さ」は5行目から10行目までの「6」となります。
一方「幅」はG列だけなので、「1」を指定します。
引数がすべて指定できたら「OK」をクリックします。
「OFFSET関数」は結果としてセル参照を返します。
今回は結果を表示するセルとして「セルH5」に「G5:G10」のセル範囲を返すように「OFFSET関数」の引数を設定しました。
しかし、1つのセルに返せる「セル参照」は1つだけとなるので、今回の結果は「779」となりました。
これはセル範囲の最初のセルである「G5」の値となります。
この結果の返し方は、こういうものだと思ってください。
というのも「OFFSET関数」は、本来「SUM関数」などの引数として使われる関数であり、「OFFSET関数」が返すセル参照そのものを必要とされる場面はあまりないからです。
ただ、上の画面を見ると「セルH6からセルH10」まで青い枠で囲まれて、「セルG6からセルG10」までの値が表示されています。
これが「スピル」となります。
関数は1つのセルに1つの結果しか返せず、「セルH5」だけを表示しているものの、本来は「セルH6からセルH10」までのセル範囲も返しているよ、と自動的に値を表示してくれるものになります。
この「スピル」は、古いバージョンのエクセルだと搭載されていない機能となります。
そして、前回この入社問題の記事を書いた2018年頃にはなかった機能となるのです。
このスピル機能が搭載されているエクセルでは、周辺のセルに数式や関数などの結果に関連するデータを「オートフィル」などしなくてもいいように、自動的にデータが表示されます。
スピルによって表示されたセルかどうかを判断するには以下の2つを同時に満たします。
スピルの範囲内から別のセルを選択すると、青い枠は消えます。
もし、関数の結果にスピル機能で勝手に他のセルのデータが表示されるのが邪魔であれば、関数名の前に「@」をつけてください。
これで「OFFSET関数」の動作は確認できました。
確認用に「セルH5」にセットした「OFFSET関数」は削除しておいてください(スピルがある場合は、「セルH5」を削除するとスピルのデータも一緒に削除されます)。
それでは、実際に前日までの合計値を表示してみましょう。
前日までの合計値を取得
一番最初のセルが「セルG5」、前日(2026/6/9)のセルが「セルG10」で、この範囲のセルを「OFFSET関数」で取得できるところまで前章で確認してきました。
それでは、実際に「SUM関数」の引数に「OFFSET関数」を指定してみたいと思います。
「セルD9」に以下の関数をセットしてください。
「OFFSET関数」は先ほど確認した時と同じです。
これでセル範囲「G5:G10」が取得できていましたね。
この「OFFSET関数」をそのまま「SUM関数」の引数とします。
つまり、「SUM関数」の構文は以下のようになります。
SUM(G5:G10)
よく見る「SUM関数」の使い方と同じですね。
試しに「セルH5」に、直接引数「G5:G10」を指定した「SUM関数」をセットして、結果を確認してみました。
当然ですけど同じ結果となります。
これで、前日として決め打ちした「2026/6/9」(セルG10)までの合計を取得できるようになりました。
では同じような形で、前日として決め打ちした「2026/6/9」(セルG10)までの最大値を求めてみましょう。
前日までの最大値を取得
では同じように、最大値を求めてみましょう。
これは簡単ですね。
先ほどの「SUM関数」の部分を「MAX関数」に変えるだけです。
「セルD11」に関数をセットしてみます。
「MAX関数」の引数に「OFFSET関数」を設定します。
セル範囲は、先ほどと同じ「G5:G10」になり、この範囲の最大値は「784」と表示されました。
さて、ここまで前日を決め打ちしてきました。
今度は常に前日の日付を判断する関数を作成してみましょう。
スポンサーリンク
スポンサーリンク
前日の日付を判断する
ここまでは、前日は必ず「セルF10」(2026/6/9)として計算してきました。
ここからは、日々入力するデータの確認時に、必ず前日のセルを取得するように関数を作成していきます。
現在、「当日日付」として「セルD4」には2026/6/10が設定されています。
その状態で結構ですので、前日が表の何行目になるのかを「MATCH関数」を使って判断してみましょう。
確認するために「セルH5」に「MATCH関数」をセットしてみます。
「MATCH関数」の最初の引数は、検査値になります。
「セルD4」から1引くと、「当日日付のセルD4(2026/06/10)から1日前」という意味になります。
次の引数、「検査範囲」はここでは表の範囲なので「F5:F65」を指定します。
最後の引数は、検査方法なのでここでは「0」を指定して完全一致の検索にしています。
結果は「6」となりました。
なぜ結果として「6」を返すのかは、以下のようになります。
これで前日の日付を探す方法が見つかりました。
では、「セルD4」の当日日付を「2026/6/17」に書き換えてみます。
「MATCH関数」は、「2026/6/17」の一日前である「2026/6/16」の場所が表の範囲の13行目にあると返してくれました。
ではこれを踏まえて、前日の値を取得してみましょう。
スポンサーリンク
スポンサーリンク
前日の値を取得する
前日の日付が、表の範囲のどの行に位置しているかが分かれば、後はその同じ行にある「値」を取得するだけです。
ここで「MATCH関数」と「OFFSET関数」を両方使います。
「セルD7」をクリックして、「前日の値」を表示してみたいと思います。
「OFFSET関数」は選択したセルを参照できる関数でした。
「行数」の引数に「MATCH関数」をセットすれば、当日日付の1日前、つまり前日のセルへと参照するセルを前日日付に設定できます。
その後の引数は、1列右へ動き(列数：1)、選択するセル範囲は1つのセルだけ(高さ：1、幅：1)なので、「OFFSET関数」は以下のようになります。
OFFSET(F4,MATCH(D4-1,F5:F65,0),1,1,1)
試しに、当日日付を「表の範囲の日付」に変えて、動作を色々確認してみてください。
もう一度前日までを判断しながら合計値・最大値を取得する
さて、表の範囲から前日の日付の「値」を取得できるようになりました。
ではこれまでの内容を思い出しながら、前日までのデータを合計し、続けて最大値も求めてみましょう。
今回は、「OFFSET関数」の引数に指定する「MATCH関数」の位置に要注意です。
今回は少し見やすいように「セルD4」の当日日付を「2026/6/14」に変更します。
まずは「セルD9」に前日までの合計値を取得してみましょう。
関数が3つあります。
「SUM関数」の引数に「OFFSET関数」があり、その「OFFSET関数」の引数には「MATCH関数」が含まれていますね。
ただこれまでやってきた内容を踏まえると、それほど難しくはありません。
結果として「SUM関数」の引数に「2026/6/14」の前日、つまり「2026/6/13」までのセル範囲を指定しているだけとなります。
SUM(G5:G14)
では同じように最大値も求めてみましょう。
「SUM」のところが「MAX」に変わっただけです。
これも「SUM関数」の時と同じで「MATCH関数」で前日の行数を検索し、「OFFSET関数」でセル範囲を指定し、そのセル範囲を「MAX関数」の引数にしています。
つまり、以下に書いた式と同じと言えます。
MAX(G5:G10)
前日の「2026/6/13」までで、「値」の最大値は6/13の788になります。
スポンサーリンク
スポンサーリンク
増えるデータ | セル範囲の最下行を取得するには
ここまでで、以下の3つができるようになりました。
- 当日日付の1日前の「値」の取得
- 当日日付の1日前までの合計値の取得
- 当日日付の1日前までの最大値の取得
合計や最大値を求める関数内に、「OFFSET関数」や「MATCH関数」を使って計算してきましたね。
さて、ここでは日々増え続けるデータによって、毎日参照するセル範囲の最下行が1行ずつ変わっていく点について考えてみたいと思います。
セル範囲の最下行
「日付」と「値」の2列だけですが、日々新たなデータが入力されていくので、行数は毎日1行ずつ増えていきます。
このようにデータは増え続けるので、データ範囲の最下行は常に前日行の「プラス1」となります。
この最下行を取得する方法について、今回は2つだけ挙げておきます。
- セル範囲を下の方に決め打ちする
- 「Trim References」で最下行を参照する
2番目の「Trim References」を使った方法は比較的新しいバージョンのエクセルが必要となります。
関数の一覧に「TRIMRANGE関数」があれば利用できます。
古いエクセルを使っていると、「TRIMRANGE関数」が関数の一覧にありません。
その場合は、この2番目の方法は利用できません。
この後は、「セルF15」(2026/6/14)までデータが入力されている状態で説明していきます。
セル範囲を下の方に決め打ちする
一番楽なのは、セル範囲をずっと下の方で固定して決め打ちする方法です。
例えば上のように、「MATCH関数」で参照するセル範囲の最下行をあらかじめ「10000」と決めておけば、1日1行であれば27年は持ちます。
ただ関数も常に10000行まで検索するので、あまりいい方法とは言えないかもしれませんね。
「ROWS関数」と「Trim References」で最下行を参照する
「TRIMRANGE関数」を使えるエクセルであれば、「Trim References」を使って最下行を取得できます。
行を求める関数の「ROWS関数」の引数に「TRIMRANGE関数」を使うイメージで、その「TRIMRANGE関数」の部分を「Trim References」で簡潔に書いてみます。
「セルH5」に設定して、実際に動作を確認してみましょう。
「ROWS関数」は引数に指定された範囲の行を取得する関数です。
そしてその引数には、以下のように指定しています。
F:.F
仮に「F:F」と指定すると、「ROWS関数」はF列全体を参照して、そのF列に何行あるかを返します。
結果は、「1048576」となります。
エクセルのワークシートの最下行が「1048576行目」だからですね。
そこで先ほどのように「F:.F」と範囲の終わりのFに「.(ピリオド)」を付けると、下の空白行を除外してくれます。
つまり、「日付」が20行目まで入力されていて、それ以降が空であれば「ROWS関数」は20を返してくれるというわけです。
さあ、それでは最後になります。
この取得した最下行の「行数」と「F列」を文字列情報にします。
それを「INDIRECT関数」を使って、セル番地として認識させ、そのセル内のデータを取得するようにします。
「INDIRECT関数」を使って最終の関数を作成
それでは、先ほど取得した最下行の行数と「F」という列番号を文字列情報として「INDIRECT関数」の引数に指定します。
現在「セルF15」が最下行です。
最下行を取得するのは、「ROWS(F:.F)」でできるのが分かりました。
しかし、行だけでなく「F15」というセル番地を結果として返してほしいのです。
そしてそのセル番地の内容を取得したいのですが、現状は「15」という行数だけが分かっています。
そこで使いたいのが「INDIRECT関数」になります。
最初に「セルH15」に「INDIRECT関数」を使って、F列の最下行のデータを取得してみます。
「INDIRECT関数」の使い方のミソは、引数を文字列にする点です。
ここでは「ROWS関数」の結果である「15」と「F」を&でつなげて、「F15」という文字列作っているわけです。
そして、「INDIRECT関数」は文字列で受け取った情報を「セル番地」として認識し、そのデータを表示してくれるのです。
上の例では、セルH15を「日付データ」にして確認してください。
では、これまで見てきた関数を使って、「前日の値」、「前日までの合計値及び最大値」を求めてみます。
当日日付(セルD4)は、最初「2026/6/14」としています。
表の範囲は、先頭が2026/6/4(セルF5)から始まり、最後が2026/6/14(セルF15)までとします。
後ほど、次の行に新しいデータを追加していきます。
前日の値(最終形)
「セルD7」に先ほど設定した関数が入っているので、ここは一旦消しましょう。
再度関数を指定していきます。
だいぶ複雑になっています(笑)
順番に分解してみてみましょう。
ROWS関数
ROWS(F:.F)
まずは「ROWS関数」です。
ROWS関数の引数にF列を指定しています。
その内「TrimReferences」によって、下から見た空白行をすべて除外しています。
つまり最初に空白でないセルの「セルF15」までを行数として数えてくれますので、この関数は「15」を返します。
以降、関数の結果が分かった部分にはその結果の値を入れていきます。
INDIRECT関数
INDIRECT("F15")
ROWS関数の結果を当てはめると、「INDIRECT関数」は上のようになります。
引数は「F15」という文字列になります。
これで「セルF15」を参照しているのと同じ意味になり、このセルの値を取得できます。
MATCH関数
MATCH(D4-1,F5:F15,0)
MATCH関数は、当日日付の前日をF列のデータ範囲から検索します。
前日は「2026/6/13」なので、「セルF14」になりますね。
MATCH関数は、その探したいセルが、参照するデータ範囲の上から何行目にあるかを返してくれます。
セルF5から数えて10行目になるので、結果は「10」となります。
OFFSET関数
OFFSET(F4,10,1,1,1)
最後の「OFFSET関数」は上のようになります。
結果を埋めていくと、シンプルに見えますね。
「セルF4」を基準に、下へ10行、右へ1列の場所を起点に1行1列のセルを参照します。
つまり「セルG14」を参照するわけです。
「前日(2026/6/13)の788」が「セルD7」に表示されています。
では、日付が変わって「セルD4」に「2026/6/15」と入れてみましょう。
「セルD7」の「前日の値」が、「2026/6/14の値」(セルG15)になりましたね。
今度は新規データがない状態で、日付をさらに次の日にしてみましょう。
参照しているデータ範囲に、「2026/6/16」の前日がないので、「セルD7」にはエラーが表示されます。
前日までの合計値(最終形)
では、当日日付を「2026/6/14」に戻して、今度は「前日までの合計値」を表示してみます。
「セルD9」に入っている数式を一旦消しましょう。
「前日の値」と違うのは、「OFFSET関数」の引数である「MATCH関数」の位置と「SUM関数」が一番外側にあるだけとなります。
「ROWS」、「INDIRECT」、「MATCH」の各関数は「前日の値」と同じ指定なので省略します。
OFFSET関数
OFFSET(F4,1,1,10,1)
「前日の値」は1つのセルだけを参照すれば良かったので、「OFFSET(F4,10,1,1,1)」でした。
今回は、SUM関数の引数になるので、「F4,1,1,10,1」となります。
基準セルの「セルF4」から下へ1行、右へ1列の「セルG5」を起点とし、10行1列が参照範囲となります。
つまり「セルG5：セルG14」の10行ですね。
SUM関数
SUM(G5:G14)
結果、「SUM関数」の引数は上のようになります。
分解していくと、とてもシンプルです。
同じように、「当日日付」を次の日に変更してみてください。
合計する範囲が1行増えて「SUM(G5:G15)」になりました。
結果も変わりましたね。
最後に「最大値」です。
スポンサーリンク
スポンサーリンク
前日までの最大値(最終形)
最大値はもうお分かりですね。
「SUM」を「MAX」に変えるだけです。
当日日付を「2026/6/14」に戻して、「セルD11」の数式を一旦消しましょう。
MAX関数
MAX(G5:G14)
これだけです。
では確認してみましょう。
2026/6/13までの最大値が取得できました。
では同じように日付を変更してみましょう。
MAXの部分を変えれば、平均値や最小値も出せますね。
「OFFSET関数」の中がだいぶ複雑になりましたが、このような自動集計も関数だけで、できてしまうというわけです。
問題8まとめ
だいぶ長くなりました。
この問題は2018年頃に出ていたものなので、当サイトでもその当時の記事では一からすべてを説明していました。
今ならAIに聞けば、どの関数をどのように使えばよいかを導き出してくれるかもしれませんけどね。
ちなみに新しいデータを追加する際、前日から間に空行を作ってしまった後に新たなデータを追加していっても、きちんと計算してくれます。
試してみてください。
後は、本当にこういう形で管理するデータ一覧があるのであれば、当日日付は「TODAY関数」で今日の日付を取得してください。
さて今回も全8回で連載してきました。
知らない関数はまだまだ奥が深いですし、エクセルのバージョンが変わるとこれまでの関数がバージョンアップしたり、完全に新しい関数が作られたりしますね。
エクセルの関数は本当に便利なので、今後当サイトオリジナルの問題でも作って連載できたらいいな、と思っています。