Resumo
Para converter uma despesa em uma unidade de tempo (ou seja, diária, semanal, mensal, etc.) para outras unidades de tempo, você pode usar uma fórmula de ÍNDICE e CORRESPONDÊNCIA bidirecional. No exemplo mostrado, a fórmula em E5 (copiada transversalmente e para baixo) é:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
onde dados (O5: S9), vunidades (N5: N9) e hunits (O4: S4) são intervalos nomeados, conforme explicado abaixo.
Explicação
Para converter uma despesa em uma unidade de tempo (ou seja, diária, semanal, mensal, etc.) para outras unidades de tempo, você pode usar uma fórmula de ÍNDICE e CORRESPONDÊNCIA bidirecional. No exemplo mostrado, a fórmula em E5 (copiada transversalmente e para baixo) é:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Esta fórmula usa uma tabela de pesquisa com intervalos nomeados, conforme mostrado abaixo:
Intervalos nomeados: dados (O5: S9), vunidades (N5: N9) e hunits (O4: S4).
Introdução
O objetivo é converter uma despesa em uma unidade de tempo em uma despesa equivalente em outras unidades de tempo. Por exemplo, se temos uma despesa mensal de $ 30, queremos calcular uma despesa anual de $ 360, uma despesa semanal de $ 7,50, etc.
Como tantos desafios no Excel, muito depende de como você aborda o problema. Você pode primeiro ficar tentado a considerar uma cadeia de fórmulas IF aninhadas. Isso pode ser feito, mas você terminará com uma fórmula longa e complicada.
Uma abordagem mais limpa é criar uma tabela de pesquisa que contenha fatores de conversão para todas as conversões possíveis e, em seguida, usar uma fórmula INDEX e MATCH bidirecional para recuperar o valor necessário para uma determinada conversão. Depois de ter o valor, você pode simplesmente multiplicar pelo valor original.
A tabela de conversão
A tabela de conversão tem os mesmos valores para rótulos verticais e horizontais: diário, semanal, quinzenal, mensal e anual. As unidades "de" são listadas verticalmente e as unidades "para" são listadas horizontalmente. Para os fins deste exemplo, queremos corresponder a linha primeiro, depois a coluna. Então, se quisermos converter uma despesa mensal em uma despesa anual, combinamos a linha "mensal" e a coluna "anual" e retornamos 12.
Para preencher a própria tabela, usamos uma combinação de fórmulas simples e constantes:
Nota: Personalize os valores de conversão para atender às suas necessidades específicas. Inserir um valor como = 1/7 é uma maneira fácil de evitar inserir valores decimais longos.
A fórmula de pesquisa
Como precisamos localizar um valor de conversão com base em duas entradas, uma unidade de tempo "de" e uma unidade de tempo "para", precisamos de uma fórmula de pesquisa bidirecional. INDEX e MATCH fornecem uma boa solução. No exemplo mostrado, a fórmula em E5 é:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Trabalhando de dentro para fora, a primeira função MATCH localiza a linha correta:
MATCH($D5,vunits,0) // find row, returns 4
Extraímos a unidade de tempo "de" original da coluna D, que usamos para encontrar a linha certa nas vunidades de intervalo nomeadas (N5: N9). Observação $ D5 é uma referência mista com a coluna bloqueada, portanto, a fórmula pode ser copiada.
A segunda função MATCH localiza a coluna:
MATCH(F$4,hunits,0) // find column, returns 5
Aqui, temos o valor de pesquisa a partir do cabeçalho da coluna na linha 4, e usar isso para encontrar o direito "de" coluna no intervalo nomeado hunits (O4: S4). Novamente, observe que F $ 4 é uma referência mista com a linha bloqueada, portanto, a fórmula pode ser copiada.
Depois que ambas as fórmulas MATCH retornam resultados para INDEX, temos:
=$C5*INDEX(data,4,5)
A matriz fornecida para INDEX são os dados do intervalo nomeado , (O5: S9). Com uma linha de 4 e coluna de 5, INDEX retorna 12, então obtemos um resultado final de 12.000 como este:
=$C5*INDEX(data,4,5) =1000*12 =12000
