2017-12-14
468
Вычислить объѐм по формуле
V = H × 2 p R 2
Отобразить результаты (значение V) Конец алгоритма
В этом алгоритме действия выполняются последовательно друг за другом, такие алгоритмы называются линейными.
Этот алгоритм можно записать в виде макроса Excel на VBA, а зна- чения H и R поместить, например, в ячейки A1 и B1 или просто за- дать в теле макроса.
Для создания макроса выбирают на вкладке Вид группу Макросы, за- дают имя, например Vk, и выбирают Создать. Появляется окно ре- дактора VBA (V isual B asic for A pplication – визуального Бейсика для приложений), показанное на рис. 6.1. В окне обозревателя проектов виден включенный в проект модуль Module1 – в нѐм создаѐтся мак- рос. В окне кода уже записаны строки начала Sub Vk() и конца End Sub процедуры макроса (алгоритма), которые надо дополнить ос- тальными строками алгоритма так, чтобы получился текст макроса:
Sub Vk()
H = 4.5: R = 3.0 ¢ Назначить конкретные значения H и R. Строка 2. V = H/3*3.14 * R^2 ¢ Вычислить объѐм конуса. Строка 3. Debug.Print V ¢ Отобразить результаты (значение V ). Строка 4.
|
|
|
End Sub
В строке 2 текста макроса переменным H и R н а з н а ч а ю т с я (присваи- ваются) в двух о п е р а т о ра х конкретные числовые значения. Еѐ можно записать, указав явно имена операторов присваивания Let (пусть):
Let H = 4.5: Let R = 3.0 ¢ Назначить конкретные значения H и R. Строка 2,
которые обычно в Бейсике по аналогии с другими языками програм- мирования не указываются. По правилам Бейсика операторы, запи- санные в одной строке (как здесь во 2-ой строке), отделяются друг от друга двоеточием. Справа после апострофа «ʹ» можно дать примеча- ния, которые выводятся на экран зелѐным цветом, а в нашем тексте они будут выделяться курсивом. Ключевые слова (названия операто- ров) изображаются на экране синим цветом, а в наших текстах они выделяются полужирным шрифтом.
 |
чего в этом окне следует набить:? (4.5/3)*3.14 *(3.0^2) и дать «Enter». Знак «?» вопроса перед выражением означает печать (Print), и по это- му указанию на следующей строке будет выведен результат 84,78. Такой же результат можно получить, набив построчно операторы 2- ой и 3-ей строк макроса и выполнив затем печать вычисленного объ- ѐма по? V «Enter». Полезно отметить, что тот же результат получа- ется и по записи? 4.5/3*3.14 *3.0^2 без простановки скобок, т. е. в Бейсике возведение в степень «^» имеет приоритет. После возведения в степень выполняются слева направо деление и умножения как опе- рации одного ранга при отсутствии скобок.
|
|
|
Выполнение макроса в автоматическом режиме можно иницииро- вать через пункт меню Run, нажатием кнопки запуска 
или клави- ши F5. При первом знакомстве больше подходит выполнение в режиме отладки операторов макроса: через пункт ме- ню Debug и/или нажатием клавиши F8. Причѐм курсор должен нахо- диться внутри макроса. Тогда подсвечивается (обычно жѐлтым цве- том) оператор, который предстоит выполнить. Наведя указатель мышки на переменную H в первом операторе присваивания, можно увидеть еѐ значение и отметить, что до выполнения оператора пере- менная имела значение «Empty» (пусто), а после выполнения (по оче- редному нажатию F8) значение изменилось на 4,5. После выполнения оператора 4-ой строки в окно отладки выводится результат 84,78.
И с х о д н ы е з н а ч е н и я H и R удобно задать в я ч е й к а х л и с т а Excel, на- пример, соответственно в ячейках A1 и B1. Для ссылки в тексте мак- роса на ячейки используется нотация (r, c) – строка (row), столбец (column). Например, ячейку B1 можно в такой нотации задать как (1, 2). В макросе на эту ячейку рабочего листа ссылаются, записывая Cells(1, 2). В таком варианте текст макроса получает следующий вид:
Sub Vk()
H = Cells(1,1): R = Cells(1,2) ¢ Выбрать значения H и R из ячеек A1, B1 V = H/3*3.14 * R^2 ¢ Вычислить объѐм конуса
Debug.Print V: Cells(1,3) = V ¢ Отобразить результаты (значения V )
End Sub
Результат выводится и в окно отладки и в ячейку C1 рабочего листа.
С именем макроса можно связать сочетание клавиш или световую кнопку, по которым он будет запускаться. Выбрав при входе в макрос Параметры и задав, например, сочетание клавиш Ctrl+Shift+V, полу- чаем возможность удобно запускать макрос по этому сочетанию.
Задание для самоконтроля. Поместить в ячейку B1 значение R, рав- ное 3,0. Задавая в ячейке A1 значения H и вызывая по сочетанию кла- виш выполнение макроса, получить в ячейке C1 объѐм 100,0.
Ветвящиеся вычислительные процессы
Пример 2. По заданным значениям коэффициентов p, q квадратного
уравнения
x 2 +
px + q = 0
вычислить значения вещественных корней
или вывести сообщение «нет корней» об их отсутствии.
Алгоритм решения задачи можно представить наглядно, в виде блок-схемы примера 2 с записью этапов алгоритма в геометрических фигурах, соответствующих типу этапа.
После ввода коэффициентов и вычисления дискриминанта D следует проверка условия (заключено в ромб), в зависимости от выполнения или не выполнения которого выбирается направление дальнейших действий. Блок-схема примера 2 в этом месте разветвляется – отсюда и название «ветвящиеся вычислительные процессы». При не отрица- тельном D вычисляются и выводятся значения корней, а при отрица- тельном выводится сообщение «нет корней».
Алгоритм реализован в виде макроса Korni на VBA. Значения коэффициентов p, q берутся со- ответственно из ячеек A1, B1, значения корней - выводятся в ячейки C1, D1, а символьная константа "Нет корней" помеща- ется в ячейку C1.
Sub Korni() ¢ Текст макроса
p = Cells(1, 1): q = Cells(1, 2) D = (p / 2) ^ 2 – q
If D < 0 Then
Cells(1, 3) = "Нет корней" ¢ Ветвь «да»
Else
x1 = –p / 2 –Sqr(D) ¢ Ветвь «нет»
x2 = –p / 2 + Sqr(D)
Cells(1, 3) = x1: Cells(1, 4) = x2
End If End Sub
В приведѐнном макросе условие
D< 0
записано в блочном условном
операторе If (если). Ветвь «да» блок-схемы размещается между ключевыми словами Then (тогда) и Else (иначе) этого оператора, а ветвь «нет» - между Else и завершающим блок End If (конец если). После выполнения каждого из блоков «да» и «нет» происходит выход за End If, здесь на завершающий макрос оператор End Sub.
|
|
|
Для вычисления арифметического корня из D использована встроен- ная функция с именем Sqr. После имени функции всегда открывается скобка, затем записывается аргумент и скобка закрывается.
Первое выполнение макроса полезно выполнить в режиме отладки. Конкретные коэффициенты p, q при этом лучше назначить непосред- ственно в тексте макроса, записав, например, во второй строке:
p = –5: q = 6 ¢ p = Cells(1, 1): q = Cells(1, 2)
Выполняя макрос по шагам (по F8), полезно проследить за изменени- ем значений переменных и последовательностью выполнения опера- торов при разных исходных данных p, q. Уместно также дополнить ветви «да» и «нет» соответственно операторами
Debug.Print p, q, "Нет корней" и Debug.Print p, q, x1, x2
вывода в окно отладки.
При выполнении отлаженного макроса в ячейки A1, B1 помещаются значения коэффициентов, запускается макрос, и результаты просмат- риваются в ячейках C1, D1.
Замечания. 1. В блочном условном операторе блок ветви «нет» вме- сте с ключевым словом Else может быть опущен. Например, для оп-
ределения количества Kol вычисляемых вещественных корней урав- нения из примера 2 подойдѐт следующий фрагмент программы
Kol = 0
If D>= 0 Then
Kol = 2
End If
2. Когда блок состоит как здесь из одного оператора, уместно исполь- зовать не блочный условный оператор, а «строчный» If:
Kol = 0: If D>= 0 Then Kol = 2
В случае справедливости условия выполнится присваивание Kol = 2, при невыполнении условия - следующий по порядку оператор.
Циклические вычислительные процессы
Пример 3. Вывести таблицу квадратов и кубов натуральных чисел от 2 до 20. Как результат в ячейках листа (и в окне отладки) должны появиться строки с числами:
|
Алгоритм решения примера 3 представлен в виде блок-схемы примера 3 и макроса:
Sub NKvKub () K = 2: N = 20
Start: L = K ^ 2
M = K ^ 3
Cells(K,1) = K: Cells(K,2) = L: Cells(K,3) = M Debug.Print K, L, M ¢ вывод K, L, M
K = K + 1
If K <= N Then GoTo Start
End Sub
В данном алгоритме переменной K, предна- значенной для хранения натуральных чисел от 2 до 20, сначала при- сваивается начальное значение 2. После вычисления и вывода квад-
|
|
|
рата и куба числа 2 в операторе присваивания K=K+1 к исходному значению K прибавляется 1 и сумма (3) присваивается этой же пере- менной, то есть записывается в ту же ячейку памяти, затирая преды- дущее значение (2). После этого можно снова выполнять операторы вычисления и вывода - ведь они оперируют с содержимым всѐ той же ячейки, которое теперь уже увеличилось на единицу. Поэтому в
«строчном» операторе If после Then записан оператор безусловного перехода GoTo « метка », передающий управление на оператор L=K^2, отмеченный меткой Start. Но повторять вычисления и вывод нужно пока значение K не превысит предельной величины N=20, поэтому и использован условный переход по ветви «да».
Приведѐнный выше алгоритм реализует циклический вычислитель- ный процесс (цикл). Для цикла характерны повторения обведѐнных на блок-схеме пунктиром операторов, составляющих тело цикла. Для этого цикла характерно изменение как в арифметической про- грессии значения переменной K - счетчика цикла, определяющей число выполненных повторений, от начального значения (здесь от 2) до своего конечного значения (здесь до 20) с шагом (здесь 1).
Поскольку циклы широко используются при программировании, в Бейсике есть специальные операторы для организации циклов.
Для организации цикла примера 3 подойдут операторы For и Next, и с ними макрос NKvKub примет следующий вид:
Sub NKvKub () N=20
For K = 2 To N Step 1 ¢ Для K от 2 до N с шагом 1: L = K ^ 2 ¢ Тело цикла (начало)
M = K ^ 3
Cells(K,1) = K: Cells(K,2) = L: Cells(K,3) = M
Debug.Print K, L, M ¢ Тело цикла (конец)
Next K ¢ Следующее K
End Sub
В приведѐнном макросе оператор For начала цикла организует по- вторное выполнение тела цикла при значениях счѐтчика цикла K рав-
ного 2, 3, …, N, т, е. изменяющегося от 2 до N с шагом 1. Оператор Next отмечает конец тела цикла. В этом операторе не обязательно ука- зывать имя переменной-счѐтчика цикла. Если шаг равен (как здесь) единице, то в операторе For его вместе со словом Step можно опус- тить. После выхода из цикла счѐтчик цикла равен очередному значе- нию, при котором тело перестанет выполняться. При шаге, не позво- ляющем счѐтчику цикла достичь предельного значения, тело цикла обходится – не выполняется ни разу.
Использование циклов при накапливании сумм
Пример 4. Для заданного значения N вычислить сумму квадратов натуральных чисел от 1 до N и проверить справедливость формулы: 12 +22 +32 +... + N 2 = N ×(N +1) ×(2 N +1) / 6.
|
|
равенства слагаемых, то есть
S =12
+ 22
+ 32
+... + N 2
k =1, а
также значение (обозначенного как C) выражения, стоящего справа от знака равенства, и сравнить их.
Вычисление суммы чисел, указанных слева от знака равенства, можно организовать в цикле со счѐтчиком цикла K, принимающим значения от 1 до N с шагом 1. При каждом значении K можно вычис- лять одно слагаемое A= K2 и добавлять его к сумме S, которая снача-
ла должна равняться нулю. Алгоритм вычисления суммы S представ- лен в виде блок-схемы и текста макроса.
Sub Sum()
N = 3 ¢ Назначить какое-либо значение. S = 0 ¢ Подготовка к накапливанию суммы. For K = 1 To N Step 1
A = K ^ 2
S = S + A ¢ Пополнение суммы.
Next K
C = N * (N + 1) * (2 * N+ 1) / 6
Debug.Print N, S, C
End Sub
При выполнении макроса в режиме отладки полезно проследить (на- водя курсор на переменную S) то, как пополняется сумма при каждом выполнении оператора S = S + A. Для принятого выше значения N=3 верно вычисленная сумма должна составить 14. Такое же значение C даѐт вычисление по формуле. Совпадут ли результаты при иных зна- чениях N?
Пример 5. З н а ч е н и я в ы с о т ы H и р а д и у с а R из примера 1 заданы на- чиная с ячеек A2 и B2 в н е с к о л ь к и х п о с л е д о в а т е л ь н ы х с т р о к а х соот- ветственно столбцов A и B. Требуется: 1) заполнить столбец C, на- чиная с ячейки C2, значениями объѐмов конусов, 2) записать в ячейки D2, E2 и F2 соответственно общий объѐм всех конусов, количество конусов и средний объѐм конуса.
Для перебора строк (начиная со второй) и записи вычисляемых значений объѐмов уместно применить цикл, однако в общем случае не известно в какой строке оканчиваются данные. Поэтому обработка данных должна вестись до тех пор, пока есть данные в строках. Цик- лы, которые организуют повторное выполнение тела цикла пока справедливо (или наоборот не справедливо) некоторое условие, назы- вают итерационными циклами. В VBA такие циклы начинает опе- ратор Do (выполнять), за которым могут следовать опции: служебные слова While (пока справедливо) или Until (пока не справедливо) и ус- ловие. Конец тела цикла отмечает оператор Loop (петля, цикл), кото- рый также может иметь названные опции (только если они не указа- ны вместе с оператором Do).
Решение 1-ой части примера 5 даѐт следующий макрос.
Sub Primer5_1() i = 2
Do While Cells(i, 1) < > Empty ¢ Цикл «Выполнять пока справедливо»
H = Cells(i, 1): R = Cells(i, 2) ¢ Назначить конкретные значения H и R V= H/3*3.14 * R^2: Cells(i, 3)=V ¢ Вычислить объѐм конуса
i = i + 1
Loop End Sub
В условии оператора цикла Do While проверяется: не равно ли со- держимое очередной i-ой ячейки 1-го столбца (столбца A) значению Empty («пусто»)? И пока оно не «пусто» вычисляется объѐм и увели- чивается каждый раз на единицу номер i, указывающий на очередную строку. Цикл Do Until представлен во 2-ой части задачи.
Для решения 2-ой части задачи надо дополнить приведѐнный мак- рос накапливанием суммы S и количества k обработанных строк:
Sub Primer5_2() S=0: k = 0
Do Until Cells(k+2, 1) = Empty ¢ Цикл «Выполнять пока не справедливо»
H = Cells(k+2, 1): R = Cells(k+2, 2) ¢ Назначить конкретные значения H и R V= H/3*3.14 * R^2: Cells(k+2, 3)=V ¢ Вычислить объѐм конуса S = S +V: k = k + 1 ¢ Накапливание суммы и количества
Loop
Cells(2, 4) = S: Cells(2, 5) = k: Cells(2, 6) = S / k ¢ Результаты- в D2, E2, F2
End Sub
Можно отметить, что количество k накапливается как сумма S, но в качестве слагаемых к накапливаемому количеству добавляют едини- цы. В макросе номер обрабатываемой строки получается увеличени- ем количества k на 2.
Пример 6. По списку повторяющихся фамилий абонентов и сумм (счетов см. рис. 6.2) за каждый телефонный разговор абонента (запи- санных соответственно в столбцах B и C, начиная с ячеек B3 и C3) вычислить для указанной фамилии абонента: общую сумму к оплате, количество его звонков, среднюю стоимость его звонка.
' Макрос - Решение примера 6
Sub Primer6() ¢ == Образец задания 1 ==
Fam$ ="Котов": i = 3: S=0: Kol = 0:
Do While Cells(i, 2) < > Empty
If Cells(i, 2) = Fam$ Then ¢ Вычислния для Fam$ S=S + Cells(i, 3): Kol = Kol +1
End If
Рис. 6.2.
i = i + 1 ¢ i – номер обрабатываемой строки
Loop
¢ Запись результатов в B1, D1 и F1:
Cells(1, 2) = S: Cells(1, 4) = Kol: Cells(1, 6) = S / Kol
End Sub
Следует отметить, что в условном блочном операторе, изменение счѐтчика Kol и суммирование производится лишь тогда, когда содер- жимое ячейки совпадает с содержимым текстовой переменной Fam$. Здесь текстовая (или строковая – типа String) переменная Fam$ по старым правилам Бейсика отмечена знаком $ на конце. Перед циклом ей присваивается значение текстовой константы с фамилией (здесь "Котов"). Можно настроить макрос на считывание по оператору Fam$ = ActiveCell.Value фамилии, как значения активной ячейки. В этом случае макрос удобно запускать по сочетанию клавиш, выделив предварительно ячейку с фамилией. В разделе «3.Дополнительные сведения» этот вопрос решѐн в общем виде – ячейки с фамилиями в процессе анализа просто указываются (выделяются) мышкой.
2.Задание 1
Подготовить в соответствии с вариантом задания таблицу со списком наименований (фамилий) и значений необходимых полей. Подгото- вить макрос, в котором для указанного (среди его операторов) наиме- нования (фамилии) вычислить и записать в ячейки значения требуе- мых параметров.
Предъявить работающий макрос преподавателю. Текст макроса и схематичный вид результатов его выполнения записать в отчѐт.
Варианты заданий
1. В списке (с колонками: наименование товара, число упаковок, вес упаковки) товаров поступивших на склад за месяц по наименова- нию указанного товара определить: общее число упаковок и общий вес данного товара на складе.
2. В таблице заказов для продукта, заданного своим наименованием, определить: общую потребность (объѐм требуемого продукта) и число заказов (заявок) на данный продукт.
3. В таблице-распечатке телефонных вызовов по номеру абонента определить: число его исходящих звонков, общую продолжитель- ность разговоров и среднюю продолжительность разговора.
4. В библиотечном списке методической литературы по названию учебной дисциплины определить: число методичек и средний объ- ем (в страницах) одной методички по указанной дисциплине.
5. В таблице с аттестационными оценками школьников по пройден- ным предметам определить: среднюю оценку выбранного школь- ника и число предметов с аттестацией на «5».
6. В таблице с аттестационными оценками школьников по пройден- ным предметам определить: среднюю оценку по выбранному предмету и число отличником по данному предмету.
7. В списке поставок (за неделю) товаров со склада в магазины опре- делить: число магазинов, в которые поставляется указанный товар, а также общий объем поставляемого товара.
8. По списку повторяющихся фамилий сотрудников и часов сверх- урочной работы (в указанные дни) определить количество дней и общую сумму часов сверхурочной работы сотрудника с фамилией, указанной в этом списке.
9. Для указанной фамилии в месячной распечатке (по дням) сотруд- ников, опоздавших на работу, определить число дней с опоздания- ми и общее время опозданий.
10. В списке с названиями фирм и количеств легковых автомобилей этих фирм, проехавших по участку дороги в каждый из нескольких дней недели определить: процент всех автомобилей выбранной фирмы от общего числа автомобилей.
11. В списке (с колонками: наименование товара, вес, сорт) товаров, поступивших на склад за месяц по наименованию указанного това- ра данного сорта определить: общий вес указанного товара данно- го сорта (1-го или 2-го).
12. Для указанной фамилии в списке (по дням месяца) водителей автопредприятия, оштрафованных ГИБДД, определить: общее число нарушений правил и общую сумму штрафов.
13. Для указанной фамилии в списке спонсорских пожертвований (за год) определить: число пожертвований и среднюю сумму одно- го пожертвования.
14. Для указанной фамилии в распечатке (по дням) вложений (вкла- дов) на общий банковский счѐт определить: общую сумму вкладов и среднюю сумму одного вклада.
15. В списке (по матчам сезона) хоккеистов, иеющих удаления в матчах, для выбранного хоккеиста определить: общее время уда- лений и общее число матчей с удалениями.
3.Дополнительные сведения
