Enumerar Listas con Celdas Ocultas y Condiciones

Descargar el Archivo

"Mil gracias Kiko. Aprovechando el ejemplo de tu anterior artículo, qué ocurre si hay una fila de separación entre cada cinco o seis filas y la numeración no debe ir en las celdas en donde la celda de la derecha no hay valor".

En esta ocasión vamos a solucionar el problema con un par de fórmulas y las funciones ESNUMERO y CONTAR.

Partimos del siguiente ejemplo:

Se trata de generar una enumeración a partir de la celda B3 sin que afecten las celdas ocultas y siempre que en la columna C haya valores.

1. Nos situamos en la celda E3 y escribimos la siguiente fórmula:

=SI(ESNUMERO(C3);CONTAR($C$3:C3);0)

La función ESNUMERO comprueba si la celda de referencia (en nuestro ejemplo C3) es un número o no. Los resultados posibles son VERDADERO ó FALSO. La función CONTAR cuenta el número de celdas que contienen números en el rango indicado.

2. Copiamos la fórmula de E3 en el rango E4:E31 (en nuestro ejemplo). Obtendremos el siguiente resultado:

3. Nos situamos en la celda B3 y escribimos la siguiente fórmula:

=SI(ESNUMERO(C3);E3;"")

4. Copiamos la fórmula de B3 en el rango de nuestro ejemplo B4:B31 y problema resuelto:

Evidentemente podemos ocultar la columna E (o podríamos haberla desarrollado en otra hoja) para que no afecte a la presentación de la información.

Rellenar Series con Celdas Ocultas

"Tengo que realizar una enumeración del 1 al 1.500. El problema es que tengo unas 2.000 filas que deben estar ocultas y que no deben entrar en la enumeración. Si uso rellenar termino con 3500 en lugar de las 1500 ¿Alguna solución?"

La solución es muy sencilla. Para resolver este problema utilizaremos la herramienta Ir a Especial y Rellenar Series. Para hacer más manejable el ejemplo vamos a generar una lista del 1 al 20 ocultando 5 filas.

1. Procedemos a ocultar las filas número 11, 12, 13, 14 y 15 por ejemplo.

2. Nos situamos en la celda B2 y escribimos un uno.

3. Seleccionamos un rango que contenga 25 filas, ya que vamos a generar una lista del 1 al 20 pero hemos ocultado 5 filas. Para realizar la selección de filas podemos fijarnos en el cuadro de nombres (a la izquierda de la barra de fórmulas) ya que cuando procedemos a seleccionar un rango nos va indicando cuántas filas y columnas estamos seleccionando.

4. Vamos al menú Edición/Ir a y en la ventana que se abre pulsamos el botón Especial. En la ventana que se abre marcamos Sólo celdas visibles y aceptamos.

5. Vamos al menú Edición/Rellenar/Series. Se abrirá la siguiente ventana:

6. Seleccionamos Series en Columnas, ya que deseamos que la enumeración aparezca en columna; Tipo lineal; Incremento 1; y Límite 20 (ya que vamos a realizar una lista del 1 al 20).

7. Pulsamos Aceptar y problema resuelto (he puesto un relleno en azul en las celdas A10 y A16 para destacar que las filas 11 a 15 están ocultas):

Rellenar Celdas en Blanco con Contenido de Celda Anterior

"A menudo tengo que copiar parte de una tabla dinámica y el resultado (ejemplo reducido) es el que muestro en la imagen:

Necesito rellenar las celdas en blanco con los respectivos rótulos. Manejo más de 500 filas ¿hay alguna manera rápida de efectuar esta tediosa tarea?"

Como casi siempre, hay varias formas de realizar esta labor. Vamos a solucionarlo utilizando la herramienta Ir a Especial.

1. Seleccionamos el rango B2:B14 y vamos al menú Edición/Ir a y pulsamos el botón Especial. Se abrirá la siguiente ventana en la que debemos marcar el botón de opción Celdas en blanco:

Al pulsar Aceptar excel selecciona automáticamente las celdas en blanco del rango que teníamos seleccionado:

2. Sin modificar esta selección, escribimos la fórmula:

=B2 que es la primera celda en la que tenemos un rótulo.

3. Acabamos pulsando Ctrl + Enter y problema resuelto:

Copiar Registros Únicos

"Tengo una lista con 200 entradas de tres dígitos que representan el código de referencia de unos productos. En dicho listado hay códigos que se repiten en múltiples ocasiones. Lo que necesito es generar una lista con todos los códigos únicos".

Existen diversas maneras de resolver este problema. La solución que propongo a continuación (utilizar Filtro Avanzado) me parece la más rápida y sencilla. Supongamos que tenemos la lista que se muestra en la imagen:

Como se puede observar hay códigos que se repiten en varias ocasiones (el 108,118, 120, el 103, etc.). Debemos seguir los siguientes pasos:

1. Nos situamos en cualquier celda por debajo del rótulo, es decir, en cualquier celda del rango B3:B24 y vamos al menú Datos/Filtro/Filtro Avanzado.

2. Aparecerá la siguiente ventana:

Tal y como se muestra en la imagen, debemos ´marcar el botón de opción Copiar a otro lugar. Como Rango de la lista seleccionamos nuestro rango, es decir B2:B24. Rango de criterios lo dejamos en blanco ya que no existen en este caso concreto. En Copiar a marcamos la primera celda a partir de la cual queremos que aparezca nuestra lista con códigos no repetidos, por ejemplo seleccionamos D2. Finalmente marcamos la casilla de verificación Sólo registros únicos y pulsamos Aceptar.

3. El resultado de esta operación será el buscado:

Menú Contextual de Etiquetas de un Libro

"Tengo un archivo (libro) que contiene 14 hojas con sus correspondiente etiquetas. Cuando me quiero desplazar de una hoja a otra tengo que ir pulsando los botones para desplazar fichas ¿Existe alguna solución más cómoda y rápida para ver y desplazarse por las distintas hojas de un libro?"

Este es un problema (incomodidad, mejor dicho) tan habitual como fácil de resolver. Supongamos que tenemos un libro con distintas hojas:

La forma más habitual en la que los usuarios se desplazan entre las distintas hojas es la de ir pulsando los botones de desplazamiento de ficha (hoja o etiqueta):

La solución más rápida y cómoda para realizar esta tarea es muy sencilla pero, por desgracia, bastante desconocida. Sólo tiene que situarse encima de dichos botones (botones para desplazar fichas, situados a la izquierda de las etiquetas de las hojas) y hacer clic en el botón derecho del ratón (botón secundario del ratón). Automáticamente se abrirá un menú contextual con una lista de todas las hojas que tenga su libro. Para ir a cualquiera de ellas sólo tiene que seleccionarla en dicha lista:

Seleccionando Celdas con Fórmulas

Acaba de nacer mi ahijado Jacobo, hijo de mi queridísimo hermanito Javier y, por supuesto, de mi supercuñadita Emma.

¡¡ FELICIDADES PAPÁS !!

Aprovecho también para desearos a todos los que tenéis el humor y la paciencia de leerme que el 2010 haga honor a sus dos últimos dígitos. Y ahora a trabajar un poco...

" ¿Existe alguna manera de seleccionar todas aquellas celdas en las que haya fórmulas?"

Si necesitáis localizar todas aquellas celdas de vuestra hoja que contengan fórmulas sólo tenéis que utilizar la herramienta Ir a Especial. Supongamos que tenemos el modelo que se muestra en la imagen:

Necesitamos que excel seleccione las celdas que contienen fórmulas. Para ello vamos al menúEdición/Ir a. Se abrirá la siguiente ventana:

Pulsamos el botón Especial y aparecerá la siguiente ventana:

Pulsamos la tercera opción: Celdas con fórmulas y aceptamos. El resultado es el que se puede observar en la imagen. Excel ha seleccionado todas aquellas celdas que contienen una fórmula (en este ejemplo son las celdas C5, C8, B11 y el rango G3:G17)

Tipos de Fórmulas:

Dentro de la opción Celdas con Fórmulas podemos observar 4 subopciones, a saber: Números;Texto;Valores Lógicos; ó Errores. Por defecto aparecen los cuatro tipos marcados.

Números: Fórmulas que devuelven valores numéricos

Texto: Fórmulas cuyo resultado es una cadena de texto.

Valores Lógicos: Fórmulas cuyo resultado es un valor lógico, es decir, VERDADERO o FALSO.

Errores: Fórmulas cuyo resultado es algún tipo de error (#N/A, DIV/0, NOMBRE?, #¡REF!, etcétera).

Si necesitamos localizar las celdas que contienen fórmulas pero sólo aquellas que devuelvan un resultado concreto podemos marcar la opción que deseemos y excel hará el resto del trabajo.