Zalijepite samo u vidljive linijeExcel brojeva, formula, teksta može se napraviti na više načina. Kada trebate umetnuti brojeve, formule, tekst u ne sve retke tablice, možete koristiti filtar. Kako instalirati filter i kako filtrirati u Excelu, pogledajte članak “Filter u Excelu”. Ali za umetanje podataka samo u vidljive ćelije, potrebne su vam vlastite metode, pogotovo ako ima mnogo redaka.
Prvi način je obični .
Uzmimo ovakav stol. Tablica će biti ista za sve primjere.
Upotrijebimo filtar za uklanjanje svih znamenki 2 iz tablice. U preostale vidljive ćelije stavimo broj 600. U ćeliju B2 stavimo broj 600, pa ga kopiramo niz stupac (povučemo donji desni kut ćelije B2). Vrijednosti su kopirane samo u vidljive ćelije. Na isti način možete umetnuti i formule. Sljedeću formulu zapisujemo u ćeliju C2. =A2*10
Ispalo je ovako.
Otkažimo filtar. Rezultat je ovakva tablica.
Formula i brojevi umetnuti su samo u filtrirane retke.
Drugi način.
Također ćemo filtrirati podatke. U prvu ćeliju upisujemo broj, formulu, tekst itd. Sada, ako postoje tisuće redaka, tada odaberite ćelije ovako: pritisnite tipke “Ctrl” + “Shift” + gumb sa strelicom prema dolje (ili gumb prema gore, ovisno o tome gdje želimo odabrati ćelije - ispod ili iznad ćelija u koju je upisan broj) .
Sada, ili pritisnite kombinaciju tipki “Ctrl” + G ili tipku F5. Pojavit će se dijaloški okvir Prijelaz. Pritisnite gumb "Odaberi...". A u novom dijaloškom okviru "Odaberi grupu ćelija" potvrdite okvir pored riječi "Samo vidljive ćelije".Pritisnite "OK". Zatim umetnite kao i obično.

Drugi način za otvaranje dijaloškog okvira Odabir grupe ćelija.Na kartici "Početna", u odjeljku "Uređivanje", kliknite na gumb "Pronađi i odaberi". Na popisu koji se pojavi kliknite na funkciju "Odaberi grupu ćelija".

Do ispuniti vidljive ćelije u odabranim Excel stupci , pritisnite kombinaciju tipki “Ctrl” + D. I svi odabrani stupci bit će ispunjeni podacima ili formulom, kao u prvoj ćeliji. U našem primjeru, upisali smo broj 800 u ćeliju D2, stupac D.

Kako zalijepiti kopirane ćelije samo u vidljive/filtrirane ćelije

Općenito, mislim da je značenje članka već jasno iz naslova. Samo ću malo proširiti.

Nije tajna da Excel omogućuje odabir samo vidljivih redaka (na primjer, ako su neki od njih skriveni ili je primijenjen filtar).

Dakle, ako na ovaj način kopirate samo vidljive ćelije, one će se kopirati prema očekivanjima. Ali kada pokušate zalijepiti nešto kopirano u filtrirani raspon (ili ono što sadrži skrivene retke), rezultat lijepljenja neće biti točno onakav kakav ste očekivali. Podaci će biti umetnuti čak iu skrivene retke.

Kopirajte jedan raspon ćelija i zalijepite samo u vidljive
Za umetanje podataka samo u vidljive ćelije, možete koristiti sljedeću makronaredbu:

Opcija Explicit Dim rCopyRange As Range "Koristite ovu makronaredbu za kopiranje podataka Sub My_Copy() If Selection.Count > 1 Then Set rCopyRange = Selection.SpecialCells(xlVisible) Else: Set rCopyRange = ActiveCell End If End Sub "Koristite ovu makronaredbu za lijepljenje podaci počevši od odabranih ćelija Sub My_Paste() If rCopyRange Is Nothing Then Exit Sub If rCopyRange.Areas.Count > 1 Then MsgBox "Zalijepljeni raspon ne smije sadržavati više od jednog područja!", vbCritical, "Nevažeći raspon": Izlaz 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 rCopyRange.Columns .Count li = 0: lCount = 0: le = iCol - 1 za svaku rCell u rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False And _ ActiveCell.Offset(li , le).EntireRow.Hidden = False Zatim rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Loop While lCount >= rCell.Row - rCopyRange.Cells(1). Red Sljedeći rCell Sljedeći iCol Application.ScreenUpdating = True: Application.Calculation = iCalculation End Sub

Da biste dovršili sliku, bolje je dodijeliti ove makronaredbe vrućim tipkama (u kodovima ispod to se radi automatski kada se knjiga otvori kodom). Da biste to učinili, trebate samo kopirati donje kodove u modul Ova knjiga (Ova radna knjiga) :

Opcija Eksplicitno "Otkaži dodjelu prečaca prije zatvaranja radne knjige Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey "^q": Application.OnKey "^w" End Sub "Dodijeli prečace pri otvaranju radne knjige Private Sub Workbook_Open() Aplikacija .OnKey "^q", "My_Copy": Application.OnKey "^w", "My_Paste" End Sub

Sada možete kopirati željeni raspon pritiskom na tipke Ctrl + q , i ubacite ga u filtrirani - Ctrl + w .

Primjer preuzimanja

(46,5 KiB, 9622 preuzimanja)

Kopirajte samo vidljive ćelije i zalijepite samo u vidljive
Na zahtjev posjetitelja stranice odlučio sam doraditi ovaj postupak. Sada je moguće kopirati bilo koje raspone: sa skrivenim redovima, skrivenim stupcima, i zalijepiti kopirane ćelije također u bilo koje raspone: sa skrivenim redovima, skrivenim stupcima. Radi potpuno isto kao i prethodni: pritiskom na tipke Ctrl + q kopirajte željeni raspon (sa skrivenim/filtriranim redovima i stupcima ili nije skriven) i zalijepite tipkovničkim prečacem Ctrl + w . Umetanje se također izvodi u skrivene/filtrirane retke i stupce ili bez skrivenih.
Ako kopirani raspon sadrži formule, tada da biste izbjegli pomicanje reference, možete kopirati samo vrijednosti ćelija - tj. Prilikom umetanja vrijednosti neće se umetnuti formule, već rezultat njihovog izračuna. Ili ako je potrebno sačuvati formate ćelija u koje dolazi do umetanja, samo će se vrijednosti ćelija kopirati i zalijepiti. Da biste to učinili, trebate zamijeniti redak u kodu (u datoteci ispod):

rCell.Kopiraj rResCell.Offset(lr, lc)

na ovo:

rResCell.Offset(lr, lc) = rCell.Value

Oba ova retka nalaze se u donjoj datoteci; samo trebate ostaviti onaj koji je prikladniji za vaše zadatke.

Primjer preuzimanja:

(54,5 KiB, 7928 preuzimanja)

Također pogledajte:
[]

("Donja traka":("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":"lijevo","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"desno","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"desno","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:apsolute; top:0px; left:0px; width:100%; height:100% ; boja pozadine:#333333; neprozirnost:0,6; filter:alpha(opacity=60);","titlecss":"display:block; položaj: relativno; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; boja:#fff;","descriptioncss":"display:block; položaj: relativno; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; boja:#fff; margin-top:8px;","buttoncss":"display:block; položaj: relativno; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Pavlov Nikolaj

U ovom članku želim vam predstaviti najviše učinkovite tehnike rad u Microsoft Excel, koje sam prikupio tijekom proteklih 10 godina rada na projektima i vođenja treninga na ovom prekrasnom programu. Ovdje nema opisa super složenih tehnologija, ali postoje tehnike za svaki dan - jednostavne i učinkovite, opisane bez "vode" - samo "suhi ostatak". Za svladavanje većine ovih primjera neće vam trebati više od jedne ili dvije minute, ali će vam pomoći da uštedite mnogo više.

Brzo skočite na željeni list

Slučajno radite sa Excel radne knjige koji se sastoji od velikog broja listova? Ako ih ima više od desetak, onda svaki prijelaz na sljedeći traženi list postaje mali problem za sebe. Jednostavno i elegantno rješenje ovog problema je kliknuti u donjem lijevom kutu prozora na gumbe za pomicanje kartica listova ne lijevom, već desnom tipkom miša - pojavit će se sadržaj knjige s puni popis sve listove i jednim pokretom možete prijeći na željeni list:

Ovo je puno brže od listanja karticama listova pomoću istih gumba u potrazi za onim što vam je potrebno.

Kopirajte bez oštećenja formatiranja

Koliko stotina (tisuća?) puta sam vidio ovu sliku, stojeći iza svojih učenika tijekom treninga: korisnik unosi formulu u prvu ćeliju, a zatim je "razvlači" preko cijelog stupca, narušavajući formatiranje redaka ispod, jer ova metoda kopira ne samo formulu, već i format ćelije. Sukladno tome, tada morate ručno ispraviti štetu. Sekunda za kopiranje i zatim 30 za popravak dizajna oštećenog kopiranjem.

Počevši od Excela 2002, postoji rješenje za ovaj problem koje je jednostavno i elegantno. Odmah nakon kopiranja (povlačenja) formule na cijeli stupac potrebno je koristiti pametnu oznaku - malu ikonu koja se privremeno pojavljuje u donjem desnom kutu raspona. Klikom na njega prikazat će se popis mogućih opcija kopiranja, gdje možete odabrati Ispuni bez formatiranja. U ovom slučaju, formule se kopiraju, ali oblikovanje nije:

Kopiranje samo vidljivih ćelija

Ako ste radili u Microsoft Excelu više od tjedan dana, sigurno ste se već susreli sa sličnim problemom: u nekim slučajevima, prilikom kopiranja i lijepljenja ćelija, umetne se više ćelija nego što je, na prvi pogled, kopirano. To se može dogoditi ako kopirani raspon uključuje skrivene retke/stupce, grupiranja, međuzbrojeve ili filtriranje. Uzmimo jedan od ovih slučajeva kao primjer:

U ovoj tablici izračunavaju se međuzbrojevi, a retci su grupirani po gradu - to je lako razumjeti pomoću gumba plus-minus lijevo od tablice i prekidima u numeriranju vidljivih redaka. Ako odaberemo, kopiramo i zalijepimo podatke iz ove tablice na uobičajeni način, dobit ćemo 24 dodatna retka. Želimo samo kopirati i zalijepiti rezultate!

Problem možete riješiti mukotrpnim odabirom svakog retka ukupnih vrijednosti dok držite pritisnutu tipku CTRL - kao što biste to učinili za odabir nesusjednih raspona. Ali što ako nema tri ili pet takvih linija, već nekoliko stotina ili tisuća? Postoji još jedan, brži i praktičniji način:

Odaberite raspon za kopiranje (u našem primjeru to je A1:C29)

Pritisnite tipku F5 na tipkovnici, a zatim odaberite gumb u prozoru koji se otvori.
Pojavit će se prozor u kojem korisnik može odabrati ne sve u nizu, već samo potrebne ćelije:

U ovom prozoru odaberite opciju Samo vidljive ćelije i kliknite U redu.

Rezultirajući odabir sada se može sigurno kopirati i zalijepiti. Kao rezultat toga, dobit ćemo kopiju točno vidljive stanice i umetnuti umjesto nepotrebnih 29 samo 5 redaka koji su nam potrebni.

Ako sumnjate da ćete morati često izvoditi takvu operaciju, onda ima smisla dodati Microsoft alati Excel gumb za brzo pozivanje takve funkcije. To se može učiniti kroz izbornik Alati> Prilagodi, zatim idite na karticu Naredbe, u kategoriji Uredi pronađite gumb Odaberi vidljive ćelije i povucite ga mišem na alatnu traku:

Pretvaranje redaka u stupce i natrag

Jednostavna operacija, ali ako ne znate kako to učiniti ispravno, možete provesti pola dana povlačeći pojedinačne ćelije ručno:

Zapravo je jednostavno. U onom dijelu više matematike koji opisuje matrice, postoji koncept transpozicije - radnje koja međusobno zamjenjuje retke i stupce u matrici. U Microsoft Excelu to se provodi u tri koraka: Kopirajte tablicu

Desnom tipkom miša kliknite praznu ćeliju i odaberite Posebno lijepljenje.

U prozoru koji se otvori označite oznaku Transpose i kliknite OK:

Brzo dodajte podatke u grafikon

Zamislimo se jednostavna situacija: Imate izvješće za prošli mjesec s vizualnim grafikonom. Zadatak je dodati nove brojčane podatke u grafikon za ovaj mjesec. Klasičan način da to riješite je otvaranje prozora izvora podataka za grafikon, gdje dodajete novu seriju podataka unosom njenog naziva i označavanjem raspona sa željenim podacima. Štoviše, to je često lakše reći nego učiniti - sve ovisi o složenosti dijagrama.

Drugi način - jednostavan, brz i lijep - je odabrati ćelije s novim podacima, kopirati ih (CTRL+C) i zalijepiti (CTRL+V) izravno u grafikon. Excel 2003, za razliku od kasnijih verzija, čak podržava mogućnost povlačenja odabranog raspona podatkovnih ćelija i ispuštanja izravno u grafikon pomoću miša!

Ako želite kontrolirati sve nijanse i suptilnosti, onda možete koristiti ne uobičajeni, već poseban umetak odabirom Edit> Paste Special iz izbornika. U tom će slučaju Microsoft Excel prikazati dijaloški okvir koji vam omogućuje da konfigurirate gdje i kako će se točno dodati novi podaci:

Slično tome, možete jednostavno izraditi grafikon koristeći podatke iz različitih tablica s različitih listova. Obavljanje istog zadatka na klasičan način zahtijevat će puno više vremena i truda.

Popunjavanje praznih ćelija

Nakon preuzimanja izvješća iz nekih programa na Excel format ili prilikom stvaranja stožerne tablice Korisnici često završe s tablicama s praznim ćelijama u nekim stupcima. Ovi propusti nam ne dopuštaju da primijenimo uobičajene i prikladni alati kao što su autofilter i sortiranje. Naravno, postoji potreba za popunjavanjem praznina vrijednostima iz ćelija više razine:

Naravno, s malom količinom podataka to se lako može učiniti jednostavno kopiranje- ručnim povlačenjem svake ćelije zaglavlja u stupcu A prema dolje na prazne ćelije. Što ako tablica ima nekoliko stotina ili tisuća redaka i nekoliko desetaka gradova?

Postoji način da se ovaj problem riješi brzo i lijepo pomoću jedne formule:

Odaberite sve ćelije u stupcu s praznim razmacima (tj. raspon A1:A12 u našem slučaju)

Da biste u odabiru zadržali samo prazne ćelije, pritisnite tipku F5 i u navigacijskom prozoru koji se otvori pritisnite gumb Odaberi. Vidjet ćete prozor koji vam omogućuje odabir ćelija koje želimo odabrati:

Postavite prekidač na Prazno i ​​kliknite OK. Sada bi u odabiru trebale ostati samo prazne ćelije:

Bez mijenjanja odabira, tj. Bez dodirivanja miša unesite formulu u prvu odabranu ćeliju (A2). Pritisnite znak jednakosti na tipkovnici, a zatim strelicu prema gore. Dobivamo formulu koja se odnosi na prethodnu ćeliju:

Da biste stvorenu formulu unijeli u sve odabrane prazne ćelije odjednom, ne pritisnite tipku ENTER, već kombinaciju CTRL + ENTER. Formula će ispuniti sve prazne ćelije:

Sada ostaje samo zamijeniti formule vrijednostima za bilježenje rezultata. Odaberite raspon A1:A12, kopirajte ga i zalijepite njihove vrijednosti u ćelije pomoću Paste Special.

Padajući popis u ćeliji

Tehnika koju bi, bez pretjerivanja, trebao znati svatko tko radi u Excelu. Njegovom se upotrebom može poboljšati gotovo svaki stol, bez obzira na njegovu namjenu. Na svim treninzima nastojim to već prvi dan pokazati svojim polaznicima.

Ideja je vrlo jednostavna - u svim slučajevima kada morate unijeti podatke iz nekog skupa, umjesto ručni unos u ćeliju s tipkovnice odaberite željenu vrijednost mišem s padajućeg popisa:

Odabir proizvoda iz cjenika, imena klijenta iz baze klijenata, punog imena i prezimena zaposlenika iz kadrovske tablice itd. Postoji mnogo opcija za korištenje ove funkcije.

Za izradu padajućeg popisa u ćeliji:

Odaberite ćelije u kojima želite stvoriti padajući popis.

Ako imate Excel 2003 ili stariji, odaberite Data>Validation iz izbornika. Ako imate Excel 2007/2010, idite na karticu Podaci i kliknite gumb Provjera valjanosti podataka.

U prozoru koji se otvori s padajućeg popisa odaberite opciju Popis.

U polju Izvor morate navesti vrijednosti koje bi trebale biti na popisu. Evo mogućih opcija:

Unesite opcije teksta u ovo polje odvojene točkom i zarezom

Ako je raspon ćelija s izvornim vrijednostima na trenutnom listu, samo ga trebate odabrati mišem.

Ako se nalazi na drugom listu ove radne knjige, morat ćete mu unaprijed dati naziv (odaberite ćelije, pritisnite CTRL+F3, unesite naziv raspona bez razmaka), a zatim upišite taj naziv u polje

Ako neke ćelije, retci ili stupci na radnom listu nisu vidljivi, možete kopirati sve ćelije (ili samo vidljive ćelije). Prema zadanim postavkama, Excel kopira ne samo vidljive ćelije, već i skrivene ili filtrirane ćelije. Ako želite samo kopirati vidljive ćelije, slijedite korake u nastavku. Na primjer, možete kopirati samo sažetak podataka sa strukturiranog radnog lista.

Slijedite korake u nastavku.

Bilješka: Kada kopirate, vrijednosti se umeću redom u retke i stupce. Ako područje lijepljenja sadrži skrivene retke ili stupce, možda ćete ih morati otkriti kako biste vidjeli sve podatke koje ste kopirali.

Kada kopirate i zalijepite vidljive ćelije u raspon podataka koji sadrži skrivene ćelije ili ima primijenjen filtar, možete primijetiti da su skrivene ćelije zalijepljene zajedno s vidljivim ćelijama. Nažalost, ne možete promijeniti ovu postavku kada kopirate i zalijepite niz ćelija u programu Excel za web jer lijepljenje samo vidljivih ćelija nije dostupno.

Međutim, ako oblikujete podatke kao tablicu i primijenite filtar, možete kopirati i zalijepiti samo vidljive ćelije.

Ako ne trebate formatirati podatke kao tablicu i imati instaliran klasični Excel aplikacija, možete otvoriti radnu knjigu u njoj da kopirate i zalijepite vidljive ćelije. Da biste to učinili, kliknite gumb Otvori u Excelu i slijedite korake u Kopiranje i lijepljenje samo vidljivih ćelija.

dodatne informacije

Uvijek možete postaviti pitanje stručnjaku Excel Tech Community, zatražiti pomoć u Answers zajednici i predložiti nova značajka ili poboljšanja na web stranici