[Excel] 직장 생활에 꼭 필요한 엑셀 함수의 개요, 인수의 유형, 오류값

◆ 함수 개요◆

1. 엑셀 함수란?

엑셀함수란 반복적이고 복잡한 일련의 계산과정을 엑셀에서 미리 정해진 수식(예약어)에 의해 연산되도록 작성한 하나의 엑셀에서 약속이다. 일반 연산에서 "1+2"는 "+"라는 기호는 1과 2의 값을 서로 더하라는 세계적인 공통의 약속이라면 엑셀에서는 함수인 "=SUM(1,2)"을 사용하면 1과2의 합계를 구하라는 엑셀의 일종의 약속이다. 실제 실무에서 사용하는 계산이 다양한 만큼 엑셀에서 함수 또한 매우 다양하다. 단순한 합계를 계산하는 함수에서 어려운 각종 공식등 함수의 종류는 약 300여 종류가 있다. 이러한 엑셀함수는 각종 계산과 분석을 위하여 단독으로 사용될 수도 있고, 엑셀의 매크로 프로그램밍에도 기본적인 명령어(예약어)로도 사용된다. ​

2. 함수의 기본 문법 ​ 등 호 함수사용시 수식과 마찬가지로 입력하는 테이타가 문자열이 아닌 함수 명령임을 알려주는 기호로 등호 입력후 함수가 아닌 일반 문자열을 입력하면 에러가 나오며, 특히 수식중간에 사용되는 함수일 경우는 등호를 붙이지 않는다. 　 함수이름 계산하고자 하는 함수 이름으로 한글엑셀의 경우 약 300여 종류가 있다. 　 괄 호 괄호는 함수 계산에 필요한 인수의 시작과 끝을 나타낸다. 함수에 따라서 인수를 필요로하지 않는 함수도 있지만 이 경우에도 반드시 괄호를 사용한다(예:『=RAND()』) 주) 각 괄호 앞뒤에 공백이 있으면 함수는 실행되지 않는다. 　 인 수 인수는 함수가 미리 정해진 연산 순서에 따라 계산될 때 사용하는 테이타로 참조영역, 숫자, 문자열, 논리값, 함수등의 말하며, 특히 인수로 함수를 사용하면 여러개의 함수를 조합하여 원하는 계산을 할 수 있다(예:『=if(AND(국어>80,산수>80),"합격","불합격)』) 　 어떤 함수에는 입력 위치에 따라 인수의 유형이 정해져 있는 경우도 있으므로 이때에는 함수마법사와 HELP등을 참고하여 기본값으로 정해진 인수의 유형을 입력하면된다.(예:num=수, ref=참조영역,logical=논리값) 　 콤 마 함수의 각 인수들은 콤마에 의해 구분된다. (주:숫자 입력시 세자리마다 콤마를 입력하는등 필요없는 콤마를 입력하지 않도록 주의해야한다) ​ ◆ 인수의 유형 ◆ 　 인수는 함수가 미리 정해진 연산 순서에 따라 계산될 때 사용하는 테이타로 참조영역,영역이름, 숫자, 문자열, 논리값, 함수등의 말하며, 인수를 얼마나 이해하는냐에 따라 함수를 이용한 계산의 능력을 달라진다. 　 1. 숫자 　 -10,0,1,10....등 일반 수식에서 사용되는 숫자를 말한다. 　 인수의 숫자는 직접 입력도 가능하지만 실제 업무에서는 참조영역이나 영역의 이름을 사용하는 경우가 대부분이며, 결과값은 숫자, 참조영역, 영역이름 중 어느것을 사용해도 같다. * 홍길동성적 : 영역 B4:D4까지 지정후 『삽입->이름』 메뉴를 이용 정의 ​ 2. 문자열 　 문자열을 직접 함수의 문자열로 사용하려면 반드시 큰따옴표(" ")로 묶어야 하며, 큰따옴표로 묶이지 않은 문자열을 사용하면 『#NAME?』 오류가 발생한다.함수에서 사용되는 문자열의 크기는 7.0은 255자 97이상에서는 65536자 사용이 가능하고 빈문자열(Null 문자)는 큰 따옴표를 연속으로("") 입력한다. 　 문자열도 숫자와 마찬가지로 문자열이 입력된 참조영역나 영역이름을 인수로 지정이 가능하다. 그림의 사용한 함수는 CONCATENATE 함수(문자열 연결)로 모든 인수가 문자열이어야 하지만 문자열이나 숫자가 입력된 참조영역을 인수로 지정해도 사용할 수 있음을 보여준다. 3. 논리값 　 논리값이란 참과 거짓을 나타내는 TRUE 또는 FALSE 값으로 대부분 논리 연산자를 사용하여 계산된 결과값으로 나타난다. 그림은 점수가 60점이상이면 합격을 표시하는 것으로, IF 함수의 Logical test 부분에 B2셀의 데이터와 60을 논리연산자로 비교하여 60이상이면 "★"표를 나타내게 한 것이다. 　 『=IF(B2>60,"★",""』함수의 B2<60 인수는 연산결과값은 TRUE 또는 FALSE로 나타난다. ​ "" 인수는 공백문자(공백셀)를 출력하는 값이다. 4. 참조 영역 　 참조 영역은 계산을 위하여 직접값을 입력하는 것이 아니라, 셀 영역의 값을 계산에 활용할 수 있도록 지정한 셀 영역을 말한다. 함수에서는 셀 영역의 값을 계산하기 위하여 참조 영역으로 지정하는데 함수의 계산식에서는 셀이 어떤 기준으로 참조되는가는 매우 중요한 요소이다. 계산에 셀 영역을 상대적 위치에서 참조하는 것을 상대 참조, 계산식이 입력되는 위치와 상관없이 고유의 셀 주소를 인식하는 절대 참조등이 있다. 　 　 상대 참조 상대참조란 계산식이 있는 셀 입장에서 참조하는 셀 영역을 상대적인 위치로 인식하는 것을 말한다. 　 길 안내를 예로 들면 "그대로 직진하시다가 첫 번째 신호등에서 우회전하여 200미터쯤 가면 됩니다"라고 대답 했다면 이 경우가 바로 상대 참조에 해당한다. 즉 현재 차량의 위치와 방향을 기준으로 하여 상대적인 위치로 길을 안내 한 것이다. 위의 예제에서 『=SUM(B2:D2)』에서 이 함수가 입력된 셀이 E2셀이라는 것을 감안하여 입력된 함수의 의미를 살펴보면 "같은 행(2행)의 현재 위치(E2)에서 왼쪽으로 세 번째 셀(B2)에서 왼쪽 첫 번째 셀(D2) 사이의 모든 셀 영역의 값을 합산(=SUM() 하라"라는 의미이다. 　 임꺽정이와 황진이의 총점은 다시 함수를 사용하지 않고 끌기나 복사로 계산하는 것이 보통인데 이 경우 함수안에서 참조한 영역이 자동으로 변하게된다. 즉 엑셀에서 상대참조 일 때는 복사나,채우기,이동 명령을 사용하면 참조 영역은 그 함수가 움직인 위치 만큼 참조 영역의 값이 자동으로 변하게 된다. 　 홍길동이의 총점을 계산한 함수를 임꺽정이 총점으로 계산식을 복사하면 함수가 입력된 셀은 E2에서 E3으로 아래로 한 셀 변하는 것이고 여기에 따라 함수의 참조영역도 B2 -> B3으로 D2 -> D3으로 변하여 복사 된다. 절대 참조 절대참조란 계산식이 입력된 위치와 상관 없이 참조되는 셀 영역이 고유의 셀 위치로 인식한다. 　 사무실 위치를 예로 들면 "울산 무거동 100번지 현대빌딩 100호입니다"라고 안내하였다면 이것이 바로 절대 참조이다. 즉 안내를 받는 사람의 위치, 방향이 어디던지 사무실의 위치는 바뀌지 않는다. 엑셀의 첨대 참조는 행,열 번호 앞에 '$'를 하므로 절대위치 즉 절대 참조(예:'$A$1')가 된다.(* 참조 영역 선택시 '$'입력 하던지 F4키를 누른다) 　 함수를 사용함에 있어서 절대 참조를 사용하는 경우는 함수에서 사용하는 인수가 함수를 복사,채우기,이동 할 때 참조 영역이 변하면 계산결과가 잘못 될 때 사용하게 된다. 강동지점에서 비율을 계산한 다음 끌기,복사하면 참조영역이 상대참조로 되어 있어 합계가 입력된 위치가 E6,E7,E8로 각각 수식의 위치에 변하므로 연산 결과는 "0으로 나누었다는 에러가 나온다. 이 경우 합계가 입력된 E5는 함수의 위치와 상관없이 항상 E5를 참조해야 하므로 이때는 절대참조를 사용하여 계산한다. 5. 주소지정 방법 　 함수 인수로 주소를 사용할시 콜론(colon), 콤마(comma), 공백(space)을 이용하여 지정 할 수 있으며, 사용방법에 따라 주의해야한다. Colon 콜론은 연속된 영역을 나타내는 것으로 A3:F3이면 A3 ∼ F3이 되어 3+4+5+6+7+8된다. 　 Comma 콤마는 지정된 셀만 나타내는 것으로 3+5+6+7이 된다. 또한 여러 영역을 동시에 지정할 때도 사용가능한데 예를들어 =SUM(A3:B3,E3:F3)이면 3+4+7+8이 된다. 　 Space 공백은 두영역의 교점(공통부분)의 셀을 나타내는 것으로 A3:F3 C2:D4이면 두영역의 공통영역인 C3,D6의 데이터인 5+6이된다. ​ ​ ◆ 함수,수식 오류값◆ 　 　 　 수식에서 결과를 계산할 수 없으면 오류값이 나타납니다. 예를 들어, 숫자 값을 지정해야 하는 수식에 문자열을 입력하거나, 수식에서 참조하는 셀을 지우거나, 셀이 작아 결과를 나타낼 수 없을 때 오류값이 나타난다. 　 　 　 　 　 　 오류값 Return값 의 미 #Null! 1 교차하지 않는 두 셀 영역 사이에 참조 연산자중 공백문자(Space Bar키)을 입력하여 교차 영역을 참조 영역으로 지정한 경우에 나타난다. 예를 들어 『=SUM(A2:A5 B2:B5)』와 같이 두영역 사이에 콤마를 쓰지 않고 공백문자를 사용하여 두 영역을 교차하는 경우에 나타난다. #DIV/O! 2 모든 숫자는 0으로 나누어질 수 없다. 따라서 빈셀 또는 '0'이 입력된 셀을 나누는 인자로 지정한 경우에 이와 같은 오류가 나타난다. #VALUE 3 연산자에서 사용할 수 없는 인수나 참조영역을 지정한 경우에 나타난다. 예를 들어 '+"연산자를 이용하여 숫자가 입력된 셀과 문자열이 입력된 셀 영역을 더하기 한 경우 등에 나타난다. #REF! 4 수식에서 참조하도록 지정한 셀이 유효하지 않을 경우 등에 나타난다. 예를 들어 다른 수식에서 참조하는 셀를 삭제하거나, 이 위치에 새로운 셀을 이동 시킨 경우에 나타난다. #NAME? 5 엑셀에서 인식할 수 없는 문자열을 수식에 사용했을 때 나타한다. 함수 이름, 셀 참조 영역 등을 제외하고 수식에 입력된 문자열 중 큰 따옴표(" ")로 묶여지지 않은 경우 나타한다. #NUM! 6 수식에 숫자와 관련한 문제점이 있을 때 나타난다. 예를 들어 반드시 숫자를 인수로 지정하여야 하는 함수에서 숫자가 아닌 인수를 지정한 경우 등에 나나난다. #N/A 7 오류값 #N/A는 함수나 수식에 사용할 수 없는 값을 지정했을 때 발생한다. 예를 들어 배열 수식의 경우에는 배열의 참조 영역으로 지정한 행,열의 크기가 다른 경우에 나타난다. ###### 　 셀에 입력된 숫자 값이 너무 커 셀 안에 나타낼 수 없을 때 또는 수식의 결과가 너무 길어 셀 안에 모두 표시할 수 없을 때 나타난다. ​