Автоматически дублировать сводный столбец, но исключить определенные значения?

Question

У меня есть столбец, который я хотел бы скопировать на новый лист. Столбец можно отфильтровать:

Я хочу продублировать все в этом столбце, кроме ячеек «Разрешено» (1-й вариант в раскрывающемся меню фильтра). Это необходимо сделать автоматически, так что если я изменю данные в столбце или добавлю новые данные, дубликат столбца мгновенно отобразит изменения. И это должно произойти без макросов, потому что они работают слишком медленно на компьютерах, которые будут запускать этот файл.

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

Answer 1

Ситуация 1: Старые записи с Утверждено в столбце.

Ситуация 2: Новые записи с Утверждено в столбце вместе с Скопированными / Извлеченными записями.

NB

• Red Cells - это Criteria где 1 - это Column Offset for Col B in Sheet 1 а « Approved - это Value to match .
• При 2nd Screen Shot новые записи отображаются Green Color .
• Чтобы сделать формулу динамичной, я использовал a Cell for the Criteria .

Как это устроено:

• На листе 2 заполните ячейку A1 Column Offset position и B1 критериями.

• Формула массива в ячейке листа 2 A3 , завершите формулу с помощью Ctrl+Shift+Enter и Fill Down с несколькими дополнительными строками.

   {=IFERROR(INDEX(Sheet1!$B$4:$B$9, SMALL(IF((INDEX(Sheet1!$B$4:$B$9, , $A$1)<>$B$1), MATCH(ROW(Sheet1!$B$4:$B$9), ROW(Sheet1!$B$4:$B$9)), ""), ROWS(A3:$A$3)), COLUMNS($A$1:A1)),"")}
  

Отредактировано:

(INDEX(Sheet1!$B$4:$B$9, , $A$1) становится,

INDEX($B$4:$B$9, , 1) и возвращает B4:B9 .

(INDEX($B$4:$B$9, , $A$1)<>$B$1) возвращается,

{1;1;0;1;1;0} для ситуации 1 & для ситуации 2 {1;1;0;1;1;0;1;0;1}

который наконец возвращается,

({Ok;No;Fine;One}<=$B$1) для ситуации 1

а также

({Ok;No;Fine;One;Four;New}<=$B$1) для Ситуации 2.

MATCH часть выглядит как ,,

IF({1;1;0;1;1;0}, MATCH(ROW($B$4:$B$9), ROW($B$4:$B$9)), "") & IF({1;1;0;1;1;0;1;0;1}, MATCH(ROW($B$4:$B$9), ROW($B$4:$B$9)), "")

становится,

IF({1;1;0;1;1;0}, {1;2;3;4;5;6}, "") & IF({1;1;0;1;1;0;1;0;1}, {1;2;3;4;5;6;7;8;9}, "")

и возвращается,

{1;2;"";4;5;""} & {1;2;"";4;5;"";7;"";9}

и небольшая часть возвращается,

SMALL({1;2;"";4;5;""},1) & {1;2;"";4;5;"";7;"";9}, 1) и возвращает 1 .

Обратите внимание, что для лучшего управления данными индексированный диапазон B4:B9 на листе 1 также должен быть динамическим именованным диапазоном, который позволяет избежать изменения индексированного диапазона и расширяется вниз на столько строк, сколько есть новых записей. ,

Выполните следующие шаги, чтобы создать динамический именованный диапазон.

• Нажмите Ctrl+F3 чтобы открыть диалог диспетчера имен.
• Найти и нажать кнопку « Создать» , вы увидите диалог показа ниже.

• Присвойте имя по вашему выбору, как я сделал, имя образца - Mydata .

• Скопируйте и вставьте приведенную ниже формулу в текстовое поле « Относится к» . =OFFSET($B$1,0,0,COUNT($B:$B),1)

• Готово с ОК .

NB

• Тогда после вы можете заменить Sheet1!$B$4:$B$9, с Mydata в показанной выше основной формуле.

• Помните, что использование динамического именованного диапазона является необязательной частью.

Answer 2

Если вам не нужно, чтобы данные были "сжаты", чтобы скрыть пустые ячейки, есть тривиальное решение. Допустим, ваши данные находятся в столбце А листа 1, начиная со строки 2. В Лист2!A2, используйте формулу, например:

=IF(OR(ISBLANK(Sheet1!A2),Sheet1!A2="Approved"),"",Sheet1!A2)

Повторите это вниз по колонке, насколько вам может понадобиться. Это просто воспроизводит исходное значение ячейки, если оно не "Утверждено" или не заполнено, и в этом случае оно выдает нулевое значение. Это немедленно отразит любые изменения на Листе 1, и любые предварительно заполненные ячейки, указывающие на неиспользуемые ячейки на Листе 1, будут пустыми.

Если вы хотите скрыть пустые ячейки на Sheet2, это сложнее. Вы можете использовать фильтр и отменить выбор пустых ячеек. Однако фильтр не обновляется при пересчете листа; вам нужно принудительно повторно применить фильтр.

Вы можете сделать это вручную одним нажатием клавиши. Данные листа 2 уже будут правильными, скрытие пустых ячеек является косметическим. Так что это потенциально тривиальное решение.

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

Но если вам нужна фильтрация, и вам нужна автоматизация, приведенная выше формула является пустой тратой. С тем же успехом можно предварительно заполнить Sheet2 ссылками на ячейки Sheet1. Затем отфильтруйте, чтобы скрыть как пустые ячейки, так и "одобренные". Повторное применение фильтра с макросом не будет сложной задачей.