Maselo ochulukira (cumulative)

Zamkatimu

Nthawi zambiri, zinthu zimachitika tikafunika kufotokozera (kusonkhanitsa) mfundo zingapo motsatizana zomwe zimalowetsedwa mu cell imodzi:

Iwo. Ngati, mwachitsanzo, mulowetsa nambala 1 mu cell A5, ndiye kuti nambala 1 iyenera kuwonekera mu B15. Ngati mutalowetsa nambala 1 mu A7, ndiye kuti 1 iyenera kuwonekera mu selo B22, ndi zina zotero. Mwambiri, zomwe akauntanti (osati okhawo) amatcha kuchuluka kokwanira.

Mutha kugwiritsa ntchito chosungira cell-accumulator pogwiritsa ntchito macro osavuta. Dinani kumanja pa tsamba latsamba pomwe ma cell A1 ndi B1 ali ndikusankha kuchokera pazosankha Mawu oyambira (Source kodi). Pazenera la Visual Basic editor lomwe limatsegula, koperani ndi kumata ma macro code yosavuta:

Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "A1" Ndiye Ngati IsNumeric(.Value) Ndiye Application.EnableEvents = False Range("A2").Value = Range(" A2").Value + .Value Application.EnableEvents = Mapeto Oona Ngati Mapeto Ngati Mapeto Ndi Mapeto Sub

Maadiresi a maselo A1 ndi A2, ndithudi, akhoza kusinthidwa ndi anu.

Ngati mukufuna kutsata zomwe zalowetsedwa ndikunena mwachidule osati ma cell amodzi, koma magulu onse, ndiye kuti macro ayenera kusinthidwa pang'ono:

Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target, Range("A1:A10")) Palibe Kanthu Ndiye Ngati IsNumeric(Target.Value) Ndiye Application.EnableEvents = False Target.Offset(0, 1) .Value = Target.Offset(0, 1).Value + Target.Value Application.EnableEvents = Mapeto Oona Ngati Mapeto Ngati Mapeto Sub

Zimaganiziridwa kuti deta imalowetsedwa m'maselo amtundu wa A1: A10, ndipo ziwerengero zomwe zalowetsedwa zimafotokozedwa mwachidule pamzere woyandikana nawo kumanja. Ngati m'malo mwanu sichili pafupi, ndiye yonjezerani kusuntha kumanja kwa oyendetsa Offset - m'malo mwa 1 ndi chiwerengero chachikulu.

  • Kodi macros ndi chiyani, momwe mungayikitsire macro code mu VBA, momwe mungagwiritsire ntchito?

Siyani Mumakonda