条件に一致する最終行の値をエクセル関数で抽出する方法

B18にAの在庫数(C6)、B19にBの在庫数(C10)、B20にCの在庫数(C15)が
自動に反映されるよう数式をいれたいと考えています。

数式をご存知の方ご教示お願い致します。

No.12 回答者： d-q-t-p 回答日時： 2020/12/03 09:52

#3の数式はたまたま上手くいくだけなので止めといた方がいいです。

データ量や一致する値の位置によっては正しく答えを返しません。

それは置いといて バージョン不明ですが最新と仮定するなら

B18
=XLOOKUP(A18:A20,A2:A15,C2:C15,"",0,-1)

でいいです。

No.11 回答者： goomania 回答日時： 2020/12/01 22:52

古いバージョンでも利用できる数式を考えてみました。

ご質問者のご要望は、各名前ごとの現在の在庫数を表示させたいとのことなので、投稿された画像が判断すると、各名前のグループの最終行の在庫数を返す関数を作ればよいことになります。
すでにご回答いただいたものの中にはA列に連続番号を振って、各グループの最終行(最大行番地)を求める方法もありますが、行番地はROW関数で求められますので、A列に連番を設けなくても
B18に
=INDEX($C$1:$C$15,MAX(INDEX(($A$2:$A$15=A18)*ROW($A$2:$A$15),,0)))･･････①

という関数を記述し、オートフィルで下方向へコピーすることで対応可能です。

上記の数式で
INDEX(($A$2:$A$15=A18)*ROW($A$2:$A$15),,0)の部分は
A列がA18に等しければ1、そうでなければ0を返す配列と行数を掛けて生成される配列です。INDEX関数は列数を0とすると行全体を返すという性質を利用します。
つまり、この配列は2;3;4;5;6;0;0;0;0;0;0;0;0という配列になるわけです。
MAX(INDEX(($A$2:$A$15=A18)*ROW($A$2:$A$15),,0))の部分で各グループの最終行番地を求めます。
行番地が判明したので、在庫数が表示されているC列の当該行番地のセルの値を返せば、この課題は解決することになります。
そこで、①の式になるわけです。
ここで、INDEXの対象範囲のC列は、データのある$C$2からではなく、セル番地の行数1行目から数えるため、$C$1から範囲指定します。

No.10 回答者： banzaiA 回答日時： 2020/11/28 16:36

#7です。

在庫積み増しなど想定していませんでした。
最新バージョン365ですが。
＃８の回答者さんのように連番の列を挿入可能なら、
A列（連番）B列（名前）C列（在庫数）とし、
A列に連番を１～15（添付されている図を例として）
B18のセルに
=XLOOKUP(MAX(INDEX(FILTER($A$2:$D$15,$B$2:$B$15=A18,""),,1)),$A$2:$A$15,$D$2:$D$15)
でいかがですか。
これなら、商品毎の範囲を使って設定の必要はありません。

No.9 回答者： enunokokoro 回答日時： 2020/11/28 13:40

No.3の回答者です。

VLOOKUP関数を使った方法について、補足です。

[名前]をVLOOKUP関数では[検索値]にしているので、この関数での
省略している[検索の型]が[ TRUE ]となっています。
https://support.microsoft.com/ja-jp/office/vlook …
https://office-hack.com/excel/vlookup/

この設定だと、[名前]の列は昇順でないと正しく値を示しません。
今回の質問だとABCの昇順名なので問題ないですが、漢字などでの
名前を使っている場合は注意が必要です。
読みとしての昇順ではなく、文字コードでの昇順にしておかないと、
値が別の名前のものになる場合があります。

それと、範囲についてもC名の範囲が増えたときに、行を挿入しても
範囲は拡張されません。
手動で範囲を増やすか別方法で対応します。
https://excel-ubara.com/excel3/EXCEL016.html
このような設定については、別質問されると丁寧な回答が新規に付く
と思います。

No.8 回答者： fxq11011 回答日時： 2020/11/28 09:44

最左（連番部分）Ａ列・・・Ｂ列＝商品名、Ｃ列＝出庫数、Ｄ列＝在庫数

Ａにかかる部分の関数
＝IF(D2="","",A1+1)・・・Ａ１は空白 （または０）＝
IF(D3="","",A2+1)＝
IF(D4="","",A3+1)＝IF(D5="","",A4+1)以上で在庫表示が無ければ連番は表示されません（連番の最大数の行の在庫が最終の在庫になります）
Ａの残り在庫表示部分の関数
＝VLOOKUP(MAX(A2:A5),A2:D5,4) （Ａの在庫表示セルがＤ列５行目までの場合、10行でも20行でも拡張可能です、入庫、出庫の記入の都度自動的に変更されます）
これで、在庫積み増しに対しても対応可能になります
例　Ｅ列に新規入庫数の欄として数値を入力、Ｄ列（在庫）の数式にＥ列の数値をプラスします
※通常見かけるのは、（連番Ａ列）商品名Ｂ列、入庫Ｃ列、出庫Ｄ列、差し引き在庫Ｅ列
これの場合もほぼ同様に使えますが範囲指定がＡからＥまでになり、ルックアップの位置指定が４では無く５になります（Ａ、Ｂ、Ｃ、Ｄ、Ｅの在庫は５列目になりますね）
こんな形の時は商品毎に別シート、最終在庫表示シートも、別シート？で範囲指定の場合は該当のシート名に続けて範囲指定する形になります（例　シート名を「商品Ａ」とすれば、商品A!A2..D6）

NIN関数使用の場合（在庫積み増しには対応できません）
=MIN(D１:D5) （Ａの在庫表示セルがＤ列５行目までの場合）
※Ｄ列（在庫表示）の最小の数値が表示されます
以上どちらの場合も
商品毎の範囲を使って設定の必要が有ります

No.7 回答者： banzaiA 回答日時： 2020/11/27 23:02

#3にて回答がでているようですが、（なぜそうなるかは分かりません）

B18に
=MIN(INDEX(FILTER($A$2:$C$15,$A$2:$A$15=A18,""),,3))
でどうですか。
ちなみにExcelのバージョンが書かれていないので
最新バージョン365ですが。

No.6 回答者： fxq11011 回答日時： 2020/11/27 17:12

＞VLOOKUP($A18,$A$2:$C$15,3)

検索値＄A18＝A→（すぐ下にコピーすれば＄A19＝B）
範囲A2からC15でセルの値がAを検索？、でも複数ありますね、楯列３番目の数値、例の場合は、５つの数値がありますね、たぶん不能、です。

No.5 回答者： fxq11011 回答日時： 2020/11/27 17:00

NO2

訂正
A列の前に列挿入、昇順場番号入力ではだめですね。
同じ行のC列が空白ならば、一つ上の数値＋１、そうでければ空白、のIf関数が必要です。
それはそうと、例示の表だけ見ればC列の在庫数は減る一方ですね、であればA商品にかかるC列すべてを範囲指定して最小の数値を表示させる関数でも間に合いそうですが
MIN関数、範囲C2..C6、これでC列６行目までで最小の数値がB１８に表示されます。

No.4 回答者： mike_g 回答日時： 2020/11/27 11:57

[No.3]へのコメント、

》 何故、これで可能なのかはわかりません。
》 有識者からのアドバイスがあれば、知りたいですね。
ビックリポン！同感！私も知りた～い！
ひょっとしてバグ？
MSは知らぬ存ぜぬの頬被り？

No.3 回答者： enunokokoro 回答日時： 2020/11/27 10:46

こちら↓でも可能なようです。

　=VLOOKUP($A18,$A$2:$C$15,3)

何故、これで可能なのかはわかりません。
有識者からのアドバイスがあれば、知りたいですね。

出荷数がマイナス（入荷の場合など）になっても最終行の値になって
くれるようです。
離れた別列だったり、別シートでも一応可能なようです。