- ベストアンサー
エクセル マクロ? 関数? データの抜き出しと
エクセル2010を使っています。 画像のオレンジの部分の様なデータがあります。 A列とB列に重複したデータが複数有り、D列に重複なしの氏名(A列のデータの重複なし)を表記しました。 D列の上からの氏名のデータをA列から探して該当する氏名のB列のデータを、E列、F列、G列と右に表示したいと思います。 A列B列は現状で16000行ほど。 D列は重複は無く、900行ほどあります。 ですので出来るだけ負担の無い形で抜き出したいです。 詳しい方、よろしくお願い致します。
- gekikaraou
- お礼率41% (282/678)
- Excel(エクセル)
- 回答数4
- ありがとう数3
- みんなの回答 (4)
- 専門家の回答
質問者が選んだベストアンサー
質問にある添付図の構成でE列から右に出力します。(当方Excel2010です) データ数が多いとのことなので、シートとのI/Oを減らし、Dictionaryオブジェクトを使い、短くしたつもりです。テストデータ1万件程度で1秒弱でした。 何回も行うと、E列より右に上書きするかもしれません。メッセージが出るので「OK」してください。最初、E列より右をクリアしておけばメッセージは出ません。 「D列に重複なしの氏名」ということを信用して、あまりエラー対応をしていません。ご容赦を。 シートのコードウィンドウに貼り付けます。 Sub TotalTest() Dim myDic As Object 'Dictionaryオブジェクト Dim myVal, myVal2 'シートの値 Dim i As Long, idx As Long 'カウンタ 'Dictionaryオブジェクト Set myDic = CreateObject("Scripting.Dictionary") '出力欄を取り込む myVal = Range("D2", Range("D" & Rows.Count).End(xlUp)).Value For i = 1 To UBound(myVal) myDic.Add myVal(i, 1), i + 1 Next '出力欄をクリアし確保(myValを再利用) Range("E:E").ClearContents myVal = Range("E2", "E" & UBound(myVal) + 1).Value 'データを取り込み振り分ける(メモリー上でカンマでつなげる) myVal2 = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value For i = 1 To UBound(myVal2) idx = myDic.Item(myVal2(i, 1)) - 1 If myVal(idx, 1) = "" Then myVal(idx, 1) = myVal2(i, 2) Else myVal(idx, 1) = myVal(idx, 1) & "," & myVal2(i, 2) End If Next '出力(カンマ区切り) Range("E2", Range("E" & UBound(myVal) + 1)) = myVal 'セルに分割 Columns("E:E").Select Selection.TextToColumns Destination:=Range("E1"), Comma:=True Range("E1").Select Set myDic = Nothing End Sub
その他の回答 (3)
- tom04
- ベストアンサー率49% (2537/5117)
こんにちは! すでに回答は出ていますが、VBAでの一例です。 質問では同じSheetのD列以降に表示したい!というコトですが、Sheet2に表示するようにしてみました。 Alt+F11キー → メニュー → 挿入 → 標準モジュール → VBE画面のカーソルが点滅しているところに ↓のコードをコピー&ペースト → Excel画面に戻りマクロを実行してみてください。 (Alt+F8キー → マクロ → マクロ実行です) Sub Sample1() 'この行から Dim i As Long, lastRow As Long, wS As Worksheet Set wS = Worksheets("Sheet2") With Worksheets("Sheet1") lastRow = .Cells(Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False wS.Cells.Clear .Range("A:A").AdvancedFilter Action:=xlFilterCopy, copytorange:=wS.Range("A1"), unique:=True For i = 2 To wS.Cells(Rows.Count, "A").End(xlUp).Row .Range("A1").AutoFilter field:=1, Criteria1:=wS.Cells(i, "A") Range(.Cells(2, "B"), .Cells(lastRow, "B")).SpecialCells(xlCellTypeVisible).Copy wS.Cells(i, "B").PasteSpecial Paste:=xlPasteAll, Transpose:=True Next i .AutoFilterMode = False wS.Columns.AutoFit wS.Activate Application.ScreenUpdating = True MsgBox "処理完了" End With End Sub 'この行まで ※ 関数でないのでデータ変更があるたびにマクロを実行する必要があります。m(_ _)m
お礼
回答ありがとうございます。 助かりました。
- bunjii
- ベストアンサー率43% (3589/8248)
関数を使う場合は次の数式が良いでしょう。 =IFERROR(INDEX($B:$B,SUMPRODUCT(LARGE(($A:$A=$D2)*ROW($A:$A),COUNTIF($A:$A,$D2)-COLUMN(A1)+1)),1),"") >A列B列は現状で16000行ほど。 >D列は重複は無く、900行ほどあります。 >ですので出来るだけ負担の無い形で抜き出したいです。 動作の状況によって自動計算を止めて手動計算に変更すれば入力時のストレスを軽減できます。 手動計算のときは「ファイル」タブの「オプション」で「数式」の「手動」で「ブックの保存前に再計算を行う」にチェックしてあれば再開のときに最新状態で表示されます。 尚、編集途中での再計算の実行はF9キーです。
お礼
回答ありがとうございます。 関数でも出来るのですね、自分なりに勉強してみたいと思います。
- Cupper-2
- ベストアンサー率29% (1342/4565)
|ω・`)っ「ピボットテーブル」
関連するQ&A
- エクセル2007 データ抽出(関数)について
教えてください。エクセルシートにて郵便番号と住所が重複しているデータを抽出したいのです。 例)ある顧客の情報として、シート1のA列に、1から10までの新規顧客の氏名、B列には生年月日が入力されています。C列,D列は空白で、E列には全ての顧客(新規顧客を含みます)の氏名、F列には生年月日、G列には郵便番号、H列には住所が入力されています。このふたつの情報の中から、新規顧客データと全ての顧客データの氏名、生年月日が重複している顧客のみ、C列にその郵便番号、D列に住所を表示させたいのです。 OSはXP エクセル2007です。 困っています。よろしくお願いします。
- ベストアンサー
- Windows XP
- EXCELでの抜き出し
A列 B列 C列 D列 1行目 A0001 ○○荘 101号室 2007/3/21 2行目 A0001 ○○荘 201号室 2007/3/25 3行目 B0001 ××荘 101号室 2007/3/24 4行目 B0001 ××荘 102号室 空白 5行目 B0001 ××荘 201号室 2007/3/24 6行目 B0001 ××荘 202号室 2007/3/25 という表があります。 別のシートでA1のセルにA0001と入れると 3行目からA0001の物件の全部屋のD列までが表示されるようにしたいのです。建物によっては2部屋のものもあれば4部屋のものも8部屋のものもあります。 どのようにしたらよろしいでしょうか? よろしくお願い致します。
- ベストアンサー
- オフィス系ソフト
- エクセルのデーター検索、抜き出し。
初めまして、よろしくお願いします。 データーで A B C D 1 ・ ・ 5 5 15 6 2 30 7 9 18 ・ ・ ・ ・ ・ ・ 100 87 21 というデーターが有ります。抜き出したい値がこのデーターのA列の値と同じ行のBの値を抜き出す方法を探しています。(例えば2を入力すれば、2はA6の値と同じなので、B6の値30を抜き出す。)できたら関数式で抜き出す方法が有りましたら、よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- Excelのマクロで質問します
A列には氏名が入っています。 B列には性別が入っています。(例えば男なら○印,女なら空白) そこで,B列に○があれば男の列(C列)に,空白なら女の列(D列)に それぞれ上から(1行目から)氏名を入力していくようにしたいです。 (つまり,C1は男の1番,C2は男の2番・・・・・,D1は女の1番,D2は女の2番・・・という感じです。) このようなマクロについて,どなたか教えてください。 ちなみに1000行ぐらいまでデータがあるとします。
- ベストアンサー
- その他MS Office製品
- エクセルの関数についてです
大変恐縮ですが、エクセルの関数について教えていただきたいと思います。 例 A B C D 1 175 141 210 98 2 47 70 417 39 3 64 845 32 626 4 79 194 54 85 5 224 68 85 110 上記A列からD列の数字が1行目から5行目まで5セットあります。 その中でAはBより大きくCより小さい(B<A<C)なおかつBはAより小さく Dより大きい(A>B<D)。 と、この条件を両方満たしている行だけを抽出したいと考えております(例では1行目が該当します)。 手元に約6万行分のデータがあり、何とか関数で処理したいものの詳しく分からず困っています・・・。 お忙しいところ申し訳ございませんが、どなたか関数をご存じでしたら 教えてくださると幸いです。 よろしくお願い致します。
- ベストアンサー
- 財務・会計・経理
- Excelの関数について
エクセルの関数を教えて下さい。 できあがりの図 名前 該当 --------- A * B C * D E F * 名前 --------- A B C D E F という、データと 名前 ------- A C F というもう一つのデータをマージさせ 重複しているものに「*」をつけ、 出来上がり図を作りたいのですが、 「該当」列にはどのような関数を入れればいいのでしょうか。 ご教示下さい。
- ベストアンサー
- オフィス系ソフト
- エクセルVBAで重複データの削除
A列、B列、C列・・・とデータが入っていて、B~D列の5行目から10行目が関連の有るデータのかたまりとします。 C~D列の全てのデータが重複している場合に、最初のほうのデータ(行番号が小さいほう)を残すものとして、重複データを削除したいのです。 削除するときは、 B~Dの範囲で削除する。A列等は削除しない。 削除したらデータは上に詰める。 データはソートしない。 ということをやりたいのですが、簡単に出来ますでしょうか? 良く覚えていないのですが、ネット上で色々探してみても、必ずソートしている気がしたので、ソートしない方法が知りたいのですが。
- 締切済み
- オフィス系ソフト
- Excel 2007 マクロ 同列のデータの重複チェック
Excel 2007 マクロ 同列のデータの重複チェック A列で重複しているデータをチェックします。 重複しているデータについて、B列にフラグをつけます。 フラグはどの行とどの行が重複しているのかわかるようにしたいです。 そのため重複している行同士ごとにフラグをつけます。 上記の内容はマクロで実現できるのでしょうか。 元データと完成形の画像を添付します。 よろしくお願いします。
- ベストアンサー
- その他MS Office製品
- エクセルのデータ処理で困っています
あまりエクセル関数に詳しくないので困っております。 2点ほど質問があるのですが… 1. B1セルから横に [A], [B], [C] , [D] , [E] , [F] [1] , mikan, ichigo , - , mikan , - [2] , - , ichigo , ichigo , ichigo, - と入っています。 (桁がずれて表示されてしまうので、セルの区切りにカンマを入れました。見えづらくてすみません。以下の表も同様です) この時、各行毎に、入力されているデータ(ハイフンは除く)が同じものであるかどうかを判定し、同じであれば●を、違うものが混じっていれば×を、[A]列に入力したいのです。 結果としては[A1]が×、[A2]が●となればよいのですが… if(B1=C1=D1=E1=F1,"●","×")と入れてみましたが、"-"をどう処理すればよいのかわからず、お手上げです。 2. B1セルから横に [A], [B], [C], [D] [1] Q05 , mikan , orange [2] P05 , mikan , orange [3] S12, りんご, apple [4] R09, ぶどう, grape [5] P10 , ringo, apple [6] S16, りんご, apple [7] P30 , みかん, orange [8] T42 , ミカン, orange と入っています。 [A]列に重複の有る無しを入力したいのですが、条件があり、 まず、[D]列が同じものであること、 次に、[B]列の数字部分が等しくないものについて、 上から順番に、重複1、重複2,…と[A]列にいれていきたいのです。 [A]列にはすべて同じ式を入れますが、重複でないセルは"" になるように設定していただくと助かります。 結果として、 [A], [B], [C], [D] [1] 重複1, Q05 , mikan, orange [2] , P05, mikan, orange [3] 重複2, S12, りんご, apple [4] , R09, ぶどう, grape [5] 重複2, P10 , ringo, apple [6] 重複2, S16, りんご, apple [7] 重複1, P30 , みかん, orange [8] 重複1, T42, ミカン, orange このようになれば、と思っています。 これについては、頭の中が混乱して、お手上げ状態です。 未熟者なので、関数でご教授いただければありがたいですが、もし無理なようであればVBA でも… よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
お礼
回答ありがとうございます。 お陰さまで一発で目的を果たせる事ができました。