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

Автор Xy, 19 мая 2012, 12:31

0 Пользователи и 1 гость просматривают эту тему.

Xy

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

VlhOwn

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

Xy

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

VlhOwn

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

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

JohnSUN

Если не хочется комбинировать в рамках одной формулы несколько функций (OFFSET(MATCH()), INDEX(MATCH())) и т.п.), можно воспользоваться функцией VLOOKUP()
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Xy

Цитата: VlhOwn от 19 мая 2012, 16:39J1: =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, а не Вы. Поэтому не уверен, что смогу написать варианты формул, которые в Вашей таблице сразу же и заработают.
Ознакомьтесь с приложенным файлом. Возможно, он немного поможет в освоении функций.

[вложение удалено Администратором]
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

VlhOwn

#7
Цитата: Xy от 19 мая 2012, 13:54Как присвоить произвольной ячейке, допустим 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(Ji;MATCH(Ni+4;B1:B1000)-ROW(Ji);-6). Однако,я столкнулся с проблемой другого рода. При копировании номера столбцов сдвигаются (как впрочем и нужно), и сдвигается также область массива по столбцу B. Это нежелательно, и приходится постоянно "ручками" править формулу. Нет ли иной формы записи, как к примеру в Excel, по столбцу (B:B)??

Helen


Xy

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

VlhOwn

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


Xy

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

Helen

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