Kopēt formulas bez saites maiņas

Problēma

Pieņemsim, ka mums ir šāda vienkārša tabula, kurā tiek aprēķinātas summas par katru mēnesi divās pilsētās, un tad summa tiek konvertēta uz eiro pēc kursa no dzeltenās šūnas J2.

Problēma ir tāda, ka, ja kopējat diapazonu D2:D8 ar formulām kaut kur citur lapā, Microsoft Excel automātiski labos saites šajās formulās, pārvietojot tās uz jaunu vietu un pārtraucot skaitīšanu:

Uzdevums: kopēt diapazonu ar formulām, lai formulas nemainītos un paliktu nemainīgas, saglabājot aprēķinu rezultātus.

1. metode. Absolūtās saites

2. metode: īslaicīgi atspējojiet formulas

Lai formulas nemainās kopēšanas laikā, jums (īslaicīgi) jāpārliecinās, ka programma Excel pārstāj tās uzskatīt par formulām. To var izdarīt, aizstājot vienādības zīmi (=) ar jebkuru citu rakstzīmi, kas parasti nav atrodama formulās, piemēram, jaucējzīmi (#) vai ampersandu pāri (&&) kopēšanas laikam. Priekš šī:

1. Atlasiet diapazonu ar formulām (mūsu piemērā D2:D8)
2. Noklikšķiniet Ctrl + H uz tastatūras vai cilnes Sākums – Atrast un atlasīt – Aizstāt (Sākums — Atrast un atlasīt — Aizstāt)

   

3. Parādītajā dialoglodziņā ievadiet to, ko mēs meklējam un ar ko aizstājam, un ievadiet to parametri (Opcijas) neaizmirstiet precizēt Meklēšanas joma – Formulas. Mēs nospiežam Aizstāt visu (Aizstāt visu).
4. Kopējiet iegūto diapazonu ar deaktivizētām formulām pareizajā vietā:

   

5. aizstāt # on = atpakaļ, izmantojot to pašu logu, atgriežot funkcionalitāti formulām.

3. metode: kopējiet, izmantojot Notepad

Šī metode ir daudz ātrāka un vienkāršāka.

Nospiediet īsinājumtaustiņu Ctrl+Ё vai pogu Rādīt formulas tab formula (Formulas — rādīt formulas), lai ieslēgtu formulu pārbaudes režīmu – rezultātu vietā šūnās tiks parādītas formulas, pēc kurām tie tiek aprēķināti:

Kopējiet mūsu diapazonu D2:D8 un ielīmējiet to standartā piezīmju grāmatiņa:

Tagad atlasiet visu ielīmēto (Ctrl + A), vēlreiz kopējiet to starpliktuvē (Ctrl + C) un ielīmējiet to lapā vajadzīgajā vietā:

Atliek tikai nospiest pogu Rādīt formulas (Rādīt formulas)lai atgrieztos Excel normālā režīmā.

Piezīme: šī metode dažreiz neizdodas sarežģītās tabulās ar sapludinātām šūnām, taču vairumā gadījumu tā darbojas labi.

4. metode. Makro

Ja jums bieži ir jādara šāda formulu kopēšana, nemainot atsauces, tad ir lietderīgi izmantot makro. Nospiediet īsinājumtaustiņu Alt+F11 vai pogu Visual Basic tab attīstītājs (Izstrādātājs), izvēlnē ievietojiet jaunu moduli Ievietot - modulis  un kopējiet tur šī makro tekstu:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("Atlasīt šūnas ar formulām, ko kopēt.", _ "Precīzi kopēt formulas", Default:=Atlase.Adrese, Tips := 8 kopēt." , "Precīzi kopēt formulas", _ Default:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Tad MsgBox "Kopēt un ielīmēt diapazonus atšķiras pēc izmēra!", vbIzsaukuma zīme, "Kopēšanas kļūda" Iziet apakšgalā If If pasteRange nav nekā, tad iziet apakšā Else pasteRange.Formula = copyRange.Formula End If End Sub

Varat izmantot pogu, lai palaistu makro. Makro tab attīstītājs (Izstrādātājs — makro) vai īsinājumtaustiņš Alt+F8. Pēc makro palaišanas tas lūgs atlasīt diapazonu ar sākotnējām formulām un ievietošanas diapazonu un automātiski kopēs formulas:

• Ērta formulas un rezultātu apskate vienlaikus
• Kāpēc Excel formulās ir nepieciešams atsauces stils R1C1
• Kā ātri atrast visas šūnas ar formulām
• Rīks precīzu formulu kopēšanai no PLEX papildinājuma