Come selezionare solo le celle visibili in Excel. Copia i dati filtrati in Excel. Copia senza danneggiare la formattazione
Pavlov Nikolaj
In questo articolo vorrei presentarvi le tecniche più efficaci per lavorare in Microsoft Excel, raccolti da me negli ultimi 10 anni di lavoro su progetti e di formazione su questo meraviglioso programma. Non c'è una descrizione di tecnologie super complesse qui, ma ci sono tecniche per tutti i giorni - semplici ed efficaci, descritte senza "acqua" - solo "residuo secco". La maggior parte di questi esempi non ti richiederanno più di uno o due minuti per padroneggiarli, ma ti aiuteranno a risparmiare molto di più.
Passa rapidamente al foglio desiderato
Ti capita di lavorare con Cartelle di lavoro di Excel composto da un gran numero di fogli? Se ce ne sono più di una dozzina, ogni passaggio al successivo foglio richiesto diventa di per sé un piccolo problema. Una soluzione semplice ed elegante a questo problema è fare clic nell'angolo in basso a sinistra della finestra sui pulsanti per scorrere le schede del foglio non con il tasto sinistro, ma con il tasto destro del mouse: apparirà un sommario del libro con lista completa tutti i fogli e in un solo movimento puoi raggiungere il foglio desiderato:
Questo è molto più veloce che scorrere le schede dei fogli utilizzando gli stessi pulsanti alla ricerca di ciò di cui hai bisogno.
Copia senza danneggiare la formattazione
Quante centinaia (migliaia?) di volte ho visto questa immagine, in piedi dietro i miei studenti durante i corsi di formazione: l'utente inserisce una formula nella prima cella e poi la "allunga" su tutta la colonna, violando la formattazione delle righe sottostanti, poiché questo metodo copia non solo la formula, ma anche il formato della cella. Di conseguenza è necessario correggere manualmente il danno. Un secondo per copiare e poi 30 per riparare un disegno danneggiato dalla copiatura.
A partire da Excel 2002, esiste una soluzione a questo problema semplice ed elegante. Immediatamente dopo aver copiato (trascinato) la formula sull'intera colonna, è necessario utilizzare uno smart tag, una piccola icona che appare temporaneamente nell'angolo in basso a destra dell'intervallo. Cliccandoci sopra verrà visualizzato un elenco possibili opzioni copia, dove puoi selezionare Riempi senza formattazione. In questo caso le formule vengono copiate ma la formattazione non è:
Copia solo delle celle visibili
Se lavori in Microsoft Excel da più di una settimana, probabilmente hai già riscontrato un problema simile: in alcuni casi, quando si copia e incolla le celle, vengono inserite più celle di quante ne fossero, a prima vista, copiate. Ciò può verificarsi se l'intervallo copiato include righe/colonne, raggruppamenti, totali parziali o filtri nascosti. Prendiamo come esempio uno di questi casi:
In questa tabella vengono calcolati i subtotali e le righe sono raggruppate per città: questo è facile da capire dai pulsanti più-meno a sinistra della tabella e dalle interruzioni nella numerazione delle righe visibili. Se selezioniamo, copiamo e incolliamo i dati da questa tabella nel solito modo, ci ritroveremo con 24 righe extra. Vogliamo solo copiare e incollare i risultati!
Puoi risolvere il problema selezionando accuratamente ciascuna riga dei totali tenendo premuto il tasto CTRL, come faresti per selezionare intervalli non adiacenti. Ma cosa succede se non ci sono tre o cinque di queste linee, ma diverse centinaia o migliaia? Esiste un altro modo, più veloce e più conveniente:
Seleziona l'intervallo da copiare (nel nostro esempio è A1:C29)
Premi il tasto F5 sulla tastiera e poi il pulsante Seleziona nella finestra che si apre.
Apparirà una finestra che consentirà all'utente di selezionare non tutto in una riga, ma solo le celle necessarie:
In questa finestra, seleziona l'opzione Solo celle visibili e fai clic su OK.
La selezione risultante può ora essere copiata e incollata in modo sicuro. Di conseguenza, otterremo una copia esatta di cellule visibili e inserisci al posto delle 29 inutili solo le 5 righe che ci servono.
Se sospetti che dovrai eseguire spesso un'operazione del genere, ha senso aggiungere un pulsante alla barra degli strumenti di Microsoft Excel per richiamare rapidamente tale funzione. Questo può essere fatto attraverso il menu Strumenti > Personalizza, poi vai alla scheda Comandi, nella categoria Modifica, trova il pulsante Seleziona celle visibili e trascinalo sulla barra degli strumenti con il mouse:
Conversione di righe in colonne e viceversa
Un'operazione semplice, ma se non sai come eseguirla correttamente, puoi dedicare mezza giornata a trascinare manualmente le singole celle:
In realtà è semplice. In quella parte della matematica superiore che descrive le matrici, esiste il concetto di trasposizione, un'azione che scambia tra loro righe e colonne in una matrice. In Microsoft Excel, ciò viene implementato in tre passaggi: Copiare la tabella
Fare clic con il tasto destro su una cella vuota e selezionare Incolla speciale.
Nella finestra che si apre, seleziona il flag Transpose e fai clic su OK:
Aggiungi rapidamente dati a un grafico
Immaginiamo una situazione semplice: hai un report dell'ultimo mese con un diagramma visivo. Il compito è aggiungere nuovi dati numerici al grafico per questo mese. Il modo classico per risolvere questo problema è aprire la finestra dell'origine dati per il grafico, dove aggiungi una nuova serie di dati inserendone il nome ed evidenziando l'intervallo con i dati desiderati. Inoltre, questo è spesso più facile a dirsi che a farsi: tutto dipende dalla complessità del diagramma.
Un altro modo, semplice, veloce e bello, è selezionare le celle con i nuovi dati, copiarle (CTRL+C) e incollarle (CTRL+V) direttamente nel grafico. Excel 2003, a differenza delle versioni successive, supporta anche la possibilità di trascinare un intervallo selezionato di celle di dati e rilasciarlo direttamente nel grafico utilizzando il mouse!
Se vuoi controllare tutte le sfumature e le sottigliezze, puoi utilizzare non una pasta normale, ma una speciale selezionando Modifica> Incolla speciale dal menu. In questo caso, Microsoft Excel visualizzerà una finestra di dialogo che consente di configurare dove e come verranno aggiunti esattamente i nuovi dati:
Allo stesso modo, puoi creare facilmente un grafico utilizzando i dati di tabelle diverse di fogli diversi. Eseguire lo stesso compito nel modo classico richiederà molto più tempo e impegno.
Riempimento di celle vuote
Dopo aver scaricato i report da alcuni programmi in formato Excel o durante la creazione di tabelle pivot, gli utenti spesso ricevono tabelle con celle vuote in alcune colonne. Queste omissioni non consentono di applicare alle tabelle strumenti familiari e convenienti come il filtro automatico e l'ordinamento. Naturalmente, è necessario riempire i vuoti con valori provenienti da celle di livello superiore:
Naturalmente, con una piccola quantità di dati, ciò può essere fatto facilmente mediante una semplice copia, trascinando manualmente ciascuna cella di intestazione nella colonna A sulle celle vuote. Cosa succede se la tabella contiene diverse centinaia o migliaia di righe e diverse dozzine di città?
C'è un modo per risolvere questo problema in modo rapido e bello usando una formula:
Seleziona tutte le celle in una colonna con spazi vuoti (ovvero l'intervallo A1: A12 nel nostro caso)
Per mantenere nella selezione solo le celle vuote, premere il tasto F5 e, nella finestra di navigazione che si apre, premere il pulsante Seleziona. Vedrai una finestra che ti permette di selezionare quali celle vogliamo selezionare:
Impostare l'interruttore su Vuoto e fare clic su OK. Ora nella selezione dovrebbero rimanere solo le celle vuote:
Senza modificare la selezione, ad es. Senza toccare il mouse, inserisci la formula nella prima cella selezionata (A2). Premi il segno uguale sulla tastiera e poi la freccia su. Otteniamo una formula che si riferisce alla cella precedente:
Per inserire contemporaneamente la formula creata in tutte le celle vuote selezionate, premere non il tasto INVIO, ma la combinazione CTRL + INVIO. La formula riempirà tutte le celle vuote:
Ora non resta che sostituire le formule con valori per registrare i risultati. Seleziona l'intervallo A1: A12, copialo e incolla i valori nelle celle utilizzando Incolla speciale.
Elenco a discesa in una cella
Una tecnica che, senza esagerare, tutti coloro che lavorano in Excel dovrebbero conoscere. Il suo utilizzo può migliorare quasi ogni tavolo, indipendentemente dal suo scopo. In tutti i corsi di formazione, cerco di mostrarlo ai miei studenti il primo giorno.
L'idea è molto semplice: in tutti i casi in cui devi inserire dati da qualsiasi set, invece di inserire manualmente una cella dalla tastiera, seleziona il valore desiderato con il mouse dall'elenco a discesa:
Selezionando un prodotto dal listino prezzi, il nome del cliente dal database clienti, il nome completo del dipendente dalla tabella del personale, ecc. Ci sono molte opzioni per utilizzare questa funzione.
Per creare un elenco a discesa in una cella:
Seleziona le celle in cui desideri creare un elenco a discesa.
Se hai Excel 2003 o versioni precedenti, seleziona Dati>Convalida dal menu. Se disponi di Excel 2007/2010, vai alla scheda Dati e fai clic sul pulsante Convalida dati.
Nella finestra che si apre, seleziona l'opzione Elenco dall'elenco a discesa.
Nel campo Origine, è necessario specificare i valori che dovrebbero essere nell'elenco. Ecco le opzioni possibili:
Inserisci le opzioni di testo in questo campo separate da punto e virgola
Se l'intervallo di celle con i valori originali si trova sul foglio corrente, devi solo selezionarlo con il mouse.
Se si trova su un altro foglio di questa cartella di lavoro, dovrai dargli un nome in anticipo (seleziona le celle, premi CTRL+F3, inserisci il nome dell'intervallo senza spazi), quindi scrivi questo nome nel campo
Se alcune celle, righe o colonne in un foglio di lavoro non sono visibili, puoi copiare tutte le celle (o solo quelle visibili). Per impostazione predefinita, Excel copia non solo le celle visibili, ma anche le celle nascoste o filtrate. Se desideri copiare solo le celle visibili, procedi nel seguente modo. Ad esempio, puoi copiare solo i dati di riepilogo da un foglio di lavoro strutturato.
Seguire i passaggi seguenti.
Nota: Quando copi, i valori vengono inseriti in sequenza in righe e colonne. Se l'area di incollamento contiene righe o colonne nascoste, potrebbe essere necessario renderle visibili per visualizzare tutti i dati copiati.
Quando copi e incolli celle visibili in un intervallo di dati che contiene celle nascoste o a cui è applicato un filtro, potresti notare che le celle nascoste vengono incollate insieme alle celle visibili. Purtroppo non è possibile modificare questa impostazione quando copi e incolli un intervallo di celle in Excel per il Web perché Incollare solo le celle visibili non è disponibile.
Tuttavia, se formatti i dati come tabella e applichi un filtro, puoi copiare e incollare solo le celle visibili.
Se non è necessario formattare i dati come tabella e avere Classic installato Applicazione Excel, puoi aprire una cartella di lavoro al suo interno per copiare e incollare le celle visibili. Per fare ciò, fare clic sul pulsante Apri in Excel e segui i passaggi in Copiare e incollare solo le celle visibili.
Informazioni aggiuntive
Puoi sempre porre una domanda a uno specialista della community tecnica di Excel, chiedere aiuto nella community Risposte e anche suggerire nuova caratteristica o miglioramenti sul sito web
Incolla solo nelle righe visibili all'internoEccellere numeri, formule, testo possono essere eseguiti in diversi modi. Quando devi inserire numeri, formule, testo non in tutte le righe della tabella, puoi utilizzare un filtro. Come installare un filtro e come filtrare in Excel, vedere l'articolo "Filtro in Excel". Ma per inserire i dati solo nelle celle visibili, sono necessari metodi personalizzati, soprattutto se sono presenti molte righe.
Il primo modo è ordinario .
Prendiamo un tavolo come questo. La tabella sarà la stessa per tutti gli esempi.
Usiamo un filtro per rimuovere tutte le cifre 2 dalla tabella. Nelle restanti celle visibili inseriamo il numero 600. Nella cella B2 inseriamo il numero 600, quindi copiamolo lungo la colonna (tiriamo l'angolo in basso a destra della cella B2). I valori sono stati copiati solo nelle celle visibili. Puoi anche inserire formule allo stesso modo. Scriviamo la seguente formula nella cella C2. =A2*10
È andata così. 
Cancelliamo il filtro. Il risultato è una tabella come questa.
La formula e i numeri sono stati inseriti solo nelle righe filtrate.
Secondo modo.
Filtreremo anche i dati. Nella prima cella scriviamo un numero, una formula, un testo, ecc. Ora, se ci sono migliaia di righe, seleziona le celle in questo modo: premi i tasti “Ctrl” + “Maiusc” + il pulsante freccia giù (o il pulsante su, a seconda di dove vogliamo selezionare le celle - sotto o sopra la cella in cui è stato scritto il numero).
Ora, oppure premere la combinazione di tasti “Ctrl” + G o il tasto F5. Verrà visualizzata la finestra di dialogo Transizione. Fare clic sul pulsante "Seleziona...". E, nella nuova finestra di dialogo "Seleziona un gruppo di celle", seleziona la casella accanto alle parole "Solo celle visibili".
Fare clic su "OK". Quindi inseriscilo come al solito.
Un altro modo per visualizzare la finestra di dialogo Seleziona gruppo di celle.Nella scheda "Home", nella sezione "Modifica", fare clic sul pulsante "Trova e seleziona". Nell'elenco visualizzato, fai clic sulla funzione "Seleziona un gruppo di celle".
A riempie le celle visibili in quelle selezionate Colonne di Excel , premi la combinazione di tasti “Ctrl” + D. E tutte le colonne selezionate verranno riempite con dati o una formula, come nella prima cella. Nel nostro esempio, abbiamo scritto il numero 800 nella cella D2, colonna D.
Terza via.
In una nuova colonna (nel nostro esempio, la colonna E), seleziona le celle. Premere il tasto F5. Verrà visualizzata la finestra di dialogo Transizione. Fare clic sul pulsante "Seleziona...". E, nella nuova finestra di dialogo "Seleziona un gruppo di celle", seleziona la casella accanto alle parole "Solo celle visibili". Fare clic su "OK". Ora, senza annullare la selezione, nella prima cella della colonna (la nostra è E2), inserisci una formula, un numero, ecc. Premere la combinazione di tasti “Ctrl” + “Invio”.
Elenchi e intervalli (5)
Macro (procedure VBA) (63)
Vari (39)
Bug e problemi di Excel (4)
Come incollare le celle copiate solo in celle visibili/filtrate
In generale, il significato dell'articolo, credo, sia già chiaro dal titolo. Lo espanderò solo un po'.
Non è un segreto che Excel ti permetta di selezionare solo le righe visibili (ad esempio se alcuni di essi sono nascosti o viene applicato un filtro).
Pertanto, se copi solo le celle visibili in questo modo, verranno copiate come previsto. Ma quando provi a incollare qualcosa di copiato in un intervallo filtrato (o contenente righe nascoste), il risultato dell'incollamento non sarà esattamente quello che ti aspettavi. I dati verranno inseriti anche nelle righe nascoste.
Copia un singolo intervallo di celle e incollalo solo in quelle visibili
Per inserire dati solo nelle celle visibili, puoi utilizzare la seguente macro:
| Opzione esplicita Dim rCopyRange come intervallo "Con questa macro copiamo i dati Sub My_Copy() Se Selection.Count > 1 Quindi imposta rCopyRange = Selection.SpecialCells(xlVisible) Else: Imposta rCopyRange = ActiveCell End If End Sub "Con questa macro inseriamo i dati partendo dalla cella selezionata Sub My_Paste() Se rCopyRange non è nulla, esci da Sub se rCopyRange.Areas.Count > 1, quindi MsgBox "L'intervallo incollato non deve contenere più di una regione!",vbCritico, "Intervallo non valido": Esci 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 per ogni rCell in rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False And _ 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 ).Row Next rCell Next iCol Application.ScreenUpdating = True : Application.Calculation = iCalculation End Sub |
Option Explicit Dim rCopyRange As Range "Utilizza questa macro per copiare i dati Sub My_Copy() If Selection.Count > 1 Then Set rCopyRange = Selection.SpecialCells(xlVisible) Else: Set rCopyRange = ActiveCell End If End Sub "Utilizza questa macro per incollare i dati iniziano dalle celle selezionate Sub My_Paste() Se rCopyRange Is Nothing Then Exit Sub If rCopyRange.Areas.Count > 1 Then MsgBox "L'intervallo incollato non deve contenere più di un'area!", vbCritical, "Intervallo non valido": 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 per ogni rCell in rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False And _ 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). Riga successiva rCell successivo iCol Application.ScreenUpdating = True: Application.Calculation = iCalculation End Sub
Per completare il quadro, è meglio assegnare queste macro ai tasti di scelta rapida (nei codici seguenti ciò avviene automaticamente all'apertura di un libro con il codice). Per fare ciò, devi solo copiare i codici seguenti nel modulo Questo libro (Questa cartella di lavoro) :
Option Explicit "Annulla l'assegnazione dei tasti di scelta rapida prima di chiudere la cartella di lavoro Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey "^q": Application.OnKey "^w" End Sub "Assegna i tasti di scelta rapida all'apertura della cartella di lavoro Private Sub Workbook_Open() Application .OnKey "^q", "My_Copy": Application.OnKey "^w", "My_Paste" End Sub
Ora puoi copiare l'intervallo desiderato premendo i tasti Ctrl + Q e inserirlo in quello filtrato - Ctrl + w .
Scarica esempio
(46,5 KiB, 9.622 download)
Copia solo le celle visibili e incolla solo in quelle visibili
Su richiesta dei visitatori del sito, ho deciso di migliorare questa procedura. Ora è possibile copiare qualsiasi intervallo: con righe nascoste, colonne nascoste e incollare le celle copiate anche in qualsiasi intervallo: con righe nascoste, colonne nascoste. Funziona esattamente come il precedente: premendo i tasti Ctrl
+
Q
copiare l'intervallo desiderato (con righe e colonne nascoste/filtrate o non nascoste) e incollalo con una scorciatoia da tastiera Ctrl
+
w
. L'inserimento viene eseguito anche in righe e colonne nascoste/filtrate o senza colonne nascoste.
Se l'intervallo copiato contiene formule, per evitare lo spostamento dei riferimenti, puoi copiare solo i valori della cella, ad es. Quando si inseriscono i valori, non verranno inserite le formule, ma il risultato del loro calcolo. Oppure se è necessario preservare i formati delle celle in cui avviene l'inserimento, verranno copiati e incollati solo i valori delle celle. Per fare ciò è necessario sostituire la riga nel codice (nel file sottostante):
| rCell.Copia rResCell.Offset(lr, lc) |
rCell.Copia rResCell.Offset(lr, lc)
a questa:
| rResCell.Offset(lr, lc) = rCell.Valore |
rResCell.Offset(lr, lc) = rCell.Valore
Entrambe queste righe sono presenti nel file sottostante; devi solo lasciare quella più adatta ai tuoi compiti.
Scarica esempio:
(54,5 KiB, 7.928 download)
Vedi anche:
[]
("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":"destra","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:blocco; riempimento:12px; text-align:sinistra;","textbgcss":"display:blocco; posizione:assoluta; superiore:0px; sinistra:0px; larghezza:100%; altezza:100% ; background-color:#333333; opacità:0.6; filter:alpha(opacità=60);","titlecss":"display:block; posizione:relativa; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; posizione:relativa; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; colore:#fff; margin-top:8px;","buttoncss":"display:block; posizione:relativa; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))
La selezione dei dati in Excel è stata effettuata utilizzando un filtro o un ordinamento. Ora devono essere stampati o spostati in un'altra posizione. Copia in Excel configurato in modo che vengano copiate anche le celle nascoste.
Consideriamo due modi, A
Come copiare righe filtrate in Excel.
Primo modo.
C'è un'ottima funzione in Excel: Incolla la funzione speciale in Excel.
Quindi abbiamo un tavolo.
Come installare un filtro, vedere l'articolo " Filtra in Excel ".
Usiamo un filtro per rimuovere tutti gli Ivanov dall'elenco. È andata così.
Selezionare la tabella e fare clic su "Copia" nel menu contestuale. Fare clic con il tasto sinistro sulla cella A9 e selezionare "valori" nel menu contestuale.
Fare clic su "OK". Ecco. Non è stato copiato solo il valore delle righe visibili, ma anche il formato delle celle.
C'è una sfumatura- inserire i dati filtrati non nelle righe dove si trova il filtro. Ad esempio, nel nostro esempio, non nelle righe 1-7, ma sotto o su un altro foglio, ecc. Se inseriamo nelle righe dove si trova il filtro, allora i dati filtrati verranno inseriti anche nelle righe nascoste dal filtro. In generale, si rivelerà un disastro. 
Secondo modo.
La tabella è la stessa. Seleziona la tabella con i dati filtrati. Nella scheda "Home", fai clic sulla sezione "Modifica". Trova ed evidenzia le funzioni in Excel. Quindi, fare clic sul pulsante "Vai". Nella finestra di dialogo che appare, fare clic sul pulsante “Seleziona...”. Nella finestra "Seleziona un gruppo di celle", seleziona la casella accanto a "solo celle visibili". Fare clic su "OK". Ora sulla stessa tabella selezionata con il tasto destro del mouse chiamiamo menù contestuale. Fare clic sulla funzione "Copia". In una nuova posizione (nel nostro esempio, questa è la cella A15), fai clic su "Inserisci". Tutto. È andata così.
Come, senza copiare, subito stampare i dati del filtro in Excel, vedere l'articolo "Segnalibro foglio Excel"Layout di pagina""
