< C5FC7D4BCF6C1A4B8AE2E786C73>

Transcription

1 1. 함수개요 2. 인수의유형 3. 오류값 4. 항목별함수 ( 아래참조 ) 항목분류함수설명 정보 x CELL 셀의서식, 위치또는내용에대한정보를보여줍니다. 정보 x ERROR.TYPE 오류유형에해당하는번호를구합니다. 정보 x INFO 현재의운영환경에대한정보를보여줍니다. 정보설명 ISBLANK 값이비어있으면 TRUE 를반환합니다. 정보설명 ISERR 값이 #N/A 를제외한오류값이면 TRUE 를반환합니다. 정보설명 ISERROR 값이오류값이면 TRUE 를반환합니다. 정보 x ISEVEN 값이짝수이면 TRUE 를반환합니다. 정보설명 ISLOGICAL 값이논리값이면 TRUE 를반환합니다. 정보설명 ISNA 값이 #N/A 오류값이면 TRUE 를반환합니다. 정보설명 ISNONTEXT 값이텍스트가아니면 TRUE 를반환합니다. 정보설명 ISNUMBER 값이숫자이면 TRUE 를반환합니다. 정보 x ISODD 값이홀수이면 TRUE 를반환합니다. 정보설명 ISREF 값이셀주소이면 TRUE 를반환합니다. 정보설명 ISTEXT 값이텍스트이면 TRUE 를반환합니다. 정보 x N 숫자로변환된값을반환합니다. 정보 x NA #N/A 오류값을반환합니다. 정보 x TYPE 값의데이터형식을나타내는숫자를반환합니다. 논리설명 AND 모든인수가 TRUE 이면 TRUE 를반환합니다. 논리 x FALSE 논리값 FALSE 를반환합니다. 논리설명 IF 수행할논리검사를지정합니다.

2 논리 x NOT 인수의논리에대한역을반환합니다. 논리설명 OR TRUE 인인수가있으면 TRUE 를반환합니다. 논리 x TRUE 논리값 TRUE 를반환합니다. 조회및참조 x ADDRESS 텍스트로워크시트의한셀에대한참조를반환합니다. 조회및참조 x AREAS 참조영역내영역의수를구합니다. 조회및참조설명 CHOOSE 값목록에서값을선택합니다. 조회및참조 x COLUMN 참조영역의열번호를구합니다. 조회및참조 x COLUMNS 참조영역의열개수를구합니다. 조회및참조 x HLOOKUP 조회및참조 x HYPERLINK 조회및참조 x INDEX 배열의첫째행을조회하여지정한셀의값을반환합니다. 네트워크서버, 인트라넷, 인터넷등에저장된문서로이동할바로가기키를만듭니다. 인덱스를사용하여참조또는배열에서값을선택합니다. 조회및참조 x INDIRECT 텍스트값이나타내는참조영역을반환합니다. 조회및참조 x LOOKUP 벡터나배열에서값을찾습니다. 조회및참조 x MATCH 참조나배열에서값을찾습니다. 조회및참조 x OFFSET 주어진참조영역으로부터참조오프셋을구합니다. 조회및참조 x ROW 참조영역의행번호를구합니다. 조회및참조 x ROWS 참조영역의행개수를구합니다. 조회및참조 x RTD COM 자동화를지원하는프로그램에서실시간으로데이터를가져옵니다. 조회및참조 x TRANSPOSE 배열의행과열을바꿉니다. 조회및참조설명 VLOOKUP 수학및삼각함수 x ABS 절대값을구합니다. 배열의첫째열을조회하고행을따라이동하면서셀의값을구합니다. 수학및삼각함수 x ACOS 아크코사인값을구합니다. 수학및삼각함수 x ACOSH 역하이퍼볼릭코사인값을구합니다. 수학및삼각함수 x ASIN 아크사인값을구합니다. 수학및삼각함수 x ASINH 역하이퍼볼릭사인값을구합니다.

3 수학및삼각함수 x ATAN 아크탄젠트값을구합니다. 수학및삼각함수 x ATAN2 x, y 좌표의아크탄젠트값을구합니다. 수학및삼각함수 x ATANH 역하이퍼볼릭탄젠트값을구합니다. 수학및삼각함수 x CEILING 수학및삼각함수 x COMBIN 수학및삼각함수 x COS 코사인값을구합니다. significance의가장가까운정수또는배수가되도록수를내림하거나올림합니다. 주어진개체수로만들수있는조합의수를구합니다. 수학및삼각함수 x COSH 하이퍼볼릭코사인값을구합니다. 수학및삼각함수 x DEGREES 라디안을각도로변환합니다. 수학및삼각함수 x EVEN 가장가까운짝수로수를올림합니다. 수학및삼각함수 x EXP 수를지수로한 e 의누승을계산합니다. 수학및삼각함수 x FACT 수의계승값을구합니다. 수학및삼각함수 x FACTDOUBLE 수의이중계승값을구합니다. 수학및삼각함수 x FLOOR 0 에가까워지도록수를내림합니다. 수학및삼각함수 x GCD 최대공약수를구합니다. 수학및삼각함수 x INT 가장가까운정수로수를내림합니다. 수학및삼각함수 x LCM 최소공배수를구합니다. 수학및삼각함수 x LN 자연로그값을구합니다. 수학및삼각함수 x LOG 지정한밑에대한로그값을구합니다. 수학및삼각함수 x LOG10 밑이 10 인로그값을구합니다. 수학및삼각함수 x MDETERM 배열의행렬식을구합니다. 수학및삼각함수 x MINVERSE 배열의역행렬을구합니다. 수학및삼각함수 x MMULT 두배열의행렬곱을구합니다. 수학및삼각함수 x MOD 나눗셈의나머지를구합니다. 수학및삼각함수 x MROUND 원하는배수로반올림된수를구합니다. 수학및삼각함수 x MULTINOMIAL 수집합을다항식으로반환합니다.

4 수학및삼각함수 x ODD 가장가까운홀수로수를올림합니다. 수학및삼각함수 x PI pi 값을구합니다. 수학및삼각함수 x POWER 수를거듭제곱한결과를구합니다. 수학및삼각함수 x PRODUCT 인수들의곱을구합니다. 수학및삼각함수 x QUOTIENT 나눗셈의몫정수부를구합니다. 수학및삼각함수 x RADIANS 각도를라디안으로변환합니다. 수학및삼각함수 x RAND 0 과 1 사이의난수를구합니다. 수학및삼각함수 x RANDBETWEEN 지정한두수사이의난수를구합니다. 수학및삼각함수 x ROMAN 아라비아숫자를텍스트인로마숫자로변환합니다. 수학및삼각함수설명 ROUND 수를지정한자릿수로반올림합니다. 수학및삼각함수 x ROUNDDOWN 0 에가까워지도록수를내림합니다. 수학및삼각함수 x ROUNDUP 0 에서멀어지도록올림합니다. 수학및삼각함수 x SERIESSUM 수식에따라멱급수의합을구합니다. 수학및삼각함수 x SIGN 수의부호값을반환합니다. 수학및삼각함수 x SIN 지정한각도의사인값을구합니다. 수학및삼각함수 x SINH 하이퍼볼릭사인값을구합니다. 수학및삼각함수 x SQRT 양의제곱근을구합니다. 수학및삼각함수 x SQRTPI (number * pi) 의제곱근을구합니다. 수학및삼각함수 x SUBTOTAL 목록이나데이터베이스의부분합을구합니다. 수학및삼각함수설명 SUM 인수를모두더합니다. 수학및삼각함수설명 SUMIF 주어진조건으로지정된셀을모두더합니다. 수학및삼각함수 x SUMPRODUCT 대응되는배열요소를곱해서그합을구합니다. 수학및삼각함수 x SUMSQ 인수의제곱의합을구합니다. 수학및삼각함수 x SUMX2MY2 수학및삼각함수 x SUMX2PY2 두배열에서해당값에대한제곱의차의합을구합니다. 두배열에서해당값에대한제곱의합을모두더한값을구합니다.

5 수학및삼각함수 x SUMXMY2 수학및삼각함수 x TAN 탄젠트값을구합니다. 두배열에서해당값에대한차의제곱의합을구합니다. 수학및삼각함수 x TANH 하이퍼볼릭탄젠트값을구합니다. 수학및삼각함수 x TRUNC 정수부만남기고나머지자리를버립니다. 통계 x AVEDEV 데이터요소의절대편차평균을구합니다. 통계 x AVERAGE 인수의평균을구합니다. 통계 x AVERAGEA 인수의평균 ( 숫자, 텍스트및논리값포함 ) 을구합니다. 통계 x BETADIST 베타누적분포함수를구합니다. 통계 x BETAINV 지정된베타분포에대한누적분포의역함수를구합니다. 통계 x BINOMDIST 개별항이항분포의확률을구합니다. 통계 x CHIDIST 카이제곱분포의단측검정확률을구합니다. 통계 x CHIINV 카이제곱분포단측검정확률의역함수를구합니다. 통계 x CHITEST 독립검증결과를구합니다. 통계 x CONFIDENCE 모집단평균의신뢰구간을나타냅니다. 통계 x CORREL 두데이터집합사이의상관계수를구합니다. 통계 x COUNT 인수목록에서숫자의개수를구합니다. 통계 x COUNTA 인수목록에서값의개수를구합니다. 통계 x COUNTBLANK 범위내에서비어있는셀의개수를구합니다. 통계설명 COUNTIF 통계 x COVAR 통계 x CRITBINOM 범위내에서비어있지않고주어진조건에맞는셀의개수를구합니다. 공분산, 즉쌍을이룬편차에대한곱의평균을구합니다. 누적이항분포가기준치이하가되는값중최소값을구합니다. 통계 x DEVSQ 편차제곱의합을구합니다. 통계 x EXPONDIST 지수분포를구합니다. 통계 x FDIST F 확률분포를구합니다. 통계 x FINV F 확률분포의역함수값을구합니다.

6 통계 x FISHER Fisher 변환값을구합니다. 통계 x FISHERINV Fisher 변환의역변환값을구합니다. 통계 x FORECAST 선형추세값을구합니다. 통계 x FREQUENCY 빈도분포를계산하여수직배열로나타냅니다. 통계 x FTEST F- 검정의결과를구합니다. 통계 x GAMMADIST 감마분포를구합니다. 통계 x GAMMAINV 감마누적분포의역함수를구합니다. 통계 x GAMMALN 감마함수 Γ(x) 의자연로그값을구합니다. 통계 x GEOMEAN 기하평균을구합니다. 통계 x GROWTH 지수추세값을구합니다. 통계 x HARMEAN 조화평균을구합니다. 통계 x HYPGEOMDIST 초기하분포값을구합니다. 통계 x INTERCEPT 선형회귀선의절편을구합니다. 통계 x KURT 데이터집합의첨도를구합니다. 통계 x LARGE 데이터집합에서 k 번째로큰값을구합니다. 통계 x LINEST 선형추세의매개변수를나타냅니다. 통계 x LOGEST 지수추세의매개변수를나타냅니다. 통계 x LOGINV 로그정규분포의역함수값을구합니다. 통계 x LOGNORMDIST 로그정규누적분포값을구합니다. 통계 x MAX 인수목록에서최대값을구합니다. 통계 x MAXA 인수목록에서최대값 ( 숫자, 텍스트및논리값포함 ) 을구합니다. 통계 x MEDIAN 주어진수의중간값을구합니다. 통계 x MIN 인수목록에서최소값을구합니다. 통계 x MINA 통계 x MODE 인수목록에서최소값 ( 숫자, 텍스트및논리값포함 ) 을구합니다. 데이터집합에서가장빈도가높은값을구합니다.

7 통계 x NEGBINOMDIST 음이항분포값을구합니다. 통계 x NORMDIST 정규누적분포값을구합니다. 통계 x NORMINV 정규누적분포의역함수값을구합니다. 통계 x NORMSDIST 표준정규누적분포값을구합니다. 통계 x NORMSINV 표준정규누적분포의역함수값을구합니다. 통계 x PEARSON 피어슨곱모멘트상관계수를구합니다. 통계 x PERCENTILE 범위에서 k 번째백분위수를구합니다. 통계 x PERCENTRANK 데이터집합의백분율값순위를구합니다. 통계 x PERMUT 주어진개체수로만들수있는순열의수를구합니다. 통계 x POISSON 포아송확률분포값을구합니다. 통계 x PROB 영역내의값이최소값을포함한두한계값사이에있을확률을구합니다. 통계 x QUARTILE 데이터집합에서사분위수를구합니다. 통계 x RANK 수목록내에서지정한수의크기순위를구합니다. 통계 x RSQ 피어슨곱모멘트상관계수의제곱을구합니다. 통계 x SKEW 분포의왜곡도를구합니다. 통계 x SLOPE 선형회귀선의기울기를구합니다. 통계 x SMALL 데이터집합에서 k 번째로작은값을구합니다. 통계 x STANDARDIZE 정규화된값을구합니다. 통계 x STDEV 표본집단의표준편차를구합니다. 통계 x STDEVA 표본집단의표준편차 ( 숫자, 텍스트및논리값포함 ) 를구합니다. 통계 x STDEVP 전체모집단의표준편차를구합니다. 통계 x STDEVPA 통계 x STEYX 전체모집단의표준편차 ( 숫자, 텍스트및논리값포함 ) 를구합니다. 회귀분석에의해예측한 y 값의표준오차를각 x 값에대하여구합니다. 통계 x TDIST 스튜던트 t- 분포값을구합니다. 통계 x TINV 스튜던트 t- 분포의역함수값을구합니다.

8 통계 x TREND 선형추세값을구합니다. 통계 x TRIMMEAN 데이터집합의내부평균을구합니다. 통계 x TTEST 스튜던트 t- 검정에근거한확률을구합니다. 통계 x VAR 표본집단의분산을구합니다. 통계 x VARA 표본집단의분산 ( 숫자, 텍스트및논리값포함 ) 을구합니다. 통계 x VARP 전체모집단의분산을구합니다. 통계 x VARPA 전체모집단의분산 ( 숫자, 텍스트및논리값포함 ) 을구합니다. 통계 x WEIBULL 와이블분포값을구합니다. 통계 x ZTEST z- 검정의단측검정확률값을구합니다. 텍스트및데이터 x ASC 텍스트및데이터 x BAHTTEXT 전자문자 ( 더블바이트 ) 를반자문자 ( 싱글바이트 ) 로바꿉니다. ß( 바트 ) 통화형식을사용하여숫자를텍스트로변환합니다. 텍스트및데이터 x CHAR 코드숫자로지정된문자를반환합니다. 텍스트및데이터 x CLEAN 인쇄할수없는모든문자들을텍스트에서제거합니다. 텍스트및데이터 x CODE 텍스트의첫째문자를나타내는코드값을구합니다. 텍스트및데이터 설명 여러텍스트항목을한텍스트항목에결합합니 CONCATENAT 다. 텍스트및데이터 x DOLLAR $( 달러 ) 통화형식을사용하여숫자를텍스트로변환합니다. 텍스트및데이터설명 EXACT 두텍스트값이동일한지검사합니다. 텍스트및데이터설명 FIND 텍스트및데이터 x FIXED 텍스트및데이터 x JUNJA 다른텍스트값에서한텍스트값을찾습니다 ( 대 / 소문자구분 ). 고정소수점을사용하여숫자에텍스트서식을지정합니다. 반자문자 ( 싱글바이트 ) 를전자문자 ( 더블바이트 ) 로바꿉니다. 텍스트및데이터설명 LEFT 텍스트값에서가장왼쪽의문자를반환합니다. 텍스트및데이터 x LEN 텍스트문자열내의문자개수를구합니다. 텍스트및데이터 x LOWER 텍스트를소문자로변환합니다. 텍스트및데이터설명 MID 텍스트문자열의지정한위치로부터특정수의문자를반환합니다. 텍스트및데이터 x PHONETIC 텍스트문자열에서윗주문자를추출합니다.

9 텍스트및데이터 x PROPER 텍스트값의각단어의첫글자를대문자로바꿉니다. 텍스트및데이터 X REPLACE 텍스트내의문자를바꿉니다. 텍스트및데이터 x REPT 텍스트를지정한횟수만큼반복합니다. 텍스트및데이터설명 RIGHT 텍스트값에서가장오른쪽의문자를표시합니다. 다른텍스트값에서한텍스트값을찾습니다 ( 대 / 텍스트및데이터설명 SEARCH 소문자구분안함 ). 텍스트문자열의기존텍스트를새텍스트로바꿉텍스트및데이터설명 SUBSTITUTE 니다. 텍스트및데이터 x T 관련인수를텍스트로변환합니다. 텍스트및데이터설명 TEXT 숫자에서식을지정하고텍스트로변환합니다. 텍스트및데이터 x TRIM 텍스트에서공백을제거합니다. 텍스트및데이터 x UPPER 텍스트를대문자로변환합니다. 텍스트및데이터 x VALUE 텍스트인수를숫자로변환합니다.

10 함수개요 1. 엑셀함수란? 엑셀함수란반복적이고복잡한일련의계산과정을엑셀에서미리정해진수식 ( 예약어 ) 에의해연산되도록작성한하나의엑셀에서약속이다. 일반연산에서 "1+2" 는 "+" 라는기호는 1 과 2 의값을서로더하라는세계적인공통의약속이라면엑셀에서는함수인 "=SUM(1,2)" 을사용하면 1 과 2 의합계를구하라는엑셀의일종의약속이다. 실제실무에서사용하는계산이다양한만큼엑셀에서함수또한매우다양하다. 단순한합계를계산하는함수에서어려운각종공식등함수의종류는약 300 여종류가있다. 이러한엑셀함수는각종계산과분석을위하여단독으로사용될수도있고, 엑셀의매크로프로그램밍에도기본적인명령어 ( 예약어 ) 로도사용된다. 2. 함수의기본문법 등 호 함수사용시수식과마찬가지로입력하는테이타가문자열이아닌함수명령임을알려주는기호로등호입력후함수가아닌일반문자열을입력하면에러가나오며, 특히수식중간에사용되는함수일경우는등호를붙이지않는다. 함수이름계산하고자하는함수이름으로한글엑셀의경우약 300 여종류가있다.( 엑셀 HELP 참조 ) 괄호괄호는함수계산에필요한인수의시작과끝을나타낸다. 함수에따라서인수를필요로하지않는함수도있지만이경우에도반드시괄호를사용한다 ( 예 : =RAND() ) 주 ) 각괄호앞뒤에공백이있으면함수는실행되지않는다. 인수인수는함수가미리정해진연산순서에따라계산될때사용하는테이타로참조영역, 숫자, 문자열, 논리값, 함수등의말하며, 특히인수로함수를사용하면여러개의함수를조합하여원하는계산을할수있다 ( 예 : =if(and( 국어 >80, 산수 >80)," 합격 "," 불합격 ) ) 어떤함수에는입력위치에따라인수의유형이정해져있는경우도있으므로이때에는함수마법사와 HELP 등을참고하여기본값으로정해진인수의유형을입력하면된다.( 예 :num= 수, ref= 참조영역,logical= 논리값 ) 콤마함수의각인수들은콤마에의해구분된다. ( 주 : 숫자입력시세자리마다콤마를입력하는등필요없는콤마를입력하지않도록주의해야한다 )

11 인수의유형 인수는함수가미리정해진연산순서에따라계산될때사용하는테이타로참조영역, 영역이름, 숫자, 문자열, 논리값, 함수등의말하며, 인수를얼마나이해하는냐에따라함수를이용한계산의능력을달라진다. 1. 숫자 -10,0,1,10... 등일반수식에서사용되는숫자를말한다. 인수의숫자는직접입력도가능하지만실제업무에서는참조영역이나영역의이름을사용하는경우가대부분이며, 결과값은숫자, 참조영역, 영역이름중어느것을사용해도같다. 결과값 : 200 * 홍길동성적 : 영역 B4:D4 까지지정후 삽입 -> 이름 메뉴를이용정의 2. 문자열 문자열을직접함수의문자열로사용하려면반드시큰따옴표 ( ) 로묶어야하며, 큰따옴표로묶이지않은문자열을사용하면 #NAME? 오류가발생한다. 함수에서사용되는문자열의크기는 7.0 은 255 자 97 이상에서는 자사용이가능하고빈문자열 (Null 문자 ) 는큰따옴표를연속으로 ("") 입력한다 문자열도숫자와마찬가지로문자열이입력된참조영역나영역이름을인수로지정이가능하다. 그림의사용한함수는 CONCATENATE 함수 ( 문자열연결 ) 로모든인수가문자열이어야하지만문자열이나숫자가입력된참조영역을인수로지정해도사용할수있음을보여준다. 3. 논리값 논리값이란참과거짓을나타내는 TRUE 또는 FALSE 값으로대부분논리연산자를사용하여계산된결과값으로나타난다.

12 그림은점수가 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() 하라 " 라는의미이다. 임꺽정이와황진이의총점은다시함수를사용하지않고끌기나복사로계산하는것이보통인데이경우함수안에서참조한영역이자동으로변하게된다. 즉엑셀에서상대참조일때는복사나, 채우기, 이동명령을사용하면참조영역은그함수가움직인위치만큼참조영역의값이자동으로변하게된다

13 홍길동이의총점을계산한함수를임꺽정이총점으로계산식을복사하면함수가입력된셀은 E2 에서 E3 으로아래로한셀변하는것이고여기에따라함수의참조영역도 B2 -> B3 으로 D2 -> D3 으로변하여복사된다. 절대참조절대참조란계산식이입력된위치와상관없이참조되는셀영역이고유의셀위치로인식한다. 사무실위치를예로들면 " 울산무거동 100 번지현대빌딩 100 호입니다 " 라고안내하였다면이것이바로절대참조이다. 즉안내를받는사람의위치, 방향이어디던지사무실의위치는바뀌지않는다. 엑셀의첨대참조는행, 열번호앞에 '$' 를하므로절대위치즉절대참조 ( 예 :'$A$1') 가된다.(* 참조영역선택시 '$' 입력하던지 F4 키를누른다 ) 함수를사용함에있어서절대참조를사용하는경우는함수에서사용하는인수가함수를복사, 채우기, 이동할때참조영역이변하면계산결과가잘못될때사용하게된다. 강동지점에서비율을계산한다음끌기, 복사하면참조영역이상대참조로되어있어합계가입력된위치가 E6,E7,E8 로각각수식의위치에변하므로연산결과는 "0 으로나누었다는에러가나온다. 이경우합계가입력된 E5 는함수의위치와상관없이항상 E5 를참조해야하므로이때는절대참조를사용하여계산한다. 5. 주소지정방법 함수인수로주소를사용할시콜론 (colon), 콤마 (comma), 공백 (space) 을이용하여지정할수있으며, 사용방법에따라주의해야한다.

14 Colon 콜론은연속된영역을나타내는것으로 A3:F3 이면 A3 F3 이되어 된다. Comma 콤마는지정된셀만나타내는것으로 이된다. 또한여러영역을동시에지정할때도사용가능한데예를들어 =SUM(A3:B3,E3:F3) 이면 이된다. Space 공백은두영역의교점 ( 공통부분 ) 의셀을나타내는것으로 A3:F3 C2:D4 이면두영역의공통영역인 C3,D6 의데이터인 5+6 이된다.

15 함수, 수식오류값 수식에서결과를계산할수없으면오류값이나타납니다. 예를들어, 숫자값을지정해야하는수식에문자열을입력하거나, 수식에서참조하는셀을지우거나, 셀이작아결과를나타낼수없을때오류값이나타난다. 오류값 Return 값의미 #Null! 1 #DIV/O! 2 교차하지않는두셀영역사이에참조연산자중공백문자 (Space Bar키 ) 을입력하여교차영역을참조영역으로지정한경우에나타난다. 예를들어 =SUM(A2:A5 B2:B5) 와같이두영역사이에콤마를쓰지않고공백문자를사용하여두영역을교차하는경우에나타난다. 모든숫자는 0으로나누어질수없다. 따라서빈셀또는 '0' 이입력된셀을나누는인자로지정한경우에이와같은오류가나타난다. #VALUE 3 #REF! 4 #NAME? 5 #NUM! 6 #N/A 7 ###### 연산자에서사용할수없는인수나참조영역을지정한경우에나타난다. 예를들어 '+" 연산자를이용하여숫자가입력된셀과문자열이입력된셀영역을더하기한경우등에나타난다. 수식에서참조하도록지정한셀이유효하지않을경우등에나타난다. 예를들어다른수식에서참조하는셀를삭제하거나, 이위치에새로운셀을이동시킨경우에나타난다. 엑셀에서인식할수없는문자열을수식에사용했을때나타한다. 함수이름, 셀참조영역등을제외하고수식에입력된문자열중큰따옴표 (" ") 로묶여지지않은경우나타한다. 수식에숫자와관련한문제점이있을때나타난다. 예를들어반드시숫자를인수로지정하여야하는함수에서숫자가아닌인수를지정한경우등에나나난다. 오류값 #N/A 는함수나수식에사용할수없는값을지정했을때발생한다. 예를들어배열수식의경우에는배열의참조영역으로지정한행, 열의크기가다른경우에나타난다. 셀에입력된숫자값이너무커셀안에나타낼수없을때또는수식의결과가너무길어셀안에모두표시할수없을때나타난다. 15/53

16 ROUND 숫자를지정한자릿수로반올림합니다. 구문 반올림할수 ROUND(number,num_digits) 반올림할 Number 의자릿수 주의 Num_digits 가 0 보다크면숫자는지정한소수자릿수로반올림됩니다. Num_digits 가 0 이면가장가까운정수로반올림됩니다. Num_digits 가 0 보다작으면소수점왼쪽에서반올림됩니다. 예제 예제 수식 설명 ( 결과 ) 결과 1 =ROUND(2.15, 1) 2.15를소수점한자리로반올림합니다. (2.2) =ROUND(2.149, 1) 3 =ROUND(-1.475, 2) 4 =ROUND(21.5, -1) 응용중량을소수점두자리로반올림반올림합니다 를소수점한자리로반올림합니다. (2.1) 를소수점두자리로반올림합니다. (-1.48) 21.5 를소수점왼쪽한자리로반올림합니다. (20) 순번 품번 중량 결과 1 85Z B B HOL B B B 참고 ROUNDUP : 0 에서먼방향으로수를올림합니다. ROUNDDOWN : 0 에가까운방향으로내림합니다. INT : 가장가까운정수로내립니다. TRUNC : 숫자의소수점이하를버리고정수로변환합니다. ROUND 함수와활용방법이유사하므로설명은하지않겠습니다. 스스로한번공부해보세요.(EXCEL 도움말참고 ) 20

17 SUM 셀범위에있는숫자를모두더합니다. 구문 합을구할인수 SUM(number1,number2,...) 간편하게사용하기위하여자동합계도구를만들어제공한다. 단문자열과공백셀은 '0' 으로처리한다. 주의 인수목록으로직접입력한숫자, 논리값, 숫자의텍스트표시는계산됩니다. 아래나오는예제중처음두예제를참고하십시오. 숫자로변환될수없는텍스트나오류값을인수로사용하면오류가발생합니다. 1 개부터 30 개까지사용할수있습니다. 예제 데이터 '5 TRUE 예제수식설명 ( 결과 ) 결과 1 =SUM(3, 2) 3 과 2 를더합니다. (5) 5 2 =SUM("5", 15, TRUE) 3 =SUM(A22:A24) 4 =SUM(A22:A24, 15) 5 =SUM(A25,A26, 2) 텍스트값은숫자로변환되고, 논리값 TRUE 는숫자 1 로변환되므로, 5 와 15 와 1 을더합니다. (21) 위열에서처음세개의숫자를더합니다. (40) 위열에서처음세개의숫자와 15 를더합니다. (55) 위의마지막두개의행에있는값을더합니다. 참조영역의숫자가아닌값은변환되지않으므로위열의값들은무시됩니다. (2) 참고 COUNT : 인수목록에서숫자가포함된셀과숫자의개수를계산합니다. COUNTA : 인수목록에서공백이아닌셀과값의개수를계산합니다. AVERAGE : 인수의산술평균을반환합니다. PRODUCT : 인수를모두곱한결과를표시합니다.

18 SUMIF 주어진조건에따라지정되는셀을더합니다. 구문 SUMIF(range,criteria,sum_range) criteria 숫자, 수식또는텍스트형태로된찾을조건입니다. 예를들어 criteria 는 32, "32", ">32", " 사과 " 등으로표시할수있습니다. 주의 sum_range의셀에대응하는 range의셀이찾을조건과일치할때만더할수있습니다. sum_range를생략하면 range에있는셀들이더해집니다. Microsoft Excel에서는조건을기준으로데이터를분석하기위해사용할수있는함수들을추가로제공합니다. 예를들어셀범위내에서텍스트문자열이나숫자가나오는횟수를구하려면 COUNTIF 워크시트함수를사용합니다. 수식에서조건에따라두개의값중하나가반환되게하려면 ( 예 : 지정된판매량에따른특별판매수당 ) IF 워크시트함수를사용합니다. 예제 조건을적용시킬셀범위 속성값 수수료 100,000 7, ,000 14, ,000 21, ,000 28,000 합을구할실제셀 찾을조건 예제 수식 설명 ( 결과 ) 결과 1 =SUMIF(A24:A27,"> 이넘는속성값에대한수수료합 ",B24:B27) 계입니다. COUNTIF 범위내에서주어진조건에맞는셀의개수를셉니다. 구문 조건을적용시킬셀범위 COUNTIF(range,criteria) 찾을조건

19 응용 각품번별로수량의합을구하고목록에몇번사용되었는지구하는예제입니다. Part No CAGE Qty 품번 QTY의합 사용횟수 AA A A-A A A A A A80480 F A A A-A A AA A-A TYI-GRA 2 2 A-A TYI-GR A-A TYI-GRB 2 2 A-A A-A TYI-GRC 2 2 A-A TYI-GR A-A A-A F6 셀의수식 A-A "=SUMIF($A$6:$A$41,E6,$C$6:$C$41)" 에서 $A$6:$A$41은조건을적용시킬셀범위로 A-A 절대주소를사용한이유는상대주소를그대로사용 A-A TYI-GR 하면복사해서쓸경우셀범위가변경되어원하는 A 정확한값을찾을수없으므로절대주소 (F4키) 로 AA 변경하였습니다. A-A E6 은합을할조건으로 E6의값 "1636" 을 A $A$6:$A$41 에서모두찾으라는뜻입니다. A-A TYI-GR $C$6:$C$41은조건으로찾은값의합을할 범위입니다. 조건 "1636" 에해당하는값은 $C$6:$C$41 의범위에서찾아보면 3, 2 A 이고이들의합이결과 "5" 가됩니다 A-A G6의수식 "=COUNTIF($A$6:$A$41,E6)" A-A 는 SUMIF 함수와거의유사한형태로조건에해당 A-A TYI-GR 하는셀의개수를구하는합수입니다. A-A TYI-GR COUNTIF 합수는중복된데이터를식별할때에도유용하게쓰일수있습니다. 사용횟수에서값이 "2" 이상인것은중복되서사용된것입니다.

20 AND 인수가모두 TRUE 이면 TRUE 를반환하고, 인수들중하나라도 FALSE 이면 FALSE 를반환합니다. 구문 TRUE 또는 FALSE 로계산될수있는조건 AND(logical1,logical2,...) 1 개부터 30 개까지사용할수있습니다. 주의 인수는 TRUE 또는 FALSE 와같은논리값으로평가되거나논리값이들어있는배열또는참조이어야합니다. 배열이나참조인수에텍스트또는빈셀이들어있는경우에는그러한값들이무시됩니다. 지정한범위에논리값이없으면 #VALUE! 오류값이반환됩니다. 예제 데이터 예제 수식 설명 ( 결과 ) 결과 1 =AND(TRUE, TRUE) 인수가모두 TRUE입니다. (TRUE) TRUE 2 =AND(TRUE, FALSE) 인수하나는 FALSE입니다. (FALSE) FALSE 3 =AND(2+2=4, 2+3=5) 인수가모두 TRUE로계산됩니다. (TRUE) TRUE 4 =AND(1<A2, A2<100) 50은 1과 100 사이에있습니다. (TRUE) FALSE 5 6 =IF(AND(1<A25, A25<100), A25, " 범위를벗어난값입니다.") =IF(AND(1<A24, A24<100), A24, " 범위를벗어난값입니다.") 위숫자중둘째숫자가 1과 100 사이에있으면그수를표시하고그렇지않으면메시지를표시합니다. ( 범위를벗어난값입니다 ) 위숫자중첫째숫자가 1 과 100 사이에있으면그수를표시하고그렇지않으면메시지를표시합니다. (50) 범위를벗어난값입니다. 50

21 응용 AND 함수을이용하여품번과 CAGE 가유일한품목을하나씩만식별해보세요데이터는품번으로정렬된상태입니다. 순번 품번 CAGE 결과 1 M23053/ FALSE 2 M23053/ FALSE 3 M23053/ FALSE 4 M23053/ TRUE 5 M23053/ TRUE 6 M23053/ FALSE 7 M23053/ TRUE 8 M23053/ FALSE 9 M23053/ TRUE 10 M23053/ TRUE 11 M23053/ FALSE D54 의수식 =AND(B54=B55,C54=C55) 을살펴보면먼저 B54 의값 "M23053/ " 과 B55 의값 "M23053/ " 을비교합니다. 두개의품번은서로같으므로 TRUE 를반환하고다음으로 C54 의값 "81343" 과 C55 의값 "96906" 을비교하여 FALSE 값을반환합니다. 결과적으로수식은 =AND(TRUE, FALSE) 가되어 D54 에는 FALSE 값을반환하게되는것입니다. 만약 C54 의값을 으로바꾸면 =AND(TRUE, TRUE) 둘다 TRUE 가되므로 TRUE 가될것입니다. 위의표에서결과중 TRUE 값을살펴보면같은품번과 CAGE 를사용하고있는데이터가바로밑에있을것입니다. 반대로 FALSE 값은여러개의중복값중하나만표시되어있습니다. 표의값중 FALSE 값만필터링하면아래와같이 6 개의유일한서로다른품목의리스트구할수있습니다. 순번 품번 CAGE 1 M23053/ M23053/ M23053/ M23053/ M23053/ M23053/

22

23 트

24 OR 인수중하나가 TRUE 이면 TRUE 를, 모든인수가 FALSE 이면 FALSE 를반환합니다. 구문 TRUE 또는 FALSE 로계산될수있는조건 OR(logical1,logical2,...) 1 개부터 30 개까지사용할수있습니다. 주의 인수는논리값이포함된배열이나참조에서또는 TRUE 나 FALSE 와같은논리값을계산해야합니다. 배열또는참조인수에텍스트또는빈셀이있는경우그값들은무시됩니다. 지정한범위가논리값을포함하지않은경우 #VALUE! 오류값을표시합니다. 예제 예제수식설명 ( 결과 ) 결과 1 =OR(TRUE) 하나의인수가 TRUE 입니다. (TRUE) TRUE 2 =OR(1+1=1,2+2=5) 모든인수는 FALSE입니다. (FALSE) FALSE 최소하나이상의인수가 TRUE입니다. 3 =OR(TRUE,FALSE,TRUE) TRUE (TRUE)

25 응용 OR 함수를이용하여품번이 MS 로시작하거나 CAGE 가 인품목을식별해보세요 순번 품번 CAGE 결과 1 85F F FALSE 2 NAS FALSE 3 NAS FALSE 4 NAS1149C0432R FALSE 5 85TV1028C416L FALSE 6 85F F FALSE 7 MS C TRUE 8 NAS1149C0332R FALSE 9 MS TRUE 10 MS21251-A2S TRUE AND 함수와차이점은 AND 함수는모든인수가참 (TRUE) 이되어야참값 (TRUE) 을반환하지만 OR 함수는인수중하나만참 (TRUE) 값이면참값 (TRUE) 을반환합니다. D61 의수식 =OR(LEFT(B61,2)="MS",C61="96906") 에서 LEFT(B61,2)="MS" 는 B61 의값 "MS C9" 의문자열중첫문자부터 2 번째자리까지문자를반환하므로첫번째인수는 "MS"="MS" 가되어참 (TRUE) 값을반환합니다. 두번째인수 C61="96906" 에서 C61 의값은 "80205" 이므로 "80205"="96906" 가되어거짓 (FALSE) 값을반환합니다 D61 의수식에첫번째와두번째인수의결과값을대입해보면수식은 =OR(TRUE,FALSE) 가되어 D61 에는참 (TRUE) 값을반환합니다. 위표의결과중 TRUE 값만필터링하면아래와같은결과를얻을수있습니다. 순번 품번 CAGE 7 MS C MS MS21251-A2S 참고 NOT : 인수의논리값을역으로반환합니다. 예제수식설명 ( 결과 ) 결과 3 =NOT(OR(TRUE,FALSE)) OR 함수에서최소하나이상의인수가 TRUE 이므로 TRUE 이나 NOT 함수로인하여역의논리값인 FALSE 값을반환합니다. FALSE

26 IF 지정한조건이 TRUE 일때와 FALSE 일때각각다른값을반환합니다. IF 를사용하여값이나수식에대한조건부검사를수행합니다. 구문 TRUE 또는 FALSE 로계산될수있는조건 IF(logical_test,value_if_true,value_if_false) logical_test 가 TRUE 이고 value_if_true 가공백인경우, 이인수는 0( 영 ) 을반환합니다 TRUE 라는단어자체를표시하려면이인수에 TRUE 논리값을사용합니다. value_if_true 는다른수식이될수도있습니다. logical_test 가 FALSE 이고 value_if_false 는생략된경우 ( 즉, value_if_true 뒤에쉼표가없을경우 ) FALSE 논리값이표시됩니다. logical_test 가 FALSE 이고 value_if_false 가공백일경우 ( 즉, value_if_true 뒤괄호다음에쉼표가있을경우 ) 0( 영 ) 이반환됩니다. value_if_false 는다른수식이될수도있습니다. 주의 value_if_true 와 value_if_false 인수로 IF 함수를 7 개까지중첩하여사용할수있습니다 value_if_true 와 value_if_false 인수가계산될때 IF 함수는이들에의해계산된값을반환합니다. IF 함수의인수중에배열이있으면 IF 문이수행될때그배열의모든요소가계산됩니다. 셀범위내의텍스트의문자열이나숫자의개수를세려면 COUNTIF 워크시트함수를사용하십시오. 범위내의텍스트의문자열이나숫자를기준으로하여합계를계산하려면 SUMIF 워크시트함수를사용하십시오. 예제 1 조건이 FALSE 일때반환할값 조건이 TRUE 일때반환할값 실제경비 예상경비 예제 수식 설명 ( 결과 ) 결과 1 =IF(B40>1000," 확인 ","") 첫째행의실제경비가 "1000" 을초과하는지여부를확인합니다. ( 확인 ) 확인 2 =IF(B40>C40," 예산초과첫째행이예산을초과하는지여부를확인예산초과 ","OK") 합니다. ( 예산초과 ) 3 =IF(B42>C42,"Over 둘째행이예산을초과하는지여부를확인 OK Budget","OK") 합니다. (OK)

27 예제 2 점수 90 이상 A B C D 59 이하 F 결과값 위의기준에따라점수에따른등급을지정하는예제입니다. 점수 수식 결과 45 =IF(A61>89,"A",IF(A61>79,"B", F 90 =IF(A62>89,"A",IF(A62>79,"B", A 78 =IF(A63>89,"A",IF(A63>79,"B", C 응용 아래의표에서 SMR 이 PA* 로시작되는품목에대해서 "Y" 를아니면 "N" 를적용해보세요. 순번 품번 SMR 결과 1 85Z PAODD Y 2 85E XC N 3 85VC PAOZZ Y 4 NAS1580A3T5 PAOZZ Y 5 85VC PAODD Y 6 85SW PAOLD Y 8 85F XC N D71 의수식 =IF(LEFT(C71,2)="PA","Y","N") 에서 LEFT(C71,2)="PA" 는 C71 의문자열 "PAODD" 의첫문자부터 2 번째자리까지의문자를반환하므로 "PA"="PA" 가되어참 (TRUE) 이되고 D71 의수식은 =IF(TRUE,"Y","N") 와같으므로참일때의값 "Y" 를반환합니다. LEFT 함수의자세한설명은 LEFT 함수설명란을참고하세요

28 IS 함수여기서는값또는참조유형을검사할때사용하는 9 개의함수를설명합니다. IS 함수라고하는이러한함수들은값의유형을검사하고그결과에따라 TRUE 또는 FALSE 를반환합니다. 예를들어 ISBLANK 함수는값이빈셀을참조하면 TRUE 를, 그렇지않으면 FALSE 를반환합니다. 구문 ISBLANK(value) ISERR(value) ISERROR(value) ISLOGICAL(value) ISNA(value) ISNONTEXT(value) ISNUMBER(value) ISREF(value) ISTEXT(value) 검사 value 의값으로는공백 ( 빈셀 ), 오류값, 논리값, 텍스트, 숫자, 참조값또는이러한항목을참조하는이름을사용할수있습니다. 함수 ISBLANK ISERR ISERROR ISLOGICAL ISNA ISNONTEXT ISNUMBER ISREF ISTEXT TRUE 가반환되는경우값이빈셀을참조하는경우 값이 #N/A 외의오류값을참조하는경우 값이오류값 (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!) 을참조하는경우 값이논리값을참조하는경우 값이 #N/A ( 사용할수없는값 ) 오류값을참조하는경우 값이텍스트가아닌항목을참조하는경우. 이함수는값이빈셀을참조하는경우에 TRUE를반환합니다. 값이숫자를참조하는경우값이참조를참조하는경우값이텍스트를참조하는경우 주의 IS 함수의값인수는변환되지않습니다. 예를들어숫자를지정해야하는대부분의다른함수에서는텍스트값 "19" 가숫자 19 로변환되지만, 수식 ISNUMBER("19") 에서는텍스트값 "19" 가숫자로변환되지않으므로 ISNUMBER 함수는 FALSE 를반환하게됩니다. IS 함수는수식에서계산결과를검사할때유용합니다. IF 함수와함께사용하면수식에서오류를쉽게찾을수있습니다.

29 예제 순번데이터 1 금 2 지역1 3 #REF! #N/A 예제 수식 설명 ( 결과 ) 결과 1 =ISLOGICAL(TRUE) TRUE가논리값인지여부를확인합니다. TRUE 2 =ISLOGICAL("TRUE") "TRUE" 가논리값인지여부를확인합니다. FALSE 3 =ISNUMBER(4) 4 가숫자인지여부를확인합니다. TRUE 4 =ISBLANK(B48) A2 셀이공백인지여부를확인합니다. FALSE 5 =ISERROR(B49) #REF! 가오류인지여부를확인합니다. TRUE 6 =ISNA(B49) #REF! 가 #N/A 오류인지여부를확인합니다. FALSE 7 =ISNA(B51) #N/A 가 #N/A 오류인지여부를확인합니다. TRUE 8 =ISERR(B51) #N/A 가오류인지여부를확인합니다. FALSE 9 =ISNUMBER(B52) 가숫자인지여부를확인합니다. TRUE 10 =ISTEXT(B50) 지역 1 이텍스트인지여부를확인합니다. TRUE 11 =IF(ISTEXT(B49)," 문자 "," 숫자 ") " 금 " 이텍스트인지판별하고텍스트일경우 " 문자 " 라고표현합니다. 문자

30 응용 아래의표에서수량항목의값중숫자인것은동일한품번의수량의합으로나타내고숫자가아닌것은 " 오류 " 라고나타내보세요. 순번 품번 수량 결과 1 85TV V 오류 2 85TV1359W TV V 오류 4 D38999/20JE26SN TV M7928/ TV TV D38999/20JE26SN D38999/26JJ35SN M39029/5-115 V 오류 12 M39029/ V 오류 13 D38999/20JE26SN M39029/ V 오류 15 M39029/ V 오류 16 M7928/ TV1359W TV V 오류 19 M7928/ D98 의수식 =IF(ISNUMBER(C98), SUMIF($B$98:$B$116,B98,$C$98:$C$116)," 오류 ") 는 IF 함수의조건부분인 ISNUMBER(C98) 와조건이 TRUE 일경우반환되는 SUMIF($B$98:$B$116,B98,$C$98:$C$116) 그리고조건이 FALSE 일경우반환되는 " 오류 " 로나누어생각할수있습니다. 첫번째로조건부분을보면 C98 의값이 "V" 이므로 ISNUMBER(C98) 은 FALSE 가되고따라서 IF 함수는 " 오류 " 를반환합니다. 순번 1 의수량부분의 "V" 값을숫자 3 으로한번바꿔보세요결과부분의값이변하는것을볼수있을것입니다. 3 을입력하는순간 IF 함수의조건부분인 ISNUMBER(C98) 는 TRUE 를반환하고따라서이번에는 SUMIF 함수를수행하므로품번 "85TV1317-2" 의모든수량을합해서그값을반환합니다. IF 함수와 SUMIF 함수의자세한설명은각설명부분을참고하세요

31 CHOOSE index_num 을사용하여인수값목록에서값을반환합니다. 구문 인수가선택되는값 CHOOSE(index_num,value1,value2,...) index_num이 1일때의인수 index_num이 2일때의인수 index_num은 1과 29 사이의숫자이거나, 1과 29 사이의숫자가들어있는셀에대한참조또는수식이어야합니다. index_num이 1이면 value1을, 2이면 value2 등의방식으로계속해서반환됩니다. index_num이 1보다작거나목록의마지막값의수보다크면 #VALUE! 오류값이반환됩니다. index_num이분수이면소수점이하를잘라서정수로변환됩니다. value1,value2,... CHOOSE 함수가 index_num에따라값이나작업을선택할때사용하는인수입니다. 1개부터 29개까지지정할수있습니다. 인수는숫자, 셀참조영역, 정의된이름, 수식, 매크로함수, 텍스트등이될수있습니다. 주의 index_num 이배열이면 CHOOSE 함수가계산될때모든 value 가계산됩니다. CHOOSE 함수에서 value 인수는단일값일수도있고참조영역일수도있습니다. 예제 DATA1 DATA2 DATA3 1 첫째 못 2 둘째 나사 3 셋째 너트 4 완료 볼트 예제 수식 설명 결과 1 =CHOOSE(A34,B34,B35, A34가참조하는값이 1 이므로 B34 의값첫째 B36,B37) 을반환합니다. 3 =CHOOSE(A36,C34,C35, A36이참조하는값이 3 이므로 C36 의값너트 C36,C37) 을반환합니다. 4 =CHOOSE(4,C34,C35,C3 index_num 이 4 이므로 C37 의값을반볼트 6,C37) 환합니다. 5 =CHOOSE(A36," 월 "," 화 A36이참조하는값이 3 이므로세번째인 "," 수 "," 목 ") 수인 " 수 " 의값을반환합니다. 수

32 6 =CHOOSE(B34,C35,C36, C37,C38) index_num 이 " 첫째 " 인텍스트로오류값 #VALUE! 를반환합니다. #VALUE! 응용 각품목의 LEVEL 을기입하세요 NO LCN Part No LEVEL 1 A 85Z A 2 A1 WUC14000 B 3 A1A WUC14A00 C 4 A1AA 85E D 5 A1AA001 85VC E 6 A1AA002 NAS1580A3T5 E 7 A1AAA 85VC E 8 A1AB 85F D 9 A1AB001 NAS1149C0463R E 10 A1AB002 NAS E 11 A1ABA 85F E 12 A1ABA001 MS24693-C4 F 13 A1ABAA 85VC F 14 A1ABAB 85VF F CHOOSE( 응용 ) 참조

33 응용 각품목의 LEVEL 을기입하세요규칙을참고하세요 규칙 1.LCN 으로단품인지조립체인지먼저구별합니다. 단품의경우는뒤의세자리가숫자로구성됩니다. 그외품목은모두조립체입니다. 2. 조립체의경우에는 LCN 의문자열길이로 LEVEL 이식별가능합니다. 예를들어 LCN 이 "A" 일경우문자열길이가 1 이므로 "A" LEVEL 입니다. LCN 이 "A1A" 일경우문자열길이가 3 이므로 "C" LEVEL 입니다. 단, LEVEL CODE 에알파벳 "O","I" 는쓰지않습니다. 3. 단품의경우에는실제의문자열보다 2 자리적게생각하시면됩니다. 예를들어 LCN 이 "A1AA001" 일경우문자열길이가 7 자리로 LEVEL 이 "G" 가되어야겠지만실제 LEVEL 은문자열이 5 자리의경우와같이 "E" 가됩니다. NO LCN Part No LEVEL 1 A 85Z A 2 A1 WUC14000 B 3 A1A WUC14A00 C 4 A1AA 85E D 5 A1AA001 85VC E 6 A1AA002 NAS1580A3T5 E 7 A1AAA 85VC E 8 A1AB 85F D 9 A1AB001 NAS1149C0463R E 10 A1AB002 NAS E 11 A1ABA 85F E 12 A1ABA00 MS24693-C4 F 13 A1ABAA 85VC F 14 A1ABAB 85VF F D18 의수식 =CHOOSE(IF(ISNUMBER(VALUE(RIGHT(B18,3))),LENB(B18)-2,LENB(B18)),"A","B","C","D","E","F","G","H","J","K","L","M","N","P","Q","R","S","T","U","V","W","X","Y","Z") 를살펴보면 CHOOSE 함수이외에도 IF,ISNUMBER, VALUE, RIGHT, LENB 함수등을중첩해서사용하므로상당히복잡해보일것입니다. 하지만이것을하나씩만놓고보면그렇게어렵지않다는것을알수있을것입니다. 먼저조건을다시한번보세요. 1.LCN 으로단품인지조립체인지먼저구별합니다. 단품의경우는뒤의세자리가숫자로구성됩니다. 그외품목은모두조립체입니다. 1 번조건을보면먼저 LCN 의뒤 3 자리가숫자인지숫자인지를구분해야합니다. LCN 의뒤 3 자리를가져오는함수가 RIGHT(B18,3) 이고 VALUE 함수는텍스트를숫자로변환시키는함수입니다. 문자열에속해있던숫자를참조할경우형식은여전히텍스트로되어있기때문에이를숫자형으로바꾸기위해 VALUE 함수가필요한것입니다.

34 ISNUMBER 함수는 VALUE 함수로변환된어떤값이숫자인지판별하는함수입니다. 여기까지이해가되셨으면아래표의함수들을자세히한번살펴보세요. 함수하나씩을풀어서나열하였습니다. NO LCN Part No RIGHT 함수 VALUE 함수 ISNUMBER 함수 1 A 85Z A #VALUE! FALSE 2 A1 WUC14000 A1 #VALUE! FALSE 3 A1A WUC14A00 A1A #VALUE! FALSE 4 A1AA 85E AA #VALUE! FALSE 5 A1AA001 85VC TRUE 6 A1AA002 NAS1580A3T TRUE 7 A1AAA 85VC AAA #VALUE! FALSE 8 A1AB 85F AB #VALUE! FALSE 9 A1AB001 NAS1149C0463R TRUE 10 A1AB002 NAS TRUE 11 A1ABA 85F ABA #VALUE! FALSE 12 A1ABA00 MS24693-C TRUE 13 A1ABAA 85VC BAA #VALUE! FALSE 14 A1ABAB 85VF BAB #VALUE! FALSE 위의표에서 VALUE 함수항목을보시면 #VALUE! 에러가발생했는데이는 VALUE 함수에문자를넣었기때문입니다. 오류값또한숫자는아니므로상관은없습니다. 여기까지조건 1 을만족시키기위해서한작업이었습니다. 다시한번설명하면 LCN 뒤 3 자리가숫자이면단품이므로 ISNUMBER 함수항목에 TURE 인품목들이단품이겠지요. 당연히나머지들은조립체입니다. 다음은 2, 3 번째조건을한번살펴보겠습니다. 2. 조립체의경우에는 LCN 의문자열길이로 LEVEL 이식별가능합니다. 예를들어 LCN 이 "A" 일경우문자열길이가 1 이므로 "A" LEVEL 입니다. LCN 이 "A1A" 일경우문자열길이가 3 이므로 "C" LEVEL 입니다. 단, LEVEL CODE 에알파벳 "O","I" 는쓰지않습니다. 3. 단품의경우에는실제의문자열보다 2 자리적게생각하시면됩니다. 예를들어 LCN 이 "A1AA001" 일경우문자열길이가 7 자리로 LEVEL 이 "G" 가되어야겠지만실제 LEVEL 은문자열이 5 자리의경우와같이 "E" 가됩니다. 이미조립체와단품은구별된상태이므로이제남은일은조립체일때의 LCN의문자길이와단품일때의문자길이만구하면되겠지요? 문자길이를구하는함수는 LENB함수입니다. 참고로 LEN함수와동일하나 LEN 함수는문자열의문자수를구하지만 LENB 함수는문자열의바이트수를 구할수있습니다. 예 ) LEN 함수 LENB 함수 수식 =LEN(" 서전 ") =LENB(" 서전 ") 결과값 2 4 조립체일경우 LCN 문자열에서의문자길이를그대로사용하면됩니다. 이것을수식으로표현하면 =LENB("LCN 이있는셀주소 ") 가되겠지요단품일경우 LCN 문자열의길에어서 2 자리만큼빼면되구요. 이것또한수식으로표현하면 =LENB("LCN 이있는셀주소 ")-2 가됩니다.

35 조건 2 와 3 을다시정리하보면조립체즉 ISNUMBER 의결과가 FALSE 일경우 LENB("LCN 이있는셀주소 ") 이고 TRUE 일경우 LENB("LCN 이있는셀주소 ")-2 가되면됩니다. 이것도수식으로표현하면 IF(ISNUMBER 결과, LENB(LCN 이있는셀주소 )-2,LENB(LCN 이있는셀주소 )) 가됩니다. 여기까지이해가되셨으면아래의수식을한번살펴보세요. NO LCN Part No ISNUMBER 함수결과 IF 함수 CHOOSE 함수 1 A 85Z FALSE 1 A 2 A1 WUC14000 FALSE 2 B 3 A1A WUC14A00 FALSE 3 C 4 A1AA 85E FALSE 4 D 5 A1AA001 85VC TRUE 5 E 6 A1AA002 NAS1580A3T5 TRUE 5 E 7 A1AAA 85VC FALSE 5 E 8 A1AB 85F FALSE 4 D 9 A1AB001 NAS1149C0463R TRUE 5 E 10 A1AB002 NAS TRUE 5 E 11 A1ABA 85F FALSE 5 E 12 A1ABA00 MS24693-C4 TRUE 6 F 13 A1ABAA 85VC FALSE 6 F 14 A1ABAB 85VF FALSE 6 F 이제마지막으로 CHOOSE 함수를사용하면결과가처음표에보셨던것과같은나올것입니다. 지금까지사용한함수를한번에중첩해서쓰면 D18 의함수가되겠지요? 이렇게길게설명한이유는여러가지조건에함수가여러개쓰여질경우하나씩먼저생각해보면쉽게답이나올수있을것같아서장황하게설명을한번해보았습니다. 설명이너무길어오히려이해가더안되면어떻하나하는생각도드네요..

36 VLOOKUP 원하는값을 Table_array( 선택영역 ) 의첫열에서찾아그값이있는그행에서지정한열의값을찾아그값을표시합니다. 구문 첫번째열에서찾을값 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 찾을조건정보테이블부터찾을값의열번호데이터를찾을정보테이블 Lookup_value : table_array 의첫번째열에기록된내용중사용자가찾고자하는검색조건으로숫자, 참조영역, 또는문자열로지정할수있다. Table_array : 검색하려는데이터들이포함된셀을시작으로, 비교하여가져올데이터가있는열까지의영역으로 table_array 의첫열에는 lookup_value 와비교하는이포함되어있어야합니다. Col_index_num : table_array 의첫번째열에서 lookup_value 가기록된행을찾은후동일행에서얻고자하는데이터가포함되어있는열번호를말합니다. 따라서, col_index_num 로지정한숫자가 1 보다작거나 table_array 의전체열수보다크면각각 #VALUE! 오류, #REF! 오류가나타난다 Range_lookup : 정확하게일치하는값을찾을때는 false 입력하고, 근사값을찾고자할때는 true 를입력하거나생략을합니다. 단근사값을정확히찾을려면 table_array 영역의첫열기준으로반드시올림차순 (1,2,3..., abc..., 가나다... 순 ) 으로정렬을되어야정확한근사값을얻을수있습니다. 예제 표 1 순번 이름 고향 나이 1 이팔삼 사천 31 2 이준열 서울 32 3 김진균 모름 29 4 강충덕 부산 30 5 정해규 사천 31 표 2 순번 이름 나이 1 강충덕 2 김진균 3 이준열 4 이팔삼 5 이성현

37 표 1 의에서이름으로나이를찾아서표 2 에기입해보해요. 먼저표 1 의데이터가현재 5 개만있지만이데이터가수천개라생각하고편집메뉴의찾기를이용해입력한다고가정해보겠습니다. 정확한데이터를찾기위해표 1 의 " 이름 " 항목의이름들의범위를지정합니다. 단축키 Ctrl+F 를눌러찾기대화상자를열고표 2 의첫번째이름부터표 1 에서찾아서이름에해당하는나이항목을값을확인하고표 2 에기입할것입니다. " 강충덕 " 의나이는표 1 의순번 4 의이름에서찾아질것이고이름에서오른쪽옆으로 3 번째칸에서나이 30 을가져와표 2 에입력하겠지요. 다음으로표 2 의두번째이름을또같은방법으로찾아나이를입력할것입니다. 만약수천개의데이터가있다면나이항목을다채우기위해많은시간이필요하게될것입니다. 이번에는 VLOOKUP 함수를한번이용해보겠습니다. 표 1 순번 이름 고향 나이 1 이팔삼 사천 31 2 이준열 서울 32 3 김진균 모름 29 4 강충덕 부산 30 5 정해규 사천 31 표 2 순번 이름 나이 고향 1 강충덕 30 부산 2 김진균 29 모름 3 이준열 32 서울 4 이팔삼 31 사천 5 이성현 #N/A #N/A

38 순번 1 의수식을살펴보면 =VLOOKUP(B95,$B$87:$D$91,3,FALSE) 으로 B95 는이름항목의 " 강충덕 " 즉찾기대화상자의찾을내용을입력하는것과같습니다. 그리고두번째인수인 $B$87:$D$91 는찾을이름과나이가들어있는데이터의범위입니다. 찾기대화상자를이용할때이름부분을범위로잡아그범위안의데이터만찾았던것처럼 table_array 의범위도찾을값즉이름을포함해야하며찾을값이항상제일앞열에있어야합니다. 표 2 의이름들이표 1 의 B 열에있으므로 B 열부터꼭범위를잡아야지만나이값을가져올수있습니다. 만약 A87:D91 로범위를잡았다면표 1 의순번항을범위로잡아놓고찾기대화상자로이름 " 강충덕 " 을찾는거나마찬가지가되는것입니다. A 열에는 " 강충덕 " 이름이없으므로그리고셀범위를절대주소로지정한것은함수를복사해서사용할경우참조범위가변경되기때문에이를방지하기위해서입니다. 다음으로이름부터 3 번째칼럼에나이값이있으므로세번째인수는 "3" 이됩니다. 고향을찾고싶으면 "3" 대신 "2" 를입력하면되겠지요. 마지막으로찾는이름이똑같을때만나이값을가져오기위해네번째인수는 "FALSE" 로하였습니다. "True" 를입력하거나 range_lookup 인수를생락하면근사값으로찾아서값을가져오므로원하는값을얻지못할수가있으니주의하세요. 참고로 =VLOOKUP(B95,$B$87:$D$91,3,) 이수식처럼마지막인수자리에 "," 는입력하고 range_lookup 값만생략했을경우에는 "FALSE" 를입력한결과와같습니다표 2 의순번 5 번째이름인 " 이성현 " 은표 1 에없으므로 "#N/A" 로에러표시를합니다. VLOOKUP( 응용 ) 참조

39 응용 1 표 1 과 2 를비교하여표 2 를기준으로품번과 SMR 이바뀐품목을찾아보세요 표1 표2 순번 품번 SMR 순번 품번 SMR 1 MS LS PAOZZ 1 MS LS PAOZZ 2 MS RS PAOZZ 2 MS RS PAOZZ 3 MS PAOZZ 3 MS PAOZZ 4 MS A PAOZZ 4 MS A PAOZZ 5 NAS1149C0332R PAOZZ 5 NAS1149C0332R PCOZZ 6 MS24693-C4 PAOLD 6 MS24693-C4 PAOLD 7 85VC PAOFF 7 85VC PAOFF 8 85VF XC 8 85VF XC 9 85F PAOZZ 9 85F PAOFF 10 85F PAOZZ 10 85F PAOZZ 표 순번 품번 SMR VLOOKUP SMR 비교 품목변경여부 결과 1 MS LS PAOZZ PAOZZ SMR동일 SMR동일 2 MS RS PAOZZ PAOZZ SMR동일 SMR동일 3 MS PAOZZ PAOZZ SMR동일 SMR동일 4 MS A PAOZZ PAOZZ SMR동일 SMR동일 5 NAS1149C0332R PCOZZ PAOZZ SMR다름 SMR변경 6 MS24693-C4 PAOLD PAOLD SMR동일 SMR동일 7 85VC PAOFF PAOFF SMR동일 SMR동일 8 85VF XC XC SMR동일 SMR동일 9 85F PAOFF #N/A #N/A 품번확인 품번확인 10 85F PAOZZ PAOZZ SMR동일 SMR동일 표 2 1 칼럼의 VLOOKUP 함수를보면 =VLOOKUP(B21,$B$7:$C$16,2,FALSE) 는 B21 의값 "MS LS" 으로 $B$7:$C$16 범위의 B 열에서 "MS LS" 를찾아서 "MS LS" 에서 2 번째칼럼의값 "PAOZZ" 을가져오라는말입니다. "FALSE" 는정확히일치하는값만가져오라는뜻이구요. 2 번칼럼은 VLOOKUP 으로불러온표 1 의 SMR 과표 2 의 SMR 을비교하여같으면 SMR 동일다르면 "SMR 다름 " 으로표현하였습니다. 수식으로는 =IF(C21=D21,"SMR 동일 ","SMR 다름 ") 과같습니다. IF 함수는 IF 함수설명란을참고하세요. VLOOKUP 함수는찾는값이찾을범위내에없을경우 "#N/A" 에러값을반환합니다. 따라서 3 번항목은 ISERROR 함수를이용하여에러여부를판별하고 IF 함수로판별된값이에러일때에는 " 품번확인 " 이라고표기합니다. 수식은다음과같습니다. =IF(ISERROR(E21)," 품번확인 ","") ISERROR 함수와 IF 함수는 ISERROR 함수와 IF 함수설명란을참고하세요. 4 항목의값은 1,2,3 의항목들을한꺼번에수식으로표현한것입니다. 1,2,3 의항목이이해가되셨으면 4 항목은쉽게이해가될것입니다.

40 응용 2 아래조건을만족하도록표 3 의결과값을채우세요. 조건 1 : EC 가 1 일경우결과란체 "V" 를입력한다. 조건 2 : EC 가 3 일경우결과란체 "N" 를입력한다. 조건 3 : EC 가 5 일경우결과란체 "S" 를입력한다. 조건 4 : EC 가 7 일경우결과란체 "P" 를입력한다. 표3 순번 품번 EC 결과 1 M85049/95-16A MS MS H MS24693-C MS MS N 1 이경우는 IF 문으로쉽게답을구할수있지만 VLOOKUP 으로한번해보겠습니다. VLOOKUP 의경우우선조건을만족하는참조표를먼저만들어야하는번거로움이있지만대신 IF 문은조건이 7 개이상일경우적용하기가곤란하다는문제점을 VLOOKUP 함수로해결할수도있습니다. 먼저아래와같이위의조건을만족하는참조표를만듭니다. 참조표 EC 반환값 1 V 3 N 5 S 7 P 표3 순번 품번 EC 결과 1 M85049/95-16A 1 V N 3 MS S 4 MS P H N 6 MS24693-C26 1 V 7 MS S P N 10 MS N 1 V 조건을만족하는참조표만잘만든다면조건이많을경우 IF 함수보다유용할수도있을것입니다.

41 CONCATENATE 여러문자열항목을한문자열로합칩니다. 구문 CONCATENATE (text1,text2,...) Text1, text2,... 한텍스트로합치려는텍스트항목으로 30개까지지정할수있습니다. 텍스트항목에는텍스트, 숫자, 단일셀참조영역등을지정할수있습니다. 주의 CONCATENATE 대신 "&" 연산자를사용하여텍스트항목을결합할수있습니다. 예제 DATA1 DATA2 봄옷벌 2 예제수식설명결과 1 =CONCATENATE(" 가을 "," 하늘 ") 2 =" 가을 "&" 하늘 " 문자열 ( 숫자, 셀등 ) 문자열 ( 숫자, 셀등 ) 문자열 " 가을 " 과 " 하늘 " 을하나의문자열로합칩니다. & 를사용하여문자열 " 가을 " 과 " 하늘 " 을하나의문자열로합칩니다. 가을하늘 가을하늘 3 =CONCATENATE(A22,B2 2,B23,A23) 4 =A22&B22&B23&A23 DATA1 과 DATA2 를참조하여 " 봄옷 2 벌 " 이라는문자열을만듭니다. & 를사용하여 " 봄옷 2 벌 " 이라는문자열을만듭니다. 봄옷 2 벌 봄옷 2 벌 5 = "1 과 2 의합은 " & SUM(1,2) &" 입니다 " 문자열과셀참조이외에도함수를함께사용할수있습니다. 1 과 2 의합은 3 입니다

42 EXACT 두문자열을비교하여정확하게일치하면 TRUE 를반환하고일치하지않으면 FALSE 를반환합니다. 구문 EXACT(text1,text2) 주의 대소문자를구분합니다. "=" 를대신사용할수있습니다. "=" 경우대소문자는구분하지않습니다. 예제 문자열 ( 숫자, 셀등 ) 문자열 ( 숫자, 셀등 ) DATA1 가을 EXCEL DATA1 가을 excel 예제1 수식 설명 결과 1 =EXACT(A22,B22) DATA1 과 DATA2의값을비교합니다. TRUE 2 =EXACT(A23,B23) 3 =" 겨울 "=" 겨울 " 4 =A23=B23 5 =EXACT(3,1+2) DATA1 과 DATA2 의값을비교합니다. 대소문자를구분하므로결과는 FALSE 입니다. 비교연산자인 "=" 를사용하여같은결과를얻을수있습니다. 비교연산자인 "=" 를사용하여같은결과를얻을수있습니다. 대소문자구분은하지않습니다. 인수로문자열뿐아니라수식도사용가능합니다. FALSE TRUE TRUE TRUE

43 응용 아래의품목중중복되는품목을제외하고유일한품목하나씩만식별해보세요 순번품번 1 NAS1581C4T13 2 NAS1581C4T PF NAS NAS PF PF NAS1581C4T14 9 MS20470AD PF NAS4450S PF NAS1581C4T PF PF 우선데이터들을품번으로정렬합니다. 순번 품번 결과 6 85PF TRUE 10 85PF TRUE 15 85PF FALSE 12 85PF FALSE 3 85PF FALSE 14 85PF FALSE 7 85PF FALSE 9 MS20470AD5-12 FALSE 1 NAS1581C4T13 FALSE 2 NAS1581C4T14 TRUE 8 NAS1581C4T14 TRUE 13 NAS1581C4T14 FALSE 11 NAS4450S05-05 FALSE 4 NAS FALSE 5 NAS FALSE C71 수식을살펴보세요수식 =EXACT(B71,B72) 에서 B71 의값 "85PF " 과 B72 의값 "85PF " 을서로비교합니다. 서로같은값을가지고있으므로결과는 TRUE 입니다. 품번으로정렬을했기때문에같은값이있으면바로밑에오게됩니다. 이번에는결과가 FALSE 인곳을한번보세요품번이바로아래의값과서로다른것을확인할수있을것입니다. 여러개의품번이서로중복되었다하더라도바로아래의값만비교하기때문에중복된품번중하나의값만 FALSE 값을가지게됩니다. 따라서결과의 FALSE 값만필터링하면중복되지않은유일한품번을식별할수있습니다.

44 FIND,SEARCH FIND 함수는다른텍스트문자열 (within_text) 에서텍스트문자열 (find_text) 을찾아서 within_text 의첫째문자에서 find_text 의시작위치번호를반환합니다. SEARCH 함수를사용하여다른문자열에서텍스트문자열을찾을수도있지만 SEARCH 함수와달리 FIND 함수는대 / 소문자구분이있으며와일드카드문자를사용할수없습니다. FINDB 함수는다른텍스트문자열 (within_text) 에서텍스트문자열 (find_text) 을찾아서 within_text 의첫째문자에서각문자가사용하는바이트수를기반으로 find_text 의시작위치번호를반환합니다. 이함수는더블바이트문자와함께사용합니다. SEARCHB 함수를사용하여다른텍스트문자열에서텍스트문자열을찾을수도있습니다. 구문 FIND(find_text,within_text,start_num) FINDB(find_text,within_text,start_num) SEARCH(find_text,within_text,start_num) SEARCHB(find_text,within_text,start_num) Start_num 검색을시작할문자를지정합니다. within_text에서첫문자의문자번호는 1입니다. start_num을생략하면 1로간주됩니다. SEARCH 함수는 find_text에와일드카드문자인물음표 (?) 와별표 (*) 를사용할수있습니다. 물음표는한문자에해당하고별표는일련의문자에해당합니다. 실제물음표나별표를찾으려면문자앞에물결표 (~) 를입력합니다. 주의 SEARCH 및 SEARCHB 는텍스트를검색할때대 / 소문자를구분하지않습니다. SEARCH 및 SEARCHB 는 FIND 및 FINDB 와비슷하지만 FIND 및 FINDB 는대 / 소문자를구분합니다. find_text 가 "" ( 빈문자열 ) 이면 FIND 는검색문자열의첫문자를찾습니다. 즉 start_num 번째또는첫째문자를찾습니다. find_text 가 within_text 에없으면 #VALUE! 오류값을반환합니다. start_num 이 0 보다크지않으면 #VALUE! 오류값을 예제 찾으려는문자열 검색을시작할문자 찾을문자열을포함하는문자열 예제수식설명결과 1 2 =FIND(" 오 "," 일이삼사오육칠팔 ",1) =SEARCH(" 이준열 "," 기술 2 실이준열 ",1) 문자열 " 일이삼하오육칠팔 " 에서 " 오 " 의위치를찾아서반환합니다. 문자열 " 기술 2 실이준열 " 에서 " 이준열 " 의시작문자위치를찾아서반환합니다. 5 6

45 응용 아래의표에서 LCN/Part Number 항목의자료들은 "/" 를기준으로 LCN 과 Part Number 로분류되어있습니다. "LCN/Part Number" 항목에서 Part Number 만추출해보세요 순번 LCN/Part Number Part Number 1 A/85Z Z A1AA/85E E A1AA001/85VC VC A1AA002/NAS1580A3T5 NAS1580A3T5 5 A1AAA/85VC VC A1AAB/85SW SW A1AB/85F F A1AB001/NAS1149C0463R NAS1149C0463R 9 A1AB002/NAS NAS A1ABA/85F F A1ABA001/MS24693-C4 MS24693-C4 12 A1ABA002/MIL-L MIL-L A1ABA003/MIL-C MIL-C 먼저 "LCN/Part Number" 항목의 DATA 를한번뚜러져하쳐다보세요. "/" 바로뒤문자부터끝까지가 "PART NUMBER" 이지요. 반대로 "/" 앞까지가 LCN 이되구요. 만약 LCN 길이가동일하다면쉽게답을낼수있습니다. 위의표에서 LCN 의길이가같은것을한번가져와보겠습니다. 순번 LCN/Part Number Part Number 11 A1ABA001/MS24693-C4 MS24693-C4 12 A1ABA002/MIL-L MIL-L A1ABA003/MIL-C MIL-C 순번 11,12,13 의경우에는 "LCN" 이모두 8 자리로동일합니다. "PART NUMBER" 는 10 번째자리부터끝까지가되구요. C78 의수식은 =MID(B78,10,LEN(B78)-9) 로 MID 함수와 LEN 함수로구성되어있습니다. MID 함수는문자열에서지정한자리수부터지정한자리수만큼문자열의일부를가져오는함수입니다. 수식으로표현하면 =MID(" 문자열 "," 가져와야될문자의시작위치 "," 가져와야될문자의끝위치 ") 간단한예로 =MID(" 일이삼사오 ",3,2) 라고하면 " 일이삼사오 " 의 3 번째글자부터뒤로 2 글자를가져옴니다. 그래서결과는 " 삼사 " 가됩니다. MID 함수는이해가되셨지요? 다시 C78 의수식은 =MID(B78,10,LEN(B78)-9) 로돌아와서 B78 의값 "A1ABA001/MS24693-C4" 로 10 번째자리 "M" 부터 10 자리를가져면 PART NUMBER 만남게됩니다. 순번 11 의값만있다면 =MID(B78,10,10) 로하면되겠지만 PART NUMBER 길이또한제각각이므로모든문자열에서 PART NUMBER 길이만알아내야합니다. 순번 11 의값 "A1ABA001/MS24693-C4" 의모든문자길이는 19 자이고여기서 LCN 부분과 "/" 문자까지가 9 자리입니다. 따라서모든문자의길이에서 9 자리를빼면 PART NUMBER 의길이가되겠지요?

46 문자열의길이를알아내는함수는 LEN 함수이고 =LEN(B78) 을하면결과는 19 가되고여기에 -9 를해주면 PART NUMBER 의길이인 10 이되는것입니다. LCN 의길이가항상일정할때는 PART NUMBER 의길이만구할수있으면쉽게문제가해결되었습니다. 이번에는 LCN 의길이를알아내는방법을한번생각해보세요. LCN 의끝은항상 "/" 로끝납니다. 결국 "/" 이몇번째자리에있는지만확인하면되는것이지요. 이것은 FIND 함수나 SEARCH 함수로쉽게찾으수있습니다. 순번 LCN/Part Number FIND 함수 1 A/85Z A1AA/85E A1AA001/85VC A1AA002/NAS1580A3T5 8 5 A1AAA/85VC A1AAB/85SW A1AB/85F A1AB001/NAS1149C0463R 8 9 A1AB002/NAS A1ABA/85F A1ABA001/MS24693-C A1ABA002/MIL-L A1ABA003/MIL-C 위의 FIND 의결과에서 +1 을하면 PART NUMBER 의시각위치가되고 -1 을하면 LCN 의끝문자위치가됩니다. 이제결과들을모두종합해보면 =MID(" 문자열 ",1PART NUMBER 의시작위치,2PART NUMBER 의끝위치 ) PART NUMBER 의시작위치는 FIND("/"," 문자열 ",1) PART NUMBER 의끝위치는 (" 전체길이 ") - ( "/" 위치 ) 이므로 LEN(" 문자열 ")-FIND("/"," 문자열 ",1) , 2 를위에대입하면 = MID(" 문자열 ", FIND("/"," 문자열 ",1)+1, LEN(" 문자열 ")-FIND("/"," 문자열 ",1)) 이됩니다. 순번 LCN/Part Number Part Number 1 A/85Z Z A1AA/85E E A1AA001/85VC VC 이제순번 1 의수식을보세요 =MID(B138,FIND("/",B138,1)+1,LEN(B138)-FIND("/",B138,1)) 수식의내용이머리속에다들어오세요..? 그럼성공한겁니다. 참고로 MID 함수의마지막인수인 LEN(B138)-FIND("/",B138,1) 에서 FIND 함수부분은생략해 PART NUMBER 의길이가전체문자열길이보다적으므로같은결과를얻을수있습니다.

47 LEFT, RIGHT, MID 다음사항은 LEFTB, RIGHTB, MIDB 에도적용됩니다. LEFT 는지정한문자수에따라텍스트문자열의첫문자부터원하는수만큼의문자를반환합니다. LEFTB 는지정한바이트수에따라텍스트문자열의첫문자부터원하는수만큼의문자를반환합니다. RIGHT 는지정한문자수에따라텍스트문자열의마지막문자부터지정된개수의문자를반환합니다. RIGHTB 는지정한바이트수에따라텍스트문자열의마지막문자부터지정한개수의문자를반환합니다. MID 는지정한문자수에따라문자열의지정한위치로부터지정한개수의문자를표시합니다. MIDB 는지정한바이트수에따라문자열의지정한위치로부터지정한개수의문자를표시합니다. 구문 LEFT(text,num_chars) LEFTB(text,num_bytes) 문자열 추출할문자수 RIGHT(text,num_chars) RIGHTB(text,num_bytes) 추출할첫문자위치 MID(text,start_num,num_chars) MIDB(text,start_num,num_bytes) text 추출할문자가들어있는텍스트문자열입니다. num_chars 추출할문자수입니다. num_chars는 0보다크거나같아야합니다. num_chars가텍스트보다길면텍스트전체가반환됩니다. num_chars를생략하면 1로간주됩니다. num_bytes LEFTB에서추출할문자수를바이트로지정합니다. Start_num 추출할첫문자의위치로서 Text에서첫문자는 start_num 1입니다. Num_chars MID 함수가텍스트에서반환할문자의개수를지정합니다. Num_bytes MIDB 함수가텍스트에서바이트단위로반환할문자의개수를지정합니다. 주의 num_chars 는 0 보다크거나같아야합니다. num_chars 가텍스트보다길면모든텍스트가반환됩니다. num_chars 를생략하면 1 로간주됩니다. Start_num 이텍스트의길이보다길면 MID 는 ""( 빈문자열 ) 을표시합니다.

48 Start_num 이텍스트의길이보다작고 Start_num 과 Num_chars 의합이문자열의길이를초과하면 MID 는마지막문자까지표시합니다. Start_num 이 1 보다작으면 #VALUE! 오류값을표시합니다. Num_chars 가음수이면 #VALUE! 오류값을반환합니다 Num_bytes 가음수이면 #VALUE! 오류값을반환합니다. 예제 순번데이터 1 판매가격 2 주식번호 3 아름다운하늘 예제 수식 설명 ( 결과 ) 결과 1 =LEFT(B61,2) 순번1 문자열의왼쪽 2 글자를반환합니판매다. 2 =LEFT(B62) 순번2 문자열의왼쪽첫글자를반환합니다 주 3 =RIGHT(B61,2) 순번1 문자열의오른쪽 2 글자를반환합니다. 가격 4 =RIGHT(B61) 순번2 문자열의오른쪽첫글자를반환합니다 격 5 =MID(B63,1,4) 순번3 문자열중첫자에서시작하여 4자아름다운를표시합니다. 6 =MID(B63,6,20) 순번3 문자열중여섯번째에서시작하여 20자를표시합니다. 하늘 7 =MID(B63,20,5) 시작위치가문자열의길이보다더크기때문에빈텍스트가표시됩니다. 응용 아래표에서 NSN 을 FSC( 군급분류부호 : 앞 4 자리 ) 와, NIIN( 국가품목식별부호 : 뒤 9 자리 ) 로나누어보세요 순번 품번 NSN FSC NIIN 1 AN3-4A AN3-5A AN AN AN4-21A FSC 는 LEFT 함수로쉽게추출할수있을것입니다. D80 번수식에서처럼 =LEFT(C80,4) 왼쪽 4 글자만가져오면됩니다. NIIN 은문자열의일부를조합해야되므로한가지함수로는좀힘들겠지요 "-" 가없다면 RIGHT 함수로쉽게자져올수있겠지만 NSN 의자리수를한번살펴볼보면 NSN : 자리수 :

49 모두 16 자리로구성되고그중앞의 4 자리가 FSC 그리고나머지 12 자리중 "-" 를제외한 9 자리가 NIIN 이됩니다. 따라서 1 자리수 6 부터 2 글자 "00" 와 2 자리수 9 부터 3 글자 "722", 3 자리수 13 부터 4 글자 "0393" 을하나의문자로연결하면 NIIN 을추출할수있습니다. 위설명을함수로표한해보면 1 MID(" ",6,2) 2 MID(" ",9,3) 3 RIGHT("MID(" ",4) 또는 MID(" ",13,4) 로나타낼수있고위의세식을하나로연결하는함수는 CONCATENATE 나 "&" 를이용해연결합니다.(CONCATENATE 설명란참고 ) E80 의수식 =MID(C80,6,2)&MID(C80,9,3)&RIGHT(C80,4) 이이해되시죠..

50 SUBSTITUTE 문자열에서 old_text 를 new_text 로바꿉니다. 문자열의특정텍스트를바꾸려면 SUBSTITUTE 를사용합니다. 문자열의특정위치에있는텍스트를바꾸려면 REPLACE 를사용합니다. 구문 문자열 SUBSTITUTE(text,old_text,new_text,instance_num) 몇번째 old_text 를바꿀지. 대신할텍스트 (~ 로 ) 바꿀텍스트 (~ 을 ) Instance_num Text에서몇번째에있는 Old_text를 New_text로바꿀것인지를지정하는수입니다. Instance_num을지정하면해당하는위치에있는 Old_text 만이바뀝니다. 그렇지않으면모든 Old_text가 New_text로바뀝니다. 예제 순번데이터 1 판매데이터 년 1/4분기 년 1/4분기 예제수식설명결과 =SUBSTITUTE(B25, " 판매 ", " 비용 ") =SUBSTITUTE(B26, "1", "2", 1) =SUBSTITUTE(B27, "1", "2", 3) 순번 1 의문자열 " 판매데이터 " 에서 " 판매 " 를 " 비용 " 으로대체합니다. 순번 2 의문자열에서 1 번째나오는 "1" 을 "2" 로대체합니다. 순번 3 의문자열에서 3 번째나오는 "1" 을 "2" 로대체합니다. 비용데이터 2021 년 1/4 분기 2011 년 2/4 분기

51 응용 아래표에서 NSN 을 FSC( 군급분류부호 : 앞 4 자리 ) 와, NIIN( 국가품목식별부호 : 뒤 9 자리 ) 로나누어보세요 순번 품번 NSN FSC NIIN 1 AN3-4A AN3-5A AN AN AN4-21A FSC 는 LEFT 함수로쉽게추출할수있을것입니다. D53 번수식에서처럼 =LEFT(C53,4) 왼쪽 4 글자만가져오면됩니다. 결과는 "5036" 이구요 NIIN 은앞의 4 글자를뺀나머지가되겠지요. 나머지는오른쪽에서부터 12 자리를가져오면되므로 RIGHT(C53,12) 이되고결과는 " " 이렇게됩니다. 순번 품번 NSN LEFT 함수 RIGHT 함수 1 AN3-4A AN3-5A AN AN AN4-21A 결과가비슷하긴한데불필요한문자가포함되어있습니다. "-" 문자는불필요하죠? "RIGHT 함수 " 항의값들에서 "-" 만빼주면우리가원하는값이됩니다. 즉, "-" 를공백 "" 로바꾸면값이간단히나오게될것입니다. 이것을함수로표현하면 =SUBSTITUTE(RIGHT(C53,12),"-","") 이렇게됩니다. Instance_num 를지정하지않았기때문에문자열내의모든 "-" 를 "" 으로바꿀것입니다. 참고 REPLACE : 지정한자리수의문자부터지정한자리수까지새로운문자로바꾸어줍니다. 스스로한번공부해보세요.(EXCEL 도움말참고 )

52 TEXT 값을지정한표시형식의텍스트로변환합니다. 구문 TEXT(value,format_text) Value 숫자값, 숫자값을계산하는수식또는숫자값이포함된셀의참조입니다. Format_text 셀서식대화상자의표시형식탭의범주상자의텍스트형식에있는숫자서식입니다. 주의 숫자값 Format_text 에별표 (*) 는포함될수없습니다. 서식메뉴의셀명령에있는표시형식탭에서셀서식을정하면값은변경되지않고서식만변경됩니다. TEXT 함수를사용하면값이서식이지정된텍스트로바뀌고그결과는더이상숫자로계산되지않습니다. 표시형식에사용하는문자 적용할서식 문자 # 0,. % 설명하나의자릿수를의미하며해당자릿수에숫자가없을경우는표시하지않하나의자릿수를의미하며해당자릿수에숫자가없을경우는 0을표시한천단위구분기호로사용된다. 통화기호로사용된다. 소수점의자리표시에사용된다. 입력된숫자에 100을곱한후 % 기호를붙인다. 좀더자세한내용은 Excel 도움말참조 예제 예제 Format_text 입력값 수식 결과 1 #,## =TEXT(C37,"#,## ") (#,##0) =TEXT(C38,"(#,##0)") (12,345) 3 ######-####### =TEXT(C39,"######-#######") =TEXT(C40," ") (0###)###-### =TEXT(C41," (0###)###-###") (025)

53 응용 1 아래표에서 NSN 이 "-" 구분없이표기되어있습니다. NSN 의적당한위치에 "-" 를넣어보세요 ( 예 : ) 순번 NSN 결과 순번 1 의수식 =TEXT(C58,"####-##-###-####") 을살펴보면 C58 의값 " " 을 "4 자리 -2 자리 -3 자라 -4 자리 " 의형태로나타내면되므로하나의문자를나타내는 "#" 을사용하여필요한자리수만큼 "#" 을넣어주고원하는위치에 "-" 를추가해주면원하는값을쉽게얻을수있습니다. 응용 2 아래품목의중량을전체자리는 5 자리로하되마지막 1 자리는소수점 2 번째자리에서반올림한소수점 1 자리값으로하고해당자리수에숫자가없을경우는 "0" 로표현해보세요 ( 예 : ) 순번 품번 중량 FORMAT 적용 (5NR1) 1 85B B B B B 순번 1 의수식 =SUBSTITUTE(TEXT(C76,"0000.0"),".","",1) 에서먼저 TEXT 함수를먼저살펴보겠습니다. TEXT(C76,"0000.0") 는 C76 의값은 " " 이고이값을 "0000.0" 의형태로변환합니다. "0000.0" 으로 Format 을정해줌으로서중량값이자동으로소수점 1 자리로반올림되어지고자리수에값이없을때는 "0" 값으로체워주게됩니다. 아래의 "Text 함수 " 항을참조하세요. 순번 품번 중량 Text 함수 1 85B B B B B TEXT 함수만적용했더니문제가한가지보이지요? 소수점자리를표현하는 "." 이없어져야원하는값을얻을수있습니다. 따라서 SUBSTITUTE 함수로첫번째나오는 "." 을 ""( 공백 ) 으로바꾸어주면주었습니다. 이것을수식으로표현하면 =SUBSTITUTE(TEXT(C76,"0000.0"),".","",1) 이되는것입니다. Instance_num 인 "1" 은문자열내에 "." 이하나밖에없으므로생략해도상관없습니다.