 ПОЗНАВАТЕЛЬНОЕ Сила воли ведет к действию, а позитивные действия формируют позитивное отношение Как определить диапазон голоса - ваш вокал Игровые автоматы с быстрым выводом Как самому избавиться от обидчивости Противоречивые взгляды на качества, присущие мужчинам Вкуснейший "Салат из свеклы с чесноком" Натюрморт и его изобразительные возможности Применение, как принимать мумие? Мумие для волос, лица, при переломах, при кровотечении и т.д. Как научиться брать на себя ответственность Зачем нужны границы в отношениях с детьми? Световозвращающие элементы на детской одежде Как победить свой возраст? Восемь уникальных способов, которые помогут достичь долголетия Классификация ожирения по ИМТ (ВОЗ) Глава 3. Завет мужчины с женщиной
Оси и плоскости тела человека - Тело человека состоит из определенных топографических частей и участков, в которых расположены органы, мышцы, сосуды, нервы и т.д.
| Описание выбора процессора
Лабораторная работа № 12 Тема: «Составление смет и расчет стоимости заказов с использованием элементов управления MS Excel»
Цель работы: практическое освоение разработки рабочих листов с использованием элементов управления в среде электронной таблицы Excel для решения различных задач учета.
Теоретическая часть.
В работе многих предприятий постоянно возникает необходимость быстро подсчитать стоимость какого-либо заказа. Это может быть сборка компьютера заказной конфигурации, печать издания в типографии или смета на ремонт квартиры. Рассмотрим процесс расчета цены на сборку компьютера заказной конфигурации. Конечный вид рабочего листа представлен на рисунке 1.
 Этот рабочий лист назовем Смета,на нем будут выполняться все действия и расчеты. На дополнительных листах Процессоры, Память, Винчестеры, Мониторыбудут размещены соответствующие прайс- листы комплектующих.
На рисунке 2 приведено содержимое прайс-листов, которые должны быть размещены на соответствующих листах рабочей книги.
Для выбора комплектующих будут использоваться элементы управления из панели инструментов Формы (см. рис. 3) и функции Excel ИНДЕКС(). Если этой панели инструментов нет на экране, то ее следует включить используя меню Вид Þ Панели инструментов.
Расчет производится в несколько шагов. 1. Подготовка основного текста сметы – заполнение рабочего листа Смета (заголовки сметы и названий входящих в список). В ячейке D3 – фиксированное значение, В3 – функция СЕГОДНЯ(), В13 – сумма комплектующих, В19 – с гарантией и доставкой, С19 – конечная стоимость заказа в гривнах, пересчитанная по курсу. В точном соответствии с рис.2 готовятся рабочие листы Процессоры, Память, Винчестеры, Мониторы. 2. Подготовка списка процессоров и выбор из списка с использованием элемента управления Поле со списком. 3. Подготовка списка конфигураций памяти и выбор из списка с использованием элемента управления Счетчик. 4. Подготовка списка винчестеров и выбор из списка с использованием элемента управления Счетчик. 5. Подготовка списка мониторов и выбор из списка с использованием элемента управления Поле со списком. 6. Выбор из этих списков и суммирование стоимостей всех комплектующих. 7. Расчет стоимости гарантии с использованием элемента управления Переключатель. 8. Окончательный расчет общей стоимости заказа - суммирование стоимости компьютера, стоимости гарантии и доставки, определение стоимости в гривнах.
Описание выбора процессора
Переместите указатель мыши к верхнему левому углу ячейки С5. После того, как крестик указателя совместится с этим углом ячейки С5, нажмите кнопку мыши и, не отпуская кнопку, проведите указатель до ячейки D5 где-то на треть ширины ячейки от левого ее края (см. рис. 4).
Щелкните правой клавишей мыши на созданном элементе управления. В появившемся контекстном меню выберите команду Формат объекта. На экране появится диалог Формат элемента управления (рис. 5).
В поле ввода диалога Формат элемента управления следует ввести диапазон ячеек А3:А7 листа Процессоры, а в поле ввода Связь с ячейкой – ячейку Е5 листа Смета. Нажмите кнопку ОК и проверьте работу созданного элемента управления. Для отображения цен на выбранный процессор в ячейке В5 используется функция ИНДЕКС(). В этом диалоге (см.рис.6) в поле ввода Массив вводится диапазон ячеек В3:В7 с ценами на процессоры, в поле ввода Номер строки – ячейку Е5 листа Смета. Нажмите кнопку ОК. Теперь вы выбираете из списка процессор, а рядом в ячейке появляется соответствующая ему цена. Ячейка Е5 вспомогательная и может быть в дальнейшем скрыта.
Установка элемента управленияСчетчик выполняется аналогично установкеПоля со списком, описанной ранее. Размер поля элемента управления следует установить примерно в четверть ширины ячейки. Зададим параметры счетчика. Щелкните правой кнопкой мыши на полеСчетчика, в появившемся контекстном меню выберите командуФормат объекта. На экране появится диалогФормат элемента управления. Введите значения полей, как показано на рис.7.
Текущее значение вводится в поле ввода с клавиатуры. Минимальное значение,Максимальное значениеиШаг изменения можно также вводить с клавиатуры, а можно менять с помощью стрелок в соответствующем поле. Значение в полеСвязь с ячейкойзаносится, как обычно. Смысл параметров понятен из их названия. Они задают интервал значений счетчика, начальное его значение и шаг изменения при каждом нажатии на стрелку. Интервал значений счетчика представляет собой количество строк списка конфигураций памяти. Результат - выбранное значение счетчика, - помещается в указанную ячейку для дальнейшего использования. В качестве такой ячейки возьмем ячейку Е7 на основном рабочем листе. Нажмите ОК. Диалог Форматирование объекта исчезнет. В ячейке Е7 появится начальное значение счетчика - «I». Нажмите верхнюю стрелку счетчика. Значение счетчика увеличится и в ячейке Е7 появится новое значение, - «2». Нажмите нижнюю стрелку счетчика. Значение счетчика уменьшится, в ячейке Е7 появится значение, - «I». Теперь нам нужно поместить цену и название выбранной конфигурации памяти в ячейки В7 и С7 соответственно. Это мы уже делали ранее, когда связывали название выбранного процессора с ячейкой В5. В данном случае наши действия аналогичны: - выделите нужную ячейку; - вызовитеМастер функций; - выберите функцию ИНДЕКС; - укажите рабочий лист- Память, и диапазон ячеек соответствующего списка, - колонку цен либо колонку названий; - укажите ячейку, в которой находится номер выбранной в списке строки. Вы помните, что это Е7, - ячейка со значением счетчика; - нажмитеОК. В выделенной ячейке появится выбранное в списке значение.
При связывании ячейки В7 с ценой памяти задайте параметры, как показано на рис.8. При связывании ячейки С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. Результаты сохраните на сетевом диске в своей личной папке.
|
После подготовки листов Смета и Процессоры переходим к связыванию информации на разных листах рабочей книги. Нажмите кнопку на панели инструментов Формы.
Поле со списком приняло нужный вид, но пока это = только рамка, а нам нужно в эту рамку вставить список процессоров.
Установим элемент управленияСчетчик на основном рабочем листе. Щелкните мышью на ярлычке Смета. Откроется основной рабочий лист расчета. Нажмите кнопку на панели инструментовФормы. Установите Счетчик в ячейке D7.
Зададим это условие на рабочем листеСмета. Установим сначала переключатель для первого вида гарантии. Нажмите кнопкуна панели инструментов Формы.
Нажмите кнопку на панели инструментов Формы.
