Sumar y contar con varias condiciones en Excel

Una pregunta que recibo frecuentemente es cómo sumar y contar con varias condiciones en Excel. Cuando estás en esta situación nuestra reacción es utilizar las funciones SUMAR.SI y CONTAR.SI pero pronto nos damos cuenta de que no son el camino correcto.

Si tienes Excel 2007 o una versión superior, entonces puedes utilizar la función SUMAR.SI.CONJUNTO y la función CONTAR.SI.CONJUNTO, de lo contrario puedes utilizar alguno de los métodos que presento a continuación.

Limitaciones de SUMAR.SI y CONTAR.SI

Las funciones SUMAR.SI y CONTAR.SI no nos ayudan a sumar y contar con varias condiciones en Excel porque solo pueden contener una sola condición. Observa la siguiente tabla de datos:

La función SUMAR.SI funciona correctamente si quiero conocer las ventas del mes de Enero para lo cual puedo utilizar la siguiente fórmula:

=SUMAR.SI(A2:A12, "Enero", D2:D12)

Si quiero contar el número de ventas en el mes de Marzo utilizo la siguiente fórmula:

=CONTAR.SI(A2:A12, "Marzo")

Sin embargo, ¿cómo puedo sumar las ventas del mes de Enero de la región Norte? A continuación veremos dos alternativas para sumar con varias condiciones en Excel.

Sumar con varias condiciones utilizando SUMA

La primera alternativa para sumar con varias condiciones es utilizar la función SUMA. Observa con detenimiento la siguiente fórmula:

=SUMA((A2:A12="Enero") * (B2:B12="Norte") * D2:D12)

Antes de explicar esta fórmula debo decir que para que funcione correctamente debemos utilizarla como una fórmula matricial por lo que después de introducirla en la barra de fórmulas debemos pulsar la combinación de teclas Ctrl + Mayus + Entrar.

En la formula anterior encontrarás que las condiciones se encuentran encerradas entre paréntesis. La condición (A2:A12=”Enero”) será verdadera solamente cuando el valor de la columna A tenga el valor “Enero”. De la misma manera, la condición (B2:B12=”Norte”) será solamente verdadera precisamente cuando la celda de la columna B tenga el valor “Norte”.

El resultado de ambas condiciones es multiplicado y recordando la lógica binaria sabemos que al multiplicar verdadero por verdadero obtendremos como resultado un valor verdadero (uno). Si cualquiera de los factores de la multiplicación es falso obtendremos un valor falso (cero) como resultado.

De esta manera, solo en caso de que ambas condiciones sean verdaderas (igual a uno) obtendremos un valor diferente a cero al realizar la multiplicación por la columna D. Si cualquier condición es falsa, la columna D será multiplicada por cero y no tendrá efecto alguno en la suma total. Observa el resultado de aplicar la fórmula antes descrita:

Sumar con varias condiciones utilizando SUMAPRODUCTO

Si conoces la manera en que opera la función SUMAPRODUCTO, te habrás dado cuenta de que opera de manera similar a la fórmula que acabamos de revisar. Es por ello que también podemos sumar con varias condiciones en Excel utilizando la función SUMAPRODUCTO.

=SUMAPRODUCTO((A2:A12="Enero")*1, (B2:B12="Norte")*1, D2:D12)

Observa que las condiciones son las mismas que en la fórmula anterior. La única diferencia es que hacemos la multiplicación de cada condición por 1 para asegurar que tendremos valores numéricos. Observa el resultado de esta función:

Contar con varias condiciones utilizando SUMA

Aunque parezca raro, podemos contar con varias condiciones en Excel utilizando la función SUMA. La fórmula para lograrlo es la siguiente:

=SUMA((A2:A12="Enero") * (B2:B12="Norte"))

Esta fórmula es parecida a la primera que revisamos con la diferencia de que no estamos haciendo la tercera multiplicación por la columna D. Esta función contará las celdas que cumplen con ambas condiciones. Observa el resultado:

Para que esta fórmula funcione debemos pulsar la combinación de teclas Ctrl + Mayus + Entrar ya que debe ser una fórmula matricial.

Contar con varias condiciones utilizando SUMAPRODUCTO

Te podrás imaginar que también podemos utilizar la función SUMAPRODUCTO para contar con varias condiciones. Esta es la fórmula:

=SUMAPRODUCTO((A2:A12="Enero")*1, (B2:B12="Norte")*1)

El resultado de esta fórmula es el esperado:

Limitaciones de este método

La única limitación que tienen los métodos expuestos en este artículo es que no es posible sumar y contar con varias condiciones bajo la misma columna. Es decir, no podemos poner dos condiciones para una sola columna, como por ejemplo sumar las ventas de Enero y Febrero. Estas son dos condiciones para una misma columna.

En un caso como este, la mejor opción es utilizar dos veces la función SUMAR.SI de la siguiente manera:

=SUMAR.SI(A2:A12, "Enero", D2:D12) + SUMAR.SI(A2:A12, "Febrero", D2:D12)

Función CONTAR.SI sensible a mayúsculas

La función CONTAR.SI es una de las funciones más utilizadas en Excel ya que nos permite hacer una cuenta condicionada es decir, nos permite contar el número de celdas dentro de un rango que cumplen con un criterio establecido. Sin embargo, cuando dicho criterio involucra una comparación con texto, la función CONTAR.SI no puede distinguir entre mayúsculas y minúsculas.
Hagamos un ejemplo para dejar claro el comportamiento de la función. En la columna A1 tengo una lista de códigos de países de acuerdo a la norma ISO 31166-1, sin embargo algunos han sido capturados en letras mayúsculas y otros en letras minúsculas.


Observa que la función CONTAR.SI devuelve el valor 5 indicando que ha encontrado cinco coincidencias para la cadena de texto “MX” aunque algunos de los valores están en mayúsculas y otros en minúsculas. Hay ocasiones en las que no deseamos que la cuenta condicional se comporte de esa manera, sino que necesitamos diferenciar entre mayúsculas y minúsculas. Hoy te mostraré tres alternativas para hacer una cuenta condicional que sea sensible al uso de mayúsculas y minúsculas en Excel.

La función IGUAL en Excel

Antes de iniciar con la revisión de los métodos, es necesario hablar un poco sobre la función IGUAL la cual nos permite conocer si un valor es idéntico a otro considerando las diferencias en las letras mayúsculas y minúsculas. Por ejemplo, en la siguiente imagen he utilizado la función IGUAL para comparar todos los valores de la columna A con la cadena de texto “MX”:


Observa que solo para aquellos valores formados exclusivamente por letras mayúsculas, obtendremos el resultado VERDADERO. Si quisiéramos contar la cantidad de valores que cumplen con la condición de ser iguales a la cadena “MX”, entonces podemos utilizar los resultados devueltos por la función IGUAL.

Columna auxiliar sensible a mayúsculas

El primer método para hacer una cuenta condicional sensible a mayúsculas en Excel es a través de una columna auxiliar que contenga el resultado de la función IGUAL tal como lo mostré en la sección anterior. Una vez que conocemos aquellos valores que son iguales, podemos hacer la cuenta de la siguiente manera:

El resultado es correcto y cumple con el requerimiento de hacer una cuenta sensible a mayúsculas, pero estamos obligados a crear una columna auxiliar antes de utilizar la función CONTAR.SI.

Formula matricial sensible a mayúsculas

Una manera de evitar la creación de la columna auxiliar es utilizar una fórmula matricial. Pero antes de mostrarte dicha fórmula, quiero dejar en claro un truco que voy a utilizar en este método.
En la sección anterior observaste que, una vez que la función IGUAL hace la comparación de valores y nos devuelve los valores VERDADERO o FALSO, solo es cuestión de contar cuántos valores VERDADERO tenemos para tener la respuesta correcta. Ahora bien, si en lugar de tener valores VERDADERO, tuviéramos números 1, entonces podríamos sumarlos todos para saber cuántos valores cumplen con el criterio establecido. La cuestión es ¿Cómo convertir los valores VERDADERO y FALSO, devueltos por la función IGUAL, en números 1 y 0 respectivamente?
La solución es muy sencilla, solo debemos anteponer un signo negativo al valor VERDADERO y se convertirá en el número -1 y el valor FALSO se convertirá en 0. Pero si en lugar de colocar un solo signo negativo, colocamos dos, entonces el valor VERDAERO se convertirá directamente en el número 1.Observa cómo se efectúa esta conversión al aplicar ambos signos negativos en la columna C:

Si ahora utilizamos la función SUMA para contar todos los números 1 de la columna C, entonces obtendremos la cantidad de valores de la columna A que son iguales a “MX”:



Ahora que he mostrado la lógica de solución de este método, te puedo mostrar la fórmula matricial que consolida todos los pasos anteriores en una sola fórmula:
=SUMA(--IGUAL(A1:A10, "MX"))
Recuerda que ésta es una fórmula matricial y por lo tanto deberá ser ingresada con la combinación de teclas Ctrl + Mayús + Entrar. El resultado será el mismo que obtuvimos en los pasos anteriores:


El único inconveniente que le veo a este método es el uso de fórmulas matriciales ya que no todos los usuarios de Excel se sienten cómodos utilizándolas. Lo bueno es que aún tenemos un tercer método que nos permitirá hacer una cuenta condicionada que sea sensible a mayúsculas y sin utilizar fórmulas matriciales.

Contar con la función SUMAPRODUCTO

Para nuestro tercer método utilizaremos la función SUMAPRODUCTO que por naturaleza trabaja con matrices y es la razón por la que no es necesario utilizarla como fórmula matricial. La fórmula que utilizaremos es la siguiente:
=SUMAPRODUCTO(--IGUAL(A1:A10, "MX"))
Como puedes observar, la lógica se centra de nueva cuenta en la función IGUAL que se encarga de hacer la comparación de cada valor del rango especificado y posteriormente aplicamos el doble signo negativo para convertir los resultados en números. Finalmente la función SUMAPRODUCTO hace la suma de los productos de la matriz y nos devuelve el resultado correcto:

Cómo filtrar fechas en Excel

Cuando deseamos filtrar datos de un rango o una tabla de Excel por alguna de sus columnas que contenga fechas podremos hacerlo fácilmente utilizando el Autofiltro pero será importante tener en cuenta algunos detalles importantes de las opciones de filtrado que tenemos para este tipo de datos. Hoy revisaremos con detenimiento cada una de las opciones disponibles para filtrar fechas en Excel.

Habilitar los filtros de datos

Antes de comenzar con la revisión detallada, haré una pausa en beneficio de aquellos lectores que son nuevos en Excel y que están aprendiendo a utilizar los filtros en Excel. Para poder crear filtros en un rango de celdas es altamente recomendable que todas las columnas tengan un encabezado el cual identificará adecuadamente el tipo de datos de cada columna. Para crear los filtros será suficiente con seleccionar una celda del rango e ir a la ficha Datos y pulsar el botón Filtro que se encuentra dentro del grupo Ordenar y filtrar.



De inmediato Excel habilitará el filtrado en el rango de celdas colocando una flecha en cada uno de los encabezados. También es posible habilitar los filtros desde la ficha Inicio, y dentro del grupo Modificar, pulsar el botón Ordenar y filtrar que nos permitirá seleccionar la opción Filtro.
Por otro lado, si los datos del rango de celdas son convertidos en una tabla de Excel, entonces no habrá necesidad de habilitar los filtros porque Excel lo hará automáticamente para todas las columnas de la tabla.

Opciones de filtrado para fechas

Podemos conocer las opciones de filtrado para fechas haciendo clic en la flecha del encabezado de la columna que contiene las fechas y posteriormente seleccionando la opción Filtros de fecha. Las opciones disponibles las puedes ver en la siguiente imagen:

Filtrar fechas iguales

Si seleccionamos la opción de filtrado “Es igual a” se mostrará un cuadro de diálogo que nos permitirá seleccionar la fecha con la cual deseamos comparar todos los valores de la columna. Aunque puedes ingresar la fecha directamente en el cuadro de texto, también es posible pulsar el botón ubicado en el extremo derecho para seleccionar la fecha desde un calendario emergente.


Una vez que hayamos indicado la fecha deseada, pulsamos el botón Aceptar para aplicar dicha condición al filtro de datos.

Cómo borrar un filtro de fechas

Antes de revisar las otras opciones que tenemos para filtrar fechas en Excel, te mostraré como eliminar un filtro. En primer lugar podrás reconocer una columna que tiene un filtro porque el encabezado de columna ya no mostrará un botón de flecha sino que tendrá un pequeño icono de filtro que al pulsarlo te permitirá seleccionar la opción Borrar filtro:

Filtrar fechas antes, después y entre

El siguiente grupo de opciones de filtrado disponibles para una columna de fechas son las opciones: Antes, Después y Entre. Para cualquiera de estas tres opciones que elijas, se mostrará el mismo cuadro de dialogo de Autofiltro personalizado. Para las opciones Antes y Después será suficiente con elegir la fecha a partir de la cual se aplicará el filtro:



En este ejemplo he elegido la opción de filtrado Después y por lo tanto la lista desplegable muestra la opción “es posterior a”. En este caso la fecha indicada en el cuadro de texto será excluida de los resultados y solamente se mostrarán las fechas que sean posteriores a dicha fecha. Si deseas que el filtro incluya la fecha indicada en el cuadro de texto, entonces deberás cambiar la selección de la lista desplegable por la opción “es posterior o igual a”.
Si en lugar de elegir las opciones Antes o Después, elijes la opción Entre, entonces deberás indicar el límite inferior y superior del rango de fechas que deseas mostrar:

Filtrar fechas iguales a hoy, mañana o ayer

El tercer grupo de opciones de filtrado de fecha nos permite mostrar aquellas que son iguales a la fecha de hoy, mañana o ayer. En este caso no se mostrará ningún cuadro de diálogo y con solo elegir cualquiera de estas tres opciones se aplicará el filtro sobre los datos.

Filtrar fechas en Excel por semana

El filtro de fechas nos permite aplicar un filtrado por la semana actual, la semana anterior o la semana próxima. Estas opciones de filtrado también son directas, es decir, no se mostrará ningún cuadro de diálogo sino que se aplicará el filtro tan pronto como hagamos la selección.
Lo único que debes considerar al elegir cualquiera de estas opciones es que Excel tomará el día domingo como el inicio de la semana. Por ejemplo, la fecha de hoy es lunes 22 de septiembre del 2014, así que al elegir el filtro “Esta semana” las fechas comenzarán a partir del día domingo 21 de septiembre y hasta el sábado 27 de septiembre.

Filtrar fechas por mes

Una opción de filtrado que puede ser muy conveniente es la de filtrar fechas por el mes actual, el mes pasado o el mes próximo. Estas opciones aplicarán de inmediato el filtro y nos permitirán visualizar en pantalla rápidamente los datos que corresponden al mes seleccionado.

Filtrar por trimestres y años

Para utilizar la opción de filtrado por trimestre debes recordar que Excel siempre tomará el mes de enero como el inicio del año así como el inicio del primer trimestre y no habrá manera de modificar dicho comportamiento. Si utilizo los datos de ejemplo y selecciono la opción de filtrado “Este trimestre”, considerando que el mes actual es Septiembre, las fechas mostradas serán las de los meses Julio, Agosto y Septiembre que son las que corresponden al tercer trimestre del año.
En el caso del filtro por año el resultado lo podemos predecir fácilmente ya que se mostrarán las fechas de los doce meses del año elegido.

Filtrar datos hasta la fecha

Otra de las opciones de filtrado de fechas en Excel es la opción “Hasta la fecha” la cual mostrará todos los datos del año en curso y hasta la fecha actual. Este tipo de filtro es también conocido por su nombre en inglés: “Year to Date” que generalmente es abreviado como YTD.

Filtrar todas las fechas en el período

La opción “Todas las fechas en el período” nos permite filtrar los datos por los diferentes trimestres o meses del año. Solo será necesario elegir la opción deseada para que tenga efecto el filtro sobre las fechas, pero debes recordar que estas opciones incluirán las fechas del período elegido para todos los años, es decir, si seleccionas el mes de Enero, se mostrarán los datos del mes de Enero de todos los años.

Lo mismo sucederá con los trimestres, ya que al elegir cualquiera de ellos se mostrarán los datos del trimestre elegido para cualquiera de los años contenidos en los datos.

Filtro de fechas personalizado

La última de las opciones de los filtros de fecha es la opción “Filtro personalizado” la cual mostrará el cuadro de diálogo Autofiltro personalizado y que es el mismo cuadro que utilizamos en las primeras opciones mostradas en este artículo.



Este cuadro de diálogo te permitirá crear como máximo dos condiciones de filtrado para las fechas. Si deseas forzar el cumplimiento de ambas condiciones, entonces la opción Y deberá estar seleccionada. Por el contrario, si solo deseas que se cumpla alguna de las condiciones, entonces deberás seleccionar la opción O.
También debo decirte que aunque la lista desplegable muestra diferentes tipos de condiciones que podemos crear, cuando hablamos de fechas, solamente hacen sentido las siguientes opciones: es igual a, no es igual a, es posterior a, es posterior o igual a, es anterior a, es anterior o igual a. El resto de opciones no serán funcionales para columnas que contienen fechas ya que fueron hechas para trabajar exclusivamente con cadenas de texto.

Filtrar fechas mediante cuadros de selección

Hasta ahora hemos visto todas las opciones del menú “Filtros de fecha” pero también es posible filtrar fechas en Excel utilizando los cuadros de selección mostrados al final de las opciones de filtrado.


Con solo marcar o desmarcar los cuadros de selección se aplicarán los filtros correspondientes a las fechas. En la imagen puedes ver que Excel ha detectado que mi columna tiene fechas de varios años y por lo tanto muestra el primer nivel de cuadros de selección con dichos valores. Si hago clic en la opción [+] se mostrarán los meses para cada año y si vuelvo a hacer clic en la opción [+] de cualquier mes se mostrarán los días.
Ahora ya conoces las diferentes alternativas que tenemos para filtrar fechas en Excel. La próxima vez que tengas un rango de celdas con este tipo de datos podrás aplicar cualquier filtro de una manera fácil y rápida.

Cómo dar Nombre a un Rango de Celda

    Cómo dar Nombre a un Rango de Celda

Para nombrar un rango de celdas existen dos maneras de hacerlo, y ambas se pueden usar por igual.

Primera forma:

1.- Seleccionar el rango de celda que se desea nombrar.

2.- En la ficha Formulas/ Nombres definidos,  elegir la opción Asignar nombre a un rango.

3.- En la ventana de diálogo que aparece escribir el nombre que se le quiere dar al rango (lo recomendable es que el nombre sea de una sola palabra, no importa en mayúsculas o minúsculas)

4.- Finalmente hacer clic en el botón Aceptar.

Segunda forma:

1.- Seleccionar el rango de celda que se desea nombrar.

2.- Hacer un clic en el casillero de Cuadro de Nombres que se encuentra en la parte izquierda de la barra de fórmulas sobre los encabezados de columna.

3.- Escribir en el casillero el nombre que se le quiere dar al rango.

4.- Presionar Enter.

Un vez nombrado un rango, este podrá ser usado de allí en adelante en cualquier fórmula o en cualquier orden de menú de Excel cada vez que se tenga que hacer referencia a ese rango.

Ejemplo:

Suponga que se tiene un cuadro con la relación de los montos de pago de varios clientes y al rango B4:B16 se le ha dado el nombre de rango PAGOS.

Entonces…

Si se deseara calcular la suma de todos los pagos, podríamos utilizar la función autosuma:

Antes se tenía que escribir la formula así:

                          =SUMA(B4:B16)

Ahora que B4:B16 tiene el nombre PAGOS, la formula podría escribirse así:       

                          =SUMA(PAGOS)

Referencias 3D en Excel 2013

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 7 hojas, una para cada día de la semana y dentro de cada hoja, en la celda C3, tengo el dato del total de la venta del día.

Ahora deseo crear una nueva hoja que tendrá la suma de las celdas C3 de todas las hojas. Para obtener esta suma podría sumar cada una de las celdas C3 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 C3 de todas las hojas de los dias la formamos de la siguiente manera:

	LUNES:DOMINGO!C3

 

La referencia 3D indica a Excel que debe considerar todas las hojas de Excel comprendidas entre la hoja LUNES y la hoja DOMINGO y tomar el valor de la celda C3 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.

FORMATO CONDICIONAL EN EXCEL

FORMATO CONDICIONAL EN EXCEL

Esta diseñado para cambiar el formato de una celda siempre y cuando cumpla con ciertas condiciones dadas por el usuario, se puede aplicar mas de un formato condicional a un rango de celdas.

Ej:   Se escriben en Excel ciertos números aleatorios:

Y se desea darles formato azul a aquellos que son menores o iguales a 3, formato verde a los que están entre 4 y 6 (inclusive) y rojo a los mayores de de 7.

1. Se selecciona el rango de números y damos clic en el menú Formato y luego en Formato Condicional.

2. Se selecciona la opción deseada y al frente se pone la condición:

3. Al dar clic en el botón “formato” se selecciona color, fuente, bordes, etc.

4. Como son varias condiciones para un mismo rango de celdas, debemos dar clic en el botón “agregar” y aparecerá otra ventana de condiciones para agregar la siguiente categoría de 4 a 6.

5. Cuando ya se tienen todas las condiciones establecidas, se da clic en aceptar.

6. Si se desea quitar el formato condicional, se selecciona el rango de celdas y se da clic en el menú “formato” y luego en “formato condicional”, en la parte inferior aparece el botón “eliminar”, se pueden eliminar todas o solo una de las condiciones.

7. Si se desea encontrar celdas con formato condicional en una hoja de cálculo, se va al menú “Edición”, luego a “ir a”, y se da clic en el botón “especial”, se selecciona la opción “celdas con formatos condicionales” y se da clic en aceptar.

Diferencia entre ENCONTRAR y HALLAR

Las palabras encontrar y hallar son sinónimos, sin embargo cuando hablamos de la función ENCONTRAR y la función HALLAR nos damos cuenta de que son funciones muy similares pero que tienen una diferencia peculiar.

Las funciones ENCONTRAR y HALLAR

Ambas funciones pertenecen al grupo de las funciones de texto y devuelven la posición inicial donde se encuentra un texto buscado dentro de otra cadena de texto. Inclusive la sintaxis de las funciones es prácticamente idéntica:

ENCONTRAR(texto_buscado, dentro_del_texto, [núm_inicial])

HALLAR(texto_buscado, dentro_del_texto, [núm_inicial])

Puedes observar que las funciones tienen el mismo número de argumentos y además todos los argumentos se refieren a lo mismo: texto_buscado es el texto o palabras que deseamos buscar, dentro_del_texto es el texto principal donde realizaremos la búsqueda y núm_inicial es un parámetro opcional que nos indica el carácter donde se iniciará la búsqueda. Entonces, si ambas funciones son tan parecidas ¿Por qué Microsoft decidió incluir ambas?

Diferencia entre ENCONTRAR y HALLAR

Vamos a encontrar la diferencia entre ambas funciones con un ejemplo muy sencillo. En la siguiente imagen puedes observar que tengo la misma cadena en la celda A1 y A2. Y para ambas buscaré la palabra “excel” solo que para una fórmula utilizaré la función HALLAR y para la otra utilizaré la funcion Encontrar.

La función HALLAR encuentra que la palabra “excel” comienza en la posición 1 de la cadena, sin embargo la función ENCONTRAR nos devuelve un error #¡VALOR! indicando que no ha encontrado dicha palabra. La diferencia entre ambas funciones es sutil pero importante de recordar: La función ENCONTRAR es sensible a mayúsculas y minúsculas mientras que la función HALLAR no lo es.

Para comprobar que efectivamente la función ENCONTRAR es sensible a mayúsculas cambiaré las fórmulas para buscar la palabra “Excel” con la letra E como mayúscula. Observa los nuevos resultados:

Ahora ya lo sabes. La única diferencia entre las funciones ENCONTRAR y HALLAR es que la función ENCONTRAR es sensible a mayúsculas y minúsculas.

Calcular la edad con Excel

Calcular la edad con Excel puede llegar a ser un poco complicado porque los cálculos no solo dependen del año sino también del día actual. La solución se complica cuando te das cuenta que es necesario considerar los años bisiestos.

En esta ocasión presentaré tres maneras diferentes de calcular la edad con Excel. Todas las fórmulas asumen que la celda A2 contiene la fecha nacimiento y que la celda B2 tiene la fecha actual que es calculada por la función HOY.

Calcular la edad dividiendo los días

El primer método obtiene la diferencia en días entre ambas fechas y el resultado lo divide entre 365.25. Este último número es porque cada cuatro años tenemos un año con 366 días (bisiesto) por lo que 365.25 es el promedio de los cuatro años. La función ENTERO eliminará los decimales del resultado.

La desventaja de este método es que no es muy exacto y tiene problemas serios cuando intentas calcular la edad con Excel de niños. Por ejemplo, para un niño que tenga exactamente 1 año de edad cumplido obtendremos el resultado 0 (cero) por haber realizado la división entre 365.25.

Calcular la edad con la función FRAC.AÑO

La función FRAC.AÑO devuelve la fracción de un año a partir del número total de días que existen entre dos fechas. Observa el resultado:

De igual manera he utilizado la función ENTERO para remover los decimales del resultado.

Calcular la edad con la función SIFECHA

La función SIFECHA es el método más exacto para calcular la edad con Excel. Para obtener los años de diferencia entre dos fechas utilizamos el argumento “y” en la función:

Así que solamente elige el método que te parezca más adecuado para calcular la edad con Excel e impleméntalo.

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.