Kā kopēt formulas, izmantojot automātisko aizpildīšanu programmā Excel 2016

Ja programmā Excel 2016 ir jākopē tikai viena formula, izmantojiet automātiskās aizpildes līdzekli vai komandas Kopēt un ielīmēt. Šāda veida formulas kopēšana, lai arī diezgan izplatīta, nevar tikt veikta ar vilkšanu un nomešanu.

Neaizmirstiet ātrās analīzes rīka opciju Kopējie. Varat to izmantot, lai zibenīgi izveidotu kopsummas rindu vai kolonnu datu tabulas apakšējā vai labajā malā. Vienkārši atlasiet tabulu kā šūnu diapazonu un noklikšķiniet uz pogas Ātrā analīze un tās paletē noklikšķiniet uz Kopsummas. Pēc tam noklikšķiniet uz pogas Summa paletes sākumā, lai izveidotu formulas, kas summē kolonnas jaunā rindā tabulas apakšā un/vai pogas Summa paletes beigās, lai izveidotu formulas, kas summē rindas jaunā rindā. kolonnu labajā galā.

Tālāk ir norādīts, kā varat izmantot automātisko aizpildi, lai kopētu vienu formulu šūnu diapazonā. Šajā attēlā ir redzama darblapa Mother Goose Enterprises — 2016 Sales ar visiem uzņēmumiem, taču šoreiz tikai viena mēneša kopsumma 12. rindā, kas tiek kopēta caur šūnu E12.

Formulas kopēšana šūnu diapazonā, izmantojot automātisko aizpildi.

Nākamajā attēlā parādīta darblapa pēc aizpildīšanas roktura vilkšanas šūnā B12, lai atlasītu šūnu diapazonu C12:E12 (kur šī formula ir jākopē).

Darblapa pēc formulas kopēšanas, kas apkopo mēneša (un ceturkšņa) pārdošanas apjomus.

Relatīvi runājot

Attēlā parādīta darblapa pēc tam, kad šūnā esošā formula ir kopēta šūnu diapazonā C12:E12 un šūna B12 ir aktīva. Ievērojiet, kā Excel apstrādā formulu kopēšanu. Sākotnējā formula šūnā B12 ir šāda:

=SUMMA(B3:B11)

Kad sākotnējā formula tiek kopēta šūnā C12, programma Excel nedaudz maina formulu, lai tā izskatītos šādi:

=SUM(C3:C11)

Programma Excel pielāgo kolonnas atsauci, mainot to no B uz C, jo jūs pāri rindām kopējāt no kreisās puses uz labo.

Kopējot formulu šūnu diapazonā, kas stiepjas pa rindām, programma Excel pielāgo rindu numurus kopētajās formulās, nevis kolonnu burtus, lai tie atbilstu katras kopijas pozīcijai. Piemēram, darblapas Mother Goose Enterprises — 2016 Sales šūnā E3 ir šāda formula:

=SUMMA(B3:D3)

Kopējot šo formulu šūnā E4, programma Excel maina formulas kopiju uz šādu:

=SUMMA(B4:D4)

Programma Excel pielāgo rindas atsauci, lai tā būtu aktuāla ar jauno 4. rindas pozīciju. Tā kā programma Excel pielāgo šūnu atsauces formulas kopijās attiecībā pret kopēšanas virzienu, šūnu atsauces sauc par relatīvajām šūnu atsaucēm .

Dažas lietas ir absolūtas!

Visas jaunās formulas, ko izveidojat, dabiski satur relatīvas šūnu atsauces, ja vien nenorādīsiet citādi. Tā kā lielākajai daļai formulu kopiju ir jāpielāgo to šūnu atsauces, jums reti ir jāpārdomā šis izkārtojums. Tad ik pa laikam jūs saskaraties ar izņēmumu, kas prasa ierobežot, kad un kā kopijās tiek koriģētas šūnu atsauces.

Viens no visizplatītākajiem izņēmumiem ir gadījumi, kad vēlaties salīdzināt dažādu vērtību diapazonu ar vienu vērtību. Visbiežāk tas notiek, ja vēlaties aprēķināt katras daļas procentuālo daļu no kopējās summas. Piemēram, darblapā Mother Goose Enterprises — 2016 Sales jūs saskaraties ar šādu situāciju, veidojot un kopējot formulu, kas aprēķina, cik procentu katra mēneša kopsumma (šūnu diapazonā B14:D14) ir no ceturkšņa kopsummas šūnā E12.

Pieņemsim, ka vēlaties ievadīt šīs formulas darblapas Mother Goose Enterprises – 2016 Sales 14. rindā, sākot ar šūnu B14. Formula šūnā B14, lai aprēķinātu procentuālo daļu no janvāra pārdošanas apjoma līdz pirmā ceturkšņa kopsummai, ir ļoti vienkārša:

=B12/E12

Šī formula dala janvāra kopējo pārdošanas apjomu šūnā B12 ar ceturkšņa kopējo summu E12 (kas varētu būt vieglāk?). Tomēr paskatieties, kas notiktu, ja aizpildīšanas rokturi vilktu vienu šūnu pa labi, lai kopētu šo formulu šūnā C14:

=C12/F12

Pirmās šūnas atsauces pielāgošana no B12 uz C12 ir tieši tā, ko ārsts lika. Tomēr otrās šūnas atsauces pielāgošana no E12 uz F12 ir katastrofa. Jūs ne tikai neaprēķināt, cik procentu februāra pārdošanas apjomi šūnā C12 veido no pirmā ceturkšņa E12 pārdošanas apjoma, bet arī iegūstat vienu no šiem šausmīgajiem #DIV/0! kļūdu lietas šūnā C14.

Lai neļautu programmai Excel pielāgot šūnas atsauci formulā jebkurā izveidotajā kopijā, konvertējiet šūnas atsauci no relatīvās uz absolūto. To var izdarīt, nospiežot funkciju taustiņu F4 pēc tam, kad programma Excel ir iestatīta rediģēšanas režīmā (F2). Programma Excel norāda, ka šūnas atsauci padarāt absolūtu, kolonnas burta un rindas numura priekšā novietojot dolāra zīmes. Piemēram, šajā attēlā šūna B14 satur pareizo formulu, ko kopēt šūnu diapazonā C14:D14:

Formulas kopēšana mēneša un ceturkšņa pārdošanas apjoma aprēķināšanai ar absolūtu šūnu atsauci.

=B12/E$12

Apskatiet darblapu pēc tam, kad šī formula ir kopēta diapazonā C14:D14 ar aizpildīšanas rokturi un ir atlasīta šūna C14 (skatiet nākamo attēlu). Ņemiet vērā, ka formulas josla parāda, ka šajā šūnā ir šāda formula:

Darblapa pēc formulas kopēšanas ar absolūtās šūnas atsauci.

=C12/$E$12

Tā kā sākotnējā formulā E12 tika mainīts uz 12 $ E$, visām kopijām ir tā pati absolūtā (nemainīgā) atsauce.

Ja jūs muļķojat un nokopējat formulu, kurā vienai vai vairākām šūnu atsaucēm vajadzēja būt absolūtām, bet atstājāt tās visas relatīvas, rediģējiet sākotnējo formulu šādi:

Veiciet dubultklikšķi uz šūnas ar formulu vai nospiediet F2, lai to rediģētu.

Novietojiet ievietošanas punktu kaut kur uz atsauces, kuru vēlaties pārvērst par absolūtu.

Nospiediet F4.

Kad esat pabeidzis rediģēšanu, formulas joslā noklikšķiniet uz pogas Enter un pēc tam kopējiet formulu izjauktā šūnu diapazonā ar aizpildīšanas turi.

Noteikti nospiediet F4 tikai vienu reizi, lai mainītu šūnas atsauci uz pilnīgi absolūtu, kā es aprakstīju iepriekš. Ja nospiežat funkciju taustiņu F4 otrreiz, tiek iegūta tā sauktā jauktā atsauce, kur tikai rindas daļa ir absolūta un kolonnas daļa ir relatīva (kā E$12). Ja pēc tam vēlreiz nospiežat taustiņu F4, programma Excel parādīs cita veida jauktas atsauces, kur kolonnas daļa ir absolūta un rindas daļa ir relatīva (kā $E12). Ja turpināsit un vēlreiz nospiežat F4, programma Excel maina šūnas atsauci atpakaļ uz pilnīgi relatīvu (kā E12).

Kad esat atgriezies tajā vietā, kur sākāt, varat turpināt izmantot F4, lai vēlreiz veiktu šo pašu šūnu atsauces izmaiņu kopu.

Ja izmantojat programmu Excel 2016 ierīcē, kurai nav piekļuves fiziskai tastatūrai ar funkciju taustiņiem (piemēram, skārienekrāna planšetdatorā), vienīgais veids, kā pārveidot šūnu adreses formulās no relatīvās uz absolūto vai kādu jauktu adresi, ir atvērt Touch tastatūru un izmantojiet to atbilstošajā formulas joslas šūnas adresē pirms kolonnas burta un/vai rindas numura pievienojiet dolāra zīmes.