データの操作
- 既存のピボットテーブルで使用するデータの範囲を変更したい
- 20031225を2003年12月25日に変換するには?
- ピボットテーブルのデータのグループ化することができない。どうすれば?
- 一つのセルに256文字以上入力できない! Accessからインポートしても途中で切れてしまうのはなぜ?
- ピボットテーブルをコピーして値を編集したい
既存のピボットテーブルで使用するデータの範囲を変更したい
すでに出来上がったピボットテーブルにおいて、時間とともに元データが増えた場合、作り直さなくてもデータの範囲を変更することができます。
- ピボットテーブルツールバーの(ピボットテーブルウィザード)ボタンをクリックします。
または、メニューバーから実行する場合は[データ]→[ピボットテーブルとピボットグラフ レポート]をクリックします。 - ピボットテーブル/ピボットグラフウィザード 3/3が表示されます。
[< 戻る]ボタンをクリックしてください。 - すると、使用するデータの範囲の指定画面に戻ります。ここで再設定を行い、[完了]ボタンをクリックします。
20031225を2003年12月25日に変換するには?
他のアプリケーションソフトからのデータの場合、20031225は2003万1225という数値としてみなされます。このデータを一つ一つ間にスラッシュ(/)記号を入れるのにも気の遠くなるような作業になりますし、どんなに力技でセルの書式設定で変更しても、所詮20031225は2003万1225なのです(笑)
日付はシリアル値として指定されます。1900年1月1日を1とみなさますので、2003年12月25日の実際のシリアル値は37980です。
実際、20031225は一体いつになるか、というと、1年を365.2422日として想定すると、西暦52943年になります。つまり、5万年以上も未来の話になります。Excelでは、日付は9999年12月31日までしか扱うことができませんから、20031225をセルの書式設定で日付として換算しようとしても######表示になります。
では、どのようにすればいいのでしょうか?
A | B | |
---|---|---|
1 | 20031225 | |
2 | 031225 | |
3 | 31225 |
20031225のうち、最初の4桁は年、次の2桁が月、最後の2桁が日として認識させてあげればいいのですから、
文字列操作関数のLEFT関数、MID関数、RIGHT関数、及び日付関数のDATE関数を組み合わせて使います。
DATE関数 | =DATE(年,月,日) | 指定した日付を表すシリアル値を返します |
LEFT関数 | =LEFT(文字列,文字数) | 文字列の先頭から指定した数の文字を返します。1文字を1として処理します。 |
MID関数 | =MID(文字列,開始位置,文字数) | 文字列の指定した位置から、指定された数の文字を返します。 |
RIGHT関数 | =RIGHT(文字列.文字数) | 文字列の末尾から指定した数の文字を返します。1文字を1として処理します。 |
B1にA1の日付変換するための結果を出したい場合、年はLEFT関数、月はMID関数、日はRIGHT関数で導きます。
- 年→A1の最初の4文字を抜き出すので、 LEFT(A1,4)
- 月→A1の5文字目から2文字を抜き出すので MID(A1,5,2)
- 日→A1の最後の2文字を抜き出すので RIGHT(A1,2)
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
A2のように、031225を文字列として認識されている場合は年に2000を足してください。
=DATE(LEFT(A2,2)+2000,MID(A2,3,2),RIGHT(A2,2))
A3のように、031225が数値として31225と認識されてしまった場合は
=DATE(LEFT(A3,1)+2000,MID(A3,2,2),RIGHT(A3,2))
ピボットテーブルのデータをグループ化することができない。どうすれば?
AccessとかSQLサーバーからExcelにインポートしたデータは結構厄介です・・・。
「OLAP データベースの元データから作成したピボットテーブル レポートおよびピボットグラフ レポートでは、データをグループ化することはできません。」とExcelのヘルプに記述されているとおり、うまく加工することができないのです。
「数値」なのに「文字列」として認識され、セルの書式設定でうまく数値として認識できないことが多いです。
20031225というデータはExcelにインポートすると「20031225」という文字列として認識されます。この場合はすべてのデータに1で乗算してあげなきゃいけないんですね。
思い通りにピボットテーブルを加工したい場合は、以下の操作を行ってください。
- すべてのデータを1で乗算しておく
→「文字列でインポートしたデータを書式設定で標準や数値に変えてもオートSUMで合計してくれない」を参考にしてください。 - 日付データを日付として認識させておく
→「20031225を2003年12月25日に変換するには?」を参考にしてください。 - ピボットテーブルを作成する
→「ピボットテーブルを作ろう!」を参考にしてください。
一番手っ取り早い方法として、Excel形式でもCSV形式でもなく、テキストファイル(TXT)形式でエクスポートする方がいいです。
余計な情報が入らないので、Excelにインポートして関数を使っても問題なく思い通りにデータの加工を行うことができます。
一つのセルに256文字以上入力できない! Accessからインポートしても途中で切れてしまうのはなぜ?
一つのセルに256文字以上入力しようとしても、どうしてもできません。Accessからデータをインポートしても、メモ型フィールドでの長文データが最初の255文字までで途切れてしまいます。これはExcelの仕様で、列の幅は255文字までしかサポートされていないからですね。
詳しくはExcelの仕様についてをご覧ください。
ピボットテーブルをコピーして値を編集したい
生成されたピボットテーブルだけをコピーして別の場所に貼り付けると集計元データとのリンクされたままの状態になります。
シートごとコピーして(つまり「全セル選択」ボタンでコピー)、別ファイルまたは別シートに貼り付けをすると、[形式を選択して貼り付け]画面が表示され、[値]を貼り付けることにより、リンクは解除され、データを編集することができます。
- Microsoft Excel FAQ
- データの操作
- 並べ替えをしようとすると、「この操作には、同じサイズの結合セルが必要です。」というメッセージがでてしまう。
- 文字列でインポートしたデータを書式設定で標準や数値に変えてもオートSUMで合計してくれない
- 複数のセルに同じデータを一度に入力するには?
- 同じ読みの漢字とひらがなやカタカナが含まれるデータの並べ替えができない
- アウトラインが設定されている場合データの並べ替えができない
- 既存のピボットテーブルで使用するデータの範囲を変更したい
- 20031225を2003年12月25日に変換するには?
- ピボットテーブルのデータのグループ化することができない。どうすれば?
- 一つのセルに256文字以上入力できない! Accessからインポートしても途中で切れてしまうのはなぜ?
- ピボットテーブルをコピーして値を編集したい
- データの操作
コメント
※技術的な質問はMicrosoftコミュニティで聞いてください!