Spis treści
Problem
Załóżmy, że mamy taką prostą tabelę, w której kwoty są obliczane za każdy miesiąc w dwóch miastach, a następnie suma jest przeliczana na euro według kursu z żółtej komórki J2.
Problem polega na tym, że jeśli skopiujesz zakres D2:D8 z formułami w inne miejsce arkusza, to Microsoft Excel automatycznie poprawi łącza w tych formułach, przenosząc je w nowe miejsce i przestając liczyć:
Zadanie: skopiuj zakres z formułami, aby formuły się nie zmieniły i pozostały takie same, zachowując wyniki obliczeń.
Metoda 1. Linki bezwzględne
Metoda 2: Tymczasowo wyłącz formuły
Aby zapobiec zmianie formuł podczas kopiowania, musisz (tymczasowo) upewnić się, że program Excel przestaje traktować je jako formuły. Można to zrobić, zastępując znak równości (=) dowolnym innym znakiem, którego normalnie nie można znaleźć w formułach, takim jak znak krzyża (#) lub para znaków ampersand (&&) określająca czas kopiowania. Dla tego:
- Wybierz zakres z formułami (w naszym przykładzie D2:D8)
- Kliknij Ctrl + H na klawiaturze lub na karcie Strona główna – Znajdź i wybierz – Zamień (Dom — Znajdź i wybierz — Zamień)
- W wyświetlonym oknie dialogowym wpisujemy czego szukamy i czym zastępujemy oraz w parametry (Opcje) nie zapomnij wyjaśnić Zakres wyszukiwania – Wzory. Naciskamy Zamień wszystko (Zamień wszystko).
- Skopiuj wynikowy zakres z dezaktywowanymi formułami we właściwe miejsce:
- zastąpić # on = z powrotem używając tego samego okna, przywracając funkcjonalność formułom.
Metoda 3: Kopiuj za pomocą Notatnika
Ta metoda jest znacznie szybsza i łatwiejsza.
Naciśnij skrót klawiaturowy Ctrl+Ё lub przycisk Pokaż formuły Zakładka formuła (Formuły — Pokaż formuły), włączyć tryb sprawdzania formuł – zamiast wyników w komórkach pojawią się formuły, według których są obliczane:
Skopiuj nasz asortyment D2: D8 i wklej go do standardu Notatnik:
Teraz zaznacz wszystko wklejone (Ctrl + A), skopiuj ponownie do schowka (Ctrl + C) i wklej na arkuszu w potrzebne miejsce:
Pozostaje tylko nacisnąć przycisk Pokaż formuły (Pokaż formuły)aby przywrócić program Excel do normalnego trybu.
Uwaga: ta metoda czasami zawodzi w przypadku złożonych tabel ze scalonymi komórkami, ale w zdecydowanej większości przypadków działa dobrze.
Metoda 4. Makro
Jeśli często musisz wykonywać takie kopiowanie formuł bez przesuwania referencji, to ma sens użyć do tego makra. Naciśnij skrót klawiaturowy Alt + F11 lub przycisk Visual Basic Zakładka wywoływacz (Deweloper), wstaw nowy moduł przez menu Wstaw – moduł i skopiuj tam tekst tego makra:
Sub Copy_Formulas() Przyciemnij zakres copyRange jako zakres, wklejRange jako zakres w przypadku błędu Wznów następny Ustaw copyRange = Application.InputBox("Wybierz komórki z formułami do skopiowania.", _ "Kopiuj dokładnie formuły", Domyślnie:=Selection.Address, Typ := 8) Jeśli copyRange to nic, wyjdź z podzestawu pasteRange = Application.InputBox("Teraz wybierz zakres wklejania." & vbCrLf & vbCrLf & _ "Zakres musi mieć taki sam rozmiar jak oryginalny zakres komórek " & vbCrLf & _ " do skopiowania." , "Dokładnie skopiuj formuły", _ Domyślnie:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count then MsgBox "Kopiuj i wklej zakresy różnią się wielkością!", vbExclamation, „Błąd kopiowania” Wyjdź z Sub End If Jeśli pasteRange to nic, to wyjdź z Sub W przeciwnym razie pasteRange.Formula = copyRange.Formula End If End SubMożesz użyć przycisku, aby uruchomić makro. Makra Zakładka wywoływacz (Deweloper — Makra) lub skrót klawiaturowy Alt + F8. Po uruchomieniu makra poprosi Cię o wybranie zakresu z oryginalnymi formułami i zakresem wstawiania i automatycznie skopiuje formuły:
- Wygodne przeglądanie formuł i wyników jednocześnie
- Dlaczego styl odniesienia W1K1 jest potrzebny w formułach Excela
- Jak szybko znaleźć wszystkie komórki za pomocą formuł
- Narzędzie do kopiowania dokładnych formuł z dodatku PLEX