[[20221201151312]] 『変動する指定セルの下がブランクか判断して返すセ』(エクセル事務員) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『変動する指定セルの下がブランクか判断して返すセルを変更?』(エクセル事務員)

在庫表から、指定した日付の最後の行の在庫数が表示されるようにしたいのですが、
どのような数式で実現できるでしょうか。

在庫表のイメージは下のようなものです。

  A列  B列  C列

 1 指定日   指定日在庫
 2 12/3         60
 3          
 4 日付  出荷数  残在庫
 5 12/1     10    100
 6 12/2     15     85
 7 12/3     15     70
 8       10     60
 9 12/4     20     40
 10       30     10

1日1行の日もあれば複数行の日もありますが、複数行の場合、2行目以降の日付(A列)がブランクです。
上のイメージでC2に入れる数式を考えているのですが、IF、OFFSET、VLOOKUP、MATCHなどを組み合わせてやってみても私ではうまくいきません。

ご教示くださいますようお願いいたします。

< 使用 アプリ:Office2021、使用 OS:Windows11 >


=FILTER
を調べてみて下さい。^^
多分、出来たような気がいたします。

(隠居Z) 2022/12/01(木) 15:55:37


↑取り消します、済みませんでした
日付けが空白だとダメかもです。←自信が御座いませんので
無視して下さいm(__)mm(__)mm(__)m
(隠居Z) 2022/12/01(木) 15:59:14

 すべての行に日付を入れておき、条件付き書式で上のセルと同じ日付は表示しないようにしておいて
 MINIFS関数を使ってはどうだろうか?

(ねむねむ) 2022/12/01(木) 16:21:42


ご回答いただきありがとうございます。

この在庫表、日々の動きを入力・管理するのは他部署の方たちなので、すべての行に日付を入れるように運用を変えるのはすぐにできないのです…… 組織って……

「指定した日付の下セルがブランクの場合はその行の在庫数を、ブランクでないならば指定した日付の入っている行の在庫数を返す」といったことは、数式では難しいでしょうか?
(エクセル事務員) 2022/12/01(木) 17:13:26


 もし、同一日付の行が3行以上になることがなければそれで求められるが、実際に3行以上に
 なることはないのだろうか?
(ねむねむ) 2022/12/01(木) 17:22:01

 全員が Office2021 なら、
 これで行けるかも(最大10000行までとして)

 C2セル =LET(cn,COUNT(C5:C10000),q,SEQUENCE(cn,1,5),r,A5:INDEX(A:A,cn+4),XLOOKUP(A2,LOOKUP(q,q/ISNUMBER(r),r),C5:INDEX(C:C,cn+4),"",0,-1))

(半平太) 2022/12/01(木) 17:35:39


在庫表の運用方法を確認したところ、3行になることはないそうです。

しかし先述の内容を数式化しようとしても数式として成立させられず、ご教示いただければ幸いです。
(エクセル事務員) 2022/12/01(木) 17:41:03


半平太様、ご回答いただきありがとうございます。

早速試してみます。
(エクセル事務員) 2022/12/01(木) 17:41:37


ダメでした…… エラー表示になってしまいます。

半平太様の数式は私レベルでは理解が追いつかないので、どこが原因かもわからないのですが。

エクセルでは無理なのでしょうか……
(エクセル事務員) 2022/12/01(木) 18:09:10


 こんばんは

 SEQUENCE関数が使用できない場合に...これで行けるかも
 (半平太)さんと一緒にしといた(最大10000行までとして)

 C2セル =IF(OFFSET(A1,MATCH($A$2,$A$5:$A$10000,0)+4,0)="",OFFSET(A1,MATCH($A$2,$A$5:$A$10000,0)+4,2),INDEX($A$5:$C$10000,MATCH($A$2,$A$5:$A$10000,0),3))

 ※検索日付の下に...空白行が、2行あるとか無しね^^;

(あみな) 2022/12/01(木) 18:10:57


 >エクセルでは無理なのでしょうか……

 いえ、出来ますよ。特に「在庫表の運用方法→3行になることはない」なら
 難易度がぐっと下がります。

 ※他の回答者からレスがあると思いますので、待ってみてください。

(半平太) 2022/12/01(木) 18:15:31


 作業列を使えばいいのでは?

 どこか空いた列の5行目
 =IF(B5="","",IF(A5<>"",A5,MAX($A$4:A4)))
 下コピー

 この列で MINIFS とか XLOOKUP とかを使う。

 同じ日が3つ以上あっても問題ありません。

 以上
(笑) 2022/12/01(木) 18:30:11

 こんなのではどうだろうか?
 =AGGREGATE(15,6,C5:C10/(IF(A5:A10="",A4:A9,A5:A10)=A2),1)

 同じ日付内の最後の行が一番小さくなるものとして。
(ねむねむ) 2022/12/01(木) 19:59:51

 最多2行しかない場合(計算量が心配なので、3000行まで)

 1.全員が Office2021 を使っているなら、
  C2セル =LOOKUP(A2,IF((A5:A3000="")*(C5:C3000<>""),A4:A2999,(A5:A3000&"")*1),C5:C3000)

 2.Office2021 より古いのも使っている人が居る場合
  C2セル =LOOKUP(A2,IF(MMULT((A5:A3000="")*(C5:C3000<>""),1),A4:A2999,INDEX((A5:A3000&"")*1,0)),C5:C3000)

(半平太) 2022/12/01(木) 22:34:35


前提付きですが、提示されたように日付に飛びがなく昇順でしたら
次の式でもできそうに思います。

 C2: =INDEX(C5:C11,MATCH(A2+1,A5:A11)-1)
(メジロ) 2022/12/02(金) 08:57:07

皆様いろいろとご回答いただきありがとうございます。

順番に試してみて、後ほどご報告いたします。
(エクセル事務員) 2022/12/02(金) 09:00:22


 メジロさん

 12/4の時、マズくないですか?

(半平太) 2022/12/02(金) 09:09:41


まだ閉じてないみたいなので別案。

=INDEX(C5:C10,IFERROR(MATCH(SMALL(A5:A10,COUNTIF(A5:A10,"<="&A2)+1),A5:A10,0)-1,ROWS(A5:A10)))
(d-q-t-p) 2022/12/02(金) 09:26:58


 めじろさんのアイデアをヒントに、2行縛りなしで考えてみました。(3000行まで想定)

 C2セル =IF(A2>=MAX(A5:A3000),LOOKUP(8^16,C5:C3000),INDEX(C5:C3000,MATCH(0,INDEX(0/(A5:A3000>A2),0),0)-1))

(半平太) 2022/12/02(金) 09:54:26


おかげさまで解決いたしました。

私がメンテナンスしやすいと感じた、あみな様の数式で進めていきます。

様々なアイデアを頂戴し、誠にありがとうございます。

ネット上でも手を差し伸べてくれる方がいるというのは嬉しいものですね。

また困ったときには質問させていただきます。
(エクセル事務員) 2022/12/02(金) 11:51:11


 >あみな様の数式で進めていきます。

 最終日が一行で完結していたら、マズくないですか?

(半平太) 2022/12/02(金) 12:46:40


半平太さんへ

ご指摘ありがとうございました。
最後のツメをしていないことが多くなりました。
年のせいにして謝罪!

エクセル事務員さん、失礼しました。
(メジロ) 2022/12/02(金) 13:25:37


戻ってきました。

半平太様がおっしゃる通り、最終日が一行の場合ゼロが表示されてしまい駄目でした。

他の数式で再度検討してみます。
(エクセル事務員) 2022/12/02(金) 14:07:51


半平太様が最後に提示された数式が問題なく動くみたいですので、こちらを使わせていただきます。

ただ、数式の中でLOOKUP関数の検索値を「8^16」とされているのは、何を意味しているのでしょう?

エクセル初心者に毛が生えた程度の私には何が何やら……

ともかく、問題解決しました。ありがとうございました。
(エクセル事務員) 2022/12/02(金) 14:20:39


  >数式の中でLOOKUP関数の検索値を「8^16」とされているのは、何を意味しているのでしょう?

  8^16は、8の16乗(281,474,976,710,656)、つまりとてつもなく大きな数値です。

  LOOKUP関数は、対象範囲にあるどの数値よりも大きな数値で検索すると、
  その範囲の最後尾の値を返してくる性質があります。

  <応用例>

  B1セル =LOOKUP(MAX(A1:A4)+1,A1:A4)

  行  _A_  _B_
   1    4   2
   2   10     
   3    3     
   4    2     

(半平太) 2022/12/02(金) 15:20:00


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.