学习Excel技术，关注微信公众号： excelperfect 我们知道，在Excel中，日期是以序号数字来存储的，虽然你在工作表中看到的是“2020-3-31”，而Excel中存储的实际上是“43921.00...”，整数部分是日期的序号，小数部分是当天时间的序号。...这样方便了日期的表示和存储，但也同样带来了一些问题，例如我们以为是“2020-3-31”，因此会将数据直接与之比较，导致错误的结果。本文举一个案例来讲解公式中日期的处理方式。...等价于公式： =AVERAGE(B2:B7) 5. 我们注意到，上面的公式中我们没有提供IF函数的参数value_if_false的值，这是有原因的。...其实，Excel 2007及以后的版本中引入了一个函数AVERAGEIFS，可以很好地解决上述问题，其公式为： =AVERAGEIFS(B2:B20,A2:A20,DATE(2020,3,31)) 或者
学习Excel技术，关注微信公众号： excelperfect 在查找相匹配的值时，如果存在重复值，而我们想要获取指定的匹配值，那该如何实现呢？...图1 我们知道VLOOKUP函数通常会返回找到的第一个匹配值，或者最后一个匹配值，详见《Excel公式技巧62：查找第一个和最后一个匹配的数据》。...然而，我们可以构造一个与商品相关的具有唯一值的辅助列（详见《Excel公式技巧64：为重复值构造包含唯一值的辅助列》），从而可以使用VLOOKUP函数来实现查找匹配值。...首先，添加一个具有唯一值的辅助列，如下图2所示。 ? 图2 在单元格B3中输入公式： =D3 & "-" &COUNTIF( 下拉至单元格B14。...在单元格H6中输入公式： =VLOOKUP(H2 & "-" &G6,B3:E 即可得到指定的匹配值，如下图3所示。 ? 图3 可以修改单元格H2或G6中的数值，从而获取相应匹配的数据。
也就是说，当VLOOKUP执行近似查找时，取决于查找列按升序排列。这意味着，它不是从顶部到底部进行搜索，而是通过在数据中上下跳跃来进行查找（二分查找）。...图1 单元格D2中的公式为： =VLOOKUP(C2,F2:G6,2,TRUE) 向下复制至单元格D5。...示例2：查找列按升序排列且执行精确查找 如下图2所示，列表中有一系列日期相对应的人名，现在想要选择日期后获取该日期对应的人名。 ?...图3 示例3：查找列无序 VLOOKUP函数的一种巧妙的使用，与查找列的排序顺序无关。 听起来有些奇怪，但在某些情况下排序顺序实际上并不重要。一个很好的示例是，当需要一个返回列中最后一个数字的公式时。...我们知道，Excel允许的最大正数是1.7976931348623158e+308，因此，我们可以定义名称BIGNUM为： =9.99999999999999E+307*1.79769313486231
引言：本文的练习整理自chandoo.org。多一些练习，想想自己怎么解决问题，看看别人又是怎解决的，能够快速提高Excel公式编写水平。 本次的练习是：给1900年前的日期加上或者减去一定的天数。...示例数据如下图1所示，列A中的日期，加上或减去列B中的天数，返回正确的日期。 图1 假设所有的日期都使用mm/dd/yyyy格式，并且都大于0年。...不应该使用任何辅助单元格、中间公式、命名区域或者VBA。 写下你的公式。...公式中： DATE(MID(A2,7,4)+2000,MID(A2,1,2)+0,MID(A2,4,2)+0) 得到年份、月份和日，年份加上2000以满足Excel表示日期的要求。...返回： 725014 再加上单元格B2中的天数，并传递到TEXT函数： TEXT(725014+B2,"MM/DD/YYYY") 返回： "02/05/3885" 公式中： YEAR(DATE(MID(
本次的练习是：如何使用公式求得最近的日期？例如，下图1所示，x表示该日期开展了一次活动，在列G中求出对应的最近一次活动日期。 ? 图1 先不看答案，自已动手试一试。...解决方案 公式1：使用LOOKUP函数 =LOOKUP("y",C4:F4,F3) 由于示例中采用“x”表示开展活动对应的日期，使用其随后的字母“y”来查找，显示在对应区域找不到该值，这样LOOKUP函数会返回与查找值最接近的值...，即最后一个“x”，然后返回对应的日期行中的日期。...公式2：使用MAX/SUMPRODUCT函数 =SUMPRODUCT(MAX((C3:F3)*(C4:F4="x"))) 由于日期在Excel中是以数字形式存储的，因此可以将它们与TRUE/FALSE值组成的数组相乘...{41091,41092,0,0})) 得到： 41092 即该日期对应的序数，设置适当的格式后在Excel中显示相应的日期。
下图1所示，求单元格F12中指定名称所对应的最新日期？在单元格区域B12:C20中是要查找的数据。 ? 如何在单元格F13中编写公式？ 先不看答案，自已动手试一试。...解决方案 公式1：使用LOOKUP函数 =LOOKUP(2,1/(B13:B20=F12),C13:C20) 很显示，使用LOOKUP公式不可取，我们必须构造一个供查找的数组，即公式中的： 1/(B13...公式2：使用MAX/SUMPRODUCT函数 =SUMPRODUCT(MAX((B13:B20=F12)*(C13:C20))) 这个公式由于日期在Excel中是以数字形式存储的，因此可以将它们与TRUE...41091;41092;41092;41093;41094;41094})) 可转换为： =SUMPRODUCT(MAX({41091;0;0;41092;0;0;0;0})) 得到： 41092 即该日期对应的序数...，设置适当的格式后在Excel中显示相应的日期。
Excel的常见用途之一是维护事件、安排或其他日历相关内容的列表。我们可以使用一些想象力以及条件格式、少量的公式和几行VBA代码，在Excel中创建一个流畅的交互式日历，使信息可视化。...4.指定某单元格来识别所选择的日期 在工作簿中选择一个空单元格，将其命名为“selectedCell”，该单元格将用于识别用户选择的日期。...，当选择有效日期时显示详细情况 每件事有与之相关的4个详细信息：标题、日期、地点和详细情况描述。...当用户选择日历中的日期时，显示事情的详情。...由于所选的日期在“selectedCell”中，我们使用VLOOKUP、IF、IFERROR来完成： 如果所选日期中有事件，则获取单元格中事件标题，否则为空：=IFERROR(VLOOKUP(selectedCell
这样，获取值的数组公式（单元格C7）如下： =VLOOKUP(A7&”|”&B7,INDIRECT("'"&INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets...A1:D10"),4,0) 其中，Sheets是定义的名称： 名称：Sheets 引用位置：={"Sheet1","Sheet2","Sheet3"} 这个公式的运行原理与上文相同，可参见《Excel公式技巧...16：使用VLOOKUP函数在多个工作表中查找相匹配的值（1）》。...B1 公式中的： INDIRECT("'"&INDEX(Sheets,Arry1)&"'!C1") 得到： Sheet3!C1 现在，单元格C3中的公式变为： =INDEX(Sheet3!...同样地，公式中的： N(OFFSET(Sheet3!
在某个工作表单元格区域中查找值时，我们通常都会使用VLOOKUP函数。但是，如果在多个工作表中查找值并返回第一个相匹配的值时，可以使用VLOOKUP函数吗？本文将讲解这个技术。...图4：主工作表Master 数组公式如下： =VLOOKUP($A3,INDIRECT("'"&INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"...B1:D10"),3,0) 其中，Sheets是定义的名称： 名称：Sheets 引用位置：={"Sheet1","Sheet2","Sheet3"} 在公式中使用的VLOOKUP函数与平常并没有什么不同...公式中的： COUNTIF(INDIRECT("'"&Sheets&"'!...B:B"}),$A3) INDIRECT函数指令Excel将这个文本字符串数组中的元素转换为单元格引用，然后传递给COUNTIF函数，同时单元格A3中的值作为其条件参数，这样上述公式转换成： {0,1,3
本文用的主要是pandas，绘图用的库是plotly，实现的Excel的常用功能有： Python和Excel的交互 vlookup函数 数据透视表 绘图 以后如果发掘了更多Excel的功能，会回来继续更新和补充...vlookup函数 vlookup号称是Excel里的神器之一，用途很广泛，下面的例子来自豆瓣，VLOOKUP函数最常用的10种用法，你会几种?...方法：在B2:B7区域中输入公式=VLOOKUP(A2&"*", 折旧明细表!...方法：使用VLOOKUP+MATCH函数，在“2010年3月员工请假统计表”工作表中选择B3:F8单元格区域，输入下列公式=IF(A3="","",VLOOKUP(A:H,MATCH(B2,员工基本信息...[3] 问题：需要汇总各个区域，每个月的销售额与成本总计，并同时算出利润 通过Excel的数据透视表的操作最终实现了下面这样的效果： python实现：对于这样的分组的任务，首先想到的就是pandas
参考资料： 七周成为数据分析师 知乎 | 怎样快速掌握 VLookup？ 【训练营】职场Excel零基础入门 ?...2.反向查找 当检索关键字不在检索区域的第1列，可以使用虚拟数组公式IF来做一个调换。 =VLOOKUP(G2,IF({1,0},B2:B8,A2:A8),2,0) ?...反向查找 反向查找的固定公式用法： =VLOOKUP(检索关键字，IF({1,0},检索关键字所在列，查找值所在列)，2，0) 注意：其实反向查找除了检索区域改成一个虚拟数组公式IF之外，其他和单条件查找没有区别...多条件查找 返回多列的固定公式用法： =VLOOKUP(混合引用关键字，查找范围，COLUMN(xx)，0) 返回第几列就用COLUMN函数引用第几列的单元格即可。...时间序列函数 时间的本质是数字。 YEAR MONTH DAY 分别返回日期序号的年、月、日。 =YEAR(日期序号) =MONTH(日期序号) =DAY(日期序号) ?
在Excel中，标准的查找函数例如INDEX、MATCH、VLOOKUP等都非常好，但当存在重复值时就比较困难了。如下图1所示，提取满足3个条件的数据记录，可以看出有2条记录满足条件。...辅助列包含提供顺序号的公式，只要公式找到了满足条件的记录。这些顺序号解决了重复值问题，因为对于每条匹配的记录都有唯一的标识号。辅助列作为查找列，供查找函数查找并提取数据。 2.基于全数据集的数组公式。...单独使用AND函数的问题是获得了两个TRUE值，这意味着又回到了查找列中有重复项的问题。真正想要的是查找列包含数字，其中单元格E14中第一个TRUE是数字1，而E17中第二个TRUE是数字2。 ?...，使用INDEX和MATCH函数仅提取部分列的数据 如下图7所示，使用AND和OR条件的辅助列，只从日期和商品数列中提取数据。...图7：AND和OR条件，双向查找从日期和商品数列中获取数据 未完待续>>> 注：本文为电子书《精通Excel数组公式（学习笔记版）》中的一部分内容节选。
plotly，实现的Excel的常用功能有： Python和Excel的交互 vlookup函数 数据透视表 绘图 以后如果发掘了更多Excel的功能，会回来继续更新和补充。...vlookup函数 vlookup号称是Excel里的神器之一，用途很广泛，下面的例子来自豆瓣，VLOOKUP函数最常用的10种用法，你会几种?...：类似于案例二，但此时需要使用近似查找 方法：在B2:B7区域中输入公式=VLOOKUP(A2&"*", 折旧明细表!...方法：使用VLOOKUP+MATCH函数，在“2010年3月员工请假统计表”工作表中选择B3:F8单元格区域，输入下列公式=IF($A3="","",VLOOKUP($A3,员工基本信息!...方法：在C9：C11单元格里面输入公式=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2:$B$6&COUNTIF(INDIRECT("b2:b"&ROW($2:$6)),B$9),$
VLookup无疑是Excel中进行数据匹配查询用得最广泛的函数，但是，随着企业数据量的不断增加，分析需求越来越复杂，越来越多的朋友明显感觉到VLookup函数在进行批量性的数据匹配过程中出现的卡顿问题也越来越严重...、“雇员”、“订购日期”、“到货日期”、“发货日期”等6列数据匹配到订单明细表中。...在思考这些问题的时候，我突然想到，Power Query进行合并查询的步骤，其实是分两步的： 第一步：先进行数据的匹配 第二步：按需要进行数据的展开 也就是说，只需要匹配查找一次，其它需要展开的数据都跟着这一次的匹配而直接得到...，而我们在前面用VLookup、Index+Match写公式的思路则是对每一个需要取的值，都是一次单独的匹配和单独的取值。...，用时约17秒，约为直接使用VLookup函数或Index+Match函数组合公式（约85秒）的五分之一！
2.保险精算： 运用数学，统计学，保险学的理论和方法，对保险经营中的计算问题作定量分析，以保证保险经营的稳定性和安全性。...3.税务审计： 在定制的审计底稿上填报基础数据，通过Excel的公式计算汇总，整理成审计人员需要的信息，生成审计报告，常见于税费汇算清缴，税务稽查工作等。...如果用软件系统来管控，在前端页面中操作Excel，可以解决版本控制，以及打通数据孤岛的相关问题，但会引入新的问题：限于浏览器运行环境的资源限制，模型中蕴含大量的复杂公式计算容易造成交互端的性能瓶颈。...解决方案： 基于前端运行环境性能瓶颈的存在，不能将大量公式计算放在前端进行。...我们接下来采取前后端结合的全栈方案，服务端利用GcExcel高效的性能进行公式计算，前端采用SpreadJS，利用其与GcExcel的兼容性和前端类Excel的操作和展示效果，将后端计算后的结果进行展示
excelperfect 当今社会，电梯已经成了建筑物的必备之物。通常，当进入电梯的人员重量之和超过设定的重量时，电梯会报警并且停止运行。...这篇文章的素材来源于chandoo.org，让你使用Excel公式判断电梯能否运行。示例数据如下图1所示。...图1 电梯能否运行的判断条件是： 如果电梯里面的人数大于20人，或者人员总重量超过1400kg，那么电梯会停止运行。 图1中给出了10行数据，你能使用10个不同的公式进行判断吗？...是的，这个问题很简单，也很容易想出解决方案的公式，但要使用10个不同的公式，还是需要动点脑筋。 我们先从最常规的开始。...在单元格B5中输入公式： =IF(OR(COUNT(C5:X5)>AA4,SUM(C5:X5)>AA5),"不能","能") 根据条件，要满足不超过20人，则记录的数据最多到列V，不能到列W，因此列W中单元格的数据应为空
Excel日常操作中最怕的不是不会公式啥的，而是被一些疑难杂症搞怕了，这些疑难杂症往往有一个共同点，那就是：看起来什么都没错，但就是报错了。 ?...会计专用：这个薪酬的小伙伴可能也会用得着，和货币格式相比，这个格式除了会自动补齐货币符号以外，还会自动补齐小数点 日期：日期格式，和其他软件相比，Excel的日期格式要强大许多，因为可以选择国家地区...，再选择日期格式，直接解决了每个国家有不同的表达习惯的问题 ?...VLOOKUP坏了么 有的时候我们会遇到一个非常诡异的情况，明明这个工号就是有的，但是VLOOKUP偏偏报错了，就像这样: ? 明明公式是完全正确的，但就是报错了！...点击这个感叹号，选择“转化为数字”这个问题就解决啦~ 类似的，如果直接修改D列的数据为文本的话似乎也没啥反应。
有人会说，现在网上excel技巧太多，一眼看过去感觉各个都好牛逼，恨不得全部收藏起来。可是，能真正能用到的时候并不多，因为学习的知识都太散了，也不能及时进行总结整理。...前面我介绍了有关于数据整理中的一些小技巧，本次将为大家介绍的是excel函数与公式的应用。 直接上香喷喷的干货啦！！！...： （3）使用公式VLOOKUP将编码转换为地区，公式为“=VLOOKUP(C2：L:M,2，0)”,结果如图15: 2.员工的性别： (1)18位身份证号码中倒数第二位是用来判断性别，奇数为男，偶数为女...： (1)身份证号码的第7到15位对应的编码是出生日期； (2)在F2中输入公式“=MID(B2，7，8)”，提取出的是文本类型的，没有办法直接转换成为日期格式,如图17： (3)换一种方法，输入公式...1.要记录到具体的时间点，输入公式"=NOW()"，如图19： 2.要记录到具体的日期，输入公式"=TODAY()"，如图20： 函数12：MONTH、YEAR、DAY函数 YEAR函数用来计算某个日期值中的年份
文 | 兰色幻想-赵志东 函数是excel中最重要的分析工具，面对400多个excel函数新手应该从哪里入手呢？下面是实际工作中最常用的8个（组）函数，学会后工作中的excel难题基本上都能解决了。...第一名：Vlookup函数 用途：数据查找、表格核对、表格合并 用法： =vlookup（查找的值，查找区域，返回值所在列数，精确还是模糊查找） 第二名：Sumif和Countif函数 用途：按条件求和...用法： =Left(字符串，从左边截取的位数) =Right(字符串，从右边截取的位数) =Mid(字符串，从第几位开始截，截多少个字符） 第七名：Datedif函数 用途：日期的间隔计算。...用法： =Datedif(开始日期，结束日期."y") 间隔的年数 =Datedif(开始日期，结束日期."M") 间隔的月份 =Datedif(开始日期，结束日期."...D") 间隔的天数 第八名：IFERROR函数 用途：把公式返回的错误值转换为提定的值。如果没有返回错误值则正常返回结果 用法： =IFERROR(公式表达式，错误值转换后的值） end
领取专属 10元无门槛券
手把手带您无忧上云
云服务器
即时通信 IM
ICP备案
对象存储
实时音视频
