logotip web stranice ic.ims.hr
Home - počtna stranica  FORUM ACCESS 2003 EXCEL 2003 WORD 2003  
1 Pokretanje i zatvaranje Microsoft Excel-a 2013
2 Izgled prozora Microsoft Excela 2013
3 Alatne trake u MS Excelu 2013
4 Podešavanje Excela
5 Otvaranje nove radne knjige i radnog lista
6 Spremanje i zatvaranje Workbook u Excelu
7 Pregled radnog lista prije ispisa u Excelu 2013
8 Određivanje margina Radnog lista
9 Kreiranje Zaglavlja i Podnožja Radnog lista
10 Ispis radnog lista ili tablice ili područja u Excelu 2013
11 Kreiranje mapa u Excelu
12 Kretanje radnom tablicom u Excelu 2013
13 Označavanje ili selektiranje u Excelu
14 Operatori u Excelu
15 Kreiranje formule u Excelu
16 Uvjetno oblikovanje u Excelu 2013
17 Kako izraditi padajuću listu u Excelu - Data Validation
18 Imenovanje ćelija ili raspona podataka u excelu 2013
19 Poravnanje teksta
20 Promjena podataka i brisanje sadržaja ćelije u Excelu 2013
21 Spajanje i dijeljenje ćelija u Excelu 2013
22 Automatska ispuna - brzi unos podataka
23 Popunjavanje ćelija specijalnim znakovima i simbolima
24 Crtanje u Excelu 2013
25 Dodavanje komentara na ćelije u Excelu 2013
26 Naredbe poništi i ponovi u excelu 2013
27 Premještanje, izrezivanje i kopiranje sadržaja ćelije u Excelu 2013
28 Umetanje i brisanje Ćelija, Stupaca i Redova
29 Promjena veličine Ćelija, Stupaca i Redova u Excelu 2013
30 Umetanje slika u Excelu
31 Radnje vezane uz Radni List u Excelu 2013
32 Apsolutna i relativna adresa ćelija u Excelu
33 Izrada Grafikona u Excelu 2013
34 Oblikovanje radnih tablica u Excelu 2013
35 Sortiranje podataka u Excelu 2013
36 Filtriranje podataka u Excelu 2013
37 Kreiranje Pivot Table
38 Zamrzavanje dijela prozora radnog lista u Excelu 2013
39 Oblikovanje Datuma u Excelu 2013
40 Zaključavanje ćelija i radnog lista u Excelu 2013
41 Zaštita radne knjige u Excelu 2013
42 Greške u Excelu 2013
43 Popis svih funkcija u Excelu 2013
44 Tipkovni prečaci u Excelu (hot key, shortcut)
45 Ograničenja u Excelu 2013
46 Makronaredbe u Excelu
47 Razni primjeri u Excelu
   
1 Excel 2003 primjeri
1 Excel 2007 primjeri
1 VBA U EXCELU
   
 

MICROSOFT EXCEL 2013 tutoriali - Vraćanje naslova reda i stupca na osnovu sjecišta Row-Column u Excelu, Kako na osnovu vrijednosti ili podatka vratiti podatak iz naslova reda i naslova stupca, Kako znati kome smo isplatili određeni iznos i u kojem mjesecu, Pretraživanje podataka u Excelu i vraćanje zaglavlja u stupcu i retku za određenu najbližu manju vrijednost

Kako na osnovu tražene vrijednosti ili podatka vratiti zaglavlje naslova reda i naslova stupca




sponzor



Vraćanje naslova reda i stupca na osnovu sjecišta Row/Column u Excelu

U situaciji kada imamo bazu podataka sa imenima djelatnika i mjesecima u godini kao u situaciji na slici ispod i želimo znati kome smo isplatili određeni iznos i u kojem mjesecu tada možemo kao rezultat vratiti ime djelatnika i mjesec isplate ako postavimo bilo kakav uvjet jer će Excel pronaći najbližu vrijednost u rasponu podataka i prikazati je kao rezultat.

Dakle, kako pretražiti tablicu podataka u Excelu i za traženu vrijednost vratiti najbližu manju vrijednost kao i zaglavlja u stupcu i retku.

Na slici ispod uočite na radnom listu "master" raspon ćelija i određene podatke tj. vrijednosti i zaglavlja redova i stupaca. Ovo su izvorni podaci koje pretražujemo na radnom listu "results" i za određeni uvjet želimo znati koja je najbliža manja vrijednost traženoj vrijednosti a kao drugi rezultat želimo znati ime osobe kojoj je isplaćena vrijednost i u kojem mjesecu.

NAPOMENA:Ovdje želim napomenuti da vrijednosti moraju biti unikatne ili jedinstvene. Ako vrijednosti nisu jedinstvene formula će vratiti prvu najbližu manju pronađenu vrijednost u prvom stupcu u kojem pronađe identičnu vrijednost.

Označi sjecište stupca i retka u rasponu ćelija u Excelu

Dobro pogledajte sliku ispod i uočite sličnosti sa slikom iznad. Na obe slike nalaze se podaci u rasponu ćelija. Međutim na drugoj slici ispod formirana je Table1 na osnovu raspona podataka na slici iznad. O ovim razlikama između naziva "tablica vs table" pisao sam na tutorijalu "tablica vs Table u Excelu. U nastavku ovog tutorijala vidjeti ćete zašto ove raspone ćelija prikazujem dva puta.

Označi bojom presjek reda i stupca u Table1 Excel izračuna

Postavljanje Conditional Formatting ako se uvjet nalazi na drugom radnom listu

Na slikama iznad uočite da ćelija koja je sjecište retka i stupca ima zelenu pozadinu. Ovdje na ovom radnom listu postavio sam Conditional Formatting/Uvjetno oblikovanje da bi obojao ili ofarbao traženu ćeliju. Formula za označavanje ćelije u presjeku stupca i reda nije komplicirana, ali problem nastaje kada se uvjet nalazi na drugom radnom listu, a naša uvjetna ćelija se nalazi na radnom listu "results" a to je ćelija 'B2'.
Da bi označavanje presjeka reda i stupca funkcioniralo ja sam definirao ime za ćeliju "B2' na radnom listu "results" tj. imenovao sam je nazivom "najblizaVrijednost" (pogledajte sliku ispod).

Dakle formula za Conditional formatting na radnom listu "master" je slijedeća =najblizaVrijednost

Uvjetno oblikovanje u Excelu i postavljanje uvjeta za označavanje ćelije bojom

Na radnom listu "results" želimo prikazati slijedeće rezultate:

  1. Prvu najbližu manju vrijednost od uvjeta
  2. Zaglavlje retka
  3. Zaglavlje stupca

Dakle mi želimo znati kojoj osobi je isplaćen najbliži iznos na osnovu traženoga uvjeta i u kojem mjesecu. Dakle tražimo sjecište stupca i retka na radnom listu "master".

Ja sam namjerno kreirao drugi radni list, da bi dodatno prikazao kako koristiti Conditional Formatting na različitim radnim listovima, a isto tako i zbog razlika u kreiranim formulama, što ćete vidjeti u nastavku pregleda tutorijala. Pažljivo čitajući ovaj tutorial u nastavku ćete sve shvatiti.

sponzor


Na slici ispod uočite radni list "results" na kojem možete vidjeti slijedeće:

  • U ćeliji 'A2' unosimo uvjet prema kojem tražimo određeni novčani iznos.
  • U ćeliji 'B2' formula kao rezultat prikazuje najbližu manju vrijednost postavljenom uvjetu u 'A2' ćeliji.
  • U ćeliji 'A6' formula kao rezultat prikazuje zaglavlje retka tj. ime djelatnika kojem smo isplatili novčanu vrijednost.
  • U ćeliji 'B6' formula kao rezultat prikazuje zaglavlje stupca tj. mjesec u kojem smo izvršili isplatu novčane vrijednosti

Također na slici ispod uočite da sam definirao naziv za ćeliju 'B2' tj. postavio ime 'najblizaVrijednost', ovo je važno zbog označavanja bojom sjecišta retka i kolone na radnom listu "master". Opće poznato je da Uvjetno oblikovanje ne funkcionira u Excelu ako se podaci ne nalaze na istom radnom listu pa je zbog toga potrebno definirati naziv ćelije ili raspona ćelija.

Formula za pretraživanje raspona ćelija ili table1 i vraćanje sjecišta

Formule za prikazivanje sjecišta retka i stupca i vraćanje zaglavlja reda i kolone

I pri kraju ovog tutoriala o sjecištu i zaglavlju reda i kolone kreirane formule su slijedeće:

ARRAY formula u ćeliji 'B2' je slijedeća: (Prva formula odnosi se na raspon ćelija a druga na Table1)

=MAX(IF(master!$C$2:$H$11<=A2;master!$C$2:$H$11))
=MAX(IF(Table1[[I]:[VI]]<=A2;Table1[[I]:[VI]]))

ARRAY formula u ćeliji 'A6' je slijedeća: (Prva formula odnosi se na raspon ćelija a druga na Table1)

=INDEX(master!$B$2:$B$11;MIN(IF(master!$C$2:$H$11=B2;ROW(master!$C$2:$H$11)-1)))
=INDEX(Table1[Ime];MIN(IF(Table1[[I]:[VI]]=B2;ROW(Table1[[I]:[VI]])-1)))

Uočite u formulama iznad crvenim slovima broj -1, on označava koliko stupaca se nalazi ispred prvog stupca u formuli ili [Ime] u drugoj formuli.

ARRAY formula u ćeliji 'B6' je slijedeća: (Prva formula odnosi se na raspon ćelija a druga na Table1)

=INDEX(master!$C$1:$H$1;MIN(IF(master!$C$2:$H$11=B2;COLUMN(master!$C$1:$H$1)-2)))
=INDEX(Table1[[#Headers];[I]:[VI]];MIN(IF(Table1[[I]:[VI]]=B2;COLUMN(Table1[[#Headers];[I]:[VI]])-2)))

Uočite u formulama iznad crvenim slovima broj -2, on označava koliko stupaca se nalazi ispred prvog stupca u formuli ili [Ime]. Umjesto ovih brojeva možemo koristiti Exel funkciju COLUMN(A1) i COLUMN(B1)

Dakle, u ovom slučaju kada koristimo Excel funkciju COLUMN, naše ARRAY formule izgledaju kako slijedi:

=INDEX(master!$B$2:$B$11;MIN(IF(master!$C$2:$H$11=B2;ROW(master!$C$2:$H$11)-COLUMN(A1))))
=INDEX(Table1[Ime];MIN(IF(Table1[[I]:[VI]]=B2;ROW(Table1[[I]:[VI]])
-COLUMN(A1))))
=INDEX(master!$C$1:$H$1;MIN(IF(master!$C$2:$H$11=B2;COLUMN(master!$C$1:$H$1)-COLUMN(B1))))
=INDEX(Table1[[#Headers];[I]:[VI]];MIN(IF(Table1[[I]:[VI]]=B2;COLUMN(Table1[[#Headers];[I]:[VI]])
-COLUMN(B1))))

Također pogledajte tutorijale:

- Jedna formula INDEX-MATCH za kopiranje u desnu stranu koja pronalazi sjecište za više stupaca i redova
- Automatsko označavanje reda i stupca bojom nakon selektiranja ćelije
- Kako prikazati zaglavlje retka i stupca ako nam je poznat podatak iz sjecišta
- Dva uvjeta, zaglavlje retka i stupca vrati podatak ili vrijednost iz sjecišta
- Kako pronaći podatak u tablici uz 2 uvjeta
- Kako pronaći jedan uvjet u Excel tablici i vratiti sve pripadajuće vrijednosti iz ostalih redova za zadani uvje

 




     

sponzor



COPYRIGHT © - 2005 - 2020 IvanC - Sva prava pridržana - Ova web domena/web stranica koristi cookies.
Prema međunarodnom zakonu o autorskom djelu zabranjeno je kopiranje, reproduciranje, prosljeđivanje i publiciranje cijelog sadržaja ili bilo kojeg njegova dijela