abyul.com > Excel > Question & Answer - 엑셀 수식 질문.. _ OFFSET 활용하여 특정 범위값만 합계로 표시하기

엑셀에 대해서 궁금한 것이 있으시면 마음껏 질문해주세요.

이 게시판은 아별닷컴 회원만 질문을 올릴 수 있습니다. 회원에게 주어지는 특권인셈이지요. 회원이 아닌 분들은 열람만 가능합니다.

글 수 133

엑셀 수식 질문.. _ OFFSET 활용하여 특정 범위값만 합계로 표시하기

엑셀수식 조회 수 2941 추천 수 0 2012.02.29 15:34:14

박주연 *.193.110.10 http://abyul.com/zbxe/105277

안녕하세요... 이런 경우는 어떤 함수로 풀어야할지 모르겠어서 글을 이렇게 올립니다.

엑셀파일을 보시면 A1에 일수가 있는데요

하나만 먼저 예를 들어보면 A2의 값9일과 1행에 있는 일수와 일치하여야 합니다..

허나 9일과 11일로 같지가 않죠..

그래서 10일과 11일의 금액을 9일의 금액과 합치는 수식! 이게 필요한 겁니다..ㅠㅠ

그럼 9일은 77,645+28,267+2,290 = 108,202가 되겠죠..

쉽게.. 설명해주셨음 합니다..ㅠㅠ 고수님들 부탁드려요

이 게시물을...

예제-1.xlsx [File Size:48.0KB/Download:22]

엮인글 :: http://abyul.com/zbxe/105277/f2a/trackback

2012.02.29 18:08:06
*.104.126.21

안녕하세요?

아별툴 깔고 나서 글이 안써진다고 하셨었는데..

그 문제는 잘 해결이 되셨는지요.. ^-^a

첨부파일 열어보고.. 화들짝~ 놀랐습니다.

데이터가 좀 많네요..

일단 만들긴 했는데요..

쉽게 설명해달라고 하셔서.. 고민입니다. ㅎㅎ

어떻게 쉽게 설명을 해야할지.. ㅠㅠ

일단 첨부파일을 다운 받으세요..

다운받기 : abyul.com_20120229_OFFSET-1.xlsx

abyul.com_0000340.jpg

음..

기본 로직은.. 음.. 음.. ㅎㅎ 음.. 쉽게.. 쉽게.. 음.. ^_^;;;;;

1. 새로운 시트를 만듭니다.

2. 원본 시티의 A열의 일수는 그냥 갖고 와요..

새로운 시트의 A1셀을 선택하고 =원본!A1 라고 입력한 다음.. 복사하고..

아래쪽에 쭉 붙여넣기 하시면 원본시트의 A열을 그대로 갖고 오겠죠.

3. 비슷한 방법으로 첫번째 행의 값도 원본 시트의 값을 그대로 갖고 와요..

A1셀을 선택하고 복사해서.. B1셀부터 DC1셀에 붙여넣으면 되겠죠?

4. 이제부터가 문제인데요.. ^_^;;

B2셀에 아래 수식을 입력하신 다음..

=IF(B$1<$A2,원본!B2,IF(B$1>$A2,"",SUM(원본!$B2:$DC2)-SUM(OFFSET(원본!$A2,0,1,1,원본!$A2-1))))

B2셀을 복사하신 다음에.. B2부터 DC114셀까지 한번에 선택해서 붙여넣기 하시면 됩니다.

5. 끝~ ㅎㅎ

쉽죠? ^__^;;;;

음.. 음..

문제는 저 수식을 어떻게 만들었느냐는 것이겠지요.. 쿨럭~

문제의 수식..

=IF(B$1<$A2, 원본!B2, IF(B$1>$A2, "", SUM( 원본!$B2:$DC2 ) - SUM( OFFSET( 원본!$A2 , 0 , 1 , 1 , 원본!$A2-1 ) ) ) )

수식을 요소별로 끊어서 보겠습니다.

=IF(B$1<$A2, 원본!B2,

IF(B$1>$A2, "",

SUM( 원본!$B2:$DC2 ) - SUM( OFFSET( 원본!$A2 , 0 , 1 , 1 , 원본!$A2-1 ) ) ) )

위 수식을 간략하게 보면..

=IF( 열 순번이 일수보다 작으면 , 원본 그대로 갖고옴,

그렇지 않고 열 순번이 일수보다 크면, 공백""으로 만듬 ,

이도 저도 아니면.. 순번이 일수와 같으면.. , 행 전체 합계에서 1번 순번부터 일자전 순번까지의 합계를 빼줌 )

다른것들은 대충 이해가 가실테고..

OFFSET 함수가 문제인데요..

OFFSET함수의 사용법은 아래와 같습니다.

=OFFSET( 기준셀, 몇번째 아래 행, 몇번째 오른쪽 열, 그 위치에서 행의 개수, 그 위치에서 열의 개수 )

원 수식의 OFFSET 부분을 보면..

OFFSET( 원본!$A2 , 0 , 1 , 1 , 원본!$A2-1 )

원본시트의 A2셀이 기준 점이 되고..

몇번째 아래 행이 0이니까.. 같은 행이니까.. 여전히 A2셀이고..

몇번째 오른쪽 열이 1이니까.. 바로 옆셀.. 즉 B2셀이 다시 기준이 되고..

그 위치에서.. 행의 개수가 1이니까.. B2가 되고..

그 위치에서.. 열의 개수가 원본시트의 A2값의 -1한 값이니까.. 일수인 9 -1 한 값.. 즉 8이 됩니다.

최종 위치인 B2셀에서 열의 개수가 8만큼 확장되니까.. B2:I8이 되겠죠..

그 범위를 합합니다.

SUM(OFFSET( 원본!$A2 , 0 , 1 , 1 , 원본!$A2-1 ))

아.. 이해가 되셨으면 좋겠어요.. ㅠ_ㅠ

» 편집 도구모음 건너뛰기

하이퍼링크

새 창으로
특수문자 삽입
선택한 기호
찾기/바꾸기
- 찾기
- 바꾸기
찾을 단어

찾을 단어
바꿀 단어

칸 수 지정

행
열

표 속성 지정

테두리 굵기
테두리 색
표 배경색

문서 첨부 제한 : 0Byte/ 2.00MB
파일 제한 크기 : 2.00MB (허용 확장자 : *.*)

비밀글 기능

List of Articles

번호	제목	글쓴이	날짜	조회 수
공지	공지 [공지] 아별닷컴의 엑셀 질문방 폐쇄합니다. 카페 질문방 이용하세요..	아별	2015-04-23	41451
33	엑셀수식 indirect ,address함수 관련 문의 [4]	부루끄	2012-05-14	3162
32	엑셀수식 조건에 맞는 값들을 한 셀 안에 ,(콤마) 붙이면서 나열하는 방법은? [3]	성투은자	2012-04-26	5071
31	VLookup 수치를 넣으면 자동으로 그에 상응하는 문자나 숫자가 나타나게 하려면.. [4]	만쥬	2012-04-25	3218
30	SUMIF 일자와 연동되는 누적합계 만들기 _ SUMIF [4]	진주22	2012-04-24	5177
29	엑셀수식 합계액 오류 _ 단수차 보정 방법 [3]	Editor	2012-04-03	4221
28	엑셀수식 직급, 차수별 교육일정을 자동으로 만드는 방법을 알고 싶네요 [5]	읍내꽃미남	2012-03-29	2153
27	VLookup 자동차세계산법 _ 등급이 있는 기준표를 VLOOKUP으로 참조하는 방법.. 00이상~00미만 : A [6]	현대자동차	2012-03-28	3536
26	엑셀수식 조건 함수 [2]	블루짱이	2012-03-15	2087
25	엑셀수식 데이터 통합에 대한 질문 드려요ㅜㅜ [4]	훙훙쿵	2012-03-15	2214
24	엑셀수식 함수 질문 합니다. [2]	블루짱이	2012-03-14	1834
23	엑셀수식 엑셀 배열함수 질문입니다. [2]	북곰	2012-03-14	1952
22	날짜/시간 수식 엑셀질문입니다 [2]	현대자동차	2012-03-13	2123
21	엑셀수식 찾기/참조 함수인지 아니면.... [2]	북곰	2012-03-12	2117
20	VLookup 멀고도 먼 길이여 _ OFFSET을 활용하여 조건별 현황 뽑기 [2]	빛의행운아	2012-03-02	2759
»	엑셀수식 엑셀 수식 질문.. _ OFFSET 활용하여 특정 범위값만 합계로 표시하기 [1]	박주연	2012-02-29	2941
18	VLookup 하 ~.~ vlookup 이여 _ VLOOKUP과 MATCH 활용하여 값 불러오기 [11]	빛의행운아	2012-02-28	3859
17	엑셀수식 ㅠ 그렇군요. _ 수식의 기본원리.. 이해.. 함수식이란? 인수와 문장부호 [5]	빛의행운아	2012-02-17	3823
16	엑셀수식 기본적인 질문인데 답변부탁드립니다. - 십원(10원) 단위 절사 방법 [1]	매직	2012-02-17	2449
15	IF수식 특정조건에 만족이 되면 끝에 문자를 추가하는 방법 [3]	부루끄	2012-02-02	3200
14	IF수식 조건 함수문의 [2]	블루짱이	2012-01-17	2493