Power Query: Trabajando con Fechas

Un problema con que topé recientemente fue tratar con fechas almacenadas como texto, con formato mmmyyyy en español, e intentar convertirlas con Power Query en fechas legibles.

A priori muy simple si tuviéramos las fechas escritas en inglés y no en español...

Veamos nuestra Tabla de trabajo, con tres campo: Mes, Concepto e Importe (aunque nos centraremos en las Fechas):

Iniciamos la carga en Power Query desde una tabla:
Ficha Datos > grupo Obtener y Transformar > Desde una tabla

Esto nos lleva al editor de consultas y comenzaríamos con el primer intento 'natural', que sería cambiar el tipo de datos de la columna de 'Mes', de tipo Texto a Fecha (de 'Text' a 'Date').
Por ejemplo desde la barra de formulación...
donde pone:
= Table.TransformColumnTypes(Origen,{{"Mes", type text}, {"Concepto", type text}, {"Importe", Int64.Type}})

sobreescribimos:
= Table.TransformColumnTypes(Origen,{{"Mes", type date}, {"Concepto", type text}, {"Importe", Int64.Type}})

El problema es que no ha reconocido los meses en español:
ene
abr
ago
dic

si hubiera estado escrito en inglés ya hubiéramos terminado. Como no es el caso, tendremos que trabajar un poco.

La idea es separar el mes+año en dos columnas:
Mes.1:= solo contendrá los tres caracteres del mes
Mes.2:= solo los caracteres del año.

Para esto seleccionaremos, con los datos originales cargados, la columna de Mes, y dentro del Editor de consulta iremos a:
Ficha Transformar > grupo Columna de Texto > botón Dividir columna > Por número de caracteres

En la ventana de configuración para dividir la columna por número de caracteres, indicaremos que son tres el número de caracteres y que dividimos una vez, lo más a la izquierda posible.

El resultado es que ahora tenemos nuestro campo 'Mes' en dos partes como queríamos:
Mes.1:= solo contendrá los tres caracteres del mes
Mes.2:= solo los caracteres del año.

Ahora trabajaremos sobre el campo Mes.1 que contiene los meses en texto en español...
Tenemos que cambiarlos por sus equivalentes en inglés (para que Power Query pueda reconocerlos). Para esto crearemos una columna personalizada.
Navegamos dentro del editor de consultas a
Ficha Agregar columna > grupo General > botón Columna Personalizada
En la ventana añadiremos la siguiente fórmula:
= Table.AddColumn(#"Tipo cambiado1", "Month", each if [Mes.1] = "ene" then "jan" else if [Mes.1] = "abr" then "apr" else if [Mes.1] = "ago" then "aug" else if [Mes.1] = "dic" then "dec" else [Mes.1])

Esta nueva columna personalizada evalúa el valor del campo Mes.1 y en los casos de nombre de meses discordantes español-inglés (ene-jan, abr-apr, ago-aug y dic-dec) realizamos el cambio, para el resto nos vale los meses del campo Mes.1

Tenemos los meses en inglés en el nuevo campo 'Month' y tenemos los años en el campo 'Mes2'...
solo tenemos que combinar y unir ambos campos para reconstruir nuestras fechas.
Seleccionamos los dos campos y hacemos clic derecho, buscamos la opción de Combinar columnas, en la ventana diálogo marcaremos sin Separador y escribiremos el nuevo nombre

Y llegamos al final transformando o cambiando, ahora sí, el tipo de dato de nuestro nuevo campo de Texto a Fecha.. y lo tenemos!!

Solucionado.

Seguir leyendo...

Repartir con Solver Evolutionary

Conocemos la existencia de los tres métodos de cálculo que Solver nos ofrece:
1-GRG Nonlinear
2-Simplex LP
3-Evolutionary

Los dos primeros son los más frecuentes, y los más ágiles...
pero hoy nos detendremos del método Evolutionary de Solver para Excel.

una gran desventaja es el tiempo que requiere para su resolución, ya que su algoritmo consiste en una búsqueda constante de una mejor solución (que no necesariamente la óptima!!).
Aquí radica la relevancia de este método frente a los otros dos... ya que en ciertos modelos en nuestras hojas, con soluciones múltiples, nos viene muy bien no buscar una única solución óptima, válida en cualquier caso, si no que nos basta la mejor solución posible que se encuentre.
Y en esto consiste nuestro ejercicio de hoy.

Tenemos un listado de once partidas que tenemos que repartir lo más equitativamente entre dos personas de acuerdo al importe de cada partida.

Como siempre en Solver la construcción del modelo en la hoja de cálculo lo es todo...
Debemos habituarnos a construir condiciones sobre la hoja que de otra forma serían complicadas en lal configuración del propio Solver.
En mi modelo he añadido algunas que he considerado pueden ayudar a su resolución (celdas azules).
1-celdas F3:E13 con la fórmula:
=Y(SUMA(D3:E3)=1;O(D3=0;E3=0))*1
2-celda D15:
=1*(SUMA(D3:D13)>1)
3-celda E15:
=1*(SUMA(E3:E13)>1)
4-celda E18:
=1*((D14+E14)=SUMA(C3:C13))
5-celda D19:
=Y(D14/$C$14>40%;D14/$C$14<60%)*1
6-celda E19:
=Y(E14/$C$14>40%;E14/$C$14<60%)*1
7-celda F15:
=ABS(PROMEDIO(D14:E14)-(C14/2))

Es muy posible algunas sean redundantes e innecesarias... pero con Solver mejor pecar de exceso.
Nota: observemos como todas las fórmulas (son pruebas lógicas) están multiplicadas por 1 para tener como resultado 0 o 1 (falso o verdadero), con el fin de poder emplearlos fácilmente en la configuración de Solver.

Por otra parte tenemos como celda objetivo a F14:
=ABS(D14-E14)

Ya podemos entrar en Solver y su configuración.

Las restricciones que hemos incorporado en el modelo se basan en esas 'celdas azules' formuladas descritas anteriormente:
1- D19:E19 que tomen valor 1 (esto es, que sean VERDADERO)
2- D15:E15 que tomen valor 1 (esto es, que sean VERDADERO)
3- F3:F13 que tomen valor 1 (esto es, que sean VERDADERO)
4- E18 que tome valor 1 (esto es, que sea VERDADERO)
5- F15>=0 esto es, que la diferencia sea positiva... redundante por que se calcula sobre el valor absoluto.. pero no la vamos a quitar ;-)
y una más, muy importante, al rango a rellenar D3:E13 se le exige sean número binarios, es decir, se completo con ceros y unos
6- D3:E13 = bin

Hemos marcado como celda objetivo la celda F14 que contiene la fórmula:
=ABS(D14-E14)
al que exigimos que se minimice.

D14 contiene la fórmula
=SUMAPRODUCTO($C$3:$C$13;D3:D13)
y E14 la fórmula
=SUMAPRODUCTO($C$3:$C$13;E3:E13)

Como celdas cambiantes o variables el rango D3:E13 (al que hemos exigido sean binarios).

Y por último, indicamos como método de resolución de Solver el método Evolutionary

Importante es tener las opciones del método de resolución definidas...
En este método especialmente relevante es el tiempo de espera, i.e., el tiempo máximo sin mejora.
Lo dejaremos en 30.

Ojo por que tendremos que jugar con este tiempo hasta lograr el equilibrio

Hemos acabado nuestra configuración de Solver... y podríamos lanzarlo o resolverlo ya.
Pero para 'ayudarlo' un poco, y esto es un truco curioso, vamos a completar en las celdas D3:E13 con unos y ceros al azar, para partir de una solución, solución mejorable por supuesto.

Por ejemplo alternando entre las dos personas 0 y 1:

Por supuesto esto es una solución, en sí misma, al reparto que deseamos conseguir, pero ¿es la mejor que podemos encontrar?.
Lo sabremos enseguida al ejecutar Solver.

Fíjate en la barra de estado, en su parte izquierda.. verás algo curioso.
Nos informa de:
Incumbente: mejor solución hasta ahora
Subproblema: número del intento realizado
Solución de prueba: solución intermedia
Celda objetivo: valor de la celda objetivo

Al finalizar el proceso aparece la típica venta de Solver resumiendo qué ha ocurrido.
En la ventana de resolución de este método Evolutionary suele aparecer un texto algo diferente, en nuestro ejemplo:
Solver no puede mejorar la solución actual. Se cumplen todas las restricciones

Leemos lo esperado... hemos llegado a una solución no mejorable.. al menos en los tiempos máximos de espera que le hemos dado

Al aceptar se plasma el resultado en la hoja de cálculo y comprobamos como el reparto no puede ser más equitativo... ambas personas suman casi la misma cantidad... Objetivo conseguido!!

Seguir leyendo...

VBA: Páginas de impresión Excel

Al hilo de una duda planteada a través del email, donde se preguntaba por la forma de añadir un pie de página o encabezado diferenciado en una página intermedia de impresión, busqué alternativas con el estándar de la hoja de cálculo... sin éxito.
Por lo que opté por desarrollar un proceso donde identificar los rangos contenidos en cada página de impresión... para a continuación ir imprimiéndoles de manera individual y así poder personalizar el encabezado (o pie de página) de la hoja deseada...

la macro es laboriosa por que debemos identificar, basándonos en los saltos de página verticales y horizontales, cada rango de cada página, para luego definir su alto y ancho y terminar redefiniendo el área de impresión y su configuración de página.

Insertamos el siguiente procedimiento 'Sub' en un módulo estándar:

Sub PaginasImpresion()
'www.excelforo.com
'macro destinada para añadir un encabezado o pié de página diferenciado
'en una página intermedia (que no sea la primera)...

'determinamos número de saltos horizontales y verticales
Dim SaltosH As Long, SaltosV As Long
SaltosH = ActiveSheet.HPageBreaks.Count
SaltosV = ActiveSheet.VPageBreaks.Count

'obtenemos el área de impresión
Dim AreaImp As String
AreaImp = ActiveSheet.PageSetup.PrintArea

'número inicial de fila y de columna
'y número final de fila y columna del área de impresión
Dim filaIni As Long, colIni As Long, filaFin As Long, colFin As Long
filaIni = ActiveSheet.Range(AreaImp).Row
colIni = ActiveSheet.Range(AreaImp).Column
filaFin = filaIni + ActiveSheet.Range(AreaImp).Rows.Count
colFin = colIni + ActiveSheet.Range(AreaImp).Columns.Count

'forzamos el sentido de impresión
ActiveSheet.PageSetup.Order = xlOverThenDown 'hacia derecha y luego hacia abajo

'determinamos las columnas de los saltos verticales
Dim arrVertical()
ReDim Preserve arrVertical(0)
arrVertical(0) = colIni
vv = 1
For v = 1 To SaltosV
    salto = ActiveSheet.VPageBreaks(v).Location.Column
    If salto >= colIni And salto <= colFin Then
        ReDim Preserve arrVertical(vv)
        arrVertical(vv) = salto
        vv = vv + 1
    End If
Next v
ReDim Preserve arrVertical(vv)
arrVertical(vv) = colFin

'calculamos ancho de páginas
Dim arrAncho()
a = 1
For x = 1 To vv
    ReDim Preserve arrAncho(a)
    arrAncho(a) = arrVertical(x) - arrVertical(x - 1)
    a = a + 1
Next x

'determinamos las filas de los saltos horizontales
Dim arrHorizontal()
ReDim Preserve arrHorizontal(0)
arrHorizontal(0) = filaIni
hh = 1
For h = 1 To SaltosH
    salto = ActiveSheet.HPageBreaks(h).Location.Row
    If salto >= filaIni And salto <= filaFin Then
        ReDim Preserve arrHorizontal(hh)
        arrHorizontal(hh) = salto
        hh = hh + 1
    End If
Next h
ReDim Preserve arrHorizontal(hh)
arrHorizontal(hh) = filaFin

'calculamos alto de páginas
Dim arrAlto()
a = 1
For x = 1 To hh
    ReDim Preserve arrAlto(a)
    arrAlto(a) = arrHorizontal(x) - arrHorizontal(x - 1)
    a = a + 1
Next x

'calculamos el número de páginas a imprimir
Dim NumPags As Long
NumPags = hh * vv

Dim arrPaginas()
'generamos el área de cada página
pp = 1
'recorremos primera las cols y luego las filas
'de acuerdo al orden de impresión marcado al inicio de la macro
For filas = 0 To hh - 1
    For cols = 0 To vv - 1
        rngArea = Range(Cells(arrHorizontal(filas), arrVertical(cols)), _
            Cells(arrHorizontal(filas), arrVertical(cols)).Offset(arrAlto(filas + 1) - 1, arrAncho(cols + 1) - 1)).Address
        ReDim Preserve arrPaginas(pp)
        arrPaginas(pp) = rngArea
        pp = pp + 1
    Next cols
Next filas


'Terminamos imprimiendo página a página
'para añadir solo el pié de página a la hoja que queramos... por ejemplo a la segunda página
For impresion = 1 To NumPags
    ActiveSheet.PageSetup.PrintArea = arrPaginas(impresion)
    If impresion = 2 Then
        With ActiveSheet.PageSetup
            .CenterHeader = "página 2 personalizada"
        End With
        ActiveSheet.PrintPreview
        'en su caso imprimimos
        'ActiveSheet.Printout
    Else
        With ActiveSheet.PageSetup
            .CenterHeader = ""
        End With
        ActiveSheet.PrintPreview
        'en su caso imprimimos
        'ActiveSheet.Printout
    End If
Next impresion

'terminamos dejando el área de impresión inicial
ActiveSheet.PageSetup.PrintArea=AreaImp
End Sub

Podemos lanzar la macro sobre un rango como el de la imagen:

En el ejemplo vemos nuestra área de impresión (B3:H27) dividida en cuatro páginas que hemos ordenado su impresión 'hacia la derecha y luego hacia abajo': Página 1: área B3:E12 Página 2: área F3:H12 Página 3: área B13:E27 Página 4: área F13:E27 lanzando el proceso podremos comprobar como solo la página dos aparece con el encabezado diferenciado...

Sin duda se pueden sacar más ventajas de lo desarrollado...

Seguir leyendo...

Power Query: Recuperar datos de fichero más reciente

Un caso muy habitual es la necesidad de recuperar la información del fichero más reciente dentro de una carpeta... por ejemplo, si tenemos cada mes u nuevo fichero de precios para nuestros productos, y cada vez añadimos el nuevo fichero con nuevos precios...

Supongamos el ejemplo en que tenemos precios de enero y febrero en dos ficheros distintos (obviamente el de febrero tiene fecha de creación más reciente)

Por otro lado, en otro libro de trabajo, tenemos nuestro listado de ventas

El objetivo a alcanzar es aplicar los precios más recientes (del fichero más actual) a las distintas ventas registradas.

En un primer paso cargaremos la tabla de ventas solo como conexión.
Así desde la ficha Datos > grupo Obtener y transformar > Desde una tabla procedemos.
Recuerda, preferiblemente, cerrar y cargar en ... solo conexión

En el siguiente paso accederemos a los ficheros de precios, desde la ficha Datos > grupo Obtener y transformar > Nueva consulta > Desde un archivo > Desde una carpeta

En la ventana siguiente buscaremos la ubicación de nuestra carpeta de precios... y Aceptaremos

la siguiente ventana es importante... ya que deberemos Editar la consulta.

Dentro del editor de consultas de Power Query ordenaremos en sentido descendente por el campo de fecha deseado (hay tres posibilidades: Fecha creación, Fecha últimos acceso o Fecha última modificación).
Yo optaré por la Fecha de creación / Date created.

A continuación, buscaremos la opción dentro de la ficha Inicio > grupo Reducir filas > Conservar filas > Conservar filas superiores

Se abrirá una ventana donde se nos pregunta cuántas filas queremos conservar de la consulta que estamos viendo... claramente solo queremos mantener una.

Desde nuestra posición o vista actual forzaremos a mostrar el contenido de nuestro fichero 'superviviente', así pues buscaremos el campo 'Content' y lo desplegaremos... esto abrirá una nueva ventana para que indiquemos la situación de nuestros datos dentro de libro de trabajo con los precios (podemos optar por el nombre de la hoja o el nombre de la tabla/rango). Luego de seleccionarlos, aceptaremos.

La conexión ya está finalizada, y vemos los datos esperados...

Finalmente, para conseguir relacionar nuestra tabla de ventas y nuestros precios más recientes, solo tendremos que combinar ambas tablas... lo que podemos hacer en este momento.
Con la consulta de últimos precios aún abierta, podemos navegar a la ficha Inicio > botón Combinar > Combinar consultas par crear una nueva.

En la ventana de 'Combinar' marcamos los campos que servirán de nexo en ambas tablas e igualmente el tipo de combinación deseada, en mi ejemplo: Externa derecha (todas de la segunda, coincidencias de la primera)
Esto es, todos los registros de nuestras ventas y los precios coincidentes de los precios más recientes...

En la nueva consulta creada solo tendremos que Expandir los campos deseados de la tabla de ventas (TblVentas):= (fechas, productos y unidades)

Hemos acabado.. podemos renombrar nuestra consulta y ocultar las columnas no deseadas, añadir columnas calculadas, etc...
Para el ejemplo dejaré todas las columnas y nombres por defecto.
Queda Cargar y cerrar la consulta y mostrarla en la hoja de cálculo.

Lo interesante es que al añadir un nuevo fichero de precios, por ejemplo par marzo 2018, al actualizar nuestra consulta recuperaremos los precios de este último fichero con la fecha de creación más actual...

Seguir leyendo...

VBA: Kill y RmDir para eliminar ficheros y carpetas

Hace algún tiempo publiqué la manera de crear carpetas en nuestro árbol de Windows con la función MkDir (ver aquí)

Hoy veremos la acción opuesta para eliminar carpetas y los ficheros que ellas contengan, para lo que emplearemos las funciones Kill y RmDir.

Tenemos la siguiente carpeta que deseamos eliminar y que contiene ciertos ficheros...

Nuestro código VBA a incluir en un módulo estándar dentro del Editor de VBA es:

Sub Borrado()
Dim strRuta As String
'ruta de la carpeta a borrar..
strRuta = "E:\excelforo\000AAAExcelforo\Carpeta_a_borrar_1"

If Right(strRuta, 1) <> "\" Then strRuta = strRuta & "\"

On Error Resume Next
'paso 1 - eliminamos contenido
'función KILL: para el borrado de una carpeta y los ficheros qeu hubiera en ella
Kill strRuta & "*.*"    ' borra la totalidad de archivos
'Kill strRuta & "*.xl*"    ' borraría la totalidad de archivos solo de Excel

'paso 2 - borramos la carpeta
'función RmDir: para borrar una carpeta ya vacía de ficheros
RmDir strRuta  'eliminamos la carpeta (si está vacía!!)
On Error GoTo 0
End Sub

Un alternativa a estas funciones de VBA es emplear el ScriptSystemObject y su método .DeleteFolder

Nuestro código VBA a incluir en un módulo estándar dentro del Editor de VBA es:

Sub Borrado_Carpeta()
'Elimina la carpeta sin necesidad de eliminar los ficheros en ella contenidos
Dim FSO As Object
Dim strRuta As String

'ruta de la carpeta a borrar..
strRuta = "E:\excelforo\000AAAExcelforo\Carpeta_a_borrar_2"

'quitamos la posible última barra \ de la ruta
If Right(strRuta, 1) = "\" Then MyPath = Left(strRuta, Len(strRuta) - 1)

'llamamos al script de FileSystem
Set FSO = CreateObject("Scripting.FileSystemObject")

'y acabamos borrando la carpeta y todo su contenido
FSO.DeleteFolder strRuta

End Sub

Con ambos métodos conseguimos nuestro objetivo de eliminar desde Excel carpetas y ficheros situados en cualquier parte de nuestro entorno de trabajo...

Seguir leyendo...

VBA: Convertir textos a mayúsculas

Estudiaremos en esta ocasión la forma de convertir en mayúsculas los textos de un rango...
Se trata de dar respuesta a la cuestión planteada por un usuario del blog:

[...] Tengo una duda, cómo se podría hacer para que cuando se copie un rango, por ejemplo 5 celdas con letras en minúsculas, y se pegue en la hoja1, todas las celdas pegadas se transformen en mayúsculas. La alternativa que nos presentas aplica solo si se pega una celda o se escribe en una sola celda. [...]

Partiremos del siguiente rango B2:D6 con textos y números.

Veremos algunas alternativas...
Insertamos el siguiente procedimiento 'Sub' en un módulo estándar:

Sub ConvertirMayusculas()
'a elegir una manera...

'1-forma clásica
'recorremos celda a celda la selección
For Each celda In Selection
    ' y convertimos con la función UCASE todo en mayúscula
    celda.Value = UCase(celda.Value)
Next celda

'2-forma óptima (recomendada)
Dim strSel As String
strSel = Selection.Address
Selection = Evaluate("INDEX(UPPER(" & strSel & "),,)")

'3-forma alternativa sobre rango estático
[B2:D6] = [INDEX(UPPER(B2:D6),,)]
End Sub

OJO!!!, estos métodos convierten todo a valores... por lo que las fórmulas 'desaparecen, convertidas a valores!!!

Para evitar este contratiempo nos obligaremos a emplear el modo bucle (forma clásica), donde con la propiedad .HasFormula discriminaremos las celdas con fórmulas.
Puedes ver un ejemplo similar aquí.

La primera forma emplea la función VBA llamada UCASE que procesa los textos y los convierte en mayúsculas...
Su contraria es LCASE que transforma en minúscula los textos.
El procedimiento se realiza celda a celda...

La segunda y tercera forma (en el fondo es la misma) emplea funciones de la hoja de cálculo:
función INDICE y función MAYUSC para transformar cada celda del rango indicado en la función.
En primer lugar UPPER - MAYUSC convierte en mayúscula los textos, y luego INDEX - INDICE devuelve cada valor a su celda...

Como punto interesante la manera de referirnos a los rangos en el método 3 expuesto
[B2:D6] = [INDEX(UPPER(B2:D6),,)]

que emplea una notación de VBA algo olvidada... con el uso de los corchetes (square brackets).
Estos corchetes en general representan o replican el uso de la función EVALUATE.

Seguir leyendo...

Power Query: Filtro de datos dinámico

Explicaré hoy una utilidad alternativa al filtro avanzado empleando Power Query (Obtener y Transformar), en concreto aplicaremos un filtro múltiple sobre una base de datos fuente, con la ventaja de controlar desde la hoja de cálculo las condiciones del filtro.

Tenemos una base de datos origen con Zona, años e importes (llamada 'TblDatos'), y una segunda tabla con las condiciones a aplicar (llamada 'TblFiltros').

En primer lugar cargaremos y guardaremos la primera tabla 'TblDatos' sobre la hoja de cálculo, por ejemplo en la celda F7.
Así pues desde la ficha Datos > grupo Obtener y transformar > botón Desde una Tabla cargamos en el editor de Consultas la información.
A continuación Cerramos y guardamos en la hoja de cálculo en la celda F7 comentada.

Cargamos solo como conexión la 'TblFiltros'.
Y a continuación repetiremos en tres ocasiones la siguiente acción (una por cada criterio de filtro a aplicar).
Una vez cargada la consulta de filtros desde la ficha Datos > grupo Obtener y transformar > botón Desde una Tabla, haremos clic derecho sobre la consulta en cuestión, marcando la opción de Referencia o Duplicar:

Recuerda repetir la acción tantas veces como filtros distintos desees aplicar.

En la siguiente etapa seleccionaremos uno a uno estas consultas duplicadas e iremos a Rastrear desagrupando datos para cada una de los valores de los filtros.
Esto lo haremos marcando el dato en la tabla a la vista y haciendo clic derecho.

Lo haremos igual para la celda de la Zona, del Año y del Importe, una por cada una de las consultas duplicadas...
Para facilitar su uso posterior renombraremos estas 'consultas' como varZona, varAño y varImporte.

Última fase.
Volvemos a la consulta de la TblDatos (la tabla principal) en nuestro editor de consultas de Power Query... y aplicaremos los filtros particulares que deseemos sobre los campos en cuestión...
NO importa qué filtros sean, ya que en el siguiente paso los personalizaremos empleando nuestras recién creadas variables.

Entonces, desde nuestra consulta aplico:
1- un filtro sobre el campo Zona para que muestre la zona Sur,
2- un filtro sobre el campo Año para que muestre el año 2019
3- y un filtro sobre el campo Importe para que muestre importes menores o iguales a 3000

Si accedemos al Editor avanzado de nuestra consulta veremos:

let
    Origen = Excel.CurrentWorkbook(){[Name="TblDatos"]}[Content],
    #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Zona", type text}, {"Año", Int64.Type}, {"Importe", Int64.Type}}),
    #"Filas filtradas" = Table.SelectRows(#"Tipo cambiado", each ([Zona] = "Sur") and ([Año] = 2019)),
    #"Filas filtradas1" = Table.SelectRows(#"Filas filtradas", each [Importe] <= 3000)
in
    #"Filas filtradas1"

Ahora solo buscaremos los elementos concretos grabados y los sustituiremos por nuestras variables: varZona, varAño y varImporte

let
    Origen = Excel.CurrentWorkbook(){[Name="TblDatos"]}[Content],
    #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Zona", type text}, {"Año", Int64.Type}, {"Importe", Int64.Type}}),
    #"Filas filtradas" = Table.SelectRows(#"Tipo cambiado", each ([Zona] = varZona) and ([Año] = varAño)),
    #"Filas filtradas1" = Table.SelectRows(#"Filas filtradas", each [Importe] <= varImporte)
in
    #"Filas filtradas1"

y presionamos Listo ya veremos el resultado listado de acuerdo a los criterios de nuestra hoja de cálculo... Pero para acabar presionaremos Cerrar y Cargar y visualizaremos el resultado en nuestras celdas.

Comprueba que cambiando los criterios de esa fila 2 de la TblFiltros y tras actualizar los datos, la tabla resultante refrescará con la información correcta. Un dato final... la primera vez que se ejecute la consulta se abrirán tantas hojas como consultas duplicadas hayamos creado... todas ellas se pueden eliminar son problemas (es recomendable para no ralentizar nuestro fichero).

Seguir leyendo...

Suma apoyada en celda combinada

Quizá no te resulte llamativo el nombre de este post, pero es sin duda una de las cuestiones más frecuentes con las que nos enfrentamos.
Trabajar apoyándonos en celdas combinadas.

Veámoslo mas claro en la siguiente imagen:

El objetivo es claro, sumar las cantidades que corresponden al mes buscado, en el ejemplo 'Febrero' (20+21).
Es importante notar que, en estos casos, los datos están distribuidos de manera homogénea, es decir, en pares de dos para cada mes.

Lo que haremos será localizar el inicio de ese rango de dos celdas a sumar, lo haremos con la función INDICE:
INDICE($C$2:$C$25;COINCIDIR(E2;B2:B25;0))
para una vez identificado, generar un rango formulado con DESREF:
DESREF(inicio;;;2;1)
que finalmente será el que sumaremos:
=SUMA(DESREF(INDICE($C$2:$C$25;COINCIDIR(E2;B2:B25;0));;;2;1))

Podemos comprobar que la operación es correcta para cualquier mes...

Otra cuestión similar, igual de frecuente, es querer acumular por un criterio cuando ese 'campo/columna' tiene las celdas combinadas.
Por ejemplo:

El asunto es claro, deseamos acumular los importes correspondientes al código deseado (en el ejemplo 'xxx').
El problema son las celdas combinadas que realmente, a efectos de cálculo, solo están en una de las dos celdas que deberían...
La solución pasa por generar un rango matricial 'virtual' de ceros y unos, debiendo corresponder los unos a las cantidades a acumular.

Nuevamente es fundamental la homogeneidad en la distribución de datos, en el ejemplo cada dos celdas.

Nuestra fórmula matricial (recuerda validarla con Ctrl+Mayus+Enter), sustentada sobre SUMAPRODUCTO es:
=SUMAPRODUCTO(SI($D$2:$D$25=$G$2;1;SI(DESREF($D$2:$D$25;-1;0)=$G$2;1;0));$E$2:$E$25)

que multiplica nuestra matriz de 0 y 1 por los importes de E2:E25.
Esa matriz de 0 y 1 surge de:
SI($D$2:$D$25=$G$2;1;SI(DESREF($D$2:$D$25;-1;0)=$G$2;1;0))

que se puede visualizar a modo de ejemplo en el rango B2:B25 de la imagen...

Se puede comprobar como en ambos ejemplos, una manera de combatir esas celdas combinadas es el uso de la función DESREF, que nos permite generar rangos matriciales o comparativas sobre celdas posicionadas.

Seguir leyendo...