- 締切済み
エクセル 一致項目を基準にデータを合わせたい
特定の1項目を一致させ、2つのシートのデータを一つにまとめたいのですが、エクセル初心者にもできる方法があったら教えてください。行数は20000件位です。列は10列程度です。 番号 あ い う 1001 aa bb a 1002 sd bb sd 1003 aa bb aa 1004 sd bb sd 1005 aa bb aa 番号 か き 1002 abc bbs 1004 sdb gds 1005 aab bba を、まとめて 番号 あ い う か き 1001 aa bb a 1002 sd bb sd abc bbs 1003 aa bb aa 1004 sd bb sd sdb gds 1005 aa bb aa aab bba といったように番号行数(2万件位)が多いデータを 番号を一致させてまとめたいのです。初心者にもできる簡単な方法があったら教えていただけると大変助かります。
- bears
- お礼率100% (12/12)
- オフィス系ソフト
- 回答数6
- ありがとう数8
- みんなの回答 (6)
- 専門家の回答
みんなの回答
- tom04
- ベストアンサー率49% (2537/5117)
こんばんは! すでに同じような回答は出ていると思いますが・・・ 一例です Sheet1のA1~D6に「う」列までのデータが入力されていて そのSheetのE列以降に、Sheet2のA1~C4に入っている「か」「き」列のデータを追加する場合です。 E1セル(「か」が入るセル) =IF(COUNTIF(Sheet2!$A$1:$A$4,A1)<1,"",VLOOKUP(A1,Sheet2!$A$1:$C$4,2,0)) F1セル(「き」が入るセル) =IF(COUNTIF(Sheet2!$A$1:$A$4,A1)<1,"",VLOOKUP(A1,Sheet2!$A$1:$C$4,3,0)) E1・F1セル両方を範囲指定 → F1セル右下のオートフィルマーク(+)でダブルクリック! これで希望通りの表示にならないでしょうか? ただし、列数が多いと結構手間がかかると思います。 行数は多くても問題ありませんけどね! Excel2003での回答でした。 参考になれば幸いです。m(__)m
A B C D E F 1 番号 あ い う か き 2 1001 aa bb a 3 1002 sd bb sd abc bbs 4 1003 aa bb aa 5 1004 sd bb sd sdb gds 6 1005 aa bb aa aab bba 7 8 番号 か き 9 1002 abc bbs 10 1004 sdb gds 11 1005 aab bba E2: =IF(COUNTIF($A$9:$A$11,$A2),VLOOKUP($A2,$A$9:$C$11,COLUMN(B2),0),"")
お礼
やはり超初心者には関数の使い方が十分理解できませんでした。もう少しトライしてみます。 ありがとうございました。
- wisemac21
- ベストアンサー率39% (171/429)
数式を使わず編集作業で行う方法 Sheet1 ┌─┬──┬─┬─┬─┬─┬─┐ │ │ A │ B│ C│ D│ E│ F│ ├─┼──┼─┼─┼─┼─┼─┤ │1 │番号│あ│い│う│か│き│ │2 │1001│aa│bb│a │ │ │ │3 │1002│sd│bb│sd│ │ │ │4 │1003│aa│bb│aa│ │ │ │5 │1004│sd│bb│sd│ │ │ │6 │1005│aa│bb│aa│ │ │ └─┴──┴─┴─┴─┴─┴─┘ Sheet2 Sheet1にある列を挿入して、空き列を作成 ┌─┬──┬─┬─┬─┬──┬──┐ │ │ A │ B│ C│ D│ E │ F │ ├─┼──┼─┼─┼─┼──┼──┤ │1 │番号│ │ │ │か │き │ │2 │1002│ │ │ │abc │bbs │ │3 │1004│ │ │ │sdb │gds │ │4 │1005│ │ │ │aab │bba │ └─┴──┴─┴─┴─┴──┴──┘ Sheet3 Sheet1とSheet2のデータをコピーして貼り付ける ┌─┬──┬─┬─┬─┬──┬──┐ │ │ A │ B│ C│ D│ E │ F │ ├─┼──┼─┼─┼─┼──┼──┤ │1 │番号│あ│い│う│か │き │ │2 │1001│aa│bb│a │ │ │ │3 │1002│sd│bb│sd│ │ │ │4 │1003│aa│bb│aa│ │ │ │5 │1004│sd│bb│sd│ │ │ │6 │1005│aa│bb│aa│ │ │ │7 │1002│ │ │ │abc │bbs │ │8 │1004│ │ │ │sdb │gds │ │9 │1005│ │ │ │aab │bba │ └─┴──┴─┴─┴─┴──┴──┘ A列基準にして昇順に並び替える ┌─┬──┬─┬─┬─┬──┬──┐ │ │ A │ B│ C│ D│ E │ F │ ├─┼──┼─┼─┼─┼──┼──┤ │1 │番号│あ│い│う│か │き │ │2 │1001│aa│bb│a │ │ │ │3 │1002│sd│bb│sd│ │ │ │4 │1002│ │ │ │abc │bbs │ │5 │1003│aa│bb│aa│ │ │ │6 │1004│sd│bb│sd│ │ │ │7 │1004│ │ │ │sdb │gds │ │8 │1005│aa│bb│aa│ │ │ │9 │1005│ │ │ │aab │bba │ └─┴──┴─┴─┴─┴──┴──┘ セル範囲E3:F3を選択して、右クリック、削除を選択、削除ダイアログボックスで、「上方向にシフト」で 同じ番号のデータを揃える。 次に不要となった行を削除するために B列を選択して、F5キーをおし、セル選択ボタンを押し、選択オプションダイアログボックスで 空きセルを選択して、OKをクリック。 その状態で編集→削除で、削除ダイアログボックスで行全体を選択して、OKをクリック
お礼
行数が少ない場合は初心者にとってわかりやすいですね。ありがとうございました。
- 某HN クロメート(Chromate)(@CoalTar)
- ベストアンサー率40% (705/1742)
回答No2のhallo-2007さんとほぼ同じですが、計算速度がほしい場合で かつ、番号が昇順の場合です G2セルに =MATCH(A2,$B$9:$B$11) E2セルに =IF(ISNA($G2),"",IF($A2<>INDEX($B$9:$B$11,$G2),"",INDEX(C$9:C$11,$G2))) 右へオートフィル E2:G2セルを下へオートフィル 参考まで
お礼
やはり超初心者には関数の使い方が十分理解できませんでした。もう少しトライしてみます。 ありがとうございました。
- hallo-2007
- ベストアンサー率41% (888/2115)
関数案ですが、 番号 あ い う か き 1001 aa bb a 1002 sd bb sd と準備して、かの列には =IF(COUNTIF(もう一方のデータ!A:A,A1)>0,VLOOKUP(A1,もう一方のデータ!A:C,2,FALSE),"") きの列は =IF(COUNTIF(もう一方のデータ!A:A,A1)>0,VLOOKUP(A1,もう一方のデータ!A:C,3,FALSE),"") 下へコピィします。
お礼
これでできそうかなあと思いましたが、やはり超初心者には関数の使い方が十分理解できませんでした。もう少しトライしてみます。 ありがとうございました。
関連するQ&A
- excel データ抽出方法
こんいちは 列1と列2を比べて条件が一致したデータを抽出したいのですが,方法がわかりません。どなたかご存知のかた教えていただけないでしょうか? 列1 列2 aa vv bb dd cc ff dd aa ee bb この場合aa,bb が一致する。
- ベストアンサー
- オフィス系ソフト
- 条件に一致したデータ数について
該当する商品の合計数を所得したいのですが検出方法が分かりませんので教えて下さい。 商品マスタ(A)と売上げデータ(B)に下記のデータがあります。AとBの関係は、1:nになります。 Bの商品とAの商品が一致した場合にAの商品が売れたと判断します。 最終的にAの一致した商品数を取得したいのですが、Bの検出数が反映されてしまい正常に一致した件数を取得できません。いろいろ試みましたけどダメでした。どなたか、ご享受下さい。 ---------------- 商品マスタ(A) ---------------- AA BB CC DD EE FF ---------------- 売上げデータ(B) ---------------- AA 06/9/10 BB 06/9/10 BB 06/9/11 DD 06/9/12 AA 06/9/14 BB 06/9/14 AA 06/9/15 AA 06/9/16 BB 06/9/17 BB 06/9/17 BB 06/9/18 ---------------- 結果 ---------------- 3 ※--- 補 足 --- Bを商品毎にグループ化してカウントすると、 AA = 4件 BB = 6件 CC = 0件 DD = 1件 EE = 0件 FF = 0件 になります。売上げ件数が、0件以上の商品は、AA、BB、DDの3商品になります。この3の値を取得したい訳です。
- ベストアンサー
- SQL Server
- シート1とシート2のデータ一致 抽出方法
エクセルのデータ抽出でお知恵をお貸し下さい。 シート1 A列 B列 C列 品名 製品番号 数量 AA 1AAAA 200 BB 1BBBB 250 CC 1CCCC 300 シート2 A列 B列 C列 品名 製品番号 数量 AA 1AAAA 200 BB 1BBBB 250 AB AAAA 500 CC 1CCCC 300 上記のようなシートがあります。 内容は同じなのですが、シート2にはシート1にはない品名、製品番号、数量が含まれます。 シート1の品名、製品番号、数量がすべて一致するものをシート3へ抽出し、 一致しなかったデータ(シート1にはなく、シート2にあるものを含め)をシート4へ抽出したいのですが 何か良い方法はないでしょうか??
- 締切済み
- その他(業務ソフトウェア)
- エクセルでデータを自動的にコピーする方法を教えてください。
1、AのエクセルファイルのAAの列に車体番号が入力されている。 2、BのエクセルファイルのBBの列にも車体番号が入力されている。 3、BのエクセルファイルのCCのは登録番号が記入されている。 4、AのファイルのAAの列の車体番号がBファイルのBBの列の車体番号と合致する時、CCの列の登録番号がAのエクセルファイルのDDの列にコピーされる様にしたいです。 こんなこと出来るのでしょうか??
- ベストアンサー
- その他MS Office製品
- エクセル 条件範囲データと特定のセルの部分一致
こちらどなたかご存知の方教えていただけますでしょうか。 A列 (3文字) 1. ABC 2. DEF 3. AXE 4. MNE 5. BEH 6. LXE B列 1. AA 2. BB 3. CC 4. DD 5. EE 6. FF C列 1. NXE/MME 2. LEC/CCE/BNE/ZEX 3. DEF/CCE/BBE 4. MME/ABC/EFB 5. EEA/BBC/EEA 6. XXA/DEF/AXE/XEA D列 1. X 2. X 3. CC 4. DD 5. X 6. FF A列、B列がともにデータベース、C列のスラッシュで区切られた3文字が毎回集計に必要な情報になります。このC列の各行のいずれかの3文字がA列のデータベースに一致したら同行のB列の文字をD列に表示させたいのです。 例えば、1行目C列のNXE/MMEはA列のデータとは一致しないのでD列にはX(該当なし)を表示、C列3行目の文字郡のなかにはDEFがあり、A列のデータの中にも含まれているのでD列には同行B列のCCを表示させる。 一般的な関数、D列にB列の情報が指定できなくても0, 1など数字で有り無しがわかればよかったのですが、countif関数でも部分一致はできずエラーとなってしまいました。vlookup, index matchでも部分一致ができないのですが、何か方法ありますでしょうか?
- ベストアンサー
- オフィス系ソフト
- エクセルでのデータ抽出について
こんにちは!関数初心者です・・・。 どうしてもできなくて教えていただきたいのですが、たくさんのデータの中から一致するデータを表示したいのです。 下記のようなデータがあったとき、検索する文字列を入れるセルをA1とします。検索結果をB1に表示したいのです。 たとえば品目にある「鉛筆」の商品ID「AA-001」を探したいとき、セルA1に「鉛筆」と入力したら、セルB1に「AA-001」と表示したいのですが、どうしてもうまくいきません。 商品ID 業者NO 科目 品目 単価 AA-001 スズキ100 文具 鉛筆 100 AA-002 スズキ200 文具 ノート 200 BB-001 タナカ100 食品 卵 100 BB-002 タナカ200 食品 チーズ 200 CC-001 サトウ100 衣類 シャツ 100 CC-002 サトウ200 衣類 スーツ 200 どなたかおわかりになりましたら教えてください!! よろしくお願いいたします!
- ベストアンサー
- オフィス系ソフト
- SQLで条件の文字列を含んでいるデータを取得する方法
SQLにて指定した文字列を含むデータを取得する方法を探しています。 例 テーブル Test 番号 条件 __________ 01 AA 02 BB 03 AA,CC 上記のように、番号と条件という2つのフィールドを持っているテーブルをTestとします。 (1)条件にAAと指定すると、番号01と03が取得。 (2)条件にBBと指定すると、番号02が取得。 (3)条件にAAAと指定すると、一致条件なし。 (1)~(3)のような条件を満たすSQLの構文はありませんでしょうか? ご教授お願いいたします。
- 締切済み
- MySQL
- エクセル マクロ 文字列からの行数の取得
エクセルでマクロ初心者です。よろしくお願いします。例えば1列目にデータ名が入力されていてその個数が可変である場合(小計は末尾ではありません)小計行の1列目から数式をいれたいのですが、行数が固定でないため小計と書かれたセルの行数を取得したいのですが、わかりません。いい方法をお願いします。 A B C D データ aa bb cc 小計 ・ ・
- ベストアンサー
- オフィス系ソフト
- 一致する項目が最も多いものを抽出する方法は?
エクセルの初心者です。 関数で今日の注文を入力すると過去の注文から 今日の注文と項目が一致する または一致する項目が最も多い注文番号を抽出する方法をおしえてください。 Sheet1 注文番号 0924 項目1 A-1 項目2 A-5 項目3 B-3 項目4 C-7 上が今日の注文とします。 これを下の過去の注文リストと照合させて一致するまたは一致する項目の最も多い 注文番号を抽出したいのです。 Sheet2 注文番号 0920 0919 0918 0917 項目1 A-2 A-1 B-2 A-1 項目2 A-5 B-1 B-3 A-5 項目3 B-3 C-1 B-1 項目4 B-4 D-1 B-3 項目5 C-7 項目の数量は日によって変わります。 Sheet1と2は同一シートにすることも可能です。 抽出する項目は上からABC順などに並んでいないと不可能でしょうか。 ご多忙の中申し訳ございませんがよろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- Excelマクロ 複数条件一致データの抽出方法
お世話になります。 2個の条件に一致するものを別シートに抽出したいのですが、お知恵を貸してください。 Excelシートで下記のような表があります。 これをL列(品名)かつS列(品質)の条件に一致するデータで新しいシートを作成したいのですが、 その際に新しいシート名は"AA1"のようにしたいのです。 条件がC列(品名)だけであれば下記で動いたのですが…。 (データ) A列 入荷日 I列 品目コード L列 品名 S列 品質 V列 在庫 A・・・・・・I・・・・・・・・・L・・・・・・・・・S・・・・・・・・・V 1/1・・・・01・・・・・・・AA・・・・・・・・1・・・・・・・・・20 2/2・・・・01・・・・・・・AA・・・・・・・・1・・・・・・・・・10 2/3・・・・01・・・・・・・AA・・・・・・・・2・・・・・・・・・10 2/3・・・・01・・・・・・・AA・・・・・・・・2・・・・・・・・・10 2/3・・・・01・・・・・・・BB・・・・・・・・1・・・・・・・・・10 ※以下、最大100品目の行数10000程です。 ↓↓ (実行後希望) シート名 AA1 A・・・・・・I・・・・・・・・・L・・・・・・・・・S・・・・・・・・・V 1/1・・・・01・・・・・・・AA・・・・・・・・1・・・・・・・・・20 2/2・・・・01・・・・・・・AA・・・・・・・・1・・・・・・・・・10 シート名 AA2 A・・・・・・I・・・・・・・・・L・・・・・・・・・S・・・・・・・・・V 2/3・・・・01・・・・・・・AA・・・・・・・・2・・・・・・・・・10 2/3・・・・01・・・・・・・AA・・・・・・・・2・・・・・・・・・10 シート名 BB1 A・・・・・・I・・・・・・・・・L・・・・・・・・・S・・・・・・・・・V 2/3・・・・01・・・・・・・BB・・・・・・・・1・・・・・・・・・10 Sub Sheet抽出() Dim i As Long, Lstrow As Long, myName As String Dim MySht As Worksheet, myFlg As Boolean Application.ScreenUpdating = False With Sheets("sheet1") '準備 For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row myName = .Cells(i, 9) 'シートの存在確認 For Each MySht In Worksheets If MySht.Name = myName Then myFlg = True '既にシート在り!! Sheets(myName).Range("a1") _ .CurrentRegion.Offset(1).ClearContents Exit For End If Next '新規シートの追加 If myFlg = False Then Worksheets.Add.Name = myName End If With Sheets(myName) .Range("A1") = "入荷日" .Range("I1") = "品名コード" .Range("L1") = "品名" .Range("S1") = "品質" .Range("V1") = "在庫" End With myFlg = False Next 'データの転記 For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row myName = .Cells(i, 9).Value .Range("A" & i & ":V" & i).Copy _ Sheets(myName).Cells(Rows.Count, 1).End(xlUp).Offset(1) With Sheets(myName) .Activate Lstrow = .Cells(Rows.Count, 1).End(xlUp).Row .Cells(Rows.Count, 1).End(xlUp).Offset(1, 21) = "" .Cells(Rows.Count, 1).End(xlUp).Offset(1, 21) = _ "=SUM(v2:V" & Lstrow & ")" End With Next End With Sheets("Sheet1").Activate Application.ScreenUpdating = True End Sub 実行後希望のように抽出するには、どうすれば良いのでしょうか? よろしくお願いいたします。
- ベストアンサー
- Visual Basic
お礼
エクセルの基本も知らない私にとってやはり初心者過ぎて無理のようです。せっかくアドバイスいただきましたのに申し訳ありません。