Создайте список из двух столбцов, исключая 0 значений

Это кажется очень простым, но я просто не могу об этом подумать. Итак, у меня есть две колонки. A имеет имена и B имеет значения. Все, что я хочу сделать, это дублировать этот список (в столбцы D и E), исключая только 0 значений в столбце B. (Я приведу пример того, что я ищу ниже)

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

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

Я считаю, что у меня есть версия формулы, которая должна работать. Потяните первую ненулевую строку на D2: E2 (либо вручную, либо с помощью простых формул индекса), а затем используйте следующие в D3 и E3:

D3=IFERROR(INDEX(OFFSET($A$1,MATCH(D2&E2,$A$1:$A$99&$B$1:$B$99,0),0,99),MATCH(TRUE,(OFFSET($B$1,MATCH(D2&E2,$A$1:$A$99&$B$1:$B$99,0),0,99)<>0),0)),"") E3=IFERROR(INDEX(OFFSET($B$1,MATCH(D2&E2,$A$1:$A$99&$B$1:$B$99,0),0,99),MATCH(TRUE,(OFFSET($B$1,MATCH(D2&E2,$A$1:$A$99&$B$1:$B$99,0),0,99)<>0),0)),"") 

Затем автозаполнение в нижней части списка.

Обратите внимание, что это формулы массива, поэтому их необходимо ввести с помощью Ctrl + Shift + Enter. Кроме того, если ваш список длиннее 99, обновите все 99 в формуле до номера, по крайней мере, такого же размера, как длина списка.

Способ, которым это работает, заключается в использовании формулы OFFSET чтобы начать поиск ненулевых значений чуть ниже ранее указанного результата.

Вы можете использовать сводную таблицу. Перетащите цели в строки, Значения в поле значений хорошо. Затем щелкните правой кнопкой мыши любую из целей в сводной таблице и выберите «Фильтр»> «Фильтры значения» и отфильтруйте значение, равное нулю.

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

Когда к листу добавляется больше данных, просто обновите сводную таблицу, которая может быть автоматизирована с помощью VBA.

Вы можете просто использовать формулы, чтобы проверить значение, подобное этому …

 'Column D formula: =IF(E1="","",A1) 'Column E Formula: =IF(B1=0,"",B1) 

Хотя я сомневаюсь, что это то, что вы ищете, поскольку оно просто оставит пустые ячейки в столбцах D и E, если значение в столбце B равно 0.

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

  Sub Check_Values() Application.ScreenUpdating = False Range("D:E").Select Selection.ClearContents Dim name As Integer, val As Integer Dim name1 As Integer, val1 As Integer Dim Emptyrw As Long, Emptyrw1 As Long, Emptyrw2 As Long Dim i As Integer name = 1 val = 1 name1 = 1 val1 = 1 Emptyrw = WorksheetFunction.CountA(Range("B:B")) + 1 Emptyrw1 = WorksheetFunction.CountA(Range("D:D")) + 1 Emptyrw2 = WorksheetFunction.CountA(Range("E:E")) + 1 i = 1 Do While val < Emptyrw If Cells(val, 2) <> 0 Then Range(Cells(name, 1), Cells(val, 2)).Select Selection.Copy Range(Cells(Emptyrw1, 4), Cells(Emptyrw2, 5)).Select Selection.PasteSpecial Application.CutCopyMode = False End If name = name + 1 val = val + 1 name1 = name1 + 1 val1 = val1 + 1 Emptyrw1 = Emptyrw1 + 1 Emptyrw2 = Emptyrw2 + 1 Loop Do While i < Emptyrw1 If Cells(i, 4) = "" Then Range(Cells(i, 4), Cells(i, 5)).Select Selection.Delete Shift:=xlUp End If i = i + 1 Loop End Sub 

Вы можете просто скопировать этот код в свой VBA и назначить ему командную кнопку и работать для вас.

Удачи!

Мои усилия …

Войдите в D2, используя Ctrl + Shift + Enter:

 =INDEX(A:A,SMALL(IF($B$2:$B$20<>0,ROW($A$2:$A$20),999),ROWS(A$2:A2)),1)&"" 

Затем заполните и переверните.

Сделайте «999» достаточно большим, чтобы всегда быть вне списка значений.

Недостаток: добавление «» для предотвращения появления нулей приводит к тому, что числа ведут себя как текст.

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

  • Добавление значений в одном диапазоне ячеек до конца формул в другом диапазоне ячеек
  • Использование SUMIF в Excel
  • ошибка функции журнала excel
  • Excel VBA Autofilter - несколько критериев «начать с»
  • Разница между двумя датами с десятичным результатом
  • Возврат всех возможных значений вместо VLOOKUP
  • Функция Excel VBA Range RandBetween возвращает ошибку
  • Лучшая формула для нескольких продуктов sumproducts
  • Excel: изменить цвет фона ячейки на цвет RGB, записанный в этой ячейке
  • Фильтрация набора данных
  • Экземпляр Excel
  • Interesting Posts

    EXCEL: количество элементов столбца для каждого отдельного элемента в другом

    Вставить столбцы Динамически vba

    Исключение из HRESULT: 0x800A03EC – Запись массива в диапазон в надстройке Excel

    Как обновлять новые строки в таблице доступа из Excel (не связанная таблица), сохраняя старые данные

    Альтернативы VSTO

    Как получить информацию о диаграмме из электронной таблицы Excel с помощью Apache POI?

    Отправка электронной почты с помощью приложения excel, но лист excel поврежден. что я делаю?

    Напишите Excel Addin с VBA, а затем нажмите кнопку, запускающую его

    Excel добавляет url с приращением во всех строках столбца

    Таблица сводных таблиц Excel в подстроках

    Как я могу открыть эту книгу VBA быстрее?

    Поиск книги для строки «datatoFind» за пределами первого вхождения

    Как прочитать конкретные данные из Excel и записать их в другой столбец того же листа. Использование asp.net c #

    Получить excel для создания групп данных на основе значений ячеек

    Раскрашивающая ячейка в соответствии с ограничениями

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