Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Excel 2010)
積上げグラフ
投稿日時: 18/04/09 19:26:59
投稿者: 純

よろしくお願いいたします。
 
A列に店舗名(A5セルからA47セル)
B列に前年比(77.2%、79.8%、95.5%、101.6%・・・とバラバラです)
 
があり
 
前年比の括りは下記の区分で括りたくて対象店は43店です。
 
〜80%・・・5店
〜90%・・・3店
〜100%・・・10店
〜105%・・・6店
〜110%・・・2店
〜115%・・・3店
〜120%・・・4店
120%以上・・・10店
 
これをグラフで作成するとき、
 
横軸が〜80%、〜90%、〜100%となっていて
 
〜80%のところに5店が積みあがり(前年比が低い順に)〜90%には3店が積みあがるように
グラフを作っていくのはどのようにしたらいいでしょうか?
 
どうぞよろしくお願いいたします。
 
 

回答
投稿日時: 18/04/10 07:49:10
投稿者: んなっと

●Sheet1
 
    A    B
 1 店舗 前年比
 2  あ   74%
 3  い   75%
 4  う   80%
 5  え   81%
 6  お   86%
 7  か   93%
 8  き   93%
 9  く  119%
10  け  120%
 
もしかして、こんなグラフですか?
 
 |                
 |                
 |                
 |                
3|     ┌―――┐      
 |     |  お|      
2|┌―――┐├―――┤┌―――┐ 
 ||  い||  え||  き| 
1|├―――┤├―――┤├―――┤ 
 ||  あ||  う||  か| 
0└┴―――┴┴―――┴┴―――┴―
   〜80%   〜90%  〜100%  
                 
 
●Sheet2
 
1行目B1に80%,C1に90%,.....と入力して
書式 ユーザー定義
"〜"0%
 
   A   B   C    D    E    F    G    H     I
 1   〜80% 〜90% 〜100% 〜105% 〜110% 〜115% 〜120% 120%以上
 2 あ   1  #N/A  #N/A  #N/A  #N/A  #N/A  #N/A   #N/A
 3 い   1  #N/A  #N/A  #N/A  #N/A  #N/A  #N/A   #N/A
 4 う  #N/A   1  #N/A  #N/A  #N/A  #N/A  #N/A   #N/A
 5 え  #N/A   1  #N/A  #N/A  #N/A  #N/A  #N/A   #N/A
 6 お  #N/A   1  #N/A  #N/A  #N/A  #N/A  #N/A   #N/A
 7 か  #N/A  #N/A    1  #N/A  #N/A  #N/A  #N/A   #N/A
 8 き  #N/A  #N/A    1  #N/A  #N/A  #N/A  #N/A   #N/A
 9 く  #N/A  #N/A  #N/A  #N/A  #N/A  #N/A    1   #N/A
10 け  #N/A  #N/A  #N/A  #N/A  #N/A  #N/A  #N/A     1
 
B2
=IFERROR(1/(MATCH(VLOOKUP($A2,Sheet1!$A:$B,2,FALSE),INDEX(1*(A$1:B$1),))=1),NA())
 
右方向・→下方向・↓
 
→挿入 グラフ 積み上げ縦棒
→行/列の切り替え
→縦(値)軸
 目盛間隔 固定 1
→ラベルを付けるために下のマクロ
 
Sub DataLabelAdd()
  Dim Chrt As Chart
  Dim Srs As Series
  If Not ActiveChart Is Nothing Then
    Set Chrt = ActiveChart
  Else
    If ActiveSheet.ChartObjects.Count > 0 Then
      Set Chrt = ActiveSheet.ChartObjects(1).Chart
    Else
      Exit Sub
    End If
  End If
  For Each Srs In Chrt.SeriesCollection
    With Srs
      .HasDataLabels = True
      .DataLabels.ShowSeriesName = True
      .DataLabels.ShowCategoryName = False
      .DataLabels.ShowValue = False
    End With
  Next
End Sub

回答
投稿日時: 18/04/10 07:50:56
投稿者: Suzu

こんにちは。
 
進め方を提示します。
 
1.C列に、〜80%、〜90%、〜100% の区分の値を持つ列を持たせる必要がありますよね。
  そこを、関数を使うのか、手動で入力するのかはお任せします。
 
2.A列から、C列までを選択し ピボットテーブルを作成
   行ラベルにC列の列名を指定、Σに前年比の値を指定し、
   値フィールドの設定で【合計】から【データの個数】に変更
 
3.作成されたピボットテーブルを選択した状態で、リボン中のピボットテーブルツール オプションにある
  ピボットグラフを選択し、希望のグラフ形態を選びましょう。

回答
投稿日時: 18/04/10 08:25:36
投稿者: んなっと

引用:
〜80%のところに5店が積みあがり(前年比が低い順に)

赤字の部分がポイントですね。
 
ということは、Sheet1はB列で昇順に並べ替えておいて、
 
Sheet2
A1は必ず空欄
A2
=Sheet1!A5
下方向・↓
 
も必要です。

投稿日時: 18/04/10 15:10:16
投稿者: 純

んなっと さま
Suzu さま
 
返信ありがとうございます。
 
Suzu さまの方法で簡単にグラフが作れる事がわかりました。ありがとうございます。
 
ただ今回は、んなっとさまが提示してくださった通り、店別がわかった上での積上げのため
んなっとさまの方法でなんとか形にする事ができました。
 
あともう一歩で必要な形になりますのでアドバイスをお願いします。
 
ラベルのところに店名以外の前年比の項目もTrue falseで表示が可能、
そこまで考えてくださり
 
んなっとさまの想像力に感動と感謝をしています。
 
この前年比を区分の前年比ではなく、本当の前年比(77%又は77.2%など)を表示させる事はできますでしょうか
 
難しいことをお願いして申し訳ありませんが、店名が長いためラベルの上側に店名、中段に前年比が
入ればなおいいのですが出来なければ続けて表示でも構いません。
 
引き続きどうぞよろしくお願いいたします。

回答
投稿日時: 18/04/10 15:28:08
投稿者: んなっと

      A   B   C    D    E    F    G    H     I
 1       〜80% 〜90% 〜100% 〜105% 〜110% 〜115% 〜120% 120%以上
 2  あ 74.0%   1  #N/A  #N/A  #N/A  #N/A  #N/A  #N/A   #N/A
 3  い 75.0%   1  #N/A  #N/A  #N/A  #N/A  #N/A  #N/A   #N/A
 4  う 80.0%  #N/A   1  #N/A  #N/A  #N/A  #N/A  #N/A   #N/A
 5  え 81.0%  #N/A   1  #N/A  #N/A  #N/A  #N/A  #N/A   #N/A
 6  お 86.0%  #N/A   1  #N/A  #N/A  #N/A  #N/A  #N/A   #N/A
 7  か 93.0%  #N/A  #N/A    1  #N/A  #N/A  #N/A  #N/A   #N/A
 8  き 93.0%  #N/A  #N/A    1  #N/A  #N/A  #N/A  #N/A   #N/A
 9 く 119.0%  #N/A  #N/A  #N/A  #N/A  #N/A  #N/A    1   #N/A
10 け 120.0%  #N/A  #N/A  #N/A  #N/A  #N/A  #N/A  #N/A     1
 
A2
=Sheet1!A5&CHAR(10)&TEXT(Sheet1!B5,"0.0%")
下方向・↓
B2
=IFERROR(1/(MATCH(VLOOKUP(LEFT($A2,FIND(CHAR(10),$A2)-1),Sheet1!$A:$B,2,FALSE),INDEX(1*(A$1:B$1),))=1),NA())
右方向・→下方向・↓

回答
投稿日時: 18/04/10 16:05:00
投稿者: んなっと

引用:
〜80%のところに5店が積みあがり(前年比が低い順に)
店別がわかった上での積上げ

表現があいまいです。
 
〜80%のところに、前年比の低い順に下からあ店,い店,う店,え店,お店のような5つの「店名」を積みあげたいのです。
そして積み上げグラフの一番上が縦軸の5個の値の高さになるようにしたいと思っています。

投稿日時: 18/04/10 16:44:24
投稿者: 純

んなっと さま
 
前年比表示の事を最初に書いておかなくて二度手間になってしまい申し訳ありませんでした。
 
無事完成することができました。
 
こんな難しいことはとても一人では出来なかったので
んなっとさまには感謝しかありません。
 
本当にありがとうございました。