close
第十一課 Large與Small
Large(範圍，第幾大)
Small(範圍，第幾小)
Small(範圍，第幾小)
這兩個函數是計算範圍中之第幾大和第幾小，其中，邏輯值、空白值及文字皆會忽略
| 100 | ||
| 呼 | ||
| TRUE | ||
| @@@ | ||
| -20 | ||
| 1 | ||
| 0 | ||
| 50 | ||
| 50 | ||
| 例１，Large的基本用法 | ||
| 1 | 100 | =LARGE($A$1:$A$10,A13) |
| 2 | 50 | =LARGE($A$1:$A$10,A14) |
| 3 | 50 | =LARGE($A$1:$A$10,A15) |
| 4 | 1 | =LARGE($A$1:$A$10,A16) |
| 5 | 0 | =LARGE($A$1:$A$10,A17) |
| 6 | -20 | =LARGE($A$1:$A$10,A18) |
| 7 | #NUM! | =LARGE($A$1:$A$10,A19) |
| 8 | #NUM! | =LARGE($A$1:$A$10,A20) |
| 9 | #NUM! | =LARGE($A$1:$A$10,A21) |
| 10 | #NUM! | =LARGE($A$1:$A$10,A22) |
| 說明 | ||
| １．數值相同時（如：50），則依序指定 ２．由於範圍中僅六個數值（邏輯值、空白及文字皆會忽略），故第七大至第十大則顯示錯誤 | ||
| 例２，Small的基本用法 | ||
| 1 | -20 | =SMALL($A$1:$A$10,A30) |
| 2 | 0 | =SMALL($A$1:$A$10,A31) |
| 3 | 1 | =SMALL($A$1:$A$10,A32) |
| 4 | 50 | =SMALL($A$1:$A$10,A33) |
| 5 | 50 | =SMALL($A$1:$A$10,A34) |
| 6 | 100 | =SMALL($A$1:$A$10,A35) |
| 7 | #NUM! | =SMALL($A$1:$A$10,A36) |
| 8 | #NUM! | =SMALL($A$1:$A$10,A37) |
| 9 | #NUM! | =SMALL($A$1:$A$10,A38) |
| 10 | #NUM! | =SMALL($A$1:$A$10,A39) |
| 說明 | ||
| １．數值相同時（如：50），則依序指定 ２．由於範圍中僅六個數值（邏輯值、空白及文字皆會忽略），故第七小至第十小則顯示錯誤 | ||
| 例３，僅計算A47至A56中前三大之平均值 | ||
| 100 | 666.67 | |
| 200 | ||
| 300 | ||
| 500 | ||
| 400 | ||
| 300 | ||
| 200 | ||
| 100 | ||
| 800 | ||
| 700 | ||
| =(LARGE($A$47:$A$56,1)+LARGE($A$47:$A$56,2)+LARGE($A$47:$A$56,3))/3 | ||
| 說明 | ||
| 公式同 =SUM(LARGE($A$47:$A$56,{1,2,3}))/3 | ||
| 例４，有幾個正值呢(數列需有零值) | ||
| -2 | 5 | |
| -5 | ||
| 0 | ||
| 0 | ||
| 0 | ||
| 300 | ||
| 200 | ||
| 100 | ||
| 800 | ||
| 700 | ||
| =MATCH(TRUE,LARGE($A$65:$A$74,ROW(1:10))=0,)-1 | ||
| 說明 | ||
| 公式同(數列不需有零值) =COUNTIF($A$65:$A$74,">"&0) | ||
全站熱搜
留言列表
