Как в Excel получить ссылку на диапазон одинаковых чисел, расположенных в столбце подряд?

Cardano, 322 просмотра

На листе Excel есть столбик с целыми шестизначными числами, которые идут массивами, т.е.:

123432
123432
123432
123432
123432
567582
567582
567582
567582
567582
567582
567582
и т.д.

Числа по возрастанию не упорядочены.

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

Люди умные, помогите, пожалуйста...

Примечание:
Народ! Спасибо вам большое, но, как оказалось, если долго медитировать в Excel, можно добиться многого... Не знаю как меня осенило, видимо, озарение свыше, не иначе!

Суть в том, что нужно просто ПОСЧИТАТЬ количество ячеек, в которых содержится данное число — функцией СЧЁТЕСЛИ. В результате получим длину диапазона — как раз то, что нужно! То есть начало диапазона (номер строки) находим с помощью ПОИСКПОЗ, а конец — просто сложением «начала» и «длины».

Тем, не менее, спасибо ВАМ ВСЕМ огромное за мозговые усилия! Лучшим ответом выбираю формулу Алексея Матевосова — спасибо, про такой способ я не знал! Формула работает и принимается на вооружение.

Ответы:

Младшенький

Приветствую! :) Спасибо за интересную задачку.
Значицца, так:
Столбец с значениями должен начинаться с 1 строки
Формулу вводить как формулу массива - т.е. одновременным нажатием Ctrl+Shift+Enter
Формула:
=СЦЕПИТЬ(АДРЕС(РАНГ(D1;A1:A10);1;4;1);":";АДРЕС(НАИБОЛЬШИЙ((--(A1:A10=D1))*СТРОКА(A1:A10);1);1;4;1))
А1:А10 - массив с значениями
D1 - ячейка, куда вводим число, диапазон занимаемый которым (о как загнул!) надо найти. :)
Основное условие, чтобы числа шли ПОДРЯД и НЕ ПОВТОРЯЛИСЬ.

Алексей Матевосов

Не формула , а монстр получился.
Допустим Числа в столбце А начиная с А1
В ячейке В1 число, диапазон которого надо определить.
В С1 формула
=ПСТР(ЯЧЕЙКА("адрес";A1:A100);2;ПОИСК("$";ЯЧЕЙКА("адрес";A1:A100);2)-2)&ПОИСКПОЗ(1;ИНДЕКС(--(A1:A100=B1);0);0)&":"&ПСТР(ЯЧЕЙКА("адрес";A1:A100);2;ПОИСК("$";ЯЧЕЙКА("адрес";A1:A100);2)-2)&ПОИСКПОЗ(1;ИНДЕКС(--(A1:A100=B1);0);0)+ПОИСКПОЗ(1;ИНДЕКС(--(ДВССЫЛ("A"&ПОИСКПОЗ(1;ИНДЕКС(--(A1:A100=B1);0);0)&":A100")<>B1);0);0)-2

DV-68

Можно не просто ссылку на диапазон, а сразу выделять диапазон.
Данные С1:С12, искомое В1. Тогда:
=ГИПЕРССЫЛКА("#"&АДРЕС(ПОИСКПОЗ(B1;C1:C12;);3)&":"&АДРЕС(СЧЁТЕСЛИ(C1:C12;B1)-1+ПОИСКПОЗ(B1;C1:C12;);3);B1)

12 лет назад

RPI.su - самая большая русскоязычная база вопросов и ответов. Наш проект был реализован как продолжение популярного сервиса otvety.google.ru, который был закрыт и удален 30 апреля 2015 года. Мы решили воскресить полезный сервис Ответы Гугл, чтобы любой человек смог публично узнать ответ на свой вопрос у интернет сообщества.

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

Чтобы связаться с нами по любому вопросу О САЙТЕ (реклама, сотрудничество, отзыв о сервисе), пишите на почту [email protected]. Только все общие вопросы размещайте на сайте, на них ответ по почте не предоставляется.