Основные принципы формирования рабочей книги
Для организации работы в электронных таблицах Excel сформируйте макет своей будущей рабочей книги, присвойте файлу имя Расчет дохода и сохраните в своей папке на диске D: .
При создании таблиц необходимо учитывать то, что данные делятся на две категории: исходные и расчетные.
Исходные данные вводятся непосредственно с клавиатуры, формируя исходную базу данных.
Расчетные являются результатом формульной зависимости связанных ячеек непосредственно с ячейками исходных (первоначальных) данных, либо с ячейками промежуточных расчетов, либо и тех и других.
Формирование рабочей книги
Название рабочего листа должно отражать его смысловое содержание. Для облегчения дальнейших действий с таблицами на разных рабочих листах, их необходимо переименовать.
Задание 1. Переименуйте рабочие листы:
Лист1 – Закупка продукции, Лист2 – Реализация продукции, Лист3 – Цена продукции, Лист4 – Выручка от реализации, Лист5 – Доход от реализации.
Если в новой рабочей книге недостаточно рабочих листов добавьте новые рабочие листы, для этого воспользуйтесь одним из способов 2.
Таким образом, новые имена рабочих листов "подскажут" Вам о своем содержании, что позволит быстрее ориентироваться в имеющихся данных для получения информации и формирования отчетов.
Все задания нужно выполнять на разных рабочих листах в одной рабочей книге.
Создание и заполнение таблиц данными. Ввод формул
Задание 2. На рабочем листе "Закупка продукции" создайте таблицу и внесите данные, как показано на рис. 1.
Для оформления общего заголовка таблицы воспользуйтесь кнопкой на панели инструментов Форматирование "Объединить и поместить в центре", для размещения заголовков таблицы во всех столбцах следует выделить всю 3-ю строку, затем выберите команду Формат/Ячейки, откройте закладку Выравнивание, выберите в полях По вертикали - По центру, По горизонтали - По центру, установите флажок в поле Переносить по словам, затем нажмите OK.
Перед вводом данных задайте форматы ячеек таблицы - Числовой, число десятичных знаков 0, выравнивание - по центру. Наименование продукции - формат Текстовый. Выравнивание - по левому краю (отступ) – 0,5.
В ячейках H4:Н9 - введите формулы суммирования по строкам.
Рис. 23. Исходная таблица на рабочем листе Закупка продукции
Задание 3. На рабочем листе "Реализация продукции" внесите исходные данные в таблицу и оформите, как показано на рисунке 24.
Рис. 24. Исходная таблица на рабочем листе Реализации продукции
Задание 4. На рабочем листе "Цена продукции" создайте и заполните две таблицы - Расходы на закупку и Расчет цен как показано на рисунке 25.
Оформление заголовков таблиц выполняется аналогично предыдущим заданиям.
Для ячеек в таблице Расходы на закупку задайте форматы:
А4:А10 - текстовый;
В4:В9 - денежный, число десятичных знаков - 2, обозначение - р.
C4:C9 - числовой, число десятичных знаков - 0.
D4:D10 - денежный, число десятичных знаков - 2, обозначение - р.
В ячейки А4:С9 внесите данные с клавиатуры. В ячейку D4 введите формулу = В4*С4, подтвердите формулу - нажмите клавишу Enter.
Чтобы не повторять набор формулы в ячейках D5, D6, D7, D8, D9. Скопируйте в эти ячейки содержимое ячейки D4 вместе с формулой.
Для снятия команды копирования с ячейки D4 нажмите клавишу Esc.
Обратите внимание на изменение ссылок в формулах суммирования, т.к. они относительные.
Задайте форматы ячеек в таблице Расчет цен:
А15:А21 - текстовый;
В15:В20 - денежный, число десятичных знаков - 2, обозначение - р.
C15:C20 - процентный, число десятичных знаков - 0.
D15:D21 - денежный, число десятичных знаков - 2, обозначение - р.
В ячейки А15:А20, В15:В18 и С15:С20 внесите данные с клавиатуры.
В ячейку D15 введите формулу = В15*С15+В15, подтвердите формулу нажатием клавиши Enter.
Аналогично предыдущей таблице скопируйте содержимое ячейки D15 с формулой в ячейки D16, D17, D17, D18, D19.
Рис.25. Общий вид рабочего листа Цена продукции
Примените к таблицам обрамление, как показано на рисунке 25.
Задание 5. Создайте и заполните таблицы на листе Выручка от реализации, как показано на рисунке 26.
В таблицах задайте форматы ячеек, выравнивание данных в них и обрамление ячеек, как показано на рисунке 26.
Обратите внимание, что в таблице Выручка от реализации за 1 квартал число, отражающее количество проданной продукции, совпадает с количеством проданной продукции на листе Реализация продукции, но только за январь, февраль и март месяцы.
Рис. 26. Общий вид рабочего листа Выручка от реализации
Задание 6. Заполните и оформите таблицы на листе Доход от реализации (рис. 27).
Рис. 27. Общий вид рабочего листа Доход от реализации
Обратите внимание, что в таблицах добавились данные столбца Цена закупки из таблицы Расчет цен расположенной на листе Цена реализации.
В таблицах задайте форматы ячеек, выравнивание данных в них и обрамление ячеек, как показано на рисунке 5.
Внесите данные в таблицы.
В таблице Доход от реализации за полугодие количество проданного товара, число в столбце Н, совпадает с суммой за полугодие на листе Реализация продукции.
В таблице Доход от реализации за 1 квартал количество проданного товара также составляет сумму, но только за январь, февраль и март месяцы.
В ячейках E4:E9 находится формула =D4*(B4-C4).
В строке ИТОГО в ячейке E10 находится формула, суммирующая ячейки E4:E9.
В ячейках E15:E20 находится формула =D15*(B15-C15).
В строке ИТОГО в ячейке E21 находится формула, суммирующая ячейки E15:E20.
После оформления рабочих листов, создания таблиц и внесения в них данных, можно считать работу полностью выполненной.
Однако полученные таблицы представляют собой разрозненные источники данных и не взаимосвязаны между собой, что не позволяет использовать их как единую базу данных для получения различных отчетов.
Убедитесь в этом на примерах:
Измените наименование продукции – Пшеница 3 класс в ячейке А4 на листе Закупка продукции на другое – Ячмень фуражный. Проверьте, произошло изменение наименования данной продукции на остальных листах или оно осталось прежним?
Измените на листе Реализация продукции количество проданного гороха в марте с 10 на 15. Проверьте, произошли соответствующие изменения на других рабочих листах?
Измените на листе Цена продукции в таблице Расходы на закупку в графе Цена закупки (ячейка В4) число 2 600,00 р. на новую цену закупки – 3 700,00 р. Проверьте, произошли соответствующие изменения в соседней таблице Расчет цен, и на других рабочих листах где используется данный показатель?
Таким образом, при изменении содержимого в исходной ячейке, не происходит никаких изменений в ячейках содержащих аналогичную информацию в других таблицах, как бы "логически" связанных между собой или как кажется на первый взгляд имеющих общий исходный компонент.
Изменения происходят только в тех ячейках, которые имеют прямую связь с исходной ячейкой например, при выполнении п.3 на листе Цена продукции изменяется ячейка D4, т.к. содержит формулу со ссылкой на ячейку В4, и как следствие изменяется итоговая сумма в ячейке D10.
Возникает необходимость установить связь между таблицами, расположенными как на одном рабочем листе, так и на разных рабочих листах.