EXCEL日常使用经验积累Word文件下载.docx
- 文档编号:21930578
- 上传时间:2023-02-01
- 格式:DOCX
- 页数:12
- 大小:694.44KB
EXCEL日常使用经验积累Word文件下载.docx
《EXCEL日常使用经验积累Word文件下载.docx》由会员分享,可在线阅读,更多相关《EXCEL日常使用经验积累Word文件下载.docx(12页珍藏版)》请在冰豆网上搜索。
(11)判断单元格是否为空白,IF(B2=””,”空白”,”非空白”),如果有“空格”也不是“空白”,有空格不能用””判断
IF(Isnumber(A1),”是数字”,”不是数字”)判断A1单元格内是否是数字
IF(A1,”1234”,”5678”)如果A1空白,则表示A1=0,即条件FALSE;
A1为数字,则TURE,其他则不和规矩
Sum(IF(A1=”销售一部”,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!
ISNA(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列非空单元格数量之和
计算某列中特定值的单元格的个数{=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,"
<
"
&
A21)
AVERAGE(F2:
F13)),&
为字符串连接符
=COUNTIF(D2:
D13,"
*空调*"
)通配符*,匹配多个字符,含空调2个字的单元格的数量
=COUNTIF(C2:
C13,"
?
)通配符?
匹配一个字符
=COUNTIF(G2:
G13,"
20000"
)-COUNTIF(G2:
=10000"
)
(20)限制某列不允许有重复值
选中某列如M列;
选中部分高亮反白单元格为选中区域的“活动单元格”;
输入的数据要满足自定义公式,自定义公式为:
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(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:
DATE(2012,H2,1),B:
B)
A是日期型,DATE(2012,3,2)就表示日期2012年3月2日。
上述是分月求和
隔列求和,求和区域自动对应条件判断区域
(24)sumproduct(),多条件求和和计数
=SUMPRODUCT(B2:
B7,C2:
C7)
B7*C2:
C7)这两个公式都是B2*C2+B3*C3+…….
=SUMPRODUCT((MONTH(A3:
A9)=3)*(B3:
B9="
))month()返回对应单元格内容的月份数,
(MONTH(A3:
A9)=3)等于3时返回TURE,否则返回FALSE,TURE就是1,FALSE是0,上述公式就是SUM(A*B),但A须为3月,B须为A,由于是逻辑运算,实际求的就是3月份并且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,start_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)两个日期之间天数
=DATEDIF("
1999/8/31"
2012/8/31"
D"
);
”Y”,年,”M”月,”D”日
(27)饼图中隐藏0数据项
Excel自定义数字格式代码分为四个部分,分别指定不同数值的格式,中间用“;
”号分隔,具体如下:
正数格式;
负数格式;
零格式;
文本格式
在本例中,因我们只希望出现正数的数值,故可以指定格式为:
0%;
;
。
格式;
还可隐藏所有值。
(28)消除提示:
“此工作簿包含到其他数据源的链接”
在“数据”—》“编辑链接”中断开链接即可
(29)Excel中循环引用的检查
在“公式”—》“错误检查”—》“循环引用”中可以找到循环引用的单元格进行修改
(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()
Large(范围,n),范围内的数据从大到小排列,第n个数据
Small(范围,n),范围内的数据从小到大排列,第n个数据
(37)选中公式的一部分,F9可以显示公式的结果
(38)从工作表中筛选出部分数据放在另一个工作表中。
下表A-C列有部分“姓名”重复,“姓名”重复部分取第一条记录,形成一张没有重复“姓名”的表。
思路:
将第一次出现的姓名标志为1,后续再次出现标志依次加1,这样将标志为1的筛选出来放到另一个工作表中即可
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 日常 使用 经验 积累