[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『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
詳細なご説明を頂き、有難うございます。
これから、しっかり読んで勉強します。
また、分からないところがあると思いますが、
その際は、穏便にご教授頂けると幸いです。
この度はご協力頂きまして、有難うございました。
(みと) 2014/11/05(水) 21:33
>んで、今回は「最小値」を返すんだけど、この結果から最小値を返すと「ゼロ」を返してしまう。 >。。。それはダメっすね。
> なのでこの「ゼロ」を大きな数値(今回は10の16乗)にして「2」を返すようにするのに >ANDでなくOR(論理「和」)で処理しちゃう。
いつも前半のところで悩んでいたので、(MINで0が返ってきちゃう) 大変勉強になりました。 ありがとうございます!
(稲葉) 2014/11/06(木) 09:16
>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
あと、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
もうひとつ、条件追加で(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列なし ⇒ D列。ゼロとして処理される。 なのでD列「0」有りでもOK
ってことっす。 (GobGob) 2014/11/10(月) 14:16
この式ですと、うまくいくのですが
=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
本プログラムは今でも大活躍です!大変助かっています。
今回の条件は商品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
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
???さんと似たような考え方でやってみた 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
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.