[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『数式内の参照セルを可変にする場合』（権兵衛）
if(and(e5=j5,f5=“”,’sheet1’!d19,if(and(e5=j5,isnumber(f5)),’sheet1’!d19+f5/500,’sheet1’!d19) )
Sheet2のk5に上記の数式が入っています。
(セルの前に’sheet1’!とないセルは、全てsheet2のセル番地を表しています)
今回やりたいことは、答えを表示させるセル(k5)は一つのみにしたままで、
上記数式内のe5とf5のみを可変(一列ずつ下がっていくよう)にすることです。
1、e5とf5に数字を入れる→k5に上記数式の答えが表示される
2、e6とf6に新たに数字を入れる
→k5には参照セルが変わった(e5、f5がe6、f6に)数式の答えが表示される
大変分かりにくい質問で申し訳ありません。
いくら調べても方法が発見できず、しかしどうにか完成させたいので、
ご教示をお願いいたします。
＜ 使用 Excel：Excel2013、使用 OS：unknown ＞
実例を２つ書いていただくと行き違いが減るような気がします。
（半平太） 2022/02/18(金) 11:23
＞e5とf5のみを可変(一列ずつ下がっていくよう) 「1行ずつ」ですよね？
・E列とF列に入力するのは数値だけ（文字列は入力しない） ・E列とF列の入力範囲は5行目から最大30行目
K5 =Sheet1!D19+IF(AND(IFNA(LOOKUP(10^9,E5:E30),0)=J5,F5<>""),LOOKUP(10^9,F5:F30)/500,0)
こういうこと？ （笑） 2022/02/18(金) 12:00
今回やりたいことを簡易的にすると以下のようになります。
最初の段階でe1にはa1*b1を入れています(実際にはK5に挿入している式のこと)。
このままの式だと、いくら二行目以下に数値を入力しても、e1にはa1*b1の答えしか表示されません。
なので、この式を変更して、随時最新の数値が入っている行数のセルを参照できるようにしたいと考えております。
……a列………b列………e列 …………………a……b……………e 1………2………200………400………→………2……200……1,200 2…………………………………………………………3……400
説明が下手で申し訳ありません。宜しくお願いいたします。
（権兵衛） 2022/02/18(金) 14:09
数式もありがとうございます。
後ほど実際に挿入したいと思います。
また何かありましたら、質問させてください。
（権兵衛） 2022/02/18(金) 14:12
シート1にはベタ打ちで在庫数を入れる表があります。
シート2(質問内の数式が入っているシート)には二つの表があり、その詳細は以下の通りです。
1つ目の表の主な機能は二つです。
…商品の種類を選択(e列セル)することで、現状在庫をg列に表示させる。
/商品を選択した上で個数単位で目安消費量をf列に-で入力すると、
g列には現状在庫から箱単位の消費量(個数単位目安量/一箱分の数量)が引かれたものが表示される。
?A2種類の箇所に分かれています
?@のe列の文字列と?A表のj列の文字列が合致し(同一に商品名の時)、f列の目安量が空白の時は、シート1の該当商品の在庫数を表示。
…if(and(e5=j5,f5=“”),sheet1!d19のところ
一方で、e列j列が同一商品名で、f列に数字が入っていれば、シート1の在庫数に箱単位目安量を一箱分の数量で割ったものをプラスする。
… if(and(e5=j5,isnumber(f5)),’sheet1’!d19+f5/500,’sheet1’!d19) ) のところ
（権兵衛） 2022/02/18(金) 15:10
質問は最初のレスにございます数式(k5)の参照セルを可変にする方法です。
私の最終レスは、質問内の数式及びそれを含む表の説明になっております。
分かりくく申し訳ありません。
（権兵衛） 2022/02/18(金) 15:53
MATCH関数で記入している最終行の行ナンバーを取り出して
INDIRECT関数と組合せればどうでしょうか
E5の例で行くと5行目が最終行とすれば下記でE5になると思います
INDIRECT("E"&MATCH(MAX(E:E)+1,E:E,1))
E6に入力するとE6が最終行になって変更されます
（なるへそ） 2022/02/18(金) 16:32
数式ありがとうございました。
早速1レス目のk5セル数式に教えていただいたものを当てはめてみました。
ですが、私の理解不足でうまいこと応用できず、上手く値が返ってきませんでした。
せっかく教えてくださったにも関わらず申し訳ありません。
引き続き自分でも調べながら、ご回答をお待ちしております。
（権兵衛） 2022/02/18(金) 17:03
K5=IF(AND(INDIRECT("E"&MATCH(MAX(E:E)+1,E:E,1))=J5,INDIRECT("F"&MATCH(MAX(F:F)+1,F:F,1))="",'Sheet1'!D19,IF(AND(INDIRECT("E"&MATCH(MAX(E:E)+1,E:E,1))=j5,ISNUMBER(INDIRECT("F"&MATCH(MAX(F:F)+1,F:F,1)))),'Sheet1'!D19+INDIRECT("F"&MATCH(MAX(F:F)+1,F:F,1))/500,'Sheet1'!D19))
元の計算式が合っているならこんな感じだと思いますがE5やF5が数字でないとか E列、F列の下の方に何か記入しているとか2013だと使用している関数の回数が多すぎるのかな そんなことはないと思いますが （なるへそ） 2022/02/18(金) 17:56
＞if(and(e5=j5,f5=“”,’sheet1’!d19,if(and(e5=j5,isnumber(f5)),’sheet1’!d19+f5/500,’sheet1’!d19) )
１．相当無駄な数式に見えますね(一度見直した方が良さそう）。
２．現状の説明が分かりにくいです。 それを明確にしないと迷走しますよ。
以下の図形式で、「当初の状態」と「追記後の状態」がどうなるのか説明して貰えませんか。
<Sheet1 シート> 行 _A_ _B_ _C_ __D__
19 9900
<Sheet2 シート> 行 _A_ _B_ _C_ _D_ __E__ _F_ _G_ _H_ _I_ __J__ ___K___
5 1500 900 1500 9901.8 6
（半平太） 2022/02/18(金) 18:27
＞後ほど実際に挿入したいと思います。 試した結果はどうなったんですか？（試してくれたんですよね？）
＞e列の文字列 ＞e列j列が同一商品名 E列って文字列なんですか？
以上、確認だけ （笑） 2022/02/18(金) 18:47
（権兵衛） 2022/02/18(金) 22:07
行 _A_ _B_ _C_ __D__ 19 9900 20. 1100 <Sheet2 シート> 行 _A_ _B_ _C_ _D_ __E__ _F_ _G_ _H_ _I_ __J__ ___K___ 5 商品a. 9900 商品a 9900 6 商品b 1100
2<Sheet1 シート>
行 _A_ _B_ _C_ __D__ 19 9900 20. 1100 <Sheet2 シート> 行 _A_ _B_ _C_ _D_ __E__ _F_ _G_ _H_ _I_ __J__ ___K___ 5 商品a.-300 9600 商品a 9600 6 商品b 1100
（権兵衛） 2022/02/18(金) 22:17
1の段階で、sheet2のe5のみに商品aと入力する(他セルはくうらんのまま)と、k5のセルはsheet1のd19の9900の値がかえってきます。
そして、g5(=k5という数式を入れている)には9900が表示されます
2の段階で、更にf5に-300と入れると、k5には9600(＝300個/@1個:300個の使用目安量を箱一つ当たりの個数でわったもの)が表示されます。
g5にも9600が表示されます。
（権兵衛） 2022/02/18(金) 22:29
（権兵衛） 2022/02/18(金) 22:32
?@のe列の文字列と?A表のj列 正しくは、E列の文字列とJ列でした。
ご指摘ありがとうございました。
（権兵衛） 2022/02/18(金) 22:37
＞1、e5とf5に数字を入れる→k5に上記数式の答えが表示される ＞2、e6とf6に新たに数字を入れる ＞ →k5には参照セルが変わった(e5、f5がe6、f6に)数式の答えが表示される
１．当初の説明と図入りの説明と整合性が取れているんですか？ 私には、ちょっと理解できないですが。
２．6行目に新たに数字を入れる話には、どう繋がって行くのですか？
（半平太） 2022/02/18(金) 23:39
整合性が取れているんですか？
取れております。
1の段階と2の段階は、別の行での区別というわけではなく
同じ行内での作業の段階の話となっております。
6行目に新たに数字を入れる
上述の図形からさらに、
1 sheet2のe6のみに商品aと入力する(他セルはくうらんのまま)と、k5のセルは9600が表示されている。
そして、g5(=k5という数式を入れている)にも9600が表示されます
2 更にf6に-300と入れると、k5には9300(＝300個/@1個:300個の使用目安量を箱一つ当たりの個数でわったもの)が表示されます。
g5にも9300が表示されます。
このようになります。
分かりにくく大変申し訳ありません。
（権兵衛） 2022/02/19(土) 08:04
＞＞整合性が取れているんですか？ ＞取れております
そうですか・・引き続きよく分かりませんが、
F6セルに-300を入れるとどうして9300になるんですか？ その算式を具体的数値で示してください。
9300は商品bに関係していると思うのですが、商品aも何か関係していますか？
（半平太） 2022/02/19(土) 17:28
9300は商品aに関連しています。
なぜなら、e6に商品aと入力しているからです。
（権兵衛） 2022/02/20(日) 08:09
様々なお知恵をお貸しくださり、ありがとうございました。
式の見直しなど、私の方で試してみたいと思います。
また何かありましたら、別の掲示板を立てさせていただきます。
今回は本当にありがとうございました。お世話になりました。
（権兵衛） 2022/02/20(日) 08:12
＞6行目のセルに数値を入れても、今の段階では数値には何の変動もありません。 ＞なぜならk5には5行目のみが参照されるような式しか入ってないからです。 ＞そこで、今回私は、e,fの6行目以降に数値を入れても、k5の値が変わるような数式を作りたくこちらで
今の段階での数式の話はしておりません。 9300になる為の数式を考える上で、9300となる数値の組合せが何なのかを知りたいのです。
＞9300は商品aに関連しています。 ＞なぜなら、e6に商品aと入力しているからです。 そちらが示したサンプルにe6セルが商品aとなったものは無かったと認識していますけど？
次回質問を立てるときは、 (1)初期状態がどうなっているのか。(その結果が得られる数式の仕様の説明） (2)次にどのセルに何を入れると、どう変わって欲しいのか（その結果が得られる数式の仕様の説明） 以上を明確にするようにしてください。さもないと迷走します。
※(1)と(2)を両立させられる数式はこちらで考えます。
（半平太） 2022/02/20(日) 09:30
迷走しているのは質問者の説明。 こちらは質問を素直に解釈して式を提示しただけ。
なのに後になって、E列は文字列ですが何か？みたいな反応。 最初の説明通り、数値を前提にした式を提示したんだから期待する値が返らないのは当たり前の話。
一応言っておくと、E列が文字列ということ以外は最初の説明通りなら
K5 =Sheet1!D19+IF(AND(IFNA(LOOKUP("ー",E5:E30),"")=J5,F5<>""),LOOKUP(10^9,F5:F30)/500,0)
2022/02/18(金) 14:09 以降の説明は何を言っているのか理解できないのでスルーしました。 ただ、 2022/02/18(金) 22:17の中で、Sheet1の「D20セル」にも値が入っていますが、これは何か関係あるんですか？ 可変にするのはE列とF列だけですよね？
それと、回答の式を試してうまくいかなかったのなら どのセルにどんな値が入っている状態で、○○になってほしいのに、その式では××になってしまう と具体的に説明してください。 その説明に矛盾がなければ、ですけど 数式はこちらで考えます（たぶん） （笑） 2022/02/20(日) 13:34
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.