ifempty

    Урок 10. Анализ, что если: Таблицы данных

    Оглавление 

    Введение

    Работа с таблицей данных

    Файлы для скачивания

     

    Введение

    Таблица данных - это инструмент для получения множества результатов вычисления формулы с двумя изменяемыми переменными. Таблица данных находится на вкладке Данные-> Анализ, "что если" -> Таблица данных.

     таблица данных рис1

    Рисунок 1. Расположение кнопки Таблица данных на вкладке Данные

    Для того, чтобы использовать таблицу данных, у вас должен быть расчет/формула, в переменные которой таблица данных будет подставлять указанные вами значения. 

    Далее вам нужно построить таблицу, с которой будет работать таблица данных. Конструкция таблицы выглядит следующим образом:

     таблица данных рис2

    Рисунок 2. Конструкция таблицы данных

    На рисунке 3 показан пример таблицы данных. В Таблице1 находится расчет заработной платы, которую сотрудник получает на руки - за минусом пенсионных взносов и индивидуального подходного налога. В Таблице2 находится таблица данных, которая показывает какой будет зарплата на руки сотрудника, если ставка ИПН будет увеличиваться на 1% и оклад будет увеличиваться на 50 000. 

     таблица данных рис3

    Рисунок 3. Пример построения таблицы данных

     

    Работа с таблицей данных

    Скачайте файл Приложение 1.xlsx по ссылке ниже.

    Откройте лист Задача без решения. Если у вас стоит защищенный просмотр, то нажмите на кнопку разрешить редактирование в правом верхнем углу окна. 

     таблица данных рис4

    Рисунок 4. Разрешение редактирования файла

    Задача:

    На листе Задача без решения находится таблица с расчетом ЗП на руки сотрудника. Нужно с помощью таблицы данных определить, как будет меняться ЗП на руки сотрудника в течение следующих 5 лет, если ставка ИПН будет расти каждый год на 1%, и при этом оклад сотрудника будет увеличиваться на 50000 каждый год.

    Решение:

    Для использования таблицы данных у нас должно быть:

    1. Расчет/формула, в которую с помощью таблицы данных мы будем подставлять изменяемые значения;
    2. Посмтроить нужную конструкцию, как на рисунке 2;
    3. Настроить и заполнить таблицу.

    Расчет ЗП на руки уже есть - формула находится в ячейке G3. Осталось создать конструкцию таблицы и настроить таблицу данных. На следующем рисунке 5 показана конструкция таблицы данных:

     таблица данных рис5

     Рисунок 5. Конструкция таблицы данных 

    На рисунке 5 по столбцам находятся ставки ИПН, а по строкам оклады к начислению. На пересечении строк и столбцов в ячейке C7 находится ссылка на ячейку с формулой расчета ЗП на руки - =G3. Ставка ИПН увеличивается на 1%, а оклады к начислению на 50000. 

    После того, как конструкция создана, нужно выделить данные начиная от ячейки с формулой до конца таблицы вправо, как показано на рисунке 6, а затем перейти на вкладку Данные  -> Анализ, "что если" - > Таблица данных. 

     таблица данных рис6

    Рисунок 6. Выделение таблицы и вкладка Данные

    У вас откроется окно Таблица данных, где нужно заполнить поля, как указано на рисунке 7 и нажать на кнопку ОК.:

     таблица данных рис7

    Рисунок 7. Окно настройки таблицы данных

    После этого Таблица2 заполнится данными и примет следующий вид:

     таблица данных рис8

    Рисунок 8. Результат работы инструмента Таблица данных

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.