《Excel技巧的整理、讲解》由会员分享，可在线阅读，更多相关《Excel技巧的整理、讲解（94页珍藏版）》请在金锄头文库上搜索。
1、常用办公软件 Excel 技巧的整理、讲解，在这里给读者们看一看，给大家一些提示，希望在你在平时能用得上。 1、两列数据查找相同值对应的位置=MATCH（B1，A：A，0）2、已知公式得结果定义名称=EVALUATE（Sheet1！C1）已知结果得公式定义名称=GET.CELL（6，Sheet1！C1）3、强制换行用 Alt+Enter4、超过 15 位数字输入这个问题问的人太多了，也收起来吧。一、单元格设置为文本；二、在输入数字前先输入5、如果隐藏了 B 列，如果让它显示出来？选中 A 到 C 列，点击右键，取消隐藏选中 A 到 C 列，双击选中任一列宽线或改变任一列宽将鼠标移到到 AC 列
2、之间，等鼠标变为双竖线时拖动之。6、EXCEL 中行列互换复制，选择性粘贴，选中转置，确定即可7、Excel 是怎么加密的（1）、保存时可以的另存为右上角的工具常规设置（2）、工具选项安全性8、关于 COUNTIFCOUNTIF 函数只能有一个条件，如大于 90，为=COUNTIF（A1：A10，=90）介于 80 与 90 之间需用减，为 =COUNTIF（A1：A10，80）-COUNTIF（A1：A10，90）9、根据身份证号提取出生日期（1）、=IF（LEN（A1）=18，DATE（MID（A1，7，4），MID（A1，11，2），MID（A1，13，2），IF（LEN（A1）=15，
3、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）*110、想在 SHEET2 中完全引用 SHEET1 输入的数据工作组，按住 Shift 或 Ctrl 键，同时选定 Sheet1、Sheet2。11、一列中不输入重复数字数据-有效性-自定义-公式输入=COUNTIF（A：A，A1）=1如果要查找重复输入的数字条件格式公式=COUNTIF（A：A，A5）1格式选红色12、直接打开一个电子表格文件的时候打不开“文件夹选项”-“文件类型”中找到.XLS 文件，
4、并在“高级”中确认是否有参数 1%，如果没有，请手工加上13、Excel 下拉菜单的实现数据-有效性-序列14、 10 列数据合计成一列=SUM（OFFSET（$A，（ROW（）-2）*10+1，10，1）15、查找数据公式两个（基本查找函数为 VLOOKUP，MATCH）（1）、根据符合行列两个条件查找对应结果=VLOOKUP（H1，A1：E7，MATCH（I1，A1：E1，0），FALSE）（2）、根据符合两列数据查找对应结果（为数组公式）=INDEX（C1：C7，MATCH（H1&I1，A1：A7&B1：B7，0）16、如何隐藏单元格中的 0单元格格式自定义 0；-0； 或 选项视图零值
5、去勾。呵呵，如果用公式就要看情况了。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（filename）得到路径级文件名在需要得到文件名的单元格输入=MID（A1，FIND（*，SUBSTITUTE（A1，*，LEN（A1）-LEN（SUBSTITUTE（A1，）+1，LEN（A1）（4）、自定义函数Public Function
6、 name（）Dim filename As Stringfilename = ActiveWorkbook.namename = filenameEnd Function19、如何获取一个月的最大天数：=DAY（DATE（2002，3，1）-1）或=DAY（B1-1），B1 为2001-03-01数据区包含某一字符的项的总和，该用什么公式=sumif（a：a，*&某一字符&*，数据区）最后一行为文本：=offset（$b，MATCH（CHAR（65535），b：b）-1，）最后一行为数字：=offset（$b，MATCH（9.9999E+307，b：b）-1，）或者：=lookup（2，1/
7、（b1：b100030,B20，在 EXCEL 中可以省略60,合格,不合格)语法解释为，如果单元格 B11 的值大于 60，则执行第二个参数即在单元格 B12 中显示合格字样，否则执行第三个参数即在单元格 B12 中显示不合格字样。在综合评定栏中可以看到由于 C 列的同学各科平均分为 54 分，综合评定为不合格。其余均为合格。3、 多层嵌套函数的应用在上述的例子中，我们只是将成绩简单区分为合格与不合格，在实际应用中，成绩通常是有多个等级的，比如优、良、中、及格、不及格等。有办法一次性区分吗？可以使用多层嵌套的办法来实现。仍以上例为例，我们设定综合评定的规则为当各科平均分超过 90 时，评定为
8、优秀。如图 7 所示。 图 7说明：为了解释起来比较方便，我们在这里仅做两重嵌套的示例，您可以按照实际情况进行更多重的嵌套，但请注意 Excel 的 IF 函数最多允许七重嵌套。根据这一规则，我们在综合评定中写公式（以单元格 F12 为例）：=IF(F1160,IF(AND(F1190),优秀,合格),不合格)语法解释为，如果单元格 F11 的值大于 60，则执行第二个参数，在这里为嵌套函数，继续判断单元格 F11 的值是否大于 90（为了让大家体会一下 AND 函数的应用，写成 AND(F1190)，实际上可以仅写 F1190），如果满足在单元格 F12 中显示优秀字样，不满足显示合格字样，
9、如果 F11 的值以上条件都不满足，则执行第三个参数即在单元格 F12 中显示不合格字样。在综合评定栏中可以看到由于 F 列的同学各科平均分为 92 分，综合评定为优秀。（三）根据条件计算值在了解了 IF 函数的使用方法后，我们再来看看与之类似的 Excel 提供的可根据某一条件来分析数据的其他函数。例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使用 COUNTIF 工作表函数。如果要根据单元格区域中的某一文本串或数字求和，则可使用 SUMIF 工作表函数。关于 SUMIF 函数在数学与三角函数中以做了较为详细的介绍。这里重点介绍 COUNTIF 的应用。COUNTIF 可以用来
10、计算给定区域内满足特定条件的单元格的数目。比如在成绩表中计算每位学生取得优秀成绩的课程数。在工资表中求出所有基本工资在 2000 元以上的员工数。语法形式为 COUNTIF(range,criteria)。其中 Range 为需要计算其中满足条件的单元格数目的单元格区域。Criteria 确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、32、32、apples。1、成绩表这里仍以上述成绩表的例子说明一些应用方法。我们需要计算的是：每位学生取得优秀成绩的课程数。规则为成绩大于 90 分记做优秀。如图 8 所示 图 8根据这一规则，我们在优秀门数中写公
11、式（以单元格 B13 为例）：=COUNTIF(B4:B10,90)语法解释为，计算 B4 到 B10 这个范围，即 jarry 的各科成绩中有多少个数值大于 90 的单元格。在优秀门数栏中可以看到 jarry 的优秀门数为两门。其他人也可以依次看到。2、 销售业绩表销售业绩表可能是综合运用 IF、SUMIF、COUNTIF 非常典型的示例。比如，可能希望计算销售人员的订单数，然后汇总每个销售人员的销售额，并且根据总发货量决定每次销售应获得的奖金。原始数据表如图 9 所示（原始数据是以流水单形式列出的，即按订单号排列） 图 9 原始数据表按销售人员汇总表如图 10 所示 图 10 销售人员汇总
12、表如图 10 所示的表完全是利用函数计算的方法自动汇总的数据。首先建立一个按照销售人员汇总的表单样式，如图所示。然后分别计算订单数、订单总额、销售奖金。（1） 订单数 -用 COUNTIF 计算销售人员的订单数。以销售人员 ANNIE 的订单数公式为例。公式：=COUNTIF($C$2:$C$13,A17)语法解释为计算单元格 A17(即销售人员 ANNIE)在销售人员清单$C$2:$C$13 的范围内（即图 9 所示的原始数据表）出现的次数。这个出现的次数即可认为是该销售人员 ANNIE 的订单数。（2） 订单总额-用 SUMIF 汇总每个销售人员的销售额。以销售人员 ANNIE 的订单总额
13、公式为例。公式：=SUMIF($C$2:$C$13,A17,$B$2:$B$13)此公式在销售人员清单$C$2:$C$13 中检查单元格 A17 中的文本（即销售人员 ANNIE），然后计算订单金额列（$B$2:$B$13）中相应量的和。这个相应量的和就是销售人员 ANNIE 的订单总额。（3） 销售奖金-用 IF 根据订单总额决定每次销售应获得的奖金。假定公司的销售奖金规则为当订单总额超过 5 万元时，奖励幅度为百分之十五，否则为百分之十。根据这一规则仍以销售人员 ANNIE 为例说明。公式为：=IF(C1710。清单是指包含相关数据的一系列工作表行，例如，发票数据库或一组客户名称和电话号码
14、。清单的第一行具有列标志。2、 建立条件区域的基本要求（1）在可用作条件区域的数据清单上插入至少三个空白行。（2）条件区域必须具有列标志。（3）请确保在条件值与数据清单之间至少留了一个空白行。如在上面的示例中 A1:F3 就是一个条件区域，其中第一行为列标志，如树种、高度。3、 筛选条件的建立在列标志下面的一行中，键入所要匹配的条件。所有以该文本开始的项都将被筛选。例如，如果您键入文本“Dav”作为条件，Microsoft Excel 将查找“Davolio”、“David”和“Davis”。如果只匹配指定的文本，可键入公式=text，其中“text”是需要查找的文本。如果要查找某些字符相同但
15、其他字符不一定相同的文本值，则可使用通配符。Excel中支持的通配符为： 图 74、 几种不同条件的建立（1）单列上具有多个条件如果对于某一列具有两个或多个筛选条件，那么可直接在各行中从上到下依次键入各个条件。例如，上面示例的条件区域显示“树种”列中包含“苹果树”或“梨树”的行。（2）多列上具有单个条件若要在两列或多列中查找满足单个条件的数据，请在条件区域的同一行中输入所有条件。例如，下面示例的条件区域显示所有在“高度”列中大于 10 且“产量”大于 10 的数据行。图 8（3）某一列或另一列上具有单个条件若要找到满足一列条件或另一列条件的数据，请在条件区域的不同行中输入条件。例如，上面示例的
16、条件区域显示所有在“高度”列中大于 10 的数据行。（4）两列上具有两组条件之一若要找到满足两组条件（每一组条件都包含针对多列的条件）之一的数据行，请在各行中键入条件。例如，下面的条件区域将显示所有在“树种”列中包含“苹果树”且“高度”大于 10 的数据行，同时也显示“樱桃树”的“使用年数”大于 10 年的行。 图 9（5）一列有两组以上条件若要找到满足两组以上条件的行，请用相同的列标包括多列。例如，上面示例的条件区域显示介于 10 和 16 之间的高度。（6）将公式结果用作条件Excel 中可以将公式（公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号 (=) 开始。）的计算结果作为条件
