Перейти к содержимому

Excel. Преобразование массива в столбец или строку

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

Рис. 1. Массив преобразован в столбец

Скачать статью в формате Word, примеры в формате Excel

Функция ИНДЕКС возвращает значение, хранящееся в ячейке (являющейся элементом массива). Какую ячейку выбрать, функция указывает по горизонтальному и вертикальному номеру ячейки, отсчитываемому от левого верхнего угла массива. Например (рис. 2):

Рис. 2. Пример «работы» функции ИНДЕКС

=ИНДЕКС(A1:G16;3;5)

где      A1:G16 – область массива

3 – номер строки массива

5 – номер столбца массива

то есть, индекс задал ячейку Е3

Чтобы легче понять формулу ИНДЕКС, преобразующую массив в столбец, выполним последовательные шаги (см. также лист «Рис. 3» Excel-файла):

  1. Функция СТРОКА() возвращает номер строки той ячейки, в которой она забита (рис. 3); столбец I;
  2. Функция ЧИСЛСТОЛБ($A$1:$G$16) возвращает число столбцов в массиве;
  3. Функция ОКРВВЕРХ(СТРОКА()/ЧИСЛСТОЛБ($A$1:$G$16);1) возвращает номер столбца, ячейки, значение который мы хотим получить, столбец J; дает значение 1 для первых 7 значений (в общем случае, для первых N значений, где N – число столбцов в массиве);
  4. Функция ОСТАТ(СТРОКА();ЧИСЛСТОЛБ($A$1:$G$16)+0,0001) возвращает номер от 1 до 7 (в общем случае, от 1 до N, где N – число столбцов в массиве); получается, что идет перебор индексов: сначала первый индекс равен 1 (строка), а второй изменяется от 1 до 7 (столбец); далее строка = 2, а столбец перебирается от 1 до 7 и т.д., пока не пройдемся по всем строкам массива; +0,0001 – это маленькая хитрость; без этой добавочки при делении 7/7 будет получаться 0 в остатке, а нам нужно получить 7; эта формула расположена в столбце K;
  5. Функция ИНДЕКС($A$1:$G$16;ОКРВВЕРХ(СТРОКА()/ЧИСЛСТОЛБ($A$1:$G$16);1); ОКРВВЕРХ(ОСТАТ(СТРОКА();ЧИСЛСТОЛБ($A$1:$G$16)+0,0001);1)) возвращает значение из ячейки; см. столбец L
    1. Массив, где ищется значение, выделен желтым
    2. Номер строки внутри массива – зеленым
    3. Номер столбца внутри массива – серым

В Excel-файле представлено преобразование массива в столбец и строку. При этом приведено два варианта преобразования:

  • Сначала по строкам, затем по столбцам массива
  • Сначала по столбцам, затем по строкам массив

34 комментария для “Excel. Преобразование массива в столбец или строку”

  1. дмитрий

    всюду слово возвращает — не понятно значение этого слова в данном контексте. Что возвращает, куда?
    Такое чувство что взяли статью на англ языке и кинули в переводчик сократ. затем выложили.

  2. Не знаю, что вас так смутило… 🙂 Функция либо принимает значение, либо возвращает значение. Например, введите в Goggle «функция возвращает значение» и получите около 500 млн. ссылок. Функция — это черный ящик, на входе параметры, которые обрабатываются функцией и возвращается результат. Мне не режет слух… 🙂 Статья конечно же оригинальная…

  3. Николай

    Отличная статья! Всё ясно и понятно. Давно задавался вопросом транспонирования таблицы, но нужное решение нашлось только сейчас.
    Большое Вам спасибо!
    to Дмитрий.
    Если не шаришь — лучше попытайся разобраться, прежде чем комент писать.

  4. А как сделать наоборот: из столбца (или строки) сформировать массив?

  5. Можно воспользоваться формулой массива; пример я поместил на лист «Обратное преобразование» в Excel-файле. Что-то типа:
    {=ИНДЕКС(A7:X7;СТРОКА()*$K$2-$K$2+СТОЛБЕЦ())}, где
    A7:X7 — строка, которую нужно преобразовать в массив; сам массив занимает область A1:F4
    $K$2 — ячейка, в которой задано число столбцов в массиве = 6
    формулу, указанную выше следует вводить как формулу массива; если не сталкивались с таковыми, см., например, Введение в формулы массива
    Строка в массив

  6. +0,0001 – это маленькая хитрость; без этой добавочки при делении 7/7 будет получаться 0 в остатке, а нам нужно получить 7;
    маленькая хитрость — на большой выборке => сюрприз
    ОСТАТ(7*10002;7+0.0001) = 5.9999
    ОКРВВЕРХ(ОСТАТ(7*10002;7+0.0001);1) = 6, а не 7

    корректно использовать : -1 и +1
    ОСТАТ(СТОЛБЕЦ()-1;7)+1

  7. Александр

    А как быть, если это массив слов, а не чисел?
    Есть несколько столбцов со словами, нужно их «загнать» в один.

  8. Александр, формула работает с ячейками. Ей всё равно, что в них, числа или текст. Пробуйте. Должно получиться и с массивом слов.

  9. А если мне нужно 1 строку превратить в 3 столбца, а не в 1? Эдакое транспонирование сегментами? Возможно ли это средствами Excel?

  10. Спасибо!!за формулу! все работает как часы!!!

  11. Спасибо, очень полезная формула, как раз то, что искал.
    Подскажите пожалуйста, как сделать так, что в результатах там, где в массиве пустоты, Ваша формула не выводила 0.

    Пример массива:
    a aa aaa aaaa
    b bb bbb
    c cc
    d dd ddd ddd

    Текущий результаты:
    a
    aa
    aaa
    aaaa
    b
    bb
    bbb
    0
    c
    cc
    0
    0
    d
    dd
    ddd
    ddd

    Желаемый результат:
    a
    aa
    aaa
    aaaa
    b
    bb
    bbb
    c
    cc
    d
    dd
    ddd
    ddd

  12. Руслан, если бы вы просто хотите заменить ноль пустым значением, этого можно было бы добиться функцией ЕСЛИ, наложенной поверх ИНДЕКС. Но вы еще хотите «подтянуть» столбец, чтобы пустые значения вовсе не выводились. Думаю, что это возможно, но формула будет очень громоздкой.

  13. Нужно подтянуть столбец вверх.
    Если это сложно сделать, то бог с ним…
    В настоящее время я решаю эту проблему следующим образом:
    Получаю Вашей формулой столбец значений, копирую и вставляю полученные значения (чтобы не было формулы в теле ячейки) в соседний столбец.
    Выделяю столбец — Asap Utilites — Select — Select cell based on content formatting and more — contains — 0 — Ok выделяются ячейки с 0 нажимаю клавишу Delete.
    Опять выбираю столбец (где вместо 0 уже пустые ячейки) — Asap Utilites — Delete all empty rows
    Получаю желаемый результат. Как на примере Выше.

  14. Николай

    Формула — как раз то что я искал, спасибо! Только один момент: у меня в массиве более 700k значений, можно как-то сделать, чтобы не надо было протягивать весь столбец вручную, а чтобы он сам как бы разверулся?

  15. Николай

    Ответ нашелся на одном из форумов, может кому-нибудь пригодится:

    например, попробуйте так — выделяете ячейку с нужной формулой. Нажимаете кнопочку «Копировать» (или клавиши Ctrl-Insert) переходите на первую ячейку, откуда хотите начать вставку формулы, потом, CTRL-G (это меню Правка — Перейти) — там набираете нужный адрес, обязательно нажимаете Shift и мышкой на кнопочку «ОК» (при этом выделились нужные ячейки) — потом «Вставить» (клавиши Shift-Insert)
    Взято здесь: http://www.programmersforum.ru/showthread.php?t=26516

  16. Руслан, я это тоже искал, нашел довольно подробный ответ здесь — «Создание списка в Excel без повторов». Вот только первая пустая ячейка у меня все равно сохраняется, это я убрать не смог. Да, там есть опечатка: вместо «$B1:$B1» (примерно в центре статьи) д.б. «B$1:$B1».

  17. Уважаемый Baguzin — СПАСИБО! Эта задача возникла у меня несколько дней назад, я сгоряча не полез в Интернет. Теперь обнаружил, что решил ее формулой, очень похожей на вашу. Но у наших решений обнаружились и недостатки. Я согласен с Сергеем, что „маленькая хитрость — на большой выборке => сюрприз“, хоть и применил ее не для определения КОЛОНКИ матрицы, а для ее СТРОКИ. Формулу Сергея „ОСТАТ(СТОЛБЕЦ()-1;7)+1“ я что-то не понял, потому остаюсь пока при своей. Так что давайте объединять наши „плюсы“. Далее для краткости вместо „A$1:$G$16“ пишу „мас“:

    =индекс(мас;ОКРВВЕРХ(СТРОКА()/ЧИСЛСТОЛБ(мас);1);ЧИСЛСТОЛБ(мас)-ОСТАТ(СТРОКА;ЧИСЛСТОЛБ(мас));ЧИСЛСТОЛБ(мас)))

    За критику буду благодарен.

  18. Дмитрий

    Мой способ добавления каждого столбца матрицы по очереди:
    =ИНДЕКС(матрица;СТРОКА()-ЧСТРОК(матрица)*ЦЕЛОЕ((СТРОКА()-1)/ЧСТРОК(матрица));ЦЕЛОЕ((СТРОКА()-1)/ЧСТРОК(матрица))+1)

  19. Наталья

    Отличная статья!!! То, что я искала. 🙂 СПАСИБО ОГРОМНОЕ!!!

  20. Добрый день!

    Отличная формула! Работает на ура и не сложна в понимании. Огромное спасибо!

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

    Заранее благодарю за помощь, если у кого-то возникнут идеи!

  21. Уже разобралась сама, посредством мозгового штурма:
    при подсчете номера столбца после всех данных в формуле:
    =ЕСЛИОШИБКА(ИНДЕКС($A$1:$G$16;ОКРВВЕРХ( ОСТАТ(СТРОКА();ЧСТРОК($A$1:$G$16)+0,0001);1); ОКРВВЕРХ(СТРОКА()/ЧСТРОК($A$1:$G$16);1);"")

    Добавляем следующую надстройку, которая учитывает интервал между выводимыми столбцами:
    +ЕСЛИ(ОСТАТ(СТРОКА()*3/ЧСТРОК($A$1:$G$16);3)>0;СТРОКА()*3/ЧСТРОК($A$1:$G$16)-ОСТАТ(СТРОКА()*3/ЧСТРОК($A$1:$G$16);3);ОКРУГЛВНИЗ(СТРОКА()*3/ЧСТРОК($A$1:$G$16);0)-3),
    где 3 — количество столбцов, которые необходимо пропустить.

    Вдруг кому-то пригодится)

  22. Роман Ц.

    Всем добрый день!
    А если, например, массив с датами. Преобразование в столбец работает, но как упорядочить даты в столбце по возрастанию?

  23. Николай

    Огромное спасибо! Очень помогла Ваша формула!
    Здоровья, счастья и с наступающим Новым 2020 Годом!!!

  24. Сумачев

    Добрый день, есть таблица данных за 70 лет в столбцах забиты месяцы, в строках дни от одного до 31, затем идет следующий год и так до конца в 2000 строка. Подскажите, пожалуйста, способ преобразования данных так, чтобы значения шли друг за другом по месяцам, годам?

  25. Пришлите файл на s_bag@mail.ru. Есть идея — думаю, что с Power Query смогу сделать нужное преобразование.

  26. Товарищи, а можно ли средствами эксель сделать такое преобразование — например, есть массив 12 столбцов по 10 строк, нужно этот массив преобразовать в массив из 4 столбцов по 30 строк как в приложенном файле. Очень нужно

  27. С помощью лямбды (версия 365) можно короче:

    =LET(
    а2;массив;
    а3;ТРАНСП(ТЕКСТРАЗД(REDUCE("";а2;LAMBDA(а;б;а&"@"&б));"@"));
    ВЗЯТЬ(а3;-СЧЁТЗ(а3)+1))

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *