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

[µo°Ý] §ì¨ú°Ñ·Ó­¶­±ªº sheet ¦WºÙ

[µo°Ý] §ì¨ú°Ñ·Ó­¶­±ªº sheet ¦WºÙ

¥»©«³Ì«á¥Ñ Genie ©ó 2012-6-4 17:42 ½s¿è

½Ð°Ý¦³¨S¦³¿ìªk¦bÀx¦s®æ¤¤Åã¥Ü§Ú¬O±q­þ­Ó sheet §ì¨ú¸ê®Æªº sheet ¦WºÙ¡H

¦]¬°§Ú¦³¤@­ÓÀɮס@¨C­Ó sheet ³£ªø±o«Ü¹³
¦³®É­Ô·|¦³ÂIÃø§PÂ_¬O±q­þ¤@­Ó sheet §ì¨úªº¸ê®Æ
©Ò¥H·Q­n·s¼W¤@Äæ¨ÓÅã¥Ü§Ú¬O±q­þ­Ó sheet §ì¨úªº

ªþ¤W²³æªº½d¨Ò
§Æ±æ¯àÀ°À°¦£
ÁÂÁ¡ã
½d¨Ò.zip (2.95 KB)

¦^´_ 1# Genie

C2=IF(ISERROR(VLOOKUP(A2,'T7'!$A:$B,2,0)),"P123","T7")

TOP

½Ð°Ý§Ú­Y¦³¤»­Ó sheet ªº¸Ü
³o¼Ë¦b B Äæ´N­n¥Î¤­­Ó IF
¦b C Äæ¤S¥Î¤­­Ó IF ¥h§ä¥X¬O¹ïÀ³¨ì­þ­Ó sheet
³o¼Ë·í¸ê®Æ¶q¤@¦h®É¡@¾ã­Ó excel ¶]°_¨Óªº³t«×¬O«ÜºCªº
©Ò¥H·Q°Ý¦³¨S¦³¿ìªk¦b C Ä椣­n¥Î IF ªº±¡ªp¤U¥h§ä¥X©Ò¹ïÀ³ªº sheet¡H

TOP

¥i¯à»Ý­n¨Ï¥ÎVBA¨Ó¼¶¼g¦^õXÄæ¦ì¸ê°T¡C³o¥i¯à»Ý­n¤@ÂI®É¶¡¥h¼¶¼g¡C(¤p§Ì¥\¤O¦³­­)

TOP

¦^´_ 3# Genie

¦Û©w¸q¨ç¼Æ
  1. Function LocationName(Fn As Range, Rng As Range, item_name As Integer) As String
  2. Dim A As Range
  3. For Each sh In Sheets
  4.   With sh
  5.   If sh.Name <> ActiveSheet.Name Then
  6.   Set A = .Range(Rng.Address(, , xlA1)).Find(Fn)
  7.   If Not A Is Nothing Then
  8.     Select Case item_name 'item_name=0¶Ç¦^¤u§@ªí¦WºÙ,item_name=1¶Ç¦^Àx¦s®æ¦ì§}
  9.     Case 0
  10.     LocationName = .Name
  11.     Case 1
  12.     LocationName = A.AddressLocal
  13.     End Select
  14.     Set A = Nothing
  15.     Exit For
  16.   End If
  17.   End If
  18. End With
  19. Next
  20. End Function
½Æ»s¥N½X
SheetName.zip (10.27 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¤£¦n·N«ä¡@³]©w A ªº³o¸Ì§Ú¬Ý¤£¤ÓÀ´¡@¤£ª¾¹D¥i¤£¥i¥H¸ÑÄÀ¤@¤U¡H
ÁÂÁ¡ã
Set A = .Range(Rng.Address(, , xlA1)).Find(Fn)

TOP

¦^´_ 6# Genie

§ä¨ìaÄæ¹ïÀ³ªºÀx¦s®æ
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ Genie ©ó 2012-6-5 14:09 ½s¿è

§Ú®M¥Î¨ì¨ä¥¦ªº excel ÀÉ®×
¦ý¬O¥u·|±a¥X²Ä¤@­Ó sheet ªº sheet ¦WºÙ
¨ä¥¦ªº sheet ¦WºÙ³£Åã¥Ü¬°ªÅ¥Õ
³o¬O§_¬O¦]¬°¨C­Ó sheet ªºÄæ¦ì³£¤£¤@¼ËªºÃö«Y¡H
¡]¦³ªº sheet ¬O±q A Äæ¶}©l°õ¦æ vlookup¡A¦³ªº¬O±q B Äæ¶}©l°õ¦æ vlookup¡^

¥t¥~
¦b LocationName ¨ç¼Æªº²Ä¤G­ÓÄæ¦ì
­Y¬O¿ï¨ú¾ãÄæ $B¡G$B Åã¥Ü¤£·|¦³°ÝÃD
¦ý­Y¬O¦³«ü©w½d³ò $B$2¡G$B$50¡@Åã¥Ü¥X¨Óªº·|¬OªÅ¥Õ

½Ð°Ý³o¸Ó¦p¦ó½Õ¾ã©O¡H
ÁÂÁ¡ã

TOP

¦^´_ 8# Genie

¦¹¨ç¼Æ¬O¦b½d³ò¤¤§ä¨ì§A­nªº¸ê®Æ«á¡A¶Ç¦^¸Ó¸ê®Æªº¤u§@ªí¦WºÙ©Î¬OÀx¦s®æ¦ì§}
©Ò¥H¡A=LocationName(A2,$A$1:$A$100,0)
¤@¼Ë¥i¥H¶Ç¦^²Ä¤@­Ó§ä¨ì¸ê®Æªº¤u§@ªí
°²¦pÄæ¦ì¤£½T©w¦bAÄd¡A¥i§ï¬°=LocationName(A2,$A$1:$B$100,0)
¥u­n½d³ò¤º¦³¥X²{¸Óitem´N¯à¶Ç¦^¤u§@ªí¦WºÙ¤~¹ï
¦pªG¤´µMµLªk¹F¨ì§Aªº»Ý¨D¡A½Ð¤W¶Ç§A¥X¿ùªºÀɮרû¡©ú§Aªº»Ý¨D
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦b³o­Ó excel ÀÉ·|§ì¤£¨ì sheet ªº¦WºÙ
½d¨Ò.zip (15.2 KB)

TOP

        ÀR«ä¦Û¦b : ¤f»¡¤@¥y¦n¸Ü¡A¦p¤f¥X½¬ªá¡F¤f»¡¤@¥yÃa¸Ü¦p¤f¦R¬r³D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD