4. Использование visual basic в excel

4.1. Работа с макросами

Познакомимся с таким средством работы в Excel, как "макросы".

Выберем раздел меню СервисМакросНачало записи... Появится окно Запись макроса. Вы можете ввести или изменить информацию в полях этого окна, в частности, назначить свое имя или "горячую клавишу" для запуска макроса. Оставьте содержание полей неизменным или, если хотите, измените их и нажмите кнопку <ОК>. Обратите внимание на появление небольшой панели с кнопкой <Остановить запись> на листе книги Excel. Выполните любую последовательность действий на листе, например, закажите для выделенных ячеек более крупный и цветной шрифт и введите туда формулу. После этого нажмите кнопку <Остановить запись> (надо именно нажимать кнопку, а не закрывать панель).

Сотрите все результаты выполненных вами действий при записи макроса. Далее выберем раздел меню СервисМакросМакросы…. В появившемся окне выделим имя того макроса, который Вы только что записали, и нажмем кнопку <Выполнить>. Убедитесь что последовательность действий, проделанных Вами при записи макроса, оказалась выполненной заново. Вы можете опять стереть результаты этих действий или выделить другие ячейки и опять заказать выполнение макроса. Все действия, сделанные при записи макроса, повторятся автоматически. При этом, если в макрос входят формулы, которые содержат ссылки на другие ячейки , и он выполняется не на прежнем месте, то ссылки автоматически будут преобразованы в соответствии с правилами копирования.

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

Примечания.

• Не забывайте останавливать запись макроса сразу после завершения нужной Вам последовательности действий.

• Ввод формулы следует заканчивать нажатием кнопки <Enter> в Строке формул, а не клавиши <Enter>.

• Если Вы при записи макроса назначили ему «горячее» сочетание клавиш, например <Ctrl+m>, или другое, выполнить соответствующий макрос можно без выхода в меню, а просто нажав это сочетание клавиш. Проверьте это на практике.

Задание.

Запишите макрос, выполняющий следующую последовательность действий:

• поставьте курсор в ячейку А2 и введите число "1";

• в ячейку А3 введите число "1,5";

• протяните этот ряд до значения "6";

• в ячейку В2 введите формулу "=SIN(А2)+А2:1,5" ( не забудьте, что заканчивать формулу надо кнопкой < Enter > в Строке формул) ;

• протяните формулу из В2 до В12;

• округлите значения в В2:В12 до двух цифр после запятой;

• обведите рамками ячейки А1:В12 и А1:В1 и проведите линию между А1:А12 и В1:В12:

• поставьте курсор в А1.

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

5. Задания для самостоятельной работы

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

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

3. Известны месячные коэффициенты инфляции в течение года. Представить эти данные в виде таблицы и графика. Найти общую и среднюю инфляцию за год.

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

5. Имеются сведения об успеваемости студентов по разным дисциплинам. Представить эти данные в виде таблицы и накопленной гистограммы (по оси Х – дисциплина, по оси У – средний балл по ней). Определить средний балл каждого студента и группы в целом. Выделить студентов, имеющих "2" по какой-нибудь дисциплине.

6. Для аргументов, изменяющихся от а-3 до а+3 с шагом , составить таблицу значений плотности распределения нормального закона, которая задаётся функцией

.

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

7. Для аргументов x от до с шагом и y от до с шагом составить таблицу значений плотности распределения двумерного нормального закона, которая задаётся функцией

.

Предусмотреть возможность изменения параметров Представить эти данные в виде серии кривых для разных y.

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

Значения ввести как параметры функции.

9. Для аргументов, изменяющихся от до с шагом , составить таблицу и график значений плотности распределения закона Стьюдента, которая задаётся функцией

Значения ввести как параметры функции.

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

Значение ввести как параметр функции.

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

12. Имеются сведения о спросе на определённый товар в течение ряда лет. Представить эти данные в виде таблицы и графика. Выделить годы с минимальным и максимальным спросом. Подобрать функцию тренда.

13. Составить таблицу, отражающую цену, объём продаж и выручку по ряду продуктов. Выделить продукты, дающие максимальную выручку, максимальный объём продаж. Представить объём продаж и выручку в виде круговых диаграмм.

14. Имеются сведения об ежедневном времени простоя оборудования из-за нехватки сырья. Оформить эти данные в виде таблицы и точечного графика. Отметить в таблице дни, когда простои превысили “а” часов. Подобрать на графике функцию тренда.

15. Имеются данные о среднем количестве покупателей в магазине в разное время дня. Представить эти данные в виде таблицы и графика. Отметить в таблице время, когда количество посетителей превышает “а” человек. Найти среднее количество посетителей за день.

16. Предприятие реализует свой товар в нескольких районах области. Представить данные о реализации по районам в виде таблицы и круговой диаграммы. Отметить районы, в которых реализация превысила “а” рублей. Найти общий объём реализации.

17. Имеются данные об объёме затрат каждого кандидата на рекламную кампанию и его популярности. Представить их в виде таблицы и пузырьковой диаграммы. Выделить в таблице кандидатов, которые затратили на рекламную кампанию больше "a" рублей, подсчитать их количество. Подсчитать общую сумму затрат на рекламу кандидатов.

18. Предприятие проводит рекламные мероприятия. Отразить сведения о сбыте товаров в каждый день рекламной компании в виде таблицы и ломаной линии. Отметить день с максимальным сбытом. Определить общий объём сбыта за время компании. Подобрать на графике функцию тренда.

19. Стоимость партии товара зависит от её объёма и вычисляется по формуле

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

11. Составить таблицу значений интеграла как функции верхнего предела. Отразить эти данные на графике. Подобрать функцию тренда.

12. Составить таблицу значений интеграла как функции верхнего предела. Отразить эти данные на графике. Подобрать функцию тренда.

13. Составить таблицу значений интеграла как функции верхнего предела. Отразить эти данные на графике. Подобрать функцию тренда.

14. Составить таблицу значений интеграла как функции верхнего предела. Отразить эти данные на графике. Подобрать функцию тренда.

15. Составить таблицу значений интеграла как функции верхнего предела. Отразить эти данные на графике. Подобрать функцию тренда.

16. Составить таблицу значений интеграла как функции верхнего предела. Отразить эти данные на графике. Подобрать функцию тренда.

17. Решить систему нелинейных уравнений х²+у²=1; у=-х². Начальное приближение выбрать по таблице вспомогательной целевой функции.

18. Решить систему нелинейных уравнений 3х²+5у²=24; ху=-0,4. Начальное приближение выбрать по таблице вспомогательной целевой функции.

19. Решить систему нелинейных уравнений ху=-32; х=-6у². Начальное приближение выбрать по таблице вспомогательной целевой функции.

20. Решить систему нелинейных уравнений ху=31; у=-6х². Начальное приближение выбрать по таблице вспомогательной целевой функции.

21. Решить систему нелинейных уравнений 2х²+5у²=17; ху=0,3. Начальное приближение выбрать по таблице вспомогательной целевой функции.

22. Найти графически и по командам СервисПодбор параметра…, СервисПоиск решения… минимумы и корни функции х⁴-х³-х²-2х+1.

23. Найти графически и по командам СервисПодбор параметра…, СервисПоиск решения… минимумы и корни функции х⁴-2х³-3х²-3х-32.

24. Найти графически и по командам СервисПодбор параметра…, СервисПоиск решения… минимумы и корни функции х⁴-3х³+2х²-20.

25. Найти графически и по командам СервисПодбор параметра…, СервисПоиск решения… минимумы и корни функции х⁴-х³-3х²-5х-16.

26. Найти графически и по командам СервисПодбор параметра…, СервисПоиск решения… минимумы и корни функции х⁴-2х³+х²-3х-22.