Смекни!
smekni.com

Методические указания к лабораторным работам По дисциплине (стр. 2 из 10)

В ячейке ВЗ файла «Полугодие» формула для расчета полугодо­вого дохода имеет вид:

= '[1 квартал.хls]Лист1'!$В$3 + '[2 квартал.хls]Лист1'!$В$3.

Аналогично рассчитайте полугодовые значения Расходов и Прибыли, используя данные файлов «1 квартал» и «2 квартал». Результаты работы представлены на рис. 2.1. Сохраните текущие результаты расчетов.

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

Задание 2.1. Обновление связей между файлами.

Порядок работы

Закройте файл «Полугодие» предыдущего задания.

Измените значение «Доходы» в файлах первого и второго квартала, увеличив значения на 100 р.:

Доходы 1 квартала = 334,58 р.;

Доходы 2 квартала = 552,6 р.

Сохраните изменения и закройте файлы.

Откройте файл «Полугодие». Одновременно с открытием
файла появится окно с предложением обновить связи. Для обновления связей нажмите кнопку Да. Проследите, как изменились данные файла «Полугодие» (величина «Доходы» должна увели­читься на 200 р. и принять значение 887,18 р.).

Рис. 2.2. Ручное обновление связей между файлами

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

4. Изучим процесс ручного обновления связи. Сохраните файл «Полугодие» и закройте его.

5. Вновь откройте файлы первого и второго кварталов и измените исходные данные «Доходы», увеличив еще раз значения на 100 р.:

Доходы 1 квартала = 434,58 р.;

Доходы 2 квартала = 652,6 р.

Сохраните изменения и закройте файлы.

6. Откройте файл «Полугодие». Одновременно с открытием файла появится окно с предложением обновить связи, нажмите кнопку Нет. Для ручного обновления связи в меню Правка выбе­рите команду Связи, появится окно (рис. 2.2), в котором перечислены все файлы, данные из которых используются в активном
файле «Полугодие».

Расположите его так, чтобы были видны данные файла «Полу­годие», выберите файл «1 квартал», нажмите кнопку Обновить и проследите, как изменились данные файла «Полугодие». Анало­гично выберите файл «2 квартал» и нажмите кнопку Обновить. Проследите, как вновь изменились данные файла «Полугодие».

Примечание. При изменении данных в нескольких исход­ных файлах обновление связи производится для каждого файла.

Задание 2.2. Консолидация данных для подведения итогов по таблицам данных сходной структуры.

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

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

Порядок работы

1. Откройте все три файла Задания 2 и в файле «Полугодие» в колонке «В» удалите все численные значения данных. Установи­те курсор в ячейку ВЗ.

2. Выполните команду Данные/'Консолидация (рис. 2.3). В появив­шемся окне Консолидация выберите функцию — «Сумма».

В строке «Ссылка» сначала выделите в файле «1 квартал» диа­пазон ячеек ВЗ:В5 и нажмите кнопку Добавить, затем выделите в файле «2 квартал» диапазон ячеек ВЗ:В5 и опять нажмите кнопку Добавить (см. рис. 2.3). В списке диапазонов будут находиться две области данных за первый и второй кварталы для консолидации. Далее нажмите кнопку ОК, произойдет консолидированное сум­мирование данных за первой и второй кварталы.

Вид таблиц после консолидации данных приведен на рис. 2.4.

Рис. 2.4. Таблица «Полугодие» после консолидированного суммирования

Задание 2.3. Консолидация данных для подведения итогов по таблицам неоднородной структуры.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу. Наберите отчет по отделам за третий квартал по образцу (рис. 2.5). Произведите расчеты и со­храните файл с именем «3 квартал».

2. Создайте новую электронную книгу. Наберите отчет по отде­лам за четвертый квартал по образцу (рис. 2.6). Произведите рас­четы и сохраните файл с именем «4 квартал».

3. Создайте новую электронную книгу. Наберите название таб­лицы «Полугодовой отчет о продажах по отделам». Установите курсор в ячейку A3 и проведите консолидацию за третий и чет­вертый кварталы по заголовкам таблиц. Для этого выполните ко­манду Данные/Консолидация. В появившемся окне Консолидация данных сделайте ссылки на диапазон ячеек АЗ:Е6 файла «3 квар­тал» и A3:D6 файла «4 квартал» (рис. 2.7). Обратите внимание, что интервал ячеек включает в себя имена столбцов и строк таб­лицы.

Рис. 2.5. Исходные данные для третьего квартала Задания 2.2

Рис. 2.6. Исходные данные для четвертого квартала Задания 2.2

Рис. 2.7. Консолидация неоднородных таблиц

В окне Консолидация активизируйте опции (поставьте галочку): подписи верхней строки; значения левого столбца; создавать связи с исходными данными (результаты будут не константами, а формулами).

Рис. 2.8. Результаты консолидации неоднородных таблиц

После нажатия кнопки ОК произойдет консолидация данных (рис. 2.8). Сохраните все файлы в папке вашей группы.

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

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Что такое автозаполнение?

2. Какие способы объединения нескольких исходных таблиц в одну вам известны?

3. Что такое консолидация данных?

ЛАБОРАТОРНАЯ РАБОТА 2

Тема: ОТНОСИТЕЛЬНАЯ И АБСОЛЮТНАЯ АДРЕСАЦИЯ В ТАБЛИЧНОМ ПРОЦЕССОРЕ MS EXCEL

Цель. Изучение информационной технологии приме­нения относительной и абсолютной адресации для финансовых расчетов.

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

Исходные данные представлены на рис. 1.1, результаты рабо­ты — на рис. 1.2 и 1.3.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.

2. Создайте таблицу расчета заработной платы по образцу (см. рис. 2.1).

Введите исходные данные — Табельный номер, ФИО и Ок­лад, % Премии = 27 %, % Удержания = 13 %.

Выделите отдельные ячейки для значений % Премии (D4) и % Удержания (F4).

Рис. 1.1. Исходные данные для Задания 1

3. Произведите расчеты во всех столбцах таблицы.

При расчете Премии используется формула Премия = Оклад * % Премии. В ячейке D5 наберите формулу =$D$4xC5 (ячейка D4 используется в виде абсолютной адресации). Скопируйте на­бранную формулу вниз по столбцу автозаполнением.

Краткая справка. Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул в расчетную ячейку окрашенная ячейка с константой будет вам напоминани­ем, что следует установить абсолютную адресацию (набором сим­вола $ с клавиатуры или нажатием клавиши [F4]).

Формула для расчета «Всего начислено»:

Всего начислено = Оклад + Премия.

При расчете Удержания используется формула:

Удержания = Всего начислено х % Удержаний.

Для этого в ячейке F5 наберите формулу: =$F$4xE5. Формула для расчета столбца «К выдаче»:

К выдаче = Всего начислено — Удержания.

4. Рассчитайте итоги по столбцам, а также максимальный, ми­нимальный и средний доходы по данным колонки «К выдаче» (Вставка/Функция/категория — Статистические функции).

5. Переименуйте ярлычок Листа 1, присвоив ему имя «Зарпла­та октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой Переиме­новать контекстного меню ярлычка, вызываемого правой кноп­кой мыши. Результаты работы представлены на рис. 2.2.

Краткая справка. Каждая рабочая книга Excel может со­держать до 255 рабочих листов. Это позволяет, используя несколь­ко листов, создавать понятные и четко структурированные доку­менты, вместо того чтобы хранить большие последовательные наборы данных на одном листе.

6. Скопируйте содержимое листа «Зарплата октябрь» на новый
лист (Правка/Переместить/Скопировать лист). Можно воспользоваться командой Переместить/Скопировать контекстного меню ярлычка. Не забудьте для копирования поставить галочку в окне Создавать копию.

Краткая справка. Перемещать и копировать листы мож­но, перетаскивая их корешки (для копирования удерживайте на­жатой клавишу [Ctrl]).