
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に表示されます。
この回答へのお礼
お礼日時:2010/12/20 14:45
分かりやすい説明と設定しやすい関数を教えていただきありがとうございました。
やはり下準備(作業列)の設置が必要とよくわかりました。
これから関数もVBAも出来るように勉強いたします。
勉強になり、ありがとうございました。
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/HA0 …
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)
数式ではこれが限界かなあ。添付画像参照
はっきり言ってマクロの記録でマクロを覚えたほうが正確で簡単だ。

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
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【関数】3つのセルの中で最新...
-
【マクロ】数式を入力したい。...
-
【マクロ】変数に入れるコード...
-
エクセルの関数について
-
【マクロ】excelファイルを開く...
-
エクセルのリストについて
-
【マクロ】実行時エラー '424':...
-
【マクロ】左のブックと右のブ...
-
Office2021のエクセルで米国株...
-
【マクロ】元データと同じお客...
-
エクセルのVBAで集計をしたい
-
【マクロ】【相談】Excelブック...
-
vba テキストボックスとリフト...
-
【マクロ】【配列】3つのシー...
-
他のシートの検索
-
【画像あり】オートフィルター...
-
エクセルシートの見出しの文字...
-
LibreOffice Clalc(またはエク...
-
【関数】=EXACT(a1,b1) a1とb1...
-
Dir関数のDo Whileステートメン...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【マクロ】元データと同じお客...
-
エクセルの関数について
-
【画像あり】オートフィルター...
-
エクセルのVBAで集計をしたい
-
エクセルのリストについて
-
【マクロ】数式を入力したい。...
-
【マクロ】【相談】Excelブック...
-
Office2021のエクセルで米国株...
-
【マクロ】実行時エラー '424':...
-
他のシートの検索
-
エクセルの複雑なシフト表から...
-
【マクロ】【配列】3つのシー...
-
vba テキストボックスとリフト...
-
【マクロ】左のブックと右のブ...
-
【マクロ】変数に入れるコード...
-
エクセルシートの見出しの文字...
-
【マクロ】別ファイルへマクロ...
-
【関数】同じ関数なのに、エラ...
-
Amazonでマイクロソフトオフィ...
-
ページが変なふうに切れる
おすすめ情報