Основные способы ссылок на ячейки рабочего листа Excel
Простейшие способы ссылки на значения отдельных ячеек – Range(ячейка).Value или Cells(строка, столбец).Value.
Пример 5.1 – Программа считывает число из ячейки A2, извлекает из него квадратный корень и выводит результат в ячейку A4.
Sub primer5_1()
x = Range("A2").Value
y = Sqr(x)
Range("A4").Value = y
End Sub
Это же можно реализовать и многими другими способами, например:
Sub primer5_1()
x = Cells(2, 1).Value
y = Sqr(x)
Cells(4, 1).Value = y
End Sub
или
Sub primer5_1()
Cells(4, 1).Value = Sqr((Cells(2, 1).Value))
End Sub
В качестве адресов ячеек могут использоваться не только конкретные значения, но и переменные. Например, в операторе x = Cells(i,j).Value переменная x получит значение, равное содержимому ячейки, расположенной в i-й строке и j-м столбце текущего рабочего листа. При этом переменные i и j должны иметь некоторые значения, причем они должны представлять собой целые положительные числа (если это не так, то программа прерывается с выводом сообщения об ошибке).
Можно также ссылаться на ячейки, отсчитывая их не от левого верхнего угла рабочего листа (т.е. не от ячейки A1), а от некоторой заданной ячейки. Например, ссылка Range(“E7”).Cells(3,2).Value означает ссылку на значение ячейки F9, так как, если отсчитывать ячейки от E7, то ячейка F9 находится в третьей строке и втором столбце от заданной ячейки. Эту же ссылку можно указать как Cells(7,5).Cells(3,2).Value. Вместо конкретных номеров строк и столбцов могут указываться переменные. Например, ссылка Cells(7,5).Cells(i,j).Value – этот ссылка на ячейку, расположенную в i-й строке и j-м столбце относительно ячейки E7.
Ссылки на ячейки могут также указываться относительно ячейки, выделенной с помощью мыши. Например, ссылка Selection.Cells(3,2).Value означает ссылку на значение ячейки, находящейся в третьей строке и втором столбце от выделенной ячейки. Если при этом выделено несколько ячеек, то ссылка определяется относительно левого верхнего угла выделенного диапазона.
Во многих случаях удобно указать диапазон ячеек, связав с ним некоторую переменную, а затем ссылаться на отдельные ячейки этого диапазона. Для связывания переменных с диапазонами ячеек используется оператор Set. Основные способы такого связывания следующие:
- ссылка на прямоугольный диапазон ячеек, заданный явно:
Set переменная = Range(левый_верхний_угол, правый_нижний_угол)
- ссылка на диапазон ячеек, выделенный на рабочем листе с помощью мыши:
Set переменная = Selection
- ссылка на прямоугольный диапазон ячеек, заполненный данными (например, числами) и отделенный от других данных хотя бы одной свободной строкой и столбцом:
Set переменная = ячейка.CurrentRegion
В последнем случае ячейка – это ссылка (в форме Range, Cells или Selection) на любую ячейку в заполненном диапазоне.
Примеры операторов Set, связывающих переменные с диапазонами ячеек, приведены в таблице 5.1.
Таблица 5.1 – Примеры описания диапазонов ячеек в операторе Set
| Пример | Описание |
| Set d = Range(“A2:E8”) | Переменная d связывается с диапазоном ячеек A2:E8. |
| Set d = Range(Cells(2,1),Cells(8,5)) | То же, что и в предыдущем примере. |
| Set x = Selection | Переменная x связывается с диапазоном ячеек, выделенным с помощью мыши (это может быть как одна ячейка, так и несколько). |
| Set y = Range(“E2”).CurrentRegion | Переменная y связывается с заполненным диапазоном ячеек, содержащим ячейку E2. |
| Set y = Cells(2,5).CurrentRegion | То же, что и в предыдущем примере (так как ячейка во второй строке и пятом столбце – это ячейка E2). |
| Set z = Selection.CurrentRegion | Переменная z связывается с заполненным диапазоном ячеек, содержащим выделенную ячейку. |
Пусть переменная связана с некоторым диапазоном ячеек с помощью оператора Set. Тогда ссылка переменная.Cells(i,j).Value – это ссылка на ячейку, расположенную в i-й строке и j-м столбце относительно левого верхнего угла диапазона, с которым связана указанная переменная.
Пример 5.2 – Пусть имеется следующий фрагмент программы:
Set d = Range(“A2:E7”)
Set f = Selection
x = d.Cells(1,3).Value
y = d.Cells(1,8).Value
z = Cells(1,3).Value
k = f.Cells(2,4).Value
В данном случае переменной x будет присвоено значение ячейки C2, так как эта ячейка находится в первой строке и третьем столбце относительно диапазона, связанного с переменной d. Аналогичным образом, переменная y получит значение ячейки H2. Переменная z получит значение ячейки C1, т.е. ячейки из первой строки и третьего столбца рабочего листа, так как ссылка на другой диапазон ячеек (например, на переменную d) в данном случае отсутствует, и ячейка определяется относительно рабочего листа в целом. Переменной k будет присвоено значение ячейки из второй строки и четвертого столбца относительно диапазона, выделенного на рабочем листе с помощью мыши (точнее, левого верхнего угла этого диапазона).
Для диапазона, связанного с переменной, легко определить его размеры, т.е. количество строк и столбцов. Для этого используются стандартные свойства Rows.Count и Columns.Count.
Пусть к фрагменту программы, приведенному в примере 5.2, добавлены следующие строки:
m = d.Rows.Count
n = d.Columns.Count
Переменная m в этом случае получит значение 6 (количество строк в заданном диапазоне), а переменная n – значение 5 (количество столбцов).
Примеры обработки данных в ячейках рабочего листа MS Excel
Пример 5.3– Некоторый прямоугольный диапазон ячеек на рабочем листе Excel заполнен числами. Требуется вычислить средние значения каждой строки этого диапазона ячеек и вывести их справа от диапазона. Если, например, числами заполнен диапазон B1:E5, то требуется сначала вычислить среднее значение ячеек B1:E1 и вывести его в ячейку F1, затем вычислить среднее значение ячеек B2:E2 и вывести его в ячейку F2, и т.д.
Рассмотрим несколько случаев решения этой задачи в зависимости от того, как задан диапазон ячеек, для которого вычисляются средние значения строк.
a) Вычисления выполняются для диапазона ячеек B1:E5.
Sub primer5_3a_1()
Set d = Range("B1:E5")
m = d.Rows.Count
n = d.Columns.Count
For i = 1 To m
sum = 0
For j = 1 To n
sum = sum + d.Cells(i, j).Value
Next j
srednee = sum / n
d.Cells(i, n + 1).Value = srednee
Next i
End Sub
Здесь переменная d связывается с диапазоном ячеек B1:E5. Переменные m и n получают значения, равные количеству строк и столбцов этого диапазона. Затем вычисляется среднее по каждой строке этого диапазона. Важно обратить внимание, что ссылка Cells(i, j) – это ссылка на ячейку с номером строки i и номером столбца j, причем номера строк и столбцов отсчитываются от левого верхнего угла заданного диапазона (в данном случае – от ячейки B1).
Для вычисления среднего значения каждой строки используются вложенные циклы For (см. лабораторную работу 4).
В операторе d.Cells(i, n + 1).Value = srednee вычисленное среднее значение выводится в ячейку с номером строки i и номером столбца n+1 относительно диапазона d, т.е. в столбец справа от этого диапазона.
Примечание – Если бы в программе отсутствовал оператор Set d = Range("B1:E5"), то для ссылки на диапазон ячеек каждый раз требовалось бы указывать его. Например, для определения количества строк потребовалось бы указать: m = Range("B1:E5").Rows.Count.
Конечно, рассмотренную задачу можно было решить и многими другими способами. Например, можно было воспользоваться ссылкой на ячейки в форме Cells:
Sub primer5_3a_1()
Set d = Range(Cells(1, 2), Cells(5, 5))
…
Здесь вместо обозначения ячейки B1 использовано обозначение Cells(1, 2), а вместо E5 - Cells(5, 5).
Рассмотрим еще один способ решения рассмотренной задачи: содержимое ячеек вводится в массив, который затем обрабатывается.
Sub primer5_3a_2()
Dim a() As Single, srednie() As Single
Set d = Range("B1:E5")
m = d.Rows.Count
n = d.Columns.Count
ReDim a(1 To m, 1 To n), srednie (1 To m)
For i = 1 To m
For j = 1 To n
a(i, j) = d.Cells(i, j).Value
Next j
Next i
For i = 1 To m
sum = 0
For j = 1 To n
sum = sum + a(i, j)
Next j
srednie(i) = sum / n
Next i
For i = 1 To m
d.Cells(i, n + 1).Value = srednie(i)
Next i
End Sub
Здесь значения ячеек диапазона B1:E5 считываются в двумерный массив a. Затем вычисляются средние значения строк этого массива. Эти средние значения сохраняются в одномерном массиве srednie. Элементы этого массива затем выводятся в ячейки справа от диапазона B1:E5.
б) Вычисления выполняются для произвольного диапазона ячеек, выделенного с помощью мыши.
Программа для решения этой задачи отличается от приведенной в примере 5.3a только тем, что оператор Set d = Range("B1:E5") требуется заменить на Set d = Selection. Здесь переменной d присваивается диапазон ячеек, выделенный с помощью мыши. В операторах m = d.Rows.Count и n = d.Columns.Countпеременные m и n получают значения, равные количеству строк и столбцов этого диапазона. Дальнейшие действия выполняются аналогично примерам, рассмотренным выше.
в) Вычисления выполняются для произвольного прямоугольного диапазона ячеек. Известно, что одна из ячеек этого диапазона – B1.
В программе, приведенной в примере 5.3a, требуется заменить оператор Set d = Range("B1:E5") на Set d = Range("B1").CurrentRegion. Здесь переменной d присваивается заполненный данными прямоугольный диапазон ячеек, одна из которых – B1.
г) Вычисления выполняются для произвольного прямоугольного диапазона ячеек, одна из которых выделена с помощью мыши.
В программе, приведенной в примере 5.3a, требуется заменить оператор Set d = Range("B1:E5") на Set d = Selection.CurrentRegion.
Пример 5.4 – На рабочем листе Excel в столбце C, начиная с ячейки C2 (т.е. в ячейках C2, C3 и т.д.), введены фамилии студентов, а в столбцах D, E, F, G – их оценки по четырем предметам. Требуется получить в столбце L (начиная с ячейки L1) список студентов, имеющих средний балл не ниже минимально допустимого. В столбце M рядом с фамилиями студентов должны выводиться их средние баллы. Минимально допустимый средний балл вводится с клавиатуры.
Sub primer5_4()
Dim min_ball As Single
min_ball = InputBox("Введите минимально допустимый средний балл: ")
Set d = Range("C2").CurrentRegion
m = d.Rows.Count
n = d.Columns.Count
k = 0
For i = 1 To m
sum = 0
For j = 2 To n
sum = sum + d.Cells(i, j).Value
Next j
srednee = sum / (n-1)
If srednee >= min_ball Then
k = k + 1
Cells(k, 12).Value = d.Cells(i,1).Value
Cells(k, 13).Value = srednee
End If
Next i
End Sub
Здесь в операторе Set d = Range("C2").CurrentRegion переменной d присваивается диапазон заполненных ячеек, заполненных данными (фамилиями и оценками); одна из этих ячеек – C2. Переменная m получает значение – количество строк диапазона d (в данном примере – количество студентов, для которых введены данные). Переменная n– количество столбцов диапазона d. Если данные введены в соответствии с постановкой задачи, то переменная n должна получить значение 5, так как диапазон данных в этом примере содержит пять столбцов: в столбце C – фамилии студентов, в столбцах D-G – их оценки.
Цикл For i = 1 To m предназначен для перебора строк (каждая строка содержит данные об одном студенте). Для каждого студента вычисляется средний балл. Цикл For j = 2 To n предназначен для перебора оценок студента (т.е. столбцов). Начальное значение переменной j, используемой в качестве номера столбца, равно двум (а не одному), так как оценки, по которым вычисляются среднее, начинаются со второго столбца в диапазоне данных (в первом столбце находятся фамилии, а не оценки). Величина d.Cells(i, j).Value – это значение ячейки с оценкой студента. Следует еще раз обратить внимание, что номера ячеек (значения i и j) отсчитываются для диапазона d, т.е. от ячейки C2.
Если вычисленный средний балл оказывается не ниже заданной минимальной величины (т.е. выполняется условие srednee >= min_ball), то фамилия студента выводится в столбец L (в 12-й столбец рабочего листа), а его средний балл – в столбец M (13-й столбец). Переменная k– номер строки рабочего листа, куда выводится фамилия студента; при выводе каждой фамилии она увеличивается на единицу.
Следует обратить внимание на строку Cells(k,12).Value=d.Cells(i,1).Value. Здесь Cells(k, 12).Value – ячейка, расположенная в k-й строке и 12-м столбце (т.е. столбце L) рабочего листа Excel. Этой ячейке присваивается значение d.Cells(i, 1).Value, т.е. содержимое ячейки, расположенной в i-й строке и первом столбце диапазона d (в этой ячейке находится фамилия студента).
Пример 5.5 – На рабочем листе Excel введены данные о студентах (см. исходные данные для примера 5.4). Требуется удалить данные обо всех студентах, имеющих средний балл ниже минимально допустимого.
Sub primer5_5()
Dim min_ball As Single
min_ball = InputBox("Введите минимально допустимый средний балл: ")
Set d = Range("C2").CurrentRegion
m = d.Rows.Count
n = d.Columns.Count
i = 1
Do While i <= m
sum = 0
For j = 2 To n
sum = sum + d.Cells(i, j).Value
Next j
srednee = sum / (n-1)
If srednee < min_ball Then
For k = i to m-1
For j = 1 to n
d.Cells(k, j).Value = d.Cells(k+1, j).Value
Next j
Next k
For j = 1 to n
d.Cells(m, j).Value = Empty
Next j
m = m-1
Else
i = i+1
End If
Loop
End Sub
В данном случае количество строк в диапазоне с данными (переменная m) не является постоянной величиной, а может изменяться, так как некоторые строки (данные о студентах) будут удаляться. Поэтому для перебора строк использован цикл Do While.
Если для i-го студента (т.е. в i-й строке) вычисленный средний балл оказывается ниже заданной минимальной величины, то данные об этом студенте требуется удалить. Следующая группа операторов смещает все последующие данные, начиная с i+1-й строки, на одну строку выше:
For k = i to m-1
For j = 1 to n
d.Cells(k, j).Value = d.Cells(k+1, j).Value
Next j
Next k
Другими словами, в i-ю строку записываются данные из i+1-й строки, в i+1-ю – из i+2-й строки, …, в m-1-ю – из m-й (т.е. из последней) строки. Затем последняя (m-я) строка очищается: ее ячейкам присваивается значение Empty.
После этого общее количество строк в диапазоне (или, другими словами, номер последней строки) уменьшается на единицу: m = m-1.
Если же для i-го студента средний балл оказался не ниже минимально необходимого, то просто выполняется переход к данным о следующем студенте (к следующей строке диапазона данных): i = i+1.
Примечание – В этом примере показано, как очистить ячейку. Если требуется проверить, пуста ли ячейка, то используется функция IsEmpty. Например, функция IsEmpty(Cells(1,5).Value) возвращает значение True, если ячейка E1 пуста, и значение False, если в этой ячейке есть какая-либо величина.
Пример 5.6 – На рабочем листе Excel введены данные о контрактах на поставку некоторых товаров (по каждому контракту поставляется один товар). Для каждого контракта в столбце A указан его номер, в столбце B – название поставляемого товара, в столбце C – количество товара. Один и тот же товар может поставляться по нескольким контрактам, но цена товара во всех контрактах одинакова. В столбце F перечислены названия товаров, в столбце G – цены на них. Фрагмент исходных данных для задачи приведен на рисунке 5.1. Требуется вычислить и вывести в столбец D стоимость каждого контракта. В конце столбца стоимостей требуется вывести сумму стоимостей всех контрактов.
Рисунок 5.1 – Фрагмент исходных данных для примера 5.6
Sub primer5_6()
Set d1 = Range("A1").CurrentRegion ‘d1 – диапазон ячеек с данными о контрактах
Set d2 = Range("F1").CurrentRegion ‘d2 - диапазон ячеек с данными о товарах
m1 = d1.Rows.Count ‘ Количество контрактов
m2 = d2.Rows.Count ‘ Количество товаров
For i = 1 To m1 ‘ Перебор всех контрактов
nazv = d1.Cells(i,2).Value ‘ Переменной nazv присваивается название товара, поставляемого
‘ по контракту
kol = d1.Cells(i,3).Value ‘ Переменной kol присваивается количество поставляемого товара
For j = 1 To m2 ‘ Перебор данных о товарах (поиск цены на товар)
If nazv = d2.Cells(j,1).Value then ‘Если товар найден
cena = d2.Cells(j,2).Value ‘Цена на товар присваивается переменной cena
stoimost = cena*kol ‘ Вычисление стоимости контракта
d1.Cells(i,4).Value = stoimost ‘ Стоимость выводится в i-ю строку, столбец D
sum_stoimost = sum_stoimost + stoimost ‘Суммируется стоимость всех контрактов
End If
Next j
Next i
d1.Cells(m1+1,4).Value = sum_stoimost ‘В строку m1+1 (в конце столбца стоимостей)
‘выводится сумма стоимостей всех контрактов
End Sub
В данном примере используются два диапазона: d1 – диапазон ячеек с данными о контрактах, d2 – с данными о товарах и ценах на них. Если считать, что на рисунке 5.1 показаны все данные, то диапазон d1 содержит ячейки A1:C7, а d2 – ячейки F1:G4. Переменная i используется в качестве номера строки в диапазоне d1, переменная j – также номер строки, но в диапазоне d2.
Примеры операций с рабочими листами MS Excel
Во многих случаях данные, требующие обработки, размещаются на нескольких рабочих листах MS Excel. Если требуется указать рабочий лист, на котором находится ячейка, то перед ссылкой на нее (т.е. перед словом Range или Cells) указывается слово Worksheets("имя_листа").
Пример 5.7 –На рабочем листе Лист1 в столбец введено несколько чисел. Курсор находится в одной из ячеек с этими числами. Требуется скопировать на другой рабочий лист (имя листа – Лист5) все числа, превышающие 25. Числа должны копироваться в столбец E, начиная с ячейки E2. Между числами не должно быть пустых ячеек. Другими словами, если из десяти заданных чисел только три числа превысят 25, то они должны быть выведены в ячейки E2, E3 и E4 на рабочем листе Лист5.
Sub primer5_7()
Set d=Selection.CurrentRegion
m=d.Rows.Count
Set vyvod = Worksheets("Лист5").Range("E2")
j = 0
For i = 1 To m
x = d.Cells(i, 1).Value
If x > 25 Then
j = j + 1
vyvod.Cells(j, 1) = x
End If
Next i
End Sub
В операторе Set d=Selection.CurrentRegion переменная d связывается с заполненным диапазоном ячеек, содержащим выделенную ячейку. Затем в операторе m=d.Rows.Count переменной m присваивается количество строк в заполненном диапазоне, т.е. количество чисел в столбце. В операторе Set vyvod = Worksheets("Лист5").Range("E2") переменная vyvod связывается с ячейкой E2 на рабочем листе Лист5. Эта переменная будет затем использоваться для ссылок на ячейки, в которые будут выводиться отобранные числа. Переменная j– номер строки на рабочем листе Лист5 (начиная с ячейки E2), куда будет копироваться число из исходного набора данных. Сначала эта переменная принимается равной нулю. ЦиклFor i = 1 To mиспользуется для перебора ячеек с числами. В операторе x = d.Cells(i, 1).Value значение ячейки из i-й строки текущего столбца присваивается переменной x. При этом не требуется указывать, что ячейка находится на рабочем листе Лист1, так как этот рабочий лист – текущий. Если значение x превышает 25, то переменная j увеличивается на единицу, и переменная xвыводится в соответствующую ячейку рабочего листа Лист5.
Примечание – Можно было не использовать переменную vyvod, а указать оператор вывода отобранных чисел в ячейку следующим образом: Worksheets("Лист5").Range("E2"). Cells(j, 1) = x.
Пример 5.8 – Пусть в условиях примера 5.7 рабочий лист Лист5, на который требуется выводить отобранные числа, еще не существует. Программа в этом случае будет следующей.
Sub primer5_8()
Set d=Selection.CurrentRegion
m=d.Rows.Count
Set NewSheet = Worksheets.Add
NewSheet.Name = "Лист5"
Worksheets("Лист1").Activate
Set vyvod = Worksheets("Лист5").Range("E2")
j = 0
…
… ‘См. пример 5.7
…
End Sub
Здесь в операторе Set NewSheet = Worksheets.Add создается новый рабочий лист. При этом он становится текущим. В операторе NewSheet.Name = "Лист5" ему присваивается имя Лист5. В операторе Worksheets("Лист1").Activate текущим становится рабочий лист Лист1. Последующий текст программы такой же, как в примере 5.7.
В данном случае важно, чтобы оператор Set d=Selection.CurrentRegion был указан до оператора Set NewSheet = Worksheets.Add. Это необходимо, чтобы программа определила текущий диапазон ячеек (и «запомнила» его в переменной d), прежде чем текущим станет другой (новый) рабочий лист.
Пример 5.9 – В условиях примера 5.4 требуется вывести перечень отобранных студентов на новый рабочий лист с именем Отобранные. На этом листе фамилии студентов должны выводиться в столбец A (начиная с ячейки A1), а средние баллы – в столбец B.
Для этого необходимо предусмотреть в программе создание рабочего листа с именем Отобранные (аналогично примеру 5.8). Операторы вывода результатов в ячейки рабочего листа будут иметь следующий вид:
Worksheets("Отобранные").Cells(k, 1).Value = d.Cells(i, 1).Value
Worksheets("Отобранные").Cells(k, 2).Value = srednee
Пример 5.10 – Пусть в условиях примера 5.6 информация о ценах на товары хранится на отдельном рабочем листе с именем Цены. Названия товаров указаны в столбце A, а цены – в столбце B.
В программе, приведенной в примере 5.6, достаточно заменить оператор, задающий диапазон ячеек с ценами, на следующий:
Set d2 = Worksheets("Цены").Range("A1").CurrentRegion
Варианты заданий
Примечание – В этой лабораторной работе рекомендуется выполнить один из вариантов 1-7 и один из вариантов 8-14.
Вариант 1 –На рабочем листе имеется заполненная числами прямоугольная область (будем называть ее исходной). Программа должна скопировать в другую область рабочего листа (область результатов) все строки исходной области, кроме строк, заполненных некоторым числом. Это число запрашивается с клавиатуры. Пример исходных данных и результатов программы приведен на рисунке 5.2 (копируются все строки, кроме заполненных числом 7). Кроме того, программа должна выводить на экран количество скопированных строк.
Рисунок 5.2 – Исходные данные и результаты для варианта 1
Вариант 2 –На рабочем листе имеется некоторая прямоугольная область, заполненная числами (будем называть ее исходной). Программа должна из каждой строки исходной области скопировать в другую область рабочего листа (область результатов) все числа, превышающие некоторое заданное число. Это число запрашивается с клавиатуры. Пример исходных данных и результатов программы приведен на рисунке 5.3 (копируются все числа, превышающие 4). Кроме того, программа должна выводить на экран общее количество скопированных чисел.
Рисунок 5.3 – Исходные данные и результаты для варианта 2
Вариант 3 –На рабочем листе имеется заполненная числами прямоугольная область. Программа должна определять минимальный элемент в первом столбце этой области, а затем менять местами строку, где этот элемент находится, с последней строкой области. Кроме того, программа должна выводить на экран номера строк, которые поменялись местами.
Вариант 4 –На рабочем листе расположены две прямоугольные области, заполненные числами (будем называть их первой и второй областью). Размеры областей одинаковы. Программа должна сравнивать суммы соответствующих строк этих областей. Если сумма строки из первой области оказывается меньше, чем сумма соответствующей строки из второй области, то эти строки меняются местами (т.е. строка с большей суммой переносится из второй области в первую, а строка с меньшей суммой – из первой области во вторую). Кроме того, программа должна выводить на экран количество случаев, когда строки менялись местами.
Вариант 5 –На рабочем листе имеется прямоугольная область, заполненная числами. Пользователь вводит с клавиатуры некоторое число. Программа определяет в каждой строке выделенной области число, ближайшее к введенному. Номера этих чисел выводятся на рабочий лист в отдельный столбец (например, если введено число 5, а в некоторой строке есть числа 8, 12, 4 и 7, то для этой строки должен быть выведен номер 3).
Вариант 6 –На рабочем листе имеется прямоугольная область, заполненная числами. Программа должна вычислить для каждого столбца области среднее значение и поменять местами столбцы с максимальным и минимальным средним значением. Кроме того, номера столбцов, которые поменялись местами, должны выводиться на экран.
Вариант 7 – На рабочем листе имеется заполненная числами прямоугольная область из m строк и n столбцов (будем называть эту область ячеек первой). Каждая строка представляет собой координаты некоторой точки в n-мерном пространстве. На этом же рабочем листе в другом месте расположена строка из n чисел – координаты еще одной точки в n-мерном пространстве (будем называть эту область ячеек второй). Программа должна находить среди точек n-мерного пространства, указанных в первой области, точку, ближайшую к указанной во второй области. Номер найденной ближайшей точки должен выводиться на экран.
Указание – Расстояние между точками вычисляется как квадратный корень из суммы квадратов разностей их координат. Квадратный корень в VBA вычисляется функцией Sqr.
Вариант 8 – На рабочем листе Цены в столбце A, начиная с ячейки A1, введены названия товаров, в столбце B – цены на эти товары, в столбце C – названия валют, в которых указаны цены. Может быть указано несколько товаров, цены которых выражены в одной и той же валюте.
На рабочем листе Курсы в столбце A, начиная с A1, перечислены названия валют, в столбце B – их курсы в долларах. Курс каждой валюты указывается один раз.
Программа должна вычислять цены товаров в долларах (если исходная цена была указана в долларах, то она не должна изменяться). Вычисленные цены должны выводиться в столбец D рабочего листа Цены. Должно также подсчитываться и выводиться на экран количество товаров, для которых потребовался пересчет цены в доллары (т.е. количество товаров, для которых исходная цена была указана не в долларах).
Вариант 9 – На рабочем листе Работники в столбце A (начиная с ячейки A1) введены фамилии работников, в столбце B – номера отделов, где они работают, в столбце C – зарплаты. Может быть указано несколько работников, работающих в одном и том же отделе.
На рабочем листе Повышение в столбце A, начиная с A1, перечислены номера отделов для которых повышается зарплата, в столбце B – коэффициенты повышения зарплаты для работников данного отдела. Для каждого отдела коэффициент повышения зарплаты указывается только один раз (т.е. он одинаков для всех работников отдела). Некоторые отделы могут быть не указаны на листе Повышение (для них зарплата не повышается).
Программа должна вычислять новые зарплаты (путем умножения старой зарплаты на коэффициент повышения). Новые зарплаты должны указываться в столбце C рабочего листа Работники вместо старых зарплат. Должно также подсчитываться и выводиться на экран количество всех работников, для которых повышается зарплата.
Вариант 10 – На рабочем листе Контракты в столбце A, начиная с ячейки A1, введены номера контрактов, в столбце B – названия товаров, продаваемых по этим контрактам (по каждому контракту – один товар), в столбце C – количество товара, в столбце D - цены, по которым продаются товары. Может быть указано несколько контрактов на продажу одного и того же товара.
На рабочем листе Итоги в столбце A, начиная с A1, перечислены названия товаров. Каждый товар указывается один раз.
Для каждого товара программа должна вычислять количество контрактов на поставку данного товара и суммарное количество данного товара, поставляемое по всем контрактам. Количество контрактов на поставку товара должно выводиться в столбец B рабочего листа Итоги, а количество поставляемого товара по всем контрактам – в столбец C этого рабочего листа. Должна также подсчитываться и выводиться на экран суммарная стоимость всех контрактов.
Вариант 11 – На рабочем листе Контракты в столбце A, начиная с ячейки A1, введены номера контрактов, в столбце B – названия товаров, проданных по этим контрактам (по каждому контракту – один товар), в столбце C – цены, по которым проданы товары. Может быть указано несколько контрактов на продажу одного и того же товара.
На рабочем листе Ограничения в столбце A, начиная с A1, перечислены названия товаров, в столбце B – предельные цены на них (т.е. товары запрещается продавать по ценам, превышающим эти величины). Предельная цена на каждый товар указывается только один раз. Некоторые товары не указываются на листе Ограничение (для них цена не ограничивается).
Программа должна для каждого контракта, где цена превышает предельную, заменять ее (в рабочем листе Контракты) на соответствующую предельную цену. Должно также подсчитываться и выводиться на экран количество контрактов, для которых потребовалось снижение цены.
Вариант 12 – На рабочем листе Контракты в столбце A, начиная с ячейки A1, введены номера контрактов, в столбце B – названия товаров, проданных по этим контрактам (по каждому контракту – один товар), в столбце C – количество товара, проданного по каждому контракту, в столбце D - цены, по которым проданы товары. Может быть указано несколько контрактов на продажу одного и того же товара.
На рабочем листе Ставки в столбце A, начиная с A1, перечислены названия товаров, в столбце B – ставки налогов по контрактам на эти товары. Ставка налога для каждого товара указывается только один раз. Например, если в ячейке A1 указано название товара – компьютер, а в ячейке B1 – ставка 12%, это означает, что с каждого контракта на поставку компьютеров выплачивается налог в размере 12% от его полной стоимости.
Программа должна для каждого контракта вычислять выплачиваемый за него налог. Величины налогов должны выводиться в столбце E рабочего листа Контракты. Должна также подсчитываться и выводиться на экран сумма налогов по всем контрактам.
Вариант 13 – На рабочем листе Контракты в столбце A, начиная с ячейки A1, введены номера контрактов, в столбце B – названия товаров, продаваемых по этим контрактам (по каждому контракту – один товар), в столбце C – количество товара, в столбце D - цены, по которым продаются товары. Может быть указано несколько контрактов на продажу одного и того же товара.
На рабочем листе Повышение в столбце A, начиная с A1, перечислены названия товаров, в столбце B – коэффициенты повышения цены на товары. Коэффициент повышения цены для каждого товара указывается только один раз. Например, если в ячейке A1 указано название товара – компьютер, а в ячейке B1 – коэффициент 1,2, это означает, что цена компьютеров во всех контрактах должна быть повышена в 1,2 раза. Некоторые товары не указываются на листе Повышение (для них цена не повышается).
Программа должна для каждого контракта вычислять новую цену (путем умножения старой цены на коэффициент повышения). Новые цены должны указываться в столбце D рабочего листа Контракты (вместо старых цен). Должно также подсчитываться и выводиться на экран количество контрактов, для которых потребовалось повышение цены.
Вариант 14 –На рабочем листе Контракты в столбце A, начиная с ячейки A1, введены номера контрактов, в столбце B – названия товаров, продаваемых по этим контрактам (по каждому контракту – один товар), в столбце C – количество товара, в столбце D - цены, в столбце E – названия заказчиков (у каждого контракта один заказчик).
На рабочем листе Заказчики в столбце A, начиная с A1, перечислены названия заказчиков. Каждый заказчик указывается один раз.
Для каждого заказчика программа должна подсчитывать количество контрактов и их общую стоимость. Эти величины должны выводиться в столбцы B и C рабочего листа Заказчики. Должно также подсчитываться и выводиться на экран количество заказчиков, для которых нет ни одного контракта.
ЛАБОРАТОРНАЯ РАБОТА №6