A B C D E F…
1
2
3
4
5
6
:
現在、２つのBOOKに別れているデータを一元化したいと考えてえております。
１つは
Aに住所データ
Bに名前Cに会員番号
が入った37801人分のデータがございます。
２つ目は
Dに住所データ
EFにBCと異なる内容のデータ
が入った4544人分のデータがございます。
つまり
A〜CとD〜Fは全く異なるデータです。
同じ町のとある２つのデータなので
１つ目のデータと２つ目のデータで、同一住所のデータがあります。
A列D列と異なる列にあり、行もバラバラです。
しかし、ＡとＤで同一データ（同一住所）
例えば
Ａ〜Ｃ列10000行に長崎県長崎市長崎町1-1に住んでいる福山さんのデータ
Ｄ〜Ｆ列2000行に長崎県長崎市長崎町1-1に住んでいる福山さんのデータ
があります。
2000行目のＡ〜Ｃには福山さんではないデータがあり
10000行目のＤ〜Ｆは空白になっております。
なので10000行目のＤ〜Ｆに2000行目のＤ〜Ｆにある福山のデータを持ってきて、10000行目のＡ〜Ｆに一列に同じ住所のデータを並べたいのです。
Ａ列の住所データと
Ｄ列の住所データを
比較して同一住所だった時にはＡ列を基準に並び替えをしたいのですが、手作業以外に良い方法はございませんか？
文章力がなくて申し訳ございません
1
2
3
4
5
6
:
現在、２つのBOOKに別れているデータを一元化したいと考えてえております。
１つは
Aに住所データ
Bに名前Cに会員番号
が入った37801人分のデータがございます。
２つ目は
Dに住所データ
EFにBCと異なる内容のデータ
が入った4544人分のデータがございます。
つまり
A〜CとD〜Fは全く異なるデータです。
同じ町のとある２つのデータなので
１つ目のデータと２つ目のデータで、同一住所のデータがあります。
A列D列と異なる列にあり、行もバラバラです。
しかし、ＡとＤで同一データ（同一住所）
例えば
Ａ〜Ｃ列10000行に長崎県長崎市長崎町1-1に住んでいる福山さんのデータ
Ｄ〜Ｆ列2000行に長崎県長崎市長崎町1-1に住んでいる福山さんのデータ
があります。
2000行目のＡ〜Ｃには福山さんではないデータがあり
10000行目のＤ〜Ｆは空白になっております。
なので10000行目のＤ〜Ｆに2000行目のＤ〜Ｆにある福山のデータを持ってきて、10000行目のＡ〜Ｆに一列に同じ住所のデータを並べたいのです。
Ａ列の住所データと
Ｄ列の住所データを
比較して同一住所だった時にはＡ列を基準に並び替えをしたいのですが、手作業以外に良い方法はございませんか？
文章力がなくて申し訳ございません
|
|
|
|
コメント(14)
目的とされる作業は、クエリーが使えると容易に行えるのですが、
そうでなくても「Excel 表 結合」あたりのキーワードで検索すると
幸せになれるかも知れません。
ただし自動的に表を結合する際の前提として、結合時のレコード選択用のキー
となるフィールド(今回は住所)の入力精度が十分高い必要があります。
そうでないと抜け・漏れが出る恐れがあります。
例えば、同一住所を示すテキストでも、様々な表現方法があるために
単純な一致比較が出来ない事が多いので注意が必要です。
データ入力時に十分気をつけていないと、様々なパターンを取り得るため
結局手作業に頼るしかないかも知れません。。。
○単純な比較のできない同じ住所を示すテキストのばらつきの例
・〜町1丁目23番地EXCELハイツ45号 (丁目、番地、建物名あり、半角)
・〜１−２３ ＥＸＣＥＬハイツ４５(全角数値とハイフン、建物名あり)
・〜1-23-45 (半角数値とハイフン、建物名なし)
そうでなくても「Excel 表 結合」あたりのキーワードで検索すると
幸せになれるかも知れません。
ただし自動的に表を結合する際の前提として、結合時のレコード選択用のキー
となるフィールド(今回は住所)の入力精度が十分高い必要があります。
そうでないと抜け・漏れが出る恐れがあります。
例えば、同一住所を示すテキストでも、様々な表現方法があるために
単純な一致比較が出来ない事が多いので注意が必要です。
データ入力時に十分気をつけていないと、様々なパターンを取り得るため
結局手作業に頼るしかないかも知れません。。。
○単純な比較のできない同じ住所を示すテキストのばらつきの例
・〜町1丁目23番地EXCELハイツ45号 (丁目、番地、建物名あり、半角)
・〜１−２３ ＥＸＣＥＬハイツ４５(全角数値とハイフン、建物名あり)
・〜1-23-45 (半角数値とハイフン、建物名なし)
ＶＢＡなら比較的楽にできるとおもいますが、Minonさんの言うとおり比較対象が同じパターンで入力されている必要があります。
例：鈴木 太郎と鈴木 太郎はスペースが半角、全角の差で同じと見なされない。
斉藤と斎藤など人によって入力が違う等
x=1
y=1
b=cells(y,10).value'bは10列目の1行目
do while b<>"" 'bが空欄でなければループ
b=cells(y,10).value'bは10列目の1行目
for i=1to 37801 '対象列の人数分比較を行う
a=cells(x,1).value'aは1列目の1行目
if b=a then
cells(x,2).value=y 'もしbがaと同じならaの右横のセルにｂの行数が入る
endif
x=x+1
next i
y=y+1
loop
試してませんが、流れはこんな感じになると思います。
例：鈴木 太郎と鈴木 太郎はスペースが半角、全角の差で同じと見なされない。
斉藤と斎藤など人によって入力が違う等
x=1
y=1
b=cells(y,10).value'bは10列目の1行目
do while b<>"" 'bが空欄でなければループ
b=cells(y,10).value'bは10列目の1行目
for i=1to 37801 '対象列の人数分比較を行う
a=cells(x,1).value'aは1列目の1行目
if b=a then
cells(x,2).value=y 'もしbがaと同じならaの右横のセルにｂの行数が入る
endif
x=x+1
next i
y=y+1
loop
試してませんが、流れはこんな感じになると思います。
こんなんでどうでしょ？
※データはバックアップしてからお試しください
Sub TEST()
Dim TmpA As String
Dim TmpB As String
Dim TmpC As String
Dim A_end As Integer
Dim D_end As Integer
Dim k As Integer
A_end = InputBox("A列の行数入力")
D_end = InputBox("D列の行数入力")
k = A_end + 1
For i = 1 To D_end
For j = 1 To A_end
TmpA = ""
TmpB = ""
TmpC = ""
If Range("D" & i) = Range("A" & j) Then
TmpA = Range("A" & i)
TmpB = Range("B" & i)
TmpC = Range("C" & i)
Range("A" & i) = Range("A" & j)
Range("B" & i) = Range("B" & j)
Range("C" & i) = Range("C" & j)
Range("A" & j) = TmpA
Range("B" & j) = TmpB
Range("C" & j) = TmpC
End If
Next j
If Range("A" & i) <> Range("D" & i) Then
Range("D" & k) = Range("D" & i)
Range("E" & k) = Range("E" & i)
Range("F" & k) = Range("F" & i)
Range("D" & i) = ""
Range("E" & i) = ""
Range("F" & i) = ""
k = k + 1
End If
Next i
End Sub
※データはバックアップしてからお試しください
Sub TEST()
Dim TmpA As String
Dim TmpB As String
Dim TmpC As String
Dim A_end As Integer
Dim D_end As Integer
Dim k As Integer
A_end = InputBox("A列の行数入力")
D_end = InputBox("D列の行数入力")
k = A_end + 1
For i = 1 To D_end
For j = 1 To A_end
TmpA = ""
TmpB = ""
TmpC = ""
If Range("D" & i) = Range("A" & j) Then
TmpA = Range("A" & i)
TmpB = Range("B" & i)
TmpC = Range("C" & i)
Range("A" & i) = Range("A" & j)
Range("B" & i) = Range("B" & j)
Range("C" & i) = Range("C" & j)
Range("A" & j) = TmpA
Range("B" & j) = TmpB
Range("C" & j) = TmpC
End If
Next j
If Range("A" & i) <> Range("D" & i) Then
Range("D" & k) = Range("D" & i)
Range("E" & k) = Range("E" & i)
Range("F" & k) = Range("F" & i)
Range("D" & i) = ""
Range("E" & i) = ""
Range("F" & i) = ""
k = k + 1
End If
Next i
End Sub
x=1
y=1
b=cells(y,10).value'bは10列目の1行目
do while b<>"" 'bが空欄でなければループ
b=cells(y,10).value'bは10列目の1行目
for i=1to 37801 '対象列の人数分比較を行う
a=cells(x,1).value'aは1列目の1行目
if b=a then
cells(x,2).value=y 'もしbがaと同じならaの右横のセルにｂの行数が入る
endif
x=x+1
next i
x=1
y=y+1
loop
同じ内容の比較ならうまくいきました
あとは、bの列とaの列を指定すればマクロに入れるだけで使えると思います
y=1
b=cells(y,10).value'bは10列目の1行目
do while b<>"" 'bが空欄でなければループ
b=cells(y,10).value'bは10列目の1行目
for i=1to 37801 '対象列の人数分比較を行う
a=cells(x,1).value'aは1列目の1行目
if b=a then
cells(x,2).value=y 'もしbがaと同じならaの右横のセルにｂの行数が入る
endif
x=x+1
next i
x=1
y=y+1
loop
同じ内容の比較ならうまくいきました
あとは、bの列とaの列を指定すればマクロに入れるだけで使えると思います
>>12 メモリー さん
もし現状でACCESSをお持ちでなくて、今回の目的のためだけにACCESSを導入
されるのでしたら勿体無い気がします。
今回のケースは既にみなさんの提示されたVBAの操作で実現できると思いますので、
その方が費用的にも安上がりかと思います。
もちろん、今回を機にデータの管理ツールをExcelベースからより本格的な
データベースのACCESSベースに移行されるのでしたら、手間と費用はかかりますが
有効な選択だと思います。
また、Office製品には、ACCESSのデータ抽出機能の縮小版とも言える
Microsoft Query(MSクエリー)なるツールが付属されています。
Officeの標準インストールした場合はインストールされなかったと
思いますので、追加インストールが必要となりますが、こちらを使う事で
(今回の課題においては)ACCESSを用いるのと同様の抽出操作が行えます。
試されてみては如何でしょう？
(EXCEL単品に付属していたかどうかは分かりませんが、ACCESSを含まない
Officeパーソナルエディションなどでも付属しているのは確かです。)
もし現状でACCESSをお持ちでなくて、今回の目的のためだけにACCESSを導入
されるのでしたら勿体無い気がします。
今回のケースは既にみなさんの提示されたVBAの操作で実現できると思いますので、
その方が費用的にも安上がりかと思います。
もちろん、今回を機にデータの管理ツールをExcelベースからより本格的な
データベースのACCESSベースに移行されるのでしたら、手間と費用はかかりますが
有効な選択だと思います。
また、Office製品には、ACCESSのデータ抽出機能の縮小版とも言える
Microsoft Query(MSクエリー)なるツールが付属されています。
Officeの標準インストールした場合はインストールされなかったと
思いますので、追加インストールが必要となりますが、こちらを使う事で
(今回の課題においては)ACCESSを用いるのと同様の抽出操作が行えます。
試されてみては如何でしょう？
(EXCEL単品に付属していたかどうかは分かりませんが、ACCESSを含まない
Officeパーソナルエディションなどでも付属しているのは確かです。)
VLOOKUP,VBA,accessのクリエ
どれでも出来ると思いますが、 accessがなく、VBAも知らないとなると、
VLOOKUPで十分だと思います。
というか、わざわざ難しい方法を教える意味が分かりません。
問題なのは、2つのデータの住所表記が完全に一致していなければなりません。
この問題を解決するのは、VLOOKUPでも,VBAでも,accessのクリエでもありません。
2つめのデータの住所が、1つめのデータに完全に一致するものがあるかどうか
の判定(COUNTIFとかSUMPRODUCT)をして、
＞4 べべーあさんの手法(置換)
＋手動で、全て完全一致させることができれば、この問題は解決します。
住所でソートしておくと、良いでしょう。
ここで、どれだけ簡単に完全一致させられるかが鍵となるでしょう。
問題(完全一致)が解決すれば、後は、VLOOKUP,VBA,accessのクリエのどれでも
出来ます。
どれでも出来ると思いますが、 accessがなく、VBAも知らないとなると、
VLOOKUPで十分だと思います。
というか、わざわざ難しい方法を教える意味が分かりません。
問題なのは、2つのデータの住所表記が完全に一致していなければなりません。
この問題を解決するのは、VLOOKUPでも,VBAでも,accessのクリエでもありません。
2つめのデータの住所が、1つめのデータに完全に一致するものがあるかどうか
の判定(COUNTIFとかSUMPRODUCT)をして、
＞4 べべーあさんの手法(置換)
＋手動で、全て完全一致させることができれば、この問題は解決します。
住所でソートしておくと、良いでしょう。
ここで、どれだけ簡単に完全一致させられるかが鍵となるでしょう。
問題(完全一致)が解決すれば、後は、VLOOKUP,VBA,accessのクリエのどれでも
出来ます。
- mixiユーザー
- ログインしてコメントしよう！
|
|
|
|
Excel（エクセル）活用 更新情報
Excel（エクセル）活用のメンバーはこんなコミュニティにも参加しています
人気コミュニティランキング
- 1位
- 相棒
- 59204人
- 2位
- 30代〜40代から友達作り【友活】
- 35840人
- 3位
- 40歳以上のおじさん&おばさん
- 14764人