今天是本月第几周=INT((DAY(NOW())+MOD(NOW()-DAY(NOW()),7)-1)/7)+1

今天是本年第几周=WEEKNUM(NOW())

今天是本季度的第几天=COUPDAYBS(NOW(),6^9/9,4,1)+1

今天所在的季度有几天=COUPDAYS(NOW(),6^9/9,4,1)

本月有几天=DAY(EOMONTH(NOW(),0))

本月第一天

=NOW()-DAY(NOW())+1

=--TEXT(NOW(),"e-m")

下个月有几天的公式

=DAY(EOMONTH(NOW(),1))

上个月有几天

=DAY(NOW()-DAY(NOW()))

=DAY(EOMONTH(NOW(),-1))　

本月最后一天=TEXT(NOW()-DAY(NOW())+32,"e-m")-1

=TEXT(TEXT(NOW(),"e-m")+31,"e-m")-1

上个月最后一天=NOW()-DAY(NOW())

本月到今天有几个星期日=INT((MOD(NOW()-DAY(NOW())-1,7)+DAY(NOW()))/7)

今天是星期几=TEXT(NOW(),"aaaa")

本月是几月=TEXT(NOW(),"mm")

=MONTH(NOW())

今天是今年的第几天=TODAY()-"1-1"+1

本月英文全写:=TEXT(NOW(),"mmmm")

本月英文简写=TEXT(NOW(),"mmm")

中文大写月=TEXT(NOW(),"[DBNum2]m月")

excel常用快捷键(小集)

插入行、删除行：在excel里插入行的快捷键是ctrl + shift + "+" ，即先同时按ctrl和shift键，然后按+插入空行。

   删除行的快捷键是ctrl + "-",然后按回车键

插入工作表：在excel里快速插入工作表有两种方法：

  1、先插入一个工作表，然后按F4，每按一次就会插入一个新的工作表。

  2、按shift+f11就可以插入一个工作表

  3、在excel2007和excel2010中直接点工作表标签最右端的插入工作表就可以插入了

excel中求和快捷键：excel中求和快捷键是alt+"="，即在一组数据旁，按alt+"="就可以自动生成sum求和公式。

表格换行：excel表格中强制换行的快捷键是alt+enter，可以在输入时按alt+enter就可以让文字折到下一行。

excel工作表操作大全

保护工作表：保护工作表可以保护单元格的内容不被修改，单元格行列不被删除或添加等。工作表保护的方法是：选取工作表--工具-保护工作表-输入密码-确认密码-保护

复制工作表：如果你想添加一个和已知工作表相同内容的工作表，那不用费用的去编辑，只需要复制即可，复制工作表的方法是：选取要复制的工作表--点右键菜单上的复制或移动工作表-选中“建立副本”。也可以按ctrl键用鼠标拖动的方式快速复制一个工作表。

合并工作表：工作表的数据合并可以用手工方法（比较笨，但易操作）、函数法（灵活性强、公式较复杂）和VBA方法（需要有VBA基础），另外还可以用数据透视表和合并计算对多个表进行合并，具体的要看表格的结构再选取适合的合并方法

切换工作表：工作表切换记住快捷键：ctrl+pageup和ctrl+pagedown

工作表个数：一个工作簿中到底有多少个呢？

VBA方法：sheets.count

宏表函数法：定义名称：SC =GET.WORKBOOK(4) 在工作表中输入=SC即可得到工作表的总个数

excel表格和你捉迷藏

隐藏excel文件：在你的电脑里，每个文件都需要相应的支持程序，象EXCEL文件需要EXCEL程序，JPG文件需要图片程序等，但用什么样的支持程序是和后辍有关系的，比如，我们把A格xls改为A.jpg，看看会有什么结果。你会惊奇的发现。excel文件变成图片格式了，不过这种变化只是形变，本质还是excel文件，需要打开时再回A.xls即可。

  这招狠吧，让你看到而想不到它是EXCEL文件。

隐藏Excel工作表：工作表的一般隐藏方法很简单，如果是EXCEL2003，则可以用格式-工作表--隐藏来隐藏某个工作表，如果是EXCEL2007和EXCEL2010，则可以在工作表标签的右键菜单中选取隐藏即可。这种隐藏方式可以通过取消隐藏来显示出来。

   如果想让工作表隐藏后无法通过取消隐藏显示工作表，则可以用下面的步骤：

  按ALT+F11打开VBE窗口，在窗口左上角选取要隐藏的工作表，然后在下面的属性窗口中设置VISIBLE属性为2即可。如下图所示

隐藏网格线：EXCEL中默认显示灰色网格线，可以用下面的方法隐藏网格线

 EXCEL2003中，工具菜单--选项命令--视频选项卡，在下面去掉“网格线”的勾选。

 EXCEL2007和EXCEL2010，在“视图”选项卡中可以直接看到“网格线”的选项，去掉勾选即可。

隐藏0（零）值：EXCEL表格中如果不想显示0值，可以用下面的方法完成。

  EXCEL2003中，工具菜单--选项命令--视频选项卡---把0值前面的勾选去掉即可。

  EXCEL2007中，单击Office按钮/Excel选项，打开Excel选项对话框。单击高级选项卡，在此工作表的显示选项中取消在具有零值的单元格中显示零项的勾选，然后单击确定按钮。这样表格中的0值显示将被隐藏。

隐藏行和列：要隐藏EXCEL行和列，只需要选取要隐藏的行和列，然后右键菜单中点“隐藏即可”，也可以用冻结窗格式的形式隐藏。比如把前3列隐藏起来，选中第四列，窗口-冻结窗格，然把前四格隐藏起来或用拖动方式隐藏起来。EXCEL2007的用户可以在视图选项卡中找到冻结窗格的选项。

隐藏公式：在EXCEL里输入的公式不想让别人看到，可以用下面的方式完成对公式的隐藏。

   选取公式---右键--设置单元格格式---保护--把隐藏前面的勾勾上---确定---保护工作表

隐藏单元格内容：选择要隐藏的单元格区域，在“格式”菜单中单击“单元格”，单击“数字”选项卡，在“分类”框中，单击“自定义”，然后在“类型”框中键入三个半角的分号“;;;”。单击“确定”按钮。

此时，这些单元格中的内容将不会显示在工作表中的单元格中，但当你单击某个单元格后，编辑栏中将会显示其内容。如果希望在编辑栏中也不显示其内容，请继续下面的操作。

隐藏工作簿窗口：如果让别人打开你的EXCEL文件，却看不到你的文件内容，可以用隐藏窗口的方法来实现。EXCEL2003中窗口菜单--隐藏。EXCEL2007中视图选项卡，点“隐藏”命令

无法取消隐藏行和列：1、ctrl+a（全选） 

2、格式——列——取消隐藏 



1.你当时隐藏的时候用的不是自动隐藏吧？ 



而是你手动的把单元格之间的行距缩小到看不到该行（显示效果跟自动隐藏一样），



但是，在这种情况下，你用取消隐藏是无法显示出来的。 



所以，就得麻烦你，一个一个的把单元格拉出来。 



2.取消自动筛选再试试看 



3.点表格左上角选择整张表，或选择包含隐藏行的范围，把鼠标放在左侧行号上成实心黑十字时双击，所有隐藏行就出来了 

4 也可能是冻结了窗体，窗口---取消窗口冻结。

excel中取绝对值函数是哪个？

答：excel绝对值函数是ABS函数

Excel与身份证号码

输入18位：新的身份证号有18位之多，但在EXCEL里输入大于15位的数字时，数字会自动变成0，这时可以用下面的两种方式来输入完全的身份证号。

   1、单元格右键菜单--设置单元格格式--在打开的窗口中，选“数字”选项卡的分类为“文本”即可。

   2、在输入数字前先输入'（英文状态下的单撇），然后再输入身份证号。

计算年龄：身份证号里有年龄信息，提取身份证号的公式有很多种方法，下面说一种通用的

=datedif(text((len(a3)=15*19&mid(a3,7,6+(len(a3)=18*2),”#-00-00”),today(),”y”)

提取出生年月日：从身份证号码中提取出生年月日

=text(mid(a1,7,6+(len(a1)=18)*2),"#-00-00")+0

=text(mid(a1,7,6+(len(a1)=18)*2),"#-00-00")*1

=if(a2<>"",text((len(a2)=15)*19&mid(a2,7,6+(len(a2)=18)*2),"#-00-00")+0,)

取出性别：从身份证号码中提取出性别

=if(mod(mid(a1,15,3),2),"男","女") （最简单公式）

=if(mod(right(left(a1,17)),2),"男","女")

=if(a2<>” ”,if(mod(right(left(a2,17)),2),”男”,”女”),)

=if(value(len(round(right(a1,1)/2,2)))=1,"男","女")

COUNTIFS 函数的使用学习

适用于: Microsoft Excel 2010, Excel Web App, SharePoint Online for enterprises, SharePoint Online for professionals and small businesses , Excel 2007

打印 全部显示全部隐藏本文介绍 Microsoft Excel 中 COUNTIFS 函数 （函数:函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。函数可以简化和缩短工作表中的公式，尤其在用公式执行很长或复杂的计算时。）的公式语法和用法。

说明

将条件应用于跨多个区域的单元格，并计算符合所有条件的次数。

语法

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)COUNTIFS 函数语法具有以下参数 （参数:为操作、事件、方法、属性、函数或过程提供信息的值。）：

criteria_range1    必需。在其中计算关联条件的第一个区域。

criteria1    必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对哪些单元格进行计数。例如，条件可以表示为 32、">32"、B4、"苹果" 或 "32"。

criteria_range2, criteria2, ...    可选。附加的区域及其关联条件。最多允许 127 个区域/条件对。

 要点   每一个附加的区域都必须与参数 criteria_range1 具有相同的行数和列数。这些区域无需彼此相邻。

注解

每个区域的条件一次应用于一个单元格。如果所有的第一个单元格都满足其关联条件，则计数增加 1。如果所有的第二个单元格都满足其关联条件，则计数再增加 1，依此类推，直到计算完所有单元格。

如果条件参数是对空单元格的引用，COUNTIFS 会将该单元格的值视为 0。

您可以在条件中使用通配符，即问号 (?) 和星号 (*)。问号匹配任一单个字符；星号匹配任一字符序列。如果要查找实际的问号或星号，请在字符前键入波形符 (~)。

示例

示例 1

如果将示例复制到一个空白工作表中，可能会更容易理解该示例。

如何复制示例？

选择本文中的示例。

 要点   不要选择行或列标题。

从“帮助”中选择示例

按 Ctrl+C。

在 Excel 中，创建一个空白工作簿或工作表。

在工作表中，选择单元格 A1，然后按 Ctrl+V。

 要点   若要使该示例能够正常工作，必须将其粘贴到工作表的单元格 A1 中。

要在查看结果和查看返回结果的公式之间进行切换，请按 Ctrl+`（重音符），或在“公式”选项卡上的“公式审核”组中，单击“显示公式”按钮。

将示例复制到一个空白工作表中后，可以按照您的需要改编示例。

 

1

2

3

4

5

6

7

8

9

 A B C D

销售人员 超出苹果汁配额 超出牛奶配额 超出酱油配额

王伟 是 否 否

赵军 是 是 否

张颖 是 是 是

李芳 否 是 是

公式 说明 结果

=COUNTIFS(B2:D2,"=是") 计数王伟超出苹果汁、牛奶和酱油销售配额的次数。 1

=COUNTIFS(B2:B5,"=是",C2:C5,"=是") 计算有多少销售人员同时超出其苹果汁和牛奶配额。 2

=COUNTIFS(B5:D5,"=是",B3:D3,"=是") 计数李芳和赵军超出苹果汁、牛奶和酱油销售配额的次数。 1

 

示例 2

如果将示例复制到一个空白工作表中，可能会更容易理解该示例。

如何复制示例？

选择本文中的示例。

 要点   不要选择行或列标题。

从“帮助”中选择示例

按 Ctrl+C。

在 Excel 中，创建一个空白工作簿或工作表。

在工作表中，选择单元格 A1，然后按 Ctrl+V。

 要点   若要使该示例能够正常工作，必须将其粘贴到工作表的单元格 A1 中。

要在查看结果和查看返回结果的公式之间进行切换，请按 Ctrl+`（重音符），或在“公式”选项卡上的“公式审核”组中，单击“显示公式”按钮。

将示例复制到一个空白工作表中后，可以按照您的需要改编示例。

 

1

2

3

4

5

6

7

8

9

10

11

 A B C

数据 数据 

1 5/1/2008 

2 5/2/2008 

3 5/3/2008 

4 5/4/2008 

5 5/5/2008 

6 5/6/2008 

公式 说明 结果

=COUNTIFS(A2:A7,"<6",A2:A7,">1") 计算 1 和 6 之间（不包括 1 和 6）有几个数包含在单元格 A2 到 A7 中。 4

=COUNTIFS(A2:A7, "<5",B2:B7,"<5/3/2008") 计算单元格 A2 到 A7 中包含小于 5 的数，同时在单元格 B2 到 B7 中包含早于 5/3/2008 的日期的行数。 2

=COUNTIFS(A2:A7, "<" & A6,B2:B7,"<" & B4) 说明与前例相同，但在条件中使用单元格引用而非常量。 2

 

excel2007设置下拉选项的方法

例如我们要在A2:A10单元格实现下拉选项列表

具操作流程

1,准备一个辅助列

在其他区域输入

2，选中A2:A10单元格，菜单“数据”——“数据有效性”

3，然后弹出设置数据有效性的窗口，设置——有效性条件——允许（序列）

4，然后在来源选择刚才的辅助列的数据（一般下拉选项较少的时候可以直接在这里输入文本，例如：张三,李四），确定

5，查看效果

excel的if函数多层判断用法

在遇到使用IF多层判断时，需要注意以下问题：

      1、括号的位置和个数

         =IF（A1>3,1%,IF(A1>2,0.2%,1%))

        上例中，第一层判断后，IF前不能添加括号，括号只在IF后添加（在公式中的其他函数需要的括号除外），然后在公式最后添加=IF个数的N个右括号，保持左括号和右括号个数相同。

     2、嵌套的层数

         在EXCEL里函数最多只能嵌套7层，所以设置IF判断时也只能用7个IF函数，如果有更多条件，可以用VLOOKUP函数或定义名称来实现。

在Excel中如何自定义数字格式

虽然Excel为用户提供了大量的数字格式，但还是有许多用户因为工作、学习方面的特殊要求，需要使用一些Excel未提供的数字格式，这时我们就需要利用Excel的自定义数字格式功能来帮助实现用户的这些特殊要求。

一、在Excel中创建自定义数字格式的方法

1、选择要设置格式的单元格或单元格区域。

　　2、单击“格式”菜单中的“单元格”命令，然后单击“数字”选项卡。

　　3、在“分类”列表中，单击“自定义”选项。

　　4、在“类型”框中，编辑数字格式代码以创建所需的格式。

在Excel自定义数字格式使用如下的表达方式：

　　正数的格式　　　负数的格式　　　零的格式　　　文本的格式

　　　　#,##0.00　　　 [Red]-#,##0.00　　　0.00　　　"TEXT"@

在Excel自定义数字格式的格式代码中，用户最多可以指定四个节；每个节之间用分号进行分隔，这四个节顺序定义了格式中的正数、负数、零和文本。如果用户在表达方式中只指定两个节，则第一部分用于表示正数和零，第二部分用于表示负数。如果用户在表达方式中只指定了一个节，那么所有数字都会使用该格式。如果在表达方式中要跳过某一节，则对该节仅使用分号即可。

二、自定义数字格式实例

(一)自动添加文本

在日常财务工作中，常常需要在金额数字后加单位“元”，这时就可以使用“0.00元”的自定义数字格式，当用户在单元格中录入数字后，Excel就会自动在数字后加上单位“元”。

如果需要将单元格中录入的数字“32”自动转换成“高三十二班”格式，只要将单元格的数字格式定义为“高[DBNum1]G/通用格式班”即可。

(二)在自定义数字格式中使用颜色

要设置格式中某一部分的颜色，只要在该部分对应位置用方括号键入颜色名称或颜色编号即可。Excel中可以使用的颜色名称有[黑色]、[蓝色]、[青色]、[绿色]、[洋红]、[红色]、[白色]、[黄色]八种不同的颜色，此外Excel还可以使用[颜色X]的方式来设置颜色，其中X为1-56之间的数字，代表了56种不同的颜色。

例如：当用户需要将单元格中的负数数字用蓝色来表示，只要使用“#,##0.00;[蓝色]-#,##0.00”自定义数字格式，用户在单元格中录入负数时，Excel就会将数字以蓝色显示。

(三)在自定义数字格式中使用条件格式

在Excel自定义数字格式中用户可以进行条件格式的设置。当单元格中数字满足指定的条件时，Excel可以自动将条件格式应用于单元格。Excel自定义数字格式中可以使用如下六种标准的比较运算符：

　　　运算符　　　　含义

　　　　　=　　　　　　 等于

　　　　　>　　　　　　 大于

　　　　　<　　　　　　 小于

　　　　　>=　　　　　　大于等于

　　　　　<=　　　　　　小于等于

　　　　　<>　　　　　　不等于

在Excel中要想设置满足指定条件数字的格式，在自定义数字格式代码中必须加入带中括号的条件，条件由比较运算符和数值两部分组成。

例如：在学生成绩工作表中，当我们想以红色字体显示大于等于90分的成绩，以蓝色字体显示小于60分的成绩时，其余的成绩则以黑色字体显示，这时只需将自定义数字格式设置为“[红色][>=90];[蓝色][<60];[黑色]”即可。

值得注意的是，当你在以后需要继续使用刚才所创建的成绩条件自定义数字格式时，你会发现在“单元格格式”的“自定义”分类类型中找不到“[红色][>=90];[蓝色][<60];[黑色]”格式，这是因为Excel自动将你所创建的“[红色][>=90];[蓝色][<60]”格式修改成“[[红色][>=90]G/通用格式;[蓝色][<60]G/通用格式;[黑色]G/通用格式”，你只需选择此格式即可达到同样的使用效果。

(四)隐藏单元格中的数值

在Excel工作表中，有时为了表格的美观或者别的因素，我们希望将单元格中的数值隐藏起来，这时我们使用“;;;”(三个分号)的自定义数字格式就可达到此目的。这样单元格中的值只会在编辑栏出现，并且被隐藏单元格中的数值还不会被打印出来,但是该单元格中的数值可以被其它单元格正常引用。

如何将数字改变为字符串

答：=TEXT(A1,"@")

日期型编号设置方法

想在单元格里输入1，产生0207121这样一个数字，02是2002年，07是7月份，12是12日，1是输入的数值

答：如a1输入1，要在b1生成你要的数字，你可以在b1设公式=text(today(),"yymmdd")&a1

工资条制作方法（全）

函数法

工资条就是在打印的时候每隔一行都有标题行，制作的方法很多。今天对制作工资条的方法来一个大汇总。

    一、函数法生成工资条

        易操作程度：易

        易理解：难

      公式：=IF(MOD(ROW(),3),OFFSET(工资表!$A$1,(MOD(ROW()-1,3)>0)*ROUND(ROW()/3,),COLUMN(A1)-1),"")

       示例下载：upload/08072413272275.rar

一、技巧法生成工资条

        易操作程度：易

        易理解：易

    本技巧是巧妙使用辅助列隔行插入空行，然后再批量复制粘贴表头，详见下面的动画。

函数+技巧法：一、技巧法+函数法生成工资条

        易操作程度：易

        易理解：易

        灵活性：强

 公式：=VLOOKUP($A3,工资表!$A$3:$F$8,COLUMN(B1),0)

操作动画：

VBA法：现在许多单位都实现了工资代储，但每月的工资清单(工资条)还得要发给职工。如果用Excel程序处理的工资表，打印后每页只有一个表头，剪裁成条后除了每页的第一名职工外，其余的就没有表头了，这样将使职工无法清楚工资条的数字对应什么具体名目。怎样才能给每个职工的工资条加上工资表表头呢？这里笔者将解决方案介绍给大家。

      首先根据每个职工工资具有唯一电脑序号的特点，使用VBA里的判断语句，让循环从第一个电脑序号开始，如果Excel表中相邻两行的电脑序号不同，就在这两行之间插入一个空白行，然后使用循环语句，把表头的内容依次复制粘贴到每个空白行。

      下面是具体的操作步骤：

      1.在Excel中打开需要制作工资条的工资表文件。

      2.打开“工具”/“宏”(如果在菜单中没有看见“宏”，让鼠标在菜单上稍停留或者按向下的双箭头就会出现)，选择“Visual

      Basic编辑器”(图1)打开VBA编辑器窗口，在VBAProject的工程资源管理器中双击Sheet1，会出现代码编辑窗口，在代码编辑窗口输入如下代码(图2)：

      Sub gongzitiao()

      Application.ScreenUpdating = False

      '为避免破坏表一，将表一内容完整复制到表二

      Sheets(1).[A1].CurrentRegion.Copy Sheets(2).[A1]

      '定义循环变量的最大值不小于表二中职工数的二倍(我校职工总数为2564)

      a = (Application.WorksheetFunction.CountA(Sheets(2).[a1:a2600] * 2

      '如果第一列(职工的工资电脑序号)上下单元格的值不相等，则在它们之间插入一个空白行

      For i = 3 To a

      If Sheets(2).Cells(i，1)<>Sheets(2).Cells(i + 1，1) And (Sheets(2).Cells(i，

      1) <> "") Then

      Sheets(2).Rows(i + 1).Insert

      End If

      '如果第一列中的单元格为空，则将表中[A2:M2](工资清单表头项目)复制到此行

      If Sheets(2).Cells(i，1) = "" Then

      Sheets(2).[A2:M2].Copy Sheets(2).Cells(i，1)

      End If

      Next

      Application.ScreenUpdating = True

      End Sub

      3.检查源代码编辑无误后，在VBA窗口中打开菜单“运行”/“运行宏”，将弹出“宏”窗口，你只需单击“运行”即可。

      4.待宏运行完毕，再回到Excel，单击工资表下方的“Sheet2”，于是在每两个相邻职工间就插入了工资清单项目(图3)，再用打印机打印并裁条后，就可以发放给每位职工了。

      该操作在Excel 2000下调试通过

工作表中可看到的彩色三角形的功能介绍(Excel)

可在单元格中出现的彩色三角形分别为绿色（表示错误）和红色（表示批注）。

绿色三角形

红色三角形

 提示   错误检查三角形指示器的颜色是可以更改的。使用下列过程可更改颜色：

1. 单击“文件”选项卡，然后单击“选项”。

2. 单击“公式”类别，然后在“错误检查”下，选中“允许后台错误检查”复选框。

3. 单击“使用此颜色标识错误”按钮，然后从调色板中选择一种新的颜色。

关于SUMPRODUCT 函数的使用方法(Excel)

适用于: Microsoft Excel 2010, Excel Web App, SharePoint Online for enterprises, SharePoint Online for professionals and small businesses , Excel 2007, Excel 2003

打印 全部显示全部隐藏本文介绍 Microsoft Excel 中 SUMPRODUCT 函数 （函数:函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。函数可以简化和缩短工作表中的公式，尤其在用公式执行很长或复杂的计算时。）的公式语法和用法。

说明

在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。

语法

SUMPRODUCT(array1, [array2], [array3], ...)SUMPRODUCT 函数语法具有下列参数 （参数:为操作、事件、方法、属性、函数或过程提供信息的值。）：

1     必需。其相应元素需要进行相乘并求和的第一个数组参数。

2, array3,...     可选。2 到 255 个数组参数，其相应元素需要进行相乘并求和。

说明

数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。

函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。

示例

如果将示例复制到一个空白工作表中，可能会更容易理解该示例。

如何复制示例？

选择本文中的示例。如果在 Excel Web App 中复制该示例，请每次复制并粘贴一个单元格。

 要点   请勿选择行标题或列标题。

从帮助中选择一个示例

按 Ctrl+C。

创建一个空白工作簿或工作表。

在工作表中，选择单元格 A1，然后按 Ctrl+V。如果在 Excel Web App 中工作，请对示例中的每个单元格重复复制和粘贴操作。

 要点   为使示例正常工作，必须将其粘贴到工作表的单元格 A1 中。

要在查看结果和查看返回结果的公式之间进行切换，请按 Ctrl+`（重音符），或在 “公式”选项卡上的 “公式审核”组中单击 “显示公式”按钮。

在将示例复制到空白工作表中后，您可以根据自己的需求对它进行调整。

  

1

2

3

4

5

6

 A B C D

1 1 2 2

3 4 2 7

8 6 6 7

1 9 5 3

公式 说明（结果）  

=SUMPRODUCT(A2:B4, C2:D4) 两个数组的所有元素对应相乘，然后把乘积相加，即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3。(156)  

 

 注释   上例所返回的乘积之和，与以数组形式在 Excel 桌面应用程序中输入的公式 SUM(A2:B4*C2:D4) 的计算结果相同。使用数组公式可以为类似于 SUMPRODUCT 函数的计算提供更通用的解法。例如，使用公式 =SUM(A2:B4^2) 并按 Ctrl+Shift+Enter 可以计算 A2:B4 中所有元素的平方和。

在excel表格中如何设置字符间距？

在excel中不能直接加宽字符间距。你可以通过调整列宽大小，然后设置分散对齐方式来实现。

选择该单元格，右键-〉设置单元格格式-〉切换到“对齐”标签页,“水平对齐”方式选择“分散对齐”，确定,然后调整列宽,让文字的距离调整到自己认可的间距就行了

在EXCEL中如何统计字数

用{=SUM(LEN(范围))}试试

EXCEL里录制宏全过程

录制宏时，宏录制器会记录完成需要宏来执行的操作所需的一切步骤。记录的步骤中不包括在功能区上导航的步骤。

如果“开发工具”选项卡不可用，请执行下列操作以显示此选项卡：

单击“文件”选项卡。

单击“选项”，然后单击“自定义功能区”。

在“自定义功能区”类别的“主选项卡”列表中，选中“开发工具”复选框，然后单击“确定”。

若要临时将安全级别设置为启用所有宏，请执行下列操作：

在“开发工具”选项卡上的“代码”组中，单击“宏安全性”。

 

在“宏设置”下，单击“启用所有宏(不推荐，可能会运行有潜在危险的代码)”，然后单击“确定”。

注意    为帮助防止运行有潜在危险的代码，我们建议您在使用完宏之后恢复任一禁用所有宏的设置。

有关如何更改这些设置的详细信息，请参阅在 Excel 中更改宏安全设置。

在“开发工具”选项卡上的“代码”组中，单击“录制宏”。

在“宏名”框中，输入宏的名称。

注意    宏名的第一个字符必须是字母。后面的字符可以是字母、数字或下划线字符。宏名中不能有空格，下划线字符可用作单词的分隔符。如果使用的宏名还是单元格引用，则可能会出现错误消息，指示宏名无效。

若要指定用于运行宏的 Ctrl 组合快捷键 （快捷键:功能键或组合键，例如 F5 或 Ctrl+A，可用来执行菜单命令。存取键是另外一种组合键，例如 Alt+F，可以将焦点移至菜单、命令或控件。），请在“快捷键”框中，键入要使用的任何小写字母或大写字母。

注意    当包含该宏的工作簿打开时，该快捷键将覆盖任何对等的默认 Excel 快捷键。

如需查看在 Excel 中已分配的 Ctrl 组合快捷键的列表，请参阅 Excel 快捷键和功能键。

在“保存在”列表中，选择要用来保存宏的工作簿。

提示    如果要在每次使用 Excel 时都能够使用宏，请选择“个人宏工作簿”。在选择“个人宏工作簿”时，如果不存在隐藏的个人宏工作簿 (Personal.xlsb)，Excel 会创建一个，并将宏保存在此工作簿中。在 Windows Vista 中，此工作簿保存在 C:\Users\用户名\AppData\Local\Microsoft\Excel\XLStart 文件夹中。在 Microsoft Windows XP 中，此工作簿保存在 C:\Documents and Settings\用户名\Application Data\Microsoft\Excel\XLStart 文件夹中。每次启动 Excel 时，XLStart 文件夹中的工作簿都会自动打开。如果想在另一个工作簿中自动运行个人宏工作簿中的宏，则还必须将该工作簿保存在 XLStart 文件夹中，以便在启动 Excel 时同时打开这两个工作簿。

在“说明”框中，键入对该宏的描述。

单击“确定”开始录制。

执行要录制的操作。

在“开发工具”选项卡上的“代码”组中，单击“停止录制” 。

提示    您也可以单击状态栏左侧的“停止录制”

关于SUMPRODUCT 函数的使用方法(Excel)

适用于: Microsoft Excel 2010, Excel Web App, SharePoint Online for enterprises, SharePoint Online for professionals and small businesses , Excel 2007, Excel 2003

打印 全部显示全部隐藏本文介绍 Microsoft Excel 中 SUMPRODUCT 函数 （函数:函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。函数可以简化和缩短工作表中的公式，尤其在用公式执行很长或复杂的计算时。）的公式语法和用法。

说明

在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。

语法

SUMPRODUCT(array1, [array2], [array3], ...)SUMPRODUCT 函数语法具有下列参数 （参数:为操作、事件、方法、属性、函数或过程提供信息的值。）：

1     必需。其相应元素需要进行相乘并求和的第一个数组参数。

2, array3,...     可选。2 到 255 个数组参数，其相应元素需要进行相乘并求和。

说明

数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。

函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。

示例

如果将示例复制到一个空白工作表中，可能会更容易理解该示例。

如何复制示例？

选择本文中的示例。如果在 Excel Web App 中复制该示例，请每次复制并粘贴一个单元格。

 要点   请勿选择行标题或列标题。

从帮助中选择一个示例

按 Ctrl+C。

创建一个空白工作簿或工作表。

在工作表中，选择单元格 A1，然后按 Ctrl+V。如果在 Excel Web App 中工作，请对示例中的每个单元格重复复制和粘贴操作。

 要点   为使示例正常工作，必须将其粘贴到工作表的单元格 A1 中。

要在查看结果和查看返回结果的公式之间进行切换，请按 Ctrl+`（重音符），或在 “公式”选项卡上的 “公式审核”组中单击 “显示公式”按钮。

在将示例复制到空白工作表中后，您可以根据自己的需求对它进行调整。

  

1

2

3

4

5

6

 A B C D

1 1 2 2

3 4 2 7

8 6 6 7

1 9 5 3

公式 说明（结果）  

=SUMPRODUCT(A2:B4, C2:D4) 两个数组的所有元素对应相乘，然后把乘积相加，即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3。(156)  

 

 注释   上例所返回的乘积之和，与以数组形式在 Excel 桌面应用程序中输入的公式 SUM(A2:B4*C2:D4) 的计算结果相同。使用数组公式可以为类似于 SUMPRODUCT 函数的计算提供更通用的解法。例如，使用公式 =SUM(A2:B4^2) 并按 Ctrl+Shift+Enter 可以计算 A2:B4 中所有元素的平方和。

关于SUMSQ 函数的学习应用(Excel)

适用于: Microsoft Excel 2010, Excel Web App, SharePoint Online for enterprises, SharePoint Online for professionals and small businesses

打印 全部显示全部隐藏本文介绍 Microsoft Excel 中 SUMSQ 函数 （函数:函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。函数可以简化和缩短工作表中的公式，尤其在用公式执行很长或复杂的计算时。）的公式语法和用法。

说明

返回参数的平方和。

语法

SUMSQ(number1, [number2], ...)SUMSQ 函数语法具有下列参数 （参数:为操作、事件、方法、属性、函数或过程提供信息的值。）：

Number1, number2, ...    Number1 是必需的，后续数值是可选的。这是用于计算平方和的一组参数，参数的个数可以为 1 到 255 个。也可以用单一数组或对某个数组的引用来代替用逗号分隔的参数。

说明

参数可以是数字或者是包含数字的名称、数组或引用。

逻辑值和直接键入到参数列表中代表数字的文本被计算在内。

如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。

如果参数为错误值或为不能转换为数字的文本，将会导致错误。

示例

如果将示例复制到一个空白工作表中，可能会更容易理解该示例。

如何复制示例？

选择本文中的示例。

 要点   不要选择行或列标题。

从“帮助”中选择示例

按 Ctrl+C。

在 Excel 中，创建一个空白工作簿或工作表。

在工作表中，选择单元格 A1，然后按 Ctrl+V。

 要点   若要使该示例能够正常工作，必须将其粘贴到工作表的单元格 A1 中。

要在查看结果和查看返回结果的公式之间进行切换，请按 Ctrl+`（重音符），或在“公式”选项卡上的“公式审核”组中，单击“显示公式”按钮。

将示例复制到一个空白工作表中后，可以按照您的需要改编示例。

  

1

2

 A B

公式 说明（结果）

=SUMSQ(3, 4) 3 和 4 的平方和 (25)

 

关于SUMIF 函数的学习资料(Excel)

适用于: Microsoft Excel 2010, Excel Web App, SharePoint Online for enterprises, SharePoint Online for professionals and small businesses , Excel 2007, Excel 2003

打印 全部显示全部隐藏本文介绍 Microsoft Excel 中 SUMIF 函数 （函数:函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。函数可以简化和缩短工作表中的公式，尤其在用公式执行很长或复杂的计算时。）的公式语法和用法。

说明

使用 SUMIF 函数可以对区域 （区域:工作表上的两个或多个单元格。区域中的单元格可以相邻或不相邻。）中符合指定条件的值求和。例如，假设在含有数字的某一列中，需要让大于 5 的数值相加，请使用以下公式：

=SUMIF(B2:B25,">5")在本例中，应用条件的值即要求和的值。如果需要，可以将条件应用于某个单元格区域，但却对另一个单元格区域中的对应值求和。例如，使用公式 =SUMIF(B2:B5, "俊元", C2:C5) 时，该函数仅对单元格区域 C2:C5 中与单元格区域 B2:B5 中等于“俊元”的单元格对应的单元格中的值求和。

 注释   若要根据多个条件对若干单元格求和，请参阅 SUMIFS 函数。

语法

SUMIF(range, criteria, [sum_range])SUMIF 函数语法具有以下参数 （参数:为操作、事件、方法、属性、函数或过程提供信息的值。）：

range    必需。用于条件计算的单元格区域。每个区域中的单元格都必须是数字或名称、数组或包含数字的引用。空值和文本值将被忽略。

criteria    必需。用于确定对哪些单元格求和的条件，其形式可以为数字、表达式、单元格引用、文本或函数。例如，条件可以表示为 32、">32"、B5、32、"32"、"苹果" 或 TODAY()。

 要点   任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号 (") 括起来。如果条件为数字，则无需使用双引号。

sum_range    可选。要求和的实际单元格（如果要对未在 range 参数中指定的单元格求和）。如果 sum_range 参数被省略，Excel 会对在 range 参数中指定的单元格（即应用条件的单元格）求和。

 注释

sum_range 参数与 range 参数的大小和形状可以不同。求和的实际单元格通过以下方法确定：使用 sum_range 参数中左上角的单元格作为起始单元格，然后包括与 range 参数大小和形状相对应的单元格。例如：

如果区域是 并且 sum_range 是 则需要求和的实际单元格是

A1:A5 B1:B5 B1:B5

A1:A5 B1:B3 B1:B5

A1:B4 C1:D4 C1:D4

A1:B4 C1:C2 C1:D4

可以在 criteria 参数中使用通配符（包括问号 (?) 和星号 (*)）。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符 (~)。

注解

使用 SUMIF 函数匹配超过 255 个字符的字符串时，将返回不正确的结果 #VALUE!。

示例

示例 1

如果将示例复制到一个空白工作表中，可能会更容易理解该示例。

如何复制示例？

选择本文中的示例。如果在 Excel Web App 中复制该示例，请每次复制并粘贴一个单元格。

 要点   请勿选择行标题或列标题。

从帮助中选择一个示例

按 Ctrl+C。

创建一个空白工作簿或工作表。

在工作表中，选择单元格 A1，然后按 Ctrl+V。如果在 Excel Web App 中工作，请对示例中的每个单元格重复复制和粘贴操作。

 要点   为使示例正常工作，必须将其粘贴到工作表的单元格 A1 中。

要在查看结果和查看返回结果的公式之间进行切换，请按 Ctrl+`（重音符），或在 “公式”选项卡上的 “公式审核”组中单击 “显示公式”按钮。

在将示例复制到空白工作表中后，您可以根据自己的需求对它进行调整。

  

1

2

3

4

5

6

7

8

9

10

 A B C

财产价值 佣金 数据

100,000 7,000 250,000

200,000 14,000 

300,000 21,000 

400,000 28,000 

公式 说明 结果

=SUMIF(A2:A5,">160000",B2:B5) 财产价值高于 160,000 的佣金之和。 63,000

=SUMIF(A2:A5,">160000") 高于 160,000 的财产价值之和。 900,000

=SUMIF(A2:A5,300000,B2:B5) 财产价值等于 300,000 的佣金之和。 21,000

=SUMIF(A2:A5,">" & C2,B2:B5) 财产价值高于单元格 C2 中值的佣金之和。 49,000

 

示例 2

如果将示例复制到一个空白工作表中，可能会更容易理解该示例。

如何复制示例？

选择本文中的示例。如果在 Excel Web App 中复制该示例，请每次复制并粘贴一个单元格。

 要点   请勿选择行标题或列标题。

从帮助中选择一个示例

按 Ctrl+C。

创建一个空白工作簿或工作表。

在工作表中，选择单元格 A1，然后按 Ctrl+V。如果在 Excel Web App 中工作，请对示例中的每个单元格重复复制和粘贴操作。

 要点   为使示例正常工作，必须将其粘贴到工作表的单元格 A1 中。

要在查看结果和查看返回结果的公式之间进行切换，请按 Ctrl+`（重音符），或在 “公式”选项卡上的 “公式审核”组中单击 “显示公式”按钮。

在将示例复制到空白工作表中后，您可以根据自己的需求对它进行调整。

  

1

2

3

4

5

6

7

8

9

10

11

12

 A B C

类别 食物 销售额

蔬菜 西红柿 2300

蔬菜 西芹 5500

水果 橙子 800

 黄油 400

蔬菜 胡萝卜 4200

水果 苹果 1200

公式 说明 结果

=SUMIF(A2:A7,"水果",C2:C7) “水果”类别下所有食物的销售额之和。 2000

=SUMIF(A2:A7,"蔬菜",C2:C7) “蔬菜”类别下所有食物的销售额之和。 12000

=SUMIF(B2:B7,"西*",C2:C7) 以“西”开头的所有食物（西红柿、西芹）的销售额之和。 4300

=SUMIF(A2:A7,"",C2:C7) 未指定类别的所有食物的销售额之和。 400

如何将excel多个工作表合并成为一个表?

具体内容为：在一个EXCEL文件中，有100张表格，现需建立一张总汇，总表需要有所以分表的名称，和最后各表结存的总数。也就是说需提取每张表的一个名称和一个结存数据。结存的数字在表格的位置不定。谢谢啊~

注：表一

名称 收入 发出 结存

电脑 100 50 50

30 20

表二

名称 收入 发出 结存

CPU 120 50 70

表三

名称 收入 发出 结存

硬盘 110 30 80

完成后效果：

总表

名称 结存

电脑 20

CPU 70

硬盘 80

问题补充：

注：表一

名称 收入 发出 结存

电脑 100 50 50

30 20

表二

名称 收入 发出 结存

CPU 120 50 70

表三

名称 收入 发出 结存

硬盘 110 30 80

完成后效果：

总表

名称 结存

电脑 20

CPU 70

硬盘 80

方法如下

把所有工作表放入"D:\data\" ,工作表要求规范:

第一行:名称(在A列) 收入 发出 结存

统计数据在最后一行:CPU(在A列,不能为空) 120 50 70

新建一个表,拷贝下面代码到VBA

Sub 多工作薄转换()

awn = ThisWorkbook.Name

fm = InputBox("请输入软换文件所在的路经" & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "如：", "提示", "D:\data\")

Set fs = CreateObject("Scripting.FileSystemObject")

On Error GoTo E1

Set Folders = fs.GetFolder(fm)

Set Files = Folders.Files

For Each file In Files

Filename = Right(file.Name, 4)

If Filename = ".XLS" Or Filename = ".xls" Then

On Error GoTo E2

Workbooks.Open Filename:=file

Sheets(1).Copy Before:=Workbooks(awn).Sheets(1)

Sheets(1).Name = Left(file.Name, Len(file.Name) - 4)

ActiveSheet.Range("2:" & ActiveSheet.UsedRange.Rows.Count - 1).Delete Shift:=xlUp

Windows(file.Name).Close

End If

Next

Set fs = Nothing

End

E1:

MsgBox "输入路经有误!", , "提示"

Set fs = Nothing

End

E2:

MsgBox "打开文件" & file & "出错!文件是否已经打开？", , "提示"

Set fs = Nothing

End

End Sub

数据转换为文本

答：数据转换为文本方法是，在输入这前添加'号。如果是已输入的数字，可以选取这一列，数据-分列-分列向导第一二步默认，第三步中选“文件”，确定后即可把数字转换成文本

在excel添加页眉和页脚的方法

每张工作表上只能设置一种自定义页眉和页脚。如果创建了新的自定义页眉或页脚，它将替换工作表上的其他自定义页眉和页脚。

1. 单击相应的工作表。

2. 在“视图”菜单上，单击“页眉和页脚”。

3. 若要根据已有的页眉或页脚来创建自定义页眉或页脚，请在“页眉”或“页脚”框中单击所需的页眉或页脚选项。

4. 单击“自定义页眉”或“自定义页脚”。

5. 单击“左”、“中”或“右”编辑框，再单击相应的按钮，然后在所需的位置插入相应的页眉或页脚内容。

6. 请执行下列一项或多项操作：

o 若要在页眉或页脚中添加其他文本，请在“左”、“中”或“右”编辑框中输入相应的文本。

o 若要在某一位置另起一行，请按 Enter。

o 若要删除某一部分的页眉或页脚，请选中需要删除的内容，然后按 Backspace。

注意

o 可用“页眉”或“页脚”对话框中的按钮来设置页眉和页脚的格式或进一步自定义页眉和页脚。

o 请确认页眉或页脚的边距有足够的空间放置自定义页眉或页脚

VLOOKUP函数的使用方法（入门级）

VLOOKUP函数是Excel中几个最重函数之一，为了方便大家学习，兰色幻想特针对VLOOKUP函数的使用和扩展应用，进行一次全面综合的说明。本文为入门部分

     一、入门级

      VLOOKUP是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。它的基本语法为：

      VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找)

下面以一个实例来介绍一下这四个参数的使用

     例1：如下图所示，要求根据表二中的姓名，查找姓名所对应的年龄。  

   公式：B13 =VLOOKUP(A13,$B$2:$D$8,3,0)  

   参数说明：

       1 查找目标：就是你指定的查找的内容或单元格引用。本例中表二A列的姓名就是查找目标。我们要根据表二的“姓名”在表一中A列进行查找。

        公式：B13 =VLOOKUP(A13,$B$2:$D$8,3,0)   

       2 查找范围（VLOOKUP(A13,$B$2:$D$8,3,0) ）：指定了查找目标，如果没有说从哪里查找，EXCEL肯定会很为难。所以下一步我们就要指定从哪个范围中进行查找。VLOOKUP的这第二个参数可以从一个单元格区域中查找，也可以从一个常量数组或内存数组中查找。本例中要从表一中进行查找，那么范围我们要怎么指定呢？这里也是极易出错的地方。大家一定要注意，给定的第二个参数查找范围要符合以下条件才不会出错：

        A 查找目标一定要在该区域的第一列。本例中查找表二的姓名，那么姓名所对应的表一的姓名列，那么表一的姓名列（列）一定要是查找区域的第一列。象本例中，给定的区域要从第二列开始，即$B$2:$D$8，而不能是$A$2:$D$8。因为查找的“姓名”不在$A$2:$D$8区域的第一列。

        B 该区域中一定要包含要返回值所在的列，本例中要返回的值是年龄。年龄列（表一的D列）一定要包括在这个范围内，即：$B$2:$D$8，如果写成$B$2:$C$8就是错的。

       3 返回值的列数（B13 =VLOOKUP(A13,$B$2:$D$8,3,0)）。这是VLOOKUP第3个参数。它是一个整数值。它怎么得来的呢。它是“返回值”在第二个参数给定的区域中的列数。本例中我们要返回的是“年龄”，它是第二个参数查找范围$B$2:$D$8的第3列。这里一定要注意，列数不是在工作表中的列数（不是第4列），而是在查找范围区域的第几列。如果本例中要是查找姓名所对应的性别，第3个参数的值应该设置为多少呢。答案是2。因为性别在$B$2:$D$8的第2列中。

       4 精确OR模糊查找（VLOOKUP(A13,$B$2:$D$8,3,0)  ），最后一个参数是决定函数精确和模糊查找的关键。精确即完全一样，模糊即包含的意思。第4个参数如果指定值是0或FALSE就表示精确查找，而值为1 或TRUE时则表示模糊。这里兰色提醒大家切记切记，在使用VLOOKUP时千万不要把这个参数给漏掉了，如果缺少这个参数默为值为模糊查找，我们就无法精确查找到结果了。  

      好了，关于VLOOKUP函数的入门级应用就说到这里，VLOOKUP函数可不只是这么简单的查找，我们讲的还只是1/10的用法。其他的没法在一篇文章中说明。敬请期待“VLOOKUP的使用方法-进阶篇”吧。

   本文为兰色幻想原创，转载请注明作者和转自“excel精英培训”

兰色幻想

VLOOKUP函数的使用方法（初级篇）  (10/16/2011 11:48:49)

原创：VLOOKUP函数的使用方法(高级篇)  (11/22/2011 10:58:36)

VLOOKUP函数的使用方法（进阶篇）  (10/20/2011 08:01:32)

的《Excel VBA基础入门》第2版上市了，希望大家支持一下。

vlookup代替if实现任意多条件判断

答：在excel中函数最多只能嵌套七层，IF函数也不能例外，遇到需要进行多次判断的怎么办呢？可以用VLOOKUP函数替代。

    例如：下表中需要根据提供的销售额判断提成比率，这里可能有很多，为了演示方便，只列中三种。这种情况下怎么判断呢？

公式1：=vlookup（C2，A$1：B$100，2，0）

     如果区域不想放在单元格区域，可以直接写成常量数组，即：

     =VLOOKUP(C2,{"销售额","提成比率";"电视",0.1;"洗衣机",0.05;"吸油烟机",0.06},2,0)

     如果IF是进行的区间判断，怎么用VLOOKUP替换呢？答案是可以用vlookup的模糊查找功能。看下例：

   公式为：=VLOOKUP(D2,A1:B11,2)

VLOOKUP函数的使用方法（初级篇）

上一讲咱们学习了VLOOKUP的基本用法和示例，本讲将介绍VLOOKUP在使用中的一些小技巧。

Excel函数速成教程全系列(包括VLOOKUP函数,IF函数,offset函数,sumif函数等66个函数）预计6月初全部录制完成，现已在淘宝开始预订（8折优惠)，地址：http://item.taobao.com/item.htm?id=175********

一、VLOOKUP多行查找时复制公式的问题

    VLOOKUP函数的第三个参数是查找返回值所在的列数，如果我们需要查找返回多列时，这个列数值需要一个个的更改，比如返回第2列的，参数设置为2，如果需要返回第3列的，就需要把值改为3。。。如果有十几列会很麻烦的。那么能不能让第3个参数自动变呢？向后复制时自动变为2，3，4，5。。。   

    在EXCEL中有一个函数COLUMN，它可以返回指定单元格的列数，比如

         =COLUMNS（A1） 返回值1

         =COLUMNS（B1） 返回值2

   而单元格引用复制时会自动发生变化，即A1随公式向右复制时会变成B1，C1，D1。。这样我们用COLUMN函数就可以转换成数字1，2，3，4。。。 

    例：下例中需要同时查找性别，年龄，身高，体重。

     公式：=VLOOKUP($A13,$B$2:$F$8,COLUMN(B1),0)

  公式说明：这里就是使用COLUMN（B1）转化成可以自动递增的数字。

二、VLOOKUP查找出现错误值的问题。

    1、如何避免出现错误值。

     EXCEL2003 在VLOOKUP查找不到，就#N/A的错误值，我们可以利用错误处理函数把错误值转换成0或空值。

      即：=IF(ISERROR(VLOOKUP（参数略）),"",VLOOKUP（参数略）

     EXCEL2007，EXCEL2010中提供了一个新函数IFERROR，处理起来比EXCEL2003简单多了。

     IFERROR（VLOOKUP（），"") 

    2、VLOOKUP函数查找时出现错误值的几个原因

      A、实在是没有所要查找到的值

      B、查找的字符串或被查找的字符中含有空格或看不见的空字符，验证方法是用=号对比一下，如果结果是FALSE，就表示两个单元格看上去相同，其实结果不同。

      C、参数设置错误。VLOOKUP的最后一个参数没有设置成1或者是没有设置掉。第二个参数数据源区域，查找的值不是区域的第一列，或者需要反回的字段不在区域里，参数设置在入门讲里已注明，请参阅。

     D、数值格式不同，如果查找值是文本，被查找的是数字类型，就会查找不到。解决方法是把查找的转换成文本或数值，转换方法如下：

     文本转换成数值：*1或--或/1

     数值转抱成文本：&""  

     VLOOKUP函数的初级篇就说到这里了，咱们下一讲将介绍VLOOKUP的模糊查找有、反向查找等。敬请大家关注：VLOOKUP函数的使用方法（进阶篇）。本系列为兰色幻想原创。转载请注明作者和转自“EXCEL精英培训”

     附：入门篇链接http://www.excelpx.com/home/show.aspx?id=31325&cid=10

VLOOKUP函数的使用方法（进阶篇）

在学习了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%。

  附： VLOOKUP函数的使用方法（入门级）

       VLOOKUP函数的使用方法（初级篇）

   

示例文件下载：upload/2011_10/11102008481887.rar    

注：本系列为兰色幻想原创，转载请注明作者和转自“Excel精英培训”

小妖整理offset函数教程

这是小妖老师整理的offset函数使用教程，非常的全面，主要内容包括：

Offset的基本用法

Offset的二维引用

Offset的特点

附：Match函数基本用法

用Offset套Match替代VLookup

Offset反向查找

Offset错位查找

“套打”

查找图片

动态透视表区域

可以替代Offset的函数

转置

隔行插入

多个结果查找

下载地址：

upload/2011_11/11112119128390.zip

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

前言：前面我们分别学习了VLOOKUP函数的入门、初级和进阶篇。今天我们学习VLOOKUP函数的高级应用部分-VLOOKUP函数的数组应用。（本文由兰色幻想原创，转载请注明转自excel精英培训）

 一、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函数在数组运算中还有着其他应用，但只是配角了，所以本系列不再介绍。由于笔者水平有限，不免有错漏之处，请大家多多指点。

                  -------兰色幻想于2011年11月22日、

附：      VLOOKUP函数的使用方法（入门级）

        VLOOKUP函数的使用方法（初级篇）

        VLOOKUP函数的使用方法（进阶篇）

示例下载：

upload/2011_11/11112212272422.rar

excel提取唯一品牌的公式

使用公式提取唯一有多种方法，有一般公式方法，也有数组公式方法，下面的示例是使用了LOOKUP函数和MATCH函数完成提取。

示例下载：

upload/2011_12/11120410247442.rar

=LOOKUP(1,0/ISNA(MATCH($B$2:$B$16,F$2:F2,)),$B$2:$B$16)

Indirect函数的应用（小妖讲Excel）

本周二（2011-12-6）公开课的课件，小妖老师将带大家一起进入indirect函数的神奇世界，领略这个可以让单元格，工作表、工作簿名称中插入变量的函数用法。

VLOOKUP函数查找错误时的解决方法

 VLOOKUP函数是一个非常常用的函数，但使用不当就会返回错误。下面是一个收集了差不多全部VLOOKUP常见错误的文档，并附有解决方法。纯对值得大家收藏。

upload/2012_01/12011400159166.rar

SUMIF函数的一个怪异用途(excel)

Excel表格中的SUMIF函数在条件求和时经常使用，我们一般习惯于于根据某一列计算另一列的数字之和。

  例1：假设置A列是部门，B列是姓名，C列是工资，可以用下面的公式算出所有财务部员工的工资总和：

     =SUM（A1：A100，"财务部",C1:C100)

  我们今天说的不是这个正规用法，而是给大家介绍一种看上去和SUMIF毫无一毛钱关系的例子。

  例2  如下图中，是一些不规则的数据，我们只关注填充颜色的区域，现在我们要计算所有A下面数字之和，看上去这个题没有好的思路，其实这个题恰好可以用SUMIF函数轻松解决。

  =SUMIF(B2:E7,B12,B3:E8)

 我们分析一下这个公式，第二个参数是条件我们可以了解，第一个参数和第二个参数有什么关系呢？大家仔细看会发现第三个参数正好是第一个参数的区域向下偏移一个单位。

 总结，SUMIF函数并不是只对规则区域求和，还可以对不规则的排列进行条件求和。

SUMIFS和SUMIF函数的区别

SUMIFS对某一区域内满足多重条件的单元格求和。

要点  SUMIFS 和 SUMIF 的参数顺序不同。具体而言，sum_range 参数在 SUMIFS 中是第一个参数，而在 SUMIF 中则是第三个参数。如果要复制和编

辑这些相似函数，请确保按正确顺序放置参数。语法

SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)

Sum_range  是要求和的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。空值和文本值会被忽略。

Criteria_range1, criteria_range2, …  是计算关联条件的 1 至 127 个区域。

Criteria1, criteria2, …  是数字、表达式、单元格引用或文本形式的 1 至 127 个条件，用于定义要对哪些单元格求和。例如，条件可以表示为

32、""32""、"">32""、""apples"" 或 B4。

我们可以从AVERAGEIF函数表中看到2003函数公式中有用到SUMIF,但是到了多条件AVERAGEIFS函数表中SUMIF却有点无能为力了，但是SUMIFS却是轻而

易举的解决了。

EXCEL2003时没有此新增函数公式为

方法一:数组公式

=SUM((B4:B11="一班")*(D4:D11="男")*F4:F11)

注此公式如不想三键组合可用SUMPRODUCT但运算原理仍是数组

=SUMPRODUCT((B4:B11="一班")*(D4:D11="男")*F4:F11)

EXCEL2007版SUMIFS公式为

=SUMIFS(F4:F11,B4:B11,"一班",D4:D11,"男")

　公式中sum_range是指F4:F11，即“语文”这列的所有成绩。Criteria_range1是指D4:D11，即性别这列数据,Criteria1是指条件“男

生”,Criteria_range2是指B4:B11，即“班级”这列数据,Criteria2是指条件“一班”。

趣味题:

熟悉函数三维运算的人都知道,SUMIF在三维合计运算中是一个非常不错的方法,但是SUMIF只能单条件,从而限制了此函数的更好运用,而SUMIFS就顺利

的解决了此问题。

在这个工作薄中有1、2、3三个工作表，要求计算日期大于11月1日小于12月5日项目为“乙”的数值合计结果

公式为：

=SUM(SUMIFS(INDIRECT(ROW($A1:$A3)&"!"&ADDRESS(2,MATCH("乙",'1'!1:1,0))&":"&ADDRESS(6,MATCH("乙",'1'!1:1,0))),INDIRECT(ROW($A1:$A3)

&"!A2:A6"),">"&DATE(2007,11,1),INDIRECT(ROW($A1:$A3)&"!A2:A6"),"<"&DATE(2007,12,5)))

 "COUNTIFS计算某个区域中满足多重条件的单元格数目。语法COUNTIFS(range1, criteria1,range2, criteria2…)Range1, range2, …  是计算关联

条件的 1 至 127 个区域。每个区域中的单元格必须是数字或包含数字的名称、数组或引用。空值和文本值会被忽略。

Criteria1, criteria2, …  是数字、表达式、单元格引用或文本形式的 1 至 127 个条件，用于定义要对哪些单元格进行计算。例如，条件可以表

示为 32、""32""、"">32""、""apples"" 或 B4。

我们可以从AVERAGEIF函数表中看到2003函数公式中有用到COUNTIF,但是到了多条件AVERAGEIFS函数表中COUNTIF却有点无能为力了，但是COUNTIFS却

是轻而易举的解决了。

EXCEL2003时没有此新增函数公式为

方法一:数组公式

=SUM((B4:B11="一班")*(D4:D11="男"))

注此公式如不想三键组合可用SUMPRODUCT但运算原理仍是数组

=SUMPRODUCT((B4:B11="一班")*(D4:D11="男"))

EXCEL2007版COUNTIFS公式为

=COUNTIFS(B4:B11,"一班",D4:D11,"男")

range1是指D4:D11，即性别这列数据,Criteria1是指条件“男生”,range2是指B4:B11，即“班级”这列数据,Criteria2是指条件“一班”。

Excel IF函数的精彩解释

EXCEL中关于IF函数的详细说明，感觉作者juyouhh

  if最善于解决非此即彼、非男即女、非阴即阳、非前即后、非有即无的问题。如果问题的答案是二选其一，则除了if，没有更好的办法。比如学龄，以7岁为条件，if(年龄>=7,"已到学龄","未到学龄")，做这样的判断，任何函数方法都不会更简明于此了。

            如果我们的问题都是这么简单就好了。

            有一个著名的数组公式，其内核公式为：if(match(列起点:列终点,列起点:列终点,0)=row(列起点:列终点),row(列起点:列终点),"")，作用是在一列中查找重复值各单项的所在行号，这个if就是不可或缺，不可不用的，因为到目前为止还没有其他更简明的办法来达到用公式筛选重复值的目的。但说穿了，if在这里所解决的，仍然还是一个非此即彼的问题。

            再看一例：设A列为姓名，B列为数值，求姓名甲的数值合计。{=SUM(IF(A1:A15="甲",B1:B15))}，其实也是一类问题，是{=SUM(IF(A1:A15="甲",B1:B15,0))}的一种简写，叫做非甲即0。而在数组公式中，*号可以用来替代AND，+号则可以替代OR，因此也可以进一步简写作{=SUM((A1:A15=F1)*B1:B15)}，而且条件越多，越可以体现这种写法的优点，比如再加上一列月份，求甲在3月份的数值合计，你可以省下两个if，多用一个*号就可以了（自己试试？）

       再来说不可多用。

            为什么不可多用？大致是因为：一、会增加公式写入的强度；二、降低公式的可读性；三、降低运算速率；四、不利于脑力的发挥和开掘，使人懒惰。

            例一：A1为一个数值，其范围为1-7，B1设置公式，按A1数值变化分别等于A-G。

            先来看看纯粹使用if的解法：=IF(A1=1,"a",IF(A1=2,"b",IF(A1=3,"c",IF(A1=4,"d",IF(A1=5,"e",IF(A1=6,"f",IF(A1=7,"g","")))))))

            是不是很麻烦？何止是麻烦，假如再增加两个条件，A1的数值范围为1-26，B1相应取值为A-Z，你又当如何？

            if的嵌套最大可以为7层，上面的公式已经用到了极限。虽然说可以用一些旁门左道来“突破”这个限制，但也只是一种堆沙式的游戏，如上例，可以采用以下方式：=IF(A1=1,"a",IF(A1=2,"b",IF(A1=3,"c",IF(A1=4,"d",IF(A1=5,"e",IF(A1=6,"f",IF(A1=7,"g","")))))))&IF(A1=8,"h",IF(A1=9,"I",""))……

            这样的用法，真是叫人兴味荡然，昏昏欲睡，EXCEL何必还要学下去，还不如去跟儿子摆积木更好玩呢！

            所以说，if最好不要多用。不是说不能用，而是说用多了会叫人伤心。

            其实EXCEL里准备了许多办法来替代上面的愚蠢的做法。

            比如CHOOSE函数。=CHOOSE(A1,"a","b","c","d","e","f","g","h","i")，这是不是方便多了？CHOOSE的参数清单可以有29项之多，一般足够你使用了。如果还不够，那么请看下面：

            =LOOKUP(A1,{1,2,3,4,5,6,7,8,9,10;"a","b","c","d","e","f","g","h","i","j"})，你可以尽情地输入参数，只要公式内容长度允许（规定公式内容长度为1024个字符）。

            如果真的如例中所举，只是生成A-Z等字母的话，则只需=CHAR(A1+64)就可以了。当然，实际使用中这样的巧合实在是太少了，但作为一种方法还是有提及的必要。

            一个if只能处理一个有无或是否的问题，即使这个问题可能是由诸多小的方面组合而成的。我们可以利用这一点，来达到替代if使用的目的。

            例二：公司结算日期为每月24日，帐目的月份一栏，如果超过24日，就要记为下月。

            如果按照普通思路，公式应该是这样的：=IF(DAY(A1)>24,IF(MONTH(A1)=12,1,MONTH(A1)+1),MONTH(A1))

            要用到两个if判断，外层的是判断日期是否大于24，内层的是判断月份是否在12月，因为12月的下月是1月而非13月。现在对比一下下面的公式：

            =MONTH(DATE(YEAR(A1),MONTH(A1)+1,0)+(DAY(A1)>24))

            后者用了A1日期当月最后一天的序列值，最重要的是后面加了一个由判断是否大于24而生成的逻辑值，相当于=if(day(a1)>24,1,0)。逻辑值在公式设置中是一个很重要的概念，是对问题本身的逻辑关系的判断，其中TRUE=1，FALSE=0，生成的同样是有无或是否的结果，用得恰当，会使你的公式格外生动有趣。类似的还有根据年龄计算性别、年龄的公式，也是使用逻辑值做判断，具体见我以前的相关帖子，此处不在赘述。

            是不是一定要少用if，以至于该用的也想办法不用？我曾经说，最少用到if的公式往往是最好的公式。之所以用“往往”来做限制，就是因为我没有根据来做一定如此的定论。凡事都要实事求是，具体情况具体分析。

            例三：A1为性别，B1为年龄，C1标注是否退休。条件是男60岁，女55岁。

            对这个问题，=IF(OR(AND(A1="男",B1>=60),AND(A1="女",B1>=55)),"退","未退")只用到一个if，但未必就比=IF(B1-IF(A1="男",5)>=55,"退","未退")更简洁，尽管后者用到两个if判断。当然我还是反对=IF(AND(A1="男",B1>=60),"退",IF(AND(A1="女",B1>=55),"退","未退"))这种用法的。

            就写这么多，欢迎批评。

excel表格行间距如何调整？

答：Excel行间距可以按照下面的方法调整：

      1、选取单元格区域。

      2、格式菜单-单元格-在弹出的窗口中，对齐选项卡中：垂直对齐下拉中选分散对齐。

      3、通过调整行高调整行距的高度。

ISERROR函数的用途及用法

函数名称：ISERROR

  主要功能：用于测试函数式返回的数值是否有错。如果有错，该函数返回TRUE，反之返回FALSE。

  使用格式：ISERROR(value)

  参数说明：Value表示需要测试的值或表达式。

  应用举例：输入公式：=ISERROR(A35/B35)，确认以后，如果B35单元格为空或“0”，则A35/B35出现错误，此时前述函数返回TRUE结果，反之返回FALSE。

  特别提醒：此函数通常与IF函数配套使用，如果将上述公式修改为：=IF(ISERROR(A35/B35),"",A35/B35)，如果B35为空或“0”，则相应的单元格显示为空，反之显示A35/B35的结果。

Excel函数应用之逻辑函数：IF

true,false函数

TRUE、FALSE函数用来返回参数的逻辑值，由于可以直接在单元格或公式中键入值TRUE或者FALSE。因此这两个函数通常可以不使用。

if函数

（一）IF函数说明

IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此If函数也称之为条件函数。它的应用很广泛，可以使用函数 IF 对数值和公式进行条件检测。

它的语法为IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示计算结果为 TRUE 或 FALSE 的任意值或表达式。本参数可使用任何比较运算符。

Value_if_true显示在logical_test 为 TRUE 时返回的值，Value_if_true 也可以是其他公式。Value_if_false logical_test 为 FALSE 时返回的值。Value_if_false 也可以是其他公式。

简言之，如果第一个参数logical_test返回的结果为真的话，则执行第二个参数Value_if_true的结果，否则执行第三个参数Value_if_false的结果。IF函数可以嵌套七层，用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。

Excel 还提供了可根据某一条件来分析数据的其他函数。例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使用 COUNTIF 工作表函数。如果要根据单元格区域中的某一文本串或数字求和，则可使用 SUMIF 工作表函数。

（二）IF函数应用

     1、 输出带有公式的空白表单 

    

     图5 人事分析表1

     

以图中所示的人事状况分析表为例，由于各部门关于人员的组成情况的数据尚未填写，在总计栏（以单元格G5为例）公式为：

=SUM(C5:F5)

我们看到计算为0的结果。如果这样的表格打印出来就页面的美观来看显示是不令人满意的。是否有办法去掉总计栏中的0呢？你可能会说，不写公式不就行了。当然这是一个办法，但是，如果我们利用了IF函数的话，也可以在写公式的情况下，同样不显示这些0。如何实现呢？只需将总计栏中的公式（仅以单元格G5为例）改写成：

=IF(SUM(C5:F5),SUM(C5:F5),"")

通俗的解释就是：如果SUM(C5:F5)不等于零，则在单元格中显示SUM(C5:F5)的结果，否则显示字符串。

几点说明：

（1） SUM(C5:F5)不等于零的正规写法是SUM(C5:F5)<>0，在EXCEL中可以省略<>0；

     （2） ""表示字符串的内容为空，因此执行的结果是在单元格中不显示任何字符。

如果对上述例子有了很好的理解后，我们就很容易将IF函数应用到更广泛的领域。比如，在成绩表中根据不同的成绩区分合格与不合格。现在我们就以某班级的英语成绩为例具体说明用法。

          图6

某班级的成绩如图6所示，为了做出最终的综合评定，我们设定按照平均分判断该学生成绩是否合格的规则。如果各科平均分超过60分则认为是合格的，否则记作不合格。

根据这一规则，我们在综合评定中写公式（以单元格B12为例）：

=IF(B11>60,"合格","不合格")

语法解释为，如果单元格B11的值大于60，则执行第二个参数即在单元格B12中显示合格字样，否则执行第三个参数即在单元格B12中显示不合格字样。

在综合评定栏中可以看到由于C列的同学各科平均分为54分，综合评定为不合格。其余均为合格。

3、 多层嵌套函数的应用

在上述的例子中，我们只是将成绩简单区分为合格与不合格，在实际应用中，成绩通常是有多个等级的，比如优、良、中、及格、不及格等。有办法一次性区分吗？可以使用多层嵌套的办法来实现。仍以上例为例，我们设定综合评定的规则为当各科平均分超过90时，评定为优秀。如图7所示。

          图7

说明：为了解释起来比较方便，我们在这里仅做两重嵌套的示例，您可以按照实际情况进行更多重的嵌套，但请注意Excel的IF函数最多允许七重嵌套。

根据这一规则，我们在综合评定中写公式（以单元格F12为例）：

=IF(F11>60,IF(AND(F11>90),"优秀","合格"),"不合格")

语法解释为，如果单元格F11的值大于60，则执行第二个参数，在这里为嵌套函数，继续判断单元格F11的值是否大于90（为了让大家体会一下AND函数的应用，写成AND(F11>90)，实际上可以仅写F11>90），如果满足在单元格F12中显示优秀字样，不满足显示合格字样，如果F11的值以上条件都不满足，则执行第三个参数即在单元格F12中显示不合格字样。

在综合评定栏中可以看到由于F列的同学各科平均分为92分，综合评定为优秀。

Excel函数应用之逻辑函数：AND、OR、NOT函数

这三个函数都用来返回参数逻辑值。详细介绍见下：

      （一）  AND函数

所有参数的逻辑值为真时返回 TRUE；只要一个参数的逻辑值为假即返回 FALSE。简言之，就是当AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSE。

语法为AND(logical1,logical2, ...)，其中Logical1, logical2, ... 表示待检测的 1 到 30 个条件值，各条件值可能为TRUE，可能为 FALSE。 参数必须是逻辑值，或者包含逻辑值的数组或引用。举例说明：

1、 在B2单元格中输入数字50，在C2中写公式=AND(B2>30,B2<60)。由于B2等于50的确大于30、小于60。所以两个条件值（logical）均为真，则返回结果为TRUE。



此主题相关图片如下：

2、 如果 B1-B3 单元格中的值为 TRUE、FALSE、TRUE，显然三个参数并不都为真，所以 在B4单元格中的公式=AND(B1:B3) 等于 FALSE



此主题相关图片如下：

（二）OR函数

OR函数指在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE。它与AND函数的区别在于，AND函数要求所有函数逻辑值均为真，结果方为真。而OR函数仅需其中任何一个为真即可为真。比如，上面的示例2，如果在B4单元格中的公式写为=OR(B1:B3)则结果等于TRUE



此主题相关图片如下：

（三）  NOT函数

NOT函数用于对参数值求反。当要确保一个值不等于某一特定值时，可以使用 NOT 函数。简言之，就是当参数值为TRUE时，NOT函数返回的结果恰与之相反，结果为FALSE.

比如NOT(2+2=4)，由于2+2的结果的确为4，该参数结果为TRUE，由于是NOT函数，因此返回函数结果与之相反，为FALSE

Excel函数应用之逻辑函数：countif和sumif函数

根据条件计算值

在了解了IF函数的使用方法后，我们再来看看与之类似的Excel提供的可根据某一条件来分析数据的其他函数。例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使用 COUNTIF 工作表函数。如果要根据单元格区域中的某一文本串或数字求和，则可使用 SUMIF 工作表函数。关于SUMIF函数在数学与三角函数中以做了较为详细的介绍。这里重点介绍COUNTIF的应用。

COUNTIF可以用来计算给定区域内满足特定条件的单元格的数目。比如在成绩表中计算每位学生取得优秀成绩的课程数。在工资表中求出所有基本工资在2000元以上的员工数。

语法形式为COUNTIF(range,criteria)。其中Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、"32"、">32"、"apples"。

1、成绩表

这里仍以上述成绩表的例子说明一些应用方法。我们需要计算的是：每位学生取得优秀成绩的课程数。规则为成绩大于90分记做优秀。如图8所示

          图8

根据这一规则，我们在优秀门数中写公式（以单元格B13为例）：

=COUNTIF(B4:B10,">90")

语法解释为，计算B4到B10这个范围，即jarry的各科成绩中有多少个数值大于90的单元格。

在优秀门数栏中可以看到jarry的优秀门数为两门。其他人也可以依次看到。

2、 销售业绩表

销售业绩表可能是综合运用IF、SUMIF、COUNTIF非常典型的示例。比如，可能希望计算销售人员的订单数，然后汇总每个销售人员的销售额，并且根据总发货量决定每次销售应获得的奖金。

原始数据表如图9所示（原始数据是以流水单形式列出的，即按订单号排列）

           图9 原始数据表

按销售人员汇总表如图10所示

图10 销售人员汇总表

如图10所示的表完全是利用函数计算的方法自动汇总的数据。首先建立一个按照销售人员汇总的表单样式，如图所示。然后分别计算订单数、订单总额、销售奖金。

（1） 订单数 --用COUNTIF计算销售人员的订单数。

以销售人员ANNIE的订单数公式为例。公式：

=COUNTIF($C$2:$C$13,A17)

语法解释为计算单元格A17(即销售人员ANNIE)在"销售人员"清单$C$2:$C$13的范围内（即图9所示的原始数据表）出现的次数。

这个出现的次数即可认为是该销售人员ANNIE的订单数。

（2） 订单总额--用SUMIF汇总每个销售人员的销售额。

以销售人员ANNIE的订单总额公式为例。公式：

=SUMIF($C$2:$C$13,A17,$B$2:$B$13)

此公式在"销售人员"清单$C$2:$C$13中检查单元格A17 中的文本（即销售人员ANNIE），然后计算"订单金额"列（$B$2:$B$13）中相应量的和。

这个相应量的和就是销售人员ANNIE的订单总额。

（3） 销售奖金--用IF根据订单总额决定每次销售应获得的奖金。

假定公司的销售奖金规则为当订单总额超过5万元时，奖励幅度为百分之十五，否则为百分之十。根据这一规则仍以销售人员ANNIE为例说明。公式为：

=IF(C17<50000,10%,15%)*C17

如果订单总额小于 50000则奖金为 10%；如果订单总额大于等于 50000，则奖金为 15%。

至此，我们已完全了解了EXCEL函数的逻辑函数，相信大家在实际工作中会想出更多更有用的运用

VLOOKUP函数的使用方法(问题篇)

前言：VLOOKUP函数是EXCEL最常用的查找函数，本站已对该函数进行过详细的讲解，但还是有很多同学提问与该函数有关的问题。下面再把常见的问题总结一下。

  1 VLOOKUP怎么用？

     VLOOKUP是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。它的基本语法为：

      VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找)

下面以一个实例来介绍一下这四个参数的使用

     例1：如下图所示，要求根据表二中的姓名，查找姓名所对应的年龄。  

   公式：B13 =VLOOKUP(A13,$B$2:$D$8,3,0)  

   参数说明：

       1 查找目标：就是你指定的查找的内容或单元格引用。本例中表二A列的姓名就是查找目标。我们要根据表二的“姓名”在表一中A列进行查找。

        公式：B13 =VLOOKUP(A13,$B$2:$D$8,3,0)   

       2 查找范围（VLOOKUP(A13,$B$2:$D$8,3,0) ）：指定了查找目标，如果没有说从哪里查找，EXCEL肯定会很为难。所以下一步我们就要指定从哪个范围中进行查找。VLOOKUP的这第二个参数可以从一个单元格区域中查找，也可以从一个常量数组或内存数组中查找。本例中要从表一中进行查找，那么范围我们要怎么指定呢？这里也是极易出错的地方。大家一定要注意，给定的第二个参数查找范围要符合以下条件才不会出错：

        A 查找目标一定要在该区域的第一列。本例中查找表二的姓名，那么姓名所对应的表一的姓名列，那么表一的姓名列（列）一定要是查找区域的第一列。象本例中，给定的区域要从第二列开始，即$B$2:$D$8，而不能是$A$2:$D$8。因为查找的“姓名”不在$A$2:$D$8区域的第一列。

        B 该区域中一定要包含要返回值所在的列，本例中要返回的值是年龄。年龄列（表一的D列）一定要包括在这个范围内，即：$B$2:$D$8，如果写成$B$2:$C$8就是错的。

       3 返回值的列数（B13 =VLOOKUP(A13,$B$2:$D$8,3,0)）。这是VLOOKUP第3个参数。它是一个整数值。它怎么得来的呢。它是“返回值”在第二个参数给定的区域中的列数。本例中我们要返回的是“年龄”，它是第二个参数查找范围$B$2:$D$8的第3列。这里一定要注意，列数不是在工作表中的列数（不是第4列），而是在查找范围区域的第几列。如果本例中要是查找姓名所对应的性别，第3个参数的值应该设置为多少呢。答案是2。因为性别在$B$2:$D$8的第2列中。

       4 精确OR模糊查找（VLOOKUP(A13,$B$2:$D$8,3,0)  ），最后一个参数是决定函数精确和模糊查找的关键。精确即完全一样，模糊即包含的意思。第4个参数如果指定值是0或FALSE就表示精确查找，而值为1 或TRUE时则表示模糊。这里兰色提醒大家切记切记，在使用VLOOKUP时千万不要把这个参数给漏掉了，如果缺少这个参数默为值为模糊查找，我们就无法精确查找到结果了。  

2 VLOOKUP的反向查找，需要用IF函数把数据源倒置一下。

 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"}

模糊查找就是匹配查找。

 在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%。

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一般情况下只能查找一个，那么多项该怎么查找呢？

     例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函数的基本用法是固定的，要实现高级查找，就需要借助其他函数来重构查找内容和查找数组。

excel求和公式(原创)

多表求和

如果有N个工作表中有格式相同的表格，需要合并求和时，可以用SUM函数的多表合并功能。即：

   =SUM(表1:表N！A1）就可以对表1至表N之间的所有表A1进行汇总求和

 例：汇总日报表，在汇总表C5中设置公式：

   =SUM('1日:空白'!C5)

    这是统计"1日"工作表至“空白”工作表之间的所有C5的和，这是之所以用“空白”工作表，是因为方便在空白前插入新的工作表，这些新插入的也会计算在内，如果最后一个设置为5日，那么这个汇总也只能计算5天了。

excel求和公式(原创)

小计求和

一个表格中有很多小计，当设置对这些小计求和的总计求和公式时，常常需要一个个的加小计，这样一方面公式太复杂，另一方面当小计行删除时公式会出错，当插入新的小计行时还需要再更新求和公式。

   大家想想，总和是不是小计和加明细和的1/2，因为小计等于明细，而总和等于小计和，所以我们可以这样简化公式：

     =SUM（A1：A100）/2 这样就可以算出A1：A100区域的小计和了。

excel求和公式(原创)

带单位求和

有时候数字后需要显示单位，如：“1元/斤”，而这些带单位的数字是无法求和的，那么我们该怎么进行求和呢？方法很多，这里说一个截取法

 =LEFT（ A1,LEN(A1)-3)*B1

 上述公式中，假设置A1是带单位的单价，B1是数量，那么可以从A1中截取出数字部分，然后再进行求和。

excel求和公式(原创)

隔行、列求和

很多表格中都有计划数和实际数，这时候我们需要进行隔列求和。隔列求和有一个比较简单的方法，就是用SUMFI取巧。

  如下图中设置公式：=SUMIF($A$2:$F$2,"计划",A3:F3) 就可以计算出计划数。

隔行求和同理，就不再举例

excel求和公式(原创)

单条件求和

单条件求和的方法也有很多，最常用的就是SUMIF函数，下面给出几个小例子，大家就照着学习吧：

 1 销售金额大于2000的数值进行求和=SUMIF(B2:B9,">3000")

 2 商品名称为A1的商品相对应的销售金额进行求和。=SUMIF(A2:A9,"A1",B2:B9)

 3 要求为销售金额大于D2单元格的数值进行求和。=SUMIF(B2:B9,"> "&D2)

 4 求B列中大于平均数的销售金额求和 =SUMIF(B2:B9,"> "&AVERAGE(B2:B9))

 5 求商品名称包含“A”的销售金额之和=SUMIF(A2:A9,"*A1*",B2:B9)

 6 商品名称第四五个字符为“A2”且字符总长度为6个字符的的销售金额之和   =SUMIF(A2:A9,"???A2?",B2:B9)

excel求和公式(原创)

多条件求和

1、使用SUMIF多条件求和：

符合入库数量大于4小于10的商品，对其入库金额求和

    =SUM(C2:C10)-SUMIF(C2:C9,"<=4")-SUMIF(C2:C9,">=10")

    =SUM(C2:C10)-SUM(SUMIF(C2:C9,{"<=4",">=10"}))

2、使用SUMPRDUCT函数进行求和：

   供应商A1的冰箱入库型号有多少种

     =SUMPRODUCT((B3:B11="A1")*(C3:C11="冰箱"))

   供应商A3的洗衣机入库数量是多少

     =SUMPRODUCT((B3:B11="A3")*(C3:C11="洗衣机")*E3:E11)

   示例图：   

excel求和公式(原创)

求累计和

累计和可以用indirect函数来实现，具体给一个日报表累计求和示例吧，大家下载后看看：

 =INDIRECT(DAY(C2)-1&"日!D13")+D12

upload/2012_01/12011223432387.rar

excel求和公式(原创)

按颜色求和

按颜色求和需要借用宏表函数和定义名称：

 =GET.CELL(38,Sheet1!C12)&T(NOW())

具体的大家看附件中的求和示例吧。

upload/2012_01/12011223453660.rar

ISERROR函数的用途及用法

函数名称：ISERROR

  主要功能：用于测试函数式返回的数值是否有错。如果有错，该函数返回TRUE，反之返回FALSE。

  使用格式：ISERROR(value)

  参数说明：Value表示需要测试的值或表达式。

  应用举例：输入公式：=ISERROR(A35/B35)，确认以后，如果B35单元格为空或“0”，则A35/B35出现错误，此时前述函数返回TRUE结果，反之返回FALSE。

  特别提醒：此函数通常与IF函数配套使用，如果将上述公式修改为：=IF(ISERROR(A35/B35),"",A35/B35)，如果B35为空或“0”，则相应的单元格显示为空，反之显示A35/B35的结果。

第几周时间计算