엑셀 SWITCH 함수는 조건식의 결과에 따라 다른 값을 반환하는 함수이다.

형식) SWITCH(조건식, 결과값1, 반환값1, [결과값2], [반환2], ~~~~, [기본값])

설명1)

조건식 ☞ 결과값을 비교할 조건식

결과값1 ☞ 조건식에서 연산된 결과값과 비교할 첫번째 값이다.

결과값1은 필수인수이다.

반환값1 ☞ 조건식에서 연산된 결과값이 결과값1과 일치하면 반환한다.

반환값1은 필수 인수이다.

결과값2 ☞ 조건식에서 연산된 결과값과 비교할 두번째 값이다. (첫번째 결과값1 과 맞지 않을 경우 결과값2와 비교한다)

결과값2는 선택인수이다.

반환값2 ☞ 조건식 결과값이 결과값2와 일치하면 반환할 값이다.

반환값2는 선택인수이다.

즉, 결과값2, 반환값2 부터는 선택인수이다.

기본값(선택인수) ☞ 결과값들과 일치하지 않을 경우 반환될 기본값이다.

설명2)

☞ 조건식의 결과에 따라 다른 값을 반환하는 논리함수 이다.

사용할 수 있는 버젼은 MSOffice2019 버젼 이후에서 사용 가능하다.

엑셀 CHOOSE함수와 IF함수의 상위 호환 함수이다.

☞ SWITCH함수는 조건식과 정확히 일치하는 결과값만 비교할 수 있다.

☆ 크기비교에는 사용할 수 없는 단점이 있다.

EX) 결과값이 1보다 크다, 10보다 작다. 등등

☞ 모든 인수는 배열을 참조 할 수 있다. 다른 함수를 인수로 사용할 수 있어 다양한 방법으로 활용하기 좋은 함수이다.

♣ 호환 버젼

Windows 버젼 : MSOffice2019버젼 이후, 365버젼에서 사용가능

Mac 버젼 : Mac용 2019, 365 버젼에서 사용 가능

예제1) 지역번호를 구하시오, 지역은 SWITCH함수를 통해서 구하시오.

결과값)

정답)

B2 ☞ =LEFT(A2,FIND("-",A2)-1)

C2 ☞ =SWITCH(B2,"02","서울","031","경기","033","강원","044","세종","정보없음")

또는 배열함수도 가능함 (Ctrl + Shift +Enter)

엑셀 TEXT함수에 대한 설명

설명 : 엑셀 TEXT 함수는 첫 번째 인수인 숫자값에 두 번째 인수로 주어진 표시형식을 적용하여 텍스트 문자열로 표시하는 함수입니다.

☞ 간단히 말하면, TEXT함수는 숫자를 텍스트로 바꿔주는 함수입니다.

형식) TEXT ( value, format_text )

TEXT(숫자, 서식)

인수 :

value : 텍스트로 변환을 원하는 숫자 값 혹은 텍스트 (참조 혹은 직접입력)

format_text : value 인수에 적용 될 사용자 지정 서식을 텍스트로 전달

=TEXT(숫자 셀선택, “ 변경할 format 지정”)

노트1 : format_text 인수 적용 방법

TEXT 함수의 format_text 인수를 설정하기 위해서는 엑셀의 사용자 지정 서식을 알아야 합니다.

TEXT 함수에서 이 사용자 지정 서식은 큰따옴표 (“ ”)로 둘러 텍스트 형태로 작성합니다.

즉, format 을 지정할 때는 큰 따옴표(“내용”)으로 입력하면 됩니다.

★ TEXT함수는 숫자를 텍스트 형식으로 변환하는 함수입니다. 이때 서식을 적용할 수 있는 것입니다.

TEXT 함수의 기본 기능은 숫자 값의 “텍스트”로의 변환임을 기억해야합니다.

주의)

TEXT함수로 나온 결과값은 연산을 해도 수식 계산이 되지 않습니다.

텍스트 이므로, 숫자 연산이 되지 않습니다.

실습)

TEXT 함수 내용

예제) 날짜에서 TEXT함수에서 연월일을 뽑아오는 예제입니다.

실습)

약간 응용해보겠습니다. 아래를 보고 따라 해보세요.

응용실습)

예제) 핸드폰 번호 하이픈(-) 자동입력하기

자동으로 가운데 숫자가 3자리 또는 4자리 나오면 하이픈이 자동 입력되도록 만들어 봅시다.

※ 엑셀에서 표시 형식 입니다.

엑셀의 표시형식이란 셀의 데이터 내용을 원본으로 하여 그 데이터를 효시하는 다양한 방식을 말합니다.

셀에서 오른쪽 버튼 -> 셀서식 -> 표시형식 탭에서 작업이 이루어집니다.

다양한 범주가 있지만, 그 주 ㅇ가장 많이 사용되고 유용한 사용자 정의 (지정) 범주에 대해 살펴봅니다.

사용자 지정 범주는 사용자가 임의의 표시 형식을 만들어 내는 것인 이에는 서식 코드라는 것을 알아야 합니다.

● 엑셀 숫자 서식

# : 숫자를 의미 합니다. 의미없는 0은 제거 하고 표시 합니다.

0 : 숫자를 의미 합니다. 수가 없을 경우에는 0을 표시하며, #과의 기능을 햇갈려서는 안됩니다.

, : 수 천단위를 말합니다.

. : 소수점을 의미합니다.

/ : 분수 기호입니다.

● 엑셀 날짜 서식

yy : 연도를 2자리로 표기

yyyy : 연도를 4자리로 표기

m : 월을 표기

mm : 월을 01~12 형태로 표기

mmm : 월을 영문 3자리로 표기

mmmm : 월을 영문으로 표기

d : 일을 표기

dd : 일을 01~31 형태로 효기

ddd : 일의 요일을 영문 3자리로 표기

dddd : 일의 요일을 영문으로 표기

aaa : 요일을 한글 1자리로 표기(일~월)

aaaa : 요일을 한글로 표기 (일요일 ~ 월요일)

● 엑셀 시간 서식

h : 시간을 표시(0~23)

hh : 시간을 두자리로 표시

m : 분을 표시(0~59)

mm : 분을 두자리로 표시 (00~59)

s : 초를 표시(0~59)

ss : 초를 두자리로 표시(00~59)

시간형식에 AM/PM 추가 : AM(오전), PM(오후)으로 시간 표시

● 엑셀 문자열과 공백 서식

_ : 숫자 서식에서 한문자의 폭 만큼 공백 표시

@ : 문자열 표시

※ #, 0은 한 개의 경우는 모든 문자 혹은 모든 숫자들을 표시하지만 여러개일 경우 그 개수만큼의 문자 혹은 숫자를 표시합니다. 예) 0 : 얼마나 큰 수 인지 알 수 없을 때, 00 : 숫자 두자리를 표시합니다. 한 자리일 때는 앞자리에 0을 추가)

● TEXT함수에서 표시형식 정리

표시형식 부분을 정리한 부분입니다.

타이핑해서 하나씩 익혀보시면 어렵지 않습니다.

,(콤마)하나당 3개까지 자리 표시 하지 않음, 해당위치 5이상이면 반올림

#,, : 십만 이하의 자리수는 표시되지 않음, (콤마 하나당 3자리씩)

#, : 백 이하의 자리수는 표시 되지 않음, (콤마 하나당 3자리씩)

#, : 백의 자리수가 5이상이면 반올림해줌

#,### : 천단위 구분기호

#,##0 : 천단위 구분기호

0 : 값이 공백이거나 0인 경우에 0 표시

# : 값이 공백이거나 0인 경우 아무것도 표시 하지 않음

#.000 : 소수 3번째 자리까지 표시하되, 그 뒤 숫자가 5이상이면 반올림

#.00 : 소수 2번째 자리까지 표시하되, 그 뒤 숫자가 5이상이면 반올림

#% : 숫자를 백분율로 나타내어 표시

#,###.0 : 천단위 구분하고, 소수 첫번째자리 까지 표시하고 그 뒤에 수가 5이상이면 반올림

#,##0 과 #,### 둘다 천단위마다 콤마를 찍어준다.

값을 0을 넣을 때 0이라도 찍히게 하기 위해서 책, 자격증책 등에는 #,##0을 입력하라고 합니다.

☞ 문제풀때 과목의 항목들을 적어주고, 오름차순 또는 내림차순으로 문제 풀이에 맞게 차순을 정리해주기

여기 문제에서는 MATCH함수를 이용하는데, 항목은 두개만 사용하되, 내림차순꺼 사용하면 되므로,

영어, 수학 두개만 사용해서 처리하면 된다. 그리고 { } 안에 쌍따옴표 넣어서 처리해주면 된다.

▶HLOOKUP, MATCH 함수 사용

1) MATCH 함수를 이용하여 과목의 위치값 구하기

식 : =MATCH(N3,{"영어","수학"},-1)+1

My 간단설명 )
맨 먼저 할것,
보통 문제에서 3개 정도 항목이 된다.
내림차순 또는 오름차순으로 정렬할 항목을 차순을 하고,
두개만 사용하면 된다.

영어, 수학 // 이부분이 내림차순, 즉, 숫자처럼 범위라고 생각해야한다.

범위안에 들어가게끔 문자를 넣어줘야한다.
예) 영어 : 1 // 수학 : 2 // 나머지없는 영역도 다 2가 된다.
하지만, 수학 이라는 글자 부분을 잘 넣어줘야한다.
exJ) 수학 이라는 글자를 찾으므로, 숫자처럼 포함 되도록 글자 범위가 들어가게 해줘야 한다.
예) 수학부터가 2 에 포함 되므로,
수기 라고 만약 글을 넣으면, 수학 영역 유니코드(한글이므로, 아스키 아니고 유니코드) 미만쪽이므로, 1로 처리 되고, 수학이라는 유니코드 이상으로 처리 되게 처리해주면, 2가 나오게 된다.
예를 들면, 수하 --> 는 수학 보다 유니코드값 미만위치이므로, 1로 처리 된다.
수학 다음인 ㄱ 다음인 ㄴ 을 넣어주어서, 수한 으로 처리하면 2가 나오게 된다.
즉, 글자도 유니코드라고 생각하고 글자의 범위라고 생각해서 해석하면 해결된다.






[N3]셀의 값에 대한 행의 위치값이 반환되어야 함. [표6]의 할인율표에 ‘영어’와 ‘기타’만 있고, ‘수학’과 ‘국어’에 대한 값이 없으므로 ‘기타’의 행 값이 ‘수학’과 ‘국어’ 할인율 값으로 반환되어야 함.

위와 같이 { } 안에 “영어”, “수학”을 넣어주면 “영어”를 1, “수학”을 2의 값을 반환한다. “수학” 대신 “국어”를 넣으면 제대로 된 값을 반환하지 못하므로 반드시 “영어”, “수학” 순서대로 넣어주고, 마지막 정렬방법 부분에는 ㅇ → ㅅ 순서이므로 내림차순(-1)으로 정렬된 것에서 위치 값 구하라고 넣어주면 된다.

그러면 “영어”가 1, “수학”,“국어”는 2의 값을 반환한다. [표6]에서는 “영어”가 2, 나머지는 3행의 “기타”에 있는 할인율 값이 가져와져야 되므로 [=MATCH(N3,{"영어","수학"},-1)+1] 뒤에 ‘+1’을 해줘서 “영어”가 2, “수학”,“국어”가 3 값이 되도록 해준다.

2) 학년과 과목에 따른 할인율값을 가지고와서 표시해준다.

=HLOOKUP(M3,$R$3:$T$5,MATCH(N3,{"영어","수학"},-1)+1,FALSE)

● 오름차순은 낮은 숫자부터 숫자가 점점 커지면서 정렬하는 것이다.

문자는 가나다순, ABC순으로 정렬을 의미한다.

●  내림차순은 높은 숫자부터 숫자가 점점 내려가면서 정렬하는 것이다.

문자는 다나가, CBA 등 오름차순 반대로 정렬된것을 내림차순이라 한다.

☞ 아래 그림을 보면 쉽게 이해할 수있습니다.

TIME함수

구문) TIME(시, 분, 초)     TIME(hour, minute, second)

HOUR(serial_number) :

serial_number : 시간 중 시 값을 반환합니다.

MINUTE(serial_number) :

serial_number : 시간 중 분 값을 반환합니다.

시간은 1일, 24시간의 전체길이가 1이다.

1시간의 양이 나오면

0.041666667 이 나온다.
이건 전체 길이 1의 1/24 이다.

시계는 1일을 24등분 하니깐
24를 곱해주면 단위가 시간으로 처리 된다.

참고) 24시간 * 60 = 1440 (단위가 분으로 처리된다)


0900     1000  // 출발시간 입력시간 넣을시 09에서 0이 빠지면, 표시형식을 텍스트로 해주면 된다.

1. 09:00-10:00
2. G1 : 0900
  H1 : 1000

3. =(TIME(LEFT(H1,2),RIGHT(H1,2),)-TIME(LEFT(G1,2),RIGHT(G1,2),))*24

----------------------------------

엑셀 2016 / 도움말

TIME 함수

이 문서에서는 Microsoft Excel의 TIME에 사용되는 수식 구문과 이 함수를 사용하는 방법을 설명합니다.

설명

특정 시간에 대한 실수를 반환합니다. 함수가 입력되기 전의 셀 서식이 일반이었으면 결과값은 날짜 서식으로 지정됩니다.

TIME에서 반환하는 소수는 0에서 0.99988426 사이의 값이며 0:00:00(오전 12:00:00)에서 23:59:59(오후 11:59:59) 사이의 시간을 나타냅니다.

구문

TIME(hour, minute, second)

TIME 함수 구문에는 다음과 같은 인수가 사용됩니다.

• hour    필수 요소입니다. 시간을 나타내는 0에서 32767 사이의 숫자입니다. 23보다 큰 값은 24로 나눈 그 나머지가 시간 값으로 처리됩니다. 예를 들어 TIME(27,0,0)은 TIME(3,0,0)이 되고 이 값은 .125 또는 오전 3:00입니다.
• minute    필수 요소입니다. 분을 나타내는 0에서 32767 사이의 숫자입니다. 59보다 큰 값은 시간과 분으로 변환됩니다. 예를 들어 TIME(0,750,0)은 TIME(12,30,0)이며 이 값은 .520833 또는 오후 12:30입니다.
• second    필수 요소입니다. 초를 나타내는 0에서 32767 사이의 숫자입니다. 59보다 큰 값은 시간, 분, 초로 변환됩니다. 예를 들어 TIME(0,0,2000)은 TIME(0,33,22)이 되고 이 값은 .023148 또는 오전 12:33:20입니다.

주의

시간 값은 날짜 값의 일부이며 소수로 표시됩니다. 예를 들어 오후 12:00는 하루의 반이기 때문에 0.5로 표시됩니다.

예제

다음 표의 예제 데이터를 복사하여 새 Excel 워크시트의 A1 셀에 붙여 넣습니다. 수식의 결과를 표시하려면 수식을 선택하고 F2 키를 누른 다음 Enter 키를 누릅니다. 필요한 경우 열 너비를 조정하면 데이터를 모두 표시할 수 있습니다.

B20 : =H4>=AVERAGE($H$4:$H$16)

일단 정리하면,

My 해석 정리

정리
1. >=6 이렇게 비교대상이 상수이면, 필드명 밑으로 참,거짓으로 찾고,
2. =H4>=AVERAGE($H$4:$H$16)  // 이것은 >= 뒤에 숫자가 아니므로, 필드명 밑으로 참거짓을 찾을 수 없고, 다 거짓이 되므로, 필드명을 인지 못하게 해야하며,
H4 주소값으로 비교대상이 맞는지 체크하므로, 필드명은 없이 조건부 서식처럼 상대주소가 알아서 참거짓을 판별하게 해야한다.

부가설명)  // 2021-03-28

=COUNTIF(E5:E12,">="&AVERAGE(E5:E12))&"개"

조건부분에서 ">="     &     AVERAGE(E5:E12

각각의 조건을 연결해준다고 생각하면 된다.

고급필터에서

함수를 넣어서 조건을 넣을 때, 필드명을

1. 안쓰거나,

2. 다른 이름으로 넣으면 된다. 예) 조건    // 이런식으로 하면 된다.

필드명을 굳이 주지 않아도 된다. 굳이 주려면, 그냥 "조건" 이렇게 주던가, 아무 이름을 주되, 필드명과 같지 않게만 주면 된다.

고급필터의 조건은 필드(열) 데이터 내에서 그대로 조건에 해당하는것만 추출해 낸다.

하지만, 함수명이 들어가면, 필드명을 다르게 해줘야 한다.

해당 필드의 데이터에 함수명으로 작성된 데이터가 없기 때문이다.

데이터 자체가 함수명, 즉, average , sum 등등 이런 데이터가 아니기 때문이다.

함수명을 넣게 되면, (해당열에) 숫자데이터만 있었다면, 일치하는게 하나도 없다.

필드명을 같지 않게 해주는건, 조건의 필드명을 인지하지 못하게 하고,

조건부서식처럼, = 주고, 조건을 줘서 조건에 해당하는 데이터를 추출하도록 해주면 된다.