Импорт таблицы Excel в текстовый документ Word

Обмен данными между приложениями Word и Excel становится возможным благодаря технологии OLE. OLE – это связывание и внедрение объектов.

Связывание означает установление связей между файлами. Внедрение означает использование другого приложения для создания объекта и его сохранение в документе-приемнике.

Для обмена данными между приложениями Excel и Word может использоваться один из следующих методов: 1) вставка данных путем копирования или перемещения (простое копирование), 2) внедрение данных как объекта (OLE-внедрение), 3) связывание данных как объекта (OLE-связывание) или 4) комбинация связывания и внедрения объекта (OLE-связывание и внедрение). Выполните все четыре разновидности импортирования таблицы Excel в документ Word.

5.1. Откройте редактор MS Word и сохраните новый документ с именем Фамилия_6_n.doc в своей папке.

5.2. Вернитесь в книгу Фамилия_6_n.xls на лист Выборка, выделите ячейки с таблицей и выполните команду Правка  Копировать (или [Ctrl]+[C]).

5.3. С помощью команды Вставить выполните простое копирование таблицы из Excel в Word, которую в дальнейшем можно будет редактировать средствами Word.

5.4. Выполните OLE-внедрение Excel-таблицы в Word, воспользовавшись командой Правка  Специальная вставка и задав опции Вставить и Лист Microsoft Excel (объект). При таком копировании в Word-документ сохраняется независимая копия Excel-таблицы, а связи с исходным файлом нет, т. е. при изменении данных в исходной Excel-таблице никаких изменений в Word-таблице не будет. Но при этом, двойной щелчок на внедренной таблице откроет систему меню и панели инструментов Excel и позволит редактировать ее с помощью средств той программы, которой она была создана, т. е. используя весь арсенал средств пакета Excel.

5.5. С помощью диалогового окна Специальная вставка и его опций Связать и Текст в формате RTF выполните OLE-связывание данных Excel-таблицы. При этом изменения в исходной таблице Excel будут отображаться и в Word-отчете при обновлении связи (команда Обновить связь).

Перейдите к исходной таблице в Excel и внесите изменения, например, измените табельные номера сотрудников, воспользовавшись выпадающим списком. Переключитесь на Word-документ и убедитесь в том, что внесенные в таблице Excel изменения тут же отобразились в таблице Word-документа.

5.6. Выполните OLE-связывание и внедрение данных таблицы, воспользовавшись командой Правка  Специальная вставка и задав опции Связать и Лист Microsoft Excel (объект). Многие из параметров внедренной и связанной таблицы будут такими же, как и в предыдущем случае (см. п. 5.5), а отличительной особенностью является то, что при двойном щелчке по таблице станут доступными для работы средства пакета Excel как в примере п. 5.4. Поскольку такое копирование поддерживает связь с исходной таблицей, то любые изменения в Excel-таблице автоматически отобразятся в таблице Word-документа.

5.7. Сохраните изменения в файле Фамилия_6_n.doc и закройте все файлы.

5.8. Устно проработайте ответы на все контрольные вопросы и письменно ответьте на контрольный вопрос, соответствующий номеру Вашего варианта.__

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

1) Опишите алгоритм проверки вводимых в ячейки данных на допустимость значений.

2) Каким образом сформировать текст сообщения, которое автоматически будет выводиться, когда ячейка станет активной?

3) Какое предназначение имеет функция ВПР? Какой ее синтаксис?

4) Опишите алгоритм присваивания собственного имени диапазону ячеек.

5) Объясните работу функций: 1) =ВПР("бакалавр"; А2:С10; 3) и 2) =ВПР(101; Лист1!А2:В10; 2).

6) Объясните допущенные в формулах ошибки: 1) =ВПР("МТС"; А1:В10; 4); 2) =ВПР(А2; В2; 1); 3) =ВПР(100; Лист1!$C$1:$В$10; 3).

7) Каким образом можно организовать выпадающий список в ячейке?

8) Какое предназначение имеет функция ПРОСМОТР? Какой ее синтаксис?

9) Какой синтаксис совместного использования функций ИНДЕКС и ПОИСКПОЗ используется для поиска и выборки данных из других ячеек?

10) С какими функциями категории Ссылки и массивы Вы научились работать в этой лабораторной работе? Укажите их предназначение.

11) Какой синтаксис совместного использования функций СМЕЩ и ПОИСКПОЗ используется для поиска и выборки данных из других ячеек?

12) Перечислите известные Вам функции и их совместные комбинации для поиска и выборки данных из других ячеек.

13) Как с помощью условного форматирования можно увидеть все ячейки с дублирующимися данными в столбце?

14) Назовите способ удаления повторяющихся данных в Excel 2007 – 2010.

15) Назовите способ удаления повторяющихся данных в Excel 2003.

16) В чем заключается технология OLE?

17) Перечислите методы технологии OLE для обмена данными между приложениями Excel и Word.

18) Опишите алгоритм OLE-внедрения Excel-таблицы в документ Word.

19) Опишите алгоритм OLE-связывания Excel-таблицы в документ Word.

20) Опишите алгоритм OLE-внедрения и связывания Excel-таблицы в документ Word.

21) Что обозначают квадратные скобки в ссылках [Книга1.xls]Лист1!А1 и '[Книга 1.xls]Лист 1'!А1? Почему во второй ссылке указаны кавычки?

22) Как выглядят ссылки на ячейки других Excel-файлов?

23) Объясните на нескольких примерах использование функции ВПР.

24) Объясните на нескольких примерах использование функции ПРОСМОТР.

25) Для каких целей можно использовать функцию ВПР?

26) Для каких целей можно использовать функцию СЦЕПИТЬ?

27) Для каких целей можно использовать функцию ЛЕВСИМВ?

28) Для каких целей можно использовать функцию ПОИСКПОЗ?

29) Для каких целей можно использовать функцию ПРОСМОТР?

30) Для каких целей можно использовать функцию ИНДЕКС?

Практическая работа № 6

СРЕДСТВА УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ В EXCEL

Цель:научиться выполнять сортировку данных, их фильтрование, создавать промежуточные итоги, сводные таблицы и диаграммы, использовать функции баз данных для решения задач.

Теоретические сведения

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

Для относительно небольших предприятий учет подобных данных можно вести средствами электронных таблиц Excel.

Аналогом простой БД в Excel служит таблица. Таблица (список) – это последовательность строк и столбцов таблицы, содержащих взаимосвязанные данные. Отличительной особенностью табличной БД является то, что каждый ее столбец содержит однотипные данные, например: перечень фамилий, цену за единицу товара, дату реализации товара и т. д. Если провести аналогию между таблицей и табличной БД, то столбцы таблицы являются полями БД, а ее строки – записями. Считается, что первая строка таблицы является ее заголовком и содержит названия столбцов таблицы. Заголовок должен иметь на листе электронной таблицы горизонтальную ориентацию (т. е. он должен располагаться в первой строке). Заголовки применяются при анализе данных, при составлении отчетов, а также при поиске и организации данных. Шрифр, его размер, выравнивание и другие параметры форматирования, присвоенные заголовкам столбцов таблицы, должны отличаться от параметров, назначенных для строк данных. Для отделения заголовков от расположенных ниже данных не следует применять пустые строки.

К характерным операциям с таблицами БД, имеющимся в Excel, относят:

1) сортировку данных в определённом порядке, 2) фильтрование данных для поиска нужной информации, 3) промежуточные итоги, 4) сводные таблицы, 5) функции категории Работа с базой данных. Все действия с БД выполняет команда главного меню Данные.

1) Сортировка данныхвыделенных ячеек таблицы осуществляется командой Данные  Сортировка. При этом Excel откроет диалоговое окно Сортировка диапазона, позволяющее выбирать поля и параметры сортировки.

Можно также сортировать БД с помощью кнопок сортировки на панели инструментов, выбрав отдельную ячейку в столбце, по которому требуется сортировка.

2) Фильтрование данных– отбор записей, удовлетворяющих определённому условию, – в Excel можно осуществлять с помощью двух средств: автофильтр и расширенный фильтр. Условия фильтрования можно задавать для одного или нескольких полей, а с помощью расширенного фильтра допускается задавать вычисляемые условия (например, можно вывести на экран список

Средства управления базами данных в EXCEL только тех сотрудников, у которых оклад хотя бы на 25 % выше среднего). При фильтрации строки, неудовлетворяющие условию, будут скрыты, а слева в столбце с номерами отфильтрованных строк будут видны прежние номера элементов, которые они имели в исходном списке.

3) Промежуточные итоги– операция Excel, позволяющая производить автоматическое вычисление итоговых сумм и их занесение на рабочий лист, и при этом одновременно на рабочем листе создаётся структура, связанная с полученными итогами.

4) Сводные таблицыиспользуются для быстрого подведения итогов и получения разнообразных динамичных отчетов. С помощью сводных таблиц и сводных диаграмм в считанные секунды можно строить сложные отчеты по огромным массивам данных. Кроме этого, сводные таблицы позволяют изменять способ анализа данных "на лету" в результате перетаскивания полей из одной области отчета в другую.

5) Функции категории"Работа с базой данных" – встроенные функции Excel, обеспечивающие автоматизацию расчета итогов по записям списка, например, подсчета количества записей в БД (функция БСЧЁТ) или вычисления суммы значений (функция БДСУММ), удовлетворяющих некоторому условию.

Для применения встроенных функций необходимо подготовить диапазон условий.

Задание

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

1.1. Создайте в своей папке книгу MS Excel с именем Фамилия_6_n.

1.2. Переименуйте Лист1 на ПродИрис и скопируйте на этот лист одноименную таблицу (только ячейки с таблицей), созданную в лабораторной работе № 2 (файл Фамилия_2_n.xls). После чего закройте файл Фамилия_2_n.xls.

1.3. Перейдите на Лист2 файла Фамилия_6_n.xls и переименуйте его на Сортировка. Скопируйте на этот лист таблицу с листа ПродИрис.

1.4. Отсортируйте данные этой таблицы, задав ключи и порядок сортировки:

первый ключ – Имя по возрастанию,

второй ключ – Дата по возрастанию,

третий ключ – Стоимость по убыванию.

1.5. Сохраните изменения в файле.

Использование фильтров

2.1. Перейдите на Лист3 файла Фамилия_6_n.xls и переименуйте его на Автофильтр1. Скопируйте на этот лист исходную таблицу с листа ПродИрис.

2.2. Скопируйте лист Автофильтр1 со всем его содержимым. Назовите лист Автофильтр2.

2.3. На листе Автофильтр1 с помощью автофильтра отберите строки таблицы о проданных товарах (поле Состояние) с наименованиями Клавиатура и Программное обеспечение в поле Наименование проданного товара.

2.4. Добавьте еще один фильтр к таблице на листе Автофильтр1: отберите строки с ценой в диапазоне от 5000 до 10000 тг.

2.5. На листе Автофильтр2 отсортируйте данные таблицы по убыванию значений поля Стоимость. С помощью автофильтра выберите пять наибольших элементов списка.

2.6. Создайте новый лист с именем Расширенный фильтр. Скопируйте на этот лист исходную таблицу с листа ПродИрис.

2.7. За пределами таблицы, например, в ячейках А17:B18, создайте критерий фильтрации. Для этого в ячейку А17 скопируйте название поля Дата, а в ячейку А18 введите ">01.04.2015" (без кавычек). Кроме этого в ячейку В17 скопируйте название поля Состояние, а в ячейку В18 – текст "Возвращено" (рис. 6.1).

Рис. 6.1. Вид таблицы с применением расширенного фильтра

2.8. В диапазон ячеек, начиная с ячейки А20, с помощью расширенного фильтра отберите данные, удовлетворяющие созданному в п. 2.7 критерию.

Просмотрите и проанализируйте результат фильтрации.

2.9. Сохраните изменения в файле Фамилия_6_n.xls.

Промежуточные итоги

3.1. Создайте новый лист с именем Итоги1. Скопируйте на этот лист исходную таблицу с листа ПродИрис.

3.2. Скопируйте лист Итоги1 со всем его содержимым (см. п. 2.2) и назовите этот лист Итоги2.

3.3. Перейдите на лист Итоги1 и отсортируйте записи таблицы по возрастанию значений поля Имя (в алфавитном порядке) для подведения итогов по каждому продавцу.

3.4. Сформируйте итоговую таблицу с вычислением суммарной стоимости товаров, проданных каждым продавцом (рис. 7.2).

Рис. 6.2. Вид таблицы с итогами по суммарной стоимости товаров, проданных каждым продавцом

3.5. Перейдите на лист Итоги2 и отсортируйте таблицу по полю, указанному в табл. 6.1 согласно Вашему индивидуальному варианту. Сформируйте итоговую таблицу с указанной для Вашего варианта операцией для указанных полей.

 Таблица 6.1

3.6. Измените разметку листов Итоги1 и Итоги2 так, чтобы вся информация на них занимала только по одной странице с альбомной ориентацией.

3.7. Сохраните изменения в файле Фамилия_6_n.xls.