Лабораторная работа №5. Макросы

Цель работы – овладеть практическими навыками создания макросов с использованием средств автоматического создания макросов.

 

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

 

Создание макроса.

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

1. Для отображения вкладки Разработчик, выполнить следующие действия:

- нажать кнопку Microsoft Office , а затем щелкнуть Параметры Excel;

- в категории Личная настройка в группе Основные параметры работы с Excel установить флажок Показывать вкладку " Разработчик" на ленте, а затем нажать кнопку ОК.

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

- на вкладке Разработчик в группе Код нажать кнопку Безопасность макросов;

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

3. На вкладке Разработчик в группе Код нажать кнопку Запись макроса.

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

5. Чтобы назначить в сочетании с клавишей CTRL клавишу быстрого вызова (Клавиши быстрого вызова. Функциональные клавиши или сочетания клавиш, такие как F5 или CTRL+A, которые служат для выполнения команд меню. В отличие от них, клавишами доступа называют сочетания клавиш, такие как ALT+Ф, которые переводят фокус в меню, на команду или на элемент управления.) для запуска макроса, в поле Сочетание клавиш ввести любую строчную или прописную букву.

6. В списке Сохранитьвыбрать книгу, в которой необходимо сохранить макрос.

7. Ввести описание макроса в поле Описание.

8. Для начала записи макроса нажать кнопку ОК.

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

10. На вкладке Разработчик в группе Код нажать кнопку Остановить запись .

 

Выполнение макроса.

Способ.

1. Перейти на вкладку Разработчик.

2. В группе Код нажать кнопку Макросы.

3. В окне Макросы из списка Имя макроса выделить нужный макрос и нажать кнопку Выполнить.

2 способ. С помощью комбинации клавиш < Ctrl> +< буква>, заданных при записи макроса.

3 способ. С помощью элемента управления.

Назначение макроса элементу управления.

1. Для создания элемента управления на листе книги:

- перейти на вкладку Разработчик;

- в группе Элементы управления открыть список Вставить;

 

- из группы Элементы управления формы нажать элемент Кнопка ;

- нарисовать кнопку на свободном месте листа.

2. В окне Назначить макрос объекту в поле Имя макроса выделить нужный макрос и нажать кнопку ОК.

Удаление макроса.

1. На вкладке Разработчик в группе Код нажать кнопку Макросы.

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

3. Нажать кнопку Удалить.

 

Задание №33. Напишите макрос вычисления Суммарной выручки на листе Выручка.

1. Перейдите на лист Выручка.

2. Удалите числовые данные столбца Суммарная выручка

3. Поместите курсор в любой ячейке, расположенной рядом с таблицей.

4. Подключите, если это необходимо, вкладку Разработчик (смотри выше).

5. Перейдите на вкладку Разработчик.

6. В группе Код нажмите кнопку Запись макроса.

7. В окне Запись макроса в поле Имя макроса введите Суммарная_выручка (в имени макроса нельзя использовать пробел, заменим его знаком подчеркивания). В поле Сочетание клавиш введите любую букву. Нажмите кнопку ОК.

8. Теперь все выполняемые действия будут записаны в макросе:

-  щелкните в ячейке F5;

- перейдите на вкладку Главная;

- из списка Автосумма выберите функцию Сумма;

- автоматически выделится нужный диапазон ячеек C5: E5 – аргументов функции. Нажмите кнопку ОК;

- скопируйте формулу вниз;

- остановите запись нажатием на кнопку Остановить запись в группе Код на вкладке Разработчик.

9. Очистите столбец Суммарная выручка от числовых данных.

10. Проверьте выполнение макроса через окно Макросы в группе Код на вкладке Разработчик.

11. Еще раз очистите столбец Суммарная выручка от числовых данных.

12. Проверьте выполнение макроса с помощью комбинации клавиш.

 

Задание №34. Самостоятельно создайте элемент управления Кнопку для быстрого вызова макроса Суммарная_выручка.

Задание №35. Самостоятельно создайте макрос вычисления Средней выручки от продажи путевок. Назначьте макросу кнопку.

Лабораторная работа №6. Логическая Функция Если

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

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

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

 

=ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь)

 

Логические выражения используются для записи условий, в которых сравниваются числа, функции, формулы, текстовые или логические значения. Любое логическое выражение должно содержать хотя бы один оператор сравнения, который определяет отношение между элементами логического выражения. Список операторов сравнения MS Excel:

= Равно

> Больше

< Меньше

> = Больше или равно

< = Меньше или равно

< > Не равно

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

Функция Если возвращает значение_если_истина, если результатом логического выражения будет ИСТИНА, в противном случае – значение_если_ложь.

 

Задание №36. С помощью функции Если выведите сообщение о том, какое число введено в ячейке – положительное или отрицательное.

 

1. Вставьте новый лист. Переименуйте его в Логическая функция.

2. Введите следующие числа

3. Активизируйте ячейку B1.

4. Перейдите на вкладку Формулы. Нажмите в группе Библиотека функций кнопку Логические. Из списка выберите функцию Если.

5. В диалоговом окне Аргументы функции введите следующие аргументы:

 

- в поле Лог_выражение введите A1> 0;

- в поле Значение_если_истина введите текст Число положительное;

- в поле Значение_если_ложь введите текст Число отрицательное;

- нажмите кнопку ОК.

6. Скопируйте формулу вниз. Результат выполненных действий

Функции И, ИЛИ, НЕ

Функции И (AND), ИЛИ (OR), НЕ (NOT)  позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:

=И(логическое_значение1; логическое_значение2...)

=ИЛИ(логическое_значение1; логическое_значение2...)

 

Функция НЕ имеет только один аргумент и следующий синтаксис:

=НЕ(логическое_значение)

 

Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

 

Задание №37. С помощью функции Если проанализируйте, кто из студентов будет получать стипендию. Условие получения стипендии – Все дисциплины должны быть сданы на 4 и 5.

1. Перейдите на лист Успеваемость.

2. В ячейку E3 введите название столбца Успеваемость.

3. Активизируйте ячейку F4.

4. Вызовите функцию Если.

5. В логическом выражении необходимо проанализировать оценку каждой дисциплины, поэтому для объединения 3 условий используем функцию И:

- вызовите функцию И. Для этого нажмите в поле Функция справа в строке формул, откроется список Функция, из списка выберите Другие функции;

 

Список функций

 

- появится окно Мастера функций с открытой категорией Логические. Из списка функций выберите функцию И;

- появится окно аргументов функции И;

- введите следующие логические выражения.

 

ВНИМАНИЕ! Не нажимайте кнопку ОК

 

6. Для отображения окна аргументов функции Если щелкните в строке формул по названию функции. В окне в поле Логическое выражение отобразится сложное условие, состоящее из 3 логических выражений, объединенных функцией И, которое читается следующим образом: Если и B 4 больше или равно 4 и C 4 больше или равно 4 и D 4 больше или равно 4.

Щелкните

 

 

7. В поле Значение_если_истина введите – Стипендию получает.

8. В поле Значение_если_ложь введите – Стипендию не получает.

9. Скопируйте формулу вниз.

Задание №38. Самостоятельно выведите фамилии студентов в столбце G, которые будут получать стипендию, при условии, что средний балл больше или равен 4, иначе выведите *****.

Вложенные функции ЕСЛИ

 

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

 

=ЕСЛИ(А1=100; " Всегда"; ЕСЛИ(И(А1> =80; А1< 100); " Обычно"; ЕСЛИ(И(А1> =60; А1< 80); " Иногда"; " Никогда" )))

Задание №39. Выведите в столбце H сообщение «Обычная стипендия» для студентов, которые будут получать стипендию, при условии, что средний балл > = 4 и < 5 и «Повышенная стипендия», если средний балл =5, иначе выведите сообщение «Не получает стипендию».

1. Активизируйте ячейку H4.

2. Вызовите логическую функцию Если.

3. С помощью списка функций в строке формул вызовите логическую функцию И.

4. Введите логические значения

5. Щелкните мышью в строке формул по названию функции Если

Щелкните

 

 

6. Появится окно с аргументами функции Если.

7. Введите в поле Значение_если_истина значение Обычная стипендия.

8. Щелкните обязательно в поле Значение, если_ложь.

9. Из списка функций в строке формул выберите новую функцию Если.

10. В окне с аргументами вложенной функции Если в поле Логическое_выражение введите E4=5.

11. Введите в поле Значение_если_истина значение Повышенная стипендия.

12. В поле Значение, если_ложь введите значение Не получает стипендию.

13. Нажмите ОК и скопируйте формулу вниз.

Задание №40. Самостоятельно составьте таблицу расчета оплаты за аренду помещений в зависимости от площади: если арендуемая площадь меньше 100 м², то арендная плата составляет 500 руб. за 1 м², если арендуемая площадь больше, чем 100 м², но не превышает 200 м², то арендная плата составляет 700 руб. за 1 м². За площадь более 200 м² арендная плата – 800 руб. за 1 м².

Кафе	Площадь (кв.м)	Сумма арендной платы
«Эдельвейс»	167
«Нарцисс»	95
«Камелот»	234
«Рандеву»	128
«Экспромт»	173

 

⇐ Предыдущая12345678Следующая ⇒

Поделиться: