티스토리

가벼운 블로그

검색하기

블로그 홈

가벼운 블로그

lightblog.tistory.com/m

LightBlogger 님의 블로그입니다.

구독자: 16

방명록 방문하기

반응형

주요 글 목록

[엑셀]대분류 선택시 소분류가 달라지는 종속적(반응형) 드랍다운 메뉴 만들기 엑셀에서 드랍다운 목록은 [데이터] > [데이터 유효성 검사] 메뉴에서 만들 수 있다. 오늘은 대분류의 목록 중 하나의 항목을 선택하면, 선택항목에 따라 소분류의 드랍다운 목록이 달라지는 기능을 구현해 보자. 일단은 대분류와 소분류에 사용할 목록이 있어야겠다. 사소한 팁이지만, 목록은 오름차순으로 정렬해 두는 편이 나중에 사용하기에 편리하다. 우리는 이 각각의 목록에 이름을 지정해 줄 것이다. {과일, 동물, 전자제품} 의 목록은 [대분류] 라는 이름으로 {사과, 수박, 자두, 포도}의 목록은 [과일] 이라는 이름으로 지정해 주는 식이다. 이름 지정은 [수식] > [이름 정의] 메뉴에서 할 수 있는데 우리처럼 표로 잘 정리해 놓은 경우에는 그냥 '각 목록의 첫 행을 이름으로 지정' 해 달라고 할 수도 있.. 공감수 4 댓글수 3 2020. 1. 15.
[엑셀] 필터 기능 활용하기 필터는 주어진 데이터를 의도대로 다루는 데 매우 유용한 도구다. 필터의 기능은 크게 두 가지인데, 첫 번째는 정렬. 두 번째는 선별(필터링)이다. 다음과 같은 자료가 있다고 하자. 1행의 No. data1, data2, data3 가 제목행이다. 먼저 제목행에 범위를 잡고 [데이터] - [필터] 를 클릭한다. 혹은 [홈] - [정렬 및 필터] - [필터]를 클릭해도 된다. 제목 행에 필터가 적용되었다. 이제 제목행의 역삼각형 모양 단추를 누르면 해당 열을 기준으로 데이터를 정렬하거나 선별할 수 있다. data1 기준 오름차순 정렬 No. 기준 오름차순 정렬 + data3 의 값이 O 인 것만 선별 필터가 실제로 적용된 경우 각 단추의 모양이 다음과 같이 바뀌어 적용된 필터를 알려준다. ↑ : 오름차순으로.. 공감수 4 댓글수 2 2018. 12. 6.
[엑셀] 숨겨진 행/열을 다시 보이게 하는 세 가지 방법 엑셀을 사용하다 보면 숨겨진 행/열을 찾아야 하는 경우가 종종 있다. 1. 진짜로 숨겨진 경우 예를 들어 다음과 같은 시트에서는 2행부터 11행까지가 숨겨져 있을 터이다. 이 경우 숨겨진 행의 위 아래행을 범위로 잡고 선택된 범위 아무 곳에서나 마우스 오른쪽 버튼을 눌러 [숨기기 취소]를 눌러주면 숨겨진 행이 나타난다. 2. 크기를 작게 해 놓은 경우 위 방법대로 [숨기기 취소]를 눌렀는데도 숨겨진 행이 나오지 않는 경우에는 행들의 높이 (열의 경우 너비) 가 작게 설정되어 있을 수도 있다. 확인을 위해 1행과 12행 사이 조금 두꺼운 부분에서 마우스 오른쪽 클릭 - [행 높이]를 확인해 보면 행 높이가 0.25로 매우 작게 설정되어 있는 것을 알 수 있다. 이 경우 1번과 마찬가지로 위 아래 행까지 범.. 공감수 27 댓글수 6 2018. 10. 22.
[엑셀] 특정 문자가 들어 있는(포함된) 행 모아서 추출하기 2018/03/07 - [엑셀] - [엑셀] 특정 문자와 일치하는 행 모아서 추출하기에 이어서 다음과 같이 조건을 판단하는 열에 A, B, C 에 해당하는 문자가 무작위로 쓰여 있고 그 중 A가 들어 있는 행만을 추출하려면 어떻게 해야 할까? 앞서와 같이 =IF(B:B="A" 하는 식으로 시작할 수는 없다. 그런 식으로는 3행과 7행만 추출될 뿐, 4, 6, 10행은 추출되지 않는다. 우리는 엑셀의 SEARCH 함수를 이용해 이 문제를 해결할 수 있다. SEARCH 함수는 주어진 셀 내에서 특정 문자의 위치를 숫자로 반환해 준다. 아래와 같이 입력해 보자. =SEARCH("A",B3) 셀 내에 A라는 글자가 들어 있는 경우는 해당 글자의 위치를 숫자로 출력하고, 없는 경우에는 에러를 출력한다. 이제 이것.. 공감수 18 댓글수 9 2018. 10. 16.
[엑셀] 인터넷에서 다운받은 파일의 '제한된 보기' 해결 방법 인터넷에서 엑셀 파일을 다운로드 받고 여는 경우 다음과 같은 경고 메시지가 뜰 때가 있다. 이때 '편집 사용' 버튼을 눌러 줘야 정상적으로 사용이 가능한데, 매번 이렇게 하는 것은 번거로운 일이다. 해당 경고 메시지가 나오지 않도록 설정해 보자. 먼저 [파일]을 클릭하고 가장 아래의 [옵션]을 클릭한다. 이제 [보안센터]를 클릭하고 [제한된 보기] - [인터넷에서 가져온 파일에 대해 제한된 보기 사용] 항목의 체크를 해제해 준다. 이제 인터넷에서 다운로드 받은 파일도 경고 메시지 없이 바로 편집이 가능하다. 단, 경고메시지의 내용처럼 다운로드 받은 파일은 위험할 수도 있으므로 본인의 판단 하에 적절히 사용하자. 공감수 1 댓글수 0 2018. 10. 2.
[엑셀] VLOOKUP 대신 INDEX, MATCH 를 사용하면 좋은 4가지 경우 2018/08/28 - [엑셀] - [엑셀] VLOOKUP 대신 INDEX, MATCH 사용하기 에 이어서 VLOOKUP 대신 INDEX, MATCH 를 사용하면 좋은 경우를 살펴보자. 1. 기준 열 왼쪽에 있는 값을 출력할 때 특정 ID에 해당하는 PRICE 는 VLOOKUP으로 쉽게 출력할 수 있다. 그럼 특정 PRICE에 해당하는 ID는? VLOOKUP 으로는 이 문제를 해결할 수 없다. VLOOKUP은 왼쪽 열을 기준으로 오른쪽에 있는 데이터를 출력하기 때문. 하지만 MATCH - INDEX 라면 어렵잖게 해결할 수 있다. 앞서와 마찬가지로 F4에 있는 값이 D:D 중 어디에 있는지 알아내고 =MATCH(F4, D:D, 0) (결과는 4가 출력됨) A열의 4번째라고 알려주면 된다. =INDEX(A.. 공감수 10 댓글수 0 2018. 8. 30.
[엑셀] VLOOKUP 대신 INDEX, MATCH 사용하기 다음과 같은 자료가 있다고 할 때 특정 id에 해당하는 product를 불러오고 싶다면 다음과 같이 Vlookup 을 사용할 수 있다. Vlookup 수식내 요소들의 순서는 다음과 같이 기억하자. =VLOOKUP(F4, A:B, 2, 0) 얘를 이 범위에서 찾아서 두 번째 값을 출력. 마지막 인자인 0은 완전일치를 뜻하며, 일반적인 경우에는 항상 0을 사용하므로 수식의 일부처럼 기억해 두면 좋다. 실제 함수를 완성하는 모습은 다음과 같다. 1. =VL 까지 입력 후 Tap 키를 이용하여 Vlookup 입력 2. F4 셀 클릭 ('얘를' 이라고 중얼거리며) 3. 콤마 입력 4. 마우스를 A열 위에서 B열까지 드래그 ('이 범위에서' 라고 중얼거리며) 5. 콤마 입력 6. 2 입력 ('두 번째 값' 이라고 .. 공감수 5 댓글수 0 2018. 8. 28.
[엑셀] 배열수식에서 AND, OR, NOT 등의 논리연산 사용하기 AND 2017/07/13 - [엑셀] - [엑셀] 배열수식에 여러 개의 조건 사용하기에 다음과 같은 수식이 등장한 적이 있다. 조건1은 3이고 조건2는 b인 행의 값으로 만든 배열 =IF((A:A=3)*(B:B="b"),C:C) 배열수식에서 여러 개의 조건을 사용하려면 *(Asterisk) 를 사용하면 된다는 이야기였다. 위 예시는 말하자면 논리연산 AND 에 해당한다. 배열수식에서는 AND(조건1, 조건2) 대신 (조건1)*(조건2) 를 사용하는 것이다. OR 나 NOT 도 이렇게 표현이 가능하다. OR OR에 해당하는 연산은 +로 표현한다. OR(조건1, 조건2) → (조건1) + (조건2) > 0 위의 예에서 조건1이 1이거나 조건2가 A인 값들은 다음과 같이 구할 수 있다. (배열수식의 값을 직.. 공감수 3 댓글수 2 2018. 7. 10.
[엑셀] 지난 달, 다음 달 입력하기 EDATE() EOMONTH() 가끔 지난 달, 다음 달, 두 달 전, 세 달 전과 같이 날짜 중 월 부분만 필요할 때가 있다. 그럴 땐 EDATE() 나 EOMONTH() 함수를 사용하면 편리하다. 두 함수 모두 두 번째 인자로 변화할 개월 수를 받는데, 한달 전은 -1, 한달 후는 1 과 같이 입력하면 된다. A5에 TODAY()를 입력하고, 위로 EDATE(셀, -1)을 입력한 결과는 다음과 같다. 한달 전의 같은 날짜를 반환해 주는 것을 알 수 있다. 두달 후의 같은 날짜는 EDATE(셀, 2) 와 같이 입력한다. 월에 해당하는 숫자만 필요하다면 범위를 잡고 Ctrl + 1 을 눌러 셀 서식에 들어간 후 (참고: 2016/09/22 - [엑셀] - [엑셀] 셀 내용에 큰따옴표 넣기 (표시 형식)) 사용자 지정에서 M"월" 혹은 .. 공감수 2 댓글수 0 2018. 7. 4.
[엑셀] 최초로 0이 아닌 값이 나오는 곳 찾기 INDEX() MATCH() 다음과 같은 자료가 있다고 할 때 다음과 같이 처음으로 0이 아닌 값이 나오는 날짜를 얻기 위해서는 어떻게 해야 할까? 수식부터 미리 공개하면 다음과 같다. =INDEX($B$1:$H$1,MATCH(TRUE,INDEX(B2:H20,),0)) =INDEX($날짜행,MATCH(TRUE,INDEX(값행0,),0)) 수식의 원리를 아래에서 생각해 보자. INDEX() 는 잘 아는 것처럼 특정 범위에서 어떤 값을 반환하라는 명령이다. =INDEX(A1:C3, 3, 2) 위와 같이 입력할 경우 주어진 범위(A1:C3) 에서 3행 2열의 값인 8을 반환하게 된다. 범위가 2차원이 아니라 1차원일 경우, 값을 하나만 지정해도 된다. 예를 들어 =INDEX(A2:C2, 2) 한 행을 범위로 지정할 경우 뒤의 인자를 2 .. 공감수 17 댓글수 26 2018. 6. 13.
[로또] 로또 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명 나올 만한가 하는 것이다. 이.. 공감수 4 댓글수 1 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를 눌러야 수식이 정상적으로 작동.. 공감수 16 댓글수 51 2018. 4. 17.
[엑셀] 특정 문자와 일치하는 행 모아서 추출하기 왼쪽 표에서 Type이 c인 것만 모아 오른쪽처럼 별도의 표를 만들려면 어떻게 해야 할까? 가장 간단한 것은 원본 표에 필터를 거는 방법인데 가끔은 필터를 걸 수 없는 상황도 있다. 예를 들어 2018/03/06 - [엑셀] - [엑셀] 다른 시트로부터 내용 가져오기 Importrange 에 나온 것 처럼 다른 시트에서 내용을 가져올 경우 최초 필터는 작동하지만, 이후 원본에 새로 기입된 데이터는 필터의 적용을 받지 않는 불편함이 있다. 우리가 오늘 다룰 수식에는 배열수식과 index, small 등의 함수가 사용되며 그리 간단하지는 않다. 차근차근 가 보자. 일단 Type 열만 생각해 보면, 각각 C5, C8, C11 을 가져와야 함을 알 수 있다. 예상하듯, 5, 8, 11 의 숫자를 적절히 반환할 .. 공감수 54 댓글수 33 2018. 3. 7.
[구글시트] 다른 시트로부터 내용 가져오기 Importrange 다음과 같이 a.xlsx 와 b.xlsx 두 개의 시트가 있고, b.xlsx 에서 a.xlsx 에 있는 특정 셀의 내용을 불러오고 싶다면 위와 같이 표시하려는 셀에서 = (equal) 입력 후 원본 셀을 클릭하면 된다. 엑셀이 자동으로 파일 이름과 시트 주소, 셀 위치를 파악해 적어준다. a.xlsx의 A1셀에 해당하는 내용을 잘 가져오는 것을 볼 수 있다. 만일 다른 셀들의 내용도 가져오려면 수식에서 셀 주소를 고정시키는 $ 문자를 삭제한 후 Ctrl + D 와 Ctrl + R 로 수식을 채워주면 된다. 엑셀에서는 이와 같은 작업이 비교적 수월하지만, Google Spreadsheet 에서는 같은 방식을 사용할 수 없다. 아무리 = (equal) 을 누르고 다른 시트를 클릭해 봐도, 수식을 자동으로 입.. 공감수 6 댓글수 6 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를 반환하고.. 공감수 3 댓글수 1 2018. 3. 2.
[엑셀] 중복값의 위치 찾기 다음과 같은 자료에서 놀랍게도 단 하나의 값이 중복이라고 할 때 (-_-) 중복값의 위치를 찾고 싶다면 [조건부 서식]의 [중복 값]을 이용하는 것도 방법이다. 친절하게 색깔로 알려준다. 하지만 이게 몇만 셀쯤 되면 중복값이 뭔지, 어디에 있는지 아무리 PgDn 키를 눌러도 찾기가 쉽지 않다. 이럴 땐 피벗테이블을 이용하는 것이 도움이 된다. 2016/09/23 - [R] - [R.아르] R에서 피벗테이블 사용하기 dcast()에서 예로 든 것처럼 피벗테이블은 각 항목의 개수를 파악할 때도 유용하다. 옆에 임의의 열을 만들어 열 이름을 붙이고, 아무 값이나 채워 주자. 본 예에서는 'a' 로 채웠다. 이미 데이터 옆에 값이 채워진 열이 있다면 그 열을 그대로 이용해도 된다. 값이 무엇인지는 크게 중요치 .. 공감수 0 댓글수 0 2018. 1. 26.
[엑셀] 표준편차 STDEV.P 와 STDEV.S 의 차이 표준편차란 산포도의 일종으로, 주어진 자료들이 평균에서 얼마나 흩어지는 경향성을 보이는지 판단하는 지표다. 다음과 같은 자료가 있다면 평균은 80, 표준편차는 5가 된다. 여기서 표준편차가 5라는 것은 자료 중 절반은 평균보다 5가 크고, 나머지 절반은 평균보다 5가 작다는 의미이다. STDEV.P 와 STDEV.S 는 모두 표준편차를 구하는 함수인데, 전자는 주어진 자료가 전부일 때, 후자는 주어진 자료가 일부일 때 사용한다. [이미지: 위키피디아] 위 그림에서 위쪽, 1번부터 12번까지가 우리가 알고 싶은 '전체' 라고 할 때, 이것을 모집단Population 이라고 한다. 현실에서는 모집단 전체의 자료를 모으는 것이 거의 불가능하므로 일부 자료를 모아 그 자료를 토대로 모집단의 특성을 추측한다. 위.. 공감수 25 댓글수 2 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.. 공감수 54 댓글수 13 2017. 8. 24.
[엑셀] 한 셀 내에서 특정 문자 개수 구하기 한줄요약: =(LEN(셀)-LEN(SUBSTITUTE(셀,"찾는문자","")))/LEN("찾는문자") COUNTIF()는 기본적으로 '셀의 개수'를 세는 함수이므로 한 셀 내에 반복되는 특정 문자열의 개수를 셀 수는 없다. 한 셀 내에서 특정 문자열의 개수를 세고 싶다면 의외로(?) SUBSTITUTE()함수를 사용해야한다. 다음과 같은 자료가 있다고 하자. 기본 전략은 간단하다. 2016/12/29 - [엑셀] - [엑셀] 오른쪽에서 특정 문자까지 추출하기와 마찬가지로 찾으려는 문자를 공백으로 바꾼 후 원래 문자의 길이와 차이를 보는 것이다. 예를 들어 위의 자료에서 "E"를 찾으려면 이와 같이 "E"를 공백으로 바꾼 후에 LEN() 함수를 이용하여 다음과 같이 길이를 구한다. "E"가 하나씩 사라졌으.. 공감수 37 댓글수 17 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() 등으로 둘러싸 주어야 비로.. 공감수 3 댓글수 0 2017. 8. 22.
[엑셀] 엑셀 엔터(줄바꿈) 입력하는 방법 2017/08/09 - [엑셀] - [엑셀] 실무에서 많이 사용하는 엑셀 단축키 모음에서도 언급한 바 있지만, 엑셀에서 [Enter]키를 누르면 아래 행의 셀로 넘어간다. 한 셀 내에서 강제개행, 그러니까 줄바꿈을 입력하려면 [Alt] + [Enter] 를 눌러야 한다. 셀에 입력을 마친 후에는 [Enter]나 방향키로 셀에서 벗어나 입력을 완료한다. 공감수 15 댓글수 1 2017. 8. 21.
[엑셀] 수식 내용을 그대로 표시하는 세 가지 방법 수식의 결과값이 아니라 수식 자체를 보고 싶은 경우가 있다. 예를 들어 다음과 같은 경우 C2에 쓰인 64라는 값은 무슨 값일까? 시트 전체에 이렇게 결과값만 있다면 각 값에 해당하는 수식이 궁금할 것이다. 첫 번째 방법은 단순히 수식을 복사하여 붙여 넣는 방법이다. 붙여넣을 D2 셀의 형식을 텍스트로 지정한 후 C2 셀에서 F2를 눌러 수식을 복사하고 D2에 붙여넣는다. D2에 수식 내용이 표시된다. 또 다른 방법은 단축키를 이용해 입력된 수식을 확인하는 방법이다. 다음과 같은 자료에서 C열에 쓰인 각 값에 해당하는 수식이 무엇인지 궁금하다면 [Ctrl] + [ ` ] 를 눌러보자. `(Grave) 는 보통 ESC 바로 아래 있는, 숫자 1 왼쪽 키이다. 모든 수식을 입력된 그대로 보여준다. 다시 한번.. 공감수 4 댓글수 3 2017. 8. 10.
[엑셀] 실무에서 많이 사용하는 엑셀 단축키 모음 엑셀 2010부터는 Alt 키만 누르면 메뉴의 어느 곳이든 키보드로 접근할 수 있게 되었다. 그러므로 자주 사용하는 기능이 있다면 해당 기능이 정확히 어느 메뉴에 속하는지 보고 그 메뉴에 접근하는 경로를 키보드로 암기해 두면 도움이 될 것이다. 예를 들어, 이전까지 자주 사용하였던 연속데이터 채우기 단축키 Alt + E, I, S 는 2016 메뉴 방식으로는 Alt, H, FI, S 를 차례로 누르면 된다. 홈(Home) - 채우기(FIll) - 계열(Serial) 메뉴를 차례로 선택한 것과 같다. 엑셀의 단축키 목록은 아래에서 버전별로 찾아볼 수 있다. https://support.office.com/ko-kr/article/Windows%EC%9A%A9-Excel-%EB%B0%94%EB%A1%9C-%.. 공감수 0 댓글수 0 2017. 8. 9.
[엑셀] 특정 문자열에 해당하는 값 참조하기 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번째 열을 지정했으므로 .. 공감수 1 댓글수 0 2017. 8. 8.
[엑셀] 도수분포표/히스토그램 만들기 (2016 데이터분석도구 설치하기) 아래와 같은 자료가 있다고 하자. 무슨 숫자가 얼마나 있는지 궁금해서 다음과 같은 차트를 만들었다면 별로 도움이 되지 않는다. 그냥 각 숫자가 한 개씩 있다는 정보일 뿐이다. 이럴 때 필요한 것이 도수분포표와 히스토그램이다. 도수분포표는 계급을 이용하는데, 계급은 위에 나열된 숫자들을 일정 구간으로 묶어 주는 역할을 한다. 각 숫자는 하나씩 있지만 1에서 10 사이에 위치하는 숫자는 몇 개인지, 10에서 20 사이에 위치하는 숫자는 몇개인지를 보면 숫자들의 전체적인 분포가 눈에 들어올 것이다. 본격적으로 계급을 정해 보자. 계급을 정하기 위해 주어진 자료가 어디부터 어디까지 분포하는지, 자료의 최소값(MIN)과 최대값(MAX)을 확인한다. 숫자들은 최소 0부터 최대 131까지 분포한다고 한다. 계급은 정.. 공감수 11 댓글수 8 2017. 8. 7.
[엑셀] 랜덤추출하기 RANDBETWEEN() 다음과 같은 자료가 있다고 하자. A열에 있는 값 중 하나를 랜덤으로 추출하려면 어떻게 해야할까? A1 ~ A26 셀에 있는 값 중 하나를 참조하도록 하면 될 것이다. 숫자가 바뀌므로 2017/08/02 - [엑셀] - [엑셀] 계산 결과 값으로 셀 지정하기 INDIRECT() 에 등장한 INDIRECT를 이용하면 되겠다. 셀 주소는 A(1~26중 랜덤) 의 모양이면 되겠는데, 이때 셀 주소의 숫자 부분 입력을 위해 RANDBETWEEN을 사용한다. RANDBETWEEN은 인자로 받은 두 수 사이의 정수를 무작위로 반환하는 함수다. A1 부터 A26 까지 숫자가 1 ~ 26 사이에서 바뀌어야 하므로 RANDBETWEEN 은 다음과 같이 입력한다. =RANDBETWEEN(1, 26) 이제 &를 이용해 숫자.. 공감수 6 댓글수 10 2017. 8. 3.
[엑셀] 계산 결과 값으로 셀 지정하기 INDIRECT() 어떤 계산 결과값으로 셀을 지정해 주고 싶은 경우가 있다. 예를 들어 다음과 같은 자료에서 A1에 있는 값은 "A"인데, 그로부터 17번째 아래에 있는 값은 무엇일까? 엑셀에게 1+17 번째를 계산시킬 수 있을까? 물론 암산하여 =A18과 같이 적어주면 되겠지만, 계산이 복잡해지면 일일이 계산하여 결과값을 적어줄 수는 없는 노릇이다. 그렇다고 =A(1+17)과 같이 적으면 인식될 리가 없다. 이럴 때 사용하는 함수가 INDIRECT다. INDIRECT는 무엇이 되었든 괄호 안의 최종 텍스트가 셀 주소 모양 (A1, B12, T34 등) 이기만 하면 해당 셀의 값을 반환해 준다. 그러니 먼저 계산식을 이용하여 A18에 해당하는 텍스트를 만들어야겠다. A(1+17)을 A18로 인식시키려면 앞의 문자인 A와 .. 공감수 1 댓글수 0 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 네.. 공감수 2 댓글수 0 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)이다. 예를 들어 앞선 포스트에서 .. 공감수 1 댓글수 0 2017. 7. 31.
[엑셀] 상/하위 몇 퍼센트에 해당하는 값 알아보기 percentile() 통계적으로 백분위수(Percentile)를 구하는 방법에는 여러 가지가 있다고 한다. 엑셀은 어떻게 계산하는지 알아보자. 다음과 같은 자료에서 하위 30%에 있는 값은 3이 될 것이다. 수식으로는 percentile()을 이용하여 이렇게 적는다. 말하자면 이런 셈이다. 실제로 계산하는 방법은 조금 다르지만, 쉽게, 맨 처음 숫자는 0%이므로 말하자면 '0번째 숫자'로 치고, 1부터 첫 번째 숫자로 생각한다고 보면 이해가 빠를 것이다. (정확한 계산 방법은 2017/07/31 - [엑셀] - [엑셀] PERCENTILE INC와 EXC의 차이 를 참고) 자료가 1부터 시작한다면 이야기가 좀 다르겠다. 말하자면 1이 0%이므로 0번째 숫자, 2부터 첫 번째 숫자가 되고 이때 하위 10%는 1과 2 사이 어디.. 공감수 6 댓글수 2 2017. 7. 14.

반응형

문의안내

티스토리
로그인
고객센터

티스토리는 카카오에서 사랑을 담아 만듭니다.