WBSに役立つExcel関数

インターネッツに「最左セルの値を取得」や「最右セルの列番号を取得」などは散逸していたのですが、「最左・最右セルの番号から日付を取得」というニッチ需要を満たすピンポイント回答がなかったので自分で書きました。

イメージはこんな感じ。このJ列とK列にセットする関数の紹介（というか自分が忘れないための記録）。JK列なのはただの偶然。


【前提】

• 3行目（M3:V3）に日付が入力されている（本当は右にもっとあるけど）。めんどくさいので $3:$3 で指定する。
  • セルの書式設定＞表示形式＞ユーザー定義で「d」とだけ指定して日付のみ表示している（「yyyy/m/d」と指定すればちゃんと年月日すべて表示される）。
• 各行、M〜V列目をデータ列として、なにかしら入力されているセル（上記イメージでは□）のうち、最も左のセルの日付をJ列で、最も右のセルの日付をK列で拾う。
  • 例えば、18行目は最左セルがO列にあるので、O列の日付である「5月7日」をJ列に表示する。
  • また、最右セルがS列にあるので、S列の日付である「5月11日」をK列に表示する。
• データ列のうち、なにかしら入力されているセルの個数をL列に表示する。
  • 例えば、18行目はO、P、Q、R、S列に□が入力されているので、L列に5を表示する。
  • もしO、S列のみに□が入力され、P、Q、R列が空欄の場合は、L列には2を表示する（J、K列の日付は変わらない）。
• 以下、18行目を例に説明する。

【関数と適当解説】
J列：最左列の日付を取得して表示
=INDEX($3:$3, 1, MATCH(1, INDEX(SIGN(LEN(M18:V18)), 0), 0) + COLUMN() + 2)

1. 「MATCH(1, INDEX(SIGN(LEN(M18:V18)), 0), 0)」の部分は、ざっくり言うと「M〜V列でなにかしら入力されているセルのうち、最も左のセルの列番号（M列基準）」を求めている（M列基準なので、M列になにか入力されていれば 1 に、N列になにか入力されていれば 2 になる）。O列に□があるので 3 が求まる。
2. COLUMN関数でJ列自身の列番号 10 を取得している。
3. +2でデータ列へのオフセット（J列からの距離）を調整している。もしデータ列がK列から始まっていたら（J列の隣＝距離が1なら）+0を、L列から始まっていたら+1を指定する。
4. 3 + 10 + 2 = 15 で、O列の列番号になる。
5. 「INDEX($3:$3, 1, 15)」で「3行目のうち、1行15列目のセルの値＝3行15列目＝O3」を取得する。

K列：最右列の日付を取得して表示
=INDEX($3:$3, 1, MAX(INDEX((M18:V18 <> "") * COLUMN(M18:V18), )))

1. 「MAX(INDEX((M18:V18 <> "") * COLUMN(M18:V18), ))」の部分は、ざっくり言うと「M〜V列でなにかしら入力されているセルのうち、最も右のセルの列番号」を求めている（J列の式と違ってダイレクトに列番号を取得可能）。S列に□があるので 19 が求まる。
2. 「INDEX($3:$3, 1, 19)」で「3行目のうち、1行19列目のセルの値＝3行19列目＝S3」を取得する。

L列：件数カウント
=COUNTA(M18:V18)


お目汚し失礼しました。

JK Love