PDFの表をExcelにコピーする Excel上のデータ整理の方法を知っておくと便利です

シェアする

  • このエントリーをはてなブックマークに追加

会計や財務などで、数字を扱うときにはExcelが便利です。ただ、データがPDFの場合は、Excelにペーストしてから使わなければいけません。セルごとに数字が入っていない場合のデータ整理の方法を考えます。

スポンサーリンク
自動サイズレスボンシブ

便利なのは有料版のアプリを使うこと

PDFをExcelに変換してくれるアプリがあります。
たとえば、

スクリーンショット 2015 02 04 10 30 34
(出典:アドビ公式サイト)

スクリーンショット 2015 02 04 10 30 48
(出典:アンテナハウスHP)

スクリーンショット 2015 02 04 10 31 15
(出典:Wondershare HP)


など。


いずれも、PDFファイルを取り込んでボタン一つで、Excelファイルに(他のMSOfficeアプリのファイルにも)変換してくれる優れものです。


時間の節約になりますし、細々コピー&ペーストを繰り返すのはミスの元にもなります。


ですので、ビジネスで定期的に一定数以上のPDFファイルを変換する場合には、素直に有料アプリを使うことをお勧めします。

スクリーンショット 2015 02 04 10 59 11
(出典:アドビ公式サイト)

スクリーンショット 2015 02 04 10 59 28
(出典:アンテナハウスHP)

スクリーンショット 2015 02 04 11 00 12

(出典:Wondershare HP)


Adobeの「Acrobat XI Pro」が毎月2,180 円、 
アンテナハウスの「瞬簡PDF 変換 8」が5,832 円、 
Wondershareの「PDFから簡単変換!」が3,390円、 
と値段にはかなりの差があります。


「Acrobat XI Pro」はファイルの変換以外に、PDFの編集機能がついているなど、機能面にも違いがありますので、用途に合わせて選ばれるのがいいでしょう。


このような仕事効率化に役立つアプリは、ある程度のボリュームがあれば、有料でもメリットがあります。

「たまに使う」程度なら、コピーしてデータを整理します

私はビジネス上、頻繁にファイルの変換が必要な訳ではないので、有料アプリは使っていません(将来的には導入するかもしれません)。


また、会社で貸与されているPCで自由にアプリを入れられない方も多いのではないでしょうか。


そのような場合は、コピー&ペーストしてデータを整理することになります。


例として、PDFファイルになった決算書をExcelファイルにコピーするケースを考えます。

PDFファイルをコピー

まずは、PDFファイルからExcelで加工したい箇所を範囲指定して、コピーします。

スクリーンショット 2015 02 04 11 17 13
(出典:トヨタ自動車WEBサイト IRライブラリ)


トヨタ自動車株式会社さんの有価証券報告書から、平成26年3月決算の単体の決算書の数字をお借りしました。


通常、上の画像のように、範囲指定して「CTR+C」でコピーして、Excelファイルを開いて、「CTR+V」でペーストしますが、

スクリーンショット 2015 02 04 11 23 47


このように、セルごとに数字が入っていない状態で、ペーストされることになります。


これを、元の決算書の様に並べて、セルごとに数字が入った状態に整理します。


※ 
ひょっとすると、ペースト自体ができないことがあるかもしれません。


これは、PDFファイルの作成時に、セキュリティコントロールをしているためで、この場合は、ロックを外して(PDFのロックを解除するアプリがあります)からコピーします。

「区切り位置」でデータをセルに収める

もう一度、ペーストしたデータを見てみましょう。

スクリーンショット 2015 02 04 11 37 32


元のデータが、

スクリーンショット 2015 02 04 11 38 22


この形ですから、横に広がってペーストされてしまったことがわかります。


しかも、

スクリーンショット 2015 02 04 11 39 53


上の画像では、A1セルをアクティブにしているのですが、この1つのセルに、全てのデータが入力されてしまっているのです。


そこで、次のような流れでデータを整理することを考えます。


1.1つのデータが、1つのセルに入力されるようにする

2.横に広がった表を縦にする

3.2つの会計年度のデータが、横に並ぶように並べ替える


この3つの作業です。

 
まずは、「1.1つのデータが、1つのセルに入力されるようにする」から。


「そんな都合の良い機能があるのか?」と思われるかもしれませんが、あります。


画像をもう一度見ていただくと、データとデータの間にスペースがあることが分かります。

スクリーンショット 2015 02 04 11 49 53


このように「スペース」や「カンマ(,)」でデータが区切られている場合、それを区切りにして、セルに分割してくれる機能があります。


”区切り位置”機能です。


データが入力されているセルを選択してから、「データ」タブにある「区切り位置」をクリック。

スクリーンショット 2015 02 04 11 53 59


「区切り位置指定ウィザード1/3」が開きます。
元のデータ形式の選択です。


スクリーンショット 2015-02-05 8.48.11


ここは、
「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選択して、「次へ」をクリックします。


「スペースによって右または左に揃えられた固定長フィールドのデータ」でも問題ありません。


こちらを選ぶと、セルの分割ポイントを自分で調整することができる、という違いがあります。


ただ、「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」の方が簡単なので、こちらで上手くいかなかったときに考えればいいでしょう。


「区切り位置指定ウィザード2/3」が開くので、フィールドの区切り文字を指定します。


スペースで区切りますので「スペース」をチェック。
(カンマで区切るときは、「カンマ」を選択して下さい)


プレビューで、上手く分割できていることを確認して、OKなら、「次へ」をクリック。

スクリーンショット 2015 02 04 12 17 56


「区切り位置指定ウィザード3/3」が開くので、特にデータの形式を指定する必要がなければ「G/標準」を選んで、「完了」をクリック。


これで、数値データは数値データとして保存されます。

スクリーンショット 2015 02 04 12 25 55


すると、

スクリーンショット 2015 02 04 12 29 54


セルごとにデータ入力することができました。

横に広がった表を縦にする

データをセルごとに収めることができましたので、次は、横に伸びたデータを縦にします。


「形式を選択して貼り付け」を使います。


横に伸びたデータを全て選択するように、範囲指定します。

スクリーンショット 2015 02 04 12 33 50


この時、マウスで指定するのは大変なのでショートカットを使ってみて下さい。


「shift+ctr+→」で連続するデータを一気に範囲指定することができます。


範囲指定できたら、「CTR+C」でコピーして、貼り付けたいセルを選んで、右クリック。
(ここでセルを移動するときにも「CTR+←」などショートカットを使うと便利です)


「形式を選択して貼り付け」を選びます。

スクリーンショット 2015 02 04 12 36 43


「形式を選択して貼り付け」画面が開くので、右下の「行列を入れ替える」を選択して、


スクリーンショット 2015-02-05 8.49.18


「OK」をクリック。

スクリーンショット 2015 02 04 12 41 09


縦に並べることができました。

会計年度のデータが、横に並ぶように整理する1

今度は縦に広がってしまったデータを整理します。


余計な空白のセルを削除します。


列全体を指定して、フィルターをかけます。

スクリーンショット 2015 02 04 12 50 32


フィルターのボタンが出てくるのでクリック。
選択画面から、「全て選択」のチェックを外して、

スクリーンショット 2015 02 04 12 57 07


空白セルを探し、チェックを入れて「OK」をクリック

スクリーンショット 2015 02 04 12 57 28


空白セルだけが選択されました。

スクリーンショット 2015 02 04 12 57 41


データが入力されている範囲を指定して(shift+control+↓)、セルを削除します。

スクリーンショット 2015 02 04 13 06 28


と警告が出るので、OKをクリック。


再度、フィルタのボタンをクリックして、「全て選択」にチェックを入れ、「OK」ボタンをクリック。

スクリーンショット 2015 02 04 13 07 25


すると、

スクリーンショット 2015 02 04 13 11 41


空白セルのない状態で、データが縦に並びました。


これと同じやりかたで、不要なデータである「※1」なども削除して、科目と数値データのみの形にします。

会計年度のデータが、横に並ぶように整理する2

科目と数値のデータが縦に並びました。
先に、科目を取り出します。


先ほど使った、フィルターを使います。


少し面倒ですが、フィルタのボタンをクリックして、科目にだけチェックを入れます。

スクリーンショット 2015 02 04 13 18 46


OKをクリックすると、

スクリーンショット 2015 02 04 13 21 26


科目だけが並びました。


ここで一休みせずに、科目だけをコピーしていきます。
データが入力されている範囲を選択します(「shift+control+↓」を使いましょう)。

スクリーンショット 2015 02 04 13 56 24


「CTR+G」のショートカットで「ジャンプ」を開きます。

スクリーンショット 2015 02 04 13 59 20


「セル選択」をクリック。

スクリーンショット 2015 02 04 13 59 11


「選択オプション」が開くので、「可視セル」にチェックを入れて「OK」をクリック。


これで、指定範囲のうち、見えているセルだけ、つまり、フィルターで隠れているセルを除いて、選択することができます。


「CTR+C」でコピーして、「CTR+V」でペーストします。

スクリーンショット 2015 02 04 14 03 56


フィルターで見えていた「科目」だけをコピーすることができました。

同じ要領で、今度は数字だけをコピーします。

スクリーンショット 2015 02 04 14 13 50


後は、2期分を科目ごとに並べるだけです。

会計年度のデータが、横に並ぶように整理する3

このあとどうするかですが、「一つ一つコピー&ペースト」は、面倒です。やめましょう。


一つは、マクロを使う方法があります。


簡単なマクロで可能なので、効率的です。


ただし、マクロを書けるようになるまでに少し時間がかかるので、ここでは関数を使います。


VLOOKUP関数です。


VLOOKUP関数は、


「①探したいデータ」と、


スクリーンショット 2015-02-04 19.00.01

「②調査する範囲」を指定すると、


スクリーンショット 2015-02-04 18.35.33


「①探したいデータ」を見つけるために、
「②調査する範囲」の一番左の列を、縦に調べていきます。

スクリーンショット 2015-02-04 19.45.08


「①探したいデータ」が、見つかったら、

スクリーンショット 2015-02-04 19.45.08 のコピー 2


「③「①探したいデータ」が見つかったとき、そのセルから右にいくつ進んだセルを表示するか」を指定しておくと、

スクリーンショット 2015-02-04 19.50.37


「①探したいデータ」が見つかったセルから、③で指定した数だけ、右に進んだセルを表示します。


スクリーンショット 2015-02-04 18.35.33 のコピー 2


その結果が、


スクリーンショット 2015-02-04 19.32.38


このようになります。


ここで、VLOOKUP関数の要素を確認しておくと、


①探したいデータ


②調査する範囲


③「①探したいデータ」が見つかったときに、そのセルからいくつ分右に進んだセルを表示するか


④検索の方法:0(※”0”は、ピッタリ一致するデータがあった時以外はエラーの判定をする、という方法です)

この4つの要素について、


=VLOOKUP(①探したいデータ,②調査する範囲,③「①探したいデータ」が見つかった時に、いくつ分右に進むか,④検索の方法)


この順に入力していきます。


実際の入力は次の通り。


スクリーンショット 2015-02-04 19.25.16

このVLOOKUP関数を使って、数値を、前期、当期の順に並べていきます。


数値の列をもう一度見てみましょう。

スクリーンショット 2015 02 04 14 20 33


この数値は、科目順に、「前期→当期」と規則的に並んでいます。


通し番号を1,2,3,…、とつけていくと


1 現金及び預金 前期 
2 現金及び預金 当期 
3 有価証券   前期 
4 有価証券   当期 
5 商品及び製品 前期 
    ・ 
    ・ 
    ・ 

と割り振られることになります。


この規則性に着目して、「科目」の左隣の列に奇数番号、偶数番号の通し番号をつけます。


奇数なら「1,3」と入力したら、あとは、2つのセルを指定して、セルの右下の角にカーソルを合わせます。


カーソルが十字になるので、そのまドラッグすると、

スクリーンショット 2015 02 04 14 33 08


連続する奇数を入力することができます。


同様に、偶数についても入力します。


スクリーンショット 2015 02 04 14 43 59


これで通し番号をつけることができました。
「流動資産」「固定資産」「投資その他の資産」などの、科目の分類を示す項目には数字は入らないので、空白セルになるように調整しています。


ここでVLOOKUP関数です。
次の3つの要素を入力します。


①探したいデータ 
 =通し番号


②調査する範囲 
=数値のデータが入力されているセル


③「①探したいデータ」が見つかったときに、 
  そのセルからいくつ分、右に進んだセルを表示するか

  =通し番号の隣の列を表示させるので「2」


④検索の方法:0


これを、Excel上のイメージに置き換えると、

スクリーンショット 2015-02-04 20.49.58


このようになります。


具体的なVLOOKUP関数の入力内容を、「前期の有価証券」を例に取って説明しておきます。


①探したいデータ


スクリーンショット 2015-02-04 21.16.07


「有価証券」の左隣に振られた、A10セルの通し番号、”5”が「①探したいデータ」です。

  
②調査する範囲


スクリーンショット 2015-02-04 21.24.59


数値が入力されている表の範囲です。
F7セルからG61セルまでが範囲になります。


「$F$7:$G$61」と入力しました。


③「①探したいデータ」が見つかったときに、 
 そのセルからいくつ分右に進んだセルを表示するか


スクリーンショット 2015-02-04 21.41.06


「数値」が入力されている表の、一番左の列(通し番号が入力されている列)を調べて、 
「①探したいデータ」である「通し番号」が見つかったら、隣の列にある”数値”を表示させたいので、


”2”と入力します。


実際の入力は、


スクリーンショット 2015-02-04 21.42.25


=VLOOKUP(A10,$F$7:$G$61,2,0)


このようになり、式が完成します(「④検索の方法」は”0”を入力)。


他のセルについても、同様に、VLOOKUP関数を入力していくと、

スクリーンショット 2015-02-04 21.54.06


全体をお見せすることはできませんが、前期、当期ともに正しく数値が入力できています。


整理したデータを数値で扱えるように整えます。


「#N/A」となっているセルを、フィルターを使って削除します。


スクリーンショット 2015-02-04 21.59.20


スクリーンショット 2015-02-04 21.59.51


セル内に「スペース」があると数値データとして扱ってくれないので、 
余計な「スペース」を取り除きます。


TRIM関数です。


不必要な「スペース」を取り除きたいセルを指定するだけでOKです。


=TRIM(D8)


のように入力します。


スクリーンショット 2015-02-04 22.08.03


スクリーンショット 2015-02-04 22.14.26


これで、余計な「スペース」がなくなりました。


さらに、セル内の桁区切りにカンマ”,”が使われていると、 
「文字データ」として処理されてしまい、数値データとして扱えません。


ですので、カンマ”,”を削除しておきます。


数値データの入力されているセルを、範囲指定します。


スクリーンショット 2015-02-04 22.25.19


「CTR+H」のショートカットキーを使い、「検索と置換」画面を開きます。

スクリーンショット 2015-02-04 22.25.35


「検索する文字列」にカンマ”,”を 
「置換後の文字列」を空欄にして、 
「全て置換」をクリック。


スクリーンショット 2015-02-04 22.29.42


スクリーンショット 2015-02-04 22.26.51


カンマ”,”を削除できました。


では、桁区切りは諦めるのかと言うと、そうではありません。
セルの書式設定で、桁区切りすればOKです。


スクリーンショット 2015-02-04 22.35.45

さらに体裁を整えると。


スクリーンショット 2015-02-04 22.44.28


これで見やすくなりました。


1行おきに色づけする方法については、こちらの記事(”Excelの表を見やすく ROW関数、MOD関数、条件付き書式で1行おきに色づけします”)で詳細を解説しています。

まとめ

PDFのデータをExcelにコピー&ペーストして扱うときは、「区切り位置」機能を使って、データをセルに分割しましょう。
並べ替えは、マクロを使うのもいいですし、フィルターやVLOOKUP関数などを使うと効率的にすすめることができます。
<おまけ>
明日は雪が降りそうですね。
今日は走っておきたいところ。

 

スポンサーリンク
自動サイズレスボンシブ
自動サイズレスボンシブ

シェアする

  • このエントリーをはてなブックマークに追加

フォローする