入出庫帳から部品の最新の入庫価格を抽出したい

質問

在庫らくだの入出庫帳から出力したエクセルファイルで、商品コードから複数ある入庫時の最新の仕入単価を抽出したいです。

（入出庫帳ファイル）
商品コード(D列),仕入単価(J列),区分(N列),日時(P列)
88ant2000,200,入庫,2011/2/23
88ant2000,220,入庫,2012/4/23
88ant2000,300,入庫,2015/6/11
88ant2000,400,入庫,2023/6/11

例えば、上記のように入出庫ファイルに"88ant2000"という商品コードがあり、複数の入庫日が記録されていた場合、抽出したいのは、4つの中で最新日が”2023/6/11”の”400”の数値です。

どうぞ、ご教示の程よろしくお願い致します。

回答No.3

kagakusuki · ベストアンサー

済みません、回答No.1の回答は間違っていました。
　同じコードを持つ商品が1日の内に複数回入庫した場合に、同じ日に入庫したものについては仕入れ単価が必ず同じになるという前提があれば次のような関数になります。(A1セルに商品コードを入力する事で｢最新の仕入れ単価を抽出したい抽出商品コードを｣指定するものとします)

=SUMIFS($J:$J,$D:$D,$A$1,$N:$N,"入庫",$P:$P,MAXIFS($P:$P,$D:$D,$A$1,$N:$N,"入庫"))/COUNTIFS($D:$D,$A$1,$N:$N,"入庫",$P:$P,MAXIFS($P:$P,$D:$D,$A$1,$N:$N,"入庫"))

お礼

回答頂きありがとうございます。
値の抽出ができていることを確認しました。

大変助かりました。

回答No.4

imogasi

[当面している課題のパターン化」ができるようになること。
そして大前提がエクセル関数の利用らしい。質問に景気すべき。
元々（本件は仕事でやることらしいから）ACCSESSのVBAでも使うべき課題だろう、と思うが。
本件では、「同一商品（商品でなくても良い）のうち、入庫日（注文日？）の一番新しい行を見つける」ということになろう。生徒の科目テスト（複数日あり）の実施成績の最近の点数を知るも同じ。
エクセルでは一番新しい（最後とか最近）とは、同一範疇で、日付の（セルの）値が一番大きいと考えればよいのだ。
エクセルの日付の捉え方の、日付シリアル値という考えから、そうなる。
ーー
下記は配列数式を使っているので、余り良いとは言えないが、WEBで他の方法（関数）を探してみてください。２０１９以後なら、すっきりした、関数での方法がある。（質問に、使用しているエクセルバージョンも書いてない。）
他ブックや他シートのデータ参照ならそれなりの番地指定の書き方が必要．下記は同一シート。
この点も質問に明示してないようだ。
例データ　A1：商品 仕入れ日
リンゴ 2023/2/1
リンゴ 2023/3/2
イチゴ 2023/2/14
イチゴ 2023/2/26
柿 2023/2/16
梨 2023/3/19
柿 2023/3/8
梨 2023/3/30
リンゴ 2023/2/18
イチゴ 2023/3/12
柿 2023/3/4
リンゴ 2023/3/31
ーー
E2：E５に（手入力で）
梨
リンゴ
イチゴ
柿
とあるとすると、
F2に式　=MAX(IF(E2=$A$2:$A$13,B$2:B$13)
と入れて、SHIFT+CTRL+ENTERを同時押しする。配列数式。
式をF5まで複写。
結果 E2;F5
梨 2023年3月30日
リンゴ 2023年3月31日
イチゴ 2023年3月12日
柿 2023年3月8日

お礼

回答頂きありがとうございます。
もう少しINDEX関数とかMATCH関数の勉強したいと思います。

助かります。

回答No.2

yamada82

A1からP4までデータがあると仮定して、
P列の日時を新しい順に並べ替えてから、
=INDEX(A1:P4,MATCH("88ant2000",D1:D4,0),16)
という方法もあります。
ちょっと強引ですが。
MATCH関数で指定文字が一番最初にくる行を、
16はA列から16番目＝P列を参照させてます。

お礼

回答頂きありがとうございます。
日時を並び替えておけばシンプルな式で済みそうですね。

活用させて頂きます。

回答No.1

kagakusuki

今仮に、A1セルに商品コードを入力する事で｢最新の仕入れ単価を抽出したい抽出商品コードを｣指定するものとします。
　ExcelのバージョンがExcel2019以降であれば次の関数で最新の仕入れ単価を抽出出来ます。

=LOOKUP(MAXIFS($P:$P,$D:$D,$A$1),$D:$J,7,FALSE)