国产探花免费观看_亚洲丰满少妇自慰呻吟_97日韩有码在线_资源在线日韩欧美_一区二区精品毛片,辰东完美世界有声小说,欢乐颂第一季,yy玄幻小说排行榜完本

首頁(yè) > 辦公 > Excel > 正文

Excel:重復(fù)名次也可以查姓名成績(jī)

2019-12-13 16:56:51
字體：大 中 小
來(lái)源：轉(zhuǎn)載
供稿：網(wǎng)友
當(dāng)老師的，對(duì)分析學(xué)生成績(jī)大概有癮。這不，本來(lái)我們已經(jīng)把學(xué)生各學(xué)科的成績(jī)、總分、名次都排出來(lái)了，并按照總分進(jìn)行了升序排序，但現(xiàn)在又有任課老師過(guò)來(lái)要求希望能夠把自己學(xué)科的前10名的學(xué)生姓名及成績(jī)找出來(lái)。按理說(shuō)，這個(gè)要求并不是很困難，但是麻煩就在于學(xué)生各科名次有可能相同，這樣的話，前10名的學(xué)生其實(shí)不一定是10個(gè)人，有可能更多。每個(gè)學(xué)科都要這么做的話，工作量也不小，所以，還是得靠函數(shù)和公式來(lái)幫忙。

用Excel巧妙實(shí)現(xiàn)重復(fù)名次也可以查姓名成績(jī)_武林網(wǎng)轉(zhuǎn)載
圖1 原始成績(jī)表
　　原始的成績(jī)表如圖1所示。姓名位于C2:C92單元格，語(yǔ)文成績(jī)位于D2:D92單元格區(qū)域。我們就以查找語(yǔ)文學(xué)科的前10名成績(jī)及學(xué)生姓名為例。為方便比較結(jié)果，圖1中我們已經(jīng)將數(shù)據(jù)按語(yǔ)文成績(jī)降序進(jìn)行了排序，實(shí)際操作中是不需要事先排序的。
　　一、名次表的建立
　　前面我們說(shuō)過(guò)，我們不太容易確定排在前10名的學(xué)生共有多少，所以，我們需要使用公式將它們找出來(lái)。當(dāng)然，最好順便將名次表填寫(xiě)出來(lái)。完成結(jié)果如圖2所示。

用Excel巧妙實(shí)現(xiàn)重復(fù)名次也可以查姓名成績(jī)_武林網(wǎng)轉(zhuǎn)載
圖2 成績(jī)排序
　　將鼠標(biāo)定位于X3單元格，然后在編輯欄輸入公式“=TEXT(SUMPRODUCT(($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))/COUNTIF($D$2:$D$92,$D$2:$D$92)),"第G/通用格式名")”，回車(chē)后就可以得到“第1名”的結(jié)果。選定X3單元格，向下拖動(dòng)其填充句柄至出現(xiàn)“第11名”為止。
　　這里用到了幾個(gè)函數(shù)，感覺(jué)上比較復(fù)雜。其實(shí)思路是這樣的：“ROW(1:1)”的結(jié)果是“1”，而“LARGE($D$2:$D$92,1)”的結(jié)果是在指定的單元格區(qū)域中最大的一個(gè)數(shù)；那么公式中“($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))”可以理解為拿D2:D92單元格區(qū)域中的數(shù)據(jù)與該區(qū)域中最大值比較，大于或等于該值及小于該值的則會(huì)分別以“TRUE”、“FALSE”的結(jié)果保存在一個(gè)數(shù)組中。
　　公式中“COUNTIF($D$2:$D$92,$D$2:$D$92))”部分則會(huì)統(tǒng)計(jì)D2:D92單元格區(qū)域中每一個(gè)數(shù)值出現(xiàn)的次數(shù)，也分別保存到一個(gè)數(shù)組中。所以，我們所用公式中“SUMPRODUCT(($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))/COUNTIF($D$2:$D$92,$D$2:$D$92))”在執(zhí)行時(shí)會(huì)得到一個(gè)類(lèi)似于“SUMPRODUCT({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;…}/{1;1;2;2;1;2;2;1;2;2;2;2;1;…})”的結(jié)果。兩個(gè)數(shù)組中的對(duì)應(yīng)的數(shù)據(jù)分別相除，再將所有的商相加，正是分?jǐn)?shù)所對(duì)應(yīng)的名次。這種方法即使名次是并列的，也不會(huì)影響顯示效果。
　　至于最外層的TEXT函數(shù)，則是將得到的結(jié)果轉(zhuǎn)換為按指定數(shù)字格式表示的文本。也就是本來(lái)內(nèi)層公式運(yùn)算的結(jié)果是數(shù)字“1”，現(xiàn)在我們將它顯示為“第1名”。
　　二、分?jǐn)?shù)的查找
　　將鼠標(biāo)定位于Y3單元格，在編輯欄中輸入如下公式“=INDEX($D$2:$D$92,MATCH(LARGE($D$2:$D$92 1/ROW($D$2:$D$92),ROW(1:1)),$D$2:$D$92 1/ROW($D$2:$D$92),0))”，然后按下“Ctrl Shift Enter”快捷鍵，完成數(shù)組公式的輸入。這一步很關(guān)鍵的，否則不會(huì)出現(xiàn)正確的結(jié)果。
　　向下拖動(dòng)Y3單元格的填充句柄向下至最后一個(gè)單元格完成公式的復(fù)制。
　　我們還是簡(jiǎn)單解釋一下公式的思路。
　　由于D2:D92區(qū)域中有很多數(shù)據(jù)是重復(fù)的，這給我們?cè)斐闪死щy。所以，我們要想辦法使每一數(shù)據(jù)都變成唯一。公式中“$D$2:$D$92 1/ROW($D$2:$D$92)”就是給D2:D92區(qū)域中每一個(gè)數(shù)據(jù)都加了該數(shù)據(jù)對(duì)應(yīng)行數(shù)的倒數(shù)。由于每一數(shù)據(jù)對(duì)應(yīng)的行數(shù)是不一樣的，這樣，就會(huì)使每一數(shù)據(jù)都變成了唯一的值，并保存到了一個(gè)數(shù)組中。
　　公式中的“LARGE($D$2:$D$92 1/ROW($D$2:$D$92),ROW(1:1))”還是返回了上面所得數(shù)組中的最大值。本例中的結(jié)果是“｛96.5｝”。
　　公式中“MATCH(LARGE($D$2:$D$92 1/ROW($D$2:$D$92),ROW(1:1)),$D$2:$D$92 1/ROW($D$2:$D$92),0)”返回的是剛剛得到的最大值在數(shù)組中的位置。本例中的結(jié)果是“｛1｝”。
　　這樣，其實(shí)Excel最后執(zhí)行的查詢(xún)就是“INDEX($D$2:$D$92,1)”了，自然可以返回在$D$2:$D$92區(qū)域中的第一個(gè)值了。
　　三、姓名的查找
　　將鼠標(biāo)定位于Z3單元格，在編輯欄中輸入公式“=INDEX($C$2:$C$92,MATCH(LARGE($D$2:$D$92 1/ROW($D$2:$D$92),ROW(1:1)),$D$2:$D$92 1/ROW($D$2:$D$92),0))”，同樣按下“Ctrl Shift Enter”快捷鍵完成數(shù)組公式的輸入。
　　向下拖動(dòng)Z3單元格的填充句柄向下至最后一個(gè)單元格完成公式的復(fù)制。最后的效果如圖3所示。

用Excel巧妙實(shí)現(xiàn)重復(fù)名次也可以查姓名成績(jī)_武林網(wǎng)轉(zhuǎn)載
圖3 完成公式的復(fù)制
　　其實(shí)您肯定已經(jīng)明白了，姓名的查找與前面分?jǐn)?shù)的查找是一樣的。公式本身也沒(méi)有什么大的變化。所以，明白了前面的方法，要查找別的什么東西也就方便了。
　　其它的學(xué)科可以照此辦理。只要注意變換一下公式中的單元格區(qū)域就可以了，我這里就不羅嗦了。
發(fā)表評(píng)論 共有條評(píng)論
用戶(hù)名: 密碼:
驗(yàn)證碼: 匿名發(fā)表
主站蜘蛛池模板： 洛浦县| 文安县| 石首市| 探索| 灵川县| 子长县| 辽阳市| 宿松县| 五峰| 壶关县| 花莲县| 崇州市| 行唐县| 绥宁县| 乃东县| 金秀| 南汇区| 德江县| 仁布县| 汉中市| 阳信县| 马龙县| 阳江市| 高州市| 唐河县| 宁德市| 灵璧县| 武清区| 岐山县| 侯马市| 吉安市| 聂荣县| 沐川县| 连山| 榆社县| 丽水市| 上虞市| 东至县| 两当县| 罗江县| 连城县|