• ベストアンサー
  • 困ってます

秒以下を含む時間の集計

  • 質問No.9727991
  • 閲覧数103
  • ありがとう数3
  • 回答数7

お礼率 79% (561/702)

秒以下を含む時間の集計を行う場合
excelには秒以下の書式設定がありません。

例えば、秒以下を表す場合、

60進数で
 04:23:32 (4分23秒32)

(これを秒以下を10進数で無理やり表示すると
       04:23.53 (4分23秒と100分の53秒))
このままでは
 4時間23分32秒と認識されてしまします。

-------------------
処理したいことは、

A1セルに04:23:32(4分23秒32)と入力して
以下A列に同じような時間形式でDATAを入力して

BセルにB2=A1+A2のような合計時間を表示するようにしたいのですが
 どのような計算式になりますか ?

60分を超える場合は、1時間とします。

例えば、
 1:42:25:23 (1時間 42分 25秒 23)
 46:24:13   (46分24秒13)

質問者が選んだベストアンサー

  • 回答No.6
  • ベストアンサー

ベストアンサー率 44% (4757/10632)

他カテゴリのカテゴリマスター
前提としては
> 02:04:26  ----->2分4秒26 
> 05:16:27
> 06:08:16
> 06:42:02
> 09:36:13
> 08:42:18
> 03:48:05
> 01:59:18
を全部足すと
> 44:19:05 ---> 実際の集計の数値
になるということですよね。
これは
> 02:04:26  ----->2分4秒26 
の解釈が間違っています。これは2分4秒26フレームです。
エクセルに入力するときは
00:02:04と26の2つのセルに分解して入力して別々に合計を出します。そうすると合計は
00:44:15と125
になりますから,30フレーム=1秒を使えば125フレーム=4秒5フレームです。
お礼コメント
NuboChan

お礼率 79% (561/702)

>解釈が間違っています。これは2分4秒26フレームです。


目からウロコの勘違い。
チコちゃんに叱られた思いです。

解釈が全く違っていたのですね。

PowerDirectorのチャピター位置を設定するので先にJPEGを読み込んで
MP3に合わせる形式でしたが、MP3>Jpegと読み込む順番を逆にして対応する事にしました。

解説いただき大変有り難うございます。
投稿日時:2020/03/27 15:48

その他の回答 (全6件)

  • 回答No.7

ベストアンサー率 27% (4522/16168)

小生は、理系の実験データなど扱った経験がないので、秒以下のことを(特にエクセルで)深く考えたことがない。
(1)エクセルでは、秒より下の単位がないので、それを表す、何か記号や漢字を自分で作る。ミリ秒[ms〕など常識的ですが。
「秒以下の単位」「秒以下の単位 漢字」で、WEB照会すると面白い。
(2)値としては、1日=1,1時間=1/24,1分=1/(24*60)、1秒=1/(24*60*60),1000分の1秒=1/(24*60*60*1000)として、自分で小数点付の数に直し、計算は普通通りに小数点付の数の計算をして、出た結果に対し
日、時間、分、秒、新単位に直す計算を自分でやる。
エクセルも、背後でこれをやっているのだろう。
(3)秒までの部分(文字列的に)は(それ以下の数を省いた数で)エクセルの機能でFormat関数やText関数で文字列に表せるだろう。それ以下の端数の数は(文字列で)付け足す。
 自分自身で、1から考え直さないと行けないということ。
ーー
1つ、エクセルの質問者の認識について、
質問者の頭は、「分」から始まっているようだが、エクセルの時刻入力は、時(間)から考えて、半角で00:45:15のように入れないとならない仕組みと思う。
4:30は4:30:00と直されて、4時間30分のはず。4分30秒jじゃないはず。
ーー
エクセルの日付、時刻に触れるとき、エクセルは非常に面倒なことを引き受けてくれているな、といつも思い、エクセルの基本設計者に思いをいたしている。
==
この面でエクセルとは別の、ソフトや付加ソフトが(業界・学会・研究者内などでは)あるのではないか。そういうものを使えばよいのでは。全く存在を知らないが。
エクセルは、どちらかというと、個数や金額などを重用する、ビジネス向けのソフトであるように思う。これを本件の課題に使おうとするのが無理があると思う。
  • 回答No.5

ベストアンサー率 27% (22/80)

秒未満は小数10進数で扱うのが基本です(なぜなら秒以下には単位がないから)。
Excelは 1:42:25:23 は文字列と認識し、46:24:13 は数値(時刻も数値)と認識します。なので 46:24:13 は 46時間24分13秒であり、46分24秒13(?) と認識させることはできません。
どうしても秒以下を60進数で扱いたいのなら、文字列として入力するしかありません。
当然ですが文字列は計算できないので、1:42:25:23 を 1:42:25 と 23 に分けて数値化してから計算しなければなりません。
また、計算結果は数値で返りますので、1秒未満の部分を60進数に変換して表示することになります。

Excelでは1日が整数1に該当しますので、1秒は 1/24/3600 です。
1秒未満の60進数を10進小数にする =(1秒未満の60進数/60)*(1/24/3600)
1秒未満の10進小数を60進数にする =60*(1秒未満の10進小数/(1/24/3600))

数値を1秒以上と1秒未満に分けるには
1秒未満 =MOD(数値,(1/24/3600))
1秒以上 =数値-MOD(数値,(1/24/3600))

なお、: が4つなら"時間"、3つなら"分"で扱うのは : の数を数えれば良いので、
=LEN(A1)-LEN(SUBSTITUTE(A1, ":","")))/LEN(":")
A1 に 1:42:25:23 が入力されていれば 3 が返ります。

以上のように、かなり厄介な数式になりますが、そもそも1秒以下を60進数で表示しないといけないケースって何なのでしょうか?
補足コメント
NuboChan

お礼率 79% (561/702)

No6さんの解説で
 解釈が全く違っていたのを指摘されました。

反省しています。
投稿日時:2020/03/27 15:51
  • 回答No.4

ベストアンサー率 43% (3489/8052)

>excelには秒以下の書式設定がありません。
>60進数で 04:23:32 (4分23秒32)
その通りです。

>これを秒以下を10進数で無理やり表示すると
> 04:23.53 (4分23秒と100分の53秒)
Excelの表示形式には1秒未満の端数を60進で表示するものはありませんので10進数で表示することになります。
競技で時間の表示も秒未満の端数は10進数で100分の1秒まで表示される習慣のようです。
提示の「4時間23秒32/60」を「4時間23分53/100」と判断する人はいないのではないでしょうか?

>BセルにB2=A1+A2のような合計時間を表示するようにしたいのですがどのような計算式になりますか ?
計算式は「=A1+A2」になります。
B2の表示形式は [h]:mm:ss.00 で良いでしょう。
但し、1秒未満の端数は10進数の100分の1秒まで表示になります。

あなたのポリシーで1秒未満を60進数表示したいのでしたら文字列に変換して表示することになります。
補足コメント
NuboChan

お礼率 79% (561/702)

1)PowerDirectorの集計表示

02:04:26  ----->2分4秒26 
05:16:27
06:08:16
06:42:02
09:36:13
08:42:18
03:48:05
01:59:18
------------
44:19:05 ---> 実際の集計の数値


>あなたのポリシーで1秒未満を60進数表示したいのでしたら文字列に変換して表示することになります。


ポリシーと言うほどの大げさなものではありませんが、
  PowerDirectorで02:04:26と表示されているので60進での表示と単純に考えただけでこの数値が60進なのか?
  はっきりさせたいと思う様になっています。

  (ExcelでMp3を読み込ませて秒以下を求める処理するのは、無理なことは理解しました。)

文字列で構わないので見た目だけでも60進で集計を求められないでしょうか?

  A列に文字列で020426を入力、以下同じく 051627,----------------,015918
  B列にコンバートされて下記のように表示されて

   02:04:26
   05:16:27
   06:08:16
   06:42:02
   09:36:13
   08:42:18
   03:48:05
   01:59:18

 C列にB列を10進数形式(hh.mm.ss.00)に変換した数値を計算
 D列に合計数値を計算
    D2=C2+C1
D3=C2+C1+C3 ----> D2+C3

------
 E列にD列を見た目60進形式で表示する(例 44:19:05)
投稿日時:2020/03/27 14:23
  • 回答No.3

ベストアンサー率 72% (4555/6311)

他カテゴリのカテゴリマスター
あー、こちらの質問

>excelで『MP3の「曲の長さ」を調べる』
https://okwave.jp/qa/q9727959.html

を考えていたら、締め切りになってしまってしまったのでこちらで…。

まずこの質問の主題について。

>excelには秒以下の書式設定がありません。

これは、あります。カスタム書式設定で、「hh:mm:ss.00」としてください。

そしてセル値としても、1秒未満の時刻を保持できます。

しかしここで前の質問になるんですが、GetDetailsOf では、秒単位の長さまでしか、取れないようです。
これは、Windowsエクスプローラで「曲の長さ」を表示したときに、秒単位までしか表示されなく、おそらくその値を取ってきているためだと思います。

そこで、ファイル長とビットレートから計算できないかと思いました。

ファイル長の取得 = FileLen(Target)
ビットレートの取得 = Folder.GetDetailsOf(Folder.ParseName(Target), 28)

ビットレートは「kbps」という単位付きの文字列になるようなので、
例) "320kbps" → kbps置換"320000" → 数値320000(bps) →8で割って40000(バイト毎秒)
のように変換すれば、ファイル長 ÷ バイト毎秒 で秒単位の長さが計算でき、
それを1日の秒数 (24*60*60) で割るとExcelの時刻値になり、
前記の書式で時刻形式表示できるんじゃないかと思いました。

…しかしこれでも、誤差はでます。
MP3ファイルにはID3タグが入っていることが多いですが、そのデータ分も曲長に含んでしまいます。

そしてさらに、CBR(固定ビットレート)エンコードなら単なる誤差で済みますが、VBR(可変ビットレート)エンコードでは、計算値があてにならなくなってきます。

結局こちらもダメかなあと諦めて、参考回答で書かせていただきます…。

----

結局、本当の長さを知るためには、mp3ファイルを頭からお尻まで読み込み、全領域デコードしてみて、その結果○分△秒××、と計測しなければならず、プロパティ一つで取れるものではないと…。

PowerDirectorはフレーム単位まで考慮する動画編集ソフトですから、mp3ファイルを読み込んだ時点でそれをやっているんでしょうね。
お礼コメント
NuboChan

お礼率 79% (561/702)

興味を持って頂き感謝いたします。


実際にビット長を取得して計算してみました。

  
 Excel 0:02:04
 PowerDirector 0:02:04:26
 
 ビット長 5440520 > 0:02:16.01

    ID3タグが付加されているので誤差が多いようです。

やはり、解説されていたようにexcelでMP3を読み込んで
  秒以下を求めるのは無理が有るようなので諦めました。

大変参考になる解説ありがとうございます。
投稿日時:2020/03/27 14:01
  • 回答No.2

ベストアンサー率 56% (386/688)

時間の単位には秒の下がありませんので、23秒32 は、23.32秒です。
.32 の部分は60進数ではありません。
23秒99 に0秒01 を加えると24秒になります。
04:23:32 と入力することは、秒の下の単位がないので、:32 が秒であると解釈され、4時間23分32秒 を入力したことになります。
4分23秒32 を入力するには、04:23.32 と入力します。
足し算は普通に B2=A1+A2 でOKです。
計算結果がおかしいときは、表示形式を yyyy/mm/dd hh:mm:ss.00 にしてみてください。
yyyy が1900, mm が1, dd が0 となっていなければ入力した数値が違っています。
お礼コメント
NuboChan

お礼率 79% (561/702)

>時間の単位には秒の下がありませんので、23秒32 は、23.32秒です。
>.32 の部分は60進数ではありません。

なぜ、60進数と考えたかと言うと
実際の表示を元に説明したいと思います。


PowerDirector_18のトラックに8本のMP3を読み込みました。


  (見やすくするため、HH相当の00:の部分は省略しています。)



1)PowerDirectorの集計表示

02:04:26  ----->2分4秒26 
05:16:27
06:08:16
06:42:02
09:36:13
08:42:18
03:48:05
01:59:18
------------
44:19:05 ---> 実際の集計の数値

mdmp2さんの説明から10進数と考えると

2)Excelの集計
秒以下
02:04 26   
05:16 27
06:08 16
06:42 2
09:36 13
08:42 18
03:48 5
01:59 18
-------------------
44:15 125

125は1秒25となるはず。
 44:15と合計すると44:16.25

この数値は、1)の数値 44:19:05 と合いません。

----------------------------------------------------------
Excelで60進数で合計すると
   44:19:05 になるのかも含めて60進数での計算式を作成したいのですが ?
投稿日時:2020/03/27 13:16
  • 回答No.1

ベストアンサー率 38% (474/1217)

https://xn--eck9awc8j.biz/excel-ms/
ミリ秒を含む時刻の計算方法が記載されてます 読んでみて^^
関連するQ&A

その他の関連するQ&Aをキーワードで探す

ピックアップ

ページ先頭へ