30 Excel funkcija za 30 dana: INDEKS

Jučer na maratonu 30 Excel funkcija za 30 dana tražili smo tekstualne nizove koristeći funkciju PRONAĐITE (FIND), i otkrio da je osjetljiv na velika i mala slova, za razliku od funkcije TRAŽI (TRAŽI).

24. dana maratona proučavat ćemo funkciju INDEX (INDEX). Koristeći broj reda i stupca, može vratiti vrijednost ili referencu na vrijednost. Tokom maratona, više puta smo koristili ovu funkciju INDEX (INDEX) u kombinaciji s drugim funkcijama:

  • sa TOČNO (TAČNO) – za traženje imena po tačnom podudaranju na listi;
  • sa OBLASTI (OBLASTI) – za traženje zadnje oblasti u imenovanom opsegu;
  • sa KOLONE (NUMBERCOLUMN) – za izračunavanje sume zadnje kolone u imenovanom opsegu;
  • sa MATCH (MACH) – za traženje imena učesnika koji je pogodio najbližu vrijednost.

Dakle, okrenimo se teoretskim informacijama i praktičnim primjerima o funkciji INDEX (INDEX). Ako imate dodatne informacije ili primjere, podijelite ih u komentarima.

Funkcija 24: INDEX

funkcija INDEX (INDEX) vraća vrijednost ili referencu na vrijednost. Koristite ga u kombinaciji sa drugim funkcijama kao što su MATCH (MACH) za stvaranje moćnih formula.

Kako se može koristiti funkcija INDEX?

funkcija INDEX (INDEX) može vratiti vrijednost ili referencu na vrijednost. Možete ga koristiti za:

  • Pronađite iznos prodaje za odabrani mjesec.
  • Dobijte referencu na odabrani red, kolonu ili područje.
  • Kreirajte dinamički raspon na osnovu brojanja.
  • Sortirajte stupac tekstualnih podataka po abecednom redu.

Sintaksa INDEX (INDEX)

funkcija INDEX (INDEX) ima dva sintaktička oblika, niz i referencu. Obrazac polja vraća vrijednost, dok referentni obrazac vraća referencu.

Forma niza ima sljedeću sintaksu:

INDEX(array,row_num,column_num)

ИНДЕКС(массив;номер_строки;номер_столбца)

  • arzrak (niz) – niz konstanti ili raspon ćelija.
  • Ako je argument array (niz) ima samo 1 red ili 1 kolonu, tada je odgovarajući argument broja reda/kolone opcioni.
  • Ako je argument array (niz) sadrži više od 1. reda i 1. stupca:
    • i data vrijednost samo argumenta red_br (broj_line), tada će biti vraćen niz svih vrijednosti ove linije.
    • ako je data samo vrijednost argumenta kolona_br (broj_kolone), zatim niz svih vrijednosti u toj koloni.
  • Ako je argument red_br (broj_line) nije naveden, morate navesti kolona_br (broj_kolone).
  • Ako je argument kolona_br (broj_kolone) nije naveden, morate navesti red_br (broj_line).
  • Ako su navedena oba argumenta, tada funkcija vraća vrijednost ćelije na presjeku navedenog reda i stupca.
  • Ako kao argument red_br (broj_line) ili kolona_br (broj_stupca) navedite nulu, tada će funkcija vratiti niz vrijednosti cijele kolone ili cijelog reda.

Referentni obrazac ima sljedeću sintaksu:

INDEX(reference,row_num,column_num,area_num)

ИНДЕКС(ссылка;номер_строки;номер_столбца;номер_области)

  • rreferenca (link) može se odnositi na jedan ili više raspona ćelija, uključujući i nesusjedne opsege, koji moraju biti zatvoreni u zagradama.
  • Ako je svako područje u upućivanje (referenca) ima samo 1 red ili 1 kolonu, tada je odgovarajući argument broja reda/kolone opcioni.
  • oblast_br (region_number) specificira broj regije u argumentu upućivanje (referenca) iz koje se vraća rezultat.
  • Ako je argument oblast_br (broj_područja) nije navedeno, 1 područje će biti odabrano.
  • Ako je argument red_br (broj_line) ili kolona_br (broj_kolone) je nula, tada će funkcija vratiti referencu na cijeli stupac ili cijeli red, respektivno.
  • Rezultat je referenca koju mogu koristiti druge funkcije.

Zamke INDEX (INDEX)

If red_br (broj_line) i kolona_br (broj_stupca) ukazuje na ćeliju koja ne pripada datom nizu ili referenci, funkciji INDEX (INDEX) će prijaviti grešku #REF! (#VEZA!).

Primjer 1: Pronađite iznos prodaje za odabrani mjesec

Unesite broj reda i funkciju INDEX (INDEX) će vratiti iznos prodaje iz ovog reda. Evo broja mjeseca 4, tako da će rezultat biti zbir prodaje za april (apr).

=INDEX($C$2:$C$8,F2)

=ИНДЕКС($C$2:$C$8;F2)

30 Excel funkcija za 30 dana: INDEKS

Da biste ovu formulu učinili fleksibilnijom, možete koristiti funkciju MATCH (MACH) za određivanje broja reda po mjesecima, koji se može odabrati sa padajuće liste.

=INDEX($C$2:$C$8,MATCH($F$2,$D$2:$D$8,0))

=ИНДЕКС($C$2:$C$8;ПОИСКПОЗ($F$2;$D$2:$D$8;0))

30 Excel funkcija za 30 dana: INDEKS

Primjer 2: Dobivanje reference na određeni red, stupac ili područje

U ovom primjeru, imenovani raspon MonthAmts sastoji se od 3 nesusedna opsega. Domet MonthAmts ima 3 oblasti – po jednu za svaki mjesec – i svaka oblast ima 4 reda i 2 kolone. Evo formule za ime MonthAmts:

='Ex02'!$B$3:$C$6,'Ex02'!$E$3:$F$6,'Ex02'!$H$3:$I$6

='Ex02'!$B$3:$C$6;'Ex02'!$E$3:$F$6;'Ex02'!$H$3:$I$6

Korištenje funkcija INDEX (INDEX) Možete vratiti trošak (Cost) ili iznos prihoda (Rev) za određeni region i mjesec.

=INDEX(MonthAmts,B10,C10,D10)

=ИНДЕКС(MonthAmts;B10;C10;D10)

30 Excel funkcija za 30 dana: INDEKS

Sa rezultatom funkcije INDEX (INDEX) možete izvršiti operaciju množenja, kao kod obračuna poreza (Tax) u ćeliji F10:

=0.05*INDEX(MonthAmts,B10,C10,D10)

=0,05*ИНДЕКС(MonthAmts;B10;C10;D10)

ili može vratiti referencu funkcije CELL (CELL) za prikaz adrese rezultata u ćeliji G10.

=CELL("address",INDEX(MonthAmts,B10,C10,D10))

=ЯЧЕЙКА("адрес";ИНДЕКС(MonthAmts;B10;C10;D10))

30 Excel funkcija za 30 dana: INDEKS

Primjer 3: Kreiranje dinamičkog raspona na osnovu brojanja

Možete koristiti funkciju INDEX (INDEX) za kreiranje dinamičkog raspona. Ovaj primjer kreirao je imenovani raspon MonthList sa ovom formulom:

='Ex03'!$C$1:INDEX('Ex03'!$C:$C,COUNTA('Ex03'!$C:$C))

='Ex03'!$C$1:ИНДЕКС('Ex03'!$C:$C;СЧЁТЗ('Ex03'!$C:$C))

30 Excel funkcija za 30 dana: INDEKS

Ako dodate još jedan mjesec na listu u koloni C, automatski će se pojaviti na padajućoj listi u ćeliji F2, koja koristi MonthListkao izvor podataka.

30 Excel funkcija za 30 dana: INDEKS

Primjer 4: Sortirajte stupac tekstualnih podataka po abecednom redu

U posljednjem primjeru, funkcija INDEX (INDEX) radi u kombinaciji s nekoliko drugih funkcija za vraćanje liste mjeseci poredanih po abecednom redu. Funkcija COUNTIF (COUNTIF) broji koliko mjeseci dolazi prije svakog određenog mjeseca na listi (kreira se niz). SMALL (SMALL) vraća n-tu najmanju vrijednost u kreiranom nizu, i MATCH (MATCH) vraća broj reda željenog mjeseca na osnovu ove vrijednosti.

Ova formula se mora uneti u ćeliju E4 kao formula niza pritiskom Ctrl + Shift + Enter. A zatim kopirano iz ostalih ćelija u rasponu E4:E9.

=INDEX($C$4:$C$9,MATCH(SMALL(COUNTIF($C$4:$C$9,"<"&$C$4:$C$9),
ROW(E4)-ROW(E$3)),COUNTIF($C$4:$C$9,"<"&$C$4:$C$9),0))

=ИНДЕКС($C$4:$C$9;ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ($C$4:$C$9;"<"&$C$4:$C$9);
СТРОКА(E4)-СТРОКА(E$3));СЧЁТЕСЛИ($C$4:$C$9;"<"&$C$4:$C$9);0))

30 Excel funkcija za 30 dana: INDEKS

Ostavite odgovor