2015.10.14

“AND, OR, NOT, XOR을 이용한 데이터 선별” 엑셀 논리 함수 마스터하기

JD Sartain | PCWorld
엑셀에서 논리 함수는 참(TRUE) 또는 거짓(FALSE)로 대형 스프레드시트에서 특정 데이터나 결과를 가려내는 방법이다. 물론 룩업(Lookup) 함수와 피벗 테이블 등 스프레드시트를 검색하는 다른 방법도 존재한다. 불린 로직을 파헤치는 이유는 이 방법을 검색 엔진이나 데이터베이스 등의 다른 애플리케이션에서 사용할 수 있기 때문이다.

엑셀의 논리 함수 연산자는 AND, OR, NOT 그리고 새로운 함수인 XOR이 있다. 이런 연산자는 검색어 사이에 사용하여 데이터베이스, 스프레드시트, 검색 엔진, 기타 특정 정보를 찾는 경우에 결과를 좁히거나 넓히거나 배제할 수 있다. 이 4가지를 차근차근 살펴보도록 하자.

논리 함수의 기초
각 연산자의 가장 단순한 정의는 다음과 같다.
AND - 지정된 모든 조건이 참인 경우 TRUE를 반환한다.
예: =AND (100<200, 200>100) TRUE 왜냐하면 두 조건이 모두 참이기 때문이다.
OR - 지정된 조건 중 최소 1개가 참이면 TRUE를 반환한다.
예: =OR(100<200, 100>300) TRUE 왜냐하면 조건 중 하나가 참이기 때문이다.
NOT – 지정된 조건이 충족되지 않은 경우 참을 반환한다 (역전 로직)
예: =NOT(100>500) TRUE 왜냐하면100이 500보다 크지 않기 때문이다.
XOR - 또는 Exclusive OR라고도 하며 인수 중 하나가 참이면 참을 반환한다.
예: =XOR(1+1=2, 2-1=2)는 하나의 조건이 참이고 하나의 조건이 거짓이기 때문에 TRUE를 반환한다.
=XOR(1+1=2, 2-1=1)는 두 조건이 모두 참이기 때문에 FALSE를 반환한다.
=XOR(5+1=2, 5-1=2)는 두 조건이 모두 거짓이기 때문에 FALSE를 반환한다.

기타 참고 사항:
· 논리 함수를 통해 결과 범위를 검색하는 경우 초과 또는 미만으로 범위를 정의한다.
· 엑셀 2013에서는 단일 논리 함수에 최대 255개의 인수가 가능하지만 수식이 8,192자를 초과해서는 안 된다.


 

논리 함수 연산자 정의

논리 함수는 처음에는 단순해 보일 수 있다. 하지만 IF 등 다른 함수와 조합하는 경우 매우 강력한 결과를 제공하는 복잡한 수식을 만들 수 있다.

AND, IF-AND
복수의 기준을 충족하는 무엇인가를 찾고자 하는 경우 AND 연산자가 필요하다. 예를 들어, 조지의 연극에 등장하는 배우 중 한 사람이 다리가 부러져 조지는 매우 특정한 기술과 외모를 갖춘 다른 배우가 즉시 필요하다. 옷이 몸에 맞으려면 새로운 사람의 키는 68-69인치에 체중은 180-200파운드이며 나이는 30-50세여야 한다.

조지의 배우 목록에 50-100명만 포함되어 있는 경우 목록을 스캔하여 스스로 대역을 찾을 수 있다. 하지만 길드 액터스(Guild Actors) 데이터베이스는 기록이 2만 개나 되기 때문에 검색을 좁히기 위한 더욱 신속한 방법이 필요하다.

이 쿼리를 위해 다음 3개 수식 중 하나를 사용할 수 있다. 3개 수식 모두 사용 가능하고 유사하지만 AND문은 TRUE 또는 FALSE만 반환할 뿐이다. IF문을 통해 “일치(match)” 또는 “자격 있음(Qualified)” 등의 맞춤형 답변이 가능하다.

<그림 1>의 데이터베이스와 수식을 복사하고 결과를 실험해 보자.
1) (3개 조건으로) AND 연산자를 사용하는 AND 문 : 참 또는 거짓을 반환한다.
=AND(AND(C6>67,C6<70),AND(D6>179,D6<201),AND(E6>29,E6<51)) = TRUE

2) (3개 조건으로) AND 연산자를 사용하는 IF/AND 문 : IF문에서 다음과 같이 진술하기 때문에 Yes 또는 No를 반환한다. 이것, 이것, 이것이 참이면 Yes이고 그렇지 않은 경우에는 No이다.
=IF(AND(AND(C8>67,C8<70),AND(D8>179,D8<201),AND(E8>29,E8<51)),”Yes”,”No”)

3) (3개 조건으로) AND 연산자를 사용하는 IF문 : Yes 또는 No를 반환한다.
이 때, AND문 중 하나가 충족되지 않는 경우 응답은 False를 반환하고 곱셈(*) 결과는 0(False)이다. 이 형식은 구문에 오류가 있는 경우에 자주 나타나며 엑셀이 이를 수정한다 (그 전에 도움이 필요한지 묻는다).
=IF(AND(C10>67,C10<70)*AND(D10>179,D10<201)*AND(E10>29,E10<51),”YES”,”NO”)
참고: 수식에서 각 조건의 구문을 이해하기 위한 노력의 일환으로 엑셀에서 괄호 열림과 괄호 닫힘을 포함하여 셀에 일치하는 수식의 색 코드가 무엇인지 참조하자..

그림 1

OR, AND-OR
첫 번째 데이터베이스 검색에서 1,100명의 배우가 반환되었다. 조지는 결과를 더욱 좁히기 위해 1,100개의 결과에서 2가지 특정한 기술을 질문한다. 이 배우는 반드시 이탈리아어(Italian)나 프랑스어(French)를 유창하게 구사하고 AND 테너(tenor)나 베이스(base)의 발성 범위여야 한다.

이 쿼리에 다음 수식을 사용한다:
=OR(OR(C9="Italian",C9="French"),AND(OR(D9="tenor",D9="base"))) = TRUE

답변이 참이 되기 위해서 배우는 반드시 프랑스어 또는(OR) 이탈리아어를 구사하고(AND) 테너 또는(OR) 베이스여야 한다.

정보가 올바르지 않으면 FALSE 응답이 생성된다.

<그림 2> 데이터베이스와 수식을 복사하고 결과를 실험해 보자. 다시 한 번 수식에서 각 조건의 구문을 이해하기 위한 노력의 일환으로 엑셀에서 괄호 열림과 괄호 닫힘을 포함하여 셀에 일치하는 수식의 색 코드가 무엇인지 참조하자.

그림 2

NOT, NOT-OR
NOT 연산자를 설명하는 가장 간단한 방법은 인터넷 검색과 비교하는 것이다. 예전에 친구였던 잭 러셀의 이름을 입력하여 온라인으로 검색하는 경우 개(dogs)와 강아지([puppies)에 대한 수 백 개의 결과도 함께 도출된다. NOT 연산자를 이용해 “Jack Russell NOT dogs NOT puppies”를 검색하여 개 변수를 제거할 수 있다.

조지는 춤을 추고 다양한 악기를 연주하기 위해 배경 연주자가 일부 필요하지만 피아노(piano)는 필요 없다. 왜냐하면 피아노 연주자는 춤을 출 수 없기 때문이며, 사교 댄스(ballroom dancing)도 필요 없다. 왜냐하면 사람이 아닌 악기와 함께 춤을 추어야 하기 때문이다. 조지는 다시 데이터베이스를 쿼리 처리하고 NOT 피아노 AND NOT 사교 댄스를 지정한다.

이것은 역전 로직이기 때문에 NOT 피아노와 NOT 사교댄스는 FALSE와 같다. 왜냐하면 그는 사교댄스도 원치 않고 피아노도 원치 않기 때문이다. FALSE를 “이 사람은 아니다.”로 생각하자. 또한 기록 3 (Feyd-Rautha)은 기타와 사교댄스를 명시하고 있다. 기타는 좋지만 사교댄스는 아니기 때문에 응답은 FALSE이다. 왜냐하면 조지는 사교댄스를 원치 않기 때문이다 (기타가 괜찮더라도 말이다). 기록 4 (Piter De Vries)도 같은 상황으로 피아노와 왈츠이다. 둘이 아닌 하나만 수용할 수 있기 때문에 둘 다 거부된다.

이 쿼리에 다음 수식을 사용하고 <그림 03>에 나타낸 수식을 복사하여 결과를 실험하자.
=NOT(OR(C5="piano", D5="ballroom"))

그림 3

XOR 또는 Exclusive OR
엑셀 2013은 새로운 연산자 XOR(Exclusive OR)을 선보였다. NOT과 유사한 반대로 생각하자. 하나의 조건이 참이고 하나의 조건이 거짓이면 XOR은 TRUE를 반환한다. 두 조건이 모두 참이거나 두 조건이 모두 거짓이면 XOR은 FALSE를 반환한다.

이 쿼리에 다음 수식을 사용하고 <그림 4>에 나타낸 수식을 복사하여 결과를 실험하자.
=XOR(C5="piano", D5="ballroom")


논리 함수가 익숙해지면 방대한 셀 속에서 특정 기록을 찾는 새로운 기술을 얻게 된다. 게다가 이를 활용해 인터넷 검색, 데이터베이스 검색 등을 개선할 수 있다. editor@itworld.co.kr
 


2015.10.14

“AND, OR, NOT, XOR을 이용한 데이터 선별” 엑셀 논리 함수 마스터하기

JD Sartain | PCWorld
엑셀에서 논리 함수는 참(TRUE) 또는 거짓(FALSE)로 대형 스프레드시트에서 특정 데이터나 결과를 가려내는 방법이다. 물론 룩업(Lookup) 함수와 피벗 테이블 등 스프레드시트를 검색하는 다른 방법도 존재한다. 불린 로직을 파헤치는 이유는 이 방법을 검색 엔진이나 데이터베이스 등의 다른 애플리케이션에서 사용할 수 있기 때문이다.

엑셀의 논리 함수 연산자는 AND, OR, NOT 그리고 새로운 함수인 XOR이 있다. 이런 연산자는 검색어 사이에 사용하여 데이터베이스, 스프레드시트, 검색 엔진, 기타 특정 정보를 찾는 경우에 결과를 좁히거나 넓히거나 배제할 수 있다. 이 4가지를 차근차근 살펴보도록 하자.

논리 함수의 기초
각 연산자의 가장 단순한 정의는 다음과 같다.
AND - 지정된 모든 조건이 참인 경우 TRUE를 반환한다.
예: =AND (100<200, 200>100) TRUE 왜냐하면 두 조건이 모두 참이기 때문이다.
OR - 지정된 조건 중 최소 1개가 참이면 TRUE를 반환한다.
예: =OR(100<200, 100>300) TRUE 왜냐하면 조건 중 하나가 참이기 때문이다.
NOT – 지정된 조건이 충족되지 않은 경우 참을 반환한다 (역전 로직)
예: =NOT(100>500) TRUE 왜냐하면100이 500보다 크지 않기 때문이다.
XOR - 또는 Exclusive OR라고도 하며 인수 중 하나가 참이면 참을 반환한다.
예: =XOR(1+1=2, 2-1=2)는 하나의 조건이 참이고 하나의 조건이 거짓이기 때문에 TRUE를 반환한다.
=XOR(1+1=2, 2-1=1)는 두 조건이 모두 참이기 때문에 FALSE를 반환한다.
=XOR(5+1=2, 5-1=2)는 두 조건이 모두 거짓이기 때문에 FALSE를 반환한다.

기타 참고 사항:
· 논리 함수를 통해 결과 범위를 검색하는 경우 초과 또는 미만으로 범위를 정의한다.
· 엑셀 2013에서는 단일 논리 함수에 최대 255개의 인수가 가능하지만 수식이 8,192자를 초과해서는 안 된다.


 

논리 함수 연산자 정의

논리 함수는 처음에는 단순해 보일 수 있다. 하지만 IF 등 다른 함수와 조합하는 경우 매우 강력한 결과를 제공하는 복잡한 수식을 만들 수 있다.

AND, IF-AND
복수의 기준을 충족하는 무엇인가를 찾고자 하는 경우 AND 연산자가 필요하다. 예를 들어, 조지의 연극에 등장하는 배우 중 한 사람이 다리가 부러져 조지는 매우 특정한 기술과 외모를 갖춘 다른 배우가 즉시 필요하다. 옷이 몸에 맞으려면 새로운 사람의 키는 68-69인치에 체중은 180-200파운드이며 나이는 30-50세여야 한다.

조지의 배우 목록에 50-100명만 포함되어 있는 경우 목록을 스캔하여 스스로 대역을 찾을 수 있다. 하지만 길드 액터스(Guild Actors) 데이터베이스는 기록이 2만 개나 되기 때문에 검색을 좁히기 위한 더욱 신속한 방법이 필요하다.

이 쿼리를 위해 다음 3개 수식 중 하나를 사용할 수 있다. 3개 수식 모두 사용 가능하고 유사하지만 AND문은 TRUE 또는 FALSE만 반환할 뿐이다. IF문을 통해 “일치(match)” 또는 “자격 있음(Qualified)” 등의 맞춤형 답변이 가능하다.

<그림 1>의 데이터베이스와 수식을 복사하고 결과를 실험해 보자.
1) (3개 조건으로) AND 연산자를 사용하는 AND 문 : 참 또는 거짓을 반환한다.
=AND(AND(C6>67,C6<70),AND(D6>179,D6<201),AND(E6>29,E6<51)) = TRUE

2) (3개 조건으로) AND 연산자를 사용하는 IF/AND 문 : IF문에서 다음과 같이 진술하기 때문에 Yes 또는 No를 반환한다. 이것, 이것, 이것이 참이면 Yes이고 그렇지 않은 경우에는 No이다.
=IF(AND(AND(C8>67,C8<70),AND(D8>179,D8<201),AND(E8>29,E8<51)),”Yes”,”No”)

3) (3개 조건으로) AND 연산자를 사용하는 IF문 : Yes 또는 No를 반환한다.
이 때, AND문 중 하나가 충족되지 않는 경우 응답은 False를 반환하고 곱셈(*) 결과는 0(False)이다. 이 형식은 구문에 오류가 있는 경우에 자주 나타나며 엑셀이 이를 수정한다 (그 전에 도움이 필요한지 묻는다).
=IF(AND(C10>67,C10<70)*AND(D10>179,D10<201)*AND(E10>29,E10<51),”YES”,”NO”)
참고: 수식에서 각 조건의 구문을 이해하기 위한 노력의 일환으로 엑셀에서 괄호 열림과 괄호 닫힘을 포함하여 셀에 일치하는 수식의 색 코드가 무엇인지 참조하자..

그림 1

OR, AND-OR
첫 번째 데이터베이스 검색에서 1,100명의 배우가 반환되었다. 조지는 결과를 더욱 좁히기 위해 1,100개의 결과에서 2가지 특정한 기술을 질문한다. 이 배우는 반드시 이탈리아어(Italian)나 프랑스어(French)를 유창하게 구사하고 AND 테너(tenor)나 베이스(base)의 발성 범위여야 한다.

이 쿼리에 다음 수식을 사용한다:
=OR(OR(C9="Italian",C9="French"),AND(OR(D9="tenor",D9="base"))) = TRUE

답변이 참이 되기 위해서 배우는 반드시 프랑스어 또는(OR) 이탈리아어를 구사하고(AND) 테너 또는(OR) 베이스여야 한다.

정보가 올바르지 않으면 FALSE 응답이 생성된다.

<그림 2> 데이터베이스와 수식을 복사하고 결과를 실험해 보자. 다시 한 번 수식에서 각 조건의 구문을 이해하기 위한 노력의 일환으로 엑셀에서 괄호 열림과 괄호 닫힘을 포함하여 셀에 일치하는 수식의 색 코드가 무엇인지 참조하자.

그림 2

NOT, NOT-OR
NOT 연산자를 설명하는 가장 간단한 방법은 인터넷 검색과 비교하는 것이다. 예전에 친구였던 잭 러셀의 이름을 입력하여 온라인으로 검색하는 경우 개(dogs)와 강아지([puppies)에 대한 수 백 개의 결과도 함께 도출된다. NOT 연산자를 이용해 “Jack Russell NOT dogs NOT puppies”를 검색하여 개 변수를 제거할 수 있다.

조지는 춤을 추고 다양한 악기를 연주하기 위해 배경 연주자가 일부 필요하지만 피아노(piano)는 필요 없다. 왜냐하면 피아노 연주자는 춤을 출 수 없기 때문이며, 사교 댄스(ballroom dancing)도 필요 없다. 왜냐하면 사람이 아닌 악기와 함께 춤을 추어야 하기 때문이다. 조지는 다시 데이터베이스를 쿼리 처리하고 NOT 피아노 AND NOT 사교 댄스를 지정한다.

이것은 역전 로직이기 때문에 NOT 피아노와 NOT 사교댄스는 FALSE와 같다. 왜냐하면 그는 사교댄스도 원치 않고 피아노도 원치 않기 때문이다. FALSE를 “이 사람은 아니다.”로 생각하자. 또한 기록 3 (Feyd-Rautha)은 기타와 사교댄스를 명시하고 있다. 기타는 좋지만 사교댄스는 아니기 때문에 응답은 FALSE이다. 왜냐하면 조지는 사교댄스를 원치 않기 때문이다 (기타가 괜찮더라도 말이다). 기록 4 (Piter De Vries)도 같은 상황으로 피아노와 왈츠이다. 둘이 아닌 하나만 수용할 수 있기 때문에 둘 다 거부된다.

이 쿼리에 다음 수식을 사용하고 <그림 03>에 나타낸 수식을 복사하여 결과를 실험하자.
=NOT(OR(C5="piano", D5="ballroom"))

그림 3

XOR 또는 Exclusive OR
엑셀 2013은 새로운 연산자 XOR(Exclusive OR)을 선보였다. NOT과 유사한 반대로 생각하자. 하나의 조건이 참이고 하나의 조건이 거짓이면 XOR은 TRUE를 반환한다. 두 조건이 모두 참이거나 두 조건이 모두 거짓이면 XOR은 FALSE를 반환한다.

이 쿼리에 다음 수식을 사용하고 <그림 4>에 나타낸 수식을 복사하여 결과를 실험하자.
=XOR(C5="piano", D5="ballroom")


논리 함수가 익숙해지면 방대한 셀 속에서 특정 기록을 찾는 새로운 기술을 얻게 된다. 게다가 이를 활용해 인터넷 검색, 데이터베이스 검색 등을 개선할 수 있다. editor@itworld.co.kr
 


X