Как создать новое поле параметров в фильтре Excel и применить один фильтр для нескольких столбцов в Excel?

Question

У меня есть рабочий лист Excel. В столбце A находится список объектов (около 50); в других столбцах перечислены характеристики этих объектов. Столбцы E, F, G имеют для меня особое значение. Они содержат числовые значения, такие как 1, 7и т. д., числовые и буквенные значения, такие как 16B, 17C, а также есть разделенные запятыми значения, которые содержат по крайней мере пару числовых и/или числовые и буквенные значения, например 24, 13B, 15Gили 17A, 20B, 16. В этих столбцах также есть несколько пустых ячеек. Вот принтскринрабочий лист.

Я хотел бы использовать фильтры по столбцам E, F, G.

Вот как я хотел бы, чтобы были фильтрыбыть.

И вот как они сейчасявляются.

Например, я хотел бы отфильтровать столбец F по числовому буквенному значению, такому как '17C'. В идеале, то, что останется, будет ячейками, содержащими '17C', например, 17C, 17C, 24A, 16B, 17B, 17C, 24A, 24F, и т. д.

Также я хотел бы отфильтровать столбец F по числовому значению, например '6'. В идеале, то, что останется, будет ячейками, содержащими '6', например 1, 2, 3, 6, 12, 13, 15, 20, 6, но не 6E, 7C, 13C, ни 6A, ни 16B, 17A, 19C.

Я хотел бы иметь возможность фильтровать по всем возможным значениям, используемым в столбцах, т. е. по 1, 1B, 2, 2D, 2E, … , 20, 24A, 24F, и т. д.

Я хотел бы применить такую ​​фильтрацию к столбцам E, F и G.

Было бы замечательно, если бы я мог обойтись без вспомогательных колонок. Но если это невозможно без них, то я бы с радостью ими пользовался.

Надеюсь, мне удалось ясно выразить свою борьбу.

Answer 1

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

Теперь, когда я прочитал ваши обновленные данные, попробуйте выбрать все три столбца, Условное форматирование, Правила выделения ячеек, Текст, содержащий «13».

Answer 2

Я никогда не слышал о такой возможности, встроенной в Excel. Однако я построил решение, которое — я считаю — дает желаемые результаты.

Предположим, что расположение столбцов, указанное вами в вопросе, верно:

• В ячейке I2введите формулу=OR(AND(E2=$H$1,E2<>""), LEFT(E2, LEN($H$1)+1)=($H$1&","), RIGHT(E2, LEN($H$1)+1)=(" "&$H$1), NOT(ISERROR(SEARCH(" "&$H$1&",", E2)))) =OR(AND(E2=$H$1,E2<>""), LEFT(E2, LEN($H$1)+1)=($H$1&","), RIGHT(E2, LEN($H$1)+1)=(" "&$H$1), NOT(ISERROR(SEARCH(" "&$H$1&",", E2))))

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

• Перетащите/заполните ячейку I2вправо, в ячейку K2. Теперь формула в J2проверяет Столбец  F и  K2проверяет Столбец  G.

  Вспомогательные столбцы можно размещать где угодно. Вы можете разместить указанные выше формулы в Columns  X, Yи  Zесли хотите — или AE, AFи AG.

• В ячейке H2введите =OR(I2:K2). Очевидно, если вы переместили вспомогательные столбцы I- J- K, отрегулируйте это так, чтобы они совпадали.
• Выберите ячейки вспомогательных столбцов ( H2по K2или там, где вы их разместили) и перетащите/заполните их вниз, чтобы охватить все строки с данными.
• Фильтр столбцов H.

Сейчас,

• Введите буквенно-цифровое значение, например 1B,  17A или  24, в ячейку H1.
• Активируйте фильтр по столбцу, Hчтобы отобразить толькоистинныйзначения. Теперь вы увидите только те строки, где Column  или Fсодержит введенное вами значение  .GHH1

Нефильтрованное:

Отфильтровано:

ОК; формула во вспомогательных столбцах (описанных выше как I, Jи  K) выглядит так

=ИЛИ(выражение 1,выражение 2,выражение 3,выражение 4)

где четыре выражения:

• AND(E2=$H$1,E2<>""). Это проверяет, является ли значение в Column  E просто равным значению в H1(но не пустым). Например, это будет TRUE, E3если значение в H2равно 24.
• LEFT(E2, LEN($H$1)+1)=($H$1&","). Это проверяет, начинается ли значение в Column  Eсо значения в  H1. Точнее, начинается ли значение в Column  E со значения в ,  H1за которым следует запятая. Так, например, для F3этого будет TRUE для , 16B но FALSE для 1или 16.

• RIGHT(E2, LEN($H$1)+1)=(" "&$H$1). Зеркальное отражение вышеизложенного; это проверяет,  Eзаканчивается ли значение в Column значением в  H1. Точнее,  Eзаканчивается ли значение в Column значением в ,  H1 которому предшествует пробел. Так, например, для F3это будет ИСТИНА для ,  19C но ЛОЖЬ для 9Cили  C.

  Обратите внимание, что три приведенных выше теста нечувствительны к регистру, поскольку в Excel он нечувствителен.string1 = string2

• NOT(ISERROR(SEARCH(" "&$H$1&",", E2))). Это проверяет, содержит ли значение в Column  Eзначение в  H1. Точнее,  Eсодержит ли значение в Column значение в ,  H1 которому предшествует пробел, а после него запятая. Это нечувствительно к регистру, поскольку функция SEARCHнечувствительна к регистру.

Таким образом, формула в I2проверяет, содержит ли значение в каким-либо образом E2значение в .H1

Таким образом, формула в H2проверяет, содержит ли какое-либо значение в строке 2 (Столбцы  E, Fили G) значение в H1.

---

Если вы хотите создать список буквенно-цифровых значений в столбцах  Eи F(  G например,  1B, 2D, 2E, 5C, 24, 16B, 17A, 19Cи т. д.), это можно сделать, но я не буду этого делать сейчас.