Копирование и вставка ненулевых строк и соседних ячеек

Я имею дело с большим количеством метаданных ГИС, которые я импортирую в Excel, со многими строками и столбцами пустых или нулевых значений. Я пытаюсь взять данные следующим образом:

(Левая колонка – это имя, столбцы справа – значения, связанные с этим именем)

введите описание изображения здесь

и только выбрав столбцы, у которых есть значение, в итоге появится новый набор таблиц, показывающий ненулевые строки и его соответствующее имя слева:

введите описание изображения здесь

Я попытался сделать это, отфильтровав данные в таблице, чтобы они отображали только ненулевые значения, копируя этот столбец и левый столбец и вставляя его на новый лист. Это легко, если у меня есть только несколько столбцов, но учитывая количество, которое у меня есть, это очень кропотливо. Я должен фильтровать каждый столбец отдельно, потому что строки в каждом столбце могут иметь или не иметь пробелов или нулей в зависимости от столбца.

Можно ли использовать функцию LOOKUP для этого или использовать VBA лучше?

почему нельзя использовать 2 простых цикла (строки, столбцы)? я полагаю, что данные начинаются с «А1», я подсчитываю строки и столбцы и воспроизвожу этот код.

Sub tras() Dim lastRw As Integer Dim lastCol As Integer Dim ResultRow As Integer ResultRow = 20 '1th row for result lastRw = Range("A1").End(xlDown).Row lastCol = Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column Set rgn = Range("A1", Cells(lastRw, lastRw)) For x = 2 To lastCol For y = 1 To lastRw If Cells(y, x) <> "" Then Cells(ResultRow, 1) = Cells(y, 1).Value Cells(ResultRow, 2) = Cells(y, x).Value ResultRow = ResultRow + 1 End If Next y ResultRow = ResultRow + 1 Next x End Sub 

Фильтрация в подобном случае может быть выполнена с помощью формул массива, например, в приведенном ниже примере.

См. Пример: http://i.stack.imgur.com/YlyUN.jpg

(Я еще не могу опубликовать изображения – отредактируйте мой пост, чтобы изображение было интегрировано)

Чтобы использовать его, выберите диапазон E1: E6, напишите показанную формулу и нажмите ctrl + shift + enter.

Логика такова:

1), основанный на диапазоне B1: B6, создает два вектора: 1 с номерами строк каждой строки, которая просто является v1 = {1,2,3,4,5,6} в этом случае и вектором TRUE и FALSE для каждого row с TRUE, если строка непустая, а FALSe – пустой: в этом случае это v2 = {TRUE, FALSE, TRUE, TRUE, FALSE, FALSE}.

2) умножить v1 и v2 на элемент, который дает вектор v3 = {1,0,3,4,0,0}

3) используя значения функции «МАЛАЯ» от наименьшего до самого большого с помощью функции COUNTBLANK, чтобы пропустить все нули (их число равно количеству пробелов). Функция ROW, используемая в диапазоне E1: E6, служит нашим итератором в функции SMALL

4) после пункта 3) вы получите номера строк непустых ячеек на основе диапазона B1: B6, теперь вам нужно вызвать функцию INDEX для извлечения значений из диапазона A1: A6

5) добавьте IFERROR поверх всего, чтобы он возвращался «» при выходе из диапазона входных массивов SMALL

Чтобы назначить значения в столбце F, просто используйте INDEX + MATCH (я призываю всех забыть о * LOOKUP).

То, что вы в конечном итоге здесь, – это то, что, по-моему, касается вашей основной проблемы. Если вы хотите сделать все «перетаскиваемым» или «заполняемым», используйте его в других диапазонах, начиная с 1 или помещая все это в строку настройки после строки, а не после столбца после столбца, вам придется внести некоторые изменения в формулы здесь, но логика будет такой же.

  • Поиск последней используемой ячейки на листе и копирование строки в другой рабочий лист?
  • Почему Excel vba копируется в буфер обмена непоследовательно?
  • Найдите слово, затем скопируйте ячейку рядом с ней в другую книгу Excel с VBA
  • vba Значения вставки Excel: автоматический: ошибка 1004 руководство: ОК
  • VBA для копирования в новую книгу и сохранения
  • VBA копирует диаграмму Excel в Word, так как изображение меняет размер диаграммы
  • Как скопировать ячейки в строке с различной длиной, используя VBA
  • Как я могу запретить Excel объединять данные строки в одну ячейку при вставке?
  • VBA Найти пустые ячейки в заголовках столбцов
  • Excel VBA Macro: проверьте содержимое (из буфера обмена?) Перед вставкой
  • Прокрутите лист, найдите определенное значение, вставьте строку с соответствующим значением на другой лист
  • Interesting Posts

    Код VBA: ошибка времени выполнения '-2147012890 (80072ee6)' Ошибка автоматизации

    Тело отсутствует в первом электронном письме в списке, отправленном с использованием VBA

    Скопируйте определенные элементы HTML с веб-страницы в Excel

    Excel – формула массива в качестве диапазона имен не работает

    Простой Excel VBA – изменить масштаб страницы до значения по умолчанию

    Преобразование hhmmss во времени в Excel

    Заполнение серийных номеров на другом листе

    ошибка при экспорте в данные excel: '', шестнадцатеричное значение 0x07, является недопустимым символом с использованием c #

    Как установить формат столбца Excel с помощью ActiveX из IE

    В Excel, что эквивалентно функции таблицы в R

    Excel: автозаполнение (копирование) целая строка, когда значение вводится в 1-ом столбце

    Горизонтальный поиск по нескольким критериям

    поиск и обновление в одну ячейку

    Запустить макрос VBA (Excel) из командной строки

    Удалить дубликаты Функция не всегда работает в Excel на Mac

    Давайте будем гением компьютера.