Cómo cambiar el formato de fichero guardado por defecto.

Trabajando con el equipo (PC) de un alumno hace unos días me dí cuenta que cada vez que guardaba un fichero nuevo, creado en la formación, y a pesar de trabajar con una versión Excel 2013, el fichero se guardaba con formato .xls (Libro de Excel 97-2003).
Le pregunté el motivo, pero no supo explicarme el porqué... No sabía cómo evitar tal cosa.

La solución es bien sencilla.
Navegaremos a la ficha Archivo > Opciones de Excel > menú Guardar > sección Guardar Libros:

Seleccionaremos la opción deseada de entre las existentes; normalmente elegiremos: Libro de Excel (*.xlsx)

A partir de este momento del cambio, los siguientes libros NUEVOS guardados, lo harán con la extensión elegida.
Por supuesto, no creo necesario aclarar, que los libros existentes con lo que trabajemos, se seguirán guardando con su extensión...(o bien emplear la opción de 'Guardar como...' para cambiar el formato de grabación.

Seguir leyendo...

Cómo abrir un libro de Excel con las macros deshabilitadas.

Veremos hoy un pequeño truco para prevenir que ciertas macros, construidas bajo el evento _Open, se ejecuten al abrir nuestros Libros de Excel... al hilo de lo que un lector planteaba:

...Hace poco baje un programa hecho en vba para excel para manejar inventario, al mirar su código veo que tiene una rutina Auto_open que contiene otra que lanza un formulario para validar el usuario y la clave, pero además cierra la ventana de la Aplicación Excel quedando únicamente el formulario anterior, funcionaba bien, pero el código para la validación del usuario y la clave era duro, así que busque otra rutina en internet que fuera más flexible y la ingresé sin realizar copia del archivo, ahora cuando abro el archivo me pide usuario y clave, al dársela me dice que clave errada y no se cómo regresar a la edición del código en VBA porque siempre cierra la Ventana de la Aplicación Excel y me muestra el formulario anterior...

Una solución sería la siguiente:
1- Cambiaríamos la ubicación del fichero; así nos aseguramos que la ubicación no sea de confianza.
2- Cambiamos la Configuración de seguridad de macros a 'Deshabilitar todas las macros con notificación'

Bajo estos supuestos, y al trabajar con macros, al abrir el fichero, nos solicitará la Habilitación de macros.

En nuestro caso, no las habilitaremos. Así todas las macros quedarán inactivas, y podrás entrar en el editor de VB, en los módulos de código y ver/desactivar los procedimientos que necesites...

Otro procedimiento más sencillo es presionar desde cualquier lugar, mientras abrimos el Libro de Excel, la tecla mayúscula.
Con esto conseguimos se abra una ventana diálogo de Seguridad de macros, donde podremos optar por Habilitar o Deshabilitar las macros:

Si optamos por el botón Habilitar macros (y esto es lo interesante para responder la pregunta del lector) saltaremos aquellas macros construidas bajo el evento _Open (Auto_Open o Workbooks_Open)!!!...
mientras que el resto de macros contenidas en el libro, quedan habilitadas y funcionales... perfecto para nuestros fines.

Un par de trucos sencillos pero prácticos.

Seguir leyendo...

Un gráfico de burbujas para comparar dos magnitudes.

En esta semana una alumna me planteaba cómo construir un gráfico que mostrara una comparativa muy visual entre un dato objetivo y uno real sobre la ventas de una empresa.
La meta era genera un gráfico de este estilo:

El tema de hoy está claro, debemos representar y comparar las ventas de los tres productos vendidos.. y que además se comprenda muy rápidamente y de manera visual cúal es la diferencia del importe de ventas marcado como objetivo respecto al conseguido.

En este ejercicio se plasmará mediante un gráfico de burbujas, y en concreto uno de burbujas 3D.

Para ello comenzaremos insertando un gráfico de burbuja 3D (sin seleccionar ningún rango!!). Por tanto, desde la ficha Insertar > grupo Gráficos > desplegamos Dispersión > marcamos Burbuja 3D:

En el siguiente paso accederemos a la opción Seleccionar datos..., lo que abrirá la ventana diálogo de 'Seleccionar origen de datos' y donde agregaremos dos series de datos.

La primera representará el valor de las Ventas Objetivo, tomando los datos mostrados en la imagen siguiente:

Fijémonos que el rango para los 'Valores de la Y' y para el 'Tamaño de la burbuja' tomamos el mismo rango de datos, i.e., las celdas B2:B4 (que son las ventas objetivo).

Agregamos ahora la siguiente serie de datos, que representará las ventas reales alcanzadas. Repetimos la acción siguiendo los rangos seleccionados en la imagen siguiente:

Notemos ahora que el rango para los 'Valores de la Y' sigue siendo el rango B2:B4 y que para el 'Tamaño de la burbuja' tomamos el rango C2:C4 (que son las ventas reales).

¿Por qué marcamos en ambas series como 'Valores de Y' el mismo rango B2:B4?... para conseguir el efecto de superposición, y optar así a una fácil comparativa.
Dejamos para los valores del 'Tamaño de la burbuja' la comparativa visual entre las cantidades Reales y Presupuestadas.

El resto de aspectos para alcanzar el gráfico son meramente ornamentales.. aplicando Formato a los ejes, añadiendo títulos al gráfico y al eje vertical primario, aplicación de algún estilo de diseño, etc...

Seguir leyendo...

VBA: La Colección Controls de un UserForm.

Días atrás un lector planteaba la siguiente cuestión:

En un formulario tengo 3 controles del tipo opción dentro de un grupo de opciones y a cada uno de ellos quiero renombrarlo (con la propiedad .Caption) en función de lo que tenga en un rango de celdas en una hoja de Excel. He probado a poner en el código cuando se activa el formulario algo así: for c=1 to lista_nombres.count a="formulario.opcion_"&c&".Caption" a=lista_nombres.offset(c-1,0).value next c pero no me hace nada. es como si no reconociera bien el nombre de la propiedad.

Bien, lo que falló en el intento del lector fue la forma en cómo construyó y aplicó la propiedad .Caption a/los Control/es... y sobre todo en la forma en que llamaba o nombraba los Botones de opción de su formulario.
Una manera correcta sería emplear la collection .Controls dentro del UserForm.

Veamos nuestros puntos de partida.
En primer lugar tenemos un rango de tres celdas (A1:A3) con tres valores (al que hemos asignado un Nombre definido:= 'lista_nombres'):

Por otro lado tenemos el siguiente formulario de usuario (al que he renombrado como 'formulario', y el que tiene dispuestos los siguientes controles:

1- Un cuadro o Frame que contiene los tres botones de opción (llamado 'Frame1').
2- Tres botones de opción (OptionButton) llamados 'opcion_1', 'opcion_2' y 'opcion_3'.
3- Un Botón de comando, llamado CommandButton1, destinado a ejecutar el proceso....

Para resolver nuestra cuestión en particular, insertamos el siguiente código dentro de la ventana de código del UserForm desde el editor de VB:

Private Sub CommandButton1_Click()
'recorremos las celdas de nuestro rango
For c = 1 To Range("lista_nombres").Count
     'creamos/componemos los nombres de los botones de opción...
     a = "opcion_" & c
     'los llamamos mediante el uso de la collection .Controls
     Me.Controls(a).Caption = Range("lista_nombres").Item(c).Value
Next c
End Sub

Listo...
Si ejecutamos el UserForm, y presionamos el botón de comando, veremos como los nombres de los botones toman los valores de cada celda de la hoja de cálculo...

Igualmente podríamos haber asociado la carga de datos a un evento _Initialize del formulario:

Private Sub UserForm_Initialize()
For c = 1 To Range("lista_nombres").Count
     a = "opcion_" & c
     Me.Controls(a).Caption = Range("lista_nombres").Item(c).Value
Next c
End Sub

Seguir leyendo...

Autoformato de un rango.

Veremos hoy un pequeño truco para aquellos momentos en que queremos formatear nuestros rangos de celdas, a modo de tablas, pero sin añadirle todas las características asociadas a esta gran funcionalidad (autofiltro, fila totales, autorelleno, notación especial, etc.).

En definitiva, queremos seguir trabajando sobre nuestro rango como lo que es, pero también necesitamos aplicarle un formato similar al de las tablas (filas alternas, encabezado de otro color, etc.).

Para este fin emplearemos una herramienta escondida en las versiones posteriores a 2003.. pero que sigue estando hábil a través de los métodos abreviados, o buscando el icono de acceso directo y extrayéndolo en la barra de acceso rápido.
Buscamos en definitiva el Autoformato.

Supongamos este rango de trabajo que deseamos formatear:

La forma más simple de aplicar el formato de tabla (pero manteniendo su status de rango) es llamar a la herramienta Autoformato a través del método abreviado, presionando: Alt+F+A.
Lo que abrirá una ventana diálogo donde poder seleccionar un formato de 'tabla' predefinido:

Además, tenemos la opción (botón en la parte inferior izquierda) para indicar qué características del formato deseamos incorporar... por si hubiera alguna parte del formato ofrecido que no nos conviniera.

El resultado, a modo de ejemplo, tras seleccionar el estilo predefinido: 'Contabilidad 4' sería:

Otra forma de acceder a esta herramienta es incorporando el botón correspondiente (Autoformato) a nuestra barra de acceso rápido...

Seguir leyendo...

Abrir un Libro de Excel en varias ventanas simultáneas.

Recientemente me pasó algo curioso, y me pareció interesante compartirlo.
Un cliente me envío unos ficheros de trabajo con la particularidad que al abrirlos, automáticamente se habilitaban dos ventanas del mismo libro abierto!!.
Lo primero que pensé fue: 'mi Excel se ha vuelto loco' :D
Lo segundo fue, algo he desconfigurado entre las opciones de Excel...
Finalmente, investigando un poco, descubrí que había ocurrido, y sobre todo, cuál es la forma de evitarlo; ya que resulta algo pesado.. ya que además, tenía necesidad de realizar ciertos procedimientos con macros, y abrir estos libros de esta forma, me llevaba a un error en mis rutinas.

En primer lugar, ¿qué nos lleva a este extraño comportamiento?.
Todos en alguna ocasión hemos activado la opción de Nueva Ventana de la ficha Vista > grupo Ventana > botón Nueva Ventana, y así poder visualizar dos lugares del mismo libro simultáneamente (para comparar información, etc.).
En especial en versiones 2010 y anteriores.
En la parte superior del libro, aparecerá el nombre del fichero completo, con dos puntos a continuación y un indicador numérico de en qué ventana de todas las abiertas del libro de trabajo estamos: (nombre_libro.xlsx:2)

Si fuera este el caso, y mientras estemos bajo estas condiciones (varias ventanas abiertas), decidimos guardar nuestro libro de trabajo, será entonces cuando en ocasiones futuras al abrir este Libro se abra simultáneamente el mismo número de de ventanas que hubiera en el momento de la grabación!!!.

Solución.
Abrir el Libro,
Cerrar las ventanas excedentes, dejando solo una abierta,
Guardar la situación.

Listo. Las siguientes ocasiones que abramos este Libro, únicamente se abrirá una ventana.

Seguir leyendo...

Ocultar elementos sin datos en una Segmentación de datos.

Aprenderemos hoy un truco para ocultar, al usar las Segmentaciones de datos, aquellos elementos sin datos.

El sentido de este trabajo es NO visualizar en nuestra segmentación ciertos elementos, que aún existiendo en el origen, no deseamos ver.

Partiremos de una Tabla origen, de la cual al utilizar un cuadro de Segmentación de datos sobre el camp 'Cód', NO queremos ver ni el elemento/código 'x002' no el 'x003':

La idea es generar un campo auxiliar calculado con la siguiente fórmula, que no discrimina precisamente aquellos elementos que no deseamos ver:
=SI(O([@cod]="x003";[@cod]="x002");"";"Ok")

A continuación insertaremos dos cuadros de Segmentación de datos para los campos 'cod' y 'CalcField':

haz clic en la imagen

Puesto que es el primer cuadro, asociado al campo 'cod', donde no deseamos ver los elementos 'x002' y 'x003', será en éste donde Configuraremos dicha Segmentación.
Concrétamente marcaremos la opción: Ocultar los elementos que no contienen datos

Ahora es suficiente aplicar un filtro sobre la Segmentación de nuestro campo calculado y listo, dejaremos de visualizar u ocultos los elementos no deseados...

Notemos que sin esta configuración, dichos elementos aparecerían con un color degradado...

Seguir leyendo...

Power Query: Agrupar por.

Días atrás un lector planteaba la siguiente cuestión:

...Necesito calcular el tiempo que una persona ha trabajado a lo largo de su vida en diferentes trabajos a los efectos de calcular si llega a la cantidad de años requeridos por la normativa uruguaya para jubilarse o retirarse de sus actividades laborales. Hasta ahí no habría problemas porque colocando en dos columnas la fecha de inicio y finalización de cada tarea y aplicando la función de sumar fechas, obtuve de forma individual cada periodo y la suma de todos al final. El problema es que hay personas que trabajaron simultáneamente en dos trabajos y por periodos diferentes de tiempo, y la normativa indica que los periodos superpuestos de tiempo no se suman, es decir, si una persona trabajo en un empleo del 1/1/14 al 31/12/14 y en el otro del 1/7/14 al 30/6/15, Excel me dice que trabajo en cada empleo por el periodo de un año, por lo tanto en la suma final me dice que trabajo 2 años, pero en realidad 6 meses se superponen (del 1/7/14 al 31/12/14) y a los efectos de contabilizar los años trabajados, ese periodo no se contabiliza, por lo que no debería ser sumado.

En el post de hoy daremos una solución empleando Power Query, y en particular una de sus funcionalidades más potentes: Agrupar por.

La idea, para dar respuesta al lector, consistirá en llegar para cada trabajador a la fecha de inicio más baja de entre todos sus trabajos, y de forma similar a la fecha más alta de entre todas las fechas de baja de entre todos sus trabajos...
De esta forma evitaremos duplicidades en el conteo de días, tal como exponía el lector.

Por ejemplo, partamos del siguiente listado de empleados:

Si nos centramos en uno de los trabajadores (empleado1) vemos que ha desarrollado su trabajo en dos empresas distintas, y en tiempos diferentes... si bien, existe una superposición de periodos en los que ha trabajado al mismo tiempo en esas dos empresas:
empleado1 empresa1 01/01/2015 31/12/2015
empleado1 empresa2 01/07/2015 31/01/2016

en concreto desde el 01/07/2015 al 31/12/2015, i.e., seis meses que no se deben contabilizar de manera doble!!.

Una posible solución la encontramos con Power Query, para ello, seleccionamos nuestra tabla con el listado de trabajadores (llamada 'TblOrigen') y desde la ficha Power Query > grupo Datos de Excel > botón Desde Tabla, accederemos al editor de consultas de PQ.

Ya dentro del Editor de consultas, desde la Ficha Inicio > grupo Transformar > botón Agrupar por configuraremos, en la ventana diálogo correspondiente, los criterios de la agrupación:

haz clic en la imagen

Como se observa hemos indicado cuál es nuestro criterio de agrupación de registros: 'cuando el elemento del campo empleados coincida'; además hemos aplicado un cálculo sobre los campos tipo Fecha de Inicio y Fin (mínimo y máximo respectivamente).
Tras Aceptar este sería el resultado... un registro único para cada empleado, y la fecha mínima y máxima de entre todos sus trabajos.

Aunque ya podríamos trabajar con esa información, añadiremos una Nueva columna en nuestra tabla, empleando la herramienta 'Agregar columna personalizada' desde la ficha Agregar columna > grupo General:

Notemos el sencillo cálculo para llegar al dato requerido... simplemente restaremos la Fecha más alta de las fechas de baja (llamada MaxFin) a la Fecha inferior de entre las fechas de alta (llamada MinInicio).

Al Aceptar, y Cerrar y Cargar el complemento, obtenemos la siguiente tabla en una de nuestras hojas de Excel:

En esta tabla comprobamos cómo el cálculo ahora es el correcto para todos los casos, sin duplicidades...

OJO!!, esta forma de trabajar únicamente sería válida para casos de continuidad en los trabajos, esto es, que no haya intervalos de tiempo sin empleo.

Seguir leyendo...