안녕하세요?
가끔씩이라도 강좌를 올려야 하겠기에... ^^;
예전 msn 커뮤니티 시절에 올려두었던 강좌를 그대로 복사해 왔습니다.
이미 보신분들은 다시 보실 필요가 없겠죠. ^^
엑셀 파일로 다시 작성할까 생각도 했지만...
그냥 올리는 맛도 있을듯... ^^*
모쪼록...
스페셜 강좌 1. 소수(Prime Number)를 판별하는 함수
안녕하세요? 강용림입니다.
배열함수가 어떤것인지 아직 모르시는 분들과 배열함수로 과연 어떤 문제를 풀 수 있을것인가에 대해 의문을 품는 분들을 위해서 본격적인 강좌에 들어가기 앞서 외국뉴스그룹에 소개 되었던 Excel Mvp Bob Umlas 의 함수를 소개합니다. 이해가 가지 않더라도 전혀 걱정하실 필요는 없습니다. 다음 함수를 이해하실 수준이면 이 강좌를 보실 필요도 없을테니까요. ^^*
다음함수는 Bob Umlas 가 만든 함수로써 어떤 수(A1)가 소수인지 아닌지를 판별하여 주는 함수입니다. A1의 수가 소수이면 True 를 소수가 아니면 False 를 출력합니다.
=OR(A1=2,A1=3,ISNA(MATCH(TRUE,A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))=INT(A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))),0)))
A1에 소수인지 아닌지 알고 싶어하시는 수를 넣으시고 결과를 원하는 셀에 위의 함수를 복사해 넣으신 후 Ctrl+Shift+Enter로 마무리 하시면 원하는 결과를 얻으실 수 있습니다. 배열함수는 항상 Ctrl+Shift+Enter로 입력하니 꼭 기억하시기 바랍니다.
일반적으로 어떤 수가 소수인지 아닌지를 판별하는 방법부터 생각해 보죠.
예를들어 10이라는 수가 소수인지 아닌지를 알기위해서 1부터 루트10보다 같거나 작은 자연수로 하나씩 나눠줍니다. 이때 나눠 떨어지는 수가 없으면 소수이고, 그렇지 않으면 소수가 아니죠. 그런데 1은 모든 수를 나눌 수 있으므로 정정하면 2부터 루트 10보다 같거나 작은 자연수로 나눠 떨어지느냐를 따지게 되는겁니다.
A1이 20이라고 가정하고 위 함수를 분석해 보면... (각함수에 대한 사항은 도움말을 참조 하세요. 나중에 함수강좌에서 보다 자세히 다룰것이므로 여기에선 걍 넘어가겠습니다.)
ROW(INDIRECT("2:"&INT(SQRT(A1)))) 이부분은 배열을 생성하는 곳입니다.
배열을 생성할땐 거의 공식처럼 ROW(INDIRECT( )) 함수를 씁니다. 배열함수를 자기것으로 만드실분들은 꼭 기억하셔야 하는 함수의 쓰임새랍니다.
INDIRECT함수안을 살펴보면 "2:"&INT(SQRT(A1)) 으로 되어 있는데요.
SQRT(A1)은 루트20이고 이것은 대략 4.472가 됩니다.
그런데 INDIRECT함수는 어떤 영역을 인수로 받게 되는데
1부터 65536사이의 자연수가 아니면 곤란합니다.
그래서 INT 함수로 정수(여기선 자연수)로 만들어 주는것이죠.
즉 위의 식은 ROW(INDIRECT("2:4"))이 되는것이죠.
INDIRECT("2:4")은 2, 3, 4 행 전체를 의미하게 됩니다.
여기에다 ROW함수를 중첩시키면 행배열 {2;3;4}가 되는 것이죠.
이제 {2;3;4} 라는 배열상수를 생성시킨겁니다.
비로소 배열을 다루게 되는것이니 배열함수가 되는것이고 그냥 ENTER가 아니라 CTRL+SHIFT+ENTER 하는 것입니다.
그럼 좀더 살펴보겠습니다. A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))은
A1/{2;3;4} 로 바뀌는건 위해서 설명을 드렸고,
이 식은 {20/2; 20/3; 20/4} 이므로 {10; 6.7777; 5} 가 되는것이죠.
INT(A1/ROW(INDIRECT("2:"&INT(SQRT(A1)))))
= 오른쪽에 있는 식은 아까의 식에 INT 함수를 중첩시켰습니다.
즉 INT({10; 6.7777; 5} 가 되는것이고 이 결과는 {10; 6; 5} 가 되겠죠.
자 그럼 원식에서 A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))=INT(A1/ROW(INDIRECT("2:"&INT(SQRT(A1)))))
부분을 봅시다. {10; 6.7777; 5} = {10; 6; 5} 가 되는것이 이해가 가시는지요.
여기까지 이해가 가셨으면 거의 다 끝났습니다.
{10; 6.7777; 5} = {10; 6; 5} 이것을 풀면 배열식은 각각에 대해서 적용되어 {10=10;6.7777=6;5=5} 가 되고 이것은 다시 {TRUE;FALSE;TRUE} 가 됩니다.
한단계 더 나아가서
MATCH(TRUE,A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))=INT(A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))),0)
식을 살펴보면...
MATCH(TRUE,{TRUE;FALSE;TRUE},0) 이 되는것을 이해하시겠죠?
MATCH함수의 세번째 인수가 0이면 첫번째 인수가 두번째 인수인 배열에서 일치하는 첫번째 위치를 출력합니다. 즉 1이 되겠죠. 만약 일치하는 같이 없으면 #NA! 에러를 출력합니다.
한단계 더 나아가서 ISNA(지금껏 분석한 식) 는 ISNA(1)이고 1은 NA 에러가 아니므로 FALSE 값을 갖게 됩니다.
한단계 더 나아가면...
OR(A1=2,A1=3,ISNA(...))는 OR(FALSE,FALSE,FALSE) 가 되어 FALSE가 되는것입니다.
만약 A1 이 2도 아니고 3도 아닌 어느 소수(PRIME NUMBER) 라고 가정하면 MATCH(TRUE,{FALSE;FALSE;FALSE},0) 가 되어 NA에러를 출력하고
ISNA(MATCH(...))=TRUE 가 되어 OR(A1=2,A1=3,TRUE),
OR 함수는 하나만 TRUE라도 TRUE를 출력하니까 TRUE 즉 소수라고 출력하는 겁니다.
그럼 왜 앞에 A1=2, A1=3 이라고 했을까요?
만약에 A1=2라고 하죠. 그럼 INT(SQRT(A1))=INT(SQRT(2))=1 이 됩니다.
그럼 위의식에서 A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))=INT(A1/ROW(INDIRECT("2:"&INT(SQRT(A1)))))
부분이 A1/1=INT(A1/1) 이 되어 언제나 TRUE 값을 가지게 되어 위에서와 같은 방법으로는 걸러 낼 수가 없는것이죠. A1=3인 경우에도 똑같은 경우가 됩니다. 즉 SQRT(A1) 이 2 이상이 되어야 하기 때문입니다.
이 함수는 INT(SQRT(A1))가 65536보다 작은 자연수에 대해서 소수를 판단하여 줍니다.
그런데 이 함수는 헛점이 있습니다.
A1에 5.5 와 같이 자연수가 아닌 값을 대입하여도 TRUE값을 가지게 됩니다.
이러한 헛점을 인정하면 위의 함수를 다음과 같이 고쳐써도 역시 어떤 수가 소수인지 아닌지를 판별할 수 있습니다.
=OR(A1=2,A1=3,NOT(PRODUCT(MOD(A1,ROW(INDIRECT("2:"&INT(SQRT(A1))))))=0))
먼저 소개드린 함수보다는 간단하죠. 물론 단점은 있습니다. 그건 여러분이 직접 확인해 보시기 바라며 스스로 완벽한 함수를 만들어 보시는건 어떨까요. ^^*
정리해보면... 일반적인 함수에서는 반복작업을 할 수 없습니다. 그러나 배열함수를 사용하시면 함수내에서 반복작업을 할 수 있죠. 물론 65536 회의 한계는 있지만 잘 생각해 보시면 이 한계를 뛰어넘는 작업도 하실 수 있습니다. 단지 식이 무진장 길고 복잡해지죠. 위함수에서 IF함수는 전혀 사용하지 않았습니다. 전에도 말씀드렸듯이 평소에 IF함수를 쓰지않고 문제를 해결하려는 노력을 하시면 배열함수를 보다 효과적으로 작성하시는데 큰 도움이 됩니다.
에고에고, 순전히 글로만 설명드리려니 넘넘 힘들군요. 직접 워크시트에 써 보이면서 F9를 눌러가면서 설명을 할 수 있으면 좋으련만... 지금은 화일을 업로드 할 수 없고... 다음부터는 적어도 배열함수에 대해서라도 화일로 올려드릴까 합니다.
모쪼록...
에고...
설명중 계산이 틀린곳이 있군요... ^^;;
20/3=6.6666 인데 6.7777 이라고 썼네요...
직접 확인하지 않고, 걍 식만 보고 설명을 했더니...
지송...
모쪼록...
안녕하세요 강용림님
항상 좋은 배열수식을 소개해 주셔서 고맙습니다
강용림님의 소수를 판별하는 식도 아주 명쾌하군요
=OR(a1=2,a1=3,NOT(PRODUCT(MOD(a1,ROW(INDIRECT("2:"&INT(SQRT(a1))))))=0))
a1이 2이거나 또는 a1이 3이면은 소수이고
그리고 a1을 2부터 a1의 제곱근값까지 차례로 나누어서(이것 잘배웠습니다)
이값을 모두 곱했을때 0이면 참인것을
not연산자을 이용 거짓으로 바꾸어준다
그런데 이것도 6은 소수가 아닌데 6.6은 소수가 되네요
if함수을 한번더 사용하면 될것 같군요
강용림님 수식에서 if을 사용하면 중복인수가 7개가 넘어 수식이 입력이 안되어 제가 조금 수정했습니다
=IF(A1=INT(A1),OR(A1=2,A1=3,AND(MOD(A1,ROW(INDIRECT("2:"&INT(SQRT(A1)))))<>0)),"정수가 아닙니다")
a1에 입력된 수가 정수가 아니면 정수가 아니라고 나옵니다
확실히 만들기는 어려워도 수정하기는 조금 쉽네요
그리고 위의 수식은 정수가 4,294,967,296까지만 됩니다
그럼 20000......
안녕하세요? 조성남님...
좋은 식을 주셨는데요. 6.6이 정수가 아니라는 메시지는 필요없을것 같습니다. 소수가 아니면 그만이니까요. ^^*
그리고 제가 배열함수 강좌를 시작하면서 가능한한 if 함수를 사용하지 않겠다고 했거든요. 아래와 같이 하면 똑같은 효과를 얻을 수 있죠. 다음식은 조성남님의 식을 조금 더 변형한겁니다. 제가 강좌를 하기 땜시 될 수 있으면 새로운 함수들을 소개하는 의미에서 product 함수를 사용해 본것이죠... ^^*
=SUM(A1=INT(A1),A1=2,A1=3,AND(MOD(A1,ROW(INDIRECT("2:"&INT(SQRT(A1)))))<>0))>1
참 테스트 안해 보신것 같은데요. 이 함수는 실제로 그렇게 큰 수가 소수인지 아닌지 판단을 못합니다. 원래의 식은 가능하지만... 왜 그런지도 살펴 보시죠... ^^*
모쪼록...
안녕하세요?
위의 식이 실제로 그렇게 큰 수에서 올바른 답을 주지 못하고 에러를 발생하는 이유는 mod 함수 때문이더군요.
mod(a1,b1) 은 a1을 b1으로 나눈 나머지를 구하는 식인데요. a1을 b1으로 나눈 몫이 2^27-1 보다 크면 #NUM! 에러가 나네요. a1, b1에 들어가는 수는 거의 제한을 느낄 수 없을정도로 크더군요.
모쪼록...
추신...
에고에고...
행배열과 열배열이 뒤죽박죽이 되어 있군요. ^^;
새겨서 잘 보시기 바랍니다. ^^*