google-spreadsheet - sheets - google spreadsheet substring
Resume la agrupación por año y mes (5)
Tengo una hoja de trabajo Movimientos con dos campos:
mydate | amount
2009-01-01 | 10.00
2009-01-02 | 13.00
2009-02-01 | 11.00
2009-02-05 | 12.00
2009-02-08 | 52.00
Quiero tener en otra hoja de trabajo MonthSum que muestre las sumas de los datos en el monto de la columna agrupados por año + mes de la fecha de la columna:
mydate | amount
2009-01 | 23.00
2009-02 | 75.00
No quiero especificar las celdas donde tiene que sumarse la hoja de cálculo, quiero una fórmula genérica para agrupar mis datos por mes + año. Si estuviera en una base de datos MySQL simplemente lo haría:
SELECT DATE_FORMAT(''%Y-%m'', mydate), SUM(amount)
FROM mytable GROUP BY DATE_FORMAT(''%Y-%m'', mydate)
Necesito la solución para trabajar en Google Spreadsheets .
Creo que las posibles soluciones serían el uso de SUMIF o las tablas dinámicas o la function QUERY de Google Spreadsheets.
Además de la contribución de JMax, déjame presentarte la siguiente fórmula usando la función QUERY ...
=query(index(''Sheet3''!A:B); "select year(Col1)+(month(Col1)+1)/100,sum(Col2) where Col1 is not null group by year(Col1)+(month(Col1)+1)/100 label year(Col1)+(month(Col1)+1)/100 ''mydate'',sum(Col2) ''amount'' ")
Notas: Asumí que los datos originales estaban en la Hoja3 y mi resultado presenta la combinación de año y mes como aaaa.mm en lugar de aaaa-mm He creado una ilustración:
Estaba trabajando con un problema similar. Encontré que esta fórmula funciona bien.
=SUMIF(ARRAYFORMULA(MONTH(''Sheet1''!A:A)), A2, ''Sheet1''!B:B)
En la celda A2 puse el mes que quería totalizar.
Esto es para lo que son las tablas pivote .
He creado un ejemplo usando tus datos.
Primero, agregue una columna para formatear la fecha como su cadena mensual, por ejemplo, "2009-01" con la fórmula:
TEXT(A2, "YYYY-MM")
A continuación, resalte los datos y elija "datos"> "informe de tabla dinámica ..."
Para Filas, seleccione "mes"
Para valores, seleccione "cantidad"
¡TADA! ¡Eso es!
Para obtener una descripción general rápida de las tablas dinámicas, consulte esta publicación de blog de Google .
La respuesta de Matt Burns y Cœur es bastante precisa y me gustaría añadir un comentario (no lo agregué en la sección de comentarios ya que es bastante largo)
En su lugar, puede utilizar esta fórmula: DATE(YEAR(A2);MONTH(A2);1) para agrupar por año y mes.
De esta forma , mantendrá los valores como fechas y, por lo tanto, podrá aplicar cualquier tipo de formato de fecha u orden en su tabla dinámica.
Explicación puede omitir:
En lugar de TEXT(A2, "YYYY-MM") convierte el valor en texto y, por lo tanto, elimina la libertad sobre la manipulación posterior de la fecha.
DATE(YEAR(A2);MONTH(A2);1) eliminar el día y la hora de la fecha, por ejemplo, 2018/05/03 y 2018/05/06 ambos se convierten en 2018/05/01, que sigue siendo una fecha que puede luego se agrupan.
Al aplicar un formato "YYYY-MM" directamente en la tabla dinámica, se obtiene el mismo resultado.
Puedes hacerlo con una fórmula de matriz, pongo la fórmula en A2 y en A1 , pongo el número de mes:
=ARRAYFORMULA(SUMPRODUCT(1*(MONTH(Sheet1!$A$2:$A$6)=A1)*Sheet1!$B$2:$B$6))
Ver el documento en acción en la hoja de cálculo de Google
[EDITAR] Si quieres tanto el año como el mes:
=ARRAYFORMULA(SUMPRODUCT(1*(YEAR(Sheet1!$A$2:$A$8)=A2)*(MONTH(Sheet1!$A$2:$A$8)=B2)*Sheet1!$B$2:$B$8))