Номер ячейки в excel как переменная
for Serge_007,
for Serge_007,
for Serge_007,
Да. На это указывает 0 на второй позиции
=ДВССЫЛ(«R12C»&A1; 0 )
0mega,
То, что формула пишется для стиля RC вовсе не означает, что для ее правильной работы нужно переключаться в этот стиль. Работать можно в обычном.
Да. На это указывает 0 на второй позиции
=ДВССЫЛ(«R12C»&A1; 0 )
0mega,
То, что формула пишется для стиля RC вовсе не означает, что для ее правильной работы нужно переключаться в этот стиль. Работать можно в обычном. _Boroda_
Да. На это указывает 0 на второй позиции
=ДВССЫЛ(«R12C»&A1; 0 )
0mega,
То, что формула пишется для стиля RC вовсе не означает, что для ее правильной работы нужно переключаться в этот стиль. Работать можно в обычном. Автор — _Boroda_
Дата добавления — 28.04.2011 в 09:16
Как ссылаться на диапазон или ячейку в Excel VBA
В этом руководстве мы собираемся показать вам, как ссылаться на диапазон или ячейку в Excel VBA.
Основы VBA
Прежде чем углубиться в код, давайте начнем с обозначения ячейки или диапазона в VBA. Во-первых, VBA — это объектно-ориентированный язык программирования, что означает, что язык ссылается на каждый элемент как на объекты. Объекты имеют свойства, которые их определяют, и они могут инкапсулировать другие объекты или коды. Таким образом, отдельная ячейка, диапазон ячеек, рабочий лист или программное обеспечение Excel в целом являются объектом для VBA. Вы можете представить это как иерархическую модель.
Изображение иллюстрирует лишь небольшую часть объектов Excel в VBA. Экземпляр Excel содержит коллекцию Workbooks . Коллекция — это группа связанных объектов. Например, если вы откроете две рабочие книги, в коллекции Workbooks будет два объекта Workbook. Каждый объект Workbook имеет свои собственные листы в коллекции Worksheets. Эта структура применима ко всем.
VBA охватывает все ячейки и ссылки на основе ячеек в объекте с именем Range. Теоретически, когда ссылаются на конкретный объект, вам также необходимо указать его родителей. В синтаксисе VBA оператор dot (.) используется для перемещения по иерархии объектов. Например, для доступа к объекту Range код должен быть:
Однако эта ссылка неоднозначна, когда нужно указать, на какую книгу Excel вы ссылаетесь. Чтобы сделать это, вы должны указать имя или порядковый номер (начиная с 1) конкретного объекта, на который вы ссылаетесь. Этот подход аналогичен формулам Excel — аргумент указывается в круглых скобках. Например, для ссылки на объект range на рабочем листе “Sheet1” рабочей книги “Book1.xlsm”:
Вам не обязательно каждый раз указывать все родительские элементы для объекта Range . Если вы собираетесь работать на активном листе активной книги, вы можете просто использовать диапазон.
Объект диапазона
Объект Range позволяет ссылаться на ячейку или ячейки точно так же, как в любой формуле Excel. Например,
Использование квадратных скобок для ссылки на диапазон или ячейку
Вы можете использовать квадратные скобки ([]) вместо ключевого слова “Range” и двойные кавычки (“). Этот подход, по сути, является сокращением, и результат тот же.
Свойство Cells для ссылки на диапазон или ячейку
Ячейки — это имя свойства объектов как листа, так и диапазона . Это не является ни коллекцией, ни объектом. Таким образом, объектов с именем Cell не существует.
Другими словами, свойство Cells также возвращает объект Range . Каждое свойство Cells в объектах Worksheet и Range работает только в контексте своего родителя. Такое поведение может иметь больше смысла в примерах.
Синтаксис
Свойство Cells можно использовать двумя способами:
С помощью индексных номеров строк и столбцов:
Используя номер индекса ячейки:
Примеры
Worksheet.Cells
Свойство Cells объекта Worksheet возвращает ячейку в определенном месте на рабочем листе.
С помощью индексных номеров строк и столбцов:
Используя номер индекса ячейки:
Нумерация ячеек начинается с 1 и увеличивается слева направо и сверху вниз. Это означает, что A1 является первой ячейкой.
Range.Cells
С другой стороны, свойство Cells объекта Range возвращает ячейку в указанном местоположении диапазона.
С помощью индексных номеров строк и столбцов:
Используя номер индекса ячейки:
Активные объекты для ссылки на диапазон или ячейку
В VBA есть ключевые слова для ссылки на активные (выбранные) объекты во время выполнения кода.
Примеры функции АДРЕС для получения адреса ячейки листа Excel
Функция АДРЕС возвращает адрес определенной ячейки (текстовое значение), на которую указывают номера столбца и строки. К примеру, в результате выполнения функции =АДРЕС(5;7) будет выведено значение $G$5.
Примечание: наличие символов «$» в адресе ячейки $G$5 свидетельствует о том, что ссылка на данную ячейку является абсолютной, то есть не меняется при копировании данных.
Функция АДРЕС в Excel: описание особенностей синтаксиса
Функция АДРЕС имеет следующую синтаксическую запись:
Первые два аргумента данной функции являются обязательными для заполнения.
- Номер_строки – числовое значение, соответствующее номеру строки, в которой находится требуемая ячейка;
- Номер_столбца – числовое значение, которое соответствует номеру столбца, в котором расположена искомая ячейка;
- [тип_ссылки] – число из диапазона от 1 до 4, соответствующее одному из типов возвращаемой ссылки на ячейку:
- абсолютная на всю ячейку, например — $A$4
- абсолютная только на строку, например — A$4;
- абсолютная только на столбец, например — $A4;
- относительная на всю ячейку, например A4.
- [a1] – логическое значение, определяющее один из двух типов ссылок: A1 либо R1C1;
- [имя_листа] – текстовое значение, которое определяет имя листа в документе Excel. Используется для создания внешних ссылок.
- Ссылки типа R1C1 используются для цифрового обозначения столбцов и строк. Для возвращения ссылок такого типа в качестве параметра a1 должно быть явно указано логическое значение ЛОЖЬ или соответствующее числовое значение 0.
- Стиль ссылок в Excel может быть изменен путем установки/снятия флажка пункта меню «Стиль ссылок R1C1», который находится в «Файл – Параметры – Формулы – Работа с Формулами».
- Если требуется ссылка на ячейку, которая находится в другом листе данного документа Excel, полезно использовать параметр [имя_листа], который принимает текстовое значение, соответствующее названию требуемого листа, например «Лист7».
Примеры использования функции АДРЕС в Excel
Пример 1. В таблице Excel содержится ячейка, отображающая динамически изменяемые данные в зависимости от определенных условий. Для работы с актуальными данными в таблице, которая находится на другом листе документа требуется получить ссылку на данную ячейку.
На листе «Курсы» создана таблица с актуальными курсами валют:
На отдельном листе «Цены» создана таблица с товарами, отображающая стоимость в долларах США (USD):
В ячейку D3 поместим ссылку на ячейку таблицы, находящейся на листе «Курсы», в которой содержится информация о курсе валюты USD. Для этого введем следующую формулу: =АДРЕС(3;2;1;1;»Курсы»).
- 3 – номер строки, в которой содержится искомая ячейка;
- 2 – номер столбца с искомой ячейкой;
- 1 – тип ссылки – абсолютная;
- 1 – выбор стиля ссылок с буквенно-цифровой записью;
- «Курсы» — название листа, на котором находится таблица с искомой ячейкой.
Для расчета стоимости в рублях используем формулу: =B3*ДВССЫЛ(D3).
Функция ДВССЫЛ необходима для получения числового значения, хранимого в ячейке, на которую указывает ссылка. В результате вычислений для остальных товаров получим следующую таблицу:
Как получить адрес ссылки на ячейку Excel?
Пример 2. В таблице содержатся данные о цене товаров, отсортированные в порядке возрастания стоимости. Необходимо получить ссылки на ячейки с минимальной и максимальной стоимостью товаров соответственно.
Исходная таблица имеет следующий вид:
Для получения ссылки на ячейку с минимальной стоимостью товара используем формулу:
Функция АДРЕС принимает следующие параметры:
- число, соответствующее номеру строки с минимальным значением цены (функция МИН выполняет поиск минимального значения и возвращает его, функция ПОИСКПОЗ находит позицию ячейки, содержащей минимальное значение цены. К полученному значению добавлено 2, поскольку ПОИСКПОЗ осуществляет поиск относительно диапазона выбранных ячеек.
- 2 – номер столбца, в котором находится искомая ячейка.
Аналогичным способом получаем ссылку на ячейку с максимальной ценой товара. В результате получим:
Адрес по номерам строк и столбцов листа Excel в стиле R1C1
Пример 3. В таблице содержится ячейка, данные из которой используются в другом программном продукте. Для обеспечения совместимости необходимо предоставить ссылку на нее в виде R1C1.
Исходная таблица имеет следующий вид:
Для получения ссылки на ячейку B6 используем следующую формулу: =АДРЕС(6;2;1;0).
- 6 – номер строки искомой ячейки;
- 2 – номер столбца, в котором содержится ячейка;
- 1 – тип ссылки (абсолютная);
- 0 – указание на стиль R1C1.
В результате получим ссылку:
Примечание: при использовании стиля R1C1 запись абсолютной ссылки не содержит знака «$». Чтобы отличать абсолютные и относительные ссылки используются квадратные скобки «[]». Например, если в данном примере в качестве параметра тип_ссылки указать число 4, ссылка на ячейку примет следующий вид:
Так выглядит абсолютный тип ссылок по строкам и столбцам при использовании стиля R1C1.
Номер ячейки в excel как переменная
предполагая, что ваш номер строки находится в B1 , вы можете использовать INDIRECT :
это принимает ссылку на ячейку в виде строки (в этом случае конкатенация A и значение B1 — 5), и возвращает значение в этой ячейке.
это должно сделать трюк! ��
альтернативой является использование OFFSET:
предполагая, что значение столбца хранится в B1, вы можете использовать следующее
a) взятие базовой ячейки (A1)
b) добавление 0 в строку (сохраняя ее как A)
c) добавление (A5 — 1) в колонку
вы также можете использовать другое значение вместо 0 если вы хотите изменить значение строки.
Переменная ячейка в Excel
Никто не спорит, что работая в Excel, как нигде больше, можно размещать данные в таблицах и выводить их красиво на печать. Но выло бы большим, читай — смертным, грехом использовать его только для этого. Это все равно как купить калькулятор и использовать его как табло для цифр. Так вот, Excel — это, прежде всего, мощный вычислительный комплекс, позволяющий обрабатывать и анализировать внесенные в таблицы данные. Давайте рассмотрим, как, работая в Excel, заставить его выполнять вычисления.
Как вы уже, наверняка, знаете (или догадываетесь ? ), в ячейки Excel можно поместить такие данные: числа, текст, даты и формулы.
Как же Excel отличает формулы от других данных? Очень просто, по знаку равенства. Представьте, что ячейка, это некая переменная, отображающая результат вычисления. Следовательно, если приравнять ее к какому-то выражению (например, записать в ней =3+2), то результат в виде числа 5 отобразится в ячейке. А если знак равенства убрать, то в ячейке будет видно надпись 3+2. Попробуйте.
Адрес ячейки Excel
Как я уже отметил, ячейку удобно представлять как переменную (как X или Y в математике). Стало быть, записывая в ячейку данные, мы присваиваем их значение ячейке-переменной. Каждая ячейка имеет в таблице свой определенный адрес, который определяется как в шахматах по пересечению столбца и строки. Так, ячейка, расположенная на пересечении столбца В и пятой строки имеет адрес (он же ее имя) В5. Фактически это как бы переменная В5.
Это наиболее привычный нам формат адреса ячеек Excel. Но можно встретить и RC-формат, который устанавливается в настройках табличного редактора. RC расшифровывается как Row (строка) и Column (столбец). Ниже на рисунке показано, где в параметрах устанавливается (или отключается) переключение форматов ссылок. Таким образом, для той же ячейки В5 адрес в этом формате будет выглядеть как R5C2 (5-я строка и 2-й столбец). Так, с адресацией, надеюсь, разобрались.
Заставляем Excel считать
Как уже отмечалось, чтобы отобразить в ячейке расчетные данные, нужно записать в нее знак равенства и математическое выражение. Но, не будем же мы каждый раз переписывать наше выражение, изменяя в нем цифры. Гораздо удобней иметь готовую формулу, которая пересчитывает результат, как только в ней изменяются значения. То есть, чтобы записать формулу, суммирующую два числа, в ней можно указать адреса ячеек, в которые эти числа будут подставляться.
Например, поместим в ячейки A1 и В1 числа 5 и 12 соответственно, а в ячейку С1 запишем формулу суммирования =A1+В1. Как только формула будет введена, в ячейке С1 отобразится результат — число 17. Все просто! Если поменять в ячейках A1 и В1 числа на другие, то сумма в ячейке С1 будет автоматически пересчитана.
Нужно отметить, что в формулах Excel могут использоваться различные арифметические операторы: умножения (*), деления (/), сложения (+) и вычитания (-). Если отсутствуют скобки, то их приоритет стандартный, как в математике. Тут они перечислены в порядке убывания приоритета: умножение и деление имеют более высокий приоритет, чем сложение и вычитание. Операторы с одинаковым приоритетом выполняются слева направо. Но, чтобы работая в Excel не ошибиться, можно использовать скобки, тем более, что скобки облегчают чтение и анализ формул.
Внимание! Если в формуле Excel количество закрывающих и открывающих скобок не совпадает, то Excel выдаст сообщение об ошибке и предложит вариант ее исправления.
Относительные ссылки Excel
Выше были рассмотрены относительные ссылки, которые указывают на некоторые ячейки: А1, В1 и др. Их называют относительными, так как при копировании формулы в другую ячейку, содержащиеся в ней формулы изменяются на величину перемещения.
Например, если взять ячейку С1, содержащую формулу =A1+В1, и скопировать в ячейку С2, то формула в последней будет =A2+В2. Если копировать по строкам дальше, то формула будет соответственно изменяться, указывая на ячейки той строки, куда выполняется копирование. Это очень удобно для расчета данных таблицы построчно.
Допустим, есть небольшая табличка с какими-то данными. Достаточно создать формулу в итоговом столбце для 1-й строки и скопировать ее на другие строки протаскиванием. Результаты появятся в итоговом столбце для каждой строки. При этом в адресах ячеек изменятся номера строк на величину перемещения формулы.
Этот прием можно проводить и для копирования в другие столбцы, адреса ячеек в формуле изменят имена столбцов на величину смещения формулы относительно исходной (копируемой).
Абсолютные ссылки Excel
Абсолютные ссылки нужны, если мы хотим указать на ячейку, местоположение которой не должно изменяться при копировании формулы. Абсолютные ссылки ячеек записываются как $A$1, A$1 или $A1. При этом знак доллара ($) указывает на то, какая часть ссылки зафиксирована. Доллар перед именем столбца фиксирует столбец (при копировании по горизонтали имя столбца меняться не будет), а доллар перед номером строки фиксирует строку (при копировании по столбцу номер строки меняться не будет). Если же знак доллара указан и перед столбцом, и перед строкой, то ссылка останется неизменной при любом копировании.
Для быстрого изменения абсолютной ссылки на относительную используется клавиша F4. Для смена типа достаточно установить текстовый курсор на какую-нибудь ссылку в созданной формуле и нажать несколько раз клавишу F4. После каждого нажатия этой клавиши тип ссылки будет циклично меняться. Например, если установить курсор на ссылку A2 в формуле =A2+В2 и нажимать F4, то она поочередно примет вид: $A$2, A$2, $A2 и опять A2. Достаточно удобно и лучше, чем вручную забивать туда знак доллара.
Чтобы лучше разобраться в различиях ссылок, давайте рассмотрим такой пример: допустим, надо получить в столбце С сумму чисел, указанных в столбцах А и В, умноженную на некоторый коэффициент, размещенный в ячейке D1 и равный 0,5.
Для выполнения этого задания следует в ячейке с адресом С1 разместить формулу, которая будет вычислять данные. Это удобно выполнить, вводя знаки действия с клавиатуры, а адреса ячеек удобно вводить щелчком мыши на нужных ячейках (при этом их адреса будут заноситься в формулу).
После этого устанавливаем текстовый курсор на адрес ячейки D1 и нажимая на клавишу F4 делаем ссылку абсолютной — $D$1. После этого формула примет такой вид:
Теперь содержимое ячейки можно копировать на весь столбец таблицы с данными. При этом ссылки A1 и B1 будут изменяться на величину смещения, а ссылка $D$1 останется неизменной, благодаря чему значение коэффициента будет браться только из этой ячейки.
Обратите внимание, что в ячейке и в строке формул ссылки на ячейки подсвечены разными цветами. Этими же цветами отмечены ячейки, на которые они указывают.
Ссылки на другие листы книги Excel
Следует отметить, что можно также ссылаться и на другие листы текущей книги Excel (вашего файла) точно так же, как и на ячейки текущего листа. Честно говоря, можно ссылаться и на листы других книг (при этом ссылка будет называться внешней ссылкой), но об этом в другой статье.
Например, чтобы записать в ячейку А1 (Лист 1) ссылку на ячейку А15 листа 2, надо проделать следующие действия:
- установить курсор на ячейку А1 и ввести знак равенства;
- выполнить щелчок на ярлыке листа «Лист 2» (выполнится переход на этот лист);
- щелкнуть на ячейке А15 и нажать клавишу Enter;
после этого опять будет выполнен переход на Лист 1 в ячейку А15, где появится формула Excel. =Лист2!А15.
При работе в Excel редактирование формулы выполняется так же, как и редактирование текстовой строки, записанной в ячейку. Т.е. надо курсор Excel (белый крестик) установить на ячейку с формулой и выполнить двойной щелчок мышью или нажать клавишу F2. При этом в формуле появится текстовый курсор и выполняется редактирование.
Номер ячейки в excel как переменная
Введите имя пользователя, чьи записи вы хотите найти
Войти
Авторизуясь в LiveJournal с помощью стороннего сервиса вы принимаете условия Пользовательского соглашения LiveJournal
|