728x90

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

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


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



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



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





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


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


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



블로그 이미지

Link2Me

,
728x90



위 그림처럼 테이블(자료)에서 원하는 걸 찾아서 값을 변경하고 싶다면 어떻게 해야할까요?

방법은 여러가지가 있을 수 있습니다.

오늘은 조건부 서식을 이용해서 한번 해보겠습니다.










블럭설정은 첫번째 지정하고 싶은 셀에서 Shift 누른상태에서 마우스로 마지막 셀을 선택해서 누르면 됩니다.

그 상태에서 Ctrl + D를 눌러주면 값이 변경되는 걸 확인하실 수 있습니다.


조건부 서식 텍스트포함은 어떤 특정한 글자가 들어간 것만 찾아서 색깔별 정렬을 해서 원하는 결과를 찾을 때도 매우 유용합니다.


다른 방법으로 해본 다면

값 대체하는 substitute 함수를 이용하는 방법입니다.

이렇게 한 다음에 값붙여넣기를 하고 나서 원래 수량 열은 지우고 값대체 열에 '수량'으로 변경만 해도 됩니다.

방법은 다양하게 해볼 수 있겠지요..


자주 사용하는 IF함수 사용법이랑 다른 함수들을 적절히 조합하면 됩니다.


직접 해보실 분을 위해 첨부파일 첨부합니다.


Excel_조건부서식.xlsx




블로그 이미지

Link2Me

,
728x90

엑셀 피벗을 뭘로 설명하면 좋을까 곰곰히 생각해보다가 로또당첨번호를 이용해서 분석해보면 어떨까 하는 생각이 들어서 로또당첨번호를 조회해봤더니 친절하게 정리해서 올려주신 분이 있어서 이 자료를 가지고 정리를 해볼까 합니다.

 

VLOOKUP 을 알면 여러가지 작업을 하는데 매우 편리합니다.

피벗작업하는 방법을 알면 분석하는데 여러가지로 편리합니다.



 

번호는 지난주인 2013년 3월 30일날 추첨한 번호까지 확인해서 추가했답니다.

번호는 총 6 개인데 번호가 뒤죽박죽 섞여 있군요.

피벗을 돌려서 각 번호별로 몇개나 당첨번호가 나왔는지 확인해보는 작업을 해보겠습니다.

눈으로 일일이 확인한다는 건 그야말로 힘든 일이겠죠?

 

가장 먼저 할일은 표 전체를 블럭설정한다.


새워크 시트에 피봇작업을 하도록 지정하면 아래 그림이 나온다.

여기서 구하고자 하는 걸 구한다.

로또번호는 무작위로 6개 이므로, 여기서는 각 열에 해당하는 걸 각각 구해야 한다.

즉, 번호1 열에 나열된 숫자가 몇개씩인지를 일단 구한다.


 여기서 왜 2등번호 개수를 지정했느냐고 따지지 마시길 ^^

다른 걸 가져다가 놓아도 결과를 동일합니다 

 

값을 구했으면 복사를 해서 값붙여넣기로 다른 시트에 추가를 한다.

 


다시 다른열을 작업하고 피벗결과값을 복사하여 다른 시트에 값붙여넣기를 한다.


이런식으로 6개의 열 모두를 피벗으로 결과를 얻어낸다.



이제는 결과로 얻어진 표를 가지고 블럭을 설정하여 다시 피벗 작업을 한다.




지금까지 가장 많은 당첨번호 숫자는 아래와 같군요

전체적으로 보면 가장 적게 나온 숫자는 58번 나왔네요.

가장 많이 나온 숫자는 40번이 90회 나왔구요..


 

어때요?

로또당첨번호로 피벗을 돌려보니 좀 와닿나요?

 

회사에서 일하는 걸로 하면 금방 자료 올리기도 편하지만 보안이라 불가~~

그래서 어떤 샘플을 찾아서 설명을 해야 하나 하는 고민이 되었답니다.

작업했던 샘플 파일 올리니 직접 해보실 분은 해보세요


로또당첨번호 이용한 피벗작업.xlsx


블로그 이미지

Link2Me

,
728x90

엑셀 자료를 찾다보면 두개의 셀이 동시에 일치하는 경우에 해당되는 값을 찾아서 가져오고자 할 경우가 있다.

이럴 경우 두개의 셀 옆에 하나의 셀처럼 &를 써서 만들어서 사용해도 되는데 좀 귀찮다.

손대지 않고 할 수 있는 방법이 있는지 찾아봤는데, 방법이 있어서 잘 되는지 확인하고 포스팅을 합니다.

엑셀 가지고 모든 걸 할 수 있는 건 아니지만 업무를 하면서 유용하게 활용할 수 있는 것 만으로도 정말 대단하다는 생각이 든다.

 

MATCH(lookup_value,lookup_array,match_type)

* lookup_value 가 하나의 셀값일 수도 있지만 여기서는 두개의 셀값을 마치 하나처럼 인식시키는 A2&B2 로 &로 묶어줄 수 있다.

* lookup_array 는 하나의 행만 표시하는 배열인게 대부분이지만

                         G2:G22&H2:H22 로 두개의 배열을 & 로 묶어서 처리가 가능하다.

                         값이 변하지 않게 구간설정을 하기 위해서는 모두 절대값 처리를 하면 된다. F4키룰 눌러서 처리하면 된다.

* match_type) : 0 --> 정확하게 일치하는 값을 찾아라

 

아래 그림에서 가져오고자 하는 값을 화살표로 표기했다.

품목과 코드 두개가 일치할 때 원하는 값을 뽑아서 가져오면 되는 거다.

 

 

아래처럼 MATCH(1,(G4:G22=A3)*(H4:H22=B3),0) 로 해서 처리하면 원하는 결과를 얻을 수 있다.

여기서 주의할 점은 그냥 엔터키를 치면 원하는 값이 나오지 않는다.



반드시 CTRL + SHIFT + Enter 키를 눌러야 한다




아래처럼 MATCH 함수값을 지정해도 결과는 동일하게 나온다.

마찬가지로 반드시 CTRL + SHIFT + Enter키를 입력해야 한다. 



두개의 셀이 일치하는 조건에 맞는 값을 가져오고자 할 경우에

INDEX, MATCH 함수를 사용하면 원하는 결과를 얻을 수 있다.

 

필요하신 분은 첨부파일 받아서 직접 눈으로 확인해보세요.. ^^ 


Index_match_array_sample.xlsx


블로그 이미지

Link2Me

,
728x90

현재 만들어진 자료를 손대지 않고서 작업을 하고자 할 경우에 VLOOKUP 함수로 다 해결할 수 없다는 아쉬움이 있습니다.

VLOOKUP 함수를 사용할 때 주의할 점은 VLOOKUP보다 왼쪽에 있는 값을 가져오려고 하는 건 불가능하다는 거다

 


왜냐구?! 비교할 값을 항상 배열의 첫구간으로 설정해야 한다는 거다


그래서 VLOOKUP 함수를 사용할 때는 비교하고자 하는 값이 왼쪽에 있으면 해당열 전체를 복사하여 오른쪽으로 옮기거나

복사를 하여야 한다.

 

그런데 이 표를 그대로 둔 상태에서도 VLOOKUP처럼 가져올 수 있는 함수가 있다.

그건 INDEX, MATCH 함수를 조합하여 사용하면 됩니다.

VLOOKUP 함수와 INDEX, MATCH 함수를 알면 업무를 하면서 원하시는 걸 쉽게 처리할 수 있습니다.

여기에 한가지 더 피봇까지 잘 다루면 참 편하게 원하는 결과를 도출할 수 있답니다.


그럼 다음번에는 INDEX, MATCH 함수를 다뤄보겠습니다.

블로그 이미지

Link2Me

,
728x90

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

 

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

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

 

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

어디서??

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 에 가시면 설명이 잘 되어 있으니 따라해보세요
 

 

블로그 이미지

Link2Me

,