MS Excel работа с электронными таблицами Задача

MS Excel работа с электронными таблицами

Задача

Подзадача 1. Работа с клиентами. 1. Ведение списка клиентов 2. Ведение списка договоров

Подзадача 2. Работа с отгрузками и оплатами. Для каждой отгрузки (ТТН) на отдельном листе объединим все оплаты (ПП) ТТН № 1 (лист № 1)

Подзадача 3. Контроль поставок и оплат.

Порядок выполнения работы. Создание справочных документов. Откройте новую книгу MS Excel и сохраните под именем ОПЛАТА ПОСТАВОК. Переименуйте Лист1 в КЛИЕНТЫ, Лист2 в ДОГОВОР. На листе КЛИЕНТЫ создайте список клиентов:

На листе ДОГОВОР создайте список договоров: При этом настройте (см. слайд):

для № договоров - текстовый формат, для названий фирм - проверку данных, для большого срока оплаты по договору - примечание (новый клиент)

Создание учетных документов. Переименуйте пять следующих листов – 1, 2, 3, 4, 5. На каждом листе сформируйте таблицу с итоговой строкой, применив соответствующие форматы: Если нет общей суммы оплаты в ячейке С 11, то в текущей ячейке должно быть пусто, в противном случае в текущей ячейке должна быть максимальная дата из графы ДАТА ОПЛАТЫ (блок ячеек В 2: В 10).

Заполните таблицы данными:

Анализ оплаты поставок: На новом листе КОНТРОЛЬ создайте таблицу, применив соответствующие форматы: При этом настройте (см. слайды):

для номеров договоров - проверку данных, для вывода названий фирм и сроков оплаты по договору - функцию ВПР, даты последних оплат и суммы оплат должны соответствовать максимальным датам и итоговым суммам оплат по каждой ТТН (итоговые ячейки на соответствующих листах по ТТН № 1, 2, 3, 4, 5), долг по оплате - разность между суммой отгрузки и суммой оплаты, дни задержки : если дата последней оплаты отсутствует, то в графе ДНЕЙ ЗАДЕРЖКИ будет надпись НЕТ ОПЛАТЫ, в противном случае – разность между датой последней оплаты, датой отгрузки и сроком оплаты по договору,

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

Ввод новых данных: На листе № 4 введите полную оплату : 1. ПП № 5 от 26. 01. 11 на сумму 300 руб. ; 2. ПП № 6 от 27. 01. 11 на сумму 700 руб. По умолчанию произойдет пересчет формул на текущем листе № 4 и на листе КОНТРОЛЬ относительно ТТН № 4 и итоговых значений. Верните данные на листе № 4 в первоначальное состояние – отсутствие ПП.

Автофильтр: На новом листе АВТОФИЛЬТР выведите все неоплаты (перед началом работы очистите от содержимого и форматов итоговую строку):

На листе АВТОФИЛЬТР выведите данные по суммам отгрузки от 10000 руб. до 50000 руб. Перед выполнением задания отмените предыдущее условие.

Добавьте к предыдущему условию новое условие – наличие долга. Будет реализовано 2 условия – вывод сумм отгрузки от 10000 руб. до 50000 руб И наличия долга:

Расширенный фильтр: На новом листе РАСШ ФИЛЬТР выведите на месте исходной таблицы данные по долгу больше или равно 2000 руб. И просроченным срокам оплаты (дни задержки больше 0):

При имеющемся условии выведите результат фильтрации на другом месте, начиная от ячейки D 12:

При имеющемся условии выведите только № договора и название фирмы. Результат фильтрации поместите на другом месте, начиная от ячейки D 15:

Выведите на листе РАСШ ФИЛЬТР данные по долгу больше или равно 2000 руб. И просроченным срокам оплаты (дни задержки больше 0). ИЛИ данные по отсутствию суммы оплаты в руб. Результат фильтрации поместите, начиная от ячейки Е 12. Перед выполнением задания очистите блок ячеек D 12: N 16 (результаты предыдущей фильтрации). Результат – см. слайд.

Итоги: На новом листе ИТОГИ по каждой фирме нужно подсчитать количество отгрузок (перед началом работы очистите от содержимого и форматов итоговую строку):

На листе ИТОГИ дополнительно к существующим итогам нужно подсчитать общую сумму отгрузки (в руб. ) по каждой фирме:

Сводные таблицы: На новом листе на основе данных листа КОНТРОЛЬ (А 1: К 6) по каждой фирме и по каждому договору нужно подсчитать количество отгрузок, суммарную отгрузку в руб. , суммарный долг в руб. , % задолженности.

В новом поле сводной таблицы рассчитайте суммарный долг в у. е. Курс будет фиксированным – 40, 5.

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

Конец работы