●体内時計27時間の人の生活を表とグラフにしたいが、自力では限界... 画像があまりにも判りにくかったので訂正させてください

質問

●(1)表、(1)グラフのようなものを作る方法を教えて下さい。
　自力では、(2)表位までしか出来ず
　睡眠時間や合計時間が正確に出せません。

以下は自力の式です
F1　=IF(F37-E37+D37-C37>0,F37-E37+D37-C37,"")
G1　=IF(C38="","",IF(F37>=24,C38+24-F37+E37-D37,C38-F37+E37-D37))
H1　=IF(OR(F37="",C38=""),"",G37+H37)

お手間を取らせてしまい申し訳ございません。

------------------------------------------------------------
●(1)表のように各時間を**:**のように、"2桁:2桁"の時間で表示したい

●"シリアル値"の理解が私には難しそうであれば
　最悪、(2)表のように数字でもよいのですが正確な数式にしたい

------------------------------------------------------------
例え間違っている自力の式でも、同じものは2度と作れそうにないほど未熟です。
なんとなく合ってる所もあるのが不思議な位です。

用いている数式は、もちろん全否定でも結構ですので
どなたか、こんな私でもわかるように教えてくださいませ。

1分でも早く、作りたい状況です
何卒、よろしくお願い致します。m(_ _)m

回答No.6

Nouble · ベストアンサー

多くのデータを一元的に可視化するなら尚更です

一，まず何を見せるか、何を見せたいのかを決める必要があるでしょう

二、次に基軸となる単位を決める必要があるでしょう
　　この場合は時間なのでしょうか

三，そして多くのデータがある中で
　　基軸単位のどの値でそれらを括って可視化するかを
　　見定める必要があるでしょう

直接お話しできないのが歯がゆいですが
これらが決まればどの様なグラフにすべきか適切なアドバイスが出来そうな気がします

取り敢えずこの点について補足をお願い頂けますでしょうか?

ｐｓ
先に示したやり方を応用しても可能だと思いますよ
あれは２４時間単位で表示させるものですが
就寝時間と活動時間をそれぞれ一括りとするサイクル中に必要な分だけ増やします
その上で発生しなかったものは当然時間が０時間０分０秒だと解釈すればいいのではないですか?

因みに積み上げ棒グラフでの完成予想例はこうです

　　■　□　□　■　□
　　■　□　□　■　■
　　■　■　□　□　■　　　□活動中
　　□　■　□　□　■　　　■睡眠
　　□　■　■　□　□
　　□　□　■　□　□
　　□　□　■　■　□
　　■　□　□　■　□
　　■　□　□　■　■
　　■　■　□　□　■
　　□　■　□　□　■
　　□　■　■　□　□
　　1/1 1/2 1/3 1/4 1/5 …

こうすれば単位周期の中で
起床と就寝のタイミングに
スパイラル状のズレが発生していることを
直感的に視認できると思いますよ

此に活動時間の折れ線グラフを右軸で追加すればいい訳ですよね
多分

お礼

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

Nouble様には大変感謝いたしております。

やっと時間が出来たと思いきや、またしばらく無理そうになってしまいました...

上記の積み上げ棒グラフの図は、私にもとても解りやすいです！
私の使っている下記のソフトにも共通しております。
http://www.nasu0123.gozaru.jp/hypnos/hypnos01.html
http://www.nasu0123.gozaru.jp/hypnos/hypnos02.html

もう少し、頭を整理し直さねばならないようですが
Nouble様のご解答は、大変参考になりました。

本当にどうもありがとうございます。m(_ _)m

回答No.5

Nouble

失礼しました
書き上げてから理解が及びました
２７時間周期で活動している人が
この地球自転間隔の環境下で
どのような就寝と起床を繰り返しているか
可視化したいのですね

折れ線では繰り上がったタイミングの前後も繋がざるを得ないので
やはり不向きでしょう

なので積み上げ２４時間棒グラフで書きましょう

更に
２４時間以内に起床より先に就寝が来たり来なかったりとまちまちなので
数値表の起床と就寝は記載を諦めましょう

その上でデータページに
起床の記載列と就寝の記載列を儲けてデータを記入します

ここからがミソですが
積み上げ棒グラフにする際
睡眠時間帯と活動時間帯は色分けしたいですよね

そこで一工夫してどういう可能性があるか分析します

結論から先に言うと４パターンになります

まず０時が睡眠から入り
その後の２４時間以内に起床１回のみが入るパターンＡ

その逆の
０時が活動中から入り
その後の２４時間以内に就寝１回のみが入るパターンＢ

０時が睡眠から入り
その後の２４時間以内に起床１回と就寝１回のみが入るパターンＣ

０時が活動中から入り
その後の２４時間以内に就寝１回と起床１回のみが入るパターンＤ

これを数値化します

まず基準を決めます
一日の開始が便宜上睡眠から入るとします

データページの基軸はこうです

　　睡眠時間　活動時間　睡眠時間　活動時間
Ａ　　ｍ　　　　ｎ　　　　０　　　　０
Ｂ　　０　　　　ｎ　　　　ｍ　　　　０
Ｃ　　ｍ　　　　ｎ　　　　ｍ′　　　０
Ｄ　　０　　　　ｎ　　　　ｍ　　　　ｎ′
(単位はｈ)

これを積み上げ棒グラフにするのです

こうしておけば活動時間は橙就寝中は紺色と
色分けできるはずです

元々２４時間の中に２７時間を入れ込もうとする行為
これ自体に矛盾があるから無理が生じるのだと思います

なので発想を逆転して
２４時間の内で考えるのではなく
「活動パターン中で一日が何処に来るか」
にするのです

どうしても計算式ができないなら作りますが
ここまで解説すればピンとこられたと思うので
後は頑張ってくださいね

まあヒントとしては
２７時間の周期表と
２４時間の周期表を並べてズレ…

て、とこですか

お礼

実は、gooより連絡がありました。

皆様方への補足内に設けたリンクを、規約により削除したとの事です。

なるべく解りやすく直したつもりだったのですが
ご覧いただけなくて残念です。

もし、文面だけでご理解頂けるようであれば
よろしくお願い致します。

補足

Nouble様

ここまで親切に教えて頂き、大変感銘を受けております。
頭が下がる思いでいっぱいです。
本当にどうもありがとうございます。m(_ _)m

本日、先回答者様へ順に補足しており今に至ります。
自分でも、あまりに解りにくいので、再度まとめた際に先補足にて触れたのですが

実は、体内時計のくだりは、説明の為に設けた設定であり
実際は、起床1、就寝1、起床2、就寝2のようなものを数十個必要としています。

また、その数十パターンの全てで、不定期に存在しない箇所もあります。
その全ての状況の各周期の状況を、一覧にて表したいのですが

その各周期の中には、実際には、最大で100時間を超える周期のものもあるのです。
体内時計0から100時間位の、たくさんの人の生活の中の、数十のシーンを一覧に表したいような感じです。
うまく説明できませんが、他にもいくらかの縛りみたいなものも設けなくてはなりません...

しかし、決してたいしたことをしたいわけではありません
なにせ、エクセルのシリアル値さえ理解できてない私には、そんなんは夢のまた夢です。

Nouble様の、積み上げ棒グラフのアイデアは
私などでは、とうてい考えも及びませんし
ハッキリ言いますが、正直、何度読んでみましても
なんとなくでしか、理解できない頭しか持ち合わせておりません。

本当に、お恥ずかしい限りです...

もし、よろしければ
お時間があるときにでも
下記に、簡単にした感じの補足を記しておきましたので
アドバイス頂けましたら、幸いです。

貴重なお時間を拝借致してしまい恐縮の極みです。
どうもありがとうございました。

回答No.4

Nouble

２７時間のサイクルタイムを
２４時間軸単純計算で規定して
折れ線で表現しようとするから
無理が来るのでは？

実験結果の傾向が解らないので
サイクルタイムで規定可能なのかが不明ですが

例えば
０時からの睡眠時間の上に活動時間
そのまた上に２４時までの睡眠時間を積み上げ
もし翌日分活動サイクルが
当日２４時以前にずれ込むときは
それも２４時分まで積み上げて
１日の活動タイミング積み上げ棒グラフを作り
逆に活動サイクル時間を折れ線にする

とか

地球自転時間とのズレを強調したいなら
いっそのこと１サイクルタイムごと
睡眠時間１回と活動時間１回の１セットのみを積み上げて
積み上げ棒グラフを作り
２４時間の値に線を引いて差を強調しておいて
サイクルタイムの表記入は不要とする

とか
…

作るターゲットグラフを決めてから
それに必要なデータ計算をしては如何でしょうか

回答No.3

Wendy02

こんばんは。

あまり回答するつもりはなかったけれども、少し書かせていただきます。男性は関係ないけれども、「着床」って意味が違います。そんなに頻繁には起こりません。たぶん、起床の反対の、「就寝」(go to bed/bedtime)ことだと思います。

>F1　=IF(F37-E37+D37-C37>0,F37-E37+D37-C37,"")
>G1　=IF(C38="","",IF(F37>=24,C38+24-F37+E37-D37,C38-F37+E37-D37))
>H1　=IF(OR(F37="",C38=""),"",G37+H37)

ずいぶん離れているようですが、以下のようにしたらどうでしょうか。

活動時間
=IF(COUNT(B37:C37)<2,"",C37-B37+E37-D37)

睡眠時間
=IF(COUNT(D37:E37)<2,"",D37-C37+B38+(B38=0)+1-E37)

合計
=IF(COUNT(F37:G37)<2,"",F37+G37)

棒グラフは、データを選択して追加していけば、横に着きます。

補足

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

解り難くて本当に申し訳ございません。m(_ _)m
正直、まだ自信はありませんが、下記が
現段階での精一杯でございます。

もし、よろしければアドバイス頂きたいです。
よろしくお願い致します。

------------------------------------------------------------
着床にそんな意味があったなんて...
たしかに、聞いた記憶がございました
語呂がいいんで使ってましたが、お恥ずかしい限りです。

なお、せっかく各関数式を教えていただいたのですが
元の質問の表でのセル表記がおかしかったのか
私には再現できませんでした。

わかりやすいのでは、と思い
エクセルの縦と横の灰色の部分を作ったのが仇となってしまったのか
そこらへんのちょうせいや、37行目にちゃんと合わせたりもしたのですが、うまく出来ませんでした。

めちゃめちゃ気になります。
出来ましたら、上記リンクでの関数式をアドバイス頂きたいと思っています。

よろしかったら、お時間があるときにでも
何卒よろしくお願い致します。m(_ _)m

回答No.2

hallo-2007

こんばんは（夜分ですが）
何度見ても表が理解できません。
なぜ、起床と着床が2度あるのですか？
例えば、C2の1月1日の27時とは何時のことでしょうか。1月2日の3時の事？
H列の合計時間は必ず27時間になるのでしょうか？
どのような集計のデータから何をグラフにしたいのか、その意図を説明してもらった方が良いと思います。

補足

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

解り難くて本当に申し訳ございません。m(_ _)m
正直、まだ自信はありませんが、下記が
現段階での精一杯でございます。

もし、よろしければアドバイス頂きたいです。
よろしくお願い致します。

------------------------------------------------------------
＞なぜ、起床と着床が2度あるのですか？
昼寝をしたとお考えくださいませ。

＞例えば、C2の1月1日の27時とは何時のことでしょうか。1月2日の3時の事？
はい、その通りです。
解り難くてすみません。(^_^;

＞H列の合計時間は必ず27時間になるのでしょうか？
長時間をかけて27時間周期を収束させる様を表したいと思っておりますが、起床1時間が24時を過ぎた起算日の起床時間を0時としたいと思っておりますので、その日が来るまでは、最大48時間？までの表記があるかもしれません。

詳しくは、上記リンクをご覧頂けましたら幸いです。

回答No.1

nori_007

画像が小さいため、何を行いたいのか良く判りません。
行いたい事を、文章で説明出来るように整理して再度質問した方が、アドバイスを頂けるのではないでしょうか。

補足

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

大変解りにくくて申し訳、ございませんでした。
正直、自分の中ではハッキリ解っているのですがうまく伝えられない状況です...

しかし、そうも言ってられ無い状況ですので
アドバイス等頂けましたら幸いです。

何卒、よろしくお願い致します。m(_ _)m

------------------------------------------------------------
●(1)表：自力での失敗作：現段階の限界…
・活動時間：　IF(E2-D2+C2-B2>0,E2-D2+C2-B2,"")
・睡眠時間：　IF(B3="","",IF(E2>=24,B3+24-E2+D2-C2,B3-E2+D2-C2))
・合計時間：　IF(OR(E2="",B3=""),"",F2+G2)

●理想　(体内時計の例は、説明の為に用いています)

・各起床、各就寝は毎日変動しますので、関数で求めたい活動、睡眠、合計の各時間も毎日変動しますが
　長時間経過と共に周期を収束させる様を表現したい

・起床時間が24:00を過ぎた日から00:00に戻さなければならないルール（みたいなもの）がある

・いずれ24時間以内での周期が安定した場合の縦軸は24時間以内に戻したいが
　現状は、たとえ日付が変わっても、周期内の活動、睡眠、合計時間はすべて同一行で表したいので24時間以上の表記方法を用いたい

・まだ値が解らないセルは、空白のままにしておきたい

・特に難しい事をやりたいとは思っておりませんので、理想としては上表のように出来るだけ簡素なものしたい
　(1)表でのうまくいかない点は、黄色セル内の数字を思い通りに出せない事です

・欲を言えば、(1)表のように数字で時間を表すのではなく、**：**のように正式な時間表記にしたいが
　"24時イコール0時"のルールのもとに成り立つ時計表記を
　"何時を過ぎたら0時になる"とは決められない状況の中で、用いるのは難しいのかなぁと思っている
　60分ごとに1時間増えるのは変わらなくても、例えば38：24なんてExcelで扱えるのかは自力では限りなく不可能…な現状

・シリアル値での時間の差の出し方さえわからない程の未熟者ですのが、グラフの縦棒を隣同士で並べる方法は理解していますので
　(1)表の活動、睡眠、合計時間の関数式がわかるだけでもありがたいです