엑셀 TIP(팁)-드롭다운 목록을 통해 데이터를 정확하게 입력할 수 있는 데이터유효성검사에 대해알아봅니다.

 

구독신청~ 알림설정~  좋아요     감사합니다.

오늘은 지정한 값을 드롭다운 목록을

통해 선택하여 데이터를 정확하게 입력

할 수 있도록 도와주는 데이터 유효성 검사에

대해 알아 보겠습니다. 어떤범위의 데이터를

반복적으로 입력 해야 할경우 미리 입력될

데이터를 지정해 놓고 드롭다운 목록을 통해

선택해서 입력 하므로 오타걱정 없이 입력

하실 수 있습니다.

물론 목록지정시 오타가 없어야 겠죠.

기본적인 내용은 위 이미지와 같습니다.

예를 들어보면

위의 그림에서 직위 부분에 부장,차장,과장

대리,사원을 지정 입력 할려고 할 경우

이 부분을 목록으로 지정해서 정확하게

입력할 것 입니다. 먼저

직위란 부분에 데이터가 들어갈 부분(A)를

드래그 해서 모두 선택합니다. 선택한 상태

에서 상단메뉴의 데이터(B)를 클릭 후

나타나는 리본메뉴에서 데이터유효성검사

(C)를 클릭하면 데이터유효성검사창(D)이

뜹니다. 여기서 제한대상에 (H)부분을 클릭

해서 목록으로 바꾸어줍니다.

그리고 원본 부분에 부장,차장,과장,대리,

사원을 오타 없이 정확하게 입력합니다.

반드시 콤마(,)로 구분해야 합니다.

그리고 확인(G)을 클릭합니다.

그러면 입력하고자 하는 셀을 클릭(A)하면

셀 우측 하단에 화살표(B)가 나타나는데

이것을 클릭해보면 데이터유효성 창에서

지정한 목록들이 나열됩니다.

여기서 원하시는 직위를 선택하시면

정확하게 입력을 하실 수 있습니다.

​

다른 유형을 한번 해보겠습니다.

위 그림에서 데이터가 입력될 사용권한

부분을 드래그해서(A)선택 후 메뉴의

데이터(B)를 클릭 후 리본메뉴의

데이터유효성검사(C)를 선택하면 뜨는

창(D)에서 (E)부분을 클릭해서 제한대상을

목록 으로 바꿔주고 화살표(G)부분을 클릭

합니다.

그리고 입력될 데이터가 있는 부분(H)

범위를 드래그 해서 선택하면 됩니다.

그러면 따로 입력하지 않아도 목록범위

가 지정됩니다. 그리고 확인합니다.

입력될 셀(J)을 클릭후 화살표(K)를 클릭해

나타난 목록에서 해당하는 목록을 선택

하면 됩니다. 그러면 지정된 목록에 오타가

없으면 정확하게 입력이 되겠죠.

또한 목록지정시 한글자음 입력 후 키보드

한자키를 눌러 특수문자도 기입가능 합니다.

-감사합니다.-

엑셀 TIP(팁)-엑셀의 데이터 계급 구분함수에 대해 알아봅니다

 

구독신청~ 알림설정~  좋아요   감사합니다.

오늘은 IF함수의 응용을 통한 데이터 계급 구분

함수와 IF함수의 AND조건과 OR조건이 있는데

이전에 포스팅한것(2023년1월13일자)에

이어 이번에는 IF함수의 AND와 OR조건을 같이

사용해서 데이터를 구하는 방법에 대해 알아

보겠습니다. 데이터 계급 구분함수라는 것은

IF함수의 참과 거짓의 개념을 이용해 수,우,미,

양, 가 등의 구분을 한다던지 매우우수,우수,

보통, 취약 이런식의 등급을 나누는 함수를

말합니다.

우선 순위를 한번 구해보겠습니다.순위를

구하는 포스팅은 2022년12월10일자에

되어 있습니다. 참고하시면 됩니다.

J31셀을 클릭후 =RANK(I31,$I$31:$I$35,0)

를 입력후 엔터를 치면 순위를 구하게 됩니다.

순위를 구하는 함수는 RANK함수이고 평균을

이용해 절대참조(F4)를 이용하고 내림차순

으로 구합니다.

위 그림은 자동채우기 핸들을 드래그한 결과

입니다. 그러면 이제 평균을 이용해

계급 구분함수를 구해보겠습니다.

j24셀을 클릭후

=IF(H24<60,"유급",IF(H24<70,"양",

IF(H24<80,"미",IF(H24<90,"우","수"))))를

입력하고 엔터를 치면 위와 같이 구해

집니다. 여기서 H24의 평균값이 60점미만

일경우는 "유급"이고 그렇지 않으면 

거짓값을 입력해야 하는데 여기에 

IF함수를 지속적 으로 추가해가며 

우측의 비고의 점수 분포에 해당하는

등급을 입력하면 됩니다.

맨 마지막에 H24<90이면 "우"이고 거짓

이면 "수"값을 입력하고 마지막 괄호를

IF함수를 사용한 만큼 닫아야 합니다.

나온 결과값의 셀에서 우측 하단의 자동채우기

핸들(A)을 아랫방향으로 드래그하시면 그러면

위와 같은 등급값을 구해진 것을 알수 있습니다.

한번더 알아보겠습니다.

위의 그림에서 보면 비고(A)를 참고로 등급을

표시해 보겠습니다. J45셀을 클릭후

=IF(I45<70,"취약",IF(I45<80,"보통",

IF(I45<90,"우수","매우우수")))를 입력후

엔터치면 위와 같이 등급이 구해집니다.

I45셀의 평균값이 70미만일경우"취약"이고

거짓일경우 추가 IF함수를 입력하여 마지막의

I45셀의 평균값이 90미만일경우 "우수"이고

거짓일경우"매우우수"가 되게 하시고 마지막

괄호를 IF함수 수에 맞게 닫으시면 됩니다.

마찬가지로 자동채우기핸들을 드래그 해서

나머지 등급을 자동으로 구하시면 됩니다.

이번에는 앞서 포스팅했던(2023년1월13일자)

IF함수의 AND조건과 OR조건을 조합한 예시를

한번 알아 보겠습니다.

문제를 분석해보면 성적이 90점이상이고

결석횟수가 2번 미만이면의 조건이므로

AND조건을 사용하고 성적이 60점미만

이거나 출석점수가16미만 이므로

여기서는 OR조건을 사용하시면 됩니다.

따라서 J7셀을 클릭후

=IF(AND(I7>=90,D7<2),"A+",

IF(OR(I7<60,E7<16),"F","B")) 을 입력하고

엔터치면 됩니다. 즉 AND조건이 맞으면

"A+"표시하고 OR조건이 맞으면"F"표시하고

그렇지 않으면 "B"를 표기한다는 의미입니다.

이렇게 구한 값을 자동채우기 핸들을

드래그해서 나머지를 구하시면 됩니다.

아래 동영상을 참고하시면 도움이 되실듯

합니다.

-감사합니다.-

엑셀 TIP(팁)-엑셀의 IF함수와 IF함수의 AND조건과 OR조건에 대해 알아봅니다.

 

구독신청~  알림설정~   좋아요    감사합니다.

오늘은 엑셀의 함수중 IF함수와 IF함수의

AND조건과 OR조건에 대해 알아보겠습니다.

먼저 IF함수는 어떠한 조건식이 있을때

이 조건식이 참일때의 값과 거짓일때의 값을

나타내는 함수 입니다. IF함수의 기본식은

위 그림과 같이 표현됩니다.

예를 들어 보겠습니다.

위 그림처럼 점수가 80점이상이면 합격 그렇지

않으면 불합격을 표시해야 하는데 여기서 IF문을

사용하면 F5셀(A)을 클릭후

=IF(E5>=80,"합격","불합격")을 입력후 엔터치면

위와 같이 불합격이 입력됩니다. 여기서 우측

하단의 자동채우기 핸들(A)을 아랫방향으로

드래그하시면 결과값(F)이 나타납니다.

위 그림은 문자추출함수인 MID함수를 같이

사용하여 추출한 문자가 1일때 남자 그렇지

않으면 여자를 표기하는 문제입니다. 따라서

D5셀을 클릭후

=IF(MID(C5,8,1)="1","남자","여자")입력후

엔터치면 결과값이 나오는데 여기서 우측

하단의 자동채우기 핸들을 아랫방향으로

드래그하시면결과값(F)이 나옵니다.

위 그림은 성별을 구한후 성별이 남자이면

입대 그렇지 않으면 면제를 표기하는데

E5셀을 클릭후

=IF(D5="남자","입대","면제")입력후

엔터치면 결과값이 나오는데 여기서

우측 하단의 자동채우기 핸들을 아랫

방향으로 드래그하시면결과값(F)이 

나옵니다.

​

다음은

IF문의 AND조건과 OR조건에 대해 알아

보겠습니다.

위 그림은 엑셀점수와 파워포인트 점수가

동시만족이므로 AND조건을 이용합니다.

G7셀을 클릭후

=IF(AND(E7>=85,F7>=80),"합격","불합격")

을 입력하면 결과 값이 나옵니다. 여기서

자동채우기핸들을 아랫방향으로 드래그

하면 (F)와 같은 결과값이 나옵니다.

위 그림은 엑셀점수와 파워포인트 점수가

둘중에 하나만 만족해도 되므로 OR조건을

이용합니다.G7셀을 클릭후

=IF(OR(E7>=85,F7>=80),"합격","불합격")

을 입력하면 결과 값이 나옵니다. 여기서

자동채우기핸들을 아랫방향으로 드래그

하면 (F)와 같은 결과값이 나옵니다.

이렇게 오늘은 기본 IF함수와 IF함수의

AND조건과 OR조건에 대해 살펴

보았습니다. 아래의 동영상을 참고 하시면

조금더 도움이 될것 같습니다.

-감사합니다.-

엑셀 TIP(팁)-엑셀의 날짜및 시간함수에 대해 알아봅니다.

 

유투브 구독신청~ 알림설정~ 좋아요 감사합니다.

오늘은 엑셀의 함수중 날짜및 시간함수에 대해

알아보겠습니다. 먼저 시간함수에 대해 살펴

보겠습니다. 가장기본인 NOW함수는 현재의

연,월,일,시간을 표시합니다.

위의 그림처럼 F4셀을 클릭후 =NOW() ( A)를

입력하고 엔터를 치면 (B)부분과 같이 년,월,일,

시간이 표시됩니다. 이렇게 NOW함수로 구해진

값을 기본으로 현재 초,분,시간을 구할수

있습니다. 초를 구하는 함수는 SECOND인데

여기에 NOW함수를 같이 동반합니다. 즉

=SECOND(NOW())를 입력(C)하면 NOW함수에

의한 결과값에서 초만 구한다는 의미 입니다.

따라서 11(D)값만 나타냅니다.

분을 구하는 함수는 MINUTE 인데 마찬가지로

=MIHUTE(NOW())를 입력(E)하면 NOW함수에

의한 결과값에서 분만 구한다는 의미입니다.

따라서 7(F)값만 나타냅니다.

시간을 구하는 함수는 HOUR인데 마찬가지로

=HOUR(NOW())를(G) 입력 하면 NOW함수에

의한 결과값에서 시간만 구한다는 의미입니다.

따라서 1(H)값만 나타냅니다.

참고로 빈셀을 클릭후 CTRL+SHIFT+:(세미콜론)을

동시에 누르면 현재시간(J)을 나타낼수 있습니다.

​

다음은 날짜 함수에 대해 알아보겠습니다.

현재 날짜를 나타내는 함수는 TODAY함수

입니다. F11셀을 클릭후 =TODAY()를 입력후

엔터치면 현재 년,월,일 (N)을 구할수 있습니다.

같은 의미로 CTRL+:(세미콜론)(K)을 동시에

눌러도 현재 년,월,일(L)을 구할수 있습니다.

현재 몇일을 구하는 함수는 DAY함수인데

=DAY(TODAY())입력후 엔터치면 12일(Q)를

구할수 있습니다.이는 TODAY함수에의해

구해진 값에서 몇일만 구한다는 의미입니다.

현재 월 을 구하는 함수는 MONTH인데

=MONTH(TODAY())입력후 엔터치면1(S)를

구할수 있습니다. 마찬가지로 TODAY함수에

의해 구해진 값에서 월 만 구한다는 의미입니다.

현재 년도를 구하는 함수는 YEAR함수인데

=YEAR(TODAY())입력후 엔터치면 2023(U)를

구할수 있습니다. 마찬가지로 TODAY함수에

의해 구해진 값에서 년도만 구한다는 의미

입니다. 물론 =DAY(NOW()),=MONTH(NOW())

,=YEAR(NOW())로도 구할수 있습니다.

이번에는 위에서 설명한 내용으로 실습을

해보겠습니다. 위 그림은 임의로 작성된

것입니다. 여기서 생년월일,나이,근무년수를

구해보겠습니다.

먼저 생년월일은 주민번호를 이용해 구하며

이전에 포스팅(2022년12월16일자)한

문자추출함수도 같이 사용 됩니다.

생년월일은 E6셀을 클릭후

=DATE(LEFT(D6,2),MID(D6,3,2),MID(D6,5,2))

를 입력후 엔터치면 1968-11-02(A)가 구해집니다.

여기서 자동채우기핸들(D)점을 클릭후 아래로

드래그해서 나머지 부분을 구하시면 됩니다.

여기서 DATE함수는 LEFT(D6,2)에서추출한 68

MID(D6,3,2)에서추출한 11,MID(D6,5,2)에서

추출한 02를 날짜함수로 변환하는 함수입니다.

다음은 나이를 구하는 함수입니다.YEAR함수를

이용하는데 현재년도에서 출생년도를 빼면되죠

따라서 F6셀을 클릭후

=YEAR(TODAY())-LEFT(D6,2)-1900하시면

됩니다. 여기서 YEAR(TODAY())에서 구한값

2023에서 LEFT(D6,2)에서 추출한68을 빼고

1900년도를 추가로 빼준다는 의미입니다.

다음은 근무년수를 구해보겠습니다.

근무년수도 현재년도에서 입사년도를 빼면

됩니다. 따라서 H6셀을 클릭후

=YEAR(TODAY())-YEAR(B6)입력후 엔터치면

됩니다. 현재년도에서 YEAR(B6)는 B6셀의값

에서 년도를 구하는 함수이므로 빼면됩니다.

모두 자동채우기 핸들을 드래그해서 나머지

부분을 구하시면 됩니다.

글로만 적으니 복잡해 보일수 있는데 동영상을

참고하시면 이해가 되실것입니다.

-감사합니다.-