Windows 10 & Office 2016 Excel 2016

ピボットテーブルの作成・集計ができない(データベースにする準備)

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

Excelのテーブルピボットテーブルは、データベースとしての管理や分析が簡単にできるようになる、とても便利な機能です。

[挿入]タブの[テーブル]グループ

でも、既存の表をテーブルやピボットテーブルに変換しようとしても、うまくいかないということはよくあります。

その場合の表についての注意点を解説します。テーブルやピボットテーブルに変換できない場合は、既存の表を見直してみてください。

表をテーブルに変換してデータベース管理などの作業効率をアップ

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

続きを見る

ピボットテーブルの作成(ウィザードを使った作成方法を解説)

ピボットテーブルは、1つのデータをいろいろな視点から統計をとったり、分析したりする場合に使います。 ピボットテーブルを作成する場合の注意点で ...

続きを見る

Excel2007以降の場合は、以下の記事の方が分かりやすいと思います。

ピボットテーブルとピボットグラフを同時に作成

ピボットテーブルの作成に慣れたなら、グラフも同時に作成してみませんか? ここでは、Excel2016で解説します。 サンプルのデータは以下の ...

続きを見る

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

表に隣接するセルに文字列を入力しないようにします。

例えば、下のような表の場合、セル[G2]に日付が入力されています。

中央にタイトルも入力されています。

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

Excelの表

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

[挿入]タブの[テーブル]

[テーブルの作成]ダイアログボックスは、下のようにセル[A1]からセル[G11]をデータ範囲と認識します。

もちろん、ここで範囲の修正も可能です。

[テーブル]ボタンをクリックする前に範囲選択をしておく方法もあります。

ですが、やはり自動認識ができるようしておいたほうがいいと思います。

[テーブルの作成]ダイアログボックス

自動で範囲を認識させるには、下のように隣接するセルは空白にしておきます。

(ここでは、分かりやすくするためにセルを青色で塗りつぶしています。)

下の図では、表の上に一行挿入しています。

要するに、空白セルで囲まれた状態にします

こうしておくと、データ範囲が自動認識されるので効率的です。

空白行を挿入して空白行と空白列に囲まれた表

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

[テーブルの作成]ダイアログボックス

参考A列や1行目から入力している場合は、わざわざ空白列や空白行を作る必要はありません。

その場合、右端の列と、下端の行が空白になっていればOKです。

これは、フィルターモードをオンにして、フィルターボタンを列見出しに表示する場合も同じです。

フィルターボタンが先頭行のセルにないのでオートフィルターが使えない

Excelでは、[データ]タブの[フィルター]を使ってデータの並べ替えや絞り込みができます。 いろいろな抽出条件を指定して、データの抽出がで ...

続きを見る

セルの結合をしない

よくあるのが、下のような表です。

表としては見やすいかもしれませんが、これではデータベースとはなりません。

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

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

列の見出しが空白でもNGです。

テーブルであれば自動的に[列1]などが付けられますが、ピボットテーブルではメッセージが表示されて作成できません。

セル結合された表

参考どうしても結合セルのままにしておきたい表の場合は、以下の方法で解決できます。

セルを結合したままフィルターモードでデータを抽出するには

Excelの結合セルは、やっかい扱いされることが多いですが、その1つがフィルターを設定しても正常に抽出されないということがあります。 セルの ...

続きを見る

セルの先頭に余白を入力しない

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

下の場合、[販売店種別]がスペースを使って中央寄せになっているセルと、[中央揃え]で設定されたセルがあります。

見た目だけでは、気づきませんよね。

セルの先頭に空白スペースがある表

[販売店種別]のフィルターボタンをクリックすると、下のように別のものと認識されてしまいます。

空白スペースは置換機能などを使って削除します。

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

置換機能を使った空白スペースの削除方法については、以下の記事で解説しています。

フィルターと置換機能を使って不揃いのデータを整える

Excelのフィルター機能は、データのチェックをして整えるときにも便利です。 ピボットテーブルの元表には、統一されたデータがなければ、分析も ...

続きを見る

参考文字列前後の空白スペースは本当に曲者です。

ピボットテーブルや、AccessでExcelのデータをインポートする時も、文字列末尾の空白スペースがあると別物と認識されます。

重複の削除をする場合も文字列末尾の空白スペースには注意が必要です。

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

よくあるのが日付ですね。

日付に見えても文字列ということがあります。

また、日付と文字列が混在している場合もあります。

下の場合、セル[A5]からセル[A8](2016/11/1~2016/11/4)が日付、セル[A9]からセル[A12](2016/11/5~2016/11/8)が文字列です。

日付フィールド

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

詳細なフィルターも[テキストフィルター]になってしまいます。

日付は、日付フィルターを使用したほうが便利です。

以下の記事を参照してください。

フィルターモードの詳細な抽出条件はデータの種類で異なる

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

続きを見る

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

もし、日付が文字列になっている場合は、[データ]タブの[区切り位置]を使って、まとめて日付に変更することもできます。

以下の記事で解説しています。

ピボットテーブルで日付のグループ化ができない(文字列を日付に変更)

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

続きを見る

数値も文字列にならないようにします。チェックする方法を以下の記事で解説しています。

セル範囲の中から数値に見える文字列のセルを見つけるには

Excelのセルに数値が入力されていると思っても、実際は文字列だったということがあります。 見た目は数値に見えても表示形式は文字列になってい ...

続きを見る

半角と全角を混在しない

また、日本語は本当に面倒なのですが、半角と全角が見分けにくいことがあります。

アルファベットやカタカナは全角で統一するか、半角で統一するか決めておきます。

表記ルールは入力前に決定しておくことが大事です。

下は、[製品名]に半角と全角が混在しています。

半角と全角が混在した表

[製品名]のフィルターボタンをクリックすると下のようになってしまいます。

混在していると、並べ替えや抽出が思い通りに行かなくなります。

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

関数で半角や全角を統一する方法もあります。

カタカナは全角で数字は半角にしたい

Excelでカタカナは全角で、数字は半角にしたいという場合があります。 たとえば、住所でマンション名のカタカナは全角のままで、番地や部屋番号 ...

続きを見る

入力規則を使ってIMEの入力モードを自動切り替えるなどの工夫も必要かもしれません。

半角・ひらがな入力などを自動で切り替えるには[データの入力規則]で

Excelでは、[入力規則]を使って入力時の日本語入力をオンにしたり、オフにしたりすることができます。 この設定を行っておくと、入力時にIM ...

続きを見る

Power Queryエディターでも表記ゆれを統一することができます。

Power Queryで区切り記号で区切られたデータを別々のセルへ表示

1つのセルに複数のデータが入力されている場合は、それぞれのデータを分割して表示しないとデータベースにはなりません。 Power Queryの ...

続きを見る

重複データの削除

重複するデータがあると、正しい結果を得ることができません。

重複データのチェックは、条件付き書式の[重複する値]を使うと便利です。

また、[重複の削除]という機能もあります。Excel2007以降の機能です。

重複する値の削除(条件付き書式でチェックしてデータを削除)

Excel2007以降には、[重複の削除]という機能があります。 [重複の削除]を実行すると1番目に登場した値が残り、残りのデータはすべて削 ...

続きを見る

[重複の削除]は1番目の値が残るがデータの空白スペースには注意

Excel2007以降には、[データ]タブの[データツール]グループに[重複の削除]というコマンドがあります。 ポップヒントには、以下のよう ...

続きを見る

重複の削除と重複する値の確認

膨大なデータの場合は、COUNTIF関数を使ったチェックがおすすめです。

重複のチェック(COUNTIF関数を使って)

=IF(COUNTIF($A$1:$A$100,$A1)>1,"重複","")

COUNTIF関数を使って重複データをチェック

COUNTIF関数で何回目の出現かをチェックしてリストを作成する方法もあります。

重複を削除してリストを作成(COUNTIF関数)

参考Microsoft 365のExcelとExcel 2021では、UNIQUE関数を使用すると便利です。

=UNIQUE(A2:A18)

UNIQUE関数で重複のないリストを作成

以下の記事では、ピボットテーブルを使用して一意のリストを作成する方法を紹介しています。

ピボットテーブルで重複データをチェックして一意のリストを作成

Excelのリストで重複データが混在している場合、重複データをチェックして一意のリストを作成したい場合があります。 重複データをチェックする ...

続きを見る

また、入力規則で重複データを規制する方法もあります。

重複データが入力されたらメッセージを表示して規制するには

Excelで1つの列に重複した値を入力できないように規制する方法を紹介します。 Excel2013で解説しますが、他のバージョンも同じ方法で ...

続きを見る

条件付き書式の重複のチェックは便利ですが、複数列で共に重複しているデータをチェックするにはCOUNTIFS関数を使用します。

重複しない一意の値を求める方法と複数列で重複をチェックする方法

Excelで2つの列にデータが入力されていて、そのデータの差分を求めたい場合がありますね。 A列には分析用データとして別表に貼り付け済みのI ...

続きを見る

Power Queryエディターのグループ化でリストを作成することもできます。

Power Queryエディターで複数のグループ化の結果を表示するには

Power Queryには、[グループ化]という機能があります。 COUNTIF関数やSUMIF関数、またはピボットテーブルで求める結果と同 ...

続きを見る

[フィルターオプションの設定]で重複しないリストを作成することもできます。

複数の条件に合うデータを抽出する方法(別シートへの書き出しも可能)

Excelのデータ抽出の方法はいろいろありますが、ここでは[データ]タブの[並べ替えとフィルター]グループにある[詳細設定]を使って、複数の ...

続きを見る

データのクレンジング

とにもかくにも、ピボットテーブルを作成するうえで大切なのは、データのクレンジングです。

ピボットテーブルができないという場合、ピボットテーブルを作成しようとしている、元のデータに原因がある場合がほとんどです。

Excelのデータクレンジングは、本当にやっかいです。

以下の本は、実務で出会う困ったなというケースがほぼ網羅されていると思います。おすすめです。

1つのワークシートに1つの表

1つのワークシートには1つの表を作成するようにしましょう。

1つのワークシートに複数の表があると、1つの表の抽出結果が他の表に影響する場合があります。

データを綺麗に整理することをデータクレンジングといいますが、データ分析の前には必須の作業となります。

下記ページは、AccessにExcelのデータをインポートする時の注意点として列記したものですが、参考になると思います。

データクレンジング時に必要な操作や機能を解説しています。

データクレンジングに必要な操作や機能

1行1レコードの表に加工

以下のような表は、加工して1行に1レコード(1行に1件のデータ)の表になるようにします。

先頭行に複数の同じ項目があるような表

先頭行に同じ項目名のフィールドが複数表示されている表を見ることがあります。こういう表では分析はできません。

同じ項目名のフィールドが複数ある表

1行に1レコード(1行に1件の情報)となるようにします。

修正方法については、以下の記事で解説しています。

1行に複数の同じ項目名がある表を1行1レコードの表に変更するには

Excelの表の作り方は様々です。リストとなる表は、1行に1レコード(1行に1個の情報)となっていなければいけません。 横に長い表を4列ずつ ...

続きを見る

Power Query(パワークエリ)が使用できる環境であれば、もっと簡単にテーブルに変換できます。

先頭行に複数の同じ項目名がある表をPower Queryでテーブルに変換

Excel2016から標準で装備されているPower Query(パワークエリ)を使用すると、効率よくデータを加工できます。 Excelの表 ...

続きを見る

マトリックス表(クロス集計表)

マトリックス表(クロス集計)のようなものも、Power Queryエディターでテーブルに整形できます。

マトリックス表(クロス集計)

Power Queryでデータベースに整形(マトリックス表をテーブルに)

Power Query(パワークエリ)は、Excel2016とExcel2019には、標準でインストールされています。Excel2010とE ...

続きを見る

Power Queryエディターを使用しない方法も解説しています。

マトリックス表(クロス集計)をピボットテーブルウィザードでテーブルに変換

マトリックス表(クロス集計)は、ピボットテーブルウィザードの[複数のワークシート範囲]を選択して、リスト形式に変換することができます。 Ex ...

続きを見る

1列の複数行で1件のレコードになっているような表

1列の複数行で1レコードになっているような表もPower Queryエディターで複数列のテーブルに整形できます。

1列の複数行で1レコードになっている表

1列の複数行で1レコードになっている表をPower Queryで複数列のテーブルに変換

1列の複数行に1レコードのデータが並んでいる表も、Power Query(パワークエリ)エディターを使って、複数列のテーブルに整形することが ...

続きを見る

Power Queryエディターを使用しない方法も解説しています。

縦1列に入力されたデータを横に項目を並べた表(1行1レコード)に変更

A列の複数行に1レコードが並んでいる表を、横に項目を並べた表に変更する方法を紹介します。ここでは、数式を使った方法を紹介します。Excel2 ...

続きを見る

検索

ほかの記事も検索してみませんか?

Access / Excel / PowerPoint / Word

関連記事30件

Profile

-Windows 10 & Office 2016 Excel 2016

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

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