ホームページ上の図表をエクセルに貼りつけようとすると、壊れてしまった――。そんな経験をされた方は多いでしょう。本稿では、ＨＴＭＬで記述されている図表がうまくエクセルに貼りつかない際に、それをうまくエクセル形式に「修復」するテクニックを取り上げています。

エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。

ネットの図表、エクセルに貼ると壊れた！…でも慌てずに！

インターネットのサイトなどで発見した図表をエクセルに貼りつけようとしてうまく行かないというケースは多いだろう。とくに、最近のHTML（ウェブ言語）は複雑であり、「見た目どおり」にエクセルに貼ることができないという事例が非常に多いのである。こうしたときに活躍するのが、「MOD関数」「INDIRECT関数」「MATCH関数」を組み合わせた基本テクニックだ。

セル番地は「アルファベット＋数値」

エクセルのシートは、タテ（つまり行）が数字、ヨコ（つまり列）がアルファベットでできている。

こんな具合だ（図表１）。

図表１　エクセルのシートの基本構造

そして、セルの番地については、「A1セル」「A2セル」、といった具合に、すべてこの「アルファベット＋数字」の組み合わせで特定できる。これがエクセルの基本構造だ。

• 行（row）…数値
• 列（column）…アルファベット

もちろん、これを「R1C1」という相対参照形式などで表示することも可能だが、普段、やはりこの「A1」形式などの「絶対参照」に慣れているという方は多いだろう。

まずは、これが本日の議論の「前提条件」だ。

ネット図表を解読する

ネットサイトの図表をエクセルに貼ると壊れた！

さて、実務上、よく出てくる悩みがあるとしたら、図表がうまく貼り付けられないというものだろう。

たとえば、インターネット上のサイトで発見したこんな図表（図表２）をエクセルに貼り付けようとしたら、一行に並んでしまった（図表３）、といった経験をされた方も多いだろう。

図表２　「こんな図表」

（【出所】Asian Infrastructure Investment Bank, “Members and Prospective Members of the Bank”）

図表３　こんな結果に（※シート名は「Sheet1」とする）

ここで、入力したシートのシート名は「Sheet1」とする。

このようなケースだと、単純に図表をコピーしてペーストする、というのは困難だ。

なぜこんな事態が生じるのか。

多くの場合、これは、ウェブサイトを記載するための言語「html」で図表を作成しているからである。

これをうまくエクセルのタテヨコ形式に変換するテクニックがないわけではないが、大変面倒であり、やはり、この形式をうまく活用せざるを得ない。

法則性があれば「MOD関数」で挽回可能

ただ、こうなってしまった場合でも、一定の法則性があれば、例の「MOD関数」を使ってデータを表形式に訂正することも可能である。

図表３をよく見てほしい。

元データは「国名」、「日付」、「出資金額」、「出資比率」、「議決権数」、「議決権比率」の６つが規則的に繰り返されている。ここで思い出しておきたいのが、「一定の法則で繰り返されるデータ」を分類するための「MOD関数」だ。

当ウェブサイトでも『MOD関数を使って「曜日」を特定する超絶テクニック』『MOD関数でエクセルの「列数と列番号」を対応させる』などで触れたとおり、MOD関数を使えば、一定間隔で繰り返し登場するデータをうまく分類することができる。

この事例だと６種類のデータが上から順番に繰り返されているため、A列に次のMOD関数を入力してみよう。

=MOD(ROW()-2,6)+1

この計算式は、「『現在の行数マイナス２』を６で割った余りに１を足した値を返す」という関数だが、これを入力すれば、「国名」が1、「日付」が2、という具合に、うまくすべてのデータを処理することができる（図表４）。

図表４　入力結果

ためしに、オートフィルタでA列を「1」、「2」などと指定してやれば、「国名リスト」などを簡単に作ることができる（図表５）。

図表５　国名リストの例（オートフィルタで「1」を指定した場合）

図表に復元する

図表の形に復元するための下準備

ただし、この状態だと、まだ不便である。いちいちオートフィルタを使って値を指定してやらないといけないからだ。

では、これを別シートに図表として転記するにはどうすればよいか。

さきほどの図表４について、今度はC2セルにこんな数式を入力してみよう（※ただし、この場合、C1セルに何か入力するとエラーとなるかもしれないので、C1セルには何も入力してはならない）。

=IF(A3=1,C2+1,C2)

次に、D2セルにはこんな数式を入力しよう。

=C2&”-“&A2

これで、準備完了だ（図表６）。

図表６　準備完了

D列はいわば「データの主キー」（固有の値）のようなもので、データの所在を特定する際のアドレスでもある（なお、さきほどの図表３の前提から、このシートの名称は「Sheet1」であることを思い出しておいてほしい）。

次に、できれば別シートに、こんなシートを準備する（図表７）。

図表７　転記用シート

「INDIRECT関数」と「MATCH関数」の合わせ技

次のステップに行こう。

ここでは「INDIRECT関数」と「MATCH関数」を使用する。

そして、B2セルにこんな計算式を入力する。

=INDIRECT(“Sheet1!B”&MATCH($A2&”-“&B$1,Sheet1!$D:$D,0))

この計算式、「INDIRECT」関数、すなわち自分自身でセルの値の絶対参照値を入力してやる、という仕組みだ。この計算式だと、最初のB2セルには、結果的に「Sheet1!B2」を指定してやる、という機能がある。

とくに、MATCH関数のなかにある「$A2&”-“&B$1」の部分は、「『1-1』という値に相当するものを『Sheet1』のD列から検索し、その値を返す」という意味があるが、結果的にB2に入るべきデータが格納されているセルの番地を参照しているというのである。

その結果が、次の図表８だ。

図表８　計算結果

いかがだろうか。

これで図表の修復が完了した。

INDIRECTとMATCHなどの初歩的な関数を組み合わせているだけなのに、図表を復元することができてしまった、というわけだ。

ぜひ、実務でもこのパワフルさを実感していただきたい。

エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。