はじめに
「EXCELの１シートの最大行数を超えてしまった。。。」
空でないセルをワークシートの外に押し出してしまうため、新しいセルを挿入できません。空のように見えるセルであっても、空白の値、書式、または数式が含まれている場合があります。挿入するためにの領域を確保できるように十分な行または列を削除してから、もう一度やり直してください。
今まで作ってたデータに、新しいデータをコピペ追加しようとしたら出てきたメッセージです。
なにこれ？
何度やっても出てくる。ただ、コピペしてるだけなのに。なんで？
これ、EXCELの１シートの最大行超えた時に出てくるやつです。EXCELで１シートに入れられる最大行数は、「1048576行」です。これ以上は入れられません。※EXCELの仕様(外部サイト)
ちなみに、満タン行数から無理やり行を挿入しようとすると、「コピー領域と貼り付け領域のサイズが違うため、これをここに貼り付けることが出来ません。貼り付け領域内のいずれか１つのセルを選ぶか、または同じサイズの領域を選び、もう一度貼り付けてください。」って怒られます。
最近のWEBの効果測定データなんか溜めて触ってると結構すぐにぶち当たります。コレばかりはどうしようもありません。そろそろ普通のEXCELの枠を超えて旅立つときが来たということです。諦めて下さい。
解決方法
解決策は色々ありますが、とりあえず一番スタンダードな方法で解決します。きっとこれを読んでる読者さんが、今まで敬遠していただろうACCESS(アクセス)さんの力を素直に借りましょう。でも安心して下さい。あくまでもEXCEL軸。ACCESSさんにはちょっとだけ手伝ってもらうだけのつもりです。
今回のシチュエーション
大量のデータをEXCELの１シートに元データとして溜めて、ピボットテーブルで集計処理していた。この度データ行数がEXCELの最大行を超えてしまったため、元データをACCESSに移して、ACCESSを元データにし今までと同じようにEXCEL上のピボットテーブルで処理を行う。
EXCELでは、行数の制限があるので入り切らなかったデータも、ACCESSは行数(レコード数)の制限がないので入っちゃうよっていうことです。それを利用して同じようにEXCELでピボットテーブルを組むことが出来るのでやってみましょう。（※ACCESSは1ファイル約2GBの容量制限はあります）
こんなサンプル使います。ある架空の会社の求人媒体の応募効果のデータです。2つ目の図にあるように、ピボットテーブルを組んでいます。
今は3ヶ月分だけですが、1年、2年と溜めていくとすぐにいっぱいになってしまいます。それでは作業をしていきましょう。
作業の流れ
１．ACCESSの空データを作成する。
２．EXCELからそのまんまACCESSに取り込む
３．EXCELにピボットテーブルを作成し、ソース（元データ）をACCESSのテーブルに設定する
ステップは上記の３つです。
１．ACCESSの空データを作成する。
まずは、ACCESSの空のデータを作成します。
こんなアイコンのやつですね。もしかしたら入ってない人もいるかも知れないですが。。。入ってることが前提で進めさせていただきます。
クリックすると次の画面が出てきます。
ACCESS立ち上げると、なんか色々出てくるんで、うげっ！ってなるけど見なかったことにして、左上の「空のデータベース」ってのを押して下さい。
この画面が出てくるので、ファイル名を変更したり、作成される場所を選んだり色々整えます。整ったら「作成」ボタンを押して下さい。
こんな感じになればOK！一旦、そっとしておいて下さい。
２．ACCESSにEXCELの元データをそのまんま取り込む
次に、EXCELのデータをACCESSに取り込みたいと思います。まずはEXCELの状態を確認しましょう！
まずはEXCELファイルがどこにあるか一応確認しといて下さい。どこにあっても大丈夫なんですが、僕の場合はさっき作ったACCESSファイルと同じところにあります。
ACCESSファイル名：Database3.accdb
EXCELファイル名：応募効果データ_サンプル
※「Database3.laccdb」ってのもあると思いますが、これACCESSファイル開いてると出てくるやつなんで気にしないで良いです。ACCESS閉じたら消えます。
EXCELファイルの中身も確認しときましょう。
「raw」シートと「pivot」シートがあって、「raw」シートに元データが入っています。
確認出来たところで、さっき作ったACCESSに戻って、EXCELのデータを取り込みましょう。
ACCESSの画面から、「外部データ」タブ→「新しいデータソース」→「ファイルから」→「Excel」の順に進みます。
次のような画面になります。
「参照」で先程の取り込むExcelファイルを選びます。
①「現在のデータベースの新しいテーブルにデータをインポートする」か②「レコードのコピーを次のテーブルに追加する」かを選びます。①は新しく作る、②は今あるテーブルに追加する。とおぼえといて下さい。②を選んだ場合は、右のボックスでどのテーブルに追加するか選びます。今回は、①で新しいテーブルに作ることにしましょう。選択して「開く」を押してください。
この画面で、改めて確認して「OK」を押します。
選択したExcelファイルのシート名が並んでいますね。元データが入っている「raw」シートを選びます。下に選んだシートのサンプルが表示されるので、確認して下さい。それでは「次へ」で次に進みます。
エラー！？？「先頭行に、Accessのフィールド名として使用出来ないデータが含まれています。フィールド名として有効な名前が自動的に割り当てられます。」と出ました。
どこか分かんね～な。でも自動的にやってくれるみたいなんで、そのままGo！します（笑）※多分「職種名称（入稿時選択）」の「（）」じゃないかと思います。全角なんで半角にされるとかかな？まあ良いでしょう。
進みます。
先頭行をフィールド名として使うかどうかをチェックします。今回は先頭の行が項目・見出し名なので、コレをフィールド名として使います。
「次へ」で進みます。
この画面は、各フィールドの型を指定する画面なのですが、基本的にはいい感じにしてくれてるのでそのまま進んで下さい。
これも、主キーを設定するかどうか決めるところですが、大体このままで大丈夫です。
そのまま「次へ」で進みます。
いよいよ終盤です。「インポート先のテーブル」は名前も変更出来ますが、特に問題なければ自動的に選んだEXCELのシート名が入ります。今回はこのまま進みます。「完了」で決定して下さい。
ちょっと時間がかかるかもしれませんが、のんびりまって下さい。次のような画面が出れば完了です。
閉じるを押すと次のような画面になります。「raw」というテーブルが出来上がってますね。コレで、EXCELデータの取り込みは完了です。
以降追加する場合は、②「レコードのコピーを次のテーブルに追加する」を選んで「raw」テーブルに追加していって下さい。
３．EXCELにピボットテーブルを作成し、ソース（元データ）をACCESSのテーブルに設定する
では、最後にEXCELでピボットテーブルを作成して、ソースをACCESSのテーブルに設定しましょう。
EXCELファイルは新しく作っても、今あるものを使ってもどちらでもOKです。せっかくあるので、「応募効果データ_サンプル.xlsx」のファイルに新しいシートを作って、そこにピボットテーブルを作成します。
次にピボットテーブルを作っていきます。挿入タブのピボットテーブルをクリックして下さい。
※こちらも参考に。
次の画面が出るので、「外部データソースを使用」をチェックして、「接続の選択」をクリック
続けて、「参照」をクリックしてACCESSのファイルを選択します。
ACCESSファイルを選択して、「開く」で進みます。
これは、このまま「OK」
これもそのまま「OK」どんどん進みます。
ここでは、ACCESSで取り込んだ「raw」テーブルを選びます。
「OK」で進みます。
この画面に戻ってきました。接続名がきちんと選択したACCESSになっているか確認して「OK」で進みます。
ピボットテーブルを作る場所は、既存のワークシートを選んでいます。
ピボットテーブルが作成されました。フィールドを選択すると集計が出来ることが確認できます。
これで完成です！
以降は、ACCESSデータにデータを追加していき、ピボットテーブルを更新すればデータが反映されるようになります。
まとめ
このように、ピボットテーブルの元データをACCESSにすることによって、大量のデータ集計も可能になります。EXCELだけでやっているときよりもスピードも早くて快適ですよ^_^
行数が足りなくなったときだけでなく、元データが多くて動きが鈍くなって来た時にも、この方法で快適に操作出来るようになります。
おまけ
EXCELだけで突破する方法も記事にしました(^^)
コメント