Лабораторная работа №3 Сводная таблица. Создание серийных документов Лабораторная работа №3
Тема: Сводная таблица. Создание серийных документов.
Цель: Проанализировать сводную таблицу и создать серийные документы.
Задание №1. Имеется таблица платежей (таб. 14), поступающих из разных стран и городов в наличном и безналичном виде. Таблицу 14 необходимо набрать и проанализировать.
Шаг 1. Набрать таблицу 14 в Excel.
Таблица 14
Исходные данные
Страна |
Город |
Вид |
Платеж |
Дата |
РФ |
Курск |
нал |
50 |
05.янв |
Украина |
Киев |
банк |
100 |
14.янв |
Беларусь |
Минск |
нал |
80 |
25.янв |
Беларусь |
Брест |
банк |
24 |
09.фев |
РФ |
Москва |
банк |
90 |
12.фев |
РФ |
Курск |
нал |
300 |
20.фев |
Украина |
Киев |
банк |
120 |
08.мар |
Украина |
Киев |
банк |
150 |
18.мар |
РФ |
Москва |
нал |
65 |
24.мар |
РФ |
Москва |
нал |
70 |
26.мар |
Украина |
Львов |
нал |
200 |
01.апр |
РФ |
Курск |
банк |
120 |
06.апр |
Беларусь |
Минск |
нал |
60 |
08.апр |
Беларусь |
Брест |
банк |
90 |
20.апр |
Беларусь |
Минск |
банк |
50 |
21.апр |
РФ |
Москва |
нал |
100 |
25.апр |
Шаг 2. После того как набрали таблицу 14, необходимо построить сводную таблицу.
Шаг 2.1. Таблицу 14 выделяем курсором и заходим в меню «Данные» - «Сводная таблица». Нажимаем кнопку «Далее», и «Готово». Появляется список полей: «Страна», «Город», «Вид», «Платеж» и «Дата» и пустая таблица. В ней написано: «перетащите сюда поля столбцов», «перетащите сюда поля строк», «перетащите сюда элементы данных». Её необходимо заполнить данными: «Город», «Страна», «Платеж». Для появления в шапке таблицы обозначения «Город», необходимо при помощи левой клавиши мыши тащить курсором нужное обозначение в шапку таблицы. Там где написано «перетащите сюда поля столбцов», перетаскиваем «Город» в ячейку В3. В нашей таблице появляются названия городов (таб. 15).
Таблица 15
Форма для ввода условий задания
|
|
|
|
|
|
|
|
|
Город |
|
|
|
|
|
|
|
Брест |
Киев |
Курск |
Львов |
Минск |
Москва |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
33
|
|
|
|
|
Шаг 2.2. Для появления в шапке таблицы 15 обозначения «Страна», необходимо при помощи левой клавиши мыши тащить курсором нужное обозначение в шапку таблицы 15. Там где написано «перетащите сюда поля строк», перетаскиваем «Страна» в ячейку А5. В нашей таблице появляются названия стран (таб. 16).
Таблица 16
Форма для ввода условий задания
|
Город |
|
|
|
|
|
|
Страна |
Брест |
Киев |
Курск |
Львов |
Минск |
Москва |
|
Беларусь |
|
|
|
|
|
|
|
РФ |
|
|
|
|
|
|
|
Украина |
|
|
|
|
|
|
|
Общий итог |
|
|
|
|
|
|
|
Шаг 2.3. Для появления в таблице 16 обозначения сумм платежей, необходимо при помощи левой клавиши мыши тащить курсором нужное обозначение в таблицу 16. Там где написано «перетащите сюда элементы данных», перетаскиваем «Платеж» в ячейку В5. В таблице 17 появляются суммы платежей. После получения таблицы 17, окно «Список полей» не закрываем.
Таблица 17
Результирующая сводная таблица
|
|
|
|
|
|
|
|
Сумма по полю Платеж |
Город |
|
|
|
|
|
|
Страна |
Брест |
Киев |
Курск |
Львов |
Минск |
Москва |
Общий итог |
Беларусь |
114 |
|
|
|
190 |
|
304 |
РФ |
|
|
470 |
|
|
325 |
795 |
Украина |
|
370 |
|
200 |
|
|
570 |
Общий итог |
114 |
370 |
470 |
200 |
190 |
325 |
1669 |
Задание №2. Преобразовать таблицу 17 следующим образом: отобрать платежи только для Москвы и Киева (таб. 18)
Действия: продолжаем анализировать таблицу 14 с помощью результирующей сводной таблицы 17. Для того чтобы отобрать платежи только для Москвы и Киева, необходимо поставить курсор в таблицу рядом с обозначением «Город» и нажать на появившуюся стрелку. После этого появляется список городов, и мы курсором убираем, галочки напротив городов Брест, Курск, Львов и Минск, и нажимаем кнопку «ОК». После получения таблицы 18, окно «Список полей» не закрываем.
Таблица 18
Таблица платежей для г. Москва и г. Киев
|
|
|
|
Сумма по полю Платеж |
Город |
|
|
Страна |
Киев |
Москва |
Общий итог |
РФ |
|
325 |
325 |
Украина |
370 |
|
370 |
Общий итог |
3
34
|
325 |
695 |
Задание №3. Показать наличные и безналичные платежи по странам.
Действия: Необходимо перетащить кнопку Вид на горизонтальную ось, а кнопку Город на ось страниц. Для этого продолжаем анализировать таблицу 14 с помощью таблицы 18. Теперь необходимо в шапку таблицы поставить наименование «Вид», вместо наименования «Город». Для появления в шапке таблицы обозначения «Вид» вместо «Город», необходимо при помощи левой клавиши мыши тащить курсором обозначение «Город» назад в список полей, а обозначение «Вид» в шапку таблицы в ячейку В3. В результате получается таблица 19.
Таблица 19
Таблица наличных и безналичных платежей
|
|
|
|
Сумма по полю Платеж |
Вид |
|
|
Страна |
банк |
нал |
Общий итог |
Беларусь |
164 |
140 |
304 |
РФ |
210 |
585 |
795 |
Украина |
370 |
200 |
570 |
Общий итог |
744 |
925 |
1669 |
Задание №4. Требуется сформировать серийные документы, которые содержат один и тот же текст с разными числовыми и другими данными, взятыми из таблиц.
Источником данных является таблица 20 «Клиенты», содержащая сведения о сроках и суммах кредитов, взятых в банке. В ней в столбце F вычисляется число дней задержки выплат (F2=ЕСЛИ(B$8>E2;B$8-E2;0)) относительно установленной даты; D7 – сумма всех просроченных долгов клиентов (D7=СУММЕСЛИ(F2:F6;”>0”;D2:D6))
Шаг 1. Набрать таблицу 20 в Excel. Обратите внимание, что в нашем примере для получения видимого результата дата в ячейках Е2, Е3, Е4, Е6, должна быть не позднее 31 декабря 2009 года. Для этого необходимо зайти в ячейки Е2, Е3, Е4, Е6 и поставить 2009 год, вместо текущего года.
Таблица 20
Таблица «Клиенты»
-
A
B
C
D
E
F
1
Фирма
Директор
Пол
Сумма
Дата
Срок (дней)
2
Весна
Иван
м
50000
02.сен
3
Спорт
Петр
м
12000
25.сен
4
Сервис
Ольга
ж
70000
25.авг
5
Союз
Олег
м
8000
20.авг
6
Восток
Сергей
м
30000
01.сен
7
Всего просрочено
8
Сегодня
35
Для ввода формулы необходимо мышкой нажать на ячейку (для её активации). Формула набирается вручную, но не в самой ячейке, а выше, там, где на панели инструментов находится обозначение f(x). Ввод формулы начинается со знака равенства « = ». Например, f(x)=……..
Для введения формулы необходимо произвести следующие действия:
Шаг 2.1. В ячейку F8 необходимо ввести постоянно обновляющуюся дату, используя функцию «Сегодня». С этой ячейкой сравниваем даты окончания кредита, указанные в столбце Е. Для введения функции «Сегодня» необходимо зайти в меню «Вставка», выбрать «Функция». В появившемся окне в категории «Дата и Время» выбираем функцию «Сегодня», и нажимаем кнопку «ОК» (рис. 12).
Рис. 12. Диалоговое окно «Мастер функций – шаг 1 из 2»
36
Рис. 13. Окно «Аргументы функции»
Шаг 2.2. В ячейку F2 необходимо ввести формулу ЕСЛИ($F$8>E2;$F$8-E2;0), означающую, что если F8 (сегодняшняя дата), больше, чем дата окончания кредита (у фирмы Весна это ячейка Е2), то тогда срок задолженности будет равен «сегодняшняя дата минус дата окончания кредита». В ячейках E2-E6 указан срок, когда клиент обязан вернуть кредит в банк, т.е. срок просрочки кредита. Если F8 больше, чем срок окончания кредита, то задолженности нет, то есть ноль (например, если кредит надо вернуть 20.08.2010 г., а текущая дата 04.02.2010 г., то срок просрочки кредита равен нулю).
Для ввода формулы необходимо мышкой нажать на ячейку (для её активации). Набираем формулу вручную, но делаем это не в самой ячейке, а выше, там, где на панели инструментов находится обозначение f(x). Ввод формулы начинается со знака равенства « = ». Например, f(x)=……..
Шаг 2.3. Наберите формулы для подсчета задолженности (в каждой ячейке диапазона F2:F6), не используя функцию копирования. Для этого курсор установите в ячейке F2, F3 и т. д., и наберите формулы, которые приведены в таблице 21 (в диапазон ячеек F2:F6).
Шаг 2.4. Для подсчёта общего итога суммы задолженности клиентов в ячейку D7, вводим формулу: D7=СУММЕСЛИ(F2:F6;»>0»;D2:D6), означающую, что если в диапазоне F2:F6 есть значения больше нуля, то необходимо сложить ячейки из диапазона D2:D6, которым они соответствуют, получая, в результате, сумму задолженности по всем клиентам. В общем, после ввода формулы в ячейке D8, мы получим общую сумму просроченного кредита.
Таким образом, Ваша таблица (в варианте формул) должна выглядеть следующим образом (таб. 21):
Таблица 21
Форма для ввода условий
|
A |
B |
C |
D |
E |
F |
1 |
Фирма |
Директор |
Пол |
Сумма |
Дата |
Срок (дней) |
2 |
Весна |
Иван |
м |
50000 |
02.сен |
=ЕСЛИ($F$8>E2;$F$8-E2;0) |
3 |
Спорт |
Петр |
м
37
|
12000 |
25.сен |
=ЕСЛИ($F$8>E3;$F$8-E3;0) |
Продолжение таблицы 21
4 |
Сервис |
Ольга |
ж |
70000 |
25.авг |
=ЕСЛИ($F$8>E4;$F$8-E4;0) |
5 |
Союз |
Олег |
м |
8000 |
20.авг |
=ЕСЛИ($F$8>E5;$F$8-E5;0) |
6 |
Восток |
Сергей |
м |
30000 |
01.сен |
=ЕСЛИ($F$8>E6;$F$8-E6;0) |
7 |
Всего просрочено |
|
|
=СУММЕСЛИ(F2:F6;">0";D2:D6) |
||
8 |
Сегодня |
=СЕГОДНЯ() |
||||
Примечание: результирующая таблица «Клиенты» должна выглядеть следующим образом, как показано в таблице 22 (если дата 04.02.2010 г). Срок (дней) зависит от даты, которую мы введем!
Таблица 22
Результирующая таблица «Клиенты»
-
A
B
C
D
E
F
1
Фирма
Директор
Пол
Сумма
Дата
Срок (дней)
2
Весна
Иван
м
50000
02.сен
155
3
Спорт
Петр
м
12000
25.сен
132
4
Сервис
Ольга
ж
70000
25.авг
163
5
Союз
Олег
м
8000
20.авг
0
6
Восток
Сергей
м
30000
01.сен
156
7
Всего просрочено
162000
8
Сегодня
04.02.2010
Задание №5: требуется сформировать и разослать массовые уведомления должникам, о возврате кредита, превышающего 10000 руб. в виде документа на рис. 14.
Для этого Вам потребуется два документа: один уже созданный в Exсel, другой надо будет создать в текстовом редакторе Word.
Пример документа, который надо получить, показан на рисунке 14.
Директору фирмы Весна Уважаемый Иван! Напоминаем, что срок просрочки взятого Вами кредита составляет «155» дней. Размер кредита составляет 50000 руб. и он истек «02/09/2009». Просьба срочно погасить кредит. Президент банка Васильев А.А.
|
Рис. 14. Результирующие данные уведомления
Примечание: для формирования и рассылки массовых уведомлений должникам, о возврате кредита, превышающего 10000 руб. в виде документа (рис. 14), необходимо произвести следующие действия:
Шаг 1. Сначала работаем в программе Word. Для этого вводим текст уведомления в файл Word (без конкретных данных) (рис. 15). Назовем этот документ- шаблон именем Уведомление.
38
Директору фирмы «» Уважаемый «»! Напоминаем, что срок просрочки взятого Вами кредита составляет «» дней. Размер кредита составляет «» руб. и он истек «». Просьба погасить кредит. Президент банка Васильев А.А. |
Рис. 15. Шаблон уведомления
Шаг 2. Через меню «Сервис» – «Письма и рассылки» – «Слияние» вызвать окно «Слияние».
Шаг 3. В окне «Слияние» в меню «Выбор типа документа» выберите параметр «Письма» (рис. 16), затем ссылку «Далее открытие документа» (внизу окна «Слияние»).
Рис. 16. Фрагмент диалогового окна «Слияние»
Шаг 4. В окне «Слияние» в меню «Выбор документа» выберите «Текущий документ» (справа вверху) (рис. 17). В окне «Слияние» щелкните ссылку «Далее выбор получателей» (справа, внизу окна).
Р
39
Шаг 5. В окне «Слияние» появляется меню «Выбор получателей», где выбираем параметр «Использование списка» (справа вверху) (рис. 18).
Рис. 18. Фрагмент диалогового окна «Слияние»
Шаг 6. В меню «Существующий список» выберите ссылку «Обзор» (рис. 18).
Шаг 7. После того как выбрали ссылку «Обзор» появляется окно «Выбор источника данных» (рис. 19). В папке «Мои источники данных» выберите нужный источник данных (Ваш файл Excel).
Рис. 19. Диалоговое окно «Выбор источника данных»
40
Он может быть на рабочем столе, в папке «Мои документы», либо в другой папке. Если в Вашем файле несколько листов, то надо указать лист, где находится таблица «Клиенты» (таб. 22). Т.е. нам требуется выбрать лист, содержащий необходимые сведения. Для того чтобы увидеть лист, где находиться таблица «Клиенты» необходимо 2 раза кликнуть мышкой и страница раскроется. Убедитесь, что в появившемся окне «Получатели слияния» отображаются данные из Вашей таблицы, и нажмите «ОК». Если с первого раза не получилось выбрать нужную страницу, то следует в окне «Слияние» (рис. 20) нажать клавишу «Выбрать другой список» и повторить выше описанные действия.
Рис. 20.Фрагмент диалогового окна «Слияние»
Шаг 8. В окне «Слияние» внизу нажмите кнопку «Далее. Создание письма» (рис. 21).
Рис. 21. Фрагмент диалогового окна «Слияние»
Шаг 9. После произведенных действий необходимо установить курсор в месте, где необходимо вставить текст («»). Например, Директору фирмы «здесь ставим курсор мышки». После этого появляется название фирмы. И нажимаем кнопку «Закрыть». В окне «Слияние» в меню «Создание письма» нажмите на кнопку «Другие элементы» (рис. 22).
41
Рис. 22. Фрагмент диалогового окна «Слияние»
После этого появится окно «Добавление поля слияния». Выбираем необходимый параметр меню «поля», например «Фирма» (рис. 23). Нажимаем в окне «Добавление поля» кнопку «Вставить» и «Закрыть» В письме в кавычках появляется «Фирма». После этого закрываем окно «Добавление поля слияния». В общем, там, где в уведомлении находятся кавычки (рис. 24), вставляем необходимые данные, например: «Фирма», «Дней», «Сумма», «Срок дней» и повторяем необходимое число раз, через окно «Слияние», меню «Создание письма» и кнопку «Другие элементы».
Рис. 23. Диалоговое окно «Добавление поля слияния»
42
Директору фирмы ««Фирма» Уважаемый «Имя»! Напоминаем, что срок просрочки взятого Вами кредита составляет ««Срок_дней»». Размер кредита составляет ««Сумма»» руб. и он истек ««дней»». Просьба погасить кредит. Президент банка Васильев А.А.
|
Рис. 24. Итоговый шаблон уведомления для ввода данных
Шаг 10. После получения общего образца для рассылки писем (рис. 24), внизу окна «Слияние» нажимаем кнопку «Далее. Просмотр писем» (рис. 25).
Рис. 25. Фрагмент диалогового окна «Слияние»
Шаг 11. Через окно «Слияние», меню «Просмотр писем», нажав на кнопку «Получатель» можно просмотреть все письма-уведомления клиентам банка (рис. 26).
Рис. 26. Фрагмент диалогового окна «Слияние»
Шаг 12. После просмотра уведомления в окне «Слияние» внизу нажимаем кнопку «Далее. Завершение слияния». В окне «Слияние» появляется меню: «Завершение слияния», «Слияние» (рис. 27).
Рис. 27. Фрагмент диалогового окна «Слияние»
43
КОНТРОЛЬНЫЕ ВОПРОСЫ
1. Перечислите итоговые функции, которые могут быть использованы при автоматическом подведении итогов.
2. Создание сводных таблиц.
3. Как ввести постоянно обновляющуюся дату?
4. Для чего используется окно «Аргументы функции»?
5
44