• ベストアンサー

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

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

質問者が選んだベストアンサー

  • ベストアンサー
  • KURUMITO
  • ベストアンサー率42% (1835/4283)
回答No.4

シート1に元の表があるとしてシート2にお求めの表を作るとします。 内容が複雑ですのでシート1では多くの作業列を作って対応することにします。 シート1のD2セルには次の式を入力して下方にオートフィルドラッグします。 =IF(C2="","",CODE(C2)) E2セルには次の式を入力して下方にオートフィルドラッグします。 =IF(D2="","",MATCH(D2,I:I,1)) F2セルには次の式を入力して下方にオートフィルドラッグします。 =(E2-1)*3+2+COUNT(E$2:E2) なお、データベースになるものですがH1セルから下方には、ア、カ、サ、タ、ナ、ハ、マ、ヤ、ラ、ワとH10セルまで入力します。 I1セルには次の式を入力して下方にオートフィルドラッグします。 =IF(H1<>"",CODE(H1),IF(ROW(A1)=11,9700,"")) J1セルには次の式を入力して下方にオートフィルドラッグします。 =IF(H1="","",(ROW(A1)-1)*3+COUNTIF(D:D,"<"&I1)+1) その後にシート2ではA1セルに次の式を入力してC2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。 =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に表示されます。

katsuodesu
質問者

お礼

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

その他の回答 (5)

  • mitarashi
  • ベストアンサー率59% (574/965)
回答No.6

関数の達人の常連の皆様には感服するばかりですが、このレベルになると、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/HA001126115.aspx
katsuodesu
質問者

お礼

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

  • KURUMITO
  • ベストアンサー率42% (1835/4283)
回答No.5

回答No4です。 シート1では最初にフリガナを重点に昇順で並べ替えを行ってから実施してください。 なお、F2セルへの入力の式は次のように訂正してください。 =IF(E2="","",(E2-1)*3+2+COUNT(E$2:E2))

回答No.3

数式で行ったらとんでもないことに、、、 しかも、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) 数式ではこれが限界かなあ。添付画像参照 はっきり言ってマクロの記録でマクロを覚えたほうが正確で簡単だ。

katsuodesu
質問者

お礼

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

  • mu2011
  • ベストアンサー率38% (1910/4994)
回答No.2

関数では無理とは言いませんが非常に難しい、又、VBAは無理だという事なので次の方法は如何でしょうか。 抽出結果は元シート構成と同じになります。 エクセル2003以下の操作手順例として元シートをSheet1からSheet2にア行を抽出します。 (1)Sheet2のA1は未入力、A2に=SUM(COUNTIF(Sheet1!C2,{"ア*","イ*","ウ*","エ*","オ*"}))>0を入力(文字列はsheet1のフリガナ列に合わせて下さい、但しアスタリスクは半角) (2)Sheet2の上でデータ→フィルタ→フィルタオプションの設定→指定した範囲を選択、リスト範囲欄にsheet1!$A:$C、検索条件欄に$A$1:$A$2、抽出範囲欄にA4→OK

  • aokii
  • ベストアンサー率23% (5210/22062)
回答No.1

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

katsuodesu
質問者

お礼

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

専門家に質問してみよう