700 столбец в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция СТОЛБЕЦ в Excel и полезные примеры ее использования

Функция СТОЛБЕЦ в Excel возвращает номер столбца на листе по заданным условиям. Синтаксис элементарный: всего один аргумент. Но с ее помощью можно эффективно решать разнообразные задачи.

Описание и синтаксис функции

Функция с параметром: = СТОЛБЕЦ (С3) возвращает значение 3, т.к. (C) является третьим по счету.

Аргумент «ссылка» необязательный. Это может быть ячейка или диапазон, для которого нужно получить номер столбца.

Аргумент – ссылка на ячейку:

Функция выдала номер колонки для этой ячейки.

Функция вернула номер столбца, в котором находится.

Аргумент – вертикальный диапазон ячеек:

Функция вернула номер столбца, в котором расположен диапазон.

Аргумент – горизонтальный диапазон ячеек:

Функция СТОЛБЕЦ вернула номер крайнего левого столбца (А) в указанном диапазоне. Если выделить формулу в строке формул и нажать кнопку F9, то программа выдаст все номера столбцов заданного диапазона.

Но при нажатии кнопки Enter в ячейке с формулой отобразится только номер крайнего левого столбца.

Чтобы на листе появились номера всех столбцов диапазона, который является аргументом функции СТОЛБЕЦ, нужно использовать формулу массива. Выделяем такое количество ячеек, сколько элементов входит в горизонтальный диапазон. Вводим формулу и нажимаем сочетание кнопок Ctrl + Shift + Enter.

Аргумент – ссылка на горизонтальный массив:

Формула вернула номера столбцов в виде горизонтального массива.

В качестве аргумента нельзя применять ссылки на несколько областей.

Полезные примеры функции СТОЛБЕЦ в Excel

Формула с использованием функции выдает массив последовательных чисел. Эту особенность можно применить для решения других задач.

Например, рассчитаем значение выражения 1 + ½ + 1/3. Используем формулу: =СУММПРОИЗВ(1/СТОЛБЕЦ(A2:C2)).

Выполним более сложные манипуляции с числовым рядом: найдем сумму значений от 1 до 1/n^3, где n = 6. Формула расчета: =СУММПРОИЗВ(1/СТОЛБЕЦ(A9:F9)^3).

Чаще всего данную функцию используют совместно с функцией ВПР. Задача первой функции – указать номер столбца возвращаемых значений. Такое совмещение удобно при работе с огромными таблицами. Например, пользователь помещает возвращаемые данные в табличку с такой же, как в исходной таблице, последовательностью столбцов. Причем обе таблицы достаточно широкие.

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

Аргументы функции ВПР: искомое значение, массив данных для анализа, номер столбца, интервальный просмотр (точный или приблизительный поиск). Сам номер можно задать с помощью такой формулы: =ВПР(8;A1:C10;СТОЛБЕЦ(C1);ИСТИНА).

При работе с широкими таблицами можно просто копировать функцию ВПР по горизонтали. В этом случае номера столбцов автоматически пересчитываются – табличка заполняется.

Нужна корректировка номера– прибавляем или отнимаем определенную цифру или рассчитанное с помощью какой-либо функции значение. Например,

Функция СТОЛБЕЦ должна вычесть 1 из номера колонки C. Поэтому функция ВПР возвращает значение не из третьего, а из второго столбца девятой строки.

Теперь проиллюстрируем, как работает многоразовое копирование без необходимости в ручной правке. Сначала в формуле закрепим ссылки на таблицу (кнопка F4). Скопируем формулу ВПР поперек столбцов – номер меняется автоматически (функция СТОЛБЕЦ сдвигается вместе с другими ссылками).

Достаточно элегантное решение, позволяющее править формулы в автоматическом режиме.

Функция СТОЛБЕЦ в Excel

Приветствую вас на сайте TopExcel!

Темой этой статьи я хочу продолжить рассмотрения функционала раздела «Ссылки и массивы», а именно как работает функция СТОЛБЕЦ в Excel. Совсем недавно я рассматривал сестру этой функции – это функция СТРОКА. Общий принцип работы для этих функций и остается одинаковый, разнятся только координаты и направления, исходя из названий формул, в нашем случае функция СТОЛБЕЦ будет возвращать тот номер столбика для указания ссылки на ячейку или же диапазон ячеек.

Рассмотрим детально синтаксис, который использует функция СТОЛБЕЦ в Excel, хотя, что там смотреть, всего один аргумент, но всё же: =СТОЛБЕЦ(ссылка), где

• Ссылка – несмотря на то, что данный аргумент не является обязательным, он обязан указать на ячейку или диапазон ячеек, для которых будет определяться номер столбика.

Как видите всё максимально просто, но всё же несколько нюансов как используется функция СТОЛБЕЦ в Excel стоит прояснить, во избежание возникновения ошибок. Список будет небольшим, но полезным:

• Ссылаться на несколько областей, аргумент «ссылка» не может;
• В случае, когда аргумент «ссылка» ссылается на диапазон ячеек, а функция СТОЛБЕЦ вExcel введена не как формула для горизонтального массива, то функция вернет номер крайнего левого столбика вашей таблицы;
• Если же аргумент «ссылка» вами был не указан или же указана ссылка на диапазон ячеек, а функция СТОЛБЕЦ вExcel введена в таблице, как формула горизонтального массива, то наша функция также вернет номера столбиков в ссылке, и выглядеть будет это как горизонтальный массив. Внимание! Для ввода вашей формулы массива вам необходимо начиная с первой ячейки формулы, выделить нужный диапазон, в котором должна содержаться формула вашего массива. Для начала вы нажимаете клавишу F2, а после жмёте «горячее» сочетание клавиш Ctrl + Shift +Enter.
• Если вы не указали аргумент функции «ссылка», то по умолчанию этим аргументом будет та ячейка в которой, собственно и размещается функция СТОЛБЕЦ вExcel.

Обратите внимание! В программе Excel Online возможность создания формулы массива невозможно.

Рассмотрим на примерах, как используется функция СТОЛБЕЦ в Excel: Пример №1: Как видите с примера, и как я писал ранее, функция СТОЛБЕЦ без аргумента «ссылка» возвращает нам номер того столбика, где размещается, собственно, ваша формула. То есть, так как наша формула размещена в ячейке А9, то и результатом будет возвращения значения 1 (то есть первый столбик).

Пример №2: Зададим в этом примере аргументу «ссылка», например, ссылку на город «Рим» по адресу D5, и результатом станет номер «4», так название столицы Италии размещено в четвёртом столбике.

Пример №3: Также вы можете увидеть возможность, как используется функция СТОЛБЕЦ в Excel, на примере создания пронумерованного списка в статье «5 интересных способов как происходит нумерация строк в Excel».

Как видите, функция СТОЛБЕЦ очень проста в понимании и использовании, поэтому ее использование для вас станет простым и лёгким. С другими интересными и полезными функциями вы можете познакомиться в «Справочнике функций».

Если статья вам понравилась и пригодилась, напишите комментарий, поставьте лайк или делитесь с друзьями!

До новых встреч на страницах TopExcel.ru!

«Всякий, кому приходилось жить в бедности, знает, до чего это накладно — быть бедным
»
Джеймс Болдуин

700 столбец в excel

Исходные данные: Дана огромная таблица в Excel с большим количеством строк и столбцов.

Задача: собрать все значения всех колонок в один столбец.

Рис.1. Исходная таблица Excel

Решение: для решения задачи потребуется помощь макросов. Ниже приведен код макроса для решения задачи.

Для того, чтобы макрос сработал, выполните следующие шаги:

1. Добавть макрос в книгу (Разработчик – Visual Basic, в открывшемся редакторе вставьте код)

2. Выделите на листе диапазон данных, которые нужно собрать в один столбец;

3. Запустите макрос (Разработчик – Макросы – Выполнить)

Рис.2. Запуск макроса в Excel

На втором листе книги в первой колонке будет массив всех значений из выделенного фрагмента.

• 5 июня 2017, Alexey
• Уроки Excel
• 7 комментариев

7 комментариев »

Потрясена. Спасибо, очень вовремя.
Прошу дополнить код, если это возможно.
Надо, чтобы шапка столбца -дата- так же переносилась в новое место с сохранением очередности и формата «дата».

Комментарий от dina — 11.07.2018 @ 12:17

Спасибо, очень пригодилось.
Если можно попросить, то сделайте, пожалуйста, так, чтобы переносилась и дата с сохранением места и формата, у меня не получается никак.

Комментарий от dina — 11.07.2018 @ 13:39

Опубликуйте файл с исходными данными или скриншот. Не очень понимаю, что нужно сделать

Комментарий от Alexey — 12.07.2018 @ 9:59

добрый день!
Спасибо, что отозвались!
не знаю как послать данные и скрин.
А нужно так: есть 365 столбцов, в заголовке — дата, под ней в ячейках числа либо текст.
Следует перенести заголовки и данные столбцов в новый сохраняя календарную последовательность.

Комментарий от dina — 21.07.2018 @ 12:39

1) Файл таблицы должен быть в формате .xlsm
2) У меня не было кнопки «разработчик», чтобы появилась, нажать Файл — Параметры — Настроить ленту. Там справа в списке «Основные вкладки» поставить галку «Разработчик»
3) Вставив код в Visual Basic, это окно можно закрыть, сохранять не надо.

Комментарий от 4е — 27.03.2019 @ 23:29

Добрый день!
Макрос — просто супер.
Подскажите пожалуйста, почему я смог его выполнить только на 2х листах, далее макрос не срабатывает ни на других листах, ни в новых в новых файлах итд
перепробовал все способы, просто ничего не происходит.
Заранее спасибо!!

Комментарий от Денис — 26.07.2019 @ 18:49

Очень странно. Попробуйте закрыть все лишние файлы Excel, оставьте только рабочий файл. Уже тут откройте редактор макросов и вставьте в него код. Должно сработать.
Если не поможет, можно обращаться к данным файла Excel, указывая имя файла. Это позволяет получать данные файла Excel в другом файле с макросом.

Комментарий от Alexey — 20.08.2019 @ 15:54