자동화된 스프레드시트를 생성해야 하는 프로젝트가 있습니다. 첫 번째 시트에는 개인이 수동으로 입력한 데이터가 포함됩니다. 예를 들어, 사용자가 XX/XX/XX 날짜에 받은 금액 중 X 금액을 입력한다고 가정해 보겠습니다(Excel의 열에 텍스트를 사용하고 구분한 다음 탭과 공백을 클릭하고 완료).
11/19/2015 11/20/2015 11/25/2015 11/30/15 1/2/2016
Apples 4 5 2 7 1
Bananas 6 4 5 7 3
Oranges 3 0 4 0 0
이제 이것은 모두 사용자가 입력한 데이터입니다. 이 사용자가 입력한 데이터에서 주간 날짜(주를 정의하는 토요일인 날짜)와 해당 주에 받은 모든 항목의 합계를 표시하는 두 번째 시트가 필요합니다. 사용자가 입력한 데이터를 표시하려면 시트 2가 필요합니다.
11/21/2015 11/28/2015 1/2/2016
Total 22 11 4
Apples 9 2 1
Bananas 10 5 3
Oranges 3 4 0
알 수 있듯이 2015년 11월 19일과 2015년 11월 20일은 2015년 11월 21일 주에 해당하므로 2015년 11월 21일은 하나의 셀에만 표시되고 해당 주의 모든 항목이 합산됩니다. 사용자가 입력한 날짜는 언제든지 다를 수 있으므로 2016년 1월 2일을 포함했습니다.
이제 합계 공식을 알아내는 것이 더 간단해졌을 텐데, 문제는 주간 날짜를 한 번만 표시하는 것입니다. 발생할 수 있는 일 중 하나는 사용자가 실수로 같은 날짜를 두 번 입력하는 것입니다. 저는 다음 공식을 사용하여 이를 확인했습니다.
=IF(IFERROR(MODE.SNGL(1:1)>=1,0),"CHECK DATES",__________)
첫 번째 주간 날짜 셀은 사용자가 처음 입력한 날짜에 응답하므로 쉽습니다. 그건 그저:
=IF(IFERROR(MODE.SNGL(1:1)>=1,0),"CHECK DATES",B1-weekday(B1)+7)
따라서 문제는 다음 날짜가 2015년 11월 21일을 다시 표시하지 않도록 하는 것입니다.
업데이트 Google 검색을 한 후 내 문제는 기본적으로 지정된 숫자보다 k번째로 큰 숫자를 찾는 것과 같습니다. 다행히도 유용한 정보를 제공하는 웹사이트가 많이 있었습니다. 첫 번째 주간 날짜 다음에 주간 날짜를 표시하기 위해 제가 생각해낸 결과 수식은 다음과 같습니다.
=IF(IFERROR(MODE.SNGL(1:1)>=1,0),"CHECK DATES",IFERROR(LARGE(1:1,COUNTIF(1:1,">"&B17))-WEEKDAY(LARGE(1:1,COUNTIF(1:1,">"&B17)))+7,""))
또한 이 수식을 사용하면 마지막 주 날짜 다음의 셀에 ""를 표시할 수 있으며 원하는 경우 #N/A 값을 표시하도록 수정할 수 있습니다. 이제 적절한 합산으로 넘어갑니다.
답변1
귀하의 질문은 "세 번째 셀부터 시작하는 수평 배열의 모든 일곱 번째 셀 값을 원합니다."로 해석하고 있습니다.
이 경우 A2 셀에 다음 수식을 사용하고 행 2를 채울 수 있습니다.
=INDEX($A1:$J1,1,7*(B1-$A1)-4)
다음 사항을 확인하세요.
A: 입력 데이터가 Excel에서 텍스트가 아닌 날짜로 인식되는지 확인하세요. 작동하려면 입력한 날짜를 현지 형식(예: dd/mm/yy)으로 변경해야 할 수도 있습니다.
B: $A1:$J1 범위를 데이터 범위의 전체 너비로 확대합니다.