EXCEL表格的基本操作（excel表格教程）汇集帖，欢迎大家跟帖！

后勤部长 · 发表于 2012-3-16 15:17

一、VLOOKUP的反向查找。 一般情况下，VLOOKUP函数只能从左向右查找。但如果需要从右向右查找，则需要把区域进行“乾坤大挪移”，把列的位置用数组互换一下。
例1：要求在如下图所示表中的姓名反查工号。

公式：=VLOOKUP(A9,IF({1,0},B2:B5,A2:A5),2,0)
公式剖析：
      1、这里其实不是VLOOKUP可以实现从右至右的查找，而是利用IF函数的数组效应把两列换位重新组合后，再按正常的从左至右查找。
      2、IF（{1,0},B2:B5,A2:A5)这是本公式中最重要的组成部分。在EXCEL函数中使用数组时（前提时该函数的参数支持数组），返回的结果也会是一个数组。这里1和0不是实际意义上的数字，而是1相关于TRUE，0相当于FALSE，当为1时，它会返回IF的第二个参数（B列），为0时返回第二个参数（A列）。根据数组运算返回数组，所以使用IF后的结果返回一个数组（非单元格区域）：{"张一","A001";"赵三","A002";"杨五","A003";"孙二","A004"}
二、VLOOKUP函数的多条件查找。
   VLOOKUP函数需要借用数组才能实现多条件查找。
   例2：要求根据部门和姓名查找C列的加班时间。

   分析：我们可以延用例1的思路，我们的努力方向不是让VLOOKUP本身实现多条件查找，而是想办法重构一个数组。多个条件我们可以用&连接在一起，同样两列我们也可以连接成一列数据，然后用IF函数进行组合。
公式：{=VLOOKUP(A9&B9,IF({1,0},A2:A5&B2:B5,C2:C5),2,0)}
公式剖析：
   1、A9&B9 把两个条件连接在一起。把他们做为一个整体进行查找。
   2、A2:A5&B2:B5，和条件连接相对应，把部分和姓名列也连接在一起，作为一个待查找的整体。
   3、IF({1,0},A2:A5&B2:B5,C2:C5) 用IF（{1，0}把连接后的两列与C列数据合并成一个两列的内存数组。按F9后可以查看的结果为：
   {"销售张一",1;"销售赵三",5;"人事杨五",3;"销售赵三",6}
   4、完成了数组的重构后，接下来就是VLOOKUP的基本查找功能了，另外公式中含有多个数据与多个数据运算(A2:A5&B2:B5),，所以必须以数组形式输入，即按ctrl+shift后按ENTER结束输入。
   三、VLOOKUP函数的批量查找。
   VLOOKUP一般情况下只能查找一个，那么多项该怎么查找呢？
   例3 要求把如图表中所有张一的消费金额全列出来

分析：经过前面的学习，我们也有这样一个思路，我们在实现复杂的查找时，努力的方向是怎么重构一个查找内容和查找的区域。要想实现多项查找，我们可以对查找的内容进行编号，第一个出现的是后面连接1，第二个出现的连接2。。。
公式：{=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2

B$6&COUNTIF(INDIRECT("b2:b"&ROW($2:$6)),B$9),$C$2:$C$6),2,)}
   公式剖析：
      1、B$9&ROW(A1) 连接序号，公式向下复制时会变成B$9连接1，2，3
      2、给所有的张一进行编号。要想生成编号，就需要生成一个不断扩充的区域（INDIRECT("b2:b"&ROW($2:$6))，然后在这个逐行扩充的区域内统计“张一”的个数，在连接上$B$2:$B$6后就可以对所有的张一进行编号了。
   3、IF（{1，0}把编号后的B列和C组重构成一个两列数组
   通过以上的讲解，我们需要知道，VLOOKUP函数的基本用法是固定的，要实现高级查找，就需要借助其他函数来重构查找内容和查找数组。
   至此VLOOKUP函数从入门到高级的四篇VLOOKUP函数使用教程全部结束了，VLOOKUP函数在数组运算中还有着其他应用，但只是配角了，所以本系列不再介绍。由于笔者水平有限，不免有错漏之处，请大家多多指点。

后勤部长 · 发表于 2012-3-16 15:19

VLOOKUP函数的使用方法(高级篇)

示例下载：.rar (2.45 KB, 下载次数: 4)

后勤部长 · 发表于 2012-3-16 15:20

在学习了VLOOKUP的入门和初级篇后，本文将带将大家学习VLOOKUP的进阶篇：VLOOKUP的模糊查找。 一、字符的模糊查找
在A列我们知道如何查找型号为“AAA”的产品所对应的B列价格，即：
=VLOOKUP（C1,A:B,2,0)
如果我们需要查找包含“AAA”的产品名称怎么表示呢？如下图表中所示。

   公式=VLOOKUP("*"&A10&"*",A2:B6,2,0)
公式说明：VLOOKUP的第一个参数允许使用通配符“*”来表示包含的意思，把*放在字符的两边，即"*" & 字符 & "*"。
二、数字的区间查找
   数字的区间查找即给定多个区间，指定一个数就可以查找出它在哪个区间并返回这个区间所对应的值。
在VLOOKUP入门中我们提示VLOOKUP的第4个参数，如果为0或FALSE是精确查找，如果是1或TRUE或省略则为模糊查找，那么实现区间查找正是第4个参数的模糊查找应用。
首先我们需要了解一下VLOOKUP函数模糊查找的两个重要规则：
1、引用的数字区域一定要从小到大排序。杂乱的数字是无法准确查找到的。如下面A列符合模糊查找的前题，B列则不符合。

2、模糊查找的原理是：给一定个数，它会找到和它最接近，但比它小的那个数。详见下图说明。

最后看一个实例：
例：如下图所示，要求根据上面的提成比率表，在提成表计算表中计算每个销售额的提成比率和提成额。

公式：=VLOOKUP(A11,$A$3

B$7,2)
公式说明：
1、上述公式省略了VLOOKUP最后一个参数，相当于把第四个参数设置成1或TRUE。这表示VLOOKUP要进行数字的区间查找。
2、图中公式中在查找5000时返回比率表0所对应的比率1%，原因是0和10000与5000最接近，但VLOOKUP只选比查找值小的那一个，所以公式会返回0所对应的比率1%。

示例文件下载：.rar (2.77 KB, 下载次数: 3)

后勤部长 · 发表于 2012-3-16 15:22

常用Excel函数精华教程（全篇）

Countif函数一、countif函数
作用：根据条件统计个数
countif函数是使用频率最高的几个函数之一,下面针对这个函数做一个小小的专题.以方便大家学习，此文是本站原创。转载请注明转自“excel精英培训”
一、countif函数的用途
countif函数是根据条件在另一个区域进行个数的统计，一方面它可以完成符合条件的统计计算。另一方面由此扩展出它可以进行重复值的查找我表格的核对。
二、countif的基本语法：
COUNTIF(单元格引用, 条件)

参数说明：
1 第一个参数只能是单元格引用方式，不能使用内存数组
2 第二个参数是条件，条件可以是值，可以是字符串构成的复合条件，可以使用通配符进行模糊统计，可以使用内存数组。
应用示例：
  例1：统计在A列是“公司A”的个数
   公式=Countif(A:A,"公司A"）
  例2：统计A列包含“公司A”的个数
   公式=Countif(A:A,"*公司A*"）
注：这里使用通配*表示字符前后有任意个字符。
  例3：统计C列成绩大于60的学生个数
公式 =Countif（C：C,">60")
注：这里是用运算对比符号和数字构成的条件
  例4：统计大于C1单元格数值的C列个数。
   公式：=Countif(c:c,">" & c1)
注：这里是用&插入了一个变量，即C1的值。
  例5：统计C列大于平均数的个数
   公式：=Countif（c:c,">" & average(c:c))
注：这里是使用了平均值函数average先计算平均值，然后作为条件。
  例6：统计A列为“公司A”或“公司B”的个数
   公式：{=Sum(Countif（A：A,{"公司A","公司B"}）) }
注：这里在第二个参数里加入了常量数组，使用countif的结果是分别按两个公司名称统计的结果，也是一个数组假如是{3,4}，得到数组后用sum函数对两个数进行求和，得到总的个数，这个公式是数组公式，所以一定要输入公式后把光标放在公式最后，按ctrl+shift，然后按enter键结束输入。
  另：也许也还会问，如果设置更多条件该怎么弄，兰色幻想建议使用另一个可以多条件求和与计数的函数:sumproduct
例：统计大于1000，小于3000的数字个数
=sumprodcut((a1:a100>1000)*(a1:a100<3000))
好了，关于countif函数就介绍到这里吧，希望能对大家有用。

后勤部长 · 发表于 2012-3-16 15:29

一、折线图的简单制作技巧

1.“字体”的处理
建议：取消图表的字体“自动缩放”功能，这样可防止在变动图表大小时，图表项的字体发生不必要的改变。
取消所有图表项的“自动缩放”功能，要取消所有图表项的字体“自动缩放”功能，取消图表区的“字体缩放“功能即可。可通过双击图表区，并调出“图表区格式”对话框，切换到“字体”选项卡，取消“自动缩放”前面的复选框的选择，这样便是取消了所有图表项的字体缩放功能，然后分别对各图表项的字体按需要设定字体大小。

2.“网格线”的处理
使用“折线图”或“散点图”时，尤其要注意淡化网格线对数据系列的影响，可取消网格线或是将其设为虚线，并改为浅色。

3. 数据系列格式的设置
一般不使用默认的格式设置，根据自己的需求改变“线形“或是“数据标记”及“填充”。
如截图中，上面的一个图表进行的是普通格式化操作；而下面一个是采取如上几点格式化建议后的图表。

后勤部长 · 发表于 2012-3-16 15:33

在非堆积图、二维图、面积图、条形图、柱形图、折线图、股价图、xy（散点）图和气泡图上，单击要向其添加趋势线或移动平均的数据系列，或执行以下操作从图表元素的列表中选择数据系列：
- 单击图表内的任意位置。

这会显示“图表工具”，其中带有“设计”、“布局”和“格式”选项卡。

在“格式”选项卡上的“当前选择”组中，单击“图表元素”框旁边的箭头，然后单击所需的图表元素。

注释如果选择具有多个数据系列的图表，而不选择数据系列，则 Excel 会显示“添加趋势线”对话框。在列表框中，单击需要的数据系列，然后单击“确定”。

在“布局”选项卡上的“分析”组中，单击“趋势线”。

执行下列操作之一：

单击要使用的预定义趋势线选项。

注释这会在未使您能够选择特定选项的情况下应用趋势线。

单击“其他趋势线选项”，然后在“趋势线选项”类别中的“趋势预测/回归分析类型”下，单击要使用的趋势线的类型。

使用此类型	创建
线性	通过使用以下公式来计算线的最小平方拟合的线性趋势线：其中，m 为斜率，b 为截距。
对数	通过使用以下公式来计算点的最小平方拟合的对数趋势线：其中，c 和 b 为常量，ln 为自然对数函数。
多项式	通过使用以下公式来计算点的最小平方拟合的多项式或曲线趋势线：其中，b 和为常量。
乘幂	通过使用以下公式来计算点的最小平方拟合的乘幂趋势线：其中，c 和 b 为常量。注释在数据包括负值或零值时此选项不可用。
指数	通过使用以下公式来计算点的最小平方拟合的指数趋势线：其中，c 和 b 为常量，e 为自然对数的底数。注释在数据包括负值或零值时此选项不可用。
移动平均	使用以下公式的移动平均趋势线：注释移动平均趋势线的数据点数等于数据系列中数据点的总数减去为周期指定的数目。
R 平方值	通过使用以下公式在图表上显示 R 平方值的趋势线： “添加趋势线”或“设置趋势线格式”对话框的“选项”选项卡上提供了此趋势线选项。注释使用趋势线可显示的 R 平方值不是调整后的 R 平方值。对于对数、乘幂和指数趋势线，Excel 使用转换后的回归模型。