본문 바로가기

엑셀257

엑셀] 입력할 데이터를 미리 정하기 - 유효성 검사 실무에서 일할 때 엑셀로 양식을 만들어서 배포하고, 다른 사람들이 그 양식을 채워서 보내면 수합하는 일을 할 때가 있다. 담당자 입장에서는 귀찮은 일이란건 확실한데, 보내온 파일의 숫자가 텍스트 형식이라든지, 부서명을 약자로 표기하거나 오타를 입력한 경우, 수합해서 정리해야 하는 담당자 입장에서는 짜증이 뽀록뽀록 밀려오게 마련이다. 이런 경우 입력할 데이터를 미리 정해줄 수 없을까?? 있다. ^^; 엑셀에서 제공하는 가 그런 기능을 제공한다. 많이 사용되는 기능은 드롭 다운 화살표를 클릭해서 선택할 목록이 나타나게 하는 방법이다. 여기서는 소속 부서명을 드롭다운으로 선택하도록 한다. 소속열을 블록으로 선택하고 [데이터 리본 - 데이터 도구 - 데이터 유효성 검사]를 클릭한다. [제한대상]을 클릭해보면 제.. 2014. 2. 6.
엑셀] 텍스트를 숫자로, 숫자를 텍스트로 - VALUE, TEXT 함수 시스템에서 데이터를 추출해서 엑셀에 붙여넣었을 때 숫자로 인식하지 않고 텍스트로 인식해서 엉뚱한 계산식이 나올 때가 있다. 다음 그림의 경우 SUM 함수를 썼는데 엉뚱한 결과가 나온다. 엑셀에서는 이렇게 숫자가 아닌 텍스트인 경우 셀 왼쪽 위 초록색 삼각형이 나타난다. 해당 셀을 클릭하면 느낌표가 나타나고, 느낌표에 마우스를 갖다대면 숫자가 텍스트로 지정되어있다고 알려준다. 이런 문제를 해결하는 방법은 여러가지가 있는데 대표적인 방법 다섯가지를 하나씩 살펴보자. 1. 느낌표를 눌러서 숫자 형식으로 바꿔준다. 2. 텍스트로 표시된 셀이 여러 개라면 셀들을 블록으로 잡고 느낌표를 눌러서 숫자 형식으로 바꿔준다. 3. VALUE 함수를 써서 텍스트를 숫자로 바꿔준다. * 엑셀2013부터 numbervalue함.. 2014. 2. 4.
엑셀] 셀 범위에서 찾는 값이 몇번째 있을까?? - MATCH MATCH 함수는 셀 범위에서 지정된 항목을 검색한 다음, 범위 내에서 해당 항목의 위치값을 반환한다. MATCH 함수의 첫번째 인수는 검색할 항목두번째 인수는 검색할 셀 범위세번째 인수는 MATCH 타입(정확도 관련)이다. 다음 그림으로 D10 셀에 입력된 MATCH 함수의 사용 예를 보자 참고로 검색하는 항목(B10)과 셀 범위(B1:F1)은 같은 셀서식이어야 한다. 같은 텍스트로 지정되거나, 같은 숫자 형식이어야 한다. (여기서는 숫자로 입력하고 에서 으로 지정해 두었다) 세번째 인수에 대해 부연설명하면 0, 1, -1 이 옵션값이다. 입력하지 않으면 디폴트 1이고, 셀 범위가 오름차순이어야 하고 검색 항목보다 작거나 같은 값 중에서 최대값을 찾는다. 0은 셀 범위의 정렬 여부와 관계없이 검색 항목.. 2014. 2. 3.
엑셀] 표에서 원하는 값 찾기(3) - INDEX 엑셀의 표에서 원하는 데이터를 찾는 함수는 VLOOKUP 함수와 HLOOKUP 함수 이외 INDEX 함수가 있다. 엑셀] 표에서 원하는 값 찾기(1) - VLOOKUP 엑셀] 표에서 원하는 값 찾기(2) - HLOOKUP 함수 INDEX 함수는가로와 세로 순서값을 인수로 전달해서 그 교차점에 있는 값을 반환하는 함수이다. 아주 간단한 예를 들어보면 위 그림과 같은 급여표에서 각 직급과 호봉에 해당하는 급여값을 반환하는 방법은 VLOOKUP이나 HLOOKUP으로도 가능하지만INDEX 함수를 사용하는게 가장 간단한 경우 이다.(개인적인 의견으로 엑셀에서 데이터를 찾는 방법은 여러 가지 함수를 사용할 수 있지만상황에 따라, 자기 손에 익은 방법으로, 제일 빨리 수식을 만들 수 있는 방법을 사용하는게 정답인듯 .. 2014. 2. 2.
엑셀] 표에서 원하는 값 찾기(2) - HLOOKUP 함수 엑셀에서 VLOOKUP 함수는 기준이 되는 표에서 다른 데이터와 구분이 되는 기준값으로부터 원하는 값을 찾을 때 사용된다. VLOOKUP은 vertical 이란 말 그대로 세로로 기준 값이 정렬 되어있을 때 찾는다. 위 그림처럼 찾는 기준인 사번이 세로로 쭉~ 입력되어 있다면 검색으로 VLOOKUP 함수를 사용한다. 엑셀] 표에서 원하는 값 찾기 - VLOOKUP HLOOKUP 함수는 VLOOKUP 함수와 비슷한데, 검색을 가로를 기준으로 한다는 점에서 차이가 있다. 위 조견표처럼 기준이 되는 값들이 가로로 쭉~ 입력되어 있고, 그 아래로 해당값을 찾을 때는 HLOOKUP 함수를 사용한다. 기본적인 사용방법이나, 함수인수는 VLOOKUP 함수와 동일하다. HLOO.. 2014. 2. 2.
엑셀] 표에서 가로 세로(행/열) 바꾸기 - 선택해서 붙여넣기 업무에 따라 표로 정리하다보면 행/열(가로와 세로)를 바꾸는 것이 좀 더 명확하게 데이터를 표현하는 경우가 있다. 예를 들면 표의 가로와 세로, 즉 행과 열을 바꾸는 경우가 그렇다. 위 그림에서 가로와 세로(행/열)을 바꾸려면 표 전체를 복사하고를 선택하거나를 눌러서 를 선택한다. 선택하여 붙여 넣기 중에서 제일 아래 을 선택하고 확인 버튼을 누르면 표의 가로와 세로(행/열)이 바뀐다. 선택하여 붙여넣기에서 또 자주 사용하는 붙여넣기 방법은 이다 엑셀에서 여러 시트의 데이터로 부터 계산을 하거나, 다른 엑셀 파일들과 연결하여 계산을 하고나서, 최종 완성본에 해당하는 하나의 시트만 남길 경우관련된 파일이나 시트를 삭제하면 계산 오류가 나기 때문에을 선택해서 붙여넣으면, 엑셀 계산식은 없어지고, 그 때까지의.. 2014. 1. 28.
엑셀] 표에서 원하는 값 찾기(1) - VLOOKUP 업무용으로 엑셀을 사용하는 경우 소스가 되는 데이터들로부터 필요한 데이터들을 추출해서 사용하게 된다. 소스 데이터는 전체 직원들의 사번을 포함한 명단일 수도 있고, 직급별, 호봉별 급여표같은 조견표일 수도 있다. 이처럼 어떤 기준이 되는 표에서 필요한 데이터를 찾을 때 제일 많이 사용하는 함수 중 하나가 VLOOKUP 함수이다. 보통 기준이 되는 표는 사번이나 제품번호처럼 다른 데이터들과 확실히 구분되는 Key값을 갖는다. 경험상 데이터 검색시 이런 key 값을 활용하는 것이 정확한 데이터 추출에 도움이 된다 아래와 같은 기준 데이터가 있다고 가정하자. 여기서 사번정보를 이용해서 이름, 집주소 등의 정보를 추출하려고 하는 경우를 살펴보자. VLOOKUP함수는 인수를 네가지를 사용한다. 도움말에는 이렇게 .. 2014. 1. 27.
엑셀] 고급 필터 기능으로 복수 조건 데이터 검색 엑셀의 필터기능은 데이터를 원하는 조건에 맞춰 간단하게 추출할 수 있는 기능이다. 다음 표에서 본사에서 근무하고, 급여가 3,500,000원 이상인 사람들을 추출해보자. 먼저 필터에서 근무지를 본사로 설정하고,급여열에서는 을 선택하고 찾을 조건을 3,500,000원보다 크다를 선택한다. 이렇게 자동필터를 이용해서 간단하게 본사에서 근무하고, 급여가 3,500,000원 이상인 사람들을 추출할 수 있다. 위 방법처럼 자동필터를 이용하는 방법도 있지만, 고급 필터 기능을 이용해서 동일한 결과를 얻을 수도 있다. 고급 필터는 자동필터와 달리 별도의 조건범위 셀을 지정해 두어야 한다. 이제 을 클릭한다. 여기서 목록범위는 데이터를 추출할 목록이 있는 범위이고 조건범위는 오른쪽에 따로 만들어 놓은 조건 부분이다. .. 2014. 1. 25.
엑셀 TIP] 한/영 자동고침 문제 해결 아래한글이나 엑셀 프로그램은 한/영 자동전환 기능을 제공한다. 한글과 영문을 혼합해서 입력하는 일이 많을 때는 요긴한 기능이긴 한데, 가끔 전혀 도움이 안 될 때도 있다. ^^;; 약어나 ID, 고유명사를 입력하는데 자꾸 한글로 바뀌면 상당히 곤란하다.가령 아이디로 rkdk@hanmail.com을 쓴다면(이건 내 메일 주소가 아니고.. 예를 든다면..)rkdk를 입력해야 하는데 엑셀은 자동으로 한글로 바꿔서 '가아'로 바꿔버린다. 이럴 때 한/영 자동전환 기능을 해제시켜 놓으면 문제가 해결되긴 하지만, 평소엔 유용하게 사용할 수 있는 기능을 없애버리는 것도 마음에 들지 않는다면????? 팁! 고유명사처럼 본인이 자주 쓰는 단어인데 엑셀이 한글로 바꿔버린다면, 그 단어만 등록하면 된다. ^^ 엑셀 옵션 -.. 2014. 1. 23.

티스토리툴바