본문 바로가기
반응형

분류 전체보기221

[엑셀] 특정 문자열에 해당하는 값 참조하기 VLOOKUP, MATCH VLOOKUP 은 자주 등장하는 유용한 함수로, 다음과 같이 사용한다. VLOOKUP(찾을 문자열, 참조할 데이터, 몇 번째 열의 셀을 참조할지 지정, 0) 마지막의 0은 '완전일치'를 뜻하는 옵션이다. FALSE로 입력해도 같은 의미이며 TRUE, 혹은 1을 입력할 경우 완전일치가 없으면 주어진 값을 넘지 않는 최대의 값을 반환하게 된다. (마지막 인자를 생략하면 TRUE로 간주한다. TRUE에 대한 자세한 내용은 포스트 말미에서 확인하자.) VLOOKUP 은 주로 위와 같이 쓰인다. =VLOOKUP(H2, A:F, 2, 0) H2 셀에 있는 내용을 A열에서 찾아서 그로부터 2번째 위치한 열 (=B열) 의 값을 반환하라는 명령이다. A:F는 참조할 셀을 찾을 범위인데, 위에서 2번째 열을 지정했으므로 .. 2017. 8. 8.
[엑셀] 도수분포표/히스토그램 만들기 (2016 데이터분석도구 설치하기) 아래와 같은 자료가 있다고 하자. 무슨 숫자가 얼마나 있는지 궁금해서 다음과 같은 차트를 만들었다면 별로 도움이 되지 않는다. 그냥 각 숫자가 한 개씩 있다는 정보일 뿐이다. 이럴 때 필요한 것이 도수분포표와 히스토그램이다. 도수분포표는 계급을 이용하는데, 계급은 위에 나열된 숫자들을 일정 구간으로 묶어 주는 역할을 한다. 각 숫자는 하나씩 있지만 1에서 10 사이에 위치하는 숫자는 몇 개인지, 10에서 20 사이에 위치하는 숫자는 몇개인지를 보면 숫자들의 전체적인 분포가 눈에 들어올 것이다. 본격적으로 계급을 정해 보자. 계급을 정하기 위해 주어진 자료가 어디부터 어디까지 분포하는지, 자료의 최소값(MIN)과 최대값(MAX)을 확인한다. 숫자들은 최소 0부터 최대 131까지 분포한다고 한다. 계급은 정.. 2017. 8. 7.
[MySQL] 결과 테이블의 일부 조건만 출력하기 HAVING 연습으로 계속 사용하던 두 테이블, product_data 와 user_data에서 각 유저가 구매한 총 금액을 구하려면 어떻게 해야할까? user_data 테이블의 product 항목을 product_data 테이블에서 참조하여 금액을 불러오고, 해당 금액의 합계를 sum()으로 구하면서 user_id 별로 그룹핑해 주면 되겠다. 즉, SELECT u.user_id, sum(p.price) FROM user_data as u LEFT JOIN product_data as p ON u.product=p.product_id GROUP BY u.user_id ORDER BY sum(p.price) DESC; 정도로 적으면 되겠다. 결과는 다음과 같다. 이중에서 총 구매금액 합계가 5억 5천 이상인 유저만 출.. 2017. 8. 4.
[엑셀] 랜덤추출하기 RANDBETWEEN() 다음과 같은 자료가 있다고 하자. A열에 있는 값 중 하나를 랜덤으로 추출하려면 어떻게 해야할까? A1 ~ A26 셀에 있는 값 중 하나를 참조하도록 하면 될 것이다. 숫자가 바뀌므로 2017/08/02 - [엑셀] - [엑셀] 계산 결과 값으로 셀 지정하기 INDIRECT() 에 등장한 INDIRECT를 이용하면 되겠다. 셀 주소는 A(1~26중 랜덤) 의 모양이면 되겠는데, 이때 셀 주소의 숫자 부분 입력을 위해 RANDBETWEEN을 사용한다. RANDBETWEEN은 인자로 받은 두 수 사이의 정수를 무작위로 반환하는 함수다. A1 부터 A26 까지 숫자가 1 ~ 26 사이에서 바뀌어야 하므로 RANDBETWEEN 은 다음과 같이 입력한다. =RANDBETWEEN(1, 26) 이제 &를 이용해 숫자.. 2017. 8. 3.
[엑셀] 계산 결과 값으로 셀 지정하기 INDIRECT() 어떤 계산 결과값으로 셀을 지정해 주고 싶은 경우가 있다. 예를 들어 다음과 같은 자료에서 A1에 있는 값은 "A"인데, 그로부터 17번째 아래에 있는 값은 무엇일까? 엑셀에게 1+17 번째를 계산시킬 수 있을까? 물론 암산하여 =A18과 같이 적어주면 되겠지만, 계산이 복잡해지면 일일이 계산하여 결과값을 적어줄 수는 없는 노릇이다. 그렇다고 =A(1+17)과 같이 적으면 인식될 리가 없다. 이럴 때 사용하는 함수가 INDIRECT다. INDIRECT는 무엇이 되었든 괄호 안의 최종 텍스트가 셀 주소 모양 (A1, B12, T34 등) 이기만 하면 해당 셀의 값을 반환해 준다. 그러니 먼저 계산식을 이용하여 A18에 해당하는 텍스트를 만들어야겠다. A(1+17)을 A18로 인식시키려면 앞의 문자인 A와 .. 2017. 8. 2.
[엑셀] 상/하위 몇% 의 평균 구하기 2017/07/13 - [엑셀] - [엑셀] 배열수식에 여러 개의 조건 사용하기 와 2017/07/14 - [엑셀] - [엑셀] 상/하위 몇 퍼센트에 해당하는 값 알아보기 percentile() 위 두 가지를 이용하면 상/하위 몇%의 값뿐 아니라, 그 구간에 해당하는 값의 평균도 구해볼 수 있다. (AVERAGEIFS 등의 함수를 이용할 수도 있으나 여기서는 배열수식을 이용하기로 한다.) 다음과 같은 자료가 있다고 하자. 일단 하위 10%에 해당하는 값을 찾으려면 Percentile.INC 를 이용해야 할 것이다. (2017/07/31 - [엑셀] - [엑셀] PERCENTILE INC와 EXC의 차이 참고) 345.3이라고 한다. 그러니 하위 10%에 해당하는 값은 317, 336, 337, 345 네.. 2017. 8. 1.
[엑셀] PERCENTILE INC와 EXC의 차이 2017/07/14 - [엑셀] - [엑셀] 상/하위 몇 퍼센트에 해당하는 값 알아보기 percentile() 에서 엑셀이 어떻게 백분위수를 구하는지 언급한 적이 있다. Excel 2016 에서는 Percentile 함수가 Percentile.EXC 와 Percentile.INC 두 가지로 나누어졌는데, 이중 Percentile.INC 가 이전에 사용하던 Percentile 과 같은 함수이다. Percentile.EXC 는 약간 다른 결과를 내어 주는데, 두 함수가 어떻게 다른가 살펴보자. Percentile.INC 는 주어진 백분위수가 몇 번째인지 판단할 때 정확히 다음과 같은 공식을 사용한다. (n-1) x p + 1 n은 모든 데이터의 개수고, p는 백분위(0~1)이다. 예를 들어 앞선 포스트에서 .. 2017. 7. 31.
[MySQL] 일부 값만 음수로 계산하기 (조건문 CASE) 다음과 같은 자료가 있다고 하자. 위와 같이 엉망인 자료는 드물겠지만, 어쨌든 flag=1 은 코인 충전, flag=2는 코인 사용이라고 생각해 보자. 이때 각 유저별로 갖고 있는 코인의 총량을 알고 싶다면? flag=1 인 경우는 coin을 더하고 flag=2인 경우는 coin을 빼야 할 것이다. 엑셀에서는 IF를 쓰면 되겠고, 이런 경우 SQL에서는 CASE를 쓴다. SELECT CASE WHEN flag=1 THEN coinWHEN flag=2 THEN coin * -1ELSE NULLEND FROM cointable; 위 쿼리를 실행시키면 다음과 같은 결과를 볼 수 있다. CASE문 전체가 하나의 열을 만드는 것을 알 수 있다. 다시 쿼리를 보자. SELECT CASE WHEN flag=1 THE.. 2017. 7. 28.
[MySQL] apmsetup으로 연습환경 구축하기, 데이터 넣기 Import MS ACCESS에 이어 Apmsetup으로 연습환경을 구축해 보자. Apmsetup의 APM은 각각 Apache, PHP, MySQL 을 의미한다고 하며, 우리는 MySQL DB 에서 SQL을 연습해 볼 것이다. (MS ACCESS에서는 몇 가지 지원하지 않는 쿼리가 있어, 이쪽에서 연습하기로 한다.) https://kldp.net/apmsetup/ 위 주소에서 Apmsetup을 받아 설치한다. 다음과 같은 트레이 아이콘이 생길 것이다. 오른쪽 버튼을 눌러 [MySQL 관리]로 들어간다. (root 패스워드 변경을 먼저 해 두어도 좋다. 초기 로그인은 root / apmsetup 으로 가능하다.) root / apmsetup 으로 로그인한 후 데이터베이스를 만들어 준다. MS ACCESS와 마찬가지로 .. 2017. 7. 25.
[엑셀] 상/하위 몇 퍼센트에 해당하는 값 알아보기 percentile() 통계적으로 백분위수(Percentile)를 구하는 방법에는 여러 가지가 있다고 한다. 엑셀은 어떻게 계산하는지 알아보자. 다음과 같은 자료에서 하위 30%에 있는 값은 3이 될 것이다. 수식으로는 percentile()을 이용하여 이렇게 적는다. 말하자면 이런 셈이다. 실제로 계산하는 방법은 조금 다르지만, 쉽게, 맨 처음 숫자는 0%이므로 말하자면 '0번째 숫자'로 치고, 1부터 첫 번째 숫자로 생각한다고 보면 이해가 빠를 것이다. (정확한 계산 방법은 2017/07/31 - [엑셀] - [엑셀] PERCENTILE INC와 EXC의 차이 를 참고) 자료가 1부터 시작한다면 이야기가 좀 다르겠다. 말하자면 1이 0%이므로 0번째 숫자, 2부터 첫 번째 숫자가 되고 이때 하위 10%는 1과 2 사이 어디.. 2017. 7. 14.
반응형

티스토리툴바