'업무 능력 향상/엑셀 유용팁'에 해당되는 글 17건

데이터량이 많을 때 중복체크 빠르게 하는 팁

엑셀 데이터량이 많을 때 중복체크를 하기 위해서 함수를 만들어서 사용하는 것이 좋을까요?

VBA 를 배우면서 중복체크 하는 걸 이용해보는데 속도문제가 장난아니게 느린 경우도 많이 발생하더군요.

아무래도 사용자가 만들어서 사용하는 알고리즘은 엑셀 자체에서 제공하는 알고리즘보다 최적화가 안되어 훨씬 느릴 수 있다는 점입니다.

데이터량이 많을 때 제가 사용하는 방법은

0. 가장 먼저 No를 줄 열을 생성해서 순차 번호를 기록한다. (자료가 틀어지는 것을 방지하기 위한 목적)

1. 먼저 제목열일 기준으로 필터를 걸어서 정렬하고 싶은 열을 내림차순/오름차순으로 정리를 한다.

2. 정렬하고 싶은 열(중복체크 대상)을 바로 위아래 열간에 중복체크를 하기 위해서

   바로 옆에 하나의 열을 추가한다.

3. 엑셀은 기본적으로 대문자, 소문자를 검사하지 않는데

   대문자, 소문자를 검사하는 EXACT 함수를 이용해서 위아래 열을 검사한다.

   즉, IF(EXACT(C2,C3),1,0) 이런 식으로 조건을 걸어서 검사한다.

4. 그래서 불일치된 열을 기준으로 소팅을 한다.

5. 불필요한 행만 선택해서 삭제를 한다.

이런 방법으로 하는게 때로는 중복체크하는 함수를 사용할 때보다 훨씬 더 빠른 결과를 얻을 수 있습니다.

조금이나마 도움이 되셨다면 공감 꾸~욱 눌러주세요.

엑셀에 삽입된 이미지만 일괄 삭제하는 방법

엑셀에 삽입된 그림이나 이미지만 지우고 싶은 경우가 있습니다.

하나 하나 일일이 지우려면 짜증나겠죠.

이럴 때 아래 그림처럼 하시면 바로 해결 됩니다.

이제 Deltet 키를 누르면 이미지가 다 지워집니다.

동일자료 중복검사

특정열 기준으로 동일자료가 얼마나 존재하는지 중복검사하고 싶을 때 검사하고 싶은 경우가 있습니다.

중복된 건수가 궁금한 경우에는 =COUNTIF($A$2:A2,A2)

'// 시작셀은 고정으로 두고 범위를 상대셀로 두어 계속 가변적으로 변하도록 만들어서 중복된 숫자를 다르게 카운트 하도록 하는 겁니다.

중복된 건수는 Blank 처리하고 싶은 경우에는 =IF(COUNTIF($A$2:A2,A2)=1,1,"")

그런 다음에 필터를 이용하여 정렬(sort)를 하면 어떤 자료가 중복된 것인지 육안으로 확인이 가능합니다.

텍스트로 된 숫자를 값으로 변경하는 쉬운 방법

엑셀을 다루다보면 자료가 값으로 된 숫자가 아니라 텍스트로 된 숫자로 표기된 경우가 있습니다.

주로 시스템에서 뽑아낸 값이 텍스트로 되어 있죠.

시스템에서 뽑아낸 데이터 값이 많은 경우에는 방법을 모르면 상당한 노가다를 할 수 있습니다.

좀 편하고 쉽게 하는 방법으로 두가지가 있습니다.

하나는 고정틀을 이용하는 방법이고 또하는 텍스트분할 방법입니다.

고정틀을 이용하는 방법

아무리 길어도 고정틀을 설정하고 나서 원하는 셀까지 한꺼번에 이동하는 건

END키를 누르고 화살표키를 움직여서 전체를 설정할 수 있습니다.

텍스트 분할을 이용하는 방법

이런 방법이 가장 편하고 빠르게 원하는 결과를 얻을 수 있습니다.

첨부파일 올려두니 받아서 직접 한번 해보세요.

옆셀에다가 =VALUE(B1) 이라고 직접 함수를 이용하여 값을 바꿔보는 것도 한번 해보시구요.

엑셀이 제공하는 기능을 조금만 편리하게 사용하면 정말 편하게 만들 수 있답니다.

http://link2me.tistory.com/250

이건 VBA를 이용한 방법입니다. 순식간에 변경되어 버립니다.

매크로를 이용하면 좀 더 편하게 작업할 수 있습니다.

Text2Number.xlsx

엑셀을 다루다보면 셀에 동일한 값을 1000 개 이상 채울 수 있을 경우도 있을 겁니다.

이럴 경우 일일이 마우스를 Drag 하여 채우기를 하는 건 정말 비효울적인 방법입니다.

간단하게 할 수 있는 방법을 소개하니 한번만 해보시면 금방 아실 겁니다.

아래 그림처럼 B3000 이라고 직접 입력하고 엔터키를 침

이제 틀고정을 선택하여 아래처럼 해주세요

이렇게 하면 쉽게 원하는 값을 채울 수가 있습니다.

더 간단한 방법일 수도 있는 팁 한가지는 Ctrl + D 를 눌러도 됩니다.

궁금한 사항은 댓글 달아주시면 답변 드리겠습니다.

유용한 단축키

엑셀을 사용하다보면 단축키를 알아두면 편리한게 있습니다.

대부분은 마우스로 다 해결 가능하지만 알아두면 좀 더 편리한 걸 제 나름대로 정리해보고 있는 중입니다.

더 도움이 될 단축키는 추가 수정하여 올리겠습니다. 

엑셀을 하다보면 원하지 않게 공백이 있어서 정렬을 해서 비교를 해도 중복자료가 있는지 없는지 파악하기 힘들때가 있습니다.

이럴 때 유용하게 사용하는 함수가 공백제거 함수인 trim 함수 입니다.

trim 함수는 문장의 앞뒤의 공백만 제거합니다. 즉 문장 사이의 띄어쓰기는 처리 하지 않습니다.

B열 8행 보시면 앞의 공백이 제거된 거 보이시죠?

문장을 입력하다보면 한칸띄기를 해야 하는데 두칸띄기가 되어 있는 경우가 생기기도 하는데요

이럴 때는 아래처럼 Ctrl + H를 눌러서 스페이스바를 두번 눌러주고, 변경할 내용에는 스페이스바를 한번만 하고 나서

찾기를 한 다음 변경하기를 해주시면 됩니다.

아주 간단한 내용인데 경우에 따라서는 잘 생각나지 않아서

필요할 때 도움이 되실수도 있어서 적어봤습니다.

엑셀을 사용하다보면 영문으로 넣었는데 자동으로 특정글자가 한글로 변경되어 할 때마다 짜증스러울 때가 있죠..

그렇다고 한영변환을 아예 막아버리자니 그렇고~~

이런경우에는 간단하게 설정해서 사용하시면 됩니다.

이런 경우에는 아래 순서대로 따라서 하시면 됩니다.

설명한 그림은 엑셀 2010 이지만 2007 등 다른 것도 찾으시면 메뉴는 거의 비슷한 곳에서 찾으실 수 있을 겁니다.

자동고침 옵션에서 한/영 자동고침을 완전히 해제를 해버리면 잘못하여 한글로 입력하고 있다고 생각하고 입력중일때 영문입력을 하고 있을때 완전히 지우고 새로 입력을 해야 하겠죠..

그래서 자동으로 변환되는 단어가 나오면 그 단어만 추가를 해주는 것이 좋습니다.

자동변환이 되지 않도록 입력값과 결과값을 동일하게 넣어줍니다.

확인을 다 하셨으면 이제 확인을 눌러주세요..

아래는 수식 자동고침을 할 값을 하나 추가를 해봤습니다.

MS워드에서는 --> 를 입력하면 자동으로 화살표 방향키로 바뀝니다.

동일(유사)하게 하려고 값을 넣어본 것입니다.

엑셀을 편하게 사용하기 위해서 좀 더 편리하고 간단한 팁을 추가해봤습니다.

엑셀 작업을 하다보면 셀을 자동으로 채워서 작업을 해야 하는 경우가 부지기수로 많답니다.

특히 번호를 붙여두면 여러모로 작업이 편리한 경우가 많아요

값이 존재하는 왼쪽이나 오른쪽에 자동번호 붙이기 같은 걸 하는 경우

아래 그림처럼 번호(No)를 쓰고 숫자 1과 2를 쓴 다음에 1 과 2가 들어간 셀을 선택하고 나서 우측 하단을 더블클릭한다.

C열의 마지막 값이 들어간 셀까지 자동으로 채우기가 순식간에 된다.

눌러서 셀복사도 해보고, 자동채우기도 해보시기 바랍니다. 어떤 변화가 생기는지 확인해보라는 의미입니다.

C열과 D열의 값이 존재하는 경우에는 번호 자동붙이기는 쉽게 해결이 가능합니다.

중간에 값이 하나가 비어 있을 경우에는 어떻게 되는지 직접 확인해보시기 바랍니다.

그런데.........

왼쪽과 오른쪽에 아무것도 없는 경우에는 마우스 더블클릭만으로 자동채우기가 될까요?

해보시면 움직이지 않는다는 걸 아시게 될 겁니다.

이 경우에는 마우스를 Drag 하여 끌고 죽 내리는 방법으로 해야 합니다.

마우스를 Drag 하여 끌어서 채우는 것은 1,3 으로 놓고 채우기를 하면 아래 그림처럼 채워집니다.

1월, 2월 채우기도 가능하죠.

채우기를 간단하게 하는건 참 쉬워요.

만약 채워야할 데이터가 5000개를 채워야 한다고 하면 어떻게 해야할까요?

계속 마우스를 Drag 한채 죽 아래로 내려야 할까요?

그렇게 한번 해보세요..

제가 여기서 설명드릴 방법은 ^^

틀고정 기능을 활용해서 Drag 하면 빠르고 쉽고 편하다는 겁니다.

이 정도면 데이터가 많은 경우에도 자동채우기 쉽게 할 수 있겠죠?

이번에는 엑셀 다루면서 알아두면 좋을 TIP 에 대해서 알아보겠습니다.

엑셀 작업을 하다보면 자주 사용하는 단축키는 아래 그림처럼 빼놓고 사용하는게 여러모로 편리하고 좋아서 여러분에게 소개합니다.

중요한 것은 본인이 작업을 하다가 자주 사용하는 단축키를 정해서 편하게 사용하면 되는 겁니다.

이밖에 그냥 외워두면 편리한 단축키로는

셀서식을 바로 불러쓰는 CTRL + 1

셀을 아래로 밀거나 오른쪽으로 밀고 싶을 땐

CTRL + PLUS KEY(+)

사실 이 키는 굳이 몰라도 워낙 자주 마우스 우클릭하여 금방 찾아서 사용하는 거라 굳이 몰라도 된다.

작업을 하는 경우에는 CTRL + D, CTRL + R 키를 눌러서 작업하는 것만 알아도 편하다.

(참조 http://link2me.tistory.com/entry/%EC%97%91%EC%85%80-%EB%8F%99%EC%9D%BC%ED%95%9C-%EC%85%80-%EB%82%B4%EC%9A%A9%EC%9D%84-%EC%97%AC%EB%9F%AC%EC%85%80%EC%97%90-%EB%B3%B5%EC%82%AC%ED%95%98%EB%8A%94-%EB%B0%A9%EB%B2%95 )

다른 이름으로 파일을 저장하고 싶다면 작업도중에 그냥 F12키를 눌러주면 된다.

단축키 몰라도 일하는데 크게 지장은 없습니다만 그래도 알아두면 여러모로 편하고 좋아서

제가 아는 방법을 소개해 봤습니다.

엑셀 텍스트 정렬

작업을 하다보면 원하지 않게 아래 그림과 같은 결과가 나옵니다

그럼 어떻게 해야 할까요?

아래 적용된 함수를 보면 A셀과 B셀을 &로 묶어서 표기를 했다.

함수를 concatenate 함수를 사용해도 되지만 간단하게 아래처럼 활용하는 것도 편하다.

숫자부분을 2자리수로 표기되도록 하는 함수인 TEXT함수를 이용하면 된다.

전체 적용을 위해 3번 실행을 한다.

동일에서 값이 다른 셀을 찾는 명령어를 알려주고자 이전 게시물을 작성했습니다.

전해받은 자료에 동일 셀의 값이 5천행 정도 있다 그 다음행 값이 뭔지도 모르면

그 다음으로 이동하기가 참~~ 난감하죠.

이럴 경우에 유용하게 활용하는 것이 바로 F5키를 활용한 이동 옵션입니다.

블럭이 설정되지 않은 상태에서 누르면 ....

원하는 결과를 얻을 수가 없습니다. 

먼저 동일열에서 셀 전체를 선택하는 명령어입니다. (셀 지정하는 것이 귀찮으면 A 전체를 선택하고 해도 된다.)

셀의 마지막까지 블럭이 설정되었다. 이제 F5키를 누른다

이동옵션 창이 뜨면 옵션을 눌러줍니다.

여기서 3번 "동일열에서 값이 다른 셀"을 선택하고 확인을 누릅니다.

이전 게시물에서 연습하면서 입력했던 1번 값의 마지막 셀의 위치를 찾아줍니다.

일을 하다보면 이런 걸 활용하면 자료 찾는게 편하고 여러가지 생각을 함께 정리하면 매우 유용하게 활용할 수가 있습니다.

제가 다루는 내용은 소소한 팁을 가지고 유용하게 엑셀작업을 할 수 있는 걸 정리해 볼 생각입니다

엑셀을 다루면서 쉬우면서도 잘 모르는 기능을 알아보도록 하겟습니다.

셀을 아래셀에 동일하게 붙이고 싶은 경우와 오른쪽 셀에 붙이고 싶은 경우 단축키입니다.

그러면 만약에 동일하게 여러셀을 붙이고 싶다면 어떻게 해야 할까요?

마찬가지로 오른쪽으로 하고 싶다면

간단하게 엑셀 단축키를 이용하여 편리하게 복사하는 방법을 알아봤습니다.

그런데 동일하게 여러셀을 복사하는게 300개 정도 된다고 하면 어떻게 해야 할까요?

B302 셀까지 모두 한꺼번에 동일한 셀값으로 채워졌습니다.

사소한 거 같지만 알아두면 편리한 걸 포스팅 해보겠습니다.

제가 업무를 하다가 배운 것 위주로 포스팅 하겠습니다.

도움이 되신다면 댓글 달아주세요.

오늘 개설해서 공감 어떻게 다는 건지 아직 못배운 상태입니다 ㅋㅋ

인터넷을 뒤져도 쉽게 찾아지지가 않아서 고생스러워서 정리하여 올립니다.

엑셀 메뉴를 영문으로 변경하고 싶은 경우에 해당된다.  

가령 사무실에서는 영문엑셀를 사용하고 집에서는 한글엑셀을 사용하는 경우라고 볼 수 있습니다.

먼저, 언어팩을 받아야 합니다. 

어디서??

http://www.niharsworld.com/2010/09/29/download-microsoft-office-2010-language-packs-free-official-direct-download-links/

 본인한테 맞는 팩을 다운로드 받아서 설치합니다

설치가 다 되었으면 이제....

설정을 변경해야 합니다.

엑셀에서 직접 변경해도 되고 메뉴에서 선택하여 변경해도 됩니다.

언어팩이 설치되어 있지 않으면 영어(English)는 화면에 표시되지 않아요. 

엑셀 및 인터넷 오피스 도움말 열심히 봐도 설명은 되어 있지만 이거 설치된다는 도움말에 대한 내용은 없어서 아주 애를 먹었습니다.

이제 디폴트 값을 변경해주면 됩니다.

이제 엑셀을 실행하면 영문으로 메뉴가 변경되어 있는 것을 확인할 수 있습니다.

혹시 오피스 2013 버전을 사용하시는 분은

http://support.microsoft.com/kb/2265002/ko 에 가시면 설명이 잘 되어 있으니 따라해보세요
 

토탈커맨더(Total Commander)가 뭔지에 대한 내용은 유용한 유틸정보란에 포스팅을 해뒀습니다.

이제 토털커맨드를 이용해서 엑셀창 2개 이상 띄우는 방법에 대해서 설정하는 방법을 알아봅시다.

토털커맨터 상에서 엑셀창을 띄우면

운영체제가 윈도우XP 이건 윈도우7 이건 상관없이 모두 잘 동작합니다.

Specify file type 입력란에 *.xls;*.xlsx;*.xlsm;*.csv 등으로 엑셀로 열고자 하는 파일 확장자를 세미콜론(;) 으로 구분하여 입력

등록할 때 EXCEL.EXE" "%1" 까지만 자동입력 되므로

중간의 /e 는 사용자가 직접 입력해주어 합니다.

이제는 엑셀 창을 두개 이상 띄우면 서로간에 분리되어 움직이므로 모니터 1대와 노트북을 사용하는 환경 또는 저처럼 모니터 2대를 띄워놓고 작업하시는 분들 모두 편리하실 겁니다.

모니터 한대만 가지고 하는 경우에도 창이 분리되어 있으면 자료를 찾아보기가 좀 더 수월합니다.

사무실에 업무효율을 높이고 싶으신 분은 당장 해보시기 바랍니다.

사무실에서 엑셀을 작업하다보면 창을 두개 이상 띄우고 작업을 해야 편리한 경우가 많습니다.

요즈음에는 모니터가 와이드화면인데가 보통 사무실에서

노트북 하나에 모니터 하나를 사용하는 환경도 많습니다.

이유는 작업이 정말 편하기 때문이죠..

그런데 기존에 작업해논 엑셀을 다시 불러서 보고 싶은데 엑셀 창이 분리되면 좋은데 그렇지 못한 경우에는 어떻게 해야 할까요?

아래 그림에서 /e 로 된 것을 /en "%1" 으로 수정하고 DDE 체크된 것을 uncheck 하면 됩니다.

저는 더 편리하게 사용하는 방법은 토탈커맨더 기반에서 엑셀 창 2개 이상 띄우기 입니다.

바로 다음 게시물에서 설명 드리도록 하겠습니다.