rand(life)

오늘도 쉬운 함수 두 개를 다루겠습니다. 너무나 쉬워서 다루기가 민망할 정도인데요^^; 최소값과 최대값을 구하는 함수인 MIN, MAX함수입니다. 점수중에서 최고점과 최하점을 구하는 수식은 다음과 같습니다. 최고점 =MAX(B2:B11) 최하점 =MIN(B2:B11) 아울러, 최고점과 최하점의 인원수를 구하는 수식은 다음과 같습니다 최고점자수 =COUNTIF(B2:B11,MAX(B2:B11)) 최하점자수 =COUNTIF(B2:B11,MIN(B2:B11)) 물론, 위의 그림처럼 표를 만들었다면 그 옆의 최고점수와 최하점수가 출력된 E2,E3셀을 참조하여 수식을 만드는 것이 좋겠지요. 최고점자수 =COUNTIF(B2:B11,E2) 최하점자수 =COUNTIF(B2:B11,E3) 간단한 함수지만 다른 수식과 결합하여 큰 역할을 할때가 있을겁니다^^

오늘은 OFFSET함수입니다. 약간 어려울수도 있지만, 고급의 기능을 수행하기 위해서는 꼭 알아야하는 함수입니다. 오늘은 간단한 설명만 하고, 다음에 구체적인 상황에서 사용할 때 다시 설명하도록 하겠습니다. 구문 OFFSET ( reference , rows , cols ,height,width) reference   오프셋의 기반으로 삼을 참조입니다. reference는 셀 또는 인접한 셀 범위를 참조해야 합니다. 그렇지 않으면 #VALUE! 오류 값이 반환됩니다. rows   왼쪽 위에 있는 셀이 참조할 위 또는 아래에 있는 행의 수입니다. rows 인수로 5를 사용하면 참조의 왼쪽 위 셀이 참조보다 5행 아래로 지정됩니다. rows는 양수(시작 참조보다 아래) 또는 음수(시작 참조보다 위)일 수 있습니다. cols   결과의 왼쪽 위 셀이 참조할 왼쪽 또는 오른쪽에 있는 열의 수입니다. cols 인수로 5를 사용하면 참조의 왼쪽 위 셀이 참조에서 5열 오른쪽으로 지정됩니다. cols는 양수(시작 참조에서 오른쪽) 또는 음수(시작 참조에서 왼쪽)일 수 있습니다. height   반환되는 참조의 높이(단위: 행 수)입니다. height는 양수여야 합니다. width   반환되는 참조의 너비(단위: 열 수)입니다. width는 양수여야 합니다. 주의 rows 및 cols 오프셋이 워크시트 가장자리 위를 참조하는 경우 OFFSET은 #REF! 오류 값을 반환합니다. height 또는 width를 생략하면 높이나 너비가 reference와 같은 것으로 간주됩니다. OFFSET은 셀을 실제로 이동하거나 선택을 변경하지 않으며 단지 참조를 구할 뿐입니다. OFFSET은 참조 인수를 기대하는 함수로 사용될 수 있습니다. 예를 들어 수식 SUM(OFFSET(C2,1,2,3,1))은 셀 C2의 아래에 1행이 있고 오른쪽에 2열이 있는 3행 x 1열 범위의 합계...

오늘은 쉽고 간단한 함수를 두 개 하려합니다. WON함수와 DOLLAR함수로, 숫자앞에 통화표시 ( ₩ ,$)를 붙여주는 함수입니다. WON ( number ,decimals) number   숫자, 숫자가 들어 있는 셀에 대한 참조 또는 숫자로 계산되는 수식입니다. decimals   소수점 이하의 자릿수로서 decimals가 음수이면 number가 소수점 왼쪽으로 반올림됩니다. decimals를 생략하면 2로 간주됩니다. DOLLAR함수의 도움말은 한글엑셀에서는 나오지 않습니다. 하지만 사용법은 WON과 같습니다. 지정 자릿수에서 반올림하는 것을 지정하는 decimals인수도 WON과 DOLLAR가 차이가 있는 것 같습니다. 도움말에서는 생략하면 2로 간주하므로, 소수점뒤에 두 자리까지 표시하게 되어있습니다만, DOLLAR의 경우는 제대로 표시가 되는 반면, WON의 경우는 원단위에서 끝납니다. 아마도 돈의 단위를 표시하는 문화가 달라서인 것 같습니다. 영미권에서는 센트자리까지 표시하는 경우가 많지만, 우리는 원단위밑의 전( 錢) 단위까지 표시하는 일은 드물지요. 그런데, 통화기호를 표시하는 것은 셀서식에서도 지정 가능합니다.  지난 번에 셀서식을 지정하면 편리하다는 내용 을 쓴 적이 있습니다. 마찬가지로, 해당 셀을 오른쪽 클릭하여 "셀서식"을 클릭하고 "표시형식"에서 "통화"를 선택한 다음, 소수점 자리수와 통화기호와 음수일 때 표시방법등을 지정하면 통화기호가 붙습니다. 하지만, 백단위에서 반올림하기위해 -3을 입력하면 위와 같은 오류메시지가 뜹니다. 즉, 셀서식에서는 백단위 반올림을 표시할 수가 없습니다.  통화기호를 표시하면서 동시에 지정단위수에서 반올림을 시키고 싶으면 , WON함수나 DOLLAR함수를 사용하면 편리합니다.   2009/04/23 - [컴퓨터/엑셀] - 1일1함수 (20...

지난 번에는 REPLACE함수를 사용해서, 특정위치에 있는 문자열 을 다른 문자열로 바꾸었습니다. 오늘 다루게 될 SUBSTITUTE함수는 특정한 문자열을 찾아, 다른 문자열로 바꾸는 함수 입니다. 구문 SUBSTITUTE ( text , old_text , new_text ,instance_num) text   문자를 대체할 텍스트가 포함된 셀의 참조 또는 텍스트입니다. old_text   바꿀 텍스트입니다. new_text   old_text를 대신할 텍스트입니다. instance_num    text에서 몇 번째에 있는 old_text를 new_text로 바꿀 것인지를 지정하는 수입니다 . instance_num을 지정하면 해당하는 위치에 있는 old_text만 바뀝니다. 그렇지 않으면 모든 old_text가 new_text로 바뀝니다. 다음과 같이 전화번호의 국번표시를 바꾸는 함수를 생각해 봅시다. SUBSTITUTE를 적용하니까 앞의 괄호는 바뀌지 않았습니다. 위의 수식으로 나온 결과값에다 한번 더 SUBSTITUTE를 적용하여, 앞에 있는 괄호를 공백으로 바꾸는 수식을 만들어 봅시다. 사실, 이것은 단축키 CTRL+F를 누르면 나오는 "찾기-바꾸기"와 유사한 기능입니다. CTRL+F를 눌러서 ")"를 찾아 "–"로 바꾸어주고 난뒤, "("를 찾아 빈칸으로 바꾸는 작업을 해도 됩니다. 하지만, CTRL+F로는 할 수 없는 경우도 있습니다. 위의 작업을 거꾸로 한다고 가정합시다. 즉, 02-222-2222라는 전화번호를 (02)-222-2222로 바꾼다고 해봅시다. 위의 그림처럼 CTRL+F를 하여 찾을내용을"–", 바꿀내용을" )"로 입력한 뒤 "모두바꾸기"하면 02)222)2222가 됩니다. 물론, 02뒤에 있는 "–"에 커서가 오면...

그동안 45개 정도의 함수를 살펴보았습니다. 앞으로도 400개 정도의 함수가 남아있습니다만, 우리가 평생가도 한번도 쓸 필요가 없을지도 모르는 함수들도 있으므로, 모든 함수를 다 살펴보는 것은 의미가 없을 것 같습니다. 많이 쓰이거나 특정 기능을 위해 꼭 필요한 함수들만 살펴보고, 중간중간에 엑셀 사용을 편리하게 해주는 팁들과, 함수 외에 많은 엑셀의 기능들(차트, 피벗, 해찾기등)을 살펴보기로 하겠습니다. (혹시 다루었으면 하는 함수가 있다면 댓글을 달아주세요) 그리고, 함수와 기능들에 대한 연구를 계속하면서, 각자가 맡은 업무에서 엑셀을 사용했을 때 궁금증이나 해결방법등을 공유하고 함께 연구하는 자리를 마련하려고 합니다. 지난번에 올렸던 이원목적분류표 같은 경우가 좋은 예가 되겠지요. "이런 문서를 엑셀로 만들면 편할 것 같은데, 이 부분에는 어떤 함수를 써야할까요?" 라던지, "이러저런 통계수치료를 보기 좋게 정리하려면 어떤 형식으로 표를 만들면 될까요?"라던지 하는 질문을 누군가 올리면, 다른 분들이 각자 연구하여 해결책을 찾아 올리고, 함께 보고 공유할 수 있겠지요. 그런 자료들이 모이고, 또 수정하는 과정을 거치면 굉장한 자료가 될 것입니다. 지금은 자기가 당장 맡은 업무가 아니기에 관계없을지 몰라도, 다음에 그 업무를 맡게 되면 모아둔 자료들이 힘을 발휘하겠지요. 그러기위해서는 지금의 블로그 방식으로는 힘들 듯 합니다. 블로그는 1인미디어이기 때문에 여러 사람이 참여하는 글을 올릴 수가 없습니다. 카페형식이 되어야할텐데, 기존의 포탈사이트의 카페를 이용하여 외부사람들도 참여하는 방법도 있고, 폐쇄된 형태로 학교 내에서만 운영하는 방법도 있을 듯 합니다. 좋은 의견 있으시면 주세요. 자, 서론이 길었습니다. 오늘은 문자열 함수중에서 REPLACE함수입니다. (앞으로 남은 문자열함수 중에서 우리가 다룰 것은 SUBSTITUTE, WON, DOLLAR 뿐입니다.) 구...

드디어 AVERAGEIFS함수 까지 왔습니다. 지금까지 SUMIF, SUMIFS, COUNTIF, COUNTIFS함수를 다 파악하신 분은 AVERAGEIFS함수의 특징 및 주의점등에 대해서 말하지 않아도 알고 계실 것입니다. 그리고 이전 글 에서 AVERAGEIFS함수를 발견하고서 놀라움과 허탈함을 느꼈다는 제 경험도 밝혔고요. 그래서 이번에는 AVERAGEIFS함수를 길게 설명하고 싶은 생각은 없습니다. 그래도 기본적인 설명은 해야겠지요? ^^; AVERAGEIFS ( average_range , criteria_range1 , criteria1 ,criteria_range2,criteria2...) average_range   숫자나 이름, 배열 또는 숫자가 들어 있는 참조를 포함하여 평균을 계산할 하나 이상의 셀입니다. criteria_range1, criteria_range2, ...   관련 조건을 평가할 1개에서 127개 사이의 범위입니다. criteria1, criteria2, ...   숫자, 식, 셀 참조 또는 텍스트 형식으로 된 1개에서 127개 사이의 조건으로서, 평균을 계산할 셀을 정의합니다. 예를 들어 32, "32", ">32", "사과" 또는 B4와 같이 지정할 수 있습니다. 주의 average_range가 빈 값이거나 텍스트 값이면 #DIV0! 오류 값이 반환됩니다. criteria_range로 정의한 셀 중 비어 있는 셀은 값 0으로 처리됩니다. 범위에서 TRUE가 들어 있는 셀은 1로 평가되고, FALSE가 들어 있는 셀은 0으로 평가됩니다. average_range의 각 셀은 해당 셀에 대해 지정한 관련 조건을 모두 만족하는 경우에만 평균 계산에 사용됩니다. AVERAGEIF 함수의 range 및 criteria 인수와 달리 AVERAGEIFS의 각 criteria_range는 sum_range와 크기 및 모양이 같아야 합니다. ...

고생끝에 SUMPRODUCT함수를 완전히 이해했다. 그래서 다중 조건에 맞는 셀의 합이나 평균을 구할 수 있게 되었다. 예를 들면, 8반학생 중에 영어90점 이상이고 수학이 80점 이상인 학생들의 국어점수의 평균을 구하고 싶다면? 그럼 수식은 다음과 같이 된다. =sumproduct(n(학생의반=8)*n(영어>90)*n(수학>80)*(국어))/sumproduct(n(학생의반=8)*n(영어>90)*n(수학>80)) 8반이며 영어90점 이상이고 수학이 80점 이상인 학생들의 국어점수의 합계/(나누기) 8반 학생중 영어90점 이상, 수학 80점 이상인 학생들의 숫자 = 8반 학생중 영어90점 이상, 수학 80점 이상인 학생들의 국어점수 평균 SUMPRODUCT함수가 각 조건들의 논리값중 TRUE만을 1로 반환한다는 점을 이용, 분모에는 주어진 조건을 만족하는 셀의 개수를, 분자에는 주어진 조건을 만족하는 셀안의 값의 합을 점수합계/학생수=평균 이라는 공식에 넣어서 만든 수식이다. 나름대로 애써서 만들었고, 그 결과에 대해 만족했다. 그런데....... 오늘 AVERAGEIFS 라는 함수를 알게 되었다. 2007에서 새로 만들어진 함수인데, 위의 수식을 간단하게 만들 수 있다. =AVERAGEIFS(국어,반,"=8",영어,">90",수학,">80") 너무나 간단해서 허탈하다. 마찬가지로, 다중조건에 맞는 셀의 값의 합을 구하는 함수는 SUMIFS 이다. 8반이며 영어90점 이상이고 수학이 80점 이상인 학생들의 국어점수의 합계는 다음과 같다. =SUMIFS(국어,반,"=8",영어,">90",수학,">80") 다중조건에 맞는 셀의 개수를 구하는 함수는 COUNTIFS이다. 8반이며 영어90점 이상이고 수학이 80점 이상인 학생들의 수는 다음과 같다. =COUNTIF...