Popunjavanje obrazaca podacima iz tablice

Formulacija problema

Imamo bazu (popis, tablicu – nazovite kako hoćete) s podacima o uplatama na listu Datum:

Zadatak: brzo ispišite blagajnički račun (uplata, račun…) za bilo koji željeni unos odabran s ovog popisa. Ići!

Korak 1. Napravite obrazac

Na drugom listu knjige (nazovimo ovaj list Oblik) stvoriti prazan obrazac. Možete to učiniti sami, možete koristiti gotove obrasce, preuzete, na primjer, s web stranica časopisa Chief Accountant ili web stranice Microsofta. Dobio sam nešto poput ovoga:

Popunjavanje obrazaca podacima iz tablice

U praznim ćelijama (Račun, Iznos, Primljeno od itd.) dobit će podatke iz tablice plaćanja s drugog lista – malo kasnije ćemo se time pozabaviti.

Korak 2: Priprema tablice plaćanja

Prije preuzimanja podataka iz tablice za naš obrazac, tablicu je potrebno malo modernizirati. Naime, lijevo od tablice umetnite prazan stupac. Koristit ćemo za unos oznake (neka to bude englesko slovo “x”) nasuprot retka iz kojeg želimo dodati podatke u obrazac:

Korak 3. Povezivanje tablice i obrasca

Za komunikaciju koristimo funkciju VPR(VLOOKUP) – više o tome možete pročitati ovdje. U našem slučaju, da biste u ćeliju F9 na obrascu umetnuli broj uplate označen sa “x” iz Data sheeta, morate u ćeliju F9 unijeti sljedeću formulu:

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

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

Oni. prevedeno u “razumljivo”, funkcija bi trebala pronaći u rasponu A2: G16 na podatkovnom listu redak koji počinje znakom “x” i dati nam sadržaj drugog stupca tog retka, tj. broj plaćanja.

Sve ostale ćelije na obrascu popunjavaju se na isti način – mijenja se samo broj stupca u formuli.

Za prikaz iznosa riječima koristio sam funkciju Vlastiti iz PLEX dodatka.

Rezultat bi trebao biti sljedeći:

Popunjavanje obrazaca podacima iz tablice

Korak 4. Da ne postoje dva “x” …

Ako korisnik unese "x" u više redaka, funkcija VLOOKUP će uzeti samo prvu vrijednost koju pronađe. Da biste izbjegli takve dvosmislenosti, desnom tipkom miša kliknite karticu lista Datum a zatim Izvorni tekst (Izvorni kod). U prozoru uređivača Visual Basic koji se pojavi kopirajte sljedeći kod:

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

Ova makronaredba sprječava korisnika da unese više od jednog "x" u prvi stupac.

Pa to je sve! Uživati!

  • Korištenje funkcije VLOOKUP za zamjenu vrijednosti
  • Poboljšana verzija funkcije VLOOKUP
  • Iznos u riječima (funkcija Propis) iz PLEX dodatka

 

Ostavi odgovor