teoria excel nivel ii - miel.unlam.edu.ar

Com

puta

ción

Tra

nsve

rsal

UNLaM MANUAL TEÓRICO DE EXCEL - NIVEL II

UNIVERSIDAD NACIONAL DE

LA MATANZA

COMPUTACIÓN TRANSVERSAL

TEORIA DE EXCEL

Nivel II (MICROSOFT EXCEL 2007)

Compuesta de 4 partes (unificadas en este material)

POR

Parte 1 Parte 2 Parte 3 Parte 4 Autor Daniel MAYAN Mabel VAZQUEZ-

Inés ARANDA Mabel VAZQUEZ-

Inés ARANDA Rodolfo Verde

Revisión Mabel VAZQUEZ Daniel MAYAN Daniel MAYAN Daniel MAYAN

Com

puta

ción

Tra

nsve

rsal

Autor/es:Daniel Mayán Teoría de Excel: Parte 1

Revisión: Mabel Vázquez Página2 de 29

UNLaM MANUAL TEÓRICO DE EXCEL- Parte 1

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

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)

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

Com

puta

ción

Tra

nsve

rsal




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

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)

Com

puta

ción

Tra

nsve

rsal




0

5

10

15

20

25

-5 0 5 10 -10

-5

0

5

10

15

-5 0 5 10

-150

-100

-50

0

50

100

-5 0 5 10

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).

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

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.

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

Com

puta

ción

Tra

nsve

rsal




3. Se desea ahora obtener la tabla y gráfica de una función trigonométrica del tipo:

Y= a .cos(X)

4. Introducir en la celda C6 la fórmula = C1 * cos (C2) ↵↵↵↵

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

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

Y= a.ex+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.

Com

puta

ción

Tra

nsve

rsal

Autor/es:Mabel Vázquez-Inés Aranda Teoría de Excel: Parte 2

Revisión:Daniel Mayán Página6 de 29


Funciones De Bases De Datos 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: 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. Ver el ejemplo desarrollado en la Ilustración 8:

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.

Base

de

datos

Criterio

Sintaxis general Resultados

Com

puta

ción

Tra

nsve

rsal




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

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

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

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

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

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

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

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

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 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.

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

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

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

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

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

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

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

Com

puta

ción

Tra

nsve

rsal




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. 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: 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.

Com

puta

ción

Tra

nsve

rsal




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. 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)

PRODUCTO CLIENTE CANTIDAD FECHA VENDEDOR DIA VENTA

TR45 MC LONG 12 03/05/2004 JORGE ORTIZ VIE

VT67 LOPEZ 23 04/05/2004 ANA DIAZ JUE

SW23 SMITH 12 05/05/2004 JORGE ORTIZ MIE

SW26 SMITH 32 05/05/2004 LUIS NUÑEZ MAR

TR45 GOMEZ 43 05/05/2004 JORGE ORTIZ LUN

SM26 MC LONG 44 10/05/2004 SILVIA LEVI JUE

TR45 RODRIGUEZ 33 16/05/2004 ANA DIAZ LUN

SM26 PEREZ 26 12/05/2004 LUIS NUÑEZ MIE

SW26 SMITH 29 05/05/2004 LUIS NUÑEZ MAR

SW23 SMITH 12 08/05/2004 ANA DIAZ MAR

VT12 MC LONG 54 08/05/2004 SILVIA LEVI VIE

SM26 MC LONG 32 10/05/2004 JORGE ORTIZ JUE

TR45 LOPEZ 22 10/05/2004 JORGE ORTIZ JUE

SW23 SMITH 12 08/05/2004 ANA DIAZ VIE

TR45 SMITH 65 13/05/2004 LUIS NUÑEZ LUN

VT12 LOPEZ 33 20/05/2004 JORGE ORTIZ MAR

TR45 GOMEZ 23 12/05/2004 LUIS NUÑEZ MAR

SW23 RODRIGUEZ 43 12/05/2004 ANA DIAZ SAB

SW26 MC LONG 34 13/05/2004 SILVIA LEVI VIE

TR45 SMITH 65 13/05/2004 JORGE ORTIZ MAR

TR45 MC LONG 45 13/05/2004 ANA DIAZ LUN

SW23 MC LONG 34 15/05/2004 ANA DIAZ MIE

SW26 MC LONG 23 15/05/2004 LUIS NUÑEZ VIE

TR45 RODRIGUEZ 27 16/05/2004 ANA DIAZ LUN

VT12 MC LONG 12 17/05/2004 LUIS NUÑEZ MIE

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

Com

puta

ción

Tra

nsve

rsal




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.

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

Ilustración 12: Realizando una tabla dinámica

Com

puta

ción

Tra

nsve

rsal




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.

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

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. 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.

Com

puta

ción

Tra

nsve

rsal




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.

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.

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.

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

Com

puta

ción

Tra

nsve

rsal




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.

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á:

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.

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.

Com

puta

ción

Tra

nsve

rsal




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.

Ordenar los elementos de una fila o columna. Se ordenarán los elementos del campo VENDEDORdel último ejemplo, en orden descendente.

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.

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.

Ilustración 18: aumentar o disminuir detalle de

un campo de la tabla

Com

puta

ción

Tra

nsve

rsal




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

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.

Com

puta

ción

Tra

nsve

rsal




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.

Com

puta

ción

Tra

nsve

rsal

Autor/es: Rodolfo Verde Teoría de Excel:Parte 4

Revisión: Daniel Mayán Página17 de 29


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. Se indica a continuación el diagrama de flujo que representa gráficamente el razonamiento anterior.

Una de las principales dificultades que afrontamos a la hora de resolver problemas con la función “SI”, principalmente cuando se encuentran enlazadas o anidadas, es la manera de plasmar nuestras ideas aún difusas. A tal efecto, sugeriremos el siguiente método gráfico al que llamaremos “de la casita” el cual facilitará el razonamientode la consigna: Es de suma importancia seguir la sintaxis de la estructura planteada, ya que a partir de ahora se asumirá la siguiente representación para la función condicional:

= SI (CONDICION; condición es verdadera; condición es falsa)

Nota: Independiente del método planteado, es importante recordar que el desarrollo de la función “SI” para la resolución de un problema planteado tendrá varias soluciones válidas posibles, dependiendo de cómo se plantea la “prueba lógica” o “condición” (primer parámetro de dicha función) y de cómo se van “anidando” .

CONDICION

SALIDA x FALSO

SALIDA x VERDADERO

<=50

SI

NO

“BAJA”

FINAL

<=100

SI SI

NO

“NORMAL” “ALTA”

E5

Com

puta

ción

Tra

nsve

rsal




Regresando al EJEMPLO 1 y aplicando el procedimiento detallado en el diagrama de flujo del inicio, el mismo resultaría utilizando ésta sintaxis tal como se representa a continuación:

Ahora bien, este método gráfico presentado no nos serviría del todo si no podemos trasladarlo a la sintaxis de Microsoft Excel, por lo que a continuación daremos algunas pautas que facilitarán la tarea.

1. Asumimos que ciertas aristas o lados del gráfico representarán los símbolos “(“, “)” y “;” según se detalla.

2. El diagrama “se lee” o representa de izquierda a derecha, y de arriba hacia abajo como se indica:

3. Ahora trasladaremos dicho concepto al ejercicio planteado inicialmente.

(

; ) )

(

;

;

;

E5 <= 100

E5 <= 50

“BAJA”


Equivalente a =SI( ; ;

(

;

; )

E5 <= 50

“BAJA”

E5 <= 100


=SI

Com

puta

ción

Tra

nsve

rsal




Literalmente y de acuerdo a la forma de lectura especificada, procederemos paso por paso a la migración a Microsoft Excel, debiendo obtener la siguiente expresión:

=SI(E5<=50;”BAJA”;SI(E5<=100;”NORMAL”;”ALTA”)) A fin de afianzar este método, se plantearán varios ejercicios con funciones “SI” anidadas. Debe tenerse en cuenta que las fórmulas planteadas en todos los casos corresponderán a la “Condición Final” que le corresponde al primer alumno (Pérez Juan en este caso) y luego se copiará a todo el rango de cálculo (para dicho procedimiento tener en cuenta los conceptos de anclaje de celdas anteriormente citados al mencionar direcciones absolutas, relativas y mixtas). Tener en cuenta que deberá mantenerse la estructura planteada, es decir no deberá agregarse cálculos auxiliares en otras celdas para lograr la solución.

EJEMPLO 2: 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, caso contrario, desaprobará.

Se escribe en E3:

=SI(PROMEDIO(C3:D3)>=7;”APROBADO”;”DESAPROBADO”) Ahora bien, se modificará levemente la consigna anterior y se obtendrá como salida del aprobado el promedio del mismo. (Notar así que el resultado del condicional puede ser un texto o un cálculo).

PROMEDIO(C3:D3) >= 7

PROMEDIO(C3:D3) “DESAPROBADO”

PROMEDIO(C3:D3) >= 7

“APROBADO” “DESAPROBADO”

Com

puta

ción

Tra

nsve

rsal




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

A continuación, se plantearánejemplos cuya dificultad irá en incremento. Se darán algunas soluciones posibles válidas en cada caso.

EJEMPLO 3:Se desean las salidas “APROBADO” y “DESAPROBADO” como condición final del alumno. El alumno aprobará solo cuando obtenga una nota igual o superior a 7 en ambos parciales.

Ésta primera posible solución lo hace mediante funciones SI anidadas. Se escribe en E3:

=SI(C3>=7;SI(D3>=7;”APROBADO”;”DESAPROBADO”);”DESAPROBADO”) 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.

=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á.

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


D3>= 7

C3>=

“APROBADO

”

“DESAPROBADO

”

“DESAPROBAD

O”

Com

puta

ción

Tra

nsve

rsal




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

Se escribe en F3:

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

=SI(Y(PROMEDIO(C3:D3)>=7;E3>=7);”APROBADO”;”DESAPROBADO”) 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á.

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


E3>=7

PROMEDIO(C3:D3)>= 7

“APROBADO” “DESAPROBADO” “DESAPROBADO”

Com

puta

ción

Tra

nsve

rsal




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

=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:

=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:

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


C3>=

D3>= 7

E3>= 7

“APRO “DESAPRO “DESAPRO “DESAPROB

Com

puta

ción

Tra

nsve

rsal




Observarcómo se establecen las condiciones en este caso con la función O respecto a la función Y tratada en la solución anterior. =SI(O(C3<7;D3<7;E3<7);” DESAPROBADO”;”APROBADO”)

EJEMPLO 6:Se pretenden obtener las siguientes salidas de acuerdo al desempeño del alumno a lo largo de un cuatrimestre. Las mismas son: si las notas de ambos parciales son superiores o iguales a 7 y sus inasistencias no superan las 3 unidades, dicho alumno APRUEBA. Si el mismo no ha aprobado alguno de los parciales pero ha asistido según lo establecido (RECUPERA 1 o RECUPERA 2). Si ha fallado en ambos parciales y asistió correctamente a clase, el alumno se encuentra DESAPROBADO. Por último, en el caso que no haya cumplido con el requisito de asistencia (<=3) el alumno quedará LIBRE. Resumiendo: Condición: Parcial 1 y Parcial 2>=7 e Inasistencias <=3 salida: ”APROBADO” Condición: Parcial 1<7, Parcial 2>=7 e Inasistencias <=3 salida: ”RECUPERA 1” Condición: Parcial 1>=7, Parcial 2<7 e Inasistencias <=3 salida: ”RECUPERA 2” Condición: Parcial 1<7, Parcial 2<7 e Inasistencias <=3 salida: ”DESAPROBADO” Condición: Inasistencias>3 salida: ”LIBRE”

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

O(C3<7;D3<7;E3<7)

“DESAPROBADO” “APROBADO”

Com

puta

ción

Tra

nsve

rsal




=SI(E3>=3;”LIBRE”;SI(C3>=7;SI(D3>=7;”APROB”;”REC2”);SI(D3>=7;”REC 1”;”DESAPROB”))) Observar que “las paredes externas derechas de las casas” nos dan una idea de cómo serían los cierres de estas funciones SI anidadas (tres paredes externas derechas implican tres paréntesis). La solución alternativa, utilizando funciones SI e Y en la celda F3, se plantea a continuación.

=SI(Y(C3>=7;D3>=7;E3<=3);”APROB”;SI(E3>3;”LIBRE”;SI(C3>=7;”REC 2”;SI(D3>=7;”REC 1”;”DESAPROB”)))) 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.

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

C3>= 7

E3>3

“APROB”

“LIBRE”

“REC 1” “DESAPROB” “REC 2”

D3>= 7

E3>= 3

D3>= 7

“APROB”

“LIBRE”

“REC 1” “DESAPROB”

D3>= 7

C3>= 7

“REC 2”

Com

puta

ción

Tra

nsve

rsal




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)

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)

Se obtiene la antigüedad del primer empleado, insertando en la celda B2: =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

Com

puta

ción

Tra

nsve

rsal




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.

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: =BUSCARH(B2;$A$13:$E$14;2) 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. 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.) La tabla principal y las dos tablas auxiliares para éste caso son las que se indican a continuación:

Com

puta

ción

Tra

nsve

rsal




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)

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.

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)

Cuadro de

nombres

Cinta de nombres

definidos en ficha

Fórmulas

Com

puta

ción

Tra

nsve

rsal




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:

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.

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. 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:

=BUSCARH(BUSCARV(A2;TABLA2;2;0);TABLA3;2;VERDADERO) 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:

Com

puta

ción

Tra

nsve

rsal




teoria excel nivel ii - miel.unlam.edu.ar

Documents