Excel - Leccion 8

8

Utilizar fórmulas más avanzadas MATRIZ DE HABILIDADES DE LA LECCION H ABILIDADES

H ABILIDAD

Utilizar fórmulas para resumir condicionalmente los datos.

Utilizar las funciones SUMAR.SI, SUMAR.SI.CONJUNTO, CONTAR.SI, CONTAR.SI.CONJUNTO, PROMEDIO.SI y PROMEDIO.SI.CONJUNTO.

3.4.1

Utilizar fórmulas para buscar datos en un libro.

Utilizar las funciones BUSCARV y BUSCARH.

3.5.1

Agregar funciones lógicas condicionales a fórmulas.

Utilizar las funciones SI, Y, O, NO, SI.ERROR.

3.6.1

Utilizar fórmulas para formatear texto.

Utilizar las funciones NOMPROPIO, MAYUSC, MINUSC, SUSTITUIR.

3.7.1

Utilizar la función SUSTITUIR.

Utilizar las funciones NOMPROPIO, MAYUSC, MINUSC, SUSTITUIR.

3.7.1

Utilizar una fórmula para convertir texto en columnas.

Convertir texto en columnas.

DE LA MATRIZ

N ÚMERO

DE HABILIDAD

Utilizar fórmulas para modificar texto.

3.7.2, 2.3.5

Fabrikam utiliza varias herramientas analíticas de Excel para revisar los datos de ventas durante sus actividades de planeación estratégica. Los dueños han creado un programa de bonos estándares como parte de sus esfuerzos de retener a los empleados, y como un premio al desempeño para reconocer a los agentes de ventas que han sido clave en lograr los objetivos estratégicos de la empresa. El programa de bonos estándares está basado en los años de servicio en Fabrikam. El bono de desempeño se otorga cuando un agente alcanza su objetivo del año. Los contadores de Fabrikam crearán fórmulas para analizar los datos de ventas. TÉRMINOS CLAVE Argumentos Matriz Fórmula condicional Funciones de búsqueda Tabla

149

150 | Lección 8

O R I E N TA C I Ó N

SOBRE EL SOFTWARE

La ficha Fórmulas En lecciones anteriores, usted aplicó formato a celdas con base en los formatos condicionales predefinidos como mayor que y menor que. Los formatos condicionales predefinidos le permiten indicarle a Excel que las celdas deberán formatearse de manera diferente si los valores almacenados en ellas cumplen con una cierta condición. Los formatos condicionales resaltan aspectos relevantes de los datos por lo que ayudan a los lectores a entenderlos con mayor facilidad. En esta lección utilizará comandos de la ficha Fórmulas, que se muestra en la Figura 8-1, para crear fórmulas que resuman condicionalmente los datos, para buscar datos, para aplicar lógica condicional, y para formatear y modificar el texto. Utilice esta ilustración como referencia a lo largo de esta lección a medida que se familiariza con otros comandos de la ficha Fórmulas y los emplea para crear fórmulas.

Figura 8-1

Se usa para crear fórmulas BUSCARV y BUSCARH

Ficha Fórmulas

Se usa para modificar texto

Se usa para aplicar lógica condicional (SI, Y, O...)

Crear y usar rangos con nombres en fórmulas lógicas y de búsqueda

MOAC Excel 2007/ETC Lección 8 fig 1

I

Utilizar fórmulas para resumir condicionalmente los datos

Utilizar la función SUMAR.SI La función SUMAR.SI calcula el total de sólo aquellas celdas que cumplen con un determinado criterio o condición. La sintaxis de la función SUMAR.SI es SUMAR.SI(Rango, Criterio, Rango_suma). En una fórmula, los valores que una función emplea para realizar operaciones o cálculos se denominan argumentos. Así, los argumentos para la función SUMAR.SI son rango, criterio y rango de suma. Al emplearse juntos crean una fórmula condicional en la que se suman las celdas que cumplen con el criterio previamente definido. Las celdas dentro del rango de criterio que no cumplen con dicho criterio, no se incluyen en el total.

Abra el documento L8-1.1 Utilizar la función SUMAR.SI que está disponible en el CD-ROM complementario.

Usar fórmulas más avanzadas | 151

RESOLUCIÓN DE PROBLEMAS

Si la función SUMAR.SI no está visible, teclee SUMAR.SI en el cuadro Buscar función y haga clic en Ir. Haga clic en SUMAR.SI cuando aparezca en el cuadro Seleccionar una función.

RESOLUCIÓN DE PROBLEMAS

No es necesario escribir el signo de pesos o la coma. Si los escribe, Excel los elimina de la fórmula y devuelve un valor preciso.

TOME NOTA

*

El resultado de la fórmula SUMAR.SI en la celda B20 no incluye el valor de propiedad en la celda B15 porque la fórmula especificó valores mayores a $200,000. Para incluir este valor, el criterio debió haber sido >= (mayor o igual que).

*

Cuando hace clic en Usadas recientemente, la última función que se utilizó aparece al principio de la lista. Cuando hace clic en Insertar función, se abre el cuadro de diálogo Insertar función con la última función empleada resaltada.

TOME NOTA

¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo utilizar una fórmula SUMAR.SI para resumir condicionalmente los datos? 3.4.1

La Tabla 8-1 explica el significado de cada argumento en la sintaxis SUMAR.SI. Si se omite Rango_suma de la fórmula, como lo hizo en el primero cálculo del ejercicio anterior, se evalúan las celdas en el rango y se suman si cumplen con el criterio. No es necesario que Rango_suma tenga el mismo tamaño y forma que el rango. En realidad, las celdas que se suman se determinan utilizando la celda superior izquierda de Rango_suma como la celda inicial, e incluyendo después las celdas que corresponden con el tamaño y la forma del rango.

Tabla 8-1 Argumentos en la sintaxis SUMAR.SI

A RGUMENTO

EXPLICACIÓN

Rango

El rango de celdas que desea que evalúe la función. Los valores en blanco y los de texto no se toman en cuenta.

Criterio

La condición o criterio en forma de número, expresión o texto que determina las celdas que se van a sumar.

Rango_suma

Las celdas reales que se deben sumar si las celdas correspondientes en el rango cumplen los criterios.

No es necesario que Rango_suma tenga el mismo tamaño y forma que el rango. En realidad, las celdas que se suman se determinan utilizando la celda superior izquierda de Rango_suma como la celda inicial, e incluyendo después las celdas que corresponden con el tamaño y la forma del rango.

Utilizar la función SUMAR.SI.CONJUNTO SUMAR.SI.CONJUNTO suma aquellas celdas en un rango que cumplen con múltiples criterios. Es importante observar que el orden de los argumentos es diferente que para SUMAR.SI. En una fórmula SUMAR.SI, el argumento rango_suma es el tercer argumento; en SUMAR.SI.CONJUNTO es el primer argumento.

152 | Lección 8

Abra el documento L8-1.2 Utilizar la función SUMAR.SI.CONJUNTO que está disponible en el CD-ROM complementario.

¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo crear una fórmula que sume los valores que cumplan múltiples criterios? 3.4.1

Una fórmula SUMAR.SI.CONJUNTO le permite especificar múltiples criterios para resumir condicionalmente los datos. La primera fórmula SUMAR.SI.CONJUNTO que creó sumó el precio de venta de las propiedades que se vendieron por más de $200,000 y que estuvieron en el mercado por 60 días o menos. La segunda fórmula sumó las propiedades que se vendieron al 98% (>3%) de su precio de lista dentro de un plazo de 60 días. Estas fórmulas analizaron los datos con base en dos criterios. Usted puede continuar agregando hasta 127 criterios para evaluar los datos. Dado que el orden de los argumentos es diferente en SUMAR.SI y SUMAR.SI.CONJUNTO y que ambas funciones son muy similares, si desea copiarlas y editarlas, asegúrese de colocar los argumentos en el orden correcto.

Utilizar la función CONTAR.SI La función CONTAR.SI cuenta el número de celdas en un determinado rango que cumplen con una condición específica. La sintaxis para la función CONTAR.SI es CONTAR.SI(Rango, Criterio). El rango es el rango de celdas que se van a contar. La condición puede ser un número, una expresión o un texto.

Abra el documento L8-1.3 Utilizar la función CONTAR.SI que está disponible en el CD-ROM complementario. ¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo crear una fórmula que cuente el número de celdas en un rango que cumplen una determinada condición? 3.4.1

El rango es el rango de celdas que deben contarse. Los criterios son las condiciones que deben cumplirse para que se puedan contar las celdas. En el ejercicio, el rango fue el precio de venta de las casas vendidas durante el período. Los criterios fueron las casas que se vendieron por $200,000 o más.

Utilizar la función CONTAR.SI.CONJUNTO La fórmula CONTAR.SI.CONJUNTO cuenta el número de celdas dentro de un rango que cumplen un conjunto de criterios. La sintaxis es CONTAR.SI.CONJUNTO(rango1, criterio1, rango2, criterio2 . . .). Puede crear hasta 127 rangos y criterios.

Abra el documento L8-1.4 Utilizar la función CONTAR.SI.CONJUNTO que está disponible en el CD-ROM complementario.

¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo crear una fórmula que cuente el número de celdas dentro un rango que cumplen con múltiples criterios? 3.4.1

Una celda del rango que indicó en el cuadro Argumentos de función se contará sólo si todos los criterios correspondientes especificados son verdaderos para esa celda. Si un criterio hace referencia a una celda vacía, CONTAR.SI.CONJUNTO la trata como un valor 0. Como se ilustra en la Figura 8-2, las sugerencias para cada cuadro en el cuadro de diálogo Argumentos de función se reemplazan con el valor cuando navega al siguiente cuadro de argumento. También se muestra el resultado de la fórmula, permitiéndole revisar y hacer correcciones si ocurre un mensaje de error o se devuelve un resultado inesperado.

Usar fórmulas más avanzadas | 153 Figura 8-2 Argumentos y resultados para la fórmula CONTAR.SI.CONJUNTO

TOME NOTA

*

Cuando crea fórmulas, puede utilizar los caracteres comodín signo de interrogación (?) y asterisco (*) en los criterios. El signo de interrogación corresponde a un solo caracter (cualquiera) y el asterisco corresponde a cualquier secuencia de caracteres. Si desea un signo de interrogación o asterisco reales, teclee una tilde (~) delante del caracter.

Utilizar la función PROMEDIO.SI PROMEDIO.SI devuelve la media aritmética de todas las celdas en un rango que cumplen con un determinado criterio. La sintaxis es PROMEDIO.SI(rango, criterio, rango_promedio).

Abra el documento L8-1.5 Utilizar la función PROMEDIO.SI que está disponible en el CD-ROM complementario.

¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo crear una fórmula que devuelva el promedio de todas las celdas que cumplen una determinada condición? 3.4.1

En la sintaxis PROMEDIO.SI, el rango es el conjunto de celdas cuyo promedio se desea obtener. Por ejemplo, en este ejercicio usted deseaba saber el número de días promedio que una propiedad valorada en $200,000 estuvo en el mercado antes de venderse. El rango es B5:B16, el valor de lista de las casas que se vendieron. El criterio es la condición sobre la que desea evaluar las celdas, es decir >=200000. Rango_promedio es el conjunto real de celdas de las que se va a obtener el promedio: el número de días que cada casa estuvo en el mercado antes de venderse.

Utilizar la función PROMEDIO.SI.CONJUNTO Una fórmula PROMEDIO.SI.CONJUNTO devuelve el promedio (media aritmética) de todas las celdas que cumplen múltiples criterios. La sintaxis es PROMEDIO.SI.CONJUNTO(rango_promedio,rango_criterio1,criterio1,-rango_criterio2,criterio2 . . .).

Abra el documento L8-1.6 Utilizar la función PROMEDIO.SI.CONJUNTO que está disponible en el CD-ROM complementario.

¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo crear una fórmula que calcule el promedio de los datos que cumplen múltiples criterios? 3.4.1

Usted introdujo sólo dos criterios para las fórmulas SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO y PROMEDIO.SI.CONJUNTO que creó en los ejercicios anteriores. Sin embargo, en hojas de cálculo muy grandes, con frecuencia necesitará emplear múltiples criterios para que la fórmula devuelva un valor que sea significativo para su análisis. Usted puede introducir hasta 127 condiciones que los datos deben cumplir para que una celda sea incluida en el resumen condicional que resulta de una fórmula SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO o PROMEDIO.SI.CONJUNTO.

154 | Lección 8

TOME NOTA

Las siguientes declaraciones resumen la forma en que se tratan los valores cuando usted introduce una fórmula PROMEDIO.SI o PROMEDIO.SI.CONJUNTO (siguiente ejercicio):

*

En una fórmula condicional, puede hacer referencia a otra hoja de cálculo en el mismo libro, pero no puede emplear referencias a otro libro.

I

• Si se omite Rango_promedio de los argumentos de función, se emplea el rango. • Si una celda de Rango_promedio es una celda vacía, PROMEDIO.SI la ignora. • Si un rango está en blanco o contiene un valor de texto, PROMEDIO.SI devuelve el valor de error #DIV0!. • Si una celda en un criterio está vacía, PROMEDIO.SI la trata como un valor 0. • Si no hay celdas en el rango que cumplan con los criterios, PROMEDIO.SI devuelve el valor de error #DIV/0!.

Utilizar fórmulas para buscar datos en un libro Utilizar la función BUSCARV La V en BUSCARV significa vertical. Esta fórmula se utiliza cuando los valores de comparación se encuentran en una columna situada a la izquierda de los datos que desea buscar. La función BUSCARV busca un valor en la primera columna de una matriz de tabla en la hoja de cálculo y devuelve, en la misma fila, un valor de otra columna de dicha matriz de tabla.

Abra el documento L8-2.1 Utilizar la función BUSCARV que está disponible en el CD-ROM complementario.

TOME NOTA

*

¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo crear una fórmula BUSCARV que recupere un valor de una tabla? 3.5.1

TOME NOTA

Los argumentos que se utilizan en las funciones BUSCARV y BUSCARH no distinguen entre mayúsculas y minúsculas así que puede escribirlos empleando mayúsculas o minúsculas o usando una combinación de ellas. Además, los nombres de función BUSCARV y BUSCARH tampoco distinguen entre mayúsculas y minúsculas.

La sintaxis de la función BUSCARV es BUSCARV(valor_buscado,matriz_buscar_en,indicador_columnas). Los componentes de los argumentos que se emplean en las fórmulas de búsqueda se muestran en la Figura 8-3, y se describen en la Tabla 8-2 que aplica para las fórmulas BUSCARV y BUSCARH.

*

Figura 8-3 Argumentos de función para la fórmula BUSCARV

VERDADERO en el cuadro ordenado devolverá el valor más aproximado. FALSO sólo devuelve un valor exacto. Si deja vacío el cuadro Ordenado, Excel introducirá VERDADERO cuando haga clic en Aceptar.

Usar fórmulas más avanzadas | 155 Tabla 8-2 Sintaxis de función para BUSCARV

A RGUMENTO

DESCRIPCIÓN

Valor_buscado

El valor que se va a buscar en la columna o fila. Puede ser un valor constante, el texto de un contacto entre comillas, o la dirección o nombre de una celda que contiene una constante numérica o de texto.

Matriz_buscar_en

Dos o más columnas de datos. Utiliza una referencia a un rango o a un nombre de rango. Los valores en la primera columna de matriz_buscar_en son los valores que busca valor_buscado.

Indicador_filas

La posición numérica de la fila en la que deberá buscar BUSCARH.

Indicador_columnas

La posición numérica de la columna en la que deberá buscar BUSCARV. El número de columna de matriz_buscar_en desde la cual debe devolverse el valor coincidente. Un indicador_columnas igual a 1 devuelve el valor en la primera columna de matriz_buscar_en. Un indicador_columnas igual a 2 devuelve el valor en la segunda columna de matriz_buscar_en, etcétera.

Ordenado

Un valor lógico que especifica si BUSCARV va a buscar una coincidencia exacta o aproximada. Si la función debe devolver el valor más aproximado, aún cuando no haya una coincidencia, este valor debe establecerse en VERDADERO; si se requiere una coincidencia exacta, este valor debe establecerse en FALSO. Si no se incluye este argumento, la función asume que el valor es VERDADERO.

Repase los siguientes puntos clave relacionados con las funciones y argumentos de las funciones BUSCARV y BUSCARH. • Se emplea una matriz para crear fórmulas únicas que produzcan múltiples resultados o que operen sobre un grupo de argumentos. Los datos en la matriz de tabla deben estar ordenados en filas y columnas. Puede ser una constante o una fórmula. • Si Valor_buscado es menor que el número más pequeño en la primera columna de Matriz_buscar_en, BUSCARV devuelve el valor de error #N/A, como vio en la celda E11 en el ejercicio anterior. • Los valores de Matriz_buscar_en pueden ser texto, números o valores lógicos. El texto en mayúsculas y minúsculas es equivalente. • Los valores en la primera columna de Matriz_buscar_en deben colocarse en orden ascendente, de otra manera BUSCARV podría no devolver el valor correcto. La tabla de búsqueda que utilizó en este ejercicio mostraba los años de servicio en orden ascendente. • Si Ordenado es VERDADERO o si se omite, se devolverá una coincidencia exacta o aproximada. Si no se encuentra una coincidencia exacta, devolverá el siguiente valor más alto inferior a Valor_buscado. • Si Ordenado es FALSO, BUSCARV sólo buscará una coincidencia exacta. Si no se encuentra una coincidencia exacta, se devolverá el valor de error #N/A. En este ejercicio, se paga el mismo bono para un rango de años, así que introdujo VERDADERO en el cuadro Ordenado para que devolviera un valor para todos los agentes que han estado en la empresa más de un año.

156 | Lección 8

Utilizar la función BUSCARH BUSCARH busca un valor en la fila superior de una tabla o una matriz y luego devuelve un valor en la misma columna de una fila que usted especifique en la tabla o matriz. Use BUSCARH cuando los valores de comparación se encuentren en una fila en la parte superior de una tabla de datos y desee buscar dentro de un número especificado de filas.

Abra el documento L8-2.2 Utilizar la función BUSCARH que está disponible en el CD-ROM complementario.

¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo crear una fórmula BUSCARH que recupere un valor de una fila específica de una tabla? 3.5.1

I

Podría ser difícil recordar la sintaxis de la función BUSCARH o BUSCARV. Utilice el cuadro de diálogo Argumentos de función para que le ayude a recordar el orden de los argumentos. Cuando haga clic en cada campo, revise las sugerencias que aparecen a la derecha de cada cuadro, así como la explicación debajo de los cuadros de argumentos que le indican el propósito de cada argumento en la fórmula.

Agregar funciones lógicas condicionales a fórmulas Utilizar la función SI El resultado de una fórmula condicional se determina por el estado de una condición específica, o la respuesta a una pregunta lógica. Una fórmula SI devuelve un valor si una condición especificada es verdadera, y otro valor si la condición es falsa. La función Si requiere la siguiente sintaxis: SI(prueba_lógica, valor_si_verdadero, valor_si_falso).

Abra el documento L8-3.1 Utilizar la función SI que está disponible en el CD-ROM complementario. ¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo crear una fórmula que devuelva un valor si existe una condición, y un valor diferente si la condición no existe? 3.6.1

Una función SI establece una declaración condicional para probar datos. Si la condición existe, se devolverá un valor. Si la condición no existe, se devolverá otro.

Utilizar la función Y La función Y devuelve VERDADERO si todos sus argumentos son VERDADEROS, y devuelve FALSO si uno o más argumentos son FALSOS. La sintaxis es Y(valor_lógico1, valor_lógico2,. . .). Usted utilizará la función Y para determinar si el total de ventas anuales de Fabrikam cumple con el objetivo estratégico y si éste excedió las ventas del año anterior en un 5 por ciento.

Abra el documento L8-3.2 Utilizar la función Y que está disponible en el CD-ROM complementario.

Usar fórmulas más avanzadas | 157 ¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo crear una fórmula Y para evaluar lógicamente los datos para determinar si se cumplieron múltiples condiciones? 3.6.1

La función SI devuelve un resultado VERDADERO sólo cuando se han cumplido ambas condiciones en la fórmula. Examinemos los resultados que logró en el ejercicio anterior: las ventas en el año 2 excedieron las ventas del año anterior, por lo tanto se ha cumplido la primera condición; las ventas del año 2 también han excedido las ventas del año 1 en 5 por ciento; dado que se han cumplido ambas condiciones, la fórmula devuelve VERDADERO. Considere los argumentos para las pruebas lógicas para el año 3 (la fórmula en la celda D5): las ventas excedieron el objetivo de ventas; por lo tanto, el primer argumento devuelve el valor VERDADERO; sin embargo, las ventas no excedieron las ventas del año anterior en 5 por ciento; como sólo se ha cumplido una condición, la fórmula devuelve FALSO.

Utilizar la función O La sintaxis de la fórmula O es similar a la de la función Y. Sin embargo, O devuelve VERDADERO si cualquier argumento es VERDADERO, y devuelve FALSO sólo cuando todos los argumentos son FALSOS. Los argumentos deben evaluarse como valores lógicos, como VERDADERO o FALSO, o como matrices o referencias que contengan valores lógicos. En el siguiente ejercicio creará una fórmula que evalúe si las ventas aumentaron todos los años durante el período de los datos. La fórmula O devolverá VERDADERO si aumentaron durante cualquiera de los periodos

Abra el documento L8-3.3 Utilizar la función O que está disponible en el CD-ROM complementario.

TOME NOTA

*

¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo crear una fórmula O para evaluar lógicamente los datos para determinar si se cumplieron cualquiera de las condiciones? 3.6.1

A medida que añade argumentos, los campos de valor en el cuadro de diálogo Argumentos de función se amplían para permitirle introducir más argumentos.

En la primera fórmula O que introdujo en este ejercicio, ambas pruebas lógicas devolvieron un valor VERDADERO. Las ventas del Sr. Carey en el año 3 fueron al menos 3 por ciento mayores que en el año 2. Sus ventas para el año 4 también fueron al menos 3 por ciento mayores que sus ventas del año 3. Si una de las pruebas hubiera resultado en un valor VERDADERO y la otra en un valor FALSO, aun así la fórmula hubiera devuelto un valor VERDADERO en la celda A18.

Utilizar la función NO La función NO invierte el valor de sus argumentos. Utilice NO cuando desee asegurarse de que un valor no sea igual a otro valor específico. Si el valor lógico es falso, NO devuelve VERDADERO. En el siguiente ejercicio usted contestará a la pregunta: ¿Las ventas durante el año 5 de Cardoso sobrepasan en 3 por ciento a sus ventas durante el año 4?

Abra el documento L8-3.4 Utilizar la función NO que está disponible en el CD-ROM complementario.

158 | Lección 8

¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo crear una fórmula NO para asegurarse de que cierto valor no está presente? 3.6.1

En este ejercicio, usted deseaba asegurarse que en el año 5 las ventas de Cardoso hubieran aumentado al menos 3 por ciento en comparación con el año anterior. La fórmula condicional NO resulta en un valor lógico (Verdadero o Falso) y puede emplearse para esta prueba lógica, devolviendo el valor opuesto de lo que hubiera devuelto una fórmula SI.

Utilizar la función SI.ERROR Cuando una fórmula no contiene los suficientes argumentos, se devuelve un mensaje de error. Utilice la función SI.ERROR para interceptar y controlar errores en las fórmulas. La función devuelve un valor que usted determina, si una fórmula presenta un error. Si la fórmula está bien, devuelve el resultado. La sintaxis es SI.ERROR(valor,valor_si_error).

Abra el documento L8-3.5 Utilizar la función SI.ERROR que está disponible en el CD-ROM complementario. ¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo reemplazar un mensaje de error con texto específico cuando la fórmula produce un error? 3.6.1

Valor es el argumento en que se busca el error. Si selecciona la celda E11 y hace clic en el botón Insertar función a un lado de la barra de fórmulas, verá que la fórmula BUSCARV original aparece en el cuadro Valor (primer argumento) en la fórmula SI.ERROR. Como se ilustra en la Figura 8-4, ese argumento devolvió un error #N/A. El cuadro Valor_si_error contiene el texto para reemplazar el mensaje de error.

Figura 8-4 Argumentos de la función SI.ERROR

Fórmula original

Mensaje de error devuelto por la fórmula original

Texto para reemplazar el mensaje de error

MOAC Excel 2007/ETC Lección 8 fig 9

La función SI.ERROR evalúa los siguientes tipos de error: #N/A, #VALOR!, #REF!, #DIV/0!, #NUM!, #¿NOMBRE? o #NULO!. En el ejercicio, usted reemplazó el mensaje de error con un texto que explicaba por qué uno de los agentes de ventas no recibió un bono cuando se aplicó la fórmula BUSCARV. I

Utilizar fórmulas para formatear texto Utilizar la función NOMPROPIO La función NOMPROPIO cambia a mayúscula la primera letra de una cadena de texto y cualquiera de las otras letras de texto que se encuentren después de cualquier caracter que no sea una letra, y convierte todas las demás letras a minúsculas. En la sintaxis NOMPROPIO (texto), el texto es el texto entre comillas, una fórmula que devuelve texto o una referencia a una celda que contiene el texto al que desea agregar mayúsculas. Puede usar NOMPROPIO para cambiar texto en mayúsculas o minúsculas, a texto tipo título.

Usar fórmulas más avanzadas | 159 Abra el documento L8-4.1 Utilizar la función NOMPROPIO que está disponible en el CD-ROM complementario.

¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo modificar texto empleando una fórmula NOMPROPIO? 3.7.1

La función NOMPROPIO cambia a mayúscula la primera letra en cada palabra en la cadena de texto, y convierte todas las demás letras a minúsculas. Ésta es la razón por la cual editó el texto después de haberlo convertido en el ejercicio anterior. Dado que Excel reconoce el apóstrofe como una interrupción, cambió a mayúscula la siguiente letra. En la sintaxis NOMPROPIO(texto), el texto puede estar entre comillas o puede seleccionar referencias de celdas como las seleccionó en el ejercicio anterior. Cuando creó la fórmula para convertir el texto a tipo título, tenía dos líneas de texto. Si hubiera eliminado el texto original en la celda A16, también se habría eliminado el texto convertido de la celda A17. Cuando utilizó la función Pegar valores, el contenido de la celda A17 se pegó en la celda A14 en lugar de la fórmula, la cual dependería del texto que quedó en la celda A16. Después de que pegó los resultados en lugar de la fórmula, fue capaz de quitar las líneas de texto duplicadas.

Utilizar la función MAYUSC La función MAYUSC le permite convertir texto a mayúsculas. La sintaxis es MAYUSC(texto), siendo texto el texto que desea convertir a mayúsculas. El texto puede ser una referencia o una cadena de texto.

Abra el documento L8-4.2 Utilizar la función MAYUSC que está disponible en el CD-ROM complementario. ¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo utilizar MAYUSC para convertir el texto a mayúsculas empleando una fórmula? 3.7.1

Convertir el texto a mayúsculas es un proceso que requiere dos pasos. Usted no puede introducir la fórmula en la ubicación actual del texto, así que después de que el texto ha sido convertido, debe cortar y pegar el texto en la ubicación deseada. El comando Pegar valores pega el contenido sin la fórmula para que usted pueda quitar los datos duplicados.

Utilizar la función MINUSC La función MINUSC convierte todas las letras mayúsculas de una cadena de texto en minúsculas. MINUSC no cambia los caracteres de texto que no sean letras.

Abra el documento L8-4.3 Utilizar la función MINUSC que está disponible en el CD-ROM complementario.

¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo utilizar MINUSC para convertir texto en minúsculas empleando una fórmula? 3.7.1

Es importante que revise el texto después de que haber cambiado las mayúsculas a minúsculas. Por ejemplo, el nombre del agente en la celda B20, así como la primera palabra en la oración deben tener la primera letra en mayúsculas. Si reemplaza el texto en la celda B19 con el texto en minúsculas de la celda B20, deberá editar el texto.

160 | Lección 8 I

Utilizar fórmulas para modificar texto Utilizar la función SUSTITUIR La función SUSTITUIR de Excel es especialmente útil cuando necesite editar datos y desee sustituir el texto existente con texto nuevo. Emplee la función SUSTITUIR cuando desee reemplazar texto específico en una cadena de texto. Use REEMPLAZAR cuando desee reemplazar cualquier texto que aparezca en una ubicación específica en una cadena de texto, como cuando ocurre un cambio de nombre.

Abra el documento L8-5.1 Utilizar la función SUSTITUIR que está disponible en el CD-ROM complementario.

¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo utilizar la función SUSTITUIR para reemplazar texto existente con texto nuevo empleando una fórmula? 3.7.1

Figura 8-5 Argumentos de la función SUSTITUIR

Cuando desee emplear el texto existente con pequeños cambios, puede utilizar la función SUSTITUIR. En el cuadro de diálogo Argumentos de función, el Texto puede ser el texto real que desee sustituir o puede ser una referencia de celda. Usted deseaba emplear el texto en la celda B18 con un cambio. La Figura 8-5 ilustra los argumentos de función que resultan en el cambio de 3% a 5% en el texto nuevo. El número 3 aparece tres veces en el texto original. El último argumento de función indica qué ocurrencia debe reemplazarse.

Usar el texto en la celda B19 en una nueva ubicación

Reemplazar la primera ocurrencia de 3

Reemplazar el número 3 en una nueva ubicación

Reemplazar 3 (texto original) con 5

MOAC Excel 2007/ETC Lección 8 fig 10

Utilizar una fórmula para convertir texto en columnas ¿LISTO PARA LA CERTIFICACIÓN? ¿Cómo utilizar una fórmula para convertir texto en columnas? 3.7.2, 2.3.5

Puede utilizar el Asistente para convertir texto en columnas para separar el contenido de celdas simples en columnas distintas, por ejemplo, separar un nombre completo en nombre y apellido. Dependiendo de la manera en que estén organizados sus datos, usted puede dividir el contenido de las celdas en función de un delimitador, como un espacio o una coma, o en función de la ubicación específica de un salto de columna. En el siguiente ejercicio, usted convertirá los datos de la columna A en dos columnas.

Abra el documento L8-5.2 Utilizar una fórmula para convertir texto en columnas que está disponible en el CD-ROM complementario.

Usar fórmulas más avanzadas | 161

MATRIZ DE RESUMEN DE HABILIDADES EN

ESTA LECCIÓN USTED APRENDIÓ

H ABILIDAD

DE LA MATRIZ

N ÚMERO

DE HABILIDAD

A crear fórmulas para resumir los datos que cumplen con los criterios especificados.

Utilizar SUMAR.SI, SUMAR.SI.CONJUNTO CONTAR.SI, CONTAR.SI.CONJUNTO PROMEDIO.SI y PROMEDIO.SI.CONJUNTO.

3.4.1

A crear fórmulas para buscar datos en un libro.

Utilizar BUSCARV y BUSCARH.

3.5.1

A crear fórmulas que prueben si las condiciones están presentes.

Utilizar SI, Y, O, NO, SI.ERROR.

3.6.1

A crear fórmulas para cambiar de mayúsculas a minúsculas o viceversa.

Utilizar NOMPROPIO, MAYUSC, MINUSC, SUSTITUIR.

3.7.1

A sustituir texto existente con texto nuevo.

Utilizar NOMPROPIO, MAYUSC, MINUSC, SUSTITUIR.

3.7.1

A separar el contenido de celdas en diferentes columnas.

Convertir texto en columnas.

A utilizar fórmulas para modificar texto.

3.7.2, 2.3.5