賢い主婦の買い物常識!  「スーパー底値管理」


匠への技

 「匠への技」第11弾は、主婦の皆さんのためのスーパー底値管理。 
熾烈な買い物合戦を生き抜くための賢い主婦を支援する「買い時を逃さない」をポイントとした第11弾。
パソコンレジスター同様、i一部VBAの力を借りています。
VBAシリーズではないので見所に加えることは心苦しいのですが、ワークシートイベントを駆使して作成しています。

関数としての見所は、HLOOKUP関数とCOLUMN関数です。
もう少し機能を補強したいところがあり完成品ではありませんがトクトご覧ください。

 画面デザイン       ブック名:takumivol-011.xls   シート名:底値OP、底値DB、底値LST

    セル書式の説明へ   1:B2,3,4  2:A列  3:B列  4:C列  5:D列  6:E列  7:F列  8:G列  9:P列
  

■ 仕 様 設 計

@ あらかじめ準備するテーブル
  • スーパーを管理するためのテーブル  (J列に近隣のスーパーを登録  最大25店舗)
  • 品目を分類するための品目分類  (M列に40分類を登録済み NO36,37,38は任意項目)
  • 品目分類に沿った品目を登録 1分類50品目。  品目分類NO.1米をP列としてNO.2牛肉をQ列.....以下40分類の順番に沿ってCOLUMN関数を使い列を1づつシフトさせて品目分類から代入しています。

A ちらしからのデータエントリー
 スーパーの特売チラシから品目毎データエントリーを行います。入力は、A列:スーパーの識別コード、B列:スーパー名、C列:品目分類コード、D列:品目分類名、E列:品目コード、F列:品目名、G列:単価の項目です。
品目を表すキーは、「品目分類コード+品目コード」となります。
今回の底値管理は、このエントリー方法に注目です。J列のスーパー名のテーブル欄、M列の品目分類名のテーブル欄、P列から始まる品目名のテーブル欄をダブルクリックすることで、データ入力を行うことができます。当然、コードを入力することもできます。

B 入力箇所
 日付とAで説明した項目が入力箇所です。


 使用する関数  
  1. IF(論理式,真の条件,偽の条件)   
  2. VLOOKUP(検索値,範囲,列番号,検索の型)
  3. INDIRECT(参照文字列,参照形式)
  4. HLOOKUP(検索値,範囲,行番号,検索の型)
  5. COLUMN(範囲)
・・・・・11回目登場
・・・・・5回目登場
・・・・・4回目登場
・・・・・初回
・・・・・初回



 解説
  1. セルB2,3,4  西暦年、月、日    (入力)                           <画面デザインへ>
    データ入力の対象年月日を入力します。
  2. A列  スーパーの管理コード    (入力)                             <画面デザインへ>
    スーパーの管理コードを入力します。J列のスーパー名欄の該当するスーパーの位置でのダブルクリックからデータ入力を行うこともできます。 
  3. B列  スーパー名    (非入力)                                  <画面デザインへ>
    入力したスーパーの管理コードを検索キーとしてスーパー管理テーブルからスーパー名を引用します。 
    関数式  =IF(A7="","",VLOOKUP(A7,$I$1:$J$41,2,FALSE))
    • 論理式 A7="" セルA7のスーパー管理コードが空欄の時は ""を代入します。
    • 偽の条件の場合は、VLOOKUP(A7,$I$1:$J$41,2,FALSE) スーパー管理コードを検索キーとしてテーブルからスーパー名を代入します。
  4. C列 品目分類コード  (入力)                                    <画面デザインへ>
    品目分類コードを入力します。M列の品目分類名欄の該当する品目分類の位置でのダブルクリックからデータ入力を行うこともできます。
  5. D列 品目分類名   (非入力)                                    <画面デザインへ>
    入力した品目分類コードを検索キーとして品目分類テーブルから品目分類名を引用します。 
    関数式  =IF(C7="","",VLOOKUP(C7,$L$2:$M$41,2,FALSE))
    • 論理式 C7="" セルC7の品目分類コードが空欄の時は ""を代入します。
    • 偽の条件の場合は、VLOOKUP(C7,$L$2:$M$41,2,FALSE) 品目分類コードを検索キーとしてテーブルから品目分類名を代入します。
  6. E列 品目コード   (入力)                                      <画面デザインへ>
    品目コードを入力します。品目分類に基づくP列以降の品目名欄の該当する品目の位置でのダブルクリックからデータ入力を行うこともできます。
  7. F列 品目名  (非入力)                                        <画面デザインへ>
    入力した品目コードを検索キーとして品目名テーブルから品目名を引用します。初めて登場したHLOOKUP関数は、VLOOKUPと同じ働きですがVLOOKUP 関数の "V" は、縦方向 (vertical) の検索に対してHLOOKUP関数
    の "H" は、横方向 (horizontal) の検索を意味します。
    ここでは品目分類をキーとした横列の検索となりHLOOKUPが機能します。
    関数式  =IF(OR(C7="",E7=""),"",HLOOKUP(D7,$P$1:$BC$51,E7+1,FALSE))
    • 論理式 OR(C7="",E7="") セルC7とE7の値が両方またはがいずれかが "" の場合は ""を代入します。
    • 偽の条件の場合は、HLOOKUP(D7,$P$1:$BC$51,E7+1,FALSE) 品目分類コードを検索キーとしてP列からBC列を範囲に品目コードセルE7に+1した値の位置の品目名を代入します。+1する理由は、品目テーブルが2行目から始まるので +1 が品目テーブルの基底値となります。
    • HLOOKUP関数の引数の内、FALSEは検索の型で完全一致を意味します。キーが一致しない場合は #N/A を返します。
  8. G列 金額    (入力)                                         <画面デザインへ>
    対象品目の金額(単価)を入力します。
  9. P列からBC列 品目テーブルのタイトル    (非入力)                     <画面デザインへ>
    品目テーブルのタイトルは、品目分類テーブル縦行から引用してます。
    関数式  =INDIRECT("M"&COLUMN()-14)
    • INDIRECTは()内d指定したセルの値を参照する機能です。品目分類名を参照しますのでM列になり "M" と文字定数で定義しています。&は文字の結合です。
    • 今回HLOOKUP同様初めて登場するCOLUMN関数列数を求めます。P列で=COLUMN()を実行すると 16 が返値となります。その値から -14 を減算している理由ですが、品目分類名は2行目から登録されています。と言うことは品目分類 1 の米は 2行目となりますので 14 を基底値とすれば本来の品目分類名の位置と照合することになります。次の牛肉で見た場合ですが品目分類コードは 2 です。Q列ですのでCOLUMN関数の値は17です。17-14の値を求めていますので 3 が返値です。
      このINDIRECTの関数式にあてはめると 「セルM3の値を代入しなさい」 となります。以降BC列まで同じ理屈です。
  10. 操作ボタン
    ちらしからめぼしい特売品の入力を完了したら登録ボタンをクリックします。
    この機能は、ワークシート「底値OP」の入力明細を「底値DB」へ追加登録します。
    登録後は、入力明細を初期化します。
    入力した明細の消去ボタンです。
    この機能は、消去したい明細をフォーカスしてクリックすればフォーカス行の明細を消去、明細外のセルをフォーカスしてのクリックは、全入力データの消去となります。
    品目>品目分類>スーパー の順に特売品を管理し、「買い」の判断基礎として底値データを作成します。
    但し、VBAの世界なので機能させていません。


 WebMambowから
 底値管理の活用ですがワークシート「底値DB」にデータが累積されますので、データ評価をオートフィルタで多様な検索を試みることができます。ご活用下さい。
尚、この仕様は冒頭にもお話の通り完全形ではありません。
仕様のアウトラインは、描いておりますがその域はVBAの世界となりますので趣旨方向からすると逸脱してしまいますので匠への技本来の関数の組み立てだけに止めました。


○「匠への技」VOL-11で作成したExcelブックのダウンロード

  演習できるExcelブックを用意しています。下図のダウンロードボタンをクリックして下さい。

     Excel Book Name: takumivol-011.xls  Download file: takumivol-011.lhz