[[20061004154441]]　『集計』（りん）　ページの最後に飛ぶ

『集計』（りん）

 コード4を基準としてコード番号ごとに　1・2・3列ごとに集計しています。列1の集計が同じものはそれぞれ相殺して行を削除しています。手作業のためよい方法あれば教えてください。お願いします。ﾃﾞｰﾀは17列6000行あります																
 　
NO　日　日　　コード1　コード2　品目　品名　コード1　コード2　　1　　2　　3　　コード3　コード4　コード5																
1　41　42　　A1　　111　　A　　　B　　　A-1　　　A-2　　11　0　11　A-3　　A-4　　A-5																
1　41　42　　A1　　111　　A　　　B　　　A-1　　　A-2　　11　0　11　A-3　　A-4　　A-6削除																
1　41　42　　A1　　111　　A　　　B　　　A-1　　　A-2　　　7　0　７　A-3　　A-4　　A-7削除																
1　41　42　　A1　　111　　A　　　B　　　A-1　　　A-2　　-7　0　-7　A-3　　A-4　　A-8削除																
1　41　42　　A1　　111　　A　　　B　　　A-1　　　A-2　　-7　0　-7　A-3　　A-4　　A-9																
1　41　42　　A1　　111　　A　　　B　　　A-1　　　A-2　-11　0　-11　A-3　　A-4　　A-10削除																
　　　　　　　　　　　　　　　　　　集計　　　　　　　　　　　　　　4　　0　　4																
1　41　42　　A1　　111　　A　　　B　　　A-1　　　A-2　　0　　46　46　A-3　　A-4　　A-10																
1　41　42　　A1　　111　　A　　　B　　　A-1　　　A-2　　-137　0　-137　A-3　　A-4　　A-11																
　　　　　　　　　　　　　　　　　　集計　　　　　　　　　　　　　　-137　　46　　4																
※右端の削除は削除したいデータです。	
 すみませんうまく表が表示できません。

 レイアウトは下記であっていますか？
	[A]	[B]	[C]	[D]	[E]	[F]	[G]	[H]	[I]	[J]	[K]	[L]	[M]	[N]	[O]	
[1]	NO	日	日	コード1	コード2	品目	品名	コード1	コード2	1	2	3	コード3	コード4	コード5	
[2]	1	41	42	A1	111	A	B	A-1	A-2	11	0	11	A-3	A-4	A-5	
[3]	1	41	42	A1	111	A	B	A-1	A-2	11	0	11	A-3	A-4	A-6	削除
[4]	1	41	42	A1	111	A	B	A-1	A-2	7	0	7	A-3	A-4	A-7	削除
[5]	1	41	42	A1	111	A	B	A-1	A-2	-7	0	-7	A-3	A-4	A-8	削除
[6]	1	41	42	A1	111	A	B	A-1	A-2	-7	0	-7	A-3	A-4	A-9	
[7]	1	41	42	A1	111	A	B	A-1	A-2	-11	0	-11	A-3	A-4	A-10	削除
[8]							集計			4	0	4				
[9]	1	41	42	A1	111	A	B	A-1	A-2	0	46	46	A-3	A-4	A-10	
[10]	1	41	42	A1	111	A	B	A-1	A-2	-137	0	-137	A-3	A-4	A-11	
[11]							集計			-137	46	4

 とりあえず、整形と(表はエクセルから貼り付けると簡単ですよ。)
 疑問点。
 Ｊ列が
「 11」のもの　A-5,A-6
「-11」のもの　A-10
なぜA-10の対がA-5ではなく A-6なのですか？

 それとも私の考え方がちがうのですかね？

 >コード4を基準としてコード番号ごとに　1・2・3列ごとに集計しています。
 ここの意味もちょっとわからないです・・・。
 実際のデータは７行目までのコード４と、９・１０行目のコード４は
 違ったコードなんですかね？（１・２・３列も？ほかの所も？）

 作業列を作ってABS関数で絶対値を表示させて
 オートフィルタで絞り込みながら削除していく
 ・・・とかじゃ駄目ですかね？
   今回の例だと全ての行を表示した状態で「11」と「-11」を探すのではなく
   一度絶対値「11」の行のみを表示して、プラスの物とマイナスの物を同行数削除する。
   と言う手順です。

 (HANA)

 HANA　さま

 ありがとうございます。

 すいません。誤った説明とデータでした。ごめんなさい。	
　
 下記にデータも書き直しました。（教えていただいたようにエクセルを貼りました）													
 この並び順のＤ列のコード1のコードが同一、　計3で相殺して0　のデータは削除したいのです。														
 計3で相殺して0　のデータは削除したいのです。	（										
 Ｌ列に削除（又は×）と入力できればうれしいです。お願いします。														
	Ａ	Ｂ	Ｃ	Ｄ	Ｅ	Ｆ	Ｇ	Ｈ	Ｉ	Ｊ	Ｋ	Ｌ	Ｍ	Ｌ
1	ＮＯ	日1	日2	コード1	ＮＯ	担1	担2	担3	担4	計1	計2	計3	品	
2	01	330	210	A7	7	a	A	あ	ｋ	-3	0	-3	2	削除
3	02	330	210	A7	7	a	A	あ	ｋ	3	0	3	2	削除
4	03	330	310	A6	16	i	I	い	ｋ	0	2	2	5	削除
5	04	330	310	A6	16	i	I	い	ｋ	-2	0	-2	5	削除
6	05	330	310	A6	16	i	I	い	ｋ	2	0	2	5	削除
7	06	330	310	A6	16	i	I	い	ｋ	-2	0	-2	5	削除
8	06	330	310	A6	16	i	I	い	ｋ	-2	0	-2	5	
9	07	330	228	A6	90	u	Ｕ	う	ｋ	-5	0	-5	4	削除
10	08	330	228	A6	90	u	Ｕ	う	ｋ	5	0	5	4	削除
11	09	330	318	A1	4	e	Ｅ	え	ｋ	8	0	8	4	
12	10	330	318	A1	4	e	Ｅ	え	ｋ	5	0	5	4	
13	11	330	215	A9	0	o	Ｏ	お	ｋ	0	4	4	6	
14	12	401	216	A7	7	a	A	あ	ｋ	6	0	6	2	削除
15	13	401	216	A7	7	a	A	あ	ｋ	0	-6	-6	2	削除

 >Ｌ列に削除（又は×）と入力できればうれしいです。
 ・・・えっと、Ｎ列でいいですよね？

 N2に
=IF(L2="","",IF(OR(
SUMPRODUCT(($D$2:$D$1000=D2)*($L$2:$L$1000=L2))<=SUMPRODUCT(($D$2:$D$1000=D2)*($L$2:$L$1000=L2*-1)),
SUMPRODUCT(($D$2:$D$1000=D2)*($L$2:$L$1000=L2*-1))>=SUMPRODUCT(($D$2:D2=D2)*($L$2:L2=L2))),"削除",""))

 と言う式を入れてやっちゃってみて下さい。
 計算に時間がかかりそうな予感です。
 上手く行くと良いのですが。

 一応リスト範囲は1000行までです。
 そんなに必要無い場合や、それでも足りない場合は式中の
「$D$1000」と「$L$1000」の部分
 計６カ所を変更して下さい。

 ・・・何故かＢ列を見ていたので式を変更しました・・・。
 (HANA)

 HANAさん
 すごいエクセルありがとうございます。
 気の遠くなるような作業だったので本当にうれしいです。今から使わせて頂きます。
 もし宜しかったら　上記式の意味を教えて頂けませんか？
 本当にすいません。

 ・・・使えましたかね？（良かったです。）
 説明ですが、やりますが、その前に忘れそうなので
 「文章の最後にはニックネーム(ご署名)をお願いします。」
 自主的に書かないと入らないんですよね。

 さて、本題。
 SUMPRODUCT関数は分かりますでしょうか？
http://www.excel.studio-kazu.jp/lib/e3h/e3h.html
 とりあえずリンクだけしておきます。
 先に私が式を考えた手順を載せるので、まずは見て下さい。
   実際に関数で何故その値が出るのかは、その後で。
   リンク先をみて分からなかったらまたお訊ね下さい。

 説明・・・と言っても大したことではないのですが
 １．自分がそのグループの中で何回出現するか数える。（Ｏ）
 ２．相方が同じグループの中で何回出現するか数える。（Ｐ）
 ３．自分がそのグループの中で何回目に出現か数える。（Ｑ）
 「数えて比べて表示」しているだけです。

 まず『数えて』みる式です。
 Ｏの式が　O2=SUMPRODUCT(($D$2:$D$1000=D2)*($L$2:$L$1000=L2))
 Ｐの式が　P2=SUMPRODUCT(($D$2:$D$1000=D2)*($L$2:$L$1000=L2*-1))
 Ｑの式が　Q2=SUMPRODUCT(($D$2:D2=D2)*($L$2:L2=L2)))
 　ＯとＰの式は最後が「L2」か「L2*-1」かの違いしかないですよね。
   「自分自身」か、「その相方」かの違いしか無いからです。
 (計算途中を見たければ、それぞれセルにコピーしてみて下さいね。)

 つぎに『比べて』みる式です。
 ＯとＰの数が同じ場合は、その数が何個あっても“削除”ですよね。
 ＯよりＰの数が多い場合は、Ｏの数分は相殺出来るので“削除”ですよね。
 式は R2=IF(O2<=P2,"削除","★")（Ｒ）
   ＯがＰ以下なら“削除”そうでないなら未定なので★を表示します。

 問題は、ＯよりＰの数が少ない場合です。
 Ｐの数分だけＯは“削除”なのですが、Ｐの数を超えると相殺ではありません。
 それなら、Ｏが一番上から今までで何回出てきたかとＰの数を見比べて
 Ｐの数を超えない分は“削除”にすればいいですね。
 「Ｏが一番上から今までで何回出てきたか」数えるのがＱの式です。
 Ｑの値がＰと同じかそれより小さい時に“削除”なので
 式は　S2=IF(P2>=Q2,"削除","★")（Ｓ）

 ・・・とここまで来て「おやおや？」ですね。
 Ｑの値はＯの値を超えることはないので、ＱとＰを見れば
 ＯとＰを見る必要がないですね・・・・。
 最初の式は、「ＲとＳのどちらかが“削除”なら削除だな」と思って
 OR関数に入れたのですが、Ｓの式だけで良いみたいでした。

 よって、式が短くなりました。(笑)
 N2は
=IF(L2="","",
IF(SUMPRODUCT(($D$2:$D$1000=D2)*($L$2:$L$1000=L2*-1))>=SUMPRODUCT(($D$2:D2=D2)*($L$2:L2=L2)),
"削除",""))

 S2=IF(P2>=Q2,"削除","★")この式の
「P2」の所に現在P2に入っている式を
「Q2」の所に現在Q2に入っている式をいれて
「それ以外は削除じゃない！！」と分かったので「"★"」を「""」に変更しました。
 さらに、式を多目にコピーしても余計なものが表示されないように、
 「L2が空欄なら空欄表示」と言うIF関数でくくりました。

 要するに、相方のトータル出現回数より、自分が今まで出てきた回数が同じか
 それより小さい場合が“削除”になります。

 やっぱり、「ちゃんと確認しないと」ダメですね。

 で、実際の計算の方は分からなかったらまたご質問下さい。
 （もちろん上の説明で分からない部分もあればあわせて。）

 (HANA)

 ＮＯ２の（マクロでやっつける）方法。
 　　　　　（弥太郎）
 Option Explicit
 Sub 削除一発()
    Dim dic As Object, tbl, x
    Dim i As Long, data As Double

    Set dic = CreateObject("scripting.dictionary")
    tbl = Range("a2").Resize(Range("a" & Rows.Count).End(xlUp).Row, 13).Value
    ReDim x(1 To UBound(tbl, 1), 1 To 1)

    For i = 1 To UBound(tbl, 1)
        data = tbl(i, 12) * -1
        If dic.exists(tbl(i, 4) & data) Then
            x(i, 1) = "削除"
            x(dic.Item(tbl(i, 4) & data), 1) = "削除"
            dic.Remove tbl(i, 4) & data
        Else
            dic(tbl(i, 4) & tbl(i, 12)) = i
        End If
    Next i

    Cells(2, "n").Resize(UBound(tbl, 1) - 1) = x
    Set dic = Nothing

 End Sub

コメント返信：

[ 一覧(最新更新順) ]

[[20061004154441]] 『集計』（りん） ページの最後に飛ぶ

[[20061004154441]]　『集計』（りん）　ページの最後に飛ぶ