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

[µo°Ý] Äæ¦ì¤º®e¥]§tABC¤å¦r§PÂ_

[µo°Ý] Äæ¦ì¤º®e¥]§tABC¤å¦r§PÂ_

¦U¦ì¤j¤j:


½Ð¨DÀ°¦£Àɮפº®e§PÂ_¶·¦p¦ó¼g,¨D¸Ñ~

»Ý¨D
1. ¤KÂI°µ²Ä¤@µ§ªá1.5¤p®É,CÄæ¦ì¦Û°Ê­pºâ: ¤KÂI¥[1.5¤p®É¬°09:30 ; °µ²Ä¤Gµ§ªá8¤p®É¦Û°Ê­pºâ:09:30¥[8¤p®É¬°17:30 ; cÄæ¦ì¨Ì§Ç
2. §PÂ_A3Äæ¦ì¤º®e¦³¥]§tABC¤å¦rD3Äæ¦ìÅã¥Ü: ¤Wñ   ;  §PÂ_A3Äæ¦ì¤º®e­Y¦³¥]§tADED¤å¦rÅã¥Ü: ¤Uñ ; ¨ä¾l¨S¦³¥]§tªºDÄæ¦ìÅã¥Ü: ²¤¹L
2022-09-17_233025.jpg

§PÂ_TEST.zip (7.16 KB)

¥]§t¤å¦r§PÂ_

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 2# hcm19522


hcm19522 :
¤§«eÁ٥Πif ¥[¤W%(¸U¥Î¦r¤¸) §PÂ_Äæ¦ì¤º®e¦³ABC¬ÛÃö¤å¦r, ¤@ª½¸Õ¤£¥X¨Ó»¡,  ·PÁ hcm19522 ¨ó§U¸Ñµª~

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-9-19 10:07 ½s¿è

¦^´_ 1# jackyliu


    ÁÂÁ«e½úµoªí¦¹¥DÃD
VBA¤è¦¡¨Ñ°Ñ¦Ò
«á¾Ç¤µ¤Ñ±Ëªñ¨D»·
1.½m²ßÅܼƦW°Ñ¼Æ¤Æ
2.§ó¿Ëªñ¦r¨å

«á¾Ç«Ü·Q¾Ç²ß¤£¤@¼Ëªº§Þ¥©! ¥ýÁÂÁ¦U¦ì«e½úÀ°­q¥¿¿ù»~¨Ã¦A«ü¾É!
  1. Option Explicit
  2. Sub Äæ¦ì¤º®e¥]§tABC¤å¦r§PÂ_()
  3. 'BÄæ¿é¤J®É¼Æ,CÄæ§e²{²Ö¥[«á®É¶¡
  4. 'AÄæ¥]§tABC,¦bDÄæ="¤Wñ"
  5. 'AÄæ¥]§tADEM,¦bDÄæ="¤Uñ"
  6. 'AÄ椣¥]§tABC¥B¤£¥]§tADEM,¦bDÄæ="²¤¹L"
  7. 'AÄæ¥]§tABC¥B¥]§tADEM,¦bDÄæ="¤Wñ+¤Uñ"
  8. Dim Arr, i, Y, Z(1 To 3)
  9. Set Y = CreateObject("Scripting.Dictionary")
  10. Y = Array("¤Wñ", "¤Uñ", "²¤¹L")
  11. Arr = [A2].CurrentRegion
  12. For i = 2 To UBound(Arr)
  13.    Z(1) = Arr(i, 2) / 24 + Arr(i - 1, 3)
  14.    Z(2) = InStr(Arr(i, 1), "ABC")
  15.    Z(3) = InStr(Arr(i, 1), "ADEM")
  16.    Arr(i, 3) = IIf(Arr(i, 2), Z(1), "")
  17.    If Z(2) <> 0 And Z(3) <> 0 Then
  18.       Arr(i, 4) = Y(0) & "+" & Y(1)
  19.       ElseIf Z(2) = 0 And Z(3) = 0 Then
  20.          Arr(i, 4) = Y(2)
  21.       ElseIf Z(3) = 0 Then
  22.          Arr(i, 4) = Y(0)
  23.       Else
  24.          Arr(i, 4) = Y(1)
  25.    End If
  26. Next
  27. [A2].Resize(UBound(Arr), UBound(Arr, 2)) = Arr
  28. End Sub
½Æ»s¥N½X

TOP

=LOOKUP(,0/FIND({"","ADEM","ABC"},A3),{"²¤¹L","¤Uñ","¤Wñ"})

TOP

§Ú·Q½Ð°Ý­Y§ï¦¨¥ÎSearch¨ç¼Æ¥i¥H¶Ü?
§ä¨ì¹ïÀ³ªº¬ÛÃö¤å¦r,Åã¥Ü¬ÛÃö¤å¦r¦p: ¤W¤WÅÒ , ¤W¤¤ÅÒ , ¤W¤UÅÒ , ¤¤¤WÅÒ , ¤¤¤¤ÅÒ , ¤¤¤UÅÒ , ¤U¤WÅÒ , ¤U¤¤ÅÒ , ¤U¤UÅÒ ,

§ï·s»Ý¨D:
1. §ï¦¨¥ÎSearch¨ç¼Æ
2. §ï¦¨±N­n§äªº¤å¦r²Î¤@©ñ¦b¬Y¤@Äæ¦ì(Á|¨Ò: Äæ¦ìA1:ABC   ,Äæ¦ìA2:BCD   ,Äæ¦ìA3:CDE   ,Äæ¦ìA4:Def   ,Äæ¦ìA5:efG  ,Äæ¦ìA6:FGh)
3. ±N§ä¨ì¬ÛÃö¤å¦r§ïABC=¤W¤WÅÒ,  BCD=¤W¤¤ÅÒ,   BCD=¤W¤UÅÒ....
¥H¤W §ó·s¤è¦¡, ¥i¥H°µ¨ì¶Ü?

TOP

¦^´_ 6# jackyliu

§âÃöÁä¦r©ñG:HÄæ//¤£¤À¤j¤p¼g
D3¤½¦¡
=iferror(LOOKUP(,0/(SEARCH(G$1:G$99,"|"&A3)>1),H:H),"")

TOP

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


ÁÂÁª©¤j¨ó§U ~
¤£¹L²{¦b ¦sÀɩνƻs¹q¸£³£·|Âà°é°é,·|ªá¤W1~2¤ÀÄÁ®É¶¡,¬O¥¿±`²{¶H¶Ü?

TOP

¦^´_ 8# jackyliu


¸ê®Æ¦h, ¤½¦¡¦h, ¥d...¬O¥¿±`,
­n¬ÝÀÉ®×, ¤~ª¾¬O­þ³¡¥÷©ìºCÀÉ®×,
¥²­n®É, ¥u¯à¥Îvba

TOP

¦^´_ 6# jackyliu


    ÁÂÁ«e½ú
¤µ¤é²ß±o
iferror() , LOOKUP() , SEARCH()
¼ÒÀÀ±¡¹Ò½m²ß°}¦C&¦r¨å
VBA¤è¦¡¨Ñ°Ñ¦Ò

§PÂ_TEST-20220926_1.zip (30.99 KB)
2022-09-26_154047.JPG
2022-9-26 16:16



2022-09-26_154110.JPG
2022-9-26 16:17

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¯Ê¤fªºªM¤l¡A¦pªG´«¤@­Ó¨¤«×¬Ý¥¦¡A¥¦¤´µM¬O¶êªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD