엑셀 빈 셀 데이터 일괄 채우기, 입력 데이터 변경하기 팁

엑셀 빈 셀 데이터 일괄 채우기, 입력 데이터 변경하기 팁

 

엑셀의 빈 셀에 특정 값을 일괄적으로 입력하거나 범위에서 비어있는 셀의 위에 입력된 데이터를 한 번에 채울 수 있는 방법, 그리고 입력된 데이터를 '찾기 및 바꾸기'를 통해 변경하는 방법을 알려드리도록 하겠습니다.

 

우선 알아둬야 될 팁은 여러 셀을 범위로 잡은 상태에서 동시에 작업을 수행하기 위해서는 'Enter'키 만으로는 불가능하며, 'Ctrl'키를 함께 사용해야 된다는 것입니다.

 

 

1. 빈 셀 데이터 일괄 채우기

 

빈 셀에 데이터를 일괄적으로 채우기 위해 빈 셀을 범위로 잡은 후 작업을 진행해야 됩니다.

 

아래와 같은 예제를 해결하는 방법입니다.

예제1

표의 모든 셀에 숫자 100을 한 번에 입력하는 문제입니다.

 

좌측 상단의 셀에 '100'을 입력한 후 오른쪽과 아래로 드래그해도 결과를 구할 수 있지만 표의 빈 셀을 모두 범위로 잡고 한 번에 모든 셀에 숫자 '100'이 입력되도록 하는 방법을 알아보도록 하겠습니다.

 

우선 아래와 같이 표의 빈 셀을 모두 모두 범위로 지정한 후 바로 '='과 '100'을 입력합니다.

예제1 풀이

이후 Enter 키를 누르면? 결과는 다음과 같이 나오게 됩니다.

예제1 풀이(잘못된 방법)

모든 셀에 숫자 '100'이 입력되도록 하기 위해 전체 범위를 잡고 진행했는데 원하는 결과가 나오지 않았습니다.

 

이유는 초반에 팁으로 설명한 'Ctrl'키를 함께 사용하지 않았기 때문입니다.

 

범위를 지정하고 '=100'을 입력한 후 'Ctrl'키를 누른 상태에서 'Enter'키를 누르게 되면 아래와 같이 모든 셀에 정상적으로 숫자 '100'이 입력되는 것을 확인할 수 있습니다.

예제1 풀이

 

이번에는 모두 빈 셀만 있는 표가 아닌 중간에 일부분이 비어있는 표에서 빈 셀만 선택한 후 바로 위의 데이터를 빈 셀에 채우는 작업을 해보도록 하겠습니다.

 

예제는 아래와 같습니다.

예제2

중간에 빈 셀을 일괄적으로 선택하고 해당 셀에 그 위에 있는 비어있지 않은 셀의 데이터를 일괄적으로 입력하도록 하는 방법입니다.

 

예제 1과 동일하게 표의 모든 범위를 잡는 것으로 시작합니다.

예제2 풀이

범위를 잡은 후 키보드 상단의 'F5'키를 클릭하여 이미지와 같이 '이동' 창을 띄운 후 좌측 하단의 '옵션'을 클릭합니다.

예제2 풀이

이후 '이동 옵션' 창이 뜨면 '빈 셀'을 클릭한 후 '확인' 버튼을 클릭합니다.

예제2 풀이

그러면 아래와 같이 전체 범위에서 빈 셀만 선택이 됩니다.

예제2 풀이

이때 바로 '=' 키를 클릭한 후 키보드 방향키를 이용하여 위로 한 칸 이동하면 아래 이미지와 같이 A3셀이 선택됩니다.

예제2 풀이

여기서 'Ctrl'과 'Enter' 키를 클릭하면 빈 셀에 일괄적으로 바로 위 셀의 데이터가 입력됩니다.

예제2 풀이

 

 

2. 입력 데이터 변경하기

 

데이터를 일괄 변경하는 방법은 자주 사용하는 '찾기 및 바꾸기' 기능을 활용하게 됩니다.

 

여기서 알아볼 내용은 단순히 특정 데이터를 '찾기 및 바꾸기'를 통해 특정 셀의 값을 다른 값으로 단순 변경하는 것이 아닌 동일한 형식의 여러 데이터에 입력된 값을 한 번에 변경하는 것입니다.

 

아래와 같이 노출되면 안 되는 개인 번호가 있다고 했을 때 이를 변경하는 방법을 알아보도록 하겠습니다.

예제1

개인 번호의 뒷자리는 모두 삭제하고 앞의 6자리 숫자만 나오도록 해보겠습니다.

 

※ 위 문제의 경우 앞서 배운 LEFT 함수를 사용하여 다른 셀에 6자리 번호만 추출한 후 원본 데이터를 삭제하는 방식으로 작업을 수행해도 됩니다.

 

우선 변경하고자 하는 범위를 지정합니다.

예제1 풀이

이후 'Ctrl'과 'H' 키를 클릭하여 '찾기 및 바꾸기' 창을 띄워주고 찾을 내용에 '-*'을 입력하고 바꿀 내용은 비어있는 상태에서 '모두 바꾸기'를 클릭합니다.

 

※ '*'기호가 '-'뒤에 붙으면서 '-*'의 의미는 '-'뒤에 붙는 모든 값을 의미합니다.

예제1 풀이

위의 이미지처럼 작성한 내용을 해석하면 '지정한 범위에서 '-'를 포함하여 '-'뒤에 있는 모든 값을 빈칸으로 변경하라'입니다.

(만약 '*-'로 입력하였다면 '-'를 포함한 앞의 모든 값을 빈칸으로 변경하라는 의미가 됩니다)

 

모두 바꾸기를 완료한 결과는 아래와 같이 나오는 것을 확인할 수 있습니다.

예제1 풀이

 

 

이번에는 개인 번호의 뒷자리를 '*'기호로 변경해보도록 하겠습니다.

예제2

※ 앞선 예제와 마찬가지로 해당 문제 역시 LEFT 함수와 '&' 기호를 활용하여 다른 셀에 결과를 구할 수 있습니다.

LEFT(개인번호 셀,6)&"-****"과 같이 수식을 입력하면 됩니다.

 

'찾기 및 바꾸기' 창을 띄우는 것 까지 동일하나 '바꿀 내용'에 변경하고자 하는 값을 넣어주면 됩니다.

예제2 풀이

'-'기호 뒤 4자리 숫자를 '****'으로 변경하기 위해 바꿀 내용에는 '-'기호까지 포함하여 입력합니다.

 

그러면 다음과 같이 변경됩니다.

예제2 풀이

 

본 포스팅에서처럼 전체 부분을 지정하여 변경할 수도 있지만 '찾을 내용'과 '바꿀 내용'에 입력되는 값에 따라 특정 셀의 값만 변경할 수도 있으며, 다양한 값으로 변경할 수 있습니다.

 

다양한 경우를 가정하여 응용해보시기 바랍니다.