Задание 4. Связи между файлами и консолидация данных
Создайте новую книгу, а в ней таблицу «Отчёт о продажах». Введите исходные данные, как показано на рисунке.
Задайте формат ячеек Числовой. Для этого нужно нажать ПКМ на выделенном диапазоне, в выпадающем меню выбрать Формат ячеек – Денежный, число знаков после запятой 2 и обозначение рубля как показано на рисунке
Переименуйте лист в «Отчёт по отделам за 3 кв.»
Сохраните ваш файл с помощью Файл - Сохранить как. В появившемся окне Напишите в имени файла «3 квартал» и сохраните
Создайте таблицу как на рисунке, переименуйте лист «Отчёт по отделам за 4 кв.» и сохраните как файл «4 квартал»
Вернитесь к таблице «3 квартал»
Чтобы рассчитать прибыль, необходимо вычесть из доходов расходы. Рассчитайте расходы в строке «Прибыль» для каждого отдела.
Далее для столбца «Всего» рассчитайте суммы доходов и расходов по отделам.
Посчитайте Прибыль для столбца «Всего». Для этого нужно из Доходов вычесть Расходы. Сохраните таблицу «CTRL-S» или Файл –> Сохранить. Проделайте эти операции со второй таблицей и сохраните её.
Теперь сверните вкладку до оконного размера, нажав на кнопку «свернуть»
Создайте новую книгу с именем «Полугодие».
Теперь для рассчёта доходов, расходов и прибыли за полугодие, необходимо Сложить соответствующие параметры кварталов. Для этого расположите таблицы как на рисунке, затем, для рассчёта доходов за полугодие наберите «=» затем дважды щёлкните на общем доходе за 3 квартал, затем наберите «+» затем щелкните на общем доходе за 4 квартал, затем нажмите «Enter».
|
|
|
Рассчитайте доходы за полугодие, сложив доходы за 3 и за 4 квартал. Для этого в ячейке B2 введите знак равенства, а затем из разных книг выбирайте данные, которые будете складывать. В ячейке данные запишутся. Как ссылки, которые помимо названия ячеек будут иметь названия книг, откуда они взяты.
Проделайте аналогичные операции для расчётов расходов и прибыли
Сохраните таблицу полугодие, закройте её, и увеличьте доход 1го отдела за 3й квартал на 200 рублей.
Теперь откройте таблицу «Полугодие». Показания таблицы изменились, так как она теперь связана с другими таблицами.
Теперь сохраните и закройте таблицу «Полугодие». Верните предыдущее значение в доходы 1го отдела, сохраните таблицу и закройте все таблицы и программу. После этого откройте таблицу «Полугодие». Появится предупреждение.
Нажмите кнопку «Не обновлять».
Связи не обновятся. Для обновления нажмите на вкладку «Данные» и выберите меню «Изменить связи», в появившемся меню нажмите кнопку «Обновить».
|
|
|
Данные обновятся.
Теперь вновь откройте все три таблицы. Удалите расчёты из таблицы «Полугодие». Установите курсор в ячейку Дохода за полугодие и выполните команду «Консолидация».
В открывшемся окне
Выберите значения из итогового столбца таблицы «3 квартал»
Затем нажмите кнопку «Добавить» и выберите итоговые значения таблицы «4 квартал».
После этого нажмите «ОК». Произойдёт консолидированное суммирование данных.
Самостоятельная работа по лабораторной работе №2.
Создайте таблицу, как на рисунке ниже. Столбец «Категории» удобнее заполнить с помощью автозаполнения (после введения первой буквы слова оно автоматически прописывается полностью) или копированием.
Отсортировать значения в таблице так, чтобы сначала данный сортировались по категориям «от А до Я», а затем по цене «по убыванию». Для этого используйте знания, полученные ранее. Должно получиться так, как на картинке.
Скопируйте эту таблицу на другой лист, дайте листу название «Копирование». Вернитесь к предыдущему листу и проведите сортировку данных в таблице «от А до Я».
|
|
|
С помощью фильтра выведите данные о товарах из одной категории – Стройматериалы. Сохраните эту таблицу на лист Копирование под ранее сохранённой. Вернитесь на предыдущий лист. Удалите фильтр с таблицы.
С помощью нового фильтра выведите данные о товарах, стоимость которых больше 200 р. и меньше 1200 р. Скопируйте получившуюся таблицу на лист Копирование. Отсортируйте в ней данные по цене «От максимального к минимальному».
Произведите отбор только тех строк таблицы, которые содержат категорию «Инструменты» (в ячейке выглядит как ="=инструменты"), ИЛИ которые в столбце Цена содержат значение >1200 (для решения этой задачи применяется Расширенный фильтр). Критерии в случае ИЛИ должны размещаться под соответствующими заголовками (Категория и Цена) и должны располагаться на разных строках.
Скопируйте получившуюся таблицу на лист Копирование. Дайте диапазону ячеек, которые содержат Цены, имя и посчитайте сумму этих ячеек, используя имя в формуле.
Дата добавления: 2015-12-17; просмотров: 20; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!