[µo°Ý] switch§PÂ_Äæ¦ì²Ä¤@­Ó¦r¤¸°ÝÃD

Ãz¨x¹F¤H

¤U¸ü (58.29 KB)

2023-11-27 14:45

¨Ï¥Î±¡¹Ò:

B2Äæ¦ì¤¤¦r¦ê¡A²Ä¤@¦ì¦r¤¸¬°0®É¡AC2Äæ¦ì§e²{BV¦r¦ê

C2Äæ¦ìªºswitch¨ç¼Æ¦p¤U¡A

1. =SWITCH(LEFT($B2),"0",$E$2,"1",$E$3,"4",$E$4)

½Æ»s¥N½X

¦³µo²{B2¤ÎB3ªº¦r¦ê¼Æ¤£·|¦³5­Ó¦r¦ê¡A·Q»¡¥ý¸É0¡AÅܦ¨'00001¹ïÀ³¥X¨Ó¸ê®Æ·|¥X²{#NA

¦³«e½ú¦³§ó¦nªº¤è¦¡¨ç¼Æ´N¯à¹ïÀ³?

ÁÂÁ ·P®¦~

­ã´£³¡ªL

=INDEX(E:E,FIND(LEFT(TEXT(B2&"","00000;9;9;\9")),"+014"&1/17))&""

014¤§¥~, ¬°""

Ãz¨x¹F¤H

¦^´_ 2# ­ã´£³¡ªL
¤£¦n·N«ä¡A§Ú¨SÁ¿²M·¡­nªºµ²ªG


¹ÏB:BÄæ¦ì¤¤¦³­È

1~160¡B10001~10256¡B40001~40512


¦bC:C§e²{B:B­ÈÀ³ÅܼưÏD2:D4ªº¼Æ¦r¡A¦A°t¹ïE2:E4ªº­ÈÅã¥Ü¦AC:CÄæ¦ì

¥u¬O1³o­Ó­È¶}ÀY¤£¬O0¡A¤@¶}©l·Qªk¬O¸É0¦b¹ïÀ³D2:D4ªº­È


«e½úªº¨ç¼Æ§Ú¨Ï¥Î¹L«á®ÄªG¤£¬O§Ú­nªº¡A¤S©ÎªÌ§Ú»~¸Ñ³o¬q¨ç¼Æ¨Ï¥Î

switch¨ç¼Æ­n+¤J«e½úªº³o¬q¨ç¼Æ?

«D±`·PÁ«e½ú«ü±Ð

­ã´£³¡ªL

¦^´_ 3# Ãz¨x¹F¤H

=iferror(VLOOKUP(--LEFT(TEXT(B2,"00000")),D:E,2,),"")

Ãz¨x¹F¤H

¦^´_  Ãz¨x¹F¤H

=iferror(VLOOKUP(--LEFT(TEXT(B2,"00000")),D:E,2,),"")
­ã´£³¡ªL µoªí©ó 2023-11-28 09:52

   ·P®¦ «e½ú¤w¸Ñ¨M~~®ÄªG¬O§Ú»Ý¨D~~

Andy2483

¦^´_ 1# Ãz¨x¹F¤H


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

°õ¦æ«e:

¤U¸ü (93.97 KB)

2023-11-28 16:15

°õ¦æµ²ªG:

¤U¸ü (114.26 KB)

2023-11-28 16:15

Option Explicit
Sub TEST()
Dim Brr, Z, i&
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range([E2], [D65536].End(3))
For i = 1 To UBound(Brr): Z(Val(Brr(i, 1))) = Brr(i, 2) & "": Next
Brr = Range([B2], [B65536].End(3))
For i = 1 To UBound(Brr)
   If Trim(Brr(i, 1)) Like "#*" = False Then Brr(i, 1) = "": GoTo i01
   Brr(i, 1) = Z(Val(Left(Format(Brr(i, 1), "00000"), 1)))
i01: Next
[H2].Resize(UBound(Brr)) = Brr
End Sub