添付写真のようなデータシートがあります。
レコードの行は20,000行ほどあります。
このデータから集計した表を作りたいのです。
【表の内容】
・縦列名称 氏名
・横列名称 月・日
・表の中に 勤務種別を表示する
・表の外にリストフィルタがあり、年と月を選べるようにする
ピヴォットテーブルで上記表を実現したかったのですが、
ピヴォットテーブルだとデータフィールドが計算された値(最大値、最小値、平均値など・・)
ですので、断念しました。
できるだけ重くならずに、早く作れる方法を教えていただけないでしょうか?
No.2ベストアンサー
- 回答日時:
回答の前に確認です。
データのサンプルでは、
2014年4月27日の『か』さんの勤務種別は、すべて『A1』
2014年4月1日の『き』さんの勤務種別は、すべて『A2』
のように、特定の年月日において特定の人物の勤務種別はすべて同じになっています。本物の
データも同様でしょうか?
そうであれば、ピボットテーブルで処理することがベストだと思いますので、勤務種別を数値に
置き換えて進めることにします。
ここでは勤務種別がサンプルのように2文字という前提で回答します。
手順
1. サンプルではT列が最終列なので、その次の列(U列)に変換したデータを作成することにします。
2. U1セルには「勤務種別2」などの適当な名前を入力します。
3. 最終列2行目(U2セル)に『=CODE(G2)*10000+CODE(MID(G2,LEN(G2),1))』を入力して
最下行までコピーします。
サンプルどおりに勤務種別が全角の『A1』であれば、「90299005」になります。
これは文字列をcode関数で数値に変換して、A⇒9029、1⇒9005 になったものを、
『9029*10000+9005』 の計算をして一つの数値にしたものです。
5. ピボットテーブルを作成します。
6. ピボットテーブルのフィルター機能を残しつつ、数値「90299005」を文字列『A1』に逆変換
することは困難です。かといって、ピボットテーブルを使わずにご希望のフィルターを設定する
のも困難です。
7. このままでは、1桁目 A:9029、B:9030、・・・、2桁目 1:9005、2:9006、・・・
であり、読みかえが困難です。
8. 実際に「勤務種別」で使っている文字にあわせて最も小さいものが『1』になるように 手順3で
入力した式を修正します。
サンプル通りに『A1』から始まるのであれば、1桁目の『A』⇒「1」、2桁目の『1』⇒「1」
となるように
『=(CODE(G2)-9028)*10000+CODE(MID(G2,LEN(G2),1))-9004』 とすれば、
『A1』⇒「10001」
『A2』⇒「10002」
『B1』⇒「20001」
となり使いやすくなります。
9. 報告する等で『A1』の形式に戻すことが必要なら、ピボットテーブルの機能が不要になった
段階で逆変換します。
手順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
- 回答日時:
ピボットテーブルで出来なかったのでしょうか…?
http://hamachan.info/excel/piboto.html
オートフィルターで出来るのなら、
プログラムを組んでみるのもありかもしれません。
回答ありがとうございます。
やはりピヴォットテーブルがベストですよね。。
データフィールドに集計させないようにするということが望みなので、
簡単だと思ったのですが、すごく難しいということが分かってきました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 【マクロ】同じフォルダ内にある複数ブックから1つのブック内の1シートにデータを集めたい 6 2022/09/28 18:16
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
- Access(アクセス) Accessテーブルの結合で別々のテーブルのフィールドを組み合わせて値を出す方法について 2 2022/07/20 19:43
- Excel(エクセル) 別シートに毎回異なるデータをコピーする 7 2022/06/24 09:02
- Excel(エクセル) Excelで全クラスのランキング表を作成したい 4 2022/05/24 15:28
- Excel(エクセル) 非表示にしたい行をグループ化して折り畳み 4 2022/09/17 20:17
- PHP 配列の値の更新方法について 1 2022/08/05 09:49
- C言語・C++・C# このプログラミングの問題を教えてほしいです。 キーボードからデータ数nとn個のデータを入力し、平均値 3 2022/12/19 22:51
- Excel(エクセル) IF 関数で「〇〇 という文字を含む場合」の分岐処理で表示された数字はSUMで数字集計できますか? 3 2022/08/02 16:29
- その他(Microsoft Office) ピボットテーブルへの集計フィールド挿入 1 2023/02/26 11:33
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで1列に500行並んだデ...
-
Excelで2つのデータの突合せを...
-
エクセルで日付から日にちを削...
-
Excelで2行単位のソートの出来...
-
複数の条件に合う行番号を取得...
-
Countifよりも早く重複数をカウ...
-
エクセルで、重複データを除外...
-
VBA 数式を最終行までコピー
-
Excelの30個ずつの平均値の出し方
-
エクセルデータの比較をしたい。
-
excel:別シートの値を飛び飛び...
-
エクセルで沢山のレコードの最...
-
500行の中から、多い順に抽出す...
-
Excelの関数でこんな処理ができ...
-
エクセルで1つの会社名に対して...
-
エクセルで時刻だけを抜き出す...
-
エクセルVBA C列に特定の文字列...
-
Excelのマクロの使い方
-
エクセル2016にて、行挿入&コピ...
-
エクセルで並び替えをしたあと...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで日付から日にちを削...
-
複数の条件に合う行番号を取得...
-
エクセルで1列に500行並んだデ...
-
Excelで2つのデータの突合せを...
-
Excelで2行単位のソートの出来...
-
excel:別シートの値を飛び飛び...
-
エクセルで、重複データを除外...
-
Countifよりも早く重複数をカウ...
-
Excelの30個ずつの平均値の出し方
-
エクセルで横並びの複数データ...
-
エクセルVBA C列に特定の文字列...
-
VBA 数式を最終行までコピー
-
500行の中から、多い順に抽出す...
-
エクセルで1つの会社名に対して...
-
VBA 大きなtxtテキストファ...
-
【エクセル】1列内に複数ある同...
-
エクセルで時刻だけを抜き出す...
-
エクセル~空白のセルのある行...
-
Excelの関数でこんな処理ができ...
-
Excel VBA 【QueryTables.Add】...
おすすめ情報