본문 바로가기
반응형

엑셀61

[엑셀]대분류 선택시 소분류가 달라지는 종속적(반응형) 드랍다운 메뉴 만들기 엑셀에서 드랍다운 목록은 [데이터] > [데이터 유효성 검사] 메뉴에서 만들 수 있다. 오늘은 대분류의 목록 중 하나의 항목을 선택하면, 선택항목에 따라 소분류의 드랍다운 목록이 달라지는 기능을 구현해 보자. 일단은 대분류와 소분류에 사용할 목록이 있어야겠다. 사소한 팁이지만, 목록은 오름차순으로 정렬해 두는 편이 나중에 사용하기에 편리하다. 우리는 이 각각의 목록에 이름을 지정해 줄 것이다. {과일, 동물, 전자제품} 의 목록은 [대분류] 라는 이름으로 {사과, 수박, 자두, 포도}의 목록은 [과일] 이라는 이름으로 지정해 주는 식이다. 이름 지정은 [수식] > [이름 정의] 메뉴에서 할 수 있는데 우리처럼 표로 잘 정리해 놓은 경우에는 그냥 '각 목록의 첫 행을 이름으로 지정' 해 달라고 할 수도 있.. 2020. 1. 15.
[엑셀] 필터 기능 활용하기 필터는 주어진 데이터를 의도대로 다루는 데 매우 유용한 도구다. 필터의 기능은 크게 두 가지인데, 첫 번째는 정렬. 두 번째는 선별(필터링)이다. 다음과 같은 자료가 있다고 하자. 1행의 No. data1, data2, data3 가 제목행이다. 먼저 제목행에 범위를 잡고 [데이터] - [필터] 를 클릭한다. 혹은 [홈] - [정렬 및 필터] - [필터]를 클릭해도 된다. 제목 행에 필터가 적용되었다. 이제 제목행의 역삼각형 모양 단추를 누르면 해당 열을 기준으로 데이터를 정렬하거나 선별할 수 있다. data1 기준 오름차순 정렬 No. 기준 오름차순 정렬 + data3 의 값이 O 인 것만 선별 필터가 실제로 적용된 경우 각 단추의 모양이 다음과 같이 바뀌어 적용된 필터를 알려준다. ↑ : 오름차순으로.. 2018. 12. 6.
[엑셀] 숨겨진 행/열을 다시 보이게 하는 세 가지 방법 엑셀을 사용하다 보면 숨겨진 행/열을 찾아야 하는 경우가 종종 있다. 1. 진짜로 숨겨진 경우 예를 들어 다음과 같은 시트에서는 2행부터 11행까지가 숨겨져 있을 터이다. 이 경우 숨겨진 행의 위 아래행을 범위로 잡고 선택된 범위 아무 곳에서나 마우스 오른쪽 버튼을 눌러 [숨기기 취소]를 눌러주면 숨겨진 행이 나타난다. 2. 크기를 작게 해 놓은 경우 위 방법대로 [숨기기 취소]를 눌렀는데도 숨겨진 행이 나오지 않는 경우에는 행들의 높이 (열의 경우 너비) 가 작게 설정되어 있을 수도 있다. 확인을 위해 1행과 12행 사이 조금 두꺼운 부분에서 마우스 오른쪽 클릭 - [행 높이]를 확인해 보면 행 높이가 0.25로 매우 작게 설정되어 있는 것을 알 수 있다. 이 경우 1번과 마찬가지로 위 아래 행까지 범.. 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라는 글자가 들어 있는 경우는 해당 글자의 위치를 숫자로 출력하고, 없는 경우에는 에러를 출력한다. 이제 이것.. 2018. 10. 16.
[엑셀] 인터넷에서 다운받은 파일의 '제한된 보기' 해결 방법 인터넷에서 엑셀 파일을 다운로드 받고 여는 경우 다음과 같은 경고 메시지가 뜰 때가 있다. 이때 '편집 사용' 버튼을 눌러 줘야 정상적으로 사용이 가능한데, 매번 이렇게 하는 것은 번거로운 일이다. 해당 경고 메시지가 나오지 않도록 설정해 보자. 먼저 [파일]을 클릭하고 가장 아래의 [옵션]을 클릭한다. 이제 [보안센터]를 클릭하고 [제한된 보기] - [인터넷에서 가져온 파일에 대해 제한된 보기 사용] 항목의 체크를 해제해 준다. 이제 인터넷에서 다운로드 받은 파일도 경고 메시지 없이 바로 편집이 가능하다. 단, 경고메시지의 내용처럼 다운로드 받은 파일은 위험할 수도 있으므로 본인의 판단 하에 적절히 사용하자. 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.. 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 입력 ('두 번째 값' 이라고 .. 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인 값들은 다음과 같이 구할 수 있다. (배열수식의 값을 직.. 2018. 7. 10.
[엑셀] 지난 달, 다음 달 입력하기 EDATE() EOMONTH() 가끔 지난 달, 다음 달, 두 달 전, 세 달 전과 같이 날짜 중 월 부분만 필요할 때가 있다. 그럴 땐 EDATE() 나 EOMONTH() 함수를 사용하면 편리하다. 두 함수 모두 두 번째 인자로 변화할 개월 수를 받는데, 한달 전은 -1, 한달 후는 1 과 같이 입력하면 된다. A5에 TODAY()를 입력하고, 위로 EDATE(셀, -1)을 입력한 결과는 다음과 같다. 한달 전의 같은 날짜를 반환해 주는 것을 알 수 있다. 두달 후의 같은 날짜는 EDATE(셀, 2) 와 같이 입력한다. 월에 해당하는 숫자만 필요하다면 범위를 잡고 Ctrl + 1 을 눌러 셀 서식에 들어간 후 (참고: 2016/09/22 - [엑셀] - [엑셀] 셀 내용에 큰따옴표 넣기 (표시 형식)) 사용자 지정에서 M"월" 혹은 .. 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 .. 2018. 6. 13.
반응형

티스토리툴바