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