エクセルで最初に値が入っているセルを見つける方法はありますか？

エクセルで売上金額の集計を作っています。
スパンとしては６０ヶ月ほどの集計です。
横軸に年月度、縦軸を個人とします。

最後の列に合計金額が入るのですが、それに対して、売上が開始した月と終了した月を自動的に反映させることができるでしょうか？

具体的には、こんな感じです。
(1)Ａ列１行目をを氏名、Ｂ～Ｙ列の１行目を月度とする(2023/04等)
(2)Ｂ～Ｙ列の２行目以降(ｎ行目)に売上金額を入れる
(3)Ｂ～Ｃのｎが空欄(またはゼロ)で、Ｄのｎに１００万円と入力したら、
(4)Ｚ列のｎ行目セルに、Ｄ列１行目の値(2023/06等)が入る

IF関数を使って、「Ｂ列が空欄(またはゼロ)なら」を繰り返すには、６０ヶ月のスパンは長すぎます。

よい方法がありましたら、ご教示ください。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2023/07/18 16:41

こんにちは

イマイチはっきりしないのですが、以下と解釈しました。
・B1:Y1（＝1行目）に日付が入力されている
・各行について、B列～Y列が空白でない最初の列の1行目の日付をＺ列に表示したい

もしも、1行目の日付が『シリアル値で昇順』になっていると仮定しても良ければ、Ｚ2セルに
=AGGREGATE(15,6,(B$1:Y$1)/(B2:Y2<>""),1)

そうでない場合は、Ｚ2セルに
=INDEX($1:$1,AGGREGATE(15,6,COLUMN(B$1:Y$1)/(B2:Y2<>""),1))
を入力して、下方にフィルコピーすればできると思います。

なお、スピル機能が利用できる環境であれば、Z2セルに
=INDEX(FILTER(B$1:Y$1,B2:Y2<>""),1)
でも可能と思います。

※ Ｚ列の書式は、必要に応じて、日付型の書式にしておいてください。
※ 「最後の空白でない列」の日付も、同様の方法で求めることが可能です。
※ Ｂ～Ｙが全て空白の場合には、上の式はいずれもエラーになります。
　（そのような可能性がある場合は、エラー処理を加えてください）

この回答へのお礼

こちらの説明が分かりにくくて申し訳ございませんでした。
ややこしい問題なのに、早々のご回答をいただき、ありがとうございました。
まさに欲しかった処理です。助かりました。

お礼日時：2023/07/19 10:04

No.2 回答者： goomania 回答日時： 2023/07/19 10:37

No.1さんの回答は、

>B列～Y列が空白でない最初の列の1行目の日付
を求めるという理屈で計算式を組み立てたものだと思います。
ですから、同じ理屈で「最後の空白でないの列」も求められると説明されています。つまり、No.1さんの発想は空白であるか否かをチェックするという考えだと思います。
「この表には売上の数値以外は入力しない」という前提なので全く問題ありませんが、それを忘れて、文字列を入力してしまった場合、例えば、ある人の「2023/4」の欄に「研修中」などと文字を入力されてしまうと困ることが起きます。この場合「2023/4」が空白ではなくなってしまうので、その人の売上開始月が「2023/4」となってしまいます。
そこで、「文字列は無視」する方法を考えてみました。

ご質問者は
>最後の列に合計金額が入るのですが、それに対して、売上が開始した月と終
>了した月を自動的に反映させる
と仰っているので、B列～Y列には基本的には売上の「数値」が入るのだと思います。そこで、「最初に数値ある列」「最後に数値がある列」を特定するという方法を使います。

まず、「最初に数値がある列」ですが、MATCH関数を用います。Z2セルに、

=INDEX($B$1:$Y$1,MATCH(0,INDEX(0/$B2:$Y2,0),0))

という数式を入れて下方向へコピーすることになります。
MATCH関数は配列の中から、検索値と完全一致するものの位置を返すことができますが、同じ数値が複数ある場合、「最初にヒットした位置」しか返せません。「0をB列からY列のセルの値で割った配列」はセルが数値なら「0」、空白なら「DIV/0!」、文字列なら「#VALUE!」が返ります(売上0の場合は開始月に含めないものとします)。その配列から最初の0の位置を特定すれば、「最初に(0より大きい)数値がある列」を特定したことになります。

次に「最後に数値がある列」ですが、LOOKUP関数を用います。AA2セルに

=LOOKUP(10^15,$B2:$Y2,$B$1:$Y$1)

という数式を入れて下方向へコピーすることになります。
LOOKUP関数は検索範囲から検索値を見つけて対応範囲の同じ位置にあるものを返すことができますが、検索値が見つからない場合、検索範囲の最後の値の位置に対応する値を返します。ですから、10^15というとても大きな数値で通常セル範囲に存在しないと思われる値を検索値にすれば、見つからないので「最後に数値がある列」を特定できることになります。

纏めると
「最初に数値がある列の月」
Z2=INDEX($B$1:$Y$1,MATCH(0,INDEX(0/$B2:$Y2,0),0))
「最後に数値がある列の月」
AA2=LOOKUP(10^15,$B2:$Y2,$B$1:$Y$1)
となります。

なお、No.1さんの数式と同様の注意事項があります。
※ Ｚ列、AA列の書式は、必要に応じて、日付型の書式にしておいてください。
※ Ｂ～Ｙが全て空白の場合には、上の式はいずれもエラーになります。
　（そのような可能性がある場合は、エラー処理を加えてください）

この回答へのお礼

ありがとうございました。
いろいろな方法があるんですね。実はこの仕様、上司から相談を受けたものなのですが、最初は「無理だろう」と思っていました。
ですが、端から断るのも失礼なので「考えてみます」として、考えながら(結局思い至らず、VBAに逃げようかと考えていた)こちらでも質問したものです。
何事も、簡単に「無理」とか「できない」とは断定できないものですね。

エラーについては、むしろ「#N/A」が出ている方が未入力が分かっていいかもしれません。

お礼日時：2023/07/20 16:35