본문 바로가기

컴퓨터/엑셀관련

(17)
조건에 따라 배경색/글자색을 다르게 하기 엑셀에서 셀 값의 조건에 따라 배경색 혹은 글자색을 자동으로 설정하는 방법을 알아 보자. 예를 들면 다음과 같은 기능이다. 위의 경우 '연번'이라 되어 있는 A열에 있는 값이 홀수면 배경이 살구색, 짝수면 하늘색으로 변경되도록 한 경우이다. 우선 자동서식을 지정할 영역을 선택 후 새 규칙을 만든다. 그 후 각 값을 다음과 같이 설정한다. 스타일을 고전, 그 밑에는 포멧 지정을 할 것인지의 여부를 판단할 셀 값을 지정하도록 해준다. 또한 기본 포멧이 아닐 경우 위처럼 사용자 지정 포멧을 설정하자. 이제, 어떤 식으로 공식을 지정하는지 보자. 지금 총 3개의 규칙을 사용 중인데, 가장 밑에 있는 것은 중복된 값을 표현하기 위한 것이니 이 글의 내용에 해당하지 않는다. 가장 첫 번째 규칙이 짝수의 경우 배경색..
조건에 따라 글자색/배경색 등을 자동으로 지정하기 원하는 조건에 따라 글자의 색이나 배경색을 자동으로 설정하고자 할 때 conditional formatting 을 사용하면 일괄적으로 한 번에 원하는 작업을 수행할 수 있다. 다음과 같다. 우선 작업을 적용시킬 범위를 선택한다. 그 후, Home --> Style 에 있는 'Conditional Formatting'을 선택하면, 다음과 같이 어떤 조건에 따라 어떤 format 으로 할 지를 선택할 수 있다. 지금 나의 경우 0.05 보다 작은 값들을 잘보이게 하고 싶으니까 Less Than 을 선택해서 default 값이 아니라 내가 원하는 값 0.05 로 지정을 해주면 위처럼 0.05 이하의 값만이 원하는 형태로 formatting 이 된다. 비단 이런 것 뿐만이 아니라, 다음과 같이 값을 좀 더 잘 ..
피벗 테이블 피벗 테이블은, 만들고자 하는 데이터가 테이블 형태인데, 갖고 있는 데이터는 테이블의 열과 행이 될 데이터가 모두 열로 되어 있을 때 그것을 테이블로 만들 수 있는 방법을 지칭한다. 다시 말해, 하나의 열은 행, 또 다른 열은 열, 다른 열은 값, 으로 되어 있는 데이터를 테이블 형태로 재구성하는 방법을 지칭한다. 그림으로 표현해 보자면 다음과 같다. 엑셀에선 지정된 행과 열이 같은 값의 목록에 대하여 여러 함수를 지정해서 '값'으로 지정할 수 있게끔 되어 있다. 즉, (1,a) 에 해당하는 값이 10개가 있었다고 하면 그 값의 '평균'을 테이블로 만든다던가, '합계'를 테이블로 만들 수가 있게 되어 있다. 만드는 방법은 어렵지 않다. 다음과 같다. 우선 피벗 테이블로 만들 데이터를 전체 선택하고, In..
열과 행을 출력과 스크롤에서 고정시키기 print 로 출력을 할 때 매 페이지마다 특정 열을 반복하거나, 화면을 스크롤 할 때 특정 열과 행은 고정을 시키고 나머지만을 스크롤 해야 할 때가 있다. 어떻게 하는지 알아 보자. print 할 때 특정 열을 매 페이지마다 출력하기 이것은 곧 어느 열이 어느 값인지를 텍스트로 써 놓은 열, 즉 일반적으로 '헤더열'이라 말하는 열을 페이지마다 출력하는 것을 의미한다. 만약 이렇게 하지 않으면 두 번째 장 이상부터는 대체 이 컬럼(열)이 무슨 값인지를 알기 위하여 첫 페이지를 계속 확인해야 하는 불편함을 막기 위함이다. 실제로 두 경우의 출력물을 비교해 보면 다음과 같다. 위는 헤더가 출력되지 않은 그림이다. 녹색으로 된 헤더 열이 첫 페이지만 나와 있는 것을 알 수 있다. 반면, 위 그림은 모든 페이지..
초기 sheet 수 등 엑셀 초기 환경 지정하기 엑셀을 처음 시작하면 아마도 3개의 sheet가 있을 것이다. 나는 이것이 마음에 들지 않아서 보통 10개의 sheet 가 나오게 바꿔 사용하고 있는데, 이렇게 초기 시작 환경을 지정하고자 할 때는 엑셀의 전체 옵션을 바꿔 주면 된다. 다음과 같다. 엑셀 2010 의 경우 엑셀 2010의 전체 옵션은 다음과 같이 File 메뉴의 Options 에 있다. 엑셀 2007 의 경우 엑셀 2007의 경우 전체 옵션은 메인 아이콘을 누르면 펼쳐지는 메뉴의 오른쪽 아래에 있다. 참 쌩뚱맞은 위치다. 위와 같이 각 버전에서 Excel Options 를 누르면 다음과 같이 설정 화면이 나타난다. 위의 General (한글 버전은 '일반'일듯) 에서 초기 font 나 font size, 초기에 나타날 sheet 의 수 ..
엑셀에서 필터 사용하기 필터는 특정 조건을 만족하는 데이터만을 보여 주고 나머지 데이터는 숨기는 기능을 할 수 있는 방법을 제공해 준다. 필터는 사용법이 간단하면서도 매우 유용하므로 익혀 두는 것이 좋다. 필터를 거는 방법은 매우 간단하다. 필터를 걸고자 하는 열을 선택한 이후 Data -> Filter 를 누르면 된다. 위와 같이 필터를 걸면 제일 윗 칸, 즉 헤더로 인식된 칸에 다음과 같은 표시가 된다. 이제 원하는 조건에 맞는 데이터만을 보고자 할 때는 위의 저 세모가 생긴 칸을 누르면 나오는 여러 기준들 중 적절한 것을 선택하면 된다. 주어진 열이 숫자로 구성되어 있는지 텍스트로 구성되어 있는지 등에 따라 적절한 기준을 선택할 수 있게끔 되어 있는데 그 중 몇 예를 보면 다음과 같다. 만약 숫자로 구성되어 있는 열이라고..
글자 길이로 정렬하기 종종 쎌에 있는 글자의 길이로 쎌을 정렬하고 싶을 때가 있다. 특히, 칸의 폭을 글자의 폭에 맞게 조절했을 때 어느 한 칸이 유독 길어서 폭이 너무 길어질 때가 있는데, 그 때 어느 칸 때문에 그렇게 길어진 것인지 궁금할 때가 있다. 그 때 이 방법을 사용하면 된다. built-in 함수가 제공되는지는 모르겠는데, 다음과 같이 간단히 알아낼 수 있다. 위 그림과 같이 LEN 함수로 쎌에 있는 글자의 길이를 반환받는다. 그 후 내림차순 정렬을 하면 글자 수가 가장 긴 행이 제일 위쪽으로 올라오기 때문에 가장 긴 글자를 포함한 쏄을 찾을 수 있게 된다.
sumif, sumifs : 조건에 맞는 칸만 더하는 함수 주어진 조건을 만족하는 행의 특정 열의 값들을 더하고 싶다면 sumif 나 sumifs 함수를 이용하면 된다. 조건이 하나이면 sumif, 여러 조건을 동시에 만족해야 한다면 sumifs 를 사용하면 된다. 우선 sumif 부터 보면, 사용법은 다음과 같다. =sumif([조건구간], '조건', [더할 구간]) [조건구간]에서 '조건'에 부합하는 칸들을 찾아서 더한다. 만약 [더할 구간]이 지정되면 [조건구간] 중 '조건'에 맞는 행에서 [더할구간]의 열을 찾아서 더하게 된다. 위의 경우처럼, 만약 '조건'을 30 이하로 하면, 조건에 맞는 칸에 해당하는 행의 더할 구간의 값을 찾아서 더하게 되므로, 더해지는 값은 붉은 색으로 쓰인 값들이다. 실제 사용 예를 보면 다음과 같다. 실제 사용된 수식은 다음과..