contingut
Les files i columnes buides poden ser un dolor a les taules en molts casos. Les funcions estàndard per ordenar, filtrar, resumir, crear taules dinàmiques, etc. perceben les files i columnes buides com un salt de taula, sense recollir les dades situades més darrere d'elles. Si hi ha molts d'aquests buits, eliminar-los manualment pot ser molt costós i no funcionarà eliminar tots alhora "a granel" mitjançant el filtratge, perquè el filtre també "ensopegarà" als descansos.
Vegem diverses maneres de resoldre aquest problema.
Mètode 1. Cerca cel·les buides
Potser no és la més convenient, però definitivament val la pena esmentar la manera més senzilla.
Suposem que estem davant d'una taula que conté moltes files i columnes buides a l'interior (ressaltats per a més claredat):
Suposem que estem segurs que la primera columna de la nostra taula (columna B) sempre conté el nom d'una ciutat. Aleshores, les cel·les buides d'aquesta columna seran un signe de files buides innecessàries. Per eliminar-los tots ràpidament, feu el següent:
- Seleccioneu l'interval amb ciutats (B2:B26)
- Premeu la tecla F5 i premeu Destacar (Vés a Especial) o seleccioneu a la pestanya Inici — Cerca i selecciona — Selecciona un grup de cel·les (Inici — Cerca i selecciona — Vés a l'especial).
- A la finestra que s'obre, seleccioneu l'opció Cel·les buides (En blanc) i premi OK – S'han de seleccionar totes les cel·les buides de la primera columna de la nostra taula.
- Ara seleccioneu a la pestanya Home Comando Suprimeix: suprimeix files del full (Suprimeix — Suprimeix files) o premeu la drecera del teclat Ctrl+menys – i la nostra tasca està resolta.
Per descomptat, podeu desfer-vos de columnes buides exactament de la mateixa manera, utilitzant la capçalera de la taula com a base.
Mètode 2: cerqueu files buides
Com ja heu descobert, el mètode anterior només funcionarà si les nostres dades contenen necessàriament files i columnes completament plenes, que es poden connectar quan cerqueu cel·les buides. Però, què passa si no hi ha aquesta confiança i les dades també poden contenir cel·les buides?
Mireu la taula següent, per exemple, per a aquest cas:
Aquí l'enfocament serà una mica més complicat:
- Introduïu a la cel·la A2 la funció COUNT (COUNTA), que calcularà el nombre de cel·les plenes a la fila de la dreta i copiarà aquesta fórmula a tota la taula:
- Seleccioneu la cel·la A2 i activeu el filtre amb l'ordre Dades – Filtre (Dades — Filtre) o drecera de teclat Ctrl+Canviar+L.
- Filtrem els zeros per la columna calculada, és a dir, totes les files on no hi ha dades.
- Queda per seleccionar les línies filtrades i eliminar-les amb l'ordre Inici — Suprimeix -' Suprimeix les files del full (Inici — Suprimeix — Suprimeix files) o drecera de teclat Ctrl+menys.
- Apaguem el filtre i obtenim les nostres dades sense línies buides.
Malauradament, aquest truc ja no es pot fer amb columnes: Excel encara no ha après a filtrar per columnes.
Mètode 3. Macro per eliminar totes les files i columnes buides d'un full
També podeu utilitzar una macro senzilla per automatitzar aquesta tasca. Premeu la drecera del teclat Alt+F11 o seleccioneu a la pestanya revelador - Visual Basic (Desenvolupador — Editor Visual Basic). Si pestanyes revelador no és visible, podeu activar-lo Fitxer – Opcions – Configuració de la cinta (Fitxer — Opcions — Personalitza la cinta).
A la finestra de l'editor de Visual Basic que s'obre, seleccioneu l'ordre de menú Inserir – Mòdul i al mòdul buit que apareix, copieu i enganxeu les línies següents:
Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки Per a r = 1 A ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 rng No és res Llavors Estableix rng = Files (r) Else Set rng = Union (rng, Files (r)) Finalitza Si Següent r Si No rng No és res Aleshores rng.Delete 'удаляем пустые столбrцы Estableix rng = Res per a ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count Si Application.CountA(Columns(r)) = 1 Aleshores, si rng no és res, aleshores estableix rng = Columns(r) En cas contrari, estableix rng = Union(rng, Columns( r)) End If Next r If Not rng Is Nothing Aleshores rng.Delete End Sub
Tanqueu l'editor i torneu a Excel.
Ara premeu la combinació Alt+F8 o botó Macros llengüeta revelador. La finestra que s'obre mostrarà totes les macros disponibles actualment per executar, inclosa la macro que acabeu de crear. DeleteEmpty. Seleccioneu-lo i feu clic al botó Correr (correr) – totes les files i columnes buides del full s'eliminaran a l'instant.
Mètode 4: Power Query
Una altra manera de resoldre el nostre problema i un escenari molt comú és eliminar les files i columnes buides a Power Query.
Primer, carreguem la nostra taula a l'Editor de consultes de Power Query. Podeu convertir-lo en un dinàmic "intel·ligent" amb la drecera de teclat Ctrl+T o simplement seleccioneu el nostre rang de dades i doneu-li un nom (per exemple dades) a la barra de fórmules, convertint-se en named:
Ara fem servir l'ordre Dades - Obtenir dades - Des de la taula / rang (Dades - Obtenir dades - De la taula / rang) i carregar-ho tot a Power Query:
Aleshores tot és senzill:
- Esborram les línies buides amb l'ordre Inici – Reduir línies – Eliminar línies – Eliminar línies buides (Inici – Eliminar files – Eliminar files buides).
- Feu clic amb el botó dret a l'encapçalament de la primera columna Ciutat i seleccioneu l'ordre Desactivar altres columnes al menú contextual. La nostra taula serà, com s'anomena tècnicament correctament, normalitzat – convertit en tres columnes: ciutat, mes i valor de la intersecció de la ciutat i mes de la taula original. La particularitat d'aquesta operació a Power Query és que omet cel·les buides a les dades d'origen, que és el que necessitem:
- Ara fem l'operació inversa: tornem a convertir la taula resultant en una de dues dimensions per tornar-la a la seva forma original. Seleccioneu la columna amb mesos i a la pestanya Transformació triar un equip Columna pivotant (Transformació: columna pivotant). A la finestra que s'obre, com a columna de valors, seleccioneu l'últim (Valor) i a les opcions avançades - l'operació No agregar (No sumar):
- Queda per tornar a carregar el resultat a Excel amb l'ordre Inici — Tancar i carregar — Tancar i carregar a... (Inici — Tancar i carregar — Tancar i carregar a...)
- Què és una macro, com funciona, on copiar el text d'una macro, com executar una macro?
- Omplint totes les cel·les buides de la llista amb els valors de les cel·les pares
- Eliminació de totes les cel·les buides d'un interval determinat
- Eliminació de totes les files buides d'un full de treball amb el complement PLEX