職場で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で質問しましょう!
似たような質問が見つかりました
- 債券・証券 仕訳のこの問題が分かりません。教えていただけるとありがたいです 1 2022/06/15 20:34
- Excel(エクセル) 【エクセル関数】複数条件に該当する場合、別の列の数値を合算する。 9 2022/07/09 08:46
- その他(ネットショッピング・通販・ECサイト) 転売されてしまった商品を取り返そうと検索していたらフリマサイトや大手会社ではない聞いたことのないサイ 5 2022/08/25 21:22
- 簿記検定・漢字検定・秘書検定 売上諸掛りについて 当社はブンタ株式会社ヘ商品100円に送料10円を加えた合計額で販売し、代金は掛け 3 2022/04/28 10:40
- 簿記検定・漢字検定・秘書検定 簿記2級 連結会計 未実現利益の消去の問題です。 「次の取引について連結修正仕訳を行いなさい。 S社 1 2023/08/24 12:30
- スーパー・コンビニ ずっと前の出来事なのですが、相談させて下さい。 ある日コンビニにいって商品を3つ購入しました。レジに 2 2022/04/24 11:11
- ヤフオク! 関税 詐欺でしょうか?ヤフオク 3 2023/06/25 11:22
- 財務・会計・経理 管理会計論 運転資金 2 2022/11/21 14:28
- その他(データベース) c言語の問題です。これを踏まえてコーディングしたいのでおしえていただきたいです。 3 2023/08/03 09:27
- 財務・会計・経理 簿記の質問(難問です) この場合の仕訳、記帳方法を教えてください。 6 2022/09/13 13:52
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
英数字のみ全角から半角に変換
-
【スプレッドシート】指定の日...
-
Microsoft Formsの「個人情報や...
-
会社PCのメールが更新されない
-
【Excel VBA】PDFを作成して,...
-
マイクロソフト 一時使用コード...
-
エクセルでXLOOKUP関数...
-
office2016のパソコン2台インス...
-
Excelのセルの重複チェックが出...
-
teams設定教えて下さい。 ①ビデ...
-
Excel テーブル内の空白行の削除
-
Microsoft365で写真をアルバム...
-
会社におけるOfficeライセンス...
-
VLOOKUP関数について
-
Windows 11で、IME言語バー(IM...
-
officeビジネス型のワードやエ...
-
VBAファイルの保存先について
-
エクセル、ワード、ネット検索...
-
Outlook で宛先が複数の場合の人数
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
マイクロソフト 一時使用コード...
-
英数字のみ全角から半角に変換
-
Office2021を別のPCにインスト...
-
Microsoft Formsの「個人情報や...
-
officeビジネス型のワードやエ...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
Microsoft Officeを2台目のPCに...
-
何このステータスバー
-
2つのシートの一致する行のセ...
-
会社のTeamsのことで相談です。...
-
エクセルにリンクされるのをし...
-
Windows 11で、IME言語バー(IM...
-
office2010とoffice365の共存で...
-
Microsoftのパソコンです。 エ...
-
エクセルでXLOOKUP関数...
-
Excel関数について質問ですm(__)m
-
VBA
-
自分の専門分野の仕事。初見で...
おすすめ情報