[[20100508114947]] 『一つでも条件に合えば全て同じ値』(ゆき夫) ページの最後に飛ぶ

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

 

『一つでも条件に合えば全て同じ値』(ゆき夫)

 お世話になっております。
 [[20100329192844]] 『重複データの検索方法を教えていただきたいです』(ゆき夫)
 では追記を拝見したのがかなり遅く、お礼をしそびれたことをお詫びいたします。
 今回も宜しくお願いします。

 下記のような表があり(実際は10000行、150列ほどの表)
 ある従業員コードで一つでも売上が0の時、その人のF列には全て1を、
 売上に0がなければその人のF列は全て0を入れるようにしたいのです。

    [A]   [B]   [C]  [D]  [E]  [F]
 [1]  従業員ID  本支店  他項目 売上  他項目  結果
 [2]     123     100             4             0
 [3]     123      10             1             0
 [4]     123      35             7             0
 [5]     223     100             2             1
 [6]     223      22             3             1
 [7]     223      43             0             1
 [8]     223       6             2             1
 [9]     323     100             3             0
 [10]    323      32             6             0
 [11]    323       2             1             0
 [12]    423     100             7             1
 [13]    423      23             0             1
 [14]    523     100             8             0
 [15]    523       8             5             0

 IF関数とSUMPRODUCT関数を使い、結果はうまく出ましたが
 データ量が多いせいか次の作業をする時に再計算が数十分かかってしまうため
 自力でコードを書いてみました。
 こちらも確かにきちんとした結果は出るのですが、知識がないので
 非常に無駄な箇所まで見に行ってしまいます。

 自分で書いたものはこちらです。これでもかなり奮闘しました。
 実際の表は項目名が3行目、データは4行目から始まります。
 従業員IDはC列、本支店区分はN列、売上はEB列、結果はEE列です。 

 Sub TEST1()

    Dim i As Long
    Dim ii As Long
    Dim ID As Integer
    Dim myRwMax As Long

    myRwMax = Range("C" & Rows.Count).End(xlUp).Row

    For i = 4 To myRwMax
        If Range("N" & i).Value = 100 Then
            ID = Range("C" & i)
        End If
        If Range("C" & i) = ID And Range("EB" & i) = 0 Then
            For ii = 4 To myRwMax      ←かなり無駄なことをしています
                If Range("C" & ii) = ID Then
                    Range("EE" & ii) = 1
                End If
            Next ii
        Else
            Range("EE" & i) = 0
        End If
    Next i

 End Sub

 本当はこのようにしたいのです。

 Sub TEST1()

    '4行目から最終行まで行います

        'N列が"100"だったら
        'その行"100"の行番号を取得し、
        'その行のC列(顧客コード)をIDに書き込んで
        'C列がIDである終わりの行番号を取得

            'C列がIDであるの最初の行から最後の行まで行います

                'C列がIDである中のEB列に一つでも0が入力されていたら
                'C列がIDであるすべての行のEE列に1を入力
                '一つも0がなければすべて0を入力

        '次のN列が"100"だったらに進む

 End Sub

 本来ならRedimやLBoundやUBoundを使うのでしょうか。(自力では使えません。)
 どうぞよろしくお願いいたします。
 OSはXPでExcelは2007使用です。

 作業列を使う方法
 G2:=N(D2=0)
 F2:=IF(SUMIF($A$2:$A$1000,A2,$G$2:$G$1000)>0,1,0)

 どうもありがとうございます。
 欲しい答えがすぐに出てきました。

 N関数は初めてお目にかかります。
 調べたところN関数とは『値を数値に変換する』というもののようですが 
 実は他の作業をするためにD列とは別に、売上が0の行は0、1以上の行は1が
 入っている列が既にあります。
 当然、N関数とはまったく逆の答えでが出ている状態で、でもこの既にある
 1と0を入れ替えるわけにはいかないのです。

 という事でこの行を活用しようと、教えていただいた=IF(SUMIF())の1と0を加工して
 =IF(SUMIF(C:C,C4,EC:EC)=0,1,0) 0の人は1、1の人は0にしてみたのですが
 結果は上から下まで全て0でした。
 でも、N関数とセットで使わなければならないものでもないような気もします。

 今、存在する列を活用する方法はありますでしょうか?
 何とぞよろしくお願い申し上げます。(ゆき夫)

 F2:=IF(A2="","",IF(MATCH(A2,$A$2:$A$10000,0)=ROW(A1),IF(COUNTIF($A$2:$A$10000,A2)>SUMIF($A$2:$A$10000,A2,$EC$2:$EC$10000),1,0),INDEX($F$2:$F$10000,MATCH(A2,$A$2:$A$10000,0))))

 素早いご対応をありがとうございました。

 この式で一つ教えてください。
 ROW(A1)というのが出てきましたが、これはこの表とは全く別物で
 単純に行番号を表すために使用していると考えていいのでしょうか?
 A1でなくてもB1やC1でも(結果は変わらなかったので)ということでしょうか? 

 宜しくお願いいたします。

 (ゆき夫)

 ROW(A1)=1,ROW(B1)=1,ROW(C1)=1  行番号の1番目
 検索するセル範囲が「$A$2:$A$10000」の1番目  A2で当たれば「1」(実際の行番号は「2」)・・・
 検索する範囲が「$A$2」からなので 「1」だけずれています。


 IF(MATCH(A2,$A$2:$A$10000,0)=ROW(A1),・・・・は
 「A2」が「A2」に当たれば(当たり前)、
 しかし「A4」が「A2」と同じなら
 IF(MATCH(A4,$A$2:$A$10000,0)=ROW(A3),・・・MATCH(A4,$A$2:$A$10000,0)=1なので、「FALSE」になります。


 ありがとうございました。

 式全体を理解するのがなかなか苦しいので、一つ一つ小分けにして結果を見ていたのですが
 =INDEX($F$2:$F$10000,MATCH(A2,$A$2:$A$10000,0))
 の部分だけで欲しい答えが出てきました。

 場合によっては
 =INDEX($F$2:$F$10000,MATCH(A2,$A$2:$A$10000,0))
 の部分だけではきちんとした答えが出ないことから、その前にCOUNTIFやSUMIFが入っているのだと思うのですが
 それはなぜなのでしょうか?
 自分なりにINDEX関数を調べてみましたが、配列形式とセル範囲形式(この場合は配列形式ですか???)
 との解説でかなり苦戦しております。

 どうぞよろしくお願いいたします。
 (ゆき夫)

 >=INDEX($F$2:$F$10000,MATCH(A2,$A$2:$A$10000,0))
 核心の計算は後にして、
 核心部分「IF(COUNTIF($A$2:$A$10000,A2)>SUMIF($A$2:$A$10000,A2,$EC$2:$EC$10000),1,0),」
 従業員の名前は何度もでてきますが、いわゆる「計算」は その名前が最初に出てきたところで行います。2回目以降は最初の名前のところでの計算結果を示します。「計算」は人数分だけですみます。
 この部分の説明。
 すでに説明していますが、
 IF(MATCH(A2,$A$2:$A$10000,0)=ROW(A1),・・MATCHは最初に当たった位置を示しますので(同じものがいくつあっても、)、 その位置とROW(A1)の数字が一致するということ(「TRUE」)はその名前が、最初に出てきたということで、計算します。
 もし「FALSE」なら、
 「INDEX($F$2:$F$10000,MATCH(A2,$A$2:$A$10000,0))))」で最初の値の位置に対応した「答え」(F列)を求めます。

 単純にある名前が、「0」か「1」なら、核心部分のみで計算できます。
  F2:=IF(COUNTIF($A$2:$A$10000,A2)>SUMIF($A$2:$A$10000,A2,$EC$2:$EC$10000),1,0)
 しかしこの式は、「COUNTIF」「SUMIF」のみですが、10000行のデータを対象にしています。同じ名前がいくつもあるのにすべてで計算するのは大変な無駄です。

 どなた様なのか・・・何度もありがとうございます。

 そうなのです。
 =IF(COUNTIF($A$2:$A$10000,A2)>SUMIF($A$2:$A$10000,A2,$EC$2:$EC$10000),1,0)
 の部分のみでも、また
 =INDEX($F$2:$F$10000,MATCH(A2,$A$2:$A$10000,0))
 の部分のみでも、両方とも同じように欲しい結果が出てきたので
 何だか自分の中で余計に混乱している状態です。

 もっと勉強してみます。 

 (ゆき夫)

 >=INDEX($F$2:$F$10000,MATCH(A2,$A$2:$A$10000,0))
 の部分のみでも
 ある名前が最初に出てきたところの F列に「A」、でも「あ」でも入力してみてください。この部分で正しい結果が出ないと、2つ目以降もそのおなじ値になります。「式」が消えますが、上下のセルをコピーすればよい。

コメント返信:

[ 一覧(最新更新順) ]


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