Excelの関数について

同じ列の中からある値以下の値を全て返し、その行の値、その行の前後の行の差を出したいです。

添付の画像で例えると、B列の5以下を検索する値とすると、返したい値は以下です。
その行の値：(い,5,200)、(か,3,100)、(さ,3,600)
その行の前後の行の差：15-9＝6、21-7＝14、6-8＝-2

難しいかもしれませんがどうかよろしくお願い致します。

質問者からの補足コメント

• 補足で以下の数値も出せると嬉しいです。
  
  該当する行のC列の前後の差
  例：300-500＝-200、600-300＝300、800-100＝700

    補足日時：2023/11/27 16:21

• 早速ご回答いただき有難うございます。
  
  新しくご教示いただいた式を代入したところ、VALUEが出てきました。（数式バーで式は｛｝で囲まれていました。）
  
  TEXTJOINを調べたところ、処理できる行数に制限があるとありました。もしかしたら行数が問題でできないのでしょうか？
  例では15行でしたが、実際は約500,000行処理したいのです、、、。
  
  また、ご教示いただいた式ですと、同じセルの中に全ての数値が入るかと思いますが、別々のセル入れることは可能でしょうか。
  
  色々と後出しの情報となってしまい申し訳ござません。
  
  何卒よろしくお願い致します。

  No.5の回答に寄せられた補足コメントです。 補足日時：2023/11/28 18:21

• 沢山ご回答いただき誠にありがとうございました。
  今回はたくさんご回答いただきましたfujillin様をベストアンサーとさせていただきます。

    補足日時：2023/11/29 09:29

No.6 ベストアンサー 回答者： fujillin 回答日時： 2023/11/28 21:05

No5です。

＞例では15行でしたが、実際は約500,000行処理したいのです、、、。
50万行は多すぎますね・・・

＞別々のセル入れることは可能でしょうか。
関数でも可能ですけれど、配列計算で行うことになるので無駄な計算が多いため、反応が重くなったりする可能性が考えられます。
（大雑把に数千件くらいまでが限度かと・・）

50万行でおこないたいのなら、No4様の回答にある「フィルタオプション」を利用するのが賢明だと思います。

この回答へのお礼

多すぎですよね、、、。
データのまとめ方を変えて数を少なくできるように考えてみます。

色々とご検討くださり誠にありがとうございました。

お礼日時：2023/11/29 08:42

No.8 回答者： mike_g 回答日時： 2023/11/29 09:26

[No.7お礼]へのコメント、

》 ＞貴方はなぜソレを外されたのでしょうか？
》 見落としていました。
よくもまぁ、シャーシャーとｯ!
私、そうもあろうかと、わざわざ着色してたのに？
サ・ヨ・ナ・ラ

No.7 回答者： mike_g 回答日時： 2023/11/28 21:11

[No.5お礼]へのコメント、

》 フィルタを使えばいいのですね。
「B列の5以下を検索する」だけでＯＫな場合は[フィルタオプションの設定]が最も簡単と紹介したまでです。

》 5以下に該当する行も欲しいのですが、…
私の添付図はそうなってませんか？

》 その行の値：(い,5,200)、(か,3,100)、(さ,3,600)
(そ,5,800)も「5以下に該当する行」じゃアーリマセンか？
貴方はなぜソレを外されたのでしょうか？

この回答へのお礼

＞》 5以下に該当する行も欲しいのですが、…
＞私の添付図はそうなってませんか？

私の書き方が良くなかったですね。仰る通り、mike_g様にいただいた回答は5以下に該当する行が抽出されておりました。ただそれにプラスして、その前後の行の差も知りたいという意味で書かせていただきました。

＞》 その行の値：(い,5,200)、(か,3,100)、(さ,3,600)
＞(そ,5,800)も「5以下に該当する行」じゃアーリマセンか？
＞貴方はなぜソレを外されたのでしょうか？

見落としていました。

お礼日時：2023/11/29 08:47

No.5 回答者： fujillin 回答日時： 2023/11/28 17:40

No3です。

＞「この数式には問題があります」と出てOKを押すとFILTERの文字があみがけになります。
No2の回答中でお断りしてあるように、No3は、スピル機能が使える環境を想定した数式です。
2019ではFILTER関数は使えません。

2019で同様のことを行おうとすると、配列数式になってしまいますが・・
E2セルに
=TEXTJOIN("、",1,IF(B2:B16<=E1,"("&A2:A16&","&B2:B16&","&C2:C16&")",""))
の式を入力し、Ctr + Shift + Enter で確定する必要があります。
（数式バーで、式の両端が{}で囲まれて表示されれば正しく認識されたことになります）

No.4 回答者： mike_g 回答日時： 2023/11/27 22:34

》 B列の5以下を検索する値とすると、…

[フィルタオプションの設定]の結果を示した添付図を参照ください。(^_^)

添付図参照(Excel 2019)
￣￣￣￣￣￣￣￣￣￣￣￣￣↓↓↓↓↓↓↓↓↓↓↓↓↓

この回答へのお礼

ご回答いただき有難うございます。

フィルタを使えばいいのですね。5以下に該当する行も欲しいのですが、その前後の行の値も知りたいのですが、何か方法はありますか。

お礼日時：2023/11/28 17:09

No.3 回答者： fujillin 回答日時： 2023/11/27 18:37

No1です。

連投失礼。
E2セルの式中に改行が紛れてしまっていたようなので、訂正しておきます。

E2セルの式は
=TEXTJOIN("、",1,FILTER"("&A2:A16&","&B2:B16&","&C2:C16&")",B2:B16<=E1,""))
です。

この回答へのお礼

回答いただき有難うございます。

E2セルのところですでにつまずいてしまいました、、、。
「この数式には問題があります」と出てOKを押すとFILTERの文字があみがけになります。

バージョンがExcel2019なのですが、それが問題なのでしょうか？

お礼日時：2023/11/28 17:05

No.2 回答者： fujillin 回答日時： 2023/11/27 18:34

こんばんは

ご例示の「5以下」には添付画像の16行目も該当するものとして解釈しました。
エクセルのバージョンが不明なので、スピル機能が使える環境（365など）と仮定しました。

「5以下の」という5の数も可変と考え、例えば
　　E1セルに、5の数字が存在するものとします。
（固定なら、以下のE1セルへの参照を固定値の「5」に変更してください）

この状態で
＞その行の値：
として、E2セルに
=TEXTJOIN("、",1,FILTER("("&A2:A16&","&B2:B16&","&C2:C16&")",B2:B16<=E1,""))
を入力。

＞その行の前後の行の差
として、E3セルに
=TEXTJOIN(",",0,FILTER(IFERROR(B1:B15-B3:B17,"計算不能"),B2:B16<=E1,""))
を入力すれば、質問文にご提示のような結果が得られると思います。

補足の
＞該当する行のC列の前後の差
に関しては、B列と同じなので、計算対象列を変えれば良いでしょう。

No.1 回答者： もちからぼたもち 回答日時： 2023/11/27 17:55

VBAだったらその通り書けばたぶんできます。

関数だったら前後の差の列を足してフィルタかけたらたぶんできます。

この回答へのお礼

ご回答いただき有難うございます。

VBAについて全く知識がないので、できれば関数で処理したいです。

お礼日時：2023/11/28 17:06