函数技巧
-
SUMPRODUCT函数的使用方法和实例解析
今天和大家分享一个名字拖沓，但是应用非常广泛的函数——sumproduct，语法：sumproduct(参数1,参数2……），被称为万能的神函数，可以求和、计数，还能排序；sum是求和的意思，product是指相乘，函数结果是返回给定数组对应的乘积之和，日记坊将带给你sumproduct函数的使用技巧及精彩实例；开门见山，咱们通过几个例子，一起来看下SUMPRODUCT具体能干什么样的工作。假设上面这张图，是某个公司工资发放的部分记录表，A列是工资发放的时间，B列是员工所属的部门，C列是员工姓名，D列是相关员工领取...
-
excel函数三剑客，因为太厉害，所以被藏起来了
Excel中有一些不容易被发现的函数，它们被微软给隐藏起来了。通常你在函数工具栏中找不到它们，并且你在帮助文档里找不到它们的相关说明。但即使这样，也藏不住他们强大的功能的光芒，以及被人们所认知，如果你还不知道的话，请跟我一起来温习一下吧；日记坊之前的文章也有涉及到这三个函数的，现在对它们进行一个总结。第一个：datestring函数DATESTRING通常用来计算日期，转换格式。通常会转换为（年，月，日）的形式。语法结构：DATESTRING（日期）下面是使用方法，它可以将任意格式的日期形式，转换为它的形式。上面的...
-
excel表格中将汉字与数字混合的单元格分列方法
今天早上，看了一下世界新冠疫情实时数据，想做一个相关的图表，于是将网页上的数据导出到excel中，却发现导出来的数据国家与病例数据都在同一单元格里，尝试了一下常规的分列方法，结果是失败的，因为导出的数据中文汉字与数字同一个单元格里，而且国家名字有长有短，根本无迹可循，如下图：研究了一下，发现只有用函数提取字符数字然后截取才能实现中文与数字混合的分列，一共需要四个函数就可以实现了：left函数：从文本字符串的最左边字符开始，截取指定数目的字符，与right函数完全一样，只是方向不同，语法：left（字符串，提取数量）...
-
vlookup函数将汉字转换拼音首字母
在制作表格的时候，我们需要提取汉字的首个拼音字母，如果使用VBA可以分分钟轻松实现，但VBA的使用并不是很普及，所以仅使用函数就可以实现这些需求就成了很多人寻找的方法；如下图：这些都是可以使用函数实现的，并不需要在excel和word中粘贴转换这么麻烦，省了不少步骤，减少失误；第一，整理对照表我们需要先整理一份汉字和字母的对照表，然后就可以用vlookup函数或者是lookup函数的查找单个汉字的拼音首字母了。日记坊找来了Asc码的对照字母顺序表，然后得出了汉字与字母的对照关系，对照表如下：字母起止汉字对照表A45...
-
xlookup函数的引用新用法
前面文章有介绍过Xlookup函数的使用方法，功能上秒杀Vlookup函数，今天又发现Xlookup的一个新功能，它查找返回的竟然是单元格引用。【例1】如下图所示，要求对中国和泰国之间的B列数字求和公式：=SUM(XLOOKUP(D2,A:A,B:B):XLOOKUP(E2,A:A,B:B))从上面的公式中可以看出，先用Xlookup查出中国所在行的B列单元格，再查出泰国的B列单元格，然后直接套用到Sum(开始单元格,结束单元格)进行求和。利用返回引用的特点，只用xlookup即可搞定双向查找。【例2】如下图所示，...
-
index函数的使用技巧及实例演示
index函数是返回表或区域中的值或值的引用,可以是数组形式或引用形式，又可以分连续区域和非连续区域内的引用两种；通俗语法：index（区域或数组，返回行，返回列），index+match是查找引用的经典套路组合，日记坊将带给你index函数的使用技巧及精彩实例；实例演示：1、index函数从单行/列中提取指定位置的值①如下图，从A列提取第4个位置的值，结果为A4的值；公式：=INDEX(A:A,4)；②从A3:D3区域提取第3个位置的值，结果是C3的值；公式：=INDEX(A3:D3,3)；说明：一个单行/列的区...
-
vlookup函数的使用技巧及实例演示
vlookup函数是一个纵向查找函数，通俗语法结构：vlookup（找谁，在哪找，找第几列，精确还是模糊查找），它与lookup函数和hlookup函数属于一类函数，日记坊将带给你vlookup函数的使用技巧及精彩实例；案例演示：拓展vlookup函数是从左向右正向引用，如果反向引用，可以使用vlookup+if组合函数，可适用于多条件引用；公式说明：if后面加的{1,0}，表示如果1的正向从左到右不能返回正确结果，那就从返向即从右到左进行查找，这个函数可是好帮手；如果有多个条件，将各项条件用&符号连接起来...
-
lookup函数的使用技巧及实例演示
函数主要用于在查找范围中查询指定的查找值，并返回另一个范围中对应位置的值。语法：lookup(查找的值，查找的范围，返回值的范围)，lookup函数在excel中是一种非常强大和灵活的运算函数，计算返回向量或数组中的数值，要求数值必须按升序排序,否则结果错误，该函数支持忽略空值、逻辑值和错误值来进行数据查询，几乎可以完成VLOOKUP函数和HLOOKUP函数的所有查找任务，日记坊将带给你lookup函数的使用技巧及精彩实例；一、返回B列最后一个文本：公式：=LOOKUP("々",B:B)或是=L...
-
xlookup函数的使用技巧及实例演示
xlookup函数是2019年8月发布的查找函数，共5个参数，语法：xlookup（查找的值,查找范围或数组,返回范围或数组,查找模式,搜索模式），其中参数123是必须项，参数45是可选项，日记坊将带给你xlookup函数的使用技巧及精彩实例；1、常规查询如下图所示，要根据G1的部门，在A列查询该部门，并返回B列对应的负责人姓名。公式为：=XLOOKUP(G1,A2:A11,B2:B11)公式的意思就是在A2:A11单元格区域中查找G1单元格指定的部门，并返回B2:B11单元格区域中与之对应的姓名。2、逆向查询由于...
-
excel表格制作账龄分析台账
使用excel表格制作账龄分析台账，只需要使用到几个简单的函数，不算复杂；我们来看一下台账需要用到的必要条件：客户名称、应收（应付）到期日、应收（应付）账款、excel函数等，如下图：该表制作账龄有两种方法：①添加辅助列；②不添加辅助列；制作excel表格账龄表一、vlookup函数制作应收账龄台账1、添加辅助列我们在A列之前插入一列，A5:A8输入0，30，60，90，将账龄区间定义为0-30天，30-60天，60-90天，90天及以上，一般公司90天以上账龄也算是长的了；2、计算I列账龄区间输入公式：vlook...
-
EXCEL的8种舍入函数用法及示例
我们经常会遇到数值的小数位的舍入处理，excel中提供了众多功能强大的数值舍入的函数，我们今天就来盘点回忆一下8种excel的舍入函数的用法及实例处理；1、round函数该函数将数字四舍五入到指定的位数，语法：round(数值,小数位数)；如果单元格 A1 包含 55.3825，您想要将此数值舍入到两个小数位数，可以使用公式：=ROUND(A1, 2)，此函数的结果为 55.38。说明：语法中的小数位数有三种，大于0，0，小于0；①如果小数位数大于 0，则将数字四舍五入到指定的小数位数。②如果小数位数等于 0，则将...
-
使用char函数在EXCEL中生成随机或正序的大小写字母
在excel中生成大小写字母，如果是随机生成的，使用char+randbetween函数可以完成；如果需要横向生成正序大小写字母，使用char+column函数可以完成；如果需要纵向生成正序大小写字母，使用char+row函数可以完成；用途：工作需要、幼儿英文字母认识练习....我们先看一下这几个函数的语法和作用：1、char函数：主要作用是将1-255之间的整数转换成对应的ANSII符号(字符集)，语法：char(数字）；ascii码的65-90是大写字母，97-122是小写字母；2、randbetween函数主...
-
substitute函数的使用方法和实例解析
substitute，经典的替换函数，它的主要作是对指定的字符串进行替换，共有4个参数；通俗语法：substitute（需要替换的文本，旧文本，新文本，[第N个旧文本]）；其中第4参数可以省略，表示替换所有的旧文本，跟其他函数组合，运用更灵活；案例1、substitute函数批量对指定数字或文本进行替换公式：SUBSTITUTE(A2,"中山市","广州市")，说明：此处仅使用了三个参数，第四个参数省略，表示将所有符合条件的旧文本进行替换；案例2：substitute函数配合...
-
快过年了，向女神告白你想说的话，带她回家！
你想跟你心中的女神说些什么？9012年都快过完了，趁过年之前向她告白，把她带回家吧！1、连续输入字母在EXCEL中，输入字母向下或向右拉的方法是不能连续填充的，可以使用公式来完成呀；公式：SUBSTITUTE(ADDRESS(1,ROW(A1),4),1,)解析：address(1,row(A1),4),取得A1单元格的地址名称，第一参数，指定所在行；第二参数，指定所在列；第三参数有1-4个选择，分别是：1，绝对引用；2，行绝对引用；3，列绝对引用；4，相对引用；address(1, row(a1),4)...
-
简短而不简单，记EXCEL最精简的两个函数
有些函数名字很长，有些函数却很短；有些函数使用很复杂，有些函数却很简单；多数时候，大家都追求华丽的表达，但却忘记了，简洁明了才是我们的重点；比如这个T和N，精简而实用的两个名字最短的函数；一、N函数公式用法：N(Value)，主要功能是将文本型数据转换成0，而数值型则不变；1、数值型数据，N函数后返回值不变；2、文本型数字，N函数返回的结果是0；3、文本型文本，N函数返回的结果也是0；4、逻辑值，N函数TRUE返回1，FALSE返回0；5、日期值，N函数返回的是时间序列；6、错误值，N函数返回的也是错误值；二、T函...
-
经典LOOKUP函数的九种运用方法
今天介绍的是lookup函数，它的超级强大自是无需多言，它的参数可以是向量，也可以是二维的数组；语法结构（向量）lookup（①查找值，②查找所在区域，③返回的结果）①查找值，大家都明白；②查找所在区域，为行或者是列，需要留意的是，需要升序排列，不然结果返回不正确；③返回的结果，当没找到精确值时，返回小于离查找值最近的值；（数组）lookup（①查找值，②二维数组）经典实例运用套路公式=lookup(1,0/(条件1)*(条件2)*..(条件n),目标区域或数组)如果是使用在区间闭合方向不同的时候，可以：=look...
-
KPI区间闭合方向不同，如何快速取值
公司组织KPI考核，不同公司KPI考核方法和要求各不相同，最终结果无非是计算各条件最后得分，并根据得分在不同的区间段进行评定系数，今天日记坊跟大家介绍一下，对得分在不同的区间段，闭合方向不同的情况下，如何快速计算；1 某公司的KPI计算方法要求如下： 该方案的闭合方向是：80≤分值<85,在这种要求下，简单很多，与EXCEL中很多函数默认的取值区间方向相同，在这里，日记坊九天给大家介三种方法取值： 为方便取值，建议大家起一列辅助列，将各区间分数与对应的值填列出来；当然也可以不用填列，直接在公式中使用...
-
日期函数的使用技巧，新手收藏，高手略过~
一、计算两个日期之间相隔多少小时 方法：（结束时间-开始时间）*24 二、计算两个日期之间相隔多少分钟 方法：（结束时间-开始时间）*1440 三、两个日期之间，除去周末，有多少个工作日 方法1：NETWORKDAYS.INTL(开始日期，结束日期，，法定假期） 方法2：NETWORKDAYS(开始日期，结束日期，法定假期） 注：以上不含周末调休上班 四、高亮显示日期为周末的单元格 方法：条件格式输入：weekday(A$2,2)>5 五、快速合并日期和时间 如下图，日期和发生时间如...
-
用网上软件给宝宝起名字？out啦！2个函数就搞定！
写这篇文章的起因是我在模拟某个项目的数据时突发奇想而想到的，自已测试过后，给朋友们分享一下，如果有准爸爸或是准妈妈在阅读本文，不要走开哦； 在进入主题之前，我还是把我模拟项目的过程给大家分享一下，因为有些技巧非常实用，很可能会帮助你解决一些实际工作中的问题，提高一丢丢的工作效率； 1、建立表格 使用表格样式和公式快速建立一个简单的表格； 2、快速选取目标列，并建立序号 3、随机取名字 3、给各位同学模拟性别 4、给各科成绩模拟分值 好了，表格至此模拟完了，如果其中的技巧能对你有帮助，日记坊君九天...
-
年终了，KPI来了，快速统计员工流失数据
不知不觉又到岁末，年终报告又如约而至，在很多传统行业，公司对员工流失率还是看得蛮重要的，如何维持公司生存，很多公司HR都各施招法，但这个KPI指标还是架在了头上，比如日记君所在的公司就是这样； 本文是日记君在解答同事的问题，顺便记录下来和大家分享，我们以统计第三季度为例，来看看如何快速统计在某个时间段员工离职数据； 一 公式：COUNTIFS(C2:C26,">=2019-7-1",C2:C26,"<2019-10-1") 解释：函数countifs(...
