タイトル 第3章 ロータス1−2−3 入門 (3)  棚卸表
 

  第3章 エクセル 入門 (3)  棚卸表

使用関数  合計関数=SUM 表引関数=VLOOKUP データベース関数^DSUM

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

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

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

(1)実地棚卸表はフィールド(アイテム・項目・カラム)を1.商品コード 2・数量3・品名

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

実地棚卸表

 A        B            C             D     E

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

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

品名・単価は=VLOOKUP関数を使って自動計算させます。

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

金額合計は=SUM関数で自動計算させます。

実地棚卸表 $A$3..$E$100とします。

(2) 表引関数=vlookupの使い方を説明

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

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

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

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

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


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

A4のセルの内容が たとえば商品コードである 3 だとします

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

化しないで固定させるために付けます。この場合A列222行 とC列300行とをに固定します。

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

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

商品テーブルの構成はセル位置A222が右上端部でセル位置C300が右下端部です。商品

テーブルの列位置は左端から0、1、2、3と数えます。

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

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

商品テーブル

  A         B               C

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

    0                     1                                     2              (位置)

従って、照合値A4に照合するデータは、位置=とすると品名が、位置=とすると 単価が選

択され取り出されます。  

 

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

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

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

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

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

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

商品実地棚卸表

  A       B             C           D         E

 

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

29

 

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

 

 

 (4)=dsum関数の説明です。

=dsum関数の説明        
@DSUM(対象範囲,フィールド,位置,[条件範囲])はデータベーステーブルのフィールド位置
の中で、指定された条件範囲を満たす数値の合計を計算します。    
引数 1・対象範囲・フィールド位置3・条件範囲    
           
           @
@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 10 名古屋 11,120    
6 10 東京             9 ,600    
7 12 名古屋 13,620    
8 12 東京 15,880    
           
9 15 大阪 12,120    
           

 

(5)エクセル2010で作成した商品テーブルを利用した商品種類別実地棚卸合計表です。

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

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

次にD5のセルに"商品コーFド"=2と変更して書きこむ。

以下順次商品コードを3,4,5...と変更しながら最後まで書きこむ。

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

商品テーブル兼商品別棚卸合計票 
  A       B       C         D      E   

   0                1              2

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

 

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