发表于 2009-1-5 19:48
| 
鐢‥xcel瀹忓疄鐜板伐璧勮〃宸ц浆宸ヨ祫鏉
鍙戝竷: 2008-7-01 15:18 | 浣滆: admin | 鏌ョ湅: 90娆
鐢‥xcel瀹忓疄鐜板伐璧勮〃宸ц浆宸ヨ祫鏉
鐑
銆愬瓧浣擄細灏 澶с
鐢‥xcel瀹忓疄鐜板伐璧勮〃宸ц浆宸ヨ祫鏉
浣滆咃細- 鏂囩珷鏉ユ簮锛- 鐐瑰嚮鏁帮細590 鏇存柊鏃堕棿锛2007-8-28 
鏂/閭撲簹鎬
銆銆Excel鍏锋湁寮哄ぇ鐨勬暟鎹鐞嗗拰鎵撳嵃杈撳嚭鍔熻兘锛屽苟涓旀槗瀛︽槗鐢紝鏄箍澶х敤鎴峰枩娆娇鐢ㄧ殑鐢靛瓙琛ㄦ牸澶勭悊杞欢銆傜幇鍦ㄤ竴浜涘崟浣嶇殑璐細浜哄憳鍠滄鐢‥xcel鎵撳嵃鏈崟浣嶇殑鑱屽伐宸ヨ祫鎬昏〃涓庡伐璧勬潯锛屼絾鍦‥xcel涓灏嗗伐璧勬昏〃锛堝琛1锛夋墜宸ュ湴杞崲涓哄伐璧勬潯锛堝琛2锛夊垯鏄竴浠舵瘮杈冪儲鐞愮殑浜嬶紝涓嬮潰鏄垜缂栧啓鐨勪竴涓狤xcel瀹忥紝杩愯杩欎釜瀹忓氨鍙皢缂栬緫濂戒簡鐨勫伐璧勬昏〃寰堟柟渚垮湴杞崲涓哄伐璧勬潯鎵撳嵃杈撳嚭銆
琛1宸ヨ祫琛
缂栧彿
鏃ユ湡
濮撳悕
鑱屽姟宸ヨ祫
鍚勭琛ヨ创
搴旈鏁
鎵ｆ按鐢
鍏朵粬鎵ｆ
瀹炲彂鏁
001
200109
閭撳崼骞
432.50
525.50
958.00
80.50
15
862.50
002
200119
鎴愭枃鍕
425.00
500.00
925.50
75.00
15
835.50
鈥
鈥
鈥
鈥
鈥
鈥
鈥
鈥
鈥
銆
琛2宸ヨ祫鏉
缂栧彿
鏃ユ湡
濮撳悕
鑱屽姟宸ヨ祫
鍚勭琛ヨ创
搴旈鏁
鎵ｆ按鐢
鍏朵粬鎵ｆ
瀹炲彂鏁
001
200109
閭撳崼骞
432.50
525.50
958.00
80.50
15
862.50
缂栧彿
鏃ユ湡
濮撳悕
鑱屽姟宸ヨ祫
鍚勭琛ヨ创
搴旈鏁
鎵ｆ按鐢
鍏朵粬鎵ｆ
瀹炲彂鏁
002
200109
鎴愭枃鍕
425.50
500.50
925.50
75.00
15
835.50
銆銆鍦‥xcel涓柊寤轰竴涓枃浠讹紝灏嗗叾鍛藉悕涓衡滃伐璧勮〃涓庡伐璧勬潯鈥濓紝鍦ㄥ伐浣滆〃鈥渟heet1鈥濅腑杈撳叆骞剁紪杈戝ソ鏈崟浣嶈亴宸ュ伐璧勬昏〃锛堝琛1鎵绀猴級鍚庯紝鐐瑰嚮鈥滃伐鍏封濊彍鍗曗啋鈥滃畯鈥濃啋鈥滃畯鈥︹濃啋杈撳叆瀹忓悕鈥滅敓鎴愬伐璧勬潯鈥濃啋鍒涘缓锛岃緭鍏ュ涓嬬殑瀹忕殑鍚勮鏂囨湰锛岃緭鍏ュ畬鎴愬悗淇濆瓨璇ュ畯銆傚皢宸ヤ綔琛ㄢ渟heet1鈥濆鍒朵负鍙︿竴涓伐浣滆〃鈥渟heet2鈥濅腑锛屼娇鈥渟heet2鈥濇垚涓哄綋鍓嶅伐浣滆〃锛屾墽琛屽垰鎵嶅垱寤虹殑瀹忥紝鍗冲彲寰堝揩灏嗚〃1鎵绀虹殑宸ヨ祫琛ㄨ浆鎹负琛2鎵绀虹殑宸ヨ祫鏉°
銆銆瀹忕殑鍐呭濡備笅锛
銆銆Sub 鐢熸垚宸ヨ祫鏉()
銆銆Cells.Select
銆銆'閫夋嫨鏁翠釜琛ㄥ幓鎺夎〃鏍肩嚎
銆銆Range("F1").Activate
銆銆Selection.Borders(xlDiagonalDown).Line
銆銆Style = xlNone
銆銆Selection.Borders(xlDiagonalUp).LineStyle = xlNone
銆銆Selection.Borders(xlEdgeLeft).LineStyle = xlNone
銆銆Selection.Borders(xlEdgeTop).LineStyle = xlNone
銆銆Selection.Borders(xlEdgeBottom).LineStyle = xlNone
銆銆Selection.Borders(xlEdgeRight).LineStyle = xlNone
銆銆Selection.Borders(xlInsideVertical).LineStyle = xlNone
銆銆Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
銆銆Rows("2:2").Select
銆銆'閫夋嫨绗2琛
銆銆Selection.Insert Shift:=xlDown
銆銆 '鍦ㄧ2琛屽墠鎻掑叆涓琛岋紝淇濇寔绗2琛
銆銆涓洪変腑鐘舵
銆銆num=150
銆銆'鎬讳汉鏁懊3锛屽宸ヨ祫琛ㄤ腑鏈100浜哄垯
銆銆涓100脳3鍗硁um=300
銆銆col=14
銆銆'宸ヨ祫琛ㄧ殑鏍忔暟锛屽宸ヨ祫琛ㄦ湁17鏍忓垯
銆銆 'col=17
銆銆num1 = 4
銆銆Do While num1 <= num
銆銆'寰幆鎻掑叆绌鸿
銆銆Range(Cells(num1, 1), Cells(num1, col)).Select
銆銆'閫変腑绗琻um1琛岀殑绗1鍒楀埌绗琧ol鍒
銆銆Selection.Insert Shift:=xlDown
銆銆Selection.Insert Shift:=xlDown
銆銆num1 = num1 + 3
銆銆Loop
銆銆Range(Cells(1, 1), Cells(1, col)).Select
銆銆Application.CutCopyMode = False
銆銆'鍓垏澶嶅埗妯″紡鏃犳晥
銆銆Selection.Copy
銆銆'澶嶅埗閫夋嫨鍖哄煙
銆銆Range("A2").Select
銆銆'閫夋嫨A2鍗曞厓鏍
銆銆ActiveSheet.Paste
銆銆'浠嶢2鍗曞厓鏍艰捣绮樿创鍐呭
銆銆num2 = 5
銆銆Do While num2 <= num
銆銆'寰幆鎻掑叆鏍囬琛
銆銆Range(Cells(1, 1), Cells(1, col)).Select
銆銆Application.CutCopyMode = False
銆銆Selection.Copy
銆銆Cells(num2, 1).Select
銆銆ActiveSheet.Paste
銆銆num2 = num2 + 3
銆銆Loop
銆銆Range(Cells(2, 1), Cells(3, col)).Select
銆銆Application.CutCopyMode = False
銆銆Selection.Borders(xlDiagonalDown).LineStyle
銆銆 = xlNone
銆銆 '瀹氫箟琛ㄦ牸杈规绾裤佸唴绾挎牱寮
銆銆Selection.Borders(xlDiagonalUp).LineStyle = xlNone
銆銆With Selection.Borders(xlEdgeLeft)
銆銆.LineStyle = xlDouble
銆銆.Weight = xlThick
銆銆.ColorIndex = xlAutomatic
銆銆End With
銆銆With Selection.Borders(xlEdgeTop)
銆銆.LineStyle = xlDouble
銆銆.Weight = xlThick
銆銆.ColorIndex = xlAutomatic
銆銆End With
銆銆With Selection.Borders(xlEdgeBottom)
銆銆.LineStyle = xlDouble
銆銆.Weight = xlThick
銆銆.ColorIndex = xlAutomatic
銆銆End With
銆銆With Selection.Borders(xlEdgeRight)
銆銆.LineStyle = xlDouble
銆銆.Weight = xlThick
銆銆.ColorIndex = xlAutomatic
銆銆End With
銆銆With Selection.Borders(xlInsideVertical)
銆銆.LineStyle = xlDash
銆銆.Weight = xlThin
銆銆.ColorIndex = xlAutomatic
銆銆End With
銆銆With Selection.Borders(xlInsideHorizontal)
銆銆.LineStyle = xlDash
銆銆.Weight = xlThin
銆銆.ColorIndex = xlAutomatic
銆銆End With
銆銆Selection.Copy
銆銆Range(Cells(5, 1), Cells(6, col)).Select
銆銆Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
銆銆 False, Transpose:=False
銆銆'鎺ヤ笂琛屽垹闄や笂琛屽熬鐨勮繛瀛楃
銆銆 _锛屽鍒惰〃鏍肩嚎鏍峰紡
銆銆num3 = 8
銆銆Do While num3 锛= num
銆銆'寰幆澶嶅埗琛ㄦ牸绾挎牱寮
銆銆Range(Cells(num3, 1), Cells(num3 + 1, col))
銆銆.Select
銆銆Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
銆銆 False, Transpose:=False
銆銆num3 = num3 + 3
銆銆Loop
銆銆Rows("1:1").Select
銆銆'鍒犻櫎澶氫綑鐨勪竴琛
銆銆Selection.Delete
銆銆End Sub
銆銆浠ュ悗姣忔湀瑕佹墦鍗板伐璧勮〃涓庡伐璧勬潯鏃讹紝鍙渶灏嗏滃伐璧勮〃涓庡伐璧勬潯鈥濇枃浠舵墦寮锛屼慨鏀瑰ソ宸ヤ綔琛ㄢ渟heet1鈥濅腑鐨勫綋鏈堢殑宸ヨ祫鎬昏〃鏁版嵁鍚庡皢鍏跺鍒朵负宸ヤ綔琛ㄢ渟heet2鈥濓紝骞朵娇鈥渟heet2鈥濇垚涓哄綋鍓嶅伐浣滆〃锛屾墽琛屽畯鈥滅敓鎴愬伐璧勬潯鈥濆嵆鍙
