시험결과 엑셀 검증 4단계 — 독립 계산·경계값·수식 보호·재검증

시험결과 엑셀 검증은 계산값의 정확성, 잘못된 입력의 처리, 수식 보호, 변경 후 기능 유지까지 확인하는 작업이다. 독립적으로 산출한 기대값과 경계값 시험을 기준으로 승인본을 확정하고, 이후 변경은 영향평가와 재검증 기록으로 관리한다.

시험결과 엑셀 검증의 범위와 합격 기준부터 정한다

검증 대상은 엑셀 프로그램 자체가 아니라 시험실이 사용하는 통합문서의 계산 기능과 업무 흐름이다. 원자료 입력, 단위 환산, 보정계수 적용, 결과 판정, 성적서 전송까지 실제 사용 범위를 먼저 정의한다.

ISO/IEC 17025:2017의 데이터 관리 요구사항을 적용한 NIST 품질매뉴얼 7.11.2와 7.11.6은 도입 전 기능 검증, 변경의 승인·문서화·검증, 계산과 데이터 전송의 체계적 확인을 다룬다. 아래 4단계는 이를 엑셀 업무에 적용하기 위한 실무 구성으로, 표준이 동일한 단계나 고정된 시험 건수를 지정한다는 뜻은 아니다. NIST 측정서비스 품질매뉴얼

검증계획에는 파일 식별번호와 버전, 시험방법, 입력 단위와 허용 범위, 계산식, 보고 자릿수, 판정 로직을 적는다. 사용하는 엑셀 버전과 계산 모드, 외부 연결, 매크로, 추가 기능도 기록해 검증한 환경을 특정한다.

합격 기준은 시험 전에 정하며 숫자 계산, 오류 처리, 판정, 출력의 기준을 구분한다. 수치 비교 허용차는 계산 정밀도와 보고 요구에 근거해 설정하고, 잘못된 단위나 판정 불일치를 넓은 허용차로 통과시키지 않는다.

시험결과 엑셀 검증 순서

1. 독립 계산 대조
   시험방법에서 기대값을 산출하고 수식·단위·참조를 대조한다.
2. 경계값 시험
   범위 경계와 잘못된 입력의 처리 결과를 확인한다.
3. 수식 보호
   입력 셀과 계산 셀을 구분하고 승인본을 통제한다.
4. 변경 후 재검증
   영향평가에 따라 변경 기능과 기존 기능을 다시 확인한다.

1단계: 독립 계산 대조로 계산식과 참조를 확인한다

기대값은 승인된 시험방법의 계산식에서 출발해 수기 계산서, 계산기 또는 별도로 구현한 계산 도구로 산출한다. 기존 엑셀 수식을 다른 시트에 복사하거나 동일한 템플릿의 결과끼리 비교하면 공통 오류를 발견하기 어렵다.

다음 수치는 검증 설계 설명용 가상 예시이며 실제 시험방법의 기준값이 아니다. 공시험 보정과 추가 희석 보정이 필요한 방법에서 결과를 X=(c−b)×D×V/m으로 정의하고, c와 b는 각각 측정용액과 공시험의 농도인 mg/L, D는 추가 희석배수, V는 추출액 부피인 mL, m은 시료 질량인 g으로 둔다.

c=2.400, b=0.100, D=5, V=50.0, m=2.000을 입력하면 보정 농도는 2.300 mg/L이고 최종 결과는 287.5 mg/kg이다. 독립 계산서에는 2.300 mg/L×5×0.0500 L÷0.002000 kg=287.5 mg/kg처럼 단위를 전개해 mL와 g의 환산이 올바른지 확인한다.

입력 셀을 B2부터 F2까지 c, b, D, V, m 순서로 배치했다면 정상 입력에 대한 계산식 예시는 =(B2-C2)*D2*E2/F2이다. 희석배수를 누락한 수식은 57.5 mg/kg을 산출하므로, 수정 후 287.5 mg/kg으로 돌아오는지와 다른 시험 사례가 유지되는지를 확인한다.

첫 행뿐 아니라 중간 행과 마지막 행, 행을 추가한 뒤의 참조도 대조한다. 공통 보정계수는 의도한 셀을 계속 참조하는지, 시료별 입력은 해당 행을 따라가는지 확인하며, 기기에서 이미 적용한 희석배수를 다시 곱하지 않도록 입력값의 정의도 검토한다.

CRM 측정 결과가 인증값 근처라는 사실만으로 계산 파일 전체를 검증했다고 판단하지 않는다. CRM 결과는 보조 근거로 활용하되, 계산 기능의 기대값은 실제 측정 변동과 분리해 확정한다.

독립 계산 예시 대조

2단계: 경계값 시험으로 입력 오류와 판정 분기를 확인한다

경계값 시험은 허용 범위의 바로 안쪽과 바깥쪽, 경계와 정확히 같은 값을 넣어 처리 결과를 확인하는 절차이다. 각 시험은 입력값, 사전 기대값 또는 기대 동작, 실제 출력, 합격 여부를 한 쌍으로 기록한다.

설명용으로 측정용액 농도 c의 허용 범위를 양 끝값을 포함한 0.100~10.000 mg/L, 입력 간격을 0.001 mg/L로 가정한다. 나머지 입력을 정상으로 고정한 뒤 0.099, 0.100, 0.101과 9.999, 10.000, 10.001을 각각 넣으면 범위 내 값은 계산을 허용하고 범위 밖 값은 보고를 보류해야 한다.

이 수치는 법정 기준이나 일반적인 정량범위가 아니며 실제 파일에는 승인된 시험방법의 범위를 적용한다. 범위 검사는 추가 희석 전후 중 어느 농도에 적용하는지 명시하고, 시험법의 정량범위와 최종 시료의 보고 범위를 혼동하지 않는다.

빈칸, 숫자처럼 보이는 문자열, 시료 질량 0, 희석배수 0도 각각 시험한다. 질량 0을 넣었을 때 나눗셈 오류를 IFERROR로 0에 바꾸면 정상 결과처럼 보일 수 있으므로, 입력 오류 상태를 표시하고 해당 결과의 보고를 차단하는 방식으로 설계한다.

공시험 보정 결과가 음수가 되는 경우는 시험방법의 처리 규칙에 따라 별도로 확인한다. 빈칸과 실제 측정값 0도 구별하며, 음수를 일괄적으로 0으로 바꾸거나 모든 0을 입력 오류로 처리하지 않는다.

Microsoft는 데이터 유효성 검사 경고가 직접 입력에는 작동하지만 복사나 채우기에는 나타나지 않을 수 있다고 설명한다. 따라서 동일한 잘못된 값을 직접 입력하는 시험과 붙여넣는 시험을 모두 수행하고, 붙여넣기 후에도 별도 오류 검사가 작동하는지 확인한다. Microsoft 데이터 유효성 검사 안내

경계값 시험의 기대 동작

※ 농도 단위는 mg/L이며 허용 범위 0.100~10.000은 설명용 가정이다. 나머지 입력은 정상으로 고정한다.

3단계: 수식 보호와 승인본 관리로 검증 상태를 유지한다

입력 셀만 잠금을 해제하고 계산식, 단위 환산계수, 기준값, 판정 셀은 잠근 뒤 시트 보호를 활성화한다. Microsoft 안내처럼 셀의 잠금 설정만으로는 보호가 작동하지 않으므로, 보호를 켠 상태에서 실제 편집 시도를 수행해야 한다. Microsoft 워크시트 보호 안내

일반 사용자 권한으로 수식 덮어쓰기, 셀 삭제, 행 삽입, 범위 붙여넣기를 시도해 허용 동작과 차단 동작을 확인한다. 업무상 행 추가가 필요하다면 추가된 행에도 계산식과 입력 검사, 보호 설정이 유지되는지 검증한다.

시트 보호는 파일 접근 통제나 변경 이력의 완전성을 보장하는 보안 기능이 아니다. Microsoft도 이 한계를 명시하므로, 데이터 무결성 관리는 저장소 접근 권한, 승인본 배포, 버전 이력, 백업과 결합한다. Microsoft 워크시트 보호 안내

검증한 원본 템플릿은 승인된 저장 위치에서 관리하고, 시험별 작업 파일에는 사용한 템플릿 버전과 원자료 식별정보를 남긴다. 변경 기록에는 변경자, 일시, 이유, 변경 전후 내용과 승인 근거를 남기며, 같은 통합문서에 직접 적는 변경 목록만으로 모든 수정이 자동 추적된다고 간주하지 않는다.

계산 모드가 의도한 상태인지 확인하고 입력을 바꾼 뒤 결과가 갱신되는지도 시험한다. 실제 사용하는 출력 경로로 성적서나 전송 파일을 만들어 시료번호, 결과, 단위, 판정이 원래 계산 결과와 대응하는지 확인한다.

4단계: 변경 후 재검증은 영향 범위에 따라 설계한다

수식 수정, 기준값 교체, 참조 시트 이동, 매크로 변경, 외부 연결 변경은 재검증 필요성을 검토할 변경이다. 엑셀 버전이나 실행 환경이 바뀌었을 때도 사용 기능에 대한 영향을 평가하고, 필요한 확인 범위를 기록한다.

먼저 변경 항목이 어느 입력과 중간 계산을 거쳐 최종 결과와 판정에 연결되는지 추적한다. 변경 기능을 직접 확인하는 시험과 기존 기능이 유지되는지 확인하는 회귀시험을 함께 선정하고, 제외한 기능에는 영향이 없다고 판단한 이유를 남긴다.

예를 들어 희석배수 참조를 수정했다면 정상 계산의 287.5 mg/kg 기대값, 희석배수 0 처리, 여러 행의 참조, 최종 출력까지 다시 확인한다. 기존 승인본과 같은 결과가 나온다는 사실만으로 합격시키지 않고, 독립 기대값 및 승인된 처리 규칙과 일치하는지를 판단한다.

표시 형식 변경도 단위나 보고 자릿수, 판정에 영향을 줄 수 있으므로 검토 없이 검증 대상에서 제외하지 않는다. 단순 안내 문구 수정처럼 계산과 출력 의미에 영향이 없는 변경은 그 근거를 기록하고 확인 범위를 제한할 수 있으며, 이 구분은 시험실이 승인한 변경관리 절차로 운영한다.

재검증 중 기존 오류를 발견하면 해당 파일의 사용을 중지하거나 승인된 대체 절차를 적용하고, 영향을 받은 버전과 사용 기간을 추적한다. 수정본의 합격과 별개로 과거 결과를 평가하며, 재계산·성적서 정정·고객 통보 여부는 확인된 영향에 따라 결정한다.

승인 시에는 변경 전후 버전, 영향평가, 시험 사례와 결과, 미해결 사항, 검토자와 승인자를 연결해 보존한다. 구버전은 신규 시험에 사용되지 않도록 배포를 통제하면서도 과거 결과를 재현할 수 있도록 보존한다.

관련해서 정도관리 반올림 규칙 설정 4단계 — 원자료·판정값·보고값의 유효숫자 일치 기준 글도 참고할 만하다.

시험결과 엑셀 검증 최종 체크포인트

검증 완료는 대표 계산 하나가 맞는 상태가 아니라, 정의한 사용 범위에서 계산과 오류 처리, 보호, 출력이 사전 기준을 충족한 상태이다. 최종 기록에는 검증한 파일과 환경, 기대값의 산출 근거, 실제 결과, 불일치의 조치와 재시험 결과를 함께 남긴다.

시험 기록은 다른 검토자가 같은 입력으로 재현할 수 있어야 한다. 화면 캡처만 보관하기보다 사용한 통합문서와 입력 데이터, 독립 계산서, 출력물을 연결하고, 실패했던 시험도 수정 이력과 함께 보존한다.

외부 데이터 연결이나 매크로가 있는 파일은 연결 실패, 갱신되지 않은 데이터, 실행 실패의 처리까지 검증 범위에 포함한다. 검증하지 않은 기능과 환경은 사용 제한으로 명시하고, 실제 시험마다 시료번호·원자료·입력값을 확인하는 일상 검토도 계속 수행한다.

표시 자릿수와 실제 판정값의 관계는 기존 글 「정도관리 반올림 규칙 설정 4단계 — 원자료·판정값·보고값의 유효숫자 일치 기준」과 연결해 관리한다. 계산 파일 검증 기록에는 그 반올림 규칙을 어느 셀과 출력에 적용했는지까지 남긴다.

승인 전 최종 확인

• ✓검증 파일·버전·실행 환경을 특정했다.
• ✓독립 기대값과 합격 기준을 사전에 정했다.
• ✓경계값·빈칸·잘못된 입력·붙여넣기를 시험했다.
• ✓수식 보호와 최종 출력의 일치를 확인했다.
• ✓실패 원인·수정·재시험 결과를 보존했다.
• ✓변경 영향평가와 재검증·승인 기록을 연결했다.

참고 자료

• NIST 측정서비스 품질매뉴얼 제11판 — 7.11 데이터 및 정보 관리
• Microsoft Support — 데이터 유효성 검사 상세 안내
• Microsoft Support — 워크시트 보호와 보호 기능의 한계