Применение функции ЕСЛИ для вычисления Долга

Активизировать ячейку Н6. Вызвать функцию ЕСЛИ и заполнить значения параметров в окне функции:

В строке формул получим =ЕСЛИ (G6>0;G6;0). Формула

возвращает значение из ячейкиG6, если значение в ячейке G6больше 0. В противном случае возвращает число 0.

· Функция ЕСЛИ имеет следующий синтаксис:

ЕСЛИ(логическое выражение; значение если ИСТИНА; значение если ЛОЖЬ )

Расчёт значения поля Долг+Пеня

Рассчитать значения поля Долг+Пеня, используя имя Пеня. Присвоить ячейкам поля Долг+Пеня числовой формат с двумя знаками после запятой.

Для расчета поля Долг+Пеня активизируйте (нажмите) ячейку I6 ивведите выражение =Н6+Н6*Пеня. Значение Пеня вводить в выражение с помощью клавиши F3.

Вычисление удельного веса долга

· В ячейку С3 ввести текст «Общая сумма долга».

· ячейке Е3 рассчитать сумму по полю Долг+Пеня. Для этого:

активизировать ячейку Е3, вызвать функцию СУММ с помощью меню Формулы,разделБиблиотека функций,командаВставить функцию,найти функцию СУММ.В окне «Аргументы функции» выделить ячейки I6:I16, нажать ОК.

· Чтобы выделить ячейку Е3каким-либо цветом,надо сделать ее активной, затем в меню Главная,в разделеШрифтвыбрать цвет заливки.

· Задать ячейке Е3 имя Общ_сум (именованная ячейка).

· Активизировать ячейку J6 и ввести в неё формулу

= I6/Общ_сум.Общ_сумввести через клавишуF3из списка имён.

· Отформатировать данные столбца J как процентные с двумя знаками.

Сформированная таблица на листе Ведомость имеет вид:

Защита листа

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

Защита Книги (команда рядом с командой защиты листа) – ограничения доступа к книге, то есть установка запрета на создание новых листов, предоставление доступа определённым пользователям и многое другое.

Вставка новых листов

Вставить новый лист, расположив его за листом Ведомость, назвать новый лист Рабочая ведомость. Скопировать с листа Ведомость основную таблицу А5: j16 с листа Ведомость на лист Рабочая ведомость в ячейку А1 .

Сравнить значения полей таблицы на листе Ведомость стаблицей на листе Рабочая ведомость.

Дать имя Раб_вед блоку ячеек А1: j12на листе Рабочая ведомость.

Копирование листа Рабочая ведомость

Создать три листа с копиями Рабочая ведомость, разместив эти листы за листом Рабочая ведомость.

Для копирования кликнуть правой кнопкой мыши по ярлычку листа Рабочая ведомость,выбрать командуПереместить/ скопировать,установить флажок на Создать копию, указать место размещения листа (переместить в конец).

Новые листы назвать Сортировка,Итоги и Автофильтр,

Часть II

Сортировка таблицы

На листе Сортировка выполнить сортировку таблицы последовательно по трём полям: полю Адрес, затем по полю Наименование заказчика, затем по полю Период.

Поставить курсор в любую ячейку таблицы.

В меню Главная, в разделе Редактирование кликнуть Сортировка и Фильтр, выбрать Настраиваемая сортировка.

В окне «Сортировка»:

· в «Сортировать по» открыть список и выбрать Адрес,

· в «Сортировка» - выбрать Значения,

· в «Порядок» - выбрать От А до Я.

Щёлкнуть кнопку Добавить уровень (в левом верхнем углу). В появившейся строке:

· «Сортировать по» открыть список и выбрать Наименование заказчика,

· «Сортировка» - выбрать Значения,

· в «Порядок» - выбрать От А до Я.

Добавить третий уровень и выбрать: Период, Значения, От А до Я.

Получим:

Фильтрация данных

Перейти на лист Автофильтр. Отфильтровать данные в поле Период по значению 1 кв и 2 кв, в поле Долг вывести значения, не равныенулю.

Поставить курсор в любую ячейку таблицы.

В меню Главная, в разделе Редактирование кликнуть Сортировка и Фильтр, выбрать Фильтр. У каждого столбца таблицы появится стрелка. Раскроем список в столбце Период и выберем Текстовые фильтры, дальше Настраиваемые фильтры. Появится окно, в котором выполним установки:

Далее требуется из выбранных строк отобрать те, в которых Долг не равеннулю. В столбце Долг выберем из Числовые фильтры, дальше Настраиваемые фильтры. В окне Пользовательский автофильтр сделаем установки:

После этого получим :

Создадим новый лист Фильтр.

Из таблицы Рабочая_ведомость с помощью расширенного фильтра отобрать записи, у которых Период – 1 кв и Долг+Пеня>0. Результат получить в виде таблицы на листе Фильтр:

Код заказчика

Наименование заказчика

Долг+Пеня

Шапку таблицы результата создать копированием из таблицы Рабочая_ведомость на листе Фильтр, начиная с ячейки А5.

На листе Фильтр создадим диапазон условий в указанных ниже ячейках. Названия полей и значения периодов обязательнокопировать с листа Рабочая_ведомость. Если выделяемые блоки несмежные, то при выделении применить клавишу Ctrl.

Присвоим имя этому диапазону Условие_долг.

Выполним команду Данные/Фильтр/Дополнительно.

Появится диалоговое окно:

Исходный диапазон и диапазон условий задать с помощью клавиши F3.

Поместить полученные результаты на листе Фильтр в ячейку А5 (выделить ячейки А5:С5).

Получим результат:

Формирование итогов

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

При вычислении итогов таблица структурируется. Получим:

Чтобы отобразить на экране только итоговые данные, следует выполнить щелчок на кнопке 2 (второго уровня структуры), вследствие чего данные третьего уровня (исходные значения) будут скрыты.

Получим:

Для отображения исходных значений необходимо выполнить щелчок на кнопке 3 (третьего уровня) или выполнить в меню Данные, в разделе Структура команду Промежуточные Итоги, а в окне Промежуточные итоги нажать кнопку Убрать все.