Podmienené formátovanie (5)
Zoznamy a rozsahy (5)
Makrá (postupy VBA) (63)
Rôzne (39)
Excel chyby a závady (4)

Ako vložiť kopírované bunky len vo viditeľných / filtrovaných bunkách

Všeobecne platí, že význam článku, ktorý už myslím, je z mena jasný. Len mierne rozbaľte.

Nie je to tajomstvo, že Excel vám umožňuje zvýrazniť iba viditeľné čiary. (Napríklad, ak niektoré z nich sú skryté alebo filtrom).

Takže, ak kopírujete iba viditeľné bunky, sú skopírované, ako by sa mali kopírovať. Ale keď sa pokúsite vložiť kopírovať v rozsahu, filtrovaný (alebo obsahujúci skryté čiary) je výsledkom inzercie nebude presne tak, ako ste očakávali. Údaje budú vložené aj v skrytých riadkoch.

Skopírujte jeden rozsah buniek a vložte len na viditeľný
Aby boli údaje vložené iba vo viditeľných bunkách, takýto makro možno aplikovať:

Možnosť Explicit Dim Rcopyrange ako rozsah "Toto údaje o kopírovaní makier SUB MY_COPY () Ak výber.Count\u003e 1 Potom nastavte rcopyrange \u003d selekcia .Špecialcells (xlvisible) inak: Nastavenie Rcopyrange \u003d ActiveCell END IF END SUB "Tento makro vloží údaje od vybranej bunky Sub my_paste () Ak rcopyrange nie je nič, potom ukončite sub, ak rcopyrange.areas.Count\u003e 1 Potom MsgBox "Vložený rozsah by nemal obsahovať viac ako jednu oblasť!", vbkritické, "Neplatný rozsah": EXPUT SUB DIM RCELL as rozsah, LI tak dlho, le tak dlho, lcount as dlhej, iCol ako celé číslo, icalculation ako celé číslo aplikácie.Screenupdated \u003d false icalculation \u003d Application.Calculation: Application.Calculation \u003d -4135 pre Icol \u003d 1 až Rcopyrange .Columns.count li \u003d 0: lcount \u003d 0: Le \u003d Icol - 1 pre každý RCELL v Rcopyrange.Columns (Icol) .Cells robiť, ak ActiveCell.offset (li, le) .entirecolumn.hidden \u003d false a _ ActiveCell.offset (Li, Le). Centrirerow.hidden \u003d FALSE TAKO RCELL.Copy ActiveCell.offset (li, le): lcount \u003d lcount + 1 koniec, ak li \u003d li + 1 slučka, zatiaľ čo Lcount\u003e \u003d RCELL.ROW - RCOPYRANGE.cells (1 ) .R-NEXT RCELL NEXT ICOL APLIKÁCIA.SCREENUPDATION \u003d TRUE: APLIKÁCIA.CALCELÁLNY \u003d ENTERCULÁCIA END SUB

Možnosť Explicit Dim Rcopyrange ako rozsah "Tento makro kopírovať údaje sub my_copy () ak selekcia.Count\u003e 1 Potom nastavte rcopyrange \u003d selekcia .Specialcells (XLVisible) inak: SET RCOPYRANGE \u003d ACTIVECELL END IF End Sub" Tento makro vložte údaje začínajúce Vybrané bunky SUB MY_PUSTER () Ak Rcopyrange nie je nič, potom ukončite sub, ak rcopyrange.areas.Count\u003e 1 Potom Msgbox "Vložený rozsah nemá obsahovať viac ako jednu oblasť!", VBritical, "Neplatný rozsah": Exit Sub Dim RCELL as , LI tak dlho, LE tak dlho, Lcount as dlhej, Icol ako celé číslo, icalculation ako celočíselné aplikácie.Screenupdation \u003d FALSE ICALCulation \u003d Application.Calculation: Application.Calculation \u003d -4135 pre Icol \u003d 1 až Rcopyrange.columns.count li \u003d 0 : Lcount \u003d 0: Le \u003d Icol - 1 pre každý RCELL v Rcopyrange.columns (Icol) .Cells robiť, ak ActiveCell.offset (Li, Le). intricRecolumn.hidden \u003d False a _ ActiveCell.offset (Li, Le). Centirerow .hidden \u003d false potom rcell.copy ActiveCell.offset (Li, Le): Lcount \u003d Lcount + 1 Koniec Ak Li \u003d Li + 1 slučky, zatiaľ čo LCOUNT\u003e \u003d RCELL.ROWROW - RCOPYRANGE.Cells (1) .R-NEXT RCELL NEXT ICOL APLIKÁCIA.SCREENUPDATION \u003d TRUE: APLIKACIE.CALKÁZKO

Na úplnosť obrázku sú údaje MacROS lepšie priradiť klávesové skratky (v nižšie uvedených kódoch, to sa vykonáva automaticky pri otváraní knihy s kódom). Na tento účel musia byť nižšie uvedené kódy jednoducho kopírovať do modulu. Táto kniha Táto pracovná kniha) :

Možnosť Explicitná "Zrušujeme priradenie klávesových tlačidiel pred zatvorením knihy súkromné \u200b\u200bsub na pracovisku_beForeclose (Zrušiť ako boolean) Application.Onkey" ^ q ": Application.onkey" ^ W "End Sub" Priradenie klávesových tlačidiel pri otváraní knihy súkromné \u200b\u200bsub Workbook_OPEN () Application.Onkey "^ q", "my_copy": Application.onkey "^ W", "my_paste" koncový sub

Teraz môžete skopírovať požadovaný rozsah stlačením tlačidiel Cttrl + q. a vložte ho do filtrovaného - Cttrl + w. .

Príklad stiahnutia

(46,5 kib, 9,622 na stiahnutie)

Skopírujte iba viditeľné bunky a vložte len vo viditeľnom
Na žiadosť návštevníkov na stránke sa rozhodla dokončiť tento postup. Teraz je možné kopírovať všetky rozsahy: s skrytými riadkami, skryté stĺpce a vložte kopírované bunky aj do akýchkoľvek rozsahov: s skrytými radmi, skryté stĺpce. Pracuje úplne ako predchádzajúci: stlačte klávesy Cttrl + q. Skopírujte požadovaný rozsah (s skrytými / filtrovanými čiarami a stĺpmi alebo nie skrytými)a vložte kombináciu kľúčov Cttrl + w. . Vložka sa uskutočňuje aj v skrytých / filtrovaných vedeniach a stĺpcoch alebo bez skrytého.
Ak sú vzorce prítomné v rozsahu kopírovania, potom, aby sa zabránilo odsadeniu odkazu, môžu byť kopírované iba hodnoty buniek - t.j. Pri vkladaní hodnôt nie je vložená vzorca, ale výsledok ich výpočtu. Alebo ak potrebujete uložiť formáty buniek, v ktorých dochádza k inzercii - sa budú kopírovať a vložené hodnoty buniek. Ak to chcete urobiť, vymeňte riadok v kóde (v súbore nižšie):

Rcell.copy rrescell.offset (LR, LC)

rcell.copy rrescell.offset (LR, LC)

na takéto:

Rrescell.offset (LR, LC) \u003d RCELLUE

rrescell.offset (LR, LC) \u003d RCELLUE

V súbore nižšie ako tieto čiary sú prítomné, stačí opustiť ten, ktorý je vhodnejší pre vaše úlohy.

Príklad sťahovania:

(54,5 kIB, 7,928 na stiahnutie)

Pozri:
[]

Článok pomohol? Zdieľajte odkaz s priateľmi! Video lekcie

("BOTTH BAR" :( "TextStyle": "Static", "TextPositionAtic": "Dno", "TextauTohide": TRUE "TEXTAKTAČNÝMARMARGNATICKÝ": 0, "TextPospositiondynamic": "Bottleft", "TextPotositionSmarginleft": 24, " TextPositionArginight ": 24," TextPositionArgintOP ": 24," TextPotositionSmargintom ": 24," TextEffect ":" Slide "," texteffectEffecting ":" EaseoutCubic "," texteffectDuration ": 600," texteffectsLedriretionsing " : 30, "texteffectdelay": 500, "texteffectsesparte": false, "texteffect1": "snímka", "texteffectslidridirection1": "vpravo", "texteffectslidisance1": 120, "texteffectasing1": "EaseoutCubic", "texteffectDuration1": " , "TexteffectDelay1": 1000, "texteffect2": "snímka", "texteffectssLedrirection2": "vpravo", "texteffectsLedistance2": 120, "texteffecting2": "EaseoutCubic", "texteffectDeluration2": 600, "texteffectDelay2": 1500, " TEXTCSS ":" Displej: Blok; Vyplnenie: 12px; Text-ALIGN: vľavo; "," TextBGCSS ":" Displej: Blok; Poloha: Absolútna; TOP: 0px; vľavo: 0px; šírka: 100%; výška: 100% ; pozadia-farba: # 333333; opacita: 0,6; filter: a LPHA (opacity \u003d 60); "," TitLECSS ":" Displej: blok; Pozícia: relatívna; Font: Bold 14px "Lucida Sans Unicode", "Lucida Grande", Sans-Serif, Arian; Farba: #fff; "," PopisCSSS ":" Displej: blok; Pozícia: relatívna; Font: 12px "Lucida Sans Unicode", "Lucida Grande", Sans-Serif, Arian; Farba: #fff; Margin-Top: 8px; "," ButtonCSS ":" Displej: blok; Pozícia: relatívna; Margin-Top: 8px; "," texteffectresponsive ": true," texteffectresponsize ": 640," TitLecsssResponsive ":" Font-veľkosť: 12px; "," PopisCSSSSSSRESSEPONSIVE ":" Displej: Žiadne! Dôležité; "," ButtoncssResponsive ":" "", "addgooogelfonts": false, "GoogleFonts": "", "TEXTLEFTRIGHTIVERFENTFORSTATIC": 40))

Výber údajov v programe Excel strávil pomocou filtra alebo triedenia. Teraz musia byť vytlačené alebo preniesť na iné miesto. Kopírovanie do programu Excel Je nakonfigurovaný tak, aby sa skryli skryté bunky.
Zvážte dva spôsoby na aK Kopírovanie filtrované riadky v programe Excel.
Prvým spôsobom.
V programe Excel je nádherná funkcia - funkcia "Špeciálna vložka" v programe Excel.
Takže máme tabuľku.
Ako nainštalovať filter, pozrite sa do článku "Filtrovať v programe Excel.
Filter odstránime zo zoznamu všetkých IVANOV. Ukázalo sa to tak.
Vyberte tabuľku, kliknite na kontextové menu "Kopírovať". Stlačte ľavú myš v bunke A9, v kontextovej ponuke vyberte "Hodnoty".
Kliknite na tlačidlo "OK". Voila. Nielen hodnota viditeľných línií, ale aj formát buniek.
Je tu jeden nuan - Vložte filtrované údaje nie sú v reťazci, kde je filter. Napríklad v našom príklade - nie v riadku 1-7, ale nižšie alebo na inom liste atď. Ak vložíte do reťazca, kde stojí filter, potom filtrované údaje a čiary ukryté filtrom sú vložené. Všeobecne platí, že vypne kašu. Druhý spôsob.
Tabuľka je rovnaká. Vyberte tabuľku s filtrovanými údajmi. Na karte Domov kliknite na tlačidlo Upraviť nájsť a zvýrazniť funkcie v programe Excel. Potom stlačte tlačidlo "Go". V dialógovom okne, ktoré sa zobrazí, stlačte tlačidlo "Vybrať ...". V okne "Výber skupiny skupiny" sa zaškrtávame na strun "iba viditeľné bunky". Kliknite na tlačidlo "OK". Teraz na rovnakej zvýraznenej tabuľke sa kontextové menu nazýva pravým rukou. Stlačte funkciu "Kopírovať". Na novom mieste (v našom príklade je to bunka A15) Kliknite na "Pasta". Všetko. Ukázalo sa to tak.
Ako, okamžite sa kopíruje tlačové údaje o filtroch v programe Excel, Pozrite sa do článku "Záložka Leaf Excel" Stránka označenie ""

Pavlov Nicholas.

V tomto článku by som vám chcel predstaviť najúčinnejšie techniky práce v programe Microsoft Excel, zozbieral mi za posledných 10 rokov práce na projektoch a školení na tomto nádhernom programe. Neexistuje žiadny opis superstrátových technológií, ale každý deň existujú techniky - jednoduché a efektívne, opísané bez "vody" - len "suchý zvyšok". Nebudete mať najviac jednu alebo dve minúty, aby ste vytvorili väčšinu z týchto príkladov, ale ušetria vám oveľa viac.

Rýchly prechod na požadovaný list

Pracujete s Excel knihy pozostávajúce z veľkého počtu listov? Ak existuje viac ako tucet, potom sa každý prechod na ďalšiu potrebnú skutočnosť stane malým problémom. Jednoduchým a elegantným riešením takejto úlohy je kliknúť v ľavom dolnom rohu okna na posúvaných tlačidlách listov listov, ktoré nie sú ponechané, a pravé tlačidlo myši - obsah sa objaví s úplným zoznamom všetkých Listy a na požadovanom hárku môžu byť prenesené na jeden pohyb:

Je oveľa rýchlejšie ako rolovanie listov listov tým istými tlačidlami pri hľadaní požadovaného.

Kopírovať bez poškodenia formátovania

Koľko stoviek (tisícov?) Akonáhle som videl tento obrázok, stojil za mojich poslucháčov počas školení: užívateľ vstúpi do vzorca v prvej bunke a "tiahne" to na celom stĺpci, čo narušuje formátovanie nasledujúcich línií Metóda kópie nielen vzorca, ale formát buniek. V súlade s tým, potom musíte manuálne správne poškodiť. Po druhé na kopírovanie a potom 30 - na opravu dizajnu poškodeného kopírovaním.

Počnúc programom Excel 2002, je tu riešenie tohto problému - jednoduché a elegantné. Bezprostredne po kopírovaní (ťahanie) vzorcov do celého stĺpca musíte použiť inteligentnú značku - malú ikonu, ktorá sa dočasne zobrazí v pravom dolnom rohu rozsahu. Stlačením tlačidla zobrazí zoznam možných možností kopírovania, kde si môžete vybrať iba hodnoty (vyplniť formátovanie vo formáte). V tomto prípade sú vzorce kopírované a formátovanie - nie:

Kopírovať iba viditeľné bunky

Ak pracujete v programe Microsoft Excel viac ako týždeň, mali ste už byť s podobným problémom: V niektorých prípadoch, keď sú pri kopírovaní vložení buniek, sú vložené viac, než bolo, na prvý pohľad, kopírované. To sa môže vyskytnúť, ak je rozsah kopírovania zahrnutý skryté reťazce / stĺpce, zoskupenia, medziprodukty alebo filtrovanie. Považovať za príklad jedného z takýchto prípadov:

Medziprodukty sa počítajú v tejto tabuľke a zoskupenie reťazcov v mestách je vyrobené - je ľahké pochopiť tlačidlá "plus-mínus" vľavo od stola a na medzerách v číslovaní viditeľných línií. Ak vyberiete, kopírujte a prilepte údaje z tejto tabuľky obvyklým spôsobom, potom dostaneme 24 nepotrebných riadkov. Chceme tiež kopírovať a vložiť len výsledky!

Problém môžete vyriešiť, starostlivo zvýrazniť každý riadok výsledku a udržanie klávesu CTRL - pokiaľ ide o výber negatívnych rozsahov. Ale ak nie sú tri-päť takýchto línií, ale niekoľko stoviek alebo tisíc? Existuje ďalší, rýchlejší a pohodlný spôsob:

Zvýraznite kopírovaný rozsah (v našom príklade je A1: C29)

Stlačte klávesové tlačidlo klávesnice F5 a potom vyberte tlačidlo (špeciálne) v okne, ktoré sa otvorí.
Zobrazí sa okno, čo umožňuje užívateľovi prideliť nie všetko v rade, ale iba potrebné bunky:

V tomto okne vyberte len viditeľné bunky (len viditeľné bunky) a kliknite na tlačidlo OK.

Získaný výber môže byť teraz bezpečne kopírovať a vložiť. V dôsledku toho dostaneme kópiu viditeľných buniek a vložiek namiesto zbytočných 29 iba 5 riadkov, ktoré potrebujete.

Ak existuje podozrenie, že takáto operácia, ktorú musíte urobiť, je zmysel pridať tlačidlo do panela s nástrojmi Microsoft Excel, aby ste mohli rýchlo zavolať takúto funkciu. To možno vykonať prostredníctvom ponuky Nástroje\u003e Nastavenie (Nástroje\u003e Prispôsobiť), potom prejdite na kartu Príkazov (príkazy), v kategórii Upraviť (Upraviť) Nájdite tlačidlo Vybrať viditeľné bunky a preneste ho na panel s nástrojmi:

Transformácia riadkov v stĺpcoch a späť

Jednoduchá operácia, ale ak neviete, ako to urobiť správne - môžete stráviť pol dňa na ťahanie jednotlivých buniek manuálne:

V skutočnosti je všetko jednoduché. V tej časti najvyššej matematiky, ktorá opisuje matricu, je koncepcia transpozície - akcie, ktoré menia reťazce a stĺpce v matrici na niektorých miestach navzájom. V programe Microsoft Excel je implementovaný v troch pohyboch: Skopírujte tabuľku

Kliknite pravým tlačidlom myši na prázdnu bunku a vyberte špeciálny príkaz vkladania (vložiť špeciálne)

V okne, ktoré sa otvorí, nastavte príznak pre transpozíciu (transponovať) a kliknite na tlačidlo OK:

Rýchle pridanie údajov do grafu

Predstavte si jednoduchú situáciu: Máte správu za posledný mesiac s vizuálnym diagramom. Úloha - Ak chcete tento mesiac pridať nové číselné údaje. Klasická cesta je otvorenie okna zdroja údajov pre diagram, kde pridáte nové dátové série zadaním svojho mena a zvýraznite rozsah s požadovanými údajmi. Okrem toho je často ľahšie povedať, ako to urobiť - to všetko závisí od zložitosti grafu.

Ďalším spôsobom je jednoduchý, rýchly a krásny - vybrať bunky s novými dátami, skopírujte ich (CTRL + C) a PASTE (CTRL + V) priamo v grafe. Excel 2003, na rozdiel od neskorších verzií, dokonca aj možnosť pretiahnutia vyhradenej škály buniek s údajmi a hodiť ho priamo do grafu s myšou!

Ak chcete ovládať všetky nuansy a jemnosti, môžete použiť obyčajnú, a špeciálnu vložku výberom položky Upraviť\u003e Vložiť špeciálnu (Edit\u003e Paste Special). V tomto prípade program Microsoft Excel zobrazí dialógové okno, ktoré umožňuje konfigurovať, kde a ako budú nové údaje pridané:

Podobne môžete ľahko vytvoriť diagram pomocou údajov z rôznych tabuliek z rôznych listov. Ak chcete vykonať tú istú úlohu, bude trvať oveľa viac času a sily.

Plnenie prázdnych buniek

Po vyložení správ z niektorých programov do formátu programu Excel alebo pri vytváraní konsolidovaných tabuliek používatelia často dostávajú tabuľky s prázdnymi bunkami v niektorých stĺpcoch. Tieto preskaky nám neumožňujú aplikovať nás na tabuliek známe a pohodlné nástroje, ako je autofilter a triedenie. Samozrejme, že je potrebné naplniť hodnoty Void z vyšších buniek:

Samozrejme, s malým množstvom dát, môže byť ľahko vyrobený jednoduchým kopírovaním - manuálne natiahnutie každej titulnej bunky v stĺpci a dole na prázdne bunky. A ak je niekoľko stoviek alebo tisícov riadkov a niekoľko desiatok miest v tabuľke?

Tam je spôsob, ako rýchlo a krásne vyriešiť túto úlohu pomocou jedného vzorca:

Zvýraznite všetky bunky v stĺpci s dutinami (t.j. rozsah A1: A12 v našom prípade)

Ak chcete vybrať iba prázdne bunky, stlačte kláves F5 a v okne prechodu, ktorý sa otvorí, tlačidlo ALOCECT. Pozrite si okno, ktoré vám umožní vybrať si - ktoré bunky chceme prideliť:

Nastavte prepínač do prázdnej polohy (prázdne) a kliknite na tlačidlo OK. Teraz by mali zostať len prázdne bunky, ktoré by mali zostať v výbere:

Bez zmeny alokácií, t.j. Nie je to dotyková myš, predstavujeme vzorec v prvej vyhradenej bunke (A2). Kliknite na klávesnicu k "Rovnakému" znameniu a potom v šípke nahor. Dostaneme vzorec, ktorý odkazuje na predchádzajúcu bunku:

Ak chcete zadať vytvorený vzorec ihneď do všetkých vybraných prázdnych buniek, stlačte kláves ENTER, ale kombinácia CTRL + ENTER. Vzorec vyplní všetky prázdne bunky:

Teraz zostáva len nahradiť vzorce na hodnotách na stanovenie výsledkov. Zvýraznite rozsah A1: A12, skopírujte ho a vložte ich do buniek pomocou špeciálnej vložky.

Vypúšťanie

Recepcia, ktorá, bez preháňania, by mala vedieť, kto pracuje v programe Excel. Jeho použitie je schopné zlepšiť, takmer akúkoľvek tabuľku bez ohľadu na jej účel. Vo všetkých tréningoch sa snažím na prvý deň ukázať svojim poslucháčom.

Myšlienka je veľmi jednoduchá - vo všetkých prípadoch, keď musíte zadať údaje z ľubovoľného ovládača, namiesto manuálneho vstupu do bunky z klávesnice, vyberte požadovanú myš v rozbaľovacom zozname:

Výber tovaru z cenníka, meno klienta z klientskej základne, Fio zamestnanca z personálu a tak ďalej. Možnosti aplikácie pre túto funkciu.

Vytvorenie rozbaľovacieho zoznamu v bunke:

Vyberte bunky, v ktorých chcete vytvoriť rozbaľovací zoznam.

Ak máte program Excel 2003 alebo starší, potom vyberte položku Údaje\u003e Validácia v ponuke údajov\u003e Validácia. Ak máte Excel 2007/2010, potom prejdite na kartu Údaje a kliknite na položku Validácia údajov.

V okne, ktoré sa otvorí, vyberte možnosť Zoznam (zoznam) z rozbaľovacieho zoznamu.

V zdrojovom poli (zdroj) musíte zadať hodnoty, ktoré by mali byť uvedené. Tu sú možné možnosti:

Zadajte do tejto oblasti Textové možnosti prostredníctvom čiarky

Ak je rozsah buniek so zdrojovými hodnotami na aktuálnom hárku - stačí ho jednoducho zvýrazniť myšou.

Ak je na inom hárku tejto knihy, bude musieť dať vopred (na zvýraznenie buniek, stlačte kláves CTRL + F3, zadajte názov rozsahu bez medzier) a potom registráciu tohto mena v poli

Ak sa niektoré bunky, reťazce alebo stĺpce na hárku nezobrazia, môžete skopírovať všetky bunky (alebo iba viditeľné bunky). V predvolenom nastavení Excel Copies nielen viditeľné, ale aj skryté alebo filtrované bunky. Ak chcete kopírovať iba viditeľné bunky, postupujte podľa nižšie uvedených krokov. Môžete napríklad kopírovať iba súhrnné údaje zo štruktúrovaného listu.

Postupujte podľa nižšie uvedených krokov.

Poznámka: Pri kopírovaní sú hodnoty konzistentne vložené do strunov a stĺpcov. Ak vkladacia oblasť obsahuje skryté reťazce alebo stĺpce, možno budete musieť zobraziť, aby ste videli všetky kopírované údaje.

Pri kopírovaní a vkladaní viditeľných buniek v dátovom rozsahu, ktorý obsahuje skryté bunky alebo na ktoré sa aplikuje filter, je možné poznamenať, že skryté bunky sú vložené spolu s viditeľnými. Tento parameter bohužiaľ nemôžete zmeniť, keď skopírujete a vložíte rozsah buniek v programe Excel na internete, pretože vloženie iba viditeľných buniek nie je k dispozícii.

Ak však formátujete údaje ako tabuľku a aplikujte filter, môžete kopírovať a prilepiť iba viditeľné bunky.

Ak nemusíte formátovať údaje ako tabuľku a je nainštalovaná klasická aplikácia Excel, môžete do nej otvoriť knihu na kopírovanie a vkladanie viditeľných buniek. Kliknite na tlačidlo Otvorené v programe Excel A postupujte podľa krokov opísaných v článku kopírovania a vložte iba viditeľné bunky.

Ďalšie informácie

Môžete vždy položiť otázku programu Excel Tech Community, požiadajte o pomoc v odpovediach komunity Spoločenstva, rovnako ako ponúkajú novú funkciu alebo zlepšenie na webovej stránke.