Excel。○時間○分と表示したい場合、どうしたらいいの【time】

Excel。○時間○分と表示したい場合、どうしたらいいの

＜表示形式＞

時間を○時間○分と表示したい場合は、自分で入力してはいけなくて、表示形式を使う必要があります。

A2の時間をC2に○時間○分で表示します。

C2に

=A2

とセル参照の数式を設定したら、セルの書式設定ダイアログボックスを表示します。

分類のユーザー定義から「h”時”mm”分”」を選択して、「h”時間”mm”分”」と修正して、OKボタンをクリックします。

これで、C2を4時間15分と表示することができました。

ちなみに、A3は、直接4時間15分と入力しています。

C3には、

=A3

というようにセル参照しています。

さて、ここから重要なポイントで、C列の時間に＋2時間してみましょう。

D2に

=C2+"2:00"

という数式を設定します。+2hは、単純に+2ではダメです。

また「”（ダブルコーテーション）」で囲う必要があります。

D2は、6時間15分と求めることができましたが、D3のほうは、#VALUE!というエラーが表示されてしまいました。

このエラーは、文字と数値を計算しているというエラーです。

原因は、自分自身で4時間15分と入力してしまったことです。

時間ではなくて、文字扱いになっています。

なお、4時15分ならば、時刻という数値扱いになるので、エラーは表示されません。

表示形式を設定するだけで、○時間○分と表示することもできます。

Excel。ゼロとハイフンがない郵便番号をゼロとハイフン付きに変更したい【post code】

Excel。ゼロとハイフンがない郵便番号をゼロとハイフン付きに変更したい

＜TEXT+LEFT+RIGHT関数＞

CSVなどのデータを読み込んだところ、郵便番号が数値だったため、036ではじまる郵便番号の0がない状態でした。

そこで、先頭の0をつけた郵便番号やハイフンでわけた郵便番号にするには、どうしたらいいのでしょうか。

次のデータを用意しました。 

A列が読み込んだデータです。

A2は0がないので、6桁の数値になっています。

ゼロを先頭につける必要がありますので、B2には、表示形式を設定できる、TEXT関数をつかった数式を設定します。

=TEXT(A2,"0000000")

これで、6桁の数値は、先頭に0（ゼロ）を表示した数値（文字数値型）にすることができました。

では、ハイフンをいれた郵便番号にしたい場合には、次の数式をC2につくってみました。

=LEFT(B2,3) & "-" & RIGHT(B2,4)

B2には、先ほど7桁のゼロ始まりの郵便番号をつくりましたので、左から3文字のあとに-（ハイフン）を挟んで、右から4文字を結合させてみました。

そこで、LEFT関数とRIGHT関数をつかってみました。

どうしても、左から、右からと考えがちですが、LEFT関数やRIHGT関数をつかわなくても、一発でゼロ始まりであり、ハイフンもはさんだ表示にすることができます。

D2には次の数式を設定しました。

=TEXT(A2,"000-0000")

TEXT関数だけで、対応することができます。

たぶん、この方法が一番早いように思えます。

このアイディアは、セルの書式設定ダイアログボックスの表示形式にヒントがあります。

 

表示形式タブの「その他」にある郵便番号を選び、即、ユーザー定義にすると、郵便番号の中身を確認することができます。

すると、

[<=999]000;[<=9999]000-00;000-0000

となっています。

この中に、「000-0000」というのがあります。

つまり、ゼロ始まりでハイフンを入れて表示することができることが確認できます。

ということで、表示形式を設定できるTEXT関数をつかうことで、ゼロ始まりでハイフンをいれた、郵便番号をつくることができたというわけです。

Excel。時間の合計を、○時間○分で表示したい。【Time calculations】

Excel。時間の合計を、○時間○分で表示したい。

＜SUM関数＋表示形式＞

作業工程の予想時間の表があります。

B列の予想時間の単位は「分」です。

B2:B5の合計分数が、B6に算出されています。

B6の数式は、

=SUM(B2:B5)

ただ、結果は「分」なわけです。

このままでもいいのですが、わかりにくいので、○時間○分という表示に変えていきます。

B6をクリックして、セルの書式設定ダイアログボックスを表示します。

セルの書式設定ダイアログボックスは、Ctrl+1というショートカットキーで表示するのが便利です。

セルの書式設定ダイアログボックスが表示されたら、表示形式タブの分類を時刻。

時刻にある「13時30分」を選択してOKボタンをクリックしてみることにします。

なんと、0時間00分と表示されてしまいました。

原因は、B6の値。

日時は、シリアル値で考えなければいけません。

190分と思っていても、190という数値でしかありません。

そこで、時間に直す必要があります。

Excelは1日を1としておりますので、1時間は1/24です。

さらに、分なので、1/24した値から1/60しなければなりません。

まずは、数式を修正しましょう。

=SUM(B2:B5)/24/60

これで、3時10分　と表示されました。

あとは、3時10分を3時間10分に変更するだけです。

改めて、B6をクリックして、セルの書式設定ダイアログボックスを表示しましょう。

表示形式タブの分類「ユーザー定義」をクリックします。

種類を、

h"時間"mm"分";@

と修正します。

「;@」はなくてもOKです。

これで、完成です。

Excelは日付や時間の場合、シリアル値という数値であることを押さえておくと、いいかと思います。

Excel。「分数」の計算もできるし、通分もしてくれます。でも、どうやって分数で表示するの？【Fraction】

Excel。「分数」の計算もできるし、通分もしてくれます。でも、どうやって分数で表示するの？

＜Youtube＞

Excelで分数の計算をすることもできます。

しかも、「通分」までしてくれます。

さらに、手早く分数で表示する方法もご紹介しております。

学校の先生や親御さんも助けられるテクニックなのかも。

Excel。途中計算など文字や数値を非表示にすることができます。【Youtube】

Excel。途中計算など文字や数値を非表示にすることができます。

＜表示形式＞

途中計算など文字や数値を非表示にすることができます。これで、列や行を非表示にしなくても大丈夫です。

途中計算などを表示したまま印刷すると、ちょっとカッコが悪いというか、お客さん相手の資料なら、途中計算の結果を表示することはできませんね。

列や行を非表示にしてもいいのですが、Excelファイルを見せる場合は、列や行を非表示にすると、列や行番号がかけているのがわかります。

つまり、「非表示」にしたことがわかってしまいます。

そこで、表示形式をつかうことで『セル内の文字を非表示』にすることができます。

Excel。数値を全角に、しかも三桁区切りのカンマも全角にしたい。【em】

Excel。数値を全角に、しかも三桁区切りのカンマも全角にしたい。

＜表示形式＞

Excelは、全角で数値を入力してみても、半角に変わってしまいます。

どうしても全角の数値を使いたい場合には、表示形式をつかうことで、全角にすることができます。

ところが、そのあとホームタブにある「,」三桁区切りのボタンを押すと、半角に戻ってしまいます。

数値を全角に、しかも三桁区切りのカンマも全角にしたい場合は、表示形式の「全角桁区切り」をつかうことで、対応することができます。

Excel。通貨スタイルの「￥」は、桁数によって位置がバラバラなので揃えたい。【currency style】

Excel。通貨スタイルの「￥」は、桁数によって位置がバラバラなので揃えたい。

＜表示形式＞

お馴染みの「￥」マークですが、数値の先頭に表示されるため、桁数によって、一がバラバラで表示されます。

できたら、位置を揃えたいと思ったら、どのようにしたらいいのでしょうか。

そこで、表示形式を、「会計」にしてみましょう。

セルの書式設定ダイアログボックスを表示するのもいいですが、ホームタブに用意されていますので、そちらの方法をご紹介します。

C2:C5を範囲選択します。

ホームタブの数値の書式ボックスにある▼をクリックして、展開します。

その中にある「会計」をクリックします。

すると、会計の表示形式が設定されました。

「￥」マークは数値の桁数に関係なく、揃っていることが確認できます。

ただし、「通貨スタイル」と「会計」で違う点は、数値が「負数」の時です。

通貨スタイルは、文字の色が赤色に変わりますが、会計はそのままで、赤色には変わりません。

Excel。なぜ数式を入力しても、算出されないで数式で表示されてしまうの【string】

Excel。なぜ数式を入力しても、算出されないで数式で表示されてしまうの

＜表示形式：文字列＞

ちょっとしたことで、どうして・なんでそうなるの。ということがExcelで発生します。

例えば、次のようなケース。

C2に、単価×数量なので、=A2*B2と入力しても、数式が表示されてしまい、計算結果を表示してくれません。

原因は、ホームタブにある「数値の形式」。

たまたまなのか、以前そのような書式の設定をしたままだったのか、理由はわかりませんが、「文字列」になっています。

これでは、数式も文字列で表示されてしまうというわけです。

「文字列」から「標準」に戻してから、再度、数式を設定することで、対応することができます。

Excel。年月日を漢数字にした和暦で日付を表示する方法とは【Display format】

Excel。年月日を漢数字にした和暦で日付を表示する方法とは

＜表示形式＞

Excelで、日付を和暦に変更するには、表示形式の日付から、和暦を選択することで、令和5年10月15日のように表示することができます。

では、令和五年十月十五日というように、数値を漢数字にするには、どのようにしたらいいのか、その対応方法をご紹介します。

Excel。マイナスのパーセントなら、自動的に文字の色を赤色にしたい。【minus】

Excel。マイナスのパーセントなら、自動的に文字の色を赤色にしたい。

＜表示形式＞

Excelでは、三桁区切りの「,（カンマ）」を設定すると、数値がマイナスの時に、自動的に、文字の色が赤色に変わります。

ところが、「％」だと、数値がマイナスのパーセントの時は、文字の色が赤色に変わってくれません。

そこで、「表示形式」のユーザー定義を設定することで、パーセントの数値がマイナスになっても、文字の色を赤色にすることができます。

Excel。日付から、手早く、曜日を表示したい。しかも「日曜」のように表示したい【day of week】

Excel。日付から、手早く、曜日を表示したい。しかも「日曜」のように表示したい

＜表示形式＞

日付から、曜日を表示したい時には、セル参照をしてから、表示形式をつかって変更してきます。

B2には、=A2とすることで、セル参照。

つまり、A2のデータを表示することができます。

B2は日付表示なので、曜日に変更します。

表示形式のユーザー定義をつかうわけですが、今回は、日曜日のような曜日ではなく、日曜のような「曜」だけを表示していきます。

B2をクリックして、ショートカットキー：Ctrl+1をつかって、セルの書式設定ダイアログボックスを表示します。

分類が「ユーザー定義」になっていることを確認して、種類に「aaa"曜"」と入力します。

あとは、OKボタンをクリックして、完成です。

なお、種類に「aaaa」と入力すると、「金曜日」と「曜日」での表示になってしまい、「曜」だけで表示するには、「aaa"曜"」と入力する必要があります。

Excel。数値が０（ゼロ）の時は、「±０」と表示したいけど、どうしたらいいの。【ZERO】

Excel。数値が０（ゼロ）の時は、「±０」と表示したいけど、どうしたらいいの。

＜表示形式＞

数値が0（ゼロ）の場合に、「±」の記号をつけた「±０」というように表示するには、表示形式のユーザー定義をつかって、設定しなければなりません。

また、正数と負数の場合のことも考慮して、表示形式を設定する必要があります。

今回は、三桁区切りのカンマをつける。

負数の時は、文字の色を赤にする。そして0（ゼロ）のときには、「±０」と表示するという表示形式を設定することにします。

C2:C4に設定した表示形式を確認します。

C2:C4を範囲選択して、Ctrl+1でセルの書式設定ダイアログボックスを表示します。

表示形式のユーザー定義をクリックします。

種類に次の表示形式を設定します。

#,##0;[赤]-#,##0;±0

これで、±０と表示することができます。

Excel。表示形式内で「改行」したい場合、どうしたらいいの【Display format】

Excel。表示形式内で「改行」したい場合、どうしたらいいの

＜表示形式＋Ctrl+J＞

セル内の任意な場所で改行したい時には、Alt+Enterで対応することができますが、表示形式をつかった場合、Alt+Enterでは対応することができません。

次の表で確認しておきましょう。

A1には、2023/9/1という日付が入力してあります。

それを、表示形式で「d"日"(aaa)」と設定してあります。

これを、曜日の前で改行したい場合、Alt+Enterでは対応できないというわけです。

どのようにしたらいいのかというと、表示形式を設定する時に、改行したいところで
「Ctrl + J」
を入力することで、改行することができます。

なお、Ctrl+Jはショートカットキーなので、実際は文字として見えません。

あと、設定時に注意点というか、ビックリすることがありますので、その点を確認しておきましょう。

設定時、「Ctrl + J」を入力すると、改行が入力されてしまうので、それ以降が見えなくなります。

そこで、先に、入力しておいてから、改行するようにすれば、設定しやすいです。

設定後セルの表示が改行されていない場合があります。

その場合には、

セルの書式設定ダイアログボックスの配置で「折り返して全体を表示する」をオンにします。

さらに、行の幅を広げてあげると、改行されたことを確認することができます。

Excel。WEEKDAY関数で算出された曜日の数値を、「日」のように手早く表示するには【day of week】

Excel。WEEKDAY関数で算出された曜日の数値を、「日」のように手早く表示するには

＜WEEKDAY関数＋表示形式＞

曜日を算出することができるWEEKDAY関数ですが、算出後、曜日に該当する数値を算出する関数なので、いったいこれが、何曜日なのか、一目ではわかりません。

どのようにしたら「日」のように、手早く表示することができるのでしょうか。

用意した表をつかって確認します。

B2に設定した数式は、

=WEEKDAY(A2,1)

オートフィルで数式をコピーしています。

実は、この数値を「日」のようにするには、表示形式をつかうことで、簡単に設定することができます。

B2:B6を範囲選択して、「セルの書式設定」ダイアログボックスを表示します。

表示形式タブの「ユーザー定義」に「aaa」と設定します。

あとは、OKボタンをクリックします。

これで、「日」のように、曜日が一目でわかるようにできました。

ただし、これをやるためには、ある条件があります。

それは、WEEKDAY関数の2つ目の引数「種類」を「1」か「17」の日曜日が「1」から始まる場合にだけ対応しています。

2～16は、数値こそ算出されますが、「1」を「日」と表示されるようにしか表示形式は対応していません。

よって、「種類」を2～16で設定した場合は、IFS関数などの関数を組み合わせる必要があります。

Excel。知っていると便利。表示形式の「月」はmの数で表示が色々かわります。【month】

Excel。知っていると便利。表示形式の「月」はmの数で表示が色々かわります。

＜表示形式＋TEXT関数＞

Excelの表示形式は、様々なものが用意されています。

日付に関しても様々なものが用意されていますが、その中でも「月」に関する表示形式は、知っていると、面白いというか、便利だったりします。

A列には、日付を入力してあります。

C2には、表示形式の関数であるTEXT関数で、次の数式を設定しています。

=TEXT(A2,"m")

C3からC6まで、同じTEXT関数をつかった数式を設定してあります。

ただ、mの数が、B列のように1個から5個と変えてあります。

「m」が1個だと、月の1桁を表示します。

「mm」と2個だと、1桁の1～9月の場合「0（ゼロ）」が追加された表示に変わります。

「mmm」と3個だと、月を、英語表記の3文字で表示されます。

「mmmm」と4個だと、英語表記の月で表示します。

「mmmmm」と5個だと、英語の月表示の頭文字を表示します。

このように、表示形式は様々表示が用意されていますので、使えるものがあるかもしれませんね。

Excel。正数・ゼロ・負数で文字の色を手早く変更するには、表示形式をつかいます。【Plus or minus】

Excel。正数・ゼロ・負数で文字の色を手早く変更するには、表示形式をつかいます。

＜表示形式＞

条件によって、文字の色を変えるならば、条件付き書式をつかいます。

ただ、条件付き書式は、設定する条件数が増えれば増えるほど、面倒になってきます。

例えば、正数なら文字の色を青。

ゼロなら、文字の色を黒。

負数なら文字の色は赤。

というようにしたいなら、条件付き書式をつかうならば、3種類設定しなければなりません。

つまり、3回同じようなことをしないといけないというわけです。

ちょっと面倒ですね。

また、Excel VBAでプログラム文をつくるというほどのことでもない。

そこで、表示形式のユーザー定義をつかうことでも、文字の色を条件によって変更することも可能です。

今回は、誤差の値が、正数なら文字の色を「青」。ゼロならば、そのまま「黒」で、負数ならば、文字の色を「赤」にしたいわけです。

設定方法を確認していきます。

D2:D6を範囲選択します。

セルの書式設定ダイアログボックスを表示します。

表示形式の分類にある「ユーザー定義」をクリックします。

種類に、

[赤][<0]-#,##0;[=0]0;[青]#,##0

と設定します。

これで、正数なら文字の色を「青」。

ゼロならば、そのまま「黒」で、負数ならば、文字の色を「赤」にすることができます。

設定した「[赤][<0]-#,##0;[=0]0;[青]#,##0」を確認しておきましょう。

[赤]や[青]ですが、これで文字の色を設定することができます。

あと、[<0]や[=0]とすると、[<0]はゼロより小さい場合、[=0]でゼロの場合の条件を設定することができます。

なので、[赤][<0]とすることで、「ゼロより小さい場合は文字の色を赤色」という条件をつくることができるというわけです。

Excel。数値を全角にするなら表示形式をつかいます。ところで表示形式の「$jp-JP」って何？【Display format】

Excel。数値を全角にするなら表示形式をつかいます。ところで表示形式の「$jp-JP」って何？

＜表示形式：全角＞

Excelは、全角で数値を入力しても、半角で入力されてしまいます。

通常は半角でいいのですが、業務上どうしても、全角で入力したい場合、どのようにしたらいいのでしょうか。

そこで、表示形式をつかうことで、半角の数値を全角で表示することができます。

セルの書式設定ダイアログボックスを表示します。

なお、セルの書式設定ダイアログボックスは、頻繁に表示するので、「Ctrl + 1」というショートカットキーを覚えておくと便利です。

 

表示形式タブの分類にある「その他」の「全角」を選択してOKボタンをクリックします。

これで、全角で数値を表示することができます。

設定後、表示形式をユーザー定義にすると、どのようにして全角にしているのかが、わかります。

ユーザー定義をクリックしてみましょう。

種類には、

[DBNum3][$-ja-JP]0

と設定されています。

[DBNum3]は、数値を全角にすることができます。

全角数値にするには、[DBNum3]を追加すればいいというわけですね。

ちなみに、

[DBNum1]は、「一」とか「二」という漢数字で表示することができます 。

[DBNum2]は、「壱」とか「弐」という大字などでつかう漢数字で表示することができます。

さて、「$-ja-JP」は、何なのか？

これは、jaが日本語で、JPは日本を意味しています。

要するに「$-言語-地域」の設定です。

ダイアログボックスで、種類の下側に「ロケール」という地域を設定するボックスがありますが、その設定が入力されているというわけです。

だから、「$-ja-JP」を削除しても、全角の数値にするのに、影響はありません。

また、「$-411」と表示されることがありますが、411が地域名を表しているので、$-ja-JPと同じ意味です。

表示形式は、なかなか奥が深いですが、知っていると何かと重宝します。

Excel。パーセント表示で正数なら「＋」。負数なら「-」で数値を赤色にしたい【Display format】

Excel。パーセント表示で正数なら「＋」。負数なら「-」で数値を赤色にしたい

＜表示形式＞

三桁区切りの「,（カンマ）」を設定すると、負数ならば「-（マイナス）」が表示されて、数値の色も赤色に変わってくれますが、パーセント表示の場合、赤色になりません。

三桁区切りと同じようにしたいのと、正数ならば、「+（プラス）」を表示するのも合わせて設定してきます。

最初に、ホームタブにあるパーセント表示に変えるボタン。

「パーセントスタイル」をクリックしておきましょう。

C1:C2を範囲選択します。

セルの書式設定ダイアログボックスを表示します。

セルの書式設定ダイアログボックスは「Ctrl + 1」というショートカットキーが用意されています。

セルの書式設定ダイアログボックスは頻繁に使用しますので、知っていると便利なショートカットキーの一つです。

表示形式タブの「ユーザー定義」をクリックして、種類に表示形式を設定します。

+0.0%;[赤]-0.0%

あとは、OKボタンをクリックします。

これで、正数の場合、「+（プラス）」が付いたパーセント表示で、負数の場合なら「-（マイナス）」が表示されて、数値の色を赤色で表示することができます。

Excel。合計した時間を、時分表示にするには、どうしたらいいの。【total time】

Excel。合計した時間を、時分表示にするには、どうしたらいいの。

＜表示形式とSUM関数＞

時間計算は、Excelが苦手としているものの一つだといえます。

苦手というか、面倒といったところでしょうか。

次の表をつかって説明します。

B7は、B2:B6の分を合算した時間を表示してあります。

B7の数式は、

=SUM(B2:B6)

と設定してみましょう。

B7の結果は150で単位は「分」です。

150分ではわかりにくいので、単位を「時分」に変えてみることにします。

B7をクリックして、セルの書式設定ダイアログボックスを表示します。

表示形式タブの分類から「時刻」をクリックして、種類を「13時30分」の時分を選択してOKボタンをクリックします。

ところが、表示がおかしくなっています。

0時00分と表示されています。

原因は、B7の値は単位こそ「分」としていますが、実際は150という数値なわけです。

Excelは1日を1としました。

つまり1=1日=24hですから、1/24しないといけません。

そして、1時間は60分ですから、1分は1分=1/60です。

よって、B7の値を24で除算して、さらに60で除算する必要があるというわけです。

それでは、B7の数式を、修正します。

=SUM(B2:B6)/24/60

あとは、表示形式を

h"時間"mm"分"

と修正したら完成です。

Excelでは時間計算をするときなどに、1=1/24ということを、頭の中に入れておく必要がある場合がありますので、注意が必要ですね。

Excel。時間の表示を「○時間○分」と表示するには、どうしたらいい【Times of Day】

Excel。時間の表示を「○時間○分」と表示するには、どうしたらいい

＜表示形式＞

時間の表示形式だと、「○時○分」はありますが、「○時間○分」と表示したい場合には、表示形式のユーザー定義をつかって、表示を変更する必要があります。

設定したい範囲を選択します。

今回は、C2:C3を範囲選択します。

セルの書式設定ダイアログボックスを表示します。

表示形式タブの「ユーザー定義」をクリックします。

種類を次のように設定します。

h"時間"mm"分"

これで、「○時間○分」と表示することができます。