- ©«¤l
- 129
- ¥DÃD
- 25
- ºëµØ
- 0
- ¿n¤À
- 159
- ÂI¦W
- 0
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-12-24
- ³Ì«áµn¿ý
- 2022-12-12
|
¥»©«³Ì«á¥Ñ iceandy6150 ©ó 2020-5-18 23:12 ½s¿è
¦^´_ 6# ã´£³¡ªL
ª©¥D±z¦n¡A§Ú«á¨Ó·Q¤FÓ¤g¤èªk
§Úª¾¹D¥i¥H¿ï©w¤@Ó¤j½d³ò¡Aµ¹¥L©w¸q¦WºÙ
µM«áVLOOKUP³oÓ¨ç¼Æ¥u¯à§ä...¤@Ó½d³ò¤º¡A³Ì¥ªÃ䨺Äæ·í¼Ð·Ç
©Ò¥H쥻 AÄæ BÄæ CÄæ¡A´N½Æ»s¨ì§Oªº¦a¤è¡AÅܦ¨CÄæ¡ABÄæ¡AAÄæ
µM«áVLOOKUP CÄæ¡A¥i¥H§ä¨ì¬Û¹ïÀ³ªºA¸òB
(¨ä¹ê¥ÎFIND¦n¹³¤]¥i¥H)
¦Ü©ó¤U©Ô¦¡¿ï³æªº¤º®e¡A¦]¬°¶i¶¥¿z¿ï¹L«á¡A§â¿z¿ï¹Lªº½d³ò¡A©w¸q¦WºÙ
¦A§â³oÓ¦WºÙµ¹¤U©Ô¦¡¿ï³æ (¨Ó·½¬°ÅܼÆ)
³oÃän°t¦X INDIRECT ³oÓ»yªk¡A¤~¯à¥¿±`¨Ï¥Î¡A¤]¬Oºô¸ô¬d¨ìªº
¥H¤U¬Oµ{¦¡½X
¸ò´ú¸ÕÀÉ®×
(¥Ø«e¦Û°Ê²£¥Í¤U©Ô²M³æ¥u³]©w5®æ)- Private Sub CommandButton1_Click()
- Range("A2:C20").Select
- Selection.ClearContents
- With Selection.Validation
- .Delete
- .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
- :=xlBetween
- .IgnoreBlank = True
- .InCellDropdown = True
- .IMEMode = xlIMEModeNoControl
- .ShowInput = True
- .ShowError = True
- End With
-
- Range("C2").Select
-
- End Sub
- '*******************************************************************************
- Private Sub CommandButton2_Click()
- Dim a, b, c, d
- Sheets("¤u§@ªí2").Select
- With Sheets("¤u§@ªí2")
- '¨C¦¸°õ¦æ¥ý±N³Ì¥kÃä¥|Äæ¬å¥ú
- .Columns(.Columns.Count).Delete Shift:=xlShiftLeft
- .Columns(.Columns.Count - 1).Delete Shift:=xlShiftLeft
- .Columns(.Columns.Count - 2).Delete Shift:=xlShiftLeft
- .Columns(.Columns.Count - 3).Delete Shift:=xlShiftLeft
- '[¥Î³~Ãþ§O]¥ý½Æ»s¨ì³Ì¥kÃä˼ƲĤTÄæ
- .Columns("C:C").Select
- Selection.Copy
- Sheets("¤u§@ªí2").Columns(.Columns.Count - 2).PasteSpecial (xlPasteAll)
- Application.CutCopyMode = False
- '[¥Î³~§O]¥ý½Æ»s¨ì³Ì¥kÃä˼ƲĤGÄæ
- .Columns("B:B").Select
- Selection.Copy
- Sheets("¤u§@ªí2").Columns(.Columns.Count - 1).PasteSpecial (xlPasteAll)
- Application.CutCopyMode = False
-
-
- '[¤u§@pµe]¥ý½Æ»s¨ì³Ì¥kÃä˼ƲĤ@Äæ
- .Columns("A:A").Select
- Selection.Copy
- Sheets("¤u§@ªí2").Columns(.Columns.Count).PasteSpecial (xlPasteAll)
- Application.CutCopyMode = False
-
- '¶i¶¥¤£«½Æ¿z¿ï--©ñ¨ì...³Ì¥kÃä˼Ʋĥ|Äæ
- .Columns("C:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Columns(.Columns.Count - 3), Unique:=True
- Application.CutCopyMode = False
-
- a = Sheets("¤u§@ªí2").Columns(.Columns.Count - 3).End(xlDown).Row '¬d¬Ý(¬ì¥Ø)¦³¦h¤ÖӼƶq
- b = .Columns.Count - 3 'b¬°²Ä´XÄæ(³Ì¥kÃä˼Ʋĥ|Äæ)
-
-
- '³]©w--©w¸q½d³ò
- ActiveWorkbook.Names.Add Name:="abc", RefersToR1C1:="=¤u§@ªí2!R2C" & b & ":R" & a & "C" & b
- ActiveWorkbook.Names("abc").Comment = ""
-
-
- '³]©w¤U©Ô¦¡²M³æ
- Sheets("¤u§@ªí1").Select
- Range("C2").Select
- With Selection.Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
- xlBetween, Formula1:="=INDIRECT(""abc "")"
- .IgnoreBlank = True
- .InCellDropdown = True
- .InputTitle = ""
- .ErrorTitle = ""
- .InputMessage = ""
- .ErrorMessage = ""
- .IMEMode = xlIMEModeNoControl
- .ShowInput = True
- .ShowError = True
- End With
- '½Æ»s¤U©Ô¦¡²M³æ¨ì§O®æ
- Selection.AutoFill Destination:=Range("C2:C5"), Type:=xlFillDefault
- '³]©w ©w¸q½d³ò
- a = Sheets("¤u§@ªí2").Columns(.Columns.Count - 2).End(xlDown).Row '¬d¬Ý³o±i°Ñ·Óªí¦³¦h¤Ö[¥Î³~Ãþ§O]¡A³o±iªíªº©³³¡¨ì¦h§C
- b = .Columns.Count - 2 'b¬°²Ä´XÄæ(³Ì¥kÃä˼ƲĤTÄæ...[¥Î³~Ãþ§O])
-
- Sheets("¤u§@ªí2").Select
- ActiveWorkbook.Names.Add Name:="data", RefersToR1C1:="=¤u§@ªí2!R2C" & b & ":R" & a & "C" & (b + 2) '³oÓ½d³ò¦³¤TÄæ¡A±qb¨ìb+2
- ActiveWorkbook.Names("data").Comment = ""
- '³]©w¦Û°Ê¥X²{-¤èªk2¡AY¹ï·Óªí¤¤¬°ªÅ®æ¡AÅã¥ÜªÅ®æ¡AY¤u§@Ãþ§O(¬ì¥Ø)¥¼¿ï¡AÅã¥ÜªÅ®æ
- '(±q©w¸q¦nªº½d³òdata¤¤¥h§ä¨ì½d³ò¤¤ªº²Ä2Äæ¡A©ñ¨ìB2---B2n©ñ¥Î³~§O¡Adataªº²Ä2Äæ¤]¬O¥Î³~§O)
-
- '¥ý³]©w[¥Î³~§O]
- Sheets("¤u§@ªí1").Select
- Range("B2").Select
- ActiveCell.FormulaR1C1 = _
- "=IFERROR(IF(VLOOKUP(RC3,data,2,0)=0,"""",VLOOKUP(RC3,data,2,0)),"""")"
- '½Æ»s¤½¦¡¨ì§O®æ
- Selection.AutoFill Destination:=Range("B2:B5"), Type:=xlFillDefault
-
- '¦A³]©w[¤u§@pµe]---A2n©ñ¥Î[¤u§@pµe]¡Adataªº²Ä3Äæ¤]¬O[¤u§@pµe])
- Sheets("¤u§@ªí1").Select
- Range("A2").Select
- ActiveCell.FormulaR1C1 = _
- "=IFERROR(IF(VLOOKUP(RC3,data,3,0)=0,"""",VLOOKUP(RC3,data,3,0)),"""")"
- '½Æ»s¤½¦¡¨ì§O®æ
- Selection.AutoFill Destination:=Range("A2:A5"), Type:=xlFillDefault
-
- End With
- Range("C2").Select
- End Sub
½Æ»s¥N½X
¦Û°Ê³]¤U©Ô¦¡¿ï³æ.rar (31.51 KB)
·PÁ¦U¦ì |
|