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 - Dva uvjeta vrati sjecište reda i stupca, Kako vratiti rezultat iz sjecišta zaglavlja reda i zaglavlja stupca ako su zaglavlja redova i stupaca tekst ili ako se u zaglavljima stupaca i redova nalaze brojevne vrijednosti. Kako kreirati ComboBox u Excelu

Dva uvjeta vrati sjecište reda i stupca




sponzor



Kako vratiti rezultat iz sjecišta zaglavlja reda i zaglavlja stupca

Surfajući internetom i posjećujući foruma čija je tematika EXCEL, često sam vidio pitanja vezana za zaglavlje redova i zaglavlje stupaca, rezultat bi trebao biti sjecište ta dva uvjeta. Dakle to su dva uvjeta 'Zaglavlje reda' i 'Zaglavlje stupca'. U ovom Excel tutorijalu pokazat ću vam kolekciju Excel formula koje kao rezultat vraćaju sjecište zaglavlja stupca i zaglavlja reda. Na slikama ispod uočite da sam prikazao dvije opcije postavljanja uvjeta.

  1. Prva opcija: dva uvjeta nalaze se u ćelijama
  2. Druga opcija: dva uvjeta nalaze se u ComboBox izborniku

Također uočite da sam prikazao dvije varijante izvornih podataka.

  1. U zaglavlju redova i stupaca nalaze se vrijednosti (brojevi)
  2. U zaglavlju redova i stupaca nalazi se tekst

Da bi riješili zadatak ako imamo dva specifična uvjeta kao u ovom slučaju, možemo koristiti više kombinacija ugniježđenih formula. Ako gledamo bazne podatke Vrijednosti/Tekst tada možemo uočiti da mi možemo koristiti iste formule za vraćanje rezultata iz sjecišta stupaca i redova, ali postoji mala razlika.

Zaglavlje redova i stupaca su VRIJEDNOSTI (brojevi)

U ovom prvom primjeru, uočite da se u zaglavlju redova i stupaca nalaze vrijednosti (tj. brojevi). Za opciju 1 kao uvjete koristimo brojeve unesene u 'B16' i 'C16' ćelije. Rezultati za ova dva uvjeta prikazani su u 'D16' ćeliji pa na dolje.

Zaglavlje redova i stupaca je vrijednost

Na slici iznad uočite slijedeće: Sve formule koje su prikazane ispod vraćaju isti rezultat za dva uvjeta.

OPTION 1

Formula u 'D16' ćeliji je slijedeća:
=INDEX(B2:J12;MATCH(B16;A2:A12;0);MATCH(C16;B1:J1;0))

Formula u 'D17' ćeliji je slijedeća:
=SUMPRODUCT(B2:J12*(A2:A12=B16)*(B1:J1=C16))

Formula u 'D18' ćeliji je slijedeća:
=INDEX(B2:J12;RANK(B16;A2:A12;1);RANK(C16;B1:J1;1))

Formula u 'D19' ćeliji je slijedeća:
=HLOOKUP(C16;A1:J12;MATCH(B16;A2:A12)+1;FALSE)

Formula u 'D20' ćeliji je slijedeća:
=OFFSET($A$1;MATCH(B16;A2:A12;0);MATCH(C16;B1:J1;0))

Formula u 'D21' ćeliji je slijedeća:
=INDEX(A1:J12;MATCH(B16;INDEX(A1:J12;;1);0);MATCH(C16;INDEX(A1:J12;1;);0))

Kako vratiti rezultat sjecišta ako se dva uvjeta 'Vrijednosti' nalaze u ComboBox izborniku

OPTION 2

U ovoj opciji uvjeti su postavljeni u ComboBox izbornik. 'Helper' stupac služi kao pomoćni stupac za padajući izbornik za ComboBox (žute boje) Criteria2. Dakle da bi imali padajući izbornik u ComboBox, moramo izvorne podatke smjestiti u okomiti položaj. Ovaj 'helper' pomoćni stupac možete sakriti da bude nevidljiv. Pri kraju ovog tutorijala pogledajte detaljnije objašnjenje kako kreirati ComboBox i kako postaviti osnovne postavke. Uvjeti se nalaze u 'F16' i 'G16' ćelijama (ove ćelije ne vidite jer su prekrivene sa ComboBox okvirom). Uočite razliku u nekim formulama kada su izvorni podaci vrijednosti ili tekst.

Formula u 'H16' ćeliji je slijedeća:
=INDEX(B2:J12;MATCH(F16*1;A2:A12;0);MATCH(G16*1;B1:J1;0))

Formula u 'H17' ćeliji je slijedeća:
=SUMPRODUCT(B2:J12*(A2:A12=F16*1)*(B1:J1=G16*1))

Formula u 'H18' ćeliji je slijedeća:
=INDEX(B2:J12;RANK(F16;A2:A12;1);RANK(G16;B1:J1;1))

Formula u 'H19' ćeliji je slijedeća:
=HLOOKUP(G16*1;A1:J12;MATCH(F16*1;A2:A12)+1;FALSE)

Formula u 'H20' ćeliji je slijedeća:
=OFFSET($A$1;MATCH(F16*1;A2:A12;0);MATCH(G16*1;B1:J1;0))

Formula u 'H21' ćeliji je slijedeća:
=INDEX(A1:J12;MATCH(F16*1;INDEX(A1:J12;;1);0);MATCH(G16*1;INDEX(A1:J12;1;);0))

Zaglavlje redova i stupaca je TEKST

Ovaj drugi primjer sličan je prvom, razlika je što u zaglavlju redova i stupaca nalazi se tekst. U principu možemo koristiti iste formule kao u prethodnom slučaju ali uz male razlike u formuli.

Zaglavlje redova i stupaca je tekst

Uočite da nema razlike u formulama kada su izvorni podaci tekst. Naravno, morate voditi računa ako imate puno podataka, koje su od navedenih formula, Excel Volatile funkcije, jer vam mogu usporiti Excel prilikom kalkulacije.

OPTION 1

Formula u 'D16' ćeliji je slijedeća:
=INDEX(B2:J12;MATCH(B16;A2:A12;0);MATCH(C16;B1:J1;0))

Formula u 'D17' ćeliji je slijedeća:
=SUMPRODUCT(B2:J12*(A2:A12=B16)*(B1:J1=C16))

Formula u 'D18' ćeliji je slijedeća:
=VLOOKUP(B16;A2:J12;MATCH(C16;A1:J1;0);0)

Formula u 'D19' ćeliji je slijedeća:
=HLOOKUP(C16;A1:J12;MATCH(B16;A2:A12)+1;FALSE)

Formula u 'D20' ćeliji je slijedeća:
=OFFSET($A$1;MATCH(B16;A2:A12;0);MATCH(C16;B1:J1;0))

Formula u 'D21' ćeliji je slijedeća:
=INDEX(A1:J12;MATCH(B16;INDEX(A1:J12;;1);0);MATCH(C16;INDEX(A1:J12;1;);0))

Kako vratiti rezultat sjecišta ako se dva uvjeta 'Tekst' nalaze u ComboBox izborniku

OPTION 2

U ovoj opciji uvjeti su postavljeni u ComboBox izbornik. 'Helper' stupac služi kao pomoćni stupac za padajući izbornik za ComboBox (žute boje) Criteria2.

Formula u 'H16' ćeliji je slijedeća:
=INDEX(B2:J12;MATCH(F16;A2:A12;0);MATCH(G16;B1:J1;0))

Formula u 'H17' ćeliji je slijedeća:
=SUMPRODUCT(B2:J12*(A2:A12=F16)*(B1:J1=G16))

Formula u 'H18' ćeliji je slijedeća:
=VLOOKUP(F16;A2:J12;MATCH(G16;A1:J1;0);0)

Formula u 'H19' ćeliji je slijedeća:
=HLOOKUP(G16;A1:J12;MATCH(F16;A2:A12)+1;FALSE)

Formula u 'H20' ćeliji je slijedeća:
=OFFSET($A$1;MATCH(F16;A2:A12;0);MATCH(G16;B1:J1;0))

Formula u 'H21' ćeliji je slijedeća:
=INDEX(A1:J12;MATCH(F16;INDEX(A1:J12;;1);0);MATCH(G16;INDEX(A1:J12;1;);0))


Kako kreirati Combo Box padajući izbornik u Excelu

U slučajevima kada koristite ComboBox padajući izbornik, trebate znati da izvorni podaci za 'padajući izbornik' trebaju biti postavljeni okomito u stupcu. Zbog toga sam ja koristio 'helper' stupac u gore navedenim primjerima. Na slikama iznad, ne vide se 'F16' i 'G16' ćelije, jer ih prekriva ComboBox okvir. Na slici ispod uočite dotične ćelije i ComboBox okvir. ComboBox okviri povezani su sa ćelijama. Znači, ComboBox1 povezan je sa 'F16' ćelijom a ComboBox2 povezan je sa 'G16' ćelijom.

Da bi postavili ComboBox padajući izbornik na Excel Worksheet učinite slijedeće:

  1. Kliknite na 'Developer' tab
  2. Kliknite na 'Design Mode' button
  3. Kliknite na 'Insert' button
  4. Kliknite na drugi button po redoslijedu 'Combo Box' u području vrste kontrola 'ActiveX Controls'
  5. Kliknite na Worksheet ćeliju gdje želite kreirati Combo Box padajući izbornik (po metodi Drag-And-Drop kreirajte Combo Box okvir.
  6. Kliknite desnom tipkom miša na Combo Box okvir (obavezno vam mora biti uključena naredba 'Design Mode')

Pojavio vam se Properties dijalog box na kojem možete izmijeniti osnovne postavke (LinkedCell, ListFillRange itd). Uočite da se vrijednost ili tekst koji izaberete u Combo Box izborniku automatski kopira u 'LinkedCell' u ovom slučaju 'F16' ćelija, sjećate se ova ćelija je naš uvjet u formulama. Vi trebate pomjeriti ComboBox okvir da prekrije 'F16' ćeliju, da vizualno bude nevidljiva.

Uočite da 'LinkedCell' ćelije poput 'F16' i 'G16' imaju format kao tekst.
Kada su u pitanju uvjeti kao Vrijednost/Brojevi tada sam zbog ovog formata kao tekst, u gore navedenim formulama dodao množenje sa brojem 1 (...MATCH(F16*1;INDEX...).

Kreiranje ComboBox u Excelu

Naravno, uvijek umjesto Combo Box možete koristiti Data Validation padajući izbornik.

Kreiranje formule kada se rezultat nalazi na drugom radnom listu

Surfajući internetom, uočio sam da mnogi korisnici Excela imaju problema, kada treba kreirati formulu koja se nalazi na drugom radnom listu a uvjeti na trećem ili istom radnom listu. Ovo ne bi trebao biti problem.

Savjetujem:

  1. Kreirajte formulu na istom radnom listu na kojem se nalaze izvorni podaci.
  2. Metodom Cut/Paste premjestite rezultate na drugi radni list
  3. Excel će automatski dodati ime radnog lista ispred raspona ćelija ili određenih ćelija

Da bi vam bilo još lakše raditi, imenujte raspone ćelija ili samo određenu ćeliju. Gdje god kreirali formulu i ako koristite imenovane raspone ćelija, neće biti problema.
[npr: =VLOOKUP(A2;Sheet1!$R$14:$U$19;2;FALSE) je isto što i =VLOOKUP(A2;imenovaniPodaci;2;FALSE)]

Za primjer gore navedenih formula, iste formule na drugom radnom listu mogu biti slijedeće:
(Values! je ime radnog lista na kojem se nalaze izvorni podaci)

=INDEX(Values!B2:J12;MATCH(A3;Values!A2:A12;0);MATCH(B3;Values!B1:J1;0))
=SUMPRODUCT(Values!B2:J12*(Values!A2:A12=A3)*(Values!B1:J1=B3))
=INDEX(Values!B2:J12;RANK(A3;Values!A2:A12;1);RANK(B3;Values!B1:J1;1))
=HLOOKUP(B3;Values!A1:J12;MATCH(A3;Values!A2:A12)+1;FALSE)
=OFFSET(Values!$A$1;MATCH(A3;Values!A2:A12;0);MATCH(B3;Values!B1:J1;0))
=INDEX(Values!A1:J12;MATCH(A3;INDEX(Values!A1:J12;;1);0);MATCH(B3;INDEX(Values!A1:J12;1;);0))

Nadam se da će ova kolekcija Excel formula sa dva uvjeta, vezana za prikaz rezultata iz sjecišta zaglavlja redova i stupaca, olakšati rješavanje vaših problema i zadataka u korištenju Excela.

I na kraju u prilogu ovog tutorijala imate mogućnost Download datoteke na kojoj sam radio ovaj tutorijal.




     

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