엑셀에서 주로 계산을 하는 파일을 만들지만 가끔씩 서식을 만들어 사용하기도 합니다.

예로 직원 명부를 엑셀로 만들 때 그냥 만드는 것보다 유효성 검사를 사용하여 만들면 더 나은 명부를 만들 수 있습니다.

엑셀에서 직원명부를 만듦.

이렇게 서식을 만들어  있으면 여기에 주민등록번호를 입력하여야 하는데, 주민등록번호를 입력하려면 같은 글자 수의 크기가 정해져 있기 때문에 좀 더 정확하게 입력을 할 수 있도록 유효성 검사를 지정하는 것이 입력 오류를 막을 수 있습니다.

이럴 때는 데이터 유효성 대화 상자에서 제한 대상을 텍스트 길이를 이용해서 글자 수를 맞출 수 있습니다.

빨간 테두리처럼 범위를 선택합니다.

데이터 탭을 클릭하고, 데이터 유효성 검사를 클릭합니다.

이렇게 데이터 유효성이라는 팝업창이 열리면, 설정 탭에서 제한 대상은 '텍스트 길이'로, 제한 방법은 '='을 선택하고 길이에는 주민등록번호 열세 자리에 가운데 하이픈(-)을 포함해서 14를 입력한 후 확인을 클릭합니다.

이렇게 확인을 클릭하고 다시 직원 명부로 와서 주민등록번호를 입력하면

첫 번째 621013-1234567을 입력하면 14자리가 맞기 때문에 입력이 됩니다. 그런데 다음칸에 740103-123456을 입력하면 14자리가 아닌 13자리이므로 입력이 중지되며 '입력한 값이 잘못되었습니다.'라는 오류 메시지 창을 띄웁니다. 입력을 다시 하려면 취소를 클릭하면 됩니다.

취소를 클릭하면 다시 그 칸으로 돌아갑니다. 그럼 직위도 이런 방법으로 다시 입력해 보겠습니다.

직위를 입력할 셀 범위를 선택합니다. 위와 같이 데이터 탭을 클릭, 데이터 유효성 검사를 클릭하면 데이터 유효성 팝업창이 뜹니다. 위와 같이 설정 탭에서 제한 대상을 '목록'으로 하고, 원본에 '대표이사, 이사, 팀장, 차장, 과장, 대리, 사원'을 입력한 후 확인을 클릭합니다.

그러면 아래와 같이 목록 단추가 생깁니다.

이것을 클릭하면 아래와 같이 클릭할 수 있는 목록이 나옵니다.

여기서 필요한 직위를 선택하면 됩니다.

여기서 사원번호가 빠져서 사원번호칸을 추가하여 사원번호가 입력이 되어있다고 가정합니다.

사원번호는 성명 앞에 입력해 보겠습니다. 위와 같이 사원번호가 입력되어 있는 상황에서 입력할 범위를 선택하고 데이터 탭을 클릭, 데이터 유효성 검사를 클릭하면 아래와 같이 팝업창이 뜹니다.

여기서 설정 탭에서 제한 대상에서 사용자 지정을 선택, 수식에 "=COUNTIF($B$4:$B$14, B4)<2"를 입력한 후에 확인을 클릭합니다.

아래와 같이 14번째 열에 10번 사원의 사원번호와 똑같은 사원번호를 입력하면 

위와 같이 오류 메시지가 나옵니다. 여기서 취소를 클릭하면 다시 그 셀로 돌아가서 재입력을 합니다. 이렇게 중복입력을 막을 수 있습니다.

위와 같이 데이터 유효성 검사로 글자 수를 지정하는 방법과 지정 목록에만 데이터 입력하는 방법과 중복 데이터의 입력을 차단하는 방법을 알아보았습니다. 서식을 만들 때 이렇게 유효성 검사를 사용하면 입력의 오류를 조금이나마 막을 수 있습니다. 이상 엑셀에서 데이터 유효성 검사에 대해서 알아보았습니다.

여기까지 입니다.

  1. 라미드니오니 2019.07.02 05:32 신고

    유효성 검사를 잘 사용하면 멋진 시트가 만들어지죠.ㅎ

  2. 봉리브르 2019.07.02 06:47 신고

    유용한 정보네요.
    잘 보고 갑니다.
    기분좋은 하루 시작하세요^^

  3. Deborah 2019.07.02 21:31 신고

    아 잘 하셨네요. 잘 보고 배웠습니다.

  4. 담덕01 2019.07.05 14:29 신고

    참 이런 거 보면 엑셀은 확실히 대단한 프로그램인 듯 싶어요.
    어지간한 건 다른 프로그램 없이 엑셀로 대체 가능한 거 같거든요. ^^

엑셀에서 자주 사용하는 기호에 대해서 기호가 필요할 때마다 한글에 자음을 눌러서 한자 키를 입력하면 찾을 수 있지만 자주 사용하는 기호를 자동 고침 옵션에 등록하면 더 편리하게 사용할 수 있습니다.

우선 파일을 클릭합니다.

옵션을 클릭합니다.

Excel옵션 창이 열립니다. 여기서 언어교정을 클릭합니다.

자동 고침 옵션을 클릭합니다.

자동 고침 창이 열립니다. 여기에서 입력과 결과에 자주 사용하는 기호를 넣으면 됩니다.

저는 원이 두개 겹치는 기호를 많이 써서 입력에는 (쌍원)으로 결과는 ◎으로 입력합니다.

입력을 하면 추가를 클릭합니다.

그러면 이렇게 입력이 됩니다. 그리고 밑에 확인을 누릅니다. 다시 Excel 옵션 창에서 확인을 클릭합니다.

아무 셀에서 (쌍원)이라고 입력하고 엔터키를 누르면 ◎으로 바뀝니다.

밑에는 위의 과정을 동영상 저장해 보았습니다. 궁금하시거나 질문이 있으시면 댓글로 남겨주십시오. 꼭 답변하겠습니다.

여기까지입니다.

 

 

엑셀 관련 다른 글을 보시려면 여기를 클릭해주세요.

  1. Patrick30 2019.05.27 17:35 신고

    오~ 따라하다보니 굉장히 쉽네요 ㅎ
    저는 그냥 특수문자로 찾아서 했는데, 간편하게 사용이 가능하군요 ㅎ

1. 엑셀(제 컴퓨터는 2010 버전입니다.)은 이전 버전인 엑셀 2007의 저장 형식과 똑같이 XML 형식으로 저장됩니다. 일반적으로 파일의 확장자가 xl로 시작하고, 통합 문서는 xlsx, 매크로가 포함된 문서는 xlsm으로 저장됩니다.

위와 같이 여러 확장자명으로 저장이 가능합니다. 주로 저장하는 것이 Excel 통합 문서입니다. 그런데 2010 버전부터는 PDF로 바로 저장이 가능합니다. 여러 가지 확장자명으로 저장 가능하니 상황에 맞게 저장을 하면 됩니다.

2. 엑셀 2010은 기본적으로 맑은 고딕, 11pt, 3개의 시트 탭을 제공합니다. 이런 기본값을 엑셀 옵션에서 변경할 수 있습니다. 

우선 파일을 클릭합니다.

옵션을 클릭합니다.

일반을 클릭하여 아래의 부분을 변경하면 됩니다.

본문 글꼴에는 여러 가지 글꼴이 나오고 여기서 원하는 글꼴을 택하면 됩니다.

밑의 포함할 시트수는 1~255까지 입력이 가능합니다. 숫자를 입력하거나 화살표 위아래로 조정합니다.

0을 넣으면 "1보다 크거나 같아야 하는 항목입니다", 255보다 큰 수를 넣으려고 하면 " 255보다 작거나 같아야 하는 항목입니다."라고 메시지가 나옵니다. 1과 255의 수를 입력하고 밑에 확인을 클릭하면 새 통합 문서의 기본값이 변경됩니다.

엑셀 팁 몇 가지를 알아봤습니다.

오늘은 여기까지입니다.

좋은 하루 보네세요.

 

 

엑셀 관련 다른 글을 보시려면 여기를 클릭해주세요.

오늘은 엑셀에서 자주 사용하는 목록을 등록하여 연속 채우기로 쉽게 해 보겠습니다.

자주 사용하는 목록을 등록하면 되는데 여기서는 예로 직급을 등록해보겠습니다.

우선 직급은 대표이사, 상무이사, 전무이사, 이사, 부장, 차장, 과장, 대리, 주임, 사원 이렇게 있다고 하고 이 직급을 등록하여 쉽게 마우스 클릭으로 직급들을 넣어보겠습니다.

우선 파일을 누릅니다.

파일을 누르면 이렇게 나옵니다. 여기서 옵션을 마우스로 클릭하면,

이렇게 나옵니다.

여기서 고급을 클릭하면

여기서 마우스 휠을 돌려서 밑으로 가서 일반을 찾습니다. 거기에서 "사용자 지정 목록 편집(O)을 찾아서 클릭합니다.

그러면 이런 창이 뜹니다. 여기에 우리가 알고 있는 것들이 있습니다. 요일부터 자, 축, 인, 묘로 시작되는 띠도 있고, 갑, 을, 병, 정도 있습니다. 그러나 마우스로 밑에 내려보면 직급은 없습니다.

이렇게 목록 항목에 직급을 넣고 추가를 누릅니다.

이렇게 사용자 지정 목록에 추가가 됩니다. 확인을 누르고 나옵니다. 그럼 잘 되었는지 확인을 해봅니다.

아무 곳이나 대표이사를 입력하고 마우스 오른쪽 버튼을 눌러서 드래그를 하면 풀다운 메뉴가 나오고 여기서 두 번째

 

"연속 데이터 채우기" 누르면

이렇게 채워집니다. 자주 사용하는 목록이 있다면 이렇게 등록하여 사용하면 됩니다.

오늘은 사용자 지정 목록에 대해 알아봤습니다.

간단하게 동영상으로 만들어보았습니다. 한번 확인해보십시오.

여기까지입니다. 모르것이나 질문 사항 있으시면 댓글로 남겨주십시오. 최대한 노력해서 알려드리겠습니다.

 

 

엑셀 관련 다른 글을 보시려면 여기를 클릭해주세요.

  1. Patrick30 2019.05.09 09:25 신고

    오 이런기능은 처음 봤습니다 ㅎ
    별도로 등록이 가능하군요 ㅎ
    잘 보고 갑니다 ^^

오늘은 엑셀에서 IF함수에 대해 알아보겠습니다.

우선 도움말에 나오는 뜻을 적어보면, IF 함수는 가장 많이 사용하는 Excel 함수 중 하나로, 특정 값과 예상 값을 논리적으로 비교할 수 있는 함수입니다. 따라서 IF 문에서는 두 개의 결과가 나타날 수 있습니다. 첫 번째 결과는 비교가 True인 경우이고 두 번째 결과는 비교가 False인 경우입니다. 예를 들어 =IF(C2=”Yes”,1,2)는 IF(C2 = Yes이면 1을 반환하고, 그렇지 않으면 2를 반환한다)라는 의미입니다.

IF함수 중에서 좋은 것은 중첩이 가능하다는 것입니다. 여기서 "중첩"은 하나의 수식에서 여러 함수를 결합하는 방식을 가리킵니다.

인수가 3가지 이상이면, IF함수 안에 IF함수를 넣을 수 있습니다.

예를 들면 노란색 칸에 A부품 두께가 들어오면 밑에 줄에서 제품명 옆에 제품명이 나오게 만드는 것입니다.

처음은 A부품두께가 24와 28만 비교하면 함수는

=IF(B2=24, D13, D14)라고 입력하고

노란색칸에 이 둘 중에서 24를 입력하면 제품명: AAA 사용이라고 값이 나옵니다.

그러면 비교할 값이 3개이상이 되면 이 함수를 중첩하여 사용합니다.

도움말에서 Excel에는 서로 다른 IF 함수를 64개 중첩할 수 있지만 그렇게 하는 것은 전혀 바람직하지 않습니다. 다음과 같은 이유 때문입니다.

  • IF 문이 여러 개이면 문을 올바르게 작성하고 논리에 따라 각 조건이 끝까지 올바르게 계산되도록 고심해야 합니다. 수식을 100% 정확하게 중첩하지 않으면 75%는 제대로 실행되지만 25%는 예상치 못한 결과가 반환될 수 있습니다. 안타깝게도 이 25%를 파악하는 것은 쉽지 않습니다.

  • IF 문이 여러 개이면 유지 관리가 매우 어려워질 수 있습니다. 자신이나 다른 사람이 작성하던 문을 나중에 다시 파악하려는 경우 특히 그렇습니다.

이라고 나와있습니다.

A부품의 두께가 24, 28, 32, 36 중에서 하나의 값을 보여주려면

=IF(B2=24, D13, IF(B2=28, D14, IF(B2=32, D15, IF(B2=36, D16, ""))))입니다.

계속 IF를 중첩하면 되지만 이때 도움말처럼 유지관리가 힘들어집니다.

그래서 제가 알려드리는 방법은 IF함수와 &를 같이 쓰는 것입니다.

=IF(B2=24, D13, IF(B2=28, D14, IF(B2=32, D15, IF(B2=36, D16, "")))) 를

=IF(B2=24, D13, IF(B2=28, D14, "")&IF(B2=32, D15, "")&IF(B2=36, D16, ""))

으로 바꿀 수 있습니다. 비교대상이 더 많아지면 괄호의 개수도 틀릴가능성이 있습니다.

그런데 &와 함께 쓰면, 계속 붙이기만 하면 됩니다. 그리고 회사 컴퓨터에서는 오피스 365였는데 그 버전에서는 비교대상이 7개가 넘으면 IF함수를 쓸 수가 없었습니다. 중첩의 제한이 있을 때 &와 같이 쓰면 비교대상의 제한이 없습니다.

간단하게 영상을 올려보았습니다.

여기까지입니다. IF함수에 대해 알아보았습니다.

 

엑셀 관련 글을 보시려면 여기를 클릭해주세요.

  1. Patrick30 2019.05.03 08:46 신고

    아 if를 이렇게 쓸수도 있군요 ㅎ
    근데 if가 너무 많으면 정말 좇아가기 어렵더라구요 ㅎ
    잘 보고 갑니다 ^^

    • 늘 언제나 키리나 2019.05.03 09:31 신고

      if를 많이 쓰면 눈이 많이 아프죠.^^
      자주 찾아와 주셔서 감사합니다. 저도 자주 방문하겠습니다.

+ Recent posts