ARCHIVOS

BUSCARV
www.transfernow.net/91bat3b43nfg
EJERCICIO DIAGNOSTICO
www.transfernow.net/11by13b6t0ja
EJERCICIO FUNCIÓN SI
www.transfernow.net/64jsj1w1gihn
EJERCICIO SI. O
www.transfernow.net/18jet1v6q1in

TABLAS DINÁMICAS

¿Que son?

Las tablas dinámicas en Excel son un tipo de tabla que nos permiten decidir con facilidad los campos que aparecerán como columnas, como filas y como valores de la tabla y podemos hacer modificaciones a dicha definición en el momento que lo deseemos.

Las conocemos como tablas dinámicas porque tú decides “dinámicamente” la estructura de la tabla, es decir sus columnas, filas y valores.

Las tablas dinámicas en Excel también son conocidas como tablas pivote debido a su nombre en inglés: Pivot tables. Son una gran herramienta que nos ayuda a realizar un análisis profundo de nuestros datos ya que podemos filtrar, ordenar y agrupar la información de la tabla dinámica de acuerdo a nuestras necesidades.

Para qué sirven las tablas dinámicas en Excel

Por la descripción que he dado hasta el momento, pareciera que las tablas dinámicas en Excel son una maravilla. El problema es que muchos usuarios de Excel no las utilizan porque parecieran ser complejas, sin embargo una vez que conoces y entiendes su funcionamiento te darás cuenta de todos sus beneficios.

Las tablas dinámicas en Excel nos ayudan a comparar grandes cantidades de datos e intercambiar fácilmente columnas por filas dentro de la misma tabla y realizar filtros que resulten en reportes que de otra manera necesitaríamos un tiempo considerable para construirlos. Considera el siguiente ejemplo.

En una hoja de Excel tengo la información de ventas de diferentes establecimientos de la empresa así como el nombre del vendedor que realizó la venta.

En unos cuantos clics puedo crear una tabla dinámica que muestre las ventas mensuales de cada uno de los establecimientos:

Esta tabla dinámica nos permite saber fácilmente que el mes de marzo tiene el monto de ventas mayor de todo el período. De la misma manera podemos saber que el establecimiento Este es el que tiene al monto de ventas mayor de todo el período.

Ahora bien, con un par de clics adicionales puedo modificar la misma tabla dinámica para conocer las ventas de cada uno de los vendedores:

Fácilmente puedo conocer que el vendedor con mayor monto de ventas en el período es Caro.

Este tipo de información no se puede obtener a simple vista de la tabla original. Fue a través de la tabla dinámica que hemos podido analizar de una mejor manera nuestros datos.

Cuando utilizar tablas dinámicas

Cuando tienes una tabla con datos que deseas analizar desde diferentes puntos de vista, será un indicador para saber que debes utilizar una tabla dinámica.

De igual manera, si tus datos tienen registros que deseas agrupar y totalizar para realizar una comparación entre ellos, será otro indicador de que una tabla dinámica será la mejor opción. Esta agrupación también aplica cuando deseas agrupar por fechas una tabla dinámica ya que podrás hacerlo con facilidad.

Procedimiento

Tabla dinámica recomendada

Crear manualmente una tabla dinámica

Haga clic en una celda del rango de datos o de la tabla de origen. Vaya a Insertar > Tablas > Tabla dinámica recomendada. Excel analiza los datos y le presenta varias opciones, como en este ejemplo con los datos de gastos domésticos. Seleccione la tabla dinámica que mejor le parezca y presione Aceptar. Excel creará una tabla dinámica en otra hoja y mostrará la lista Campos de tabla dinámica.

Haga clic en una celda del rango de datos o de la tabla de origen. Vaya a Insertar > Tablas > Tabla dinámica. Si usa Excel para Mac 2011 y versiones anteriores, el botón Tabla dinámica se encuentra en el grupo Análisis de la pestaña Datos. Excel mostrará el cuadro de diálogo Crear tabla dinámicacon el nombre de tabla o de rango seleccionado. En este caso, utilizamos una tabla denominada "tbl_HouseholdExpenses". En la sección Elija dónde desea colocar el informe de tabla dinámica, seleccione Nueva hoja de cálculo u Hoja de cálculo existente. En el caso de Nueva hoja de cálculo, tendrá que seleccionar la hoja de cálculo y la celda donde quiera colocar la tabla dinámica. Si quiere incluir varios orígenes de datos o de tabla en la tabla dinámica, active la casilla Agregar estos datos al Modelo de datos. Haga clic en Aceptar. Excel creará una tabla dinámica en blanco y mostrará la lista Campos de tabla dinámica.

CODIGO QR

EJEMPLO DE MACRO

EN ESTE LINK ENCONTRARÀS N ARCHIVO DONDE HAY UN EJEMPLO SENCILLO DE MACROS.
www.transfernow.net/79bav671zw25

EXTENSIONES DE ARCHIVOS EXCEL

Tipo de archivo de Excel 2013	Extensión	Descripción
Libro de Excel	.xlsx	Formato de archivo predeterminado de Excel. No puede almacenar código de macro de VBA ni hojas de macro de Microsoft Excel 4.0 (archivos .xlm en Excel 4.0).
Hoja de cálculo Open XML estricta	.xlsx	Versión ISO estricta del formato de archivo Libro de Excel (.xlsx).
Libro habilitado para macros de Excel	.xlsm	Usa el mismo formato XML básico que el formato de libro de Excel, pero puede almacenar código de macro de VBA. Se pedirá a los usuarios que guardan un libro de Excel que tiene código de VBA u hojas de macro de Excel 4.0 (archivos .xlm en Excel 4.0) que usen este formato de archivo.
Plantilla de Excel	.xltx	Formato de archivo predeterminado de una plantilla de Excel. No puede almacenar código de macro de VBA ni hojas de macro de Excel 4.0 (archivos .xlm en Excel 4.0).
Plantilla habilitada para macros de Excel	.xltm	Puede contener una parte VBAProject u hojas de macro de Excel 4.0 (archivos .xlm en Excel 4.0). Los libros creados a partir de esta plantilla heredan la parte VBAProject o las hojas de macro de Excel 4.0 que haya en la plantilla.
Complemento de Excel	.xlam	Programa complementario que ejecuta código adicional. Los complementos de Excel usan el formato de archivo Open XML para almacenar los datos, y permiten usar proyectos de VBA y hojas de macro de Excel 4.0.

PASOS PARA UTILIZAR LA GRABADORA DE MACROS

La grabadora de macros

Puedes crear una macro utilizando el lenguaje de programación VBA, pero el método más sencillo es utilizar la grabadora de macros que guardará todos los pasos realizados para ejecutarlos posteriormente.

La grabadora de macros almacena cada acción que se realiza en Excel, por eso es conveniente planear con antelación los pasos a seguir de manera que no se realicen acciones innecesarias mientras se realiza la grabación. Para utilizar la grabadora de macros debes ir a la ficha Programador y seleccionar el comando Grabar macro.

Al pulsar el botón se mostrará el cuadro de diálogo Grabar macro.

En el cuadro de texto Nombre de la macro deberás colocar el nombre que identificará de manera única a la macro que estamos por crear. De manera opcional puedes asignar un método abreviado de teclado el cual permitirá ejecutar la macro con la combinación de teclas especificadas.

La lista de opciones Guardar macro en permite seleccionar la ubicación donde se almacenará la macro.

• Este libro. Guarda la macro en el libro actual.

• Libro nuevo. La macro se guarda en un libro nuevo y que pueden ser ejecutadas en cualquier libro creado durante la sesión actual de Excel.

• Libro de macros personal. Esta opción permite utilizar la macro en cualquier momento sin importar el libro de Excel que se esté utilizando.

También puedes colocar una Descripción para la macro que vas a crear. Finalmente debes pulsar el botón Aceptar para iniciar con la grabación de la macro. Al terminar de ejecutar las acciones planeadas deberás pulsar el botón Detener grabación para completar la macro.

EJEMPLO

La maravillosa aplicación que utilizan los comerciales, vendedores, etc. nos extrae esta tabla, que como veréis, sólo nos muestra los datos, sin ningún tipo de formato, y esto, por así decirlo: no es presentable a nadie.

Y lo que hacemos mes a mes, es enviarle algo así a nuestro superior:

Más bonito, visual, corrigiendo los formatos de fechas, y añadiendo un promedio de ventas/día en cada vendedor, con un formato condicional para que muestre los semáforos en relación a las ventas entre ellos (Verde: más ventas; Rojo: menos ventas; Ámbar: la media). Para que este promedio salga correctamente, hemos tenido que eliminar los “0” en los fines de semana (días en los que no hay servicio de ventas).

Todas estas acciones, que hacemos mes a mes, realmente no nos comen mucho tiempo, no más de 5 minutos… salvo que nos lo pidan diario o semanal, tengamos varios grupos de vendedores… Estoy seguro de que este pequeño tutorial te será de mucha ayuda para tus tareas.

Vamos con algo de acción.

Localicemos la pestaña de Programador en Excel 2007. Si no la tienes activa, haz clic en este otro enlace para enseñarte a hacerlo en Office 2007 y 2010.

Y ahora, a la izquierda, veremos varias opciones… la que nos interesa: Grabar Macro.

Es cierto que hay mucha gente que programa sus macros para tareas más complejas, pero este tutorial no es para esa gente… es para todo el mundo que empieza con excel o que no tiene muchas tablas en el asunto. La mejor manera de empezar es grabando una Macro.

Antes de grabar la Macro, vamos a preparar un poco nuestra tabla… simplemente vamos a bajar los datos, salvo los títulos, dejando 3 filas vacías encima de los datos de los vendedores. De la siguiente forma:

¿Motivo? Simple… las semanas comienzan en lunes, y el primer día del que tenemos datos es jueves, así que dejamos espacio para esos días. En este mes no nos servirá para nada, pero sí para futuros meses, ya le dejamos espacio. En el futuro, sólo tenemos que pegar los datos empezando en el día que sea necesario.

Ahora sí, vamos con la Macro.

Cuando pulsamos el botón Grabar Macro, nos aparecerá esta ventana:

Vamos a ponerle el nombre. Para esto, Office es un poco especialito, dado que:

• El primer carácter del nombre de la macro debe ser una letra.
• Los demás caracteres pueden ser letras, números o caracteres de subrayado.
• No se permiten espacios en un nombre de macro; puede utilizarse un “_” como separador de palabras.
• No puedes utilizar un nombre de macro que también sea una referencia de celda; de lo contrario puede aparecer un mensaje indicando que el nombre de la macro no es válido.

Llamemos a nuestra macro: Estilo_informe, y le indicaremos que nos lo guarde en “Libro de macros personal”, así lo tendremos disponible en cualquier hoja de Excel que abramos.

Desde el momento en que pulsemos el botón Aceptar, Excel comenzará a grabar todas nuestras acciones, que serán las que programen nuestra macro, así que vayamos con cuidado para no tener que repetir acciones, o borrar la macro.

Sigamos estos pasos:

1. Le damos formato a la fecha, para que se muestre como más te guste (si indicas que te muestre el nombre, podrás identificar rápidamente los fines de semana).
2. Coloreamos la tabla a nuestro gusto. Yo diferencio días de la semana y fin de semana, para ver grupos de días más cómodamente. También le pongo las celdas que no uso en color blanco para darle un aspecto más limpio… pijadas personales.
3. Quitamos los “0” de los fines de semana para que el promedio nos salga correctamente.
4. Añadimos una nueva tablita, debajo de la grande, que sea resumen del número de ventas y el promedio de ventas/día de cada vendedor. A la derecha de la tablita añadimos los totales. Como el mes que hemos escogido es de 31 días, en el futuro no tendremos que mover la tabla, dado que no habrá meses de más de 31 días.
5. Coloreamos esta tablita en el estilo de la tabla superior.

Hemos terminado, ahora le damos al botón “Detener Macro”, y listo.

Ahora, cada vez que queramos dar forma a nuestra tabla extraída del programa de los vendedores, lo único que tendremos que hacer será “dejar X filas vacías encima de los datos” en relación al día de la semana con que empiece el mes, y “llamar” a nuestra macro para que haga su trabajo. Nos vamos a la Pestaña Programador y le damos al botón de Macros, y escojemos la nuestra de la lista. Puede que tengas que desplegar el menú de “Macros en:”.

Si nos damos cuenta, lo único que hemos hecho a mayores de la preparación de nuestro informe ha sido grabar una Macro que ha guardado todas las acciones que hemos hecho.

Cada uno le encontrará una utilidad diferente, y programará una macro diferente dependiendo de sus necesidades, pero desde luego todos coincidirán en que es una herramienta imprescindible para el ahorro de tiempo en las tareas más rutinarias.

• Podemos programar macros para eliminar contenido en un clic.
  • Por ejemplo, si el programa de vendedores extrae las tablas perfectas, podemos simplemente eliminar los “0” y añadir nuestra tabla resumen.
• Podemos programar macros para dar un formato determinado.
• Podemos programar macros para que nos creen gráficos obteniendo datos de un rango determinado.
• Podemos asignar una macro a un botón o una imagen para no tener que buscarla en la Pestaña Programador.
• Podemos…

Podemos hacer lo que todos queremos hacer… hacer más en menos tiempo, e invertir el restante en cosas más placenteras o productivas… al fin y al cabo, la procrastinación está ahí para hacer uso de ella de vez en cuando, no va a ser todo trabajar o estudiar, no?

LENGUAJE VBA

Visual Basic for Applications

Microsoft VBA (Visual Basic para aplicaciones) es el lenguaje de macros de Microsoft Visual Basic que se utiliza para programar aplicaciones Windows y que se incluye en varias aplicaciones Microsoft. VBA permite a usuarios y programadores ampliar la funcionalidad de programas de la suite Microsoft Office. Visual Basic para Aplicaciones es un subconjunto casi completo de Visual Basic 5.0 y 6.0.

Microsoft VBA viene integrado en aplicaciones de Microsoft Office, como Outlook, Word, Excel, Access y Powerpoint. Prácticamente cualquier cosa que se pueda programar en Visual Basic 5.0 o 6.0 se puede hacer también dentro de un documento de Office, con la sola limitación que el producto final no se puede compilar separadamente del documento, hoja o base de datos en que fue creado; es decir, se convierte en una macro (o más bien súper macro). Esta macro puede instalarse o distribuirse con sólo copiar el documento, presentación o base de datos.

Su utilidad principal es automatizar tareas cotidianas, así como crear aplicaciones y servicios de bases de datos para el escritorio. Permite acceder a las funcionalidades de un lenguaje orientado a eventos con acceso a la API de Windows.

Al provenir de un lenguaje basado en Basic tiene similitudes con lenguajes incluidos en otros productos de ofimática como StarBasic y Openoffice.

Sub LoopTableExample

    Dim db As DAO.Database
    Dim rcs As DAO.Recordset

    Set db = CurrentDb
    Set rcs = db.OpenRecordset("SELECT * FROM tblMain")

    Do Until rcs.EOF
        MsgBox rcs!FieldName
        rcs.MoveNext
    Loop

    rcs.Close
    db.Close
    Set rcs = Nothing
    Set db = Nothing
End Sub

VBA puede ser usado para crear una función definida por el usuario para usar en una hoja de Microsoft Excel:

Public Function BUSINESSDAYPRIOR(dt As Date) As Date

    Select Case Weekday(dt, vbMonday)
    Case 1
        BUSINESSDAYPRIOR = dt -3
    Case 7
        BUSINESSDAYPRIOR = dt -2
    Case Else
        BUSINESSDAYPRIOR = dt -1
    End Select
End Function

VBA también tiene acceso a funciones internas de Windows en diversos grados, y puede acceder recursos desde horarios hasta archivos y control:

Sub ObtenerFecha()

    MsgBox "La fecha es " & Format(Now(), "dd-mm-yyyy")


End Sub

Se puede acceder al lenguaje al ingresar al menú herramientas. Y una vez allí MACRO y EDITOR DE VISUAL BASIC.

FUTURO

El siguiente paso natural en la evolución de VBA es dejar de ser un subconjunto de Visual Basic y serlo de la plataforma .NET. Microsoft no planea hacer mejoras significativas a VBA en el futuro. Aunque continuará dando soporte a las licencias de VBA que se han ido ofreciendo, VBA está siendo sustituido por las Herramientas para Aplicaciones de Microsoft Visual Studio (VSTA: Visual Studio Tools for Applications) y las Herramientas para Office de Microsoft Visual Studio (VSTO: Visual Studio Tools for Office). Estas herramientas funcionan bajo la plataforma .NET. Desde el 1 de julio de 2007, Microsoft ya no ofrece nuevas licencias de VBA a nuevos clientes. Los que poseían una licencia de VBA podrán conseguir una licencia de las nuevas soluciones por parte de Microsoft.