Практикум по vba для Microsoft Excel Учебное пособие
 Скачать 2.32 Mb.
|
|
Задание 1 Написать программу, позволяющую пользователю угадать число из интервала от 1 до 100 сгенерированное компьютером. Сосчитать, сколько потребовалось попыток, чтобы пользователь угадал число Обратите внимание на команду Option Explicit в первой строчке. Для чего она нужна? Вы, наверное, встречались с ситуацией, когда ваша программа считала не правильно. Это часто происходит, если в программе имена одной и тоже переменной записываются по-разному, например, rez и res. Наличие команды Option Explicit позволяет использовать в программе только имена переменных описанных в команде Dim. Задание 2 Дано целое число. Определить сколько цифр в этом числе. Чтобы определить количество цифр надо делить нацело данное число на 10 до тех пор, пока результат деления не станет равным 1. Циклы широко используются при работе с ячейками в Excel. Задание 3 Получить в первом столбце ряд чисел начиная с 2. Следующее число получается умножением предыдущего на 2. Ряд заканчивается, когда число становится больше 256. 61 Цикл с выходом по условию используется для того, чтобы найти конец таблицы. В этом случае используется условие пока не достигнута пустая ячейка: Cells(i, 2).Text <> "". Задание 4 Во втором столбце дан ряд целых чисел. Найти количество четных чисел в этом ряду и вывести в конце соответствующее сообщение. Задание 5 В седьмом столбце находятся числа и текст. Удалить из этого столбца все строки, содержащие текст. Просмотр столбца будем продолжать пока не встретиться пустая ячейка. Для того, чтобы определить, является ли содержимое ячейки числом, можно использовать функцию Val, преобразующую текст в число. Если результат этой функции больше 0, значит это число. Также надо исключить и удаления ячейки содержащей 0. Для удаления ряда надо создать автомакрос, в котором удалить строку. 62 Этот макрос будет иметь вид: Rows("15:15").Select Selection.Delete Shift:=xlUp Его надо преобразовать для ряда в котором будет находится текст. Обратите внимание, что переход к следующему ряду выполняется только, когда в ячейке находится число. Задания для самостоятельной работы Вариант 1 1. После каждого движения поршня разряжающего насоса их сосуда удаляется 20% находящегося газа. Сколько движений поршня нужно сделать, чтобы достичь давления P при начальном давлении P n 2. Дан столбец целых чисел. Закрасить желтым цветом все отрицательные числа. Вариант 2 1. Спортсмен в первый день пробежал 10 км. Каждый следующий день он увеличивал дневную норму на 10% от результата предыдущего дня. Найти через сколько дней спортсмен будет пробегать более 20 км в день. 2. Генерировать числа в столбец в интервале от 1 до 100 пока не встретиться число 50. Найти сумму сгенерированных чисел. Вариант 3 1. Спортсмен в первый день пробежал 10 км. Каждый следующий день он увеличивал дневную норму на 10% от результата предыдущего дня. Найти через сколько дней спортсмен пробежит суммарный путь более 100 км. 2. Дан столбец целых чисел. Просуммировать все числа меньше 100 и вывести в конце столбца эту сумму. Вариант 4 1. Сотрудник взял беспроцентный кредит в размере S рублей. Условия погашения кредита следующие: каждый месяц выплачивается сумма вдвое превышающая сумму, выплаченную в предыдущий месяц. Через сколько месяцев кредит будет погашен, если в первый месяц сотрудник выплатил N рублей. 63 2. Дан столбец целых чисел. Найти количество чисел, равных первому числу и вывести это количество в конце столбца. Вариант 5 1. Шары расположены в форме треугольника так, что в первом ряду находится один шар, во втором – два, в третьем – три и так далее. Сколько рядов удастся построить, если имеется N шаров? 2. Дан столбец чисел. Просуммировать все числа в этом столбце попадающие в интервал от -20 до 20 и вывести результат в конце. Вариант 6 1. Собран урожай из N кг яблок. Определить, через сколько дней будет вынесен весь урожай, если каждый день количество вынесенных корзин удваивается. В одной корзине помещается 5 кг. 2. Дан столбец целых чисел. Удалить стоки, содержащие нули. Вариант 7 1. Задать целое число и подсчитать сумму цифр в этом числе используя операции целочисленного деления - \ и mod – остатка от деления. 2. Дана строка, содержащая ячейки с текстом. Напечатать в конце этой строки «Итого». Вариант 8 1. Дан текст. Определить, сколько слов в этом тексте. Слова отделяются пробелами. 2. Дана строка, содержащая ячейки с текстом. Напечатать под этой строкой целые числа, начиная с 1. Вариант 9 1. На складе можно разместить N ящиков помидор. В первый день привезли K ящиков в последующие дни количество привезенных ящиков увеличивалось на 2. Через сколько дней заполнится склад. 2. Дан столбец, содержащий ячейки с текстом. Удалить в этом столбце тексты из одного символа. Вариант 10 1. Сколько чисел надо взять в последовательности 1 + 2 + 3 + 4 + …, чтобы получить число, больше чем N? 2. Дан столбец, содержащий ячейки с текстом. Добавить в начале каждого текста номер (1, 2, …). Вариант 11 1. Дано натуральное n. Выяснить, входит ли цифра 3 в запись числа n 2 Операции со строками не использовать. 2. Первый столбец содержит текст. Вставит перед ним новый столбец, и вывести в нем числа натурального ряда. 64 Вариант 12 1. Задать целое число и получить новое число из первой и последней цифры этого числа. Строки не использовать. 2. Дано целое 4-значное число. Вывести столбец целых чисел полученных делением на 2, начиная с заданного числа до 0. Контрольные вопросы 1. Чем отличаются циклы с «пост»-условием от циклов с «пред»- условием. 2. Что будет, если после Do и Loop не поставить условие. 3. Какое условие записывается после слова Until/ 4. Как изменится макрос Угадайка если в операторе цикла слово Until заменить на слово While/ 5. Что надо изменить в макросе количество, чтобы вместо количества считать сумму. 65 Лабораторная работа № 7 Цикл со счетчиком Цикл со счетчиком имеет следующий синтаксис: For счетчик=начальноеЗначение То конечноеЗначение [Step приращение] блокОператоров> Next [счетчик] Несколько пояснений к приведенному описанию: Необязательные конструкции, как обычно, заключены в квадратные скобки приращение — может быть как положительным, так и отрицательным числом. Если использовать отрицательное приращение, то конечное значение должно быть меньше либо равно начальному значению для того, чтобы тело цикла выполнилось хотя бы один раз После завершения работы цикла For...Next переменная, которая использовалась в качестве счетчика, получает значение, обязательно превосходящее конечное значение в том случае, если приращение положительно, и строго меньшее конечного значения, если приращение отрицательно Если начальное и конечное значения совпадают, тело цикла выполняется лишь один раз Выход из циклов и процедур Обычно выполнение процедуры заканчивается после выполнения ее последнего оператора, а выполнение цикла — после выполнения тела цикла несколько раз, по достижении условия завершения его работы. Однако в некоторых случаях бывает нужно прекратить выполнение процедуры или цикла досрочно, избежав выполнения лишних операторов процедуры или лишних повторений цикла. Например, если при выполнении процедуры произошла ошибка, которая делает продолжение ее работы бессмысленным, можно выполнить команду немедленного выхода из процедуры. Другой пример: если цикл For...Next используется для поиска нужного значения в столбце, то когда нужное значение найдено, нет смысла продолжать дальнейший перебор ячеек. Досрочный выход из управляющей конструкции можно осуществить с помощью одного из операторов Exit. Для досрочного выхода из циклов Do …Loop используется оператор Exit Do, а для выхода из циклов For — оператор Exit For. Для досрочного выхода из процедур и функций используются операторы Exit Sub и Exit Function, соответственно. Следует, однако, отметить, что хотя использование оператора Exit может быть вполне оправданным, следует избегать излишнего употребления этого оператора, прибегая к нему только в крайних случаях. Излишне частое его применение затрудняет понимание написанного текста программы и его отладку. 66 Задание 1 За 5 попыток угадать число, задуманное компьютером. Задуманное число лежит в интервале от 1 до 100. Задание 2 Записать функцию, которая в выделенном диапазоне-столбце находит номер ряда, с заданным значением. Обратите внимание, что для аргумента значение не указан тип. Это означает, что данная функция может находить и числа и текст. Задание 3 В выделенном диапазоне-ряде получить ряд чисел кратный 3. 67 Задание 4 В заданном диапазоне-столбце выделить зеленым все числа, равные минимальному элементу. Задание 5 Найти максимальный элемент в столбце и поставить в него курсор. 68 Ввести и отладить (найти ошибку) данный макрос. Таблица стандартных алгоритмов. этап Сумма S Произведение P Количество k Минимум Min До цикла S=0 P=1 k=0 Min=первый элемент Начало цикла В ци кле 1 Проверка, надо ли данный элемент суммировать (необязательно) Проверка, надо ли данный элемент умножать (необязательно) Проверка, удовлетворяет ли элемент условию Проверка, элемент меньше Min? 2 S=S+элемен т P=P*элемен т k=k+1 Min:=элем ент Переход к следующему элементу. Конец цикла Задания для самостоятельной работы Вариант № 1 1. Ввести дневную и ночную температуры за неделю и определить день с самым небольшим перепадом температур. 1. Сгенерировать в выделенном столбце диапазоне числа от -100 до 100. Закрасить красным цветом значение, которое максимально отличается от среднего арифметического значения в этом диапазоне. Вариант № 2 1. Спортсмен в первый день пробежал 10 км. Каждый следующий день он увеличивал дневную норму на 10% от результата предыдущего дня. Найти какой путь пробежит спортсмен на 7 день; 2. Дан ряд-диапазон чисел. Найти все минимальные значения в этом диапазоне. Удалить эти числа и найти новое минимальное значение. Вариант № 3 1. Ежемесячная стипендия студента составляет А руб., а расходы на проживание превышают стипендию и составляют В руб. в месяц. Рост цен ежемесячно увеличивает расходы на 3%. Составьте программу расчета суммы денег, которую необходимо попросить у родителей, чтобы можно было прожить учебный год (10 месяцев) используя только эти деньги и стипендию. 69 2. Дан ряд диапазон чисел. Найти максимальное значение в этом диапазоне, обнулить все максимальные значения и найти среднеарифметическое значение в этом диапазоне после обнуления. Вариант № 4 1. Одноклеточная амеба каждые 3 часа делится на 2 клетки. Определить, сколько амеб будет через 3, 6, 9, 12, …, 24 часа. 2. Дан ряд диапазон чисел. Удалить в диапазоне все неотрицательные числа и найти максимальный элемент, среди оставшихся чисел. Вариант № 5 1. Ввести средние месячные температуры за год и определить, сколько в году месяцев с отрицательной средней температурой. 2. Дан ряд диапазон чисел. Удалить все числа меньшие среднеарифметического и найти среди оставшихся минимальный элемент. Вариант № 6 1. Задать закупочную цену 10 товаров и продажную цену. Найти товар, который принес самую большую прибыль. 2. Дан ряд диапазон чисел. Найти среднее арифметическое среди отрицательных элементов и умножить на это среднее арифметическое минимальный элемента в этом ряду Вариант № 7 1. Клиент банка взял кредит в размере 500000 на год с условием, что ежемесячно, кроме долга он дополнительно выплачивает проценты в размере 10% от оставшейся суммы. Найти сумму, которую клиент выплатит банку за год. 2. Дан столбец диапазон чисел. Найти минимальное значение среди элементов, стоящих в нечетных столбцах. Найти количество чисел, равных этому минимальному значению. Вариант № 8 1. Даны дневные и ночные температуры за неделю. Найти среднюю дневную температуру и среднюю ночную температуру за неделю, а так же разницу между этими средними температурами. 2. Дан ряд диапазон целых чисел. Удалить все нечетные числа и найти среди оставшихся чисел максимальный элемент. Просуммировать числа, стоящие после этого значения. Вариант № 9 1. В группе 12 человек .Ввести отметки за экзамен и найти: количество двоек, количество пятерок и среднюю отметку за экзамен. 2. Дан столбец диапазон чисел. Переписать жирным цветом число максимально близкое к среднему арифметическому числу среди этих чисел. 70 Вариант № 10 1. Если вес пушного зверька в возрасте от 6 до 8 месяцев превышает 7 кг. То необходимо снизить дневное потребление витаминного концентрата на 125 г. Ввести возраст и вес для 10 зверьков и выяснить, на сколько килограммов в месяц снизится потребление витаминного концентрата. 2. Дан столбец диапазон чисел. Закрасить синим и просуммировать все числа стоящие перед последним минимальным значением (если несколько ячеек имеют это значение). Вариант № 11 1. Ввести возраст и рост для 10 учеников. Сколько учеников могут заниматься в баскетбольной секции, если туда принимают детей ростом не менее 160 см., а возраст не должен превышать 13 лет. 2. Сгенерировать в выделенном столбце диапазоне числа от -100 до 100. Найти минимальное значение и количество отрицательных чисел. Вариант № 12 1. Дано 10 блюд с указанием количества калорий в 100 г. И вес каждого блюда в граммах. Сколько блюд имеют общую калорийность менее 100 калорий. 2. Дан столбец диапазон чисел. Закрасить зеленым цветом все числа, стоящие между минимальным и максимальным значением. 71 Лабораторная работа № 8 Вложенные циклы В ранее рассмотренных примерах рассматривались диапазоны, являющееся частью строки и столбца. Для работы с этими диапазонами достаточно было одного цикла, в котором перебирались номера строк или номера столбцов. В общем случае диапазон состоит из нескольких строк и столбцов. Задание 1 В заданном диапазоне получить целые числа из интервала от -100 до 100. Задание 2 Выделить красным цветом минимальный элемент в заданном диапазоне. 72 В рассмотренных выше заданиях перебор ячеек выполнялся построчно. Иногда требуется изменить порядок просмотра. Задание 3 В заданном диапазоне подсчитать количество отрицательных значений в каждом столбце, и записать это количество в конце каждого столбца. Для этого примера надо изменить порядок просмотра: при фиксированном столбце перебирать ряды. Цикл For Each … Next Цикл For Each..Next не использует счетчик цикла. Циклы For Each..Next выполняются столько раз, сколько имеется элементов в определенной группе. Цикл For Each..Next выполняется один раз для каждого элемента в группе. For Each <элемент> In <группа> <операторы> Next В нашем случае элемент – это ячейка, а группа – это выделенный диапазон. Задание 4 В заданном диапазоне чисел закрасить ячейки с нулями желтым цветом. Использование цикла For Each..Next зачастую сокращает код программы по сравнению с вложенными циклами. 73 Задание 5 Приведем пример программы нахождения минимума с использованием цикла For Each..Next. Задания для самостоятельной работы Вариант 1 1. Дан диапазон чисел. Каждую строку переписать в обратном порядке. 2. В заданном диапазоне у всех отрицательных элементов изменить знак на противоположный. Вариант 2 1. Дан диапазон чисел. Заменить минимальное значение в каждом столбце на максимальное значение в этом же столбце. 2. В заданном диапазоне все числа меньшие среднего арифметического закрасить красным цветом. Вариант 3 1. Дан диапазон чисел. Найти в каждой строке сумму положительных чисел и записать в конце каждой строки это количество. 2. В заданном диапазоне все числа из интервала от 0 до 10 заменить на 0. Вариант 4 1. Дан диапазон чисел. Найти в каждом ряду максимальное значение и записать в конце каждого ряда это значение. 2. В заданном диапазоне все числа нечетные отрицательные числа закрасить зеленым цветом. 74 Вариант 5 1. Дан диапазон чисел. Найти в каждой строке среднее арифметическое значение и записать его в конце каждой строки. 2. В заданном диапазоне найти количество нулевых элементов, и заменить на это количество первый элемент в диапазоне. Вариант 6 1. Дан диапазон чисел. Заменить первое значение в каждом столбце на сумму ячеек в этом столбце 2. В заданном диапазоне все четные числа закрасить желтым цветом. Вариант 7 1. Дан диапазон чисел. Найти в каждой строке количество чисел равных первому числу в диапазоне и записать в конце каждой строки это количество. 2. В заданном диапазоне все числа равные заданному числу выделить жирным шрифтом. Вариант 8 1. Дан диапазон чисел. Найти в каждом ряду минимальное значение и записать в конце каждого ряда это значение. 2. В заданном диапазоне найти среднее арифметическое среди отрицательных чисел и заменить нулевые значения на это число. Вариант 9 1. Дан диапазон чисел. Найти в каждом ряду количество четных чисел и записать в конце каждого ряда это количество.. 2. В заданном диапазоне найти сумму чисел, не попадающих в интервал от 0 до 30. Вариант 10 1. Дан диапазон чисел. Заменить минимальное значение в каждом столбце на максимальное значение в этом же столбце. 2. В заданном диапазоне все нечетные числа увеличить на 1. |