[[20140729152118]] 『条件合致で印を付す』(daddy) ページの最後に飛ぶ

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

 

『条件合致で印を付す』(daddy)

類似の投稿を参考にして何とかなると思い後回しにしていた
作業がなんともならず..ご助言よろしくお願いします。
問い)
A-C列にデータがあり、以下の条件に合致した場合にD列に印を付す。
条件)
同一名で点数を比較し、より新しい日付で上昇している時は「*」を、
下がっている時は「#」を付す(非該当は「-」)
説明)
・A列の名前は、順不同で重複あり
・C列の日付は、順不同で6桁数字に変換済み(2014/7/29→140729)
・データは4,000行〜10,000行程度

 	A	B	C	D
 1	名前	点数	日付	印
 2	a	80	140729	#
 3	c        60       140601	-
 4	a	90	140720	*
 5	c	80	140729   *	
 6	a	70	140706	#
 7	a	80	140504   -
 .......

作業の都度更新データを扱うため、フィルタ操作等は煩わしく、
できれば関数式"一発"でお願いします。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 私のない頭で絞り出した結果です・・・
 D1=IF(SUM(IF($C$2:$C$7<C2,IF($A$2:$A$7=A2,$C$2:$C$7,0),0))=0,"-",CHOOSE(SIGN(B2-SUMIFS($B$2:$B$7,$A$2:$A$7,A2,$C$2:$C$7,LARGE(IF($C$2:$C$7<C2,IF($A$2:$A$7=A2,$C$2:$C$7,0),0),1)))+2,"#","-","*"))

 Ctrl+Shift+Enterで配列数式に

 必要範囲にコピー
(稲葉) 2014/07/29(火) 16:21

稲葉さん

例示ではうまくいきました!(「D1=」は「D2=」ですよね?)
実データで再確認させていただきます。

想像以上に長い式で^^;、式の意味は..わかっていません_ _;

これからじっくり考えてみます。

先ずはお礼まで..ありがとうございました。

(daddy) 2014/07/29(火) 22:30


実データのB,C列には、ごく僅かですが「空白」や「文字」が
含まれていることが判明しました。

上記の場合は、データ上「無視」してエラー表示を回避する
ことはできるでしょうか?

CHOOSEをSIGNと合わせて、"うまいことするなぁ〜"と
感心しきりですが、オツムがついていけず..(苦笑)

・最初のSUMは、「非該当」を先に判定しているのだと思いますが
 どうしても必要なのでしょうか?
 例えば、後のCHOOSEの中ではムリなんでしょうか?
・SIGNの中のSUMIFSの解説お願いできませんでしょうか?
 (SUMしても結局は該当は1個??)

的外れで勝手いいますがよろしくお願いします。

(daddy) 2014/08/01(金) 10:39


 別案ですが。。。

 D2 =IF(COUNTIFS(A$2:A$7,A2,C$2:C$7,"<"&C2),TEXT(SUMPRODUCT((A$2:A$7=A2)*(C$2:C$7=MAX((A$2:A$7=A2)*(C$2:C$7<C2)*C$2:C$7))*B$2:B$7)-B2,"!#;!*;!-"),"-")
  
(GobGob) 2014/08/01(金) 11:51

 エラー処理忘れてた・・・

 >実データのB,C列には、ごく僅かですが「空白」や「文字」が 含まれていることが判明しました。 

 たとえば、 

	A	B	C	D
1	名前	点数	日付	印
2	a	80	140801	
3	b	100	140801	
4	a	-	140731	
5	b	110	140731	
6	a	60	140730	

 こんな場合、D2は「エラー」とするのか? 6行目と比較するのか?
 どっち?
 
(GobGob) 2014/08/01(金) 12:03

 一応、後者を考慮して・・・ダラダラしてるけど

 D2 =IFERROR(IF(COUNTIFS(A$2:A$7,A2,C$2:C$7,"<"&C2),TEXT(SUMPRODUCT((A$2:A$7=A2)*(TEXT(C$2:C$7,"0;;0;""999999""")*1=MAX(INDEX((A$2:A$7=A2)*(TEXT(C$2:C$7,"0;;0;""999999""")*1<C2)*TEXT(C$2:C$7,"0;;0;""999999"""),)))*TEXT(B$2:B$7,"0;;0;"""&B2&""""))-B2,"!#;!*;!-"),"-"),"-")
 
(GobGob) 2014/08/01(金) 12:07

 ありゃりゃ。エラー処理案。全然だめだね。。。。

 再考しますわー
 
(GobGob) 2014/08/01(金) 12:16

ありがとうございます。

 >こんな場合、D2は「エラー」とするのか? 6行目と比較するのか?どっち?

→D2は6行目と比較してください。 
 で、D4は「-(非該当)」か新規に「X」を付してください
(より簡単な方でいいです)

よろしくお願いします。

(daddy) 2014/08/01(金) 12:57


 ごめんなさい。
 これ以上関数で行うのは私には無理です。
 せめて日付が昇順(降順ではなく)になっていると、他の回答者さんも回答し易くなると思いますよ。

 空白や文字が含まれているそうですが、私なら置換機能若しくはジャンプ機能で空白と文字を0に
 しちゃいます。

 SUMIFSについてはヘルプを参照してください。
 SUMIFSの引数になっている
 >LARGE(IF($C$2:$C$7<C2,IF($A$2:$A$7=A2,$C$2:$C$7,0),0),1)
 こちらの部分がキモになっていますので、こちらの解説だけ。分かりやすく$とります
 =LARGE(IF(C2:C7<C2,IF(A2:A7=A2,C2:C7,0),0),1)
           ~~~~~~~~    ~~~~~~~~ ~~~~~
              (a)         (b)     (c)

 (a)(b)(c)は配列を返す式です。
 (a)(b)はAND条件として使用しています。
 配列関数でAND関数を使うと、1つのTrueまたはFalseしか得られない為。
(FはFalse、TはTrueの意)
 =LARGE(IF({F;T;T;T;T;T},IF([T;F;T;F;T;T},C2:C7,0),0),1) '(a)(b)を表に従いTrueとFalseの配列にします。
 =LARGE(IF({0;F;T;F;T;T},C2:C7,0),1)                      '(a)がTの場合(b)の同じ列の配列を、Fの場合0になります
 =LARGE({0;0;140731;0;140706;140504},1)                   '(a)(b)の組合せがTの場合、C列の値が帰ります。
 =140731                                                  'Large関数で一番大きい数値をとります

 こうすることで、参照したセルよりも小さい日付の同じ項目の一番大きい値を取得しています。

 ちなみにこれだけでも数式が成り立ちますので、数式タブの数式の検証で検証されると理解が
 深まると思います。

 後はGobGobさんにバトンタッチです・・・

(稲葉) 2014/08/01(金) 13:26


稲葉さん、 恐縮です。
LARGE関数の解説、ありがとうございます。
まだ完全に理解できてませんが、イメージはできてます。

日付の昇順、データ置換も止むを得ずと覚悟?はしておりますが、
日付の場合も「0」置換で大丈夫でしょうか?
今、実データ確認環境にないため試せず、事前に教えていただけると
助かります。

しばしGobGobさん他のご回答を待ちつつ...^^;

よろしくお願いします。

(daddy) 2014/08/01(金) 14:16


B,C列の「空白」「文字」処理を「0」置換処理すると
D列の判定に影響するのでダメっぽいですね..

やはり、「無視」でお願いします。

C列の日付けについては、実データが降順っぽいので
都合いいのですが、昇順が必要であればいってください。
あるいは、該当行を一括削除しましょうか?

よろしくお願いします。

(daddy) 2014/08/01(金) 15:51


 だらだらしてるなぁ・・・

 D2 =IFERROR(IF(SUMPRODUCT((A$2:A$20=A2)*(C$2:C$20<C2)*ISNUMBER(B$2:B$20))*(C2&""),TEXT(SUMPRODUCT((A$2:A$20=A2)*(TEXT(C$2:C$20&"","0;;0;""999999""")*1=MAX(INDEX((A$2:A$20=A2)*(TEXT(C$2:C$20&"","0;;0;""999999""")*1<C2)*ISNUMBER(B$2:B$20)*TEXT(C$2:C$20&"","0;;0;!0"),))),B$2:B$20)-B2,"!#;!*;!-"),"-"),"×")
 
(GobGob) 2014/08/01(金) 17:36

GobGobさん、ありがとうございます。

例示で式をコピペして試しましたが、結果はすべて「X」になります。

配列数式じゃないですよね?(結果は同じでしたが..)

↑訂正です。当方の確認ミスでした..しばしお時間ください_ _;

(daddy) 2014/08/02(土) 17:29


日付が最も旧い(小さい)時は「×」が表示されないようです。
これはGobGobさんの想定内でしょうか?
(できれば、「×」表示したいのですが..)

 名前	点数	日付	印
 a	80	140729	#
 c	欠	140601	-
 a	90	140720	*
 c	80	140729	-
 a	70	140707	-
 a	欠	140505	-

さすがにこれだけの長い式は当方の理解を超えており(苦笑)、もし「×」表示を
「-」で統一することにすれば、かなり簡略できるものでしょうか?

ご意見お聞かせください..
(daddy) 2014/08/03(日) 12:14


 >日付が最も旧い(小さい)時は「×」が表示されないようです。 

 んな条件、後出しされても知りまへんがな。

 =IFERROR(IF(SUMPRODUCT(・・・),・・・,"×"),"×") に変更。 

 ・IFERROR → エラーのとき。
 ・IF(SUMPRODUCT(・・・) → 該当するデータが1以上。

 なんで、ケツから2番目の - を ×にするだけ。
 
(GobGob) 2014/08/03(日) 14:05

結果です。

 名前	点数	日付	印
 a	80	140729	#
 c	60	140601	×
 a	90	140720	*
 c	80	140729	*
 a	70	140707	#
 a	80	140505	×

??、単に日付が最小の時「×」にしたいのではなく、最小の時に点数が
「文字や空白(例では“欠”)」だと「-」になってしまいます..
ということなんですが。

説明ベタですみません.._ _;
(daddy) 2014/08/03(日) 15:09


 =IFERROR(IF(SUMPRODUCT((A$2:A$20=A2)*(C$2:C$20<C2)*ISNUMBER(B$2:B$20))*(-B2&C2),TEXT(SUMPRODUCT((A$2:A$20=A2)*(TEXT(C$2:C$20&"","0;;0;""999999""")*1=MAX(INDEX((A$2:A$20=A2)*(TEXT(C$2:C$20&"","0;;0;""999999""")*1<C2)*ISNUMBER(B$2:B$20)*TEXT(C$2:C$20&"","0;;0;!0"),))),B$2:B$20)-B2,"!#;!*;!-"),"-"),"×")
 
(GobGob) 2014/08/04(月) 08:15 → 8:41訂正

 悔しいので参考までにマクロ案・・・
 「一発」には変わらないと思う・・・
 条件は
 日付は必ず降順になっていること
  1)点数が空白か文字列の場合、「-」
  2)比較できる最後の点数入力日が「×」
    (例えばB君は一番最後の点数が「-」になっているが、30行目で初めて
     点数が入力されているので、そこが最終日と考える)
 以上をふまえた上で、下記の結果で良いなら
	[A]	[B]	[C]	[D]
[1]	名前	点数	日付	印
[2]	D	70	140804	#
[3]	B	80	140729	#
[4]	D	90	140728	*
[5]	C	90	140722	*
[6]	D	-	140713	-
[7]	B	90	140707	*
[8]	A	90	140703	*
[9]	B	70	140628	*
[10]	A	-	140626	-
[11]	E	90	140616	*
[12]	A	-	140612	-
[13]	B	60	140608	#
[14]	B	70	140603	#
[15]	A	80	140601	#
[16]	E	80	140527	#
[17]	A	90	140523	*
[18]	E	90	140521	*
[19]	A		140517	-
[20]	C		140509	-
[21]	E	80	140507	-
[22]	A	-	140428	-
[23]	B	90	140426	*
[24]	B	70	140418	#
[25]	A	-	140417	-
[26]	B	90	140410	-
[27]	E	80	140402	*
[28]	A	-	140331	-
[29]	E	70	140328	*
[30]	B	90	140322	×
[31]	C	70	140314	#
[32]	E	60	140312	#
[33]	E	70	140311	#
[34]	A		140303	-
[35]	A	70	140221	×
[36]	E	90	140213	*
[37]	D	60	140203	×
[38]	C	90	140127	×
[39]	E	70	140123	×
[40]	B	-	140117	-

 コード
Sub test()
    '名前毎に比較するためのDictionaryオブジェクト
    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    'A1を基準に空白で囲まれたデータを格納する
    Dim tbl
    tbl = Range("A1").CurrentRegion.Value
    '結果を出力するための変数
    Dim Result
    ReDim Result(2 To UBound(tbl, 1))
    '繰り返し構文で使う変数
    Dim i As Long
    Dim N As String
    Dim T
    Dim tmp As String
    For i = 2 To UBound(tbl, 1)
        N = tbl(i, 1)
        T = tbl(i, 2)
        If (Not T = "") And (IsNumeric(T)) Then
            If Not dic.exists(N) Then
                dic.Add N, Array(T, i)
            Else
                Select Case dic(N)(0)
                    Case Is > T: tmp = "*" '前回結果より良かった場合
                    Case Is < T: tmp = "#" '前回結果より悪かった場合
                    Case Is = T: tmp = "-" '前回結果と同じだった場合
                End Select
                Result(dic(N)(1)) = tmp
                dic(N) = Array(T, i)
            End If
        Else
            '得点が数値以外の場合
            Result(i) = "-"
        End If
    Next i
    '一番最後に「点数がある」日付に該当するデータに×をつける
    Dim K
    For Each K In dic.keys
        Result(dic(K)(1)) = "×"
    Next K
    Range("D2").Resize(UBound(Result) - 1).Value = Application.Transpose(Result)
End Sub
(稲葉) 2014/08/04(月) 10:50

 もう一回関数でチャレンジ!
 配列数式です。
=IFERROR(IF(ISNUMBER(B2),TEXT(B2-INDEX(B3:$B$40,MATCH(A2,IF(ISNUMBER(B3:$B$40),A3:$A$40,0),0))*1,"""*"";""#"";""-"";"""""),"-"),"×")

 日付が降順の前提はそのままで、お尻を固定してその行より下で数値のある行と比較して大小判定
 私が出した表なら同じ結果が得られるはず・・・
(稲葉) 2014/08/04(月) 13:37

ありがとうございます。
GobGobさん
例示では点数(B列)の「空白・文字」対応うまくできました!
※IFERROR使用できる環境にないため、IF(ISERROR(..にて試しました)
日付(C列)の「空白・文字」対応も希望ではあったのですが、
これはおり込まれてないですよね?
※こちらは実データのバグ?っぽいので予め削除することにしますので、
 対応不要とさせていただきますが。

因みに、日付を「空白」にして試したのですが、下表では1行目の「a」の
印は「#」→「*」に更新されましたが、3行目は「*」のままです。
想定内でしょうか?
(実害はないと思っていますが、式を理解する上でお尋ねしています)

 名前	点数	日付	印
 a	80	140729	*
 c	60	140601	-
 a	90	 	*
 c	80	140729	*
 a	70	140706	#
 a	80	140504	-

稲葉さん
例示でうまくいくのを確認しました!
マクロは理解できませんが(涙)、式も..まだまだです(苦笑)
問題は"条件"ですが、「非該当」と「空白・文字」を区別しない
のがちょっと..最終は実データでの処理時間等も含め決めさせて
いただきます。

よろしくお願いします。

(daddy) 2014/08/04(月) 15:44


 >(空白や文字は)やはり、「無視」でお願いします。 
 >→D2は6行目と比較してください。  
 > で、D4は「-(非該当)」か新規に「X」を付してください
 >C列の日付けについては、実データが降順っぽいので 

 ↑はあなたの発言です。

 >問題は"条件"ですが、「非該当」と「空白・文字」を区別しないのがちょっと・・
 それはあなたがしっかり条件付けないからでしょう?
 非該当の定義はなに?
  点数に空白が含まれている場合?
  点数に「-」が含まれている場合?
  点数に文字列が含まれている場合?
  該当行より古い日付がない場合?

 >マクロは理解できませんが(涙)、式も..まだまだです(苦笑) 
 理解する努力も見えないのに、後だし条件で新しくデータも提供するでもなく、
 要求だけ言われても困ります。

 すべての条件が網羅出来るデータと求める結果を出してから言ってください。
(稲葉) 2014/08/04(月) 16:10

稲葉さん、失礼しました。 やりたいことを整理しますと、
・より“新しい日付”で点数が上昇していれば「*」を、下がっていれば
「#」を、変わらなければ「-」を付す
※上昇、下降どちらにも該当しないのを「非該当」としています
・「空白・文字」は、「数値でない」のを指しています
 実データで確認できているのは、「空白」と漢字の「欠」「取消し」、
 記号?の「------」ですが、第三者から提供されるのでこれで全てを
 網羅しているかは不明です
・「数値でない」場合は「×」表示が希望ですが、式が複雑になる場合や
 処理速度が遅くなるようなら「-」でも構いません
※その判断が自分でできないため、つい“あいまいな”表現になってしまいますが、ご容赦ください。
※つい“問題”という言葉を使ってしまいましたが、全然そんなことはありません。
 取消しさせてもらいます(汗)

 >それはあなたがしっかり条件付けないからでしょう?
 >理解する努力も見えないのに、後だし条件で...

全くもってそのとおりで、お詫びしようがありません。
以後気をつけるようにします。
GobGobさんもすみませんでした。

お二人から提示されたのは、もうすでに十分実用できますので、このまま
使用させていただこうと思っています。
まだまだ式やマクロの理解にはほど遠いですが、コツコツやっていこうと
思います。

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

(daddy) 2014/08/04(月) 18:11


 まだ足りません。
 1)日付が入力されていない場合について、条件出しておいて整理されていません。
 2)「実データが降順っぽい」なのに、「2014/08/04(月) 15:44」投稿ではcさんが降順になっていない
   実際はどうなんですか?
 3)>「空白・文字」は、「数値でない」のを指しています 
   >「数値でない」場合は「×」表示が希望ですが、
   >※その判断が自分でできないため・・・

   とありますが、それが
   >>理解する努力も見えないのに、後だし条件で...
   という言葉になりました。

   =IFERROR(IF(ISNUMBER(B2),前回結果と比較する式,"-"),"×")
   私はこのように提示しました。IF関数とISNUMBER関数の組み合わせです。
   分かりますよね?
   ここに点数が空白の場合と、日付が空白を付けたすと
   =IFERROR(IF(B3="","点空",IF(C3="","日空",IF(ISNUMBER(B3),前回結果と比較する式,"文"))),"×")
   簡単なIF文です。

 こちらも最低限の注釈は入れておきました。
Sub test()
    '名前毎に比較するためのDictionaryオブジェクト
    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    'A1を基準に空白で囲まれたデータを格納する
    Dim tbl
    tbl = Range("A1").CurrentRegion.Value
    '結果を出力するための変数
    Dim Result
    ReDim Result(2 To UBound(tbl, 1))
    '繰り返し構文で使う変数
    Dim i As Long
    Dim N As String
    Dim D As String
    Dim T
    Dim tmp As String
    For i = 2 To UBound(tbl, 1)
        N = tbl(i, 1)
        T = tbl(i, 2)
        D = tbl(i, 3)
        Select Case True
            Case D = "":           Result(i) = "日"  '日付が空白の場合
            Case T = "":           Result(i) = "点"  '点数が空白の場合
            Case Not IsNumeric(T): Result(i) = "文"  '点数が文字列の場合
            Case IsNumeric(T)                        '点数が数値の場合
                If Not dic.exists(N) Then
                    dic.Add N, Array(T, i)
                Else
                    Select Case dic(N)(0)
                        Case Is > T: tmp = "*"       '前回結果より良かった場合
                        Case Is < T: tmp = "#"       '前回結果より悪かった場合
                        Case Is = T: tmp = "-"       '前回結果と同じだった場合
                    End Select
                    Result(dic(N)(1)) = tmp
                    dic(N) = Array(T, i)
                End If
            Case Else:             Result(i) = "?"  'それ以外の場合
        End Select
    Next i
    '一番最後に「点数がある」日付に該当するデータに×をつける
    Dim K
    For Each K In dic.keys
        Result(dic(K)(1)) = "×"
    Next K
    Range("D2").Resize(UBound(Result) - 1).Value = Application.Transpose(Result)
End Sub
(稲葉) 2014/08/05(火) 09:02

ご丁寧な説明、ありがとうございます。
式はわかります。 マクロは注釈のおかげで作業の流れ的なところがわかるので、
これを“教材”にして構文?など学習していきたいと思います。

日付の件ですが、現時点での確認では「空白」や「---」がごく稀にありますが、
降順にはなっているようです。
既述のように第三者からの提供であり、そもそも日付に「空白、記号」は
何だかアヤシイ?^^;ので降順は前提にしませんでした。
ただ、事前にフィルタ操作で「空白、記号」は削除しますので、
同時に降順並べ替え(念の為)するのは手間ではありません。

説明になっていますでしょうか?

これからもよろしくお願いします。

(daddy) 2014/08/06(水) 10:29


コメント返信:

[ 一覧(最新更新順) ]


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