１つの列で特定の項目が最後に入力されているセル調べ

質問

こんにちは。

以下のような表を作っていますが、該当する関数がよくわかりません。

　　　　　　Ａ　　　　　　　　Ｂ　　　　　　　　　　Ｃ
　仕入れた商品名　　仕入日　前回仕入れた日から何日後か？
１　たこ　　　　　　　　　１/１　　　　　　”初回仕入”
２　いか　　　　　　　　　１/5　　　　　　”初回仕入”
３　まぐろ　　　　　　　　1/10　　　　　　”初回仕入”
４　いか　　　　　　　　　1/11　　　　　　”6日”
５　はまち　　　　　　　　1/12　　　　　　”初回仕入”

上記のような表を作りたいと思っています。
「今回の「いか」仕入日は前回の「いか」の仕入日から何日経っているか？」を
計算させたいと思っています。例では、求めた結果、「６日後」という値が出ます。

基本的な式は、「前回の仕入日」から「今回の仕入日」を引けばいいと思うのですが、
「いか」の前回仕入れた日を知るために、直近で最後に「いか」が入力されている行（Ａ列内）を
知りたいのです。

どのような関数を使えば知ることができるでしょうか。

よろしくお願いします。

回答No.3

keithin · ベストアンサー

１行目はタイトル行として
Ｃ２に
=IF(COUNTIF($A$1:A2,A2)=1,"初回",B2-MAX(IF($A$1:A1=A2,$B$1:B1)))
と記入し，コントロールキーとシフトキーを押しながらEnterで入力，下向けにコピーします

さらにＣ列を選んで右クリックしてセルの書式設定の表示形式のユーザー定義を選び
0日;;;@
と設定しておきます。

＃参考
「(エクセル上での)直近」と「最後の日」は，日付が「順不同ではない場合」には一致しません。
実際のデータを見て，必然的に「日付の昇順」でデータが蓄積されるといった何か暗黙の前提があるなら，問題ありません。
でももしかすると日付順がてれんこになる可能性もあるといった場合は，「一番近くに記入されたデータ」と比較するのか，それとも「至近の日付」で計算をしたいのか，考える必要が出てきます。

お礼

回答ありがとうございます！

そのままコピペしたところ無事に条件通り動きました。
構文もシンプルでわかりやすいですね。

参考の件ですが、入力が「てれんこ」になる可能性もなくは
ないですが、日付はシリアルデータが入っており、それを基準に、
今回は「直近」を考えているので、ご提案いただいた式で問題
ないと思います。
（「直近」を「上に入力された行で一番近い行」とは考えていません）

回答No.4

mitarashi

#2です。xl2000と2003では動きましたので、#VALUEとなった原因は分かりかねます。
やっている事は、#3の方とほぼ同じです。COUNTIFの対象範囲がちょっと知恵が足りなかったので、C1に適用できなくなっていますが。
＊の解説をいたします。（SUMPRODUCT関数などで良く使われる表現です）
4行目の「いか」に相当するC4に、{=IF(COUNTIF($A$1:A3,A4)=0,"初回仕入",(B4-MAX(($A$1:A3=A4)*($B$1:B3)))&"日")}という式が入れてあるとき、
{($A$1:A3=A4)}は、「いか」に等しいときだけTRUEとなる、配列{FALSE;TRUE;FALSE}を与えます。（数式バーに上記式を表示した状態で、($A$1:A3=A4)の部分を選択して、f9を押すと確認できます。これは演算する場合{0;1;0}と等価です。
一方、{($B$1:B3)}は、{40909;40913;40918}を与えます。
これらをかけ算すると、0を掛けたものはどんな数でも0になりますので、{0;40913;0}となります。
MAX関数で最大値を取り出すと、「いか」に該当する日付の最大値＝直近の日が得られる事になります。
ご参考まで。

お礼

大変丁寧にフォロー頂きありがとうございます。
「＊」の意味が分かりました。
ここまで、深い？複雑？なことをやったことが
なかったので勉強になってます。
今後の表つくりの参考にしたいと思います。

ありがとうございました。

回答No.2

mitarashi

関数は得意分野ではありませんが、面白そうなのでやってみました。
#1同様配列数式です。前提も同様ですが、ご質問の文ではデータがA1から始まっていると読めますので、それに沿っています。
C2セルに、
{=IF(COUNTIF($A$1:A1,A2)=0,"初回仕入",(B2-MAX(($A$1:A1=A2)*($B$1:B1)))&"日")}
と入れて、下方にドラッグします。
ただし、{　}はCtrl+Shift+Enterで入力すると、自動で付きますので入力不要です。
C1セルには決め打ちで"初回仕入"といれておいて下さい(^^;)

なお、最初ご質問の通り、直近の入力行を求めて、INDEXで日付を取り出すのもやってみましたが、日付がシリアル値である事を利用するとその必要もないのでした。
{=IF(MAX(($A$1:A1=A2)*ROW($A$1:A1))=0,"初回仕入",(B2-INDEX($A$1:B1,MAX(($A$1:A1=A2)*ROW($A$1:A1))-0,2))&"日")｝

補足

回答ありがとうございます！
＃1の方に比較して、後段がすっきりしていますね。

実際にセルに入力して、動作確認したのですが、
６行目（項目名の行含む）の「いか」のところに、
上記の数式を入れてみたのですが、「＃ＶＡＬＵＥ！」という
エラーメッセージが出てしまいました・・・（本来なら「6日」がでるセル）
確認はしてみたのですが、配列数式として入力されているようですし、
特に問題はないと思うのですが。
なぜでしょうか。

あと調べて少し不明だったのでですが、

>MAX(($A$1:A1=A2)*($B$1:B1))
↑の「*」は何の処理を意味しているのでしょうか。

回答No.1

tom04

こんにちは！
一例です。

B列にはシリアル値が入っているとします。
C2セルに
=IF(COUNTBLANK(A2:B2),"",IF(COUNTIF($A$2:A2,A2)=1,"初回仕入",MAX(IF($A$2:A2=A2,$B$2:B2))-LARGE(IF($A$2:A2=A2,$B$2:B2),2)&"日"))

これは配列数式になってしまいますので、
Shift＋Ctrl＋Enterキーで確定します。

この画面からコピー＆ペーストする場合はC2セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

これをオートフィルで下へコピーではどうでしょうか？m(_ _)m

お礼

ありがとうございます！
この値を求めるには、マクロが必要？なんて思っていたもので、
助かりました。
最後にＬＡＲＧＥ関数を使ったのも、わかりやすかったです。

配列数式の確定方法もわかりやすく、ためになりました。

丁寧にありがとうございました！