다음과 같은 셀이 있습니다.
Number Band Color
1 10 A A1
2 20 A A1
3 30 A A1
4 40 B A1
5 50 B A2
6 60 C B1
7 70 C B1
8 80 A B1
9 90 A A1
10 100 B A1
다음과 같이 데이터의 "범위"/요약을 만들고 싶습니다.
Start End Band Color
10 30 A A1
40 40 B A1
50 50 B A2
60 70 C B1
손으로 할 수도 있지만 데이터가 수천 개의 셀에 있으므로 지루해 보입니다. 이를 용이하게 하는 데 도움이 되는 기능이 내장되어 있습니까?
답변1
두 개의 Helper 열을 사용하여 이 작업을 수행할 수 있습니다. D2와 E2에 각각 다음 공식을 넣었습니다.
=IF(OR(B2<>B1,C2<>C1),A2,"")
=IF(OR(B2<>B3,C2<>C3),A2,"")
그런 다음 데이터 세트의 길이를 복사했습니다.
이를 통해 SMALL() 공식을 사용하여 시작 및 종료를 얻을 수 있는 목록이 제공되었습니다.
G2에는 다음을 넣었습니다.
=IFERROR(SMALL(D:D,ROW(1:1)),"")
그런 다음 빈 셀이 생길 때까지 한 열을 복사했습니다.
그런 다음 INDEX/MATCH에서 해당 숫자를 사용하여 다른 정보를 반환합니다.
I2에는 다음을 넣었습니다.
=IF($G2<>"",INDEX(B:B,MATCH($G2,$A:$A,0)),"")
공백이 생길 때까지 한 열에 걸쳐 복사했습니다.
VLOOKUP을 사용하여 동일한 작업을 수행할 수도 있습니다.
=VLOOKUP($G2,$A:$C,COLUMN(B:B),FALSE)
답변2
귀하의 질문에 사용하려는 패턴과 도구에 대해 좀 더 설명할 수 있습니다. "함수"라고 말하면 VBA 코드가 아닌 시트 수식을 원한다고 가정하겠습니다. 각 밴드/색상 쌍의 첫 번째 인스턴스만 보고 있습니까? 아니면 연속적으로 일치하는 밴드 및 색상 세트를 아래로 찾고 있습니까? 후자의 경우 테이블에는 실제로 다음과 같이 세 개의 행이 더 포함됩니다.
Start End Band Color
10 30 A A1
40 40 B A1
50 50 B A2
60 70 C B1
80 80 A B1
90 90 A A1
100 100 B A1
이 더 긴 테이블을 원할 경우 두 개의 새 행(이 시트 또는 새 행)을 생성하고 A1에 "숫자"가 있다고 가정하고 다음 코드를 사용할 수 있습니다.
셀 D2=IF(NOT(AND($B1=$B2,$C1=$C2)),IFERROR(MAX(INDIRECT("D1:D"&ROW(D2)-1))+1,1),0)
셀 E2=IF(NOT(AND($B2=$B3,$C2=$C3)),IFERROR(MAX(INDIRECT("E1:E"&ROW(E2)-1))+1,1),0)
코드를 테이블의 전체 열 길이 아래로 확장하세요. 결과는 다음과 같습니다.
Number Band Color Start End
10 A A1 1 0
20 A A1 0 0
30 A A1 0 1
40 B A1 2 2
50 B A2 3 3
60 C B1 4 0
70 C B1 0 4
80 A B1 5 5
90 A A1 6 6
100 B A1 7 7
여기에서 각 세트(1,2,3,...)의 번호를 조회하고 행 번호를 찾아 원하는 정보를 얻으세요. 시작/끝 열을 먼저 배치하면 vlookup이를 사용할 수 있습니다.
각 쌍의 첫 번째 모양만 원하는 경우(그런데 왜 행 8이 제외됩니까?) 조금 더 많은 논리가 필요합니다. 여기에는 추가 열이 필요할 수 있지만 추가 논리는 매우 간단합니다.