Estoy intentando aplicar ingeniería inversa a una plantilla de presupuesto para poder modificarla. Tiene secciones para diferentes gastos (Hogar, Entretenimiento, Transporte, etc.). Cada sección tiene múltiples gastos (El transporte tiene seguro de auto, gasolina, estacionamiento, etc.). Cada sección es un rango con nombre y cada gasto se suma a una celda en el extremo derecho de la fila. Sin embargo, la plantilla hace cada fila en una sección con la misma función y no puedo entender cómo funciona.
Aquí hay una imagen con la función en la parte superior y el administrador de nombres. Como dije, la función resaltada es exactamente la misma función en todos los totales de fila para 'Vida Diaria'. https://i.stack.imgur.com/mbMij.jpg
Tengo exactamente los mismos grupos de nombres (el administrador de nombres y todos los rangos son idénticos) y el mismo diseño, pero no puedo conseguir que la misma función produzca un error y no se acepte. Lo mejor que he podido hacer es poner SUM(Diario), que suma todas las filas de la sección, no cada fila individualmente.
¿Alguien puede explicar esta función a partir de la imagen =SUM(Daily[@[January]:[December]]), particularmente el símbolo '@', y cómo Excel sabe que JAN (en el encabezado de la columna) significa enero cuando no está en el administrador de nombres?
BONIFICACIÓN: ¿Alguien puede explicar cómo funcionan también las funciones total de sección y total general? https://i.stack.imgur.com/ydMfz.jpg
Respuesta1
Bueno, se preguntan muchas cosas y espero responder a cada una. Esperanza.
Primero y más importante: el diseño de los datos es un formato Table. A Tablees diferente para Excel que el rango de datos de celdas en el que aparecían anteriormente. Es un objeto formal en el que se siguen reglas simples y, a cambio, Excel, no tiene que tomar en cuenta todas las posibles cosas raras que cualquier persona en el mundo podría hacer al configurar las cosas (he visto que las celdas de totales aparecen EN los datos, no debajo). , arriba, o hacia la izquierda o hacia la derecha, y en el mismo rango, aparecen en diferentes filas para diferentes columnas. Se ajusta al esquema del tipo, de una manera débil mental, pero qué diablos hace Excel con eso cuando parece. ¿Extraño y confuso para un humano? ¿O para diez humanos?
En cualquier caso, su tabla se llamaA diario. Por cierto, se puede decir que es una tabla debido a la forma en que se escriben los elementos de la fórmula, aunque solo sea por otra razón: ¿ve los corchetes ("[ ]")? Casi nunca se usan en Tablas y en algún otro lugar (todos los cuales probablemente usan tecnología de Tabla para funcionar). De todos modos, al crear la SUM()fórmula simple, cuando resalta toda la fila, Excel convierte las direcciones de celda que vio mientras resaltaba en referencias de tabla basadas en los títulos de las columnas: de enero a diciembre son los bordes del rango que resalta aquí. Entonces, en lugar de ver una referencia como D20:O20, aparece [enero:diciembre].
Un elemento que queda en esa fórmula: el@. Dado que se trata de una tabla, si no tuviera forma de decirle a Excel que se refiere SÓLO A ESTA FILA, pensaría que está sumando TODAS las filas. En Excel, @le dice a Excel "solo esta fila" y se usa no solo en tablas y en otros lugares, sino en GRAN medida al usar Spillfórmulas. Para ser explícito, le está diciendo a Excel ESTA FILA, por lo que suma solo la fila de Tintorería, sumando los números en todas las columnas entre enero y diciembre.
En muchos sentidos, SUM()se usan como aquí y SUBTOTAL()(se usan en cualquier lugar) son lo mismo, más o menos. Pero SUBTOTAL()tiene una gran diferencia: Excel está dispuesto a ignorar los SUBTOTALES obtenidos al resumir una columna. Pero no ignorará los subtotales creados, SUM()por lo que si están en la misma columna que algunos números sin procesar, obtendrás el doble del total que deberías. SUBTOTAL()también tiene un montón de cosas diferentes que devolverá, como un promedio, que SUM()no puede devolverlas por sí sola.
La persona que escribió la hoja de cálculo claramente adoptó una forma muy estándar y sensata de establecer el total. Obtuvo totales horizontales SUM()para que el total vertical que luego hizo no los ignorara automáticamente y luego los usó SUBTOTAL()para hacer el total vertical que ve en la Tabla.
Ninguna de estas cosas está en rangos con nombre porque están disponibles automáticamente (tan automáticos que a veces es difícil evitarlos...) porque todo es una tabla. Dado que es una tabla, puede utilizar encabezados de columna como referencias. Antes se podía hacer esto todo el tiempo, pero lo quitaron hace unos 17 años y luego lo devolvieron de esta forma... suspiro...
Además, a veces resulta desagradable el hecho de que los encabezados de las columnas son automáticamente texto, por lo que no pueden ser fórmulas. Por tanto, los encabezados dinámicos no son posibles con las tablas.
Una de las razones por las que puede que no parezca una tabla es que no se ven los desagradables íconos de filtrado a la derecha de cada encabezado, generalmente encima de la parte superior del encabezado... Es sencillo hacer que desaparezcan y el autor claramente lo hizo. . Sin ellos, la mayoría NO pensaría en "Tabla" durante bastante tiempo, pero la formación de las referencias a la fórmula es un claro indicio. Otra cosa que oscurece el hecho es que la mayoría de la gente está acostumbrada a tener la apariencia de "banda verde" con las tablas (de cualquier color, pero ese papel solía ser mayoritariamente verde y tengo 845 años, así que...). Sin embargo, eso también se puede formatear cuando no es apropiado o se considera odioso (¿Por qué? ¿Por qué? ¡Hace que las tablas en toda la pantalla sean mucho más legibles!) y en este caso, claramente no pertenece. Se podría usarlo en cada área de datos, pero dado que varían en la cantidad de filas que tiene cada uno, supondría un gran esfuerzo año tras año.
Entonces:
- Es un
Tabley por eso tiene algunas cosas buenas disponibles para ti. - Los datos están en dos secciones, la columna de la izquierda con los encabezados de sección y luego de subsección (filas individuales en su caso). Como "Limpieza en seco" y luego la sección "para completar tal como existe a lo largo del tiempo" bajo los títulos de enero a diciembre.
- Cada fila se suma para el año de su derecha mediante
SUM()funciones. - Cada columna se suma para el mes (y el año en la columna de la derecha) utilizando
SUBTOTAL()funciones. - Las
SUM()funciones funcionan solo en una fila a la vez porque colocan@antes del primer elemento en su referencia de rango ("@January"). - Las secciones INDIVIDUALES ("INGRESOS", "INICIO", etc.) tienen un total de sus bits en cada mes y para el año, utilizando
SUBTOTAL()funciones porque se ignorarán al sumar la columna general. (En contraste conSUM()cuyos resultados de cada sección SE incluirían en la parte inferior, los totales generales darían resultados el doble del valor que deberían tener).
Muy plausible y útilmente diseñado con atención a los detalles importantes. Se podría argumentar que es mejor poner este tipo de cosas en una tabla simple, o incluso en una versión de Tabla... una Tabla simple si se quiere, y luego una tabla dinámica (sí, 845 años, así que una tabla cruzada, incluso tabulaciones cruzadas, todavía me suena normal) para la presentación. Después de todo, las tablas dinámicas, o para ser más exactos, las tablas Power Pivot tienen muchas ventajas (oooooo... segmentaciones...) y capacidades de totalización propias, y así sucesivamente, pero eso es otra cosa. aprender y esto resolvió muy bien las necesidades de alguien.
No estoy seguro de lo que debe hacer, por lo que no puedo ofrecerle ninguna ayuda. Pero si simplemente quieres mejorar de alguna manera el esfuerzo, o hacerlo tuyo de alguna manera, realmente no puedes, está muy bien hecho.A menos que sigas la ruta de la tabla dinámica. Entonces, si eso es todo, ahórrese algunos problemas: esto es muy decente y realmente no tiene mejoras que encontrar. Si es solo que el jefe quiere que se haga algo o, bueno, creo que todos hemos pasado por eso.