Редактирование формы в режиме Конструктора

1. Откройте форму Заказанный товар в режиме Конструктора (вкладка Главная).

2. Удалите из формы поле«Код типа» (маркируйте его и нажмите Del).

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

4. В открывшемся окне Создание группы переключателей введите подписи переключателей в соответствии с полем «Тип» (например, зимнее, летнее…). Нажмите кнопку Далее.

5. Во втором диалоговом окне определите, использовать ли какой-либо переключатель по умолчанию. Нажмите кнопку Далее.

6. Задайте значения переключателей в соответствии с полем «Код типа». Нажмите кнопку Далее.

7. Сохраните значения в поле «Код типа». Нажмите кнопку Далее.

8. Выберите тип элементов управления и задайте оформление типа. Нажмите кнопку Далее.

9. Выберите подписи для группы переключателей – Тип товара. Нажмите Готово.

10. Удалите поле «Единица измерения».

11. Выберите на панели элементов элемент Поле со списком.

12. В первом диалоговом окне выберите Фиксированный набор значений.

13. Во втором окне введите значения списка (например, шт., тыс. шт.).

14. Выберите способ применения выбранного значения Сохранить в поле Единица измерения.

15. Задайте подпись списка «Единица измерения» и нажмите Готово (рис. 1.8).

Рис. 1.8

Тема 6. Создание отчетов

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

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

Редактирование созданного отчета происходит в режиме Конструктора.

Технология работы

Создание отчета при помощи Мастера

1. В открытой БД перейдите на вкладку Создание – Мастер отчетов и нажмите.

2. В диалоговом окне Создание отчетов выберите запрос «Заказ клиента».

3. В списке Доступные поля выберите поля «Название», «Город», «Наименование», «Количество», «Единица измерения».

4. Из таблицы Типы – поле «Код типа». Нажмите Далее.

5. Выберите вид представления данных Заказ клиента, нажмите Далее.

6. В следующем окне диалога сгруппируйте данные по полю «Город» и нажмите Далее.

7. Выберите порядок сортировки по возрастанию для поля «Наименование».

8. Выберите требуемый вид макета отчета Ступенчатый, нажмите Далее.

9. В следующем окне выберите требуемый стиль представления отчета.

10. В последнем окне диалога задайте имя отчета и нажмите Готово.

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

Редактирование отчета при помощи Конструктора

1. Откройте созданный отчет в режиме Конструктора.

2. Удалите из отчета поле«Код типа» (маркируйте его и нажмите Del).

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

4. В открывшемся окне Создание группы переключателей введите подписи переключателей в соответствии с вашими типами. Нажмите Далее.

5. Во втором диалоговом окне определите, использовать ли какой-либо переключатель по умолчанию. Нажмите Далее.

6. Задайте значения переключателей. Нажмите Далее.

7. Сохраните значения в поле «Код типа». Нажмите Далее.

8. Выберите тип элементов управления и задайте оформление группы. Нажмите Далее.

9. Выберите подпись для группы переключателей – Тип товара. Нажмите Готово.

10. Просмотрите отчет в режиме Предварительного просмотра (рис. 1.9).

Рис. 1.9

ЧАСТЬ II. БАЗЫ ДАННЫХ И СПИСКИ
В MICROSOFT EXCEL 2007

Табличный редактор Excel дает возможность работать с базами данных с помощью некоторых своих инструментов. Например:

· контролировать тип данных можно путем организации Проверки данных;

· имитировать связи Access – путем использования функции ВПР, которая берет значение из связанной таблицы-справочника и возвращает его в оперативную таблицу;

· отчеты можно создавать с помощью Сводных таблиц и Промежуточных итогов.

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

Тема 1. Проверка данных

Команда Проверка данных защищает ячейки от ввода неверных данных. Чтобы задать условия проверки данных, выделите диапазон ячеек, к которому должны применяться эти условия. Затем в меню Данные, группа Работа с данными, выберите команду Проверка данных, и Excel откроет окно диалога Проверка вводимых значений. На вкладке Параметры этого окна диалога укажите тип и интервал значений, которые разрешается вводить. Вкладка Сообщение для ввода позволяет создать подсказку пользователю о том, какие данные можно вводить. На вкладке Сообщение об ошибке можно указать сообщение, которое будет появляться на экране при вводе некорректных данных.

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

Задание типа допустимых значений

Чтобы задать тип разрешенных значений, раскройте список Тип данных на вкладке Параметры окна диалога Проверка вводимых значений. Вы можете выбрать один из следующих типов: Любое значение, Целое число, Действительное, Список, Дата, Время, Длина текста и Другой. После выбора типа данных Excel изменит окно диалога так, чтобы вы могли ввести дополнительную информацию для выбранного типа. Например, для целых чисел Excel предложит ввести минимальное и максимальное значения.

Задание списка допустимых значений

Чтобы задать список допустимых значений, выберите пункт Список в раскрывающемся списке Тип данных и затем укажите диапазон рабочего листа, где находится ваш список. Например, чтобы пользователь мог ввести в ячейку А1 только слово Север, Запад, Юг или Восток, введите эти четыре значения в какой-нибудь части листа, скажем в диапазоне Z1:Z4. После этого выделите ячейку А1, выберите команду Проверка, в раскрывающемся списке Тип данных выберите пункт Список и затем в поле «Источник» укажите диапазон Z1:Z4.

Использование формулы для проверки данных

Чтобы данные проверялись с помощью формулы, в раскрывающемся списке Тип данных выберите пункт Другой и затем введите нужное выражение в поле «Формула». Например, чтобы значение в ячейке А1 было больше значения в ячейке В1, выделите ячейку А1, выберите команду Проверка, в раскрывающемся списке Тип данных выберите пункт Другой и затем введите выражение =А1>В1 в поле «Формула».

Если перед выбором команды Проверка выделен диапазон ячеек, то в вашей формуле должны быть соответствующим образом указаны относительные и абсолютные ссылки. Предположим, что вы хотите обеспечить правильный ввод данных в ячейки А1:А10 и для проверки значений используете формулу =A1>B1. В этом случае Excel проверяет условия А1>В1, А2>В2 и т. д. Но если вы зададите формулу =А1>$В$1, то Excel будет проверять выполнение А1>В1, А2>В1, АЗ>В1 и т. д.

Задание сообщения для ввода

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

Задание сообщения об ошибке

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

Кроме того, в раскрывающемся списке Вид вы можете выбрать тип сообщения об ошибке: Останов, Предупреждение или Сообщение. Эти три варианта отличаются значками, которые выводятся рядом с текстом сообщения, а также набором кнопок. Сообщение типа Останов (по умолчанию) позволяет пользователю повторить либо отменить ввод. Сообщение типа Предупреждение позволяет пользователю наряду с этими возможностями сохранить введенное некорректное значение. Если в списке Вид вы выберете пункт Сообщение, то пользователь сможет сохранить введенное значение или отменить ввод, но ему не будет разрешено повторить ввод.

Технология работы

1. Создайте новую Книгу в Excel.

2. Переименуйте «Лист1» в «Справочник поставщика».

3. Введите заголовок таблицы «Справочник поставщика».

4. Отформатируйте заголовок (объедините и поместите в центре ячеек А1:D1).

5. Отформатируйте шапку таблицы (перенос по словам + выравнивание по центру).

6. Введите информацию так, как это показано на рис.2.1.

Рис. 2.1

7. Присвойте имя группе ячеек А3:D7 – «Код_поставщика» (Формулы – Присвоить имя).

8. Переименуйте «Лист2» в «Справочник товаров».

9. Аналогично создайте таблицу Справочник товаров (рис. 2.2).

Рис. 2.2

10. Присвойте имя группе ячеек А3: С8 – «Код_товара».

11. Переименуйте «Лист3» в «Приходная накладная».

12. Создайте таблицу «Приходная накладная» (рис. 2.3). Отформатируйте заголовок и шапку.

Рис. 2.3

13. Организуйте контроль вводимых данных в поле «Код поставщика» через Проверку данных:

Тип данных – Целое число, Минимум – 100, Максимум – 105. Сообщение об ошибке – «Код поставщика может принимать значения от 100 до 105».

14. Аналогично организуйте контроль вводимых данных в поле «Код товара» на значения от 1001 до 1006.

15. Введите все исходные данные, кроме полей «Наименование поставщика» и «Наименование товара» (рис. 2.4).

Рис. 2.4

Тема 2. Связывание таблиц

Excel позволяет имитировать связи Access путем использования функции ВПР, которая берет значение из связанной таблицы-справочника и возвращает его в оперативную таблицу. Функция ВПР ищет значение в первом столбце массива таблицы и возвращает значение в той же строке из другого столбца массива таблицы.

Синтаксис функции:

ВПР (искомое_значение;таблица;номер_столбца;интервальный_просмотр)

Искомое_значение – значение, которое должно быть найдено в первом столбце табличного массива.. Если искомое значение меньше, чем наименьшее значение в первом столбце табличного массива, функция ВПР возвращает значение ошибки #Н/Д.

Таблица – два или более столбцов данных. Можно использовать ссылку на диапазон или имя диапазона. Значения в первом столбце аргумента «таблица» – это значения, в которых выполняется поиск аргумента «искомое_значение».

Номер_столбца – номер столбца в аргументе «таблица», из которого возвращается соответствующее значение. Если номер_столбца = 1,
то возвращается значение из первого столбца таблицы; если номер_столбца = 2 – значение из второго столбца таблицы и т. д.

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

Технология работы

1. Задайте заполнение графы «Наименование поставщика» в соответствии с Кодом поставщика (ключевое поле) через функцию ВПР:

а) встаньте в ячейку В3;

б) выберите функцию ВПР из категории «Ссылки и массивы»;

в) введите аргументы, как это указано на рис. 2.5 (синтаксис функции ВПР применительно к данному заданию описан в табл. 2.1);

г) скопируйте формулу в ячейки В4:В13.

Таблица 2.1

Наименование аргумента	Пояснения к заполнению
Искомое_значение	Значение из первого столбца таблицы, по которому будем искать Наименование поставщика, в данном случае это «Код поставщика»
Таблица	Таблица, в которой будем искать Наименование поставщика по полю «Код поставщика». В данном случае это таблица, которую мы назвали Код_поставщика
Номер_столбца	Номер столбца в таблице Код_поставщика, из которого нужно вернуть значение Наименования поставщика. В данном случае это столбец № 2 из таблицы Код_поставщика

Рис. 2.5

2. Аналогично задайте заполнение поля Наименование товара.

Тема 3. Сводные таблицы

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

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

1) выбрать таблицу или диапазон источника данных;

2) указать, куда следует поместить отчет сводной таблицы – на новый лист или существующий (с указанием диапазона);

3) построить макет сводной таблицы.

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

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

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

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

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

Рис. 2.6

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

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

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

Технология работы

1. Создайте ведомость «Фактическое выполнение поставок» (рис. 2.7):

а) установите курсор в поле таблицы «Приходная накладная»;

б) воспользуйтесь командой Сводная таблица из меню Вставка;

в) перенесите в область «Названия строк» поле «Наименование товара»;

г) перенесите в область «Значения» столбец Сумма поставки фактическая;

д) переименуйте лист со сводной таблицей в Фактическое выполнение поставок.

Рис. 2.7

Поле сводной таблицы играет роль фильтра данных. Каждое поле имеет команду «Все», используемую для отображения всех возможных значений поля. Используя сводную таблицу Фактическое выполнение поставок, получите данные для товара пальто:

е) сделайте активной страницу Фактическое выполнение поставок;

ж) нажмите стрелку правее поля «Название строк»;

з) отметьте товар Пальто.

2. Создайте ведомость «Сумма поставок по дням» (рис. 2.8):

а) установите курсор в поле таблицы «Приходная накладная»;

б) воспользуйтесь командой Сводная таблица из меню Вставка;

в) перенесите в область «Названия строк» поле «Дата поставки»;

г) перенесите в область «Названия строк» надпись «Наименование поставщика»;

д) перенесите в область «Фильтр отчета» надпись «Наименование товара»;

е) перенесите в область «Значения» поле «Сумма поставки фактическая»;

ж) переименуйте лист со сводной таблицей в «Поставки по дням».

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

Тема 4. Фильтрация списка

Фильтры обеспечивают выборку подмножества данных из списка. Имеются две разновидности этой команды: Автофильтр и Расширенный фильтр.

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

Рис. 2.8

Если условие фильтрации для значений в столбце более сложное, выбирается режим Настраиваемый фильтр, который выводит диалоговое окно Пользовательский автофильтр (рис. 2.9).

Рис. 2.9

Условие для отбора записей по значению в определенном столбце может состоять из двух самостоятельных частей, соединенных логической связкой И/ ИЛИ. Каждая часть условия включает:

· оператор отношения: равно, не равно, больше, больше или равно, меньше;

· значение, которое может выбираться из списка или содержать шаблонные символы «*» и «?».

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

Расширенный фильтр используется для более сложных критериев отбора. И по сравнению с командой Автофильтр имеет некоторые дополнительные возможности:

· допускается задавать условия, соединенные логическим оператором ИЛИ, для нескольких столбцов (оператор И расширенного фильтра аналогичен тому, если дважды применить Автофильтр);

· допускается задавать три и более условий для конкретного столбца;

· допускается задавать вычисляемые условия;

· позволяет сразу копировать отобранные строки без применения команд Копировать и Вставить.

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

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

Расширенный фильтр обеспечивает использование двух типов критериев для фильтрации записей списка:

· критерий сравнения;

· вычисляемый критерий.

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

Условие для отбора записей по значению в определенном столбце может состоять из двух самостоятельных частей, соединенных логической связкой И/ ИЛИ. Каждая часть условия включает:

· оператор отношения: равно, не равно, больше, больше или равно, меньше;

· значение, которое может выбираться из списка или содержать шаблонные символы «*» «и»?.

Технология использования расширенного фильтра включает два этапа:

1-й этап – формирование интервала критериев;

2-й этап – фильтрация записей списка.

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

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

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

3. Ниже меток располагаются критерии сравнения типа:

· точного значения;

· значения, формируемого с помощью операторов отношения;

· шаблона значения, включающего символы «*» и «?».

Правила формирования множественного критерия:

1) если критерии (условия) указываются в каждом столбце на одной строке, то они считаются связанными условием И;

2) если условия записаны в нескольких строках, то они считаются связанными условием ИЛИ.

Пример

1. Условие выбора записей о поставщике Аврора за 06.09.2008:

Наименование поставщика	Дата поставки
Аврора	06.09.2008

2. Условие выбора записей о поставщике ВосходИЛИ по товару Ткань:

Наименование поставщика	Наименование товара
Восход
	Ткань

3. Условие выбора записей о поставке товара Шуба за 07.09.2008 ИЛИ 08.09.2008:

Наименование товара	Дата поставки
Шуба	07.09.2008
Шуба	08.09.2008

Вычисляемый критерий. Вычисляемые условия отличаются от обычных условий сравнения тем, что позволяют использовать значения, возвращаемые формулой. Вычисляемый критерий представляет собой формулу, записанную в строке условий интервала критериев, которая возвращает логическое значение ИСТИНА или ЛОЖЬ. Формула строится с использованием: адресов ячеек, встроенных функций, констант различных типов (число, текст, логическая константа), операторов отношения.

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

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

· Ссылки на ячейки, находящиеся вне списка, должны быть абсолютными ($).

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

Пример

1. Выбрать записи о товарах с фактической суммой поставки ниже средней:

2. Выбрать записи о поставщике Аврора с суммой поставки фактической выше средней:

После завершения ввода вычисляемого критерия в ячейке должна появиться логическая константа ИСТИНА или ЛОЖЬ – результат применения сформированного вычисляемого критерия по отношению к первой записи списка.

2 этап. Фильтрация записей расширенным фильтром. После подготовки интервала критериев курсор устанавливается в список и на вкладке Данные, группа Сортировка и фильтрация, выбирается команда Дополнительно (рис. 2.10).

Рис. 2.10

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

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

Тема 5. Промежуточные итоги

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

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

Рис. 2.11

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

Рис. 2.12

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

Слева от таблицы на служебном поле появляются символы структуры: показа деталей (кнопка Плюс); скрытия (кнопка Минус); уровней структуры (кнопка Номера уровней 1, 2, 3 и т.д.); полос уровней структуры (объединение по принадлежности одному иерархическому уровню). Для открытия (закрытия) определенного уровня иерархии необходимо щелкнуть на номере. Для открытия определенной иерархической ветви нажимаются Плюс, Минус определенного элемента структуры, которые находятся слева от таблицы.

Рис. 2.13

Технология работы

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

1. Отсортировать таблицу по полям «Наименование поставщика» и «Дата поставки».

2. Построить итог первого уровня:

а) при каждом изменении в: Наименование поставщика;

б) операция: количество;

в) добавить итоги по: Наименование поставщика;

г) ОК.

3. Построить итог второго уровня:

а) при каждом изменении в: Дата поставки;

б) операция: сумма;

в) добавить итоги по: Сумма поставки фактическая;

г) убрать галочку – Заменить текущие итоги;

д) ОК.

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

Тема 6. Анализ «что–если»

Анализ «что–если» обеспечивает проверку различных значений для формул на листе. Существует три инструмента для проведения такого анализа.

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

2. Диспетчер сценариев. Позволяет создать и сохранить различные группы значений (сценарии) и переключаться между ними.

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

Таблицы данных

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

Таблицы для одной переменной. Если вы хотите вычислить месячные выплаты по ссуде 200 000 р. на 30 лет для нескольких процентных ставок, это можно сделать с помощью таблицы данных для одной переменной.

1. Создайте следующую таблицу: в ячейке С1 – 200 000, процентные ставки – в ячейках В3:В8 (рис. 2.14).

2. Введите формулу в ячейку С2 = ПЛТ(A2/12;30·12;C1).

Здесь: A2/12 – месячная процентная ставка; 30 – срок ссуды в годах, 30·12 – срок ссуды в месяцах; C1 – ссуда.

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

С1 – это пустая вспомогательная ячейка, куда впоследствии будет подставлена ссуда, т.е. та переменная, которая стоит в строке. В данном примере ссуда не является переменной, а имеет фиксированное значение, в следующем примере ссуда является переменной.

3. В результате в ячейке C2 появится сумма –555,56. Знак минус возникает из-за того, что это ваши ежемесячные выплаты по процентам, вы их отдаете.

4. Выделите диапазон таблицы данных В2:С8.

5. В меню Данные выберите команду Таблица данных. Выберите – Подставлять значения по строкам в, щелкните на ячейку A2. Результат –
на рис. 2.15.

Таблица с несколькими формулами. Если вы хотите взять ссуду 180 000 р. и сравнить с выплатами для ссуды в 200 000 р., то необходимо использовать несколько формул.

1. Введите в D1 – 180 000 р.

2. В ячейку D2 введите формулу = ПЛТ (A2/12;30·12;D1).

3. Выделите диапазон В2:D8.

4. В меню Данные выберите команду Таблица данных. Выберите – Подставлять значе-
ния по строкам в, щелкните на ячейку A2 (рис. 2.16).

Таблицы данных для двух переменных. Требуется узнать месячные выплаты для разных процентных ставок при сроках 5, 10, 15 и 20 лет.

1. Создайте следующую таблицу (рис. 2.17).

2. Величину ссуды введите в ячейку вне области таблицы, например, I2 – 1000000 р.

3. В ячейку В2 введите = ПЛТ (A2/12;B1·12;I2) (получится значение #ДЕЛ/0!).

4. Выделите диапазон В2:F8.

Рис. 2.17

5. В меню Данные выберите команду Таблица данных. Выберите – Подставлять значения по столбцам в, щелкните на ячейку В1, Подставлять значения по строкам в, щелкните на ячейку A2.

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

Диспетчер сценариев

Таблица данных – хорошее средство в относительно простых ситуациях, когда используются только одна или две переменные, но реальные задачи обычно имеют значительно больше неизвестных величин. При моделировании более сложных задач, в которых бывает до
32 переменных, можно обратиться к команде Сценарии.

Сценарии – это именованная комбинация значений, заданных для одной или нескольких изменяемых ячеек в модели «что–если». Изменяемые ячейки – это ячейки, содержащие значения, которые вы ходите использовать в качестве переменной.

Сценарием в Microsoft Excel называется набор значений подстановки, используемый для прогнозирования поведения модели. Существует возможность создания и сохранения в листе различных сценариев и переключения на любой из этих сценариев для просмотра результатов. Так, если требуется сформировать бюджет, но годовой доход точно не известен, то для дохода определяются различные значения, а затем для каждого сценария выполняется анализ «что–если».

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

Технология работы

1. Наберите математическую модель по заданному примеру
(рис. 2.18). Изменяемые параметры выделите в ячейках серым цветом. Сверьте пол