☰

연결된 시트의 순서를 잃지 않고 사이에 빈 행이 있는 경우 수식을 복사하는 방법은 무엇입니까?

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2013 microsoft-excel-2016
연결된 시트의 순서를 잃지 않고 사이에 빈 행이 있는 경우 수식을 복사하는 방법은 무엇입니까?

두 개의 시트가 있는 Excel 파일이 있습니다.

  • 요약
  • 원시 데이터

내 요약 시트에는 다음 데이터 행마다 몇 행의 간격이 있는 이와 같은 데이터가 있습니다.

예시 데이터의 스크린샷

이제 문제는 1000개 이상의 행이 있는 시트가 있고 그 안의 내용을 변경하려는 경우 RawData 시트에 대한 참조를 계속하지 않고 셀 번호를 참조로 가져오기 때문에 각 셀 참조를 수동으로 편집해야 한다는 것입니다. 요약 시트.

첨부된 예제 그림에서 수식을 복사하여 붙여 넣으면 수동 수식 열에 표시된 대로 수식을 복사해야 하는 행 간격에 따라 셀 번호가 2, 5, 8, 11 등으로 표시됩니다. 현재 시트 행 번호에 관계없이 B2, B3, B4, B5 순서와 같습니다. 많은 것을 시도했지만 전혀 해결책을 찾지 못했습니다. 도움이 필요하세요?

혼란을 피하기 위해 RawData 탭의 스크린샷을 사용하여 내 시트에서 보다 현실적인 예를 만듭니다.

원시데이터 시트:- 원시 데이터 시트

RawData 시트의 데이터를 연결하는 링크된 시트는 다음과 같습니다. 요약 제품 시트

보시다시피, 수식을 복사하여 붙여넣으면 테스트 제품 1, 테스트 제품 6 및 테스트 제품 11이 표시되지만 이는 테스트 제품 1, 2, 3이어야 합니다.

다음은 Excel 파일 링크의 예입니다.https://we.tl/t-3F3mJra7pe

답변1

먼저 문제의 원인을 설명하겠습니다.

  • ~에요약 시트셀에서 F3수식은 =Rawdata!A2값을 반환합니다 Test Product 1.
  • 복사하자마자요약 시트B3:F7의 데이터 B8, Excel 읽기6줄셀 사이 및 셀 에서 F3:F8반환 Test Product 7B8원시 데이터시트.

그것은 전체 운동을 망치고 있었습니다.

해결책:

  • 당신은 필요도우미 칼럼~에원시 데이터시트.

여기에 이미지 설명을 입력하세요

  • B3셀 다시 쓰기요약시트 단순제품 1.
  • 다음 셀에 이 수식을 입력하세요 F3.요약시트.

=VLOOKUP(B3,RawData!A$2:E$12,2,FALSE)

당신은 이것을 얻습니다.

여기에 이미지 설명을 입력하세요

  • 지금,복사범위 B3:F7및반죽셀에 B8.

당신은 이것을 얻습니다.

여기에 이미지 설명을 입력하세요

  • B8다음 으로 셀 다시 쓰기단순제품 2, 이제 올바른 순서를 얻었습니다.

여기에 이미지 설명을 입력하세요

  • 반복하다복사 붙여 넣기교정으로간단한 제품순서대로, 당신은 원하는 결과를 얻습니다.

필요에 따라 수식에서 셀 참조를 조정할 수 있습니다.

답변2

이러한 유형의 시나리오에서는 OFFSET() 함수를 사용하는 것이 더 간단하면서도 강력한 솔루션이라고 생각합니다. 오프셋은 단일 셀 또는 셀 범위에 대한 참조를 반환합니다. 시나리오를 고려할 때 첫 번째 공식 복사 및 붙여넣기가 RawData Product1을 참조해야 하고 두 번째 복사 및 붙여넣기가 Product2를 참조하는 경우 이러한 복사 및 붙여넣기에서 건너뛴 행 수에 관계없이 다음과 같이 이 OFFSET을 구현합니다.

A B               C      D        E       F
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B1);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B6);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B11);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B16);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large

수식 "=OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B1);0)"을 복사하면 "Summary!$B$1:B1"에 대한 참조가 자동으로 동일하게 변경됩니다. 당신이 원하지 않는 행동; 그러나 해당 참조가 COUNTA() 수식 내에서 사용되면 효과가 나타나며 수천 개의 참조를 직접 변경할 필요가 없습니다. 이를 수행하면 COUNTA()는 열 시작 이후 "단순 제품"이 나타나는 횟수를 계산하고 이 내부 오프셋을 사용하여 순서 위치에서 해당 제품을 참조합니다. 보조 기둥을 만들 필요 없이 이 모든 작업을 수행할 수 있습니다. 결과는 다음과 같습니다.

A B               C      D        E       F
  Simple Product  FALSE  TRUE             TestProduct1
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct2
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct3
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct4
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large

관련 정보