змест
Праблема
Выкажам здагадку, што ў нас ёсць такая простая табліца, у якой сумы разлічаны за кожны месяц у двух гарадах, а затым агульная сума пераўтворана ў еўра па курсе з жоўтай ячэйкі J2.
Праблема ў тым, што калі вы скапіруеце дыяпазон D2:D8 з формуламі ў іншае месца на аркушы, Microsoft Excel аўтаматычна выправіць спасылкі ў гэтых формулах, перанясучы іх на новае месца і спыніць падлік:
Задача: скапіяваць дыяпазон з формуламі так, каб формулы не змяняліся і заставаліся ранейшымі з захаваннем вынікаў вылічэнняў.
Спосаб 1. Абсалютныя спасылкі
Спосаб 2: Часова адключыць формулы
Каб прадухіліць змяненне формул пры капіраванні, трэба (часова) пераканацца, што Excel перастае разглядаць іх як формулы. Гэта можна зрабіць, замяніўшы знак роўнасці (=) любым іншым сімвалам, які звычайна не сустракаецца ў формулах, напрыклад, знакам хэшта (#) або парай амперсандаў (&&) для часу капіравання. Для гэтага:
- Выберыце дыяпазон з формуламі (у нашым прыкладзе D2:D8)
- націсніце Ctrl + H, на клавіятуры або на ўкладцы Галоўная – Знайсці і выбраць – Замяніць (Галоўная — Знайсці&Выбраць — Замяніць)
- У якое з'явілася дыялогавым акне увядзіце тое, што мы шукаем і чым замяняем, і ў параметры (Параметры) не забудзьцеся ўдакладніць Вобласць пошуку – Формулы. Націскаем Замяніць усё (Замяніць усе).
- Скапіруйце атрыманы дыяпазон з адключанымі формуламі ў патрэбнае месца:
- Замяняць # on = назад, выкарыстоўваючы тое ж акно, вяртаючы функцыянальнасць формулам.
Спосаб 3: Скапіруйце праз нататнік
Гэты спосаб нашмат хутчэй і прасцей.
Націсніце спалучэнне клавіш Ctrl+Ё альбо кнопка Паказаць формулы таб формула (Формулы — Паказаць формулы), уключыць рэжым праверкі формул – у ячэйках замест вынікаў будуць адлюстроўвацца формулы, па якіх яны разлічваюцца:
Скапіруйце наш дыяпазон D2:D8 і ўстаўце яго ў стандарт ноўтбук:
Цяпер вылучыце ўсё ўстаўленае (Ctrl + A), зноў скапіруйце ў буфер абмену (Ctrl + C) і ўстаўце на ліст у патрэбнае вам месца:
Засталося толькі націснуць кнопку Паказаць формулы (Паказаць формулы)каб вярнуць Excel у звычайны рэжым.
Заўвага: гэты метад часам не працуе на складаных табліцах з аб'яднанымі ячэйкамі, але ў пераважнай большасці выпадкаў ён працуе нармальна.
Спосаб 4. Макрас
Калі вам часта даводзіцца рабіць падобнае капіяванне формул без зрушэння спасылак, то ёсць сэнс выкарыстоўваць для гэтага макрас. Націсніце спалучэнне клавіш Alt + F11 альбо кнопка Візуальны Бейсик таб распрацоўшчык (Распрацоўшчык), устаўце новы модуль праз меню Устаўка – модуль і скапіруйце туды тэкст гэтага макраса:
Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("Выберыце ячэйкі з формуламі для капіравання.", _ "Дакладна скапіяваць формулы", Па змаўчанні:=Selection.Address, Type := 8) Калі copyRange не знаходзіць нічога, то выйдзіце Sub Set pasteRange = Application.InputBox("Цяпер выберыце дыяпазон устаўкі." & vbCrLf & vbCrLf & _ "Дыяпазон павінен быць роўны па памеры зыходнаму " & vbCrLf & _ " дыяпазону ячэек скапіяваць." , "Дакладна скапіяваць формулы", _ Па змаўчанні:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Then MsgBox "Капіяваць і ўставіць дыяпазоны адрозніваюцца па памеры!", vbExclamation, "Copy error " Exit Sub End If If pasteRange Is Nothing Then Exit Sub Else pasteRange.Formula = copyRange.Formula End If End SubВы можаце выкарыстоўваць кнопку для запуску макраса. Макрас таб распрацоўшчык (Распрацоўшчык — Макрасы) або спалучэнне клавіш Alt + F8. Пасля запуску макраса ён папросіць вас выбраць дыяпазон з зыходнымі формуламі і дыяпазон устаўкі і аўтаматычна скапіруе формулы:
- Зручны прагляд формул і вынікаў адначасова
- Чаму стыль спасылкі R1C1 неабходны ў формулах Excel
- Як хутка знайсці ўсе клеткі з формуламі
- Інструмент для капіравання дакладных формул з надбудовы PLEX