[[20210915232517]] 『大項目の売上高を基準に、中項目と小項目の売上高』(you) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『大項目の売上高を基準に、中項目と小項目の売上高の結果を並び替える』(you)

いつもお世話になります。

売上を地方別/都道府県別/本・支店別で集約した結果と、その結果を使った円グラフを作成するマクロを作りました。

本・支店別の売上データを集約したものが<売上集約データ>になります。

<売上集約データ>
 本・支店 | 地方別 | 都道府県別 | 売上高
  東京  |  関東  |  東京都  | 800,000
  札幌  | 北海道 |  北海道  | 650,000
  旭川  | 北海道 |  北海道  | 455,000
  仙台  |  東北  |  宮城県  | 580,000
   ・   |   ・  |    ・   |   ・  
   ・   |   ・  |    ・   |   ・  

上の表を範囲指定し、Dictionaryを用いて「重複のない値」と、「重複していたら足す」ということを「地方別」「都道府県別」「本・支店別」に吐き出して”売上高順”に並び替えたものが以下の表になります。

<地方別>
      | 売上高
  北海道  |1,105,000 
  関東  | 800,000
  東北  | 580,000

<都道府県別>
      | 売上高
  北海道  |1,105,000 
  関東  | 800,000
  東北  | 580,000

<本・支店別>
 本・支店 | 売上高
  東京  | 800,000
  札幌  | 650,000
  仙台  | 580,000
  旭川  | 455,000

これらの3つの表と、別シートにある支店リストにあらかじめ地域別に背景色を設定し、その背景色を用いた円グラフを作成したのですが、出来上がりを見た方から、
 ・売上の高い地域順
 ・その中でも売上の高い順
に表示できないかという相談がありました。

3つの表をその方の理想に合わせると
<地方別>
      | 売上高
  北海道  |1,105,000 
  関東  | 800,000
  東北  | 580,000

<都道府県別>
      | 売上高
  北海道  |1,105,000 
  東京都  | 800,000
  宮城県  | 580,000

<本・支店別>
 本・支店 | 売上高
  札幌  | 650,000
  旭川  | 455,000
  東京  | 800,000
  仙台  | 580,000

という事になります。
(円グラフにすると、同色が固まり内訳のように見ることができるからこうしたいとのこと)

実現したいことは判りましたが、<地方別>売上高の順位をどのように取得/利用したらよいか判らず相談させていただきました。
よろしくお願いします。

< 使用 Excel:Excel2010、使用 OS:Windows10 >


>円グラフにすると、同色が固まり内訳のように見ることができるから

ということは、円グラフは「地方別」の1つだけ作成ということでしょうか

(マナ) 2021/09/16(木) 08:29


↑そうだとすると、試してませんが

1)ピボットテーブルで集計
2)売上高で、地方別フィールドを並べ替え
3)ピボットテーブルを、複製(全部で3つ)
4)不要な列を非表示

これで、どうでしょうか。

(マナ) 2021/09/16(木) 08:41


マナさん

ありがとうございます。

グラフは3つ作ります。
地方別のグラフ、見た目(色の割合)は地方別と変わらないけど、要素が都道府県別に分かれているグラフと、本_支店別に分かれているグラフになります。
個人的には思うところもありますが、一応これが指示でした。

本日、
1)ピボットテーブルを作る
  行:地方/都道府県/本・支店
  Σ:売上
2)表形式にしてすべてのデータを繰り返す
  小計を表示後、並び替え:合計/売上
3)小計をなくす
という作業をしてから、今まで使用していたDictionaryのコードを使い、ピボットテーブルの項目行以外の範囲を取得して、吐き出すようにしました。
結果、地域順に吐き出されるし円グラフも「見た目は」希望された通りに同じ色が固まるようになりました。

ただ、個人的に気になっているのが、この状態だと地域の中での売上順にならない事です。
(ピボットテーブルのデータの順番をそのまま取得、吐き出ししているから)

Dictionaryオブジェクトを使った時は、内部での並び替えというのは可能なのでしょうか?
現在使用しているコードを抜粋すると、以下のようになります。

====================================
Const 地方 As Long = 1
Const 都道府県 as Long = 2
Const 本_支店 as Long = 3
Dim flg as String

'---メインコード----
 flg = 地方
 Call 重複を抜いた表

 flg = 都道府県

  Call 重複を抜いた表

 flg = 本_支店

  Call 重複を抜いた表
'-------------------- 

Private Sub 重複を抜いた表

Set myDic = CreateObject("Scripting. Dictionary")

    myVal = Range("A1:D10")

For i = 1 to Ubound(myVal,1)

   If Not myVal(i,flg) = Empty Then
     If Not myDic.Exists(myVal(i,flg)) Then
        myDic.add myVal(i,flg),myVal(i,4)
     Else
        myDic(myVal(i,flg))=myDic(myVal(i,flg)) + myVal(i,4)     
     End If
   End If
Next

End sub
====================================

(you) 2021/09/16(木) 20:57


投稿してから気づきました。

Dictionaryは「重複しない」が前提なので、

 地方,都道府県,値

とか

 地方,本_支店,値

といったデータを収納していくことはできないんですよね。
(地方が重複してしまうから)

Dictionaryにこだわってるからダメなのかなと思えてきました。
(you) 2021/09/16(木) 21:08


ちょっと何をしているか理解できていませんが、

並べ替えは、小計でなく、下記だとどうなりますか

1)地方▼をクリックで「その他の並べ替えオプション」から
  降順:合計/売上
2)本・支店▼をクリックで「その他の並べ替えオプション」から
  降順:合計/売上

(マナ) 2021/09/16(木) 21:23


ピボットで集計しているのに、dictionaryって必要ですか?

(マナ) 2021/09/16(木) 21:26


ピボットで集計(これで、重複はなくなる)
それをピボットグラフにすればよいというもの。
これなら、マクロも必要ないのでは、
と考えていました。

(マナ) 2021/09/16(木) 21:34


マナさん

ありがとうございます。
お返事が遅くなり申し訳ありません。

色々相談した結果、
更新ができるピボットテーブル(グラフ)のままでは困ると言われたので、
 ・ちょっと外れたところにピボットテーブル(3種類)をつくる
 ・ピボットテーブルはそれぞれ並び替えを行う
 ・必要な部分だけをコピー→指定の場所に「値の貼り付け」
 ・表示されたデータをもとにグラフ作成(要素は指定の色に塗り分け)
 ・3つのピボットテーブル削除
という流れでコードを作成し、本日確認していただき無事OKをもらいました。

相談にのっていただき、ありがとうございました。
(you) 2021/09/21(火) 23:17


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.