Резюме
Щоб перетворити витрати в одній одиниці часу (тобто щодня, щотижня, щомісяця тощо) в інші одиниці часу, ви можете використовувати двосторонню формулу 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, який ми використовуємо для пошуку правильного рядка в названих діапазонах vunits (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
