Results of 1 - 1 of about 19633 for 20�����������������������... (0.004 sec.)
- [[20141104172552]]
- #score: 2681
- @digest: ffbd3c2207db1475a42397b5b21ee329
- @id: 66544
- @mdate: 2015-02-23T08:34:33Z
- @size: 17775
- @type: text/plain
- #keywords: cdim (19427), 重量 (14990), 品b (12829), idim (11761), 品a (9576), ゼロ (7024), 「ゼ (6789), 列デ (6518), 候補 (5587), のno (5466), の和 (5369), 商品 (5044), が9 (4874), 小値 (4570), 最小 (3355), 2014 (3239), 列番 (3168), 論理 (3071), gobgob (2727), index (2693), iferror (2576), 行番 (2514), （go (2101), 番号 (2009), 回避 (1981), 列数 (1898), 未満 (1887), 連番 (1771), 省略 (1765), 配列 (1641), 稲葉 (1608), 返す (1594)
- 『1つ目と2つ目のデータの合計が規格内となる様に2つ目のデータを選択したい』（みと）
A列に連番1〜10、B列に10個のデータがあります。(A列とB列は連携) C列に連番1〜10、D列に10個のデータがあります。(C列とD列は連携) その時、A1+X1=10〜20となる様に、X1に当てはまるデータを D列から選択し、E1にC列の連番を、F1にD列の数字を表示したいです。 その後、同様にE2以降も表示させたいのですが、E1、F1で選択した 数字は除外した残りのC列、D列のデータから、当てはまるデータを 選択し、E2、F2以降に表示したいです。同じデータが存在する場合 があるため、連番で管理しています。 ＜ 使用 Excel:Excel2007、使用 OS:Windows7 ＞ ---- 条件を整理。 1:A列とX列を足して 10〜20の値にする。 2:X列はD列からデータをもってくる。 3:上位行で選択されたD列数値は選択できない。 ってこと? ぜんぜんイメージわかないんだけど。。 けど、A1が1でD列に 9〜19 が連番であるなら、どれを持ってくるの? 範囲が10〜20なんてあいまいなんで判断材料がないと難しいね。 （GobGob） 2014/11/04(火) 19:26 ---- 説明不足で申し訳ありません。 下記のイメージで伝わるといいのですが。 商品A 商品B 商品Aに対する商品Bの組合せ(商品A+商品B=9〜11g) No. 重量 No. 重量 No. 重量の和 1 5g 1 3g 2 10g 2 6g 2 5g 1 9g 3 7g 3 4g 3 11g 4 8g 4 5g - - 5 6g 5 6g - - 1)商品AのNo.1に対して、商品Bの何れかとの和が9〜11gの範囲内にあり、その候補 の内、No.が小さいものを優先して、そのNo.と重量の和を表示。 (上記の例:商品AのNo.1に対する商品Bの候補はNo.2〜5となり、その内一番小さい No.2と重量の和を表示） 2)商品AのNo.2に対して、上記1)で既に使用した商品BのNo.2を除き、商品Bの何れか との和が9〜11gの範囲内にあり、その候補の内、No.が小さいものを優先して、その No.と重量の和を表示。 (上記の例:商品AのNo.に対する商品Bの候補はNo.1〜4となるが、既にNo.2は使用 済のため除き、残ったNo.1、3、4の内、No.が小さいものを優先して、そのNo.と 重量の和を表示) 3)商品Aに対する商品Bの重量の和が9〜11gの範囲外である場合、組合せのNo.、重量 の和ともに"-"を表示。 (上記の例:商品AのNo.4に対する商品Bの候補はNo.1、3となるが、既に商品AのNo.3 までで使用済であるため、候補値がなく、その他の重量では範囲外であるため、"-" を表示。) 以上です。 宜しくお願い致します。 （みと） 2014/11/05(水) 01:02 ---- 商品A No5 は 6gなら 商品B No4 が対象となるよね? A B C D E F G 1 商品A 商品B 2 No. 重量 No. 重量 No. 重量の和 3 1 5 1 3 2 10 4 2 6 2 5 1 9 5 3 7 3 4 3 11 6 4 8 4 5 - - 7 5 6 5 6 4 11 8 F3 =IF(B3="","",IFERROR(INDEX(C$3:C$20,MIN(INDEX(((B3+D$3:D$20<9)+(B3+D$3:D$20>11)+COUNTIF(F$2:F2,C$3:C$20))*10^16+C$3:C$20,))),"-")) G3 =IF(F3="","",IF(F3="-","-",SUMIF(C$3:C$20,F3,D$3:D$20)+B3)) F3:G3 下へコピー。 ※A:D列 20行目まで対象 （GobGob） 2014/11/05(水) 09:52 ---- ありゃりゃ。。。。 不十分だね。。。 F3だけ見直し。 =IF(B3="","",IFERROR(INDEX(C$3:C$20,TEXT(MIN(INDEX(((B3+D$3:D$20<9)+(B3+D$3:D$20>11)+COUNTIF(F$2:F2,C$3:C$20))*10^16+C$3:C$20,)),"0;;")),"-")) TEXT使うならINDEXいらないねw （「-」処理するのにわざわざINDEX使ってたw） =IF(B3="","",IFERROR(TEXT(MIN(INDEX(((B3+D$3:D$20<9)+(B3+D$3:D$20>11)+COUNTIF(F$2:F2,C$3:C$20))*10^16+C$3:C$20,)),"[=0]#;[<20]0")*1,"-")) （GobGob） 2014/11/05(水) 11:42 ⇒ 11:50修正 ---- GobGobさん有難うございました。無事出来ました! お忙しいところ恐縮ですが、出来ましたら 構文のご説明をお願いできないでしょうか。 途中までは何となく分かりそうなんですが、 B3+D$3:D$20<9、*10^16、"[=0]#;[<20]0")*1等が さっぱり分かりません。 お手数をおかけしますが、ご教授願います。 （みと） 2014/11/05(水) 20:26 ---- まず。これから ＞B3+D$3:D$20<9、*10^16、 条件を整理。 1:B列データに対し、加算して9以上11以下になる数値をD列から探し、C列の番号を返す。 2:C列の番号は上から順番にE列に返す。 3:以降はE列既出のC列番号は除いて1〜2の処理を繰り返す。 これを条件として数式(配列数式)を組むと =(B3+D$3:D$20>=9)*(B3+D$3:D$20<=11)*(COUNTIF(F$2:F2,D$3:D$20)=0) となるんす。 これは 「B3+D列のデータが9以上」×「B3+D列のデータが11以下」×「F2から一つ上のF列セル範囲にD列データがなし（ゼロ）」 という数式っす。 んで、今回の例でいいと ={0;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0}*{0;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0}*{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1} の処理となり、掛け算なんで ={0;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0} が、返されますわ。 各論理式を掛け算するのはAND処理するためっす。（ANDはその名の通り論理「積」言います） この数式にC列を掛け算すると。 ={0;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0}*{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18} ={0;2;3;4;5;0;0;0;0;0;0;0;0;0;0;0;0;0} になりますわ。 んで、今回は「最小値」を返すんだけど、この結果から最小値を返すと「ゼロ」を返してしまう。 。。。それはダメっすね。 なのでこの「ゼロ」を大きな数値（今回は10の16乗）にして「2」を返すようにするのに ANDでなくOR（論理「和」）で処理しちゃう。 =(B3+D$3:D$20<9)+(B3+D$3:D$20>11)+COUNTIF(F$2:F2,C$3:C$20) さっきのANDの考えを真逆にし、乗算を加算に、 「B3+D列のデータが9未満」+「B3+D列のデータが11より大」+「F2から一つ上のF列セル範囲にD列データがあり」 にしたら ={1;0;0;0;0;1;1;1;1;1;1;1;1;1;1;1;1;1}+{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}+{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0} の処理となり、足し算なんで ={1;0;0;0;0;1;1;1;1;1;1;1;1;1;1;1;1;1} が、返されてさっきの ANDのときと真逆になります。（あくまで「ゼロか否か」の観点で） この結果に10の16乗を掛け算すると1の所が全て10の16乗になり、ゼロは当然掛け算なんでゼロになる。 ={1;0;0;0;0;1;1;1;1;1;1;1;1;1;1;1;1;1}*10^16 ={10^16;0;0;0;0;10^16;10^16;10^16;10^16;10^16;10^16;10^16;10^16;10^16;10^16;10^16;10^16;10^16} この結果にC列数値を足し算すると、ゼロのところがC列数値。それ以外は10の16乗+C列数値になる。 …これで最小値がゼロにならなくて済む。ってことっす。 （GobGob） 2014/11/05(水) 21:08 ---- 次にこれ >"[=0]#;[<20]0" これはTEXT関数の第2引数っす。 さっき説明した配列数式で、もし仮にB列が「9」、+D列で全て11より大きくなると。 10^16とゼロ（D列空白の所のC列）を返すんですな。 こうなるとゼロが最小値になるので具合が悪い。 んで、TEXT関数でゼロを空白文字列に変換させてますわ。 あと、"[=0]#;[<20]0" ←この表示形式は「0より大〜20未満は数値、それ以外は空白」にするため。 ※なぜ20未満なのかは。。。範囲3〜20行未満なんで「これくらいでいいやろ」で設定してしもたw。実際はC列最大値+1でいいよ。 >*1 TEXT関数の結果を*1にして文字列結果を「数値」に変換。 0より大〜20未満は数値に変換。それ以外は空白文字列×1でエラー。 エラーの時はIFERRORで「-」を返す。 こんな感じっすかね。 まぁ、CTRL+SHIFT+ENTER回避で面倒くさいことしてるのは確かですがw （GobGob） 2014/11/05(水) 21:23 ---- GobGobさんへ 詳細なご説明を頂き、有難うございます。 これから、しっかり読んで勉強します。 また、分からないところがあると思いますが、 その際は、穏便にご教授頂けると幸いです。 この度はご協力頂きまして、有難うございました。 （みと） 2014/11/05(水) 21:33 ---- ＞んで、今回は「最小値」を返すんだけど、この結果から最小値を返すと「ゼロ」を返してしまう。 ＞。。。それはダメっすね。 ＞ なのでこの「ゼロ」を大きな数値（今回は10の16乗）にして「2」を返すようにするのに ＞ANDでなくOR（論理「和」）で処理しちゃう。 いつも前半のところで悩んでいたので、（MINで0が返ってきちゃう） 大変勉強になりました。 ありがとうございます! （稲葉） 2014/11/06(木) 09:16 ---- だいぶ理解できましたが、INDEXのところが いまいち分かりません。 あと、CTRL+SHIFT+ENTER回避をするのはなぜでしょうか。 もう少し教えて頂けると幸いです。 宜しくお願いします。 （みと） 2014/11/08(土) 19:06 ---- B列が9以上になると、F列の計算結果が"-"となるのですが、 どうすれば良いのでしょうか。 （みと） 2014/11/09(日) 22:27 ---- >B列が9以上になると、F列の計算結果が"-"となるのですが、 例でいうと、B列が9だと全て12以上になるんだけど。。。 9〜11が範囲だよね? なお、B:D列とも数量「0」は考慮してまへん（ありえない思て） >INDEX INDEXは「CTRL+SHIFR+ENTER回避」のため。 ((***)+(***)+COUNTIF(***))*10^16+・・・の結果をINDEXを使ってENTERだけで 配列データとして返すため。 INDEXはC+S+Eしなくても配列データとして返してくれるからね。 >CTRL+SHIFT+ENTER回避をするのはなぜでしょうか。 なぜ、いわれても。。。 みとさん以外のヒトが配列数式知らなくて、なんかの拍子に数式編集してしもたらアカンし。 まぁ、保険みたいなモンですわ。 C+S+Eでいいなら =IF(B3="","",IFERROR(TEXT(MIN(IF((B3+D$3:D$20>=9)*(B3+D$3:D$20<=11)*(COUNTIF(F$2:F2,C$3:C$20)=0),C$3:C$20)),"[=0]#;[<20]0")*1,"-")) でもOK。（コレはORでなくAND処理） （GobGob） 2014/11/10(月) 08:19 ---- 何度も本当にスイマセン・・・ B列が9で、D列が1の場合はどうでしょうか。 どうしてもF列が"-"になってしまいまして。 あと、INDEXですが通常ですとINDEX(範囲、行番号、列番号)と いう形だと思いますが、ここではINDEX(((B3+…C$20,)という記載で 行番号、列番号とも省略しているという事でよろしいでしょうか。 （みと） 2014/11/10(月) 09:21 ---- あら、ホンマですね。。。 条件追加。 =IF(B3="","",IFERROR(TEXT(MIN(INDEX(((B3+D$3:D$20<9)+(B3+D$3:D$20>11)+(C$3:C$20="")+COUNTIF(F$2:F2,C$3:C$20))*10^16+C$3:C$20,)),"[=0]#;[<20]0")*1,"-")) （GobGob） 2014/11/10(月) 09:46 ---- ＞あと、INDEXですが通常ですとINDEX(範囲、行番号、列番号)と ＞いう形だと思いますが、ここではINDEX(((B3+…C$20,)という記載で ＞行番号、列番号とも省略しているという事でよろしいでしょうか。 INDEXの書式 配列形式 INDEX(配列, 行番号, [列番号]) セル範囲形式 INDEX(範囲, 行番号, [列番号], [領域番号]) 今回は「配列形式」。何をもって「通常」なのかわかりまへんが。 厳密には「列番号は省略」「行番号は『表記』を省略、処理はしてる」ってことですな。 今回は INDEX(配列,) となって「,」のあと「行番号の表記」を省略。 省略すると「FALSE = 0」 の処理をする。 INDEXは 行列番号を「0」にすると 第一引数の範囲を1データでなく「配列」として複数範囲で返す。 （GobGob） 2014/11/10(月) 09:57 ---- 「通常」という表現は誤りですね。申し訳ありません。 INDEXの件、勉強になりました。 もうひとつ、条件追加で(C$3:C$20="")を追加すると、 なぜ問題が解消されるのでしょうか。 何から何まで聞いてしまい恐縮ですが、宜しくお願いします。 （みと） 2014/11/10(月) 10:18 ---- 当初の数式だと、B列が9でD列に1〜3があっても ゼロが帰ってきてしまう。 なんでか?というと、当初の論理和の数式ではC列空白（=D列空白 =0）が最小値となって ゼロが返ってくるんで、C列データなしだと10^16を返すようにすれば最小値「ゼロ」を回避できる。 ってことっす。 なので、TEXTいらなくなるねw。 =IF(B3="","",IFERROR(INDEX(C$3:C$20,MIN(INDEX(((B3+D$3:D$20<9)+(B3+D$3:D$20>11)+(C$3:C$20="")+COUNTIF(F$2:F2,C$3:C$20))*10^16+C$3:C$20,))),"-")) ※コレだとC列データあり、D列「ゼロ」にも対応できるよ。 （GobGob） 2014/11/10(月) 13:06 ---- 有難うございます。やっと分かりました。 ちなみに、C列データあり、D列「ゼロ」にも対応とありますが、 C列にデータがあれば、D列のデータ有無にかかわらず、処理される様にも 思えますが、どこが違うのでしょうか。 （みと） 2014/11/10(月) 14:06 ---- ＞C列にデータがあれば、D列のデータ有無にかかわらず、処理される様にも 思えますが、どこが違うのでしょうか。 違いはありまへん。 C列有り。D列なし ⇒ D列。ゼロとして処理される。 なのでD列「0」有りでもOK ってことっす。 （GobGob） 2014/11/10(月) 14:16 ---- C列が1からではなく、2から始めると うまく処理できないのですが、どうしたら良いでしょうか。 この式ですと、うまくいくのですが =IF(B3="","",IFERROR(TEXT(MIN(INDEX(((B3+D$3:D$20<9)+(B3+D$3:D$20>11)+(C$3:C$20="")+COUNTIF(F$2:F2,C$3:C$20))*10^16+C$3:C$20,)),"[=0]#;[<20]0")*1,"-")) この式では、うまく処理できません。 =IF(B3="","",IFERROR(INDEX(C$3:C$20,MIN(INDEX(((B3+D$3:D$20<9)+(B3+D$3:D$20>11)+(C$3:C$20="")+COUNTIF(F$2:F2,C$3:C$20))*10^16+C$3:C$20,))),"-")) （みと） 2014/11/10(月) 16:56 ---- ))*10^16+C$3:C$20 を ))*10^16+ROW(C$3:C$20)-2 に。 または、TEXT関数案で。 ※もう、条件追加ないよね? 小出しはヤだよ。 （GobGob） 2014/11/10(月) 17:08 ---- ありがとうございます。大変助かりました! （みと） 2014/11/10(月) 17:47 ---- GobGobさんへ 本プログラムは今でも大活躍です!大変助かっています。 今回の条件は商品Bの候補の中から、Noが小さいものから順に 選定していますが、そうすると組合せができないものが発生し ています。Noが小さいものから選定する、という条件を外して より多くの組合せができるような計算にしたいのですが、 どうしたらよろしいでしょうか。 （みと） 2015/02/20(金) 01:03 ---- その条件外すと、すべての条件を計算する必要があってナップサック問題だっけ? になると思います。 http://ja.wikipedia.org/wiki/%E3%83%8A%E3%83%83%E3%83%97%E3%82%B5%E3%83%83%E3%82%AF%E5%95%8F%E9%A1%8C その場合、ソルバーを使って近似値を求めるのが適切かと思います。 （稲葉） 2015/02/20(金) 08:40 ---- この場合組み合わせの最大数を求めるから、ナップサックと違った! でも似たようなものかと思われます。 （稲葉） 2015/02/20(金) 08:47 ---- 対象の数が多くなるにつれ、計算量が急激に膨らんで行きます。 まず、A商品とB商品、それそれの最多個数を幾つで考えればいいのか、 それを提示できませんか? (後で変更は無し） （半平太） 2015/02/20(金) 10:23 ---- なるべく多く商品Bを使う、ということですね。 最適解を求めるのは、数式では無理があるので、マクロでしょうか。それでも難しいなぁ。 とりあえず、だた〜っ、と書いてみた例。（動作未確認） Sub test() Dim i As Long Dim j As Long Dim k As Long Dim iMax As Long Dim jMax As Long Dim iw As Long Dim iw1 As Long Dim iw2 As Long Dim iDim() As Long Dim cDim() As String iMax = Cells(Rows.Count, "A").End(xlUp).Row - 3 jMax = Cells(Rows.Count, "C").End(xlUp).Row - 3 ReDim iDim(iMax) ReDim cDim(iMax) Range("F3:G" & iMax + 3).ClearContents For i = 0 To iMax iw1 = Cells(i + 3, "B").Value - 1 iw2 = Cells(i + 3, "B").Value + 1 For j = 0 To jMax iw = 10 - Cells(j + 3, "D").Value If iw1 <= iw And iw <= iw2 Then iDim(i) = iDim(i) + 1 cDim(i) = cDim(i) & "," & j End If Next j cDim(i) = cDim(i) & "," Next i sLoop: For i = 1 To iMax + 1 For j = 0 To iMax If iDim(j) = i Then iw = Mid(cDim(j), 2, InStr(2, cDim(j), ",") - 2) Cells(j + 3, "F").Value = Cells(iw + 3, "C").Value Cells(j + 3, "G").Value = Cells(iw + 3, "D").Value + Cells(j + 3, "B").Value iDim(j) = 0 cDim(j) = "" For k = 0 To iMax iw1 = InStr(cDim(k), "," & iw & ",") If 0 < iw1 Then cDim(k) = Replace(cDim(k), "," & iw & ",", ",") iDim(k) = iDim(k) - 1 End If Next k GoTo sLoop End If Next j Next i End Sub ロジック的には、まずAに対して有効なBを全て列挙しcDimにセット。 列挙した数が少ないものから代入していく感じです。代入したものは、候補から消してく。 （???） 2015/02/20(金) 10:32 ---- 現状の計算式でも、データを大きい順に並び替えてしまえば、そのまま使えたりしそうですね。 （???） 2015/02/20(金) 10:59 ---- ???さんと似たような考え方でやってみた F列以降に結果を出力 Sub test() Dim dicA As Object Dim dicB As Object Dim ans As Object Dim tbl Dim i As Long Dim j As Long Dim uMin As Long Dim uMax As Long Dim dMin As Long Dim dMax As Long Dim x As String Dim y As String Dim dlm As String dlm = " " uMin = 9 uMax = 11 dMin = Application.Min(Range("B:B")) dMax = Application.Max(Range("B:B")) Set dicA = CreateObject("Scripting.Dictionary") Set dicB = CreateObject("Scripting.Dictionary") Set ans = CreateObject("Scripting.Dictionary") tbl = Range("A1").CurrentRegion.Value For i = 3 To UBound(tbl, 1) SetData dicA, tbl(i, 2), tbl(i, 1), dlm SetData dicB, tbl(i, 4), tbl(i, 3), dlm Next i For i = dMax To dMin Step -1 x = CStr(i) If dicA.exists(x) Then For j = uMax - i To uMin - i Step -1 y = CStr(j) Do While dicA.exists(x) And dicB.exists(y) ans.Add RmDt(dicA, x, dlm) & "-" & RmDt(dicB, y, dlm), i + j Loop Next j End If Next i Range("F3").Resize(ans.Count).Value = Application.Transpose(ans.keys) Range("G3").Resize(ans.Count).Value = Application.Transpose(ans.items) Range("H3").Resize(dicA.Count).Value = Application.Transpose(dicA.items) Range("I3").Resize(dicB.Count).Value = Application.Transpose(dicB.items) End Sub Private Function SetData(ByRef dic, ByVal k As String, ByVal i As Long, ByVal dlm As String) If dic.exists(k) Then dic(k) = dic(k) & dlm & "[" & i & "]" Else dic.Add k, "[" & i & "]" End If End Function Private Function RmDt(ByRef dic, ByVal x As String, dlm As String) As String Dim itm As String itm = Split(dic(x), dlm)(0) dic(x) = Trim(Replace(dic(x), itm, "")) If Len(dic(x)) = 0 Then dic.Remove (x) End If RmDt = itm End Function ???さん また敬称つけ忘れました。 大変失礼いたしました。 2/23 17:34 （稲葉） 2015/02/20(金) 12:00 ---- 稲葉さん、半平太さん、???さん 様々なアイデアを頂き、有難うございます。 まったく理解できていませんが、これから トライをしてみたいと思います。 また、分からない事があるかと思いますが、 その際はよろしくお願い致します。 （みと） 2015/02/23(月) 17:24... - http://www.excel.studio-kazu.jp/wiki/kazuwiki/201411/20141104172552.txt - [detail] - similar
PREV
NEXT
Powered by Hyper Estraier 1.4.13, with 97024 documents and 607976 words.
訪問者：