Примеры обработки данных в ячейках рабочего листа 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.

Не нашли, что искали? Воспользуйтесь поиском по сайту: