Таблица данных в excel

data_client

Как создать таблицу данных с одной или двумя переменными в Excel

Таблица данных в Excel представляет собой диапазон, который оценивает изменение одной или двух переменных в формуле. Другими словами, это Анализ «что если», о котором мы говорили в одной из прошлых статей (если Вы ее не читали — очень рекомендую ознакомиться по этой ссылке), в удобном виде. Вы можете создать таблицу данных с одной или двумя переменными.

Предположим, что у Вас есть книжный магазин и в нем есть 100 книг на продажу. Вы можете продать определенный % книг по высокой цене — $50 и определенный % книг по более низкой цене — $20. Если Вы продаете 60% книг по высокой цене, в ячейке D10 вычисляется общая выручка по форуме 60 * $50 + 40 * $20 = $3800.

Скачать рассматриваемый пример Вы можете по этой ссылке: Пример анализа «что если» в Excel.

Таблица данных с одной переменной.

Что бы создать таблицу данных с одной переменной, выполните следующие действия:

1. Выберите ячейку B12 и введите =D10 (ссылка на общую выручку).

2. Введите различные проценты в столбце А.

3. Выберите диапазон A12:B17.

Мы будет рассчитывать общую выручку, если Вы продаете 60% книг по высокой цене, 70% книг по высокой цене и т.д.

4. На вкладке Данные, кликните на Анализ «что если» и выберите Таблица данных из списка.

5. Кликните в поле «Подставлять значения по строкам в: «и выберите ячейку C4.

Мы выбрали ячейку С4 потому что проценты относятся к этой ячейке (% книг, проданных по высокой цене). Вместе с формулой в ячейке B12, Excel теперь знает, что он должен заменять значение в ячейке С4 с 60% для расчета общей выручки, на 70% и так далее.

Примечание: Так как мы создает таблицу данных с одной переменной, то вторую ячейку ввода («Подставлять значения по столбцам в: «) мы оставляем пустой.

Вывод: Если Вы продадите 60% книг по высокой цене, то Вы получите общую выручку в размере $3 800, если Вы продадите 70% по высокой цене, то получите $4 100 и так далее.

Примечание: Строка формул показывает, что ячейки содержат формулу массива. Таким образом, Вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:B17 и нажмите Delete.

Таблица данных с двумя переменными.

Что бы создать таблицу с двумя переменными, выполните следующие шаги.

1. Выберите ячейку A12 и введите =D10 (ссылка на общую выручку).

2. Внесите различные варианты высокой цены в строку 12.

3. Введите различные проценты в столбце А.

4. Выберите диапазон A12:D17.

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

5. На вкладке Данные, кликните на Анализ «что если» и выберите Таблица данных из списка.

6. Кликните в поле «Подставлять значения по столбцам в: » и выберите ячейку D7.

7. Кликните в поле «Подставлять значения по строкам в: » и выберите ячейку C4.

Мы выбрали ячейку D7, потому что высокая цена на книги задается именно в этой ячейке. Мы выбрали ячейку C4, потому что процент продаж по высокой цене задается именно в этой ячейке. Вместе с формулой в ячейке A12, Excel теперь знает, что он должен заменять значение ячейки D7 начиная с $50 и в ячейке С4 начиная с 60% для расчета общей выручки, до $70 и 100% соответсвенно.

Вывод: Если Вы продадите 60% книг по высокой цене в размере $50, то Вы получите общую выручку $3 800, если Вы продадите 80% по высокой цене в размере $60, то получите $5 200 и так далее.

Примечание: строка формул показывает, что ячейки содержат формулу массива. Таким образом, вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:D17 и нажмите Delete.

Спасибо за внимание. Теперь Вы сможете более эффективно применять один из видов анализа «что если» , а именно формирование таблиц данных с одной или двумя переменными.

Остались вопросы — задавайте их в комментариях ниже, также не забывайте подписываться на нас в социальных сетях.

Глава 21. Таблицы данных

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

В этой главе рассматривается таблица данных, которая создает массив, как результат работы функции Таблица (эту функцию нельзя ввести в ячейки вручную). Таблица данных – это быстрый и простой способ выполнить анализ «что если» для сложных взаимосвязанных вычислений на основе формул. Эта функция позволяет изменять одну или две формулы входов и отображение того, что результаты. В главах 4, 5 и 10 уже были приведены примеры таблиц данных, которые использовались в сочетании с функциями базы данных.

Рис. 21.1. Ячейка В6 содержит формулу, которая косвенно зависит от значения ячейки В2

Скачать заметку в формате Word или pdf, примеры в формате Excel

Анализ «что если» на основе Таблицы с одной переменной

На рис. 21.1 в ячейки В6 используется функция ПЛТ, косвенно зависящая от значения ячейки В2. Если вы измените годовую ставку ставка, функция ПЛТ обновит значение в ячейке В6. Цель состоит в том, чтобы одновременно увидеть, как месячный платеж будет меняться при пяти различных годовых ставках. Хотя это можно сделать путем написания формулы, функция Таблица может быть полезна по двум причинам:

• Таблицы данных работают быстрее, чем решения на основе иных формул.
• Таблицы данных проще в использовании, и время создания формулы сокращается.

Чтобы создать таблицу данных:

1. Создайте заголовки А9:В9. В ячейке В10 введите формулу =В6. В ячейки А11:А15 введите значения годовой ставки для анализа. Выделите диапазон А10:В15.
2. Пройдите по меню ДАННЫЕ –>Анализ «что если» –>Таблица данных, чтобы открыть диалоговое окно Таблица данных, или нажав и удерживая клавишу Alt, последовательно нажмите Ы, Ё, Т (после нажатия Alt в меню будут появляться подсказки).
3. Поскольку вы анализируете влияние годовой ставки, укажите ссылку на нее в поле Подставлять значение по строкам в (рис. 21.2). Вы говорите Таблице данных, заменить значение из ячейки В2 в процессе расчета ПЛТ и вместо него подставить в формулу значения из диапазона А11:А15.
4. Нажмите ОК.

Рис. 21.2. Диалоговое окно Таблица данных

Если вы выделите диапазон В11:В15 и взглянете на строку формул, то увидите формулу массива Таблица со ссылкой на ячейку В2. Функцию Таблица нельзя ввести с клавиатуры; она автоматически создается при использовании диалогового окна Таблица данных.

Рис. 21.3. Функцию Таблица можно ввести только с помощью диалогового окна Таблица данных

На рис. 21.4 ячейки в диапазон E3:I3 содержат различные формулы, которые прямо или косвенно ссылаются на число проданных штук (в ячейке В3). Используя Таблицу данные можно выполнить анализ «что если» для пяти формул. Причем все они основываются на одной и той же переменной, расположенной в диапазоне D4:D12.

Рис. 21.4. Одна переменная Таблицы данных может работать на нескольких формулах; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Две переменные в Таблице данных

В следующем примере задача – найти минимальное значение по двум критериям. Для этого используется Таблица данных на основе двух переменных (рис. 21.5). Поместите формулу в левом верхнем углу Таблицы данных (в нашем примере – в ячейке F8), и введите настройте в диалоговом окне Таблица данных, как показано на рис. 21.5.

Рис. 21.5. Таблица данных с двумя переменными

Второй пример (рис. 21.6) вы уже видели в главе 5. Там использовалась формула массива. Например, в ячейке F9: =ИНДЕКС($C$2:$C$15;ПОИСКПОЗ($E9&F$8;$A$2:$A$15&$B$2:$B$15;0)). Решение на основе Таблицы данных проще, и работает быстрее.

Рис. 21.6. Использование Таблицы данных, как альтернатива ВПР по двум параметрам

Одно заключительное замечание по поводу Таблицы данных: существует параметр, который позволяет отключить автоматическое обновление Таблиц данных, при этом другие формулы будут пересчитываться автоматически. Если ваш файл «тормозит», пройдите по меню ФАЙЛ –> Параметры, перейдите на вкладку Формулы, и выберите опцию автоматически, кроме таблиц данных (рис. 21.7). Когда вы всё же захотите обновить вычисления в Таблице данных, нажмите F9.

Рис. 21.7. Отключение автоматического вычисления Таблиц данных

Инструмент Таблица данных в Excel

Часто при расчетах в Excel у нас возникают ситуации, когда нужно просчитать формулу при изменении переменной или переменных. За примерами далеко ходить не надо:

1. Какой будет ежемесячный платеж, если берется кредит на 100 тыс. руб. на 3 года при различных процентных ставках.
2. Сколько необходимо платить в месяц, если взять в кредит на год, два, три, четыре, пять при различных процентных ставках банка.

Смотрите также видеоверсию статьи «Инструмент Таблица данных в Excel».

Задача 1. А ведь ничего сложного

На самом деле, ничего сверхъестественного в вычислениях нет. Для расчета ежемесячного платежа по аннуитету используется финансовая функция Excel ПЛТ (PMT). Соответственно, зная желаемую сумму займа и срок кредитования, можно рассчитать ежемесячный платеж.

Расчет ежемесечного платежа с помощью ПЛТ

Общая сумма выплат рассчитывается как ежемесячный платеж умноженный на количество периодов (всего 36 месяцев), проценты переплаты – это общая сумма выплат минус сумма займа.

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

Благодаря правильному использованию различных типов ссылок и автозаполнению, можно сделать подсчет для различных процентных ставок

Решение первой задачи с помощью инструмента «Таблица данных».

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

При использовании «Таблица данных» не важен тип ссылок в формуле

Шаг 2. Выделяется диапазон с тестовым расчетом и заготовкой под остальные данные (в нашем случае это диапазон B5:G8), дальше выбирается инструмент «Таблица данных» на вкладке Данные, группа «Прогноз», команда «Анализ что если» (для версии Excel 2016, если у вас версия 2013, то таблица данных находится в группе «Работа с данными»).

Выбор инструмента «Таблица данных» на ленте интерфейса

Шаг 3. В диалоговом окне необходимо указать ссылку на ячейку, которая является переменной для расчетов. В нашем случае мы хотим посчитать различные варианты задачи при различных вариантах процентной ставки, значит необходимо поставить ссылку на годовой процент, который используется в вычислениях. Ссылку на годовой процент необходимо поставить в поле «Подставлять значения по столбцам в:», т.к. у нас значения заполняются по столбцам, а варианты годового процента расположены горизонтально. Если бы варианты различного процента были расположены вертикально, тогда ссылку необходимо было ставить во второе поле.

Также, поскольку у нас расчет идет по одной переменной, то заполняем только одно поле диалогового окна, а второе оставляем пустым.

Выбор ссылки на переменную в диалоговом окне

После нажатия «ОК» мы получи результат, а при выборе любого из значений, которое было рассчитано, в строке формул увидим формулу массива «<=ТАБЛИЦА(B5;)>», а не формулы расчета, как в случае с автозаполнением.

После расчета в строке формул находится только специальная функция ТАБЛИЦА

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

Задача 2. Рассчитать ежемесячный платеж при различных сроках займа и различных процентных ставках.

Подготовительная таблица выглядит следующим образом.

Заготовка для прогноза значений с двумя переменными

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

Расстановка ссылок на переменные в диалоговом окне

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

Результат работы инструмента «Таблица данных» с двумя входами

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

Больше примеров работы с финансовыми функциями, инструментом «Таблица данных» в восьмом занятии курса «Excel от новичка до профессионала».

Анализ данных в Excel с примерами отчетов скачать

Анализ данных в Excel предполагает сама конструкция табличного процессора. Очень многие средства программы подходят для реализации этой задачи.

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

Инструменты анализа Excel

Одним из самых привлекательных анализов данных является «Что-если». Он находится: «Данные»-«Работа с данными»-«Что-если».

Средства анализа «Что-если»:

1. «Подбор параметра». Применяется, когда пользователю известен результат формулы, но неизвестны входные данные для этого результата.
2. «Таблица данных». Используется в ситуациях, когда нужно показать в виде таблицы влияние переменных значений на формулы.
3. «Диспетчер сценариев». Применяется для формирования, изменения и сохранения разных наборов входных данных и итогов вычислений по группе формул.
4. «Поиск решения». Это надстройка программы Excel. Помогает найти наилучшее решение определенной задачи.

Практический пример использования «Что-если» для поиска оптимальных скидок по таблице данных.

Другие инструменты для анализа данных:

• группировка данных;
• консолидация данных (объединение нескольких наборов данных);
• сортировка и фильтрация (изменение порядка строк по заданному параметру);
• работа со сводными таблицами;
• получение промежуточных итогов (часто требуется при работе со списками);
• условное форматирование;
• графиками и диаграммами.

Анализировать данные в Excel можно с помощью встроенных функций (математических, финансовых, логических, статистических и т.д.).

Сводные таблицы в анализе данных

Чтобы упростить просмотр, обработку и обобщение данных, в Excel применяются сводные таблицы.

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

1. Перейти на вкладку «Вставка» и щелкнуть по кнопке «Таблица».
2. Откроется диалоговое окно «Создание таблицы».
3. Указать диапазон данных (если они уже внесены) или предполагаемый диапазон (в какие ячейки будет помещена таблица). Установить флажок напротив «Таблица с заголовками». Нажать Enter.

К указанному диапазону применится заданный по умолчанию стиль форматирования. Станет активным инструмент «Работа с таблицами» (вкладка «Конструктор»).

Составить отчет можно с помощью «Сводной таблицы».

1. Активизируем любую из ячеек диапазона данных. Щелкаем кнопку «Сводная таблица» («Вставка» — «Таблицы» — «Сводная таблица»).
2. В диалоговом окне прописываем диапазон и место, куда поместить сводный отчет (новый лист).
3. Открывается «Мастер сводных таблиц». Левая часть листа – изображение отчета, правая часть – инструменты создания сводного отчета.
4. Выбираем необходимые поля из списка. Определяемся со значениями для названий строк и столбцов. В левой части листа будет «строиться» отчет.

Создание сводной таблицы – это уже способ анализа данных. Более того, пользователь выбирает нужную ему в конкретный момент информацию для отображения. Он может в дальнейшем применять другие инструменты.

Анализ «Что-если» в Excel: «Таблица данных»

Мощное средство анализа данных. Рассмотрим организацию информации с помощью инструмента «Что-если» — «Таблица данных».

• данные должны находиться в одном столбце или одной строке;
• формула ссылается на одну входную ячейку.

Процедура создания «Таблицы данных»:

1. Заносим входные значения в столбец, а формулу – в соседний столбец на одну строку выше.
2. Выделяем диапазон значений, включающий столбец с входными данными и формулой. Переходим на вкладку «Данные». Открываем инструмент «Что-если». Щелкаем кнопку «Таблица данных».
3. В открывшемся диалоговом окне есть два поля. Так как мы создаем таблицу с одним входом, то вводим адрес только в поле «Подставлять значения по строкам в». Если входные значения располагаются в строках (а не в столбцах), то адрес будем вписывать в поле «Подставлять значения по столбцам в» и нажимаем ОК.

Анализ предприятия в Excel: примеры

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

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