Excelで領収書

たまに読みきりのテーマを・・・・・。

入力したデータをデータベースに変換するVBコード「dhenkan」マクロを利用した領収書を作ってみました。

dhenkanコードを貼り付けし、図形にマクロを登録します。
※「dhenkan」の使い方は「データベース変換①～コードを貼り付ける」及びデータベース変換～「dhenkan」マクロの使い方を参照してください。

「入力」シートの真ん中あたりに領収書を作成します。（こんな感じだと思うのですが・・・。）

※図をクリックすると大きく表示されます。


作成した領収書の入力項目を作成します。「税抜き金額」や「消費税」は計算式を入れておくといいです。※ちなみに税抜き金額の算出は「＝金額/1.05」です。



入力項目がそのまま領収書へ表示されるように、領収書側へ、参照式を入力します。（図は名前の項目を設定してます。）



すべて設定するとこのように「０」表示になります。金額は通貨表示にしましょう。※日付はなにも入力されないと、「1900/1/0」と表示されます。エラーではありません。



データを入力してみましょう。



「税抜き金額」や「消費税」は自動で計算されました。

領収書はこんな感じになりました。※表示の位置や文字の大きさを調整します。




印刷するときは、領収書の範囲のみの印刷されるよう印刷範囲を設定しましょう。

設定方法

領収書範囲を選択後、「ページレイアウト」タブ「ページ設定」グループに「印刷範囲」というボタンから、「印刷範囲の設定」をクリックすると、毎回の設定が必要なくなります。
※Office2003では「ファイル」→「印刷範囲」→「印刷範囲の設定」



用紙は「A5」など小さいサイズがあるので探してみてください。


印刷後は、図形をクリックし、データを追加しておくと、領収書を発行した履歴やその日の売上金額の算出にも使えます。

このように、データを入力し、蓄積するだけではない利用方法もあります。

この他にも、販売伝票や納品書、報告書などいろいろ作れそうですね。いろいろ試してみてください。

ピボットテーブルで在庫管理①～項目設定

「Excelで在庫管理ってできるかなあ」と輸入家具などを扱ってる生徒さんから聞かれたたことがあり、「できますよ」軽くと答えたんですが、実際どうやって管理したらいいか考えてみました。

考え方としては、在庫ですから、商品の「仕入」により在庫が増えて、販売により在庫が減る。これが大きな流れです。

そして管理するわけですから、毎日の在庫の動きを入力していく必要があるわけです。

入力する項目をこのように設定しました。



注目点

①「倉庫ID」～倉庫が２つ以上あることを想定しています。

②処理区分～「仕入」「販売」のほか、倉庫間の「転送」「転受」さらに「値引き」や初年度の「繰越」などの処理を指定します。

③「在庫数量」「在庫仕入」「在庫販売」～在庫のみを管理するために設けました。②の「処理区分」により値の表示を変化させます。

この他にも入力した日付や数量の単位、税込金額、などいろんな項目が想定されると思いますが、自分の管理で必要と思う項目はどんどん追加してください。


項目の入力が終わったらデータ追加用のフィールドも別シートに作成しておきます。



※上記の項目作成からフィールド作成の流れは（「データ変換①～コードを貼り付ける」と「データ変換②～dhenkanマクロの使い方」）を参照してください。

参照用に倉庫マスタ、商品マスタを別シートに作成しておきます。私はシート名を「マスタ」にしました。

このシートから商品名や単価などを関数により参照します。




それでは、項目に必要な設定を行います。

倉庫と商品にはVLOOKUP関数で、それぞれ「ID」を入力すると「マスタ」シートから名前が自動で表示されるよう設定しましょう。エラー時になにも表示されないようIFERROR関数を合わせて使いましょう。



「処理区分」には、処理を選択できるよう入力規則を設定します。この処理区分により在庫項目の表示が変化するので、大事な設定になります。

※図はOffice2007での解説になります。

処理区分のセル「B5」を選択し、「データ」タブの「データツール」グループから「入力規則」をクリックします。

※Office2003ではデータメニューの「入力規則」になります。



入力規則ダイアログボックスの「入力値の種類」に「リスト」を選択します。

次に「元の値」に処理名を入力します。処理ごとにカンマ「,」で区切ります。

指定した処理区分は「仕入」「販売」「値引き」「転送」「転受」「繰越」「訂正増」「訂正減」です。

※「訂正増」「訂正減」は年度末などなんらかの理由で在庫を調整するとき使います。この他にもいろんな処理が考えられるかもしれません。



このように処理をリストから選択できるようになります。




「仕入単価」「販売単価」には商品IDを検索値としてVLOOKUP関数を使い「マスタ」シートからそれぞれの単価を表示しましょう。（図は仕入単価を設定しています。）



「仕入金額」は基本的には「数量×仕入単価」ですが、「処理区分」が「値引き」の場合は販売金額にのみ影響をあたえる（後半でも説明しています。）ので「０」を表示させるように設定します。



=IF($B$5="値引き",0,B7*B8)

「処理区分」セル「B5」が「値引き」の場合は「０」表示し、それ以外は「数量×仕入単価」を表示。という意味になります。


販売金額も基本的には「数量×販売単価」ですが、「処理区分」が「値引き」の場合は「数量」項目を値引き金額欄として一時的に置き換えるので「数量」欄をそのまま表示するよう設定します。



=IF($B$5="値引き",B7,B7*B9)

「処理区分」セル「B5」が「値引き」の場合は数量セル「B7」表示し、それ以外は「数量×販売単価」を表示。という意味になります。


それでは在庫管理用に用意した項目への設定です。

この項目は、「処理区分」により在庫がプラス（増）になるのかマイナス（減）になるのか判断し表示します。

この項目を集計することにより、現在の在庫を把握することができるわけです。


まずは「在庫数」と「在庫仕入」の設定を行います。



下記条件が考えられます。

①在庫がプラス（増）になる処理は「仕入」「転受」「繰越」「訂正増」です。

②在庫がマイナス（減）になる処理は「販売」「転送」「訂正減」です。

③ここで問題になるのは「値引き」という処理です。値引きは商品が古かったり、または破損したときの処理なので、数量や仕入値には影響を与えません。与えるのは販売額のみです。したがって「値引き」時の処理は「０」表示にする必要があります。

上記の３つの条件分岐をIF関数とOR関数を使って処理します。

※図はクリックすると大きく表示されます。


=IF(OR($B$5="仕入",$B$5="転受",$B$5="繰越",$B$5="訂正増"),B7,IF(B5="値引き",0,B7*-1))

「処理区分」セル「B5」が「仕入」「転受」「繰越」「訂正増」のいずれかの場合は数量セル「B7」をそのまま表示、「値引き」の場合は「０」を表示、それ以外の処理（「販売」「転送」「訂正減」）の場合は数量セル「B7」に「-1」を掛けた数量を表示する。という意味になります。

在庫仕入にも同じ条件で、仕入金額を表示させます。



=IF(OR($B$5="仕入",$B$5="転受",$B$5="繰越",$B$5="訂正増"),B10,IF(B5="値引き",0,B10*-1))


次の「在庫販売」欄は「値引き」の場合はマイナス表示になりますので、上記の値引き用の条件分岐はいらなくなります。



=IF(OR($B$5="仕入",$B$5="転受",$B$5="繰越",$B$5="訂正増"),B11,B11*-1)

「処理区分」が「仕入」「転受」「繰越」「訂正増」のいずれかの場合は販売金額セル「B11」をそのまま表示、それ以外は「販売金額」に「-1」を掛けた金額を表示。という意味になります。

入力箇所は「倉庫ID」「商品ID」「処理区分」「数量」のみです。あとは入力した時点で、自動で単価や金額が計算されるように設定がされました。

最後に、入力支援マクロ「dhenkan」マクロを図形（星にしてみました。）に登録して、項目設定の終了になります（「データ変換①～コードを貼り付ける」及び「データ変換②～dhenkanマクロの使い方」）を参照してください。



毎日の在庫データを入力する準備が整いました。

次回は実際に入力し、確認をします。

ピボットテーブルで在庫管理②～データ入力・・・・・へつづく

ピボットテーブルで在庫管理②～データ入力

ピボットテーブルで在庫管理①～項目設定からのつづき

在庫を管理するための項目設定ができました。



項目に沿ってデータを入力し、「dhenkan」マクロを登録した図形をクリックすることでデータをどんどん追加することができます（「データ変換①～コードを貼り付ける」と「データ変換②～dhenkanマクロの使い方」を参照）。




まず最初に入力するデータは、現在の在庫を入力することですね。

年度初めと想定し、処理区分を「繰越」で入力します。



倉庫ごとに商品、数量を入力すると、他の項目は自動計算されます。



入力できたら図形をクリック！

「データ」シートにはこのようにデータが追加されていきます。




繰越処理が終わったら、別な処理区分で入力してみます。

「弘前」倉庫から「弘前家具店」に「A品」を販売しました。



在庫項目に注目すると・・・・・。

「販売」は在庫がすべてマイナス（減）になります。

図形をクリックし、データを追加します。


少し破損があったので、１，０００円程「値引き」しました。

値引きの場合は、「数量」欄を「値引き額」として利用します。



「値引き」は販売額（売価）だけマイナス（減）になります。

図形をクリックし、データを追加します。



データは追加されてますね。


弘前の倉庫から青森倉庫へ「A品」を「10」個「転送」します。取引先へは「青森」と入力しておくとわかりやすいですね。



転送は在庫減ですね。図形をクリックし、データを追加します。



青森倉庫側は、弘前からの転送を受ける形になるので、処理区分は「転受」になります。

数量を「10」と入力します。



転受は在庫増ですね。図形をクリックし、データを追加します。


このようにデータが追加されます。



「転送」「転受」は個々の倉庫では増減がありますが、全体としてはプラマイ「０」になります。


このように毎日の在庫の動きを入力していき蓄積されたデータを使うことで在庫管理ができるわけです。

今回は、全倉庫のデータを一つのブックで管理していますが、倉庫ごとにデータ管理してもいいですね。


ピボットテーブルで在庫管理③～データ範囲（名前の定義）・・・・・につづく

ピボットテーブルで在庫管理③～データ範囲（名前の定義）

ピボットテーブルで在庫管理②～データ入力からのつづき

毎日の在庫の動きを入力し、データを蓄積していく体制が整いました。



あとは、そのデータを使い集計するだけです。

ピボットテーブルは、指定したデータ範囲から、さまざまな角度で集計できる優れものの機能です。

今回は、ピボットテーブルの作成に入る前に、設定しておくと便利な機能を解説します。

上記でも説明したように、ピボットテーブルは指定したデータ範囲からさまざまな集計をする機能です。このデータ範囲が曲者です。

データは毎日追加または削除されたりもするでしょう。

そう、増えたり減ったりします。

そういった動きをピボットテーブル側は認識してくれません。つまり、最新の情報を得るためには、データが追加や削除などの変更があるたび、データ範囲を指定し直す作業が必要になります。

この作業を忘れると間違った情報を社員やお客様に提供しかねません。

そこで、関数を使いデータの動きに合わせて範囲が自動で変更されるようにします。

使う関数は、以前当ブログでも解説したOFFSET関数（「使える関数～OFFSET関数」参照）です。

引数は次のとおりです。

OFFSET(①基準,②行数,③列数,④高さ,⑤幅)

前回の解説では、①基準から、②行、③列数を指定し、セル位置を特定し、そのセルに入力された値を獲得するところまで解説しました。

今回はさらに、④高さと⑤幅を利用し、データ範囲を獲得します。

例えばこんな引数 「OFFSET(①A1,②2,③2,④4,⑤4)」 を指定すると、図の色が付いている範囲が選択されます。



まず①基準値セル「A1」から②２行目、③２列目のセル（赤い部分）が特定されます。

そして特定されたセル（赤い部分）から④高さ４行で⑤幅が４列の範囲が選択されます。

このOFFSET関数の特性を活かし、データ範囲を獲得します。

さてこの関数をどこに入力するのか、という問題がありますが、Excelには「名前の定義」というのがあり、データ範囲に名前を付けて管理できる機能があります。

ここに、データ範囲の名前と参照範囲を指定しておき、後にピボットテーブルで利用します。


「データ」シートに切り替え、「数式」タブの「定義された名前」グループから「名前の定義」をクリックします。（Office2007での解説になります。）

※Office2003では「挿入」メニューから「名前」→「定義」




「新しい名前」ダイアログボックスが表示されます。名前の欄に「在庫データ範囲」と入力します。（任意でかまいません）




参照範囲の欄に移り、「＝OFFSET（」まで入力します。

在庫データを確認します。（※ピボットテーブルはフィールド名も含めてデータ範囲とします。）

基準セルは「A1」になります。セル「A1」をクリックします。「,」で区切りながら指定していきます。

今回は特定のセルからデータ範囲を指定する必要がないので、②行と③列は「０」になります。



=OFFSET（データ!$A$1,0,0,

基準セルが「A1」で、行列が「０」指定なので基準値「A1」からすべてが始まります。


④の高さですが、これは行数の指定になります。

ここはデータの増減により変化します。

こういう場合は、COUNT関数を使い、入力されているデータ数をカウントすることで、常に最新のデータ数（行数）を獲得するようにします。

フィールドも含めてカウントするので、COUNTA関数を使います。（※COUNTA関数は数値の他、文字もカウントできます。）

「COUNTA（」と入力し、列番「A」をクリックします。



=OFFSET(データ!$A$1,0,0,COUNTA(データ!$A:$A),

列名だけ指定すると列全体を指定したことになります。


最後に⑤幅です。ここはフィールド数です。「A列」から「O列」までの15列あります。



したがって「15」と直接入力します。



=OFFSET(データ!$A$1,0,0,COUNTA(データ!$A:$A),15)

基準セル「A1」からA列に入力されたデータ数（行数）と幅15列の範囲を参照するという意味になります。

「OK」をクリックし、「名前の定義」の操作は終了です。

一応「名前の管理」をクリックして確認しましょう。





登録されてますね。

この名前「在庫データ範囲」をピボットテーブルのデータ範囲として使います。

これでデータ数に増減があってもデータ範囲を指定し直す必要がなくなりました。

ピボットテーブルで在庫管理④～ピボットテーブルを作成する・・・・・・へつづく

ピボットテーブルで在庫管理④～ピボットテーブルを作成する

ピボットテーブルで在庫管理③～データ範囲（名前の定義）からのつづき

前回はデータの増減に合わせて範囲が変化する「在庫データ範囲」を定義しました。

それでは、ピボットテーブル作成に入ります。

ピボットテーブルは指定したデータ範囲を使い2次元の集計（クロス集計ともいう）を簡単に行うことができる機能です。

今回は商品在庫を倉庫別に一目でわかる集計表を作成します。

「データ」シートを選択します。

「挿入」タブの「テーブル」グループから「ピボットテーブル」ボタンの「ピボットテーブル」をクリックします。




ダイアログボックスの「テーブル/範囲」に作成した名前「在庫データ範囲」と入力します。

ピボットテーブルを作成する場所は、「新規ワークシート」でいいでしょう。もしピボットテーブル用にシートを作成した場合は、「既存のワークシート」を選択します。




左にテーブル作成用の領域（集計エリア）、右の作業ウィンドウには「データ」シートのフィールド名が入った「フィールドリスト」が表示されます。



「フィールドリスト」の下の欄には、集計に使用するフィールドを格納するボックスが表示されます。このボックスに必要なフィールドをドラックするだけで集計表が作成されます。




実際に始める前に、一つだけ作業を行います。

Office2007になって、このピボットテーブルの機能もかなり向上しました。しかし、以前のバージョン（Office2003）の機能の方が使いやすい部分もあります。

したがって、新しい機能も使い、以前の良い部分も使えるように設定を変更します。

左の集計エリアで右クリックし、「ピボットテーブルオプション」をクリックします。



ダイアログボックス「表示」タブの「従来のピボットテーブルレイアウト・・・・・・」にチェックを付けて「OK」をクリックします。



「Office2003」バージョンのレイアウト表示に変わります。


それでは、集計をしましょう。

「フィールドリスト」から、このようにボックスにフィールドをドラックします。

※Office2003では直接集計エリアにドラックします。



列ラベル・・・・「倉庫」～　列（右方向）に倉庫名がすべて表示されます。

行ラベル・・・・「商品ID」「商品」～　行（下方向）にIDと商品名が表示されます。※IDがあったほうが、似たような商品名や並び替えのとき便利です。

値・・・・・・・「在庫数」　～　集計するフィールドです。

もう集計表は完成しています。（早いですね～）

ここからは細かい設定が必要になります。（列幅等は任意で調整しましょう。）

まずは商品IDごとに小計が算出されてます。これは必要がないので削除しましょう。



「商品ID」（どのIDでもいいです。）で右クリックします。メニューの「商品IDの小計」にチェックがついてますので、クリックします。



「商品ID」の小計が削除されました。これはいつでも復活できます。




次は、在庫数の集計表示を「,」付きに変更しましょう。

集計エリアで右クリックし、「表示形式」をクリックします。



書式設定のダイアログボックスが表示されますので、分類から「数値」、桁区切りを使用するにチェックを入れます。




倉庫名を並び替えましょう。一応「青森、弘前、八戸」の順にしたいので、弘前を移動します。



フィールド「弘前」上で右クリックし、「移動」から「弘前を右に移動」をクリックします。



最初はこういう細かい作業は仕方ないですね。がんばりましょう。

さあ、それではデータを入力して在庫数の変化を確かめましょう。

八戸倉庫のA品に注目しましょう。



「八戸倉庫で「A品」を八戸家具から20個仕入れた」と想定しましょう。

このようにデータを入力し、追加します。



ピボットテーブルのシートへ戻り確認しましょう。

まだ変化はないです。データ範囲は、増減があっても再指定しなくてもいいように設定しました。




あとは「更新」操作を行いテーブルにデータを反映させるだけです。

テーブル上で右クリックし、メニューから「更新」をクリックします。



八戸倉庫へ「A品」が「20」個追加され、総計も再計算されています。



これで全倉庫の在庫数が一目で確認できるようになりました。

普段はデータを1つ入力して毎回更新するという必要はなく、まとめて入力して、一回の更新でOKです。

それから、毎日の仕事では複数の人がデータを追加したり削除したりする場合もあると思います。そうなると表示されているピボットテーブルが最新かどうか確認できないこともあるので、必ず「更新」の操作を行ってから作業をしましょう。


今回は右クリックメニューからいろんな操作を行いましたが、ピボットテーブルを選択すると表示される「ピボットテーブル　ツール」からも同じ操作ができます。



「デザイン」タブでは、ピボットテーブルに様々なスタイルを適用し、見栄えのいい表を作ることができます。

試してみてください。

ピボットテーブルで在庫管理⑤～いろんなパターンを試そう！・・・・・へつづく