【Googleスプレッドシート】CSVを行列で取り込む応用!IMPORTDATAとSPLITの併用

CSVデータをスプレッドシートに取り込もうとしたとき、IMPORTDATA関数を使うと1つのセルにすべてのデータが横並びになってしまうことがあります。本来ならば各行と各列に分割されてほしいのに、そのままでは表として使えません。そんなときに役立つのが、IMPORTDATAとSPLIT関数を組み合わせる方法です。この記事では、IMPORTDATAで取得したCSVを、SPLITでカンマ区切りに分割し、さらにTRANSPOSEや配列数式を使って正しい行列に変換する応用テクニックを解説します。

具体的には、IMPORTDATAで読み込んだテキストをSPLITで列に分け、さらに各行を別々のセルに展開する方法を、手順を追って説明します。また、よくあるトラブルや対処法についても紹介しますので、CSVの行列取り込みでお困りの方はぜひご参考ください。

IMPORTDATAとSPLITがCSVを行列にする仕組み

IMPORTDATA関数は、指定したURLからCSV形式のデータをテキストとして取得し、1つのセルに収めます。このとき、CSVの改行やカンマはそのままテキスト内に保持されます。そのため、IMPORTDATAだけではデータが1セルに縦横が混ざった状態で表示され、行と列に分かれません。

そこでSPLIT関数を使います。SPLITは、テキストを指定した区切り文字で分割し、横方向のセルに展開します。ただし、IMPORTDATAの結果には複数行のデータが含まれているため、まず改行で行を分割する必要があります。改行で分割するには、SPLITの区切り文字に改行コード(“\n”)を指定します。これで各行が別々のセル(縦方向)に分かれます。しかし、各行にはまだカンマ区切りの列データが含まれています。そこで、外側のSPLITで改行分割した後、さらに内側のSPLITでカンマ分割をする必要があります。この2段階の分割を実現するには、ARRAYFORMULAを使って各行に対してSPLITを適用します。

具体的な式は、=ARRAYFORMULA(SPLIT(IMPORTDATA(URL), “\n”)) という形になります。この式は、IMPORTDATAで取得したテキストを改行で分割し、それぞれの行に対してSPLIT(カンマ区切り)を自動的に適用します。結果として、CSVの各行がスプレッドシートの行に、各列が列に展開され、行列として取り込めます。

CSVを行列で取り込む具体的な手順

1. URLを用意する
   取り込みたいCSVファイルの公開URLを用意します。直接ダウンロードできるURLが必要です。Google Drive上にあるCSVの場合は、共有設定を「リンクを知っている全員(閲覧可)」に変更し、共有リンクを取得します。そのリンクの末尾を「?export=download」に変更すると、CSVを直接ダウンロードできるURLになります。
2. IMPORTDATA関数でCSVを取得する
   任意のセルに「=IMPORTDATA(“URL”)」と入力します。これでCSVの内容が1つのセルにテキストとして表示されます。データが正しく取得されたことを確認してください。エラーの場合はURLが正しいか、アクセス権限を確認します。
3. ARRAYFORMULAとSPLITで行列に変換する
   別のセル(例:A1)に以下の数式を入力します。
   =ARRAYFORMULA(SPLIT(IMPORTDATA(“URL”), “\n”))
   この式では、IMPORTDATAの結果を改行(“\n”)で分割し、さらに各行を自動的にカンマで分割します。その結果、CSVの行と列が正しく配置されます。
4. 必要に応じて列番号を調整する
   CSVによっては区切り文字がカンマ以外(タブやセミコロン)の場合があります。その場合はSPLITの区切り文字を変更します。例えばタブ区切りなら「”\t”」、セミコロンなら「”;”」と指定します。
5. 結果を確認して整形する
   データが正しく行列に展開されたら、ヘッダー行の整形や数値の書式設定を行います。必要に応じて、他のセルにコピーして固定値として貼り付けることもできます。

例:公開CSVを利用した実演

例えば、政府公開データのCSV(例:https://example.com/data.csv)を想定します。以下の手順で取り込みます。

1. セルA1に「=IMPORTDATA(“https://example.com/data.csv”)」と入力します。
2. セルC1に「=ARRAYFORMULA(SPLIT(A1, “\n”))」と入力します。
3. すると、C列以降にデータが展開されます。ただし、この方法では各行がSPLITで分割される際に、カンマが残っている場合があります。より確実な方法は、直接「=ARRAYFORMULA(SPLIT(IMPORTDATA(“https://example.com/data.csv”), “\n”))」と1つのセルに入力することです。

CSV取り込みで失敗しがちなポイントと対処法

改行コードが正しく認識されない

CSVファイルの改行コードは、WindowsではCR+LF(\r\n)、Mac/LinuxではLF(\n)の違いがあります。IMPORTDATAは多くの場合LFを改行として認識します。CR+LFの場合は、SPLITの区切り文字に「”\r\n”」と指定すると正しく分割できます。もしうまく分割できない場合は、まずIMPORTDATAの結果を確認し、改行がどのように表れているか調べます。

CSV内にカンマを含む引用符付きデータがある

CSVでは、データ内にカンマが含まれる場合、そのフィールドは引用符で囲まれます(例:”商品A, サイズM”)。SPLIT関数は引用符を考慮せずにカンマで分割してしまうため、データが壊れることがあります。このような場合は、Google Apps Scriptを使ってパースするか、クエリ関数(QUERY)のimport機能を利用する方が安全です。ただし、引用符付きデータが少なければ、手動で修正することも可能です。

IMPORTDATAの取得制限に引っかかる

IMPORTDATA関数は、Googleスプレッドシートの制限により、1回あたりのデータサイズや取得頻度に制限があります。大きなCSVファイルや頻繁な更新が必要な場合は、読み込みに失敗することがあります。その場合は、CSVをGoogle Driveにアップロードし、IMPORTRANGEではなく、IMPORTDATAで直接URL指定するか、Google Apps Scriptでインポートする方法を検討します。

IMPORTDATA単体とSPLIT併用の違い

まとめ

IMPORTDATAで取得したCSVを行列に変換するには、ARRAYFORMULAとSPLITを組み合わせる方法が効果的です。改行で行を分割し、カンマで列を分割することで、スプレッドシート上でそのまま分析や加工に使える表データを得られます。引用符付きデータや改行コードの違いなど、注意すべき点もありますが、基本的なCSVであればこの方法で簡単に取り込めます。

より高度なCSVインポートが必要な場合は、Google Apps ScriptやGoogle Sheetsの「データ」メニューのインポート機能も検討してください。ただし、リアルタイム更新が必要な場合はIMPORTDATAとSPLITの組み合わせが最も手軽な方法です。ぜひ実際のデータで試してみてください。