Si të kopjoni formulat me plotësimin automatik në Excel 2016

Nëse thjesht duhet të kopjoni një formulë të vetme në Excel 2016, përdorni veçorinë AutoFill ose komandat Copy and Paste. Ky lloj kopjimi i formulës, megjithëse mjaft i zakonshëm, nuk mund të bëhet me tërheqje dhe lëshim.

Mos harroni opsionin Totals në mjetin e Analizës së Shpejtë. Mund ta përdorni për të krijuar një rresht ose një kolonë totalesh në fund ose në skajin e djathtë të një tabele të dhënash menjëherë. Thjesht zgjidhni tabelën si një gamë qelizash dhe klikoni butonin Analiza e Shpejtë e ndjekur nga Totalet në paletën e saj. Më pas klikoni butonin Shuma në fillim të paletës për të krijuar formula që i përmbledhin kolonat në një rresht të ri në fund të tabelës dhe/ose butonin Shuma në fund të paletës për të krijuar formula që përmbledhin rreshtat në një të re kolona në skajin e djathtë.

Ja se si mund të përdorni AutoPlotësimin për të kopjuar një formulë në një sërë qelizash. Në këtë figurë mund të shihni fletën e punës Mother Goose Enterprises – 2016 Sales me të gjitha kompanitë, por këtë herë me vetëm një total mujor në rreshtin 12, i cili është në proces kopjimi përmes qelizës E12.

Kopjimi i një formule në një gamë qelizash me plotësimin automatik.

Figura e mëposhtme tregon fletën e punës pas zvarritjes së dorezës së mbushjes në qelizën B12 për të zgjedhur gamën e qelizave C12:E12 (ku kjo formulë duhet të kopjohet).

Fleta e punës pas kopjimit të formulës që arrin në total shitjet mujore (dhe tremujore).

Duke folur relativisht

Figura tregon fletën e punës pasi formula në një qelizë është kopjuar në diapazonin e qelizave C12:E12 dhe qeliza B12 është aktive. Vini re se si Excel e trajton kopjimin e formulave. Formula origjinale në qelizën B12 është si më poshtë:

=SUM(B3:B11)

Kur formula origjinale kopjohet në qelizën C12, Excel e ndryshon pak formulën në mënyrë që të duket kështu:

=SUM(C3:C11)

Excel rregullon referencën e kolonës, duke e ndryshuar atë nga B në C, sepse keni kopjuar nga e majta në të djathtë nëpër rreshta.

Kur kopjoni një formulë në një gamë qelizash që shtrihet poshtë rreshtave, Excel rregullon numrat e rreshtave në formulat e kopjuara në vend të shkronjave të kolonës për t'iu përshtatur pozicionit të secilës kopje. Për shembull, qeliza E3 në fletën e punës Mother Goose Enterprises – 2016 Sales përmban formulën e mëposhtme:

=SUM(B3:D3)

Kur e kopjoni këtë formulë në qelizën E4, Excel e ndryshon kopjen e formulës në sa vijon:

=SUM(B4:D4)

Excel rregullon referencën e rreshtit për të mbajtur aktual pozicionin e rreshtit të ri 4. Për shkak se Excel rregullon referencat e qelizave në kopjet e një formule në lidhje me drejtimin e kopjimit, referencat e qelizave njihen si referenca relative të qelizave .

Disa gjëra janë absolute!

Të gjitha formulat e reja që krijoni përmbajnë natyrshëm referenca relative të qelizave, përveç nëse thoni ndryshe. Për shkak se shumica e kopjeve që bëni të formulave kërkojnë rregullime të referencave të qelizave të tyre, rrallëherë duhet t'i jepni një mendim të dytë kësaj rregullimi. Pastaj, herë pas here, hasni në një përjashtim që kërkon kufizimin e kohës dhe mënyrës se si rregullohen referencat e qelizave në kopje.

Një nga këto përjashtime më të zakonshme është kur dëshironi të krahasoni një sërë vlerash të ndryshme me një vlerë të vetme. Kjo ndodh më shpesh kur dëshironi të llogaritni se sa përqind është secila pjesë ndaj totalit. Për shembull, në fletën e punës Mother Goose Enterprises – 2016 Sales, ju e hasni këtë situatë në krijimin dhe kopjimin e një formule që llogarit se çfarë përqindje është çdo total mujor (në rangun B14:D14) të totalit tremujor në qelizën E12.

Supozoni se dëshironi të futni këto formula në rreshtin 14 të fletës së punës Mother Goose Enterprises – 2016 Sales, duke filluar në qelizën B14. Formula në qelizën B14 për llogaritjen e përqindjes së totalit të shitjeve nga janari në tremujorin e parë është shumë e drejtpërdrejtë:

=B12/E12

Kjo formulë ndan totalin e shitjeve të janarit në qelizën B12 me totalin tremujor në E12 (çfarë mund të jetë më e lehtë?). Shikoni, megjithatë, se çfarë do të ndodhte nëse tërhiqni dorezën e mbushjes një qelizë në të djathtë për të kopjuar këtë formulë në qelizën C14:

=C12/F12

Rregullimi i referencës së parë të qelizave nga B12 në C12 është pikërisht ajo që urdhëroi mjeku. Sidoqoftë, rregullimi i referencës së dytë të qelizës nga E12 në F12 është një fatkeqësi. Jo vetëm që nuk llogaritni se sa përqind janë shitjet e shkurtit në qelizën C12 të shitjeve të tremujorit të parë në E12, por gjithashtu përfundoni me një nga ato të tmerrshmet #DIV/0! gabime në qelizën C14.

Për të ndaluar Excel që të rregullojë një referencë qelize në një formulë në çdo kopje që bëni, konvertoni referencën e qelizës nga relative në absolute. Këtë e bëni duke shtypur tastin e funksionit F4, pasi të keni vendosur Excel në modalitetin Edit (F2). Excel tregon që ju e bëni referencën e qelizës absolute duke vendosur shenjat e dollarit përpara shkronjës së kolonës dhe numrit të rreshtit. Për shembull, në këtë figurë, qeliza B14 përmban formulën e saktë për të kopjuar në diapazonin e qelizave C14:D14:

Kopjimi i formulës për llogaritjen e raportit të shitjeve mujore me ato tremujore me një referencë absolute të qelizave.

=B12/$12E$

Shikoni fletën e punës pasi kjo formulë të kopjohet në diapazonin C14:D14 me dorezën e mbushjes dhe zgjidhet qeliza C14 (shih figurën e mëposhtme). Vini re se shiriti i Formulës tregon se kjo qelizë përmban formulën e mëposhtme:

Fleta e punës pas kopjimit të formulës me referencën absolute të qelizës.

=C12/$12E$

Për shkak se E12 u ndryshua në 12 $ E$ në formulën origjinale, të gjitha kopjet kanë të njëjtën referencë absolute (të pandryshueshme).

Nëse godisni dhe kopjoni një formulë ku një ose më shumë nga referencat e qelizave duhet të ishin absolute, por i keni lënë të gjitha relative, modifikoni formulën origjinale si më poshtë:

Klikoni dy herë në qelizën me formulën ose shtypni F2 për ta modifikuar.

Vendoseni pikën e futjes diku në referencën që dëshironi të konvertoni në absolute.

Shtypni F4.

Kur të përfundoni redaktimin, klikoni butonin Enter në shiritin e Formulës dhe më pas kopjoni formulën në diapazonin e qelizave të ngatërruara me dorezën e mbushjes.

Sigurohuni që të shtypni F4 vetëm një herë për të ndryshuar një referencë qelize në plotësisht absolute siç e përshkruaja më herët. Nëse shtypni tastin e funksionit F4 për herë të dytë, përfundoni me një të ashtuquajtur referencë të përzier, ku vetëm pjesa e rreshtit është absolute dhe pjesa e kolonës është relative (si në E$12). Nëse më pas shtypni përsëri F4, Excel vjen me një lloj tjetër referimi të përzier, ku pjesa e kolonës është absolute dhe pjesa e rreshtit është relative (si në $E12). Nëse vazhdoni dhe shtypni përsëri F4, Excel e ndryshon referencën e qelizës në plotësisht relative (si në E12).

Pasi të jeni kthyer atje ku keni filluar, mund të vazhdoni të përdorni F4 për të kaluar përsëri në të njëjtin grup ndryshimesh të referencës së qelizave.

Nëse jeni duke përdorur Excel 2016 në një pajisje pa qasje në një tastierë fizike me çelësa funksioni (siç është një tabletë me ekran me prekje), e vetmja mënyrë për të kthyer adresat e qelizave në formulat tuaja nga relative në absolute ose një formë adrese të përzier është hapja Prekni tastierën dhe përdorni atë, shtoni shenjat e dollarit përpara shkronjës së kolonës dhe/ose numrit të rreshtit në adresën e duhur të qelizës në shiritin e Formulës.