- ベストアンサー
秒以下の時間の集計方法と書式設定について
- 秒以下を含む時間の集計を行いたい場合、Excelでは秒以下の書式設定ができません。60進数で時間を表記する場合、4分23秒32は04:23:32となります。しかし、Excelではこれを時間の集計として認識することができません。
- したがって、秒以下の時間の集計を行いたい場合、数式を用いて計算する必要があります。例えば、A1セルに04:23:32と入力し、A2セルに同じような時間形式でデータを入力すると、B2セルには=A1+A2という数式を入力することで合計時間を表示することができます。ただし、60分を超える場合は1時間として計算されます。
- 上記の例では、1:42:25:23は1時間42分25秒23、46:24:13は46分24秒13という表記になります。これらの時間の集計には、書式設定や数式を用いる必要がありますが、Excelでは秒以下の書式設定はできないため、数式による計算が必要です。
- NuboChan
- お礼率65% (1335/2027)
- Excel（エクセル）
- 回答数7
- ありがとう数3
- みんなの回答 （7）
- 専門家の回答
質問者が選んだベストアンサー
前提としては > 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フレームです。
その他の回答 (6)
- imogasi
- ベストアンサー率27% (4737/17070)
小生は、理系の実験データなど扱った経験がないので、秒以下のことを（特にエクセルで）深く考えたことがない。 （１）エクセルでは、秒より下の単位がないので、それを表す、何か記号や漢字を自分で作る。ミリ秒［ms〕など常識的ですが。 「秒以下の単位」「秒以下の単位 漢字」で、WEB照会すると面白い。 （２）値としては、１日＝１，１時間＝1/24,１分＝1/(24*60)、１秒＝1/(24*60*60),１０００分の１秒＝１/(24*60*60*1000)として、自分で小数点付の数に直し、計算は普通通りに小数点付の数の計算をして、出た結果に対し 日、時間、分、秒、新単位に直す計算を自分でやる。 エクセルも、背後でこれをやっているのだろう。 （３）秒までの部分（文字列的に）は（それ以下の数を省いた数で）エクセルの機能でFormat関数やText関数で文字列に表せるだろう。それ以下の端数の数は（文字列で）付け足す。 自分自身で、１から考え直さないと行けないということ。 ーー １つ、エクセルの質問者の認識について、 質問者の頭は、「分」から始まっているようだが、エクセルの時刻入力は、時（間）から考えて、半角で00:45:15のように入れないとならない仕組みと思う。 4：30は4:30:00と直されて、４時間３０分のはず。４分３０秒jじゃないはず。 ーー エクセルの日付、時刻に触れるとき、エクセルは非常に面倒なことを引き受けてくれているな、といつも思い、エクセルの基本設計者に思いをいたしている。 ＝＝ この面でエクセルとは別の、ソフトや付加ソフトが（業界・学会・研究者内などでは）あるのではないか。そういうものを使えばよいのでは。全く存在を知らないが。 エクセルは、どちらかというと、個数や金額などを重用する、ビジネス向けのソフトであるように思う。これを本件の課題に使おうとするのが無理があると思う。
- masnoske
- ベストアンサー率35% (67/190)
秒未満は小数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)) なお、: が４つなら"時間"、3つなら"分"で扱うのは : の数を数えれば良いので、 =LEN(A1)-LEN(SUBSTITUTE(A1, ":","")))/LEN(":") A1 に 1:42:25:23 が入力されていれば 3 が返ります。 以上のように、かなり厄介な数式になりますが、そもそも1秒以下を60進数で表示しないといけないケースって何なのでしょうか？
補足
No6さんの解説で 解釈が全く違っていたのを指摘されました。 反省しています。
- bunjii
- ベストアンサー率43% (3589/8249)
>excelには秒以下の書式設定がありません。 >６０進数で 04：23：32 （4分23秒32） その通りです。 >これを秒以下を１０進数で無理やり表示すると > 04:23.53 （４分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進数表示したいのでしたら文字列に変換して表示することになります。
補足
１）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）
- asciiz
- ベストアンサー率70% (6849/9742)
あー、こちらの質問 ＞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ファイルを読み込んだ時点でそれをやっているんでしょうね。
お礼
興味を持って頂き感謝いたします。 実際にビット長を取得して計算してみました。 Excel 0：02：04 PowerDirector 0：02：04：26 ビット長 5440520 ＞ 0：02：16.01 ID3タグが付加されているので誤差が多いようです。 やはり、解説されていたようにexcelでMP3を読み込んで 秒以下を求めるのは無理が有るようなので諦めました。 大変参考になる解説ありがとうございます。
- mdmp2
- ベストアンサー率55% (438/787)
時間の単位には秒の下がありませんので、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 となっていなければ入力した数値が違っています。
お礼
＞時間の単位には秒の下がありませんので、23秒32 は、23.32秒です。 ＞.32 の部分は60進数ではありません。 なぜ、60進数と考えたかと言うと 実際の表示を元に説明したいと思います。 PowerDirector_18のトラックに8本のMP3を読み込みました。 (見やすくするため、HH相当の00：の部分は省略しています。） １）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さんの説明から１0進数と考えると ２）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 この数値は、１）の数値 44：19：05 と合いません。 ---------------------------------------------------------- Excelで60進数で合計すると 44：19：05 になるのかも含めて60進数での計算式を作成したいのですが ？
- Nobu-W
- ベストアンサー率39% (724/1831)
https://xn--eck9awc8j.biz/excel-ms/ ミリ秒を含む時刻の計算方法が記載されてます 読んでみて＾＾
お礼
>解釈が間違っています。これは2分4秒26フレームです。 目からウロコの勘違い。 チコちゃんに叱られた思いです。 解釈が全く違っていたのですね。 PowerDirectorのチャピター位置を設定するので先にJPEGを読み込んで MP3に合わせる形式でしたが、MP3＞Jpegと読み込む順番を逆にして対応する事にしました。 解説いただき大変有り難うございます。