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

엑셀 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

지난시간에 내드린 문제 의 답을 먼저 알려드리겠습니다. 문제는 sheet1 부터 sheet10사이에 있는 모든 시트에서 B2:B11의 범위에 있는 모든 셀의 값을 더하는 수식을 구하는 것이었습니다. 답은 아래와 같습니다. =SUM(Sheet1:Sheet10!B2:B11) 오늘은 SUMIF함수입니다.  SUMIF(range, criteria, [sum_range]) range   조건을 적용할 셀 범위로서 필수 항목입니다. 각 범위의 셀은 숫자나 이름, 배열 또는 숫자가 들어 있는 참조여야 합니다. 공백과 텍스트 값은 무시됩니다. criteria   추가할 셀을 정의하는 숫자, 식, 셀 참조, 텍스트 또는 함수 형식의 조건으로서 필수 항목입니다. 예를 들어 32, ">32", B5, 32, "32", "사과", 오늘() 등으로 criteria를 표시할 수 있습니다.  중요     텍스트 조건이나 논리 기호 또는 수학 기호가 포함된 조건은 큰따옴표( " )로 묶어야 합니다. 조건이 숫자인 경우에는 큰따옴표가 필요 없습니다. sum_range   r ange 인수에 지정된 것과 다른 셀을 더하려는 경우 실제로 더할 셀로서 선택 항목입니다. sum_range 인수를 지정하지 않으면 range 인수에 지정된 셀(조건이 적용되는 셀)이 더해집니다. criteria  인수로 와 일드카드 문자인 물음표( ? )와 별표( * )를 사용할 수 있습니다 . 물음표는 문자 하나에 해당하고, 별표는 개수에 상관없는 일련의 문자에 해당합니다. 실제 물음표나 별표를 찾으려면 문자 앞에 물결표( ~ )를 입력합니다.  이전에 COUNTIF에서 다루었던 주의점들이 여기서도 보입니다. - 같은 수를 조건으로 주면 따옴표가 필요없지만,  부등호로 비교를하면 따옴표를 넣어줘야한다 는 점  SUM(A1:A5,100)         A1:A5범위중 100와 같은 값을 가지고 있는 셀의 값

지난번에 엑셀로 이원목적 분류표 를 만들었습니다. 여러가지 경우의 수를 생각하면서 수식을 만들다보니 이렇게 고생할바에야 그냥 개수를 세는게 낫겠다는 생각도 들었지만, 써보니 편하다고 말씀하시는 분들이 많아 보람도 느낍니다. 사소한 몇가지 오류를 수정하여 이전포스트에 올려두었습니다. 그리고 또 한가지 느낀 점은, 엑셀을 거의 사용하지 않으시는 분들이 의외로 많다는 것이었습니다. 제가 이것을 만들때는 어느정도는 엑셀을 사용할 줄 아시는 분들을 대상으로 만들었기에, 엑셀을 처음 다뤄보시는 분들이 난감해 하는 것을 볼때 제가 편하자고 만든 것이 또 어떤 분에게는 불편함이 되는구나 하는 생각도 들었습니다. 그래서, 다음 버전업때는 엑셀 초보이신 분들도 큰 불편없이 사용할 수 있게 대대적으로 바꾸어 볼까 합니다. 예를 들면, 주관식 문항수와 객관식 문항수를 입력하면, 자동으로 그 수만큼 표가 만들어져서, 행을 삭제하거나 추가하는 수고를 덜어드리는 것이라든지, 아니면 HWP파일에서처럼 주관식,객관식 밑에 빈칸이 있어도 오류가 생기지 않도록 하는 방법을 연구한다던지 하는 것입니다. 혹시 좋은 생각이 있으시면 제안해주시면 고맙겠습니다. (P.S. 사실은 수식이 이렇게 복잡할 필요가 없었습니다. 엑셀의 기본적인 상식을 깜빡한 탓입니다. 다시 이원목적분류표를 다시 수정해서 올렸습니다.) 2009/04/18 - [컴퓨터/엑셀] - 엑셀로 작성한 이원목적 분류표 간단버전 이번주는 엑셀로 만든 이원목적분류표안에 있는 함수들을 알아보는 시간으로 하겠습니다. 사용된 수식들은 다음과 같습니다. 문항수합계의 수식은 =COUNTA(INDIRECT("R"&MATCH("유형",$A:$A,0)+2&"C"&COLUMN()&":R"&ROW()-1&"C"&COLUMN(),FALSE)) 문항배점합계의 수식은 =SUM(INDIRE

오늘은 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( 다음의 조건을 만족하는 셀의 수를

오늘은 이전에 배운 COUNTA함수와 정반대의 기능을 하는 COUNTBLANK함수입니다. COUNTBLANK ( range ) range   빈 셀의 개수를 계산할 범위입니다. 주의 ""(빈 텍스트)를 반환하는 수식이 포함된 셀의 개수도 계산됩니다. 그러나 0 값을 포함하는 셀은 제외됩니다. COUNTBLANK 함수는 비어있는 셀의 개수를 세며, 실제로는 셀 안에 수식이 들어있더라도 결과값이 빈텍스트가 출력되도록 되어있다면 카운트를 합니다. 단, 0이 들어있으면 비어있는 것이 아니라 0이 들어있는 것이므로 카운트를 하지 않습니다. 위 그림처럼 C2:C11의 범위에서 COUNTBLANK함수를 쓰면 결과값은 2가 됩니다. 학생4와 학생8은 0이 입력되어있으므로, 카운트하지 않습니다. C열에는 =IF(B2>70,"합격","") 와 같은 수식이 들어있기는 하지만, 결과값으로 빈텍스트 ("")가 출력된 6행과 7행을 빈셀로 간주하여 카운트하기에 결과값은 2입니다. 2009/04/05 - [컴퓨터/엑셀] - 1일1함수 (11) COUNTIF 함수 2009/04/12 - [컴퓨터/엑셀] - 1일1함수 (13) COUNT함수 : 숫자가 들어있는 셀의 수를 세자. 2009/04/12 - [컴퓨터/엑셀] - 1일1함수 (14) COUNTA 함수 : 비어있지 않은 셀의 개수 구하기

오늘은 COUNTA함수입니다. COUNT함수 는 숫자가 들어있는 셀의 개수만을 구했다면, COUNTA함수는 숫자, 텍스트 뿐만 아니라 논리값(TRUE,FALSE)이나 오류메시지조차도 세어줍니다. 인수 목록에서 공백이 아닌 셀과 값의 개수를 계산합니다. COUNTA를 사용하면 범위나 배열에서 데이터가 들어 있는 셀의 개수를 계산할 수 있습니다. 구문 COUNTA ( value1 ,value2,...) value1, value2, ...   개수를 계산할 값을 나타내는 인수로, 1개에서 255개까지 사용할 수 있습니다. 주의 오류 값과 빈 텍스트("")를 포함하여 모든 형식의 정보를 값으로 사용할 수 있습니다. 빈 셀은 값에 포함되지 않습니다. 인수가 배열 또는 참조이면 해당 배열이나 참조의 값만 사용되고 배열 또는 참조의 빈 셀과 텍스트 값은 무시됩니다. 논리값, 텍스트 또는 오류 값의 개수를 계산할 필요가 없는 경우에는 COUNT 함수를 사용하십시오. 전체 학생수를 표시해야하는 문서를 자주 작성해야하는데 그때마다 전체 학생 이름이 나열된 시트를 찾아가서 확인하면 귀찮겠지요? 엑셀러가 귀찮음을 느낀다는 것이 발전하고 있다는 증거입니다. 전혀 귀찮아하지 않고 부지런히 작업할 수도 있습니다만, 그렇다면 발전이 없습니다. 각설하고, 2008년 매달마다 전체학생수의 변화를 나타내는 표를 작성해야한다고 합시다. 시트의 이름은 200803, 200804, ~, 200812 처럼 연도와 달의 숫자로 주었다고 합시다. 위의 그림과 같은 내용을 200803 시트부터 200812 시트까지 작성했다고 할때, 매달 전체 학생수의 변화를 나타내는 합계시트를 다음과 같이 작성했습니다. 3월의 전체 학생수에 해당하는 수식은 위의 그림과 같습니다. =COUNTA('200803'!$D:$D)-1  =COUNTA( 다음 범위에서 공백이 아닌 모든 셀의 개수를 세어라  &#

지난주까지는 좀 어려운 내용들이었습니다만, 이번주부터는 함수의 종류별로 차근차근히 알아보도록 하겠습니다. 오늘은 숫자가 들어있는 셀의 수를 세는 COUNT함수입니다. 인수 목록에서 숫자가 포함된 셀과 숫자의 개수를 계산 합니다. COUNT 함수를 사용하면 숫자 범위나 배열 내의 숫자 필드에 있는 항목 수를 계산할 수 있습니다. 구문 COUNT ( value1 ,value2,...) value1, value2, ...   여러 데이터 형식을 포함하거나 참조하는 인수이며 개수 계산에는 숫자만 포함됩니다. 1개에서 255개까지 사용할 수 있습니다. 주의 숫자, 날짜 또는 숫자의 텍스트 표현을 인수로 사용하여 개수를 계산할 수 있습니다. 인수 목록에 직접 입력하는 논리값, 텍스트로 나타낸 숫자 등은 계산에 포함됩니다. 숫자로 변환할 수 없는 텍스트나 오류 값이 인수에 포함된 경우 이러한 값은 무시됩니다. 인수가 배열 또는 참조이면 해당 배열이나 참조의 숫자만 사용되고 배열 또는 참조의 빈 셀, 논리값, 텍스트, 오류 값 등은 무시됩니다. 논리값, 텍스트 또는 오류 값의 개수를 계산하려면 COUNTA 함수를 사용해야 합니다. 다음과 같이 학생의 성적이 있을때 학생들의 성적 평균을 내기위해 AVERAGE함수를 씁니다. 그런데, 가끔씩 결시하는 학생이나 성적표에 오류값이 들어있는 경우가 있는데, 과연 AVERAGE함수가 그 학생들의 성적은 어떻게 처리하고 있는 건지 궁금합니다. 이번 기회에 확인해 봅시다. 아래 그림과 같이 학생들의 성적이 있고, 학생4와 학생8은 점수가 들어있지 않습니다. 학생 성적의 평균은 시험에 응시한 학생만의 성적으로 내야합니다. 즉, 다음과 같이 수식을 쓸 수 있습니다. =SUM(B2:B11)/COUNT(B2:B11)  =SUM(B2:B11)  학생1부터 학생10까지학생의 성적의 총합을  / 다음의 수로 나눈다  COUNT(B2:B11)  학생1부터

오늘은 주어진 조건에 맞는 셀의 개수를 세는 COUNTIF함수에 대해 알아보겠습니다. COUNTIF ( range , criteria ) range   숫자나 이름, 배열 또는 숫자가 포함된 참조를 비롯하여 개수를 계산할 하나 이상의 셀입니다. 빈 값과 텍스트 값은 무시됩니다. criteria   개수를 계산하려는 셀을 정의하는 텍스트, 숫자, 식 또는 셀 참조의 형식으로 된 조건입니다. 예를 들어 32, "32", ">32", "사과", B4 등으로 조건을 지정할 수 있습니다. 주의 와일드카드 문자인 물음표(?)와 별표(*)를 조건에 사용할 수 있습니다. 물음표는 문자 하나에 해당하고, 별표는 일련의 문자에 해당합니다. 실제 물음표나 별표를 찾으려면 문자 앞에 물결표(~)를 입력합니다. 점수가 90점 이상인 학생들의 수를 세고 싶다면 다음처럼 합니다. =COUNTIF($C$2:$C$15,">=90") 여기서 주의할 점은, 점수가 90점인 학생의 수를 구하는 식은 =COUNTIF($C$2:$C$15,90) 로, 따옴표가 없습니다. 그런데 90점 이상 학생의 수를 구할때처럼, 부등호가 들어가면 따옴표를 넣어야한다 는 것이 주의할 점입니다. 더구나, 만일 숫자를 90을 넣지 않고, 90이라는 숫자가 들어있는 다른 셀을 참조하게 만들려면 =COUNTIF($C$2:$C$15,">"&F2) 와 같이, &기호도 붙여줘야한다 는 점입니다. 와일드카드도 사용할 수 있습니다. 와일드카드란, 임의의 문자를 대신하는 기호로, 별표(*)는 길이에 상관없이 모든 문자, 물음표(?)는 한 글자를 대신합니다. 주소지가 강남구인 학생의 수를 세려면? =COUNTIF($B$2:$B$15,"강남구*") 동이나 번지는 달라도 되고, &qu