2022年比较常用的Excel技巧 .pdf
《2022年比较常用的Excel技巧 .pdf》由会员分享,可在线阅读,更多相关《2022年比较常用的Excel技巧 .pdf(16页珍藏版)》请在淘文阁 - 分享文档赚钱的网站上搜索。
1、比较常用的 Excel 技巧。欢迎大家补充哦1、两列数据查找相同值对应的位置=MATCH(B1,A:A,0) 2、已知公式得结果定义名称 =EVALUATE(Sheet1!C1) 已知结果得公式定义名称 =GET.CELL(6,Sheet1!C1) 3、强制换行用 Alt+Enter 4、超过 15 位数字输入这个问题问的人太多了,也收起来吧。一、单元格设置为文本;二、在输入数字前先输入 5、如果隐藏了B 列,如果让它显示出来? 选中 A 到 C 列,点击右键,取消隐藏选中 A 到 C 列,双击选中任一列宽线或改变任一列宽将鼠标移到到AC 列之间,等鼠标变为双竖线时拖动之。6、EXCEL 中行
2、列互换复制,选择性粘贴,选中转置,确定即可7、Excel 是怎么加密的(1)、保存时可以的另存为右上角的 工具 常规 设置(2)、工具 选项 安全性8、关于 COUNTIF COUNTIF 函数只能有一个条件,如大于90,为 =COUNTIF(A1:A10,=90) 介于 80 与 90 之间需用减,为=COUNTIF(A1:A10,80)-COUNTIF(A1:A10,90) 另外补充:条件:列中包含某字符A1- 含字符 EA 目的:赋予含该字符对应单元格相应公式或数值套用公式: =if(COUNT(search(EA,A1),公式或数值 1,公式或数值 2) =if(ISERR(searc
3、h(EA,A1),公式或数值2,公式或数值1) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页,共 16 页 - - - - - - - - - Count 因为 Search 得到的是代表位置的数字或者#Value 错误,Count 对数字可以统计, 对错误值得到0。Iserr 条件反转。Isnumber 当 A1 为数字时,则返回true 9、根据身份证号提取出生日期(1)、=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(
4、A1,13,2),IF(LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2),错误身份证号 ) (2)、=TEXT(MID(A2,7,6+(LEN(A2)=18)*2),#-00-00)*1 身份证 EXCEL 补充:A)提取生日(年 -月-日):=IF(LEN(A1)=15,TEXT(CONCATENATE(19,MID(A1,7,6),#-00-00),TEXT(MID(A1,7,8),#-00-00) B)提取年龄: =YEAR(TODAY()-IF(LEN(A1)=15,CONCATENATE(19,MID(A1,7,2),MID(A
5、1,7,4) C)提取性别: =IF(MOD(IF(LEN(A1)=15,MID(A1,15,1),MID(A1,17,1),2)=1,男,女) 10、想在 SHEET2 中完全引用SHEET1 输入的数据工作组,按住Shift 或 Ctrl 键,同时选定Sheet1 、Sheet2 。11、一列中不输入重复数字数据- 有效性 -自定义 -公式 输入 =COUNTIF(A:A,A1)=1 如果要查找重复输入的数字条件格式公式=COUNTIF(A:A,A5)1格式选红色12、直接打开一个电子表格文件的时候打不开“ 文件夹选项 ” -“ 文件类型 ” 中找到 .XLS 文件,并在 “ 高级 ” 中
6、确认是否有参数1%,如果没有,请手工加上13、excel 下拉菜单的实现数据-有效性 -序列 14、 10 列数据合计成一列=SUM(OFFSET($1,(ROW()-2)*10+1,10,1) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页,共 16 页 - - - - - - - - - 15、查找数据公式两个(基本查找函数为VLOOKUP,MATCH) (1)、根据符合行列两个条件查找对应结果=VLOOKUP(H1 ,A1:E7 ,MATCH(I1 ,A1:E1 ,0)
7、,FALSE) (2)、根据符合两列数据查找对应结果(为数组公式 ) =INDEX(C1 :C7,MATCH(H1&I1,A1:A7&B1:B7,0) 16、如何隐藏单元格中的0 单元格格式自定义0;-0; 或 选项视图零值去勾。呵呵,如果用公式就要看情况了。17、多个工作表的单元格合并计算=Sheet1!D4+Sheet2!D4+Sheet3!D4,更好的 =SUM(Sheet1:Sheet3!D4) 18、获得工作表名称(1)、定义名称: Name =GET.DOCUMENT(88) (2)、定义名称: Path =GET.DOCUMENT(2) (3)、在 A1 中输入 =CELL(fi
8、lename)得到路径级文件名在需要得到文件名的单元格输入=MID(A1,FIND(*,SUBSTITUTE(A1,*,LEN(A1)-LEN(SUBSTITUTE(A1,)+1,LEN(A1) (4)、自定义函数Public Function name() Dim filename As String filename = ActiveWorkbook.name name = filename End Function 19、A) 如何获取一个月的最大天数:=DAY(DATE(2002,3,1)-1)或=DAY(B1-1),B1为2001-03-01 B) 数据区包含某一字符的项的总和,该用
9、什么公式=sumif(a:a,*&某一字符 &*, 数据区 ) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页,共 16 页 - - - - - - - - - C) 最后一行为文本:=offset($b$1,MATCH(CHAR(65535),b:b)-1,) D) 最后一行为数字:=offset($b$1,MATCH(9.9999E+307,b:b)-1,) 或者: =lookup(2,1/(b1:b1000),b1:b1000) E) 评委打分中, 如何去掉两个以上最高
10、分,两个以上最底分, 求剩余人员的平均分?同时显示出被去掉的分数。看看 trimmean() 函数帮助。被去掉的分数:最大两个: =large(data,1;2) 最小两个: =small(data,1;2) F) 怎样很简单的判断最后一位是字母right(a1)*1 G) 出错的字母=IF(ISNUMBER(-RIGHT(A1,1),数字 ,字母 ) =IF(ISERR(RIGHT(A1)*1),字母 ,数字) H) 如何设置单元格,令其不接受包含空格的字符选定 A 列数据 有效性 自定义 公式=iserror(find( ,a1) 数据 -有效性 -自定义 -公式=len(a1)=len(
11、trim(a1) 注* 单元格消空格用函数=TRIM(A1) I) 实现跳格累加如=a1+a4+a7+a10+a13+a16+a19+a22. =sum(n(offset(a1,(row(1:10)-1)*3,) J) 在一个工作表中引用其他工作表中的数据,但是被引用的工作表不是固定的,根据输入的工作表名自动选择相应的工作表中的数据,请问在公式里怎样引用? =INDIRECT(A1&!&E1) A1为工作表名名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页,共 16 页 - -
12、 - - - - - - - K) 奇数行求和=SUMPRODUCT(A1:A1000)*MOD(ROW(A1:A1000),2) L) 偶数行求和=SUMPRODUCT(A1:A1000)*NOT(MOD(ROW(A1:A1000),2) M) 查看字符串字数=LEN(A1) N) 求非空单元格数量公式计算出来的数据,COUNTA 不能用的 (否则空字符也计算进去了) =COUNTIF($E$3E$65536,?*) O) 动态求和公式 ,自 A 列 A1 单元格到当前行前面一行的单元格求和. =SUM(INDIRECT(A1:A&ROW()-1) 20、比较好用的EXCEL 文档修复工具E
13、xcelRecovery 21、EXCEL 开方运算将 8 开 3 次方,可以用这个公式,在单元格中输入=8(1/3) 22、单元格中的数据分散对齐文本格式全角输入23、查找工作表中的链接Ctrl+ 或编辑链接24、如何让空单元格自动填为0 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页,共 16 页 - - - - - - - - - 选中需更改的区域查找空替换0 25、把 Word 里的数字转换到Excel 方法有多种,选中复制设置输入单元格为文本选择性粘贴值选中表格转换
14、为文本粘贴分列对分列选项设置为文本另存为文本文件EXCEL 中打开文本文件对导入文本对话框进行对应设置26、扩展你的命名域Excel 允许你定义命名的域,这样你的方程式就能够更具描述性,例如,你可以使用“=SUM (Bonus) ”来替换“=SUM(C2:C25) ”。尽管 Excel 的命名域通常都是静态的单元域,例如 “C2:C25 ” ,但你也可以在添加新数据的时候创建一个动态的命名域。要将一个域更名并让其变成动态,请注意工作簿的名称,以及第一个数据单元所在的地址,它需要不是一个列标题,例如 “$C$2 ” 。点击 “ 插入 名称 定义” ,在 “ 在当前工作簿的名称” 中输入工作簿名。
15、在“ 引用位置 ”中,输入 “=OFFSET(Bonus!$C$2,0,0,COUNTA(Bonus!$C:$C) ”,并注意其中不能有空格,接着按下回车键来创建命名域。这个方程式就能够定义你的命名域,计算列中的数值,并能够按单元格的数字延伸这个域。27、单双行简单选在 A 列前插入一空列,A1 中输入 1,A2 中输入 2,选中 A1 A2,将光标移到A2 右下角,当光标变成实心十字时,按住ctrl 见往下拖动到想要的位置,再按这列排序,就把单双行分开了28、如何在已有的单元格中批量加入一段固定字符?例如:在单位的人事资料,在excel 中输入后,由于上级要求在原来的职称证书的号码全部再加两
16、位,即要在每个人的证书号码前再添上两位数13,如果一个一个改的话实在太麻烦了,那么我们可以用下面的办法,省时又省力:1)假设证书号在A 列,在 A 列后点击鼠标右键,插入一列,为B 列 ;2)在 B2 单元格写入:=13 & A2 后回车;名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页,共 16 页 - - - - - - - - - 3)看到结果为13xxxxxxxxxxxxx 了吗?鼠标放到B2 位置,单元格的下方不是有一个小方点吗,按着鼠标左键往下拖动直到结束。当你放开
17、鼠标左键时就全部都改好了。若是在原证书号后面加13 则在B2 单元格中写入: =A2 & “13” 后回车。29、如何设置文件下拉窗口的最下面的最近运行的文件名个数?打开 “ 工具 ” ,选 “ 选项 ” ,再选 “ 常规 ” ,在“ 最近使用的文件清单” 下面的文件个数输入框中改变文件数目即可。若不在菜单中显示最近使用的文件名,则将“ 最近使用的文件清单” 前的复选框去掉即可。30、在 EXCEL 中输入如 “ -” 、“ -” 之类的格式后它即变成月日,月日等日期形式,怎么办?这是由于 EXCEL 自动识别为日期格式所造成,你只要点击主菜单的“ 格式 ” 菜单,选 “ 单元格 ” ,再在“
18、 数字” 菜单标签下把该单元格的格式设成文本格式就行了。31、在 EXCEL 中如何使它象WORD 一样的自动定时保存文件?点击 “ 工具 ” 菜单 “ 自动保存 ” 项,设置自动保存文件夹的间隔时间。如果在“ 工具 ” 菜单下没有 “ 自动保存 ”菜单项,那么执行“ 工具 ” 菜单下 “ 加载宏 . ”选上 “ 自动保存 ” ,“ 确定 ” 。然后进行设置即可。32、用 Excel 做多页的表格时,怎样像Word 的表格那样做一个标题,即每页的第一行(或几行)是一样的。但是不是用页眉来完成?在 EXCEL 的文件菜单页面设置工作表打印标题;可进行顶端或左端标题设置,通过按下折叠对话框按钮后,
19、用鼠标划定范围即可。这样 Excel 就会自动在各页上加上你划定的部分作为表头。33、在 Excel 中如何设置加权平均?加权平均在财务核算和统计工作中经常用到,并不是一项很复杂的计算,关键是要理解加权平均值其实就是总量值(如金额)除以总数量得出的单位平均值,而不是简单的将各个单位值(如单价)平均后得到的那个单位值。在Excel 中可设置公式解决(其实就是一个除法算式),分母是各个量值之和,分子是相应的各个数量之和,它的结果就是这些量值的加权平均值。34、如果在一个Excel 文件中含有多个工作表,如何将多个工作表一次设置成同样的页眉和页脚?如何才能一次打印多个工作表?把鼠标移到工作表的名称处
20、(若你没有特别设置的话,Excel 自动设置的名称是“sheet1 、sheet2 、sheet3.”),然后点右键,在弹出的菜单中选择“ 选择全部工作表 ” 的菜单项,这时你的所有操作都是针对全部工作表了,不管是设置页眉和页脚还是打印你工作表。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页,共 16 页 - - - - - - - - - 35、EXCEL 中有序号一栏,由于对表格进行调整,序号全乱了,可要是手动一个一个改序号实在太慢太麻烦,用什么方法可以快速解决?如果序号
21、是不应随着表格其他内容的调整而发生变化的话,那么在制作EXCEL 表格时就应将序号这一字段与其他字段分开,如在“ 总分 ” 与“ 排名 ” 之间空开一列,为了不影响显示美观,可将这一空的列字段设为隐藏,这样在调整表格(数据清单)的内容时就不会影响序号了。36、用 Excel2000 做成的工资表,只有第一个人有工资条的条头(如编号、姓名、岗位工资.) ,想输出成工资条的形式。怎么做?这个问题应该这样解决:先复制一张工资表,然后在页面设置中选中工作表选项,设置打印工作表行标题,选好工资条的条头,然后在每一个人之间插入行分页符,再把页长设置成工资条的高度即可。使用自定义方式重装了一遍中文offic
22、e97 , xcel 的打印纸选项中只有A4 一种,怎么办?随便安装一个打印机驱动程序就可以了。37、在 xcel 中小数点无法输入,按小数点,显示的却是逗号,无论怎样设置选项都无济于事,该怎么办?这是一个比较特殊的问题,我曾为此花了十几个小时的时间,但说白了很简单。在indows 的控制面板中,点击 “ 区域设置 ” 图标,在弹出的 “ 区域设置属性 ” 对话面板上在 “ 区域设置 ” 里选择 “ 中文(中国) ” ,在“ 区域设置属性 ” 对话面板上在 “ 数字 ” 属性里把小数点改为“.”(未改前是 “ ,” ),按“ 确定 ” 按钮结束。这样再打开 Excel 就一切都正常了。38、如
23、何快速选取特定区域?使用 F5 键可以快速选取特定区域。例如,要选取 A2:A1000 ,最简便的方法是按F5 键,出现 “ 定位 ”窗口,在 “ 引用 ” 栏内输入需选取的区域A2:A1000 。39、如何快速返回选中区域?按 Ctr+BacksPae (即退格键)。40、如何快速定位到单元格?方法一:按 F5 键,出现 “ 定位 ” 对话框,在引用栏中输入欲跳到的单元格地址,单市“ 确定 ” 按钮即可。方法二:单击编辑栏左侧单元格地址框,输入单元格地址即可。41、“Ctrl *” 的特殊功用一般来说,当处理一个工作表中有很多数据的表格时,通过选定表格中某个单元格,然后按下Ctrl名师资料总
24、结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页,共 16 页 - - - - - - - - - * 键可选定整个表格。Ctfl* 选定的区域是这样决定的:根据选定单元格向四周辐射所涉及到的有数据单元格的最大区域。42如何快速选取工作表中所有包含公式的单元格?有时, 需要对工作表中所有包含公式的单元格加以保护,或填入与其他单元格不同的颜色,以提醒用户注意不能在有此颜色的区域内输入数据。以下方法可以帮助快速选取所有包含公式的单元格:选择 “ 编辑 ”“ 定位 ” ,单击 “ 定位条件 ”
25、 按钮,在 “ 定位条件 ” 对话框中选择 “ 公式 ” 项,按 “ 确定 ” 按钮即可。43、如何在不同单元格中快速输入同一数内容?选定单元格区域,输入值,然后按Ctrl Ener 键,即可实现在选定的单元格区域中一次性输入相同的值。44、只记得函数的名称,但记不清函数的参数了,怎么办?如果你知道所要使用函数的名字,但又记不清它的所有参数格式,那么可以用键盘快捷键把参数粘贴到编辑栏内。具体方法是:在编辑栏中输入一个等号其后接函数名,然后按Ctr A 键,Excel 则自动进入 “ 函数指南 步骤 2 之 2” 。当使用易于记忆的名字且具有很长一串参数的函数时,上述方法显得特别有用。45、如何
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 2022年比较常用的Excel技巧 2022 比较 常用 Excel 技巧
限制150内