Merwyn iz Engleske poslao je ovaj problem.
Postoji li jedna ćelija u ćeliji B2 koja se može kopirati preko i dolje kako bi se stvorila tablica množenja?
Cilj Merwyna je unijeti jednu formulu u B2 koja se lako može kopirati u svih 144 ćelija kako bi se stvorila referentna tablica množenja.
Napadnimo ga kao početnika u Excelu i vidimo na koje zamke nailazimo. Početna reakcija mogla bi biti formula u B2 =A2*B1. Ova formula daje točan rezultat, ali nije prikladna za Merwynov zadatak.
Kada kopirate ovu formulu iz ćelije B2 u ćeliju C2, stanice na koje se poziva formula ujedno se pomiču preko jedne ćelije. Formula koja je =A2*B1sada bila postaje =C1*B2. Ovo je značajka dizajnirana za Microsoft Excel i naziva se relativnom referencom formule. Dok kopirate formulu, reference ćelija u formuli također premještaju odgovarajući broj stanica preko i prema dolje.
Postoje slučajevi kada ne želite da Excel pokazuje ovo ponašanje, a trenutni slučaj je jedan od takvih. Da biste spriječili da Excel promijeni referencu tijekom kopiranja ćelija, umetnite znak "$" prije dijela reference koji želite zamrznuti. Primjeri:
- $ A1 govori Excelu da se uvijek želite pozivati na stupac A.
- B $ 1 govori Excelu da se uvijek želite pozivati na red 1.
- $ B $ 1 govori Excelu da se uvijek želite pozivati na ćeliju B1.
Učenje ovog koda dramatično će smanjiti vrijeme potrebno za izradu radnih listova. Umjesto da mora unijeti 144 formule, Merwyn može koristiti ovu skraćenicu za unos jedne jedine formule i kopiranje u sve stanice.
Gledajući Merwynovu formulu, =B1*A2shvaćamo da bi dio izraza "B1" uvijek trebao ukazivati na broj u retku 1. To bi trebalo prepisati kao "B $ 1". Odjeljak "A2" formule uvijek treba ukazivati na broj u stupcu A. To bi trebalo prepisati kao "$ A2". Dakle, nova formula u ćeliji B2 jest =B$1*$A2.
Nakon unosa formule u B2, mogu je kopirati i zalijepiti u odgovarajući raspon. Excel slijedi moje upute i nakon kopiranja pronalazim sljedeće formule:
- Stanica M2 sadrži
=M$1*$A2 - Stanica B13 sadrži
=B$1*$A13 - Stanica M13 sadrži
=M$1*$A13
Svaka od ovih vrsta formula ima svoje ime. Formule bez znakova dolara nazivaju se relativnim formulama. Formule u kojima su i redak i stupac zaključani znakom dolara nazivaju se apsolutnim formulama. Formule u kojima je redak ili stupac zaključan znakom dolara nazivaju se mješovitim formulama.
Postoji stenografska metoda za unos znakova dolara. Dok tipkate formulu i dovršavate referencu ćelije, možete pritisnuti tipku za prebacivanje između 4 vrste referenci. Recimo da ste počeli tipkati formulu i upisali ste =100*G87.
- Pritisnite F4 i vaša se formula mijenja u
=100*$G$87 - Ponovno pritisnite F4 i vaša se formula mijenja u
=100*G$87 - Ponovno pritisnite F4 i vaša se formula mijenja u
=100*$G87 - Ponovno pritisnite F4 i vaša se formula vraća na izvornik
=100*G87
Možete pauzirati tijekom unosa formule na svakoj referenci ćelije da biste pogodili F4 dok ne dobijete pravu vrstu reference. Pritisci na tipku za unos gornje Merwynove formule su =B1*A1.
Jedna od mojih najdražih upotreba mješovitih referenci formula pojavljuje se kada imam dugački popis unosa u stupcu A. Kada želim brzu i prljavu metodu za pronalaženje duplikata, ponekad unesem ovu formulu u ćeliju B4, a zatim je kopiram:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Napomena: 2. izraz VLOOKUP formule koristi apsolutnu ($ A $ 2) i mješovitu ($ A3) referencu za opis raspona pretraživanja. Na engleskom govorim Excelu da uvijek pretražuje od ćelije $ A $ 2 do ćelije u stupcu A odmah iznad trenutne ćelije. Čim Excel naiđe na duplikat vrijednosti, rezultat ove formule mijenja se iz # N / A! na vrijednost.
Kreativnom upotrebom referenci ćelija $ in, možete osigurati da se jedna jedina formula može kopirati u mnoge ćelije s točnim rezultatima.
