Например, формула =ЕСЛИ ( G6>0;G6;0) возвращает значение из ячейки G6, если значение в ячейке G6 больше 0. В противном случае возвращает число 0.
11. Значение Пеня представим как именованную ячейку. Выделим ячейку С3 и в ыполним команду Вставка / Имя / Присвоить. Введём имя Пеня. Рассчитаем значения поля Долг+Пеня. Присвоим ячейкам поля Долг+Пеня числовой формат с двумя знаками после запятой. Для расчёта поля Долг+Пеня Активизируем ячейку I6. Введём выражение =Н6+Н6* Пеня. Значение Пеня вводить в выражение с помощью клавиши F3.
12. Значения всех полей заполненной таблицы расположим по центру с помощью кнопки панели инструментов.
13. В ячейку F18 листа Ведомость введём фразу Общая сумма долга. В ячейке I18 рассчитаем сумму по полю Долг+Пеня. Выделим ячейку I18 любым цветом.
Объединим ячейки F18:G18. Для этого выделим ячейки F18:G18. Выполним команду Формат / Ячейки… Закладка Выравнивание. Установим флажок на объединение ячеек. В ячейке I18 рассчитаем сумму по полю Долг+Пеня с помощью кнопки панели инструментов 
. Для этого активизируем ячейку I18, нажмём кнопку 
, а затем Enter. Чтобы выделить ячейку I18 каким-либо цветом,надо сделать её активной, затем воспользоваться кнопкой панели инструментов (выделение цветом). Выделить ячейку I18 любым цветом.
Сформированная таблица на листе Ведомость имеет вид:
14. Защитить лист Ведомость от изменений можно с помощью команды Рецензирование /Защитить лист…
15. Вставить новый лист, расположив его за листом Ведомость, назвать новый лист Рабочая ведомость. Скопировать с листа Ведомость основную таблицу (А5:I16) на лист Рабочая ведомость в ячейку А1.
Проверить правильность скопированных данных в поле Долг+Пеня. Сравнить значения полей этой и таблицы на листе Ведомость.
|
|
16. Дать имя Ведомость_список блоку ячеек А1:I12 на листе Рабочая ведомость.
17. Скопировать лист Рабочая ведомость, на вновь созданные листы Сортировка,Итоги и Автофильтр, разместив эти листы за листом Рабочая ведомость. Применить команду Правка/Переместить /Скопировать лист… В окне установить флажок на Создать копию.
Сортировка списка
1. Перейдём на лист Сортировка. Отсортируем список по полю Адрес, затем по полю Наименование заказчика, затем по полю Период. В окне Сортировка сделаем установки:
Получим:
Формирование итогов
2. Перейдём на лист Итоги. Получим итоги (операция сумма) по полям Сумма к выплате,Оплачено и Долг для каждого периода. Для этого отсортируем таблицу по полю Период. Затем выполним команду
Данные/Итоги. Появится окно промежуточные итоги:
Сделаем в окне указанные установки. Нажмём ОК.
Получим:
При вычислении итогов таблица структурируется.
Чтобы отобразить на экране только итоговые данные, следует выполнить щелчок на кнопке второго уровня структуры, вследствие чего данные третьего уровня (исходные значения) будут скрыты. Получим:
Для восстановления отображения исходных значений необходимо выполнить щелчок на кнопке третьего уровня или выполнить команду Данные / Итоги, а в окне Промежуточные итоги нажать кнопку Убрать все.
Внимание! Перед вычислением итогов надо отсортировать данные в таблице по тому полю, по которому создаются группы для вычисления итогов. В противном случае итоговые значения будут определены для каждой строки.
|
|
Построение диаграмм
3. Построить круговую диаграмму, отобразив на ней итоговые значения поля Сумма к выплате по кварталам.
Чтобы построить круговую диаграмму, надо выделить на полученной таблице столбцы Период и Сумма к выплате.
Выполнить команду Вставка/Диаграмма или нажать кнопку Мастер диаграмм.
В появившемся окне выбрать тип диаграммы – Круговая. Нажать кнопку Далее. В следующем окне ещё раз нажать кнопку Далее. В вести название диаграммы Сумма к выплате.
Открыть закладку Подписи данных и установить флажок доли. После этого каждый сектор будет подписан своим числовым значением в процентах.
После нажатия в этом окне кнопки Далее появится окно, в котором указать, где будет расположена диаграмма:
Расположим полученную диаграмму на отдельном листе. Для этого установим флажок отдельном.
Лист получит имя Диаграмма 1.
Нажать кнопку Готово.
На листе Диаграмма 1 получим:
Можно изменить шрифт текста на диаграмме, предварительно выделив его. Двойной щелчок по сектору даёт возможность выдвинуть его с помощью мыши. Переименуем лист Диаграмма 1 в Круговая.
4. Построим гистограмму, изобразив на ней по периодам Сумму к выплате, Оплачено и Долг. Порядок созданиядиаграммы как в предыдущем задании. Однако здесь удобно скрыть столбец Разница на листе Итоги. Для этого активизируем любую ячейку этого столбца. Выполним команду Формат/Столбец/Скрыть. Выделим необходимые столбцы:
Построим гистограмму:
Чтобы получить смешанную диаграмму и представить Долг в виде линейного графика, надо щёлкнуть по одному из столбцов Долга.
|
|
Затем выполнить команду Диаграмма/Тип Диаграмм. Выбрать закладку Нестандартные, а после этого из списка выбрать График I гистограмма. Диаграмма примет вид смешанной.
Для линейного графика удобно создать дополнительную ось Y-ов справа на графике. Это тем более необходимо, если значения для линейного графика несоизмеримы со значениями других столбцов гистограммы. Щёлкнуть по линейному графику и выполнить команду Диаграмма/Параметры диаграммы. В открывшемся окне:
открыть закладку Оси и установить флажок ось Y(значений) в группе по вспомогательной оси. После этого на графике появится дополнительная ось Y-ов (справа). Нажать кнопку ОК.
На столбцы можно нанести соответствующие им значения. Для этого дважды щёлкнуть по одному из столбцов. В появившемся окне открыть закладку Подписи данных:
Установим флажок значения. Нажать ОК.
Можно изменить фон диаграммы. Для этого щёлкнуть правой кнопкой мыши по полю диаграммы.
Появится контекстное меню:
Выбрать из списка Формат области построения…
В появившемся окне выбрать заливка – обычная.
Получим фон диаграммы - белый:
Фильтрация данных
5. Перейти на лист Автофильтр. Отфильтровать данные в поле Период по значению 1 кв и 2 кв, в поле Долг вывести значения, не равныенулю.
Сделать активной любую ячейку таблицы. Выполнить команду Данные /Фильтр/Автофильтр. У каждого столбца таблицы появится стрелка. Раскроем список в столбце Период и выберем Условие. Появится окно, в котором выполним установки:
Далее требуется из выбранных строк отобрать те, в которых Долг не равеннулю. Активизируем ячейку полученной таблицы. Выполним команду Данные /Фильтр/ Автофильтр. В столбце Долг выберем из списка Условие. В окне Пользовательский автофильтр сделаем установки:
После этого получим:
