Calcular el tiempo transcurrido

Calcular la diferencia entre dos fechas en Excel es algo muy sencillo. En este artículo mostraré cómo calcular ese tiempo transcurrido entre ambas fechar y presentar el resultado en diferentes unidades de tiempo como años, meses, semanas, etc.

Supongamos que tenemos la fecha de nacimiento de una persona y queremos calcular el tiempo que ha vivido hasta el día de hoy. Observa la siguiente imagen:

Tiempo transcurrido en días

Puede observar que la línea que se encuentra sombreada es la de los días transcurridos y la razón principal es porque esa es la operación más sencilla de todas. Para obtener la cantidad de días entre dos fechas es suficiente con realizar una resta entre ambas. En este ejemplo la fórmula utilizada es:

=B3-B2

Excel regresar el tiempo transcurrido en días utilizando decimales y por motivos de presentación se los he removido, sin embargo tú puedes dejar el formato con decimales si así lo deseas.

Calcular las horas, minutos y segundos

El siguiente paso natural es calcular las horas, lo cual es sumamente sencillo una vez que tengo el número de días. Solamente debo multiplicar el resultado en días por 24:

=A8 * 24

Y una vez que tengo las horas puedo obtener los minutos haciendo una multiplicación por 60 que es la cantidad de minutos en una hora:

=A9 * 60

Finalmente, y como has de suponer, para obtener los segundos multiplico el resultado anterior de nuevo por 60:

=A10 * 60

De esa manera obtengo los segundos transcurridos entre ambas fechas.

Tiempo en semanas

Como puedes observar el cálculo de días es la parte central para el resto de cálculos y las semanas no son la excepción ya que las podemos obtener fácilmente realizando una simple división:

=A8 / 7

Días laborables

El cálculo de días laborales entre dos fechas ya no lo podemos realizar con una simple operación aritmética, pero afortunadamente existe la función DIAS.LAB la cual realiza el cálculo por nosotros, solamente es necesario especificar ambas fechas como los argumentos de la función y Excel nos devolverá el resultado correcto:

=DIAS.LAB(B2, B3)

Meses y años

Obtener los meses es una tarea un tanto más complicada, porque los meses no tienen la misma cantidad de días. Lo mismo sucede con los años ya que tenemos los años bisiestos los cuales tienen un día adicional.

Sin embargo Excel provee de una función que nos ayuda con este cálculo, pero debo advertirte que ésta no es una función tradicional, de hecho no la podrás encontrar en la documentación de Excel y sin embargo la podremos utilizar. La función es conocida como la función SIFECHA y aunque en este artículo no entraré en los detalles de ésta función observa cómo me ayuda a obtener los meses transcurridos entre dos fechas:

De una manera similar, esta función obtiene los años transcurridos:

Campos calculados en tabla dinámica

Las tablas dinámicas nos permiten hacer uso de campos calculados los cuales son columnas que obtienen su valor de la operación realizada entre algunas de las otras columnas existentes en la tabla dinámica.

Supongamos la siguiente tabla dinámica en donde tenemos el resumen de las ventas de nuestros productos para el año pasado.

Ahora queremos insertar una nueva columna que contendrá nuestro pronóstico de ventas para el próximo año y el cual hemos estimado que tendrá un crecimiento del 4%. Esta nueva columna la podemos agregar como un campo calculado a la tabla dinámica de la siguiente manera.

Dentro del título Herramientas de tabla dinámica selecciona la ficha Opciones y posteriormente el botón Cálculos el cual desplegará varias opciones y deberás seleccionar Campos, elementos y conjuntos para finalmente seleccionar la opciónCampo calculado.

Se mostrará el cuadro de diálogo Insertar campo calculado en donde podremos especificar un Nombre para nuestro campo.

Además del nombre del campo debemos especificar la Fórmula que Excel utilizará para realizar el cálculo de la nueva columna. Puede observar un recuadro donde aparecen en listados todos los Campos de la tabla dinámica de manera que al seleccionar alguno de ellos y presionar el botón Insertar campo se insertará su nombre en la fórmula. Una vez que hayas terminado de configurar el nuevo campo calculado pulsa el botón Aceptar y Excel insertará el campo.

Al actualizar los datos de la tabla dinámica los campos calculados reflejarán automáticamente los nuevos cálculos. Recuerda que un campo calculado es una nueva columna que ha sido creada realizando operaciones con los campos existentes de una tabla dinámica.

Conexiones a datos externos

Los datos con los que trabajamos en nuestros libros de Excel provienen solamente de dos lugares: están almacenados en el mismo libro o están almacenados en una fuente de datos externa como un archivo texto o una base de datos.

Conexión de datos

En artículos anteriores hemos visto cómo importar datos desde texto, desde una base de datos Access, desde una página web e inclusive desde otras fuentes de datos como archivos dBASE. Aunque cada uno de estos ejemplos es un repositorio de datos diferentes, al momento de importar la información a Excel todos tendrán una cosa en común: una conexión de datos.

El objetivo de la conexión de datos es mantener conectados a Excel con la fuente de datos externa de manera que esté informado sobre su ubicación exacta. De igual manera en ocasiones nos conectaremos a fuentes externas que requerirán un usuario y contraseña por lo que  dicha información permanecerá almacenada en la misma conexión de datos de manera que Excel puede conectarse de nuevo sin problema alguno.

El beneficio de una conexión de datos

El gran beneficio de una conexión de datos es que una vez que hemos especificado la información de conexión la primera vez, no tendremos que hacerlo de nuevo para volver a traer los datos a Excel sino que será suficiente con pulsar el botónActualizar todo para poder traer la información actualizada de la fuente de datos externa.

Conexiones existentes

Para encontrar las conexiones existentes puedes pulsar el botón Conexiones que se encuentra en el grupo Conexiones  de la ficha Datos. Al pulsar el botón se mostrará el cuadro de diálogo Conexiones del libro.

La lista de conexiones estará formada por aquellas conexiones que hayas creado previamente. Si has creado una conexión a un archivo de texto, a una base de datos Access o a una base de datos SQL Server, todas las conexiones estarán en listadas en este cuadro de diálogo.

Procedimiento de conexión

Aunque existe una amplia variedad de fuentes de datos externas, al momento de conectarnos a ellas seguimos los mimos pasos:

1. Elegimos la fuente de datos externa que puede ser un archivo de texto, una base de datos SQL Server, un archivo XML, etc. Algunas de las fuentes tendrán requerimientos de conexión específicos.
2. En base a los requerimientos de conexión Excel nos solicitará la información pertinente para encontrar la fuente de datos y conectarse.
3. Una vez proporcionados los datos de conexión se importarán los datos a una hoja de Excel y la información de la conexión será guardada en el libro para futuras actualizaciones.

Cómo quitar o poner ceros a la izquierda en Excel

Cuando tenemos datos en Excel que han sido importados de algún otro sistema es común tener datos numéricos que tienenceros a la izquierda así que en esta ocasión veremos un par de alternativas para remover esos dígitos adicionales y obtener el valor numérico de los datos.

Cómo quitar ceros a la izquierda

Como ejemplo tomaremos los datos mostrados en la siguiente imagen donde todos los valores tienen 6 dígitos y los primeros tres son ceros. Nuestro objetivo será remover los ceros a la izquierda de los valores en el rango A1:A10.

Observa que los valores de las celdas están alineados a la izquierda, lo cual indica que Excel los ha identificado como texto y no como valores numéricos. Algunos pensarían que cambiando el formato de la celda se solucionaría el problema, pero eso no funcionará, así que es necesario aplicar una transformación a los datos.

Quitar ceros con el Pegado especial

Para quitar ceros a la izquierda utilizando el Pegado especial es necesario llenar una columna con números 1 y en seguida copiar los valores originales.

Después debemos hacer clic derecho sobre la celda B1 y elegir la opción Pegado especial que mostrará el siguiente cuadro de diálogo.

En este cuadro de diálogo debemos seleccionar la opción Multiplicar y al hacer clic en el botón Aceptar tendremos el siguiente resultado:

Con esta acción hemos quitado los ceros, pero Excel sigue alineando a la izquierda los valores de las celdas. Eso se corregirá fácilmente cambiando el formato de las celdas a General o Número.

Remover ceros con la función VALOR

La segunda alternativa que tenemos para quitar ceros a la izquierda es utilizar la función VALOR la cual obtiene el valor numérico que está representado como texto. Ya que Excel descarta los ceros a la izquierda para los números, al utilizar esta función habremos eliminado los ceros a la izquierda. En la siguiente imagen puedes ver el resultado de utilizar esta función:

Excepción: Ceros a la izquierda con texto

Los métodos que he mencionado hasta ahora funcionan perfectamente cuando todos los caracteres de la cadena de texto son números,  pero si agregamos una sola letra entonces ninguno de los métodos funcionará. Por ejemplo, si tuviéramos el valor “000115X” el método de Pegado especial devolverá el valor 1 y la función VALOR nos devolverá el error #¡VALOR!

Si te encuentras en una situación de este tipo tienes que extraer primero la parte numérica del texto y para lograrlo puedes consultar los métodos mostrados en cualquiera de los siguientes dos artículos:

• Extraer números de una celda en Excel
• Macro para extraer números de una celda

Una vez extraídos los números podrás aplicar los métodos mostrados en este artículo para eliminar los ceros a la izquierda.

Cómo poner ceros a la izquierda

Ahora invertiremos el proceso en el que hemos trabajado hasta ahora y analizaremos las alternativas que tenemos para agregar ceros a la izquierda de un valor numérico. Tal vez te preguntarás en qué casos sería necesario hacer tal cosa en Excel, y te puedo decir que yo lo he visto cuando necesitamos preparar datos que serán enviados a un sistema externo el cual nos obliga a tener una cantidad específica de posiciones para los valores numéricos.

También he visto que en ocasiones se exige el relleno con ceros para cumplir con la presentación de valores en algún reporte. Y precisamente con este método es con el que iniciaremos, es decir, un método que nos permita rellenar con ceros a la izquierda de un número pero solo para su presentación.

Agregar ceros con el Formato de celdas

Iniciamos seleccionando el rango que contiene los valores numéricos y hacemos clic derecho para seleccionar la opción Formato de celdas lo cual mostrará el siguiente cuadro de diálogo:

Seleccionamos la opción Personalizada y dentro del cuadro de texto Tipo colocaremos tantos ceros como posiciones de dígitos necesitamos en los datos. En el ejemplo he colocado 6 ceros para que todos los valores numéricos tengan seis posiciones y las faltantes sean rellenadas con ceros. Al hacer clic en el botón Aceptar obtengo el siguiente resultado:

Observa que la celda A1 despliega “000115” pero la barra de fórmulas nos indica que el valor de dicha celda sigue siendo 115. Es por eso que este método afecta solamente la presentación de los datos y no su valor.

Rellenar con ceros usando la función TEXTO

La alternativa que tenemos para modificar directamente el valor de la celda es aplicar el mismo formato utilizado en el método anterior pero a través de la función TEXTO la cual nos ayuda a convertir un valor numérico en texto aplicando un formato específico. Observa el resultado de utilizar esta función sobre los datos de ejemplo:

La función aplica el formato indicado a cada valor numérico y lo convierte en texto. Podemos saber fácilmente que los valores de la columna B son texto porque Excel los ha alineado a la izquierda de la celda. Es así como podemos rellenar con ceros a la izquierda en Excel utilizando la función TEXTO.

Referencias 3D

Una referencia nos ayuda a identificar una celda o rango de celdas y de esta manera podemos indicar a Excel el lugar exacto donde buscar los valores o los datos que deseamos utilizar. Hoy hablaré de manera especial de las referencias 3D en Excel.

Referencias de celdas tradicionales

Para comprender mejor el beneficio de las referencias 3D, hagamos un ejemplo utilizando referencias de celdas de la manera tradicional. Supongamos que tengo un libro de Excel con 12 hojas, una para cada mes del año y dentro de cada hoja, en la celda B2, tengo el dato del total de ventas del mes.

Ahora deseo crear una nueva hoja que tendrá la suma de las celdas B2 de todas las hojas. Para obtener esta suma podría sumar cada una de las celdas B2 de todas las hojas de la siguiente manera:

Con esta fórmula obtendré la suma adecuada, sin  embargo me tardaré algunos segundos en construir la formula y probablemente cometeré algún error al introducirla. Para evitar cualquier problema, podemos hacer esta misma suma de una manera mucho más rápida utilizando una referencia 3D.

Crear una referencia 3D en Excel

Cuando deseamos utilizar la misma celda de varias hojas podemos crear una referencia 3D. Para nuestro ejemplo la referencia 3D para las celdas B2 de todas las hojas de los meses la formamos de la siguiente manera:

ENE:DIC!B2

La referencia 3D indica a Excel que debe considerar todas las hojas de Excel comprendidas entre la hoja ENE y la hoja DIC y tomar el valor de la celda B2 de todas ellas. Al indicar esta referencia como el argumento a la función SUMA obtendremos el resultado deseado:

En este ejemplo he utilizado una referencia 3D para indicar una sola celda, sin embargo las referencias 3D también se pueden utilizar con rangos de celdas.

Crear una tabla dinámica desde múltiples hojas

Al crear una tabla dinámica generalmente tenemos todos nuestros datos en una sola hoja, sin embargo es posible crear una tabla dinámica a partir de varias hojas de Excel. En esta ocasión te mostraré como crear una sola tabla dinámica basada en múltiples hojas.

Tabla dinámica desde múltiples hojas

La condición necesaria para crear una tabla dinámica desde múltiples hojas es que cada una de las tablas de datos de las diferentes hojas debe tener la misma cantidad de columnas. Los datos pueden variar, pero los encabezados de las tablas deben ser los mismos. Para nuestro ejemplo supondremos que tengo dos hojas. La primera contiene los datos de las ventas para la zona Norte y la segunda hoja las ventas de la zona Sur.

Crear la tabla dinámica

La manera más sencilla de crear este tipo de tabla dinámica es utilizar el Asistente para tablas y gráficos dinámicos el cual podemos iniciar con la combinación de teclas ALT+T+B.

En el cuadro de diálogo selecciona la opción Rangos de consolidación múltiples y pulsa el botón Siguiente. En el segundo paso del asistente selecciona la opción Crear un solo campo de página.

Al pulsar el botón Siguiente se mostrará un paso intermedio llamado paso 2b el cual nos permitirá seleccionar los rangos de las diferentes hojas.

Para agregar un rango debes seleccionarlo directamente de la hoja y posteriormente pulsar el botón Agregar. Esta acción se debe repetir para todos los rangos que se desees consolidar. Al terminar de agregar todos los rangos pulsa el botón Siguiente para ir al paso final el cual nos preguntará si deseamos insertar la tabla dinámica en una hoja de cálculo nueva o en una hoja de cálculo existente. Selecciona la opción de tu preferencia y pulsa el botón Finalizar.

Personalizar la tabla dinámica

Al crearse la nueva tabla dinámica notarás de inmediato que se muestra el recuento de todos los valores. Si lo que necesitas es la suma de los valores, puedes cambiar este comportamiento haciendo clic derecho sobre alguna de las celdas de valores y seleccionando la opción Resumir valores por y posteriormente la opción Suma.

Si adicionalmente necesitas ocultar alguna de las columnas mostradas debes hacer clic en la flecha que se encuentra junto aEtiquetas de columna y seleccionar solamente aquellas columnas que deseas ver en pantalla.

También notarás que se ha agregado una columna llamada Total general cuya función es mostrar la suma de cada fila. Para nuestro ejemplo esta columna no es necesaria, así que  puedo remover dicha columna haciendo clic derecho sobre cualquier celda de valor y seleccionando Opciones de tabla dinámica.

Dentro de este cuadro de diálogo, dentro la pestaña Totales y filtros, debes remover la selección de la opción Mostrar totales generales de las filas y finalmente pulsar el botón Aceptar. Con  esta última modificación habremos llegado a la tabla dinámica que nos permite analizar adecuadamente los datos provenientes de varias hojas.

Inteligencia de negocios con Excel

Inteligencia de negocios con Excel

La toma de decisiones dentro de las empresas es sumamente importante y sin embargo no es un proceso simple. Con tanta información generada dentro de una empresa es importante tomar decisiones adecuadas que ayuden a crecer y fortalecer el negocio por lo que se hace obligatoria una mejora continua en los procesos de toma de decisiones.

Es inevitable apoyarse de la tecnología para analizar la información ya que eso nos llevará a tomar las mejores decisiones. Es así como entramos al campo de la inteligencia de negocios.

¿Qué es la inteligencia de negocios?

La inteligencia de negocios (BI por sus siglas en inglés) es el conjunto de estrategias, métodos y herramientas que nos permiten analizar los datos de una empresa de manera que podamos generar el conocimiento necesario para ayudar en la toma de decisiones. Las herramientas de software tienen un papel importante en esta generación de conocimiento porque nos permiten analizar y detectar patrones o tendencias en la información que nos ayudarán a construir diferentes escenarios y generar pronósticos. Y entre toda esta generación de conocimiento y análisis de la información se encuentra Excel.

Excel en la Inteligencia de negocios

Hoy en día existe una gran cantidad de herramientas de BI y Microsoft no se ha quedado atrás sino que ha estado apostando fuertemente en esta área en los últimos años y Excel juega un papel importante dentro de

los planes de la empresa para el futuro.

De acuerdo con la empresa Gartner, las herramientas de inteligencia de negocios de Microsoft se han comenzado a posicionar dentro de los líderes del mercado y afortunadamente, para los usuarios de Excel, es precisamente la hoja de cálculo la herramienta que será utilizada como punta de lanza en la estrategia de BI de Microsoft. Aunque la plataforma de inteligencia de negocios de Microsoft está basada fuertemente en su producto de base de datos conocido como SQL Server, se ha invertido fuertemente para que Excel sea la interfaz por excelencia para consultar y analizar los datos contenidos en dicho repositorio de datos.

Mejoras de Excel en el campo de BI

Excel 2010 ha tenido grandes mejoras en el área de Inteligencia de negocios. En primer lugar tenemos la segmentación de datos (slicers en inglés) que son controles visuales que nos permiten filtrar rápida y fácilmente los datos. Podemos utilizar la segmentación de datos con las tablas dinámicas, gráficos dinámicos y con funciones de Cubo para poder generar tableros de control interactivos.

 

Así mismo se han realizado grandes esfuerzos para mejorar la funcionalidad de las tablas dinámicas. Un ejemplo de esto es el nuevo cuadro de búsqueda y filtro que aparece tanto para las tablas dinámicas como para las tablas de datos.

Este cuadro de búsqueda permite navegar rápidamente por grandes cantidades de registros encontrando el dato exacto que necesitamos.

PowerPivot en Excel

 Finalmente, el equipo de SQL Server colaboró fuertemente con el equipo de Excel para crear un producto llamado PowerPivot que es una poderosa herramientas de análisis de datos y que está diseñada para trabajar con Excel 2010.

PowerPivot está diseñado para analizar y visualizar fácilmente millones de registros de información. Así que hasta ahora vemos grandes avances en Excel dentro del área de Inteligencia de negocios (BI) y seguramente habrá más características y mejoras que están aún por venir. Mantente conectado con Excel Total donde pronto comenzaré a adentrarme más en éstas características de Excel.