Kuzadza mafomu nedata kubva patafura

Kugadziriswa kwedambudziko

Tine dhatabhesi (rondedzero, tafura - idaidze zvaunoda) ine ruzivo rwekubhadhara pane pepa Data:

basa: kurumidza kudhinda risiti yemari (kubhadhara, invoice ...) kune chero chaunoda chinyorwa chakasarudzwa kubva pane iyi runyorwa. Enda!

Danho 1. Gadzira Fomu

Pane rimwe pepa rebhuku (ngatidaidzei pepa iri fomu) gadzira fomu risina chinhu. Iwe unogona kuzviita iwe pachako, unogona kushandisa mafomu akagadzirira, akatorwa, semuenzaniso, kubva kune mawebhusaiti emagazini Chief Accountant kana webhusaiti yeMicrosoft. Ndine chinhu chakadai:

Kuzadza mafomu nedata kubva patafura

Mumasero asina chinhu (Akaunti, Mari, Yakagamuchirwa kubva etc.) ichawana data kubva patafura yekubhadhara kubva kune rimwe pepa - zvishoma gare gare tichabata neizvi.

Danho rechipiri: Kugadzirira tafura yekubhadhara

Usati watora data kubva patafura yefomu yedu, tafura inoda kuvandudzwa zvishoma. Zvichireva, isa koramu isina chinhu kuruboshwe kwetafura. Isu tichashandisa kuisa label (rega ive tsamba yechirungu "x") yakatarisana nemutsara watinoda kuwedzera data kufomu:

Danho 3. Kubatanidza tafura uye fomu

Nekutaurirana, tinoshandisa basa VPR(VLOOKUP) - unogona kuverenga zvakawanda nezvazvo pano. Kwatiri, kuti uise nhamba yekubhadhara yakanyorwa "x" kubva paData sheet musero F9 pane fomu, iwe unofanirwa kuisa inotevera fomula muchitokisi F9:

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

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

Avo. rakashandurirwa mu "zvinonzwisisika", basa rinofanira kuwana muhuwandu hweA2: G16 paData sheet mutsara unotanga nechimiro "x" uye utipe zviri mukati mechikamu chechipiri chemutsara uyu, kureva nhamba yekubhadhara.

Mamwe masero ese ari pafomu anozadzwa nenzira imwechete - chete nhamba yekoramu inoshandura mufomu.

Kuti ndiratidze huwandu mumashoko, ndakashandisa basa racho Izvo kubva kune PLEX yekuwedzera.

Mhedzisiro inofanira kuva inotevera:

Kuzadza mafomu nedata kubva patafura

Nhanho 4. Kuti pasave nembiri "x" ...

Kana mushandisi akapinda "x" achipesana nemitsara yakawanda, basa reVLOOKUP rinongotora kukosha kwekutanga kwarinowana. Kuti udzivise kusanzwisisika kwakadaro, tinya-kurudyi pane pepa tab Data ndokuzo Kwakabva mavara (Source Code). MuVisual Basic mupepeti hwindo rinoonekwa, tevedzera inotevera kodhi:

Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Long Dim str Se String Kana Target.Count > 1 Wobuda Sub Kana Target.Column = 1 Then str = Target.Value Application.EnableEvents = False r = Masero(Rows.Count , 2).End(xlUp).Row Range("A2:A" & r).ClearContents Target.Value = str End Kana Application.EnableEvents = True End Sub

Iyi macro inodzivirira mushandisi kupinda anopfuura "x" muchikamu chekutanga.

Zvakanaka, ndizvo chete! Enjoy!

  • Kushandisa VLOOKUP basa kutsiva kukosha
  • Shanduro yakavandudzwa yeVLOOKUP basa
  • Kuwanda mumashoko (Propis basa) kubva kune PLEX yekuwedzera

 

Leave a Reply