トップ > スキル : アプリケーション > Excel(エクセル) > 基礎編(表示形式をマスターする)
日付と時間の表示形式
セルに入力された日付、時間のデータは、シリアル値という特別な数値データとして扱われます。シリアル値は、1900年1月1日を「1」として一日に1ずつ加算される連続番号です。ちなみに「2006/11/1」のシリアル値は「39022」です。つまり、1900年1月1日から数えて2006年11月1日が39022日目となります。
このシリアル値同士を引き算することで、日数差が計算されます。日付の表示形式は、シリアル値に対して行うことになります。
パート勤務時間表を作成します。セルB2に「2006/11/1」と入力します。次にセルA5に「=B3」と入力します。セルA6には「=B3+1」を入力し、その数式をセルA19までコピーします。(図参照)
日付データを和暦にする
セルB3の日付表示を和暦で月までの表示にします。
「セルの書式設定」画面で分類を「ユーザー定義」にし、次のように種類を入力します。
ggge"年"m"月"
日にちの欄には「日」のみの表示を設定します。セルA5からセルA19まで選択して、次のように設定します。
曜日の表示
次に曜日の欄を作成します。セルB5には「=A5」と入力し、セルB19までコピーします。次に曜日の欄を選択し、次の書式を設定します。
日付データのシリアル値には曜日の情報も含まれているので、曜日の書式記号「a」を使って表示します。
年 | 月 | 日 | 曜日 | ||||
---|---|---|---|---|---|---|---|
yy | 06 | m | 8 | 5 | d | aaa | 金 |
yyyy | 2006 | mm | 08 | dd | 05 | aaaa | 金曜日 |
ggge | 平成18 | mmm | Aug | ddd | Fri | ||
ge | H18 | dddd | Friday |
セル内で改行して表示
日付と曜日をセル内で改行したいときは次のように行います。下の例では、「セルの書式設定」画面で「ユーザー定義」の表示形式に「m"月"d"日"(dddd)」と入力しています。
入力後に、日付と曜日の間にカーソルを入れて「Ctrl+Jキー」を押します。
次に[配置]タブを選択し、折り返して全体を表示するにチェックを入れます。
また、セル内にデータ内容が全部表示されないので、セルの高さはあらかじめ広めにしておきます。
時間の表示形式
時間も日にちと同じように、シリアル値を利用して計算されます。0時は「0」で24時が「1」となります。よって、その間の時刻は小数点と言う細かな値で管理されます。
24時間以上を表示させる
出退勤時刻を計算して、時給を掛けて給与を計算させます。
セルE20に勤務時間の合計となる数式を入力します。
=SUM(E5:E19)
合計すると「18:20」と表示されます。合計が間違っているように見えます。しかし、これには原因があります。標準で適用される時刻の表示形式では24時を超えるとリセットされて「0時」になります。この場合、「h」を[ ]で囲んで「[h]」をすれば、24時間を超える経過時間を表示できます。
「セルの書式設定」画面で次のように指定します。
時間単位の計算
次に時間相当の数値に変換します。これは、給与計算をするときには必須のテクニックとなります。時給金額と勤務時間をそのまま計算すると、かなり少ない給与となる。なぜなら、24時間を1とするからです。
正しい給与にするには、日付関数で時間データから「日」「時」「分」の数値を取り出して、単位を「時」にそろえて合計します。この場合の表示形式は、「標準」にします。
セルE22に次の数式を入力します。
=DAY(E20)*24+HOUR(E20)+MINUTE(E20)/60
セルE23に次の数式を入力して、給与結果を表示させます。セルE23にはあらかじめ通貨スタイルが設定されています。
=E21*E22