Как найти номер строки в excel по значению ячейки vba
Возвращает значение или ссылку на значение из таблицы или диапазона. Существует две формы функции Index : форма массива и эталонная форма.
Синтаксис
выражение Переменная, представляюная объект WorksheetFunction .
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Arg1 | Обязательный | Variant | Array или Reference — это диапазон ячеек или константа массива. Для ссылок это ссылка на один или несколько диапазонов ячейки. |
Arg2 | Обязательный | 64-разрядное число с плавающей запятой двойной точности. | Row_num — выбирает строку в массиве, из которой можно вернуть значение. Если row_num не будет, column_num требуется. Для ссылок, номер строки в ссылке, из которой можно вернуть ссылку. |
Arg3 | Необязательный | Variant | Column_num — выбирает столбец в массиве, из которого возвращается значение. Если column_num не будет, row_num требуется. Для справки, номер столбца, из которого нужно вернуть ссылку. |
Arg4 | Необязательный | Variant | Area_num — используется только при возвращении ссылок. Выбирает диапазон ссылок, из которого можно вернуть пересечение row_num и column_num. Первая область, выбранная или вступив, имеет номер 1, вторая — 2 и так далее. Если area_num опущен, индекс использует область 1. |
Возвращаемое значение
Variant
Примечания
Форма массива
Возвращает значение элемента в таблице или массиве, выбранном индексами номеров строки и столбцов.
Используйте форму массива, если первый аргумент index — это константа массива.
Если используются row_num и column_num, индекс возвращает значение в ячейке на пересечении row_num и column_num.
Если вы row_num или column_num 0 (ноль), Index возвращает массив значений для всего столбца или строки соответственно. Чтобы использовать значения, возвращаемые в качестве массива, введите функцию Index в качестве формулы массива в горизонтальном диапазоне ячеек для строки и в вертикальном диапазоне ячеек для столбца. Чтобы ввести формулу массива, нажмите кнопку Ctrl+Shift+Enter.
Форма ссылок
Возвращает ссылку ячейки на пересечении определенной строки и столбца. Если эталон состоит из неадиацентных выборов, можно выбрать выбор, который нужно посмотреть. Если каждая область в ссылке содержит только одну строку или столбец, аргумент row_num или column_num, соответственно, необязательный. Например, для ссылки на одну строку используйте INDEX(reference,column_num).
После ссылки и area_num выбрали определенный диапазон, row_num и column_num выберите определенную ячейку: row_num 1 — первая строка в диапазоне, column_num 1 — первый столбец и так далее. Ссылка, возвращаемая Индексом , является пересечением row_num и column_num.
Если вы row_num или column_num 0 (ноль), Index возвращает ссылку для всего столбца или строки соответственно.
Результат функции Index является эталонным и интерпретируется как таковой другими формулами. В зависимости от формулы возвращаемая стоимость Index может использоваться в качестве ссылки или в качестве значения. Например, формула эквивалентна CELL("width",INDEX(A1:B2,1,2)) CELL("width",B1) . Функция CELL использует возвращаемую величину Index в качестве ссылки ячейки. С другой стороны, такая формула, 2*INDEX(A1:B2,1,2) как перевод возвращаемого значения Index в число в ячейке B1.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Основное назначение этой функции в том, чтобы искать позицию заданного элемента в наборе значений. Чаще всего она применяется для поиска порядкового номера ячейки в диапазоне, где лежит нужное нам значение.
Синтаксис этой функции следующий:
=ПОИСКПОЗ( Что_ищем ; Где_ищем ; Режим_поиска )
- Что_ищем - это значение, которое надо найти
- Где_ищем - это одномерный диапазон или массив (строка или столбец), где производится поиск
- Режим_поиска - как мы ищем: точно (0), с округлением в большую строну (-1) или в меньшую сторону (1)
Давайте рассмотрим несколько полезных вариантов ее применения на практике.
Точный поиск
Классический сценарий - поиск точного текстового совпадения для нахождения позиции нужного нам текста или числа в списке:
Поиск первой или последней текстовой ячейки
Если в качестве искомого значения задать звездочку, то функция будет искать первую ячейку с текстом и выдавать её позицию. Для поиска последней текстовой ячейки можно изменить третий аргумент Режим_поиска с нуля на минус 1:
Числа и пустые ячейки в этом случае игнорируются.
Поиск ближайшего числа или даты
Если последний аргумент задать равным 1 или -1, то можно реализовать поиск ближайшего наименьшего или наибольшего числа. Таблица при этом обязательно должна быть отсортирована по возрастанию или убыванию соответственно. В общем и целом, это чем-то похоже на интервальный просмотр у функции ВПР (VLOOKUP) , но там возможен только поиск ближайшего наименьшего, а здесь - есть выбор.
Например, нам нужно выбрать генератор из прайс-листа для расчетной мощности в 47 кВт. Если последний аргумент задать равным 1 и отсортировать таблицу по возрастанию, то мы найдем ближайшую наименьшую по мощности модель (Зверь):
Если же третий аргумент равен -1 и таблица отсортирована по убыванию, то мы найдем ближайшую более мощную модель (Бомба):
Связка функций ПОИСКПОЗ и ИНДЕКС
Очень часто функция ПОИСКПОЗ используется в связке с другой крайне полезной функцией - ИНДЕКС (INDEX) , которая умеет извлекать данные из диапазона по номеру строки-столбца, реализуя, фактически, "левый ВПР".
Так, в предыдущем примере получить не номер, а название модели генератора можно очень легко:
Ну, и поскольку Excel внутри хранит и обрабатывает даты как числа, то подобный подход на 100% работает и с датами. Например, мы можем легко определить на каком этапе сейчас находится наш проект:
Принципиальное ограничение функции ПОИСКПОЗ состоит в том, что она умеет искать только в одномерных массивах (т.е. строчке или столбце), но никто не запрещает использовать сразу два ПОИСКПОЗа вложенных в ИНДЕКС, чтобы реализовать двумерный поиск по строке и столбцу одновременно:
Здраствуйте!
Столкнулся со следующей проблемой:
Есть колонка, содержащая данные в след виде
А1
1 Иванов
2 Иванов
3 Иванов
4 Сидоров
5 Сидоров
6 Петров
7 Петров
8 Петров
Очень хочется (точнее очень надо, а то руки отваливаются вставлять пустые строки и в глазах троится, когда записей около 3000 шт):
1. Найти номер строки где начинаются след одинаковые значения
2. Добавить 2 пустые строки
3. Добавить в пустые строки столбца Фамилии
4. Создать переменные, содержащие номер строки и номер столбца, для посл операций с данными с соседними столбцами (как получить номер)
И так для каждой фамилии пока не кончатся данные
В результате:
А1
1 Иванов
2 Иванов
3 Иванов
4 Иванов – добавл строки и фамилии
5 Иванов – добавл строки и фамилии
6 Сидоров
7 Сидоров
8 Сидоров – добавл строки и фамилии
9 Сидоров – добавл строки и фамилии
10 Петров
11 Петров
12 Петров
13 Петров – добавл строки и фамилии
14 Петров – добавл строки и фамилии
Очень жду ответа. Думаю код будет не сложный, по крайней мере я на это надеюсь.
Заранее огромнейшей СПАСИБ.
Алексей.
Как в Excel удалить строки с повторяющимися значениями в определенном столбце
Пожалуйста, подскажите - есть таблица. Нужно - если значение (текстовое) в ячейке С2 = С1, удалить.
Как красиво определить наличие дубликатов в столбце Листа Excel?
См. сабж. По-моему, эта тема где-то уже обсуждалась, но что-то не нашёл я её. 'Красиво' - это без.
Найти номер последней заполненной ячейки в столбце, Excel
Добрый день! Импортирую данные из таблицы Excel,таблица состоит из двух столбцов. Первый очень.
Как программно получить номер строки в Excel?
Кто знает как программно получить номер строки на которой выделена ячейка в базе данных Excel.
Если хочешь, чтобы проблем было меньше, то для начала разбей фамилии и номера по отдельным ячейкам. Код, действительно, несложный. Однако, не следует усложнять жизнь самому себе. Поэтому послушайся первого совета.
Не совсем понятна идея с переменными. Неужели ты их хочешь сначала насоздавать (3000 штук), а потом использовать? Опиши подробнее использование этих непонятных переменных и получишь код.
Имеется след табл ведется ежедневно
Иванов
вр нач вр кон длит операц К Вр ед Норм вр Оплач вр
07:00 08:00 60 мин делал то-то 80 шт 0,75 мин 0,5 мин 30 мин
08:00 09:00 60 мин делал то-то 100 шт 1,25 мин 1,0 мин 60 мин
сидоров
07:00 08:00 делал то-то и далее как у иванова
08:00 09:00 делал то-то и далее как у иванова
Далее надо
1. Просмотреть столбец фамилия
2. После Иванова добавить строки
3. После Сидорова добавить строки
вр нач вр кон операц К Вр ед Нвр Оплач время
Иванов 07:00 08:00 делал то-то 80 шт 0,75 мин 0,5 мин 40 мин
Иванов 08:00 09:00 делал то-то 100 шт 1,25 мин 1,0 мин 100 мин
Иванов Время на работе 03:00*60 = 120 мин - добавл строки с расчетом
Иванов Оплачиваемое время = 90 мин - добавл строки с расчетом
и так по кажд фамилии
Фамилии постоянны и практически не меняются
Щас мне приходится на кажд работника добавлять эти строки с обсчетом и вручную вносить их в табель
хотелось автоматизировать данный процесс
расчеты длительности и расчет времени оплачив у меня построчно автоматизирован , осталась токо проблема добавления строк после фамилии и обсчета времени присутсвия и оплаты по каждому рабочему
Далее минуты переносятся в отд файл и напртив кажд фамилии стоит
присутствие на работе и оплачиваемое время
вот.
мой icq 212-749-159
Спасибо.
Номер последней заполненной строки в таблице Excel обычно используется в коде VBA для определения следующей за ней первой пустой строки для добавления новой записи. А также для задания интервала для поиска и обработки информации с помощью цикла For… Next (указание границ обрабатываемого диапазона).
Переменную, которой присваивается номер последней строки, следует объявлять как Long или Variant, например: Dim PosStr As Long . В современных версиях Excel количество строк на рабочем листе превышает максимальное значение типа данных Integer.
Таблица в верхнем левом углу
В первую очередь рассмотрим все доступные варианты поиска номера последней заполненной строки для таблиц, расположенных в верхнем левом углу рабочего листа. Такие таблицы обычно представляют собой простые базы данных в Excel, или, как их еще называют, наборы записей.
Пример таблицы с набором данных в Excel
Вариант 1
Основная формула для поиска последней строки в такой таблице, не требующая соблюдения каких-либо условий:
PosStr = Cells(1, 1).CurrentRegion.Rows.Count
Вариант 3
В первом столбце таблицы не должно быть пропусков, а также в таблице должно быть не менее двух заполненных строк, включая строку заголовков:
PosStr = Cells(1, 1).End(xlDown).Row
Вариант 4
В первой колонке рабочего листа внутри таблицы не должно быть пропусков, а ниже таблицы в первой колонке не должно быть других заполненных ячеек:
PosStr = WorksheetFunction.CountA(Range("A:A"))
Вариант 5
Ниже таблицы не должно быть никаких записей:
PosStr = Cells.SpecialCells(xlLastCell).Row
Последняя строка любой таблицы
Последнюю заполненную строку для любой таблицы будем искать, отталкиваясь от ее верхней левой ячейки: Cells(a, b) .
Вариант 1
Основная формула для поиска последней строки в любой таблице, не требующая соблюдения каких-либо условий:
PosStr = Cells(a, b).CurrentRegion.Cells(Cells(a, b).CurrentRegion.Cells.Count).Row
Вариант 2
Дополнительная формула с условием, что в первом столбце таблицы нет пустых ячеек:
PosStr = Cells(a, b).End(xlDown).Row
Если у вас на рабочем листе Excel есть записи вне таблиц, следите за тем, чтобы таблицы были окружены пустыми ячейками или пустыми ячейками и границами листа. Тогда не будет случайно внесенных заметок, примыкающих к таблицам, которые могут отрицательно повлиять на точность вычисления номера последней строки из кода VBA.
Добрый день!
Не знаю функций и методов VBA, подскажите пожалуйста.
Как мне определить номер строки по найденной ячейке в файле Excel.
Ситуация такая:
я нашел ячейку, а теперь мне надо удалить всю строку полностью этой ячейки.
ЗЫ: использую VBA через 1С.
В любом случае, спасибо большое за совет, не взирая на результат!
В Excel поставленная задача может быть решена так :
Dim iCell As Range, iRow As Long
Set iCell = Worksheets(1).UsedRange.Find( _
What:="Образец_для_поиска", LookIn:=xlValues, LookAt:=xlWhole)
If Not iCell Is Nothing Then
iRow = iCell.Row 'Номер строки,
'который, на самом деле, нам не нужен, ибо :
iCell.EntireRow.Delete
Else
MsgBox "Увы, ничего не найдено", , ""
End If
В 1С Вы также можете использовать свойства EntireRow и Row
Да это так, но вот в 1С отладчик не знает, что такое Selection или Row!
Если писать
ExcelЛист.Rows(ПерваяЯчейка.Selection().Row()).Delete();
то ошибка такая
Метод объекта не обнаружен (Selection)
Если писать
ExcelЛист.Rows(ПерваяЯчейка.Selection.Row()).Delete();
то ошибка такая
Поле объекта не обнаружено (Selection)
[quote=Андре]я нашел ячейку . [/quote]
Если исходить из Ваших слов, то поиск ячейки уже реализован, однако, второй пост содержит лишь набор ключевых слов, использование которых, в представленном виде, не имеет особого смысла . при этом сам поиск и находка изволят отсутствовать.
Что касается об'екта Selection, то если предположить, что в момент выполнения инструкций выделен действительно диапазон (хотя это может быть и не так), то удаление всех строк этого диапазона, по идее, должно выглядить следующим образом (определение необходимости наличия или отсутствия скобок ложится на Вас) :
Поиск был выполнен с помощью этого:
ПерваяЯчейка = ExcelЛист.Cells.Find("Привет");
с помощью такой строки я её смогу удалить:
ExcelЛист.Rows(1).Delete();
но в параметрах Rows(1) - это номер строки.
Поэтому я и спрашиваю, как мне найти этот самый пресловутый номерок с помощью найденной переменной ПерваяЯчейка!
Вот и все!
"Удивительное" открытие свойства Row, о возможности использовании которого, было сказано ещё в моём первом посте, равно как и о свойстве EntireRow
P.S. Интересно, а что произойдёт, если в рабочем листе ExcelЛист не будет найдено ни одной ячейки, содержащей "Привет" .
Читайте также: