多個數(shù)值在同一單元格內(nèi)的乘積,用PQ批量計算
?
作者:阿碩來源:部落窩教育發(fā)布時間:2021-07-13 10:49:05點(diǎn)擊:3162
編按:
一個單元格內(nèi)有2個或3個或多個數(shù)值,怎么批量核算乘積呢?上千行的數(shù)據(jù),每個單元格的數(shù)值數(shù)量也不盡相同,不可能一個個計算。其實,這是很簡單的數(shù)值計算問題,不該被不規(guī)范的數(shù)據(jù)表格給難住了。下面,小E給大家講一講用PQ來實現(xiàn)批量核算的方法……
最近,有小伙伴問了這樣一個問題:單元格內(nèi)的數(shù)據(jù)是帶乘號的數(shù)據(jù),想要計算這些數(shù)據(jù)的乘積,有什么辦法呢?
數(shù)據(jù)如下圖所示,A2至A9中的數(shù)據(jù)都是“數(shù)字*數(shù)字”(2個數(shù)據(jù)相乘)或者“數(shù)字*數(shù)字*數(shù)字”(3個數(shù)據(jù)相乘)的這種格式。
因為多個數(shù)值在同一單元格內(nèi),每個單元格的數(shù)值數(shù)量又不相同,這就讓很簡單的運(yùn)算也變得困難起來。公司領(lǐng)導(dǎo)30分鐘以后就要,核算結(jié)果關(guān)系到公司的切實利益,如果做差了,后果不堪想象。小伙伴變得急切起來……
其實,對于這樣的數(shù)據(jù),計算乘積的常用方法有三種:
1.利用連接函數(shù)(&),在數(shù)據(jù)前面加上某個字符,然后再批量把這個字符替換成等號,就可以自動計算乘積;
2.利用宏表函數(shù)Evaluate定義名稱,然后調(diào)用這個名稱來計算乘積;
3.利用分列功能,將數(shù)據(jù)拆分后,再計算乘積。
感興趣的小伙伴們,可以在咱們公眾號搜索一下,可以找到相關(guān)教程。
今天,學(xué)習(xí)一個新的方法——PQ技巧。
利用PQ來處理這種數(shù)據(jù),主要的思路就是先拆分?jǐn)?shù)據(jù),再計算乘積。和分列有相似之處,但比之簡單多了!本例中,涉及到的操作主要有拆分列、替換值、添加自定義列等,下面大家一起來學(xué)習(xí)一下具體的操作!
Step 1 拆分乘號
點(diǎn)中A1:A9中的任意一個單元格,如A6,然后依次點(diǎn)擊“數(shù)據(jù)”-“來自表格/區(qū)域”,彈出“創(chuàng)建表”對話框,如下圖所示。
勾選“表包含標(biāo)題”(如已勾選則保持其不變即可),點(diǎn)擊“確定”,即可進(jìn)入PQ編輯器界面,如下圖所示。
用鼠標(biāo)選中“數(shù)據(jù)”這一列(注:若該列底色為淺綠色則意味著該列已被選中),依次點(diǎn)擊“轉(zhuǎn)換”-“拆分列”-“按分隔符”,如下圖所示。
彈出“按分隔符拆分列”對話框后,在“選擇或輸入分隔符”下方的下拉列表中,讓默認(rèn)的“自定義”保持其不變;在“自定義”下方的輸入框中,PQ自動判斷出了需要拆分的分隔符號——“*”(即乘號),同樣保持其不變即可,如下圖所示。
用鼠標(biāo)點(diǎn)擊“確定”后,得到的數(shù)據(jù)拆分后的結(jié)果如下圖所示。
可以看到,經(jīng)過拆分,原始數(shù)據(jù)已經(jīng)由一列變成了三列,字段名稱分別為“數(shù)據(jù).1”、“數(shù)據(jù).2”、“數(shù)據(jù).3”。原始數(shù)據(jù)中如果是3個數(shù)據(jù)相乘的,則新拆分出來的三列分別為相應(yīng)的三個數(shù)據(jù),如上圖中第3、5、6、7、8行所示;原始數(shù)據(jù)中如果是2個數(shù)據(jù)相乘的,則新拆分出來的是兩列數(shù)字,外加第三列為“null”(即空值),如第1、2、4行所示。
Step 2 替換“null”值
接下來,大家再將“null”替換一下。由于在乘法運(yùn)算中,數(shù)據(jù)乘以1后,得到的結(jié)果不變,所以本例中,將“null”替換為“1”即可。
用鼠標(biāo)選中“數(shù)據(jù).3”這一列,依次點(diǎn)擊“轉(zhuǎn)換”-“替換值”-“替換值”,如下圖所示。
在彈出的“替換值”對話框中,在“要查找的值”下方的輸入框中,輸入“null”;在“替換為”下方的輸入框中,輸入“1”。
用鼠標(biāo)點(diǎn)擊“確定”后,即可將“數(shù)據(jù).3”這一列中所有的“null”替換為“1”。
Step 3 計算乘積
依次用鼠標(biāo)點(diǎn)擊“添加列”-“自定義列”,彈出“自定義列”對話框。
在“自定義列”對話框中,將“新列名”下方的“自定義”,改為“乘積”;在“自定義列公式”下方的函數(shù)輸入框中,在等號后面輸入“[數(shù)據(jù).1]*[數(shù)據(jù).2]*[數(shù)據(jù).3]”,如下圖所示。
用鼠標(biāo)點(diǎn)擊“確定”后,PQ中生成了一個叫做“乘積”的列,如下圖所示。
接下來,再對數(shù)據(jù)進(jìn)行兩個小的處理 :
1. 將“乘積”列的數(shù)據(jù)類型修改為小數(shù)類型。
具體操作:用鼠標(biāo)點(diǎn)擊“乘積”前面的“ABC123”,在彈出的界面中,選擇“1.2 小數(shù)”,即可。本步驟得到的結(jié)果如下。
2. 刪除 “數(shù)據(jù).1”、“數(shù)據(jù).2”、“數(shù)據(jù).3”這三列(注:如果這三列在實際應(yīng)用中有需要的話,則不必刪除)。
具體操作為:選中“乘積”這一列,單擊鼠標(biāo)右鍵,選擇“刪除其他列”,即可。本步驟得到的結(jié)果如下圖所示。
Step 4 上載數(shù)據(jù)
用鼠標(biāo)依次點(diǎn)擊“主頁”-“關(guān)閉并上載”-“關(guān)閉并上載至”,如下圖所示。
在彈出的“導(dǎo)入數(shù)據(jù)”對話框中,將“數(shù)據(jù)的存放位置”選擇為“現(xiàn)有工作表”,然后通過拾取器,選擇當(dāng)前工作表的B1單元格。
最后,用鼠標(biāo)點(diǎn)擊“確定”,數(shù)據(jù)即可上載至當(dāng)前工作表中的B1:B9區(qū)域,如下圖所示。
小伙伴們,是不是很簡單?你學(xué)會了嗎?
本文配套的練習(xí)課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
Excel運(yùn)用規(guī)范1:一個單元格只記錄一條信息
如何精確提取單元格內(nèi)不同屬性的數(shù)據(jù)
按指定次數(shù)重復(fù)數(shù)據(jù)就用PQ!
版權(quán)申明:
本文作者阿碩;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!