Dowiedz się, jak kopiować formułę do wielu komórek, w dół kolumny, do niesąsiadujących komórek, kopiować formuły z bezwzględnymi lub mieszanymi odwołaniami do komórek itp.
Kopiowanie formuł to jedno z najczęstszych i najłatwiejszych zadań wykonywanych w typowym arkuszu kalkulacyjnym, który opiera się głównie na formułach. Zamiast wielokrotnie wpisywać tę samą formułę w programie Excel, możesz po prostu łatwo skopiować i wkleić formułę z jednej komórki do wielu komórek.
Po napisaniu formuły w programie Excel możesz użyć poleceń kopiowania i wklejania do wielu komórek, wielu nieprzylegających komórek lub całych kolumn. Jeśli nie zrobisz tego dobrze, skończysz z okropnymi błędami #REF i /DIV0. W tym artykule pokażemy różne metody kopiowania formuł w programie Excel.
Jak kopiować i wklejać formuły w programie Excel
Microsoft Excel zapewnia różne sposoby kopiowania formuł z względnymi odwołaniami do komórek, bezwzględnymi odwołaniami do komórek lub odwołaniami mieszanymi.
- Skopiuj formułę z jednej komórki do drugiej
- Skopiuj formułę jedną komórkę do wielu komórek
- Kopiowanie formuły do całej kolumny
- Kopiowanie formuły bez formatowania
- Skopiuj formuły do niesąsiadujących komórek
- Kopiuj formuły bez zmiany odwołań do komórek
Jak skopiować formułę z jednej komórki do drugiej w programie Excel?
Czasami możesz chcieć skopiować formułę z jednej komórki do drugiej w programie Excel, aby uniknąć ponownego wpisywania całej formuły i zaoszczędzić trochę czasu.
Powiedzmy, że mamy ten stół:
Istnieje kilka metod kopiowania formuły z jednej komórki do drugiej.
Najpierw wybierz komórkę z formułą i kliknij prawym przyciskiem myszy, a następnie w menu kontekstowym wybierz „Kopiuj”, aby skopiować formułę. Możesz też użyć opcji „Kopiuj” w sekcji „Schowek” na karcie „Strona główna”.
Ale możesz też kopiować formuły, naciskając skrót klawiaturowy Ctrl + C. Jest to bardziej wydajna i oszczędzająca czas metoda.
Następnie przechodzimy do komórki, którą chcemy wkleić, naciskamy skrót Ctrl + V wkleić formułę. Lub kliknij prawym przyciskiem myszy komórkę, którą chcesz wkleić, i kliknij opcje w sekcji „Opcje wklejania”: prosta opcja „Wklej (P)” lub wklej jako opcję „Formuła (F)”.
Możesz też kliknąć „Wklej specjalnie” pod sześcioma ikonami wklejania, aby otworzyć okno dialogowe „Wklej specjalnie”. Tutaj masz kilka opcji, w tym sześć opcji wklejania z menu kontekstowego. Wybierz „Wszystkie” lub „Formuły” w sekcji Wklej i kliknij „OK”.
Teraz komórka z wklejoną formułą powinna mieć te same formuły (jak ta w skopiowanej komórce), ale z różnymi odwołaniami do komórek. Adres komórki jest automatycznie dostosowywany przez program Excel, aby pasował do numeru wiersza wklejonej komórki.
Skopiuj formułę z jednej komórki do wielu komórek
Ta sama operacja wklejania działa tak samo, jeśli wybierzemy wiele komórek lub zakres komórek.
Wybierz komórkę z formułą i naciśnij Ctrl + C skopiować formułę. Następnie zaznacz wszystkie komórki, w których chcesz wkleić formułę, i naciśnij Ctrl + V aby wkleić formułę lub użyj jednej z powyższych metod wklejania, aby wkleić formułę (tak jak zrobiliśmy dla pojedynczej komórki).
Skopiuj formułę do całej kolumny lub wiersza
W programie Excel możesz szybko skopiować formułę do całej kolumny lub wiersza.
Aby skopiować formułę do kolumny lub wiersza, najpierw wprowadź formułę w komórce. Następnie zaznacz komórkę z formułą (D1) i umieść kursor nad małym zielonym kwadratem w prawym dolnym rogu komórki. Gdy najedziesz kursorem, kursor zmieni się w czarny znak plus (+), który nazywa się uchwytem wypełnienia. Kliknij i przytrzymaj ten uchwyt wypełniania, a następnie przeciągnij go w dowolnym kierunku (kolumna lub wiersz) nad komórkami, aby skopiować formułę.
Po skopiowaniu formuły do zakresu komórek odwołania do komórek formuły zostaną automatycznie dostosowane na podstawie względnej lokalizacji wierszy i kolumn, a formuła wykona obliczenia na podstawie wartości w tych odwołaniach do komórek (patrz poniżej).
W powyższym przykładzie, gdy formuła w D1 (=A1*B1)/2) została skopiowana do komórki D2, odwołanie względne zmienia się na podstawie jego lokalizacji (=A2*B2)/2) i tak dalej.
W ten sam sposób możesz przeciągnąć formułę do sąsiednich komórek w lewo, w prawo lub w górę.
Innym sposobem skopiowania formuły do całej kolumny jest dwukrotne kliknięcie uchwytu wypełniania zamiast przeciągania go. Dwukrotne kliknięcie uchwytu wypełniania powoduje natychmiastowe zastosowanie formuły, o ile w sąsiedniej komórce znajdują się jakiekolwiek dane.
Skopiuj formułę do zakresu bez kopiowania formatowania
Gdy kopiujesz formułę do zakresu komórek za pomocą uchwytu wypełniania, kopiuje ona również formatowanie komórki źródłowej, takie jak kolor czcionki lub kolor tła, waluta, wartość procentowa, czas itp. (jak pokazano poniżej).
Aby zapobiec kopiowaniu formatowania komórek, przeciągnij uchwyt wypełniania i kliknij „Opcje automatycznego wypełniania” w prawym dolnym rogu ostatniej komórki. Następnie z rozwijanego menu wybierz „Wypełnij bez formatowania”.
Wynik:
Skopiuj formułę Excela tylko z formatowaniem liczb
Jeśli chcesz skopiować formułę tylko z formułą i formatowaniem, takim jak format procentowy, kropki dziesiętne itp.
Skopiuj formułę i zaznacz wszystkie komórki, do których chcesz skopiować formułę. Na karcie „Strona główna” kliknij strzałkę pod przyciskiem „Wklej” na wstążce. Następnie kliknij ikonę „Formuły i formatowanie liczb” (ikona z % fx) z menu rozwijanego, aby wkleić tylko formułę i formatowanie liczb.
Ta opcja kopiuje tylko formatowanie formuł i liczb, ale ignoruje wszystkie inne formatowania komórek, takie jak kolor tła, kolor czcionki itp.
Skopiuj formułę do nieprzylegających/niesąsiadujących komórek
Jeśli chcesz skopiować formułę do niesąsiadujących komórek lub niesąsiadujących zakresów, możesz to zrobić za pomocą klawisz kontrolny klucz.
Wybierz komórkę z formułą i naciśnij Ctrl + C by go skopiować. Następnie wybierz niesąsiadujące komórki/zakresy, naciskając i przytrzymując klawisz kontrolny klucz. Następnie naciśnij Ctrl + V wkleić formułę i nacisnąć Wchodzić ukończyć.
Kopiowanie formuł bez zmiany odwołań do komórek w programie Excel
Gdy formuła jest kopiowana do innej komórki, program Excel automatycznie zmienia odwołania do komórek, aby dopasować je do nowej lokalizacji. Te odwołania do komórek używają względnej lokalizacji adresu komórki, dlatego są nazywane względnymi odwołaniami do komórek (bez $). Na przykład, jeśli masz formułę „=A1*B1” w komórce C1 i skopiujesz tę formułę do komórki C2, formuła zmieni się na „=A2*B2”. Wszystkie metody, które omówiliśmy powyżej, wykorzystują odniesienia względne.
Gdy kopiujesz formułę ze względnymi odwołaniami do komórek, automatycznie zmienia ona odwołania, tak aby formuła odwoływała się do odpowiednich wierszy i kolumn. Jeśli użyjesz odwołań bezwzględnych w formule, ta sama formuła zostanie skopiowana bez zmiany odwołań do komórek.
Gdy umieścisz znak dolara ($) przed literą kolumny i numerem wiersza w komórce (na przykład $A$1), zamienia komórkę w komórkę bezwzględną. Teraz bez względu na to, gdzie skopiujesz formułę zawierającą bezwzględne odwołanie do komórki, formuła nigdy nie zostanie skopiowana. Ale jeśli masz względne lub mieszane odwołanie do komórki w formule, użyj dowolnej z poniższych metod, aby skopiować bez zmiany odwołań do komórek.
Kopiuj formułę z bezwzględnym odwołaniem do komórki za pomocą metody kopiuj-wklej
Czasami może być konieczne skopiowanie/zastosowanie dokładnej formuły w dół kolumny bez zmiany odwołań do komórek. Jeśli chcesz skopiować lub przenieść dokładną formułę z odniesieniem bezwzględnym, wykonaj następujące czynności:
Najpierw wybierz komórkę z formułą, którą chcesz skopiować. Następnie kliknij pasek formuły, wybierz formułę za pomocą myszy i naciśnij Ctrl + C by go skopiować. Jeśli chcesz przenieść formułę, naciśnij Ctrl + X by go wyciąć. Następnie naciśnij wyjście klawisz, aby opuścić pasek formuły.
Alternatywnie wybierz komórkę z formułą i naciśnij F2 lub kliknij dwukrotnie komórkę. Spowoduje to przejście wybranej komórki w tryb edycji. Następnie wybierz formułę w komórce i naciśnij Ctrl + C aby skopiować formułę z komórki jako tekst.
Następnie wybierz komórkę docelową i naciśnij Ctrl + V wkleić formułę.
Teraz dokładna formuła jest kopiowana do komórki docelowej bez żadnych zmian w odwołaniu do komórki.
Kopiuj formuły z bezwzględnymi lub mieszanymi odwołaniami do komórek
Jeśli chcesz przenieść lub skopiować dokładne formuły bez zmiany odwołań do komórek, zmień względne odwołania do komórek na odwołania bezwzględne. Na przykład dodanie znaku ($) do względnego odwołania do komórki (B1) powoduje, że jest to odwołanie bezwzględne ($B$1), więc pozostaje ono statyczne bez względu na to, gdzie formuła jest kopiowana lub przenoszona.
Czasami jednak może być konieczne użycie mieszanych odwołań do komórek ($B1 lub B$1), dodając znak dolara ($) przed literą kolumny lub numerem wiersza, aby zablokować wiersz lub kolumnę w miejscu.
Wyjaśnijmy na przykładzie. Załóżmy, że masz tę tabelę, która oblicza miesięczne oszczędności, odejmując czynsz (B9) od zarobków (w kolumnie B) każdego miesiąca.
W poniższym przykładzie formuła używa bezwzględnego odwołania do komórki ($B$9) w celu zablokowania kwoty czynszu w komórce B9 oraz względnego odwołania do komórki B2, ponieważ musi być dostosowywane dla każdego wiersza, aby odpowiadało co miesiąc. B9 jest bezwzględnym odniesieniem do komórki (9 USD B, ponieważ chcesz odjąć tę samą kwotę czynszu od zarobków każdego miesiąca.
Załóżmy, że chcesz przenieść salda z kolumny C do kolumny E. Jeśli skopiujesz formułę (zwykłą metodą kopiuj / wklej) z komórki C2 (= B2-$ B $ 9) zmieni się na = D2- $ B $ 9 po wklejeniu w komórce E2, czyniąc twoje obliczenia błędnymi!
W takim przypadku zmień względne odwołanie do komórki (B2) na mieszane odwołanie do komórki ($B2), dodając znak „$” przed literą kolumny formuły wprowadzonej w komórce C2.
A teraz, jeśli skopiujesz lub przeniesiesz formułę z komórki C2 do E2 lub dowolnej innej komórki i zastosujesz formułę w dół kolumny, odwołanie do kolumny pozostanie takie samo, podczas gdy numer wiersza zostanie dostosowany dla każdej komórki.
Kopiuj Wklej formuły Excela bez zmiany odniesień za pomocą Notatnika
Możesz zobaczyć każdą formułę w arkuszu kalkulacyjnym Excel, używając opcji Pokaż formułę. Aby to zrobić, przejdź do zakładki „Formuły” i wybierz „Pokaż formuły”.
Alternatywnie możesz przejść do trybu widoku formuły, naciskając Ctrl + ` skrót, który wyświetla każdą formułę w arkuszu. Klawisz z akcentem grobowym (`) można znaleźć w lewym górnym rogu klawiatury w rzędzie z klawiszami numerycznymi (pod WYJŚCIE i przed klawiszem cyfry 1).
Zaznacz wszystkie komórki z formułami, które chcesz skopiować, i naciśnij Ctrl + C skopiować je, lub Ctrl + X aby je wyciąć. Następnie otwórz Notatnik i naciśnij Ctrl + V aby wkleić formuły do notatnika.
Następnie wybierz formułę i skopiuj (Ctrl + C) z notatnika i wklej (Ctrl + V) w komórce, do której chcesz skopiować dokładną formułę. Możesz je kopiować i wklejać pojedynczo lub wszystkie naraz.
Po wklejeniu formuł wyłącz tryb widoku formuł, naciskając Ctrl + ` lub ponownie przejdź do „Formuły” -> „Pokaż formuły”.
Skopiuj dokładne formuły za pomocą funkcji Znajdź i zamień w programie Excel
Jeśli chcesz skopiować zakres dokładnych formuł, możesz również użyć w tym celu narzędzia Znajdź i zamień w programie Excel.
Zaznacz wszystkie komórki zawierające formuły, które chcesz skopiować. Następnie przejdź do zakładki „Strona główna”, kliknij „Znajdź i wybierz” w grupie Edytowanie i wybierz opcję „Zamień” lub po prostu naciśnij Ctrl + H aby otworzyć okno dialogowe Znajdź i zamień.
W oknie dialogowym Znajdź i zamień wprowadź znak równości (=) w polu „Znajdź co”. W polu „Zamień na” wprowadź symbol lub znak, który nie jest jeszcze częścią formuł, np. # lub itd. Następnie kliknij przycisk „Zamień wszystko”.
Otrzymasz komunikat z komunikatem „Zrobiliśmy 6 zamienników” (ponieważ wybraliśmy 6 komórek z formułami). Następnie kliknij „OK” i „Zamknij”, aby zamknąć oba okna dialogowe. Spowoduje to zastąpienie wszystkich znaków równości (=) znakami hash (#) i zamienia formuły w ciągi tekstowe. Teraz odwołania do komórek formuł nie zostaną zmienione podczas kopiowania.
Teraz możesz zaznaczyć te komórki, naciśnij Ctrl + C aby je skopiować i wkleić do komórek docelowych za pomocą Ctrl + V.
Na koniec musisz zmienić znaki (#) z powrotem na (=). W tym celu zaznacz oba zakresy (oryginalny i skopiowany) i naciśnij Ctrl + H aby otworzyć okno dialogowe Znajdź i zamień. Tym razem wpisz znak krzyża (#) w polu „Znajdź co”, znak równy (=) w polu „Zamień na” i kliknij przycisk „Zamień wszystko”. Kliknij „Zamknij”, aby zamknąć okno dialogowe.
Teraz ciągi tekstowe są konwertowane z powrotem na formuły, a otrzymasz następujący wynik:
Gotowe!