[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『部分一致の件数を細かく分類する方法』（ニック）
A | 000000 | 000001 | 000100 | 002007 |
・
・
・
000000 |
000111 |
001100 |
102007 |
上記のような文字列(例は数字ですが実際は文字列)があり、同じものも存在します。このデータを、10を基準に分類していきます。
例でいうと、6文字すべてが一致するデータの数が10以上であればそのままカウント。
10未満ならば、先頭から5文字一致するものを合計し、それが10以上ならその数字を、それでも満たなければ先頭四文字で再集計・・・といった感じです。
sheet2に結果を表示
A | B | C | D | 6文字一致 | 個数 | 5文字一致 | 個数 | 000000 | 15 | 00001* | 11 | 100010 | 10 | 00002* | 17 |・・・・・・ 000100 | 25 | | | 000200 | 11 | | |
countifで集計するというのは何となくわかるのですが、ifの条件指定が複雑でうまくいきません。
どなたかご教授願えないでしょうか？
よろしくお願いいたします。
＜ 使用 Excel：Excel2007、使用 OS：Windows7 ＞
（γ） 2017/02/16(木) 23:01
（γ） 2017/02/16(木) 23:32
00000
の文字列があったとして、
00000 0000 000 00 0
とするのですよね？
データが重複しませんか？
00000が10以上あれば、このデータは0000や00の分類ではカウントしません
（ニック） 2017/02/17(金) 07:10
A B C D E F G 1 6 5 4 3 2 1 2 000000 3 3 3 3 3 3 3 100000 3 3 3 3 3 18 4 110000 3 3 3 3 15 18 5 111000 3 3 3 12 15 18 6 111100 3 3 9 12 15 18 7 111110 3 6 9 12 15 18 8 111111 3 6 9 12 15 18 9 000000 3 3 3 3 3 3 10 100000 3 3 3 3 3 18 11 110000 3 3 3 3 15 18 12 111000 3 3 3 12 15 18 13 111100 3 3 9 12 15 18 14 111110 3 6 9 12 15 18 15 111111 3 6 9 12 15 18 16 000000 3 3 3 3 3 3 17 100000 3 3 3 3 3 18 18 110000 3 3 3 3 15 18 19 111000 3 3 3 12 15 18 20 111100 3 3 9 12 15 18 21 111110 3 6 9 12 15 18 22 111111 3 6 9 12 15 18
1行目に左からの個数 B2 =COUNTIF($A:$A,LEFT($A2,B$1)&"*") 範囲コピー。 （GobGob） 2017/02/17(金) 08:09
一度対象としたものは、重複して対象にはしないということでしたか。
ところで、質問者さんはどのような回答を期待しているのでしょうか？
・作業列は使わない。
・式をセルに書き込んで、一発で答えがでるようにしたい。
ということでしょうか？
それは、たぶん難しいと思いますよ。
私は、
(1)ピボットテーブルで同一のものの数をカウント
(2)それを降順にソート
(3)10以上なら、それを結果に書込
(4)10未満のものについては、文字列の下一桁を落としたものを作成
(5)そのカウント数の合計を集計
(6)以下、(2)に戻って繰り返す。
というのを念頭に置きました。
3、4回繰り返すだけだと思います。
こういうのは、手作業といって、手を汚したくないのでしょうか？
ピボットテーブルはこういう時のための特注の道具なので、
私には理解しかねますね。
マクロを使ってよいなら、dictionaryを使って割と簡単に書けるでしょうね。
# 3万行になると、Countifで総当たりの合計は、大変重くなりますねえ。
（γ） 2017/02/17(金) 20:36
いったん書きこみましたが、誤解していたようなので消しました。
（ β） 2017/02/17(金) 20:44
質問です。
SHeet2 には 一致したものが 10個以上のものを表示するようですので、Sheet2 の個数の合計は 必ずしも Sheet1 の個数（行数）に一致しないということですね？
かつ、6文字一致したデータは、そのあとの 5文字一致判定の対象外にするんですね？
しかし・・・こんなの、関数でできるものなんでしょうか？
（ β） 2017/02/17(金) 21:17
マクロなら、こんなことになるんじゃないかな。
マクロがお呼びでなければ、無視しておいて結構。
前提： ・"データ"シートのA2から下に元データがある。 ・"結果"シートに結果を書き込みます。 （形式は、質問にあるようなものです）
なお、 "データ"シートのB列以降は作業列なので用が済んだら消去されます。 注意して下さい。
Sub test()
Const inf As Long = 10
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim dic As Object
Dim key As Variant
Dim s As String
Dim col As Long
Dim k As Long
Dim p As Long
Dim q As Long
Set ws1 = Worksheets("データ") ' データ
Set ws2 = Worksheets("結果") '結果を書き込むためのシート
Set dic = CreateObject("Scripting.Dictionary")
'結果シートをクリアーしておく
ws2.Cells.ClearContents
'初回だけカウント数を入れておく
For k = 2 To ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
ws1.Cells(k, 2).Value = 1
Next
For col = 1 To 11 Step 2
Columns(col).NumberFormatLocal = "@"
'辞書の初期化
dic.RemoveAll
'文字列の出現回数を dictionaryに保持
For k = 2 To ws1.Cells(ws1.Rows.Count, col).End(xlUp).Row
s = ws1.Cells(k, col).Value
dic(s) = dic(s) + ws1.Cells(k, col + 1).Value
Next
'既定回数以上の出現回数ならば、文字列と出現回数を結果シートに書き込む
'既定回数未満なら、次回の処理のために、文字列の長さを減らして、
' データシートに書き込む
p = 1
q = 1
For Each key In dic.keys
If dic(key) >= inf Then
p = p + 1
ws2.Cells(p, col).Value = Left(key & "******", 6)
ws2.Cells(p, col + 1).Value = dic(key)
Else
q = q + 1
ws1.Cells(q, col + 2).Value = Left(key, Len(key) - 1)
ws1.Cells(q, col + 3).Value = dic(key)
End If
Next
Next
'データシートの作業列を消去
ws1.Range("A1").CurrentRegion.Offset(0, 1).ClearContents
End Sub
（γ） 2017/02/17(金) 21:26
（γ） 2017/02/17(金) 21:30
γさんのコードの後で恥ずかしいのですが参考出品です。 ５個以下の一致の場合の xxxxx* といった * でのパディング、手抜きで、やっていません。
γさんと同じく、データシートの２行目からデータ、結果シートの１行目にはタイトル行があらかじめセット済みという前提です。
Sub Sample()
Dim dic As Object
Dim ans As Object
Dim tmp As Object
Dim x As Long
Dim k As Variant
Dim shT As Worksheet
Dim c As Range
Set shT = Sheets("結果")
shT.UsedRange.Offset(1).ClearContents
Set dic = CreateObject("Scripting.Dictionary")
Set ans = CreateObject("Scripting.Dictionary")
Set tmp = CreateObject("Scripting.Dictionary")
With Sheets("データ")
For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
dic(c.Value) = dic(c.Value) + 1
Next
End With
For x = 6 To 1 Step -1
tmp.RemoveAll
ans.RemoveAll
For Each k In dic
If dic(k) >= 10 Then
ans(k) = dic(k)
Else
If x > 1 Then tmp(Left(k, x - 1)) = tmp(Left(k, x - 1)) + dic(k)
End If
Next
If x > 1 Then
dic.RemoveAll
For Each k In tmp
dic(k) = tmp(k) + dic(k)
Next
End If
If ans.Count > 0 Then
shT.Cells(2, 11 - (x - 1) * 2).Resize(ans.Count).Value = WorksheetFunction.Transpose(ans.keys)
shT.Cells(2, 11 - (x - 1) * 2 + 1).Resize(ans.Count).Value = WorksheetFunction.Transpose(ans.items)
End If
Next
End Sub
（ β） 2017/02/17(金) 22:35
（γ） 2017/02/18(土) 06:50
関数の専門家さんであれば、よりよい数式の組み立てもできるかもしれませんが 素人の私がやるなら、たとえば N列を作業列にして、
N2 : =IF(COUNTIF(データ!A$1:A2,データ!A2)<>1,"",IF(COUNTIF(データ!A:A,データ!A2)>=10,データ!A2,"")) O2 : =IF(N2="","",COUNTIF(データ!A:A,データ!A2))
こんな式を下に『３万行』フィルコピーして、できあがったもので値のあるものを、A2:B2 から始まる部分に 上づめで転記するような数式をいれて、これも『３万行』フィルコピーする。
次に Q列あたりに A列のデータで N列に登場しないものだけを、値としては5桁、列挙する数式を、『３万行』ちりばめる。 この Q列を元ネタにして、N:O でやったような式で、5桁ベースのものをR:S列あたりに取り出して、それを C:D 列に反映させる。
次に Q列のデータで R列に登場しないものだけを 値としては 4桁・・・・・
といった、作業列、つかいまくりの数式処理になりそうです。 仮に、この処理で作業列なしの数式を作ったとしても、それは、きわめて複雑な配列数式になると思いますし、 それを 各列 ３万行 埋め込むことになりますから、重くて重くて使い物にならないような気がします。
（ β） 2017/02/18(土) 09:33
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.