エクセル 他のファイルにデータを飛ばしたい！

質問

BOOK１に顧客リスト（お客様情報）を随時更新しています。

これからBOOK2にデータを自動で飛ばしたいのですが、どうしたらいいか教えて下さい。

BOOK１にはお客様の住所や年齢など情報を入力してまして、担当が決まれば他のBOOKにお客様の名前だけでも飛ばしたいのです。
現在３人の営業がいまして、Aと入力するとBOOK2、BはBOOK3、CはBOOK4という形を取りたいのですが、関数でできますか？

《BOOK1》
　A列　　　　　　　B列　　　C列　　　　D列・・・・・・・・・・AB列
お客様名　　　　住所　　　年齢　　　勤務先・・・・・・・・担当者

（600名程度のデータがあります）

担当者がAと決まったのでBOOK2に自動で飛ばしたい。

《BOOK2》
　A列
お客様名

複数人を担当しているわけなので、VLOOKで引っ張れないですよね？

こっちにも入力して、あっちにも入力するという手間を省きたいです。

どうかエクセル詳しい方、ご指導下さい。

回答No.5

MackyNo1 · ベストアンサー

＞このINDEXの関数ですが、参照があるほうを使われてますか？

「参照がある方」とはどういう意味でしょうか？

=INDEX([Book1.xls]Sheet1!A:A,SMALL(INDEX(([Book1.xls]Sheet1!$AB$2:$AB$1000<>"A")*1000+ROW($A$2:$A$1000),),ROW(A1)))&""

ちなみに上記の数式は、Book1のSheet1のAB列に「A」がない場合は1000を、ある場合はその行数の配列を取得し、その配列（該当行）の中で小さい順に行数を求めその行数のA列のデータ（該当データがない場合は1000行目のデータ=空白）を返す式になっています。

なお、どのセルに入力する場合もROW(A1)の部分は変わりません（1つ目のデータを抽出するという意味です）。

ただし、この数式を1つのブックに数多く入力するとシートの動きが重くなりますので、多数のデータを表示する場合は、後で回答した元シートにA列を挿入し（この列は非表示にする）その補助列にユニークな検索する文字列を作成して、このデータを検索値としたVLOOKUP関数でそれぞれ対応するセルを表示するほうが良いと思います。

なお、お分かりになると思いますが、補足で回答したB2セルに入力するVLOOKUP関数に再度誤りがありましたので訂正します。

=IF(A2="","",VLOOKUP($A2,[Book1.xls]Sheet1!$A:$Z,COLUMN(B2),0))

お礼

式最後の　&""　は何ですか？

それと1000行目のデータ=空白を返す式とありますが、リストはどんどん増えていきますので、1000行目に達したときおかしくなりますよね！？

私の知識ではなかなか難しい式で、難易しております。

ご指導お願いします。

回答No.6

MackyNo1

＞式最後の　&""　は何ですか？

該当データがない場合は1000行目のデータ(空白セル）を参照しますが、セルが空白だと「0」と表示されます（例えばZ1セルが空白の場合、「=Z1」のように空白セルを参照すると空白ではなく「0」と表示されます）。
これを空白表示にするため、空白文字列「&""」を連結して、全体を文字列にすれば、「０」ではなく空白文字列として表示されます。
ただし、この数式では必ず文字列として返りますので、数値や日付などのデータを参照する場合は文字列数字やシリアル値の文字列数字になりますので注意が必要です。

＞それと1000行目のデータ=空白を返す式とありますが、リストはどんどん増えていきますので、1000行目に達したときおかしくなりますよね！？

データが600行と書いてあったので1000行まで対応できる数式にしましたが、データがそれ以上になる場合は、もちろん数式を変更する必要があります。
ただし、この数字（配列数式の配列範囲）を大きくすると、再計算に時間がかかりシートが重くなるので、必要以上に大きな範囲を指定しないようにしてください。

また、表示データ数が多くなる場合も、同様にシートの動きが重くなります。これらの場合には別回答の補助列を使った方法で対応するほうが計算負荷が少ないと思いますので、条件によって使い分けるようにしてください。

お礼

MackyNo1さん

毎回ご指導ありがとうございます。

自動で入力されるようになりました。
で、また新たな問題点を見つけたんですが、並べ替えが出来ません。
→当たり前ですよね・・・

どうにもならないのでしょうか？

エクセル難しいです。

回答No.4

KURUMITO

BOOK1やBOOK2はそれぞれシート1及びシート２にあるとします。
シート1のE列の2行目から下方には担当者名（A,B,C)がそれぞれ入力されているとします。
F列には作業列を設けF2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(E2="","",E2&COUNTIF(E$2:E2,E2))

シート2ではA1セルには担当者名のAを入力し、2行目には項目名を入力します。
A3セルには次の式を入力しD3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNTIF(Sheet1!$F:$F,$A$1&ROW(A1))=0,"",INDEX(Sheet1!$A:$D,MATCH($A$1&ROW(A1),Sheet1!$F:$F,0),COLUMN(A1)))

お礼

ありがとうございます。
シート別にするのは考えたんですが、今回は別のデータに飛ぶ様言われてまして断念しました。

でも皆さん関数の組合わせに詳しいので助かりました。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

数式では不向きな作業です。
まさにピッタリな操作として、MSクエリを使った方法をリンクしておきます
BOOK2で操作を行います。

Microsoftのサイトはちょっと見にくいかも
データの更新は最短で1分ごとの自動更新が可能です。

Microsoft Query を使用して外部データを取り出す
http://office.microsoft.com/ja-jp/excel-help/HA010099664.aspx

データを扱う時のいろいろな操作 2
http://www11.plala.or.jp/koma_Excel/contents6/mame6041/mame604101.html

お礼

こんな方法があるんですね！？
まだまだ知らないことが多すぎて、勉強になります。
クエリは便利そうなので、勉強してみます。
ありがとうございました。

回答No.2

MackyNo1

訂正と補足です。

No1の回答のVLOOKUP数式は第3引数を省略していましたが正しくは以下の数式です。

=IF(A2="","",VLOOKUP($A2,[Book1.xls]Sheet1!$A:$Z,COLUMN(B2)))

ちなみに、関数で表示するデータが多くなると動きが重くなるので、元データのBook1のA列に新しい列を挿入して、「=IF(B2="","",G2&COUNTIF($G$2:G2,G2))」の数式をオートフルコピーしておき、Book2のA2セルに以下のような数式を入力して下方向及び右方向にオートフィルコピーするほうが計算負荷が少ないので適当かもしれません。

=VLOOKUP("A"&ROW(A1),[Book1.xls]Sheet1!$A:$AB,COLUMN(B1),0)

お礼

ありがとうございます。
このINDEXの関数ですが、参照があるほうを使われてますか？
なかなか難しいですが、ちょっと根気をもってやってみますので
それだけ教えてください。

勝手ながら明日あさって久々の連休なので、休み明けのお返事になるかと思います。
申し訳ありません。

回答No.1

MackyNo1

担当者が「A」のブックのA2セルに以下のような数式を入力して下方向にオートフィルすれば、Book1のSheet1のAB列の「Aの入力されているA列のセルのお客様名を自動的に表示することができます。

=INDEX([Book1.xls]Sheet1!A:A,SMALL(INDEX(([Book1.xls]Sheet1!$AB$2:$AB$1000<>"A")*1000+ROW($A$2:$A$1000),),ROW(A1)))&""

この数式を横方向にドラッグしても該当データを表示することができますが、配列数式を多用すると動きが重くなるので、お客様名に重複がないなら、B２セルには以下のようなVLOOKUP関数の式を入力して、右方向および下方向にオートフィルされることをお勧めします。

=IF(A2="","",VLOOKUP($A2,[Book1.xls]Sheet1!$A:$Z,COLUMN(B2)))