스마일맨의 정보창고

Q) 피벗 테이블 (Pivot Table)을 만들었는데,
     그 피벗 테이블에 들어가는 원본 데이터의 영역을 수정하고 싶습니다.
     해당 원본 영역을 변경(수정) 하는 방법을 알려주세요.

A) 피벗 테이블(Pivot Table)은 엑셀에서 참 유용하게 사용이 됩니다.
     하지만 한 번 만들고 수정을 할 수 없다면 참 힘들겠죠?
     여기서는 원본 데이터에 새로운 사항을 추가 및 삭제하여 원본 데이터의 영역에 변경이 이뤄나는경우
     그 영역을 수정(변경) 하는 방법을 알아보겠습니다.

1. 예제로 만든 왼쪽의 원본 데이터의 테이블과 여기서 파생된 오른쪽의 피벗 테이블 입니다.
    간단히 해당 과일의 갯수(개수)의 합계를 표시하는 방법으로 만들어 보았습니다.

2. 여기서 원본 데이터 영역의 마지막 줄에 멜론을 추가해 보았습니다.
    그냥 하나 더 쓴다고 해서 오른쪽의 피벗 테이블의 내용이 수정이 되지 않죠?
    아래와 같이 하시면 됩니다.

3. 여기서 오른쪽의 피벗 테이블 안의 셀에 커서를 두면 상단에 있는 메뉴의 끝에
    위처럼 '피벗 테이블 도구'의 메뉴가 나타나게 됩니다.
    피벗 테이블에서만 사용하는 옵션이라는 것이죠. ^^

4. '피벗 테이블 도구' - '옵션' 탭의 '원본 데이터 변경'을 클릭하신 후 '데이터 원본 변경'을 선택합니다.

5. '피벗 테이블 이동' 창이 뜨는데 여기의 '표 또는 범위 선택' 에서
     새로운 내용까지 포함하여 영역을 다시 수정하여 잡아 준 후 [확인] 버튼을 클릭합니다.

6. 그러면 위처럼 피벗 테이블에 추가된 내용이 반영되어 수정된 모습을 볼 수 있습니다.
    참 쉽죠잉?~ ^^

Q) 엑셀(Excel) 에서 간혹가다 행과 열을 바꾸가 필요할 때가 있습니다.
      이때 일일이 따로 적거나 복사하지 않고 옮길 수 있는 방법이 있으면 알려주세요. ^^

A) 엑셀에서 작성한 표에서 행과 열을 다시 바꾸고 싶은 충동(?)을 느낄 때가 있습니다.
     이때 쉽게 할 수 있는 방법은 아래와 같이 저를 따라하시면 됩니다.
     자~ 고고씽~ ㅎ

1. 먼저 위와 같이 왼쪽열에는 과일의 이름이, 오른쪽에는 그 갯수가 적힌 표가 있습니다.
    우리는 이 세로로 작성된 표를 가로로 행과 열을 바꾸어 보겠습니다.
    이 표를 영역을 잡아 복사를 합니다.

2. 그리고 붙여 넣을 셀에서 마우스 오른쪽 버튼을 클릭합니다.
    그러면 위와 같은 메뉴(Menu)가 나오는데, 여기서 '선택하여 붙여넣기'를 선택합니다.

3. 그러면 '선택하여 붙여넣기' 박스가 나옵니다.
    여기서 가장 하단의 '행/열 바꿈'을 선택(Check) 하고 [확인] 버튼을 클릭합니다.

4. 짠~
    위처럼 처음 작성한 세로 표에서 행과 열이 바뀐 가로 표로 바뀐 것을 확인 하실 수 있으실 것입니다.
    참~ 쉽죠잉~ ^^

Q) MicroSoft Office Excel 2007 을 사용 중입니다.
      파일에 중요한 내용이 있어서 암호(PassWord)를 설정하려고 하는데 메뉴에 보이지가 않네요.
      암호를 설정하는 방법을 알려주세요.

A) MicroSoft Office Excel 2007 에서 암호를 설정하는 방법은 아래와 같습니다.

1. 암호를 설정할 엑셀 문서를 열고 왼쪽 상단의 오피스(MS Office) 로고를 클릭합니다.

2. 그러면 위와 같은 화면이 나타날 것입니다.
    이제 막 작성한 파일이라면 '저장' 버튼을 클릭하시고,
    이미 저장된 파일이라면 저장 버튼을 누르면 그냥 저장이 되니 '다름 이름으로 저장' 버튼을 클릭합니다.

3. 그럼 위처럼 저장하는 창이 나타날 것입니다.
    하단의 [도구] 라고 쓰여 있는 곳이 보이실 것입니다.
    우리는 이 쪽에서 작업을 할 것입니다.

4. 이 도구 버튼을 클릭하면 아래로 설정 목록이 나타나게 됩니다.
     이 중의 '일반 옵션' 을 선택합니다.

5. 그러면 '일반 옵션' 창이 뜨게 되는데 파일공유의 열기암호, 쓰기암호에서 암호를 설정하시면 됩니다.

    여기서 잠깐!!!
    열기 암호 : 문서를 열때 물어보는 암호로 이 암호를 모르면 문서을 열 수 없습니다.
    쓰기 암호 : 이 암호를 설정하고 나서 암호를 모르면 해당 문서를 열고 나서 수정을 할 수 없습니다. (읽기전용)

    자신이 필요로 하는 암호를 설정 한 후 [확인] 버튼을 클릭합니다.

Q) 엑셀(Excel)에서 각 셀들의 문자들을 연결하려고 합니다.
     방법을 자세히 설명해 주세요.

A) 엑셀에서 각 셀들에 들어 있는 문자열들의 연결(결합)은 아래와 같은 방식으로 진행하시면 됩니다.

1. 위와 같은 엑셀시트가 있습니다.
    우리는 '문자열 1'과 '문자열 2'를 결합하여 옆의 '문자열 연결'에 내용을 나타낼 것입니다.

2. 문자열을 연결하려면 연결자인 엔드(&) 기호를 사용합니다.
    스마일맨이 들어가 있는 B3셀과 블로그 단어가 들어가 있는 C3셀을 연결(결합) 하기 위해
    '=B3&C3' 라고 입력을 후 엔터(Enter)를 누릅니다.

3. 그럼 두셀의 문자열이 이어지며 '스마일맨블로그' 라고 노출이 되게 됩니다.
    참~ 쉽죠? ㅎ
    그런데... 여기서 한가지... 중간에 공백 등을 넣고 싶다면 어떻게 해야 할까요?

4. 이번에는 B3의 스마일맨과 C4의 짱의 단어를 연결할 것입니다.
    중간에 공백(Space)를 넣고 말이죠.
    이 때에는 '=B3&" "&C4' 로 써 줍니다.
    큰따음표(") 로 공백을 묶어 넣고 그 앞뒤로 & 기호를 넣어줍니다.

5. 그러면 이렇게 '스마일맨'과 '짱'의 중간에 공백이 삽입되어 '스마일맨 짱' 이렇게 표현이 되게 됩니다.
    역시나 그렇지만...
    참~ 쉽죠잉? ^o^

Q) 엑셀에서 중복된 셀이 여러개가 존재를 합니다.
      내 셀 목록에서 중복된 내용없이 순수(유니크, Unique) 한 내용만 골라서 뽑아내고 싶습니다.
      방법을 알려주세요.

A) 엑셀을 사용하다보면 중복된 내용을 제거하고 한개씩만 표현하고 싶을 때가 많습니다.
     손으로 일일이 지워가면서 하나씩 할 수도 있지만...
     우리는 조금 배운 사람들 이자나요. ㅎ
     좀 쉬운 방법으로 저와 함께 해 보자구요 ^^

1. 위와 같은 엑셀의 샘플 예제가 있습니다.
    왼쪽의 목록에서 '블로그', '1' 같은 단어는 중복이 되어 있죠?
    이렇게 찾을 항목이 눈으로 보이고 작다면 바로 바로 하면 쉽죠. ㅎ
    하지만 내용이 방대할 때에는 어림도 없담니다.
    이 '목록셀' 들의 내용 중 중복된 내용 없이 한개씩만 표현하여 '중복셀 제외' 쪽에 나타내 보도록 하겠습니다.

2. 메뉴에서 '데이터' - '필터' - '고급필터' 를 선택합니다.

3. 그러면 '고급필터' 창이 하나 노출이 됩니다.
    B3:B14의 내용 들 중 중복 제거 하고 C3의 셀에 붙여 넣을 것입니다.
    그러니 결과 에서는 '다른 장소에 복사'를 선택하시고,
    '목록범위'에는 B3:B14, '복사위치'에는 C3을 선택합니다.

4. 그리고 가장 중요한 내용!!!
    하단의 '동일한 레코드는 하나만(R)' 앞의 체크박스를 꼭! 선택합니다.
    이 부분이 가장 핵심인 것이죠. 동일한 내용는 중복 표기하지 말아라~ 하는 것이요 ^^
    내용을 다 입력하셨으면 [확인] 버튼을 클릭합니다.

5. 그럼 위 처럼 블로그, 1 등의 중복된 내용은 한개만 표시되어 노출이 됩니다.
   
※ 예제에 중복된 셀을 더 많이 넣을껄 그랬나요?
    그럼 중복된 셀이 한개만 표시되는게 더 실가분하게 표현이 될텐데... ^^;
    암튼 방법을 하셨으니 유용하게 사용하시길 바래요~ ^^

Q) 엑셀에서 현재 시간을 쉽게 입력하는 방법이 있을가요?
      있다면 알려주세요.

A) 지난번에 오늘의 날짜를 알려드렸던 팁과 같이, 엑셀 에서는 현재 시간을 입력 하는 단축키도 있습니다.
      그것도 아주 쉽게요...

      원래 현재 시간은 시계를 보고 입력하면... 되는데... 귀찮죠?
      자동으로 현재 시간을 가지고 와서 나타내 주면 쉬울 것입니다.
      그 방법은 아래와 같습니다.

1. 먼저 엑셀의 시트가 있습니다.
    C2에서는 지난번에 배운대로 오늘의 날짜를 입력해 봅니다.
    그리고 C3 셀에서는 현재 시간을 나타내 보도록 하겠습니다.

2. 그 방법은 컨트롤(Ctrl)을 누르고 콜론(:)을 누르는 것입니다.
    콜론은 영문 엘(L)자 옆에 있습니다.
    기본값은 세미콜론(;)이니 콜론(:)을 누르기 위해선 쉬프트(Shift)를 함께 누르시면 됩니다.
    즉, 콘트롤(Ctrl) + 쉬프트(Shift) + 세미콜론(;)을 누르시면 됩니다.
    'Ctrl' + ':' 을 누르면 되는 것이죠.

3. 그려면 위에서 보시는 바와 같이 현재 시간이 입력이 되게 됩니다.
    옆의 컴퓨터 시간에서 보시면 알겠지만 동일하죠? ㅎ

    이번에도 쉽게 잘 성공 하셨죠? ㅎ
    모두 모두 축하 축하~ ^^

Q) 엑셀(Excel)을 사용 중 자신이 원하는 내용만을 콤보박스 (목록상자)의 형태로 나타나게 하여
     선택할 수 있는 방법에 대해서 설명해 주세요.

A) 엑셀에서 콤보박스(목록)을 이용하여 내용을 채우는 방법은 예상외로 쉽습니다.
     물론 제가 아래에 설명할 내용 말고도 다른 방법으로 가능하며, 그 방법은 다양합니다. ^^
     우선 그 중에 첫번째로 아래의 방법대로 저와 함께 하시면 되시겠습니다.
     자~ 고고씽~ ㅎ

1. 기본적인 엑셀을 준비합니다.
     여기서는 운송수단에 버스, 자가용, 택시의 입력값만 받으려고 합니다.
     이 3가지의 운송수단민을 쉽게 입력할 수 있도록 해 보겠습니다.
     (뒤의 탑승자는 이 강의를 다 해보시고 나서 여러분이 한 번 시도해 보세요. ^^)

2. 콤보박스가 나타나고자 하는 곳에 셀을 선택하고,
    메뉴의 '데이터' - '유효성 검사'를 선택합니다.

3. 그러면 '데이터 유효성' 팝업창이 나타나게 됩니다.
    '설정' 탭의 '유효성 조건'에서 가장 처음에 있는 '제한대상'의 콤보박스를 열어 봅니다.
     그 안에는 모든값, 정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자 지정 등 내용이 많이 있는데,
     여기서 '목록'을 선택합니다.

4. 그러면 설정 탭 안의 내용이 살짝 변합니다.
     하단의 '원본'란을 주목해 주세요.
     여기에 셀에서 선택할 내용인 '버스, 자가용, 택시'를 써 줍니다.
     선택할 단어들의 구분자는 콤마(,) 입니다.
     (다른 셀에 해당 내용들을 미리 써 놓고 옆의 셀 선택으로 하셔도 동일한 효과를 보실 수 있습니다.)
     완료 하였으면 확인 버튼을 클릭하셔서 설정을 저장하고 빠져 나옵니다.

5. 아까 설정한 셀을 선택하면 옆에 콤보박스가 노출되는 것을 보실 수 있으실 것입니다.
     그 버튼을 클릭해 볼까요?

6. 아까 저희가 입력하였던 버스, 자가용, 택시가 나옵니다.
    이 3가지중 하나를 고르셔서 클릭하시면 해당 내용이 선택되어 자동으로 입력이 되게 됩니다.
    참~ 쉽죵이~ ㅎ

7. 여기서 잠깐!!!
    혹시 여기서 처음에 선택할 내용이 아닌 다른 단어를 넣으면 어케 될까요?
    바로 위에서 보시는 바와 같이 
    '입력한 값이 잘못되었습니다. 
     이 셀에 입력할 수 있는 값은 제한되어 있습니다.' 라고 경고가 나오게 되며,
    처음에 설정한 내용 이외에는 입력을 하실 수가 없게 됩니다.
    이것이 바로 우리가 설정한 셀의 유효성 검사 때문 입니다. ^^

Q) 제가 엑셀(Excel) 을 사용하는데 기본적으로 시트(Sheet)를 한개만 사용을 합니다.
     그런데 엑셀을 실행시 시트가 3개나 열리는데, 뒤에 두개는 불필요 합니다.
     엑셀을 열때 기본으로 열리는 시트 수를 변경하는 방법을 알려주세요.

A) 많은 사람들이 엑셀을 사용하면서 기본적으로 열리는 
     시트의 갯수인 3개를 모두 사용 하는 사람은 많지 않습니다.
     이 시트 갯수를 변경하는 방법은 아래와 같습니다.
     자~ 따라 오세요~ ^^

1. 처음 디폴트(Default) 상태에서 엑셀을 실행시 위의 그림과 같이 처음 열리는 시트 수는 3개 입니다.
    이 시트 수 변경은... 계속 따라 오세요 ^^

2. 메뉴에서 '도구' - '옵션'을 선택합니다.

3. 그러면 '옵션' 창이 열리는데, '일반' 탭을 선택 하신 후
    '새 통합 문서의 시트 수'를 확인 합니다.
     지금 열리고 있는 3개라는 숫자 '3'이 써 있는 것을 확인하실 수 있으실 것입니다.

4. 우리는 이 시트를 한개만 열리도록 1로 변경을 하고 하단의 [확인] 버튼을 클릭합니다.
    그리고 열려 있는 엑셀을 종료 합니다.

5. 자~ 마음을 다스리며... 엑셀을 다시 실행을 합니다.
    두근 두근 두근~~~
    짠~ 하단을 보시면 시트가 1개만 열린 것을 확인 하실 수 있으 실 것입니다.
    여러분도 성공 하셨죠? 축하드려요~ ^^

Q) 엑셀(Excel) 에서 줄에 해당 단어가 들어가 있는 셀(Cell)이 몇개가 있는지 세고 싶습니다.
      하지만 행의 갯수가 몇만개가 되니 그걸 일일이 다 셀수는 없고...
      행에서 해당 글이 들어간 셀의 갯수를 카운트(Count) 해서 옆에 그 숫자를 나타내는 방법을 알려주세요.

A) 네, 엑셀의 기능은 참 무궁무진 하죠?
     그런 기능들을 이용하면 참 쉽고, 편리하게 생활을 할 수 있습니다.
     
     오늘은 셀 카운트 기능에 대해서 한 번 알아보겠습니다.


   자~ 위와 같은 엑셀의 예제가 있습니다.
   왼쪽의 Data1 ~ Data5 까지의 셀 안에 각종 단어들이 있습니다.
   이 데이터 중에 오른쪽의 카운트 단어 안의 단어가 있는 셀의 갯수를 세어서
   '단어개수' 안에 그 숫자를 써 넣으려고 합니다.
    과연 어떤 함수를 이용해야 할까요?

   이때에는 'Countif' 함수를 사용합니다.
   이 CountIF 함수의 정의 및 사용법에 대해서 우선 알아볼까요?


   자~ 이제 이해가 좀 가시겠지요?
  
   이제 본론으로 다시 들어와서 저 위의 해당 갯수를 적는 셀에 적을 함수를 적어 보겠습니다.
   일단 J3셀에 =CountIF 함수를 적고 괄호('(')를 열고, 해당 범위를 적습니다.
   저기서는 Data1 ~ Data5까지의 셀이니, 
   J3 셀에 '=COUNTIF(C3' 라고 적습니다.
   그리고 콤마(',')를 적은 후 카운트 단어가 들은 셀인 I3 을 적어
   최종적으로 '=COUNTIF(C3:G3,I3)' 를 적습니다.
   (물론 I3 대신에 자신이 정하는 단어를 직접 써 넣으셔도 됩니다.)

   그렇게 J3 셀에 위의 함수를 적은 후 엔터(Enter)를 쳐서 해당 값을 구합니다.
   그리고 밑의 셀에도 적용이 되게 함수 내용을 드레그(Drag) 하여 채워 넣습니다.
   최종적으로 완성된 모습은 아래와 같습니다.


   참~ 쉽죠잉~ ^^

Q) 엑셀(Excel) 에서 셀에 적혀 있는 숫자의 갯수를 구하거나,
      평균값, 개수, 최대값, 최소값, 합계 등을 빠르게 구할 수 있는 방법이 있으면 알려주세요.

A) 엑셀은 참 많은 기능을 포함하고 있죠.
      특히 수식을 계산하거나 하는 것은 계산기를 사용하는 것보다 더 빠르고 쉽게 사용할 수 있죠.
      자~ 그 방법을 저와 함께 알아보시죠~ ^^

  1. 자~ 위와 같은 엑셀이 있습니다.
     우리는 저기의 1~10번의 각각에 들어 있는 셀들의 합을 구할 것 이구요.
     합계의 셀에 '=sum(D3:D12)' 하면 답은 나오겠죠?
     하지만 우리는 그 합계 칸에 답을 적지 않고 그냥 합계만 알기만 하면 됩니다.
     그럼 계산기를 꺼낼까요? 에이~~~ ㅎ
     우리는 그저 마우스의 드레그(Drag) 로 합계를 구할 것이에요. 그 방법은 아래에... ^^

  2. 먼저 엑셀의 하단을 봅니다.
     거기에 준비라는 글이 써 있죠? 
     그리고 자세히 보시면 흰색으로 칸칸이 있을 것입니다.
     거기에는 온갖 정보들이 나오죠. Num Lock이 켜져 있는지, 꺼져 있는지,
     Insert 키가 작동하여 겹침 모드인지 등등등...
     거기에 마우스를 가져가서 마우스 오른쪽 버튼을 클릭합니다.    

    3. 그러면 위와 같은 팝업 창이 나옵니다.
       우리는 합계를 구할 것이기 때문에 합계를 선택합니다.

  4. 그리고 우리는 1에서 10번값인 D3열부터 D12열까지 마우스로 드레그를 하여 선택합니다.
      그러면 하단에 '합계=398' 이라고 친절히 그리고 빠르게 알려줍니다.

      만약 평균값을 구하고 싶다면 평균값을 선택하면 선택을 하고 드레그(Drag)를 합니다.
      이렇게 우리는 평균값(average), 셀의 개수(counta), 드레그 한 곳 중 숫자만 들어 있는 셀의 개수(count),
      최대값(max), 최소값(min), 합계(sum) 등을 쉽고 빠르게 구할 수 있습니다. ^^