rand(life)

네이버 지식인 에서 알게 된 좋은 수식을 소개합니다. 이런 식으로 A1:D2 범위 내에 사람 이름이 중복된 것 포함해서 여러개 있습니다. 이때  중복된 이름은 제외하고 모두 몇 명의 이름이 있는지  알고 싶을 때 쓰는 수식입니다. 수식은 위와 같습니다.  일단, 이 수식의 기본적인 구조는,  각 사람의 이름이 나올 때마다 그 자리에 전체 범위에서 그 사람의 이름이 나오는 횟수를 분모로, 1을 분자로 한 값을 배정하여, 나중에 그 모든 숫자를 합하는 것입니다. 간단한 예를 들어서, 다음과 같이 이름이 6개가 배열되어 있다고 합시다. 홍길동,장길산,홍길동,홍길동,홍길동,장길산 그럼 이름 대신에 그 자리에 다음 숫자를 배당합니다. 0.25, 0.5, 0.25, 0.25, 0.25, 0.5 홍길동은 총 4번 나오니 1/4해서 0.25를 홍길동이 나오는 자리마다 배정합니다. 장길산은 총 2번 나오니 1/2해서 0.5를 장길산이 나오는 자리마다 배정합니다. 이제 모든 숫자를 더하면 0.25+ 0.5+ 0.25+ 0.25+ 0.25+ 0.5 = 2  그래서 중복된 이름을 제외하고 모두 2명의 이름이 있다는 결과가 나옵니다. 이제 수식을 살펴보겠습니다. =SUMPRODUCT((A1:D2<>"")/(1-(A1:D2<>"")+COUNTIF(A1:D2,A1:D2))) (엑셀에서 수식-수식분석-수식계산을 하시면 단계별로 수식이 어떻게 계산되는지 볼 수 있으므로 수식을 이해하는데 도움이 됩니다.  밑줄이 쳐진 부분은 이번 단계에서 계산이 될 예정이라는 것을 의미하고 이탤릭으로 표시된 부분은 바로 이전 단계에서 계산이 된 결과라는 것을 보여줍니다) 이 부분은 A1:D2 범위에서 공백이 아닌, 즉 이름이 있는 셀을 골라내는 것입니다. 그래서 이름이 들어있으면 TRUE, 공백이면 FALSE

고생끝에 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

엑셀 2007에서 새로이 추가된 함수입니다. 이전에 배웠던 SUMIF함수나 COUNTIF함수와 비슷한 구문을 사용한다고 보시면 됩니다. AVERAGEIF ( range , criteria ,average_range) range   숫자나 이름, 배열 또는 숫자가 들어 있는 참조를 포함하여 평균을 계산할 하나 이상의 셀입니다. criteria   숫자, 식, 셀 참조 또는 텍스트 형식의 조건으로서, 평균을 계산할 셀을 정의합니다. 예를 들어 32, "32", ">32", "사과" 또는 B4와 같이 지정할 수 있습니다. average_range   평균을 계산하는 데 사용할 실제 셀 집합입니다. 지정하지 않으면 range가 사용됩니다. 주의할 점으로, 와일드 카드(?,*)를 사용할 수 있으며, 부등호를 붙일때 따옴표를 써야한다는 점등이 SUMIF, COUNTIF등과 같습니다.  average_range를 따로 지정하지 않으면 맨 처음에 나오는 범위인수 (range)의 평균을 구한다는 점도 SUMIF와 같습니다. 지난시간에 SUMIF함수에서 썼던 표와 유사한 표입니다. 1반의 국어성적의 평균을 구하는 수식은 다음과 같습니다. =AVERAGEIF(   다음 조건이 맞을때 평균을 구합니다    $A$2:$A$13,  A2:A13의 범위중 "반"의 값이  I3,  I3의 값과 같은 값이 있으면  1 (셀서식에서 "0반"으로 지정) 이면  $D$2:$D$13)  D2:D13중에서 해당 셀들의 평균  국어성적의 평균 [복습과제1]  위의 수식을 그대로 오른쪽으로 끌어채우기하면 영어,수학,총점에서 올바른 값이 나오지 않습니다. 상대참조와 절대참조를 정확하게 사용하지 않았기 때문입니다.  앞 시간 에서 다루었던 대로, 상대참조와 절대참조를 사용해서, 아래로, 오른쪽으로 끌어도 정확한 답이 나오도록 수식을 다시 작성하세요. [복습과제2] 엑셀 200

N함수는 각종 값을 숫자로 변환시켜줍니다. 다양한 데이타 서식을 하나로 통일시켜 준다는 점에서 T함수와 유사합니다. (참고로, T함수는 각종 값을 텍스트로 변환시켜줍니다) N ( value ) value   변환할 값입니다. N 함수를 사용하여 변환되는 값은 다음과 같습니다. 값 또는 참조 결과 숫자 같은 숫자 Microsoft Excel에서 사용할 수 있는 기본 제공 날짜 형식 중 하나를 사용하는 날짜 해당 날짜의 일련 번호 TRUE 1 FALSE 0 오류 값(예: #DIV/0!) 오류 값 기타 0 주의 일반적으로 Excel에서는 필요에 따라 값이 자동으로 변환되므로 수식에서 N 함수를 사용할 필요가 없습니다. 이 함수는 다른 스프레드시트 프로그램과의 호환을 위해 제공됩니다. 날짜는 계산에 사용할 수 있도록 순차적인 일련 번호로 저장됩니다. 기본적으로 1900년 1월 1일이 일련 번호 1이고, 2008년 1월 1일은 1900년 1월 1일에서 39,448일째 날이므로 일련 번호 39448이 됩니다. Macintosh용 Microsoft Excel에서는  기본적으로 다른 날짜 체계 가 사용됩니다. 아래는 지난 시간에 언급했던 다중답의 개수를 구하는 수식입니다.  =SUMPRODUCT(N(LEN(INDIRECT("C"&MATCH("유형",A:A,0)+2&":C"&MATCH("주관식",A:A,0)-1))>1)) : 원래버전 =SUMPRODUCT(N(LEN($C$9:$C$38)>1))  : 간편버전 둘 다 N함수가 들어있습니다. 여기서 N함수는 논리값을 숫자0,1로 변환시켜주는 일을 합니다. 이전에 다중 조건의 개수를 구하는 수식은 COUNTIFS로 구할 수도 있고 SUMPRODUCT로 구할수도 있다고 했었지요? 즉,  SUMPRODUCT((국어>=90)*(수학(>=90))   이라는 수식은 국어점수가 90점 이상이면서

계속해서 이원목적분류표에 있는 함수중 다중답의 개수를 구하는 수식을 살펴보겠습니다. 수식은 다음과 같습니다. =SUMPRODUCT(N(LEN(INDIRECT("C"&MATCH("유형",A:A,0)+2&":C"&MATCH("주관식",A:A,0)-1))>1)) LEN함수와 N함수를 제외하고는 모두 배운 함수입니다. 여기서 N함수는 내일 설명하겠지만, 여기서는 굳이 없어도 됩니다만 배열계산을 할때 배열이 하나밖에 없으면 집어넣는 것이 오류 가능성을 줄여준다고 합니다. 오늘은 LEN함수에 대해 배우겠습니다. LEN함수는 문자열의 길이를 반환하는 함수입니다. LEN ( text ) text   길이를 확인하려는 문자열입니다. 공백도 문자로 계산됩니다. 아주 간단하죠? ^^* 위에서처럼, 영어, 숫자, 한글, 구두점 모두 한 개로 계산하여 나타냅니다. 이원목적분류표의 다중답의 개수를 구하는 위의 수식에서 INDIRECT부분을 계산하여 실제 셀 주소로 바꾸고 보면, C8:C37 이 됩니다. 즉, 위의 수식은 다음과 같습니다. =SUMPRODUCT(N(LEN(C8:C37)>1)) 즉, C8:C37의 범위중에서 문자열의 길이가 1보다 긴 셀의 개수를 구하는 것입니다. 정답이 1,2,3,4,5중의 하나라면 LEN함수를 거친 결과값은 1이 되겠지만, 정답이 다중답이라면 한자리 숫자보다 더 많은 글자가 들어가야하므로 길이가 1보다 커지겠네요. 예를 들어, C8셀은 1번 문제의 정답을 보여주며, 정답은 "2,4"라면 =LEN(C8) 의 결과값은 얼마일까요? 예, 3입니다. LEN함수는 다음에 한번 정리하게 될 문자열 함수들과 함께 사용할때 유용한 함수입니다. 2009/04/15 - [컴퓨터/엑셀] - 엑셀로 작성한 이원목적분류표 2009/04/21 - [컴퓨터/엑셀] - 1일1함수 (18

이원목적분류표를 엑셀로 만들어 보았습니다. 배점합이 100점이 맞는지 계산하거나 난이도 상중하가 몇개씩인지 세기가 귀찮아서 만들어 보았습니다. 엑셀 2007에서 제일 잘 작동합니다. 파일을 열어보고 혹시나 걱정하실 분이 있을까 미리 말씀해 드리는데, 이 안에 들어있는 데이타는 2005년에 실시한 시험데이타입니다. 유출되더라도 전혀 문제될 것이 없습니다. 이 안에 사용된 함수들중에서 지금까지 배운 것과 다음에 배울 것들이 많이 있습니다. SUMPRODUCT, INDIRECT, MATCH, COUNTA, LEN, N ... 과목마다 객관식이나 주관식의 문항수가 다르다는 문제를 해결하기가 제일 어려웠습니다. 수식이 복잡한 이유는 객관식문항이나 주관식 문항의 수가 바뀌어도 오류가 나지 않도록 하기위해서입니다. 따라서 객관식과 주관식사이에 공백을 두면 오류가 납니다. 주관식과 맨 아랫단 사이에 빈 행이 들어가 있어도 오류가 납니다. 이런식으로 객관식과 주관식 사이가 떨어져 있거나 주관식과 계 사이가 떨어져 있으면 이 부분이 이런 식으로 빨간색으로 변합니다. 오류가 났다는 의미입니다. 지금처럼 객관식,주관식,계 사이에 공백이 없이 연결되어있어야합니다. p.s. 2009/4/16 엑셀 2003에서는 설정한 서식과 수식에 일부 문제가 있어서 2003버전을 따로 만들었습니다. p.s. 2009/4/17 제목 줄을 추가하였을때 생기는 오류를 수정하였습니다. ( 2003 , 2007 버전 둘 다) p.s. 2009/4/18 지나치게 복잡한 수식을 제거하고, 최대한 단순하게 만든 버전 입니다.

오늘은 2007에서 새로이 추가된 함수인 COUNTIFS함수를 알아보겠습니다. 이전에 배웠던 COUNTIF함수가 단일한 조건을 만족하는 셀의 게수를 구했다면, COUNTIFS함수는 여러조건을 동시에 만족하는 셀의 개수를 구합니다. 범위 내에서 여러 조건을 만족하는 셀의 개수를 계산합니다. 구문 COUNTIFS ( range1 , criteria1 ,range2, criteria2...) range1, range2, ...   관련 조건을 평가할 1개에서 127개 사이의 범위입니다. 각 범위의 셀은 숫자나 이름, 배열 또는 숫자가 들어 있는 참조여야 합니다. 빈 값이나 텍스트 값은 무시됩니다. criteria1, criteria2, ...   숫자, 식, 셀 참조 또는 텍스트 형식으로 된 1개에서 127개 사이의 조건으로서, 개수를 계산할 셀을 정의합니다. 예를 들어 32, "32", ">32", "사과" 또는 B4와 같이 지정할 수 있습니다. 주의 range의 각 셀은 해당 셀에 대해 지정한 관련 조건을 모두 만족하는 경우에만 개수를 계산하는 데 포함됩니다. 조건이 빈 셀이면 해당 셀은 값 0으로 처리됩니다. 와일드카드 문자인 물음표(?)와 별표(*)를 조건에 사용할 수 있습니다. 물음표는 한 문자에 해당하고 별표는 모든 문자열에 해당합니다. 실제 물음표나 별표를 찾으려면 해당 문자 앞에 물결표(~)를 입력합니다. 다음과 같은 학생의 성적이 있습니다. 이중에서 국어, 영어, 수학의 점수가 모두 80점 이상인 학생의 수를 알고 싶습니다. A14열에는 다음과 같은 수식이 들어갑니다. =COUNTIFS($B$2:$B$11,">=80",$C$2:$C$11,">=80",$D$2:$D$11,">=80")  =COUNTIFS( 다음의 조건을 만족하는 셀의 수를

오늘은 SUMPRODUCT함수를 다룹니다. SUMPRODUCT함수는 매우 다양한 문제의 해결을 위해 광범위하게 응용될 수 있는 함수입니다. 오늘은 석차를 낼때 사용하는 예를 들어 설명해 보겠습니다. 주어진 배열에서 해당 요소를 모두 곱하고 그 곱의 합계를 반환합니다. 구문 SUMPRODUCT ( array1 ,array2,array3, ...) array1, array2, array3, ...   곱한 후 더할 값이 들어 있는 배열을 지정합니다. 2개에서 255개 사이의 배열을 사용할 수 있습니다. 주의 인수로 사용하는 배열의 차원은 모두 같아야 합니다. 차원이 같지 않으면 #VALUE! 오류 값이 반환됩니다. 숫자가 아닌 배열 항목은 0으로 처리됩니다. SUMPRODUCT함수는 다음에 이야기하게 될 "배열함수"와 깊은 관련이 있습니다. 영어로는 ARRAY인데, 학교 다닐때 "행렬"이라는 이름으로 배운 기억이 있는데 엑셀에서는 "배열"이라는 표현을 씁니다. 배열로 수식을 작성하는 것은 저도 아직 정확하게 이해는 하지 못했지만, 대충 감은 잡고 있는데, 그 감을 잡는데 이 SUMPROCUT함수가 도움이 되었습니다. 이 함수는 위에 있는 설명에도 있듯이, 주어진 배열을 모두 곱한 뒤에 그 합계를 반환하는 함수입니다. (2,4) 라는 배열과 (3,5) 라는 배열이 있을때, SUMPRODUCT({2,4},{3,5})라고 하면 2*3 + 4*5 를 계산하여 26을 반환합니다. 같은 식을 다음과 같이 표현할 수도 있습니다. A1:A2 의 배열과 B1:B2의 배열이 * (논리곱)으로 연결되어있습니다. 이때의 계산식은 위에서 설명한 것과 같이, 2*3 + 4*5 입니다. 그런데, 다음처럼 + (논리합)으로 연결되면 (2+3) * (4+5) 를 계산하여 14를 반환합니다. 여기까지 SUMPRODUCT함수의 기본적인 설명이