Pegar datos solo en celdas filtradas (eliminando datos no filtrados)

Como ya hemos visto anteriormente, si se copia un rango de celdas filtradas y se pega en una zona vacía de la hoja de cálculo, los datos se pegan de forma continua, no al lado de los originales, como suele ser de esperar.

Para conseguir ese efecto se puede recurrir a dos técnicas:

1 - Invertir el filtro y eliminar los datos que no deseamos, manteniendo de esa forma los que si deseamos.

2- Mediante programación en VBA (Visual Basic for Applications)

En este post explicaremos el primer método.

Se trata simplemente de aplicar lógica negativa a nuestro problema.

Si tenemos que pegar los datos de edad de una columna de las personas de sexo masculino, lo que haremos será seleccionar las personas de sexo femenino y borrar los datos.

Mostraremos el proceso, paso a paso:

Primero creamos los datos.

Estos serán los datos que deseamos mantener, y que en principio intentaríamos copiar y pegar, pero como hemos visto en el post anterior (Pegar celdas filtradas en filas filtradas), esto provocará que no se peguen al lado de los datos originales, sinó consecutivamente desde la primera fila.

En su lugar, haremos una selección usando la lógica inversa, esto es, mostrando los datos que no deseamos mantener. 

En el caso del ejemplo, los salarios de las personas de sexo Femenino.

Una vez mostrados, seleccionamos los datos que no deseamos.

Y lo eliminamos.

Un vez quitado el filtro, obtenemos el resultado deseado.

Nota:

En caso de tener que seleccionar una cantidad importante de datos, es útil, usar una columna auxiliar para indicar los datos a los que se aplicará el filtro.

Pegar celdas filtradas en filas filtradas

Cuando se trabaja con filtros, en muchas ocasiones interesa aplicar un filtro para luego copiar los datos mostrados.

Por ejemplo, creamos una columna con datos de edades y aplicamos un Autofiltro, mediante el que mostramos los datos de edad de la gente "mayor de edad", esto es, aquellos que han cumplido 18 años.

Una vez aplicado el filtro, seleccionamos y copiamos los datos.

Nos colocamos en la celda C1 y pegamos.

En este contexto mucha gente espera que los dato se peguen al lado de los ya filtrados para obtener una lista con solo esos datos.

Lo que en realidad sucede es que Microsoft Excel, ha seleccionado ocho celdas, y las pegará en las primeras ocho filas de la columna C (en realidad lo hará en un rango que comienza en la celda seleccionada actualmente)

Este efecto se ve claramente al desactivar el filtro.

Es importante tener en cuenta esta forma de proceder, ya que es muy fácil, provocar un cruce de datos inadvertido, consiguiendo resultados indeseados. 

Para obtener una columna con los datos filtrados y sin los datos no filtrados, existen diversas formas de proceder.

En la mayoría de los casos es suficiente con invertir el filtro y eliminar los datos no deseados.

Copiar y Pegar Formato

Justo lo contrario a "Pegar Valores" es "Copiar Formato".

Esta opción nos puede ahorrar mucho tiempo a la hora de aplicar formatos, ya que en un único paso aplica todos los formatos de otra celda.

Los formatos copiados incluyen la fuente, con su tamaño, colores de fondo y fuente, así como todas las demás características que puedan tener (Negrita, Cursiva, etc)

También copia el formato condicional, así como alineaciones y formatos de bordes de celda.

Una vez aplicados todos los formato a una celda, en este caso la celda B2, seleccionamos esta celda.

Y pulsamos en el botón "Copiar Formato"

Una vez hecho esto, solo tenemos que pulsar en la celda que queremos que tenga el mismo conjunto de formatos.

También es posible aplicar el mismo formato a una selección de celdas, ya sea una Columna, Fila o Rango.

Para ello seleccionaremos la letra o letras de la columna, los números en el caso de Filas o un rectángulo de celdas en el caso de un Rango.

En el ejemplo, tras seleccionar la casilla B2 y pulsar el botón "Copiar Formato" se han seleccionado las columnas G:J. Al soltar el botón, se aplica el mismo formato a todas las celdas de las cuatro columnas seleccionadas (G, H, I, J).

Bonus:

No todo el mundo sabe que pulsando con doble clic el botón "Copiar Formato" este queda seleccionado hasta que se vuelve a pulsar en el, permitiendo de esta forma aplicar el formato a varia celdas aisladas con solo pulsar en ellas.

Pegar valores

Según el uso que les demos a las fórmulas en una hoja de cálculo, podríamos definir tres tipos distintos de hojas.

Un primer tipo en el que no existen fórmulas y que se usa para filtrar, ordenar o agrupar datos ya conocidos.

Un segundo tipo en el que se necesita conocer unos datos finales a partir de unos dato originales conocidos y que no cambian.

Y un tercer tipo en el que los datos de partida pueden cambiar modificando de esta manera los resultados.

Es posible transformar el segundo tipo de hojas en el primero eliminando las fórmulas una vez calculadas.

Esto mejorará la velocidad de uso de la Hoja de cálculo, reducirá el tamaño, y mejorará la velocidad de guardado del documento, ya que, por defecto los documentos de Microsoft Excel se recalculan al guardarse.

En este caso solo hay que seleccionar las columnas deseadas, puede ser todo el documento, "Copiar" y luego, sobre las mismas celdas, seleccionar la opción "Pegar Valores", por ejemplo tras pulsar el botón derecho.

El icono es el que muestro a continuación.

En el ejemplo, en un fichero de 400 lineas se ha conseguido pasar de 57 KB a 46 KB, una reducción cercana al 20% de tamaño, in perder ninguna información útil.

En esta imagen podemos ver las fórmulas utilizadas.

Las cuales tras usar el comando "Pegar Valores" solo contienen los datos mostrados a continuación.

Nota: Usualmente en documentos de gran cantidad de líneas es útil, a medida que se van consiguiendo resultados intermedios, ir pegando como valores, para mejorar la velocidad de Microsoft Excel.

Convertir Verdadero / Falso en 1 / 0

En ocasiones es muy cómodo poder operar con los valores de una comparación.

En muchos lenguajes de programación el valor Verdadero (también denominado True) se asocia al valor numérico 1, mientras que el Falso (False) se asocia principalmente a 0 (aunque en eso hay diferencias entre los distintos lenguajes).

Si Verdadero fuese 1 sería suficiente con seleccionar la columna y ver el recuento de valores y la suma de los mismos para saber cuantos resultados hay, y cuantos de ellos son Verdaderos, y por lo tanto restando también sabríamos cuantos son Falsos.

Supongamos que nos dan una columna A con edades de personas, y nos solicitan que en la columna B indiquemos si son mayores o menores de edad.

Podríamos resolverlo usando la función si, de una forma como esta:

=SI(A2>=18;"Mayor de edad";"Menor de edad")

Sin embargo esta solución, aunque es autoexplicativa, no nos permite ir más allá fácilmente.

Por ejemplo, si ahora quisiésemos indicar en una celda el número de mayores de edad, habría que usar una función contar.si, la cual tiene un alto coste de procesado, y puede retardar mucho la obtención del resultado en hojas con muchos datos.

Para mejorar la velocidad de procesado de la hoja, nos limitaremos ha hacer una comparación en la celda. Para Microsoft Excel el resultado de una comparación es Verdadero o Falso.

Se podría hacer de esta manera. =A2>=18

Como se puede ver, el resultado sigue siendo autoexplicativo, y se ha utilizado solo una comparación para obtenerlo, mejorando considerablemente el tiempo de calculo de la hoja.

Se puede dar un paso más, transformando el valor VERDADERO / FALSO en un número mediante la función N() de Excel, que entre otras cosas, transforma VERDADERO en 1 y FALSO en 0.

Se usaría así =N(A2>=18)

Además en este caso podemos apreciar, que con solo seleccionar los datos de la columna B, podemos comprobar en la barra de estado que el total de datos es 15 (Recuento: 15) y que el total de mayores de edad, esto es resultados verdaderos es de 7.

Eso permitiría usar de forma sencilla estos datos como fuente para otros cálculos.

Localizar última celda en Microsoft Excel

La última celda de Microsoft Excel, es la intersección entre la fila inferior y la columna de la derecha con datos.

A priori e tan sencillo como fijarse en los datos, pero esto puede ser un tanto difícil si el número de filas o columnas es muy grande, o existen datos no visibles en las celdas, como pueden ser caracteres no imprimibles, espacios o fórmulas que tengan como resultado un dato vacío.

Para poder saber cual es la última celda de un documento de Microsoft Excel, por lo tanto, no es suficiente fijarse en las celdas.

Se puede localizar la última celda mediante el comando "Ir a" al que se puede acceder mediante la tecla de Función F5 o en Inicio, Modificar, Buscar y Seleccionar, Ir a.

Una vez accedido al menú Ir a..., se pulsa el botón Especial...

Posteriormente se selecciona la opción Última celda.

Aunque, aparentemente la última celda era la H12, vemos que la celda seleccionada es la J12.

Esto implica que en alguna celda de la columna J hay algún dato.

Como se puede ver, en la celda J1 hay un espacio, lo que hace que la última celda.

En estos casos eliminar las columnas con datos no visibles puede reducir el tamaño del documento final.

Diferencias de tamaño en Microsoft Excel debidas a la localización de la última celda usada

En ocasiones, vemos documentos de Microsoft Excel con muy poco contenido, pero un tamaño elevado.

Esto puede ser debido a diversas razones, por ejemplo, la existencias de hojas ocultas con contenido.

Sin embargo, una de las razones más habituales es que la hoja de cálculo esté usando más celdas de las necesarias.

En un programa como Microsoft Excel, al guardar el fichero, se almacena la información necesaria de una serie de celdas.

En concreto se almacenan el contenido de un rectángulo de celdas que tuviese su esquina superior izquierda en la celda A1, y su esquina inferior derecha en la intersección de la última columna y la última fila con datos. Esta celda se denomina comúnmente "última celda".

En el ejemplo de la imagen solo se ha puesto contenido en dos celdas (H1 y A12), sin embargo, Microsoft Excel almacenará el contenido de todo el rectángulo resaltado, desde la celda A1 hasta la celda H12.

Esto implica que para almacenar los datos de esas dos celdas, es preciso guardar en fichero las 96 celdas correspondientes a la región A1:H12.

Esto en ficheros pequeños no tiene demasiada relevancia, pero en ficheros grandes puede hacer que el tamaño del fichero aumente considerablemente.

Dependiendo del tipo de fichero usado para almacenar la información (xls, xlsx, xlsb, ods) la repercusión sobre el tamaño puede ser distinta, pero siempre es una buena idea intentar compactar lo máximo posible los datos a fin de reducir el tamaño del fichero final.

El efecto del incremento de tamaño, se observa principalmente, cuando se dejan in usar celdas que se han utilizado con anterioridad. En el caso de celdas vacías que nunca han contenido datos el efecto puede ser inapreciable.

Este caso es aplicable a todas las herramienta de hoja de cálculo como pueden ser Libreoffice Calc o Google Spreadsheets.

Eliminar imágenes no deseadas en documentos Excel

En ocasiones cuando se pega un dato de una fuente externa, en especial desde una página web, con los datos se desean obtener, se arrastran imágenes no deseadas, como en el ejemplo.

La solución típica en estos casos es ir seleccionando las imágenes de una en una para luego borrarlas pulsando la tecla suprimir, sin embargo es posible realizar este proceso en un único paso.

Para ello primero seleccionamos la opción "Ir a..." que se encuentra en Inicio, Modificar, o pulsamos la tecla F5.

La ventana que abre permite ir a una celda determinada o acceder a selecciones avanzadas, pulsando el botón "Especial"

Pulsando ese botón accedemos a la ventana de selección en la que podemos, entre otras cosas, seleccionar los Objetos. 

Dado que para Microsoft Excel, las imágenes son un objeto, esta acción selecciona todas las imágenes de la hoja activa.

Lo siguiente es, simplemente pulsar la tecla Supr, y desaparecen todas las imágenes al unísono.