Cika fom tare da bayanai daga tebur

Samar da matsala

Muna da bayanai (jeri, tebur - kira shi abin da kuke so) tare da bayani kan biyan kuɗi akan takarda data:

Task: da sauri buga takardar kuɗi (biyan kuɗi, daftari ...) don duk shigarwar da ake so da aka zaɓa daga wannan jeri. Tafi!

Mataki 1. Ƙirƙiri Form

A kan wani takardar littafin (bari mu kira wannan takardar Form) ƙirƙira wani fanko mara kyau. Kuna iya yin shi da kanku, kuna iya amfani da fom ɗin da aka shirya, waɗanda aka ɗauka, alal misali, daga gidajen yanar gizon Mujallar Babban Akanta ko gidan yanar gizon Microsoft. Na sami wani abu kamar haka:

Cika fom tare da bayanai daga tebur

A cikin sel mara komai (Account, Adadin, Karɓi daga da dai sauransu) za su sami bayanai daga teburin biyan kuɗi daga wani takarda - kadan daga baya za mu magance wannan.

Mataki na 2: Ana shirya teburin biyan kuɗi

Kafin ɗaukar bayanai daga tebur don fom ɗin mu, tebur ɗin yana buƙatar sabunta ɗan lokaci kaɗan. Wato, saka ginshiƙi mara komai a gefen hagu na tebur. Za mu yi amfani da shigar da alamar (bari ya zama harafin Ingilishi “x”) sabanin layin da muke son ƙara bayanai zuwa fom:

Mataki 3. Haɗa tebur da tsari

Don sadarwa, muna amfani da aikin VPR(VLOOKUP) - zaku iya karanta ƙarin game da shi anan. A cikin yanayinmu, don saka lambar biyan kuɗin da aka yiwa alama "x" daga takaddar bayanan zuwa cikin tantanin halitta F9 akan fom, dole ne ku shigar da dabara mai zuwa a cikin tantanin halitta F9:

=VLOOKUP("x",Data!A2:G16)

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

Wadancan. Fassara zuwa “masu fahimta”, aikin yakamata ya samo a cikin kewayon A2: G16 akan takardar bayanan layin da ke farawa da harafin “x” kuma ya ba mu abinda ke cikin shafi na biyu na wannan layin, watau lambar biyan kuɗi.

Duk sauran ƙwayoyin da ke kan fom an cika su ta hanya ɗaya - kawai lambar ginshiƙi tana canzawa a cikin dabara.

Don nuna adadin a cikin kalmomi, na yi amfani da aikin Own daga PLEX add-on.

Sakamakon yakamata ya kasance kamar haka:

Cika fom tare da bayanai daga tebur

Mataki na 4. Don haka babu “x” guda biyu…

Idan mai amfani ya shigar da "x" akan layi daya, aikin VLOOKUP zai ɗauki ƙimar farko da ya samo. Don guje wa irin wannan shubuha, danna-dama a shafin takardar data sai me Tushen rubutu (Lambar tushen). A cikin taga editan Visual Basic da ke bayyana, kwafi lambar mai zuwa:

Mai zaman kansa Sub Worksheet_Change(ByVal Target As Range) Dim r As Long Dim str As String If Target.Count > 1 Sai Exit Sub If Target.Column = 1 Sai str = Target.Value Application.EnableEvents = Karya r = Sel(Rows.Count) , 2).Karshe (xlUp) .Row Range ("A2: A" & r) .ClearContents Target.Value = str Ƙarshen Idan Aikace-aikacen.EnableEvents = Gaskiya Karshen Sub

Wannan macro yana hana mai amfani shigar da "x" fiye da ɗaya a cikin shafi na farko.

To, shi ke nan! Ji dadin!

  • Amfani da aikin VLOOKUP don musanya dabi'u
  • Ingantacciyar sigar aikin VLOOKUP
  • Adadi a cikin kalmomi (aikin Propis) daga ƙarawar PLEX

 

Leave a Reply