Excel - Leccion 7

Abra el documento LL11--33..33IInnttrroodduucciirr ddaattooss eenn uunnaa hhoojjaa ddee

TT ÉÉ RR MM II NN OO SS CC LL AA VV EEReferencia de celda absolutaConstanteReferencia externaFórmulaFunciónOperador matemáticoReferencia mixtaNombreOperandoReferenciaReferencia de celda relativaÁmbito

125

MM AA TT RR II ZZ DD EE HH AA BB II LL II DD AA DD EE SS DD EE LL AA LL EE CC CC II OO NN

HABILIDADES HABILIDAD DE LA LECCIÓN NÚMERO DE HABILIDAD

Crear fórmulas básicas. Crear fórmulas que utilicen referencias de 3.1.1celdas absolutas y relativas.

Utilizar referencias de celda en fórmulas.

Utilizar referencias de celda relativas en Crear fórmulas que utilicen referencias de 3.1.1una formula. celdas absolutas y relativas.

Utilizar referencias de celda absolutas Crear fórmulas que utilicen referencias de 3.1.1en un fórmula. celdas absolutas y relativas.

Hacer referencia a datos de otra Crear fórmulas que hagan referencia a los datos 3.1.2hoja de cálculo. de otras hojas de cálculo o libro.

Hacer referencia a datos de otro Crear fórmulas que hagan referencia a los datos 3.1.2libro. de otras hojas de cálculo o libro.

Utilizar rangos de celdas en fórmulas. Administrar los rangos con nombre. 3.1.3

Crear una fórmula que opere en un Utilizar rangos con nombre en fórmulas. 3.1.4rango con nombre.

Resumir datos usando funciones. Utilizar las funciones SUMAR, CONTAR, 3.2.1CONTARA, PROMEDIO, MIN y MAX.

Utilizar fórmulas para crear subtotales. Crear y modificar rangos de lista. 3.3.1

Controlar el aspecto de las fórmulas. Mostrar e imprimir fórmulas. 3.8

Usar fórmulas básicasy funciones

Así como la mayoría de las personas están de acuerdo en que es de vital importancia para unnegocio contar con un presupuesto realista, también lo es a nivel personal. Todos deberían contarcon un presupuesto personal, esto es, un plan para administrar los ingresos y los gastos.

Katie Jordan ha estado administrando o, mejor dicho, gastando su dinero sin un presupuestoformal. De hecho, el único presupuesto que ha preparado fue el que garabateó en la parte deatrás de su currículo inmediatamente después de que se le ofreció lo que ella consideró era su

7

112266 || LLeecccciióónn 77

trabajo ideal. Desde esa ocasión, Katie ha cambiado de trabajo varias veces. Ahora, desea com-prar un condominio y se da cuenta de que necesita crear un presupuesto personal detalladoque le permita logar su meta de ser propietaria de una casa.

Katie utiliza Excel en su trabajo como analista de mercadotecnia en la Juguetería Tailspin.Planea dar seguimiento a sus gastos y desarrollar un presupuesto realista empleando una hojade cálculo. Realizó una investigación en línea y desarrolló un presupuesto preliminar. Es suprimero paso hacia la independencia financiera

La ficha Fórmulas

Las fórmulas hacen de Excel una herramienta muy poderosa. En esta lección, usted aprenderá aescribir fórmulas simples y a utilizar muchas de las funciones de Excel que contienen fórmulasintegradas que le permiten realizar muchos tipos de cálculos haciendo clic en un comando enla ficha Fórmulas que se ilustra en la Figura 7-1.

ORIENTACIÓN SOBRE EL SOFTWARE

Figura 7-1

Ficha Fórmulas

MOAC Excel 2007/ETC Lección 7 fig 1

Usar Autosuma para sumarlas celdas adyacentes

Ver y editarrangoscon nombres

Se activa sólo cuandose crea una fórmula

Seleccionar unafunción

integrada

Funcionesusadas

recientemente

Crear una celdao rango

con nombre

Utilice esta ilustración como una referencia a lo largo de esta lección conforme se familiari-za con los grupos de comandos en la ficha Fórmulas y los emplea para crear fórmulas.

� Crear fórmulas básicas

Crear una fórmula que realice sumas

Una fórmula consiste en dos elementos: los operandos y los operadores matemáticos. Losooppeerraannddooss identifican los valores que se usarán en el cálculo. Un operando puede ser unvalor constante, una referencia de celda, un rango de celdas u otra fórmula. Una ccoonnssttaanntteees un valor numérico o de texto que se introduce directamente en una fórmula. Los ooppeerraa--ddoorreess mmaatteemmááttiiccooss especifican los cálculos que se deben realizar. En los siguientes ejercicios,usted creará fórmulas básicas utilizando diferentes métodos para introducir las fórmulas.

Abra el documento LL77--11..11 CCrreeaarr uunnaa ffóórrmmuullaa qquuee rreeaalliiccee ssuummaass que está disponible en el CD-ROM complementario.

UUssaarr ffóórrmmuullaass bbáássiiccaass yy ffuunncciioonneess || 112277

TOME NOTA*Las fórmulas deben escribirse sin espacios, pero si los escribe, Excel los eliminará cuandopresione la tecla EEnnttrraarr.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo crear una fórmulaque realice sumas? 3.1.1

TOME NOTA* Cuando hace clic en la barra de fórmulas, entra automáticamente al modo edición.

Para comenzar a crear una fórmula, seleccione la celda en la que desea que aparezca. Para per-mitirle a Excel distinguir las fórmulas de los datos, todas las fórmulas comienzan con el signoigual (=).

TOME NOTA*Usted puede comenzar una fórmula con ++ o −− como el operador matemático de inicio,pero Excel lo cambiará a == cuando presione la tecla EEnnttrraarr.

Cuando usted crea una fórmula, ésta aparece en la barra de fórmulas y en la celda en sí.Como se ilustra en la Figura 7-2, cuando termina de escribir la fórmula y presiona la teclaEEnnttrraarr, el valor se muestra en la celda y la fórmula se muestra en la barra de fórmulas. Comopracticó en este ejercicio, usted puede modificar una fórmula en la celda o en la barra de fór-mulas, de la misma manera en que modifica una entrada de datos.

Para crear una fórmula que realice restas se emplean los mismos métodos que utilizó paracrear una fórmula que realice sumas. Cuando cree una fórmula de resta, escriba == seguidodel número positivo y luego escriba un signo de menos para indicar la resta.

Crear una fórmula que realice restas

RESOLUCIÓN DEPROBLEMAS

Si su fórmula devolvió un valor negativo (por ejemplo, –64), significa que invirtió elorden en el que debió haber introducidos los números.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo crear una fórmulaque realice restas? 3.1.1

Cuando crea una fórmula de resta, el signo menos debe preceder al número que va a restar.Cuando introdujo una fórmula para restar 125 de 189, usted pudo hacer introducido =189−125 o = −125+189. Cualquiera de las dos fórmulas dará como resultado un 64 positivo. Si seintroduce primero el número positivo no es necesario introducir el signo más.

Abra el documento LL77--11..22 CCrreeaarr uunnaa ffóórrmmuullaa qquuee rreeaalliiccee rreessttaass que está disponible en el CD-ROMcomplementario.

Crear una fórmula que realice multiplicaciones

La fórmula para multiplicar 33 por 6 es =33*6. Si una fórmula contiene dos o más opera-dores, las operaciones no necesariamente se realizan en el orden en que se lee la fórmula.El orden lo determinan las reglas matemáticas, pero usted puede anular las prioridadesestándar de los operadores utilizando paréntesis. Las operaciones dentro de los paréntesisse realizan antes de las que están fuera de ellos.


Abra el documento LL77--11..33 CCrreeaarr uunnaa ffóórrmmuullaa qquuee rreeaalliiccee mmuullttiipplliiccaacciioonneess que está disponible en elCD-ROM complementario.

Cuando añadió un paréntesis a la última fórmula que introdujo en este ejercicio, cambió elorden de los cálculos. Cuando introdujo la fórmula sin el paréntesis, Excel multiplicó 2 por 8y añadió el 5 para obtener un valor de 21. Cuando introdujo (5+2)*8, Excel realizó primerola suma y devolvió un valor de 56. En el siguiente ejercicio, se ilustrará con mayor detalle elorden de los cálculos.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo crear una fórmulaque realice multiplicaciones? 3.1.1

Crear una fórmula que realice divisiones

La barra diagonal es el operador matemático para la división. Cuando un cálculo incluyevarios valores, utilice paréntesis para indicar qué parte del cálculo es la que debe realizar-se primero.

Abra el documento LL77--11..44 CCrreeaarr uunnaa ffóórrmmuullaa qquuee rreeaalliiccee ddiivviissiioonneess que está disponible en el CD-ROM complementario.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo crear una fórmulaque realice divisiones? 3.1.1

Dado que las celdas de esta hoja de cálculo no estaban formateadas antes de que usted intro-dujera los datos, se aplicó el formato General predeterminado a los números que tecleó.Cuando creó la fórmula =795/45, Excel devolvió un valor de 17.66667 y se aplicó el forma-to Número a la celda D7. Los resultados del cálculo de la fórmula se redondearon al valordespués del séptimo dígito (octavo caracter) porque el ancho de columna estándar es 8.43. Enotras palabras, el valor fue redondeado a ese número de posiciones sólo debido al ancho decolumna.

Cuando insertó paréntesis dentro de la fórmula, los resultados fueron diferentes que cuandono los había. Excel no necesariamente realiza las operaciones en el mismo orden en que ustedlas escribe o lee en la fórmula, o sea de izquierda a derecha. Excel utiliza las reglas matemáti-cas para determinar qué operaciones realizar primero cuando una fórmula contiene variosoperadores. El orden es:

• número negativo (−)• porcentaje (%)• exponenciación (ˆ)• multiplicación (*) y división (/)• suma (+) y resta (−)

Por ejemplo, considere la siguiente ecuación.

5 + 6 * 15 / 3 −1 = 34

Siguiendo las prioridades de los operadores matemáticos, la primera operación sería 6 multi-plicado por 15 y el resultado se dividiría entre 3. Luego se sumaría el 5 y finalmente se res-taría 1. La Figura 7-2 ilustra la fórmula que se introdujo en Excel.



Operadoresmatemáticos

Operandos

Re s ult a d o s d ela fór m ula

Figura 7-2

Estructura de una fórmula

Cuando utiliza paréntesis en una fórmula, está indicando qué cálculos deben realizarse prime-ro, anulando así las prioridades estándar de los operadores. Por lo tanto, el resultado de lasiguiente ecuación será significativamente diferente a la de la anterior. La Figura 7-3 ilustra lafórmula de Excel y aquí está la fórmula matemática:

(5 + 6) * 15 / (3 −1) = 82.5


Resultados de la fórmula

Los valores dentro delparéntesis se calculan primero

Figura 7-3

Utilice paréntesis para controlar el orden de las operaciones

� Usar referencias de celda en fórmulas

Abra el documento LL77--22..11 UUttiilliizzaarr rreeffeerreenncciiaass ddee cceellddaa rreellaattiivvaass eenn uunnaa ffóórrmmuullaa que está disponibleen el CD-ROM complementario.

Utilizar referencias de celda relativas en una fórmula

Utilizar referencias de celda en las fórmulas en lugar de cantidades constantes le permitecopiar las fórmulas sin tener que cambiar manualmente las referencias de celda. Cuandoincluye una referencia de celda en una fórmula y la copia, Excel cambia dicha referenciapara que coincida con la columna o fila a la cual se está copiando la fórmula. Por lo tanto,una rreeffeerreenncciiaa ddee cceellddaa rreellaattiivvaa es aquella cuyas referencias cambian de manera “relativa” a laubicación a donde se copia o mueve.

TOME NOTA*Puede emplear mayúsculas o minúsculas cuando escriba una referencia de celda en unafórmula. Por ejemplo, no importa si usted escribe B4 o b4 en la fórmula.

TOME NOTA*Abra el cuadro de diálogo Formato de celdas para cambiar la forma en que Excel mues-tra los resultados “sin diferencia”. Por ejemplo, en la ficha Número usted puede elegirque aparezca un 0.


En este ejercicio, usted copió las fórmulas en lugar de escribirlas varias veces. De esta formaahorró una cantidad de tiempo considerable al copiar una celda con una fórmula completa ypegarla en una celda destino.

Para entender lo que es una referencia de celda relativa, examinemos la fórmula en la celda B7,o sea =suma(B4:B6). La referencia a la celda B4 está basada en su posición relativa con respec-to a la celda B7, la celda que contiene la fórmula. Cuando copió la fórmula a la celda C7, cam-bió la posición de la celda que contenía la fórmula de tal manera que la referencia cambió aC7. Usted debe utilizar referencias de celda relativas cuando desee que la referencia se ajusteautomáticamente cuando copia o rellena la fórmula en las filas o columnas. De manera prede-terminada, las fórmulas nuevas utilizan referencias relativas.

En este ejercicio, usted empleó dos métodos para crear fórmulas utilizando referencias relati-vas:

• Teclear el signo igual para marcar la entrada como una fórmula. Después, escribir lafórmula directamente en la celda, incluyendo las referencias de celda, los valores cons-tantes y los operadores matemáticos.

• Teclear el signo igual y hacer clic en la celda o en el rango de celdas que se deseaincluir en la fórmula, en lugar de escribir las referencias de celda.

Normalmente el segundo método es más rápido y elimina la posibilidad de escribir incorrec-tamente una celda o un rango de celdas. Las referencias de celda y los bordes alrededor de lasceldas correspondientes están codificadas con colores para facilitarle comprobar que seleccionóla celda deseada.

Como vio en la Figura 7-5 (del documento del CD), cuando crea una fórmula, ésta apareceen la barra de fórmulas y en la celda. Cuando termina de escribir la fórmula y presiona la teclaEEnnttrraarr, el valor se muestra en la celda y la fórmula se muestra en la barra de fórmulas.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo crear fórmulas queusan referencias de celdarelativas? 3.1.1

Utilizar referencias de celda absolutas en una hoja de cálculo

En muchas ocasiones, usted no querrá que cambie la referencia de una celda cuando lamueve o la copia. Por ejemplo, en su presupuesto personal usted sabe qué porcentaje desus ingresos asignó a cada uno de sus gastos. Ahora bien, las fórmulas que cree para calcu-lar el monto de estos gastos harán referencia a la celda que contiene el importe de susingresos totales. La referencia a esta celda debe ser una rreeffeerreenncciiaa ddee cceellddaa aabbssoolluuttaa, esto es,no debe cambiar.

Abra el documento LL77--22..22 UUttiilliizzaarr rreeffeerreenncciiaass ddee cceellddaa aabbssoolluuttaass eenn uunnaa hhoojjaa ddee ccáállccuulloo que estádisponible en el CD-ROM complementario.

TOME NOTA*No es necesario teclear el paréntesis de cierre cuando termina la selección para una fór-mula. Excel lo incluye cuando usted presione la tecla Entrar.


Cuando introduce una fórmula que dará un resultado menor a un número entero,asegúrese de que la celda esté formateada con decimales. Si la celda está formateada paranúmeros enteros, ésta mostrará 0 ó 1 en lugar del valor esperado.


Usted puede reconocer una referencia de celda absoluta por la inclusión de uno o más signosde pesos en la fórmula. La referencia de celda absoluta $B$7 en el ejercicio anterior siemprehará referencia a la celda B7 porque tanto la columna B ($B) como la fila 7 ($7) son absolu-tas. Cuando copia una fórmula en las filas y columnas, la referencia absoluta no se ajustará alas celdas de destino. De manera predeterminada, las fórmulas nuevas usan referencias relati-vas y usted debe editarlas si desea que se conviertan en referencias absolutas. También puedecrear una referencia mixta en la que la columna es absoluta y la fila relativa, o viceversa. Porejemplo, si la referencia de celda en la fórmula fuera $B7 o B$7, tendría una rreeffeerreenncciiaa mmiixxttaaen la cual un componente es absoluto y otro es relativo. Si la referencia es $B7, entonces lacolumna es absoluta y la fila es relativa.

Si copia una fórmula, la referencia relativa se ajusta automáticamente mientras que la referen-cia absoluta no se ajusta. Por ejemplo, si usted copió una fórmula que contenía la referenciamixta $B7 a una celda en la columna C, la fórmula en la celda destino sería =$B8. La referen-cia de columna sería la misma porque esa parte de la fórmula es absoluta. La referencia de filase ajustaría debido a que es relativa.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo crear una fórmulaque contenga una referenciade celda absoluta? 3.1.1

Hacer referencia a datos de otra hoja de cálculo

Usted puede hacer referencia al contenido de las celdas de otra hoja de cálculo dentro delmismo libro. Esta estrategia se emplea frecuentemente para crear un resumen de los datoscontenidos en varias hojas. Los principios para crear fórmulas operan de la misma maneraque para crear fórmulas con los datos dentro de la misma hoja.

Abra el documento LL77--22..33 HHaacceerr rreeffeerreenncciiaa aa llooss ddaattooss ddee oottrraa hhoojjaa ddee ccáállccuulloo que está disponibleen el CD-ROM complementario.

En este ejercicio usted hizo referencia a los datos de otra hoja de cálculo en el libroPresupuesto. En el siguiente ejercicio, hará referencia a los datos de otro libro. Haber cambia-do los nombres de las hojas de este libro, lo preparó para el siguiente ejercicio.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo crear una fórmulaque haga referencia a datosde otra hoja de cálculo? 3.1.2

Hacer referencia a datos de otro libro

Una referencia externa hace referencia a una celda o a un rango de celdas que se encuen-tran en una hoja de cálculo en otro libro de Excel, o bien, a un nombre definido en otrolibro. Las referencias externas son útiles cuando es impráctico tener grandes hojas de cál-culo en un mismo libro

Abra el documento LL77--22..44 HHaacceerr rreeffeerreenncciiaa aa ddaattooss ddee oottrroo lliibbrroo que está disponible en el CD-ROM complementario.

Más adelante en esta lección, aprenderá a asignarle un nombre a un rango de celdas.


Si su administrador de sistemas ha deshabilitado la opción Mostrar todas las ventanas enla barra de tareas, deberá utilizar el comando Cambiar ventanas en el grupo Ventana dela ficha Vista para moverse entre los dos libros.

Más adelante


Aunque las referencias externas son similares a las referencias de celda, existen diferenciasimportantes. Normalmente usted utiliza referencias externas al trabajar con grandes cantidadesde datos y fórmulas complejas que comprenden varios libros.

Cuando crea referencias externas, el libro o libros deben estar abiertos. En un ejercicio ante-rior, usted creó una referencia a otra hoja de cálculo dentro del mismo libro. Un signo deexclamación en la fórmula para esa referencia indicó que el origen estaba en el mismo libro.

Las fórmulas con referencias externas se muestran de dos formas dependiendo de si el libro quecontiene los datos a los que se hace referencia está abierto o cerrado. Cuando el libro de origenestá abierto, la referencia externa presenta corchetes alrededor del nombre, seguido por el nom-bre de la hoja de cálculo, un signo de exclamación (!), y el rango de celda del que depende lafórmula. Cuando el libro de origen está cerrado, se quitan los corchetes y toda la ruta de archivose muestra en la fórmula.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo crear una fórmulaque haga referencia a losdatos de otra hoja decálculo? 3.1.2

� Usar rangos de celdas en fórmulas

Asignar un nombre a un rangoUn nnoommbbrree es una forma abreviada significativa que facilita entender el propósito de unareferencia de celda, de una constante, de una fórmula o de una tabla. Usted puede crearsus propios nombres para representar celdas, rangos de celdas, fórmulas, valores constanteso tablas de Excel. Asignar un nombre a un rango aclara el propósito de los datos que con-tiene. En una fórmula, es fácil entender un rango con nombre mientras que una referen-cia a un rango de celdas es difícil de comprender a primera vista, especialmente cuando elrango en cuestión no es visible.

Abra el documento LL77--33..11 AAssiiggnnaarr uunn nnoommbbrree aa uunn rraannggoo que está disponible en el CD-ROMcomplementario.

TOME NOTA*

Existen varias reglas de sintaxis para crear nombres. Por ejemplo, el primer caracter debeser una letra, un caracter de subrayado (_) o una barra diagonal inversa (\). No puedeusar C, c, R, o r como nombre definido y debe usar el carácter de subrayado o unpunto en lugar de espacios para separar palabras.

TOME NOTA*Puede asignar un nombre a un rango por medio del menú contextual: seleccione elrango y haga clic con el botón derecho del mouse y haga clic en Asignar nombre a unrango para abrir el cuadro de diálogo Nombre nuevo.


Si durante el proceso de asignar un nombre a un rango recibe un mensaje diciendo queel nombre ya existe, abra el Administrador de nombres (del que se habla más adelanteen esta lección) y modifique el nombre existente, elimínelo y comience de nuevo.


¿LISTO PARA LA CERTIFICACIÓN?¿Cómo administrar celdascon nombres? 3.1.3

Todos los nombres tienen un ámbito, ya sea para una hoja de cálculo específica o para todo ellibro. El ámbito de un nombre es la ubicación en la cual el nombre es reconocido sin califica-ción. Por ejemplo, cuando creó el nombre Total_de_ingresos para la celda B7, el cuadroNombre nuevo (el cual se muestra en la Figura 7-9) identificó el ámbito como parte del libro.Esto significa que la celda con nombre puede utilizar fórmulas en las hojas de cálculo Gastos yResumen en este libro.En este ejercicio, usted empleó tres métodos para asignar nombres a celdas y a rangos deceldas. Usted creó los nombres:

• haciendo clic en Definir nombre en la ficha Fórmulas y seleccionando la celda o rangoa ser incluido en el nombre.

• seleccionando una celda o rango y escribiendo el nombre en el cuadro de nombres a unlado de la barra de fórmulas.

• seleccionando una celda o rango que incluía un rótulo y haciendo clic en el botónCrear desde la selección en la ficha Fórmulas.


Para crear un rango con nombre, antes de usar el cuadro Nombre, debe seleccionar elrango de celdas al que desea asignar un nombre. Cuando crea un nombre por medio delcomando Definir nombre, tiene la oportunidad de seleccionar el rango después de intro-ducir el nombre. Esta opción no está disponible cuando emplea el cuadro Nombre.

Asignar un nombre a un rango o a una celda de acuerdo a los datos que contienen es una téc-nica que ahorra tiempo aunque pudiera no parecerlo cuando trabaja con archivos de datospequeños durante los ejercicios de práctica. En el entorno del trabajo, con frecuencia utilizaráhojas de cálculo que contengan datos en cientos de filas o columnas. Después de asignar unnombre a un rango, usted puede seleccionarlo en el cuadro de nombres y después realizar unavariedad de funciones tales como cortarlo y pegarlo en otro libro así como utilizarlo en unafórmula.

Cambiar el tamaño de un rango

Si necesita cambiar los parámetros de un rango con nombre, puede fácilmente redefinir elrango por medio del Administrador de nombres en la ficha Fórmulas. En el siguienteejercicio modificará el rango para Total_de_gastos.

Abra el documento LL77--33..22 CCaammbbiiaarr eell ttaammaaññoo ddee uunn rraannggoo que está disponible en el CD-ROMcomplementario.

XREFÉste es el rango al que le asignó un nombre en un ejercicio anterior por medio del coman-do Crear desde la selección. Aunque parecía que no había sucedido nada cuando creó elrango con nombre, éste fue creado y ahora puede editarlo.

En el ejercicio anterior, usted utilizó el cuadro de diálogo Administrador de nombres paraampliar la referencia de un rango con nombre. También puede cambiar el nombre de unrango o utilizar la función Filtro para mostrar los que cumplen con cierto criterio, por ejem-plo, los nombres cuyo ámbito sea la hoja de cálculo o los nombres cuyo ámbito sea el libro.


Dar seguimiento a rangos

Utilice el cuadro de diálogo Administrador de nombres para trabajar con todos los nom-bres definidos en el libro. Desde este cuadro de diálogo usted puede agregar, cambiar oeliminar un nombre. También le permite confirmar el valor y referencia de una referenciacon nombre o para determinar su ámbito.

Abra el documento LL77--33..33 DDaarr sseegguuiimmiieennttoo aa rraannggooss que está disponible en el CD-ROM comple-mentario.


Si utiliza la barra diagonal en un nombre, recibirá un mensaje de error. Aunque la barradiagonal se emplea en el rótulo faltante/excedente en las hojas de cálculo, sólo puedeusar el carácter de subrayado o la barra diagonal inversa como divisor de palabras en unnombre con rango.

Si definió una referencia con nombre después de haber introducido una referencia de celda enuna fórmula, es posible que desee actualizar la referencia de celda existente con el nombre quedefinió: seleccione una celda vacía, haga clic en la flecha Asignar nombre a un rango y hagaclic en Aplicar nombres...; en el cuadro de diálogo Aplicar nombres, haga clic en uno o másrangos y haga clic en Aceptar.

Usted puede crear una lista de nombres definidos en un libro: seleccione un área de una hojade cálculo con dos columnas vacías, una para el nombre y otra para la descripción; seleccionela celda superior izquierda de la lista; haga clic en Utilizar en la fórmula; haga clic en Pegarnombres y haga clic en Pegar lista.

Puede utilizar un rango con nombre en una fórmula. Usted ha creado varios rangos connombre, que ahora puede emplear para rellenar celdas en las hojas de su libro Presupuesto.

Crear una fórmula que opere en un rango con nombre

Abra el documento LL77--33..44 CCrreeaarr uunnaa ffóórrmmuullaa qquuee ooppeerree eenn uunn rraannggoo ccoonn nnoommbbrree que estádisponible en el CD-ROM complementario.

Utilice el cuadro de diálogo Administrador de nombres para trabajar con todos los nombresdefinidos en el libro. Usted puede ver o editar el ámbito, u ordenar y filtrar la lista de nom-bres.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo crear una fórmulaque opere sobre un rangocon nombre? 3.1.4

� Resumir datos utilizando funciones

Utilizar la función SUMASumar un rango de celdas es uno de los cálculos más comunes que se realizan con losdatos de una hoja de cálculo. Puede utilizar la función SUMA para seleccionar de manerafácil y precisa las celdas que serán incluidas en el cálculo. La función Autosuma hace aúnmás fácil este proceso.


Abra el documento LL77--44..11 UUttiilliizzaarr llaa ffuunncciióónn SSUUMMAA que está disponible en el CD-ROM comple-mentario.


De manera predeterminada, la función Autosuma calcula el total sólo desde la celdaactiva hasta la primera celda no numérica. Debido a que la celda C25 está vacía, ustednecesita modificar el rango que debe considerarse.

En ejercicios anteriores, usted creó una fórmula para realizar sumas tecleando o seleccionandolas celdas a ser incluidas, y conectándolas con el signo más. Utilizar SUMA o la funciónAutosuma es una forma mucho más sencilla de obtener el mismo resultado.

Una función consiste en un nombre de función y argumentos de función. En la fórmula, losargumentos aparecen entre paréntesis. Dependiendo de la función, un argumento puede serun valor constante, una referencia a una sola celda, un rango de celdas o incluso otra función.Si una función contiene varios argumentos, éstos se separan con comas.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo utilizar la funciónSUMA para realizar un cálculo? 3.2.1

TOME NOTA*Dado que la función Autosuma se emplea con mucha frecuencia, está disponible en laficha Fórmulas en el grupo Biblioteca de funciones y en la ficha Inicio en Modificar.

Utilizar la función CONTAR

Las funciones estadísticas, como SUMA y CONTAR se emplean para recopilar y clasifi-car datos con el fin de presentar información relevante. Utilice la función CONTAR paracontar el número de entradas numéricas en un rango.

Abra el documento LL77--44..22 UUttiilliizzaarr llaa ffuunncciióónn CCOONNTTAARR que está disponible en el CD-ROMcomplementario.

TOME NOTA*Conforme agrega argumentos, los campos de valor en el cuadro de diálogo Argumentosde función aumentan para permitirle introducir más argumentos.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo crear una fórmulaempleando la funciónCONTAR? 3.2.1

El texto y las celdas en blanco se ignoran en la fórmula CONTAR. Si una celda contiene unvalor de 0 (cero), la función CONTAR la contará como si fuera una celda con un número.

Utilizar la función CONTARA

Utilice la función CONTARA para contar el número de celdas que no están vacías en unrango. Esta función cuenta tanto el texto como los valores en un rango de datos seleccio-nado.

Abra el documento LL77--44..33 UUttiilliizzaarr llaa ffuunncciióónn CCOONNTTAARRAA que está disponible en el CD-ROMcomplementario.


De manera predeterminada, las funciones empleadas más recientemente se muestrancuando se abre el cuadro de diálogo Insertar función. Usted puede hacer clic en la flechaen el campo de categoría y seleccionar Todas para mostrar una lista de todas las funciones.

OTRA MANERA

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo crear una fórmulaempleando la funciónaCONTARA? 3.2.1

CONTARA devuelve un valor que indica el número de celdas que contienen datos. Las celdasvacías dentro de los rangos de datos son ignoradas.

Utilizar la función PROMEDIO

La función PROMEDIO suma un rango de celdas y divide el resultado entre el númerode entradas de celdas. Por ejemplo, puede ser interesante saber la diferencia promedioentre lo que se presupuestó para gastos y el importe que realmente se gastó durante elmes. Sin embargo, antes de calcular el promedio, tiene que calcular las diferencias.

Abra el documento LL77--44..44 UUttiilliizzaarr llaa ffuunncciióónn PPRROOMMEEDDIIOO que está disponible en el CD-ROMcomplementario.

Aunque introdujo los números (referencias de celda) como un solo número, si abre el cuadrode diálogo Argumentos de función después de que haber introducido la fórmula, cada referen-cia de celda estará en un cuadro de número diferente.El valor exacto devuelto por la fórmula PROMEDIO fue 38.25. Dado que la columna B estáformateada para tener cero decimales, el valor devuelto por la fórmula fue 38.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo calcular el promediode un rango de celdas? 3.2.1

La fórmula MIN devuelve el valor mínimo en un conjunto de valores. Por ejemplo, unprofesor puede emplear la función MIN para determinar la calificación más baja de unexamen. Una empresa de ventas puede determinar qué representante de ventas ganó lacomisión más baja o qué empleado tiene el menor sueldo. Los valores máximos se calculangeneralmente para ese mismo conjunto de datos.

Utilizar la función MIN

Abra el documento LL77--44..55 UUttiilliizzaarr llaa ffuunncciióónn MMIINN que está disponible en el CD-ROM comple-mentario.

Puede desplegar el cuadro de diálogo Insertar función haciendo clic en el botón Insertarfunción en la barra de fórmulas.OTRA MANERA

En este ejercicio, calculó el valor más bajo (mínimo) de un rango de datos. Los argumentosque introdujo en el cuadro de diálogo Argumentos de función pueden ser referencias de celda,un rango con nombre o un número. Si los argumentos no contienen números, la funciónMIN devuelve 0.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo crear una fórmulaque devuelva el número másbajo en un rango de datos? 3.2.1


Utilizar la función MAX

La función MAX devuelve el valor máximo de un conjunto de valores. Los valores míni-mos se calculan generalmente para ese mismo conjunto de datos.

Abra el documento LL77--44..66 UUttiilliizzaarr llaa ffuunncciióónn MMAAXX que está disponible en el CD-ROM comple-mentario.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo encontrar el valormás alto en un rango deceldas? 3.2.1

Los argumentos para la función MAX pueden ser números, nombres o referencias que con-tengan números. Las celdas en blanco dentro del rango son ignoradas.

� Utilizar fórmulas para crear subtotales

Los grupos para calcular los subtotales se crean ordenando los datos. Los datos debenordenarse por grupos para poder insertar una función de subtotal. Los subtotales se calcu-lan con una función de resumen, y usted puede utilizar la función SUBTOTAL paramostrar más de un tipo de función de resumen en cada columna.

Seleccionar rangos para calcular subtotales

Abra el documento LL77--55..11 SSeelleecccciioonnaarr rraannggooss ppaarraa ccaallccuullaarr ssuubbttoottaalleess que está disponible en el CD-ROM complementario.


Si no incluye los rótulos en la selección datos, Excel le pedirá incluirlos para que puedaordenar por rótulo en lugar de por encabezado de columna.

Los subtotales se calculan utilizando una función de resumen, como Suma, Contar oPromedio. Usted puede mostrar más de un tipo de función de resumen para cada columna.Los totales generales, por otra parte, se derivan de los datos de detalle y no de los valores enlos subtotales. Por lo tanto, cuando empleó la función de resumen Promedio, la fila del totalgeneral mostró un promedio de todas las filas de detalle en la lista y no un promedio de losvalores en las filas de subtotales.

Los números de la función SUBTOTAL especifican qué función de subtotal debe emplearsepara calcular los subtotales en su lista. Cuando utiliza el comando Subtotal en la ficha deDatos, usted escoge la función de resumen en una lista desplegable y el número de función seincluye automáticamente en la fórmula de subtotal. Cuando utiliza la función SUBTOTAL,deberá introducir el número de función. La Tabla 7-1 muestra dichos números de función.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo crear subtotales enuna hoja de cálculo? 3.3.1


NÚM_FUNCIÓN NÚM_FUNCIÓN(INCLUYE VALORES OCULTOS) (IGNORA VALORES OCULTOS) FUNCIÓN

1 101 PROMEDIO

2 102 CONTAR

3 103 CONTARA

4 104 MAX

5 105 MIN

6 106 PRODUCTO

7 107 DESVEST

8 108 DESVESTP

9 109 SUMA

10 110 VAR

11 111 VARP

Tabla 7-1

NNúúmmeerrooss ddee ffuunncciióónn ppaarraaSSUUBBTTOOTTAALL

Modificar un rango en un subtotal

Usted puede cambiar la forma en que se agrupan y se calcula el subtotal de los datos,modificando el rango de subtotal por medio de la función SUBTOTAL. Esta opción noestá disponible cuando crea subtotales empleando los comandos de la ficha Datos.

Abra el documento LL77--55..22 MMooddiifificcaarr uunn rraannggoo eenn uunn ssuubbttoottaall que está disponible en el CD-ROMcomplementario.

Cuando modificó el número de función en este ejercicio (cambió el 9 a 109), excluyó subto-tales de suma y promedio para los departamentos individuales. Si no hubiera cambiado elnúmero de función a 109, el resultado de la fórmula habría incluido el sueldo promedio y eltotal de sueldos, así como los sueldos para los empleados individuales.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo crear y modificarsubtotales dentro de unahoja de cálculo? 3.3.1

En el ejercicio anterior, copió y modificó una fórmula para crear un subtotal para ungrupo combinado. Usted puede lograr el mismo resultado por medio de la función SUB-TOTAL para crear una fórmula y agregar subtotales a los datos que no puede o no deseaordenar en una categoría, con el fin de poder emplear la función integrada en la funciónSubtotal de la ficha Datos.

Crear fórmulas para calcular subtotales y totales

Abra el documento LL77--55..33 CCrreeaarr ffóórrmmuullaass ppaarraa ccaallccuullaarr ssuubbttoottaalleess yy ttoottaalleess que está disponible en elCD-ROM complementario.

Cuando utilice el comando Subtotal en la ficha Datos, las entradas de subtotal tienen un for-mato predefinido y usted puede crear varios subtotales como lo hizo en el ejercicio anterior.Cuando utiliza la función SUBTOTAL, usted debe crear la fórmula además de rotular y for-matear las entradas de subtotal de forma manual.


TOME NOTA*Si el libro está configurado para calcular automáticamente las fórmulas, el comando Sub-total vuelve a calcular el subtotal y el total general de manera automática cada vez queusted modifica los datos de detalle.

� Controlar el aspecto de las fórmulas

Mostrar las fórmulas en la pantalla

Cuando crea una fórmula, el resultado del cálculo se muestra en la celda y la fórmula semuestra en la barra de fórmulas. Usted podría necesitar ver todas las fórmulas en la panta-lla para auditarlas. Puede hacer clic en el comando Ver fórmulas para mostrar la fórmulaen cada celda en lugar del valor resultante..

Abra el documento LL77--66..11 MMoossttrraarr llaass ffóórrmmuullaass eenn ppaannttaallllaa que está disponible en el CD-ROMcomplementario.

Por razones de seguridad, puede que desee ocultar las fórmulas a otros usuarios del libro.Éstos y otros temas de seguridad se presentarán en la Lección 11.Más adelante

Si trabaja con fechas y horas, descubrirá que útil es entender el sistema de fechas y horas deExcel. Aunque normalmente no tiene que preocuparse por series de números seriales, cuandomostró las fórmulas de la hoja de cálculo en el ejercicio anterior, probablemente se preguntóqué había pasado con los números en su hoja de cálculo. Excel almacena las fechas comonúmeros seriales secuenciales. De manera predeterminada, el 1 enero, 1900, es el número 1, el2 enero, 1900, es el número 2, etcétera. Este sistema de fechas numéricas seriales le permiteutilizar fechas en las fórmulas. Por ejemplo, usted puede introducir una fórmula para calcularel número de días que ha vivido creando una fórmula que reste su fecha de nacimiento de lafecha del día de hoy.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo mostrar las fórmulasen una hoja de cálculo? 3.8

Imprimir fórmulasCuando audita las fórmulas en una hoja de cálculo grande, puede ser de utilidad impri-mir la hoja con las fórmulas visibles. Para obtener el máximo beneficio de la copia impre-sa, imprima las líneas de cuadrícula y los encabezados de filas y columnas.

Abra el documento LL77--66..22 IImmpprriimmiirr ffóórrmmuullaass que está disponible en el CD-ROMcomplementario.

¿LISTO PARA LA CERTIFICACIÓN?¿Cómo imprimir una hoja decálculo con las fórmulas visibles? 3.8


MM AA TT RR II ZZ DD EE RR EE SS UU MM EE NN DD EE HH AA BB II LL II DD AA DD EE SS

EN ESTA LECCIÓN USTED APRENDIÓ HABILIDAD DE LA MATRIZ NÚMERO DE HABILIDAD

A crear fórmulas para realizar Crear fórmulas que utilicen referen- 3.1.1funciones matemáticas básicas. cias de celda absolutas y relativas.

A utilizar referencias en fórmulas.

A crear fórmulas que contengan Crear fórmulas que utilicen referen- 3.1.1referencias de celda que cambien cias de celda absolutas y relativas.en relación a la ubicación de las celdas a las que se refiere.

A crear referencias de celda que no Crear fórmulas que utilicen referen- 3.1.1cambien cuando se copie la fórmula. cias de celda absolutas y relativas.

A crear fórmulas que hagan Crear fórmulas que hagan referencia 3.1.2referencia a datos de otra hoja a los datos de otras hojas de cálculode cálculo en el mismo libro. o libros.

A crear fórmulas que hagan Crear fórmulas que hagan referencia 3.1.2referencia a datos de otro libro. a los datos de otras hojas o libros.

A crear y utilizar celdas con nombreen fórmulas.

A utilizar el Administrador de nombres Administrar los rangos con nombre. 3.1.3para dar seguimiento a rangos.

A crear fórmulas que operen en Utilizar rangos con nombres 3.1.4rangos con nombre. en fórmulas.

A resumir datos por medio de Utilizar las funciones SUMA, 3.2.1funciones. CONTAR, CONTARA, PROMEDIO,

MIN y MAX.

A utilizar fórmulas para crear Crear y modificar rangos de lista. 3.3.1subtotales.

A controlar el aspecto de Mostrar e imprimir fórmulas. 3.8las fórmulas.

Documents

Excel - Leccion 7