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

Macro para desplazarse entre hojas

Al construir un reporte en Excel es común mostrar un resumen de la información en la primera hoja que nos permitirá consultar el detalle que se encuentra en el resto de las hojas del libro y que también ofrecen información relevante. Nuestro objetivo es hacer que el usuario llegue al detalle que está buscando de una manera sencilla. 

Una buena alternativa para crear este sistema de “navegación” en nuestro libro, es colocar un botón para moverse entre hojas. De esta manera podemos guiar a los usuarios hacia las hojas que contienen todo el detalle y evitar que ellos tengan que visitar cada una de las hojas en busca de la información.

Moverse a otra hoja con la grabadora de macros

El método más sencillo para crear un botón que se desplace a otra hoja es a través de la grabadora de macros. Debes ubicarte en la hoja inicial y hacer clic en el botón Grabar macro que se encuentra en la ficha Vista > Macros > Grabar macro o que también puedes iniciar con el icono ubicado en el extremo lateral izquierdo de la barra de estado. Al pulsar ese botón se mostrará el cuadro de diálogo Grabar macro.

Una vez que hayas especificado un nombre adecuado para la macro haz clic en Aceptar y solamente deberás realizar una sola acción: Hacer clic sobre la pestaña de la hoja a donde deseas moverte.

Inmediatamente después deberás detener la grabación pulsando el botón Detener grabación que se encuentra en la barra de estado de Excel. Con esto hemos creado la macro que nos ayudará a movernos a una hoja dentro del libro y solamente nos restará agregar el botón a la hoja. Para agregar este botón debes hacer clic en el botón desplegable Insertar que se encuentra dentro del grupo Controles de la ficha Programador y seleccionar el elemento Botón.

El puntero del ratón te permitirá definir el tamaño del botón sobre la Hoja y al terminar de dibujarlo se mostrará el cuadro de diálogo Asignar macro.

Lo único que tenemos que hacer es seleccionar la macro que recién acabamos de crear y pulsar el botón Aceptar. Al pulsar el botón recién creado nos moveremos hacia la nueva hoja.  De esta misma manera podemos grabar una macro diferente que nos lleve de regreso a la hoja principal. Observa la siguiente animación en donde ya he creado una segunda macro que me permite regresar a la Hoja inicial:

Analizando el código VBA

Si analizas el código creado con la grabadora de macros, podremos aprender un poco sobre programación VBA. Las macros que fueron creadas generaron un código que puedes ver en el Editor de Visual Basic y que será similar al siguiente:

Observa que el objeto Sheets tiene un método llamado Select el cual hace la selección de dicha hoja:

Sheets("Hoja1").Select

Esta instrucción siempre seleccionará la Hoja1 independientemente de la hoja en la que me encuentre. De esta manera, la macro que creamos la podemos asignar a un botón en cualquier otra hoja de nuestro libro y siempre nos moveremos a laHoja1.

Moverse entre hojas con VBA

El ejemplo anterior funciona perfectamente siempre y cuando no se cambien los nombres de las hojas del libro ya que si esto llega a suceder entonces nuestra macro dejará de funcionar. Para evitar este problema debemos utilizar el nombre del objeto en VBA de manera que sin importar que cambie de nombre podremos movernos hacia dicha hoja. En la siguiente imagen puedes observar cómo he renombrado la Hoja1 con el nombre Principal y que se ve reflejado en el Explorador de proyectos:

Para garantizar que el botón que nos mueve a la hoja Principal funcione adecuadamente debo modificar el código VBA de la siguiente manera:

Con la nueva instrucción (Hoja1.Select) estaremos utilizando el nombre interno del objeto y no importará si cambia de nombre la hoja, nuestro sistema de navegación siempre funcionará adecuadamente.

Instrucciones VBA adcionales para cambiar de hoja

Algunas instrucciones VBA que pueden ser de utilidad al movernos entre hojas son las siguientes:

Sheets(Sheets.Count).Select

Esta instrucción siempre nos moverá a la última hoja de nuestro libro.

Sheets(1).Select

Esta instrucción nos moverá a la primera hoja de nuestro libro.

Con los métodos e instrucciones presentados en este artículo estás listo para crear tus propios botones de navegación en tus reportes de Excel. Descarga el libro de trabajo utilizado en este artículo y revisa el código VBA creado en este ejemplo.

Formato de fechas y horas

Las fechas son un tipo de dato muy común dentro de Excel y aprender a darles el formato adecuado es de suma importancia para poder presentar la información de manera adecuada. De igual manera el formato de horas es importante.

Formatos de fecha y hora en Excel

De manera predeterminada en la lista de formatos que se encuentra en la ficha Inicio dentro del grupo Número tenemos un par de formatos para fecha y otro para hora.

Sin embargo en el cuadro de diálogo Formato de celdas tenemos muchas más opciones de donde elegir. Asegúrate de elegir la categoría Fecha o la categoría Hora para tener acceso a los diferentes tipos de formato adicionales:

Fecha y hora en la misma celda

Es posible tener celdas que contengan un valor tanto de fecha como de hora. Esto es posible al separar la información por un espacio tal como se muestra a continuación:

Observa cómo la barra de fórmulas muestra el contenido de la celda tanto con la fecha como con la hora sin embargo el valor de la celda solamente muestra la fecha. Esto se debe a que la celda tiene un formato de fecha. Si cambiamos al formato de la celda por un formato de hora entonces la celda se mostrará de manera diferente:

Para que la celda muestre ambos datos debe tener un formato personalizado el cual podemos elegir dentro del cuadro de diálogo Formato de celdas.

Ingresando fechas y horas en Excel

Excel trata a los datos de tipo fecha y de tipo hora como un tipo especial de datos numéricos y al momento de presentarlos en pantalla les da el formato adecuado para que podamos entender mejor dichos valores.

La primera fecha que Excel reconoce es el día 1 de enero de 1900 y el cual tiene asignado el número de serie 1 y a partir de ahí la cuenta es progresiva de uno en uno. Ya que las fechas son valores numéricos, las operaciones entre fechas se facilitan en Excel.

Formatos de fecha en Excel

La siguiente tabla muestra los formatos de fecha en Excel que son válido y que podemos utilizar en nuestras hojas. Es importante mencionar que estos formatos dependerán de las configuraciones regionales del equipo. La siguiente tabla la he realizado considerando una configuración Español (México).

Texto introducido	Interpretación de Excel
1-08-2011	1 de Agosto de 2011
1-08-11	1 de Agosto de 2011
1/08/2011	1 de Agosto de 2011
1/08/11	1 de Agosto de 2011
2011-08-01	1 de Agosto de 2011
1 Agosto, 2011	1 de Agosto de 2011
1 Agosto	1 de Agosto del año actual
1 Ago	1 de Agosto del año actual
1-8	1 de Agosto del año actual
1/8	1 de Agosto del año actual

Debes poner mucha atención al momento de introducir fechas ya que si no utilizas alguno de los formatos adecuados Excel tratará la información como si fuera una cadena de texto en lugar de fecha.

Observa la última entrada de la tabla. ¿Qué sucede si lo que quiero especificar a Excel es una valor numérico que represente a la fracción 1/8? Si introducimos el texto “1/8” se interpretará como fecha pero si anteponemos el símbolo igual (=) entonces obtendremos el valor fraccionario que buscamos.

Formatos de hora en Excel

Cuando trabajamos con horas en Excel entonces comenzaremos a utilizar valores decimales. Por ejemplo, el valor de serie para el 1 de agosto de 2011 es 40756 y si queremos indicar el medio día de esa misma fecha entonces Excel agregará una parte decimal a ese valor para tener 40756.5 especificando así las horas del día.

Realmente nunca nos preocupamos sobre los números de serie de las fechas o sus fracciones sino que solamente ingresamos en Excel el formato que estamos acostumbrados a utilizar todos los días. A continuación te muestro una tabla con los formatos de hora en Excel que podemos utilizar:

Texto introducido	Interpretación de Excel
9:45 a.m.	9:45 a.m.
9:45 A.M.	9:45 a.m.
9:45 am	9:45 a.m.
9:45 AM	9:45 a.m.
9:45	9:45 a.m.
9:45 p.m.	9:45 p.m.
21:45	9:45 p.m.

En estos ejemplos no existe una fecha asociada a las horas sin embargo puedes hacer una combinación de una fecha con una hora dejando un espacio en blanco entre ambos datos. Por ejemplo, las 9:45 a.m. del día 1 de Agosto del 2011 se especificará en Excel de la siguiente manera: