[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『積み上げ棒グラフの色分け，負の値の取り扱い，強調について』（コロナで寝不足の30歳医師）
はじめまして．
現在500人程度の癌患者さんの治療データをまとめていますが，積み上げ棒グラフの処理に苦慮しております．
各患者さんは1つ目の治療として治療A，それが効かなくなった後に治療B，それが効かなくなった後に治療C．．．という流れで治療を受けます．
患者さんによっては，1つ目の治療で治療B，次治療で治療A，次治療で治療C．．．と治療順序が一部逆転していたりします．
各治療の間には，治療A，Bの間にInterval-1，BとCの間にInterval-2．．．のように休薬期間が設けられます．
患者さんが死亡した日まで，もしくは転院などでその後の経過が分からなくなった日（転院日）まで観察を続け，それぞれの治療期間，Interval，生存期間のデータを収集しています．
上記を元にスイマーズプロットという，横向きの積み上げ棒グラフ（縦軸は各患者さん，横軸は患者さんの治療期間+Intervalの総和＝生存期間(転院の場合には，最期に観察した日までの期間)（日））を作成し，どの治療がどの位効いているのかの傾向を探りたいと考えています．
積み上げ棒グラフを作成することは出来ましたが，お聞きしたいのは，
１．全ての患者の治療A，Bを同じ色にすることは出来るのでしょうか？
→治療の順序がランダムなので，全ての治療A，Bが同じ系列に入ってはおらず，一括選択できません．また患者数も500人程度と多いので，積み上げ棒グラフの項目一つ一つの色変更を行うのは，エラーのもとになるかと考えています．（データが500もあると一本一本の棒グラフが細くて．．．）
２．治療間のIntervalが負の値になる＝治療AとBを並行して行っている場合があり，これを積み上げ棒グラフに反映することが出来るのでしょうか？（治療A+Bを行っている期間を表示する）
→負の値を入れたままグラフ作成すると，縦軸の左側に負の値が表示されてしまいます．
３．当院で死亡確認した患者さんの棒グラフのみに印をつける，あるいは強調することは出来るのでしょうか？
→現在は，死亡確認した患者さんにのみ，生存期間+10の値を入力し，それを散布図（軸変換して）で組み合わせることで，棒グラフの頂点より少し上（右横向き棒グラフなので棒グラフの右横）に●印が来るようにしていますが，微妙に位置がずれてしまうなど，苦慮しております．何か別の方法はないでしょうか？
質問内容が煩雑かつ多くなってしまい申し訳ありませんが，ご検討の程よろしくお願い申し上げます．
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
早速のご回答ありがとうございました．
当方としましては，1人の患者さんの経過を1行（今回は積み上げ棒グラフ）で分かるようにし，それを患者さんの数だけ積み上げて治療薬の治療効果とそのパターンの傾向を見たいと考えております．
質問２につきましては，積み上げ棒グラフでの表現が難しければ，治療を同時併用したことがある患者のデータは省いて，その傾向をみようと考えておりましたので，誠に勝手ながら質問を取り下げさせていただきます．
積み上げ棒グラフで行うとすれば，質問１，２につきましては，如何でしょうか？
繰り返しの質問になってしまい恐縮ですが，ご検討の程，よろしくお願い申し上げます．
（コロナで寝不足の30歳医師） 2020/05/14(木) 20:34
（なまけもの） 2020/05/15(金) 00:01
遅ればせながら。 (1) | １．全ての患者の治療A，Bを同じ色にすることは出来るのでしょうか？ | →治療の順序がランダムなので，全ての治療A，Bが同じ系列に入ってはおらず， | 一括選択できません．また患者数も500人程度と多いので，積み上げ棒グラフの | 項目一つ一つの色変更を行うのは，エラーのもとになるかと考えています． | （データが500もあると一本一本の棒グラフが細くて．．．）
治療順序によらず、治療内容に応じて同じ色にすることは可能でしょう。 マクロを利用すれば、数の話はクリアーできるはずです。
ActiveChart.SeriesCollection(k).Points(m)で m番目の患者についての、k番目治療行為のポイントと特定できますので、 それに対応した領域の背景色を、治療内容に応じた色で塗ることが可能です。 並行治療の場合に色を変えることも可能でしょう。（ただ500人を同時表示するとなると 殆ど目立たないでしょうけど）
もしそうした方針が妥当であるとするなら、 データ形式の例を示してもらえば、サンプルコードは書けると思います。
(2) 質問3についても、マクロで可能です。 グラフ上に AutoShapeを挿入することは比較的容易です。
例えば、下記のようなサンプルでできます。
Sub test()
Dim sp As Point
Dim cht As Chart
Dim shp As Shape
Set cht = ActiveChart
Set sp = cht.SeriesCollection(4).Points(1)
'赤い楕円を描画
Set shp = cht.Shapes.AddShape(msoShapeOval, sp.Left + sp.Width, sp.Top + 0.5 * (sp.Height - 10), 10, 10)
shp.Fill.ForeColor.RGB = RGB(255, 0, 0) '赤に塗る
End Sub
もちろん、これも一人目の４つめの治療が済んだあとの所に決め打ちで赤丸を追加するものです。
実際には、状況に応じてk、m を判定するロジックを追加することになります。
これも元データのサンプルがあれば、コード化は可能です。
（γ） 2020/05/16(土) 05:55
まず、「スイマーズプロット」ってのは↓ここにあるグラフで良いでしょうか？
https://ameblo.jp/hbksakuemon/entry-12288814590.html
確認ですが
出来上がっている積み上げ横棒グラフのデータ表イメージはこんな感じでしょうか？
[A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L]
[1] １つ目 ２つ目 ３つ目
[2] 治療名 期間１ Int-1 治療名 期間２ Int-2 治療名 期間３ Int-3 生存期間 脂肪
[3]Ａさん 治療Ａ 3 3 治療Ｂ 5 3 治療Ｃ 7 3 24 34
[4]Ｂさん 治療Ｂ 3 3 治療Ａ 4 3 治療Ｃ 4 3 20
[5]Ｃさん 治療Ｂ 3 3 治療Ａ 4 3 治療Ｃ 4 3 20
[6]Ｄさん 治療Ｃ 2 3 治療Ｂ 5 3 治療Ａ 3 3 19 29
[n]Ｚさん
私の方法は現状作られている積み上げ棒グラフと、積み上げ順序が変わると思いますので、
これが問題になる様でしたら読み飛ばして下さい。
＞ １．全ての患者の治療A，Bを同じ色にすることは出来るのでしょうか？
＞ →治療の順序がランダムなので，全ての治療A，Bが同じ系列に入ってはおらず，
＞ 一括選択できません．（後略）
色々試してみましたが、上記の様なデータ表で出来ているグラフ要素色を一発で変える策は思いつきませんでした。
ただグラフ作成する手順に工夫を加えれば、それらしく見えるグラフが出来そうです。
手順説明が文章だと煩雑なので、データ表を以下の様に簡略化(Interval省略)して説明します。
（１）列Ｊ：項目を追加して計算式を入れます(積み上げ棒と散布図を軸合わせ目的)
[A] [B] [C] [D] [E] [F] [G] [H] [I] [J]
[1]治療名 治療Ａ 治療Ｂ 治療Ｃ
[2] １つ目 ２つ目 ３つ目
[3] 治療名 期間１ 治療名 期間２ 治療名 期間３ Inter計 脂肪 項目
[4]Ａさん 治療Ａ 3 治療Ｂ 5 治療Ｃ 7 9 34 3.5 ← =ROWS($A4:$A$7)-0.5
[5]Ｂさん 治療Ｂ 3 治療Ａ 4 治療Ｃ 4 9 20 2.5 ← =ROWS($A5:$A$7)-0.5
[6]Ｃさん 治療Ｂ 3 治療Ａ 4 治療Ｃ 4 9 20 1.5 ← =ROWS($A6:$A$7)-0.5
[7]Ｄさん 治療Ｃ 2 治療Ｂ 5 治療Ａ 3 9 29 0.5 ← =ROWS($A7:$A$7)-0.5
（２）【Ctrl】キーを押しながら、A3:A7、C3:C7、E3:E7、G3:G7、H3:H7、I3:I7をドラッグ選択します
（３）ドラッグ選択状態のままで、積み上げ横棒グラフを作成します。
※出来上がったグラフが、期間１,２,３がグラフ系列になっていればＯＫです。
★ここからが煩雑手順です★
（４）期間１系列を右クリック選択して[データの選択]⇒「データソースの選択」ダイアログを表示させます
（５）凡例項目(系列)で☑期間１を選択して編集ボタン押下すると「系列の編集」ダイアログが表示されます
（６）系列名欄の↑を押下して、Ｂ１セルをクリックして「系列の編集」ダイアログに戻ります
（７）系列値欄の↑を押下して、【Ctrl】キーを押しながら、Ｃ４、Ｅ５、Ｅ６、Ｇ７をセルをクリックして「系列の編集」ダイアログに戻ります
◆言葉を換えると、患者様毎の治療Ａの期間セルだけを【Ctrl】キーを押しながら選択する、です◆
（８）期間２、期間３についても(５)～(７)を行います。
これで、期間１,２,３のグラフ系列が治療Ａ,Ｂ,Ｃに変わっていればＯＫです。
＞３．当院で死亡確認した患者さんの棒グラフのみに印をつける，
＞ あるいは強調することは出来るのでしょうか？
＞ →現在は，死亡確認した患者さんにのみ，生存期間+10の値を入力し，
＞ それを散布図（軸変換して）で組み合わせることで，（中略）
＞ 微妙に位置がずれてしまうなど，苦慮しております．何か別の方法はないでしょうか？
こちらは散布図の作り方に一工夫加えれば解決するように思います。
上記データ表の列Ｊ：項目がそれで、列Ｉ：脂肪と列Ｊ：項目で散布図を作れば積み上げ横棒との
微妙な位置ズレは解消できると思います。
こちらの掲示板にも「縦折れ線グラフ」で検索すれば過去ログはあると思いますが、
稚拙ですが↓で手順３以降を参考にして貰えれば幸です。
「縦方向に伸びる折れ線グラフの作り方」
http://excelgraph1.starfree.jp/graph107/graph107.html
解説では「横棒と散布図(直線とマーカー)の組合わせ」で説明していますが、
これを「積み上げ横棒と散布図(マーカーのみ)の組合わせ」に読み替えれば出来ると思います。
少しでもお役に立ちましたら幸です。
では、では。m(_ _)m
（ブックマーク） 2020/05/16(土) 11:33
１）ダミーの系列を一番右側に１つ追加して、
２）塗りつぶしと枠線なし
３）データラベル（セルの値）で、死亡マークを表示
（マナ） 2020/05/16(土) 13:02
こんなデータとして
-A- -B- -C- -D- -E- -F- -G- -H- -I- -J- -K- 1 治療1 期間1 治療2 期間2 治療3 期間3 治療4 期間4 状況 作業列 2 Aさん 治療A 3 治療B 5 Int 2 治療C 2 退院 1 3 Bさん 治療B 3 Int 2 治療A 3 1 4 Cさん 治療B 4 Int 2 死亡 1 5 Dさん 治療C 5 治療A 5 治療B 4 転院 1
下記のマクロを実行すると
治療内容によって色分けした
フラフを作成します。
あくまでたたき台です。
実際のレイアウトとは違うでしょうから
完成までは、かなり修正は必要でしょう。
データラベルの設定は手作業でして
色分けだけをマクロにするとよいかもしれません。
なお質問2については、まったくわかりません。
Option Explicit
Sub test()
Dim r As Range
Dim cht As Chart
Dim n As Long, k As Long
Dim ser As Series, p As Point, dl As DataLabels
Dim clr As Long
Set r = Range("a1").CurrentRegion
Set r = Intersect(r, r.Offset(1))
Set cht = ActiveSheet.ChartObjects.Add(100, 10, 500, 400).Chart
cht.ChartType = xlBarStacked
cht.HasLegend = False
n = r.Columns.Count
For k = 3 To n Step 2
Set ser = cht.SeriesCollection.NewSeries
ser.XValues = r.Columns(1)
ser.Values = r.Columns(k)
ser.ApplyDataLabels
Set dl = ser.DataLabels
dl.Format.TextFrame2.TextRange.InsertChartField _
msoChartFieldRange, r.Columns(k - 1).Address(, , , True)
dl.ShowRange = True
dl.ShowValue = False
If k < n Then
dl.Format.TextFrame2.TextRange.Font.Fill.Visible = msoFalse
End If
For Each p In ser.Points
Select Case p.DataLabel.Text
Case "治療A": clr = vbBlue
Case "治療B": clr = vbRed
Case "治療C": clr = vbYellow
Case "Int": clr = vbGreen
Case Else: clr = vbWhite
End Select
p.Format.Fill.ForeColor.RGB = clr
Next
Next
End Sub
途中インデントがずれていたのと せっかく変数を宣言しておいたのに、使っていなくて with～end withまでが長く みずらかったので修正しました。 （2020/05/16(土) 17:34）
（マナ） 2020/05/16(土) 17:11
何度もコメント頂き，ありがとうございます．
診療で中々PC端末が見られず，返事が遅くなってしましました．申し訳ありません．
当方が見たいものとしては「どの患者にどの治療が効いていそうか」「どの患者が全生存期間（転院していれば最後に観察した日までの期間）が長いか」の特徴を見たいと考えています．
全患者の各情報を1つの散布図に盛り込めるということでしょうか？それはとても魅力的です．
しかし，恥ずかしながら散布図の作成・解釈に慣れておらず，具体的なイメージが全くついていない状況です．
以下の例では，どのような作成手順，結果・解釈になるのでしょうか？
治療1 Int.1 治療2 Int.2 治療3 Int.3 治療4 Int.4 治療5 Int.5 治療6 Int.6 当院死亡
患者１ A180 15 B120 12 C100 25 D80 40 E50 40 あり
患者２ B200 12 C150 15 A5 300 D50 -20 E30 15 F20 30 あり
患者３ A200 0 A'80 15 B100 18 C80 30 D40 30 なし（転院）
患者４ A150 15 B120 -30 C10 15 D60 80 なし（転院）
患者５ A100 10 B300 15 A'60 20 C200 0 D5 300 あり
ご教授の程，よろしくお願い申し上げます．
（コロナで寝不足の30歳医師） 2020/05/17(日) 15:01
ご検討，コメントありがとうございます．返信が遅くなってしまい申し訳ありません．
マクロについて今しがた初歩的な知識をYouTubeで入れてきました．引用で大変申し訳ないのですが，（なまけもの）様への返信欄に記載させていただいた例において，治療A，治療B，治療A'に同じ色を割り振るにはどのようにすればよいのでしょうか？
質問ばかりで申し訳ありませんが，お時間のある際にご検討のほどよろしくお願い申し上げます．
また，質問3につきましては，（Y）様ご自身もご指摘の通り，（まな）様のものを試してみることにしました．ご検討・ご回答頂きまして，ありがとうございました．
（コロナで寝不足の30歳医師） 2020/05/17(日) 15:11
コメントありがとうございます．返信が遅くなってしまい，申し訳ありません．
とても事細かなアドバイス，誠にありがとうございました．
スイマーズプロットはリンクに貼っていただいた様なもので，一人の患者さんの生涯の治療経過を端的に表したものです．
各治療は，導入するタイミングや順序で治療の効果が若干異なる可能性もあるため，データの順序を入れ替えることは難しかもしれません．
ただ，ご助言頂いたとおりにデータを一部作成してみましたが，全体の傾向を掴むのには大変見やすく有用であると考えます．こちらもデータ検討の際に活用させて頂きたいと考えています．
お忙しいところ詳細なご助言を頂きまして，誠にありがとうございました．
（コロナで寝不足の30歳医師） 2020/05/17(日) 15:57
コメントありがとうございます．
質問?Bにつきましては，その方法が一番簡単そうですので，それでやらせて頂くことにしました，ありがとうございます．
また，お示し頂いたコードでサンプル作成してしてみましたが，大変イメージに近いものでした．
まずはこれで作成してみようと考えておりますが，
質問A．患者数を増やしていく際に，コードに変更すべき点はありますでしょうか？
質問B．治療とその色を増やしていく際には，「Case "治療C": clr = vbYellow」の様に行を足していけばよろしいでしょうか？
質問C．経過中に手術（例えば治療B）を行った患者に，患者の状況と同様に手術を行ったことが分かるマークを付けたいと考えています．その際には，最初にさせて頂いた質問3の様に行えばよいでしょうか．
お忙しいところ大変恐縮でございますが，ご検討のほどよろしくお願い申し上げます．
（コロナで寝不足の30歳医師） 2020/05/17(日) 17:05
>Set cht = ActiveSheet.ChartObjects.Add(100, 10, 500, 400).Chart
ただ、そもそも500人分も一つのグラフで表示することに無理がありませんか。
質問B
そうなのですが、実際はどの色が何を表すか説明が必要ですよね
例えば、N列をこんな感じにして
各セルの背景色を設定したい色に変更しておいてください。
マクロを修正しました。
-N-
1 色設定
2 治療A
3 治療B
4 治療C
5 Int
Option Explicit
Sub test2()
Dim r As Range
Dim cht As Chart
Dim n As Long, k As Long
Dim ser As Series, p As Point, dl As DataLabels
Dim clr As Long
Dim 色設定 As Range
Set 色設定 = Columns("N").Cells
Set r = Range("a1").CurrentRegion
Set r = Intersect(r, r.Offset(1))
Set cht = ActiveSheet.ChartObjects.Add(100, 10, 500, 400).Chart
cht.ChartType = xlBarStacked
cht.HasLegend = False
n = r.Columns.Count
For k = 3 To n Step 2
Set ser = cht.SeriesCollection.NewSeries
ser.XValues = r.Columns(1)
ser.Values = r.Columns(k)
ser.ApplyDataLabels
Set dl = ser.DataLabels
dl.Format.TextFrame2.TextRange.InsertChartField _
msoChartFieldRange, r.Columns(k - 1).Address(, , , True)
dl.ShowRange = True
dl.ShowValue = False
If k < n Then
dl.Format.TextFrame2.TextRange.Font.Fill.Visible = msoFalse
End If
For Each p In ser.Points
Select Case p.DataLabel.Text
Case "治療A": clr = 2
Case "治療B": clr = 3
Case "治療C": clr = 4
Case "Int": clr = 5
Case Else: clr = 1
End Select
p.Format.Fill.ForeColor.RGB = 色設定(clr).Interior.Color
Next
Next
End Sub
質問C
はい。状況欄に手術と入力してみてください。
（マナ） 2020/05/17(日) 18:26
γです。丁寧なコメントをいただきありがとうございました。 私への質問ですが、それはマナさんが書いてくださっているもので既に対応されていますので、 私から別のものを提示することは控えたいと思います。
念のための確認質問ですが、 | 患者１ A180 15 B120 12 C100 25 D80 40 E50 40 あり というデータですが、 A180 というのが一つのセルに入っているのですか？治療Aを180日間継続したと。 これは説明の便宜上の話で、実際には、Aと180は別セルに入っていますということですか？ (それとも、A180という種類の治療があるとか。） （γ） 2020/05/17(日) 21:33
質問2に関してですが、下記のような表に(マクロで）変換した上で、二段階の表示をしたらどうでしょうか。
A B C D E F G H I J K L M N O P Q R S T 1 治療1 期間 Int.1 治療2 期間 Int.2 治療3 期間 Int.3 治療4 期間 Int.4 治療5 期間 Int.5 治療6 期間 Int.6 当院死亡 2 患者１ A 180 15 B 120 12 C 100 25 D 80 40 E 50 40 あり 3 患者２ B 200 12 C 150 15 A 5 300 D 50 0 4 dummy 0 712 E 30 15 F 20 30 あり 5 患者３ A 200 0 A' 80 15 B 100 18 C 80 30 D 40 30 なし（転院） 6 患者４ A 150 15 B 120 0 7 dummy 0 255 C 10 15 D 60 80 なし（転院） 8 患者５ A 100 10 B 300 15 A' 60 20 C 200 0 D 5 300 あり
それなりのグラフにはなりましたが、併用治療がクローズアップされすぎでしょうかね。 （γ） 2020/05/18(月) 10:47
併用治療の場合は、患者1の治療2のように、カンマで区切って、入力します。 γさんのと違って、単に図形（長方形）をグラフに重ねただけなので 簡単に位置がずれてしまいます。 その場合は、再度、マクロ（sub色分け）を実行してください。
-A- -B- -C- -D- -E- -F- -G- -H- -I- -J- -K- -L- -M- -N- 1 治療1 期間1 治療2 期間2 治療3 期間3 治療4 期間4 状況 作業列 色設定 2 患者１ A 3 A,B 5 Int 2 C 2 退院 1 A 3 患者２ B 2 Int 4 A 3 1 B 4 患者３ B 4 Int 2 死亡 1 C 5 患者４ C 5 A 5 B 4 転院 1 Int
Option Explicit
Sub グラフ作成()
Dim r As Range
Dim cht As Chart
Dim n As Long, k As Long
Dim ser As Series, dl As DataLabels
Set r = Range("a1").CurrentRegion
Set r = Intersect(r, r.Offset(1))
Set cht = ActiveSheet.ChartObjects.Add(100, 10, 500, 400).Chart
cht.ChartType = xlBarStacked
cht.HasLegend = False
n = r.Columns.Count
For k = 3 To n Step 2
Set ser = cht.SeriesCollection.NewSeries
ser.XValues = r.Columns(1)
ser.Values = r.Columns(k)
ser.ApplyDataLabels
Set dl = ser.DataLabels
dl.Format.TextFrame2.TextRange.InsertChartField _
msoChartFieldRange, r.Columns(k - 1).Address(, , , True)
dl.ShowRange = True
dl.ShowValue = False
If k < n Then
dl.Format.TextFrame2.TextRange.Font.Fill.Visible = msoFalse
End If
Next
End Sub
Sub 色分け()
Dim 色設定 As Range
Dim cht As Chart
Dim ser As Series, p As Point
Dim v
Dim n As Long, k As Long
Dim f As Range
Dim clr As Long
Dim l As Single, t As Single, w As Single, h As Single
Dim sp As Shape
Set 色設定 = Columns("N")
On Error Resume Next
Set cht = ActiveSheet.ChartObjects(1).Chart
On Error GoTo 0
If cht Is Nothing Then Exit Sub
On Error Resume Next
cht.Rectangles.Delete
On Error GoTo 0
For Each ser In cht.SeriesCollection
For Each p In ser.Points
v = Split(p.DataLabel.Text, ",")
n = UBound(v)
For k = 0 To n
Set f = 色設定.Find(v(k))
If f Is Nothing Then
clr = 色設定(1).Interior.Color
Else
clr = f.Interior.Color
End If
If k = 0 Then
p.Format.Fill.ForeColor.RGB = clr
Else
w = p.Width
h = p.Height / (n + 1)
l = p.Left
t = p.Top + h * k
Set sp = cht.Shapes.AddShape(msoShapeRectangle, l, t, w, h)
sp.Fill.ForeColor.RGB = clr
End If
Next
Next
Next
End Sub
（マナ） 2020/05/19(火) 20:23
質問者さんは、それは無しでいいとおっしゃっている一方で、
例示としてマイナスありを提示されているので、
未練はあるんだろうな、なんとかならないかと考えていました。
それと、手術のマークというのは、指定した治療期間の最初に▲マークという
図形をつけたいということなのか、と思っています。
それも実装してみましたが、難点が一つあり、
全ての図形をグループ化したとしても、
グラフの大きさを変えたりという変換に対しては脆弱なんですよね。
少し図形の位置がずれてしまう。
それで私の対応策は、図形処理がからむものは、別出しにして、
再作成するという方針にしています。
（γ） 2020/05/19(火) 20:47
返信が遅くなってしまい，誠に申し訳ありません．
先に示して頂きましたマクロで，欲しかった図を作成することが出来ました．
研究の前段階で患者選択をする際に必要なものでしたが，皆様のおかげで，適切な患者をpick upすることができ，今後の研究に確実に活かせそうです．
当方の研究室のトップもこのように，ビジュアルで示されるとわかりやすく，次なる研究の内容に入っていきやすいと絶賛しておりました．（この掲示板で教えて頂いたことは勿論伝えております）
コロナの第2波が懸念される中ですが，皆様くれぐれも体調管理にお気をつけくださいね．
本当にありがとうございました．
（コロナの寝不足解消されてきたの医師） 2020/07/02(木) 13:46
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.