Eliminando filas e columnas baleiras dos datos

As filas e columnas baleiras poden ser unha dor nas táboas en moitos casos. As funcións estándar para ordenar, filtrar, resumir, crear táboas dinámicas, etc. perciben as filas e columnas baleiras como un salto de táboa, sen recoller os datos situados máis atrás delas. Se hai moitas lagoas deste tipo, eliminalas manualmente pode ser moi custosa e non funcionará eliminalas todas á vez "a granel" mediante o filtrado, porque o filtro tamén "tropezará" nas pausas.

Vexamos varias formas de resolver este problema.

Método 1. Busca celas baleiras

Este pode non ser o máis cómodo, pero definitivamente o máis sinxelo é digno de mención.

Supoñamos que estamos a tratar cunha táboa que contén moitas filas e columnas baleiras no seu interior (resaltadas para claridade):

Supoñamos que estamos seguros de que a primeira columna da nosa táboa (columna B) sempre contén o nome dunha cidade. Entón, as celas baleiras desta columna serán un sinal de filas baleiras innecesarias. Para eliminalos todos rapidamente, fai o seguinte:

  1. Seleccionar rango con cidades (B2:B26)
  2. Prema a tecla F5 e logo prema Resaltar (Ir a Especial) ou selecciona na pestana Inicio — Buscar e seleccionar — Seleccione un grupo de celas (Inicio — Buscar e seleccionar — Ir a especial).
  3. Na xanela que se abre, seleccione a opción Celas baleiras (Brazos en branco) e prensa OK – Deberían seleccionarse todas as celas baleiras da primeira columna da nosa táboa.
  4. Agora seleccione na pestana casa Mando Eliminar: elimina filas da folla (Eliminar — Eliminar filas) ou prema o atallo do teclado Ctrl+menos – e a nosa tarefa está resolta.

Por suposto, pode desfacerse das columnas baleiras exactamente do mesmo xeito, usando a cabeceira da táboa como base.

Método 2: busca filas baleiras

Como xa descubriches, o método anterior só funcionará se os nosos datos conteñen necesariamente filas e columnas totalmente cubertas, ás que se poden conectar cando se buscan celas baleiras. Pero que pasa se non hai tal confianza e os datos poden conter tamén celas baleiras?

Bótalle un ollo á seguinte táboa, por exemplo, só para tal caso:

Aquí o enfoque será un pouco máis complicado:

  1. Introduza na cela A2 a función COUNT (CONTA), que calculará o número de celas cubertas na fila da dereita e copiará esta fórmula a toda a táboa:
  2. Seleccione a cela A2 e active o filtro co comando Datos - Filtro (Datos — Filtro) ou atallo de teclado Ctrl+Desprazarse+L.
  3. Imos filtrar os ceros pola columna calculada, é dicir, todas as filas onde non hai datos.
  4. Queda por seleccionar as liñas filtradas e eliminalas co comando Inicio — Eliminar -' Elimina filas da folla (Inicio — Eliminar — Eliminar filas) ou atallo de teclado Ctrl+menos.
  5. Desactivamos o filtro e obtemos os nosos datos sen liñas baleiras.

Desafortunadamente, este truco xa non se pode facer con columnas: Excel aínda non aprendeu a filtrar por columnas.

Método 3. Macro para eliminar todas as filas e columnas baleiras dunha folla

Tamén pode usar unha macro sinxela para automatizar esta tarefa. Preme o atallo do teclado alt+F11 ou seleccione na pestana revelador - Visual Basic (Desenvolvedor — Editor de Visual Basic). Se fichas revelador non é visible, podes activalo Ficheiro - Opcións - Configuración da cinta (Ficheiro — Opcións — Personalizar cinta).

Na xanela do editor de Visual Basic que se abre, seleccione o comando de menú Inserir – Módulo e no módulo baleiro que aparece copia e pega as seguintes liñas:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки Para r = 1 Para ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 rng Is Nothing Then Establecer rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r Se Non rng Is Nothing Then rng.Delete 'удаляем пустые столбцы Set rng = Nothing Para ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count Se Application.CountA(Columns(r)) = 1 Entón, se rng non é nada, entón Establece rng = Columns(r) Entón Establece rng = Union(rng, Columns( r)) End If Next r If Not rng Is Nada Entón rng.Delete End Sub

Pecha o editor e volve a Excel. 

Agora preme combinación alt+F8 ou botón Macros aba revelador. A xanela que se abre listará todas as macros dispoñibles actualmente para executar, incluída a macro que acaba de crear. BorrarValeiro. Selecciónao e fai clic no botón Correr (correr) – eliminaranse ao instante todas as filas e columnas baleiras da folla.

Método 4: Power Query

Outra forma de resolver o noso problema e un escenario moi común é eliminar filas e columnas baleiras en Power Query.

En primeiro lugar, carguemos a nosa táboa no Editor de consultas de Power Query. Podes convertelo nun dinámico "intelixente" co atallo de teclado Ctrl+T ou simplemente selecciona o noso rango de datos e dálle un nome (por exemplo Data) na barra de fórmulas, converténdose en named:

Agora usamos o comando Datos - Obter datos - Da táboa / intervalo (Datos - Obter datos - Da táboa / intervalo) e cargamos todo en Power Query:

Entón todo é sinxelo:

  1. Eliminamos liñas baleiras co comando Inicio – Reducir liñas – Eliminar liñas – Eliminar liñas baleiras (Inicio – Eliminar filas – Eliminar filas baleiras).
  2. Fai clic co botón dereito sobre o título da primeira columna da cidade e selecciona o comando Desactivar outras columnas no menú contextual. A nosa mesa será, como se chama tecnicamente correctamente, normalizado – convertido en tres columnas: cidade, mes e valor da intersección da cidade e mes da táboa orixinal. A peculiaridade desta operación en Power Query é que omite celas baleiras nos datos de orixe, que é o que necesitamos:
  3. Agora realizamos a operación inversa: convertemos a táboa resultante de novo nunha bidimensional para devolvela á súa forma orixinal. Seleccione a columna con meses e na pestana Transformación escolle un equipo Columna pivote (Transformar — Columna pivote). Na xanela que se abre, como columna de valores, seleccione o último (Valor) e nas opcións avanzadas - a operación Non agregar (Non agregar):
  4. Queda por cargar o resultado de novo a Excel co comando Inicio — Pechar e cargar — Pechar e cargar en… (Inicio — Pechar e cargar — Pechar e cargar en…)

  • Que é unha macro, como funciona, onde copiar o texto dunha macro, como executar unha macro?
  • Enchendo todas as celas baleiras da lista cos valores das celas nai
  • Eliminando todas as celas baleiras dun intervalo determinado
  • Eliminando todas as filas baleiras dunha folla de traballo co complemento PLEX

Deixe unha resposta