Excel学习笔记
写在前面的话：关于Excel的函数还有很多，在此由于能力和针对性就不一一列举，平时常用的那些简单的函数如IF和SUM等在本文中便没有收入，本文收入的也仅仅是本人在学习Excel过程中记录的一些比较有用且有点复杂功能的函数，我们在平常生活和学习的过程中也应该去丰富和多记录，以方便自己在需要用的时候能够及时解决问题。衷心希望本文能够为您带来帮助。
Excel基础知识：单元格与单元格之间用“：”隔开表示取单元格与单元格之间的区域。$B$2这种符号表示锁定(F4)，即当下拉单元格时候该B2不变。字符用""英文双引号，数据后面需要跟文字或者符号用&，如20%--20&"%"。字符串操作函数：right/left--选取左/右多少位数。
数组公式，按Ctrl+Shift+Enter结束公式输入。
1在Excel中函数Vlookup和Hlookup的应用
语法：vlookup(lookup_value,table_array,col_index_num,[lookup_range])。
lookup_value指需要在table_array的表格范围内查的值
table_array 查的范围
col_index_num是列的【序号】，不是【列号】通俗地讲，就是在Table_Array这个区域中，【第几列】的意思。
你选中的B:C列，其中xxx在C列，那么C列在B:C这个范围中是第2列，col_index_num处填写2即可。
lookup_range是精确匹配或近似匹配（TRUE,FALSE）(0,1)
语法2：Hlookup(lookup_value,table_array,row_index_num,[lookup_range])。
具体解释和vlookup基本一样，只有一点，vlookup是在查范围的首列进行查，而Hlookup是在查范围的首行进行查，而参数row_index_num也是现实的是查范围内的某一行的匹配列的值。
注：vlookup和Hlookup不是匹配在查范围内所有的值，只是匹配的查范围内的首列或者首行。其次他们的参数中col_index_num和row_index_num一定要明白这个值的含义，它是指显示在查范围内第几列（行）的值。
2、在你的公式中加入错误判断函数，以防止结果出现#VALUE和#N/A
举例如下：
#VALUE和#DIV/0!
=IF(ISERROR(原来的公式),"",原来的公式)
也可以直接判断原来两个单元任意一个为空白时，返回空白：
（假设A1、B1为计算数值，C1为结果）
=IF(SERROR(A1="",B1=""),"",A1*B1)
#N/A
IF(ISNA(值),0,1)
3、统计区域重复个数的函数：
  COUNTIF($B$2:$B$52,B8)-----（统计区域，统计对象）
  COUNTIF($B$2:$E$52,B8)-----（统计区域，统计对象）
    注：注意理解区域，条件(统计对象)的形式可以是数字、表达式或文本，甚至可以使用通配符。
列：=SUM(COUNTIF(A2:A13,{"上海发货平台","成都发货平台"})) 统计A2到A13中上海发货平台和成都发货平台的个数
列：=COUNTIF(A2:A13,”上海*”)统计A2到A13中带有上海这两个字符的行数
COUNTIFS (criteria_range1,criteria1,criteria_range2,criteria2,…)　多条件计数函数
语法：countifs（第一个条件区，第一个对应的条件，第二个条件区，第二个对应的条件，第N个条件区，第N个条件对应的条件）
列：=COUNTIFS(A:A,"="&A2,B:B,"="&B2,C:C,"="&C2,D:D,"="&D2,E:E,"<="&E2) 统计A列中=A2并且B列中等于B2并且C列中等于C2并且D列中=D2并且E列中小于等于E2的行数
注：此函数也可以用Sumproduct代替
4、函数Index的应用
语法：INDEX(default!$B$2:default!$B$799,INT(RAND()*799+1),1)
（范围，行号，列号）
其中Rand是随机函数（使用该函数后可获得一个大于零且小于1的一个随机值），Int（）将值转换为小于该数的整数。
作用：把B列的数据随机取XX个
5、跨工作薄引用的简单表达式是：'盘符:\[工作薄名称.xls]表名1'!数据区域
  比如 'D:\[成绩表.xls]Sheet1'!A2:A7　
  如果是相对路径，还可以这样写：'[成绩表.xls]Sheet1'!A2:A7
6、SUMIF 单条件求和函数：列--求数学成绩大于（包含等于）80分的同学的总分之和
            =SUMIF(C2:C22,">=80",I2:I22)
excel计算字符串长度    SUMIF(C2:C22,">=80",I2:I22)中的C2:C22表示条件数据列，">=80"表示筛选的条件是大于等于80（如果是某一值或者某一单元格则可以直接用单元格，不需要=号），那么最后面的I2：I22就是我们要求的总分之和的范围
7、SUMIFS多条件求和函数：列--求数学与英语同时大于等于80分的同学的总分之和
            =SUMIFS(I2:I22,C2:C22,">=80",D2:D22,">=80")
该函数SUMIFS(I2:I22,C2:C22,">=80",D2:D22,">=80")表示的意思是，I2：I22是求和列（总分），C2： C22表示数学列，D2：D22表示英语列，两者后面的">=80"都表示是大于等于80
注：sumif和sumifs函数中的数据列和条件列是相反的，这点非常重要，千万不要记错咯
8、保留小数函数round(数据单元格,要保留位数) 四舍五入法。
9、多行文本数据的合并，使用PHONETIC函数。
输入以下公式: =PHONETIC(A2:A4) 
公式表示：将A2:A4单元格的文本连接起来。
不同区域的多行合并：=PHONETIC(A2:A4,A10:B20,……)
公式表示：将A2到A4和A10到B20区域内的文本合并。
10、行转列/列转行：在工作表选中要转换的行，并复制，再将光标定位到其它的某个单元格。依次点击菜单栏中的“编辑-->选择性粘贴”，在打开的 “选择性粘贴”对话框中，勾选“转置”项，并确定。
11、当前单元格所在列序号：=COLUMN（）
    当前单元格所在行序号：=row()
12、OFFSET(reference,rows,cols,height,width)
    reference：参考系(可以是单元格也可以是区域)。rows：以参考系为焦点上下移动rows行（负    数为向上移动）。cols：以参考系为焦点左右移动cols列（负数为向左移动）。height,width：    表示引用的区域大小。
用法：可以将行转换成指定行数的列：如=OFFSET($A$1,,ROW(A1)*3+COLUMN(A1)-4)把一行每三个单元格的数据转换成一列
13、rept函数的语法格式：=REPT（text，number_times)。
参数text表示——重复出现的文本
参数number_times表示——指定文本重复出现的次数
函数表示：一次性输入多个重复的相同符号（一般用于显示百分比---一般符号用"|"）
14、将一行有规律的数据截取成多列：
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(PHONETIC(B$2:B$100),",",""),"c",REPT(" ",LEN(SUBSTITUTE(PHONETIC(B$2:B$100),",","")))&"c"),LEN(SUBSTITUTE(PHONETIC(B$2:B$100),",",""))*ROW(A1)+1,LEN(SUBSTITUTE(PHONETIC(B$2:B$100),",",""))))
解析：将区域(b2:b100)中的所有文本即“c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,……c100”截取成1列。
15、sumproduct  （这是一个很强大的函数）
函数的适用范围，在给定的几组数组中，然后把数组间对应的元素相乘，最后返回乘积之和。
函数的语法格式：=SUMPRODUCT（array1,array2,array3, ...）Array为数组。
    1）基础用法：当sumproduct函数中的参数只有一个数组时，即对数组｛1;2;3;4;5;6;7｝进行求和，1+2+3+4+5+6+7=28，如sumproduct（A1：A7）
当函数中参数为两个数组时，两个数组的所有元素对应相乘。（注意对应相乘）
公式=sumproduct（A1:A7,B1:B7）可转化为
    =sumproduct（数组1，数组2）
    =sumproduct（｛1；2；3；4；5；6；7｝，｛1；2；3；4；5；6；7｝）=1*1+2*2+3*3+4*4+5*5+6*6+7*7=140。
当函数中参数为三个数组时，三个数组的所有元素对应相乘。依次类推……。
变换应用:
    2）多条件求和：
单条件（类似sumif） =sumproduct((A2:A13="条件")*(B2:B13)) 解析---看到这公式你可能有疑惑，它跟语法格式好像不一样，其实把它看做是只有一个参数。因为当函数中出现由TRUE和FALSE组成的逻辑数组时，这时公式要写成这种格式=sumproduct((A1:A7=A1)*1，(B1:B7))，乘以1，把它转化成数组才能参与运算。公式分解
=sumproduct（｛数组1｝*｛数组2｝）
=sumproduct（｛TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE｝*｛1;2;3;4;5;6;7｝）
=1*1+0*2+0*3+0*4+0*5+0*6+0*7=1

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。