Sumar omitiendo las columnas ocultas en Excel.

Estamos muy habituados a obtener nuestros acumulados omitiendo filas ocultas de nuestras tablas o rango empleando las funciones AGREGAR o SUBTOTALES... pero y qué ocurre si queremos realizar una operación similar pero sobre columnas ocultas???.
La inquietud viene originada de la pregunta de un compañero de un grupo de LinkedIn al que pertenezco... y me picó el gusanillo de la curiosidad.

Después de buscar y buscar posibles soluciones encontré una solución, apoyándome en un rango auxiliar.
Veamos el planteamiento de partida:

La idea es obtener la suma de los diferentes conceptos pero omitiendo ciertas columnas (meses) que vamos a ocultar...
Por ejemplo, los meses pares (Febrero, Abril, Junio, etc...)

Al no poder emplear las funciones AGREGAR o SUBTOTALES que sólo trabajan sobre filas ocultas (NO columnas), en el rango C1:N1 incluiremos la siguiente fórmula con la función de Excel CELDA, empleando su propiedad 'ancho', la cual nos dirá el ancho de cada columna... y esta es la clave, ya que el ancho de una columna oculta es CERO!:

Esta es nuestra fórmula para cada celda de C1:N1
=SI(CELDA("ancho";C:C)<>0;1;0)
donde simplemente evaluamos, convirtiendo en 0 y 1, si la columna está oculta o no (oculta =0, visible=1).

Sobre este rango auxiliar bastará aplicar, para cada fila, una función SUMAPRODUCTO, multiplicando cada fila por el rango auxiliar, obteniendo entonces el valor sumado únicamente de las columnas visibles:
=SUMAPRODUCTO(C3:N3;$C$1:$N$1)

OJO!! MUY IMPORTANTE, esta función CELDA exige la actualización de la hoja.. suficiente con presionar la tecla de función F9.

Podemos comprobar y comparar la celda con la función SUMA, que nos devuelve el dato acumulado de los 12 meses, frente a nuestro cálculo que sólo retorna el dato 'visible'.

En post siguientes escribiré algo más de las funciones SUBTOTALES y AGREGAR.

Seguir leyendo...

Seis años enseñando Excel...

Seis años que han pasado muy rápido... seis años prestando la mejor formación presencial y elearning(online).

Como en ocasiones anteriores, en este sexto aniversario, mostraré algunos datos estadísticos acumulados hasta la fecha; respecto al blog diré que son ya más 3.000.000 visitas únicas, con más de 4.700.000 páginas vistas, y un 2.390.000 usuarios de todo el mundo (España, México, Colombia, Perú, Chile, Argentina.. y un largo etcétera)...
Son ya más de 670 entradas publicadas, de casos prácticos propuestos por vosotros, solucionados y explicados; más de 6.600 comentarios, y muchísimas horas dedicadas.

Terminar comentando que por segundo año consegutivo (2014 y 2015) he sido premiado por Microsoft con el título MVP (Most Vauable Professional) en Excel... (ver perfil).
Aunque la mayor satisfacción es y ha sido poder contestar personalmente todas las consultas presentadas, bien a través del correo bien a través de los comentarios del blog...

Una satisfacción personal y mi eterno agradecimiento a todos vosotros.

Por todo ello no puedo dejar de seguir ofreciendo estos cursos en modalidad elearning, para permitir el acceso a ellos a cualquier persona desde cualquier parte del mundo...
No lo dudes haz de Excel tu mejor aliado!
Aprende con los mejores y adquiere una buena base: Edición de Cursos de Excel y Macros online con tutor personal de Junio de 2015.
Nunca estudiar fue tan fácil.

Los cursos de Excel y Macros abiertos para este mes de Junio son:

Curso Excel Avanzado

(ver más)

Curso Macros Medio

(ver más)

Curso Macros Iniciación

(ver más)

Curso Excel Nivel Medio

(ver más)

Curso Tablas dinámicas en Excel

(ver más)

Curso preparación MOS Excel 2010 (Examen 77-882)

(ver más)

Curso Excel Financiero

(ver más)


Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) comienzan el día 1 de Junio de 2015; y la matrícula estará abierta hasta el día 10.

Excelforo: con la confianza de siempre....estás a tiempo!!

También formación Excel a empresas. Explota los recursos a tu alcance (ver más).

Informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com.

Seguir leyendo...

La función TEXTO: Luchando con los números como texto...

Cuántas veces nos hemos encontrado con valores numéricos (normalmente exportados desde algún otro sistema de gestión) que nos aparecen como texto??. Y cuántas veces nos han complicado nuestras búsquedas verticales o relaciones entre diferentes orígenes??
Voy a plantear hoy una solución a este problema con la función TEXTO.

Partamos del siguiente planteamiento. Tenemos una serie de valores de los que queremos recuperar la descripción... con el problema que algunos de los códigos 'numéricos' aparecen como texto... de igual forma que los que tenemos en la tabla a completar. Lo vemos en la imagen:

haz clic en la imagen

Es bien sabido que existen algunas formas de convertir valores como texto en numéricos:
1- multiplicando por 1: =A2*1
2- sumando más 0: =A2+0
3- empleando el doble negativo: =--A2
4- utilizar la función VALOR: =VALOR(A2)

todas ellas muy sencillas y prácticas...

En el caso que nos aplica, al existir tanta variedad y coexistir datos numéricos y números como texto, tanto en la tabla principal como en la auxiliar de donde tomaremos los datos, no es tan directo como aplicar alguna de estas técnicas.. por lo que emplearemos la función TEXTO y además una de las cuatro técnicas descritas.
En concreto, la fórmula que buscamos es:
=SI.ERROR(BUSCARV(TEXTO(A2;"0");$E$2:$F$5;2;0);BUSCARV(--A2;$E$2:$F$5;2;0))

El sentido, o la forma de leer, esta fórmula sería que realizamos una búsqueda vertical del valor numérico convertido a texto:
BUSCARV(TEXTO(A2;"0");$E$2:$F$5;2;0)
si no encuentra el valor convertido a texto, puede ser porque en la tabla auxiliar aparezca como número; por tanto, con la función SI.ERROR controlamos el caso y realizamos la búsqueda con el valor como número:
BUSCARV(--A2;$E$2:$F$5;2;0)

Obviamente sólo tenemos dos posibilidades, el valor como texto o como número; y esto queda recogido con la fórmula vista.

Seguir leyendo...

Gráfico para mostrar Grado de Avance en Excel.

Hace unos días solicitaba un lector ayuda para elaborar un gráfico que mostrara el grado de avance de ciertos datos

.generar una gráfica en la que pueda representar el avance de construcción de las cuadrillas en relación a un kilometraje proyectado. Necesito representar como los kilómetros construidos semana a semana van llegando a lo proyectado y que porcentaje de la totalidad de kilómetros representa el avance cada semana hasta llegar al 100% de lo proyectado...

y adjuntaba un gráfico de cómo podría ser el resultado final:

Lo que tenemos de especial en este gráfico es el Multinivel en la etiquetas del eje horizontal, y sobre todo, como siempre, fundamental contar con un estructura de datos en el origen adecuada...
No era el caso de nuestro lector, por lo que partiré mostrando cómo debería ser dicha distribución:

Fijémonos que al necesitar un multinivel de la etiqueta del eje horizontal, necesitaremos dos columnas: 'Semana' y 'Fibra', y obviamente las dos series de datos Proyectado y Avance.

El trabajo para llegar a nuestra meta, como casi siempre, consiste en crear diferentes Series Auxiliares; en este ejercicio crearemos seis series.
Tres de ellas que llamaré:
F12%, F24% y F96%
representaré individualmente los valores de Avance (columna C) de cada tipo de Fibra.
Por otra parte, las tres restantes series auxiliares me servirán para agregar a las etiquetas de datos los valores porcentuales de avance.

haz clic en la imagen

Las fórmulas necesarias aparecen en la imagen.
para F12%: =SI($B12="Fibra 12";$C12;NOD())
para F24%: =SI($B12="Fibra 24";$C12;NOD())
y para F96%: =SI($B12="Fibra 96";$C12;NOD())

Y para las series necesarias para las etiquetas de valores:
=SI($B4="Fibra 12";$C4/$D4;"")
=SI($B4="Fibra 24";$C4/$D4;"")
=SI($B4="Fibra 96";$C4/$D4;"")

El resultado será:

Seguir leyendo...

VBA: Una macro con SQL para evitar dobles búsquedas verticales.

Tiempo atrás explicaba un par de formas para obtener información a partir de un doble id de registro (ver 1 y ver 2).
Las técnicas empleadas, por supuesto, eran válidas, pero tenían el inconveniente que para grandes volúmenes de información se ralentizaba el proceso de cálculo en exceso.

Hoy veremos, como continuación del post anterior, la forma en que utilizando una sentencia SQL dentro de nuestro código VBA de programación obtener de manera mucho más ágil, la misma información

Partimos de nuestra información en dos hojas distintas, en la que código de producto está partido en dos columnas, haciendo cada Id único la combinación de dos campos....

No olvidemos habilitar la Referencia: Microsoft ActiveX Data Objects 6.x Library.
Insertamos el código de la función en un módulo estándar de nuestro proyecto de VBA desde el editor de VB:

Sub QueryDobleRelacion_SQL_Excel()
'Abrimos el editor de Visual Basic y en el menú de Herramientas clic en Referencias
'añadimos: Microsoft ActiveX Data Objects 6.x Library.
Dim sConn As String
Dim sSQL As String
Dim oQt As QueryTable
Dim sh As Worksheet
Dim origen As String

'limpiamos la hoja destino
Sheets("Resultado").Cells.ClearContents
'obtenemos la ruta completa y nombre del fichero de trabajo
'donde se encuentran los datos!!!
origen = Application.ThisWorkbook.FullName
'establecemos la Sentencia de conexión para ficheros de Excel
sConn = "ODBC;DSN=Excel Files;DBQ=" & origen & ";"
'escribimos el código SQL necesario para Relacionar según dos criterios la información de ambas tablas
sSQL = "SELECT [Ppal$].[Num Id], [Ppal$].[Producto], [Precios$].[Precio]" & _
"FROM [Ppal$] LEFT JOIN [Precios$] ON " & _
"([Ppal$].[Producto] = [Precios$].[Producto]) AND ([Ppal$].[Num Id] = [Precios$].[Num Id]);"

'Generamos la Tabla resultante en la hoja final 'Resultado'
Set sh = Sheets("Resultado")
Set oQt = sh.QueryTables.Add(sConn, sh.Range("A1"), sSQL)
'Refresca la conexión y vuelca los datos...
oQt.Refresh

End Sub

El resultado lo vemos en las hoja 'Resultado':

La clave de la sentencia SQL empleada es la doble relación construida, que requiere la igualdad al mismo tiempo del campo 'Producto' y 'Num Id' para devolver el dato de Precio existente... (instrucción SQL LEFT JOIN.. AND).

Seguir leyendo...

VBA: Consolidando datos con SQL dentro de Excel.

Un caso muy solicitado es conseguir mediante macros la unión de información procedente de diferentes rangos, para obtener en un solo origen todos los datos consolidados...
Hoy emplearemos el lenguaje de SQL para conseguir unir información de diferentes hojas.. en este caso la clave será la instrucción SQL: UNION ALL.
Obviamente conocer algo las estructuras de este lenguaje de programación ayuda mucho a construir nuestras macros en VBA para Excel.

El trabajo consistirá en generar una conexión sobre los diferentes rangos y aplicarle una sentencia de SQL que realice y mantenga una conexión entre los orígenes de datos.
OJO!! una vez lanzada nuestra macro, la conexión quedará hecha y viva, por lo que bastará Actualizar datos para refrescar nuestro resultado consolidado....

Supongamos el siguiente caso: disponemos información de nuestra empresa con datos del 2014 y 2015, cada conjunto de datos en una hoja distinta: 'Datos14' y 'Datos15':

El objetivo es conseguir toda la información en el mismo lugar, en la hoja 'Consolidado'.
Para ello insertamos el código de la función en un módulo estándar de nuestro proyecto de VBA desde el editor de VB.
Sin olvidar previamente habilitar la Referencia: Microsoft ActiveX Data Objects 6.x Library.

Sub QueryUNION_SQL_Excel()
'Abrimos el editor de Visual Basic y en el menú de Herramientas clic en Referencias
'añadimos: Microsoft ActiveX Data Objects 6.x Library.
Dim sConn As String
Dim sSQL As String
Dim oQt As QueryTable
Dim sh As Worksheet
Dim origen As String

'limpiamos la hoja destino
Sheets("Consolidado").Cells.ClearContents
'obtenemos la ruta completa y nombre del fichero de trabajo
'donde se encuentran los datos!!!
origen = Application.ThisWorkbook.FullName
'establecemos la Sentencia de conexión para ficheros de Excel
sConn = "ODBC;DSN=Excel Files;DBQ=" & origen & ";"
'escribimos el código SQL necesario para UNIR la información de ambas tablas
sSQL = "SELECT [Datos14$].[Fechas], [Datos14$].[Comercial], [Datos14$].[Unidades], [Datos14$].[Precio] FROM [Datos14$] [Datos14$]" & _
    " UNION ALL " & _
    "SELECT [Datos15$].[Fechas], [Datos15$].[Comercial], [Datos15$].[Unidades], [Datos15$].[Precio] FROM [Datos15$] [Datos15$]" & _
    " ORDER BY Fechas ASC;"

'Generamos la Tabla resultante en la hoja final 'Consolidado'
Set sh = Sheets("Consolidado")
Set oQt = sh.QueryTables.Add(sConn, sh.Range("A1"), sSQL)
'Refresca la conexión y vuelca los datos...
oQt.Refresh

End Sub

Tras ejecutar una sola vez nuestra macro QueryUNION_SQL_Excel podemos comprobar el resultado:

El resultado se comporta, a efectos de refresco, como cualquier otra Tabla...
Puedes comprobar que añadiendo nueva información en nuestros dos orígenes de información (Hojas Datos14 y/o Datos15) y presionando el botón de Actualizar (Ficha Datos > grupo Conexiones > botón Actualizar todo) los nuevos registros se incorporarán al resultante...
También podemos comprobar la acción realizada visualizando las Conexiones existentes:

haz clic en la imagen

En alguna futura entrada expondré la solución, con SQL, a la doble búsqueda.

Seguir leyendo...

La función TEXTO y los idiomas disponibles.

Dispondremos hoy algo más sobre los idiomas en Excel... en concreto su suso respecto a Fechas combinándolo con la función TEXTO.
Se trata de mostrar en nuestras celdas las fechas de acuerdo al idioma que indiquemos..
Es importante recordar que para mostrar fechas con diferentes formatos empleamos los caracteres:
d para días
m para meses
a para años
Caracteres dobles para mostrar dos dígitos
Caracteres triples para mostrar tres letras (para días y para meses: ddd ó mmm)
Cuatro caracteres para mostrar el año completo (aa o bien aaaa) o el mes completo mmm).
Así, en el ejemplo, he optado por el formato:
dddd, dd mmmm aaaa
para mostrar el día de la semana completo, el día del mes con dos dígitos, el mes completo y el año con cuatro dígitos.

Lo importante para este ejercicio es que incorporamos también un ID para cambiar el idioma mostrado.. que deberemos colocarlo antes del formato entre corchetes y seguid del símbolo dolar y guión medio:
[$-040a]dddd, dd mmmm aaaa

Empleando la función TEXTO, quedaría, calculando sobre la fecha de la celda F3:
=TEXTO($F$3;"[$-040a]dddd, dd mmmm aaaa")
lo que muestra en español la fecha... también valdría en este caso, al tener mi configuración el Español como predeterminada:
=TEXTO($F$3;"dddd, dd mmmm aaaa")
es decir, sin indicar código de idioma.

Otros ejemplos:

haz clic en la imagen

Dejo aquí la lista de los diferentes idiomas:

Seguir leyendo...

Gráfico de Pirámide en Excel o Embudo invertido.

Construiremos hoy un gráfico diferente, de una manera muy sencilla, para representar diferentes episodios secuenciales de un mismo hecho.
En el ejemplo que expondré a continuación se refleja como varían las visitas a una web según el número de repeticiones:

El gráfico muestra cómo va variando la caída de visitas según las repeticiones.. es decir, sobre un número de visitas '1 vez', cuantas han repetido una '2a vez', una tercera, etc...

Partiendo de los datos originales, rango A1:B6, construimos un rango auxiliar con la fórmula más importante de este ejercicio, en C3:C6 introducimos la fórmula:
=(MAX($B$2:$B$6)-B3)/2
Fijémonos que la primera fila la dejamos sin dato.

La finalidad de esta serie 'auxiliar' será crear una segunda serie que separe y centre los datos reales del eje vertical, para dar el efecto pirámide.. de ahí que restemos el valor máximo (el de 'visita 1 vez') del dato correspondiente, y lo dividamos por dos para repartir la diferencia 'ficticiamente' a izquierda y derecha del dato real.
De hecho, el dato auxiliar calculado multiplicado por dos más el dato real, devuelve una constante igual al dato máximo....

La siguiente columna auxiliar la emplearemos para mostrar los valores calculados en las etiquetas de datos de nuestra serie principal.
En E2:E6 aplicamos la fórmula:
=B2/$B$2
OJO con el dato máximo fijado...

Estamos listos para crear el gráfico de barras apiladas previa selección del rango A2:C6:

Importante el siguiente paso.. cambiamos el orden de las series. Para ello marcamos en el gráfico la serie Auxiliar (en la imagen la de color Naranja) y presionamos Seleccionar Datos (por ejemplo con el botón derecho del ratón)... en el cuadro diálogo marcamos la serie y la subimos en la ordenación:

Seguir leyendo...