ウェブサイトの図表をエクセルにうまく貼り付ける方法

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

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

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

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

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

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

こんな具合だ(図表1)。

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

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

  • 行(row)…数値
  • 列(column)…アルファベット

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

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

ネット図表を解読する

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

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

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

図表2 「こんな図表」

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

図表3 こんな結果に(※シート名は「Sheet1」とする)

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

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

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

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

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

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

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

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

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

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

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

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

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

図表4 入力結果

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

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

図表に復元する

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

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

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

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

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

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

=C2&”-“&A2

これで、準備完了だ(図表6)。

図表6 準備完了

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

次に、できれば別シートに、こんなシートを準備する(図表7)。

図表7 転記用シート

「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に入るべきデータが格納されているセルの番地を参照しているというのである。

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

図表8 計算結果

いかがだろうか。

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

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

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

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

読者コメント一覧

  1. ななっしー より:

    おのれAIIBめ、「table」を使わないとは(レスポンシブの一種なのか?)。
    でも、Webページを.mhtでローカルに保存してからワードで開いてレイアウトが崩れてなければコピペできるかも。

    テキストフィルタでも1周で済ますなら行番号を6で割る必要がありますねー
    (2周なら1周目で「”Number of Votes”の次の行の末尾に改行追加」とかでレコード区切り明示)。

※【重要】ご注意:他サイトの文章の転載は可能な限りお控えください。

やむを得ず他サイトの文章を引用する場合、引用率(引用する文字数の元サイトの文字数に対する比率)は10%以下にしてください。著作権侵害コメントにつきましては、発見次第、削除します。

※現在、ロシア語、中国語、韓国語などによる、ウィルスサイト・ポルノサイトなどへの誘導目的のスパムコメントが激増しており、その関係で、通常の読者コメントも誤って「スパム」に判定される事例が増えています。そのようなコメントは後刻、極力手作業で修正しています。コメントを入力後、反映されない場合でも、少し待ち頂けると幸いです。

※【重要】ご注意:人格攻撃等に関するコメントは禁止です。

当ウェブサイトのポリシーのページなどに再三示していますが、基本的に第三者の人格等を攻撃するようなコメントについては書き込まないでください。今後は警告なしに削除します。なお、コメントにつきましては、これらの注意点を踏まえたうえで、ご自由になさってください。また、コメントにあたって、メールアドレス、URLの入力は必要ありません(メールアドレスは開示されません)。ブログ、ツイッターアカウントなどをお持ちの方は、該当するURLを記載するなど、宣伝にもご活用ください。なお、原則として頂いたコメントには個別に返信いたしませんが、必ず目を通しておりますし、本文で取り上げることもございます。是非、お気軽なコメントを賜りますと幸いです。

コメントを残す

メールアドレスが公開されることはありません。