Резюме
Чтобы преобразовать расходы в одной единице времени (например, за день, неделю, месяц и т. Д.) В другие единицы времени, вы можете использовать двустороннюю формулу ИНДЕКС и ПОИСКПОЗ. В показанном примере формула в E5 (скопированная поперек и вниз):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
где данные (O5: S9), единицы измерения (N5: N9) и единицы измерения (O4: S4) являются именованными диапазонами, как описано ниже.
Объяснение
Чтобы преобразовать расходы в одной единице времени (например, за день, неделю, месяц и т. Д.) В другие единицы времени, вы можете использовать двустороннюю формулу ИНДЕКС и ПОИСКПОЗ. В показанном примере формула в E5 (скопированная поперек и вниз):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
В этой формуле используется таблица поиска с именованными диапазонами, как показано ниже:
Именованные диапазоны: данные (O5: S9), единицы (N5: N9) и единицы (O4: S4).
Введение
Цель состоит в том, чтобы преобразовать расходы в одной единице времени в эквивалентные расходы в других единицах времени. Например, если у нас ежемесячные расходы в размере 30 долларов США, мы хотим рассчитать годовые расходы в размере 360 долларов США, еженедельные расходы в размере 7,50 долларов США и т. Д.
Как и многие другие задачи в Excel, многое зависит от вашего подхода к проблеме. Сначала у вас может возникнуть соблазн рассмотреть цепочку вложенных формул ЕСЛИ. Это можно сделать, но в итоге вы получите длинную и сложную формулу.
Более чистый подход состоит в том, чтобы создать таблицу поиска, которая содержит коэффициенты преобразования для всех возможных преобразований, а затем использовать двустороннюю формулу ИНДЕКС и ПОИСКПОЗ для получения требуемого значения для данного преобразования. Получив значение, вы можете просто умножить его на исходную сумму.
Таблица преобразования
Таблица преобразования имеет одинаковые значения для вертикальных и горизонтальных меток: ежедневно, еженедельно, раз в две недели, ежемесячно и ежегодно. Единицы «от» перечислены по вертикали, а единицы «до» - по горизонтали. В этом примере мы хотим сопоставить сначала строку, а затем столбец. Итак, если мы хотим преобразовать ежемесячные расходы в годовые, мы сопоставляем строку «ежемесячно» и столбец «годовой» и возвращаем 12.
Для заполнения самой таблицы мы используем смесь простых формул и констант:
Примечание. Настройте значения конверсии в соответствии со своими потребностями. Ввод значения как = 1/7 - простой способ избежать ввода длинных десятичных значений.
Формула поиска
Поскольку нам нужно найти значение преобразования на основе двух входных данных, единицы времени «от» и единицы времени «до», нам нужна формула двустороннего поиска. INDEX и MATCH предлагают хорошее решение. В показанном примере формула E5 выглядит так:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Работая изнутри наружу, первая функция ПОИСКПОЗ находит нужную строку:
MATCH($D5,vunits,0) // find row, returns 4
Мы извлекаем исходную единицу времени «из» из столбца D, который мы используем, чтобы найти правую строку в названном диапазоне vunits (N5: N9). Примечание. $ D5 - это смешанная ссылка с заблокированным столбцом, поэтому формулу можно копировать.
Вторая функция ПОИСКПОЗ находит столбец:
MATCH(F$4,hunits,0) // find column, returns 5
Здесь мы получаем значение поиска из заголовка столбца в строке 4 и используем его, чтобы найти столбец справа от именованного диапазона hunits (O4: S4). Опять же, примечание F $ 4 - это смешанная ссылка с заблокированной строкой, поэтому формулу можно скопировать.
После того, как обе формулы MATCH возвращают результаты в INDEX, мы имеем:
=$C5*INDEX(data,4,5)
Массив, предоставленный для INDEX, представляет собой данные именованного диапазона (O5: S9). В строке 4 и столбце 5 ИНДЕКС возвращает 12, поэтому мы получаем окончательный результат 12000 следующим образом:
=$C5*INDEX(data,4,5) =1000*12 =12000
