[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『「時間計算の総合計算出」』（トラ）
勤務時間の合計をだしています。
=TEXT(SUMPRODUCT(--SUBSTITUTE(TEXT(B5+D5,"0.00"),".",":")*1),"[h].mm")*1
を利用して離れたセルの合計をだしたいのですが、
B5 C D5
5 ◎ 11.30 1.45 =13.15
6 × 111.30 1.45 =#VALUE!
になってしますのですが、どこを修正したら計算できるようになりますか？
＜ 使用 Excel：Excel2007、使用 OS：unknown ＞
1.45 が 翌1時45分なら 14時間15分
だと思うんですけど、13.15ってなんの数字でしょうか、、
普通に時刻として入力して引き算ではだめですか？
（TAKA） 2018/09/07(金) 11:53
=TEXT(SUBSTITUTE(TEXT(B5,"0.00"),".",":")+SUBSTITUTE(TEXT(D5,"0.00"),".",":"),"[h].mm")*1 （GobGob） 2018/09/07(金) 11:59
現状の入力は変えないとして、こんな式ではいかがでしょう？（数値にするなら *1）
=TEXT(SUBSTITUTE(TEXT(B5,"0.00"),".",":")+SUBSTITUTE(TEXT(D5,"0.00"),".",":"),"[h]:mm") （???） 2018/09/07(金) 12:01
時 24時越え、 分 60以上
だと、数値計算してくれないみたいだね。
あと、 --数式*1 って*1の意味あるの？ （GobGob） 2018/09/07(金) 12:04
=TEXT((INT(B5+D5)*60+MOD(B5+D5,1)*100)/1440,"[h].mm")*1 でもいいかも （GobGob） 2018/09/07(金) 12:14
=TEXT(TEXT(B5*100,"0!:00")+TEXT(D5*100,"0!:00"),"[h].mm")*1
とか （笑） 2018/09/07(金) 12:52
Webで見て良い案だな、と思ったのは、ピリオドを2回連続するとコロンに置換するという方法でした。 テンキーで打てるし、文字の形も似ているから覚えやすいです。
（???） 2018/09/07(金) 12:53
確認です。
結果はシリアル値ではなく、「13.15」のような見た目通りの小数にしたい、 ということでいいですか？
結果を時間計算に使うのならシリアル値にした方がいいです。
=TEXT(B5*100,"0!:00")+TEXT(D5*100,"0!:00")
表示形式〜ユーザー定義 [h].mm
以上 （笑） 2018/09/07(金) 13:03
もうひとつ確認
合計したいのは、本当に2つのセルだけ？
もっと数が多いのなら、実際の範囲を書きましょう。 また、間に挟まれたセル（例ならC5とか）には何が入力されていますか？
以上 （笑） 2018/09/07(金) 13:40
なかなか思うようにいきません。。
オプションの「文書校正」で別の入力をコロンに置換えは、まだしていないので、やってみます。
結果はシリアル値ではなく、「13.15」のような見た目通りの小数にしたい、
ということです。
Cには金額が入ります。
実際の範囲は1月〜12月の12ヶ月分なので、実際の範囲は24列で
時間・金額・時間・金額と並ぶので24列中12列の計算になります。
表現と説明が不足していてすみません。
みなさん、ありがとうございます。
（トラ） 2018/09/07(金) 15:41
. であらわすなら、10進法だと簡単なんですけどね。。 単純に足し算するだけで済みますし、
=INT(対象)+(対象-INT(対象))*60/100
で60進法に戻すことも出来ます。
E2 : =B2+D2
F2 : =INT(E2)+(E2-INT(E2))*60/100
B C D E F 11.5 1.75 13.25 13.15 111.5 1.75 113.25 113.15
みたいな感じです。
（TAKA） 2018/09/07(金) 15:52
関数苦手でよくわからない私なら、いっそ時間と分を分けちゃうかもしれないです。 列も増えるし、他の方の数式のほうが圧倒的にスマートですが、
H2 : =B2+E2+INT((C2+F2)/60) I2 : =MOD(C2+F2,60)
B C D E F G H I 11 30 1 45 13 15 111 30 1 45 113 15
で計算式はこんな感じで。。。
（TAKA） 2018/09/07(金) 16:10
でも24列のデータが36列になったら嫌ですよね。 ごめんなさい、忘れてください。 （TAKA） 2018/09/07(金) 16:13
＞時間・金額・時間・金額と並ぶので24列中12列の計算になります。
B列から始まってるんですか？ B列〜X列だとすれば
=IF(B5="","",TEXT(SUM(IF(MOD(COLUMN(B5:X5),2)=0,TEXT(B5:X5*100,"0!:00")*1)),"[h].mm")*1)
Ctrl＋Shift＋Enter で確定、表示形式〜ユーザー定義 0.00
参考まで （笑） 2018/09/07(金) 16:40
今パソコンが手元にないので、また明日試してみます!
（トラ） 2018/09/07(金) 22:19
＞時間の部分だけ計算したいのでB5:X5じゃだめですよね
？？？ B列から1列おきに、時間の列だけ計算してますけど？
ただし、 B列以降の時間を数式で出していて、時間の列に空白文字列「""」があると、 前回の数式はエラーになります。
↓そんな場合の修正版
=IF(B5="","",TEXT(SUM(IF(MOD(COLUMN(B5:X5),2)=0,TEXT((0&B5:X5)*100,"0!:00")*1)),"[h].mm")*1)
~~~~~~~~~
Ctrl＋Shift＋Enter で確定、表示形式〜ユーザー定義 0.00
ところで、金額の合計はどうやって出すつもりですか？ もしかして =C5+E5+G5+I5+K5+M5+O5+Q5+S5+U5+W5+Y5 これ？
だとしたら、見出しはどうなってるんですか？ 4行目は見出しでしょ？
とりあえず以上です （笑） 2018/09/07(金) 23:42
会社のパソコンが今、見られないので金額の合計は
どうやってだしていたかわからないのですが・・・・。。
（トラ） 2018/09/08(土) 09:39
{=IF(G5="","",TEXT(SUM(IF(MOD(COLUMN(G5:AD5),2)=0,TEXT((0&G5:AD5)*100,"0!:00")*1)),"[h].mm")*1)｝
これはどこをなおしたらよいですか？
すみません。。助けてください。
（トラ） 2018/09/11(火) 14:58
＞=IF(G5="","",TEXT(SUM(IF(MOD(COLUMN(G5:AD5),2)=0,TEXT((0&G5:AD5)*100,"0!:00")*1)),"[h].mm")*1) ↑ の式、つまり前回の回答は、時間が偶数列にあることを前提にしています。 偶数・奇数というのは、A列から数えて何番目の列か、ということ。
時間が、B列・D列・F列･･･にあれば、2列目・4列目・6列目･･･なので偶数列。 今回のように、G列・I列・K列･･･にあるのなら、 7列目・9列目・11列目･･･なので奇数列。
■どこを修正するか？
偶数列： …IF(MOD(COLUMN(G5:AD5),2)=0,…
↓
奇数列： …IF(MOD(COLUMN(G5:AD5),2)=1,…
~~
または「=1」を省略して
奇数列： …IF(MOD(COLUMN(G5:AD5),2),…
式を修正したら、Ctrl＋Shift＋Enter で確定
■ついでに 数式を =IF(G5="","",…… で始めているのは、 1月から入力することを前提にしていますが、それでいいんですか？
年の途中から、例えば1〜3月は空白で、4月から入力する、といったこともあるのなら =IF(G5="","",… ↓ =IF(COUNT(G5:AD5)=0,"",…
=IF(COUNT(G5:AD5)=0,"",TEXT(SUM(IF(MOD(COLUMN(G5:AD5),2),TEXT((0&G5:AD5)*100,"0!:00")*1)),"[h].mm")*1)
Ctrl＋Shift＋Enter で確定
以上です （笑） 2018/09/11(火) 16:14
お察しのとおり、月途中からのものもあったので
助かりました!!
関数難しいけど、奥が深くて面白いですね!
（トラ） 2018/09/11(火) 20:22
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.