こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセルデータ照合

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

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万件です。
宜しくお願いいたします。

投稿日時 - 2011-06-15 03:11:19

QNo.6810481

困ってます

質問者が選んだベストアンサー

 今仮に、各シートにおいて、校名のデータの列が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行目以下に貼り付けて下さい。

 以上です。

投稿日時 - 2011-06-15 05:43:00

お礼

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

投稿日時 - 2011-06-16 01:45:09

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

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

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

投稿日時 - 2011-06-15 14:20:29

お礼

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

投稿日時 - 2011-06-16 01:37:46

ANo.3

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

投稿日時 - 2011-06-15 13:33:05

お礼

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

投稿日時 - 2011-06-16 01:41:11

ANo.2

エクセルにはデータの「統合」があり、は別の機能でも使う。
ひょうだいとしては、「検索」。
Sheet12(のA列)を中心にして、Sheet1のA列にデータがあるかどうか「検索」し、在れば、同行の隣列のデータを持ってくる。タイプとしては、「検索」なんだ。
ーー
エクセルには検索に適したかんすうは、2つしかない。
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、・・に指定する。

投稿日時 - 2011-06-15 10:57:30

お礼

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

投稿日時 - 2011-06-16 01:43:24

あなたにオススメの質問