«ü©w"¤½¦¡"Åܦ¨­È

jsc0518

Dear ¥ý¶i,
§Ú¦³¤@­ÓEXCEL¡A¤º®e¥D­n¬O¥ÎVLOOKUP¥h§ä´M¸ê®Æ±a¥X¬Û¹ïªº­È
¦³¤@Äæ¦ì¬O°µ¥[Á`ªºSUM

°ÝÃD¬O§Ú¥u·Qµ¹§Ú¥DºÞ¬Ý¨ìSUMªºÄæ¦ì¤½¦¡¡A¦ÓVLOOKUPªº³¡¤À¡A§Ú·Q¥Î¤@­ÓVBA»yªk±NVLOOKUPÅܦ¨³æ¯Â¼Æ¦r(µLVLOOKUP¤½¦¡)¡A§Ú­n«ç»ò¼gVBA»yªk
*Á|¨Òªº¬O¤@­Ó«Ü²©öªºEXCEL¡A§Ú¥»¨­ªºªí¬O«Ü½ÆÂø¡A«Ü¦hÄæ¦ì¥[Á`¤Î¥ÎVLOOKUP

§Ú¥u·Q«O¯dSUM¤½¦¡

¤U¸ü (36.85 KB)

2018-1-21 20:24

VLOOKUP·Q¥Î­È¨ú¥N

¤U¸ü (42.59 KB)

2018-1-21 20:24

test.rar (12.61 KB)

¤U¸ü¦¸¼Æ: 12

2018-1-21 20:20

kim223824

test_ANS.zip (18.6 KB)

¤U¸ü¦¸¼Æ: 6

2018-1-22 15:33

½d¨Ò¦p¤U:

Private Sub CommandButton1_Click()

    Range("C4").Value = "=IF(ISNA(VLOOKUP($B4,Sheet2!$H:$I,2,0)),0,VLOOKUP($B4,Sheet2!$H:$I,2,0))"
    Range("C5").Value = "=IF(ISNA(VLOOKUP($B5,Sheet2!$H:$I,2,0)),0,VLOOKUP($B5,Sheet2!$H:$I,2,0))"
    Range("C6").Value = "=IF(ISNA(VLOOKUP($B6,Sheet2!$H:$I,2,0)),0,VLOOKUP($B6,Sheet2!$H:$I,2,0))"
    Range("C7").Value = "=IF(ISNA(VLOOKUP($B7,Sheet2!$H:$I,2,0)),0,VLOOKUP($B7,Sheet2!$H:$I,2,0))"
   
    Range("C4:C7").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("C4").Select
   
End Sub

jsc0518

¦^´_ 2# kim223824
·PÁ±zªº¦^ÂСA§Ú¸Õ¸Õ¬Ý
ÁÂÁ±z¡I

GBKEE

¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub Ex()
   
3.     With Sheets("SHEET2")
   
4.         With .Range(.Range("B4"), .Range("B4").End(xlDown)).Offset(, 1)
   
5.             .FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC2,Sheet2!R1C8:R4C9,2,0)),0,VLOOKUP(RC2,Sheet2!R[-3]C[5]:RC[6],2,0))"
   
6.             .Value = .Value
   
7.         End With
   
8.     End With
   
9. End Sub

½Æ»s¥N½X

¦^´_ 3# jsc0518

jsc0518

¦^´_ 4# GBKEE
·PÁ±zªº¦^ÂСA·Q»P±z½Ð±Ð
¦]§Ú¦Û¤v¥»¨­excel¦³³\¦h(¤j¶q)Äæ¦ì¦³§tvlookup¤½¦¡
¬O§_¥i¥ÎVBA¦Û°Ê§ä´MÄæ¦ì¤º¥u­n¦³vlookupªº¨ç¼Æ®É¡A«K±N¸ÓÄæ¦ìÂà´«¦¨­È©O¡H
Thanks!

hcm19522

VLOOKUPµ{¦¡¦b§O³B²£¥Í ¦A¥Î½Æ»s-->¿ï¾Ü©Ê¶K¤WC4:C7-->¿ï­È-->¥u¦³¼Æ¦r
§ïVLOOKUPµ{¦¡=IFERROR(VLOOKUP(B4&"*",H:I,2,),)