1、Excel 在管理中的应用案例 01第 1 部分 Excel 基础概念1. 名称名称可以代表一个单元格或者一个单元格区域，或者是常量，公式。名称的定义：选中需要命名的单元格或区域，在界面左上角名称框中输入名称后回车；名称的删除：插入名称定义，选中需要删除的名称点击删除按钮，点确定。名称的引用：需要引用某单元格时输入该单元格的名称：名称第 1 步：选中需要命名的某个单元格或单元格区域。第 2 步：在左上角名称框输入命名后回车。删除已有命名：选择“插入”“名称”“定义” ；选中需要删除的名称，点击“删除”按钮。名称命名的优点：1) 避免绝对引用的错误2) 对公式进行文字化表述，让公式更加容易理解3
2、) 可以在整个工作簿中通用，引用方便2. 常用引用函数row: 返回指定单元格的行号column: 返回指定单元格的列标match: 返回查找值在查找范围中的序号=match(查找值，查找范围，0)其第三个参数为 0，表示查找精确值address: 返回单元格名称，其参数为行号列标或计算行号列标的表达式=address(行号，列标)=address(1,1) 此公式返回 A1indirect: 返回单元格的值.其参数为单元格名称=indirect(“A1”),假设 A1=10,则 indirect 返回 10index: 在某区域内查找某个位置的值=index(查找区域，查找值所在的行号，查找
3、值所在的列号)offset：指定基点，指定位移量，得到单元格引用单个单元格引用：=offset(基点单元格，向下移动的行，向右移动的列)区域的引用：=offset(基点单元格，向下移动的行，向右移动的列，区域包括的行数，区域包括的列数)向上和向左移动时，位移量为负值。=offset(A1,1,1) 将得到 B2 单元格的值3. 动态引用动态引用是通过引用函数实现对于单元格或区域的相对引用。它和相对引用的效果很相似，但比简单的相对引用用途广泛。常用的动态引用的实现方法有：引用函数嵌套。比如 offset 和 row,column 嵌套；index 和 match，indirect,address
4、嵌套等；引用函数与控件结合使用，此类控件包括组合框，滚动条。动态引用的作用：实现对于单元格的动态引用；进行动态分析；制作动态报表。4. 运算类型数值运算：1 + 1 = 2逻辑运算：1 0 = TRUE在逻辑运算中，TRUE=1，FALSE=0我们利用逻辑运算进行条件判断在 Excel 中常用的一些逻辑函数：if,and,or,not,iserror 等and: 只有当所有条件全部满足，才会返回 true 的逻辑值语法：and(条件 1，条件 2，)or: 满足其中任何一个条件，都会返回 true 的逻辑值语法：or(条件 1，条件 2，条件 3,)5. 函数调用的语法函数名称（参数 1，参数
5、 2，）6. 绝对引用和相对引用：绝对引用：所引用的单元格不随着公式的复制而移动的引用方式。相对引用：所引用的单元格随着公式的复制而移动的引用方式。改变引用方式的方法：1) 在公式栏中选中需要改变引用方式的单元格，按 F4 键2) 给需要引用的单元格定义名称，然后在公式中引用该名称7. 数组公式对单元格区域进行多重计算的计算方式。与普通计算公式的区别是录入公式结束后，需要同时按下 ctrl + shift + enter ，其特征是在公式两端会出现一对大括号。如上图例，使用一个公式计算出所有产品的金额合计，引用的是所有的单价和所有的数量，执行的是多重计算。8. 错误提示Excel 中存在错误的
6、类型，比如 1/0=#DIV/0!, todas()=#NAMES!iserror 是一个逻辑函数，用以判断某个单元格内的值是否是一个错误，是错误则返回 TRUE,不是错误则返回 FALSE.iserror 有时可以和 if 函数嵌套进行一些较为复杂的判断。9. 有取值区间的随机数=最小值（最大值最小值）*rand()10. 循环引用是单元格引用其自身的引用方式。可以设置 Excel 允许进行循环引用：工具-选项-重新计算：将迭代计算选项打钩选中。行列互换（函数方式）：利用 transpose 函数+数组公式实现。首先选中行列数和原区域相反的一个区域；然后输入 transpose 函数最后按下
7、组合键 ctrl+shift+enter第 2 部分 Excel 设置1 显示当前文件的完整路径菜单区域右键菜单Web2 显示菜单项全部菜单视图工具栏自定义选项始终显示整个菜单3 鼠标移动方向工具选项编辑按 Enter 键后移动方向4 隐藏界面要素工具选项视图包括：网格线，滚动条，工作表标签，行号列标等。5 以显示值为准工具选项重新计算以显示精度为准行的合计与列的合计有时出现不相等的情况。可以采用以下方法解决。选择“工具”“选项”“重新计算”“以显示精度为准” ，选中该选项。6 自定义序列工具选项自定义序列7 改变文件保存位置工具选项常规默认文件位置8 改变文件用户名工具选项用户名9 单元格自
8、动换行格式单元格对齐自动换行第 3 部分 Excel 基础操作1. 快速选中数据表的整行或者整列ctrl + shift + 下箭头/右箭头2. 快速选中区域ctrl + shift + 83. 行列互换复制需要进行行列互换的区域后，将光标放置在数据表外面位置，选择性粘贴选中“转置”选项4. 一个单元格内容输入为多行alt + 回车键5. 显示公式ctrl + 6. 冻结窗口选择需要进行冻结的单元格位置，选择窗口冻结窗格7. 自定义格式语法：大于条件值格式；小于条件值格式；等于条件值格式；文本格式8. 缩放数值0.00， ， 按百万缩放0“.”0，按万缩放0.00, 按千缩放9. 不复制隐藏的
9、行或列a 首先选中需要复制的被隐藏了一些行或列的表格区域；b 然后点击“编辑”-“定位”-“定位条件” ，在其中选择“可见单元格” ；c 复制表格区域，粘贴即可。案例 02 一 文本的处理1. 等长文本的分割从字符串的左边取字符：=Left (字符串，文本长度)从字符串的右边取字符：=Right (字符串，文本长度)从字符串的中间取字符：=Mid (字符串，文本起始位置，文本长度)2. 不等长文本的分割第 1 步：选中要进行分割的字符串区域。第 2 步：点击“数据”“分列” ，在文本分列向导中选择适合的分隔符。第 3 步：设置需要导入的列以及放置该列的位置。3. 文本的合并=Concatena
10、te(文本 1，文本 2，)使用连接符&：动态表头：=“ABC 公司“&YEAR(NOW()&“年“&MONTH(NOW()&“月报表“4. 有效性选中需要设置有效性的区域，点击“数据”“有效性” ，在有效性条件中选择“序列”，在来源中录入或选取列表。输入信息：事前提醒。出错警告：事后提示。如果待选列表不在当前工作表中，需要首先为该列表定义名称，然后在有效性来源框中输入：“=该列表的名称”5. 有效性的其他用法不允许录入重复数据的有效性设定：countif(e:e,e12)=1输入的内容中必须包括某字符：=not(iserror(find(“中国“,g30)二级选项：首先将一级选项的每个项目定
11、义一个名称，该名称内容包括相应的二级项目；制作一级项目的有效性；制作二级项目的有效性：内容为：=INDIRECT(g6)，其中 g6 为设定了有效性的一级选项所在的单元格。6. 圈示无效数据。选择“工具”“公式审核”“显示公式审核工具栏” ，点击“圈示无效数据”按钮。该工具可以将所有不符合有效性设定的内容圈示出来。二 日期的处理1. Datedif 函数：用于计算两个日期之间的年数，月数，天数。=Datedif(开始日期，结束日期， “y”)第三个参数：“y”：表示年数“m”：表示月数“d”：表示天数2. 生日提醒=DATEDIF(TODAY(),DATE(YEAR(TODAY(),MONTH
12、(G2),DAY(G2),“d“)3. 某日期的星期=weekday(A2,2) 返回的值是 3，则表示该日期是星期三。4. 两个日期间的工作日天数=networkdays(开始日期，结束日期，节假日列表)需要首先加载“分析工具库”才能使用此工具案例 03 数据查询1. VLOOKUP（）VLOOKUP 在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。VLOOKUP 中的 V 代表垂直。VLOOKUP(lookup_value, table_array, col_ index_num, range_lookup)Lookup_value 为需要在数组第一列中查
13、找的数值。Lookup_value 可以为数值、引用或文本字符串。也可以理解为：两表共有的索引字段。Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或列表。必须使得共有字段位于该范围的第一列。col_ index_num 为需要调转的数据位于第二个参数中定义的范围的第几列。必须为单纯数值。range_lookup 定义大致匹配或精确匹配。False 或 0:精确匹配；true 或忽略或1：如果无法找到精确匹配的值，那么就查找并匹配比查找值小的最近似的值。2. HLOOKUP()Hlookup 中的 H 是水平的意思，在横向排列的表格中查询数据用
14、Hlookup，垂直排列的表格查询用 Vlookup 函数。其参数含义与 VLOOKUP 近似。案例 04 查询表信息查询表利用有效性和 VLOOKUP 函数实现。利用有效性实现索引字段的切换。利用 VLOOKUP 函数查询与索引字段相关的信息。案例 05 数据筛选1. 自动筛选（略）2. 高级筛选由于自动筛选只能筛选出简单条件的数据，因此如要在复杂条件下进行筛选就需要采用高级筛选的方式。在需要进行筛选的数据表外设定筛选条件。在对话框中设定：列表区域为数据表区域；条件区域为刚才在数据表外部设定的条件区域，要包括字段名称和条件所在的单元格区域。3. 条件格式条件格式可以使得符合特定条件的记录按照某种设定的格式显示。1) 选中要进行条件格式设定的单元格范围2) 执行【格式】 【条件格式】3) 在条件格式设置界面进行条件的设定：4. 统计Frequency(需要进行统计的数据区域，分段点)对数据进行区间统计。此函数需要和数组公式一起使用。此函数是根据数值的大小划分区间进行统计的。列出需要分析的工资区间，选中需要计算统计结果的单元格区域，输入函数：
