Excel에서 셀 참조가 있는 테이블을 정렬하려면 어떻게 해야 합니까?
저는 다른 많은 시트에서 다양한 셀을 참조하는 시트 1의 엑셀 데이터 표를 가지고 있습니다.시트를 정렬하거나 필터링하려고 하면 셀이 이동할 때 참조가 변경됩니다.그러나 수동으로 각 셀에 들어가서 $ 기호를 모든 곳에 삽입하고 싶지는 않습니다. 일부 참조가 연속적이고 나중에 자동으로 채우기를 원할 수도 있기 때문입니다.
예를 들어, 데이터 열 중 하나에서 시트 2, 열 B 23:28, 시트 2, 열 C 1:15 등을 사용합니다.시트 2의 열 B를 사용하는 열의 섹션은 다른 섹션과 마찬가지로 정렬 전에 모두 같은 위치에 있습니다.$ 기호를 삽입하면 더 이상 빈 행을 삽입할 수 없고 새 데이터에 자동 채우기를 사용할 수 없습니다.
요약하자면, 저는 $ 기호를 사용하지 않고 정렬/필터링 목적으로만 셀 참조를 고정하는 방법을 원합니다.현재 해결 방법에는 표 값을 복사하고 빈 시트에 붙여넣은 다음 정렬하는 작업이 포함됩니다.더 좋은 방법이 있었으면 좋겠습니다.
나는이해수있꽤다확니합신고다결될것이▁the▁with다▁can▁be▁solved▁i▁sure니확합▁this신▁pretty'로 해결될 수 있다고 확신합니다.indirect()과 같습니다.단순화된 스프레드시트는 다음과 같습니다.
A B C D ...
+------------------------------------------------------+- - - - - - - - -
1 |CITY |Q1-Q3 SALES|ANNUALIZED SALES:(Q1+Q2+Q3)*1.33|
+======================================================+- - - - - - - - -
2 |Tampa | $23,453.00| $31,192.49|
+------------------------------------------------------+
3 |Chicago | $33,251.00| $44,223.83|
+------------------------------------------------------+
4 |Portland | $14,423.00| $19,182.59|
+------------------------------------------------------+
...| ... | ... | ... |
일반적으로 셀 C2의 공식은 다음과 같습니다.=B2*1.33복잡한 정렬을 하기 전까지는 잘 작동합니다.셀의 행하여 셀 합니다.=indirect("B"&row())*1.33.
그것이 당신의 상황에서 효과가 있기를 바랍니다.그것은 제가 겪고 있던 비슷한 문제를 해결했습니다.
나에게 이것은 아래와 같이 작용했습니다.
저는 동일한 시트에 대한 공식의 시트 이름 참조를 받았습니다.공식에서 현재 시트 이름을 제거하고 정렬했을 때 제대로 작동했습니다.
몇 가지 옵션이 있습니다.
정적 참조를 사용하지 않을 때 셀 참조를 정렬할 때 셀 참조가 흐트러지는 것을 피할 수 없습니다.이 문제를 해결하는 가장 기본적인 방법은 정렬하기 전에 값으로 복사하여 붙여넣기만 하면 되며, 이는 간단한 VBA 매크로를 통해 자동화될 수 있습니다.
수식에서 여러 공통 범위를 사용하는 경우 명명된 범위를 사용할 수도 있습니다.'Sheet2!'를 정의할 수 있습니다.B23:28'을 'Range1'로 표시하고 공식 내에서 'Range1'을 참조합니다.이 경우 정렬은 다른 곳에서 정의되므로 지정된 범위에 영향을 주지 않습니다.
HTH
절대 참조가 있더라도 정렬은 참조를 올바르게 처리하지 못합니다.상대 참조는 새 행 위치에서 동일한 상대 오프셋을 가리키도록 이루어지며(다른 행이 동일한 상대 위치에 있지 않기 때문에 분명히 잘못됨), 절대 참조는 변경되지 않습니다(SORT는 행의 각 재배열 후 절대 참조를 변환하는 단계를 생략하므로).이를 위한 유일한 방법은 (참조를 절대로 변환한) 행을 하나씩 수동으로 이동하는 것입니다.그런 다음 Excel은 필요한 참조 번역을 수행합니다.Excel SORT는 이 작업을 수행하지 않기 때문에 부족합니다.
가장 좋은 방법은 참조 데이터를 시트의 한 쪽에 보관하고 모든 공식을 시트의 다른 쪽에 보관하는 것입니다.그런 다음 두 열 사이에 공백 열을 두고(원하는 경우 숨깁니다) 공식 참조가 항상 같은 위치를 가리키도록 유지하면서 참조 데이터만 정렬합니다.단점은 당신이 정렬할 때마다 Excel이 다시 호출된다는 것입니다.
저도 이 문제에 어려움을 겪었고 최선의 가장 쉬운 해결책을 찾았습니다.
- 만들려는 데이터를 테이블로 복사한 다음 정렬합니다.
- 스프레드시트의 다른 위치에 붙여넣기
- 셀 참조로 인해 값이 변경되지만 이를 원하지 않으므로 팝업을 클릭하여 '특별 붙여넣기'를 수행하고 '값 붙여넣기'만 선택합니다.
- 새로운 데이터를 표로 변환하여 생활을 정리할 수 있습니다!!!
효과가 있기를 바랍니다.안부 전해요.
셀을 참조와 함께 정렬해야 했고 작업할 값을 붙여넣는 것을 피해야 했습니다."피벗 테이블"이 그 묘기를 부렸습니다.
- 참고 자료가 있는 테이블을 준비합니다.
- 참조가 있는 테이블을 선택하고 피벗 테이블 삽입
- 피벗 테이블에서 필요한 필터를 선택하여 피벗 테이블이 원래 테이블처럼 보이도록 합니다(필요한 경우).
- 필요에 따라 데이터를 추가로 정렬/필터링합니다.
피벗 테이블을 마우스 오른쪽 버튼으로 클릭하고 일부 일반 데이터(테이블에 사용됨)를 변경할 때마다 "새로 고침"을 누르십시오.
도움이 되길 바랍니다.안드레이
우리는 또한 같은 문제로 어려움을 겪고 있습니다.
해결 방법으로 매크로를 사용하여 테이블을 목록화하고 정렬한 다음 테이블로 다시 목록을 숨깁니다.
여기 샘플 매크로를 찾으십시오.
Sub Sort_Table()
'change table name to "table1"
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$c$4"), , xlYes).Name = _
"Table1"
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
Dim oSh As Worksheet
Set oSh = ActiveSheet
'remove table or list style
oSh.ListObjects("Table1").Unlist
'Sort List
Range("B2").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A4"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:C4")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Change list back to table again
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$c$4"), , xlYes).Name = _
"Table1"
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub
저도 같은 문제가 있었습니다. 제 워크북의 다른 워크시트에 대한 정보를 요약한 마스터 시트가 있었습니다.
데이터가 저장된 워크시트에서 필터링/정렬한 다음 데이터를 원래 상태로 되돌리려면 필터링/정렬 기준에 관계없이 첫 번째 열을 라인 항목 번호로 지정합니다.
초기 필터/정렬 후에는 "라인 항목 번호"를 사용하여 모든 항목을 정상으로 되돌릴 수 있습니다.참고: 이 기능은 항상 새 행을 순서대로 목록 끝에 추가하는 경우에만 작동합니다.
매우 간단한 방법은 문제 열을 필터링된 데이터에서 제외하고 셀 참조("='cell')가 포함된 열을 필터링된 섹션에 추가하는 것입니다.
이렇게 하면 문제 공식은 그대로 유지되지만 필터링된 데이터는 올바르게 참조하므로 셀을 사용하여 적절히 정렬할 수 있습니다.
다음 방법을 사용해 보십시오.
- 정렬할 셀의 왼쪽에 열을 삽입합니다.
- 콜 B:G가 B를 기준으로 정렬된다고 가정합니다.
- 복사열 B
- 값을 A열에 붙여넣기
- 강조 표시 색상 B:g
- = 기호를 # 기호로 교체합니다.
- A를 기준으로 A:G 색상 정렬
- 강조 표시 색상 B:g
- # 기호를 = 기호로 대체
- A열을 삭제합니다.
- Voila!
이것이 모두를 만족시킬 수 있을지 확신할 수 없지만, 저는 문제를 해결할 수 있는 간단한 해결책을 찾았습니다.정렬할 테이블이 있는 시트의 셀을 참조하지 않도록 참조하는 모든 셀/식을 다른 시트로 이동합니다.이렇게 하면, 여러분은 분류할 수 있습니다!
이것은 다른 사용자의 정렬이 어떻게 작동하는지 파악하기 위한 하나의 좋은 답변이며, 어떻게 완전히 정확하지 않은지와 무엇이 정확한지 알기 위해 메모를 추가하겠습니다.
정렬 후 수식에 미치는 영향은 복사와 동일합니다.정렬은 행 내용을 이동하지 않고 복사합니다.공식은 복사된 공식과 마찬가지로 새 데이터를 사용할 수도 있고(또는 기준/상대 참조에 따라 달라지지 않을 수도 있습니다).
제 요점은 공식을 한 행에서 다른 행으로 복사할 수 있고 효과가 변경되지 않으면 정렬이 공식 결과에 영향을 주지 않는다는 것입니다.수식이 너무 복잡하고 위치에 따라 달라져 복사하면 상대적인 내용이 변경되는 경우에는 정렬하지 마십시오.
그리고 제가 실제로 경험한 메모는 다음과 같습니다.
위의 사용자는 옳다고 말하지만 실제로는 몇 가지 예외가 있습니다: 시트 이름을 포함하는 열 공식의 일부입니다(예: 시트 1!A1)은 절대 참조로 취급됩니다(상대적인 경우 참조를 변경하는 복사에도 불구하고). 공식의 일부는 공식의 위치 변경과 관련하여 참조를 변경하지 않고 복사됩니다. 여기에는 완전히 다음과 같이 주소가 지정된 현재 시트 셀이 포함됩니다: sheet1!A2 및 절대 참조로 처리될 것입니다(정렬 전용). 저는 엑셀 2010의 이것을 테스트했고 다른 버전에서는 이 문제가 해결되지 않을 것 같습니다.해결책은 특수 파일을 복사하고 다른 위치에 값으로 붙여넣은 다음 정렬을 사용하는 것입니다.
저의 대답은 데이터를 빈 열을 사이에 두고 두 개의 그리드로 분리하는 것이었습니다.왼쪽의 그리드는 정렬할 수 있는 데이터이고 오른쪽의 그리드는 계산할 공식을 포함합니다.정렬할 때 수식은 왼쪽 그리드의 행에 있는 데이터에만 적용되며 행 참조가 정렬별로 혼동되지 않습니다.
가장 쉬운 방법은 OFFSET 기능을 사용하는 것입니다.따라서 원래 예제의 공식은 다음과 같습니다. =offset(c2,0,-1)*1.33 ="현재 셀(c2)을 기준점으로 사용하여 동일한 행에 셀의 내용을 가져오지만 왼쪽(b2)에 한 열을 곱하면 1.33이 됩니다.
효과가 좋습니다.
"다른 이름으로 저장"을 사용하여 msdos 텍스트 형식으로 시트를 새 파일로 복사했습니다.이렇게 하면 수식이 제거되고 셀 내용이 계산된 값으로만 바뀝니다.그런 다음 탭 구분으로 새 파일을 열고 열을 정의한 후 정렬합니다.각 목적지의 마일리지를 합산할 수 있도록 마일리지 로그에 대한 관련 텍스트 문자열(목적지)별로 계산된 값을 정렬해야 했습니다.
날짜 시작 종료 거리 목적지
여기서 2행 및 연속 행 시작은 이전 행 끝이고 거리는 - 시작으로 끝납니다.
두 개의 탭을 만듭니다. 하나는 공식으로 만들고 다른 하나는 전체 테이블을 연결하는 데 사용합니다.정렬할 때 두 번째 탭에 문제가 없어야 합니다!
저는 여기서 이유를 찾고 싶었지만, 저는 우리가 만드는 것보다 답이 더 간단하다고 생각합니다.
셀은 워크북에서 참조하는 페이지에 관계없이 정렬해야 합니다.문제의 원인은 현재 페이지의 페이지 이름 참조입니다.
EX: 저는 오늘 워크북에 있으며, 다른 페이지에 있는 영업 담당자의 데이터를 참조하고 있습니다.
내 기준(담당자 이름)이 오늘에 있는 경우!C1(또는 $C1, $c$1)과 정렬하려고 하면 시트가 동작을 인식하지 못합니다.그러나 현재 페이지를 참조하는 이름(중복 참조)을 제거하면 이 문제는 중지됩니다.
그래서 SUMIFS('Sales'!C1, '판매'!A1, 오늘!C1)은 이제 SUMIFS('영업')가 될 것입니다!C1, '판매'!A1,C1)
만약 누군가가 그것이 왜 그런 식으로 작동하는지 알 수 있다면, 그것은 놀라운 일일 것입니다.
또 다른 해결책은 다음과 같습니다. (1) 전체 테이블 - 붙여넣기를 동일한 스프레드시트의 링크로 복사하고, 이제부터는 '연결된 테이블'에 대해서만 작업합니다. (2) 정렬할 값이 있는 열을 복사하고, 정렬할 테이블 바로 옆에만 값을 붙여넣습니다. (3) 테이블을 선택하고 모든 =을 #로 대체합니다.정적 텍스트의 참조가 변경됩니다(4). 붙여넣은 값을 기준으로 테이블 정렬(5). 모든 #을 =로 대체합니다. 참조가 완료되었습니다!Excel 바로 가기를 사용하면 속도가 상당히 빠릅니다.
참조가 절대적이 되도록 수식에 시트 이름을 추가합니다.예를 들어, 셀 기준이 =T7이면 =시트1!T7. Paste-link는 다른 시트에 붙여넣을 때만 제외하고 동일한 작업을 수행했습니다.같은 시트에 붙여넣는 경우 링크 붙여넣기가 예상대로 작동하지 않습니다.
저는 Sum을 이용해서 풀 수 있었습니다.테이블에 중복되는 IF 명령어가 없습니다.기본적으로 저는 다음과 같은 공식을 가지고 있었습니다.
=SUM($C$3*0.94,$C$5*0.06)
그 다음에 대체된 것은
=SUM(SUMIF(A:A,"FIRST_UNIT",C:C)*0.94,SUMIF(A:A,"SECOND_UNIT",C:C)*0.06)
일정하게 유지할 셀의 행 및/또는 열 앞에 달러 기호를 놓습니다.
고쳐줬어요!
간편한 솔루션 - Excel에서 복사하여 Google 시트에 붙여넣습니다.처음부터 시작할 수 있도록 모든 참조 셀을 절대 셀로 복사합니다.그런 다음 Excel 그리드로 정렬하여 다시 다운로드하고 필요에 따라 다시 포맷합니다.
언급URL : https://stackoverflow.com/questions/7979923/how-do-i-sort-a-table-in-excel-if-it-has-cell-references-in-it
'programing' 카테고리의 다른 글
| 원격 컴퓨터의 디스크 용량 및 여유 공간을 얻는 방법 (0) | 2023.05.09 |
|---|---|
| .Net이 잘못된 참조 어셈블리 버전을 선택합니다. (0) | 2023.05.09 |
| Azure Container 인스턴스와 Web App for Container의 차이점은 무엇입니까? (0) | 2023.05.09 |
| 바이트 배열을 문자열로 변환하는 방법 (0) | 2023.05.09 |
| postgres에서 시퀀스를 재설정하고 ID 열을 새 데이터로 채우는 방법은 무엇입니까? (0) | 2023.05.09 |