Главная

Контакты

Случайная статья

• Автоматизация
• Антропология
• Археология
• Архитектура
• Биология
• Ботаника
• Бухгалтерия
• Военная наука
• Генетика
• География
• Геология
• Демография
• Деревообработка
• Журналистика
• Зоология
• Изобретательство
• Информатика
• Искусство
• История
• Кинематография
• Компьютеризация
• Косметика
• Кулинария
• Культура
• Лексикология
• Лингвистика
• Литература
• Логика
• Маркетинг
• Математика
• Материаловедение
• Медицина
• Менеджмент
• Металлургия
• Метрология
• Механика
• Музыка
• Науковедение
• Образование
• Охрана Труда
• Педагогика
• Полиграфия
• Политология
• Право
• Предпринимательство
• Приборостроение
• Программирование
• Производство
• Промышленность
• Психология
• Радиосвязь
• Религия
• Риторика
• Социология
• Спорт
• Стандартизация
• Статистика
• Строительство
• Технологии
• Торговля
• Транспорт
• Фармакология
• Физика
• Физиология
• Философия
• Финансы
• Химия
• Хозяйство
• Черчение
• Экология
• Экономика
• Электроника
• Электротехника
• Энергетика

МИНИСТЕРСТВО ОБРАЗОВАНИЯ РЕСПУБЛИКИ БЕЛАРУСЬ

МИНИСТЕРСТВО ОБРАЗОВАНИЯ РЕСПУБЛИКИ БЕЛАРУСЬ

УО «Белорусский государственный экономический университет»

Кафедра информационных технологий

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

ПО ЛАБОРАТОРНОМУ КУРСУ

дисциплины

Компьютерные информационные технологии

Раздел 1. Техническое и программное обеспечение информационных технологий

Тема

РАБОТА С ТАБЛИЦЕЙ EXCEL КАК С БАЗОЙ ДАННЫХ

Оглавление

1 Создание базы данных. 3

2 Сортировка данных базы.. 4

3 Работа с базой данных на основе формы.. 6

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

5 Подведение частных и общих итогов. 15

6 Создание сводной таблицы.. 17

7 Использование технологии слияния. 21

Задания для самостоятельной работы.. 24

Контрольные вопросы.. 25

Индивидуальные задания. 26

& Табличный процессор Excel позволяет работать с таблицей на рабочем листе книги, как с базой данных (списком). Работая с базой данных, можно:

· сортировать данные (позволяют вкладки Главная и Данные);

· фильтровать данные (вкладка Данные);

· подводить итоги с использованием статистических функций (вкладка Данные);

· разрабатывать сводные таблицы для анализа данных (вкладка Вставка).

База данных (список) в Excel–это специально организованная таблица. Требования, которым она должна удовлетворять, следующие:

· каждый столбец содержит однотипные данные;

· первая строка содержит подписи столбцов и имеет отличное от других строк оформление;

· отсутствуют пустые строки и столбцы;

· вводимые в ячейки таблицы значения не содержат пробела перед ними и после них;

· между таблицей и другими данными листа, по меньшей мере, должна быть одна пустая строка и один пустой столбец.

Строки такой таблицы называются записями, а столбцы – полями.

1 Создание базы данных

Задание 1.Создать базу данных Продажи (рис. 5.34).

Рис. 5.34 База данных Продажи

Рекомендации по выполнению

· Загрузите табличный процессор Excel.

· Создайте рабочую книгу База данных и сохраните ее в папке Excel.

· На Листе1 введите в ячейки А1:D1 соответственно имена полей БД – Менеджер; Товар; Месяц; Сумма, тыс. р. Затем выделите указанные ячейки и измените их фон, например, на желтый, воспользовавшись на вкладке Главная в группе Шрифт раскрывающимся списком Цвет заливки.

· Не оставляя пустых строк после первой введенной строки, вводите значения полей первой записи, второй записи и т.д., указанные в задании. Для ускорения ввода выполните следующее:

- повторяющиеся значения поля Менеджер (Мороз А.И., Брель Л.В., Зенько О.Н.) введите в ячейки А2:А4, поля Товар (ноутбуки, флэшки, диски) – в ячейки В2:В4, поля Месяц (январь, февраль, март) – в ячейки С2:С4;

- отобразите область задач Буфер обмена, нажав на вкладке Главная кнопку [Буфер обмена], и каждое значение ячеек вышеуказанных диапазонов скопируйте в буфер. Затем вставляйте их из буфера в соответствующие ячейки таблицы.

·  Сохраните книгу База данных в папке Excel.

2 Сортировка данных базы

& Сортировка данных базы – это упорядочение записей по возрастанию или убыванию значений одного или нескольких полей (максимум 64-х полей).

Сортировку по одному полю считают простой, а по нескольким – сложной. Простую сортировку позволяют вкладки Главная и Данные, а сложную сортировку – вкладка Данные.

Сортировку можно выполнять, в частности, по тексту (от А к Я или наоборот), числам (от наименьших к наибольшим или наоборот), датам и времени (от старых к новым или наоборот).

Пользователь может задать параметры сортировки, например, сортировку с учетом регистра букв.

Задание 2.Выполнить простую сортировку записей БД Продажи:

• по убыванию значений поля Сумма, тыс. р.;

• в алфавитном порядке значений поля Товар.

Рекомендации по выполнению

· Для сортировки по полю Сумма, тыс. р выделите любую ячейку этого поля и на вкладке Главная в группе Редактирование из раскрывающегося списка Сортировка и фильтр выберите Сортировка от максимального к минимальному. Проанализируйте результат сортировки.

· Для сортировки по полю Товар выделите любую ячейку этого поляи на вкладке Данные в группе Сортировка и фильтр нажмите кнопку  [Сортировка от А до Я]. Проанализируйте результат сортировки.

Задание 3.Выполнить сложную сортировку записей БД Продажи – рассортировать ее записи в алфавитном порядке значений поля Менеджер, а записи о каждом менеджере – по возрастанию значений поля Сумма, тыс. р.

Рекомендации по выполнению

· Выделите любую ячейку БД и на вкладке Данныев группеСортировка и фильтрнажмите кнопку  [Сортировка].

· В открывшемся окне Сортировка задайте требуемую сортировку (рис. 5.35):

Рис. 5.35 Окно Сортировка

- в группе Столбециз раскрывающегося спискаСортировать повыберите полеМенеджер;

- в группе Сортировкаоставьте заданный по умолчанию тип сортировки Значения;

- в группе Порядок оставьте заданный по умолчанию порядок сортировки От А до Я;

- нажмите кнопку [Добавить уровень] и аналогично задайте параметры сортировки по полюСумма, тыс. р. (рис. 5.35).

· Проанализируйте результат сортировки.

3 Работа с базой данных на основе формы

& Форма– это шаблон на экране, позволяющий работу с БД. В форме отображаются имена и значения полей текущей записи. В ней можно:

· просматривать записи;

· перемещаться по полям записи и редактировать их значения;

· добавлять записи в БД;

· удалять записи из БД;

· выбирать записи из БД по условию. В условиях для числовых полей можно использовать знаки операций сравнения: = (равно), < (меньше), > (больше), <= (меньше или равно), >= (больше или равно), <> (неравно). В условиях для текстовых полей можно использовать шаблоны с символами * (любая последовательность символов) и ? (любой один символ). Если условия задаются в нескольких полях записи, то они объединяются логическим «И».

Задание 4.Используя форму, выполнить следующие операции с БД Продажи:

4.1. добавить в БД новую запись:

½Зенько О.Н. ½ноутбуки½ март½17000½

4.2. выбрать из БД запись о продажах менеджером Мороз А.И. дисков в январе и удалить ее;

4.3. выбрать из БД записи о продажах на сумму, большую или равную 500 тыс. р.;

4.4. выбрать из БД записи о менеджерах, фамилии которых начинаются с буквы «М».

Рекомендации по выполнению

· На Панели быстрого доступа разместите кнопку  [Форма] (см. задание 1 в пункте 5.1.1).

· Выполните задание 4.1:

-  откройте форму для работы с БД, для чего установите курсор на любую ячейку БД и нажмите кнопку [Форма] на Панели быстрого доступа. Появится форма, в которой отображена первая запись БД, вида, как на рис. 5.36;

- нажмите в форме [Добавить] и введите значения полей добавляемой записи, указанные в задании;

- закройте форму кнопкой [Закрыть]. Обратите внимание на появление новой записи в БД.

Рис. 5.36 Форма для работы с БД Продажи

· Выполните задание 4.2:

- откройте форму. Для поиска в БД нужной записи нажмите [Критерии];

- задайте условие отбора записей: в поле Менеджер наберите значение Мороз А.И., в поле Товар – диски, в поле Месяц – январь;

- для просмотра отобранной записи нажмите в форме [Далее];

- для удаления найденной записи нажмите в форме [Удалить]. В появившемся окне подтвердите удаление, нажав [ОК];

- закройте форму. Обратите внимание, что найденная запись удалена из БД.

· Выполните задание 4.3:

- откройте форму и нажмите в ней [Критерии]. Задайте условие отбора записей – в поле Сумма, тыс.р. наберите >=500, а затем просматривайте отобранные записи, нажимая [Далее];

- по завершении просмотра закройте форму.

· Выполните задание 4.4:

- откройте форму и нажмите в ней [Критерии]. В поле Менеджер наберите букву М, а затем просматривайте отобранные записи, нажимая [Далее];

- по завершении просмотра закройте форму.

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

& Фильтрация – это быстрый и легкий способ поиска подмножества интересующих данных в БД. В отфильтрованном списке отображаются только записи, отвечающие условиям отбора, заданным для полей (временно скрываются записи, которые не требуется отображать). В отличие от сортировки, при фильтрации порядок записей в списке не изменяется.

Записи, отобранные при фильтрации, можно редактировать, форматировать и выводить на печать. На их основе можно создавать диаграммы.

Для фильтрации данных в Excel используется два типа фильтров – Автофильтр и Расширенный фильтр.

Автофильтр задается на вкладке Данные в группе Сортировка и фильтр с помощью кнопки  [Фильтр]. В результате к имени каждого поля таблицы добавляется кнопка раскрывающегося списка, например, . Раскрыв список у поля, можно задать условия фильтрации по списку значений или по некоторому условию отбора.

Если фильтрация применяется к текстовому полю, то в условии отбора могут использоваться следующие операции: равно, не равно, начинается с, заканчивается на, содержит, не содержит.

Если фильтрация применяется к числовому полю, то в условии отбора могут использоваться следующие операции: равно, не равно, больше, больше или равно, меньше, меньше или равно, между, Первые 10, Выше среднего, Ниже среднего.

Можно задать условия отбора для нескольких полей, и они будут объединяться логическим «И».

При использовании Автофильтра фильтрация осуществляется непосредственно на исходных данных БД.

Для отмены результата фильтрации следует выбрать в раскрывающемся списке поля фильтрации Удалить фильтр с «имя поля».

Если фильтрация применялась больше чем к одному полю, то для ее отмены на вкладке Данныев группе Сортировка и фильтрнажимается кнопка [Очистить].

Отключение автофильтра выполняет повторное нажатие кнопки  [Фильтр].

Задание 5.Используя автофильтр, выбрать записи БД Продажи о продажах:

5.1. флэшек и дисков;

5.2. менеджеров, фамилии которых начинаются с буквы «Б»;

5.3. менеджеров, Ф.И.О. которых содержат инициалы «О.Н.»;

5.4. ноутбуков менеджером Мороз А.И.;

5.5 на сумму от 1000 до 15000 тыс. р.;

5.6. с 5-ю наибольшими суммами;

5.7. на сумму выше среднего.

Рекомендации по выполнению

· Задайте автофильтр. Для этого сделайте активной любую ячейку БД и выберите команду: вкладка Данные/группа Сортировка и фильтр/кнопка

[Фильтр]. В результате справа от имен полей БД появятся кнопки раскрытия списка.

· Выполните задание 5.1:

- раскройте список поля Товар, оставьте флажок только у значений флэшки и диски и нажмите [ОК];

- проанализируйте результат фильтрации. Обратите внимание, что у поля Товар вместо кнопки автофильтра  появилась кнопка , что сигнализирует о том, что фильтр применен по данному полю;

- удалите результат фильтрации, для чего нажмите кнопку  и в появившемся меню выберите команду Удалить фильтр с «Товар».

· Выполните задание 5.2:

- раскройте список поля Менеджер и в появившемся меню выберите Текстовые фильтры, а затем – начинается с;

- в открывшемся окне Пользовательский автофильтр в позицию курсора наберите букву Б (рис. 5.37) и нажмите [ОК].

Рис. 5.37 Окно Пользовательский автофильтр

- проанализируйте результат фильтрации, а затем отмените его, как это делали в предыдущем задании.

· Выполните задание 5.3:

- раскройте список поля Менеджер и в меню выберите Текстовые фильтры, а затем – содержит;

- в окне Пользовательский автофильтр в позицию курсора наберите  О.Н. и нажмите [ОК];

- проанализируйте результат автофильтра, а затем отмените его.

· Выполните задание 5.4:

- раскройте список поля Менеджер, оставьте флажок только у значения Мороз А.И. и нажмите [ОК];

- раскройте список поля Товар, оставьте флажок только у значения ноутбуки и нажмите [ОК];

- проанализируйте результат автофильтра по двум полям, а затем отмените его командой: вкладка Данные/группа Сортировка и фильтр/кнопка [Очистить].

· Выполните задание 5.5:

- раскройте список поля Сумма, тыс.р. и в меню выберитеЧисловые фильтры, а затем – между;

- в окне Пользовательский автофильтрзадайте условие отбора записей так, как на рис. 5.38, и нажмите [ОК];

Рис. 5.38 Условие отбора в задании 5.5

- проанализируйте результат фильтрации, а затем отмените его.

· Выполните задание 5.6:

- раскройте список поля Сумма, тыс.р. и в меню выберитеЧисловые фильтры, а затем – Первые 10;

- в открывшемся окне Наложение условия по спискузадайте условие отбора записей так, как на рис. 5.39, и нажмите [ОК];

Рис. 5.39 Условие отбора в задании 5.6

- проанализируйте результат фильтрации, а затем отмените его.

· Выполните задание 5.7:

- раскройте список поля Сумма, тыс.р. и в меню выберитеЧисловые фильтры, а затем – Выше среднего. Проанализируйте результат фильтрации, а затем отмените его.

· Отмените автофильтр командой: вкладка Данные/группа Сортировка и фильтр/ [Фильтр].

& Расширенный фильтр позволяет задавать для поиска данных в БД более сложные критерии, чем в автофильтрах.

Примеры условий отбора в расширенном фильтре:

· несколько условий для одного поля:

Если условия задаются в разных строках, то они связываются логическим «ИЛИ»;

· по одному условию для нескольких полей в одной строке:

Если условия задаются в одной строке, то они связываются логическим «И»;

· по одному условию для нескольких полей в разных строках:

· один из двух наборов условий для двух полей:

· более двух наборов условий для одного поля:

В качестве условия отбора можно использовать и значение, являющееся результатом расчета по формуле.

Расширенный фильтр выполняется с помощью команды: вкладка Данные/группаСортировка и фильтр/кнопка[Дополнительно]. При его создании пользователем задается:

· диапазон ячеек, занимаемый БД, в котором будет осуществляться поиск;

· диапазон критериев отбора;

· диапазон результатов отбора.

Данные диапазоны могут быть расположены на одном листе книги, на разных листах книги, в разных книгах.

Задание 6.Используя расширенный фильтр, выбрать из БД Продажи записи о менеджерах, у которых фамилии начинаются с буквы «М» или были продажи товара в феврале.

Рекомендации по выполнению

· Сформируйте диапазон критериев отбора:

- имена полей Менеджер и Месяц, задействованные в условии отбора записей, скопируйте в две любые смежные ячейки рабочего листа (не примыкающие к диапазону БД), например, справа от БД, соответственно в ячейку F1 и G1;

- под именами полей укажите требуемые для отбора значения: в ячейку F2 введите М, а в ячейку G2 – февраль.

· Задайте первую строку диапазона результатов отбора. Для этого скопируйте первую строку с именами полей БД ниже самой БД, оставив пустой хотя бы одну строку под ней, например, в ячейки диапазона А17:D17.

Примечание. В диапазоне результатов можно указывать не все, а выборочные поля БД.

· Установите курсор на любую ячейку БД и выберите команду вкладка Данные/группаСортировка и Фильтр/кнопка[Дополнительно].

· В открывшемся окнеРасширенный фильтр(рис. 5.40) задайте фильтрацию:

- поставьте переключатель в положение скопировать результат в другое место, чтобы была возможность видеть на одном листе и саму БД, и результат расширенного фильтра;

- в поле Исходный диапазон Excel автоматически определяет диапазон ячеек, занимаемых БД. Если данный диапазон не совпадает с нужным, то нажмите кнопку  [Свернуть] справа от указанного поля, выделите на листе диапазон А1:D15 и нажмите кнопку  [Развернуть] в окне Расширенный фильтр-Исходный диапазондля возврата к исходному окну;

Рис. 5.40 Окно Расширенный фильтр

- в поле Диапазон условий укажите диапазон критериев отбора (F1:G2), выделив его мышью на листе;

- в поле Поместить результат в диапазон укажите диапазон результатов отбора. Для этого выделите мышью на листе ячейки первой строки диапазона результатов и несколько строк под ними, например, А17:D27, предположительно достаточных для отображения результата фильтрации;

- поставьте флажок Только уникальные записии нажмите [ОК].

· Проанализируйте результат расширенного фильтра.

· Переименуйте Лист1, дав ему имя Фильтр.

5 Подведение частных и общих итогов

& Часто в таблицах требуется подводить промежуточные (частные) и общие итоги с помощью различных операций. Для этого используется команда: вкладка Данные/группаСтруктура/кнопка [Промежуточный итог].

В результате ее выполнения в таблицу вставляются итоговые строки.

До выполнения данной команды необходимо таблицу представить как БД и рассортировать ее по полю, при изменении значения которого надо подвести итоги. Затем следует указать:

· поле группировки данных – поле, по которому были рассортированы записи;

· операцию для подведения итогов (Сумма, Количество и др.);

· поля, по которым подводятся итоги.

Для отмены в БД записей с итогами выбирается вышеуказанная команда и в открывшемся окне Промежуточные итоги нажимается кнопка [Убрать все].

Задание 7.По данным БД Продажи определить, на какую сумму продал товаров каждый менеджер (подвести частные итоги) и на какую сумму продали товаров все менеджеры (подвести общий итог).

Рекомендации по выполнению

· Скопируйте БД с листа Фильтр на Лист2, начиная с ячейки А1.

· Выполните сортировку записей БД на Листе2 в алфавитном порядке значений поля Менеджер.

· Установите курсор на любую ячейку БД и выберите команду: вкладка Данные/группаСтруктура/кнопка [Промежуточный итог].

· В открывшемся окне Промежуточные итоги (рис. 5.41) оставьте без изменения установки Excel по умолчанию, так как необходимо подводить итоги при каждом изменении значения поля Менеджер – суммировать значения поля Сумма, тыс. р. Нажмите [ОК].

Рис. 5.41 Окно Промежуточные итоги

В результате в БД вставляются записи с итогами и слева от них отображается панель управления итогами, как на рис. 5.42.

Рис. 5.42 БД Продажи с итоговыми записями

· Отобразите только итоговые записи, используя на панели управления итогами кнопки со знаком «-». Затем отобразите их совместно с группами записей БД, используя кнопки со знаком «+».

· Переименуйте Лист2, дав ему имя Итоги.

6 Создание сводной таблицы

& Сводная таблица (СТ) – это интерактивный отчет, в котором могут интегрироваться и анализироваться данные из текущей БД и внешних источников.

С помощью СТ можно анализировать данные в различных разрезах, не создавая каждый раз новый отчет. В этом и заключается достоинство СТ. В СТ можно показывать данные, скрывая детали или, наоборот, со всеми подробностями.

СТ состоит из четырех областей:

· названия строк – в ней отображаются значения поля, используемого в качестве заголовков строк СТ;

· названия столбцов – в ней отображаются значения поля, используемого в качз – в ней отображаются итоговые значения как результат выполнения нужной операции;

· фильтр отчета – позволяет применить фильтр к данным СТ.

СТ на основе текущей БД создается с помощью команды: вкладка Вставка/группаТаблицы/кнопка[Сводная таблица].

Задание 8.На основе БД Продажи создать сводную таблицу, позволяющую проводить анализ продаж товаров менеджерами по месяцам квартала года в различных разрезах. Сводную таблицу разместить на том же листе, что и БД.

Рекомендации по выполнению

· Скопируйте БД с листа Фильтр на Лист3, начиная с ячейки А1.

· На Листе3 установите курсор на любую ячейку БД и выберите команду: вкладка Вставка/группаТаблицы/кнопка[Сводная таблица].

· В открывшемся окне Создание сводной таблицы(рис. 5.43) выполните следующее:

Рис. 5.43 Окно Создание сводной таблицы

-  в группе Выберите данные для анализа оставьте переключатель в положении Выбрать таблицу или диапазон и автоматически определенный табличным процессором Excel диапазон БД.

- установите переключатель в положение На существующий лист и в поле Диапазон укажите адрес ячейки рабочего листа, начиная с которой желаете разместить сводную таблицу, например, F1;

- нажмите [ОК]. В результате на рабочем листе появится пустая сводная таблица (начиная c ячейки F1) и область Список полей сводной таблицы, в которой создается ее макет и производится ее настройка, как на рис. 5.44.

Рис. 5.44 Пустая сводная таблица и область Список полей сводной таблицы

· В области Список полей сводной таблицысоздайте макет сводной таблицы следующим образом:

- поле Менеджер перетащите мышью в область Названия строк;

- поле Товар – в область Названия столбцов;

- поле Месяц – в область Фильтр отчета;

- поле Сумма, тыс.р. – в областьЗначения;

- закройте область Список полей сводной таблицы.

В результате будет созданасводная таблица вида, как на рис. 5.45.

Рис. 5.45 Сводная таблица для анализа сумм продаж

Примечание. Для изменения порядка полей в сводной таблице перетаскиваются поля в области Список полей сводной таблицы из одной области макета в другую. Для удаления поля в области макета следует щелкнуть по удаляемому полю и из появившегося меню выбрать команду Удалить поле.

Задание 9.Пользуясь сводной таблицей, проанализировать суммы продаж:

9.1. в январе;

9.2. менеджером Мороз А.И. в январе;

9.3. дисков в 1-м квартале всеми менеджерами;

9.4. дисков и ноутбуков в феврале всеми менеджерами;

9.5. товара в 1-м квартале менеджером Брель Л.В.

Рекомендации по выполнению

Раскрывайте список у полей, задействованных в условиях отбора, указанных в задании, и выбирайте требуемые значения.

Задание 10.Создать сводную таблицу для анализа количества продаж:

10.1. товара в феврале всеми менеджерами;

10.2. дисков в январе всеми менеджерами;

10.3. товара менеджером Брель Л.В. в марте;

10.4. ноутбуков в 1-м квартале менеджером Бос Д.С.

10.5. флэшек менеджерами Мороз А.И. и Зенько О.Н. в 1-м квартале;

Рекомендации по выполнению

· Для анализа количества продаж в сводной таблице замените используемую статистическую операцию Сумма на Количество:

- выделите любую ячейку области данных сводной таблицы;

- на появившейся вкладке Параметры раскройте список Вычисления, а затем список Итог пои выберите операцию Количество. В результате будет создана сводная таблица для анализа количества продаж вида, как на рис. 5.46.

Рис. 5.46 Сводная таблица для анализа количества продаж

· Раскрывайте список у полей, задействованных в условиях отбора, указанных в задании, и выбирайте требуемые значения.

· Переименуйте лист со сводной таблицей, дав ему имя Свод.

· Сохраните книгу База данных.

7 Использование технологии слияния

& Часто офисным работникам требуется производить рассылку писем одинакового содержания многим адресатам. В этом случае можно информацию об адресатах поместить в таблицу БД Excel, а затем вставить ее в текст письма, подготовленный в текстовом процессоре Word, – и письма для рассылки готовы. Вставка информации об адресатах осуществляется в среде Word по технологии слияния командой: вкладка Рассылки/ группаНачало слияния /раскрывающийся списокНачать слияние/командаПошаговый мастер слияния.

Задание 11.Подготовить для рассылки трем получателям (например, Ильину Анатолию Петровичу, Лобач Дмитрию Николаевичу, Горгуну Сергею Ивановичу) письма нижеприведенного содержания (рис. 5.47). Бланк письма сохранить в файле Бланк, а созданные для рассылки письма – в файле Письма в папке Excel.

☼БелСофт

Уважаемый (ая) !

Сообщаем Вам, что 1 ноября 2011 г. в офисе компании состоится презентация новой версии автоматизированной бухгалтерской системы «Бухгалтерия -1С».

Исполнительный директор                                        М.В. Кравцов

Рис. 5.47 Бланк письма

Рекомендации по выполнению

· Создайте рабочую книгу Получатели и сохраните ее в папкеExcel.

· На Листе1данной книги, начиная с ячейки А1, создайте таблицу как базу данных (рис. 5.48):

Рис. 5.48 База данных Получатели письма

· Сохраните и закройте книгу Получатели.

· В Word создайте документ – бланк письма, приведенный на рис. 5.14. Символ логотипа компании (☼)выберитена вкладке Вставка в группе Символы, нажав кнопку [Символ], изшрифта обычный текст. Можно вставить любой другойсимвол. Сохраните созданный документ в файле с именем Бланк в папке Excel.

· В документе Бланк установите курсор после слова Уважаемый (ая).

· С целью вставки в данную позицию значений полей Фамилия, Имя, Отчество созданной БД, активизируйте Мастер, выбрав на вкладке Рассылкив группеНачало слиянияиз раскрывающегося спискаНачать слияниекомандуПошаговый мастер слияния.Появится область задачСлияние.

· Организуйте слияние, отвечая на запросы Мастера слияния:

- Этап 1. В окне Выбор типа документа оставьте переключатель в положении Письма и щелкните по ссылке Далее. Открытие документа;

- Этап 2. В окне Выбор документа оставьте переключатель в положении Текущий документ и щелкните по ссылке Далее. Выбор получателей;

- Этап 3:

-  в окне Выбор получателей оставьте переключатель в положении Использование списка и щелкните по ссылке Обзор;

- в окне Выбор источника данных выберите тип файлов файлы Excel(*.xls), выделите файл Получатели в папке Excel и нажмите [Открыть];

- в окне Выделить таблицу оставьте выделенным Лист1$ (на нем находится таблица с информацией об адресатах) и нажмите [ОК];

- в окне Получатели слияния рассортируйте адресатов в алфавитном порядке значений поля Фамилия, щелкнув по его заголовку, и оставьте заданных по умолчанию всех 3-х адресатов, нажав [ОК];

- в окне Выбор получателей щелкните по ссылке Далее. Создание письма;

- Этап 4:

- в окне Создание письма щелкните по ссылке Другие элементы;

- в окне Добавление поля слияния оставьте переключатель в положении Поля базы данных и при выделенном поле Фамилия нажмите [Вставить]. Затем выделите Имя и нажмите [Вставить], выделите Отчество и нажмите [Вставить], а затем [Закрыть];

- в документе Бланк вставьте пробелы между словом Уважаемый (ая) и полями слияния «Фамилия», «Имя», «Отчество» и в окне Создание письма щелкните по ссылке Далее. Просмотр писем;

- Этап 5. В окне Просмотр писем,нажимая по кнопке , просмотрите письма (они имеют вид, как на рис. 5.49) и щелкните по ссылке Далее. Завершение слияния;

- Этап 6:

- в окне Завершение слияния щелкните по ссылке Изменить часть писем;

- в окне Составные новые документы оставьте переключатель Объединить записи в положении Все и нажмите [ОК].

Рис. 5.49 Вид писем для рассылки

· Осуществите просмотр созданных писем на отдельных страницах, подготовленных к печати, выбрав на вкладке Файл команду Печать и используя кнопки  в нижней части окна. Затем сохраните их в файле Письма в папке Excel.

Задание 12.Представить преподавателю результаты работы в папке Excel с файлами База данных, Бланк, Получатели, Письма.

Задания для самостоятельной работы

Задание 1.Используя возможности сортировки, определить на основании информации БД Продажи:

1) наилучшего менеджера в каждом месяце;

2) наилучшего менеджера по продаже каждого вида товара.

Задание 2.Используя возможности автофильтра, выбрать из БД Продажи записи о продаже флэшек или дисков.

Задание 3.С помощью расширенного фильтра отобрать записи БД Продажи:

1) о менеджерах, у которых были продажи на сумму или 350, или 500 тыс. р.;

2) о продажах в январе или о продажах на сумму более 2000 тыс. р.

Задание 4.Используя возможности подведения итогов, определить на основании информации БД Продажи:

1) сумму продаж менеджерами каждого вида товара;

2) среднюю сумму продаж товара менеджерами в каждом месяце;

3) количество продаж каждым менеджером.

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

Задание 6.Создать наклейки на почтовые конверты для рассылки письма трем получателям по адресам:

Контрольные вопросы

1. Какой пункт ленты команд Excel позволяет работать с таблицей как с базой данных?

2. Как называются столбцы и строки таблицы, представляющей собой базу данных?

3. По скольким полям одновременно возможна сортировка записей базы данных?

4. Как задается условие отбора записей при использовании автофильтра?

5. Какие диапазоны ячеек должен определить пользователь при создании расширенного фильтра?

6. Назовите операции, которые можно задавать при подведении частных и общих итогов по полям БД.

7. Что должен указать пользователь при создании макета сводной таблицы?

8.  Можно ли информацию об адресатах писем перед их созданием размещать в таблице Excel как базе данных?

9. Какая команда Word используется для создания писем с целью их рассылки?

Индивидуальные задания

10. В таблице БД нижепредложенной структуры определить в качестве частных итоговстоимость материалов, хранящихся на каждом складе, и общего итога – стоимость материалов, хранящихся на всех складах. В БД предусмотреть информацию о 3-х складах для хранения 5-ти видов стройматериалов.

11. В таблице БД нижепредложенной структуры определить в качестве частных итогов–среднее количество посетителе