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

Рубрика: 7. Полезняшки 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-файле представлено преобразование массива в столбец и строку. При этом приведено два варианта преобразования:

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

Комментарии: 19 комментариев

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

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

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

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

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

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

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

Спасибо, сэкономил кучу времени 🙂

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

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

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

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

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

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

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

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

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

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

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

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

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


Прокомментировать