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

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

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

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

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

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

 

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

 

Sub CopyFilledRows()
    On Error Resume Next
    r = 1
    With Sheets(2)
        For Each c In Intersect(Selection, ActiveSheet.UsedRange).Cells
            If c <> "" Then
                .Cells(r, 1) = c
                r = r + 1
            End If
        Next
    End With
End Sub

 

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

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

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

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

 

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

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

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

 


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  8. Большое спасибо за макрос! Находка! В книге было несколько листов, таблица была наверное на третьем-четвертом, нажимал выполнить макрос, но ничего не происходило, а потом понял, что данные-то должны появиться на втором листе))) а не на следующем за таблицей

    Комментарий by Дмитрий — 11.02.2021 @ 10:42

  9. Спасибо огромное!
    Сэкономил уйму времени!

    Комментарий by Вячеслав — 11.01.2022 @ 16:05

  10. Спасибо большое! На MacOS тоже все работает! Такая простая задача и excel не умеет по чайниковски это делать! Сэкономил часы своей жизни! Потрачу это время с умом!

    Комментарий by Анатолий — 18.02.2023 @ 12:13

RSS feed for comments on this post. TrackBack URL

Leave a comment






MarkiMarta.ru. Записки отца-программиста" с 2009 г.
Категория информационной продукции 18+
Яндекс.Метрика