Таблицы Сопряженности в Excel Как Строить
На этом шаге мы рассмотрим создание таблиц подстановки.
При работе с моделью «что-если» в определенный момент времени можно использовать только один сценарий (только один набор данных). Но что если необходимо сравнить результаты нескольких сценариев? Вот несколько вариантов решения подобной проблемы:
- Отпечатать несколько копий рабочего листа с разными сценариями на каждом.
- Скопировать модель на другие листы и расположить их окна на экране так, чтобы были видны все сценарии.
- Вручную создать формулу, которая бы подытоживала значения из ключевых ячеек для каждого сценария.
- Использовать команду Excel Данные | Таблица подстановки для автоматического создания итоговой таблицы.
- Имеется один набор данных для одной ячейки (одной переменной), на которую ссылаются несколько формул. В этом случае создается так называемая таблица подстановки с одним входом .
- Имеются два набора данных для двух ячеек (две переменные), на которые ссылается одна формула. Создаваемая в этом случае таблица называется таблицей подстановки с двумя входами .
Создать таблицу подстановки очень просто, но на ее использование наложены некоторые ограничения. Самое главное ограничение — это то, что она может временно оперировать только с одной или двумя ячейками исходных данных. Другими словами, нельзя создать таблицу подстановки, которая бы использовала комбинацию трех или более ячеек с исходными данными.
В таблице подстановки с одним входом приводятся результаты расчетов по одной или нескольким формулам при различных значениях одного входного параметра. На рисунке 1 показан общий макет таблицы подстановки с одним входом.
В приведенном ниже примере используется рабочий лист, по которому рассчитывается ипотечная ссуда (рис. 2).
Рассмотрим пример создания таблицы подстановки, в которой бы отражались значения, рассчитанные по формулам, находящимся в ячейках Размер ссуды, Месячная плата, Общая сумма, Общая сумма комиссионных , при изменении ставок от 7% до 9% с шагом 0,25%. На рисунке 3 показана заготовка таблицы подстановки для описанного примера. Строка 2 состоит из ссылок на соответствующие ячейки с формулами.
Рис.3. Подготовка к созданию таблицы подстановки с одним входом
Чтобы создать таблицу подстановки, выделите диапазон ячеек (для рассматриваемого примера G2:K11 ), а затем выберите команду Данные | Таблица подстановки . Появится диалоговое окно, показанное на рисунке 4.
Вам необходимо определить ячейку листа, в которую должны подставляться исходные данные. Поскольку все исходные данные находятся в столбце, то адрес следует поместить в поле Подставлять значения по строкам в (для нашего примера следует ввести $D$7 ). Щелкните на кнопке OK , и Excel заполнит таблицу соответствующими результатами (рис. 5).
Рис.5. Результат анализа, проведенного с помощью таблицы подстановки с одним входом
Таблица подстановки с одним входом может быть организована вертикально или горизонтально. Если значения исходных данных, которые должны подставляться во входную ячейку, расположены в строке, введите ссылку на эту ячейку в поле Подставлять значения по столбцам в , которое находится в диалоговом окне Таблица подстановки .
Таблица подстановки с двумя входами позволяет отобразить на экране результаты расчетов при изменении двух входных параметров. Макет для этого типа таблицы показан на рисунке 6.
Приведем пример таблицы подстановки с двумя входами. Это пример расчета эффективности проведения рекламной компании с помощью рассылки материалов по почте путем вычисления чистой прибыли после продажи (рис. 7).
Рис.7. Пример расчета чистой прибыли после проведения рекламной акции
В этой модели используются две ячейки для ввода информации: количество разосланных рекламных материалов и предполагаемый процент ответов. Область результата включает следующее:
- Стоимость печатных материалов . Стоимость печати одного рекламного буклета. Цена изменяется в зависимости от количества: 0,20 — если количество экземпляров не превышает 200000; 0,15 — от 200001 до 300000 экземпляров; 0,10 — если больше 300000. Стоимость отпечатаннх материалов (в зависимости от их количества) определяется по фомуле:
=ЕСЛИ(Разослано_материалов . - Почтовые расходы . Их стоимость фиксирована и составляет 0,32 за одно почтовое отправление.
- Число респондентов . Количество ответов, которое предполагается получить. Оно определяется в зависимости от процента предполагаемых ответов и количества разосланных материалов. Формула для этой ячейки следующая:
=Процент_ответевших*Разослано_материалов . - Доход на одного респондента . Это фиксированное значение. Компании известно, что за каждый заказ она получит прибыль 22.
- Суммарный доход . Суммарный доход вычисляется по простой формуле, в которой величина дохода, полученного от одного заказа, умножается на количество заказов:
=Доход_на_одного_респондента*Число_респондентов . - Суммарные расходы . По формуле, находящейся в этой ячейке, вычисляются суммарные расходы на рекламу, в которую входит стоимость печатных материалов и почтовых услуг:
=(Стоимость_печатных_материалов+Почтовые_расходы)*Разослано_материалов . - Чистая прибыль . Определяется как разность суммарных доходов и суммарных расходов.
Рис.8. Результат анализа, проведенного с помощью таблицы подстановки с двумя входами
По данным таблицы подстановки с двумя входами можно построить трехмерные диаграммы (рис. 9).
На следующем шаге мы рассмотрим анализ данных с помощью средства Диспетчер сценариев .
Создание связи между двумя таблицами в Excel
Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок.
Дополнительные сведения о связях между таблицами в Excel
Вы узнаете, существуют ли связи, при перетаскивании полей из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, то в Excel уже есть сведения, необходимые для связи данных.
Создание связей аналогично использованию VLOOKUP: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel могли ссылаться на строки в одной таблице с строками из другой таблицы. В примере со временем в таблице Customer должны быть значения дат, которые также существуют в таблице аналитики времени.
Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.
Сводная таблица на основе двух и более связанных таблиц — Сводные таблицы — Excel — Каталог статей — Perfect Excel
Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром подключении к Интернету импорт займет около минуты. После выполнения вы увидите отчет о состоянии перемещения 73 414 строк. Нажмите Закрыть.
