贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

本篇文章是前二篇文章的延伸:

Excel-查詢指定字串所在的儲存格位址(MATCH,ADDRESS,IFERROR)

Excel-查詢指定字串所在的儲存格位址(MATCH,ADDRESS,FIND,OFFSET)

如下圖,如何在 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)

文章標籤

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

當在 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-設定格式化的條件使用横條圖

最小值:類型(數值)/值(=MIN($B$11:$B$16)-200)

最大值:類型(自動)/值(自動)

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

負值和座標軸的設定:將相同填滿色彩套用正值橫條。

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

(E) 設定負值在中央(0)的左邊(紅色),設定正值在中央(0)的右邊。

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

最小值:類型(自動)/值(自動)

最大值:類型(自動)/值(自動)

負值和座標軸的設定:(預設值)

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

(F) 正負值不分左右邊,負值以紅色顯示。

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

最小值:類型(自動)/值(=MIN($G$11:$G$16)-50)

最大值:類型(自動)/值(自動)

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

負值(預設值)和座標軸的設定(無:將負值橫條顯示為正值横條同方向)

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

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

網友問到:如何在 Excel 中能由指定的日期開始,依指定週數循環不同格式,該如何處理?

如下圖,本例在儲存格A2中,指定一個日期為起始日期,第一列會自動標示星期幾,再利用儲儲存格I2的循環週數,自動產生間隔週數的儲存格背景和前景色彩。

Excel-依起始日期和循環週數標示日期(MOD,INT)

 

【公式設計與解析】

1. 產生日期

儲存格B2:=A2+1

複製儲存格B2,貼至儲存格B2:G2。

儲存格B3:=A2+7

複製儲存格B3,貼至儲存格B3:G17。

 

2. 產生星期幾

儲存格A1:=A2

複製儲存格A1,貼至儲存格A1:G1。

自定儲存格數值格式:星期三格式

 

3. 設定日期儲存格的格式化的條件

複製儲存格B2,貼至儲存格B2:G2。

選取儲存格A2:G17,設定格式化的條件。

規則類型:使用公式來決定要格式化哪些儲存格

規則:=MOD(INT((ROW(A2)-2)/$I$2),2)=0

格式:粉紅色背景和紅色前景

Excel-依起始日期和循環週數標示日期(MOD,INT)

公式:=MOD(INT((ROW(A2)-2)/$I$2),2)=0

INT((ROW(A2)-2)/$I$2):將列號除以循環週數取其商,得到其為第幾個循環週期。

MOD(INT((ROW(A2)-2)/$I$2),2)=0:判斷前式的商是否為 2 的倍數(偶數週期數),若是,則顯示不同格式(粉紅色背景和紅色前景)。

Excel-依起始日期和循環週數標示日期(MOD,INT)

另外,

(參考下圖)如果你想要指定一個日期(儲存格I2)為起始,再依循環週數(儲存格I5)顯示不同的格式,該如何處理?

Excel-依起始日期和循環週數標示日期(MOD,INT)

1. 產生日期

儲存格A2:=TODAY()-WEEKDAY(TODAY(),1)+COLUMN(A:A)

關於公式說明,請參考 :Excel-產生最近四週的日期並標示顏色

2. 設定格式

步驟如下:

複製儲存格B2,貼至儲存格B2:G2。

選取儲存格A2:G17,設定格式化的條件。

規則類型:使用公式來決定要格式化哪些儲存格

規則:=(A2>=$I$2)*(MOD(INT((A2-$I$2)/($I$5*7)),2)=0)

格式:紅色前景

Excel-依起始日期和循環週數標示日期(MOD,INT)

公式:=(A2>=$I$2)*(MOD(INT((A2-$I$2)/($I$5*7)),2)=0)

利用二個條件來判斷是否標示為紅色前景:

(A2>=$I$2):儲存格日期要大於或等於儲存格I2。

MOD(INT((A2-$I$2)/($I$5*7)),2)=0:利用INT((A2-$I$2)/($I$5*7))計算每個儲存格日期是第幾個日期,並且位於第幾個循環週期。再利用 MOD 函數判斷是否是偶數週期,若是給予紅色前景。

Excel-依起始日期和循環週數標示日期(MOD,INT)

文章標籤

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

找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼