Resumen
Para convertir un gasto en una unidad de tiempo (es decir, diario, semanal, mensual, etc.) a otras unidades de tiempo, puede utilizar una fórmula de INDICE y COINCIDIR bidireccional. En el ejemplo que se muestra, la fórmula en E5 (copiada horizontalmente y hacia abajo) es:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
donde los datos (O5: S9), vunits (N5: N9) y hunits (O4: S4) se denominan rangos, como se explica a continuación.
Explicación
Para convertir un gasto en una unidad de tiempo (es decir, diario, semanal, mensual, etc.) a otras unidades de tiempo, puede utilizar una fórmula de INDICE y COINCIDIR bidireccional. En el ejemplo que se muestra, la fórmula en E5 (copiada horizontalmente y hacia abajo) es:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Esta fórmula utiliza una tabla de búsqueda con rangos con nombre como se muestra a continuación:
Rangos con nombre: datos (O5: S9), vunits (N5: N9) y hunits (O4: S4).
Introducción
El objetivo es convertir un gasto en una unidad de tiempo en un gasto equivalente en otras unidades de tiempo. Por ejemplo, si tenemos un gasto mensual de $ 30, queremos calcular un gasto anual de $ 360, un gasto semanal de $ 7,50, etc.
Como muchos desafíos en Excel, mucho depende de cómo aborde el problema. Es posible que primero tenga la tentación de considerar una cadena de fórmulas SI anidadas. Esto se puede hacer, pero terminará con una fórmula larga y complicada.
Un enfoque más limpio es crear una tabla de búsqueda que contenga factores de conversión para todas las conversiones posibles, luego usar una fórmula de INDICE y COINCIDIR bidireccional para recuperar el valor requerido para una conversión determinada. Una vez que tenga el valor, simplemente puede multiplicar por la cantidad original.
La tabla de conversión
La tabla de conversión tiene los mismos valores para las etiquetas verticales y horizontales: diaria, semanal, quincenal, mensual y anual. Las unidades "desde" se enumeran verticalmente y las unidades "hasta" se enumeran horizontalmente. Para los propósitos de este ejemplo, queremos hacer coincidir la fila primero, luego la columna. Entonces, si queremos convertir un gasto mensual en un gasto anual, hacemos coincidir la fila "mensual" y la columna "anual" y devolvemos 12.
Para completar la tabla en sí, usamos una combinación de fórmulas y constantes simples:
Nota: personalice los valores de conversión para satisfacer sus necesidades específicas. Ingresar un valor como = 1/7 es una manera fácil de evitar ingresar valores decimales largos.
La fórmula de búsqueda
Dado que necesitamos ubicar un valor de conversión basado en dos entradas, una unidad de tiempo "desde" y una unidad de tiempo "a", necesitamos una fórmula de búsqueda bidireccional. INDEX and MATCH proporciona una buena solución. En el ejemplo que se muestra, la fórmula en E5 es:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Trabajando de adentro hacia afuera, la primera función COINCIDIR ubica la fila correcta:
MATCH($D5,vunits,0) // find row, returns 4
Extraemos la unidad de tiempo "desde" original de la columna D, que usamos para encontrar la fila correcta en el rango nombrado vunits (N5: N9). Tenga en cuenta que $ D5 es una referencia mixta con la columna bloqueada, por lo que la fórmula se puede copiar.
La segunda función COINCIDIR ubica la columna:
MATCH(F$4,hunits,0) // find column, returns 5
Aquí, obtenemos el valor de búsqueda del encabezado de la columna en la fila 4, y lo usamos para encontrar la columna derecha "a" en el rango con nombre hunits (O4: S4). Nuevamente, tenga en cuenta que F $ 4 es una referencia mixta con la fila bloqueada, por lo que la fórmula se puede copiar.
Después de que ambas fórmulas COINCIDIR devuelvan resultados a ÍNDICE, tenemos:
=$C5*INDEX(data,4,5)
La matriz proporcionada a INDEX son los datos del rango con nombre , (O5: S9). Con una fila de 4 y una columna de 5, INDICE devuelve 12, por lo que obtenemos un resultado final de 12000 como este:
=$C5*INDEX(data,4,5) =1000*12 =12000
