Поиск решения с использованием средства «Подбор параметра».

 

Вернемся к первому нашему примеру «Покупка видеомагнитофонов в кредит» (рис. 5.82) расположенному на листе 1. В ячейке С8 содержится формула для расчета выплаты по кредиту. Если вы знаете, какую максимальную выплату можете сделать, то Excel сможет вычислить максимальную сумму кредита на покупку, которую вы можете себе позволить при заданном уровне процентной ставки и сроке погашения кредита. Поместив значение, максимально возможной выплаты в ячейку С8, вы удаляете формулу и вычисляете результат с использованием средства Подбор параметра.

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

В ячейку С4 внесем значение 3500. Сделаем активной ячейку С8 (содержащую формулу). Выполним команду ДанныеÞАнализ «что-если» Þ Подбор параметра. Откроется диалоговое окно, показанное на рисунке 5.90.

Рисунок 5.90 – Окно подбора параметра

 

В этом окне в поле Значение набрать 200, в поле Изменяя значение ячейки набрать $C$4. Щелкнуть ОК. В появившемся окне, «Результат подбора параметра», вы можете принять новое значение, щелкнув ОК, или вернуться к исходным данным, нажав, Отмена.

Подбор параметра для графиков. (Для версий Microsoft Office кроме 2007).

На новом листе построить таблицу, показанную на рисунке 5.91. Построим для нее диаграмму, представленную на рисунке 5.92. Причем в ячейки, содержащие прибыль, внесены формулы, в которых величины из строки 2 умножаются на 25%. Т.е. уровень прибыли поддерживается одинаковый – 25%.

Рисунок 5.91 – Начальные данные

 

Зададим вопрос: каким должен быть объем продаж в 2000 году, чтобы поднять уровень прибыли до 48000?

Для этого дважды щелкните на диаграмме, чтобы сделать ее активной. Щелкните на любом из столбцов в наборе данных Прибыль, чтобы выбрать этот набор. Маленький квадратик в каждом прямоугольнике указывает на то, что набор выбран. Щелкните на элементе данных Прибыль в 2005 году, чтобы выбрать его. По периметру элемента появятся маркеры. Потяните вверх центральный маркер, для того, чтобы установить высоту столбца равной прибыли 48000 руб. Открылось диалоговое окно Подбор параметра. Заполните в нем поля Установить в ячейке - $F$3; Значение - 48000; Изменяя значения ячейки - $F$2. Нажать ОК. Появится диалоговое окно Результат подбора параметра. Величина в ячейке F2 равна 192000 руб. Щелкните ОК. Чтобы выйти из режима редактирования диаграммы щелкните мышкой за пределами диаграммы (рис. 5.92).

Рисунок 5.92 – Диаграмма подбора параметра

 

Самостоятельное задание

 

1. Для уравнения y=23+x+x² в Excel выполнить подбор параметра, который дает значение y равное 112.

 

2. Выполнить в Excel таблицу подстановки для расчета выплаты по кредиту банка на сумму 100000 руб., взятого на 10 лет с изменяющимися процентами от 10 до 20% с шагом 1%.

 

3. Построить в Excel таблицу:

 

Год
Объем продаж
Цена единицы			12,50
Выручка

 

Подобрать на графике такое значение объема продаж в 2005 году, которое позволит получить выручку при той же цене 12000.

 

 

Лабораторная работа №9. Консолидация данных

 

Цель работы: изучить способы группировки данных и подсчета итоговых значений.

Задание 1. Консолидация по расположению

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

На рис. 5.93 представлен простой пример книги, содержащей итоговый лист Усредненный, который устроен так же, как четыре исходных листа. Эти листы – 2002 год, 2003 год, 2004 год и 2005 год – могут быть консолидированы по расположению, так как все они имеют идентичную структуру данных, размещенных в пяти столбцах и пяти строках.

 

Методика выполнения работы

1. Дайте имена рабочим листам: Усредненный, 2002 год, 2003 год, 2004 год, 2005 год. Отформатируйте и внесите данные, как показано на рисунке 5.93.

2. Мы воспользуемся командой Консолидация из меню Данные для консолидации данных из листов 2002 год, 2003 год, 2004 год и 2005 год в листе Усредненный.

3. Активизируйте итоговый лист и выделите конечную область, то есть блок ячеек, в который будут помещены консолидированные данные. На рис. 2 конечная область – это диапазон B3:Е6 в листе Усреднение.

Рисунок 5.93 – Заготовка для консолидации

4. На ленте Данные щелкните кнопку Консолидация.

5. Для усреднения значений из всех исходных листов выберите Среднее в поле с раскрывающимся списком Функция в окне диалога Консолидация. Оставьте флажки в секции Использовать в качестве имен не установленными. Поскольку мы не собираемся создавать связи с исходными листами, флажок Создавать связи с исходными данными также оставьте не установленным (рис. 5.94).

6. Введите ссылку для каждого исходного диапазона в поле Ссылка или выделите эти диапазоны с помощью мыши.

Рисунок 5.94 – Заполнение окна консолидации

 

Конечно, использование мыши является наиболее простым способом ввода ссылок, но если необходимо сослаться на закрытые в данный момент исходные листы, придется ввести эти ссылки с клавиатуры. (Можно использовать кнопку Обзор, чтобы определить местонахождение файла, а затем вручную ввести ссылку на ячейку.)

Вводимая ссылка должна иметь следующую форму:

[ИмяФайла]ИмяЛиста!Ссылка

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

7. Нажмите кнопку Добавить в окне диалога Консолидация. Excel перенесет ссылку из поля Ссылка в поле Список диапазонов.

 

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

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

8. Нажмите кнопку ОК. Excel усреднит исходные значения и поместит их в итоговый лист, как показано на рис. 5.95.

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

9. В столбец F добавьте формулы для подсчета среднего по магазинам с использованием функции СРЗНАЧ, как показано на рис.5.95.

Рисунок 5.95 – Итоговая таблица консолидации

Диапазон B3:Е6 в листе Усредненный теперь содержит средние значения для соответствующих ячеек в четырех исходных листах.

Задание 2. Консолидация по категории.

Теперь рассмотрим более сложный пример. На этот раз исходные листы будут содержать различное количество строк, но одинаковое количество столбцов (рис. 5.96).

Итоговый лист имеет заголовки столбцов от Экзамен 1 до Экзамен 4 – в этом отношении все листы одинаковые. Однако в итоговом листе отсутствуют заголовки строк. Мы вынуждены опустить их, потому что в исходных листах они расположены неодинаково. Как вы увидите, команда Консолидация сама вводит заголовки строк.

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

Методика выполнения работы

1. Переименуйте рабочие листы следующим образом: Средний балл, Первый семестр, Второй семестр, Третий семестр, Четвертый семестр.

2. Заполните информацией и отформатируйте (можно с помощью стилей) рабочие листы. На каждом листе должны присутствовать графы Студент, Экзамен 1, Экзамен 2, Экзамен 3, Экзамен 4, Средний. Баллы по каждому студенту и экзамену заполните самостоятельно из расчета, что балл должен находиться в пределах от 50 до 100. В графе Средний вычислите среднее значение по строке с помощью встроенной функции.

3. Перейдите на лист Средний балл.

4. Выделите конечную область.

На этот раз конечная область должна включать столбец А, чтобы Excel мог ввести заголовки для консолидированных строк. Но сколько строк должна содержать конечная область? Чтобы ответить на этот вопрос, мы можем просмотреть каждый исходный лист и определить, сколько имеется различных (уникальных) элементов строк (в данном случае фамилий студентов). Еще проще в качестве конечной области выделить ячейку А3. При задании одной ячейки в качестве конечной области команда Консолидация заполняет необходимую область ниже и справа от этой ячейки. В нашем примере мы выделили более чем достаточное число строк для размещения данных с тем, чтобы сохранить форматирование (диапазон А3:F14). В противном случае вам придется сначала консолидировать данные, а затем использовать команду Стили, чтобы быстро переформатировать итоговые данные

 

Рисунок 5.96 – Исходные листы для консолидации

5. На ленте Данные выберете, команду Консолидация и заполните окно диалога Консолидация. Выберите Среднее в поле с раскрывающимся списком Функция. В данном примере для консолидации по строкам установите флажок Значения левого столбца в секции Использовать в качестве имен.

Рисунок 5.97 – Заполнение окна консолидации

Рисунок 5.98 – Итог консолидации

 

6. Итоговый лист уже имеет заголовки столбцов, и поэтому мы можем исключить их из ссылок на исходные листы. Но наши исходные ссылки должны включать все заголовки строк и столбцы от А до F. Поэтому в поле Ссылка введите или укажите с помощью мыши следующие исходные ссылки (рис. 5.97).

7. Нажмите кнопку OK, и итоговый лист будет заполнен, как показано на рисунке 5.98.

8. Выделите диапазон числовых ячеек и задайте число десятичных знаков 1.

Задание №3. Создание связей с исходными листами.

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

Команду Консолидация можно также использовать для установления постоянной связи между итоговым и исходными листами. Чтобы создать такую связь, установите в окне диалога Консолидация флажок Создавать связи с исходными данными (рис. 5.99) и выполните консолидацию так же, как и без связи.

Рисунок 5.99 – Установление связи с исходными данными

 

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

Рисунок 5.100 – Итоговая таблицы консолидации с поддержкой

связей

 

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

2. Внесите изменения в значения по баллам на исходных листах и посмотрите, что происходит с итоговым листом.

3. На основании итогового листа постройте три различных диаграммы.

 

Самостоятельное задание

 

В магазине имеется 5 видов товаров (папки, скрепки, кнопки, скоросшиватели, степлеры). Продажа этих товаров осуществлялась в течение двух кварталов (1 квартал – январь, февраль, март; 2 квартал – апрель, май, июнь). Для оценки суммы выручки поквартально, необходимо создать на трех листах таблицы с данными: 1 лист – 1квартал; 2лист -2квартал; 3лист - итого). Воспользовавшись командой Консолидация сделать эти таблицы связанными и в итоговой таблице подсчитать сумму выручки по каждому товару за каждый квартал.