Программинг

Элементы управления в Excel.

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

И так начнем.

Рассмотрим следующую задачу: Используя элементы управления флажок и поле со списком автоматизировать ввод и расчет товаров. Список товаров и их стоимость находится на листе “Товары”. Если у покупателя имеется скидка, стоимость приобретенных товаров уменьшается на 5%.

Результат решения данной задачи.

 

Решение: первый лист назовем Магазин, а второй Товары. На лист Товары поместим данные о товарах (название и стоимость).

 

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

·         Функция ИНДЕКС(массив;номер_строки;номер_столбца) - возвращает значение элемента таблицы или массива , заданного номером строки и номером столбца. Если массив содержит только одну строку или один столбец, то соответствующий аргумент номер_строки или номер_столбца не является обязательным.

·        Функция ЕСЛИ(лог_выражение;значение_если_истина ;значение_если_ложь) - возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Лог_выражение    — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае — ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения.

Значение_если_истина    — это значение, которое возвращается, если лог_выражение равно ИСТИНА. Например, если этот аргумент — строка «В пределах бюджета» и лог_выражение равно ИСТИНА, тогда функция ЕСЛИ отобразит текст «В пределах бюджета». Если лог_выражение равно ИСТИНА, а значение_если_истина пусто, то возвращается значение 0. Чтобы отобразить слово ИСТИНА, необходимо использовать логическое значение ИСТИНА для этого аргумента. Значение_если_истина может быть формулой.

Значение_если_ложь    — это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. Например, если этот аргумент — строка «Превышение бюджета» и лог_выражение равно ЛОЖЬ, то функция ЕСЛИ отобразит текст «Превышение бюджета». Если лог_выражение равно ЛОЖЬ, а значение_если_ложь опущено (то есть после значение_если_истина нет точки с запятой), то возвращается логическое значение ЛОЖЬ. Если лог_выражение равно ЛОЖЬ, а значение_если_ложь пусто (то есть после значение_если_истина стоит точка с запятой с последующей закрывающей скобкой), то возвращается значение 0. Значение_если_ложь может быть формулой.

Начнем с флажка. Если щелкнуть правой кнопкой маши по флажку, то откроется контекстное меню. В этом меню выбирем команду [Формат объекта]. Откроется окно.

 

Перейдем на вкладку [Элемент управления]. Здесь группа [Значение] определяет, стоит галочка или нет. Если галочка установлена, то элемент принимает значение ИСТИНА, если нет то, ЛОЖЬ. Поле [Связь с ячейкой] определяет адрес ячейки, в которой будет находиться значение флажка.

Думаю, что все понятно. Щелкаем по флажку и устанавливаем [значение] [снят], а в поле [Связать с ячейкой] адрес F6. Теперь в ячейке F6 будет находится соответствующее значение и для красоты установим [объемное затенение].

Теперь займемся полем со списком.

Аналогичным образом вызываем формат элемента управления [поля со списком].

[Формировать список по диапазону] – в этом поле указывается диапазон ячеек где находится наш список. Эти данные будут находиться в списке при его открытии.

При формировании списка каждому элементу присваивается номер 1, 2, 3, и т.д.

[Связать с ячейкой] – указывается адрес ячейки в которой будет находится номер выбранного элемента списка.

Выполним эти действия. Наш список товаров находится на листе Товары в диапазоне A2:A7. Введем в поле [Формировать список по диапазону] этот диапазон, но учитывая, что он находится на другом листе укажем его как внешнюю ссылку Товары!$A$2:$A$7. Где Товары – название листа, а $A$2:$A$7 диапазон адресов этого листа. ! является разделителем. Знак $ указывает на обсалютную адресацию и он в данном примере не обязателен.

В поле [Связать с ячейкой] введем адрес F2. В этой ячейки будет хранится номер выбранного элемента.

В ячейку С3 запишем формулу =ИНДЕКС(Товары!B2:B7;F2), где Товары!B2:B7 – диапазон ячеек, в котором определена стоимость товара  и F2 номер выбранного элемента списка. В результате как только мы выбираем из списка наименование товара в ячейке С3 отобразится его стоимость так как  в ячейки F2 находится номер выбранного элемента.

В ячейку  Е3 введем формулу =ЕСЛИ(F6;C3*D3-C3*D3*A7;C3*D3). Она вычисляет стоимость выбранного товара C3*D3 если флажок пустой и стоимость товара со скидкой C3*D3-C3*D3*A7 если на флажке стоит галочка.

Вот и все.