読者です 読者をやめる 読者になる 読者になる

銀の弾丸

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

セルの範囲に入力された最も右の値を得るにはワークシート関数「MATCH」を使えば良いらしい

表計算

MATCHと一緒にINDEXとMAXも使います。

何の話かってエクセルです。セルの中の計算式。 エクセルのワークシート関数はたくさんあって、かなり強力。統計用の関数とか、行列演算さえできてしまう。 さらに、Google Driveスプレッドシートなど、他の表計算ソフトでも同じ様に使えるようなので、いろんな局面で役に立ちそう。

しかし、かれこれ20年以上使い続けているのに、今になって新たなワークシート関数を知って感心するとは思ってもいませんでした。

いくつになってもお勉強ですニャ

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

事の発端:進捗管理しなくちゃだっ!

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

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

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

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

一番右が欲しいのです

作業項目は、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スプレッドで作ったサンプルを埋め込んでいますが計算式が読めないのでこちらからどうぞ。