Excel - Formula - If Statement

Muszę stworzyć formułę, która powie mi, czy pożyczka ma co najmniej 1 otwartą sprawę.

Mam numery kredytów w kolumnie "A", istnieje wiele duplikatów z powodu kolumny "B", które mogą odzwierciedlać kilka spraw otwartych lub zamkniętych.

Loan    Case Status
1234    Open
1234    Open
1234    Closed

4567    Closed
4567    Closed

W tym przykładzie muszę być w stanie zidentyfikować, że pożyczka 4567 jest problemem, ponieważ jest zamknięta i powinna mieć co najmniej 1 Open Matter. Gdzie jako pożyczka 1234, gdy ma zamkniętą sprawę, jest ok, ponieważ ma co najmniej 1 Otwartą sprawę, w tym przypadku 2, co jest w porządku.

microsoft-excel

— Jturnbow1
źródło

Zasadniczo w przypadku każdej Pożyczki powinny one mieć co najmniej jeden status "Otwartego"?

— BruceWayne

Czy musisz utworzyć główną listę unikatowych numerów kredytowych jako tabelę w innym miejscu tego samego arkusza, a następnie w odniesieniu do każdego unikatowego numeru pożyczki odzwierciedlać stan w oparciu o wymienione kryteria?

— patkim

Bruce - Tak, muszę zobaczyć, że mają co najmniej 1 status otwartej skrzynki.

— Jturnbow1

@BruceWayne - Tak, o ile istnieje co najmniej 1 otwarta sprawa. Zasadniczo są one na liście, ponieważ Hold został zatwierdzony. Tak więc, jeśli wstrzymanie jest zatwierdzone przez kolumnę D, wówczas musi istnieć minimalna 1 otwarta sprawa w kolumnie B. Jeśli kolumna B odzwierciedla tylko przypadki zamknięte, to jest to, co muszę zgłosić, ponieważ nie powinno być "TAK" w zatwierdzone pole. Mam nadzieję, że ma to sens?

— Jturnbow1

Odpowiedzi:

Nie wprowadzając żadnych zmian w danych, możesz skonfigurować dwie dodatkowe kolumny "pomocnika".

Zakładając, że Twoje dane zaczynają się w A1, umieść tę formułę w kolumnie, aby uzyskać unikalny identyfikator. (Używam kolumny D, formuła zaczyna się w D2):

=INDEX(loanID,MATCH(0,COUNTIF($D$1:D1,loanID),0))
Wprowadź za pomocą CTRL + SHIFT + ENTER i przeciągnij w dół.

Następnie, obok tego, możesz zrobić =IF(D2<>0,IF(COUNTIFS(loanID,D2,caseStatus,"Open")>0,"Has Open","No Open Status!"),"")

Uwaga: Może to być nieco łatwiejsze, jeśli nie masz pustych wierszy, które dzielą dane, lub , skopiuj kolumnę pożyczki, umieść w D, następnie uruchom "Usuń duplikaty" na tym. To pozbędzie się tego błędnego 0 w kolumnie "Pożyczka unikatowa". Wtedy nie potrzebujesz tego If(D2<>0 trochę If formuła.

— BruceWayne
źródło

Skopiuj całą pożyczkę do innej kolumny G i usuń duplikaty
W H2 (G2 twoja pierwsza pożyczka) napisz:

=IF(COUNTIFS($A$2:$A$12,G2,$B$2:$B$12,"Open")>=1,"Open","All Closed")

Countify zliczają pożyczkę równą G2, a status sprawy jest otwarty
Jeśli odpowiedź wynosi 1 lub więcej, oznacza to, że masz Otwarte
Jeśli odpowiedź jest mniejsza, oznacza to wszystko zamknięte
If da ci Open lub All Closed
A2: A12 to kolumna Pożyczka (możesz użyć $ A: $ A)
B2: B12 to kolumna Case (możesz użyć $ B: $ B)

Jeśli wolisz mieć kolumnę C w tych samych danych co Pożyczka i Case, możesz użyć w C2:
=IF(COUNTIFS($A:$A,A2,$B:$B,"Open")>=1,"Open","All Closed")

W C3 napisz:

=IF(OR(A3=A2,A3=""),"",IF(COUNTIFS($A:$A,A3,$B:$B,"Open")>=1,"Open","All Closed"))
i przeciągnij w dół

— yass
źródło