Контрольный пример
Откройте книгу контрольного примера
Добавьте в книгу еще один рабочий лист и назовите его Фирма-клиент
С
оздайте
таблицу по следующему образцу (рис.1),
начиная с ячейкиA1.
Рисунок 1 - Таблица «Фирмы-клиенты»
Присвойте столбцам листа имена: Код, Фирма, Руководитель, Адрес, Телефон. Для этого необходимо выделить целый столбец листа электронной таблицы например А, а затем дать команду Вставка – Имя - Присвоить. В открывшемся окне Присвоение имени (рис.2) в поле Имя ввести название Код и нажать клавишу ОК.
Рисунок 2 - Окно для присвоения имени
Повторите описанную выше операцию для всех столбцов таблицы, присвоив им соответствующие имена.
Добавьте в книгу контрольного примера еще один лист и назовите его Ревизия
В ячейку B1 введите текст Ведомость просроченных платежей на
В ячейку B2 введите функцию для определения сегодняшней даты. Для этого используйте мастер функций (рис.3): в категории Дата и время выберитефункциюСЕГОДНЯ().
Р
исунок
3 - Окно мастера функций
В ячейку B3 введите текст Дата переучета.
В
ячейку С2 введите функцию для задания
даты в числовом формате. Для этого
используйте мастер функций: В категорииДата и время
выберите функцию ДАТА.
В качестве списка параметров введите
год, месяц и число даты введенной спомощью функции
СЕГОДНЯ.
Рисунок 4 - Функция ДАТА
На листе рабочей книги у вас должны получиться записи подобные тем, что показаны на рис.5. Датой формирования контрольного примера является 20 мая 2002 года.
Рисунок 5 – Результат вычислений функций
В ячейки A5:E10 введите заголовки столбцов таблицы со следующими названиями: Код заказа, Наименование товара, Код фирмы, Цена заказа, Дата продажи.
Наименование товаров скопируйте с листа Накладная.
Код фирмы и цену заказа введите как показано на рис.127.
В ячейку F5 введите заголовок столбца Дата оплаты
В ячейку G5 введите заголовок столбца Просрочка дней.
Отредактируйте записи Е6:Е10, вводя дату продажи с помощью функции ДАТА (см п.10).
В ячейки F6 и F9 введите даты оплаты по заказам 30 и 28 апреля текущего года соответственно.
В ячейку G6 введите формулу для определения количества просроченных дней по оплате заказа =ЕСЛИ(F6=0;$C$3-E6;0).
Протяните полученную формулу для ячеек столба G.
В результате проведенных действий у вас должна получиться следующая таблица
Рисунок 6 - Таблица заказов
А в ячейках таблицы должны находиться следующие формулы:
Рисунок 7 - Расчетные формулы
Введите в ячейку D13 текст «Количество просроченных заказов».
Введите в ячейку D14 текст «Стоимость просроченных заказов».
Введите в ячейку G13 формулу для определения количества просроченных заказов:
=СЧЁТЕСЛИ(G6:G10;">0")
Введите в ячейку G14 формулу для определения суммы просроченных платежей:
=СУММЕСЛИ(G6:G10;">0";D6:D10)
Введите в ячейку A17 формулу для формирования вывода по полученным данным. Это формула сцепления, она имеет следующий вид:
=СЦЕПИТЬ("На сегодняшний день в фирме неоплаченных заказов ";G13;" на сумму ";G14;" рублей")
Сформируем таблицу с данными о клиентах просрочивших платежи. В ячейку B19 введем заголовок таблицы «Фирмы неоплатившие заказы».
В ячейки A20:E20 введем наименования столбцов таблицы – Код фирмы, Наименование фирмы, Руководитель, Адрес, Телефон.
В столбец Код фирмы введем коды организаций, просрочивших платежи – 2,4.
Для заполнения таблицы остальными данными будем использовать функцию ПРОСМОТР. Введем в ячейку B21 функцию
=ПРОСМОТР($A21;Код;фирма).
Введем в ячейку C21 функцию
=ПРОСМОТР($A21;Код;Руководитель)
Введем в ячейку D21 функцию
=ПРОСМОТР($A21;Код;Адрес)
Введем в ячейку E21 функцию
=ПРОСМОТР($A21;Код;Телефон)
С
копируем
формулы в строки таблицы. У вас должна
получиться таблицапредставленная
нарис.8
Рисунок 8 - Вывод результатов расчетов
Сохраните файл рабочей книги.
Формулировка задания: MS Excel. Сортировка и обработка списков
Цель работы: научиться обрабатывать таблицу, как базу данных: сортировать данные по определённым критериям; вводить данные с помощью функции Автоввод; использовать автоматическую фильтрацию для просмотра данных, получать промежуточные результаты при обработке данных.