Excel輔助列的妙用之九大實用案例(上)
?
作者:小可來源:部落窩教育發(fā)布時間:2021-06-10 11:18:41點擊:4957
編按:
日常工作中,不是每一個人都能迅速掌握各種Excel函數(shù),那么,有什么辦法可以減少復雜函數(shù)的使用,依然達到解決問題的結果呢?試試使用萬能的輔助列吧~ 不管是生成不重復序列、對數(shù)據(jù)分組、求和、編碼,亦或是生成工資表……Excel小白也能用它輕松完成!
函數(shù)雖然更強大,但是對于很多初學者來說,無法理解更無法掌握較難的函數(shù)!所以,大家可以借助輔助列,去更簡單快捷的解決工作中的難題!下面跟小編一起領略輔助列的厲害之處吧~~~
一、隨機生成不重復序列
面試時,抽到的序號順序有可能會影響到你的面試分數(shù),比如大多數(shù)人都不想自己抽到第一個或最后一個,所以為了考試公平起見,需要生成一定范圍數(shù)的隨機不重復序列。
如下,要對面試名單人員隨機生成不重復序列,該怎么操作呢?一起看看吧~
Step 1:建立輔助列,在D列的D2單元格輸入公式
=RANDBETWEEN(1,10)+0.01*ROW()
注意:
因為RANDBETWEEN函數(shù)生成的隨機數(shù)可能重復,所以,隨機數(shù)后面加上“+0.01*ROW()”這部分,就可以確保每次的數(shù)據(jù)都是唯一的。
Tip:大家除了RANDBETWEEN,也可以用RAND()函數(shù)。RAND生成的是1—0的隨機不重復小數(shù),所以這里也可以用RAND函數(shù)生成輔助列,看各位讀者愛好咯!
Step 2:在B列對D列的輔助列數(shù)據(jù)用RANK函數(shù)進行排序,在B2單元格輸入公式
=RANK(D2,$D$2:$D$11)
按
除了用RANK函數(shù)排名外,還可以用MATCH+SMALL函數(shù)嵌套得出隨機序列
=MATCH(D2,SMALL($D$2:$D$11,ROW($1:$10)),0)
按
二、按銷售員對銷售記錄分組
如圖,老板要求把左圖的銷售記錄按銷售員的姓名進行分組做表,最終做成右圖的效果,并且含有表頭。接到這樣的要求你會怎么完成呢,其實靈活運用輔助列便可以輕松搞定!
Step 1:建立輔助列,在F2單元格輸入公式
=SUM(N(MATCH($A$2:A2,$A$2:A2,0)=ROW($1:1)))
按
這個公式是一個數(shù)組公式,為了方便大家理解公式,筆者把MACTH函數(shù)部分的結果顯示放在了H列,把ROW函數(shù)部分的結果顯示放在了I列。
N函數(shù)是excel函數(shù)的信息函數(shù),也是excel中最短的函數(shù)之一,它的作用是將數(shù)值轉(zhuǎn)換成數(shù)字,日期轉(zhuǎn)換成序列值,TRUE轉(zhuǎn)換成1,其它對象轉(zhuǎn)換成0。而這個案例里邊,N函數(shù)的作用是把邏輯值TRUE轉(zhuǎn)換為1,FALSE轉(zhuǎn)換為0,最后SUM函數(shù)求和就可以得到當前所有不重復的“銷售員”的個數(shù)。
Step2:因為由上一步得出有5個不重復的“銷售員”名單,所以需要有四個空行,再添加四行表頭。
如圖,將第一行表頭復制粘貼到A19到E22,在F15到F18依次輸入1.1、2.1、3.1、4.1;在F19到F22輸入1.2、2.2、3.2、4.2。
Step 3:最后一步,見證奇跡的一步到啦!選中F2單元格,先用鼠標向下再向左,選中整個數(shù)據(jù)區(qū)域(這么做的原因是保證F2為活動單元格)。接著選擇【數(shù)據(jù)】選項卡下的【升序】,對選中區(qū)域進行排序。
三、快速隔行求和
如圖,要求對以下銷售數(shù)據(jù)進行隔行求和,也可以說是奇偶行分別求和。用公式有點麻煩,那么輔助列就最適合我們這種懶孩子啦!
Step 1:在C2單元格輸入1,C3單元格輸入2,接著同時選中C2和C3兩個單元格一起向下進行復制填充。
Step 2:對ABC三列添加篩選,點擊C列按鈕,篩選條件勾選“1”;選中B16后,按快捷鍵
四、按不同產(chǎn)品的不同數(shù)量給貨物編批號
按照每個品牌商品的數(shù)量給其編號,最終整理成如圖的“編號”列。
Step 1:建立輔助列,在F2單元格輸入起始值1,在G2單元格輸入“=B2”;在F3單元格輸入公式“=F1+G2”,向下填充公式至F13;在G3單元格輸入公式“=G2+B3”,向下填充公式至G13。
用TEXT函數(shù)設置編號模式,在D2單元格輸入公式:
=TEXT(F2,"WX000")&"-"&TEXT(G2,"WX000")
按
五、輔助列生成生成工資條
1.定位法
Step 1:建立輔助列。在I3和J4單元格輸入數(shù)字1,選擇I3:J4區(qū)域,向下進行復制填充;選中I3:J14單元格,按
Step 2:復制表頭,選中A2:A26數(shù)據(jù),按
2.排序法
首先建立輔助列,在I2到I14單元填充序列1-14,再復制I2:I14的序列到I15:I27區(qū)域;將表頭粘貼復制到A15:H27區(qū)域;然后選中I2單元格,先向下再向右拖動鼠標選至整個A2:I27區(qū)域,點擊【數(shù)據(jù)】選項卡下的【升序】就OK啦!動圖呈上~
今天的輔助列運用暫時分享到這里,希望能給你帶來幫助,讓你就算不會復雜函數(shù),也能輕松解決大量Excel問題!
我們下期見!
本文配套的練習課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關注公眾號,可隨時隨地學習Excel:
相關推薦:
Excel數(shù)據(jù)透視表系列教程第六節(jié):分組問題
版權申明:
本文作者小可;同時部落窩教育享有專有使用權。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復,Excel卻判定數(shù)據(jù)重復,這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!