APILARV en Excel: combinar varias hojas en una lista

Brantwood Garden Centres declaró 63.208,10 € de ventas en octubre. Las tiendas ingresaron 75.773,35 €. Cada pestaña cuadraba con su informe de caja, no falló nada, y la hoja de grupo salió 12.565,25 € corta.

Esa hoja es una sola fórmula. =APILARV(Alderwell:Thurlby!A2:F200) apila nueve hojas de tienda en una lista, y SUMAR.SI.CONJUNTO lee la lista. Es lo más limpio del libro.

Una pila se alinea por posición, nunca por encabezado. La columna F de una pestaña cae bajo la columna F de la siguiente, digan lo que digan los dos títulos, y nada en Excel los compara.

Cuatro fallos abrieron ese hueco, y dos de ellos tiran en sentidos opuestos, así que el total parecía un mes flojo. Una tienda ausente, una pestaña contada dos veces, dos columnas intercambiadas y un rango que para en la fila 200.

Qué necesitas. APILARV y APILARH necesitan Microsoft 365 o Excel 2024. FILTRAR, ORDENAR, UNICOS, LET y las referencias de desbordamiento como =H2# necesitan Microsoft 365 o Excel 2021 y posteriores.

SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO, SUMAPRODUCTO, INDICE, CONTARA, RESIDUO y las referencias 3D funcionan en cualquier versión de este siglo. La sección 11 es para la versión que no tiene ninguna de las nuevas.

Los centros de jardinería son solo el ejemplo. Delegaciones, clínicas, obras, fondos, filiales: una pestaña cada uno, una hoja resumen y el mismo silencio.

01Nueve Pestañas, Una Pila, 12.565,25 € de Menos

La hoja de arriba no es la pila. Es la conciliación que nadie había hecho contra ella: una fila por pestaña, lo que la pila tomó de esa pestaña y lo que suma la pestaña misma.

La columna F es el importe que lleva la pila. La G es el importe que hizo la tienda. Seis tiendas cuadran al céntimo. Tres no, y una de las tres se equivoca a favor del grupo.

En neto, al grupo le faltan 12.565,25 €. Suma los cuatro fallos sin signo y verás que 30.785,25 € de octubre acabaron en un sitio distinto del que les corresponde.

02APILARV Apila por Posición, no por Encabezado

APILARV toma matrices y pone cada una debajo de la anterior. Si le das una referencia 3D, toma el mismo bloque de todas las pestañas que hay entre las dos que nombras, en el orden en que están.

Nada en ella lee un encabezado. Las pestañas de tienda salieron de una plantilla en 2019, así que durante seis años la columna F llevó el dinero en todas. Eso es una costumbre, y una costumbre no es una regla.

Una sola celda dice si dos pestañas siguen de acuerdo:

=SUMAPRODUCTO(--(Alderwell!$A$1:$F$1=Oakmere!A1:F1))

Cuenta los encabezados que coinciden, posición por posición. Seis significa alineado. Cualquier cifra menor es una pestaña que la pila va a desordenar sin decir nada, y en este libro devolvió 4.

03La Tienda Que Apiló Recuentos Como Dinero

Oakmere cambió de TPV en septiembre. El nuevo informe escribe el importe en la columna E y las unidades en la F. Las demás tiendas, y la plantilla de la que salieron todas, lo hacen al revés.

Así que la pila archivó los 12.742,60 € de ventas de Oakmere como un recuento de unidades, y sus 486 unidades como 486,00 €. El total del grupo perdió 12.256,60 € y no ganó nada que pareciera un error.

La pista estaba en una columna que nadie suma. El recuento de unidades de octubre salió 20.324,60. Un recuento de sacos de sustrato con céntimos es una columna intercambiada, y es la comprobación más barata de esta página.

=SUMAPRODUCTO(--(RESIDUO(E2:E1593;1)<>0))

Cuenta las filas cuya cifra de unidades no es un número entero. Devolvió 149: las filas de Oakmere cuyo importe lleva céntimos. Las otras ocho de sus 157 ventas eran euros redondos.

04Cada Pestaña Da 199 Filas, Haya Ventas o No

Una referencia 3D toma el mismo bloque de todas las pestañas, así que A2:F200 son 199 filas tanto si la tienda vendió 199 cosas como 96. Las filas vacías llegan igual, y no llegan vacías.

Una referencia a una celda vacía devuelve 0, no un blanco. La pila guarda 493 filas de ceros, con 00/01/1900 en la columna de fecha, y CONTARA cuenta cada una de ellas como dato.

Por eso se movió el ticket medio. 63.208,10 € entre 1.592 filas son 39,70 €. Entre las 1.099 filas que son ventas, 57,51 €. Las dos son aritmética; solo una responde a la pregunta del informe.

FILTRAR quita el relleno, y la prueba es donde se pierde la tarde: los blancos son ceros, así que <>"" los deja pasar todos.

=LET(s; APILARV(Alderwell:Thurlby!A2:F200);
     FILTRAR(s; INDICE(s;0;1)<>0))

05La Tienda Que Nunca Estuvo en la Pila

Ferncliffe abrió en septiembre. Su pestaña fue a donde Excel pone una hoja nueva cuando la pides al final: un sitio a la derecha de Thurlby. La fórmula dice Alderwell:Thurlby.

Una referencia 3D significa las pestañas que están físicamente entre esas dos, en orden de pestaña. No las tiendas, ni el alfabeto, ni lo que el libro contenga hoy. 7.480,20 € quedaron a una pestaña del tramo, en una hoja que cuadraba sola perfectamente.

Pon dos hojas vacías como topes del tramo. Llámalas Inicio y Fin, coloca una a cada lado de las pestañas de tienda y escribe =APILARV(Inicio:Fin!A2:F200). Cualquier pestaña que arrastres entre ellas entra en la pila.

Ese es todo el truco, y su precio es el de la sección 6: un tramo se lleva todas las pestañas de dentro, incluidas las que no querías.

06La Copia Que se Contó Dos Veces

El encargado de Greenhall quería probar otro diseño, hizo clic derecho en la pestaña y eligió Mover o copiar. Excel deja la copia justo detrás del original, que está dentro del tramo, así que la pila se la llevó.

9.110,00 € contados dos veces. Por sí solo se habría visto: el total del grupo habría salido 9.110,00 € alto y alguien habría preguntado. Junto a Ferncliffe y Oakmere cerró el hueco y convirtió cuatro fallos en un octubre lento.

Dos comprobaciones de una celda lo encuentran. =FILAS(s)-FILAS(UNICOS(s)) devuelve 134, que son las filas que la pila guarda por duplicado. =CONTARA(UNICOS(INDICE(s;0;1))) devuelve 8: siete nombres de tienda y el cero que aportan las filas vacías, para ocho pestañas apiladas.

07El Rango Que Para en la Fila 200

Thurlby es la tienda más grande: 214 ventas en octubre. A2:F200 toma 199 filas de cada pestaña, así que quince de ellas, 1.938,45 €, están en el libro y no en el informe.

Un tramo no puede crecer en filas. No tiene ni idea de cuánto mide cada pestaña, y estirarlo hasta A2:F5000 multiplica por veinticinco las filas vacías sin dar nunca una garantía.

Las tablas crecen solas, y una referencia 3D no admite tablas. =APILARV(tAlderwell;tBexmoor;tFerncliffe) sobre las tablas de cada tienda sigue a cada una según se llena, y nombra todas las pestañas que querías incluir. La décima tienda habrá que añadirla a mano.

Elige qué mitad quieres automática: pestañas nuevas gratis o filas nuevas gratis. Y escribe una comprobación contra la mitad que no elegiste, porque esa es la que va a fallar.

08Los Anchos Distintos Rellenan con #N/D

Si listas las matrices a mano en vez de usar un tramo, los anchos dejan de estar garantizados. APILARV rellena las estrechas con #N/D, así que una pestaña de cinco columnas entre otras de seis recibe una columna de errores y no un desplazamiento.

Ese es el comportamiento amable. Los errores son ruidosos, sobreviven en todos los totales que no los ignoran, y =SI.ERROR(APILARV(...);"") los convierte en blancos una vez que has entendido por qué están ahí.

APILARH hace lo mismo de lado, rellenando con #N/D por abajo las matrices cortas. En los dos casos el relleno te dice que las entradas no coinciden, que es más de lo que hizo nunca la columna intercambiada de la sección 3.

09Cinco Comprobaciones Encima de la Tabla

Cada una de estas es una celda, y todas fallaron en este libro. Ponlas encima de la pila, donde se lee el informe, y no en una pestaña llamada Comprobaciones que nadie abre.

La sexta cuesta una columna en vez de una celda, y es la que habría cazado los cuatro fallos: la conciliación por tienda de la sección 1, total apilado contra total de la pestaña, todos los meses.

10Leer la Pila: SUMAR.SI.CONJUNTO Pide un Rango

Una pila que vive dentro de LET es una matriz en memoria, y SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO no admiten matrices. Admiten rangos. Apúntalos a una y la respuesta es #¡VALOR!, que al menos es una respuesta.

Así que deja que la pila se derrame en una hoja. Si =APILARV(...) está en H2, entonces H2# es un rango de verdad que crece con ella, y =SUMAR.SI.CONJUNTO(M2:M1593;H2:H1593;$A2) suma una tienda de la forma de siempre.

Dentro de LET, usa SUMAPRODUCTO: =SUMAPRODUCTO((INDICE(s;0;1)=$A2)*INDICE(s;0;6)) lee matrices sin quejarse y da el mismo número. BUSCARX y FILTRAR también admiten matrices.

11Cuando Tu Versión no Tiene Nada de Esto

Excel 2019 y anteriores no tienen APILARV, y Anexar de Power Query es más antiguo que todo esto: Datos ▸ Obtener datos ▸ Combinar consultas ▸ Anexar, una consulta por pestaña y una tabla de salida.

Anexar empareja por nombre de columna. Las columnas intercambiadas de Oakmere habrían caído bajo el encabezado correcto, y una columna renombrada habría dado una columna nueva de nulos en lugar de una mezcla silenciosa.

También lee cada pestaña hasta su última fila real, así que no hay relleno vacío ni cola perdida. Lo que no hace es recalcular: alguien tiene que pulsar Actualizar, y una actualización vieja esconde justo el hueco del que trata este artículo.

Cuál usar. Una pila está viva, se lee en una fórmula y necesita Microsoft 365. Power Query es un paso que se actualiza, empareja por encabezado y funciona en cualquier versión desde 2016.

Si las pestañas son en realidad archivos sueltos en una carpeta, ninguna de las dos respuestas sirve: apunta Power Query a la carpeta y el archivo número trece entrará sin que nadie toque una fórmula.

12Diez Trampas

1. Una pila se alinea por posición. Dos pestañas con los mismos encabezados en otro orden se apilan en un disparate sin error.
2. Una referencia 3D es orden de pestaña, no alfabeto. Arrastrar una pestaña cambia lo que abarca la fórmula.
3. Una pestaña añadida al final queda fuera del tramo. No entra en nada y no avisa de nada.
4. Mover o copiar deja la copia dentro del tramo. La copia se cuenta entera.
5. Las celdas vacías llegan como 0. <>"" no las quita; <>0 sí.
6. Una fecha vacía es 00/01/1900. Ordena por ella y el relleno sube a lo alto.
7. El límite de filas es fijo. La pestaña que lo supera pierde la cola en silencio.
8. Los anchos distintos rellenan con #N/D. Ruidoso, y mejor que la alternativa.
9. SUMAR.SI.CONJUNTO rechaza una matriz. Derrama la pila, o súmala con SUMAPRODUCTO.
10. La pila está viva. Borra una pestaña de tienda y el informe entero cambia mientras lo miras.

13Ejercicios

1. Rómpelo a propósito. Copia una pestaña con Mover o copiar, déjala donde Excel la puso y mira cómo el total del grupo sube exactamente esa tienda.
2. Cuenta el relleno. Escribe =FILAS(H2#) y =SUMAPRODUCTO(--(INDICE(H2#;0;1)<>0)) sobre la pila del ejemplo y calcula el ticket medio de las dos maneras.
3. Caza el intercambio. Apunta =SUMAPRODUCTO(--(Alderwell!$A$1:$F$1=Oakmere!A1:F1)) a cada pestaña por turnos y encuentra la que devuelve 4.
4. Pon los topes. Añade las hojas Inicio y Fin, vuelve a apuntar la pila a Inicio:Fin!A2:F200 y arrastra Ferncliffe dentro. Confirma que el total sube 7.480,20 €.
5. Concilia el conjunto. Monta la columna de la sección 1 y explica los 30.785,25 € enteros, no solo los 12.565,25 € que admite el total del grupo.

Qué Llevarte

Nada en este libro estaba roto. Nueve pestañas cuadraban, una fórmula las apiló, SUMAR.SI.CONJUNTO sumó la pila, y cada número del informe era la salida honesta de la fórmula que tenía encima.

Lo que nadie había escrito es que una pila es una suposición: que todas las pestañas tienen las mismas columnas en el mismo orden, que las pestañas de dentro del tramo son las que quieres, y que las filas que lees llegan al fondo de la más larga.

Esas tres suposiciones se prueban con una celda cada una. =SUMAPRODUCTO(--(Alderwell!$A$1:$F$1=Oakmere!A1:F1)) prueba la primera, =FILAS(H2#)-FILAS(UNICOS(H2#)) la segunda, y =CONTARA(Thurlby!A2:A10000) contra el recuento apilado la tercera.

Tres celdas, frente a 12.565,25 € que nadie sabía explicar y 30.785,25 € que nunca estuvieron en el sitio que les tocaba.