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

[µo°Ý] ½Ð°ª¤âÀ°¦£¹ïÀ³°Ïªº­È¨ç¼Æ¸Ó¦p¦ó¼g ÁÂÁÂ

[µo°Ý] ½Ð°ª¤âÀ°¦£¹ïÀ³°Ïªº­È¨ç¼Æ¸Ó¦p¦ó¼g ÁÂÁÂ

~test.JPG
2020-3-12 11:54



1.·í¦³¦¨¥æ»ù®É·|§e²{¥X¹ïÀ³¤§¶R»ù©Î½æ»ù
2.¨ä¹ïÀ³¼Æ­È¬°¹ïÀ³¶R»ùA¦æ©Î¹ïÀ³½æ»ùB¦æ¤º¨ú±o
¨Ò¦p ­Y¬O¶R¨ì3.43ªº©e°U»ù ¨º¨ä¹ïÀ³¤§½æ»ù±N¬°3.46
     ­Y¬O½æ¨ì3.43ªº©e°U»ù ¨º¨ä¹ïÀ³¤§¶R»ù±N¬°3.4
3.½Ð¯d·N¤@¤U A¦æ¶R»ù©ÎB¦æ½æ»ù¦³³sÄò¬Û¦P­Èªº¥i¯à ¤]¥i¯à¤£¬O³sÄòªº¼Æ­È
4.¬ÛÃö¹ïÀ³¨ìªº(©³¦â)®æ¦¡¤Æªº±ø¥ó§Ú¤w¥[¤J
  ·í¦³¼Æ­È®É·|¨Ì±ø¥ó²£¥Í©³¦â
  ·PÁ°ª¤âªº«ü¾É
¥t¥~¸û¦­«e¦³µo¤@©«µo¿ù°Ï¤F(¦bAccess°Ï)  ©Ò¥H­«·s©óExcelµ{¦¡°Ï¦Aµo¤@¦¸ ³Â·Ðª©¤j±N¯Çµo¿ù°Ïªº©«§R¤F
ÁÂÁÂ

~test.zip (2.96 KB)

¦^´_ 1# lcctno


   ¸Õ¬Ý¬ÝI2
=IF($H2="","",VLOOKUP($E2,IF(ISNUMBER($F2:$G2),$A$2:$A$26,$B$2:$B$26),2,0))

TOP

¦^´_ 2# cucu


·PÁ±zªº±Ð¾É ¦ý§Ú¦³ºÃ°Ý Á٧Ʊæ±z¯àÀ°§Ú¸ÑºÃ
½Ð°Ý ¤U­±³o¦C ³Ì«áªº2,0¬O¥Nªí¤°»ò? ÁÂÁÂ
   =VLOOKUP($E2,IF(ISNUMBER($F2:$G2),$A$2:$A$26,$B$2:$B$26),2,0)

01-1..JPG
2020-3-13 08:19

TOP

¦^´_ 3# lcctno


    VLOOKUP($E2,IF(ISNUMBER($F2:$G2),$A$2:$A$26,$B$2:$B$26),2,0)
«e¤èIF(ISNUMBER($F2:$G2)...¥h§PÂ_¬O¶R©Î¬O½æ¨Ó¨M©wvlookupªº¬d§äÄæ¦ì»P¦^¶ÇÄæ¦ì¡A
2´N¬O¦^¶Ç«e­z²Õ¦¨½d³òªº²Ä¤GÄæ¡A0´N¬Ofalse§¹¥þ¬Û²Åªº·N«ä¡C

TOP

¦^´_ 4# cucu

~test3.PNG
2020-3-17 10:55


~test-3.zip (86.59 KB)
³o¦¸§Ú±N¶R½æ¤À¶} ¤ñ¸û®e©ö¨Ï¥Î ¦ý§Ú§Ë¤F¤@¾ã¤ÑÁÙ¬O§Ë¤£¦n Àµ½Ð±zªº«ü¾É ÁÂÁÂ
§Ú·Q­n°Ýªº¬OF¦æ(¹ïÀ³½æ»ù)»PI¦æ(¹ïÀ³¶R»ù)ªº¨ç¼Æ¸Ó¦p¦ó¼g
1.·í¦³©e°U»ù½æ®É·|§e²{¥X¹ïÀ³¶R»ù©Î¹ïÀ³½æ»ù
2.¨ä¹ïÀ³¼Æ­È¬°¹ïÀ³¶R»ùA¦æ©Î¹ïÀ³½æ»ùB¦æ¤º¨ú±o
¨Ò¦p ­Y¬O¶R¨ì3.43ªº©e°U»ù ¨º¨ä¹ïÀ³¤§½æ»ù±N¬°3.46
          ­Y¬O½æ¨ì3.43ªº©e°U»ù ¨º¨ä¹ïÀ³¤§¶R»ù±N¬°3.4
3.½Ð¯d·N¤@¤U A¦æ¶R»ù©ÎB¦æ½æ»ù¦³³sÄò¬Û¦P­Èªº¥i¯à
   ¤]¥i¯à¤£¬O³sÄòªº¼Æ­È
4.¬ÛÃö¹ïÀ³¨ìªº(©³¦â)®æ¦¡¤Æªº±ø¥ó§Ú¤w¥[¤J
   ·í¦³¼Æ­È®É·|¨Ì±ø¥ó²£¥Í©³¦â
                    ·PÁ°ª¤âªº±z¨Ó«ü¾É

TOP

¦^´_ 5# lcctno

¦pªG¥Î­ì¥»ªº¨ç¼Æ¼Ò¦¡
    F2=VLOOKUP($E2,IF({1,0},$A$2:$A$6008,$B$2:$B$6008),2,0)
    I2=VLOOKUP($H2,IF({1,0},$B$2:$B$6008,$A$2:$A$6008),2,0)

¦ý¬O¬JµM±z¤w¸g¤À¶}¨Ó¤F¡A¥Îindex+match¨ç¼Æ©Î³\¤ñ¸û®e©ö²z¸Ñ
    F2=INDEX($B$2:$B$6008,MATCH($E2,$A$2:$A$6008,0))
    I2=INDEX($A$2:$A$6008,MATCH($H2,$B$2:$B$6008,0))

TOP

¦^´_ 5# lcctno

¸Õ¸Õ³o­Ó~
½Ð¦bF4Äæ¦ì =LOOKUP(1,0/($E4=A:A),B:B)  '·N«ä¬°±qA:AÄæ¦ì¤¤¬d´M¸òE4¤@¼Ëªº­È«á¡A±a¥XBªºÄæ¦ì­È
        A4Äæ¦ì =LOOKUP(1,0/($H4=B:B),A:A) '¦P¤W¤Ï¦V
VBA ·s¤â¶i¤Æ¤¤

TOP

        ÀR«ä¦Û¦b : ¤p¨Æ¤£°µ¡B¤j¨ÆÃø¦¨¡C
ªð¦^¦Cªí ¤W¤@¥DÃD