学习园地
1.快速复制表格
直接拖动表格,可以移动到另一个位置,如果按ctrl不松拖动表格,可以快速复制出一个表格。
2.快速翻看工作表
按Ctrl+Pgdn/Pgup可以快速翻看工作表。不用一个个点了。
3.快速输入序号/快速调整列宽
做表时输入序号是必不可少的,但想更快,记住一个规则:
按Ctrl键不松可以让纯数字自动递增,也可以让含文本+数字中的数字不变。调整列宽有个小技巧:双击边线可以让列宽自适调整.
4.不复制隐藏的行
按Alt+;选取显示的行,复制-粘贴
5.两列快速互换
左手按shift键不松,右手按鼠标左键不松拖动列边线,可以快速让2列换位。
6.一键显示所有表格中公式
按Ctrl+~(波浪线)可以切换公式的显示或隐藏,但,这个快捷键常被输入法给占用。所以点击公式选项中的“显示公式”命令最靠谱。
7.快速选取单元格区域
快速选取区域的技巧:按Ctrl+A选取整个连续的表格;按Ctrl+shift+方向键可以选取连续的区域
8.快速复制公式
如果相邻列是连续的区域,双击公式所在单元格的右下角。如果邻列不连续,可以选取后按Ctrl+D复制。
Vlookup常用公式
1.模糊查找
查找包含AAA名称的产品价格
=VLOOKUP("*"&A10&"*",A2:B6,2,0)
2.多项查找
同时查找“李飞”和“南宁”的年龄 ,并返回最小的。
=MIN(VLOOKUP(T(IF(1,{"李飞","南宁"})),A2:C6,3,0))
3.隔任意列求和
隔1列求和
数组公式:大括号是按ctrl+shift+enter后自动产生的,非手工输入{=SUM(VLOOKUP(A2,A2:K2,ROW(1:6)*2-1,0))}
指定列求和
指定对2,3,5,6,8列求和公式:
{=SUM(VLOOKUP(A2,A2:K2,{2,3,5,6,8,9},0))}
注:{2,3,5,6,8,9}把要求和的列数放在大括号内,用逗号分隔。
SUM函数
1.设置求和公式
传统做法是设置一个SUM公式,然后复制复制。其实只需要按CTRL+G定位空单元格,然后点一下∑(或按住“ALT”和“=”)
2.设置总计公式
在设置总计公式时,不需要用小计1+小2+小3...了,只需要设置公式:
=SUM(B2:B14)/2
3.多表求和
例:合计从1日到31日的所有B2单元格之"和。
=SUM(1日:31日!B2)
4.多条件求和
虽然不是SUM函数的强项,但可以给一个小攻是
{=SUM((a1:a100=“A产品”)b1:b100)}
5.合并单元格求和
合计单元格的求和是一个难点,如果用一般的方法公式很复杂,如果用SUM函数的倒减法,就会变简单
=SUM(C2:C13)-SUM(D3:D13)
在输入公式时不能拖动复制,需要选取D2:D13,先在D3输入公式,然后把光标放在公式结尾处,按ctrl+回车键批量输入
6.任意多表求和
如果N个不相邻的表的同一个单元格求和,不能直接用SUM,需要用SUM(SUM(结构,这里等同与SUM(N,提取三维引用的单元格求和)
=SUM(SUM(INDIRECT({1,2,3}&“!A1”)))
=SUM(N(INDIRECT({1,2,3}&“!A1”)))
Countif函数
精确统计
1)返回A列包含值12的单元格数量
=COUNTIF (A:A,12)
2)返回A列部门为“采购部”的数量
=COUNTIF(A:A,“采购部”)
字符在公式中需要加双引号
3)返回包含值;逻辑值为TRUE的单元格数量
=COUNTIF(A:A,TRUE)
4)返回A1:A10空单元格的数量
=COUNTIF(A1:A10,“=”)
5)返回A列非空单元格的个数(相当于counta函数)
=COUNTIF(A1:A10,“<>”)
6)返回A1:A10区域,真空+假空的数量
=COUNTIF(A1:A10,“”)
7)返回A1:A10区域所有单元格的数量(非空+空值)
=COUNTIF(data,“<>“””)
8)返回A1:A10假空单元格数量
=COUNTIF(A2:A32,“”)-COUNTIF(A2:A32,“=”)
真空+假空-真空=假空
9)统计A列身份证号410105198504182965出现的数量
=COUNTIF(A:A,“410105498504182965*”)
默认情况下,countif函数对数字只识别前15位,而带上通配符*后可以识别其为文本型格式。