リンクユニット

Windows10 & Office2016 Excel2016

テーブルやピボットテーブルに変換する時の注意点

投稿日:2017年1月9日 更新日:

Excelのテーブルピボットテーブルは、データベースとしての管理が簡単にできるようになる、とても便利な機能です。でも、既存の表をテーブルやピボットテーブルに変換しようとしても、うまくいかないということはよくあります。その場合の表についての注意点を解説します。

テーブルの便利機能(テーブルに変換して効率アップ)

Excelのテーブルはとても便利なものです。通常の表をテーブルに変換することによって、データベースとしての機能が自動的に設定され、管理や分析 ...

続きを見る

ピボットテーブルの作成

一つのデータをいろいろな視点から統計をとったり、分析したりする場合に使います。 元になる表は、領収書の項目をただ横並びに入力したようなものが ...

続きを見る

隣接する行や列は空白セルにします

表の隣接するセルに文字列を入力しないようにします。例えば、下のような表の場合、セルG2に日付が入力されています。中央にタイトルも入力されています。

この表内のセルをアクティブにして、テーブルを作成しようとすると、自動的に範囲を認識してくれません
excel表

表内のセルをクリックして、[挿入]タブの[テーブル]グループにある[テーブル]をクリックすると、
テーブルボタン

[テーブルの作成]ダイアログボックスは、下のようにセルA1からセルG11をデータ範囲と認識します。もちろん、ここで範囲を修正することも可能です。[テーブル]ボタンをクリックする前に範囲選択をしておく方法もあります。ですが、やはり自動認識ができるようしておいたほうがいいと思います。
テーブルの作成ダイアログボックス

自動で範囲を認識させるには、下のように隣接するセルは空白にしておきます。(ここでは、分かりやすくするためにセルを青色で塗りつぶしています。)下の図では、表の上に一行挿入しています。

要するに、空白セルで囲まれた状態にします。こうしておくと、データ範囲が自動認識されるので効率的です。
空白行の挿入

下のように[テーブルの作成]ダイアログボックスは、テーブルに変換する範囲を自動的に認識するようになります。
テーブルの作成ダイアログボックス

セルの結合をしません

よくあるのが、下のような表です。表としては見やすいかもしれませんが、これではデータベースとはなりません。

注意必ず1行に1件分のデータを入力します。

列見出しも結合してあったり、2行にまたがっていることがありますが、表の1行目には、列見出しを1つのセルに1つ入力します。
結合された表

セルの先頭に余白を入力しません

見ただけでは分かりづらいのですが、文字列の先頭に空白スペースが入力されている場合があります。

下の場合、[販売店種別]がスペースを使って中央寄せになっているものと、[中央揃え]の機能を使って設定してあるものがあります。見た目だけでは、気づきませんよね。
セルの先頭に空白スペースがある表

[販売店種別]のフィルターボタンをクリックすると、下のように別のものと認識されてしまいます。空白スペースは置換機能などを使って削除します。
フィルターボタンをクリックした状態

参考文字列の前後の空白スペースは本当に曲者です。AccessでExcelのデータをインポートした際には、文字列の後ろの空白スペースも別物と認識されます。

1つのフィールドには同じ種類のデータを入力します

よくあるのが日付ですね。日付に見えても文字列ということがあります。また、日付と文字列が混在している場合もあります。下の場合、セルA1からA8(2016/11/1~2016/11/4)が日付、セルA9からセルA12(2016/11/5~2016/11/8)が文字列です。
日付フィールド

この場合、[日付]のフィルターボタンをクリックすると、下のようになってしまいます。詳細なフィルターも[テキストフィルター]になってしまいます。

日付は、日付フィルターを使用したほうが便利です。フィルターの抽出条件はデータの種類で変わるを参照してください。

フィルターの抽出条件はデータの種類で変わる

Excelのフィルター機能を使用すると、条件に合うデータを簡単に抽出することができます。表をフィルターモードにするには、表内のセルをアクティ ...

続きを見る

フィルターボタンをクリックした状態

もし、日付が文字列になっている場合は、[データ]タブの[区切り位置]を使って一気に日付に変更することができます。
ピボットテーブルで日付のグループ化ができないを参照してください。

ピボットテーブルで日付のグループ化ができない

Excelでピボットテーブルを作成して、日付フィールドで[月]ごとのグループ化をしようとしてもできないことがあります。これは、見た目は日付に ...

続きを見る

数値も文字列とならないようにします。

半角と全角が混在しないようにします

また、日本語は本当に面倒なのですが、半角と全角が見分けにくいことがあります。アルファベットやカタカナは全角で統一するか、半角で統一するか決めておきます。表記ルールは入力前に決定しておくことが大事です。
下は、[製品名]に半角と全角が混在しています。
半角と全角が混在した表

[製品名]のフィルターボタンをクリックすると下のようになってしまいます。混在していると、並べ替えや抽出が思い通りに行かなくなります。
フィルターボタンをクリックした状態

1つのワークシートに1つの表を作成します

1つのワークシートには1つの表を作成するようにしましょう。1つのワークシートに複数の表があると、1つの表の抽出結果が他の表に影響することがあります。

データを綺麗にすることをデータクレンジングといいますが、データ分析の前には必須の作業となります。
下記ページは、AccessにExcelのデータをインポートする時の注意点として列記したものですが、参考になると思います。データクレンジング時に必要な操作や機能を解説しています。

データクレンジングに必要な操作や機能 : 初心者のためのOffice講座-SupportingBlog1

ピボットテーブルを学習するなら、お勧めのテキストです。

おすすめ記事と広告

推奨管理人一押しのテキストです。業務改善、時短のための一冊です。関数が分かるとExcelが楽しくなります。

記事を更新しました

2つの文書を比較して違いを確認

Wordで似たような文書があった時、どこが違うのかを確認したいときがありますね。目視で分かる場合もありますが、見落とさないように正確に違いを ...

AccessのテーブルやクエリのデータをExcelに取り込む

Accessで作成されたテーブルやクエリのデータをExcelのワークシートに取り込む方法です。 Accessのデータを接続しないで取り込む方 ...

1ページで収めたいのに2ページ目にはみ出したときの対処方法

Wordで文書を作成していると、1ページに収めるつもりだったのに、2ページ目まではみ出してしまったということはよくあると思います。そういう場 ...

アウトラインの自動作成とグループ化

データをグループ化すると、アウトラインを作成することができます。アウトラインが設定された表は、目的にあったレベルの詳細を表示したり、または部 ...

Wordの表に連番を自動入力

Wordで表を作成すると、1つの列に連番を入力したい時があります。セルに1つ1つ入力するのは面倒ですね。一気に入力する方法を紹介します。 下 ...

-Windows10 & Office2016 Excel2016

Copyright © 2001-2018 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2018 AllRights Reserved Powered by AFFINGER4.