月別アーカイブ: 2014年5月

ステータスバー

office2013になってステータスバーがなくなったのかと思ったことがありましたが、緑色になっているだけできちんとありました。

excel2003では選択範囲の合計や平均などが数式を使わずにステータスバーを見ればわかるようになっていました。計算結果を合計から平均などに変更するときはステータスバーを右クリックしてメニューから選択していました。(図はexcel2003)

statusbar2003

エクセル2007からは同時に複数の計算結果が表示できるようになりました。エクセル2013でも同じです。(図はexcel2013)

statusbar2013_1

緑色ですがステータスバーです。

statusbar2013_2

同様に右クリックすると沢山の項目があります。平均、データの個数、数値の個数、最小値、最大値、合計を同時に表示することも可能です。

エクセルでバーコード

エクセルでバーコードの検索が多いようです。ワードのQRコードがあるのでご覧になった方はがっかりされたかもしれません。

基本的にエクセルの標準機能でバーコードは作成できないようです。無料で優秀なフリーソフトがありますので、そちらをお使いになられることをお勧めします。

以前、エクセルでバーコード作成をしたことがあったので挑戦してみましたが、以前と違いました。以前作成したバーコードは社内独自のバーコードだったようで、仕組み自体は簡単でした。

おそらくお探しのバーコードはJANコードだと思われます。

そこでJANコードのバーコード作成方法をネットで探しましたが、初めは理解しづらかったです。

13桁と8桁の物があり、通常は13桁がよくつかわれるということなので13桁のJANコードをバーコード化します。

jan13

アドオンのインストールが制限されていたり、不安がある場合にこれをお試しください。ただし、バーコードが1つしか作れなかったり、印刷時のことは考慮外だったりなので実用には向きません。実際にご使用になる場合は自己責任の上、よく実物と比較したり、実際のバーコードリーダーで読み込み可能かなどの検証をお願いします。

13桁のバーコードのお約束は最初の2ケタが国コード次の5ケタが企業コード次の5ケタが商品コード最後の1ケタがチェックデジットです。参考サイト http://www.dsri.jp/jan/about_jan.htm や http://ja.wikipedia.org/wiki/%E3%83%90%E3%83%BC%E3%82%B3%E3%83%BC%E3%83%89

チェックデジットはそれまでの12桁から計算されるもので読み取り間違いの防止用です。http://www.dsri.jp/jan/check_digit.htm

12桁の数値からチェックデジットを計算してバーコードの画像を作る方法を考えます。

数値がどのような仕組みで黒と白のバーになっているのかは、少し面倒なルールがあります。

バーコードリーダーで読み込んで使用するので、バーコードの仕組みはコンピュータにわかりやすいものになっています。コンピュータがわかりやすいというものは2進数です。2進数を黒白のバーで表しています。

しかし、単純に2進数を当てはめているわけではなく、バーコードの天地が逆でもわかるような工夫がされています。特定の組み合わせで0から9までの数値を表しています。1つの数値を表すのに7桁の2進数を使用しています。また、国コードも含めてバーコードを作成したいのですが国際化のため国コードはバーコードに含められません。バーになっているのはチェックデジットを含む後ろからの12桁分です。

日本は45または49が国コードなので2ケタ目の5や9はバーコードに含むことは出来ますが、4を含むことは出来ません。そこで4を含まない最初の数字の6桁の組み合わせで、初めの4を表します。

最初の6桁の0から9までの数値は2種類ずつ用意しておきます。便宜上左1と左2としておきます。左1と左2の組み合わせを使って、バーコードに含まれていない4を表します。(下の表の見出しや国識別の数値は、私独自の物になっています。)国番号45の場合は4の行の国識別を使います。

 1桁目  国識別 左1 左2
0 111111 0001101 0100111 1110010
1 112122 0011001 0110011 1100110
2 112212 0010011 0011011 1101100
3 112221 0111101 0100001 1000010
4 121122 0100011 0011101 1011100
5 122112 0110001 0111001 1001110
6 122211 0101111 0000101 1010000
7 121212 0111011 0010001 1000100
8 121221 0110111 0001001 1001000
9 122121 0001011 0010111 1110100

1桁目が4から始まる場合の国識別は121122です。121122の6桁の数値はそれぞれ左1、左2、左1、左1、左2、左2を表します。(桁ごとに左につけたもの)

451234567890の数値をバーコード化するなら初めの4はバーにしません。次の512345は左1、左2、左1、左1、左2、左2のそれぞれの列から

  • 5は0110001(左1を使う)
  • 1は0110011(左2を使う)
  • 2は0010011(左1を使う)
  • 3は0111101(左1を使う)
  • 4は0011101(左2を使う)
  • 5は0111001(左2を使う)

ということになります。残りの右の5ケタは右の列から該当する数値を当てはめます。451234567890のチェックデジットは6です。チェックデジットも右の列から探します。

これで11桁+チェックデジットの12桁のバーコードの2進数が得られます。これにバーコードの始まりと終わりを示す101という数値を最初と最後に追加します。そして左6桁と右6桁の間に真ん中を示す01010を入れます。

合計、3桁+7桁×6+5桁+7桁×6+3桁 の95桁の2進数が得られます。

10101100010110011001001101111010011101011100101010101000010001001001000111010011100101010000101

この2進数の0を白、1を黒に塗りつぶせばJANコードのバーコードが出来上がります。実際のバーコードは縦横の幅など、規定があります。

セルの幅と高さを調整し1のセルを黒で塗りつぶして0のセルを白で塗りつぶすという方法もありますが、今回はグラフを使いしました。

値が0と1だけの棒グラフです。0は棒がなくて、1は棒がある。棒と棒の間をゼロにしてくっつけてあります。

ご自身でエクセルでJANコードの13桁のバーコードを作成されるときの手順をまとめておきます。

バーコードにする数値をルールに従い2進数化して1を黒0を白(何もしない)にしてバーコードに見せる

  • 数値13桁のルールを確認する
  • チェックデジットの計算方法を数式化する
  • 13桁の1桁目はバーにしない
  • 1桁目の数値で左6桁の2進数が変わる
  • はじめと真中と終わりに既定の数値が入る

13(バーにする12)桁のルールは上の表にまとまっているのでどの場合どこの数値を取得するかは国識別の数値でわかります。

あとはVLOOKUP関数、TEXT関数(頭のゼロが消えないよう文字列として扱う)、MID関数(7桁ある2進数の何文字目から何文字を取得するか)で出来ます。2進数の頭のゼロが消えないように表示形式を設定することも必要です。

バーコード作成時1ケタずつ別のセルに数値を入力するのが面倒な場合は、どこかのセルに入力した値をそれぞれのセルにばらすとか、12桁の入力時はチェックデジットを計算するが、13桁入力されたらチェックデジットは計算しないなど工夫の余地はたくさんあります。入力されたチェックデジットとエクセルで計算したチェックデジットに違いがあれば赤文字で警告する、などもよいかもしれません。

バーコード作成エクセルブックは自己責任でご利用ください。ご利用による損害、損失などの責任は一切負いませんのでよろしくお願いします。

バーコード作成エクセルブックのダウンロード

(万年)カレンダー

日別月間万年スケジュールは縦型でしたが今回はよくあるカレンダーと同じような曜日の位置が固定されている7日で1行のカレンダーを作成してみます。(図はexcel2013)

schedule日別月間万年スケジュールで使った関数を使いますので一度見ておいてください。

今回は以下のような曜日の場所が固定されているカレンダーを作成してみます。

calendar

日別月間万年スケジュールは朔日(ついたち)の位置はいつも一緒なので月末が何日まであるのかがポイントでした。

今回はいくつかの処理に分かれますので処理ごとに考え方を決めておきます。

calendar_1

  1. ついたちがどこに来るかの処理
  2. 週初めの処理
  3. 月末がどこに来るかの処理
  4. 月末がどこに来るかの処理2

1.ついたちがどこに来るかの処理
月によりついたちの曜日が違うので、どのように1という数値の場所を決めるかです。3行目に曜日が文字列としてありますので、セルA4がついたちだったとして曜日が上の3行目のセルにある曜日と一致するかどうか調べます。曜日が一致していたら『1』と表示して、そうでなければ空白にします。セルA4に入力する数式は

=IF(A3=TEXT(DATE($A$1,$C$1,1),”aaa”),1,””)  セルA4の数式

となります。DATE($A$1,$C$1,1)が2014年5月1日です。TEXT(DATE($A$1,$C$1,1),”aaa”)2014年5月1日の曜日が取得できます。(TEXT関数は曜日の表示を参照してください)

IF関数で曜日同士を比較して、その月のついたちが日曜なら1と表示され、日曜でないなら空白が表示されます。(コピーを考えて絶対参照と相対参照が混在しています。)

次にこの数式をセルG4までオートフィルでコピーすると、その月のついたちには1と表示されますが、2日以降が空白になります。

calendar_5

セルB4には隣のセルA4が空白かどうかIF関数で、比較して空白であればついたちであるかどうかの処理をします。すでに数値1があれば翌日分として1加算します。

=IF(A4=””,IF(B3=TEXT(DATE($A$1,$C$1,1),”aaa”),1,””),A4+1)  セルB4からG4の数式

これをセルG4までオートフィルでコピーします。(相対参照は参照セルが変わります。)

この時に、年月日が表示されてしまったら表示形式を標準にしてください。

2.週初めの処理
これは単純です。B列以降は隣のセルに1を足したものでいいのですが週初めは前週の最後(土曜)の数値に1を足したものにします。セルA5は前週末セルG4プラス1です。

=G4+1  セルA5の数式

=A5+1   セルB5からG5までの数式

6行目7行目の第3週、第4週は第2週の式をそのままフィルハンドルでコピーしてください。

3.月末がどこに来るかの処理
どんな月でも第4週の週末までは必ず日が埋まります。

calendar_2

第5週と第6週はある月とない月があります。あるか無いかは前週の同じ曜日に7を足して同じ月かどうかで判別します。month(日付)で月が取得できますのでセルC1の値と比較して同じだったら前週に7足した数値を表示、来月だったら空白の数式を入力します。DATE($A$1,$C$1,A7+7)で前週の同じ曜日に7を足した日。MONTH(DATE($A$1,$C$1,A7+7))でその日が何月か。これをセルC1と同じかどうか比較して、真なら前週の同一曜日(1行上のセル)に7を足したものを表示し、偽なら空白を表示します。結局、セルA8に入力する数式は

=IF(MONTH(DATE($A$1,$C$1,A$7+7))=$C$1,A$7+7,””)  セルA8からG8の数式

セルA8の数式をフィルハンドルでセルG8までコピーします。

4.月末がどこに来るかの処理2
は先ほど数式を縦にコピーしたいのですが、すでに前週が空白の場合があるのでエラーが表示されてしまう可能性があります。ここは前々週(これは第4週なので必ず存在します。)プラス14にすることでエラーが防げます。

=IF(MONTH(DATE($A$1,$C$1,A$7+14))=$C$1,A$7+14,“”)  セルA9からセルB9の数式

+7を+14に変更するだけです。

calendar_3

ついたちが土曜の31日まである月でもセルC9(第6週の火曜)以降は必ず空白ですから数式を入れる必要はありません。

数式だけまとめると(上の図と同じものを作るとして3行目に曜日を入れておく)

  1. セルA4に
    =IF(A3=TEXT(DATE($A$1,$C$1,1),”aaa”),1,””)
    と入力
  2. セルB4に
    =IF(A4=””,IF(B3=TEXT(DATE($A$1,$C$1,1),”aaa”),1,””),A4+1)
    と入力しセルG4までオートフィルでコピー
  3. セルA5に
    =G4+1
    と入力
  4. セルB5に
    =A5+1
    と入力しセルG5までオートフィルでコピー
  5. セルB5からセルG5までを範囲選択し2行下のセルB7からG7までオートフィルでコピー
  6. セルA8に
    =IF(MONTH(DATE($A$1,$C$1,A$7+7))=$C$1,A$7+7,””)
    と入力しセルG8までオートフィルでコピー
  7. セルA8を選択しセルA9までオートフィルでコピーし数式バーで+7の部分を+14に変更(2か所)。または、
    =IF(MONTH(DATE($A$1,$C$1,A$7+14))=$C$1,A$7+14,””)
    とセルA8に入力しセルB8にオートフィルでコピー

となります。

うまくいったら書式を整てそれらしい見栄えにしてみてください。

また、第5週と第6週はあったりなかったりなので罫線は条件付き書式にしておくと自動で表示されたり消えたりします。第4週までは普通に罫線を設定します。日曜日が空白でなければ週末まで罫線を引くという条件を設定します。

5週目のセルA8からセルG8を選択します。ホームタブのスタイルの条件付き書式をクリックし新しいルールをクリックします。

数式を使用して、書式設定するセルを決定をクリックして=$A8<>””と入力し書式のボタンをクリックし罫線を設定します。(<>は小なりと大なりの記号で等しくないを表します。セルA8が空白でなければという意味)

calendar_4

同様にセルA9からG9にも条件付き書式を設定します。この時の数式は=$A9<>””とA8をA9に変更してください。

ほかにもいくつも作成方法があるようですが、簡単な方だと思います。ご参考にしてください。

 

セル幅に収まらない文字列の表示

セルの幅に文字列が収まらない場合右隣のセルが空白セルならはみ出して表示してくれます。列の幅を変更してよいのなら、列見出しのところで幅を変更すれば大丈夫です。(図はexcel2013)

mojihaba

B列はセル幅を列見出BとCの間で左右の矢印になるところでドラッグして広げています。セルC1はセル幅より文字列の方が長く途中で切れています。セルF1は隣のセルが空白なのでセルG1にはみ出して表示しています。

何らかの都合でセル幅を変更できない時の対処法をいくつかご紹介。

単純なのは文字サイズを小さくする方法です。

mojihaba_1

文字が読みにくいですね。ただセルの幅も高さも変わりません。(よく似たものにセルの書式設定の配置の縮小して全体を表示するがあります。)

ホームタブの配置の折り返して全体を表示するをクリックして折り返す方法もあります。ALTキー+エンターキーの改行は必ずそこで改行されるのでセルの幅が変更されても同じ場所で改行されます。

mojihaba_2

いっそ、縦書きもありですか。ホームタブの配置の右下の矢印をクリックしセルの書式設定の配置タブから方向で縦書きの文字列というところをクリックします。

mojihaba_3

またはホームタブの配置の方向ボタンで縦書きを選択します。

mojihaba_4

おすすめは斜めです。先ほどの方向ボタンで左回りに回転をクリックします。ポイントは左揃えと下揃えを設定しておくことです。そうすると文字列の1文字目が左下の角にそろいます。(微妙な角度はセルの書式設定の配置タブで指定できます。)

mojihaba_5

キーから値を抽出 VLOOKUP

商品番号から商品名や価格を抽出するときによく利用されるのがVLOOKUP関数です。

商品一覧があり、商品番号を入力すると商品名が自動で表示されるようにします。価格も自動で表示します。(図はexcel2013)

vlookup

VLOOKUP関数

セルの値を縦方向に検索し一致するセルの同一の行の値を返します。

VLOOKUP(検索値, 範囲, 列番号, [検索の型])

セルB2の数式は、セルA2の値とG列の商品番号の値を比較し一致したセルと同じ行にあるH列の商品名を返します。

vlookup_1

検索値が商品番号、範囲が商品一覧、列番号が勝因一覧の何列目科の数値、検索の型は0(ゼロ)。

VLOOKUP関数の検索値は探したい商品を特定できるものです。

範囲の設定の仕方は少しルールがあります。検索値で指定した値は範囲の一番左の列から探されます。もし、商品名のバナナから120という値を得たいなら範囲はH列から始めなくてはいけません。範囲の左端に探したい値があるようにします。1行目の見出しは範囲に含める必要はありません。また、オートフィルでコピーすることが多いので絶対参照にすることが多いです。

列番号は範囲の左端列を1として何列目のセルの値を取得するかを指定します。

[検索の型]は、省略可能です。完全一致の場合は、0(ゼロ)またはfalseを指定します。trueを設定した場合は検索値未満の最大の値と一致します。(ただし範囲の検索列(左端列は)昇順で並べ替えておく必要があります。)

A列に商品番号を入れるとVLOOKUP関数でB列に自動商品名が入るようになりましたので、範囲を絶対参照にして、セルB10までオートフィルでコピーします。

vlookup_2

セルB2ではうまく答えが出た数式ですがコピーするとエラーが表示されました。これは関数を実行したが該当する答えがないという表示です。今回はセルA3以降の値が入っていませんのでエラーが発生しました。セルA3に商品番号を入力すればエラーは表示されません。

しかし、請求書や見積書などで使う場合エラー表示は格好が悪いです。

いくつか解決方法があります。IF関数を使ってA列が空白かどうかの場合分けをします。空白はダブルクォテーションとダブルクォテーションを続けて入力します。

vlookup_3

セルB3に『もし、セルA3の値が空白だったら、空白を表示、そうでない場合はVLOOKUP関数で商品名を表示』という数式を入力します。

=IF(A3=””,””,VLOOKUP(A3,$G$1:I8,2,0))

これで空白の場合でもエラー表示されなくなりました。この場合は存在しない商品番号を入力した場合同じエラーが表示されます。

vlookup_4

範囲に存在しない商品番号の500を入力してみました。500番の商品はありませんのでエラー表示されます。商品が存在しないのでエラーが正しいのですが、好ましくない場合もあります。知らない人が数式の入っているエラー表示列を消してしまうかもしれません。

ISERROR(テストの対象)かISNA(テストの対象)をつかって(テストの対象)で#N/Aが発生しているかどうかを調べます。エラーが発生していればtrueを返します。

では、セルB3に『セルA2の値でVLOOKUP関数を実施してエラーが発生していれば空白を表示、エラーが発生していなければセルA2の値でVLOOKUP関数を実施する』という数式を入力します。下線部は同じことなので数式に同じものが出てきます。

=IF(ISERROR(VLOOKUP(A3,$G$1:I8,2,0)),””,VLOOKUP(A3,$G$1:I8,2,0))

vlookup_5

これで空白の場合も存在しない商品番号でもエラー表示されなくなりました。

また、Excel2007からIFERROR 関数が使えます。

IFERROR 関数

数式がエラーの時は指定した値を表示し、エラーでないときは数式の結果を返す。

IFERROR(値, エラーの場合の値)

今回はVLOOKUP関数がエラーを返したら空白を返す式にします。

=IFERROR(VLOOKUP(A4,$G$1:I9,2,0),””)

vlookup_6

IFERROR関数の方が数式も見やすく、VLOOKUP関数の修正時も1か所でいいので非常に便利です。

セルB5では、先ほどのセルの値が空白なら空白というIF関数と組み合わせて、『もし、セルA5の値が空白ならば空白を表示、空白でないならセルA5の値でVLOOKUP関数を実施を実施して、エラーだったら”商品番号が違います”と表示する』式を入力してみます。

=IF(A5=””,””,IFERROR(VLOOKUP(A5,$G$1:$I$7,3,0),”商品番号が違います。”))

セルA5に何も値が入力されていない場合はセルB5も何も表示されません。セルA5に範囲にない商品番号が入力されたら”商品番号が違います。”と表示され、正しい商品番号(範囲にある番号)が入力されたら、価格を返します。

vlookup_7

 

元利均等払いの内訳 元金と利息 PPMT関数

PMT関数 ローン計算 元利均等払いでローンの支払額の計算が出来ます。

PPMT関数を使用すると元利均等払いの元金分の支払いがどれくらいかを計算することが可能です。また、IPMT関数で利息分の支払額がわかります。

PPMT関数もPMT関数同様、月払いの計算時には年利を1か月分に、支払回数を年から月に変換しますので以下の図のような表を作成します。百万円を年利5%で1年の分割払いで考えてみます。(図はexcel2013)

ppmt(後ほどB列C列D列と使用する予定ですので、数値はD列から入力しています。)

セルE3には年利を12で割った結果です。=D3/12という数式が入力済み。セルE4は総支払回数になるよう年数×12の=D4*12という数式を入力しました。また、セルD5にはE列の数値を使って毎月の返済額が計算済みです。=PMT(E3,E4,E2)*-1

ppmt_1

では、セルB8に支払第1回の元金分はいくらかPPMT関数で計算します。

PPMT関数

一定の利率で定期的に支払するローンの1回の元金分を計算する

PPMT(利率, 期, 期間, 現在価値, [将来価値], [支払期日])

利率が、3行目の利率です。期は何回目の支払かです。期間は返済期間、現在価値が元金に相当します。元利均等払いは後になるほど支払額に含まれる元金の割合が増えてきます。よってPPMT関数は期の値が何回目の支払かを指定して、都度支払元金を求めます。数式タブの関数ライブラリの財務をクリックし、PPMTをクリックします。

ppmt_2

PPM関数の関数の引数のダイアログボックスが表示されますので適切なセル番地を入力します。利率も期間もE列側の12か月換算の方を使います。期はセルA8です。また、後ほど支払回ごとの計算をしますので期以外は絶対参照にします。

ppmt_3

PPMT関数も結果がマイナスで返ってきますので-1をかけて正の数にしておきます。

ppmt_4

A列の回数を全期間分の12回に増やし、元金の計算式セルB8 もコピーします。

ppmt_5

これで毎月の支払額の中の元金額が作成できました。次に利息分を計算します。セルD5の毎月支払額から元金を引いてもいいですが、利息分がわかる関数がありますので使用して計算してみます。

IPMT関数

一定の利率で定期的に支払するローンの1回の利息分を計算する

IPMT(利率, 期, 期間, 現在価値, [将来価値], [支払期日])

セルC8にIPMT関数をつかって利息を計算しますので先ほどと同じように数式タブの関数ライブラリの財務をクリックし、IPMTをクリックします。

ppmt_6

IPMT関数の関数の引数のダイアログボックスにセル番地を入力します。参照するセルは先ほどのPPMT関数と全く同じになります。

ppmt_7

また、負の値が返ってきますので-1をかけておきます。

ppmt_8

同様にコピーしてD列に元金と利息を足した( =B8+C8) 返済額を出しておきます。

ppmt_9

そのほか少し手を加えて積み上げ縦棒のグラフなんかも追加してみました。支払期間に合わせて表が伸び縮みする工夫なんかがあるといいかもしれませんね。

ppmt_10

今回ローン残高は元金から支払済み元金を引いて計算していますがIMPT関数から算出することも可能です。

http://support.microsoft.com/kb/214091/ja

PMT関数の記事もそうですが、小数点以下の端数や借入日から初回返済日までの期間、月単位ではなく日割りで計算する、などいろいろな要素で実際の数値とは異なることがりますのであくまでシミレーションとしてご利用ください。

ローン計算 元利均等払い PMT関数

自動車や家を購入する際ローンを組むことがあるかもしれません。住宅ローンや自動車ローンなどの支払額がエクセルの関数で簡単に計算できますので実際に購入するにあたって、シミュレーションしておくことが可能です。

PMT関数

一定の利率で定期的に支払するローンの1回の返済金額を計算する

PMT(利率, 期間, 現在価値, [将来価値], [支払期日])

百万円を年利5%、1年で返済する場合の月額返済額を計算してみます。

セルB1に金額として1000000、セルB2に利率として5%(パーセント表示にしています。)、セルB3に期間1年として1、セルB4に毎月の返済額をPMT関数を使って算出します。(図はExcel2013)

pmt

PMT関数を呼び出します。数式タブの財務をクリックします。その中からPMTを選択しクリックします。

pmt_1

関数の引数のダイアログボックスのそれぞれにセルを当てはめていきます。利率、期間、現在価値がそれぞれ金利、期間、元金に相当します。OKボタンをクリックします。

pmt_2

百万円の12回払いなので金利を考えない場合、おおよそ、1回84000円くらいになります。PPM関数で出てきた数値は-1050000とマイナスで百万を超えています。

pmt_3

これは関数の使い方が間違っています。直感で使うとおかしなことになりました。

C列で修正します。期間は実際に支払う回数になります。セルC3に=B3*12 と入力し月の数にします。金利は年利を12で割って、1か月分に変更します。セルC2に =B2/12 と入力します。元金はそのまま横セルC1にコピーします(=B1)。支払額はマイナスで出てくるのが正しい関数の振舞なのでマイナス1を掛けます。

pmt_4

月額は85607円になりました。今回はセルB1からB3までの数値を変更したら色々と使えますのでC列で計算しなおしましたが、PMT関数の引数内で12で割ったり、12をかけてもかまいません。

pmt_5

念のため月額に12をかけてローンの総支払額を計算してみました。一括払いと比べてかなり安くなりました。コツコツと元金を返していくと支払利息も減りますね。

百万円の5パーセント分の金利5万円を貸し付け時に天引きして95万円を渡して、元金百万円を12等分で返済させるあこぎな金融屋さんがいるとか、いないとか。

自分でしっかり計算できるといいですね。

借入日からの初回返済日までの日割り計算や、1円未満の端数処理などで実際の返済額とは一致しないかもしれませんのでそのあたりは気を付けてお使いください。

 

時間の計算 FLOOR関数

日付の計算はDATEDIFを参照してください。概ね、時間は通常の四則計算やオートサムで計算可能です。以下おかしなことが起こる一例と対策を挙げてみます。

エクセルで時間を計算するときに気を付けていただきたいことがあります。コンピュータは内部で2進数が使われているのはよくご存じだと思います。0と1の組み合わせで数字も文字も処理しています。

2進数は1の次が10です。10は10進数で2です。1桁上がると数値が倍になります。10進数は1桁上がると10倍ですね。逆に1桁下がると2進数は2分の1になります。10進数は10分の1です。2進数の0.1は1が一桁下がっているので1の2分の1で10進数でいうところの0.5です。

2進数    10進数
10        2
1         1
0.1       0.5
0.01      0.25
0.001     0.125

2進数では10進数の0.1の数値が表せません。0.25、0.125などそれ以下のものを組み合わせても10進数のちょうど0.1は作れません。

エクセルでは計算時に小数点以下は近似値が使われることがあります。小数点以下の計算を組み合わせると誤差が発生し正しい答えが出ないことがあります。

セルA1に1.2セルB2に1.1セルC1に=A1-B1と入力するします。1.2-1.1は0.1なので0.1と表示されます。(図はexcel2013)

gosa

続いてセルD1に0.1と入力しセルE1に=C1-D1と入力します。セルC1は0.1、セルD1も0.1なので答えは0になるはずですが、とっても小さい負の数と対数表現されています。とにかく0になっていません。gosa_1

セルA1からセルE1までを小数点以下の表示桁数を増やすボタンで小数点以下をどんどん表示させます。

gosa_2

どんどん、0が増えてきますが、あるところから1.2-11の答えのセルC1が、0.1ではなく0.09999999という表示に変わります。エクセルはセルに表示されていない桁で四捨五入されてい表示されます。表示桁数を増やすとと四捨五入されない数値が表示されます。

gosa_3

1.2-1.1は0.01よりもほんの少しだけ少ない数だったということがわかります。なので0.1よりほんのわずかに少ない数から0.1を引くと0よりほんの少しだけ小さいマイナスの数値になります。

エクセルでの小数点以下の数値の計算はわずかな誤差により思わぬ端数が発生することがあります。

エクセルの日付は1900年の1月1日を1として1日ごとに1増えていきます。時間は1日を24で割ったものが1時間になります。

1時間  0.0416666666666667000 (1÷24)
1分   0.0006944444444444440 (1÷24÷60)
1秒   0.0000115740740740741 (1÷24÷60÷60)

すべて割り切りず循環小数です。これにより時間の計算は場合によりおかしな結果になることがあります。とはいえ、小数点以下十数桁のことなので見た目不都合が起きることは少ないです。(またどうしたわけか都合のいい結果になるようです。)

エクセルで1秒の0.0000115740740740741に1日の86400秒を掛けると全く端数のない1になる(見える?)のは不思議です。ウィンドウズの電卓で計算すると1.00000000000000224になりますが、内部で切り捨てするか何かの処理がされているのかもしれません。

しかし、時間の計算結果の比較や単位時間での丸めで誤差が発生することがあります。

誤差とは関係なしに下のような時間計算をしてみます。労働時間を求めるのには退勤時間から出勤時間と休憩時間を引けば求まります。(0時から0時までの24時間なら)gosa_4

次に基本時間を8時間として8時間を超過したものについては残業時間とする計算式を入れます。これはあくまでもエクセルで時間計算でうまくいかないことがある例のためのものです。日々の残業時間を日ごとに切り捨てることは違法です。

実労時間から基本時間の8時間を引いて端数を15分単位で切り捨てます。(労働基準法等関連法令をあたって違法でない範囲で使用してください。)

四捨五入や桁数指定での切り上げ切り捨ての関数では15分単位での切り捨てが出来ません。

FLOOR関数

基準値の倍数未満を切り捨てます。

FLOOR(数値, 基準値)

FLOOR(実労時間-基本時間、15分)で残業時間の15分単位の丸めが出来ます。3行目の残業時間は=FLOOR(D3-$E$1,”0:15″)という式で求まります。

gosa_5

しかし、4行目の計算結果がおかしくなっています。実労時間計算と基本時間の差の計算時に生じた誤差が15分単位の倍数の30分よりほんの少し小さくなってしまったからです。エクセルの画面上では時間:分で表示されているものも実際は小数点以下の値を持った数値です。表示上、端数が見えなくなっているだけです。

あくまで1例として次の方法があります。FLOOR関数で丸める際に見えている8:30や9:30などの文字列で計算してみます。TEXT関数を使用して8:30に見えている0.354166666666667 という数値ではなくて8:30という文字列で計算します。

=FLOOR(TEXT(D3,”hh:mm”)-TEXT($E$1,”hh:mm”),”0:15″)

で計算します。

gosa_7で4行目の計算も合いました。

これ以外にも

  • エクセルの設定で『表示桁数で計算する』にチェックする
  • 誤差のありそうなところに微小な少数(0.0000001など)を加算、減算しておく
  • 少数が出ないように千倍や1万倍して計算し後ほど千分の一や1万分の一に戻す

があります。ほかにもあると思いますし、先ほどの計算式も含めすべての場合に正しい結果が出るかは確認しきれていません。

累積時間を計算すると24時間以上の部分が表示されないことがある(40:00が16:00と表示されたりする)のでセルの書式設定で時間の部分を[h]とすると24時間以上も表示されます。

gosa_8

まとめ

  • エクセルは小数点以下の数値の計算は微小な誤差が生じることがある。
  • 通常の時間の計算は通常の四則計算やオートサムで問題ありません。
  • 時間は内部で少数として計算されるの思わぬ結果になることがある。計算結果と手入力の時間の比較や丸めなど。
  • 単位で切り捨てのFLOOR関数を時間計算で使用すると誤差が表面化することがある。(切り上げはCEILING関数がある)
  • TEXT関数で文字列としてから処理するとうまくいくことがある。理屈上うまくいくと思うが、何かの原因でうまく計算できないかもしれません。使用時はよく確認してください。
  • もし残業代の計算をする場合は計算結果に間違いがないかよく確認し関係法令に基づいて処理してください。未払い請求で莫大な負担がかかるようになるかもしれません

 

 

日別月間 万年スケジュール DATE関数 MONTH関数

エクセルで月間のスケジュール表を作ってみます。もともと時給計算のために作ったものの日付部分だけご紹介します。

年と月を入力すれば自動で月末まで日付と曜日を表示してくれるタイプをを作成します。一度作成しておくと万年カレンダーならぬ万年スケジュールが可能です。(図はexcel2013)

schedule

セルA3とセルC4に年と月を入力すれば月末の該当日までとその曜日が表示されるようにします。2月なら28日まで表示し29、30、31は表示しません。大の月、小の月、うるう年に対応します。DATE関数とMONTH関数を使いますので関数の説明です。

DATE関数

日付のシリアル値を返します。

DATE(年,月,日)

=DATE(1900,1,1)は1を返しますが、通常セルの表示形式が自動で日付になり1900/1/1 と表示されます。=DATE(2014,5,19)だったらシリアル値は41778と表示されます。1900/1/1から41778日経っているということですね(厳密には1引く?)。2014の代わりにA3とセル参照すれば2014のことですしC4とすれば上の図では3月ですね。2014年3月1日はDATE関数ではDADATE(A4,C4,1)とする事で表せます。セルA4の値を変えれば今年でも来年でも使えます。また、セルC4の値で何月でも表すことが可能です。ここで次の式はいつを表しているかお考えください。

=DATE(2014,2,29)

2014年はうるう年ではありませんので2月29日はありません。2月末の1日次の日なので2014/3/1になります。月末の辻褄が合わなければエクセルの方でよろしく来月にしてくれます。

MONTH関数

月を1から12で返します。

MONTH(シリアル値)

=MONTH(“2014/5/1”)は五月ですから5が返ってきます。=MONTH(DATE(2014,5,1))の場合はMONTHの中がDATE関数で2014年5月1日ですからもちろん5が返ります。

では=MONTH(DATE(2014,2,29))とうるう年ではない年の2月の29日を指定すると何月とみなすのでしょうか。DATE関数は月末の辻褄が合わないと自動で調整してくれるのでDATE関数内は2014年の3月1日としてくれます。するとMONTH関数は3月とみなし3を返します。

では、実際についたちから作成していきます。セルA5にセルA3の年とセルC3の月の1日をDATE関数で表示します。=DATE($A$3,$C$3,1)と入力します。

schedule_1

自動で日付の形式で表示されます。

schedule_2

セルB5はセルA2の値をそのまま表示するように=A2と入力します。セルA5もセルB5も3月1日と表示されます。

schedule_3

A列は日だけB列は曜日を表示するようにセルの表示形式を設定します。セルA5からセルA35まではユーザー定義でdとします。

schedule_4

同様にセルB5からセルB35まではユーザー定義で省略された曜日のaaaとします。

schedule_5

セルA6は2日なので上のセルに1足せば1日増えます。=A5+1と入力します。セルB6も=A6として隣の値を参照します。この2つのセルをフィルハンドルで28日までコピーします。

29日30日31日はない月とある月があるので少し工夫します。

schedule_6

セルA33は通常の2月はありませんがうるう年はあります。

=DATE(A3,C3,29)にすればセルA3年セルC3月の29日を日付形式で表示します。セルC3が2月なら29日はありませんので3が1日になります。うるう年なら2月29日になります。この関数の答えに対してMONTH関数で月を取得します。

=MONTH(DATE(A3,C3,29))

セルC3が2ならセルA3年の2月の29日は今月か来月かを知りたいという式になります。3が返ったら2月の29日は来月の3月1日と同じことですよということです。この式の答えとセルC2が同じだったら29日は今月で、違う場合は来月となります。IF関数でどちらかに場合分けします。

=IF(MONTH(DATE(A3,C3,29))=C3,”今月”,”来月”)

同じだったら今月なのでセルの値はその日付を表示します。同じでなければ来月ですからなにも表示しません。上の式の今月の代わりにDATE(A3,C3,29)を、来月の代わりに””で何も表示しなければ月末処理完成です。

=IF(MONTH(DATE(A3,C3,29))=C3,DATE(A3,C3,29),””)

たった3行ですがコピーするときのためにセル参照は絶対参照を使います。そしてそれぞれ29を30と31に変更しておきます。

セルA33 =IF(MONTH(DATE($A$3,$C$3,29))=$C$3,DATE($A$3,$C$3,29),””)
セルA34 =IF(MONTH(DATE($A$3,$C$3,30))=$C$3,DATE($A$3,$C$3,30),””)
セルA35  =IF(MONTH(DATE($A$3,$C$3,31))=$C$3,DATE($A$3,$C$3,31),””)

と数式を入れてください。これで出来上がりです。年月により月末がきちんと表示されます。

もう一つのパターンは条件付き書式に月の比較を使って来月なら白文字にして見えなくするという方法です。

28日までは同じ手順です。29,30,31も同じ手順で日付を入れてしまいます。

条件付き書式(何かの条件によって書式―見え方を変更する)を設定するセルを選択します。月末29,30,31とその曜日のセルA33からセルB35までを範囲選択します。ホームタブのスタイルの条件付き書式をクリックし新しいルールをクリックします。

schedule_7

新しい書式ルールの数式を選択して、書式設定するセルを決定をクリックします。

schedule_8

次の数式を満たす場合に値を書式設定のボックス以下の式を入力します。

=MONTH($A33)<>$C$3

今6つのセルが選択されていますが、アクティブセル(白抜きで表示されている)はA33です。このセルA33にこの数式が適用されます、残りの5つのセルにもこの式をオートフィルでコピーした数式が当てはまります。セルB33にオートフィルでコピーした場合、横向きにドラッグになるので行番号の数値は各々33と3で変わりません。$aと$cは絶対参照なのでこれも変わりません。したがってセルB33にあてはめられる数式は

=MONTH($A33)<>$C$3

全く同じです。そのまま29日の日付により条件が変わります。

セルA34なら下にドラッグなので数値だけが変わります。$A、$C、$3は絶対参照なので変わりません

=MONTH($A34)<>$C$3

セルA34の30日の日付により条件が変わります。あとは同じ要領で考えてみてください。

<>は等しくないという論理演算子です。

schedule_9

セルC3に入力した月と計算で求まった29,30,31の月が等しくなければ文字が見えないように文字の色を白にします。書式をクリックします。

schedule_10

フォントタブの色を白にします。OKボタンで戻っていきます。

どちらも少し面倒かもしれません。条件付き書式がわかっていれば後の方法が簡単かもしれません。条件付き書式で文字色白で見えなくするのよく使うかもしれませんが、後で見直したとき、数式バーに出てきませんので思い出すのが大変かも。また、見えていないだけで存在しますので、計算とかにセルが使用されたら思わぬことになるかもしれません。

私が何度か作ってみて今のところこの2つが比較的簡単な方法かと思います。もちろんほかの方法もたくさんあるでしょう。一度参考に作成してみてください。

列の幅、行の高さを保ったまま張り付ける

セルをコピー貼り付けするとセルの高さと幅が貼り付け時に反映されません。

列の幅と行の高さを設定しあるセルを選択します。(図はexcel2013)

retuhaba

別のシートに張り付けます。

retuhaba_1

既定の列幅でセルの内容に合わせた行の高さになります。元の表と同じ列幅と行の高さにはなりません。

元のシートに表のほかのデータがない場合や、貼り付け先シートにデータがないときはシートのコピーが確実です。

コピー元のシート見出しの上にマウスポインターを移動しCtrlキーを押したまま横にドラッグします。マウスポインターの先の絵柄の中に+のマークがついている状態でマウスのボタンを放しCtrlキーを放します。

retuhaba_2

シートのコピーが出来ました。ドラッグするときにプラスのマークが入っていないとシートの移動になります。

retuhaba_3

セルのコピーで対応する場合は、形式を選択して貼り付けの元の列幅を保持を使用します。

貼り付け先のセルで右クリックし形式を選択して貼り付けをポイントします。

retuhaba_3-1

表示されたサブメニューに元の列幅を保持があるのでクリックします。

retuhaba_4

これで列の幅が同じ状況で貼り付けることが出来ます。しかしまだ高さが反映されません。

コピーする際にセルを選択するのではなく行見出しをドラッグして行単位でコピーします。これを張り付けると行の高さが保持されて貼り付けられます。(列単位でコピーすると列幅が保持されて貼り付けられます。)

retuhaba_5

行選択しコピーしたあと、貼り付け先のセルで右クリックし形式を選択して貼り付けから元の列幅を保持をクリックします。すると幅と高さが元と同じ状態で貼り付けが出来す。retuhaba_6

この方法は、コピーの際、行すべてをコピーしているので、貼り付け先の行にデータがあったらすべて上書きされますので気を付けてください。