Получение данных из внешних источников

Лабораторная работа № 2

Работа с большими таблицами.

В реальной практике работы в Excel обычно приходится оперировать большим числом данных, когда таблица может иметь десятки столбцов, а также десятки, сотни, а иногда и тысячи строк. Иногда данные хранятся в другом месте, и их требуется перенести для обработки в Excel. Эти данные могут находиться не только в другом файле, но и на другом компьютере, подключенном к локальной сети или даже в сети INTERNET. Кроме того, способ организации этих данных может отличаться от таблиц Excel. Тем не менее, в Excel предусмотрена возможность сформировать запрос и получить нужные данные из любых доступных источников. Это осуществляет специальная надстройка в MS Office, которая носит название MS Query.

Рассмотрим типичную потребность торгового предприятия в учете продажи товаров.

1.Запустите Excel, создайте новую книгу. На ее первом листе нужно будет поместить данные, содержащиеся в файле базы данных формата MS Access. В качестве учебного примера используем файл Tovar2000.mdb. Эта база данных содержит следующую информацию: вид товара, наименование товара, дата заказа, количество, цена за единицу, заказчик и номер варианта. Вся информация помещается в четыре связанных между собой таблицы. При обращении к базе данных необходимо выбрать только те записи, которые соответствуют номеру варианта, равному N. Пусть N=0.

Вызовем команду Данные - Из других источников – Из Microsoft Query. Появится окно, представленное на рис. 1 или на рис.1.1. Следует выбрать нужный нам источник (База данных MS Access или MS Access Database) и щелкнуть кнопку OK. Теперь для подключения к источнику данных необходимо найти и указать упомянутый файл Tovar2000.mdb.

Рис. 1.

Рис. 1.1

2.В появившемся окне (см. рис. 2) MS Query помогает нам сформировать запрос. В правую часть остается перенести те поля из разных таблиц, которые нам нужны. Это легко сделать, если раскрыть каждую (список слева) из четырех таблиц источника, щелкая значок с плюсом около каждой таблицы. Затем выделяем поле и щелкаем кнопку переноса в правую часть. Можно просто выполнять двойной щелчок на имени поля. Таким образом в запрос должны быть перенесены шесть полей из базы данных (см. рис.2).

Рис. 2.

Рис. 3.

3.Далее (щелкните такую кнопку) окончательно сформируем запрос (нам нужны не все записи, а только те, что соответствуют варианту № 0. Поле «Вариант» есть в таблице «Заказы»). Сейчас мы находимся в среде MS Query. В верхней части окна представлены таблицы источника данных, которые были выбраны на предыдущем шаге. Они связаны по ключевым полям (см. рис. 3). Если по каким-либо причинам связь не установилась, не огорчайтесь. Вы с легкостью можете установить нужные связи вручную. Мышью достаточно перетащить поле одной таблицы к соответствующему полю другой таблицы, и между таблицами протянется ниточка. Эта ниточка и указывает на связь таблиц. Перейдем к отбору (фильтру) данных. Вызов команды Условия-Добавить условия выводит окно, показанное на рис. 4. Здесь нужно выбрать из списка Поле «Номер варианта» из таблицы «Заказы», выбрать Оператор «равно», а Значение поставить равным нулю.

Рис.4.

Чтобы не вводить значение с клавиатуры, можно получить список всех значений, содержащихся в выбранном поле (кнопка Значения…). Щелкните кнопку Добавить, а затем Закрыть. Мы выполнили свою задачу, хотя MS Query предоставляет более богатые возможности. Вполне возможно добавление нескольких условий, соединенных операциями И или ИЛИ (обратите внимание на соответствующие селекторные кнопки). Выбор групповой операции позволит найти сумму или среднее для числовых данных.

Введенное нами условие появилось в рабочем окне MS Query. В нижней части окна отражаются результаты запроса. Сравните полученное на вашем компьютере с тем, что было ранее (как на рис. 3).

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

4.Выберите команду Вид-Запрос SQL….Теперь можно просмотреть и пролистать текст сделанного нами запроса на специальном и достаточно стандартизированном языке. Этот язык, называемый «SQL» служит для управления базами данных с форматами очень широкого спектра. Например, наш запрос можно было написать, используя этот язык, не прибегая к помощи мастера запросов. Впрочем, как видите, и без этих знаний в данном случае можно решить задачу. Поэтому не будем ничего исправлять в тексте и щелкнем кнопку Отмена.

5. Теперь мы опять в рабочем окне MS Query. Выберем команду Файл-Вернуть данные в MS Excel. На промежуточный вопрос «куда помещать полученные данные», укажем ячейку A1 первого листа нашей рабочей книги. Фрагмент таблицы представлен на рис. 5. В первой строке размещаются заголовки столбцов. Excel активизирует инструмент Фильтр. Выбором команды Данные-Фильтр можно отключить данную функцию, которая нам сейчас не нужна.

Рис. 5.

6.Вставка новых строк и столбцов. Прежде чем вставлять новые строки и столбцы, отформатируем полученную таблицу. Во вкладке Конструктор уберем галочку чередующиеся строки Рис.6.

Рис. 6.

Выберем стиль таблицы, который соответствует шаблону стиля НЕТ. Наводим курсор мыши, на полосу прокрутки вкладки Стили таблиц, как показано на рисунке 7 и из ниспадающего поля выбираем первый слева шаблон рис.8.

Рис.7

6.1. Проставим порядковые номера товаров. Для этого нужно слева от первого столбца вставить новый столбец. Щелкните на любой ячейке столбца А. Выполните команду Главная - Вставить столбцы на лист. Слева появится новый столбец. Внесите в ячейку А2 число 1, в ячейку А3 – 2. Теперь выделите эти ячейки, наведите курсор на маркер автозаполнения ячейки А3 и протяните курсор с нажатой левой клавишей мыши по столбцу А до конца таблицы. Все ячейки заполнятся порядковыми номерами (автозаполнение). Вставьте в ячейку А1 надпись «№».

Рис.8

6.2. Вставим столбцы, которые будут содержать год заказа и общую цену. Пусть они располагаются как столбцы F и E. Введите в ячейку F1 «Год», а в E1 «Общая цена». Занесите в ячейку F2 формулу =ГОД(H2). В ячейке F2 появится следующая формула

Таблица_Запрос_из_MS_Access_Database3[[#Эта строка];[Дата заказа]],

нажмите Enter, и весь столбец автоматически сам заполнится нужными значениями. Аналогично в ячейку Е2 введите формулу =G2*D2. В ячейке высветится следующее выражение

Таблица_Запрос_из_MS_Access_Database3[[#Эта строка];[Количество]]*Таблица_Запрос_из_MS_Access_Database3[[#Эта строка];[Цена за единицу]].

Как и в предыдущем случае, столбец заполнится автоматически. Отрегулируйте с помощью команды Главная- Формат - Автоподбор ширины столбца ширину столбцов таблицы.

6.3.Щелкните по любой ячейке первой строки (например А1), выполните команду Главная - Вставить строки на лист. Строка добавится выше текущей строки. Введите в ячейку текст «Заказы за последнее время». Установите параметры форматирования: размер 16, полужирный. Пусть введенный текст как заголовок помещается по центру всей таблицы. Этого можно добиться следующим образом. Выделите диапазон A1:I1. Щелкните кнопку Объединить и поместить в центре на вкладке Главная. Другой способ – выбрать команду Формат-Ячейки-вкладка Выравнивание.Установите флажок объединение ячеек.

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

Если требуется удалить лишь ограниченный диапазон ячеек, то нужно выполнить те же команды. Далее в появившемся окне Удаление ячеек следует назначить требуемую опцию удаления и нажать кнопку ОК.