Automatizar Textos-Datos de un Informe

Descargar el Archivo

En numerosísimas ocasiones en nuestra labor diaria utilizamos informes, plantillas, formularios... que contienen textos que a su vez contienen datos que introducimos de forma manual cuando, en realidad, podríamos ahorrarnos dicha tarea rutinaria que no aporta gran valor (más bien aporta errores cuando nos olvidamos de actualizar correctamente dichos datos). Por ejemplo, es muy habitual tener que introducir la "caducidad" de una oferta comercial; o el nombre del responsable de dicha oferta; etcétera. Veámoslo con un ejemplo sencillo. Supongamos que tenemos el siguiente formulario que, toda vez cubierto, enviamos al cliente que nos ha solicitado el presupuesto.

Los círculos naranja destacan aquella información que debemos introducir (fecha validez oferta; comercial responsable de la oferta; y porcentaje de iva a aplicar) y que, en muchas ocasiones, introducimos a mano cada vez que preparamos una oferta. Para automatizar estas tareas debemos hacer lo siguiente:

1. Planteamos la entrada de datos que se muestra a continuación:

2. Nos situamos en C2 y escribimos, por ejemplo, 15, con lo que indicamos que la vigencia de la oferta será de 15 días a contar desde hoy (desde la fecha de emisión de la propuesta).

3. Nos ponemos en C3 y vamos al menú Datos/Validación datos y en Permitir seleccionamos Lista y en el cuadro Origen le indicamos el rango G3:G7, que es donde tenemos la lista de comerciales de la empresa. Pulsamos Aceptar.

4. En C4 introducimos 16%, que será el tipo de iva aplicable.

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

=HOY()

Esta función no tiene argumentos porque simplemente nos proporciona la fecha del día en curso (que será la fecha desde la que comenzaremos a contar la vigencia de la propuesta).

Concluidos estos pasos tendremos lo siguiente:

6. Nos situamos en B8 y escribimos:

="Este presupuesto tiene validez hasta el "&TEXTO(C6+C2;"dd/mm/aaaa")

Fíjese que el texto inicial, "Este presupuesto tiene validez hasta el ", está entre comillas. Esto es necesario porque vamos a concatenar más elementos a dicho texto. La segunda parte de la fórmula, &TEXTO(C6+C2;"dd/mm/aaaa") concatena al texto el cálculo de la suma de 15 días naturales respecto a la fecha en curso. Como ya comentamos en el artículo "Cálculos Simultáneos en una Celda", al utilizar la función CONCATENAR perdemos los formatos específicos y excel aplica el formato General. En este ejemplo, si no utilizáramos la función TEXTO, el resultado devuelto sería: Este presupuesto tiene validez hasta el 39991. Para dar el formato correcto a este número (39991), que es el número de serie correspondiente a la fecha 27/06/2009, introducimos la función TEXTO.

7. Nos situamos en la celda B9 y escribimos la fórmula:

="Comercial responsable: "&C3

8. Nos situamos en la celda D20 y escribimos:

="iva "&TEXTO(C4;"0%")

De esta manera si cambiamos el porcentaje de iva a aplicar en la celda C4 el texto de D20 se actualizará automáticamente.

Cálculos Simultáneos en una Celda

Descargar el Archivo

Hoy vamos a ver cómo se pueden realizar cálculos simultáneos en una única celda. Supongamos que tenemos los ingresos y gastos de diversas áreas de un negocio y queremos calcular el margen y el porcentaje que representa dicho margen sobre los ingresos. Pero queremos que muestre ambos resultados (el margen en Euros y el porcentaje sobre ingresos) en la misma celda.

Partiendo del ejemplo mostrado en la imagen, queremos realizar en E4 los cálculos citados. Para ello necesitaremos utilizar la función CONCATENAR o, lo que es lo mismo pero más corto, &. Nos situamos en E4 y escribimos la siguiente fórmula:

=C4-D4&" "&(C4-D4)/C4

C4-D4 calcula el margen en euros

&" " une al resultado calculado tres espacios en blanco (para separar del siguiente cálculo)

&(C4-D4)/C4 une a todo lo anterior el cálculo del porcentaje que supone el margen sobre el ingreso.

El resultado de esta fórmula será:

Aunque el resultado numérico es correcto, creo que estará de acuerdo conmigo en que el formato presentado no es el más conveniente... Cuando realicemos cálculos y utilicemos la función CONCATENAR nos encontraremos con este problema de formatos. Para solucionarlo podemos acudir a la función TEXTO.

TEXTO(Valor;Formato)

Esta función convierte un valor en texto con un formato determinado. El argumento Valor es un valor numérico o una fórmula que nos proporciona un valor numérico. El argumento Formato es el formato que queremos darle al Valor en cuestión. El formato elegido debemos introducirlo entre comillas y podemos indicar cualquiera de las posibilidades que se pueden encontrar en el menú Formato/Celdas/Numero.

Nos situamos nuevamente en E4 y procedemos a aplicar esta función a nuestra fórmula inicial:

=TEXTO(C4-D4;"0.00€")&" "&TEXTO((C4-D4)/C4;"0,00%")

Fíjese que hemos añadido la función TEXTO para conseguir que el primer cálculo aparezca con formato de moneda (0.00€) y el segundo cálculo con formato porcentual (0,00%). Una vez hecha esta fórmula y copiada en el rango E5:E11 el resultado será:

Si queremos añadir el margen total y el porcentaje sobre los ingresos totales deberemos realizar una nueva fórmula en E12. A saber:

=TEXTO(SUMA(C4:C11)-SUMA(D4:D11);"0.00€")&" "&TEXTO((C12-D12)/C12;"0,00%")

Cálculo de Días Laborables

Descargar el Archivo

Hoy vamos a ver un pequeño modelo para calcular la fecha de incorporación al trabajo después de unas benditas vacaciones. Para ello utilizaremos la función DIA.LAB

Comenzamos creando la entrada de datos:

Como se puede observar, en la celda C3 he introducido un Control de número que nos permitirá incrementar o disminuir el número de días laborables para nuestro cálculo (puede consultar cómo introducir dicho Control de número en el artículo Análisis de Sensibilidad con Controles de Número).

Lo que queremos conseguir es automatizar el cálculo de la fecha en la que deberíamos incorporarnos después de, por ejemplo, disfrutar de un periodo de vacaciones. Para ello tendremos que tener en cuenta los sábados y domingos y los días festivos. La función DIA.LAB nos ayudará en esta labor. 

Nota: La función DIA.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.

DIA.LAB(Fecha_inicial;días_lab;festivos)

Esta función devuelve el número de serie de una fecha anterior o posterior a la fecha inicial según determinemos el número de días laborables. DIA.LAB considera los sábados como no laborables.

Como se puede adivinar, observando los argumentos de esta función, 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):

Una vez preparado el listado sólo tenemos que hacer lo siguiente:

1. Seleccionamos el rango E2:E25 y vamos al menú Insertar/Nombre/Crear y seleccionamos Crear nombre en Fila superior. De esta manera nuestra lista (rango) de días festivos pasará a denominarse Fiestas.

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

=DIA.LAB(C2;C3;Fiestas)

De esta manera le estamos pidiendo que a la fecha inicial 21/12/2009 le añada 12 días laborables. DIA.LAB tiene en cuenta como no laborable tanto la fecha inicial como el último día que calcula, por lo que nos devuelve la fecha inmediata posterior, que es la correcta. En nuestro ejemplo nos devuelve el 11 de enero de 2010, que sería la fecha de incorporación (no el último día de vacaciones). Puede añadir una salida de datos de Días Naturales transcurridos (=B6-C2) para ver la diferencia: 

Una función similar a la vista es DIAS.LAB

Esta función calcula el número de días laborables transcurridos entre dos fechas indicadas. Su sintaxis es: DIAS.LAB(Fecha_inicial;Fecha_final;Festivos)

Pruebe a calcular con esta función el número de días laborables transcurridos entre el 21/12/2009 y el 11/01/2010 y comprobará que le devuelve precisamente 12.

Reorganizar Listados de Nombres y Apellidos

Una pregunta que me habéis realizado de manera recurrente es si se puede "trocear" en distintas celdas un listado de nombres y apellidos en el que tenéis dichos nombres y apellidos en celdas únicas. La solución es muy sencilla pero deduzco que poco conocida por el número de veces que he tenido que contestar esta pregunta... 

Existen varias formas (además de hacerlo a mano...) de solucionar este problema. En este caso vamos a resolverlo utilizando la herramienta de Texto en Columnas. Una vez concluyamos el "troceado" trabajaremos en el "reorganizado" de los nombres y apellidos.

A) TROCEADO

 Supongamos que tenemos el siguiente listado de empleados:

Queremos conseguir separar los nombres y apellidos en distintas columnas. Para ello haremos lo siguiente:

1. Seleccionamos el rango A4:A9 y vamos al menú Datos/Texto en columnas... Se abrirá la siguiente ventana:

2. Como se muestra en la imagen, en Tipo de los datos de origen seleccionamos la opción Delimitados y pulsamos Siguiente. Aparecerá esta ventana:

3. El tipo de Separador que tenemos en nuestro listado es Espacios, por lo que marcamos dicha casilla de verificación. Además, dejamos marcada la opción Considerar separadores consecutivos como uno solo. Esta opción permitirá que si existe más de un espacio entre nombres y apellidos los considere -los espacios- como uno solo, evitando así que inserte columnas en blanco inútiles. Pulsamos Siguiente y se abrirá esta ventana:

4. en el cuadro Destino seleccionamos la celda a partir de la cual queremos que aparezcan separados en columnas los nombres y apellidos de nuestro listado (en nuestro ejemplo B4). Si no modificamos esta opción Excel sobreescribirá el resultado sobre nuestro listado original. Pulsamos Finalizar y obtenemos el siguiente resultado:

B) REORGANIZACIÓN

Vamos a ver ahora cómo podemos reorganizar, en función de nuestras necesidades, estos nombres y apellidos que hemos separado en distintas columnas.

Supongamos que queremos conseguir el formato: Apellidos, Nombre/s. El primer problema que nos vamos a encontrar a la hora de realizar la fórmula es que existen registros con dos nombres (María Teresa) y registros con nombre único (Ignacio). Empezamos solucionando, para facilitar la comprensión, los registros con nombre único:

1. Nos situamos en F5 y escribimos la fórmula:

=C5&" "&D5&", "&B5

Fíjese que hemos utilizado la función CONCATENAR -&- (ya explicada en artículos anteriores que puede localizar en las Etiquetas de Funciones y Herramientas de este blog) para unir los apellidos y el nombre. Además hemos dejado espacios entre estos y una coma antes del nombre. El problema de esta fórmula, como ya advertí, es que sólo nos valdrá para los registros con un único nombre.

2. Para los registros con dos nombres la fórmula a aplicar sería (nos situamos en F4):

=D4&" "&E4&", "&B4&" "&C4

Resueltos los dos casos posibles sólo nos queda detectar cuál debemos aplicar a cada caso. La forma es muy sencilla. En el rango E4:E9 sólo habrá contenido en los casos en los que tengamos dos nombres. Así las cosas sólo tenemos que comprobar si E4 se encuentra vacío o no para aplicar una u otra fórmula. Nos situamos en F4 y escribimos la siguiente fórmula (que luego copiaremos hasta F9):

=SI(E4="";C4&" "&D4&", "&B4;D4&" "&E4&", "&B4&" "&C4)

Si queremos actuar sobre este listado (por ejemplo ordenándolo alfabéticamente) es conveniente convertir en valores las fórmulas realizadas. Para ello seleccione el rango F4:F9 pulse el icono de Copiar y vaya al menú Edición/Pegado especial y seleccione la opción Valores.

Valores más Frecuentes (Moda) de un Rango

Descargar el Archivo

En contestación a una consulta que me habéis realizado, vamos a ver cómo podemos calcular el valor más frecuente (el valor que más se repite) en un rango o matriz o, lo que es lo mismo, la MODA, y destacar dicho valor o valores haciendo uso de nuestro buen amigo el Formato Condicional.

Supongamos que tenemos una tabla donde recogemos el resultado de 20 encuestas realizadas a nuestros clientes donde nos valoran de 1 a 10 en tres categorías, a saber: atención al cliente, servicio prestado y servicio postventa. Los resultados son los siguientes:

Con la información dada queremos destacar para cada columna la Moda, es decir, el valor que más se repite (en cada columna). Para ello seguimos los siguientes pasos:

1. Seleccionamos el rango B3:D23 y vamos al menú Insertar/Nombres/Crear y seleccionamos Nombre en la Columna Superior.

2. Seleccionamos el rango B4:D23 y vamos al menú Formato/Formato condicional. En el primer cuadro seleccionamos Fórmula y en el cuadro de la derecha escribimos la siguiente fórmula:

=SI(B4=MODA(INDIRECTO(B$3));1;0)

La función MODA calcula el valor más repetido dentro de un rango. Si, por ejemplo, se coloca en B26 y escribe =MODA(B4:B23) le devolverá el valor 7, ya que es el que más veces aparece en dicho rango. En nuestro caso hemos creado nombres para los rangos Atención, Servicio y Postventa. Para no tener que realizar varias fórmulas, en función del número de rangos independientes que queramos analizar, incorporamos la función INDIRECTO referenciada a B$3 (para que al copiar a la derecha no modifique la referencia de la columna pero sí la referencia de fila si copiamos hacia abajo)

3. Pulsamos el botón Formato y elegimos Trama naranja; Bordes Contorno y Fuente color negro, por ejemplo. Pulsamos Aceptar.

Fíjese que automáticamente Excel calcula la MODA y además la sombrea de naranja.

4. Si queremos además calcular la calificación promedio podemos utilizar el truco visto con la función SUMA de seleccionar el rango B4:D23 , abrir el icono de autosuma y seleccionar la función PROMEDIO. De esta manera Excel calculará la nota media para cada columna y la dispondrá después de la última fila cubierta (colocará el promedio en la fila 24).