엑셀의 리스트는 중복 될 수 있습니다. 

중복된 값을 제외하고 지우고 고유한 값만 남기는 방법을 알아보겠습니다.

1. 중복된 항목 제거

위치는 [상단매뉴] - [데이터] - [데이터도구] - [중복된 항목 제거]에 있습니다.

① 원하는 데이터를 선택합니다.

선택 후 "중복된 항목 제거 아이콘"을 클릭하여 실행시키면 됩니다.

② 어떤 열을 기준으로 중복된 값을 지울지 선택합니다.

"내 데이터에 머리글 표시"를 체크하면 상단의 한줄을 데이터의 이름으로 설정됩니다.

체크 해제하면 열 번호 A, B, C ... 에서 선택하게 됩니다.

순서대로 설명했지만 실행하는 것을 직접 보시면 이해될 것 같네요.

움짤로 보여드리겠습니다.(엑셀의 버전이 다르면 버튼 위치등이 약간 다를 수 있습니다.)

예시) 중복된 데이터 지우기.gif

엑셀은 스프레드 시트 프로그램입니다.

다중의 표형식으로 이루어지는데 그러다 보니 통계 데이터나 분석표를 입력하는데 특화되었습니다.

그래서 데이터베이스 프로그램의 인터페이스를 따르는 경우도 있는 것이죠.

데이터를 입력하고 관리하는 레코드 관리 기능을 알아봅시다.

① 레코드 관리 기능의 위치

레코드 관리는 일반 인터페이스에서는 찾을 수 없습니다.

상단의 "검색" 기능을 활용하면 찾을 수 있습니다.

아니면 [상단 매뉴] - [파일] - [옵션] - [빠른 실행 도구 모음]에 등록해야 합니다.

편리한 기능인데 바로 없으니까 조금 섭섭하네요

② 레코드 관리 기능 사용

레코드기능을 사용할 때는 일부 작성된 표 위에 커서를 위치해야 합니다.

그래야 엑셀이 표를 인식해서 레코드를 활성화 시킵니다.

표의 내용과 속성을 인식해서 리스트로 만듭니다.

③ 레코드 관리의 내용 - 새로 만들기

레코드 관리의 매뉴 중 "새로 만들기"를 하면 빈칸 양식으로 변경됩니다.

마지막의 나이 항목은 수식이 들어가 있습니다.

나머지를 입력하면 표의 맨 아래에 여기서 입력한 후 "새로 만들기"를 또 누르면 표의 맨 아래 들어갑니다.

④ 레코드 관리 - 조건

"이전 찾기"와 "다음 찾기"로 항목의 앞뒤로 이동 할 수 있습니다.

조건을 눌러서 활성화 하면 조건에 맞는 항목만 움직입니다.

즉 "조건"의 성별을 "남"으로 입력하면, 이전과 다음으로 조건에 맞는것만 찾아 이동합니다.

다시 "조건"을 누르고 입력한 것을 지운 후 "레코드 관리"를 누르면 해제됩니다.

데이터가 많으면 항상 그중에서 뭐가 가장 크냐 작으냐를 보게되죠.

극단적인 상황을 볼 때 아니면 개중에 뭐가 가장 심각한지, 양호한지 보려는 것이죠.

오늘은 데이터의 크기를 보는 함수를 알아보겠습니다.

1. MAX / MIN 함수 : 가장 큰 수와 작은 수를 추출합니다.

엑셀도 용도가 많으니 배우는 성격에 따라서 안배울 수도 있지만요.

특히 숫자로 된 데이터를 정리하는 위주로 엑셀을 배우셨다면 100% 알고 있을 것입니다.

그만큼 많이 쓰는 함수라는 것입니다.

MAX(number1, [number2], ...) : 숫자들 중 가장 큰 최대값을 구합니다.
셀의 영역을 입력하거나 숫자 혹은 배열을 입력할 수 있습니다. 종류로 최대 255개까지 지정이 가능합니다.

MIN(number1, [number2], ...) : 숫자들 중 가장 작은 최소값을 구합니다.
셀의 영역을 입력하거나 숫자 혹은 배열을 입력할 수 있습니다. 종류로 최대 255개까지 지정이 가능합니다.

2. LARGE / SMALL 함수 : 몇 번째로 크거나 작은 숫자을 추출합니다.

이 함수들은 짝으로 이름만 다르고 크냐작냐 차이지 기능이나 용법을 같습니다.

두개를 한번에 설명 드리겠습니다.

LARGE(array,k) : 데이터들이 있는 "array"에서 K 번째로 큰 숫자를 뽑습니다.

• Array : k번째로 큰 값을 확인할 데이터 배열 또는 범위입니다.
• k : Array에서 구하고 싶은 상대 순위입니다.

SMALL(array,k) : 데이터들이 있는 "array"에서 K 번째로 작은 숫자를 뽑습니다.

• Array : K번째로 작은 값을 확인할 숫자 데이터 배열 또는 범위입니다.
• k : Array에서 구하고 싶은 상대 순위입니다.

가장 높은/낮은 값을 뽑아주는 MAX/MIN는 많이 사용하지만,

두번째 부터 뽑을 수 있는 Large / Small의 사용 빈도가 적은 걸 보면 역시 일등아니면 꼴찌해야 기억하는 세상이네요.

3. LARGE, SMALL을 이용해서 순위 뽑기

크거나 작은 값들이 그냥 떠있으면 내용을 파악하지 못 할 수도 있습니다.

구체적으로 데이터의 이름이 필요하죠.

이럴 때 쓰는 것이 INDEX와 MATCH 함수와 조합하면 멋있는 순위표를 만들 수 있습니다.

( 이 링크는 INDEX와 MATCH에 대한 설명입니다. )

=INDEX(B7:B23,MATCH(G5,$C$7:$C$23,0))
① LARGE로 순위별 숫자를 뽑습니다.(G5)
② MATCH로 그 숫자가 몇번째 숫자 인지 찾습니다.
③ INDEX로 값을 출력합니다.

생산량 옆에 최다 / 최소 생산 시도만 붙여 넣어 보고서 느낌이 나네요.

보시면 알겠지만 양식화 해 놓으면 어떤 숫자들도 다 적용이 가능합니다.

생산, 비용, 인구에 관한 통계는 최다/최소만 몇개 뽑아도 분석이 빨라지죠.

사소한 것이지만 단순 DATA하나 주는 것보다 이렇게 뭐라도 붙여놓으면 데이터의 의미가 강해집니다.

(이런 사소한 배려 직장생활에서 중요합니다.)

표 형식으로 데이터를 정리해 두기는 하지만 특정하나의 DATA를 찾을 경우도 있습니다.

이때 사용할 수 있는 Vlookup함수과 Hlookup 함수에 대해서 소개하려고 합니다.

기능에 대해서 설명을 해보려면 좀 어렵게 적혀 있습니다.

VLOOKUP : 이 함수는 테이블의 첫 번째 열에 지정된 조회 값의 값을 기반으로 테이블의 지정된 열에서 값을 검색하는 데 사용됩니다.
HLOOKUP : 이 함수는 테이블의 첫 번째 행에 지정된 조회 값의 값을 기반으로 테이블의 지정된 행에서 값을 검색하는 데 사용됩니다.

VLOOKUP과 HLOOKUP은 행과 열만 바꼈을 뿐 같은 함수입니다.

기본적으로 VLOOKUP으로 구조를 먼저 설명하겠습니다.

VLOOKUP(lookup_value, table_arry, col_index_num, [range_lookup])

• lookup_value : 찾는 값입니다. 문자와 숫자도 모두 가능합니다.
• table_arry : 조회값이 있는 범위입니다. 찾는 값(lookup_value)이 맨 왼쪽에 있어야 동작 합니다.
• col_index_num : 출력할 값의 열 번호입니다.
• [range_lookup] : 찾는 값이 정확하게 일치하기를 원하면 False를 유사할 경우는 True를 입력합니다.
                           기본값은 True이나 문자를 기반으로 검색할 경우에는 False를 꼭 입력해야 합니다.

기능에 대해서 알아보면 아래과 같습니다.

표에서 가장 왼쪽에서 값을 찾은 다음에 오른쪽에 몇개 떨어진 값을 찾습니다.

위의 지역명과 숫자를 변경하면 서울의 2012년이나 대구의 2007년도 쉽게 찾을 수 있겠죠.

HLOOKUP(lookup_value, table_arry, col_index_num, [range_lookup])

HLOOKUP은 VLOOKUP에서 열이 행으로 변경되고 나머지는 동일 합니다.

• lookup_value : 찾는 값입니다. 문자와 숫자도 모두 가능합니다.
• table_arry : 조회값이 있는 범위입니다. 찾는 값(lookup_value)이 맨 위쪽에 있어야 동작 합니다.
• col_index_num : 출력할 값의 행 번호입니다.
• [range_lookup] : 찾는 값이 정확하게 일치하기를 원하면 False를 유사할 경우는 True를 입력합니다.
                           기본값은 True이나 문자를 기반으로 검색할 경우에는 False를 꼭 입력해야 합니다.

주의할 점이 있는데 리스트에 행이 병합되어 있으면 병행된 범위의 왼쪽 상단만 값이 있습니다.

그리고 범위 내에 똑같은 값이 여러개 있는 경우 가장 위나 왼쪽에 값만 한번 찾습니다.

그래서 사번이나 코드등 고유번호를 사용해야 하는 점이 있습니다.

조건이 좀 까다로워 보여도 익숙해지면 사용하기 편리합니다.

엑셀의 기본함수 중에서 많이 쓰이는 함수 축에 듭니다.

최근 버전의 엑셀에는 XLOOKUP이라고 VLOOKUP과 HLOOKUP을 합쳐둔 함수도 제공합니다.

판매량, 생산량, 매출 같은 데이터의 변화추세를 한눈에 보는 막대챠트를 자주사용합니다.
중요한 데이터를 가지고 그래프를 그릴때 허전해 보일때가 있습니다.
그래프를 강조하는 혼합형 차트를 그려봅시다.
그냥 보기만해도 혼합형이 선이있어서 오른쪽으로 가면 값이 커지는게 강조됩니다.

혼합형을 그리는 방법은 두가지 입니다.

1. 혼합형 바로 그리기

우선 테이터를 선택하고 [삽입] - [챠트] - [혼합형]를 그립니다.
막대형과 꺽은선으로 그리는 방식이 엑셀에서 자동으로 선정하는데 좀 특이합니다. 
세로인 경우 범위에서 오른쪽에 있는 것이 가로인경우 아래 있는 DATA가 꺽은 선형으로 분류됩니다.

DATA가 2개 : (막대) + (꺽은선)
DATA가 3개 : (막대) + (막대) + (꺽은선)
DATA가 4개 : (막대) + (막대) + (꺽은선) + (꺽은선)
DATA가 5개 : (막대) + (막대) + (막대) + (꺽은선) + (꺽은선)

2. 기존 차트에서 수정하기

또 다른 방법은 우선 다중 막대 그래프로 그리고 하나만 선택해서 다른 차트 기능을 활성화 시키면 됩니다.
사실 어렵지도 않고 원하는 데로 그릴 수 있기 때문에 이 방법을 더 많이 사용합니다.
[삽입] - [차트] - [꺽은선 그래프]로 변경합니다.

이제 그래프 하나에 두가지 서식이 적용되었습니다.
설정을 변경 할 때는 한번에 한가지 범주를 선택하고 설정하면 됩니다.

그럼 이렇게 설정할 수 있습니다.

기능적으로는 별로 어렵지 않습니다.

하지만 디자인은 다른 문제라 예쁘게 챠트를 그리기가 어렵더라고요.

그래도 의사표현을 잘하기 위한 팁은 X축과 Y축을 명확하게 명시하고 혼합형그래프를 사용하는 것입니다.

저는 미적감각이 거의 없는 사람이라서 가끔 구박받고는 합니다.
하지만 이렇게 표현하면 최소한 깔끔하면서 말하고자 하는 것을 깨끗하게 할 수 있겠죠
경험상 메시지가 명확해 진 것만으로도 불만은 많이 줄어들더라고요.

이 그래프는 똑같은 그래프를 두개 그려서 하나는 막대 하나는 꺽은선으로 만든 것입니다.

제 작품중에서는 그나마 보기 깔끔하더라고요

각자 상황에서 보기 좋은 차트를 연습해 봅시다.

목차

1. 상관관계에 대해서

2. Fitting 하기

3. 상관계수 R2에 대해서

4. Linest 함수

5. Linest 함수의 추가요소(R2 값 얻기)

1. 상관관계에 대해서

높은 품질의 제품은 정확한 계측기술로 얻을 수 있다는게 개인적인 생각입니다.
하지만 비용을 적게 들이는 평가 기술을 개발해야 제품에 적용할 수 있습니다.

그러기 위해서는 여러가지 요인의 상관성을 분석을 해야 하는데 회귀분석이라고 합니다.
그러나 저는 이 과정을 Fitting이라고 부릅니다. 이걸 잘하면 비용 절감을 효율적으로 이룰 수 있습니다.
상황은 보통 아래와 같습니다.

CASE ①. 외부 계측기로 1시간 측정해야 하는 A와 내부에서 계측 가능한 B
CASE ②. 완성품으로 알 수 있는 A와 단품에서 알 수 있는 B

B는 상대적으로 비용이 적게 드는 평가 항목이니 상관성을 잘 확보하면 A를 측정하는 것과 같은 효과를 낼 수 있습니다.

2. Fitting 하기

예시 DATA 를 분산형 그래프로 그려 보겠습니다.

보통의 경우 A가 더 정확하지만 B로 진행할때 절약이 됩니다.
아래 DATA를 분산형 그래프로 그리면 A가 증가하면 B도 증가한다는 것을 알 수 있습니다.
그런데 정확하게 얼마가 들어가느냐를 알기 위해서 추세선을 그립니다.

수식을 챠트에 표시하게 하면 "Y=2.1223x + 2.3716" 이라는 방정식을 얻었습니다.

또 상관계수 R2 = 0.9523도 얻을 수 있습니다.
실험의 오차가 있겠지만 그래도 B는 A의 2배가 약간 넘는 것으로 보이네요.

3. 상관계수 R2에 대해서

또 R2 값이 주어지는데 이는 실제 실험한 DATA가 이 추세선에서 벗어 나는 정도입니다.
RMS방식으로 완벽하게 일치하면 값이 1로 나옵니다.
기준이야 적용하기 마련이지만 0.95이상이면 A와 B는 충분히 "상관있다"고 결론 내릴 수 있습니다.

1차 함수, 즉 선형성만 확인하는 경우가 많습니다. 하지만 상황에 따라서 다차방적식을 고려해야 하죠.
추세선 속성에서 [다항식]을 선택하면 차수를 높일 수 있습니다.

추세선 옵션 아래에서 "R-제곱 값을 차트에 표시"하면 얻을 수 있습니다.

3차까지 높였을 때 0.96까지 R2가 상승하는 것을 알 수 있습니다.
다만 4차 이후에는 변곡점의 모양으로 봤을때 큰수에서 신뢰성이 떨어집니다.

사실 차수가 높아지면 숫자가 커질때 문제가 될 수 있습니다.
고차 방정식의 경우 ① X 축을 0~1까지 스케일링 해서 사용할 수 있을때 혹은
② DATA의 크기가 한도가 있고 거의 거기까지 실험을 했을 때 사용할 수 있습니다.

이것을 못할 땐 그냥 선형방정식만 보고 판단하는게 더 나을 수 있다는 것이죠.

4. Linest 함수

그래프를 일일히 그리지 않고 바로 숫자로 분석하는 Linest 함수를 소개합니다.

LINEST(known_y's, [known_x's], [const], [stats])

• known_y's : 필수 요소입니다. y = mx + b 식에서 이미 알고 있는 y 값의 집합입니다.
• [known_x's] : 선택 요소입니다. y = mx + b 식에서 이미 알고 있는 x 값의 집합입니다.
  입력하지 않으면 1, 2, 3, 4, … 이 들어갑니다.
• [const] : b가 True이면 정상적으로 계산 False면 0으로 고정됩니다.
• [stats] : 선택요소로 True면 추가 통계값을 반환합니다.

Linest는 2개이상의 결과값을 반환합니다.
=LINEST(C23:C32,B23:B32,1,0) 라는 수식을 적으면 "Y=2.125x + 2.354" 의 2개 숫자를 반환합니다.
그래서 INDEX 함수를 추가로 사용해야 합니다. 아래 표를 보시면 INDEX의 활용법을 알수 있습니다.

다항식을 적용하면 좀더 길어집니다.
12차까지 적용할 수 있으면 하는 방법은 

LINEST(known_y's, [known_x's]^{1,2,3, ... , 12}, [const], [stats])
이 다차방정식으로 들어가면 여러가지 솔루션을 마련할 수 있습니다.

예시 : =INDEX(LINEST(C23:C32,B23:B32^{1,2,3},1,0),1)

5. Linest 함수의 추가요소(R2 값 얻기)

LINEST(known_y's, [known_x's], [const], [stats])
Linest의 구성중 [stats]가 TRUE(1)를 알아 보겠습니다.

[stats]을 1이나 True로 입력하면 입체적인 DATA가 됩니다.

예시 : =LINEST(C21:C30,B21:B30^{1,2,3},1,1)

이렇게 되면 2차 행렬 형식으로 index를 작성하면 됩니다.
여기서 우리가 가장 궁금한 R2는 (3,1)입니다. 이걸 가장 많이 사용합니다.
입력 방법 : =INDEX(LINEST(C23:C32,B23:B32^{1,2,3},1,1),3,1)
각 항목을 도움말을 그대로 사용했습니다.