Mostrando entradas con la etiqueta Excel. Mostrar todas las entradas
Mostrando entradas con la etiqueta Excel. Mostrar todas las entradas

martes, 1 de septiembre de 2015

Actualizar tablas en Excel

En el siguiente ejercicio vamos a ver como actualizar tablas en excel. Nos centraremos en modificar unos precios con código VBA. Modificaremos los valores de la primera tabla teniendo en cuenta los datos de la segunda tabla. Cada vez que queramos hacerlo pulsaremos un botón.

Partimos de una tabla de precios que queremos modificar de acuerdo con los valores de la segunda tabla.
actualizar-tablas-en-excel

Para lograr esto vamos a introducir el siguiente código VBA:

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim j As Integer
    
    For i = 2 To WorksheetFunction.CountA(Range("A:A"))
        For j = 2 To WorksheetFunction.CountA(Range("D:D"))
            If Cells(i, 1).Value = Cells(j, 4).Value Then
                Cells(i, 2).Value = Cells(j, 5).Value
            End If
        Next j
    Next i
End Sub

En primer lugar vamos a insertar un botón donde implementaremos estas líneas de código.

En la ficha programador elegimos el botón de comando marcado en rojo, tal como vemos en la imagen. Si la ficha de programador no está activa, lo podremos hacer de la siguiente forma:
Iremos a Archivo/Opciones y en la ventana que se abre en el panel izquierdo donde marcaremos Personalizar cinta de opciones y a continuación en la ficha programador.
Pulsamos el botón y arrastrando el botón sobre la hoja tenemos el botón:


Para introducir el código haremos doble clic sobre el botón y se abre la ventana de Visual Basic.



Escribiremos las instrucciones entre la línea de Private y la de End Sub. De esa forma cuando hagamos clic en el botón se ejecutará el contenido de ese procedimiento.
Comentando un poco el contenido del programa, en primer lugar declaramos dos variables, i y j con Dim de tipo entero:

    Dim i As Integer
    Dim j As Integer

Una vez declaradas utilizamos dos bucles for para ir recorriendo ambas tablas, en caso de encontrar el mismo producto en ambas tablas cambiamos el valor del precio asignando el precio de la segunda a la primera tabla. La función CountA nos da el número total de celdas ocupadas que tenemos en un conjunto de celdas. En este caso el for recorrerá solo las celdas desde la segunda fila hasta el final de las tablas gracias a que conocemos cuantas celdas ocupadas tenemos. La variable i recorre la primera tabla y la variable j recorre la segunda tabla.
También modificamos el texto del botón con la propiedad Caption. Para ver las propiedades del botón pulsamos en propiedades en el grupo de Controles de la barra Programador. Dentro de misma tenemos bastantes propiedades y en Caption escribiremos Actualizar precios:


De esta forma el aspecto del botón cambiará por algo mas cercano y que nos indica lo que hará cuando lo pulsemos:


Cerramos la ventana de propiedades y pulsamos en modo Diseño para que esté operativo el botón:



Después solo basta hacer un solo clic en el botón para tener los precios como queríamos:


Espero que les haya resultado útil este ejemplo en el que hemos modificado varios datos con dos  tablas en excel haciendo clic en un botón.

miércoles, 8 de enero de 2014

Formato tablas dinámicas


Feliz Año 2014 a todos. Espero que vuestros deseos se cumplan. Continuamos con tablas dinámicas. Vamos a ver cómo dar formato a las celdas. Seguimos con la tabla del post anterior a partir de la cual vamos a crear más tablas dinámicas. También podemos ver videos donde nos hablan de dar formatos en las tablas como lo hace Raymundo Ycaza.
 
Para cambiar el formato podemos hacerlo de varias formas:

  1. Seleccionando las celdas y dando directamente el formato (negrita, moneda…)
  2. Entrar en configuración de campo valor, pulsar en Formato Número y damos el formato que nos interesa.
 

Esta acción nos abre la ventana:

Donde pulsamos en el botón Formato Número.

Que hacer con las celdas vacías
 
Si tenemos alguna celda vacía de la tabla dinámica se debe a que no tenemos datos para rellenar esa celda. En la ventana opciones de tabla dinámica, dentro de Diseño y Formato, podemos hacer dos cosas:

  1. Desactivar la casilla Para celdas vacías, mostrar con lo que en esas celdas se mostrarán ceros.
  2. Para celdas vacías poner un dato o valor para que lo muestre en caso de no tener datos en la celda, Excel almacena el valor cero en esa celda.


Como colocar los campos

En cada zona podemos colocar más de una etiqueta, por ejemplo en Etiquetas de fila podemos bajar Vendedor e Idpedido. En este sentido, hay que tener cuidado como los bajamos, es decir, importa el orden en que se bajan. En este sentido, si hacemos caso al orden sugerido ahora, Vendedor Idpedido, aparece el nombre de un vendedor y a continuación los idpedidos realizados por el mismo.


La tabla queda así:

Tenemos todos los pedidos agrupados por cada vendedor

En cambio, si cambiamos el orden, es decir, primero Idpedido y después Vendedor, tal como muestra la siguiente imagen: la tabla aparece primero el Idpedido y al lado el vendedor que lo ha gestionado.
El aspecto de la tabla será el siguiente:

 
Bastante distinto al anterior. Con pequeños cambios, obtenemos resultados distintos. Es importante colocarlos en el orden adecuado para obtener lo que interesa.
Para avanzar un poco más podemos aprender sobre formatos condicionales. Una muestra lo tenéis en Excelforo, donde ilustran como aplicar los formatos condicionales.
En el próximo post hablaré de los filtros en las tablas dinámicas, y de como a partir de la versión 2010 se introduce una nueva herramienta, la segmentación de datos. Un saludo a todos.

viernes, 27 de diciembre de 2013

Cómo crear tablas dinámicas

Después de un tiempo sin publicar, retomamos el pulso con este post. Vamos a ver cómo podemos trabajar con tablas dinámicas. Como se crean, y cómo podemos visualizar los datos que nos interesan de un conjunto de datos previos. Excel 2010 presenta mejoras con respecto a versiones anteriores como la segmentación de datos. Una introducción se puede ver en este pdf.

Crear una tabla dinámica
En primer lugar nos situaremos en la tabla que contiene los datos con los que vamos a trabajar.




 A continuación vamos al menú insertar y dentro del grupo Tablas pulsamos en Tabla dinámica.

Aparece un cuadro de dialogo donde nos pregunta donde queremos colocar la nueva tabla.

Pulsaremos en aceptar para crear la tabla en una nueva hoja. De hacerlo en una hoja existente deberíamos indicar la ubicación diciéndole a partir de que celda interesa colocarla. Se abre una nueva hoja de cálculo con el siguiente aspecto:

El cuadro de la parte derecha es el lugar dónde aparecerá la tabla dinámica y en la zona derecha tenemos los campos de la tabla dinámica. Para crearla solo tenemos que arrastrar los campos a la zona correspondiente. Por ejemplo, queremos ver la cantidad de productos facturados por cada vendedor. Para ello arrastramos vendedor a Etiqueta de fila y a Valores llevamos el campo Cantidad.

El aspecto de la tabla será:

Como vemos cuando llevamos un campo de tipo número a valores, por defecto nos saca la suma de estos valores.
En cambio, si llevamos un campo de tipo texto, Excel nos contará el número de veces que podemos ver cada nombre en la tabla. Mostrará el número de repeticiones de cada cadena. Si llevamos vendedor a Valores y también lo llevamos a Etiquetas de fila (arrastrándolo dos veces, una para cada zona) el resultado será el siguiente:

Que nos dirá cuántas veces aparece cada vendedor en nuestra tabla.
Seguiremos con este tema, viendo que nos permiten hacer las tablas dinámicas cómo establecer porcentajes o ver los datos a partir de los que se ha generado un dato de la tabla dinámica. Un saludo a todos!!

miércoles, 9 de enero de 2013

Función elegir de excel

Cuando utilizamos botones de opción podemos utilizar la función SI para seleccionar una acción, en el caso de tres o más opciones estamos obligados a utilizar anidamiento de funciones. Una buena alternativa  a esta situación es usar la función elegir, que no obliga a usar funciones anidadas.

Vamos ver su uso con botones de opción. Crearemos tres de ellos. En la ficha programador vamos al grupo controles. En controles de formulario pinchamos en el botón de opción.

Nuestro botón insertado es:

Siguiendo el mismo proceso añadimos dos botones más:


Para alinearlos sólo basta seleccionar uno y con las teclas CTRL y las flechas de dirección las movemos para que queden bien. El paso siguiente es asociar dichos botones a una celda. En la celda aparecerá un 1, un 2 o un 3 dependiendo del botón de opción sobre el que pinchemos. En el caso de la imagen anterior en la celda asociada a los botones sería un 2.
Para seleccionar la celda donde aparecerán los valores numéricos pulsamos con el botón derecho del ratón y en el menú contextual seleccionamos Formato de control:
Para asociar una celda a los botones
Pulsamos, seleccionamos la pestaña Control y en Vincular con la celda seleccionamos una celda, por ejemplo E2. Pulsamos en aceptar:


Cada vez que pulsemos en un botón la celda E2 cambiará a un valor determinado. Ahora vamos a cambiar el texto de los botones. Botón derecho y modificar texto en cada uno de ellos nos permite poner los siguientes textos a los botones:

Creamos una tabla en la que calculamos el total facturado:


La suma de los subtotales nos da el total. Los botones de opción nos permiten aplicar distintos descuentos a ese total. Para calcularlo hemos utilizado la función Elegir.
La sintaxis de la función es:


Según el valor del argumento num_índice elegir devolverá uno de los argumentos restantes de la función. Si num_índice es 1, elegir devuelve valor 1, si es 2 elegir devuelve valor 2, y así sucesivamente. Estamos obligados a utilizar los dos primeros argumentos, el resto son opcionales. Excel nos permite introducir hasta 254  valores distintos (serían 255 argumentos con el índice).  Como valores podemos introducir una cadena de texto (entre comillas) o expresiones que debe calcular Excel. En nuestro ejemplo hemos configurado la función elegir del siguiente modo, teniendo en cuenta el valor de la celda E2:

Es decir, si en la celda E2 tiene un 1 aplicaremos un descuento del 5%, si es 2, el descuento aplicado será del 7,5% y con 3 en E2 el descuento asciende al 10% del total.
El valor de E2 es controlado o decidido con los botones de opción y decidimos sobre que descuento aplicamos.
No hemos anidado ninguna función dentro de elegir, en cambio, si hubiéramos usado la función si, deberíamos poner la función si como argumento del primer si usado. Con elegir hemos simplificado el calculo del total aplicando un descuento decidido con unos botones de opción.

Total con un descuento del 10%  usando elegir

Sólo nos queda un detalle, ocultar el contenido del a celda E2, para hacerlo podemos aplicar al color del texto el mismo de la celda, en este caso el color blanco. De esa forma no se ve el valor contenido de la celda, sigue ahí pero no se ve.

domingo, 18 de marzo de 2012

Tablas dinámicas. Personalizar resultados

A partir de los datos de una tabla vamos a ver como personalizar los resultados. Se pueden obtener los porcentajes de cada datos con respecto del total de la columna, u obtener porcentajes con respecto al total de una fila. Las tablas dinámicas permiten realizar estas transformaciones de los datos mostrados con las mismas. Podemos ver una introducción a este tipo de tablas y como se trabaja con ellas en esta entrada. Ahora vamos a calcular el porcentaje que supone una cantidad dentro de la columna en la que se encuentra sobre la suma de valores de toda la columna. Comencemos.


Hallar el % de la columna
Partimos de la siguiente tabla:


Para insertar una tabla dinámica nos dirigimos al menú insertar, donde pulsamos en tabla dinámica tal como muestra la siguiente imagen:



Ahora nos preocupa elegir la ubicación de la tabla dinámica que la podemos colocar en la misma hoja de datos de la tabla a partir de la cual queremos generar la tabla dinámica. Hemos elegido una hoja de cálculo nueva.

una vez que pulsamos en aceptar ya estamos en condiciones de configurar nuestra tabla dinámica. Vamos a colocar los campos Empresa y número de empleados dentro de la tabla dinámica. El campo Empresa irá como rótulo de fila y Nº Empleados lo pondremos en el área de datos o valores. Como nos interesa saber el % de cada cantidad de trabajadores de cada empresa sobre el total de trabajadores colocaremos dos veces el campo en la zona de valores. Los campos quedan así:


Configuración del campo Nº Empleados
Sólo nos queda cambiar la configuración del segundo elemento que hemos llevado al área de valores. Pulsaremos sobre él con el botón derecho y seleccionamos Configuración de campo valor...



En Resumir por dejamos activada la suma y hacemos clic en la pestaña Mostrar valores como. En la misma desplegamos el menú y seleccionamos % de la columna y pulsamos en aceptar:

El resultado es el que esperamos, tenemos en una columna el número de empleados, y en la otra el porcentaje que representa los empleados de cada empresa sobre el total de los empleados.

Resumiendo
Con las tablas dinámicas es posible modificar la apariencia de los resultados para presentarlos como nos interesa verlos. En este caso, los datos los hemos cambiado para verlos en forma de porcentaje sobre el total de la columna. Un saludo!!

lunes, 5 de diciembre de 2011

Como filtrar en Excel

En una entrada anterior mostramos como utilizar los filtros avanzados. Para completar un poco más este tema vamos a ver como copiar en otro lugar de la hoja aquellos registros que tienen un campo vacío o justo lo contrario, que nos muestren sólo los registros que tienen datos guardados en un campo (campos no vacíos).

Obtener todos los registros que tienen un campo vacío (fecha compra)

Partimos de la siguiente tabla:


Como se observa en la misma, algunos registros en la tabla no tienen el dato de la fecha de compra. Mediante un filtro avanzado vamos a obtener todos los registros sin fecha. En primer lugar vamos a colocar los criterios para obtener todos los registros vacíos. El criterio lo escribiremos de la siguiente forma:

Escribimos esa comilla sencilla y el signo igual. No hay que teclear más. Pulsamos en intro y ya tenemos el criterio escrito. El rango de criterios es G3:G4. Ahora ya podemos crear el filtro.

Nos colocamos dentro de la tabla y vamos a Datos /Avanzadas:


Ahora tenemos la ventana Filtro avanzado donde marcaremos el rango de criterios y donde copiaremos el resultado del filtro:

El rango de la lista lo ha reconocido. Ahora en rango de criterios seleccionaremos las celdas G3:G4 y lo copiaremos en otro lugar marcando la acción Copiar a otro lugar. A continuación marcaremos en Copiar a la celda donde aparecerán los registros que no tienen la fecha.


Los registros que nos interesan obtener aparecerán a partir de la celda A15:


Obtener todos los registros que tienen el dato de fecha compra

Ahora vamos a modificar el criterio para obtener lo contrario. Queremos obtener sólo los registros de los que disponemos su fecha de compra. El criterio será:


En G4 tenemos que escribir <>. Pulsamos intro y repetimos el proceso ya descrito en el caso anterior. Copiaremos el resultado a partir de la celda A22:


Hacemos clic en Aceptar, y el resultado es:



Con esta entrada se pretende completar un poco más la relación de criterios que podemos usar en un filtro avanzado. Ahora podemos localizar registros con campos vacíos y registros con campos no vacíos.

jueves, 13 de enero de 2011

Formas de ejecutar una macro en Excel

Vamos a ver algunas formas que tenemos para ejecutar una macro creada:

1ª Forma

Para ejecutar una macro sólo utilizamos una combinación de teclas. Cuando empezamos a crear cualquier macro vamos a Herramientas / Macro / Grabar nueva macro y aparece en pantalla la siguiente imagen:



Esta ventana nos da permiso para modificar el nombre de la macro, que por defecto es Macro1. Tenemos, además, un método abreviado para ejecutar la macro con la tecla de control CTRL. Colocamos un carácter, y podemos pulsar en aceptar. A continuación ejecutamos los pasos que queremos grabar. Detenemos la grabación. A partir de ahí, cada vez que deseamos que se ejecute la macro solo hay que pulsar la tecla CTRL y la tecla que hayamos elegido antes. Por ejemplo, puede ser CTRL+Ñ. Hay que tener un poco de cuidado para elegir la tecla, ya que algunas teclas estar reservadas para guardar (CTRL+G) o imprimir (CTRL+P) una hoja en combinación con la tecla CTRL.

2ª Forma

Otra manara sería hacerlo así: con la macro creada vamos a Herramientas / Macro / Macros...

Se abre una ventana con la relación de macros que hemos grabado hasta el momento.

Como vemos, tenemos en nuestro libro de cálculo dos macros, macro1 y macro2. Si seleccionamos macro2 solo hay que pulsar en el botón ejecutar para que esa macro muestre el resultado de todas sus acciones. Pulsamos en el botón ejecutar y vemos su resultado:

3ª Forma

Mediante una barra de herramientas. Ha sido una publicada en una entrada reciente. Con la macro grabada vamos a Herramientas /Personalizar y dentro de la pestaña Barra de Herramientas pulsamos en el botón Nueva. Damos nombre a la barra de herramientas, por ejemplo Mis macros, y vamos a la pestaña Comandos de Personalizar. En categorías se selecciona macros y en en el panel derecho arrastramos la imagen del sol (Personalizar botón) a la nueva barra de herramientas. Cerramos la ventana personalizar. Sólo queda asignar el botón a la macro creada.
Hacemos clic en el botón recién incorporado. Se abre la  ventana Asignar macro donde seleccionamos el nombre de la macro que hemos creado y pulsamos en aceptar. Esta es la forma que tenemos para asignar botones a una macro en cualquier barra de herramientas. Ahora ya podemos pulsar en el botón para que se ejecuten todas las instrucciones que componen la macro.  

4ª Forma

Mediante un botón de comando. En primer lugar tenemos que visualizar la barra de herramientas Formularios. Vamos al menú Ver /Barras de herramientas y en el menú que se despliega elegimos Formularios:
Ahora hacemos clic en el botón de Formularios:

Y sobre la hoja pinchamos y arrastramos el ratón para generar el botón. Al soltar el ratón aparece también la ventana Asignar macro para seleccionar la macro que nos interesa que se ejecute con ese botón.


Ahora seleccionamos macro1 y pulsamos en Aceptar. Podemos modificar el nombre del botón, pulsamos en el botón, borramos el nombre por defecto y escribimos Macro1:



Y pulsamos fuera del mismo. Volvemos a ir al botón, el cual se convierte en una mano y pulsamos. Tenemos ahora el resultado del mismo:


Actualizar tablas en Excel

En el siguiente ejercicio vamos a ver como actualizar tablas en excel. Nos centraremos en modificar unos precios con código VBA. Modificare...