Excel Korea

[Excel] A열 각 셀에 있는 값을 B열에 일정 횟수 반복하여 입력하기

2021-12-19T20:09:00.005+09:00

Excel(엑셀) 워크시트의 A열에 데이터가 위에서 아래로 각 셀에 입력되어 있다고 하자. 각 셀에 있는 값을 B열에 두 번이면 두 번, 세 번이면 세 번 연속 입력하면서 전체 데이터를 다시 입력해야 할 때가 있다.

즉, 아래 그림과 같은 경우이다.

이 작업을 간단하게 하는 방법도 여럿 있겠지만, 아래와 같은 방법도 있다.

우선 아래 그림과 같이 B열에 1번 행부터 A열의 첫 번째 데이터를 반복이 필요한 만큼 입력해준다. 직접 입력해도 되고, 간단한 수식을 써도 된다.

그 다음 A열의 두 번째 값이 입력되는 첫 번째 셀에 아래와 같은 수식을 입력한다.

이 수식의 의미는 이렇다. 현재 자신의 위치(B5)에서 반복하고자 하는 횟수(여기서는 네 번이므로 4)만큼 위에 위치한 셀의 값이 원본 A열의 몇 번째에 있는 지를 알아내고, 이 위치에는 그 다음 번 행에 있는 값을 불러와 적으라는 뜻이다.

이렇게 입력한 다음 해당 셀의 데이터가 정확한지를 확인하고, 아래 그림과 같이 채우기 핸들을 필요한 만큼 잡아 끌어내린다.

이렇게 하면 끝난다.

Excel 모든 버전에서 같다.

[Excel(엑셀)] Y축 두 개짜리 막대 그래프를 겹치지 않게 그리는 방법

2011-03-01T13:37:00.000+09:00

Excel(엑셀)에서 두 개의 계열(열)로 데이터를 정리했는데 이 데이터의 숫자 규모가 너무 달라 하나의 Y축을 갖는 차트를 그리면 한 쪽이 너무 작거나 너무 커서 한눈에 흐름을 파악하기 어려운 경우가 있다.

이때는 보통 Y축을 "기본 축"과 "보조 축"으로 분리해 차트의 왼쪽과 오른쪽에 Y축을 각각 하나씩 두는 방법을 택하게 된다. 이렇게 하면 해당 데이터의 숫자 규모에 맞게 적당한 크기로 차트가 그려지기는 하지만, 하나의 데이터를 다른 모양의 차트로 하지 않고, 모두 막대 그래프로 한다면 서로 겹치는 문제가 나타난다. 일반적으로는 서로 다른 모양의 차트(하나는 막대, 하나는 꺾은선)를 사용하는 것이 좋기는 한데, 꼭 막대 그래프로 그려야 한다면 수고스럽지만 약간의 작업을 더 해주면 된다.

[동영상 강좌]

[Excel(엑셀)] 메모나 도형의 모양을 다른 것으로 간단하게 바꾸기

2011-02-28T18:11:00.000+09:00

Excel(엑셀)에서 각 셀에 직접 입력할 필요는 없지만 뭔가 적어 놓을 때 사용하는 메모 기능이 있다. 이 메모는 기본적으로 노란색 네모 형태로 만들어지는데 이 모양을 바꾸고 싶다면 Excel 2007 이상에서는 빠른 실행 도구 모음에 [도형 모양 변경] 단추를 끌어다 놓으면 무척 편리하다.

이전 버전의 Excel에서는 메뉴에서 쉽게 도형을 바꿀 수 있지만, 더 높은 버전에서는 이렇게 단추를 끌어오는 것이 좋다.

[동영상 강좌]

[Excel(엑셀)] 워크시트 이름을 포함하는 셀 참조를 만들 때 와일드카드 이용하기

2011-02-28T00:40:00.000+09:00

Excel(엑셀)에서 여러 개의 워크시트에 걸쳐 정리된 데이터를 하나의 시트에 종합해 정리하는 경우가 있다. 같은 형태로 만들어진 워크시트를 이용하는 것이라면 일반적으로 데이터 통합 기능을 사용하는 것이 좋다.

그래도 가끔은 여러 개의 워크시트의 이름을 이용해 셀 참조를 만들어야 하기도 한다. 워크시트가 연속으로 배열되어 있는 경우라면 셀 범위 지정할 때 사용하는 참조 연산자인 콜론(:)을 이용하는 방법이 있다. 배열이 연속적이지 않으면서, 워크시트 이름에 약간의 패턴이 있다면 물음표(?), 별표(*)와 같은 와일드카드를 이용할 수 있다.

[동영상 강좌]

[Excel(엑셀)] 피벗 테이블에서 집계할 데이터가 없는 항목도 나타나게 하기

2011-02-22T21:59:00.000+09:00

Excel(엑셀) 피벗 테이블은 잘 정리된 데이터를 일정 기준에 맞게 집계해 보여주는 중요한 기능이다. 사용자가 보고싶어 하는 데이터를 원하는 위치로 쉽게 이동하면서 보여주는데, 이 작업을 하다 보면 같은 열에 있는 데이터 중 같은 것끼리를 모아 집계해 보여줄 때, 기본적으로 필터링 조건에 따라 보여줄 데이터가 없는 항목은 누락되어 결과표에 나타나지 않는다.

이렇듯 전체 데이터 목록에는 존재하지만, 필터링 조건 때문에 결과표에 나타나지 않는 항목을 항상 나타나게 하는 방법이다.

[동영상 강좌]

[Excel(엑셀)] 워크시트에 분석용 데이터를 입력할 때 필요한 가이드라인

2011-02-14T12:08:00.000+09:00

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

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

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

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

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

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

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

[Excel(엑셀)] 셀에 분수(分數)를 바로 입력하기

2011-02-11T17:52:00.000+09:00

Excel 워크시트에서 1/2, 4/5와 같은 분수를 입력하려고 이 모양의 데이터를 입력하면 바로 날짜 형식으로 바뀌는 것을 볼 수 있다.

다시 분수로 제대로 형식을 바꾸려면 [셀 서식] 등을 이용해야 하는데 아래 동영상처럼 입력하고자 하는 분수 앞에 숫자 0을 쓰고 한 칸 비워주면 된다.

원래 대분수(1보다 큰 가분수의 분자를 분모보다 작게 쓰기 위해 정수와 진분수를 혼용하는 방법)를 표기하는 방법이다.

[Excel(엑셀)] 가나다 순이 아니라 셀 배경색이나 글꼴 색으로 정렬하기(Sorting)

2011-02-10T11:38:00.000+09:00

Excel 2007이 이전 버전보다 더 좋아진 기능 중의 하나가 바로 정렬(Sort) 기능이다. 정렬 기준의 개수도 더 늘어났고 재미있는 것은 셀의 내용, 즉 데이터 값에 의한 정렬뿐만 아니라 셀의 서식인 색상에 따라서도 정렬할 수 있게 되었다는 것이다.

정렬 기준이 되는 색을 선택할 때에는 워크시트에 사용 중인 색상만 나열된다. 사실 색상의 정렬 순서보다는 같은 색의 데이터를 한 데에 묶는 용도로 사용할 때 요긴하다.

[Excel(엑셀)] 각 달의 마지막 날짜 구하기: 복잡한 방법과 간단한 방법

2011-02-09T21:43:00.000+09:00

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

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

[Flash] http://infosys.iptime.org/for_cantips/attach/1293946117.swf

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

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

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

[Flash] http://infosys.iptime.org/for_cantips/attach/1328497832.swf

[Excel(엑셀)] 같은 도형을 연속으로 그리기

2011-02-09T00:09:00.000+09:00

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

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

[Flash] http://infosys.iptime.org/for_cantips/attach/1317585446.swf

[Excel(엑셀)] 같은 열에 중복되어 입력된 값들을 단일 레코드로 집계하기

2011-02-06T15:42:00.001+09:00

다음과 같이 이름과 매출액이 일자순으로 기록되어 있는 데이터 목록이 있을 때, 같은 이름이 여러 일자에 걸쳐 나타날 수 있다. 이 데이터를 이름별로 합쳐 정리하는 방법이다.

데이터 통합 메뉴를 이용하면 된다.

MS Excel 2007

MS Excel 2003

[Flash] http://infosys.iptime.org/for_cantips/attach/1104919079.swf

SUMIF와 같은 함수를 이용할 수도 있겠지만 이름 목록을 중복되지 않은 단일 레코드 집합으로 만들어주는 이점이 있다. (참고 글: [Excel] 중복되어 입력된 데이터를 고급 필터 기능으로 하나씩 뽑아 내 정리하기[2008. 1. 24.])

[Excel(엑셀)] 같은 형식의 두 데이터 목록을 하나로 합쳐 정리하기

2011-02-01T17:00:00.001+09:00

다음과 같이 이름과 매출액이 적혀있는 두 개의 동종 데이터 목록이 있을 때, 두 목록을 하나의 목록으로 합쳐 정리하는 방법이다.

양쪽 목록 모두에 이름이 있는 경우는 매출액을 합계낸다. 합계뿐만 아니라 다음과 같은 함수를 사용할 수도 있다.

데이터 통합 메뉴를 이용하면 된다.

MS Excel 2007

MS Excel 2003

[Flash] http://infosys.iptime.org/for_cantips//attach/1153949726.swf

[Excel(엑셀)] 중복되어 입력된 데이터를 고급 필터 기능으로 하나씩 뽑아 내 정리하기

2011-01-31T14:12:00.000+09:00

Excel(엑셀)에서 한 열에 중복이 허용되어 다수의 데이터가 입력된 경우 어떤 값들이 들어 있는지 하나씩 뽑아내 정리할 때 사용하는 방법이다.

여기에서는 고급 필터(Advanced Filter) 기능을 이용한다. 아래 그림에서와 같이 조건 범위를 만들고 "동일한 레코드는 하나만(R)"를 선택하는 것이 핵심이다.

[Flash] http://infosys.iptime.org/for_cantips/attach/1108823510.swf

[Excel(엑셀)] 각 달의 마지막 날짜 구하기

2011-01-31T14:00:00.001+09:00

Excel(엑셀)에서 각 달(month)의 마지막 날짜를 알아내는 방법이다.

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

[Flash] http://infosys.iptime.org/for_cantips/attach/1293946117.swf

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

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

[Excel(엑셀)] 피벗 테이블의 빈 셀을 특정 값으로 채우기

2011-01-31T01:20:00.000+09:00

MS Excel(엑셀)의 막강한 기능 중의 하나로 피벗 테이블(Pivot Table)이 있다. 잘만 쓰면 훌륭한 분석 및 보고서 작성 툴이 된다.

피벗 테이블을 통해 집계된 결과 표를 보면 데이터에 따라서 군데군데 빈 셀이 나오는 경우가 있다. 이때 이 셀을 0(영)이나 다른 특정 내용으로 채워주어야 하는 일이 있다.

[동영상 강좌]

[Excel(엑셀)] 한 셀에 두 줄 이상 입력하기와 한 줄로 다시 만들기

2011-01-29T22:02:00.000+09:00

사용 함수
- CLEAN()

셀 안에 데이터를 입력할 때 두 줄 이상 내용을 나타나게 하려면 줄을 바꾸는 곳에서 [Alt]+[Enter] 조합을 사용하면 된다. 이런 식으로 미리 입력된 데이터를 한 줄로 모두 바꾸려면 아래 그림과 같이 CLEAN 함수를 이용하면 된다. 데이터 관리 차원에서는 가급적 한 셀에 두 줄 이상을 입력하지 않는 것이 좋다.

[Flash] http://infosys.iptime.org/for_cantips/attach/1373786733.swf

전(全) 버전에서 사용할 수 있다.