老師如何有效學(xué)習(xí)Excel?一文概括成績(jī)單中的函數(shù)問題!
?
作者:E圖表述來源:部落窩教育發(fā)布時(shí)間:2022-04-13 17:27:27點(diǎn)擊:1508
編按:
今天的文章是一篇概述性的內(nèi)容,針對(duì)一張成績(jī)表,講解一系列的函數(shù)操作。雖然都不難,但是我們可以從中學(xué)到許多思路,希望大家能從中“溫故知新”吧!
(模擬數(shù)據(jù)源,這班學(xué)生偏科的厲害)
數(shù)據(jù)源很常見。
需求也很“簡(jiǎn)單且樸實(shí)”——要做全套的數(shù)據(jù)統(tǒng)計(jì)。
STEP 1 排名
在G2單元格輸入函數(shù)=RANK(F2,$F$2:$F$13),下拉填充至G13單元格。
實(shí)際上RANK函數(shù)是有三個(gè)參數(shù),第三個(gè)參數(shù)是“升序排序、降序排序”
如果我們不填寫,則默認(rèn)為0降序排序,需要的話,也可以在函數(shù)中輸入1進(jìn)行升序排序。
STEP 2 中國(guó)式排名
上面的排名明顯有一個(gè)問題,有兩個(gè)第一名,卻沒有第二名,所以我們要調(diào)整同分?jǐn)?shù)不占名次的問題。
在G2單元格輸入函數(shù):
{=SUM(IF($F$2:$F$13>F2,1/COUNTIF($F$2:$F$13,$F$2:$F$13)))+1},下拉填充至G13單元格。
這是全篇最不好理解的一個(gè)函數(shù),使用數(shù)組函數(shù),判斷數(shù)據(jù)列表中有多少是大于F2單元格值的,如果大于則用1/COUNTIF的技巧,標(biāo)記序號(hào),如果有重復(fù)的,則如下圖標(biāo)記為0.5,最后再用SUM將這些數(shù)值相加既得F2數(shù)值的排序。
STEP 3 不重復(fù)排序
中國(guó)式排名的內(nèi)涵,就是如果有同分情況,則比例第N名,下一個(gè)分?jǐn)?shù)則為N+1名次。但是這樣的話,還是會(huì)有重復(fù)排名,那我們?cè)賮硪粋€(gè)“不重復(fù)排序”的函數(shù)吧。
在單元格H2輸入函數(shù)=RANK(F2,$F$2:$F$13)+COUNTIF($F$2:F2,F2)-1,下拉填充至H13單元格。
STEP 4 動(dòng)態(tài)按總分排列成績(jī)單
STEP3的作用就是這樣了,可以讓我們動(dòng)態(tài)的按照H列的排序引出排序明細(xì)。這里用了經(jīng)典的VLOOKUP函數(shù)的反向查詢。
在J2單元格輸入函數(shù)=VLOOKUP(ROW(J1),IF({1,0},$H$2:$H$13,$B$2:$B$13),2,0)
在K2單元格輸入函數(shù)=VLOOKUP(ROW(J1),IF({1,0},$H$2:$H$13,$F$2:$F$13),2,0)
STEP 5 按照J、K列做一個(gè)圖表
選中J1:K13單元格區(qū)域,按ALT+F1快捷鍵,就可以快速的創(chuàng)建一個(gè)總分排名的圖表,再加上數(shù)據(jù)標(biāo)簽,齊活兒!
STEP 6 各科平均分
很直白的函數(shù),在單元格C15輸入函數(shù)=AVERAGE(C2:C13),右拉填充至F15單元格即可。
STEP 7 及格率和優(yōu)秀率
假定及格線是60及以上,優(yōu)秀率是85及以上。
在單元格C16輸入函數(shù){=SUM((C2:C13>=60)*1)/COUNT(C2:C13)},右拉填充至E16單元格
在單元格C17輸入函數(shù){=SUM((C2:C13>=85)*1)/COUNT(C2:C13)},右拉填充至E17單元格
這里我們利用邏輯值求和技巧,再除以總體樣本數(shù)。純粹的TRUE或者FALSE是不能被SUM求和的,但是可以進(jìn)行四則運(yùn)算,所以用了“*1”之后就可以轉(zhuǎn)化成1或者0,再用SUM就OK了。
STEP 8 前N名、后N名
在單元格C19輸入函數(shù)=LARGE(C$2:C$13,ROW(C1)),利用ROW函數(shù)得到第N大的序號(hào),配合相對(duì)飲用固定行號(hào),下拉再右拉填充至E21單元格,就得到了相對(duì)于各科的前三甲。
與LARGE相對(duì)應(yīng)的函數(shù)是SMALL函數(shù),引用數(shù)據(jù)列表中的第N小的值,在C22單元格輸入函數(shù)=SMALL(C$2:C$13,ROW(C1)),原理同上。
本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。 做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你! 掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦: 如何計(jì)算兩個(gè)日期間的工作日天數(shù)?超實(shí)用的5類日期函數(shù)來了! 超級(jí)經(jīng)典的8個(gè)函數(shù)組合,解決70%工作中的函數(shù)難題 Power Query的數(shù)據(jù)替換技巧比Excel函數(shù)更萬能! 版權(quán)申明: 本文作者E圖表述;同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!