Excelの関数で教えてください。

以下のようなデータがあります。

Book1
A列…社員No
B列…社員名
C列…性別




M列…部署名







ザックリなデータ説明ですみません。。。
Book1にあるデータをBook2やBook3へ部署ごとに分けたデータ作成したいのですが、オートフィルタで部署別に出して社員Noをコピーーペーストしてその後B~AFくらいまであるデータをVlookupで出すのも少々面倒だなぁ~と思いまして。
各部署ごとですからM列にある部署名から他のBookへ関数で表示できないかとHlookupとかFindとかいろいろな関数試しましたが上手くいきません。
何か方法はないでしょうか?
出来ればVBAやマクロ使用せず、関数でお願いします。(他の人に説明するのにVBAなど使用されるとちょっとキツイ…)
よろしくお願い致します。

回答の条件
  • 1人2回まで
  • 登録:2009/06/08 11:28:51
  • 終了:2009/06/12 14:48:45

回答(1件)

id:SALINGER No.1

SALINGER回答回数3430ベストアンサー獲得回数9692009/06/08 12:18:54

ポイント60pt

関数だけの方法を紹介します。

まず、AGに作業列を作ります。

AGには1行目からオートフィルで1・2・3・4・・・と連番を入れます。(社員Noが連番ならばそちらを使う方法もあります)

各シートは1行目がタイトルで2行目からデータが入っているとして、Book1のシート名はSheet1だとすると

Book2のA2の数式を

=INDEX([Book1.xls]Sheet1!A:A,SMALL(IF([Book1.xls]Sheet1!$M$2:$M$1000="×××",[Book1.xls]Sheet1!$AG$2:$AG$1000,1001),ROW()-1))

として、Shift+Ctrl+Enterで配列数式とし、縦横にコピーします。

上記の式の×××は部署名が入り、その部署名の行だけを抽出した表となります。

式の1000とか1001はBook1の行数よりも大きい数字ならばなんでもいいです。

id:kanachan

いつもありがとうございます。

何とかできました。が。。。A列の社員Noしか出来なくて、B列以降Shift+Ctrl+Enterを押しても全部社員Noとして出てきてしまいまして…

この場合はVlookup関数で出してあげればよいのでしょうか??

2009/06/08 14:04:07
  • id:kn1967
    >オートフィルタで部署別に出して

    説明が面倒ということであれば、もっと単純に
    ステップ1:データ全体(面倒ならシート全体)を選択
    ステップ2:M列で並べ替え
    ステップ3:部署単位でコピペ
    が簡単確実で良いと思うのだけど?
  • id:kanachan
    そういえば…その方法もありましたね^^;
    部署数が多くて「面倒!」しか頭に浮かばなかったです(汗
    ありがとうございます。
  • id:SALINGER
    A2の数式をそのまま横にコピーすれば、式中の一箇所
    A:A→B:B
    と変わる式がコピーされます。
    これはB列の式になります。
  • id:kanachan
    教えて頂いたとおり行ってみたのですが「NUM!」のエラーメッセージが出てしまいまして…なぜでしょう?
  • id:SALINGER
    AG列に1行目から最終行までちゃんと連番がついているかどうか。(空白だとNUM!がでます)
    式中の$がちゃんとついているか。(省略するとコピーしたときにNUM!がでる場合があります)
    を確認してみてください。

    直接B2の数式を
    >>
    =INDEX([Book1.xls]Sheet1!B:B,SMALL(IF([Book1.xls]Sheet1!$M$2:$M$1000="×××",[Book1.xls]Sheet1!$AG$2:$AG$1000,1001),ROW()-1))
    <<
    として、Ctrl+Shift+Enterでも同じようにエラーがでますか?
  • id:kanachan
    同じエラーが出たり「REF!」が出たり…
    $も付いていますし連番にもなっているのですが…上手くいきません…
  • id:SALINGER
    私のテスト環境ではうまくいっているので、私の作ったものと比較してみてください。
    http://kissho.xii.jp/1/src/1jyou79562.zip.html
    DLKeyはあなたのIDです。
    注)Book2.xlsだけ開くと外部ブックのリンクにアドレスがつきますが、Book1.xlsも開くと元に戻ります。
  • id:kanachan
    やっぱり原因はわかりませんでした。
    出来たりできなかったり。エラー表示されることが殆どですが、たま~に上手くいくのでがんばってみます。
    ありがとうございました。
  • id:SALINGER
    複雑な数式の場合、どういう動作なのかがわかりづらいのでやはり理解して自分で作るしかないのかもしれないですね。
    特に配列数式は直感的にわかりづらいので、作業列を増やして途中経過を把握することが必要かもしれません。
    お役にたてなくて申し訳ありませんでした。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません