Excel
-
[Excel] 데이터 처리와 자동 변환 방지Excel 2020. 10. 29. 01:12
연구실의 유전학자들이 데이터를 처리하는데, 엑셀에서 예를들어 'MARCH3'이나 'OCT1' 같은 데이터들을 자동으로 날짜로 변경했다고 한다. 그로 인해 유전학 관련 논문 3,597건을 분석한 결과, 잘못 입력된 데이터 이름들이 약 20%라고 한다. 나도 데이터 처리를 위해 엑셀을 공부하는 중이다. 데이터를 처리하는 중 자동으로 데이터가 바뀐다면 커다란 문제가 생길 것이다. 엑셀에서 데이터 입력시 자동 변환을 막는 법 1. 데이터 앞에 스페이스바를 입력하는 법 데이터 앞에 스페이스바를 넣으면 한 칸 공백이 생겨서 선호하지 않은 사람도 있다. 2. 셀 서식을 텍스트로 변경하는 법 3. 작은 따옴표(')를 사용하는 법 문제점 데이터 처리를 할 때 CSV 파일을 많이 이용한다. CSV(Comma Separat..
-
[Excel] 사용자 지정 목록 편집Excel 2020. 10. 28. 23:36
엑셀에서 '1, 2, 3, 4', '가, 나, 다, 라' 또는 날짜별로 정렬은 쉽게 된다. 하지만 '하나, 둘, 셋, 넷, 다섯'처럼 한글 순서는 어떻게 정렬될까? 오름차순이나 내림차순으로는 순서대로 정렬되지 않는다. 이럴 때 '사용자 지정 목록 편집'을 통해 마음대로 정렬을 추가할 수 있다. 사용자 지정 목록 편집 '파일 -> 옵션 -> 고급 -> 사용자 지정 목록 편집'을 선택한다. '새 목록'에 '하나, 둘, 셋, 넷, 다섯'을 추가한다. 이후 정렬을 클릭한 후 사용자 지정 목록을 선택한다. 방금 추가한 '하나, 둘, 셋, 넷, 다섯'을 선택한다. 올바르게 정렬된 모습을 볼 수 있다. 실제 응용 군대 계급을 정렬하려고 하는데, 기본으로 정렬하면 올바르게 정렬되지 않는다. 이렇게 순서대로 계급을 추가..
-
[Excel] 필터 단축키와 고급 필터Excel 2020. 10. 28. 12:49
필터의 단축키 필터 단축키 Ctrl + Shift + L로 빠르게 필터를 걸 수 있다. 아래 화살표 단축키 Alt + 아래 화살표로 빠르게 선택할 수 있다. 필터 검색 탄축키 E키를 눌러서 바로 검색 칸으로 갈 수 있다. 고급 필터 만약 날짜가 28일인 데이터만 보고 싶다면 필터를 하나하나 열어서 28일을 체크해주어야하는 번거로움이 있다. '고급 필터' 기능을 이용하면 세밀한 조건을 더 쉽게 걸 수 있다. 먼저 다른 시트에 찾고자 하는 날짜를 모두 입력한다. 우리는 매월 28일인 데이터가 필요하므로 28일인 날짜를 전부 입력한다. 그 후 '데이터 -> 고급'을 선택하여 고급 필터 기능을 사용한다. '목록 범위'에 전체 데이터를 지정한다. '조건 범위'에 입력해 놓은 날짜 범위를 지정한다. 그렇게 하면 날..
-
[Excel] 찾기와 바꾸기Excel 2020. 10. 28. 11:38
찾기 대문자 알파벳 I(아이)와 소문자 알파벳 l(엘)은 비슷하게 생겨서 구분하기 어렵다. 다음 중 알파벳 l(엘)을 '찾기' 기능을 이용하여 쉽게 찾을 수 있다. Ctrl + F를 눌러서 '찾기' 창을 연 뒤, 알파벳 l을 입력하고 '다음 찾기'를 누르면 알파벳 l을 찾을 수 있다. '모두 찾기'를 누르면 여러 개의 l이 존재하는지, 어디에 존재하는지 모두 찾을 수 있다. 이때, 맨 위의 셀인 '알파벳 l(엘)을 찾아라' 라는 셀도 같이 찾아졌다. 만약 l(엘)만 존재하는 셀을 검색하고 싶다면 '옵션'을 누른 뒤 '전체 셀 내용 일치' 옵션을 선택한 후 다시 '모두 찾기'를 누르면 된다. 모두 찾은 결과들을 Shitf + 클릭으로 전체 선택이 가능하고, 셀에 색을 넣는 행위 등을 전체 선택된 셀에 한번..
-
[Excel] 효율적인 Rawdata 생성 규칙Excel 2020. 10. 27. 19:23
효율적인 Rawdata 생성 규칙 하나의 셀에는 반드시 하나의 값만 입력해야 한다. 데이터는 행으로 쌓아야 한다. 분석 목적에 맞게 데이터를 분리해야 한다. 날짜를 '진짜 날짜'로 분리해야 한다. 1. 하나의 셀에는 반드시 하나의 값만 입력해야 한다. 셀 안에 여러 개의 줄로 데이터를 입력하는 경우가 있다. 다음과 같이 하나의 셀 당 하나의 데이터만 들어가게 입력하는 것이 데이터를 분석하는데 좋다. 2. 데이터는 행으로 쌓아야 한다. 데이터를 열로 늘려나가는 것 보다 행으로 늘려나가는 것이 좋다. 3. 분석 목적에 맞게 데이터를 분리해야 한다. 지역 속성에 '서울 종로구'로 데이터가 입력되면 지역이 '서울'인 데이터만 보고 싶을 때, 지역이 '종로구'인 데이터만 보고 싶을 때 어려운 점이 발생한다. '서..
-
[Excel] 필터 기능 조심해야할 점Excel 2020. 10. 26. 12:58
필터 '데이터 -> 필터' 기능을 이용하여 데이터를 원하는 속성별로 볼 수 있다. 게임이 Starcraft인 데이터만 보길 원하면 게임 속성에서 Starcraft만 선택하면 된다. 선택한 속성 이외의 행은 숨겨지게 된다. Sheet2에 '이름'을 복사, 붙여넣기 하면 중간에 행이 숨겨져 있어도 붙여넣기가 잘 실행되는 것을 볼 수 있다. 주의점 하지만 아래와 같이 연속된 값을 필터가 적용된 데이터들에 붙여넣기 하고 싶을 때 문제가 발생한다. 4개의 행을 붙여넣기 했지만, 1개의 행만 바뀌어있다. 숨겨진 행을 모두 보게 되면 왜 이렇게 되었는 지 알 수 있다. 바꾸고자 하는 3, 10, 12, 15 행이 아닌 연속된 3, 4, 5, 6 행이 바뀌어 있다. 이런 오류는 치명적인 결과로 이어질 수 있다. 해결법..
-
[Excel] 한 셀에 여러 줄의 데이터 처리하기(Feat.SUBSTITUTE)Excel 2020. 10. 26. 10:53
한 셀에서 여러줄의 데이터 처리하기 엑셀에서 한 셀에 여러 줄을 넣고 싶다면 Alt + Enter를 입력하여 줄 바꿈 해주면 된다. 엑셀에서 줄 바꿈 문자는 char(10)으로 표현이 가능하다. SUBSTITUTE 함수와 char(10)을 이용하여 여러 줄의 데이터를 한 줄로 출력할 수 있다. SUBSTITUTE 함수 Text : 데이터를 수정할 영역이다. Old_text : 기존의 바꾸고 싶은 단어를 입력한다. 여기서는 줄 바꿈 문자를 선택하여 바꿔주려고 한다. New_text : 새로 넣어줄 단어를 입력한다. 이로 인해 얻은 수식을 '값으로 붙여넣기' 하여 문자열을 얻는다. 여러 열로 분리하기 이 문자열을 '데이터 -> 텍스트 나누기' 기능으로 여러 열의 데이터로 나눌 수 있다. "/" 문자로 구분하..
-
[Excel] 엑셀 단축키 TOP 10Excel 2020. 10. 24. 17:43
1. F2(수식 편집) 더블 클릭을 이용하여 수정을 할 수 있지만, F2를 누르면 키보드에서 손을 떼지 않고 편하게 수식을 편집할 수 있다. 지금까지 계속 더블 클릭으로 수정했는데, 이 기능 너무 편하다! 2. F4(반복하기) 글자 색 입히기, 셀 색깔 정하기 등 이전 행동을 반복할 수 있다. 3. F9(수식 단계 계산하기) 중간 중간의 단계를 볼 수 있다. 일 때, E12가 "A"인지 확인하고 싶으면 E12="A"를 드래그하여 F9를 눌러서 확인할 수 있다. 3 * 6 도 마찬가지로 F9를 이용하여 결과를 확인할 수 있다. 4. Ctrl + 1(셀 서식) 오른쪽 클릭으로 셀 서식을 선택하지 않아도 Ctrl + 1을 이용하여 셀 서식을 열 수 있다. 5. Ctrl + 화살표(값이 있는 곳의 끝까지 이동한..
-
[Excel]SUMPRODUCT 함수 (다중IF 대체하기)Excel 2020. 10. 24. 16:10
SUMPRODUCT 함수 위와 같은 예제에서 매출액을 구하기 위해서 PRODUCT 함수를 이용하여 곱한 뒤, 총 매출액을 구하기 위해 SUM 함수를 이용하여 매출액들을 다 더했다. SUMPRODUCT 함수를 이용하면 총 매출액을 쉽게 구할 수 있다. 각 Array 칸에 속성들의 범위를 넣어준다. 속성끼리 곱하여, 그 결과를 다 더해서 총합을 출력해주는 함수이다. 총 매출액이 계산 된 것을 알 수 있다. SUMPRODUCT의 활용 SUMPRODUCT 함수를 응용하여 다중 IF문을 간단하게 해결 할 수 있다. 지역이 서울인 행의 개수를 구하려면 어떻게 해야할까? =SUMPRODUCT((C5:C11="서울"))을 입력하면 결과값으로 0이 나온다. =SUMPRODUCT((C5:C11=""서울) * 1)을 입력하..