Buscar Dentro de Fórmulas

Descargar el Archivo

"Tengo una hoja con muchos datos y con cálculos de sumatorios parciales dispersos por dicha hoja. Además de dichos sumatorios tengo otras fórmulas. Necesito localizar todas las celdas en donde existe una fórmula que esté utilizando la función SUMA, ya que debo revisar que los rangos aplicados en esta función sean correctos".

La solución a este problema es muy sencilla utilizando la herramienta Buscar. Para ello vamos a utilizar el siguiente ejemplo:

Se trata de una tabla en la que tenemos diversos datos y diversas fórmulas y lo que pretendemos es localizar aquellas que contengan la función SUMA. Para ello seguimos los siguientes pasos:

1. Vamos a l menú Edición/Buscar. Se abrirá la siguiente ventana:

2. Dentro del campo Buscar: escribimos la palabra SUMA y comprobamos que dentro de del campo Buscar dentro de: tenemos seleccionado Fórmulas. Pulsamos el botón Buscar todo. El resultado será el siguiente:

Una vez resuelto el problema planteado en esta consulta, tenemos muchas opciones para trabajar. Lo primero que cabe destacar es que esta herramienta nos presenta un listado de todas las celdas que contienen la función buscada con su fórmula correspondiente. De esta manera ya podemos comprobar desde la propia herramienta si el rango de SUMA es el que nos interesa. Además podemos hacer clic en cada uno de los resultados de la búsqueda y excel selecciona automáticamente la celda a la que hace referencia:

En esta imagen puede comprobar que al seleccionar el primer resultado de la búsqueda excel selecciona la celda en la hoja a la que hace referencia (celda B11).

También podemos realizar una selección múltiple marcando, por ejemplo, el primer resultado de la búsqueda y haciendo clic con la tecla Shift pulsada en el último resultado de la búsqueda:

Como puede comprobar en la imagen excel selecciona las tres celdas implicadas, a saber: B11, C11 y E11.

Finalmente también puede realizar una selección discontinua pulsando la tecla Ctrl y haciendo clic en los resultados de la búsqueda que le interese (por ejemplo el primero y el último):

Evidentemente, las opciones de esta herramienta son mucho más amplias y, por lo tanto, serán objeto de futuros posts.

Días Laborables Entre Dos Fechas

Descargar el Archivo

"Necesito calcular los días laborables transcurridos entre dos fechas ¿Hay alguna función que lo calcule?"

Sin problema. Resolveremos esta cuestión con la función DIAS.LAB

Partimos del siguiente ejemplo:

Queremos que en la celda C6 aparezca la diferencia de los días laborables transcurridos entre la fecha inicial indicada en la celda C2 y la fecha final indicada en la celda C4. Aplicando la función DIAS.LAB la solución es sencilla.

Nota: La función DIAS.LAB no aparece por defecto en la categoría Fecha y Hora de Excel (en versiones anteriores a Excel 2007). Para añadirla debe hacer lo siguiente: vaya al menú Herramientas/Complementos y active la casilla de verificación Herramientas para análisis. Pulse Aceptar y nuevas funciones, incluida la que nos ocupa, le aparecerán en las distintas categorías.

La sintaxis de esta función es:

=DIAS.LAB(Fecha inicial;Fecha final;Festivos)

Es importante destacar que la función DIAS.LAB considera los sábados y domingos como no laborables. Por otro lado, vamos a necesitar un listado de los días festivos del periodo a analizar. En nuestro ejemplo hemos introducido una lista de las fechas festivas en 2009 y 2010 (calendario que debe actualizarse y completarse con festivos locales):

Para que excel nos advierta si introducimos incorrectamente una fecha vamos a utilizar la herramienta de Validación de datos y la función lógica SI.

1. Seleccionamos el rango B16:B38 y le damos el nombre Fiestas (haciendo clic en el cuadro de nombres -a la izquierda de la barra de fórmulas- y escribiendo directamente dicho nombre y pulsando después Enter)

2. Nos situamos en la celda C6 y escribimos la fórmula:

3. Nos situamos en la celda C4 y vamos al menú Datos/Validación y realizamos la configuración que se muestra en las siguientes imágenes:

De esta manera además de obtener el cálculo que estábamos buscando:

si introducimos incorrectamente la fecha final excel nos advertirá:

Generar Agenda de Tareas (Gráficos Gantt)

Durante esta última semana he tenido varias consultas relativas a la generación de gráficos para el control de agendas de proyectos, o lo que es lo mismo, gráficos Gantt. Excel no presenta este tipo de gráficos por defecto pero resulta bastante sencillo conseguirlo siguiendo unos pasos...

Partimos del siguiente ejemplo donde podemos ver en la columna B la tarea; en la columna C la fecha de inicio de dicha tarea; y en la columna D la duración en días desde la fecha de inicio de cada tarea:

1. Seleccionamos el rango B2:D8 y procedemos a insertar gráfico tipo barras horizontales; subtipo barras apiladas:

2. Pulsamos directamente Finalizar. El gráfico obtenido será el siguiente:

3. Ya tenemos la base para empezar a trabajar... Lo que debemos hacer a continuación es un clic con el botón derecho del ratón sobre el eje de las Y (eje vertical) y seleccionar Formato de ejes...

4. En la pestaña Escala marcamos la opción Categorías en orden inverso. De esta manera excel colocará las tareas en el orden correcto (no como las presentaba en el gráfico anterior) y las fechas en la parte superior del gráfico. Hacemos clic encima de la leyenda y suprimimos:

5. Hacemos doble clic encima de la serie de datos de color azul, que se refiere a las fechas de inicio, y le quitamos el relleno y el borde:

6. Hacemos doble clic encima de las fechas que se encuentran en la parte superior del gráfico, para darles formato, y seleccionamos en la ventana que se abre la pestaña Escala. Excel no reconoce aquí los formatos de fecha, por lo que debe utilizar el formato general. Para hacer esto debe volver a la hoja y situarse, por ejemplo en la celda F3. En esta celda copie la fecha inicial de la primera tarea y vaya a Formato/Celda/Número y seleccione la opción General. Póngase en F4 y copie la fecha de inicio del último proyecto más el número de días de duración (en nuestro ejemplo la última fecha es el 7/8/2010 y la duración de esta última tarea es de 7 días, por lo que la fecha final será el 14/8/2010) y haga lo mismo que en el anterior caso. De esta manera obtendremos los números de serie que corresponden a estas dos fechas que nos interesan (en concreto el 40369 y el 40404 ¿Para qué? Pues volviendo a nuestra ventana de Formato de ejes en la pestaña Escala debemos escribir como Mínimo el 40369 (que es la fecha de inicio de la primera tarea) y como Máximo el 40404 (que es la fecha de finalización de la última tarea):

Y con algunas mejoras de formato (color de fuentes, color de lineas de división, etc) conseguimos un sencillo gráfico Gantt como el mostrado a continuación:

Búsquedas de Derecha a Izquierda

Descargar el Archivo

"Tengo una tabla con diversas columnas, cuyo orden no puedo modificar, y necesito realizar una búsqueda en la segunda columna y que me devuelva lo que contiene la primera columna. No puedo utilizar BUSCARV porque esta función realiza la búsqueda de izquierda a derecha y yo necesito lo contrario".

Para solucionar este problema utilizaremos las funciones INDICE y COINCIDIR. Partimos del siguiente ejemplo:

En la tabla de la figura nos encontramos con que el código de empleado se encuentra en la columna C. Lo que queremos es introducir uno de estos códigos en la celda C2 y que en C4 nos aparezca el nombre del empleado en cuestión (búsqueda de derecha a izquierda). Para ello seguimos los siguientes pasos:

1. Nos situamos en C2 y vamos al menú Datos/Validación. Seleccionamos Permitir Lista y en Origen marcamos el rango C9:C16, que es donde tenemos los códigos que queremos que aparezcan en la lista. Pulsamos Aceptar.

2. Nos situamos en la celda C4 y escribimos la fórmula:

=INDICE(B9:B16;COINCIDIR(C2;C9:C16;0))

La parte de la fórmula COINCIDIR(C2;C9:C16;0) nos proporciona el número de fila en el que se encuentra el contenido de C2 dentro del rango C9:C16. Si seleccionamos el código G17 en la celda C2 y probamos a introducir este "trozo" de fórmula en una celda, por ejemplo en C6, veremos que nos proporcionará el resultado 7. Esto es debido a que el código G17 se encuentra en la fila 7 del rango C9:C16. Incorporando este resultado como segundo argumento de la función INDICE ya obtendremos la solución deseada (búsqueda de derecha a izquierda):

Consolidar Informes

"Habitualmente recibo resúmenes de ventas trimestrales de más de 12 comerciales distintos ¿Existe alguna manera de que excel "junte" toda la información en una tabla sin que yo tenga que andar copiando/pegando?"

Sí, existe: la herramienta Consolidar. Partamos como siempre de un ejemplo. Supongamos que tenemos 4 comerciales, a saber, Carlos, Javier, José y Santiago, y que cada uno nos remite un archivo de excel donde indica su cifra de ventas por trimestres de los distintos productos que componen su cartera. En las siguientes imágenes vemos esta información:

Queremos consolidar toda esta información en un documento que denominaremos TOTALES. Para ello debemos seguir los siguientes pasos:

1. Desde el archivo TOTALES vamos al menú Datos/Consolidar. Aparecerá la siguiente ventana:

2. Elegimos la función que queremos utilizar en nuestra consolidación (Suma, Cuenta, Promedio, etc). En nuestro caso utilizaremos la SUMA.

3. En el apartado Referencia debemos seleccionar el primero de los rangos que nos interesa. Para ello pulsamos el icono con la flechita roja, nos vamos a la hoja llamada Ventas Carlos y seleccionamos el rango A6:E12 . En pantalla aparecerá la ruta completa:

4. Pulsamos Enter y veremos lo siguiente:

5. Pulsamos el botón Agregar. Una vez hecho esto la referencia aparecerá en el cuadro Todas las referencias.

6. Volvemos a pulsar el icono para seleccionar un nuevo rango en el campo Referencia. En esta ocasión nos dirigimos a la hoja llamada Ventas Javier y seleccionamos el rango A5:E8 . Al igual que en el caso anterior excel mostrará la ruta completa. Pulsamos Enter y después el botón Agregar. A estas alturas podremos ver en pantalla lo siguiente:

7. Repetimos los pasos anteriores para agregar las hojas de venta de José y de Santi:

8. Mantenemos marcadas las opciones de Usar rótulos en / Fila superior y Columna izquierda. Esto es debido a que tenemos rótulos en la fila superior (los trimestres) y rótulos en la columna izquierda (los productos). Finalmente decidimos si dejamos marcada la opción Crear vínculos con los datos de origen. Si quitamos la marca excel no guardará los vínculos y se limitará a realizar la consolidación "pegando valores". Por el contrario, si mantenemos marcada esta opción, excel generará un esquema con la información de dónde proviene cada dato y además mantendremos vinculada la hoja TOTALES con el resto de las hojas, de tal manera que si modificamos cualquier dato en dichas hojas (dentro de los rangos considerados para la consolidación) se actualizará automáticamente la hoja TOTALES. Pulse el botón Aceptar y el resultado obtenido será este:

Como puede comprobar excel ha generado un esquema con la información consolidada. Pulse el nivel 2 de información y obtendrá el máximo detalle: