在写EXCEL公式的时候，经常遇到相对引用、绝对引用与混合引用。相对引用是指，公式进行填充的时候，从属单元格与引用单元格的相对位置不变；绝对引用是指，公式进行填充的时候，引用单元格的绝对位置不变；混合引用是指，公式进行填充的时候，所引用单元格的行或列方向之一的绝对位置不变，而另一个方向位置发生变化。可分为对......
2021-12-29 21:28:35 431 excel公式技巧 excel函数公式 Excel常用函数 excel教程
今天，有一位男士提出这样一个问题：他那可爱的老婆，做微商，学着用EXCEL做买卖记录，今天拿出记录表让他帮着计算现有的存货，这位男士拿到数据，眼睛瞪的老大！这样的交易记录怎么算目前存货？如下：来支招：关键操作第一步：选项设置【文件】——【选项】——【高级】：勾选【转换Lotus1-2-3公式】：......
2021-12-29 21:28:34 496 excel公式技巧 excel函数公式 Excel常用函数 excel教程
用普通的传统【插入】——【数据透视表】方法制作的数据透视表，虽然有“刷新”功能，但如果在数据源添加了数据行或者列，也不能实现数据透视表的动态更新，如下动：我们可以利用OFFSET函数定义数据源区域名称，实现数据透视表动态更新。关键操作第一步：定义名称【公式】——【定义名称】：引用位置内输入：......
2021-12-29 21:28:31 441 excel公式技巧 excel函数公式 Excel常用函数 excel教程
朋友传来如下数据：其中四列“地区”中，名称与排序都不尽相同，需要保留一列地区名称，把A、B、C、D四种数据并列写到地区列后面，即做成如下结果：关键操作VLOOKUP函数出现错误值如果仅仅用VLOOKUP函数，会出现错误值：在C2单元格内输入公式=VLOOKUP(A2,$E$2:$F$35......
2021-12-29 21:28:30 209 excel公式技巧 excel函数公式 Excel常用函数 excel教程
某公司进行员工考核，数据录入不规范，部分分数带有数量单位“分”。现需要计算员工平均考核分数。解决过程第一步：统一去单位：数量单位“分”，是文本，不能参与计算。所以，在写公式时，首先要把单位去除。去除单位文本用SUBSTITUTE函数：{=SUBSTITUTE(B2:B10,'分',)}，因为是数......
2021-12-29 21:28:28 99 excel公式技巧 excel函数公式 Excel常用函数 excel教程
很多时候，数据输入并不规范，比如下表中的员工姓名和业绩挤在一个单元格里，要求统计业绩最大值。这种不规范的数据并不是不能统计，只是给统计带来了麻烦。公式实现在C2单元格输入公式：{=MAX((SUBSTITUTE(B2,ROW($1:$100),)B2)*ROW($1:$100))}，......
2021-12-29 21:28:27 489 excel公式技巧 excel函数公式 Excel常用函数 excel教程
以下两列身份证号码，绝大部分是相同的，只有小部分数据不同，且两列的排序不同。现要求标识两列中不相同的数据。关键操作1、选中A2:A21区域，[开始]——[条件格式]——[新建规则]：2、选择规则类型为[使用公式确定要设置格式的单元格]，并输入公式：=OR(EXACT(A2,$B$2:$B$21))=......
2021-12-29 21:28:25 186 excel公式技巧 excel函数公式 Excel常用函数 excel教程
有朋友问如何统计两列重复的个数，他想来统计前两个季度销售都进入前一百名的人数。这了好述，把数据改成了统计都进入前十的人数，如下：关键操作公式：在D2单元格输入公式：=COUNT(MATCH(A2:A11,B2:B11,0))，以CTRL+SHIFT+ENTER结果。公式解释：MATCH(......
2021-12-29 21:28:23 193 excel公式技巧 excel函数公式 Excel常用函数 excel教程
尤其打印单位人员名单时，单列数据太长，要做成多行多列适合打印的形式：关键操作公式：在D2单元格输入公式：=INDIRECT('A'&6*ROW(A1)-5+COLUMN(A1))&''，然后向右向下填充，即可变成适合打印的6列多行数据。公式解析：6*ROW(A1)-5+COL......
2021-12-29 21:28:23 252 excel公式技巧 excel函数公式 Excel常用函数 excel教程
一位朋友说遇到难题：公司要求把近三年的销售额按照年份与月份进行汇总，三年的销售数据有四万多行，他问有没有函数可以实现快速统计。用下所示的简单数据，一下汇总方法：关键操作公式：在F2单元格输入公式：=SUMPRODUCT((YEAR($A$2:$A$15)=D2)*(MONTH($A$2:$A$15)......
2021-12-29 21:28:22 196 excel公式技巧 excel函数公式 Excel常用函数 excel教程
身份证号码是个人最重要信息，单位人事部门为了对每个员工信息进行保密，往往在常用的EXCEL工作表里隐藏身份证号码有部分数字，如：函数实现公式：在D2单元格，输入公式：=REPLACE(C3,7,8,'********')，再往下填充，即可隐藏所有身份证号码部分数字。该公式的解释是：对C3单元格的......
2021-12-29 21:28:21 84 excel公式技巧 excel函数公式 Excel常用函数 excel教程
做人事管理的某位朋友，说想要用函数查询单位不同部门员工信息，随意输入单位，所在部门的员工信息会自动查询，结果如下：上中的查询效果是由函数VLOOKUP实现的，过程如下解：第一步：建立辅助列在“部门”前增加一列，在A3单元格输入公式“=COUNTIF($B$3:B3,$H$3)”。其中：$B$3:......
2021-12-29 21:28:18 468 excel公式技巧 excel函数公式 Excel常用函数 excel教程
朋友留言：您好，请教一下怎么用LEFT公式把A列中的工号和员工姓名分开到B列（纯数字）和C列（RIGHT公式，纯中文姓名）？谢谢！数据如下：实现方法第一种方法：函数法第一步先将文本分离出来：在C2单元格输入公式：“=RIGHT(A2,LENB(A2)-LEN(A2))”，向下填充，即可提取所有员工姓......
2021-12-29 21:28:16 136 excel公式技巧 excel函数公式 Excel常用函数 excel教程
今天学习群里一位朋友传来一副图片，问：如何把分布不规则的字母部分单独提取出来？像这种字母、汉字、数字无规律分布的内容，如果想把字母、汉字、数字单独提取出来，利用公式，是非常难的。下面，教大家一种Excel与Word双剑合璧的方法，简单方便，一学即会！提取所有字母将A1：A3单元格的内容复制到空白WORD......
2021-12-29 21:28:15 423 excel公式技巧 excel函数公式 Excel常用函数 excel教程
VLOOKUP函数要求查询值必须位于查询区域的首列。比如，下中的数据：原数据区，“部门”位于“姓名”的左侧，而要求按照姓名去查询部门，那直接用VLOOKUP函数进行查找，是查不到结果的。我们需要构建一个新的查询数据区，将“姓名”置于“部门”的左侧。这种新的查询数据区，可以通过IF和CHOOSE两个函数来实现......
2021-12-29 21:28:13 292 excel公式技巧 excel函数公式 Excel常用函数 excel教程
Excel没有提供忽略隐藏列汇总的函数，比如下：虽然E列与G列隐藏了，但E、G两列的数据还是参与了求和汇总。如何使隐藏列的数据不参与汇总呢？效果如下动：方法解释第一步：建立辅助行在第11行建立辅助行，B11中输入函数“=CELL('width',B1)”，向右填充，计算出B列到G列各列的列宽，如......
2021-12-29 21:28:11 119 excel公式技巧 excel函数公式 Excel常用函数 excel教程
今天朋友在根据身份证号码筛选员工信息的时候出现错误：同一个身份证号码，高级筛选出好几位员工，错误出现过程如下动：这是什么原因？问题解析原因分析高级筛选的结果如下：分析结果，我们可以看出：筛选出的三位员工，他们的身份证号码的前15位是一致的。原因是Excel对同一串数字的有效辨识，仅限于15位......
2021-12-29 21:28:09 418 excel公式技巧 excel函数公式 Excel常用函数 excel教程
上一篇文章讲了，VLOOKUP函数区间查找、等级评定、模糊查找，其中，区间查找折扣率的公式如下：今天，就有朋友说：，公司给我的折扣表是这个样子的，怎么区间查找折扣率：像这种查找值与返回值横向分布的情况，用行查找函数HLOOKUP。公式实现在C2单元格输入公式：=HLOOKUP(B2,$F$......
2021-12-29 21:27:58 390 excel函数 excel函数公式 excel表格制作 excel教程
今天朋友发给我一张数据表，模拟他们公司系统导出的产品销量数据，如下：要统计销售量最大的部门名称、部门主管、销售员名称、销售量、月份等信息，也就是提取最大销售量所在整列的信息。这个问题，可以用OFFSET函数解决。公式实现在B9单元格输入公式：=OFFSET($A$2,ROW(A1)-1,MATC......
2021-12-29 21:27:57 366 excel函数 excel函数公式 excel表格制作 excel教程
只查找第一列的信息，在我们单位是不够的，我们需要查找前三名的数据进行分析，怎么用公式实现？我们只需把昨天公式中的最大值函数换成LARGE函数就好了。公式实现在B9单元格输入公式：=OFFSET($A$2,ROW($A1)-1,MATCH(LARGE($B$5:$K$5,COLUMN(A1)),$B$5:......
2021-12-29 21:27:56 271 excel函数 excel函数公式 excel表格制作 excel教程
有朋友留言：“我的数据很多，里面有多种不同商品，核对数据费眼力，能不能实现我选哪种商品，哪种商品就高亮显示？”示例结果如下：其实这个方法很简单：条件格式。方法实现1、选中数据，【开始】——【条件格式】——【新建规则】：2、在【新建格式规则】对话框中选择【使用公式确定要设置格式的单元格】......
2021-12-29 21:27:54 500 excel函数 excel函数公式 excel表格制作 excel教程
做仓库管理的一位朋友提出的问题：他经常发表格到各个分仓库，让管理员填写商品数据，可：交上来的数据填写很不规范，时常会多出很多空白单元格，或者是少填写某项数据，给后续数据统计工作带来很多麻烦。问：能不能限定填写的时候不能隔行隔列填写？用数据验证（数据有效性）可以解决这个问题。效果如下:实现方法1、......
2021-12-29 21:27:52 276 excel函数 excel函数公式 excel表格制作 excel教程
今天，有位朋友需要按日期统计产品销量，可是，系统导出的日期与时间是在同一个单元格，不好统计，需要将日期与与时间分离。来介绍三种日期与时间分离的方法。方法一：分列选中数据，【数据】——【分列】，如下动图：分列后的日期与时间格式，可以自定义。方法二：INT函数在B2单元格输入公式：=INT(......
2021-12-29 21:27:50 364 excel函数 excel函数公式 excel表格制作 excel教程
一位做人事管理的朋友，核对本单位员工信息，要求信息不完整的要补充完整。他们单位上千名员工，核对起来很是费劲。她问：有没有方便的办法，快速查到哪些员工信息不完整？一、定位空值CTRL+A全选数据，CTRL+G打开“定位”窗口，选择“空值”，数据区域内所有空单元格被选中，填充一种醒目的颜色即可。过程如下动图......
2021-12-29 21:27:48 322 excel函数 excel函数公式 excel表格制作 excel教程
一位朋友问一个图表制作问题：怎么根据下面的数据：制作这样一张产品对比图表：做出这张图表，既能不同产品同月比较，也可同种产品各比较，而且结果清晰可见。实现步骤第一步：添加辅助列分别在1月2月3月4月5月后添加一辅助列，辅助列内的数据为200减原来的数据。此处取数值200，是因为六个月销......
2021-12-29 21:27:46 205 excel函数 excel函数公式 excel表格制作 excel教程
