задачи в виде модели. Результат сохранить в виде сценария и отчета. После просмотра результата восстановить исходные значения.

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

8.Изменить ограничение, установив верхний предел расходов на рекламу за год 50000 р. Сохранить установки задачи в виде модели. Результат сохранить в виде сценария и отчета. После просмотра результата восстановить исходные значения.

9.Просмотреть отчеты по результатам поиска решения.

10.Загрузить и просмотреть все созданные сценарии.

11.Восстановить первоначальные значения с помощью первого сценария.

4. Консолидация данных

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

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

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

•выделить только одну ячейку, тогда после выполнения консолидации Excel расширит диапазон назначения вправо и вниз от выделенной ячейки до необходимых размеров;

•выделить диапазон ячеек, состоящий из нескольких строк или столбцов, чтобы было консолидировано ровно столько категорий по столбцам и строкам;

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

62

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

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

1.На новом листе сформировать шапку итоговой таблицы, в соответствии со структурой сводимых таблиц. Установить курсор в верхнюю левую ячейку предполагаемого диапазона назначения объединенных данных (под названием 1-го столбца).

2.Задать команду КОНСОЛИДАЦИЯ из меню ДАННЫЕ.

3.В окне КОНСОЛИДАЦИЯ в поле ФУНКЦИЯ указать нужную функцию (например, СУММА).

4.Установив курсор в поле ССЫЛКА, поочередно в каждой из сводимых таблиц (с разных листов или разных рабочих книг) с помощью мыши выделять диапазон подлежащих консолидации данных (без шапки) и нажимать кнопку ДОБАВИТЬ.

5.Если результат консолидации должен изменяться при изменении исходных данных, включить параметр СОЗДАВАТЬ СВЯЗИ С ИСХОДНЫМИ

ДАННЫМИ.

6. После добавления всех консолидируемых областей нажатьОК.

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

1.Заполним рабочую книгу данными о получаемых сотрудниками зарплате и премиях, поместив информацию о каждом месяце на отдельный лист. Выделив два листа рабочей книги введем в 1-ю строку шапку таблицы «Фамилия», «Оклад», «Премия» и в первый столбец любые 3 фамилии.

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

3.На новом рабочем листе сформируем итоговую таблицу, скопировав названия столбцов.

4.Поместим курсор под названием 1-го столбца, т.е. «Фамилия».

5.Выберем в меню ДАННЫЕ команду КОНСОЛИДАЦИЯ.

6.В полеФУНКЦИЯ из списка функций выберем функцию СУММА.

7.Установив курсор в поле ССЫЛКА, щелкнем мышью на ярлыке первого листа и выделим на нем все фамилии и числовые значения.

8.Нажмем кнопку ДОБАВИТЬ в окне КОНСОЛИДАЦИЯ.

9.Вернувшись в поле ССЫЛКА аналогичным образом добавим данные со 2-го листа.

10.В наборе флажков ИСПОЛЬЗОВАТЬ В КАЧЕСТВЕ ИМЕН установим флажок, соответствующий расположению в исходной части заголовков: ПОДПИСИ ЛЕВОГО

СТОЛБЦА.

11.После нажатия на ОК в новую таблицу будут помещены фамилии всех сотрудников

исуммы полученных ими окладов и премий.

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

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

1. Если консолидация производится не по всем категориям, то сначала следует

вобласть консолидации (т.е. в результирующую таблицу) ввести требуемые

63

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

ИСПОЛЬЗОВАТЬ В КАЧЕСТВЕ ИМЕН в окне КОНСОЛИДАЦИЯ будут

установлены соответствующие флажки, то эти категории будут помещены автоматически).

2.Установить курсор в верхнюю левую ячейку диапазона назначения (области, где будут располагаться значения объединенных данных).

3.Задать команду КОНСОЛИДАЦИЯ из меню ДАННЫЕ.

4.В поле ФУНКЦИЯ указать нужную функцию (например, СУММА).

5.В наборе флажков ИСПОЛЬЗОВАТЬ В КАЧЕСТВЕ ИМЕН следует установить

флажки, соответствующие расположению в исходной части заголовков:

ПОДПИСИ ВЕРХНЕЙ СТРОКИ или ЗНАЧЕНИЯ ЛЕВОГО СТОЛБЦА или ПОДПИСИ ВЕРХНЕЙ СТРОКИ иЗНАЧЕНИЯ ЛЕВОГО СТОЛБЦА одновременно.

6.Установив курсор в поле ССЫЛКА, поочередно в каждой из сводимых таблиц (с разных листов) с помощью мыши выделять диапазон подлежащих консолидации данных (вместе с именами-категориями) и нажимать кнопку

ДОБАВИТЬ.

7.Если результат консолидации должен изменяться при изменении исходных

данных, включить параметр СОЗДАВАТЬ СВЯЗИ С ИСХОДНЫМИ ДАННЫМИ.

8.После добавления всех консолидируемых областей нажатьОК.

4.3.Удаление результатов ошибочной консолидации

Витоговой таблице, полученной при консолидации без параметра СОЗДАВАТЬ

СВЯЗИ С ИСХОДНЫМИ ДАННЫМИ, следует удалить только неправильные данные. В окне КОНСОЛИДАЦИЯ необходимо кнопкой УДАЛИТЬ очистить все ссылки в поле СПИСОК

ДИАПАЗОНОВ.

Если использовалась консолидация с параметром СОЗДАВАТЬ СВЯЗИ С ИСХОДНЫМИ ДАННЫМИ, в итоговой таблице придется удалить все строки с неправильными данными, раскрыв кнопкой 2 (второй уровень) все строки таблицы. В окне

КОНСОЛИДАЦИЯ кнопкой УДАЛИТЬ очистить все ссылки в поле СПИСОК ДИАПАЗОНОВ.

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

1.Воспользуемся данными первых 2-х листов (Пример V.5).

2.Добавим на 2-м листе еще 2 любые фамилии и соответствующие им значения окладов и премий.

3.На новом рабочем листе (или в новом месте 3-го листа) сформируем итоговую таблицу, скопировав или введя заново названия столбцов «Фамилия» и «Премия».

4.Выделим названия столбцов, т.е. «Фамилия» и «Премия».

5.Выберем в меню ДАННЫЕ команду КОНСОЛИДАЦИЯ.

6.В полеФУНКЦИЯ из списка функций выберем функцию КОЛИЧЕСТВО ЗНАЧЕНИЙ.

7.С помощью кнопки УДАЛИТЬ удалим старые ссылки из поля СПИСОК ДИАПАЗОНОВ.

8.Установив курсор в поле ССЫЛКА, щелкнем мышью на ярлыке первого листа и выделим на нем значения всех 3-х столбцов (включая шапку).

9.Нажмем кнопку ДОБАВИТЬ в окне КОНСОЛИДАЦИЯ.

10.Вернувшись в поле ССЫЛКА аналогичным образом добавим данные со 2-го листа (обратите внимание, что исходная область на этом листе больше и ее нужно увеличить).

64

11.В наборе флажков ИСПОЛЬЗОВАТЬ В КАЧЕСТВЕ ИМЕН установим флажок, соответствующий расположению в исходной части заголовков: ПОДПИСИ ВЕРХНЕЙ

СТРОКИ иПОДПИСИ ЛЕВОГО СТОЛБЦА.

12.После нажатия на ОК в новую таблицу будут помещены фамилии всех сотрудников и количество полученных ими премий.

Пример V.7. Требуется подсчитать средний оклад первых двух сотрудников по итогам 2 месяцев (основываясь на данных Пример V.5).

1.На новом рабочем листе (или в новом месте 3-го листа) сформируем итоговую таблицу, скопировав или введя заново названия столбцов «Фамилия» и «Оклад».

2.Под названием 1-го столбца, т.е. «Фамилия» выделим диапазон назначения из 2-х строк и 2-х столбцов (для записи фамилий и средних окладов).

3.Выберем в меню ДАННЫЕ команду КОНСОЛИДАЦИЯ.

4.В полеФУНКЦИЯ из списка функций выберем функцию СРЕДНЕЕ.

5.Установив курсор в поле ССЫЛКА, щелкнем мышью на ярлыке первого листа и выделим на нем значения 1-го и 2-го столбцов (без шапки), соответствующие первым двум сотрудникам.

6.Нажмем кнопку ДОБАВИТЬ в окне КОНСОЛИДАЦИЯ.

7.Вернувшись в поле ССЫЛКА аналогичным образом добавим данные со 2-го листа.

8.В наборе флажков ИСПОЛЬЗОВАТЬ В КАЧЕСТВЕ ИМЕН установим флажок, соответствующий расположению в исходной части заголовков: ПОДПИСИ ЛЕВОГО

СТОЛБЦА.

9.После нажатия на ОК в новую таблицу будут помещены фамилии первых 2-х сотрудников и среднее значение полученных ими окладов.

Пример V.8. Требуется найти суммы окладов и премий сотрудников по итогам 3 месяцев, предусмотрев связь с исходными данными.

1.Скопируйте данные 1-го листа в новую рабочую книгу, поместив их также на 1-й лист.

2.На втором рабочем листе новой книги сформируем итоговую таблицу, скопировав названия всех столбцов.

3.Поместим курсор под названием 1-го столбца, т.е. «Фамилия».

4.Выберем в меню ДАННЫЕ команду КОНСОЛИДАЦИЯ.

5.В полеФУНКЦИЯ из списка функций выберем функцию СУММА.

6.Установив курсор в поле ССЫЛКА, щелкнем мышью на ярлыке первого листа и выделим на нем все фамилии и числовые значения.

7.Нажмем кнопку ДОБАВИТЬ в окне КОНСОЛИДАЦИЯ.

8.Вернувшись в поле ССЫЛКА аналогичным образом добавим данные с 1-го и 2-го листа первой книги. Для этого перейдем в нее через меню ОКНО или нажав клавиши

<Ctrl><F6>.

9.В наборе флажков ИСПОЛЬЗОВАТЬ В КАЧЕСТВЕ ИМЕН установим флажок: ПОДПИСИ

ЛЕВОГО СТОЛБЦА.

10.Включим параметр СОЗДАВАТЬ СВЯЗИ С ИСХОДНЫМИ ДАННЫМИ.

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

12.Перейдем на 1-й лист рабочей книги и внесем изменения в числовые данные. Автоматически произойдут изменения в результатной таблице.

Вполученной таблице можно работать как со структурой:

Нажатие кнопки 2 (второй уровень) раскроет все внутренние данные, входящие в сумму.

Нажатие кнопки 1 (первый уровень) восстановит вариант таблицы с итоговыми данными.

65

Нажатие кнопки + (плюс) приводит к раскрытию промежуточных данных для соответствующей метки (категории).

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

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

Задание:

Выполните примеры V.5-V.8.

Контрольные вопросы

1.В каких случаях может быть применена команда Подбор параметра?

2.В каких случаях может быть применена команда Таблица подстановки?

3.В каких случаях может быть применена команда Поиск решения?

4.Для чего предназначен инструмент Консолидация?

5.Какие методы консолидации существуют? В каких случаях может быть применен каждый из них?

6.Можно ли консолидировать данные не по всем категориям? Если – да, то что для этого нужно сделать?

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

8.Как удалить результаты ошибочной консолидации?

Упражнение 6

1.Создать рабочую книгу из трех листов. Присвоить первому листу имя “Январь”, второму - “Февраль”, третьему - “Итого”.

2.Выделить все листы и ввести название и шапку таблиц.

3.Снять выделение листов и внести необходимые изменения в названия трех таблиц.

4.Заполнить данными таблицу № 1 на листе “Январь”, а таблицу № 2 - на листе

Таблица 1

Справка по объемам эмиссии ГКО и доходах бюджета за январь

№ выпуска ГКО	Эмиссия	Выручка		Погашено	Доходы	Средн.взв.
					бюджета
	(млрд.руб.)	(млрд.руб.)		(млрд.руб.)	(млрд.руб.)	цена
21000RMFS	979,69	662,95		433,90
21000RMFS	1998,00	1276,40		1250,70
22000RMFS	2440,89	1409,89		1296,50
23000RMFS	278,53	197,45
22000RMFS	162,50	66		54,64
		118,23
ИТОГО: