Как преобразовать массив в столбец excel
Excel формулы, возвращающие набор значений, также известных как массив, возвращают эти значения в соседние ячейки. Это действие называется переносом.
Формулы, которые могут возвращать массивы переменного размера, называются формулами динамического массива. Формулы, которые возвращают массивы, которые успешно пролиты, могут называться формулами с пролитой массивом.
Ниже следующую заметку можно найти, чтобы помочь вам понять и использовать формулы такого типа.
Что означает "пролитие"?
Примечание: Более старые формулы массива, известные как формулы массива прежних поколений, всегда возвращают фиксированный размер— они всегда протекают в одно и то же количество ячеек. Поведение, описанное в этом разделе, не относится к устаревшим формулам массива.
Пролитие означает, что формула содержит несколько значений, которые были помещены в соседние ячейки. Например, =СОРТ(D2:D11;1;-1) для сортировки массива в порядке убыния возвращает соответствующий массив высотой 10 строк. Но вам нужно ввести формулу только в левую верхнюю ячейку , или F2 в этом случае, и она автоматически перетекает на ячейку F11.
Основные моменты
При нажатии ввода для подтверждения формулы Excel динамический размер диапазона вывода и поместите результаты в каждую ячейку в этом диапазоне.
Если вы пишете формулу динамического массива для работы со списком данных, может быть полезно разместить ее в таблице Excel, а затем использовать структурированные ссылки для ссылки на данные. Это происходит потому, что структурированные ссылки автоматически настраиваются при добавлении или удалении строк из таблицы.
Пролитые формулы массива не поддерживаются в Excel таблицах, поэтому их следует разместить в сетке за пределами таблицы. Таблицы лучше всего подходят для хранения строк и столбцов независимых данных.
После ввода формулы разлитого массива при выделении любой ячейки в области Excel вокруг диапазона будет выделена граница. При выборе ячейки за пределами области граница исчезнет.
Редактировать можно только первую ячейку в области пролития. Если выбрать другую ячейку в области пролития, формула будет видна в области формул, но текст будет замеен, и изменить его будет нельзя. Если вам нужно обновить формулу, вы можете выбрать левую верхнюю ячейку в диапазоне массива, при необходимости изменить ее, а Excel при нажатии нажатием ввод автоматически обновит оставшуюся часть области.
Устаревшие формулы массива, вводиться с помощью CTRL+SHIFT+ВВОД (CSE), по-прежнему поддерживаются по соображениям совместимости, но использовать их больше не нужно. При этом можно преобразовать устаревшие формулы массива в динамические формулы массива, выйдя из первой ячейки в диапазоне массива, скопируйте текст формулы, удалите весь диапазон массива, а затем снова введите формулу в левую верхнюю ячейку. Перед обновлением устаревших формул массива до формул динамического массива следует помнить о некоторой разнице междуними в вычислениях.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Предположим, у вас есть список из одного столбца с несколькими строками, и теперь вы хотите преобразовать этот список в несколько столбцов и строк, как показано ниже, для четкого просмотра данных. Как быстро решить эту проблему в Excel? В этой статье вы можете найти хороший ответ.
Преобразование нескольких строк в столбцы с помощью Kutools for Excel
Преобразование нескольких строк в столбцы с помощью формулы
Чтобы преобразовать несколько строк в столбце в несколько столбцов и строк, вы можете использовать формулы, выполнив следующие действия:
Выберите пустую ячейку, которую вы хотите вывести, поместите первые данные вашего списка, говорит Ячейка C2, и введите эту формулу =OFFSET($A$1,(ROW()-2)*3+INT((COLUMN()-3)),MOD(COLUMN()-3,1)) , Затем нажмите Enter , чтобы получить первый список формы данных, затем перетащите дескриптор автозаполнения в нужные ячейки, а затем перетащите дескриптор автозаполнения вниз к ячейкам, пока не отобразится ноль. Смотрите скриншоты:
Примечание:
Эту формулу можно интерпретировать как
OFFSET ($ A $ 1, (ROW () - f_row) * rows_in_set + INT ((COLUMN () - f_col) / col_in_set), MOD (COLUMN () - f_col, col_in_set))
f_row номер строки этой формулы
f_col это номер столбца этой формулы
rows_in_set это количество строк, которые составляют одну запись данных
col_in_set это количество столбцов исходных данных
Вы можете изменить их по своему усмотрению.
Преобразование нескольких строк в столбцы с помощью Kutools for Excel
Для некоторых более экологичных формул приведенная выше формула может быть несколько сложной, но не волнуйтесь, Kutools for ExcelАвтора Диапазон транспонирования Утилита может помочь каждому быстро преобразовать несколько строк в столбце в столбцы и строки.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите список столбцов и щелкните Kutools > Диапазон > Диапазон преобразования. Смотрите скриншот:
2. Затем в Диапазон транспонирования диалог, проверьте Один столбец для диапазона вариант в Тип трансформации раздел, проверьте Фиксированная стоимость вариант в Строк на запись раздел и введите количество фиксированных столбцов в следующее текстовое поле. Смотрите скриншот:
3. Нажмите Ok, и появится диалоговое окно, напоминающее вам о выборе ячейки для вывода результата. Смотрите скриншот:
4. Нажмите OK. И список с несколькими строками был преобразован в несколько столбцов и строк.
Если у вас есть таблица с данными в столбцах, которые необходимо повернуть для переупорядочивать их по строкам, используйте функцию Транспонировать. С его помощью можно быстро переключать данные из столбцов в строки и наоборот.
Например, если данные выглядят так: "Регионы продаж" в заголовках столбцов и "Кварталы" с левой стороны:
Функция Транспонировать переупомешет таблицу, в которой столбцы "Кварталы" отображаются в заголовках столбцов, а слева будут показаны регионы продаж, например:
Примечание: Если данные хранятся в таблице Excel, функция Транспонирование будет недоступна. Можно сначала преобразовать таблицу в диапазон или воспользоваться функцией ТРАНСП, чтобы повернуть строки и столбцы.
Вот как это сделать:
Выделите диапазон данных, который требуется переупорядочить, включая заголовки строк или столбцов, а затем нажмите клавиши CTRL+C.
Примечание: Убедитесь, что для этого нужно скопировать данные, так как не получится использовать команду Вырезать или CTRL+X.
Выберите новое место на том месте на компьютере, куда вы хотите ввести транспонную таблицу, чтобы вместить данные в достаточном месте. В новой таблице будут полностью переоформатироваться все данные и форматирование, которые уже есть.
Щелкните правой кнопкой мыши левую верхнюю ячейку, в которой нужно ввести транспонировать таблицу, и выберите транспонировать .
После успешного поворота данных можно удалить исходную таблицу, и данные в новой таблице останутся без изменений.
Советы по транспонированию данных
Если данные содержат формулы, Excel автоматически обновляет их в соответствие с новым расположением. Убедитесь, что в этих формулах используются абсолютные ссылки. Если они не используются, перед поворотом данных можно переключаться между относительными, абсолютными и смешанными ссылками.
Если вы хотите часто поворачивать данные для их просмотра под разными углами, создайте с помощью нее с помощью перетаскиванием полей из области строк в область столбцов (или наоборот)в списке полей.
Вы можете ввести в книгу данные в качестве транспон данных. Транспонировать: переупочевание содержимого скопированные ячейки при копировании. Данные строк будут вставлены в столбцы, и наоборот.
Вот как можно транспоннять содержимое ячейки:
Скопируйте диапазон ячеев.
Вы выберите пустые ячейки, в которые вы хотите ввести транспонировать данные.
На вкладке Главная щелкните значок Ввести и выберите Ввести транспонировать.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Хотим, упрощенно говоря, повернуть таблицу на бок, т.е. то, что располагалось в строке - пустить по столбцу и наоборот:
Способ 1. Специальная вставка
Выделяем и копируем исходную таблицу (правой кнопкой мыши - Копировать). Затем щелкаем правой по пустой ячейке, куда хотим поместить повернутую таблицу и выбираем из контекстного меню команду Специальная вставка (Paste Special) . В открывшемся диалоговом окне ставим галочку Транспонировать (Transpose) и жмем ОК.
Минусы : не всегда корректно копируются ячейки с формулами, нет связи между таблицами (изменение данных в первой таблице не повлияет на вторую).
Плюсы : в транспонированной таблице сохраняется исходное форматирование ячеек.
Способ 2. Функция ТРАНСП
Выделяем нужное количество пустых ячеек (т.е. если, например, исходная таблица была из 3 строк и 5 столбцов, то выделить обязательно нужно диапазон из 5 строк и 3 столбцов) и вводим в первую ячейку функцию ТРАНСП (TRANSPOSE) из категории Ссылки и массивы (Lookup and Reference):
После ввода функции необходимо нажать не Enter, а Ctrl+Shift+Enter, чтобы ввести ее сразу во все выделенные ячейки как формулу массива . Если раньше не сталкивались с формулами массивов, то советую почитать тут - это весьма экзотический, но очень мощный инструмент в Excel.
Плюсы : между таблицами сохраняется связь, т.е. изменения в первой таблице тут же отражаются во второй.
Минусы : не сохраняется форматирование, пустые ячейки из первой таблицы отображаются в виде нулей во второй, нельзя редактировать отдельные ячейки во второй таблице, поскольку формулу массива можно менять только целиком.
Способ 3. Формируем адрес сами
Этот способ отчасти похож не предыдущий, но позволяет свободно редактировать значения во второй таблице и вносить в нее любые правки при необходимости. Для создания ссылок на строки и столбцы нам понадобятся четыре функции из категории Ссылки и массивы:
- Функция АДРЕС(номер_строки; номер_столбца) - выдает адрес ячейки по номеру строки и столбца на листе, т.е. АДРЕС(2;3) выдаст, например, ссылку на ячейку C2.
- Функция ДВССЫЛ(ссылка_в_виде_текста) - преобразует текстовую строку, например, "F3" в настоящую ссылку на ячейку F3.
- Функции СТРОКА(ячейка) и СТОЛБЕЦ(ячейка) - выдают номер строки и столбца для заданной ячейки, например =СТРОКА(F1) выдаст 1, а =СТОЛБЕЦ(А3) выдаст 3.
Теперь соединяем эти функции, чтобы получить нужную нам ссылку, т.е. вводим в любую свободную ячейку вот такую формулу:
=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))
в английской версии Excel это будет =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))
А затем копируем (протягиваем) формулу на соседние ячейки как обычно черным крестом. В итоге должно получиться примерно следующее:
Т.е. при копировании формулы вниз по столбцу, она выдает ссылку, которая уходит вправо по строке и наоборот. Чего и требовалось.
Плюсы : сохраняются связи между таблицами, можно легко вносить изменения во вторую таблицу.
Минусы : форматирование не сохраняется, но его можно легко воспроизвести Специальной вставкой (вставить только Формат с флажком Транспонировать
Читайте также: