[[20141104172552]] 『1つ目と2つ目のデータの合計が規格内となる様に2ax(みと) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『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

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.