做表格公式及应用小技巧

在Excel中，公式和应用是提升工作效率的关键。以下是一些常见的Excel公式及其应用场景的详细解释： 1. **查找相同值对应的位置**：`=MATCH(B1, A:A, 0)` 这个公式用于在A列中查找B1单元格的值，并返回其相对位置。0参数表示精确匹配。 2. **EVALUATE和GET.CELL函数**：`EVALUATE`函数可以计算一个字符串表达式的值，例如`Sheet1!C1`中的公式。而`GET.CELL(6, Sheet1!C1)`则用于获取单元格的格式信息，6代表颜色索引。 3. **COUNTIF函数**：`COUNTIF`函数用于计数满足特定条件的单元格。例如，`>=90`的计数方式是`=COUNTIF(A1:A10, "&gt;=90")`。如果要计算介于80与90之间的单元格，需要使用减法，即`=COUNTIF(A1:A10, "&gt;80") - COUNTIF(A1:A10, "&gt;90")`。 4. **根据身份证号提取出生日期**：有两种方法，一种是使用嵌套的`IF`函数，另一种是使用`TEXT`和`MID`函数组合。前者更直观，后者更简洁。 5. **防止输入重复数字**：通过设置数据验证，可以确保单元格内不输入重复数字。在“数据”菜单中选择“数据验证”，然后在“允许”下拉框中选择“自定义”，公式栏中输入`=COUNTIF(A:A, A1)=1`。 6. **多列数据合并成一列**：`=SUM(OFFSET($A, (ROW()-2)*10+1,,10,1))` 这个公式用于将数据区域按行偏移并累加。 7. **查找函数VLOOKUP和MATCH**：`VLOOKUP`用于根据行查找，`MATCH`用于根据列查找。两者结合可以实现复杂查找，如`=VLOOKUP(H1, A1:E7, MATCH(I1, A1:E1, 0), FALSE)`。 8. **多个工作表单元格合并计算**：`=SUM(Sheet1:Sheet3!D4)` 这种方式可以快速求和多个工作表同一单元格的数据。 9. **获取工作表名称和路径**：可以通过`GET.DOCUMENT`和`CELL("filename")`等函数来获取当前工作簿的名称和路径。 10. **获取一个月的最大天数**：`=DAY(DATE(2002, 3, 1) - 1)` 或 `=DAY(B1 - 1)`，其中B1为日期。 11. **数据区特定字符项的总和**：使用`SUMIF`函数，如`=SUMIF(A:A, "*某一字符*", 数据区)`。 12. **查找序列中的最后一个数字或文本**：可以使用`OFFSET`、`MATCH`和`CHAR(65535)`或`9.9999E+307`等方法。 13. **去除评分中的极端值**：利用`TRIMMEAN`函数可以计算排除最高和最低分后的平均分。极端分数可通过`LARGE`和`SMALL`函数获取。 14. **判断末尾是否为字母**：可以使用`ISNUMBER`和`ISERR`配合`RIGHT`函数来检查。 15. **设置单元格不允许输入包含空格的字符**：通过数据验证，设置自定义公式如`=AND(NOT(ISERROR(FIND(" ", A1))), A1<>"")`，这将拒绝包含空格的输入。 这些技巧可以帮助你在处理Excel表格时提高效率，简化复杂的任务。熟练掌握它们，你将在日常工作中事半功倍。