EXCELの凄技(2)
使いなれたEXCELで、こんなことが出来ますよ!
たとえば、自社で取り扱っている商品の売上台帳を作ろうとしたとき、その都度Excelの表に入力すれば、台帳としての機能はそれで果たせるかもしれません。しかし、商品名、カラー、サイズ、売り単価、仕入単価を1件ずつ入力するのは手間ではありませんか?
一度、商品台帳を作成しておけば、売上台帳の商品ID欄に、売れた商品のIDを入力するだけで、商品名、カラー、サイズ、売り単価、仕入単価を自動で商品台帳から持ってくることができます。数量はその都度変わるでしょうから、そこは手入力しますが、(売り単価-仕入単価)×数量の式を使って、粗利益まで簡単に求められます。
高度なデータベース構築アプリのひとつであるFileMakerとほとんど遜色ないものが、使いなれているExcelひとつで出来ます。
① まず商品台帳の表を作ってみましょう。
このような感じですか。
入力する際、Tabキーを活用していますか?
A1のセルに商品IDと入力してTabキーを押すと、右横のB1のセルに移動できます。
入力の時間が短縮できますよ!
② 便利な表作成機能を活用していますか?
商品IDから粗利益までのセルを選択し、「挿入」から「テーブル」を選択します。
すると、「テーブルの作成」画面が表示されます。「先頭行をテーブルの見出しとして使用する」にチェックを入れ、OKボタンをクリックします。
③ 次のような表の体裁が作られます。画面の右上のカラーを選択すると、色が変わります。
1行おきに色アミが自動で敷かれるので、行を見誤る間違いも極力減ります。
④ Tabキーを活用しながら、入力していきます。「粗利益」のところは=SUM(E2-F2)という引き算の結果を表示される式を入力しておきます。Tabキーを使って1行目の最後のセルまできたら、さらにTabキーを押すと、2行目の最初の「商品ID」が入力できます。このようにして「商品台帳」を作っていきます。アイテム数が多いとそれなりに最初の入力には時間がかかりますが、一度作ればあとの「売上台帳」作成時は時間が短縮できますので、頑張ってください。
⑤ シート下部のSheet1を商品台帳に、ついでにSheet2を売上台帳に書き換えておきましょう。
⑥ つぎは「売上台帳」の表を作成します。
このように入力したら、先ほどの「商品台帳」を作成した時と同じように、「挿入」から「テーブル」を選択し、色を茶系に変えてみます。
さて、次は少し難しいところへ入ります。
VLOOKUP関数を勉強しましょう。
⑦ VLOOKUP関数とは
「商品名」の下のセルを選択し、「数式」から「検索/行列」を選び、下の方の「VLOOKUP」を選択します。すると下記のように、「数式の引数」が表示されます。
B2のセル(商品ID)の下をクリックすると、「検索値」の欄には、「商品ID」と記されます。
次に、「範囲」の右横の四角をクリックし、「商品台帳」のシートに移り、A2のセルから対角線上にG11のセルまでの範囲を選択します。「範囲」のところには「テーブル1」記されます。
「列番号」には数字の2を入力します。これは「商品台帳」の左から2列目ということにあたります。
「検索方法」の欄には「false」と入力します。
次の図をご覧ください。
「OK」をクリックすると、このようになります。
「#N/A」と表示されますね。
これは「商品ID」が未入力だからです。
この表示を消すために、IFERROR関数というものを使用します。
現在、C2のセルを選択すると、= VLOOKUP([[商品ID ]],テーブル1,2,FALSE)と記述されていますね。この式に=IFERROR(VLOOKUP([[商品ID ]],テーブル1,2,FALSE),””) と赤文字の部分を追加してクリックしてください。
「#N/A」という文字が消えましたね。
同様に、「色」の項目にもVLOOKUP関数を指定します。この時、注意するのは「列番号」ですが、3と入力します。これは「商品台帳」の左から3列目に当たるからです。
「サイズ」も同じように指定します。
次の「数量」は手入力する項目ですから、空欄のままにして、「売値」、「仕入値」の欄を関数指定してください。
最後の「粗利益」は「売値」から「仕入値」を引いて、数量をかければ求められますので、=IFERROR((G2-H2)*F2,””) と入力してください。
こうすると次のような体裁の表が出来上がっているはずです。
試しに「売上年月日」に2018/5/10 、「商品ID」には10005 と入力してみてください。
このようになりましたか? 「粗利益」が¥0となっているのは、「数量」が空欄になっているからです。たとえば、3と入力してみましょう。
これで「粗利益」も、きちんと表示されましたね。
最後の「粗利益」のところでTabキーを押すと、自動的に次の行に移動でき、関数もそのままコピーされます。