Excel объединить два массива в один

Обновлено: 04.07.2024

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Если вы выделите эту формулу в ячейке и нажмите клавишу F9, вы должны заметить, что она даст вам желаемый результат .

Аналогично, следующая формула даст вам желаемый результат :

Суммируя эти два значения вместе, получим желаемый конечный результат, который равен .

НОТА

На данный момент этого может быть достаточно информации для ваших потребностей/потребностей. Однако для больших массивов может быть слишком громоздким, чтобы жестко закодировать эти матрицы 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 чтобы сделать ее формулой массива. Ваши данные могут идти куда угодно - вы не привязаны к этим диапазонам - просто подставьте соответствующие диапазоны.

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

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

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

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

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

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

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

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

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

Table2_UL : =ROWS(Table1)
Table3_UL : =Table2_UL+ROWS(Table2)
Table4_UL : =Table3_UL+ROWS(Table3)

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

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

Output_CC : =COLUMN()-COLUMN(Sheet1!E3)+1
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 который ссылается на данные в исходном массиве:

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

Пусть дано 5 списков и все они разной длины (см. Файл примера ).

Задача

Объединим все значения из 5 списков в один. Задача объединения 2-х списков решена в одноименной статье .

Решение1 (Простое)

Объединенный спискок будем строить на основе функции СМЕЩ()

=СМЕЩ(заголовок первого списка;Номер элемента в списке;Номер списка-1)

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

Эта таблица позволит нам сопоставить каждой позиции объединенного списка Номер исходного списка:

выражение СТРОКА()-СТРОКА($H$11) генерирует последовательность 1; 2; 3; 4.
функция ГПР() - горизонтальный аналог ВПР() выбирает по позиции первого элемента каждого списка в объединенном списке номер исходного списка.

Номер списка является смещением по столбцам в формуле на основе СМЕЩ()

Подробности можно посмотреть в файле примера на листе Пример2.

Решение2 (сложное, с формулами массива и именованными формулами)

Сначала создадим именованный диапазон , содержащий значения из всех списков. Для этого:

выделите, диапазон A2:E10 ;
на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
в поле Имя введите: Диапазон_Списков ;
убедитесь, что в поле Диапазон введена формула =пример!$A$2:$E$10
нажмите ОК.

Для вывода всех значений из 5 списков в один столбец будем использовать функцию ИНДЕКС() . Эта функция будет последовательно выводить значения из всех ячеек диапазона Диапазон_Списков на основании их номера столбца и номера строки. Осталось только определить адреса не пустых ячеек.

Создадим массив пар (номер столбца; номер строки) для всех ячеек диапазона. Для этого применим трюк: значения пары будем хранить в виде обычного числа, но формировать его будем по определенному правилу: правая часть числа будет содержать номер строки (для этого выделяется два разряда, т.е. максимальная длина списка может быть 99), а левая часть числа будет содержать номер столбца. Например, число 512 будет означать: 5-й столбец, 12-ая строка. Естественно, при необходимости можно увеличить разрядность для хранения номеров строк (формула из файла примера позволяет столбцам иметь до 9999 строк).

Технически осуществим это так. Сначала определим номер столбца и строки левого верхнего угла нашего Диапазона_Списков . Для этого создайте две именованные формулы =МИН(СТОЛБЕЦ(Диапазон_Списков)) и =МИН(СТРОКА(Диапазон_Списков))

Создайте еще одну именованную формулу Адреса: =ЕСЛИ(ЕПУСТО(Диапазон_Списков);""; --((СТОЛБЕЦ(Диапазон_Списков)-Мин_Столбец+1)&ВЫБОР(ДЛСТР(СТРОКА(Диапазон_Списков)-Мин_Строка+1);"0";"")&СТРОКА(Диапазон_Списков)-Мин_Строка+1))

Эта формула вернет массив адресов из нашего диапазона . Вместо адресов пустых ячеек в массиве содержатся значения Пустой текст (""). Номера столбцов и строк отсчитываются от левой верхней ячейки Диапазона_список .

Заключительный этап. Формируем объединенный список. Запишем в ячейке следующую формулу: =ЕСЛИОШИБКА(ИНДЕКС(Диапазон_Списков; --ПРАВСИМВ(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1));2);--ЛЕВСИМВ(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1)); ДЛСТР(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1)))-2));"")

Функция НАИМЕНЬШИЙ() будет последовательно извлекать все числа, содержащие адреса ячеек. Функция ПРАВСИМВ() будет извлекать из этих чисел номер строки, а функция ЛЕВСИМВ() – номер столбца. Эти две функции возвращают текстовые значения, поэтому применим двойное отрицание (--), чтобы преобразовать текст в число (см. статью Преобразование чисел из текстового формата в числовой (часть 1) ).

Таким образом можно объединить нужное количество столбцов, каждый из которых длиной не более 99 строк.

Для объединения столбцов, каждый из которых длиной не более 9999 строк нужно использовать формулу

Именованную формулу Адреса также придется подкорректировать:

Примечание : при объединении большого количества столбцов с количеством строк >100, расчет формулы может притормаживать.

Чтобы объединить или объединить несколько столбцов в один длинный список, обычно вы можете скопировать и вставить данные столбцов один за другим в указанный столбец. Но здесь я могу представить вам несколько быстрых приемов решения этой задачи в Excel.

Объедините несколько столбцов в один список с кодом VBA

1. Удерживая нажатой Alt + F11 ключи в Excel, и он открывает Microsoft Visual Basic для приложений окно.

2. Нажмите на Вставить > Модуль, и вставьте следующий макрос в Модуль Окно.

Код VBA: объединить или объединить несколько столбцов в один список

3, Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится диалоговое окно с напоминанием о выборе диапазона данных, который вы хотите объединить только в один столбец, см. снимок экрана:

4, Затем нажмите OK кнопку, и выбранные столбцы были объединены только в один столбец, как показано на следующих снимках экрана:

Советы: После запуска этого VBA исходные данные диапазона будут очищены, вам лучше сначала скопировать и сохранить их в другом месте.

Объедините несколько столбцов в один список с помощью Kutools for Excel

Если вы установили Kutools for Excel, Его Диапазон преобразования Утилита может помочь вам объединить несколько столбцов в один столбец или одну строку.

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите данные столбцов, которые вы хотите объединить в один список.

2. Затем нажмите Kutools > Диапазон > Диапазон преобразования, см. снимок экрана:

3. В Диапазон преобразования диалоговое окно, выберите Диапазон до одного столбца и нажмите Ok кнопку, см. снимок экрана:

4. В следующем выскочил Диапазон преобразования поле, щелкните ячейку, в которой вы хотите вывести результат, см. снимок экрана:

5. Затем нажмите OK Кнопка, выбранные вами столбцы были объединены в один столбец в указанном вами месте. Смотрите скриншот:

Пример формулы массива

Формулы массива — загадочная и, казалось бы, сложнейшая сущность в Excel. Но на практике все очень просто, нужно только понять принцип их работы и рассмотреть несколько примеров.

Не знаю, новичок вы в Excel или уже опытный пользователь, но коль судьба привела вас на эту страницу, в ходе прочтения вы:

узнаете все о формулах массива;
увидите множество примеров таких формул;
сможете начать активно их применять на практике.

Что такое формула массива

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

Будьте на 100% уверены: если формула

принимает массив на вход,
создает его как промежуточную сущность
или выводит как результат вычислений

— это формула массива.

Слишком очевидно и все еще непонятно? Не беда — читаем далее.

На деле, прежде, чем говорить о формулах массива, нужно упомянуть сами массивы и их отличие от диапазонов. Т.к. многие не до конца понимают разницу.

Массивы в Excel

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

Отличие массивов от диапазонов в Excel

В отличие от массива, диапазон (range) — это просто метод адресации к ячейкам в Excel, никак не связанный с самим их содержимым.

Между ячейками диапазона можно вставить дополнительные столбцы и строки, сделав его длиннее и шире, а ссылка на такой диапазон изменится автоматически.

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

Если же на лист Excel выведен массив, ни сортировка строк, ни вставка строк и столбцов в середину диапазона, в который он выведен, не возможна — это нарушило бы само его определение.

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

Ошибка Excel при попытке нарушить целостность массива

Размерность массивов

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

Одномерные массивы (векторы) в свою очередь могут быть вертикальными и горизонтальными.

Одномерные и двумерные массивы в Excel

Трехмерные массивы — довольно редко используемая на практике сущность.

Виды массивов

По типам элементов в них:
По типу данных —

числовой массив (массив чисел)
строковый массив (текстовые значения)
булевый массив (значения ИСТИНА-ЛОЖЬ)
смешанного типа

По изменяемости элементов —

Методы создания массивов

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

Массив констант, созданный вручную

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

При этом разделителем между элементами слева направо является точка с запятой, а разделителем строк — двоеточие.

Двумерные массивы вводятся построчно, т.е. сначала первая строка через точку с запятой, далее двоеточие, вторая строка через точку с запятой, и так далее.

Именованный массив констант

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

Массив цифр как в примере ниже, поможет быстро найти цифры в текстовых ячейках.

Это позволит больше не вводить его вручную, а обращаться к нему по его имени. Более того, Microsoft Excel помогает при их вводе всплывающими подсказками.

Используем именованный массив

С помощью функций

Указанные выше методы создания массивов довольно редки на практике. Чаще всего массивы создаются автоматически при обращении различных функций к диапазонам в Excel.

Единственное условие для создания массива с помощью функции — функция не должна обладать агрегирующим свойством. Например, СУММ, СРЗНАЧ не создадут массив при обращении к диапазону.

На этом сайте есть статья — как дописать символ, слово или текст к нескольким ячейкам. Там рассматриваются несколько способов, в том числе без создания дополнительного столбца. Но вот еще один — с помощью функции СЦЕПИТЬ и формулы массива:

Формула массива с функциями СЦЕПИТЬ и СИМВОЛ

Кавычки-ёлочки в этой формуле нам помогает создать функция СИМВОЛ.

С помощью математических операторов

Достаточно применить любую из математических операций к диапазону — и будет создан массив, аналогичный по размерам.

Массивы в Excel, полученные с помощью математических операторов

С помощью операторов сравнения

Аналогично математическим операторам, массивы создаются при сравнении диапазонов с константой или значением ячейки. Результатом операции сравнения являются значения ИСТИНА или ЛОЖЬ.

Массивы в Excel, полученные с помощью операторов сравнения

С помощью других массивов

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

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

Размер результирующего массива в таком случае будет аналогичен размеру массива констант.

Еще раз обратите внимание на разделители строк и столбцов в массивах. Столбцы разделяются точкой с запятой, строки — двоеточием.

С помощью комбинаций указанных методов

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

Вывод массива на лист

Для корректного вывода массива на лист нужно выделить диапазон ячеек эквивалентной размерности и размера, использовать метод создания массива (вручную или формулой), и нажать

На примере результирующий массив должен быть размером 5*5, но перед вводом формулы массива был выделен диапазон 6*6.

Ошибка НД при несоответствии размера диапазона и массива

Взаимодействие функций Excel с массивами

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

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

Из раздела математических функций к таковым также относятся ПРОИЗВЕД, СУММПРОИЗВ, СУММЕСЛИ, СУММЕСЛИМН.

Практически все статистические функции по природе берут на вход диапазон или массив и возвращают одно число. Наиболее популярные из них — СРЗНАЧ, МИН, МАКС, СЧЁТ, СЧЁТЗ, СЧЁТЕСЛИ.

Среди логических функций агрегирующим свойством обладают функция И и функция ИЛИ.

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

учитывают весь массив
выводят одно значение

ВЫБОР, ВПР, ГПР, ИНДЕКС, ПОИСКПОЗ — эти функции поиска используются наиболее часто.

Формулы массива — примеры формул

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

Читайте также: