Kudzaza mafomu ndi deta kuchokera patebulo

Kupanga vuto

Tili ndi nkhokwe (mndandanda, tebulo - itchuleni zomwe mukufuna) ndi zambiri zamalipiro papepala Deta:

Ntchito: sindikizani mwachangu risiti yandalama (malipiro, invoice ...) pazolemba zilizonse zomwe mukufuna zomwe zasankhidwa pamndandandawu. Pitani!

Gawo 1. Pangani Fomu

Patsamba lina la bukhu (tiyeni titchule pepala ili fomu) pangani mawonekedwe opanda kanthu. Mutha kuchita nokha, mutha kugwiritsa ntchito mafomu okonzeka, otengedwa, mwachitsanzo, kuchokera patsamba la Chief Accountant magazine kapena tsamba la Microsoft. Ndili ndi chinthu chonga ichi:

Kudzaza mafomu ndi deta kuchokera patebulo

M'maselo opanda kanthu (Akaunti, Ndalama, Zolandilidwa kuchokera etc.) adzalandira deta kuchokera pa tebulo la malipiro kuchokera ku pepala lina - pang'ono pang'ono tidzathana ndi izi.

Gawo 2: Kukonzekera tebulo lolipira

Musanatenge deta kuchokera patebulo la mawonekedwe athu, tebulo liyenera kusinthidwa pang'ono. Mwakutero, ikani mzati wopanda kanthu kumanzere kwa tebulo. Tidzagwiritsa ntchito kuyika chizindikiro (chikhale chilembo cha Chingerezi "x") moyang'anizana ndi mzere womwe tikufuna kuwonjezera deta ku fomu:

Gawo 3. Kulumikiza tebulo ndi mawonekedwe

Kulumikizana, timagwiritsa ntchito ntchitoyi VPR(VLOOKUP) - mukhoza kuwerenga zambiri za izo apa. Kwa ife, kuti muyike nambala yolipira yolembedwa "x" kuchokera pa Deta mu cell F9 pa fomu, muyenera kuyika fomula ili mu cell F9:

=VLOOKUP(“x”,Data!A2:G16)

=VLOOKUP(“x”;Data!B2:G16;2;0)

Iwo. atamasuliridwa kuti "zomveka", ntchitoyi iyenera kupeza mumtundu wa A2: G16 pa Tsamba la Data mzere woyambira ndi "x" ndi kutipatsa zomwe zili mugawo lachiwiri la mzerewu, mwachitsanzo, nambala yolipira.

Maselo ena onse pa fomu amadzazidwa mofanana - nambala yokha yazambiri imasintha mu ndondomeko.

Kuti ndiwonetse kuchuluka kwa mawu, ndidagwiritsa ntchito Womwe kuchokera pazowonjezera za PLEX.

Zotsatira zake ziyenera kukhala izi:

Kudzaza mafomu ndi deta kuchokera patebulo

Gawo 4. Kuti pasakhale awiri "x" ...

Ngati wosuta alowetsa "x" motsutsana ndi mizere ingapo, ntchito ya VLOOKUP imangotenga mtengo woyamba womwe wapeza. Kuti mupewe izi, dinani kumanja pa pepala tabu Deta Kenako Mawu oyambira (Source kodi). Pazenera la Visual Basic editor lomwe likuwonekera, lembani nambala iyi:

Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Long Dim str Monga String If Target.Count > 1 Ndiye Tulukani Sub Ngati Target.Column = 1 Ndiye str = Target.Value Application.EnableEvents = False r = Maselo(Rows.Count , 2).End(xlUp).Row Range("A2:A" & r).ClearContents Target.Value = str End If Application.EnableEvents = True End Sub

Macro iyi imalepheretsa wogwiritsa ntchito kulowa kuposa "x" m'gawo loyamba.

Chabwino, ndizo zonse! Sangalalani!

  • Kugwiritsa ntchito VLOOKUP kusintha ma values
  • Mtundu wowongoleredwa wa ntchito ya VLOOKUP
  • Kuchuluka kwa mawu (ntchito ya Propis) kuchokera ku chowonjezera cha PLEX

 

Siyani Mumakonda