タイトル 第３章　ロータス１−２−３　入門　（３）　　棚卸表
　

  第３章　エクセル　入門　（３）　　棚卸表

使用関数　　合計関数＝ＳＵＭ　表引関数＝ＶＬＯＯＫＵＰ　データベース関数＾ＤＳＵＭ

企業では期末に必ず実地棚卸を行います。

実地棚卸では現品票に商品数量と品名コードを記入します。　この現品票をコンピュータに入力

して、実地棚卸表が作成されされます。

（１）実地棚卸表はフィールド（アイテム・項目・カラム）を１．商品コード　２・数量３・品名

４・単価５・金額で構成します。完成した表は下記のようになります。

実地棚卸表

　Ａ　　　　　　　　Ｂ　　　　　　　　　　　　Ｃ　　　　　　　　　　　　　Ｄ　　　　　Ｅ

商品コード 数量 品名 単価 金額

商品コードと数量は手入力します。

品名・単価は＝ＶＬＯＯＫＵＰ関数を使って自動計算させます。

金額も四則演算で自動計算します。

金額合計は＝ＳＵＭ関数で自動計算させます。

実地棚卸表の範囲は ＄Ａ＄３．．＄Ｅ＄１００とします。

（２） 表引関数＝ｖｌｏｏｋｕｐの使い方を説明

 目的は商品コードと数量が入力されたら、＝ｖｌｏｏｋｕｐ関数で商品名と単価を自動的に表引きし、数量と単価の乗算をします。

表引関数＝VLOOKUP(照合値,照合範囲,列位置)は、

照合値を指定し、照合範囲から、列方向に照合値に合致する指定した列位置にあるデータを

上から下に探してを取り出します。 
           
＝VLOOKUP(A4,$A$222$C$300,1)  

　　　　　　　　　照合値、照合範囲、列位置    


照合値＝Ａ４　　Ａ４はセル位置で、セルＡ４にあるデータを照合値とするという意味です。

Ａ４のセルの内容が　たとえば商品コードである　３　だとします。

        
照合範囲は商品テーブルの構成範囲$A$222..$C$300です。＄マークはその位置を変

化しないで固定させるために付けます。この場合Ａ列２２２行 とＣ列３００行とをに固定します。

この照合範囲は検索の対象とセル範囲で、左上から右下に見たセル番地で指定しています。検索対象が数値の場合は小から大へ昇順に並べておきます。

 
位置＝1    　商品テーブルから品名を取り出すための列位置を指定します。

商品テーブルの構成はセル位置Ａ２２２が右上端部でセル位置Ｃ３００が右下端部です。商品

テーブルの列位置は左端から０、１、２、３と数えます。

（商品テーブル：この範囲で企業の商品一覧表を構成しています。これは、棚卸の前に

作成されていなくてはなりません。）

商品テーブル

　　Ａ　　　　　　　　　Ｂ　　　　　　　　　　　　　　　Ｃ

商品コード 品名 単価
1
 デジタル最小目量 １gのハカリ 3000

    0                     1                                     2              (位置）

従って、照合値Ａ４に照合するデータは、位置＝１とすると品名が、位置＝２とすると 単価が選

択され取り出されます。  

 

（３）下記は表計算ロ−タス１−２−３で作成した実地棚卸表です。これは商品コードと数量がすべて、棚卸現品票によりインプットされると自動的に作成されることになります。

ピンク色のところ和データとして手入力され、イェローのところ品名・単価・金額および合

計は自動計算されています。

各欄に（２）で説明した関数が埋め込まれたからです。この例では関数は一箇所に記入し

たらコピー機能で複数箇所に展開できます。ただし引用の相対変化と絶対値の関係を理

解しなければなりません。

商品実地棚卸表

　　Ａ　　　　　　　Ｂ　　　　　　　　　　　　　Ｃ　　　　　　　　　　　Ｄ　　　　　　　　　Ｅ

 

棚卸年月日            
高津商店 2005/03/31   合計  7,276,500              
商品コード 数量 品名 単価 金額
2 2 ■最小目量 ２gのハカリ 3500 7,000              
8 3 ■最大計量 ８kgのハカリ 30000 90,000        
9 4 ■最大計量 １２kgのハカリ 40000 160,000            
11 5 ■最大計量 ３０kgのハカリ 60000 300,000          
12 7 ■最大計量 ４０kgのハカリ 80000 560,000            
15 2 ■最小目量 ２gのハカリ 6000 12,000  
16 1 ■最大計量 500gのハカリ 7000 7,000      
9 2 ■最大計量 １２kgのハカリ 40000 80,000              
8 3 ■最大計量 ８kgのハカリ 30000 90,000              
11 3 ■最大計量 ３０kgのハカリ 60000 180,000              
20 5 ■最大計量 ５kgのハカリ 25000 125,000              
21 10 ■最大計量 ８kgのハカリ 50000 500,000      
22 5 ■最大計量 １２kgのハカリ 100000 500,000              
23 3 ■最大計量 ２０kgのハカリ 150000 450,000          
25 4 ■最大計量 ４０kgのハカリ 250000 1,000,000              
26 8 ■最大計量 ５０kgのハカリ 300000 2,400,000            
27 1 ■最小目量 ２gのハカリ 3500 3,500            
28 10 ■最大計量 ４０kgのハカリ 80000 800,000        

29

 

 4 デジタル最小目量 １gのハカリ 3000 12,000              
    0              
    0            
    0              
    0              
    0              
    0              
    0              
    0              

　

 

 (４)＝dsum関数の説明です。

＝ｄｓｕｍ関数の説明        
@DSUM(対象範囲,フィールド,位置,[条件範囲])はデータベーステーブルのフィールド位置
の中で、指定された条件範囲を満たす数値の合計を計算します。    
引数 １・対象範囲２・フィールド位置３・条件範囲    
           
           @
@ｄｓｕｍ関数の例示          
「売上げ」という名前のデータベーステーブルには東京、大阪、名古屋    
での5月の住宅の売上げが入っています。このデータベーステーブル    
の手数料という名前のフィールドには不動産業者の手数料が入ってい  
います。東京での不動産業者の手数料の合計を求めるには、次の式を使います。  
  A B C    
@DSUM(売上げ,"手数料",支店="東京") → 25,480      
A B C    
           
1 日付 支店 手数料    
2 5 大阪 28,800    
3 .5 名古屋 12,720    
4 .5 大阪 19,920    
5 １０ 名古屋 11,120    
6 １０ 東京             9 ,600    
7 １２ 名古屋 13,620    
8 １２ 東京 15,880    
           
9 １５ 大阪 12,120    
           

　

（５）エクセル２０１０で作成した商品テーブルを利用した商品種類別実地棚卸合計表です。

 ＝dsum($実地棚卸表,"金額",商品コード=１)または

＝dsum($A$3..$E$100,"金額",商品コード=１) の算式をＤ４のセルに書き込む。

次にＤ５のセルに"商品コーＦド"＝２と変更して書きこむ。

以下順次商品コードを３，４，５...と変更しながら最後まで書きこむ。

D欄に商品棚卸合計が自動的に計算される。

商品テーブル兼商品別棚卸合計票 
　　Ａ　　　　　　　Ｂ　　　　　　　Ｃ　　　　　　　　　Ｄ　　　　　　Ｅ　　　

   0                1              2
商品テーブル $A$222$C$300   棚卸表        
商品コード 品名 単価 合計
7,276,500    
1 デジタル最小目量 １gのハカリ 3000 12,000  
   
   
2 ■最小目量 ２gのハカリ 3500 10,500            
3 ■最大計量 500gのハカリ 4000 0            
4 ■最大計量 １kgのハカリ 5000 0          
5 ■最大計量 ２kgのハカリ 10000 0    
6 ■最大計量 ４kgのハカリ 15000 0    
7 ■最大計量 ５kgのハカリ 20000 0  
8 ■最大計量 ８kgのハカリ 30000 180,000  
9 ■最大計量 １２kgのハカリ 40000 240,000      
10 ■最大計量 ２０kgのハカリ 50000 0      
11 ■最大計量 ３０kgのハカリ 60000 480,000    
12 ■最大計量 ４０kgのハカリ 80000 1,360,000            
13 ■最大計量 ５０kgのハカリ 100000 0    
14 アナログ'最小目量 １gのハカリ 5000 0

 

    
15 ■最小目量 ２gのハカリ 6000 12,000    
16 ■最大計量 500gのハカリ 7000 7,000    
17 ■最大計量 １kgのハカリ 10000 0    
18 ■最大計量 ２kgのハカリ 15000 0    
19 ■最大計量 ４kgのハカリ 20000 0    
20 ■最大計量 ５kgのハカリ 25000 125,000    
21 ■最大計量 ８kgのハカリ 50000 500,000            
22 ■最大計量 １２kgのハカリ 100000 500,000    
23 ■最大計量 ２０kgのハカリ 150000 450,000            
24 ■最大計量 ３０kgのハカリ 200000 0            
25 ■最大計量 ４０kgのハカリ 250000 1,000,000            
26 ■最大計量 ５０kgのハカリ 300000 2,400,000            
27     0            
28     0            
29     0            
ホームページ　　戸張会計　tobari-kaikei とばりかいけい　トバリカイケイ（キーワード検索)
戻る