Формула Excel для создания двух записей из одной ссылки

Question

У меня все данные в Excel в столбце A, и я хочу заполнить столбец B с помощью формулы. Смотрите прикрепленное изображение:

Если я использую «&_x1» и «&_x2» с ячейкой ссылки, это работает, но ограничение в этом случае заключается в том, что я не могу перетащить ее вниз, чтобы применить ту же формулу в следующей ячейке в столбце B. Мне приходится вручную настраивать ячейку ссылки, переходя к каждой строке в столбце B.

Есть ли другой подход, который я могу использовать? Моя цель заключается в том, что после добавления формулы в Row1-ColumnB, если я потяну угол вниз, он должен заполнить все строки столбца B. Мне не нужно корректировать формулу в какой-либо другой ячейке в столбце B. Окончательная таблица должна выглядеть точно так же, как вы видите на прикрепленном рисунке.

Answer 1

Следующая формула создаст нужный вам столбец.

Из вашего описания ясно, что вы заполняете формальную таблицу, поэтому я сделал это способом, не использующим SPILLфункциональность, поскольку в таблице это не сработает.

=INDIRECT("A" & ROUNDUP(ROW()/2, 0)) & "_x" & IF(MOD(ROW(),2)=1, 1, 2)

Адресация исходной ячейки выполняется с помощью INDIRECT()строки, которую вы создаете из буквы столбца и формулы. Формула берет номер строки и делит его на 2, затем округляет ВВЕРХ. Вы делите на 2, чтобы получить пары для каждого ввода. Если вам нужно пять элементов на набор, вы делите на 5 и т. д.

Значение "1" или "2", необходимое в "_x1" и "_x2", получается с помощью другой арифметической операции, на этот раз MOD()с использованием 2 в качестве делителя и, таким образом, получением либо 1 (нечетные строки), либо 0 (четные строки). Тестирует, IF()для чего и дает правильные 1 или 2 для построения этой части строки.

(Если бы вы не использовали таблицу и хотели бы получить SPILLфункциональность, вы могли бы сделать то же самое, используя SEQUENCE()вместо нее ROW()для получения чисел, которые нужно разделить, или, что сложнее, вы могли бы использовать ее для непосредственной генерации значений строк.)

Answer 2

Вы можете попробовать это:

• Формула массива (CSE) в ячейке B42:

  {=INDEX($A$42:$A$45, MATCH(FALSE, COUNTIF($B$41:B41, $A$42:$A$45)=2, 0))}

• Закончите формулу сCtrl+Shift+Enter, и заполните до нужного уровня.

• 2помогает формуле в ячейке B42повторять значения из A42:A45и является редактируемой.

• Формула в ячейке C42: =IF(COUNTIF(B$42:$B42,$B42)=1,B42&"_x1",IF(COUNTIF(B$42:$B42,$B42)=2,B42&"_x2",""))

• Или вы можете использовать это также: =IF(COUNTIF(B$42:$B42,$B42)=1,B42&"_x1",B42&"_x2")

Примечание.

• При необходимости измените ссылки на ячейки в формуле.
• Для удобства вы можете скрыть колонку «Помощь».