Determinar fechas específicas en Excel

En esta ocasión revisaremos diferentes métodos para determinar ciertas fechas específicas en Excel como determinar el número de día del año, conocer el último día el mes, así como otras fechas que pueden ser de utilidad al trabajar con Excel.

Calcular los días transcurridos en el año

Si la celda B1 contiene la fecha 12 de junio del 2012 y necesito saber los días del año que han transcurrido hasta ese día lo puedo hacer con la siguiente fórmula:

=B1 - FECHA(AÑO(B1),1,0)

Observa como la fórmula devuelve el número 164 que es justamente el número de días transcurridos:

Si deseas calcular el número de días que faltan para el final de año podemos utilizar la siguiente fórmula:

=FECHA(AÑO(B1),12,31) - B1

La fórmula nos devolverá los días que existen entre el fin de año y nuestra fecha:

Por supuesto que si la fecha actual la deseas obtener con la función HOY, entonces la fórmula será la siguiente:

=FECHA(AÑO(HOY()), 12, 31) - HOY()

Determinar la fecha del domingo pasado

Para conocer la fecha del domingo anterior a la fecha proporcionada en la celda B1 podemos utilizar la siguiente fórmula:

=B1 - RESIDUO(B1 - 1, 7)

Observa el resultado de la fórmula para una fecha determinada:

Es importante mencionar que el resultado de la fecha será un valor numérico y deberás aplicar el formato de fecha correspondiente. Esta misma fórmula funciona para encontrar otros días de la semana con tan solo cambiar el número que es restado en el primer argumento de la función RESIDUO.

Para este ejemplo se hizo la resta del número 1 para obtener la fecha del domingo anterior, pero si deseo obtener la fecha del lunes anterior debo utilizar el número 2:

=B1 - RESIDUO(B1 - 2, 7)

Para obtener la fecha del martes anterior debo restar el número 3 y así sucesivamente hasta llegar al número 7 que obtendrá la fecha del sábado anterior.

Determinar el último día del mes

Para obtener el último día del mes podemos utilizar la función FECHA pero con un pequeño truco que será incrementar el número del mes en 1 y utilizar el día 0 (cero). El día “cero” del mes siguiente será el último día del mes actual.

=FECHA(AÑO(B1), MES(B1) + 1, 0)

Observa cómo se calcula el último día del mes en base a una fecha especificada en la celda B1.

Haciendo una pequeña variación a esta fórmula podemos obtener el número total de días del mes en cuestión ya que hemos obtenido el último día del mes podemos saber el número de días de un mes con la ayuda de la función DIA:

=DIA(FECHA(AÑO(B1), MES(B1) + 1, 0))

Para mostrar el resultado adecuadamente debemos dar un formato de celda de número:

Combinar la función BUSCARV y SI.ERROR

La función BUSCARV es una de las funciones más utilizadas en Excel por lo que es muy probable que hayas visto el error #N/A cuando la función no ha encontrado el valor que estás buscando. Hoy veremos una opción para mostrar un mensaje de error más amigable.

La función BUSCARV en Excel

Hemos visto en artículos anteriores cómo la función BUSCARV nos ayuda a encontrar un valor dentro de una tabla de datos. Pero ¿qué sucede cuando la función BUSCARV no encuentra una coincidencia exacta? Observa cómo la función regresa un error del tipo #N/A:

Evitar desplegar el error #N/A con la función SI.ERROR

La función SI.ERROR fue introducida desde Excel 2007 y es de mucha utilidad cuando queremos detectar si una función nos ha devuelto un mensaje de error. En nuestro ejemplo no deseamos ver el mensaje de error #N/A sino que deseamos desplegar el mensaje “Nombre no encontrado” en caso de que la función BUSCARV no encuentre el Nombre especificado en la celda E1. Para alcanzar nuestro objetivo utilizamos la función SI.ERROR de la siguiente manera:

La función SI.ERROR solamente tiene dos argumentos, el primero es el valor o expresión que va a evaluar, que para nuestro ejemplo es la función BUSCARV, y el segundo argumento es el valor que regresará en caso de que el primer argumento devuelva un error.

En nuestro ejemplo la función BUSCARV no ha encontrado el nombre “Dana” por lo que regresa el error #N/A pero la función SI.ERROR se da cuenta de ello y no deja que se despliegue la leyenda #N/A sino que sabe que le hemos indicado que muestre el mensaje “Nombre no encontrado”. Por el contrario, si la función BUSCARV ha encontrado el valor que estaba buscando entonces la función SI.ERROR no tienen ningún efecto en el resultado. Observa el siguiente ejemplo donde busco el nombre “Diana” el cual sí es encontrado en la lista:

La función SI.ERROR nos ayuda a personalizar los mensajes de error de cualquiera de las funciones de Excel incluyendo a la función BUSCARV.

Contar valores únicos en Excel

En ocasiones necesitamos contar valores únicos en Excel de manera que podamos conocer la cantidad exacta de entradas que no se repiten dentro de un rango. Para resolver este problema haré uso de las fórmulas matriciales.

Supongamos que nos ha llegado un archivo de Excel que tiene la lista consolidada de varias personas con su ciudad de origen.

Ahora me han pedido que cuente las diferentes ciudades de la lista, es decir obtener el número de ciudades únicas de la columna B. Para este ejemplo lo podría hacer visualmente, pero si tengo una lista con miles de registros la tarea se puede complicar.

Fórmula para contar valores únicos en Excel

Para contar valores únicos en Excel podemos utilizar la siguiente fórmula matricial:

{=SUMA(1/CONTAR.SI(B2:B10, B2:B10))}

Recuerda que para ingresar una fórmula matricial debemos pulsar las teclas CTRL + MAYÚS + ENTRAR justo al terminar de introducir la fórmula lo cual hará que Excel coloque los corchetes alrededor de la fórmula. Observa el resultado de aplicar esta fórmula a los datos del ejemplo:

La única desventaja de esta fórmula es que dejará de funcionar adecuadamente si una celda está vacía y obtendremos un error #¡DIV/0! como resultado.

Para resolver este problema podemos utilizar la función SI.ERROR de manera que nuestra fórmula siga funcionando. Esta es la fórmula a utilizar:

=SUMA(SI.ERROR(1/CONTAR.SI(B2:B10, B2:B10), 0))

Observa el resultado al utilizar esta fórmula sobre el rango que contiene una celda vacía:

De esta manera hemos eliminado el error #¡DIV/0! y hemos logrado contar valores únicos en Excel aún dentro de un rango con celdas vacías.

Sumar celdas de distintas hojas

Para sumar celdas de distintas hojas en Excel debemos poner especial atención a las referencias de las celdas que vamos a utilizar de manera que la función SUMA devuelva los resultados adecuados. 

En primer lugar debemos recordar que para crear una referencia a una celda de otra hoja debemos hacerlo colocando el nombre de la hoja seguido de un signo de exclamación (!) y posteriormente la dirección de la celda. Por ejemplo: Hoja2!A1

Sumar celdas de diferentes hojas

Si quiero sumar todas las celdas A1 de la Hoja1, Hoja2 y Hoja3 puedo tener una fórmula como la siguiente:

Con esta fórmula hemos sumado los valores de todas las celdas A1 de la Hoja1 a la Hoja3. De esta manera podemos especificar la dirección exacta de todas las celdas que deseamos sumar.

Sumar la misma celda de varias hojas

En el ejemplo anterior sumamos la celda A1 de todas las hojas y especificamos las tres direcciones de la celda A1. Sin embargo existe un método abreviado para estos casos. Cuando necesitamos sumar la misma celda en varias hojas podemos utilizar el siguiente método abreviado en la fórmula:

En este caso los dos puntos (:) separan los nombre de la primera hoja y de la última hoja que vamos a incluir seguidos del signo de exclamación (!) y la dirección de la celda que se sumará en cada una de las hojas.

Este método es muy útil cuando tienes que sumar la misma celda de muchas hojas porque evita introducir la dirección de cada una de las celdas. Por ejemplo, si deseamos sumar la celda B5 de las Hoja1 hasta la Hoja15 la fórmula será:

SUMA(Hoja1:Hoja15!B5)

Sumar un rango en distintas hojas

Así como podemos sumar una misma celda en diferentes hojas, también podemos sumar un rango en distintas hojas. La nomenclatura es similar.

Esta fórmula regresará la suma del rango A1:A3 en todas las hojas desde la Hoja1 hasta la Hoja3. Las técnicas que hemos revisado pueden ser aplicadas a otras funciones, así que puedes hacer esta misma prueba con otras funciones como PROMEDIO, MAX, MIN.

Combinar texto y números en una celda

Si necesitas combinar texto y números en una misma celda, Excel nos permite hacerlo de tres maneras diferentes: Concatenando los valores, utilizando la función TEXTO o utilizando un formato personalizado. Supongamos la siguiente tabla de datos:

Lo que deseamos hacer es agregar la palabra “días” a cada celda de manera que podamos leer: 76 días, 66 días, 75 días, etc. Podrías pensar que podemos agregar la palabra “días” en la columna de la derecha, pero no es eso lo que buscamos, sino que necesitamos dentro de una misma celda tanto el número como el texto.

Utilizar la concatenación

El método más simple para combinar texto y números en una celda es utilizar la concatenación. Para ello podemos utilizar el carácter “&” que nos ayuda a unir tanto el texto como el número. Observa el ejemplo que utiliza esta técnica en la columna B:

La desventaja de este método es que las celdas ya no pueden ser utilizadas con su valor numérico porque ahora son celdas que contienen un texto.

Utilizar la función TEXTO

Otra alternativa es utilizar la función TEXTO que nos permite convertir un número en texto y darle el formato que necesitamos. Observa la solución en la columna C:

El segundo parámetro de la función TEXTO me permite especificar el formato que deseo colocar al valor numérico y es donde puedo aprovechar para incluir la palabra “días”. Este formato se construye con los mismos códigos de formato personalizadoque revisamos en un artículo anterior.

Esta alternativa deja el contenido de la celda como un texto, así que también nos vemos imposibilitados a utilizar el valor de la celda en algún cálculo numérico. Sin embargo la idea de trabajar con el formato de la celda es lo que nos lleva a la tercera y última opción que es la que quiero recomendar.

Utilizar el formato personalizado

Si queremos combinar texto y números en una misma celda sin perder la posibilidad de utilizar su valor en un cálculo numérico, entonces la mejor alternativa es aplicar un formato personalizado. El formato que utilizaré es el mismo del ejemplo con la función TEXTO, solo que en esta ocasión colocaré dicho formato personalizado en las propiedades de la celda de la siguiente manera:

Una vez que se aplica el formato personalizado a todas las celdas obtenemos el siguiente resultado:

Observa cómo la barra de fórmulas despliega un valor numérico para la celda indicando que sigue siendo un valor numérico a pesar de que en pantalla se muestra un texto dentro de la celda. Ahora observa lo que sucede al hacer una suma con los valores de la columna D:

Así que si deseas combinar texto y números en una sola celda, pero sin dejar de utilizar el valor numérico de una celda, te recomiendo utilizar un formato personalizado.

Cuándo utilizar referencias absolutas en EXCEL 2013

Cuándo utilizar referencias absolutas

Cuando creamos una fórmula que incluye referencias a otras celdas, dichas referencias pueden ser absolutas o relativas. Una referencia relativa se ajustará automáticamente a su nueva ubicación al momento de copiarla mientras que una referencia absoluta jamás cambiará aun cuando la copiemos en otro lugar.

¿Cuándo utilizar referencias absolutas?

De manera predeterminada Excel crea todas las referencias como relativas, entonces ¿Cuándo debemos considerar la creación de una referencia absoluta? La respuesta es simple. La única ocasión en las que debes considerar crear una referencia absoluta es cuando planeas copiar una fórmula a otro lugar y necesitas que una o varias de sus referencias permanezcan fijas aún después de realizar la copia.

Te lo explicaré con un ejemplo. Tengo una lista de productos que debo comprar para la oficina así como su precio individual. Para obtener el total del primer producto utilizo la siguiente fórmula:

Es evidente que puedo evitarme el trabajo de crear la misma fórmula para los demás artículos así que puedo copiar esta fórmula hacia abajo. Pero antes de copiarla me puedo preguntar ¿Existe algún elemento de la fórmula que necesito que sea fijo para todas las fórmulas?

Para este caso en específico la respuesta es no, porque quiero que para todos los productos se multiplique su cantidad con su precio correspondiente. Al copiar la fórmula hacia abajo Excel creará las referencias relativas adecuadas y obtendré el resultado esperado para cada producto:

Pero ahora supongamos que en la celda B8 tengo el porcentaje del impuesto que debo pagar para los productos de mi lista. Si quiero conocer el monto de dicho impuesto para el primer producto puedo insertar una nueva columna para realizar el cálculo.

De igual manera puedo copiar la fórmula hacia abajo para ahorrarme un poco de trabajo pero debo preguntarme si existe una referencia que necesito que sea fija para todas las fórmulas. En esta caso la respuesta es si, porque necesito que todas tomen el porcentaje del impuesto que está especificado en la celda B8. Sin embargo, para ilustrar mejor el ejemplo, no haré caso de este consejo y copiaré la fórmula hacia abajo. Observa el resultado obtenido:

Excel copió la fórmula usando referencias relativas, de esta manera la fórmula para la celda E3 es =D3*B9, para la celda E4 es =D4*B10 y así sucesivamente. Ya que las celdas B9 y B10 no tienen valor alguno entonces obtenemos un cero como resultado.

El razonamiento inicial estaba correcto y debemos utilizar una referencia absoluta para la celda que contiene el porcentaje del impuesto. Al copiar la fórmula de la celda E2 hacia abajo necesito que la celda B8 quede fija para todas las fórmulas. Para lograr este objetivo la fórmula de la celda E2 debe ser la siguiente:

Solo debo fijar la celda $B$8 porque es la celda que contiene el porcentaje del impuesto a pagar y quiero que todas las fórmulas que voy a copiar hacia abajo hagan referencia a ese valor. Al momento de copiar la fórmula hacia abajo obtengo el resultado esperado:

Las referencias a las celdas de la columna D se ajustan automáticamente mientras que la referencia a la celda B8 permanece fija que es justamente lo que necesitamos.

Recuerda, para saber cuándo utilizar una referencia absoluta en Excel debes preguntarte si existe una referencia dentro de la fórmula que necesitas que permanezca fija aún después de haber copiado la fórmula a otro lugar.

Diseño de bases de datos

El diseño de una base de datos es de suma importancia ya que de ello dependerá que nuestros datos estén correctamente actualizados y la información siempre sea exacta. Si hacemos un buen diseño de base de datos podremos obtener reportes efectivos y eficientes.

En esta ocasión proporcionaré algunas recomendaciones a seguir al momento de realizar el diseño y modelo de una base de datos. No importa la herramienta que se utilice para almacenar la información, puede ser Excel, Access o sistemas gestores de bases datos más complejos como Microsoft SQL Server pero siempre debes diseñar y modelar una base de datos antes de tomar la decisión de crearla.

Conceptos básicos sobre el diseño de bases de datos

En cualquier base de datos la información está almacenada en tablas las cuales a su vez están formadas por columnas y filas. La base de datos más simple consta de una sola tabla aunque la mayoría de las bases de datos necesitarán varias tablas.

Las filas de una tabla también reciben el nombre de registros y las columnas también son llamadas campos.

Diseñar y modelar una base de datos

Al diseñar una base de datos determinamos las tablas y campos que darán forma a nuestra base de datos. El hecho de tomarnos el tiempo necesario para identificar, organizar y relacionar la información nos evitará problemas posteriores.

Es por eso que para diseñar una base de datos es necesario conocer la problemática y todo el contexto sobre la información que se almacenará en nuestro repositorio de datos. Debemos determinar la finalidad de la base de datos y en base a eso reunir toda la información que será registrada. A continuación los 5 pasos esenciales para realizar un buen diseño y modelo de una base de datos.

1. Identificar las tablas

De acuerdo a los requerimientos que tengamos para la creación de nuestra base de datos, debemos identificar adecuadamente los elementos de información y dividirlos en entidades (temas principales) como pueden ser las sucursales, los productos, los clientes, etc.

Para cada uno de los objetos identificados crearemos una tabla. Si en una base de datos los objetos principales son los empleados y los departamentos de la empresa entonces tendremos una tabla para cada uno de ellos. Si en otra base de datos los objetos principales son los libros, autores y editores entonces necesitaremos tres tablas en nuestra base de datos.

2. Determinar los campos

Cada entidad representada por una tabla posee características propias que lo describen y que lo hacen diferente de los demás objetos. Esas características de cada entidad serán nuestros campos de la tabla los cuales describirán adecuadamente a cada registro. Por ejemplo, una tabla de libros impresos tendrá los campos ISBN, título, páginas, autor, etc.

3. Determinar las llaves primarias

Una llave primaria es un identificador único para cada registro (fila) de una tabla. La llave primaria es un campo de la tabla cuyo valor será diferente para todos los registros. Por ejemplo, para una tabla de libros, la llave primaria bien podría ser el ISBN el cual es único para cada libro. Para una tabla de productos se tendría una clave de producto que los identifique de manera única.

4. Determinar las relaciones entre tablas

Examina las tablas creadas y revisa si existe alguna relación entre ellas. Cuando encontramos que existe una relación entre dos tablas debemos identificar el campo de relación. Por ejemplo, en una base de datos de productos y categorías existirá una relación entre las dos tablas porque una categoría puede tener varios productos asignados. Por lo tanto el campo con el código de la categoría será el campo que establezca la relación entre ambas tablas.

5. Identificar y remover datos repetidos

Finalmente examina cada una de las tablas y verifica que no exista información repetida. El tener información repetida puede causar problemas de consistencia en los datos además de ocupar más espacio de almacenamiento.

Por ejemplo, una tabla de empleados que contiene el código del departamento y el nombre del departamento comenzará a repetir la información para los empleados que pertenezcan al mismo departamento.

¿Qué pasaría si el nombre del departamento cambiara de Informática a Tecnología? Tendríamos que ir registro por registro modificando el nombre correspondiente y podríamos dejar alguna incongruencia en los datos. Una mejor solución es tener una tabla exclusiva de departamentos y solamente incluir la clave del departamento en la tabla de empleados.

De esta manera dejamos de repetir el nombre del departamento en la tabla de empleados y ahorramos espacios de almacenamiento. Y en caso de un cambio de nombre de departamento solamente debemos realizar la actualización en un solo lugar.

El diseño de bases de datos es un tema muy extenso y es difícil considerar todos sus aspectos en un solo artículo. Sin embargo, al seguir estas 5 reglas básicas del diseño de bases de datos estaremos dando un paso hacia adelante en las buenas prácticas de creación y gestión de bases de datos