Книга: Самоучитель работы на компьютере: быстро, легко, эффективно

6.9. Демонстрация приемов работы на конкретном примере

6.9. Демонстрация приемов работы на конкретном примере

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

6.9.1. Постановка условий задачи

Допустим, что нам требуется создать таблицу, отражающую прибыль от продаж в разрезе видов продукции за последний квартал 2008 года. При этом в строках будет отражена информация о видах продукции, а по столбцам – о периодах времени. По каждой товарной позиции мы должны отразить сумму налога, ставка которого равняется 15 % от прибыли.

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

Для примера возьмем такие товарные группы: Напитки , Печенье и Крупа . В группу Напитки включим следующие позиции: Фанта , Кока-кола и Спрайт , в группу Печенье – позиции Василек , Снежок и Чайное , а в группу Крупа – позиции Гречка , Пшено и Рис .

Перечисленные позиции разместим по строкам таблицы. По столбцам же отразим прибыль и налог за Октябрь , Ноябрь и Декабрь по отдельности.

Начнем работу с рисования таблицы – этому и посвящен следующий раздел.

6.9.2. Построение таблицы для расчетов

Чтобы решить имеющуюся задачу, нам необходима таблица, состоящая из 9 строк и 8 столбцов. Напомним, что создание таблиц осуществляется с помощью панели Граница , которая вызывается командой главного меню Вид ? Панели инструментов ? Граница .

Вначале создадим «шапку» таблицы. Для этого выберем в инструментальной панели Граница обычную сплошную линию, включим режим Сетка по границе рисунка и очертим мышью диапазон из двух строк и восьми столбцов так, как это показано на рис. 6.30.

Рис. 6.30. Первый этап создания таблицы

Далее выполним форматирование ячеек «шапки» таблицы, и введем названия ее столбцов.

Выделим ячейки В5 и В6 , затем выполним команду главного меню Формат ? Ячейки и в открывшемся окне перейдем на вкладку Выравнивание (рис. 6.31).

Рис. 6.31. Форматирование ячеек

На этой вкладке в поле по горизонтали из раскрывающегося списка выберем значение по центру , в поле по вертикали – значение по высоте , установим флажок объединение ячеек и нажмем кнопку ОК . После этого в объединенную ячейку введем с клавиатуры название столбца – Группа товаров . Результат выполненных действий показан на рис. 6.32.

Рис. 6.32. Формирование заголовка столбца

Теперь точно так же объединим и отформатируем ячейки С5 и С6 , и введем в объединенную ячейку заголовок столбца Наименование товара . В данном случае придется немного расширить границы столбца, чтобы его заголовок отображался полностью. Далее нужно выделить ячейки D6 , E6 , F6 , G6 , H6 и I6 , выполнить команду Формат ? Ячейки , и в открывшемся окне на вкладке Выравнивание (см. рис. 6.31) в поле по горизонтали установить значение по центру , после чего нажать ОК . Затем в ячейках D6 , F6 и H6 введем заголовок столбца Прибыль , а в ячейках Е6 , G6 и I6 – заголовок столбца Налог . В настоящий момент шапка таблицы будет выглядеть так, как показано на рис. 6.33.

Рис. 6.33. Заполнение «шапки» таблицы

Теперь выделим ячейки D5 и Е5 , выполним команду главного меню Формат ? Ячейки и в открывшемся окне на вкладке Выравнивание в поле по горизонтали из раскрывающегося списка выберем значение по центру , установим флажок объединение ячеек и нажмем кнопку ОК . Затем в объединенной ячейке с клавиатуры введем значение Октябрь . Аналогичную операцию выполним с ячейками F6 и G6 , а затем – с ячейками H6 и I6 ; в первом случае в объединенную ячейку введем значение Ноябрь , во втором случае – Декабрь . На этом создания шапки таблицы можно считать завершенным; результат выполненных действий показан на рис. 6.34.

Рис. 6.34. Готовая «шапка» таблицы

Идем далее. В инструментальной панели Граница (см. рис. 6.21) включим режим Сетка по границе рисунка , и очертим мышью диапазон оставшейся части таблицы на девять строк вниз. Затем в предназначенных для этого ячейках введем названия групп товаров, товарных позиций и размер прибыли от продаж по каждой товарной позиции в каждом месяце. Чтобы наша таблица выглядела эргономичней, оформим ее «шапку» жирной границей, выбрав ее в раскрывающемся списке инструментальной панели Граница (не забываем перед этим выделить соответствующий диапазон и включить режим Сетка по границе рисунка ). После выполнения всех перечисленных действий таблица примет вид, как показано на рис. 6.35.

Рис. 6.35. Исходные данные в таблице

Теперь мы можем выполнять настройку формул и рассчитывать промежуточные итоги. Об этом пойдет речь в следующем разделе.

6.9.3. Настройка формул и расчет итогов

Согласно условиям нашей задачи, прибыль облагается налогом по ставке 15 %. Чтобы произвести автоматический расчет, введем в ячейку Е7 формулу =D7/100*15 . После нажатия клавиши Enter в ячейке Е7 отобразится автоматически рассчитанная сумма налога. Затем установим курсор в ячейку Е7 , скопируем ее содержимое (формулу) в буфер обмена, выделим диапазон Е8:Е15 и нажмем клавишу Enter – в результате будет рассчитана сумма налога во всех выделенных ячейках. Обратите внимание – будет скопирована корректно, с учетом координат ячеек: например, в ячейке Е9 она будет выглядеть так: =D9/100*15 , и т. д. Затем аналогичным образом вставим формулы в диапазоны G7:G15 и I7:I15 – результат выполненных действий показан на рис. 6.36.

Рис. 6.36. Расчет суммы налога по всем товарам

Итак, мы получили сумму налога на прибыль по каждой товарной позиции в отдельности. Теперь при каждом изменении прибыли соответствующим образом автоматически изменится сумма исчисленного по ней налога.

Следующий этап – это расчет промежуточных и общих итогов. Полученный результат даст нам возможность увидеть размер прибыли и налога по каждой товарной группе, а также общую сумму прибыли и налога в каждом месяце.

Выделяем всю таблицу, выполняем в главном меню команду Данные ? Итоги и положительно отвечаем на появившийся запрос программы – в результате отобразится окно настройки расчета промежуточных итогов (рис. 6.37).

Рис. 6.37. Настройка расчета промежуточных итогов

В данном режиме производим настройку следующим образом.

1. В поле При каждом изменении в из раскрывающегося списка выбираем значение Группа товаров .

2. В поле Операция из раскрывающегося списка выбираем значение Сумма .

3. В поле Добавить итоги по устанавливаем следующие флажки: Октябрь , Столбец Е , Ноябрь , Столбец G , Декабрь , Столбец I .

4. В нижней части окна устанавливаем флажок Итоги под данными .

После выполнения указанных действий и нажатия в данном окне кнопки ОК таблица примет вид, как показано на рис. 6.38.

Рис. 6.38. Расчет промежуточных и общих итогов

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

При любом последующем изменении исходных данных данные в таблице будут соответствующим образом автоматически пересчитаны.

6.9.4. Построение диаграммы по полученным данным

Допустим, нам требуется наглядно представить динамику прибыли в последнем квартале года. Для этого необходимо построить диаграмму на основании имеющихся данных.

В нашей таблице выделим три ячейки: D19 , F19 и H19 (это ячейки с итоговыми суммами выручки в каждом месяце квартала). Затем нужно выполнить команду главного меню Вставка ? Диаграмма – в результате на экране откроется окно мастера диаграмм, которое показано на рис. 6.23. В этом окне выполняем указанный ниже порядок действий.

1. На первом этапе создания диаграммы выберем стандартную круговую диаграмму.

2. Параметры, предложенные по умолчанию на втором этапе, оставляем без изменений (поле Диапазон на вкладке Диапазон данных будет заполнено автоматически, поскольку предварительно мы выделили ячейки с исходными данными).

3. На третьем этапе на вкладке Заголовки в соответствующем поле укажем название диаграммы – Динамика прибыли . Параметры, предложенные по умолчанию на вкладке Легенда , оставляем без изменения. На вкладке Подписи данных устанавливаем флажки значения и доли .

4. На заключительном этапе выполним настройку, при которой диаграмма будет расположена на отдельном специально созданном листе. Для этого установим переключатель в положение отдельном , и в расположенном правее поле с клавиатуры введем название листа диаграммы (можно оставить и название, которое предлагается по умолчанию).

После нажатия кнопки Готово откроется лист с диаграммой, которая изображена на рис. 6.39.

Рис. 6.39. Построение круговой диаграммы

Здесь в графическом представлении отражена прибыль в процентном и денежном выражении в каждом месяце квартала.

Теперь сформируем гистограмму, которая будет содержать информацию о прибыли по каждому виду товаров в первом месяце квартала. Действовать будем следующим образом.

1. Выделим три диапазона ячеек: C7:D9 , C11:D13 и C15:D17 (рис. 6.40).

Рис. 6.40. Выделение исходных данных для диаграммы

2. В меню Вставка выполним команду Диаграмма .

3. На первом этапе создания диаграммы откроем вкладку Стандартные и щелчком мыши выберем в списке обычную гистограмму и нажимаем Далее .

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

5. Открываем вкладку Заголовки , и в поле Название диаграммы вводим значение Прибыль по видам товаров за октябрь , в поле Ось Х (категорий)  – значение Виды товаров , а в поле Ось Y (значений)  – значение Размер прибыли . На вкладке Подписи данных устанавливаем флажок значения . На всех остальных вкладках данного окна оставляем значения параметров, предложенные по умолчанию

6. На заключительном этапе укажем, что мы хотим поместить диаграмму на отдельный рабочий лист. Для этого установим переключатель в положение отдельном , после чего в расположенном правее поле можно ввести название листа диаграммы (либо оставить предложенное по умолчанию).

Нажимаем кнопку Готово – результат показан на рис. 6.41.

Рис. 6.41. Готовая гистограмма

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

Оглавление книги