Значение из объединённой ячейки при ссылке на не верхнюю-левую ячейку.

[tolikt]

Ячейки A1:B3 объединены. В этой объединённой ячейке находится значение 1.
Если в C4 ввести формулу "=A1" то получим результат 1. Если формулу в C4 скопировать на область [noparse]C4 6[/noparse], то во всех ячейках, кроме самой C4, результат будет 0. Это понятно, т.к. формулы скопировались со смещением по ссылке. А хотелось бы получать значение из верхней-левой, т.е. 1.
Как обычными формулами без VBA получить значение объединённой ячейки, если ссылка не на левую-верхнюю ячейку из этой объединённой?

Абсолютная ссылка ($A$1) тут не подойдёт, ибо формулу надо скопировать на несколько объединённых областей, где значения верхней-левой ячеек разные.

Через VBA это решается просто.

Код: Выделить всё

Function V(R As Range)
V=R.MergeArea
End Function

И в область [noparse]C4 6[/noparse] ввести формулу "=V(A1)"
Но надо без макросов.

В функции ПОЛУЧИТЬ.ЯЧЕЙКУ типа информации о вхождении или нет ячейки в объединённую область тоже не нашёл.

[ZORRO2005]

Может так подойдёт:
=A$1

[VictorM]

tolikt,

Абсолютная ссылка ($A$1) тут не подойдёт

не совсем понятно, а почему не подойдет
если следовать Вашим условиям, то формула =$A$1 прекрасно "размножается" на диапазон C4 : D6 "растягиванием" и во всех ячейках этого диапазона имеем значение 1. (специально проверил, а вдруг ).
и что такое

несколько объединённых областей, где значения верхней-левой ячеек разные

мож я где чё не понял?

[tolikt]

Нет, никакая абсолютная ссылка не пойдёт. Ибо нужно одинаковую формулу скопировать на большой диапазон, в каждой ячейке диапазона ссылки на РАЗНЫЕ объединёные ячейки в которых, соответственно, РАЗНЫЕ данные. И размер (количество входящих одинарных ячеек) каждой объединённой ячейки заранее неизвестно.

Для пояснения см. файл.

В столбце A - ряд объединённых ячеек с данными
В столбце B - вид формулы
В столбце C - результат формулы
В столбце D - результат функции VBA - который и есть правильный.

Т.е. надо получить результат, как в D, но без VBA.

[VictorM]

tolikt, ну что же. С примером оно конечно понятнее будет.
Тогда действительно вопрос, как получить значение ячейки которой нет?
Я, конечно, не знаю полностью условий Вашей задачи, но может присвоить объединенным ячейкам имена?

[tolikt]

Ну, ячейка-то как бы есть... Значение в ней - пусто.

Идея получения нужного значения лежит на поверхности:
Если значение в ячейке пусто, то брать значение из ячейки над пустой. Если и она пустая, то выше и так далее вверх. В VBA данный цикл можно организовать легко. Впрочем, там есть ещё более простая функция MergeArea. А как всё это организовать без VBA, а только с помощью своих формул.

Пробовал поиграться с ПОИСКПОЗ, ЧСТРОК и другими, но не получается.
В функции ПОЛУЧИТЬ.ЯЧЕЙКУ нет такого типа информации о ячейке, как объединена она или нет. Хотя она тоже не совсем функция листа.

Составная функция (для ячейки C5) типа =ЕСЛИ(A5<>"";A5;C4) тоже не прокатит, т. к. на самом деле формула сложная и в ячейке C4 уже будет какое-то вычисленное значение, а не нужное исходное, находящееся в объединённой ячейке, в которую входит ячейка A5.

[VictorM]

Да, с VBA там конечно проще получается. Я это тоже попробовал. А вот без оного...
Надо чёт искать...
А с именоваными ячейками тоже не катит?

[ZORRO2005]

Попробуйте так:

[VictorM]

ZORRO2005, класс!
Действительно, не именованные ячейки , а массивы!
Посмотрим, что скажет автор темы.

[tolikt]

Да, ZORRO2005 молодец!

Мелкие нюансы, конечно, есть: надо не забыть ввести формулу массива и вообще итоговая формула получается довольно громоздкой. Но это мелочи.
Главное, почему именно надо было данные из объединённой ячейки. В некоторых ячейках данных нет (т.е. даже не 0, а пусто), но они не входят в область объединения с верхней объединённой ячейкой. И данная формула пропускает пустые необъединённые ячейки. Т.е. полного аналога MergeArea не получается и, похоже, без VBA не получится.
Но всё равно спасибо. Данное решение, скорее всего, придётся использовать, немного подкорректировав постановку задачи.