エクセルの関数で複数条件での抽出

エクセルの関数を教えて欲しいです。


会員番号順に並んだ名簿を、別のシートへフリガナの「ア行」「カ行」「サ行」・・・・と
フリガナ順で抽出をしたいのです。
下の図が関数でやりたいイメージです。
私自身、ＶＢＡは全く分からなく、関数なら初心者程度に分かりますので関数で
教えて下さい。
宜しくお願いします。

No.4 ベストアンサー 回答者： KURUMITO 回答日時： 2010/12/15 17:32

シート1に元の表があるとしてシート2にお求めの表を作るとします。

内容が複雑ですのでシート1では多くの作業列を作って対応することにします。
シート1のＤ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(C2="","",CODE(C2))

Ｅ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(D2="","",MATCH(D2,I:I,1))

Ｆ2セルには次の式を入力して下方にオートフィルドラッグします。

=(E2-1)*3+2+COUNT(E$2:E2)

なお、データベースになるものですがＨ1セルから下方には、ア、カ、サ、タ、ナ、ハ、マ、ヤ、ラ、ワとＨ10セルまで入力します。

Ｉ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(H1<>"",CODE(H1),IF(ROW(A1)=11,9700,""))

Ｊ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(H1="","",(ROW(A1)-1)*3+COUNTIF(D:D,"<"&I1)+1)

その後にシート2ではＡ1セルに次の式を入力してＣ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(AND(COUNTIF(Sheet1!$J:$J,ROW(A1))>0,COLUMN(A1)=1),INDEX(Sheet1!$H:$H,MATCH(ROW(A1),Sheet1!$J:$J,0))&"行",IF(COUNTIF(Sheet1!$J:$J,ROW(A1)-1)>0,IF(COLUMN(A1)=1,"名前",IF(COLUMN(A1)=2,"フリガナ",IF(COLUMN(A1)=3,"会員番号",""))),IF(COUNTIF(Sheet1!$F:$F,ROW(A1))>0,IF(COLUMN(A1)=1,INDEX(Sheet1!$B:$B,MATCH(ROW(A1),Sheet1!$F:$F,0)),IF(COLUMN(A1)=2,INDEX(Sheet1!$C:$C,MATCH(ROW(A1),Sheet1!$F:$F,0)),IF(COLUMN(A1)=3,INDEX(Sheet1!$A:$A,MATCH(ROW(A1),Sheet1!$F:$F,0)),""))),"")))

これでお望みの表がシート2に表示されます。

この回答へのお礼

分かりやすい説明と設定しやすい関数を教えていただきありがとうございました。
やはり下準備（作業列）の設置が必要とよくわかりました。
これから関数もＶＢＡも出来るように勉強いたします。
勉強になり、ありがとうございました。

お礼日時：2010/12/20 14:45

No.6 回答者： mitarashi 回答日時： 2010/12/15 19:00

関数の達人の常連の皆様には感服するばかりですが、このレベルになると、VBAを会得する方が簡単で、メンテナンス性も良いと思います。

と、言いながら、関数でやってみました。分類だけで、並べ替えはできておりません。エクセルの機能でフリガナ列をキーに並べ替えしておいて下さい。(^^;)
$だらけの式は、自分の目が拒絶反応を示しますので、名前をつけてやっています。動的な名前（参考URLをご覧下さい）にすると良いと思います。
A2:C?に、tableという名前を付けておきます。
F2に、{=INDEX(table,SMALL(IF((CODE(ASC(INDEX(table,,3)))>=177)*(CODE(ASC(INDEX(table,,3)))<182),ROW(INDEX(table,,3))-1,""),ROW()-1),2)}
G2に、{=INDEX(table,SMALL(IF((CODE(ASC(INDEX(table,,3)))>=177)*(CODE(ASC(INDEX(table,,3)))<182),ROW(INDEX(table,,3))-1,""),ROW()-1),3)}
H2に、{=INDEX(table,SMALL(IF((CODE(ASC(INDEX(table,,3)))>=177)*(CODE(ASC(INDEX(table,,3)))<182),ROW(INDEX(table,,3))-1,""),ROW()-1),1)}
という式を入れます。配列数式なので、Ctrl+Shift+Enterで確定させて下さい。
それぞれ、下方にエラーになるところまでコピーします。
これでア行の名前だけ抽出できました。
他の行（カ行、サ行．．．の事）は、177と182のASCIIコードのところを変更して応用して下さい。
ASCIIコードは、CODE関数で取得できます。
下方向に作成するのは調整が大変だと思いますので、横方向に作る方が楽だと思います。

参考URL：http://office.microsoft.com/ja-jp/excel-help/HA0 …

この回答へのお礼

ありがとうございました。関数もあまり理解していませんが、これから関数とＶＢＡを少しずつ勉強したいと思います。

お礼日時：2010/12/20 14:40

No.5 回答者： KURUMITO 回答日時： 2010/12/15 17:38

回答Ｎｏ4です。

シート1では最初にフリガナを重点に昇順で並べ替えを行ってから実施してください。
なお、Ｆ2セルへの入力の式は次のように訂正してください。

=IF(E2="","",(E2-1)*3+2+COUNT(E$2:E2))

No.3 回答者： CoalTar 回答日時： 2010/12/15 17:04

数式で行ったらとんでもないことに、、、

しかも、7文字分しか検査してないし、
重複は13人までだし。

D2セルに 7文字分のコードを検査し、足す
=SUM((MATCH(MID(JIS(C2)&REPT("ァ",MAX(0,7-LEN(JIS(C2)))),{1,2,3,4,5,6,7},1),
CHAR(9504+ROW($A$1:$A$86)))+13)*100^{6,5,4,3,2,1,0})
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式です({}で囲まれる)

E2セル 重複チェック
=COUNTIF($C$2:C2,C2)

F2セル 重複考慮
=D2*10+E2

A12セル 連番
B12セル 並び替え
=SMALL($F$2:$F$9,A12)

C12セル 会員番号の抽出
=INDEX(A$2:A$9,MATCH($B12,$F$2:$F$9,0))

D12セル ○行に属するか
=MATCH(B12/10^13,{14,24,34,44,55,60,75,80,86,92})

A21セル ○行の数値化
=MATCH(LEFT(JIS(B21),1),{"ァ";"カ";"サ";"タ";"ナ";"ハ";"マ";"ャ";"ラ";"ワ"})

A22セル 連番とエラー処理
=IF(COUNTIF($D$12:$D$19,$A$21)<ROW(A1),"",ROW(A1))

B22セル 会員番号の抽出
=INDEX($C$12:$C$19,MATCH($A$21,$D$12:$D$19,0)+A22-1)

数式ではこれが限界かなあ。添付画像参照
はっきり言ってマクロの記録でマクロを覚えたほうが正確で簡単だ。

この回答へのお礼

関数にも制限等がわかりました。ありがとうございました。

お礼日時：2010/12/20 14:46

No.2 回答者： mu2011 回答日時： 2010/12/15 16:06

関数では無理とは言いませんが非常に難しい、又、ＶＢＡは無理だという事なので次の方法は如何でしょうか。

抽出結果は元シート構成と同じになります。
エクセル２００３以下の操作手順例として元シートをSheet1からSheet2にア行を抽出します。
（１）Sheet2のA1は未入力、A2に=SUM(COUNTIF(Sheet1!C2,{"ア*","イ*","ウ*","エ*","オ*"}))>0を入力（文字列はsheet1のフリガナ列に合わせて下さい、但しアスタリスクは半角）
（２）Sheet2の上でデータ→フィルタ→フィルタオプションの設定→指定した範囲を選択、リスト範囲欄にsheet1!$A:$C、検索条件欄に$A$1:$A$2、抽出範囲欄にA4→ＯＫ

No.1 回答者： aokii 回答日時： 2010/12/15 14:40

かなり面倒ですので、フリガナ順で並べ替えて手動で作る方法をお勧めします。

この回答へのお礼

ご回答ありがとうございます。
名簿の数が多いのと毎月更新しなくてはいけないので・・・
悩むところです。

ありがとうございました。

お礼日時：2010/12/15 15:56