カテゴリー別アーカイブ: マイクロソフトエクセル サンプル

勤務表 月別 時間給

日別月間 万年スケジュール DATE関数 MONTH関数制作時にすでに出来上がっていたのですが、なぜか公開していませんでした。なぜだったんでしょう。

少し見直しをして、公開いたします。ダウンロードできますが、よく動作を確認してくださいね。これは実際にに使い込んでいませんのでもしかしたら見落としがあるやもしれません。

終業時間から始業時間と休憩時間を引いて実労時間を算出しています。入力方法によっては実労時間が1分単位になりますので丸め時間を設定しています。

丸め時間が30分なら29分までは切り捨てです。59分までが30分労働となります。実労時間のHOUR部分と丸め後の分に時給をかけてその日の日当(小計)が計算されています。交通費欄に書かれた金額も加算されます。

変更部分

セルB2 :氏名
セルA3 :西暦
セルC3:月
セルG3:時間給
セルI3:丸め時間(60にしておけば1時間未満は59分でも0円)

これらのセルを設定すればあとは始業時間と終業時間、休憩時間、交通費を入力すれば37行目に累計されます。

月間150時間から180時間などの丸め後の時間が要る場合はL列に丸め後の時間を表示していますので数式を参考に作り替えてみてください。

簡単にシートの計算式などに触れておきます。

全体の表については日別月間 万年スケジュール DATE関数 MONTH関数と同じものです。F列の実労時間は引き算なのですが、終業時間から始業時間と休憩時間を引いていますので始業時間だけが入力された時点ではマイナス値になってしまいます。マイナス値が表示されるのは嫌なので表示形式 (数値)にあるようにマイナス値;ゼロ値;文字列は表示しないように、表示形式を

[h]:mm;;;

というように設定しています。正の数の時だけ時:分と表示されるようにしています。[]は24時間以上の場合時間が0に戻ってこずに、24:15とか26:50のような表示形式です。時間の計算 FLOOR関数を参照ください。

金額のセルは実労時間の時間部分に時給をかけたものに実労時間の分の部分を丸め時間で割った商に時間給をかけて丸め時間給(15分あたりとか30分あたりとか)をかけたものを足しています。端数が出ないようにTRUNC関数で切り捨てています。

=IF(F5>0,HOUR(F5)*$G$3+TRUNC(TRUNC(MINUTE(F5)/$I$3)*$G$3*$I$3/60))

$G$3は時給のセル番地、F5は実労時間のセル番地、なのでHOUR(F5)*$G$3が実労のじか時間部分だけに対する金額。

TRUNC(MINUTE(F5)/$I$3)が丸め時間単位が何回あったか(15分が3回とか)、$G$3*$I$3/60が丸め時間単位あたりの給与

TRUNC(TRUNC(MINUTE(F5)/$I$3)*$G$3*$I$3/60)が実労時間の分に対する給与額になります。1円未満が出ないようにTRUNC関数で切り捨てています。

されにその金額を計算するにあたっては労働時間が0より大きい時だけ計算するようにif文で囲んでいます。

計算用の途中経過の値のセルを作っていませんのでわかりずらいかもしれませんが、そう難しいことはやっていません。

くれぐれもご使用の際は何度も検証の上、自己責任でご利用ください。すでに1日24時間以上の実労時間の場合はうまくいかないことは分かっています。

勤務表のダウンロード

人工無知能

たいそうなタイトルですが人工知能(Artificial Intelligence)とは程遠いのでArtificial No Intelligenceといいますか人口無知能です。ただただセルの値をVLOOKUP関数で引っ張り出していますが結果を図形(オートシェイプ)に表示しているところが味噌です。

10年以上前ですが何とかオートシェイプに(その当時はオートシェイプと呼んでました)動的に値を表示させられないかとやっていたらたまたまできましたが、どこで使うわけでもなく今に至っています。なので全然利用価値はないと思います。

利用価値がないからか寡聞にしていままでこれを紹介しているサイトを見なかったのでここで無理やり人口無知能を作成し発表します!

まず普通にVLOOKUP関数で数値により値を変更できるようにします。

セルA1の値を見てセルR5にすぐ上の表(Q1:R4)から文字列を取得します。この辺りはVLOOKUP関数参照してくださいね。

写真と図形(オートシェイプ)を配置したら図形に文字入力できるようにします。2013からはダブルクリックでカーソルが出ます。それ以前は右クリックしてテキストの追加をクリックしてください。

ここがポイント

図形に文字入力できる状態になったら数式バーに表示したい文章のあるセル、今回はセルR5ですね。これを数式バーに入力します。

=R5

以上です。

人口無知能のダウンロード

請求書

VLOOKUP関数を使って請求書を作成してみます。キーから値を抽出 VLOOKUP,キーから値を抽出 VLOOKUP 2

請求書のレイアウトはセル結合したり、右揃えにしたりとエクセルの表現力をフルに使って頑張ってください。

seikyusyo

本題はVLOOKUP関数を使って別シートの商品一覧から商品名と単価を引っ張ってくることです。これ自体は簡単ですが、商品コードが入っていないときのエラー処理とゼロの表示をどうするかです。

まずは、sheet2に商品一覧を作成します。sheet2の名前を商品リストに変更しました。シート名の変更はシート操作を参照してください。

左端の列に重複しない識別のためのID番号を振っておきます。商品番号などになるでしょう。

seikyusyo_1

次に別のシートに請求書を作成します。詳細欄には商品番号を入力すると商品名と単価が自動で表示されて、個数を入れると単価かける個数の小計が計算されて、小計の合計が自動で計算されるようにします。

seikyusyo_2

VLOOKUP関数を使っています。小計や合計は単なる掛け算とオートサムです。消費税はROUNDDOWN関数で小数点以下を切り捨てています。四捨五入、切り上げ、切り捨て

seikyusyo_3

このままでは、商品IDが入力されていない時にエラー表示されてしまうのでIFERROR関数でエラー表示されないようにします。商品名と、単価、小計にIFERROR関数を使用しています。キーから値を抽出 VLOOKUP

seikyusyo_4下段の小計、消費税、合計は商品番号が何もないときには0が表示されますが、商品なしで請求書はないと思いますので放置しています。

あとは、少しごにょごにょすれば請求書完成です。

商品番号を毎回手入力するのは面倒なのでリストからの入力にしています。入力規則2 リストからの入力

seikyusyo_5

次回使うときにデータを消すのがちょっとだけ面倒です。数式の入っているセルがあるので商品番号の列と数量の列を選択しなくてはなりません。名前ボックスにクリアという名前でありますので選択すると商品番号の列と数量の列が範囲選択されますのでDeleteキーで値をクリアしてください。計算式のセル参照

seikyusyo_6

窓付き封筒対応としたかったのですが、手元になかったので相手先の住所の位置は適当です。

請求書のダウンロード

ローン支払い明細書

PMT関数 ローン計算 元利均等払い元利均等払いの内訳 元金と利息で元利均等払いの計算方法をご紹介しました。

今回は月払いの明細が出力されるエクセルシート作成してみました。使い道はあまりないかもしれません。

loandetails

あくまで、下調べ程度でお使いいただきたいのですが、もしかしたら、不動産屋さんや自動車屋さんなどで重宝するのかもしれません。(自前でいいのをお持ちですよね。)

元利均等払いの内訳 元金と利息ではローン残高は元金から支払済み元金を引いて計算していますしたが、今回はIMPT関数から算出しています。計算結果は同じなのですが、最終回の残高0となるところが、まれに小数点以下でマイナスになる場合があったので、IMPT関数で残高を算出しました。

IPMT(利率, 次の期, 期間, 現在価値)/利率

計算に当たっては月賦に換算しますので利率(年利)を12で割り、期間(年)を月に直すのに12をかけたりします。残高は次の期(次の支払)の利息分から逆算するので期に1を足して計算しています。

使用に当たっては入力は年利と支払年数のほうがわかりよいと思いますので月あたりに変換したセルを文字色白で見えないようにしています。また、間違って消さないように保護をかけています。(数式には保護をかけていません 保護にパスワードは使用していません)

期間は1年から40年まで対応しています。住宅ローンでも40年あれば対応できると思います。

40年だと480か月分の明細行が出力されますので計算式もそれだけ入っています。1年でも数式で空白など出力していますので480か月分の行が印刷されてしまいます。そこでフィルタで元金が0以上の行のみ表示するようにしていますが、フィルタは自動再計算が行われても更新してくれません。

仕方がないのでVBA(マクロですね。)でフィルタを作動させています。

ファイルの拡張子がxlsmになっています。また実行時にはマクロを有効にしますか?などの警告が表示されます。

どうでもいいことですが、エクセルのセルの枠と行番号、列番号は非表示にしています。

どうか自己責任でご使用ください。計算間違いなどの精査はしていますが本ファイルご使用による損害等には責任は負いかねますのでよろしくお願いします。

ローン支払明細書のダウンロード

エクセルでバーコード

エクセルでバーコードの検索が多いようです。ワードの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桁入力されたらチェックデジットは計算しないなど工夫の余地はたくさんあります。入力されたチェックデジットとエクセルで計算したチェックデジットに違いがあれば赤文字で警告する、などもよいかもしれません。

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

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