Theo는 Excel 워크 시트를 사용하여 회사의 예약을 추적합니다. 데이터는 세 개의 열로만 구성됩니다. 첫 번째는 사람의 이름, 두 번째는 예약의 첫 번째 주 번호 (1-52), 세 번째는 예약의 마지막 주 번호입니다.

사람들은 여러 주 동안 예약 할 수 있습니다 (즉, 시작 주가 15이고 종료 주가 19). Theo는 주 번호를 입력 한 다음 수식을 통해 해당 주 번호와 연결된 이름 (A 열)을 결정해야합니다. 데이터는 특정 순서로 정렬되지 않으며 회사는 Theo가 매크로를 사용하여 결과를 얻도록 허용하지 않습니다 (공식이어야 함).

테오의 상황은 간단하게 들리지만 해결책을 고안 할 때 함정으로 가득 차 있습니다. 잠재적 데이터 (다음 그림 참조)를 살펴보면 그 이유를 빠르게 알 수 있습니다. (그림 1 참조)

image

그림 1. Theo의 문제에 대한 잠재적 데이터.

데이터 (Theo가 말했듯이)는 특정 순서가 아닙니다.

또한 예약이없는 주 (예 : 5 주 또는 6 주), 여러 사람이있는 주 (예 : 11 주 또는 16 주), 누군가 예약 한 주가 있지만 주 숫자는 B 또는 C 열에 표시되지 않습니다 (예 : 12 주 또는 17 주).

잠재적 솔루션을 살펴보기 전에 알고 싶은 주가 E1 셀이라고 가정 해 보겠습니다. 이 범위의 이름을 Query로 지정해야합니다. 또한 사람의 이름 (이 예에서는 셀 A2 : A10)이 포함 된 범위의 이름을 ResNames로, 시작 주 (B2 : B10)를 StartWeeks로, 종료 주 (C2 : C10)를 EndWeeks로 지정합니다. 마지막으로 MyData와 같은 전체 테이블 (A2 : C10)의 이름을 정의합니다. 이 이름 지정은 반드시 필요한 것은 아니지만 수식을 훨씬 쉽게 이해할 수 있도록합니다.

한 가지 잠재적 인 해결책은 일반적으로 “도우미 열”이라고하는 것을 추가하는 것입니다. 다음을 D2 셀에 추가하십시오.

=IF(AND(Query>=B2,Query<=C2),"RESERVED","")

표에 이름이있는만큼 많은 셀에 대해 수식을 아래로 복사합니다. (예를 들어, D10 셀을 통해 아래로 복사하십시오.) E1 셀에 주 번호를 입력하면 해당 주 번호와 관련된 모든 예약의 오른쪽에 “RESERVED”라는 단어가 나타납니다. 또한 그 주에 예약 된 사람이 여러 명인지 또는 그 주에 예약 된 사람이 없는지 쉽게 확인할 수 있습니다. 자동 필터를 적용하고 D 열에 “RESERVED”라는 단어가있는 레코드 만 표시하도록 선택할 수도 있습니다.

원하는 경우 도우미 열을 무시하고 조건부 서식을 사용하여 원하는 주에 예약 된 사람을 표시 할 수 있습니다.

A 열의 이름을 선택하고 다음 공식을 사용하는 조건부 서식 규칙을 추가하기 만하면됩니다.

=AND(Query>=B2,Query<=C2)

(조건부 서식 규칙을 입력하는 방법은 _ExcelTips의 다른 문제에서 광범위하게 설명되었습니다.) 셀에 적용된 음영 (패턴)이 변경되도록 규칙을 설정하면 어떤 예약이 적용되는지 쉽게 알 수 있습니다. 관심있는 주.

또 다른 방법은 배열 수식을 사용하는 것입니다. 예상되는 중복 예약 수보다 더 많은 셀을 선택한 다음 Ctrl + Shift + Enter를 눌러 해당 셀에 다음을 입력합니다.

=IFERROR(INDEX(ResNames,LARGE((StartWeeks<=Query)(EndWeeks>=Query)(ROW(ResNames)),ROW()-1)-1),"")

셀 수를 선택할 때이 배열 수식이 차지하기를 원합니다. 예를 들어 11 주 동안 예약 할 수있는 사람 수를 확인합니다.이 팁에 표시된 예에서는 2 명입니다. 해당 수보다 많은 셀을 선택한 다음 해당 셀에 배열 수식을 입력합니다.

같은 주에 20 명을 예약 할 가능성이 있다고 예상되는 경우 20 명 또는 30 명과 같이 더 많은 수의 셀을 선택하는 것이 좋습니다. 셀을 선택하고 수식 입력 줄에 수식을 입력 한 다음 Ctrl + Shift + Enter를 누릅니다.

마지막으로 데이터 배치 방식을 수정하는 것을 고려해야합니다.

A 열 (1 ~ 52 또는 53)에 주 번호가있는 워크 시트를 만든 다음 B 열에 이름을 입력 할 수 있습니다. 사람이 2 주 동안 예약 된 경우 해당 이름은 B 열에 두 번 표시됩니다. 2 주를 예약했습니다.

이 형식의 데이터를 사용하면 데이터를 쉽게 스캔하여 어떤 주를 사용할 수 있는지, 어떤 주를 가져 갔는지, 누가 가져 갔는지 확인할 수 있습니다.

어떤 종류의 조회를하려는 경우 데이터의 첫 번째 열이므로 정렬 된 순서로 주 번호를 기준으로 VLOOKUP 함수를 사용하는 것이 쉽습니다.

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (11078)은 Office 365의 Microsoft Excel 2007, 2010, 2013, 2016, 2019 및 Excel에 적용됩니다. 여기에서 Excel의 이전 메뉴 인터페이스에 대한이 팁 버전을 찾을 수 있습니다.

link : / excel-Determining_a_Name_for_a_Week_Number [주 번호의 이름 결정].