[[20041103162245]]　『３のところのウのくが抜き出せません』（ミーママ）　ページの最後に飛ぶ

『３のところのウのくが抜き出せません』（ミーママ）

[どなたか教えてください！！]

下の表は数式を入力していて、どんどん変化していきます（減少）。
そして数が「５」以下になったら赤になるよう条件付き書式を設定しています。
そして「５」以下になった数を横の方に抜き出しています（数式を入力）。
そこで、その数の種目（例えば"３”のところの”ウのく”）が抜き出せません。この表が変化していくので、循環参照とかで、うまく出来ません。というより分かりません。どなたか教えてください！！

 　n　  o　　  p　　 q　 r

 1　　　か　　き　　く　　け

 2 ア　１　４５　　４３　５

 3 イ　４　　４　　５３　３４

 4 ウ　３４　１　　　３　　４

 減少していくのは手入力ですか。
 また、横のほうに抜き出すとは具体的にどのようにしてでしょうか。（数式を挙げてみてください）
 （川野鮎太郎）

実は上の表の下の方に同じ形の表を作成しています（元の表）。そして上の表の横の方に

 　　Ａ　Ｂ　Ｃ

 １　イ　か　７

 ２　ウ　き　５

・

 　　　　・
　　　
 　　　　・

 というふうに入力していきます。
 これは、下の表の「"イ"と"か"の交差するところのセルの数から「７」を引いた残りが「４」のなるという感じです。
 なので、下の表の数は数値入力ですが、上の表の数は下の数式を入力しています。

 =｛a1-SUM(IF(($a$1:$a$10=$n$3)*($b$1:$b$10=$o$1),$c$1:$c$10))｝

 そして「５」以下のデータを抜き出しているのは、
 =｛IF(ISERROR(SMALL(IF($o$2:$r$4<=5,$o$2:$r$4,""),ROW()-1)),"",SMALL(IF($o$2:$r$4<=5,$o$2:$M$4,""),ROW()-1))｝という数式を入力しています。（下にコピー）

１

１

 　　　　　　４
　
 　　　　　　５

 というふうに「５」以下の数値は出るのですが、その数値がそれぞれ何処の行の何処の列なのかという数式がわからないのです。
 説明不足で申し訳ありません！
 すみません！初めてなものでうまく数字をならべませーん！！

 行の頭に半角スペースを入れて整形すると良いですよ。
 とりあえず直しておきましたけど、内容はまだ見てません(^_^A;

 （川野鮎太郎）

 とりあえず疑問点ですが、
=｛a1-SUM(IF(($a$1:$a$10=$n$3)*($b$1:$b$10=$o$1),$c$1:$c$10))｝
 上の式はどこのセルに入力されていますか。
 それとA1から引くようになってますが、A1には数値がないようですが・・・。

 下の式はどこのセルに入力されていますか。
 （川野鮎太郎）

 これって
=IF(ISERROR(SMALL(IF($N$2:$Q$4<=5,$N$2:$Q$4,""),ROW()-1)),"",SMALL(IF($N$2:$Q$4<=5,$N$2:$Q$4,""),ROW()-1))
と
=C1-SUM(IF(($A$1:$A$10=$N$3)*($B$1:$B$10=$O$1),$C$1:$C$10))
なんじゃないのかな？？
ちがうかな？？
(SoulMan）

 申し訳ありません。
上の数式の”A1”というのは間違いでした。
下の表（とりあえず上の表を"a表"、下の表を"b表"とします）ｂ表ですが、"m11:r20"まであって、これはａ表と同じ形です（すみませんａ表は4行までしか書いていませんが実際は10行まであります）。

    Ｎ　Ｏ　Ｐ　Ｑ　Ｒ

　　　　か　き　く　け

13　ア　56 67 78 87

14 イ　55 87 98 67

15 ウ　57 87 67 56

とりあえずここでは"ｍ11:r15"とします。ｂ表というのが元々のデータでこの表の数値から引かれていく結果が、a表のそれぞれの同じ位置に表われるようにしています。なのでｂ表のデータは変化せず、ａ表のデータは、どこかしら減少していきます。というわけで、まず上の方の式は"02"のセルに入力しています。そして、"a1"が、間違いで、"o13"となります。つまり、上の方に表示してます「イか 7 」「ウき５」というのが、「ｂ表の"イ"と"か"の交差する"セルo14"のデータ"５５"から"7"を引く。また、ｂ表の"ウ"と"き"の交差する"セルp15"のデータ"８７"から"５"を引く。というふうになって引かれた結果がａ表に出るようになります。ということで、上の数式は、"o2"
に入力していますが、この式を"o2:r4"までコピーしているので、・・・・とすみません！！。上の数式の絶対参照のつけ方も間違ってました！セル"o2"の正しい式は・・

 ｛=o13-SUM(IF(($a$1:$a$10=$n2)*($b$1:$b$10=o$1),$c$1:$c$10))｝

となります。間違いだらけで申し訳ありません。。
それから、下の式はａ表の左側の開いたところの"ｈ2"あたりです。そして下に適当にコピーしています。

｛=IF(ISERROR(SMALL(IF($o$2:$r$4<=5,$o$2:$r$4,""),ROW()-1)),"",SMALL(IF($o$2:$r$4<=5,$o$2:$M$4,""),ROW()-1))｝

これで、「５以下」のデータそのものは抜き出せるのですが、そのデータがそれぞれ何の種目なのかというのが抜き出せません。

よろしくお願いします。
この説明で通じましたでしょうか？？

　（自分のペンネームも載せるの忘れてました。。すみません　ミーママです）

 こんばんは！レスがつきませんね。
多分、わからいんだと思います。答えじゃなくてご質問の意味が・・・
それと、やはり情報は正確に伝えないと意味をなしませんよ。
｛=IF(ISERROR(SMALL(IF($o$2:$r$4<=5,$o$2:$r$4,""),ROW()-1)),"",SMALL(IF($o$2:$r$4<=5,$o$2:$M$4,""),ROW()-1))｝
この数式はあってますでしょうか？
私が以前に提示した様に
=IF(ISERROR(SMALL(IF($O$2:$R$4<=5,$O$2:$R$4,""),ROW()-1)),"",SMALL(IF($O$2:$R$4<=5,$O$2:$R$4,""),ROW()-1))
ではないですか？
もしも、そうだとしての私の想像ですが、
 >そのデータがそれぞれ何の種目なのかというのが抜き出せません。
というのは
1
1
3
4
4
4
5
のことだと思うんですが、これは
$O$2:$R$4<=5,$O$2:$R$4
の部分の
{1,45,43,5;4,4,53,34;34,1,3,4}
{TRUE,FALSE,FALSE,TRUE;TRUE,TRUE,FALSE,FALSE;FALSE,TRUE,TRUE,TRUE}
となり
つまりTRUEと相対的な位置にある
1
5
4
4
1
3
4
それをSMALL関数で小さい順に並び替えているんですよね？
つまり
1
1
3
4
4
4
5
です。
それでもしもそれに対応する「か」とか「イ」を取得したいのであれば
方法はいくつかあると思いますが、やはりINDEX関数等を使うことになると思います。
ところで、提案ですが、先ず式は実際のExcelからコピーして貼り付けられます。
図も実際のエクセルからコピーできます。
文章よりも図で「前」と「後」を示された方がよりよい回答を得られると思います。
要するに「何がしたいのか？」です。
そのとき一番大事なことは、簡潔にわかりやすく実際のレイアウトでなくてもいいです。
ミーママさん自身が後で応用できる範囲であれば後で応用されればいいわけですから。。
私が思いますのに、今、ミーママさんがなさりたいことは必ず全く同じじゃなくても
類似は過去ログにあると思いますよ。で、その中から質問の仕方などを真似されて
尚且つご自身のわからない部分だけを新たに質問されれば必ずいい回答が得られると思　　いますよ
長々と書いてしまいましたが、どうですか？
もう一度、熟考されてみませんか？
あっ、くれぐれもご気分を悪くなさらないでくださいねv(=∩_∩=)v
（SoulMan）

 SoulManさん　ご指導ありがとうございます！！
そうですね。確かにExcelのデータからコピーした方が簡単で正確に表せますよね。
今まで、いちいち入力していたので、ますます分かりにくくなってました。
確かにこの質問のやり方では正確に伝わらないですよネ。
もう1度最初から質問をやり直したいと思います。よろしくお願いします。

　まず、ある商品の在庫表があって、ここでは小さくまとめて例をあげます。
　　　「ａ表」とします。
　　　　

 　　　コードNO	ＳＳ	Ｓ	Ｍ	Ｌ

	01       50       50       50        50
	02	50	50	50	50			
	03	50	50	50	50
	04	50	50	50	50
　　　　 05	50	50	50	50
	06	50	50	50	50
	07	50	50	50	50
	09	50	50	50	50
	10	50	50	50	50

一番左の列がコードNOで、サイズが「SS，Ｓ，Ｍ，Ｌ」とあり、それぞれ枚数を表示しています。ここでは分かりやすくすべて｢50」にします。

　そして、この中から使用する商品の「コードNO」と「サイズ」と「枚数」を使用するたびに入力していって表示したものが下の表です。
　　　「ｂ表）とします。

　この使用した商品の数が、上の在庫表からそれぞれいくら減っていっているか、つまり、現在の残りの在庫数を表示した表が下の表です。
　　　「ｃ表」とします。

 　　		ＳＳ	Ｓ	Ｍ	Ｌ	
 	01	50	50	50	50	 
	02	2	50	43	37	
 	03	50	50	50	50	
 	04	50	45	50	50	
 	05	50	5	50	50	
 	06	50	50	50	50	
 	07	50	50	2	42	
 	08	50	50	50	50	
 	09	50	50	5	50	
 	10	46	50	50	50

　
　この表には、数量を表示しているセル全体に下の数式を入力しているので、「ｂ表」のデータを1行1行入力するたびに、自動的に使用した商品の数量が減っていって、その残りの数量が表示されるようになっています。

 ｛=F14-SUM(IF(($A$2:$A$10=$E2)*($B$2:$B$10=F$1),$C$2:$C$10))｝

そして、残りの在庫が少なくなってきた商品を分かりやすくするために、「５」以下の数量に「赤」が表示されるように条件付き書式を設定しています。
ですから、表を見れば残りの少ない商品が何なのか分かることは分かるのですが、もっと分かりやすく「５」以下の数量の「コードNO」と「サイズ」と数量を別に表示させたいと思っています。（やりたいのはコレです。）
　今のところ、「５」以下の「数量」だけは下の数式を入力して抜き出せました。

 ｛=IF(ISERROR(SMALL(IF($F$2:$I$11<=5,$F$2:$I$11,""),ROW()-1)),"",SMALL(IF($F$2:$I$11<=5,$F$2:$I$11,""),ROW()-1))｝

 　コード	　　　サイズ	数量　　　
 		　　　　　2 
 		　　　　　2
 		　　　　　5
 		　　　　　5

　ところが、この数量のそれぞれの「コードNO」と「サイズ」を出す数式がわからないのです。 SoulManさんが教えてくださったようにINDEX関数で色々考えてみたのですが、ｃ表が数式が入力しているので"循環参照"になるとかでうまくいきませんでした。どんな数式を入れればよいのでしょうか？
　それと、SoulManさんが2回も質問してくださったのに回答してなかった件ですが、　上の数式ですが、これは「｛｝」が付いているか付いていないかの違いということですよね？（もし他の箇所の違いだったらすみません）
はい。これは「｛｝」を付けています。実際はずした状態でやってみたら何も表示しませんでした。

　うわー。。。確かにSoulManさんがおっしゃったようにやると前回のより、説明の方もまとまった気がしてビックリしてます。みなさんにうまく伝わったかどうかは分かりませんが。。。
　これからもよろしくお願いします！！

　　　　　　　　　　（ミーママ）

 今度はうまく伝わってます(^_^A;
 ただ1列に並べる方法は判りませんでした。
 以下でよければお試しください。

 	 A	 B	 C	 D	 E
 1		ＳＳ	Ｓ	Ｍ	Ｌ
 2	01	50	50	50	50
 3	02	2	50	43	37
 4	03	50	50	50	50
 5	04	50	45	50	50
 6	05	50	5	50	50
 7	06	50	50	50	50
 8	07	50	50	2	42
 9	08	50	50	50	50
 10	09	50	50	5	50
 11	10	46	50	50	50
 12					
 13		02ＳＳ	05Ｓ	07Ｍ	
 14				09Ｍ

 B13セル=IF(ISERROR(SMALL(IF((B$2:B$11<=5),ROW(B$1:B$10),""),ROW(A1))),"",INDEX($A$2:$A$11,SMALL(IF((B$2:B$11<=5),ROW(B$1:B$10),""),ROW(A1)),1)&B$1)
 配列数式のため　Shift+Ctrl押しながらEnterです。

 あ！読み直したら数量もだ＿/￣|○ il||li
 （川野鮎太郎）

 すみません。作業列を使っちゃいましたぁ(^^;; ヒヤアセ
でも、これって複数あったら駄目じゃないのかなぁ？？
v(=∩_∩=)v
（SoulMan）
http://ryusendo.no-ip.com/cgi-bin/upload/src/up0129.xls
あのぅ、、余計なお世話かもしれませんが、エクセル君はリスト形式にしてあげると
その実力を如何なく発揮します。
ということで、、わたしなら、こうします。みたいな感じでご参考までに
よかったら、、どうぞv(=∩_∩=)v
http://ryusendo.no-ip.com/cgi-bin/upload/src/up0130.xls
（SoulMan）

 出来るかどうかやってみました＿/￣|○ il||li
 限界＿/￣|○ il||li
http://skyblue123.hp.infoseek.co.jp/Excel/zaiko.xls
 （川野鮎太郎）

 参った＿/￣|○ il||li
（SoulMan）

 うわぁー！川野鮎太郎さん！SoulManさん！望み通りの完璧なご解答をくださり本当にどうもありがとうございます！！
このお礼の言葉を書き始めて2時間がたちました。
お二人のものすごい解答が次々と出てきて大変感心してしまいました！
そして、望み通りの解答をいただけたということはもちろんのこと、
自分でも何がなんだか分からないような説明を理解していただけたということに
すごく感激してしまいました！！
途中泣きそうにもなりましたが、どうにかここまでこぎつけて本当に良かったです。。。
大変勉強になりました。いろんな計算のやり方があるんだなあということもわかりました。
お二人の解答の２つめまでは、今やっと全体の数式の意味が漠然と分かりました。
（そのあとの解答は数式の中身を見ただけで卒倒しそうだったので、のちほどゆっくりおちついてからしっかり勉強します！）
何箇所か分からない点があったので質問させていただきます。
　
　まず、SoulManさんが最初に作ってくださったエクセルファイルの解答の

　=(MIN(F2:I2)<=5)*MIN(F2:I2)

　の数式の　「MIN(F2:I2)<=5」の部分が良く分からないんですけど、というかこれで計算が成り立っているということ自体がすごく不思議なかんじなんですけど。。。

　それから

=IF(SUM(IF($J$2:$J$11>0,1))<ROW(A1),"",INDEX($E$2:$E$11,MOD(SMALL(IF($J$2:$J$11>0,($J$2:$J$11)*10000+ROW($A$1:$A$9)),ROW(A1)),10000)))

　の部分の「10000」を使用してあるのはリストの数が、だいたい10000以下ぐらいのかんじとしてとりあえず「10000」としてある、という解釈の仕方でいいんでしょうか？

　それから
「でも、これって複数あったら駄目じゃないのかなぁ？？」
とありましたが何が複数あったらだめなんでしょうか？

　それから、これはここの欄への文字の入力の仕方の質問なんですが、例えば上の方に入力してある下の表ですが

	 A	 B	 C	 D	 E
 1		ＳＳ	Ｓ	Ｍ	Ｌ
 2	01	50	50	50	50
 3	02	2	50	43	37
 4	03	50	50	50	50
 5	04	50	45	50	50
 6	05	50	5	50	50
 7	06	50	50	50	50
 8	07	50	50	2	42
 9	08	50	50	50	50
 10	09	50	50	5	50
 11	10	46	50	50	50
 12					
 13		02ＳＳ	05Ｓ	07Ｍ	
 14				09Ｍ

　この表は多分コピーしてあると思うんですけど1番左側の行番号などは後から１つ１つ入力してあるのですか？

　それからどうでもいいことですが、この掲示板に入力している文字が大きくなったり小さくなったりするのはナゼでしょう？

　と、とりあえず今回の質問は以上です。これから残りの解答のファイルをみっちり勉強させていただきたいと思っています。いつのことになるかわかりませんがまた、そのうち質問させていただきたいと思います。
　川野鮎太郎さん、SoulManさん、本当にどうもありがとうございましたあ！！！！！
　
　　　　はぁー。。。なんか名残惜しい・・・！！
　　　　　　　　　　　　　　　　　（ミーママ）

 ＞1番左側の行番号などは後から１つ１つ入力してあるのですか？

 一番左はワークシート上でA列を追加して、半角スペース1として下方にコピーしています。

 大きい文字と小さい文字は、行頭に半角スペースが入っているかどうかです。

 （川野鮎太郎）

　さっそくのご解答ありがとうございます！

そうか、左のデータは最初に作っといてコピーすればいいんですね。

　あああ

 あああ

　ほんとだ。確かにスペースの大きさで文字の大きさが変わるんですね！知らなかった。。。。
　
　すみません。遊んでしまいました。。。
　　　　　　　　　　　（ミーママ）

 参ったしたままじゃ悔しいので
A　　　　B　　　　C　　　　D　　　　E　　　　F　　　　　G　　　　H　　　　I
	ＳＳ	Ｓ	Ｍ	Ｌ		コード	サイズ	数量
01	50	50	50	50		02	Ｓ	4
02	50	4	50	50		03	ＳＳ	5
03	5	50	50	5		03	Ｌ	5
04	50	50	50	50		05	Ｍ	5
05	50	50	5	50		07	ＳＳ	3
06	50	50	50	50		07	Ｓ	2
07	3	2	50	3		07	Ｌ	3
08	50	50	50	50		09	Ｍ	5
09	50	50	5	50				
10	50	50	50	50				
G2に
=IF(COUNTIF($B$2:$E$11,"<=5")<ROW(A1),"",
INDEX($A$2:$A$11,SMALL(IF($B$2:$E$11<=5,ROW($B$1:$E$10)),ROW(A1))))
H2に
=IF(COUNTIF($B$2:$E$11,"<=5")<ROW(A1),"",
INDEX($B$1:$E$1,1,MOD(SMALL(IF($B$2:$E$11<=5,ROW($A$2:$D$11)*10000+COLUMN($A$1:$D$10)),ROW(B1)),10000)))
I2に
=IF(COUNTIF($B$2:$E$11,"<=5")<ROW(A1),"",
SUM(IF(($A$2:$A$11=G2)*($B$1:$E$1=H2),$B$2:$E$11)))
で、どうでしょう？
（SoulMan）

 >だいたい10000以下ぐらいのかんじとしてとりあえず「10000」としてある、という解釈の仕方でいいんでしょうか？
うぅ～～ん！！違います。
重みを付けているんです。頑張って解読してください。^^;
 >とありましたが何が複数あったらだめなんでしょうか？
複数あってもいいようにしましたからね^^;
ではでは、頑張ってください。
v(=∩_∩=)v
（SoulMan）

 ぉお!!(ﾟﾛﾟ屮)屮
 (*^^)//。・:*:・°'★,。・:*:♪・°'☆パチパチ
 （川野鮎太郎）

 ありがとっう～～～～v(=∩_∩=)v
ほめられるとすぐ調子にのっちゃう(^^;; ヒヤアセ
=INT(RAND()*(15-1+1))+1
これを
=INT(RAND()*(10-1+1))+1
なんかにすると抽出される確立があがるから、
もっと、ばんわんばんわんしましゅ
F9キーを押してばぁわんばぁわぁんしちゃってくらはい
http://ryusendo.no-ip.com/cgi-bin/upload/src/up0131.xls
v(=∩_∩=)v
（SoulMan）

コメント返信：

[ 一覧(最新更新順) ]

[[20041103162245]] 『３のところのウのくが抜き出せません』（ミーママ） ページの最後に飛ぶ

[[20041103162245]]　『３のところのウのくが抜き出せません』（ミーママ）　ページの最後に飛ぶ