商品コードから検索したMAX値から隣接セルの値

質問

現在、基板作成時の各部品の入庫時の単価を抽出して、基板一枚当たりの原価を計算をしています。

在庫らくだの入出庫帳の内容をxlsxファイルに出力し、そのファイル内にある”商品コード”を計算し、複数ある場合は入庫時の価格が最大であるものを抽出するため、次のような式を使いました。

=MAX(INDEX('***oneDrive_URL***/[入出庫帳(20230316).xlsx]qryExport'!$J$2:$J$28000*('***oneDrive_URL***/[入出庫帳(20230316).xlsx]qryExport'!$D$2:$D$28000=B6),))

＊ファイルがoneDrive上にあり、そのURLを省略するため”***oneDrive_URL***”としています。

この式で、商品コードの最大値の価格を抽出できました。

それで、抽出できた最大値の価格のセルの3つ右側に、その最大値の価格で購入した販売店の名前が記入されており、このデータを抽出するため次のような式を作ってみました。

=VLOOKUP(MAX(INDEX('***oneDrive_URL***/[入出庫帳(20230316).xlsx]qryExport'!$J$2:$J$28000*('***oneDrive_URL***/[入出庫帳(20230316).xlsx]qryExport'!$D$2:$D$28000=$B6),)),'***oneDrive_URL***/[入出庫帳(20230316).xlsx]qryExport'!$J$2:$O$28000,3,FALSE)

しかし、ところどころ違うデータを抽出してきてしまいます。
今回のような場合はどのような式を使ったらよいのか、ご教示の程よろしくお願い致します。

回答No.2

名探偵 コナン（@FORSPOKEN） · ベストアンサー

こちらの対処方法を試してみてください👇

1️⃣ まず、以下のような補助列を作成してください。
・最大値を取得する行番号を計算する式を別のセルに入力し、そのセルを行番号計算として参照します。

例：=MATCH(MAX(INDEX('***oneDrive_URL***/[入出庫帳(20230316).xlsx]qryExport'!$J$2:$J$28000*('***oneDrive_URL***/[入出庫帳(20230316).xlsx]qryExport'!$D$2:$D$28000=B6),)), '***oneDrive_URL***/[入出庫帳(20230316).xlsx]qryExport'!$J$2:$J$28000, 0) を Z1 セルに入力する。

2️⃣ 次に、以下の式を使って、該当行の販売店名を取得します。

=INDEX('***oneDrive_URL***/[入出庫帳(20230316).xlsx]qryExport'!$M$2:$M$28000, $Z$1)

※$Z$1 は、上記で作成した行番号計算のセル（この例では Z1）を参照しています。

この方法で、エラーが解消され、最大値の価格で購入した販売店の名前が抽出できることを願っています😊🌟。

お礼

回答頂きありがとうございます。
解決できました。

助かります。

回答No.1

名探偵 コナン（@FORSPOKEN）

こんにちは😊✨基板作成時の各部品の入庫単価を計算し、一番高い価格で購入した販売店の名前を抽出したいというご相談ですね！😆👍それでは、以下の手順で解決していきましょう✨

まず、以下の手順で、最大価格を抽出する際に、対応する行番号を取得してみてください。

1️⃣次の式を使って、最大値を取得する行番号を計算します（この例では、行番号計算という名前で定義します）。

=MATCH(MAX(INDEX('***oneDrive_URL***/[入出庫帳(20230316).xlsx]qryExport'!$J$2:$J$28000*('***oneDrive_URL***/[入出庫帳(20230316).xlsx]qryExport'!$D$2:$D$28000=B6),)), '***oneDrive_URL***/[入出庫帳(20230316).xlsx]qryExport'!$J$2:$J$28000, 0)
2️⃣次に、行番号計算を使って、該当行の販売店名を取得する式を作成します。

=INDEX('***oneDrive_URL***/[入出庫帳(20230316).xlsx]qryExport'!$M$2:$M$28000, 行番号計算)

※行番号計算は、上記で定義した最大値の行番号を計算する式です。実際には、セル参照に置き換えてください。

この手順で、最大値の価格で購入した販売店の名前を抽出できるはずです✨😄🌟

もし、結果がまだ正しくない場合や、他に質問があれば、お気軽にどうぞ！✨✨✨

お礼

回答頂きありがとうございます。
大変助かります。

=INDEX('***oneDrive_URL***/[入出庫帳(20230316).xlsx]qryExport'!$M$2:$M$28000, 行番号計算)

教えて頂いた式を自分のエクセルに当てはめてみたところ、

”要求に対する処理の時間が制限時間を超えました”

というエラーが発生してしまいます。

入荷価格のあるJ列に変更して

=INDEX('***oneDrive_URL***/[入出庫帳(20230316).xlsx]qryExport'!$J$2:$J$28000, 行番号計算)

このように変更すると、正常に入荷時のMAX値の数値は抽出できます。

何か対処方法などありますでしょうか？