Excel数据透视表制作库存表一例

Excel数据透视制作库存表一例 问题:我需要用Excel做产品的库存表，这样我可以清楚产品进出数量以及库存。如果要在一个Excel工作簿中，用一张sheel1做进货表，另一张sheel2做出货表，然后sheel3就可以看到总的库存。能否问一下类似这种表如何制定, 这种库存表可以用数据透视表中的“多重合并计算数据区域”功能和添加计算项来实现，并且通过名称定义给数据透视表设置一个动态数据源，这样当在进货表和出货表中更新记录时，可以通过刷新数据透视表来更新结存数据。 下面以Excel 2003为例，来介绍一下其制作。假如工作簿中包含三个工作表，分别是“进货”、“出货”及“结存”。在“进货”表中A列是“货号”，B列为“进仓”，即进货数量;在“出货”表中A列是“货号”，B列为“出仓”，即出货数量;“结存”表为总的库存情况。 ships of 72 and a half the Harbour, like room Tao Yuan. Triangle at the cemetery (Shanghai, Nanjing and Hangzhou), and the small triangle (Soviet Union, Ka, Lake) where the hinterland, at the junction of Jiangsu, East, South and west sides respectively, and jiaxing in Zhejiang Province, Wuzhen, Nanxun town and connected. After the liberation, 17 townships, Yan Tomb, provided there is a town, District Administration had in town. A unique geographical environment, has become the military's battleground. On November 5, 1937, Japan 10th army landed in Hangzhou Bay, North Bay, on November 12, Shanghai fell. Part of the Japanese army to the North along the flood of jiaxing railway, Jiujiang road, invaded Wu Jiang. On November 18, the Japanese army occupied Wujiang city. Stationed in Wujiang's 一、定义区域名称作为数据透视表的动态数据源 在“结存”表中使用数据透视表可以直接得到各货号的汇总数量，十分方便。但当在“进货”表和“出货”表中添加新的数据后，数据透视表不会自动更新。为此，可以给数据透视表设置一个动态数据源，让其在刷新后自动更新数据源。 在“进货”表中，单击菜单“插入?名称?定义”，在“定义名称”对话框中定义一个名称，例如“进货”，在“引用位置”下输入公式: =OFFSET(进货!$A$1,0,0,COUNTA(进货!$A:$A),2) 注意本例进货表中要作为数据源的区域仅有两列，公式中用红色标识的数字为“2”。 ships of 72 and a half the Harbour, like room Tao Yuan. Triangle at the cemetery (Shanghai, Nanjing and Hangzhou), and the small triangle (Soviet Union, Ka, Lake) where the hinterland, at the junction of Jiangsu, East, South and west sides respectively, and jiaxing in Zhejiang Province, Wuzhen, Nanxun town and connected. After the liberation, 17 townships, Yan Tomb, provided there is a town, District Administration had in town. A unique geographical environment, has become the military's battleground. On November 5, 1937, Japan 10th army landed in Hangzhou Bay, North Bay, on November 12, Shanghai fell. Part of the Japanese army to the North along the flood of jiaxing railway, Jiujiang road, invaded Wu Jiang. On November 18, the Japanese army occupied Wujiang city. Stationed in Wujiang's 同样，在“出货”表中定义另一个名称，本例为“出货”，其公式为: =OFFSET(出货!$A$1,0,0,COUNTA(出货!$A:$A),2) 二、建立数据透视表 1.添加多重合并计算数据区域 选择“结存”工作表中需要放置数据透视表的某个单元格，单击菜单“数据?数据透视表和数据透视图”，打开“数据透视表和数据透视图向导”对话框，选择“多重合并计算数据区域”。 ships of 72 and a half the Harbour, like room Tao Yuan. Triangle at the cemetery (Shanghai, Nanjing and Hangzhou), and the small triangle (Soviet Union, Ka, Lake) where the hinterland, at the junction of Jiangsu, East, South and west sides respectively, and jiaxing in Zhejiang Province, Wuzhen, Nanxun town and connected. After the liberation, 17 townships, Yan Tomb, provided there is a town, District Administration had in town. A unique geographical environment, has become the military's battleground. On November 5, 1937, Japan 10th army landed in Hangzhou Bay, North Bay, on November 12, Shanghai fell. Part of the Japanese army to the North along the flood of jiaxing railway, Jiujiang road, invaded Wu Jiang. On November 18, the Japanese army occupied Wujiang city. Stationed in Wujiang's 单击“下一步”按钮，在对话框中直接单击“下一步”按钮。 弹出“数据透视表和数据透视图向导 3步骤之2b”对话框。在“选定区域”下分 别输入上述所定义的区域名称，并分别单击“添加”按钮添加上述两个区域。 ships of 72 and a half the Harbour, like room Tao Yuan. Triangle at the cemetery (Shanghai, Nanjing and Hangzhou), and the small triangle (Soviet Union, Ka, Lake) where the hinterland, at the junction of Jiangsu, East, South and west sides respectively, and jiaxing in Zhejiang Province, Wuzhen, Nanxun town and connected. After the liberation, 17 townships, Yan Tomb, provided there is a town, District Administration had in town. A unique geographical environment, has become the military's battleground. On November 5, 1937, Japan 10th army landed in Hangzhou Bay, North Bay, on November 12, Shanghai fell. Part of the Japanese army to the North along the flood of jiaxing railway, Jiujiang road, invaded Wu Jiang. On November 18, the Japanese army occupied Wujiang city. Stationed in Wujiang's 单击“完成”按钮在“结存”表中建立数据透视表。 2.去掉行总计和列总计 本例中不需要默认的数据透视表行总计和列总计，可以通过下面的方法将其 去掉:右击数据透视表，在快捷菜单中选择“选项”，在弹出的“数据透视表 选项”对话框中取消勾选“行总计”和“列总计”。 ships of 72 and a half the Harbour, like room Tao Yuan. Triangle at the cemetery (Shanghai, Nanjing and Hangzhou), and the small triangle (Soviet Union, Ka, Lake) where the hinterland, at the junction of Jiangsu, East, South and west sides respectively, and jiaxing in Zhejiang Province, Wuzhen, Nanxun town and connected. After the liberation, 17 townships, Yan Tomb, provided there is a town, District Administration had in town. A unique geographical environment, has become the military's battleground. On November 5, 1937, Japan 10th army landed in Hangzhou Bay, North Bay, on November 12, Shanghai fell. Part of the Japanese army to the North along the flood of jiaxing railway, Jiujiang road, invaded Wu Jiang. On November 18, the Japanese army occupied Wujiang city. Stationed in Wujiang's 3.在列字段中添加计算项 计算项使用自定义的公式，对数据透视表中相同字段的其他项的进行计 算。本例中数据透视表的列字段中只有“进仓”和“出仓”两项，现在需要添加一个 计算项“结存”，用来反映结存情况。选择数据透视表中列字段，或选择列字段下 方的B4或B5，即包含“进仓”或“出仓”字样的单元格。 在“数据透视表”工具栏中依次单击“数据透视表?公式?计算项”。 ships of 72 and a half the Harbour, like room Tao Yuan. Triangle at the cemetery (Shanghai, Nanjing and Hangzhou), and the small triangle (Soviet Union, Ka, Lake) where the hinterland, at the junction of Jiangsu, East, South and west sides respectively, and jiaxing in Zhejiang Province, Wuzhen, Nanxun town and connected. After the liberation, 17 townships, Yan Tomb, provided there is a town, District Administration had in town. A unique geographical environment, has become the military's battleground. On November 5, 1937, Japan 10th army landed in Hangzhou Bay, North Bay, on November 12, Shanghai fell. Part of the Japanese army to the North along the flood of jiaxing railway, Jiujiang road, invaded Wu Jiang. On November 18, the Japanese army occupied Wujiang city. Stationed in Wujiang's 如果未看到数据透视表工具栏，可以单击菜单“视图?工具栏?数据透视表”将其显示出来。 弹出“在„列?中插入计算字段”对话框。在“名称”右侧的文本框中输入一个名称，如“结存”，在“项”下选择“进仓”，单击“插入项”按钮将其添加到“公式”右侧的文本框中，接着在其后输入一个英文减号“-”，再次在“项”下选择“出仓”，单击“插入项”按钮将其添加到“公式”右侧的文本框中。这时公式变为: =进仓- 出仓 ships of 72 and a half the Harbour, like room Tao Yuan. Triangle at the cemetery (Shanghai, Nanjing and Hangzhou), and the small triangle (Soviet Union, Ka, Lake) where the hinterland, at the junction of Jiangsu, East, South and west sides respectively, and jiaxing in Zhejiang Province, Wuzhen, Nanxun town and connected. After the liberation, 17 townships, Yan Tomb, provided there is a town, District Administration had in town. A unique geographical environment, has become the military's battleground. On November 5, 1937, Japan 10th army landed in Hangzhou Bay, North Bay, on November 12, Shanghai fell. Part of the Japanese army to the North along the flood of jiaxing railway, Jiujiang road, invaded Wu Jiang. On November 18, the Japanese army occupied Wujiang city. Stationed in Wujiang's 单击“确定”按钮，这时数据透视表中的列字段中将添加一个计算项“结存”，它 可以反映各种货号的结存情况 如果有必要还可以修改行字段名称，例如将其改为“货号”。方法是右击行字 段或行区域，选择“字段设置”，在弹出的“字段设置”对话框中修改名称即可。 最后调整“进仓”和“出仓”项的位置，使数据透视表看上去更加直观。右击C4 单元格，在弹出的快捷菜单中依次选择“顺序?向左移”。 ships of 72 and a half the Harbour, like room Tao Yuan. Triangle at the cemetery (Shanghai, Nanjing and Hangzhou), and the small triangle (Soviet Union, Ka, Lake) where the hinterland, at the junction of Jiangsu, East, South and west sides respectively, and jiaxing in Zhejiang Province, Wuzhen, Nanxun town and connected. After the liberation, 17 townships, Yan Tomb, provided there is a town, District Administration had in town. A unique geographical environment, has become the military's battleground. On November 5, 1937, Japan 10th army landed in Hangzhou Bay, North Bay, on November 12, Shanghai fell. Part of the Japanese army to the North along the flood of jiaxing railway, Jiujiang road, invaded Wu Jiang. On November 18, the Japanese army occupied Wujiang city. Stationed in Wujiang's 当在“进货”及“出货”表中添加数据后，在“结存”表中的数据透视表工具栏中单 击“刷新”按钮即可更新数据。 ships of 72 and a half the Harbour, like room Tao Yuan. Triangle at the cemetery (Shanghai, Nanjing and Hangzhou), and the small triangle (Soviet Union, Ka, Lake) where the hinterland, at the junction of Jiangsu, East, South and west sides respectively, and jiaxing in Zhejiang Province, Wuzhen, Nanxun town and connected. After the liberation, 17 townships, Yan Tomb, provided there is a town, District Administration had in town. A unique geographical environment, has become the military's battleground. On November 5, 1937, Japan 10th army landed in Hangzhou Bay, North Bay, on November 12, Shanghai fell. Part of the Japanese army to the North along the flood of jiaxing railway, Jiujiang road, invaded Wu Jiang. On November 18, the Japanese army occupied Wujiang city. Stationed in Wujiang's