성공의법칙

엑셀은 많은 데이터를 효율적으로 관리해주므로 숫자, 수식 등을 주로 사용하게 된다. 물론 부수적으로 들어가는 문자 데이터나 한자, 날짜, 기호 등도 있을 수 있다. 엑셀 사용 시 빠져서는 안될 문서작업을 좀 더  알아 보도록 하자.

셀에는 숫자뿐만 아니라 문자, 기호, 날짜, 시간 등을 입력할 수 있다. 먼저 가장 기본적인 문자와 숫자 데이터 입력하는 방법을 예제를 통해 보도록 하자.

▲ [문자 데이터 입력①] 셀 안에 문자 입력 후 엔터를 누르면 다음 셀로 이동

▲ [문자 데이터 입력②] 셀 내에서 두 줄로 입력하고 싶으면 첫 줄 입력 후 Alt+엔터를 사용하면 커서가 아랫줄로 이동

▲ [문자 데이터 입력③] 숫자를 계산하지 않기를 원할 때에는 숫자 앞에 ‘(어퍼스트로피)를 입력.
‘숫자 입력 후 셀 옆에 생기는 아이콘 화살표를 눌러 오류 무시 클릭

* 문자 데이터는 입력 시 왼쪽 정렬을 기본으로 한다.

▲ [숫자 데이터 입력①] 일반 숫자 입력 시 그대로 표기, 12자리 이상 숫자 입력 시 지수로 표기

▲ [숫자 데이터 입력②] 분수를 입력하고 싶을 땐 0이상의 숫자를 입력 후 한 칸 띄고 분자/분모 값 입력

▲위와 같이 자동 변환

* 숫자데이터는 입력 시 기본적으로 오른쪽 정렬된다.

이번에는 셀 안에 날짜와 시간을 입력해보도록 하자.

▲ [날짜 입력] 2가지 방법을 사용할 수 있다. 예제를 통하여 따라 해보고 원하는 타입으로 사용하자.

* 현재 날짜 입력 시 Ctrl+; 사용

▲[시간 입력] 셀에 시:분:초 단위로 입력하면 수식 입력 줄에 AM/PM 맞춰 표기됨

* 현재 시간 입력할 시 Ctrl+Shift+; 사용

한글뿐만 아니라 다양한 언어를 입력 할 수 있는데, 가장 많이 쓰이는 한자 입력 방법을 알아보자.

▲ 한자 변환이 필요한 셀 클릭(다중일 경우에는 드래그) 후 [검토]-[한글/한자변환] 클릭 후 [원하는 한자 선택] 후 [변환] 클릭

키보드에 있는 기호 외 다른 기호와 특수 문자를 입력해 보자.

▲ 기호 입력할 곳에 클릭 한 후 [삽입]-[기호]-[원하는 기호 클릭]-[삽입]

▲ [한자]키를 이용한 특수기호 입력방법도 있다. 자주 사용하는 기호라면 이 방법이 더 빠를 수 있다. 한글 자음+한자키를 누르면 특수기호 미니 창이 나타난다. 스크롤하여 원하는 기호 선택.

채우기 핸들은 규칙적인 데이터나 연속된 데이터를 빠르게 입력할 수 있는 좋은 툴이다. 내게 필요한 데이터를 지정하여 채우는 방법을 알아보자.

▲ [파일]-[옵션] 클릭

▲ [고급]-[사용자 지정 목록 편집]

▲ 목록 항목에 내가 필요한 항목을 입력 후 [추가]버튼 클릭, 확인           

▲ 다시 셀로 돌아와 전 단계에서 만든 항목 중 가장 첫 번째 단어를 입력한다. 그리고 채우기 핸들을 사용
하면 내가 입력한 목록 항목으로 채우기가 완성

데이터 입력 시 오류를 최대한 막기 위하여 사용하는 기능으로 미리 유효한 데이터만 입력되도록 설정하는 방법이다.

▲ [영문 데이터 유효성 검사]
데이터 유효성 검사가 필요한 항목을 [드래그] 후 [데이터]-[데이터 유효성 검사] 클릭

▲[IME모드]-[영문] 선택-[확인]

▲ [한글 데이터 유효성 검사]
같은 방법으로 데이터 유효성 창을 띄우고 IME 모드에서 한글 선택 후 확인

*위와 같은 방법으로 설정하면 한/영 키를 누르지 않아도 해당 셀에서는 설정한 한글이나 영문으로 표시됨

이번에는 항목에 필요한 유효한 데이터의 목록 설정을 해 보도록 하자. 말 그대로 내가 필요한 항목을 따로 만드는 것이다.

▲ [제한적 선택에 데이터 유효성 검사 설정①]
아래 예제는 성별을 이용한 데이터 유효성 검사 설정을 하였다. 
한글/영문 데이터 유효성 검사 방법처럼 먼저 드래그 후 [데이터 유효성 검사창]을 실행 시킨다. 
창에서 [설정]-제한 대상을 [목록]-원본에 [남, 여]로 기입 후 확인 버튼

* 설정되면 성별 셀에는 남 아니면 여 로만 입력할 수 있다.

▲ [제한적 선택에 데이터 유효성 검사 설정②]
이번에는 입력 해 놓은 데이터 범위 안에서 선택, 유효하도록 설정해보자.
[드래그, 유효성 검사창 실행]-[설정]-[목록]-[데이터 드래그]-[원본클릭]-[확인]
아래 예제와 같이 설정해 놓은 계정 항목에서는 계정에 해당하는 데이터 3가지만 입력할 수 있다.

▲ [제한적 선택에 데이터 유효성 검사 설정③]
다음은 날짜로 예제를 들어보자. 
[드래그, 유효성 검사창 실행]-[설정]-[날짜]-[시작날짜와 끝 날짜 입력]-[확인]

▲ 유효성 검사에서 설정한 이외의 값을 입력했을 때 나타나는 오류 메시지를 만들 수 있다. 
[드래그, 유효성 검사창 실행]-[설명 메시지]-[메시지 작성]-[확인]

▲ 데이터 이외의 값을 입력했을 때 아래와 같은 오류메시지가 창에 나타난다.

엑셀에서 가장 많이 쓰이는 함수라고 해도 과언이 아닐만큼, 합계를 구하는 함수(SUM)는 가장 기본적이고 필수적이라 꼭 알아두어야 할 함수다. 더하고 싶은 셀들을 가로 또는 세로로 일렬로 정리해 둔 다음, 빈 셀에 "=SUM"이라고 쓴 다음 해당 줄의 첫번째 셀(A1)과 마지막 셀(D1)까지 더한다는 뜻으로 "(A1:D1)"이라고 적으면 해당 범위의 값을 모두 더한 값이 도출된다.

합계와 투톱을 이루는 가장 대중적인 엑셀 함수 평균(AVERAGE). 합계를 구하는 함수에서 'SUM'을 'AVERAGE'로만 바꾸면 된다. 먼저 "=AVERAGE"라고 쓴 다음, 첫번째 셀(A1)과 마지막 셀(D1)까지, 즉 해당하는 범위의 평균을 구한다는 뜻으로 "(A1:D1)"이라고 쓰면 해당 범위의 평균값이 도출된다. 

많은 거래처들이 오퍼한 가격, 반 학생들의 점수들을 파악하려 할 때 가장 높은 값, 가장 낮은 값, 중간값,가장 자주 나온 값을 알 필요가 종종 있다. 이럴 때 사용하는 함수 역시 어렵지 않다. 합계와 평균을 구할 때 'SUM'이나 'AVERGAE'에 썼던 단어를 바꾸기만 하면 된다. 먼저 범위를 셀 A1부터 D4까지 라고 가정하면 범위를 "(A1:D4)"라고 쓰면 된다. 그리고 최대값을 구하려면 그 앞에 "=MAX"를, 최저값을 구하려면 "=MIN"을, 중간값을 구하려면 "=MEDIAN"을, 최빈값을 구하려면 "=MODE"을 쓰면 된다.

가장 높은 값이나 가장 낮은 값 말고 2번째, 3번째로 높은 값이나 낮은 값을 구하고 싶다면? 엑셀에는 이를 구하는 함수 역시 존재한다. 먼저 높은 값을 구하려면 "=LARGE"를 쓰고, 낮은 값을 구하려면 "=SMALL"을 쓴다. 그리고 구하려는 범위를 쓰는데, 그림을 예로들면 A1부터 D2까지 구하려고 하니 "(A1:D2)"라고 써주자. 그리고 범위 뒤에 몇 번째로 높은 값, 또는 낮은 값을 구할 것인지 써준다. 예를 들어 3번째로 높은 값을 구하려고 한다고 하면 범위 뒤에 '3'을 써준다. 즉, "=LARGE(A1:D2,3)"이라고 쓰면 된다. 낮은 값도 마찬가지. 이번엔 4번째로 낮은 값을 구하고 싶다면 "=SMALL(A1:D2,4)라고 쓰면 된다.

엑셀을 다루다보면 데이타가 굉장히 많은 파일을 받기 일쑤다. 그 안에서 원하는 값이 몇 개 있는지 하나하나 찾아보며 직접 셀 수는 없는 셈. 만능 프로그램 엑셀에는 특정 값이 몇 개 있는지 파악할 수 있는 함수도 있다. 범위를 A1부터 D4까지라고 가정하면 "(A1:D4)"라고 먼저 쓰자. 그리고 이 중에서 '90'이 몇 개 있는지 알고 싶다면 범위 뒤에 '90'이 있는 셀 하나를 쓰면 된다. 그림을 예로 들면 90이 쓰여 있는 A3를 넣어 "(A1:D4, A3)"라고 쓰면 된다. 그리고 그 앞에 "=COUNTIF"를 써준다.

데이터 하나하나가 평균에서 얼만큼 떨어져 있는지 알려면 하나 하나 평균과의 차이를 계산하는 방법도 있지만, 데이터가 너무 많다면 표준편차를 쓰는 것이 좋다. 다시 말해, 표준편차는 데이타가 얼마나 평균 주변에 모여있는지 혹은 흩어져 있는지, 그 정도를 나타내는 값이다. 그림을 예로 들면 구하려는 범위인 "(A1:D2)" 앞에 "=STDEV"를 써주면 된다. 첫 번째 줄의 함수는 A1부터 D2까지의 평균값을 구하는 함수이고, 두번째는 해당범위의 표준편차를 구하는 함수이다. 즉, 평균 45을 기준으로 한 데이터들의 산포도는 24.4949인 것을 알 수 있다.

'MOD'는 데이터 중 원하는 셀을 다른 셀로 나눈 나머지를 구해주는 함수다. 예를 들어, 80을 30으로 나눈 값의 나머지를 구한다고 하자. 그림에서 80의 값을 가진 셀은 D2이고, 30의 값을 가진 셀은 C1이다.이때 함수는 "=MOD(나뉘는 셀, 나누는 셀)"이므로, "=MOD(D2, C1)"을 써준다. 그러면 80을 30으로 나눌 때 나오는 나머지 값인 20이 도출된다.

데이터의 수치들이 너무 지저분해서 원하는 단위까지만 구분해서 파악하고 싶다면? 해당 범위의 데이터들을 반올림 해주거나, 올림 또는 내림해주거나, 지정한 자릿수 기준으로 잘라내는 함수들이 있다. 금액과 관련된 데이터를 처리할 때 용이하게 쓸 수 있는 함수들이니 차근차근 해보자. 먼저 A1의 값을 반올림할 때에는 "=ROUND"를 써주고, 해당 셀인 "(A1)"을 써준 다음, A1 뒤에 콤마를 찍고 반올림하려는 자릿수를 -를 붙여 써준다. 예를 들어 144인 A1을 1의 자리에서 반올림한다면 "=ROUND(A1,-1)"을 써주면 144를 반올림한 140의 값이 도출된다. 같은 방식으로 A1의 값인 144를 일의 자리에서 올림하려면 "=ROUNDUP(A1,-1)", 일의 자리에서 내림하려면 "=ROUNDDOWN(A1,-1)", 일의 자리 값을 잘라내려면"=TRUNC(A1,-1)"을 써주면 된다.

그림과 같이 A1부터 D2까지 중에 특정 값의 등수를 알고 싶다면? 함수 RANK를 쓰면 된다. 2010년 이하 버전에서는 RANK를 쓰고, 2010년 이상 버전에서는 RANK.EQ를 써준다. RANK.EQ를 예로 들면, 먼저 "=RANK.EQ"를 쓰고, "(등수를 구하려는 셀, 범위)"를 써주면 된다. 그림을 예로 들었을 때 10의 값을 가진 A1이 A1부터 D2까지의 셀 중에서 몇 등인지 알아보고 싶다면, "=RANK.EQ(A1,A1:D2)"라고 쓰면 된다. 2010년 이하 버전에서는 "RANK.EQ"를 "RANK"로 바꾸기만 하면 된다. 이때 대신 "RANK.AVG"를 써주면 목록내에서 순위를 비교하고 평균값으로 순위를 내보낸다.

글 출처 : 네이버 포스트 엡스토리