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

[µo°Ý] ¦h±ø¥ó¦¡¨Dµª

[µo°Ý] ¦h±ø¥ó¦¡¨Dµª

¥»©«³Ì«á¥Ñ PJChen ©ó 2018-4-26 22:33 ½s¿è

¤j¤j¦n,

§Ú¸Õ¹L¥Îvlookup¥u¯à§ä¨ì²Ä¤@­Ó®Æ¸¹,¦Ó¥Îlookup¤S¥u¯à§ä¨ì³Ì«á¤@­Ó®Æ¸¹,
½Ð°Ý¹³¥H¤Uªº¦hºØ±ø¥ó³]©w,·|²£¥Í¦h­Ó®Æ¸¹,¥i¥H¥Î¤°»ò¨ç¼Æ¨D±o©Ò¦³ªº®Æ¸¹¡H(§Ú¥u¥Î¤Ö¼Æªº½d¨Ò,¹ê»Ú¤W¦³¥i¯à§ä¨ìªº®Æ¸¹¦b3­Ó¥H¤W,­n«ç¼ËÅý¥¦¥þ³¡³£¦C¥X¨Ó¡H)
¦h±ø¥ó¨Dµª.rar (179.51 KB)

²Å¦X¤T­Ó±ø¥ó
»â®Æsheet EÄæ=­q³æsheet DÄæ
»â®Æsheet QÄæ=­q³æsheet AAÄæ
»â®Æsheet BBÄæ= 1
«h:
­q³æMSO18010017
¥i¥H±q
»â®ÆsheetªºABÄæ§ä¨ì3­Ó®Æ¸¹,¶ñ¦b ­q³æsheet BQÄ椤,¤£¦P®Æ¸¹¤¤¶¡¥Î¤@­ÓªÅ®æ¹j¶}
550-000-000-0010
550-000-000-0002
153-211-166-0002

­q³æMSO15090032¥H¬Û¦P¤è¦¡,¥i¥H§ä¨ì2­Ó®Æ¸¹
155-201-300-0001
150-321-000-0006

­q³æMSO16110013¥H¬Û¦P¤è¦¡,¥i¥H§ä¨ì2­Ó®Æ¸¹
150-211-000-0014
152-211-000-0010

¦^´_ 1# PJChen

½Ð±Ð¨ç¼Æ°ª¤â¡G

¬O§_³o­Ó¥\¯à¨S¦³¤½¦¡¥i¥H¹F¦¨¡H

TOP

§Úªº¸ÑÀ£³nÅé¤ÓÂÂ, ¸Ñ¤£¶}À£ÁYÀÉ!!!

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2018-4-28 12:16 ½s¿è

¦^´_ 3# ­ã´£³¡ªL

¤W¶Ç¤å¥ó¥u¯à¥ÎÀ£ÁY®æ¦¡,½Ð°ÝÁÙ¦³¤°»ò¤èªk¥i¥H¤W¶ÇÀɮסH

ÁÙ¬O ±z¤è«K§ó·s¸ÑÀ£³nÅé¶Ü¡H winrar 5.5 (¥i¥Ã¤[¯}¸Ñ)
https://mega.nz/#F!GZAwFDqY!jPFljQ7hCHlwsasC_OWD8Q

TOP

³o¤½¦¡µLªk°µ, ¥u¯à¥ÎVBA
  1. Sub Test()
  2. Dim R&, i&, Arr, Brr, Crr, Drr, xD, T$, U
  3. R = [­q³æ!D1].Cells(Rows.Count, 1).End(xlUp).Row
  4. Arr = [­q³æ!A1:AA1].Resize(R)
  5. ReDim Brr(1 To R - 1, 0)
  6. Set xD = CreateObject("Scripting.Dictionary")
  7. For i = 2 To R
  8.     T = Arr(i, [D1].Column) & Arr(i, [AA1].Column)
  9.     xD(T) = i - 1
  10. Next i

  11. R = [»â®Æ!E1].Cells(Rows.Count, 1).End(xlUp).Row
  12. Arr = [»â®Æ!A1:BB1].Resize(R)
  13. For i = 2 To R
  14.     If Val(Arr(i, [BB1].Column)) <> 1 Then GoTo 101
  15.     T = Arr(i, [E1].Column) & Arr(i, [Q1].Column)
  16.     U = xD(T): If U = 0 Then GoTo 101
  17.     Brr(U, 0) = Trim(Brr(U, 0) & " " & Arr(i, [AB1].Column))
  18. 101: Next i

  19. [­q³æ!BQ2].Resize(UBound(Brr)) = Brr
  20. End Sub
½Æ»s¥N½X
Xl0000397.rar (105.13 KB)

TOP

¦^´_ 5# ­ã´£³¡ªL

½Ð°Ý±z,

¦b­q³æ.sheet
BQ2=IFERROR(IF($AY2="³]³Æ",LOOKUP(2,1/(([¥X³f¤å¥ó.xlsx]»â®Æ!$E:$E=$D2)*([¥X³f¤å¥ó.xlsx]»â®Æ!$Q:$Q=$AA2)*([¥X³f¤å¥ó.xlsx]»â®Æ!$AM:$AM=$AY2)),[¥X³f¤å¥ó.xlsx]»â®Æ!$AB:$AB),"«D³]³Æ"),"")
¥i¥H±o¨ì³Ì«á¤@­Ó­È153-211-166-0002

¦³¨S¦³Ãþ¦üªº¤èªk,¥i¥H¨Ì§Ç¨D±o²Ä1,2,3...ªº­È¡H¨Ì¦¹Ãþ±À...

TOP

¦^´_ 6# PJChen


­n§â3­ÓªF¦è©ñ¦b¦P¤@Àx¦s®æ, ¥²¶·¥Î§ó°ªª©¥»ªºEXCEL~~
§Ú¥u¥Î2003, ¨S¿ìªk~~

TOP

¦^´_ 7# ­ã´£³¡ªL

¦pªG¦³³o¼Ëªº¤è¦¡´N¤Ó¦n¤F¡A§Ú¥Îªºª©¥»¬OExcel 2016,±z¥i¥H§â¤½¦¡¶Kµ¹§Ú¸Õ¸Õ¬Ý¶Ü¡H«ô°U¤F...

TOP

¦^´_ 7# ­ã´£³¡ªL
¤S©ÎªÌ¥þ³¡©ñ¦b¦P¤@­ÓÀx¦s®æ¤ÓÃø¹F¦¨ªº¸Ü,¤]¥i¥Hµ¹§Ú¥t¤@­Ó¤è¦¡¡A¤ñ¦p»¡BQ2ªº³¡¥÷¦³3­Ó®Æ¸¹,§Ú¦A¦Û¦æ·s¼W©Ò»Ýªº¦C¼Æ,¥u­n¯à¥Î¦P¤@¤½¦¡§ì¨ú²Ä1.2.3.....¨Ì¦¹Ãþ±Àªº¶¶§Çµª®×,´N¬OµL½×¦³´X­Ó®Æ¸¹,§Ú´N¦Û¦æ¼W¥[´X­Ó¦C¼Æªº·N«ä,³o¼Ë¯à¦æ¶Ü¡H

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

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