Libro Graficos Inteligentes en Excel

Primera Edición

2018

Carlos Augusto López Murillo Microsoft Office Expert

La información contenida en este libro procede de una obra original entregada por el autor. Su fin es exclusivamente exclusivamente didáctico. Este trabajo se publica con el reconocimiento expreso de que se está proporcionando una información que tiene solo la intención de servir s ervir como guía general.

Página web: www.compexcel.net Canal Youtube: https://www.youtube.com/user/MrSrAnalista Fans Page: www.facebook.com www.facebook.com/compexcel /compexcel Email: [email protected] Teléfono: 57 7 6436694 Bucaramanga – Bucaramanga – Colombia Colombia ISBN: 978 – 978 – 958 958 48 4124 7

Primera Edición Está prohibida la reproducción total o parcial de este libro, sin el permiso previo de forma escrita del autor Autor: Carlos Augusto López Murillo Edición: Carlos Enrique Rueda Arango

Dedicatoria A mi papá Jairo López y a la memoria de mi mamá Gilma Murillo.

Tabla de contenido 1.

Gráfico con segmentación de datos............... ........ .............. .............. .............. .............. ............... ............... .............. ........... .... 13

2.

Gráfico con barra de desplazamiento. desplazamiento. .............. ....... ............... ............... .............. .............. .............. ............... ............... ....... 20

3.

Gráfico Gráfico Con Imágenes Imágenes ....................................................................... .......................................................................................... ................... 30

4.

Gráfico Con Cuadro Combinado .............. ....... .............. .............. .............. .............. ............... ............... .............. .............. .......... ... 37

5.

Gráfico con cuadro combinado y botones de opción .............. ....... .............. .............. .............. .............. ....... 44

6.

Gráfico Con Cuadro Combinado Para Varias Tablas ............... ....... ............... .............. .............. .............. ........... 52

7.

Gráficos Gráficos con Fx Indirecto Indirecto ........................................................................ ..................................................................................... ............. 59

8.

Gráfico con Listbox, Índice y combobox............... ........ .............. .............. .............. .............. ............... ............... ............ ..... 71

9.

Gráfico Gráfico Tipo Termómet Termómetro ro ....................................................................... .................................................................................... ............. 86

10. Gráfico Tipo Termómetro Con Botones De Opción ............... ....... ............... .............. .............. .............. ........... 91 11. Gráfico Tipo Termómetro con formato Condicional ............... ........ .............. .............. .............. ............... ........ 99 12. Gráfico Tacómetro con tipo de gráfico Circular ............... ........ .............. .............. .............. ............... ............. ..... 109 13. Gráfico Tacómetro con tipo de gráfico anillos .............. ....... ............... ............... .............. .............. .............. ......... 115 14. Gráficos con casillas de Verificación .............. ....... .............. .............. .............. .............. ............... ............... .............. ........... 132 15. Gráfico Gráfico con la Fx Desre Desreff ........................................... ............................................................................... .......................................... ...... 140 16. Gráficos Imágenes, Macros y Fx Sumar.Si.Conjunto ............... ........ .............. .............. .............. ............. ...... 159 17. Gráfico Gráfico con Macros Macros ..................................................................... ............................................................................................ ....................... 177

6

Ya no queremos asistir a salas de juntas j untas para analizar la gestión de un departamento cualquiera de la empresa donde el responsable de esta socialización tiene preparado su informe apoyado en 30 o más diapositivas, y Usted y yo sabemos que, si las tablas y/o gráficos no ayudan a la lectura y al análisis de la información debido a la complejidad y al volumen de la misma, nos estamos adentrando a la caverna de la pereza y del aburrimiento. Por eso las nuevas gerencias buscan que sus equipos de trabajo elaboren modelos con gráficos amigables, que permitan deducir de una manera fácil y sencilla el comportamiento de las cifras en estudio y por ende que se conviertan en nexos nex os de apoyo para la toma de decisiones. Lo que se necesita ahora es que el profesional esté en capacidad de elaborar tableros de mando o tableros de control que combine tablas y gráficos y que, por medio de un sólo clic, éstas enfoquen la situación precisa que se quiere analizar, a lo que algunos llaman “Golpe de vista”. Es preciso que comprenda que graficar por graficar no tiene sentido, graficar debe ser esa tarea que tiene la importancia de medir, comunicar, conectar, sensibilizar e identificar el desempeño de un negocio. Elaborar gráficos inteligentes dejará una excelente impresión al equipo de trabajo. Es muy importante que Usted haga los modelos en el orden que se presentan en el libro, esto obedece a que para el estudio y entendimiento de temas que se encuentran en capítulos finales, necesita comandos, funciones y herramientas que se trataron en e n páginas anteriores.

7

8

Para la elaboración de los modelos que se plantean en este libro, es necesario que Usted tenga presente las siguientes recomendaciones:

1.

Realice los modelos planteados en el orden que se presenta en el libro, ya que a medida que avance en la lectura del mismo, encontrará nuevos comandos y nuevos retos.

2.

Debe habilitar la ficha Programador (Excel versión 2010) o desarr ollador (Excel versión 2013 – 2016). – 2016). Esto es: es: clic en “opciones” que se encuentra en ficha “Archivo”, después dé clic en la opción “Personalizar cinta de opciones” y habilitar la casilla “Programador” o “Desarrollador”.

3. En la ficha programador o desarrollador se encuentra un botón llamado “Insertar” que muestra dos categorías de controles: “Controles de Formulario” y “Controles Active X”. En los distintos capítulos a tratar, se trabajará únicamente con los “Controles de formulario”, ya que los “Controles Active X”, se usan para las macros y exigen y exigen para su funcionamiento sentencias de visual Basic

9

A continuación, se explicará el funcionamiento de los controles de formulario usados en los gráficos de este libro.

Este control tiene dos eventos que son: Verdadero y Falso. Se dice que el control está en evento “verdadero” si aparece el símbolo (✓) y está en evento “Falso” si éste no aparece. Para pintar este control solo basta con seleccionarlo y dar clic en la posición de la hoja de Excel Ex cel donde quiere que aparezca.

El control “Casilla de verificación” debe estar vinculado a una celda, esto significa que si se habilita o deshabilita, entonces en la celda que se ha de vincular, mostrará un mensaje de acuerdo al estado del mismo, y para poder vincular el control con la celda elegida, debe de be dar clic derecho sobre este control, seleccione la propiedad “Formato de control” y de la ventana que aparece, indíquele a Excel la celda que debe quedar q uedar vinculada. Cada casilla de verificación debe estar vinculada a una celda diferente.

10

En el momento que se activa el control “Casilla de verificación”, la celda vinculada mostrará la palabra “verdadero”, y si por el contario lo deshabilita saldrá la palabra “Falso”. Además, es importante recalcar que este control es de opción múltiple, lo que significa que es posible seleccionar o activar varios controles tipo casilla verificación.

Este control es de opción única, esto significa que de los diferentes controles de opción que se tienen en el modelo de Excel, sólo es posible seleccionar se leccionar o habilitar uno de ellos. Todos los controles de opción que se pintan en la hoja de Excel deben están vinculados a una misma celda y el dato arrojado en esta celda será un número entero que empieza con el número 1. Este número indica el control de opción que se habilitó, así por ejemplo: si se tiene tres controles y la celda vinculada dio como resultado el número tres, indica que el control habilitado fue el tercero.

Para configurar este control dé clic en cualquiera de los controles de opción y seleccione la propiedad “Formato de control”. En el argumento “Vincular con con la celda”, seleccione cualquier cualquier celda que esté vacía. Automáticamente todos los demás controles de opción del modelo de Excel quedan vinculados a la misma celda.

11

Este control tiene la particularidad de desplegar una lista y como todo control también debe vincularse a una celda. Para pintar este control solo basta con seleccionarlo y con clic sostenido arrastrar el mouse hacía la derecha y hacía abajo con el fin de indicarle a Excel el tamaño de éste. Para configurar el “Cuadro combinado”, dé clic derecho sobre este control y seleccione la propiedad “Formato de control”. El asistente solicita solicita dos argumentos que son: “Rango “Rango de entrada” y “Vincular con la celda”. “Rango de entrada” se refiere re fiere al grupo de celdas que tiene los datos que ha de desplegar la lista; “Vincular con la celda” se refiere a la posición de celda que cambiará de valor de acuerdo al dato seleccionado. Por ejemplo, si el rango de entrada son ciudades, entonces la celda vinculada mostrará un numero entero positivo que inicia en 1 y que indica que posición de ciudad se seleccionó, si fue la primera o segunda o tercera y así sucesivamente.

12

Gráfico con segmentación de datos 13

Capítulo 1

Gráfico con segmentación de datos

La intención del modelo es vincular tablas dinámicas con segmentación de datos para generar gráficos inteligentes.

Para poder desarrollar este modelo de grafico es necesario contar con una tabla que registre un buen número de transacciones, por lo tanto, debe crear una matriz como ejemplo, que tiene como fin el ingreso de las ventas realizadas en un centro de capacitación especializada. La intención del modelo - ejemplo es generar dos tablas dinámicas y que por medio de dos segmentaciones de datos se pueda analizar la información. Empiece por crear la estructura de la tabla así con 100 o más registros.

Una vez creada la tabla debe proceder a generar 2 tablas dinámicas. En la primera de ellas debe mostrar cual fue el valor facturado por cada curso, por lo tanto, cruce el campo “Curso” y el campo “Valor”. La segunda tabla dinámica debe informar cual es el valor facturado por sede, esto es, cruzar el campo “Sede” y el campo “Valor”.

14

Capítulo 1


Para realizar la primera tabla dinámica, realice los siguientes pasos: Clic en el botón “Tabla Dinámica” de la ficha “Insertar”, verifique que el rango de la tabla dinámica coincida con el tamaño de la matriz y dé clic en el botón “Aceptar”.

En la nueva hoja que genera Excel, seleccione se leccione del panel que sale a la derecha derec ha las casillas de verificación de los campos “CURSO” y “VALOR”. La L a tabla dinámica debe quedar como se ve en la imagen.

15

Capítulo 1


Ubicado en cualquier celda de la tabla dinámica, genere un gráfico tipo columna. En el gráfico se observan dos botones que se refieren a los campos que se insertaron en la tabla dinámica (Suma de valor, Curso), los cuales se pueden ocultar dando clic derecho sobre cualquiera de estos dos y seleccionando la propiedad “Ocultar todos los botones de campos en gráfico”.

Ahora debe crear la segunda tabla dinámica recordando que en ella van los campos “Sede” y “Valor”, y una vez creada ésta, proceda a insertar un gráfico “tipo circular” y oculte los botones de “campo de gráfico”. La tabla y el gráfico dinámico deben quedar como se ve en la imagen.

Inserte una nueva hoja de Excel y a ésta le copia los dos gráficos gráf icos a partir de la fila 8, ya que se necesitan las primeras filas para colocar allí los botones de segmentación s egmentación de datos.

16

Capítulo 1


La segmentación de datos es una herramienta que aparece desde la versión 2010 y permite filtrar información de una tabla de acuerdo a un argumento escogido. Hace lo mismo que el campo “Filtro”, de la tabla dinámica, pero desde una ventana con botones. El modelo que se plantea solicita que se pueda visualizar en dos gráficas el comportamiento de las ventas de cada curso y el comportamiento de las ventas en cada sede, permitiendo filtrar la información por Vendedor y por Bimestre; por lo tanto, se necesita de dos segmentaciones de datos que son: la segmentación del campo “Bimestre” y la del campo “Vendedor”. Para insertar estas segmentaciones debe realizar rea lizar los siguientes pasos: Seleccione el gráfico tipo “Columna” (Gráfico por cursos), luego dé clic en el e l botón “Insertar Segmentación de datos” datos ” que se encuentra en la ficha “Analizar”. De la ventana que aparece seleccione los campos “Vendedor” y “Bimestre” que son s on los campos que deben mostrarse como segmentación. Acomode las dos segmentaciones de datos en la parte superior de la hoja

El modelo debe ir como se ve en la imagen.

17

Capítulo 1


Para apreciar la funcionalidad de la herramienta “segmentación de datos”, dé clic en el botón “Miriam”, y observe que la gráfica tipo columna mostrará las ventas realizada en cada uno de los cursos por esta vendedora; si escoge otro nombre, la gráfica automáticamente se actualizará. Lo ideal sería que, al momento de seleccionar un vendedor, v endedor, las dos gráficas muestren el valor facturado por curso y por sede que realizó este est e asesor. Para vincular la segmentación de datos “Vendedor” con la gráfica tipo circular debe realizar los siguientes pasos: seleccione la segmentación “Vendedor”, luego dé clic en el botón “Conexiones de Informe” que se encuentra en la ficha “Opciones”. “O pciones”.

Excel indica que la segmentación de datos solo está afectando a la gráfica de la primera tabla dinámica y que, para vincular esta segmentación de vendedor, con la segunda tabla dinámica debe habilitar la casilla de verificación de la tabla dinámica 2.

18

Capítulo 1


Ahora la segmentación de datos “Vendedor”, está vinculada está vinculada a los dos gráficos dinámicos. Debe realizar este mismo procedimiento para vincular La segmentación de datos “Bimestre” con los dos gráficos dinámicos. Este mismo proceso lo debe realizar con cada una de las segmentaciones de datos que insertó. Para cambiar la apariencia de la segmentación de datos, debe tener ésta seleccionada y en la ficha “opciones”, aplicar los cambios a su gusto…

¡¡Súper!!

19

1. 2.

Gráfico con barra de desplazamiento 20

Capítulo 2

Gráfico con barra de desplazamiento desplazamiento

Claudia 10000

Barra de desplazamiento 8032

8000 5330

6000 4000

2899

3835

2000 0 Año Añ o 20 2010 10 Añ Año o 20 2011 11 Añ Año o 20 2012 12 Añ Año o 20 2013 13

Este modelo de gráfico le permite observar individualmente el comportamiento de las ventas de un asesor en cada periodo, y la función de la barra de desplazamiento, es ir cambiando la serie que desea graficar (serie Juan, Serie Carlos, etc.), además de un formato condicional que resalta en color, el registro seleccionado tomado como referencia en la gráfica. Para empezar, realice la siguiente tabla con los datos como se s e ve en la imagen.

21

Capítulo 2


Ahora seleccione desde la celda “A1” hasta la celda “E2”, copie y pegue este rango en la celda “G1”.

Este nuevo rango “G1:K2” que se ha copiado, es lo que realmente ha de graficarse. Esta es una tabla paralela a la que he llamado “ ” o la tabla , pues el usuario final no sabrá que ella existe, pero que es la responsable del gráfico. Ahora, con este nuevo rango, cree un gráfico tipo “Columna” (ALT(ALT - F1).

Proceda a insertar el control de formulario llamado “Barra de Desplazamiento” que se encuentra en el botón “Insertar” de la Ficha “Programador”. Para esto dé clic clic en el botón botón “Barra de desplazamiento”, luego ubique el puntero del mouse en el lugar donde desea que aparezca el control y con clic sostenido arrastre. Este control puede ir en posición Horizontal o Vertical.

22

Capítulo 2


Arrastre la barra de fórmula hasta el final del área del grafico

Para configurar las propiedades del control “Barra de desplazamiento”, dé clic derecho sobre éste y seleccione la propiedad “Formato de Control”. (Ver referencia)

Escriba los límites asignados a este control tal como se ve en la imagen.

23

Capítulo 2


Corresponde al número más pequeño que debe arrojar el control. Es el número mayor que ha de arrojar el control. Se colocó el número 8 porque la tabla tiene 8 registros. Significa que la barra de desplazamiento cambiará del número 1 al 8 de uno en uno. Se refiere a la celda donde se mostrará el número que arroja la barra de desplazamiento. Verifique que al manipular la barra de desplazamiento la celda G3 v aya cambiando de número.

Como puede observar la barra de desplazamiento aún no está afectando la gráfica; por eso es necesario necesario vincular la celda “G3” (celda afectada por el control), con la celda “G2” (celda que hace parte de la gráfica). Esto es, sí en la celda “G3” aparece el número 4, entonces en la celda “G2” tendrá que mostrarse el nombre del cuarto asesor de la tabla, pero pero sí en la celda “G3” aparece el número 7, entonces en la celda “G2” tendrá que mostrarse mostrarse el nombre del séptimo asesor de la tabla. Para este procedimiento es necesario, entre otras, usar la función “Índice” de la categoría “Búsqueda y Referencia”. La función “

”, identifica el dato de un vector que ocupa “x” posición.

Por ejemplo, sí la posición es 1, la fx índice sabrá que éste corresponde a Juan, sí es tres, sabrá que corresponde a María. Ubicado en la celda “G2”, ejecute la fx “Índice” y coloq ue los argumentos como aparece en la imagen. Al momento de ejecutar la Fx índice, saldrá una ventana con dos argumentos. Debe seleccionar el primero, que significa que la búsqueda la hará en una s ola tabla, la segunda opción se escoge si la búsqueda se ha de realizar en varias tablas.

24

Capítulo 2


En el argumento “Matriz” debe quedar el rango “A2:A9”, que es el rango donde están los nombres de los asesores, y en el argumento “Num_Fila”, debe quedar la celda “G3”, que es la celda que cambia de número a medida que se manipula la barra de desplazamiento. Para finalizar dé clic en el botón “Aceptar” Observe que a medida que se manipula la barra de desplazamiento, sólo el título de gráfico va cambiando. Para que cambien las cifras, debe apoyarse con la Fx “BuscarV”, de t al manera que se le indique a Excel que busque el nombre que aparece en la l a celda “G2” en la tabla que va desde “A1” hasta “E9” y coloque el dato de la columna que corresponde. La función se encarga de buscar verticalmente un valor en la primera columna de una tabla y encontrado éste devuelve el valor que esté en la misma fila a partir de la columna que se indique. Por ejemplo, si se está buscando el nombre Juan, la función BuscarV sabrá que este nombre se encuentra en la fila No 2, y una vez identificada la fila, la función podrá conocer cada uno de los datos que se encuentre a la derecha de éste. Ubicado en la celda “H2”, ejecute la función “BuscarV” y coloque los argumentos tal como se ven en la imagen. Verifique que los argumentos “Valor Buscado” y “Matriz buscar en” queden fijos o absolutos, ya que la fórmula ha de copiarse hacia a la derecha y estas posiciones no deben cambiar.

25

Capítulo 2


es el dato a buscar. Se refiere a la tabla donde ha de buscar el dato Es un numero entero, que a nuestro caso será el numero dos porque de la tabla seleccionada se necesita el dato que está en la segunda columna (Año 2010) Se coloca el número cero, que indica que la búsqueda debe ser literal o exacta. Si manipula la barra de desplazamiento observará que en la gráfica cambia el título del gráfico y el tamaño de la primera columna, que corresponde al año 2010. Ahora copie la fórmula que está en la celda “H2” hasta la celda “K2” y una vez copiada la f órmula, órmula, debe cambiar el número número del argumento “Indicador “Indicador columnas”. Para el año 2010 es el número número 2, para el año 2011 el número 3, para el 2012 el número 4 y para el 2013 el número 5. Una vez más manipule la barra de desplazamiento y observará que ahora todas t odas las columnas del gráfico cambian de tamaño.

26

Capítulo 2


Observe detenidamente los gráficos de Andres y Patricia. En el gráfico de Andrés el eje principal va hasta 8.000 y en el de Patricia va hasta los 10.000. Esto sucede porque Excel se autoajusta a los datos ha graficar. Para fijar los límites de los ejes, dé clic derecho en el eje principal, seleccione la opción “dar formato al eje” y en el “límite mínimo” ponga cero y en el “límite máximo” ponga 10.000. Esto obligará a Excel a no autoajustar los valores del eje principal.

Por último, debe usar la herramienta “Formato condicional” para resaltar con un color cualquiera el registro de la tabla que se está graficando. Para esto realice el siguiente procedimiento: Seleccione desde la celda “A2” hasta la celda “E9” luego de clic en el botón “Formato condicional” que se encuentra en la ficha “Inicio”.

27

Capítulo 2


Del menú que se despliega, seleccione la opción “Nueva Regla” y del asistente que aparece seleccione la opción “Utilice una fórmula que determine las celdas para aplicar formato”. Diligencie la caja de Texto como se ve en la imagen

La fórmula se debe interpretar de la siguiente manera: Cambiará el color de la fila correspondiente al rango A2 a E9 si y solo si el dato de la celda G2 es igual a cualquier nombre de la columna A. Dé clic en el botón “Formato” y escoja el e l color de relleno y para finalizar dé clic en “Aceptar”.

28

Capítulo 2


Verifique que el registro cambie de color de a medida que se mueva con la barra de desplazamiento. No olvide ocultar la tabla “trampa”, o tabla “espejo”, ya sea colocando encima de ella el gráfico o cambiando la fuente y el relleno de celda con color blanco. Para mover la barra de desplazamiento se debe hacer en dos tiempos: debe dar clic derecho sobre ella, luego escape para quitar el menú y seguido a esto ya se puede mover. ¡¡Espectacular!!

29

Gráfico con Imágenes 30

Capítulo 3

Gráfico con imágenes

La intención de este gráfico es la de comparar el comportamiento de las ventas de algunas camisetas del futbol, con la novedad que el gráfico no tendrá columna de relleno como es la costumbre, sino una imagen, y además un eje secundario para referenciar los porcentajes de participación. Para empezar cree la tabla como se aprecia en la imagen. En la columna F, haga h aga una sumatoria de las ventas de los tres almacenes almac enes y en la columna G calcule el % de participación. La fórmula que debe quedar en la celda F3 es la siguiente: =SUMA(C2:E2) La fórmula que debe quedar en la celda G3 es la siguiente: =F3/SUMA($F$2:$F$7)

Ahora proceda a seleccionar el rango de celdas B2 a B7 y F2 a G7, que serán será n las celdas que Excel tomará como referencia para la elaboración del gráfico y luego presione las teclas Alt F1, para insertar el mismo. Se observa que el gráfico dice en la sección “leyenda”, que está graficando dos series que son: Total y %, pero solo se aprecia el total. La explicación del por qué no se visualiza la serie %, obedece a que los valores de la columna “% “, son valores muy cercanos a cero.

31

Capítulo 3


Para crear el eje secundario en e n la gráfica, seleccione el área del gráfico, luego debe dar clic en el botón “Cambiar tipo de gráfico” de la ficha “Diseño”.

En la ventana que aparece debe seleccionar la última propiedad que dice “Cuadro combinado” y deje las propiedades tal como se aprecian en la imagen. Para finalizar de clic en el botón “Aceptar”.

32

Capítulo 3


Paso a seguir seguir es ampliar el ancho de cada una de las columnas. Esta propiedad se llama “Ancho del intervalo” y se realiza de la siguiente manera: Clic derecho sobre cualquier columna, luego clic en la opción que dice “Dar formato a la serie de datos” y aparecerá un pa nel a la derecha que permitirá ajustar el ancho. Es importante recalcar que el ancho del intervalo no hace referencia al ancho de la columna, sino al espacio que hay entre una y otra columna, por tanto, si se quiere columnas anchas, debe reducir el ancho del intervalo. Sugiero 40 ó menos.

33

Capítulo 3


La gráfica debe quedar como se ve en la imagen

El paso final reemplazar el color de relleno de cada una de las columnas, por las imágenes respectivas, y para ello sugiero ubicar en alguna carpeta las imágenes a usar en el gráfico.

34

Capítulo 3


Para reemplazar el color de relleno de la primera columna por la imagen de la camiseta del Barcelona, debe realizar los siguientes pasos: clic en la primera columna, luego hacer una pausa y nuevamente clic sobre ésta, de tal manera que esto permitirá que solo la primera columna quede seleccionada, luego se da clic derecho sobre la columna y se selecciona la propiedad “Formato punto de datos”

Aparecerá un panel a la derecha de la ventana, ven tana, del cual seleccionará el botón “Relleno y Línea”, y del menú que se despliega se escoge la opción “Relleno con imagen o textura”.

35

Capítulo 3


Ahora de clic en el botón “Archivo” y seleccione la imagen que debe quedar reemplazar el color de relleno.

Repita este procedimiento con cada una de las columnas restantes. re stantes.

36

Gráfico con Cuadro Combinado

37

Capítulo 4

Gráfico con cuadro combinado

Asesor

Trim1

Trim2

Trim3

Trim4

Jairo López

7.877.027

7.249.102

2.359.317

7.868.214

Gilma Murillo

7.034.536

4.220.793

5.620.588

4.927.898

Tatiana Martinez

3.423.728

9.118.257

9.721.186

1.325.847

Alvaro Uribe

4.169.367

4.869.012

2.832.980

1.347.594

Mario Torres

9.845.901

4.411.306

8.175.653

4.176.041

Luisa Tejada

3.884.052

4.038.202

7.844.770

5.210.707

10.000.000 9.000.000 8.000.000 7.000.000 6.000.000 5.000.000 4.000.000 3.000.000 2.000.000 1.000.000 -

7.877.027

7.868.214

7.249.102

2.359.317

Trim1

Trim2

Trim3

Trim4

Este gráfico cumple con la misma intención del gráfico anterior; la novedad es hacerlo mediante el uso del control de formulario “Cuadro Combinado”, el cual le permite seleccionar el asesor que ha de mostrarse en la gráfica. Para empezar, debe crear la siguiente tabla:

Creada la la tabla, seleccione desde la celda “A1” hasta la la celda “E2”, copie este rango y péguelo en la celda “G1”. Recuerde que este rango es el que se ha de graficar, y que le he llamado “ ”, o también con el nombre “ ”, porque el usuario no sabrá que está es la matriz que realmente se está graficando. Ahora, con este nuevo rango, cree un gráfico tipo “Columna” (ALT(ALT - F1).

38

Capítulo 4


Luego inserte el control “Cuadro Combinado” Combinado” (segundo botón de la primera fila), dentro del dentro del área del gráfico. No olvide quitar el título de gráfico.

9.000.000 8.000.000 7.000.000 6.000.000 5.000.000 4.000.000 3.000.000 2.000.000 1.000.000 Trim1

Trim2

Trim3

Trim4

Tenga presente que todo “control de formulario”, debe configurarse, y para ello debe dar clic derecho sobre el control y seleccionar la propiedad “Formato de Control”. En esta venta na, configure los argumentos como se muestra a continuación

39

Capítulo 4


: Son las celdas que tienen los datos que debe mostrar el cuadro combinado, para nuestro caso el rango es “A2:A7” : Se refiere a la celda donde se mostrará el número que arroja el cuadro combinado; para nuestro caso, tome como referencia la celda “F1”. Es necesario comprobar si el “Cuadro Combinado” ha quedado bien configurado, entonces dé clic en el ascensor del cuadro combinado y elija un nombre de la lista. En la la celda “F1”, debe aparecer un número que va de 1 a 6, ya que esa fue la cantidad de nombres que se ha seleccionado en el “Rango de Entrada”. Sí el número es 1, es porque se escogió el primer nombre, si sale 2, es porque escogió el segundo nombre y así sucesivamente. Ahora es necesario vincular este número que sale en e n la celda “F1” con la tabla “trampa” “ trampa” o tabla “espejo”. Por ejemplo: Sí en la celda “F1” está el número 1, entonces en la celda “G2” debe aparecer el nombre “Jairo López”. Sí en la celda “F1” “ F1” está el número 4, entonces en la celda “G2” debe aparecer el nombre “Álvaro Uribe”, y así as í sucesivamente. Para realizar este procedimiento, debe usar la Fx “Índice”. La función “

”, identifica el dato de un vector que ocupa “x” posición.

Ubicado en la celda “G2”, ejecute la fx “Índice” “ Índice” con los siguientes argumentos:

40

Capítulo 4


El asistente se lee de la siguiente manera: “De los nombres que están entre las celdas A2 y A7, el que está en la primera posición del vector es Jairo López”. Ahora apoyado con la Fx “BuscarV”, puede conocer los valores de cada uno de los trimestres, tomando como dato de búsqueda o referencia el valor de la celda “G2”, pero este procedimiento ya se explicó en el capítulo anterior; por lo tanto, voy a plantear otra solución usando las funciones Índice y Coincidir. Ubíquese en la celda “H2” y ejecute la función “Índice” con los argumentos como se ven en la imagen.

: Corresponde al rango “B2:B7”, que hace refer encia a las cifras de cada trimestre. trimestre . : Corresponde al número de la fila del asesor ases or que se quiere consultar. En el argumento “Num_columna”, debería ir el número 1, porque el título “Trim1” corresponde a la posición 1 de la matriz seleccionada “B2:E7”, pero la idea es que, para no cambiar manualmente ese argumento al copiar horizontalmente la fórmula, me apoyaré de la fx “Coincidir”, para que encuentre el número correspondiente, esto es, que el título “Trim1” corresponde a la columna 1, el título “Trim2” a la l a columna 2, y así sucesivamente. La función pertenece a la categoría Búsqueda y Referencia R eferencia y se encarga de arrojar arr ojar un número entero positivo que indica que posición ocupa un dato en un vector. Para nuestro modelo, es necesario la función “Coincidir” dentro de la función “Índice”, entonces ubicado en el argumento “Num_columna”, dé clic en el ascensor del cuadro de nombres, busque y seleccione la función “Coincidir”. : Procedimiento por el cual una función llama a otra o varias funciones dentro de la misma expresión.

41

Capítulo 4

Gráfico con cuadro combinado Cuadro de Nombres

Al momento de anidar, Excel sale temporalmente de la función “Índice” para ubicarse en la función “Coincidir”. Diligencie los argumentos como se muestran en la imagen. No presione el botón “Aceptar”.

: Hace referencia al dato que se quiere buscar. Corresponde al vector donde se encuentra el valor buscado. : Se coloca el número cero, que indica que la búsqueda debe ser literal o exacta. La respuesta del asistente de la f x coincidir es 1, y se lee así: “El valor valor buscado “Trim1”, en el vector “B1:E1”, ocupa la primera posición”. Una vez diligenciado los argumentos de “Coincidir”, regrese a la función Inicial, dando clic en el nombre de la función que se encuentra en la barra ba rra de fórmulas.

42

Capítulo 4


Los argumentos Matriz y Num_Fila deben quedar fijos, porque no pueden cambiar de posición a la hora de arrastrar la fórmula a la derecha. Una vez copiada la fórmula, hasta la celda K2, verifique que el gráfico cambia a medida que se escoge nombre del control cuadro combinado.

43

Gráfico con cuadro combinado y botones de opción 44

Capítulo 5

Gráfico con cuadro combinado y botones de opción

La intención de este modelo es vincular un “Cuadro Combinado” y dos “Botones de Opción”, en un gráfico, pero podrían ser varios. El cuadro combinado permitirá seleccionar qué serie ha de graficarse (Norte, sur, etc) y los botones de opción permitirán seleccionar de qué tabla se toman los datos (Tabla Café, Tabla Azúcar). Para empezar debe crear las tablas café y azúcar como se ven en la imagen. Ahora seleccione desde la celda “A1” hasta “C2”, copie y pegue este rango en la celda “E1”.

La tabla cuyo rango va desde “E1” hasta “G2”, es lo que hemos venido llamando “la trampa”, o tabla “espejo” que es la tabla que realmente se ha de graficar, pero que el usuario no ve. Ubicado en la celda “E1”, inserte un gráfico tipo “columna” con los controles, como se ve e n la siguiente imagen. Recuerde que los controles se encuentran en el botón “Insertar” de la ficha “Programador”.

45

Capítulo 5


100.000 80.000 60.000 40.000 20.000 Semestre 1

Semestre 2

El botón de “Opción1” tendrá como nombre “Café” y el botón de “Opción2”, “Azúcar”. Dé clic derecho sobre el botón de “Opción1” y del menú que aparece en pantalla seleccione “Modificar Texto”. Repita este procedimiento para el botón de “Opción2”. El “ es un control de opción única; esto significa que de todos los botones de opción, únicamente podrá ser seleccionado o habilitado uno de ellos. Al ser el botón de opción un control de opción única, significa que una y solo una misma celda estará vinculada a cada uno de los botones de opción. Para configurar un botón de opción dé clic derecho sobre cualquiera de estos dos, y seleccione “Formato de control”. Ubicado en el argumento “Vincular con la celda” de la pestaña “Control”, indíquele a Excel qué celda será la que esté vinculada para éste y todos los demás controles. Para el ejercicio tome la celda “D1”. “ D1”.

Si repite repite este procedimiento en el control de “Opción2” (Azúcar) notará que el argumento “Vincular con la celda” ya está diligenciado, porque al momento de configurar uno de ellos, todos los demás apuntarán a la misma celda.

46

Capítulo 5


Ahora hay que configurar el cuadro combinado, para ello dé clic derecho sobre el control “Cuadro combinado”, seleccione la opción “Formato de control” y diligencie los argumentos como se ve en la imagen. Se tomó “D2” como celda referencia.

Hasta aquí la presentación de la información debe quedar como se ve en la imagen:

: El valor de la celda “D1” depende de los botones de opción 1 y 2, y el valor v alor de la celda “D2” depende del cuadro combinado. Paso a seguir, debemos formular la tabla “Trampa”, por la tanto en la celda “E2” debe ir una fórmula que permita mostrar la misma sede que se selecciona del cuadro combinado. Ubicado en la celda “E2”, ejecute la Fx “Índice” como se ve en la imagen (Función explicada en los capítulos 1 y 2)

47

Capítulo 5


El asistente se lee de la siguiente manera: “De los nombres los nombres de las sedes que están entre el rango r ango “A2:A6”, la sede que ocupa la posición referenciada en la celda “D1” (Número 1), es la sede “Norte”. Para conocer las cifras de la sede seleccionada en cada uno los semestres, es importante saber qué número muestra muestra cada botón de opción “Café” y “Azúcar”. Si presiona clic en e n el botón “Café”, observe que en la celda “D1” aparece el número “1”, y si presiona clic en el botón “Azúcar” en la celda “D1” aparece aparece el número “2”. Este número permite identificar identificar de qué tabla se ha de buscar la cifra. Sí el número es “1”, la búsqueda debe hacerse en la tabla “Café” (Rango “A1:C6”) y si es “2”, la búsqueda debe hacerse en la tabla “Azúcar” (Rango “A8:C13”). La solución que propongo es anidar la Fx ”Si” con la Fx “BuscarV”. “BuscarV”. : Procedimiento que permite que una función llame a una o varias funciones. Entonces ubicado en la celda “F2”, dé clic en el asistente de funciones y ejecute de la categoría “Lógica”, la Fx “Si”. En el primer argumento llamado “Prueba lógica” debe ir la comparación, por lo tanto escriba la expresión D2=1, que valida sí el valor v alor de la celda “D2” es igual a “1”. : Función de la categoría “Lógica” que se utiliza para comparar uno o varios datos.

48

Capítulo 5


Pase al segundo argumento “Valor_si_verdadero” y dé clic en e n el ascensor del cuadro de nombres para o llamar a la Fx “BuscarV”.

Tenga presente que al momento de anidar la nueva función, Excel oculta temporalmente la función inicial Fx “Si” y la Fx “BuscarV” aparece en pantalla. pantalla. Proceda a diligenciar a diligenciar los argumentos de la Fx “BuscarV” como se ve en la imagen:

El asistente de la Fx “BuscarV” se lee de la siguiente manera: “Busque el dato de la celda “E2” (Norte), en la primer columna de la matriz que va desde la celda “A1” hasta “C6” (ta bla café) y una vez ubicada la sede, tome el dato que se encuentra en la segunda posición de la misma fila y colóquelo en la celda que se está haciendo la fórmula”. : Al anidar funciones no se debe dar clic en el botón “aceptar” de la función que se anida si hacen falta diligenciar argumentos de la función principal.

49

Capítulo 5


Para regresar a la Fx principal de la fórmula que es la Fx “Si”, dé clic en la palabra “=sí” que se encuentra en la barra de fórmulas. El asistente debe ir quedando como se muestra en la imagen.

Ahora dé clic en el argumento “Valor si Falso” y anide otra vez la Fx “Buscarv” y diligencie el asistente como se ve en pantalla.

El asistente de la fx “BuscarV” se lee de la siguiente manera: “Busque el dato de la celda “E2” (Norte), en la primer columna de la matriz que va desde la celda ce lda “A8” hasta “C13” (tabla azúcar) y una vez ubicada la sede, tome el dato que se encuentra en la segunda posición de la misma fila y colóquelo en la celda que se está haciendo la fórmula”.

50

Capítulo 5


Finalmente, el asistente debe quedar como se muestra a continuación.

Este mismo procedimiento se repite en la celda “G2” para el semestre 2, pero con la precaución que el indicador de columna colum na es el número “3”, ya que “semestre2” es la tercera columna de cada una de las tablas.

51

Gráfico Con Cuadro Combinado Para Varias Tablas 52

Capítulo 6

Gráfico con cuadro combinado para varias tablas

La intención del modelo es permitir que un único grafico se muestre el comportamiento de las cifras de acuerdo a la la tabla seleccionada. Elabore las tablas tablas como se en la imagen. imagen.

El modelo tiene 2 tablas. La primera hace referencia a cifras alcanzadas por asesores que trabajan en la sede “Cañaveral”, y la segunda hace referencia a cifras alcanzadas por asesores que trabajan de la sede “Cabecera”. Ubíquese en cualquier celda de la primera tabla y elabore una gráfica tipo columna, luego inserte un control de formulario tipo “cuadro combinado” (Ficha Desarrollador, Botón Insertar). La grafica debe quedar como se ve en la imagen.

El cuadro combinado debe mostrar los nombres de cada una de las sedes, para ello realice el siguiente procedimiento: En la celda G1 escriba la palabra “Cañaveral” y en la celda G2 la palabra “Cabecera”. Seleccione estas dos celdas y en el “Cuadro de nombres” asígnele el nombre de “Sedes”; para finalizar presione la tecla “enter”. “e nter”.

53

Capítulo 6


Cuadro de Nombres

: Para verificar que el nombre quedo bien asignado, ubíquese en cualquier celda, luego de clic en el ascensor del cuadro de nombres y seleccione la palabra “sedes”. Este procedimiento mostrará las que celdas a que hace referencia refer encia este nombre. Ya asignado el nombre, debe configurar el control “Cuadro combinado”. Dé clic derecho sobre el control, seleccione la opción “formato de control” y coloque los argumentos como se ve en la imagen

El asistente asistente indica que el control “Cuadro combinado” desplegará la lista con los nombres que se encuentran en las celdas G1 y G2 y vinculará a F1 como celda referencia. Esto es: sí selecciona la palabra “Cañaveral”, en la celda “F1” aparecerá el número 1, y sí sí selecciona la palabra “Cabecera”, entonces aparecerá el número 2. Ahora debe crear 2 nuevos nombres, uno para asesores a sesores y otro para las cifras, pero condicionados a la sede que se seleccione. seleccione. Esto es, para hacer referencia a los los asesores el nombre asignado puede tomar los datos del rango A2 a A7 (tabla cañaveral) o del rango D2 a D5 (Tabla Cabecera).

54

Capítulo 6


Así mismo el nombre usado para hacer referencia a las cifras puede tomar los datos del rango B2 a B7 (tabla cañaveral) o del rango E2 a E5 (tabla cabecera). Para crear el nombre que hace referencia r eferencia a las sedes, realice el siguiente procedimiento: Dé clic en el botón “Administrador de nombres” de la ficha “Fórmulas”, luego dé clic en el botón “Nuevo”. Diligencie el asistente como se ve en la imagen. Clic en el botón “Aceptar” para terminar.

El asistente se lee de la siguiente manera: “Sí el valor de la celda F1 es igual a 1el nombre “SECTORES” hará referencia al rango A2 a A7, de lo contario, el nombre “SECTORES” hará referencia al rango D2 a D5”.

=SI(Hoja8!$F$1=1;Hoja6!$A$2:$A$7;Hoja6 =SI(Hoja8!$F$1=1;Hoja6! $A$2:$A$7;Hoja6!$D$2:$D$5) !$D$2:$D$5) Condición

En caso Verdadero

En caso Falso

Para crear el nombre que hace referencia a las valores o cifras, realice el siguiente procedimiento: Dé clic en el botón “Administrador de nombres” de la ficha “Fórmulas”, luego dé clic en el botón “Nuevo”. Diligencie el asistente como se ve en la imagen. Clic en el botón “Aceptar” para terminar.

55

Capítulo 6


El asistente se lee de la siguiente manera: “Sí el valor de la celda F1 es igual a 1el nombre “VALORES” hará referencia al rango B2 a B7, de lo contario, el nombre “VALORES” hará referencia al rango E2 a E5”.

=SI(Hoja8!$F$1=1;Hoja6!$A$2:$A$7;Hoja6!$D$2:$D$5)

: ¡La expresión Hoja8! hace referencia a la hoja donde donde se encuentra la tabla. En su fórmula irá el nombre de la hoja donde esté realizando el ejercicio.

Ahora debe usar estos nombres en las series s eries del gráfico. Seleccione el grafico y dé clic en el botón “Seleccionar datos” de la ficha “Diseño”. Seleccione la serie ser ie “Valores” y dé clic en el botón “modificar”.

En el argumento “Valores de la serie”, reemplace el rango que se muestra por el nombre “VALORES”. Esto se hace así: Seleccione todo el texto tex to que se encuentra después del símbolo del símbolo admiración (!), bórrelo y luego presione la tecla de función F3 para que aparezca la ventana de nombres y de allí selección “VALORES”. Finalice con aceptar.

56

Capítulo 6


En el argumento “Valores de la serie” reemplace el rango que se muestra por el nomb re “VALORES”. Esto se hace así: Seleccione todo el texto que se encuentra después del símbolo admiración (!), bórrelo y luego presione la tecla de función F3 para que aparezca la ventana de nombres y de allí selección “VALORES”. Finalice con aceptar. Para asignar el nombre “SECTORES”, al rango del eje horizontal dé clic en el botón “Editar” y reemplace el rango A2 a A7 por el nombre sectores.

57

Capítulo 6


58

Gráficos con Fx Indirecto 59

Capítulo 7

Gráfico con Fx Indirecto

La intención de este modelo es mostrar en un u n único gráfico el comportamiento de las ventas de cada asesor según la zona, para lo cual tendrá que escoger ésta por medio de los botones de opción.

Empiece creando las tres tablas con los mismos datos y en las mismas posiciones, después seleccione la primera tabla e inserte un gráfico tipo columna, luego proceda a insertar los tres botones de opción debajo del gráfico y recuerde cambiar el nombre a cada uno de ellos. e llos.

60

Capítulo 7


Para cambiar el texto de cada botón de opción dé clic derecho sobre éste, seleccione la propiedad “Modificar texto” y reemplace el texto predeterminado pre determinado por la palabra “Zona Oriente”. Repita este procedimiento para los otros 2 controles con las palabras “Zona “ Zona Sur” y “Zona “Z ona Centro” respectivamente. Nota: Recuerde que este control se caracteriza por permitir escoger una única opción; esto significa que de los tres controles solo se podrá seleccionar uno de el los. Paso a seguir, se debe configurar los tres controles y para ello dé clic derecho sobre sobre el primero de éstos éstos y del menú que que aparece, aparece, seleccione la última última propiedad propiedad que lleva lleva por nombre “Formato de Control”.

61

Capítulo 7


En la ventana que aparece indique que celda estará vinculada a este control. Para nuestro ejercicio tome como referencia la celda “H9” y tenga presente que automáticamente los otros dos controles ya estarán vinculados a la misma celda.

Para verificar el estado de los botones de opción, dé clic en el primer control (Zona Oriente) y el número 1 saldrá en la celda “H9”, y si da clic en el control 2 (Zona Sur), saldrá el número 2 en esta misma celda y por último saldrá el número 3 si selecciona el control 3 (Zona Centro). A continuación, en la celda “I9” se hará un fórmula que permita que aparezca los nombres de cada una de las zonas de acuerdo al botón seleccionado, así por ejemplo: si en la celda “H9” está el número 1, entonces en la celda “I9” saldrá la frase “ Zona Oriente”; Oriente”; si en la celda “H9” está el número 2, entonces en la celda “I9” saldrá la frase “Zona Sur”, Sur”, y si en la celda “H9” está el número 3, entonces en la celda “I9”saldrá la frase “Zona _Centro”. De la misma manera en la celda “J9”, se hará otra fórmula similar, pero en esta ocasión la idea es que permita que aparezca los siguientes nombres: “Valor Oriente” si en la celda “H9” está el número 1; “Valor Sur”, si en la celda “H9” está el número 2, y “Valor Centro”, si en la celda “H9” está el número 3. : •

•

Es importante que en las celdas “I9” y “J9”, se muestre las frases frases sin espacios, ya que el modelo que se está realizando obliga a asignar nombres a grupos de celdas y Excel no permite que éstos lleven espacios. De cada tabla se debe asignar dos nombres. Uno de estos hará referencia a los nombres de los asesores y el segundo hará referencia a las cifras.

62

Capítulo 7


Para resolver esto, podría haber usado Fx Si, o la función Índice; pero tomé la decisión de apoyarme mejor con la Fx “BuscarV”; y por tanto se hace necesario crear la siguiente tabla de referencia que va de “K4 a M6”.

Ubicado en la celda “I9”, ejecute la Fx “BuscarV” “ BuscarV” y registre los datos como se s e ve en la imagen.

ç

El asistente se lee de la siguiente manera: “Busque el dato que se encuentra en la celda H9, en la tabla que va de K4 a M6 y una vez encontrado el dato, traiga el valor que se encuentra en la segunda columna de la matriz seleccionada.

Ahora ubicado en la celda “J9”, ejecute la Fx “BuscarV” y registre los datos como c omo se ve en la imagen.

63

Capítulo 7


El asistente se lee de la siguiente manera: “Busque el dato que se encuentra se encuentra en la celda H9 en la tabla que va de K4 a M6 y una vez encontrado el dato, traiga el valor que se encuentra en la tercera columna de la matriz seleccionada. El modelo debe quedar como se en la imagen

Para verificar que la Fx “BuscarV” haya quedado bien en las celdas “I9” y” J9”, dé clic en cada uno de los controles y tendrá que cambiar los nombres en cada una de estas celdas.

64

Capítulo 7


Ahora debe crear 6 nombres, dos por tabla. Un nombre hará referencia a los asesores y el otro, a las cifras. Para crear los 6 nombres realice los siguientes pasos. Seleccione únicamente los nombres de la primera tabla y dé clic en el “cuadro de Nombres”, luego proceda a asignar el primer nombre que debe ser “Zona “ Zona Oriente” Oriente” y para finalizar presione la tecla Enter.

Ahora Seleccione únicamente los valores de la primera primera tabla y dé dé clic en el “cuadro de Nombres”, luego proceda a asignar el nombre que debe ser “ Valor Oriente” Oriente” y para finalizar presione la tecla Enter.

Repita este procedimiento 4 veces más para las siguientes dos tablas. Si ya realizó la asignación de nombres para todas las tablas, verifique que estos nombres quedaron bien asignados dando clic en el ascensor del “cuadro de nombres” y allí se mostrará el nombre de las 6 listas.

65

Capítulo 7


El propósito de crear estos 6 nombres obedece a que el gráfico necesita identificar qué serie ha de graficar, tomado como referencia los nombres que aparecen en las celdas “I9” y “J9”, de ahí la importancia que los nombres asignados coincidan con los nombres que se muestran en las celdas “I9” y “J9”. Así por ejemplo: Si en la celda I9 está la palabra palabra “Zona “Zona Oriente”, Oriente”, entonces el grafico tomará como referencia los datos del nombre “ Zona Oriente” Oriente” y “Valor Oriente” que corresponden a la primera tabla. Ahora, si en la celda I9 está la palabra “Zona “Zona Sur”, Sur”, entonces el grafico tomará como referencia los datos del nombre “Zona “ Zona Sur” Sur” y “Valor Sur” Sur” que corresponden a la segunda tabla y así mismo con la tercera de ellas. Paso a seguir es crear dos nuevos nombres que serán en últimas los nombres que el gráfico tomará como referencia; pero estos dos nuevos nombres no se pueden crear desde el “cuadro de nombres”, ya que es necesario vincular la Fx Indirecto a cada uno de ellos.

pertenece a la categoría “Búsqueda y Referencia” y se encarga de identificar todos los elementos o datos que pertenecen a un nombre. Así por ejemplo: si se s e llama desde la función Indirecto el nombre “Zona “ Zona Oriente”, Oriente”, Excel sabrá que se está refiriendo a los nombres los nombres de los 6 asesores de la primera tabla, y si se llama desde la función Indirecto el nombre “ Valor Oriente”, Oriente”, Excel sabrá que se está refiriendo a los valores que reportaron los 6 asesores de la primera tabla.

Para asignar el primer nombre realice los l os siguientes pasos: Clic en el botón “Administrador de Nombres” de la ficha “Fórmulas”, luego dé clic en el e l botón “Nuevo”.

66

Capítulo 7


Diligencie la ventana tal como se ve en la imagen.

El nombre asignado es “ ” y el rango que debe tomar como referencia son los datos del nombre que se encuentra en la celda “I9”. Nuevamente dé clic en el botón “Nuevo” y asigne el segundo nombre tal como se ve en la imagen.

67

Capítulo 7


El nombre asignado es “ ” y el rango que debe tomar como referencia son los datos del nombre que se encuentra en la celda “J9”. Para terminar, debe referenciar estos dos últimos nombres en el gráfico, y para ello realice el siguiente procedimiento: Clic dentro del gráfico, luego clic en el botón “Seleccionar datos” de la ficha “diseño”.

Seleccione la serie “valor” y luego dé clic c lic en el botón “Modificar”.

68

Capítulo 7


En el argumento “Nombre de la serie”, seleccione la celda “I9” y en el argumento “Valores de la serie” borre de atrás hacia adelante la referencia que está tomado el grafico hasta don de encuentre el símbolo de admiración y luego presione la tecla F3 para seleccionar el nombre “Valor”, que corresponde a las cifras de la tabla a graficar o en su defecto escribirlo y finalice con “Aceptar”.

Inmediatamente Excel se ubica en la ventana de seleccionar sele ccionar datos y ahora dé clic en el botón “Editar”, de la sección “Etiquetas del eje horizontal”.

69

Capítulo 7


Nuevamente borre de atrás hacia adelante hasta encontrar el símbolo admiración y presione la tecla F3 para seleccionar el nombre “Zona”, que corresponde corresponde a los nombre de los asesores que tendrá que mostrar el gráfico. Presione 2 veces “Aceptar” para terminar.

Ahora usando los botones de Opciones verifique que el grafico represente los datos de la tabla seleccionada.

70

Capítulo 8

Gráfico con Listbox, Índice y combobox 71

Capítulo 8

Gráfico con Listbox, Ïndice y Combobox

La intención de este modelo es mostrar en un único gráfico el comportamiento de las ventas por trimestre de cada zona, según el año seleccionado, para lo cual tendrá que trabajar con los controles “Caja de lista lista o listbox”, y “Cuadro combinado o combobox”, combobox”, además de formatos condicionales y las funciones Índice y Coincidir. Empiece por crear la tabla con todos los datos como se ve en la imagen anterior y luego cree la tabla “trampa” o tabla “espejo”, a partir de la celda “A31”, con los datos de la primera zona en el primer año.

72

Capítulo 8


Ubicado en cualquier celda de la tabla “Espejo” inserte un gráfico presionado las teclas “Alt + F1” y luego borre el título del gráfico. gr áfico. Una vez insertado el gráfico, debe pintar los controles: “Listbox o cuadro de lista” y el “C uadro combinado o combobox”. Sugiero que el combobox se pinte dentro del gráfico y el Listbox fuera de él.

Cuadro de lista

Cuadro Combinado

El gráfico debe ir quedando como se muestra en la siguiente s iguiente imagen:

El control “Cuadro combinado” tiene por función mostrar la lista de los años y el control “Caja de lista” los nombres de cada una de las zonas.

73

Capítulo 8


En el cuadro combinado se pide que aparezca la lista de los años, pero este control solo permite desplegar listas siempre y cuando la lista de referencia se encuentre en forma vertical, por lo tanto es necesario crear la lista de los años en esta posición. Ubicado por ejemplo en la celda “O2” cree la lista de estos años.

Ahora debe configurar este control realizando los siguientes pasos: clic derecho sobre el “combobox o cuadro combinado”, luego del menú que se muestra seleccione la propiedad “formato de control” y diligencie el asistente como se ve en la imagen, y para terminar dé clic en el botón aceptar y verifique que el control esté mostrando mo strando la lista de los años.

Si selecciona de la lista la opción “Año 2013”, la celda que vinculó a este control (celda A29), mostrará el número 1, si selecciona la opción “Año 2014”, mostrará el número 2 y así sucesivamente. Ahora pase a configurar el control “Caja de lista” realizando los siguientes pasos: clic derecho sobre este control, luego del menú que se muestra seleccione la propiedad “formato de control”, diligencie el asistente como se ve en la imagen, y para finalizar dé clic en el botón aceptar. Verifique que el control esté mostrando las diferentes zonas de la tabla. ta bla.

74

Capítulo 8


La caja de lista o listbox está mostrando los nombres de las 25 zonas de la tabla. Si señala el primer nombre que es “Amazonas”, entonces en la celda que vinculó a este control (celd a A31) saldrá el número 1, si selecciona la segunda zona que es Arauca, en la celda vinculada se mostrará el número 2 y así sucesivamente con cada una de las zonas. La tabla y el gráfico se tendrán que ver como se muestra a continuación.

75

Capítulo 8


Nota: Las celdas vinculadas a cada una de los controles son muy importantes, ya que por medio de éstas la tabla “trampa” o tabla “espejo” “es pejo” modificará el gráfico. Ahora se necesita que en la celda “A32”, se muestre el nombre de la zona que se escogió del listbox y la f orma orma para relacionar la celda con este control c ontrol es por medio de la “ celdavinculada” . Por ejemplo, si en la celda vinculada “A31”, aparece el número 1, entonces en la celda A32 se mostrará la palabra “Amazonas”, si es el número 2, será Arauca y así sucesiva s ucesivamente. mente. Este procedimiento se puede hacer de varias formas, pero per o lo desarrollaremos por medio de la Fx Índice. La función “

”, ubica el dato de un vector o matriz que ocupa “x” posición.

Ubicado en la celda “A32”, de clic en el asistente de funciones y de la categoría “Búsqueda y Referencia”, seleccione la Fx “Índice”. De los dos argumentos que ofrece la función seleccione el primero que dice: “matriz;num_fila;num_columna”.

76

Capítulo 8


Nota: El primer argumento de esta función se debe utilizar si la búsqueda se hace en una sola matriz o tabla, y el segundo se debe seleccionar si la búsqueda se hace en 2 o más má s tablas. Diligencie el asistente como se ve en la imagen.

El asistente se lee de la siguiente manera: “De las zonas que están comprendidas entre las cel das A3 y A27, indique que zona ocupa la posición que se muestra muestr a en la celda A31”. Nota: El argumento Num_Fila y Num_Columna exige que el dato sea numérico. Para verificar seleccione cualquiera de los nombres que se muestran en el listbox, esto hará que la celda “A31” cambie de número y por ende en la celda “A32” cambiará el nombre de la zona. Se necesita ahora que en la la celda “B32” se muestre la cifra del trimestre 1 que cumpla cumpla con estas dos condiciones: que pertenezcan a la zona seleccionada por medio del listbox y al año seleccionado por medio del combobox, así por ejemplo, si la zona es Amazonas y el año es 2013, en esta celda se mostrará el valor 4285, pero si la zona es la misma y el año es 2014, en esta celda se mostrará el valor 8027 y si es del año 2015 la cifra a mostrar será 7110.

77

Capítulo 8


Existen varias formas de dar solución, pero nuestra propuesta es por medio de la Fx “Índice” anidada con la Fx “Coincidir”. Como mencionábamos en líneas atrás, la Fx “Índice” puede hacer búsquedas en una o varias tablas. Si la búsqueda se debe hacer en una sola tabla, se selecciona el primer argumento de esta función, pero si la búsqueda se debe realizar en varias tablas, el modelo nos obliga a usar el segundo argumento. Usted se preguntará: “Por qué el segundo argumento?

Porque partimos del concepto que la búsqueda del dato se debe hacer en tres tablas diferentes que son: la tabla que corresponde a las cifras del año 2013, la tabla que corresponde a las cifras del año 2014 y por último a la la tabla que corresponde a las cifras del año 2015.

Los cuatros argumentos que presenta la Fx Índice se explican a continuación:

78

Capítulo 8


: Se refiere a cada una de las matrices de donde se debe hace la búsqueda. Este argumento debe iniciar y finalizar con el símbolo paréntesis, además cada referencia o matriz deben ir separadas con el símbolo (;). Acá debe ir un número entero positivo, y es el número que está arrojando el control Listbox, en la cedla A31, porque esta celda indica que fila de la matriz se ha de graficar. : Acá debe ir un número entero positivo que puede ir de 1 a 4, porque cada uno de los cuatro trimestres, se encuentra en columnas diferentes, pero como el argumento exige un número, y las celdas tienen un texto, nos obliga a anidar en este argumento argumento la Fx Coincidir. : Este argumento va vinculado con el argumento REF, esto es: Si en el argumento REF hay tres referencias, entonces en el argumento NUM_AREA, debe ir o el número 1, ó el número 2 ó el número tres, porque es la forma de saber cuál de las tres referencias, refer encias, es la que se necesita graficar, pero este número se debe tomar de la celda que está vinculada al combobox, es decir de la celda A29.

Ubicado en la celda “B32”, el asistente de la función Índice debe quedar como se muestra a continuación : En esta primera muestra no se anidará la Fx coincidir, con el fin de explicar mejor el intención de cada argumento de la fx.

Como se mencionó anteriormente, el argumento REF, inicia y finaliza con el símbolo paréntesis y se observa que allí hay tres referencias que deben quedar fijas o constantes, que son: la matriz 1 que va desde B3 hasta E27 (año 2013), la matriz 2 que va desde F3 hasta I27 (año 2014) y la matriz 3 que va desde J3 hasta m27 (año 2015).

79

Capítulo 8


Nota: este primer argumento arroja un error, pero per o curiosamente no incide en la respuesta. En el segundo argumento NUM_FILA, debe ir el valor que se muestra en la celda A31, que corresponde a la sede. sede. Debe fijar esta posición En el tercer argumento NUM_COLUMNA, por ahora debe ir el número 1, porque se debe tomar el dato de la primera columna (trim1). Por último, en el argumento NUM_AREA, debe ir el valor de la celda A29, que q ue indica que año se ha de tomar. Debe fijar esta posición.

Una vez realizada la formula, debe copiarla hacia la derecha hasta encontrar la celda E32 que corresponde al trimestre trimestre 4. Observará que el valor es el mismo para cada una de las cuatro cuatro celdas, y esto obedece a que el argumento NUMCOLUMNA siempre está buscando la columna1, por tanto debe debe en cada celda cambiar este numero 1 por el que corresponde, es decir, en la celda C32, el argumento NUMCOLUMNA debe ser el número 2, en la celda D32, el argumento NUMCOLUMNA debe ser el número 3, y en la celda E32, el argumento NUMCOLUMNA debe ser el número 4. Ahora empiece cambiar de año y de sede por medio de los controles y observará que el gráfico registra el comportamiento de las ventas de los datos seleccionados. se leccionados. El número que se ha colocado en el argumento NUM_COLUMNA no debería digitarse en cada una de las cuatro celdas, como se hizo anteriormente, si no que éste sea producto de una función que debe ser anidada en este lugar. Le recuerdo que la función pertenece a la categoría Búsqueda y Referencia y se encarga de arrojar un número entero positivo que indica que posición ocupa un dato en un vector. Para anidar esta función, ubíquese en la celda B32 y de clic en el asistente de funciones, luego de clic nuevamente en el segundo argumento que dice: “Ref; N um_Fila; Num_Columna; Num_área”. Observará que aparece el asistente de la función Índice. Borre el número 1 del argumento “Num_Columna” e inmediatamente anide la función coincidir (clic en el ascensor del cuadro de nombres y seleccionar coincidir).

80

Capítulo 8


Aparecerá el asistente de esta función y deberá quedar q uedar como se muestra a continuación.

El asistente se lee de la siguiente manera: “busque la palabra Trim1 en el vector comprendido entre B31 y E31, e indique posición ocupa esta palabra en este vector.” Par a finalizar presione la tecla enter y copie la formula hasta la celda E32.

El asistente de la función Índice debe quedar como se ve en e n la imagen

81

Capítulo 8


Por último es necesario que con colores se marque el registro que se está graficando, esto es: si se escoge por ejemplo la sede Arauca y el año es el 2015, los datos que corresponden a estas coordenadas tendrán que resaltarse con un color usando la herramienta herr amienta formato condicional. Formato Condicional es el comando que permite cambiar el color de una celda de acuerdo a una o varias condiciones. Este comando se encuentra en la ficha “Inicio”. El formato condicional tendrá que evaluar dos criterios a la hora de asignar el color a las celdas: el primero de ellos es verificar que coincida la sede seleccionada por el listbox (celda A32) con el de esta misma sede que está en el rango comprendido entre A3 a A27, y la segunda consiste en identificar que años se está graficando. En la celda A29 aparece el número entero que es producto del año que se selecciona del combobox. Recuerde que si es 1, se entiende que corresponde al año 2013, si es 2, corresponde al año 2014 y si es 3, obviamente al año 2015. Para crear el primer formato condicional para los datos correspondientes al año 2013, debe seleccionar el rango comprendido comprendid o entre A3 y E27, luego, de clic en el botón “Formato Condicional” de la ficha Inicio, y del menú que se despliega debe dar clic en la opción “Nueva Regla”.

82

Capítulo 8


Aparece una ventana con varias opciones, de la cuales se escogerá la última de ellas que lleva por nombre “Utilice una fórmula que determine las celdas para aplicar formato”. El asistente de formato condicional debe quedar como se en la imagen.

Después de escribir la fórmula, debe dar clic en el botón “Formato” para escoger color de relleno, y con respecto a la expresión, se deduce que es una fórmula porque inicia con el símbolo

83

Capítulo 8


“=” y seguidamente se encuentra la función “Y”, que permite que solo se realice el cambio de color si se cumplen con las dos condiciones.

=Y($A$32=$A3;$A$29=1) La expresión expresión se lee de la siguiente manera: “si el nombre del departamento que se encuentra en la celda A32 es igual a la primera zona de la matriz, y, si el valor del de la celda A29 es igual a 1 (año 2013).” Nota: Observe que en la fórmula se fijó de la celda A3, únicamente la columna A, ya que el formato condicional que está simulando ser una fórmula debe arrastrar estas condiciones a cada una de las celda que se han seleccionado, es decir, en la celda B3, C3, D3, y E3, el formato condicional evalúa el contenido la celda A3, en la celda B4, C4, D4 y E4, el formato condicional evalúa la celda A4 y así sucesivamente. Para crear el segundo formato condicional correspondiente a los datos del año 2014, debe seleccionar el rango comprendido entre A3 y A27 y el rango comprendido entre F3 y I27, luego, clic en el botón “Formato Condicional” Condicional” de la ficha “Inicio” y seleccionar la opción “Nueva Regla”. El asistente para esta segunda región debe quedar como se ve en la imagen. Una vez escrito la sentencia, debe escoger el color colo r dando clic en el botón “formato”.

Para crear tercer y último formato condicional correspondientes a los datos del año 2015 , debe seleccionar el rango comprendido entre A3 y A27 y el rango comprendido entre J3 y M27, luego,

84

Capítulo 8


clic en el botón “Formato Condicional” de la ficha “Inicio” y seleccionar la opción “Nueva Regla”. El asistente para esta segunda región debe quedar como se ve en la imagen.

Ahora a descrestar a su equipo de trabajo.

85

Ca

Gráfico Tipo Termómetro 86

Capítulo 9

Gráfico tipo Termómetro

La intención de este gráfico es la de observar el comportamiento de las ventas en relación a una meta por medio de un gráfico tipo termómetro. Para empezar, copie los datos como se ve en la imagen.

En la celda “E1” debe quedar la suma de las ventas, por la tanto la fórmula es la siguiente: “=SUMA(B2:B17)”. En la celda “E2”, debe digitar el valor de $122.000 que es la meta que se ha

87

Capítulo 9


fijado para este modelo y en la celda “E3” se debe calcular la efectividad que sale de dividir el total de las ventas ventas entre el valor de la meta. La fórmula es la siguiente: siguiente: “=E1/E2”.

Ahora proceda a insertar un gráfico tipo “columna”, seleccionando únicamente las celdas “D3 y E3” (variable efectividad).

Área del Gráfico

Efectividad

30% Líneas de División

25% 20% 15%

Efectividad

10%

Leyenda

5% 0% 1 Es hora de empezar a configurar el gráfico para que tome la apariencia de termómetro; empiece por ampliar el ancho de la columna, para ello dé clic derecho dentro de la columna y seleccione la opción “Formato serie de datos” y en el argumento “Ancho del intervalo” lleve el indicador hasta que el valor sea Cero (a menor valor, más ancha es la columna); ahora borre la leyenda y el título del gráfico (Tecla suprimir) y finalmente, reduzca el ancho del “Área del gráfico”. La gráfica debe quedar como se ve en la imagen.

88

Capítulo 9


30% 25% 20% 15% 10% 5% 0% 1

La gráfica muestra que los valores del eje principal van hasta el 30%, y teniendo en cuenta que el total de ventas puede superar la meta, es necesario que estos porcentajes sean mayores a 100%, para esto dé clic derecho sobre cualquier porcentaje del eje principal, seleccione la propiedad “Formato del eje” y en el argumento “Mínimo” coloque cero y en el argumento “Máximo” coloque 1,2 (equivale al 120%).

Nota: Al fijar los valores mínimo y máximo del eje principal, significa que siempre mostrará este intervalo para medir la gráfica, de no hacer esto, los límites se ajustan de acuerdo al valor graficado.

89

Capítulo 9


Para visualizar el actual porcentaje de efectividad en la gráfica, dé clic derecho dentro de la columna y seleccione la opción “Agregar etiqueta de datos”. Observará que el valor 27% aparece encima de columna. Para crear un contraste entre la efectividad de las ventas y la meta, dé clic en el área de trazado de la gráfica (fondo de la gráfica) y cambie de color con el botón “Relleno”; realice el mismo procedimiento para cambiar el color de la gráfica (Use verde como venta realizada y gris como presupuesto), finalmente borre borre las líneas de división. división. La gráfica debe quedar como se ve en la imagen.

120% 100% 80% 60% 27 %

40% 20% 0%

Para observar el comportamiento del termómetro, proceda a colocar ventas en las siguientes semanas, observará que la franja de color verde va aumentando.

90

Gráfico Tipo Termómetro Con Botones De Opción 91

Capítulo 10

Gráfico tipo Termómetro con botones de opción

Al igual que el ejercicio anterior, la intención de este gráfico es la de observar el comportamiento de las ventas respecto a una meta, con la diferencia que en este modelo tendremos una variable adicional y es que la tabla muestra las ventas de varias zonas, por tanto, se usará un control de formulario (botón de opción) que hará que el gráfico se comporte dinámicamente mostrando la efectividad de la zona seleccionada. Para empezar, copie los datos como se ve v e en la imagen.

92

Capítulo 10


De la celda “F1 a “F3” escriba los rótulos o títulos como se ve en la imagen (Suma, Meta, Efectividad). En la celda “G1” sume de los datos de la columna la columna B (zona norte). La fórmula queda así: “=SUMA(B2:B17)”. El valor de la Meta es fijo, por lo tanto en la celda “G2” escriba 150.000. La efectividad es la división del valor “suma” entre el valor “meta”. La fórmula en “G3” queda así: “=G1/G2”.

Seleccione Seleccione las celdas “F3:G3” e inserte un gráfico tipo columna. Ahora empiece a configurar el gráfico para que tome la apariencia de termómetro. Para ampliar am pliar el ancho de la columna, dé clic derecho dentro de la columna y seleccione la opción “Formato serie de datos” y en el argumento “Ancho del intervalo” lleve el indicador hasta que el valor sea “0” (A menor valor, más ancha es la columna), ahora borre la leyenda y el título del gráfico (Tecla suprimir) y finalmente, reduzca el ancho del “Área del gráfico”. La gráfico”. La gráfica debe quedar como se ve en la imagen.

93

Capítulo 10


Efectividad 18% 16% 14% 12% 10% 8% 6% 4% 2% 0% 1

La gráfica muestra que los valores del eje principal van hasta el 18%, y teniendo en cuenta que el total de ventas puede superar la meta, es necesario que estos porcentajes sean mayor a 100%, para esto dé clic derecho sobre cualquier porcentaje del eje principal, seleccione la propiedad “Formato del eje” y en el argumento “Mínimo” coloque cero y en el argumento “Máximo” coloque 1,2 (equivale al 120%).

Nota: Al fijar los valores mínimo y máximo del eje principal, significa que siempre mostrará este intervalo para medir la gráfica, de no hacer esto, los límites límites se acomodan de acuerdo al valor graficado. Para visualizar el actual porcentaje de efectividad en la gráfica, dé clic derecho dentro de la columna y seleccione la opción “Agregar etiqueta de datos”. Observará que el valor 17% aparece encima de columna.

94

Capítulo 10


Para crear un contraste entre la efectividad de las ventas y la meta, dé clic en el área de trazado de la gráfica (fondo de la gráfica) y cambie de color con el botón “Relleno”; realice el mismo procedimiento para cambiar el color de la gráfica (se verde como venta realizada y gris como presupuesto), finalmente finalmente borre las líneas de división. división. La gráfica debe quedar como se ve en la imagen.

Efectividad 120% 100% 80% 60% 40% 17 %

20% 0%

1

Ahora proceda a pintar los tres botones de opción dando clic en el botón “Insertar” de la ficha “Desarrollador” y seleccione el quinto botón de la primera fila.

95

Capítulo 10


Una vez pintado los tres controles proceda a cambiar los nombres de cada uno de ellos, para esto dé clic derecho en cada control y seleccione seleccione la propiedad “Modificar texto”. Los nombres a usar en cada uno de los controles son: Zona Z ona Norte, Zona Sur y Zona Oriente. El “ es un control de opción única; esto significa que de todos los botones de opción, únicamente podrá ser seleccionado o habilitado uno de ellos. Al ser el botón de opción un control de opción única significa que con solo configurar un control, entonces todos quedarán automáticamente configurados, eso significa que cada uno de ellos estarán vinculados a una misma celda. Para configurar un botón de opción dé clic derecho sobre cualquiera de éstos y seleccione “Formato de control”. Ubicado en el argumento “Vincular con la ce lda” de la pestaña “Control”, indíquele a Excel qué celda será la que esté vinculada para éste y todos los demás controles. Para el ejercicio tome como referencia la celda “J1”. Verifique que los botones de opción estén bien configurados así: si activa el botón “Zona Norte2, entonces en la celda “J1” saldrá el número 1, si activa el botón “Zona Sur”, entonces en la celda “J1” saldrá el número 2, y si el botón activado es el de la Zona Oriente, en “J1” saldrá el número 3.

Ahora se necesita que en la celda cel da “G1” quede la suma de los valores que corresponden a la zona seleccionada en los botones de opción, así por ejemplo: si seleccionó “Zona Norte”, entonces en tonces en esta celda debe quedar la suma de los valores v alores de columna “B”, pero si la opción seleccionada es “Zona Sur”, se presume que en G1, debe quedar la suma de los valores de la columna “C” y por consiguiente, si la opción activada es “Zona Oriente”, entonces en G1 debe quedar la suma de los valores de columna “D”.

96

Capítulo 10


Para esto borre el dato que está en “G1” y ejecute la Fx Sí y diligencie el asistente como se ve la imagen:

El asistente se lee de la siguiente manera: “Si el valor de la celda “J1” es igual a 1, debe sumar los datos del rango B2 a B17”. Ahora pase al argumento “Valor si Falso” y anide an ide otra Fx “Si”, para evaluar si la celda “J1” toma el valor de 2. Recuerde que para anidar otra Fx “Si”, debe dar clic en la palabra “SI” del “Cuadro de nombres”.

Cuadro de Nombres

Una vez aparezca el segundo asistente de la Fx “Sí”, “Sí” , diligencie los datos como se ve en la imagen.

97

Capítulo 10


El asistente se lee de la siguiente manera: “Si el valor de la celda “J1” es igual a 2, debe sumar los datos del rango C2 a C17”. Ahora pase al argumento “Valor si Falso” y anide por última última vez la Fx “Sí”, para evaluar si la celda “J1” toma el valor de 3. Una vez aparezca el tercer asistente de la Fx “Si”, diligencie los datos como se ve en la imagen.

El asistente se lee de la siguiente manera: “Si el valor de la celda “J1” es igual a 3, debe sumar los datos del rango D2 a D17”. Nota: Se pudo haber omitido la última función anidada, colocando la expresión en la parte falsa de segundo Sí, ya que si el valor de J1, no entró por el primer si, y tampoco entró por el segundo si, entonces por deducción, se pudo haber colocado la expresión en el argumento falso. Listo. Ahora tiene un gráfico tipo termómetro con controles de formulario. Agre gue ventas para las diferentes zonas y manipule el gráfico por medio de los botones de opción.

98

Gráfico Tipo Termómetro con formato Condicional 99

Capítulo 11

Gráfico tipo termómetro con formato condicional

La intención de este modelo es mostrar el comportamiento de las ventas, respecto a una meta, por medio de un gráfico tipo termómetro y que tiene como novedad que el color de relleno de la columna cambie de color de acuerdo a unos criterios establecidos, es tablecidos, esto es, si la efectividad de las ventas es menor a 25%, la gráfica se mostrará en color rojo; si la efectividad está entre 25% y 50%, la gráfica se mostrará en color naranja; si la efectividad está entre 50% y 80%, la gráfica se mostrará en color amarillo y por último, si la efectividad está por encima del 80%, la gráfica se mostrará en color verde. Nota: Se requiere haber leído el capítulo anterior para poder entender y realizar el modelo propuesto. Para empezar, cree la tabla como se ve en la imagen.

100

Capítulo 11


De la celda “D1 a “D3” escriba los rótulos rótulos o títulos como se ve en la imagen (Suma, Meta, Efectividad). En la celda “E1” sume de los datos de la columna “B”. La fórmula queda así: “=SUMA(B2:B17)”. El valor de la Meta es fijo, por lo tanto en la celda “E2” escriba 150.000, y la efectividad es la división di visión del valor “suma” entre el valor “meta”. La fórmula en “E3” queda así: “=E1/E2”. Tenga “=E1/E2”. Tenga presente que el dato que se está graficando es el porcentaje de efectividad (celda “E3”).

En el rango “G1:H5” se encuentra la tabla trampa o tabla espejo, que tiene ti ene por objeto identificar qué celda de esta tabla es la que se ha de graficar. Así, por ejemplo, si el % de efectividad está por debajo de 25%, este valor se mostrará en la celda “H2” y en el resto de las celdas tendrá que aparecer un error, ; pero si la efectividad se ubica entre el 25% y 50%, entonces este valor se mostrará en la celda “H3” y de la misma manera, el resto de las celdas tendrán tendrán que mostrar un error. En conclusión, podemos podemos decir, que el porcentaje de efectividad se mostrará en la celda correspondiente a los límites y en el resto de éstas se mostrará errores. Es importante recalcar que la hoja de cálculo Excel teniendo como premisa pre misa esta afirmación, Excel nos ofrece la fx “NOD”,

y

Función que pertenece a la categoría “Información” y devuelve el v alor de error #N/A, que significa valor no disponible. Ahora, ubicado en la celda “H2” debe aparecer el porcentaje de efectividad efectividad si y solo si, éste se encuentra por debajo del 25%. Para ello use la Fx Lógica “Sí” y ejecute la función como se ve en la imagen.

101

Capítulo 11


El asistente de se lee de la siguiente manera: “Sí el valor de la celda “E3” “ E3” es menor a 25%, 25 %, muestre este valor, de lo contario anide la Fx NOD()”. Ahora ubicado en la celda “H3” realice la función que permitirá mostrar el porcentaje de efectividad si éste valor se encuentra entre el 25% y el 50. Diligencie la función como se ve en la imagen.

NOTA: En este caso, dentro del argumento “Prueba lógica” se usó la fx lógica “Y”, que permite realizar dos o más comparaciones, separando cada una de ellas por el separador de listas, para nuestro caso (;). El asistente de se lee de la siguiente manera: “Sí el valor de la celda “E3” es mayor a 25% y al mismo tiempo, el valor de la celda “E3” es menor al 50%, entonces muestre este valor, de lo contario anide la Fx NOD()”. N OD()”. Luego, en la celda “H4” debe aparecer el porcentaje de efectividad si éste se encuentra entre el 50% y el 80%. Para ello use la Fx lógica Sí y ejecute la función como se ve en la imagen.

102

Capítulo 11


El asistente de se lee de la siguiente manera: “Sí el valor de la celda “E3” está entre el 50% y el 80% muestre este valor, de lo l o contario anide la Fx NOD()”. Finalmente, en la celda “H5” debe aparecer el porcentaje de efectividad si éste se mayor o igual 80%. Para ello use la Fx lógica Sí y ejecute la función como se ve en la imagen.

El asistente de se lee de la siguiente manera: “Sí el valor de la celda “E3” “ E3” es mayor o igual al 80% muestre este valor, de lo l o contario anide la Fx NOD()”. La estructura de las tablas debe quedar como se ve en la imagen.

103

Capítulo 11


A continuación, seleccione el rango “H2:H5” e inserte un gráfico tipo columna.

Título del gráfico 20% 15% 10% 5% 0% 1

2

3

4

Seleccione el gráfico y dé clic en el botón “Cambiar tipo de gráfico” que se encuentra en la ficha “Formato” y seleccio y seleccione ne la segunda alternativa del gráfico “Columna apilada”.

104

Capítulo 11


Realice a la gráfica los siguientes ajustes: •

•

• •

Cambie los valores del eje principal de 0 a 1,2 (El límite máximo para la gráfica gr áfica se asignó en 1,2, porque se estima que el % de efectividad pueda llegar al 120%). El ancho del intervalo de la columna debe ser 0, con el fin de ampliar al máximo el ancho de la columna. Reduzca el área del gráfico, elimine el título y las líneas de división. Use el color Gris como contraste para el indicador del termómetro y agregue la etiqueta de datos.

El modelo debe ir quedando como se muestra a continuación:

105

Capítulo 11


Para nuestro modelo, debe cambiar el color de la columna a rojo porque la única efectividad que se está graficando está por debajo del 25%. Esto se hace seleccionando la columna columna y luego dé clic en el ícono sombreado de la pestaña “Inicio”. A continuación, agregue valores de ventas en la columna “B”, buscando que la efectividad varíe entre los diferentes criterios planteados, y de esta manera m anera pueda cambiar manualmente el color para cada criterio. Para esto suponga que en la celda “B7” (semana 6) se registraron ventas por 30.000, esto hace que la efectividad llegue al 37%, es decir se está grafican do únicamente la segunda serie “H3”, por lo tanto, cambie a color Naranja y agregue agr egue la etiqueta de datos.

Ahora, suponga que en la celda “B8” (semana 7), se registraron ventas por otros 30.000, esto hace que la efectividad llegue al 57%, es decir se está graficando la tercera serie “H4”, por lo tanto, cambie a color Amarillo y agregue la etiqueta de datos. Por último, suponga que en la celda “B9” (semana 8), se registraron ventas por 40.000, esto hace que la efectividad llegue al 84%, es decir se está gra ficando la cuarta serie “H5”, por lo tanto, cambie a color Verde y agregue la etiqueta de datos

106

Capítulo 11


Se podría reemplazar el color de relleno de la columna por una imagen de tal manera que el gráfico se muestre más espectacular; por ejemplo, ejem plo, en vez de mostrarse el e l gráfico en color verde, que significa que la efectividad supera el 80%, podría mostrarse una imagen alusiva al comportamiento de las ventas. Para reemplazar el color de relleno por una imagen, realice el siguiente procedimiento: dé clic derecho dentro dentro de la columna, seleccione la propiedad “Formato serie de datos” y en el panel de la derecha seleccione “Relleno con imagen o textura” que se encuentra en el icono “relleno y línea” (tarrito de pintura).

107

Capítulo 11


Ahora, dé clic en el botón b otón “Archivo” y seleccione una imagen (previamente descargada).

Súper guau.

108

Gráfico Tacómetro con tipo de gráfico Circular 109

Capítulo 12

Gráfico Tacómetro con tipo de gráfico circular

La intención de este modelo es evaluar el comportamiento de las ventas mediante un gráfico tipo tacómetro; así la gráfica indicará el % de ventas que tenemos respecto a una meta que será un 100%. El único problema es que Excel no incluye este tipo de gráfico, sin sin embargo, podemos emplear algunas técnicas especiales para simular este tipo de gráfico y es mediante un gráfico circular. Elabore la tabla como se ve en la imagen.

110

Capítulo 12


En la celda “E1” debe quedar la suma de las ventas. La fórmula es esta celda es: “=SUMA(B2:B17)”. El valor de la meta para nuestro modelo es de $150.000 y debe quedar en la celda “E3”. El faltante es el valor que se necesita para cumplir cumplir la meta, es decir, si decir, si la venta es de 25.000 y la meta es de 150.000, significa que falta 125.000 para cumplir con lo esperado, entonces en la celda “E2” realice la siguiente fórmula: “=E3 -E1”.

Ahora proceda a insertar el gráfico tipo “Circular” en dos dimensiones, selec cionando el rango “D1:E3”.

El gráfico debe quedar como se muestra a continuación:

111

Capítulo 12


Para que el gráfico tome la apariencia de Tacómetro, empiece por cambiar únicamente a color blanco la serie “Meta”. Recuerde que este procedimiento procedimiento se hace mediante mediante el ícono “Color de Relleno” que aparece en la pestaña “Inicio”, en la pestaña “Formato” o presionando clic derecho sobre la serie que quiere cambiar de color. Verifique que antes de cambiar de color, esta serie sea la única que esté seleccionada.

Los gráficos circulares tienen la particularidad que pueden ser girados en los grados necesarios, sobre su mismo eje. Para nuestro modelo es necesario necesario que este gráfico gire gire 270°, de tal manera que el gráfico se visualice visualice de forma horizontal. Par esto dé clic derecho derecho en cualquier parte del gráfico y seleccione la propiedad “Formato serie de datos” y en el argumento “Angulo del primer sector” digite 270° y presione enter.

112

Capítulo 12


Paso a seguir, agregue la etiqueta de datos al gráfico gr áfico presionando clic derecho sobre el gráfico grá fico y seleccionando la propiedad “Agregar etiqueta de datos” o activando la casilla de verificación que aparece al dar clic sobre el botón “ ” que aparece en la parte superior dere cha del gráfico.

Finalmente, borre la etiqueta de datos de la serie “ Meta” y la leyenda del gráfico, presionando clic en cada una de éstas, y presionando la tecla suprimir. suprimir. La gráfica debe quedar de la siguiente manera:

113

Capítulo 12


Agregue valores de venta a la tabla para que pueda visualizar el funcionamiento del tacómetro.

Genial!!

114

Gráfico Tacómetro con tipo de gráfico anillos 115

Capítulo 13

Gráfico Tacómetro con tipo de gráfico anillos

La intención de este modelo es evaluar el comportamiento de las ventas de cada sede en cada trimestre, mediante un gráfico circular tipo anillo, administrado desde un control de formulario (combobox). A nivel de gráficos circulares existen tres tipos: Gráficos circulares en 2D, gráficos circulares en 3D y gráficos circulares en anillos. La diferencia que existe entre los gráficos tipo anillo y los de 2D y 3D, es que los primeros permiten graficar varias series, mientras que gráficos 2D y 3D grafican sólo una serie. Empiece por crear la tabla como se muestra en la imagen.

116

Capítulo 13


Los datos que están en el rango de “A9:B19” corresponden a las 2 tablas “trampas” o tablas “espejo” (Tabla que realmente ofrece los datos que usa la gráfica.) Para este modelo a realizar es necesario aplicar el grafico “Circular - tipo anillos”, ya que debe usar 2 series que son: la serie de “Ventas” que está en el rango “A9:B12” y la serie de “Criterios” que está está en el rango “A14:B19”. Por ahora solo se graficará las ventas de la ciudad de Bucaramanga, más adelante se insertará el cuadro de control “combobox” para seleccionar de la lista la ciudad que se quiere graficar; entonces, para nuestro modelo en la celda ce lda “B9”, digite “Bucaramanga” y en la celda “B12”, “B1 2”, digite “3.000.000”, que es la cifra que se tomó como meta. Con esta información las fórmulas deben quedar así: •

•

En la celda “B10” debe quedar el valor total de la venta (año) de la ciudad de Bucaramanga, entonces la fórmula es “=F2” En la celda “B11”, debe quedar el valor que falta para cumplir la meta, es decir, es la diferencia que hay entre la “Meta” y la “Venta”, escriba la siguiente siguiente fórmula “B12 – B10”.

117

Capítulo 13


Con respecto a la segunda tabla “tabla de criterios”, que tiene por objeto demarcar los rangos para saber en qué estado se encuentra e ncuentra la venta y para ello tenemos los siguientes s iguientes criterios. sí la venta está por debajo de 1.000.000, y se muestra en color “rojo” sí la venta está entre 1.000.000 y 1.600.000, y se muestra en color “naranja” sí la venta está entre 1.600.000 y 2.200.000, y se muestra en color “amarillo” sí la venta está entre 2.200.000 y 2.800.000 y se muestra en color “verde claro” sí la venta es mayor a 2.800.000 y se muestra en color “verde oscuro”

Empiece por graficar en anillos la primera serie tomando como referencia los datos de la tabla venta (rango “B10:B12”).

Ahora debe insertar la segunda serie ser ie “tabla criterios”, para esto seleccione la gráfica gr áfica y dé clic en el botón “Seleccionar Datos” de la ficha “Diseño”, aparece apare ce la ventana “Seleccionar origen de datos.

118

Capítulo 13


Seguidamente dé clic en el botón “Agregar”, se muestra la ventana “Modificar serie”. En el argumento “Nombre de la serie”, deje vacío va cío y en el argumento “Valores de la serie, empiece por borrar la información que aparece allí y luego seleccione sele ccione el rango “B15:B19”, y finalice presionando clic en “Aceptar”.

La grafica debe quedar como se ve en la imagen.

119

Capítulo 13


Observe que la mitad de la serie del anillo interior no coincide con la mitad del anillo exterior, y es necesario para la elaboración del grafico tipo tacómetro, que las dos mitades coincidan. El gráfico que se busca debería quedar así

Para hacer coincidir la mitad del anillo interno con la mitad del anillo externo, es necesario realizar un procedimiento adicional. Este procedimiento adicional consiste en llevar a la mitad cada una de las cifras de cada serie. s erie.

120

Capítulo 13


Para empezar, tome como c omo referencia los datos de la serie ventas, y ubicado en la celda “C10” realice la siguiente fórmula: “=B10/2”. Enter para terminar. Ahora arrastre la fórmula hasta la celda “C12”.

Para llevar los datos a la mitad de la tabla criterios, en la celda “C15” realice la siguiente fórmula “=B15/2”. A diferencia del procedimiento de la tabla anterior, esta fórmula no se puede copiar para las demás celdas; porque lo que se debe llevar a la mitad es valor del intervalo que hay entre un criterio y el otro, es decir, si malo está en un 1.000.000 y regular está en 1.600.000, el valor que debe llevarse a la mitad es la diferencia diferencia entre éstos dos límites, límites, es decir, 600.000; por tanto en la celda “C16” debe quedar la fórmula “=(B16“=(B16 -B15)/2”. Y esta fórmula si aplica para el resto de las celdas. Por último, es necesario hacer la suma del rango “C15:C19” y verificar ver ificar que este valor sea igual al dato de la meta que está en la celda “C12”; de tal manera que a la hora de graficar la mitad de la primera seria coincida con la mitad de la segunda.

121

Capítulo 13


Teniendo en cuenta que ahora sí coincide la meta de la tabla ventas y la suma de la tabla criterios, borre el gráfico creado y proceda a insertar nuevamente el gráfico tipo anillo con los datos del rango de las ventas “C10:C12” y luego agregue la serie de los datos del rango de criterios “C15:C20”. La gráfica debe quedar como se muestra a continuación: continuación:

Los gráficos anillos también tienen la particularidad que pueden ser girados los grados necesarios, sobre su mismo mismo eje. Para nuestro modelo es necesario que éste gire 270°, de tal

122

Capítulo 13


manera que el gráfico se visualice de forma horizontal. horizontal. Para esto dé clic derecho en cualquier cualquier parte del gráfico y seleccione la propiedad “Formato serie de datos” y en el argumento “Angulo del primer primer sector” digite 270° y además reduzca la cifra que corresponde a la propiedad “Tamaño del agujero del anillo” a 10% y finalice con enter.

El grafico debe quedar como se ve en la imagen. Para que el gráfico tome la apariencia de Tacómetro, cambie el color de la serie “Meta” y de la serie “Total” a color blanco. blanco. Recuerde que este procedimiento procedimiento se hace mediante el ícono “Color de Relleno” que aparece en la pestaña “Inicio”, de la pestaña “Formato” o presionando clic derecho sobre la serie que quiere cambiar de color.

123

Capítulo 13


Proceda a cambiar los colores en el gráfico gráfi co de la serie criterio, de izquierda a derecha, d erecha, así: Rojo, Naranja, Amarillo, Verde Claro, Verde Oscuro. La grafica debe quedar quedar como se ve en la imagen.

Título del gráfic gráfico o

1

2

3

4

5

6

Para crear el efecto de la línea que mide la venta, debe cambiar a color blanco todas las series del anillo de ventas (anillo interior), y luego seleccione únicamente la serie que corresponde al valor de “faltante”.

Seleccionada únicamente la seria “Faltante”, de clic en cualquier “estilo de forma” que se encuentra en la ficha “Formato”. El gráfico debe quedar como se ve en la imagen.

124

Capítulo 13


Título del gráfic gráfico o

1

2

3

4

5

6

Borre el título del gráfico y la leyenda, luego proceda a crear los títulos de cada uno de los criterios de las ventas. Esto es: dé clic en el botón “Formas” de la ficha “Insertar”. Seleccione “Cuadro de texto” e inserte los textos. El gráfico debe quedar como se ve en la imagen.

125

Capítulo 13


Regular

Bueno Sobresaliente

Malo Excelente

Ahora se debe crear el control de formulario que permita desplegar una lista con las sedes que han de graficarse. Inserte Inser te el control de formulario “Cuadro combinado” debajo del grafico circular.

126

Capítulo 13


Regular

Bueno Sobresaliente

Malo Excelente

Para configurar el control de formulario debe dar clic derecho dentro de este control que se insertó, luego del menú que aparece, seleccione la opción “formato de control” y automáticamente saldrá el asistente. En el argumento “Rango de entrada”, seleccione el rango de “A2” hasta “A7”, que corresponde a las sedes, y en el argumento “vincular celda” seleccione una celda vacía, para nuestro modelo selecciones la celda B8.

Verifique que el control “cuadro combinado” si está desplegando la lista de sedes y que la celda B8 está arrojando un número que corresponde a la posición que ocupa la sede del listado de las ciudades.

127

Capítulo 13


Sí escoge de la lista la palabra “Bucaramanga”, en la celda “B8” saldrá el número 1. Sí escoge de la lista la palabra “Cúcuta”, saldrá en la celda “B8” el número 2 y así sucesivamente. Ahora debe vincular la celda “B8” con la celda “B9”, esto es: Sí en la celda “B8” está el número 1, en la celda “B9” debe aparecer apare cer la palabra “Bucaramanga”, y sí en la celda “B8” “ B8” está el número 2, entonces en la celda “B9” debe aparecer la palabra “Cúcuta”, y así sucesivamente. s ucesivamente. Ubíquese en la celda “B9” y ejecute la Fx Índice que se encuentra la categoría “Búsqueda y Referencia” Referencia” del asistente de funciones. Diligencie los datos de asistente tal como se ve en la imagen.

128

Capítulo 13


El asistente se lee de la siguiente manera: “De las sedes que están en el rango A2 a el rango A7, la sede que ocupa la posición indicada en la celda “B8” es Cúcuta”. Verifique que el nombre de la ciudad que está en la celda “B9” cambie al momento de seleccionar una sede del cuadro combinado. Es necesario que en la celda “B10” quede el valor total de la ciudad que se encuentra en la celda “B9”. Para esto ejecute la función “BuscarV” que pertenece a la categoría “Búsqueda y Referencia”. El asistente debe quedar como se en la imagen. im agen.

El asistente se lee de la siguiente manera: “Busque la ciudad que se encuentra en la celda B9 en la matriz o tabla que va desde la celda A1 hasta la celda F7 y una vez encontrada la fila donde se encuentra esta ciudad, ponga en la celda el dato que está en la columna 6.”. Verifique que el gráfico mueve la aguja a medida que cambia de ciudad. Para que aparezca el valor exacto de la venta, debe insertar el objeto “Rectángulo” que se encuentra en el botón “Formas “de la ficha “Insertar”. Pinte este objeto debajo del cuadro combinado.

129

Capítulo 13


Para que este botón muestre el valor de la venta, seleccione este objeto, luego dé clic en la barr a de fórmula y escriba la siguiente expresión “=B10”. Significa que el objeto cuadro debe mostrar el valor de la celda B10. La gráfica debe quedar como se ve en la imagen.

Por último, sería interesante que de la tabla quede seleccionada con un color la fila de la ciudad que se está analizado. Para ello realice el siguiente procedimiento: Seleccione el rango desde d esde la celda A1 hasta la celda F7, luego dé clic en “Formato Condicional” de la ficha “Insertar”, y escoge es coge la opción “Nueva regla”. En el nuevo pantallazo seleccione la opción “Utilice una fórmula que determine las celdas para aplicar formato”. Escriba la siguiente fórmula “=$B$9=$A2”, y en el botón “Formato” escoja de la ficha “Relleno”, el color que desea.

130

Capítulo 13


Cambie de ciudad para que el gráfico muestre el movimiento de la aguja y resalte r esalte en color el registro que se está graficando. Nos quedó super.

131

Gráficos con casillas de Verificación 132

Capítulo 14

Gráfico con casillas de Verificación

La intención del modelo es que por medio de las casillas de verificación el usuario indique que sedes desean ver y que sedes no se desean ver en el e l gráfico. Proceda a crear la tabla como se ve en la imagen.

Ubicado en cualquier cualquier celda de la la tabla inserte un gráfico tipo “Línea”. Verifique que en el eje horizontal queden los trimestres y la “leyenda” correspondan a las “sedes”. Sí quedaron en orden inverso, dé clic en el botón “Cambiar entre filas y columnas” que se encuentra encuent ra en la ficha “Diseño”.

133

Capítulo 14


Ahora inserte cuatro casillas de verificación (Ficha Programador, botón Insertar) debajo del gráfico y cambie los textos. Bucaramanga para la primera casilla, Cúcuta para la segunda, Tunja para la tercera y Bogotá para la cuarta. Para cambiar los textos, dé clic derecho sobre el control y seleccione la propiedad “Cambiar texto”.

134

Capítulo 14


Las casillas de verificación o checkbox son controles de opción múltiple; esto significa que es posible activar varios controles al mismo tiempo. Este control tiene dos eventos: Sí se habilita, se dice que el control está “verdadero”, sí se deshabilita se dice que el control está “falso”.

Tenga presente que el gráfico sólo mostrará la línea de la sede cuya casilla de verificación esté habilitada. Para configurar la casilla de verificación “Bucaramanga”, dé clic derecho sobre este control y seleccione la propiedad “Formato de control”. En el argumento “Vincular con la celda” seleccione la celda “A22”.

135

Capítulo 14


Para verificar el estado del control de la sede “Bucaramanga”, dé clic en la casilla de verificación de esta sede y en la celda “A22” debe mostrarse el texto “Verdadero”, sí lo deshabilita el texto t exto a mostrar será “Falso”. Configure el resto de los controles. Para Cúcuta vincule la celda “B22”, para Tunja vincule la celda “C22” y para Bogotá vincule la celda “D22”. Para validar sí la serie de una sede se ha de graficar o no, es necesario conocer el estado del control (verdadero o falso) y para ello es necesario crear “Nombres” a rangos de celdas. Así por ejemplo: Sí en la celda “A22” dice verdadero (Bucaramanga está activa), el nombre asignado debe hacer referencia al rango B2 a E2, pero si en la celda A22 dice FALSO (Bucaramanga no está activa), el nombre debe hacer referencia a un grupo de celdas que estén vacías, por ejemplo al rango B6 a E6. Esto significa que Excel graficará el rango B2 a E2 sí la casilla “Bucaramanga” está activa y graficará el rango B6 a E6, sí la la casilla Bucaramanga no está activa. Como esta última hace referencia a celdas vacías, el gráfico no mostrará nada. Para crear el primer “Nombre” realice el siguiente procedimiento: En la ficha “Fórmulas”, dé clic en el botón “Administrador de Nombres” y de la ventana que aparece, dé clic en el botón “Nuevo”. Diligencie los datos como se muestran en la imagen: image n:

136

Capítulo 14


El asistente se lee de la siguiente manera: “Sí el valor de la celda A22 es Verdadero, el nombre “Bucaramanga” hará referencia al rango B2 a E2. Sí el valor de la celda A22 es Falso, el nombre “Bucaramanga” hará referencia al rango B6 a E6”. =SI(Hoja6!$A$22;Hoja6!$B$2:$E$2;Hoja6!$B$6:$E$6)

Condición

En caso Verdadero

En caso Falso

Nota: •

•

La función SI no lleva operador de comparación (>,<,>=,><=,=, <>), porque la condición va implícita. La celda está verdadera o la celda está falsa. La expresión Hoja6! Hace referencia refer encia a la hoja donde se encuentra la tabla. En su fórmula irá el nombre de la hoja donde esté realizando el ejercicio.

Proceda a crear los nombres Cúcuta, Tunja y Bogotá.

137

Capítulo 14


Ahora debe reemplazar las series que tiene el gráfico con los nombres que se crearon. Seleccione el gráfico y dé clic en el botón “Seleccionar datos” de la ficha “diseño”. Seleccione la serie “Bucaramanga” y luego dé clic en el e l botón “Modificar”.

138

Capítulo 14


En el argumento “Valores de la serie” reemplace el rango que se muestra por el nombre “Bucaramanga”. Esto se hace así: Seleccione todo el texto que se encuentra después del símbolo admiración (!), bórrelo y luego presione la tecla de función F3 para que aparezca la ventana de nombres y de allí seleccione “Bucaramanga”. Finalice con aceptar.

Para verificar si los datos de la sede Bucaramanga se muestran o no, habilite y/o y /o deshabilite la casilla de verificación de esta sede. Repita este procedimiento para cada una de las otras sedes. Si habilita los datos de Bucaramanga y Bogotá, la gráfica deberá mostrarse como se muestra continuación.

139

Gráfico con la Fx Desref 140

Capítulo 15

Gráfico con la Fx Desref

La intención del modelo es la de precisar que tanta información es la que se desea visualizar en la gráfica a partir de una matriz o tabla que tiene muchos registros.

Para poder desarrollar este modelo de grafico es necesario contar con una tabla que registre muchas transacciones, por lo tanto, se creará una matriz que tiene como fin el ingreso de las ventas mes a mes desde enero del año 2010 hasta diciembre del año 2015. Empiece por crear la estructura de la tabla tabla así: En la celda “A1” va el campo “Fecha” y en la celda “B1” el campo “Valor”. Para crear los intervalos de las fechas entre los periodos enero 2010 a diciembre 2015 realice el siguiente procedimiento: En la celda “A2” escriba la fecha “01/01/2010”, luego seleccione desde la celda “A2” hasta la celda “A73”, ahora dé clic en el botón “Rellenar” de la ficha “Inicio” y allí clic en el opción “Series”; en la ventana que se muestra debe dejar los argumentos seleccionados como se ven en la imagen:

141

Capítulo 15


Verifique Verifique que en la celda “A73” corresponda al “31/12/2015”. Para generar los valores de la columna “Ventas”, use la Fx “Aleatorio.Entre”, esto es: Ubicado en la celda “B2”, ejecute el asistente de funciones (Shift F3) y de la categoría “Matemáticas y Trigonométricas”, Trigonométricas”, seleccione la Fx “Aleatorio.Entre” y diligencie el asistente como se ve en la imagen:

142

Capítulo 15


El asistente se lee de la siguiente manera: “Genere números al azar entre 100.000 y 1.000.000”. Copie la fórmula hasta la celda “B73” y después quite la fó rmula de esta columna (Pegado Especial – Especial – Valores). Valores). En la celda “F1” digite” Punto de Partida” y en la celda “H1” digite “No Periodos”. Estos datos son necesarios para limitar el área a graficar. El “Punto de partida” indica desde que periodo se ha de graficar graficar y el “No Periodos” indica la cantidad de datos a graficar. Suponga que se quiere graficar desde el periodo 10 y que la cantidad de periodos a mostrar sean 15; entonces en la celda “F2” digite 10 y en la celda “H2” digite 15.

Ubicado en la celda “A1” inserte un gráfico tipo “Áreas”.

143

Capítulo 15


Valor 1.200.000 1.000.000 800.000 600.000 400.000 200.000 0 1 0 2 / 1 0 / 1 0

0 1 0 2 / 5 0 / 1 0

0 1 0 2 / 9 0 / 1 0

1 1 0 2 / 1 0 / 1 0

1 1 0 2 / 5 0 / 1 0

1 1 0 2 / 9 0 / 1 0

2 1 0 2 / 1 0 / 1 0

2 1 0 2 / 5 0 / 1 0

2 1 0 2 / 9 0 / 1 0

3 1 0 2 / 1 0 / 1 0

3 1 0 2 / 5 0 / 1 0

3 1 0 2 / 9 0 / 1 0

4 1 0 2 / 1 0 / 1 0

4 1 0 2 / 5 0 / 1 0

4 1 0 2 / 9 0 / 1 0

5 1 0 2 / 1 0 / 1 0

5 1 0 2 / 5 0 / 1 0

5 1 0 2 / 9 0 / 1 0

En este gráfico se están tomando todos los 72 valores de la matriz y analizar este gráfico, con tanto dato, no es fácil; por lo tanto es necesario apoyarse con funciones que permitan delimitar desde donde y hasta donde se desea graficar. Excel tiene una Fx que lleva por nombre “Desref” y que se encarga de delimitar un área, a partir de unas coordenadas, de tal manera que por medio de esta función se puede graficar los datos de la matriz que tienen como punto de partida 10 y como número de datos 15. La función Desref se encuentra en la categoría “Búsqueda y Referencia” y consta de 5 Argumentos que son:

144

Capítulo 15


Ref : Se refiere celda inicial de la columna a delimitar. Filas: Se refiera a la fila inicial o punto de partida. Columna: Se refiere a la columna inicial Alto: se refiere a la fila Final o Número de periodos Ancho: Se refiere a la columna Final

Nota: •

•

Como los nombres a crear solo hacen referencia a los datos de una única columna (el nombre fecha para los datos de la columna A, y el nombre valores para los datos de la columna B), en este caso el argumento “Columna” o columna inicial será cero y el argumento “Ancho” o columna final será uno, esto es porque si el Desref toma como referencia una sola columna, está será la columna final y no habrá columna inicial. En el “Administrador de Nombres” no es posible ejecutar una función desde el asistente de funciones, por lo tanto, es necesario escribir la función.

Es necesario crear nombres para definir el tamaño del vector a graficar, los cuales deben ser definidos por el usuario, y que luego irán en los datos del gráfico. El primer nombre corresponde a los datos de la columna “Fecha” y el segundo a los datos de la columna “Valor”. Para crear el nombre “Fecha” realice el siguiente procedimiento: Dé clic en el botón “Administrador de Nombres” de la ficha “Fórmulas” y en la ventana que aparece dé clic en el botón “Nuevo”; proceda a diligenciar los datos en la ventana como se ve en la imagen:

145

Capítulo 15


La fórmula muestra los 5 argumentos de la función Desref los cuales están separados por el símbolo (,) y está debe quedar así: =DESREF(Hoja1!$A$1;Hoja3!$G$1;0;Hoja3!$I$1;1)

El asistente se lee de la siguiente manera: El nombre nom bre “Fecha” corresponde a los datos que cumplen con los siguientes argumentos de la función DESREF: “Ref corresponde al nombre que identifica la columna y que está en la celda A1; Filas corresponde c orresponde al valor de la celda F2 o punto de partida, Columnas debe ir el número 0 porque no existe columna inicial; Alto corresponde al valor de la celda H2 o número de periodos y Ancho va el número 1 que significa que solo hay una columna”. Ahora debe crear el segundo nombre que lleva por título “Valor”

El asistente se lee de la siguiente manera: El nombre “Valor” corresponde a los datos que cumplen con los siguientes criterios: “Ref es título de la columna que está en la celda B1; Filas

146

Capítulo 15


Corresponde al valor de la celda F2 o punto de partida, Columnas debe ir el número 0 porque no existe columna inicial; Alto corresponde al valor de la celda H2 o número de periodos y Ancho va el número 1 que significa que solo hay una columna”. Para involucrar estos dos nombres con el gráfico, seleccione el gráfico y en dé clic en el botón “Seleccionar datos” de la ficha diseño. Una vez aparezca el asistente seleccione la serie “Valor” y dé clic en el botón “Modificar”.

Aparece la ventana “Modificar serie” y muestra que los datos que se están graficando van desde la celda “B2” hasta la celda “B73”. Este rango debe ser reemplazado por el nombre ”Valores”.

Borre la expresión que se encuentra después del símbolo admiración “!” y luego presione la tecla de Función “F3” para que aparezca la ventana que muestra los nombres que han sido creados. De allí seleccione el nombre “Valor”.

147

Capítulo 15


El asistente debe quedar como se ve en la imagen:

Para vincular el nombre “Fecha” con el gráfico debe dar clic en el botón “Editar” para que aparezca la ventana “Rótulo del eje”.

148

Capítulo 15


Borre la expresión que se encuentra después del símbolo admiración “!” y luego presione la tecla de Función “F3” para que aparezca la ventana ventana que muestra los nombres que han sido creados. De allí seleccione el nombre “Fecha”.

Si cambia manualmente los límites “Punto de partida” celda “F2” y/o “No Periodos” celda “H2”, el gráfico se empezará a ajustar. Para crear un efecto dinámico use el control de formulario “Control de número”, para que éste vaya afectado los límites.

149

Capítulo 15


Inserte dos controles “Control de número” al lado lado de la celdas “F2” y “H2”. Para configurar configurar estos controles dé clic derecho sobre uno de éstos, y seleccione la opción “Formato de control”. Los argumentos para el primer control deben quedar como se ve en la imagen.

El asistente asistente se lee de la siguiente manera: “El argumento valor mínimo hace referencia al valor más pequeño que debe mostrarse en la celda, es decir, como mínimo se mostrará 5 datos, el argumentos valor máximo hace referencia al mayor valor que debe mostrarse en la celda, es decir, podrá graficar hasta 68 datos. El argumento incremento debe ser el número 1, es decir, por cada clic que se de en el control el número aumenta o disminuye de en uno en uno, y en el

150

Capítulo 15


argumento vincular con la celda, se selecciona F2, que es la celda que va a estar cambiando de número.” Realice el mismo procedimiento para el segundo control c ontrol pero modifique el argumento “Vincular con la celda” con la posición $H$2”. Los argumentos para el segundo botón “Control de número” deben quedar como se ve en la imagen:

Cambie los límites del gráfico por medio de estos dos controles c ontroles de número. Sería interesante que el gráfico informará en que momento de los límites escogidos, se hizo la mejor y la peor venta, y que éstas se muestre con un “marcador” (figura geométrica”).

151

Capítulo 15


120.000 100.000 80.000 60.000 40.000 20.000 0 0 1 0 2 / 9 0 / 1 0

0 1 0 2 / 0 1 / 1 0

0 1 0 2 / 1 1 / 1 0

0 1 0 2 / 2 1 / 1 0

1 1 0 2 / 1 0 / 1 0

1 1 0 2 / 2 0 / 1 0

1 1 0 2 / 3 0 / 1 0

1 1 0 2 / 4 0 / 1 0

1 1 0 2 / 5 0 / 1 0

1 1 0 2 / 6 0 / 1 0

1 1 0 2 / 7 0 / 1 0

1 1 0 2 / 8 0 / 1 0

1 1 0 2 / 9 0 / 1 0

1 1 0 2 / 0 1 / 1 0

1 1 0 2 / 1 1 / 1 0

1 1 0 2 / 2 1 / 1 0

2 1 0 2 / 1 0 / 1 0

2 1 0 2 / 2 0 / 1 0

2 1 0 2 / 3 0 / 1 0

2 1 0 2 / 4 0 / 1 0

Para lograr esto, en la celda “C1” digite “No Mayor” y en la celda “D1” digite “No menor”. Ahora ubicado en la celda “C2”, ejecute la función “Si” y diligencie este asistente como se ve en la imagen, luego copie fórmula.

El asistente asistente se lee de la siguiente manera: “Sí la celda B2 tiene tie ne el número mayor que se encuentra en el rango “Valor” (rango definido por la función Desref), entonces coloque ese ese dato, de lo contario use la Fx NOD() para mostrar m ostrar error (Excel no gráfica errores)”. error es)”. Para hallar el dato de menor valor ubíquese en la celda “D2”, ejecute la función “Si” y diligencie este asistente como se ve en la imagen

152

Capítulo 15


El asistente se lee de la siguiente manera: “Sí la celda B2 tiene el número menor que se encuentra en el rango rang o “Valor” (rango definido por la función Desref), entonces coloque ese ese dato, de lo contario use la Fx NOD() para mostrar m ostrar error (Excel no gráfica errores)”. El paso a seguir es crear dos nuevos nombres, “Máximo” y “Mínimo”. Para crear el primer nombre dé dé clic en el botón “Administrador de nombres” de la ficha “Fórmulas” y en la ventana que aparece dé clic en el botón “Nuevo”. Diligencie los datos como se ve en la imagen

El asistente se lee de la siguiente manera: El nombre “Máximo” corresponde a los datos que cumplen con los siguientes criterios: “Ref es título de la columna que está en la celda C1; Filas Corresponde al valor de la celda F2 o punto de partida, Columnas debe ir el número 0 porque

153

Capítulo 15


no existe columna inicial; Alto corresponde al valor de la celda H2 o número de periodos y A ncho va el número 1 que significa que solo hay una columna”. Ahora debe crear el nombre “Mínimo”

El asistente se lee de la siguiente manera: El nombre “Minimo” corresponde a los datos que cumplen con los siguientes criterios: cr iterios: “ Ref es es título de la columna que está en la celda D1; Filas Corresponde al valor de la celda F2 o punto de partida, Columnas debe ir el número 0 porque no existe columna inicial; Alto corresponde al valor de la celda H2 o número de periodos y Ancho va el número 1 que significa que solo hay una columna”. Para que estos dos nuevos nombres hagan parte de la gráfica, seleccione la gráfica y dé clic en el botón “Seleccionar datos” de la ficha f icha “Diseño”. En la ventana que aparece dé d é clic en el botón “Agregar” y de la ventana “Modificar Serie”, diligencie los argumentos como se ve en la imagen y una vez terminado dé clic en el botón “aceptar”.

La ventana “Seleccionar origen de datos” muestra que en entrada de leyenda hay 2 series que son: Valor y No Mayor.

154

Capítulo 15


Una vez más dé clic en el botón “Agregar” para traer los datos de la columna “No menor”. El asistente debe quedar como se ve en la imagen

La apariencia de la gráfica es la siguiente

155

Capítulo 15


La grafica cuenta con tres series que son: s on: Valor, No Mayor y No Menor, pero es necesario que las dos últimas series se vean en gráfico tipo “Línea con marcadores”. Seleccione el gráfico y dé clic en el botón “Cambiar tipo de gráfico” de la ficha f icha “diseño”. De esta ventana seleccione la última opción que dice “Cuadro combinado” y verifique que las series “No Mayor” y “No Menor” queden en gráfico “Líneas con marcadores”.

156

Capítulo 15


Sí desea cambiar la apariencia del “Marcador”, dé clic derecho sobre la figura de la serie No Mayor o No menor y seleccione la opción “formato serie de datos”, y del panel que aparece escoja el tipo de marcador y el tamaño..

157

Capítulo 15


158

Gráficos Imágenes, Macros y Fx Sumar.Si.Conjunto 159

Capítulo 16

Gráfico Imágenes, Macros y Fx Sumar.Si.Conjunto

La intención de este gráfico es la de mostrar las ventas que realizó cada asesor en cada sede, apoyándose en imágenes y controles de formularios, así por ejemplo: Si da clic en la foto “Manuela” y selecciona el control “Toshiba”, la gráfica tendrá que mostrar most rar las ventas que realizó esta vendedora de la marca seleccionada en cada una de las cuatro ciudades. Empiece por crear la siguiente tabla con los mismos datos

160

Capítulo 16


Ahora ubicado en la celda “G9” proceda a crear la siguiente tabla:

161

Capítulo 16


Consiga tres fotos e insértelas insértelas dando clic en el botón “Imagen” de la ficha “Insertar”. Ubíquelas estratégicamente tal como se ve en la imagen.

162

Capítulo 16


Cada una de las fotos tiene forma rectangular, pero Excel permite ajustar la foto a cualquier otra forma geométrica, para esto, seleccione la primera foto, luego dé clic en el botón “Recortar” de la ficha “Formato” y del menú que se despliega, seleccione la opción “Recortar a la forma”. Inmediatamente aparece una nueva ventana con distintas formas. Escoja la que desea para la foto.

Repita este procedimiento con cada una de las fotos. Si quiere resaltar las figuras geométricas, cambie el color y/o grosor de la línea de contorno. El reporte debe ir quedando como se ve en la imagen.

163

Capítulo 16


Paso a seguir es crear tres macros, una por cada asesor. La macro “Manuela”, se encargará de mostrar el nombre “Manuela” en la celda “F1”. La Macro “Carlos” y la macro “Felipe” harán lo mismo con sus respectivos nombres. Esto obedece a que es necesario conocer el nombre del asesor que se escogió. Este nombre es e s importante conocerlo, porque afectará algunas fórmulas que son necesarias para el modelo en desarrollo. Para crear crear la primera macro realice los siguientes pasos: Dé clic en el botón “Grabar Macro” de la ficha “programador” (Excel versión 2013) o “Desarrollador” (Excel Versión 2007 – 2010). – 2010).

En el ventana o asistente escriba “manuela” como nombre de la macro y verifique v erifique que ésta quede guardada en este libro y para finalizar dé clic en el botón “Aceptar”.

La macro empieza a grabar todas las instrucciones que Usted realice apenas desaparezca la ventana, por lo tanto, es recomendable ser preciso en e n los comandos o tareas a ejecutar.

164

Capítulo 16


La macro “Manuela” debe realizar los siguientes pasos: Ubicarse en la celda “F1”, escribir la palabra “manuela” y dar enter. Para finalizar o detener la macro dé clic en el botón “Detener” de la ficha “programador” o “desarrollador”.

Ahora debe crear la macro “Carlos” y la macro “Felipe”. Una vez creada las tres macros, tendrá que asignar cada macro a la imagen correspondiente. Esto es, clic derecho sobre la imagen que corresponde a la vendedora “Manuela”, luego dé clic en la opción “Asignar macro”, señale la macro “manuela” y para finalizar dé clic en el botón “Aceptar”.

Para confirmar si la macro está funcionando dé clic sobre la imagen de la vendedora “Manuela” y en forma automática tendrá que aparecer la palabra “manuela” en la celda “F1”. Ahora asigne la macro “Carlos” y la macro “Felipe” a las imágenes correspondientes. c orrespondientes.

165

Capítulo 16


En cuanto a las marcas de los productos, estás serán seleccionadas por medio del control “Botón de Opción”. Para insertar este control realice los siguientes pasos: Dé clic en el botón “Insertar” de la ficha “Programador” o “Desarrollador” y seleccione el control “Botón de opción”.

Ubicado estratégicamente debajo de la tabla, pinte este botón y para los otros dos botones puede copiar y pegar este último o repetir los pasos p asos anteriormente mencionados.

Para cambiar los textos de cada botón de opción, dé clic derecho sobre el primero de éstos y seleccione la propiedad “Modificar texto”. Reemplace el texto predeterminado por la palabra “Sony”. Repita este procedimiento para los otros 2 controles con las palabras “L.g” y “Toshiba” respectivamente”. El modelo debe ir quedando como se ve en la imagen.

166

Capítulo 16


Recuerde que el “botón de opción”, tiene la particularidad de ser un control de opción “única”, esto significa que de los tres controles, solo uno es posible p osible tenerlo habilitado. Para configurar estos botones debe dar clic derecho sobre el control “Sony” y seleccionar la propiedad “Formato de control”. En esta ventana indique que celda estará vinculada a este botón. Para nuestro ejercicio tome como referencia la celda “G17” y automáticamente los otros dos controles ya estarán vinculados a la misma celda.

Para verificar el estado de los botones de opción, dé clic en el primer control que corresponde a la marca “Sony” y el número 1 saldrá en la celda “G17”, pero si da clic clic en el control 2, es decir, decir, en la marca “L.g”, saldrá el número 2 en esta en esta misma celda y saldrá el número 3 si escoge el control de la marca “Toshiba”. En la celda “H17”, se hará una fórmula que permita aparezca los nombres de cada una de las marcas así: si en la celda “G17” está el número 1, saldrá la palabra “Sony”; si en la celda “G17”

167

Capítulo 16


está el número 2, saldrá la palabra “L.g”, y si en e n la celda “G17” está el número 3, saldrá la palabra “Toshiba”. Ubicado en la celda “H17”, ejecute la función “SI”, y el asistente de esta primera función debe quedar como se ve en la imagen.

El asistente se lee de la siguiente manera: “Si en la celda “G17” está el número 1, escriba la palabra Sony”. Ahora ubicado en el argumento “Valor_si_falso”, vaya al cuadro de nombre y de clic en la función “Si”. Este proceso permitirá agregar otra función otra función SI a la fórmula. Clic en la palabra “SI” que se

encuentra en el “cuadro de nombres”, para

agregar otra función “SI” (anidar).

El nuevo asistente de la función SI, debe quedar como se ve en la imagen

168

Capítulo 16


El asistente se lee de la siguiente manera: “Si en la celda “G17” está el número 2, escriba la palabra L.g, de lo contrario contrario escriba la palabra Toshiba”. La fórmula que debe quedar en esta celda es la siguiente: =SI(G17=1;"Sony";SI(G17=2;"Lg";"Toshiba"))

Verifique que la fórmula que se muestra en la celda “H17” está colocando la marca de acuerdo al botón de opción que se habilite. En resumen tenemos lo siguiente: Al dar clic en cualquier foto, saldrá un nombre en la celda F1 (Manuela, Felipe, Carlos) , y al dar clic sobre cualquier botón que representa las marcas, saldrá el nombre de ésta en la celda “H17” (Sony, Lg, Toshiba).

Nota: Los nombres de las marcas también los hubiese podido sacar con Macros, y cada macro sería asignada a cada botón de opción.

169

Capítulo 16


Ahora en la celda “H10”, debe quedar una fórmula que permita sumar todas las ventas de la columna “D”, que cumpla con tres condiciones: Deben ser ventas realizadas por el vendedor cuyo nombre aparece en la celda “F1”, de la marca que aparece en la celda “H17”, y de la ciudad que se encuentra en la celda “G10”. Para el modelo que se ve en la imagen anterior la formula debería ser la siguiente: Sumar las ventas de la columna D, que haya realizado el vendedor Carlos, de la marca Sony, en la ciudad de Armenia. La Función a usar es “Sumar.Si.Conjunto” que permite sumar números que cumplan con hasta 127 condiciones, pero nuestro modelo solo necesita tres de esas 127. Ubicado en la celda “H10”, ejecute el asistente de funciones y de la categoría “Matemáticas y Trigonométricas” seleccione la Función Sumar.Si.Conjunto”. El asistente debe quedar como se ve en la imagen:

170

Capítulo 16


En la imagen solo aparecen dos criterios o condiciones, pero a la derecha del asistente aparece una barra de desplazamiento que permite agregar más condiciones.

El asistente se lee de la siguiente manera: “ SumelascifrasdelrangocomprendidoentreD2a D30(rangosuma),quecumplacon D30(rangosuma ),quecumplaconestástrescondi estástrescondicione ciones:quedelosnombre s:quedelosnombresqueestánenel squeestánenel rangodeA2a rangodeA2aA30, A30,tomeelqueapareceenlaceldaF1,quedelasciudad tomeelqueapareceenlaceldaF1,quedelasciudadesqueapare esqueapareceenel ceenel rangodeB2aB30tomeeldatoqueapareceenG10yporúltimoquedelasmarcasqueaparecen entreelrangoC2aC30,tomelaqueapareceenH17”

Antes de arrastrar la fórmula, es muy importante tener en cuenta que todos los argumentos que se colocaron en esta función deben quedar fijo o constantes a excepción ex cepción de la celda G10, que es el único argumento que se debe mover. La fórmula de la función Sumar.Si.Conjunto es la siguiente:

171

Capítulo 16


=SUMAR.SI.CONJUNTO($D$2:$D$30;$A$2:$A$30;$F$1;$B$2:$B$30;G10;$C$2:$C$30;$H$17) En la celda “I10”, la fórmula que permite calcular el porcentaje de participación es la siguiente: =H10/SUMA($H$10:$H$13) Arrastre la formula hasta la celda “H13” y cambie le formato de los números a porcentual. El modelo debe quedar como se ve en la imagen:

Ya se tiene la tabla que se ha de graficar y que cambiará de acuerdo al vendedor y marca que se escoja. Ubicado dentro de la tabla presione las Teclas Te clas Alt F1 e inserte un gráfico.

172

Capítulo 16


El gráf ico ico está tomado como referencia la serie “ventas” y la serie “%”, pero en el gráfico solo se ve la serie ventas, ya que la serie ser ie “%”, corresponde a un número menor a 1 y no se alcanza a ver. Para poder visualizar la serie “%”, Excel ofrece el grafico con e je e je secundario. Para convertir este gráfico con eje secundario, realice los siguientes pasos: Seleccione el área del gráfico, luego de clic en el botón “Cambiar tipo de gráfico”, de la ficha “Diseño”.

En el asistente seleccione la última opción de la iz quierda que dice “cuadro combinado”. Al frente Excel permite seleccionar que tipos tipos de gráficos podrán participar participar en el mismo. La serie “ventas” debe quedar en Columna Agrupada, y la serie “%”, debe quedar en “Línea con marcadores” y activar eje secundario dando secundario dando clic en la casilla de verificación. v erificación.

173

Capítulo 16


El modelo debe quedar como se ve en la imagen.

174

Capítulo 16


Si desea ampliar el ancho de las columnas, debe dar clic derecho sobre cualquier columna y seleccionar la propiedad “Formato Serie de Datos”

. Aparece un panel panel a la derecha de la pantalla y allí en el argumento “Ancho del intervalo”, aparece por defecto la cifra 219 en Excel 2013 ó 150 en Excel 2010. Esta cifra corresponde al espacio que

175

Capítulo 16


hay entre dos columnas, por lo tanto, si desea que las columnas queden más amplias, deben reducir el ancho del intervalo. Sugiero que quede con 50. La gráfica se v e de la siguiente manera.

Título del gráfico 16.000.000 14.000.000 12.000.000 10.000.000 8.000.000 6.000.000 4.000.000 2.000.000 -

40% 35% 30% 25% 20% 15% 10% 5% 0% Armenia

Barranquilla Bucaramanga venta

Cali

%

Pruebe el modelo cambiando el vendedor y la marca, y observe como cambia la gráfica. Súper.

176

Gráfico con Macros 177

Capítulo 17

Gráfico con Macros

La intención del modelo es permitir que el gráfico muestre únicamente las ventas del asesor seleccionado ya sea con teclado o mouse; esto es, que cambie automáticamente la gráfica con tan solo ubicarse en la celda del nombre del asesor.

Empiece por crear la tabla como se ve v e en la imagen.

Una vez creada la tabla, proceda a crear nombre por cada registro, por ejemplo, definir el nombre “Álvaro “Álvaro Uribe” al rango C4 a F4, el nombre “William Jaimes” al rango C5 a F5, y así sucesivamente. Para evitar asignar los nombres uno a uno, Excel nos ofrece una técnica que permite asignarlos todos y cada uno de ellos en una sola rutina. Estos son, seleccione desde la celda B4 hasta la celda F10, luego dé clic en el botón “Crear desde la selección” que se encuentra en la ficha “Fórmulas”.

178

Capítulo 17

Gráfico con Macros

Una vez dado clic, aparece una pequeña venta que lleva por nombre “Crear nombres a par tir de la selección”. Seleccione únicamente la opción “Columna Izquierda”, y para finalizar de clic en el botón “aceptar”. Este procedimiento permitió permi tió crear siete nombres tomando como “nombre” el dato que se encuentra a la izquierda de la región sombreada.

179

Capítulo 17

Gráfico con Macros

Para verificar la creación de estos nombres, de clic en el ascensor del “cuadro de nombre” y allí se desplegará la lista de nombres asignados.

Nota: Observe que entre el nombre y el apellido del asesor va el símbolo “guion de piso” porque Excel obliga a que los nombres asignados no lleven espacios ni empiecen con número. Ahora es necesario crear una pequeña macro que muestre en la celda A1 el nombre del asesor que está seleccionado. Así, por ejemplo, si se ubica en la celda B10, el nombre “Alejandro Ordoñez” se mostrará en la celda A1, si se ubica en la celda B6, el nombre “Benjamín Gu aldrón se mostrará en la celda A1 y así sucesivamente. Para crear esta pequeña macro debe ir a la ficha “Programador” y dar clic en el primer botón de la cinta que lleva por nombre “Visual Basic”.

En la parte izquierda del editor del visual Basic se encuentra los nombres de los archivos que se encuentra abiertos, y se identifican porque comienzan con el prefijo VBAProject y el nombre del archivo va entre paréntesis. El archivo que se está trabajando (libro2) señala que solo existe una hoja de trabajo en este libro y que lleva por nombre “Hoja 1 (Hoja1)”.

180

Capítulo 17

Gráfico con Macros

La macro debe quedar en esta hoja, por tanto, de doble clic en el nombre de la hoja y automáticamente se mostrará una hoja en blanco a la derecha derec ha del editor. En la parte superior de esta hoja, se muestra dos cuadros de listas desplegables. De clic en el ascensor del primero de ellos y escoja la opción “Worksheet”.

En forma automática se muestra en el segundo seg undo cuadro de lista la propiedad “SelectionChange” y en el cuerpo de la hoja el siguiente texto: Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub

181

Capítulo 17

Gráfico con Macros

Las sentencias de la macro que se propone en el modelo deben ir en medio me dio de estas dos líneas. Escriba el código tal como se muestra en la imagen.

A continuación se explicará brevemente que significa cada sentencia de la macro: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Corresponde al encabezado del programa e indica que la macro se ejecutará al momento de realizar un cambio en la hoja de trabajo. If Intersect(ActiveCell, Range("B4:B10")) Is Nothing Then Exit Sub Esta sentencia valida que solo la macro se ejecuta eje cuta si se está posicionado en cualquier celda del rango B4 a B10. Si no lo está no hace nada Range("A1").Value = ActiveCell Si está posicionado en cualquier celda del rango B4 a B10, B10 , la macro tendrá que mostrar el dato de la celda activa (activecell) en la celda A1. End Sub Fin de la Macro

182

Capítulo 17

Gráfico con Macros

Valide que la macro este funcionado, y para ello navegue entre las celdas B4 a B10. Tendrá que mostrarse en la celda A1 el nombre del asesor de la celda que esta seleccionada. se leccionada.

Este nombre que se muestra en la celda A1, corresponde a la serie que la gráfica tendrá que ilustrar, pero como se había mencionado líneas atrás, Excel no permite que los nombre asignados a un grupo de celdas llevan espacios ni empiecen con números, por lo tanto se hace necesario que el nombre que muestra la macro en la celda A1, sea idéntico al nombre que asignó Excel. Una forma de corregir esto, es mostrar en la celda A2 el nombre tal cual se definió en Excel, usando la función “Sustituir” que pertenece a la categoría “Texto”. Ubicado en la celda A2, ejecute el asistente de funciones y de la categoría texto seleccione “Sustituir”. Diligencie el formulario tal como como se ve en la en la imagen.

El asistente se lee de la siguiente manera: “Del texto Alvaro Uribe, busque el carácter “espacio” y reemplácelo por el carácter guion de piso”.

183

Capítulo 17

Gráfico con Macros

Es necesario crear un último nombre que tenga como referencia la celda A2. Para ello de clic en el botón “Administrador de nombres” que se encuentra en la ficha “Formulas”. Ahora de clic en el botón “Nuevo” y diligencie el asistente como se ve en la imagen.

El asistente se lee de la siguiente manera: “el nombre asignado “asesor” toma como toma como referencia la lista que se muestra en la celda A2”. Es decir, el nombre “asesor” puede referenciar la lista “Alvaro_uribe”, o la lista “Benjamín Guladrón” o cualquiera de los nombres que se muestre en esta celda. Proceda a crear el grafico tomando como referencia el rango B3 a F4. Seleccione el área del gráfico y dé clic en el botón “Seleccionar Datos” de la ficha “Diseño”.

184

Capítulo 17

Gráfico con Macros

Aparece la venta “Seleccionar origen de datos”. De clic en el botón “Modificar”.

En la ventana “Modificar serie”, Excel señala que el grafico está tomando como título el dato que se muestra en la celda B4 y como valores de serie los datos que se encuentran en el rango referencian el rango C4 a F4. Remplace la referencia B4 (título del gráfico) por la celda A1, y las serie C4 a F4 por el nombre “asesor”.

185

Capítulo 17

Gráfico con Macros

No olvide guardar este archivo con la propiedad “Libro de Excel habilitado para macros”, ya que, si no lo hace, perderá la macro. Aceptar para terminar. Listo… súper.

186

Libro Graficos Inteligentes en Excel

Recommend Documents