2017年10月16日 星期一

Excel-計算一維和二維的數列中不重覆的個數(SUMPRODUCT,COUNTIF)

網友問到:如何在 Excel 中,對於一維和二維的數列,計算不重覆的個數。(如下圖)
Excel-計算一維和二維的數列中不重覆的個數(SUMPRODUCT,COUNTIF)
【公式設計與解析】
1. 一維儲存格
儲存格A2:1/COUNTIF(C1:C24,C1:C24)
複製儲存格A2,貼至儲存格A1:A24。
儲存格B2:=SUMPRODUCT(1/COUNTIF(C1:C24,C1:C24))
透過 SUMPRODUCT 函數讓公式執行陣列的運算,本公式的結果相當於:
SUM(A1:A24)
即三個相同時可得三個『1/3』,其和的結果為『1』。

2. 二維儲存格
儲存格J2:=SUMPRODUCT(1/COUNTIF(F1:J6,F1:J6))
二維儲存格的運算和一維儲存格相同。

2017年10月15日 星期日

Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

本篇文章是前二篇文章的延伸:
如下圖,如何在 Excel 的工作表中,找出分區各自的最大值所在的儲存格位址?
在下圖中,可以指定每一個分區有幾列,並且於I欄中標示每列的最大值(每列有7個儲存格數值)。當找出每一區的最大值(儲存格L3:L15)後,再找出其儲存格位址(儲存格M4:O15)。
Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)
【公式設計與解析】
1. 產生序號
儲存格K4:=((ROW(1:1)-1)*$L$1+1)&"-"&ROW(1:1)*$L$1
複製儲存格K4,貼至儲存格K4:K15。
ROW(1:1)向下複製公式時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
本例中,當儲存格L6為 6 時,會依序產生1-6、7-12、13-18、…。
Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

2. 找出每區最大值
儲存格L4:=MAX(OFFSET($B$2,(ROW(1:1)-1)*$L$1,0,$L$1,7))
(1) (ROW(1:1)-1)*$L$1
當儲存格L3為3時,公式向下複製時會依序產生 0, 3, 6, 9, …。(作為 OFFSET 函數參數中的相對起始列)
(2) OFFSET($B$2,(ROW(1:1)-1)*$L$1,0,$L$1,7)
根據儲存格L1的數值,透過 OFFSET 函數產生每一區的儲存格範圍。
(3) 再由 MAX 函數取得該範圍中的最大值。(題目有預設每一區中的數值不重覆)

3. 找出最大值的列號
儲存格M4:=MATCH(L4,OFFSET($I$2,(ROW(1:1)-1)*$L$1,0,$L$1,1),0)+$L$1*
(ROW(1:1)-1)+1
(1) OFFSET($I$2,(ROW(1:1)-1)*$L$1,0,$L$1,1)
利用 OFFSET 函數找到每一區的儲存格範圍。(例如:序號1-3區為儲存格B2:H4、序號4-6區為儲存格B5:H7)
(2) MATCH(L4,第(1)式,0)
利用 MATCH 函數找到最大值位於第(1)式傳回的儲存格範圍中的第幾個。
(3) 第(2)式+$L$1*(ROW(1:1)-1)+1
找出各區最大值在I欄中的列號。(本例在第 1 區中,最大值是在第 2 列。)
Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

4. 找出最大值的欄號
儲存格N4:=MATCH(L4,OFFSET($B$1,M4-1,0,1,7),0)+1
(1) OFFSET($B$1,M4-1,0,1,7)
根據儲存格M4所提供的最大值在I欄中的列號,透過 OFFSET 函數取得該列的儲存格範圍。(在下圖的範例中第 1 區傳回儲存格B4:H4)
(2) MATCH(L4,OFFSET($B$1,M4-1,0,1,7),0)+1
利用 MATCH 函數取得最大值的位置是 4,所以欄號是4+1=5。(因為資料由B欄開始)
Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

5. 產生各區最大值儲存格位址
儲存格O4:=ADDRESS(M4,N4,4)
使用 ADDRESS 函數將第(3)式和第(4)式取得的列和欄參數代入,即可得各區最大值的位址。

只要改變佔儲存格L1的數值,即可改變分區的列數。
Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

【延伸學習】
如果你想要像本例中可以自動標示每一區中的最大值,可以在設定格化的條件中做以下的設定。
1. 選取儲存格B2:H298,設定格式化的條件。
Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)
2. 設定格式化的條件
規則類型 :使用公式來決定要格式哪些儲存格
條件公式:=B2=MAX(OFFSET($B$2,(INT((ROW(1:1)-1)/$L$1))*$L$1,0,$L$1,7))
格式:紅色前景色彩
Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

2017年10月14日 星期六

Excel-設定格式化的條件使用横條圖

當在 Excel 中使用設定格式化的條件時,有許多的預設樣式可供選取使用。本例要使用資料横條來練習。
如下圖,列有六種不同的呈現方式,對於正負數和圖形起始位置略有不同之處。
Excel-設定格式化的條件使用横條圖
(本例中,儲存格B2=儲存格A2、儲存格B3=儲存格A3、儲存格B4=儲存格A4、...)
(A)這是 Excel 的預設值,設定:
Excel-設定格式化的條件使用横條圖
最小值:類型(自動)/值(自動)
最大值:類型(自動)/值(自動)
(本例全部都勾選:僅顯示資料横條,所以不會顯示數值。)
Excel-設定格式化的條件使用横條圖
(B)以百分比概念,最小值為百分比=0,所以最小值沒有顯示圖形。設定:
Excel-設定格式化的條件使用横條圖
最小值:類型(百分比)/值(0)
最大值:類型(百分比)/值(100)
Excel-設定格式化的條件使用横條圖
(C) 最小值設為50,設定:
Excel-設定格式化的條件使用横條圖
最小值:類型(數值)/值(=MIN($G$2:$G$7)-50)
最大值:類型(自動)/值(自動)
Excel-設定格式化的條件使用横條圖
(D) 此例數列有工數和負數,設定:
Excel-設定格式化的條件使用横條圖
最小值:類型(自動)/值(自動)
最大值:類型(自動)/值(自動)
Excel-設定格式化的條件使用横條圖
負值和座標軸的設定:(預設值)
Excel-設定格式化的條件使用横條圖
(E) 設定負值在中央(0)的左邊(紅色),設定正值在中央(0)的右邊。
Excel-設定格式化的條件使用横條圖
最小值:類型(自動)/值(自動)
最大值:類型(自動)/值(自動)
負值和座標軸的設定:(預設值)
Excel-設定格式化的條件使用横條圖
(F) 正負值不分左右邊,負值以紅色顯示。
Excel-設定格式化的條件使用横條圖
最小值:類型(自動)/值(=MIN($G$11:$G$16)-50)
最大值:類型(自動)/值(自動)
Excel-設定格式化的條件使用横條圖
負值(預設值)和座標軸的設定(無:將負值橫條顯示為正值横條同方向)
Excel-設定格式化的條件使用横條圖

檢視其他文章

好康東東