rand(life)

"범위&범위"라고 주면 범위끼리 문자열을 합하여 배열을 만든다 예를 들어, 이런 형태의 데이터를  이렇게 정리하기 위해 수식을 J3셀에서는  =INDEX($B$3:$D$14,MATCH($H3&$I3,INDEX($A$3:$A$13&$A$4:$A$14,),0),COLUMNS($J3:J3)) M3셀에서는 =INDEX($B$3:$D$14,MATCH($H3&$I3,INDEX($A$3:$A$13&$A$4:$A$14,),0)+1,COLUMNS($M3:M3)) 이렇게 준다. 여기서 가운데 있는  MATCH($H3&$I3,INDEX($A$3:$A$13&$A$4:$A$14,),0) 이 수식의 의미는 다음과 같다 MATCH($H3&$I3  H3&I3의 값의 위치를 찾는데 H3&I3= 김태희서울   INDEX($A$3:$A$13&$A$4:$A$14,) A$3:$A$13와 $A$4:$A$14의 문자열을 합친 배열 중에서 찾고  김태희서울,장동건수원,유지태인천,아유미일본,유승호부산,유리서울  0  비슷한 글자가 아니라 정확히 일치하는 글자를 찾아라 이 중에서  INDEX($A$3:$A$13&$A$4:$A$14,) 여기를 잘 보면 맨 뒤에 콤마만 있고 그 뒤가 없다 바로 INDEX 함수의 설명 중 다음 사항에 해당한다. 주의 reference와 area_num으로 특정 범위가 선택된 후에는 row_num과 column_num이 특정 셀을 선택합니다. 즉, row_num 1은 범위의 첫째 행이고, column_num 1은 첫째 열의 방식으로 선택됩니다. INDEX 함수로 반환되는 참조는 row_num과 column_num이 교차되는 위치입니다. row_num이나 column_num을 0으로 설정하면 전체 열이나 행에 대한 참조가 각각 반환됩니다. 즉, INDEX함수는 주로 첫번째

엑셀의 도움말에 보면 "엑셀의 바로가기 및 기능키"라는 항목이 있다. 거기 보면 많은 단축키들이 있는데, 그 중에서 개인적으로 유용하다 싶은 몇 개만 소개한다. 1. 전체 선택 Ctrl  * (컨트롤+별표) 현재 선택된 셀에 연결된, 데이타가 있는 셀의 범위를 한번에 선택한다. 지금처럼 데이타가 있을때, B3셀을 선택하고 CTRL * 을 누르면  위와 같이, B3셀과 연결된 직사각형 범위가 선택된다. 대개는 이렇게 군데군데 떨어져 있는 경우가 드물기 때문에, 데이타가 있는 전범위를 선택하는 단축키로 유용하다. 2. 범위의 끝까지 한번에 이동 - Ctrl + 화살표 표가 클때, 마지막 셀까지 한번에 이동하려고 할때 유용하다.  이렇게 500행이 넘는 곳에서 작업을 하다가, 갑자기 1행으로 가고 싶다면, 페이지 업키를 부지런히 눌러서 올라갈 수도 있다. 하지만, 이 상태에서 CTRL + UP(컨트롤+위화살표)를 누르면 한번에 맨 첫행까지 올라간다.  첫행부터 끝행까지 한번에 선택하는 방법에도 응용할 수 있다. 쉬프트키를 누른채로 화살표키를 누르면 그 방향으로 셀들이 연속으로 선택된다. 만약, B1셀에서 CTRL+SHIFT+DOWN(컨트롤+쉬프트+아래화살표)를 누르면? B2:B576의 전범위가 선택된다. (좌우 방향으로 하려면 왼쪽,오른쪽 화살표키를 이용한다) 3. 범위 끝까지 한번에 끌기 - 채우기 핸들 더블클릭 일련번호를 붙일때, 대개는 1과 2는 입력하고, 두 셀을 선택한 후, 오른쪽 아래의 채우기 핸들에 마우스를 클릭하여 원하는 위치까지 끌어채우는 방식을 사용한다. 하지만 채워야할 행이 많으면 마우스로 끌다가 정확하게 마지막 행까지 맞추지 못하고 훨씬 지나쳐 버리는 경험을 많이 해보았을 것이다. 다음부터는 채우기 핸들을 끌지 말고 이 곳을 더블클릭해보라. 깜짝 놀랄 것이다. ^^* 단, 왼쪽이나 오른쪽에 데이타가 채워져 있을 경우에 그 데이타의 끝행까지

오늘 어떤 선생님에게서 전화가 왔습니다. 학생별로 시트가 있고, 그 시트에 학생의 성적이 있는데, 별개의 시트에서 학생의 이름을 입력하면, 그 학생의 이름과 같은 시트를 찾아 그 시트의 특정셀값이 출력되게 할 수 없느냐는 질문이었습니다. 정교하게 짜려면 VBA를 사용해야할 것 같습니다만, 간단히 하려면 다음과 같이 가능합니다.  그림에서처럼, 학생의 이름별로 시트가 별도로 있고, 그 학생의 시트안에는 각 기간별 성적이 들어있습니다. 그리고, 학생의 시트마다 평균 성적을 구해둡니다. 여기서는 7행에 평균성적에 해당하는 셀들이 있군요. "평균"이라는 이름의 시트에 와서, 다음과 같이 수식을 입력합니다. =HLOOKUP(B$1,INDIRECT($A2&"!C1:F7"),7,0) --------답만 알기를 원하신다면 이 이후는 읽지 않으셔도 됩니다.--------- 사실, Kevin학생의 국어평균점수는 이미 Kevin시트의 C7셀에서 구해두었기때문에 =Kevin!C7 이라고 간단하게 구할 수 있습니다. 그런데 굳이 복잡한 함수를 쓴 이유는 뭘까요? 엑셀의 함수를 사용할때는 많은 양의 데이타를 다룬다는 것을 항상 염두에 두어야합니다. 그래서  함수를 아래로, 오른쪽으로 끌어채우기했을때도 제대로 작동할 수 있도록 만들어야합니다 . 그렇지 않고 모든 셀마다 함수를 달리 주어야한다면 엑셀을 사용하는 의미가 없는 것이지요. =Kevin!C7 이라는 수식을 아래로 끌면 오류가 날것입니다. Kevin시트가 아니라 Mike시트에서 셀을 찾아야하는데 그걸 표현해 주지 못하니까요. 따라서 올바른 수식을 만들려면 Kevin이나 Mike와 같은 시트이름을 그대로 사용하지 말고, 수식으로 표현해야 할 것입니다. 또, =Kevin!C7 이라는 수식을 오른쪽으로 끌면 차례대로 =Kevin!D7 =Kevin!E7 =Kevin!F7 의 수식이 만들어질 것이고, 각각 영어, 수학, 과학의 평

지난번 엑셀 강좌의 마지막은 배열수식으로 하려 했습니다만, 저도 잘 모르고, 이것을 실제 써 먹을 수 있는 일이 얼마나 있을까해서 망설였습니다.  그러다 뜻밖에 시간이 나게 된 오늘 배열수식에 대해 잠시 알아보는 시간을 가질까 합니다. 위 그림은 연이율5%일때 빌린 돈(원금)의 액수와 빌린 기간(햇수)에 따른 이자를 계산해주는 표입니다.  원금X햇수X이율  하면  총 지불해야할 이자가 나오겠지요? 그런데 이 수식을 c3셀에 넣고 아래로 끌기하면 그 아래 셀에서는 0으로 나옵니다. 오, 그럼 이자를 지불하지 않아도 되는 건가요? ㅋㅋ C6셀을 클릭해보시면 끌어채우기를 하면서 수식이 변경된 것을 알 수 있습니다. 지난 번에 살펴보았던 대로, 절대주소와 상대주소를 적절히 사용하여 수식을 다시 써보면,  위와 같이 됩니다. 햇수는 가로로 변하고(C,D,E...) 세로는 2열에서 변하지 않아야 하므로  C$2 와 같이 써야하고 원금은 세로로 변하고 (1,2,3..) 가로는 B열에서 변하지 않아야 하므로  $B3 과 같이 써야합니다. 이율은 항상 B1을 가리키야하기 때문에  $B$1 과 같이 써야합니다. 그런데, 상대주소와 절대주소가 아무래도 헷갈립니다. 생각을 잘 하셔야지, 틀리기 쉽습니다.  이제 배열수식을 이용하는 방법을 살펴보겠습니다. 값을 구할 범위 전체를 선택하고, 등호(=)를 입력하여 수식을 다음과 같이 입력합니다. 그리고 CTRL+SHIFT+ENTER를 누릅니다. 선택했던 모든 셀에 값이 입력되고, 수식창을 보시면 입력한 수식의 앞뒤에 중괄호({})표시가 들어가 있는 것을 볼 수 있습니다. 이 중괄호 표시가 바로 이 수식은 배열수식으로 입력되었다는 것을 말해주는 표시입니다.  배열수식은 반드시 CTRL+SHIFT+ENTER를 눌러야 제대로 동작합니다 . 배열수식의 특징은 어느 셀에서나 수식이 같다는 것입니다. E7셀을 클릭해보시면,C3셀에 들어있는 수식과 똑같은 수식이라는

점수를 구간으로 나누어 각 구간마다 등급을 매기고 싶을 때가 있습니다.  지난번에 소개한 것처럼  사용자정의함수를 이용하는 방법 도 있습니다만, 좀 더 간단한 방법을 보여드리려고 합니다. 바로 vlookup 함수를 사용하는 방법입니다.  VLOOKUP함수의 구문중 다음 주의사항을 한번 보시죠. VLOOKUP ( lookup_value , table_array , col_index_num ,range_lookup) range_lookup   정확하게 일치하는 값을 찾을 것인지, 근사값을 찾을 것인지를 결정하는 논리값입니다. TRUE이거나 생략되면  정확한 값이나 근사값을 반환합니다.  정확하게 일치하는 값이 없으면 lookup_value보다 작으면서 그 다음으로 가장 큰 값을 반환합니다 . table_array의 첫 번째 열 값은  오름차순으로 정렬해야 합니다 .  그렇지 않으면 VLOOKUP 함수를 실행하여 올바른 결과를 얻을 수 없습니다.  오늘 할 일은 위의 주의사항중 밑줄친 부분의 내용을 이용하는 것입니다. 위 그림과 같이 학생들의 성적이 있고, 각 성적에 따른 등급을 매기고 싶습니다.  지난번의 글 에서 제시한 상황과 같습니다. 이때, 다른 시트나 빈 공간아무곳에나 오른쪽과 같은 등급표를 작성합니다. 왼쪽의 숫자는  각 등급의 최소값 을 보여줍니다. 즉, 오른쪽의 등급표의 의미는  0~49점은 F,  50~59점은 E,  60~69점은 D,  70~79점은 C,  80~89점은 B,  90점 이상은 A라는 의미입니다. 그리고 학생들의 등급이 들어가는 셀의 수식을 다음과 같이 입력합니다.  =VLOOKUP(B2,$F$3:$G$8,2) 그러면 각 성적에 따라 등급이 매겨졌습니다. VLOOKUP함수의  마지막 인수를 TRUE로 주거나 생략했을때 작거나 같은 값을 찾는다는 성질 을 이용한 것입니다.  사용자정의 함수를 쓰지 않아도 되니 편합니다. 위에 설명한 두 가지만 주의하시면 됩니다. 한번 더 설명하면,  1.

INDEX함수가 상당히 자주 쓰이는 유용한 함수인데 작년에 빠뜨려서 이번에 보충합니다.  엑셀 도움말에는 다음과 같이 되어있습니다. INDEX ( array ,row_num,column_num) array     배열 상수나 셀 범위입니다. 배열에 행이나 열이 하나만 있을 때는 row_num이나 column_num 인수를 생략할 수 있습니다. 배열에 행과 열이 두 개 이상 있을 때 row_num이나 column_num만 사용하면 배열의 전체 행이나 열이 하나의 배열로 반환됩니다. row_num     값을 반환할 배열의 행을 선택합니다. row_num을 생략하면 column_num을 지정해야 합니다. column_num     값을 반환할 배열의 열을 선택합니다. column_num을 생략하면 row_num을 지정해야 합니다. 주의 row_num과 column_num 인수를 모두 사용하면 row_num과 column_num이 교차하는 셀의 값이 반환됩니다. row_num이나 column_num을 0으로 설정하면 전체 열이나 전체 행에 대한 값의 배열이 각각 반환됩니다. 배열로 반환된 값을 사용하려면 행에 대한 가로 셀 범위와 열에 대한 세로 셀 범위에 INDEX 함수를 배열 수식 (배열 수식: 하나 이상의 값 집합에 대해 여러 가지 계산을 수행하고 하나 또는 여러 개의 결과를 반환하는 수식입니다. 배열 수식은 중괄호 { } 안에 표시되고 Ctrl+Shift+Enter를 눌러 입력합니다.)으로 입력합니다. 배열 수식을 입력하려면 Ctrl+Shift+Enter를 누릅니다. row_num과 column_num은 반드시 배열에 있는 셀이어야 합니다. 그렇지 않으면 #REF! 오류 값이 반환됩니다. INDEX함수에는 사실, 배열형과 참조형이 있으며, 위의 도움말은 배열형에 대한 설명입니다. 하지만, 참조형을 지금 알 필요는 없을 듯하여 배열형만을 설명하겠습니다. 당분간은 INDEX함수를 쓰실때 다음 그림처럼 배열형이나 참조형중에 선택

permutation : 순열 combination : 조합 에서 따온 이름들입니다. 수학시간에 다들 배우신 기억이 날 것입니다. 로또 1등 당첨확율은 얼마일까요? 답은 "1에서 45까지의 공이 있는데, 그 중에 6개의 공을 무작위로 꺼낼때 만들 수 있는 조합의 수"분의 1입니다. 실제로 계산하려면 45!/(45-6)!을 계산해야하므로, 매우 복잡할 것처럼 보입니다만, =COMBIN(45,6) 와 같이 간단하게 수식을 만들수 있으며, 답은 8,145,060 입니다. 로또 1등 당첨 확율이 8백만분의 1이 넘는다더니 정말이군요^^ 순열은 공을 꺼내는 것 뿐만 아니라 꺼낸 공을 배열하는 경우까지 따지므로, 그 값이 더 커지게 됩니다. =PERMUT(45,6) 의 결과값은  5,864,443,200 입니다. 50억이 넘는 숫자이군요. 추억의 수학문제입니다^^;  공이 다섯개가 있고, 공 하나에 1에서 5까지의 숫자중 하나가 쓰여져 있습니다. 5개의 공 중에서 무작위로 2개의 공을 꺼낼때 만들 수 있는 숫자의 개수는 모두 몇 개일까요? 예를 들어, 1과 2를 꺼냈다면, 12와 21이라는 숫자를 만들 수 있습니다. 수식과 결과값을 표시해 보세요. 재미로 보는 엑셀함수였습니다.^^ 2009/03/20 - [컴퓨터/엑셀] - 1일1함수(4): 계승(팩토리얼)을 구하자 FACT()함수

엑셀 2003에서 사용하던 난수 발생함수로 RAND함수가 있습니다. 인수가 필요없이 RAND()라고 입력하기만하면, 0에서 1사이의 실수를 반환합니다. 그래서 80과 100사이의 정수를 난수로 발생시키려면 다음과 같은 수식을 사용했습니다. =INT(RAND()*(100-80)+80) 수식을 분석하면, RAND()부분에서는 0에서 1사이의 실수가 반환되고 거기다 20을 곱하면 0과 20 사이의 실수가 됩니다. 거기다 80을 더하면 80과 100사이의 실수가 되고 그 값에 INT함수를 적용시키면 80과 100사이의 정수가 됩니다. 참고: INT함수는 가장 가까운 정수로 내림하는 함수입니다. INT(8.9) = 8 INT(-8.9) = -9  그런데, 엑셀2007에서는 이렇게 복잡하게 할 필요없이 RANDBETWEEN함수를 사용하면 됩니다.  80에서 100사이의 난수를 발생시키는 수식은 다음과 같습니다. =RANDBETWEEN(80,100) RAND함수를 이용하는 예를 하나 보여드리겠습니다. 학생들을 번호순이 아니라 무작위로 섞은 새로운 순번을 부여하고 싶습니다.  C열에는 RAND함수를 이용해서 난수를 발생시키고 D열에는 생성된 C열의 난수값으로 순번을 매기는 함수를 넣습니다. =RANK(C2,$C$2:$C$11) 그런데 해보시면 아시겠지만, RAND함수는 셀에 변화가 있을때마다 난수값을 새로 발생시킵니다 . 엔터하나만 쳐도 전체 셀의 값이 달라지는 겁니다. 그래서 지금 발생시킨 난수 및 순번을 그대로 고정시켜야합니다. 셀의 전체 범위를 선택하여 복사하신후, 같은 셀범위에 오른쪽 클릭하여 선택하여 붙여넣기를 실행하면 창이 뜹니다. 여기서 "값"을 선택하고 확인하면 이제는 난수가 더이상 새로 생성되지 않고 셀값이 고정됩니다. 아까 RAND함수가 들어있던 C열을 클릭해보면 RAND함수가 아니라 0에서1사이의 실수가 들어있습니다. 혹시라도