Примеры операций с рабочими листами 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

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