Вид рабочего листа изображен на рис.2.2.
Рис.2.2.Рабочий лист «Продажа фруктов» |
Создадим таблицу «Цены на фрукты» на чистом рабочем листе. Заполним ее теми же данными, что и на Рис.2.2. Затем оформим шапку следующей таблицы и скопируем содержимое столбцов «№», «Название» и «Единицы измерения». Все столбцы «К-во» заполняем произвольными данными.
Теперь осталось ввести формулы в столбцы «Сумма». Во всех трех таких столбцах это практически одна и та же формула, поэтому введем ее в ячейку Е16, а затем скопируем во все остальные ячейки. С помощью мастера функций заносим ПРОИЗВЕД(D16;D4). В этой формуле используются относительные ссылки на ячейки, которые при копировании в другие ячейки будут автоматически изменяться. Но нам требуется, чтобы буква D ячеек, в которых содержится цена, все время оставалась неизменной, поэтому здесь разумнее использовать абсолютную ссылку, которая не меняется при копировании. Добавим знак «$» перед буквой D. Получим ПРОИЗВЕД(D16;$D4). При копировании этой формулы автоматически будут меняться все ссылки, кроме буквы D.
Распространяем формулу на весь столбец Е, а затем копируем и распространяем на столбцы G и I. Затем вводим ИТОГО и с помощью кнопки Автосуммирование заносим формулу для подсчета суммарной стоимости в ячейку Е24, а затем копируем ее в ячейки G24 и I24. В формуле были использованы относительные ссылки, поэтому при копировании они автоматически изменятся.
Первая часть работы закончена. Теперь разнесем таблицу - справочник и таблицы продаж по разным рабочим листам. Конечно, при решении реальных задач надо сразу оформлять таблицы на нужных рабочих листах, чтобы не делать лишней работы, но в данном случае преследуется цель научиться вводить различные виды ссылок на ячейки.
Изменим заголовок активного рабочего листа. Например, дадим ему заголовок «Цены». Еще один чистый рабочий лист озаглавим «Продажи».
Копируем на рабочий лист «Продажи» только две таблицы продаж. При этом в ячейках первой таблицы, содержащих формулы, появляется сообщение о недопустимой ссылке. Исправим в любой из этих ячеек $D4 на ‘Цены’!$D4. Это означает ссылку на ячейку $D4, находящуюся на рабочем листе «Цены». Распространяем исправленную формулу на все остальные ячейки. Больше никаких исправлений не требуется, т.к. все остальные формулы содержат ссылки на ячейки, находящиеся на активном рабочем листе.
Осталось только отобразить содержимое таблицы продаж в виде диаграммы.
В Excel можно построить два типа диаграмм: внедренные диаграммы, которые создаются на рабочем листе рядом с таблицами, и диаграммы на отдельных листах диаграмм. Оба типа диаграмм легко строятся при помощи мастера диаграмм.
Создадим внедренную диаграмму на одном рабочем листе с таблицей продаж. Но немного преобразуем таблицу, т.к. в исходном состоянии она плохо пригодна для создания диаграммы. Скопируем таблицу на чистый рабочий лист и удалим столбцы с суммами и другие лишние столбцы, а также часть строк. На рис. 2.3 изображена преобразованная таблица по которой будет строиться диаграмма.
Продажа фруктов в первом квартале
Рис.2.3. Таблица для построения диаграммы
Построение диаграммы по такой таблице - дело несложное. Для этого сначала выделим таблицу. Затем выполняем команду вставка/Диаграмма или нажимаем кнопку Мастер диаграмм на стандартной панели инструментов. Указатель мыши примет форму тонкого креста с диаграммой под ним. Перетаскиваем указатель мыши по ячейкам, определяя место и размер будущей диаграммы, а затем отпускаем кнопку мыши.
После определения области диаграммы откроется первое окно Мастера диаграмм. В нем нужно выбрать тип диаграммы из имеющихся типов. Для перехода ко всем последующим окнам нажимаем кнопку Далее. Второе окно позволяет уточнить и в случае необходимости откорректировать диапазон данных, по которым будет строиться диаграмма, третье окно - ввести заголовок диаграммы и подписи к осям координат, а также установить другие параметры диаграммы. Выберите тот тип и формат, который соответствует рисунку 2.4 (объемная гистограмма).
В четвертом окне можно задать вывод диаграммы на существующий или отдельный новый лист. После закрытия последнего диалогового окна Excel внедряет созданную диаграмму в выделенную область рабочего листа. Если нас не устраивают размеры или расположение диаграммы, это легко поправить с помощью мыши. Любой элемент диаграммы может быть изменен. Для этого нужно двойным щелчком на соответствующем элементе вызвать диалоговое окно и внести изменения. Также можно щелкнуть по соответствующему элементу правой кнопкой и выбрать в контекстном меню пункт Формат.
В результате получили диаграмму, изображенную на рис.2.4.
Рис.2.4. Диаграмма продажи фруктов |