Одна таблиця змінних даних в Excel | Як створити? (з прикладами)

Що таке таблиця даних із однією змінною в Excel?

Одна таблиця даних змінних у Excel означає зміну однієї змінної разом із декількома параметрами та отримання результатів для декількох сценаріїв.

Як створити таблицю даних із однією змінною в Excel? (з прикладами)

Завантажити цей шаблон змінної таблиці даних з однією змінною можна тут - Шаблон Excel з однією змінною таблицею даних

Приклад №1

Ви берете позику в Rs. 2 50000 на 2 роки. Ви обговорюєте з кредитним працівником питання процентної ставки.

Вам потрібно проаналізувати за різними процентними ставками, який щомісячний платіж потрібно сплатити, щоб очистити позику. Для цілей розрахунку візьміть базову ставку 12% за анум.

  • Крок 1: Розрахуйте щомісячний EMI за допомогою функції PMT.

  • Крок 2: Тепер створіть таблицю сценаріїв, як показано на малюнку нижче.

  • Крок 3: Виберіть комірку E9 і дайте посилання на комірку B6 (сума EMI). Тепер клітинка E9 відображає щомісячний EMI.

  • Крок 4: Виберіть діапазон від D9 до E22.

  • Крок 5: Клацніть на вкладці Дані, а потім "  Що-якщо-аналіз" та " Таблиця даних"

  • Крок 6: З'явиться діалогове вікно Таблиця даних. У комірці вводу стовпця виберіть клітинку B5 (яка містить основну процентну ставку за позикою).

Одна таблиця змінних даних в Excel, ми завжди ігноруємо або вхідну комірку РЯДО, або комірку введення Стовпця. Це залежить від нашої структури таблиці. Якщо в нашій таблиці сценаріїв різні процентні ставки вертикальні, тоді ми ігноруємо комірку введення рядка, а якщо процентні ставки таблиці сценаріїв горизонтальні, то ігноруємо комірку введення стовпця. У цьому прикладі я проігнорував комірку введення рядка, оскільки в таблиці сценаріїв різні процентні ставки розташовані вертикально.

  • Крок 7: Тепер натисніть кнопку OK, щоб створити різні сценарії.

Тепер діапазон E10: E22 показує деякі нові значення. З таблиці дуже ясно видно, що @ 12,5% відсоткової ставки щомісяця EMI становитиме 11 827 INR, а @ 13,5% відсоткової ставки щомісяця EMI буде 11 944 INR тощо.

Ось як працює одна таблиця даних змінних у Excel. Ви також можете показати це на графіку.

Приклад №2

Припустимо, ви менеджер з продажу компанії. Від керівництва ви щомісяця отримували від своєї команди цільовий показник продажів 1, 70, 00 USD. У таблиці нижче наведено цільовий рівень продажів для 6 учасників. Вам потрібно проаналізувати, яким повинен бути рівень їхньої ефективності, щоб досягти мети в 1,7 лари США за місяць.

Загальна ціль ваших команд - 2,04 лака. Ви не впевнені, на скільки відсотків ефективності вони повинні залучити до таблиці для досягнення цілі, заданої керівництвом.

Ваша команда може дати максимальний рівень ефективності 90%, і ви розрахували загальний дохід на рівні 90% ефективності.

На рівні 90% ефективності ваша команда може досягти загального доходу в розмірі 1,83 лак за місяць. Вам потрібно досягти рівня ефективності для досягнення цільового доходу, заданого керівництвом.

Створіть таблицю сценаріїв, як показано на малюнку нижче.

  • Крок 1: Під таблицею потрібно створити файл Excel.

У цій таблиці на різних рівнях ефективності показано, яким буде дохід?

  • Крок 2: Виберіть комірку H3 та надайте посилання на клітинку B11 (на рівні 90% ефективності доходу комірка). Тепер клітинка клітини H3 демонструє 90% доходу від рівня ефективності.

  • Крок 3: Виберіть діапазон від G3 до H12

  • Крок 4: Тепер знайдіть таблицю даних у розділі Що робити, якщо аналіз.

  • Крок 5: Після натискання таблиці даних нам потрібно надати посилання у комірці вводу стовпця , виберіть комірку B10 (яка містить відсоток ефективності).

Для вхідної комірки Стовпець я дав посилання на комірку B10, оскільки на основі різних рівнів ефективності ми збираємося створити сценарії. Тепер, згідно з таблицею даних, 90% доходу становитиме 1,83 лари USD. Так само він створить сценарії на 100%, 95%, 90%, 85%, 80% тощо.

  • Крок 6: Натисніть OK, щоб створити різні сценарії.

Тепер діапазон G3: H12 показує сценарії. Керівництво визначило цільовий показник на цей місяць 1,70 лари. Для того, щоб отримати стільки доходу, ваша команда повинна працювати на рівні ефективності щонайменше 85%.

Таким чином, ви можете використовувати таблицю даних для створення іншого аналізу та обрання відповідного сценарію для досягнення цілей.

Що слід пам’ятати

  • Після створення сценаріїв ми не можемо скасувати дію. Це залишиться незмінним.
  • Ми навіть не можемо змінити значення комірок, оскільки це стає формулою масиву excel, і вам потрібно видалити все не одне за одним.
  • В одній таблиці даних змінних завжди залишайте осередок введення рядка, якщо сценарій відображається у вертикальній формі. Якщо сценарій повинен відображатися в горизонтальній формі, залиште клітинку введення стовпця.
  • Ви можете змінити значення в основній базі даних, щоб побачити результати в реальному часі для різних типів альтернатив.