[関数について]わかる方教えてください

わかりにくい説明に恐縮しております。
ご教示頂けますと幸いです。

B列の数字を空セル①に入力する
空セル①（例えばC列のどれかのセル）に入力した「数字（B列の数字）」からC列にある「「数字（B列の数字）」と同じ数字（C列の数字）」もしくは「「数字（B列の数字）」に近くて大きい数字（C列の数字￥）」を空セル②に出力。
例：B18の数字71200の場合、C14の数字72000が表示される
B19の数字72000の場合、C14の数字72000が表示される

空セル②で表示された数字からA列の番号を空セル③に表示

という事をしたいのですが、、、
お分かりになりますでしょうか？

何卒よろしくお願いいたします。

No.4 ベストアンサー 回答者： goomania 回答日時： 2024/04/12 15:45

No.3です。

前回回答でEXCEL的アプローチもあると書きました。
その例をお示しします。

>空セル①に入力した数字（B列の数字）からC列にある数字（B列の数字）
>と同じ数字（C列の数字）もしくは数字（B列の数字）に近くて大きい数字
>（C列の数字）を空セル②に出力。空セル②で表示された数字からA列の
>番号を空セル③に表示

ということは、ご質問者がNo.1さんへのお礼で「検索値以上で最も小さい値に･･･」と仰っているように「C列の数値のうち①以上で最も小さい数値を②に出力する」という意味と同じです。

添付画像をご覧ください。

①をE2,②をF2,③をG2としています。A列～C列は100行目までデータがあるものとします。

F2に「C列の数値のうち①以上で最も小さい数値」を表示する数式は
=AGGREGATE(15,6,C4:C100/(C4:C100-E2>=0),1)･･･(a)
という数式になります。
(a)の数式は
=AGGREGATE(15,6,配列,1)
という数式になっており、配列を昇順に並べたとき1番目の数値を表示することになります。ここで、配列つまり「C4:C100/(C4:C100-E2>=0)」の部分はC列のC4:C100の範囲のうち①以上つまりE2以上であるという条件を満たす数値の集まりを意味します。この集まりの中で昇順の1番目の数値は「C列の数値のうち①以上で最も小さい数値」になります。

③は前回回答数式のままでもOKですが、(a)の数式により②が表示されますので、②つまりF2が求められているので、
=MATCH(F2,C4:C100,0)･･･(b)
という数式にすることもできます。

No.3 回答者： goomania 回答日時： 2024/04/12 14:33

このご質問はご質問者の過去のご質問から推測すると、

A列A4セル以下に1からの自然数がありこれをnとする。B列B4セル以下に60000+(n-1)×800、C列C4セル以下に60000+(n-1)×1200が、夫々入力されている。B4セル以下のn番目の数値を指定し、その数値以上の最も近い数値をC4セル以下から見つけたとき、これがC4セル以下m番目の数値だとすると、mをnの関数として表せ。

という数学的アプローチで解決できると思います。
「但し、EXCELの関数を使用してよい」という注釈がつくだけです。

800と1200の最小公倍数は2400です。ですから「nが3つ進む」と「mが2つ進む」のと増加分が一致するので、なんとなくnを3分の2にすればmが求められる気がします。
そこで、m=(2n+1)÷3･･･(a)
としてみるとn=1,2,3,4,5,6,7,･･･のときm=1,1.666･･･,2.333･･･,3,3.666･･･,4.333･･･,5,･･･となります。
nが1,2,3,4,5,6,7･･･と進むとmは1,2,3,3,4,5,5と進むことになりますから、(a)の数式の結果を全て、小数点以下を切り上げしてやればよさそうです。すると、
m=ROUNDUP((2n+1)/3,0)･･･(b)
となります。

添付画像をご覧ください。
ご質問では「空セル①にB列の数値を入力」との説明で、条件に合致するC列の数値を空セル②に表示することになるので、画像では①をE2セル、②をF2セルとしています。

(b)の数式は空セル③の数式ですから、まずE2セルからnを求めます。
B列はB4セルから始まっているので、n=(E2-$B$4)/800+1となるはずです。これを(b)に代入すると
m=ROUNDUP((2*((E2-$B$4)/800+1)+1)/3,0)
=ROUNDUP(((E2-$B$4)/400+2+1)/3,0)
=ROUNDUP(((E2-$B$4)/400+3)/3,0)
=ROUNDUP((E2-$B$4)/1200+1,0)･･･(c)
となりますので、これを③の数式にます。画像でG2を③にしています。

よく見ると(c)の数式は①と開始値(60000)との差を1200で割ってC4からどれだけ離れるかを計算する数式になっていることが判ります。A4の開始値が0ではなく1なので、+1になっている訳です。

③が求められれば、②はVLOOKUP関数で求めればよいので、F2に
=VLOOKUP(G2,$A:$C,3,0)
を入れています。

なお、①は手入力だとすると入力ミスも考えられますが、上記数式では入力ミス対策はしておりません。このため、B列に存在しない値を入力した場合、結果が保証されないことになります。従って、E2セルは入力規則でリストを選択しB4セル以下の範囲を指定しておくことをお勧めします。

なお、このような数学的アプローチとは異なるEXCEL的アプローチも当然存在します。

余談ですが、ご質問者は同じご質問を複数回繰り返しておられます。
https://oshiete.goo.ne.jp/qa/13784249.html
https://oshiete.goo.ne.jp/qa/13785056.html
https://oshiete.goo.ne.jp/qa/13785177.html
このような行為はマルチポストと呼ばれマナー違反です。再投稿するときは、まず、前の質問をクローズするようにしましょう。

以下URLの投稿ガイドラインをお読みください。
https://blog.goo.ne.jp/oshietegoo/c/2fd47a043646 …

No.2 回答者： grumpy_the_dwarf 回答日時： 2024/04/12 13:58

C列の値は6万から始まって1200ずつ増えてるので、B列から任意の値を選んだら「1200単位で切り上げ」すれば、検索しなくても選んだ値以上の最小値になるでしょう。

空きセル② =ceiling(空きセル①, 1200)
真面目に検索する場合、match関数やlookup関数では「検索値以下の最大値」しか探さないので、xlookup関数を使います。
空きセル② =xlookup(空きセル①, C列の範囲,"",1)

空きセル②に計算された値がC列の範囲で上から何番目なのかは、match関数で一撃です。
空きセル③ =match(空きセル②, C列の範囲,0)
まじめにA列を返す場合は、vlookupでは右端の列しか探さないのでxlookup関数を使います。
空きセル③ =xlookup(空きセル②, C列の範囲,A列の範囲)

No.1 回答者： 銀鱗 回答日時： 2024/04/11 15:29

VLOOKUP関数を使えばいい。

そんだけの事です。

VLOOKUP関数の使い方を確認してください。
4番目のパラメータに ”１” または ”TRUE” を指定するか、何も指定しない（初期値で TRUE が選択される）と、検索値に対応する値を返すか、検索値が無い場合は検索値未満で最も大きい値に対する値を返します。
ただし検査値は昇順（小さい順）で並べられている必要があります。

質問のケースでは検索範囲と返す値の範囲が同じになるので、
3つ目のパラメータは ”1” になります。

2つ目のパラメータは C列 ですね。

もちろん1つ目のパラメーターは 空白セル① になります。

数式は 空白セル② に書きましょう。

この回答へのお礼

答えに近づきました！！検索値以上で最も小さい値に対する値の場合はどのようにすればよいのでしょうか・・

お礼日時：2024/04/12 10:55