2つの表を統合したいのですがどなたかお知恵をお貸しください。
(例)
sheet1に古い表、sheet2に新しい表があります。それぞれの表はこんな感じです。
(sheet1)
A B C D
sony001 男 東京 Japan
sony002 女 埼玉 Japan
sony003 女 千葉 U S A
sony005 男 大阪 Canada
sony007 女 東京 Russia
(sheet2)
A B C D
sony001 男 東京 Japan
sony002 女 埼玉 Japan
sony002 女 千葉 U S A
sony004 男 大阪 Canada
sony005 女 東京 Russia
sony006 女 東京 Russia
sony007 男 東京 U S A
sony007 女 東京 Russia
これらの表をsheet3にどのように統合したいかといいますと、下記のようは統合表を作成したいのです。
(sheet3 統合表)
A B C D
sony001 男 東京 Japan
sony002 女 埼玉 Japan
sony002 女 千葉 U S A
sony003 女 千葉 U S A
sony005 女 東京 Russia
sony007 男 東京 U S A
sony007 女 東京 Russia
質問の長さが800字を超えてしまいますので、この統合表を作成するための手順を「2つの表を統合するVBAマクロについて(2)」で補足したいと思いますのでどうぞよろしくお願いいたします。
No.1
- 回答日時:
■シート1の
E1=COUNTIF(Sheet2!A$1:A$8,A1)
で、このセルをクリックして右下が「+」に変わったらI1までドラッグ&ドロップ、
次にI5のセルまでドラッグ&ドロップ
F1=IF($E1=0,A1,"")
で、このをクリックして右下が「+」に変わったらF5までドラッグ&ドロップ
■シート2
E1=COUNTIF(Sheet1!A$1:A$8,A1)・・・・・Sheet2がSheet1に変わっただけ
F1=IF($E1<>0,A1,"")・・・・・「=」だったのが「<>」に変わっただけ
↑と同じような感じで数式入力(5行から8行に増えたのでその分多めにトラッグ&ドロップ)
これでシート1とシート2が必要なデータがF列~I列に残ってるはずです。
シート3に各シートのデータを「形式を選択して貼り付け」-「値」で貼り付け、
空白行はオートフィルタで「空白以外のセル」選択すれば消えます。
並び方を変えたくないのであればシート1,2のデータにフィルで番号を付けておき、それをキー
に「並べ替え」。
(I1までだったのをこのセルの分まで増やさないと無視するデータに番号がついたままになります)
番号を付けると元データにキーになる数字などがなくても大丈夫です。
無視されたデータなどはシート2を見ればわかると思いますので省きました。
april21さん、いつもご回答ありがとうございます。早速やってみました。素晴らしいですね。見事sheet1,sheet2のE列、F列に、sheet3で統合したいデータが残りました。これをsheet3に統合する際に、上記の質問では分かりづらいのですが、A列のデータの並びを変えずに統合したいのです。これは何をやっているのかといいますと、データの更新なのです。sheet1にはお客様から送られてくる品番(A列)とそれに対するデータ(B,C,D列)があります。Sheet2にはその品番に対して私どもの会社で行った最新のテスト結果データ(B,C,D列)があります。(もちろんA列は共通です。)sheet1のA列には重複はありません。しかしsheet2の私達の方の表は、テストの結果によってはA列の1つの項目に対して2~3行の結果データになる場合もあるのです。
(例)
sheet1
みかん001 男 東京 USA ←april21さんの関数で残るデータ
みかん002 女 大阪 JAP
みかん003 男 埼玉 CAN
みかん004 女 福岡 SDD
みかん005 男 秋田 TTA ←april21さんの関数で残るデータ
みかん007 女 千葉 YER
sheet2
みかん002 男 東京 USA ←april21さんの関数で残るデータ
みかん002 女 福岡 TTA ←april21さんの関数で残るデータ
みかん003 女 秋田 CAN ←april21さんの関数で残るデータ
みかん004 男 埼玉 DAA ←april21さんの関数で残るデータ
みかん006 女 千葉 YER ←無視されるデータ
みかん007 男 東京 USA ←april21さんの関数で残るデータ
sheet3
みかん001 男 東京 USA ←sheet1のデータ
みかん002 男 東京 USA ←sheet2のデータ
みかん002 女 福岡 TTA ←sheet2のデータ
みかん003 女 秋田 CAN ←sheet2のデータ
みかん004 男 埼玉 DAA ←sheet2のデータ
みかん005 男 秋田 TTA ←sheet1のデータ
みかん007 男 東京 USA ←sheet2のデータ
という風にしたい場合、april21さんがご提案下さったSheet1,2に残ったデータを空白を無視してSheet3にコピーすると
みかん001 男 東京 USA ←sheet1のデータ
みかん005 男 秋田 TTA ←sheet1のデータ
みかん002 男 東京 USA ←sheet2のデータ
みかん002 女 福岡 TTA ←sheet2のデータ
みかん003 女 秋田 CAN ←sheet2のデータ
みかん004 男 埼玉 DAA ←sheet2のデータ
みかん007 男 東京 USA ←sheet2のデータ
というふうになりA列の並びが変わってしまうと思われます。(みかん001の後にみかん005が来てしまうので…)このA列の並びはsheet1の並びを基準にしたいのです。これを今までは手動で見合わせながらcut & Paste をしていましたが、データの数が5000件から10000件にも及ぶといつも午前様になってしまうというわけです。april21さんの関数では見事にそれぞれのsheetにsheet3で採用したいデータが残っていますので、後はこれをsheet1のA列の並びを基準に統合できれば完璧なのですが、何かいい方法はございますでしょうか?説明が長くなってしまって(しかもややこしくて)大変申し訳ありませんが,いいアイデアが思い浮かんだ時にはご指導下さいませ。どうぞよろしくお願いします。今回は素晴らしい関数をお教え下さり本当に勉強になりました。ありがとうございます。
No.2ベストアンサー
- 回答日時:
別のご質問で関数の事に触れたので一例として、こういう場合にはこの関数で出来ます
という意味で回答したものであって実際に使用する事を薦めている訳ではありません。
5000~10000件もあるようなデータを自動計算する設定のまま件数分のセルに数式をいれると
処理が遅くて使い物にならないと思います。
それにnishi6さんの考えてくださったVBAがあるのですから必要ないはずです。
■件数が少なくて何かに利用したい方の為に
商品番号などをつける場合は別々のセルするか「0001みかん」という風に数字を左に付ける。
番号を付けたセルをキーに「データ」-「並べ替え」で並べ替えを行う事が出来ます。
(文字列の左側のものを優先的にする為「みかん0001」「おかん0002」で並べ替えを
行うと「おかん0002」「みかん0001」に。)
50音順、数字などの昇順、降順でもなく任意の順に並べ替えたい場合は1番目に並べたい行の
セルに「1」2番目「2」・・・という風に番号をつけ、そのセルをキーに「並べ替え」
をする。
50音順で並び替えたけど最初の並びに戻したい場合、番号がふってないと戻す為のキーが
ないので戻らない。
だから、並べ替える前にデータの右でも左のセルでもいいので番号ふっておく。
手動では大変なのでフィルを使って番号を自動で付ける(フィルが分からなければヘルプ参照)
☆今回のを例にすると
K列にフィルを使って番号をつけ、F~I列でしたのと同様に
シート1のJ1は=IF($E1=0,K1,"") シート2のJ1は=IF($E2<>0,K1,"")にして不要な番号がセルに
入らないする。
E1をクリックしてシフト+コントロールキーを押しながら「end」キーで選択してコピーして
シート3に「形式を選択して・・」-「値」で貼り付けし「データ」-「並び替え」で「J列」を
キーにして並べ替える。
(F~J列を選択してコピーして値をシート3に張り付けて並べ替えをするとデータが空白で番号
だけが表示された行が入ってしまう)
april21さん、お忙しい中ご回答本当にありがとうございます。やはり、データが多い場合はマクロなどの自動処理の方が時間が短縮できるのですね。おっしゃるとおりnishi6さんのVBAのお世話になっているのでどうしてもというわけではなかったのですが、私にとってBVAより比較的ハードルが低いかなと思われる関数でも同じことが出来るのであれば、何かのときに自分たちの力でどうにか処理することが出来ることがあるかもしれないと、ご迷惑も考えずにご相談してしまいまいた。本当に申し訳ありませんでした。A列には何の秩序もなくリストアップされている表もあるので、april21さんが最後のところでご指導してくださったやり方で一度試してみたいと思います。本当にいつもありがとうございます。今回もお手数をおかけして恐縮ですがどうぞこれからもよろしくお願いします。
No.3
- 回答日時:
>データが多い場合はマクロなどの自動処理の方が時間が短縮できるのですね。
表計算では自動計算をしてしまうため操作する度に10000件分の処理をしてしまう
為、重くなってしまいます。
(自動計算させない設定にしておいてデータを処理する時だけ実行する方法もあります)
マクロだと実行させる処理(ボタンを押すとか特定のイベントを起こした時)を
した時しか動作してない。
それと繰り返し実行するフロー制御ステートメントがVBAにあるのでコードを
簡略化することが出来るので軽くなります。
今回を例にVBAにするなら
1~10000件のデータに対して同じ処理を繰り返して実行しているだけなので
今回使った関数をVBAの関数にして繰り返し実行するステートメン(For...Nextなど)を使って実行すれば良いだけですね。
(^^)/~~~
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルのマクロで置換を教えてください 1 2022/05/19 10:27
- 会社経営 家電メーカー(事業部別)売上ランキングTOP1位~10位 1 2023/05/10 20:23
- Visual Basic(VBA) 指定文字以外のカウント 4 2023/05/03 14:37
- 関東 関東1都3県 東京、神奈川、千葉、埼玉 天気予報などで言う順番って決まってますか なんとなくですが東 1 2023/06/23 21:12
- その他(国内) 1番2番3番…と争う? 2 2022/12/06 19:52
- 関東 関東地方(一都六県)を栄えている順で順位付けするなら 1位東京 2位神奈川 3位千葉 4位埼玉 5位 4 2023/08/21 10:15
- その他(国内) 日本の都道府県、都会度ランキングはこちらで宜しいでしょうか? 7 2023/07/26 12:25
- Excel(エクセル) エクセルのマクロを教えてください。 1 2022/03/30 09:29
- 政治 南関東は左翼が強すぎるけど、関西圏はどう? 2 2023/04/19 15:32
- 関東 東京でしかできないであろう遊びを教えてください 10 2022/09/10 14:54
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
【関数】先頭だけにある、半角...
-
エクセル 白黒印刷で白線を印刷...
-
Excelのチェックボックスの使い...
-
【関数】適切な文字数の数字を...
-
Excelのpivotについて質問です
-
Excel ピボットテーブルで日付...
-
LOOKUP関数を使えばいいのでし...
-
エクセル関数を教えてください
-
エクセルのセルに同じ大きさの...
-
UNIQUE関数が使えないバージョ...
-
excelの不要な行の削除ができな...
-
エクセルで「-0.0」と表示さ...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの関数について教えて...
-
Aというブックの1というシート...
-
【マクロ】シート名を取得する...
-
VBA Private Sub Worksheet_Cha...
-
VBA、Excelのworkbook.open に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報