Как преобразовать один столбец в несколько в эксель
Здравствуйте! Подскажите, пожалуйста, как сделать следующую вещь. Имеется произвольное число столбцов с произвольным количество строк. Необходимо, чтобы справа налево, все столбцы были сформированы в один большой столбец, в котором сначала используются все строки из первого столбца, затем из второго и т.д.
Например, имеются данные следующего вида:
1 5 a
2 6 b
3 7 c
4 8 d
Необходимо получить из этих трех столбцов один, который будет включать следующие элементы:
1
2
3
4
5
6
7
8
a
b
c
d
Напишите, пожалуйста, как это сделать. В excel разбираюсь довольно слабо, поэтому буду очень благодарен за подробные разъяснения.
Помощь в написании контрольных, курсовых и дипломных работ здесь
Скопировать несколько столбцов в один без пустых ячеек
Здравствуйте! Подскажите, пожалуйста, можно ли воплотить следующую вещь. В прикрепленном файле в.
Соединить несколько столбцов в один!
Есть задачка, ответа на нее нормального и рабочего не могу найти 3-ий день уже! Одни макросы с.
Обьединение столбцов в Excel по порядку
Здравствуйте! Подскажите, пожалуйста, как сделать следующую вещь. Имеется произвольное число.
Как отсортировать сразу несколько столбцов с числами по порядку?
Здравствуйте! Нужно отсортировать разом числа по возрастанию в столбцах.количество столбцов около.
вручную это выглядит так
1. становимся в В1 жмем Ctrl+Shift+стрелка вниз (выделятся неразрывные значения в столбце В) - Ctrl+X
2. становимся в А1 жмем Ctrl+стрелка вниз (переходим на последнюю заполненную ячейку неразрывных значений) с помощью стрелки переходим еще на одну ячейку ниже жмем Ctrl+V.
3. с третьим столбцом все тоже самое. Если я правильно Вас понял, вот пару вариантов, как это сделать. Огромное спасибо всем отписавшимся! В частности skinkol'у. Его вариант в третьем листе отлично подошел Ребят подскажите,куда вставлять данные коды,конкретно. 1-ячейка rw = 1 и т д, или как то по другому?.
Очень надо. более 100 стобцов по 500 слов надо в едино слепить для яендекс директа. Так макрос сработает правильно, если длина столбцов разная:
формула выглядит как:
=ИНДЕКС(B$2:K$42;ЕСЛИ(ЕСЛИ((СТРОКА()-ЦЕЛОЕ(СТРОКА()/40)*10)>40;1;(СТРОКА()-ЦЕЛОЕ(СТРОКА()/40)*40))=0;40;ЕСЛИ((СТРОКА()-ЦЕЛОЕ(СТРОКА()/40)*40)>40;1;(СТРОКА()-ЦЕЛОЕ(СТРОКА()/40)*40)));ОКРВВЕРХ(СТРОКА()/40;1))
=ИНДЕКС(B$2:K$42;ЕСЛИ(ЕСЛИ((СТРОКА()-ЦЕЛОЕ(СТРОКА()/40)*10)>40;1;(СТРОКА()-ЦЕЛОЕ(СТРОКА()/40)*40))=0;40;ЕСЛИ((СТРОКА()-ЦЕЛОЕ(СТРОКА()/40)*40)>40;1;(СТРОКА()-ЦЕЛОЕ(СТРОКА()/40)*40)));ОКРВВЕРХ(СТРОКА()/40;1))
В смысл формулы окончательно вникнуть не смог, поэтому - возможно - простое копирование и замена 40 на 48, и 10 на 30 не помогли.
Прошу - помогите. Она ведь почти работает ))
Господа, приветствую всех!
Пожалуйста не мучайте формулу, логическая ошибка вот тут:
=ИНДЕКС(C$1:AF$48;ЕСЛИ(ЕСЛИ((СТРОКА()-ЦЕЛОЕ(СТРОКА()/48)*30)>48;1;(СТРОКА()-ЦЕЛОЕ(СТРОКА()/48)*48))=0;48;ЕСЛИ((СТРОКА()-ЦЕЛОЕ(СТРОКА()/48)*48)>48;1;(СТРОКА()-ЦЕЛОЕ(СТРОКА()/48)*48)));ОКРВВЕРХ(СТРОКА()/48;1))
Вместо 30 должно быть 48.
Таким образом чтобы сделать формулу для себя, нужно вписать диапазон данных (в случае с zurkirill этот диапазон C$1:AF$48) и все циферки, которые отличаются от единички в формуле заменить на самую большую циферку диапазона (в случае с zurkirill эта циферка 48).
Предположим, у вас есть таблица, показанная ниже, и вам нужно изменить один столбец на диапазон. Здесь мы покажем вам некоторые хитрости о том, как превратить один столбец в несколько столбцов.
Перенести один столбец в несколько столбцов с формулами
Преобразуйте один столбец в диапазон данных от строки к строке:
В Excel OFFSET функция может вам помочь, вы можете использовать эту формулу как следующие шаги:
1. В пустой ячейке C1 введите следующую формулу: =OFFSET($A$1,COLUMNS($A1:A1)-1+(ROWS($1:1)-1)*5,0) , а затем перетащите маркер заполнения с C1 на G1, см. снимок экрана:
Внимание: A1 это запущенная ячейка, *5 обозначает количество ячеек, которое вы хотите иметь в каждой строке. Между тем вы должны начать эту формулу в столбце C.
2. Затем продолжайте перетаскивать маркер заполнения вниз до нужного диапазона. И данные одного столбца были преобразованы в несколько строк, как показано на следующем снимке экрана:
3. Поскольку они являются формулами, при их копировании необходимо копировать и вставлять их как значения.
Преобразуйте один столбец в диапазон данных от столбца к столбцу:
Приведенная выше формула может помочь вам перенести один столбец в диапазон от строки к строке, если вам нужно перенести столбец в диапазон от столбца к столбцу, следующая формула также может оказать вам услугу.
1. В пустой ячейке C1 введите следующую формулу: =INDEX($A$1:$A$20,ROW(C1)+(5*(COLUMNS($C$1:C$1)-1))) , а затем перетащите маркер заполнения с C1 на C5, см. снимок экрана:
Внимание: A1: A20 это данные столбца, которые вы хотите преобразовать, 5 * обозначает количество ячеек, которое вы хотите иметь в каждом столбце.
2. Затем перетащите маркер заполнения через столбец C в столбец F, и данные в одном столбце будут перенесены в диапазон от столбца к столбцу. см. снимок экрана:
Перенести один столбец в несколько столбцов с помощью Kutools for Excel
У этих формул есть некоторые ограничения, возможно, их сложно применять новичкам. Здесь я продолжу знакомить вас с простым способом решения этой проблемы. С участием Kutools for Excel's Диапазон преобразования вы можете быстро преобразовать данные одной строки или столбца в диапазон ячеек по мере необходимости. См. Демо ниже. Нажмите, чтобы загрузить Kutools for Excel!
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Выберите столбец, который вы хотите преобразовать.
2. Нажмите Kutools > Диапазон > Диапазон преобразования, см. снимок экрана:
3. В Диапазон преобразования диалоговом окне укажите следующие параметры:
- Выберите Один столбец для диапазона от Тип трансформации;
- Укажите количество ячеек в строке, если вы выбрали столбец с пустыми ячейками в качестве разделителя, вы можете проверить Пустая ячейка ограничивает записи, и данные начнут новую строку в каждой пустой ячейке. Вы также можете указать количество ячеек в строке из Фиксированная стоимость что вам нужно.
4. Затем нажмите OK, чтобы Диапазон преобразования Появится окно подсказки и щелкните ячейку, чтобы поместить результат. Диапазон вывода можно указать в разных листах и книгах. Смотрите скриншот:
5. Нажмите OK, вы увидите, что один столбец был преобразован в несколько столбцов в выбранной ячейке. Смотрите скриншот:
Kutools for Excel's Диапазон преобразования Инструмент может помочь вам легко преобразовать один столбец в диапазон, преобразовать одну строку в диапазон, преобразовать диапазон в одну строку и преобразовать диапазон в один столбец. Нажмите, чтобы узнать больше…
Если ваши данные в Microsoft Excel плохо структурированы и размещены в одном столбце, вы можете разделить его на несколько столбцов. Этот пост предлагает простые для понимания инструкции о том, как разбить один длинный столбец на несколько столбцов в Excel.
Разделить один столбец на несколько столбцов в Excel
В зависимости от количества набора данных пользователь может выбрать один или несколько разделителей, чтобы разделить текст в столбце.
- Используйте мастер преобразования текста в столбцы
- Выберите разделители для ваших данных
- Выберите направление
Вы можете взять текст в одном столбце и разделить его на несколько столбцов с помощью мастера преобразования текста в столбцы.
1]Используйте мастер преобразования текста в столбцы
Откройте лист Excel, в котором вы хотите разделить один столбец на несколько столбцов.
2]Выберите разделители для ваших данных
Здесь укажите разделители для ваших данных. Например, запятая и пробел.
По умолчанию Excel попытается разделить данные в одном столбце по каждой найденной вкладке. Это нормально, но вы можете настроить формат данных столбца,
- Общий
- Дата
- Текст и многое другое
General преобразует числовые значения в числа, значения дат в даты и все остальные значения в текст. Здесь мы используем данные, разделенные точкой с запятой.
3]Выберите пункт назначения
Как вы можете видеть на изображении выше, один столбец будет разделен на несколько (2 столбца), и данные будут выглядеть хорошо структурированными и организованными.
Если необходимо преобразовать массив в столбец или строку (рис. 1), можно воспользоваться функцией Индекс.
Рис. 1. Массив преобразован в столбец
Функция ИНДЕКС возвращает значение, хранящееся в ячейке (являющейся элементом массива). Какую ячейку выбрать, функция указывает по горизонтальному и вертикальному номеру ячейки, отсчитываемому от левого верхнего угла массива. Например (рис. 2):
Рис. 2. Пример «работы» функции ИНДЕКС
где A1:G16 – область массива
3 – номер строки массива
5 – номер столбца массива
то есть, индекс задал ячейку Е3
Чтобы легче понять формулу ИНДЕКС, преобразующую массив в столбец, выполним последовательные шаги (см. также лист «Рис. 3» Excel-файла):
- Функция СТРОКА() возвращает номер строки той ячейки, в которой она забита (рис. 3); столбец I;
- Функция ЧИСЛСТОЛБ($A$1:$G$16) возвращает число столбцов в массиве;
- Функция ОКРВВЕРХ(СТРОКА()/ЧИСЛСТОЛБ($A$1:$G$16);1) возвращает номер столбца, ячейки, значение который мы хотим получить, столбец J; дает значение 1 для первых 7 значений (в общем случае, для первых N значений, где N – число столбцов в массиве);
- Функция ОСТАТ(СТРОКА();ЧИСЛСТОЛБ($A$1:$G$16)+0,0001) возвращает номер от 1 до 7 (в общем случае, от 1 до N, где N – число столбцов в массиве); получается, что идет перебор индексов: сначала первый индекс равен 1 (строка), а второй изменяется от 1 до 7 (столбец); далее строка = 2, а столбец перебирается от 1 до 7 и т.д., пока не пройдемся по всем строкам массива; +0,0001 – это маленькая хитрость; без этой добавочки при делении 7/7 будет получаться 0 в остатке, а нам нужно получить 7; эта формула расположена в столбце K;
- Функция ИНДЕКС($A$1:$G$16;ОКРВВЕРХ(СТРОКА()/ЧИСЛСТОЛБ($A$1:$G$16);1); ОКРВВЕРХ(ОСТАТ(СТРОКА();ЧИСЛСТОЛБ($A$1:$G$16)+0,0001);1)) возвращает значение из ячейки; см. столбец L
- Массив, где ищется значение, выделен желтым
- Номер строки внутри массива – зеленым
- Номер столбца внутри массива – серым
В Excel-файле представлено преобразование массива в столбец и строку. При этом приведено два варианта преобразования:
- Сначала по строкам, затем по столбцам массива
- Сначала по столбцам, затем по строкам массив
31 комментарий для “Excel. Преобразование массива в столбец или строку”
Если у вас есть таблица с данными в столбцах, которые необходимо повернуть для переупорядочивать их по строкам, используйте функцию Транспонировать. С его помощью можно быстро переключать данные из столбцов в строки и наоборот.
Например, если данные выглядят так: "Регионы продаж" в заголовках столбцов и "Кварталы" с левой стороны:
Функция Транспонировать переупомешет таблицу, в которой столбцы "Кварталы" отображаются в заголовках столбцов, а слева будут показаны регионы продаж, например:
Примечание: Если данные хранятся в таблице Excel, функция Транспонирование будет недоступна. Можно сначала преобразовать таблицу в диапазон или воспользоваться функцией ТРАНСП, чтобы повернуть строки и столбцы.
Вот как это сделать:
Выделите диапазон данных, который требуется переупорядочить, включая заголовки строк или столбцов, а затем нажмите клавиши CTRL+C.
Примечание: Убедитесь, что для этого нужно скопировать данные, так как не получится использовать команду Вырезать или CTRL+X.
Выберите новое место на том месте на компьютере, куда вы хотите ввести транспонную таблицу, чтобы вместить данные в достаточном месте. В новой таблице будут полностью переоформатироваться все данные и форматирование, которые уже есть.
Щелкните правой кнопкой мыши левую верхнюю ячейку, в которой нужно ввести транспонировать таблицу, и выберите транспонировать .
После успешного поворота данных можно удалить исходную таблицу, и данные в новой таблице останутся без изменений.
Советы по транспонированию данных
Если данные содержат формулы, Excel автоматически обновляет их в соответствие с новым расположением. Убедитесь, что в этих формулах используются абсолютные ссылки. Если они не используются, перед поворотом данных можно переключаться между относительными, абсолютными и смешанными ссылками.
Если вы хотите часто поворачивать данные для их просмотра под разными углами, создайте с помощью нее с помощью перетаскиванием полей из области строк в область столбцов (или наоборот)в списке полей.
Вы можете ввести в книгу данные в качестве транспон данных. Транспонировать: переупочевание содержимого скопированные ячейки при копировании. Данные строк будут вставлены в столбцы, и наоборот.
Вот как можно транспоннять содержимое ячейки:
Скопируйте диапазон ячеев.
Вы выберите пустые ячейки, в которые вы хотите ввести транспонировать данные.
На вкладке Главная щелкните значок Ввести и выберите Ввести транспонировать.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Читайте также: