본문 바로가기

전체 글

 (20)
엑셀_유효성검사_istext_isnumber_숫자 또는 문자만 입력 템플릿을 만들어서 여러 사람에게 데이터 입력을 요청해야할 경우가 종종 있다. 자료를 입력하는 사람이 템플릿 만든 사람의 마음을 잘 헤아려, 원하는 유형의 데이터를 입력해주면 추가 작업이 없어 고마우련만.... 숫자만 입력하라는 곳에 꼭 텍스트를 넣어서 보내는 사람이 있다. (한국은 이런 일이 없을 수도 있겠지만...) 엑셀에 데이터 입력 시 여러 제한을 둘 수 있는 기능이 바로 데이터 유효성 검사 기능이다. 메뉴 바에서 데이터 --> 데이터 도구 --> 데이터 유효성 검사. 근데 해 보면 알겠지만, 기본 세팅되어 있는 메뉴 (예를 들어 정수)를 고르면 범위등을 지정할 수만 있지, 텍스트/ 숫자만 입력하게 하는 기능은 기본적으로 없다.. 이럴 때는 사용자 지정을 선택해 함수와 응용하는 지혜가 필요한데.....
엑셀 동점자 차례로 순위 매기기_상위 득점자 자동으로 추려내기 (배열함수) 예전에 어디선가 보고, 뭐에 쓸모가 있을까 싶어 흘렸는데... 기관의 채용 공고에 지원한 수 십명의 지원자들을 shortlisting 하기 위한 탬플릿을 만들 때... 유용하게 썼습니다. 단순 Rank 로 순위를 매기게 되면, 같은 순위의 사람들이 생겨서 (첨부 파일의) "Shortlisting template" 시트에서 상위 득점자를 자동으로 뽑기가 어렵더군요. 일단, 동점자들의 순위를 표에 나온 순서대로 다르게 매기기 위한 수식은 아래와 같습니다. (J23 셀) {=SUM((ROW($I$5:$I$64)
엑셀_자동(수식)으로 중복없이 고유값만 추출하기_배열함수원리이해 해당 포스트는 엑셀에 아직 Unique 함수가 없었을 때 작성한 것입니다. :) 배열함수의 작동원리를 이해할 수 있겠네요! 데이터에서 중복된 값을 제외하고 고유값만 나열하는 방법 중 가장 쉬운 것은 전체 데이터를 복사 후, 데이터 탭에 “중복된 항목 제거” 기능을 사용하는 것이다. 다만, 위의 방법은 데이터가 업데이트 될 때마다 반복해주어야 한다는 단점이 있다. 즉, 수동이라는 것… 수식을 잘 활용하면 이 작업을 자동화할 수 있다. 인터넷을 좀 찾아보니… row 함수에, 나누기 곱하기 하는 복잡한 수식들이 나오던데… 그냥, index, match, countif 함수만 잘 활용하면 된다. 물론 배열 함수를 좀 이해 해야 겠지만… ​ ​ 첨부 파일을 보면… B2:E10 표의 데이터에서 Department ..
조건부 서식으로 표 테두리 자동으로 그리기 첨부 파일은 이전 포스트에서 받으시고... 물품/ 구매 수량/ 사용 수량을 채우는대로, 참조 범위가 자동으로 변하는지 테스트 해보시면 될 듯... 같은 파일에 조건부 서식을 활용해서 데이터가 입력하는 대로 (즉, 표의 마지막 행에) 테두리가 자동으로 그려지도록 하였는데... 방법은 $B$9:$D$50 (더 많이 선택하셔도 됨) 선택후, 조건부 서식 클릭...Use a formula to determine which cells to format (수식 사용한 조건부 서식 설정?) 선택 후 위에 두 수식을 입력하여 각 수식이 True 일때 적용할 서식을 설정해 주었다. 1) =AND($B9>0,$B10=0): $B$9:$D$50 범위에서 현재 행에 값이 있고 (>0), 아래 행에 값이 없을 때 (=0), 밑..
Offset 함수를 이용한 동적 참조 (데이터 입력에 따라 참조 범위 자동 업데이트)표 엑셀을 사용하면 가장 많이 하는 작업이 수식 활용한 계산인데, 데이터가 업데이트 되면 보통 입력된 수식을 일일이 수정하는데… 번거롭기만 하다. 엑셀을 사용하는 이유는 반복 작업을 자동화해 내 일신이 편하고자 함인데도 말이다. 일정 데이터 범위를 참조하는 계산식에 offset 함수를 활용해 참조 범위를 자동으로 변하게 하면 어떨까? 수식을 한 번만 입력해 놓으면… 데이터가 입력되는 대로 자동으로 계산해 결과값을 도출해 주니 꽤 편하다.. 첨부 파일 재고 수량에 입력된 수식은 다음과 같다… 일견 복잡해 보이지만 뜯어보면 그렇지 않으니 미리 겁먹지 마시길… =SUMIF(OFFSET(B3,,,COUNTA(B:B)-1),F3,OFFSET(C3,,,COUNTA(B:B)-1))-SUMIF(OFFSET(B3,,,COU..
엑셀_ Index_Indirect_Small_배열함수_조건에 맞는 값 순서대로 추출하기 좀 난이도가 있는 내용이긴 한데... 아래 내용 읽어보시고, 첨부 파일에서 몇 번 연습해보시면 금방 따라하실 수 있을 겁니다. 첨부 파일을 보시면 세 사람이 각각의 날짜에 치른 시험 성적이 있다. G1 셀에 대상자를 선택하여, 그의 시험 성적을 순서대로 추출하려면 어떻게 해야 할까? ​ ​ H3 에 사용된 (배열) 수식은 다음과 같다. ​ {=INDEX($D$3:$D$14,SMALL(IF($B$3:$B$14=$G$1,ROW(INDIRECT("1:"&COUNTA($D$3:$D$14)))),F3))} Index 함수가 나왔으니, 참조된 범위 (여기서는 시험 점수 값이 들어있는 D3:D14)에서 Small 함수의 결과로 나올 n 번째 행의 값을 추출하라는 수식임을 알 수 있다. 따라서, 대상자를 Minsoo ..
엑셀 나만의 단축키 만들기_빠른 실행 도구 모음 사용자 지정 엑셀 필수 단축키들은 인터넷을 조금만 둘러봐도 쉽게 찾으실 수 있을 테고… ​ 이 포스트에서는 빠른 실행 도구 모음 사용자 지정을 통해 “나만의 단축키”를 만드는 방법을 알려드리겠습니다. 저는 컴퓨터를 바꾸고 엑셀 창을 처음 열 때 반드시 제가 자주 사용하는 단축키들을 먼저 지정해줍니다. 😊 방법은 굉장히 쉽습니다. 일단 엑셀을 열고, 왼쪽 상단에 있는 화살표 모양의 아이콘을 클릭하면 빠른 실행 도구 모음 (Quick Access Toolbar) 의 여러 메뉴가 나옵니다. 이중에 명령 추가 (More Commands…) 를 선택해주세요. ​ 그럼 아래와 같이 여러 명령어들을 선택해 오른쪽으로 옮길 수 있습니다. 처음에는아래와 같이 자주 쓰는 명령어 (Popular commands) 만 보여주는데... ​..
Text 함수 (LEFT, RIGHT, MID, FIND, SEARCH, LEN) 활용 - 프로젝트 WBS No 을 추출해보자 엑셀 작업할 때 생각 보다 많이 사용하게 되는 Text 함수들.. LEFT, RIGHT, MID, FIND, SEARCH, LEN 정도만 알면 어떤 형태의 문자를 만나도 자유자재로 떼었다 붙였다 할 수 있지 않을까 합니다. ​ 일단 각 함수의 작동 원리 부터... 그리 복잡한 함수들은 아닙니다. LEFT 는 참조 셀에서 왼쪽 부터 "원하는 글자 수" 만큼 RIGHT 는 참조 셀에서 오른쪽 부터 "원하는 글자 수" 만큼 MID 는 참조 셀에서 시작하는 지점을 정해주고 거기서부터 오른쪽으로 "원하는 글자 수" 만큼 가져와 주는 함수입니다. ​ FIND와 SEARCH 는 참조 셀에서 "찾고자 하는 값"이 왼쪽에서부터 몇 번째 위치에 있는지를 알려 주는 함수입니다. 참고로 FIND 와 SEARCH 의 차이는 아..

티스토리툴바