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

Рис. 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 (значений)  – значение Размер прибыли . На вкладке Подписи данных устанавливаем флажок значения . На всех остальных вкладках данного окна оставляем значения параметров, предложенные по умолчанию