Описание выбора процессора

После подготовки листов Смета и Процессоры переходим к связыванию информации на разных листах рабочей книги. Нажмите кнопку на панели инструментов Формы.

Переместите указатель мыши к верхнему левому углу ячейки С5. После того, как крестик указателя совместится с этим углом ячейки С5, нажмите кнопку мыши и, не отпуская кнопку, проведите указатель до ячейки D5 где-то на треть ширины ячейки от левого ее края (см. рис. 4).

Поле со списком приняло нужный вид, но пока это = только рамка, а нам нужно в эту рамку вставить список процессоров.

В поле ввода диалога Формат элемента управления следует ввести диапазон ячеек А3: А7 листа Процессоры, а в поле ввода Связь с ячейкой – ячейку Е5 листа Смета. Нажмите кнопку ОК и проверьте работу созданного элемента управления.

Для отображения цен на выбранный процессор в ячейке В5 используется функция ИНДЕКС (). В этом диалоге (см.рис.6) в поле ввода Массив вводится диапазон ячеек В3: В7 с ценами на процессоры, в поле ввода Номер строки – ячейку Е5 листа Смета. Нажмите кнопку ОК.

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

Описание выбора конфигурации памяти

Установим элемент управления Счетчик на основном рабочем листе. Щелкните мышью на ярлычке Смета. Откроется основной рабочий лист расчета. Нажмите кнопку на панели инструментов Формы. Уста­новите Счетчик в ячейке D7.

Установка элемента управления Счетчик выполняется анало­гично установке Поля со списком, описанной ранее. Раз­мер поля элемента управления следует установить примерно в четверть ширины ячейки. Зададим параметры счетчика. Щелкните правой кнопкой мыши на поле Счетчика, в поя­вившемся контекстном меню выберите команду Формат объекта. На экране появится диалог Формат элемента управления. Введите значения полей, как показано на рис.7.

Рис.7

Текущее значение вводится в поле ввода с клавиатуры. Минимальное значение, Максимальное значение и Шаг изменения можно также вводить с клавиатуры, а можно менять с помощью стрелок в соответствующем поле. Значение в поле Связь с ячейкой заносится, как обычно.

Смысл параметров понятен из их названия. Они задают интервал зна­чений счетчика, начальное его значение и шаг изменения при каждом нажатии на стрелку. Интервал значений счетчика представляет собой количество строк списка конфигураций памяти. Результат - выбранное значение счетчика, - помещается в указанную ячейку для дальнейшего использования. В качестве такой ячейки возьмем ячейку Е7 на основ­ном рабочем листе. Нажмите ОК. Диалог Форматирование объекта ис­чезнет. В ячейке Е7 появится начальное значение счетчика - «I».

Нажмите верхнюю стрелку счетчика. Значение счетчика увеличится и в ячейке Е7 появится новое значение, - «2». Нажмите нижнюю стрелку счетчика. Значение счетчика уменьшится, в ячейке Е7 появится значение, - «I».

Теперь нам нужно поместить цену и название выбранной конфигурации памяти в ячейки В7 и С7 соответственно. Это мы уже делали ранее, ко­гда связывали название выбранного процессора с ячейкой В5. В данном случае наши действия аналогичны:

- выделите нужную ячейку;

- вызовите Мастер функций;

- выберите функцию ИНДЕКС;

- укажите рабочий лист- Память, и диапазон ячеек соответствующего списка, - колонку цен либо колонку названий;

- укажите ячейку, в которой находится номер выбранной в списке строки. Вы помните, что это Е7, - ячейка со значением счетчика;

- нажмите ОК. В выделенной ячейке появится выбранное в списке значение.

При связывании ячейки С7 с наименованием кон­фигурации памяти все аналогично, только диапазон ячеек A3: А7.

Итак, мы научились работать со списком данных посредством двух эле­ментов управления: Поле со списком и Счетчик. Вид рабочего листа в обоих случаях примерно одинаков. Усилия, затрачиваемые на подго­товку, также равноценны. Какой элемент применить в каждом конкрет­ном случае, - в основном, дело вкуса. При этом можно руководствовать­ся следующими рекомендациями:

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

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

Описание выбора винчестера и монитора

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

Расчет стоимости гарантии

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

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

Наша смета будет предусматривать два вида гарантии по усмотрению заказчика: бесплатная - сроком на полгода, или стоимостью 10% от це­ны компьютера - сроком на год.

Зададим это условие на рабочем листе Смета. Установим сначала пере­ключатель для первого вида гарантии. Нажмите кнопкуна панели инструментов Формы.

Переместите указатель мыши к верхнему левому углу ячейки С15. Нажмите кнопку мыши и, не отпуская кнопку, проведите указатель-крестик до левого верхнего угла ячейки D15. На рабочем листе поя­вится поле Переключателя в режиме редактирова­ния, со стандартным заголовком «Перекл....». Щелкните мышкой внутри рамки редактирования, удалите стан­дартный заголовок поля и введите новый заголовок: 6 мес.

Теперь нужно задать параметры элемента управления.

Щелкните правой кнопкой мыши на поле Переключателя, в появив­шемся контекстном меню выберите команду Формат объекта. На экране появится диалог Формат элемента управления

Введите значения полей, как показано на рис.9.

Нажмите ОК. Переключатель выделится, - кружок станет темным, а в ячейке Е 15 появится значение 1 - номер активного переключателя на рабочем листе.

Установим второй переключатель - для гарантии на год. Сначала осво­бодим для него место в ячейке. Щелкните правой кнопкой мыши на Переключателе «б мес.». Поя­вится контекстное меню. В поле элемента управления включится ре­жим редактирования.

Нажмите клавишу Esc, контекстное меню исчезнет.

Установите указатель мыши на левую границу элемента управления так, чтобы указатель принял форму двунаправленной стрелки. Пере­местите границу влево, - уменьшите размер Переключателя примерно до половины размера ячейки С15.

Теперь установим второй переключатель в ячейке С15, слева от перво­го. Установка производится аналогично описанной выше. Удалите стандартный заголовок переключателя и введите новый: 1 год. Измени­те, если нужно, размеры поля переключателя.

Вызывать диалог Формат элемента управления (Format Control) в дан­ном случае не нужно, - параметры второго переключателя настраивают­ся автоматически.

Щелкните мышкой на Переключателе «I год». Выделение перемес­тится с первого на второй переключатель. В ячейке Е15 появится значение «2» - номер активного (второго) переключателя.

Щелкните мышкой на Переключателе «б мес.». Выделение перемес­тится на первый переключатель. В ячейке Е15 восстановится значе­ние «I».

Теперь нам нужно, сделать следующие действия.

- во-первых, ввести в ячейку В 15 формулу расчета стоимости гарантии;

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

Поскольку стоимость гарантии зависит от стоимости компьютера, вы­числим сначала стоимость компьютера как сумму цен комплектующих элементов.

Выделите ячейку В13. Нажмите кнопку автосуммы S на панели ин­струментов Стандартная. В ячейке В13 и в строке формул появится формула, содержащая функцию суммирования.

Выделите мышкой диапазон ячеек В5: В11. Параметры функции СУММ примут нужные значения.

Нажмите кнопку Enter. Ввод формулы завершится, а в ячейке В13 появится значение суммарной стоимости комплектую­щих компьютера.

Теперь займемся формулой стоимости гарантии. Выделите ячейку В15. Введите формулу расчета: =В13*0, 1* (Е15-1)

Установите (активизируйте, щелкните мышкой) переключатель «I год». В ячейке В15 появится значение, равное 10% от стоимости компьютера.

Давайте разберемся с формулой. «В13*0, 1» - это понятно, 10% от сум­мы комплектующих. Второй сомножитель «(Е15-1)» даст нам 0, если в Е15 - значение 1 (установлен переключатель «б мес.»). Если же уста­новлен переключатель «I год», то в Е15 будет значение 2, «Е15 -1» бу­дет равно 1, и общее значение формулы будет равно «В13*0, 1», что нам и требовалось.

Описание учета стоимости доставки

Сейчас мы рассмотрим последний из используемых в нашей смете эле­ментов управления.

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

Если доставка нужна, то в стоимость компьютера требуется включить и стоимость доставки. Установим на рабочем листе Флажок для принятия решения о доставке, а затем введем формулу для обработ­ки принятого решения.

Нажмите кнопку на панели инструментов Формы.

Переместите указатель мыши к верхнему левому углу ячейки С17. Нажмите кнопку мыши и, не отпуская кнопку, проведите указатель-крестик вдоль границы строки примерно на две трети ширины ячей­ки С17. На рабочем листе появится поле Флажка в ре­жиме редактирования, со стандартным заголовком «Флажок....».

Щелкните мышкой внутри рамки редактирования, удалите стан­дартный заголовок поля и введите новый заголовок: «Нужна»

Зададим параметры элемента управления.

Введите значения полей, как показано на рис.10.

Введем формулу стоимости доставки в ячейку В17.

В ячейке В17 введите функцию ЕСЛИ. Введите значения параметров, как показано на рис.11.

Значение функции Если вычисляется на основе трех ее парамет­ров. Первый параметр - логическое выражение, которое может прини­мать значение ИСТИНА (TRUE) или ЛОЖЬ (FALSE). Если логическое выражение имеет значение ИСТИНА (TRUE), то значением функции бу­дет значение второго параметра, если же логическое выражение имеет значение ЛОЖЬ (FALSE), то значением функции будет значение третье­го параметра. В нашем случае логическим выражением будет значение ячейки Е17. Вы помните, что это значение формируется флажком дос­тавки. Стало быть, при установленном флажке в ячейке В17 (значение функции) будет значение 50, при снятом флажке - 0.

Нажмите ОК. Диалог описания параметров исчезнет. В ячейке В17 появится значение «50».

Щелкните мышью на квадратике Флажка (или на его заголовке). По­метка с Флажка снимется, и в ячейке В17 появится значение «О».

Щелкните мышью на Флажке еще раз (установите Флажок). В ячейке В17 снова появится значение «50».

Расчет обшей суммы стоимости компьютера

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

Подсчитаем сумму стоимостей комплектующих, гарантии и доставки. Введите в ячейку В19 формулу суммы: =В13+В15+В17

Введите в ячейку С19 формулу пересчета стоимости компьютера в гривны =Bl9*D3.. Вы помните, что в ячейке D3 у нас хранится теку­щий курс доллара.

Итак, смета выдала итоговую цифру. Подведем итоги.

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

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

План работы

1. В среде MS Excel составьте описанную выше смету для расчета компьютера заказной конфигурации.

2. Составьте аналогичную задачу для расчета стоимости другого сложного изделия, используя изученные элементы управления.

3. Результаты сохраните на диске в своей личной папке.