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

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

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

¥»©«³Ì«á¥Ñ jsc0518 ©ó 2018-1-21 20:25 ½s¿è

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¤½¦¡
1.jpg
2018-1-21 20:24


VLOOKUP·Q¥Î­È¨ú¥N
2.jpg
2018-1-21 20:24


test.rar (12.61 KB)
Just do it.

test_ANS.zip (18.6 KB)

½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

TOP

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

TOP

¸Õ¸Õ¬Ý
  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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2018/1/24 12:06): ªþÀɬݬÝ

¦^´_ 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!
Just do it.

TOP

VLOOKUPµ{¦¡¦b§O³B²£¥Í ¦A¥Î½Æ»s-->¿ï¾Ü©Ê¶K¤WC4:C7-->¿ï­È-->¥u¦³¼Æ¦r
§ïVLOOKUPµ{¦¡=IFERROR(VLOOKUP(B4&"*",H:I,2,),)
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

        ÀR«ä¦Û¦b : ¥Í®ð¡A´N¬O®³§O¤Hªº¹L¿ù¨ÓÃg»@¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD