excel培训内容【excel培训内容课程问答】 |
您所在的位置:网站首页 › excel培训内容知识点总结 › excel培训内容【excel培训内容课程问答】 |
1.快速复制表格 直接拖动表格,可以移动到另一个位置,如果按ctrl不松拖动表格,可以快速复制出一个表格。 2.快速翻看工作表 按Ctrl+Pgdn/Pgup可以快速翻看工作表。不用一个个点了。 3.快速输入序号/快速调整列宽 做表时输入序号是必不可少的,但想更快,记住一个规则:按Ctrl键不松可以让纯数字自动递增,也可以让含文本+数字中的数字不变。调整列宽有个小技巧:双击边线可以让列宽自适调整 4.快速插入空列 需要插入几列就选取几列然后插入 5.不复制隐藏的行 按Alt+;选取显示的行,复制-粘贴 6.两列快速互换 左手按shift键不松,右手按鼠标左键不松拖动列边线,可以快速让2列换位。 7.一键显示所有表格中公式 按Ctrl+~(波浪线)可以切换公式的显示或隐藏,但,这个快捷键常被输入法给占用。所以点击公式选项中的“显示公式”命令最靠谱。 8.快速选取单元格区域 快速选取区域的技巧: 按Ctrl+A选取整个连续的表格 按Ctrl+shift+方向键可以选取连续的区域 9.快速复制公式 如果相邻列是连续的区域,双击公式所在单元格的右下角。如果邻列不连续,可以选取后按Ctrl+D复制。 Vlookup常用公式 1.一般查找 如下图所示,根据姓名查找职位 2.从左至右查找 如下图,要求根据姓名查找工号 3.查找公式复制 在表二中,根据姓名从表中查询对应信息 4.区间查找 根据左表的提成比率表,在右表中更具销售额在G列查找适用的比率 5.模糊查找 查找包含AAA名称的产品价格 =VLOOKUP("*"&A10&"*",A2:B6,2,0) 6.多项查找 从下表中同时查找“李飞”和“南宁”的年龄 ,并返回最小的。 =MIN(VLOOKUP(T(IF(1,{"李飞","南宁"})),A2:C6,3,0)) 7.隔任意列求和 隔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}把要求和的列数放在大括号内,用逗号分隔。 8、多条件查找 如下图所示要求,同时根据姓名和工号查找职位。 {=VLOOKUP(B9&C9,IF({1,0},B2:B6&A2:A6,E2:E6),2,0)} 9.多区域查找 根据不同的表从不同的区域查询 =VLOOKUP(B2,IF(A2="销售一部",A5:B9,D5:E9),2,0) 10.多工作表查找 从各部门中查找员工的基本工资,在哪一个表中不一定。 方法1 =IFERROR(VLOOKUP(A2,服务!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,综合!A:G,7,0),IFERROR(VLOOKUP(A2,财务!A:G,7,0),IFERROR(VLOOKUP(A2,销售!A:G,7,0),"无此人信息"))))) 方法2: =VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"销售";"服务";"人事";"综合";"财务"}&"!a:a"),A2),{"销售";"服务";"人事";"综合";"财务"})&"!a:g"),7,0) 11.一对多查找1 根据产品查找相对应的所有供应商 A2 =B2&COUNTIF(B$1:B2,B2) B11=IFERROR(VLOOKUP($A11&COLUMN(A1),$A:$C,3,0),"") 12.一对多查找2 查找张三的所有消费记录 数组公式,大括号是按ctrl+shift+enter后自动产生的,非手工输入 {=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,)} 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函数 如果评工作中最常用的函数是哪个,Vlookup函数是采购公认的NO.1函数,但它只能用于查找。在Excel中还有一个函数比它更有用,是Excel中最重要的一个函数。那就是Countif函数 1.精确统计 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位,而带上通配符*后可以识别其为文本型格式。 2.模糊计数 1)返回A列单元格包含“采购部”的数量(如:A公司采购部) =COUNTIF(A:A,“*采购部*”) 2)返回包含以单词“AB”(部分大小写)开头内容的单元格数量 =COUNTIF(A:A,“AB*”) 通配符*是表示任意多个字符,放在字符后面表示,该字符后可以有任意多个字符 3)返回A列单元格中5个字符的数量 =COUNTIF(A:A,“?????”) 通配符?表示占用一个字符位置,五个?表示五位字符长度 4)返回包含文本内容的单元格数量 =COUNTIF(A:A,“*”) 3.区间统计 1)返回包含负值的单元格数量 =COUNTIF(A:A,“ 2)返回不等于0的单元格数量 =COUNTIF(A:A,“0”) 3)返回大于5的单元格数量 =COUNTIF(A:A,“>5”) 4)返回大于单元格A1中内容的单元格数量 =COUNTIF(DATA,“>”&A1) 在引用单元格值时,一定要用&链接,千万不要表示为:“>A1” 5)返回>10并且 =SUM(COUNTIF(A1:A10,“>”&{10,15})*{1,-1}) {}表示常量数组,数组的值可以在公式内分别进行运算,每次运算返回一个值。*{1,-1}的目的是上前面的计算结果一个为正,一个为负,用sum求和时达到相减的目的。 6)返回>=10并且 =SUM(COUNTIF(A1:A10,{“>=10”,“>15”})*{1,-1}) 7)返回包含值为3或-3的单元格数量 =SUM(COUNTIF(A1:A10,{10,-3})) 4.引用函数结果统计 1)返回包含当前日期的单元格数量 =COUNTIF(A:A,TODAY()) 2)返回大于平均值的单元格数量 =COUNTIF(A:A,“>”&AVERAGE(A:A)) |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |