Excel

8. Работа с базой данных

Цель работы – освоение различных способов работы с базой данных

Общие сведения

Определение итогов в базе данных

Один из способов обработки и анализа базы данных состоит в подведении различных итогов.

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

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

В общем случае автоматическое формирование итогов требует задания следующих параметров:

поля, по которому производится группировка (например, город); поля, по которому подсчитывается итог (например, количество) операции, которая используется при группировке (например,

сумма или среднее).

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

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

Удаление итогов производится через меню Данные Итоги...

Убрать все.

Сводная таблица25

Это мощный инструмент для обработки данных, позволяющий представлять данные в удобном для восприятия виде и выявлять при- чинно-следственные связи. Однако для того, чтобы правильно пользоваться этим инструментом, следует изучить его особенности.

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

Ватман Картон Клей

Братск

Мурманск

Это и есть сводная таблица. Как видно, построить ее достаточно сложно: количество столбцов заранее неизвестно и определяется только после анализа содержащихся в исходной таблице записей. Excel позволяет автоматизировать процесс построения таких таблиц.

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

Главная часть составления сводной таблицы – разработка ее макета. Окно создания макета показано на Рис. 4.

Рис.4. Окно разработки макета сводной таблицы

25 В MS ACCESS аналогичная таблица называется перекрестной. В литературе по маркетингу встречается также термин кросс-таблица.

Центральное место в этом окне занимает заготовка макета. Поскольку создается простейшая таблица, разработка макета достаточно проста. Согласно условиям примера требуется, чтобы в строках таблицы были названия городов. Перетащите мышкой кнопку Город из набора названий полей в правой части окна, в область Строка макета. Теперь каждая строка будет соответствовать городу. Аналогично поступите со столбцом: укажите, что в столбцах должны находиться названия товаров.

Кнопку Количество перенесите в область Данные.

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

В более сложном варианте сводная таблица может содержать: несколько страниц (например, таблицы рассмотренного вида строятся по месяцам). Для построения такой таблицы одно из названий полей следует перенести в зону Страница макета. На каждое значение этого поля будет создана отдельная страница, а одна страница будет соответствовать общему итогу (в примере – по всем месяцам); несколько полей в строке и/или столбце. Тогда таблица будет вы-

давать строки и/или столбцы для каждой комбинации значений этих двух полей. Например, если ввести в зону Строка два поля – Город и Месяц поставки, то будут созданы отдельные строки для поставок из каждого города в каждом месяце; несколько различных итогов.

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

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

Задание

Определить объемы поставок за каждый месяц. Добавить к итогам среднее значение объема поставок.

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

Научиться скрывать и выводить детальную информацию: вывести только общие итоги; вывести итоги по каждому месяцу;

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

Составить сводную таблицу на отдельном листе. В ней определить среднее и максимальное значение объемов поставок по отдельным видам продукции и по городам.

Отобразить сводные таблицы по городам и товарам для каждого месяца.

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

Порядок выполнения работы

Все действия, необходимые для выполнения задания, описаны в разделе Общие сведения к данной работе.

При разработке рекомендаций следует обратиться к работе, где исследовалось использование функции СУММ.

Результат работы

Умение создавать итоги, работать со структурой документа и строить сводные таблицы.

9. Ведение базы данных об оплате работ по договорам

Цель работы – проверка навыков работы с базами данных в электронных таблицах

Общие сведения

ООО «Икс» занимается работами по договорам. Каждый договор разбивается на этапы. Например, договор №1 называется "Разработка информационной системы маркетинга для ОАО «Игрек» ". Он состоит из следующих этапов.

1.Детализация исходных требований к системе.

2.Разработка программного обеспечения.

3.Отладка системы.

4.Сдача-приемка.

5.Обучение персонала.

Фирма ведет несколько (порядка 10) аналогичных договоров одновременно.

Оплата выполненных работ обычно производится поэтапно с авансовым платежом.

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

Сведения о договорах хранятся в двух таблицах – базах данных в Excel. Одна из таблиц – Плановый ход оплаты по договорам. Ее столбцы: Номер договора, Номер этапа, Сумма, Дата оплаты плановая.

Сведения в нее заносятся после оформления каждого договора.

Вторая таблица отражает фактическую оплату. Ее столбцы: Номер платежного документа, Номер договора, Номер этапа, Сумма,

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

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

Задание

Требуется разработать три отчета: о состоянии оплаты на текущую дату, на заданную дату и на заданный период. Каждый отчет должен содержать заголовок, в котором записаны даты. Содержание отчета – три числа: Оплата по плану, Оплачено фактически, Разница.

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

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

Рекомендации по работе

Таблицы плановой и фактической оплаты, а также каждый из отчетов лучше выполнять на отдельных листах. Столбцы в таблицах должны иметь названия.

Заголовки отчетов должны занимать несколько ячеек и иметь вид:

Отчет по состоянию на

Если отчет составляется на сегодняшнюю дату, то она должна вставляться в свободную ячейку автоматически (используйте функцию СЕГОДНЯ(). В остальных случаях пользователь может вставить в свободные ячейки заголовка требуемую дату.

Для простоты расчетов в отчетах на текущую и заданную дату учитываются данные с начала года. Договоров, продолжающихся с прошлого года, пока нет26.

Для извлечения данных используются функции работы с базами данных. К ним относятся функции БДСЧЁТ и БСУММА. Среди их аргументов следует задать:

1.Базу данных. Это блок ячеек, в которых содержатся данные. Заголовки столбцов также следует включить в этот блок. Для того чтобы можно было вносить новые данные, следует включить в блок одну или несколько лишних строк снизу.

2.Поле. Задается название требуемого поля для суммирования. Это чаще всего ячейка с заголовком поля Сумма. Можно задать ссылку на ячейку заголовка поля или название поля в кавычках. Если используется функция подсчета строк, то поле можно выбирать произвольно.

3.Критерий. О критериях отбора рассказывалось в работе, посвященной фильтрации данных. Следует иметь в виду следующие моменты.

Вкритериях можно использовать условие типа больше (меньше) заданной даты. Будут выбраны соответственно более поздние и более ранние даты.

Вкритерии нельзя вставлять формулы, поэтому в нем не удается

использовать ссылку на дату или функцию СЕГОДНЯ(). Рекомендуется добавить в таблицы данных новые столбцы. Они бу-

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

Не забудьте присвоить новым столбцам имена.

При создании формулы разности дат Excel может ошибочно установить для этой ячейки формат даты. Установите вручную числовой формат и Вы увидите в ячейке количество дней между двумя датами.

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

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

Теперь можно строить критерий. Он будет указывать на определенный знак разности дат. В качестве условия установите >0 или <0.

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

Перед сдачей работы не забудьте красиво оформить таблицы отчетов.

Результат работы

Рабочая книга с двумя таблицами исходных данных и тремя отчетами.

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

10. Подбор параметра и поиск решения

Цель работы – освоение автоматических средств решения задач

Общие сведения

Электронная таблица позволяет не только производить расчеты, но и помогает в принятии решений. Она имеет черты DSS (decision support system) – системы поддержки принятия решений.

Подбор параметра

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

В то же время таблица расчета уже есть. Можно просто подставлять различные значения начисленной заработной платы и стараться, чтобы итог расчета совпал с заданным значением.

Excel позволяет автоматизировать эту задачу.

Еще более простой пример – определение квадратного (кубического) корня некоторого числа Х. Пусть это будет число 1234. При ручном расчете можно записать некоторое число в одну свободную ячейку, а в другую ввести формулу квадрата (куба) этого числа. Далее, вручную изменяя число в первой ячейке, можно добиться того, чтобы его квадрат (куб) стал равен Х (то есть 1234). Число в первой ячейке и будет решением задачи.

Для решения рассмотренных задач применяется опция меню Сервис Подбор параметра. Если правильно указать, что требуется сделать, подбор параметра будет произведен автоматически. В диалоговое окно требуется ввести три параметра:

ссылку на ячейку, в которой требуется сформировать результат (где должна находиться сумма 20000 рублей или где будет установлено число, квадратный (кубический) корень из которого надо найти – 1234); значение, которое должно находиться в этой ячейке (20000 или

1234);

ссылку на ячейку, которую надо изменять (ту, которую Вы пытались изменять вручную).

При нажатии кнопки ОК задача будет решена.

Этот инструмент позволяет изменять только одну ячейку так, чтобы в другой (единственной) ячейке установилось заданное значение.

Поиск решения

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

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

Отличие от предыдущих задач состоит в том, что приходится изменять уже не один, а два параметра (длину и ширину) одновременно. Нужно попытаться уменьшить периметр, следя за тем, чтобы площадь оставалась равной заданной величине. Это довольно сложно.

Поиск решения.

Окно поиска решения состоит из трех частей. В верхней части за-

дается одна целевая ячейка28.

Для этого требуется определить цель решения задачи. Это может быть минимизация длины забора (то есть затрат на его приобретение и установку) или максимизация огораживаемой площади.

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

Вторая часть указывает, какие ячейки требуется изменять. Для примера с участком это его длина и ширина.

В третьей части указываются ограничения. При добавлении нового ограничения появляется окно, показанное на Рис. 5.

Рис.5. Окно добавления ограничения

Ограничения определяются смыслом поставленной задачи. Либо это заданная площадь участка, либо длина имеющегося забора.

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

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

мый лучший, хороший, удовлетворительный…

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

Важно также точно указать вид неравенства. Если выделен участок 600 кв. м, то больше огородить недопустимо, а меньше – нежелательно. Поэтому для этого случая требуется указать строгое равенство. Однако бывают случаи, когда возможно и неравенство. Например, если планируется выпуск различных товаров с целью максимизации прибыли, то для каждого виде товара указывается ограничение количество не меньше 0. В данном случае отрицательное количество означает, что имеющийся товар пошел в переработку, что обычно связано с дополнительными затратами, не учитывающимися в задаче. Количество товара может быть равным нулю, если его невыгодно выпускать, или быть больше нуля.

Задание

Методом подбора параметра определить кубический корень из числа в пределах от -1000000 до 1000000. Повторить операцию для нескольких чисел.

Определить минимальную длину забора для прямоугольного участка в 600 кв. м.

Определить, какую прямоугольную площадь можно огородить прямоугольным забором длиной 100 м.

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

Подобрать начисленную сумму за январь так, чтобы сумма к выдаче за этот месяц равнялась 40000 р.

Разработать средства для решения задачи оптимальной загрузки рюкзака29.

а) Турист может перевезти авиарейсом 20 кг багажа. Он может купить три вида товаров: куртки, дубленки, сапоги. Каждый характери-

29 В математическом программировании задачи имеют различные названия,

например, задача коммивояжера, задача о Нюрнбергских мостах. Здесь рас-

сматривается задача, достаточно просторешаемая с помощьюсредств Excel.