Как объединить массивы в excel

Объединение нескольких списков в EXCEL (без формул массива))

history 28 мая 2015 г.
    Группы статей

  • Объединение
  • Многостолбцовые диапазоны

Объеденим значения, расположенные в нескольких столбцах, в один столбец.

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

Как видно из рисунка выше, названия городов, относящиеся к разным странам, размещены в соответствующих столбцах.

Вообще, хранить однотипные данные в разных столбцах в MS EXCEL не правильно: возникают сложности с использованием стандартных инструментов ( Сводные таблицы , Автофильтр и др.) и созданием таблиц (например, чтобы каждому городу соотнести его численность придется создавать новый лист или новый список городов).

Более правильно хранить перечень городов вот в такой таблице.

Платой за такое "правильное" размещение является необходимость ввода повторных значений стран.

СОВЕТ: О советах по созданию таблиц с правильной структурой читайте в статье Советы по построению таблиц в MS EXCEL.

Тем не менее, иногда встречаются задачи, в которых такое размещение данных оправдано (см. Многоуровневый связанный список в MS EXCEL ).

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

Аналогичная задача была решена в статье Объединение 3-х и более списков в MS EXCEL . Здесь мы решим ее без формул массива , но нам понадобится создать дополнительный лист с формулами.

Решение

На листе Связи выведем перечень всех стран с листа Города и подсчитаем количество городов у каждой страны = СЧЁТЗ(СМЕЩ(города!$B$2;;ПОИСКПОЗ(A2;города!$B$1:$V$1;0)-1;21)) .

Количество городов позволит нам определелить не только количество раз, которое нужно повторить название страны в итоговой таблице, но и смещение для каждой страны. Т.е. страна США будет иметь смещение 1 (т.е. в таблице идет первой), затем через 12 строк (12 — число городов в США из таблицы на листе Города) идет страна Мексика (ее смещение =13, т.е. начинается с 13-й строки таблицы) и т.д.

Смещение рассчитаем с помощью формулы = ЕСЛИ(B2;СУММ($B$1:B1)+1;-1)

У стран без городов вместо смещения будет выведена ошибка #Н/Д.

Примечание : в функции СУММ() использована относительная и абсолютная ссылка на В1 .

Теперь создадим таблицу, в которой все города будут выведены в один столбец (см. лист таблица в файле примера ).

Сначала сформируем сквозную нумерацию городов в столбце А (см. статью Числовые последовательности в MS EXCEL (порядковые номера 1,2,3. и др.)

Затем, используя связку функций ИНДЕКС() и ПОИСКПОЗ() , выведем названия стран нужное количество раз (количество соответствует количеству городов) с помощью формулы = ИНДЕКС(Связи!$A$2:$A$22;ПОИСКПОЗ(A8;Связи!$C$2:$C$22;1)) .

Примечание: функции ИНДЕКС() и ПОИСКПОЗ() позволяют сформировать аналог " левого ВПР() ". Подробнее — здесь .

Нам также понадобится счетчик городов для каждой страны =СЧЁТЕСЛИ($B$7:B8;B8)

И наконец, формула =СМЕЩ(города!$B$2;C8-1;ПОИСКПОЗ(B8;города!$B$1:$V$1;0)-1) вернет нам перечень городов из листа Города, но расположенный в одном столбце.

Есть ли способ объединить два массива в Excel без VBA?

Я пытаюсь создать формулу, которая возвращает конкатенацию двух массивов разной длины. Мне нужна эта конкатенация для части другой формулы, и я хотел бы избегать "вспомогательных" строк, если это возможно.

См. Ниже, например, данные.

Цель состоит в том, чтобы выход был <10;11;12;13;20;21;22>. Конечно, это легко может быть жестко закодировано в формулу, но эти значения являются динамическими, так что это не вариант.

Я попробовал следующее:

но это явно недействительный синтаксис Excel.

Есть ли решение?

Для чего это стоит, вот решение, которое объединяет два любых вертикальных массива (без ограничения того, что данные должны быть числами).

Вот формула массива: (например, объединение A1:A4 и C7:C9 )

И вот формула массива для объединения двух горизонтальных массивов (например, A1:D1 и C3:E3 )

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

По существу, вам нужно преобразовать <10;11;12;13>в <10;11;12;13;0;0;0>и преобразовать <20;21;22>в <0;0;0;0;20;21;22>. Как только вы получите этот результат, вы можете добавить два массива длиной 7 вместе, чтобы получить желаемый результат.

Итак, как вы добавляете нули в начало или конец массива?

Ответ заключается в том, чтобы использовать умножение матрицы (встроенную функцию MMULT Excel) умным способом.

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

Или в Excel, вы можете ввести это, чтобы получить результат: (Я добавил разрывы строк для повышения удобочитаемости.)

Если вы выделите эту формулу в ячейке и нажмите клавишу F9, вы должны заметить, что она даст вам желаемый результат <10;11;12;13;0;0;0>.

Аналогично, следующая формула даст вам желаемый результат <0;0;0;0;20;21;22>:

Суммируя эти два значения вместе, получим желаемый конечный результат, который равен <10;11;12;13;20;21;22>.

НОТА

На данный момент этого может быть достаточно информации для ваших потребностей/потребностей. Однако для больших массивов может быть слишком громоздким, чтобы жестко закодировать эти матрицы 1 и 0 в формулу. Если это так, продолжайте чтение, в котором рассказывается, как генерировать эти матрицы 1 и 0 автоматически, а не жестко кодировать их.

Как мы автоматически генерируем эти большие матрицы 1 и 0, показанные выше?

Опять же, не объясняя большую часть "почему", потому что я думаю, что обсуждение будет слишком длинным и не по теме, вот формула, которая генерирует первую матрицу из 1 и 0 выше:

Формула для 2-й матрицы 1 и 0 несколько отличается:

ЗАКЛЮЧИТЕЛЬНАЯ ФОРМУЛА

Окончательная формула для конкатенации двух (вертикальных) массивов заключается в следующем: (Несколько разрывов строк добавлены для повышения удобочитаемости)

ЗАКЛЮЧИТЕЛЬНЫЕ ПРИМЕЧАНИЯ/МЫСЛИ

Преимущество использования этой формулы заключается в том, что она позволяет объединять массивы без использования VBA. Недостатком является то, что этот метод для конкатенации массивов работает только с числами, а не с текстом. (Это потому, что MMULT требует номера.)

Да, есть способ присоединиться к массивам в pre-office 2016. Я знаю, что на это ответил ImaginaryHuman выше, но у меня есть другой способ, он возвращает массив, и его немного легче читать (IMHO). Я собираюсь рассказать об эволюции формулы, чтобы вы могли найти тот, который соответствует вашему варианту использования. Я выделил варианты использования жирным шрифтом, чтобы вы могли быстро найти их. Я знаю, что это довольно многословно, но я из тех людей, которым нравится знать, как работает решение, поэтому я постараюсь выразить вам такую же любезность.

Формула опирается на вложенные операторы IF и структуры INDEX / CHOOSE . Он работает с диапазонами, именованными диапазонами и даже столбцами таблицы. Во всех моих примерах показаны четыре диапазона, следовательно, три оператора IF , но это может быть увеличено до (я думаю) 64 диапазонов, если вы заботитесь о таком количестве вложенных операторов IF .

Для этих примеров диапазонами данных являются A3:B6 , A9:B11 , A14:B19 и A22:B32 . Результирующая формула массива помещается в диапазон E3:E26 и заканчивается Ctrl+Shift+Enter чтобы сделать ее формулой массива. Ваши данные могут идти куда угодно — вы не привязаны к этим диапазонам — просто подставьте соответствующие диапазоны.

Если ваши данные находятся в смежных диапазонах:

Как это устроено:

  1. Оператор IF гарантирует, что мы находимся в первом диапазоне, вычитая текущую строку из верхней части выходного диапазона в ячейке E3 и сравнивая ее с количеством ячеек в первом входном диапазоне A3:B6 .
  2. Оператор INDEX выбирает элемент из первого входного диапазона A3:B6 , учитывая смещение строки и столбца, рассчитанное по ячейке E3 .
  3. Если строка не находится в первом диапазоне, она переходит к следующему оператору IF , который повторяет процесс, но сравнивает текущую строку массива с длиной первых двух диапазонов. Процесс повторяется для любых последующих вложенных операторов IF .

Если ваши данные не находятся в смежных диапазонах, вам нужен столбец, показывающий, из какого диапазона были получены исходные данные, или оба:

Как это устроено:

  1. Все принципы для утверждений IF и INDEX остаются такими же, как указано выше.
  2. Добавлен оператор CHOOSE который позволяет вам выбирать несмежные столбцы данных или статический массив с любым идентификатором, который вы хотите для каждого диапазона. В данном случае я пошел с цифрами (1,2,3,4).
  3. Оператор CHOOSE может иметь столько столбцов, сколько вам нужно — просто измените первый аргумент на <1,2,3,4>для четырех столбцов и добавьте четвертый столбец в качестве последнего аргумента. Сделайте то же самое для всех последующих столбцов (например, <1,2,3,4,5>и добавьте пятый столбец в качестве последнего аргумента.

Если у вас есть горизонтальные данные вместо вертикальных, вы можете использовать TRANSPOSE чтобы предыдущий пример работал. Просто TRANSPOSE функцию TRANSPOSE функцию CHOOSE следующим образом:

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

Если этого недостаточно, вы можете сделать больше для удобства чтения, создав несколько именованных значений. Первое, что можно сделать, это определить, с какой строки мы начинаем получать данные из каждой таблицы. Для этого примера я назвал их Table2_UL , Table3_UL и Table4_UL . Их формула кода в менеджере имен выглядит следующим образом:

  1. Table2_UL : =ROWS(Table1)
  2. Table3_UL : =Table2_UL+ROWS(Table2)
  3. Table4_UL : =Table3_UL+ROWS(Table3)

Как видите, каждый из них основан на последнем, поэтому его вывод является динамическим. Теперь у нас есть гораздо более читаемая формула:

Но этого недостаточно для меня. Я хочу избавиться от всех этих неприятных ссылок на ROW() и COLUMN() . Мы можем сделать это, определив еще два значения в менеджере имен, которые отслеживают нашу текущую строку и столбец для нас:

  1. Output_CC : =COLUMN()-COLUMN(Sheet1!E3)+1
  2. Output_CR : =ROW()-ROW(Sheet1!E3)+1

Наконец, у нас есть что-то, что почти читается человеком:

Если мы действительно хотим пройти весь путь до конца, мы можем превратить наши операторы CHOOSE в именованные значения. Просто сделайте следующее для каждой из ваших входных таблиц в менеджере имен, убедившись, что присвойте каждому уникальное имя:

Теперь мы можем легко прочитать формулу:

Опять же, этого недостаточно, потому что вы не можете включить фильтр и отсортировать массивы AZ. Вы получаете ошибку "Вы не можете изменить часть массива". Однако, есть обходной путь! Требуется вспомогательный столбец и дублирование вашего вывода. Его можно скопировать в обычный старый диапазон или в таблицу. Чтобы позволить вам как сортировать, так и фильтровать данные, создайте вспомогательный столбец слева от выходных данных массива, в данном случае начиная с D3 . Если ваши данные не нужно ранжировать (как все текстовые столбцы), создайте статическую нумерацию (1, 2, 3, 4 и т.д.). В этом примере столбец G содержит номер для ранжирования. Если это нужно ранжировать, введите следующую формулу в D3 и перетащите ее вниз:

Измените последний аргумент на 1 если вместо этого вам нужен возрастающий рейтинг. Теперь у вас есть ранжирование не по порядку, если ваши данные ранжированы, или нестабильный массив со статическим числом рядом с ним, если нет. Теперь мы дублируем данные в диапазон или таблицу. В столбце I , начиная с I3 , создайте статическую нумерацию, равную длине набора данных (т.е. 1, 2, 3, 4). Теперь справа от ячейки J3 введите VLOOKUP который ссылается на данные в исходном массиве:

Перетащите формулу вниз, а затем перетащите ее вправо. Теперь вы можете сортировать и фильтровать данные, как если бы это был нормальный диапазон.

Ссылка на основную публикацию