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

Á~¸ê©Ò±oµ|¦©Ãº°ÝÃD

¥»©«³Ì«á¥Ñ ML089 ©ó 2014-2-23 00:54 ½s¿è

¦^´_ 8# aer

«Dµ¥¶ZÁ~¸ê¥i¥H­×§ï¦p¤U
H2 =IF(B2<47001,0,INDEX('102¦~'!B:M,ROUNDUP(LOOKUP(B2,{4.7,11,12,15}*10^4+1,{3,129,139,159}-1+(B2-{4.7,11,12,15}*10^4)/{500,1000,1500,2000}),0),K2+1))



«Dµ¥¶ZÁ~¸ê¾ã²z¦p¤U
470001~110000 +0500 ROW=3
110001~120000 +1000 ROW=129
120001~150000 +1500 ROW=139
150001~500000 +2000 ROW=159 ~ 333
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 11# ML089

ÁÂÁª©¥DªºÂ²¤Æ¤½¦¡¡A¤£¹L¤½¦¡¤¤ªº10¡A¬O§_À³¬°11©O¡H
H2 =IF(B2<47001,0,INDEX('102¦~'!B:M,ROUNDUP(LOOKUP(B2,{4.7,11,12,15}*10^4+1,{3,129,139,159}-1+(B2-{4.7,11,12,15}*10^4)/{500,1000,1500,2000}),0),K2+1))

TOP

¦^´_ 10# ML089
¤j¤j§A¦n!!

¬°¤°»ò B2 ªº¤½¦¡

=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,TRUE)
©M
=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,)

ªºµ²ªG·|¤£¤@¼Ë?
(¦pªG¤U­±ªºµ²ªG¬° 0, ¤W­±ªºµ²ªG¬° #N/A)

¤£¬O»¡ ¼Ò½k¬d¸ß ®É, True ¥i¥H¬Ù²¤¶Ü?
ÁÙ¬O§Ú­þ­Ó¦a¤è§Ë¿ù¤F?

TOP

¦^´_ 12# aer


    À³¸Ó¬O 11
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 13# yen956


=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,)
¦P¤U¦C2¦¡
=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,0)
=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,FALSE)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 10# ML089
ÁÂÁ¤j¤jªº¦^ÂÐ!!
´«¥y¸Ü»¡¡GExcel ªº»¡©ú, ¬O¦³³´¨Àªº, ³Ì¦n¤£­n¬Ù²¤. ¬O¶Ü?
­ì»¡©ú¦p¤U¡G
¡y¡E¦pªG range_lookup ¬° TRUE ©Î³Q¬Ù²¤¡A«h¶Ç¦^§¹¥þ²Å¦X©Î¤j¬ù²Å¦Xªº­È¡C
¦pªG§ä¤£¨ì§¹¥þ²Å¦Xªº­È¡A±N·|¶Ç¦^¶È¦¸©ó lookup_value ªº­È¡C¡z
¤F¸Ñ¤F!!

TOP

¦^´_ 15# ML089
ÁÂÁ¤j¤jªº«üÂI!!
¤£¹L·U·Q·U¤£¹ï¡A
0=False, 1=True,

¦pªG
¡y=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,)
¦P¤U¦C2¦¡
=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,0)
=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,FALSE)¡z
¨º°Z¤£ªí¥Ü FALSE ¥i¥H¬Ù²¤?

À³¸Ó¬O§Oªº°ÝÃD§a!!

TOP

¦^´_ 17# yen956

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
¦pªG range_lookup ¬° TRUE ©Î³Q¬Ù²¤¡A«h¶Ç¦^§¹¥þ²Å¦X©Î¤j¬ù²Å¦Xªº­È¡C

¥¿½T¦p¤U¦¡
=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2)

¤U¦¡³Ì«á¥[¤W" ," ªí¥Ü¨S¦³¬Ù²¤ range_lookup¥u¬O¨S¦³¶ñ­È¡AºD¨Ò¤WEXCELµø¬°0¡A³o¬O»{ª¾¤Wªº»~®t¡C
=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,)
¦P¤U¦C2¦¡
=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,0)
=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,FALSE)¡z
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 10# ML089
¯u·PÁ¤j¤jªº«üÂI°g¬z,
­ì¨Ó®t¦b¤@­Ó³r¸¹, ¦pªG¤£¬O¤j¤j¦A¤T»¡©ú,
¯u¤£ª¾°ÝÃD¥X¦b­þ¸Ì,
=VLOOKUP($B3, '102¦~'!$B$3:$N$334,$K3+2)
©M
=VLOOKUP($B3, '102¦~'!$B$3:$N$334,$K3+2, 1)
=VLOOKUP($B3, '102¦~'!$B$3:$N$334,$K3+2, TRUE)
¬Û¦P, ¦Ó
=VLOOKUP($B3, '102¦~'!$B$3:$N$334,$K3+2,)
©M
=VLOOKUP($B3, '102¦~'!$B$3:$N$334,$K3+2, 0)
=VLOOKUP($B3, '102¦~'!$B$3:$N$334,$K3+2, false)
¬Û¦P,
¨S¦³°ª¤H«üÂI, ÁÙ¤£ª¾¦Û¤w¿ù¦b­þ¸Ì,
­ì¨Ó­n¬Ù²¤, ÁÙ¦³¨º»ò¦h¬Ü¨¤, Á°Õ!!

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡µLªk¾B¾×¡j©È®É¶¡®ø³u¡Aªá¤F³\¦h¤ß¦å¡A·QºÉ¦U¦¡¤èªk­n¾B¾×®É¶¡¡Aµ²ªG¬O¡G®ö¶O¤F§ó¦h®É¶¡¡A¥B¤@µL©Ò¦¨¡I
ªð¦^¦Cªí ¤W¤@¥DÃD