Применение Excel для анализа информации из базы данных

ЗАДАНИЕ:

В вашей компании ведется учет товаров, которые имеются на складе, при помощи таблицы товары базы данных Борей, которая расположена в каталоге C:\Program Files\Microsoft Office\Office11\Samples. В этой таблице нахо­дятся следующие важные для вас столбцы:

r кодТовара — идентификатор товара;

r марка — наименование продукта;

r Цена — стоимость за единицу продукта;

r наСкладе — количество единиц этого товара на складе;

r минимальныйзапас— минимально допустимое количество единиц данного товара на складе. Если реальное количество единиц этого товара меньше, чем это значение, то товар нужно срочно заказать;

r поставкиПрекращены— флаг прекращения работы с товаром. Если в этом столбце стоит единица, то это значит, что принято решение закупки этого товара больше не производить.

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

Вам поручено создать приложение на основе Excel, которое бы:

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

2. Генерировало бы в Excel дополнительные столбцы следующего содержания:

• "Заказать товара, штук" — разница между столбцами МинимальныйЗапас и наСкладе. В этот столбец должна помещаться информация о количестве товара в штуках, которое нужно срочно заказать. Эту информацию нужно генерировать только для тех записей, для которых значение в столбце Минимальныйзапас больше, чем в столбце НаСкладе, и у которых значение столбца ПоставкиПрекращены установлено в False;

• "Стоимость заказа" — определяло бы стоимость такого пополнения склада для каждой строки в таблице. Стоимость заказа рассчитывается как произведение предыдущего столбца и столбца цена. Эту информацию также нужно генерировать только для тех записей, для которых значение в столбце минимальныйзапас больше, чем в столбце НаСкладе.

i

В реальной задаче правильнее (и намного производительнее) бы было перенести расчет таких столбцов на сервер баз данных, использовав SQL-запрос с вычисляемыми столбцами. Однако для целей этой самостоятельной работы реализуйте их вставку средствами Excel (такое решение — единственно возможное, к примеру, если мы обращаемся к не реляционному источнику данных, такому как текстовый файл).

3. Вставляло бы в одной строке под полученными записями из базы данных две итоговые строки:

• "Общая стоимость товаров на складе"— итоговая стоимость всех товаров, которые находятся на складе (как сумма произведений столбцов НаСкладе И Цена для каждой строки);

• "Общая стоимость товаров к заказу" — итог по столбцуСтоимостьЗаказа.

Общий вид получившегося приложения может быть таким, как представлено на рис. 11.2.

Итоговые строки могут выглядеть так, как показано на рис. 11.3.

Рис. 11.2. Первые строки листа с импортированными данными

Рис. 11.3. Последние строки с итоговыми значениями