함수

Excel – 수식 : Indirect, Address

안녕하세요, 채랑이 아빠 프리아입니다. 오늘은 조금 기분이 별로인 하루입니다….. 고객사로부터 오늘가지 자료 제출해 달라고 해서 아침부터 급하게 자료를 만들기 시작해서 점심때쯤 마무리 단계에 다달았는데 갑자기 연락이 와서 필요없다네요….. 이게 몬가요 ㅠ.ㅠ 급하게 하느라고 다른일도 못하고 있었는데… 에혀 여담은 여기까지만 하고, 오늘은 Indirect 함수와 Address 함수에 대해서 설명드릴께요.어떻게 보면 많이 쓸일이 없을지도 몰라요.하지만 잘 사용하기만 한다면 무지 좋은 기능을 가지고 있기도 합니다.(제 생각입니다 ㅋ) Indirect 함수는 임의의 글자를 셀이름으로 변환해 주는 명령어 입니다.무슨 뜻인지 조금 애매하지요? ㅎㅎㅎ기본적으로 엑셀에서 다른 셀을 참조하려면 다음과 같이 함수를 사용합니다. =A1 그러면 A1의 데이터를 가져오는 형식이죠.그런데, C1셀에는 A가 D1셀에는 1이 들어가 있는 상태에서, 다음과 같은 식을 써 넣으면 어떻게 될까요? =C1&D1 정답은 “A1″이라는 글자가 당겨오게 되어 있습니다.만약 저렇게 조합해서 A1셀의 데이터를 가져오고 싶다면 어떻게 해야 할까요?네 맞습니다. 이럴때 사용하는 함수가 Indirect 입니다. =INDIRECT(C1&D1,TRUE) 이렇게 입력을 하면 C1&D1의 조합이 A1이기 때문에 A1에 있는 데이터를 가져오는 것이죠.저기 보이는 마지막의 TRUE는 무엇인가 하면, A1스타일이라는 것입니다. ㅎㅎㅎ즉 우리가 부르는 셀의 이름 스타일을 True로 정의하고 있습니다.반대로 False는 R1C1 스타일이라고 하는데요, 행번호-열번호 스타일입니다.즉 R1C1은 1번행의 1번열이므로 A1이 됩니다. 즉, 각 셀을 번호로 호출하게 하는 것입니다. 위의 내용을 정리하면 다음과 같이 됩니다. 어떻게 이용할지는 아직 잘 모르시겠죠? 이용방법은 Address 명령어를 설명드리고, 응용하는 방법을 설명드리겠습니다. Address 함수는 Indirect의 R1C1 스타일과 같다고 생각하시면 됩니다.다만, R과 C라는 글자를 넣지 않고 행번호, 열번호로 이루어진다는 것이 차이점입니다.다양한 사용법이 있지만 Address를 많이 쓰지 않기 때문에 단순 사용법만 알려드릴께요.(사실 Address 함수는 Indirect명령어와 같이 쓰지 않으면 큰 의미는 없는데, 다른 사용법을 쓰실거면 그냥 Indirect명령어만 쓰는것이 더 낫습니다.) =ADDRESS(row_num, column_num) 상기와 같이 입력하면 되며, Row1, Column 1을 입력하면 $A$1 이라는 값을 리턴합니다.즉, 주소의 절대참조가 지정되는 것이지요.그리고 말 그대로 주소가 리턴되기 때문에 주소에 대한 결과가 리턴되는 것은 아닙니다.그래서 제대로 사용하려면 Indirect 명령과 혼합해서 사용해야 합니다. =INDIRECT(ADDRESS(1,1),TRUE) 이렇게 말입니다.예제는 다음과 같습니다. 자 이제 이걸 어디에 쓸것이냐 하면, 바로 Match, Index 명령어에 사용할 예정입니다.궁금하신가요? ㅎㅎㅎ일단 도서용 검색을 위한 프로그램이라고 가정하고 다음과 같이 사용할 수 있습니다. 별건 아니고, 일단 검색 기준과 출력 내용을 정하여 결과물을 출력하는 내용입니다.보시면 책 제목 “고양이 해결사 깜냥 2″의 출판사를 물어본 결과입니다.(고양이 해결사 깜냥은 우리 채랑이가 좋아하는 책입니다. ㅎㅎ TMI) 보다시피 Match, Index, Indirect, Address 4개의 명령어를 잘 사용하면 위와 같은 검색 프로그램을 만들 수가 있습니다. 정말 다양한 검색을 하기위해서는 추가로, 수식이 필요하겠지만 단순한 검색용도로는 충분합니다.대충 감이 잡히시나요? 한번씩 해보시면 쉽게 이해하실 수 있으실 거에요.어쩌면 많이 사용할 일은 없겠지만, 그래도 하나씩 알아두면 나쁘지 않을 것 같습니다. 자 오늘도 여기까지 쓰겠습니다. 좋은 하루 되세요 ^^

Excel – 수식 : Indirect, Address 더 읽기"

Excel – 수식 : Rand 랜덤 데이터의 생성

안녕하세요, 채랑이 아빠 프리아입니다. 그냥 이전 글 쓸대 생각 난 김에 랜덤 수치를 만드는 것을 설명 드리겠습니다. 많은 분들이 알고 계시겠지만, 모르시는 분들도 있으니 그냥 참고만 하시면 되겠습니다. 일단 수식은 Rand이며, 사용하는 방법은 다음과 같습니다. =RAND() 무언가 허무합니다. ㅎㅎㅎ아무런 인수도 없이 그냥 RAND()가 명령어의 전부입니다. 저렇게 입력하시면 0~1 사이의 임의의 숫자가 적용됩니다.그리고 다른 데이터를 손댈때마다 숫자가 변경됩니다. 자 그럼 설명 끝내겠습니다…. 라고 하면 웃기겠지요? ㅎㅎㅎ 그러고 싶은데….. 일단은 확장하여 사용하는 방법을 더 설명드리도록 하겠습니다. Rand 명령어는 반드시 0~1사이의 숫자가 적용되게 되어 있습니다. 여기에는 0고 1은 포함되지 않습니다. 즉 0.00000000……1에서 0.999999999….. 의 데이터가 생성되는 것이지요.여기서 문제는 흔히 우리는 1이하의 숫자를 랜덤으로 사용하는 경우는 극히 드뭅니다. 자연수 이상의 수치를 많이 사용하게 되죠.그래서 여기에 일부 변형이 필요하게 됩니다. 방법은 그렇게 어렵지 않습니다. 만약 0~100 사이 숫자를 랜덤하게 만들고자 한다면 다음과 같이 사용합면 됩니다. =RAND()*100 즉, 가장 큰 숫자를 곱하면 되는 것이죠. 다만 여기에 함정이 있다면, 절대 0과 100은 나타나지 않는 다는 것입니다. 네, 0과 1이 안나타나니 100을 곱해도 0과 100은 없습니다. 그리고 나타나는 결과는 소숫점 이하 무한으로 나오게 됩니다.(정말 무한은 아니에요 해보시면 아시겠지만 소숫점 이하 15자리 정도까지 나옵니다.) 만약 0에서 100사이의 자연수가 나오게 하고 싶다면 여기에 추가로 다른 수식을 사용해야 합니다.가장 많이 사용하는 함수는 반올림을 나타내는 Round 함수 입니다.Round함수의 사용 방법은 다음과 같습니다. =ROUND(number, num_digits) number은 숫자이며, num_digits는 소숫점 아래 몇자리까지 표시할지를 정하는 숫자입니다.자연수를 끊는다면, num_digits는 0이 되며, 소숫점 아래 한자리는 1, 두자리는 2…. 이런식입니다.물론 10의 자리이 이후를 끊고 싶다면 -1을 100의 자리에서 끊고 싶다면 -2….. 이런게 사용할 수도 있습니다. 위와 같이 결과가 나오게 됩니다. 자 그러면 0에서 100까지 랜덤 숫자를 만든다면 다음과 같이 사용하면 됩니다. =ROUND(RAND()*100,0) 이렇게 입력을 하면 0과 100의 숫자도 나타나게 됩니다.왜냐하면 0.1~0.4까지는 반올림하면 0이되고, 99.5~99.9까지는 반올림하면 100이 되니까요. 이렇게 응용을 하면 원하는 랜덤숫자를 생성할 수 있습니다. 다만 여기서 유의할 점이 하나 있습니다.위와 같이 수식을 사용할 경우는 그냥 랜덤수치를 나타내기 위한 용도로만 적용가능하다는 겁니다.무슨 말이냐… 동일한 확율을 나타내는 용도로는 사용할 수 없다는 말입니다. 예를 들면 주사위 게임의 수치를 랜덤화 한다고 해 보겠습니다.주사위의 숫자는 1~6의 숫자를 사용하게 되겠죠.정말 쉰게 생각한다면 다음과 같은 수식을 사용할 수 있습니다. =ROUND(RAND()*5,0)+1 여기서 RAND()가 왜 5이고, 마지막에 1을 왜 더하냐고 물으시는 분이 있을 수 있겠죠.만약 6을 곱한다면 0~6의 숫자가 발생하게 됩니다. 0, 1, 2, 3, 4, 5, 6 이 발생하게 되겠죠.하지만 주사위에는 0이 없습니다. 따라서, 랜듬 수치는 0, 1, 2, 3, 4, 5를 만든다음, 1을 더해서 1, 2, 3, 4, 5, 6이 나오게 만들어야 하는 것입니다. 자 여기서 문제점이 무엇인지 아시는 분들도 있겠죠.(물론 모를 수도 있습니다.. 이과가 아니시라면 모를 수 있어요 ^^)확률상으로 1, 6이 나올 확율이 다른 2~5가 나올 확률보다 낮다는 것입니다.차근히 설명해 드리자면 다음과 같습니다.(설명을 위해서 소숫점 2자리까지만 설명드리겠습니다.)1 : 1.00~1.492 : 1.50~2.493 : 2.50~3.494 : 3.50~4.495 : 4.50~5.496 : 5.50~5.99 즉, 소숫점 2자리 기준으로 했을 때, 2~5의 숫자는 나올확률이 100개라고 한다면, 1과 6의 경우에는 나올 확률이 50개 밖에 되지 않습니다. 다른 숫자에 비해서 나올 확율이 낮을 수 밖에 없는 거죠. 여기서 도박 사기가 발생합니다. ㅎㅎㅎㅎ 그래서, 이렇게 동일한 확률이 나와야 하는 경우에는 반올림 함수를 사용하시면 안됩니다.이럴 경우에는 내림(Rounddown), 올림(Roundup), 정수화(Int), 버림(Trunc)의 수식을 사용해야 합니다. Round, Rounddown, Roundup, Trunc, Int의 차이점이 궁금하신 분들은 아래에 연결된 글을 참조해 주세요. ^^ 주사위를 생성하기 위해서는 다음 중 하나를 선택하시면 됩니다. =ROUNDUP(RAND()*6,0)=ROUNDDOWN(RAND()*6,0)+1=TRUNC(RAND()*6,0)+1=INT(RAND()*6)+1 Roundup의 경우 0보다 크고 1보다 작거나 같은 수는 1로 올려버립니다. 그래서 자연스럽게 동일한 비율을 가진 주사위가 됩니다. 마찬가지로, Rounddown, Trunc, Int의 경우 소수점 이하를 버리기 때문에 0~5의 숫자가 만들어지므로 +1을 해줘야 주사위를 완성할 수 있습니다. 그래서 랜덤화 수치를 구성할 때 동일한 발생확율을 만들고자 한다면 roundup, rounddown, trunc의 명령어를 쓰시고 간단하게 하려면 round의 명령어를 쓰시기 바랍니다. Int는 정수화된 랜덤수치를 만들때만 사용하셔야 합니다. 자 이번엔 간단한 글을 완료하였습니다.(솔직히 간단하지 않음… 이글을 보시면 모르시겠지만, 저기 연결된 수식의 차이점 글을 여기에 다 쓰다가 글의 정체성이 흐트러져서 차이점 글을 중간에 하나 다시 쓰고 옮 ㅋㅋ) 오늘도 좋은 밤 되세요 ^^ 그럼 이만…

Excel – 수식 : Rand 랜덤 데이터의 생성 더 읽기"

Excel – 수식 : Round, Roundup, Rounddown, Trunc, Int의 차이점

안녕하세요. 채랑이 아빠 프리아입니다. 음 어쩌다 보니 글을 하나 더 쓰게 되었습니다.랜덤화 데이터 생성을 설명하다가 갑자기 얘기가 너무 이상한 데로 빠지고 있어서 글을 보는 사람들이 헷갈릴 것 같아 중간에 함수의 차이점을 설명하던 내용을 별도의 글로 구분하여 작성해야겠더라구요.그래서 하나의 글이 추가로 생성되었습니다. ㅎㅎㅎ Round, Rounddown, Roundup는 형제 함수라고 할 수 있을 정도로 사용방법이 동일합니다. 그리고 이 Round 3형제는 수학적인 요소의 수식입니다. 반올림, 올림, 내림의 의미입니다. 즉 지정한 자릿수 이하의 숫자를 반올림 할것이냐, 올림할것이냐, 내림할 것이냐를 결정하는 것이죠. 다만, 수학과 다른 것이 있다면 음수의 처리 방법입니다. -1.6을 반올림하면 어떤 숫자가 되어야 할까요? -1일까요, -2일까요? 답은 -2입니다. 수학이나 엑셀의 Round나 동일한 결과가 나옵니다.그럼 -1.6을 올림하면 어떤 숫자가 될까요? 수학적으로는 -1이라는 답을 내게 되나, 엑셀에서 Roundup을 걸면 -2가 되어 버립니다.또, -1.6을 내림할 경우 수학적으로는 -2가 되나, 엑셀의 Rounddown을 걸면 -1이 되어 버립니다.일반 수학과는 의미가 달라져 버리는 거죠. 이건, 사실상 엑셀에서의 오류라고 밖에 볼 수 없습니다.참 황당한 일인 거죠. 혹시나 모르셨다면 참고하시기 바랍니다.(제가 말씀드리는 것은 엑셀 2022버젼입니다. 이전버전에서는 다를 수 있어요..) 자 여기서 버림(Trunc)의 요소를 보겠습니다. Trunc도 Round 3형제와 수식 구조는 동일합니다. 사용한 결과는 Rounddown과 동일합니다.그렇다면 왜 버림이 있는지 이해가 되지 않을 수 있습니다. 왜 똑같은 결과가 나오는데 2개가 구분되어 있는것인가 하는 문제죠.이유는 아주 단순합니다. 위에서 설명한 바와 같이, 올림은 요구하는 요구자리 이하의 숫자가 0보다 크면 큰 숫자를 향해 나아가고, 내림은 요구자리 이하의 숫자를 작은 숫자를 향해 나아가는 형식입니다. 즉 양수의 경우 올림은 0에서 멀어지는 쪽으로 움직이고, 내림의 경우 0에서 가까워는 쪽으로 움직입니다. 반대로 음수에서는 올림의 경우 0에서 가까워지는 방향으로 움직이고, 내림은 0에서 멀어지는 방향으로 움직입니다.예를 들면 위에서 설명한 것처럼, 1.6을 올림하면 2가 되고, 1.6을 내림하면 1이 됩니다. 올림은 0에서 멀어지는 방향으로 갔고, 내림은 0에서 가까워지는 방향으로 갔죠.음수에서는 -1.6을 올림하면 -1이 되고, -1.6을 내림하면 -2가 되어야 합니다. 올림은 0에서 가까워지는 방향으로, 내림은 0에서 멀어지는 방향으로 이동하죠. 그런데 버림의 경우에는 지정된 이하의 자릿수를 그냥 0으로 치환해 버리게 됩니다. 즉 1.6을 버리면 1이 되고, -1.6을 버리면 -1이 되는 것입니다. 쉽게 말하면 양수에서의 버림은 내림과 동일하게 취급되고, 음수에서의 버림은 올림과 동일하게 취급되는 것이죠. 그런데 아까 말한바와 같이 엑셀에서는 Rounddown은 무조건 0으로 가까워지도록 되어 있고, Roundup은 0에서 멀어지도록 설계가 되어 수학적인 올림, 내림이 달라져 버린 것입니다. 그래서 Rounddown과 Trunc 명령어가 똑같아져 버린 것이죠.제대로 되어 있었다면 양수에서는 Rounddown과 Trunc가 동일한 결과를 내고, 음수에서는 Roundup과 Trunc가 동일한 결과를 내야 하는데, 엑셀에서는 양수와 음수 상관없이 Rounddown과 Trunc 가 동일해져 버린 것입니다. 마지막으로 Int의 경우 정수화입니다. 간단하게 소숫점 이하를 버리는 형태입니다.그런데 여기서 황당한 것이 하나가 발생하게 됩니다. 이론적으로 Int는 소숫점 이하를 버리는 것이기 때문에 Trunc와 동일한 결과가 나와야 하지만, 결과는 생각과는 다릅니다.결과론적으로 Int 함수를 쓰게 되면 수학적인 내림의 결과가 나오는 것을 볼 수 있습니다.즉, 1.6을 Int를 걸면 1이 되고, -1.6을 Int를 걸면 -2가 되어 버립니다.황당할 수 밖에 없습니다. 개념적으로 정수화는 버림의 의미를 가지거든요.(뭐 물론 수학에서는 정수화란 단어 자체가 없으니 제가 잘 못 알고 있을 수는 있어요.)어쨋든 Int 함수를 쓰면 해당 숫자보다 작은 수의 정수로 치환된다는 것으로 이해하시길 바랍니다. 자, 여기서는 엑셀 수식을 설명하는 것이므로 수학적 의미는 제외하고, 아래와 같이 정리하고 마무리 하도록 하겠습니다. 자 오늘도 재밌으셨기를 바랍니다. 이만 물러갈께요 ^^

Excel – 수식 : Round, Roundup, Rounddown, Trunc, Int의 차이점 더 읽기"

error: Content is protected !!