Tanulja meg, hogyan másolhat képletet több cellába, egy oszlopon lefelé, nem szomszédos cellákba, hogyan másolhat képleteket abszolút vagy vegyes cellahivatkozásokkal stb.
A képletek másolása az egyik leggyakoribb és legegyszerűbb feladat, amelyet egy tipikus, főleg képletekre támaszkodó táblázatban végezhet el. Ahelyett, hogy újra és újra beírná ugyanazt a képletet az Excelben, egyszerűen másolhat és illeszthet be egy képletet egy cellából több cellába.
Miután megírt egy képletet az Excelben, használhatja a másolás és beillesztés parancsokat több cellára, több nem szomszédos cellára vagy teljes oszlopra. Ha nem csinálja jól, akkor szörnyű # REF és /DIV0 hibákat fog kapni. Ebben a cikkben bemutatjuk azokat a különböző módszereket, amelyek segítségével másolhat képleteket az Excelben.
Képletek másolása és beillesztése Excelben
A Microsoft Excel különféle módokat kínál a képletek másolására relatív cellahivatkozásokkal, abszolút cellahivatkozásokkal vagy vegyes hivatkozásokkal.
- Képlet másolása egyik cellából a másikba
- Másolja a képlet egy celláját több cellába
- Képlet másolása a teljes oszlopba
- Képlet másolása formázás nélkül
- Képletek másolása nem szomszédos cellákba
- Képletek másolása a cellahivatkozások megváltoztatása nélkül
Képlet másolása egyik cellából a másikba az Excelben
Néha érdemes lehet egy képletet az Excel egyik cellájából a másikba másolni, hogy elkerülje a teljes képlet újraírását, és ezzel időt takarítson meg.
Tegyük fel, hogy megvan ez a táblázat:
Kevés módszer létezik a képlet egyik cellából a másikba másolására.
Először válassza ki a képletet tartalmazó cellát, kattintson a jobb gombbal, majd a helyi menüben válassza a „Másolás” lehetőséget a képlet másolásához. Vagy használhatja a „Kezdőlap” lap „Vágólap” részében a „Másolás” opciót.
De képleteket is másolhat egyszerűen a billentyűparancs megnyomásával Ctrl + C. Ez egy hatékonyabb és időtakarékosabb módszer.
Ezután a beilleszteni kívánt cellához lépünk, és nyomjuk meg a parancsikont Ctrl+V a képlet beillesztéséhez. Vagy kattintson a jobb gombbal a beilleszteni kívánt cellára, és kattintson a „Beillesztési beállítások” alatti lehetőségekre: vagy egyszerű „Beillesztés (P)” lehetőség, vagy beillesztés „Képlet (F)” lehetőségként.
Alternatív megoldásként kattintson a „Speciális beillesztés” gombra a hat beillesztési ikon alatt a „Speciális beillesztés” párbeszédpanel megnyitásához. Itt számos lehetőség közül választhat, beleértve a helyi menü hat beillesztési lehetőségét. A Beillesztés részben válassza az „Összes” vagy a „Képletek” lehetőséget, majd kattintson az „OK” gombra.
Most a beillesztett képletet tartalmazó cellának ugyanazokkal a képletekkel kell rendelkeznie (mint a másolt cellában), de különböző cellahivatkozásokkal. A cella címét az Excel automatikusan beállítja, hogy megfeleljen a beillesztett cella sorszámának.
Képlet másolása egy cellából több cellába
Ugyanaz a beillesztési művelet ugyanúgy működik, ha több cellát vagy cellatartományt jelölünk ki.
Jelölje ki a cellát a képlettel, és nyomja meg a gombot Ctrl + C a képlet másolásához. Ezután jelölje ki az összes cellát, ahová be szeretné illeszteni a képletet, és nyomja meg a gombot Ctrl+V a képlet beillesztéséhez, vagy a fenti beillesztési módszerek valamelyikével illessze be a képletet (mint azt az egycellánál tettük).
Képlet másolása egy teljes oszlopba vagy sorba
Az Excelben gyorsan átmásolhat egy képletet egy teljes oszlopba vagy sorba.
Képlet oszlopba vagy sorba másolásához először írjon be egy képletet egy cellába. Ezután válassza ki a képlet cellát (D1), és vigye a kurzort egy kis zöld négyzet fölé a cella jobb alsó sarkában. Ahogy az egérmutatót mozgatja, a kurzor fekete pluszjelre (+) változik, amelyet kitöltő fogantyúnak neveznek. Kattintson és tartsa lenyomva a kitöltő fogantyúját, és húzza a kívánt irányba (oszlopba vagy sorba) a cellák fölé a képlet másolásához.
Amikor egy képletet cellák tartományába másol, a képlet cellahivatkozásai automatikusan módosulnak a sorok és oszlopok relatív elhelyezkedése alapján, és a képlet a cellahivatkozások értékei alapján hajt végre számításokat (lásd alább).
A fenti példában, amikor a D1 (=A1*B1)/2) képletet a D2 cellába másoljuk, a relatív hivatkozás a helye (=A2*B2)/2) és így tovább változik.
Ugyanígy húzhatja a képletet a szomszédos cellákba balra, jobbra vagy felfelé.
A képlet teljes oszlopba másolásának másik módja az, hogy húzás helyett duplán kattint a kitöltő fogantyújára. Ha duplán kattint a kitöltési fogantyúra, azonnal alkalmazza a képletet a szomszédos cellára, amennyiben van adat.
Képlet másolása egy tartományba a formázás másolása nélkül
Amikor egy képletet másol egy cellatartományba a kitöltési fogantyúval, az a forráscella formázását is átmásolja, például a betűtípus színét vagy a háttérszínt, a pénznemet, a százalékot, az időt stb. (lásd alább).
A cellaformázás másolásának megakadályozásához húzza el a kitöltő fogantyút, és kattintson az utolsó cella jobb alsó sarkában található „Automatikus kitöltési beállítások” elemre. Ezután a legördülő menüben válassza a „Kitöltés formázás nélkül” lehetőséget.
Az eredmény:
Másoljon egy Excel-képletet csak számformázással
Ha csak a képletet és a formázást, például százalékos formátumot, tizedespontokat stb. szeretné másolni.
Másolja ki a képletet, és jelölje ki az összes cellát, amelybe a képletet másolni szeretné. A „Kezdőlap” lapon kattintson a szalag „Beillesztés” gombja alatti nyílra. Ezután kattintson a „Képletek és számformázás” ikonra (a % fx jelzésű ikonra) a legördülő menüben, hogy csak a képletet és a számformátumot illessze be.
Ez az opció csak a képlet- és számformázást másolja, de figyelmen kívül hagy minden más cellaformázást, például a háttérszínt, a betűtípus színét stb.
Képlet másolása nem szomszédos/nem szomszédos cellákba
Ha nem szomszédos cellákba vagy nem szomszédos tartományokba szeretne másolni egy képletet, ezt megteheti a Ctrl kulcs.
Jelölje ki a cellát a képlettel, és nyomja meg a gombot Ctrl + C lemásolni. Ezután jelölje ki a nem szomszédos cellákat/tartományokat, miközben lenyomva tartja a gombot Ctrl kulcs. Ezután nyomja meg Ctrl+V beilleszteni a képletet és ütni Belép teljesíteni.
Képletek másolása a cellahivatkozások megváltoztatása nélkül az Excelben
Amikor egy képletet egy másik cellába másol, az Excel automatikusan módosítja a cellahivatkozásokat az új helynek megfelelően. Ezek a cellahivatkozások a cellacím relatív helyét használják, ezért relatív cellahivatkozásnak nevezik őket ($ nélkül). Ha például a C1 cellában a „=A1*B1” képlet szerepel, és ezt a képletet a C2 cellába másolja, a képlet „=A2*B2”-re változik. Az összes fent tárgyalt módszer relatív hivatkozásokat használ.
Amikor egy képletet másol relatív cellahivatkozásokkal, az automatikusan megváltoztatja a hivatkozásokat, hogy a képlet a megfelelő sorokra és oszlopokra hivatkozzon. Ha abszolút hivatkozásokat használ egy képletben, akkor ugyanazt a képletet a rendszer a cellahivatkozások megváltoztatása nélkül másolja.
Ha egy dollárjelet ($) tesz egy cella oszlopbetűje és sorszáma elé (például $A$1), akkor a cellát abszolút cellává alakítja. Nem számít, hova másolja az abszolút cellahivatkozást tartalmazó képletet, a képlet soha nem fog. De ha egy képletben relatív vagy vegyes cellahivatkozás szerepel, használja az alábbi módszerek bármelyikét a cellahivatkozások megváltoztatása nélküli másolásához.
Képlet másolása abszolút cellahivatkozással a másolás-beillesztés módszerrel
Alkalmanként előfordulhat, hogy a cellahivatkozások megváltoztatása nélkül át kell másolnia/alkalmaznia a pontos képletet az oszlopban. Ha egy pontos képletet szeretne másolni vagy áthelyezni abszolút hivatkozással, tegye a következőket:
Először válassza ki a másolni kívánt képletet tartalmazó cellát. Ezután kattintson a képletsávra, válassza ki a képletet az egérrel, és nyomja meg a gombot Ctrl + C lemásolni. Ha át szeretné helyezni a képletet, nyomja meg a gombot Ctrl + X vágni. Ezután nyomja meg a Kilépés gombot a képletsor elhagyásához.
Alternatív megoldásként válassza ki a cellát a képlettel, és nyomja meg a gombot F2 gombot, vagy kattintson duplán a cellára. Ezzel a kijelölt cella szerkesztési módba kerül. Ezután válassza ki a képletet a cellában, és nyomja meg a gombot Ctrl + C a cellában lévő képlet szövegként másolásához.
Ezután válassza ki a célcellát, és nyomja meg a gombot Ctrl+V a képlet beillesztéséhez.
Most a pontos képlet a célcellába másolódik a cellahivatkozás módosítása nélkül.
Képletek másolása abszolút vagy vegyes cellahivatkozásokkal
Ha pontos képleteket szeretne áthelyezni vagy másolni a cellahivatkozások megváltoztatása nélkül, módosítsa a cella relatív hivatkozásait abszolút hivatkozásokra. Például, ha ($) jelet ad hozzá a relatív cellahivatkozáshoz (B1), az abszolút hivatkozássá válik ($B$1), így statikus marad, függetlenül attól, hogy a képletet hova másolják vagy helyezik át.
Néha azonban előfordulhat, hogy vegyes cellahivatkozásokat ($B1 vagy B$1) kell használnia egy dollár ($) jel hozzáadásával az oszlop betűje vagy a sor száma elé, hogy egy sor vagy oszlop a helyén maradjon.
Magyarázzuk meg egy példával. Tegyük fel, hogy rendelkezik ezzel a táblázattal, amely úgy számítja ki a havi megtakarítást, hogy minden hónapban levonja a bérleti díjat (B9) a keresetből (a B oszlopban).
Az alábbi példában a képlet abszolút cellahivatkozást (9 USD B$) használ a bérleti díj összegének a B9 cellába zárolásához, és egy relatív cellahivatkozást a B2 cellához, mert azt minden sorhoz módosítani kell, hogy minden hónapban megfeleljen. A B9 abszolút cellahivatkozás ($9 B$), mert ugyanazt a bérleti díjat szeretné levonni minden havi bevételből.
Tegyük fel, hogy át szeretné helyezni az egyenlegeket a C oszlopból az E oszlopba. Ha a képletet (a szokásos másolás/beillesztés módszerrel) kimásolja a C2 cellából (=B2-$B$9), beillesztéskor =D2-$B$9 értékre változik. az E2 cellában, így a számításaid rosszak!
Ebben az esetben módosítsa a relatív cellahivatkozást (B2) vegyes cellahivatkozásra ($B2) úgy, hogy a C2 cellába beírt képlet oszlopbetűje elé a „$” jelet adja.
És most, ha átmásolja vagy áthelyezi a képletet a C2 cellából az E2 cellába vagy bármely más cellába, és alkalmazza a képletet az oszlopban lefelé, az oszlophivatkozás változatlan marad, míg a sorszám minden cellához igazodik.
Excel-képletek másolása a hivatkozások módosítása nélkül a Jegyzettömb használatával
Minden képletet megtekinthet az Excel-táblázatban a Képlet megjelenítése opcióval. Ehhez lépjen a „Képletek” fülre, és válassza a „Képletek megjelenítése” lehetőséget.
Alternatív megoldásként a képletnézet módba léphet a gomb megnyomásával Ctrl + ` parancsikont, amely megjeleníti a munkalap minden képletét. A súlyos ékezetes billentyűt (`) a billentyűzet bal felső sarkában találja a számbillentyűket tartalmazó sorban (a KILÉPÉS gombot és az 1-es szám előtt).
Jelölje ki az összes másolni kívánt képletet tartalmazó cellát, és nyomja meg a gombot Ctrl + C másolni őket, ill Ctrl + X levágni őket. Ezután nyissa meg a Jegyzettömböt, és nyomja meg a gombot Ctrl+V hogy beillessze a képleteket a jegyzettömbbe.
Ezután válassza ki a képletet és másolja (Ctrl + C) a jegyzettömbből, és illessze be (Ctrl+V) abban a cellában, ahová a pontos képletet másolni szeretné. Másolhatja és beillesztheti őket egyenként vagy egyszerre.
A képletek beillesztése után kapcsolja ki a képletnézet módot a gomb megnyomásával Ctrl + ` vagy ismét lépjen a „Képletek” -> „Képletek megjelenítése” menüpontra.
Másolja ki a pontos képleteket az Excel Keresés és csere funkciójával
Ha pontos képleteket szeretne másolni, használhatja az Excel Keresés és csere eszközét is.
Jelölje ki az összes olyan cellát, amely tartalmazza a másolni kívánt képleteket. Ezután lépjen a „Kezdőlap” fülre, kattintson a „Keresés és kijelölés” lehetőségre a Szerkesztés csoportban, és válassza a „Csere” lehetőséget, vagy egyszerűen nyomja meg Ctrl + H a Keresés és csere párbeszédpanel megnyitásához.
A Keresés és csere párbeszédpanelen írja be az egyenlőségjelet (=) a „Mit keres” mezőbe. A „Csere erre” mezőbe írjon be egy olyan szimbólumot vagy karaktert, amely még nem része a képleteknek, például # vagy , stb. Ezután kattintson az „Összes cseréje” gombra.
Megjelenik a „6 cserét végrehajtottunk” üzenetdoboz (mivel 6 cellát jelöltünk ki képletekkel). Ezután kattintson az „OK” és a „Bezárás” gombra mindkét párbeszédpanel bezárásához. Ezzel minden egyenlő (=) jelet hash (#) jelekre cserél, és a képleteket szöveges karakterláncokká alakítja. Most a képletek cellahivatkozásai nem változnak másoláskor.
Most kiválaszthatja ezeket a cellákat, nyomja meg a gombot Ctrl + C másolásához és a célcellákba való beillesztéséhez Ctrl+V.
Végül vissza kell cserélnie a (#) jeleket (=) jelekre. Ehhez válassza ki mindkét tartományt (eredeti és másolt tartomány), és nyomja meg a gombot Ctrl + H a Keresés és csere párbeszédpanel megnyitásához. Ezúttal írja be a hash (#) jelet a „Keressen mit” mezőbe, és egyenlő (=) jelet a „Csere erre” mezőbe, majd kattintson a „Minden cseréje” gombra. Kattintson a „Bezárás” gombra a párbeszédpanel bezárásához.
Most a szöveges karakterláncok vissza lesznek konvertálva képletekre, és ezt az eredményt kapjuk:
Kész!