- ベストアンサー
ピボットテーブルのフィールドリストについて
- エクセルを使用している経理のソフトでピボットテーブルを作成しています。しかし、ピボットテーブルのフィールドリストには存在しない項目があります。具体的には、「日付」のほかに「集計月」という名前のフィールドです。この「集計月」のリストを作成する方法を知りたいです。
- 現在、エクセルを使用している経理のソフトでピボットテーブルを作成していますが、ピボットテーブルのフィールドリストに「集計月」という名前のフィールドが表示されます。しかし、仕訳帳シートには日付の項目しか存在しないため、この「集計月」のリストを作成する方法がわかりません。
- エクセル２０１０を使用している経理のソフトでピボットテーブルを作成していますが、ピボットテーブルのフィールドリストには「日付」という項目のほかに「集計月」という名前のフィールドが存在します。この「集計月」のリストを作成する方法を教えてください。
- みんなの回答 （4）
- 専門家の回答
質問者が選んだベストアンサー
ステップ２でやろうとしたのは，名前定義に仕込んだOFFSET関数に，実際の表の正しい範囲（今回明らかになったように，Ｂ７から始まって実際にデータが記入された行数×５列）を計算させ，それをピボットテーブルの元データとして食べさせようとしたわけです。 で，出来ないのは，実際の表の正しい範囲を計算できていないからです。 どこを間違えたかというと，恐らく COUNTA(仕訳帳!$B:$B) の部分です。 今，仮にＢ７に「日付」と記入してあって，Ｂ１６まで日付が記入してあるとしましょうか。 すると表はＢ７を含めて１０行から成っている勘定です。 Ｂ７から数えて１１行目以下は「日付が記入されていない」ので，そこが範囲に入ってしまっていると，すでに理解していただいたように日付がグループ化できなくなります。 実際に仕訳帳シートで，どこかのセルに =COUNTA(仕訳帳!$B:$B) と数式を記入してみて，きっちりしっかり「１０」行という答えが出るか確認してください。 １２とか１３とか，いきなり２１００個とか，そんな数字が計算されたらやっぱりここが間違っていたというワケです。何故でしょう。あなたが気にしていなかった，たとえば「Ｂ１セルやＢ２セルに何かが書いてある」からかもしれません。あるいは実はよく見てみるとＢ１７セル以下にも何かが記入されていて，それがカウントされているのかもしれません。表は２１００行までありますが，それよりもっと下に，実は何かが書かれているのかも？しれません。 仕訳帳シートの現物を確認できるのはアナタだけなのですから，アナタが原因を究明してください。ワタシはそれを持っていませんから，どうしたらいいか正しい答えはアドバイスできません。 そして今の仕訳帳シートのまま正しく「１０」行と計算できるよう，そしてその１０行をつかってOFFSET関数が正しく実際の表範囲を計算できるよう，名前定義の数式を直さなければいけません。 もしかすると =OFFSET(仕訳帳!$B$7,0,NOW()*0,COUNTA(仕訳帳!$B$7:$B$2100),5) が良いかもしれません。判りますか？Ｂ１やＢ２を計算範囲から外して，余計なセルを数えないようにすれば「１０行」と計算できた場合の式です。 もしかすると =OFFSET(仕訳帳!$B$7,0,NOW()*0,COUNT(仕訳帳!$B$7:$B$2100)+1,5) が良いかもしれません。 それから。 ＞発生させた「月 ▼」を「月集計▼」とそのセル自体書き換える 文字通りそうすることです。
その他の回答 (3)
- keithin
- ベストアンサー率66% (5278/7941)
あなたの間違い： ＞元シート（同じ「仕訳帳シート」）の全てを範囲指定（まだ未定の行も） ： ＞空欄があるので日付のグループ化はできません ご説明の繰り返し： ＞日付のグループ化は， ＞日付列（セル範囲）に「空白のセル」…が含まれていると，出来ません。 繰り返すと，空白があったらいけないのは日付の列です。 他の明細などの列に空欄があっても，日付のグループ化には関係ありません。 やるべきこと： １．仕訳帳シートで名前の定義を作成すること ２．定義した名前を使ってピボットテーブルレポートを作成すること ３．日付をグループ化し，発生させた「月 ▼」を「月集計▼」とそのセル自体書き換えること
補足
keithinさま たびたびご回答いただき、感謝いたします。 ありがとうございます。 ＞他の明細などの列に空欄があっても，日付のグループ化には関係ありません。 なるほど、わかりました。 テスト表を作ってやってみたらできました。 やるべきこと：を順番にやってみました。 実際の表でやると３．のグループ化で足踏み状態です（汗） やってみた状況を書き出してみますので、恐れ入りますが、どこがおかしいのか教えていただけませんでしょうか？ ★印は、ステップ２の練習と違うところです。 仕訳帳シート（元のシート） ※エクセル2010 B C D E F 7 日付 No. 科目 金額 詳細１ 8 4/1 1 仕入 300 a 9 4/2 2 売上 1,000 空欄 10 4/5 3 通信費 300 c 11 5/8 4 仕入 100 d 12 5/9 5 仕入 500 a 13 5/10 6 売上 2,000 b 14 5/11 7 通信費 400 c 15 6/3 8 仕入 800 d 16 6/4 9 売上 5,000 空欄 ★日付項目はB7のセルになっています。 ★項目は5列あります。 １．名前の定義 名前 リスト 範囲 仕訳帳 参照範囲 =OFFSET(Sheet1!$A$1,0,NOW()*0,COUNTA(Sheet1!$A:$A),3) を ★=OFFSET(仕訳帳!$B$7,0,NOW()*0,COUNTA(仕訳帳!$B:$B),5) にしました。 ２．ピボットテーブル作成 仕訳帳シートを範囲指定せずに、仕訳帳シートを表示したまま 挿入→ピボットテーブル→テーブル／範囲(T)：を=リストに書き換える。→ok 日付、科目、金額、詳細１でピボットテーブルを作成しました。 ３．日付をグループ化 グループ化ができません。 何が原因でできないのか、ほかに思い当たるところを書き出してみました。 ★仕訳帳シート（元のシート）は、元々、No.だけ1～2100まで入力してあります。 そして、その行の分だけ、罫線が引いてある状態です。 現在入力してあるのは、465までです（もちろんこのあと2100近くまで入力していくことになります） とりあえず、2100まで適当に日付だけ入力して、更新してからグループ化をしましたが、 やはり「選択対象をグループ化することはできません」と表示されてしまいます。 ★プログラムされているソフトなので、いろんなところで数式が入っています。 「仕訳帳シート」に入力すると、いろんなシートが連動して反映されています。 この情報が関係あるのかわからないのですが、ほかに思いつくところがありません… ちなみに、上の表だけ（B7～F16）別シートに張り付けてやってみると、グループ化ができるんです。 やはり、仕訳帳（元のシート）ができない表になっているのでしょうか？ グループ化ができてからのお話ですが・・・ ＞発生させた「月 ▼」を「月集計▼」とそのセル自体書き換えること 「月集計▼」はどうやって、そのセル自体書き換えるの“そのセル自体”とはどのセルのことでしょう・・・？（汗） 理解できず申し訳ありません＞＜； もう少しでできそうな気がするんですが、 どうぞ、よろしくお願いいたします。
- keithin
- ベストアンサー率66% (5278/7941)
＞できるのでしょうか アタリマエのことを聞かないでください。もちろん出来る方法をお話ししています。 でもあなたが何が判ってないのか，どこが出来ないのか，肝心のそういった困っているところがずっとあいまいなままなので，もしかするといつまで経っても出来ないかも？しれません。 どこまで出来たのか，どこで止まっているのか，あなたが困っている具体的な状況を手抜きせずに教えてください。そういうのはメンドクサイのでイヤなのでしたら，出来るまで頑張ってやってください。
補足
keithinさま ありがとうございます。 また、こちらの説明不足のせいで 混乱させてしまい、申し訳ありません。 初歩的なエクセルの使い方しかしたことがなく、たまたま知人にピボットテーブルという集計の取り方を教えてもらったのですが、忙しい知人のため、こちらで質問した次第です。 おっしゃる通りで、どう説明してよいのやら…説明する適切な用語も見つからず、それすらもわからず質問して、ご迷惑をおかけしております。しかし、こちらの配慮が足りないにも関わらず、再度ご回答をくださり、本当にありがとうございます。 ちょっと長くなってしまいましたが、ご迷惑でなければ、もう一度、教えていただけませんか？どうしても解決したいので、力を貸してください。 ステップ２のとおり、新しい空のシートで表を作成 例１： 日付 科目 金額 4/1 仕入 1000 4/2 売上 3000 4/3 仕入 5000 4/4 仕入 100 4/5 売上 60000 4/6 通信費 488 4/7 売上 200 4/8 仕入 400 4/9 売上 55000 4/10 仕入 6700 4/11 仕入 4300 5/1 売上 1700 5/2 通信費 1000 5/3 売上 3000 そのあと、 名前 リスト 参照範囲 =OFFSET(Sheet1!$A$1,0,NOW()*0,COUNTA(Sheet1!$A:$A),3) ピボットテーブルの作成を開始し，元の値として =リスト と書き換えて作成しました。 すると、ピボットテーブルが完成しました。 （出来上がった表をそのままコピペしました） 例１の結果： 行ラベル 合計 / 金額 4月 仕入 17500 売上 118200 通信費 488 5月 売上 4700 通信費 1000 総計 141888 次、実際の表でやってみました。 実際の「仕訳シート」の表として例２： 日付 科目 金額 ★仕入詳細 4/1 仕入 1000 a 4/2 売上 3000 （空欄） 4/3 仕入 5000 b 4/4 仕入 100 （空欄） 4/5 売上 60000 t 4/6 通信費 488 （空欄） 4/7 売上 200 g 4/8 仕入 400 （空欄） 4/9 売上 55000 g 4/10 仕入 6700 （空欄） 4/11 仕入 4300 g 5/1 売上 1700 （空欄） 5/2 通信費 1000 g 5/3 売上 3000 （空欄） 空欄セルがあり、ピボットテーブル自体作れませんでした。 とりあえず、何か入力しておいて、全て埋めてから もう一度試してみると、“例１の結果”のように ピボットテーブルもグループ化もできました。 これで、月ごとの集計ができることがわかりました。ありがとうございます。 それで、最初の質問にも書いたように、 ネットからダウンロードした“経理ソフトのようにする方法”を知りたいのです。 ＞使っている経理のソフト（エクセル）があります～（中略）～この「集計月」のリストはどうやれば作れますか？ 説明が行き届かずすいません。 経理ソフトで元々作成してあるピボットテーブルのシート（仮にシートＡ）を利用していれば、問題ない話だったのですが、 実際、それだけでは、詳しく集計ができないため、新しい別のシート（仮にシートＢ）でピボットテーブルを作成して集計をしたいと思いました。 方法は、、 元シート（同じ「仕訳帳シート」）の全てを範囲指定（まだ未定の行も）→ピボットテーブル→新規のワークシート→OK 空欄があるので日付のグループ化はできませんが、すべてのセルを範囲指定してあるため、 元データを下へ下へと継ぎ足していっても、追加データをピボットテーブルに集計することはできます。 そして、このあとが、わかりません。 シートＡのフィールドリストには、 □ 日付 □ 月集計 □ 科目 □ 金額 と表示されてあるのですが、 私が、同じ「仕訳帳シート」を使ってシートＢにピボットテーブルを作成しても □ 日付 □ 科目 □ 金額 □ ★仕入詳細（このフィールドは後で追加したものなので、シートＡには反映されていません） といういうように、「月集計」（グループ化しなくても既に月ごとに集計できるフィールドリストです）というフィールドリストは表示されません。 何か、数式などがあって、隠れてみえないのかわかりませんが、 この「月集計」のフィールドリストがでるようにしたいので、 やり方を教えてください。 なんとか、自分なりにわかりやすいようにしてみたのですが、初心者説明ということで、お許しください。 よろしくお願いいたします。
- keithin
- ベストアンサー率66% (5278/7941)
新しい練習ブックを用意し，次の手順に従い練習してマスターてください。 【ステップ１】日付をグループ化する 新しい空のシートを１枚用意する Ａ１セルから，日付列を含んだ元の表を用意する（例：ＡＢＣ列に日付，科目，金額） Ａ１セルだけを選んだ状態でピボットテーブルレポートの作成を開始する ○ウィザードが自動で表範囲を取得して作成開始したことを確認する ●Ａ：Ｃ列を列選択して作成を開始してはいけない 日付を行に，科目を列に，金額をΣに配置する 日付▼を右クリックしてグループ化を開始する 年と月でグループ化する （必要に応じてグループ化の開始・終了日付を修正する） 参考： http://www4.synapse.ne.jp/yone/excel2010/excel2010_pivot3.html ○ポイント 日付のグループ化は，レポートの「元にしたセル範囲」の日付列（セル範囲）に「空白のセル」や「日付ではない内容，たとえば文字列，例えば""」が含まれていると，出来ません。 将来のデータ追加を見越し例えば「列:列」で範囲指定してレポートの作成を開始したとか，表の途中にデータのヌケがあって空白セルが紛れていた，などの状況があると日付のグループ化が出来ません。 少なくとも日付列について，必ずキッチリ埋まった元データから，いま実際にデータが埋まっているセル範囲だけを選んでピボットテーブルの作成を開始します。 【ステップ２】範囲の自動取得 新しい空のシート（Sheet1）を１枚用意する Ａ１セルから，日付列を含んだ元の表を用意する（例：ＡＢＣ列に日付，科目，金額） ○例によって空白を含まないキッチリ埋まったデータを用意する 数式タブの名前の定義を開始し，次の通り追加する 名前 リスト 参照範囲 =OFFSET(Sheet1!$A$1,0,NOW()*0,COUNTA(Sheet1!$A:$A),3) ピボットテーブルの作成を開始し，元の値として =リスト と書き換えて作成する 一渡り作成できたら元の値の下にデータを追記した後にレポートを更新して，自動的に追加データを拾って集計出来ているか確認する ○ポイント 実際には元データを下へ下へと継ぎ足していくので，ステップ１の方法では追加データをピボットテーブルに集計させることができません。 追加データを自動的に取得し，常に最新のデータから集計を行えるよう準備を仕込みます。
補足
早速のご回答、本当にありがとうございます。 ステップ２の手順で作ってみたところ、 >ピボットテーブルの作成を開始し，元の値として =リスト と書き換えて作成する という部分が、よくわかりませんでした（汗） いろいろしてみたのですが、わからず・・・ ステップ２の方法で、質問の通り、月集計はできるのでしょうか？
お礼
keithinさま ご回答いただき、ありがとうございます。 ついに出来ましたっ！！ 嬉しくて嬉しくて仕方ありません！ ＞実際に仕訳帳シートで，どこかのセルに =COUNTA(仕訳帳!$B:$B) と数式を記入してみて，きっちりしっかり「１０」行という答えが出るか確認してください。 答えは、「１０」ではなく、「2107」で、しかもB7セル（項目セル）の上にも言葉や数式が入力されてありました。 ここが原因だったようです。 そこで、 =OFFSET(仕訳帳!$B$7,0,NOW()*0,COUNTA(仕訳帳!$B$7:$B$2100),5) の数式を実際のシートの場所や数に合わせて入力しなおしてやってみると ピボットテーブルのグループ化が成功しました！ 更新しても、ピボットテーブルも反映されます！ ＞発生させた「月 ▼」を「月集計▼」とそのセル自体書き換える も意味がわかり、出来ました！ 実は、途中、あまりにも自分の不甲斐なさに これ以上、ご迷惑かけるのもいけないと思い、 断念しようと思ったこともありました… しかし、 逆に、最後まで見放さずにご回答くださったkeithinさまに 申し訳ないと思い直し、頑張りました～！ 大袈裟に見えるかもしれませんが、 本当に最後まで諦めずにやってみてよかったです。 これもひとえに、keithinさまのお蔭です＾０＾ 本当に本当にありがとうございました。 また、よろしくお願いいたします。