먼저 이것보다 아주 쉬운 방법이 있다면 알려주세요ㅠㅠ
찾다가 도저히 안되서 제가 꼼수로 생각한 방법 이거든요...
보통, 붙여넣는 시트의 셀에 ='A시트'!A1 입력하고 복사해서 연속적인 셀에 붙여넣으면
='A시트'!A2, ='A시트'!A3, ...으로 자동으로 채워주는 편리한 기능이 있는데 이게 도움되지 않는 경우가 있습니다.
바로 참조해야 할 셀의 간격이 다른 경우입니다. 이해하기 쉽게 아래의 그림으로 설명 드리겠습니다.
'1월 보고용'시트의 해당 일에 맞는 합계를 띄엄띄엄하게 있는 '1월 백데이터'시트에서 가져와야 하는데요.
'1월 보고용' 시트의 B2, B6, B10, B14에 각각 '1월 백데이터' 시트의 D5, D10, D15, D20값이 들어가야 한다는 거죠.
그런데 무작정 붙여넣게 된다면 아래 그림처럼 D5, D10, D15, D20값이 아닌
'1월 보고용'시트 셀 위치기준으로 D5, D9, D13, D17값이 자동으로 채워지게 됩니다.
이걸 어떻게 해야하나 싶다가
'1월 보고용'시트에서 행이 2일때, 참조 시트 행에서는 5
'1월 보고용'시트에서 행이 6일때, 참조 시트 행에서는 10
'1월 보고용'시트에서 행이 10일때, 참조 시트 행에서는 15 ....
이렇게 연속하는 두 항의 차이가 모두 일정한 등차수열이 보이니, 등차수열의 관계를 나타내는 점화식으로 풀어보자라는 생각을 하게되었습니다.
a_(4n+2)=5(n+1)에 n=(k-2)/4를 대입하면, a_k=5(k/4+1/2)로 나오게 되고 깔끔하게 정리하면
위와 같은 점화식을 얻을 수 있습니다.
이 식을 엑셀에서 참조 값 반환하는 함수 인, INDIRECT 함수를 아래와 같이 이용해서 각 해당하는 셀값에 붙여넣습니다.
=INDIRECT("1월백데이터!D"&5*(ROW()+2)/4)
그러면 아래와 같이 올바른 값으로 반환되는 것을 볼 수 있습니다.
이상으로 설명을 마칩니다.
p.s. 고2때 배웠던 내용을 회사에서 써먹을 줄은 꿈에도 몰랐네요ㅋㅋ
'모로가도 서울만 가면 된다'라는 말이 정말 공감되는 하루였습니다
봐주셔서 감사합니다!
2, 6, 10, 14, ...인 순서의 수들만 출력하는 함수겠네요
함수일 뿐인데
뜬금없이 비주얼베이직이 생각나는
저는 이럴때 보통 vlookup을 씁니다. 키값을 a 열에 추가하고 끌고 오면 데이타 시트가 변경되거나 간격이 일정하지 않아도 항상 같은 결과를 가져오거든요.
Index/match 를 같이 쓰시면 됩니다.
말씀하신 수열도 정형화 된 것이니 쓰셨겠죠?
예를 들면, 보고용 B2는
=INDEX(‘1월 백데이터’!$A:$D,MATCH(A1,‘1월 백데이터’!$A:$A,0)+4,4)
Index가 (array, row, col) 순서이고
Match로 A컬럼의 날짜를 찾아서 row 에 +4를 하고,
Col은 합계 컬럼이 D이니, 4로 하면 됩니다.
P.s1 어제 침대에서 봐서, 확인차 회사에서 월급 루팡하며 해봤네요^^
P.s2 근데 저라면, 나중에 혹시 실수가 생길 수 있으므로 백데이터의 합계 앞 a컬럼을 날짜로 채워놓고 칸을 줄여놓을 것 같습니다. 그런뒤에 vlookup을 쓰죠..
나중에 정형이 흐트러지거나, 혹시나 cut-paste로 인해서 함수가 망가지면, 보고서 데이터가 제대로 안나올테니, 최대한 적은 오류가 나도록 할 것 같습니다..
=LET(range1,'1월 백데이터'!A1:A100,range2,'1월 백데이터'!D1:D100,space,3,
de,TEXT(range1,"yyyy-mm-dd"),sparr,LAMBDA(txt,MAKEARRAY(space,1,LAMBDA(r,c,IF(r=1,txt,"")))),rcol1,VSTACK(DROP(REDUCE("",FILTER(de,IFERROR(DATEVALUE(de),0)>30000),LAMBDA(a,c,VSTACK(a,c,sparr("합계")))),1),""),rcol2,REDUCE("",FILTER(range2,range1="합계"),LAMBDA(a,c,VSTACK(a,c,sparr("")))),HSTACK(rcol1,rcol2))
원본데이터가 일정한 간격으로 있지 않고 불규칙하게 있어도, 알아서 값을 찾아오는 수식입니다.
입력을 임의로 100줄까지만 잡았는데, 입력 범위를 더 늘릴수도 있고..
space 3 설정한 것도, 더 늘리면 출력 간격 늘어나요.
날짜 기준은 약 82년 이상인 숫자 데이터로 잡았고(3만), range1에서 합계가 적혀있는 셀의 range2값을 가져옵니다.
근데 원본 데이터는 본문처럼 한 레코드를 여러줄로 저장하지 말고, 되도록이면, row 한줄마다 레코드 하나씩 깔끔하게 넣는게 좋아요.
1. '1월 백데이터 시트' A열에 행을 추가합니다.
2. 새롭게 추가된 A행 기준으로, A5에 B1(기존 A1셀)의 날짜를 참조시킵니다.
3. A10, A15셀에 A5의 참조된 수식을 복붙 합니다.(A5, A10, A15셀에 각각의 날짜가 참조됨)
4. '1월 보고용' 시트 B2셀에 '=VLOOKUP(A1, '1월 백데이터'!A:E, 5, FALSE)'수식을 입력합니다.
5. 나머지 합계 셀에 위의 수식을 복붙 합니다.
1월 백데이터 시트에 행을 하나 추가하긴 하지만 복잡한 함수 없이 가능합니다.
A행 추가한건...숨기기로 처리하면 깔끔하지 않나유?