En este ejemplo, queremos dividir una tabla en subtablas para cada centro comercial.
Vamos a trabajar con el modelo que suelo usar habitualmente, donde tenemos la columna de centro comercial.
Este modelo esta en formato de tabla, y, se llama ventas.
Necesitamos hacer referencia a varias columnas de la tabla ventas, tenemos una hoja con el nombre del centro comercial C.C. Nervión.
Quiere decir que, en esta hoja, he de traerme dicho centro comercial, entonces, he de buscar el nombre de la hoja en la columna centro de la tabla ventas.
Tenemos una función llamada CELDA, que devuelve información sobre el formato, ubicación o el contenido de una celda.
En la celda B2 de la hoja C.C. Nervión, voy a poner la función CELDA, y, abro un paréntesis.
Vemos que nos aparecen sus argumentos.
En este caso, vamos a seleccionar nombrearchivo.
=CELDA("nombrearchivo"
Como argumento referencia ponemos A1.
Cerramos paréntesis y aceptamos.
=CELDA("nombrearchivo";A1)
Tenemos la ruta del archivo, y, si nos fijamos al final, vemos el nombre de la hoja.
Necesitamos quedarnos con el nombre de la hoja, vemos que el carácter antes de que empiece el nombre del centro comercial es el cierre de corchete (]), entonces, necesito la posición de dicho corchete de cierre, para ello, voy a usar la función ENCONTRAR, pero en la función complete, vamos a usar en diferentes ocasiones, la función CELDA, por lo que voy a usar la función LET, para crear una variable, y, almacenar la función CELDA.
Lo haremos en la celda B2, ya tenemos nuestra variable creada que almacenada la función CELDA.
=LET(a;CELDA("nombrearchivo";A1)
Para extraer el centro comercial usamos la función EXTRAE, pues, en la función, ponemos punto y coma (;), y, ponemos la función EXTRAE.
=LET(a;CELDA("nombrearchivo";A1);EXTRAE(
El primer argumento de EXTRAE, es texto, pues es la variable A.
=LET(a;CELDA("nombrearchivo";A1);EXTRAE(a
El siguiente argumento es posición inicial, es decir, a partir de donde debemos de empezar a extraer, que es donde esta el corchete de cierre, pero mas uno, para ello, usamos la función ENCONTRAR, donde buscamos el corchete de cierre en la variable A, y, sumamos 1.
=LET(a;CELDA("nombrearchivo";A1);EXTRAE(a;ENCONTRAR("]";a)+1
El siguiente argumento de la función EXTRAE es numero de caracteres, es decir, cuantos caracteres debemos de extraer, bien, sabiendo la longitud de la cadena, la cual hallamos con la función LARGO, y, sabiendo la longitud de la cadena hasta el corchete de cierre, la cual hallamos con la función LARGO, y, ENCONTRAR, pues, restando ambas cantidades, son los caracteres para extraer.
=LET(a;CELDA("nombrearchivo";A1);EXTRAE(a;ENCONTRAR("]";a)+1;LARGO(a)-ENCONTRAR("]";a))
Cerramos paréntesis, y, aceptamos, y, tenemos el nombre de la hoja.
=LET(a;CELDA("nombrearchivo";A1);EXTRAE(a;ENCONTRAR("]";a)+1;LARGO(a)-ENCONTRAR("]";a)))
Ahora, quiero saber las veces que aparece dicho centro en la tabla ventas, para ello, voy a usar la función CONTAR.SI, donde como primer argumento, que es rango, seleccionamos de la tabla ventas, la columna centro, y, como criterio, seleccionamos la celda B1.
=CONTAR.SI(Ventas[Centro];B1)
Vemos que aparece 22 veces.
También, podría hacerlo con el condicional SI, donde pregunto que si la columna centro de la tabla ventas, es igual al valor de la celda B1, de la hoja C.C. Nervión, en ese caso, que ponga el valor 1, en caso contrario, que ponga el valor 0.
=SI(Ventas[Centro]=B1;1;0)
Tenemos una matriz desbordada con ceros, y, unos.
Ahora, después del signo igual usamos la función SUMA, para obtener el total.
=SUMA(SI(Ventas[Centro]=B1;1;0))
Tenemos el mismo resultado.
También, podríamos hacerlo con la función HALLAR, donde busco el valor de la celda B1, en la columna centro de la tabla ventas.
=HALLAR(B1;Ventas[Centro])
Obtenemos una matriz desbordada con la posición de la primera letra de la celda B1, y, donde no hay coincidencia, obtenemos un error.
Ahora, voy a usar la función ESNUMERO, la cual devolverá VERDADERO si es número, y, FALSO si no lo es.
=ESNUMERO(HALLAR(B1;Ventas[Centro]))
Obtenemos una matriz desbordada con VERDADEROS, y, FALSOS.
Con el condicional SI, pregunto que, si el resultado de ESNUMERO es VERDADERO, que ponga el valor 1, en caso contrario, que ponga el valor 0.
=SI(ESNUMERO(HALLAR(B1;Ventas[Centro]));1;0)
Obtenemos una matriz desbordada con ceros, y, unos.
Usamos la función SUMA para obtener el total.
=SUMA(SI(ESNUMERO(HALLAR(B1;Ventas[Centro]));1;0))
Tenemos el mismo resultado.
Las funciones de búsquedas normales no manejan bien los duplicados, por lo que tenemos que acudir a una función matricial.
En este caso, el centro comercial C.C. Nervión, aparece 22 veces, debemos de identificar la posición en que se encuentra en la columna centro de la tabla ventas, para poder recuperar el registro completo.
Si usamos la función INDICE, debemos de especificar una matriz, y, un numero de fila, pues, es ese numero de fila el que debemos de encontrar.
Si nos fijamos en la tabla ventas, el centro comercial Nervión, aparece en la fila 2, 7, 8, 11, 16 entre otras.
Necesitamos encontrar dichas filas, y, luego ir extrayendo cada valor más pequeño.
Tenemos una función llamada K.ESIMO.MENOR, que nos devuelve el primer, segundo, tercer… valor menor.
Bien, en la celda A6 de la hoja C.C. Nervión, voy a aplica la función FILA a la columna centro de la tabla ventas.
=FILA(Ventas[Centro])
Obtengo una matriz desbordada con los números de filas empezando desde la fila 2.
Ahora, este resultado lo voy a dividir entre la columna centro de la tabla ventas, siempre que el centro sea igual a C.C. Nervión.
=FILA(Ventas[Centro])/(Ventas[Centro]=B1)
Tenemos una matriz desbordada con los números de filas que pertenecen al centro comercial Nervión, y, error donde no hay coincidencia.
Ahora, es cuando debemos de usar la función K.ESIMO.MENOR, pero debemos de darnos cuenta que tenemos errores, por lo que voy a usar la función AGREGAR, y, le indico que omita los errores.
Pues, después del signo igual, ponemos la función AGREGAR, y, abrimos un paréntesis, se abre una ventana con la operación que deseamos realizar, seleccionamos K.ESIMO.MENOR.
Punto y coma, en la siguiente ventana, marcamos omitir valores de error.
Como argumento matriz, dejamos la expresión anterior.
=AGREGAR(15;6;;FILA(Ventas[Centro])/(Ventas[Centro]=B1)
Ahora, debemos de poner el argumento K, es decir, que numero menor queremos que nos devuelva, pero lo queremos hacer de forma matricial, ya hemos visto que el conteo nos devuelve 22, si como argumento K, uso la función SECUENCIA, y, como argumento filas, selecciono la celda con el conteo.
=AGREGAR(15;6;FILA(Ventas[Fecha])/(Ventas[[Centro]:[Centro]]='C.C. Nervion'!B1);SECUENCIA(B2))
Obtenemos una matriz desbordada con los numero de fila de cada centro comercial Nervión, desde el numero menor al número mayor.
Ahora, vamos a usar la función INDICE, para extraer el registro, pero de forma matricial, como argumento array, seleccionamos las columnas A:G, el argumento número de fila, ya lo tenemos, que es la expresión anterior, y, el argumento número de columna, vamos a volver a usar la función SECUENCIA, donde omitimos el argumento filas, y, como argumento columnas contamos los encabezados con la función CONTARA, como sigue:
=INDICE(DATOS!A:G;AGREGAR(15;6;FILA(Ventas[Fecha])/(Ventas[[Centro]:[Centro]]='C.C. Nervion'!B1);SECUENCIA(B2));SECUENCIA(;CONTARA(Ventas[#Encabezados])))
Podemos ver como se ha rellenado toda la matriz con el centro comercial Nervión.
Ahora, copio esta hoja hacia la derecha, y, le pongo como nombre Metromar.
Vemos como se ha rellenado de forma automática.
Hacemos lo mismo para los demás centros, y, ya tenemos nuestro ejemplo terminado.
Kommentare