Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
購入履歴表から、品名毎に直近購入日のデータを抽出したい
投稿日時: 19/12/02 14:56:38
投稿者: ゆーき

お世話になります。
購入履歴表のシートから、品名毎に直近購入日、数量、金額を抽出したいと思っています。
 
購入履歴表シート
 
  A(連番) B(日付)  C(商品名)  D(数量)  E(金額)
   1    1/5    りんご 5     600
   2    1/5    みかん 10    1,100
   3    1/7    バナナ 5     450
   4    1/7    みかん 5     550
   5    1/7    りんご 3     360
   6    1/10    りんご 10    1,200
   7    1/10    みかん 8     960
 
抽出シート
  A(商品名) B(日付)  D(数量)  E(金額)
   りんご
   みかん
   バナナ
 
抽出シートへは最新のデータ(直近のデータ)が自動で商品名横のセル(B2:E4)に入るように
(B2:E4)それぞれどの様な関数を用いて抽出すればよいかご教授お願いします。
 
Vlookup,Max,INDEX,SUMPRODUCT など使ったてやってみましたがうまくできませんでした。
 
抽出結果は下記の様になって欲しいです。
  A(商品名) B(日付)  D(数量)  E(金額)
   りんご  1/10     10    1,200
   みかん  1/10     8     960
   バナナ  1/7     5     450
 
 
申し訳ありませんがよろしくお願いします。

回答
投稿日時: 19/12/02 15:25:53
投稿者: んなっと

●条件を満たす「最後の」値を求める公式
LOOKUP(1,0/(条件),範囲)
 
    A   B   C   D
1 商品名 日付 数量 金額
2 りんご 1/10  10 1200
3 みかん 1/10   8  960
4 バナナ  1/7   5  450
 
B2
=LOOKUP(1,0/(購入履歴表!$C$2:$C$21=$A2),購入履歴表!B$2:B$20)
下方向・↓
C2
=LOOKUP(1,0/(購入履歴表!$C$2:$C$21=$A2),購入履歴表!D$2:D$20)
右方向・→下方向・↓
 
 
●[Excel2010以降]条件を満たす「最大の」値を求める公式
=AGGREGATE(14,6,範囲/(条件),1)
 
    A   B   C   D
1 商品名 日付 数量 金額
2 りんご 1/10  10 1200
3 みかん 1/10   8  960
4 バナナ  1/7   5  450
 
B2
=AGGREGATE(14,6,購入履歴表!B$2:B$20/(購入履歴表!$C$2:$C$21=$A2),1)
下方向・↓
C2
=SUMIFS(購入履歴表!D:D,購入履歴表!$C:$C,$A2,購入履歴表!$B:$B,$B2)
右方向・→下方向・↓

回答
投稿日時: 19/12/02 19:00:34
投稿者: Mike

購入履歴表シート         抽出シート
 A  B  C  D  E         A  B  C  D
1 番 日付 品名 数 金額      1 品名 日付 数 金額
2 1 1/05 リンゴ 5  600      2 リンゴ 1/10 10 1,200
3 2 1/05 ミカン 10 1,100      3 ミカン 1/10 8  960
4 3 1/07 バナナ 5  450      4 バナナ 1/07 5  450
5 4 1/07 ミカン  5  550      5
6 5 1/07 リンゴ 3  360
7 6 1/10 リンゴ 10 1,200
8 7 1/10 ミカン  8  960
9
 
購入履歴表シートにおいて、
1.範囲 A1:E20 を選択 ⇒ Alt+MC ⇒“上端行”だけにチェック入れ ⇒ [OK]
 
抽出シートにおいて、
2.次の各セルにそれぞれ右側に示す式を入力
 ̄ ̄ B2: =IF(ROW()>COUNTA($A:$A),"",MAX((INDIRECT($A$1)=$A2)*INDIRECT($B$1)))
 ̄ ̄ C2: =IF(ROW()>COUNTA($A:$A),"",MAX((INDIRECT($A$1)=$A2)*(INDIRECT($B$1)=$B2)*INDIRECT(C$1)))
 ̄ ̄【お断り】上式は何れも必ず配列数式として入力のこと
3.セル C2 を右隣にオートフィル
4.範囲 B2:D2 を下方にズズーッとオートフィル

回答
投稿日時: 19/12/02 20:44:22
投稿者: んなっと

訂正。
=AGGREGATE(14,6,購入履歴表!B$2:B$20/(購入履歴表!$C$2:$C$20=$A2),1)
 
 
Excelのバージョンも選んでください。最新のExcelであれば
 
=MAXIFS(購入履歴表!B:B,購入履歴表!$C:$C,$A2)
 
優れた関数です。

投稿日時: 19/12/03 07:56:03
投稿者: ゆーき

みなさま早々の回答ありがとうございました。
 
んなっとさんの大変参考になりました。
 
Excelのバージョン2013ですので
=AGGREGATE(14,6,範囲/(条件),1)  を使用して試してみたら
思い通りに動作していました。
 
Excelのバージョンがあがる事で、色々新しい関数が出てきているのを実感できました。
 
ありがとうございました。