7 Replies Latest reply on Feb 23, 2018 6:45 PM by LM Seven

    [解答 データ準備編(頭の体操)]  時間のグラフ=エクセル複製

    Shinichiro Murakami

      もんだい:

      [チャレンジ(頭の体操)]  時間のグラフ=エクセル複製

      *********************************************************************************************************************

      さてと、正直なこというと、

      これ、地雷踏んだわ~

       

      時間の計算は厄介ですね。自分のファイルでは全く問題なかったので、「1秒ずれる」って話はまあ、『実力の差かななんて

      タカをくくっていたわけですよ。ええ。

       

      ところが、頂いたいくつかのシートを見てみるとですね。。

      全く同じ計算式が入ってるのに、ワークブックによって答えが違う訳ですよ

       

      いや、ありえへんやろ~と。

      でですね。原因追求、まるまる1週間ですよ。

      やってもたわ~。降参。。。orz

       

       

       

       

       

      って訳にはいかないので、やりましたよ。検証を。全く想定外の作業ですよ。ホントに。。。とんだ大冒険だわ。

       

      どうでもよかったはずなのに、またもや解答編を2回に分けるハメに。。。

       

       

      ずれてた人のファイル

      もとのファイル

       

      時刻の認識がもともとずれてる訳だから、これじゃ、合うわけないやろと。。。。

      ただし、時刻表記は両方とも、秒単位まで違いはありません。

       

      ********************************************************************************************

       

      まずは、エクセル側のロジック検証。

       

      時刻を手打ち

       

       

       

      タブロー側も同じく時刻を手打ち

       

      スタート日付が違うのは気になりますが、小数点以下に差異はなさそう。。。

       

      **********************************************************************************************************

      ってことで、次は、エクセル側、変化を極少にして、どこで秒が変わるかチェック。

       

       

      見てもらったら分かるとおり、秒単位になるように 24(hour)x60(minute)x60 (second) を掛け算したあと、

      小数点(ミリ秒以下)を四捨五入しているようにみえます。

       

      なので、これをタブロー側にも導入。

       

       

       

       

      再度、比較。。。

       

      合ったみたい。。

       

      ちなみに、なんでずれるんや?に関してはまあ、愚問っちゃあ愚問で、

      エクセルのバージョンの違い、OSの違い、タブローのバージョンの違い、ファイルにコネクトしたかコピペしたか、などなど

      考えられることは山ほどあります。そこはあんま問題ではなく、

      「秒」単位まで同じでも、「ミリ秒」単位での違いがあれば、引き算したときの値には違いが出ますよ。と

      そんで、一般的な話として、見えてる数値を使って引き算する場合は、最初から単位を合わせましょう。

      (今回はミリ秒以下は四捨五入)というところからの(想定外の)スタートポイント確認でした。

      単位を合わせるjのは、平常の数字では問題ないですが、時間の計算は厄介さを内包してますからね。

      データまで準備しているのに、こんなところでつまづくとは。。。。

       

      ってことで、本題に一切触れずに、本日は終了!!!

      (添付ファイルは今はエクセルのみ。)

      魂が抜けた人のイラスト(男性)

       

      Enjoy Tableau !

       

      Shin

      Shinichiro Murakami

        • 1. Re: [解答編 Part1(頭の体操)]  時間のグラフ=エクセル複製
          LM Seven

          LM-7です。

           

          なんと申しますか、お疲れ様です。

          さすがShinさん、上手いところを絶妙に付く問題作るなーって思っていたのですが、想定外でしたか。

          jirai_tanchiki_man.png

          ……時間の精度問題は厄介ですね。

          • 2. Re: [解答編 Part1(頭の体操)]  時間のグラフ=エクセル複製
            Yoshihito Kimura

            お疲れ様です

            今回、時間がなくて参加できませんでした。残念。

            (実際のところ、floatをつかったりして、少し挑戦はしてみたのですが、上手くいかずに保留にしてました)

            手間のかかる問題を・・・と思ってましたが、出題された村上さんの方も大変な事になってみたいですね。

            お疲れさまでした。何より参加者が増えていい感じですね!

             

            p.s.読みました。いつもながら凄いですねー。

            Community Digest - February 20th, 2018

            • 3. Re: [解答編 Part1(頭の体操)]  時間のグラフ=エクセル複製
              Shinichiro Murakami

              LM-7さん

               

              半分想定外ってとこですかね。

              ちゃんと計算式作らないと、ある程度ズレが出るのは分かってたのですが、それ以外にも要因があるとは。。

               

              っていうか、本質的には

               

              0.4+0.4+0.4 = 1.2 と  0+0+0=1

               

              が同じものを指しているって単純なことです。これが時間表示だと非常に分かりにくくなるってことですね。

              加えて、日付時刻に関しては、コピペとか、もとの表示形式、データタイプとかによって、実際の数字自身も変わってしまう可能性がある、

              という厄介な問題を抱えています。

               

              けっこう慎重に出題したつもりだったんですけどね。

              大学入試の出題にも不備があったとか話題になってますけど、問題作るのも、ムズカシイネ。

               

               

              Shin

              • 4. Re: [解答編 Part1(頭の体操)]  時間のグラフ=エクセル複製
                Shinichiro Murakami

                「保留」!!!

                そんな選択肢があったとは。。。

                 

                Shin

                • 5. Re: [解答 データ準備編(頭の体操)]  時間のグラフ=エクセル複製
                  LM Seven

                  時間ができたので、おそらくShinさんが気づいていない点を補足しておきます。

                   

                  ①ExcelにはStartはミリ秒以下が無いですがEndがミリ秒以下があるので、それをどの形式で読むかがポイントになります。

                  両方共日付+時刻で読んでみましょうか。

                  2018-02-24_10h31_55.png

                  ②Endがどの精度で読まれているかを確認するために、「文字列」に変えて置いてみます。

                  ミリ秒以下が付与されていることがわかります。日付+時刻読み込みをするとミリ秒精度となることがわかります。

                  2018-02-24_10h33_55.png

                  ③では、読むときに文字列形式で読み込んでみましょうか。再度新規のワークブックを開き、読み込むところから

                  やり直します(開き直しは必須です。やってみるとわかりますが)。そして両方共文字列で読んでみます。

                  2018-02-24_10h35_00.png

                  ④Endの精度を確認してみましょう。ミリ秒以下はありません。

                  文字列読み込みにするとExcelの表示形式に準じた精度で読み込まれることがわかります(今は秒精度)。

                  2018-02-24_10h35_35.png

                  なので、今回は両方共文字列で読み込むと所望の結果が得られることがわかります。

                  ⑤ datediffを利用するには両方共文字列では困るので、日付+時刻に変えます。

                  ここで日付+時刻に変えるのなら読み込むときから日付+時刻でいいんじゃないかと思いがちですが、

                  読み込みの時点で精度が決まるんですね。シートに移動してから型を変えても読み込み時の精度は変わりません。

                  2018-02-24_10h57_56.png

                  で、ここからがおそらく英語環境を利用されているShinさんが気づいていない罠。

                  ……と思ったんですが、どーやっても再現しない!なぜ????

                   

                  mokuhyou_mitatsu_man.png

                   

                  日本語ロケールだと例えば2/6/2018が2018-06-02に読み込まれることがあって、diffの値が幾つかの項目で異なるという現象が

                  あったのですが、今やってみると再現しませんね。USロケールに変えることで回避はできたのですが。

                   

                  ということで、Shinさんが気づいていないだろう罠を説明するために長々と書いてきましたが、

                  本題が再現しないという体たらくで。でもせっかくここまで書いたので投稿はしておきます…

                  • 6. Re: [解答 データ準備編(頭の体操)]  時間のグラフ=エクセル複製
                    Shinichiro Murakami

                    (失礼な言い方ですが)なんだか分からないけど、ありがとうございます。

                    Localeとか、Universal DateTimeとか出てくると、ちょっと別次元になりそうですね。

                    「今日はこれくらいにしといたろ(震え声)」で逃げないといけなくなってきます。。

                     

                    で、時刻精度ですが、すでに説明した内容で、

                    「Datetimeにした時の表示が秒単位で同じである限り、問題は起こらない」

                    と認識しているのですが、違っていたら、教えてください。

                     

                    っていてもこれ以上、深堀りするつもりもありませんが。

                     

                    いずれにしても、おつきあい頂きありがとうございます。

                     

                    Shin

                    • 7. Re: [解答 データ準備編(頭の体操)]  時間のグラフ=エクセル複製
                      LM Seven

                      当方も何が原因であんなことが起こったんだか…

                      再現を確認してから書き始めれば良かったとorz。

                       

                      DataTimeにした時の秒単位が同じである限り問題がおきないのはそのとおりだと思います。