EXCEL日常使用经验积累

1、精选优质文档-倾情为你奉上(1) 点F(x)函数，会出函数列表(2) 数组公式：=SUM(A1：A10*B1：B10)(3) “ALT”+”=” 快捷求和(4) TEXT(TODAY(),”YYYY年MM月DD日”)(5) 字符串连接符：&(6) Offset（C5,5,3）.返回C5单元格下方第5行和右边第3列的引用。正数为向下向右；负数为向上向左(7) 向下复制单元格的公式,选中有公式的单元格,双击右下角,可快速复制(8) 隐藏和保护公式:将工作表设置成受保护状态;选中需要保护的公式选择”锁定”和”隐藏”(9) 合并单元格填充序列类似下面的填充品牌编号，A1=”品牌编号”, A2=

2、1,A3=MAX(A$1：A2)+1选中A2和所有需要填充序列的单元格，将光标放在A2处，CTRL+回车，将自动填充序列(10) 快速修改公式当将一个工作表移动复制到另外一个工作簿时，原来工作表中的公式会添加对应工作簿的引用名称，好用！(11) 判断单元格是否为空白，IF(B2=”,”空白”,”非空白”) ，如果有“空格”也不是“空白”，有空格不能用”判断IF(Isnumber（A1）,”是数字”,”不是数字”) 判断A1单元格内是否是数字IF(A1,”1234”,”5678”) 如果A1空白，则表示A1=0，即条件FALSE；A1为数字，则TURE，其他则不和规矩Sum(IF(A1=”销售一

3、部”,b1:b10,c1:c10), IF可以返回值；也可以返回区域；复合条件判断IF(AND(A1<>1，A2<>3),”1111”,”222”)AND函数，中间条件要同时满足，可以用OR函数IF函数嵌套层数有限制(12) VLOOKUP(查找值，查找范围，返回范围的序列号，精确匹配和模糊匹配的标志)缺省为模糊匹配，精确匹配可以写一个大于零的正数.TURE为近似匹配，FALSE为精确匹配。0为精确匹配(13) IS函数ISODD(A1) 是否为奇数ISNUMBER(A1)ISTEXT(A1)ISERROR(A1)判断是否为错误值。错误值：#N/A，#DIV/0!ISN

4、A(A1),只针对#N/A错误码(14) 避免#N/A类错误显示，如果是#N/A，显示为空,找不到数据时显示的 IF(ISERROR(VLOOKUP(“A”,B1：B10,2,0),”,VLOOKUP(“A”,B1：B10,2,0)(15) type函数 (16) cell函数(17) count() counta() count(A1:B100), 返回对应区域内包含数字数据的单元格的个数，空格和字符型的不计数counta（A1：B100）,返回对应区域非“真空”单元格的个数，空单元格不计数区域可以跨工作表：counta(工作表1：工作表10！A:A),返回这些工作表A列非空单元格数量之和计

5、算某列中特定值的单元格的个数 =count(1/(A1:A10=”A”)从A1到A10，如果A1=”A”，则1/(A1：A10=”A”)=1/1=1 ,相等则为ture即1，将等于1的计数(18) sum()函数不连续区域求和：=sum(a1:a10,c7:c8,f10:f200),最多可以30个区域超过30个区域，可以采用定义:选择区域，点右键，定义名称，设多个区域定义成了三个名称AA、BB、CC,则求和=sum(AA)+SUM(BB)+SUM(CC)选定制定区域内为空的单元格：用鼠标选定区域，ctrl+g,弹出“定位条件”的窗口(19) countif()函数=COUNTIF(F2:F13

6、,"<" & A21)=COUNTIF(F2:F13,"<" & AVERAGE(F2:F13)， &为字符串连接符=COUNTIF(D2:D13,"*空调*") 通配符*，匹配多个字符，含空调2个字的单元格的数量=COUNTIF(C2:C13,"?") 通配符?,匹配一个字符=COUNTIF(G2:G13,"<20000")-COUNTIF(G2:G13,"<=10000")(20) 限制某列不允许有重复值选中某列如M列；选中

7、部分高亮反白单元格为选中区域的“活动单元格”；输入的数据要满足自定义公式，自定义公式为：countif(M:M,M1)=1，表示在M列中与“活动单元格内容”相同的单元格的个数为1，满足为1，则合法，否则非法。M1实际是M1：M9999等 (21) 限制某些单元格必须输入含有”AA”字符的数据O1是“活动单元格”，在定义的区域范围内，随着输入位置的不同，计数的单元格是不同的(22) 删除重复数据，下面非1的需要删除(23) Sumif(),横向隔列求和=SUMIF(A2:A8,B11,C2:C8) 判断区域A2:A8，判断条件B11，求和区域C2：C8，判断区域和求和区域必须一样大=SUMIF(

8、C2:C8,">=2000") 判断区域和求和区域相同，则可省略求和区域参数=SUMIF(B18:F24,B27,C18:G24) 求和区域自动延后一个，判断区域和求和区域都可以为矩形=SUM(SUMIF(B31:B35,"A","C",C31:C35) sumif按照判断条件”A”计算一个值，按照”B”计算一个值，由SUM合并起来=SUMIF(A:A,"<" & DATE(2012,H2+1,1),B:B)-SUMIF(A:A,"<" & DATE(2012,

9、H2,1),B:B)A:A是日期型，DATE(2012，3，2)就表示日期2012年3月2日。上述是分月求和隔列求和，求和区域自动对应条件判断区域(24) sumproduct(),多条件求和和计数=SUMPRODUCT(B2:B7,C2:C7)=SUMPRODUCT(B2:B7*C2:C7) 这两个公式都是B2*C2+B3*C3+.=SUMPRODUCT(MONTH(A3:A9)=3)*(B3:B9="A") month（）返回对应单元格内容的月份数，(MONTH(A3:A9)=3)等于3时返回TURE，否则返回FALSE，TURE就是1，FALSE是0，上述公式就是SU

10、M(A*B),但A须为3月，B须为A，由于是逻辑运算，实际求的就是3月份并且A的个数，是计数=SUMPRODUCT(MONTH(A3:A9)=3)*(B3:B9="A")*C3:C9)，则是在上述基础上的求和，满足上述条件的C列的和计数 =SUMPRODCUT(条件1)*(条件2)*(条件n)求和 =SUMPRODCUT(条件1)*(条件2)*(条件n)*数据区域)=SUMPRODUCT(MONTH($A$2:$A$17)=G$1)*ISNUMBER(FIND($E4,$B$2:$B$17)*$C$2:$C$17)FIND(find_text,within_text,sta

11、rt_num)FINDB(find_text,within_text,start_num)Find_text 是要查找的文本。Within_text 是包含要查找文本的文本。Start_num 指定开始进行查找的字符。within_text 中的首字符是编号为 1 的字符。如果忽略 start_num，则假设其为 1。(25) sumtotal() 分类汇总函数=SUBTOTAL(104,B2:B6) 第一个参数是function_num;后面的参数是范围，不同的Function_num,对单元格的范围采取不同的分类汇总方式。104为范围内的最大值（隐藏的单元格不参与运算）(26) 两个日期之

12、间天数=DATEDIF("1999/8/31","2012/8/31","D")；”Y”,年，”M”月，”D”日(27) 饼图中隐藏0数据项Excel自定义数字格式代码分为四个部分，分别指定不同数值的格式，中间用“;”号分隔，具体如下：  正数格式;负数格式;零格式;文本格式在本例中，因我们只希望出现正数的数值，故可以指定格式为：0%;  。 格式 ; 还可隐藏所有值。(28) 消除提示：“此工作簿包含到其他数据源的链接”在“数据”“编辑链接”中断开链接即可(29) Excel中循环引用的检查在

13、“公式”“错误检查”“循环引用”中可以找到循环引用的单元格进行修改(30) 透视表中子类和父类位于不同的列中得到下图右面的透视表，可以在主分类字段上设置“不分类汇总”、“以表格方式显示项目标签实现”(31) 数据有效性控制：跨表引用参考设置受控单元格的有效条件如下，则对应单元格只能输入“工作大类”表中A列的数据项(32) EXCEL表格奇偶行显示不同的底色，使用条件格式(33) 为特定的单元格内容设置特定的底色：条件格式，功能很强大(34) 转置，行列变换，如下左边的表格变成右边的表格 方法1：选择复制；选择性粘贴，转置；方法2：=tranpose(A1:F5) Ctrl+Shift+Enter,但是需要选中目标区域,过大有错误信息，过小只能转置一部分(35) 矩阵乘积函数MMULT(ARRAY1，ARRAY2)MMULT(array1, array2), 返回两个数组的矩阵乘积。结果矩阵的行数与 array1 的行数相同，矩阵的列数与 array2 的列数相同.第一个数组的列数必须和第二个数组的行数相同C(m,k)=A(m,n)*B(n,k)(36) 最值函数Max（）;Min（）Larg