Получение значений из объединенных столбцов Excel

Чтобы получить доступ к меткам «Col1» и «Col2», вы можете использовать следующее:

=ИНДЕКС($1:$1,1,СТОЛБЦ()-MOD(СТОЛБЦ()-1,2))

Примечание. Это предполагает, что вы группируете одинаковое количество ячеек. Если бы это были три ячейки, вы бы просто изменили последнее число в формуле на 3 и так далее.

Изменить. Вот как это работает:

INDEX($1:$1,1, x) возвращает значение ячейки в строке 1, столбце x. Если ваша таблица на самом деле не находится в верхнем левом углу рабочего листа, вы можете изменить это на фактический диапазон, который включает все ваши объединенные метки. В этом случае это будет: ИНДЕКС($A$1:$D$1,1, x)

COLUMN() возвращает номер столбца текущей ячейки (1 в столбце A, 2 в столбце B и т. д.)

MOD(COLUMN()-1,x) возвращает смещение от текущего столбца до столбца, содержащего соответствующую метку.

Вот еще одно решение, которое также может работать, когда объединенные ячейки имеют разную ширину, позвольте мне проиллюстрировать это на примере:

1. Откройте новый Excel, объедините B1, C1, D1
2. Введите Col1 в объединенную ячейку.
3. В B2 введите формулу =B1, а в C2 =C1 в D2 =D1
4. Вы должны увидеть B2 как Col1, а C2, D2 как 0.
5. В B3 введите формулу =A3, скопируйте ее
6. Щелкните правой кнопкой мыши объединенную ячейку B1:D1, выберите «Специальная вставка -> формулы».
7. Вы должны увидеть, что объединенная ячейка имеет значение 0.
8. Введите Col1 в объединенную ячейку.
9. Теперь вы должны увидеть, что все B2, C2, D2 являются Col1, то есть теперь вы можете ссылаться на объединенную ячейку так, как вы ожидаете.

Если вы можете объединить несколько ячеек, каждая из которых имеет разную ширину, просто вставьте формулу во все из них за один раз.

Это работает благодаря выбору дизайна Microsoft. Кажется, что когда вы вставляете формулы в объединенные ячейки, каждая базовая ячейка получает формулу (напротив, если вы вводите значение, оно получает только верхнюю левую ячейку). Таким образом, вы можете использовать это в своих интересах и вставить формулу, которая ссылается ячейку рядом с ней, а затем перезапишите верхнюю левую ячейку нужным значением, тогда каждая ячейка, лежащая в основе объединенной ячейки, будет иметь это значение.

Я создал простую функцию в vba, которая решит эту проблему:

Function mergedText(rngMergedCell As Range)

    If rngMergedCell.MergeCells = True Then
        mergedText = rngMergedCell.MergeArea(1, 1)
    Else
        mergedText = rngMergedCell
    End If

End Function

Если ячейка является объединенной ячейкой, функция вернет значение в первом элементе объединенной ячейки — здесь объединенная ячейка хранит свое значение.

Более общий вариант предложения e.James:

={INDEX($A$1:A1, 1, MAX(NOT(ISBLANK($A$1:A1))*COLUMN($A$1:A1)-COLUMN($A$1)+1))}

Это основано на том факте, что объединенные ячейки пусты, за исключением первой (если только вы не находитесь в случае, подобном предложению Мартина).

Примечание. Фигурные скобки нужны для обозначения формулы массива (не вводите их, просто нажмите Alt+Return, чтобы подтвердить формулу в ячейке).

Я понимаю, что опаздываю в эту тему, но я нашел действительно простой ответ на этот вопрос.

Если, например, ваша метка объединена в 4 столбца a1:d1, и если вы ссылаетесь на b1, вы вернете «». Для динамического поиска правильных меток используйте этот fx в вашей новой таблице:

=if(OriginalTable!B1="",ThisTable!A1,OriginalTable!B1)

Я уверен, вы поймете, что это будет захватывать диапазоны в e1:h1 и т. д., когда вы перетаскиваете курсор.

Вот и все. Надеюсь, это поможет кому-то.

В ячейках B1 и D2 нет значений, только в ячейках A1 и C1 что-то есть.

Таким образом, вам просто нужно убедиться, что ваши формулы в столбцах A и B ссылаются на A1 как на искомое значение, и что ваши формулы в столбцах C и D ссылаются на C1 как на искомое значение.

С новой динамической ссылкой теперь есть больше возможностей. Вот универсальная функция, которую я написал, которая будет искать слева от ячейки и возвращать первое значение. Он НЕ оптимизирован, но для меня работает.

=LET(
TargetCell, A1,
TargetRow, ROW(TargetCell),
TargetCol, COLUMN(TargetCell),
RowReference, INDIRECT(TargetRow & ":" & TargetRow),
RowValues, TRANSPOSE(FILTER(RowReference,ISBLANK(RowReference)=FALSE)),
RowValueColumns, MATCH(RowValues, RowReference,0),
ReturnColumn, MAX(FILTER(RowValueColumns,RowValueColumns<=TargetCol)),
Return, INDIRECT(ADDRESS(TargetRow,ReturnColumn)),

Return
)