Номер ячейки в excel как переменная

Задача: написать формулу следующего вида:
=МАКС(J X ;J Y )/J X -1 ,
где X и Y номера строчек в массиве записанные в отдельную таблице (проще говоря: не хочется руками менять каждую формулу, а проще забить 2 числа в таблицу и использовать из для создания ссылок)

функция =АДРЕС(P6;3) возвращает текст вида $C$488 если в P2 было забито число Х=488. Как получить адрес ячейки вместо текста??

Адресация ячеек в Excel

В Excel ячейка, строка, столбец и диапазон ячеек рассматривается как один объект Range.

Для определения диапазона используются различные средства, задаваемые с помощью следующих свойств и методов.

Range — свойство, определяющее диапазон ячеек ;

Cells — свойство, позволяющее выбирать ячейки рабочего листа;

ActiveCell — свойство, возвращающее одну активную ячейку;

Selection — метод, возвращающий выделенный объект.

Для доступа к диапазону ячеек чаще всего используются свойства Range и Cells.

Для ссылки на ячейки используется два формата:

Формат A1 (обычный). Ссылка состоит из имени столбца и номера строки. Например, D4. Ссылка может быть относительной(D4) и абсолютной ($D$4). Ссылка может указывать диапазон ячеек. Полный адрес диапазона может включать имя рабочего листа.

Формат R1C1. В данном формате R задает номер строки, C – номер столбца, ссылка является абсолютной. Для указания относительной ссылки задается смещение по отношению к активной ячейке. Смещение указывается в квадратных скобках, знак указывает направление смещения. Например, R[-2]C — ссылка на ячейку, расположенную на 2 строки выше в том же столбце. R[2]C[3] — ссылка на ячейку, расположенную на 2 строки ниже и 3 столбца правее. Ссылка на одиночную ячейку имеет вид:

[Объект.] Range (“адрес ячейки”). Для свойства Range в качестве аргумента используется любая ссылка в формате A1. Например, WorkSheets(“Лист1”).Range(“A7”)=34 или для текущего листа Range(“A7”)=34 — запись в ячейку A7 числа 34.

[Объект.] Cells(<номер строки>, <номер столбца>). Свойство используется для доступа к отдельной ячейке. В качестве аргументов указываются номер строки и столбца. Например, для ввода числа 34 в ячейку Лист1!A7, необходима команда: Sheets(1).Cells(7, 1). Value=34

Номер строки и столбца могут задаваться с помощью переменных, это позволяет обращаться к разным ячейкам таблицы.

Пример. Нужно поместить число 3500 в ячейку Прейскурант!D3. Для этого напишем процедуру с неявным объявлением переменных: i — для номера строки и j – для номера столбца.

Sub Ввод()

Sheets(“Прейскурант”).Cells(i, j). Value=3500

Для указания диапазона можно использовать разные способы. Например, для диапазона A1:D7 применимы ссылки: Range(Cells(1,1), Cells(7,4)) или Range( “A1:D7”), для отдельно стоящих ячеек можно задать перечисление: Range(“A1,B2,C3,D4”) или Range(“A2:C4,D4:D8”).

Операторы, выражения и операции

Операторы в VBA являются основными элементами кода. Последовательность операторов образует процедуру. Операторы состоят из ключевых слов и выражений, они выполняют основные операции программы. Различают простые операторы, записываемые в одну строку и сложные, называемые «структурами» (операторы условия и циклов).

Выражение – это комбинация знаков операций и операндов, а также скобки. Назначение любого выражения — получение некоторого значения. Это значение является результатом и используется командой. Примеры:

Kol>15 — логическое выражение

Str*31/100 — арифметическое выражение

Самые простые операторы – это операторы присваивания, используются для присвоения переменной нужного значения соответственно типу данных. Значение может быть задано константой или выражением, например:

Рассмотрим некоторые операторы языка VBA подробней.

Операторы выбора

Операторы выбора используются в программе для реализации условных алгоритмических конструкций, которые вызывают выполнение различных частей программы в соответствии с условиями, существующими на момент выполнения этих операторов. В VBA есть два основных оператора выбора: операторы условия If … Then и переключатели Select Case.

Оператор If … Then используется для того, чтобы выполнить какой-либо оператор или несколько операторов, если некоторое условие будет истинным.

Синтаксическая конструкция этого оператора может иметь две формы:

безальтернативную: If условие Then выражение

альтернативную: If условие Then выражение_1 Else выражение_2.

Если условие принимает значение True (Истина), то выполняется выражение 1, если False (Ложь), то выражение 2. Ветвь Else является необязательной. Например, если скидка в 5% применяется только к суммам >1000, то в VBA это можно записать так:

If Money > 1000 Then Discont = 0.05 Else Discont = 0

или, что равносильно

If Money > 1000 Then Discont = 0.05

Допускается также использование блочной формы синтаксиса, которая упрощает восприятие оператора условного перехода. Запомним, что при блочной организации оператора условного перехода используется ключевое слово End If для обозначения окончания оператора:

If Money > 1000 Then

If условие 1 Then

<группа операторов 1>

Elseif условие 2 Then

<группа операторов 2>

<группа операторов n>

сли дерево условий гораздо сложнее, чем просто проверка одного условия, используется оператор If . . . Then . . .Elseif, который позволяет проверять несколько условий:

Первым проверяется условие 1. Если оно имеет значение True, то выполняется группа операторов 1 и далее работа программы продолжится со строки, которая следует за ключевым словом End if. Если условие 1 возвращает значение False, то проверяется условие 2 и т.д., пока не встретится условие, возвращающее значение True, или не встретится ключевое слово Else.

Если нужно проверить достаточно много условий, то оператор If …Then может стать слишком сложным. В этом случае применяется оператор выбора, или переключатель Select Case, который выполняет одну из нескольких групп операторов в зависимости от значения выражения.

Операторы циклов

Циклы — это программные структуры, выполняющие важнейшую функцию в работе компьютера. Цикл – это оператор или группа операторов, которые программа многократно выполняет до тех пор, пока не получит команду начать выполнение чего-нибудь другого.

В Visual Basic существуют два основных типа циклов: циклы со счётчиком и циклы с условием.

Циклы со счётчиком (с известным числом повторений) называют ещё циклами For … Next. Синтаксис цикла со счётчиком таков:

For Счётчик цикла= НачальноеЗначение To КонечноеЗначение [Step Шаг]

Операторы: [Exit For], Next [Счётчик цикла].

Step (Шаг) – число, задающее шаг цикла, т.е. значение, на которое увеличивается или уменьшается значение счетчика на каждом шаге. Это число может быть отрицательным. Если ключевое слово Step отсутствует, то значение шага равно 1.

Exit For — оператор досрочного выхода из цикла (необязательный).

Пример. Просуммировать четные числа натурального ряда до 50 включительно, сумму поместить в ячейку рабочего листа Excel.

Программный код реализации этой задачи может выглядеть так:

Sub sum()

Dim f As Integer, sum As Integer ‘объявляются переменные типа целое число

sum = 0

For f = 2 To 50 Step 2 ‘переменной, являющейся счетчиком, присваивается начальное и конечное значение

sum = sum + f ‘переменная счетчик увеличивается на величину шага Step 2 и проверяется на достижение конечного значения

Next ‘команда передает управление команде For

Range(«A1»).Value = sum ‘вычисленная сумма помещается в ячейку А1 активного листа

Главной же особенностью циклов с условием является условие, которое может быть любым выражением, принимающим значение Истина (True) или Ложь (False). В Visual Basic есть два основных цикла с условием – цикл Do While … Loop и цикл Do Until … Loop. Оба они могут быть с предусловием или с постусловием.

Цикл с предусловием:

Do While/Until Выражение

[Exit Do]

Цикл с постусловием:

Loop While/Until Выражение

иклы Do While … Loop и Do Until … Loop имеют следующий синтаксис:

В первом случае условие задается в операторе начала цикла, во втором – в операторе конца цикла.

DO , LOOP — ключевые слова, обозначающие начало и окончание цикла;

WHILE и UNTIL — ключевые слова, определяющие тип цикла. Цикл DO WHILE выполняется до тех пор, пока условие имеет значение True (т.е.пока условие выполняется). Цикл DO UNTIL выполняется до тех пор, пока условие имеет значение False.

EXIT DO — принудительный выход из цикла.

Пример. Написать программу, подсчитывающую число сотрудников с высшим образованием и суммирующую их годовой доход. Результаты поместить в отдельные ячейки рабочего листа «Штат сотрудников» с поясняющей надписью, как показано на рис.9.

Рис. 9. Данные исходной таблицы с листа Штат сотрудников с результатами выполнения программы в ячейках I1:J2 .

Для решения данной задачи определим первую пустую ячейку в столбце В (предполагаем, что количество записей в столбце неизвестно). Введем переменную k для счета и переменную s для суммирования доходов. Переменную смещения строк i будем использовать для изменения адресации ячеек, увеличивая каждый раз на единицу.

Реализация данной задачи отражена в программном коде:

Public Sub Доходы_ВО()

Dim k As Integer, s As Long ‘объявляется переменная k целого типа для подсчета сотрудников, переменная s (длинное целое число) для суммирования доходов

i = 3 ‘неявно объявляем переменную для номера строки

s = 0 ‘обнуляем первоначальную сумму

k = 0 ‘присваиваем первоначальное значение переменной, считающей сотрудников

Do While Cells(i, 2) <> «»‘выполняем цикл, пока не встретится пустая ячейка в столбце В (т.е.конец списка фамилий: <> «»означает не равно пустой ячейке).

If Cells(i, 6) = «высшее» Then ‘проверяем условие

s = s + Cells(i, 8) ‘суммируем доходы при выполнении условия

k = k + 1 ‘подсчитываем число сотрудников при выполнении условия

End If ‘окончание блока оператора If

i = i + 1 ‘увеличиваем переменную смещения строк для изменения адресов ячеек

Loop ‘окончание цикла

Range(«I1″) = » Сумма доходов сотрудников с ВО» ‘вводим в ячейку I1 поясняющую надпись

Range(«I2») = s ‘ присваиваем вычисленную сумму ячейке I2

Range(«J1″) = » Количество сотрудников с ВО» ‘вводим в ячейку J1 поясняющую надпись

Range(«j2″) = k ‘ присваиваем результат счета ячейке J2

End Sub

Для быстрого и удобного запуска программ и макросов на рабочем листе можно поместить кнопку (или любой графический объект). Назначенные кнопке программа или макрос в этом случае запускаются автоматически при щелчке кнопки или графического объекта. Для добавления кнопки на лист необходимо вывести на экран панель инструментов «Формы», нажать на этой панели элемент управления «Кнопка», с помощью мыши придать элементу управления на листе нужный размер и в диалоговом окне «Назначить макрос объекту» выбрать нужный макрос или программу в поле Имя макроса. При добавлении другого элемента управления щелкните его правой кнопкой мыши и выберите команду Назначить макрос в контекстном меню.