エクセルの関数について

質問

飲料水の消費期限の表を作ろうと思っています。
作りたいの表の例として、
下記の添付画像のように、A表に購入年月日、種類、消費期限を入力します。
B表に、A表で入力した中から、消費期限が一番近い年月日を種類の横に表示させたいと思っています。

よろしくお願いします。

回答No.3

bunjii · ベストアンサー

>B表に、A表で入力した中から、消費期限が一番近い年月日を種類の横に表示させたいと思っています。
A表の消費期限が日付のシリアル値であることを条件とします。
次の数式をお勧めします。
=IF(E4="","",SUMPRODUCT(MIN((B4:B15=E4)*C4:C15+(B4:B15<>E4)*(MAX(C4:C15)+1))))

貼付画像はExcel 2013で検証した結果です。
全てのバージョンで再現できると思います。

お礼

ありがとうございます。

B列に種類を入力して、C列に消費期限を入力しない場合、F列に「M33.1.0」と表示されますが、C列に消費期限をまだ入力しない場合、F列に「M33.1.0」とされず、空欄にする方法がありましたら、教えていただければと思います。

よろしくお願いします。

回答No.10

bunjii

>ちなみに、２番目に短い消費期限を表示させることも可能でしょうか。
可能ですが数式を変更する必要があります。
MIN関数をSMALL関数に置き換えてください。
使い方はヘルプで使用例を見ながら習得されると良いでしょう。
MIN((B$4:B$15=E4)*C$4:C$15+(B$4:B$15<>E4)*(MAX(C$4:C$15)+1))
　　　　　　　　　↓
SMALL((B$4:B$15=E4)*C$4:C$15+(B$4:B$15<>E4)*(MAX(C$4:C$15)+1),2)
但し、該当がないときは登録された最大の日付の翌日になりますので、IF関数の論理式も変更する必要があると思います。

回答No.9

MackyNo1

>ちなみに、２番目に短い消費期限を表示させることも可能でしょうか。

この場合も条件によって数式が変わります。
たとえば、同じ種類は必ず2つ以上の消費期限データがあるという条件なら、以下の数式になります。

=SMALL(INDEX(($B$4:$B$100<>E4)*1000000+$C$4:$C$100,),2)

セルの書式設定は、No8の回答と同じように設定してください。

回答No.8

MackyNo1

No4の回答の補足です。

No4の回答では数式を提示しませんでしたが、以下のような数式にするほうが計算負荷が少ないかもしれません。

=MIN(INDEX(($B$4:$B$100<>E4)*1000000+$C$4:$C$100,))

このケースの場合、セルの表示形式はユーザー定義で以下のように設定してください。
[>99999]"";[=0]"";[$]ge.m.d;@

今回の結果をどのような目的に使用するのかにもよりますが、単純に消費期限が一番近い年月日を種類ごとに表示したいなら、ピボットテーブルを利用するほうが簡単です。
この場合、種類が追加されても自動的に追加されます（なお関数で対応したい場合は、ふくっ雑な数式になりますが重複のないデータを自動表示することも可能です）。

「挿入」「ピボットテーブル」で行フィールドに種類、Σ値(データフィールド）に賞味期限を配置し、データフィールドの上で右クリックし「値フィールドの設定」で集計の方法を「最小値」にして、表示形式を日付にすればご希望の集計結果が得られます。

この時元データをホームタブの「テーブルとして書式設定」しておけば、データの追加に自動対応してくれます(追加した場合はピボットテーブル上で右クリックから「更新」してください）。

回答No.7

tom04

No.5です。

＞ちなみに、２番目に短い消費期限を表示させることも可能でしょうか。

というコトですので、もう一度画像をアップしてみます。
複数表示になりますので、列方向（右方向）に順に表示させます。

↓の画像で作業列（D4セル）の数式、F4セルの数式は前回同様です。
結果のG4セルに
=IFERROR(INDEX($C$4:$C$1000,MATCH(SMALL(IF(($B$4:$B$1000=$F4)*($C$4:$C$1000>=TODAY()),$C$4:$C$1000),COLUMN(A1)),$C$4:$C$1000,0)),"")
今回も配列数式ですので、Ctrl＋Shift＋Enterで確定してください。
これを列・行方向にフィルハンドルでコピーすると
画像のような感じになります。

※　今回も賞味期限切れ（賞味期限が本日より前のもの）は表示しないようにしています。m(_ _)m

回答No.6

bunjii

>B列に種類を入力して、C列に消費期限を入力しない場合、F列に「M33.1.0」と表示されますが、C列に消費期限をまだ入力しない場合、F列に「M33.1.0」とされず、空欄にする方法がありましたら、教えていただければと思います。
空欄は数値として 0 として評価されますので日付に直したとき 1900/1/0（M33.1.0） と表示されます。
従って、同じ種類で最小になります。
空欄にするときはIF関数で結果が 0 のとき空欄とするようにすれば良いでしょう。
=IF(SUMPRODUCT(MIN((B4:B15=E4)*C4:C15+(B4:B15<>E4)*(MAX(C4:C15)+1)))=0,"",SUMPRODUCT(MIN((B4:B15=E4)*C4:C15+(B4:B15<>E4)*(MAX(C4:C15)+1))))

お礼

ありがとうございます。
大変助かります。

ちなみに、２番目に短い消費期限を表示させることも可能でしょうか。
よろしくお願いします。

回答No.5

tom04

こんにちは！
横からお邪魔します。

余計なお世話かもしれませんが、↓の画像のように作業列を設け
F列に「種類」が重複しないように表示させてみました。

尚、質問の画像では「調味期限」が「今日以降」になっていますが、
万一賞味期限切れのものがある場合はそれを表示させないようにしています。
（賞味期限がある商品が一つもない場合はエラーとなります）

列配置が違うので、やり方だけです
↓の画像で作業列D4セルに
=IF(COUNTIF(B$4:B4,B4)=1,ROW(),"")
という数式を入れフィルハンドルで下へしっかりコピーしておきます。

そして、F4セルに
=IFERROR(INDEX(B:B,SMALL(D:D,ROW(A1))),"")

G4セル（セルの表示形式は　ge.m.d　）に
=IF(F4="","",INDEX(C$4:C$1000,MATCH(MIN(IF((B$4:B$1000=F4)*($C$4:$C$1000>TODAY()),C$4:C$1000)),C$4:C$1000,0)))
このG4セルは配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　G4セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。

最後にF4・G4セルを範囲指定　→　G4セルのフィルハンドルで下へコピー！
これで画像のような感じになります。m（_ _）m

お礼

ありがとうございます。
大変助かります。

ちなみに、２番目に短い消費期限を表示させることも可能でしょうか。
よろしくお願いします。

回答No.4

MackyNo1

>C列に消費期限をまだ入力しない場合、F列に「M33.1.0」とされず、空欄にする方法がありましたら、教えていただければと思います。

この部分だけなら、セルの書式設定の表示形式をユーザー定義にして、以下のように「；」を２つ挿入してください。

[$-411]ge.m.d;;;@

数式で対応するならTEXT関数を使って以下のようにするのが簡単です。

=TEXT(元の数式,"ge.m.d;;;@")

ただし上記の数式で表示されるデータは文字列ですので、その値を使用して何か処理する場合（例：数式に使用する場合）は少し工夫する必要があります。

お礼

ありがとうございます。
大変助かります。

回答No.2

ryo_ky

前提として消費期限は数値である事（書式設定でH27.1.31となっている）とすれば，消費期限が短い＝値が小さい事になります．

数式としては
=MIN(INDEX((B:B<>E4)*10^5+C:C,0))

これを下にオートフィルすればOKです．

過去に同じような質問がありましたので，参考URLを入れておきます．

参考URL：

http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1149266196

お礼

ありがとうございます。

B列に種類を入力して、C列に消費期限を入力しない場合、F列に「M33.1.0」と表示されますが、C列に消費期限をまだ入力しない場合、F列に「M33.1.0」とされず、空欄にする方法がありましたら、教えていただければと思います。

よろしくお願いします。

回答No.1

aokii

A表を消費期限順に並べ替えてからになりますが、F4に次の式を入れて、下にドラッグコピーしてみて下さい。
=VLOOKUP(E4,B:C,2,FALSE)

お礼

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