Excel

Metode absolutnega naslavljanja v programu Microsoft Excel

Kot veste, v tabeli Excel obstajata dve vrsti naslova: relativna in absolutna. V prvem primeru se povezava spremeni v smeri kopiranja za relativno količino premika, medtem ko je v drugem primeru fiksiran in ostane nespremenjen med kopiranjem. Toda privzeti so vsi naslovi v Excelu absolutni. Hkrati je pogosto treba uporabiti absolutno (fiksno) naslavljanje. Ugotovimo, kako je mogoče storiti.

Uporaba absolutnega naslavljanja

Absolutno naslavljanje je morda potrebno, na primer, ko kopiramo formulo, katere del je sestavljen iz spremenljivke, ki je prikazana v nizu številk, druga pa ima stalno vrednost. To pomeni, da ta številka igra vlogo nespremenjenega koeficienta, s katerim morate opraviti določeno operacijo (množenje, delitev itd.) Na celo vrsto spremenljivih številk.

V Excelu lahko določite fiksno naslavljanje na dva načina: z oblikovanjem absolutne reference in z uporabo funkcije INDIRECT. Oglejmo si podrobneje vsake od teh metod.

Metoda 1: absolutna referenca

Seveda je najbolj znan in pogosto uporabljen način ustvarjanja absolutnega naslavljanja uporaba absolutnih referenc. Absolutne povezave imajo razliko ne samo funkcionalno, ampak tudi sintaktično. Relativni naslov ima to sintakso:

=A1

Na fiksnem naslovu je znak za dolar nastavljen pred vrednostjo koordinat:

=$A$1

Absolutna referenca v programu Microsoft Excel

Znak dolarja lahko vnesete ročno. Če želite to narediti, morate kazalec postaviti pred prvo vrednostjo koordinat naslova (vodoravno) v celici ali v vrstici s formulo. Nadalje, v postavitvi Angleške tipkovnice kliknite na tipko "4" v zgornjem primeru (s pritiskom na tipko "Shift" ). Tam je, da se nahaja simbol dolarja. Potem morate narediti isti postopek z navpično koordinate.

Obstaja hitrejša pot. Kazalko morate postaviti v celico, kjer se nahaja naslov, in kliknite funkcijsko tipko F4. Po tem se znak za dolar takoj prikaže hkrati pred vodoravnimi in navpičnimi koordinatami navedenega naslova.

Zdaj pa poglejmo, kako se v praksi uporablja absolutno naslavljanje z uporabo absolutnih referenc.

Vzemi mizo, ki izračuna plače zaposlenih. Izračun se opravi tako, da se vrednost njihove osebne plače pomnoži s fiksnim razmerjem, kar je enako za vse zaposlene. Sam koeficient se nahaja v ločeni celici. Pred nami je čim hitreje izračunati plače vseh delavcev.

Tabela izračunih za izplačane plače zaposlenih v programu Microsoft Excel

  1. Torej, v prvi celici stolpca "Plače" vnesemo formulo za pomnožitev stopnje ustreznega zaposlenega s koeficientom. V našem primeru ima ta formula naslednjo obliko:

    =C4*G3

    2. Formula za izračun plač v Microsoft Excelu

  2. Če želite izračunati končni rezultat, kliknite tipko Enter na tipkovnici. Rezultat je prikazan v celici, ki vsebuje formulo.

    3. Rezultat izračuna plače za prvega zaposlenega v programu Microsoft Excel

  3. Izračunali smo plačo za prvega zaposlenega. Zdaj moramo to storiti za vse druge vrstice. Seveda lahko operacijo v vsako celico stolpca »Plače« vnesete ročno, tako da vnesete isto formulo s korekcijo offsetov, vendar imamo nalogo čim hitrejšega izračuna, ročni vnos pa traja veliko časa. In zakaj je potrebno napako pri ročnem vnašanju, če se formula lahko enostavno kopira v druge celice?

    Za kopiranje formule uporabljamo orodje, kot je oznaka za polnjenje. Postanite kurzor v spodnjem desnem kotu celice, kjer je vsebovan. V tem primeru bi moral biti sam kazalec preoblikovan v ta isti polnilni marker v obliki križa. Pritegnite levi gumb miške in povlecite kazalec navzdol do konca mize.

    4. Polnilo v programu Microsoft Excel

  4. Toda, kot smo videli, namesto pravilnega izračuna plač za druge zaposlene smo prejeli eno ničlo.

    5. Zeli za izračun plač v programu Microsoft Excel

  5. V kakšnem razlogu je takšen rezultat. Če želite to narediti, izberite drugo celico v stolpcu »Plače« . Izraz, ki ustreza celici, se prikaže v vrstici s formulo. Kot lahko vidite, prvi množitelj ( C5 ) ustreza stopnji zaposlenega, čigar plača pričakujemo. Premikanje koordinat v primerjavi s prejšnjo celico je posledica lastnosti relativnosti. Vendar v tem primeru to potrebujemo. Zahvaljujoč temu je bil prvi množitelj stopnja določenega zaposlenega, ki smo ga potrebovali. Toda koordinatni premik se je zgodil tudi z drugim dejavnikom. In zdaj se njegov naslov ne nanaša na koeficient ( 1,28 ), ampak na prazno celico spodaj.

    Kopirana formula v Microsoft Excelu

    Zaradi tega se je izkazalo, da je izračun plač za naslednje zaposlene s seznama napačen.

  6. Da bi popravili situacijo, moramo spremeniti naslavljanje drugega faktorja od relativnega do fiksnega. Če želite to narediti, pojdite nazaj v prvo celico stolpca »Plače« in jo izberite. Nato se premaknemo v vrstico s formulami, kjer je prikazan izraz, ki ga potrebujemo. Izberite drug faktor ( G3 ) s kazalko in pritisnite funkcijsko tipko na tipkovnici.

    7. Preoblikovanje druge množilne povezave iz relativnega v absolutno v Microsoft Excel

  7. Kot lahko vidite, se je okoli koordinat drugega množilnika pojavil znak dolarja, kar je, kot se spomnimo, atribut absolutnega naslavljanja. Za prikaz rezultatov na zaslonu pritisnite Enter .

    8. Drugi množitelj ima absolutno naslavljanje v programu Microsoft Excel

  8. Zdaj, kot prej, pokličemo oznako za polnjenje, tako da postavimo kazalko v spodnji desni kot prvega elementa stolpca »Plače« . Pritegnite levi gumb miške in ga povlecite navzdol.

    9. Klicanje polnilnika v programu Microsoft Excel

  9. Kot smo videli, je bil v tem primeru izračun izveden pravilno in je bil znesek plač za vse zaposlene v podjetju pravilno izračunan.

    10. Plača se pravilno izračuna v programu Microsoft Excel

  10. Preverimo, kako je bila kopija formule. Če želite to narediti, izberite drugi element stolpca »Plače« . Poglejte izraz v vrstici s formulo. Kot lahko vidite, so koordinate prvega faktorja ( C5 ), ki je še vedno relativno, premaknili eno navpično vertikalno točko v primerjavi s prejšnjo celico. Toda drugi dejavnik ( $ G $ 3 ), katerega naslavljanje smo popravili, je ostal nespremenjen.

Kopirana formula v Microsoft Excelu

V Excelu se uporablja tudi tako imenovano mešano naslavljanje. V tem primeru je stolpec ali vrstica določen na naslovu elementa. To se doseže tako, da je znak za dolar postavljen tik pred eno od koordinat naslova. Tukaj je primer tipične mešane povezave:

=A$1

Mešana povezava v programu Microsoft Excel

Ta naslov se šteje tudi za mešano:

=$A1

Mešana povezava v programu Microsoft Excel

To pomeni, da se absolutno naslavljanje v mešani povezavi uporablja samo za eno od koordinatnih vrednosti obeh.

Poglejmo si, kako lahko v praksi uporabimo takšno mešano povezavo z enako plačilno tabelo za zaposlene v podjetju.

  1. Kot lahko vidite, smo prej naredili, da imajo vse koordinate drugega množitelja absolutno naslavljanje. Ampak poglejmo, ali je v tem primeru treba oboje vrednosti določiti? Kot lahko vidite, pri kopiranju pride do vertikalne offset in horizontalno koordinate ostanejo nespremenjene. Zato je mogoče absolutno naslavljanje uporabiti le na koordinate niza in pustiti koordinate stolpca privzeto - relativno.

    Izberite prvi element stolpca »Plače« in izvedite zgornjo manipulacijo v vrstici s formulo. Dobimo naslednjo formulo:

    =C4*G$3

    Kot lahko vidite, se v drugem množitelju uporablja fiksno naslavljanje samo glede na koordinate niza. Če želite prikazati rezultat v celici, kliknite gumb Enter .

    2. Absolutno naslavljanje se nanaša samo na linijske koordinate v programu Microsoft Excel

  2. Nato z uporabo označevalnika polnjenja kopirajte to formulo v obseg celic, ki se nahajajo spodaj. Kot lahko vidite, je plačilni list za vse zaposlene pravilno izveden.

    3. Izračun plač zaposlenih je bil pravilno izveden z uporabo mešane reference v programu Microsoft Excel

  3. Oglejte si, kako je kopirana formula prikazana v drugi celici stolpca, nad katero smo izvedli manipulacijo. Kot lahko vidite v vrstici s formulo, potem ko ste izbrali ta element lista, kljub dejstvu, da je absolutno naslavljanje drugega množitelja imelo samo koordinate vrstic, ni prišlo do premika koordinat kolone. To je posledica dejstva, da smo kopirali ne horizontalno, ampak vertikalno. Če smo kopirali vodoravno, bi morali v podobnem primeru, nasprotno, narediti fiksno naslavljanje koordinat stolpca, in če bi bili niza ta postopek neobvezen.

Kopirana formula z mešano povezavo v programu Microsoft Excel

Lekcija: Absolutne in relativne reference v Excelu

2. način: funkcija alfa

Drugi način organiziranja absolutnega naslavljanja v preglednici Excel je uporaba operaterja INDEX . Navedena funkcija spada v skupino vgrajenih operaterjev "Reference in nizi" . Njegova naloga je, da oblikuje sklic na določeno celico z rezultatom rezultata na tisti element lista, v katerem se nahaja operator. V tem primeru je povezava s koordinatami še močnejša kot pri uporabi znaka za dolar. Zato se včasih običajno sklicuje na povezave, ki uporabljajo DVSSYL kot "superabsolute". Ta izjava ima naslednjo sintakso:

=ДВССЫЛ(ссылка_на_ячейку;[a1])

Funkcija ima dva argumenta, prva je obvezna, druga pa ni.

Argument »povezava na celico« je povezava z elementom stanja Excel v obliki besedila. To pomeni, da je navadna referenca, vendar je navedena v narekovajih. To omogoča tisto, ki omogoča lastnosti absolutnega naslavljanja.

Argument "a1" je neobvezen in se uporablja v redkih primerih. Njegova uporaba je potrebna le, če uporabnik izbere alternativno možnost naslova, namesto običajne uporabe koordinat "A1" (stolpci imajo abecedno oznako in vrstice imajo digitalno oznako). Alternativa je uporaba sloga R1C1 , v katerem so stolpci, kot so vrstice, označeni s številkami. Preklopite na ta način delovanja prek okna možnosti Excel. Nato z uporabo operaterja INDEX uporabite vrednost "FALSE" kot argument "a1 " . Če delate v običajnem načinu prikaza povezav, tako kot večina drugih uporabnikov, lahko podate vrednost "TRUE" kot argument "a1 " . Vendar pa je ta vrednost privzeto privzeto, zato je v tem primeru veliko preprostejše, če v tem primeru ni podan argument »a1« .

Poglejmo, kako bo absolutno naslavljanje, organizirano s pomočjo funkcije DVSSYL , delovalo na primeru naše plačilne tabele.

  1. Izberemo prvi element stolpca »Plače« . Postavite znak "=" . Kot se spomnite, mora prvi faktor v zgornji formuli za izračun plače predstavljati relativni naslov. Zato preprosto kliknite na celico, ki vsebuje ustrezno vrednost plače ( C4 ). Po tem, ko je bil njegov naslov prikazan v elementu za rezultat rezultata, kliknite gumb "pomnoži" ( * ) na tipkovnici. Potem se moramo premakniti k uporabi operaterja INDIVIDUAL . Kliknite ikono »Vstavi funkcijo« .

    2. Odprite čarovnika za funkcije v programu Microsoft Excel

  2. V odprtem oknu Čarovnikov funkcij pojdite v kategorijo "Reference in nizi" . Med predstavljenim seznamom imen razlikujemo ime »DVSSYL« . Nato kliknite gumb "OK" .

    3. Preklop v okno argumentov funkcije INDIRECT v programu Microsoft Excel

  3. Izvede se aktiviranje okna argumentov operaterja DVSSYL . Sestavljen je iz dveh polj, ki ustrezajo argumentom te funkcije.

    Postavite kazalko v polje »Referenca celic« . Preprosto kliknite na tisti element lista, v katerem se nahaja koeficient za izračun plače ( G3 ). Naslov bo takoj prikazan na področju okna argumentov. Če bi se ukvarjali z običajno funkcijo, bi se lahko uvedba naslova štela za popolno, vendar uporabljamo funkcijo " ALGORITHM" . Kot se spominjamo, morajo imeti naslovi obliko besedila. Zato koordinate, ki se nahajajo na področju okna, zavijemo z narekovaji.

    Ker delamo v standardnem načinu prikaza koordinat, polje "A1" ostane prazno. Kliknemo na gumb "OK" .

    4. Okno argumenta funkcije INDIRECT v programu Microsoft Excel

  4. Aplikacija izračuna in prikaže rezultat v elementu stanja, ki vsebuje formulo.

    5. Rezultat izračunavanja formule s funkcijo Alpha v programu Microsoft Excel

  5. Zdaj kopiramo to formulo v vse ostale celice v stolpcu »Plače« z oznako polnila, kot smo to storili prej. Kot vidite, so bili vsi rezultati pravilno izračunani.

    6. Rezultat celotnega stolpca se izračuna s pomočjo formule s funkcijo Alpha v programu Microsoft Excel

  6. Poglejmo, kako je formula prikazana v eni od celic, na katere je bila kopirana. Izberite drugi element stolpca in poglejte vrstico formule. Kot lahko vidite, je prvi množitelj, ki je relativna referenca, spremenil svoje koordinate. Istočasno pa argument drugega faktorja, ki ga predstavlja funkcija ALGORITHM , ostane nespremenjen. V tem primeru smo uporabili tehniko fiksnega naslavljanja.

Prikaz kopirane formule s funkcijo INDIRECT v programu Microsoft Excel

Lekcija: Operater INDUSTRIJE v Excelu

Absolutno naslavljanje v tabelah Excel je mogoče zagotoviti na dva načina: z uporabo funkcije INDEX in z uporabo absolutnih referenc. V tem primeru funkcija zagotavlja bolj togo vezavo na naslov. Delno absolutno naslavljanje se lahko uporablja tudi pri uporabi mešanih povezav.