Как преобразовать массив в столбец Excel
Обновлено: 04.07.2024
Мне нужен один столбец со всеми этими значениями. Результат должен выглядеть так:
Функция ТРАНСП в этом случае не работает. Я попробовал функцию ДВССЫЛ, но не нашел решения. Я бы предпочел обрабатывать это стандартными формулами Excel, а не макросом.
6 ответов 6
В E1 введите:
и скопируйте, чтобы получить:
Используя аналогичные формулы, вы можете преобразовать любую двумерную таблицу в одну строку или один столбец. Столь же легко сопоставить отдельный столбец или строку с таблицей.
Найдена версия @user3964075, немного более понятная. Использование немецкой версии Excel. Таким образом, это =BEREICH.VERSCHIEBEN($A$1;KÜRZEN((ZEILE()-1)/3);REST(ZEILE()-1;3)). Может быть, даже в английской версии запятая должна быть заменена точкой с запятой. Не могу проверить. Если да, исправьте.
Здравствуйте, можете ли вы обновить ответ прямоугольной матрицей? я не знаю, какие 3 строки, а какие столбцы
1) Определяет значения разделителей строк/столбцов самостоятельно и сразу же работает для прямоугольных, например. выше нужно явно ввести 3 для количества столбцов и 3 для количества строк, а также выяснить, что есть что. В то время как решение Pearson делает это автоматически (например, комментарий/проблема rmf выше).
2) Pearson предоставляет оба варианта для упорядочения по цвету и упорядочения по строке.
Это уже было рассмотрено в пункте 2 в самом общем виде
Верхняя ячейка целевого диапазона (скажем, $H$1 ) должна содержать
где $A$1:$C$3 содержит исходные данные. Скопируйте формулу вниз по мере необходимости.
Вы также можете использовать
как указано в упомянутой статье.
Я предлагаю вам включить функцию разворота Excel, чтобы выполнить вашу задачу.
- Выберите матрицу
- Перейдите к разделу Получить и преобразовать на вкладке Данные и нажмите Из таблицы/диапазона
- В новом Редакторе Power Query выберите столбцы, которые вы хотите развернуть.
- Перейдите к разделу Любой столбец на вкладке Преобразование, щелкните стрелку рядом с пунктом "Отменить сводные столбцы" и выберите лучший вариант (если вы выполните шаг 3, вы можете нажать "Отменить сводные столбцы"). только выбранные столбцы)
- Закрыть и загрузить
Этот процесс особенно полезен для сложных матриц.
Для получения дополнительной информации перейдите по приведенной выше ссылке
Для обобщенного подхода, который создаст массив, вы можете использовать:
Хотя здесь используется функция LET, она используется для удобочитаемости. Для тех, кто не использует Excel365, это можно сделать и без LET, но это просто больно читать.
Если вам нужно, чтобы результат был представлен в виде столбца, измените порядок аргументов в SEQUENCE
Конечно, A1:C3 может быть любым массивом произвольной формы.
В текущей версии Excel я понял, что операции с матрицей значительно улучшились. Для преобразования матрицы в один столбец я предлагаю объединить функции ИНДЕКС и ПОСЛЕДОВАТЕЛЬНОСТЬ следующим образом:
Если вы хотите удалить пустые места:
Вот краткое объяснение:
Для индекса требуются три вещи: источник матрицы, строка и столбец. Строка и столбец, которые вы вычисляете каждый раз, позволяют индексу указывать на этот конкретный элемент в вашем источнике матрицы. Индекс даст вам столько элементов, сколько вам нужно, то есть Rows * Columns.
Позвольте мне расширить строки и столбцы, используя ПОСЛЕДОВАТЕЛЬНОСТЬ для навигации по каждому элементу:
Формула ПОСЛЕДОВАТЕЛЬНОСТЬ сгенерирует столько элементов, сколько есть в вашей матрице, начиная с 0 и заканчивая строками*столбцами-1, а деление по столбцам будет указывать только на определенную строку. +1 используется, потому что последовательность начинается с 0, а элемент INDEX Row работает от 1 до n. TRUNC используется только для получения целого числа строки.
Здесь вы применяете тот же принцип, используя ПОСЛЕДОВАТЕЛЬНОСТЬ, чтобы сгенерировать столько элементов, сколько есть в вашей матрице от 0 до строк * столбцов-1, и, получив остаток между последовательностью и столбцами, вы укажете на нужный вам столбец.
Хорошо, это мой первый пост, надеюсь, он кому-нибудь поможет. Спасибо за помощь во многих случаях!
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Иногда вам нужно переключать или вращать ячейки.Вы можете сделать это, скопировав, вставив и используя опцию Transpose. Но при этом создаются дублированные данные. Если вы этого не хотите, вы можете вместо этого ввести формулу, используя функцию ТРАНСП. Например, на следующем рисунке формула =ТРАНСП(A1:B4) берет ячейки с A1 по B4 и упорядочивает их по горизонтали.
Примечание. Если у вас установлена текущая версия Microsoft 365, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введите формулу в верхнюю левую ячейку выходного диапазона, а затем нажмите Ctrl+Shift+Enter для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.
Шаг 1. Выберите пустые ячейки
Сначала выберите несколько пустых ячеек. Но обязательно выберите то же количество ячеек, что и исходный набор ячеек, но в другом направлении. Например, здесь 8 ячеек, расположенных вертикально:
Итак, нам нужно выделить восемь горизонтальных ячеек, вот так:
Здесь окажутся новые транспонированные ячейки.
Шаг 2. Введите =ТРАНСП(
Не снимая выделения с пустых ячеек, введите: =ТРАНСПОЗА(
Excel будет выглядеть примерно так:
Обратите внимание, что восемь ячеек по-прежнему выделены, несмотря на то, что мы начали вводить формулу.
Шаг 3. Введите диапазон исходных ячеек.
Теперь введите диапазон ячеек, которые вы хотите транспонировать. В этом примере мы хотим перенести ячейки из A1 в B4. Таким образом, формула для этого примера будет следующей: =ТРАНСП(A1:B4) -- но пока не нажимайте клавишу ВВОД! Просто перестаньте печатать и перейдите к следующему шагу.
Excel будет выглядеть примерно так:
Шаг 4. Наконец, нажмите CTRL+SHIFT+ENTER
Теперь нажмите CTRL+SHIFT+ENTER. Почему? Поскольку функция ТРАНСП используется только в формулах массива, именно так вы завершаете формулу массива. Короче говоря, формула массива — это формула, которая применяется более чем к одной ячейке. Поскольку на шаге 1 вы выбрали более одной ячейки (вы это сделали, не так ли?), формула будет применена более чем к одной ячейке. Вот результат после нажатия CTRL+SHIFT+ENTER:
Вам не нужно вводить диапазон вручную. После ввода =ТРАНСП( вы можете использовать мышь, чтобы выбрать диапазон. Просто щелкните и перетащите от начала диапазона к концу. Но помните: нажмите CTRL+SHIFT+ENTER, когда закончите, а не ENTER сам по себе.
Нужно также транспонировать текст и форматирование ячеек? Попробуйте скопировать, вставить и использовать опцию Transpose. Но имейте в виду, что это создает дубликаты. Поэтому, если ваши исходные ячейки изменятся, копии не будут обновлены.
Есть еще что узнать о формулах массива. Создайте формулу массива или ознакомьтесь с подробными рекомендациями и примерами здесь.
Технические детали
Функция ТРАНСП возвращает диапазон ячеек по вертикали как диапазон по горизонтали или наоборот. Функцию ТРАНСП нужно вводить как формулу массива в диапазоне, который имеет такое же количество строк и столбцов, соответственно, как исходный диапазон имеет столбцы и строки. Используйте ТРАНСП, чтобы сместить вертикальную и горизонтальную ориентацию массива или диапазона на листе.
Синтаксис
Синтаксис функции ТРАНСП имеет следующий аргумент:
Обязательный массив. Массив или диапазон ячеек на листе, которые вы хотите транспонировать. Транспонирование массива создается путем использования первой строки массива в качестве первого столбца нового массива, второй строки массива в качестве второго столбца нового массива и так далее. Если вы не знаете, как ввести формулу массива, см. раздел Создание формулы массива.
У меня есть массив ячеек X= <'A','B','C','D',A,B,C,D>где A,B,C и D - векторы-столбцы одинакового размера. Я намеревался создать электронную таблицу с помощью: >>xlswrite('X.xls',X), но я не получил значения векторов в своей электронной таблице. Возможно, я неправильно создал массив ячеек (когда я отображаю массив ячеек, я получаю: >> XX =
0 комментариев
Принятый ответ
Прямая ссылка на этот ответ
Прямая ссылка на этот ответ
4 комментария
Прямая ссылка на этот комментарий
Прямая ссылка на этот комментарий
Это выдает ошибку: >>X=[<'A','B','C','D'>;A,B,C,D] Ошибка при использовании vertcat. Размеры объединяемых матриц не согласованы. .
Прямая ссылка на этот комментарий
Прямая ссылка на этот комментарий
Прямая ссылка на этот комментарий
Прямая ссылка на этот комментарий
Это сработало! Спасибо! Проблема заключалась в том, что я определил A, B, C и D как числовые массивы, а не как массивы ячеек. Теперь я преобразовал их в массивы ячеек, используя num2cell.
Прямая ссылка на этот комментарий
Прямая ссылка на этот комментарий
Есть ли способ итеративно записать большой массив ячеек в файл Excel? У меня есть массив ячеек 20000 на 1, и в каждой ячейке есть различное количество строк. Я пробовал xlswrite (имя файла, A), но это ничего не дало. Спасибо!
Еще ответы (2)
Прямая ссылка на этот ответ
Прямая ссылка на этот ответ
Похоже, что A, B, C и D на самом деле являются массивами ячеек, а не обычными числовыми массивами. Как вы создали A, B, C и D? Вы окружали их фигурными скобками, когда создавали? Если это так, вам не следует этого делать.
3 комментария
Прямая ссылка на этот комментарий
Прямая ссылка на этот комментарий
Хорошо, я понимаю вашу точку зрения, но это не помогает, даже если A, B, C, D являются числовыми массивами. На самом деле это то, что я получил сначала: X =
Та же проблема возникла при попытке написать таблицу. Затем я изменил числовые массивы на массивы ячеек с помощью num2cell, чтобы посмотреть, поможет ли это, но это не помогло.
Прямая ссылка на этот комментарий
Прямая ссылка на этот комментарий
Да, вы правы. Теперь, когда я думаю об этом, он может записывать числовой массив, но если вы собираетесь их комбинировать (строки плюс числа), вам нужно иметь один большой массив ячеек, где каждая ячейка представляет собой только одно число (элемент) из массива. числовой массив, а не весь массив. Так что вам нужно сделать
Прямая ссылка на этот комментарий
Прямая ссылка на этот комментарий
Прямая ссылка на этот ответ
Прямая ссылка на этот ответ
У меня дополнительный вопрос. Я хочу организовать некоторые данные, и у меня есть массив ячеек с некоторыми именами. Ячейки не обязательно имеют одинаковый размер, так как у меня может быть 5 имен в одной ячейке и 12 в следующей. Всего у меня 1783 ячейки, поэтому я ищу автоматизированный способ решения этой проблемы. Как я могу экспортировать этот массив ячеек в электронную таблицу Excel?
0 комментариев
См. также
Категории
Охота за сокровищами сообщества
Найдите сокровища в MATLAB Central и узнайте, как сообщество может вам помочь!
Произошла ошибка
Не удалось выполнить действие из-за изменений, внесенных на страницу. Перезагрузите страницу, чтобы увидеть ее обновленное состояние.
Выберите веб-сайт
Выберите веб-сайт, чтобы получить переведенный контент, где он доступен, и ознакомиться с местными событиями и предложениями. В зависимости от вашего местоположения мы рекомендуем вам выбрать: .
Вы также можете выбрать веб-сайт из следующего списка:
Как добиться максимальной производительности сайта
Выберите китайский сайт (на китайском или английском языке) для лучшей производительности сайта. Сайты других стран MathWorks не оптимизированы для посещений из вашего местоположения.
В этой статье объясняется, как при необходимости избежать формул массива. Он не будет работать со всеми формулами массива, однако с меньшими формулами все в порядке.
Почему следует избегать формул массива? Неопытный пользователь Excel может превратить ее в обычную формулу, отредактировав ее и нажав Enter. Это нарушит формулу массива, и часто, но не всегда, будет возвращена ошибка.
Теперь, если вы не можете избежать формулы массива, вы можете заблокировать ячейку, содержащую формулу массива, и защитить рабочий лист. Я также объясню, как это сделать.
На написание этой статьи меня вдохновил следующий разговор.
Камран Мумтаз спросил: есть ли способ использовать функцию ВПР для нескольких критериев, и я не хочу использовать СПП?
CSE – это сокращение от сочетания клавиш CTRL + SHIFT + ENTER, которым вы вводите формулу массива.
Кямран Мумтаз: это формула, данная Аладином Акьюреком без (CSE).
Эта формула захватывающая, я никогда раньше не видел такого подхода. Похоже на формулу массива, но это не так. Это открывает возможности, о которых я раньше не думал.
Пример 1
Эта формула взята из следующей статьи: Извлечение уникального списка
Исходная формула массива:
Новая обычная формула в ячейке B2:
Новая формула НЕ является формулой массива! Удивительно! Я не уверен, что все формулы массива можно преобразовать в обычные формулы, но некоторые могут.
Объяснение формулы в ячейке B2
Формула в столбце B извлекает уникальные отдельные значения из столбца A без необходимости создавать формулу массива.
Ниже я объясню шаги расчета и обходной путь, который делает это возможным.
Шаг 1. Подсчет ячеек на основе условий
Функция СЧЁТЕСЛИ подсчитывает ячейки в диапазоне ячеек на основе условия. СЧЁТЕСЛИ(диапазон, критерий), диапазон равен $B$1:B1 и критерий равен $A$2:$ 5 австралийских долларов.
Возможно, это кажется неправильным, но это не так. $ B$1:B1 — это ссылка на ячейку, которая расширяется, когда ячейка копируется и вставляется в ячейки ниже. Знак доллара в $B$1 делает эту часть ссылки на ячейку абсолютной, то есть она заблокирована.
Другая часть — B1, и эта ссылка на ячейку является относительной, то есть она меняется от ячейки к ячейке, чтобы это работало, вам нужно скопировать ячейку, а не формулу.
Это означает, что "Уникальные уникальные значения" не равны ни одному значению в этом массиве. Этот метод обычно требует ввода формулы в виде формулы массива. Однако следующий шаг — обходной путь.
Шаг 2. Создайте обычную формулу
Как правило, если функция СЧЁТЕСЛИ возвращает массив, вам потребуется ввести формулу как формулу массива. Однако, если вы инкапсулируете функцию СЧЁТЕСЛИ с функцией ИНДЕКС, вы можете создать обходной путь. К сожалению, за это приходится платить, формула становится больше.
Вы можете сократить функцию ИНДЕКС до
и результат будет таким же.
Шаг 3. Найдите первое значение, которое не отображается
Функция ПОИСКПОЗ возвращает относительное положение первого значения, равного 0 (нулю). ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_сопоставления])
и возвращает 1. 0 (ноль) равен первому значению в массиве.
Шаг 4. Возврат значения на основе номера строки
Функция ИНДЕКС возвращает значение из указанного диапазона ячеек на основе указанного номера строки. ИНДЕКС(массив, [номер_строки], [номер_столбца])
ИНДЕКС($A$2:$A$5, ПОИСКПОЗ(0, ИНДЕКС(СЧЁТЕСЛИ($B$1:B1, $A$2:$A$5), 0, 0), 0))
и возвращает "AA" в ячейке B2.
Пример 2
Этот пример взят из этой статьи: Как вернуть несколько значений с помощью vlookup.Формула возвращает значения на основе условия, если значение в ячейке E1 равно одному или нескольким значениям в диапазоне ячеек A2:A5, формула вернет соответствующие значения из B2:B5.
Исходная формула массива:
Новая обычная формула в ячейке E2:
=ИНДЕКС($B$2:$B$5, МАЛЕНЬКИЙ(ИНДЕКС(($A$2:$A$5=$E$1)*(ПОИСКПОЗ(СТРОКА($B$2:$B$5), СТРОКА($B $2:$B$5)))+($A$2:$A$5<>$E$1)*1048577, 0, 0), СТРОКА(A1)))
Объяснение формулы в ячейке E2
Шаг 1. Сравните значения
Знак равенства позволяет проверить, равно ли значение другому значению или нескольким значениям.
Шаг 2. Создайте массив чисел от 1 до n
Нам нужен массив чисел от 1 до n, где n — общее количество строк в диапазоне ячеек.
и возвращает . В диапазоне ячеек $B$2:$B$5 есть четыре строки.
Шаг 3. Умножение логического выражения на массив
Знак звездочки позволяет перемножать числа, массивы и константы в формуле. Используйте круглые скобки для управления порядком операций.
ИСТИНА и ЛОЖЬ — логические значения, их числовые эквиваленты — 1 и 0 (ноль). Например, ИСТИНА*1=1, ЛОЖЬ*1=0.
Шаг 4. Проверьте, не равны ли значения значению в E1
Этот шаг содержит логическое выражение, которое возвращает ИСТИНА, если значение не равно ячейке E1, и ЛОЖЬ, если оно равно. Объедините символы меньше и больше, чтобы проверить, не равны ли значения.
Шаг 5. Умножение на значение, превышающее максимальный номер строки
Рабочий лист может содержать до 1048 576 строк, большее число вернет ошибку на шаге 9.
Шаг 6. Добавьте массивы
Знак плюс + позволяет добавлять массивы.
Шаг 7. Удаление формулы массива
Функция ИНДЕКС позволяет выполнять вычисления без создания формулы массива.
Шаг 8. Извлечение k-го наименьшего числа из массива
Функция НАИМЕНЬШИЙ извлекает k-е наименьшее число в массиве. Этот шаг обеспечивает отображение разных значений в каждой ячейке.
МАЛЕНЬКИЙ(ИНДЕКС(($A$2:$A$5=$E$1)*(ПОИСКПОЗ(СТРОКА($B$2:$B$5), СТРОКА($B$2:$B$5)))+($ A$2:$A$5<>$E$1)*1048577, 0, 0), СТРОКА(A1))
Функция ROW содержит относительную ссылку на ячейку, которая изменяется при копировании ячейки и вставке в ячейки ниже.
Шаг 9. Возврат значения из диапазона ячеек
На этом шаге используется функция ИНДЕКС для извлечения значения из диапазона ячеек B2:B5.
ИНДЕКС($B$2:$B$5, МАЛЕНЬКИЙ(ИНДЕКС(($A$2:$A$5=$E$1)*(ПОИСКПОЗ(СТРОКА($B$2:$B$5), СТРОКА($B$2 :$B$5)))+($A$2:$A$5<>$E$1)*1048577, 0, 0), СТРОКА(A1)))
и возвращает 1 в ячейке E2.
Функция ЕСЛИ имеет логическое выражение, которое сравнивает несколько значений с условием.
Получить файл Excel
no-arrays.xlsx
Как заблокировать ячейки
Вы можете заблокировать ячейки, чтобы их нельзя было редактировать, и это повысит безопасность использования формул массива. Имейте в виду, что существуют инструменты, которые могут легко открывать листы, защищенные паролем.
Все ячейки на листе по умолчанию заблокированы, вы можете это проверить. Выберите любую ячейку на листе, нажмите CTRL + 1, чтобы отформатировать ячейку. Перейдите на вкладку «Защита».
На этой вкладке показано, заблокирована ли ячейка, флажок установлен, если он заблокирован. Чтобы заблокировать ячейки, необходимо защитить лист.
- Перейдите к листу, который хотите защитить.
- Перейдите на вкладку "Обзор" на ленте.
- Нажмите мышью на кнопку "Защитить лист".
- Введите пароль, который хотите использовать. Запомните его, потому что он понадобится вам в следующий раз, когда вы захотите снять защиту с листа.
- Нажмите левой кнопкой мыши на кнопку OK.
Что вы предпочитаете? Формулы массива или несколько более длинные и сложные обычные формулы?
Еженедельный блог EMAIL
[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.
Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.
Подпишитесь на мою рассылку, чтобы не пропустить новые статьи блога.
Статьи по теме
Формула массива в ячейке D3 возвращает длину самой длинной последовательной последовательности значений в столбце A. Ячейка […]
Функция АГРЕГАТ позволяет вычислять различные специальные функции для списка или базы данных. Специальная функция функции АГРЕГАТ […]
На изображении выше показана формула в ячейке D3, которая пытается получить наименьшее число из диапазона ячеек […]
Я прочитал интересный пост в блоге. Содержится ли определенное слово в текстовой строке? на странице электронной таблицы. Это вдохновило меня […]
Я нашел этот вопрос Excel: Я контролер в многонациональной компании. У нас много транзакций (продажи, кредиты, дебеты, […]
Вы когда-нибудь пытались ввести формулу массива в объединенные ячейки? Тогда вы знакомы с этим сообщением об ошибке: […]
Вопрос: я хочу просуммировать ячейки, содержащие букву "C" и десятичное число. Ячейки имеют другие номера и […]
Читайте также: