본문 바로가기
반응형

엑셀61

[로또] 로또 1등 당첨금액 계산 및 확률 검증 (큰수의법칙) 로또는 종종 사람들로부터 의심의 눈초리를 받는다. 대표적인 의심은 1등 확률이 815만 분의 1임에도 어떻게 매 회차 1등 당첨자가 그렇게 많이 나오냐는 것인데 당첨자의 수가 과연 믿을 만한 것인지 생각해 보자. (1등 당첨확률 구하는 식은 2016/11/08 - [R] - [R. 아르] 로또 확률 계산하기 for() / choose() 참고) 나눔로또 홈페이지에 들어가면 누구나 이전 회차들에 대한 당첨금 정보를 엑셀로 다운받을 수 있다. http://www.nlotto.co.kr/gameResult.do?method=byWin 이것은 2018년 5월 25일에 받은 파일이다: 열어보면 다음과 같은 데이터가 나온다. 우리가 알고 싶은 것은, 예를 들어 807회차에 1등이 7명 나올 만한가 하는 것이다. 이.. 2018. 5. 28.
[엑셀] 특정 문자와 일치하는 행 모아서 다른 시트로 추출하기 2018/03/07 - [엑셀] - [엑셀] 특정 문자가 들어있는 행 모아서 추출하기의 내용을 토대로 자료가 다른 시트에 있을 경우 어떻게 해야 하는지 예제로 살펴보자. Sheet1에 다음과 같이 자료가 있고 그중 거래처가 A인 것만 모아서 Sheet2 에 표시하려면 Sheet2에서 일단 다음과 같이 시작하면 되겠다. =IF(Sheet1!E:E="A", ROW(A:A)) 배열수식이므로 Ctrl + Shift + Enter를 누른다. 이제 위 배열은 거래처가 A인 행의 행번호를 포함하고 있을 것이므로 Small 함수로 첫 번째 값을 추출한다. =SMALL(IF(Sheet1!E:E="A", ROW(A:A)), 1) 수식이 변경될 때는 항상 Ctrl + Shift + Enter를 눌러야 수식이 정상적으로 작동.. 2018. 4. 17.
[엑셀] 특정 문자와 일치하는 행 모아서 추출하기 왼쪽 표에서 Type이 c인 것만 모아 오른쪽처럼 별도의 표를 만들려면 어떻게 해야 할까? 가장 간단한 것은 원본 표에 필터를 거는 방법인데 가끔은 필터를 걸 수 없는 상황도 있다. 예를 들어 2018/03/06 - [엑셀] - [엑셀] 다른 시트로부터 내용 가져오기 Importrange 에 나온 것 처럼 다른 시트에서 내용을 가져올 경우 최초 필터는 작동하지만, 이후 원본에 새로 기입된 데이터는 필터의 적용을 받지 않는 불편함이 있다. 우리가 오늘 다룰 수식에는 배열수식과 index, small 등의 함수가 사용되며 그리 간단하지는 않다. 차근차근 가 보자. 일단 Type 열만 생각해 보면, 각각 C5, C8, C11 을 가져와야 함을 알 수 있다. 예상하듯, 5, 8, 11 의 숫자를 적절히 반환할 .. 2018. 3. 7.
[구글시트] 다른 시트로부터 내용 가져오기 Importrange 다음과 같이 a.xlsx 와 b.xlsx 두 개의 시트가 있고, b.xlsx 에서 a.xlsx 에 있는 특정 셀의 내용을 불러오고 싶다면 위와 같이 표시하려는 셀에서 = (equal) 입력 후 원본 셀을 클릭하면 된다. 엑셀이 자동으로 파일 이름과 시트 주소, 셀 위치를 파악해 적어준다. a.xlsx의 A1셀에 해당하는 내용을 잘 가져오는 것을 볼 수 있다. 만일 다른 셀들의 내용도 가져오려면 수식에서 셀 주소를 고정시키는 $ 문자를 삭제한 후 Ctrl + D 와 Ctrl + R 로 수식을 채워주면 된다. 엑셀에서는 이와 같은 작업이 비교적 수월하지만, Google Spreadsheet 에서는 같은 방식을 사용할 수 없다. 아무리 = (equal) 을 누르고 다른 시트를 클릭해 봐도, 수식을 자동으로 입.. 2018. 3. 6.
[엑셀] Indirect 를 이용하여 한 행씩 번갈아 추출하기 다음과 같이 내림차순으로 정렬되어 있는 자료를 두 사람에게 비교적 공평하게 분배해야 한다고 할 때 당연한 얘기지만, 다음과 같이 나누는 것은 전혀 도움이 되지 않는다. 다음과 같이 한 행씩 번갈아가며 나누는 것이 좋을 것이다. 사람1은 다음과 같이 A열 2행, 4행, 6행... 의 자료를, 사람2는 3행, 5행, 7행... 의 자료를 가져오면 되겠다. 2017/08/02 - [엑셀] - [엑셀] 계산 결과 값으로 셀 지정하기 INDIRECT() 에 나온 INDIRECT 를 사용하자. 문자 부분은 A로 고정이고, 숫자는 2, 4, 6, 8... 과 같이 증가하면 되겠다. 일정하게 증가하는 수열은 수식을 적는 행의 번호 row() 를 이용하면 간편하다. =ROW()*2 C2 에서 row() 는 2를 반환하고.. 2018. 3. 2.
[엑셀] 중복값의 위치 찾기 다음과 같은 자료에서 놀랍게도 단 하나의 값이 중복이라고 할 때 (-_-) 중복값의 위치를 찾고 싶다면 [조건부 서식]의 [중복 값]을 이용하는 것도 방법이다. 친절하게 색깔로 알려준다. 하지만 이게 몇만 셀쯤 되면 중복값이 뭔지, 어디에 있는지 아무리 PgDn 키를 눌러도 찾기가 쉽지 않다. 이럴 땐 피벗테이블을 이용하는 것이 도움이 된다. 2016/09/23 - [R] - [R.아르] R에서 피벗테이블 사용하기 dcast()에서 예로 든 것처럼 피벗테이블은 각 항목의 개수를 파악할 때도 유용하다. 옆에 임의의 열을 만들어 열 이름을 붙이고, 아무 값이나 채워 주자. 본 예에서는 'a' 로 채웠다. 이미 데이터 옆에 값이 채워진 열이 있다면 그 열을 그대로 이용해도 된다. 값이 무엇인지는 크게 중요치 .. 2018. 1. 26.
[엑셀] 표준편차 STDEV.P 와 STDEV.S 의 차이 표준편차란 산포도의 일종으로, 주어진 자료들이 평균에서 얼마나 흩어지는 경향성을 보이는지 판단하는 지표다. 다음과 같은 자료가 있다면 평균은 80, 표준편차는 5가 된다. 여기서 표준편차가 5라는 것은 자료 중 절반은 평균보다 5가 크고, 나머지 절반은 평균보다 5가 작다는 의미이다. STDEV.P 와 STDEV.S 는 모두 표준편차를 구하는 함수인데, 전자는 주어진 자료가 전부일 때, 후자는 주어진 자료가 일부일 때 사용한다. [이미지: 위키피디아] 위 그림에서 위쪽, 1번부터 12번까지가 우리가 알고 싶은 '전체' 라고 할 때, 이것을 모집단Population 이라고 한다. 현실에서는 모집단 전체의 자료를 모으는 것이 거의 불가능하므로 일부 자료를 모아 그 자료를 토대로 모집단의 특성을 추측한다. 위.. 2018. 1. 17.
[엑셀] 두 가지 이상의 다중조건으로 VLOOKUP 사용하기 다음과 같은 자료에서 조건1은 "2"고 조건2는 "c"인 값만 찾으려면 어떻게 해야 할까? 말하자면 VLOOKUP 에 두 가지 조건을 거는 것인데 가끔 생각이 안 날 때가 있다. 방법은 3가지로, 각각 VLOOKUP, INDEX-MATCH, 배열수식을 사용하는 방법이다. 1. 하나의 열을 추가하고 VLOOKUP 을 사용 VLOOKUP을 사용하려면 열을 하나 추가해야 한다. 다음과 같이 A와 B열의 문자열을 &로 묶는 열을 하나 만든다. 새로 만든 A열에서 VLOOKUP으로 "2c"를 찾는다. 같은 방법으로 3개, 4개의 조건도 동일하게 찾을 수 있다. 2. 열 추가 없이 INDEX-MATCH 사용 2018/06/13 - [엑셀] - [엑셀] 최초로 0이 아닌 값이 나오는 곳 찾기 INDEX() MATCH.. 2017. 8. 24.
[엑셀] 한 셀 내에서 특정 문자 개수 구하기 한줄요약: =(LEN(셀)-LEN(SUBSTITUTE(셀,"찾는문자","")))/LEN("찾는문자") COUNTIF()는 기본적으로 '셀의 개수'를 세는 함수이므로 한 셀 내에 반복되는 특정 문자열의 개수를 셀 수는 없다. 한 셀 내에서 특정 문자열의 개수를 세고 싶다면 의외로(?) SUBSTITUTE()함수를 사용해야한다. 다음과 같은 자료가 있다고 하자. 기본 전략은 간단하다. 2016/12/29 - [엑셀] - [엑셀] 오른쪽에서 특정 문자까지 추출하기와 마찬가지로 찾으려는 문자를 공백으로 바꾼 후 원래 문자의 길이와 차이를 보는 것이다. 예를 들어 위의 자료에서 "E"를 찾으려면 이와 같이 "E"를 공백으로 바꾼 후에 LEN() 함수를 이용하여 다음과 같이 길이를 구한다. "E"가 하나씩 사라졌으.. 2017. 8. 23.
[엑셀] 수식의 부분 결과 검증하기 F9 2016/10/10 - [엑셀] - [엑셀] 월별 합계 구하기 month() / 배열수식. 2017/07/13 - [엑셀] - [엑셀] 배열수식에 여러 개의 조건 사용하기 등의 포스트에 등장했던 배열수식은 굉장히 강력한 도구지만, 수식이 완성되기 전까지 마땅히 배열을 확인하기 어렵다는 단점이 있다. 예를 들어 다음과 같은 자료에서 A열이 "a"이고 B열이 "가"인 자료들의 합을 구하려면 =IF((A1:A12="a")*(B1:B12="가"),C1:C12) 와 같이 배열을 작성하면 될 듯한데, 실제로 그런지 배열만 입력해서는 알 수 없다. 수식 입력 후 엔터만 치면 다음과 같이 FALSE가 반환되고, Ctrl + Shift + 엔터를 쳐도 1이라는 숫자만 반환될 뿐이다. SUM() 등으로 둘러싸 주어야 비로.. 2017. 8. 22.
반응형