Excel에 두 개의 열이 있습니다. 두 열 모두에는 임의의 문자가 접미사로 붙는 문자열 목록이 있습니다. 접두사의 형식은 두 개의 문자열에 밑줄이 추가되고 접미사에도 밑줄이 추가됩니다.
즉
ABC_DEF_a => (prefix = "ABC_DEF", suffix = "a")
HJDSGDJ_KJ1_a10 => (prefix = "HJDSGDJ_KJ1", suffix = "a10"
B열의 접두사가 A열에 있는지 어떻게 비교하나요?
편집: 문자열을 세 개의 섹션으로 분할한 다음 처음 두 섹션을 하나의 열로 결합한 다음 VLOOKUP을 사용하여 해당 열이 다른 섹션에 있는지 확인하면 이 작업을 수행할 수 있다는 것을 알고 있습니다. 하지만 저는 oneliner 기능을 찾고있었습니다.
답변1
A1 및 B1 셀에 다음이 있다고 가정합니다.
그리고 C 열에는 접두사가 같은지 여부를 반환하는 TRUE(VERDADEIRO) 또는 (FALSO) 가 있습니다 . FALSE(포르투갈어로 사진이 있어서 죄송합니다)
제가 생각해낸 공식은 다음과 같습니다.
=EXACT(LEFT(SUBSTITUTE(A1;"_";"\";2);SEARCH("\";SUBSTITUTE(A1;"_";"\";2))-1);LEFT(SUBSTITUTE(B1;"_";"\";2);SEARCH("\";SUBSTITUTE(B1;"_";"\";2))-1))
설명:
=EXACT(str1,str2)
문자열 str1과 str2가 같은지 여부를 비교합니다. A열과 B열에서 접두사를 추출해야 합니다. 이를 위해 다음을 사용합니다.
=LEFT(text, [num_chars])
[num_chars]string 에서 문자를 추출합니다 text. 얼마나 많은 문자를 추출해야 하는지 알기 위해 두 번째 밑줄을 _더미 문자로 대체합니다 \.
=SUBSTITUTE(text, old_text, new_text, [instance])
=SUBSTITUTE(A1;"_";"\";2)
여기서의 비결은 선택적 인수입니다 [instance]. 밑줄 문자의 두 번째 발생을 대체하기 위해 이를 2로 설정했습니다.
=SEARCH("\";SUBSTITUTE(A1;"_";"\";2))-1)
이 공식을 사용하여 더미 캐릭터의 위치를 찾습니다 \. 이를 모두 합치면 위의 공식을 얻게 됩니다.
단계적으로 공식을 분석하면 다음과 같습니다.
답변2
귀하의 질문은 B 열의 각 문자열에 대해 접두사가 A 열의 어느 곳에서나 발견되는지 알고 싶어하는 것으로 읽었습니다. 따라서 이 답변이 해당 문제를 해결합니다.
한 줄로 표시할 수 없었습니다(A열의 모든 부분을 살펴봐야 하기 때문입니다). 그러나 추가 열은 하나만 필요합니다.
먼저, 문자열에서 접두사를 추출하는 공식은 다음과 같습니다.
=LEFT(Column_A, FIND(Delimiter, Column_A, 1 + FIND(Delimiter, Column_A)) - 1)
Delimiter"_" 문자입니다- 내부
FIND()호출은 첫 번째 밑줄의 위치를 찾습니다. 그런 다음 이를FIND두 번째 밑줄 위치의 시작점으로 사용합니다 . - 이
LEFT()함수는 문자열의 왼쪽부터 시작하여 여러 문자를 반환합니다. 따라서1두 번째 밑줄 위치에서 빼서 문자열의 접두사를 얻습니다.
따라서 이것을 사용하여 모든 A 열 접두사의 목록을 얻습니다. 그런 다음 배열 수식을 사용하여 B열의 각 접두사가 A열 접두사 목록에 있는지 확인합니다.
{=OR(LEFT(Column_B, FIND(Delimiter, Column_B, 1 + FIND(Delimiter, Column_B)) - 1)=Prefix_A)}
Prefix_AA 열 접두사의 전체 목록입니다.- A열에 적용된 접두어 추출 공식과 동일합니다.
- 함수 내부의 명령문이 및 값
OR()의 배열을 반환 하므로 이는 배열 수식이어야 합니다.TRUEFALSE
다음은 이 질문에 답하기 위해 제가 만든 스프레드시트의 스크린샷입니다.
저는 FormulaChop을 사용하여 이러한 예제 공식을 생성했습니다(전체 공개: FormulaChop을 작성했습니다).여기첫 번째 공식에 대한 FormulaChop 출력의 스크린샷입니다.여기이 질문에 답하기 위해 제가 만든 스프레드시트에 대한 링크입니다.
