Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Как вставить адрес ячейки в формулу массива?
 
#1
26.06.2015 18:13:21
Для поиска номера строки первой пустой ячейки в диапазоне, ввожу в ячейку A1 формулу:
=ПОИСКПОЗ(ИСТИНА();(A2:A50="");ЛОЖЬ())
жму Ctrl+Shift+Enter и все работает.

Теперь хочу адреса A2:A50 записать в общем виде (что бы потом в другие диапазоны копировать)
меняю A2 на АДРЕС(СТРОКА()+1;СТОЛБЕЦ()),
а A50 - на АДРЕС(СТРОКА()+49;СТОЛБЕЦ())
получается:
=ПОИСКПОЗ(ИСТИНА();(АДРЕС(СТРОКА()+1;СТОЛБЕЦ()):АДРЕС(СТРОКА()+49;СТОЛБЕЦ())="");ЛОЖЬ())
не работает.

Даже когда явно задаю адрес:
=ПОИСКПОЗ(ИСТИНА();(АДРЕС(2;1):АДРЕС(50;1)="");ЛОЖЬ())
тоже не работает.

Подскажите, плз., в чем ошибка?
Изменено: vadi61 - 26.06.2015 18:46:00
 
 
 
#2
26.06.2015 18:19:44
Адрес возвращает текст, а не ссылку. Для того, чтобы превратить адрес в ссылку, надо ее "обернуть" ДВССЫЛ()
ДВССЫЛ(АДРЕС(...) & ":" & АДРЕС())
Кому решение нужно - тот пример и рисует.
 
 
 
#3
26.06.2015 18:49:27
Цитата
адреса A2:A50 записать в общем виде
Вообще можно без применения АДРЕС:
=ИНДЕКС(A2:H50;5;4)
 
 
 
#4
26.06.2015 19:17:32
ув. Пытливый,
спасибо, сработало!
 
 
 
#5
26.06.2015 19:26:41
Да не за что.
Уважаемый vikttur дал хороший совет, поскольку ДВССЫЛ - это т.н. "летучая" функция и пересчитывается от любого чиха на листе. Эти пересчеты, конечно же, нагружают машину и замедляют работу.
Я вам показал для вашего варианта, а vikttur предлагает принципиально другое решение, более удачное, на мой взгляд.
Кому решение нужно - тот пример и рисует.
 
 
 
#6
26.06.2015 21:27:15
vikttur,
Пытливый,
Я просто не понял как я привяжу формулу написанную ув. vikttur к своей задаче. Там и строка и столбец заданы конкретно, а в АДРЕСе я привязываюсь к строке и столбцу ячейки, в которой находится формула и могу копировать (не растягивать) ее в другие ячейки.
 
 
 
#7
26.06.2015 21:53:35
vadi61, по-моему, Вы переусложняете. Относительная ссылка A2:A50 будет меняться автоматически при копировании или "растягивании" ячейки с формулой.
Кстати, можно короче
Код
=ПОИСКПОЗ(ИСТИНА;A2:A50="";)
 
 
 
#8
26.06.2015 21:54:45
А мне не понятна эта махинация с СТРОКА и СТОЛБЕЦ. Если копировать формулу не из строки формул, а вместе с ячейкой, то и формула при вставке будет ссылаться на сдвинутый диапазон, ссылки то в формуле относительные. Может я что то упустил?
 
 
 
#9
26.06.2015 21:56:47
gling, сегодня мысли приходят одновременно :D
http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=67348&TITLE_SEO=67348-spetsvs...
 
 
 
#10
26.06.2015 22:16:54
Цитата
Казанский написал: одновременно
Почти, писАл долго, а еще дольше искал разницу.
Изменено: gling - 26.06.2015 23:07:21
 
 
 
#11
26.06.2015 22:39:45
ув. Казанский, ув. gling,
Цитата
Казанский написал: при копировании или "растягивании" ячейки с формулой
Цитата
gling написал: Если копировать формулу не из строки формул, а вместе с ячейкой
К сожалению я не могу копировать или растягивать ячейки - собъется форматирование. Многие ячейки имеют или другой фон или другой цвет шрифта. Мне именно нужно в адресной строке скопировать формулу как текст и вставить ее в уже отформатированную (по-другому) ячейку.
 
 
 
#12
26.06.2015 22:50:10
Цитата
vadi61 написал:
К сожалению я не могу копировать или растягивать ячейки - собъется форматирование... Мне именно нужно в адресной строке скопировать формулу как текст и вставить ее в уже отформатированную (по-другому) ячейку
Есть 2 решения:
1. Использовать спец. вставку - Формулы.
2. Включить стиль ссылок R1C1 (Параметры - Формулы). В этом стиле формула с одинаковой относительной ссылкой выглядит одинаково во всех ячейках.
 
 
 
#13
26.06.2015 22:53:42
Цитата
vadi61 написал: в адресной строке скопировать формулу как текст
Копируете ячейку с формулой -- выделяете нужный диапазон -- специальная вставка -- вставить формулы --ОК. В этом случае форматы не собьются.
 
 
 
#14
26.06.2015 23:09:10
vadi61, говорим-воду-льем.
От Вас пример, в котором покажете проблему.
 
 
 
#15
27.06.2015 15:05:34
ув. vikttur,

вот пример. Сам оригинал достаточно большой - это фрагмент.

В файле отображается перечень деталей. К каждой детали (по сути в отдельную мини-табличку) сгруппированы цены продаж. Цены меняются, поэтому эти мини-таблички растут по высоте (добавляются строки). Актуальная цена (нижняя ячейка колонки Е) и маркировка документа, в которой эта цена указана (колонка F) должны отображаться в верхней строке мини-табличек (там, где указаны Арт.-№, и название детали). Все мини-таблички отделены друг от друга пустой строкой. Во время работы все мини-таблички скрыты и виден только перечень деталей. Чтобы автоматически обновлять цену в этом перечне (верхняя строка каждой мини-таблички) я использовал такой механизм. Нахожу первую пустую строку диапазона (конец мини-таблички), подымаюсь на строку выше и адрес полученной ячейки сохраняю как промежуточное значение (колонки A и B во вторых строках мини-табличек. В реальности этих значений не видно, поскольку шрифт и фон этих ячеек одного цвета. Здесь я специально для наглядности сделал шрифт черным). Ну а потом использую эти адреса для отображения нужных значений в верхней строке каждой мини-таблички.

В зависимости от типа детали (уплотнительные элементы, крепежные элементы и пр.) мини-таблички отличаются по цвету.
Детали в список добавляются.

В рамках одного файла я эту задачу решил формулой (см. выше), которую указал в своем вопросе. Но теперь стала задача распространить этот механизм на другие файлы (металлические детали, детали из пластмассы и др.), а там и цвета другие и колонок больше.

Вот я и подумал, если не указывать жестко в формуле строки и колонки, а добиться того, чтобы они вычислялись сами, в зависимости от того в какой ячейке находится формула, то один раз сделав Ctrl+C, можно будет простым многократным Ctrl+V решить задачу. Растяжкой, копированием ячеек или специальной вставкой мне кажется эту задачу решать значительно трудоемче (либо потом цвета восстанавливать прийдется, либо миллион кликов по спец. вставкам).

Вот вроде все.
 
 
 
#16
27.06.2015 15:19:21
До конца не дочитал - много очень :)
=ИНДЕКС(E3:E20;ПОИСКПОЗ(1=1;E3:E20="";)-1)
Из-за того, что приходится искать пустую строку, получается формула массива (как и у Вас в файле).
Лучше в ячейку вписать определенное значение - число, текст (если его не нужно видеть - цвет текста равный цвету заливки). В таком случае формула получается легкой:
=ИНДЕКС(E3:E20;ПОИСКПОЗ("конец";E3:E20;)-1)
Диапазон не закреплять, задать размер не меньше максимальной высоты блока. Естественно, должно соблюдаться условие, что пусто (лучше, как замечено, значение) гарантированно будет в искомом диапазоне.

Еще совет. Плюсик группировки лучше располагать в строке-заголовке, так логичнее и пустая строка скроется.
См. пример
 
 
 
#17
27.06.2015 21:29:45
ув. vikttur,
спасибо, Заменил "свою" формулу на Вашу.
 
 
 
Страницы: 1
Читают тему
Наверх