Feltételes formázás (5)
Listák és tartományok (5)
Makrók (VBA eljárások) (63)
Vegyes (39)
Excel hibák és hibák (4)

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

Általában a cikk értelme szerintem már a címből is kiderü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 némelyikük el van rejtve, vagy ha szűrőt alkalmaznak).

Tehát ha csak látható cellákat másol ilyen módon, akkor azok a várt módon lesznek átmásolva. De amikor egy szűrt tartományba másolt (vagy rejtett sorokat tartalmazó) valamit próbál beilleszteni, 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ókba illessze be
Ha csak a látható cellákba szeretne adatokat beszúrni, használja a következő makrót:

Opció Explicit Dim rCopyRange As Range "Ezzel a makróval másoljuk az adatokat Sub My_Copy() Ha Selection.Count > 1 then Set rCopyRange = Selection.SpecialCells(xlVisible) Else : Set rCopyRange = ActiveCell End If End Sub "Ezzel a makróval a kiválasztott cellától kezdve szúrunk be adatokat Sub My_Paste() Ha az rCopyRange semmi, akkor lépjen ki az alból, ha rCopyRange.Areas.Count > 1, akkor MsgBox "A beillesztett tartomány nem tartalmazhat egynél több régiót!",vbCritical, "Érvénytelen tartomány": Exit Sub Dim rCell as Range, 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 rcCoolpyan iColpyan .Columns.Count li = 0: lCount = 0: le = iCol - 1 Minden rCell in rCopyRange.Columns(iCol).Cells Ha ActiveCell.Offset(li, le).EntireColumn.Hidden = False és _ ActiveCell.Offset (li, le).EntireRow.Hidden = False then rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Loop While lCount >= rCell.Row - rCopyRange.Cells(1 ).Következő sor rCell Következő iCol Application.ScreenUpdating = True : Application.Calculation = iCalculation End Sub

Opció Explicit Dim rCopyRange As Range "Használja ezt a makrót az adatok másolásához Sub My_Copy() If Selection.Count > 1 then Set rCopyRange = Selection.SpecialCells(xlVisible) Else: Set rCopyRange = ActiveCell End If End Sub "Használja ezt a makrót a beillesztéshez a kijelölt cellákból induló adatok Sub My_Paste() If rCopyRange is Nothing then Exit Sub If rCopyRange.Areas.Count > 1 Then MsgBox "A beillesztett tartomány nem tartalmazhat egynél több területet!", vbCritical, "Érvénytelen tartomány": Kilépés Sub Dim rCell As Range, 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 To.ColumnRange .Count li = 0: lCount = 0: le = iCol - 1 Minden rCell in rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False és _ ActiveCell.Offset(li) , le).EntireRow.Hidden = False then rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Loop While lCount >= rCell.Row - rCopyRange.Cells(1). Következő sor rCell Következő iCol Application.ScreenUpdating = Igaz: Application.Calculation = iCalculation End Sub

A kép teljessé tételéhez jobb, ha ezeket a makrókat gyorsbillentyűkhöz rendeljük (az alábbi kódokban ez automatikusan megtörténik, amikor a kóddal rendelkező könyvet kinyitjuk). Ehhez csak be kell másolnia az alábbi kódokat a modulba Ezt a könyvet (Ez a munkafüzet) :

Opció Explicit "A munkafüzet bezárása előtt törölje a gyorsbillentyűk hozzárendelését Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey "^q": Application.OnKey "^w" End Sub "Gyorsbillentyűk hozzárendelése a 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 má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ó cellá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ány 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 rejtve), és illessze be egy billentyűkóddal Ctrl + w . A beillesztés rejtett/szűrt sorokban és oszlopokban, vagy rejtett sorok nélkül is megtörténik.
Ha a másolt tartomány képleteket tartalmaz, akkor a referenciaeltolódás elkerülése érdekében csak a cellaértékeket másolhatja - pl. Az é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, akkor csak a cellaértékek másolása és beillesztése történik. Ehhez ki kell cserélni a kódot (az alábbi fájlban):

rCell.Copy rResCell.Offset(lr,lc)

rCell.Copy rResCell.Offset(lr,lc)

ehhez:

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

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

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

Letöltési példa:

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

Lásd még:
[]

Segített a cikk? Oszd meg a linket ismerőseiddel! Videó leckék

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmarginright":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,"texteffectslidelay2":150 textcss":"text-align:left;" ","titlecss":"display:blokk; 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))

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ó cellákat másolja, hanem a rejtett vagy szűrt cellákat is. Ha csak a látható cellákat szeretné másolni, kövesse az alábbi lépéseket. Például egy strukturált munkalapról csak összefoglaló adatokat másolhat.

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

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

Amikor egy 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 a látható cellákkal együtt be vannak illesztve. Sajnos ez a beállítás nem módosítható, amikor 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 szeretné táblázatként formázni az adatokat, és telepítve van az asztali Excel, megnyithatja a munkafüzetet az Excelben a látható cellák másolásához és beillesztéséhez. Ehhez kattintson a gombra Nyissa meg 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ó

Mindig feltehet kérdést az Excel Tech Community specialistájának, kérhet segítséget a Válaszok közösségben, és javasolhat is új funkció vagy fejlesztés a weboldalon

Az Excelben az adatok kiválasztása szűrővel vagy rendezéssel történt. Most ki kell nyomtatni, vagy át kell helyezni őket egy másik helyre. Másolás Excelbeúgy van beállítva, hogy a rejtett cellákat is másolja.
Vegyünk két módot, Nak nek Szűrt sorok másolása Excelben.
Első út.
Van egy nagyszerű funkció az Excelben - Speciális funkció beillesztése az Excelbe.
Szóval van egy asztalunk.
A szűrő telepítéséhez lásd a cikket " Szűrés Excelben".
Szűrő segítségével eltávolítjuk az összes Ivanovot a listáról. Így alakult.
Válassza ki a táblázatot, és kattintson a „Másolás” gombra a helyi menüben. Kattintson a bal egérgombbal az A9 cellára, és válassza ki az „értékek” lehetőséget a helyi menüben.
Kattintson az "OK" gombra. Voálá. Nemcsak a látható sorok értékét másoltuk át, hanem a cellák formátumát is.
Van egy árnyalat- a szűrt adatokat ne azokba a sorokba illessze be, ahol a szűrő található. Például a mi példánkban - nem az 1-7 sorban, hanem alatta vagy egy másik lapon stb. Ha beszúrunk azokba a sorokba, ahol a szűrő található, akkor a szűrt adatok a szűrő által rejtett sorokba is bekerülnek. Általában káosz lesz belőle. Második út.
A táblázat ugyanaz. Válassza ki a szűrt adatokat tartalmazó táblázatot. A „Főoldal” lapon kattintson a „Szerkesztés” szakaszra Funkciók keresése és kiemelése az Excelben. Ezután kattintson a „Go” gombra. A megjelenő párbeszédpanelen kattintson a „Kiválasztás...” gombra. A „Cellacsoport kiválasztása” ablakban jelölje be a „Csak látható cellák” melletti négyzetet. Kattintson az "OK" gombra. Most ugyanazon a kiválasztott asztalon a jobb egérrel hívjuk helyi menü. Kattintson a „Másolás” funkcióra. Egy új helyen (példánkban ez az A15-ös cella) kattintson a „Beszúrás” gombra. Minden. Így alakult.
Hogyan, másolás nélkül, azonnal szűrőadatok nyomtatása excelben, lásd a „Könyvjelző Excel lap"Oldal elrendezés""

Csak a látható sorokba 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álhat szűrőt. A szűrő telepítéséről és az Excelben való 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.
Az első út az rendes .
Vegyünk egy ilyen táblázatot. A táblázat minden példában ugyanaz lesz.
Használjunk szűrőt a táblázatból az összes 2-es számjegy eltávolítására. A fennmaradó látható cellákba tesszük a 600-as számot. A B2 cellába tesszük a 600-as számot, majd másoljuk le az oszlopban (húzzuk meg a B2 cella jobb alsó sarkát). Az értékek csak látható cellákba lettek másolva. Ugyanígy beszúrhat képleteket is. A következő képletet írjuk a C2 cellába. =A2*10
Így alakult.
Töröljük a szűrőt. Az eredmény egy ilyen táblázat.
A képlet és a számok csak a szűrt sorokba kerültek.
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 a következőképpen jelölje ki a cellákat: nyomja meg a „Ctrl” + „Shift” billentyűket + a lefelé mutató nyíl gombot (vagy a fel gombot, attól függően, hogy hol akarjuk kijelölni a cellákat - alul vagy felül a cella, amelybe a számot írták) .
Most, vagy nyomja meg a „Ctrl” + G billentyűkombinációt vagy az F5 billentyűt. Megjelenik az Átmenet párbeszédpanel. Kattintson a „Kiválasztás...” gombra. Az új „Cellacsoport kiválasztása” párbeszédpanelen jelölje be a „Csak látható cellák” szavak melletti négyzetet.Kattintson az "OK" gombra. Ezután helyezze be a szokásos módon.

Egy másik módszer a Cellák csoportjának kiválasztása párbeszédpanel megjelenítésére.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 töltse ki a látható cellákat a kijelölt Excel oszlopokban, nyomja meg a „Ctrl” + D billentyűkombinációt. És minden kijelölt oszlop meg lesz töltve adatokkal vagy képlettel, mint az első cellában. Példánkban a 800-as számot írtuk a D2 cellába, a D oszlopba.

Harmadik út.
Egy új oszlopban (a példánkban az E oszlopban) jelölje ki a cellákat. Nyomja meg az F5 billentyűt. Megjelenik az Átmenet párbeszédpanel. Kattintson a „Kiválasztás...” gombra. Az új „Cellacsoport kiválasztása” párbeszédpanelen jelölje be a „Csak látható cellák” szavak melletti négyzetet. Kattintson az "OK" gombra. Most a kijelölés törlése nélkül az oszlop első cellájába (miénk E2) írjon be egy képletet, számot stb. Nyomja meg a „Ctrl” + „Enter” billentyűkombinációt.