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

[µo°Ý] EXCEL §PÂ_¦h±ø¥ó·j´M

[µo°Ý] EXCEL §PÂ_¦h±ø¥ó·j´M

½Ð°Ý¦U¦ì¤j¤j,¤p§Ì·Q°µ¤@­Ó¤U©Ô¦¡¿ï³æ
¦ý¬O±ø¥ó§P§O¦³¤T­Ó¶µ¥Ø
¿é¤Ja,b,c¼Æ­È..a¥ÑA¶µ¥Ø§PÂ_,b¥ÑB¶µ¥Ø§PÂ_,c¥ÑC¶µ¥Ø§PÂ_,

--------|A¶µ¥Ø½d³ò-------|B¶µ¥Ø½d³ò-------|C¶µ¥Ø½d³ò---------|¦P®Éº¡¨¬A.B.C.
±ø¥ó1 : |50-900----------|50-400----------| 0-75 ------------|Åã¥Ü"1"
±ø¥ó2 : |100-1500--------|100-450---------| 0-150------------|Åã¥Ü"2"
±ø¥ó3 : |50-3500---------|300-1100--------| 0-250------------|Åã¥Ü"3"

¨S¦³¦P®Éº¡¨¬ABC«hÅã¥Ü"¬Y¤@¼Æ­È¶W¥X½d³ò"

§Ú·Q°µ¤@¤U©Ô¦¡¿ï³æ
·ía,b,c¬Ò¸¨¦b½d³ò¤º®ÉÅã¥Ü¥i¥Î¤§±ø¥ó(¥i¯à¦³¤@±ø¨â±ø©Î¤T±ø),¨Ã©ó¥t¤@Àx¦s®æÅã¥Ü¿ï¨ú«á¸Óµ²ªG"1"©Î"2"©Î"3".
°²³]§Úa=200,b=350,c=60¦n¤F.«hÀx¦s®æ¥i¥H¤U©Ô¤@±ø¥ó·|¥X²{±ø¥ó1,2,3¥i¨Ñ¿ï¾Ü
°²³]§Úa=200,b=420,c=60,«hÀx¦s®æ¨Ì±ø¥ó¤U©Ô¥u¦³±ø¥ó2,3¥i¨Ñ¿ï¾Ü
°²³]§Úa=1600,b=420,c=60,«hÀx¦s®æ¨Ì±ø¥ó¤U©Ô¥u¦³±ø¥ó3¥i¨Ñ¿ï¾Ü
¤j·§·Qªk²³æ·Q¬O³o¼Ë..µM«á§Ú¿ï¾Ü±ø¥ó«á¥t¤@­ÓÀx¦s®æ§Ú´N¯à¥ÎVLOOKUPª½±µ¹ïÀ³Åã¥Ü"1"©Î"2"©Î"3"¤F

·Pı¦³ÂI½ÆÂø¦ý§Ú¦b·Q¬O§_¯à¥ÎÅÞ¿è¨BÆJ:
²Ä¤@¨B§PÂ_a­È¬O§_¸¨¦bA¤¤,¬Oªº¸Ü¶i¤J²Ä¤G¨B,§_«hÅã¥Ü"¬Y¤@¼Æ­È¶W¥X½d³ò"
²Ä¤G¨B§PÂ_b¬O§_¸¨¦bB¤¤,¬Oªº¸Ü¶i¤J²Ä¤T¨B,§_«hÅã¥Ü"¬Y¤@¼Æ­È¶W¥X½d³ò"
²Ä¤T¨B§PÂ_c¬O§_¸¨¦bC¤¤,­Y¬Ò¬O«hÅã¬O¹ïÀ³¤§µª®×,§_«hÅã¥Ü"¬Y¤@¼Æ­È¶W¥X½d³ò"
¦³¿ìªk©ó¤U©Ô¦¡¿ï³æ¸ê®ÆÅçÃÒ¤¤¼g¥X³o¼ËªºÅÞ¿è¶Ü?
¤£¦æªº¸Ü¥HVBA¤U¥h¼gªº¸Ü¸Ó¥Î¤°»ò¨ç¼Æ©Î»yªk¥h°µ©O@@?
¤£ª¾¹D»Ý¤£»Ý­n¥Î¨ì°}¦C???³o­Ó§Ú´N¯uªº§¹¥þ¨S»³¤FQQ
·PÁªO¤W¤j¤j¸Ñ´b>"<

AAA.gif

TOP

¤j¤j±z¦n:
L1¨ºÃä¨ç¼Æ¤£¤j²z¸Ñ¥Lªº·N«ä..¥i§_±qSMALL¨ºÃä³v¶µ¸ÑÄÀ>"<µ¹ÃjÀ´µLª¾ªº¤p§Ì¾Ç²ß¤@¤U
µM«á§Ú§ä¤£¨ì¤j¤j¬O¦b¨º­ÓÀx¦s®æ¤¤¦³¿é¤J"¬Y¤@¼Æ­È¶W¥X½d³ò"ªº»¡?

TOP

¦^´_ 1# baiecesi

°}¦C¤½¦¡
A3°}¦C¤½¦¡
=MATCH(A$2,ROW($50:$900),0)
B3°}¦C¤½¦¡
=MATCH(B$2,ROW($50:$400),0)       
C3°}¦C¤½¦¡
=MATCH(C$2,ROW($1:$76)-1,0)
A4°}¦C¤½¦¡
=MATCH(A$2,ROW($100:$1500),0)       
B4°}¦C¤½¦¡
=MATCH(B$2,ROW($100:$450),0)       
C4°}¦C¤½¦¡
=MATCH(C$2,ROW($1:$151)-1,0)
A5°}¦C¤½¦¡
=MATCH(A$2,ROW($50:$3500),0)       
B5°}¦C¤½¦¡
=MATCH(B$2,ROW($300:$1100),0)       
C5°}¦C¤½¦¡
=MATCH(C$2,ROW($1:$251)-1,0)
D3¤½¦¡=IF(SUMPRODUCT(ISNUMBER(A3:C3)*1)=3,ROW(A1),"")¦V¤U½Æ»s¨ìD6
E1¤½¦¡=IF(ROW(A1)>COUNT($D$3:$D$5),"",SMALL($D$3:$D$5,ROW(A1)))¦V¤U½Æ»s¨ìE3
©w¸q¦WºÙx=OFFSET(¤u§@ªí1!$E$1,,,COUNT(¤u§@ªí1!$E$1:$E$3),)
G1ÅçÃÒ=x
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-12-16 22:27 ½s¿è

°}¦C¤½¦¡
A3°}¦C¤½¦¡
=MATCH(A$2,ROW($50:$900),0)
B3°}¦C¤½¦¡
=MATCH(B$2,ROW($50:$400),0)        
C3°}¦C¤½¦¡
=MATCH(C$2,ROW($1:$76)-1,0)
A4°}¦C¤½¦¡
=MATCH(A$2,ROW($100:$1500),0)        
B4°}¦C¤½¦¡
=MATCH(B$2,ROW($100:$450),0)
¯«µLªk«I¥Çªº¥@¬É

TOP

        ÀR«ä¦Û¦b : ­n¤ñ½Ö§ó¨ü½Ö¡D¤£­n¤ñ½Ö§ó©È½Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD