[[20051201143119]]　『行の上詰め抽出』（ポポ）　ページの最後に飛ぶ

『行の上詰め抽出』（ポポ）

①sheet1から別シート（sheet2は人事、sheet3は経理・・）にsheet1の部門名から判断して行単位で抽出したい。またその際、行を上につめて抽出したい。sheet1の最下行に追加した場合にも、他シート（サービスの行を追加したらサービスのsheet）にも反映させたい

＜sheet1＞

  A 　　　　 B  　　　　C        D
部門名　　　社員番号　　氏名　　スキルレベル
1人事　　　　001　　　　佐藤　　　3
2経理　　　　003　　　　加藤　　　2
3営業　　　　002　　　　山口　　　7
4サービス　　017　　　　斉藤　　　6
5経理　　　　030　　　　山田　　　1
6サービス　　029　　　　木本　　　6
7
8

作成したい例
＜経理シート＞

 A 　　　　 B  　　　　C        D
 部門名　　社員番号　　氏名　　スキルレベル
1経理　　　　003　　　　加藤　　　2
2経理　　　　030　　　　山田　　　1
3
4

いろいろ試したのですが、元のシート（全部門のリスト）は1枚で、
それを基本に上詰めして抽出できませんでした。
どなたか知恵をご伝授ください。
自部門以外の情報を展開したくないので部門単位にシートを
フィルターを使わずに作成しておきたいのです。
抽出に時間が多少かかっても構いませんので、よろしくお願いします。

 >フィルターを使わずに作成しておきたいのです。
 VBAも不可ですか？(INA)

(INA)様、返信ありがとうございます。

初心者なので、複雑なVBAは理解できないかもしれません。
やはり、関数だけでの対応はむずかしいのでしょうか？（ポポ）

 >関数だけでの対応はむずかしいのでしょうか？ 
 私は関数が苦手なので分からないです・・(^_^;)

 フィルタを使いたくない理由は何なのでしょうか？
 フィルタオプションを使えば簡単なのですが・・　
   (INA)

フィルタを使うというのは、1枚のシートを必要な部門数にｺﾋﾟｰ？して、
シート毎に各部門名でフィルタをかけるということでしょうか？

今回最終的に、各部門別に表示したシートを各部門ファイルを作成しそれに
個別にコピーして各部門担当者へ展開します。
フィルタで抽出した場合、各部門の方がフィルタを操作することで、
他部門情報が閲覧できてしまう？のを懸念してます。（ポポ）

 いいえ。フィルタオプションです。

 ◆他のシートへ抽出（他のブックも共通）　
http://homepage1.nifty.com/kenzo30/ex_kisotoku/ex_ks_tokubetu9_1.htm

 これに加えて、マクロの記録を利用すれば、簡単に作業を自動化できますよ。　
 (INA)

 便乗。（みやほりん）
http://miyahorinn.fc2web.com/schooltxt/filta10.html

INA様、みやほりん様

ご提示していただいた、「フィルタオプションのほかのブックへの抽出」
まさに私がやりたかったことです。
部門の抽出制限をどこでするのか、
ちょっと拝見しただけではまだ理解できなかったので、
よく読んでみます。
ありがとうございます。（ポポ）

 検索条件範囲を 抽出先ブックのA1:A2 として
    A
 1 部門名
 2　経理
 としておけば抽出できますよ。

  (INA)

 マクロで処理させると太刀打ちできませんが、関数で処理する例です。
作業を軽減するために、作業列を使いますので、レイアウトが少し違ってきますが・・・

 まず、名前を付けて保存したBOOKを立ち上げ直して（CELL関数をすぐに機能させるため）、
 Sheet1
   A	B	C	D	E
1 通し№	部門名	社員番号	氏名	スキルレベル
2  1	人事	1	佐藤	3
3  2	経理	3	加藤	2
4  3	営業	2	山口	7
5  	サービス	17	斉藤	
6  4	経理	30	山田	1
7  5	サービス	29	木本	6
A2セルに　=IF(COUNTA(B2:E2)=4,MAX($A$1:A1)+1,"")　として200行ぐらいまでコピー。
部門名、社員番号、氏名、スキルレベル全てに入力されたら有効情報として通し№が付きます。

 Sheet2
   A	  B	 C 	D
1 部門名： Sheet2		
2 通し№	 社員番号	 氏名	スキルレベル
3
B1セルに
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
A3セルに
=IF(COUNTIF(Sheet1!$B$2:$B$200,$B$1)>=ROW()-2,SMALL(IF(Sheet1!$B$2:$B$200=$B$1,Sheet1!$A$2:$A$200),ROW()-2),"")
として、CtrlとShiftとEnterキーを一緒に押して配列数式として確定。
※数式バー内では、上の式が{ }で括られた状態になります。
B3セルに
=IF($A3="","",VLOOKUP($A3,Sheet1!$A$2:$E$200,COLUMN()+1,FALSE))
で、普通に確定し、D3までコピー。
A3:D3を選択し、ズルズルと200行目ぐらいまでコピーします。
Sheet2のシート名を『経理』に変更したり、
シートタブを右クリックしてシートのコピーを作成し、シート名を部門名に変更してゆけば必要なデータが出来ます。
但し、Sheet1に入力されている部門名と半角・全角・スペースなど全てが完全一致しないとダメです。
ですから、シート名に付けられない文字を部門名に付けるのは避けて下さい。
(sin)

 ◆関数で！
 ◆Sheet1
 	A	B	C	D
 1	部門名	社員番号	氏名	スキルレベル
 2	人事	1	佐藤	3
 3	経理	3	加藤	2
 4	営業	2	山口	7
 5	サービス	17	斉藤	6
 6	経理	30	山田	1
 7	サービス	29	木本	6
 8	人事	55	藤原	8

 ◆経理のシートは、シート名を「経理」とします
 ◆シート「経理」
 	A	B	C	D	E	F
 1	部門名	社員番号	氏名	スキルレベル	経理
 2	経理	3	加藤	2		
 3	経理	30	山田	1		3
 4						
 5						
 6						6

 ◆シート「経理」のF1の式（別にどこでもいいですよ！）
 F1=REPLACE(CELL("FILENAME",A1),1,FIND("]",CELL("FILENAME",A1)),)
 ★シート名の「経理」が表示されます！

 ◆シート「経理」のF2～作業列に！（別にどこでもいいですよ！）
 F2=IF(Sheet1!A2=$F$1,ROW(Sheet1!A2),"")
 ★下にコピー（Sheet1で入力する、社員数より少し多い目程度）
 ◆作業列が目障りなら、列を「非表示」にしてください！

 ◆シート「経理」のA2の式
 A2=IF(ROW(A1)>COUNT($F:$F),"",INDEX(Sheet1!A:A,SMALL($F:$F,ROW(A1))))
 ★右・下にコピー

 ◆これで、経理の社員分が表示されます！

 ◆他部門分の作成！　Ctrlを押しながら、「経理」のシートタブを選択し右へドラッグします！
 ◆すると、経理（2）のシートが複写されます！
 ◆そのしーとのシート名を「人事」に変更します
 ◆人事の社員分が表示されるはずです！以下同じように、作成してください！

 ◆いかがでしょうか！
 （Maron)

コメント返信：

[ 一覧(最新更新順) ]

[[20051201143119]] 『行の上詰め抽出』（ポポ） ページの最後に飛ぶ

[[20051201143119]]　『行の上詰め抽出』（ポポ）　ページの最後に飛ぶ