Как отфильтровать диапазон с помощью формулы CSE?

Question

У меня есть диапазон A1:C100, ячейки в столбцах A и B могут быть пустыми. Я бы хотел использовать формулу CSE, чтобы отфильтровать A1:C100, оставить только те строки, столбцы A и B которых не пусты, и заполнить их диапазоном D1:F100.

в SQL это было бы просто, например

select A, B, C from [A1:C100] where A!="" and B!=""

но как я могу использовать формулу CSE, чтобы написать это в Excel?

Answer 1

• Выберите вкладку данных
• Выберите из таблицы в разделе «Get & Transform».
• Выберите фильтры "Удалить пропуски", когда будет предложено.
• или же
• Расширенный фильтр: где ColA не равно нулю И ColB не равно нулю, например.

Вы также можете добавить предложение AND в свой логический запрос (например, когда ColA Не равно нулю AND ColB Не равно нулю)

Выберите Загрузить и закрыть. Готово.

Функция «Получить и преобразовать> из таблицы» недоступна в Excel 2010

• Перейдите на вкладку «Данные»> «Сортировка и фильтр»> «Дополнительно».

Обратите внимание на критерии, как они введены.

Answer 2

Посмотрите на этот пример с одним запасным столбцом и тремя формулами массива.

Диапазон A1:B100 заполнен случайными строками, с пустыми ячейками, диапазон C1:C100 - просто некоторые числа (также могут быть строки).

Диапазон H1:H100 используется в качестве вспомогательного столбца, который фактически отфильтровывает и накапливает номера строк с непустыми ячейками в A и B

H1: формула массива для поиска номера первой строки с непустыми ячейками в столбцах A и B:

=MATCH(1,SIGN(LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100))),0)

H2: еще одна формула массива для поиска всех остальных номеров строк с непустыми ячейками в столбцах A и B:

=IF(ROW()>SUM(SIGN(LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100)))),"",MATCH(1,SIGN(LEN(TRIM(INDIRECT(ADDRESS((1+H1),1)&":$A$100")))*LEN(TRIM(INDIRECT(ADDRESS((1+H1),2)&":$B$100")))),0)+H1)

Формула довольно длинная, поэтому на всякий случай, вот она, отформатированная, чтобы показать структуру:

IF( 
  ROW()>
          SUM(
            SIGN(
              LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100))
            )
          )
  ,""
  ,MATCH(1, 
           SIGN(
              LEN(TRIM(INDIRECT(ADDRESS((1+H1),1)&":$A$100")))
             *LEN(TRIM(INDIRECT(ADDRESS((1+H1),2)&":$B$100")))
           )
         ,0
   )+H1
)

Введите последний тип формулы массива в D1:

=IF(ROW()>SUM(SIGN(LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100)))),"",INDEX($A$1:$C$100,$H1,COLUMN()-COLUMN($D$1)+1))

скопируйте ячейку в E1 , F1 ; выберите и скопируйте диапазон D1:F1 ; выберите диапазон D2:F100 и вставьте скопированные ячейки.

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

Обязательное примечание: чтобы ввести формулу массива, нажмите Ctrl+Shift+Enter вместо просто Enter для скалярной формулы).