[[20100518123928]]　『該当の区分のみ別の表に一覧として出したい』（あや）　ページの最後に飛ぶ

『該当の区分のみ別の表に一覧として出したい』（あや）
Excel2003、WindowsXP

 （元データ）
 区分　　取引先　　月
 　1　　　　A　　　1月
 　2　　　　B　　　2月
 　1　　　　C　　　1月
 　3　　　　D　　　1月
 　1　　　　E　　　3月

☆区分が1のデータのみを別の表にしたい

 （表示したい表）
 区分　　取引先　　月
 　1　　　　A　　　1月
 　1　　　　C　　　1月
 　1　　　　E　　　3月

別の人が使うので、
マクロや、オートフィルタ機能は使わず
関数で出したいです。

表示したい表に、関数を入れておいて
元データにデータを入力したら反映させるようなイメージをしています。

よろしくおねがいします。

 SUMPRODUCTで、できると思います。
http://www.excel.studio-kazu.jp/lib/e3h/e3h.html

 =SUMPRODUCT((元データ!$B$2:$B$6=B2)*(元データ!$C$2:$C$6=C2),(元データ!$A$2:$A$6))
 BJ

すいません、どういうふうに使えばよいかわかりません。

お手数ですがもう少し詳しく教えて下さいm(__)m

 >☆区分が1のデータのみを別の表にしたい
 すみません。
 まるっきり勘違いしてました。
 忘れてください。
 BJ

         [A]       [B]       [C]       [D]       [E]       [F]       [G]
 [1]    区分      取引先     月                  区分     取引先     月
 [2]     1          A       1月                   ※
 [3]     2          B       2月
 [4]     1          C       3月
 [5]     3          D       4月
 [6]     1          E       5月
 [7]

 このようなレイアウトだと仮定して、E2～G列最終行までに絞って表示させるものとします。
 E2セルへ下記数式を入れ、これをG列までそのままコピーし、更に下へ必要分コピーしてみて下さい。

 E2：=IF(COUNTIF($A$2:$A$6,1)<ROW()-1,"",INDEX(A$2:A$6,SMALL(INDEX(NOT($A$2:$A$6=1)*10^5+ROW(A$1:A$5),),ROW(A1)),1))

 (Dil)

ありがとうございます。

勉強になります。

あやです。Dilさん、前回はありがとうございました。

理解するのに時間がかかりました…

今使っている表に合わせるようにしたのですが、これで間違いないでしょうか。

　*10^5　の部分は何をしているのでしょうか。

よろしくおねがいします。

　　　[D]　　　　[E]　　　　　[F]　　　　　[P]　　　…　[U]　…　 [U]

[1]　　　　　　　　　　　　　　　　　　　　取引先　　　　月　　　　区分　

[2] 　　　　　　　　　　　　　　　　　　　　A　　　　　　1月　　　　1

[3] 　　　　　　　　　　　　　　　　　　　　B 　　　　　　2月　　　　2

[4] 　　　　　　　　　　　　　　　　　　　　C　　　　　　2月　　　　1

[5] 　　　　　　　　　　　　　　　　　　　　C 　　　　　　1月　　　　1

 ・
 ・
 ・

[31]　区分　　　取引先　　　月　　　　　

[32]　※１　　　※２　　　※３

[33]

※1

=IF(COUNTIF($V$2:$V$71,"H")<ROW()-30,"",INDEX(V$2:V$71,SMALL(INDEX(NOT($V$2:$V$71="H")*10^5+ROW(V$1:V$70),),ROW(V1)),1))

※2

=IF(COUNTIF($V$2:$V$71,"H")<ROW()-30,"",INDEX(P$2:P$71,SMALL(INDEX(NOT($V$2:$V$71="H")*10^5+ROW(P$1:P$70),),ROW(P1)),1))

※3

=IF(COUNTIF($V$2:$V$71,"H")<ROW()-30,"",INDEX(U$2:U$71,SMALL(INDEX(NOT($V$2:$V$71="H")*10^5+ROW(U$1:U$70),),ROW(U1)),1))

 実際の区分は1や2ではなくＨなのですね。
 現在の数式だとHの数が仮に三つあった時に、二つまでしか表示されず以下ブランクになってしまいますので
 COUNTIF($V$2:$V$71,"H")<ROW()-30　の部分を
 COUNTIF($V$2:$V$71,"H")<ROW()-31　に変更して下さい。

 この部分では、「現在の行番号-31した数が、Ｈの数を超えたら空白にする」という処理を行っています。
 これを入れなかったらブランクの代わりにエラーになりますが、数式が長く見づらければ
 エラーは条件付書式で隠す等の処理をして、数式のこの部分は削除してしまっても良いかもしれません。

 *10^5 は10の五乗を掛けるという事ですが、
 NOT($V$2:$V$71="H")*10^5 とする事で、区分がＨでないセルをとんでもなく高い数値に変換し
 SMALL関数の返り値から除外しています。
 なので、「Hの数がこんなには有り得ないと」いう数字ならいくつでも良く、10^5である必要はないです。

 (Dil)

ごっちゃになって例を間違えていましたが、
たしかに区分は"H"を使っています。

すごくよくわかる解説でした。

無事に使えそうです。
ありがとうございます＾ー＾

コメント返信：

[ 一覧(最新更新順) ]

[[20100518123928]] 『該当の区分のみ別の表に一覧として出したい』（あや） ページの最後に飛ぶ

[[20100518123928]]　『該当の区分のみ別の表に一覧として出したい』（あや）　ページの最後に飛ぶ