1
CONTENIDOS CURSO EXCEL INTERMEDIO.
1. CELDAS / RANGOS / HOJAS / LIBROS. REFERENCIAS.
2. RELLENO DE CELDAS EN EXCEL
Empleando números aleatorios.
Empleando listas personalizadas.
Empleando series.
3. TRABAJO CON CONDICIONES O CRITERIOS DE COMPARACIÓN EN EXCEL.
Condiciones. Operadores lógicos. Funciones Y; O
Función SI en Excel.
Formato condicional.
Funciones de suma y conteo condicional.
Función CONTAR, CONTAR.SI
Función SUMAR, SUMAR.SI 4. VALIDACIÓN DE DATOS.
Importancia de la validación de datos.
Opción de validación de datos.
5. OTRAS FUNCIONES DEL EXCEL.MATERIAL CURSO EXCEL INTERMEDIO
Función de redondeo.
Funciones de tratamiento de fechas
Funciones de tratamiento de textos.
2
RELLENO DE CELDAS EN EXCEL
Cómo generar números aleatorios en Excel
Los números aleatorios son utilizados en programas de simulación por computadora, en el muestreo estadístico, criptografía de datos y por supuesto en los juegos de azar. Cualquier área que necesite resultados impredecibles hará uso de los números aleatorios.
Para generar números aleatorios en Excel tenemos varias alternativas que podemos utilizar dependiendo de nuestras necesidades. A continuación revisaremos tres alternativas para obtener números aleatorios en Excel.
La función ALEATORIO
La manera más intuitiva de obtener un número aleatorio en Excel es utilizar la función ALEATORIO. Con tan solo llamar esta función obtendremos un número entre 0 y1:
Podrás generar un nuevo número aleatorio con tan solo pulsar la tecla F9.
La función ALEATORIO.ENTRE
Una variante de la función ALEATORIO es la función ALEATORIO.ENTRE que nos permite generar números aleatorios entre un rango especificado. Los argumentos de esta función son el límite inferior y superior de dicho rango.
Al igual con la función ALEATORIO, al pulsar la tecla F9 se generará un nuevo número aleatorio entre los límites especificados.
3
USAR LISTAS PERSONALIZADAS PARA AUTO RELLENAR
El auto relleno de Excel es muy útil para crear listas de datos de una manera rápida.
Por ejemplo, si necesitamos una lista con los días de la semana, solo tenemos que ingresar el nombre del primer día de la semana y arrastrar el controlador de relleno hacia abajo.
Recuerda que el controlador de relleno es el pequeño recuadro negro que se encuentra en la esquina inferior derecha de la celda activa. Al momento de posicionar el puntero del ratón sobre dicho controlador y arrastrarlo hacia abajo, activaremos el auto relleno de Excel y tendremos una lista de días como la que se observa en la siguiente imagen.
Hoy descubriremos cómo hace Excel para saber la secuencia de días correcta y además aprovecharemos esa funcionalidad para configurar nuestras propias listas de datos para el auto relleno.
Listas personalizadas para el auto relleno
La razón por la que Excel sabe la secuencia de datos con la que debe auto rellenar una lista, es porque tiene guardada dicha serie de valores como una lista personalizada.
Si quieres conocer las listas que Excel tiene configuradas por defecto, debes ir a la ficha Archivo > Opciones > Avanzadas y en el panel derecho deberás encontrar la sección General.
4
Debes hacer clic en el botón Modificar listas personalizadas y se mostrará una nueva ventana que contendrá las listas que Excel utiliza para el auto relleno.
5
En mi caso, puedes ver que se encuentran las listas de días y meses tanto en español como en inglés. Eso quiere decir que puedo colocar cualquiera de esos valores y al arrastrar el controlador de auto relleno, Excel completará automáticamente el resto de elementos de la lista.
Si quieres conocer cada uno de los elementos de las listas personalizadas, deberás seleccionarla en el panel izquierdo y sus elementos se mostrarán en el panel derecho.
Ya que las listas de la imagen anterior son las listas predeterminadas de Excel, el panel derecho se muestra inhabilitado, así que no podemos hacer modificación alguna sobre ellas, pero podemos crear nuestras propias listas para utilizarlas posteriormente con el auto relleno.
Crear una lista personalizada
Para crear una lista personalizada debes seleccionar la opción NUEVA LISTA dentro del panel izquierdo del cuadro de diálogo Listas personalizadas. De inmediato se habilitará el panel derecho y podrás ingresar cada elemento de la lista en una nueva fila.
La siguiente imagen muestra el momento en que estoy creando una lista que contiene una lista de países que utilizo frecuentemente. Al terminar de ingresar todos los elementos debemos pulsar el botón Agregar.
6
Ahora que hemos creado la lista personalizada, podré utilizar la función de auto relleno de Excel para crear rápidamente la lista de países tal como la necesito. En la siguiente imagen puedes ver que he ingresado el nombre del primer país de la lista y a su lado derecho de muestra el resultado después de arrastrar hacia abajo el controlador de relleno de la celda A1.
Si en este ejemplo, siguiera arrastrando el controlador de relleno más abajo de la celda A11, entonces Excel comenzaría de nuevo con el primer elemento de la lista.
7
También es importante mencionar que no es necesario comenzar siempre con el primer elemento de la lista, es suficiente con ingresar cualquier elemento y Excel continuará el relleno de las celdas con los elementos subsecuentes.
Listas personalizadas grandes
En nuestro ejemplo anterior fue muy fácil ingresar manualmente los elementos de la lista personalizada porque solo eran 12 países, pero ¿qué pasa si queremos crear una lista personalizada con 80 países?
Una tarea como esa sería muy laboriosa por la cantidad de elementos que tendríamos que ingresar manualmente, así que sería más cómodo el ingresarlos en una hoja de Excel y posteriormente importarlos a nuestra lista personalizada.
En la siguiente imagen puedes ver que la columna A tiene toda la lista de países con los cuales quiero crear mi listar personalizada y es precisamente dicho rango el que he especificado en el cuadro Importar lista desde las celdas.
8
Al pulsar el botón Importar se creará una lista personalizada con todos los elementos del rango indicado.
Si frecuentemente estás ingresando la misma lista de elementos en Excel, te conviene crear una lista personalizada para utilizarla con el auto relleno y de esa manera agilizar tu trabajo en Excel.
9 SERIES DE FECHAS
Es común trabajar con fechas en Excel y en más de una ocasión necesitaremos generar una serie de fechas es decir, una sucesión de fechas que cumpla con determinado patrón de comportamiento. Así que, es ampliamente recomendable conocer los comandos de Excel que nos ayudarán a crear series de fechas.
Cómo crear una serie de fechas
Para crear una serie de fechas será suficiente con ingresar el primer valor con el que iniciará nuestra serie. En la siguiente imagen se observa que he ingresado la fecha 01/01/2015 en la celda A1.
En seguida debemos ir a la ficha Inicio y dentro del grupo Modificar haré clic en el botón Rellenar y en seguida elegiré la opción Series.
Se mostrará el cuadro de diálogo Series donde podremos configurar algunos parámetros para la creación de nuestra serie de fechas. En primer lugar está el parámetro Series en que nos permite indicar si queremos insertar los valores de la serie sobre una fila o una columna, para nuestro ejemplo elegiré la opción Columnas. En seguida está el parámetro Tipo donde debemos elegir la opción Cronológica de manera que Excel sepa que estamos hablando de una serie de fechas.
10
Al elegir la opción Cronológica, se habilitará el parámetro Unidad de tiempo que nos permitirá especificar mayor detalle sobre nuestra serie. Para este primer ejemplo dejaré la selección en Fecha tal como se muestra en la imagen anterior.
Por último, debemos indicar el incremento y un límite para la serie. En nuestro ejemplo quiero que la serie de fechas termine en el último día del año y por lo tanto he colocado la fecha 31/12/2015. Además estoy pidiendo que el incremento sea de 1 es decir, la serie irá aumentando 1 día hasta llegar a la fecha límite.
Al pulsar el botón Aceptar se creará nuestra serie de fechas a partir de valor indicado en la celda A1. El resultado se puede observar en la siguiente imagen.
Los valores de fechas continuarán por todas las filas de la columna A hasta llegar a la fila 365 con la fecha 31/12/2015. Es importante recordar que es obligatorio indicar el límite para la serie o de lo contrario Excel no realizará acción alguna.
11 Otro ejemplo de serie de fechas
En este segundo ejemplo crearemos una serie de fechas de días laborables pero es importante definir lo que significa un día laborable en Excel para que no exista confusión alguna. De manera predeterminada, Excel considera la semana laboral de lunes a viernes así que al pedir la creación de una serie de fechas de días laborables se omitirán los días sábados y domingos.
Comenzaré por colocar la misma fecha de nuestro ejemplo anterior (01/01/2015) en la celda A1 y posteriormente iré a Inicio > Modificar > Rellenar > Serie. Los parámetros de creación de esta serie serán los mismos del ejemplo anterior a excepción de la unidad de tiempo, que en este ejemplo será la opción Día laborable.
En este caso nuestra lista de fechas no tendrá 365 filas porque se omitirán los sábados y domingos así que tendremos con una lista de 261 valores. Si revisas la lista podrás darte cuenta que las fechas corresponden a los días lunes a viernes de todas las semanas del año.
12 Serie de todos los lunes del año
Es posible generar una serie de fechas de un solo día específico de la semana. Por ejemplo, para crear una serie de fechas de todos los lunes del año 2015, deberás ingresar la fecha del primer lunes que es 05/01/2015 y a partir de dicha fecha crear una serie con los siguientes parámetros.
13
En este caso el incremento es de 7 días lo que ocasionará que la serie sea incrementada por una semana, y ya que la fecha inicial es un día lunes, terminaremos con una serie de fechas de todos los lunes del año.
Si necesitas crear una lista con todos los sábados y domingos del año tendrás que crear dos series de fechas en columnas diferentes, una serie para los sábados y otra para los domingos. Posteriormente deberás copiar una lista debajo de la otra y aplicar un ordenamiento ascendente para terminar con una sola serie de fechas.
14
NUMERAR FILAS AUTOMÁTICAMENTE EN EXCEL
Para numerar filas automáticamente en Excel tenemos dos alternativas: utilizar el controlador de relleno o utilizar la función FILA. A continuación te mostraré ambos métodos.
Números consecutivos en Excel
Antes de aprender a generar números consecutivos en Excel debemos aprender lo que es el Controlador de relleno. El controlador de relleno es el pequeño cuadro negro que está ubicado en la esquina inferior derecha de la celda activa. Observa la siguiente imagen:
Cuando colocas el puntero del ratón sobre el controlador de relleno, el puntero cambia a una cruz negra. El controlador de relleno es de mucha utilidad para generar números consecutivos en Excel y para hacerlo debemos seguir los siguientes pasos:
1. Selecciona la primera celda que tendrá un número y escribe el valor inicial.
Por ejemplo el número 1.
2. En la celda inferior escribe el siguiente valor de la serie lo cual establecerá un patrón de números a seguir. Escribe por ejemplo el número 2.
3. Selecciona ambas celdas y arrastra el controlador de relleno hacia abajo hasta llegar al número que deseas.
Observa la siguiente animación que dejará claro este procedimiento:
15
A medida que vamos arrastrando el controlador de relleno, Excel va insertando valores de acuerdo al patrón previamente establecido. Es por eso que es importante establecer al menos los dos primeros números de manera que Excel identifique adecuadamente el patrón a seguir. Por ejemplo, si en las primeras dos celdas coloco los números 2 y 4 al numerar las filas con el controlador de relleno obtendremos una serie de números diferente a la anterior:
Numerar filas con la función FILA
Otro método que podemos utilizar para numerar filas automáticamente en Excel es a través de la función FILA. Este método mostrará siempre el número de la fila de cada celda por lo que siempre tendremos un incremento lineal. Para este método también utilizamos el controlador de relleno para copiar la misma fórmula hacia abajo. Observa la siguiente animación:
16
Una ventaja de utilizar la función FILA es que la podemos combinar fácilmente con una cadena de caracteres para generar números consecutivos alfanuméricos.
Inclusive la función FILA nos puede ayudar a generar códigos de números consecutivos muy específicos como 000-001, 000-002, etc. Estos números pueden ser de mucha utilidad para indicar códigos de productos o números de orden de compra. Para obtener este tipo de numeración de filas debemos utilizar la siguiente fórmula:
=TEXTO(FILA(), "000-000")
Observa el resultado de esta fórmula:
17
18
TRABAJO CON CONDICIONES O CRITERIOS DE COMPARACIÓN EN EXCEL.
FUNCIONES LÓGICAS EN EXCEL
Las funciones lógicas en Excel se utilizan en la toma de decisiones. En base al resultado de una función decidiremos si ejecutar o no cierta acción requerida.
Encuentra el detalle y ejemplos de cada función haciendo clic sobre su nombre.
FUNCIÓN INGLÉS DESCRIPCIÓN
FALSO FALSE Devuelve el valor lógico FALSO.
NO NOT Cambia FALSO por VERDADERO y VERDADERO por FALSO.
O OR
Comprueba si alguno de los argumentos es VERDADERO y devuelve VERDADERO o FALSO.
Devuelve FALSO si todos los argumentos son FALSO.
SI IF
Comprueba si se cumple una condición y devuelve un valor si se evalúa como VERDADERO y otro valor si se evalúa como FALSO.
SI.ERROR IFERROR Devuelve un valor si la expresión es un error y otro valor si no lo es.
VERDADEROTRUE Devuelve el valor lógico VERDADERO.
Y AND
Comprueba si todos los argumentos son VERDADEROS y devuelve VERDADERO o FALSO. Devuelve FALSO si alguno de los argumentos es FALSO.
FUNCIÓN EXCEL FALSO
Nombre en inglés: FALSE
¿Qué hace?
Devuelve el valor lógico FALSO.
Sintaxis FALSO()
19 Ejemplos
FALSO() = FALSO FUNCIÓN EXCEL NO Nombre en inglés: NOT
¿Qué hace?
Invierte el valor lógico del argumento especificado.
Sintaxis
NO(valor_lógico)
valor_lógico (obligatorio): Valor o expresión a evaluar como FALSO o VERDADERO.
Ejemplos
NO(VERDADERO) = FALSO NO(FALSO) = VERDADERO
FUNCIÓN EXCEL VERDADERO
Nombre en inglés: TRUE
¿Qué hace?
Devuelve el valor lógico VERDADERO.
Sintaxis
VERDADERO() Ejemplos
VERDADERO() = VERDADERO
20
FUNCIÓN EXCEL O
Nombre en inglés: OR
¿Qué hace?
Regresa el valor VERDADERO si alguno de los parámetros es verdadero ó regresa FALSO si todos los parámetros son falsos.
La función O es una de las funciones lógicas de Excel y como cualquier otra función lógica solamente devuelve los valores VERDADERO o FALSO después de haber evaluado las expresiones lógicas que se hayan colocado como argumentos.
Sintaxis
O(valor_lógico1, [valor_lógico2], …)
valor_lógico1 (obligatorio): Primera condición a probar.
valor_lógico2 (opcional): Condiciones adicionales a probar.
La función O en Excel nos ayudará a determinar si al menos uno de los argumentos de la función es VERDADERO.
Valor_lógico1 (obligatorio): Expresión lógica que será evaluada por la función.
21
Valor_lógico2 (opcional): A partir del segundo argumento las expresiones lógicas a evaluar con opcionales hasta un máximo de 255.
La única manera en que la función O devuelva el valor FALSO es que todas las expresiones lógicas sean falsas. Si al menos una expresión es verdadera entonces el resultado de la función O será VERDADERO.
Ejemplos de la función O
Para comprobar el comportamiento de la función O haremos un ejemplo sencillo con la siguiente fórmula:
=O(1=2, 3>4, 5<>5, 7<=6, 8>=9)
Si analizas con detenimiento cada una de las expresiones verás que todas son falsas y por lo tanto la función O devolverá el valor FALSO. Observa el resultado:
Como mencioné anteriormente, la función O devolverá un valor VERDADERO si al menos una de las expresiones lógicas es verdadera. En nuestro ejemplo modificaré solamente la primera expresión para que sea 1=1 de manera que tenga la siguiente fórmula:
=O(1=1, 3>4, 5<>5, 7<=6, 8>=9)
Esto deberá ser suficiente para que la función O devuelva un valor VERDADERO:
Funciones como argumento de la función O
Podemos utilizar funciones como argumentos de la función O siempre y cuando devuelvan VERDADERO o FALSO como resultado. En el siguiente ejemplo utilizo las funciones ESNUMERO y ESTEXTO para evaluar el tipo de dato de las celdas B1 y B2.
22
Ya que la celda B1 es un número la función ESNUMERO regresa el valor VERDADERO. Por otro lado la celda B2 es efectivamente una cadena de texto y por lo tanto la función ESTEXTO devuelve el valor VERDADERO. En consecuencia la función O también regresa el valor VERDADERO. Ahora intercambiaré los valores de las celdas B1 y B2 de manera que tanto la función ESNUMERO como la función ESTEXTO devuelvan FALSO.
No olvides que la función O en Excel siempre devolverá VERDADERO excepto cuando TODAS las expresiones lógicas evaluadas sean falsas.
Ejemplos
O(VERDADERO, FALSO) = VERDADERO O(FALSO, FALSO) = FALSO
23
FUNCIÓN EXCEL Y
Nombre en inglés: AND
¿Qué hace?
Regresa el valor VERDADERO si todos los parámetros son verdaderos ó regresa FALSO si alguno de los parámetros es falso.
Sintaxis
Y(valor_lógico1, [valor_lógico2], …)
valor_lógico1 (obligatorio): La primera condición a evaluar como VERDADERO o FALSO.
valor_lógico2 (opcional): Las condiciones adicionales que se desean evaluar hasta un máximo de 255 condiciones.
Argumentos de la función Y
La función Y en Excel recibe como argumentos una serie de expresiones lógicas.
Si todas ellas son verdaderas, entonces la función Y devolverá el valor verdadero.
Si alguna de las expresiones lógicas es falsa, entonces la función Y devolverá el valor falso.
Todos los argumentos de la función Y deben ser expresiones que devuelven un valor lógico, es decir, VERDADERO o FALSO. Los argumentos pueden ser las expresiones mismas como se muestra en la siguiente imagen:
También pueden ser referencias a otras celdas que contienen los valores lógicos a considerar:
24
Y también pueden ser funciones que devuelven el valor VERDADERO o FALSO como es el caso de varias funciones de información en Excel por ejemplo la función ES.PAR:
Independientemente del tipo de argumentos que decidas utilizar debes recordar que la función Y acepta un máximo de 255 argumentos y cada uno de ellos debe devolver el valor VERDADERO o FALSO. La función Y devolverá VERDADERO solamente si todos y cada uno de los argumentos también devuelve el valor lógico VERDADERO. Podemos resumir el comportamiento de la función Y con la siguiente tabla:
Y(VERDADERO, VERDADERO) = VERDADERO
Y(VERDADERO, VERDADERO, FALSO) = FALSO
Observa la siguiente fórmula y te darás cuenta de que todas las expresiones son verdaderas:
=Y(5=5, 1<3, 8>6)
Ya que todas las expresiones son verdaderas, la función Y devolverá también el valor VERDADERO.
Ahora agregaré una cuarta expresión que será falsa: 7<>7. Observa cómo será suficiente para que la función Y devuelva el valor FALSO:
25 Argumentos de la función Y
Cada un de los argumentos de la función Y puede ser una expresión lógica como en los ejemplos anteriores pero también puede ser una función de Excel que devuelva un valor FALSO o VERDADERO.
Observa el siguiente ejemplo donde he combinado una expresión lógica con la función ES.PAR y la función ES.IMPAR las cuales evalúan un número para indicarnos si es par o impar respectivamente.
Ya que tanto la expresión lógica como las funciones son evaluadas como verdaderas, entonces la función Y nos un resultado VERDADERO. Ahora observa como al cambiar solamente el argumento de la función ES.IMPAR con el número 6 (en lugar de 5), causará que la función Y nos devuelva el valor FALSO.
Si alguna vez necesitas estar seguro de que todo un grupo de expresiones lógicas sean verdaderas, la función Y en Excel será una excelente opción para hacerlo.
Descarga el archivo utilizado en esta lección
26
FUNCIÓN EXCEL FALSO
Nombre en inglés: FALSE
¿Qué hace?
Devuelve el valor lógico FALSO.
Sintaxis FALSO() Ejemplos
FALSO() = FALSO FUNCIÓN EXCEL NO Nombre en inglés: NOT
¿Qué hace?
Invierte el valor lógico del argumento especificado.
Sintaxis
NO(valor_lógico)
valor_lógico (obligatorio): Valor o expresión a evaluar como FALSO o VERDADERO.
Ejemplos
NO(VERDADERO) = FALSO NO(FALSO) = VERDADERO
27
FUNCIÓN EXCEL VERDADERO
Nombre en inglés: TRUE
¿Qué hace?
Devuelve el valor lógico VERDADERO.
Sintaxis
VERDADERO() Ejemplos
VERDADERO() = VERDADERO
FUNCIÓN EXCEL O
Nombre en inglés: OR
¿Qué hace?
Regresa el valor VERDADERO si alguno de los parámetros es verdadero ó regresa FALSO si todos los parámetros son falsos.
La función O es una de las funciones lógicas de Excel y como cualquier otra función lógica solamente devuelve los valores VERDADERO o FALSO después de haber evaluado las expresiones lógicas que se hayan colocado como argumentos.
Sintaxis
O(valor_lógico1, [valor_lógico2], …)
valor_lógico1 (obligatorio): Primera condición a probar.
valor_lógico2 (opcional): Condiciones adicionales a probar.
La función O en Excel nos ayudará a determinar si al menos uno de los argumentos de la función es VERDADERO.
28
Valor_lógico1 (obligatorio): Expresión lógica que será evaluada por la función.
Valor_lógico2 (opcional): A partir del segundo argumento las expresiones lógicas a evaluar con opcionales hasta un máximo de 255.
La única manera en que la función O devuelva el valor FALSO es que todas las expresiones lógicas sean falsas. Si al menos una expresión es verdadera entonces el resultado de la función O será VERDADERO.
Ejemplos de la función O
Para comprobar el comportamiento de la función O haremos un ejemplo sencillo con la siguiente fórmula:
=O(1=2, 3>4, 5<>5, 7<=6, 8>=9)
Si analizas con detenimiento cada una de las expresiones verás que todas son falsas y por lo tanto la función O devolverá el valor FALSO. Observa el resultado:
Como mencioné anteriormente, la función O devolverá un valor VERDADERO si al menos una de las expresiones lógicas es verdadera. En nuestro ejemplo modificaré solamente la primera expresión para que sea 1=1 de manera que tenga la siguiente fórmula:
29
=O(1=1, 3>4, 5<>5, 7<=6, 8>=9)
Esto deberá ser suficiente para que la función O devuelva un valor VERDADERO:
Funciones como argumento de la función O
Podemos utilizar funciones como argumentos de la función O siempre y cuando devuelvan VERDADERO o FALSO como resultado. En el siguiente ejemplo utilizo las funciones ESNUMERO y ESTEXTO para evaluar el tipo de dato de las celdas B1 y B2.
Ya que la celda B1 es un número la función ESNUMERO regresa el valor VERDADERO. Por otro lado la celda B2 es efectivamente una cadena de texto y por lo tanto la función ESTEXTO devuelve el valor VERDADERO. En consecuencia la función O también regresa el valor VERDADERO. Ahora intercambiaré los valores de las celdas B1 y B2 de manera que tanto la función ESNUMERO como la función ESTEXTO devuelvan FALSO.
No olvides que la función O en Excel siempre devolverá VERDADERO excepto cuando TODAS las expresiones lógicas evaluadas sean falsas.
Ejemplos
O(VERDADERO, FALSO) = VERDADERO O(FALSO, FALSO) = FALSO
30
FUNCIÓN EXCEL Y
Nombre en inglés: AND
¿Qué hace?
Regresa el valor VERDADERO si todos los parámetros son verdaderos ó regresa FALSO si alguno de los parámetros es falso.
Sintaxis
Y(valor_lógico1, [valor_lógico2], …)
valor_lógico1 (obligatorio): La primera condición a evaluar como VERDADERO o FALSO.
valor_lógico2 (opcional): Las condiciones adicionales que se desean evaluar hasta un máximo de 255 condiciones.
Argumentos de la función Y
La función Y en Excel recibe como argumentos una serie de expresiones lógicas.
Si todas ellas son verdaderas, entonces la función Y devolverá el valor verdadero.
Si alguna de las expresiones lógicas es falsa, entonces la función Y devolverá el valor falso.
Todos los argumentos de la función Y deben ser expresiones que devuelven un valor lógico, es decir, VERDADERO o FALSO. Los argumentos pueden ser las expresiones mismas como se muestra en la siguiente imagen:
También pueden ser referencias a otras celdas que contienen los valores lógicos a considerar:
31
Y también pueden ser funciones que devuelven el valor VERDADERO o FALSO como es el caso de varias funciones de información en Excel por ejemplo la función ES.PAR:
Independientemente del tipo de argumentos que decidas utilizar debes recordar que la función Y acepta un máximo de 255 argumentos y cada uno de ellos debe devolver el valor VERDADERO o FALSO. La función Y devolverá VERDADERO solamente si todos y cada uno de los argumentos también devuelve el valor lógico VERDADERO. Podemos resumir el comportamiento de la función Y con la siguiente tabla:
Y(VERDADERO, VERDADERO) = VERDADERO
Y(VERDADERO, VERDADERO, FALSO) = FALSO
Observa la siguiente fórmula y te darás cuenta de que todas las expresiones son verdaderas:
=Y(5=5, 1<3, 8>6)
Ya que todas las expresiones son verdaderas, la función Y devolverá también el valor VERDADERO.
Ahora agregaré una cuarta expresión que será falsa: 7<>7. Observa cómo será suficiente para que la función Y devuelva el valor FALSO:
32 Argumentos de la función Y
Cada un de los argumentos de la función Y puede ser una expresión lógica como en los ejemplos anteriores pero también puede ser una función de Excel que devuelva un valor FALSO o VERDADERO.
Observa el siguiente ejemplo donde he combinado una expresión lógica con la función ES.PAR y la función ES.IMPAR las cuales evalúan un número para indicarnos si es par o impar respectivamente.
Ya que tanto la expresión lógica como las funciones son evaluadas como verdaderas, entonces la función Y nos un resultado VERDADERO. Ahora observa como al cambiar solamente el argumento de la función ES.IMPAR con el número 6 (en lugar de 5), causará que la función Y nos devuelva el valor FALSO.
Si alguna vez necesitas estar seguro de que todo un grupo de expresiones lógicas sean verdaderas, la función Y en Excel será una excelente opción para hacerlo.
Descarga el archivo utilizado en esta lección
33
LA FUNCIÓN SI EN EXCEL
https://exceltotal.com/la-funcion-si-en-excel/
La función SI en Excel es parte del grupo de funciones Lógicas y nos permite evaluar una condición para determinar si es falsa o verdadera. La función SI es de gran ayuda para tomar decisiones en base al resultado obtenido en la prueba lógica.
Sintaxis de la función SI
Además de especificar la prueba lógica para la función SI también podemos especificar valores a devolver de acuerdo al resultado de la función.
Prueba_lógica (obligatorio): Expresión lógica que será evaluada para conocer si el resultado es VERDADERO o FALSO.
Valor_si_verdadero (opcional): El valor que se devolverá en caso de que el resultado de la Prueba_lógica sea VERDADERO.
Valor_si_falso (opcional): El valor que se devolverá si el resultado de la evaluación es FALSO.
La Prueba_lógica puede ser una expresión que utilice cualquier operador lógico o también puede ser una función de Excel que regrese como resultado VERDADERO o FALSO.
Los argumentos Valor_si_verdadero y Valor_si_falso pueden ser cadenas de texto, números, referencias a otra celda o inclusive otra función de Excel que se ejecutará de acuerdo al resultado de la Prueba_lógica.
34 Ejemplos de la función SI
Probaremos la función SI con el siguiente ejemplo. Tengo una lista de alumnos con sus calificaciones correspondientes en la columna B. Utilizando la función SI desplegaré un mensaje de APROBADO si la calificación del alumno es superior o igual a 60 y un mensaje de REPROBADO si la calificación es menor a 60. La función que utilizaré será la siguiente:
=SI(B2>=60,"APROBADO","REPROBADO")
Observa el resultado al aplicar esta fórmula en todas las celdas de la columna C.
Utilizar una función como prueba lógica
Es posible utilizar el resultado de otra función como la prueba lógica que necesita la función SI siempre y cuando esa otra función regrese como resultado VERDADERO o FALSO. Un ejemplo de este tipo de función es la función ESNUMERO la cual evalúa el contenido de una celda y devuelve el valor VERDADERO en caso de que sea un valor numérico. En este ejemplo quiero desplegar la leyenda “SI” en caso de que la celda de la columna A efectivamente tenga un número, de lo contrario se mostrará la leyenda “NO”.
=SI(ESNUMERO(A2), "SI", "NO")
Este es el resultado de aplicar la fórmula sobre los datos de la hoja:
35 Utilizar una función como valor de regreso
Como último ejemplo mostraré que es posible utilizar una función para especificar el valor de regreso. Utilizando como base el ejemplo anterior, necesito que en caso de que la celda de la columna A contenga un valor numérico se le sume el valor que colocaré en la celda D1. La función que me ayudará a realizar esta operación es la siguiente:
=SI(ESNUMERO(A2), SUMA(A2, $D$1), "NO")
Como puedes observar, el segundo argumento es una función la cual se ejecutará en caso de que la prueba lógica sea verdadera. Observa el resultado de esta fórmula:
36
Sólo en los casos donde la función SI era verdadera se realizó la suma. De la misma manera podríamos colocar una función para el tercer argumento en caso de que el resultado de la prueba lógica fuera falso.
Función SI anidada
La función SI es una de las funciones más utilizadas en Excel ya que nos ayuda a probar si una condición es verdadera o falsa. Si la condición es verdadera, la función realizará una acción determinada, pero si la condición es falsa entonces la función ejecutará una acción diferente.
Es así como la función SI nos permite evaluar el resultado de una prueba lógica y tomar una acción en base al resultado. La siguiente imagen ilustra el funcionamiento de la función SI.
El diagrama anterior nos deja en claro que la función SI evaluará como máximo una prueba lógica y podremos ejecutar hasta dos posibles acciones.
Un ejemplo que puede ser atendido perfectamente por la función SI es el siguiente.
Supongamos que tenemos un listado de edades y en una nueva columna debemos colocar la leyenda “Menor de edad” en caso de que la edad de la persona sea menor de 18 años, pero si la edad es igual o mayor a 18, entonces debemos colocar la leyenda “Mayor de edad”.
La solución a nuestro problema será desplegar dos leyendas diferentes, es decir, necesitamos ejecutar dos acciones diferentes en base a la prueba lógica de mayoría de edad. Este problema se resuelve fácilmente utilizando la función SI de la siguiente manera:
37
=SI(A2 < 18, "Menor de edad", "Mayor de edad")
La función SI mostrará el primer mensaje solamente si la prueba lógica (A2<18) es verdadera. Si dicha prueba lógica es falsa, entonces se mostrará el segundo mensaje. Observa el resultado de aplicar esta función en nuestros datos de ejemplo:
La función SI anidada en Excel
El problema anterior lo resolvimos fácilmente con la función SI porque el resultado final serían solamente dos acciones, pero ahora imagina que necesitamos desplegar la leyenda “Tercera edad” cuando la persona tenga 65 años o más. Esto nos deja el problema con las siguientes reglas:
Menor a 18 años: “Menor de edad”
Mayor a 18 años y menor de 65 años: “Mayor de edad”
Mayor o igual a 65 años: “Tercera edad”
Ahora tenemos tres posibles acciones, y la función SI no podrá resolver por sí sola este problema, por lo que necesitamos recurrir a la función SI anidada la cual nos permitirá resolver cualquier situación en las que necesitemos evaluar más de una prueba lógica y ejecutar más de dos acciones.
La siguiente imagen muestra el funcionamiento de la función SI anidada. Observa que la clave es que, en lugar de ejecutar una segunda acción, la primera función SI incluye una segunda función SI de manera que entre ambas funciones puedan ejecutar un máximo de tres acciones.
38
La función SI anidada en Excel aumenta la flexibilidad de la función al ampliar el número de posibles resultados a probar así como las acciones que podemos ejecutar. Para nuestro ejemplo, la función SI anidada que resolverá adecuadamente el problema será la siguiente:
=SI(A2 < 18, "Menor de edad", SI(A2 < 65, "Mayor de edad", "Tercera edad"))
Pon especial atención al tercer argumento de la primera función SI, que, en lugar de ser una acción, se ha convertido en otra función SI que a su vez tendrá la posibilidad de ejecutar otras dos acciones.
Un error muy común en la creación de una fórmula que utilice la función SI anidada es intentar utilizar la primera función SI con cuatro argumentos, lo cual ocasionará un error. La primera función SI deberá “renunciar” a una de sus acciones para darnos la posibilidad de utilizar otra función SI.
Excel hará el análisis de la fórmula anterior de la siguiente manera. Comenzando con la primera función SI, se evaluará si la edad es menor a 18, en caso de que dicha condición se cumpla, se mostrará la leyenda “Menor de edad” y todo terminará ahí. Por el contrario, si la primera condición no se cumple, querrá decir que la edad es mayor o igual a 18 y se ejecutará la segunda función SI.
39
En la segunda prueba lógica probaremos si la edad es menor a 65, en caso de ser verdadero se imprimirá la leyenda “Mayor de edad”, pero si la prueba lógica es falsa, querrá decir que la edad es mayor o igual a 65 y por lo tanto se imprimirá la etiqueta
“Tercera edad”. Observa el resultado de aplicar esta fórmula sobre nuestros datos de ejemplo.
Otro ejemplo de función SI anidada
Más que otro ejemplo de la función SI anidada en Excel, quiero resaltar el hecho de que no todas las fórmulas creadas serán iguales aún para el mismo problema ya que todo dependerá de la lógica que decidas implementar. Por ejemplo, el mismo ejercicio de la sección anterior lo podremos resolver con la siguiente fórmula:
=SI(A11 >= 65, "Tercera edad", SI(A11 >= 18, "Mayor de edad", "Menor de edad"))
En esta fórmula la lógica utilizada es completamente inversa a la de nuestra fórmula anterior, es decir, la primera función SI evaluará si la edad es mayor o igual a 65 para imprimir la leyenda “Tercera edad”, en caso contrario se realizará la prueba lógica para saber si es mayor o igual a 18 años.
En la siguiente imagen puedes ver que he colocado esta fórmula a un lado de nuestro primer ejemplo y el resultado es exactamente el mismo.
40
De esta manera podemos ver que existen diversas maneras de resolver el problema y muy probablemente la fórmula resultante será diferente para cada usuario de Excel. Así que, si eres un usuario nuevo en Excel, te recomiendo ampliamente dedicar el tiempo suficiente para decidir las pruebas lógicas que utilizarás en cada función SI. También te recomiendo solucionar el problema en papel antes de intentar escribir la fórmula directamente en Excel.
En más de una ocasión he visto que los errores presentados en una función SI anidada provienen de una mala implementación de la lógica utilizada.
Adicionalmente a esto, la poca experiencia en el uso de los operadores de comparación en Excel ocasionará diversos problemas al construir adecuadamente las pruebas lógicas utilizadas por la función SI.
Si crees conveniente refrescar un poco tus conocimientos sobre dichos operadores, te recomiendo consultar el video tutorial Tipos de operadores en Excel poniendo especial atención a la sección de operadores de comparación.
Varias funciones SI anidadas en Excel
En nuestros ejemplos anteriores he utilizado solamente dos funciones SI anidadas, pero Excel nos permite anidar muchas más funciones. Originalmente solo se aceptaban hasta 8 funciones anidadas pero a partir de Excel 2010, es posible anidar hasta 64 funciones SI.
Aunque actualmente el máximo es de 64 funciones anidadas, no llegarás ni a la mitad de ese límite cuando comenzarás a tener dificultad en entender la lógica empleada. Sin embargo, es importante que conozcas dicha limitante en Excel y recordar que es imposible sobrepasar dicho límite. Lo volveré a repetir porque es
41
una pregunta muy frecuente: recuerda que es imposible hacer que Excel exceda el límite de funciones anidadas establecido por el fabricante.
Sin embargo, mientras tanto y no sobrepases el límite establecido por Excel, podrás anidar tantas funciones como necesites y lo único que deberás hacer será reemplazar una acción de la función SI por otra función SI. Para dejar esto en claro, hagamos un último ejemplo donde tenemos un grupo de puntuaciones de 100 a 999 que evaluaremos de la siguiente manera:
100 a 599: Malo
600 a 749: Promedio
750 a 899: Bueno
900 a 999: Excelente
Una alternativa de solución para este problema es la siguiente función SI anidada:
=SI(A2<600,"Malo",SI(A2<750,"Promedio",SI(A2<900,"Bueno","Excelente")))
En este caso tengo tres funciones SI anidadas porque la cantidad de acciones que necesito ejecutar son cuatro. Así que de manera general, la cantidad de funciones SI que utilizarás en tu fórmula será una menos que la cantidad de acciones que necesitas ejecutar.
También observa que todas las funciones SI van “anidando” otra función SI como uno de sus argumentos y solamente la función SI más anidada será la que tendrá sus dos acciones porque ya no habrá otra función SI que anidar. Observa el resultado de nuestra fórmula:
42
La realidad es que la práctica es el mejor método para perfeccionar el uso de la función SI anidada en Excel. Así que, te sugiero comenzar con los ejemplos publicados en este artículo y posteriormente seguir practicando con tus propios ejercicios.
FORMATO CONDICIONAL EN EXCEL
El formato condicional en Excel es una funcionalidad de gran utilidad al
momento de realizar el análisis de datos ya que puedes dar un formato especial a un grupo de celdas en base al valor de otra celda. Esto te permitirá aplicar un tipo de fuente específico o un color de relleno diferente para aquellas celdas que cumplan con ciertas reglas y así poder identificarlas fácilmente en pantalla. Esta es una serie de artículos que te ayudará a comprender mejor este concepto.
1. Fundamentos de formato condicional 2. Resaltar celdas con formato condicional
3. Aplicar formato condicional a valores duplicados o valores únicos 4. Reglas superiores e inferiores para analizar datos
5. Resaltar fines de semana con formato condicional 6. Resaltar filas alternas
7. Buscar valores usando formato condicional 8. Borrar reglas de formato condicional
Con la lectura de estos artículos tendrás una mejor idea sobre el formato condicional en Excel lo cual mejorará tus habilidades con la herramienta y estarás en el camino correcto para convertirte pronto en un usuario avanzado.
43
FUNDAMENTOS DE FORMATO CONDICIONAL
El formato condicional en Excel es una manera de hacer que la herramienta aplique un formato especial para aquellas celdas que cumplen con ciertas condiciones. Por ejemplo, se puede utilizar el formato condicional para cambiar el color de las celdas que tienen un valor negativo.
Razón para utilizar el formato condicional
Siempre tienes la opción de aplicar manualmente un formato a cada una de las celdas que cumplen con una condición, pero eso se puede convertir en un proceso largo y repetitivo, especialmente si tienes una tabla de datos muy grande y que cambia frecuentemente. Es por eso que el formato condicional puede hacer más fácil la tarea de cambiar automáticamente el formato de la celda que cumple con ciertos criterios.
Ejemplos de formato condicional
Formato especial para todos los valores entre 20 y 30:
Resaltar los valores por debajo del promedio (Para este ejemplo el promedio es 22.2).
44
¿Cómo se aplica el formato condicional?
Debes selccionar las celdas a las que se les aplicará el formato condicional y después hacer clic en el comando Formato condicional que aparece en el grupo Estilos de la ficha Inicio.
Como ya mencioné previamente, al seleccionar alguna de las opciones del menú, el formato se aplicará para aquellas celdas que cumplan con cierta condición o regla. Un formato condicional en Excel está siempre basado en una regla que posteriormentre se podrá editar si así lo deseas. Las reglas que se crean para los formatos condicionales se pueden dividir en dos grandes grupos:
45
Reglas basadas en valores de celda: Estas reglas se basan en el mismo valor de la celda (Mayor que, Menor que, Igual a, Entre, etc.).
Reglas basadas en fórmulas: Estas reglas ofrecen mayor flexibilidad porque puedes aplicar un formato especial utilzando una fórmula donde podrás aplicar una lógica más compleja. Por lo mismo es un poco más complicado de aprender, pero una vez que lo hagas seré muy intuitivo de utilizar.
RESALTAR CELDAS CON FORMATO CONDICIONAL
En el artículo fundamentos de formato condicional hemos aprendido las bases sobre este tema y en esta publicación aprenderemos sobre la primera de las opciones de menú de formato condicional: Resaltar reglas de celdas.
Esta opción de menú nos dará la oportunidad de destacar celdas que cumplan con la regla de formato condicional especificada. Cada opción muestra su propio cuadro de diálogo solicitando los argumentos necesarios para crear la regla.
46 Reglas de formato condicional
La opción Es mayor que muestra el siguiente cuadro de diálogo:
En el primer cuadro de texto deberás colocar un número a partir del cual se aplicará el formato especificado. Para este ejemplo coloqué el número 50 en la caja de texto, dejé el formato predeterminado y el resultado fue el siguiente:
Las opciones Es menor que, Es igual a, Texto que contiene, esperan que ingreses un solo valor con el cual comparar las celdas a las que se aplicará el formato. La opción Entre muestra un cuadro de diálogo diferente porque solicita 2 valores que delimitarán el rango de valores a buscar:
La opción Una fecha muestra una lista de opciones de donde podrás seleccionar la que mejor se adapte a tus necesidades.
47
Por supuesto, es necesario que tus celdas tengan datos de tipo fecha de manera que esta regla de formato condicional pueda encontrar valores coincidentes.
Finalmente la opción Duplicar valores te permitirá resaltar ya sea los valore únicos ó los valores duplicados dentro de tus datos:
Solamente debes elegir la opción adecuada de la lista de selección y se aplicará la regla de formato condicional adecuada.
APLICAR FORMATO CONDICIONAL A VALORES DUPLICADOS O VALORES ÚNICOS
En esta ocasión te mostraré cómo aplicar formato condicional a aquellas celdas que contienen valores únicos dentro de una lista de datos o por el contrario, cómo aplicar formato condicional a valores duplicados en Excel.
Formato condicional a valores duplicados
Comenzaremos nuestro ejercicio seleccionando el rango de celdas que contiene los datos a los que aplicaremos el formato condicional y que en este caso es el rango A2:A26.
48
Después de haber hecho la selección, debemos ir a la ficha Inicio, y dentro del grupo Estilos, hacer clic sobre Formato Condicional y al desplegarse el menú emergente deberás hacer clic en Resaltar reglas de celdas y posteriormente elegir la opción Duplicar valores tal como se muestra en la siguiente imagen.
49
Al seleccionar esta opción se mostrará el cuadro de diálogo Duplicar Valores el cual contiene dos listas de selección y deberás asegurarte de que la primera lista tiene la selección Duplicados. Para la segunda lista podrás elegir cualquiera de las opciones disponibles y para nuestro ejemplo dejaré seleccionada la opción de relleno rojo con texto rojo oscuro.
Al hacer clic en el botón Aceptar, la mayoría de las celdas de nuestros datos serán resaltadas en color rojo porque contienen un valor que se repite al menos dos veces.
La única celda que no será resaltada en rojo será las celdas A5 porque es la única que contiene un valor que no se repite.
50
De esta manera podrás resaltar valores duplicados en Excel utilizando el formato condicional de manera que sea fácil detectar visualmente aquellas celdas que contienen datos duplicados.
En la segunda parte de nuestro ejercicio modificaremos la regla de formato condicional recién creada de manera que se aplique el formato condicional a los valores únicos de nuestro listado.
Formato condicional a valores únicos
Para invertir el funcionamiento de nuestra regla de formato condicional de manera que el formato se aplique a los valores únicos de nuestros datos haremos una pequeña modificación. Pero antes, debemos seleccionar de nueva cuenta el rango de celdas con los datos y hacer clic en la ficha Inicio > Formato condicional y seleccionaremos la opción Administrar reglas.
51
Esto mostrará el cuadro de diálogo Administrador de reglas de formato condicionales donde podrás observar la regla que acabamos de crear en la sección anterior.
Para editar la regla de formato condicional debemos seleccionarla y posteriormente hacer clic en el botón Editar regla lo cual mostrará el cuadro de diálogo Editar regla de formato. En este nuevo cuadro de diálogo debemos seleccionar la opción “único” dentro de la lista desplegable tal como se muestra en la siguiente imagen.
52
Para finalizar deberás hacer clic en el botón Aceptar y de nuevo haz clic en Aceptar para cerrar el Administrador de reglas de formato condicional. Como resultado tendremos una regla que resaltará solamente la celda A5 con un formato especial por ser el único valor que no se repite.
53
Es así como el Formato condicional en Excel puede ser utilizado para resaltar valores únicos o valores duplicados. Aunque en este ejercicio he utilizado una misma regla de formato condicional para demostrar ambas tareas, es totalmente factible crear reglas independientes que resalten valores duplicados o valores únicos.
Ambos tipos de reglas se crean de la misma manera, es decir, desde Inicio >
Formato condicional > Resaltar reglas de celdas > Duplicar valores y al abrirse el cuadro de diálogo deberás elegir la opción “duplicado” o “único” en base al tipo de datos que deseas resaltar.
54
REGLAS SUPERIORES E INFERIORES PARA ANALIZAR DATOS
Después de haber introducido los conceptos fundamentales de formato condicional, ahora analizaremos con un poco más de detenimiento algunos tipos de reglas de formato condicional especiales. Estas reglas se muestran dentro del menú Formato condicional y dentro de la opción Reglas superiores e inferiores.
Reglas superiores e inferiores de formato condicional
La regla 10 superiores te permitirá resaltar de manera inmediata las celdas que contengan los 10 valores con mayor valor dentro del rango de celdas. El cuadro de diálogo de esta regla es el siguiente:
55
Observa que puedes cambiar el número de elementos a seleccionar, por lo que en lugar de los 10 superiores podrías seleccionar los 20 superiores si así lo deseas. La regla 10 inferiores funciona de manera similar, solo que Excel obtendrá las celdas con menor valor dentro del rango.
La regla 10% de valores superiores es similar a las reglas anteriores, pero en lugar de indicar un número específico de celdas se introduce un porcentaje de celdas a seleccionar. Por ejemplo, dentro de un rango que contiene 20 celdas, si se especifica un 10% entonces el formato condicional se aplicará a las 2 celdas con mayor valor porque el 10% de 20 celdas son 2 celdas. El cuadro de diálogo es el siguiente:
De la misma manera puedes aumentar o disminuar el porcentaje. Si especificas un 20% para un rango de 20 celdas, entonces el formato condicional se aplicará a 4 celdas. La regla 10% de valores inferiores selecciona el 10% de celdas del rango que tenga el menor valor.
Finalmente la regla Por encima del promedio hace dos cosas. En primer lugar calcula el valor promedio de las celdas del rango seleccionado y posteriormente aplica el formato condicional a todas aquellas celdas que tienen un valor por encima del promedio recién calculado. Al no tener nigún argumento, el cuadro de diálogo de esta regla solamente solicta el formato a aplicar:
La regla Por debajo del promedio seleccionará todas las celdas que tengan un valor inferior al promedio. También puedes consultar el artículo Resaltar celdas con formato condicional para conocer más sobre otras reglas de formato condicional.
56
RESALTAR FINES DE SEMANA CON FORMATO CONDICIONAL
Cuando tienes un listado de fechas en Excel a veces es de utilidad resaltar los días que pertenecen al fin de semana, ya sea porque estás creando un calendario laboral o simplemente porque necesitas conocer las fechas de los sábados y domingos.
Esta tarea la puedes lograr fácilmente aplicando una regla de formato condicional sobre el listado de fechas. Para nuestro ejercicio supondremos una lista de fechas como la siguiente:
El primer paso será seleccionar todo el rango de fechas y posteriormente ir a la ficha Inicio y hacer clic sobre Formato condicional para seleccionar la opción Nueva regla:
57
Formato condicional para resaltar fines de semana
Se mostrará el cuadro de diálogo Nueva regla de formato y deberás elegir la opción Utilice una fórmula que determine las celdas para aplicar formato tal como lo muestra la siguiente imagen:
58
En el cuadro de fórmula deberás colocar lo siguiente:
=DIASEM(A1, 2) > 5
Ahora solo falta indicar el color del fondo de las celdas de fin de semana. Haz clic en el botón Formato y elige la pestaña Relleno donde podrás seleccionar el color de tu preferencia para resaltar las fechas que cumplan con la fórmula que acabamos de ingresar.
La configuración de la nueva regla de formato condicional quedará de la siguiente manera:
59
Al hacer clic sobre Aceptar, las celdas que contengas una fecha de fin se semana se resaltarán con el color previamente definido:
60 Explicación de la fórmula
En primer lugar debemos recordar que la regla condicional que acabamos de crear aplicará el formato cuando el resultado de la fórmula sea verdadero así que conviene analizar detenidamente dicha fórmula.
De manera predeterminada la función DIASEM devuelve un número del 1 al 7 para cada día de la semana comenzando por el día domingo, pero si queremos que el día lunes sea considerado como el primer día de la semana, entonces debemos colocar el número 2 como el segundo argumento de la función:
=DIASEM(A1, 2)
De esta manera, el valor numérico devuelto por la función corresponderá a los días de la semana de la siguiente manera:
1. Lunes 2. Martes 3. Miércoles 4. Jueves 5. Viernes 6. Sábado 7. Domingo
Es por eso que la fórmula de la regla de formato condicional que acabamos de crear, compara el valor devuelto de la función DIASEM con el valor 5. Si el valor devuelto es mayor a 5, querrá decir que tenemos el día sábado o domingo y deberá aplicar el formato condicional.
Si quisieras resaltar solamente el día domingo, podrías modificar la fórmula de la regla de formato condicional de la siguiente manera:
=DIASEM(A1, 2) = 7
Realiza este ejercicio con tu propia lista de fechas y comprueba lo fácil que es resaltar cualquier día de la semana en Excel con una regla de formato condicional.
61
RESALTAR FILAS ALTERNAS
Muy a menudo, cuando presentamos los datos en una tabla muy grande, se dificulta distinguir la información que pertenece a una misma fila. Para ayudar en la lectura de los datos es posible resaltar las filas alternas en Excel de manera que cada fila tenga un color diferente que nos permita distinguir fácilmente la información entre cada una de ellas.
Es posible aplicar este tipo de formato convirtiendo el rango de datos en una tabla de Excel y que será tan simple como seleccionar el rango de celdas e ir a la ficha Inicio > Estilos > Dar formato como tabla y finalmente seleccionar cualquiera de los formatos que tienen un color diferente para las filas.
62
Sin embargo, en esta ocasión te mostraré un método adicional para resaltar filas alternas en Excel utilizando el formato condicional.
Resaltar filas alternas en Excel
El primer paso es seleccionar los datos a los que aplicaremos la regla de formato condicional.
En seguida debemos ir a la ficha Inicio y dentro de Formato condicional deberás seleccionar la opción Nueva regla lo cual mostrará el cuadro de diálogo Nueva regla de formato.
63 Fórmula para resaltar filas alternas
Dentro del cuadro de diálogo deberás seleccionar la opción Utilizar una fórmula que determine las celdas para aplicar formato y en el cuadro Dar formato a los valores donde esta fórmula sea verdadera coloca la siguiente fórmula:
=RESIDUO(FILA(),2)=0
Solo en caso de que tengas Excel 2010 y aún no hayas aplicado el Service Pack para Office 2010, deberás utilizar la función RESTO en lugar de la función RESIDUO. Las funciones son exactamente las mismas y solo se debe a un cambio
“temporal” en el nombre de la función en Excel 2010 el cual se corregirá al instalar el Service Pack.
La fórmula que acabamos de introducir hace que cualquier fila que sea par, dentro del rango de celdas seleccionado, se le aplicará el formato que elijamos. En caso de que quieras que sean las filas impares las que tengan el formato especial entonces deberás modificar la fórmula de la siguiente manera:
=RESIDUO(FILA(),2)=1
Por otro lado, si lo que quieres es resaltar las columnas alternas dentro del rango, entonces puedes reemplazar la función FILA por la función COLUMNA:
=RESIDUO(COLUMNA(),2)=0
64
Una ventaja de este método es que puedes elegir la cantidad de filas que habrá entre cada cambio de formato. Por ejemplo, si deseamos resaltar cada 3 filas, podemos utilizar la siguiente fórmula:
=RESIDUO(FILA(),3)=0
Ya he explicado variantes de la fórmula que podemos indicar en nuestra regla de formato condicional pero nos hace falta algo muy importante y es elegir el formato que será aplicado.
Para definir el formato que tendrán las filas alternas deberás dar clic en el botón Formato que está por debajo del cuadro de texto donde acabamos de introducir la fórmula. De inmediato se mostrará un nuevo cuadro de diálogo llamado Formato de celdas y es aquí donde elegiremos un color de relleno diferente.
De hecho puedes ir a cualquiera de las pestañas de este cuadro de diálogo (Número, Fuente, Bordes, Relleno) y elegir el formato que será aplicado a las filas alternas. Para nuestro ejemplo solo elegiré un color de fondo diferente y al terminar
65
haré clic en el botón Aceptar y posteriormente de nuevo en Aceptar para cerrar el cuadro de diálogo Editar regla de formato.
La nueva regla de formato condicional será aplicada sobre los datos previamente seleccionados y se verán de la siguiente manera.
Ahora ya conoces dos métodos para resaltar filas alternas en Excel. Ya sea aplicando un formato de tabla a los datos o creando una regla de formato condicional que hará uso de la función RESIDUO para aplicar el formato a las filas alternas.
BUSCAR VALORES USANDO FORMATO CONDICIONAL
Seguramente utilizas las hojas de Excel para almacenar grandes cantidades de datos, como el catálogo de clientes de la empresa, el registro de las ventas de productos, etc. Con esa gran cantidad de información es indispensable encontrar
los datos precisos en todo momento.
Buscar en Excel con formato condicional
En esta ocasión te mostraré cómo utilizar el formato condicional para buscar dentro de una hoja de Excel y resaltar las celdas coincidentes. Observa la siguiente imagen:
66
Para este ejemplo utilizaré los datos de la columna B y utilizaré la celda B2 como el cuadro de búsqueda, es decir, la celda donde colocaré el término/texto que deseo encontrar en los datos. Ahora debemos crear la regla que aplique el formato condicional y para eso se deben seleccionar las celdas con los datos e ir al comando Formato condicional y seleccionar la opción Nueva regla:
67
Se mostrará el cuadro de diálogo Nueva regla de formato:
Fórmula para encontrar valores
Selecciona la opción Utilice una fórmula que determine las celdas para aplicar formato. En la caja de texto coloca la siguiente fórmula, que explicaré en un instante:
=Y($B$2<>"",ENCONTRAR(MINUSC($B$2), MINUSC(B4)))
También haz clic en el botón Formato… para seleccionar el formato que se aplicará a las celdas que cumplan la regla.
68
La fórmula que acabas de escribir compara el texto de la celda de búsqueda ($B$2) con las celdas que contienen los datos. La comparación se hace a través de la función ENCONTRAR que compara ambos valores. Puedes observar también que para ambos valores utilicé la función MINUSC que los convierte en minúsculas antes de hacer la comparación de manera que la búsqueda no sea sensible a mayúsculas y minúsculas. Finalmente, la intención de la función Y es evitar que el formato se aplique a todas las celdas cuando la celda $B$2 esté vacía.
Ahora haz clic en el botón Aceptar y podrás comenzar a encontrar los valores que introduzcas en la celda de búsqueda:
Consulta más información sobre las funciones de Excel utilizadas en esta publicación: ENCONTRAR, MINUSC, Y
69
BORRAR REGLAS DE FORMATO CONDICIONAL
Una vez que has analizado la información y aplicado algún formato condicional es probable que desees retornar al estado inicial de la hoja de Excel, es decir, remover cualquier formato condicional aplicado a los datos.
Eliminar formato condicional de celdas seleccionadas
Una manera de remover los formatos condicionales es seleccionar las celdas que tienen el formato que deseas eliminar y hacer clic sobre Formato condicional y seleccionar la opción Borrar reglas y posteriormente Borrar reglas de las celdas seleccionadas.
Excel quitará de inmediato cualquier formato condicional que esté aplicado sobre las celdas seleccionadas.
Eliminar formato condicional de todas las celdas
El segundo método para remover los formatos es seleccionar la segunda opción del menú previamente mencionado: Borrar reglas de toda la hoja. Al seleccionar esta opción debes estar seguro de que no necesitas ningún formato condicional en toda la hoja de Excel ya que se borrarán todos y cada uno de ellos.
70
Eliminar algunas reglas de formato condicional
El último método que te mostraré se debe utilizar en caso de tener varias reglas de formato y solamente querer remover una sola de ellas. De igual manera debes seleccionar las celdas que tienen el formato y hacer clic en Formato condicional y posteriormente en la opción Administrar reglas, lo cual mostrará el siguiente cuadro de diálogo:
Este cuadro de diálogo contiene todas las reglas creadas para las celdas seleccionadas. Elige la regla que deseas borrar y haz clic en el botón Eliminar regla y posteriormente en el botón Aceptar. Excel removerá la regla seleccionada y dejará el resto de las reglas tal como estaban siendo aplicadas sobre los datos.
El formato condicional en Excel nos permite aplicar de manera selectiva un formato a las celdas de acuerdo a su contenido. Este formato se aplicará de manera automática sobre la celda después de evaluar el cumplimiento de la regla de formato condicional.
Para qué sirve el formato condicional
Es muy común entre los usuarios de Excel querer cambiar la apariencia de una celda en base a su valor o al resultado de otra fórmula. Sin embargo, no existe una función de Excel que nos permita cambiar el color de fondo de una celda o el color de la fuente y por esa razón se creó el formato condicional en Excel que nos permite evaluar el valor de una celda para decidir si se aplicará o no el formato definido.
71
Es así como el formato condicional nos sirve para detectar de una manera visual a aquellas celdas que cumplen con una condición establecida. Cada vez que cambiemos el valor de una celda, Excel volverá a hacer la evaluación de la regla para saber si debe seguir aplicando el formato.
Reglas de formato condicional
Podemos definir una regla de formato condicional como la condición que debe cumplirse para que Excel aplique el formato a una celda, si la regla no se cumple entonces no se hará ningún cambio a la apariencia de la celda. Una regla de formato condicional nos ayuda a especificar dos cosas:
El criterio de decisión para saber si se debe aplicar el formato a una celda.
El formato que se debe aplicar en caso de que se cumpla la regla.
Cada regla de formato condicional se puede aplicar a una sola celda o a un rango de celdas y una misma celda puede tener varias reglas de formato condicional aplicadas sobre ella pero será visible aquella regla que tenga la mayor prioridad.
Dicha prioridad se define dentro del Administrador de reglas de formato condicionales.
