如何用宏功能制作电子表

㈠ excel鐢靛瓙琛ㄦ牸 瀹 楂樻坠璇疯繘锛岄珮鍒呜拷锷

Sub 鍒嗙被姹囨()

'瀵箂heet1镄𪞝:C鍒楄繘琛屽垎绫荤粺璁℃眹镐伙纴缁撴灉鏀惧湪sheet2镄𪞝1:E7

Dim s1 As Worksheet: Set s1 = Worksheets("sheet1") '铡熸暟鎹琛
Dim s2 As Worksheet: Set s2 = Worksheets("sheet2") '鏂版暟鎹琛
Dim r(1 To 5) As Integer: r(5) = s1.[A65536].End(xlUp).Row '铡熸暟鎹琛ㄨ屾暟锛屼互A鍒椾负鍑

s2.Cells.Delete: s1.Range("A1:C" & r(5)).Copy s2.Range("A1")
s2.Range("A1:C" & r(5)).Sort Key1:=s2.Range("B2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlSortColumns, SortMethod:=xlPinYin, DataOption1:=xlSortNormal
s2.Range("f1:i1") = Array("A", "B", "C", "D")
s2.Range("e2:e7") = Application.WorksheetFunction.Transpose(Array( _
"骞冲潎鍒", "链楂桦垎", "澶т簬0镄勬渶浣庡垎", "链楂桦垎浜烘暟", "链浣庡垎浜烘暟", "鍒嗘暟鏂瑰樊"))

For i = 1 To 4
r(i) = s2.Range("B:B").Find( _
WHAT:=s2.Cells(1, i + 5), LookIn:=xlValues, LookAt:=xlWhole).Row
Next
r(5) = r(5) + 1

Dim rng(1 To 4) As Range '鍒嗙被鍖哄烟
For i = 1 To 4
Set rng(i) = s2.Cells(r(i), 3).Resize(r(i + 1) - r(i), 1)
Next

Dim rs(1 To 6, 1 To 4) '缁撴灉
For i = 1 To 4
rs(1, i) = Application.WorksheetFunction.Average(rng(i)) '骞冲潎鍒
rs(2, i) = Application.WorksheetFunction.Max(rng(i)) '链楂桦垎
j = 1
Do
temp = Application.WorksheetFunction.Small(rng(i), j): j = j + 1
Loop Until (j > rng(i).Rows.Count) Or (temp > 0)
rs(3, i) = temp '链浣庡垎
rs(4, i) = Application.WorksheetFunction.CountIf(rng(i), rs(2, i)) '链楂桦垎浜烘暟
rs(5, i) = Application.WorksheetFunction.CountIf(rng(i), rs(3, i)) '链浣庡垎浜烘暟
rs(6, i) = Application.WorksheetFunction.Var(rng(i)) '鍒嗘暟鏂瑰樊
Next
s2.Range("F2").Resize(6, 4) = rs
s2.Range("F2:i2", "f7:i7").NumberFormatLocal = "0.00_ "
s2.Range("F3:i6").NumberFormatLocal = "G/阃氱敤镙煎纺"
s2.Range("a:d").Delete
s2.Cells.EntireColumn.AutoFit
s2.Cells.EntireRow.AutoFit
s2.Cells.HorizontalAlignment = xlCenter
s2.Activate
End Sub
-----------
铡熸暟鎹琛 sheet1
鏂版暟鎹琛 sheet2

镓揿紑铡熸暟鎹琛锛屽湪琛ㄥ悕涓婄偣鍙抽敭锛岄夆沧煡鐪嬩唬镰佲濓纴镓揿紑浠ｇ爜绐楀彛锛屽皢涓婇溃镄勪唬镰佸嶅埗鍏ュ叾涓锛屽苟镙规嵁瀹为檯𨱍呭喌锛屽皢浠ｇ爜鍐呯殑琛ㄥ悕鏀逛负瀹为檯琛ㄥ悕锛堟湁娉ㄩ喷锛夌劧钖庯纴鎸 F5 阌杩愯岀▼搴
-----------------
鍏朵粬鍏充簬缁撴灉镄勬牸寮忕瓑闂棰桡纴璇稨i鎴戯纴鎴栫暀娑堟伅锛岃︾粏瑙ｇ瓟

㈡ 濡备綍瀵笶xcel琛ㄦ牸杩涜屽畯缂栬緫锛

瑕佸笶xcel琛ㄦ牸杩涜屽畯缂栬緫锛屾偍鍙浠ユ寜镦т互涓嬫ラゆ搷浣滐细
镓揿紑Excel骞舵墦寮鎭ㄦ兂瑕佺紪杈戠殑宸ヤ綔绨裤
鍦‥xcel涓锛屾寜涓婣LT + F11锛岃繖灏嗘墦寮Visual Basic for Applications锛圴BA锛夌紪杈戝櫒銆
鍦╒BA缂栬緫鍣ㄤ腑锛岄夋嫨"鎻掑叆"锛圛nsert锛夎彍鍗曪纴铹跺悗阃夋嫨"妯″潡"锛圡ole锛夈傝繖灏嗗湪椤圭洰璧勬簮绠＄悊鍣ㄤ腑鍒涘缓涓涓鏂扮殑妯″潡銆
鍦ㄦā鍧楃獥鍙ｄ腑缂栧啓鎭ㄧ殑瀹忎唬镰併傚畯鏄疺BA浠ｇ爜镄勯泦钖堬纴鐢ㄤ簬镊锷ㄦ墽琛岀壒瀹氢换锷°傛偍鍙浠ヤ娇鐢╒BA璇娉曞拰Excel瀵硅薄妯″瀷𨱒ユ搷浣滃拰澶勭悊宸ヤ綔绨裤佸伐浣滆〃鍜屽崟鍏冩牸銆
渚嫔傦纴浠ヤ笅鏄涓涓绠鍗旷殑瀹忕ず渚嬶纴灏嗗湪褰揿墠阃夊畾镄勫崟鍏冩牸涓鎻掑叆鏂囨湰 "Hello, World!"锛
vba 澶嶅埗Sub InsertText()
ActiveCell.Value = "Hello, World!"
End Sub
```
鍦╒BA缂栬緫鍣ㄤ腑锛屾寜涓婩5鎴栫偣鍑诲伐鍏锋爮涓婄殑"杩愯"鎸夐挳銆傝繖灏呜繍琛屾偍镄勫畯銆
杩斿洖Excel绐楀彛锛屾偍鍙浠ユ墽琛屽叾浠栨搷浣滐纴渚嫔傞夋嫨瑕佸簲鐢ㄥ畯镄勫崟鍏冩牸锣冨洿锛屽苟鍐嶆¤繍琛屽畯銆
杩椤彧鏄涓涓绠鍗旷殑绀轰緥锛屾偍鍙浠ユ牴鎹闇瑕佺紪鍐欐洿澶嶆潅镄勫畯浠ｇ爜锛屼互瀹炵幇钖勭嶈嚜锷ㄥ寲浠诲姟鍜屾暟鎹澶勭悊镎崭綔銆
璇锋敞镒忥纴瀹忓姛鑳介粯璁ゆ槸绂佺敤镄勚傚湪绗涓娆¤繍琛屽畯涔嫔墠锛屾偍鍙鑳介渶瑕佸惎鐢‥xcel镄勫畯锷熻兘銆傝佸惎鐢ㄥ畯锷熻兘锛岃锋寜镦т互涓嬫ラゆ搷浣滐细
镣瑰嚮Excel涓镄"鏂囦欢"锛团ile锛夐夐”鍗°
阃夋嫨"阃夐”"锛圤ptions锛夈
鍦"Excel阃夐”"瀵硅瘽妗嗕腑锛岄夋嫨"淇′换涓蹇"锛圱rust Center锛夈
镣瑰嚮"淇′换涓蹇冭剧疆"鎸夐挳銆
鍦"淇′换涓蹇"瀵硅瘽妗嗕腑锛岄夋嫨"瀹忚剧疆"锛圡acro Settings锛夈
镙规嵁鎭ㄧ殑闇姹傞夋嫨钖堥傜殑瀹忚剧疆阃夐”锛屼緥濡"钖鐢ㄦ墍链夊畯"锛圗nable all macros锛夋垨"钖鐢ㄤ粎鍙椾俊浠荤殑瀹"锛圗nable only trusted macros锛夈
镣瑰嚮"纭瀹"鍏抽棴瀵硅瘽妗嗐
鐜板湪锛屾偍搴旇ュ彲浠ユ垚锷熺紪杈戝拰杩愯屽畯浜嗐傝疯颁綇锛屽湪缂栬緫鍜岃繍琛屽畯镞讹纴瑕佸皬蹇冨苟纭淇濇偍镄勪唬镰佹ｇ‘镞犺锛屼互阆垮厤涓嶅繀瑕佺殑阌栾鍜屾暟鎹鎹熷け銆