Определение номера строки [РЕШЕНО]

[Xy]

Добрые люди, подскажите пожалуйста. Как определить номер строки по искомому значению в определённом столбце. Например, есть значение в ячейке "H5". По столбцу "B" необходимо найти ячейку "B_i" с тем же значением что и "H5", и определить номер строки ячейки "B_i". Как записать формулу такой процедуры?

[VlhOwn]

=MATCH(H5;B1:B1000)
Заметьте, что MATCH дает смещение найденного значения относительно начала диапазона поиска, так что если диапазон начинается не с первой ячейки, придется делать соответствующую поправку.

[Xy]

Ув. VlhOwn, спасибо за ответ. В связи с этим у меня имеется продолжение вопроса. Определив номер строки, как прописать значение любой ячейки по этой строке? Например, с помощью команды MATCH, мы определили искомую строку. Как присвоить произвольной ячейке, допустим J1, значение ячейки со следующими координатами: "строка" - MATCH(H5;B1:B1000), "столбец" J-6, то есть на 6 столбцов левее столбца J. Возможно ли решение такой задачи?

[VlhOwn]

J1: =OFFSET(J1;MATCH(H5;B1:B1000)-1;-6)

Раздел справки: Функции электронных таблиц - почитайте, там много интересного.

[JohnSUN]

Если не хочется комбинировать в рамках одной формулы несколько функций (OFFSET(MATCH()), INDEX(MATCH())) и т.п.), можно воспользоваться функцией VLOOKUP()

[Xy]

J1: =OFFSET(J1;MATCH(H5;B1:B1000)-1;-6)

Не очень понятно, почему указан сдвиг на строку выше относительно строки определённой командой MATCH. Например, при записи =OFFSET(J6;MATCH(H10;B1:B1000)-1;-6) в случае, если значение в ячейке H10 указывает на строку 1, в ячейку J6 возвращается значение ячейки D6, а не D1. По поводу VLOOKUP. Честно говоря, путаюсь в синтаксисе формул, поскольку являюсь новичком. Не могли бы Вы указать пример написания формулы для вышеописанного случая.

[JohnSUN]

Для вышеописанного случая, наверное, не смогу - все диапазоны и ячейки со значениями указал VlhOwn, а не Вы. Поэтому не уверен, что смогу написать варианты формул, которые в Вашей таблице сразу же и заработают.
Ознакомьтесь с приложенным файлом. Возможно, он немного поможет в освоении функций.

[вложение удалено Администратором]

[VlhOwn]

Как присвоить произвольной ячейке, допустим J1, значение ячейки со следующими координатами: "строка" - MATCH(H5;B1:B1000), "столбец" J-6, то есть на 6 столбцов левее столбца J

OFFSET(J1;MATCH(H5;B1:B1000)-1;-6)

OFFSET(J1 ...) задает смещение целевой ячейки относительно 1-й строки и j-того столбца (т.е. ячейки J1) на MATCH(H5;B1:B1000)-1 строк вниз и на (-6) столбцов вправо (т.е. на 6 столбцов влево).

Если MATCH(H5;B1:B1000) дает значение 1, то это означает, что искомое значение находится в первой строке диапазона B1:B1000, что смещения относительно начала диапазона нет, смещаться по строкам не нужно. Поэтому вычитаем 1, чтобы задать нулевое смещение.

=OFFSET(J6;MATCH(H10;B1:B1000)-1;-6) задает смещение относительно J6, а не J1, поэтому Вы и получаете D6. В качестве опорной точки следует использовать верхнюю левую ячейку диапазона. В Вашем случае - J1.

С VLOOKUP искомая формула будет выглядеть так:
=VLOOKUP(H5,B1:D1000;3)
Здесь 3 - номер столбца D в диапазоне ячеек B1:D1000.

[Xy]

Хмм, похоже я разобрался. В общем виде формула получила такой вид. OFFSET(J_i;MATCH(N_i+4;B1:B1000)-ROW(J_i);-6). Однако,я столкнулся с проблемой другого рода. При копировании номера столбцов сдвигаются (как впрочем и нужно), и сдвигается также область массива по столбцу B. Это нежелательно, и приходится постоянно "ручками" править формулу. Нет ли иной формы записи, как к примеру в Excel, по столбцу (B:B)??

[Helen]

B$1:B$1000
$B$1:$B$1000

[Xy]

Спасибо Вам, ув. Helen. Возник ещё один вопрос, возможно кто-нибудь подскажет направление, где искать. Существует ли какая-либо команда, по которой можно ориентироваться на ту ячейку произвольной строки, в которой помещена крайняя запись. То есть, если строка заполняется слева направо, есть необходимость ориентироваться на крайнюю запись в этой строке. Возможно ли это с помощью команд Calc?

[VlhOwn]

OFFSET(Ji;MATCH(Ni+4;B1:B1000)-ROW(Ji);-6)

Ваша формула эквивалентна приводившейся ранее OFFSET(J1;MATCH(Ni+4;B1:B1000)-1;-6), в которой нет лишнего вычисления ROW.
_________________________________
На нашем форуме мы стараемся придерживаться принципа "одна тема - один вопрос", в силу которого в теме рассматривается только один вопрос. Это существенно облегчает в дальнейшем поиск информации по форуму. Если в ходе обсуждения у Вас возникли дополнительные вопросы, не связанные непосредственно с вопросом темы, заведите новые темы и в них сформулируйте возникшие вопросы.
У нас также принято, что топикстартер, получив удовлетворяющий его ответ на вопрос темы, добавляет в название темы слово "РЕШЕНО".

[Xy]

Хорошо, не подскажите, как редактировать название темы? В принципе, проблема, озвученная в первом сообщении темы успешно решена.

[Helen]

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