He estado buscando y me cuesta encontrar por qué mis fórmulas no funcionan.
Antes de publicar mis fórmulas, necesito explicar lo que estoy tratando de hacer y es un poco complicado (al menos, esta es la única forma en que se me ocurre hacerlo).
**Fondo**
Necesito crear una hoja de cálculo sobre el presupuesto de mi división (ordenada por categoría). Actualmente, cuando descargo los ingresos/gastos de nuestro sistema de Finanzas, simplemente genera filas de datos (DATOS SIN PROCESAR), ordenados por fecha.
Objetivo 1- Haga que los DATOS SIN PROCESAR exportados se clasifiquen automáticamente en categorías para que luego una tabla muestre claramente cuánto se ha gastado en cada categoría.LOGRADO
Objetivo 2- Haga que los DATOS SIN PROCESAR exportados se clasifiquen en categorías y SÓLO muestren meses específicos según la selección del usuarioATASCADO
EJEMPLO DE EXPORTACIÓN DE DATOS SIN PROCESAR:
TABLA: Datos2015
Date | Description of Expense | Amount | Codes
Jan-15 | Workshop for employee | 100000 | WRKS
Jan-15 | Workshop for employee | 100000 | WRKS
Feb-15 | Sponsors for employee | 200000 | SPON
Feb-15 | Sponsors for employee | 100000 | SPON
Feb-15 | Workshop for employee | 300000 | WRKS
Mar-15 | Sponsors for employee | 100000 | SPON
Si usamos la tabla anterior como ejemplo de DATOS SIN PROCESAR (llamados "Datos2015") que obtengo cuando exporto desde nuestros sistemas financieros (que convertí en una tabla dentro de Excel).
Lo que hice para lograr el Objetivo 1 fue crear una nueva tabla (llamada "Categorías") con las descripciones de las categorías.
TABLA: Categorías
Categories | Codes
Workshop | WRKS
Sponsors | SPON
Luego creó otra tabla (llamada "Presupuesto") como a continuación:
TABLA: Presupuesto
Categories | Codes | Amount
Workshop | WRKS | 500000
Sponsors | SPON | 400000
La fórmula que utilicé para la columna Monto en la tabla Presupuesto es
=SUMIF(Data2015[Codes],[@Codes],Data2015[Amount])
Entonces esta fórmula me ayuda a lograr el Objetivo 1.
En mi intento de lograr el Objetivo 2, intenté lo siguiente.
Creé una tabla para capturar los meses que el usuario quiere ver:
TABLA: Condiciones
Month | X | Background
Jan-15 | x | 1/01/2015
Feb-15 | x | 1/02/2015
Mar-15 | x | 1/03/2015
El usuario pone una "x" en la columna X si desea ver los gastos de ese mes en particular.
Utilicé esta fórmula en la columna Antecedentes:
=IF([@X]="x",TEXT([@Month], "d/mm/yyyy"),"")
Luego quiero filtrar aún más los meses en categorías como antes.
Muy similar a la tabla de Categorías anterior, pero para meses específicos según lo que el usuario quiere ver.
Logré que funcionara, pero solo para 1 línea, no para toda la tabla.
Me gustaría que revisaras mi fórmula y me dijeras qué puedo estar haciendo que sea incorrecto o tal vez incluso mi lógica para lograr el objetivo 2 sea incorrecta. Su visión será muy útil y me disculpo por la situación tan confusa, no se me ocurre una manera más fácil de explicar.
He creado otra hoja de cálculo que imitará la hoja de Data2015 con la siguiente fórmula
=IF(ISNUMBER(SEARCH(Month_View!$D$5, $A5)),Data2015[@Account], IF(ISNUMBER(SEARCH(Month_View!$D$6,$A5)),Data2015[@Account],""))
¡Vista_mes! es dondeTABLA: Condicionesse encuentra y $D$5 es el 15 de enero, $D$6 es el 15 de febrero y así sucesivamente.
Esta fórmula prácticamente completa la tabla si detecta que A5 (columna donde está la fecha) es la misma que laTABLA: Datos2015La columna Fecha (en la misma fila, de ahí la @).
Hasta este punto todo está bien, funciona, siempre y cuando tenga "15 de enero" o "15 de febrero" en la celda A5. Para completar esta celda según las preferencias del usuario, utilicé la siguiente fórmula (que no produce el resultado que quiero):
=IF(Condition[Background]=TEXT(Data2015[@Date],"d/mm/yyyy",Data2015[@Date],"")
Entonces, para mí, esa fórmula está destinada a hacer lo siguiente:
SI la fecha en elDatos2015La tabla coincide con una de las fechas/valores (en el formato "d/mm/aaaa") enCondiciónla columna denominada "Fondo" (que solo se mostrará si el usuario pone una "x" en el mes que desea ver) luego complete esta celda con el valor deDatos2015La columna Fecha.
Si la celda luego se completa con "15 de enero" o "01/01/2015", entonces se completará el resto de la tabla, que luego puedo usar para filtrar aún más por categoría con la fórmula que usé para lograr el Objetivo 1.
Pero la celda no se completa con "15 de enero" o "01/01/2015", sino que se muestra en blanco o #VALOR.
Parece estar haciendo referencia a laCondiciónmesa con @ también que no quiero.
Por confuso que sea... Si puedes entender lo que estoy tratando de lograr, cualquier idea o discusión será de ayuda. Quizás mi cerebro esté demasiado sobrecargado en esta etapa.
Respuesta1
Yo usaría la funcionalidad Tablas integrada en Excel; no se necesitan fórmulas. Seleccione su rango y presione Ctrl-T para comenzar.
Esto activa los filtros en la fila superior: usted o sus usuarios pueden usarlos para filtrar fechas por años y/o meses.
Para la funcionalidad de total, agregue una fila de Totales (usando la cinta Herramientas de tabla/Diseño) y en esa fila puede elegir qué agregación desea para cada columna (Suma, Recuento, etc.). Esto se ajustará automáticamente para seguir su filtrado.
Respuesta2
¡Bien, ya lo he solucionado!
Aquí está la fórmula que utilicé:
=SUM(SUMIF(Data2015BG1[Category],[@Codes],Data2015GB1[Amount]))
*PD: Lo siento si las referencias son diferentes a los ejemplos dados en mi pregunta.
La hoja Data2015BG1 tiene fórmulas que detectan si la columna Activador tiene una "x" o no y presenta los datos necesarios según el mes seleccionado. La fórmula que utilicé es:
=IF(ISNUMBER(MATCH(TEXT(Data2015[@Period],"d/mm/yyyy"),Condition2015A[Background],0))=TRUE,Data2015[@Period],"")
Espero que tenga sentido. Habría publicado imágenes para mostrarles exactamente lo que logré visualmente porque esencialmente lo que hice fue facilitar el uso de cualquiera, ¡pero todavía no tengo suficiente reputación para publicar imágenes! Tal vez la próxima vez.
Los filtros habrían funcionado, pero estoy creando esto para personas que prácticamente no tienen mucha experiencia en Excel, por lo que incluso pedirles que filtren es un poco excesivo (... lo sé, lo sé...)