Excel案例 药品库存管理.docx

1、Excel案例 药品库存管理 项目三 制作药品库存报表在我们的工作、学习中，经常会遇到许多表格，比如：患者各项化验单、账务结算单、医疗设备清单、职工工资表、药品库存统计表等，常常要用到对数据的计算、排序、汇总功能。利用Excel2003能方便的制作各种表格，并进行复杂的计算和统计，还能轻松的完成美化表格、自动生成图表。通过细化打印设置，可以准确实现各种数据的完美打印。本章就让我们一起来制作药品库存统计表，领略一下Excel的智能与高效。【学习目标】1.掌握数据输入的基本操作与技巧；2.熟练掌握数据的计算、排序和筛选；3.学会使用分类汇总和创建图表进行数据分析；4.掌握表格美化与打印的技巧。任务

2、1 药品库存表的建立在进行药品管理时，通常需要四张表格，分别是“初期库存盘点表”、“药品入库表”、“药品出库表”和“药品库存报表”。【任务实施步骤】步骤1 新建并保存工作簿“药品库存统计.xls”1.启动Excel（Excel的启动/退出方法与Word的启动/退出方法相同），自动新建一个空白的Excel文档，默认文件名是book1，扩展名是.xls。这时,大家看到的是Excel 2003的操作界面，窗口组成如下图2-3-1：图2-3-1 Excel窗口界面在Excel中，一个文档称为一个工作簿，一个工作簿默认包含三张工作表，每张工作表由多个行和列组成，行、列交叉构成的小方格称为单元格，每一个单

3、元格都有一个唯一的单元格地址相对应。单元格地址由确定单元格位置的列标和行号组成，如第A列第5行单元格地址为A5。矩形单元格区域的地址用“左上角单元格地址：右下角单元格地址”表示，如下图2-3-2所示的矩形单元格区域的地址可标志为C3:E8。图2-3-2 单元格区域(C3:E8)2.将文件保存为“药品库存统计表”。数据输入后要及时保存，只有保存后的数据才能被永久性地记录下来，以便今后继续使用。方法：单击“文件”菜单中的“保存”命令，在图2-3-3中的对话框选择保存位置，输入文件名，注意保存类型是*.xls，完成后单击“保存”按钮。图2-3-3 “另存为”对话框步骤2 将“Sheet1”改名为“初

4、期库存盘点表”，操作方法如下图2-3-4，完成后输入如下图2-3-5中的数据。图2-3-4 工作表重命名图2-3-5 单元格内换行单击A1单元格，输入“药品编号”，按Tab在下一单元格中输入“药品名称”。依次按Tab输入完第一行后，按回车活动单元格会出现在A2单元格。注意：在E1单元格输入“进价”后，要换到单元格内的下一行时需按下Alt+Enter，再输入“（元）”，如上图2-3-5。如需更改已输入的内容，需双击单元格进入编辑状态进行修改。对于有序数列的输入，可以使用智能填充功能。在A2中输入“20030015”后，选定A2并将指针指向A2右下角的填充柄，指针形状呈黑色实心十字时，按下Ctrl

5、键向下拖动到A6单元格松开左键，即可完成这一列数据的输入。不按Ctrl键向下拖动时，实现数据的复制。效果如下图2-3-6所示。图2-3-6 智能填充 拖动填充柄，也可以实现等差等比数据序列的填充，如下图2-3-7：图2-3-7 智能填充等差序列【知识拓展】1.不相邻的单元格中要输入相同的内容，方法如下图2-3-8。图2-3-8 不相邻单元格输入相同内容2.选定单元格方法a)选择一个单元格：将鼠标指针移到所要选定的单元格上，然后单击鼠标左键。b)选择连续单元格区域：将指针指向选定区域左上角单元格，然后按住鼠标左键，拖动鼠标至该区域右下角单元格，松开左键。如果选择的单元格区域较大，可以先单击所选区

6、域左上角的单元格，再按住Shift键的同时，单击选区右下角单元格。c)选择不连续单元格区域：选定第一个单元格或单元格区域，然后按住Ctrl键不放，再依次选定其他的单元格或单元格区域。d)选择整行或整列：单击某行的行号，可以选择该行。单击某列的列标，可以选择该列。在行号或列标上拖动鼠标，可以选定多行或多列。e)选定整个工作表中的单元格：单击行号与列标交叉处的表选择按钮，或使用快捷键Ctrl+A。3.修改单元格中数据选中需要修改的单元格后，就可以输入新内容覆盖原有内容；如果只是修改数据中的一部分，如将标题“药品库存表”改为“药品库存登记表”，可以双击要修改的单元格，进入编辑状态，对其中的内容进行修

7、改。步骤3 在表格中插入标题行和缺少的一列“单位”1. 在表格上方要插入一行写入标题“某诊所部分药品初期库存盘点表”。具体操作如下图2-3-9：图2-3-9 插入行2.在“产地”的左侧插入一列并输入“单位”，方法如下图2-3-10：图2-3-10 插入列【知识拓展】1）删除行和列 操作步骤如下。a)选定要删除的行或列。b)单击“编辑”菜单中的“删除”命令；或者单击鼠标右键，在弹出的快捷菜单中单击“删除”。 2）删除单元格操作步骤如下。a)选定要删除的单元格。b)单击“编辑”菜单中的“删除”命令；或者单击鼠标右键，在弹出的快捷菜单中单击“删除”。c)在打开的“删除”对话框中，根据需要选择相应的选

8、项，然后单击“确定”按钮，如下图2-3-11。图2-3-11 “删除”对话框步骤4 调整“单位”一列的宽度图2-3-10中“单位”一列的宽度较大，调整行高或列宽时可以使用手动调节，也可以使用菜单自动调整，方法如下图2-3-12：图2-3-12 调整列宽如果要设定准确数据的列宽，可以单击“格式”“列”“列宽”，然后在弹出的对话框中输入数据。调整行高的方法类似：可以将指针指向两行号之间的间隔线出现垂直调整指针时上下拖动，也可以使用菜单自动调整为“最适合的行高”，或者单击“格式”“行”“行高”，然后在弹出的对话框中输入数据。步骤5 在Sheet2、Sheet3工作表中输入“药品入库表”（图2-3-1

9、3）和“药品出库表”（图2-3-14）。图2-3-13 药品入库表图2-3-14 药品出库表1. 单击Sheet2工作表标签，此时标签呈白色，成为当前工作表，改名为“药品入库表”并开始输入数据。 其中，B2到C7的内容与第一张工作表“初期库存盘点表”的前两列数据相同，大家可以进行复制，避免重复的打字输入。复制数据的具体操作如下：（1）单击工作表“初期库存盘点表”的标签，选定A2：B7，指向选定区域右击，在弹出的菜单中单击“复制”命令；（2）单击工作表Sheet2，指向B2右击，在弹出的菜单中单击“粘贴”。2. 单击Sheet3工作表标签，改名为“药品出库表”并开始输入数据。“药品出库表”与“药

10、品入库表”的很多数据相同，大家可以将“药品入库表”复制后进行修改来完成。方法如下：（1）指向“药品入库表”标签，按下Ctrl的同时向右拖动，如下图2-3-15，松开左键后，出现“药品入库表（2）”，完成同一工作薄中工作表的复制；图2-3-15 工作表的复制（2）双击“药品入库表(2)”，改名为“药品出库表”，并对表内数据进行相应的修改。如下图2-3-16。图2-3-16 修改药品出库表【知识拓展】在使用Excel的过程中，经常要把工作表中的某部分内容从一处移动或复制到另一处，可以利用鼠标拖动或者通过菜单来完成。方法一：使用鼠标完成(1) 选定需要移动或复制的单元格。(2) 将鼠标指向选定区域的

11、选定框，鼠标指针变为。(3) 如果要移动选定的单元格，按住鼠标左键将选定区域拖动到目标区域的左上角单元格，然后释放鼠标。如果要复制选定的单元格，则需要在按住Ctrl键的同时拖动鼠标。方法二：通过菜单完成（1） 选定需要移动或复制的单元格。（2） 选择“编辑”菜单中的“剪切”或“复制”命令，也可以单击常用工具栏上的“剪切”或“复制”按钮。（3） 选定目标区域左上角单元格。（4） 选择“编辑”菜单中的“粘贴”命令，或者单击常用工具栏上的“粘贴”按钮。步骤6 插入新工作表，命名为“药品库存报表”，并输入如下图2-3-17的数据。图2-3-17 药品库存报表默认情况下，新建的工作簿中有3张工作表，用户

12、可以根据需要插入新的工作表，但一个工作簿中最多只能有255张工作表。要插入一张新的工作表，首先选定某一张工作表,如选定“药品出库表”作为插入的位置，然后单击“插入”菜单中的“工作表”命令，如下图2-3-18，一张新的工作表Sheet1就插入到当前工作表的左边。图2-3-18 插入新工作表也可以用鼠标右键单击工作表标签，在弹出的快捷菜单中选择“插入”来完成。此时，Sheet1出现在“药品出库表”的左边，要移动工作表的位置，可以将指针指向Sheet1的标签，按下左键向右拖动出现如下图2-3-19所示时松开左键，就可以将Sheet1移到“药品出库表”的右侧。图2-3-19 移动工作表Sheet1双击

13、Sheet1标签，改名为“药品库存报表”，将“初期库存盘点表”中的药品编号、药品名称、进价和售价复制到“药品库存报表”中，如上图2-3-17完成表格的输入。【知识拓展】1. 删除工作表的方法如下：（1） 选择所要删除的工作表。（2） 单击“编辑”菜单中的“删除工作表”命令；或者用鼠标右击选中的工作表标签，在弹出的快捷菜单中选择“删除”命令。（3） 在弹出的警告对话框中，单击“确定”按钮。注意：删除的工作表不能使用撤消命令进行恢复。2. 复制/移动工作表的方法如下：方法一：使用鼠标完成（1）将鼠标指针指向工作表标签。（2）按下左键拖动工作表到另一位置，实现工作表的移动；（3）按Ctrl键的同时拖

14、动工作表到另一位置，实现工作表的复制。方法二：通过菜单完成（1）在工作表标签上单击右键。（2）从弹出菜单中选择“移动或复制工作表”。这时会出现“移动或复制工作表”对话框，我们可以看到，可以将选定的工作表移动到同一工作簿的不同位置，也可以选择移动到其它工作簿的指定位置。如果选中对话框下方的 “建立副本”复选框，就可以实现选定工作表的复制。步骤7 将此工作簿进行保存，并退出Excel。保存过的文件进行修改后，选择“文件”菜单中的“保存”命令，文件就会按原来的文件名和保存位置保存，不会出现“另存为”对话框；选择“文件”菜单中的“另存为”命令，可以在打开的“另存为”对话框里选择好保存路径和文件名后点击

15、“保存”。此时，原工作簿文件是没有变化的，而在新选择的路径里以新命名的文件名保存了一个在原工作簿文件的基础上修改后的工作簿文件。Excel退出的方法与Word的退出方法相同。修改后的文件如没有保存直接关闭文档或退出程序，Excel会弹出以下提示，如图2-3-20，单击“是”进行保存，单击“否”将放弃本次修改，单击“取消”返回编辑状态而不会关闭窗口。图2-3-20 是否保存提示任务2 药品库存的数据计算Excel的强大功能体现在数据计算上，通过在单元格中输入公式和函数，代替手工输入计算完成任务。现在就打开建好的工作簿，来完成其中的数据计算问题吧！【任务实施步骤】步骤1 打开“药品库存统计表”要浏

16、览或编辑已经保存过的工作簿，需要先将工作簿打开，方法如下。方法1：启动Excel后，单击常用工具栏上的“打开”按钮。方法2：启动Excel后，单击“文件”菜单中的“打开”命令。方法3：启动Excel后，按快捷键Ctrl+O。方法4：在“我的电脑”或“资源管理器”中找到需要打开的工作簿，双击即可启动Excel并打开文件。步骤2 在“初期库存盘点表”中计算出“库存数量”，“药品入库表”中计算出“入库金额”，“药品出库表”中计算出“出库金额”。公式就是对工作表中的数据进行计算的等式。在Excel中，公式一般包括三个部分。（1） “=”：用户输入的内容是公式而不是数据（注意输入公式时必须以“=”开头）

17、。（2） 运算符：公式所执行的运算方式。（3） 运算数：参加运算的对象。可以是常量、单元格或单元格区域的引用、名称和函数。公式一般都可直接输入，操作方法为：单击将要在其中输入公式的单元格，输入等号“=”，接着输入公式内容，最后按Enter键或单击编辑栏中的“”按钮确认。在引用“货品总量”时，要输入单元格地址，单元格地址由列号和行号组，即H3。在“初期库存盘点表”中，“库存数量”=“货品总量”-“过期数量”，如下图2-3-21所示输入公式。输入H3和I3时，可以用鼠标直接单击这两个单元格进行输入。图2-3-21计算库存数量输入完成后，选中J3单元格，在编辑栏中显示公式，在J3单元格中显示计算结果

18、，如图2-3-22。图2-3-22 编辑栏显示公式如果需要修改公式，要先选中该单元格，在编辑栏中单击进行修改，也可以双击该单元格，直接在单元格中修改。下面J4到J7的计算，不需要反复输入公式，而是直接拖动J4的填充柄到J7右下角松开左键，即引用了单元格的公式，而公式中的单元格地址会相应发生变化，从而正确的计算出各种药品的库存。如图2-3-23。图2-3-23 智能填充公式公式的灵活性是通过单元格的引用来实现的。所谓单元格引用就是指通过单元格地址来引用单元格的内容。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一个单元格的数值。还可以引用同一个工作簿中不同工作表的单元格和

19、其他工作簿中的单元格。当引用的单元格中的数值发生改变时，公式会自动地将有关数据重新计算一遍，并将最新结果显示在屏幕上。公式中单元格地址的输入既可以直接用键盘敲入，也可以用鼠标单击相应的单元格来得到相应的单元格地址。这种直接把单元格地址写入公式中的引用称为相对引用。当把含有相对引用的公式复制到新位置时，新位置中公式包含的相对引用地址会随之改变，公式将会依据更改后地址内的值重新进行计算。如图2-3-23编辑栏显示J3的公式为“=H3-I3”，引用J3单元格使用填充柄复制到J4时，公式变为“=H4-I4”，恰好满足我们的需求。在“药品入库表”中，入库金额=入库数量进价，在“药品出库表”中，出库金额=

20、出库数量售价。使用上面的方法，可以快速实现所有的计算。如下图2-3-24。图2-3-24 计算入库金额【知识拓展】Excel包含四种类型的运算符：算术运算符、比较运算符、文本运算符和引用运算符。（1）算术运算符：完成基本的数学运算，如加法、减法和乘法等，可以连接数字，并产生数字结果，如下表2-3-1所示。表2-3-1 算术运算符算术运算符含义示例+（加号）加法运算3+4-（减号）减法运算9-2-（减号）负号-6*（星号）乘法运算3*5/（正斜线）除法运算8/4%（百分号）百分比80%（插入符号）乘幂运算23（2）比较运算符：比较两个数值并产生逻辑值TRUE或FALSE，如下表2-3-2所示。表

21、2-3-2 比较运算符比较运算符含义示例=（等号）等于A1=B1（大于号）大于A1B1（小于号）小于A1=（大于等于号）大于或等于A1=B1=（小于等于号）小于或等于A1=B1（不等号）不相等A1B1（3）文本运算符：文本运算符&可以加入或者连接一个或多个文本字符串以产生一串文本，如下表2-3-3所示。表2-3-3 文本运算符文本运算符含义示例&（和号）将两个文本值连接或串起来产生一个连续的文本值护理&专业结果为护理专业注意：在公式中输入文本型常量时，需要加英文的双引号。（4）引用运算符：引用运算符可以将单元格区域合并计算，如下表2-3-4所示。公式中可能同时用到多个运算符，当多个运算符同时出

22、现在公式时，Excel对运算符的优先级有严格规定，优先级从高到低的依次为引用运算符负号百分比乘幂乘和除加和减&比较运算符，如果运算优先级相同，则按从左到右的顺序计算。如果有圆括号，则先计算括号里面的。表2-3-4 引用运算符引用运算符含义（示例）:（冒号）区域运算符，产生对包括在两个引用之间的所有单元格的引用，如A2:E2表示单元格A2、B2、C2、D2、E2,（逗号）联合运算符，将多个引用合并为一个引用，如C3:E3,D6:F6表示单元格C3、D3、E3、D6、E6、F6空格交叉运算符，产生对两个引用共有的单元格的引用，如C4:D6 D5:F8表示单元格D5、D6步骤3 完成“药品库存报表”

23、中所有的计算1.计算 “期初库存”在“药品库存报表”中,期初库存就是“初期库存盘点表”中的“库存数量”，由于这一列数据是使用公式计算而来，当直接复制过来时，单元格中的公式重新计算会出错，原因是公式中单元格的引用是相对引用，当复制后位置发生变化时，公式中单元格地址相对于位置发生相应的变化，所以出现下图2-3-25中的问题。图2-3-25 错误提示其中“#VALUE!”是表示不正确的参数或运算符，如图2-3-25所示，编辑栏显示的公式“=A3-B3”中A3和B3都不是数据，所以是参数不正确。在这种情况下，就需要在“药品库存报表”的C3中重新输入公式进行计算，由于要计算的参数与结果不在同一张工作表，

24、公式中的单元格引用要使用外部引用。单元格引用可分为相对引用、绝对引用、混合引用和外部引用。相对引用是指直接把单元格地址写入公式中。当把含有相对引用的公式复制到新位置时，新位置中公式包含的相对引用地址会随之改变，公式将会依据更改后地址内的值重新进行计算。绝对引用是指公式所引用的单元格是固定不变的。采用绝对引用的公式，无论公式移动或复制到哪里，都将引用同一个单元格。绝对引用的形式是“$列号$行号”。如下图2-3-26中，公式中的单元格地址都用绝对地址，G3的计算结果不变，但填充后，下面的公式因为是绝对引用导致单元格地址不会发生对应变化，所以计算结果仍不变。图2-3-26 绝对引用混合引用是指在引用

25、单元格地址时，单元格地址的列号和行号之前一个有“$”符号，另一没有“$”符号。如果希望公式中的引用B2在复制时，行不变列变或者列不变行变，就需要使用混合引用，B$2或者$B2。外部引用是指在公式中，引用同一个工作簿中其他工作表的单元格。外部引用的格式为“工作表名!单元格地址”。引用其他工作簿中的单元格，引用格式为“工作簿文件名.xls工作表名!单元格地址”。例如：“药品库存报表”中的期初库存等于“初期库存盘点表”中的库存数量J3，所以在C3中直接输入“=初期库存盘点表!J3”。这里的引用就属于外部引用。如下图2-3-27：图2-3-27 外部引用其他药品的期初库存使用填充柄拖动来完成。注意：当

26、数值长度超过了单元格的列宽时，系统将会显示 “#”，此时只需将列宽调大。2. 计算“入库数量”和“出库数量”在“药品库存报表”中，入库数量等于“药品入库表”中所有对应药品的入库数量 之和，使用公式计算时很难自动找出对应药品的所有入库记录，这就要用到函数计算。函数是一些预定义的公式，通过使用参数来按特定的顺序或结构进行计算。例如，在计算“入库数量”时，需要在“药品入库表”中先找出药品名称是“小儿化痰止咳颗粒”的数据记录，再对对应的入库数量求和，就是这一药品的总入库量。SUMIF条件求和函数即可实现这一功能。操作方法如下图2-3-28到图2-3-32：图2-3-28 插入函数图2-3-29 选择函

27、数图2-3-30 搜索函数图2-3-31 设置函数参数图2-3-32 输入函数参数下面其他药品的入库数量也应如此计算，但使用填充柄时，由于函数参数使用的是相对地址，复制到下面会出现相对变化：D3中的公式为： =SUMIF(药品入库表!C3:C17,B3,药品入库表!E3:E17)D4中的公式为： =SUMIF(药品入库表!C4:C18,B4,药品入库表!E4:E18)D5中的公式为： =SUMIF(药品入库表!C5:C19,B5,药品入库表!E5:E19)其中的第一个参数“要进行计算的单元格区域”（即搜索条件的数据范围）和第三个参数“用于求和计算的实际单元格”范围在下面各行的引用中应该是不变的

28、，而第二个参数“定义的条件”应该相应的变为B4、B5等对应药品名称，所以在计算第一个药品的入库数量时，第一、第三参数要使用绝对引用使其始终不变，从而满足需求。如下：D3中的公式为： =SUMIF(药品入库表!$C$3:$C$17,B3,药品入库表!$E$3:$E$17)D4中的公式为： =SUMIF(药品入库表!$C$3:$C$17,B4,药品入库表!$E$3:$E$17)D5中的公式为： =SUMIF(药品入库表!$C$3:$C$17,B5,药品入库表!$E$3:$E$17)结果如下图2-3-33：图2-3-33 入库数量“出库数量”也使用同样的方法来完成，G3中的函数是：“=SUMIF(药

29、品出库表!$C$3:$C$17,B3,药品出库表!$E$3:$E$17)”。如下图2-3-34：图2-3-34 出库数量3. 计算“入库金额”和“出库金额” “入库金额”使用公式计算时，可输入“=D3*E3”，也可以用乘积函数 “=PRODUCT(D3:E3)” 来完成，如图2-3-35。图2-3-35 乘积函数确定后计算完成，选定F3，向下拖动其填充柄完成其他“入库金额”的计算。使用同样的方法可以求出“出库金额”。4. 计算“期末库存”和“期末库存金额”现在同学们已经了解了使用公式和函数的方法，在计算“期末库存”和“期末库存金额”时，可以选择比较方便快捷的方式来完成。比如：“期末库存”=“初

30、期库存”+“入库数量”-“出库数量”可以使用公式；“期末库存金额”=“期末库存”“进价”既可以使用公式也可以使用函数。完成后结果如图2-3-36：图2-3-36 库存报表计算结果【知识拓展】常用函数如下：（1） 求和函数SUM：返回各参数的累加和。格式：SUM（number1，number2，）。number1，number2，为130个需要求和的参数。（2） 求平均值函数AVERAGE：返回参数的平均值（算术平均值）。格式：AVERAGE（number1，number2，）。number1，number2，为需要计算平均值的130个参数。（3） 最大值函数MAX：返回一组参数中的最大值。格式：MAX（number1，number2，）。number1，number2，为需要找出最大值的130个数字参数。（4） 最小值函数MIN：返回一组参数中的最小值。格式：MIN（number1，number2，）。number1，number2，是要从中找出最小值的130个数字参数。（5） 逻辑函数IF：进行逻辑判断，根据真假值返回不同结果。格式：IF（logical_test，value_if_true，value_if_false）。Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。例如，