Hvordan sette inn filtrerte celler i. Lim inn i synlige rader i Excel. Konvertering av rader til kolonner og tilbake igjen
Pavlov Nikolay
I denne artikkelen vil jeg presentere de mest effektive teknikkene for å jobbe med Microsoft Excel, samlet av meg i løpet av de siste 10 årene med å jobbe med prosjekter og gjennomføre opplæring i dette fantastiske programmet. Det er ingen beskrivelse av super komplekse teknologier her, men det er teknikker for hver dag - enkel og effektiv, beskrevet uten "vann" - bare "tørre rester". De fleste av disse eksemplene vil ikke ta deg mer enn ett eller to minutter å mestre, men de vil hjelpe deg med å spare mye mer.
Hopp raskt til ønsket ark
jobber du tilfeldigvis med Excel-arbeidsbøker bestående av et stort antall ark? Hvis det er mer enn et dusin av dem, blir hver overgang til neste nødvendige ark et lite problem i seg selv. En enkel og elegant løsning på dette problemet er å klikke i nedre venstre hjørne av vinduet på knappene for å bla i arkfanene ikke med venstre, men med høyre museknapp - en innholdsfortegnelse for boken vises med full liste alle ark og du kan gå til ønsket ark i en bevegelse:
Dette er mye raskere enn å bla gjennom arkfaner ved å bruke de samme knappene på jakt etter det du trenger.
Kopier uten å skade formateringen
Hvor mange hundre (tusenvis?) ganger har jeg sett dette bildet, stående bak elevene mine under trening: brukeren skriver inn en formel i den første cellen og "strekker" den deretter over hele kolonnen, noe som bryter med formateringen av radene nedenfor, siden denne metoden kopierer ikke bare formelen, men også celleformatet. Følgelig må du deretter korrigere skaden manuelt. Et sekund for å kopiere og deretter 30 for å reparere et design som er skadet ved kopiering.
Fra og med Excel 2002 er det en løsning på dette problemet som er enkel og elegant. Umiddelbart etter å ha kopiert (draget) formelen til hele kolonnen, må du bruke en smart-tag - et lite ikon som midlertidig vises i nedre høyre hjørne av området. Ved å klikke på den får du opp en liste mulige alternativer kopiering, hvor du kan velge Fyll uten formatering. I dette tilfellet kopieres formlene, men formateringen er ikke:
Kopierer bare synlige celler
Hvis du har jobbet i Microsoft Excel i mer enn en uke, må du allerede ha støtt på et lignende problem: i noen tilfeller, når du kopierer og limer inn celler, settes det inn flere celler enn det som ble kopiert ved første øyekast. Dette kan skje hvis det kopierte området inkluderte skjulte rader/kolonner, grupperinger, delsummer eller filtrering. La oss ta et av disse tilfellene som eksempel:
I denne tabellen beregnes delsummer og rader grupperes etter by - dette er lett å forstå ved pluss-minus-knappene til venstre for tabellen og av bruddene i nummereringen av synlige rader. Hvis vi velger, kopierer og limer inn data fra denne tabellen på vanlig måte, vil vi ende opp med 24 ekstra rader. Vi ønsker kun å kopiere og lime inn resultatene!
Du kan løse problemet ved å møysommelig velge hver rad av totalene mens du holder nede CTRL-tasten - som du ville gjort for å velge ikke-tilstøtende områder. Men hva om det ikke er tre eller fem slike linjer, men flere hundre eller tusen? Det er en annen, raskere og mer praktisk måte:
Velg området du vil kopiere (i vårt eksempel er det A1:C29)
Trykk på F5-tasten på tastaturet og deretter på Velg-knappen i vinduet som åpnes.
Et vindu vises som lar brukeren velge ikke alt på rad, men bare de nødvendige cellene:
I dette vinduet velger du alternativet Kun synlige celler og klikker OK.
Det resulterende utvalget kan nå trygt kopieres og limes inn. Som et resultat vil vi få en kopi av nøyaktig synlige celler og sett inn i stedet for de unødvendige 29 bare de 5 linjene vi trenger.
Hvis du mistenker at du må utføre en slik operasjon ofte, er det fornuftig å legge til en knapp på Microsoft Excel-verktøylinjen for raskt å kalle en slik funksjon. Dette kan gjøres gjennom Verktøy> Tilpass-menyen, gå deretter til fanen Kommandoer, i kategorien Rediger, finn knappen Velg synlige celler og dra den til verktøylinjen med musen:
Konvertering av rader til kolonner og tilbake igjen
En enkel operasjon, men hvis du ikke vet hvordan du gjør det riktig, kan du bruke en halv dag på å dra individuelle celler manuelt:
Det er faktisk enkelt. I den delen av høyere matematikk som beskriver matriser, er det konseptet transposisjon – en handling som bytter rader og kolonner i en matrise med hverandre. I Microsoft Excel implementeres dette i tre trinn: Kopier tabellen
Høyreklikk på en tom celle og velg Lim inn spesial.
I vinduet som åpnes, sjekk Transpose-flagget og klikk OK:
Legg raskt til data i et diagram
La oss forestille oss en enkel situasjon: du har en rapport for forrige måned med et visuelt diagram. Oppgaven er å legge til nye numeriske data til diagrammet for denne måneden. Den klassiske måten å løse dette på er å åpne datakildevinduet for diagrammet, hvor du legger til en ny dataserie ved å skrive inn navnet og markere området med ønskede data. Dessuten er dette ofte lettere sagt enn gjort - alt avhenger av kompleksiteten til diagrammet.
En annen måte – enkel, rask og vakker – er å velge cellene med nye data, kopiere dem (CTRL+C) og lime inn (CTRL+V) direkte inn i diagrammet. Excel 2003, i motsetning til senere versjoner, støtter til og med muligheten til å dra et utvalgt utvalg av dataceller og slippe det direkte inn i diagrammet med musen!
Hvis du vil kontrollere alle nyansene og finessene, kan du ikke bruke en vanlig, men en spesiell lim ved å velge Rediger> Lim inn spesial fra menyen. I dette tilfellet vil Microsoft Excel vise en dialogboks som lar deg konfigurere hvor og nøyaktig hvordan de nye dataene skal legges til:
På samme måte kan du enkelt lage et diagram ved å bruke data fra forskjellige tabeller fra forskjellige ark. Å utføre den samme oppgaven på den klassiske måten vil ta mye mer tid og krefter.
Fylle tomme celler
Etter å ha lastet ned rapporter fra noen programmer til Excel-format eller når du oppretter pivottabeller, ender brukere ofte opp med tabeller med tomme celler i enkelte kolonner. Disse utelatelsene lar deg ikke bruke kjente og praktiske verktøy som autofilter og sortering på tabeller. Naturligvis er det behov for å fylle tomrommene med verdier fra celler på høyere nivå:
Selvfølgelig, med en liten mengde data, kan dette enkelt gjøres ved enkel kopiering - manuelt dra hver overskriftscelle i kolonne A ned på de tomme cellene. Hva om tabellen har flere hundre eller tusen rader og flere dusin byer?
Det er en måte å løse dette problemet raskt og vakkert ved å bruke én formel:
Velg alle cellene i en kolonne med tomme mellomrom (dvs. området A1:A12 i vårt tilfelle)
For å beholde bare tomme celler i utvalget, trykk på F5-tasten og i navigasjonsvinduet som åpnes, trykk på Velg-knappen. Du vil se et vindu som lar deg velge hvilke celler vi vil velge:
Sett bryteren til Blank og klikk OK. Nå skal bare tomme celler være igjen i utvalget:
Uten å endre utvalget, dvs. Uten å berøre musen, skriv inn formelen i den først valgte cellen (A2). Trykk på likhetstegnet på tastaturet og deretter på pil opp. Vi får en formel som refererer til forrige celle:
For å legge inn den opprettede formelen i alle valgte tomme celler samtidig, trykk ikke ENTER-tasten, men kombinasjonen CTRL + ENTER. Formelen vil fylle alle tomme celler:
Nå gjenstår det bare å erstatte formlene med verdier for å registrere resultatene. Velg området A1:A12, kopier det og lim inn verdiene deres i cellene ved å bruke Paste Special.
Rullegardinliste i en celle
En teknikk som, uten å overdrive, bør kjenne til alle som jobber i Excel. Bruken kan forbedre nesten alle bord, uavhengig av formålet. På alle treninger prøver jeg å vise det til elevene mine den første dagen.
Ideen er veldig enkel - i alle tilfeller når du må legge inn data fra ethvert sett, i stedet for å legge inn en celle manuelt fra tastaturet, velg ønsket verdi med musen fra rullegardinlisten:
Velge et produkt fra prislisten, kundens navn fra kundedatabasen, det fulle navnet på den ansatte fra bemanningstabellen, etc. Det er mange alternativer for å bruke denne funksjonen.
Slik oppretter du en rullegardinliste i en celle:
Velg cellene der du vil lage en rullegardinliste.
Hvis du har Excel 2003 eller eldre, velg Data > Validering fra menyen. Hvis du har Excel 2007/2010, går du til fanen Data og klikker på knappen Datavalidering.
I vinduet som åpnes velger du Liste-alternativet fra rullegardinlisten.
I kildefeltet må du spesifisere verdiene som skal være i listen. Her er de mulige alternativene:
Skriv inn tekstalternativer i dette feltet atskilt med semikolon
Hvis celleområdet med de opprinnelige verdiene er på gjeldende ark, trenger du bare å velge det med musen.
Hvis den er plassert på et annet ark i denne arbeidsboken, må du gi den et navn på forhånd (velg celler, trykk CTRL+F3, skriv inn navnet på området uten mellomrom), og skriv deretter dette navnet i feltet
Lim bare inn i synlige linjer iutmerke tall, formler, tekst kan gjøres på flere måter. Når du trenger å sette inn tall, formler, tekst i ikke alle rader i tabellen, kan du bruke et filter. Hvordan du installerer et filter og hvordan du filtrerer i Excel, se artikkelen "Filter i Excel". Men for å sette inn data bare i synlige celler, trenger du dine egne metoder, spesielt hvis det er mange rader.
Den første måten er vanlig .
La oss ta et bord som dette. Tabellen vil være den samme for alle eksemplene.
La oss bruke et filter for å fjerne alle sifrene 2 fra tabellen. I de gjenværende synlige cellene legger vi tallet 600. I celle B2 legger vi tallet 600, og kopierer det nedover i kolonnen (trekk nedre høyre hjørne av celle B2). Verdier ble bare kopiert til synlige celler. Du kan også sette inn formler på samme måte. Vi skriver følgende formel i celle C2. =A2*10
Det ble slik. 
La oss avbryte filteret. Resultatet er en tabell som dette.
Formelen og tallene ble bare satt inn i de filtrerte radene.
Andre vei.
Vi vil også filtrere dataene. I den første cellen skriver vi et tall, formel, tekst osv. Nå, hvis det er tusenvis av rader, velg cellene slik: trykk på tastene "Ctrl" + "Shift" + pil ned-knappen (eller opp-knappen, avhengig av hvor vi vil velge cellene - under eller over cellen der tallet ble skrevet).
Nå, eller trykk på tastekombinasjonen "Ctrl" + G, eller F5-tasten. Dialogboksen Overgang vises. Klikk på "Velg..."-knappen. Og i den nye dialogboksen "Velg en gruppe med celler", merk av i boksen ved siden av ordene "Bare synlige celler".
Klikk "OK". Sett deretter inn som vanlig.
En annen måte å få opp dialogboksen Velg gruppe av celler.På «Hjem»-fanen, i «Redigering»-delen, klikk på «Finn og velg»-knappen. I listen som vises, klikker du på funksjonen "Velg en gruppe med celler".
Til fyll synlige celler i de valgte Excel kolonner , trykk på tastekombinasjonen "Ctrl" + D. Og alle valgte kolonner vil bli fylt med data eller en formel, som i den første cellen. I vårt eksempel skrev vi tallet 800 i celle D2, kolonne D.
Tredje vei.
I en ny kolonne (i vårt eksempel, kolonne E), velg cellene. Trykk på F5-tasten. Dialogboksen Overgang vises. Klikk på "Velg..."-knappen. Og i den nye dialogboksen "Velg en gruppe med celler", merk av i boksen ved siden av ordene "Kun synlige celler". Klikk "OK". Nå, uten å avbryte valget, i den første cellen i kolonnen (vår er E2), skriv inn en formel, et tall osv. Trykk på tastekombinasjonen "Ctrl" + "Enter".
Datavalg i Excel ble utført ved hjelp av filter eller sortering. Nå må de skrives ut eller flyttes til et annet sted. Kopier til Excel konfigurert slik at skjulte celler også kopieres.
La oss vurdere to måter, Til
Hvordan kopiere filtrerte rader i Excel.
Første vei.
Det er en flott funksjon i Excel - Lim inn spesialfunksjon i Excel.
Så vi har et bord.
Hvordan installere et filter, se artikkelen " Filtrer i Excel ".
Vi bruker et filter for å fjerne alle Ivanovs fra listen. Det ble slik.
Velg tabellen og klikk "Kopier" i hurtigmenyen. Venstreklikk celle A9 og velg "verdier" i hurtigmenyen.
Klikk "OK". Voila. Ikke bare verdien av de synlige radene ble kopiert, men også formatet til cellene.
Det er én nyanse- sett inn filtrerte data ikke i radene der filteret er plassert. For eksempel i vårt eksempel - ikke i linjene 1-7, men under eller på et annet ark, etc. Hvis vi setter inn i radene der filteret er plassert, vil de filtrerte dataene også bli satt inn i radene som er skjult av filteret. Generelt vil det vise seg å være et rot. 
Andre vei.
Tabellen er den samme. Velg tabellen med filtrerte data. På «Hjem»-fanen klikker du på «Redigering»-delen Finn og uthev funksjoner i Excel. Klikk deretter på "Go"-knappen. Klikk på "Velg..."-knappen i dialogboksen som vises. I vinduet "Velg en gruppe med celler", merk av i boksen ved siden av "bare synlige celler". Klikk "OK". Nå på det samme valgte bordet med høyre mus vi kaller kontekstmenyen. Klikk på "Kopier"-funksjonen. På en ny plassering (i vårt eksempel er dette celle A15), klikk på "Sett inn". Alle. Det ble slik.
Hvordan, uten å kopiere, umiddelbart skrive ut filterdata i excel, se artikkelen "Bokmerke Excel ark"Sideoppsettet""
Lister og områder (5)
Makroer (VBA-prosedyrer) (63)
Diverse (39)
Excel-feil og feil (4)
Slik limer du inn kopierte celler kun i synlige/filtrerte celler
Generelt tror jeg at meningen med artikkelen allerede er tydelig fra tittelen. Jeg skal bare utvide det litt.
Det er ingen hemmelighet at Excel lar deg velge bare synlige rader (for eksempel hvis noen av dem er skjult eller et filter er brukt).
Så hvis du kopierer bare synlige celler på denne måten, vil de bli kopiert som forventet. Men når du prøver å lime inn noe som er kopiert inn i et filtrert område (eller inneholder skjulte rader), blir ikke resultatet av limingen akkurat det du forventet. Data vil bli satt inn selv i skjulte rader.
Kopier et enkelt celleområde og lim bare inn i synlige
For å sette inn data bare i synlige celler, kan du bruke følgende makro:
| Alternativ Eksplisitt Dim rCopyRange As Range "Med denne makroen kopierer vi dataene Sub My_Copy() If Selection.Count > 1 Then Set rCopyRange = Selection.SpecialCells(xlVisible) Else : Set rCopyRange = ActiveCell End If End Sub "Med denne makroen setter vi inn data fra den valgte cellen Sub My_Paste() Hvis rCopyRange ikke er noe, avslutt Sub Hvis rCopyRange.Areas.Count > 1 Then MsgBox "Det innlimte området må ikke inneholde mer enn ett område!",vbCritical, "Ugyldig område": 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 iCol = 1 To rCopyRange .Columns.Count li = 0: lCount = 0: le = iCol - 1 For hver rCell I rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False And _ ActiveCell.Offset (li, le).EntireRow.Hidden = False Deretter rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Loop Mens lCount >= rCell.Row - rCopyRange.Cells(1) ).Row Next rCell Next iCol Application.ScreenUpdating = True : Application.Calculation = iCalculation End Sub |
Alternativ Explicit Dim rCopyRange As Range "Bruk denne makroen til å kopiere dataene Sub My_Copy() If Selection.Count > 1 Then Set rCopyRange = Selection.SpecialCells(xlVisible) Else: Set rCopyRange = ActiveCell End If End Sub "Bruk denne makroen for å lime inn dataene starter fra de valgte cellene Sub My_Paste() If rCopyRange Is Nothing Then Exit Sub If rCopyRange.Areas.Count > 1 Then MsgBox "Det limte området må ikke inneholde mer enn ett område!", vbCritical, "Ugyldig område": 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 iCol = 1 Til rCopyRange.Columns .Count li = 0: lCount = 0: le = iCol - 1 For hver rCell I rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False And _ ActiveCell.Offset(li , le).EntireRow.Hidden = False Deretter rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Loop Mens lCount >= rCell.Row - rCopyRange.Cells(1). Rad Neste rCell Neste iCol Application.ScreenUpdating = True: Application.Calculation = iCalculation End Sub
For å fullføre bildet er det bedre å tilordne disse makroene til hurtigtaster (i kodene nedenfor gjøres dette automatisk når du åpner en bok med koden). For å gjøre dette trenger du bare å kopiere kodene nedenfor inn i modulen Denne boken (Denne arbeidsboken) :
Alternativ Eksplisitt "Avbryt tilordningen av hurtigtaster før du lukker arbeidsboken Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey "^q": Application.OnKey "^w" End Sub "Tildel hurtigtaster når du åpner arbeidsboken Private Sub Workbook_Open() Application. .OnKey "^q", "My_Copy": Application.OnKey "^w", "My_Paste" End Sub
Nå kan du kopiere ønsket rekkevidde ved å trykke på tastene Ctrl + q , og sett den inn i den filtrerte - Ctrl + w .
Last ned eksempel
(46,5 KiB, 9 622 nedlastinger)
Kopier bare synlige celler og lim inn bare i synlige
På forespørsel fra besøkende på nettstedet bestemte jeg meg for å forbedre meg denne prosedyren. Det er nå mulig å kopiere alle områder: med skjulte rader, skjulte kolonner, og lime inn kopierte celler også i alle områder: med skjulte rader, skjulte kolonner. Den fungerer nøyaktig på samme måte som den forrige: ved å trykke på tastene Ctrl
+
q
kopier det ønskede området (med skjulte/filtrerte rader og kolonner eller ikke skjult), og lim inn med en hurtigtast Ctrl
+
w
. Innsetting utføres også i skjulte/filtrerte rader og kolonner eller uten skjulte.
Hvis det kopierte området inneholder formler, kan du bare kopiere celleverdiene for å unngå referanseforskyvning - dvs. Når du setter inn verdier, vil ikke formler settes inn, men resultatet av deres beregning. Eller hvis det er nødvendig å bevare formatene til cellene der innsettingen skjer, vil bare celleverdiene kopieres og limes inn. For å gjøre dette må du erstatte linjen i koden (i filen nedenfor):
| rCell.Copy rResCell.Offset(lr, lc) |
rCell.Copy rResCell.Offset(lr, lc)
til dette:
| rResCell.Offset(lr, lc) = rCell.Value |
rResCell.Offset(lr, lc) = rCell.Value
Begge disse linjene er tilstede i filen nedenfor; du trenger bare å la den være mer egnet for oppgavene dine.
Nedlastingseksempel:
(54,5 KiB, 7 928 nedlastinger)
Se også:
[]
("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":"venstre","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 "texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; bakgrunnsfarge:#333333; opasitet:0.6; filter:alpha(opacity=60);","titlecss":"display:block; stilling:slektning; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; stilling:slektning; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; farge:#fff; margin-top:8px;","buttoncss":"display:block; stilling:slektning; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))
