贊助廠商

學不完.教不停.用不盡文章列表

前二篇文章提到了在 Excel 中的排名問題:

Excel-使用RANK.EQ和RANK.AVG處理排名問題

Excel-重覆名次不跳過

如果現在要根據分組的人員分數,來求各組人員的名次,該如何處理?

Excel-計算分組的名次(SUMPRODUCT)

 

【公式設計與解析】

這個問題無法使用 RANK 函數來直接求取名次,但可以使用 SUMPRODUCT 函數來模擬名次。

儲存格D2:=SUMPRODUCT(($B$2:$B$21=B2)*($C$2:$C$21>C2))+1

公式中使用兩個條件:

$B$2:$B$21=B2:判斷在『組別』陣列中是否和儲存格B2相同,傳回 TRUE/FALSE 陣列。

$C$2:$C$21>C2:判斷在『分數』陣列中是否大於儲存格C2,傳回 TRUE/FALSE 陣列。

公式中的『*』運算,相當於執行兩個條件的邏輯 AND 運算。計算過程中會將 TRUE/FALSE 轉換為 1/0。

最後記得要將 SUMPRODUCT 函數傳回值再加 1,意思是例如大於自己的有 2 個,自己是第 3 名。該公式的結果相當於使用 RANK.EQ 函數的運算結果。

vincent 發表在 痞客邦 留言(0) 人氣()

有同仁在 Excel 中使用「進階篩選」時,想要將[基本資料]工作表中的資料篩選至[篩選結果]工作表中,於操作過程中遇到一些的問題。(參考下圖)

其步驟為:

1. 目前被選取的是[基本資料]工作表的儲存格A1,選取「進階篩選」。

2. 在[進階篩選]對話框中填入「資料範圍」和「篩選範圍」。這兩個範圍都在[基本資料]工作表中。

3. 因為勾選了「將篩選結果複製到其他地方」,所以在「複製到」文字方塊中選取了另一個工作表的儲存格,例如:篩選結果!$A$1:$C$1。

Excel-如何解決將工作表A的內容篩選至工作表B發生的錯誤

4. 當按下[確定]按鈕時,出現了以下的錯誤訊息:只能在使用中的工作表上複製篩選範圍。

Excel-如何解決將工作表A的內容篩選至工作表B發生的錯誤

如何解決這個問題?以上的錯誤訊息是指:篩選的結果要放在作用中的工作表。

調整以下的步驟。(參考下圖)

1. 先調整作用中工作表為[篩選結果]工作表,再選取「進階篩選」。

2. 在[進階篩選]對話框中填入「資料範圍」和「篩選範圍」。這兩個範圍都在指定到[基本資料]工作表中的儲存格範圍。

3. 因為勾選了「將篩選結果複製到其他地方」,所以在「複製到」文字方塊中選取[篩選結果]工作表的儲存格,例如:篩選結果!$A$1:$C$1。

Excel-如何解決將工作表A的內容篩選至工作表B發生的錯誤

結果如下:

Excel-如何解決將工作表A的內容篩選至工作表B發生的錯誤

結論是:

如果你要將工作表A的內容篩選至工作表B,則應在工作表B中開始操作進階篩選。若在工作表A中開始進階篩選程序,則會發生錯誤。

vincent 發表在 痞客邦 留言(0) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼