직장인의 엑셀, VBA 그리고 Python

[ 응용 ] 엑셀 두 데이터 비교하기, 누락된 값 찾기

지난 블로그에서 두 데이터를 비교하고,조건부 서식을 사용하여다른 부분에는 색을 칠하여 확인하는 방법을 알아보았습니다.☞ 엑셀 두 데이터 비교하기, 엑셀 데이터 비교하여 다른 부분 찾기 이번에는 동일한 주제를 가지고 함수를 사용하여 두 데이터를 비교하는 방법을 알아보겠습니다.  IF, COUNTIF 함수 사용하여 데이터 비교하기  한 목록에서 다른 목록에 누락된 값을 찾으려면 IF 함수, COUNTIF 함수를 사용하여 값을 찾을 수 있습니다. 아래 그림의 원본과 체크를 비교하여원본에는 없지만 체크에는 있는 데이터를 찾아 결과에 표시하는 방법입니다. 함수식은 D2셀부터 D18셀까지 입력하고,사용할 함수식은 다음과 같습니다.=IF(COUNTIF($A$2:$A$11,C2), "존재", "부존재") 위 함수식을 ..

[ 응용 ] 한글 문서 엑셀로 변환하기, 한글 표 엑셀로 옮기기

엑셀 자동화를 하다 보면,한글 프로그램으로 작성한 문서를엑셀에 옮겨야 하는 경우가 종종 있습니다. 한글 문서를 엑셀에서 불러올 수는 없지만,한글 문서를 다른 문서로 변환한 후엑셀로 불러올 수 있습니다. 물론 두 프로그램의 차이점이 있기 때문에일부 문서들은 불러오더라도한글 문서에서 보이는 모양 그대로 유지되지는 않을 수 있습니다.  한글 프로그램 설정  한글 프로그램 실행 후도구 → 환경설정을 눌러 환경 설정 창을 띄워줍니다.그다음 파일 탭을 선택저장 파일 형식에 인터넷 문서(*.htm) 추가에 체크해 줍니다. 이 설정을 해주는 이유는 htm 파일은엑셀이 지원하는 확장자로, 어느 정도 양식을 유지한 채 엑셀로 불러올 수 있는 파일이기 때문입니다. 환경설정을 하지 않고, 기본적으로 .htm 확장자로 저장을 ..

엑셀 목푯값 찾기 기능은 목표를 달성하기 위해 얼마만큼의 실적이 더 필요한 지 자동으로 계산해 주는 기능입니다.  자료 준비  위 그림과 같은 자료가 있습니다.E2셀(합계)과 F2셀(평균)에는 아래와 같이 수식이 입력되어 있습니다.=SUM(B2:D2)    ※ E2셀 수식(합계)=E2/3                 ※ F2셀 수식(평균)홍길동이라는 학생은 국어와 영어에서 각각 80점, 90점의 점수를 얻었으며,평균 90점을 목표로 하고 있습니다. 이 상황에서 평균 90점을 달성하기 위해서수학과목의 점수를 몇 점을 받아야 하는 지 자동으로 계산해 주는 기능이목푯값 찾기 기능입니다.  목푯값 찾아보기  아래 그림과 같이데이터 → 가상분석 → 목표값 찾기메뉴를 차례로 클릭합니다. 아래 그림과 같은 창이 나타..

주민등록 번호를 엑셀에 입력할 때 형식이 맞는지 확인하는 데이터 유효성 검사 함수식입니다.  준비하기  아래 그림과 같이 이름과 주민등록번호를 입력해야 할 때B열에 입력되는 값이 주민등록번호의 형식과 맞지 않으면 제한하는 방법입니다.  000000-0000000 형태로 입력 제한하기  주민등록번호의 형태를 분석해보면6자리 숫자와 "-" 그리고 7자리 숫자의 결합으로 되어 있습니다. B2셀부터 B13셀까지 이 형식에 맞는 데이터를 입력하게 하려면아래와 같은 순서로 진행하면 됩니다. 1. 데이터 범위 선택 2. 데이터 탭 → 데이터 유효성 검사 → 데이터 유효성 검사 3. 설정 → 사용자 지정 → 수식에 수식 입력 4. 수식 입력 아래 수식을 입력하면 주민등록 번호의 형태만 입력이 가능하도록 제한할 수 있습..

엑셀의 열 머리글은 아래 그림의 빨간색 네모 부분을 의미합니다.이 열 머리글을 A, B, C, D가 아닌 1, 2, 3, 4와 같은 숫자로 간편하게 변경하는 방법입니다.  열 머리글을 숫자로 바꾸는 이유 데이터 정렬: 숫자로 변경하면 데이터를 숫자 순서대로 정렬할 때 더욱 편리합니다.수식 작성: 일부 수식에서는 열 번호를 직접 입력해야 할 때가 있는데, 숫자로 표시되어 있으면 혼동 없이 입력할 수 있습니다.데이터 분석: 데이터 분석 시 숫자로 된 열 번호가 더 직관적일 수 있습니다.  열 머리글을 숫자로 바꾸는 방법 1. 파일 메뉴를 클릭합니다. 2. 옵션을 선택합니다.(좌측 하단) 3. 수식 탭으로 이동 후 R1C1 참조 스타일 옵션에 체크 표시를 합니다. 4. 확인 버튼을 클릭합니다.  다시 문자로 ..

VLOOKUP 함수는 정말 사용 빈도가 높은 함수입니다.하지만 이 함수는 기준이 되는 셀의 오른쪽 값만 찾을 수 있다는 단점이 있습니다. 하지만 VLOOKUP 함수도 다른 함수와 결합하면왼쪽 값을 찾을 수 있습니다.엄밀히 말하면 왼쪽 값을 찾는다기 보다는왼쪽 값을 오른쪽 값인 것처럼 만들어서 찾는 방법입니다.  준 비 사 항  VLOOKUP 함수와 CHOOSE 함수의 기본에 대해서는 아셔야 합니다.☞ 엑셀 VLOOKUP 함수, 대응되는 값 불러오기☞ 엑셀 CHOOSE 함수 : 선택적으로 계산하는 방법 CHOOSE 함수를 설명할 때데이터의 구조를 바꿀 수 있는 방법을 말씀드렸습니다.그 방법과 VLOOKUP 함수를 같이 사용하면원본데이터에서 왼쪽에 있는 값을 찾을 수 있을 뿐만 아니라한 번에 여러 개의 값도..

CHOOSE 함수는 주어진 여러 가지 값 중에서 특정 번호에 해당하는 값을 선택하여 반환하는 역할을 합니다. 마치, 나에게 숫자를 입력하세요.그러면 목록 중에서 그에 할당된 항목을 알려드리겠습니다.라는 말과 같이 동작합니다.  기본 구문  CHOOSE(index_num, value1, value2, ..... )● index_num : 선택할 값의 위치를 지정합니다. 범위는 1~254입니다.● value1 ... : 선택 가능한 여러 가지 값입니다. 최대 254개까지 지정이 가능합니다.  기본적인 사용방법  아래 그림과 같이 사용이 가능합니다.=CHOOSE(1, "사과", "배", "감")위와 같이 수식을 사용하면 "사과", "배", "감" 중에서 첫번째 값인 "사과"를 반환하라는 의미입니다. value..

병합된 셀에 합계를 구하는 방법을 알아보기 전에먼저 엑셀에서 Ctrl + Enter의 의미에 대해 알아보겠습니다.  Ctrl + Enter의 의미  일정 범위의 셀을 선택한 후 어떤 값 또는 수식을 입력하고 Ctrl + Enter를 누르면, 선택된 범위에 모두 같은 값 또는 수식이 입력이 됩니다. 위 방식을 사용하여 병합된 셀에 합계를 구할 수 있습니다.  병합된 셀에 합계 구하기  아래 그림을 먼저 살펴보겠습니다. 위 그림과 같이 B열의 같은 색의 값의 합계 구하여 C열에 넣는다고 할 때셀이 병합되어있기 때문에 자동 채우기도 되지 않고설령 자동 채우기가 된다고 하더라도갯수가 불규칙하기 때문에 정상 작동이 되지 않는 구조입니다.  먼저 결론부터 말씀드리면먼저 아래 그림과 같이 셀 범위를 선택을 해줍니다...

일정 간격의 행마다 색을 칠해사용자가 보기 좋게 만들어야 하는 경우가 있습니다. 일정 간격의 행마다 색을 칠하는 몇 가지 방법을 알아보겠습니다.먼저 아래 그림과 같은 데이터가 있다고 가정하겠습니다.  간단한 기본 기능 사용  먼저 아래 그림과 같이 범위를 선택해 줍니다. 그 다음 홈 → 표 서식을 눌러 마음에 드는 서식을 지정해 줍니다.많은 서식이 두 번째 줄마다 색을 칠해 구분하고 있습니다. 서식을 지정하면 아래와 같이 창이 나타납니다.구분하는 제목(위 표의 구분, 점수 같은)이 따로 있다면 머리글 포함을 눌러주시면 됩니다.  별도의 행에 숫자 입력 후 색칠하기  아래 그림과 같이 별도의 열에 숫자 1, 2를 적고 1, 2가 입력된 셀을 선택한 후파란색 부분에 마우스를 올린 다음 CTRL을 누르고 아래..

엑셀을 사용하면 간단하게 일정표나 공정표 제작이 가능합니다.아래는 간단한 일정표 샘플입니다.  사전준비   먼저 아래 그림과 같이 형식을 만들어 줍니다.구분에 작성한 할 일 또는 공정 목록은 사용자가 필요한 내용을 입력하시면 됩니다. 중요한 것은 시작일과 종료일을 입력하고빨간색 네모와 같이 해당 월의 날짜를 가로로 채워주시면 됩니다. 이렇게 작성하면 가로의 길이가 너무 길어지므로빨간 네모 부분의 서식을 변경해 보겠습니다.빨간 네모 부분의 날짜를 모두 선택하고 CTRL+1을 눌러  서식을 열어줍니다. 아래 그림과 같이 날짜의 표시형식을 바꾸어줍니다.  그러면 날짜의 형식이 아래 그림과 같이 바뀌게 됩니다. 이제 가로로 날짜가 입력된 부분의 열 너비를 조정합니다.그리고 행 높이도 추가로 조정하였습니다.예제에..