Tengo un libro de hojas, una para cada año, y necesito obtener el promedio de todos los meses que transcurrieron en el año de esa hoja. Las hojas están nombradas por año y nombré la fórmula: MONTHS.
=LET(year,VALUE(MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,31)),IF(YEAR(TODAY())=year,MONTH(TODAY())-1,IF(year>YEAR(TODAY()),0,12)))
Y úsalo para obtener un promedio:
Funciona la mayor parte del tiempo, pero cuando hago algunas cosas en otras celdas u hojas, devuelve un#CALCerror que indica que no se admiten matrices vacías. Cuando vuelvo a calcular la hoja se arregla sola, pero es muy molesto tener que hacerlo todo el tiempo. ¿Hay alguna manera de evitar que esto suceda?
Respuesta1
Mi opinión personal basada en años de uso de APL es que la compatibilidad con matrices vacías es esencial. No admitir matrices vacías es un error por parte de MicroSoft.
Respuesta2
No TAKE()lo tengo disponible y por eso no puedo afirmar definitivamente "Este es tu problema". Abordaré dos aspectos: el de que su MONTHSrango con nombre probablemente tenga un alcance en el LIBRO DE TRABAJO, pero que deba tener un alcance en la HOJA para que pueda especificar una celda particular en cada hoja diferente, y que TAKE()posiblemente el problema se deba a dos problemas potenciales. el de haber ,,especificado el parámetro de filas y lo primero que se aborda a continuación, de MONTHSdevolver a veces "0", por lo que le indica TAKE()que no devuelva ninguna columna, por lo que una matriz vacía.
El hecho de que menciones este problema en la situación de trabajar en otras hojas y luego regresar tiende hacia la primera categoría, el alcance de MONTHSy, por lo tanto, CELL()no especifica ninguna celda en particular. Pero el hecho de que especificar 0columnas aparentemente genere una matriz vacía y que le digan que las matrices vacías no son compatibles sugiere que ese es el problema. Sin embargo, en ese caso no debería resolverse de forma tan sencilla. "No debería", por supuesto, no es equivalente a "no lo hará". Entonces la zona gris.
Pero lo siguiente le brindará tres cosas importantes para verificar fácilmente y, para hacerlo más fácil, probé el material del alcance Named Range y así es. (También probé la forma estándar de obligar a Excel a no devolver nunca el error descrito y no resuelve las cosas). TAKE()Sin embargo, no puedo decir cómo podría afectar a partir de las pruebas. Sin embargo, verás cómo probar fácilmente esas dos cosas. Mi apuesta se centra en el alcance de MONTHSla mejor adaptación a los problemas descritos. Entonces viene primero:
Su fórmula tiene tres resultados posibles, uno de los cuales es 0.
Hasta ahora, todo bien. Sin embargo, en su TAKE()función, utiliza sus resultados para especificar cuántas columnas tomar para enviar a la AVERAGE()función.
Pero al formar la matriz cuando la MONTHSfórmula devuelve 0, le está indicando TAKE()que tome 0columnas en la fila en la que se encuentra. 0columnas significa una matriz vacía.
Para que esto funcione para usted, necesita un enfoque más complejo. Por ejemplo:
Utilice un IF()para probar MONTHSel resultado. Si es así 0, devuelve un resultado de $0,00 en la celda O5. Si no es el 0resultado, utilice la AVERAGE()fórmula tal como está.
O
Devuelve a 1en lugar de a 0cuando el yearcálculo es en el futuro. Uno pensaría que toda la fila de esa hoja estaría vacía, por lo que tomaría un promedio de una celda con un valor cero y devolvería cero. De lo contrario, podría discernirse alguna lógica similar que se aplicaría.
Otra idea, que no está directamente relacionada y que no causa ningún problema en este momento, sería que escribir CELL("filename")mientras lo hace obliga a Excel a recalcular cada instancia de su uso en TODAS las hojas, no solo en esta, cada vez que Excel recalcula. Y al venir de trabajar en otra hoja tiene el problema que se describe en parte de lo que das en la pregunta: Cuando en la otra hoja, y Excel recalcula una de las otras hojas, usa la hoja en la que estás para dar una devolución. CELL()y no la hoja en la que se está utilizando, excepto aquella en la que te encuentras. Entonces todos los demás obtienen resultados desafortunados y Excel parece darse cuenta de eso y devolver un error en todos ellos. Como menciono a continuación, esto solo es irritante para usted, o vergonzoso si un jefe se queja de ello, si las hojas son callejones sin salida, sus resultados no son celdas precedentes para otras hojas, como hojas de suma a lo largo de los años. Pero si lo son, están alimentando un error en esos usos directos, lo que hace que devuelvan errores...
Entonces, sobre eso: esto causa un problema, no al editar la hoja actual, sino al navegar para realizar básicamente cualquier edición en una hoja diferente. Cuando lo haga, las otras hojas de trabajo darán un #VALUE!error en sus celdas que usan MONTHS. Podría decirse que no es un gran problema, siempre y cuando cada hoja sea autónoma y no alimente otras hojas. Iría a la hoja para trabajar un poco y se enfadaría al ver el efecto del error (seguramente una situación de "matriz vacía", aunque no la tengo TAKE()y por lo tanto no puedo probar el efecto exacto... podría ser que devolviera un serie de errores para tomar el promedio y, por lo tanto, AVERAGE()no arrojaría el #CALC!error sino su propio #VALUE!error. En cualquier caso, sería irritante para usted y molesto para los jefes, pero en su primera entrada en la hoja, lo haría. resolver.
Puede solucionar ese problema creando un MONTHSalcance con HOJA en lugar de alcance de LIBRO DE TRABAJO. Y especificando una celda (cualquier celda, pero ¿por qué no la celda A1?) para cada una de las dos CELL()funciones que contiene. Una vez que hagas esto, ese problema desaparecerá.
Por supuesto, significa establecer el Rango con nombre para cada hoja existente, pero si uno tiene una plantilla, o agrega nuevos años copiando una hoja actual y eliminando datos, el Rango con nombre, con alcance de HOJA, se creará cada vez que se copie y renombrado. Así que no será un problema para el futuro. Y elimine el MONTHSrango con nombre del LIBRO DE TRABAJO después de hacerlo.
Si TAKE()recibir el parámetro ,,so no rowsy, a veces, recibir un 0parámetro para las columnas no es la fuente de la dificultad (no doy muchas posibilidades, pero podría ser que MS haya cambiado todo su enfoque filosófico allí: no parecen hacerlo lo que hicieron INDEX()más), entonces probablemente el CELLS()problema sea el uso.
Hasta donde llega, todo lo que puedo encontrar actualmente sobre la función es solo su publicidad regurgitada, o bien regurgitada a través de agradables ejemplos, texto y video, pero sin que NADIE investigue los problemas de uso de la vida real, no estoy hablando de habiéndolo probado para su uso. Estoy observando que el enfoque de MS en los últimos años ha sido no permitir el ,,) INDEX()uso antiguo, sino que, cuando dicen que el parámetro de filas es obligatorio, ni siquiera se refieren a su antiguo "deben especificarse filas o columnas", sino en lugar de eso, independientemente de si proporcionará un parámetro de columnas, DEBE proporcionar un parámetro de filas. Además, al esperar que interpreten ,,MONTHS)que usted significa "ESA fila, tantas columnas", uno espera que estén haciendo una intersección implícita que en realidad podría no ser así, ya que se han estado alejando DEDICADAMENTE de ella, en la práctica y como concepto.
Entonces los problemas se ven así:
Necesidad de utilizar
MONTHSrangos con nombre con ámbito de HOJA en comparación con la versión (presumiblemente) con ámbito de LIBRO DE TRABAJO.TAKE()siendo el problema no manejar el "0" devuelto a veces porMONTHS.TAKE()siendo el problema no manejar la ausencia de un parámetro de filas.TAKE()siendo el problema en AMBOS 2 y 3 anteriores problemas.
Editar:Después de toda esa investigación y de no encontrar nada útil, publiqué esto y luego hice clic para acceder a una página de búsqueda más... y encontré un sitio web que tiene un ejemplo que utiliza la ,,MONTHS)parte que menciono a menudo arriba y muestra TAKE()éxito ya que adopta el enfoque de ignorar cualquier cosa sobre filas y así devolver todas las filas, como lo INDEX()harían los usos simples de parámetros.
Entonces ESE aspecto no es un problema. Lo siento, he editado demasiado la respuesta básica para eliminar esa parte donde se encuentra sin finalmente reescribirla por completo y ya es demasiado tarde para eso ahora que mi cama llama. Pero ese uso NO es el problema ya que su ejemplo muestra un retorno adecuado para ese uso. Es en:
https://www.get-digital-help.com/how-to-use-the-take-function/
Parte 4. en el diseño, aproximadamente entre el 20 y el 25 % del recorrido de la página.