본문 바로가기

엑셀/도구 엑셀로 푸는 통계69

[엑셀 통계] 70. 엑셀 추세선 함수 (회귀선 함수) 산점도에서 추세선을 추가하는 방법 말고, 그래프 없이 추세선을 구하는 방법을 알아봅시다. 먼저 예제에 사용할 데이터를 만들겠습니다. y=10x+5 에서 추출한 데이터입니다. 추세선을 구할 때는 linest 라는 함수를 사용합니다. y=ax+b 에서 a와 b값을 출력하기 때문에 두 칸이 필요합니다. 아래와 같이 사용합니다. TRUE로 놓아야 b값이 구해집니다. 입력결과는 아래와 같습니다. 추세선은 y=10x+5 입니다. 맞게 구해진 것을 알 수 있습니다. 만약 FALSE 라고 놓는다면, b=0으로 놓고 추세선을 구합니다. 한번 구해보면 아래와 같습니다. 2023. 4. 14.

[엑셀 통계] 69. 정규분포함수에서 랜덤 추출하기 엑셀에는 정규분포에서 랜덤으로 추출하는 함수를 제공하지 않습니다. (참고로 R에서는 제공합니다.) 다행히 방법이 있습니다. 여러 함수를 조합하여 정규분포에서 랜덤추출하는 상황을 구현할 수 있습니다. 엑셀에는 표준정규분포의 역누적분포함수가 있습니다. 역누적분포함수는 누적분포함수의 역함수입니다. 누적분포함수의 x축이 확률변수이고 y축이 확률인 반면, 역누적분포함수는 x축이 확률이고 y축이 확률변수입니다. 표준정규분포의 역누적분포함수에 0~1 사이의 확률값을 입력하면 표준정규분포의 확률변수 값을 출력합니다. 예를 들어 확률 0.5를 입력하면 0이 출력됩니다. 표준정규분포의 역누적분포함수는 아래와 같습니다. norm.s.inv( ) 입력값으로 0~1 사이 랜덤값을 입력할 수 있다면, 표준정규분포에서 표본을 랜덤.. 2022. 8. 21.

[엑셀 통계] 68. 엑셀에서 상자수염그림 그리는 방법과 해석 아래와 같은 데이터가 있다고 합시다. 두 반의 수학성적입니다. 삽입의 차트에서 상자수염그림을 선택합니다. 그래프가 뭔가 이상하죠? 너무 큰 값이 하나 있습니다. 이상치라고 부릅니다. 데이터를 살펴보니 550이 있는데, 점수는 100점만점이니까 잘못 입력된 값입니다. 55점이 잘못 입력된 것입니다. 55점으로 수정해줍니다. 이와 같이 상자수염그림에서는 이상치를 쉽게 찾을 수 있습니다. 이번에는 범례를 추가합시다. 먼저 아래 1을 선택하고 delete 키를 눌러서 제거합니다. 차트 디자인 탭에서 범레를 추가해줍니다. 차트 제목을 바꿔주면 완성입니다. 의미를 알아봅시다. 가로선이 총 다섯개 있습니다. 각각은 분위수입니다. 위에서 부터 설명하면 아래와 같습니다. 100% 75% 50% 25% 0% 50% 분위수.. 2022. 4. 11.

[엑셀 통계] 67. 엑셀에서 각도로 입력된 삼각함수 구하는 법 엑셀의 sin, cos, tan 함수는 라디안 각만 입력받습니다. 우리에게 더 익숙한 360분법 각도로 입력할 경우 변환을 해주어야 하는데요. 180/pi 를 곱해서 라디안 각으로 변환해주면 됩니다. 더 편한 방법이 있는데요. 아래와 같이 radians 함수를 이용하여 각도를 라디안으로 바꾸고 입력해주면 됩니다. 2022. 2. 28.

[엑셀 통계] 66. 엑셀에서 NA 포함된 자료 평균,최댓값 등 구하기 아래와 같이 NA가 포함된 자료가 있다고 합시다. 평균을 구하려고 하면 NA가 반환됩니다. NA인 값을 제외하고 평균을 구하고 싶은데요. 이런 경우 사용하는 함수가 aggregate 함수입니다. 가장 먼저 함수 번호를 입력해야합니다. 1번이 평균입니다. 다음은 옵션인데요. NA를 무시하려면 6번을 넣으면 됩니다. 콤마를 찍은 후, 계산하고 싶은 행을 선택해주면 됩니다. 2022. 2. 23.

[엑셀 통계] 65. 엑셀에서 NA 사용하기 결측치가 있는 경우 해당 셀을 비워두면 함수 계산 시 0으로 취급 됩니다. 이는 통계값 등 분석 결과에 영향을 미칠 수 있으므로 결측치 처리를 해주어야 합니다. 엑셀에서 결측치 처리를 하는 방법은 NA() 함수를 사용하는 것입니다. NA가 포함된 경우의 함수계산은 다음 시간에 알아봅시다. 2022. 2. 23.

[엑셀 통계] 64. 문자를 색으로 구분하기 아래와 같은 자료에서 사람 이름을 색으로 구분하고 싶은 상황입니다. 먼저 어떤 사람이 있는지 unique 함수로 확인합니다. 색을 변경할 셀을 선택한 뒤, [홈]-[조건부 서식]-[셀 강조 규칙]-[텍스트 포함] 으로 들어갑니다. 이름을 입력하고, 색을 선택해주면 됩니다. 2021. 11. 25.

[엑셀 통계] 63. 문자 중복 제거하기, 문자 종류별로 추리기 (unique함수) 아래 데이터에서 어떤 사람이 들어있는지 알고 싶은 상황입니다. 이런 경우 사용하는 함수가 unique 함수입니다. 아래와 같이 사용합니다. 2021. 11. 25.

[엑셀 통계] 62. vlookup vlookup 에서 v는 vertical 입니다. vertical은 세로의 라는 의미인데 세로방향은 '열'입니다. vlookup 함수는 특정 열에서 우리가 원하는 데이터를 가져오는 기능입니다. 예를 들어봅시다. 아래와 같이 수학점수 데이터가 있습니다. 우리는 박선우와 민찬서의 수학점수 데이터를 가져오고 싶습니다. 이때 vlookup 기능을 사용합니다. 아래와 같이 사용합니다. 형식을 정리하면 아래와 같습니다. 전체 데이터는 절대경로로 입력합니다. 열번호는 전체 데이터에서 왼쪽부터 1열입니다. VLOOKUP(찾을 값, 전체 데이터, 열 번호, 일치옵션) 위 예제에 적용하면 아래와 같습니다. VLOOKUP(누구의 수학점수가 궁금한가요?, 전체 데이터, 수학점수는 몇번째 열인가요?, 일치옵션) 일치옵션은 0을.. 2021. 9. 27.

[엑셀통계] 61. 엑셀에서 $로 수식고정 쉽게하는 법(F4) 엑셀에서 수식에 입력된 셀을 고정하는 방법은 $를 입력하는 것입니다. 아래의 경우 C7과 C8만 고정됩니다. 수식의 셀 고정을 쉽게 하는 방법이 있는데요. F4를 이용하는 것입니다. 수식에서 고정하기 원하는 만큼 선택하고 F4를 눌러줍니다. F4 누르기 2021. 9. 25.

[엑셀 통계] 60. 분산분석 하는 법 (일원분산분석) 분산분석이란? 여기서 말하는 분산분석은 일원분산분석을 말합니다. 분산분석은 세 집단 이상의 평균을 비교할 때 사용합니다. 예를들어 A,B,C 반의 수학점수 차이가 있는지 비교할 때 사용합니다. 분산분석의 귀무가설은 아래와 같습니다. 귀무가설 : 모든 그룹의 평균은 같다. 대립가설 : 평균이 서로 다른 그룹이 존재한다. 귀무가설이 기각된다는 것은 세 집단의 평균이 전부 같지는 않다는 것입니다. 분산분석 만으로는 어느 집단 간에 차이가 있는지를 알 수는 없습니다. 이를 알기 위해 하는 분석이 '사후분석'입니다. 엑셀에서는 사후분석을 지원하지 않습니다. 분산분석의 종류 분산분석의 종류는 아래와 같습니다. 오늘 살펴볼 예시는 '일원 분산분석'입니다. 분산분석의 조건 분산분석은 아래 세가지 조건을 만족한 경우 사.. 2021. 9. 2.

[엑셀 통계] 59. 두 그룹, 셋 이상 그룹의 등분산검정 (levene's test) 지난 40강에서 levene's test 를 이용 하여 두 그룹 등분산검정을 했던 글은 지웠습니다. 오류가 있었습니다. 이번 글이 더 일반적인 내용이라 40강 내용을 포함합니다. 엑셀에서는 F검정이라는 등분산검정을 제공합니다. F검정의 한계는 두 그룹의 비교밖에 할 수 없다는 것입니다. 세 그룹 이상을 비교해야하는 경우 Levene(레빈) 검정을 합니다. Levene 검정은 두그룹의 비교도 가능하고, 세 그룹 이상의 비교도 가능합니다. 엑셀에서 제공하지 않는 검정방법이기 때문에 직접 수식을 정의해서 사용해야 합니다. 귀무가설과 대립가설 귀무가설 : 모든 그룹의 분산은 같다. 대립가설 : 분산이 서로 같지 않은 그룹이 존재한다. p값이 0.05보다 클 경우 분산이 같다고 할 수 있음. 통계량 검정통계량인 .. 2021. 8. 31.

[엑셀 통계] 58. 공백 제거하는 방법 엑셀에서 데이터를 다루다보면 아래와 같이 공백이 생기는 경우가 있습니다. 일일히 지워주는 것은 상당히 번거롭습니다. 공백을 한번에 없애는 방법을 알아봅시다. 공백을 없앨 셀을 선택하고 Ctrl+F 를 누릅니다. 바꾸기를 누르고 찾을내용에 한칸을 띄워줍니다. 공백을 입력하는 것입니다. 모두바꾸기를 클릭하면 아래와 같이 공백이 제거됩니다. 2021. 8. 12.

[엑셀 통계] 57. 합이 일정한 임의의 숫자들을 추출하는 방법 (실수편) rand() 함수를 이용하여 원하는 개수의 임의숫자를 추출합니다. 예시에서는 20개를 추출하겠습니다. 합을 구해줍니다. 합해서 나오기 원하는 값을 적습니다. 추출한 값에 목표합을 곱하고, 추출한 값의 합을 나눠줍니다. 합이 50인 임의 실수가 추출되었습니다. 수학적으로 이해해봅시다. 추출한 20개의 합을 T라고 합시다. $a_{1}+a_{1}+\cdots+a_{20}=T$ 각 값에 50/T를 곱했습니다. $a_{1}\cdot \frac{50}{T}+a_{1}\cdot \frac{50}{T}+\cdots+a_{20}\cdot \frac{50}{T}$ 아래와 같이 묶어줍시다. $\frac{50}{T}\cdot (a_{1}+a_{1}+\cdots+a_{20})$ 괄호 안의 값은 T입니다. 따라서 위 수식의 값.. 2021. 7. 14.

[엑셀 통계] 56. 엑셀 유효숫자가 몇개까지 입력될까 엑셀에서는 유효숫자가 최대 15개까지만 표시됩니다. 12345를 네번 입력했는데 15개를 제외한 나머지 숫자는 0으로 바뀝니다. 엑셀에서는 IEEE 754 라는 부동소수점 표기방식을 사용하기 때문이라고 합니다. 자세한 내용은 생략. 숫자형식을 포기한다면 여러자리를 입력할 수는 있습니다. 앞에 '를 입력하거나, 텍스트 형태로 입력할 수 있습니다. 2021. 7. 11.

[엑셀 통계] 55. 생년월일 날짜로 바꾸는 법 (ex. 20201111 -> 2020-11-11) 아래와 같이 구분기호 없이 입력된 생년월일의 서식을 날짜로 바꾸는 방법입니다. Step1) 바꾸려는 셀을 선택하고 [데이터] 탭에서 [텍스트 나누기]를 클릭합니다. Step2) 다음을 누릅니다. 전부 체크 해제합니다. Step3) 다음을 누릅니다. [열 데이터 서식]에서 [날짜]를 선택해줍니다. Step4) 마침을 클릭하면 아래와 같이 날짜형태로 변경됩니다. 2021. 5. 14.

[엑셀 통계] 54. 모평균 구간추정,신뢰구간 (모분산이 알려진 경우) 신뢰구간 모평균 추정에서 95%신뢰구간과 99% 신뢰구간은 아래와 같습니다. $\bar{X}_{1} -1.96\cdot \frac{\sigma}{\sqrt{n}} \leq \mu \leq \bar{X}_{1} +1.96\cdot \frac{\sigma}{\sqrt{n}}$ $\bar{X}_{1} -2.58\cdot \frac{\sigma}{\sqrt{n}} \leq \mu \leq \bar{X}_{1} +2.58\cdot \frac{\sigma}{\sqrt{n}}$ $\bar{X}_{1}$ 은 우리가 뽑은 표본의 평균, $\mu$ 는 모평균, $\sigma$ 는 모표준편차입니다. 엑셀에서 구하기 1) 함수 소개 신뢰구간은 Confidence 함수를 이용하여 구합니다. 아래와 같이 사용합니다. CONFID.. 2021. 5. 9.

[엑셀 통계] 53. 엑셀로 파레토차트 그리는 방법 파레토 차트는 막대그래프와, 꺾은선 그래프로 된 누적백분위 그래프의 혼합형입니다. Step1. 데이터 살펴보기 다섯 사람의 소득데이터 Step2. 데이터 가공 1) 소득에 대해 내림차순 정렬 2) 비율 구하기 3) 누적비율 구하기 Step3. 그래프 그리기 이름,소득,선택하고 아래 그림의 순서로 진행합니다. Step4. 결과 파레토차트가 그려집니다. 비율,누적비율 데이터는 그래프그릴 때는 사용되지는 않지만 값 확인을 위해 구한 것입니다. 2021. 5. 7.

[엑셀 통계] 52. 평균,표준편차,중앙값 등 통계량 한번에 구하는법 평균,표준오차,중앙값,최빈값,표준편차,분산,첨도,왜도,범위,최솟값,최댓값,합,관측수를 한번에 출력하는 방법입니다. Step1 [데이터 탭] - [데이터분석] -[기술통계법]-[확인] Step 2. [입력범위에 자료 선택]-[출력범위 선택]-[요약통계량 체크]-[확인] 결과 2021. 5. 7.

[엑셀통계] 51. 특정 단어가 포함된 행 일괄 삭제 방법 특정한 단어가 포함된 행을 한번에 제거하는 방법입니다. 아래 엑셀파일을 사용하겠습니다. kostat 에서 다운받은 나이 별 암 발병 데이터입니다. 먼저 전체선택을 하고 우측의 필터를 클릭합니다. 아래와 같이 필터가 생깁니다. 성별에서 "계"를 없애고 싶다고 합시다. B열을 선택하고 Ctrl+F 를 클릭합니다. "계"를 입력하고 모두찾기를 클릭합니다. 아래 부분을 클릭하고 Ctrl+A 를 눌러 전체 선택해줍니다. 아래 그림처럼 [시트 행 삭제] 를 클릭합니다. 성별에서 "계"가 사라진 것을 알 수 있습니다. 2021. 5. 7.

[엑셀통계] 50. 아주 유용한 필터 기능 엑셀의 필터 기능은 데이터를 항목화 해주고 원하는 항목만 보이게 할 수 있는 기능입니다. Step1 표에 헤더가 있어야 하구요. 헤더에 해당되는 행을 선택합니다. Step2 아래 그림과 같이 정렬 및 필터를 클릭하고 필터를 눌러줍니다. Step3 각 행별로 생성되는 화살표를 클릭하여 원하는 항목을 보이고 숨길 수 있습니다. 2021. 5. 7.

[엑셀통계] 49. t분포표 직접 만들어보기 먼저 t분포표의 틀을 만들어봅시다. 아래와 같은 틀을 만들겠습니다. 이 틀에 대해 설명드리겠습니다. 아래 빨간 부분은 자유도가 5인 t분포에서 단측검정 유의수준이 0.025인 경우의 우측꼬리의 t값을 의미합니다. 아래 그림을 보면 이해하실 수 있습니다. 값을 구해봅시다. T.INV 함수를 이용하여 구합니다. 나머지 칸도 같은 방법으로 채워 넣으면 됩니다. 아래와 같이 열을 고정하면 그나마(?)편하게 채울 수 있습니다. (아마 더 똘똘하고 간단한 방법이 있을겁니다.) 엑셀 파일을 공유합니다. x=seq(-5,5,0.1) y=dt(x,5) plot(x,y,'l') xp=seq(qt(0.975,5),10,0.1) yp=dt(xp,5) polygon(c(xp,rev(xp)),c(rep(0,length(xp)),.. 2020. 12. 17.

[엑셀통계] 48. 표준정규분포표 직접 만들어보기 먼저 표준정규분포표의 틀을 만들어봅시다. 아래와 같은 틀을 만들겠습니다. 먼저 이 틀을 설명드리겠습니다. 아래 빨간 부분은 Z가 0.44 인 경우의 값을 의미합니다. 이 값은 어떻게 계산될까요? 이 값의 의미는 아래 그림을 보면 이해하실 수 있습니다. 표준정규분포에서 Z값의 왼쪽 부분의 넓이입니다. 값을 구해봅시다. NORM.S.DIST 함수를 이용하여 구합니다. 옵션을 TRUE로 설정해야합니다. 나머지 칸도 같은 방법으로 채워 넣으면 됩니다. 아래와 같이 열을 고정하면 그나마(?)편하게 채울 수 있습니다. (아마 더 똘똘하고 간단한 방법이 있을겁니다.) 엑셀 파일을 공유합니다. x=seq(-4,4,0.1) y=dnorm(x) plot(x,y,'l') xp=seq(-4,0.44,0.1) yp=dnorm(.. 2020. 12. 17.

[엑셀통계] 47. 자동고침 해제하기 [엑셀통계] 47. 자동고침 해제하기 엑셀을 사용하다보면 여러가지 자동고침 기능 때문에 스트레스를 받는 경우가 있습니다. 오늘은 자동고침기능을 해제하는 방법을 알아봅시다. 1. [파일]-[옵션] 클릭 2. [언어교정] - [자동 고침 옵션] 클릭 3. 원하지 않는 기능 해제 2020. 11. 21.

[엑셀 통계] 46. 여러 셀의 내용을 하나로 합쳐주는 concat 함수 [엑셀 통계] 46. 여러 셀의 내용을 하나로 합쳐주는 concat 함수 concat는 여러 셀의 내용을 하나로 합쳐서 하나의 셀에 나타내고 싶을 때 사용하는 함수입니다. 아래와 같이 사용합니다. 드레그해도 되고, 콤마로 연결해도 됩니다. 큰 따옴표를 이용하면 셀에 없는 문자도 추가로 입력할 수 있습니다. 2020. 9. 11.

[엑셀 통계] 45. 입력된 셀에 입력된 셀을 참조하는 indirect 함수 [엑셀 통계] 45.입력된 셀에 입력된 셀을 참조하는 indirect 함수 알아두면 굉장히 유용한 함수가 있어 소개하려고 합니다. 입력된 셀에 입력된 셀을 참조하는 함수입니다. 아래 예시를 보시면 쉽게 이해되실 것입니다. 예를 들면 이런 경우에 사용할 수 있습니다. 아래와 같이 평균,표준편차,최댓값,최솟값을 계산했습니다. 여기서 데이터가 하나 추가된다면, 각각의 수식을 모두 변경해주어야 합니다. 굉장히 번거로운 작업입니다. 만약 indirect 를 이용하여 정의되어 있다면, indirect 셀만 변경해주면 됩니다. 2020. 9. 11.

[엑셀 통계] 44. 정리 안된 데이터로 막대그리프 그리기 도구 엑셀로 푸는 통계44. 정리 안된 데이터로 막대그리프 그리기 마구잡이 데이터로 막대그리프 그리기 보통 막대그래프는 아래와 같은 데이터를 이용하여 그립니다. 아메리카노 50카페라떼 10카페모카 40 독립변수의 범주들과, 각 범주의 수량을 나타낸 데이터인데요. 오늘 우리가 엑셀로 막대그래프로 그려볼 데이터는 아래와 같은 데이터입니다. 핀란드미국프랑스노르웨이일본프랑스영국프랑스스웨덴 미국영국미국 편의상 일부만 가져왔습니다. 전체 데이터는 엑셀파일로 제공할 것입니다. 전체 데이터 : 위 데이터로 아래 그래프를 그려볼 것입니다. 막대그래프를 그리려면 커피의 예시처럼 범주:수량 형식의 데이터로 변형해야합니다. 직접 세서 데이터를 만드는 것은 너무나 고통스러운 작업입니다. 어떻게 해야 할까요? 피벗테이블을 이용하.. 2020. 9. 1.

[엑셀 통계] 43. 피어슨 상관분석 p값 구하기 도구 엑셀로 푸는 통계43. 피어슨 상관분석 p값 구하기 R에서 상관분석을 할 때, p값을 구해주지 않는 한계가 있었습니다. 오늘은 p값을 직접 구해보겠습니다. 42강에서 구한 R값에서 출발하겠습니다. 아래 통계량을 이용하면 "상관계수가 0이다" 라는 귀무가설로 t검정을 할 수 있습니다. 아래 통계량은 자유도가 n-2인 t분포를 따릅니다. 위에서 구한 R값으로 t통계량을 구하면 아래와 같습니다. p값을 구해봅시다. 양측검정과 단측검정을 할 수 있는데요. 상관계수가 0인지 아닌지에 관심이 있으면 양측 검정을 하시면 되구요. 0보다 큰지 아닌지, 혹은 0보다 작은지 아닌지에 관심이 있으시면 단측 검정을 하시면 됩니다. R과 SPSS 디폴트 값이 양측이므로, 일단 양측검정을 하겠습니다. T.DIST.2T 함수.. 2020. 6. 12.

[엑셀 통계] 42. 엑셀에서 피어슨 상관분석을 하는 세 가지 방법 도구 엑셀로 푸는 통계 42. 엑셀에서 피어슨 상관분석을 하는 세 가지 방법 상관분석은 두 변수 사이의 선형관계의 정도를 분석하는 방법입니다. 상관계수 r과, 유의확률 p가 계산됩니다. 상관분석은 크게 둘로 나뉩니다. 정규분포 가정 가능 여부에 따라 모수적 방법과 미모수적 방법입니다. 모수적방법에는 피어슨상관분석이 있고, 미모수적 방법에는 스피어만상관분석, 캔달의 타우 상관분석이 있습니다. 모수적 방법 : 피어슨 비모수 : 스피어만, 캔달의 타우 엑셀에서는 피어슨 상관분석만을 제공합니다. 스피어만이나 캔달의 타우는 이후 강의에서 직접 수식을 정의하여 사용하겠습니다. 엑셀에서 피어슨 상관분석을 하는 방법은 세가지가 있습니다. 산점도의 추세선을 이용하는 방법, CORREL 이라는 함수를 이용하는 방법, 데이.. 2020. 6. 9.

[엑셀 통계] 41. T.DIST.RT 와 T.DIST.2T 의 차이 도구 엑셀로 푸는 통계 41. T.DIST.RT 와 T.DIST.2T 의 차이 엑셀에서 t분포의 넓이를 구해주는 함수는 T.DIST.RT와 T.DIST.2T가 있습니다. 오늘은 이 둘의 차이를 알아보도록 합시다. T.DIst.RT 의 RT는 Right Tail입니다. 말 그대로 오른쪽 꼬리의 넓이를 구해줍니다. 사용 방법은 T.DIst.RT(t값,자유도)입니다. 예를들어 T.DIst.RT(1,50)은 자유도 50인 t분포의 아래 넓이를 구해줍니다. 값은 0.161입니다. 이번에는 T.DIST.2T 함수를 이용하여 구해봅시다. T.DIst.2T(1,50)의 값은 0.322입니다. 위 결과의 두배가 나옵니다. 이유를 알아봅시다. T.DIST.2T는 '양측검정'을 할 때 사용하는 함수입니다. 양측검정은 유의수.. 2020. 6. 5.

이전 1 2 3 다음

티스토리툴바