연속해서 두 번 이상 발생하는 날짜의 열 위치를 얻으려고 합니다.
예시 데이터 :
.
다음 수식은 대부분의 열 위치를 올바르게 식별하지만 중복 항목이 있는 경우 첫 번째 항목 위치가 항상 반환됩니다.
공식:
미리 감사드립니다.
답변1
다음 수식은 n-th일치하는 셀과 일치하지 않는 셀 집합에서 일치 항목을 선택합니다. 나중에 다양한 소스 데이터 레이아웃에 적용할 수 있습니다. 광범위한 용도로 사용되기 때문에 오류 검사를 전혀 제공하지 않는다는 점에서 기본이지만 IF(FL2<>0정확한 상황에 맞게 이러한 검사를 쉽게 추가할 수 있습니다.
=FILTERXML("<Outer><Inner>"&SUBSTITUTE(TEXTJOIN("¢",FALSE,FILTER(SUBSTITUTE(ADDRESS(ROW(),COLUMN(AW2:CW2),4),ROW(),""),AW2:CW2=B2),ROW(),""),"¢","</Inner><Inner>")&"</Inner></Outer>","/Outer/Inner["&C2&"]")
B열의 값과 C열의 값이 일치하는 행의 A열에 있다고 가정하여 설정됩니다. n-th물론 이를 조정할 수 있습니다.
LET()모든 변수를 편집하기 쉬운 한 곳에 두는 데 사용하는 버전은 다음과 같습니다.
=LET(
RangeToExamine, AW2:CW2, Delimiter, "¢", ItemToMatch, B2, InstanceToMatch, C2,
FILTERXML("<Outer><Inner>"
&SUBSTITUTE(TEXTJOIN(Delimiter,FALSE,
FILTER(SUBSTITUTE(ADDRESS(ROW(),COLUMN(RangeToExamine),4),ROW(),""),
RangeToExamine=ItemToMatch),
ROW(),""), Delimiter,"</Inner><Inner>") & "</Inner></Outer>",
"/Outer/Inner["&InstanceToMatch&"]"))
그게 전부 LET()입니다: 공식을 더 쉽게 편집할 수 있게 만드는 것입니다.
그래서 그것이 무엇을 하는지. 첫째, 레이아웃이 단일 행일 수도 있지만 확실하지 않아 모든 행에서 작업할 수 있도록 레이아웃을 그대로 두었습니다. 이는 함수 ROW()내부의 함수를 사용 ADDRESS()하고 나중에 SUBSTITUTE()행 번호를 제거하는 함수 에서 사용하여 처리됩니다 (그래서 원하는 결과인 열만 갖게 됩니다). 원하는 경우 행 단위 활동이 발생하지 않도록 두 위치 모두에 숫자 "1"을 배치할 수 있습니다. LET()여기저기서 수정할 수 있도록 하려면 절을 추가할 수도 있습니다 . LET()여기서는 선을 두 줄로 굴려서 지저분하게 만들 수 있기 때문에 여기에 가지 않았습니다 . 그건 그렇고, 이것은 NUMBER 열을 LETTER 열로 변환하는 방법입니다. 재미있는 공식이 모두 포함된 것은 아닙니다 MOD(). UDF가 필요하지 않습니다.
따라서 기능을 ADDRESS()사용하여 Spill검사 중인 범위의 모든 셀 주소 배열을 만듭니다. FILTER()그런 다음 일치하는 대상 범위를 검사하고 해당 셀 주소를 나열합니다. (다시 말하지만, 오류를 설정하지 마세요!) SUBSTITUTE()그런 다음 행 번호(또는 변경한 경우 상수)를 제거하므로 이제 Excel에는 일치하는 열에 대한 열 문자 레이블의 배열이 있습니다.
여기서 사용한 구분 기호가 항상 좋은 구분 기호가 아닐 수도 있다는 점을 빠르게 언급하고 싶습니다. 다른 경우에는 말이죠. 하지만 기본 데이터가 아니라 주소 데이터에 연결되어 있기 때문에 에서 반환한 주소에 한 번도 사용되지 않은 것만 있으면 ADDRESS()여기서 실패할 수 없습니다. 하지만 다른 곳에서는 "드물게 사용되는 문자" 접근 방식을 사용하면 문자표에서 발견한 이상한 문자를 선택해야 할 수도 있습니다.
다음으로, TEXTJOIN()주소 배열을 문자열로 바꾸고 SUBSTITUTE()해당 구분 기호를 </Inner><Inner>문자열로 바꾸는 동시에 앞뒤에 문자열을 추가하여 지금까지의 결과를 허용 가능한 XML로 변환합니다. XML을 구성하는 데 사용되는 거의 모든 것이 있을 수 있습니다. 단, 항목을 래핑하는 상위 레벨이 하나 이상 있고 문자열의 각 부분을 래핑하는 최하위 레벨이 하나 이상이면 됩니다 TEXTJOIN(). 나는 <Outer>모든 것을 포괄하는 더 높은 수준과 <Inner>실무 수준에 사용하는 것을 좋아합니다.
나는 이것을 설명하는 방법이 매우 형편없는 곳에서 처음 보았지만Chandoo.org그의 설명은 (거의) 항상 매우 명확하고 유용하기 때문에 그에 대한 팁이 있는지 확인하십시오. 매우 편리했기 때문에 신용과 감사를 표해야 합니다!
마지막으로 FILTERXML()마지막 작업을 수행합니다. 반환하려는 일치 항목의 인스턴스를 선택할 수 있습니다. Chandoo의 팁에서 그는 문장을 이런 식으로 분해하지만, 보시다시피 문자열, 열 형식 데이터를 포함하여 얻은 모든 것을 분해하거나 변환할 수 있습니다. 이 경우 세 번째 인스턴스를 원하면 [3]항목을 추가합니다.
그러나 Excel은 여기서 한 가지 더 나은 기능을 제공합니다. 첫째, 세 번째 인스턴스에 대해 [3]과 같은 숫자 인스턴스를 사용할 수 있지만 [last()]기수를 찾을 필요 없이 마지막 인스턴스를 찾는 데 사용할 수도 있습니다 . 하지만 함수가 정말 빛을 발하는 점은 놀랍게도(이런 종류의 것을 목표로 하지 않는 것처럼 보이므로) 해당 절을 "구축"하거나 다르게 말하면 변수 데이터를 일부로 포함하는 문자열을 구성할 수 있다는 것입니다. 수식을 입력하고 필요에 따라 수식 내에서 변경하는 대신 수식을 반응형으로 만들 수 있습니다. 즉, 사용자는 수식을 편집하는 대신 셀의 항목을 사용하여 해당 기능을 얻을 수 있습니다. 또는 필요한 경우 다른 공식의 결과를 반영하도록 만들 수도 있습니다.
마지막 부분은 형식화된 수식 버전의 마지막 줄에서 발생합니다. "XML 경로"(이 경우 `/Outer/Inner") 바로 뒤에 인스턴스 번호나 표현식이 포함된 대괄호("[ ]") 세트를 입력합니다.
일반적인 사용에서는 다음을 찾기 위해 오류 검사를 권장합니다.
- 일치시킬 항목이 검사 대상 범위에 속하지 않습니다.
- 원하는 인스턴스 수가 총 일치 수보다 높습니다. #6을 원하지만 4개만 존재합니다.
- 마찬가지로, 마지막 인스턴스에서 다시 n번째 인스턴스를 찾으려면 해당 인스턴스도 존재해야 합니다. "last()"에서 6번째 인스턴스를 다시 찾으려면 적어도 6개의 다른 인스턴스가 있어야 합니다.
데이터가 문자나 숫자보다 더 다양한 경우에는 구분 기호를 선택하는 데 주의를 기울여야 합니다.
열 대신 셀 주소를 원하는 경우 SUBSTITUTE()전체 주소에서 행을 제거하는 를 제거하세요.
글쎄요, 어느 정도 죄송합니다. 이는 귀하의 게시물에 완전히 적합하기보다는 일반적인 솔루션이지만 귀하가 사용하는 정확한 용도가 확실하지 않으므로 도움이 될 수 있으며 모든 편집 내용은 매우 쉽게 볼 수 있습니다. 여기 경험에 따르면 게시물은 유용한 정보를 매우 자주 생략하고 게시자는 필요한 답변을 얻기 위해 적어도 하나 이상의 질문을 더 해야 합니다. 그 과정을 거치는 데 필요한 모든 것을 단락시키려고 합니다.
답변2
COUNTIF 수식을 찾고 계십니까? =IF(A2<>0,COUNTIF($A$2:A2,A2),0)
