Csak a látható sorokra illessze beexcel számok, képletek, szöveg többféleképpen is elkészíthető. Ha számokat, képleteket vagy szöveget kell beszúrnia a táblázat nem minden sorába, használhatja a szűrőt. A szűrő beállításáról és az Excelben történő szűrésről lásd a „Szűrés az Excelben” című cikket. De ahhoz, hogy adatokat csak a látható cellákba szúrjon be, saját módszerekre van szüksége, különösen, ha sok sor van.
Első út - rendes .
Vegyük ezt a táblázatot. A táblázat egy lesz az összes példához.
Távolítsa el a szűrőt az összes 2-es számmal a táblázatból. A fennmaradó látható cellákba írja be a 600-as számot. A B2 cellába írja be a 600-as számot, majd másolja le az oszlopba (húzza a B2 cella jobb alsó sarkát). Az értékek csak a látható cellákba lettek másolva. Ugyanígy beszúrhat képleteket is. A következő képletet írjuk a C2 cellába. \u003d A2 * 10
Így alakult.
Töröljük a szűrőt. Kiderült egy ilyen asztal.
A képlet és a számok csak a szűrt sorokba kerültek.
A második út.
Szűrjük is az adatokat. Az első cellába írunk egy számot, képletet, szöveget stb. Most, ha több ezer sor van, akkor jelölje ki a cellákat így: nyomja meg a "Ctrl" + "Shift" billentyűket + a gombot (nyíl) lefelé (vagy a gombot felfelé, attól függően, hogy hol akarjuk kijelölni a cellákat - lent) vagy a cella fölé, amelybe a számot írták) .
Most, vagy nyomja meg a "Ctrl" + G billentyűkombinációt vagy az F5 billentyűt. Megjelenik a Ugrás párbeszédpanel. Nyomja meg a "Kiválasztás..." gombot. És az új párbeszédpanelen "Cellacsoport kiválasztása" jelölje be a "Csak látható cellák" szavak melletti négyzetet.Kattintson az "OK" gombra. Ezután illessze be a szokásos módon.

Egy másik módja a "Cellacsoport kiválasztása" párbeszédpanel meghívásának.A Kezdőlap lap Szerkesztés részében kattintson a Keresés és kijelölés gombra. A megjelenő listában kattintson a "Cellacsoport kiválasztása" funkcióra.

Nak nek a kijelölt cellák látható celláinak kitöltése Excel oszlopok , nyomja meg a "Ctrl" + D billentyűkombinációt. És az összes kijelölt oszlop adattal vagy képlettel lesz kitöltve, mint az első cellában. Példánkban a 800-as számot írtuk a D oszlop D2 cellájába.

A másolt cellák beillesztése csak látható/szűrt cellákba

Általában a cikk jelentése szerintem egyértelmű a címből. Csak egy kicsit bővítem.

Nem titok, hogy az Excel csak látható sorok kiválasztását teszi lehetővé. (például ha ezek közül néhány el van rejtve, vagy ha szűrőt alkalmaznak).

Tehát, ha csak a látható cellákat másolja így, akkor azok a várt módon lesznek átmásolva. De amikor megpróbálja beilleszteni a másolt fájlt a szűrt tartományba (vagy rejtett sorokat tartalmaz), a beillesztés eredménye nem lesz pontosan az, amit várt. Az adatok még rejtett sorokba is bekerülnek.

Másoljon ki egyetlen cellatartományt, és csak a látható helyre illessze be
Ha csak a látható cellákba szeretne adatokat beszúrni, alkalmazza a következő makrót:

Opció Explicit Dim rCopyRange As Range "Adatok másolása ezzel a makróval Sub My_Copy() If Selection.Count > 1 then Set rCopyRange = Selection.SpecialCells(xlVisible) Else: Set rCopyRange = ActiveCell End If End Sub "Adatok beillesztése ezzel a makróval kezdve: a kijelölt cellák Sub My_Paste() If rCopyRange is Nothing then Exit Sub If rCopyRange.Areas.Count > 1 Then MsgBox "A beillesztett tartomány nem tartalmazhat több területet!", vbCritical, "Érvénytelen tartomány": Kilépés a Sub Dim rCell tartományként , li As Long, le As Long, lCount As Long, iCol As Integer, iCalculation As Integer Application.ScreenUpdating = False iCalculation = Application.Calculation: Application.Calculation = -4135 For iCol = 1 Számoljon rCopyRange.Columns =0. : lCount = 0: le = iCol - 1 az rCopyRange.Columns(iCol).Cells minden egyes rCelljéhez. .Hidden = False Akkor rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Loop While lCount >= rCell.Row - rCopyRange.Cells(1).Row Next rCell Next iCol Application.ScreenUpdating = True: Application.Calculation = iCalculation End Sub

A kép teljessé tételéhez célszerű ezeket a makrókat gyorsbillentyűkhöz rendelni (az alábbi kódokban ez automatikusan megtörténik, amikor kinyitja a kódot tartalmazó könyvet). Ehhez az alábbi kódokat egyszerűen be kell másolni a modulba Ezt a könyvet (ez a munkafüzet) :

Opció Explicit "A gyorsbillentyűk hozzárendelésének megszüntetése a munkafüzet bezárása előtt Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey "^q": Application.OnKey "^w" End Sub "Gyorsbillentyűk hozzárendelése munkafüzet megnyitásakor Private Sub Workbook_Open() Application.OnKey "^q ", "My_Copy": Application.OnKey "^w", "My_Paste" End Sub

Most a gombok megnyomásával átmásolhatja a kívánt tartományt ctrl + q , és helyezze be a szűrtbe - ctrl + w .

Példa letöltése

(46,5 KiB, 9622 letöltés)

Csak a látható cellákat másolja, és csak a láthatókba illessze be
Az oldal látogatóinak kérésére úgy döntöttem, hogy finomítom ezt az eljárást. Mostantól bármilyen tartományt másolhat: rejtett sorokkal, rejtett oszlopokkal, és a másolt cellákat is beillesztheti bármilyen tartományba: rejtett sorokba, rejtett oszlopokba. Pontosan ugyanúgy működik, mint az előző: a gombok megnyomásával ctrl + q másolja a kívánt tartományt (rejtett/szűrt sorokkal és oszlopokkal, vagy nem rejtett), és illessze be egy billentyűkóddal ctrl + w . A beszúrás rejtett / szűrt sorokban és oszlopokban vagy rejtett sorok és oszlopok nélkül is történik.
Ha vannak képletek a másolt tartományban, akkor csak a cellaértékek másolhatók a referenciaeltolódások elkerülése érdekében - pl. értékek beszúrásakor nem képletek kerülnek beszúrásra, hanem számításuk eredménye. Vagy ha meg kell őrizni azoknak a celláknak a formátumát, amelyekbe a beillesztés történik - csak a cellaértékek másolása és beillesztése történik. Ehhez cserélje ki a sort a kódban (az alábbi fájlban):

rCell.Copy rResCell.Offset(lr,lc)

erre:

rResCell.Offset(lr, lc) = rCell.Érték

Mindkét sor megtalálható az alábbi fájlban, csak azt kell hagyni, amelyik jobban megfelel a feladatainak.

Letöltési példa:

(54,5 KiB, 7928 letöltés)

Lásd még:
[]

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"jobb","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 "texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"jobb","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":150 textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; szélesség:100%; magasság:100% ; háttérszín: #333333; átlátszatlanság: 0,6; szűrő:a lpha(opacity=60);","titlecss":"display:block; pozíció:relatív; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; pozíció:relatív; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; szín:#fff; margin-top:8px;","buttoncss":"display:block; pozíció:relatív; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Pavlov Nikolay

Ebben a cikkben szeretném a legtöbbet bemutatni hatékony technikák bedolgozni Microsoft Excel, amit az elmúlt 10 év során gyűjtöttem össze, amikor projekteken dolgoztam és tréningeket vezettem ezzel a csodálatos programmal. Itt nincs leírás a szuperbonyolult technológiákról, de minden napra vannak trükkök - egyszerűek és hatékonyak, "víz" nélkül leírva - csak "száraz maradék". E példák többségének elsajátítása legfeljebb egy-két percet vesz igénybe, de sokkal többet spórol meg.

Gyors ugrás a kívánt lapra

Dolgozol vele véletlenül excel könyvek nagyszámú lapból áll? Ha több mint egy tucat van belőlük, akkor minden egyes átmenet a következő kívánt lapra önmagában kis problémát jelent. Egyszerű és elegáns megoldás erre a problémára, ha az ablak bal alsó sarkában nem a bal, hanem a jobb egérgombbal kattintunk a lapfülek görgetésére szolgáló gombokra - a könyv tartalomjegyzéke megjelenik teljes lista minden lapot, és egy mozdulattal a kívánt lapra léphet:

Ez sokkal gyorsabb, mintha ugyanazokkal a gombokkal görgetné végig a lapfüleket, keresve, amire szüksége van.

Másolás formázási sérülés nélkül

Hány százszor (ezer?) láttam ezt a képet, amint a hallgatóim mögé állok az edzéseken: a felhasználó az első cellába beírja a képletet, majd a teljes oszlopra "nyújtja", megtörve az alábbi sorok formázását, mivel ez a módszer nemcsak a képletet másolja, hanem a cellaformátumot is. Ennek megfelelően a továbbiakban a sérülést manuálisan kell kijavítani. Egy másodperc a másoláshoz, majd 30 - a másolással elrontott terv javításához.

Az Excel 2002-től kezdve van megoldás erre a problémára – egyszerű és elegáns. Közvetlenül a képlet teljes oszlopba másolása (húzása) után kell használnia az intelligens címkét - egy kis ikont, amely ideiglenesen megjelenik a tartomány jobb alsó sarkában. Ha rákattint, megjelenik a lehetséges másolási lehetőségek listája, ahol kiválaszthatja a Csak értékek másolása (Formázás nélkül kitöltése) lehetőséget. Ebben az esetben a képletek másolásra kerülnek, de a formázás nem:

Csak a látható cellákat másolja

Ha több mint egy hete dolgozik a Microsoft Excel programban, akkor már találkoznia kellett egy hasonló problémával: bizonyos esetekben a cellák másolásakor és beillesztésekor több cellát szúrnak be, mint amennyit első pillantásra másoltak. Ez akkor fordulhat elő, ha a másolt tartomány rejtett sorokat/oszlopokat, csoportosításokat, részösszegeket vagy szűrést tartalmazott. Vegyünk példának egy ilyen esetet:

Ebben a táblázatban a részösszegeket számítjuk ki, és a sorokat városok szerint csoportosítjuk – ez könnyen érthető a táblázat bal oldalán található plusz-mínusz gombokkal és a látható sorok számozásának töréseivel. Ha ebből a táblázatból a szokásos módon kijelölünk, másolunk és beillesztünk adatokat, 24 extra sort kapunk. Csak az összegeket szeretnénk másolni és beilleszteni!

A problémát úgy oldhatja meg, hogy gondosan kiválasztja az összegek egyes sorait, miközben lenyomva tartja a CTRL billentyűt – ugyanúgy, mint a nem összefüggő tartományok kiválasztásánál. De mi van akkor, ha nem három vagy öt ilyen sor van, hanem több száz vagy ezer? Van egy másik, gyorsabb és kényelmesebb módja:

Válassza ki a másolandó tartományt (példánkban ez A1:C29)

Nyomja meg az F5 billentyűt a billentyűzeten, majd a Speciális gombot a megnyíló ablakban.
Megjelenik egy ablak, amelyben a felhasználó nem mindent, hanem csak a szükséges cellákat választhatja ki:

Ebben az ablakban válassza a Csak látható cellák (Csak látható cellák) lehetőséget, majd kattintson az OK gombra.

A kapott kijelölés most biztonságosan másolható és beilleszthető. Ennek eredményeként pontosan másolatot kapunk látható sejtekés a felesleges 29 helyett csak azt az 5 sort illesszük be, amelyre szükségünk van.

Ha felmerül a gyanú, hogy gyakran kell ilyen műveletet végrehajtania, akkor érdemes hozzáadni a panelhez Microsoft eszközök Excel gomb egy ilyen függvény gyors meghívásához. Ezt az Eszközök> Testreszabás menüben teheti meg, majd lépjen a Parancsok fülre, a Szerkesztés kategóriában keresse meg a Látható cellák kijelölése gombot, és vigye az egérrel az eszköztárra:

Sorok oszlopokká alakítása és fordítva

Egyszerű művelet, de ha nem tudja, hogyan kell helyesen csinálni, fél napot is eltölthet az egyes cellák manuális áthúzásával:

Valójában minden egyszerű. A magasabb matematikának abban a részében, amely mátrixokat ír le, ott van a transzpozíció fogalma – egy olyan művelet, amely egy mátrixban sorokat és oszlopokat cserél fel egymással. A Microsoft Excelben ez három lépésben történik: Másolja ki a táblázatot

Kattintson jobb gombbal egy üres cellára, és válassza a Különleges beillesztés lehetőséget

A megnyíló ablakban állítsa be a Transpone (Transpose) jelzőt, és kattintson az OK gombra:

Gyorsan hozzáadhat adatokat egy diagramhoz

Képzeld el egyszerű helyzet: van egy jelentésed az elmúlt hónapról vizuális diagrammal. A feladat az, hogy már erre a hónapra új számadatokat adjunk a diagramhoz. A megoldás klasszikus módja a diagram adatforrás ablakának megnyitása, ahol a nevének megadásával és a kívánt adatokkal rendelkező tartomány kiválasztásával új adatsort adhatunk hozzá. És gyakran ezt könnyebb mondani, mint megtenni - minden a diagram összetettségétől függ.

Egy másik módszer – egyszerű, gyors és gyönyörű – az új adatokkal rendelkező cellák kijelölése, másolása (CTRL+C) és beillesztése (CTRL+V) közvetlenül a diagramba. Az Excel 2003 a későbbi verziókkal ellentétben még azt is támogatja, hogy az adatcellák kiválasztott tartományát áthúzzuk, és az egérrel közvetlenül a diagramba dobjuk!

Ha ellenőrizni szeretné az összes árnyalatot és finomságot, akkor nem a szokásosat használhatja, hanem speciális betét a menü Szerkesztés> Speciális beillesztés parancsával. Ebben az esetben a Microsoft Excel megjelenít egy párbeszédpanelt, amely lehetővé teszi annak konfigurálását, hogy pontosan hol és hogyan kerüljön hozzáadásra az új adatok:

Hasonlóképpen könnyen létrehozhat diagramot különböző munkalapok különböző táblázatainak adataiból. Ugyanannak a feladatnak a klasszikus módon történő elvégzése sokkal több időt és erőfeszítést igényel.

Üres cellák kitöltése

Néhány program jelentéseinek eltávolítása után excel formátumban vagy létrehozásakor pivot táblák a felhasználók gyakran kapnak olyan táblázatokat, amelyek egyes oszlopaiban üres cellák találhatók. Ezek a kihagyások nem teszik lehetővé a szokásos ill praktikus eszközök az automatikus szűrő típusa és a válogatás. Természetesen az üregeket magasabb cellákból származó értékekkel kell kitölteni:

Természetesen kis adatmennyiséggel ez könnyen megoldható. egyszerű másolás- az A oszlop minden fejléccelláját kézzel lehúzva az üres cellákba. És ha a táblázat több száz vagy több ezer sorból és több tucat városból áll?

Egyetlen képlet segítségével gyorsan és szépen megoldható ez a probléma:

Jelölje ki az összes cellát az üres oszlopban (vagyis esetünkben az A1:A12 tartományt)

Ha csak az üres cellákat szeretné a kijelölésben tartani, nyomja meg az F5 billentyűt, majd a megnyíló navigációs ablakban kattintson a Kiválasztás gombra. Megjelenik egy ablak, amelyben kiválaszthatja, hogy mely cellákat szeretnénk kiemelni:

Állítsa a rádiógombot Üresre, majd kattintson az OK gombra. Most csak üres cellák maradhatnak a kijelölésben:

A kiválasztás megváltoztatása nélkül, i.e. az egér érintése nélkül írja be a képletet az első kijelölt cellába (A2). Nyomja meg az egyenlőségjelet, majd a felfelé mutató nyilat a billentyűzeten. Kapunk egy képletet, amely az előző cellára vonatkozik:

Ha az elkészített képletet egyszerre szeretné bevinni az összes üres cellába, ne az ENTER billentyűt, hanem a CTRL + ENTER kombinációt nyomja meg. A képlet kitölti az összes üres cellát:

Most már csak a képleteket értékekkel kell helyettesíteni az eredmények javítása érdekében. Válassza ki az A1:A12 tartományt, másolja ki és illessze be a cellákba az értékükkel a Speciális beillesztés segítségével.

Legördülő lista egy cellában

Egy trükk, amit túlzás nélkül mindenkinek tudnia kell, aki Excelben dolgozik. Használata szinte minden asztalt javíthat, függetlenül annak céljától. Minden edzésen igyekszem már az első napon megmutatni hallgatóimnak.

Az ötlet nagyon egyszerű - minden olyan esetben, amikor valamilyen halmazból kell adatokat megadni, ahelyett kézi bevitel egy cellában a billentyűzet segítségével válassza ki a kívánt értéket az egérrel a legördülő listából:

Termék kiválasztása árlistáról, vásárló neve a vevőkörből, alkalmazott teljes neve a személyzeti listáról stb. Ennek a funkciónak számos felhasználási módja van.

Legördülő lista létrehozása egy cellában:

Válassza ki azokat a cellákat, amelyekben legördülő listát szeretne létrehozni.

Ha Excel 2003-as vagy régebbi verziója van, válassza a menü Adatok> Érvényesítés parancsát. Ha Excel 2007/2010-et használ, lépjen az Adatok lapra, és kattintson az Adatok érvényesítése gombra.

A megnyíló ablakban válassza ki a Lista lehetőséget a legördülő listából.

A Forrás mezőben meg kell adnia azokat az értékeket, amelyeknek szerepelniük kell a listában. Íme a lehetséges lehetőségek:

Adja meg a szöveges beállításokat ebbe a mezőbe pontosvesszővel elválasztva

Ha a kezdeti értékkel rendelkező cellák tartománya az aktuális lapon van, akkor elegendő az egérrel kiválasztani.

Ha a könyv másik lapján van, akkor előre nevet kell adnia (jelölje ki a cellákat, nyomja meg a CTRL + F3 billentyűket, írja be a tartomány nevét szóközök nélkül), majd írja be ezt a nevet a mezőbe.

Ha a munkalap egyes cellái, sorai vagy oszlopai nem láthatók, átmásolhatja az összes cellát (vagy csak a látható cellákat). Alapértelmezés szerint az Excel nem csak a látható, hanem a rejtett vagy szűrt cellákat is másolja. Ha csak a látható cellákat szeretné másolni, kövesse az alábbi lépéseket. Például csak összefoglaló adatokat másolhat strukturált munkalapról.

Kövesse az alábbi lépéseket.

Jegyzet: Másoláskor az értékek sorokba és oszlopokba kerülnek egymás után. Ha a beillesztési terület rejtett sorokat vagy oszlopokat tartalmaz, előfordulhat, hogy fel kell oldania azokat az összes másolt adat megtekintéséhez.

Amikor rejtett cellákat tartalmazó vagy szűrőt alkalmazó adattartomány látható celláit másolja és illeszti be, észreveheti, hogy a rejtett cellák is beillesztésre kerülnek a látható cellákkal együtt. Sajnos nem módosíthatja ezt a beállítást, amikor egy cellatartományt másol és illeszt be a Webes Excelben, mert a Csak látható cellák beillesztése nem érhető el.

Ha azonban az adatokat táblázatként formázza és szűrőt alkalmaz, akkor csak a látható cellákat másolhatja és illesztheti be.

Ha nem kell formázni az adatokat táblázatként és a klasszikus Excel alkalmazás, megnyithat benne egy munkafüzetet a látható cellák másolásához és beillesztéséhez. Ehhez kattintson a gombra Megnyitás Excelbenés kövesse a Csak látható cellák másolása és beillesztése részben leírt lépéseket.

további információ

Bármikor feltehet kérdést az Excel Tech Community-nek, segítséget kérhet a Válaszok közösségben, és javaslatokat is tehet új funkció vagy fejlesztés a weboldalon