EXCEL2003で上手く集計したい

質問

添付写真のようなデータシートがあります。
レコードの行は20,000行ほどあります。

このデータから集計した表を作りたいのです。

【表の内容】
・縦列名称　氏名
・横列名称　月・日
・表の中に　勤務種別を表示する
・表の外にリストフィルタがあり、年と月を選べるようにする

ピヴォットテーブルで上記表を実現したかったのですが、
ピヴォットテーブルだとデータフィールドが計算された値（最大値、最小値、平均値など・・）
ですので、断念しました。

できるだけ重くならずに、早く作れる方法を教えていただけないでしょうか？

回答No.2

googoo900 · ベストアンサー

回答の前に確認です。
データのサンプルでは、
　2014年4月27日の『か』さんの勤務種別は、すべて『A1』
　2014年4月1日の『き』さんの勤務種別は、すべて『A2』
のように、特定の年月日において特定の人物の勤務種別はすべて同じになっています。本物の
データも同様でしょうか？

そうであれば、ピボットテーブルで処理することがベストだと思いますので、勤務種別を数値に
置き換えて進めることにします。
ここでは勤務種別がサンプルのように2文字という前提で回答します。

手順
1.　サンプルではT列が最終列なので、その次の列(U列)に変換したデータを作成することにします。
2.　U1セルには「勤務種別２」などの適当な名前を入力します。
3.　最終列2行目(U2セル)に『=CODE(G2)*10000+CODE(MID(G2,LEN(G2),1))』を入力して
　　最下行までコピーします。
　　サンプルどおりに勤務種別が全角の『Ａ１』であれば、「90299005」になります。
　　これは文字列をcode関数で数値に変換して、Ａ⇒9029、１⇒9005　になったものを、
　　　『9029*10000+9005』　の計算をして一つの数値にしたものです。
5.　ピボットテーブルを作成します。
6.　ピボットテーブルのフィルター機能を残しつつ、数値「90299005」を文字列『Ａ１』に逆変換
　　することは困難です。かといって、ピボットテーブルを使わずにご希望のフィルターを設定する
　　のも困難です。
7.　このままでは、1桁目 Ａ：9029、Ｂ：9030、・・・、2桁目 １：9005、２：9006、・・・
　　であり、読みかえが困難です。
8.　実際に「勤務種別」で使っている文字にあわせて最も小さいものが『1』になるように　手順3で
　　入力した式を修正します。
　　サンプル通りに『Ａ１』から始まるのであれば、1桁目の『Ａ』⇒「1」、2桁目の『１』⇒「1」
　　となるように
　　『=(CODE(G2)-9028)*10000+CODE(MID(G2,LEN(G2),1))-9004』　とすれば、
　　　『Ａ１』⇒「10001」
　　　『Ａ２』⇒「10002」
　　　『Ｂ１』⇒「20001」
　　となり使いやすくなります。
9.　報告する等で『Ａ１』の形式に戻すことが必要なら、ピボットテーブルの機能が不要になった
　　段階で逆変換します。
　　手順3の式なら
　　『=CONCATENATE(CHAR(ROUNDDOWN(B6,-4)/10000),CHAR(B6-ROUNDDOWN(B6,-4)))』
　　手順8の式なら
　　『=CONCATENATE(CHAR(ROUNDDOWN(B6+90249008,-4)/10000),CHAR(B6+90249008-ROUNDDOWN(B6+90249008,-4)))』
　　です。
　　※B6は「90299005」等のデータのセル位置になるように必要に応じた修正が必要です。

お礼

回答ありがとうございます。
『特定の年月日において特定の人物の勤務種別はすべて同じになっています。本物の
データも同様でしょうか？』⇒はい全て同じです。

やはり1列増やして、勤務種別をダミーの数値データにして、ピヴォットテーブルから
逆変換するということで、考えたいと思います。

提示いただいた、関数式は一度トライさせていただきます。データが20,000行あるので重くなるのが心配ですが、、、
ありがとうございました。

回答No.1

satoron666

ピボットテーブルで出来なかったのでしょうか…？
http://hamachan.info/excel/piboto.html

オートフィルターで出来るのなら、
プログラムを組んでみるのもありかもしれません。

お礼

回答ありがとうございます。
やはりピヴォットテーブルがベストですよね。。
データフィールドに集計させないようにするということが望みなので、
簡単だと思ったのですが、すごく難しいということが分かってきました。