|
每天一点小技能 职场打怪不得怂  编按：一个单元格内有2个或3个或许多个数值，怎么批量核算乘积？明明是很简单的数值就算问题，却被不规范的数据表格给难住了！上千行的数据，每个单元格的数值数量也不尽相同，不可能一个个计算，那么该怎么办呢？其实，可以用PQ来实现批量核算……  最近，有小伙伴问了这样一个问题：单元格内的数据是带乘号的数据，想要计算这些数据的乘积，有什么办法呢？ 数据如下图所示，A2至A9中的数据都是“数字*数字”（2个数据相乘）或者“数字*数字*数字”（3个数据相乘）的这种格式。 因为多个数值在同一单元格内，每个单元格的数值数量又不相同，这就让很简单的运算也变得困难起来。公司领导30分钟以后就要，核算结果关系到公司的切实利益，如果做差了，后果不堪想象。小伙伴变得急切起来…… 其实，对于这样的数据，计算乘积的常用方法有三种： 1.利用连接函数（&），在数据前面加上某个字符，然后再批量把这个字符替换成等号，就可以自动计算乘积； 2.利用宏表函数Evaluate定义名称，然后调用这个名称来计算乘积； 3.利用分列功能，将数据拆分后，再计算乘积。 感兴趣的小伙伴们，可以在咱们公众号搜索一下，可以找到相关教程。 今天，学习一个新的方法——PQ技巧。 利用PQ来处理这种数据，主要的思路就是先拆分数据，再计算乘积。和分列有相似之处，但比之简单多了！本例中，涉及到的操作主要有拆分列、替换值、添加自定义列等，下面大家一起来学习一下具体的操作！ Step 1 拆分乘号 点中A1:A9中的任意一个单元格，如A6，然后依次点击“数据”－“来自表格/区域”，弹出“创建表”对话框，如下图所示。 勾选“表包含标题”（如已勾选则保持其不变即可），点击“确定”，即可进入PQ编辑器界面，如下图所示。 用鼠标选中“数据”这一列（注：若该列底色为浅绿色则意味着该列已被选中），依次点击“转换”－“拆分列”－“按分隔符”，如下图所示。 弹出“按分隔符拆分列”对话框后，在“选择或输入分隔符”下方的下拉列表中，让默认的“自定义”保持其不变；在“自定义”下方的输入框中，PQ自动判断出了需要拆分的分隔符号——“*”（即乘号），同样保持其不变即可，如下图所示。 用鼠标点击“确定”后，得到的数据拆分后的结果如下图所示。 可以看到，经过拆分，原始数据已经由一列变成了三列，字段名称分别为“数据.1”、“数据.2”、“数据.3”。原始数据中如果是3个数据相乘的，则新拆分出来的三列分别为相应的三个数据，如上图中第3、5、6、7、8行所示；原始数据中如果是2个数据相乘的，则新拆分出来的是两列数字，外加第三列为“null”（即空值），如第1、2、4行所示。 Step 2 替换“null”值 接下来，大家再将“null”替换一下。由于在乘法运算中，数据乘以1后，得到的结果不变，所以本例中，将“null”替换为“1”即可。 用鼠标选中“数据.3”这一列，依次点击“转换”－“替换值”－“替换值”，如下图所示。 在弹出的“替换值”对话框中，在“要查找的值”下方的输入框中，输入“null”；在“替换为”下方的输入框中，输入“1”。 用鼠标点击“确定”后，即可将“数据.3”这一列中所有的“null”替换为“1”。
Step 3 计算乘积 依次用鼠标点击“添加列”－“自定义列”，弹出“自定义列”对话框。
在“自定义列”对话框中，将“新列名”下方的“自定义”，改为“乘积”；在“自定义列公式”下方的函数输入框中，在等号后面输入“[数据.1]*[数据.2]*[数据.3]”，如下图所示。
用鼠标点击“确定”后，PQ中生成了一个叫做“乘积”的列，如下图所示。
接下来，再对数据进行两个小的处理 ： 1.将“乘积”列的数据类型修改为小数类型。 具体操作：用鼠标点击“乘积”前面的“ABC123”，在弹出的界面中，选择“1.2 小数”，即可。本步骤得到的结果如下。
2.删除 “数据.1”、“数据.2”、“数据.3”这三列（注：如果这三列在实际应用中有需要的话，则不必删除）。 具体操作为：选中“乘积”这一列，单击鼠标右键，选择“删除其他列”，即可。本步骤得到的结果如下图所示。
Step 4 上载数据 用鼠标依次点击“主页”－“关闭并上载”－“关闭并上载至”，如下图所示。
在弹出的“导入数据”对话框中，将“数据的存放位置”选择为“现有工作表”，然后通过拾取器，选择当前工作表的B1单元格。
最后，用鼠标点击“确定”，数据即可上载至当前工作表中的B1:B9区域，如下图所示。
小伙伴们，是不是很简单？你学会了吗？教程配套的Excel练习课件，请扫码到微信群下载即可。
Excel教程相关推荐 |
|
|
