☰

값 목록을 반환하고 제품 유형과 일치

tag-icon • tag-icon microsoft-excel search-indexing
값 목록을 반환하고 제품 유형과 일치

특정 교육에 대한 액세스 만료에 대한 보고서를 생성하는 공식을 찾고 있습니다.

현재 내 테이블은 다음과 같습니다.

이름 훈련1 훈련2 훈련3
남자 2023년 1월 8일 2023년 1월 8일 2023년 1월 8일
표시 2023년 1월 8일 2023년 1월 6일 2023년 1월 10일
스티브 2023년 1월 6일 2023년 1월 6일 2023년 1월 6일

지금부터 45일 이내에 교육 만료를 표시하고 이름 목록(모듈 및 날짜)과 함께 만료되는 메커니즘을 만들고 싶습니다.

45일 이내에 만료:

이름 기준 치수 만료 날짜
남자 훈련1 2023년 1월 8일
남자 훈련2 2023년 1월 8일
남자 훈련3 2023년 1월 8일
표시 훈련1 2023년 1월 8일

만료:

이름 기준 치수 만료 날짜
표시 훈련2 2023년 1월 6일
스티브 훈련1 2023년 1월 6일
스티브 훈련2 2023년 1월 6일
스티브 훈련3 2023년 1월 6일

이름을 나열하는 수식을 얻었지만 헤더나 날짜에서 훈련을 일치시키는 방법을 얻을 수 없습니다.

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(B:D)/(B:D<TODAY()+45), ROW(1:1))),"") / expiring within 45 days
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(B:D)/(B:D<TODAY()), ROW(1:1))),"") / expired

각 개인의 교육 헤더 및 날짜와 일치하도록 수식을 다시 작성하는 방법은 무엇입니까?

답변1

이를 달성하는 한 가지 접근 방식은 LET()중간 결과를 저장할 수 있는 문을 사용하는 것입니다. 이렇게 하면 데이터를 올바른 형식으로 가져오고 조건에 따라 필터링할 수 있습니다(예: 만료된 훈련의 경우 날짜 < 오늘).

절차를 더 쉽게 소화할 수 있도록 이름과 모듈의 수를 다르게 하여 예제를 약간 확장했습니다. 데이터는 다음 범위에 저장됩니다 A1:D5.

이름 훈련1 훈련2 훈련3
남자 2023년 1월 8일 2023년 1월 8일 2023년 1월 8일
표시 2023년 1월 8일 2023년 1월 6일 2023년 1월 10일
스티브 2023년 1월 6일 2023년 1월 6일 2023년 1월 6일
청구서 2023년 1월 6일 2023년 1월 8일 2023년 1월 10일

진술 LET()은 다음과 같습니다.

=LET(
data,$A$1:$D$5,

nMod,COLUMNS(INDEX(data,1,))-1,
nRow,ROWS(INDEX(data,,1))-1,

tmpM,CHOOSEROWS(TRANSPOSE(INDEX(data,1,)),SEQUENCE(nMod,,2)),
modules,SORT(INDEX(tmpM,MOD(SEQUENCE(nMod*nRow)-1,nMod)+1)),

tmpN,CHOOSEROWS(INDEX(data,,1),SEQUENCE(nRow,,2)),
names,INDEX(tmpN,MOD(SEQUENCE(nMod*nRow)-1,nRow)+1),

dates,TOCOL(CHOOSEROWS(CHOOSECOLS(data,SEQUENCE(nMod,,2)),SEQUENCE(nRow,,2)),0,TRUE),

combinedTable, HSTACK(names,modules,dates),
filteredData,SORT(FILTER(combinedTable, INDEX(combinedTable,,3)<TODAY()),1),

result, VSTACK(HSTACK("Name", "Module", "Expiring on"), filteredData),
result)

A1:D5예를 들어 이 경우에는 데이터 범위를 지정하는 것부터 시작합니다 . 이는 명령문이나 유사한 내용을 포함하여 잠재적으로 더욱 자동화될 수 있지만 COUNTA()범위를 한 번만 지정하면 되며 대부분의 경우 이것으로 충분합니다. 다른 모든 것은 자동으로 계산됩니다.

다음으로, "이름"과 "헤더"가 관련 모듈이나 행이 아니라는 점을 고려하여 "데이터"의 열/행 수 - 1로 모듈 및 행 수를 계산하는 것부터 시작합니다. 다음 단계에서는 각 모듈 이름(예: Training1-Training3)을 tmpM. 모듈은 관련 모듈 및 행 수에 따라 x회 반복되어야 합니다. 이를 달성하기 위해 및 를 INDEX()결합 합니다 . 모듈을 기반으로 필요할 때마다 이름을 반복하는 각 이름 시퀀스를 생성하려면 동일한 절차가 반복됩니다. 마지막으로 모든 날짜를 행렬로 사용하지 않고 하나의 열에 수직으로 쌓아야 합니다. 이를 위해 주로 기능을 사용합니다. 또한 날짜만 선택합니다. 즉, 추가로 및 를 사용하여 이름과 헤더를 선택하지 않습니다 . 이 작업이 완료되면 를 사용하여 "이름", "모듈" 및 "날짜"를 수평으로 함께 쌓습니다 . 결과 테이블은 다음과 같습니다.MOD()SEQUENCE()TOCOL()CHOOSEROWSCHOOSECOLSHSTACK()

ㅏ 비 씨
남자 훈련1 2023년 1월 8일
표시 훈련1 2023년 1월 8일
스티브 훈련1 2023년 1월 6일
청구서 훈련1 2023년 1월 6일
남자 훈련2 2023년 1월 8일
표시 훈련2 2023년 1월 6일
스티브 훈련2 2023년 1월 6일
청구서 훈련2 2023년 1월 8일
남자 훈련3 2023년 1월 8일
표시 훈련3 2023년 1월 10일
스티브 훈련3 2023년 1월 6일
청구서 훈련3 2023년 1월 10일

다음 단계는 FILTER()날짜가 < 인 데이터를 필터링하는 간단한 명령문 입니다 TODAY(). 이를 위해 세 번째 열, 즉 날짜를 기준으로 "combinedTable"을 필터링하려고 합니다. 필터 문에서 이 정보를 사용하려면 INDEX()함수를 사용합니다.

향후 45일 이내의 데이터를 필터링하려면 날짜 > 및 날짜 <= FILTER()가 있는 사례만 필터링하도록 다음과 같이 명령문을 조정해야 합니다 .TODAY()TODAY()+45

filteredData,SORT(FILTER(combinedTable,
(INDEX(combinedTable,,3)>TODAY())*(INDEX(combinedTable,,3)<=TODAY()+45)),1),

데이터가 필터링되면 결과를 수집합니다. 즉, 관련 헤더를 포함하고 명령문을 사용하여 VSTACK()헤더를 필터링된 데이터와 결합합니다. 출력은 모든 관련 정보를 포함하고 단일 셀에 하나의 수식만 필요한 단일 분산 배열입니다. 최종 출력은 다음과 같습니다.

만료:

이름 기준 치수 만료 날짜
청구서 훈련1 2023년 1월 6일
표시 훈련2 2023년 1월 6일
스티브 훈련1 2023년 1월 6일
스티브 훈련2 2023년 1월 6일
스티브 훈련3 2023년 1월 6일

45일 이내에 만료:

이름 기준 치수 만료 날짜
청구서 훈련2 2023년 1월 8일
남자 훈련1 2023년 1월 8일
남자 훈련2 2023년 1월 8일
남자 훈련3 2023년 1월 8일
표시 훈련1 2023년 1월 8일

중간 단계를 표시하려면 공식의 마지막 "결과"를 정의된 다른 이름(예: "combinedTable", "dates" 등)으로 바꾸면 됩니다.

관련 정보