Ukugcwalisa amafomu ngedatha evela etafuleni

Ukwakhiwa kwenkinga

Sinesizindalwazi (uhlu, ithebula - yibize ngendlela oyifunayo) enolwazi ngezinkokhelo eshidini Idatha:

Umsebenzi: phrinta ngokushesha irisidi yemali (inkokhelo, i-invoyisi ...) nganoma yikuphi okufakiwe okufunayo okukhethwe kulolu hlu. Hamba!

Isinyathelo 1. Dala Ifomu

Kwelinye ishidi lencwadi (asibize leli shidi Ifomu) dala ifomu elingenalutho. Ungakwenza ngokwakho, ungasebenzisa amafomu esenziwe ngomumo, athathwe, isibonelo, kumawebhusayithi kamagazini we-Chief Accountant noma iwebhusayithi ye-Microsoft. Ngithole into efana nale:

Ukugcwalisa amafomu ngedatha evela etafuleni

Kumaseli angenalutho (I-akhawunti, Inani, Etholwe kusukela njll.) sizothola idatha etafuleni lokukhokha kwelinye ishidi - kamuva sizobhekana nalokhu.

Isinyathelo sesi-2: Ukulungisa ithebula lokukhokha

Ngaphambi kokuthatha idatha kuthebula yefomu lethu, ithebula lidinga ukuguqulwa kancane. Okungukuthi, faka ikholomu engenalutho kwesokunxele setafula. Sizosebenzisa ukufaka ilebula (makube uhlamvu lwesiNgisi “x”) ngokuphambene nomugqa lapho sifuna ukwengeza idatha efomini:

Isinyathelo sesi-3. Ukuxhumanisa ithebula nefomu

Ukuxhumana, sisebenzisa umsebenzi VPR(VLOOKUP) – ungafunda kabanzi ngakho lapha. Esimweni sethu, ukuze ufake inombolo yenkokhelo ephawulwe ngokuthi “x” eshidini Ledatha kuseli F9 efomini, kufanele ufake ifomula elandelayo kuseli F9:

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

=I-VLOOKUP(“x”;Idatha!B2:G16;2;0)

Labo. elihunyushwe ngokuthi “kuyaqondakala”, umsebenzi kufanele uthole kububanzi A2: G16 eshidini Ledatha umugqa oqala ngohlamvu “x” futhi usinike okuqukethwe kukholamu yesibili yalo mugqa, okungukuthi inombolo yokukhokha.

Wonke amanye amaseli efomini agcwaliswa ngendlela efanayo - inombolo yekholomu kuphela eshintsha ifomula.

Ukuze ngibonise inani emagameni, ngisebenzise umsebenzi Ngomnikazi kusukela kusengezo se-PLEX.

Umphumela kufanele ube lokhu okulandelayo:

Ukugcwalisa amafomu ngedatha evela etafuleni

Isinyathelo 4. Ukuze kungabikho "x" amabili ...

Uma umsebenzisi efaka u-“x” ngokumelene nemigqa eminingi, umsebenzi we-VLOOKUP uzothatha kuphela inani lokuqala alitholayo. Ukuze ugweme ukungaqondakali okunjalo, chofoza kwesokudla kuthebhu yeshidi Idatha bese Umbhalo ongumthombo (Ikhodi Yomthombo). Kuwindi lomhleli we-Visual Basic elivelayo, kopisha ikhodi elandelayo:

I-Private Sub Worksheet_Change(ByVal Target Njengobubanzi) Dim r As Long Dim str Njengochungechunge Uma Ithagethi.Bala > 1 Bese Uphuma Ngaphansi Uma Ithagethi.Ikholomu = 1 Bese str = Target.Value Application.EnableEvents = False r = Amaseli(Rows.Count , 2).End(xlUp).Ibanga Lomugqa("A2:A" & r).ClearContents Target.Value = str End Uma Isicelo.EnableEvents = Isiphetho Seqiniso Isihloko

Le macro ivimbela umsebenzisi ukuthi angafaki ngaphezu ko-“x” oyedwa kukholamu yokuqala.

Awu, yilokho kuphela! Jabulela!

  • Ukusebenzisa umsebenzi we-VLOOKUP ukushintsha amanani
  • Inguqulo ethuthukisiwe yomsebenzi we-VLOOKUP
  • Inani emagameni (umsebenzi we-Propis) elivela kusengezo se-PLEX

 

shiya impendulo