Продвинутый (?) Фильтрация Excel

Question

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

Вот что я пытаюсь сделать:

У меня очень длинная таблица. Всего 7 столбцов, но больше всего меня интересуют только два. Вот пример CSV, который намного проще, чем мой фактический набор данных, но поиск / сортировка аналогичны:

John, Apple
Dave, Apple
Dave, Orange
Steve, Apple
Steve, Orange
Steve, Kiwi
Bob, Apple
Bob, Banana

Я заинтересован в извлечении целых строк (всех столбцов), которые соответствуют следующим критериям:

["Apple"] ИЛИ ["Apple" и "Orange"] НЕ ["Apple" и "Orange" и все остальное ] НЕ ["Apple" и все, что не является оранжевым ]

Так что с вышеупомянутым CSV я бы получил все строки для Джона и Дейва, но не Стив и не Боб.

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

Спасибо!

Answer 1

Таким образом, в основном количество записей Apple и Orange для человека должно быть равно количеству всех записей для человека. Поэтому вы можете сделать это:

1. Добавьте новый столбец где-нибудь в вашей таблице.
2. Введите формулу =A2&"###"&B2 где A2 - первая ячейка, содержащая имя, B2 - первая ячейка, содержащая фрукт, а ### - разделитель, который не будет использоваться ни в имени, ни во фрукте. колонка. (Если там используется ### , просто замените его чем-то другим, чего нет в этих столбцах.)
3. Перетащите эту формулу через весь столбец.
4. Добавьте еще один столбец где-нибудь в вашей таблице.
5. Вставьте формулу =(COUNTIF($C:$C,$A2&"###Apple") + COUNTIF($C:$C,$A2&"###Orange")) = COUNTIF($A:$A,$A2) где C - это столбец, который вы только что добавили на шаге 1, A - это столбец с именем, а ### - тот же заполнитель, который вы использовали на шаге 2.
6. Перетащите эту формулу также на весь столбец.

Результат: все "недопустимые" строки должны содержать "FALSE", все "допустимые" строки должны содержать "TRUE".

Предупреждение: после того, как вы начнете удалять записи, "ЛОЖЬ" может превратиться в "ИСТИНА", поэтому вам может потребоваться создать бумажную копию всего столбца перед выполнением какого-либо редактирования данных.

НТН.

Answer 2

Вот метод, использующий три вспомогательных столбца. Идея состоит в том, что вы можете отфильтровать строки со значением 1 в третьем вспомогательном столбце, чтобы получить нужные вам строки.

Первый вспомогательный столбец (столбец C в моем примере): дает количество записей "Apple" для этого человека.

=COUNTIFS($A$1:$A$8,A1,$B$1:$B$8,"Apple")

Второй вспомогательный столбец (столбец D): дает количество записей для этого человека.

=COUNTIF($A$1:$A$8,A1)

Третий вспомогательный столбец (столбец E): возвращает 1 для людей, соответствующих вашим критериям.

=IF(D1=1,IF(C1>0,1,0),IF(AND(D1=2,C1=1),IF(SUMPRODUCT(($A$1:$A$8=A1)*($B$1:$B$8="Orange"))=1,1,0),0))

Затем просто отфильтруйте в течение 1 с в столбце E

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