職場でExcel2003を使っています。
商品ごとに、取引先の何拠点に納入しているか、調べる…という仕事が発生しました。
元データが、こんな感じ↓
2011年度7月販売実績
担当部 担当課 取引先名 取引先詳細 商品A 商品B 商品C
○○部 ○○課 A式会社 a支店 100円 100円 200円
○○部 ○○課 A式会社 b支店 10円 0円 200円
○○部 ○○課 B式会社 a支店 100円 100円 200円
○○部 ○○課 C式会社 a支店 0円 0円 200円
…
販売実績(金額)表です。
ピボットの「データの個数」の方を表示する風にすれば、各商品ごと・取引先ごとに、何店舗に買って頂いたのか!ていう、データが出せるのじゃないか、と。
担当部 担当課 取引先名 商品A 商品B 商品C
○○部 ○○課 A式会社 2 1 2
○○部 ○○課 B式会社 1 1 1
↑という形に、データを持っていきたい。
今日、元データの下に
担当部 担当課 取引先名 商品A 商品B 商品C
○○部 ○○課 A式会社 ● ● ●
○○部 ○○課 B式会社 ● ● ●
という表を作り、●部分のセルにCOUNTA関数を入れ、上の表の範囲を一つ一つ指定して…ということをしたのですが( COUNTA(C4:C124) とかそういう具合)、手間がかかるし、月ごとに取り扱う会社・店舗数が変動する(予定)なので、来月また同じ設定をしなおさなきゃならないですよね。
ピボットでやっちゃえば一発じゃない?
と思っても、
「行データ(←列だったかも)が多すぎる、データ数を減らすか、非表示に設定しろ」
みたいなエラーメッセージが出て、うまく行きません…。
ちなみに、元データが1万行と少し。かなり大きなデータです。
が、誰が作ったとも知れぬ昔のファイルを見たら、4万行を超すデータもピボットで組んでいて。
ということは、ピボットで集計できるだろう、とは思うのですが…その表のレイアウトを覗いて、全く同じように(ウィザード画面で)並べても、完了ボタンを押すと、データが多い、とはねられるのです。
うまいやり方をご存知の方、どうかお教えください。
No.1ベストアンサー
- 回答日時:
>今日、元データの下に
担当部 担当課 取引先名 商品A 商品B 商品C
○○部 ○○課 A式会社 ● ● ●
○○部 ○○課 B式会社 ● ● ●
という表を作り、●部分のセルにCOUNTA関数を入れ、上の表の範囲を一つ一つ指定して…ということをしたのですが
この部分はCOUNTA関数ではなくSUMPRODUCT関数を利用します。
表示したい担当部、担当課、取引先名がK2, L2, M2,セル以下に入力されているなら、N2セルに以下の式を入力して右方向に3つ下方向にデータ数分だけオートフィルしてください。
=SUMPRODUCT(($A$2:$A$10000=$K2)*($B$2:$B$10000=$L2)*($C$2:$C$10000=$M2)*(D$2:D$10000<>0))
ただし元データに本当に0円と入力されているなら、数式の最後の部分を「D$2:D$10000<>"0円"」にする必要があるし、実際は空白なら「D$2:D$10000<>""」に、この部分に関係なくA,B,C列の組み合わせが一致するデータで良いなら、この部分を削除することになります。
>その表のレイアウトを覗いて、全く同じように(ウィザード画面で)並べても、完了ボタンを押すと、データが多い、とはねられるのです。
そもそも元のデータはレイアウトがピボットテーブルの集計に向いていないリストになっています。
例示のリストで作成した場合は、おそらく列フィールドにD列のA商品などの項目をドラッグしたため、表示するデータが多く(この場合は100円や200円などのデータが数多く表示された)のではないかと思われます。
もしピボットテーブルで対応するなら、金額を集計するのではなく、商品の種類を集計できる以下のようなリストにする必要があります。
担当部 担当課 取引先名 取引先詳細 商品 金額
○○部 ○○課 A式会社 a支店 商品A 100
○○部 ○○課 A式会社 b支店 商品B 200
回答、ありがとうございました!
うわ~、やっぱり元データがピボット向きじゃないですかっ。
昨日、帰宅途中に(結局就業時間中にうまくデータ作り直せなかった)本屋に飛び込み、エクセルの本を立ち読みしたら、例として載っていたデータが、やっぱりお示しの表みたくなってて、
「…もしや、元データがピボットに変換できる形になっていない …?」
とは思ったのですが…!
元データのレイアウト、変えられるかなあ…
データ取得時に、レイアウトを変えられるみたいなんですが、まだあまり慣れてなくて、できるかどうかわからないんですよね…明日出社したら、元データのレイアウト変更を出来るかどうか、試します!
No.4
- 回答日時:
「ピボットテーブル」に固執して「↑という形に、データを持っていきたい」のなら、「元データが、こんな感じ」でなく、添付図上段のような「感じ」にする必要があります。
そうすると添付図下段の結果が得られます。
No.2
- 回答日時:
ビボットテーブルを利用する方法は取引先、支店名の増加などでは再びビボットテーブルを操作する必要があるなど不便なことがあります。
関数を使って別のシートに表示させるようにすればさまざまな変化に瞬時に対応させることができます。
シート1にお示しのデータがあるとしてA2セルからG2セルにかけて項目名が入力されておりそれぞれのデータが稼業に入力されているとします。作業列としてH3セルには次の式を入力して下方にオートフィルドラッグします。
=IF(C3="","",IF(COUNTIF(C$3:C3,C3)=1,MAX(H$2:H2)+1,""))
シート2にお求めの表を表示させるとしてA2セルからF2セルにかけて担当部 担当課 取引先名 商品A 商品B 商品Cと項目名をそれぞれ入力します。
A3セルには次の式を入力してC3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(ROW(A1)>MAX(Sheet1!$H:$H),"",INDEX(Sheet1!$A:$C,MATCH(ROW(A1),Sheet1!$H:$H,0),COLUMN(A1)))
D3セルには次の式を入力したのちにF3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF($C3="","",COUNTIF(INDEX(Sheet1!E:E,MATCH($C3,Sheet1!$C:$C,0)):INDEX(Sheet1!E:E,MATCH($C3,Sheet1!$C:$C,0)+COUNTIF(Sheet1!$C:$C,$C3)-1),">0"))
回答、ありがとうございます!
ま、待って、まず
「もしC3セルが空白なら、空白を表示せよ。空白じゃないならIF(COUNTIF(C$3:C3,C3)=1,MAX(H$2:H2)+1,"")を表示せよ」
と入れるんですね?
IF(COUNTIF(C$3:C3,C3)=1,MAX(H$2:H2)+1,""))
は
「COUNTIF(C$3:C3,C3)が1ならMAX(H$2:H2)+1,を表示せよ。1以外のときは空白を表示せよ。」
の意味、更に
COUNTIF(C$3:C3,C3)
は
「C列3行目(固定)から、C3までの範囲で、C3セルに入ってる値と同じ値の個数を求めなさい」。
…メモります。メモって明日会社で試します…!
…お盆休みで上司達がお休み(下っ端は出勤…)なので、会社から直接このページにアクセスしちゃあだめだろうか!と思いつめる昼下がり。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
ピボットテーブルで空白セルの数を集計したい
Excel(エクセル)
-
空白なのにセル個数がカウントされる(EXCEL)
Excel(エクセル)
-
EXCEL ピボット集計表の (空白) について
Excel(エクセル)
-
-
4
ピボットを更新すると数式が入っているセルが0になる
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル マクロVBAについて教...
-
英数字のみ全角から半角に変換
-
エクセルで自動的にQRを表示さ...
-
outlookのメールが固まってしま...
-
【Excel VBA】PDFを作成して,...
-
Microsoft Formsの「個人情報や...
-
パソコン画面の中の小さい画面...
-
会社PCのメールが更新されない
-
Microsoft Officeを2台目のPCに...
-
エクセルでXLOOKUP関数...
-
マイクロソフト 一時使用コード...
-
Officeを開くたびの「再起動メ...
-
複数の写真を1枚に印刷
-
office365って抵抗感ないですか?
-
teams設定教えて下さい。 ①ビデ...
-
http://oshiete.goo.ne.jp/qa/1...
-
Excel テーブル内の空白行の削除
-
Microsoft365で写真をアルバム...
-
Teams内でショートカットって貼...
-
この様式の雛形を作るとしたら...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
この様式の雛形を作るとしたら...
-
【Excel VBA】PDFを作成して,...
-
マクロ自動コピペ 貼り付ける場...
-
Microsoft Officeを2台目のPCに...
-
outlookのメールが固まってしま...
-
エクセルでのパーセントの計算
-
エクセルでXLOOKUP関数...
-
Microsoft Formsの「個人情報や...
-
Outlook で宛先が複数の場合の人数
-
大学のレポート A4で1枚レポー...
-
Excel テーブル内の空白行の削除
-
【マクロ】違うブックのCallス...
-
teams設定教えて下さい。 ①ビデ...
-
Officeを開くたびの「再起動メ...
-
エクセルで英文字に入れた下線...
-
Microsoft365で写真をアルバム...
-
Teams内でショートカットって貼...
-
office365って抵抗感ないですか?
おすすめ情報