>
<
<
3. El texto debe ser Verdana de 10 puntos y cursiva. La imagen debe en la parte superior, utilizar el botón formato de imagen para ajustar la a la hoja de Excel. 4. Guarde el archivo y cierre el documento.
El archivo debe quedar de la siguiente manera.
Programa Nacional de Informática
85
Microsoft Office Excel 2010
Esta vista previa se muestra en la nueva vista Backstage del Microsoft Office Excel 2010.
Ficha Hoja de la ventana Configurar Página
Esta ficha permite controlar ciertas opciones al momento de imprimir como: el área de impresión, la impresión o no de las líneas de división de la hoja, que se impriman o no los títulos de las filas y las columnas en su hoja (A, B, C etc. y 1,2, 3 etc.) Excel muestra una caja de dialogo con las siguientes opciones:
86
Programa Nacional de Informática
Microsoft Office Excel 2010
1. Active la casilla Líneas de División para imprimir las líneas de su hoja de cálculo. 2. La casilla Encabezados de filas y columnas imprime las columnas y filas de su hoja 3. Use el cuadro Comentarios para indicar la ubicación de los comentarios en su hoja.
En la siguiente imagen se muestran las Líneas de División y los encabezados de Fila y Columna activos.
Imprimir rótulos de fila y columna en cada página Programa Nacional de Informática
87
Microsoft Office Excel 2010
Si su tabla es muy grande y ocupa más de una página, usted puede imprimir un rango de Filas como encabezados de cada una de ellas. Incluso podrá tener columnas como títulos de encabezados de filas. Por ejemplo, abra el archivo Alumnos y use el botón Vista Preliminar. Observe que los títulos sólo se muestran en la primera página.
Página 1
Página 2
Para tener filas o columnas como títulos realice lo siguiente: 1. Ingrese a la ventana Configurar Página y elija la ficha Hoja. 2. En el cuadro Imprimir Títulos, haga clic en la opción Repetir filas en el extremo Superior.
88
Programa Nacional de Informática
Microsoft Office Excel 2010
Seleccionar la primera fila.
3. Use el botón vista preliminar para ver el resultado.
Página 1
Página 2
Programa Nacional de Informática
89
Microsoft Office Excel 2010
Saltos de página
Los saltos de página marcan la hoja de cálculo indicando cada una de las páginas a imprimir. Estos saltos se insertan de acuerdo a las especificaciones de la configuración de página: tamaño de papel, escala, márgenes, etc. Identificar los saltos de página Los saltos de página pueden ser horizontales y verticales y se muestran en su hoja de cálculo como líneas punteadas.
En la hoja de cálculo anterior, los saltos se ven como líneas punteadas. Aquí se muestra un salto horizontal entre las filas 6 y 7.
Insertar Saltos de página Por defecto los saltos de página se insertan en hojas que ocupan más de una página. Usted puede insertar saltos de páginas horizontales o verticales para indicar hasta que fila o columna desea imprimir. En nuestro ejemplo queremos que la segunda página a imprimir se muestre a partir de la fila 39 y no de 43 que fue determinada por Excel. NOTA: Saque una copia del archivo original y efectúe todos los cambios que se mencionaran sobre la copia.
Insertar un salto de página horizontal:
1. Lleve el selector de celdas a la fila donde desea que se realice el salto, en este caso lo colocaremos en la fila 11 para que a partir de esta fila se
90
Programa Nacional de Informática
Microsoft Office Excel 2010
visualice en una nueva página la información que continua según la hoja de Excel. 2. Haga clic en el botón Saltos y luego en Insertar salto de página.
3. Se mostrara una línea punteada de división a partir de la línea 11 como se muestra en la siguiente figura.
Salto de página.
4. Utilizando la vista preliminar verifique que solo aparece hasta la fila 10 en la primera página, el resto aparece en la página siguiente, esto se pudo realizar gracias al salto de página.
Programa Nacional de Informática
91
Microsoft Office Excel 2010 En la segunda página se muestra desde la fila 11. Solo se muestra hasta la fila 10.
Ahora con el mismo archivo vamos a insertar un saldo de página, pero esta vez para columnas, ya que se desea que la columna Nombre Completo se muestre en otra página.
Insertar un salto de página vertical:
1. Lleve el indicador a la columna donde se desea que se realice el salto, en nuestro ejemplo en la columna B1 tal como se muestra en la figura.
2. Haga clic en el botón Saltos y luego en Insertar salto de página y aparecerán las líneas punteadas en vertical.
3. Utilice la vista preliminar y verificara que ahora aparecen en páginas diferentes las columnas, ya que están separadas por un salto de página en vertical.
92
Programa Nacional de Informática
Microsoft Office Excel 2010 Solo se muestra la primera columna en la página 1
La segunda columna se muestra en la página siguiente dependiendo de la cantidad de registros.
Eliminar los Saltos de página Excel aplica los saltos que fueron detectados y los elimina. En el caso de hojas que no ocupen más de una página simplemente los saltos ya no se verían. Para hacer este ejercicio vamos a quitar el último salto de página vertical que hemos insertado, el que se encuentra entre las columnas A y B. Procedimiento: 1. Llevar el indicador de celdas en la misma celda que indico para insertar el salto de página (En nuestro caso en la columna B). 2. Haga clic en el botón Saltos y luego en Quitar salto de página.
NOTA: La opción Restablecer todos los saltos de página elimina todos los saltos encontrados en la hoja.
Programa Nacional de Informática
93
Microsoft Office Excel 2010
Vista Previa de salto de página Esta vista le permite reconocer fácilmente los saltos de página insertados en su hoja.
Configuración de la impresión Vista Preliminar Esta vista muestra su tabla antes de imprimirla. Aquí usted podrá controlar las opciones y características de la impresión.
Impr esió n Rápi da
Si desea imprimir rápidamente su tabla (Hoja de Excel), haga clic en el botón personalizar barra de herramientas de acceso rápido para agregar el comando Impresión rápida y luego ejecute este comando.
94
Programa Nacional de Informática
Microsoft Office Excel 2010
Al usar esta opción, Excel imprime su tabla con las opciones por defecto, tanto de la configuración de página como la configuración de la impresora. Si usted configuro su página se imprimirá con las opciones elegidas. Configurar Impresión Si desea configurar las opciones para imprimir, diríjase ficha Archivo y elija la opción Imprimir.
Excel muestra la siguiente caja de dialogo Imprimir en la nueva vista Backstage que presenta una nueva forma de mostrar las opciones de impresión.
El proceso de Impresión Para imprimir no es necesario indicar el rango de celdas a imprimir, ya que Excel las detecta automáticamente. Además, usted podrá modificar las características de su hoja (tamaño, orientación, márgenes, etc.) antes de enviar el trabajo a la impresora.
Programa Nacional de Informática
95
Microsoft Office Excel 2010
La Opción Impresora:
Especifique la impresora a usar. Incluso puede modificar las propiedades de la impresora seleccionada como: tamaño de hoja, si la impresión será a color o blanco y negro, la calidad de impresión, etc.
Configuración: Hojas Activas Seleccione la opción Hojas Activas para imprimir su área de impresión o las hojas seleccionadas. Si desea imprimir todas las hojas de su libro elija la opción Todo el Libro. Si elija la opción Selección, Excel ignora el área de impresión e imprime el rango de celdas seleccionados.
96
Programa Nacional de Informática
Microsoft Office Excel 2010
Definir el Área de Impresión. No es necesario que usted indique el rango o tabla a imprimir, Microsoft Excel detecta automáticamente el contenido de la hoja y define el rango de impresión. Usted puede personalizar el área de impresión, indicando que se va a imprimir sólo la parte superior de su hoja o una tabla que se encuentra en su hoja. Para indicar el área de impresión. 1) Se selecciona el rango de celdas que desea imprimir. 2) Haga clic en la ficha Diseño de página. 3) Luego clic en el comando Área de impresión. 4) Escoger la opción Establecer área de impresión.
Intervalo de páginas a imprimir
Indique si desea imprimir todas las páginas de su tabla o sólo alguna página en particular.
Programa Nacional de Informática
97
Microsoft Office Excel 2010
Intervalo de páginas.
Página inicial
Página Final
Número de Copias Esta opción nos permite obtener una cantidad determinado de copias por cada página de Excel.
Intercalar páginas. Intercaladas. Puede configurar la forma en que el Excel va a imprimir sus hojas, por ejemplo si va a sacar 3 copias(juegos) por cada página se imprimirá primero las 3 páginas consecutivas, luego se imprimen otras 3 desde la primera hasta la última y al final las ultimas 3 de la misma manera. Sin Intercalar Siguiendo el ejemplo anterior, si quiero imprimir 3 juegos y esta activada la opción Sin intercalar, primero imprimirá la página 1 3 veces, luego la página 2 3 veces y lo mismo con la pagina 3, hasta obtener los 3 juegos de copias. Orientación Dependiendo si desea que su hoja de Excel salga impresa de manera horizontal o vertical en relación a la hoja seleccionara la opción respectiva tal como se muestra en la siguiente imagen.
98
Programa Nacional de Informática
Microsoft Office Excel 2010
Tamaño de Papel Podemos configurar cual es el tamaño de papel que vamos a utilizar para imprimir nuestra hoja de Excel.
Márgenes Personalizados El margen de la hoja es la distancia que hay desde el borde de la hoja hasta donde inicia el texto, con esta opción podemos configurar esta distancia dependiendo del resultado que queremos obtener, Podemos escoger entre las opciones Normal, ancho o estrecho que ya viene por defecto o asignar nuestros propios márgenes dándole clic en la opción Márgenes personalizados.
Escalado.
Con esta opción ajustar el contenido de la hoja de cálculo a una página, ya sean filas, columnas o todo en general.
Programa Nacional de Informática
99
Microsoft Office Excel 2010
Preguntas de repaso 1. ¿Qué significa establecer el área de impresión? 2. ¿De cuantas maneras puedo cambiar la orientación de la pagina en Excel? 3. ¿Para qué sirve la opción intercalar? 4. ¿En qué casos me sirve la opción imprimir todo el libro? 5. ¿Qué es un salto de sección? 6. Si tengo una tabla con información que ocupa varias hojas, ¿Que opción debo activar para que los títulos de las columnas aparezcan en todas las hojas? 7. ¿Cómo visualizo todos los saltos de página que tiene mi hoja de Excel? 8. ¿Qué pasos debo de hacer para imprimir los encabezados de columnas y filas de mi hoja de cálculo.
100
Programa Nacional de Informática
Microsoft Office Excel 2010
Capítulo
4 CREACIÓN Y MODIFICACIÓN DE FÓRMULAS I En este capítulo se trataran los siguientes temas: Introducción a las fórmulas Realizar auditorías a las fórmulas Referencia de celdas y rangos Utilizar funciones aritméticas Introducción: Reconocer el entorno de trabajo y familiarizarse con las principales funciones, configuraciones, servicios y características que tiene el Microsoft Office Excel 2010.
Programa Nacional de Informática
101
Microsoft Office Excel 2010
CONCEPTOS PREVIOS PARA LA CREACIÓN DE FÓRMULAS Esta unidad es la unidad una de las más importantes del curso, pues en su comprensión y manejo está la base de Excel. Qué es una hoja de cálculo sino una base de datos que utilizamos con una serie de fórmulas para evitar tener que re calcular por cada cambio que hacemos. Por eso esta unidad es fundamental para el desarrollo del curso y la buena utilización de Excel. Vamos a profundizar en el manejo de funciones ya definidas por Excel 2007 para agilizar la creación de hojas de cálculo, estudiando la sintaxis de éstas así como el uso del asistente para funciones, herramienta muy útil cuando no conocemos muy bien las funciones existentes o la sintaxis de éstas. INTRODUCCIÓN A LAS FÓRMULAS Una función es una fórmula predefinida por Excel 2010 (o por el usuario) que opera con uno o más valores y devuelve un resultado que aparecerá directamente en la celda o será utilizado para calcular la fórmula que la contiene. La sintaxis de cualquier función es: nombre_función(argumento1;argumento2;...;argumentoN). Siguen las siguientes reglas: Si la función va al comienzo de una fórmula debe empezar por el signo =. Los argumentos o valores de entrada van siempre entre paréntesis. No dejes espacios antes o después de cada paréntesis. Los argumentos pueden ser valores constantes (número o texto), fórmulas o funciones. Los argumentos deben de separarse por un punto y coma (;) según corresponda.
OPERADORES Excel permite que en una función tengamos varios operadores para tratar los datos. Los operadores son símbolos que identifica Excel con operaciones aritméticas y es el enlace entre 2 argumentos.
102
Programa Nacional de Informática
Microsoft Office Excel 2010
En la tabla podemos ver los operadores más utilizados.
SIMBOLO DEL OPERACIÓN QUE OPERADOR REALIZA +
Suma
-
Resta
*
Multiplicación
/
División
^
Exponenciación
&
Unión/Concatenar
=
Igual que
>
Mayor que
<
Menor que
>=
Igual o Mayor que
<=
Igual o Menor que
<>
Distinto
En una fórmula o función pueden utilizarse tanto operadores como sea necesario teniendo en cuenta siempre que los operadores hacen siempre referencia a dos argumentos. Pueden crearse fórmulas verdaderamente complejas. Veamos un ejemplo. = ((SUMA(A1:A7)*SUMA(B1:B7)) / (SUMA(C1:C7)*SUMA(D1:D7)))=(F8*SUMA(G1:G5))
JERARQUÍA Hemos visto que una fórmula puede ser muy compleja, utilizando multitud de operadores. Excel como cualquier operador matemático tiene unas ciertas reglas para saber que operaciones debe realizar primero para que el resultado obtenido sea el correcto. En la siguiente tabla mostramos las precedencias establecidas por Excel 2010.
Programa Nacional de Informática
103
Microsoft Office Excel 2010
SIMBOLO DEL OPERACIÓN QUE PRECEDENCIA OPERADOR
REALIZA
^
EXPONENCIACIÓN
1
*
MULTIPLICACIÓN
2
/
DIVISIÓN
2
+
SUMA
3
-
RESTA
3
&
UNION/CONCATENAR
4
=
IGUAL QUE
5
>
MAYOR QUE
5
<
MENOR QUE
5
>=
MAYOR O IGUAL QUE
5
<=
MENOR O IGUAL QUE
5
<>
DISTINTO
5
Además de esta tabla de precedencias la precedencia máxima, es decir la operación que antes se evalúa, es aquella que va entre paréntesis. Veamos pues como resolvería la formula que hemos visto anteriormente como ejemplo: Podemos ver que hay 10 operaciones:
104
5 SUMAS 3 MULTIPLICACIONES 1 DIVISION 1 COMPARACION
Programa Nacional de Informática
Microsoft Office Excel 2010
CÁLCULOS SENCILLOS Para ingresar una fórmula en una celda, se debe introducir como primer carácter el signo igual (El signo igual = comenzando desde una celda indica que constituye una fórmula).
Una fórmula sencilla puede ser de constantes y operadores, por ejemplo:
=250+320 =1000-369 =8*5 =3^3
También puede contener referencias de celdas, rangos o nombres, por ejemplo:
=A3+C6 =Hoja1! B2-E5 =SUBTOTAL*IGV =BASICO-DESCUENTO
Como también hay fórmulas complejas que contienen funciones, por ejemplo:
=SUMA (A2:A10) =PRODUCTO (A11:A12) =MAX (B5:B10) =CONTAR.SI (A2:A10;”APROBADO”)
Como por ejemplo para escribir fórmulas, tenemos el siguiente caso de la Hoja Bonificación del Libro Formula I.xlsx.
Programa Nacional de Informática
105
Microsoft Office Excel 2010
ACTIVIDAD Cálculos Sencillos Se tienen los datos en la hoja Bonificación en el libro Formulas I Resuelto.xlsx
Hallaremos el Total de Bonificación, que sería la multiplicación del Sueldo Básico por los meses y dividido entre 12 (1 año en meses). Realice el siguiente procedimiento. 1. Haga clic en la celda donde se desea mostrar el resultado, en nuestro ejemplo en la celda B5 2. Escriba la siguiente fórmula: =B3*B4/12 Observe que el resultado se muestra el resultado de la fórmula. Ahora, se nos pide calcular el Descuento, que corresponde a una diferencia entre el Sueldo Básico y Total Bonificación. Para ello realice lo siguiente: 3. Haga clic en la celda B7 4. Escriba la fórmula: =B3-B5 5. El resultado se muestra de acuerdo a la siguiente imagen:
106
Programa Nacional de Informática
Microsoft Office Excel 2010
CALCULAR PORCENTAJES Es muy fácil calcular porcentajes en Excel, recuerde que basta con escribir la fórmula considerando los datos que intervienen. En este caso, se pide calcular el Descuento Essalud (9%) del Básico: Se tiene la hoja de Datos Descuento:
Recuerde que cuando usted desea calcular un porcentaje, sólo se debe multiplicar el valor por el porcentaje.
1. Lleve el indicador de celdas a la celda E5 2. Escriba la fórmula para calcular el Básico (Pago Día por Día Trabajado). =C5*D5 3. Ahora llevamos el indicador a la celda F5 para obtener el Descuento Essalud 4. Escriba la fórmula para calcular Essalud 9%. =E5*9% 5. Por último para finalizar nos desplazamos a la celda G5 para obtener el Neto a Pagar 6. Escriba la fórmula para calcular Neto a Pagar. =E5-F5 7. El resultado se muestra de acuerdo a la siguiente imagen:
Programa Nacional de Informática
107
Microsoft Office Excel 2010
REALIZAR AUDITORIAS DE FORMULAS A veces, la comprobación de las fórmulas (fórmula: secuencia de valores, referencias de celda, nombres, funciones u operadores de una celda que producen juntos un valor nuevo. Una formula comienza siempre con el signo igual (=).)para ver si son precisas o para encontrar el origen de un error puede resultar difícil cuando la fórmula usa celdas precedentes o dependientes:
Rastrear precedentes
Son celdas a las que hace referencia una fórmula de otra celda. Como por ejemplo lo veremos a continuación:
1. Para realizar esta acción active la hoja Rastrear precedentes del Libro Auditoria de Fórmulas.xlsx.
108
Programa Nacional de Informática
Microsoft Office Excel 2010
2. Seleccione la celda E18. 3. Diríjase a la Ficha precedentes.
FórmulasAuditoría
de
FórmulasRastrear
4. Observará el siguiente resultado:
Programa Nacional de Informática
109
Microsoft Office Excel 2010
Podemos ver que el Neto se obtuvo a través del Subtotal e IGV. Rastrear dependientes – Quitar flechas
Dibuja flechas indicando a qué fórmula pertenece la celda seleccionada, si esque pertenece a alguna fórmula.
1. Para realizar esta acción active la hoja Rastrear dependientes del Libro Auditoria de Fórmulas.xlsx.
2. Seleccione la celda E15. 3. Diríjase a la Ficha precedentes.
110
FórmulasAuditoría
de
FórmulasRastrear
Programa Nacional de Informática
Microsoft Office Excel 2010
4. Observará el siguiente resultado:
Podemos ver que el Neto depende del IGV (celda E15), para su resultado. Quitar flechas
Elimina todas las dependencias y rastreos.
1. Quitar un nivel de precedentes. Quita un nivel de procedencia del rastreo de losvalores. 2. Quitar un nivel de dependientes. Quita la flecha de la dependencia de alguna fórmula
Veremos en el siguiente ejemplo 1. Para realizar esta acción active la hoja Quitar flechas del Libro Auditoria de Fórmulas.xlsx.
Programa Nacional de Informática
111
Microsoft Office Excel 2010
Seleccione la celda E11. 2. Diríjase a la Ficha FórmulasAuditoría de FórmulasQuitar flechas Quitar un nivel de precedentes. 3. Veremos el siguientes resultado:
Errores típicos en la creación de fórmulas Analiza la hoja de trabajo actual en busca de errores. 1. Comprobación de errores. 112
Programa Nacional de Informática
Microsoft Office Excel 2010
Busca errores comunes en las fórmulas. Ejemplo. a. Para realizar esta acción active la hoja Comprobación de errores del Libro Auditoria de Fórmulas.xlsx.
b. Diríjase a la Ficha FórmulasAuditoría de FórmulasComprobación de errores. c. El sistema nos mostrara el primer error dentro de la hoja de la siguiente manera:
d. Aquí tenemos varias opciones dentro de dicha ventana, a continuación se describe cada una de ellas:
Ayuda sobre este error: El sistema muestra su ventana ayuda.
Programa Nacional de Informática
113
Microsoft Office Excel 2010
Mostrar pasos de cálculo: Muestra los valores que se están operando.
Omitir error: Pasar al siguiente error sin corregirlo.
Modificar en la barra de fórmulas: Nos desplaza mediante el cursor para poder modificar en la barra de fórmulas.
Anterior: Regresar al error anterior.
Siguiente: Pasar al siguiente error.
e. Accederemos por escoger el botón Modificar en la barra de fórmulas.
f. Accederemos por escoger el botón Reanudar y regresaremos a la ventana anterior para seguir corrigiendo los siguientes errores. g. Y cuando terminemos de corregir se mostrar la siguiente ventana de finalización.
Rastrear error. Muestra las celdas vinculadas al error. Ejemplo.
114
Programa Nacional de Informática
Microsoft Office Excel 2010
a. Para realizar esta acción active la hoja Comprobación de errores del Libro Auditoria de Fórmulas.xlsx. b. Seleccione el primer error (celda E9)
c. Diríjase a la Ficha FórmulasAuditoría de FórmulasComprobación de erroresRastrear error. d. El sistema nos mostrara las celdas que conforman el error, de esta manera:
Programa Nacional de Informática
115
Microsoft Office Excel 2010
Referencias circulares. Cuando una fórmula hace referencia a su propia celda, directa o indirectamente, se denomina referencia circular. Microsoft Office Excel no puede calcular automáticamente todos los libros abiertos cuando uno de ellos contiene una referencia circular. Puede quitar la referencia circular, o bien hacer que Excel calcule cada celda de dicha referencia utilizando los resultados de la iteración anterior. Si no se cambia el valor predeterminado de la iteración, Excel detendrá los cálculos tras 100 iteraciones o después de que todos los valores en la referencia circular cambien menos de 0,001 entre iteraciones, lo que se cumpla en primer lugar.
ACTIVIDAD 1. Para realizar esta acción active la hoja Comprobación de errores del Libro Auditoria de Fórmulas.xlsx. 2. Diríjase a la Ficha FórmulasAuditoría de fórmulasComprobación de erroresReferencias circulares.
3. El sistema nos mostrará las celdas que conforman las referencias circulares. REFERENCIAS DE CELDAS Y RANGOS Cuando trabajamos en Ms Excel y más concretamente cuando hacemos usos de fórmulas y funciones casies seguro que pongamos referencias a celdas o conjunto de celdas que no son propiamente la mismacelda donde tenemos la formula. Las referencias son enlaces a un lugar, es decir, cuando en unaformula escribimos =SUMA(A1;B1) nos estamos refiriendo a que sume el contenido de A1 y elcontenido de B1.
Existen 3 tipos de referencias. 116
Programa Nacional de Informática
Microsoft Office Excel 2010
Relativas, Absolutas y Mixtas 1. Relativas Las referencias de filas y columnas cambian si se copia la formula en otra celda, es decir se adapta a su entorno porque las referencias las hace con respecto a la distancia entre la formula y las celdas que forman parte de la formula. Esta es la opción que ofrece Excel por defecto. Ejemplo.
a. Para realizar esta acción active la hoja Referencias relativas del Libro Auditoria de Fórmulas.xlsx.
b. Desarrollaremos el Importe = Monto de Compra x % indicado c. Vemos que las celdas hacen referencias relativas ya que se escriben de manera directa para que esta cambian de referencia cuando cambiemos de posicion para los siguientes resultados.
Programa Nacional de Informática
117
Microsoft Office Excel 2010
Absolutas Las referencias de filas y columnas no cambian si se copia la formula a otracelda, las referencias a las celdas de la formula son fijas. Ejemplo. a. Para realizar esta acción active la hoja Referencias absolutas del Libro Auditoria de Fórmulas.xlsx.
b. Desarrollaremos el Precio en Dolares según el tipo de cambio de la tabla inferior. 118
Programa Nacional de Informática
Microsoft Office Excel 2010
c. Vemos que las celdas hacen referencias relativas ya que se escriben anteponiendo el símbolo de $ tanto a la columna como a la fila para que ésta no cambie de referencia a pesar del cambio de ubicación. Mixtas Podemos hacer una combinación de ambas referencias, podemos hacer que lasfilas sean relativas y las columnas absolutas o viceversa. Ejemplo. a. Para realizar esta acción active la hoja Referencias mixtas del Libro Auditoria de Fórmulas.xlsx.
b. Desarrollaremos la tabla de multiplicar mediante las columnas y filas. Lo cual para poder desarrollar el ejemplo tendremos que trabajar con referencias mixtas de esta manera.
c. Al termino de la referencia podemos hacer el arrastre vertical y horizontal para obtener todos los resultados como podemos apreciar a continuación.
Programa Nacional de Informática
119
Microsoft Office Excel 2010
Comportamiento al Copiar o mover celdas con una o mas tipos de referencias (Pegado especial de Excel 2010) Es importante que sea consciente de lo que ocurre a las referencias de celdas, independientemente si son absolutas o relativas, cuando se mueve una fórmula mediante el método de cortar y pegar, o cuando se copia una fórmula mediante el método copiar y pegar.
Cuando se mueve una fórmula, las referencias de celdas existentes en ella no cambian, independientemente del tipo de referencia que utilice.
Cuando se copia una fórmula, las referencias pueden cambiar en función del tipo de referencia de celda que utilice.
En la tabla siguiente se indica cómo se actualiza un tipo de referencia si la fórmula que la contiene se copia de dos celdas hacia abajo y dos hacia la derecha.
Para una fórmula que se va a copiar:
120
Si la referencia es:
Cambia a:
Columna absoluta
absoluta
y
fila
$A$1
Columna absoluta
relativa
y
fila
C$1
Columna relativa
absoluta
y
fila
$A3
Columna relativa y fila relativa
C3
Programa Nacional de Informática
Microsoft Office Excel 2010
En el caso de cortar o mover la referencia no cambia independientemente del tipo de referencia que utilice. Referencias a múltiples Hojas y rangos
Una referencia a la misma celda o al mismo rango en varias hojas se denomina referencia 3D. Una referencia 3D es un método útil y cómodo de hacer referencia a varias hojas de cálculo que siguen el mismo patrón y a las celdas de cada hoja de cálculo que contienen el mismo tipo de datos para, por ejemplo, consolidar los datos presupuestarios de diferentes departamentos de la organización. Puede utilizar la siguiente referencia 3D para sumar las asignaciones presupuestarias de tres departamentos (Ventas, Recursos Humanos y Marketing), cada uno en una hoja de cálculo diferente: =SUMA(Sales:Marketing!B3) Puede incluso agregar otra hoja de cálculo y después moverla al rango al que hace referencia la fórmula. Por ejemplo, para agregar una referencia a la celda B3 de la hoja de cálculo Instalaciones, mueva la hoja Instalaciones entre las hojasVentas y RH como se muestra en el siguiente ejemplo. Debido a que la fórmula contiene una referencia 3D a un rango de nombres de hojas de cálculo, Ventas:Marketing!B3, todas las hojas de cálculo del rango se incluyen en el nuevo cálculo. RANGOS Asignar nombres Se le considera rango a un conjunto de celdas conformadas por una o más celdas. Ejemplo.
a. Para realizar esta acción active la hoja Nombres del Libro Rangos.xlsx. b. Seleccione el rango de celdas B4:B8 Programa Nacional de Informática
121
Microsoft Office Excel 2010
c. Nos desplazamos al cuadro de nombre y hacemos clic.
d. Escribimos el nombre que le vamos asignar al rango y presionar Enter.
Utilizar en la fórmula Puede usar los nombres de los rangos en fórmulas o funciones. Ejemplo.
a. Para realizar esta acción active la hoja Nombres del Libro Rangos.xlsx.
122
Programa Nacional de Informática
Microsoft Office Excel 2010
b. Seleccione el rango de celdas B9 c. Sumar a traves del nombre del rango que le corresponde.
d. Y presionar enter. Utilizar rótulos de filas para crear nombres desde la selección
Puede usar los títulos de sus filas y columna para crear nombres de rangos. Ejemplo.
a. Para realizar esta acción active la hoja Nombres del Libro Rangos.xlsx. b. Seleccione el rango de celdas B3:G8 Programa Nacional de Informática
123
Microsoft Office Excel 2010
c. Ubíquese en la Ficha FórmulasSección Nombres Crear desde la selección.
d. El Sistema nos mostrará la siguiente ventana.
e. Lo cual se elegirá la fila superior para agregar dichos nombres. 124
Programa Nacional de Informática
Microsoft Office Excel 2010
f. Ahora podemos verificar a través del cuadro de nombres.
Administrar rango de celdas Se pueden asignar nombres a rangos de celdas previamente seleccionados y pueden ser utilizados en fórmulas o funciones desde cualquier hoja de nuestro libro en uso o incluso desde otro libro (en este caso debe indicar el libro). Como ya mencionamos anteriormente un rango puedes ser representado de esta manera A2:A20 ó B4:D5. Una celda puede ser considerada como rango con la siguiente referencia A2:A2 Podemos administrar los nombres de los rangos a través de la Ficha Fórmulas.
Donde veremos en la siguiente figura el listado de todos los nombres utilizados en su libro con sus respectivos valores y referencias, incluso si éstas están utilizadas en una función.
Programa Nacional de Informática
125
Microsoft Office Excel 2010
Desde esta ventana podemos editar las referencias así como eliminar un nombre determinado. Como por ejemplo eliminaremos el nombre Septiembre. Para ello seleccionaremos el nombre a eliminar.
Presionaremos el botón eliminar y nos mostrará la siguiente ventana.
126
Programa Nacional de Informática
Microsoft Office Excel 2010
Aquí tendremos que aceptar para eliminar dicho nombre. Cálculos simples con operadores matemáticos ENTERO(número) Redondea un número hasta el entero inferior más próximo. La sintaxis de la función ENTERO tiene los siguientes argumentos:
Número Obligatorio.es el número real que desea redondear al entero inferior más próximo.
Ejemplo. g. Para realizar esta acción active la hoja Entero del Libro Rangos.xlsx.
Para poder obtener los valores enteros de los números con decimales realizaremos lo siguiente.
h. En donde obtendremos lo siguiente
Programa Nacional de Informática
127
Microsoft Office Excel 2010
REDONDEAR(número;num_decimales) La función REDONDEAR redondea un número a un número de decimales especificado. La sintaxis de la función REDONDEAR tiene los siguientes argumentos:
Número Obligatorio.Número que se desea redondear. Num_decimales Obligatorio.Número de decimales al que se desea redondear el argumento de número.
ACTIVIDAD a. Para realizar esta acción active la hoja Redondear del Libro Rangos.xlsx.
b. Para poder redondear los valores (P.N.) a 0 decimales, realizaremos lo siguiente:
128
Programa Nacional de Informática
Microsoft Office Excel 2010
c. En donde obtendremos lo siguiente
SUMA(número1;número2;…) Suma todos los argumentos de un rango. La sintaxis de la función SUMA tiene los siguientes argumentos:
Número1;número2
Programa Nacional de Informática
Son de 1 a 30 argumentos que desea sumar.
129
Microsoft Office Excel 2010
ACTIVIDAD a. Para realizar esta acción active la hoja Suma del Libro Rangos.xlsx
b. Para poder obtener el Total debemos sumar las regiones por mes, de esta manera:
c. En donde obtendremos lo siguiente 130
Programa Nacional de Informática
Microsoft Office Excel 2010
PROMEDIO (número1;número2;…) Devuelve el promedio (media aritmética) de los argumentos. La sintaxis de la función PROMEDIO tiene los siguientes argumentos:
Número1
Obligatorio. El primer número, referencia de celda o rango
para el que desea el promedio.
Número2
Opcional. Números, referencias de celda o rangos adicionales para los que desea el promedio, hasta un máximo de 255.
Ejemplo.
a. Para realizar esta acción active la hoja Promedio del Libro Rangos.xlsx.
Programa Nacional de Informática
131
Microsoft Office Excel 2010
b. Para poder obtener el Promedio por Región debemos promediar las regiones, de esta manera:
c. En donde obtendremos lo siguiente 132
Programa Nacional de Informática
Microsoft Office Excel 2010
MAX(número1;número2;…) Devuelve el valor máximo de un conjunto de valores. La sintaxis de la función MAX tiene los siguientes argumentos:
Número1;número2 Número1 es obligatorio, los números siguientes son opcionales. De 1 a 255 números de los que desea encontrar el valor máximo.
Ejemplo. a. Para realizar esta acción active la hoja Max del Libro Rangos.xlsx.
Programa Nacional de Informática
133
Microsoft Office Excel 2010
b. Para poder obtener el Mayor presupuesto debemos obtener el máximo presupuesto por región, de esta manera:
c. En donde obtendremos lo siguiente
134
Programa Nacional de Informática
Microsoft Office Excel 2010
MIN(número1;número2;…) Devuelve el valor mínimo de un conjunto de valores. La sintaxis de la función MAX tiene los siguientes argumentos:
Número1;número2 Número1 es obligatorio, los números siguientes son opcionales. De 1 a 255 números de los que desea encontrar el valor máximo.
Ejemplo.
a. Para realizar esta acción active la hoja Min del Libro Rangos.xlsx.
b. Para poder obtener el Menor presupuesto debemos obtener el mínimo presupuesto por región, de esta manera:
Programa Nacional de Informática
135
Microsoft Office Excel 2010
c. En donde obtendremos lo siguiente
CONTAR([valor1],[valor2];..) 136
Programa Nacional de Informática
Microsoft Office Excel 2010
La función CONTAR cuenta la cantidad de celdas que contienen números y cuenta los números dentro de la lista de argumentos. Use la función CONTAR para obtener la cantidad de entradas en un campo de número de un rango o matriz de números. La sintaxis de la función CONTAR tiene los siguientes argumentos:
Valor1 Obligatorio. Primer elemento, referencia de celda o rango en el que desea contar números.
Valor2 Opcional. Hasta 255 elementos, celdas de referencia o rangos adicionales en los que desea contar números.
Ejemplo.
a. Para realizar esta acción active la hoja Contar del Libro Rangos.xlsx.
b. Para poder la cantidad de alumnos evaluados en la 2 Unidad lo realizaremos de esta manera: Programa Nacional de Informática
137
Microsoft Office Excel 2010
c. En donde obtendremos lo siguiente
138
Programa Nacional de Informática
Microsoft Office Excel 2010
CONTARA(valor1],[valor2];..) La función CONTARA cuenta la cantidad de celdas que no están vacías en un rango. La sintaxis de la función CONTARA tiene los siguientes argumentos:
Valor1 Obligatorio.Primer argumento que representa los valores que desea contar.
Valor2 Opcional. Hasta 255 elementos, celdas de referencia o rangos adicionales en los que desea contar números.
Ejemplo. a. Para realizar esta acción active la hoja Contara del Libro Rangos.xlsx.
a. Para poder obtener la cantidad de letras lo realizaremos de esta manera:
Programa Nacional de Informática
139
Microsoft Office Excel 2010
b. En donde obtendremos lo siguiente
140
Programa Nacional de Informática
Microsoft Office Excel 2010
CONTAR.SI(rango;criterio) Cuenta las celdas, dentro del rango, que no están en blanco y que cumplen con el criterio especificado. La sintaxis de la función CONTAR.SI tiene los siguientes argumentos:
Rango
Criterio es el criterio en forma de número, expresión, referencia a celda o texto, que determina las celdas que se van a contar.
es el rango dentro del cual desea contar las celdas.
Ejemplo.
a. Para realizar esta acción active la hoja Contar.si del Libro Rangos.xlsx.
b. Para poder obtener la Cant. Trab. por Sección lo podemos realizar de esta manera:
Programa Nacional de Informática
141
Microsoft Office Excel 2010
c. En donde obtendremos lo siguiente
SUMAR.SI(rango;criterios) La función CONTAR.SI cuenta el número de celdas dentro de un rango que cumplen un solo criterio especificado por el usuario. La sintaxis de la función VALHORA tiene los siguientes argumentos:
142
Rango Obligatorio.Una o más celdas que se van a contar, incluidos números o nombres, matrices o referencias que contengan números. Los valores en blanco y los de texto no se tienen en cuenta.
Criterios Obligatorio.Número, expresión, referencia de celda o cadena de texto que determina las celdas que se van a contar. Programa Nacional de Informática
Microsoft Office Excel 2010
ACTIVIDAD a. Para realizar esta acción active la hoja Sumar.si del Libro Rangos.xlsx.
b. Para poder obtener el Total Aportes por Sección lo podemos realizar de esta manera:
c. En donde obtendremos lo siguiente
Programa Nacional de Informática
143
Microsoft Office Excel 2010
Preguntas de repaso
1. ¿Para qué sirven los operadores en una Fórmula? 2. ¿Por qué es importante la jerarquía de los operadores? 3. ¿Cuál es el objetivo de la auditoria de fórmulas? 4. ¿Qué es la Referencia de celdas Relativas? 5. ¿Cómo se rastrean precedentes? 6. ¿De cuantas maneras se puede copiar celdas? 7. ¿Para qué sirve las referencias a múltiples hojas? 8. ¿Cómo se le asigna nombre a un rango de celdas? 9. ¿Cómo edito la referencia de un nombre de rango de celdas? 10. ¿Para qué sirve la Función Redondear?
144
Programa Nacional de Informática
Microsoft Office Excel 2010
Capítulo
5 CREACIÓN Y MODIFICACIÓN DE FÓRMULAS II En este capítulo se trataran los siguientes temas: Definición de una función Condiciones lógicas y de información Objetivo: Trabajar de manera ordenada con las formulas y usar las funciones dadas por Excel para realizar cálculos de información y lógicos de manera correcta.
Programa Nacional de Informática
145
Microsoft Office Excel 2010
CONCEPTOS FUNDAMENTALES Esta unidad es la unidad una de las más importantes del curso, pues en su comprensión y manejo está la base de Excel. Qué es una hoja de cálculo sino una base de datos que utilizamos con una serie de fórmulas para evitar tener que re calcular por cada cambio que hacemos. Por eso esta unidad es fundamental para el desarrollo del curso y la buena utilización de Excel. Vamos a profundizar en el manejo de funciones ya definidas por Excel 2010 para agilizar la creación de hojas de cálculo, estudiando la sintaxis de éstas así como el uso del asistente para funciones, herramienta muy útil cuando no conocemos muy bien las funciones existentes o la sintaxis de éstas.
DEFINICION DEUNA FUNCION Una función es una fórmula predefinida por Excel que opera sobre uno o más valores (argumentos) en un orden determinado (estructura). El resultado se mostrará en la celda donde se introdujo la formula. El tipo de argumento que utiliza una función es específico de esa función. Así, los argumentos pueden ser números, texto, valores lógicos como VERDADERO o FALSO, matrices, valores de error como #N/A o referencias de celda. Un argumento puede ser una constante, una fórmula o incluso otra función. Excel cuenta con una gran variedad de funciones dependiendo del tipo de operación o cálculo que realizan. Estas funciones pueden ser matemáticas y trigonométricas, estadísticas, financieras, de texto, de fecha y hora, lógicas, de base de datos, de búsqueda y referencia y de información. La sintaxis de cualquier función es: nombre_función(argumento1;argumento2;...;argumentoN). Siguen las siguientes reglas: Si la función va al comienzo de una fórmula debe empezar por el signo =. Los argumentos o valores de entrada van siempre entre paréntesis. No dejes espacios antes o después de cada paréntesis. Los argumentos pueden ser valores constantes (número o texto), fórmulas o funciones. Los argumentos deben de separarse por un punto y coma (;) según corresponda.
El ingreso de fecha debe ser preciso. En una hoja de cálculo escriba así: 146
Programa Nacional de Informática
Microsoft Office Excel 2010
24-Ago 12-08 12/08 12/08/10 Observe que ahora, Excel muestra éstos datos como fecha.
Si usted no escribe el año, las fecha son consideradas con el año actual. - Si usted escribe: 1-8 Excel lo considera como 1 de Agosto de 2010 - Si usted escribe: 20-10 Excel lo considera como 20 de Octubre de 2010 Escribe las fecha propuestas y luego vea el contenido a través de la tecla F2:
FORMATO DE FECHAS Excel aplica un formato automático a las celdas que contienen fechas. El formato aplicado es dd-mm-yyyy según lo ingresado como por ejemplo:
FORMATO PREDEFINIDOS Para cambiar el formato de fechas a una determinada celda o rango de celdas, se tiene que realizar lo siguiente: 1. Seleccione las celdas que contienen las fechas para cambiar el formato.
Programa Nacional de Informática
147
Microsoft Office Excel 2010
2. Seleccionar la ficha Inicio y realizar clic en formato de celdas como se muestra en la figura.
En la pestaña Número, escoja la categoría Fecha
3. Seleccione uno de los formatos preestablecidos.
148
Programa Nacional de Informática
Microsoft Office Excel 2010
4. Para finalizar haga clic en el botón Aceptar
En la siguiente figura se muestran algunos formatos de fecha ya aplicados.
Aplicaciones de las funciones de fecha. Calcular la edad Una de las aplicaciones de las funciones podría ser la de calcular la edad de una persona. En la Hoja6 se pide calcular la edad de los pacientes.
Lo primero que debemos hacer es calcular el número de días de la fecha actual a la fecha de nacimiento. La formula es la siguiente.
Convertimos los días en años.
Programa Nacional de Informática
149
Microsoft Office Excel 2010
Finalmente nos quedamos con la parte entera del resultado y obtendremos al edad.
FORMATO PERSONALIZADOS Lo mismo que con los números, Ms Excel nos permite crear formato personalizados para mostrar las fechas de acuerdo a los criterios que usted necesite. Por ejemplo, mostrar sólo el día de la fecha, quizás incluir el nombre del mes a una fecha o incluso mostrar un texto que acompañe a la fecha: La fecha 23-8
Se puede mostrar cómo: Lunes Se puede mostrar cómo: Agosto Se puede mostrar cómo: 23 de Agosto mi Aniversario
Para realizar estas acciones es necesario conocer los códigos que tiene Ms Excel para mostrar las fechas de acuerdo a nuestro criterio. Códigos a usar: CÓDIGO d
Muestra dígitos para el día
dd
Muestra la abreviatura del día
dddd m mm
Muestra el nombre del día Muestra dígitos para el mes Muestra la abreviatura del mes
mmmm
Muestra el nombre del mes
yy
Muestra dígitos para el año
yyyy
150
UTILIDAD
Muestra 4 dígitos para el año
Programa Nacional de Informática
Microsoft Office Excel 2010
En la siguiente figura veremos la demostración utilizando los códigos mencionados anteriormente. Tabla: ejemplo de formatos personalizados para fechas.
ACTIVIDAD Objetivo Usar la aritmética para obtener habilidades al trabajar con fechas.
Procedimiento Abrir el archivo cálculos fechas y trabajar con la hoja1. 1) Sumarle a la fecha del préstamo la cantidad de días indicada obteniendo la fecha de vencimiento. 2) Encontrar la fecha de cobranza sabiendo que es 10 días antes de la fecha de vencimiento, tal como se muestra en el grafico siguiente.
Programa Nacional de Informática
151
Microsoft Office Excel 2010
FUNCIONES DE FECHA Y HORA De entre todo el conjunto de funciones, en este capítulo estudiaremos las funciones dedicadas al tratamiento de fechas y horas. En varias funciones veremos que el argumento que se le pasa o el valor que nos devuelve es un "número de serie". Pues bien, Excel llama número de serie al número de días transcurridos desde el 1 de enero de 1900 hasta la fecha introducida, es decir coge la fecha inicial del sistema como el día 1/1/1900 y a partir de ahí empieza a contar, en las funciones que tengan núm_de_serie como argumento, podremos poner un número o bien la referencia de una celda que contenga una fecha. Estas son algunas funciones ofrecidas por Ms Excel 2010. AHORA() Esta función nos devuelve la fecha y la hora actual del sistema. Los dos datos se muestran dentro de una misma celda. Cambiando el formato de la misma indicaremos la que deseemos mostrar.
Aquí se muestra el formato personalizado que se aplica para dicha función.
En la siguiente figura se muestra el formato personalizado para que se muestre sólo la fecha.
152
Programa Nacional de Informática
Microsoft Office Excel 2010
AÑO(num_de_serie) Esta función nos devuelve el año de una fecha. A continuación se mostrará el año extraído de una fecha.
DIA(num_de_serie) Esta función nos devuelve el día de una fecha. A continuación se mostrará el día extraído de una fecha.
DIA.LAB(fecha_inicial;días_lab;[festivos]) Devuelve un número que representa una fecha que es el número de días laborables antes o después de una fecha (la fecha inicial). Los días laborables excluyen los días de fin de semana y cualquier fecha identificada en el Programa Nacional de Informática
153
Microsoft Office Excel 2010
argumento festivo. Use DIA.LAB para excluir fines de semana o días festivos cuando calcule fechas de vencimiento de facturas, las fechas de entrega esperadas o el número de días de trabajo realizado. La sintaxis de la función DIA.LAB tiene los siguientes argumentos:
Fecha_inicial
Dias_Lab
Festivos
Obligatorio. Una fecha que representa la fecha inicial.
Obligatorio. El número de días laborables (días que no sean fines de semana ni días festivos) anteriores o posteriores al argumento fecha_inicial. Un valor positivo para el argumento días_lab produce una fecha futura; un número negativo produce una fecha pasada.. Opcional. Una lista opcional de una o varias fechas que deben excluirse del calendario laboral, como los días festivos nacionales y locales. La lista puede ser un rango de celdas que contengan las fechas o una constante matricial de los números de serie que representen las fechas.. matriz utilizada para crear fórmulas sencillas que producen varios resultados o que funcionan en un grupo de argumentos que se organizan en filas y columnas. Un rango dematriz comparte una fórmula común; una constante de matriz es un grupo de constantes utilizadas como un argumento..
Ejemplo:
DIA.LAB.INTL(fecha_inicial;días_lab;[fin de semana];[festivos]) Devuelve el número de serie de la fecha que tiene lugar antes o después de un número determinado de días laborables con parámetros de fin de semana personalizados. Los parámetros de fin de semana indican cuáles y cuántos días son días de fin de semana. Los días de fin de semana y cualquier día especificado como festivo no se consideran días laborables. 154
Programa Nacional de Informática
Microsoft Office Excel 2010
La sintaxis de la función DIA.LAB.INTL tiene los siguientes argumentos:
Fecha_inicial
Dias_Lab
Fin_de_semana
Obligatorio. La fecha inicial, truncada a entero.
Obligatorio. El número de días laborables antes o después de la fecha_inicial. Un valor positivo da como resultado una fecha futura; un valor negativo proporciona una fecha pasada; un valor de cero proporciona la fecha_inicial. El desplazamiento de días se trunca a entero. Opcional. Indica los días de la semana que corresponden a días de la semana y no se consideran días laborables. Fin_de_semana es un número de fin de semana o cadena que especifica cuándo ocurren los fines de semana. Los valores numéricos de fin de semana indican los siguientes días de fin de semana:
# DE FIN DE SEMANA DÍAS DE FIN DE SEMANA 1 u omitido 2 3 4 5 6 7 11 12 13 14 15 16 17
Sábado, Domingo Domingo, Lunes Lunes, Martes Martes, Miércoles Miércoles, Jueves Jueves, Viernes Viernes, Sábado Solo domingo Solo sábado Solo martes Solo miércoles Solo jueves Solo viernes Solo sábado
Los valores de las cadenas de fin de semana tienen siete caracteres de longitud y cada carácter de la cadena representa un día de la semana, comenzando por el lunes. 1 representa un día no laborable y 0 representa un día laborable. Sólo se permiten los caracteres 1 y 0 en la cadena. 1111111 no es una cadena válida. Por ejemplo, 0000011 daría como resultado un fin de semana que es sábado y domingo.
Festivos Opcional. Un conjunto opcional de una o más fechas que se deben excluir del calendario de días laborables. Los días festivos deben estar
Programa Nacional de Informática
155
Microsoft Office Excel 2010 en el rango de celdas que contienen las fechas o una constante de matriz de los valores seriales que representan esas fechas. El orden de las fechas o los valores seriales de los días festivos puede ser arbitrario.
Ejemplo:
DIAS.LAB(fecha_inicial;fecha_final;[festivos]) Devuelve el número de días laborables entre fecha_inicial y fecha_final. Los días laborables no incluyen los fines de semana ni otras fechas que se identifiquen en el argumento festivos. Utilice DIAS.LAB para calcular el incremento de los beneficios acumulados de los empleados basándose en el número de días trabajados durante un período específico. La sintaxis de la función DIAS.LAB tiene los siguientes argumentos:
Fecha_inicial
Obligatorio. Una fecha que representa la fecha inicial.
Fecha_final
Obligatorio. Una fecha que representa la fecha final.
Festivos
Opcional. Un rango opcional de una o varias fechas que deben excluirse del calendario laboral, como los días festivos nacionales y locales. La lista puede ser un rango de celdas que contengan las fechas o una constante matricial de los números de serie que representen las fechas.
Ejemplo:
156
matriz utilizada para crear fórmulas sencillas que producen varios resultados o que funcionan en un grupo de argumentos que se organizan en filas y columnas. Un rango dematriz comparte una fórmula común; una constante de matriz es un grupo de constantes utilizadas como un argumento.
Programa Nacional de Informática
Microsoft Office Excel 2010
DIAS.LAB.INTL(fecha_inicial;fecha_final;[fin de semana];[festivos]) Devuelve el número de todos los días laborables entre dos fechas mediante parámetros para indicar cuáles y cuántos son días de fin de semana. Los días de fin de semana y los días que se especifiquen como días festivos no se consideran días laborables. La sintaxis de la función DIAS.LAB.INTL tiene los siguientes argumentos:
Fecha_inicial y Fecha_final
Fin_de_semana
Obligatorio. Las fechas para las que se desea calcular la diferencia. La fecha_inicial puede ser anterior, la misma o posterior a la fecha_final. Opcional. Indica los días de la semana que son días de fin de semana y no están incluidos en el número de todos los días laborables entre la fecha_inicial y la fecha_final. Fin_de_semana es un número o una cadena de fin de semana que especifica cuándo ocurren los fines de semana.. Los valores numéricos de fin de semana indican los siguientes días de fin de semana:
# DE FIN DE SEMANA DÍAS DE FIN DE SEMANA 1 u omitido 2 3 4 5 6 7 11 12
Programa Nacional de Informática
Sábado, Domingo Domingo, Lunes Lunes, Martes Martes, Miércoles Miércoles, Jueves Jueves, Viernes Viernes, Sábado Solo domingo Solo sábado
157
Microsoft Office Excel 2010 13 14 15 16 17
Solo martes Solo miércoles Solo jueves Solo viernes Solo sábado
Los valores de cadena de fin de semana tienen siete caracteres de largo y cada carácter de la cadena representa un día de la semana, comenzando por el lunes.1 representa un día no laborable y 0 representa un día laborable. Solo los caracteres 1 y 0 están permitidos en la cadena. Si se usa 1111111 siempre devolverá 0. Por ejemplo, 0000011 resultará en un día de fin de semana que es sábado o domingo.
Festivos
Opcional. Un conjunto opcional de una o más fechas que se deben excluir del calendario de días laborables. Los días festivos deben estar en un rango de celdas que contienen las fechas o una constante de matriz de los valores seriales que representan esas fechas. El orden de las fechas o los valores seriales de los días festivos puede ser arbitrario.
Ejemplo:
DIAS360(fecha_inicial;fecha_final;[método]) La función DIAS360 devuelve la cantidad de días entre dos fechas basándose en un año de 360 días (12 meses de 30 días) que se usa en algunos cálculos contables. Use esta función para facilitar el cálculo de pagos si su sistema de contabilidad se basa en 12 meses de 30 días. La sintaxis de la función DIAS360 tiene los siguientes argumentos:
158
Fecha_inicial, Fecha_final
Obligatorio. Fechas entre las que desea calcular la cantidad de días. Si fecha_inicial se produce después defecha_final, la función DIAS360 devuelve un número negativo. Las fechas se deben especificar con la función FECHA o se deben derivar de los
Programa Nacional de Informática
Microsoft Office Excel 2010 resultados de otras fórmulas o funciones. Por ejemplo, useFECHA(2008;5;23) para devolver el 23 de mayo de 2008. Si las fechas se especifican como texto, pueden surgir problemas.
Método
Opcional. Valor lógico que especifica si se usará el método de cálculo europeo o americano.
MÉTODO
MODO DE CÁLCULO
FALSO u omitido
Método de EE.UU. (NASD). Si la fecha inicial es el último día del mes, se convierte en el día 30 del mismo mes. Si la fecha final es el último día del mes y la fecha inicial es anterior al día 30, la fecha final se convierte en el día 1 del mes siguiente; de lo contrario la fecha final se convierte en el día 30 del mismo mes. Método europeo. Las fechas iniciales o finales que corresponden al día 31 del mes se convierten en el día 30 del mismo mes.
VERDADERO
Ejemplo:
DIASEM(num_de_serie;[tipo]) Devuelve el día de la semana correspondiente al argumento núm_de_serie. El día se devuelve como un número entero entre 1 (domingo) y 7 (sábado). La sintaxis de la función DIASEM tiene los siguientes argumentos:
Num_de_serie
Obligatorio. Un número secuencial que representa la fecha del día que intenta buscar. Las fechas deben especificarse mediante la
Programa Nacional de Informática
159
Microsoft Office Excel 2010 función FECHA o como resultado de otras fórmulas o funciones. Por ejemplo, use FECHA(2008;5;23) para el día 23 de mayo de 2008. Pueden producirse problemas si las fechas se escriben como texto.
Tipo Opcional. Un número que determina el tipo de valor que debe devolverse.
TIPO
NÚMERO DEVUELTO
1 u omitido
Números del 1 (domingo) al 7 (sábado). Igual a en versiones anteriores de Microsoft Excel. Números del 1 (lunes) al 7 (domingo). Números del 0 (lunes) al 6 (domingo). Números del 1 (lunes) al 7 (domingo). Números 1 (martes) a 7 (lunes). Números 1 (miércoles) a 7 (martes). Números 1 (jueves) a 7 (miércoles). Números 1 (viernes) a 7 (jueves). Números 1 (sábado) a 7 (viernes). Números del 1 (domingo) al 7 (sábado).
2 3 11 12 13 14 15 16 17
Ejemplo:
FECHA(año;mes;dia) Devuelve el número de serie secuencial que representa una fecha determinada. Si el formato de celda era General antes de escribir la función, el resultado tendrá formato de fecha. La sintaxis de la función FECHA tiene los siguientes argumentos:
Año Obligatorio. El valor del argumento año puede incluir de uno a cuatro dígitos. Excel interpreta el argumento año según el sistema de fechas que usa el equipo. De manera predeterminada, Microsoft Excel para Windows usa el sistema de fechas 1900 y Microsoft Excel para Macintosh usa el sistema de fechas 1904.
160
Programa Nacional de Informática
Microsoft Office Excel 2010
Si el año es un número entre 0 (cero) y 1899 (inclusive), Excel suma ese valor a 1900 para calcular el año. Por ejemplo, FECHA(108;1;2)devuelve 2 de enero de 2008 (1900+108). Si el año es un número entre 1900 y 9999 (inclusive), Excel usa ese valor como el año. Por ejemplo, FECHA(2008;1;2) devuelve 2 de enero de 2008. Si el año es menor que 0, o es igual o mayor que 10.000, Excel devuelve el valor de error #¡NUM!
Mes Obligatorio. Número entero positivo o negativo que representa el mes del año, de 1 a 12 (de enero a diciembre). Si el mes es mayor que 12, mes suma esa cantidad de meses al primer mes del año especificado. Por ejemplo, FECHA(2008;14;2)devuelve el número de serie que representa el 2 de febrero de 2009. Si el mes es menor que 1, mes resta la magnitud de esa cantidad de meses, más 1, del primer mes del año especificado. Por ejemplo,FECHA(2008;-3;2) devuelve el número de serie que representa el 2 de septiembre de 2007.
Día
Obligatorio. Número entero positivo o negativo que representa el día del mes, de 1 a 31. Si el día es mayor que la cantidad de días del mes especificado, díasuma esa cantidad a los días del primer día del mes. Por ejemplo,FECHA(2008;1;35) devuelve el número de serie que representa el 4 de febrero de 2008. Si el día es menor que 1, día resta la magnitud de la cantidad de días, más uno, del primer día del mes especificado. Por ejemplo, FECHA(2008;1;-15) devuelve el número de serie que representa el 16 de diciembre de 2007.
Ejemplo:
Programa Nacional de Informática
161
Microsoft Office Excel 2010
FECHA.MES(fecha_inicial;meses) Devuelve el número de serie que representa la fecha que indica el número de meses anteriores o posteriores a la fecha especificada (argumento fecha_inicial). Use FECHA.MES para calcular las fechas de vencimiento que caen en el mismo día del mes que el día de emisión. La sintaxis de la función FECHA.MES tiene los siguientes argumentos:
Fecha_inicial
Mes Obligatorio. El número de meses anteriores o posteriores al argumento
Obligatorio. Una fecha que representa la fecha inicial. Las fechas deben especificarse mediante la función FECHA o como resultado de otras fórmulas o funciones. Por ejemplo, utilice FECHA(2008;5;23) para el día 23 de mayo de 2008. Pueden producirse problemas si las fechas se escriben como texto.
fecha_inicial. Cuando meses es un valor positivo, el resultado es una fecha futura; cuando es un valor negativo, el resultado es una fecha pasada.
Ejemplo:
FIN.MES(fecha_inicial;meses) Devuelve el número de serie del último día del mes, anterior o posterior a la fecha_inicial del número de mes indicado. Use FIN.MES para calcular las fechas de vencimiento que caen en el último día del mes. La sintaxis de la función FIN.MES tiene los siguientes argumentos:
162
Fecha_inicial
Obligatorio Una fecha que representa la fecha inicial. Las fechas deben especificarse utilizando la función FECHA o como resultado de otras fórmulas o funciones. Por ejemplo, utilice FECHA(2008;5;23) para el 23 de mayo de 2008. Pueden producirse problemas si las fechas se escriben como texto.
Programa Nacional de Informática
Microsoft Office Excel 2010
Meses
Obligatorio. El número de meses anteriores o posteriores al argumento fecha_inicial. Cuando meses es un valor positivo, el resultado es una fecha futura; cuando es un valor negativo, el resultado es una fecha pasada.
Ejemplo:
FRAC_AÑO(fecha_inicial;fecha_final;[base]) Calcula la fracción de año que representa el número de días enteros entre la fecha_inicial y fecha_final. Utilice FRAC.AÑO para determinar la proporción de los beneficios u obligaciones de todo un año que corresponde a un período específico. La sintaxis de la función FRAC_AÑO tiene los siguientes argumentos:
Fecha_inicial
Fecha_final Obligatorio. Una fecha que representa la fecha final.
Base Opcional. Determina en qué tipo de base deben contarse los días.
Obligatorio. Una fecha que representa la fecha inicial.
BASE
BASE PARA CONTAR DÍAS
0 u omitido 1 2 3 4
EE.UU. (NASD) 30/360 Real/real Real/360 Real/365 Europea 30/360
Ejemplo:
Programa Nacional de Informática
163
Microsoft Office Excel 2010
HORA(num_de_serie) Devuelve la hora de un valor de hora. La hora se expresa como número entero, comprendido entre 0 (12:00 a.m.) y 23 (11:00 p.m.). La sintaxis de la función HORA tiene los siguientes argumentos:
Num_de_serie
Obligatorio El valor de hora que contiene la hora que se desea obtener. Los valores de horas pueden escribirse como cadenas de texto entre comillas (por ejemplo, "6:45 p.m.", como números decimales (por ejemplo, 0,78125, que representa las 6:45 p.m.), o bien como resultado de otras fórmulas o funciones, por ejemplo VALHORA("6:45 p.m.")).
Ejemplo:
HOY() Devuelve el número de serie de la fecha actual. El número de serie es el código de fecha-hora que Excel usa para los cálculos de fecha y hora. Si el formato de celda era General antes de especificar la función, Excel cambiará el formato de
164
Programa Nacional de Informática
Microsoft Office Excel 2010
celda a Fecha. Si desea ver el número de serie, debe cambiar el formato de celda a General o Número. La función HOY es útil cuando necesita que se muestre la fecha actual en una hoja de cálculo, independientemente de cuándo se abre el libro. Además es útil para calcular los intervalos. Por ejemplo, si sabe que alguien nació en 1963, puede usar la siguiente fórmula para buscar la edad de esa persona a partir de este año de nacimiento: =AÑO(HOY())-1963 Esta fórmula usa la función HOY como argumento para la función AÑO para obtener la fecha actual y, a continuación, resta 1963 y devuelve la edad de la persona. La sintaxis de la función HOY no tiene argumentos. Ejemplo:
Programa Nacional de Informática
165
Microsoft Office Excel 2010
MES(num_de_serie) Devuelve el mes de una fecha representada por un número de serie. El mes se expresa como número entero comprendido entre 1 (enero) y 12 (diciembre). La sintaxis de la función MES tiene los siguientes argumentos:
Num_de_serie Obligatorio. La fecha del mes que intenta buscar. Las fechas deben insertarse mediante la función FECHA o como resultado de otras fórmulas o funciones. Por ejemplo, use FECHA(2008;5;23) para el día 23 de mayo de 2008. Pueden producirse problemas si las fechas se escriben como texto.
Ejemplo:
MINUTO(num_de_serie) Devuelve los minutos de un valor de hora. Los minutos se expresan como números enteros comprendidos entre 0 y 59. La sintaxis de la función MINUTO tiene los siguientes argumentos:
Num_de_serie
Obligatorio. La hora que contiene el valor de minutos que se desea buscar. Las horas pueden escribirse como cadenas de texto entre comillas (por ejemplo, "6:45 p.m."), como números decimales (por ejemplo, 0,78125, que representa las 6:45 p.m.), o bien como resultado de otras fórmulas o funciones, por ejemplo VALHORA("6:45 p.m.").
Ejemplo:
166
Programa Nacional de Informática
Microsoft Office Excel 2010
NUM.DE.SEMANA(num_de_serie;[tipo_de_devolución*]) Devuelve el número de la semana correspondiente a una fecha determinada. Por ejemplo, la semana que contiene el 1 de enero es la primera semana del año y se numera como semana 1. Existen dos sistemas que se usan para esta función.
Sistema 1 La semana que contiene el 1 de enero es la primera semana del año y se numera como semana 1. Sistema 2 La semana que contiene el primer jueves del año es la primera semana del año y se numera como semana 1.
La sintaxis de la función NUM.DE.SEMANA tiene los siguientes argumentos:
Num_de_serie
Tipo Opcional. Un número que determina en qué día comienza la semana. El valor predeterminado es 1.
Obligatorio. Una fecha contenida en la semana. Las fechas deben especificarse usando la función FECHA o como resultado de otras fórmulas o funciones. Por ejemplo, use FECHA(2008;5;23) para el 23 de mayo de 2008. Pueden producirse problemas si las fechas se escriben como texto.
TIPO
LA SEMANA EMPIEZA EL
1 u omitido 2 11 12 13 14 15 16 17 21
Domingo Lunes Lunes Martes Miércoles Jueves Viernes Sábado Domingo Lunes
SISTEMA 1 1 1 1 1 1 1 1 1 2
Ejemplo:
Programa Nacional de Informática
167
Microsoft Office Excel 2010
SEGUNDO(num_de_serie) Devuelve los segundos de un valor de hora. El segundo se expresa como número entero comprendido entre 0 (cero) y 59.
La sintaxis de la función MINUTO tiene los siguientes argumentos:
Num_de_serie
Obligatorio. La hora que contiene los segundos que se desea buscar. Las horas pueden escribirse como cadenas de texto entre comillas (por ejemplo, "6:45 p.m."), como números decimales (por ejemplo, 0,78125, que representa las 6:45 p.m.), o bien como resultado de otras fórmulas o funciones, por ejemplo VALHORA("6:45 p.m.").
Ejemplo:
TIEMPO (hora;minuto;segundo) Devuelve el número decimal de una hora determinada. Si el formato de celda eraGeneral antes de escribir la función, el resultado tendrá formato de fecha. El número decimal que TIEMPO devuelve es un valor comprendido entre 0 (cero) y 0, 99999999 que representa las horas entre 0:00:00 (00:00:00 a.m.) y 23:59:59 (11:59:59 p.m.).
La sintaxis de la función TIEMPO tiene los siguientes argumentos:
Hora Obligatorio. Un número entre 0 (cero) y 32767 que representa las horas. Todo valor mayor de 23 se dividirá por 24 y el resto se considerará como el valor horario. Por ejemplo, TIEMPO(27;0;0) = TIEMPO(3;0;0) = 0,125 ó 3:00 a.m.
Minuto
Segundo
Obligatorio. Un número entre 0 y 32767 que representa los minutos. Todo valor mayor de 59 se convertirá a horas y minutos. Por ejemplo, TIEMPO(0;750;0) = TIEMPO(12;30;0) = 0,520833 ó 12:30 p.m. Obligatorio. Un número entre 0 y 32767 que representa los segundos. Todo valor mayor de 59 se convertirá en horas, minutos y segundos. Por ejemplo, TIEMPO(0;0;2000) = TIEMPO(0;33;22) = 0,023148 ó 12:33:20 a.m.
Ejemplo: 168
Programa Nacional de Informática
Microsoft Office Excel 2010
VALFECHA(texto_de_fecha) La función VALFECHA convierte una fecha almacenada como texto en un número de serie que Excel reconoce como fecha. Por ejemplo, la fórmula=VALFECHA("1/1/2008") devuelve 39448, el número de serie de la fecha 1/1/2008. La sintaxis de la función VALFECHA tiene los siguientes argumentos:
Texto_de_fecha
Obligatorio. Texto que representa una fecha en el formato de fechas de Excel o una referencia a una celda que contiene texto que representa una fecha en un formato de fechas de Excel. Por ejemplo, "30/1/2008" o "30-Ene-2008" son cadenas de texto entre comillas que representan fechas.
Con el sistema de fechas predeterminado de Microsoft Excel para Windows, el argumento texto_de_fecha debe representar una fecha entre 1 de enero de 1900 y 31 de diciembre de 9999. Con el sistema de fechas predeterminado de Excel para Macintosh, el argumento texto_de_fecha debe representar una fecha entre el 1 de enero de 1904 y el 31 de diciembre de 9999. La función VALFECHAdevuelve el valor de error #¡VALOR! si el valor del argumento texto_de_fecha se encuentra fuera de este rango. Si se omite la parte del año del argumento texto_de_fecha, la función VALFECHAusa el año actual del reloj integrado del equipo. Se omite la información de hora en el argumento texto_de_fecha.
Programa Nacional de Informática
169
Microsoft Office Excel 2010
Ejemplo:
VALHORA(texto_de_hora) Devuelve el número decimal de la hora representada por una cadena de texto. El número decimal es un valor comprendido entre 0 (cero) y 0, 99999999 que representa las horas entre 0:00:00 (12:00:00 a.m.) y 23:59:59 (11:59:59 p.m.). La sintaxis de la función VALHORA tiene los siguientes argumentos:
Texto_de_hora
Obligatorio. Una cadena de texto que representa una hora en uno de los formatos de hora de Microsoft Excel, por ejemplo, las cadenas de texto entre comillas "6:45 p.m." y "18:45" representan la hora.
Ejemplo:
Funciones de texto Una hoja de cálculo está pensada para manejarse dentro del mundo de los números, pero Excel también tiene un conjunto de funciones específicas para la manipulación de texto. Estas son todas las funciones de texto ofrecidas por Excel. 170
Programa Nacional de Informática
Microsoft Office Excel 2010
Función
Descripción
CAR(número)
Devuelve el carácter especificado por el número Devuelve el código ASCII del primer carácter del texto Devuelve una cadena de caracteres con la unión Devuelve el número de caracteres especificados Encuentra una cadena dentro de un texto Devuelve un valor lógico (verdadero/fals o) Devuelve el número de caracteres especificados Devuelve la longitud del texto Limpia el texto de caracteres no imprimibles Convierte a mayúsculas Convierte a minúsculas Convierte a moneda Convierte a mayúscula la primera letra del texto Reemplaza parte de una cadena de texto por otra Repite el texto Reemplaza el texto con texto nuevo Comprueba que el valor es texto Convierte un
CODIGO(texto)
CONCATENAR(texto1;texto2;...;textoN)
DERECHA(texto;núm_de_caracteres)
HALLAR(texto_buscado;dentro_del_texto;núm_inicial)
IGUAL(texto1;texto2)
IZQUIERDA(texto;núm_de_caracteres)
LARGO(texto)
LIMPIAR(texto)
MAYUSC(texto) MINUSC(texto) MONEDA(número;núm_de_decimales) NOMPROPIO(texto)
REEMPLAZAR(texto_original;num_inicial;núm_de_caracteres;texto_nu evo)
REPETIR(texto;núm_de_veces) SUSTITUIR(texto;texto_original;texto_nuevo;núm_de_ocurrencia)
T(valor) TEXTO(valor;formato)
Programa Nacional de Informática
171
Microsoft Office Excel 2010 valor a texto Convierte un número a texto tailandés (Baht) Convierte un texto a número
TEXTOBAHT(número)
VALOR(texto)
A continuación de detallaran algunas de las funciones más usadas. Función CAR(número) Devuelve el carácter especificado por el número de código correspondiente al código de caracteres ASCII. Ejemplo: =CAR(77) devuelve M Función CODIGO(texto) Esta función devuelve el código ASCII del primer carácter del texto pasado como parámetro. Ejemplo: =CODIGO("M") devuelve 77 Función CONCATENAR(texto1;texto2;...;textoN) Devuelve una cadena de caracteres con la unión de los textos pasados como parámetros. Esta función es de utilidad cuando tenemos texto como puedan ser el nombre completo de una persona pero está dividido en distintas celdas y en alguna ocasión queremos fundir el nombre completo en una sola celda. Ejemplo: =CONCATENAR("Maicolm ";"Rivera ";"Zamudio") devuelve Maicolm Rivera Zamudio Función DERECHA(texto;núm_de_caracteres) Devuelve de la cadena de texto, el número de caracteres especificados comenzando a contar desde el final del texto. Ejemplo: =DERECHA("Programa "Informática"
Nacional
de
Informática";11)devuelve
Función ENCONTRAR(texto_buscado;dentro_del_texto;núm_inicial) Devuelve la posición inicial del texto buscado dentro de otro texto empezando a buscar desde la posición núm_inicial. A diferencia de la función HALLAR, ENCONTRAR distingue entre mayúsculas y minúsculas y no admite caracteres comodín.
172
Programa Nacional de Informática
Microsoft Office Excel 2010
Ejemplo: =ENCONTRAR("Nac";"Programa Nacional de Informática";1)devuelve 10, que es la posición donde empieza la palabra Nac de Nacional. Las funciones HALLAR y HALLARB buscan una cadena de texto dentro de una segunda cadena de texto y devuelven el número de la posición inicial de la primera cadena de texto desde el primer carácter de la segunda cadena de texto. Por ejemplo, para buscar la ubicación de la letra "p" en la palabra "impresora", puede usar la siguiente función: =HALLAR("a","senati") Esta función devuelve 4 porque "a" es el tercer carácter en la palabra "senati" Además, puede buscar por palabras dentro de otras palabras. NOTA: =HALLAR("medio";"promedio") devuelve 4, porque la palabra "medio" comienza en el cuarto carácter de la palabra "promedio". Puede usar las funciones HALLAR y HALLARB para determinar la ubicación de un carácter o cadena de texto dentro de otra cadena de texto y, a continuación, usar las funciones MED y EXTRAEB para volver al texto o usar las funciones REEMPLAZAR y REEMPLAZARB para cambiar el texto.
CONDICIONALES, LÓGICAS Y DE INFORMACIÓN FUNCIONES LÓGICAS
SI Devuelve un valor si la condición especificada es VERDADERO y otro valor si dicho argumento es FALSO. Utilice SI para realizar pruebas condicionales en valores y fórmulas. Sintaxis SI(prueba_lógica;valor_si_verdadero;valor_si_falso) Prueba_lógica es cualquier valor o expresión que pueda evaluarse como VERDADERO o FALSO. Por ejemplo, A10=100 es una expresión lógica; si el valor de la celda A10 es igual a 100, la expresión se evalúa como Programa Nacional de Informática
173
Microsoft Office Excel 2010
VERDADERO. De lo contrario, la expresión se evalúa como FALSO. Este argumento puede utilizar cualquier operador de comparación. Valor_si_verdadero es el valor que se devuelve si el argumento prueba_lógica es VERDADERO. Por ejemplo, si este argumento es la cadena de texto "Dentro de presupuesto" y el argumento prueba_lógica se evalúa como VERDADERO, la función SI muestra el texto "Dentro de presupuesto". Si el argumento prueba_lógica es VERDADERO y el argumento valor_si_verdadero está en blanco, este argumento devuelve 0 (cero). Para mostrar la palabra VERDADERO, utilice el valor lógico VERDADERO para este argumento. Valor_si_verdadero puede ser otra fórmula. Valor_si_falso es el valor que se devuelve si el argumento prueba_lógica es FALSO. Por ejemplo, si este argumento es la cadena de texto "Presupuesto excedido" y el argumento prueba_lógica se evalúa como FALSO, la función SI muestra el texto "Presupuesto excedido". Si el argumento prueba_lógica es FALSO y se omite valor_si_falso, (es decir, después de valor_si_verdadero no hay ninguna coma), se devuelve el valor lógico FALSO. Si prueba_lógica es FALSO y valor_si_falso está en blanco (es decir, después de valor_si_verdadero hay una coma seguida por el paréntesis de cierre), se devuelve el valor 0 (cero). Valor_si_falso puede ser otra fórmula. Observaciones Es posible anidar hasta siete funciones SI como argumentos valor_si_verdadero y valor_si_falso para construir pruebas más elaboradas. Vea el último de los ejemplos siguientes. Cuando los argumentos valor_si_verdadero y valor_si_falso se evalúan, la función SI devuelve el valor devuelto por la ejecución de las instrucciones. Si uno de los argumentos de la función SI es una matriz (matriz: utilizada para crear fórmulas sencillas que producen varios resultados o que funcionan en un grupo de argumentos que se organizan en filas y columnas. Un rango de matriz comparte una fórmula común; una constante de matriz es un grupo de constantes utilizadas como un argumento.), cada elemento de la matriz se evaluará cuando se ejecute la instrucción SI. Microsoft Excel proporciona funciones adicionales que pueden utilizarse para analizar los datos basándose en una condición. Por ejemplo, para contar el número de veces que aparece una cadena de texto o un número dentro de un rango de celdas, utilice la función de hoja de cálculo CONTAR.SI. Para calcular una suma basándose en una cadena de texto o un número dentro de un rango, utilice la función SUMAR.SI. 174
Programa Nacional de Informática
Microsoft Office Excel 2010
Ejemplos: Fórmula =SI(A2<=100;"Dentro de presupuesto";"Presupuesto excedido")
Descripción (Resultado) Si el número anterior es menor o igual que 100, la fórmula muestra "Dentro de presupuesto". De lo contrario, la función mostrará "Presupuesto excedido" (Dentro de presupuesto) =SI(A2=100;SUMA(B5:B15);"") Si el número anterior es 100, se calcula el rango B5:B15. En caso contrario, se devuelve texto vacío ("") () Gastos reales Gastos previstos 1500 900 500 900 500 925 Fórmula Descripción (Resultado) =SI(A2>B2;"Presupuesto Comprueba si la primera fila sobrepasa el presupuesto excedido";"Aceptar") (Presupuesto excedido) =SI(A3>B3;"Presupuesto Comprueba si la segunda fila sobrepasa el excedido";"Aceptar") presupuesto (Aceptar) Puntuación 45 90 78 Fórmula Descripción (Resultado) =SI(A2>89,"A",SI(A2>79;"B";SI(A2>69;"C";SI(A2>59;"D";"F")))) Asigna una puntuación de una letra al primer resultado (F) =SI(A3>89;"A";SI(A3>79;"B";SI(A3>69;"C";SI(A3>59;"D";"F")))) Asigna una puntuación de una letra al segundo resultado (A) =SI(A4>89;"A";SI(A4>79;"B";SI(A4>69;"C";SI(A4>59;"D";"F")))) Asigna una puntuación de una letra al tercer resultado (C)
En el ejemplo anterior, la segunda instrucción SI representa también el argumento valor_si_falso de la primera instrucción SI. De manera similar, la tercera instrucción SI es el argumento valor_si_falso de la segunda instrucción SI. Por ejemplo, si el primer argumento prueba_lógica (Promedio>89) es VERDADERO, se devuelve el valor "A". Si el primer argumento prueba_lógica es FALSO, se evalúa la segunda instrucción SI y así sucesivamente. Las letras de puntuación se asignan a números utilizando la siguiente clave. Si la puntuación es Mayor que 89 De 80 a 89
Programa Nacional de Informática
La función devuelve A B
175
Microsoft Office Excel 2010 De 70 a 79 De 60 a 69 Menor que 60
C D F
Y
Devuelve VERDADERO si todos los argumentos son VERDADERO; devuelve FALSO si uno o más argumentos son FALSO. Sintaxis Y(valor_lógico1;valor_lógico2; ...) Valor_lógico1, Valor_lógico2, ... son entre 1 y 30 condiciones que se desea comprobar y que pueden ser VERDADERO o FALSO. Observaciones
Los argumentos deben evaluarse como valores lógicos, como VERDADERO o FALSO, o los argumentos deben ser matrices o referencias que contengan valores lógicos. Si un argumento matricial o de referencia contiene texto o celdas vacías, dichos valores se pasarán por alto. Si el rango especificado no contiene valores lógicos, la función Y devuelve el valor de error #¡VALOR! Ejemplos:
Fórmula Descripción (Resultado) =Y(VERDADERO;VERDADERO) Todos los argumentos son VERDADERO (VERDADERO) =Y(VERDADERO;FALSO) Un argumento es FALSO (FALSO) =Y(2+2=4;2+3=5) Todos los argumentos se evalúan como VERDADERO (VERDADERO) Datos 50 104 Fórmula Descripción (Resultado) =Y(1
O
176
Programa Nacional de Informática
Microsoft Office Excel 2010
Devolverá VERDADERO si alguno de los argumentos es VERDADERO; devolverá FALSO si todos los argumentos son FALSO. Sintaxis O(valor_lógico1;valor_lógico2; ...) Valor_lógico1;valor_lógico2,... son entre 1 y 30 condiciones que desea comprobar y que pueden ser VERDADERO o FALSO. Observaciones
Los argumentos deben evaluarse como valores lógicos, como VERDADERO O FALSO, o en matrices o referencias que contengan valores lógicos. Si un argumento matricial o de referencia contiene texto o celdas vacías, dichos valores se pasarán por alto. Si el rango especificado no contiene valores lógicos, O devolverá el valor de error #¡VALOR! Puede utilizar la fórmula matricial O para comprobar si un valor aparece en una matriz. Para introducir una fórmula matricial, presione CTRL+MAYÚS+ENTRAR.
Fórmula =O(VERDADERO) =O(1+1=1;2+2=5) =O(VERDADERO;FALSO;VERDADERO)
Descripción (Resultado) Un argumento es VERDADERO (VERDADERO) Todos los argumentos se evalúan como FALSO (FALSO). Al menos un argumento es VERDADERO (VERDADERO).
Anidamiento de funciones condicionales En algunos casos, puede que tenga que utilizar una función como uno de los argumentos de otra función. Resultados válidos Cuando se utiliza una función anidada como argumento, deberá devolver el mismo tipo de valor que el que utilice el argumento. Por ejemplo, si el argumento devuelve un valor VERDADERO o FALSO, la función anidada deberá devolver VERDADERO o FALSO. Si éste no es el caso, Microsoft Excel mostrará el valor de error #¡VALOR! Límites del nivel de anidamiento Una fórmula puede contener como máximo siete niveles de funciones anidadas. Si la Función B se utiliza como argumento de la Función A, la Función B es una función de segundo nivel. Por ejemplo, la función PROMEDIO y la función SUMA son ambas funciones de segundo nivel porque son argumentos de la función SI. Una función anidada dentro de la función PROMEDIO será una función de tercer nivel, etc. Funciones de información Programa Nacional de Informática
177
Microsoft Office Excel 2010
Estas son algunas de las funciones de información más usadas. Función
Descripción
Devuelve información acerca del formato, la ubicación o el contenido de una celda TIPO.DE.ERROR Devuelve un número que corresponde a un tipo de error Devuelve información acerca del entorno operativo en uso INFO Devuelve VERDADERO si el valor está en blanco ESBLANCO Devuelve VERDADERO si el valor es cualquier valor de ESERR error excepto #N/A Devuelve VERDADERO si el valor es cualquier valor de ESERROR error Devuelve VERDADERO si el número es par ES.PAR Devuelve VERDADERO si el valor es un valor lógico ESLOGICO Devuelve VERDADERO si el valor es el valor de error #N/A ESNOD Devuelve VERDADERO si el valor no es texto ESNOTEXTO Devuelve VERDADERO si el valor es un número ESNUMERO Devuelve VERDADERO si el número es impar ES.IMPAR Devuelve VERDADERO si el valor es una referencia ESREF Devuelve VERDADERO si el valor es texto ESTEXTO Devuelve un valor convertido en un número N Devuelve el valor de error #N/A ND Devuelve un número que indica el tipo de datos de un valor TIPO CELDA
CELDA(tipo_de_info;ref) Devuelve información acerca del formato, ubicación o contenido de la celda del extremo superior izquierdo de una referencia. La sintaxis de la función CELDA tiene los siguientes argumentos: Tipo_de_info es un valor de texto que especifica el tipo de información que se desea obtener acerca de la celda. La siguiente lista muestra los posibles valores de tipo_de_info y los correspondientes resultados: SI TIPO_DE_INFO ES
178
DEVUELVE
"DIRECCION"
la referencia, en forma de texto, de la primera celda del argumento ref.
"COLUMNA"
El número de columna de la celda del argumento ref.
"COLOR"
1 si la celda tiene formato de color para los valores negativos; en caso contrario devuelve 0.
"CONTENIDO"
Valor de la celda superior izquierda de la referencia, no una fórmula.
"ARCHIVO"
El nombre del archivo (incluyendo el de la ruta de acceso) que contiene referencia, con formato de texto. Devuelve texto vacío ("") si la hoja de cálculo que contiene el argumento ref aún se ha guardado.
Programa Nacional de Informática
Microsoft Office Excel 2010
"FORMATO"
Un valor de texto correspondiente al formato numérico de la celda. Los valores de texto para los distintos formatos se muestran en la tabla a continuación. Si la celda tiene formato de color para los números negativos, devuelve "-" al final del valor de texto. Si la celda está definida para mostrar todos los valores o los valores positivos entre paréntesis, devuelve "()" al final del valor de texto.
"PARENTESIS"
1 si la celda tiene formato con paréntesis para los valores positivos o para todos los valores, de lo contrario, devuelve 0.
"PREFIJO"
Un valor de texto que corresponde al "prefijo de rótulo" de la celda. Devolverá un apóstrofo (') si la celda contiene texto alineado a la izquierda, comillas (") si la celda contiene texto alineado a la derecha, acento circunflejo (^) si la celda tiene texto centrado, una barra inversa (\) si la celda contiene texto con alineación de relleno y devolverá texto vacío ("") si la celda contiene otro valor.
"PROTEGER"
0 si la celda no está bloqueada y 1 si la celda está bloqueada.
"FILA"
El número de fila de la celda del argumento ref.
"TIPO"
"ANCHO"
Un valor de texto que corresponde al tipo de datos de la celda. Devolverá "b" (para blanco) si la celda está vacía, "r" (para rótulo) si la celda contiene una constante de texto y "v" (para valor) si la celda contiene otro valor. El ancho de columna redondeado a un entero. Cada unidad del ancho de columna es igual al ancho de un carácter en el tamaño actual de fuente seleccionado.
Ref es la celda acerca de la cual desea obtener información. Si se omite, la información especificada en tipo_de_info se devuelve para la última celda cambiada. La siguiente lista describe los valores de texto que devuelve la función CELDA cuando el argumento tipo_de_info es "formato" y el argumento ref es una celda con formato para números integrados.
Ejemplo.
ES.IMPAR(número) Devuelve VERDADERO si número es impar o FALSO si número es par. Si esta función no está disponible y devuelve el error #¿NOMBRE?, instale y cargue el programa de complementos Herramientas para análisis. La sintaxis de la función ES.IMPAR tiene los siguientes argumentos:
Número es el valor que desea probar. Si el argumento número no es un entero, se trunca.
Programa Nacional de Informática
179
Microsoft Office Excel 2010
Ejemplo.
ES.PAR(número) Devuelve VERDADERO si el número es par y FALSO si el número es impar. Si esta función no está disponible y devuelve el error #¿NOMBRE?, instale y cargue el programa de complementos Herramientas para análisis. La sintaxis de la función ES.PAR tiene los siguientes argumentos:
Número es el valor que desea probar. Si el argumento número no es un entero, se trunca.
Ejemplo.
FUNCIONES ES(valor) En esta sección se describen 9 funciones para hojas de cálculo que se utilizan para comprobar el tipo de un valor o referencia. Cada una de estas funciones, a las que se conoce como funciones ES, comprueba el tipo del argumento valor y devuelve VERDADERO o FALSO dependiendo del resultado. Por ejemplo, ESBLANCO devuelve el valor lógico VERDADERO si valor es una referencia a una celda vacía, de lo contrario devuelve FALSO. La sintaxis de las funciones ES tienen los siguientes argumentos:
Número es el valor que desea probar. Puede ser el valor de una celda vacía, de error, lógico, de texto, numérico, de referencia o un nombre que se refiera a alguno de los anteriores. FUNCIÓN
ESBLANCO 180
DEVUELVE VERDADERO SI
Valor se refiere a una celda vacía. Programa Nacional de Informática
Microsoft Office Excel 2010
ESERR ESERROR ESLOGICO ESNOD
ESNOTEXTO ESNUMERO ESREF ESTEXTO
Valor se refiere a cualquier valor de error con excepción de #N/A. Valor se refiere a uno de los valores de error (#N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!). Valor se refiere a un valor lógico. Valor se refiere al valor de error #N/A (el valor no está disponible). Valor se refiere a cualquier elemento que no sea texto. (Tenga presente que esta función devuelve VERDADERO incluso si valor se refiere a una celda en blanco.) Valor se refiere a un número. Valor se refiere a una referencia. Valor se refiere a texto.
Ejemplo.
INFO(tipo) Es texto que especifica el tipo de información que desea obtener. La sintaxis de las función INFO tienen el siguiente argumento:
Número es el valor que desea probar. Puede ser el valor de una celda vacía, de error, lógico, de texto, numérico, de referencia o un nombre que se refiera a alguno de los anteriores.
SI TIPO ES
DEVUELVE
"directorio"
La ruta de acceso del directorio o carpeta en uso
"memdisp"
La cantidad de memoria disponible, en bytes
"memusada"
La cantidad de memoria utilizada para los datos
"archivos"
El número de hojas de cálculo activas en los libros abiertos
"origen"
La referencia absoluta al estilo A1, como texto, precedida de "$A:" para asegurar la compatibilidad con Lotus 1-2-3 versión 3.x. Devuelve la referencia de celda de la primera celda visible en la ventana desde la parte superior izquierda, basada en la posición de desplazamiento actual.
Programa Nacional de Informática
181
Microsoft Office Excel 2010
"versionso" (no lleva acento)
La versión del sistema operativo en uso en forma de texto
"actual"
El modo activo de actualización devuelve "Automático" o "Manual"
"version"
Versión de Microsoft Excel, como texto.
"sistema"
El nombre del entorno operativo: Macintosh = "mac" Windows = "pcdos"
"memtot"
La cantidad de memoria utilizada para los datos
Ejemplo.
N(valor) Devuelve un valor convertido en un número. La sintaxis de la función N tienen el siguiente argumento: Valor es el valor que desea convertir. N convierte los valores del siguiente modo: SI VALOR SE REFIERE A O ES Un número
N DEVUELVE Ese número
Una fecha, en uno de los formatos predeterminados de El número de serie Microsoft Excel de esa fecha VERDADERO
1
FALSO
0
Un valor de error, como #¡DIV/0!
El valor de error
Otro valor
0
Ejemplo.
182
Programa Nacional de Informática
Microsoft Office Excel 2010
ND() Devuelve el valor de error #N/A, que significa "no hay ningún valor disponible". Utilice #N/A para marcar las celdas vacías. Si escribe #N/A en las celdas donde le falta información, puede evitar el problema de la inclusión no intencionada de celdas vacías en los cálculos. (Cuando una fórmula hace referencia a una celda que contiene #N/A, la fórmula devuelve el valor de error #N/A.). TIPO(valor) Devuelve el tipo de valor. Utilice TIPO cuando el comportamiento de otra función dependa del tipo de valor de una celda especificada. La sintaxis de la función TIPO tienen el siguiente argumento:
Valor puede ser cualquier valor de Microsoft Excel, por ejemplo, un número, texto, un valor lógico, etc.:
SI VALOR ES
N DEVUELVE
Número
1
Texto
2
Un valor lógico
4
Un valor de error
16
Matriz
64
Ejemplo.
Programa Nacional de Informática
183
Microsoft Office Excel 2010
TIPO.DE.ERROR(valor_de_error) Devuelve un número que corresponde a uno de los valores de error de Microsoft Excel o devuelve el error #N/A si no existe ningún error. Use TIPO.DE.ERROR en una función SI para determinar el tipo de error y devolver una cadena de texto, como un mensaje, en vez de un valor de error. La sintaxis de la función TIPO tienen el siguiente argumento:
184
Valor_de_error es el valor de error cuyo número identificador desea buscar. Aunque valor_de_error puede ser el valor de error actual, por lo general es una referencia a una celda que contiene la fórmula que se desea probar. SI VALOR_DE_ERRORRES
TIPO_DE_ERROR DEVUELVE
#¡NULO!
1
#¡DIV/0!
2
#¡VALOR!
3
#¡REF!
4
#¿NOMBRE?
5
#¡NÚM!
6
#N/A
7
Otro valor
#N/A
Programa Nacional de Informática
Microsoft Office Excel 2010
Capítulo
6 ADMINISTRAR VENTANAS Y VISTAS DE VENTANAS Y DATOS En este capítulo se trataran los siguientes temas: Dividir las ventanas en paneles Emplear los formularios de datos Ordenar lista Crear filtros Introducción: Reconocer el entorno de trabajo y familiarizarse con las principales funciones, configuraciones, servicios y características que tiene el Microsoft Office Excel 2010.
Programa Nacional de Informática
185
Microsoft Office Excel 2010
CONCEPTOS FUNDAMENTALES Ya habrá notado que con Ms Excel se puede manipular un considerable volumen de información. Llamaremos lista o base de datos a este conjunto de información que está asociada de alguna manera, unas a otras. Toda base de datos agrupa específico.
información referente a un tema o propósito
Para uso de las opciones de análisis de datos accederemos a la Sección Ventana de la Ficha Vista.
Como también veremos la Sección Ordenar y filtrar de la Ficha Datos.
Nueva Ventana
Abrir varias ventanas en un libro proporciona una manera fácil para ver o trabajar simultáneamente con distintas áreas de su libro. Los cambios realizados en cualquier de las ventanas se realizan en el libro. Puede guardar varias ventanas en un libro guardándolo mientras están abiertas varias ventanas. Para quitar windows que se han guardado con el libro, cierre todas las varias ventanas y, a continuación, guarde el documento.
186
Programa Nacional de Informática
Microsoft Office Excel 2010
Ejemplo. d. Para realizar esta acción active la hoja Nueva ventana del Libro Conceptos fundamentales.xlsx.
e. Para abrir varias ventanas en VistaVentanaNueva Ventana.
un
libro
Diríjase
a
la
Ficha
f. Comprobamos a través de la barra de título.
g. Desde ese momento podemos realizar los cambios necesarios y después cerrar la ventana, lo cual afecta al libro en uso. Organizar Todo
Podemos tener varios libros abiertos y verlos simultáneamente en mosaico, en vista horizontal o vertical, en cascada. Si deseamos podemos grabar esta forma personalizada de verlos para la próxima vez.
Programa Nacional de Informática
187
Microsoft Office Excel 2010
Ejemplo. a. Para realizar esta acción tienes que tener en uso varios libros abiertos. b. Para organizar las ventanas de los libros Diríjase a la Ficha VistaVentanaOrganizar todo c. Se muestra la siguiente ventana la cual se indicará como desea ver sus libros en uso.
d. En este caso se escogerá la opción Mosaico.
e. Vemos que acomoda los libros activos de tal manera que se vea en una sola vista. DIVISIÓN DE LA VENTANA Excel divide las ventanas en varios paneles ajustables que contengan las vistas de su libro.
188
Programa Nacional de Informática
Microsoft Office Excel 2010
Ejemplo. a. Para realizar esta acción active la hoja Nueva ventana del Libro Conceptos fundamentales.xlsx. b. Seleccione la celda ó filas ó columnas para dividir. c. En este caso seleccione la celda E11.
d. Para dividir las ventanas VistaVentanaDividir.
del
su
libro
Diríjase
a
la
Ficha
e. Se dividió en 4 paneles para poder ver varias partes distintas de su libro.
OCULTAR Oculta la ventana actual para que no se vea.
Programa Nacional de Informática
189
Microsoft Office Excel 2010
MOSTRAR Muestra las ventanas ocultas.
Ejemplo.
a. Para mostrar las ventanas VistaVentanaMostrar.
ocultas
Diríjase
a
la
Ficha
b. Seleccione la ventana oculta de la lista. c. Presione aceptar.
190
Programa Nacional de Informática
Microsoft Office Excel 2010
TRABAJAR CON PANELES El uso de paneles nos permite “congelar” una sección de nuestra pantalla (en la parte superior y/o a la izquierda) para que al desplazarnos por la lista , los títulos y encabezados de filas siempre estén visibles.
Inmovilizar Paneles
Si lo que nos interesa es dejar inmóviles las cabeceras de los datos y así desplazarnos únicamente sobre los datos teniendo siempre disponible la vista de las cabeceras, podemos utilizar la opción deinmovilizar los paneles. Ejemplo.
a. Para realizar esta acción active la hoja Paneles del Libro Conceptos fundamentales.xlsx.
b. Seleccionaremos la fila 2
Programa Nacional de Informática
191
Microsoft Office Excel 2010
c. Para inmovilizar paneles Diríjase VistaVentanaInmovilizar paneles.
a
la
Ficha
d. Veremos que los encabezados se mantienen fijos.
Movilizar Paneles
Desbloquea todas las filas y columnas para poder desplazarse por toda la hoja de cálculo.
192
Programa Nacional de Informática
Microsoft Office Excel 2010
Inmovilizar fila superior Mantener visible sólo la primera fila superior a medida que se desplaza por toda la hoja de cálculo. Ejemplo.
a. Para realizar esta acción active la hoja Paneles del Libro Conceptos fundamentales.xlsx.
a. Para inmovilizar paneles Diríjase VistaVentanaInmovilizar fila superior.
a
la
Ficha
b. Veremos que los encabezados se mantienen fijos.
Inmovilizar primera columna
Mantener visible sólo la primera columna a medida que se desplaza por toda la hoja de cálculo.
Programa Nacional de Informática
193
Microsoft Office Excel 2010
USO DE FORMULARIO DE DATOS Excel puede generar un formulario de datos integrado para el rango. El formulario de datos muestra todos los rótulos de columna en un único cuadro de diálogo, con un espacio en blanco junto a cada rótulo para que pueda rellenar los datos de la columna. Puede escribir datos nuevos, buscar filas por el contenido de las celdas, actualizar los datos existentes y eliminar filas del rango. Utilice un formulario de datos cuando un informe simple que liste las columnas sea suficiente y no necesite funciones más complejas o personalizadas. Con los formularios de datos, escribir datos es más fácil que escribir en las columnas, especialmente si tiene un rango amplio con más columnas de las que cabrían en la pantalla. Esta herramienta se encuentra desactivada de forma predeterminada en Excel 2010. Para poder utilizarlo primero debe agregarlo a la barra de opciones de acceso rápido de la siguiente forma: 5. Haga clic en la ficha Personalizar de la barra de herramienta de acceso rápido y elija del menú la opción Mas comandos.
194
Programa Nacional de Informática
Microsoft Office Excel 2010
6. En la ventana Opciones de Excel elija la opción Personalizar y en la lista Comandos disponibles en: elija la opción Comando que no están en la cinta de opciones.
7. Elija el comando Formulario y luego haga clic en el botón Agregar y éste formará a ser parte de la barra de herramienta de acceso rápido.
8. Haga clic en Aceptar. Ahora ya lo tenemos listo para utilizarlo.
Programa Nacional de Informática
195
Microsoft Office Excel 2010
Ejemplo.
a. Para realizar esta acción active la hoja Formulario de datos del Libro Conceptos fundamentales.xlsx.
c. Ubique el cursor dentro de la base de datos. d. Para mostrar el formulario Diríjase a la barra de herramientas de acceso rápido y hacer clic en Formulario.
e. Se mostrará una ventana con información de los datos contenido en la hoja de cálculo.
196
Programa Nacional de Informática
Microsoft Office Excel 2010
Acciones permitidas para formularios: ingreso, eliminación y búsqueda de datos. 1. Nuevo: Sirve para introducir un nuevo registro. 2. Eliminar: Eliminar el registro que está activo. 3. Restaurar: Deshace los cambios efectuados. 4. Buscar anterior: Se desplaza al registro anterior. 5. Buscar siguiente: Se desplaza al siguiente registro. 6. Criterios: Sirve para aplicar un filtro de búsqueda. 7. Cerrar: Cierra el formulario. Para cambiar los datos de un registro, primero nos posicionamos sobre el registro, luego rectificamoslos datos que queramos (para desplazarnos por los campos podemos utilizar las teclas de tabulación), sinos hemos equivocado y no queremos guardar los cambios hacemos clic en el botón Restaurar, siqueremos guardar los cambios pulsamos la tecla Intro. Para crear un nuevo registro, hacemos clic en el botón Nuevo, Excel se posicionará en un registrovacío, sólo nos quedará rellenarlo y pulsar Intro o Restaurar para aceptar o cancelar respectivamente. Después de aceptar Excel se posiciona en un nuevo registro en blanco por si queremos insertar variosregistros, una vez agregados los registros, hacer clic en Cerrar.Para buscar un registro y posicionarnos en él podemos utilizar los botones Buscar anterior y Buscarsiguiente o ir directamente a un registro concreto introduciendo un criterio de búsqueda. Pulsamos enel botón Criterios con lo cual pasamos al formulario para introducir el criterio de búsqueda, es similaral formulario de datos pero encima de la columna de botones aparece la palabra Criterios. Por ejemplo, si buscamos un registro con el valor Novela en el campo GENERO, escribimos Novela enGENERO y pulsamos el botón Buscar Siguiente, Excel vuelve al formulario de datos y nos posiciona enel registro de nombre Novela.
Programa Nacional de Informática
197
Microsoft Office Excel 2010
ORDENAR Y FILTRAR LISTAS DE DATOS Ordenar los datos es una parte esencial del análisis de datos. Puede que desee poner una lista de nombres en orden alfabético, compilar una lista de niveles de inventario de productos de mayor a menor u ordenar filas por colores o por iconos. Ordenar los datos ayuda a verlos y a comprenderlos mejor, así como a organizarlos y encontrarlos más fácilmente y a tomar decisiones más eficaces. Usando el botón de A a Z y de Z a A. Sirve para ordenar en orden ascendente y descendente un listado.
Ejemplo.
a. Para realizar esta acción active la hoja Ordenar con A-Z y Z-A del Libro Conceptos fundamentales.xlsx.
b. Ubique el cursor en el campo Descripción para ordenar en orden Ascendente. c. Diríjase a la Ficha datos Sección Ordenar y Filtrar Botón A-Z
d. Veremos el ordenamiento en orden ascendente. 198
Programa Nacional de Informática
Microsoft Office Excel 2010
e. Ahora llevaremos el indicador de la celda al campo N° y ordénelo en forma descendente.
f. El listado ahora aparece en orden descendente por N°, de mayor a menor. Uso del cuadro de diálogo Ordenar para crear diversos niveles de criterios de ordenamiento. Para ordenar la lista por más de un criterio de ordenación, por ejemplo ordenar la lista por elprimer apellido más la fecha de nacimiento, en la pestaña Datos, pulsamos sobre Ordenar... nosaparece el cuadro de diálogo Ordenar donde podemos seleccionar los campos por los que queremosordenar (pulsando Agregar Nivel para añadir un campo), si ordenamos según el valor de la celda, o porsu color o icono (en Ordenar Según), y el Criterio de ordenación, donde elegimos si el orden esalfabético (A a Z o Z a A) o sigue el orden de una Lista personalizada.
Programa Nacional de Informática
199
Microsoft Office Excel 2010
Mediante esta secuencia se puede ordenar por más de un criterio de acuerdo a la necesidad del usuario.
Uso de Filtro automático Filtrar una lista no es ni más ni menos que de todos los registros almacenados en la tabla, seleccionar aquellos que se correspondan con algún criterio fijado por nosotros. Ejemplo. a. Para realizar esta acción active la hoja Ordenar del Libro Conceptos fundamentales.xlsx.
b. Para filtrar Diríjase a la Ficha datos Sección Ordenar y filtrar Botón filtro.
200
Programa Nacional de Informática
Microsoft Office Excel 2010
c. Notará que al costado de cada campo se muestra una flecha, el cual servirá para filtrar.
d. Las flechas servirán para abrir menús flotantes en el encabezado de su lista. e. En esta lista, deberá elegir el criterio para una o más columnas de su lista. Por ejemplo, si usa la categoría AUTOR se muestran las categorías disponibles.
f. Seleccionaremos sólo el AUTOR AA.VV
g. El campo usado como criterio AUTOR se muestra con el símbolo de un pequeño embudo y los números de las filas se muestran de color azul. Uso de Filtro personalizado Filtrar una base de datos no es ni más ni menos que de todos los registros almacenados en la tabla, seleccionar aquellos que se correspondan con algún criterio personalizado por nosotros.
Programa Nacional de Informática
201
Microsoft Office Excel 2010
Ejemplo. a. Para realizar esta acción active la hoja Filtrardel Libro Conceptos fundamentales.xlsx.
b. Para el filtro personalizado Diríjase al campo de la columna Eluego Filtros de texto y Filtro personalizado.
c. Visualizará una nueva ventana el cual en este caso deseamos obtener el listado del intervalo de fecha desde el 01/08/2010 hasta la actualidad.
202
Programa Nacional de Informática
Microsoft Office Excel 2010
d. Lo cual optaremos por escoger las opción Mayor igual que 01/08/2010
e. Veremos el siguiente resultado:
Programa Nacional de Informática
203
Microsoft Office Excel 2010
Preguntas de repaso
11. ¿Para qué sirven la división de ventanas? 12. ¿Para qué sirve el Botón Nueva ventana? 13. ¿Por qué es importante movilizar e inmovilizar los paneles? 14. ¿Cómo se ordena una lista en orden ascendente? 15. ¿Cómo se ordena una lista en orden descendente? 16. ¿Se puede ordenar por más de un criterio, Cómo? 17. ¿Para qué sirve el formulario de datos? 18. ¿Cómo activo el botón del formulario de datos? 19. ¿Cómo ingreso datos mediante el formulario de datos? 20. ¿Cómo realizo un filtro personalizado?
204
Programa Nacional de Informática
Microsoft Office Excel 2010
Capítulo
7 INSERTAR GRÁFICOS DE DATOS En este capítulo se trataran los siguientes temas: Crear gráficos usando el asistente de gráficos Insertar gráficos Dar formato a un grafico Agregar gráficos personalizados Crear graficas circulares Gráficos en 3D Objetivo: El participante aprenderá como elaborar gráficos de un gran impacto visual tomando como origen las tablas de datos elaboradas en la hoja de cálculo.
Programa Nacional de Informática
205
Microsoft Office Excel 2010
Crear gráficos Puede crear gráficos a partir de celdas o rangos no contiguos. Los gráficos facilitan a los usuarios el análisis que puedan lograr sobre los valores de su tabla. Un gráfico facilita la lectura de los datos, permite representar en forma clara y más interesante los valores de una tabla, permite evaluar, comparar y observar el comportamiento de los mismos. Para poder acceder a las opciones de gráficos en MS Excel utilice la sección GRAFICOS de la ficha INSERTAR.
Ejemplo de grafico Partes de un gráfico 206
Programa Nacional de Informática
Microsoft Office Excel 2010
Para representar un gráfico, Excel considera dos conceptos: Series y Categorías. SERIES: es un conjunto de valores que deseamos representar gráficamente. Estos pueden estar ubicados en columnas o filas de nuestra base de datos. El gráfico lo representará en columnas 2D o 3D, puntos de una línea o sectores de un círculo. En el ejemplo, corresponde a los Trimestres o a las Sucursales (incluye el encabezado y los datos). CATEGORÍAS: son los encabezados de filas o columnas que se utilizan para ordenar u organizar los valores de las series. En el ejemplo, corresponden a los encabezados de sucursales (Lima, Trujillo y Arequipa) o los Trimestres (Trim1, Trim2, Trim3, Trim4)
Rangos utilizados para CATEGORIAS
Rangos utilizados para SERIES
RANGOS EMPLEADOSPARA CATEGORIAS Y SERIES
Selección correcta del tipo de gráfico Programa Nacional de Informática
207
Microsoft Office Excel 2010
Como muestra el ejemplo, un gráfico permite representar claramente sus valores de su tabla, pero debe considerar que los tipos de gráficos y subtipos, se eligen de acuerdo a lo que se quiere representar o comunicar. Considere los siguientes criterios para elegir el tipo de gráfico: GRÁFICO DE COLUMNAS. Útil para representar valores o comparar series de datos. Las variantes de este tipo de gráficos son Barras, Cilíndrico, Cónico y Piramidal. En este tipo de gráficos, los valores se pueden distribuir en filas o columnas, usando varias series de datos.
GRÁFICO DE LÍNEAS. Útil para mostrar comportamientos, conductas y tendencias de una serie de datos en un mismo periodo. Una variante es el tipo Área. Aquí, los datos también se pueden distribuir en filas o columnas.
GRÁFICO CIRCULAR. Es útil para mostrar distribuciones o proporciones de un valor en un conjunto de Valores. Use también el de Anillos. Los gráficos circulares simples sólo usan una única serie de valores.
208
Programa Nacional de Informática
Microsoft Office Excel 2010
Crear gráficos Ms Excel proporciona una gran cantidad de herramientas para poder crear un gráfico estadístico de forma rápida y elegante. Para entender mejor estos pasos, trabajaremos sobre la hoja Despachos del libro Presupuesto mensual.xlsx Se desea representar en forma gráfica el presupuesto por cada región.
Procedimiento 1) Debe seleccionar el rango de celdas a representar. Para nuestro ejemplo, seleccione el rango A4: D9. 2) En la ficha Insertar, ubíquese en la sección Gráficos y desglose la lista de opciones del tipo de gráfico Columna, elija de la lista el modelo Columna.
Programa Nacional de Informática
209
Microsoft Office Excel 2010
3) Agrupada 3D.
3) Inmediatamente se insertará en la hoja de cálculo un recuadro con un gráfico estadístico que representa a los datos seleccionados. 4) Adicionalmente en la cinta de opciones se habilita la ficha Herramientas de Gráficos con una gran cantidad de secciones con sus respectivas opciones para adecuar la presentación del gráfico estadístico.
210
Programa Nacional de Informática
Microsoft Office Excel 2010
5) Si desea cambiar el Tipo de gráfico utilice el botón Cambiar tipo de gráfico de la sección TIPO, inmediatamente se activará la ventana de diálogo Cambiar tipo de gráfico.
Siguiendo los pasos anteriores obtendremos el siguiente grafico.
Programa Nacional de Informática
211
Microsoft Office Excel 2010
6) En la sección DATOS, puede modificar la dirección de distribución de los datos en el gráfico. Utilizando el botón Cambiar entre filas y columnas logrará lo siguiente: Utilizando el grafico Columna agrupada 3D tendremos el siguiente grafico.
Dándole clic en la ficha diseño de la ficha principal Herramientas de gráficos, y ejecutamos la opción Cambiar entre filas y columnas señalada en el grafico anterior obtendremos un grafico como el siguiente.
212
Programa Nacional de Informática
Microsoft Office Excel 2010
Utilizando el botón Seleccionar datos, podrá modificar los rangos seleccionados para la muestra de las leyendas y los títulos del gráfico o también volver a cambiar entre filas y columnas. Seleccionando la opción Seleccionar datos obtendremos la siguiente ventana.
Programa Nacional de Informática
213
Microsoft Office Excel 2010
7) En la sección DISEÑOS DE GRAFICO podrá seleccionar la modalidad de organización y distribución de títulos en el gráfico.
8) En este ejemplo hemos elegido el Diseño3 que se muestra de la siguiente forma:
9) En la sección ESTILOS DE DISEÑO encontrará una basta variedad de combinaciones de color para el gráfico. Elija y aplique el que más guste para su trabajo.
Microsoft Office Excel 2010 nos presenta una variada de estilos tal como se muestran el siguiente grafico dándonos una gran cantidad de posibilidades para presentar gráficos estadísticos de alto impacto. 214
Programa Nacional de Informática
Microsoft Office Excel 2010
10) En la sección UBICACIÓN encontrará el botón Mover Grafico. Que le permitirá enviar el gráfico estadístico a una nueva hoja de cálculo o a una hoja de gráfico único. Le mostrará la siguiente ventana.
11) En este ejemplo utilizaremos la opción Hoja nueva y el resultado será el siguiente:
Programa Nacional de Informática
215
Microsoft Office Excel 2010
12) Ahora utilizaremos las opciones de la sub ficha Presentación.
13) En la sección ETIQUETAS encontraremos opciones para adecuar la presentación y ubicación del título del gráfico, de los rótulos de los ejes, de la leyenda y tablas de datos. 216
Programa Nacional de Informática
Microsoft Office Excel 2010
14) En este ejemplo pondremos el título Encima del gráfico y adicionalmente utilizaremos Más opciones del título… y en la ventana Formato del título del gráfico seleccione las combinaciones de formato que requiera para el título.
Más opciones del título.
Después de aplicado los cambios obtendremos un grafico como el siguiente.
Programa Nacional de Informática
217
Microsoft Office Excel 2010
Agregar los rótulos del eje.
Titulo de eje horizontal primario.
Adicionalmente formatearemos el titulo bajo el eje dándole clic en Más opciones del titulo del eje horizontal primario tal como esta señalado en el grafico anterior.
Titulo de eje vertical primario 218
Programa Nacional de Informática
Microsoft Office Excel 2010
Lo formateamos de la misma manera que el titulo del eje horizontal primario y obtendremos al final una imagen como la siguiente. TITULO DEL GRAFICO
TITULO DEL EJE VERTICAL TITULO DEL EJE HORIZONTAL
17) Ahora utilizaremos las opciones Leyenda, Etiquetas de datos y Tabla de datos para completar la información en el gráfico. Programa Nacional de Informática
219
Microsoft Office Excel 2010
18) Aplique los pasos indicados en los siguientes gráficos.
Después de aplicar los pasos anteriores debemos de obtener un grafico como el siguiente.
220
Programa Nacional de Informática
Microsoft Office Excel 2010
Leyenda en la parte derecha Mostrar tablas de datos Mostrar etiquetas de datos
19) En la sección EJES encontraremos opciones para mostrar, ocultar o cambiar el formato de los ejes del gráfico estadístico.
20) En el gráfico actual, modificaremos la presentación del eje vertical primario (utilice la opción Más opciones del eje vertical primario) de tal forma con lo cual se mostrará la ventana Dar formato a eje, en el cual realizaremos los siguientes cambios:
Programa Nacional de Informática
221
Microsoft Office Excel 2010
El límite máximo del eje será el valor 40
Asignaremos el formato número de 1 dígito decimal
21) El eje se mostrará de la siguiente forma: 222
Programa Nacional de Informática
Microsoft Office Excel 2010
22) Ahora le agregaremos líneas verticales de división principales y secundarias al gráfico.
23) Ahora con ayuda de la sección FONDO agregaremos colores y efectos especiales de relleno al gráfico. Programa Nacional de Informática
223
Microsoft Office Excel 2010
24) Utilizando Cuadro gráfico�Más opciones de planos posteriores asignaremos un color especial de Relleno Degradado.
25) Utilizando Plano inferior del gráfico�Más opciones de planos 224
Programa Nacional de Informática
Microsoft Office Excel 2010
inferiores asignaremos un color oscuro de Relleno Sólido
26) Utilizando la opción Giro 3D asignaremos 200° para el eje X y 10° para el eje Y. El gráfico debe mostrarse de forma similar al mostrado a continuación:
Programa Nacional de Informática
225
Microsoft Office Excel 2010
Debe quedar un grafico como el siguiente.
27) Ahora con ayuda de la ficha FORMATO aplicaremos un formato especial a las series seleccionadas, también aplicaremos estilos, etc. 226
Programa Nacional de Informática
Microsoft Office Excel 2010
En el grupo selección actual puede aplicar una serie de formatos tal como lo muestra la siguientes imagen donde esta dividido por categorías.
Adicionalmente también podrá utilizar herramientas para la organización del contenido del gráfico con la sección ORGANIZAR. Con la sección TAMAÑO podrá modificar las dimensiones de todo el gráfico o de los objetos que se hayan agregado al gráfico. Programa Nacional de Informática
227
Microsoft Office Excel 2010
Estilos soportados para los objetos del grafico.
Esta ficha formato en general contiene opciones muy parecidas a las que tiene el Word, solamente hay que señalar el objeto y aplicar el color de fondo, relleno, borde o sino aplicar los estilos que vienen predeterminados para obtener un grafico ya formateado.
228
Programa Nacional de Informática
Microsoft Office Excel 2010
ACTIVIDAD. Objetivo:
Reconocer el entorno de trabajo e identificarse con los distintos comandos para aplicar cambios de formato a un grafico estadístico. Propuesta: Del grafico anterior que se ha trabajado formatear el contenido para obtener el siguiente grafico.
Programa Nacional de Informática
229
PROPIEDAD INTELECTUAL DEL SENATI PROHIBIDA SU REPRODUCCIÓN Y VENTA SIN LA AUTORIZACION CORRESPONDIENTE AÑO DE EDICIÓN 2010 CODIGO DEL MATERIAL 82000668