엑셀은 파일로 된 데이터베이스이기 때문에 데이터베이스의 기본적인 찾기와 같은 기능 역시 사용할 수 있습니다. 이와 같이 값을 찾아서 해당 하는 값을 넣고 싶을 경우 LOOKUP이라는 함수를 사용하게 되는데요. 구글 시트와 엑셀을 기반으로 설명을 해보도록 하겠습니다. 대상 데이터 우선 설명을 하기 위해서 아래와 같은 모습의 시트를 만들어보겠습니다. 나라 코드별, 나라명이 대입된 시트인데요. 간단하게 5개의 나라만 입력하였습니다. 국가코드 국가명(한글) KO 대한민국 JP 일본 CN 중국 US 미국 TW 대만 회사 데이터 예시로 데이터는 회사별 국가코드가 있다고 가정하고, 해당 코드를 기반으로 국가명을 가져오는 실습을 해보겠습니다. 나라 나라명 회사 US 테슬라 KO 삼성전자 TW TSMC CN 텐센트 KO..
우리가 숫자를 처리 할 때 특정 소수점에 값을 올리거나, 내리(버리)거나 혹은 가장 가까운 수로 올리거나 내리(버리)고 싶을 경우가 있습니다. 이렇게 반올림,올림,내림을 써야 할 경우 사용하는 함수를 알아보도록 하겠습니다. 반올림, ROUND ROUND 함수는 엑셀에서 반올림을 하는 함수로, 인자값은 2개를 제공합니다. round(값, 자리수) 첫번째 인자는 대상이 되는 값을 넣으면 되고, 2번째 인자는 반올림을 원하는 자리수를 넣으면 됩니다. 위와 같이 2/3의 값을 예시로 설명을 해보도록 하겠습니다. 이 값을 반올림하여 정수로 형태로 변경을 하고 싶을 경우, 자리수를 0으로 적으면 됩니다. 0으로 하게 되면, 반올림하면서 정수형으로 변하게 됩니다. 두번째 인자값을 2로 할 경우 아래와 같이 소수점 2..
엑셀에서는 $을 사용하여 셀을 고정하는 기능이 있습니다. 예제를 보면서 어떨때 사용하면 좋은지 알아보도록 하겠습니다. 예제, 복리 계산 아래 스크린샷은 복리를 계산하기 위해서 만든 것으로 1년에 3%의 이자를 받게 된다고 가정을 하고, 100만원의 돈을 입금하면 복리로 얼마를 받을까를 계산하는 엑셀 시트입니다. 그럼 여기서 이자를 계산하는 C3 셀에는 일반적으로 B3 셀과 C1 셀을 곱하는 수식이 들어가 있을 것입니다. 하지만 이렇게 만들 경우 3번째 행을 복사해서 아래로 붙이게 될 경우, 아래와 같은 현상이 발생하게 됩니다. 수식이 깨진 이유는 복사를 하게 되면서 C1의 셀이 한칸씩 다음값으로 바뀌면서 C4번째의 수식이 다음과 같이 변경되었기 때문입니다. 우리가 원하는 수식은 B의 값은 계속 바뀌길 원..
엑셀에서는 UTF-8로 된 파일을 그냥 읽을 경우 캐릭터셋(Character Set, Charset)이 맞지 않아 인코딩에 잘못되어 깨져서 나오게 된다. 원본 데이터 id,title 1,한글로된 제목 2,테스트 위와 같이 한글로 되어 있고 UTF-8로 저장이 되어 있는 CSV를 엑셀로 읽어보려 시도를 하면 아래와 같이 깨져서 출력이 된다. 그림 1처럼 깨져서 나올 경우 방법이 크게 2가지가 있는데 하나는 데이터를 UTF-8에서 다른 인코딩으로 변환하여 저장 후 불러오는 것이며 (데이터 손실 가능) 다른 하나는 불러올 때 캐릭터셋을 변경하여 읽는 방법이 있다. 캐릭터셋 지정 불러오기 그림 2와 같이 데이터 탭 -> 텍스트 선택 원하는 파일을 선택한 후, 가져오기 클릭 그림 4. 처럼 65001 : 유니코드..
컬럼에 값이 없을 때 해당 행을 일괄적으로 삭제하고 싶은 경우가 있다. 위와 같은 예시가 있다고 가정해보자. 2번, 5번, 8번, 9번의 행을 삭제하고 싶을 경우 하나하나 삭제를 하는 것이 귀찮은데 데이터가 만약에 만건이 넘어갈경우 정말 끔찍할 것이다. 이럴 때 다음과 같은 방법으로 엑셀 데이터를 제거할 수 있다. Step By Step 우선 표에서 빈값이 있는 범위를 지정한 후, 홈 -> 찾기 및 선택 -> 이동 옵션을 선택한다 이동 옵션은 홈을 선택한 후 제일 우측에 있기 때문에 찾기 힘들 수 있지만, 위와 같은 화면대로 선택하면 된다. 그리고 단축키로 좀 더 쉽게 알 수 있는데 Ctrl + G를 누르면 된다. Ctrl + G를 누를경우 위와 같은 이동 창이 등장하며 아래의 옵션 버튼을 클릭하면 이동..
날짜값들의 차이를 구하는 방법이 하나만 있는게 아닌데 우선 매우 간단한 DAYS부터 설명해도록 하겠습니다. DAYS 함수 (일수 계산) DAYS는 날짜값간의 차이를 일수로 리턴해주는 매우 심플한 함수입니다. 사용법 DAYS(date1, date2) 이처럼 2개의 인자값을 넣어주면, 해당 날짜간의 차이를 리턴합니다. 참고로 첫번째 날짜가 두번째 날짜보다 더 이전이면, 음수값이 리턴이 됩니다. 그러니 첫번째 값에 더 최근의 값을 넣고, 뒷값에 이전값을 넣는 방식을 하거나 계산 이후 절대값 함수(ABS)를 호출하면 됩니다. 위 이미지를 보면, A1이 A2보다 날짜가 더 이전이다보니 연산 결과가 음수로 나왔습니다. 이렇게 음수값이 나와도 최종적으로 ABS를 붙이면 양수로 변환이 되니, 날짜 계산을 할 때에는 A..
1,테스트1 2,테스트2 3,테스트3 4,테스트4 5,테스트5 6,테스트6 7,테스트7 8,테스트8 9,테스트9 10,테스트10 위와 같은 데이터가 있다고 가정을 할 때 위 내용을 그대로 복사(copy) 엑셀에 붙여넣기(paste)를 하면 알아서 분리가 될 것으로 생각하지만 그렇게 생각대로 되지 않는다. 이럴 경우 데이터를 분리하는 작업을 진행해야 한다. 데이터값 구분 Step by Step 우선 나눌 데이터를 엑셀에 복사 및 붙여넣기(Copy & Paste)를 수행한다. 이때 콤마(,)값을 인식하지는 못하지만 위와 같이 행은 인식한다. 즉, 예제를 가지고 했다면 A1행부터 A10행까지 만들어졌을 것이다. 나눌 데이터를 드래그(drag)하여 선택을 하고, 메뉴에서 데이터 탭을 선택하여 텍스트 나누기 아이..
엑셀을 읽는 방식은 상당히 스마트 하지 않다는 것을 다들 잘 알고 있을 것이다. 셀 영역 하나하나를 무슨 형인지 지정을 해야 하는데 마치 DB에서 getString, getInt하는 모양새와 유사하다. 데이터는 숫자가 들어가 있을 수 있고 문자 등이 들어갈 수 있는데 현재 내가 발생한 문제는 하나의 열에 숫자와 문자가 공존하는 형태이다. 예를 들어 이런 경우가 있을 수 있을 것이다. 첫번째 컬럼은 분류값이 들어있고, 두번째 컬럼에는 분류의 결과값이 들어가 있다고 가정을 해보자 첫번째 로우(row)의 분류값에 월급이라는 값이 들어가 있어서 결과값 컬럼에 숫자가 들어갔다고 하고 두번째 로우의 분류값에 이름이라는 값이 들어가 있어서 결과값 컬럼에 문자가 들어가 있다고 가정을 할 시 어떻게 해야 할까? 일반적으..
자바에서 Excel을 읽기 위해서, Apache POI 라이브러리를 사용하여 엑셀파일을 읽는 도중, ClassNotFound 에러가 발생하였다. 엑셀파일을 DB에 넣는 클래스를 만드는 도중이었는데 에러 메세지는 아래와 같다 BEGIN: load PI-Exln excel file to DBBEGIN: initializeEND: initializeBEGIN: read excelException in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlObjectat com.saramin.sai.service.PIExlnLoad.readData(PIExlnLoad.java:115)at com.saramin.sai.service.PIExln..
