[Excel] 유용한 엑셀 기능 정리 - 6 데이터를 찾기 위한 index와 vlookup hloopup

Working Tips/Excel 2009. 6. 4. 11:30
엑셀에서 데이터를 찾을 필요가 있다.

특히 컨트롤(슬라이드 바나, 체크 박스등)을 사용할 때에는
굉장히 유용하다.

각각의 의미는 다음과 같다.
index(Data, i, j): 선택한 data의 범위에 있는 i번째 행의 j 번째 열을 구한다.
VLOOKUP(찾으려는 값, 전체 영역,리턴하는열 번호): 전체 영역에서 가장 좌측에 있는 값이 찾으려는 값이면 가장 우측값을 리턴한다.

 1 2 3 
 4 5  6 
 7 8  9 
 10 11  12 
와 같은 데이터가 있을 때
1이 A1, 12가 C4에 있는 경우

=INDEX(A1:C4,1,1)
은 1을
=INDEX(A1:C4,4,3)
은 12를 표시해준다

=VLOOKUP(1,A1:C4,3)
은 3을
=VLOOKUP(4,A1:C4,3)
은 6을
각각 표시해준다.

'Working Tips > Excel' 카테고리의 다른 글

[Excel] 많은 데이터를 한눈에 쉽게 볼 수 있는 Heat Map을 액셀로 처리하기  (0) 2010.10.07
[Excel] 유용한 엑셀 기능 정리 - 5 엑셀에서 숫자 올림을 담당하는 CEILING  (0) 2009.06.03
[Excel] 유용한 엑셀 기능 정리 - 4 엑셀에서 글자 자르기  (0) 2009.06.02
[Excel] 유용한 엑셀 기능 정리 - 3 데이터의 조건에 따라 다르게 표시하는 if와 countif (엑셀 if)  (0) 2009.06.01
[Excel] 유용한 엑셀 기능 정리 - 2 현재 날짜 및 Random(무작위 수 만들기) 관련 함수  (0) 2009.05.31
Posted by tuning-java
,

[Excel] 유용한 엑셀 기능 정리 - 5 엑셀에서 숫자 올림을 담당하는 CEILING

Working Tips/Excel 2009. 6. 3. 13:05
엑셀에서 숫자를 다루면서 필요한 것이 CEILING이라는 것이다.

다음의 내용을 보자. 4.5라는 숫자에 CEILING 계산은 다음과 같이 한다.
CEILING(4.5,1) 
CEILING(4.5,2) 
CEILING(4.5,4) 
CEILING(4.5,8) 
CEILING(4.5,10)
 
결과는 다음과 같이 나온다.

1

5

2

6

4

8

8

8

10

10


CEILING 은 올림함수의 일종으로, 뒤에 있는 숫자의 배수를 표시해준다.
4.5를 1의 배수로 표현하면, 4 다음의 숫자가 5이기 때문에 5가 되며
4.5를 2의 배수로 표현하면, 4 다음의 숫자가 6이기 때문에 6이 된다.

CEILING의 반대는 FLOOR 로 올리지 않고, 내림을 한다.

'Working Tips > Excel' 카테고리의 다른 글

[Excel] 많은 데이터를 한눈에 쉽게 볼 수 있는 Heat Map을 액셀로 처리하기  (0) 2010.10.07
[Excel] 유용한 엑셀 기능 정리 - 6 데이터를 찾기 위한 index와 vlookup hloopup  (0) 2009.06.04
[Excel] 유용한 엑셀 기능 정리 - 4 엑셀에서 글자 자르기  (0) 2009.06.02
[Excel] 유용한 엑셀 기능 정리 - 3 데이터의 조건에 따라 다르게 표시하는 if와 countif (엑셀 if)  (0) 2009.06.01
[Excel] 유용한 엑셀 기능 정리 - 2 현재 날짜 및 Random(무작위 수 만들기) 관련 함수  (0) 2009.05.31
Posted by tuning-java
,

[Excel] 유용한 엑셀 기능 정리 - 4 엑셀에서 글자 자르기

Working Tips/Excel 2009. 6. 2. 12:57
엑셀에도 기본적으로 글자를 자르는 (substring) 기능이 존재한다.

글자를 자르는 함수는 LEFT, MIDDLE, RIGHT 가 있다.
LEFT(A1,2) ==>A1의 값이 ABCDE이면 AB가 나옴. 
RIGHT(A1,3) ==> A1의 값이 ABCDE이면 CDE가 나옴
MID(A1,2,2) ==> A1의 값이 ABCDE이면 BC가 나옴.


'Working Tips > Excel' 카테고리의 다른 글

[Excel] 유용한 엑셀 기능 정리 - 6 데이터를 찾기 위한 index와 vlookup hloopup  (0) 2009.06.04
[Excel] 유용한 엑셀 기능 정리 - 5 엑셀에서 숫자 올림을 담당하는 CEILING  (0) 2009.06.03
[Excel] 유용한 엑셀 기능 정리 - 3 데이터의 조건에 따라 다르게 표시하는 if와 countif (엑셀 if)  (0) 2009.06.01
[Excel] 유용한 엑셀 기능 정리 - 2 현재 날짜 및 Random(무작위 수 만들기) 관련 함수  (0) 2009.05.31
[Excel] 유용한 엑셀 기능 정리 - 1 유용한 엑셀 단축키와 값의 지정 (절대 참조, 데이터 선택)  (0) 2009.05.30
Posted by tuning-java
,

[Excel] 유용한 엑셀 기능 정리 - 3 데이터의 조건에 따라 다르게 표시하는 if와 countif (엑셀 if)

Working Tips/Excel 2009. 6. 1. 08:54
엑셀의 if는 베이직이라는 언어의 if 라고 생각하면 가장 쉽다.

A1 필드에 어떤 숫자가 있을 때 다음과 같이 if 를 사용하면 된다.
=if(A1 >= 30, "30 이상", "30 미만")

다시 말하면
=if(조건식, 참일때의 행동이나 값, 거짓일때의 행동이나 값)
이다.

countif는 범위에 맞는 데이터의 개수를 찾을 때 사용한다.
=COUNTIF(C:C,">5")

이렇게 하면 C 전체 셀에서 5보다 큰 데이터의 개수를 찾는다.

'Working Tips > Excel' 카테고리의 다른 글

[Excel] 유용한 엑셀 기능 정리 - 5 엑셀에서 숫자 올림을 담당하는 CEILING  (0) 2009.06.03
[Excel] 유용한 엑셀 기능 정리 - 4 엑셀에서 글자 자르기  (0) 2009.06.02
[Excel] 유용한 엑셀 기능 정리 - 2 현재 날짜 및 Random(무작위 수 만들기) 관련 함수  (0) 2009.05.31
[Excel] 유용한 엑셀 기능 정리 - 1 유용한 엑셀 단축키와 값의 지정 (절대 참조, 데이터 선택)  (0) 2009.05.30
[Excel] 엑셀의 개수를 구하는 함수들  (1) 2008.11.20
Posted by tuning-java
,

[Excel] 유용한 엑셀 기능 정리 - 2 현재 날짜 및 Random(무작위 수 만들기) 관련 함수

Working Tips/Excel 2009. 5. 31. 08:46
날짜를 만드는 함수는 여러가지가 있겠지만, 오늘 날짜와 날짜 수를 세는 함수는 다음과 같다.
=NOW() : 현재 날짜
=DAYS360(시작날짜,끝날짜) : 시작날짜에서 끝 날짜까지의 날짜 수
=DAYS360(B2,NOW()) ==> B2 부터 현재까지의 날짜 수

무작위수를 나타내는 함수는
=RANDOM() 
이다.
이렇게 하면, 빈 칸을 선택하고 "del"키를 누르면 계속 0~1까지의 무작위 수를 제공한다.

만약 min~max 까지의 난수를 만들려면 다음과 같이 하면 된다.
=RANDOM()*(max-min)+min

'Working Tips > Excel' 카테고리의 다른 글

[Excel] 유용한 엑셀 기능 정리 - 4 엑셀에서 글자 자르기  (0) 2009.06.02
[Excel] 유용한 엑셀 기능 정리 - 3 데이터의 조건에 따라 다르게 표시하는 if와 countif (엑셀 if)  (0) 2009.06.01
[Excel] 유용한 엑셀 기능 정리 - 1 유용한 엑셀 단축키와 값의 지정 (절대 참조, 데이터 선택)  (0) 2009.05.30
[Excel] 엑셀의 개수를 구하는 함수들  (1) 2008.11.20
[Excel] 엑셀에서 각 항목별 개수 구하기  (5) 2008.03.20
Posted by tuning-java
,

[Excel] 유용한 엑셀 기능 정리 - 1 유용한 엑셀 단축키와 값의 지정 (절대 참조, 데이터 선택)

Working Tips/Excel 2009. 5. 30. 08:40
얼마전 통계 교육을 받았는데, 유용한 엑셀 기능들이 있어서
정리해 두려고 한다.

나중에 까먹을 수도 있고,
이 기능들을 찾는 분들도 있을 수 있고...

IT하면서, 그리고, 일하면서 Excel을 잘 다루면 정말 시간도 단축하고,
편하게 일할 수 있다. 그런게 Excel이다.

가장 먼저 쉬운 단축키부터
F4키 : 절대 참조로 변경
Ctrl+Shift+화살표 : 데이터 범위 선택
Ctrl+Shift+8 : 근처 범위 선택

절대 참조는 셀 참조에 $을 붙이는 것이다.
예를 들어 "=A1"이 있다면 다음과 같이 된다.
$A$1 : A1으로 고정됨 $A1 : A만 고정됨. 아래 드래그를 하면 행 값이 변경됨 A$1: 1만 고정됨. 우측 드래그하면 열 값이 변경됨.


데이터 범위 선택은 여러 데이터가 있을 때 화살표를 누른 방향으로의 그 데이터 끝까지 선택된다.
(이거 모르면 몇천개 끝찾기 위해서 스크롤바를 내려야 한다.)

엑셀의 전체 단축키는 아래 링크를 따라가면 된다.
http://office.microsoft.com/client/helppreview.aspx?AssetID=HP100738481042&ns=EXCEL&lcid=1042

마지막으로
자바에서 String을 더할 때에는 +를 쓰지만,
엑셀에서는 &를 쓴다는걸 기억하자.
예를 들어 A1값이 2이고, 그 값을 참조하고 싶다면, 다음과 같이 사용하면 된다.

="이번일에 대해서 "&A1&"MB 은 머리를 숙이고 사죄를 해야만 한다."

다음부터는 정말 유용한 함수들을 정리할 예정이다.

'Working Tips > Excel' 카테고리의 다른 글

[Excel] 유용한 엑셀 기능 정리 - 4 엑셀에서 글자 자르기  (0) 2009.06.02
[Excel] 유용한 엑셀 기능 정리 - 3 데이터의 조건에 따라 다르게 표시하는 if와 countif (엑셀 if)  (0) 2009.06.01
[Excel] 유용한 엑셀 기능 정리 - 2 현재 날짜 및 Random(무작위 수 만들기) 관련 함수  (0) 2009.05.31
[Excel] 엑셀의 개수를 구하는 함수들  (1) 2008.11.20
[Excel] 엑셀에서 각 항목별 개수 구하기  (5) 2008.03.20
Posted by tuning-java
,

[Excel] 엑셀의 개수를 구하는 함수들

Working Tips/Excel 2008. 11. 20. 09:00
매번 엑셀(Excel)로 작업하다가 개수를 구하는 부분에서 애를 먹는다...

이번에 한번 정리해본다.
추가적인 사용방법도 있지만, 여기선 간단한 방법만...

COUNT(범위,값,...) : 숫자의 개수를 구한다.
=COUNT(A1:A10) 와 같이 지정하면, A1~A10 에 있는 숫자가 있는 셀의 개수를 리턴한다.

COUNTA(범위,값,...) : 공백이 아닌 셀의 개수를 구한다.
=COUNT(A1:A10) 와 같이 지정하면, A1~A10 에 있는 공백이 아닌 셀의 개수를 리턴한다.

COUNTBLANK(범위) : 공백인 셀의 개수를 구한다.
=COUNT(A1:A10) 와 같이 지정하면, A1~A10 에 있는 공백 셀의 개수를 리턴한다.

COUNTIF(범위,조건) : 범위에 있는 조건에 맞는 셀의 개수를 구한다.
=COUNTIF(A1:A10,"Java") 와 같이 지정하면, A1~A10에 Java가 들어있는 셀의 개수를 리턴한다.

나머지는 필요할때 나중에 정리...

'Working Tips > Excel' 카테고리의 다른 글

[Excel] 유용한 엑셀 기능 정리 - 4 엑셀에서 글자 자르기  (0) 2009.06.02
[Excel] 유용한 엑셀 기능 정리 - 3 데이터의 조건에 따라 다르게 표시하는 if와 countif (엑셀 if)  (0) 2009.06.01
[Excel] 유용한 엑셀 기능 정리 - 2 현재 날짜 및 Random(무작위 수 만들기) 관련 함수  (0) 2009.05.31
[Excel] 유용한 엑셀 기능 정리 - 1 유용한 엑셀 단축키와 값의 지정 (절대 참조, 데이터 선택)  (0) 2009.05.30
[Excel] 엑셀에서 각 항목별 개수 구하기  (5) 2008.03.20
Posted by tuning-java
,

[Excel] 엑셀에서 각 항목별 개수 구하기

Working Tips/Excel 2008. 3. 20. 16:06

예를 들어서 다음과 같은 데이터가 있을 때 (여기서 A 는 열의 이름)

A

aaa
aaa
bbb
ccc
ddd
ddd
eee
fff

각 항목, 그러니까 aaa, bbb, ccc, ddd, eee, fff 가 몇개인지 확인하는 방법은 다음과 같다.

이 경우 데이터가 몇개 안되니까 눈으로 보면 되지만, 만약 Access log와 같이 URL 데이터가 많이 있을 때, 어떤 URL이 많이 호출되는지를 확인할 때 그걸 일일이 더하는건 바보같은 짓이다.

1. 가장먼저 해당 열을 다른 시트에 복사한다.

2. 그 다음 다른 시트의 A 열을 클릭한 후(A 열을 모두 선택하도록...)

3. 메뉴에서 "데이터 --> 중복된 항목 제거"를 선택한다. 그럼 다음과 같이 된다.

A

aaa
bbb
ccc
ddd
eee
fff

4. 그럼 우리가 원하는 각 항목들만 남아있게 된다. (중복된걸 제거 했으니까)

5. 마지막으로
추가된 시트의 B 행에 다음과 같이 수식을 추가한다.
=COUNTIF(원본탭!A:A,A2)
여기서 "원본탭" 은 원래 중복된 항목을 제거하지 않은 탭의 이름.
A:A는 중복된 항목을 제거하지 않은 열
A2는 중복된 항목이 제거된 셀의 위치이다. 여기서는 aaa가 있는 셀의 이름이다.

6. 이 식을 아래 줄에도 복사하면 모든 작업이 끝난다.

^^;

'Working Tips > Excel' 카테고리의 다른 글

[Excel] 유용한 엑셀 기능 정리 - 4 엑셀에서 글자 자르기  (0) 2009.06.02
[Excel] 유용한 엑셀 기능 정리 - 3 데이터의 조건에 따라 다르게 표시하는 if와 countif (엑셀 if)  (0) 2009.06.01
[Excel] 유용한 엑셀 기능 정리 - 2 현재 날짜 및 Random(무작위 수 만들기) 관련 함수  (0) 2009.05.31
[Excel] 유용한 엑셀 기능 정리 - 1 유용한 엑셀 단축키와 값의 지정 (절대 참조, 데이터 선택)  (0) 2009.05.30
[Excel] 엑셀의 개수를 구하는 함수들  (1) 2008.11.20
Posted by tuning-java
,

티스토리툴바