ユーザーズガイド APPLE IWORK FORMULAS AND FUNCTIONS 2009

[. . . ] iWork K Apple Inc. © 2009 Apple Inc. [. . . ] ) 1 1 206 10   TRUE TRUE . . . FALSE FALSE  ,  . . . 1 TRUE FALSE   =AVERAGEIFS(D2:D13, A2:A13, ”<40”, B2:B13, ”=M”) 56000 57000 55000 40 40 40 40 B M =AVERAGEIFS(D2:D13, A2:A13, ”<40”, B2:B13, ”=M”, C2:C13, ”=S”) C S =AVERAGEIFS(D2:D13, A2:A13, ”<40”, B2:B13, ”=M”, C2:C13, ”=M”) C M =AVERAGEIFS(D2:D13, A2:A13, ”<40”, B2:B13, ”=F”) 59333 B F 203 204 10 AVERAGE AVERAGEA 207 205 318 199 33 15 25 36 AVERAGEIF BETADIST BETADIST BETADIST(x  x    , , , x x 1 1 , x x ) 0 0 0 x 0 x 1 1 x x x x x x  =BETADIST(0. 5, 1, 2, 0. 3, 2) 0. 221453287197232 =BETADIST(1, 1, 2, 0, 1) 1 =BETADIST(0. 1, 2, 2, 0, 2) 0. 00725 209 199 33 15 25 36 BETAINV 208 10 BETAINV BETAINV BETAINV(     , , , x 1 1 , x ) 0 0 0 x 0 x 1 1 x x x x x x  =BETAINV(0. 5, 1, 2, 0. 3, 2) =BETAINV(0. 99, 1, 2, 0, 1) =BETAINV(0. 1, 2, 2, 0, 2) 0. 797918471982869 0. 9 0. 391600211318183 208 199 33 15 25 36 BETADIST BINOMDIST BINOMDIST BINOMDIST(  , , , ) 1 0 0 1    TRUE 1 10 209 FALSE 0  BINOMDIST =BINOMDIST(3, 98, 0. 04, 1) =BINOMDIST(3, 98, 0. 04, 0) 0. 445507210083272 0. 201402522366024 221 241 246 248 199 33 15 25 36 CRITBINOM NEGBINOMDIST PERMUT PROB CHIDIST CHIDIST CHIDIST(x  x  , ) x 1 0 =CHIDIST(5, 2) 0. 0820849986238988 =CHIDIST(10, 10) 0. 440493285065212 =CHIDIST(5, 1) 0. 0253473186774683 210 10 211 211 199 33 15 25 36 CHIINV CHITEST CHIINV CHIINV CHIINV(   , ) 0 1 1 =CHIINV(0. 5, 2) =CHIINV(0. 1, 10) =CHIINV(0. 5, 1) 1. 38629436111989 15. 9871791721053 0. 454936423119572 210 211 199 33 15 25 36 CHIDIST CHITEST CHITEST CHITEST 10 211 CHITEST(   , )   1 =CHITEST(A2:B6, A9:B13) A9 5. 91020074984668E-236 B9 B13 =SUM(A$2:B$2)*SUM($A2:$A6)/SUM($A$2:$B$6) A9:B9 A13:B13 =SUM(B$2:C$2)*SUM($A6:$A11)/SUM($A$2:$B$6) 210 211 199 33 15 25 36 CHIDIST CHIINV 212 10 CONFIDENCE CONFIDENCE CONFIDENCE(  , , ) 1 1   0 0  =CONFIDENCE(0. 05, 1, 10) 100. 62 =CONFIDENCE(0. 1, 1, 10) 100. 52 =CONFIDENCE(0. 05, 1, 20) =CONFIDENCE(0. 05, 1, 30) =CONFIDENCE(0. 05, 1, 40) 0. 62 0. 52 0. 44 0. 36 0. 31 100 100 95% 90% 99. 38 99. 48 254 199 33 15 25 36 STDEV CORREL CORREL CORREL( y  y  2 , x ) y x y x x 10 213   y x CORREL A =CORREL(A2:A11, B2:B11) -0. 9076 220 319 199 33 15 25 36 COVAR COUNT COUNT COUNT(   , . . . ) 1 COUNTA . . .  214 10 COUNT COUNT =COUNT(A1:E1) 5 =COUNT(A2:E2) 0 2 =COUNT(A3:E3) TRUE FALSE 3 =COUNT(A5:E5) 2 “ ” “ ” =COUNT(2, 3, A5:E5, SUM(A1:E1), A , b ) 5 SUM 1 2 5 3 =COUNT(A4:E4) 2 3 A5:E5 0 2 215 216 217 219 319 199 33 15 25 36 COUNTA COUNTBLANK COUNTIF COUNTIFS COUNTA COUNTA COUNTA(   , . . . ) 1 . . . 10 215  COUNT COUNT COUNT COUNTA =COUNTA(A1:E1) 5 =COUNTA(A2:E2) 5 =COUNTA(A3:E3) TRUE FALSE =COUNTA(A4:E4) 3 =COUNTA(A5:E5) 2 =COUNTA(2, 3, A5:E5, SUM(A1:E1), “A”, “b”) 7 2 3 2 SUM 1 A b 5 5 A5:B5 7 214 216 217 219 319 199 33 15 25 36 COUNT COUNTBLANK COUNTIF COUNTIFS COUNTBLANK COUNTBLANK 216 10 COUNTBLANK(  ) 1 COUNT COUNT COUNTBLANK 3 8 COUNTBLANK =COUNTBLANK(A1:E1) 0 =COUNTBLANK(A2:E2) 0 =COUNTBLANK(A5:E5) 3 =COUNTBLANK(A6:E6) 5 =COUNTBLANK(A1:E6) 8 1 =COUNTBLANK(A1:E1, A5:E5) 214 215 217 219 319 199 33 15 25 36 COUNT COUNTA COUNTIF COUNTIFS COUNTIF COUNTIF 10 217 COUNTIF(   , ) FALSE FALSE TRUE TRUE  COUNT COUNT COUNTIF =COUNTIF(A1:E1, “>0”) 3 3 amet t 2 4 100 2 5 =COUNTIF(A3:E3, “>=100”) 2 4 =COUNTIF(A1:E5, “=amet”) =COUNTIF(A1:E5, “=*t”) 214 215 216 219 318 319 199 33 15 25 36 COUNT COUNTA COUNTBLANK COUNTIFS 218 10 COUNTIFS COUNTIFS COUNTIFS(   1 , TRUE TRUE . . . , , . . . , FALSE FALSE . . . ) 1   . . . 1 TRUE FALSE  1 =COUNTIFS(A2:A13, ”<40”, B2:B13, ”=M”) 4 40 2 2 40 40 40 B M C C S M =COUNTIFS(A2:A13, ”<40”, B2:B13, ”=M”, C2:C13, ”=S”) =COUNTIFS(A2:A13, ”<40”, B2:B13, ”=M”, C2:C13, ”=M”) =COUNTIFS(A2:A13, ”<40”, B2:B13, ”=F”) 3 B F 10 219 214 215 216 217 318 199 33 15 25 36 COUNT COUNTA COUNTBLANK COUNTIF COVAR COVAR COVAR(  2 1 1 2 , 2 1 2 ) 1 2     2 2 220 10 COVAR A =COVAR(A2:A11, B2:B11) -1. 6202 213 319 199 33 15 25 36 CORREL CRITBINOM CRITBINOM CRITBINOM(    , , ) 0 0 1 1 1 10 221 =CRITBINOM(97, 0. 05, 0. 05) 97 =CRITBINOM(97, 0. 25, 0. 1) 97 =CRITBINOM(97, 0. 25, 0. 05) 97 2 19 17 5% 25% 25% 95% 90% 95% 5% 10% 5% 209 241 246 248 199 33 15 25 36 BINOMDIST NEGBINOMDIST PERMUT PROB DEVSQ DEVSQ DEVSQ(   , . . . ) 1 . . . DEVSQ  =DEVSQ(1, 7, 19, 8, 3, 9) 196. 833333333333 STDEV 222 10 199 33 15 25 36 EXPONDIST EXPONDIST EXPONDIST(x  x   , , ) x 0 0 TRUE FALSE =EXPONDIST(4, 2, 1) =EXPONDIST(4, 2, 0) 1 0 0. 999664537372097 0. 000670925255805024 236 199 33 15 25 36 LOGNORMDIST FDIST FDIST FDIST(x  x F , , x ) 0 10 223  1 1   F =FDIST(0. 77, 1, 2) =FDIST(0. 77, 1, 1) =FDIST(0. 77, 2, 1) F 0. 472763488223567 0. 541479597634413 0. 627455805138159 - 224 199 33 15 25 36 FINV FINV FINV FINV(   F , , ) 0 1 1 1  =FINV(0. 77, 1, 2) =FINV(0. 77, 1, 1) =FINV(0. 77, 2, 1) 0. 111709428782599 0. 142784612191674 0. 34331253162422 224 10 223 199 33 15 25 36 FDIST FORECAST FORECAST FORECAST(x  x  x , y y y x y x , x ) x x y y x     2 y x SLOPE INTERCEPT  =FORECAST(9, A3:F3, A2:F2) 19 213 220 10 CORREL COVAR 225 231 252 199 33 15 25 36 INTERCEPT SLOPE FREQUENCY FREQUENCY FREQUENCY(   - , - ) - -  FREQUENCY -  1 INDEX =INDEX(FREQUENCY( 1 FREQUENCY - , - ), x) INDEX x 226 10 30 65 1 A F 5 =INDEX(FREQUENCY($A$1:$F$5, $B$8:$E$8), B9) B10 F10 3 8 8 6 5 F D 65 A 188 244 245 249 199 33 15 25 36 INDEX PERCENTILE PERCENTRANK QUARTILE GAMMADIST GAMMADIST GAMMADIST(x  x    , 1 1 , , x ) 0 0 0 10 227 TRUE FALSE =GAMMADIST(0. 8, 1, 2, 1) =GAMMADIST(0. 8, 1, 2, 0) 1 0 0. 329679953964361 0. 33516002301782 228 229 199 33 15 25 36 GAMMAINV GAMMALN GAMMAINV GAMMAINV GAMMAINV(    , , ) 0 1 1 0 0 1 =GAMMAINV(0. 8, 1, 2) =GAMMAINV(0. 8, 2, 1) 3. 2188758248682 2. 99430834700212 227 229 199 33 GAMMADIST GAMMALN 228 10 15 25 36 GAMMALN GAMMALN GAMMALN( x  x ) x x 0 G(x) =GAMMALN(0. 92) =GAMMALN(0. 29) 0. 051658003497744 1. 13144836880416 227 228 158 199 33 15 25 36 GAMMADIST GAMMAINV LN GEOMEAN GEOMEAN GEOMEAN(   , . . . ) 0 1 . . . GEOMEAN  10 229 =GEOMEAN(5, 7, 3, 2, 6, 22) 5. 50130264578853 203 230 199 33 15 25 36 AVERAGE HARMEAN HARMEAN HARMEAN HARMEAN(   , . . . ) 0 1 . . .  =HARMEAN(5, 7, 3, 2, 6, 22) 4. 32179607109448 203 229 199 33 15 25 AVERAGE GEOMEAN 230 10 36 INTERCEPT INTERCEPT INTERCEPT(y  y  y , x y x ) y x x 2   SLOPE INTERCEPT y =INTERCEPT(B2:B11, A2:A11) 78 252 199 33 15 25 36 SLOPE 10 231 LARGE LARGE LARGE(  n , ) 1  1  1 2 2 20 20 =LARGE(A1:E4, 1) =LARGE(A1:E4, 2) =LARGE(A1:E4, 3) =LARGE(A1:E4, 6) 86 100 92 92 86 B2 2 B2 2 C2 6 B2 C2 3 100 92 92 91 90 251 253 199 33 15 25 36 RANK SMALL 232 10 LINEST LINEST LINEST(  y y 1 , x y , y 0 y y , y ) x 1 x 1 x  x y x {1, 2, 3. . . } x x x y y y b y b x y 1 1  y 0 1 TRUE 0 FALSE b 0  0 FALSE 1 TRUE  1 INDEX =INDEX(LINEST( y FALSE 1 x TRUE 234 5 , x LINEST INDEX , const-b, ), y, x) 1 x b y x x 1 10 233 30 65 1 A F 5 =INDEX(LINEST(A2:A6, C2:C6, 1, 0), 1) =INDEX(LINEST(A2:A6, C2:C6, 1, 0), 2) 0. 752707581227437 0. 0342960288808646 b 199 33 15 25 36 LINEST LINEST x 5 1 1 2 3 4 5 slope x5 std-err x1 coe∑cient-det F-stat reg-ss reside-ss 2 slope x4 std-err x2 std-err y 5 LINEST x x 4 slope x2 std-err x4 5 slope x1 std-err x5 6 b y x 5 3 slope x3 std-err x3 std-err b slope x x x 5 5 234 10 b std-err x x y x x 5 std-err b y b y y y F y 1 coe∑cient-det y 0 std-err y F-stat F y reg-ss reside-ss  x x 5 y 5 x  1  LINEST LOGINV LOGINV LOGINV(   x , , ln(x) 0 ) 0 1 ln(x) x   LOGINV x 29. 9289150377259 =LOGINV(0. 78, 1. 7, 2. 2) 10 235 158 236 199 33 15 25 36 LN LOGNORMDIST LOGNORMDIST LOGNORMDIST LOGNORMDIST(x  x  , , x ) x ln(x) x 0 0 ln(x)  =LOGNORMDIST(0. 78, 1. 7, 2. 2) 0. 187899237956868 158 235 199 33 15 25 36 LN LOGINV MAX MAX 236 10 MAX(   , . . . ) 1 . . .   MAXA =MAX(5, 5, 5, 5, 6) =MAX(1, 2, 3, 4, 5) 6 5 232 237 239 253 199 33 15 25 36 LARGE MAXA MIN SMALL MAXA MAXA MAXA(   , . . . ) 1 . . .   FALSE 0 TRUE 1 MAX 10 237 =MAXA(1, 2, 3, 4) =MAXA(A1:C1) 4 A1:C1 -1 -10 0 236 239 199 33 15 25 36 MAX MINA MEDIAN MEDIAN MEDIAN(   , . . . 1 . . . )  2 =MEDIAN(1, 2, 3, 4, 5) 3 =MEDIAN(1, 2, 3, 4, 5, 6) 3. 5 =MEDIAN(5, 5, 5, 5, 6) 5 203 240 199 AVERAGE MODE 238 10 33 15 25 36 MIN MIX MIN(   , . . . . . . ) 1   MINA =MIN(5, 5, 5, 5, 6) =MIN(1, 2, 3, 4, 5) 5 1 232 236 239 253 199 33 15 25 36 LARGE MAX MINA SMALL MINA MINA 10 239 MINA(   , . . . ) 1 . . .   FALSE 0 TRUE 1 MIN =MINA(1, 2, 3, 4) =MINA(A1:C1) =MINA(A1:C1) 1 A1:C1 A1:C1 -1 -10 1 10 -10 0 237 239 199 33 15 25 36 MAXA MIN MODE MODE MODE(   , . . . 1 . . . )   2 240 10 =MODE(5, 5, 5, 5, 6) =MODE(1, 2, 3, 4, 5) =MODE(2, 2, 4, 6, 6) =MODE(6, 6, 4, 2, 2) 5 2 6 203 238 199 33 15 25 36 AVERAGE MEDIAN NEGBINOMDIST NEGBINOMDIST NEGBINOMDIST(    , , ) 0 1 0 1  NEGBINOMDIST =NEGBINOMDIST(3, 68, 0. 95) 0. 20913174716192 209 221 246 BINOMDIST CRITBINOM PERMUT 10 241 248 199 33 15 25 36 PROB NORMDIST NORMDIST NORMDIST(     , , , ) 0 TRUE FALSE 1 0 1 TRUE NORMDIST NORMSDIST  0 =NORMDIST(22, 15, 2. 5, 1) =NORMDIST(22, 15, 2. 5, 0) 0. 997444869669572 0. 00316618063319199 243 243 199 33 15 25 36 242 10 NORMINV NORMSDIST NORMINV NORMINV NORMINV(    , , ) 0 0 1  0 1 NORMINV NORMSINV =NORMINV(0. 89, 15, 2. 5) 18. 0663203000915 242 244 199 33 15 25 36 NORMDIST NORMSINV NORMSDIST NORMSDIST NORMSDIST(  )  0 =NORMSDIST(4. 3) 0. 999991460094529 1 10 243 242 244 199 33 15 25 36 NORMDIST NORMSINV NORMSINV NORMSINV NORMSINV(  ) 0 0 1 1  =NORMSINV(0. 89) 1. 22652812003661 243 243 199 33 15 25 36 NORMINV NORMSDIST PERCENTILE PERCENTILE 244 10 PERCENTILE(  , )  0 1 25% 0 1 0. 25  20 20 =PERCENTILE(A1:E4, 0. 90) =PERCENTILE(A1:E4, 2/3) 67 =PERCENTILE(A1:E4, 0. 50) 92 85 83 10% 3 2 1 1 90 2/3 50 226 245 249 199 33 15 25 36 FREQUENCY PERCENTRANK QUARTILE PERCENTRANK PERCENTRANK 10 245 PERCENTRANK(  , , )   1 3 x. xxx%  PERCENTRANK 10 50% 10 PERCENTRANK 10 0. 813 7 2 =PERCENTRANK({5, 6, 9, 3, 7, 11, 8, 2, 14}, 10) 226 244 199 33 15 25 36 FREQUENCY PERCENTILE PERMUT PERMUT PERMUT(   , ) 0 0 246 10 =PERMUT(25, 5) =PERMUT(10, 3) =PERMUT(5, 2) 6375600 720 20 209 221 241 248 199 33 15 25 36 BINOMDIST CRITBINOM NEGBINOMDIST PROB POISSON POISSON POISSON(    , , ) TRUE FALSE 1 0 10 8 =POISSON(8, 10, FALSE) 0. 112599 10 247 223 199 33 15 25 36 EXPONDIST PROB PROB PROB(   , , , ) 1    PROB PROB  2 248 10 1 10 C 0. 1 10% 10 E =PROB(A1:A10, C1:C10, 4, 6) 0. 30 4 5 6 =PROB(A1:A10, E1:E10, 7) 0. 28 4 5 6 =PROB(A1:A10, E1:E10, 4, 6) 0. 20 7 =PROB(A1:A10, C1:C10, 6, 10) 0. 50 6 10 5 209 221 241 246 199 33 15 25 36 BINOMDIST CRITBINOM NEGBINOMDIST PERMUT QUARTILE QUARTILE 10 249 QUARTILE(   , ) (0) 1 1 2 2 3 3 1 2 3 4  25 50 75 MIN MEDIAN MAX 0 2 4 QUARTILE =QUARTILE({5, 6, 9, 3, 7, 11, 8, 2, 14}, 0) =QUARTILE({5, 6, 9, 3, 7, 11, 8, 2, 14}, 1) =QUARTILE({5, 6, 9, 3, 7, 11, 8, 2, 14}, 2) =QUARTILE({5, 6, 9, 3, 7, 11, 8, 2, 14}, 3) =QUARTILE({5, 6, 9, 3, 7, 11, 8, 2, 14}, 0) 2 5 7 9 14 25 50 75 1 2 3 226 236 238 239 244 245 199 33 15 25 36 FREQUENCY MAX MEDIAN MIN PERCENTILE PERCENTRANK 250 10 RANK RANK RANK(   , , )  1 0 FALSE 1 TRUE 1 1   20 20 30 92 2 2 1 2 2 4 2 1 1 =RANK(91, A1:E4, 1) 4 =RANK(30, A1:E4, 1) 1 =RANK(92, A1:E4, 0) 100 92 92 91 232 253 199 33 15 25 36 LARGE SMALL 10 251 SLOPE SLOPE SLOPE( y  y  , x y x ) y x x   2  INTERCEPT SLOPE =SLOPE(B2:B11, A2:A11) -3. 2337 231 199 33 15 25 36 INTERCEPT 252 10 SMALL SMALL SMALL(  n , ) 1  1  1 2 2 20 20 =SMALL(A1:E4, 1) =SMALL(A1:E4, 2) =SMALL(A1:E4, 6) 1 75 30 51 75 75 A1 2 6 6 E1 30 51 68 70 75 5 232 251 199 33 15 25 36 LARGE RANK 10 253 STANDARDIZE STANDARDIZE STANDARDIZE(    , , ) 0 =STANDARDIZE(6, 15, 2. 1) –4. 28571428571429 242 243 243 244 268 199 33 15 25 36 NORMDIST NORMINV NORMSDIST NORMSINV ZTEST STDEV STDEV STDEV(   , . . . [. . . ] 1 on =COUNTIF(B2:E7, “*on?”) on none 2 eon 1 alone 5 10 1 5 13 319 1 1 5 A E iWork 50 100 =CORREL(B2:B11, C2:C11) 2 1 1 2 2 2 1 -0. 1732 =COUNT(A2:A11) =COUNTA(A2:A11) 10 COUNT 9 COUNTA 5 A COUNTA 1 E COUNTA =COUNT(B2:B11) =COUNTA(B2:B11) 320 13 =COUNTBLANK(B2:B11) 3 3 1 D =COUNTIF(B2:B11, “=5”) 5 1 1 A E =COUNTIF(B2:B11, “=E”) 4 5 =COVAR(B2:B11, C2:C11) 1 1 2 2 1 2 2 A =STDEV(B2:B11) =STDEVP(B2:B11) : COVAR E 1 3 1 STDEV VAR =VAR(B2:B11) =VARP(B2:B11) 1 STDEVP 1 5 STDEV VAR VARP VAR STDEV 13 321 [. . . ]