エクセルでオートフィルターで表示した複数データに新しいデータを上書きして貼り付けたい

質問

エクセル２００３を使用しています。
エクセルでオートフィルターで表示した複数データに新しいデータを上書きして貼り付けたい。
たとえば
男女混合名簿
番　氏名　性　観点
１　田中　２　A B B A
２　吉川　１　A B B B
３　大山　２　B B B B
４　藤本　１　B B C C
５　鈴木　２　A B A A
をオートフィルターで２で表示させると
１　田中　２　A B B A
３　大山　２　B B B B
５　鈴木　２　A B A A　となります。

これに
１　田中　２　
３　大山　２
５　鈴木　２ の部分に
別のシートで作成したデータ
A A A A
B A A B
A A A A
を貼り付け上書きできないでしょうか。

オートフィルターで抽出したデータを別のシートに貼り付ける方法はわかるのですが、その逆がわかりません。
よろしくお願いします。

回答No.4

goomania · ベストアンサー

２つの方法を思いつきました。

１．関数を使う方法

ご質問のようなケースではＶＬＯＯＫＵＰ関数を利用して
３つの表を結合する手法が一般的なのではないでしょうか。

男子のみの表を表１、女子のみの表を表２、男女混合の表を表３とすると
「表３に表１および表２の情報を取込み、表３を一覧することで表１、表２
の内容も分かるようにしたい」
というのがご質問者のニーズであると理解しました。

そこで表３に表１、表２の情報を取込むためには、表１、表２、表３に
共通に存在する項目（たとえば氏名とか個人番号とか）があることが
前提となります。

たとえば「表２の１０列目に登場する個人データが表３に登場する
３列目の個人データと同一人物のものである」と判断するためのキーが必要だ
ということです。

さて、各表に共通に存在する項目（キー項目）があるなら
ＶＬＯＯＫＵＰ関数を使用して、各表の情報を結合することができます。
表３は以下のようだとご質問者が示しておられます。
Ａ　　　Ｂ　　Ｃ　　　Ｄ　　　　Ｅ　　　　Ｆ　　　　Ｇ
番　氏名　性　観点１　観点２　観点３　観点４
１　田中　　２　　　Ａ　　　　Ｂ　　　　Ｂ　　　　Ａ
２　吉川　　１　　　Ａ　　　　Ｂ　　　　Ｂ　　　　Ｂ
３　大山　　２　　　Ｂ　　　　Ｂ　　　　Ｂ　　　　Ｂ
４　藤本　　１　　　Ｂ　　　　Ｂ　　　　Ｃ　　　　Ｃ
５　鈴木　　２　　　Ａ　　　　Ｂ　　　　Ａ　　　　Ａ

ご質問者がお示しになられた表がＧ列までデータが入力された
状態だとして、Ｈ列以降のＩ列、Ｊ列、Ｋ列に男子のみの表、
女子のみの表からデータを取得するために
まず、男子のみの表と女子のみの表を結合します。
前提として表の見出しである　番、氏名、性、観点などの並び順と
個数は男子、女子とも一致しているものとします。
結合するといっても、女子の表の最終行の続きに男子の表
をくっつけるだけです。（逆に男子の表の最終行の続きに女子
の表をくっつけてもＯＫです。）
これを表４とします。以下のような表です。
　Ａ　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ
氏名　観点５　観点６　観点７　観点８
田中　　　Ａ　　　　Ａ　　　　Ａ　　　　Ａ
大山　　　Ｂ　　　　Ａ　　　　Ａ　　　　Ｂ
鈴木　　　Ａ　　　　Ａ　　　　Ａ　　　　Ａ
吉川　　　Ｂ　　　　Ａ　　　　Ａ　　　　Ｂ
藤本　　　Ａ　　　　Ｂ　　　　Ａ　　　　Ａ

ここでは氏名がキー項目だという前提で述べますが、
表３のＨ２に以下のような関数を記述します。
=VLOOKUP($B2,表４.xls!$A$2:$E$6,COLUMN()-6,FALSE)
<上式の$A$2:$E$6は表４のデータの範囲を示します。>
これを例示されているケースで言えばＨ２：Ｋ６の範囲へ
コピーします。
これで、
Ａ　　Ｂ　　　Ｃ　　　　Ｄ　　　　Ｅ　　　　Ｆ　　　　Ｇ　　　　Ｈ　　　　Ｉ　　　　Ｊ　　　　Ｋ
番　氏名　　性　　観点１　観点２　観点３　観点４　観点５　観点６　観点７　観点８
１　田中　　２　　　　Ａ　　　　Ｂ　　　　Ｂ　　　　Ａ　　　　Ａ　　　　Ａ　　　　Ａ　　　　Ａ
２　吉川　　１　　　　Ａ　　　　Ｂ　　　　Ｂ　　　　Ｂ　　　　Ｂ　　　　Ａ　　　　Ａ　　　　Ｂ
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・以下略・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
といった表が得られると思います。

２．関数を使用しない方法

>オートフィルターで抽出したデータを別のシートに貼り付ける方法はわかるのですが、
>その逆がわかりません。

ということなので
「オートフィルターで抽出したデータを別のシートに貼り付ける方法」を使って
まず男子のみ抽出して別のシートに貼り付けます。
このときA列の番号も必ず別シートに貼り付けてください。
ここに
別のシートで作成したデータ
A A A A
B A A B
A A A A
を貼り付け上書きします。
ただし、A列の番号部分には上書きせず残してください。
次に女子のみ抽出して別のシートに貼り付けます。
ここに
別のシートで作成したデータ
B A A B
A B A A
を貼り付け上書きします。
ただし、A列の番号部分には上書きせず残してください。
これで、男子、女子のシートが別々に出来上がりましたので
男子の表の最終行の次に女子の表をコピーして１つの表にしてください。
これをA列を基準に並べ替えすれば、ご希望の表が完成すると思います。

お礼

関数を使う方法はうまくいきました。
一応目的は達することができました。
ありがとうございました。

=VLOOKUP関数はこれまで使用したことはありましたが、名前で一致させることをおもいつきませんでした。また列番号を求めて相対参照セル利用するCOLUMN()-6の利用など思いつきませんでした。これまで使用したことがなかったので有益なものとなりました。今後もこの数式は使えそうです。

関数を使わない方法はうまくいきませんでした。
ふりがながきちんと整理されていないので並び替えがアイウエオ順になってくれません。
かたかたで始まる名字たとえば「スマート　○○さん」などうまくならんでくれません。

しかし、理想とするのはオートフィルターで表示された部分に形式を範囲と形式を合わせた形でデータの貼り付けができればそれにこしたことはありませんが、もともとこのような機能はないのでしょうか。ここが一番知りたいことなのですが。

補足

2週間がたちました。
自分なりに試行錯誤下のですが、次の方法により解決できました。

エクセルの対象シートをアクセスにインポートし、フィルターにかけ変更したいレコードを表示させたのち、変更になったエクセルのデータ貼り付けることで解決しました。
アクセスではこのようなことができたのでした。

みなさんのアドバイスありがとうございました。

回答No.3

imogasi

貼り付けると張り付いたが。
１　田中　２　
３　大山　２
５　鈴木　２
の部分に、は２列ですね。
別のシートで作成したデータ
A A A A
B A A B
A A A A
は１列データですか？
貼り付けられるようですが、何のことを言っているのか、どういう必要性から出ているのか、伝わらない。

補足

文字データA　B　C　Bをそれぞれ１列ごとに貼り付けます。

そのまま貼り付けると表示されていないレコード（行セル）にもデータが貼り付いてしまい、表示された部分だけ（すなわち、抽出された男子あるいは女子）のところに貼り付かないので困っています。女子のデータを抽出し女子のデータを貼り付けたら表示されない男子のデータにまで上書きされてしまいます。

回答No.2

sakemoni1008

意味が無い事はしない方が良いのでは？
別シートのデータを貼り付けるのであれば、直接入力するか
一件々貼っても良いのでは？
件数が多いと言うのであれば、書式を明示すれば良い方法を
教えて下さい方がいらっしゃると思います
例題だけでは、何をどうしたいのか良く分かりませんよ

補足

>意味が無い事はしない方が良いのでは？
意味があるのでしようとしています。

>別シートのデータを貼り付けるのであれば、直接入力するか
一件々貼っても良いのでは？
件数が多いです。

>件数が多いと言うのであれば、書式を明示すれば良い方法を
教えて下さい方がいらっしゃると思います
文字データA　B　C　Bをそれぞれ１列ごとに貼り付けます。

補足します。
全体的な話になりますが、
男女混合名簿があります。レコード数は１６０件くらいです。
オートフィルターで男子だけを抽出します。
そのデータに別の人が作った男子だけのファイルからデータを貼り付け、データを更新したいのです。同様に女子だけを抽出します。そのデータに別の人が作ったファイルからデータを貼り付けたいのです。
要するに、男子だけのファイル、女子だけのファイルを合成して、男女混合名簿にある形式のデータファイルを作成したいのです。
しかし、男女混合名簿の"番"（アイウエオ順）は男子名簿の"番"（アイウエオ順）と女子名簿の"番"と異なっています。（なぜなら男女混合にすればアイウエオ順は違ってくるからです。）

ここでそのまま貼り付けると表示されていないレコード（行セル）にもデータが貼り付いてしまい、表示された部分だけ（すなわち、抽出された男子あるいは女子）のところに貼り付かないので困っています。女子のデータを抽出し女子のデータを貼り付けたら表示されない男子のデータにまで上書きされてしまいます。

回答No.1

noname#204879

私も妙手を思い付きません。

[オートフィルタ]とは全く関係ないけど…
次のような機能があることをご存知ですか？

下に示すように、範囲１、範囲２のデータがあったとき、範囲２を[コピー]した後、範囲１を選択して[形式を選択して貼り付け]画面で“空白セルを無視する”にチェックを入れて[OK]すると、範囲１aの結果が得られます。

範囲１　　　 範囲１a
A B B A　→　A A A A
A B B B　　　A B B B
B B B B　→　B A A B
B B C C　　　B B C C
A B A A　→　A A A A

範囲２
A A A A

B A A B

A A A A

補足

空白セルを無視する機能は知っております。
オートフィルターで抽出しているデータは空白行はないので使えません。