UNLaM

ers al

MANUAL TEÓRICO DE EXCEL - NIVEL II

Tr an sv

UNIVERSIDAD NACIONAL DE LA MATANZA COMPUTACIÓN TRANSVERSAL

ció n

TEORIA DE EXCEL Nivel II (MICROSOFT EXCEL 2007)

uta

Compuesta de 4 partes (unificadas en este material)

Parte 1 Daniel MAYAN

Revisión

Mabel VAZQUEZ

Co mp Autor

POR

Parte 2 Mabel VAZQUEZInés ARANDA Daniel MAYAN

Parte 3 Mabel VAZQUEZInés ARANDA Daniel MAYAN

Parte 4 Rodolfo Verde Daniel MAYAN

MANUAL TEÓRICO DE EXCEL- Parte 1

UNLaM

ers al

GRAFICOS DE FUNCIONES DE UNA SOLA VARIABLE A PARTIR DE DATOS EN UNA TABLA

Tr an sv

Cuando se necesita estudiar el comportamiento de una función matemática, lo que se debe hacer es averiguar los resultados de la expresión que la representa, para distintos valores de entrada. En general, se forma una tabla de valores, en la que para cada valor de entrada hay un resultado. Excel facilita la generación de éstas tablas mediante la función TABLA DE DATOS, permitiendo generar tablas de simple o de doble entrada, para aplicar a ecuaciones de una o dos variables, respectivamente. Se explicará el caso de tablas de simple entrada. Como ejemplo, se va a obtener la tabla de valores para graficar la ecuación de una recta: Y = aX + b recordando que:

a: pendiente (o inclinación de la recta) b: ordenada al origen (valor de corte de la recta con el eje y)

Co mp

uta

ció n

1. En una planilla en blanco ingresar los datos que se muestran en la Ilustración 1.

Ilustración 1: Tabla de datos de la función a graficar

2. Ubicarse en C2 y escribir la fórmula: =B6 y se oprime ENTER↵ 3. Ubicarse en C6 y escribir la fórmula de la recta de éste ejemplo=C1*C2+C3 y se oprime ENTER ↵ 4. A continuación se creará la tabla de datos; seleccionar el rango B6:C16

Autor/es:Daniel Mayán Revisión: Mabel Vázquez

Teoría de Excel: Parte 1 Página2 de 29

MANUAL TEÓRICO DE EXCEL- Parte 1

UNLaM

ers al

5. Seleccionar la ficha Datos, en la cinta Herramientas de datos, optar por el comando “Análisis y si” y finalmente, “Tabla de datos…”. 6. Pulsar en el cuadro de diálogo correspondiente a Celda de entrada (columna), ya que la tabla de datos es vertical, seleccionando la celda C2 y obteniendo el cuadro de la Ilustración 2. Ilustración 2: Localización de la variable

Co mp

uta

ció n

Tr an sv

Al aceptar, aparecen en el rango C7:C16 los resultados de aplicar la fórmula localizada en C6, reemplazando cada referencia a C2 por el dato correspondiente del rango B7:B16. Tener en cuenta que los textos del rango B5:C5 no son parte funcional de la tabla, sino simplemente rótulos para mejorar la apariencia de la misma. 7. Seleccionar el rango de datos B5:C16 y luego pulsar en la ficha Insertar, cinta Gráficos, y optar por el tipo XY (dispersión), que grafica pares de valores tomando la coordenada X de la primera columna y la coordenada Y de la segunda columna, del rango seleccionado. (Ilustración 3) 8. Elegir el subtipo de gráfico que une los puntos mediante líneas suavizadas, sin marcadores, y el gráfico se generará inmediatamente.Pulsar en la ficha Presentación, cinta Ejes, para desactivar las líneas de cuadrícula horizontales. En la cinta Etiquetas, conviene desactivar la Leyenda. Se puede agregar un título al gráfico, que indique el tipo de ecuación representada. 9. En el último paso, se puede optar por ubicar el gráfico como objeto en la misma hoja o si se prefiere, mediante la ficha Diseño seleccionar una nueva hoja, usando el botón Mover gráfico. Ilustración 3: Elección del tipo de gráfico 10. El gráfico terminado se muestra en la Ilustración 4.

Ilustración 4: Elección del tipo de gráfico

11. Se podrán modificar fácilmente los valores de los parámetros “a” ó “b” de la ecuación, desde C1 y C3, obteniendo automáticamente distintos gráficos. (Ilustración 5, gráficos a,b,c)

Autor/es:Daniel Mayán Revisión: Mabel Vázquez

Teoría de Excel: Parte 1 Página3 de 29

UNLaM

MANUAL TEÓRICO DE EXCEL- Parte 1

25

15

20

10

15

100 50 0

Tr an sv

5 -5

10

0 -5

5 0 0

5

10

-10

0

5

10

-50

0 -5

-5

ers al

a. Colocar en C3 el valor 6 por ejemplo, hará que se desplace la recta hacia arriba, por el cambio de ordenada al origen (las escalas se ajustan automáticamente). b. Colocar en C3 el valor 0 hará que la recta pase por el origen de coordenadas. c. Cambiando C1 por el valor -20 por ejemplo, cambiará la pendiente de la recta al nuevo valor negativo (se ajusta automáticamente la escala del eje Y).

5

10

-100 -150

Ilustración 5: Variación de parámetros en la ecuación (Ejemplos a, b, c)

Ventajas del uso de una tabla en los gráficos de funciones

ció n

La ventaja principal de la tabla es que todo depende de una sola fórmula (en la celda C6 en el ejemplo anterior), fórmula que al ser modificada permite obtener inmediatamente los nuevos resultados y el gráfico asociado. Si se hubiera copiado la fórmula colocada en C6 al rango C7:C16, cada modificación efectuada en C6 debería volver a ser copiada a toda la columna, lo cual no es el procedimiento ideal.

Co mp

uta

1. Se desea modificar la función de la recta de la tabla anterior y su gráfico asociado, por una parábola cuadrática del tipo: Y = aX2 + b 2. Introducir en la celda C6 la fórmula: = C1*C2^2 + C3 ↵ (Nota: el símbolo “^” en Excel es usado para indicar la operación de potenciación) Se consigue actualizar así todos los valores de la tabla y simultáneamente el gráfico asociado cambia en correspondencia para reflejar dicha modificación (Ilustración 6)

Ilustración 6: Cambio de ecuación a una parábola cuadrática

Autor/es:Daniel Mayán Revisión: Mabel Vázquez

Teoría de Excel: Parte 1 Página4 de 29

MANUAL TEÓRICO DE EXCEL- Parte 1

UNLaM

Tr an sv

ers al

3. Se desea ahora obtener la tabla y gráfica de una función trigonométrica del tipo: Y= a .cos(X) = C1 * cos (C2) ↵ 4. Introducir en la celda C6 la fórmula Se consigue actualizar así todos los valores de la tabla y simultáneamente el gráfico asociado cambia en correspondencia para reflejar dicha modificación a la nueva gráfica de la función cosenoidal. (En el ejemplo de la Ilustración 7, se fijó la amplitud a=1 y el valor b=0)

Ilustración 7: Cambio de ecuación a una función trigonométrica

ció n

El último ejemplo, representado en la Ilustración 8, corresponde a la función “exponencial” del tipo:

x

Co mp

uta

Y= a.e +bcon a=3 y b=2

Ilustración 8: representación de una función exponencial

Una ventaja adicional derivada del uso de una tabla, en lugar de una fórmula copiada, es que las primeras no requieren trabajar con direccionamientos absolutos y relativos al construir la tabla de puntos de coordenadas, lo que facilita las tareas y provoca menos errores.

Autor/es:Daniel Mayán Revisión: Mabel Vázquez

Teoría de Excel: Parte 1 Página5 de 29

Funciones De Bases De Datos

UNLaM

ers al

MANUAL TEÓRICO DE EXCEL- Parte 2

Así como las funciones estadísticas permiten realizar operaciones con los valores de un rango. Las funciones de Bases de Datos permiten hacer las mismas operaciones pero con los registros que cumplen con determinada condición.

Ejemplo 1:

Tr an sv

En la farmacia “Del barrio”, utilizan una planilla donde llevan el inventario de stock de los medicamentos que comercializan. En una planilla auxiliar, llevan información mediante funciones de base de datos. Criterio Ver el ejemplo desarrollado en la Ilustración 8:

Co mp

uta

ció n

Base de datos

Resultados

Sintaxis general

Ilustración 9: resultados obtenidos mediante funciones BD (base de datos).

Todos los resultados del ejemploilustrado, corresponden a la categoría “Analgésicos”, y fueron obtenidos de la base de datos de la izquierda, en todos los casos mediante funciones BD, cuya sintaxis general se muestra en el recuadro inferior.

Autor/es:Mabel Vázquez-Inés Aranda Revisión:Daniel Mayán

Teoría de Excel: Parte 2 Página6 de 29

MANUAL TEÓRICO DE EXCEL- Parte 2

UNLaM

ers al

Sintaxis de la función: Base de Datos: En EXCEL una base de datos, es una lista de datos relacionados donde las filas son registros y las columnas campos. La primera fila de la base contiene el rótulo o nombre de cada campo. Se pueden introducir en la función como rango de celdas, o como nombre que representa al rango de la lista. En el ejemplo, el rango de la lista seria: A3:C20

Tr an sv

Nombre del Campo: Es la columna sobre la que se calcula la función. Puede ser el rótulo de la columna encerrado entre comillas (“STOCK”), el nombre de la celda (C3), o un número que represente la posición de la columna en la lista, 1 para la primera columna, 2 para la siguiente y así sucesivamente; en el ejemplo seria 3. Criterios: Rango de celdas donde se han escrito las condiciones de la función. Este rango consiste en dos celdas. La primera indica el campo al cual se refiere el criterio y, la segunda indica el valor que debe tener el campo para que el criterio se cumpla. El criterio puede introducirse como rango de celdas, en el ejemplo sería: F3:F4, o como nombre si se le asignó uno a ese rango.

Funciones de Bases de Datos empleadas en el ejemplo:

BDSUMA: Suma los valores del campo especificado, entre los registros que satisfacen el criterio de selección.

=BDSUMA(A3:C20;C3;F3:F4)

BDPROMEDIO: Calcula el promedio, entre los registros que cumplen con el criterio de selección.

ció n

=BDPROMEDIO(A3:C20;C3;F3:F4)

BDMAX: Encuentra el valor máximo entre los registros que satisfacen el criterio de selección.

=BDMAX(A3:C20;C3;F3:F4)

BDMIN: Encuentra el valor mínimo entre los registros que satisfacen el criterio de selección.

=BDMIN(A3:C20;C3;F3:F4)

uta

BDCONTARA: Cuenta las celdas no vacías del campo especificado, entre los registros que satisfacen el criterio de selección.

=BDCONTARA(A3:C20;B3;F3:F4)

Co mp

BDCONTAR: Cuenta las celdas con contenido numérico (excluye celdas vacías y con valor de texto).

=BDCONTAR(A3:C20;C3;F3:F4)

BDDESVEST: Calcula el desvío estándar de los valores del rango especificado y entre los registrosque satisfacen el criterio de selección.

=BDDESVEST(A3:C20;C3;F3:F4)

BDVAR: Calcula la varianza de los valores del rango especificado.

=BDVAR(A3:C20;C3;F3:F4)

Autor/es:Mabel Vázquez-Inés Aranda Revisión:Daniel Mayán

Teoría de Excel: Parte 2 Página7 de 29

MANUAL TEÓRICO DE EXCEL- Parte 2

UNLaM

ers al

Aclaración: Las funciones para hojas de cálculo empleadas para cálculos de bases de datos o listas, son doce (12). Se describieron solo algunas en el ejemplo, dado que la estructura en todos los casos es la misma.

Tr an sv

Otras funciones de la categoría Base de Datos: DBDESVESTP: Calcula la desviación estándar, basándose en la población total de las entradas seleccionadas de la bases de datos. DBEXTRAER: Extrae de la base de datos un único registro que coincide con el criterio especificado. BDPRODUCTO: Multiplica los valores del campo (columna) de los registros de la base de datos que coinciden con el criterio especificado. DBVARP: Calcula la varianza, basándose en la población total de las entradas seleccionadas de la bases de datos.

Ejemplo 2:

Co mp

uta

ció n

Se ve otro ejemplo, donde el criterio está formado ahora por un intervalo de valores.

Ilustración 10: ejemplo de BDfunciones con intervalos como criterios

Se calcula: • El promedio de analgésicos vencidos mayores a 310 unidades. • La suma de medicamentos que hay en stock con unidades mayores a 310 unidades pero menores a 8000 unidades. • El promedio de unidades vencidas.

Autor/es:Mabel Vázquez-Inés Aranda Revisión:Daniel Mayán

Teoría de Excel: Parte 2 Página8 de 29

UNLaM

MANUAL TEÓRICO DE EXCEL- Parte 3

ers al

TABLAS DINAMICAS Concepto

Una tabla dinámica es una tabla interactiva que resume, o ejecuta una comprobación cruzada, de grandes volúmenes de datos. Se podrá girar las filas y columnas para ver diferentes resúmenes de los datos originales, filtrar los datos mostrando diferentes páginas, o ver en pantalla los detalles de determinadas áreas de interés.

Tr an sv

Se puede crear una tabla dinámica a partir de una lista o base de datos de Microsoft Excel, de una base de datos externa, de varias hojas de cálculo de Microsoft Excel o de otra tabla dinámica. La tabla dinámica resume los datos utilizando la función de resumen que especifique, como SUMA, CONTAR o PROMEDIO. Podrá incluir automáticamente subtotales y totales generales, o utilizar sus propias fórmulas agregando campos y elementos calculados.

Ejemplo de tabla dinámica,paso a paso

Para crear una tabla dinámica, se utilizará el Asistente para tablas dinámicas como guía para la búsqueda y organización de los datos. Se desea crear una tabla dinámica a partir de la siguiente planilla: (se encuentra disponible en la hoja Ejercicio 2 del TP 6 del archivo Excel-Nivel 2, en el sitio Web de la cátedra) FECHA 03/05/2004 04/05/2004 05/05/2004 05/05/2004 05/05/2004 10/05/2004 16/05/2004 12/05/2004 05/05/2004 08/05/2004 08/05/2004 10/05/2004 10/05/2004 08/05/2004 13/05/2004 20/05/2004 12/05/2004 12/05/2004 13/05/2004 13/05/2004 13/05/2004 15/05/2004 15/05/2004 16/05/2004 17/05/2004

Co mp

uta

ció n

PRODUCTO CLIENTE CANTIDAD TR45 MC LONG 12 VT67 LOPEZ 23 SW23 SMITH 12 SW26 SMITH 32 TR45 GOMEZ 43 SM26 MC LONG 44 TR45 RODRIGUEZ 33 SM26 PEREZ 26 SW26 SMITH 29 SW23 SMITH 12 VT12 MC LONG 54 SM26 MC LONG 32 TR45 LOPEZ 22 SW23 SMITH 12 TR45 SMITH 65 VT12 LOPEZ 33 TR45 GOMEZ 23 SW23 RODRIGUEZ 43 SW26 MC LONG 34 TR45 SMITH 65 TR45 MC LONG 45 SW23 MC LONG 34 SW26 MC LONG 23 TR45 RODRIGUEZ 27 VT12 MC LONG 12

VENDEDOR JORGE ORTIZ ANA DIAZ JORGE ORTIZ LUIS NUÑEZ JORGE ORTIZ SILVIA LEVI ANA DIAZ LUIS NUÑEZ LUIS NUÑEZ ANA DIAZ SILVIA LEVI JORGE ORTIZ JORGE ORTIZ ANA DIAZ LUIS NUÑEZ JORGE ORTIZ LUIS NUÑEZ ANA DIAZ SILVIA LEVI JORGE ORTIZ ANA DIAZ ANA DIAZ LUIS NUÑEZ ANA DIAZ LUIS NUÑEZ

DIA VENTA VIE JUE MIE MAR LUN JUE LUN MIE MAR MAR VIE JUE JUE VIE LUN MAR MAR SAB VIE MAR LUN MIE VIE LUN MIE

Ilustración 11: planilla con datos, que se usará para realizar una tabla dinámica

Autor/es:Mabel Vázquez-Inés Aranda Revisión:Daniel Mayán

Teoría de Excel: Parte 3 Página9 de 29

MANUAL TEÓRICO DE EXCEL- Parte 3

UNLaM

Tr an sv

ers al

1. Situar el cursor en la primera fila y primera columna de la base de datos. 2. En la ficha Insertar, cinta Tablas, se elige Tabla dinámica. (Ilustración 12,a) 3. Ahora, el asistente pide que seleccione el rango que contiene los datos que se quieren utilizar. Si el rango ya aparece seleccionado, como en nuestro caso, no hará falta que lo seleccione. Si no fuera así, deberá seleccionarlo incluyendo la primera fila de rótulos. (Ilustración 12,b) 4. Seleccionar la casilla sobre donde se quiera generar la tabla, optando por Nuevahoja de cálculo. 5. Se genera automáticamente una nueva hoja de cálculo, en la que aparecen las estructuras necesarias para crear la nueva tabla dinámica. (Ilustración 13) 6. Aparece una ventana a la derecha con los nombres de los campos y la descripción de los elementos de la tabla: rótulos de filas, de columnas, valores y filtros.

Co mp

uta

ció n

Ilustración 12: Realizando una tabla dinámica

Ilustración 13: Eligiendo los campos de la tabla dinámica.

Autor/es:Mabel Vázquez-Inés Aranda Revisión:Daniel Mayán

Teoría de Excel: Parte 3 Página10 de 29

MANUAL TEÓRICO DE EXCEL- Parte 3

UNLaM

ers al

7. Seleccionar el campo FECHA y arrastrarlo hasta posición Rótulos deFila de la tabla. 8. Seleccionar campo VENDEDOR y arrastrarlo hasta posición Rótulos de columna de la tabla. 9. Seleccionar campo CANTIDAD y arrastrarlo hasta posición Valores de la tabla. 10. Seguramente en Valores aparecerá por defecto Suma de Cantidad (Si no se desea esa operación, y por ejemplo se desea obtener la Máxima cantidad vendida, hacer clic sobre Suma de Cantidad, con lo que se desplegará un cuadro con una lista de funciones; seleccionar Configuración de campo de valor y elegir función Máximo. Pulsar Aceptar para volver al Asistente). 11. Seleccionar el campo DIA VENTA y arrastrarlo hasta posición Filtro de informe de la tabla.

ció n

Tr an sv

El asistente habrá terminado de generar una tabla dinámica como la que se muestra en la Ilustración 14.

Ilustración 14: tabla dinámica finalizada

Partes de una tabla dinámica

uta

Una tabla dinámica se compone de las siguientes partes o elementos: Campo de página: Un campo que se asigna a una orientación de página o filtro. En el ejemplo, DIA VENTA es un campo de página que puede utilizarse para filtrar el informe por días. Con el campo DIA VENTA, se pueden mostrar datos resumidos sólo del día lunes, sólo del día martes, o de cualquier otro día. Si hace clic en otro elemento de un campo de página, todo el informe de tabla dinámica cambiará para mostrar sólo los datos resumidos asociados a ese elemento.

Co mp

Elemento: Una sub-categoría o integrante de un campo de tabla dinámica. En el ejemplo, López y Smith son elementos del campo CLIENTE. Los elementos representan entradas únicas en el mismo campo o columna en los datos de origen. Los elementos aparecen como rótulos de columna o de fila, o aparecen en las listas desplegables de los campos. Campos de fila: Campos de los datos de origen subyacentes que se asignan a una orientación de fila en un informe de tabla dinámica. En el ejemplo anterior, FECHA y CLIENTE son campos de fila. Un informe de tabla dinámica que tenga más de un campo de fila tiene un campo de fila interior (CLIENTE, en el ejemplo), que es el más próximo al área de datos. Cualquier otro campo de fila se considera un campo de fila exterior. Los campos de fila interiores y exteriores tienen atributos diferentes. Los elementos del campo de fila exterior más lejano se muestran sólo una vez, pero los elementos del resto de los campos se repiten tantas veces como sea necesario.

Autor/es:Mabel Vázquez-Inés Aranda Revisión:Daniel Mayán

Teoría de Excel: Parte 3 Página11 de 29

MANUAL TEÓRICO DE EXCEL- Parte 3

UNLaM

ers al

Elemento del campo de página: Cada entrada o valor exclusivos del campo o columna de la lista de origen o de la tabla será un elemento en la lista de campos de página. En el ejemplo, LUNES es el elemento actualmente seleccionado en el campo de página DIA VENTA y el informe de tabla dinámica mostrará los datos resumidos del día lunes. Campo de columna: Un campo que se asigna a una orientación de columna en un informe de tabla dinámica. En el ejemplo anterior, VENDEDOR es un campo de columnas con cuatro elementos, Jorge Ortiz, Ana Díaz, Luis Núñez y Silvia Levi. Un informe de tabla dinámica puede tener varios campos de columna del mismo modo que puede tener varios campos de fila. La mayoría de los informes de tabla dinámica con sangría no tienen campos de columna.

Tr an sv

El área de datos: es la parte de la tabla dinámica que contiene los datos resumidos. Las celdas del área de datos muestran los datos resumidos de los elementos de los campos de fila y de columna. Los valores de cada celda del área de datos representan un resumen de los datos procedentes de filas o registros de origen. En el ejemplo, puede realizarse un resumen de cantidades vendidas por VENDEDOR y por FECHA.

Operaciones con una tabla dinámica.

Cuando se genera una tabla dinámica, Excel muestra una barra de herramientas para poder realizar diferentes operaciones con los datos obtenidos de la tabla. Realizar los ejercicios siguientes para ver cómo funcionan las diferentes opciones. Si no aparecen las fichas con opciones, es porque no se está posicionado sobre la tabla dinámica. Cambiar la función. Se cambiará la función Suma por la función Máximo. 1. Seleccionar la casilla de la tabla dinámica (A3) en Suma de Cantidad.

ció n

2. Pulsar sobre el botón Configuración de campo, en la ficha Campo activo. 3. Se abre un cuadro de diálogo. De la lista Resumir campo de valor por, escoger elemento Max y pulsar sobre Aceptar. 4. Observar que la tabla dinámica muestra la venta máxima realizada por Vendedor y por Fecha. En la columna Total General muestra el máximo vendido por día, y en la Fila Total General, muestra el máximo, pero por cada Vendedor.

Co mp

uta

5. Si no se desea incorporar a la tabla esos Totales Generales, repetir el paso 1. y pulsar sobre la ficha Diseño, cinta Diseño, el botón Totales generales, pulsando sobre el botón Desactivado para filas y columnas.

Ilustración 15: Cantidad máxima, sin totales generales

Autor/es:Mabel Vázquez-Inés Aranda Revisión:Daniel Mayán

Teoría de Excel: Parte 3 Página12 de 29

MANUAL TEÓRICO DE EXCEL- Parte 3

UNLaM

ers al

Intercambiar filas y columnas: Se pretende construir una tabla dinámica como la anterior pero intercambiando filas y columnas, es decir, en fila VENDEDOR y en columna FECHA.

Tr an sv

1. Seleccionar la primera casilla de la tabla dinámica (A1). 2. Ahora arrastrar el campo FECHA a la posición Columna, y el campo VENDEDOR a la posición Fila. Cambiar Máx. de CANTIDAD por Suma de CANTIDAD haciendo clic sobre Máx.de CANTIDAD y luego seleccionado la función Suma. 3. Filtrar con losRótulos de columna, eligiendo un Filtro de fecha que sea anterior al 11/05/2004, ya que sólo interesan los 10 primeros días del mes. La tabla resultante será:

ció n

Ilustración 16: tabla dinámica filtrada por fechas (rótulos de columnas) Incluir un nuevo elemento para las filas.

Se hará que se visualicen los PRODUCTOSvendidos por cada VENDEDOR, en cada fecha de los primeros 10 días del mes.

Co mp

uta

1. Se mantiene el filtro de Rótulo de columna ya aplicado en el ejercicio anterior, para 10 días. 2. Arrastrar el campo PRODUCTO al sector de Rótulos de fila, por debajo del campo VENDEDOR que estaba antes. Resulta la tabla de la Ilustración 17.

Ilustración 17: agregando otro rótulo de fila.

Autor/es:Mabel Vázquez-Inés Aranda Revisión:Daniel Mayán

Teoría de Excel: Parte 3 Página13 de 29

UNLaM

MANUAL TEÓRICO DE EXCEL- Parte 3

ers al

3. Probar desde la ficha Opciones, el resultado de disminuir y aumentar el detalle de productos vendidos por cada vendedor, posicionándose sobre cualquier vendedor y luego usando los botones de la cinta Campo activo.

Ilustración 18: aumentar o disminuir detalle de un campo de la tabla

Tr an sv

Ordenar los elementos de una fila o columna.

Se ordenarán los elementos del campo VENDEDORdel último ejemplo, en orden descendente.

uta

ció n

1. Hacer clic sobre el nombre de cualquier vendedor de la tabla dinámica. 2. En la ficha Opciones, cinta Ordenar, se selecciona el botón Z
A para orden descendente, o sino para tener más opciones, el botón Ordenar a su derecha. (Ilustración 19) 3. Pulsar sobre Aceptar para obtenerla tabla ordenada por VENDEDOR en forma Descendente.

Ilustración 19: ordenar la tabla dinámica por uno o varios campos.

Co mp

GRAFICO DINÁMICO (desde una tabla dinámica)

Se desarrollará otro punto referente a las posibilidades que Excel ofrece para trabajar con planillas, como son los gráficos dinámicos a partir de una tabla dinámica. Para realizar el ejemplo,se va a emplear la tabla dinámica de totales realizada con la planilla Ejercicio 2, del TP6Excel-Nivel II, en la explicación anterior, sin ningún filtro de campos ni totales de filas ni columnas. Se muestra en la Ilustración 20 la configuración inicial de la tabla dinámica que se usará para desarrollar el ejemplo de gráfico dinámico.

Autor/es:Mabel Vázquez-Inés Aranda Revisión:Daniel Mayán

Teoría de Excel: Parte 3 Página14 de 29

UNLaM

Tr an sv

ers al

MANUAL TEÓRICO DE EXCEL- Parte 3

ció n

Ilustración 20: Tabla dinámica usada para construir el gráfico dinámico. a) Seleccionar una celda cualquiera de la tabla y hacer un clic en la ficha Opciones, cinta Herramientas, eligiendo el botón Grafico dinámico. b) Excel ofrece por defecto un gráfico con las siguientes características: Tipo de gráfico: Columnas, Subtipo: Columnaagrupada. Si no representa lo que deseamos para los datos de la tabla, hay que realizarle cambios, como se hará en éste caso: elegiremos Barras, y en Subtipo: Barra agrupada con efecto 3D

Co mp

uta

Se obtiene un gráfico del tipo seleccionado, tal como se representa en la Ilustración 21. Posee dos ejes de valores, uno de suma de cantidades (el eje horizontal) y otro de Fecha (eje vertical). El aspecto del mismo, se puede modificar a gusto propio. Por ejemplo, haciendo clic con el botón derecho del mouse sobre cualquier barra, setiene una opción llamada: Dar formato a serie de datos, con una serie de solapas que permiten modificar desde la trama de la serie seleccionada (estilo, grosor de línea, efectos de relleno) hasta poder mostrar valores haciendo clic en la opciónAgregar etiquetas de datos. En el gráfico dinámico obtenido se pueden observar 3 botones, tal como sucede en la tabla dinámica, para producir el filtrado de datos, por eso el nombre de “gráfico dinámico”: Botón Día Venta: Agrupa toda la semana o permite seleccionar un día determinado. Botón Vendedor: Permite identificar todos los vendedores, o elegir a uno de ellos. Botón Fecha: Permite todas u optar por una de las fechas de venta.

Para observar el comportamiento del gráfico, se va a producir al filtrado, en correspondencia con el realizado antes en la tabla dinámica, es decir, se va a seleccionar del botón FECHA los 10 primeros días del mes y presionar el botón aceptar. El resultado que se obtiene, se representa en la Ilustración 22.

Autor/es:Mabel Vázquez-Inés Aranda Revisión:Daniel Mayán

Teoría de Excel: Parte 3 Página15 de 29

UNLaM

Tr an sv

ers al

MANUAL TEÓRICO DE EXCEL- Parte 3

Co mp

uta

ció n

Ilustración 21: gráfico dinámico obtenido desde la tabla dinámica

Ilustración 22: Gráfico y tabla dinámica filtrados por un criterio de fecha

Como se puede observar, tanto la tabla como el gráfico, se encuentran filtrados en correspondencia con el criterio establecido, lo que se indica con el símbolo de un pequeño embudo en el campo.

Autor/es:Mabel Vázquez-Inés Aranda Revisión:Daniel Mayán

Teoría de Excel: Parte 3 Página16 de 29

UNLaM

MANUAL TEÓRICO DE EXCEL- Parte 4

ers al

Funciones SI anidadas EJEMPLO 1: En la celda E5 se tiene como dato un número que se desea evaluar, de modo que deberá informarse Baja si el valor de E5 es menor o igual a 50; si la cantidad supera ese valor pero no es mayor que 100, se deberá indicar Normal. Para valores superiores a 100, la leyenda informada será Alta.

E5

=7;”APROBADO”;”DESAPROBADO”);”DESAPROBADO”)

ció n

Otra resolución alternativa al mismo problema se podría realizar mediante la utilización combinada de la función condicionalSIy el conector lógicoY.

Y(C3>= 7; D3>= 7)

uta

“APROBADO”

“DESAPROBADO”

Co mp

=SI(Y(C3>=7;D3>=7);”APROBADO”;”DESAPROBADO”)

En éste caso no hizo falta anidar condiciones como sucedió en la solución anterior.

EJEMPLO 4:Se desean las salidas “APROBADO” y “DESAPROBADO” como condición final del alumno. El alumno aprobará si el promedio de las notas es mayor o igual a 7 y la nota de los TPs es mayor o igual a 7, caso contrario, desaprobará.

Autor/es: Rodolfo Verde Revisión: Daniel Mayán

Teoría de Excel:Parte 4 Página20 de 29

UNLaM

Tr an sv

La solución mediante funciones SI anidadas se podría representar:

ers al

MANUAL TEÓRICO DE EXCEL- Parte 4

PROMEDIO(C3:D3)>= 7

E3>=7 “APROBADO”

“DESAPROBADO”

Se escribe en F3:

“DESAPROBADO”

ció n

=SI(PROMEDIO(C3:D3)>=7;SI(E3>=7;”APROBADO”;”DESAPROBADO”);”D ESAPROBADO”) La resolución mediante la utilización combinada de la función condicionalSIy el conector lógico Y.

Y(PROMEDIO(C3:D3)>= 7; E3>=7

“DESAPROBADO”

uta

“APROBADO”

=SI(Y(PROMEDIO(C3:D3)>=7;E3>=7);”APROBADO”;”DESAPROBADO”)

Co mp

Nota: Compararlos ejemplos 3 y 4 y analizar el impacto en la resolución del problema cuando se agrega una condición, tanto al utilizar funciones SI anidadas o utilizando la función Y. Analizar la diferencia de la consigna respecto a la anterior y los cambios en la resolución que ello implica.

EJEMPLO 5:Se desean las salidas “APROBADO” y “DESAPROBADO” como condición final del alumno. El alumno aprobará si la nota del primer parcial es mayor o igual a 7, la nota del segundo parcial es mayor o igual a 7 y la nota de los TPs en mayor o igual a 7, caso contrario, desaprobará.

Autor/es: Rodolfo Verde Revisión: Daniel Mayán

Teoría de Excel:Parte 4 Página21 de 29

C3>=

D3>= 7 E3>= 7 “APRO

“DESAPRO

Tr an sv

La solución mediante funciones SI anidadas, escrita en la celda F3:

ers al

UNLaM

MANUAL TEÓRICO DE EXCEL- Parte 4

“DESAPROB

“DESAPRO

ció n

=SI(C3>=7;SI(D3>=7;SI(E3>=7;”APROB”;”DESAPROB”);”DESAPROB”);” DESAPROB”) La resolución mediante la utilización de funciones SI e Y:

uta

Y(C3>=7;D3>= 7;E3>=7)

“APROBADO”

“DESAPROBADO”

Co mp

=SI(Y(C3>=7;D3>=7;E3>=7);”APROBADO”;”DESAPROBADO”) Si el enunciado estableciera que el alumno aprueba simplemente con 7 ó más puntos en cualquiera de las tres notas, la resolución mediante la utilización de funciones SI y O sería:

Autor/es: Rodolfo Verde Revisión: Daniel Mayán

Teoría de Excel:Parte 4 Página22 de 29

UNLaM

MANUAL TEÓRICO DE EXCEL- Parte 4

ers al

O(C3= 7 D3>= 7

“LIBRE”

“REC 1”

“DESAPROB”

uta

“REC 2”

=SI(Y(C3>=7;D3>=7;E33;”LIBRE”;SI(C3>=7;”REC 2”;SI(D3>=7;”REC 1”;”DESAPROB”))))

Co mp

Notar que no siempre es la solución más conveniente (observar las diferencias entre solución 1 y 2 de este ejemplo 6). Recordar que en función a como se haga el planteo de la condición o prueba lógica de la función SI, se invertirán las salidas por verdadero o falso. Suele creerse que la función Y u O siempre nos simplifica la resolución de un problema de funciones SI anidadas. Como puede observarse en las soluciones planteadas, el primer planteo (con funciones SI anidadas sin utilización de funciones Y u O) resulta más sencilla que el segundo planteo. Por dicha razón evaluar los “beneficios y contras” a la hora de resolver estas consignas ya que un planteo literal de la consigna no siempre es la mejor solución.

Autor/es: Rodolfo Verde Revisión: Daniel Mayán

Teoría de Excel:Parte 4 Página24 de 29

MANUAL TEÓRICO DE EXCEL- Parte 4

UNLaM

ers al

Se realizarán a continuación ejemplos detallados con funciones BUSCARV y BUSCARH con diversas variantes y también algunos casos anidando las mismas. (Aclaración importante: en el Office 2010 y posteriores, estas funciones cambiaron sus nombres a CONSULTAV y CONSULTAH respectivamente, aunque sigue siendo idéntico su funcionamiento)

Tr an sv

EJEMPLO 7: Se desea completar la tabla A1:D7 con datos y cálculos a partir de las otras dos tablas, de modo de completar para cada empleado los años de antigüedad en la empresa y los días de vacaciones que le corresponden según la misma. (Una tabla auxiliar es vertical y la otra horizontal)

ció n

Se obtiene la antigüedad del primer empleado, insertando en la celda B2:

uta

=BUSCARV(A2;$G$1:$H$10;2;0)

Recordamos la estructura de la función y sus cuatro parámetros: El valor contenido en A2 es buscado en la tabla ó matriz auxiliar G1:H10. G1:H10 es la tabla ó matriz de búsqueda, que se ha anclado mediante direcciones absolutas con el símbolo “$” para columnas y filas (ó presionando la tecla F4). Esto permitirá luego extender la fórmula de B2 hacia abajo, sin que se desplace la matriz de búsqueda. El valor del tercer parámetro es el indicador de la columna que contiene en la tabla auxiliar el valor buscado, cuando se encuentra la coincidencia con la referencia. (Es “2” en el ejemplo) Si el último parámetro de la función se indica con “1” ó “Verdadero” o fue omitido en la expresión, la función actuará por defecto asumiendo que la tabla de referencia está ordenada en forma ascendente y la coincidencia devuelta será la más próxima que halle al valor de referencia. El último parámetro “0” ó “Falso” en nuestro ejemplo, indica que NO se puede asegurar que la tabla de búsqueda se halla ordenada en forma ascendente y que además la función está dando como resultado la coincidencia exacta (es decir que el valor buscado fue hallado en la

Co mp

• •

•

•

Autor/es: Rodolfo Verde Revisión: Daniel Mayán

Teoría de Excel:Parte 4 Página25 de 29

MANUAL TEÓRICO DE EXCEL- Parte 4

tabla auxiliar mediante una búsqueda exhaustiva, fila por fila, independientemente de que esté o no ordenada). Ésta última alternativa puede ser más lenta si la tabla de búsqueda es muy extensa pero garantiza encontrar la coincidencia exacta, aún en tablas desordenadas. Indicará un error en caso que no exista el valor de referencia buscado mediante la leyenda “#N/A” (NotAvailable- No disponible). Puede ser de utilidad para comprender el último parámetro de la función, que el alumno pruebe poner el último valor en 0 ó 1 en forma alternativa y simultáneamente ordenar y desordenar la tabla de búsqueda, para ver los resultados obtenidos al ingresar en A1 un valor existente y luego uno inexistente en la tabla de búsqueda.

ers al

•

UNLaM

=BUSCARH(B2;$A$13:$E$14;2)

Tr an sv

Para la obtención de los días de vacaciones que corresponden a cada empleado, se debe usar la tabla de referencia que se tiene en A13:E14, que indica a partir de que antigüedad se cambia el rango en la escala de días de vacaciones. Es decir, no se debe buscar la coincidencia exacta de los años en la empresa sino el valor más próximo en la escala (el Buscar siempre aproxima hacia el valor próximo inferior) para obtener lo deseado mediante la búsqueda horizontal en la celda C2:

Notar que el cuarto parámetro ha sido omitido (ó podría indicarse “Verdadero” ó “1”) a diferencia de la función en B2, para que así realice una búsqueda aproximada como se explicó, ya que hay varias antigüedades que de otro modo no tendrían coincidencias y darían el error indicado anteriormente. El resultado final obtenido es el que se indica en la gráfica de la derecha.

ció n

Hagamos un ligero cambio en el planteo del enunciado del Ejemplo 7 de modo que el enunciado del nuevo ejercicio sea ahora el que se indica en el Ejemplo 8

EJEMPLO 8: Se desea completar la tabla A1:B7 con datos y cálculos a partir de las otras dos tablas, de modo de completar para cada empleado los días de vacaciones que le corresponden según la antigüedad que posee. (Una tabla auxiliar es vertical y la otra horizontal. No se admiten agregar filas y columnas auxiliares a la planilla.)

Co mp

uta

La tabla principal y las dos tablas auxiliares para éste caso son las que se indican a continuación:

Autor/es: Rodolfo Verde Revisión: Daniel Mayán

Teoría de Excel:Parte 4 Página26 de 29

MANUAL TEÓRICO DE EXCEL- Parte 4

UNLaM

ers al

El único cambio respecto al ejemplo anterior es que la tabla principal A1:B7 no tiene la columna “Antigüedad” para obtener el resultado intermedio que permitía luego calcular los días de vacaciones. También se restringe expresamente en el enunciado agregar celdas para cálculos auxiliares. Es decir que se deberán hacer todos los cálculos en B2 antes de extender la función hacia el resto del rango. Para conseguirlo, la única solución posible es anidar dos funciones BUSCAR. Antes de resolverlo, se aprovechará el ejemplo para mostrar como optimizar la resolución de las funciones mediante el uso de nombres de rangos para las celdas:







Seleccionando A1:B7 se hace luego un clic en el “Cuadro de nombres”, cuadro que se encuentra arriba de la celda A1, a la izquierda de la barra de fórmulas (Observar figura de la hoja próxima) y escribimos en él un nombre para identificar a ese rango, p.ej “Tabla1” sin olvidar oprimir ENTER para fijar el nombre elegido (¡el nombre de rango no admite espacios!). Repetimos el procedimiento para Tabla2 (rango G1:H10) y para Tabla3 (rango A13:E14). Todos los nombres de rango son arbitrarios y sin incluir espacios. Desplegando en el cuadro de nombres se puede seleccionar ahora cualquiera de los rangos con un simple clic sobre su nombre. En cualquier fórmula o función se puede indicar ahora a esos rangos por su nombre en vez de por sus celdas de los extremos. En la cinta “Nombres definidos” de la solapa “Fórmulas” se tienen todas las opciones para agregar, modificar o eliminar cualquier nombre de rango que se halla definido en la planilla, en cualquiera de sus hojas. (Observar figura de la hoja próxima)

Tr an sv



uta

ció n

Se verá enseguida a través del ejemplo, cuales son las ventajas de haber hecho ésta definición de nombres de rangos antes de empezar a trabajar con las funciones en la planilla.

Co mp

Cuadro de nombres Cinta de nombres definidos en ficha Fórmulas

Resolvemos ahora el ejemplo: En primer lugar, se puede obtener en B2 la antigüedad del empleado (resultado intermedio necesario para calcular luego los días de vacaciones, que es lo que se pide en B2):

=BUSCARV(A2;$G$1:$H$10;2;0)

Autor/es: Rodolfo Verde Revisión: Daniel Mayán

Teoría de Excel:Parte 4 Página27 de 29

MANUAL TEÓRICO DE EXCEL- Parte 4

UNLaM

ers al

Todos los comentarios de ésta función son idénticos a los hechos en el ejemplo anterior, ya que se usó la misma expresión también. Cambiemos la forma en que se construyó la función anteriormente, utilizando los nombres de rangos definidos y escribiendo:

=BUSCARV(A2;TABLA2;2;0)

Notamos que el resultado obtenido es idéntico en ambos casos y podemos entonces extender la función a todo el rango B2:B7. Las ventajas de usar nombres de rangos en funciones son entonces evidentes:

Se tienen los resultados intermedios que se indican en la gráfica. Recordamos que esos valores son la antigüedad del empleado, no los días de vacaciones que queremos obtener.

Tr an sv

1. Permite nombres de rangos fácilmente recordables y/o identificables en cualquier fórmula ó función, especialmente útil si las tablas usadas son muchas. 2. Evita la sintaxis utilizando el símbolo “$” para escribir direcciones absolutas ó mixtas. 3. Nos desentendemos de los problemas ocasionados cuando al extender una función a otras celdas vecinas, luego se mueven las tablas de referencia. El rango con un nombre definido será siempre identificado por cualquier función, aún si ese rango se cambia de posición dentro del archivo.

ció n

Para conseguir esto último, deberemos obtener a partir de los años de antigüedad de cada caso y usando la otra tabla, cuántos días de vacaciones le corresponden a cada empleado. Éstos resultados intermedios son el valor buscado por la otra función. Por lo tanto agregamos en la misma celda B2 e inmediatamente después del signo “=” el texto que falta para obtener:

uta

=BUSCARH(BUSCARV(A2;TABLA2;2;0);TABLA3;2;VERDADERO)

Co mp

En ésta función anidada, el BUSCARV interior (que se puso en texto de menor tamaño para que se distinga fácilmente) ya se había construido en el paso anterior, y nos daba como resultado el dato que debe usar el BUSCARH exterior para encontrar la coincidencia en la Tabla3. Notar que las dos funciones de búsqueda utilizadas usan en un caso la coincidencia exacta (cuarto parámetro en “0”) mientras que en la otra búsqueda ese valor está en “Verdadero” por ser una búsqueda de coincidencia aproximada. El Ejemplo 8 terminado se observa a continuación, con el puntero posicionado en B2:

Autor/es: Rodolfo Verde Revisión: Daniel Mayán

Teoría de Excel:Parte 4 Página28 de 29

UNLaM

Co mp

uta

ció n

Tr an sv

ers al

MANUAL TEÓRICO DE EXCEL- Parte 4

Autor/es: Rodolfo Verde Revisión: Daniel Mayán

Teoría de Excel:Parte 4 Página29 de 29