• Author / Uploaded
• YerKo002

• Categories
• Microsoft Excel
• Matriz (Matemáticas)
• Tabla (Base de datos)
• Fórmula
• Función (Matemáticas)

Citation preview

MANUAL    EXCEL    AVANZADO    BIOS   

Manual Excel Avanzado    BIOS    Módulo Funciones Avanzadas    Primer Ejemplo – Funciones simples    Dada la siguiente planilla:     

    Supongamos que la nota final se calculará basándonos en el promedio de notas que obtuvo cada  estudiante en sus parciales.  Por lo tanto aplicaremos la función promedio:    =PROMEDIO (B2:D2)    Recordemos que cualquier función que vayamos a aplicar debemos posicionarnos sobre la celda, y  comenzar con el signo = o en su defecto el signo +.    Por otro lado si hubiéramos querido tomar en cuenta solo dos de los parciales dentro del  promedio tendríamos que:    =PROMEDIO (B2; D2)    En el caso de que queramos tomar rangos discontinuos esto se aplicaría de la siguiente forma:    =PROMEDIO (B2:D2; H2:J2)    La planilla hasta ahora quedaría así:   

 

 

Además en el caso de copiar esta fórmula, podríamos realizarlo mediante la opción clásica de dar  sobre el botón derecho sobre la celda, seleccionar la opción copiar, y luego marcar el rango (en  este caso de E3:E8) y darle pegar.    Mas allá de esta clásica opción también se podría copiar la formula de forma simple dando DOBLE  CLIC en el borde inferior derecho de la celda que contiene la formula a copiar.    En la siguiente columna, se hará un breve repaso de la formula condicional SI.  De acuerdo a esta formula deberá aplicarse:    =SI (prueba _ lógica; valor verdadero; valor falso)    En el caso de la planilla que manejamos anteriormente, esta quedaría de esta manera:    =SI (E2 1000 Then  Descuento = Val(InputBox("Entrar Descuento", "Entrar"))  End If 

  ActiveSheet.Range("A1").Value = Precio  ActiveSheet.Range("A2").Value = Descuento  ActiveSheet.Range("A3").Value = Precio ‐ Descuento    End Sub    Viendo los dos programas anteriores puede que le surja la duda de si emplear variables o  directamente valores almacenados en las celdas. La solución es fácil, lo que le parezca más  conveniente en cada caso concreto que desee solucionar. Las variables, aunque muchas veces  "innecesarias", quizás dejan los programas más legibles y claros. Y la legibilidad de un programa es  lo más valioso del mundo para un programador (profesionalmente hablando), sobre todo si se da  el caso (inevitable el 99,999...% de las ocasiones) que se tenga que modificar un programa para  dotarle de más funcionalidades, facilitar su manejo, etc. En la mayoría de ejemplos que encontrará  en este manual verá que se utilizan variables preferentemente. Aunque muchas veces su función  sea simplemente recoger datos de las celdas para operarlas y dejarlas en otras celdas y,  consecuentemente, aumente el número de operaciones, creemos que con ello se gana en  legibilidad y flexibilidad.    Ejemplo 8.    Macro que compara los valores de las casillas A1 y A2 de la hoja activa. Si son iguales pone el color  de la fuente de ambas en azul.    Sub Condicional2()    If ActiveSheet.Range("A1").Value = ActiveSheet.Range("A2").Value Then    ActiveSheet.Range("A1").Font.Color = RGB(0, 0, 255)  ActiveSheet.Range("A2").Font.Color = RGB(0, 0, 255)    End If    End Sub   

Estructura If..Else    Esta estructura se utiliza cuando se requiere una respuesta alternativa a una condición. Su  estructura es la siguiente.    Si Condición Entonces    Senténcia1  Senténcia2  .  .  SenténciaN   

Sino    Senténcia1  Senténcia2  .  .  SenténciaN    Fin Si  Observe que, si se cumple la condición, se ejecuta el bloque de sentencias delimitado por Si  Condición Entonces y Si no se cumple la condición se ejecuta el bloque delimitado por Sino y Fin  Si.     En Visual Basic la instrucción Si Condición Entonces ... Sino ... Fin Si se expresa con las  instrucciones siguientes.    If Condición Then    Senténcia1  Senténcia2  .  .  SenténciaN    Else    Senténcia1  Senténcia2  .  .  SenténciaN    End If    Ejemplo 9.    Entrar una cantidad que representa el precio de algo por el teclado con la instrucción InputBox y  guardarlo en la celda A1 de la hoja activa. Si el valor entrado desde el teclado (y guardado en A1)  es superior a 1000, se aplica un descuento del 10% si no se aplica un descuento del 5%, el  descuento se guarda en la casilla A2 de la hoja activa. Colocar en A3, el total descuento y en A4 el  total menos el descuento.    Sub Condicional_Else()    Dim Precio As Single  Dim Descuento As Single    Precio = 0 

  Precio = Val(InputBox("Entrar el precio", "Entrar"))    ' Si el valor de la variable precio es mayor que 1000, entonces, aplicar descuento del 10%    If Precio > 1000 Then    Descuento = Precio * (10 / 100)    ActiveSheet.Range("A2").Value = 0,1    Else ' Sino Aplicar descuento del 5%    Descuento = Precio * (5 / 100)    ActiveSheet.Range("A2").Value = 0,05    End If    ActiveSheet.Range("A1").Value = Precio  ActiveSheet.Range("A3").Value = Descuento  ActiveSheet.Range("A4").Value = Precio ‐ Descuento    End Sub    Ejemplo 10.    Restar los valores de las casillas A1 y A2. Guardar el resultado en A3. Si el resultado es positivo o 0,  poner la fuente de A3 en azul, sino ponerla en rojo.    Sub Condicional_Else2()    ActiveSheet.Range("A3").Value = AvtiveSheet.Range("A1").Value ‐ _  ActiveSheet.Range("A2").Value    If ActiveSheet("A3").Value  ActiveSheet.Range("A2").Value Then    ActiveSheet.Range("A3").Value = "A1 mayor que A2"    Else  ActiveSheet.Range("A3").Value = "A2 mayor que A1"    End If    End If    End Sub    Observe que la segunda estructura If..Else..End If queda dentro del Else de la primera estructura.  Esta es una regla general, cuando pone un End If, este cierra siempre el último If ( o Else) abierto.  ∙  Operadores lógicos.    Estos operadores se utilizan cuando se necesitan evaluar dos o más condiciones para decidir si se  ejecutan o no determinadas acciones.   

Operador Lógico And (Y).    Utilizaremos este operador cuando sea preciso que para ejecutar un bloque de instrucciones se  cumpla más de una condición. Observe que deberán cumplirse todas las condiciones. Vea el  ejemplo siguiente.    Ejemplo 12.   

Entrar el Nombre, la cantidad y el precio de un producto desde el teclado y guardarlos  respectivamente en  A1, A2 y A3. Calcular el total y guardarlo en A4. Si el total es superior a 10.000 y el nombre del  producto es "Patatas", pedir un descuento, calcularlo el total descuento y guardarlo en A5, luego  restar el descuento del total y guardarlo en A6.    Sub Ejemplo_12()    Dim Producto As String  Dim Cantidad As Integer  Dim Precio As Single  Dim Total As Single  Dim Descuento As Single  Dim Total_Descuento As Single    Precio = 0  Producto = InputBox("Entrar Nombre del Producto","Entrar")  Precio = Val(InputBox("Entrar el precio", "Entrar"))  Precio = Val(InputBox("Entrar la cantidad", "Entrar"))  Total = Precio * Cantidad    ActiveSheet.Range("A1").Value = Producto  ActiveSheet.Range("A2").Value = Precio  ActiveSheet.Range("A3").Value = Cantidad  ActiveSheet.Range("A4").Value = Total    ' Si total mayor que 10.000 y el producto es Patatas, aplicar descuento.    If Total > 10000 And Producto = "Patatas" Then    Descuento = Val(InputBox("Entrar Descuento", "Entrar"))  Total_Descuento = Total * (Descuento / 100)  Total = Total ‐ Total_Descuento  ActiveSheet.Range("A5").Value = Total_Descuento  ActiveSheet.Range("A6").Value = Total    End If    End Sub    Observe que para que se ejecute el bloque de instrucciones entre If.. End If deben cumplirse las  dos condiciones que se evalúan, si falla cualquiera de las dos (o las dos a la vez), no se ejecuta  dicho bloque.           

Operador Lógico Or (O).    Utilizaremos este operador cuando sea preciso que para ejecutar un bloque de instrucciones se  cumpla alguna de una serie de condiciones. Observe que sólo es necesario que se cumpla alguna  de las condiciones que se evalúan. Vea el ejemplo siguiente.    Ejemplo 13.    Entrar el Nombre, la cantidad y e l precio de un producto desde el teclado y guardarlos  respectivamente en A1, A2 y A3. Calcular el total y guardarlo en A4. Si el total es superior a 10.000  o el nombre del producto es "Patatas", pedir un descuento, calcularlo el total descuento y  guardarlo en A5, luego restar el descuento del total y guardarlo en A6.        Sub Ejemplo_13()    Dim Producto As String  Dim Cantidad As Integer  Dim Precio As Single  Dim Total As Single  Dim Descuento As Single  Dim Total_Descuento As Single    Precio = 0  Producto = InputBox("Entrar Nombre del Producto","Entrar")  Precio = Val(InputBox("Entrar el precio", "Entrar"))  Precio = Val(InputBox("Entrar la cantidad", "Entrar"))  Total = Precio * Cantidad    ActiveSheet.Range("A1").Value = Producto  ActiveSheet.Range("A2").Value = Precio  ActiveSheet.Range("A3").Value = Cantidad  ActiveSheet.Range("A4").Value = Total    ' Si total mayor que 10.000 o el producto es Patatas, aplicar descuento.    If Total > 10000 Or Producto = "Patatas" Then    Descuento = Val(InputBox("Entrar Descuento", "Ent rar"))  Total_Descuento = Total * (Descuento / 100)  Total = Total ‐ Total_Descuento  ActiveSheet.Range("A5").Value = Total_Descuento  ActiveSheet.Range("A6").Value = Total    End If    End Sub 

  Observe que para que se ejecute el bloque de instrucciones entre If.. End If sólo es necesario que  se cumpla alguna de las dos condiciones que se evalúan (o las dos a la vez). Sólo cuando no se  cumple ninguna de las dos no se ejecutan las instrucciones del bloque.   

∙  Estructura Select Case.    En ocasiones se dará el caso que en función del valor o rango de valores que pueda tener una  variable, una casilla, una expresión, etc. deberán llevarse a cabo diferentes acciones o grupos de  acciones. Vea el ejemplo siguiente.    Ejemplo 15.    Macro que suma, resta, multiplica o divide los valores de las casillas A1 y A2 dependiendo de si B1  contiene el signo +, ‐, x, :. El resultado lo deja en A3. Si en B1 no hay ninguno de los signos  anteriores en A3 debe dejarse un 0.    Sub Ejemplo_15()    Dim Signo As String  Dim Valor1 As Integer, Valor2 As Integer, Total As Integer    Valor1 = ActiveSheet.Range("A1").Value  Valor2 = ActiveSheet.Range("A2").Value  Signo = ActiveSheet.Range("B1").Value  Total=0    If Signo = "+" Then  Total = Valor1 + Valor2  End if    If Signo = "‐" Then  Total = Valor1 ‐ Valor2  End if    If Signo = "x" Then  Total = Valor1 * Valor2  End if    If Signo = ":" Then  Total = Valor1 / Valor2  End if    ActiveCell.Range("A3").Value = Total  End Sub   

Observe que en el ejemplo anterior todas las instrucciones if evalúan la misma variable. El  programa funciona correctamente pero para estos casos es mejor utilizar la instrucción Select Ca  se, el motivo principal es por legibilidad y elegancia. Select Case tiene la sintaxis siguiente,    Select Case Expresión    Case valores :    Instrucciones.    Case valores :  Instrucciones.  .  .  Case valores:    Instrucciones.    Case Else    Instrucciones en caso que no sean ninguno de los valores anteriores.    End Select    Vea el ejemplo anterior solucionado con esta estructura.    Ejemplo 16.    Sub Ejemplo_16()    Dim Signo As String  Dim Valor1 As Integer, Valor2 As Integer, Total As Integer    Valor1 = ActiveSheet.Range("A1").Value  Valor2 = ActiveSheet.Range("A2").Value  Signo = ActiveSheet.Range("A3").Value    Select Case signo    Case "+"    Total = Valor1 + Valor2    Case "‐"    Total = Valor1 ‐ Valor2    Case "x" 

  Total = Valor1 * Valor2    Case ":"    Total = Valor1 / Valor2    Case Else    Total = 0    End Select    ActiveCell.Range("A3").Value = Total    End Sub    Vea el ejemplo siguiente donde cada sentencia Case evalúa un rango de valores.    Ejemplo 17.    Programa que pide tres notas de un alumno mediante la función InputBox. Las notas van a parar    respectivamente a las casillas A1, A2 y A3 de la hoja activa. El programa calcula la media y la deja  en A4.     Si la media está entre 0 y 2 deja en A5 el mensaje "Muy deficiente", si la nota es 3 deja en A5 el  mensaje "Deficiente", si la nota es 4 deja "Insuficiente", si es 5 "Suficiente", si es 6 "Bien", si está  entre 7 y 8 deja "Notable", si es mayor que 8 deja "Sobresaliente".    Sub Ejemplo_17()    Dim Nota1 As Integer, Nota2 As Integer, Nota3 As Integer  Dim Media As Single    Nota1 = Val(InputBox("Entrar Nota primera evaluación", "Nota"))  Nota2 = Val(InputBox("Entrar Nota Segunda evaluación", "Nota"))  Nota3 = Val(InputBox("Entrar Nota Tercera evaluación", "Nota"))    Media = (Nota1 + Nota2 + Nota3) / 3    ActiveSheet.Range("A1").Value = Nota1  ActiveSheet.Range("A2").Value = Nota2  ActiveSheet.Range("A3").Value = Nota3  ActiveSheet.Range("A4").Value = Media    Select Case Media    Case 0 To 2 

  ActiveSheet.Range("A5").Value = "Muy deficiente"    Case 3    ActiveSheet.Range("A5").Value = "Deficiente"    Case 4    ActiveSheet.Range("A5").Value = "Insuficiente"    Case 5    ActiveSheet.Range("A5").Value = "Suficiente"    Case 6    ActiveSheet.Range("A5").Value = "Bien"    Case 7 To 8    ActiveSheet.Range("A5").Value = "Notable"    Case >8    ActiveSheet.Range("A5").Value = "Sobresaliente"    End Select    End Sub     

∙  La función MsgBox.    Esta función muestra un mensaje en un cuadro de diálogo hasta que el usuario pulse un botón. La  función devuelve un dato tipo Integer en función del botón pulsado por el usuario. A la hora de  invocar está función, se permiten diferentes tipos de botones.   

Sintáxis de MsgBox.    MsgBox( Mensaje, Botones, Título, Archivo de ayuda, contexto)    Mensaje: Obligatorio, es el mensaje que se muestra dentro del cuadro de diálogo.    Botones: Opcional. Es un número o una suma de números o constantes (vea tabla Valores para  botones e Iconos), que sirve para mostrar determinados botones e iconos dentro del cuadro de  diálogo. Si se omite este argumento asume valor 0 que corresponde a un único Botón OK. 

  Título: Opcional. Es el texto que se mostrará en la barra del título del cuadro de diálogo.    Archivo de Ayuda: Opcional. Si ha asignado un texto de ayuda al cuadro de diálogo, aquí debe  especificar el nombre del archivo de ayuda donde está el texto.    Context: Opcional. Es el número que sirve para identificar el texto al tema de ayuda  correspondiente que estará contenido en el archivo especificado en el parámetro Archivo de  Ayuda.       

Tabla para botones e iconos del cuadro MsgBox. (Tabla copiada del archivo  de ayuda de  Microsoft Excel).    Constante  Valor  Descripción   VbOKOnly    0   Muestra solamente el botón Aceptar.     VbOKCancel    1   Muestra los botones Aceptar y Cancelar.     VbAbortRetryIgnore    2   Muestra los botones Anular, Reintentar e Ignorar.     VbYesNoCancel    3   Muestra los botones Sí, No y Cancelar.     VbYesNo    4   Muestra los botones Sí y No.     VbRetryCancel    5   Muestra los botones Reintentar y Cancelar.     VbCritical    16   Muestra el icono de mensaje crítico.     VbQuestion    32   Muestra el icono de pregunta de advertencia.     VbExclamation    48   Muestra el icono de mensaje de advertencia.     VbInformation    64   Muestra el icono de mensaje de información.     VbDefaultButton1    0   El primer botón es el predeterminado.     VbDefaultButton2    256   El segundo botón es el predeterminado.     VbDefaultButton3    512   El tercer botón es el predeterminado.     VbDefaultButton4    768   El cuarto botón es el predeterminado.     VbApplicationModal    0   Aplicación modal   VbSystemModal    4096   Sistema modal    El primer grupo de valores (0 a 5) describe el número y el tipo de los botones mostrados en el  cuadro de diálogo; el segundo grupo (16, 32, 48, 64) describe el estilo del icono, el tercer grupo (0,  256, 512) determina el botón predeterminado y el cuarto grupo (0, 4096) determina la modalidad  del cuadro de mensajes. Cuando se suman números para obtener el valor final del argumento  buttons, se utiliza solamente un número de cada grupo.    Nota Estas constantes las especifica Visual Basic for Applications. Por tanto, el nombre de las  mismas puede utilizarse en cualquier lugar del código en vez de sus valores reales.   

Los valores que puede devolver la función msgbox en función del botón que pulse el usuario se  muestran en la tabla siguiente.    Tabla de valores que puede devolver MsgBox. (Tabla copiada del archivo de ayuda de Microsoft  Visual Basic para aplicaciones).    Constante  Valor  Descripción  VbOK     1     Aceptar     VbCancel     2     Cancelar     VbAbort     3     Anular     VbRetry     4     Reintentar    VbIgnore     5     Ignorar     VbYes     6     Sí     VbNo     7     No       

Ejemplos de MsgBox.    Sub Tal()  .  .  ' El cuadro Muestra los botones Si y No y un icono en forma de interrogante. Cuando se pulsa  ' un botón, el valor lo recoge la variable X. En este caso los valores devueltos pueden ser 6 o 7  ' que corresponden respectivamente a las constantes VbYes y VbNo, observe la instrucción If de  'después.    X = MsgBox("Desea Continuar", vbYesNo + vbQuestion, "Opción",,)    ' Se ha pulsado sobre botón Si    If X = vbYes Then  .....  Else ' Se ha pulsado sobre botón No  .....  End If  .  .  End Sub    Algunas veces puede que le interese simplemente desplegar un cuadro MsgBox para mostrar un  mensaje al usuario sin que se requiera recoger ningún valor. En este caso puede optar por la forma  siguiente:    MsgBox Prompt:="Hola usuaria, Ha acabado el proceso", Buttons:=VbOkOnLy _  Title:="Mensaje"   

Lo que no puede hacer porque Visual Basic daría error es poner la primera forma sin igualarla a  ninguna variable. Por ejemplo, la expresión siguiente es incorrecta:    MsgBox ("Hola usuario, Ha acabado el proceso", VbOkOnly, "Mensaje")    Sería correcto poner:    X= MsgBox ("Hola usuario, Ha acabado el proceso", VbOkOnly, "Mensaje")    En este caso, aunque X reciba un valor, luego no se utiliza para nada, es decir simplemente se  pone para que Visual Basic dé error.    ∙  La instrucción With.    Suponemos que llegado a este punto le parecerá engorroso tener que referirse a los objetos  siguiendo toda o casi toda la jerarquía. Ya hemos indicado que es mejor hacerlo de esta manera  porque el programa gana en claridad y elegancia y, consecuentemente, el programador gana  tiempo a la hora de hacer modificaciones o actualizaciones. La sentencia With le ayudará a tener  que escribir menos código sin que por esto el programa pierda en claridad. Concretamente esta  función sirve para ejecutar una serie de acciones sobre un mismo Objeto.     Su sintaxis es la siguiente:    With Objeto    Instrucciones    End With    Repetiremos el ejemplo 13 utilizando esta sentencia. Observe como con With se hace referencia al  objeto ActiveSheet.    Ejemplo 19.    Entrar el Nombre, la cantidad y el precio de un producto desde el teclado y guardarlos  respectivamente en A1, A2 y A3. Calcular el total y guardarlo en A4. Si el total es superior a 10.000  o el nombre del producto es "Patatas", pedir un descuento, calcularlo el total descuento y  guardarlo en A5, luego restar el descuento del total y guardarlo en A6.    Sub Ejemplo_19()    Dim Producto As String  Dim Cantidad As Integer  Dim Precio As Single  Dim Total As Single  Dim Descuento As Single  Dim Total_Descuento As Single   

Precio = 0  Producto = InputBox("Entrar Nombre del Producto","Entrar")  Precio = Val(InputBox("Entrar el precio", "Entrar"))  Precio = Val(InputBox("Entrar la cantidad", "Entrar"))  Total = Precio * Cantidad    With ActiveSheet    .Range("A1").Value = Producto  .Range("A2").Value = Precio  .Range("A3").Value = Cantidad  .Range("A4").Value = Total    End With      ' Si total mayor que 10.000 o el producto es Patatas, aplicar descuento.    If Total > 10000 Or Producto = "Patatas" Then    Descuento = Val(InputBox("Entrar Descuento", "Entrar"))  Total_Descuento = Total * (Descuento / 100)  Total = Total ‐ Total_Descuento    With ActiveSheet    .Range("A5").Value = Total_Descuento  .Range("A6").Value = Total    End With    End If    End Sub   

Estructuras Repetitivas.    Este tipo de estructuras permiten ejecutar más de una vez un mismo bloque de sentencias.    Ejemplo 20.    Supongamos que tenemos que hacer un programa para entrar las notas de una clase de 5 alumnos  que se guardaran respectivamente en las celdas de A1 a A5 de la hoja activa. Después hacer la  media que se guardará en A6. Con las estructuras vistas hasta ahora, podríamos hacer:    Sub Ejemplo_20 ()    Dim Nota As Integer 

Dim Media As Single    Media = 0  Nota = Val(InputBox("Entrar la 1 Nota : ","Entrar Nota"))  ActiveSheet.Range("A1").Value = Nota  Media = Media + Nota  Nota = Val(InputBox("Entrar la 1 Nota : ","Entrar Nota"))  ActiveSheet.Range("A2").Value = Nota  Media = Media + Nota  Nota = Val(InputBox("Entrar la 1 Nota : ","Entrar Nota"))  ActiveSheet.Range("A3").Value = Nota  Media = Media + Nota  Nota = Val(InputBox("Entrar la 1 Nota : ","Entrar Nota"))  ActiveSheet.Range("A4").Value = Nota  Media = Media + Nota  Nota = Val(InputBox("Entrar la 1 Nota : ","Entrar Nota"))  ActiveSheet.Range("A5").Value = Nota  Media = Media + Nota  Media = Media / 5  ActiveSheet.Range("A6").Value = Media    End Sub    Observe que este programa repite el siguiente bloque de sentencias, 5 veces.    Nota = Val(InputBox("Entrar la 1 Nota : ","Entrar Nota"))    ActiveSheet.Range("A5").Value = Nota    Media = Media + Nota    Para evitar esta tipo de repeticiones de código, los lenguajes de programación incorporan  instrucciones que permiten la repetición de bloques de código.   

∙  Estructura repetitiva Para (for).    Esta estructura sirve para repetir la ejecución de una sentencia o bloque de sentencias, un número  definido de veces. La estructura es la siguiente:    Para var =Valor_Inicial Hasta Valor_Final Paso Incremento Hacer    Inicio    Sentencia 1  Sentencia 2  .  .  Sentencia N 

Fin    Var es una variable que la primera vez que se entra en el bucle se iguala a Valor_Inicial, las  sentencias del bucle se ejecutan hasta que Var llega al Valor_Final, cada vez que se ejecutan el  bloque de instrucciones Var se incrementa según el valor de Incremento.    En Visual Basic para Excel la estructura Para se implementa con la instrucción For ... Next.    For Varible = Valor_Inicial To Valor_Final Step Incremento    Sentencia 1  Sentencia 2  .  .  Sentencia N    Next Variable    * Si el incremento es 1, no hace falta poner Step 1.    Ejemplo 21.    Entrar 10 valores utilizando la función InputBox, sumarlos y guardar el resultado en la casilla A1 de  la hoja activa.    Sub Ejemplo_21()    Dim i As Integer  Dim Total As Integer  Dim Valor As Integer    For i=1 To 10    Valor= Val(InputBox("Entrar un valor","Entrada"))  Total = Total + Valor    Next i    ActiveCell.Range("A1").Value = Total    End Sub               

Recorrer casillas de una hoja de cálculo.    Una operación bastante habitual cuando se trabaja con Excel es el recorrido de rangos de casillas  para llenarlas con valores, mirar su contenido, etc. Las estructuras repetitivas son imprescindibles  para recorrer grupos de celdas o rangos. Vea los siguientes ejemplos para ver ejemplos de  utilización de estructuras repetitivas para recorrer rangos de casillas, observe la utilización de las  propiedades Cells y Offset.         

Propiedad Cells.    Ya conoce esta propiedad, sirve para referenciar una celda o un rango de celdas s egún  coordenadas de fila y columna.      Ejemplo 22    Llenar el rango de las casillas A1..A5 con valores pares consecutivos empezando por el 2.    Sub Ejemplo_22()    Dim Fila As Integer  Dim i As Integer  Fila = 1  For i=2 To 10 Step 2  ActiveSheet.Cells(Fila,1).Value = i  Fila = Fila+1    Next i    End Sub    Ejemplo 23.    Llenar un rango de filas, empezando por una celda, que se debe especificar desde teclado, con una  serie de 10 valores correlativos (comenzando por el 1).    Sub Ejemplo_23()    Dim Casilla_Inicial As String  Dim i As Integer  Dim Fila As Integer, Columna As Integer    Casilla_Inicial = InputBox("Introducir la casilla Inicial : ", "Casilla Inicial")  ActiveSheet.Range(Casilla_Inicial).Activate 

  ‘ Tomar el valor de fila de la celda activa sobre la variable Fila    Fila = ActiveCell.Row    ‘ Tomar el valor de columna de la celda activa sobre la variable Fila    Columna = ActiveCell.Column    For i = 1 To 10    ActiveSheet.Cells(Fila, Columna).Value = i  Fila = Fila + 1    Next i    End Sub        ∙  Estructura repetitiva Do While..Loop (Hacer Mientras).      La estructura repetitiva for se adapta perfectamente a aquellas situaciones en que se sabe  previamente el número de veces que se ha de repetir un proceso, entrar veinte valores, recorrer  cincuenta celdas, etc.     Pero hay ocasiones o casos en los que no se sabe previamente el número de veces que se debe  repetir un proceso.     Por ejemplo, suponga que ha de recorrer un rango de filas en los que no se sabe cuantos valores  habrá (esto es, cuantas filas llenas habrá), en ocasiones puede que hayan veinte, en ocasiones  treinta, en ocasiones ninguna, etc. Para estos casos la estructura for no es adecuada y deberemos  recurrir a la sentencia Do While..Loop en alguna de sus formas. Esta estructura repetitiva está  controlada por una o varias condiciones, la repetición del bloque de sentencias dependerá de si se  va cumpliendo la condición o condiciones.    Hacer Mientras (se cumpla la condición)    Sentencia1  Sentencia2  .  .  Sentencia N    Fin Hacer Mientras    En Visual Basic 

  Do While (se cumpla la condición)  Sentencia1  Sentencia2  .  .  Sentencia N  Loop    ** Los ejemplos que veremos a continuación sobre la instrucción Do While..Loop se harán sobre  una base de datos.     Una base de datos en Excel es simplemente un rango de celdas en que cada fila representa un  registro y cada columna un campo de registro, la primera fila es la que da nombre a los campos.     Para nuestra base de datos utilizaremos los campos siguientes, Nombre, Ciudad, Edad, Fecha.     Ponga estos títulos en el rango A1:D1 de la Hoja1 (En A1 ponga Nombre, en B1 ponga Ciudad, en  C1 ponga Edad y en D1 Fecha), observe que los datos se empezarán a entrar a partir de A2.    Ejemplo 27.    Programa para entrar registros en la base de datos. Cada campo se entra con InputBox. El  programa va pidiendo datos mientras se entre un valor en el InputBox correspondiente al nombre,  es decir cuando al preguntar el nombre no se entre ningún valor, terminará la ejecución del  bloque encerrado entre Do While...Loop.     Observe la utilización de la propiedad Offset para colocar los datos en las celdas correspondientes.    Sub Ejemplo_27()    Dim Nombre As String  Dim Ciudad As String  Dim Edad As Integer  Dim fecha As Date    ‘ Activar hoja1    WorkSheets("Hoja1").Activate    ‘ Activar casilla A2    ActiveSheet.Range("A2").Activate    Nombre = InputBox("Entre el Nombre (Return para Terminar) : ", "Nombre")    ‘ Mientras la variable Nombre sea diferente a cadena vacía   

Do While Nombre  ""    Ciudad = InputBox("Entre la Ciudad : ", "Ciudad")  Edad = Val(InputBox("Entre la Edad : ", "Edad"))  Fecha=Cdate(InputBox("Entra la Fecha : ", "Fecha"))    ‘ Copiar los datos en las casillas correspondientes    With ActiveCell  .Value = Nombre  .Offset(0,1).Value = Ciudad  .Offset(0,2).Value = Edad  .Offset(0,3).Value = fecha    End With    ‘Hacer activa la celda de la fila siguiente a la actual    ActiveCell.Offset(1,0).Activate  Nombre = InputBox("Entre el Nombre (Return para Terminar) : ", "Nombre")  Loop  End Sub      Ejemplo 28.    Preste especial atención a este ejemplo ya que seguro que el código que viene a continuación lo  utilizará en muchas ocasiones.     Antes que nada observe el ejemplo anterior, fíjese en que siempre empezamos a llenar el rango de  la hoja a partir de la celda A2, esto tiene una nefasta consecuencia, la segunda vez que ejecute la  macro machacará los datos de A2:D2 y si continua ejecutando machacará los datos de los rangos  siguientes.     Una solución sería observar cual es la casilla vacía siguiente y cambiar en la instrucción  ActiveSheet.Range("A2").Activate , la referencia A2 por la que corresponde a la primera casilla  vacía de la columna A. El código que le mostramos a continuación hará esto por nosotros, es decir  recorrerá una fila de celdas a partir de A1 hasta encontrar una vacía y dejará a esta como celda  activa para que la entrada de datos comience a partir de ella.    Sub Ejemplo_28()  .  .  ‘ Activar hoja1    WorkSheets("Hoja1").Activate  ‘ Activar casilla A2   

ActiveSheet.Range("A1").Activate  ‘ Mientras la celda activa no esté vacía    Do While Not IsEmpty(ActiveCell)    ‘ Hacer activa la celda situada una fila por debajo de la actual    ActiveCell.Offset(1,0).Activate    Loop  .  .  End Sub    Ejemplo 29.    Es la unión de los dos programas anteriores. Es decir habrá un bucle Do While que buscará la  primera casilla vacía de la base de datos y otro para pedir los valores de los campos hasta que se  pulse Enter en Nombre.    Sub Ejemplo_28()    Dim Nombre As String  Dim Ciudad As String  Dim Edad As Integer  Dim fecha As Date    WorkSheets("Hoja1").Activate  ActiveSheet.Range("A1").Activate  ‘ Buscar la primera celda vacía de la columna A y convertirla en activa    Do While Not IsEmpty(ActiveCell)    ActiveCell.Offset(1,0).Activate    Loop    Nombre = InputBox("Entre el Nombre (Return para Terminar) : ", "Nombre")  ‘ Mientras la variable Nombre sea diferente a cadena vacía    Do While Nombre  ""    Ciudad = InputBox("Entre la Ciudad : ", "Ciudad")  Edad = Val(InputBox("Entre la Edad : ", "Edad"))  Fecha=Cdate(InputBox("Entra la Fecha : ", "Fecha"))    With ActiveCell   

.Value = Nombre  .Offset(0,1).Value = Ciudad  .Offset(0,2).Value = Edad  .Offset(0,3).value = fecha    End With    ActiveCell.Offset(1,0).Activate  Nombre = InputBox("Entre el Nombre (Return para Terminar) : ", "Nombre")    Loop    End Sub    Cuando se tienen que entrar desde el teclado conjuntos de valores, algunos programadores y  usuarios prefieren la fórmula de que el programa pregunte si se desean entrar más datos, la típica  pregunta ¿Desea Introducir más datos ?, si el usuario contesta Sí, el programa vuelve a ejecutar las  instrucciones correspondientes a la entrada de datos, si contesta que no se finaliza el proceso,  observe como quedaría nuestro bucle de entrada de datos con este sistema.    Mas_datos = vbYes    Do While Mas_Datos = vbYes    Nombre = InputBox("Entre el Nombre (Return para Terminar) : ", "Nombre")  Ciudad = InputBox("Entre la Ciudad : ", "Ciudad")  Edad = Val(InputBox("Entre la Edad : ", "Edad"))  Fecha=Cdate(InputBox("Entra la Fecha : ", "Fecha"))    With ActiveCell    .Value = Nombre  .Offset(0,1).Value = Ciudad  .Offset(0,2).Value = Edad  .Offset(0,3).value = fecha    End With    ActiveCell.Offset(1,0).Activate  ‘ Preguntar al usuario si desea entrar otro registro.  Mas_datos = MsgBox("Otro registro ?", vbYesNo+vbQuestion,"Entrada de datos")    Loop    ** Observe que es necesaria la línea anterior al bucle Mas_datos = vbYes, para que cuando se  evalúe la condición por vez primera esta se cumpla y se ejecuten las sentencias de dentro del  bucle, Mas_datos es una variable de tipo Integer. Vea la sección siguiente donde se estudia una  variante de la estructura Do While que es más adecuada para este tipo de situaciones. 

 

∙  Estructura Do..Loop While.    El funcionamiento de esta estructura repetitiva es similar a la anterior salvo que la condición se  evalúa al final, la inmediata consecuencia de esto es que las instrucciones del cuerpo del bucle se  ejecutaran al menos una vez. Observe que para nuestra estructura de entrada de datos vista en el  último apartado de la sección anterior esta estructura es más conveniente, al menos más  elegante, si vamos a entrar datos, al menos uno entraremos, por tanto las instrucciones del cuerpo  del bucle se deben ejecutar al menos una vez, luego ya decidiremos si se repiten o no.    Do    Nombre = InputBox("Entre el Nombre (Return para Terminar) : ", "Nombre")  Ciudad = InputBox("Entre la Ciudad : ", "Ciudad")  Edad = Val(InputBox("Entre la Edad : ", "Edad"))  Fecha=Cdate(InputBox("Entra la Fecha : ", "Fecha"))    With ActiveCell    .Value = Nombre  .Offset(0,1).Value = Ciudad  .Offset(0,2).Value = Edad  .Offset(0,3).value = fecha    End With    ActiveCell.Offset(1,0).Activate  Mas_datos = MsgBox("Otro registro ?", vbYesNo+vbQuestion,"Entrada de datos")  ‘Mientras Mas_Datos = vbYes    Loop While Mas_Datos = vbYes    Observe que en este caso no es necesario la línea Mas_Datos = vbYes antes de Do para forzar la  entrada en el bucle ya que la condición va al final.     

∙  Estructura Do..Loop Until (Hacer.. Hasta que se cumpla la condición).      Es otra estructura que evalúa la condición al final observe que la interpretación es distinta ya que  el bucle se va repitiendo HASTA que se cumple la condición, no MIENTRAS se cumple la condición.     Cuál de los dos utilizar, pues, no se sorprenda, la que entienda mejor o le guste más.  La entrada de datos con este bucle quedaría:    Do   

Nombre = InputBox("Entre el Nombre (Return para Terminar) : ", "Nombre")  Ciudad = InputBox("Entre la Ciudad : ", "Ciudad")  Edad = Val(InputBox("Ent re la Edad : ", "Edad")  Fecha=Cdate("InputBox("Entra la Fecha : ", "Fecha")    With ActiveCell    .Value = Nombre  .Offset(0,1).Value = Ciudad  .Offset(0,2).Value = Edad  .Offset(0,3).value = fecha    End With    ActiveCell.Offset(1,0).Activate  Mas_datos = MsgBox("Otro registro ?", vbYesNo+vbQuestion,"Entrada de datos")  ‘Hasta que Mas_Datos sea igual a vbNo    Loop Until Mas_Datos=vbNo     

∙  Estructura For Each.    Este bucle se utiliza básicamente para ejecutar un grupo de sentencias con los elementos de una  colección una matriz (pronto veremos los que es). Recuerde que una colección es un conjunto de  objetos, hojas, rangos, etc. Vea el ejemplo siguiente que se utiliza para cambiar los nombres de las  hojas de un libro de trabajo.    Ejemplo 29.    Programa que pregunta el nombre para cada hoja de un libro de trabajo, si no se pone nombre a la  hoja, queda el que tiene.      Sub Ejemplo_29()    Dim Nuevo_Nombre As String  Dim Hoja As WorkSheet    ‘ Para cada hoja del conjunto WorkSheets    For Each Hoja In WorkSheets    Nuevo_Nombre=InputBox("Nombre de la Hoja : " & Hoja.Name,"Nombrar Hojas")    If Nueva_Nombre  "" Then   

Hoja.Name=Nuevo_nombre    End if    Next    End Sub    ** Hoja va referenciando cada una de las hojas del conjunto WorkSheets a cada paso de bucle.    Ejemplo 30.    Entrar valores para las celdas del rango A1:B10 de la hoja Activa.    Sub Ejemplo_30()    Dim R As Range    ‘ Para cada celda del rango A1:B10 de la hoja activa    For Each R in ActiveSheet.Range("A1:B10")    R.Value = InputBox("Entrar valor para la celda " & R.Address, "Entrada de valores")  Next    End Sub        ** Observe que se ha declarado una variable tipo Range, este tipo de datos, como puede imaginar  y ha visto en el ejemplo sirve para guardar Rangos de una o más casillas, estas variables pueden  luego utilizar todas las propiedades y métodos propios de los Objetos Range. Tenga en cuenta que  la asignación de las varaibles que sirven para guardar o referenciar objetos (Range, WorkSheet,  etc.) deben inicializarse muchas veces a través de la instrucción SET , esto se estudiará en otro  capítulo.                           

∙  Insertar funciones de Microsoft Excel desde Visual Basic.    Copie el siguiente procedimiento y ejecútelo. Es un procedimiento que sencillamente va pidiendo  números y los va colocando en las celdas de la columna A partir de A1, al final coloca la función  =SUMA para sumar los valores introducidos y la función =PROMEDIO para hacer el promedio de  los mismos valores.    Sub Sumar()    Dim Valor As Integer  Dim Casilla_Inicial As String  Dim Casilla_Final As String    ' Hacer activa la casilla A1 de la hoja activa  ActiveSheet.Range("A1").Activate    Do  ' Entrar un valor y convertirlo a numérico  Valor = Val(InputBox("Entrar un valor", "Entrada"))  ' Si el valor es distinto de 0    If Valor  0 Then  ' Guardar el valor en la casilla activa  ActiveCell.Value = Valor  ' Hacer activa la casilla de la fila siguiente  ActiveCell.Offset(1, 0).Activate    End If    Loop Until Valor = 0    ' Establecer la casilla inicial del rango a sumar  Casilla_Inicial = "A1"  ' Establecer la casilla final del rango a sumar.  ' Coger la dirección de la casilla activa, la última  Casilla_Final = ActiveCell.Address  ActiveCell.Offset(1, 0).Activate  ' Poner en la casilla activa la función SUMA  ActiveCell.Formula = "=Suma(" & Casilla_Inicial & ":" & Casilla_Final & ")"  ActiveCell.Offset(1, 0).Activate  ' Poner en la casilla activa la función promedio  ActiveCell.Formula = "=Promedio(" & Casilla_Inicial & ":" & Casilla_Final & ")"    End Sub         

Una vez haya ejecutado la macro, observe que en las celdas donde se han colocado  respectivamente las funciones =SUMA, =PROMEDIO aparece ¿NOMBRE? (es posible que aparezca  ####, en ese caso amplíe la columna), esto significa que Excel no reconoce el nombre de la  función, que no existe. Sin embargo, estas funciones si existen y funcionan perfectamente cuando  se teclean directamente sobre la hoja de cálculo, se preguntará el porqué cuando se colocan  desde una macro no funcionan. Pues resulta que para que cualquier función de Excel insertada  desde una macro NO de error debe ponerse con su nombre en inglés, la traducción se hace luego  de forma automática. Es decir en la macro debe ponerla en inglés y luego cuando esta se inserte  en la hoja aparecerá con su nomenclatura en el idioma que corresponda.    Modifique el procedimiento del ejemplo y en lugar de poner    ActiveCell.Formula = "=Suma(" & Casilla_Inicial & ":" & Casilla_Final & ")"    Ponga    ActiveCell.Formula = "=Sum(" & Casilla_Inicial & ":" & Casilla_Final & ")"    Y ahora, en lugar de    ActiveCell.Formula = "=Promedio(" & Casilla_Inicial & ":" & Casilla_Final & ")"    Ponga    ActiveCell.Formula = "=Average(" & Casilla_Inicial & ":" & Casilla_Final & ")"    Ejecute la macro y compruebe que ahora todo funciona correctamente. Observe que en la hoja,  las funciones se han insertado con su nombre correcto según el idioma, es decir SUMA y   PROMEDIO.    De esta forma damos por terminado el módulo de Macros, que simplemente es una introducción  al tema.    El estudiante puede desarrollarse mucho más, este manual pretende realizar un bosquejo del  tema, e iniciar al alumno a la programación en lenguaje VBA.                           

   

Repartidos    Prácticos         

                     

 

EJERCICIO 1 – MODULO: FUNCIONES AVANZADAS Se presenta a continuación una tabla conteniendo las exportaciones de carne llevadas a cabo por nuestro país en el año 2000 al resto del mundo. Exportaciones realizadas en el año 2000 en millones de dólares País de origen

Mes

Japón Inglaterra China Inglaterra China Holanda EEUU Bélgica EEUU Grecia Francia Inglaterra Holanda Grecia China Bélgica EEUU Japón

Febrero Noviembre Setiembre Enero Junio Febrero Diciembre Enero Setiembre Mayo Mayo Julio Julio Diciembre Marzo Junio Mayo Febrero

EEUU

Julio

Importe de exportación 26 67 90 120 140 150 159 200 200 200 201 240 260 300 320 690 840 980 1200

Se pide: 1. Copie la siguiente tabla en un libro cuyo nombre sea exportaciones 2. Ingrese nuevos registros a través de la opción Formularios que se ubican en el menú de Datos. 3. Ordene la tabla en función del país. 4. Obtenga la máxima y la mínima exportación realizada por Uruguay. A continuación, mediante BDEXTRAER, obtenga los nombres de los países destinos de dichas ventas. 5. Calcule el número de exportaciones hechas a EEUU, mediante BDCONTARA y CONTAR.SI 6. Calcule el importe total que se le vendió a ese país mediante las funciones BDSUMA y SUMAR.SI. 7. La máxima, la mínima y el promedio de las ventas hechas a EEUU. 8. Genere a través de una regla de validación una lista desplegable con todos los países existentes en la BD. Luego Obtenga el total vendido al país que UD seleccione en la lista. El fin de esto es que si nosotros cambiamos el país, obtengamos automáticamente el importe que a él se le vendió.

EJERCICIO 2 – MODULO: FUNCIONES AVANZADAS Se presenta a continuación una tabla con las notas obtenidas por 10 alumnos del curso de Excel avanzado. Se pide: 1. Aplique un color de fondo rojo y una fuente de tipo negrita y color blanca, a aquellas celdas que presenten valores inferiores a 70 (formato condicional). 2. Obtenga el total y el promedio de puntos obtenidos por alumno. 3. En la columna de resultados deberá aparecer las leyendas Aprobado, Prueba parcial o Examen total según los criterios de aprobación del curso. 4. Aplique nuevamente un formato condicional, tal que si el resultado es Aprobado, este aparezca con un color de fuente verde, si es Prueba parcial, sea azul y si es Examen total sea rojo. 5. Genere un cuadro de consulta como el siguiente tal que el al digitar la cédula de un estudiante aparezca su promedio y su resultado.

Cedula

T. Macros Dinámicas F. Avanzadas

Access

1.269.487-6 1.396.285-4 2.123.494-4 2.456.987-7 3.798.637-1 4.734.963-4 4.869.741-8 5.681.627-3 6.897.169-6

75 46 73 50 69 79 98 37 82

78 91 57 45 87 96 99 45 72

85 70 97 87 67 83 79 85 89

97 77 81 65 91 95 84 90 94

6.943.521-1

90

87

99

100

Ingresar CI

Total

Promedio Resultado

Promedio

Resultado

EJERCICIO 3 – MODULO: FUNCIONES AVANZADAS

En la planilla que se presenta a continuación tenemos las distintas etapas de una competencia entre 8 equipos. A medida que se va desarrollando la vamos completando con los puntajes obtenidos por cada equipo. Lo que se busca es que en la tabla adicional se ordenen automáticamente los equipos cada vez que introducimos cambios en la tabla principal. Para eso copie ambas tablas en una hoja nueva y digite como nombre Tabla de posiciones. Para lograr el objetivo Ud. deberá aplicar las funciones Jerarquía y BuscarV.

COMPETENCIA INTERBARRIAL - PUNTOS POR EQUIPO Y POR ETAPA

Equipo Equipo Equipo Equipo Equipo Equipo Equipo Equipo

1 2 3 4 5 6 7 8

Etapa 1 Etapa 2 Etapa 3 Etapa 4 Etapa 5 Etapa 6 Total 7 10 3 1 21 2 1 9 1 13 4 5 1 2 12 4 8 6 4 22 9 9 3 7 28 2 5 10 7 24 3 4 3 4 14 3 7 8 9 27

Etapa 1 Etapa 2 Etapa 3 Etapa 4 Etapa 5 Etapa 6 Total 1 2 3 4 5 6 7 8

EJERCICIO 4 – MODULO: FUNCIONES AVANZADAS Extracto de ventas de la empresa "La traicionera SA" Número de contrato compra Vendedor venta

Auto

Importe en dólares

Cliente

Forma de pago

04/04/2005

1053

Homero Simpson

Honda Civic

16000

N Elizabelar

30 cuotas

08/04/2005

1059

Condorito

Fiat Fiorino

9000

IMM

30 cuotas

16/04/2005

1065

Homero Simpson

Mercedes D420

110000

Stella Donatti

30 cuotas

29/04/2005

1078

Homero Simpson

Ferrari 350

290000

J Bush

30 cuotas

04/04/2005

1054

Condorito

Subaru Impreza

14500

Presidencia de la Rep.

60 cuotas

06/04/2005

1057

Paturzú

Fiat Fiorino

9000

IMM

60 cuotas

11/04/2005

1061

Paturzú

Lamborgini Diablo

230000

T Vazquez

60 cuotas

15/04/2005

1063

Paco Casal

BMW 320

58000

MSP

60 cuotas

23/04/2005

1073

Homero Simpson

Fiat Fitito

1200

MSP

60 cuotas

Fecha

28/04/2005

1076

Paturzú

BMW 320

58000

A. Legarburo

60 cuotas

05/04/2005

1055

Chengue Morales

Lamborgini Diablo

230000

J Battle

90 cuotas

01/04/2005

1051

Condorito

Subaru Impreza

14500

El cuqui

Contado

05/04/2005

1056

Chengue Morales

BMW 320

58000

Sin Escurpulos Ltda.

Contado

06/04/2005

1058

Condorito

Maruti

7500

JM Sanguinetti

Contado

12/04/2005

1062

Condorito

Fiat Fiorino

9000

IMM

Contado

15/04/2005

1064

Chengue Morales

Maruti

7500

La trepadora SA

Contado

18/04/2005

1067

Paturzú

Mercedes D420

110000

J Bush

Contado

19/04/2005

1069

Chengue Morales

Fiat Fitito

1200

La incobrable srl

Contado

19/04/2005

1070

Paco Casal

Lamborgini Diablo

230000

La trepadora SA

Contado

22/04/2005

1072

Chengue Morales

Mercedes D420

110000

M. Gutiérrez

Contado

29/04/2005

1077

Condorito

Subaru Impreza

14500

Cedres

Contado

30/04/2005

1080

Condorito

Honda Civic

16000

IMM

Contado

02/04/2005

1052

Paturzú

Ferrari 350

290000

La trepadora SA

En negociación

08/04/2005

1060

Homero Simpson

Fiat Fitito

1200

Presidencia de la Rep.

En negociación

18/04/2005

1066

Condorito

Subaru Impreza

14500

Recoba

En negociación En negociación

19/04/2005

1068

Paco Casal

BMW 320

58000

Sin Escurpulos Ltda.

23/04/2005

1074

Chengue Morales

Ferrari 350

290000

Loco Abreu

En negociación

22/04/2005

1071

Paco Casal

Subaru Impreza

14500

IMM

Entrega efect+15 cuotas

23/04/2005 30/04/2005

1075 1079

Paco Casal Paturzú

Lamborgini Diablo Fiat Fitito

230000 1200

Presidencia de la Rep. La incobrable srl

Entrega efect+20 cuotas Entrega efect+20 cuotas

Se pide: 1. Copie la siguiente tabla en dos hojas distintas 2. En la primera hoja registre nuevas ventas mediante la opción Formularios del menú de Datos. 3. Ordene la tabla según Fecha de Venta en forma ascendente, el nombre del vendedor de forma ascendente. 4. Ordene nuevamente la BD pero ahora solamente por vendedor en forma descendente. A través de la herramienta de subtotales obtenga: Æ El total de ventas por vendedor Æ La venta más alta y la más baja de cada vendedor Æ El promedio de ventas de cada vendedor Æ El número de ventas de cada vendedor

Saque detalle de manera de visualizar solamente los totales de venta de cada vendedor.

5. En la segunda hoja, mediante Autofiltros visualice: Æ las ventas realizadas por Condorito. Æ las ventas de Condorito hechas a la IMM Æ las ventas realizadas en la segunda quincena del mes de abril. Æ las ventas cuyos importes estén entre los 50000 y los 150000 dólares. Æ Las ventas cuyos importes sean menores a 10000 y mayores a 200000 dólares. Æ las ventas hechas a la IMM o a la Presidencia de la República. Æ las cinco ventas más grandes Æ las cinco ventas más chicas Æ las ventas de los vendedores cuyos nombres comiencen con la letra C. Æ las ventas cuya forma de pago aún esté en negociación

EJERCICIO 5 – MODULO: FUNCIONES AVANZADAS SOLVER Esta planilla ofrece los datos de producción de dos artículos, incluyendo los detalles de las materias primas que los componen. Con estos datos, y basándonos en la tabla auxiliar, podemos calcular el costo de cada artículo. Suponiendo que se puede vender el total de la producción, necesitamos calcular cuánto será lo máximo que se puede vender sin utilizar más materia prima de la que se tiene. Todo esto teniendo en cuenta que el precio final de cada artículo no puede superar los 16.5 pesos y que el margen de ganancia tiene que ser de, por lo menos, el 30 por ciento. Autito a control Tuercas por unidad Tornillos por unidad Arandelas por unidad Plástico por unidad Motor por unidad Cable por unidad Costo por unidad Margen de ganancia Precio unitario final $ Unidades a producir

Trencito eléctrico

4 3 7 0,20 kg 1 0,50 m 11,65 50% 17,48 $ 500

Importe de ventas $ 8.737,50 $ Materias primas utilizadas en Total de tuercas 2000 Total de tornillos 1500 Total de arandelas 3500

Totales

3 5 4 0,30 kg 1 0,70 m 12,18 50% 18,27 500 9.135,00 $ la producción 1500 2500 2000

17.872,50 3500 4000 5500

Existencia Tuercas 1000 Tornillos 1500 Arandelas 3000 Plástico 1000,00 Kg. Cable 750,00 m Motor 600

Costo ( por ud, Kg. o m) $ 0,50 $ 0,75 $ 0,25 $ 1,00 $ 0,90 $ 5,00

EJERCICIO 6 – MODULO: FUNCIONES AVANZADAS BUSCAR OBJETIVO

1. Aplicando Buscar objetivo calcule la cantidad a vender para obtener un importe de ventas de 1200 pesos.

1 2 3

A Precio Cantidad Importe de ventas

B 20 ? =B2*B3

2. Deseamos conocer la mayor cantidad que podemos obtener en préstamos si estamos dispuestos a endeudarnos por 36 meses pagando una cuota máxima de 500 pesos. La tasa de interés del mercado es del 50% anual 3. A B 1 Préstamo ? 2 Tasa anual 50% 3 Nº de cuotas 36 4 Valor de la cuota mensual =PAGO(B2/12;B3;B1) 4. En función de los costos y utilidad del producto determine la cantidad a producir para que el precio unitario sea igual a 15 pesos.

1 2 3 4 5 6

A Cantidad a producir Costos variables Costos fijos Costo total unitario Utilidad unitaria (10%) Precio unitario

B ? 8.5 1200 =B3+B4/B2 =B5*0.1 =B5+B6

EJERCICIOS    ANÁLISIS    DE    INFORMACIÓN   

EJERCICIOS MÓDULO: Análisis de Información Ejercicio 1 Dada la siguiente tabla de datos: Año 2004 2004 2004 2004 2003 2003 2003 2003 2002 2002 2002 2002 2001 2001 2001 2001

Período 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4

Ventas 15458 5487 6587 1587 15874 4358 5743 2543 18045 7845 6784 1254 10548 4587 6879 3597

Se pide:

Mediante Tablas Dinámicas:

1. Obtener total de ventas por año. 2. Obtener total de ventas por año y dentro de cada año, por período. 3. Obtener promedio de ventas por año y por período.

EJERCICICIO 2 Dada la siguiente base de datos: Año 2004 2004 2004 2004 2004 2004 2004 2003 2003 2003 2003 2003 2003 2003 2003 2002 2002 2002 2002 2002 2002 2002 2002 2001 2001 2001 2001 2001 2001 2001

Período 1 1 2 2 3 3 4 4 1 1 2 2 3 3 4 4 1 1 2 2 3 3 4 4 1 1 2 2 3 3

Tipo de cliente Minorista Mayorista Minorista Mayorista Minorista Mayorista Minorista Mayorista Minorista Mayorista Minorista Mayorista Minorista Mayorista Minorista Mayorista Minorista Mayorista Minorista Mayorista Minorista Mayorista Minorista Mayorista Minorista Mayorista Minorista Mayorista Minorista Mayorista

Ventas 12924 2534 4462 1025 5383 1204 745 842 13520 2354 3311 1047 4708 1035 1701 842 13544 4501 6589 1256 5944 840 929 325 7000 3548 3533 1054 5426 1453

Se pide: Mediante Tablas Dinámicas: 1. Obtener total de ventas por período, y por año. Dar la posibilidad de filtrar por cliente dicha información. 2. Obtener gráfico dinámico de dicha información. 3. Visualizar mediante tabla dinámica, porcentaje sobre el total de ventas de cada período y de cada año.

EJERCICIO 3 DADA LA SIGUIENTE BASE DE DATOS: ALUMNO

3.015.665-5 1.967.222-3 2.123.334-1 1.944.314-6 3.054.365-4 1.665.145-0 1.945.328-6 3.412.331-0 4.036.365-6 4.365.441-8 2.966.356-4 1.665.324-3 1.689.365-0 3.554.321-4

SUCURSAL

DOCENTE

CLARIDAD AL EXPLICAR

BUENA DISPOSICIÓN

BUCEO PARQUE BUCEO RIVERA BUCEO RIVERA PARQUE RIVERA RIVERA BUCEO BUCEO PARQUE PARQUE BUCEO

Luis Pedro Pedro Gabriel Luis Gabriel Gabriel Luis Luis Luis Pedro Gabriel Gabriel Luis

2 5 4 1 4 3 5 2 3 1 5 4 3 2

4 5 4 2 3 3 4 1 2 1 4 5 4 1

BUEN USO DEL PIZARRÓN 2 4 5 3 3 4 4 2 2 2 4 4 4 1

CONCEPTO GENERAL DEL DOCENTE 2 5 4 2 3 4 4 2 2 1 4 4 3 1

OBTENER: 1. NOTAS GENERALES de cada DOCENTE y dividido por cada SUCURSAL. 2. Cantidad de encuestas tomadas de cada docente. 3. Máximo Concepto General alcanzado por un docente, detallando cual es el docente y en qué sucursal obtuvo dicha nota. 4. Cantidad de encuestas que se realizaron en cada sucursal. 5. Graficar notas de cada pregunta obtenida por cada docente, y con la posibilidad de desplegar este gráfico por sucursal. EJERCICIO 4 IMPORTAR DESDE LA BASE DE DATOS NEPTUNO (C:\Archivos de Programa\Microsoft Office\Office10\Samples) las tablas de Pedidos y de Clientes. OBTENER: 1. CANTIDAD de pedidos que se tomaron por país. 2. Cantidad de pedidos tomados por empleado y por ciudad del destinatario. 3. Generar un campo calculado para saber cuántos pedidos se realizarían por país si la empresa pensara incrementar su cantidad de pedidos en un 30 %. 4. Visualizar las unidades en existencia de cada producto que nos trae cada proveedor. 5. Promedio de precios unitarios de los productos de cada proveedor. 6. Graficar la cantidad de pedidos que se le hayan entregado al cliente ALFKI y ANTON en las diferentes formas de envio.

EJERCICIO 5 Importar desde MS Access, desde la base de datos NEPTUNO (se encuentra en C:\Archivos de Programas \ Microsoft Office\Office10\Samples) las tablas de productos y categorías. Obtener mediante Tablas Dinámicas: 1. 2. 3. 4.

Cantidad de productos por categoría Promedio de precios unitarios por categoría Productos por categoría, y unidades en existencia Calcular mediante un campo calculado el IVA de todos los precios unitarios, el importe de cada precio con IVA incluido 5. Graficar cantidad de proveedores que existen por categoría de productos 6. Graficar en forma de torta los precios unitarios de los productos que pertenezcan a la categoría Bebidas. EJERCICIO 6 (Datos – Tabla) Se presenta planilla con datos de un artículo que nuestra empresa comercializa y se nos solicita estudiemos la sensibilidad del precio de venta frente a dos variables: el margen de ganancia y la cantidad de artículos a producir. Esto significa que analizaremos cuál sería el valor final para distintas combinaciones de márgenes y cantidades. Para hacerlo crearemos una tabla de datos que resuma esta información.

500 Cantidad a producir Costo por unidad $ 8,50 Costos fijos $ 1.200,00 Costo total $ 5.450,00 Margen de ganancia 30% Ventas a realizar $ 7.085,00 Valor unitario

$ 14,17 15% 20% 25% 30% 35% 40%

$

300

14,17

400

500

600

EJERCICIO 7 (Filtros) Copie esta tabla en cuatro hojas distintas

Zona Distribuidor G Fuentes Zona Este 1 G Fuentes Zona Este 2 G Fuentes Zona Este 3 G Fuentes Zona Este 4 C Rodríguez Zona Norte 1 C Rodríguez Zona Norte 2 C Rodríguez Zona Norte 3 C Rodríguez Zona Norte 4 F Pérez Zona Oeste 1 F Pérez Zona Oeste 2 F Pérez Zona Oeste 3 F Pérez Zona Oeste 4 M Carminati Zona Oeste 1 M Carminati Zona Oeste 2 M Carminati Zona Oeste 3 M Carminati Zona Oeste 4

Ventas 2003 100000 230000 59000 186000 310000 98000 77000 160000 320000 190000 132000 148000 95000 74000 86000 110000

Ventas 2004 142000 225000 72000 190000 302000 101000 75000 190000 350000 200000 106000 140000 52000 96000 105500 132000

Se pide: Nota los se pide 1 a 3 realícelos sobre la misma base de datos. 1. Ventas del 2004 mayores a 150000 pesos. 2. Ventas menores a 60000 o mayores a 200000 pesos. 3. Ventas entre los 150000 y los 200000 pesos de aquellos distribuidores cuyo nombre comience con la letra C. 4. Genere una nueva tabla que muestre aquellos registros en los que las ventas del 2003 fueron mayores a las ventas del 2004.

                 

EJERCICIOS    GRÁFICOS    PERSONALIZADOS    Y    FORMULARIOS 

Ejercicios Módulo de Gráficos Personalizados y Formularios. Ejercicio 1 Dada la siguiente tabla:

Paises Años Australia Argentina Brasil EEUU Nueva Zelanda Uruguay Mexico Paraguay

Ventas Anuales 2004 1680000 1200000 1800000 2000000

2005 1230000 2250000 3500000 3500000

2006 2000000 2100000 3200000 3400000

1900000 600000 1200000 750000

2500000 1800000 1200000 900000

3000000 1750000 1600000 1240000

1. Generar un gráfico de columnas con las ventas de cada país según los años en los que se hayan producido. Generar un color de fondo del gráfico basado en una textura. Generar un efecto de relleno con degradado en el área de trazado. Desplegar la leyenda en la parte superior del gráfico. Aplicarle efectos de degradado a cada una de las series. 2. Generar un gráfico de columnas con las ventas de cada año para los diferentes países. Aplicar en cada serie el efecto de colocar la imagen de la bandera correspondiente a cada país como fondo de cada columna correspondiente a dicho país. Aplicar en el fondo del gráfico un efecto con degradado a dos colores. 3. Generar un gráfico de torta donde se represente las ventas del año 2004 a cada país. Mostrar dentro del gráfico el país al que corresponde cada área y el porcentaje correspondiente del total. Aplicarle un efecto 3D con un efecto de elevación.

Ejercicio 2 Dada la siguiente tabla: Edad

Grupo de edades

Poblacion

0a3

4a5

6 a 14

15 a 19

20 a 24

25 a 29

30 a 49

50 a 64

65 a 79

80 o más

MALVÍN

990

530

2.756

1.969

1.916

1.973

7.877

5.093

4.112

1.418

Hombres

487

268

1.387

1.029

947

920

3.672

2.177

1.631

438

Mujeres

503

262

1.369

940

969

1.053

4.205

2.916

2.481

980

•

Generar un gráfico de tipo pirámide poblacional. Aplicarle a dicho gráfico todos los retoques correspondientes para su correcta visualización.

•

Grabar este grafico como tipo personalizado para poder utilizarlo a futuro.

•

Aplicarle efecto de degradado a las series, y un efecto de textura tanto al área de trazado como al área de gráfico.

Ejercicio 3

Tareas Compra de Terrenos Desmonte Confección de Plano Compra de Materiales Construcción Pintura Revisión Entrega

Fecha Inicio

Fecha Fin

Días Corridos

10/02/2006 21/02/2006 08/03/2006

20/02/2006 10/03/2006 31/03/2006

10 17 23

01/04/2006 20/04/2006 25/08/2006 01/09/2006 18/09/2006

18/04/2006 24/08/2006 31/08/2006 15/09/2006 19/09/2006

17 126 6 14 1

Generar un gráfico representando un diagrama de Gantt. Aplicarle efecto de relleno con degradado a un color al área de gráfico y área de trazado.

Generar un diagrama de Gantt aplicando formato condicional con la misma tabla.

Ejercicio 4 Dada la siguiente tabla:

Hora Produccion 00:43 02:37 05:21 05:35 07:20 07:47 10:22 10:33 11:18

785 662 546 356 447 611 310 564 583

Generar un gráfico de dispersión (XY) con lineas suavizadas para representar la producción en el transcurso de las horas. Adaptar el eje de valores (x) para que represente una escala de horas que vaya de las 00:00 hs a las 12:00 pasando en valores de a 1 hora. Ejercicio 5

Pais

Años

Exp 2000

Exp 2001

Exp 2002

Exp 2003

Exp 2004

Exp 2005

Argentina

2293660

2657911

1913896

1231816

5000000

1319296

Brasil

3863727

2616891

2210725

4650539

4000000

3951452

Uruguay

1125873

1074146

1021074

1449403

1057386

1049902

Paraguay

1028543

1134743

1166809

1143472

1195914

1093311

877385

947571

1019173

1015051

1071022

754180

Bolivia

1379161

1405565

1530906

1670229

1535585

1144279

Venezuela

2275591

3113542

3706347

4245991

1819779

1971444

Ecuador

1907308

1443472

1815317

961641

1905881

1949043

Colombia

2511773

2085069

2073359

2006935

2266457

2273786

Peru

Minimo

877385

947571

1019173

961641

1057386

754180

Maximo

3863727

3113542

3706347

4650539

5000000

3951452

Dada la tabla expuesta: •

Utilizar lista desplegable, cuadro combinado, barra desplazamiento para lograr graficar en lineas las exportaciones de cada pais por año. La idea es que mediante un cuadro combinado yo pueda elegir un pais y visualice el grafico de las exportacion de ese pais (a través de los años). Lo mismo con una lista desplegable y también con barra desplazamiento.

Ejercicio 6

Dic-05

PROC1

PROC2

PROC3

PROC4

PROC5

PROC6

Dias

min

M

max

Valores

Valores

Valores

Valores

Valores

Valores

1

55

60

65

56

55

57

55

58

50

3

55

60

65

62

60

65

60

60

60

5

55

60

65

65

63

63

63

63

63

7

55

60

65

55

50

50

50

50

50

9

55

60

65

56

56

56

56

56

56

11

55

60

65

64

64

64

64

64

64

13

55

60

65

62

62

62

62

62

62

15

55

60

65

60

67

67

67

67

67

17

55

60

65

56

52

52

52

52

52

19

55

60

65

59

60

60

60

60

60

21

55

60

65

58

62

62

62

62

62

23

55

60

65

56

53

53

53

53

53

25

55

60

65

60

50

50

50

50

50

27

55

60

65

58

55

55

55

55

55

29

55

60

65

55

60

60

60

60

60

31

55

60

65

57

64

65

60

55

53

Dada la siguiente tabla, generar un gráfico de líneas que represente cada uno de los procesos. Dichos procesos se seleccionaran con un cuadro de lista, y control de números.

                                       

EJERCICIOS    MACROS    EN    EXCEL 

Módulo: MACROS EN EXCEL Ejercicio 1 Generar un macro que pregunte al usuario datos de empleados tales como: ™ Nombre y Apellido ™ Dirección ™ Ciudad (donde reside) o Si la ciudad es Montevideo mostrar en la siguiente columna: “CAPITAL” o Si la ciudad es diferente a Montevideo mostrar: “INTERIOR” ™ Sueldo nominal ™ Fecha de ingreso en la empresa o o o o o

Si el empleado tiene más de 5 años de antigüedad se le pagará un incentivo de antigüedad de un 2 % de su sueldo nominal. Si el empleado tiene más de 7 años de antigüedad se le pagará un incentivo de antigüedad de un 4 % de su sueldo nominal. Si el empleado tiene más de 9 años de antigüedad se le pagará un incentivo de antigüedad de un 6 % de su sueldo nominal. Si el empleado tiene más de 10 años de antigüedad se le pagará un incentivo de antigüedad de un 8 % de su sueldo nominal. Si el empleado tiene menos de 5 años de antigüedad no se le pagará dicho incentivo.

(Se compara siempre con la fecha actual = función DATE) Mostrar en una columna el incentivo por antigüedad que le corresponde al empleado. Calcular los aportes del empleado sabiendo que, sumando Sueldo Nominal y el incentivo de antigüedad se utilizarán las siguientes escalas: Aportes: ™ ™ ™ ™

18 20 24 26

% % % %

si si si si

gana gana gana gana

menos menos menos menos

de de de de

3 SMN 6 SMN 10 SMN 12 SMN

Tomar en cuenta que el SMN es de $ 1242. Mostrar el aporte de cada empleado en una columna. Mostrar el líquido de cada empleado sumando Sueldo Nominal + Antigüedad – Aportes. Repaso de inputbox, msgbox, bucles, uso de variables.

Ejercicio 2 Pedir al usuario nombre, precio unitario y descripción de artículos. Volcar estos datos en la planilla. A medida que el usuario cargue cada registro ir acumulando el total de precios, con la finalidad de calcular el promedio de precios. Dejar que el usuario corte el pedido de artículos cuando lo desee. En la última instancia, con el promedio de precios calculado, marcar el promedio de rojo si este es mayor a 500, marcarlo de verde si es mayor a 1000, y marcarlo de azul si es mayor a 2000. Repaso de inputbox, bucles, msgbox. Aplicar offset (o mostrar la aplicación del mismo en el ejercicio práctico). Ejercicio 3 Generar un macro que le pida al usuario datos de una persona: Nombre, Apellido, y Número de Credencial (sólo número) Basándose en la planilla que se plantea a continuación se deberá ir cargando automáticamente con los datos de cada persona y la dirección donde le tocará sufragar. Nº Circuito 113 502 682 691

Desde 1000 5001 8001 12001

Hasta 5000 8000 12000 18000

Dirección Italia 6255 Rodó 2411 Chaná 1654 Canelones 1698

La planilla que se irá cargando tendrá el siguiente formato: Nombre

Apellido

Credencial

Dirección

El estudiante deberá ejecutar mediante un botón en la planilla y el usuario deberá tener la flexibilidad de cargar tantos registros como desee, o sea por cada registro que vaya cargando se le preguntará si se desea continuar. Señalar de rojo solo los registros que el usuario haya ingresado y que tengan como lugar de votación la siguiente dirección: CANELONES 1698 Cuando el usuario decida terminar, se deberá mostrar la vista preliminar de la planilla.

Ejercicio 4 Generar una macro que guarde en una base de datos la siguiente información: ƒ Nombre de Articulo ƒ Lote del Articulo ƒ Rubro: 9 Deberá figurar “Informática” solamente si se trata de un insumo de computación 9 Deberá figurar “Otros” si no se trata de un insumo de computación ƒ Precio unitario de Costo ƒ Fecha de Compra de dicho articulo En función de esta información deberá calcularse el Precio de Venta Mayorista y Minorista. El Precio Mayorista se calcula como un 7% adicional sobre el Precio de costo, salvo para los artículos comprados hace mas de 3 años que se calcula un adicional de 5% sobre Precio de costo. El Precio Minorista de cada artículo se calcula como: 9 15% sobre Precio de costo para artículos de “Informática” con antigüedad menor a 1 año 9 12% sobre Precio de costo para artículos de “Informática” con antigüedad mayor o igual a 1 año 9 17% para el resto de los artículos Tanto el Precio mayorista como minorista deberán incluirse en 2 nuevas columnas destinado a ello. La funcionalidad de dicha macro deberá incluir: ¾ En la primer fila de la Base de datos, los títulos de cada variable. ¾ La posibilidad de utilizar dicha macro en repetidas ocasiones y que no se eliminen antiguos registros ¾ La posibilidad de elegir a continuación de cada registro estas 3 opciones: ƒ Ingresar un nuevo registró a continuación. ƒ Eliminar el último registro en caso de equivocación. ƒ Dejar de ingresar nuevos registros.

Ejercicio 5

Utilizar el asistente de Microsoft Office en la aplicación del ejercicio 4.

               

 

COMPENDIO    PRUEBAS    FUNCIONES    AVANZADAS 

COMPENDIO DE PRUEBAS Y EXAMENES DE PRÁCTICA    Funciones Avanzadas  Prueba Final  Ejercicio 1    Fecha 01/01/2006 03/01/2006 05/01/2006 09/01/2006 11/01/2006 11/01/2006 11/01/2006 11/01/2006 12/01/2006 12/01/2006 13/01/2006 16/01/2006 19/01/2006 20/01/2006 23/01/2006 25/01/2006 27/01/2006 27/01/2006 27/01/2006 31/01/2006 31/01/2006 03/02/2006 03/02/2006 07/02/2006 07/02/2006 07/02/2006 10/02/2006 15/02/2006 20/02/2006

Vendedor German Luis Marcelo Esteban Andrea Luisa Daniela German Luis Marcelo Luisa Daniela German Luis Marcelo Luis Marcelo Esteban Andrea Luisa Daniela Daniela German Luis Esteban Andrea Luisa Daniela German

Tipo de Producto Alimentos Informatica Electrodomesticos Alimentos Informatica Electrodomesticos Alimentos Informatica Electrodomesticos Alimentos Informatica Alimentos Informatica Electrodomesticos Alimentos Informatica Alimentos Informatica Alimentos Informatica Alimentos Informatica Electrodomesticos Alimentos Informatica Electrodomesticos Alimentos Informatica Electrodomesticos

Sucursal Central Central Filial2 Filial2 Filial 1 Filial2 Filial2 Central Central Filial2 Filial2 Filial2 Central Central Filial2 Central Filial2 Filial2 Filial 1 Filial2 Filial2 Filial2 Central Central Filial2 Filial 1 Filial2 Filial2 Central

Tipo Operación Venta Devolucion Venta Venta Venta Venta Devolucion Venta Devolucion Venta Venta Devolucion Venta Devolucion Venta Venta Venta Venta Devolucion Venta Venta Venta Venta Devolucion Venta Venta Venta Devolucion Devolucion

Forma de Pago Contado Contado Diferido 3 pagos Diferido 3 pagos Contado Contado Contado Contado Contado Diferido 3 pagos Diferido 6 pagos Contado Diferido 3 pagos Contado Contado Diferido 6 pagos Contado Contado Contado Diferido 3 pagos Diferido 6 pagos Contado Contado Diferido 3 pagos Diferido 6 pagos Contado Diferido 3 pagos Contado Contado

    A partir de la siguiente base de datos, referida a ventas de nuestra Empresa, se pide:  1.   1.1) Realizar la suma de las ventas efectuadas al contado, en la sucursal Central.  1.2) Calcule el Promedio de las ventas realizadas por el vendedor Marcelo.  1.3) Calcule la Máxima devolución efectuada dentro del tipo de producto Alimentos.  1.4) Realizar la suma de todas las ventas que no se efectuaron al contado.    2. Aplique un formato condicional a la base de datos con la siguientes características:  2.1) Los registros que corresponden a la sucursal central, color de fondo naranja.  2.2) Los registros que corresponden a la sucursal Filial 1, color de fondo celeste.  2.3) Los registros que corresponden a la sucursal Filial 2, color de fondo amarillo.    Ejercicio 2    A partir de las siguientes bases de datos referidas a “Clientes” y “Ventas”, se solicita lo siguiente:  Genere un cuadro de consulta con las siguientes características:    Cedula: Nombre: Apellido: Direccion: Suma Ventas: Cuenta de Devoluciones:

 

 

¾ El numero de Cedula deberá estar validado de acuerdo a los clientes. 

Importe $U 1.556 $U 2.294 $U 1.966 $U 2.843 $U 5.461 $U 5.685 $U 2.971 $U 4.101 $U 4.847 $U 5.030 $U 3.296 $U 1.656 $U 4.800 $U 2.913 $U 5.526 $U 3.969 $U 4.760 $U 1.492 $U 5.282 $U 2.189 $U 4.042 $U 5.176 $U 1.528 $U 1.846 $U 2.707 $U 4.754 $U 4.936 $U 1.911 $U 1.666

¾ A partir de la Cedula se deberán calcular automáticamente el resto de los datos.    Base de Datos Ventas Id Cliente 3201506-7 3501289-1 2650450-5 3456287-1 1795461-8 2975468-6 3197486-8 2497682-3 2894657-1 4132495-8 3201506-7 3501289-1 2650450-5 3456287-1 1795461-8 2975468-6 3197486-8 2497682-3 2894657-1 4132495-8 3201506-7 3501289-1 2650450-5 3456287-1 1795461-8 2975468-6 3197486-8 2497682-3 2894657-1 4132495-8 3201506-7 3501289-1 2650450-5 3456287-1 1795461-8 2975468-6 3197486-8 2497682-3 2894657-1 4132495-8 3201506-7 3501289-1 2650450-5 3456287-1 1795461-8 2975468-6 3197486-8 2497682-3

Operación Venta Devolucion Venta Venta Venta Venta Venta Venta Venta Devolucion Venta Devolucion Venta Devolucion Venta Devolucion Venta Devolucion Venta Venta Venta Devolucion Venta Venta Venta Devolucion Venta Venta Venta Venta Venta Devolucion Venta Venta Venta Venta Venta Devolucion Venta Venta Venta Venta Venta Devolucion Venta Venta Venta Venta

Importe 4.753 € 940 € 1.551 € 5.103 € 1.722 € 651 € 5.444 € 1.524 € 4.178 € 2.189 € 2.233 € 5.014 € 5.138 € 5.097 € 3.839 € 1.181 € 6.111 € 1.850 € 2.362 € 4.861 € 3.518 € 3.985 € 1.158 € 1.497 € 1.193 € 3.443 € 1.708 € 2.195 € 1.642 € 4.057 € 3.564 € 4.873 € 2.396 € 2.685 € 2.223 € 2.638 € 2.646 € 3.401 € 3.655 € 1.489 € 5.412 € 5.145 € 1.142 € 5.769 € 1.632 € 6.008 € 6.221 € 5.710 €

Base de Datos Clientes Id Cliente 3201506-7 3501289-1 2650450-5 3456287-1 1795461-8 2975468-6 3197486-8 2497682-3 2894657-1 4132495-8

Nombre Arturo Fabiana Leticia Karina German Daniel Milton Lourdes Mario Anibal

Apellido Edad Direccion Almeida 52 Rodo 2124 Lopez 74 Bvr Artigas 3251 Gomez 48 Bvr España 1256 Stirling 38 18 de julio 1311 Torres 35 Mercedes 1565 Fernandez 22 Uruguay 6145 Falcon 18 San jose 1245 Gracia 62 Barbato 5475 Victorino 18 T Gomensoro 2158 Troilo 21 Berro 9564

Telefono 094-873889 096-943820 099-435757 099-410376 094-288854 099-239134 094-714356 096-541862 096-787281 094-885641

                       

Ejercicio 3    A partir de la siguiente base de Datos referida a entrega de productos se solicita:    Fecha 02/02/2005 02/02/2005 03/02/2005 08/02/2005 08/02/2005 08/02/2005 08/02/2005 08/02/2005 11/02/2005 14/02/2005 15/02/2005 18/02/2005 21/02/2005 22/02/2005 22/02/2005 24/02/2005 25/02/2005 28/02/2005 02/03/2005 04/03/2005 08/03/2005 11/03/2005 11/03/2005 14/03/2005 17/03/2005 18/03/2005 22/03/2005 25/03/2005 29/03/2005

Empresa

Producto

Tipo de Cliente

Foxy SRL Almiron SA Foxy SRL Almiron SA Foxy SRL Almiron SA Foxy SRL Almiron SA Foxy SRL Almiron SA Foxy SRL Almiron SA Foxy SRL Almiron SA Lypton SA DRR Corp Almiron SA Foxy SRL Almiron SA Foxy SRL Almiron SA Lypton SA DRR Corp Almiron SA Foxy SRL Almiron SA Foxy SRL Almiron SA Lypton SA

PC Pentium II PC Pentium III TV 21 DVD DVD Monitor LCD Mouse Optico PC Pentium II PC Pentium III TV 21 DVD DVD Monitor LCD Parlantes PC Pentium II PC Pentium III TV 21 DVD DVD DVD Monitor LCD Mouse Optico Parlantes PC Pentium III Parlantes DVD DVD DVD Monitor LCD

Mayorista Minorista Mayorista Mayorista Mayorista Minorista Mayorista Minorista Mayorista Minorista Mayorista Mayorista Minorista Minorista Mayorista Minorista Mayorista Mayorista Mayorista Minorista Mayorista Mayorista Mayorista Minorista Mayorista Mayorista Minorista Mayorista Mayorista

Fecha Entrega

    1. Calcule la Fecha de la Entrega de acuerdo a los siguientes criterios:  1.1. A todos los clientes Mayoristas se les entregan los productos a los 3 días hábiles de la  fecha de pedido.  1.2. A los clientes Minoristas se les entrega a los 10 días hábiles, salvo para la empresa  vendedora “DRR Corp”, que se entregan a los 5 días hábiles.   2. Aplique un formato condicional con las siguientes características:  2.1. Fondo azul para los registros correspondientes a PC (todos los modelos).  2.2. Fondo rojo para el resto de los registros.    Ejercicio 4    Se cuenta con la siguiente base de datos referida a las horas trabajadas de los diferentes  empleados de la empresa, con 109 registros.  En base a dicha información se solicita:  1. Genere en una nueva columna el cálculo de las horas trabajadas de cada empleado para cada  registro.  2. Calcule en una nueva columna el pago por hora a cada trabajador de acuerdo a la siguiente  regla:  2.1. Si pertenece al sector “Ventas”; 20 euros por hora.  2.2. Si pertenece al sector “Compras”; 18 euros por hora.  2.3. Si pertenece al sector “Contable”; 15 euros por hora.  2.4. Si pertenece al sector “Administrativo”; 12 euros por hora.  2.5. Si pertenece al sector “Producción”; 8 euros por hora.  3. Calcule en una nueva columna el pago del salario total para cada día.  4. Genere un solo cuadro de consulta a su elección entre las siguientes opciones:   

Empleado Fecha Salario pagado hasta la fecha

  En ambos casos los de Empleado o Sector y el dato de fecha será introducido por el usuario de la  consulta, y se deberá calcular automáticamente el 3º dato.  Si elige el primer cuadro de consulta se deberá calcular los salarios totales pagados a dicho  Empleado, hasta la fecha que figure en el cuadro. La celda donde se introduzca el Empleado  deberá validarse.  Si elige el segundo cuadro de consulta se deberá calcular los salarios totales pagados a dicho  Sector, hasta la fecha que figure en el cuadro. La celda donde se introduzca el Sector deberá  validarse.    Sector

Nombre Apellido

Contable Produccion Compras Compras Contable Administrativo Produccion Administrativo Produccion Administrativo Contable Produccion Ventas Produccion Administrativo Produccion Produccion Produccion Ventas Produccion Ventas Compras Produccion Produccion Ventas Produccion Produccion Contable Produccion Compras Compras Contable Administrativo Produccion Administrativo Produccion Administrativo Contable Produccion Ventas Produccion Administrativo Produccion Produccion Produccion Ventas Produccion Ventas

Raul Abal Lucila Vazquez Anibal Bass German Furia Ana Monje Eliana Boss Andrea Guzman Luis Torres Walter Lopez Hugo Prato Danila Estevanez Julio Verdi Juan Balverde Esteban Samir Fernando Luisi Milton Suarez Diego Nion Daniel Marquez Miriam Rey Maite Lupro Damian Valente Jimena Duarte Fernando Grassi Luisa Ottospan Mirta Rodriguez Carla Dupuy Ernesto Darlem Raul Abal Lucila Vazquez Anibal Bass German Furia Ana Monje Eliana Boss Andrea Guzman Luis Torres Walter Lopez Hugo Prato Danila Estevanez Julio Verdi Juan Balverde Esteban Samir Fernando Luisi Milton Suarez Diego Nion Daniel Marquez Miriam Rey Maite Lupro Damian Valente

                   

Fecha 01/02/2006 01/02/2006 02/02/2006 03/02/2006 03/02/2006 04/02/2006 04/02/2006 05/02/2006 05/02/2006 06/02/2006 06/02/2006 07/02/2006 07/02/2006 07/02/2006 08/02/2006 09/02/2006 10/02/2006 11/02/2006 11/02/2006 12/02/2006 12/02/2006 13/02/2006 13/02/2006 14/02/2006 14/02/2006 15/02/2006 15/02/2006 16/02/2006 16/02/2006 16/02/2006 17/02/2006 20/02/2006 21/02/2006 22/02/2006 22/02/2006 22/02/2006 24/02/2006 27/02/2006 01/03/2006 02/03/2006 02/03/2006 02/03/2006 06/03/2006 06/03/2006 07/03/2006 07/03/2006 07/03/2006 07/03/2006

Hora Ingreso 11:11:22 7:47:29 8:34:49 23:51:50 0:36:05 0:54:53 8:45:58 22:45:28 12:05:06 20:29:17 7:25:26 4:19:15 0:00:45 1:18:58 19:14:38 18:06:46 10:14:41 5:26:43 1:25:57 17:26:05 17:30:59 8:07:40 11:17:42 17:53:43 2:16:55 11:49:50 7:11:00 23:02:17 16:29:22 21:43:18 20:20:44 11:54:08 0:18:22 17:33:35 1:05:19 14:34:28 17:09:20 17:24:19 22:12:16 9:34:09 2:52:58 8:20:40 15:45:04 3:47:03 8:25:54 8:02:41 23:02:35 18:43:08

Hora Egreso 5:14:53 6:23:34 16:08:55 7:53:06 4:10:01 14:32:46 0:57:23 7:41:47 23:17:14 15:53:44 3:32:26 12:20:00 5:30:01 2:43:36 3:32:01 3:47:05 12:43:31 0:34:42 23:08:40 1:33:24 4:36:26 1:39:25 13:52:30 11:28:25 2:21:09 21:56:34 2:14:06 23:30:23 20:38:22 21:05:41 5:56:07 17:25:57 3:57:12 4:13:53 11:04:26 5:07:44 6:23:08 18:06:56 4:44:52 20:30:08 4:32:00 14:07:48 8:10:43 20:19:58 9:54:43 16:04:13 3:08:58 4:17:44

Horas Trabajadas

Pago/Hora

Salario/dia

Funciones Avanzadas  Prueba Final  Ejercicio 1    A continuación se presenta la siguiente base de datos, la cual contiene los nombres de los  participantes de los 4 equipos existentes.      Equipo A Adalberto Alberto Alicia Alison Ana Ana Maria Anastacia Antonio Armando Arnaldo Arturo Asencio

Equipo B Baltasar Bartolo Basilio Beatriz Benjamin Bernardo Berta Betiana Betina Brian Bruce Bruno

Equipo C Camila Camilo Carla Carlos Carmen Carol Carolina Cecilia Celene Celeste Celia Cristian

Equipo D Damian Daniel Daniela Danilo Dante Dario Demetrio Demostenes Denise Diana Dino Diogenes

    Dichos participantes de los equipos se encuentran agrupados por columnas, y se sabe que cada  equipo contara con 16 participantes.    Se Pide:    Creé un cuadro de consulta similar al que se le presenta a continuación, donde a partir de la  introducción de un texto cualquiera en el campo correspondiente al “Nombre de Participante: ”, el  mismo devuelva en el campo correspondiente a “Equipo: ”, el equipo al cual corresponde dicho  participante. En el caso que no exista dicho participante, en dicho campo deberá figurar: “No  pertenece a ningún equipo”.    Tenga en cuenta que aun se encuentran cupos libres en cada equipo, y la función que usted cree  para dicho cuadro de consulta deberá funcionar correctamente cuando se inserten nuevos  participantes. Y recuerde que el nombre de dicho participante no tiene restricción alguna (puede  comenzar con cualquier letra).    El formato del cuadro de consulta es el siguiente:    Nombre de Equipo:     Participante:          

Ejercicio 2    Se cuenta con la siguiente base de datos, referida a movimientos de comercio exterior.  Se pide:     1) Un cuadro de consulta como el siguiente, en el cual a partir del ingreso de datos como el  “País”, “Tipo de Operación”, y “Fecha”, se nos devuelva las operaciones de “Suma”,  “Promedio”, “Máximo”, “Mínimo” referida a los datos de origen.       Fecha: Pais: Tipo de Operación: Suma Promedio Maximo Minimo

Brasil Importacion

    En el caso del campo de fecha, las operaciones deberán realizarse a partir de dicha fecha  especificada (para fechas mayores o iguales a la fecha especificada).  Tanto el nombre de los campos “País” y “Tipo de Operación”, deberán validarse a través de una  lista que deberán crear.  2) Aplicar formato condicional a la Base de datos, donde se pinten las filas de la misma en función  de los siguientes criterios  • Si es Exportación, deberá figurar un fondo azul, fuente color blanco, estilo Negrita.  • Si es Importación, deberá figurar un fondo rojo, fuente color negro, estilo Negrita.                                               

Base de datos:    Fecha 01/01/2005 15/01/2005 01/02/2005 15/02/2005 01/03/2005 15/03/2005 01/04/2005 15/04/2005 01/05/2005 15/05/2005 01/06/2005 15/06/2005 01/07/2005 15/07/2005 01/08/2005 15/08/2005 01/09/2005 15/09/2005 01/10/2005 15/10/2005 01/11/2005 15/11/2005 01/12/2005 15/12/2005

Tipo de Operación Exportacion Exportacion Importacion Exportacion Importacion Importacion Importacion Exportacion Exportacion Importacion Exportacion Exportacion Exportacion Importacion Exportacion Exportacion Importacion Importacion Importacion Importacion Exportacion Exportacion Exportacion Importacion

País Brasil Argentina EEUU Australia Paraguay Mexico China España Argentina Brasil Argentina Argentina EEUU EEUU EEUU España Brasil Brasil España Argentina Mexico Paraguay China Argentina

    Ejercicio 3    Factura A001 A002 A004 A005 A011 A013 A015 A019 A021 A021 A023 A105 A205 A568 B209 B524

Fecha Facturacion 01/01/2005 02/01/2005 05/01/2005 07/01/2005 14/01/2005 16/01/2005 19/01/2005 21/01/2005 23/01/2005 27/01/2005 31/01/2005 01/03/2005 02/04/2005 03/07/2005 04/09/2005 05/11/2005

Productos VHS DVD TV 20' TV 21' DVD VHS TV 29' VHS DVD DVD DVD VHS VHS TV 21' TV 29' VHS

Importe 1250 780 1350 1540 1230 500 1450 750 800 1100 1000 750 1200 1000 580 800 800 750 450 800 780 450 1100 800  

Empresa La Tardia La Tempranera La Tempranera La Tempranera La Tardia La Tardia La Tardia La Tempranera La Tempranera La Tardia La Tardia La Tempranera La Tempranera La Tardia La Tempranera La Tardia

Fecha de Entrega

    A partir de la siguiente base de datos, nuestra empresa de logística que se dedica a la entrega de  diferentes productos a diferentes empresas, desea conocer las fechas de entrega para cada uno de  los productos.    Se nos da a conocer la siguiente información:    Todos los Productos de la Empresa “La Tardía” se entregan a los 15 días hábiles de la fecha de  facturación. 

En cambio para la Empresa “La Tempranera” se entregan los productos a los 3 días hábiles de la  fecha de facturación, salvo los DVD que deben entregarse al siguiente día hábil.     Ejercicio 4    Plan Circuital Nº de Circuito

Desde 101 151 201 251 301 501

Hasta Direccion 1 1000 Guana 2015 1001 5000 18 de Julio 2374 5001 9000 21 de septiembre 2323 9001 12000 Rodo 3111 12001 15000 Mercedes 2173 15001 20000 Uruguay 2158  

  Se solicita que cree un cuadro de consulta para facilitarle a los votantes de esta institución  encontrar su circuito y local de votación.  Ellos ingresaran su Numero de Socio, y deberá visualizarse su circuito de votación y Dirección de  dicho local.    Numero de Socio Nº de Circuito Direccion

      El cuadro de consulta jamás podrá dar como resultado algún error, y en el caso que no exista  ningún número de socio ingresado, los campos “Nº de Circuito” y “Dirección”, deberán mostrar  “No se ha ingresado Nº de Socio”.                                              

 

COMPENDIO    PRUEBAS    ANÁLISIS    DE     INFORMACIÓN 

Prueba Final  Tablas Dinámicas    A partir de la Base de datos ubicada en la hoja “BD_Ventas”, realice los siguientes ejercicios:    Ejercicio 1    1. Realice una tabla dinámica que muestre segmentado por turno; la cantidad de ventas  realizadas y el monto total de la facturación en pesos.  1.1. Asigne un formato de pesos, sin decimales, a los datos relacionados con la facturación  total.  1.2. Cambie el nombre de los campos del área de datos a: “Cantidad de Ventas” y  “Facturación Total”.  1.3. Agregue un Auto formato a dicha Tabla del tipo “Informe 4”.  1.4. Ordene la tabla de mayor a menor de acuerdo a las cantidades de ventas en cada  turno.  2. Realice otra nueva tabla dinámica que muestre segmentado para cada supervisor; el  monto total de la facturación realizada.  2.1. Asigne un formato de pesos, sin decimales, a los datos relacionados con la facturación  total.  2.2. Cambie el nombre del campo del área de datos a: “Ventas Totales”.  2.3. Agregue un Auto formato a dicha Tabla del tipo “Tabla 2”.  2.4. Ordene la tabla de mayor a menor de acuerdo a la facturación total.    Ejercicio 2    1. Realice otra nueva tabla dinámica que muestre segmentado por tipo de producto, las  unidades totales vendidas.  1.1. Asigne un auto formato del tipo “Tabla 1”  1.2. Ordene dicha tabla de mayor a menor de acuerdo a las cantidades vendidas de cada  tipo de producto.  2. Genere un grafico dinámico de columnas asociado a dicha tabla, donde se muestre los  tipos de productos (en el Eje X), con una columna que muestre las cantidades vendidas de  cada tipo .    Ejercicio 3    1. Realice una tabla dinámica que muestre para cada empleado el promedio en pesos de sus  ventas efectuadas. Filtre dicha información solamente para las ventas realizadas en el  turno de la noche y efectuadas al contado.  1.1. Aplique un AutoFormato del tipo  “Informe 10”.  1.2. Asigne un formato de pesos, sin decimales, a los datos relacionados con el promedio  de la facturación total.  1.3. Cambie el nombre del campo del área de datos a: “Promedio de Facturación por  empleado”.  1.4. Ordene la tabla de mayor a menor, de acuerdo al promedio de la facturación de  cada empleado. 

2. Genere un grafico dinámico circular asociado a dicha tabla, donde se muestre para cada  vendedor el total de las ventas efectuadas, según los criterios del punto anterior.    Ejercicio 4    1. Genere una tabla dinámica que muestre para cada tipo de pago, el monto total de la  facturación realizada.  1.1. Aplique un autoformato a dicha tabla del tipo “Informe 2”.  1.2. Agregue un formato de pesos al total de las ventas, y el campo del área de datos se  llamara “Facturacion Total”.  1.3. Agregue un campo calculado a dicha tabla que muestre la facturacion total + IVA en  una nueva columna. El nombre en la tabla deberá figurar “Importe + IVA”.  2. Genere una nueva Tabla dinámica que muestre para cada tipo de pago, el monto total de  la facturacion realizada.  2.1. Aplique un auto formato del tipo “Informe 6”.  2.2. El campo del área de datos deberá llamarse “Facturacion por forma de pago”, y  deberá tener un formato de pesos.  2.3. Agregue 2 elementos calculados en el campo Empleados, los mismos se llamaran:  “Visa Corregido” y “Master Card Corregido”. Y se calcularan con un porcentaje  adicional del 7% para “Visa”, y 5% para “Master Card”.  2.4. Oculte los elementos Visa y Master Card (que se visualicen solamente los elementos  calculados).  2.5. Ordene la tabla de mayor a menor de acuerdo a la facturacion total.                                                   

Prueba Final  Tablas Dinámicas    Ejercicio 1    A partir de la Base de Datos ubicada en “BD_Mdeo”, se pide:  3. Realice una tabla dinámica que muestre la Población de cada barrio segmentado por   sexo, además deberá filtrar dicha información para las edades comprendidas entre 15 y 29  años.  3.1. Ordene dicha Tabla de Mayor a Menor, de acuerdo a la Población de cada Barrio de  Mdeo.  3.2. Agregue un AutoFormato a dicha Tabla del tipo “Tabla 10”.  4. Genere un grafico dinámico de columnas asociado a dicha tabla, donde se visualice la  población de cada Barrio en una sola columna (cada Barrio deberá figurar en el Eje X).   En el eje X deberán aparecer solamente los Barrios: Pocitos, Cordón, Punta Carretas,  Centro, Unión y Buceo.      Ejercicio 2    A partir de la Base de Datos ubicada en “BD_Mdeo”, se solicita:  2. Efectúe una tabla dinámica, donde se pueda comparar por sexo y barrio, los hombres y  mujeres que viven en los barrios de “Flor de Maroñas”, “La Teja” y “Brazo Oriental”. Pero  filtre dicha tabla para que aparezcan solamente aquellas personas con edades entre 50 y  79 años.  2.1. Ordene dicha tabla de acuerdo a la población total de cada barrio de acuerdo a las  características mencionadas.  2.2. Agregue un AutoFormato a dicha Tabla del tipo “Tabla 3”.  3. Genere un grafico dinámico de columnas asociado a dicha tabla, donde se muestre los  citados Barrios (en el Eje X) y una columna para la población de cada sexo asociada al  barrio.      Ejercicio 3    A partir de la Base de Datos ubicada en “BD_Ventas”, se solicita:  2. Realice una tabla dinámica que muestre para cada vendedor las ventas efectuadas en los  meses de febrero y mayo.   Permita filtrar dicha información según Tipo de Pago, y muestre solamente los datos para  las ventas al contado.  2.1. Aplique un AutoFormato de “Tabla 2”.  3. Genere un grafico dinámico circular aparte, donde se muestre para cada vendedor el total  de ventas efectuadas, solamente deberán figurar las realizadas en los meses de marzo y  abril, para todas las ventas no efectuadas al contado.       

Ejercicio 4    A partir de la Base de Datos ubicada en “BD_Ventas”, se solicita:  2. Genere una tabla dinámica, que muestre el promedio de las ventas totales efectuadas,  para cada tipo de producto solamente para las ventas al contado. Permita filtrar dicha  tabla de acuerdo a los vendedores que las realizaron.  2.6. Aplique un autoformato a dicha tabla del tipo “Tabla 10”.  2.7. Agregue un formato de euros al promedio de las ventas.  3. Genere otra tabla dinámica que permita observar, el porcentaje de cada Tipo de Producto  en el total de las ventas efectuadas en marzo, para todas las ventas que han recibido un  descuento menor al 5%.                                                                           

PRUEBA Análisis de Información – EXCEL AVANZADO    EJERCICIO 1    Basados en los datos existentes en la Hoja1 del archivo Ventas.xls:  • Crear una tabla dinámica, en la celda F1, que muestre el máximo de ventas del año 2004 para cada  zona.  • Colocarle como nombre de campo “Máximo 2004”  • Ordenar la tabla por este campo en forma Ascendente  • Crear otra Tabla Dinámica, en la celda F9, que indique el promedio de ventas por Vendedor del año  2003.  • Aplicarle a este campo formato de número, con separador de miles y dos posiciones decimales.  • Mostrar solamente los datos correspondientes a LOPEZ y RODRIGUEZ  • Crear otra Tabla Dinámica, en una hoja nueva, donde se muestre el total de cada año por Zona.  • Agregar un campo calculado, llamado “Diferencia” que muestre cuanto más se vendió en el 2004  respecto del 2003.  • Agregar otro campo calculado, llamado “Porcentaje” que muestre cuanto representa  porcentualmente (sobre 2003) la diferencia calculada en el punto anterior. Colocarle formato % con  un decimal.  • Por detectarse un error deberá cambiar el valor del último registro de la lista original. Las ventas  reales de PEREZ para el año 2004 fueron de 4765. Reflejar este cambio en la Tabla Dinámica.      EJERCICIO 2    Trabajar sobre los datos que se encuentra en la Hoja1 del archivo Seminarios.xls    • Realizar una tabla dinámica en una Hoja nueva que indique la cantidad de seminarios que tendrá  cada profesor y colocarle al campo correspondiente el nombre “Total de Seminarios”  • Representar la tabla del punto anterior, en un gráfico circular, pero solamente mostrando los datos  correspondientes a “Hardware” y “Lenguajes”.    EJERCICIO 3  Utilizando el Archivo Seminarios.xls, utilizar filtros para:    • Obtener los seminarios dictados en CHILE.  • Generar una sub base de datos para obtener los seminarios dictados en CHILE y cuya duración haya  sido mayor de 30 minutos. La nueva sub base de datos tiene que tener las columnas del nombre,  tema, y duración.  • Crear, con la herramienta subtotales, los subtotales de alumnos que acudieron a cada seminario  dictado en cada país.    EJERCICIO 4  Mediante la siguiente tabla:    Importe Ventas:  560000  Porcentaje Costo:  65%  Importe :  364000  Utilidad:  196000    Con la herramienta tabla: realizar cálculos de utilidad, dependiendo de las siguientes variaciones del  porcentaje de costo (45%; 60%; 75%; 85%; 90%). 

 

COMPENDIO    PRUEBAS    GRÁFICOS    PERSONALIZADOS 

Prueba Final  Módulo: “Gráficos Personalizados”    Ejercicio Nº 1    Copie el siguiente cuadro de datos en la 1º hoja de su libro de Excel al que llamara  “Prueba_GP_Nombre_Apellido”. A la hoja nómbrela como “Tabla Datos”.    Equipos de Venta Meses Enero Febrero Marzo Abril Mayo Junio Julio Agosto Septiembre Octubre Noviembre Diciembre

Equipo A $U 150.000 $U 271.139 $U 310.874 $U 324.147 $U 338.982 $U 362.883 $U 473.192 $U 533.822 $U 653.734 $U 701.385 $U 776.790 $U 913.258

Equipo B $U 157.487 $U 299.764 $U 352.540 $U 465.699 $U 489.592 $U 525.612 $U 671.261 $U 802.015 $U 924.318 $U 1.018.397 $U 1.162.809 $U 1.245.318

Equipo C Equipo D $U 125.868 $U 90.834 $U 163.736 $U 244.286 $U 194.513 $U 246.763 $U 350.106 $U 361.981 $U 382.835 $U 536.825 $U 529.520 $U 554.831 $U 704.240 $U 693.014 $U 875.955 $U 766.389 $U 963.160 $U 905.500 $U 1.089.458 $U 978.142 $U 1.140.784 $U 1.072.761 $U 1.220.691 $U 1.197.392

Equipo E $U 52.481 $U 127.685 $U 210.303 $U 248.181 $U 349.743 $U 455.638 $U 489.846 $U 552.640 $U 722.182 $U 773.879 $U 809.750 $U 911.762

  Se Pide:    1.1) Cree un grafico de columnas e insértelo en una nueva hoja del libro a la que llamara           “Grafico 11”con las siguientes especificaciones:  • Se desea realizar un grafico que muestre un comparativo de las  ventas totales anuales realizadas por cada Equipo de Ventas  (deberá figurar una columna sola para cada Equipo de Ventas en  el eje X), dicho grafico debe ser creado a partir de la tabla de  datos original, sin crear una nueva columna para totales. Se  debe incluir en el grafico los rótulos de categoría del eje X.  • Cada serie incluida en el grafico debe llevar su nombre a través  de una referencia a las celdas ubicadas en la tabla original  • El grafico debe incluir como titulo del mismo “Ventas anuales  según Equipos de Ventas”. La leyenda de referencias no deberá  figurar en el grafico.  • Cada Eje deberá llevar los siguientes títulos, en formato  “Negrita”:  ƒ X= “Equipos de Ventas”  ƒ Y= “Ventas Anuales”  • La escala del eje “Y” deberá visualizarse cada 1200000 unidades  • El área de trazado del grafico deberá tener como efecto de  relleno una textura de mármol verde al igual que el área del  grafico.  • Asigne un formato similar a todas las series (meses) para  generar en la columna de cada equipo de venta el efecto 

deseado de una columna uniforme, para ello quite bordes y  adopte un efecto de relleno a su elección.      1.2)

    1.3)

  1.4)

Ubique el siguiente grafico en una nueva hoja llamada: “Grafico 12”  Asimismo se desea observar un grafico de barras con efecto 3D, donde se muestre las  ventas discriminadas por meses (para cada mes una sola barra que incluya todos los  equipos de ventas). Para ello se le solicita que genere dicho grafico a partir de la  información contenida en la tabla, y escoja un subtipo de grafico de barras adecuado a tal  fin. Además asigne un efecto de relleno similar en todas las series incluidas en el grafico,  para generar un efecto de homogeneidad en cada barra.   Formatos del grafico:  • No deberá figurar la leyenda con las diferentes series.  • El titulo del grafico será: “Ventas Mensuales para el año 2006”  • El titulo del Eje Z (valores) será: “Importes de Ventas”  • Se debe incluir en el grafico los rótulos de categoría del eje X, y  deberán figurar en Negrita, además del titulo para dicho eje:  “Meses”.  • El efecto de relleno de las áreas de trazado y del grafico,  deberán incluir un color celeste y gris en degradado y ser  similares en sus estilos de sombreado  • El efecto de relleno de los planos laterales e inferiores del  grafico, deberán incluir un color verde en degradado.    

Se desea generar un grafico de líneas donde se visualice la evolución mensual de las  ventas para cada Equipo de Ventas.  Realice un nuevo grafico con las siguientes características:  • El titulo del grafico será: “Evolución mensual de las ventas  según Equipo de Venta”  • El titulo del eje x: “Meses”  • El titulo del eje y: “Importe de Ventas”  • Todos los títulos deberán figurar en negrita.  • Deberá incluir el rotulo de categorías en negrita  • Las áreas de; trazado, grafico, diferentes títulos y  leyenda,  deberán tener el mismo efecto de color a su elección.   • La escala del “Eje Y” deberá visualizarse cada $175000.  • Ubíquelo en nueva hoja llamándola “Grafico13”  • La leyenda deberá figurar sobre el grafico.  Deseamos observar en un nuevo grafico la participación de cada Equipo de Venta para el  total de las ventas en diciembre, a través de un grafico circular con efecto 3d y seccionado.  Añada un formato que incluya:  • Una imagen de fondo en el área de grafico.   • El titulo de dicho grafico será: “Participación de los Equipos de  Venta en Diciembre 2006”. 

• • •

Deberá figurar el nombre y el porcentaje correspondiente a  cada Equipo, separados por una nueva línea.  La leyenda con las categorías deberá figurar sobre el grafico.  Ubíquelo en nueva hoja llamándola “Grafico14”.  

    Ejercicio Nº 2    Copie la siguiente tabla a una nueva hoja del libro con el cual esta trabajando y nómbrela  “Pirámide Poblacional”.    Total POCITOS Hombres Mujeres

0a3 4a5 69.636 2.276 1.156 29.827 1.155 590 39.809 1.121 566

Grupo de edades 6 a 14 15 a 19 20 a 24 25 a 29 30 a 49 50 a 64 65 a 79 80 o más 5.442 4.379 5.580 5.957 17.998 12.513 10.248 4.087 2.699 2.076 2.598 2.736 8.053 5.083 3.685 1.152 2.743 2.303 2.982 3.221 9.945 7.430 6.563 2.935

  A partir de la siguiente tabla de datos deberá construir un grafico personalizado que represente  una “Pirámide Poblacional”. Dicho grafico deberá presentar en el Eje horizontal los Hombres a la  izquierda y las mujeres a la derecha, recuerde que solamente deberá aparecer en el grafico los  “grupos de edades” en el Eje vertical, y no los totales.     Deben aparecer títulos en ambos ejes y fundamentalmente las categorías en el Eje X.   En cada barra deberá figurar a la interna de la misma el valor para dicho sexo y grupo etario.  El formato de las diferentes áreas del grafico debe ser del mismo color, que usted elija.      Ejercicio Nº 3 (Opcional 1)      Consultas Inscripciones 1500 750 1600 760 1800 850 1650 800 200 75 840 550 1200 680  

  Se presentan los siguientes pares de Valores (Consultas sobre un Curso, Inscripciones a dicho  curso).    Se cree que dichas series se correlacionan de alguna manera.    Se solicita si usted podría determinar el mejor ajuste lineal de dichas series a través de una línea  de tendencia, representar gráficamente la nube de puntos y dicha estimación lineal, la ecuación de  la misma y el coeficiente de correlación al cuadrado (R^2).      

Ejercicio Nº 4 (Opcional 2)    Para el siguiente proyecto de campaña publicitaria se cuenta con la siguiente información:   

Tareas Planificacion Revision Primaria Pruebas de desarrollo 1º Proceso Productivo 2º Proceso Productivo 3º Proceso Productivo Evaluacion de Resultados Replanteo del Proyecto

Fecha Inicial Fecha Final 01/03/2006 16/03/2006 25/03/2006 16/03/2006 01/05/2006 15/05/2006 01/07/2006 10/07/2006

15/03/2006 22/03/2006 15/04/2006 15/05/2006 15/06/2006 30/06/2006 05/07/2006 15/07/2006  

  Se solicita que realice para el informe de presentación del desarrollo de un nuevo producto, un  Grafico Personalizado de Gantt donde se visualicen las diferentes tareas del Proyecto en el Eje X, y  en el Eje Y se muestren las fechas involucradas.      El grafico deberá presentar:    • Titulo del mismo, y títulos de ambos ejes (no leyenda de referencias).  • Valores de duración de cada una de las tareas en el interior de cada barra  • Formatos de área de grafico y trazado en celeste.  • Formatos del área de las series en color rojo.                                                 

PRUEBA MÓDULO DE GRAFICOS – CURSO EXCEL AVANZADO BIOS

EJERCICIO 1    Meses  Equipo A 

Equipo B 

Enero 

180500 

176000 

Febrero 

190200 

120000 

Marzo 

170800 

135000 

Abril 

165900 

210000 

Mayo 

189000 

180300 

  Se pide:    1. Generar un gráfico de columnas que contenga las ventas de ambos equipos en cada uno  de los meses (en eje de X los meses y en eje de Y los importes de ventas de cada uno de  los equipos).  2. Representar en el mismo gráfico (agregado posterior al primer gráfico), dos series que  representen porcentualmente la incidencia de cada venta en cada uno de los meses, y esta  representación de porcentajes deberá estar representada por un eje secundario.  3. Aplicar efectos de relleno en cada una de las series (equipo A con un efecto de relleno a  eleccion del estudiante, equipo B con otro efecto de relleno a elección del estudiante).  4. El area de trazado tendrá como efecto de relleno una textura con efecto de marmol.  5. Generar automatización en el gráfico. Esto se refiere a que, si el estudiante incorpora  otros meses (junio, julio y agosto, junto con ventas para cada equipo) estos datos se  agreguen en forma automática en el gráfico en cuestión.      Ejercicio 2    Plantas de Celulosa     Se ha realizado una encuesta a determinada muestra poblacional (200 personas) acerca de su  opinión acerca de si las plantas se instalarán en Argentina o en Uruguay.    Paises/Edades  20 a 30 años  31 a 40 años  41 a 50 años  51 a 60 años  Argentina 

155 

145 

110 

80 

Uruguay 

45 

55 

90 

120 

  Generar un gráfico de pirámide poblacional. Adicionar al mismo un título para cada uno de los  ejes. Mostrar los valores dentro del gráfico. Aplicar efectos de relleno que el estudiante considere.    Grabar este grafico como personalizado para aplicarlo en futuros usos (darle como nombre  pirámides personalizadas).   

Prueba Final  Módulo: “Gráficos Personalizados”  Ejercicio Nº 1    Copie el siguiente cuadro de datos en la 1º hoja de su libro de Excel al que llamara  “Prueba_GP_Nombre_Apellido”. A la hoja nómbrela como “Ejercicio 1”.    Ventas Anuales Vendedores Martin Alicia Jaime Gustavo Elisa Juan

2000 150 320 260 460 750 120

2001 180 350 280 620 820 375

2002 230 350 300 750 600 400  

  Se Pide:    1.1) Cree un grafico de columnas e insértelo en una nueva hoja del libro a la que llamara           “Grafico 11”con las siguientes especificaciones:  • Se desea realizar un comparativo para cada año por  vendedores, por lo tanto se le pide que en el Eje X figuren los  años, y además se incluyan en el mismo los rótulos de categoría.  • Cada serie incluida en el grafico debe llevar su nombre a través  de una referencia a las celdas ubicadas en la tabla original  • El grafico debe incluir como titulo del mismo “Ventas anuales  según vendedores” y la leyenda con las referencias a las series.  Ambos deben presentar como efecto de relleno en sus tramas el  mismo formato, un degradado a dos colores con un estilo de  sombreado diagonal. La leyenda de referencias deberá figurar a  la izquierda del grafico.  • Cada Eje deberá llevar los siguientes títulos, en formato  “Cursiva”:  ƒ X= “Años”  ƒ Y= “Ventas en miles de Pesos”  • La escala del eje “Y” deberá visualizarse cada 120 unidades  • El área de trazado del grafico deberá tener como efecto de  relleno una textura de mármol blanco al igual que el área del  grafico  • Debido a la complejidad de reconocer cada vendedor dentro de  cada año se decidió que se incluya el nombre del mismo en cada  columna correspondiente, siguiendo la alineación de la misma.     

1.2)

1.3)

1.4)

Asimismo se desea observar un grafico de barras con efecto 3D, donde se muestre un  comparativo por vendedores para el año 2001 y 2002, con idéntico formato que el grafico  realizado en el punto 1.1), ubíquelo en una nueva hoja a la que llamara “Grafico12”.    Además nos piden realizar un nuevo grafico de columnas con formatos similar al 1.1) para  comparar los totales de ventas para los años 2001 y 2002 entre todos los vendedores,  pero sin generar una nueva serie en la tabla original. Para generar el efecto deseado en el  grafico modifique el área de una o ambas series para que queden con el mismo efecto y  generen la sensación buscada. Ubíquelo en nueva hoja llamándola “Grafico13”    Deseamos observar en un nuevo grafico la participación de cada vendedor para las ventas  del año 2001, a través de un grafico circular con efecto 3d y seccionado. Añada un formato  similar a los gráficos anteriores para las diferentes áreas del mismo. Pero en este  deseamos que aparezca el nombre del vendedor y el porcentaje (ambos separados por un  espacio). Ubíquelo en nueva hoja llamándola “Grafico14”. El titulo de dicho grafico será  “Ventas 2001”.  

        Ejercicio Nº 2    Copie la siguiente tabla a una nueva hoja del libro con el cual esta trabajando y nómbrela  “Ejercicio 2”.                 Edades  Población de Artigas según sexo y grupo etario    Sexo  Total  0 a 3   4 a 5   6 a 14   15 a 19   20 a 24   25 a 29   30 a 49   50 a 64   65 a 79   80 y mas   ARTIGAS Total       78.019  5.426  3.139 14.626 Hombres  39.082  2.798  1.587 7.419

7.349 3.797

5.634 2.860

4.767  19.619  10.169 2.316  9.836  5.255

5.771 2.663

1.519 551

Mujeres 

3.552

2.774

2.451 

3.108

968

38.937  2.628  1.552

7.207

9.783 

4.914

  A partir de la siguiente tabla de datos deberá construir un grafico personalizado que represente  una “Pirámide Poblacional”. Dicho grafico deberá presentar en el Eje horizontal los Hombres a la  izquierda y las mujeres a las derechas, recuerde que solamente deberá aparecer en el grafico los  “grupos de edades” en el Eje vertical, y no los totales.     Deben aparecer títulos en ambos ejes y fundamentalmente las categorías en el Eje X.   En cada barra deberá figurar a la interna de la misma el valor para dicho sexo y grupo etario.  El formato de las diferentes áreas del grafico debe ser similar a las escogidas para el Ejercicio 1.               

Ejercicio Nº 3 (Opcional 1)    Precios  Ventas del Mercado  1  792  2  821  3  522  4  552  5  642  6  437  7  671  8  518  9  388  10  503  11  403  12  196  13  180    Se presentan los siguientes pares de Valores (Precios, Cantidades Transadas) que representan  Precios y cantidades transadas en determinado mercado. Se cree que dichas series se  correlacionan linealmente.    Se solicita si usted podría determinar el mejor ajuste lineal de dichas series a través de una línea  de tendencia, representar gráficamente la nube de puntos y dicha estimación lineal, la ecuación de  la misma y el coeficiente de correlación al cuadrado (R^2).       Ejercicio Nº 4 (Opcional 2)    Para el siguiente proyecto de campaña publicitaria se cuenta con la siguiente información:                                                Fechas  Tareas  Planificación  Contratación de Personal temporal Campañas Piloto  Campañas Focalizadas  Folleteria Barrial  Folleteria no especializada  Grafica especializada  Radio  Televisión         

Fecha Inicio 01/11/2005 16/11/2005 17/11/2005 18/11/2005 01/12/2005 01/12/2005 01/01/2006 15/01/2006 25/02/2006

Fecha Final 15/11/2005 30/11/2005 20/11/2005 30/12/2005 15/04/2006 15/01/2006 25/01/2006 15/02/2006 15/03/2006

Se solicita que realice para el informe de presentación de la campaña un Grafico Personalizado de  Gantt donde se visualicen las diferentes tareas del Proyecto en el Eje X, y en el Eje Y se muestren  las fechas involucradas.    El grafico deberá presentar:    • Titulo del mismo, y títulos de ambos ejes (no leyenda de referencias).  • Valores de duración de cada una de las tareas en el interior de cada barra  • Formatos de área de grafico y trazado idénticos.  • Formatos del área de las series en color azul.                                                                               

                   

COMPENDIO    PRUEBAS    MACROS    EN    EXCEL 

Prueba Módulo de Macros en Excel        Ejercicio 1          Dado el archivo que le proporcionó el docente. En la hoja 1 se encuentra un listado de cheques en  cartera de la empresa. Determinados cheques van venciendo, y se debe ir pintando de rojo los que  vayan venciendo (utilizar como parámetro la fecha actual del sistema (función DATE)).  Se pretende que mediante un macro, se obtenga la suma de los importes de cheques vencidos.          Ejercicio 2          En la hoja 2 del mismo archivo entregado por el docente, se encuentra una planilla de manejo de  stock. Se pretende que el alumno desarrolle un macro, para que en dicho listado se destaque de  color azul aquellos artículos que estén por debajo del stock mínimo requerido por la empresa.            Nota:      • Generar un botón sobre la hoja 1 para el macro del ejercicio 1.    • Generar un botón en la barra de herramientas para ejecutar el macro del ejercicio 2.                         

Prueba Final  Macros en Excel  Ejercicio 1    Realice una macro que guarde en una base de datos de “Liquidaciones de Sueldo”, los siguientes  datos:  9 Nombre y Apellido del Empleado  9 Cargo en la Empresa  ƒ Cadete  ƒ Auxiliar  ƒ Administrativo  ƒ Encargado  ƒ Gerente  9 Fecha de Pago  9 Días de Ausencia  Además deberá agregarse en la base de datos nuevas columnas referidas a:  9 Sueldo Nominal  9 Aportes  9 Sueldo Liquido    Los Sueldos Nominales son los siguientes:  ƒ Cadete; $4000  ƒ Auxiliar; $5500  ƒ Administrativo; $7000  ƒ Encargado; $12000  ƒ Gerente; $25000  Los Aportes son los siguientes:  ƒ Cadete; 10% sobre Sueldo Nominal  ƒ Auxiliar; 12% sobre Sueldo Nominal  ƒ Administrativo; 15% sobre Sueldo Nominal  ƒ Encargado; 18% sobre Sueldo Nominal  ƒ Gerente; 22% sobre Sueldo Nominal  El Pago del Salario en efectivo se realiza de acuerdo a la siguiente regla:  ƒ Si las inasistencias son nulas se lo premia con un incentivo de 5% sobre (Sueldo  Nominal‐ Aportes).  Sueldo Liquido = (Sueldo Nominal ‐ Aportes)* 1.05  ƒ Si falto 3 o menos días tiene una penalización de 2% sobre (Sueldo Nominal ‐ Aportes)   Sueldo Liquido = (Sueldo Nominal ‐ Aportes)* 0.98  ƒ Si falto mas de 3 días tiene una penalización de 5% sobre (Sueldo Nominal – Aportes)  Sueldo Liquido = (Sueldo Nominal ‐ Aportes)* 0.95                        La funcionalidad de dicha macro deberá incluir:  ¾ En la primer fila de la Base de datos, los títulos de cada variable.  ¾ La  posibilidad  de  utilizar  dicha  macro  en  repetidas  ocasiones  y  que  no  se  eliminen  antiguos registros  ¾ La posibilidad de elegir a continuación de cada registro estas 2 opciones:  ƒ Ingresar un nuevo registró a continuación.   ƒ Dejar de ingresar nuevos registros. 

Prueba Final  Macros en Excel  Ejercicio 1    Realice una macro que guarde en una base de datos de “Facturación de Llamadas”, los siguientes  datos:    Datos que cargara el usuario a través de un InputBox:  9 Fecha actual, (utilice la función Now())  9 Región de la Sucursal desde donde se realizo la llamada  ƒ Montevideo  ƒ Maldonado  ƒ Rocha  ƒ Rivera  9 País de Destino  ƒ Argentina  ƒ España  ƒ EEUU  ƒ Brasil  ƒ Otros  9 Minutos enteros de la llamada    A partir de los anteriores datos que cargara el usuario al ejecutar la macro, se deberán calcular los  siguientes campos automáticamente:  9 Costo por minuto  ƒ Argentina; 0,5 USD.  ƒ España; 2 USD.  ƒ EEUU; 1,5 USD.  ƒ Brasil; 1 USD.  ƒ Otros; 3 USD.  9 Costo Total  ƒ Costo Total = Costo por minuto * Minutos Enteros de la llamada  9 Descuento  ƒ Llamadas hechas desde la sucursal Rocha o Rivera, tienen un 5% de descuento.  9 Precio Final   ƒ Precio Final = Costo Total – Descuento    La funcionalidad de dicha macro deberá incluir:  ¾ En la primer fila de la Base de datos, los títulos de cada variable.  ¾ La  posibilidad  de  utilizar  dicha  macro  en  repetidas  ocasiones  y  que  no  se  eliminen  antiguos registros  ¾ La posibilidad de elegir a continuación de cada registro estas 2 opciones:  ƒ Ingresar un nuevo registró a continuación.   ƒ Dejar de ingresar nuevos registros.