Годовой оборот

Номера компьютеров

П/п

Минимальный доход С2

Ведомость годового дохода

Лист1:Лист3!А1:С12

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

Пример. Десять компьютеров выполняли работу трех издательств: Гелиос, Сириус и Фобос, каждый из которых увеличивал свои доходы с помощью Интернета: созданием Web-страницы (+10% оборота), Web-узла (+20%) и Web-сервера (+30%). Требуется ссоздать таблицу в котором подсчитаны дополнительная прибыль издательств (Гелиос, Сириус, Фобос) от прироста оборота. Создайте отдельно таблицы трех издательств и одну итоговую таблицу, в которой получите суммарное значение прибыли, полученных сотрудниками на 10 компьютерах.

Задание 1. Создание дополнительных рабочих листов.

· Переименуйте рабочие листы книги EXCEL в листы с названиями Гелиос, Сириус, Фобос, ИТОГО спомощью контекстного меню. Выделите эти листы, щелкните по ярлычку первого листа Гелиос и нажмите клавишу Shift по ярлычку последнего листа Фобос. Ярлычки выделенных листов окрасятся белым цветом, т.е. будет включен групповой режим работы, т.е. все, что будет набираться на первом рабочем листе Гелиос, будет одновременно копироваться на всех остальных листах, включенных в группу. Сохраните файл именем Издательство.

Задание 2. Заполнение элементов таблиц рабочих листов

· В групповом режиме создайте шапку таблицы Гелиос как в таблице 6.1:

Задание 3. Расчет по формулам на рабочих листах и редактирование данных на нескольких рабочих листах.

· В групповом режиме на рабочем листе Гелиос в ячейках D4, E4, F4 введите формулы:

Прирост оборота

=C4+C4*0,01+C4*0,02+C4*0,03

Дополнительная прибыль

=D4-C4

Годовой доход

=C4*0,4+E4

· В ячейках C15, D15, E15, F15 выполните автосуммирование соответствующих столбцов C4:С13, D4:D13, E4:F13, F4:F13.

· В ячейке С2 скорректируйте формулу =МИН(F4:F13)

· Эти формулы размножьте в соответствующих диапазонах D4:D13, E4:F13, F4:F13. В групповом режиме эти формулы скопируются на рабочих листах Сириус, Фобос, ИТОГО.

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

Задание 4. Отображение таблиц в режимах формул и чисел.

· Отобразите полученные данные таблиц Гелиос, Сириус, Фобос в режиме формул, для чего для каждого листа откройте Сервис-Параметры и в окне Параметр окна установите щелчком мыши галочку в поле формулы.

· Отобразите полученные данные таблиц Гелиос, Сириус, Фобос в режиме чисел, для этого откройте Сервис-Параметры и в окне Параметр окна отмените щелчком мыши галочку в поле формулы.

Таблица 1. Рабочий лист Гелиос в режиме отображения чисел

Таблица 2. Рабочий лист Гелиос в режиме отображения формул

Таблица 3. Рабочий лист СИРИУС в режиме отображения чисел

Таблица 4. Рабочий лист СИРИУС в режиме отображения формул

Таблица 5Рабочий лист ФОБОС в режиме отображения чисел

Таблица 6 Рабочий лист ФОБОС в режиме отображения формул

Задание 5. Расчет и редактирование связанных итоговых данных нескольких рабочих листов.

· На рабочем листе ИТОГО в столбцах С2, D4:D15, E4: E15, F4: F15 введите итоговые формулы

=МИН(F4:F13)

Годовой оборот

=СУММ(ГЕЛИУС:ФОБОС!C4)

Прирост оборота

=СУММ(ГЕЛИУС:ФОБОС!D4)

Дополнительная прибыль

=D4-C4

Годовой доход

=C4*0,4+E4

В результате получите следующие таблицы.

Таблица 7 Рабочий лист ИТОГО в режиме отображения чисел

Таблица 8 Рабочий лист ИТОГО в режиме отображения формул

Задание 6. Консолидация рабочих листов.

· Произведите консолидацию рабочих листов. Новый рабочий лист табличного процессора EXCEL переименуйте Консолидация и поставьте курсор в ячейку А1 и вызовите команду Данные консолидация. Откроется диалоговое окно Консолидация (Таблица 9) и поле Функция.

Таблица 9 Диалоговое окно консолидации

· Выберите функцию Сумма и поместите курсор во второе поле Ссылка. Выделитедиапазон А1:F11 листа ГЕЛИУС и щелкните по кнопке Добавить. Эта область автоматически перенесется в поле Список диапазонов.

· Аналогично в список диапазонов внесите листы СИРИУС, ФОБОС.

· Клавишей Del очистите поле Ссылка и щелкните мышью по флажку Создавать связи с исходными данными, при изменении исходных данных в рабочих листах ГЕЛИУС, СИРИУС, ФОБОС изменятся и консолидированные данные.

Задание 7. Создание связи с метками исходных данных

· Очистите поле Ссылка и щелкните мышью по флажку Создавать связи с исходными данными ищелкните мышью по флажку метку в верхней строке. В левом столбцеполучите консолидированные данные, начиная с верхней левой ячейки расположенные в столбик, где находится курсор. Обратите внимание на соответствие Таблицы 7 ИТОГО и Таблицы 10 Консолидированные данные за квартал и насколько быстрее получены консолидированные данные, так как формулысоздаются программой EXCEL автоматически.

Таблица 10 Консолидированные данные за квартал в режиме отображения чисел

Колонка D -отражает Годовой оборот

Колонка Е -Прирост оборота

Колонка F - Дополнительную прибыль

Колонка G - Годовой доход

Таблица 11 Консолидированные данные за квартал в режиме отображения формул

Консолидированные данные вТаблицах 10 и 11 представлены в структурированной форме, что упрощает визуальный контроль в больших таблицах, при которых отдельные строки таблицы скрыты. Щелкая мышью по кнопкам, расположенных слева вдоль бордюра, можете скрывать и открывать элементы структуры таблицы

скрыть элементы отобразить элементы

Задание 8. Консолидации рабочих листов в режимах структурирования и отображения элементов.

· Получите таблицы 12 и 13, в которых представлены консолидированные данные с отображениями элементов структуры (все строки таблицы видны на экране).

· Удалите отображения элементов структуры, выполнив команду Данные, Структура и группы, Удалить структуру.

Таблица 12. Консолидированные данные за квартал в режиме отображения формул с отображением элементов структуры

Таблица 13. Консолидированные данные за квартал в режиме

отображения чисел с отображением элементов структуры

Дата добавления: 2014-11-29