來源 cherty
每週第一天開盤價
=IF(WEEKDAY(A2,2) < IF(ISERR(WEEKDAY(A1,2)),5,WEEKDAY(A1,2)),B2,"")
週高
=IF(F2="","",IF(WEEKDAY(A3,2) < WEEKDAY(A2,2),C2,IF(WEEKDAY(A4,2) < WEEKDAY(A3,2),MAX(C2:C3),IF(WEEKDAY(A5,2) < WEEKDAY(A4,2),MAX(C2:C4),IF(WEEKDAY(A6,2) < WEEKDAY(A5,2),MAX(C2:C5),IF(WEEKDAY(A7,2) < WEEKDAY(A6,2),MAX(C2:C6),""))))))
週低
=IF(F2="","",IF(WEEKDAY(A3,2) < WEEKDAY(A2,2),C2,IF(WEEKDAY(A4,2) < WEEKDAY(A3,2),MIN(D2:D3),IF(WEEKDAY(A5,2) < WEEKDAY(A4,2),MIN(D2:D4),IF(WEEKDAY(A6,2) < WEEKDAY(A5,2),MIN(D2:D5),IF(WEEKDAY(A7,2) < WEEKDAY(A6,2),MIN(D2:D6),""))))))
週收
=IF(F2="","",IF(WEEKDAY(A3,2) < WEEKDAY(A2,2),E2,IF(WEEKDAY(A4,2) < WEEKDAY(A3,2),E3,IF(WEEKDAY(A5,2) < WEEKDAY(A4,2),E4,IF(WEEKDAY(A6,2) < WEEKDAY(A5,2),E5,IF(WEEKDAY(A7,2) < WEEKDAY(A6,2),E6,""))))))
REF:
http://office.microsoft.com/zh-tw/excel-help/HP010343015.aspx
http://isvincent.pixnet.net/blog/post/39119457-excel-%E7%AC%A6%E5%90%88%E6%A2%9D%E4%BB%B6%E7%9A%84%E6%9C%80%E5%A4%A7%E5%80%BC(max,weekday,%E9%99%A3%E5%88%97)
http://club.excelhome.net/thread-1003530-1-1.html
A最大：=MAX(IF(A2:A13="A",B2:B13))，ctrl+shift+enter三键结束公式。
A最小:：=MIN(IF(A2:A13="A",B2:B13))，ctrl+shift+enter三键结束公式。
http://isvincent.pixnet.net/blog/post/42935446-excel-%E6%89%BE%E5%87%BA%E5%88%86%E7%B5%84%E6%9C%80%E5%A4%A7%E5%80%BC%E5%92%8C%E6%9C%80%E5%B0%8F%E5%80%BC(%E9%99%A3%E5%88%97%E5%85%AC%E5%BC%8F,maxif,m
【建立公式】
(1) 求各組最大值
儲存格C2：{=IF(B2=MAX(IF(組別=A2,數值,FALSE)),"V","")}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。
{IF(組別=A2,數值,FALSE)}：找出和「組別」陣列中和儲存格A2符合的儲存格陣列(判斷式中若不符合者，則給予 False)。
{MAX(IF(組別=A2,數值,FALSE))}：利用 MAX 函數取出上式中的取大值。
(2) 求各組最小值
儲存格D2：{=IF(B2=MIN(IF(組別=A2,數值,FALSE)),"V","")}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。
原理同(1)，將 MAX 函數改用 MIN 函數。
【延伸學習】
儲存格C2：{=IF(B2=MAX(IF(組別=A2,數值,FALSE)),"V","")}，其中的 FALSE 如果以 0 或空白來取代，都會得到錯誤的結果，因為 0 或空白都會被視為 0，而 0 可能會誤成為各組中的最小值。
http://isvincent.pixnet.net/blog/post/39119457-excel-%E7%AC%A6%E5%90%88%E6%A2%9D%E4%BB%B6%E7%9A%84%E6%9C%80%E5%A4%A7%E5%80%BC(max,weekday,%E9%99%A3%E5%88%97)
【輸入公式】
(一) 各個人員的最大值
儲存格G2：{=MAX(IF(人員=F2,數值,))}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格G2，貼至儲存格G2:G5。
IF(人員=F2,數值,)：因為採用陣列公式，可以求得在人員欄位中符合儲存格F2(甲)的數值陣列。
在 IF 公式中的第三個參數為空白，可以讓求得的結果為「空白」；如果填入 0 或是Fasle，則結果會顯示「0」。
最後藉由 MAX 函數，將求得的數值陣列中取最大值，即為所求。
(二) 各個星期幾的最大值
儲存格G8：{=MAX(IF(WEEKDAY(日期,1)=ROW(1:1),數值,))}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格G8，貼至儲存格G8:G14。
原理同(一)。
WEEKDAY(日期,1)：WEEKDAY 函數可以求得一個星期幾對應的數值。本例中選取「1」，所以對應ROW(1:1)=1，因此可以求得星期日的數值陣列。若往下複製公式時，ROW(1:1)→ROW(2:2)→ROW(3:3)→ …，如此可以求得各個星期幾對應的數值陣列。
(三) 各個月份的最大值
儲存格G17：{=MAX(IF(MONTH(日期)=ROW(4:4),數值,))}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格G17，貼至儲存格G17:G20。
原理同(一)和(二)。
IF(MONTH(日期)=ROW(4:4),數值,)：透過 MONTH 函數取得日期中的月份，而 ROW(4:4)=4，即求得 4 月份的數值陣列。
(四) 各個月份中某個人員的最大值
儲存格G23：{=MAX(IF((MONTH(日期)=ROW(4:4))*(人員="甲"),數值,))}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。(以下亦同)
儲存格G24：{=MAX(IF((MONTH(日期)=ROW(5:5))*(人員="乙"),數值,))}
儲存格G25：{=MAX(IF((MONTH(日期)=ROW(6:6))*(人員="丙"),數值,))}
儲存格G26：{=MAX(IF((MONTH(日期)=ROW(7:7))*(人員="丁"),數值,))}
原理同(一)、(二)和(三)。
IF((MONTH(日期)=ROW(4:4))*(人員="甲")：在 IF 函數中使用雙條件運算，其中的「*」運算子，相當於將條件做 AND 運算。
沒有留言:
張貼留言