Iyo yekirasi yekudonha-pasi rondedzero muExcel sheet yakanaka, asi inongobvumidza iwe kusarudza imwe sarudzo kubva pane yakaratidzwa seti. Dzimwe nguva izvi ndizvo chaizvo zvaunoda, asi pane mamiriro ezvinhu apo mushandisi anoda kukwanisa kusarudza vamwe zvinhu kubva pakurongwa.
Ngatitarisei kune akati wandei akajairwa mashandisirwo eiyo yakawanda-yakasarudzwa runyorwa.
Sarudzo 1. Horizontal
Mushandisi anosarudza zvinhu kubva pane yekudonha-pasi runyorwa chimwe nechimwe, uye ivo vanoonekwa kurudyi kwesero iri kuchinjwa, otomatiki zvichinyorwa zvakachinjika:
Kudonhedza-pasi mazita mumasero C2: C5 mumuenzaniso uyu anogadzirwa nenzira yakajairika, kureva
- sarudza maseru C2:C5
- tab kana menyu Data sarudza timu Kugadziriswa kweData
- pahwindo rinovhurwa, sarudza sarudzo List uye tsanangura senge renji mabviro maseru ane source data yerondedzero A1:A8
Ipapo iwe unofanirwa kuwedzera macro kune iyo sheet module, iyo ichaita ese makuru basa, kureva kuwedzera akasarudzwa kukosha kurudyi kwegirinhi maseru. Kuti uite izvi, tinya-kurudyi pane pepa tebhu ine kudonhedza-pasi zvinyorwa uye sarudza rairo Purogiramu yekupa. Namira iyo inotevera kodhi muVisual Basic mupepeti hwindo rinovhura:
Private Sub Worksheet_Change(ByVal Target As Range) PaKukanganisa Resume Next If Not Intersect(Target, Range("C2:C5")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False Kana Len(Target.Offset (0, 1)) = 0 Zvadaro Target.Offset(0, 1) = Target Else Target.End(xlToRight).Offset(0, 1) = Target End Kana Target.ClearContents Application.EnableEvents = True End Kana End Sub Kana zvichidikanwa, tsiva iyo inonzwisa tsitsi yeinodonhedza-pasi zvinyorwa C2:C5 mumutsetse wechipiri wekodhi iyi neyako.
Sarudzo 2. Yakatwasuka
Zvakafanana neyakapfuura vhezheni, asi iyo mitsva yakasarudzwa tsika haina kuwedzerwa kurudyi, asi kuzasi:
Izvo zvinoitwa nenzira imwecheteyo, asi iyo macro code inochinja zvishoma:
Private Sub Worksheet_Change(ByVal Target As Range) PaKukanganisa Resume Next If Not Intersect(Target, Range("C2:F2")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False Kana Len(Target.Offset (1, 0)) = 0 Zvadaro Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End Kana Target.ClearContents Application.EnableEvents = True End Kana End Sub Zvekare, kana zvichidikanwa, tsiva iyo inonzwisa tsitsi yeiyo C2: F2 yekudonha-pasi zvinyorwa neyako mumutsara wechipiri wekodhi iyi.
Sarudzo 3. Nekuunganidza musero rimwechete
Muchisarudzo ichi, kuunganidza kunoitika musero rimwe chete uko kunowanikwa rondedzero yekudonhedza pasi. Zvinhu zvakasarudzwa zvinopatsanurwa nechero munhu akapihwa (semuenzaniso, koma):
Kudonhedza-pasi mazita mumasero akasvibira anogadzirwa nenzira yakakwana, senge nzira dzakapfuura. Basa rese rinoitwa, zvakare, ne macro mune sheet module:
Private Sub Worksheet_Change(ByVal Target As Range) PaKukanganisa Resume Next If Not Intersect(Target, Range("C2:C5")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False newVal = Target Application.Undo oldval = Target Kana Len(kare) <> 0 Uye oldval <> newVal Then Target = Target & "," & newVal Else Target = newVal End Kana Len(newVal) = 0 Zvadaro Target.ClearContents Application.EnableEvents = True End Kana End Sub Kana uchida, unogona kutsiva iyo yekuparadzanisa hunhu (comma) mumutsara wechi9 wekodhi neyako (semuenzaniso, nzvimbo kana semicolon).
- Maitiro ekugadzira yakapfava yekudonhedza pasi rondedzero mune Excel sheet sheet
- Dropdown list ine content
- Dropdown list ine zvisipo zvakawedzerwa
- Chii macros, maitiro ekuashandisa, kupi kwekuisa macro kodhi muVisual Basic