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

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

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



¨Ï¥Î±¡¹Ò:

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®¦~

switch.zip (8.67 KB)

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

014¤§¥~, ¬°""

TOP

¥»©«³Ì«á¥Ñ Ãz¨x¹F¤H ©ó 2023-11-28 09:06 ½s¿è

¦^´_ 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½ú«ü±Ð

TOP

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

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

TOP

¦^´_  Ã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~~

TOP

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


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

°õ¦æ«e:


°õ¦æµ²ªG:


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
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤Ó¶§¥ú¤j¡B¤÷¥À®¦¤j¡B§g¤l¶q¤j¡A¤p¤H®ð¤j¡C
ªð¦^¦Cªí ¤W¤@¥DÃD