엑셀 필터로 고른 행만 수정하려면

필터로 고른 주문만 수정하려고 새 수량을 붙였는데, 제외한 주문까지 바뀌었다면 붙여넣은 위치부터 확인한다. 보이는 셀만 복사했어도 첫 대상 셀 아래에 숨겨진 행이 있으면 그곳에 값이 들어갈 수 있다. 주문 상태를 모두 같은 말로 바꾸는 일은 보이는 대상 셀에 직접 입력하고, 주문마다 다른 수량을 넣는 일은 ID로 새 값을 연결해 검토한다. 보고서용 목록만 필요하다면 원래 표를 바꾸지 않고 빈 시트로 복사한다.

Microsoft Q&A에는 한 열의 100개 셀을 다른 열에 붙였더니 필터로 제외한 셀까지 바뀌었다는 질문이 있다. 복사할 때 원하는 셀만 고르는 설명을 따라도 붙여넣을 곳의 행 대응이 해결되지 않은 사례다. 아래 메뉴와 Ctrl+Enter 절차는 Windows용 데스크톱 Excel을 기준으로 하며, 웹용 Excel의 차이는 마지막 절에서 설명한다.

화면에서 이웃한 두 행이 실제로는 붙어 있지 않을 수 있다

예를 들어 필터 후 행 번호가 2, 5, 9로 보인다고 하자. 화면에는 세 행이 나란히 있지만 그 사이의 3·4·6·7·8행은 파일 안에 그대로 있다. 첫 대상 셀부터 세 값을 연속으로 붙이면 화면에 보이는 세 항목과 다른 행에 들어갈 수 있다.

Microsoft의 셀 이동·복사 안내는 복사한 자료가 연속된 행이나 열에 붙고, 대상에 숨긴 행·열이 있으면 일부 결과를 보려면 숨김을 해제해야 한다고 설명한다. 붙여넣기 옵션에서 ‘값’을 선택해도 입력할 행을 고르는 방식이 바뀌는 것은 아니다. 값 붙여넣기는 수식 대신 계산된 값 등을 옮기는 선택이다.

필터로 제외한 행과 오른쪽 클릭의 숨기기로 가린 행도 작업 전에 알아둔다. 필터를 지워도 수동으로 숨긴 행은 별도로 남아 있을 수 있다. 전체 결과를 대조할 때는 필터와 수동 숨김을 모두 해제한 사본을 사용한다. 색이나 행 높이가 아니라 ID와 기존 값이 보이는 상태에서 비교해야 수정 제외 항목을 찾을 수 있다.

원본 파일은 별도로 보존하고 작업용 사본을 만든다. 특히 이미 잘못 붙여넣었다면 그 상태를 새 기준으로 삼아 다음 수정을 이어가지 않는다. 실행 취소가 가능한 동안 되돌리거나 보존한 원본과 대조해 어떤 범위가 바뀌었는지 먼저 확인한다.

목록을 추출할 때는 보이는 셀을 복사해 빈 시트에 붙인다

보이는 셀만 복사하는 공식 절차는 복사할 범위를 선택한 뒤 홈 → 찾기 및 선택 → 이동 옵션 → 화면에 보이는 셀만을 고르는 순서다. 영어 UI에서는 Home → Find & Select → Go To Special → Visible cells only다. 확인을 누르고 Ctrl+C로 복사한 뒤, 새 시트의 왼쪽 위 셀을 선택해 Ctrl+V로 붙인다.

필터 결과를 보고서에 옮기는 작업이라면 새 시트에 숨긴 행이나 필터가 없도록 둔다. ID·이름·결과값을 함께 복사하면 붙인 뒤 원본과 대조하기 쉽다. 값 한 열만 가져오면 행 수는 맞아도 어떤 항목의 숫자인지 뒤늦게 확인하기 어렵다.

원본의 2, 5, 9행을 가져와 새 시트 2행부터 붙였다면 결과는 2, 3, 4행으로 이어지는 목록이 된다. 원본 행 번호 사이의 빈 공간을 보존하는 작업이 아니다. 이 목록을 나중에 원래 표에 되돌릴 계획이라면 원본 ID를 같이 가져와야 한다. 새 시트의 행 번호를 원래 항목의 식별자로 쓰지 않는다.

복사한 수식을 그대로 유지할지 현재 결과만 보낼지도 정한다. 수식이 필요 없는 제출용 목록이라면 붙여넣기 메뉴의 값 옵션을 쓸 수 있다. 다만 그 목록은 원본 값이 바뀌어도 자동으로 다시 계산되는 보고서가 아니다. 수식이 필요한 작업 파일과 값만 담은 제출 사본은 용도가 다르다.

‘복사’를 ‘잘라내기’로 바꿔 같은 절차를 적용하지 않는다. 2015년 한국어 Microsoft Q&A에도 필터된 다중 범위를 Ctrl+X로 옮기려다 오류를 만난 질문이 있다. 원래 행을 지우려는 작업은 목록 추출과 별도로 결정한다. 복사 결과를 확인하기 전에 원본의 행을 삭제하면 비교할 자료가 사라진다.

모두 같은 값으로 바꿀 때는 대상 셀을 골라 Ctrl+Enter로 입력한다

필터로 고른 항목의 상태를 모두 ‘확인 완료’로 바꾸는 경우에는 여러 줄의 자료를 붙여넣을 필요가 없다. 변경할 상태 열의 데이터 범위만 선택한다. 머리글과 다른 열은 제외하고, 이동 옵션에서 화면에 보이는 셀만 선택한다. 선택을 유지한 채 수식 입력줄에 ‘확인 완료’를 입력하고 Ctrl+Enter를 누른다.

Excel 바로 가기 키 안내는 Ctrl+Enter를 현재 입력값으로 선택한 셀 범위를 채우는 기능으로 설명한다. 필터 뒤 Ctrl+Enter가 숨긴 셀까지 바꾼다는 질문의 Microsoft 답변도 보이는 셀만 다시 선택하고 수식 입력줄에서 입력하는 순서를 제안한다. 필터만 켜 놓고 열 전체를 선택하는 것으로 대신하지 않는다.

보이는 셀을 고른 뒤 데이터 셀 하나를 다시 클릭하면 여러 셀 선택이 풀릴 수 있다. 수식 입력줄을 이용하는 이유는 대상 선택을 유지하면서 같은 값을 입력하기 위해서다. 완료 뒤 필터를 풀고 제외했던 행의 상태가 그대로인지 확인한다. 이미 값이 들어 있던 대상 셀은 새 값으로 바뀌므로 빈칸만 채우려는 작업이라면 빈칸 조건도 먼저 적용한다.

‘확인 완료’를 넣는 작업이 끝나도 주문마다 다른 새 수량까지 입력된 것은 아니다. 100, 200, 300을 각각 다른 주문에 넣으려면 어느 주문에 얼마를 넣을지 연결할 자료가 필요하다. 값만 순서대로 받은 목록을 붙이기 전에 다음 절처럼 ID를 포함한 변경표를 만든다.

행마다 다른 값은 원래 ID와 연결해 검토 열에 만든다

아래는 상수로 입력한 숫자 열을 바꾸는 설명용 예제다. 원본 A열에 고유 ID, B열에 기존 값, C열에 작업 대상 표시, D열에 검토할 새 값을 둔다. 각 ID는 빈칸 없이 한 번만 나오며 숫자나 문자값이 일관돼야 한다. 먼저 작업용 사본에서 수정할 행을 필터로 고르고, 빈 C열의 보이는 데이터 셀에 앞 절의 방법으로 ‘대상’을 넣는다.

그다음 필터와 수동 숨김을 모두 해제한다. C열에 표시한 ID가 원래 수정하려던 목록과 일치하는지 확인한다. 이제 모든 행을 보면서 D열을 계산할 수 있다. 필터 조건이 이후 달라져도 이번 작업의 대상은 C열에 남아 있다.

‘변경’ 시트에는 A열에 ID, B열에 새 값을 넣는다. A2는 A103, B2는 300이고 A3는 A101, B3는 100으로 적는다. 원본과 일부러 순서를 다르게 둔 예다. 원본의 D2에 아래 수식을 입력한 뒤 D5까지 복사한다.

=IF(C2="대상",XLOOKUP(A2,변경!$A$2:$A$3,변경!$B$2:$B$3,"ID 확인",0),B2)

IF는 C2가 ‘대상’일 때만 새 값을 조회하고, 아니면 B2의 기존 값을 반환한다. XLOOKUP은 A2의 ID를 변경표에서 정확히 찾아 같은 행의 새 값을 가져온다. 마지막 인수 0이 정확한 일치를 지정한다. 변경표에 없는 대상 ID에는 ‘ID 확인’이 표시된다.

이 예제의 D2:D5는 100, 20, 300, 40이 된다. 변경표의 첫 값이 300이어도 A101에 300을 넣지 않는다. 행 순서 대신 ID를 사용했기 때문이다. 수정 제외 행 A102·A104는 B열의 20·40을 그대로 가져온다. 달러 기호로 고정한 변경표 범위는 수식을 아래로 복사해도 움직이지 않는다.

변경표가 더 길면 검색 범위와 반환 범위의 마지막 행을 함께 늘린다. 이 예제의 두 행짜리 주소를 그대로 두고 새 자료를 아래에 붙이면 추가 ID를 찾지 못한다. 지역 설정에 따라 인수 구분자가 세미콜론인 Excel에서는 쉼표 대신 해당 구분자를 사용한다. XLOOKUP은 Excel 2016·2019에 없으므로 이 수식 경로는 XLOOKUP을 지원하는 버전에서 사용한다.

중복·미매칭을 해결하고 제외 행을 확인한 뒤 확정한다

XLOOKUP은 기본적으로 첫 일치 항목을 반환한다. 변경표에 A101이 두 번 있으면 필요한 값인지 판단하지 않은 채 첫 값을 가져올 수 있다. 원본 ID도 고유해야 하며, 한 주문에 여러 품목 행이 있다면 주문번호 하나만으로는 부족하다. 주문번호와 품목 행 번호처럼 실제로 한 행을 식별하는 키가 필요하다.

COUNTIF로 각 대상 ID가 변경표에 몇 번 있는지 확인할 수 있다. 예제 원본에서 =COUNTIF(변경!$A$2:$A$3,A2)를 계산하면 A101과 A103은 각각 1이다. 대상인데 0이면 대응값이 없고, 2 이상이면 먼저 변경표의 중복 의미를 확인한다. 이 예제의 ID는 와일드카드 문자가 없는 영숫자 코드다.

‘ID 확인’을 빈칸이나 0으로 바꿔 오류를 감추지 않는다. 실제로 새 값이 0인 항목과 대응 자료가 없는 항목은 다르다. 또 이 예제는 변경값이 빈칸 아닌 숫자라는 조건이다. 빈칸으로 지우려는 요청이나 원본 수식을 바꾸려는 요청은 그대로 적용하지 않고 별도 변경 방식으로 다룬다.

B열과 D열이 나란히 있는 상태에서 대상 ID와 새 값을 대조한다. 대상 수가 변경 요청의 건수와 맞는지 보고, 제외한 A102·A104처럼 바뀌지 않아야 할 행도 확인한다. 합계만 비교하면 한 행은 늘고 다른 행은 줄어 같은 합계가 나오는 오류를 놓칠 수 있다. 이번 작업의 확인 단위는 각 ID와 그 값이다.

검토한 숫자를 기존 B열에 확정해야 한다면 필터·숨김이 없는 상태에서 D2:D5를 복사해 B2에 값으로 붙인다. 이 단계는 같은 길이의 연속 범위 전체를 옮기므로 제외 행의 기존 값도 같은 위치로 돌아간다. 예제처럼 B열이 상수 값인 경우에만 쓴다. B열에 수식이 있으면 값 붙여넣기가 그 수식을 없애므로 검토 열을 남기고 수식을 보존하는 변경 방법을 따로 정한다.

원래 값과 대상 표시가 담긴 작업 전 사본, ID·변경값 표를 함께 남긴다. ID 없이 새 값만 받은 경우에는 임의로 순서를 맞춰 원본에 넣지 않고, 어떤 항목의 값인지 제공자에게 확인한다. 필터 결과의 순서가 같아 보이는 것만으로는 정렬·누락된 한 행을 발견할 수 없다.

웹용 Excel은 일반 범위와 필터한 표의 복사 지원이 다르다

Microsoft의 보이는 셀 복사 문서는 웹용 Excel의 일반 범위에 데스크톱과 같은 옵션이 없다고 안내한다. 반면 Excel의 표로 서식 지정한 데이터에 필터를 적용한 경우에는 보이는 셀만 복사할 수 있다. 화면에 테두리가 그려진 목록인지가 아니라 실제 Excel 표인지가 조건이다.

일반 범위를 유지해야 하고 데스크톱 앱을 사용할 수 있다면 Open in Excel로 파일을 열어 보이는 셀을 선택한다. 표로 바꾸는 방법은 원본 구조를 바꾸어도 되는 작업에서 선택한다. 어느 쪽을 쓰든 추출 목록을 넣을 새 시트에는 필터와 숨김이 없어야 결과를 모두 볼 수 있다.

웹의 필터한 표에서 보이는 셀을 복사할 수 있다는 설명은 붙여넣을 표의 숨긴 행을 건너뛴다는 약속이 아니다. 원래 표의 일부 항목을 갱신할 때는 이번 작업 대상 ID, 기존 값, 변경값을 함께 놓고 연결한다. 숨겼던 행까지 다시 펼쳐 대조하면 원래 건드리지 않으려던 기록도 그대로 남았는지 확인할 수 있다.