Практическая работа 13 Составление сметы и расчет стоимости заказа средствами MS Excel

Цель и ход работы

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

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

Рис. 1. Рабочий лист расчета цены на компьютер заказной конфигурации

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

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

Расчет будем проводить в несколько шагов:

1. подготовка основного текста сметы - заполнение рабочего листа, Смета

2. подготовка списка процессоров и выбор из списка с использованием элемента управления Поле со списком

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

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

5. расчет стоимости гарантии с использованием элемента управления Переключатель;

6. расчет стоимости доставки с использованием элемента управления Флажок;

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

Подготовка основного текста сметы

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

· Выделите ячейку Al.

· Введите текст Сборка компьютера.

· Введите текст в ячейки A3, А5, А7, А9, All, А13, А15, А17, А19, СЗ, как показано на Рис. 1

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

· Выделите диапазон ячеек Al: A19.

· Откройте список масштабов на панели инструментов Стандартная.

· Выберите строку По выделению. Масштаб отображения рабочего листа изменится так, что на экране поместится 19 строк, и в заголовке списка масштабов появится новое значение.

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

Введите новое название листа1 " Смета". Переименуйте остальные рабочие листы именами Процессоры, Память, Винчестеры, Мониторы.

Расположить заголовок по центру рабочего листа и выделить его полужирным шрифтом

Теперь установим ширину столбцов А и С так, чтобы текст в ячейках помещался полностью. Числовые значения выровним по правому краю.

Установить текущую дату.

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

Последний шаг в опыте - ввод курса доллара.

· Выделите ячейку D3.

· Введите текущий курс доллара.

Выбор процессора

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

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

Введите данные на листе Процессоры как показано на рис. 2

Слева в столбце А - названия типов процессоров, справа, в столбце В - цены в долларах.

Рис. 2. Список типов процессоров и их цен

· Щелкните мышью на ярлычке Смета. Вы вернетесь к основному рабочему листу расчета.

· Выберите команду меню Вид • Панели инструментов • Формы. На экране поверх рабочего листа появится новая панель с элементами управления.

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

Вот теперь начинается самое интересное - связывание информации на разных листах рабочейкниги.

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

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

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

· Заметьте, что при перемещении указатель рисует прямоугольник на рабочем листе, - контур будущего элемента управления. Этот контур должен закрывать ячейку С5.

Все так же не отпуская кнопку мыши, установите контур прямоугольника, как показано на Рис. 3. Отпустите кнопку. Элемент управления Поле со списком установится на рабочем листе.

Рис.3 Установка элемента Поля со списком

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

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

· Щелкните правой кнопкой мыши на прямоугольнике элемента управления. Появится контекстное меню элемента управления.

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

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

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

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

· Щелкните правой кнопкой мыши на прямоугольнике элемента управления. Появится контекстное меню для элемента управления.

· Не перемещая указатель, нажмите клавишу Esc или щелкните левой кнопкой мыши. Контекстное меню закроется, а элемент управления останется выделенным, и мы сможем его отредактировать.

· Установите указатель мыши внутри области элемента управления. Указатель примет вид крестика с четырьмя стрелками.

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

· Установите пунктирный контур в нужном положении, - так, чтобы его левый верхний угол совместился с левым верхним углом ячейки С5. Отпустите кнопку мыши. Пунктирный контур исчезнет, а прямоугольник элемента управления примет нужное положение.

· Установите теперь указатель мыши на один из миленьких квадратиков- на нижней границе элемента управлении, Указатель примет вид двунаправленной стрелки.

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

· Установите нужный размер. Отпустите кнопку мыши. Пунктирный контур исчезнет, а прямоугольник элемента управления примет нужный размер по вертикали.

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

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

· Щелкните еще раз правой кнопкой мыши на созданном элементе управления. В появившемся контекстном меню выберите команду Формат объекта. На экране появится диалог Формат элемента управления (Рис. 4). По умолчанию в диалоге выбрана вкладка Элемент управления.

Рис. 4 Диалог Формат элемента управления

· Если у вас выбрана другая вкладка, щелкните мышью на вкладке Элемент управления.

· Нажмите кнопку в поле ввода Формировать список по диапазону. Диалог Формат элемента управления свернется в однострочное поле ввода (Рис. 5), в котором появится текстовый курсор.

Рис. 5 Диалог Формат элемента управления в свернутом виде

Уменьшение размера диалога в данный момент весьма целесообразно, -таким образом рабочие листы становятся доступными для манипуляций с ними. Для каких манипуляций? - Сейчас увидим

· Щелкните мышью на ярлычке Процессоры. Откроется рабочий лист Процессоры. В поле ввода диалога Формат элемента управления появится строка «Процессоры! ».

· Выделите на рабочем листе диапазон ячеек A3: А7 с наименованиями типов процессоров. Строка в поле ввода диалога примет вид «Процессоры! $А$3: $А$7».

Только что мы указали диапазон ячеек, которые будут просматриваться через наше Поле со списком на основном рабочем листе сметы. Эту ссылку на ячейки можно было задать и по-другому: ввести с клавиатуры в поле ввода Формировать список по диапазону указанную строку, содержащую имя рабочего листа «Процессоры! » и диапазон ячеек на этом листе «$А$3: $А$7». Заметьте, что ячейки задаются с абсолютными адресами. Но продолжим подготовку списка.

· Нажмите кнопку диалога Формат элемента управления. Диалог снова развернется в полном размере (Рис.4). При этом он изменит свое название на Форматирование объекта, но пусть это вас не смущает - это все тот же диалог.

· Нажмите кнопку в поле ввода Помещать результат в ячейку. Диалог Форматирование объекта снова сверяется в однострочное поле ввода (Рис.5), в котором появится текстовый курсор. При этом в рабочей книге откроется лист Смета, элемент управления которого мы форматируем.

· Щелкните мышью на ячейке Е5. Границы ячейки выделятся пунктиром, а в поле ввода диалога Форматирование объекта появится строка «$Е$5».

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

· Нажмите кнопку диалога Форматирование объекта. Диалог снова развернется в полном размере.

· Нажмите кнопку ОК. Диалог Форматирование объекта исчезнет с экрана. В форматируемом Поле со списком все еще включен режим редактирования: границы поля помечены квадратиками.

· Щелкните мышью на какой-нибудь ячейке, скажем С8, чтобы отменить выделение элемента управления. Границы Поля со списком примут нормальный рабочий вид, - без квадратиков.

· Нажмите кнопку списка. Список откроется, как показано на Рис. 6.

· Выберете строку «Р200». В ячейке Е5 появится номер выбранного элемента в списке - «4».

В диалоге Форматирование объекта мы не изменяли значение поля Количество строк списка. Этот параметр определяет количество строк в списке, открывающемся из Поля со писком (Рис. 6). Однако, размер открывающегося списка не может быть меньше размера списка-источника. В нашем случае источник –прайс-лист процессоров, содержащий 5 строк, поэтому раскрывающийся список такжеимеет размер в 5 строк. Заполнять поле Количество строк списка нужно в том случае, когда список-источник - достаточно большего размера. Однако, цель нашего опыта еще не достигнута. Мы уже можем выбирать нужный процессор, но в конечном итогетребуется поместим цену этого процессора в ячейку на основном рабочем листе для дальней обработки. Поместим цену процессора в ячейку В5.

Рис. 6 Просмотр списка процессоров

Вызовите мастер функции

Выберите строку Ссылки и массивы в списке Категория. В списке Функция выберите строку ИНДЕКС.

Нажмите ОК. На экране появится следующий диалог Мастера функций. По умолчанию в нем выделена строка массив; номер_строки; номер_столбца. Нам нужна именно эта функция.

Нажмите ОК. На экране появится диалог для задания параметров функции ИНДЕКС (Рис. 7).

Рис. 7 Диалог для задания параметров функции ИНДЕКС

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

Нажмите кнопку в поле ввода Массив. Диалог задания параметров функции свернется в однострочное поле ввода (Рис. 8). Данное поле ввода довольно неприметно, в нем даже нет заголовка окна. Найти его на экране можно по двум признакам: во-первых, в нем будет находиться текстовый курсор; во-вторых, это поле будет расположено на месте верхней части предыдущего диалога, - как раз под строкой формул окна Excel.

Рис. 8 Поле ввода параметров функций

· Щелканите мышью на ярлычке Процессоры. Откроется рабочий лист Процессоры. В поле ввода параметров функции появится строка " Процессоры! ".

· Выделитена рабочем листе диапазон ячеек ВЗ: В7 с ценами процессоров. Строка в поле ввода параметров функции примет вид " Процессоры! В3: В7".

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

· Нажмите кнопку в поле ввода. Диалог задания параметров снова развернется в полном размере (рис.8).

· Нажмите кнопку в поле ввода Номер строки. Диалог свернется в однострочное поле ввода с текстовым курсором (рис.9). При этом откроется рабочий лист Смета.

· Щелкните мышью на ячейке Е5. Границы ячейки выделятся пунктиром, а в поле ввода параметров появится строка «Е5».

· Нажмите кнопку ОК. Диалог ввода параметров исчезнет с экрана. В ячейке В5 появится цена выбранного процессора «180».

Откуда она там взялась? Мы выбрали наименование процессора в прайс-листе Процессоры, 4-я строка из диапазона ячеек A3: А7. Номер выбранной строки - 4, - занесли в ячейку Е5 на основном рабочем листе. И поместили в ячейку В5 строку 4 (4 - значение Е5) из диапазона ВЗ: В 7 на листе Процессоры. Проверим работу выстроенной конструкции.

· Откройте список процессоров и выберите строку «Р200 ММХ». В строке Поля со списком появится выбранное наименование, в ячейке Е5 - значение номера строки «5». В ячейке В5 появится соответствующая цена - «250».

· Пожалуй, на рабочем листе уже есть лишняя деталь. Ячейка Е5 служит для технических надобностей; номер строки, который в ней хранится, лучше не показывать. Давайте сделаем содержимое этой ячейки невидимым.

· Выделите ячейку Е5. Откройте список цветов шрифта на панели инструментов Форматирование. Выберите белый цвет. Текст в ячейке Е5 станет белым и потому - невидимым на белом фоне ячейки.

Заметьте, что в строке формул по-прежнему остается значение «5». То есть, содержимое ячейки - невидимо, но значение ячейки не исчезло, его можно использовать в формулах рабочего листа, что мы и делаем.

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

· Щелкните правой кнопкой мыши на ячейке В5. На экране появится контекстное меню.

Выберете команду Формат Ячеек (рис. 9). Выберете вкладку Число

Рис. 9 Диалог Формат ячеек

· В списке Числовые форматы выберите строку (все форматы). Дважды щелкните мышью на поле ввода Тип Содержимое поля ввода выделится цветом.

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

· Нажмите ОК. Диалог Формат ячеек исчезнет с экрана. В ячейке В5 стоимость выбранного процессора будет показана в следующем виде: $250.

Выбор конфигурации памяти

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

1. подготовим на отдельном рабочем листе прайс-лист по имеющимся в наличии конфигурациям памяти;

2. установим на основном рабочем листе элемент управления, с помощью которого будем просматривать список наименований прай-листа;

3. свяжем ячейку на основном рабочем листе с ценой выбранной кон фигурации;

4. оформим надлежащим образом внешний вид ячеек.

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

· Щелкните мышью на ярлычке рабочего листа Память. На экране откроется пустой рабочий лист.

Введите значения объемов оперативной памятииих цен, как показано на Рис. 10 Слева, в столбце А - объемы памяти, справа, в столбце В - цены в долларах.

Рис. 10 Список конфигураций памяти и их цен

Теперь установим элемент управления Счетчик наосновномрабочем листе.

· Щелкните мышью на ярлычке Смета. Откроется основной рабочий лист расчета.

· Нажмите кнопку на панели инструментов Формы. Установите Счетчик в ячейке D7.

Установка элемента управления Счетчик выполняется аналогично установке Поля со списком, описанной ранее. Размер поля элемента управления следует установить примерно в четверть ширины ячейки.

Зададим параметры счетчика

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

· Введите значения полей, как показано на Рис. 11.

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

Рис. 11 Диалог Формат элемента управления - параметры Счетчика

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

· Нажмите ОК. Диалог Форматирование объекта исчезнет. В ячейке Е7 появится начальное значение счетчика, -" 1".

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

· Нажмите нижнюю стрелку счетчика. Значение счетчика уменьшится, в ячейке Е7 появится значение –" 1"

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

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

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

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

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

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

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

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

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

Рис. 12 Параметры функции ИНДЕКС для цены выбранной конфигурации памяти

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

· Щелкните правой кнопкой мыши на ячейке В7. В появившемся контекстном меню выберите команду Формат ячеек. Появится диалог Формат ячеек (Рис. 11).

· Выберите вкладку Число, В списке Числовые форматы выберите строку (все форматы). В списке Тип (в конце списка) найдите и выделите добавленную ранее строку «$# ##0». Нажмите ОК. В ячейке В7 перед ценой появится знак доллара.

· Выделите ячейку Е7. Измените цвет шрифта на белый аналогично ячейке Е5. Текст в ячейке Е7 станет белым и потому - невидимым на рабочем листе.

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

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

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

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

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

· Введите на рабочем листе Винчестеры наименования и цены типов винчестеров, как показано на Рис. 13. Слева, в столбце А, наименования, справа, в столбце В - цены в долларах.

· Вернитесь на основной рабочий лист. Установитеэлементуправления Счетчик в ячейке D9.

Рис. 13 Список типов винчестеров и их цен

Задайте параметры счетчика, как показано на рис. 14

Рис. 14 Параметры Счетчика для списка винчестеров

Свяжите ячейку В9 со списком цен винчестеров на рабочем листе Винчестеры. Связывание произведите с помощью функции ИНДЕКС, при этом задайте параметры функции, как показано на Рис. 15.

Рис. 15 Параметры функции ИНДЕКС для цены выбранного винчестера

· Свяжите ячейку С9 со списком наименований винчестеров, - диапазон ячеек A3: А7 на рабочем листе Винчестеры.

· Теперь добавим знак доллара к цене в ячейке В9.

· Закончим оформление элемента управления и проверим его работу.

· Сделайте невидимым содержимое ячейки Е9 аналогично ячейкам ЕЗ и Е7.

· Выберите через счетчик винчестер емкостью 3 Гб.

Элемент управления Счетчик для выбора винчестера установлен на основном рабочем листе. Займемся теперь выбором монитора.

· Введите на рабочем листе Мониторы данные по типам мониторов как показано на Рис. 16 слева, в столбце А, - названия типов мониторов, справа, в столбце В - цены в долларах.

Рис. 16 Список типов мониторов и их цен

· Вернитесь на основной рабочий лист. Установите элемент управления Поле со списком для выбора монитора на основном рабочем листе

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

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

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

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

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

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

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

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

· Щелкните мышкой внутри рамки редактирования, удалите стандартный заголовок поля и введите новый заголовок: 6 мес.. Рабочий лист примет вид, как на Рис. 18.

Рис. 18 Установка элемента управления Переключатель

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

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

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

Рис. 19 Параметры поля Переключатель «6 мес.»

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

Установим второй переключатель - для гарантии на год. Сначала освободим для него место в ячейке.

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

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

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

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

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

Проверим функционирование переключателей.

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

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

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

1. ввести в ячейку В15 формулу расчета стоимости гарантии;

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

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

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

· Выделите мышкой диапазон ячеек В5: В11. Параметры функции Сумм примут нужные значения- значение суммарной стоимости комплектующих компьютера.

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

· В ячейке В15 появится значение «0».

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

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

Теперь нужно добавить знак доллара к сумме в ячейке В15, и сделать невидимым значение ячейки Е15. Сделайте это, аналогично предыдущим опытам, и вы получите рабочий лист, как на Рис. 20.

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

Учет стоимости доставки

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

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

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

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

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

· Щелкните мышкой внутри рамки редактирования, удалите стандартный заголовок поля и введите новый заголовок: Нужна. Рабочий лист примет вид, как на Рис. 21.

Рис. 21 Установка элемента управления Флажок

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

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

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

Рис. 22 Параметры элемента управления Флажок

· Нажмите ОК. Диалог исчезнет с экрана, Флажок установится (внутри квадратика появится значок-пометка), а в ячейке ЕЗ появится значение «ИСТИНА» - значение установленного флажка.

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

· Введите значения параметров функции ЕСЛИ, как показано на Рис. 23.

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

Рис. 23 Параметры функции ЕСЛИ

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

· Добавьте знак доллара к сумме в ячейке В17 и сделайте невидимым значение ячейки Е17, как в предыдущих опытах. Рабочий лист примет вид, как на Рис. 24.

Рис. 24. Учет стоимости доставки

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

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

⇐ Предыдущая 8 9 10 11 121314 15 16 17 Следующая ⇒