Новости:

Прикрепить к сообщению можно только файлы xls, gif, jpg, rar, zip,7z, bas, frm, cls, doc размером до 150 Кб.

Главное меню

Как получить адрес ячейки с известным значением?

Автор Олег*, 08.10.2012, 01:04

« назад - далее »

Олег*

Вот здесь шла речь о большой-пребольшой таблице:
https://forum.msexcel.ru/microsoft_excel/kak_vizualizirovat_v_eksele_ochen_bolshuyu_tablitsu_s_dvumya_vhodami-t8662.0.html

Таблица построена, требуется ее проанализировать. Максимальное и минимальное значение нашел без особого труда. Просто вот так:

1. На листе "Анализ" (на котором расположена эта таблица) установил именованный диапазон ПолеЗначенийТаблицыАнализа:
=СМЕЩ(Анализ!$B$2;0;0;СЧЁТЗ(Анализ!$A:$A);СЧЁТЗ(Анализ!$1:$1))

2. Ну а дальше очень легко:
минимум = МИН(ПолеЗначенийТаблицыАнализа)
максимум = МАКС(ПолеЗначенийТаблицыАнализа)


А теперь вот возникла задачка посложнее. Хотя, на самом деле, сначала я был абсолютно уверен, что легко найдется какая-нибудь подходящая функция под это дело, но не тут-то было.
Задача состоит в том, чтобы найти адреса того минимального и максимального значения, которые уже вычислены, т.е. требуется выяснить в каком столбце и в какой строке они (максимум и минимум) расположены, определить номер строки и столбца, на пересечении которых они находятся.
Честно говоря, я сильно надеялся на функцию ПОИСКПОЗ, но оказалось, что это совсем не то, что нужно или я просто пользоваться ей не умею.
Одним словом, обращаюсь за очередным советом к уважаемому Клубу Знатоков Экселя!

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

Poltava

Ну формула пока в голову не пришла но
Цитироватьросто буду долго-долго елозить циклами по всей таблице
Это по моему не лучший вариант особенно с большим объемом данных как у вас он будет медленно работать я бы воспользовался функцией .Find
     
Не пытайтесь спорить с дебилом. Иначе вы опуститесь до его уровня. Где он задавит вас своим опытом.

ShAM


_Boroda_

#3
=АДРЕС(МИН(ЕСЛИ(Лист1!$B$2:$KR$534=B1;СТРОКА(Лист1!$A$2:$A$534)));МИН(ЕСЛИ(Лист1!$B$2:$KR$534=B1;СТОЛБЕЦ(Лист1!$B$1:$KR$1))))формула массива
корректно работает, если число мин и макс единственное в таблице
если не единственные, то что-то типа
=АДРЕС(МИН(ЕСЛИ(Лист1!$B$2:$KR$534=B2;СТРОКА(Лист1!$A$2:$A$534)));МИН(ЕСЛИ((Лист1!$B$2:$KR$534=B2)*(СТРОКА(Лист1!$B$2:$KR$534)=МИН(ЕСЛИ(Лист1!$B$2:$KR$534=B2;СТРОКА(Лист1!$A$2:$A$534))));СТОЛБЕЦ(Лист1!$B$1:$KR$1))))
мин и макс в ячейках В1 и В2

Цитата: ShAM от 08.10.2012, 03:00
Может, поможет.
Не покатит.
Для проверки поставь -77 в ячейки D2 и А3
Скажи мне, кудесник, любимец ба'гов...



Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

ZORRO2005

Ну и мой вариант если числа мин и макс не единственные:
Яндекс-деньги: 410011658492153

Serge 007

И ещё вариант, одной формулой (возвращает список адресов в формате А1):
=АДРЕС(НАИМЕНЬШИЙ(ЕСЛИ((П=KT$3);СТРОКА(П));СТРОКА(A1));ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ((П=KT$3);СТОЛБЕЦ(П)+СТРОКА(П)*ЧСТРОК(П));СТРОКА(A1));ЧСТРОК(П));4)
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

Олег*

Большое спасибо всем, кто откликнулся!
Материал для меня пока сложный, буду разбираться, что к чему. Самое главное, что оно работает!
По-правде сказать, я не ожидал, что в Экселе нет какой-нибудь готовой дежурной функции под эту задачу. Ведь это же вполне естественно для пользователя после того, как он нашел максимум и минимум диапазона, захотеть отыскать эти найденные значения внутри этого диапазона. Конечно, задачу сильно осложняет то, что максимум (и минимум тоже) может быть не один, но все равно могли бы разработчики что-нибудь сообразить на эту тему, чтоб народ с бубном не плясал :)
Муж это единственный зарегенный юзер, а все остальные это хакеры :)

Serge 007

Цитата: Олег* от 09.10.2012, 21:18
...могли бы разработчики что-нибудь сообразить на эту тему, чтоб народ с бубном не плясал :)
А разработчики и так всё сделали :)
Мин/Макс находятся простыми функциями, а дальше Ctrl+F
Зачем нужен именно адрес ячеек, да ещё формулами - совершенно непонятно...
Что потом с этими адресами делать-то?
Так что "танцы с бубном" нужны только для разовых, индивидуальных задач, для почти всего остального есть весь арсенал функций Excel
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

Олег*

#8
Цитата: Serge 007 от 09.10.2012, 21:38
Мин/Макс находятся простыми функциями, а дальше Ctrl+F
Зачем нужен именно адрес ячеек, да ещё формулами - совершенно непонятно...
Что потом с этими адресами делать-то?

Так ведь (в условиях моей "большой таблицы") это не обычные адреса, это те самые искомые значения двух независимых переменных X и Y, которые располагаются в заголовках строк и столбцов и соответствуют наибольшему и наименьшему значению функции.
Собственно говоря, вся катавасия и затевалась, для того, чтобы быстро находить именно эти значения для любой таблицы, которая, разумеется, всегда будет изменяться в зависимости от новых исходных данных. Ну вот и хочется автоматизировать этот дело, а не нажимать каждый раз на кнопку "Поиск" :)
Муж это единственный зарегенный юзер, а все остальные это хакеры :)

Serge 007

Цитата: Олег* от 09.10.2012, 22:22
...это не обычные адреса, это те самые искомые значения двух независимых переменных X и Y, которые располагаются в заголовках строк и столбцов и соответствуют наибольшему и наименьшему значению функции.
Это и есть обычные адреса :)

Цитата: Олег* от 09.10.2012, 22:22
...быстро находить именно эти значения для любой таблицы...
Найдя их Вы вручную будете искать соответствия. Какой в этом смысл?

Цитата: Олег* от 09.10.2012, 22:22
...нажимать каждый раз на кнопку "Поиск" :)
Но так-то удобнее ;)

ЗЫ Олег, не в обиду, но Вы как-то стали в последнее время А1+В1 через формулы массива и макросы вычислять...
Строго говоря - это не моё дело, просто интересно, почему Вы не слышите тех, кто это уже проходил и не задумываетесь о продуктивности решений, которые Вам предлагают?..
Потом Вы ведь начинаете говорить, что
Цитата: Олег* от 08.10.2012, 01:04
...буду долго-долго елозить циклами по всей таблице...
и
Цитата: Олег* от 05.10.2012, 00:54
... комп у меня стал тормозить довольно прилично...
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

Олег*

Цитата: Serge 007 от 09.10.2012, 22:44
Цитата: Олег* от 09.10.2012, 22:22
...это не обычные адреса, это те самые искомые значения двух независимых переменных X и Y, которые располагаются в заголовках строк и столбцов и соответствуют наибольшему и наименьшему значению функции.
Это и есть обычные адреса :)

Не важно, как это называть, важно то, что они-то нам и нужны! :) К "обычным" адресам прибавить сотню, вот и будут наши искомые X и Y.

Цитата: Serge 007 от 09.10.2012, 22:44
Цитата: Олег* от 09.10.2012, 22:22
...быстро находить именно эти значения для любой таблицы...
Найдя их Вы вручную будете искать соответствия. Какой в этом смысл?

А вот и нетушки! :) Ручной труд будет сведен к минимуму! :) Дальше их будет продолжение макроса "подхватывать" и делать с ними что-нибудь общественнополезное :)

Цитата: Serge 007 от 09.10.2012, 22:44
Цитата: Олег* от 09.10.2012, 22:22
...нажимать каждый раз на кнопку "Поиск" :)
Но так-то удобнее ;)

Рука устанет :)

Цитата: Serge 007 от 09.10.2012, 22:44
ЗЫ Олег, не в обиду, но Вы как-то стали в последнее время А1+В1 через формулы массива и макросы вычислять...
Строго говоря - это не моё дело, просто интересно, почему Вы не слышите тех, кто это уже проходил и не задумываетесь о продуктивности решений, которые Вам предлагают?..

Как говорится, спасибо за вопрос. Давно хотел поговорить на эту тему, но как-то все не получалось. Сейчас попробую объяснить. Итак, почему я все хочу автоматизировать? Все дело в том, что я... как бы это поточнее сказать... увлекающийся человек. То есть сегодня мне интересно одно, а завтра совсем другое, что-то совсем "из другой оперы". Теперь предположим, что я смастерил книгу Экселя, которая "заточена" под определенную сложную задачу, а потом надолго переключился (по жизни) на что-то совсем другое, и эта книга пылилась очень-очень долгое время в файловой системе моего компьютера. Где гарантия, что когда мне по жизни понадобиться решать те же самые задачи, я смогу быстро сообразить, что к чему в этой моей "недоавтоматизированной" :) книге? Ну вот именно поэтому-то мне и хочется (по возможности) автоматизировать все, что только можно. Это я сейчас программист и "в теме", а через пару-тройку лет не исключено, что я буду по отношению к этой своей же собственной программе обычный пользователь, и мне хочется, чтобы она была ко мне максимально дружелюбна, т.е. знала, что "пользователь" и "дурак" - это по сути одно и то же :) Ему не хочется ни во что вникать. Ему нужна одна большая кнопка "Жми!", после нажатия на которую выскакивает сообщение "Жди!" :)

Цитата: Serge 007 от 09.10.2012, 22:44
Потом Вы ведь начинаете говорить, что
Цитата: Олег* от 08.10.2012, 01:04
...буду долго-долго елозить циклами по всей таблице...
и
Цитата: Олег* от 05.10.2012, 00:54
... комп у меня стал тормозить довольно прилично...

Про циклы я написал, когда просил подсказать формулу, чтобы по возможности обойтись без них.
А комп, действительно, старый и медленный (одноядерный Celeron 2,6 GHz с памятью 1 ГБ). Новый пока не покупаю, хочу дождаться планшета от Майкрософт с Windows 8.






Муж это единственный зарегенный юзер, а все остальные это хакеры :)