GUIA 12 EXCEL Validacion de Datos y Tablas Dinamicas

7/22/2019 GUIA 12 EXCEL Validacion de Datos y Tablas Dinamicas

1/11


2/11

Centro deConfecciones

FORMATO PARA GUA DE APRENDIZAJEF9202007Versin 0

La Jamonera est especialmente preocupada por el proceso de introduccin de datos, ya que el personal deadministracin carece, por el momento, de los conocimientos necesarios para manejar Excel. Por ello, nos exige quela entrada de datos tenga los mximos controles para minimizar los errores.

Por otro lado, dado que los vendedores son muy activos y que pueden realizar las ventas en cada una de lasprovincias de las zonas anteriormente indicadas, se reciben multitud de partes de venta (uno por cada provincia en laque haya efectuado alguna venta, por lo que en el mes pueden aparecer mltiples partes por cada agente vendedor).Por ello, la empresa nos pide que diseemos un sistema mediante el cual pueda realizar, al finalizar cada mes, unanlisis en el que, con facilidad, pueda observar cules han sido las ventas y comisiones totales, por cada artculo, encada provincia o zona, de cada agente, etc.

SOLUCIONComo dice el enunciado, hemos de procurar que la entrada de los datos en el modelo de la hoja de clculo dispongade los mximos controles, de forma que se facilite la introduccin de la informacin y se minimicen los errores. Paralograrlo, haremos uso, entre otras posibilidades, de la validacin de datos en Excel.Son mltiples las posibilidades de diseo del libro que solucione esta problemtica. Hemos optado por introducir

por filas los datos correspondientes a cada parte de venta. En concreto, en la columna B se escoger la provincia de

una lista desplegable que, obviamente, deber contener las posibles provincias en las que el negocio est expandido,apareciendo, de manera automtica en la columna A la zona en que se realiz la venta.El administrativo teclear a continuacin, en la columna C, el cdigo del agente vendedor, debindose controlar queel cdigo es correcto, en otras palabras, que existe un vendedor cuyo cdigo es el tecleado; para facilitar el control,automticamente aparecer, en la columna D, el nombre del agente al que le corresponda el cdigo. Por ltimo,debern teclearse los importes de las ventas de jamones, embutidos, y deshuesados (columnas E, F y G,respectivamente), calculndose en la columna H las comisiones que de las ventas se deriven. Los porcentajes decomisiones se han situado encima de los rtulos, en el rango E3:G3.En primer lugar, en la celda A1 insertaremos el logotipo de la empresa Jamonera. Para ello, una vez seleccionada lacelda A1, ejecutamos el comando INSERTAR/IMAGEN/IMGENES PREDISEADAS. Si el grfico que se va autilizar como logotipo no estuviera ya creado en Excel como imagen prediseada, en lugar del ltimo subcomando seelegir IMAGEN. De la galera de imgenes prediseadas que se muestra, se deber elegir la categora Acadmico yseleccionar la imagen del logotipo; haciendo doble clic en ella o pulsando sobre el botn Insertar aparecer

inmediatamente la imagen en la hoja de clculo, a la que habr que reducirle el tamao para que aparezca tal comose muestra en la figura 1.


3/11



Figura 1

A continuacin, escribimos los rtulos de la hoja(rango B1:B2; F2;A4:H4), para, despus, documentarlos mediantecomentarios para la celda A4, ya que el procedimiento es similar para el resto.Seleccionamos la celda A4 y escogemos el comando INSERTAR / COMENTARIO; sobre la nota tipopos-it, escribimos el mensaje: en esta columna se mostrar la zona en que se haya realizado la venta.No hace falta teclear nada, sale sola!, u otro similar. Una vez creada la nota, al situar el puntero delratn sobre la celda, la nota aparece, facilitando la introduccin de datos (ver figura 2) Puede crearcomentarios, de forma similar, para el resto de celdas con rtulos.

Figura 2Para facilitar el control de la informacin, hemos optado por introducir, en otra hoja de clculo independiente,denominada Datos, los datos a controlar: provincias y zonas de venta, por una parte y cdigos y nombres de losagentes de ventas, por otra. El aspecto de la hoja se muestra en la figura 3.


4/11



Figura 3

Comenzamos por establecer los controles de introduccin de datos en la celda B5 de la hoja de Ventas, que debecontener la provincia. Hemos decidido crear una lista desplegable sobre la que el usuario deber escoger la provinciacorrespondiente, de forma que no est permitido introducir otra informacin distinta de la existente en la lista; losdatos de sta (las provincias), se encuentran recogidas en el rango D2:D11 de la hoja Datos, por lo que, al estarsituadas en una hoja distinta, es preciso asignar un nombre al rango en cuestin para que pueda ser usado en lavalidacin de celdas de otras hojas, por lo que, al estar situadas en una hoja distinta, es preciso asignar un nombre alrango en cuestin para que pueda ser usado en la validacin de celdas de otras hojas (es una limitacin de Excel;).Por ello, seleccionando la hoja Datos, marcamos el rango D2:D11 y ejecutamos el comandoINSERTAR/NOMBRE/DEFINIR, dndole el nombre Provincias.

Figura 4

Seleccionamos, a continuacin, la celda sobre la que establecer el criterio de validacin(B5 de la hoja Ventas) yejecutamos el comando DATOS/VALIDACION, apareciendo la ventana de opcin Configuracin, en la queseleccionamos el criterio de validacin.En Permitir escogemos Lista, indicando en Origen en que lugar del libro se encuentra la lista en cuestin; si estuvieraen la misma hoja, podramos usar las direcciones del rango, pero dado que est situado en una hoja diferente,tenemos, como dijimos previamente, que usar un nombre para el rango. En nuestro ejemplo, el nombre esProvincias. Marcamos la opcin Celda con lista desplegable para finalizar la creacin del criterio de validacin.


5/11



Ahora podemos marcar sobre la pestaa Mensaje entrante de la ventana Validacin de datos para crear un mensajeque aparezca en pantalla cada vez que el usuario vaya a introducir datos en la celda B5. El mensaje que hemosdefinido es el que ser recoge en la Figura 4.

El efecto de lo hasta ahora realizado sobre la celda B5 puede observarse en la figura 5. Al introducir datos en dichacelda, se despliega la lista con todas las provincias (algunas no se ven; basta con mover la barra de desplazamiento) yaparece el mensaje que indica al usuario qu debe hacer.

Figura 5

Figura 6


6/11



A pesar de la existencia de la lista desplegable, Excel posibilita que el usuario teclee la informacin directamentesobre la celda; en caso de que lo tecleado coincida con alguno de los elementos de la lista, nada pasar, pero si no esas, Excel no permitir que lo tecleado pase a ser el contenido de la celda.

Podemos crear un mensaje que advierte al usuario del error cometido, para lo que marcamos la pestaa Mensaje deerror, y en esta ventana indicamos la informacin oportuna. Figura 6.

Al pulsar sobre aceptar, se crear la lista y los mensajes. Con lo hasta ahora realizado, nos aseguramos de que eldato introducido en la celda B5 corresponder a una de las provincias vlidas. En A5 debemos crear una frmulaque, de manera automtica, muestre la zona de venta de las tres posibles, tal como indicaba el enunciado. Lasolucin propuesta es al siguiente:

=SI(B5;BUSCARV(B5;Datos!$D$2:$E$11;2;FALSO);)

A continuacin los administrativos de Jamonera teclean en C5 el cdigo del vendedorque aparece en la parte de ventas; hemos de controlar que el cdigo tecleado sea

correcto, por tanto, que sea el 1, 2, 3,4, o 5, cdigos relativos a nuestros cincovendedores Figura 6. Podramos haber optado por crear otra lista desplegable, pero eneste caso es ms rpido teclear el nmero y que Excel compruebe que es vlidomediante un control de validacin numrico.Sealamos la celda C5 y activamos DATOS/VALIDACION, apareciendo la ventanaValidacin de datos con la pestaa configuracin activada; sobre sta, indicamos lainformacin tal como aparece en la Figura 7.

Figura 7Adems, hemos creado un mensaje entrante (teclee el cdigo del agente vendedor) yotro de error (Ese agente no est dado de alta)En la celda D5 debe aparecer el nombre del vendedor cuyo cdigo se haya tecleado enC5; la frmula siguiente soluciona el problema:

=SI(C5;BUSCARV(C5;Datos!$A$2:$B$6;2;FALSO);)

En el rango E5:G5 se teclearn las ventas de los tres productos; vamos a controlar quelo tecleado sea un nmero decimal positivo, al tratarse de cifras de ventas en pesos. Lomejor, dado que las tres celdas deben tener el mismos control, es seleccionarlasprimero para, la continuacin, ejecutar el comando DATOS/VALIDACION, escogiendo


7/11



las opciones de control que aparecen en la figura 8, opciones que se asociarn a lastres celdas. Tambin hemos creado un mensaje de error (debe teclear una cifra

positiva y mayor que cero), que aparecer si se teclea algo distinto a un nmero o unamenor o igual a cero.

Para finalizar, calcularemos la comisin en la celda H5, mediante la formula:

=SI(C5;E5*$E$3+F5*$F$3+G5*$G$3;)

Figura 8

Con esto acabamos la creacin de la frmulas y criterios de validacin del rango A5:H5,correspondiente a la primera parte de la venta. Podemos copiar dicho rango para elresto de posibles partes de venta de este mes.Seleccionamos el rango a copiar (A5:H5) y ejecutamos el comando EDICION/COPIAR;ahora seleccionamos dnde pegar lo copiado (rangoA6:H100) y ejecutamos el comandoEDICION /PEGAR. Con ello, habremos pegado todo lo asociado al rango A5:H5 y, portanto, las frmulas, criterios de validacin y mensajes que hemos ido creando. La hojaest preparada para comenzar la introduccin de datos.

Para obtener un informe resumen para cada uno de los cinco agentes de venta que

muestre el total vendido de cada tipo de artculo y sus comisiones, por provincias, enuna o todas las zonas, usaremos la herramienta TABLAS DINAMICAS, especialmentetil para organizar y analizar fcilmente la informacin resumida a partir de los datoscontenidos en una hoja.

Para crear la tabla dinmica es necesario que los datos de la hoja que se utilicen estnorganizados en la hoja de clculo como una base de datos de Excel, es decir, que cadacampo de informacin, con su rtulo y valores, est en una columna. Esto ocurre en lahoja Ventas, donde disponemos de cada fila (registro) de toda la informacin recogida


8/11



de cada parte de venta, estructurada en los siguientes campos: zonas, provincias,cdigo, agente, jamones, embutidos, deshuesado y comisin.

Activada una celda del rango de la hoja Ventas que contiene los datos, se ejecutar elcomando DATOS/TABLAS DINAMICAS con el que invocamos al asistente para tablasdinmicas que nos ayudar en su construccin.

En el primer cuadro de dilogo que aparece, se selecciona, como fuente de los datos, laopcin Lista o base de datos de Microsoft Excel, ya que los datos a resumir estn en lahoja Ventas del libro JAMONERA.XLS que tendremos abierto en estos momentos.

Tras pulsar sobre el botn siguiente, accedemos al prximo cuadro de dilogo, en elcual indicamos el rango de celdas que contenga los datos fuentes, incluidos los rtulos

de los campos que se utilizarn como nombres de los mismos, figura 9. Podremosescribir el rango o sealarlo sobre la hoja ventas siendo $A$4:$H$100. A continuacin,elegimos la opcin Siguiente para pasar al siguiente cuadro de dialogo.

Figura 9

En el tercer cuadro de dilogo, crearemos el diseo de la tabla, haciendo clic sobre losbotones con los nombres de los botones con los nombres de los campos arrastrndoloshasta la seccin de la tabla que corresponda.El campo Provincia del rango de datos se ha colocado como campo de fila de la tabladinmica (haciendo clic con el ratn sobre su botn y arrastrndolo hasta la seccinFILA), para que sus elementos, aparezcan como rtulos de las filas de la tabladinmica.En la seccin DATOS se ubican los campos: jamones, embutidos, deshuesado ycomisin, los cuales contienen los valores que se desean resumir.

Al contener estos campos valores numricos con formato de moneda, Excel propone,por defecto, como operacin de resumen Contar, es decir, contar el nmero de

jamones vendidos, el nmero de embutidos vendidas etc. En este caso la funcin quedeseamos utilizar para calcular los valores del rea de datos de la tabla dinmica no esContar sino suma. Luego tendremos que cambiar la funcin Contar por la funcin sumapara los cuatro campos de la seccin datos. Para ello, pulsamos dos veces el ratnsobre el campo y desde el cuadro campo de la tabla dinmica elegimos en el cuadroresumir por la opcin suma. Figura 10


9/11



Figura 10

En la seccin PAGINA hemos colocado los campos Zona y Agente, para que la tablatenga estos dos campos como listas desplegables, desde los que se podr elegir aquelelemento (zona y agente) de los que se desea obtener el resumen o agrupacin de lasventas

En la figura 11 se recoge el cuadro de dilogo definitivo del paso tercero del asistentepara tablas observarse, los dos campos de dinmicas:

Figura 11

En el cuarto y ltimo paso del asistente de tablas dinmicas, le indicamos que la tabla acrear se ubique en una Hoja de clculo nueva, que denominamos Anlisis ventas y quese muestra en las figuras 12 y 13. Como puede observarse, los dos campos de pgina(zona y agente), aparecen como listas desplegables, desde los que se puede elegiruna zona en concreto o todas y un agente de ventas determinado o todos ellos. En elprimer informe que se adjunta figura 12, hemos seleccionado la zona AndalucaOccidental y el agente Mariano Moreno, por lo que la tabla dinmica muestra las ventasdel agente Moreno, agrupadas por las provincias de Andaluca occidental en dondetiene alguna venta,


10/11



Figura 12

En el segundo informe, hemos seleccionado todas las zonas y el agente MarianoMoreno, por lo que la tabla dinmica muestra las ventas del agente Moreno, agrupadaspor las provincias de las tres zonas en las que haya realizado alguna venta.

Figura 13

Si los datos fuentes varan, deber ejecutarse el comando Actualizar datos del mencontextual o desde la barra de herramientas de manejo de las tablas dinmicas, paraque se recalcule la tabla.

EVALUACION:

Despus de haber desarrollado el ejercicio, colcalo en la carpeta de evidencias de laplataforma, y all ser revisado para su calificacin.


11/11



5. RECURSOS

Computador con Microsoft Excel y acceso a Internet.

DOCENTE:RICARDO JIMENEZ RODRIGUEZRITA RUBIELA RINCONOLIMPO CARDENASOSCAR JAVIER OSPINALADY SUAREZ

TIEMPO: 12 horas

FECHA: Diciembre 2009

Documents

GUIA 12 EXCEL Validacion de Datos y Tablas Dinamicas