[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『部分一致の件数を細かく分類する方法』(ニック)
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
γさんのコードの後で恥ずかしいのですが参考出品です。 5個以下の一致の場合の xxxxx* といった * でのパディング、手抜きで、やっていません。
γさんと同じく、データシートの2行目からデータ、結果シートの1行目にはタイトル行があらかじめセット済みという前提です。
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))
こんな式を下に『3万行』フィルコピーして、できあがったもので値のあるものを、A2:B2 から始まる部分に 上づめで転記するような数式をいれて、これも『3万行』フィルコピーする。
次に Q列あたりに A列のデータで N列に登場しないものだけを、値としては5桁、列挙する数式を、『3万行』ちりばめる。 この Q列を元ネタにして、N:O でやったような式で、5桁ベースのものをR:S列あたりに取り出して、それを C:D 列に反映させる。
次に Q列のデータで R列に登場しないものだけを 値としては 4桁・・・・・
といった、作業列、つかいまくりの数式処理になりそうです。 仮に、この処理で作業列なしの数式を作ったとしても、それは、きわめて複雑な配列数式になると思いますし、 それを 各列 3万行 埋め込むことになりますから、重くて重くて使い物にならないような気がします。
( β) 2017/02/18(土) 09:33
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.