Fórmula genérica
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Resumen
Para encontrar el valor máximo en un mes determinado, puede usar la función MAXIFS o una de las otras alternativas a continuación. En el ejemplo que se muestra, la fórmula en G5 es:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Donde las ventas (D5: D15), las fechas (B5: B15) y los valores (C5: C15) se denominan rangos.
Explicación
La función MAXIFS puede encontrar el valor máximo en un rango basado en uno o más criterios. En el ejemplo que se muestra, estamos usando MAXIFS para encontrar el valor máximo de ventas basado en un mes dado "poniendo entre corchetes" las fechas entre el primer día del mes y el último día del mes. El primer criterio verifica si las fechas son mayores o iguales que el primero del mes:
dates,">="&G4,dates // first of month
Nota: asumimos que la fecha en G4 es una fecha del "primer mes".
El segundo criterio verifica si las fechas son menores o iguales que el último del mes, calculado con la función EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Cuando ambos criterios devuelven VERDADERO, la fecha está en el mes especificado y MAXIFS devuelve el valor máximo de las fechas que cumplen los criterios.
Fórmulas alternativas
Si su versión de Excel no incluye la función MAXIFS, existen otras opciones. Una opción es una fórmula de matriz simple basada en las funciones MAX e IF:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Nota: Esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
Otra opción es usar la función AGREGAR de esta manera:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Ambas opciones anteriores usan un método más simple de probar fechas basado en la función TEXTO que usa un formato numérico para extraer el mes y el año de una fecha. La función TEXT no se puede usar así dentro de la función MAXIFS, porque MAXIFS requiere un rango. Estos enfoques alternativos se explican con más detalle aquí.
El cliente máximo
Para recuperar el cliente asociado con el valor máximo en un mes determinado, la fórmula en G6 es:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
La idea aquí es que ya conocemos el valor máximo en un mes (G5) y podemos usar ese valor como una "clave" para buscar el cliente. Tenga en cuenta que debemos repetir la lógica utilizada para aislar valores en el mes dado, para evitar una coincidencia falsa en un mes diferente.
Al igual que las alternativas mencionadas anteriormente, esta fórmula también utiliza una prueba simplificada para fechas basada en la función TEXTO. Lea más sobre INDICE y COINCIDIR con varios criterios aquí.
Para una fórmula todo en uno, reemplace G5 con una de las opciones de fórmula explicadas anteriormente para determinar el valor máximo en un mes determinado.
