가장 빈번하게 사용하는 프로그램인 엑셀의 팁(Tip)을 예제와 함께 정리했다. 여기에 없는 것들은 구글링하자.
첫째줄 고정하기
필터를 적용할 시에 항목명이 있는 첫째줄을 고정하는 것이 보기에 편리하다. '보기 - 틀 고정' 메뉴에서 '첫 행 고정' 을 선택한다.
vlookup 함수사용하기
가장 빈번하게 사용되는 vlookup 함수는 아래와 같이 쓴다.
=VLOOKUP(J3,상품목록!$A$2:$Q$1904,8,FALSE)
유의할 점은 참조할 영역 앞에 '$' 를 붙이는 것이다.
에러가 발생하는 경우
참조하는 셀의 값에 공백 때문일 수 있다. 이때 'TRIM' 함수를 사용한다.
엑셀 괄호 안 문자 추출하기
'에어컨(LG)' 에서 'LG' 라는 문자만 뽑아내고 싶다면, 아래와 같이 함수를 사용한다.
=MID(셀,FIND("(",셀)+1,FIND(")",셀,FIND("(",셀)+1)-FIND("(",셀)-1)
특정 문자 이전까지 추출하기
'백미_12322420' 에서 앞의 '백미' 라는 문자면 뽑아내고 싶다면, 아래와 같이 함수를 사용한다.
=LEFT(셀, FIND("_", 셀)-1)
vlookup 함수 확장하기
vlookup 함수는 해당 셀의 입력 값이 정확히 일치해야만 값이 출력된다. 전체입력된 값에서 일부만 있거나 할 때는 원하는 값이 출력되지 않는다.
아래의 예제를 보자.
| 이름 | 주소 | 전화 |
|---|---|---|
| 홍길동 | 서울 | 010-3023-4343 |
| 임꺽정 | 춘천 | 010-1223-2232 |
| 성춘향 | 남원 | 010-3422-2332 |
| 정형돈 | 강남 | 010-1234-2232 |
위의 데이터가 sheet1 에 있다고 하자. sheet2 에서 vlookup 함수를 이용해서 값을 출력하고 싶다.
=VLOOKUP(A1,Sheet1!$A$2:$C$4,2,FALSE)
| 홍길동 | 서울 |
| 형돈 | #N/A |
정확히 '정형돈'을 입력해야만 주소가 출력되고, 일부인 '형돈'을 입력하면 에러가 발생한다. '형돈'을 입력해도 출력하고 싶다.
아래와 같이 함수를 수정하자.
=VLOOKUP("*"&A2&"*",Sheet3!$A$2:$C$5,2,FALSE)
'형돈' 또는 '정형' 만 쳐도 주소가 나온다. 이때 유의할 점이 있다. 만일 '정' 을 입력한다면, '임꺽정'과 '정형돈'이 해당된다. 이때는 먼저 검색되는 '임꺽정'의 주소인 '춘천'이 출력된다.
괄호 안의 문자 추출하기
아래와 같이 두개의 괄호 중에 마지막에 있는 것을 추출하고자 할 때는 아래처럼 함수를 입력한다.
| 감귤(조생)(kg) | =TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1, “)”,“”),“(”,REPT(“ ”,99)),99)) |
결과는 'kg' 가 나온다.
거래처 추출하기
품목명을 보고 이에 맞는 거래처를 반환하는 재귀함수다.
=IF(ISNUMBER(SEARCH("백미",A1)),"칠성유기농",IF(ISNUMBER(SEARCH("찹쌀",A1)),"칠성유기농",IF(ISNUMBER(SEARCH("멥쌀",A1)),"칠성유기농",IF(ISNUMBER(SEARCH("가루",A1)),"괴산군조공",IF(ISNUMBER(SEARCH("5곡",A1)),"괴산군조공",IF(ISNUMBER(SEARCH("감자",A1)),"괴산먹거리연대",IF(ISNUMBER(SEARCH("양파",A1)),"괴산먹거리연대",IF(ISNUMBER(SEARCH("마늘",A1)),"괴산먹거리연대",IF(ISNUMBER(SEARCH("무",A1)),"흙살림",IF(ISNUMBER(SEARCH("양배추",A1)),"흙살림",IF(ISNUMBER(SEARCH("표고",A1)),"이길재",IF(ISNUMBER(SEARCH("느타리",A1)),"정용희",IF(ISNUMBER(SEARCH("유정란",A1)),"눈비산",IF(ISNUMBER(SEARCH("사과",A1)),"가을농원",IF(ISNUMBER(SEARCH("배",A1)),"김호진",IF(ISNUMBER(SEARCH("대파",A1)),"흙살림",IF(ISNUMBER(SEARCH("실파",A1)),"흙살림",IF(ISNUMBER(SEARCH("쪽파",A1)),"흙살림",IF(ISNUMBER(SEARCH("당근",A1)),"흙살림",IF(ISNUMBER(SEARCH("현미",A1)),"우미영농조합",IF(ISNUMBER(SEARCH("흑미",A1)),"우미영농조합",IF(ISNUMBER(SEARCH("녹두",A1)),"우미영농조합",IF(ISNUMBER(SEARCH("백태",A1)),"우미영농조합",IF(ISNUMBER(SEARCH("콩",A1)),"우미영농조합",IF(ISNUMBER(SEARCH("서리태",A1)),"우미영농조합",IF(ISNUMBER(SEARCH("압맥",A1)),"우미영농조합",IF(ISNUMBER(SEARCH("찰수수",A1)),"우미영농조합",IF(ISNUMBER(SEARCH("12곡",A1)),"우미영농조합",IF(ISNUMBER(SEARCH("율무",A1)),"우미영농조합",IF(ISNUMBER(SEARCH("통밀",A1)),"우미영농조합",IF(ISNUMBER(SEARCH("귀리",A1)),"우미영농조합",IF(ISNUMBER(SEARCH("한우",A1)),"청풍명월",IF(ISNUMBER(SEARCH("돈육",A1)),"청풍명월",IF(ISNUMBER(SEARCH("고기",A1)),"청풍명월",IF(ISNUMBER(SEARCH("계육",A1)),"청풍명월",IF(ISNUMBER(SEARCH("돼지",A1)),"청풍명월",IF(ISNUMBER(SEARCH("오리",A1)),"청풍명월",IF(ISNUMBER(SEARCH("된장",A1)),"솔뫼영농조합",IF(ISNUMBER(SEARCH("고추장",A1)),"솔뫼영농조합",IF(ISNUMBER(SEARCH("양지",A1)),"청풍명월","아님"))))))))))))))))))))))))))))))))))))))))