エクセルデータ照合

質問

エクセルのデータ照合によりデータの統合をしたいです。

Sheet1

A校　50%
B校　30%
C校　40%
D校　30%
E校　20%

Sheet2
B校
C校
E校
F校

Sheet1 とSheet2 の学校を照合して共通校のみを抽出しSheet3として
B校　30%
C校　40%
E校　20%

データ数は約1万件です。
宜しくお願いいたします。

回答No.1

kagakusuki · ベストアンサー

今仮に、各シートにおいて、校名のデータの列がA列、パーセンテージの値の列がB列であるものとします。

　まず、適当な空きシート（例えばSheet4）のA1セルに、次の数式を入力して下さい。

=IF(COUNTIF(Sheet2!$A:$A,INDEX(Sheet1!$A:$A,ROW()))=0,"",ROW())

　次に、Sheet4のA1セルをコピーして、Sheet4のA2以下に貼り付けて下さい。
　次に、Sheet3のA1セルに、次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNT(Sheet4!$A:$A),"",INDEX(Sheet1!A:A,SMALL(Sheet4!$A:$A,ROWS($1:1))))

　次に、Sheet3のA1セルをコピーして、Sheet3のB1セルに貼り付けて下さい。
　次に、Sheet3のA1～B1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　以上です。

お礼

作業の内容がよく理解できないのですが、素人の私にとっては、最も単純な作業でした。
ありがとうございました。

回答No.4

mu2011

カテゴリがＶＢなのでエクセルのＶＢＡ例です。
Sheet3のシートタブ上で右クリック→コードの表示→sampleコードを貼り付け→Ｆ５キー押下

Sub sample()
On Error Resume Next
st2Rng = Sheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Row
st2 = Sheets("sheet2").Range("a1:b" & st2Rng)
With Sheets("sheet1")
For i = 1 To st2Rng
st2(i, 2) = WorksheetFunction.VLookup(st2(i, 1), .Range("A:B"), 2, False)
Next
End With
With Sheets("sheet3")
.Cells.Clear
For i = 1 To st2Rng
If st2(i, 2) <> "" Then
.Range("A1").Offset(k, 0) = st2(i, 1)
.Range("A1").Offset(k, 1) = st2(i, 2)
.Range("A1").Offset(k, 1).NumberFormatLocal = "0%"
k = k + 1
End If
Next
End With
End Sub

お礼

ありがとうございました。VBも勉強していきたいと思います。

回答No.3

merlionXX

Visual Basicのカテゴリーでの質問ですのでVBAでやってみました。
データが1万もあるなら、単純にセルデータを順列組み合わせで見ていくと時間がかかります。
そのため一旦データを配列に取り込んで配列内で照合させてみました。
お試しください。

Sheet1は
A列に校名、B列に％
Sheet2は
A列に校名
Sheet1、Sheet2の両方に存在する校名を抜き出し、Sheet3のA,B列に転記するコードです。

Sub test01()
　　Dim ws(1 To 3) As Worksheet
　　Dim myV, myW, myX
　　Dim i As Long, j As Long, n As Long
　　Set ws(1) = Sheets("Sheet1")
　　Set ws(2) = Sheets("Sheet2")
　　Set ws(3) = Sheets("Sheet3")
　　myV = ws(1).Range("A1:B" & ws(1).Cells(Rows.Count, "B").End(xlUp).Row).Value
　　myW = ws(2).Range("A1:A" & ws(2).Cells(Rows.Count, "A").End(xlUp).Row).Value
　　ReDim myX(1 To UBound(myW, 1), 1 To 2)
　　For i = 1 To UBound(myW, 1)
　　　　For n = 1 To UBound(myV, 1)
　　　　　　If myW(i, 1) = myV(n, 1) Then
　　　　　　　　j = j + 1
　　　　　　　　myX(j, 1) = myV(n, 1)
　　　　　　　　myX(j, 2) = myV(n, 2)
　　　　　　End If
　　　　Next n
　　Next i
　　ws(3).Range("A:B").ClearContents
　　ws(3).Range("A1").Resize(UBound(myX, 1), 2).Value = myX
End Sub

お礼

とてもスムーズに実行することができました。
勉強になりますありがとうございました。

回答No.2

imogasi

エクセルにはデータの「統合」があり、は別の機能でも使う。
ひょうだいとしては、「検索」。
Sheet１２（のA列）を中心にして、Sheet1のA列にデータがあるかどうか「検索」し、在れば、同行の隣列のデータを持ってくる。タイプとしては、「検索」なんだ。
ーー
エクセルには検索に適したかんすうは、２つしかない。
MATCH関数とVLOOKUP関数だ。
ーー
あとSheet1に該当がない場合をどう見つけるか
という課題も同時に出てくる。
あと、他シートのデータを参照する書き方の問題もある。
ーー
VLOOKUPでやってみると
例データ　Sheet1
A校 50%
B校 30%
C校 40%
D校 30%
E校 20%
Sheet2でB2に
=VLOOKUP(A2,Sheet1!$A$2:$B$100,2,FALSE)
結果
B校 0.3
C校 0.4
E校 0.2
F校 #N/A
まず、B列の表示形式を％に設定する。
#N/A　がSheet1のA列に該当無しの場合だが、VLOOKUPの場合は
=IF(ISNA(VLOOKUP(A2,Sheet1!$A$2:$B$100,2,FALSE)),"",VLOOKUP(A2,Sheet1!$A$2:$B$100,2,FALSE))
のようにする。（該当無しは空白のしているが、"該当なし"なんてのも出せる。
普通はA2が空白だったら、空白を返す、という部分を入れるのだが、この場合はたまたま不要。
ーー
もっとSheet1の項目数(=列数）が多い場合は(sheet2にも複数項目を持ってくる場合）Sheet1!$A$2:$B$100の$Bの部分を変えて、またFALSEの前の2を3，4、・・に指定する。

お礼

タイプは「検索」なのですね。基本的なところから教えてもらって、感謝です。
わかりやすい丁寧なご説明ありがとうございました。