CaN (Computer & Network) Tips

일반적으로 Excel(엑셀) 워크시트를 사용하다 보면 데이터 자체와는 상관 없이 설명 등의 주석을 달아야 하는 일이 생긴다.

이때는 데이터 주변 적당한 셀에 설명을 적어 놓거나 특정 셀에 대한 것이면 메모 삽입 기능을 사용한다.

그외에 셀에 입력하는 수식에는 영향을 주지는 않으면서 수식과 같은 줄에 주석을 입력하고 싶은 경우도 있다.

그런 경우는 아래 그림과 같이 N 함수를 활용하면 된다.


그림에서 처럼 처리하고자 하는 수식 뒤에 +N("주석 내용")과 같은 형식으로 필요한 내용을 적어 넣으면 된다.

Excel(엑셀) 워크시트의 기능 중에 마우스 커서를 셀 위로 갖다대면 각 셀에 필요한 주석 등을 표시해 주는 메모(Comment)라는 것이 있다. 메모를 작성해 두면 셀의 오른쪽 상단에 빨간색 딱지가 하나 붙어 메모가 표시되어 있음을 보여주게 된다. 보통은 메모에 텍스트 위주의 정보를 입력해서 활용하게 되는데 사실 다양한 형태로 이용할 수 있다.

그 중 하나로 메모에 그림을 입력할 수도 있는데 다음 글을 참조하면 된다.

[Excel] 메모에 그림 넣기, 2007. 7. 27.

이렇게 하면 시각적으로 정보의 폭이나 재미를 넓힐 수 있어 좋다. 하지만, 그림을 여러 개 입력해야 하는 경우에는 상당히 지루한 작업이 되고, 이런 이유 때문에 필요하지만, 사용을 꺼리는 기능이 되기도 한다.

예를 들어, 각 셀에 사람 이름이 들어가 있는 명단이 있고, 각 사람에 대한 사진 파일을 특정 폴더에 정리해 두었다면 간단한 매크로(macro)를 작성하여 이름이 적힌 각 셀 위에 마우스 커서를 갖다대면 간단하게 해당 인물의 사진이 팝업창처럼 나타나도록 할 수 있다.

일단, 아래 그림과 같이 엑셀의 워크시트에 이름이 입력되어져 있고, 사진 파일의 이름은 “이름.png”로 저장되어 있다고 가정한다.

이제 Excel에서 [Alt] + [F11] 키를 눌러 VBA 편집기를 실행한다.


그 다음 왼편 창 상단에서 작업 중인 Excel 파일 이름이 괄호 안에 들어가 있는 VBAProject 항목을 확인하고 [현재 통합 문서]를 더블클릭하여 코드 입력 창을 부른다.


빈 창이 나타나면 위와 같이 코드를 입력한다. 위 그림에 있는 내용은 아래와 같다.

Sub AddPhoto()
For Each cell In Selection
    CurrentFolder = ActiveWorkbook.Path
    EmployeePhoto = CurrentFolder & "\인물\" & cell.Value & ".png"
    With cell.AddComment
        .Shape.Fill.UserPicture EmployeePhoto
        .Shape.Height = 100
        .Shape.Width = 100
    End With
Next cell
End Sub

사진 파일을 담은 폴더가 현재 작업 중인 Excel 파일의 바로 아래에 "인물"이라는 이름으로 지정된 경우이다. 사용 환경에 따라 적당하게 수정하면 된다. 확장자도 jpg라면 png를 jpg로 바꾸면 된다. 들여쓰기는 편의상 한 것으로 하지 않아도 되지만, 될 수 있으면 하는 것이 좋고, [Tab] 키를 쓰면 된다. 그림의 크기도 필요에 따라 수정하면 된다.

입력을 마쳤으면 편집기 창을 닫고 Excel 워크시트로 돌아온다. 그 다음, 사진을 넣을 이름 목록을 선택하고, [개발 도구] 메뉴에서 [매크로] 단추를 클릭한다. 만약 리본 메뉴에서 개발 도구 탭이 보이지 않는다면 다음 글을 참고한다.

[Excel 2007] 리본 메뉴에 개발 도구 탭이 보이지 않을 때, 2008. 10. 5.
[Excel 2010] 리본 메뉴에 개발 도구 탭 표시하기, 2010. 5. 1. 

매크로 목록에서 방금 작성한 해당 매크로를 선택하고, [실행(R)]을 클릭한다. 그러면 아래 그림과 같이 모든 셀에 각각 메모 표식이 나타나고 마우스 커서를 갖다대면 이름에 맞는 그림이 나타나는 것을 확인할 수 있다.


여러 모로 다양한 경우에 응용할 수 있다.

Excel(엑셀)을 쓰다보면 필요에 따라 첫 번째 열인 A열을 [숨기기] 하는 경우가 있다. 그런데 이외로 이렇게 숨긴 A열을 다시 보이게 할 수 없어 해매는 사용자들이 많다.

더 복잡하고 어려운 방법도 있지만 일반적으로 다음 두 가지 방법을 사용하면 된다.

방법 하나

원래 숨겨진 열을 보이게 할 때에는 숨겨진 열 앞과 뒤의 열을 동시에 선택하여 숨겨진 부분이 그 사이에 들어가게 한 다음 단축 메뉴에서 [숨기기 취소] 메뉴를 이용한다. A열을 숨겼을 때에는 A열 앞에 선택할 수 없는 열이 없어서 당황하게 되는데, 이때는 B열을 마우스 왼쪽 버튼으로 선택한 상태에서  왼쪽으로 행 머리글 부분까지 끈다. 그러면 단축 메뉴의 [숨기기 취소]  메뉴로 A열을 찾을 수 있다.

방법 둘

마우스 커서를 B열의 열 머리글 왼쪽 경계로 가져가면 그 모양이 아래 그림과 같이 이중 수직선을 가진 크기 변경 모드로 바뀌게 된다. 이때 마우스 왼쪽 버튼을 누른채 오른쪽으로 끌어주면 A열이 나타난다.

예전에 소개했던 아래 방법으로도 가능하다.

[MS Excel] 숨겨 놓은 여러 열 중 특정 열만 나타나게 하기, 2008. 11. 25.

일을 하다 보면 출장비 계산이나 기타 등등의 이유로 지도 상에서 두 지점 간의 직전 거리를 알아야 하는 경우가 있다. 유류비처럼 꼬불꼬불하더라도 실제 움직인 거리가 중요할텐데 아무튼 이런 정보가 필요할 때가 있다.

가장 간단한 방법은 PC에 설치한 지도 프로그램을 이용하거나 포털 사이트의 지도 서비를 이용하는 것이다. 아래는 각각 다음 과 네이버 에서 확인한 것이다.

위 예는 서울역에서 한강 대교 중간 지점 정도까지의 거리를 확인한 것이다. 대략 4.1에서 4.2km의 수치를 보여주고 있다.

이런 식으로 다수의 지점을 Excel(엑셀) 워크시트에 입력해 두고 필요한 지점끼리 짝을 지어 거리를 계산하는 방법 하나를 소개하고자 한다.

우선 Excel 워크시트에 원하는 지점 또는 주소의 위도와 경도를 입력해야 한다. 필요한 데이터를 알아내는 방법도 몇 가지 있지만 여기에서는 구글 맵 사이트를 이용한다.

구글 맵 사이트 로 이동한다.


[지도 검색] 창에 "서울역"을 입력하고 해당 위치를 찾는다.


위치를 표시하는 핀에서 마우스 오른쪽 버튼을 누르고, [지도 중앙으로 설정]을 선택한다.


해당 지점이 화면 가운데에 자리를 잡는다. 이렇게 해야 해당 지점의 정확한 좌표를 확인할 수 있다.


위치를 찾을 때 특정 이름을 사용할 수도 있지만, 번지까지 주소를 알고 있다면 주소를 입력해도 된다. 개인 주택 등 특정 이름이 없는 경우는 주소가 더 요긴하다.


해당 지점을 중앙에 놓았으면, 우측 상단의 "링크" 메뉴를 클릭하여 아래 그림과 같이 링크 정보를 불러온다.


"이메일 또는 메신저에 링크 붙여넣기" 항목에 보면 아래와 같이 좌표처럼 생긴 숫자를 찾을 수 있다. 이를 복사하고 Excel에 정리한다. 쉼표로 구분되어 있는데, 앞이 위도고 뒤가 경도이다.


다른 지점의 좌표도 위와 같은 방법으로 정리한다.


자신의 활용 목적에 맞게 자료를 정리하고 거리를 계산하는 수식을 이용하면 된다.


예를 들어, 위 시트에서 2행의 경우는 서울역에서 한강 대교 간의 거리를 계산하는 것이다. 상대 지점 열에 상대 지점의 번호를 입력하면 F열에 자동으로 지점명이 입력되도록 한다. index나 vlookup 함수를 사용하면 된다.

거리1과 거리2가 있는데, 거리1은 지구가 둥글다는 점을 감안한 거리 계산법이고, 거리2는 위도 간의 거리와 경도 간의 거리를 두 변으로 하는 직각 삼각형의 빗변의 길이를 구하는 피타고라스의 정리를 이용한 것이다. 이 공식을 더러 이용하는 경우도 있어서 적어둔 것이고, 거리1 방식을 추천한다. 서울역과 한강 대교 간의 거리는 4.13km로 다음이나 네이버의 결과와 유사하다.

거리1의 각 셀에 들어가는 공식의 형태는 아래와 같다.

=ACOS(COS(RADIANS(90-지점1의위도))*COS(RADIANS(90-지점2의위도))+SIN(RADIANS(90-지점1의위도)) *SIN(RADIANS(90-지점2의위도))*COS(RADIANS(지점1의경도-지점2의경도)))*6371

G2셀에 실제 입력된 내용:
=ACOS(COS(RADIANS(90-C2))*COS(RADIANS(90-INDIRECT("C"&E2+1)))+SIN(RADIANS(90-C2))*SIN(RADIANS(90-INDIRECT("C"&E2+1)))*COS(RADIANS(D2-INDIRECT("D"&E2+1))))*6371

이렇게 입력하면 E열 상대 지점 셀에서 다른 지점 번호로 바꾸더라도 자동으로 거리를 다시 계산할 수 있다.

H2셀에 실제 입력된 내용:
=SQRT((((C2-INDIRECT("C"&E2+1))*92)^2)+(((D2-INDIRECT("D"&E2+1))*114)^2))

92는 위도 한 단위의 실제 거리, 114는 경도 한 단위의 실제 거리를 표시한 것이다.

모든 수식은 첫 셀에 한 번만 입력한 후 아래로 복사해 사용하면 된다. 이런 방법을 바탕으로 다양한 형태의 양식에 사용할 수 있을 것이다.

참고로, 위 공식들은 예전에 어디서 찾아둔 것으로 비슷한 다른 공식이 여럿 있으므로 필요하다면 웹에서 찾아 다양하게 적용해 보면 좋을 것이다.

일단 아래 그림과 같은 조회 업무가 필요하다고 가정한다.


특정 제품이 어떤 지역에서 얼마만큼 팔렸는지 정리한 것이다. 날짜나 순서별로 같은 품명과 지역 조합이 중복되어 들어가 있고, 이 중복되어 있는 조합의 판매량을 총계 내는 업무이다.

우선 찾는 품명과 일치하는 값이 어디에 있는지를 알아내기 위해 아래와 같은 논리 수식을 입력한다.


이 과정을 거쳐 찾는 품명과 같은 값을 찾아 "TRUE"를 반환하게 한다.


같은 방법으로 조회하는 지역과 같은 셀들도 찾아낸다.


아래 그림에서 보는 바와 같이 두 열의 논리값이 모두 TRUE라면 조회하는 품명과 지역 조합에 해당하는 것이다.


두 열의 값이 TRUE인 행의 판매량만 찾아 모두 더하면 되므로 아래와 같은 수식을 필요한 셀에 입력한다.


이 수식은 각 행의 "(TRUE 또는 FALSE) * (TRUE 또는 FALSE) * 판매량"의 총합이라는 의미이며, 일반적으로 TRUE는 숫자 1, FALSE는 숫자 0을 뜻하기 때문에 결국 각 행은 "1 * 판매량"이나 "0 * 판매량"을 계산한다. 그래서 두 열이 모두 TRUE가 아니라면 모두 0(영)이 되어 필요한 값만 더할 수 있게 된다. 그런데 결과를 확인해 보면 아래 그림과 같이 "0"으로 나온다. 이는 생각과 같이 TRUE나 FALSE라는 값을 각각 1이나 0으로 알아서 계산해 주지 않기 때문이다.


논리값인 TRUE와 FALSE를 숫자로 바꾸기 위해 조건문 등을 사용할 수도 있지만, 아래 그림과 같이 수식을 바꿔주면 된다. 즉, 수식 앞에 "-(마이너스 기호)"를 두 번 넣어준다. 마이너스를 하나 넣으면 논리값이 반대가 되며 이 값은 숫자로 바뀐다. 여기에 마이너스를 하나 더 넣으면 숫자 모양은 유지하면서 논리값을 원래대로 복원한다.


이렇게 수식을 변경하면, 변경이 끝나는 순간 판매량 총계가 제대로 계산되어 나온 것을 확인할 수 있다.

미리 만들어진 Excel 차트에 열이나 행을 더 추가할 경우 차트가 같은 시트에 있다면 아래 그림과 같이 마우스로 핸들을 끌어 이 작업을 할 수 있다.

그런데 차트가 다른 워크시트나 차트 전용 시트에 있다면 이 방법이 통하지 않는다.

이때는 추가하고자 하는 데이터를 복사한 후 차트로 가서 붙여 넣으면 된다.

없앨 때에는 원하는 그래프를 클릭하고 바로 삭제하면 된다.

전(全) 버전에서 가능하다.

Excel(엑셀)에서 작업을 하다 보면 셀에 값을 넣기는 해야겠고 실제로는 안 보이게 했으면 할 때가 있다. 이럴 때는 보통 메모를 삽입하거나, 글자 색을 배경과 같은 색으로 지정하기도 한다. 다른 방법도 있지만 셀 서식을 이용하는 방법도 있다.

이런 지정이 필요한 셀을 선택한 후(범위 지정도 가능) "셀 서식" 창을 부르고, 아래 그림과 같이 '사용자 지정' 범주에서 '형식' 입력 상자에 ";;;"(따옴표를 제외하고 세미콜론(;)을 연속으로 세 개 입력)을 입력한 후 [확인]을 누르면 된다.


";;;"은 셀 값이 양수, 음수, 0(영)이거나 일반 텍스트여도 시트 상에는 아무 내용도 보여주지 말라는 의미이다.

Excel 모든 버전에서 할 수 있다.

Excel(엑셀) 워크시트에 데이터를 입력할 때에는 입력한 내용 자체를 다듬어 바로 보고서로 활용하기 위한 경우도 있고, 이 자료를 바탕으로 피벗테이블을 작성하거나 추가 분석을 위한 데이터베이스로 활용하는 경우가 있다.

후자의 경우라면 더욱 목록(list)이나 표(table)의 특성을 잘 활용할 수 있도록 입력하고 관리하는 것이 중요하다. 이를 위해 데이터를 입력할 때에는 적어도 다음과 같은 원칙을 따르는 것이 좋다.

- 첫 번째 행에 머리글(heading)을 포함한다. 같은 이름이 들어가지 않도록 하고 필요하다면 실제 데이터인 레코드 행과 쉽게 구분할 수 있는 스타일을 주도록 한다. 또한, 제목 행(머리글, 필드명)과 레코드 행 사이에 빈 행이 들어가지 않도록 한다.


- 특별한 경우가 아니라면, 워크시트 상단의 3개 행 정도는 비워놓고 네 번째 행부터 목록이나 표를 작성한다. 필요하다면 더 비워둘 수도 있다. 이 부분은 나중에 별도 수식이나 조건(특히 고급 필터를 사용할 경우) 등 부가 정보를 활용하는 용도로 사용할 수 있다. 필요하다면 이 부분은 숨기기 기능으로 안 보이게 하면 된다.


- 하나의 워크시트에 여러 개의 목록이나 표를 입력해야 한다면 이들 사이에 빈 열을 하나 이상 추가한다. 통합될 수 있는 내용이라면 가급적 하나의 목록이나 표로 만든다.


- 목록이나 표 안에는 빈 셀이 생기지 않도록 주의한다. 원래 값이 없어 비워두는 경우는 어쩔 수 없지만, 위 셀에 있는 내용과 같다고 해당 셀의 내용을 비우면 안 된다. 중복되더라도 모두 입력한다.


- 가급적 목록이나 표는 정렬(오름차순, 내림차순)해 사용한다. 한눈에 보기에도 좋고, 분석 시 속도에도 영향을 줄 수 있다. 필수 사항은 아니지만, 보통 첫 번째 열 등을 기준으로 정렬해 두도록 한다.

Excel(엑셀)에서 각 달(month)의 마지막 날짜를 알아내는 방법이다. 아래는 2007년 12월에 "[MS Excel] 각 달의 마지막 날짜 구하기"라는 제목으로 올렸던 "복잡한 방법"이다.


DATE 함수의 특성을 이용하는 것으로 해당 월의 다음 달 첫 닐짜(1일)에서 1(일)을 빼면 그 전날의 날짜가 나오도록 하는 것이 요령이다. 다음 그림을 참조한다.

여기에서 B2에 입력된 수식은 아래와 같다.

=DATE(YEAR(A2),MONTH(A2)+1,1)-1

위 방법은 "EOMONTH()"라는 함수로 간단하게 해결할 수 있다.

MS Excel 2003 이하 버전과 2007 버전에서 같은 도형을 연속으로 그리는 방법이다.


(※ 동영상에 음성으로 설명이 들어가 있습니다.)