不拆分區(qū)間數(shù)據(jù)如何進行階梯式計算
?
作者:E圖表述來源:部落窩教育發(fā)布時間:2021-05-13 10:32:38點擊:2715
編按:
之前介紹過的對“區(qū)間數(shù)據(jù)”的操作辦法,都是要將數(shù)據(jù)的起止范圍值分成起始值和終止值,并分別錄入兩個單元格內(nèi)。但如果不拆分這類區(qū)間數(shù)據(jù),其實可以使用兩個冷門函數(shù)來巧妙解決:IMREAL函數(shù)和IMAGINARY函數(shù)!下面,小E將用階梯式提成計算的實用案例對函數(shù)的應(yīng)用進行詳細剖析……
【前言】
Z=a+bi(或者Z=a+bj),這種格式的數(shù)學(xué)表達式,被稱作“復(fù)數(shù)”。它代表了一組二元有序的實數(shù)對(a,b),其中加號兩邊a為“實部”,bi(或者bj)為“虛部”;實數(shù)a為復(fù)數(shù)實部的實數(shù),b為復(fù)數(shù)虛部的實數(shù),i(或者j)稱為虛部系數(shù)。這是高中的代數(shù)知識,姑且不論復(fù)數(shù)的現(xiàn)實意義,我們借此話題來引出今天的兩個函數(shù),和這兩個函數(shù)在工作中的實用價值。
【正文】
IMREAL函數(shù)
語法:IMREAL(INUMBER)
參數(shù)只有一個,就是INUMBER,NUMBER是數(shù)值,INUMBER就是復(fù)數(shù)的意思。
函數(shù)意義:返回以 x+yi 或 x+yj 文本格式表示的復(fù)數(shù)的實系數(shù)(即x值)。
用法舉例:
IMAGINARY函數(shù)
語法:IMAGINARY(INUMBER)
IMREAL函數(shù)的參數(shù)也只有一個,也是INUMBER(復(fù)數(shù))
函數(shù)意義:返回以 x+yi 或 x+yj 文本格式表示的復(fù)數(shù)的虛系數(shù)。(即:y值)
用法舉例:
【工作實例】
存在即合理,這兩個函數(shù)對于和數(shù)學(xué)教研有關(guān)系的工作來說毋庸置疑是有意義。對于在企業(yè)、商業(yè)性質(zhì)公司就職的同學(xué)們來說,一樣也可以使用這兩個函數(shù)處理問題,如下例。
某公司的銷售提成是按照銷售單量進行的提成,A1:B11單元格區(qū)域是提成算法的參數(shù)表,要求員工在D3:G7單元格區(qū)域,根據(jù)每個人的銷售單量計算提成單價和提成額。
問題解析:
這個數(shù)據(jù)的難點是A1:B11區(qū)域的參數(shù)表寫法:“0-100”、“301-400”,都是文本字符串。
在之前的文章中介紹過“區(qū)間取值”的問題(當時給了相當多的解決方案),都是要將數(shù)據(jù)的“起止范圍值分開到兩個單元格內(nèi)”,但如果不拆分區(qū)間數(shù)據(jù),其實可以使用IMREAL函數(shù)來處理。如下:
在F4單元格輸入函數(shù):
{=VLOOKUP(E4,IF({1,0},IMREAL($A$2:$A$11&"i"),$B$2:$B$11),2,1)}
首先,A2:A11單元格的內(nèi)容連接一個“i”,形成“復(fù)數(shù)”,即"0-100i";"101-200i";"201-300i";"301-400i";"401-500i";"501-600i";"601-700i";"701-800i";"801-900i";"901-1000i"。(ps:用“j”也可以,這里需要主要,按照數(shù)學(xué)表達式的規(guī)定,只可以用小寫字母“i或者j”,其他字母都不可以,大寫字母也不可以),然后使用IMREAL提取實系數(shù)組成的數(shù)列,如下:
剩下的就是VLOOKUP+IF{1,0}的組合使用問題,因咱們部落窩對于此問題的文章教程很多,筆者就不在此贅述了。函數(shù)錄入完,使用CTRL+SHIFT+ENTER組合鍵轉(zhuǎn)成數(shù)組函數(shù)即可。最后的提成額使用“單量*單價”即可,完成后如下圖:
【問題延伸】
還是這個問題,我們把問題難度加大一下,看看IMREAL函數(shù)是不是還可以繼續(xù)使用,如下:
這種提成方式其實也不是筆者故意加大解題難度,現(xiàn)實工作中此種提成計算方法比比皆是——“階梯算法”。
在G4單元格輸入函數(shù):
{=SUM(TEXT(E4-TEXT(IMREAL($A$2:$A$11&"i")-1,"0;!0;"),"[>100]!1!0!0;[<0]!0;0")
*$B$2:$B$11)}
函數(shù)解析:
STEP1:
因為參數(shù)表中給出每檔的起始值是:0,101,201,301……,所以我們用IMREAL函數(shù)提取出實系數(shù)后再減去1,得到數(shù)列{-1,100,200,300,……}
STEP2:
使用TEXT函數(shù)的條件判斷方法將小于0的值強制顯示為0
STEP3:
再使用TEXT函數(shù)的條件判斷方法,判斷E4單元格的單量值減去數(shù)列的區(qū)間,如果大于0,強制返回100(!1!0!0);小于0,返回0;等于0,就返回差值。在此,可以先看一下到這一步的時候,函數(shù)的數(shù)列形成了什么,如下圖:
STEP4:
這個數(shù)列再乘以B2:B11單元格的提成單價,得到下面的數(shù)列,如圖:
STEP5:
再使用SUM函數(shù)計算總提成額,最后使用CTRL+SHIFT+ENTER組合鍵將函數(shù)轉(zhuǎn)成數(shù)組函數(shù)。結(jié)果如下:
【編后語】
EXCEL2016版的函數(shù)大概有400多個,筆者自認為常用函數(shù)是比較多的,也差不多就只有100多個,可以說還有很大比例的函數(shù)內(nèi)容還沒有使用過。既然設(shè)計了這個函數(shù)就一定會有它存在的道理,活學(xué)活用、善學(xué)善用是我們一直前行進步的動力。
本文配套的練習(xí)課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
腦洞大開!7種另類的區(qū)間取值的方法,打開你新世界的大門!
比VLOOKUP重要,更容易讓你晉升高手的函數(shù),就包含在這三大經(jīng)典嵌套公式中
13個最常用的Excel數(shù)學(xué)函數(shù)和三角函數(shù)公式,SIGN函數(shù)等
版權(quán)申明:
本文作者E圖表述;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!