Методика выполнения задания 2

1. Формирование листов книги. Для анализа заработной платы отдела №4 понадобится ещё две таблицы: за последующие два месяца со структурой, аналогичной имеющейся, которые создадим, используя копирование, и сводная таблица с новой структурой. Таким образом, рабочая книга будет состоять из 4-х листов.

Для этого откроем книгу «Заработная плата отдела №4 за 4 квартал» по команде Файл> Открыть. Скопируем таблицу с 1-го листа два раза. Для этого щелкните в нижней части окна на ярлыке Лист 1, затем щелкните правой кнопкой мыши, появится контекстное меню. В нем выполните команду Переместить> Скопировать, в диалоговом окне выберите установить перед листом 2 и установите флажок Создавать копию. Перед листом 2 появится копия первого листа Лист 1(2). Создайте еще одну копию. В результате получится три одинаковых таблицы. Переименуем Лист 1, для этогона ярлыке Лист 1 в нижней части окна щелкните правой кнопкой мыши. В контекстном меню выполните команду Переименоват ь. Затем наберите на клавиатуре «Октябрь». Аналогично переименуйте Лист 1(2) на «Ноябрь» и Лист 1(3) на «Декабрь». Отредактируем полученные таблицы. В первой таблице в первую строке останется «Ведомость начисления заработной платы отдела №4 за октябрь 2011 г.», во второй таблице в первую строку введите – «Ведомость начисления заработной платы отдела №4 за ноябрь 2011 г.», в третьей – за декабрь. Во второй таблице и третьей таблицах в столбец «Оклад» внесите новые данные по своему усмотрению.

Сохраните рабочую книгу, щелкнув на кнопке Сохранить. Теперь в рабочей книге «Заработная плата отдела №4 за 4 квартал» подшиты три таблицы о заработной плате отдела за три месяца.

Объединение и связывание таблиц для формирования итоговой ведомости.

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

Создадим сводную таблицу для анализа данных за три месяца, связывающую показатели оклада за эти месяцы (таблица 2). Поместите эту таблицу на четвертом листе, дайте ему название «Анализ заработной платы за три месяца». Введите заголовок и шапку таблицы. Скопируйте содержимое столбца В с фамилиями сотрудников. Для этого выделите эти данные В4:В15, щелкните кнопку Скопировать на вставке Главная в разделе Буфер обмена, вернитесь на лист «Анализ заработной платы за три месяца» и в ячейке А3 щелкните кнопку Вставить.

Таблица 2 – Сводные показатели за 3 месяца

Консолидация данных в электронных таблицах позволяет объединять дан­ные из областей-источников и выводитьих в область назначения. При консолидации могут использоваться различные функции, такие как суммирования, расчета среднего значения и др. Кроме того, можно создавать связи с исходными данными в областях-источниках или не создавать. При создании связей область назначения будет автоматически обновляться при внесении изменений в областях-источниках. Проверим и сравним, как работает консолидация в Excel в двух случаях: без создания связей с исходными данными и с их созданием.

Консолидируем данные из столбцов J за три месяца без создания связей. Для этого на листе «Анализ заработной платы за три месяца» щелкните на ячейке В3, выберите вкладку Данные, раздел Работа с данными, выберите кнопку Консолидация. В появившемся окне «Функция» выберите функцию Сумма. Затем в поле Ссылка определите области-источники, которые нужно консолидировать. Для этого перейдите на лист «Октябрь» и выделите ячейки J4:J116. Потом в окне Консолидация нажмите кнопку Добавить. Перейдите на лист «Ноябрь», выделите такие же ячейки, щелкните Добавить в окне Консолидация. Аналогично для листа «Декабрь» повторите те же действия. Проверьте, снят ли флажок Создавать связи с исходными данными. Нажмите кнопку ОК. Теперь на листе «Анализ заработной платы за три месяца» появятся итоговые данные за три месяца.

Теперь консолидируем данные из столбцов J за три месяца, создав связи с исходными данными. Для решения этой задачи по аналогии скопируйте заголовок, шапку таблицы и исходные данные столбца А с листа «Анализ заработной платы за три месяца» на следующий лист, который назовите «Итог со связью». Выполните консолидацию данных по аналогии с предыдущей задачей. Затем установите флажок «Создавать связи с исходными данными» и нажмите ОК.

Вернитесь на лист «Октябрь». Внесите изменения в несколько любых ячеек соответствующих окладу. Проследите, как идет пересчет по формулам на этом листе. Запомните новые значения расчетов. Вернитесь на лист «Анализ заработной платы за три месяца» и убедитесь, что в нем нет изменений. Теперь посмотрите лист «Итог со связью», Вы увидите в нем изменения. Это результат установления связей.

3. Работа со структурированной таблицей. Сравните внешний вид полученных таблиц. В таблице с листа «Итог со связью» изменился вид экрана: в его левой вертикальной части появились символы структуры документа и некоторые строки стали невидимыми. Символы структуры бывают двух типов: кнопки с номерами уровней – кнопки 1 и 2, находящиеся в ле­вом верхнем углу экрана и знаки + (плюс) и/или - (минус), позволяющие соот­ветственно раскрывать или скрывать детали структурированного документа. Если щелкнуть на кнопке 2, то таблица «распахнется», предоставив воз­можность просмотреть консолидируемые данные за три месяца. Щелкнув по кнопке 1 можно скрыть исходные данные из таблиц-источников. Проверьте это на своей таблице. Щелкните по любому из знаков +(плюс). Результатом будет открытие одной из составляющих итоговой таблицы. Щелкнув по значку - (минус), вы скроете исходные данные из таблиц-источников.

Содержание отчета и его форма:

Ознакомьтесь с теоретическим обоснованием. Выполните все описанные пункты задания, письменно оформите отчет, который должен содержать:

- тему, цель лабораторной работы;

- виды ссылок в Excel;

- способы вызова Мастера формул;

- перечень изученных в данной работе возможностей Excel.

Контрольные вопросы и защита работы: