정렬 시 Excel의 데이터와 함께 데이터 유효성 검사 규칙이 이동되지 않습니다.

Question

Excel에서 테이블이나 범위를 정렬할 때 셀 값과 굵게, 기울임꼴, 색상 등과 같은 기본 서식은 이동하지만 다음 항목은 이동하지 않습니다.

• 테두리
• 데이터 유효성 검사 규칙

내 스프레드시트에는 셀별 데이터 유효성 검사가 있으므로 정렬 작업을 수행하면 값이 이동하지만 유효성 검사는 수행되지 않습니다.

정렬 전의 예는 다음과 같습니다.

그리고 정렬 후(AZ는 "과일" 이름으로 표시됩니다.)

Excel에서 정렬 작업을 수행할 때 Excel에서 값 및 형식과 함께 데이터 유효성 검사 규칙을 이동하도록 하는 방법이 있나요?

편집하다:

명확히 하기 위해 여기에는 두 가지 분리 가능한 문제가 있습니다.

1. 주요 관심사: 사용자가 정렬 버튼을 클릭하거나 필터 열 제목의 정렬 옵션을 클릭하기로 결정하면 내 (복잡한!) 데이터 유효성 검사 규칙이 폐기됩니다.
2. 두 번째 우려 사항: 그럴 것입니다.멋진내장된 Excel 정렬 버튼을 사용하여 워크시트를 정렬할 수 있는지 여부(이것이 데이터를 정렬하는 가장 확실한 방법입니다). 정렬을 수행하기 위한 별도의 매크로는 약간 해킹된 것 같습니다.

편집하다:

최종 해결책은 통합 문서를 보호하고 모든 시트에 대한 정렬을 비활성화하여 정렬을 완전히 비활성화하는 것이었습니다. "정렬" 버튼을 누를 수 있지만 아무 작업도 수행되지 않습니다.

추가 보호 조치로 통합 문서를 열 때마다 통합 문서 보호를 다시 적용하고 사용 지침 및 경고(예: "이 항목을 정렬하지 마세요!")가 포함된 워크시트를 표시하는 VBA를 추가했습니다. -언제든지 통합 문서를 보호하므로 이것이 완벽하지는 않지만 내가 할 수 있는 최선이라고 믿습니다.

Answer 1

pH 값을 처리하기 위한 일반 규칙을 만들고 이를 값의 전체 열에 적용할 수 있습니다. 값이 pH인지 확인하기 위해 Key1을 확인하는 조건을 포함하면 됩니다. 그렇다면 기준을 확인하세요. 그렇지 않은 경우 TRUE를 반환하면 무엇이든 허용됩니다.

C2:C6예를 들어 다음은 테이블의 (값 1 열) 에 적용되는 사용자 지정 데이터 확인 규칙입니다 .

=IF(B2="pH",AND(C2>=0,C2<=14,C2=INT(C2)),TRUE)

이는 pH 값을 0<=pH<=14로 제한하고 정수만 제한합니다. 규칙은 전체 열에 적용되므로 정렬은 데이터 유효성 검사에 영향을 미치지 않습니다.

조건부 서식을 사용하여 테두리를 적용하면 유사한 방식으로 테두리를 처리할 수 있습니다. 다음과 같은 적절한 수식 규칙을 사용하여 열의 모든 데이터에 적용하면 됩니다.

=B2="pH"

Answer 2

오래된 코드를 보면 유효성 검사기를 삭제하고 다시 만든 것으로 보입니다.

var cellValues = activeSheet.Range[leftColumn + startingRow, Type.Missing];
cellValues.Validation.Delete();

Excel에서 프로그래밍 방식으로 작업을 수행하려면 매크로 기록 > 수동으로 작업 수행 > 매크로 기록 중지 > Alt + F11을 누르고 새로 생성된 모듈의 코드를 확인하세요. 이는 VBA 프로그래밍을 빠르게 익히는 방법입니다. 행운을 빌어요!

Answer 3

이것은 귀하의 질문에 대한 완전한 답변은 아니지만 언급되어야 한다고 생각합니다.

스프레드시트에 데이터를 입력하는 방식은 다소 일반적이지 않으며, 특별한 이유가 없다면 스프레드시트의 디자인을 약간 변경하여 문제를 해결할 수도 있습니다. 귀하의 데이터와 같은 데이터를 입력하는 가장 간단한 방법은 키당 하나의 열(색상, pH, 직경 등)과 과일당 하나의 행을 갖는 것입니다. 이렇게 하면 전체 열에 대한 규칙과 스타일을 정의할 수 있고 정렬에 대해 걱정할 필요가 없습니다.

키와 값을 사용하여 데이터에 액세스해야 하는 경우 피벗 테이블 및 피벗 테이블 조회(GETPIVOTDATA) 또는 행/열 조회(VLOOKUP / HLOOKUP)를 사용하면 도움이 될 수 있습니다.

귀하의 경우 키는 꽤 잘 정의된 것으로 보이며(그렇지 않으면 키-값 접근 방식을 사용해야 하는 이유) Excel에서 수천 개의 열을 허용하므로 키가 부족하지 않을 것입니다. 각 키에 별도의 열을 사용하는 것도 일종의 입력 형식으로 작동합니다. 해당 값에 대한 셀은 비어 있으므로 과일 값을 입력하는 것을 잊지 마세요.

여러 개의 샘플이 있는 경우(모든 사과의 직경이 동일하지는 않음 등) 스프레드시트의 각 행을 단일 샘플로 처리할 수 있습니다.

이 접근 방식은 다이어그램 및 피벗 테이블 생성과 같은 데이터의 추가 처리도 용이하게 합니다.