銀の弾丸

プログラミングに関して、いろいろ書き残していければと思っております。

セルの範囲に入力された最も右の値を得るには「MATCH」を使う

f:id:takamints:20160831214321p:plain
photo credit: Bailey holding a mug via photopin (license)

セルの範囲内で、最も右の入力された(空白でない)値を得る には、ワークシート関数 MATCH が使えるんですね(INDEXとMAXも使います)。


かれこれ20年以上エクセルを使い続けていますが、今になって新たなワークシート関数を知って感心するとは思ってもいませんでした。 長年VBAのマクロでやっていましたが計算式だとお手軽です。 そして、このワークシート関数が使えるのはエクセルだけではありません。Google Driveスプレッドシートでも使えます。 以降で実際の Google スプレッドシートを共有していますので、ご利用ください。

目次

関連記事はこちら↓になります:
takamints.hatenablog.jp

事の発端は進捗管理

先日、個人作業の進捗管理のシートを軽い気持ちで作っていたんですね。

縦方向に作業項目を並べて、横方向には当日から締め切りまでの日付が並ぶ。各項目の進捗状況を日毎に記録するものです。

期限内に全作業がきちんと終了するよう自己管理。日々の状況を把握して、対策打ったり、あきらめたり(笑)、ふさぎ込んだり(!?)ってな使い方です。

客観的マネージメントには数値管理が必要ですし、なにより残工数とか表示しちゃって、予測曲線プロットしたりと、もうデータフェチにはたまらない喜びなわけですよ。理解できないかもしれないけど。

一番右が欲しいのです

作業項目は、1人日(いちにんにち = 一人の人が一日でできる作業)程度に分割しておき、各項目行の日毎のセルに0.5=「今やってます」とか、1.0=「完了した」とか書いていく。

でも、完了したり、そのあと進捗に変化がない日には、同じ数値を入れたくない。手間だし、表としても見にくいし。

なので、各項目の最新の進捗率は、各行の中で、数値が入力されているセルのうち、一番右にあるセルの値を取り出したい

過去にはVBAのマクロでやってたんですよ。 でも入力時のトリガーで再計算とか結構遅くなりますし、別のシートへ展開しにくいんですよね。 データ管理用のシートに分けたりしてみましたが、行や列の挿入で簡単に破たんしてしまいます。 この辺はホント「エクセルあるある」だと思います。

なので、できれば計算式でやりたいが、そのやり方が分からなかったのです。

過去の自分にググレカス

で、この度ググってみたら以下のサイトが見つかった。

EXCELで、範囲指定した一番右の数値(セル)を返す関数ってありますか?...-Yahoo!知恵袋
detail.chiebukuro.yahoo.co.jp

というか他にもゴロゴロ出てきますやん。しかもそれぞれ、結構古くて2009年とか、7年前の情報です。

なんで今まで検索しなかった?と不思議でしたが、よくよく思い出してみると、ちょうどワタシが管理業務から逃れた離れることになった時期。 当時、このような管理面のことを、(一時的に)ちっとも考えなくなっていたんだな。ダメですね。

MATCHで~す

マッチ箱の脳(AI)―使える人工知能のお話
森川幸人 (2014-01-05)
売り上げランキング: 1,343
モノワイヤレス TWE-001L-DIC-WA TWE-Lite DIPシリーズ 端子付き(マッチ棒アンテナ)
モノワイヤレス(Mono Wireless)
売り上げランキング: 26,668

まあ、とにかく、そこで紹介されているのは、以下のような計算式でした。

セル範囲A1:E1に入力されてる最も右の値を得る:

= INDEX ( A1:E1, MATCH( MAX(A1:E1) + 1, A1:E1, 1 ))

INDEX関数

INDEX関数は、セル範囲からインデックスを指定して値を得る関数です。 第一引数がセルの範囲で、第二引数がインデックス。上の式ではA1:E1がセル範囲。MATCH( ... )がインデックスですね。

MATCH関数

で、INDEXの第二引数に指定されてるMATCH関数は、一次元のセル範囲(つまり1行か1列)から検索値にマッチする値が入力されたセルの位置(インデックス)を返す関数です。第一引数が検索値、第二引数がセルの範囲、第三引数が検索の型。

MAX関数

その名の通り、範囲内から最大値を得る関数ですね。

ちょっと待って、なんで最大値を探すのだ?

しかしちょっと待ってくださいよ、上の例では、検索値が、MAX(A1:E1)+1となっていて、セル範囲内の最大値+1となっているではないですか。これ検索してもダメなんじゃ?!と思ったのですが、どうやらミソは第三引数の検索の型。検索の型が1だと、検索値以下の最大値にヒットするのだとか。(だから本来+1する必要はないのだと思います)。しかも省略時のデフォルトが1なので省略してもよいみたい。

まだワカラン。一番右が最大だとは限らんのでは?

しかしまだ、納得できない。釈然としない。最大値の位置を知っても、それが一番右にあるとは限らないのだから、これでは目的の値は得られないのでは?と疑いましたが、なんとMATCH関数の説明に、セル範囲内のデータは昇順にソートされている前提と書かれているんですね。

エクセルのヘルプでは、たまにこういった軽く意味不明の記述がありまして、見るたびに「なんちゅう都合のいい仕様?!www」と嘲笑とか憤慨を覚えていました。

しかし、どうやら、この前提のおかげで、最後まで検索してくれるよう。こういう目的で使いたいから、こんな妙な前提を入れているのかなとか思ったりもしたがはて?。

サンプル(Googleスプレッドシート

以下に、Googleスプレッドで作ったサンプルを埋め込んでいますが計算式が読めないのでこちらからどうぞ。