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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

セルの結合をしない

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

半角と全角を混在しない

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

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

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

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

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

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

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

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

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

重複データの削除

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

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

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

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

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

重複のチェック（COUNTIF関数を使って）

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

重複を削除してリストを作成（COUNTIF関数）

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

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

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

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

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

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

データのクレンジング

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

マトリックス表（クロス集計表）

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

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

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

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

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