Excel : 한 번에 여러 값을 찾고 교체하십시오

이 튜토리얼에서는 여러 단어, 문자열 또는 개별 문자를 찾아 교체하는 몇 가지 방법을 살펴볼 것이므로 필요에 가장 적합한 사람을 선택할 수 있습니다.

사람들은 보통 Excel에서 어떻게 검색합니까? 대부분의 경우 단일 값에 적합한 Find & Replare 기능을 사용하여. 그러나 교체 할 수십 또는 수백 개의 품목이 있다면 어떨까요? 확실히, 아무도 모든 교체품을 수동으로 하나씩 만들고 싶지 않은 다음 데이터가 변경 될 때 다시 한 번 다시 수행하고 싶지는 않습니다. 운 좋게도, Excel에서 질량 대체를 수행하는 몇 가지 효과적인 방법이 있으며, 우리는 각각을 자세히 조사 할 것입니다.

• 여러 값을 대체하기위한 중첩 대체 공식
• xlookup 공식 여러 단어를 검색합니다
• 재귀 람다 함수를 사용하는 다중 장소
• 질량을 찾아 UDF로 교체하십시오
• 다중 값을 찾고 교체하기 위해 대량 매크로를 판매합니다
• 다중 찾기 및 교체를 사용하여 하위 문자열 도구를 사용합니다

여러 값을 중첩 대체물로 찾고 교체하십시오

Excel에서 여러 항목을 찾고 교체하는 가장 쉬운 방법은 대체 기능을 사용하는 것입니다.

공식의 논리는 매우 간단합니다. 이전 값을 새 값으로 대체하기 위해 몇 가지 개별 기능을 작성합니다. 그런 다음 해당 기능을 서로 둥지로써 각 후속 대체물이 이전 대체의 출력을 사용하여 다음 값을 찾습니다.

대체물 (대체 ( Text , Old_Text1 , New_Text1 ), Old_Text2 , New_Text2 ), Old_Text3 , New_Text3 )

A2 : A10의 위치 목록에서 약식 국가 이름 (예 : FR , UK 및 USA )을 전체 이름으로 교체하려고한다고 가정하십시오.

이를 수행하려면 D2 : D4의 이전 값을 입력하고 아래 스크린 샷에 표시된 것처럼 E2 : E4의 새로운 값을 입력하십시오. 그런 다음 아래 공식을 B2에 넣고 Enter를 누릅니다.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A10, D2, E2), D3, E3), D4, E4)

… 그리고 모든 교체품이 한 번에 수행됩니다.

위의 접근 방식은 동적 배열을 지원하는 Excel 365 에서만 작동합니다.

Excel 2019, Excel 2016 이상의 예전 버전에서 공식은 최상위 셀 (B2)에 대해 작성된 다음 아래 셀에 복사해야합니다.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, $D$2, $E$2), $D$3, $E$3), $D$4, $E$4)

이 경우 절대 셀 참조로 교체 값을 잠그므로 공식을 복사 할 때 이동하지 않을 것입니다.

메모. 대체 함수는 사례에 민감합니다 . 즉, 원본 데이터에 나타나는 것과 동일한 문자 케이스에 이전 값 ( Old_Text )을 입력해야합니다.

가능할만큼 쉽게이 방법은 중요한 단점을 가지고 있습니다. 교체 할 수십 가지 항목이있을 때 중첩 된 기능은 관리하기가 매우 어려워집니다.

장점 : 구현하기 쉬운; 모든 Excel 버전에서 지원됩니다

단점 : 제한된 수의 찾기/교체 값에 사용되는 것이 가장 좋습니다.

여러 항목을 XLOOKUP로 검색하고 교체하십시오

Xlookup 함수는 전체 셀 함량을 교체하려는 상황에서 Xlookup 함수는 편리합니다.

A 열에 국가 목록이 있고 모든 약어를 해당 성명으로 대체하는 것을 목표로합니다. 이전 예에서와 같이, "찾기"및 "교체"항목을 각각 별도 열 (각각 d와 e)으로 입력 한 다음 B2 에이 공식을 입력하는 것으로 시작합니다.

=XLOOKUP(A2, $D$2:$D$4, $E$2:$E$4, A2)

Excel 언어에서 인간 언어로 번역 된 공식은 다음과 같습니다.

d2 : d4 (lookup_array)에서 a2 값 (lookup_value)을 검색하고 e2 : e4 (return_array)에서 일치를 반환하십시오. 찾을 수없는 경우 A2에서 원래 값을 가져 오십시오.

채우기 손잡이를 두 번 클릭하여 아래 셀에 공식을 복사하면 결과가 기다리지 않습니다.

Xlookup 함수는 Excel 365에서만 사용할 수 있으므로 위의 공식은 이전 버전에서는 작동하지 않습니다. 그러나 Iferror 또는 Ifna 및 Vlookup의 조합 으로이 동작을 쉽게 모방 할 수 있습니다.

=IFNA(VLOOKUP(A2, $D$2:$E$4, 2, FALSE), A2)

메모. 대체와 달리 XLookup 및 Vlookup 기능은 대소 문자에 민감하지 않으므로 문자 케이스를 무시하는 조회 값을 검색합니다. 예를 들어, 우리의 공식은 FR 과 FR을 프랑스 로 대체 할 것입니다.

장점 : 일반적인 기능의 비정상적인 사용; 모든 Excel 버전에서 작동합니다

단점 : 셀 레벨에서 작동하며 셀 내용물의 일부를 대체 할 수 없습니다.

재귀 람다 함수를 사용하여 다중 교체

Microsoft 365 가입자의 경우 Excel은 전통적인 공식 언어를 사용하여 사용자 정의 기능을 만들 수있는 특수 기능을 제공합니다. 네, 람다에 대해 이야기하고 있습니다. 이 방법의 아름다움은 매우 길고 복잡한 공식을 매우 작고 단순한 공식으로 변환 할 수 있다는 것입니다. 또한 Excel에 존재하지 않는 자신의 기능을 만들 수 있습니다. VBA에서만 가능했던 것입니다.

Custom Lambda 함수 작성 및 사용에 대한 자세한 정보는이 자습서를 확인하십시오 : Lambda 기능을 Excel에 작성하는 방법. 여기서 우리는 몇 가지 실제 예를 논의 할 것입니다.

장점 : 결과는 교체 쌍의 수에 상관없이 우아하고 놀랍도록 사용하기가 우아하고 놀랍도록 간단합니다.

단점 : Excel 365에서만 사용할 수 있습니다. 통합 문서 별이며 다른 통합 문서에서 재사용 할 수 없습니다

예 1. 한 번에 여러 단어 / 문자열을 검색하고 교체합니다.

한 번에 여러 단어 나 텍스트를 교체하기 위해 MultirePlace 라는 사용자 정의 Lambda 함수를 만들었습니다.이 양식 중 하나를 사용할 수 있습니다.

=LAMBDA(text, old, new, IF(old"", MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0)), text))

또는

=LAMBDA(text, old, new, IF(old="", text, MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))))

둘 다 스스로를 부르는 재귀 기능입니다. 차이점은 출구 지점이 어떻게 확립되는지에만 해당됩니다.

첫 번째 공식에서 IF 함수는 이전 목록이 비어 있지 않은지 확인합니다 (Old ""). 사실이라면 다중 장소 기능이 호출됩니다. false 인 경우 함수는 현재 양식을 텍스트 하고 종료합니다.

두 번째 공식은 역 논리를 사용합니다. 오래된 경우 (오래된 = "") 텍스트를 반환하고 종료하십시오. 그렇지 않으면 Multire Place 에 전화하십시오.

가장 까다로운 부분이 달성됩니다! 당신이해야 할 일은 아래 스크린 샷에 표시된 것처럼 이름 관리자의 멀티 플레이어 함수를 지정하는 것입니다. 자세한 가이드 라인은 Lambda 기능을 지정하는 방법을 참조하십시오.

함수가 이름을 얻으면 다른 내장 기능과 마찬가지로 사용할 수 있습니다.

최종 사용자 관점에서 선택한 두 가지 공식 변형 중 어느 쪽이든 구문은 다음과 같이 간단합니다.

다중 장소 (텍스트, 오래된, 신규)

어디:

• 텍스트 - 소스 데이터
• 구식 - 찾을 가치
• 새로운 - 대체 할 값

이전 예제를 조금 더 가져 가면 국가 약어뿐만 아니라 국가 약어도 대체하겠습니다. 이를 위해 D2에서 시작하는 D 열에 약어 ( 오래된 값)를 입력하고 E2에서 시작하는 e 열의 전체 이름 ( 새 값)을 입력하십시오.

B2에서는 멀티 플레이어 기능을 입력하십시오.

=MultiReplace(A2:A10, D2, E2)

Enter를 누르고 결과를 즐기십시오 :)

이 공식의 작동 방식

공식을 이해하기위한 단서는 재귀를 이해하는 것입니다. 이것은 복잡하게 들릴지 모르지만 원칙은 매우 간단합니다. 각 반복마다 재귀 함수는 더 큰 문제의 작은 인스턴스를 해결합니다. 우리의 경우, 멀티 플레이어 함수는 기존 값과 새로운 값을 통해 루프이며 각 루프마다 하나의 대체품을 수행합니다.

MultirePlace (SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))

중첩 된 대체 기능과 마찬가지로, 이전 대체의 결과는 다음 대체의 텍스트 매개 변수가됩니다. 다시 말해, 각 후속 다중 장소 호출에서 대체 기능은 원래 텍스트 문자열이 아니라 이전 호출의 출력을 처리합니다.

이전 목록의 모든 항목을 처리하려면 최상위 셀로 시작하여 오프셋 기능을 사용하여 각 상호 작용에 따라 1 행 아래로 이동합니다.

OFFSET(old, 1, 0)

새 목록에 대해서도 마찬가지입니다.

OFFSET(new, 1, 0)

중요한 것은 재귀적인 통화가 영원히 진행되는 것을 막기 위해 출구 지점을 제공하는 것입니다. IF 함수의 도움으로 수행됩니다 - 이전 셀이 비어 있으면 함수는 현재 양식을 텍스트로 반환하고 종료합니다.

=LAMBDA(text, old, new, IF(old="", text, MultiReplace(…)))

또는

=LAMBDA(text, old, new, IF(old"", MultiReplace(…), text))

예 2. Excel에서 여러 문자를 교체하십시오

원칙적으로, 이전 예제에서 논의 된 다중 장소 기능은 각각의 기존 및 새로운 문자가 위의 스크린 샷의 약어 및 전체 이름과 같은 별도의 셀에 입력된다면 개별 문자도 처리 할 수 ​​있습니다.

하나의 셀에 이전 문자를 입력하고 다른 셀에 새 문자를 입력하거나 공식에 직접 입력하려면 이러한 공식 중 하나를 사용하여 REPLACECHARS 라는 다른 사용자 정의 기능을 만들 수 있습니다.

=LAMBDA(text, old_chars, new_chars, IF(old_chars"", ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1)), text))

또는

=LAMBDA(text, old_chars, new_chars, IF(old_chars="", text, ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1))))

평소와 같이 Name Manager의 새로운 Lambda 기능을 이름을 지정하십시오.

REPLACECHARS 함수 "TITLE ="NAME A CUSTOL REPLACECHARS 함수 ">

새로운 사용자 정의 기능이 사용할 준비가되었습니다.

REPLACECHARS (Text, Old_chars, New_chars)

어디:

• 텍스트 - 원래 문자열
• 구식 - 검색 할 캐릭터
• 새로운 - 대체 할 캐릭터

현장 테스트를하기 위해 수입 데이터에서 종종 수행되는 작업을 수행하겠습니다. 스마트 인용문과 스마트 아포스트로피를 직선 인용문 및 직선 아포스트로피로 교체하십시오.

먼저, 우리는 스마트 인용문과 스마트 아포스트로피를 D2, 직선 인용문 및 E2의 직선 아포스트로피로 입력하여 액자를 더 잘 읽을 수 있도록 캐릭터를 분리합니다. (두 셀에서 동일한 구분표를 사용하므로 결과에 아무런 영향을 미치지 않습니다. Excel은 공간을 공간으로 바꿀뿐입니다.)

그 후, 우리는이 공식을 B2로 입력합니다.

=ReplaceChars(A2:A4, D2, E2)

그리고 우리가 찾고 있던 결과를 정확히 얻으십시오.

공식에 문자를 직접 입력 할 수도 있습니다. 우리의 경우, 다음과 같은 직선 인용문을 "복제"하는 것을 잊지 마십시오.

=ReplaceChars(A2:A4, "“ ” '", """ "" '")

이 공식의 작동 방식

REPLACECHARS 기능은 Old_Chars 및 New_Chars 문자열을 통해 순환하며 왼쪽의 첫 번째 문자부터 시작하여 한 번에 하나의 교체를 만듭니다. 이 부분은 대체 기능에 의해 수행됩니다.

SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars))

각 반복마다 올바른 함수는 Old_chars 와 New_Chars 문자열의 왼쪽에서 한 문자를 제거하므로 왼쪽으로 다음 문자 쌍을 가져올 수 있습니다.

ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1))

각각의 재귀 호출 전에 if 함수는 Old_chars 문자열을 평가합니다. 비어 있지 않으면 함수는 자체를 호출합니다. 마지막 문자가 교체 되 자마자 반복 프로세스가 끝나고 공식은 현재 양식과 종료 를 텍스트 로 반환합니다.

메모. 핵심 공식에 사용되는 대체 기능은 대소 문자에 민감하기 때문에 Lambdas ( Multire Place 및 Relfacechars )는 대문자와 소문자를 다른 문자로 취급합니다.

질량을 찾아 UDF로 교체하십시오

Excel에서 Lambda 함수를 사용할 수없는 경우 VBA를 사용하여 전통적인 방식으로 멀티 레이스에 대한 사용자 정의 기능을 작성할 수 있습니다.

UDF를 Lambda 정의 멀티 플레이어 기능과 구별하기 위해 Massreplace는 말합니다. 함수의 코드는 다음과 같습니다.

function massreplace (범위 로 , 범위 로 findrng, 범위 로 , replacerng) variant () dim arres () dim arres ' as variant as as at at arsearchreplace (), stmp' 배열 ' 배열 '배열 '쌍 쌍을 저장하는 곳, 임시 스트링 딤진 ifindcurrow , cntfindrows count the the rows rows rows rows rows, count the the the rows, count iindex, count wrows, count index, count index, count index, count wrows index, count index, count index, count indel IINPUTCURCOL, CNTINPUTROWS, CNTINPUTCOL 소스 범위에서 현재 행의 긴 인덱스, 소스 범위의 현재 열의 인덱스, 행 카운트 CNTINPUTROWS = inputrng.rows.CntInputCols = inputrng.columns.count cntfindrows = FindSrow ( 1 ) cntinputrows, 1 ~ cntinputcols) redim arsearchreplace (1 ~ cntfindrows, 1 ~ 2) 'ifindcurrow = 1 에서 cntfindrows arsearchreplace 에서 찾기/교체 배열 준비 replacerng.cells (ifindcurrow, 1). value 다음 'value 다음'iinputcurrow 의 소스 범위에서 iinputcurcol = 1 to cntinputrows stmp = 1 ~ cntinputcols stmp = inputrng.cells (iinputcurrow, iinputcurcol)에 대한 cntinputrows의 소스 범위에서 검색 및 교체. ifindcurrow = 1 to cntfindrows stmp = replace (stmp, arsearchreplace (ifindcurrow, 1), arsearchreplace (ifindcurrow, 2)) 다음 Arres (iinputcurrow, iinputcurcol) = stmp 다음 massreplace = arres end function

Lambda 정의 기능과 마찬가지로 UDF는 통합 문서 전체 입니다. 즉, MassReplace 기능은 코드를 삽입 한 통합 문서에서만 작동합니다. 이 작업을 올바르게 수행하는 방법이 확실하지 않은 경우 Excel에 VBA 코드를 삽입하는 방법에 설명 된 단계를 따르십시오.

코드가 통합 문서에 추가되면 기능이 공식 지능에 나타납니다. 인수가 아니라 함수의 이름 만 나타납니다! 하지만 구문을 기억하는 것은 큰 문제가 아니라고 생각합니다.

MassReplace (input_Range, find_range, Replace_Range)

어디:

• input_range- 값을 바꾸려는 소스 범위.
• find_range- 검색 할 문자, 문자열 또는 단어.
• Replar_Range- 대체 할 문자, 문자열 또는 단어.

Excel 365에서 동적 배열을 지원하기 때문에 이것은 정상 공식으로 작동하며 상단 셀 (B2)에만 입력하면됩니다.

=MassReplace(A2:A10, D2:D4, E2:E4)

사전 동성 엑셀에서, 이것은 이전 스타일의 CSE 배열 공식으로 작동합니다. 전체 소스 범위 (B2 : B10)를 선택하고 공식을 입력하고 동시에 CTRL Shift Enter 키를 눌러 완료합니다.

장점 : Excel 2019, Excel 2016 및 이전 버전의 커스텀 람다 함수에 대한 알맞은 대안

단점 : 통합 문서는 매크로 가능 .xlsm 파일로 저장해야합니다.

벌크는 Excel에서 VBA 매크로로 교체합니다

매크로로 공통 작업을 자동화하는 것을 좋아하는 경우 다음 VBA 코드를 사용하여 범위에서 여러 값을 찾고 교체 할 수 있습니다.

sub bulkreplace () dim rng as Range, sourcerng , range as Range, REPLACERNG ON ERROR RESUME ON ERROR RESUME NEXT SOCECERNG = APPLICATION.INPUTBOX ( "소스 데이터 :" , "bulk rEPLEC" , address, address, type : = 8) sourcErng 가 아무것도 설정 하지 않으면 sourcerNg = 8), "grange :" , ",", ",", " ," , " , " , " replacerng 은 application.screenupdating = replacerng.columns (1) 의 각 rng 에 대해 false가 없습니다 . sourcerng.replace what what : = rng.value, progralement : = rng.offset (0, 1) . value next application.screenupdating = true end if end sub .

매크로를 즉시 사용하려면 코드가 포함 된 샘플 통합 문서를 다운로드 할 수 있습니다. 또는 자신의 통합 문서에 코드를 삽입 할 수 있습니다.

매크로 사용 방법

매크로를 실행하기 전에 아래 이미지 (C2 : D4)에 표시된대로 이전 값과 새 값을 두 개의 인접한 열에 입력하십시오.

그런 다음 소스 데이터를 선택하고 Alt F8을 누르고 Bulkreplace 매크로를 선택하고 실행을 클릭하십시오.

소스 분노가 미리 선택되면 참조를 확인하고 확인을 클릭하십시오.

그런 다음 교체 범위를 선택하고 확인을 클릭하십시오.

완료!

장점 : 한 번 설정하고 언제든지 재사용하십시오

단점 : 매크로는 모든 데이터 변경과 함께 실행해야합니다.

Excel에서 다중 찾기 및 교체를 서브 스트링 도구로 교체하십시오

첫 번째 예에서, 나는 중첩 대체물이 Excel에서 여러 값을 교체하는 가장 쉬운 방법이라고 언급했습니다. 나는 내가 틀렸다는 것을 인정한다. 우리의 궁극적 인 스위트 룸은 물건을 더 쉽게 만듭니다!

워크 시트에서 대량 교체를하려면 Ablebits 데이터 탭으로 이동하여 하위 문자열 도구 > 하위 문자를 교체하십시오 .

교체 하위 문자열 대화 상자는 소스 범위 및 하위 문자 범위를 정의하도록 요청합니다.

두 범위를 선택한 경우 교체 버튼을 클릭하고 원래 데이터 오른쪽에 삽입 된 새 열이있는 결과를 찾으십시오. 네, 그렇게 쉽습니다!

팁. 교체를 클릭 하기 전에 고려해야 할 중요한 사항이 있습니다. 대문자와 소문자를 다른 문자로 처리하려면 선택하십시오. 이 예에서는 자본화 된 문자열 만 교체하고 "fr", "uk"또는 "ak"와 같은 하위 문자열을 다른 단어로 그대로두기 때문에이 옵션을 선택합니다.

문자열에서 다른 벌크 작업을 수행 할 수있는 것을 알고 싶다면 Ultimate Suite에 포함 된 다른 하위 스트링 도구를 확인하십시오. 또는 더 나은, 아래 평가 버전을 다운로드하고 시도해보십시오!

이것이 Excel에서 한 번에 여러 단어와 문자를 찾고 교체하는 방법입니다. 읽어 주셔서 감사합니다. 다음 주에 우리 블로그에서 뵙기를 바랍니다!

사용 가능한 다운로드

Excel에서 다중 찾기 및 교체 (.xlsm 파일) Ultimate Suite 14 일 완전 기능 버전 (.exe 파일)

위 내용은 Excel : 한 번에 여러 값을 찾고 교체하십시오의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!