Youtubeで公開している内容をまとめたものです。
フォーマットに関数を搭載する
こんにちは、JIMOVEの山口です。
今日はエクセルを使用して金額の計算方法についてご紹介します。金額は「単価 × 数量」で計算できますが、例えば、2,400円は1,200円の単価を2個分計算した結果です。
これを手入力するのではなく、自動化するため、単価と数量を入力すると自動で金額が計算されるよう設定します。
エクセルで計算をする際には、セルにイコール（=）記号から始めます。計算した結果を表示したいセルを選択し、例えば「1,200円×2」という計算式を入力します。
エクセルで乗算はアスタリスク（*）記号で表され、この方法で「=1,200*2（1,200円×2）」の計算を行うと、結果として2,400円が表示されます。
計算式のコピーと適用
複数行にわたる計算を効率よく行うため、セルの右下にカーソルを当てると黒十字マーク（フィルハンドル）を使って下方向にドラッグすると、式がコピーされます。
これにより、エクセルは自動的に行番号を調整し、適切な計算式を各行に適用してくれます。
例）A1→A2→A3・・・
IF関数の使用
まだ入力されていないセルに「0」と表示されるのを避けるため、IF関数を使います。これは、特定の条件（例えばセルが空白の場合）に基づいて、異なる動作をさせることができます。
空白のセルには計算を行わず、値が入力されたときのみ計算するように設定します。
簡単に言えば、IF関数で「もしセルが空白だったら何もしない、そうでなければ計算を行う」という命令を実行します。これにより、入力がないセルでは計算をスキップし、入力があるセルのみ計算を行います。
以上の方法で、エクセルを使った効率的な金額計算が可能になります。自動計算機能を活用することで、手動計算にかかる時間と労力を減らし、作業効率を向上させることができます。
請求書の計算方法
請求書の下部にある小計、消費税、合計の計算方法を説明します。
まず、小計については、金額の合計を求めます。
12行目から31行目までの合計を算出するため、SUM関数を使用します。イコール（=）から始めて、SUM関数と左括弧を入力後、合計したい範囲を選択します。右括弧を閉じてEnterを押すと、合計金額が表示されます。
消費税の自動計算
消費税の計算には、小計に対して現在の消費税率（8%）を掛けます。
※動画撮影時は2018年のため、8%でした。
イコールの後にセルを指定し、小計に0.08を掛ける式を入力します。
=F32*0.08Enterを押すと、消費税額が表示されます。
ただし、小数点以下の計算も重要なので、確認が必要です。「ホーム」タブで小数点以下の表示桁数を増やして確認すると、正確な消費税額が分かります。
例えば、細かい数値が入力された場合、小計と消費税が正確に計算されているかを確認します。
消費税の小数点以下をどう扱うかは、合計金額に影響します。まず、小計と消費税の合計を出してみましょう。
イコール（=）記号から始めて、小計と消費税のセル範囲をSUM関数で指定します。Enterを押すと、合計金額が表示されます。
この金額は、8,274円に662円を加えたものです。消費税は自動的に四捨五入されていますが、会社によっては切り捨てや四捨五入などの方法が異なります。
※法律上明確な記載はありません。
消費税の小数点処理
今回は消費税を切り捨てることにします。このために、INT関数を使用して小数点以下を切り捨てます。
INT関数は整数部分のみを残すため、消費税の小数点以下を簡単に処理できます。関数を設定して、Enterを押すと、消費税が661円として計算され、合計金額が8,935円になります。
一般的には切り捨てが多く使用されるため、この方法を説明します。
小数点以下の表示は説明のために小数点以下を表示していましたが、請求で小数点以下はないため、小数点以下を非表示に戻しておきます。これで、小計、消費税、合計の計算が完了しました。
合計金額を別の場所へも表示
合計金額は、別のセルにも表示させたい場合、イコール（=）記号を入力して対象のセルを選択します。
Enterを押すと、数式を入力したセルにも同じ合計金額が表示されます。請求する商品数が増えても、自動計算された合計が表示されるため、手間が省けます。
このように自動計算をすることで、入力の手間を減らし、効率化を図ることができます。
請求書のその他の自動化
請求書の他の部分の自動化についても検討します。
例えば、請求日は手入力されていますが、作成日に基づいて自動で日付を入力することも可能です。
TODAY関数を使用することで、現在の日付を自動的に表示できます。この関数は、特にパラメータを指定する必要はなく、単に括弧を入力するだけです。
これにより、請求書の作成日が自動的に反映されます。
=TODAY()Enterキーを押すと、現在の日付（例：10月7日）が請求書に自動で入力されます。
撮影日が10月7日であるため、今日の日付が表示されます。もし翌日（10月8日）にファイルを開けば、日付は自動的に更新されます。
このTODAY関数を請求書の請求日に使用することで、手入力の手間を省けます。
住所情報の自動化
次に、住所の自動化について考えます。
会社名が決まっている場合、郵便番号と住所はわかっているはずです。
別のシートに会社名、郵便番号、住所を一覧でデータを持っていれば、それらを請求書に自動で呼び出せます。
シートには、会社名、郵便番号、住所1、住所2を入力しておきます。これにより、請求書の郵便番号、住所、会社名が自動で入力されるように設定できます。
住所を請求書に呼び出す仕組みを実現するために、関数を使用します。
別の表からの呼び出し
会社リストを基に、請求書の該当部分にデータを呼び出します。リストには会社名、郵便番号、住所が仮のデータとして入力されています。
呼び出しの際、どのデータを基準にするかが重要です。
今回は「品川株式会社」の住所と郵便番号を例にします。会社リストでは、品川株式会社の情報が3行目にあり、その郵便番号と住所を請求書に呼び出す方法を説明します。
請求書に品川株式会社と入力されているため、これを基に情報を呼び出します。
ただし、請求書には「御中」という文字が追加されているため、この部分は別の方法で対応する必要があります。
品川株式会社のデータを呼び出すためには、VLOOKUP関数を使用します。
この関数は、指定された範囲の1列目で特定の値（この場合は品川株式会社）を検索し、同じ行にある指定された列の値を返します。
この関数の使用には、データが昇順で並んでいる必要がありますが、今回は一つのデータのみなので問題ありません。
=VLOOKUP(検索値,範囲,列番号,検索方法）「検索値」として品川株式会社を基準にします。
次に、「範囲」の会社名が表の1列目にあることが必要です。選択した範囲は、将来的に会社名が追加されることを考慮して、広めに設定します。
郵便番号を表示するセルには、2列目の内容（郵便番号）を表示させる必要があります。このため、「列番号」には「2」を入力します。
最後に、「検索方法」として、TRUE（近似一致）かFALSE（完全一致）を選択します。この場合は完全一致を選ぶため、FALSEを選択します。
※近似一致はあいまい検索のため、正しく表示されないことがあります。
関数を完了させると、郵便番号が自動的に入力されます。例えば、108-0074のように表示されることが確認できます。
これで、請求書の住所情報を自動的に呼び出す方法の基本が完成しました。
0の表示を防ぐ方法
会社名を入力して郵便番号と住所を呼び出す際、住所が未入力の場合、Excelは空白を0と認識し、0が表示されます。これを防ぐためには、VLOOKUP関数の結果が0の場合に何も表示させないように設定します。
IF関数の使用
この問題を解決するためには、IF関数を使用します。IF関数では、VLOOKUP関数の結果が0であるかどうかを確認し、0の場合は何も表示せず、0でない場合はVLOOKUP関数の結果を表示させます。
具体的には、「=IF(VLOOKUPの結果=0, “”, VLOOKUPの結果)」という形式で関数を設定します。
IF関数を設定すると、住所が未入力の場合、セルは空白として表示されますが、実際には式が組まれています。
この方法を使うことで、Excelの表示と中身をうまく活用し、効率化を図ることができます。
郵便番号と住所のセルにも同様の設定を適用することが可能です。
ただし、少なくとも住所の一部は入力されることが多いため、ここでは特に設定をしないことにします。これにより、住所2が存在する場合にのみ表示されます。
例えば、品川株式会社の場合、ビル名が住所2として入力されているので、これが表示されます。同様に、他の会社名で住所2がある場合も適切に表示されます。
見た目上の文字の変更
今日は、入力されている文字に見た目上だけ別の文字を追加する方法についてお話します。
例えば、会社名を基にVLOOKUP関数で郵便番号と住所を呼び出す際に、御中のような宛先を追加する方法です。直接入力するとエラーが発生するため、別の方法で対応します。
セルの書式設定を利用する
請求書の宛先に「御中」と書く必要がありますが、直接入力するとVLOOKUP関数が使えなくなります。
この問題を解決するために、セルの書式設定の機能を利用します。
セルを右クリックし、「セルの書式設定」→「表示形式」→「ユーザー定義」を選択し、種類の下の部分に直接入力します。
ユーザー定義で「@」を入力すると、これが「文字列」を表します。例えば、「@」の後にダブルクォーテーションで「御中」と入力すると、会社名の後に「御中」という文字が表示されます。
これにより、実際のセルの中身は会社名のみで、見た目上は「御中」が追加されます。
ただし、会社名と「御中」がくっついてしまうため、アットマーク（@）と「御中」の間にスペースを数個追加すると、スペースを空けることができます。
同じ原理で郵便番号にも郵便番号のマークを追加できます。セルの書式設定で「@」の前に郵便番号のマークを追加します。
これにより、見た目上は郵便番号のマークが付き、実際には関数が入力されている状態になります。
この方法により、実際に入力されている内容と表示される内容を切り替えることができ、様々な場面で有効活用できます。ぜひこの技術を活用してみてください。
入力の制限をかける
請求書に誤った会社名が入力されないように設定する方法をご紹介します。この設定は、会社リストの2行目から4行目の内容に基づきます。
データの入力規則の設定
請求書の入力したい部分を選択し、データタブの「データの入力規則」を選択します。ここで設定することにより、特定の値以外は入力できなくなります。
この方法では、リストから選択する項目を設定し、そのリストの範囲内のみの値を入力可能にします。
ドロップダウンリストの作成
「リスト」を選択すると、ドロップダウンリストからの選択が可能になります。このリストに表示させたい内容を入力します。
これは、先程の会社リストの2行目から4行目を参照することができます。
範囲を指定する際には「=会社リスト!$A$2:$A$4」のように入力します。これにより、会社リストシートのA列の2行目から4行目の内容だけが選択可能になります。
設定後、選択したセルには下向きの三角マークが表示され、設定した内容がドロップダウンリストとして反映されます。これにより、限定された内容のみが選択できるようになります。
会社リストが増えた場合は、範囲を再設定するか、初めから広めに範囲を設定することが可能です。たとえば、「=会社リスト!$A$2:$A$14」のように設定することで、将来の増加に対応できます。
範囲を「A列全体」とすることもできますが、これには「会社名」などのタイトルが含まれてしまう可能性があります。このため、通常は具体的な行範囲を指定することが好ましいです。
プルダウンメニューを使用することで、存在しない会社名が選択されるのを防ぎ、誤った会社名の入力を防ぐことができます。これにより、入力の誤りを減らすことができます。
まとめ
今回は請求書を作成する上で、便利になる日付の自動表示や、別の表からのデータの呼び出し、表示方法の編集などをお伝えしてきました。
実務では様々な入力に迫られますが、楽できるところはエクセルの自動化に任せるのが一番です。
様々な方法を模索していきましょう。
コメント