标签 Excel技巧 下的文章 - 🥝 E 家 分 享 🥝

Excel 的加载项和数据类型 前言下面是微软网站的视频，介绍 Excel 的新的 JavaScript APTs 加载项和数据类型。原文Excel add-ins and data typesBy Chris Gross Published Nov 02 2021 08:00 AMLearn about the exciting new additions we have added to the Office JavaScript APIs.参考文章： https://techcommunity.microsoft.com/t5/video-hub/excel-add-ins-and-data-types/ba-p/2909835【END】

Excel 通过 IF 进行字符串模糊查询 Excel 的学习方法最好就是现用现学，并做好记录。比如今天就针对一个比较复杂的筛选进行判断，边思考边从网上搜索解决方法。现分享如下：前言excel 表格里，需要通过公式查找，VLOOKUP，XLOOKUP是最常用的。但是这两个查找的结果都是符合条件的第一个，也只能返回一个结果。如果得到的结果很多，有需要把查到的结果都列出来，if是比较合适的，因为if返回的是一个列表，可惜的是if不支持模糊匹配，经过多方查找，找到了一个合适的方法。模糊查找很多时候，想要查找一个字符串，如“公司”两个字，需要用到模糊匹配，因为单元格的内容往往不止这两个字。模糊匹配格式是在字符串前后加上*，*代表任意字符。VLOOKUP函数、HLOOKUP函数、XLOOKUP函数、MATCH函数和LOOKUP函数等支持，但以上函数返回的都是单一数据。批量返回查找结果批量返回查找结果如果想要找到所有包含“公司”字符的内容，就需要返回一个列表，那就要用到if了，可是if不支持模糊查找，可以使用FIND+ISNUMBER实现这个功能.比如想查找单元格中包含某个字符串=FIND("你想查找的字符串",单元格)然后用ISNUMBER函数进行嵌套=isnumber(find("你想查找的字符串",目标列)) 返回true或者false =if(isnumber(find("你想查找的字符串",目标列)),返回的结果列,"")当然，如果你想判断的是多个字符串，可以在ISNUMBER外面嵌套OR函数。如果想把匹配结果在一个单元格内显示，再套一层=textjoin("、"",1,if(isnumber(find("你想查找的字符串",目标列)),返回的结果列,""))最后ctrl+shift+回车转换成数组模式，否则不能正常显示。参考文档https://blog.csdn.net/weixin\_43085185/article/details/122799085【END】

如何在 Excel 中将 DD.MM.YYYY 转换为日期格式 MM / DD / YYYY 前言最近在处理数据时遇到一个问题，将文本格式的时间转换成 MM / DD / YYYY 的形式。不知道是原始数据导出带来的问题还是其他原因，怎么转换都不成功，最终在网上找到了一个解决方案，现分享出来。方法使用公式将 DD.MM.YYYY 转换为 DD / MM / YYYY在某些情况下，您可以将 dd.mm.yyyy 直接转换为 dd / mm / yyyy 格式，下面的公式可能会对您有所帮助。选择要转换的日期旁边的空白单元格，输入此公式=SUBSTITUTE(A6,".","/")并将自动填充手柄拖到要应用此公式的单元格上。小技巧：在上面的公式中，A6 是您想要转换的日期，您可以根据需要进行更改。使用公式将 DD.MM.YYYY 转换为 MM / DD / YYYY如果要将日期格式 dd.mm.yyyy 转换为标准日期格式 mm / dd / yyyy，则可以应用以下公式。在您使用的日期（例如 B6）旁边选择一个空白单元格，输入此公式=(MID(A6,4,2)&"/"&LEFT(A6,2)&"/"&RIGHT(A6,2))+0将填充手柄拖到要使用此公式的单元格上。 现在，您可以看到一个由五位数组成的数字列表，保持选中状态，将其格式化为日期格式。 然后，您可以看到非标准日期已转换为标准日期。 总结参照下图进行转换参考资料：https://zh-cn.extendoffice.com/documents/excel/3784-excel-convert-dd-mm-yyyy-to-date-format-dd-mm-yyyy.html【END】

认识Excel中的数据类型 参考：https://mp.weixin.qq.com/s/Z92qqJW4TOhmB8VgDIHK-gExcel 中，函数与公式无疑是最具有魅力的功能之一。使用函数与公式，能帮助用户完成多种要求的数据运算、汇总、提取等工作。函数与公式同数据验证功能相结合，能限制数据的输入内容或类型，还可以制作动态更新的下拉菜单。函数与公式同条件格式功能相结合，能根据单元格中的内容，显示出用户自定义的格式。在高级图表、透视表等应用中，也少不了函数与公式的身影。常见数据类型Excel 中的数据类型包括数值、文本、日期时间及逻辑值和错误值。数值数值是指所有代表数量的数字形式，如企业的产值和利润、学生成绩、个人的身高体重等。数值可以是正数，也可以是负数，并且都可以用于计算。除了普通的数字外，还有一些带有特殊符号的数字也会被 Excel 识别为数值，如百分号（%）、货币符号（如￥）和千分间隔符（,）。如果字母 E 在数字中恰好处于特定的位置，还会被 Excel 识别为科学记数符号。例如，输入“5e3”，会显示为科学记数形式 5.00E+03，即 5.00 乘以 10 的 3 次幂。在 Excel 中，由于软件自身的限制，对于数值的使用和存储存在一些规范和限制。Excel 可以表示和存储的数字最大精确到 15 位有效数字。对于超过 15 位的整数数字，如 1234 567 890 123 456 789，Excel 会自动将 15 位以后的数字变为 0 来存储。对于大于 15 位有效数字的小数，则会将超出的部分截去。因此，对于超出 15 位有效数字的数值，Excel 将无法进行精确的计算和处理。例如，无法比较相差无几的 20 位数字的大小、无法用数值形式存储 18 位的身份证号码等。文本文本通常是指一些非数值性的文字、符号等，如企业名称、驾校考试科目、员工姓名等。此外，很多不需要进行数值计算的数字也可以保存为文本形式，如电话号码、身份证号码、银行卡号等。在公式中直接输入文本内容时，要用一对半角双引号（""）包含，如公式“=A1="ExcelHome"”。日期和时间在 Excel 中，日期和时间是以一种特殊的“序列值”形式存储的。在 Windows 操作系统上所使用的 Excel 版本中，日期系统默认为“1900 日期系统”，即以1900 年 1 月 1 日作为序列值的基准日期，这一天的序列值计为 1，这之后的日期均以距离基准日期的天数作为其序列值。例如，1900 年 1 月 15 日的序列值为 15。Excel 中可表示的最大日期是9999 年 12 月 31 日，其日期序列值为 2 958 465。输入日期后再将单元格数字格式设置为“常规”，此时就会在单元格内显示该日期的序列值。提示 Macintosh 操作系统下的 Excel 版本默认日期系统为“1904 日期系统”，即以1904 年 1 月 1 日作为日期系统的基准日期。另外，为了保持与 Lotus 1-2-3 相兼容，在 Excel 的日期系统中还保留了并不存在的 1900 年 2 月 29 日（1900 年不是闰年）。作为一种特殊的数值形式，日期承载着数值的所有运算功能。例如，要计算两个日期之间相距的天数，可以直接在不同单元格中分别输入两个日期，在第 3 个单元格使用单元格引用并用减法运算。日期序列值是一个整数，时间的序列值则是一个小数。一天的数值单位是 1，1 小时可以表示为 1/24 天，1 分钟可以表示为 1/(24 * 60) 天，一天中的每一个时刻都可以由小数形式的序列值来表示。例如，中午 12:00:00 的序列值为 0.5（一天的一半），12:30:00 的序列值近似为 0.520833。将小数部分表示的时间和整数部分表示的日期结合起来，就能以序列值表示一个完整的日期时间点。例如，2020 年 9 月 10 日中午 12:00:00 的序列值为 44 084.5。对于不包含日期的时间值，如“12:30:00”，Excel 会自动以 1900 年 1 月 0 日这样一个实际不存在的日期作为其日期值。如需在公式中直接输入日期或时间，需要用一对半角双引号（""）包含，如公式“="2021-8-15"-15”或“="10:24:00"+"0:30:00"”。逻辑值逻辑值包括TRUE（真）和FALSE（假）两种。假设A3单元格为任意数值，使用公式“=A3＞0”，就能够返回TRUE（真）或FALSE（假）。逻辑值之间进行四则运算或是逻辑值与数值之间的运算时，TRUE 的作用等同于 1，FALSE 的作用等同于 0。例如，FALSE * FALSE=0，TRUE-1=0错误值用户在使用 Excel 的过程中可能会遇到一些错误值信息，如 #N/A、#VALUE!、#DIV/0! 等，出现这些错误值有多种原因，常见的错误值及产生的原因如表 1-1 所示。表 1-1　常见错误值及产生的原因错误值：#VALUE! 产生的原因：在需要数字或逻辑值时输入了文本，Excel 不能将其转换为正确的数据类型错误值：#DIV/0! 产生的原因：使用 0 值作为除数错误值：#NAME? 产生的原因：使用了不存在的名称或是函数名称拼写错误错误值：#N/A 产生的原因：在查找函数中，无法找到匹配的内容错误值：#REF! 产生的原因：删除了有其他公式引用的单元格或工作表，致使单元格引用无效错误值：#NUM! 产生的原因：在需要数字参数的函数中，使用了不能接受的参数错误值：#NULL！ 产生的原因：在公式中未使用正确的区域运算符，产生了空的引用区域，例如将公式“=SUM(A3 : A10)” 误写为“=SUM(A3 A10)”；在区域引用之间使用了交叉运算符（空格字符）来指定不相交的两个区域的交集。 例如，公式“=SUM(A1:A5 C1:C3)”，A1:A5 和 C1:C3 没有相交的区域当单元格中所含的数字超出单元格宽度，或者在设置了日期、时间的单元格内输入了负数，将以 # 号填充。如果将使用数据模型创建的数据透视表通过【OLAP 工具】→【转换为公式】命令转换为公式，当参数设置错误时，会瞬时显示为错误值 “#GETTING\_DATA”，再转换为错误值 “#N/A”。提示 在 Microsoft 365 版本的 Excel 中，还增加了部分新的错误值类型，如错误值“#SPILL!”表示公式的溢出区域不是空白区域。在 Microsoft 365 版本的 Excel 中，还增加了部分新的错误值类型，如错误值“#SPILL!”表示公式的溢出区域不是空白区域。除此之外，如果数据源中本身含有错误值，大多数公式的计算结果也会返回错误值。【END】

【E家原创】轻松读取 Excel 表中的值 问题一个包含行、列和数据的 Excel 表格，需要查找特定值。 比如下图中查找三月份西部区域数值800。解决方法一般会使用 vlookup 或 xlookup 等函数来实现。实现公式如上图所示。 接下来要介绍的是一种快速、无公式、智能的方法来实现。选中表格，单击 Formulas 菜单，选择 Create from Selection。默认选择首行和左列，点击 OK。 这时单击 名称 列表，会看到表格的首行和左列的标题已经被定义成名称。单击 名称 列表中的标题，会发现当前行或列的内容会被高亮显示。 此时，可以在单元格内输入 =三月 西部 （注意字符间有空格），回车即返回数值800。 三种解决方法如下图所示： 操作演示源文件下载地址「轻松读取Excel表中的值.xlsx」https://www.aliyundrive.com/s/oVsPuhuLQts 提取码: n4m3https://www.123pan.com/s/Oy5RVv-CeYB 提取码: NZoc【END】