¿Cómo copiar fórmulas cuando hay filas vacías entre ellas sin perder la secuencia de la hoja vinculada?

Question

Tengo un archivo excel que tiene dos hojas.

• Resumen
• Datos sin procesar

Mi hoja de resumen tiene datos como este, donde hay un espacio de algunas filas para cada fila de datos siguiente

Ahora el problema es que si tengo una hoja con más de 1000 filas y quiero cambiar algo en ella, tengo que editar manualmente cada referencia de celda, ya que no continúa la referencia de la hoja RawData sino que obtiene el número de celda como referencia de Hoja resumen.

En la imagen de ejemplo adjunta, si copio y pego la fórmula, obtengo los números de celda como 2, 5, 8, 11, etc., dependiendo del espacio entre filas, donde necesito copiar las fórmulas como se muestra en la columna de fórmula manual. como secuencia B2, B3, B4, B5 independientemente del número de fila de la hoja actual. Intenté muchas cosas pero no pude encontrar ninguna solución. ¿Alguna ayuda?

Para evitar confusiones, también creo un ejemplo más realista a partir de mi hoja con una captura de pantalla de la pestaña RawData.

Hoja de datos sin procesar: - Hoja de datos sin procesar

Aquí está la hoja vinculada donde estoy vinculando datos de la hoja RawData Resumen de la ficha del producto

Como puede ver, se muestra el Producto de prueba 1, el Producto de prueba 6 y el Producto de prueba 11 si copia y pega la fórmula, mientras que debería ser el Producto de prueba 1, 2 y 3.

Aquí está el enlace del archivo de Excel de ejemplo.https://we.tl/t-3F3mJra7pe

Answer 1

Permítanme primero describir el motivo del problema.

• EnHoja de resumenen la celda, F3la fórmula =Rawdata!A2devuelve el valor Test Product 1.
• Tan pronto como copieHojas de resumendatos de B3:F7en B8, lecturas de Excel6 filasentre F3:F8y regresa Test Product 7de la celda B8deDatos sin procesarHoja.

Lo cual estaba arruinando todo el ejercicio.

La solución:

• NecesitasColumna auxiliarenDatos sin procesarHoja.

• Reescribir celda B3enResumenHoja con Producto sencillo 1.
• Ingrese esta fórmula en la celda F3enResumenHoja.

=VLOOKUP(B3,RawData!A$2:E$12,2,FALSE)

Entiendes esto.

• Ahora,CopiarRango B3:F7yPegaren el celular B8.

Entiendes esto.

• Reescribir celda B8conProducto sencillo 2, ahora obtienes la secuencia correcta.

• Repite elCopiar pegarcon corregirProductos simplessecuencia, obtienes los resultados deseados.

Puede ajustar las referencias de celda en la Fórmula según sea necesario.

Answer 2

En este tipo de escenarios, creo que una solución más simple pero sólida sería usar la función OFFSET(). Desplazamiento devuelve una referencia a una sola celda o a un rango de celdas. Teniendo en cuenta su escenario, si la primera Fórmula Copiar y Pegar debería hacer referencia a RawData Producto1, y la segunda Copiar y Pegar se referiría al Producto2, sin importar cuántas filas se omitirían en estos Copiar y Pegar, implementaría este DESPLAZAMIENTO de la siguiente manera:

A B               C      D        E       F
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B1);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B6);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B11);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B16);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large

Observe que cuando copia la fórmula "=OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B1);0)", la referencia a "Resumen!$B$1:B1" cambiará automáticamente con la misma comportamiento que NO QUIERES; pero, una vez que esa referencia se utiliza dentro de una fórmula COUNTA(), funcionará y no tendrá que cambiar miles de referencias usted mismo. Al hacer esto, COUNTA() contará cuántas veces aparece un "Producto simple" desde el inicio de la columna y utilizará este desplazamiento interno para referirse al producto respectivo en posición ordinal. Todo ello sin necesidad de crear columnas auxiliares. El resultado será el siguiente:

A B               C      D        E       F
  Simple Product  FALSE  TRUE             TestProduct1
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct2
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct3
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct4
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large