8 Replies Latest reply on Dec 20, 2016 7:02 AM by hiroshi iwata

    複数のディメンションから複数のメジャーの集計はできますか?

    hiroshi iwata

      下記ユーザリスト

      ユーザID氏名部署
      12345青井たろう営業部
      54674赤井一郎A支店

       

      下記売上データ

      計上月顧客名商品売上額販売担当1ID販売担当1氏名販売担当1売上比率販売担当1売上販売担当2ID販売担当2氏名販売担当2売上比率販売担当2売上
      16年1月丸井商事りんご2000012345青井たろう50%1000054674赤井一郎50%10000
      16年2月角田商店トマト2000054674赤井一郎100%20000

       

      というもので

       

      ユーザリストと売上データをIDで左結合し集計したいのですが全くうまくいきません。

       

      販売担当1の結果と販売担当2の結果を足した集計結果を出したい

       

      集計結果としては、下記のように集計したいのですが可能ですか?

      ユーザID氏名売上額
      12345青井たろう10000
      54674赤井一郎30000
        • 1. Re: 複数のディメンションから複数のメジャーの集計はできますか?
          Shinichiro Murakami

          できないことはないです。

          が、思った以上にややこしかったです。

          ややこしいのは、半角全角処理も入ってるので、なんともいえませんが。。

           

          まずは、データのPIVOT

           

           

           

           

          Pivot Table names ==> Category Name

          Pivot Table values ==> Category Value

          (*あとから見ると、このRenameはやくに立ってないので特に必要ないです)

          ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

          ここからが、本番(計算)です。

           

          [項目名 Calculated]

          if contains([Category Name],"売上比率") then"売上比率"

          elseif contains([Category Name],"Id") then "販売担当ID"

          elseif contains([Category Name],"売上") then "売上"

          elseif contains([Category Name],"氏名") then "販売担当氏名"

          end

           

          [売上額 calculated]

          if [項目名 Calculated]="売上" then int([Category Value]) end

           

          [氏名ID calculated]

          if [項目名 Calculated]="販売担当ID" then ([Category Value]) end

           

          [担当番号 calculated]  <== 半角に揃ってればもうちょっと簡単です。

          if ASCII(mid([Category Name],5,1))<0 then ASCII(mid([Category Name],5,1))+240

          else ASCII(mid([Category Name],5,1))-48 end

           

          [Sales x person]  <==これは、月単位とかの計算が入ってくると、修正しないといけないです。

          {fixed [商品],[担当番号 calculated]:sum([売上額 calculated])}

           

          Name Tableをリンク

          で、氏名と部署をTableから持ってきて表示

           

          以上です。

           

          ****************************** 基本は同じコンセプト(二つ以上の項目をひとつにまとめる)で、ユニオン使ったほうが簡単だと思います。

          ******************************* バージョン9.3以上に限られますが、こういう感じ。

           

          むらかみ

          • 2. Re: 複数のディメンションから複数のメジャーの集計はできますか?
            Shinichiro Murakami

            バージョン9.3 以上のときの Union 使う方法です。

             

             

             

            [Simei ID calc]

            case [Table Name]

            when "Sheet2" then [販売担当1ID]

            when "Sheet21" then [販売担当2ID]

            end

             

             

            [Uriage calc]

            int(

            case [Table Name]

            when "Sheet2" then [販売担当1売上]

            when "Sheet21" then [販売担当2売上]

            end

            )

             

            [uriage x person]

            {include [Table Name],[Simei ID calc]:sum([Uriage calc])}

             

             

             

            むらかみ

            • 3. Re: 複数のディメンションから複数のメジャーの集計はできますか?
              hiroshi iwata

              詳しくご回答いただきありがとうございました。

              思った結果が出力されて大変助かりました。

               

              今使ってるバージョンが9.0なのでUnionを使わずに

              やってみました。

               

              初心者なのでやってることがよくわからないところを

              詳しく教えていただけると助かります

              [担当番号 calculated]  <== 半角に揃ってればもうちょっと簡単です。

              この部分は何を出しているのですか?IDではなく別の数字がでているのは

              確認できたのですが、この処理がどう使われているのか教えていただけませんか

               

              [Sales x person]  <==これは、月単位とかの計算が入ってくると、修正しないといけないです。

              月単位となると[計上月]を数式に加えるといいのでしょうか?

              数式を編集してない状態で列に計上月を入れても集計されたので

              何が異なるのか分からなくて再度質問させていただきました。

               

               

               

               

              • 4. Re: 複数のディメンションから複数のメジャーの集計はできますか?
                hiroshi iwata

                数件のレコードでは問題なく集計できたのですが

                件数が多くなると余分なものまで集計している感じで

                売上額がとんでもない金額になってしまいます。

                金額・件数には+-がありピボットされていれば

                問題ないように思うのですが限界があるのでしょうか?

                 

                それとも計上月を列に設定したために不具合が起きてますか?

                 

                また項目として粗利額と件数を入れたいのですが

                Sales x personと同じように作成すれば

                集計項目が増えても問題ないですよね?

                • 5. Re: 複数のディメンションから複数のメジャーの集計はできますか?
                  Shinichiro Murakami

                  [担当番号 calculated]  <== 半角に揃ってればもうちょっと簡単です。

                  この部分は何を出しているのですか?IDではなく別の数字がでているのは

                  確認できたのですが、この処理がどう使われているのか教えていただけませんか

                   

                  担当者1売上  ID1  担当者2売上 ID2

                  1000      A111  2000     A112

                  をPivotすると、        

                  担当者1売上 1000

                  ID1   A111

                  担当者2売上 2000 

                  ID2    A112

                   

                  となりますが、1、2の識別がないと、どれがどれの売上か計算できません。

                   

                   

                  [Sales x person]  <==これは、月単位とかの計算が入ってくると、修正しないといけないです。

                  月単位となると[計上月]を数式に加えるといいのでしょうか?

                  数式を編集してない状態で列に計上月を入れても集計されたので

                  何が異なるのか分からなくて再度質問させていただきました。

                   

                  表示されている数字は、全期間の合計で、すべての月に同じ数字が入っているはずです。

                  [Sales x person] <==これは、月単位とかの計算が入ってくると、修正しないといけないです。

                  {fixed [商品],[担当番号 calculated],[形状月]:sum([売上額 calculated])}  

                  となります。

                   

                   

                  それとも計上月を列に設定したために不具合が起きてますか?

                   

                  上記の通りです。

                   

                   

                  また項目として粗利額と件数を入れたいのですが

                  Sales x personと同じように作成すれば

                  集計項目が増えても問題ないですよね?

                  理論的には問題ないです。

                  ただ、簡単に「ちょっとややこしいです」と書きましたが、「できないことはない」ことを示しただけで、

                  実際にはかなり多種のノウハウが必要です。

                  非常に制約が大きく、計算条件を少し変更すれば何か修正しないといけない可能性が大です。

                  どう修正しないといけないかは、データを見ないと分かりませんし、データ自身を工夫しないといけないかも知れません。

                   

                  社内の事情があるのは容易に想像できますが、Unionが使えるVersionを導入されるのが早いでしょう。

                   

                  むらかみ

                  • 6. Re: 複数のディメンションから複数のメジャーの集計はできますか?
                    hiroshi iwata

                    むらかみさん

                     

                    回答ありがとうございます。

                    数式を少し触ってみて数字が合ってきたので何とか頑張ってみます。

                    しかし現在のバージョンでは大変難しそうなことはわかりました。

                    バージョンアップも含めて検討したいと思います。

                    • 7. Re: 複数のディメンションから複数のメジャーの集計はできますか?
                      Shinichiro Murakami

                      そうですね。もうすぐ10.2 ですし。。

                      こういったアプリケーションにおいては、新しいものの方がいいのは間違いないでしょう。

                       

                      ところで、いずれかの回答を、「正解」にマークして頂けるとありがたいです。

                       

                      むらかみ

                      • 8. Re: 複数のディメンションから複数のメジャーの集計はできますか?
                        hiroshi iwata

                        失礼しました

                        評価の方法が分かってませんでした。