ArrayFormula está rompiendo la función getLastRow(). ¿Posibles soluciones?

Asunto:

• Adición indeseable de cadenas vacías en todas las filas disponibles mediante el uso tradicional deARRAYFORMULA(IF(A:A="",...))

Solución:

• Usar ARRAYFORMULAcorrectamente con INDEX/COUNTA(para determinar la última fila que se necesita) garantiza que la fórmula solo se llene hasta la fila necesaria en lugar de un camuflaje.

• INDEX/COUNTA: INDEXdevuelve un valor y una referencia de celda. A2:INDEX(A2:A,COUNTA(A2:A))=> Si COUNTA(...)devuelve 10 => A2:INDEX(A2:A,10)=> A2:A11es la referencia final enviada al número de semana

• Suponiendo que no haya espacios en blanco entre sus datos,

  =ARRAYFORMULA(WEEKNUM(A2:INDEX(A2:A,COUNTA(A2:A)),2))
  

• Otra alternativa es utilizar ARRAY_CONSTRAIN/COUNTA:

  =ARRAY_CONSTRAIN(ARRAYFORMULA(WEEKNUM(A2:A, 2)),COUNTA(A2:A))
  

• El uso de COUNTAsupone que no hay celdas en blanco en el medio. Si hay alguno, es posible que deba agregar un desplazamiento manualmente. Si hay dos celdas en blanco, suma 2 aCOUNTA

  A2:INDEX(A2:A,COUNTA(A2:A)+2)
  

A menos que Google realice optimizaciones incorporadas, INDEX/COUNTAse prefiere a ARRAY_CONSTRAIN.

Actualizar:

Con la aparición de XMATCHfunciones con nombre, esto debería resultar más fácil. Crea una función con nombre como:

LR(rng)(ÚLTIMA FILA):

=INDEX(rng,XMATCH("*",rng,2,-1))

LRA(rng, blanks)(LASTROW AVANZADO):

=INDEX(rng,COUNTA(rng)+blanks)

XMATCHHace la búsqueda a la inversa y en teoría debería ser más rápida. Pero la *búsqueda con comodines no considerará números ni fechas. Entonces, si la última fila es un número o una fecha, debemos usar COUNTA. Luego puedes usarlo así:

=ARRAYFORMULA(WEEKNUM(A2:LRA(A:A,),2))

Si hay como 5 espacios en blanco intermedios, use

=ARRAYFORMULA(WEEKNUM(A2:LRA(A:A,5),2))

script-de-aplicaciones-de-google

Puede ser difícil arreglar esas fórmulas de matriz INDEX/COUNTAmanualmente, así que hice un script. Esto es sólo una prueba de concepto y calidad alfa. Por lo tanto, pruébelo en una copia de su hoja de cálculo en lugar del original. Dicho esto, estoy seguro de que manejará los casos más comunes sin problemas.

/**
 * @see https://stackoverflow.com/a/46884012
 */
function fixArrayFormulas_so46884012() {
  const ss = SpreadsheetApp.getActive()/*.getSheetByName('Sheet1')*/,
    map = new Map([
      [
        // Normalize first part of range
        /* A:F */ String.raw`([a-z]+):([a-z]+)`,
        /* A1:F*/ String.raw`$11:$2`,
      ],
      [
        // Convert any previous index/counta to normal ranges
        /* A1:INDEX(F:F,COUNTA(F:F)) */ String.raw`([a-z]+\d+):INDEX\(([a-z]+)\d*:\w+,COUNTA\(\w+:\w+\)\)`,
        /*A1:F*/ String.raw`$1:$2`,
      ],
      [
        // Convert open ended ranges to  index/counta ranges
        /*A1:F*/ String.raw`([a-z]+\d+:)([a-z]+)`,
        /* A1:INDEX(F:F,COUNTA(F:F)) */ `$1INDEX($2:$2,COUNTA($2:$2))`,
      ],
    ]);
  map.forEach((v, k) =>
    ss
      .createTextFinder(k)
      .matchFormulaText(true)
      .useRegularExpression(true)
      .replaceAllWith(v)
  );
}

Oct 23 '2017 07:10 TheMaster