- ベストアンサー
24時間以上の表示
いつもお世話になります。 Excelで勤務実働時間の変換作業を行ってます。 元のデータは"文字列"で表示されてて、"=TIMEVALUE(A1)"を使って 12:1 → 12:01 と表示させてます。 そこで質問ですが、"24:1"以上の場合ですが、"TIMEVALUE"を使うと当然"00:01"に表示されるので、 =IF(TEXT(LEFT(A1,2),"#,###")>="24",TIMEVALUE(A1)+"24:00" と数式を入れました。続いて、同じ様に"IF"を並べて">=96"、">=72"、">=48"と作ったのですが、今度は"100:00"以上の場合です。同じく =IF(TEXT(LEFT(A1,3),"#,###")>="120",TIMEVALUE(A1)+"120:00",IF(TEXT(LEFT(A1,3),"#,###")>="100",TIMEVALUE(A1)+"96:00"を追加してみたのですが、そうすると今度は "100:1"以上はうまく表示できたのですが"36：1"以上"99：59"いかが正しく表示されなくなりました。中身を確認すると"=TEXT"表示に問題があるようです。 長々と説明させていただきましたが、ようするに、3桁～6桁の文字列表示の時刻を"TIMEVALUE"に表示させ、それを元に関数を使ってまた別の計算式を作りたいのです。 解りにくい内容ですみませんがよろしくお願いします。
- takayosi
- お礼率84% (92/109)
- オフィス系ソフト
- 回答数5
- ありがとう数2
- みんなの回答 （5）
- 専門家の回答
質問者が選んだベストアンサー
解答Ｎｏ2です。確かに文字列としてＡ1セルに24：10と入力すれば=TIMEVALUE(A1)での表示は0：10と表示されセルの表示形式を[h]:mmとしても24：10と表示されませんね。そこでいろいろと苦労されているのでしょう。次のようにすることで解決すればよいでしょう。 =DATEVALUE(A1)+TIMEVALUE(A1) もちろんセルの書式設定では[h]:mmのようにします。 これでＡ1セルに文字列として24:10と入力されれば24:10と、37:00と入力されれば37:00と表示されます。もちろんこのようにして変換した時刻は計算に使うことができ、ＳＵＭ関数でも正常に使うことができます。
その他の回答 (4)
- poohron
- ベストアンサー率59% (574/971)
#1です。 再度ご確認いただけないでしょうか？ 私の回答で使用している関数は TIMEVALUE ではなく、VALUE です。 TIMEVALUE 関数を使用した場合は、おっしゃる通り 24:00以上の値を扱うことができずに 24:1 は 00:01 として扱われてしまいますが、 VALUE 関数を使用することにより 24:1 は 24:01 として扱うことができ、 書式設定 [h]:mm により表示も 24:01 となります。 もちろん、SUM関数による計算等も可能です。 （動作確認済み）
お礼
大変、大変遅くなって申し訳ありません。m(--)m 半ば諦めていたもので… ホントです。大きな勘違いをしてました。すみません。 "TIME"が付かないだけでこんなに一瞬でできるのですね。。。私の苦労はなんだったのか… ありがとうございました!!すっきりです!!
- yone_sk
- ベストアンサー率34% (58/167)
これでどうでしょうか？ =TIMEVALUE(A1)+CONCATENATE(24*(ROUNDDOWN(VALUE(MID(A1,1,FIND(":",A1)-1))/24,0)),":00")
補足
大変、大変遅くなって申し訳ありません。m(--)m もう、誰も答えてくれないかと半ば諦めてました。 確かにこちらの数式でもなります!!見たことない関数を利用されてるので今後の参考にもなりました。 ありがとうございました!!
- KURUMITO
- ベストアンサー率42% (1835/4283)
例えばＣ列に時間を表示している場合に24時間以上になる場合の表示を例えば25:30のように表示させるにはＣ列を選択してから右クリックして「セルの書式設定」を選択し、「表示形式」のタブで「ユーザー定義」とし、種類の窓に [h]:mm のように入力すればよいでしょう。 複雑な計算をしているようですがその必要は全くありません。
補足
それでは、ダメなのです。。。。
- poohron
- ベストアンサー率59% (574/971)
使用する関数と書式設定をすることで とても簡単にご希望の表示をすべて実現できます。 まず、式はたったこれだけ。 =VALUE(A1) そうすると、場合分けでの面倒な計算もまったく不要です。 しかしこれでは、やはり24:00以降は正しく表示されないので 書式設定で正しく表示されるように変更します。 セルの書式設定の表示形式からユーザー定義を選択し [h]:mm と設定してみてください。 もちろん、これを元に関数を使ってまた別の計算式を作ることも可能です。
補足
それではだめなのです。 セルの書式設定の表示形式からユーザー定義を選択し [h]:mm に設定してるんです。試してみてください。 セルA1を書式設定の表示形式から"文字列"に設定し、"25:1"と入力し セルB1に書式設定の表示形式から"[h]:mm "に設定し"=VALUE(A1)"を入力してください。"1：01"になりませんか。 回答No.2,KURUMITO様の答えは"=SUM()"等の関数式が使えません。"=A1+A2"なら計算できますが。。 良いお知恵を。。。
関連するQ&A
- エクセルにて負の時間を0:00と表示する方法を教えてください
タイトルのとおり、負の時間になった場合に0:00と表示したいのですが、なかなかうまくいきません。 エクセルにて勤務の遅刻・早退の表を作っています。8:30までにこなかった場合に超過時間を表示し(9:00にきた場合には0:30)、17:00より前に帰った場合に過不足時間を表示、という表を作っています。 http://oshiete1.watch.impress.co.jp/qa4183597.html この過去の質問を参考にやってみましたが・・・うまくいきません； 現在、A1にある元データの数字（例：815)を拾う為に、セルのA3に =IF(A1="","",TEXT(A1,"0!:00")) という数式が入れてあります。 A4のセルには =IF(ISBLANK(A3),"",IF(A3>TIMEVALUE("8:30"),A3-"8:30",0)) という数式が入れてあります。 A4のセルに負の時間であれば0:00と表示、という関数を入れれば出来そうなのですが、試行錯誤してもわかりませんでした。 ご教示いただけたらと思い投稿しました。 宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- 質問：エクセルで1つのセルに2つの時刻表示（再）
以前に質問し回答も頂いたのですが、数字を変えた場合の数式が分かりません。どなたか教えて下さい。 以前の質問は【Ａ１セルに８時００分～１７時３０分というように表示させたい場合、ユーザー定義でｈ""時"ｍｍ"分""～"ｈ"ｍｍ"分"として、セルに、８：００.１７：３０と入力すると８時００分～８時００分と表示されます。どこで間違っていますか？ｗｉｎＸＰエクセル２００３です。】で、回答は【=TEXT(TIMEVALUE(LEFT(A1,FIND(".",A1)-1)),"h時mm分")&"～"&TEXT(TIMEVALUE(MID(A1,FIND(".",A1)+1,5)),"h時mm分")】でした。そこでＧ１に８：００.１８：００とした場合、数式はどうなりますか？全くできませんでしたので、どなたか教えて下さい。
- ベストアンサー
- オフィス系ソフト
- Excelで時間風なテキストデータを10進表示時間数に変換したい
いまA列に、WebやOCRで取り込んだ、時刻データでない一見時間風なテキスト文字列があります。 これをB列に表示のように10進数の時間数値に変換する１個の数式を考えていますが、私の知恵ではすごく長い式になります。 これを最短の数式で現すベスト（又はベター）な方法をお尋ねします。 私が考えたプロセスは、まずB列に設けた時間の9や10を取り出す式（LEFT,FIND等）を皮切りに、順次右の列に分の15や6を取り出す式（MID,FIND）、6に0を加えて「06」とする式、分に10/6を乗じ、四捨五入して小数の時間に変える式、整数と小数の各時間を加算する式などを設け、最終的にその右列に答えを出す。あるいは、これらの式を必要か所に代入して１個の式にまとめる、などやってみましたが、どうしても長蛇の列になってしまいます。 別の方法ではSUBSTITUTE関数を使って「9時間15分」を「9.15」に置き換えて数値化し、その上で10進数に置き換えることも試みましたが、SUBSTITUTEのネスト、分の部分の6を06に変える式、10/6を乗じて四捨五入する式などもあり、これも結構長たらしくなります。 また、データ→区切り位置で分割するのも、例示の場合のように縦に整列されていないと結構面倒です。 このほか小数点一桁と二桁の混在表示という表示形式の問題もあります。 Excelでテキスト、日時、数値各データを相互に変換するのに結構面倒な場合が多いので、サンプルを例題として、表示形式の設定と併せ、関数のみの最短の数式で現す方法について、Excelご精通の方のお知恵を拝借したいと存じます。よろしくお願いします。 A列（テキスト） B列（10進数値の時間） 5時間45分 9.75 ←（5.75時間） 10時間6分 10.1 7時間07分 7.12
- ベストアンサー
- オフィス系ソフト
- データ件数のカウント方法
A列に文字列で以下のようなコードが入っていたとします ｜ Ａ －－－－－－－－－ １ ｜ １０１ ２ ｜ ０１１ ３ ｜ ０１０ ４ ｜ ００１ ５ ｜ ０００ このコードの各桁に１の数をカウントしたいのです。 １桁目 １個 ２桁目 ２個 ３桁目 ３個 Ｂ列、Ｃ列、Ｄ列に = left(A1,1)などでリストアップして その列で集計する方法は分かるのですが 列数が多いのでこれ以上増やしたくありません。（わがままですいません） 配列数式なるものでやればできるような気がするのですがよく分かりません。 ご存知のかたご教授ください。よろしくお願いします
- ベストアンサー
- オフィス系ソフト
- エクセルの入力規制で2つの条件を指定したいです
2つの条件とは・・・ 7桁で、かつ文字列にＯ(ローマ字のオー)以外が入力されている場合ＯＫ。それ以外の場合はエラーを返したいです。 今わかっている数式は 入力規則の条件を、「ユーザー定義」 数式を =AND(LEN(A1)＝7,LEFT(A1,1)<>"O") です。 ですが、これだと7桁かつ左の文字がＯの場合のみエラーになると言う意味になってしまうので、LEFTを何に変えればよいのでしょか。 どうか、宜しくお願いします。
- ベストアンサー
- その他（ビジネス・キャリア）
- Excelで、「+○,○○○」という表示を、数値として表示させるには？
Excelで、「+○,○○○」という表示を、数値として表示させるには？ 私の質問の書き方が悪かったので、改めて質問をさせて頂きます。 前回、お答えくださった方々には、この場を借りてお詫び申し上げます。 さて、本題ですが、 Sheet1のA1セルに、 Sheet2のA1セルに手入力した、「数字の前に + あるいは - が付く数値」を、桁区切りのカンマ付きで表示させています。 + を表示させるために、参照元のセルの書式を「文字列」にしています。 参照先のSheet1のA1セルの隣のA2セルに入っている数値を、 Sheet1のA1セルが -○,○○○ の場合には赤字で、 Sheet1のA1セルが +○,○○○ の場合には黒字で表示させたくて、条件付き書式で [=$A$1<0]という数式を入れてみたものの、 参照元が「文字列」なので、どうやら参照先も文字列と判断されてしまうらしく、条件付き書式が機能してくれません。（参照元を「標準」等に直せば機能します） +○,○○○という表示を、数値として表示させることができれば、解決すると思うのですが、他に方法があればそれにはこだわりません。 なにか良い案はないでしょうか? お知恵を拝借させてください。
- ベストアンサー
- その他MS Office製品
- エクセル2007で時間を足したり引いたりできます？
エクセル2007を使っている者です。 A1、A2のセルに文字列でそれぞれ3時間45分、1時間30分と入っていたとして、その２つを足したり、引いたりすることはできますか？ TIMEVALUE関数で文字列を時刻に直した後でセルの書式設定で時刻表示にすればできるというようなことが書いてあるページがあったのでそれをやろうとしたのですが、うまくいきませんでした。 まずA1のセルを時間に直そうとして「=TIMEVALUE(A1)」と入れたのですが、#VALUEとのエラーが出てしまいます。 どなたかお願いいたします。
- ベストアンサー
- オフィス系ソフト
- Excel2000のセル内の文字列の数字の先の２桁を取り出す方法について
年のいった初心者です。 以前の質問に対する回答有難うございます。また、教えてください。 エクセル2000です。セル内に数字の文字列で、４桁、６桁、８桁のものが有ります。４桁のものには"01"を、６桁のものには"60"を、表示し、８桁のものには数種類あり、８桁の場合は、各文字列で上位の２桁を表示したいのです。 =IF(LEN(A1)=4,"01",IF(LEN(A1)=6,"60",IF(LEN(A1)=8,?????? ４桁、６桁については以前に教えていただいた、関数等でいけたのですが、８桁のときが分かりません。困っています。お願いします。 A列 B列 1 7532 →→ 01 2 280573 →→ 60 3 34964821 →→ 34 4 06839775 →→ 06 5 67500023 →→ 67
- ベストアンサー
- オフィス系ソフト
- タイムカードから就業時間計算
Excel2000でタイムカードの出勤・退出時刻を入力して、就業時間を出そうとしています。 定時は8:30～17:15、休憩は12:00～12:50、14:50～15:00、17:15～17:30です。 http://www.h3.dion.ne.jp/~sakatsu/WorkingTimeCalc.htm ↑このサイトを参考に、 A列に出勤時刻(手入力)、 B列に退出時刻(手入力)、 C列に24時補正、 =IF(OR(A2="",B2=""),"",IF(A2<B2,B2,B2+1)) D列に深夜1(0:00～8:30)、 =IF(OR(A2="",C2=""),"",IF(AND(A2<TIMEVALUE("8:30"),C2>=TIMEVALUE("0:00")),MIN(C2,TIMEVALUE("8:30"))-MAX(A2,TIMEVALUE("0:00")),0)) E列に午前(8:30～12:00)、 =IF(OR(A2="",C2=""),"",IF(AND(A2<TIMEVALUE("12:00"),C2>=TIMEVALUE("8:30")),MIN(C2,TIMEVALUE("12:00"))-MAX(A2,TIMEVALUE("8:30")),0)) …の要領で、F列に午後1(12:50～14:50)、G列に午後2(15:00～17:15)、H列に夜(17:30～24:00)の式をそれぞれ入力し、I列でD～H列を合計しています。=SUM(D2:H2) このうち午前・午後1・午後2は正確に表示されるのですが、深夜・夜が0:00のままで表示されません。(退出を夜or深夜にしても0:00のままです) 午前・午後と同じような式を使っているのにどうしてなのでしょうか。どうすれば正確に出るのでしょうか。 セルの書式設定はユーザー定義で[h]:mmにしています。 今日午後から連休明けまではここを覗けないのでお礼ができませんが、よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 同じ文字列が３個以上ある場合に「３個以上」と表示するには？
Ａ列にある文字列の中で同じ文字列が３個以上ある場合に「３個以上」と表示するにはどうしたらよろしいでしょうか。 Ａ列 Ｂ列 1Ｂ０Ｔ５ ３個以上 ２ＧｈＹ 1Ｂ０Ｔ５ ３個以上 1Ｂ０Ｔ５ ３個以上 ＪＹｔｇＨ 1Ｂ０Ｔ５ ３個以上
- ベストアンサー
- オフィス系ソフト
お礼
大変、大変遅くなって申し訳ありません。m(--)m もう、誰も答えてくれないかと半ば諦めてました。他の作業もありそちらに気をとられながら、また時間作業に戻って"教えてgoo"を確認してみたら!!皆さんちゃんともう一度回答をくださってて感謝感謝です。 すごく簡単で解りやすい数式で感動しました。 ありがとうございました!!