ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] IFªº§xÂZ

¦^´_ 1# PJChen
¦^´_ 2# p212

ÀHµÛ¨C¤Ñ¤ß±¡¤£¦P¡A¬Ýªk¤]·|¤£¦P

¤W¦¸¥Î TEXT ¡A²{¦b¬Ý°_¨Óıªº¥Î±o¤£¦n¡A³o¬O¯Â¼Æ¾Ç¤ñ¸û°ÝÃD«o¥Î¹LÂo¤èªk¨Ó³B²z¡A¼Æ¦rÂà¤å¦r¦AÂà¼Æ¦r¦h¤Ö¦³ºë«×°ÝÃD¡A³t«×¤]¼vÅT«Ü¤j

ÃD¥Ø¬O ­pºâ­È¤j©ó5´N¨ú5¡A¥Î MIN(5,­pºâ­È) ²³æªº¼Æ¾Ç¤ñ¸û´N¯à³B²z¡C

­ì¦¡
Q1249
=IF(IF(AND(N1249<N1250,N1250>0),5/N1250*N1249,5/N1248*N1249)>5,5,IF(AND(N1249<N1250,N1250>0),5/N1250*N1249,5/N1248*N1249))

¥ÎMIN()´N¯àÁY´î
=MIN(5,IF(AND(N1249<N1250,N1250>0),5/N1250*N1249,5/N1248*N1249))

­pºâ¦¡µ²ºc­×§ï¤]¯à²µu
=MIN(5,5/IF(AND(N1249<N1250,N1250>0),N1248,N1248)*N1249)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 12# ML089

¶â¡A¥Îmin¬O­Ó¦n¤èªk¡I

¬O»¡§Ú³Ìªñ¤]¹J¨ìÃþ¦üªº°ÝÃD¡G
if(¤@ªø¦êºâ¦¡=0,"",¤@ªø¦êºâ¦¡)
©Î¬O
if((¤@ªø¦êºâ¦¡=0)+(¤@ªø¦êºâ¦¡>¥t¤@ªø¦êºâ¦¡),"",¤@ªø¦êºâ¦¡)
³o­Ó°£¤F¥Î©w¸q¦WºÙ¥~¡A¦³¬Ù²¤ªº¼gªk¶Ü¡H

note:
¤@ªø¦êºâ¦¡¡GINDEX('1020514¥þ¦W¥U'!A:A,MATCH(ROUNDUP((ROW()-2)/31,0)*3-2,'1020514¥þ¦W¥U'!A:A,0)+MOD(ROW()-3,31))
¥t¤@ªø¦êºâ¦¡¡GROUNDUP((ROW()-2)/31,0)*3

TOP

¦^´_ 13# Bodhidharma

if(¤@ªø¦êºâ¦¡=0,"",¤@ªø¦êºâ¦¡)
©Î¬O
if((¤@ªø¦êºâ¦¡=0)+(¤@ªø¦êºâ¦¡>¥t¤@ªø¦êºâ¦¡),"",¤@ªø¦êºâ¦¡)

µy·L¬ã¨s¤F¤@¤U¡A¦ü¥G¥i¥H¥Î
text(¤@ªø¦êºâ¦¡,"[=0] ")
¥H¤Î
text(¤@ªø¦êºâ¦¡,"[=0] ;[>¥t¤@ªø¦êºâ¦¡] "
ªº¤è¦¡³B²z¡A¤£¹L³o²zªºªÅ®æ¬O" "¡A»P""¦³¤@ÂI®t¶Z
½Ð°Ýtext­n¦p¦ó¦^¶Ç""©O¡H

TOP

¦^´_ 14# Bodhidharma

textªºformat_text»yªk¦ü¥G«Ü¦n¥Î¡A¤£¹L§ä¤£¨ì±Ð¾Ç¤å¥ó¡K
text(value,[±ø¥ó¤@]®æ¦¡¤@;[±ø¥ó¤G]®æ¦¡¤G...)
¦ü¥G¬O¥Îselect caseªºÅÞ¿è¦b¶i¦æªº¡A·í²Å¦X²Än­Ó±ø¥ó®É¡A´N¥Î¸Ó®æ¦¡¡AµM«á´N¸õ¥X¦¹¨ç¼Æ
·Q½Ð±Ð¦pªG­n¦P®É²Å¦X¨â­Ó±ø¥ó¡A­n¦p¦ó³]©w¡H(¥Î[±ø¥ó¤@][±ø¥ó¤G]¦ü¥G¤£¦æ¡K)

TOP

¦^´_ 14# Bodhidharma
>if(¤@ªø¦êºâ¦¡=0,"",¤@ªø¦êºâ¦¡)
>©Î¬O
>if((¤@ªø¦êºâ¦¡=0)+(¤@ªø¦êºâ¦¡>¥t¤@ªø¦êºâ¦¡),"",¤@ªø¦êºâ¦¡)
>
>µy·L¬ã¨s¤F¤@¤U¡A¦ü¥G¥i¥H¥Î
>text(¤@ªø¦êºâ¦¡,"[=0] ")
>¥H¤Î
>text(¤@ªø¦êºâ¦¡,"[=0] ;[>¥t¤@ªø¦êºâ¦¡] "
>ªº¤è¦¡³B²z¡A¤£¹L³o²zªºªÅ®æ¬O" "¡A»P""¦³¤@ÂI®t¶Z
>½Ð°Ýtext­n¦p¦ó¦^¶Ç""©O¡H

¥i¥H¥Î TEXT(¤@ªø¦êºâ¦¡,"[<>];;;")
0 ¦^¶Ç¬° ""¡A¨ä¥L¨Ì­ì­È¦^¶Ç¡A¼Æ¦r¤´¬° ¤å¦r®æ¦¡¡A¨Ï¥Î¤W¤´»Ýª`·N
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 15# Bodhidharma
>textªºformat_text»yªk¦ü¥G«Ü¦n¥Î¡A¤£¹L§ä¤£¨ì±Ð¾Ç¤å¥ó¡K
>text(value,[±ø¥ó¤@]®æ¦¡¤@;[±ø¥ó¤G]®æ¦¡¤G...)
>¦ü¥G¬O¥Îselect caseªºÅÞ¿è¦b¶i¦æªº¡A·í²Å¦X²Än­Ó±ø¥ó®É¡A´N¥Î¸Ó®æ¦¡¡AµM«á´N¸õ¥X¦¹¨ç¼Æ
>·Q½Ð±Ð¦pªG­n¦P®É²Å¦X¨â­Ó±ø¥ó¡A­n¦p¦ó³]©w¡H(¥Î[±ø¥ó¤@][±ø¥ó¤G]¦ü¥G¤£¦æ¡K)

TEXT ±ø¥ó¥u¯à³]©w3­Ó¡A2­Ó¥i¥H«ü©w¡A¨ä¥L´N¬O²Ä3­Ó
¨Ò¦p
=TEXT(A1,"[>10]!A;[>5]!B;!C")
¤j©ó10Åã¥Ü¬° "A"
5 - 10Åã¥Ü¬° "B"
¨ä¥LÅã¥Ü "C"
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 17# ML089

§Ú­ì·Q±N·s¾Ç¨ìªº¦¡¤l®M¶iQ2·í¤¤,¦ý¤½¦¡¤¤¤Ó¦hif,´N®M¤£¥X¨Ó¤F,¤£ª¾¥H¤U¤½¦¡¥X²{«Ü¦hif®É,¬O§_¥i¥Î¨ä ¥L¨ç¼Æ?

Q2=IF($G2="","",ROUND(IF(IF(E2=0,0,IF(F2<=$AA$2,5/$Z$2*N2,IF(AND(F2>$AA$2,N1=0,N2>$X$2,N2<N3,N3>0),5/N3*N2,IF(AND(F2>$AA$2,N1>0,N2>$X$2,N2>N3,N3=0),5/N1*N2,5))))>5,5,IF(E2=0,0,IF(F2<=$AA$2,5/$Z$2*N2,IF(AND(F2>$AA$2,N1=0,N2>$X$2,N2<N3,N3>0),5/N3*N2,IF(AND(F2>$AA$2,N1>0,N2>$X$2,N2>N3,N3=0),5/N1*N2,5))))),3))

IF_3.zip (13.06 KB)

TOP

¦^´_ 18# PJChen
  1. =IF($G2="","",ROUND(MIN(5,IF(E2=0,0,IF(F2<=$AA$2,5/$Z$2*N2,IF(AND(F2>$AA$2,N1=0,N2>$X$2,N2<N3,N3>0),5/N3*N2,IF(AND(F2>$AA$2,N1>0,N2>$X$2,N2>N3,N3=0),5/N1*N2,5))))),3))
½Æ»s¥N½X
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 19# ML089

·sªº¦¡¤l½T¹ê¤ñ­ì¦¡Â²µu,¨ç¼Æ»¡©ú¤¤¥u¼Ð¥Ü¤F:MIN(number1, [number2], ...),
¤£ª¾¦b·sªº¦¡¤l¤¤,minªº¥Îªk¸ÑÄÀ¬°¦ó?
¦³µL¤ñ¸û²©öªº°Ñ¦Ò¨Ò¤l¥iÀ°¦£§Ú¤F¸Ñ?
Tks.

TOP

¦^´_ 19# ML089

min + if ªº¥Îªk,¥H¤U¬O§Ú¦Û¤vªº²z¸Ñ,¿ù¤F½Ð«ü¥¿.. TKS.

...MIN(5,IF(E2=0,0,IF(..,IF(..,IF(..))))),3))
¸Ñ: ³Ì¤p¬°5,§_«h IF(E2=0,0,IF(..,IF(..,IF(..))))

TOP

        ÀR«ä¦Û¦b : ªY½à§O¤H´N¬O²øÄY¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD