나도 몰라서 올려보는 블로그

안녕하세요.

오늘은 틀고정에 대해서 알아보도록 합시다.

틀고정이란 엑셀의 최상단 행 또는 좌측 열을 고정시켜 엑셀 작업을 할때 타이틀 및 카테고리를 고정시켜 작업을 좀 더 효율적으로 할 수 있습니다.

[보기탭]으로 들어가면 틀고정이 있습니다.

틀 고정 옵션은 틀 고정, 첫 행 고정, 첫 열 고정이 있습니다.

첫 행 고정과 첫 열 고정은 사용자 편의성을 위해 최 상단, 최 좌측을 클릭한번으로 사용할 수 있도록 설정된 옵션입니다.

첫 행 고정 부터 클릭해보도록 합시다.

첫행 고정을 클릭하게 되면 첫 행에 검정선이 생기는것을 확인할 수 있습니다.

검정색 선이 셀 밖으로 삐져나오면 틀 고정이 제대로 설정 되었다는 것을 알 수 있습니다.

여기서 스크롤를 내려봅시다.

스크롤을 내리게 되면 첫 행은 고정는 것을 알 수 있습니다.

왼쪽 행번호를 보면 1행 다음 5행이 나타나는것을 확인할 수 있습니다.

이제 첫 열 고정을 해봅시다.

첫 열고정을 클릭하게되면 A열쪽에 실선이 생기는것을 확인할 수 있습니다.

첫 행고정과 마찬가지로 오른쪽으로 횡스크롤을 해봅시다.

오른쪽으로 횡스크롤을 하게 되면 A열이 고정되어 있는것이 확인됩니다.

A열 다음 B열을 건너뛰고 C열이 보이는것을 알 수 있습니다.

이처럼 데이터가 많아질 경우 어느순간 타이틀이나 카테고리가 안보이는 순간이 찾아오게 되는데요.

이럴때 틀고정을 이용하여 상황에 맞게 사용하시면 됩니다.

위에 설명은 첫행과 첫열에 대한 고정에 대해 설명했는데요.

그러면 열과 행을 동시에 고정시키거나, 두번째, 세번째행이나 열을 고정시키고자 할땐 틀 고정을 사용하면 됩니다.

셀 포인터를 C3(C열과 3열)에 두고 틀 고정 버튼을 눌러봅시다.

C3에 셀포인터를 두고 틀고정을 클릭하면 행,열고정이 되는 검정선이 가로세로로 그어지게 됩니다.

이렇게 되면 두번째 열과 행에 대한 틀 고정이 설정되게 됩니다.

※ 알잘딱깔센하게 틀 고정 사용해보기

알잘딱이란 알아서 잘 딱 깔끔하고 센스있게 라는 뜻입니다. ㅎㅎ

그냥 혹시나 이글을 보는 연령대가 어떻게 되는지 모르기 때문에 설명해봤습니다. 신조어 하나 배워가세요

근대 틀고정이라는게 딱히 어떤 기술이나 센스가 필요한게 아니기 때문에

간단하게 데이터가 많아져 수직스크롤을 많이 내려야한다 할때는 행 고정

입력할 항목들이 많아져 횡스크롤을 많이 하게 된다면 열 고정을 하면 되겠습니다.

이상으로 틀고정에 대해 알아보았습니다.

감사합니다.

안녕하세요.오늘은 엑셀기능인 매크로에 대해 알아보도록 합시다.

매크로는 반복작업이 있을때 효율적으로 사용할 수 있습니다.

그럼 알아보도록 합시다.

매크로 위치는 보기탭 - 맨 우측 매크로에 있습니다.

매크로를 클릭하여 기록을 해봅시다.

매크로 기록을 누르게되면 옵션창이 나타납니다.

· 매크로이름

· 바로가기키

· 매크로저장위치

· 설명

으로 구분되어 있는걸 확인할 수 있습니다.

매크로 이름은 한눈에 확인 가능하도록 명시적으로 짓는게 좋습니다.

그리고 설명란에 어떤매크로인지, 언제사용할껀지, 어떻게 사용하는건지를 상세하게 적어 놓으면 구분하기 좀더 편리하겠죠?

바로가기키는 소문자와 대문자로 설정 가능합니다.

저는 보통 대문자로 많이 사용하는데요, 그 이유는 Ctrl키에 묶여있는 window 단축키가 많기때문에 혼동되지 않게 하기 위해서입니다. 

매크로 기록을 위해 확인을 눌러봅시다.

기록을 누르게되면 매크로 중지버튼이 나오게됩니다.

매크로탭에서 중지하거나, 좌측 하단에 중지 아이콘을 클릭하여 중지하면 됩니다.

매크로 기록을 시작하면 내가 설정한 것들이 순차적으로 모두 기록되기 때문에 천천히 기록하셔야 합니다.

이제 홈으로 가서 배경색을 바꿔봅니다.

그리고 홈탭으로 들어가서 셀 배경색을 변경해준뒤 매크로 중지버튼을 눌러 매크로를 중지시킵니다.

그리고 아래 셀에 매크로를 적용시켜봅시다.

방금 만들었던 매크로를 실행버튼을 클릭하거나 이전에 등록했던 단축키를 사용하여 적용시켜봅니다.

만약 이전에 단축키를 등록 못했다면 옵션버튼을 클릭하여 다시 등록해줍니다.

매크로가 실행되면 선택한 셀의 배경색이 바뀌는걸 보실수 있습니다.

매크로 편집버튼을 클릭해봅시다.

매크로 편집버튼을 누르면 위 이미지와 같이 매크로에 대한 VBA 코드가 나오게됩니다.

코딩을 통해 좀더 디테일한 매크로를 사용하실수 있습니다.

차후에 VBA에 대한 글도 올려보도록 하겠습니다.

※ 매크로 사용전 반복패턴 생각하기

지금은 간단한 배경색만 바꾸는 매크로였지만, 어떤 특정한 셀에는 글꼴, 글자크기, 글자색 등 바꿔줘야하는 상황이 생기기 마련입니다. 이런 반복작업이 생길때면 어떤 상황에서 어떤 매크로를 사용해야 효율적인지 판단하는게 가장 중요합니다.

위의 예시처럼 간단하게 배경색만 바꾸는거라도 어떤 특정셀에 노랑, 빨강, 파랑이 들어가야 된다면 매크로 단축키에 노랑, 빨강, 파랑을 등록하고 상황에 맞게 사용하시면 됩니다.

혹은 

[제목] 매크로라는 이름으로 글꼴크기 : 30, 글꼴 : 궁서체, 굵게 등 설정

[강조] 매크로라는 이름으로 글꼴크기 : 15, 글꼴 : 나눔고딕, 기울게, 밑줄, 빨간배경색 등 설정

매크로를 여러개 만들어 필요에 따라 불러올 수도 있습니다.

이처럼 매크로에 사용에 대한 효율성은 엑셀의 숙련도나 반복패턴을 어떻게 분석하냐에 따라 좌지우지됩니다.

이상 매크로 사용법에 대해서 알아보았습니다.

궁금하거나 어떻게 엑셀을 사용해야할지 모르는 부분이 있을경우 댓글을 달아주세요

시간이 될때 참고하여 글을 올려보겠습니다.

감사합니다.

안녕하세요.

오늘은 엑셀 기초 필터 사용법에 대해서 알아보도록 합시다.

필터를 사용하는 이유는 방대한 데이터를 사용하고자 하는 입장에서 원하는 데이터를 일일이 확인하기 어려울때가 반드시 생기게 됩니다.

그럴때 단순필터, 다중필터, 조건필터를 이용하여 원하는 데이터를 얻고자 할때 사용합니다.

아래 예시를 통해 함께 배워보도록 합시다.

필터기능을 사용하기전 이름, 부서, 직급, 지역, 나이, 근무여부가 입력된 데이터를 만들어 봤습니다.

단순 예시 데이터이기 때문에 10건도 되지 않아 육안으로 확인할 수 있지만 데이터가 많아진다면 더욱 육안으로 확인하기 어려워질때가 찾아옵니다.

필터위치는 홈-편집탭에서 정렬및 필터를 클릭하게되면 나옵니다.

필터는 한 시트에 한개만 적용되니 착오없이 사용하시기 바립니다.

드래그 하지 않아도 필터가 적용되지만 열셀에 공백이 있을경우 필터 적용이 잘 되지 않을수 있으니 사용하고자 하는 범위를 드래그 후 필터적용하는 것을 지향합니다.

필터를 적용하게 되면 해당 셀 맨위부분에 아래화살표로 된 아이콘이 표시되게됩니다.

아래 화살표 아이콘을 클릭하게 되면 필터링 옵션이 나옵니다.

개발팀을 필터링 해봅시다.

개발팀을 제외한 부서를 체크 해제한 뒤 확인버튼을 클릭하게되면 개발팀이 필터링이 됩니다.

홍길동과 임꺽정이 개발팀으로 확인되었네요.

하지만 이러한 단순 필터를 통한 필터링은 체크박스에 있는 데이터에 의존되어 있어 디테일한 필터링을 하기 어렵습니다.

나이같은 경우가 디테일한 필터링이 필요한 경우겠는데요.

이러한 경우 조건 필터를 통해 필터링을 하면 되겠습니다.

다시한번 아래화살표 아이콘을 눌러 위 예시와 같이 숫자필터(F)에 마우스를 갖다 대면 여러 조건들이 나옵니다.

30살이 넘는 직원들에 대한 필터링을 해봅시다.

보다 큼을 클릭하면 사용자 지정 자동 필터 창이 나옵니다.

이전에 배웠던 >, >= <, <=, = 기호가 나오는걸 확인할 수 있네요 ㅎㅎ

보다 큼을 선택했으니 > 기호가 자동으로 선택되는걸 확인할 수 있습니다.

>와 30을 입력하고 확인버튼을 누르면 30보다 큰 직원들이 필터링 된걸 확인 할수 있습니다.

이러한 조건필터는 숫자만 해당되는것이 아니라 다른 필드에도 적용이 가능합니다.

이름 필드 필터 옵션에서 텍스트 필터를 확인하면 같음, 같지않음, 포함 등 다양한 옵션이 있습니다

여기서 포함을 클립해봅시다.

텍스트 포함을 클릭하면 사용자 지정 자동 필터가 나옵니다.

김을 포함하는 직원에 대한 필터링을 걸어보면 김에 대한 필터링이 되는걸 확인할 수 있습니다.

포함을 선택하면 [나김김]이라는 직원이 있을때 나김김직원도 표시가 되지만

김씨 성을 갖는 직원을 필터링 하고자 한다면 텍스트 필터 옵션에서 시작문자를 선택후 김을 입력하면 김씨 성을 갖는 직원이 필터링 되겠죠?

또, 여러 필터를 적용하고자 한다면 해당 필드의 필터를 적용해주면 됩니다 ㅎㅎ

마지막으로 필터링을 해제하는 방법입니다.

필터가 적용되어있는 필드의 필터 옵션에서 필터 해제를 클릭하거나, 단순필터일경우 모두선택을 해주면 됩니다.

이상으로 엑셀 필터 사용법에 대한 알아보았습니다.

감사합니다.

안녕하세요.

IF함수 심화과정으로 IFERROR를 사용해보는 시간을 갖도록 합시다.

먼저 IFERROR은

수식에서 오류가 발생할 경우 사용자가 지정한 값을 반환하고, 그렇지 않으면 수식 결과를 반환합니다. IFERROR 함수를 사용하면 수식의 오류를 트래핑하여 처리할 수 있습니다.

무슨말인지 잘 모르시겠죠? ㅎㅎㅎ

예제를 바로 보도록 합시다.

1/0의 답은 무엇일까요?

그렇죠 말이 안되는 수식입니다.

그럼 결과는 어떻게 나올까요?

당연히 에러로 표시됩니다.

#DIV/0! 의 원인으로는 

• 0으로 나누는 명시적 나눗셈(예: =5/0)을 수행하는 수식이 입력되어 있습니다. 
• 수식 또는 나눗셈을 수행하는 함수에서 0이 포함된 셀이나 빈 셀에 대한 참조를 나누는 수로 사용합니다.
• #DIV/0! 오류를 반환하는 수식이나 함수를 사용하는 매크로를 실행합니다.

가 있습니다.

첫번째 원인으로 오류를 만들어냈습니다.

솔직히 그냥 냅둬도 상관은 없지만 이상하잖아요?

이럴때 IFERROR을 사용하면 됩니다.

IFERROR사용법은

IFERROR(value, value_if_error)

입니다.

인수를 각각설명드리면

IFERROR( 값, 오류발생시 출력값 )

입니다.

글로만 쓰면 이해하기 어려우니까 위의 예제에서 IFERROR를 적용시켜봅시다.

IFERROR을 적용시켜봤는데요.

#DIV/0! 에러가 발생하면 "0으로 숫자를 나눌수 없습니다." 라는 문구가 출력됩니다.

간단하죠?

하지만 주의해야할 사항이 한가지 있는데요

IFERROR함수는 #DIV/0! 에러 뿐만 아니라 다른 에러들도 포함됩니다.

기초(7)의 예제파일로 오류를 만들어보겠습니다.

링크 :  엑셀 사용법 : 기초 (7) - VLOOKUP과 HLOOKUP을 사용하여 표 검색하기

위의 예제에서 VLOOKUP이 포함된 행을 밑으로 복사하게 되면 에러가 발생합니다.

이렇게 되면 큰일난것 같고 모양도 이상하고 그렇잖아요?

나는 그냥 함수들을 미리 복사해서 사원번호, 일렬번호, 수량만 입력하면 자동으로 계산되게 만들고싶은데 함수를 복사하니 오류가 납니다.

이럴때 IFERROR함수의 오류발생시 출력값에 공백을 넣어줍니다.

=IFERROR(VLOOKUP(D19,$C$3:$E$10,2,FALSE), "")

공백을 넣어주면 에러발생시 공백으로 출력됩니다.

확실히 IFERROR함수를 적용한 셀이 더 깔끔하지 않은가요?

이런식으로 상황에 맞게 사용하시면 됩니다. :Db

안녕하세요 오늘 배워볼 함수는

VLOOKUP과 HLOOUP입니다.

위의 함수를 사용할 때는 표 또는 행별로 범위에서 항목을 찾아야 할 때는 VLOOKUP을 사용 합니다. 예를 들어 부품 번호를 기준으로 자동차 부품의 가격을 조회 하거나 직원 ID를 기준으로 직원 이름을 찾습니다.

=VLOOKUP(1, $B$4:$D$9, 2, FALSE)

사원번호 1에 해당하는 직원이름을 찾았습니다.

인수별 설명을 드리자면

=VLOOKUP( 찾을값, 배열, 찾을 열번호, [TRUE:유사일치, FALSE:정확하게일치] )

입니다.

선택한 범위의 첫번째 열에서 값 1에 해당하는 행의 두번째 열의 값을 찾겠다.

라는 말이 됩니다.

그래서 위의 사진을 보면 사원번호 1에 해당하는 행의 2번째 열값이 런빈인걸 알 수 있습니다.

찾을값을 직접 입력할 수도 있지만

셀을 참조하여 검색할 수도 있습니다.

위의 수식과 바뀐점은 첫번째 인수가 직접입력방식이 아닌 셀참조방식으로 바뀐점입니다.

=VLOOKUP( F6, $B$4:$D$9, 2, FALSE)

해당 사원번호의 부서를 찾고싶다면 어떻게 해야할까요?

찾을 열번호 3으로 바꿔 부서를 찾을수 있도록 해주면 됩니다. :)

HLOOKUP은 어떻게 사용할까요?

VLOOKUP이 열을 찾는거라면 HLOOKUP은 행을 찾습니다.

위의 사진의 배열을 옆으로 눕였다고 생각하시면 됩니다.

사용법은 VLOOKUP과 동일합니다.

어렵지 않습니다.

따라해보시면 쉽게 하실수 있어요 ㅎㅎ

위 예제들만 보면 이 함수를 실제로는 어떻게 사용할까? 라고 생각하시잖아요.

그래서 간단하게 만들어봤습니다.

[실생활에서 필요한 예제]

갑자기 뭔가 많이졌다구요?

기분탓일겁니다 ㅎㅎㅎ

예제파일 다운받으셔서 직접 해보는게 도움됩니다.

시트1 -> VLOOKUP

시트2 -> HLOOKUP

시트3 -> 예제

로 구성되있습니다.

이상 VLOOUP, HLOOKUP 사용법이였습니다.

오늘은 COUNT 함수 심화과정에 대한 포스팅입니다.

COUNT 함수 인수 목록에서 숫자의 개수를 반환합니다.

COUNTA 함수 인수 목록에서 값의 개수를 반환합니다.

COUNTBLANK 함수 범위 내에서 비어 있는 셀의 개수를 반환합니다.

COUNTIF 함수 범위 내에서 주어진 조건을 만족하는 셀의 개수를 반환합니다.

COUNTIFS 함수 범위 내에서 여러 조건을 만족하는 셀의 개수를 반환합니다.

다양한 COUNT 함수들이 존재하는데요

예제를 통해 한개씩 배워봅시다.

COUNT 함수는 숫자만 셋다면 COUNTA 함수는 값이 있는 셀의 갯수를 셉니다.

공백이 아닌 모든 값들을 세기때문에 스페이스바가 입력된 셀도 문자로 인식하기 때문에 갯수에 포함이 됩니다.

사용방법은 COUNT 함수과 동일합니다.

=COUNTA( 범위 )

COUNTBLANK 함수는 값이 없는 공백인 셀의 갯수만 셉니다.

사용방법은 COUNT 함수와 동일합니다.

=COUNTBLANK( 범위 )

COUNTIF 함수는 해당 범위에서 찾고자하는 조건값과 일치하는 값을 찾습니다.

위 예제처럼 애완동물 종류중 개를 기르는 사람의 수를 세고자 할때 유용하게 쓰일 수 있습니다.

사용법은 SUMIF와 비슷합니다.

=COUNTIF(범위, 찾을값)

위 예제처럼 셀을 참조하여 찾을 수도 있습니다.

=COUNTIF(D5:D11, "개") 와 같은 의미라고 생각하시면 될것 같습니다.

하지만 셀을 참조하여 함수를 사용할때는 주의해야할 점이 있는데요,

D5셀의 값이 다른 값으로 바뀌게 된다면 D5에 입력된 값으로 다시 찾게됩니다.

위 예제처럼 나는 "개"를 찾고싶지만 나버터라는 사람의 애완동물을 잘못 조사하여 애완동물의 이름이 바뀐다면

=COUNTIF(D5:D11, "사자") 로 바뀌게 되므로 내가 찾고자 하던 값과 다를 수 있게됩니다.

이처럼 셀참조를 사용할 때에는 바뀌지 않을 값으로 설정하거나

찾을 값을 따로 빼놓는 방법이 있습니다.

위 예제처럼 사용한다면 큰 장점으로는 다른 동물을 찾고싶을때

H3셀의 값만 변경하면 쉽게 다른 값을 찾을 수 있습니다.

COUNTIFS 는 여러 조건을 만족하는 값의 갯수를 구하는 함수입니다.

예제에서 보듯이 서울에서 개를 기르는 수를 찾고자 할때 사용했습니다.

=COUNTIFS(범위, 찾을값, [범위, 찾을값]);

[ ]는 선택요소이므로 찾을 조건이 추가로 입력하면 됩니다.

=COUNTIFS(C4:C11, "서울")  -> 서울의 갯수를 구합니다.

=COUNTIFS(C4:C11, "서울", D4:D11, "개") -> 서울에서 개를 기르는 수

만약 추가로 조건을 설정했다면 최초범위의 크기와 같게 잡아야 오류가 나지 않습니다.

[와일드카드 문자]

와일드카드 문자인 물음표(?), 별표(*), 물결표(~)를 사용할 수 있는데요

?표는 단어 한 문자에 해당합니다.

찾을값에 "?" 를 입력하게되면 1글자인 애완동물을 모두 찾습니다.

"고??"를 입력한다면 "고"로 시작하는 3글자 애완동물을 찾습니다.

*는 모든 글자를 찾는걸 의미합니다.

"*"을 입력한다면 모든 글자를 찾게되므로 공백을 제외한 모든 애완동물의 갯수를 찾게됩니다.

"고*"을 입력한다면 어떻게 될까요?

마지막으로 ~는 별표(*)나 물음표(?)를 직접 사용하고 싶을때 사용합니다.

"~*"를 입력하게되면 모든 글자를 찾는게 아닌 별표(*)를 찾게됩니다.

=COUNTIFS(A1:A13, "~*") -> 별표(*)로 된 문자의 갯수를 구한다.

글로는 한계가 있으니 직접 해보는게 더 도움이 되니까 꼭 해보세요 :)

이상 COUNT함수의 심화과정이였습니다.

첫장에서 배웠던 SUM의 심화과정입니다.

단순 SUM에서 조건이 붙은 SUMIF,

여러 조건이 붙는 SUMIFS를 소개해드립니다.

서울지역의 지급비용 총 합계를 구했습니다.

SUMIF 사용법

SUMIF(range, criteria, [sum_range])

SUMIF(범위, 비교값, 합계범위)

범위는 합계를 구할 범위로, 지역부터 지급비용까지를 말합니다.

비교값은 ">50", "서울", C5, 등 비교하고 싶은 값을 적으면 됩니다.

합계범위는 실제 구할 숫자범위로, 지급비용 범위가 됩니다.

[sum_range] 는 선택사항이므로 필요할 경우만 적으면 됩니다.

SUMIF는 범위, 조건만 입력해도 계산이 됩니다.

입맛에 따라 사용하시면 될것 같습니다.

만약 서울에 사는 사람중 지급비용이 35,000 이하인 합계만 구하고 싶다면 어떻게 해야될까요?

SUMIF로는 조건을 한개밖에 입력을 못합니다.

만일 여러개의 조건을 만족하는 합계를 구하고 싶다면 다음과 같은 함수를 사용하면 됩니다.

SUMIFS

위와같이 여러 조건을 만족하는 합계를 구하실 수 있습니다.

SUMIFS( 합계범위, 조건범위1, 조건1, [조건범위2, 조건2], ... )

[그 밖의 SUM관련 함수]

SUMSQ : 인수의 제곱의 합을 반환합니다.

SUMSQ(5, 5) = 25

SUMPRODUCT : 주어진 배열에서 해당 요소를 모두 곱하고 그 곱의 합계를 반환합니다.

각 배열의 차원이 같을때만 사용가능한 함수입니다.

차원이 같지 않으면 #VALUE! 오류가 표시됩니다.

SUMPRODUCT은 각 배열을 모두 곱한뒤 곱한 합계를 반환합니다.

5*5 + 4*2 + 12*3 + 3*3

이상 SUM 함수 심화과정이였습니다.

설명을 잘 못해서 이해가 안되시거나 질문은 댓글로 남겨주시기 바랍니다. :)

지정 범위의 숫자의 갯수를 알려주는 함수 COUNT 함수를 사용해 봅시다.

COUNT 함수는 숫자가 포함된 셀의 개수를 세고 인수 목록 내에 포함된 숫자의 개수를 셉니다.

COUNT 함수를 사용하면 범위나 숫자 배열에 포함되어 있는 숫자 필드에서 항목의 수를 구할 수 있습니다.

예를 들어 다음과 같은 수식을 입력하면 범위 B1:B10에 있는 숫자의 개수를 구할 수 있습니다.

위 설명대로 COUNT 함수는 숫자의 갯수만 알려줍니다.

인자로 직접 입력할시엔 결과값이 달라집니다.

=COUNT( 1, 2, 3, "사과", "배")

의 결과값은 숫자가 3개가 있으므로 3으로 출력이되지만,

=COUNT( 1, 2, 3, "4", "5")

의 결과값은 5가 출력이 됩니다.

"4", "5"가 텍스트이지만 텍스트로 표현된 숫자이기 때문에 숫자로 인식하여 카운팅을 해줍니다.

이는 인수로 직접 입력했을경우에만 적용이 되고 범위로 지정하게되면 숫자만 카운팅하게 됩니다.

텍스트로 표현된 숫자를 입력해보았습니다.

COUNT함수를 사용하니 5, 3만 카운팅하여 결과값 2를 출력하게 됩니다.

이를 인수로 입력하게되면

"1", "2", "3" 이 텍스트이지만 숫자로 되어있기 때문에 결과값이 5로 출력이 되었습니다.

인자로 입력할 일은 거의 없기때문에 재미로 알아두시면 좋을것 같습니다