Excelのマトリクス表をリスト表へ変換したい

Excelで作成したマトリクス形式の表を、データとして扱い易くなるよう、リスト形式の表に並び替える手順とそれを自動処理するマクロを検討します。

【更新】2018/01/13　値が空白やゼロのだったらリストから除外するバージョンのマクロを追加しました。

マトリクス表をデータ化したい

要は下図の左の表から右の表へ表の形式を変換したいです。

これらの表形式を何と呼んだものか、日本語の標準的な呼び名が良く分からないのですが、 左の表形式はマトリクス、集計表、クロステーブル、ピボットテーブル、多次元テーブル、横持ちデータなどの呼び名が使われているようです。

一方、上図右の表形式は、単にテーブル、リスト、一覧表、フラットデータ、縦持ちデータなどと呼ばれているようです。

この記事ではとりあえず、各々「マトリクス」と「リスト」と呼ぶようにします。

マトリクス表は、公表される統計データや、各部署の営業実績表、データ連携用コードマッピング表など、いたるところで使われています。

マトリクスは人が見る分には良くても、このままデータベースに取り込む元データ（CSVなど）としては使えず、いったんリスト形式に変換・加工する必要があります。

列ごとに配置換えするコピー＆ペーストの単純作業を地道に繰り返せばいいのですが、データ量によっては非人道的な作業になります。

Excel ファイルを読み取り専用で開きたい

今回は、Excel ファイルを読み取り専用で開き直す手順を検討し、それを自動化するマクロも作成します。

【追記】2018/03/11

当記事では既に開いている Excel ファイルを読み取り専用で「開き直す」方法を紹介しています。 そうではなく、始めから読み取り専用モードで開く方法 を知りたい方は、ファイルの「右クリック」から読み取り専用で開く方法を、 以下の別記事にしましたので、そちらもご参考にしてください。

www.shegolab.jp

読み取り専用詐欺

Windows エクスプローラで Shfit キーを押しながら Excel ファイルを右クリックすると、「読み取り専用で開く」というメニュー項目が現れるのですが、これで開いてみても「読み取り専用」にはなりません。

これは昔からある Excel の有名なバグらしいのですが、筆者の環境（ Windows 10 の Excel 2013 ）でもまだ直っていませんでした。

もしかしたら最新バージョンの Excel ではすでに解消しているのかもしれませんが、残念ながら手元にないため確認できていません。 （皆さんの環境で直っていたら教えてください）

Excel ファイルを読み取り専用で開くことはできないのでしょうか。

セル書式なしでコピー＆ペーストしたい

貼り付けの形式を選択することで、セル書式なしでセルの内容だけ貼り付ける手順を検討し、またその操作をマクロ化してみます。

セルの貼り付けは気を使う

セル範囲を単純にコピー＆ペーストすると、その値だけではなく、罫線や文字色等の書式も一緒に貼り付けられます。

貼り付け先でせっかくの罫線などが破壊されてイラッとします。

それを避けるには、貼り付け時に「貼り付けのオプション」か「形式を選択して貼り付け」から、セルの書式を含めないようオプションを選択する必要があります。

貼り付けのオプションには、「書式なし」という単純な選択肢は見当たらないのですが、色々やってみて一番筆者のイメージに近いのは「数式と数値の書式」というオプションのようです。

「数式と数値の書式」では、

• 定数（数値や文字列）はそのまま貼り付けられます
• 数式は値に展開されず、数式のままです
• 数式内の相対参照も反映されます
• 数式の書式（小数点以下の桁数や通貨、日付などの書式）も一緒に貼り付けられます

逆に、数値の書式もコピーせずに貼り付け先に合わせたい場合には、「数式」の方を選択します。

さらに、数式を値（定数）に展開したい場合には「値」や「値と数値の書式」を選びます。

正直、覚えられません。

これらのコマンドの細かい仕様の違いを字面やアイコンから判断するは分かりにくく、たかがペーストするだけでそんな神経を使いたくもありません。

よく使う「下方向へコピー」（Ctrl + D）では貼り付けの形式を選べないのも中途半端な感じです。

空白セルに上のセルの値を一括コピーしたい

空白セルに上のセルの値を一括コピーする手順を検討します。またその処理を自動で行うマクロも作成します。

同上空白セル

親切なのか「同上」なセルが空欄になっている Excel シートがよくあります。

このようなシートをデータとして使う（CSVなど）には、空白セルを上の方にあるデータで補完させなければなりません。

数か所までならコピー＆ペーストや下方向にコピー(Ctrl + D)を黙々とやればいいのですが、何か所もあったり行数も多かったりすると行ったり来たりでやっていられません。

一括で空白セルを埋める方法はないのでしょうか。

Excel ファイルを読み捨てたい

不要な Excel ファイルを素早くごみ箱へ捨てる方法を検討します。また、開いている Excel ファイルを自動的に閉じてゴミ箱へ移動するマクロのサンプルプログラムを公開します。

増殖する Excel ファイル

Excel は油断していると要らないファイルがどんどん溜まりますね。

「マイドキュメント」フォルダには「Book123.xlsx」のような連番や「○○コピーコピーコピー.xlsx」とかいう謎のファイルが大量に増殖しています。

「デスクトップ」には受信メールの添付 xlsx ファイルや ZIP 解凍した xlsx ファイルなどが散在しています。

Web ブラウザや SNS アプリからダウンロードしたファイルもどこかに保存されていて、閲覧する度に「資料 (2).xlsx」 といった連番が知らないうちに増えていきます。

不要なファイルは削除すればいいのですが、問題は、そのファイルを捨てていいのかどうか、開いて確認してみないと分からないことです。

ファイル内容を見て、「あ、これゴミだ」と思っても、それを閉じてから保存先のフォルダを探しファイルを探して削除するというのも手間で、なにかと後回しになりがちです。

後で溜まった古い Excel ファイルをいっぺんに整理するにしても、まとまった時間が必要です。

そもそもダウンロードした資料やメールに添付された報告書なら、オリジナルがサーバに保存されていて何時でもまた取得できるので、取っておく必要さえありません。

そのようなファイルは見終わったらすぐに削除する習慣を身に付けておきたいものです。