Vba value — как использовать функцию значения vba excel?

Понятие макроса

Термин «Макрос» слышало множество людей. Нередко при запуске таблицы появляется предупреждение: “Этот документ использует макросы, способные навредить данному компьютеру, поэтому они отключены с целью защиты от вредоносных действий”.

Макрос – действенный способ автоматизировать самые частые действия, которые нужно выполнять в электронных таблицах. Макросы – это разновидность программирования. Разработка этих подпрограмм осуществляется с помощью языка VBA. Впрочем, некоторые виды макросов не требуют навыков программирования. Ведь существует еще такое понятие, как макрорекордер. Достаточно его включить и совершить некоторые действия, как далее они будут повторяться по нажатию одной кнопки.

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

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

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

Макросы можно запускать по нажатию определенной комбинации клавиш. Например, если нажать Ctrl+J, можно запустить подпрограмму.

Интересный факт: известная программа бухгалтерского учета 1C изначально очень напоминала Excel, но потом ее функционал расширился до текущего.

Если же нужно давать компьютеру сложные инструкции, можно воспользоваться редактором Visual Basic, примеры кода в котором мы и рассмотрим немного позже.

Что еще можно почитать

Дозапись в файл на FTP (Декабрь 8, 2015)

Генератор QR-кода на VB .NET (Март 13, 2016)

Plazma — Black Bird Fly (Cover) (Сентябрь 2, 2015)

Magic Mystery (Systems In Blue Cover) (Март 30, 2016)

Получить имя компьютера по его IP-адресу (Декабрь 4, 2015)

13 комментариев: Работа с Excel — записать в ячейку значение из TextBox

Добрый вечер! Как сделать, что б запись выполнялась в пустую строку таблицы excel т.е. программа ищет пустую стоку и в нее записывает значение?

@ Игорь: Добрый. Вам нужен UsedRange, можете загуглить.

Пример будет выглядеть так (строка 7):

@ Игорь: Добрый. Вам нужен UsedRange, можете загуглить.

БОЛЬШОЕ Спасибо! Сейчас попробую погуглить и разобраться! Но я вот, что имел ввиду: есть два поля textbox1 и textbox2, кнопка button1 «Записать». Необходимо при нажатии кнопки выполнить запись из textbox1 и textbox2 в таблицу excel (например файл 1.xlsx). Причем textbox1 в столбец «В», а textbox2 в столбец «С», но в свободную строку (не заполненную), например 1, 2, 3 строки заполнены, а 4 пустая — так вот в нее!

В cells второй параметр — это и есть индекс столбца. Там указана 1, т.е. первый столбец. Вы пишите нужный. Что Вас смущает?

Работа с диапазоном в переменной

Работать с диапазоном в переменной можно точно также, как и с диапазоном на рабочем листе. Все свойства и методы объекта Range действительны и для диапазона, присвоенного переменной. При обращении к ячейке без указания свойства по умолчанию возвращается ее значение. Строки

MsgBox myRange.Cells(6) MsgBox myRange.Cells(6).Value

равнозначны. В обоих случаях информационное сообщение MsgBox выведет значение ячейки с индексом 6.

Важно: если вы планируете работать только со значениями, используйте переменные массивов, код в них работает значительно быстрее. Преимущество работы с диапазоном ячеек в объектной переменной заключается в том, что все изменения, внесенные в переменной, применяются к диапазону (который присвоен переменной) на рабочем листе

Преимущество работы с диапазоном ячеек в объектной переменной заключается в том, что все изменения, внесенные в переменной, применяются к диапазону (который присвоен переменной) на рабочем листе.

Пример 1 — работа со значениями

Скопируйте процедуру в программный модуль и запустите ее выполнение.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

SubTest1() ‘Объявляем переменную DimmyRange AsRange ‘Присваиваем диапазон ячеек SetmyRange=Range(«C6:E8») ‘Заполняем первую строку ‘Присваиваем значение первой ячейке myRange.Cells(1,1)=5 ‘Присваиваем значение второй ячейке myRange.Cells(1,2)=10 ‘Присваиваем третьей ячейке ‘значение выражения myRange.Cells(1,3)=myRange.Cells(1,1)_ *myRange.Cells(1,2) ‘Заполняем вторую строку myRange.Cells(2,1)=20 myRange.Cells(2,2)=25 myRange.Cells(2,3)=myRange.Cells(2,1)_ +myRange.Cells(2,2) ‘Заполняем третью строку myRange.Cells(3,1)=«VBA» myRange.Cells(3,2)=«Excel» myRange.Cells(3,3)=myRange.Cells(3,1)_ &» «&myRange.Cells(3,2) EndSub

Обратите внимание, что ячейки диапазона на рабочем листе заполнились так же, как и ячейки в переменной диапазона, что доказывает их непосредственную связь между собой

Пример 2 — работа с форматами

Продолжаем работу с тем же диапазоном рабочего листа «C6:E8»:

SubTest2() ‘Объявляем переменную DimmyRange AsRange ‘Присваиваем диапазон ячеек SetmyRange=Range(«C6:E8») ‘Первую строку выделяем жирным шрифтом myRange.Range(«A1:C1»).Font.Bold=True ‘Вторую строку выделяем фоном myRange.Range(«A2:C2»).Interior.Color=vbGreen ‘Третьей строке добавляем границы myRange.Range(«A3:C3»).Borders.LineStyle=True EndSub

Опять же, обратите внимание, что все изменения форматов в присвоенном диапазоне отобразились на рабочем листе, несмотря на то, что мы непосредственно с ячейками рабочего листа не работали

Пример 3 — копирование и вставка диапазона из переменной

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

Скопировать и вставить диапазон полностью со значениями и форматами можно при помощи метода Copy, указав место вставки (ячейку) на рабочем листе.

В примере используется тот же диапазон, что и в первых двух, так как он уже заполнен значениями и форматами.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SubTest3() ‘Объявляем переменную DimmyRange AsRange ‘Присваиваем диапазон ячеек SetmyRange=Range(«C6:E8») ‘Присваиваем ячейкам рабочего листа ‘значения ячеек переменной диапазона Range(«A1:C3»)=myRange.Value MsgBox«Пауза» ‘Копирование диапазона переменной ‘и вставка его на рабочий лист ‘с указанием начальной ячейки myRange.Copy Range(«E1») MsgBox«Пауза» ‘Копируем и вставляем часть ‘диапазона из переменной myRange.Range(«A2:C2»).Copy Range(«E11») EndSub

Информационное окно MsgBox добавлено, чтобы вы могли увидеть работу процедуры поэтапно, если решите проверить ее в своей книге Excel.

Чтение и запись значения ячейки в VBA

В приложении Excel все данные как правило находятся в ячейках на листах, с которыми макросы работают как с базой данных

Поэтому, начинающему программисту VBA важно понимать как читать значения из ячейки Excel в переменные или массивы и, наоборот, записывать какие-либо значения на лист в ячейки

Обращение к конкретной ячейке

Прежде чем читать или записывать значение в ячейке, нужно определиться с тем, как можно указать какая именно ячейка нам необходима.

Полный путь к ячейке A1 в Книге1 на Листе1 можно записать двумя вариантами:

• С помощью Range
• С помощью Cells

Пример 1: Обратиться к ячейке A3 находящейся в Книге1 на Листе1

Однако, как правило, полный путь редко используется, т.к. макрос работает с Книгой, в которой он записан и часто на активном листе. Поэтому путь к ячейке можно сократить и написать просто:

Пример 2: Обратиться к ячейке A1 в текущей книге на активном листе

Если всё же путь к книге или листу необходим, но не хочется его писать при каждом обращении к ячейкам, можно использовать конструкцию With End With. При этом, обращаясь к ячейкам, необходимо использовать в начале «.» (точку).

Пример 3: Обратиться к ячейке A1 и B1 в Книге1 на Листе2.

Так же, можно обратиться и к активной (выбранной в данный момент времени) ячейке.

Пример 4: Обратиться к активной ячейке на Листе3 текущей книги.

Чтение значения из ячейки

Есть 3 способа получения значения ячейки, каждый из которых имеет свои особенности:

Value2 — базовое значение ячейки, т.е. как оно хранится в самом Excel-е. В связи с чем, например, дата будет прочтена как число от 1 до 2958466, а время будет прочитано как дробное число. Value2 — самый быстрый способ чтения значения, т.к. не происходит никаких преобразований.
Value — значение ячейки, приведенное к типу ячейки. Если ячейка хранит дату, будет приведено к типу Date. Если ячейка отформатирована как валюта, будет преобразована к типу Currency (в связи с чем, знаки с 5-го и далее будут усечены).
Text — визуальное отображение значения ячейки. Например, если ячейка, содержит дату в виде «число месяц прописью год», то Text (в отличие от Value и Value2) именно в таком виде и вернет значение

Использовать Text нужно осторожно, т.к., если, например, значение не входит в ячейку и отображается в виде «#####» то Text вернет вам не само значение, а эти самые «решетки»

По-умолчанию, если при обращении к ячейке не указывать способ чтения значения, то используется способ Value.

Пример 5: В ячейке A1 активного листа находится дата 01.03.2018. Для ячейки выбран формат «14 марта 2001 г.». Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

Пример 6: В ячейке С1 активного листа находится значение 123,456789. Для ячейки выбран формат «Денежный» с 3 десятичными знаками. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка «Type mismatch». Как определить тип значения в ячейке, рассказано в следующей статье.

Пример 7: В ячейке B1 активного листа находится текст. Прочитать значение ячейки в переменную.

Таким образом, разница между Text, Value и Value2 в способе получения значения. Очевидно, что Value2 наиболее предпочтителен, но при преобразовании даты в текст (например, чтобы показать значение пользователю), нужно использовать функцию Format.

Запись значения в ячейку

Осуществить запись значения в ячейку можно 2 способами: с помощью Value и Value2. Использование Text для записи значения не возможно, т.к. это свойство только для чтения.

Пример 8: Записать в ячейку A1 активного листа значение 123,45

Все три строки запишут в A1 одно и то же значение.

Пример 9: Записать в ячейку A2 активного листа дату 1 марта 2018 года

В данном примере тоже запишется одно и то же значение в ячейку A2 активного листа.

Визуальное отображение значения на экране будет зависеть от того, какой формат ячейки выбран на листе.

Практические примеры работы с ячейками в Excel VBA

Чтение и запись данных в ячейки

Одна из основных задач работы с ячейками в Excel VBA — это чтение и запись данных. Для этого используются методы Range, Cells и Offset. Например:

• Range(«A1»).Value = «Hello World!» — записывает значение «Hello World!» в ячейку A1
• Range(«A1»).Value = Range(«B1»).Value — копирует значение из ячейки B1 в ячейку A1
• Cells(1, 1).Value = «Hello World!» — записывает значение «Hello World!» в ячейку A1
• Cells(1, 1).Offset(1, 0).Value = «Next cell» — записывает значение «Next cell» в ячейку A2 (смещение на 1 строку вниз)

Изменение внешнего вида ячеек

С помощью VBA можно изменять внешний вид ячеек. Например, задать формат ячейки:

• Range(«A1»).NumberFormat = «0.00» — задает формат чисел с двумя знаками после запятой в ячейке A1
• Range(«A1»).Font.Bold = True — задает жирный шрифт для текста в ячейке A1
• Range(«A1»).Interior.ColorIndex = 3 — задает цвет заливки ячейки A1 (3 — красный)

Работа с несколькими ячейками

В VBA можно работать с несколькими ячейками, используя объекты Range и Selection. Например, скопировать содержимое диапазона ячеек:

• Range(«A1:B2»).Copy Destination:=Range(«C1») — копирует содержимое диапазона ячеек A1:B2 и вставляет в ячейки C1:D2
• Selection.Merge — объединяет выделенные ячейки
• Range(«A1:B2»).ClearContents — удаляет содержимое ячеек в диапазоне A1:B2

Это лишь некоторые примеры, как можно работать с ячейками в Excel VBA. Знание основных методов и свойств объектов Range и Selection поможет вам создавать мощные и эффективные макросы.

7 ответов 7

Короткий ответ:

Не используйте vba, используйте формулу. В частности, комбинация ЕСЛИ и ПОИСК.

Но это проверка кода, так что давайте все равно сделаем это.

Регулярное выражение работает медленно. Кажется, вы используете его только из-за нечувствительности к регистру. Учитывая это, вы можете напрямую сравнивать значения ячеек, используя StrComp с параметром vbTextCompare. (полезная статья о StrComp)

i и j обычно используются для счетчиков цикла, но row и col в этом случае имеют больше смысла.

Вот как это может выглядеть:

Я думаю, что простое сравнение строк будет намного быстрее, чем регулярное выражение.

\$\begingroup\$ Этот код не учитывает сравнение без учета регистра, но вы правы. Регулярное выражение является излишним, и в этом случае предпочтение отдается сравнению строк. (Кстати, добро пожаловать в Code Review!) \$\endgroup\$

Могу ли я предложить сократить время выполнения/усилия на 50 %?

Разве никто не заметил, что в ОП говорится о «проверке ОДНОГО столбца, записи в СЛЕДУЮЩИЙ смежный столбец», правда? Зачем тогда зацикливать столбцы? Второй проход будет проверять либо пустую ячейку, либо ячейку со словом «Эксплуатация».

Тогда просто заполните его. Это нечувствительно к регистру.

Это автозаполнение можно выполнить двумя способами: интерактивно на листе или программно:

Интерактивно: в Excel есть функция автозаполнения. Выбрав B1 и вставив эту формулу, просто дважды щелкните маркер заполнения, который представляет собой крошечный квадрат в правом нижнем углу ячейки, когда он выбран. Excel интеллектуально скопирует формулу до конца непрерывного диапазона, содержащего данные. Это означает, что если в A1-A256 есть данные без пробелов, они будут автоматически заполнены до B256. В качестве альтернативы, если есть пробелы, прокрутите вниз и выберите B256 (или любой другой конец).Затем Ctrl + Shift + стрелка вверх, чтобы выбрать диапазон, ведущий к B1, и Ctrl+D, чтобы скопировать его вниз (думаю, d = то же самое)

Использование VBA. если вы должны сделать это программно: с формулой, содержащей ячейку в качестве вашего выбора:

Есть и другие варианты автозаполнения, позволяющие выполнять несколько крутых трюков. Может копировать буквальное значение вместо формулы или также заполнять серию на основе шаблона. Вы также можете установить пользовательские шаблоны для распознавания, например направления бизнеса, которые вы часто повторяете в вещах, или города, в которых у вас есть магазины, и т. д.

Дж.А. Гомес

В этом учебном пособии по VBA вы узнаете, как установить значение ячейки или диапазона ячеек и получить значение ячейки или диапазона ячеек.

Это руководство по VBA сопровождается книгами Excel, содержащими макросы и данные, которые я использую в приведенных ниже примерах. Вы можете получить немедленный доступ к этим примерам книг, подписавшись на информационный бюллетень Power Spreadsheets.

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

Оглавление

Использование VBA в Excel

С помощью VBA вы можете:

• Автоматизировать рутинные задачи: Например, вы можете написать скрипт, который будет автоматически заполнять определенные ячейки данными, выполнять операции с ячейками и т.д.
• Создать пользовательские функции: VBA позволяет создавать свои собственные функции, которые можно использовать в формулах Excel. Таким образом, вы можете добавить свои собственные вычисления или логику в формулы.
• Взаимодействовать с другими программами: С помощью VBA вы можете взаимодействовать с другими программами из пакета Microsoft Office, такими как Word, PowerPoint, Outlook и Access. Вы можете создавать отчеты, отправлять электронные письма и многое другое.
• Настроить пользовательский интерфейс: Вы можете создать пользовательские формы и диалоговые окна, чтобы упростить работу с данными в Excel. Это может быть полезно, если вы хотите, чтобы другие пользователи могли легко взаимодействовать с вашей книгой Excel.

Использование VBA в Excel требует некоторых знаний программирования, но даже базовые навыки в VBA могут существенно упростить вашу работу в Excel. Вы можете начать с создания макросов, которые записывают ваши действия в формате VBA, и затем изменять этот код, чтобы соответствовать вашим потребностям.

Примечание: Для запуска и отладки кода VBA в Excel, вы должны открыть режим разработчика и открыть редактор VBA. Для этого щелкните правой кнопкой мыши на ленте инструментов Excel, выберите «Настроить ленту» и активируйте флажок «Разработка». Затем в разделе «Разработка» выберите «Visual Basic» или используйте горячие клавиши «Alt» + «F11».

Назначение диапазонов объектным переменным

До сих пор мы использовали полный адрес ячеек (например, Workbooks («Book2.xlsx»). Worksheets («Sheet1»). Range («A1»)).

Чтобы сделать ваш код более управляемым, вы можете назначить эти диапазоны объектным переменным, а затем использовать эти переменные.

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

Sub CopyRange () Dim SourceRange As Range Dim DestinationRange As Range Set SourceRange = Worksheets ("Sheet1"). Range ("A1: D20") Set DestinationRange = Worksheets ("Sheet2"). Range ("A1") SourceRange.Copy DestinationRange Конец подписки

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

Пример 6

Следующая подпрограмма демонстрирует, как обрабатывать ошибки с помощью операторов OnError и Resume. Также в этом коде описывается, как открывать и читать данные с файла.

‘ Подпрограмма, для для установки определенных значений

‘ в ячейках A1 и B1 документа «Data.xls» на диске C:\ 

Sub Set_Values(Val1 As Double, Val2 As Double)

Dim DataWorkbook As Workbook

On Error GoTo ErrorHandling

‘ Открытие документа с данными

Set DataWorkbook = Workbooks.Open(«C:\Documents and Settings\Data»)

‘ Выбрать переменные Val1 and Val2 с данных в книге Excel

Val1 = Sheets(«Sheet1»).Cells(1, 1)

Val2 = Sheets(«Sheet1»).Cells(1, 2)

DataWorkbook.Close

Exit Sub

ErrorHandling:

‘ Если файл не найден, предложить пользователю найти правильную директорию

‘ после чего продолжить выполнение подпрограммы

MsgBox «Data Workbook not found;» & _

       «Please add the workbook to C:\Documents and Settings and click OK»

Resume

End Sub

После детального ознакомления с этими примерами будет значительно легче применять свои навыки на практике. 

Excel vba присвоить переменной значение ячейки

Сообщения: 197 Благодарности: 51

Материнская плата: Gigabyte P43-ES3G

HDD: 1Gb SATA-2 Hitachi Deskstar 7K1000.C; 500 Gb SATA-2 Seagate Barracuda 7200.11

Блок питания: ATX 450W

Монитор: BENQ G900AD 19′

Профиль | Отправить PM | Цитировать На последней приведенной строке возникает 1004 ошибка «Application-defined or object-defined error». Пробовал разные вариации на тему, но получается та же ошибка. Как реализовать банальнейшее присвоение значения ячейке. Понимаю, что задача банальна, но как ни смешно, не могу понять как это закодировать. Сообщения: 25778 Благодарности: 7508 На последней приведенной строке возникает 1004 ошибка «Application-defined or object-defined error». Пробовал разные вариации на тему, но получается та же ошибка. » PhilB, недостаточно приведённых данных для ответа. Как реализовать банальнейшее присвоение значения ячейке. » Именно так, как у Вас написано. Но Вы хотите присвоить ячейке не значение, а формулу. Соответственно, Вы должны использовать не свойство «.Value», а свойства .Formula/.FormulaLocal или .FormulaR1C1/.FormulaR1C1Local. … Inserting a Worksheet Function into a Cell To insert a worksheet function into a cell, you specify the function as the value of the Formula property of the corresponding Range object. In the following example, the RAND worksheet function (which generates a random number) is assigned to the Formula property of range A1:B3 on Sheet1 in the active workbook. Это сообщение посчитали полезным следующие участники: Если же вы забыли свой пароль на форуме, то воспользуйтесь данной ссылкой для восстановления пароля.

Сообщения: 197 Благодарности: 51

Ненадежные способы поиска последней строки

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

UsedRange должен НИКОГДА использоваться для поиска последней ячейки, у которой есть данные. Это очень ненадежно. Попробуйте этот эксперимент.

Введите что-то в ячейку A5 . Теперь, когда вы вычисляете последнюю строку с помощью любого из приведенных ниже методов, она даст вам 5. Теперь окрасьте ячейку A10 в красный цвет. Если вы теперь используете любой из приведенных ниже кодов, вы все равно получите 5. Если вы используете Usedrange.Rows.Count , что вы получите? Это не будет 5.

Что произойдет, если будет только одна ячейка ( A1 ), у которой есть данные? Вы попадете в последний ряд на листе! Это как выбрать ячейку A1 , а затем нажать клавишу End , а затем нажать клавишу Down Arrow . Это также даст вам ненадежные результаты, если в диапазоне есть пустые ячейки.

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

И поэтому следует избегать использования UsedRange , xlDown и CountA , чтобы найти последнюю ячейку.

Обращение к конкретной ячейке
В приложении Excel все данные как правило находятся в ячейках на листах, с которыми макросы работают как с базой данных

Поэтому, начинающему программисту VBA важно понимать как читать значения из ячейки Excel в переменные или массивы и, наоборот, записывать какие-либо значения на лист в ячейки.. ГлавнаяСтили,Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения.

ГлавнаяСтили,Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения.

Функция ИНДЕКС в Excel — синтаксис и основные способы использования

В Excel есть две версии функции ИНДЕКС — форма массива и форма ссылки. Обе их можно использовать во всех версиях Microsoft Excel 365, 2019, 2016, 2013, 2010, 2007 и 2003.

Форма массива ИНДЕКС

В данном случае функция ИНДЕКС возвращает значение элемента в таблице или массиве на основе указанных вами номеров строк и столбцов.

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

Например, формула =ИНДЕКС(C2:F11;4;3)  возвращает значение на пересечении четвертой строки и третьего столбца в диапазоне C2:F11, что является значением в ячейке D4.

Чтобы получить представление о том, как формула ИНДЕКС работает с реальными данными, взгляните на следующий пример:

Вместо того, чтобы вводить в формулу номера строк и столбцов, вы можете указать ссылки на ячейки, чтобы получить более универсальную формулу: 

Итак, эта формула ИНДЕКС возвращает количество товаров точно на пересечении номера товара, указанного в ячейке I1 (номер_строки), и номера недели, введенного в ячейке I2 (номер_столбца).

Примечание. Использование абсолютных ссылок ($C$2:$F$11) вместо относительных ссылок (C2:F11) в аргументе массива упрощает копирование формулы в другие ячейки. Кроме того, вы можете преобразовать диапазон в таблицу () и обращаться к нему по имени таблицы.

Что нужно помнить

1. Если аргумент массива состоит только из одной строки или столбца, вы можете указать или не указать соответствующий аргумент номер_строки или номер_столбца.
2. Если аргумент массива включает более одной строки, а номер_строки опущен или равен 0, функция ИНДЕКС возвращает массив всего столбца. Точно так же, если массив включает более одного столбца, а аргумент номер_столбца опущен или равен 0, формула ИНДЕКС возвращает всю строку. Вот , демонстрирующий такое поведение.
3. Аргументы номер_строки и номер_столбца должны ссылаться на ячейку в массиве; в противном случае формула ИНДЕКС вернет ошибку #ССЫЛКА!.

Форма ИНДЕКС в виде ссылки

Ссылочная форма функции ИНДЕКС Excel возвращает ссылку на ячейку на пересечении указанной строки и столбца.

ИНДЕКС(ссылка, номер_строки, , )

ссылка — это один или несколько диапазонов.

Если вы вводите более одного диапазона, разделите диапазоны точкой с запятой и заключите аргумент ссылки в круглые скобки, например (A1:B5; D1:F5).

Если каждый диапазон в ссылке содержит только одну строку или столбец, соответствующий аргумент номер_строки или номер_столбца является необязательным.

• номер_строки – порядковый номер строки в диапазоне, из которого возвращается ссылка на ячейку, аналогично форме массива.
• номер_столбца — порядковый номер столбца, из которого возвращается ссылка на ячейку, также работает аналогично форме массива.
• номер_области — необязательный параметр, указывающий, какой диапазон из ссылочного аргумента использовать. Если он не указан, формула ИНДЕКС вернет результат для первого диапазона, указанного в ссылке.

Например, формула =ИНДЕКС((B2:F4;B8:F11);3;2;2) возвращает значение ячейки С10, которая находится на пересечении 3-й строки и 2-го столбца во второй области (B8:F11).

Что нужно помнить о функции ИНДЕКС в форме ссылки

1. Если аргумент номер_строки или номер_столбца равен нулю (0), формула ИНДЕКС возвращает ссылку для всего столбца или строки соответственно.
2. Если и номер_строки, и номер_столбца опущены, функция ИНДЕКС возвращает область, указанную в аргументе номер_области.
3. Все аргументы  (номер_строки, номер_столбца и номер_области) должны ссылаться на ячейку в пределах заданного диапазона. В противном случае формула ИНДЕКС вернет ошибку #ССЫЛКА! 

Обе формулы ИНДЕКС, которые мы обсуждали до сих пор, очень просты и служат только для иллюстрации. Ваши реальные формулы, вероятно, будут намного сложнее. Поэтому давайте рассмотрим несколько наиболее эффективных способов использования ИНДЕКС в Excel.

Рекомендации по использованию макросов

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

1. Перед тем, как записывать макрос с помощью рекордера, следует заранее продумать все свои действия, поскольку автоматизироваться будут все действия (в том числе, и ошибочные).
2. Не стоит торопиться, поскольку паузы при записи макросов не учитываются. Вполне можно начать продумывать по ходу какие-то действия. А все записанные операции будут обработаны в один момент.
3. Обязательно необходимо научиться использовать режим отладки макроса. Если возникают какие-то ошибки, он поможет обнаружить, в чем причина неполадки. На первых порах без ошибок не обойтись, потому что в реальной программе будет все не так идеально, как может показаться на первый взгляд.
4. Перед использованием макросов, сделанных другими людьми, нужно настроить антивирусную программу на их обнаружение. Как правило, эта опция установлена по умолчанию.
5. Если загружаются документы из сомнительных источников, следует выбрать опцию «Отключить макросы» при их открытии. И не рекомендуется менять настройки, которые выставлены по умолчанию в настройках безопасности Excel.