martes, 28 de agosto de 2012
viernes, 24 de agosto de 2012
Ejercicio de un BUSCARV anidado dentro de otro BUSCARV en Excel.
Explicaré hoy cómo completar el valor en una celda combinando varios instrumentos que nos ofrece Excel. En concreto veremos como combinamos la Validación de datos con un Nombre definido, trabajando sobre unos datos en forma de Tablas, y todo vinculado con una función BUSCARV aniadada dentro de otra.
El ejemplo sobre el que trabajaremos para desarrollar estas funcionalidades consiste en completar, en función a dos variables una tercera, partiendo de varias tablas de datos con registros de dos campos (un código y una descripción de producto) asociadas cada una a un cliente diferente:
En esta hoja de cálculo, llamada 'Clientes', vemos cuatro Tablas (Tabla1, Tabla2, Tabla3 y Tabla4), aunque podríamos trabajar con muchas mas; y también vemos la Tabla1 (la más importante) que nos relaciona cada Cliente con su Tabla. Esta es la base del ejercicio.
Toda esa información sirve para completar en la Hoja 'Buscador' el siguiente informe:
Se trata, en definitiva, de seleccionar un cliente, y vinculado a este cliente alguno de sus códigos de producto, para una vez seleccionados Cliente y Código, aparezca mediante fórmula el nombre del producto correspondiente.
El ejemplo sobre el que trabajaremos para desarrollar estas funcionalidades consiste en completar, en función a dos variables una tercera, partiendo de varias tablas de datos con registros de dos campos (un código y una descripción de producto) asociadas cada una a un cliente diferente:
En esta hoja de cálculo, llamada 'Clientes', vemos cuatro Tablas (Tabla1, Tabla2, Tabla3 y Tabla4), aunque podríamos trabajar con muchas mas; y también vemos la Tabla1 (la más importante) que nos relaciona cada Cliente con su Tabla. Esta es la base del ejercicio.
Toda esa información sirve para completar en la Hoja 'Buscador' el siguiente informe:
Se trata, en definitiva, de seleccionar un cliente, y vinculado a este cliente alguno de sus códigos de producto, para una vez seleccionados Cliente y Código, aparezca mediante fórmula el nombre del producto correspondiente.
martes, 21 de agosto de 2012
La validación de datos y las duplicidades en Excel.
Algunos días atrás se me planteó una cuestión relacionada con la manera de evitar duplicidades en un campo de un listado, en concreto, se trataba de configurar una validación de datos de tal forma que impidiera introducir sobre un mismo campo de una tabla elementos duplicados.
La cuestión planteada fué:
La cuestión de aplicar múltiples reglas de validación (ver validación de datos personalizada) sobre un rango de celdas ya se ha tratado anteriormente en este mismo blog, pero me resultó curioso aplicarlo en este caso, donde la Validación de datos personalizada es la respuesta ideal a un problema tan frecuente como los Duplicados.
La cuestión planteada fué:
| ...necesito colocar en una misma columna dos reglas de validación, como se hace? Ejemplo, necesito que en la columna A se introduzca un numero con un largo determinado de caracteres (5) y que a su vez no se repita ningún dato.... |
La cuestión de aplicar múltiples reglas de validación (ver validación de datos personalizada) sobre un rango de celdas ya se ha tratado anteriormente en este mismo blog, pero me resultó curioso aplicarlo en este caso, donde la Validación de datos personalizada es la respuesta ideal a un problema tan frecuente como los Duplicados.
viernes, 17 de agosto de 2012
Desviaciones por puntos de una serie de un gráfico de Excel.
Hace bastante tiempo escribí una entrada explicando cómo incorporar a un gráfico de Excel una barras de error con desviación estándar (ver). En la entrada del día explicaré una adaptación de ese gráfico, donde cada punto de la serie tiene una desviación diferente.
Se trata de dar respuesta a un usuario del blog que planteaba la siguiente cuestión:
Vemos que tenemos tres columnas de datos, una primera para medir el tiempo (que será nuestro eje de categorías) una segunda que determina el valor medido para cada hora y una tercera que determinar el error o desviación del valor tomado.
Se trata de dar respuesta a un usuario del blog que planteaba la siguiente cuestión:
| ...hora Estabulación 7:00 38.2±0.07 10:00 38.7±0.07 13:00 38.6±0.08 16:00 38.8±0.09 19:00 38.9±0.07 22:00 38.3±0.07 1:00 38.3±0.13 4:00 38.2±0.07 como puedo hacer una grafica en donde me aparezcan estos datos (error estandar)... |
Vemos que tenemos tres columnas de datos, una primera para medir el tiempo (que será nuestro eje de categorías) una segunda que determina el valor medido para cada hora y una tercera que determinar el error o desviación del valor tomado.
lunes, 13 de agosto de 2012
VBA: Un hipervínculo con macros de Excel.
Recientemente un usuario realizaba una consulta sobre la manera de construir una macro que convirtiera el valor de una celda en un hipervícunlo hacia un fichero localizado en una carpeta de nuestro Pc.
Hace ya un año expliqué un planteamiento similar, pero utilizando sólo las herramientas estándar, la función HIPERVINCULO y la Validación de datos (ver entrada). Sin embargo, hoy aplicaremos un método bastante sencillo en nuestro procedimiento, el objeto Hyperlinks, en concreto con el método Add:
expresión.Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)
| ...Quiero hacer una macro que coja el nombre que tengo escrito en una celda y le haga un hipervínculo a un achivo que habrá en una carpeta "X2" dónde habrá un archivo con el mismo nombre que yo he puesto en la celga (.html); [...] de manera que sólo active el hipervínculo en caso que la celda activa esté en la columna X ó Y... |
Hace ya un año expliqué un planteamiento similar, pero utilizando sólo las herramientas estándar, la función HIPERVINCULO y la Validación de datos (ver entrada). Sin embargo, hoy aplicaremos un método bastante sencillo en nuestro procedimiento, el objeto Hyperlinks, en concreto con el método Add:
expresión.Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)
jueves, 9 de agosto de 2012
Mostrar elementos del Eje Horizontal en un gráfico de Excel.
Hoy toca hablar de gráficos. Al hilo de una entrada anterior del blog, un lector planteaba la posibilidad de mostrar un gráfico en Excel en el que se mostraran en las etiquetas del Eje Horizontal (categorías) sólo los elementos seleccionados, asociados a un gráfico de columnas, con un color diferente al resto, disponiendo además de unas Etiquetas de datos diferenciada.
Conozcamos nuestro Origen de datos para el futuro gráfico. Se trata de un sencillo listado de clientes con su Facturación. Es a partir de esta tabla, donde añadiremos algo de funcionalidad para lograr ver un gráfico de columnas, a dos colores, según los clientes seleccionados por nosotros:
El primer paso será construir una serie de columnas auxiliares necesarias, como siempre para engañar a Excel, donde incluir los datos oportunos para dotar a las series de datos futuras con valores, así como rellenar las Etiquetas del Eje Horizontal.
La primera parte es crear una Columna (rango C2:C11), quizá la más importante, que sirva para seleccionar aquellos clientes que queremos Mostrar en el gráfico. Esto lo haremos con una sencilla Validación de datos tipo lista, con valores Sí/No.
La siguiente columna auxiliar formulada (rango D2:D11) será la que emplearemos para conseguir nuestra Etiqueta del Eje horizontal, en este caso aplicaremos la fórmula:
=SI($C2="Sí";$A2;"")
Otra columna auxiliar (rango E2:E11) nos mostrará la Serie de datos de los clientes seleccionados; en este caso la fórmula es:
=SI($C2="Sí";$B2;"")
Y la última de nuestra columnas auxiliares mostrará la Serie de datos para los clientes no seleccionados. La fórmula para el rango F2:F11 será:
=SI($C2="Sí";"";$B2)
Veamos como queda nuestro origen de datos después de los cambios:
Conozcamos nuestro Origen de datos para el futuro gráfico. Se trata de un sencillo listado de clientes con su Facturación. Es a partir de esta tabla, donde añadiremos algo de funcionalidad para lograr ver un gráfico de columnas, a dos colores, según los clientes seleccionados por nosotros:
El primer paso será construir una serie de columnas auxiliares necesarias, como siempre para engañar a Excel, donde incluir los datos oportunos para dotar a las series de datos futuras con valores, así como rellenar las Etiquetas del Eje Horizontal.
La primera parte es crear una Columna (rango C2:C11), quizá la más importante, que sirva para seleccionar aquellos clientes que queremos Mostrar en el gráfico. Esto lo haremos con una sencilla Validación de datos tipo lista, con valores Sí/No.
La siguiente columna auxiliar formulada (rango D2:D11) será la que emplearemos para conseguir nuestra Etiqueta del Eje horizontal, en este caso aplicaremos la fórmula:
=SI($C2="Sí";$A2;"")
Otra columna auxiliar (rango E2:E11) nos mostrará la Serie de datos de los clientes seleccionados; en este caso la fórmula es:
=SI($C2="Sí";$B2;"")
Y la última de nuestra columnas auxiliares mostrará la Serie de datos para los clientes no seleccionados. La fórmula para el rango F2:F11 será:
=SI($C2="Sí";"";$B2)
Veamos como queda nuestro origen de datos después de los cambios:
lunes, 6 de agosto de 2012
Calcular un aging de deuda con tablas dinámicas de Excel.
Hace bastante tiempo que no trabajamos con tablas dinámicas con nuestro Excel. Aprovechando la consulta realizada por un lector, realizaremos un ejercicio doble con tablas dinámicas, uno primero para Agrupar un campo y un segundo para generar un Campo calculado. Son tareas que ya hemos aprendido, en algún momento, pero creo conveniente recordar cómo trabajar con estas herramientas, que hacen realmente potente nuestra hoja de cálculo.
La cuestión la planteó un lector a través de un comentario:
Comenzamos trabajando sobre una base de datos con información de clientes y ciertas facturas, algunas de las cuales ya están vencidas...
La cuestión la planteó un lector a través de un comentario:
| ...elaborar el análisis de vencimiento y estime la provisión de cuentas incobrables al cierre del ejercicio. Para elaborar el análisis de vencimiento usted debe clasificar los montos de las facturas en cada categoría según su vencimiento según como se indica a continuación: Por vencer: Montos cuyo atraso (días) no supera los 30 días 1 - 30 dias: Montos cuyo atraso (días) es mayor o igual a 1 día y menor que 31 días 31 - 60 dias: Montos cuyo atraso (días) es mayor o igual a 31 días y menor que 61 días 61 - 90 dias: Montos cuyo atraso (días) es mayor o igual a 61 días y menor que 91 días 91 - > dias: Montos cuyo atraso (días) es mayor o igual a 91 días La provisión se estima como un porcentaje del total de la cuenta según cada categoría: Por vencer: 5% 1 - 30 dias: 10% 31 - 60 dias: 20% 61 - 90 dias: 50% 91 - > dias: 100% Nota: Ubique la información en dos hojas que deberá de identificar como: Hoja1: Análisis de vencimiento Hoja2: Provisión... |
Comenzamos trabajando sobre una base de datos con información de clientes y ciertas facturas, algunas de las cuales ya están vencidas...
viernes, 3 de agosto de 2012
VBA: Mostrar/Ocultar Series de un gráfico en Excel.
En el post de hoy veremos un sencillo código de VBA, donde un procedimiento Sub nos mostrará u ocultará las Series de un gráfico, insertado en una hoja de cálculo de Excel.
La cuestión la planteó un lector a través de un comentario:
De manera similar a la que plantea el lector, disponemos un origen de datos y un gráfico de columnas ya generado:
La cuestión la planteó un lector a través de un comentario:
| ......tengo tres series de valores en tres columnas (5 valores por columna, y tengo un grafico de la primera columna vs la segunda y la tercera. En la sexta fila de ls columnas dos y tres tengo una lista desplegable con las opciones SI y NO, y quiero que cuando en la lista se seleccione la opcion SI los valores de la columna correspondientes aparezcan en la grafica en caso contrario que no aparezcan en la grafica... |
De manera similar a la que plantea el lector, disponemos un origen de datos y un gráfico de columnas ya generado:
miércoles, 1 de agosto de 2012
Eliminar filas en blanco en hojas de Excel.
Recordaré hoy una funcionalidad muy práctica en las hojas de cálculo de Excel, que nos permite seleccionar, de golpe, multitud de celdas sin datos.
La cuestión me la planteó un usuario que tenía la necesidad de eliminar muchas filas en blanco que se alternaban, en su base de datos, con otras filas con valores.
Veamos una imagen, recortada, de nuestro origen de datos en Excel:
Sin duda una solución sería seleccionar todo el área de datos, en mi ejemplo A1:F1200, aplicar el Autofiltro (Datos > Ordenar y filtrar > Filtro), para luego seleccionar únicamente los elementos (Vacías), seleccionar el resultado de filas sin datos y Elimiar filas.
Sin embargo, en este post explicaré otra versión, algo más sencilla.
Seleccionamos todo el rango de filas, con datos y sin datos, rango A1:F1200, presionamos F5, o Ctrl+I, o Inicio > Modificar > Buscar y seleccionar > Ir a..., lo que abrirá la ventana de Ir a...:
La cuestión me la planteó un usuario que tenía la necesidad de eliminar muchas filas en blanco que se alternaban, en su base de datos, con otras filas con valores.
Veamos una imagen, recortada, de nuestro origen de datos en Excel:
Sin duda una solución sería seleccionar todo el área de datos, en mi ejemplo A1:F1200, aplicar el Autofiltro (Datos > Ordenar y filtrar > Filtro), para luego seleccionar únicamente los elementos (Vacías), seleccionar el resultado de filas sin datos y Elimiar filas.
Sin embargo, en este post explicaré otra versión, algo más sencilla.
Seleccionamos todo el rango de filas, con datos y sin datos, rango A1:F1200, presionamos F5, o Ctrl+I, o Inicio > Modificar > Buscar y seleccionar > Ir a..., lo que abrirá la ventana de Ir a...:
Suscribirse a:
Entradas (Atom)
