Excel - 시작 문자열을 기준으로 행을 필터링하고, 필터링된 행에서 숫자를 읽고, 산술 연산을 수행하여 열 요약을 생성합니다.

Question

프로젝트 및 자원 할당 보고서를 작성하고 있습니다. 보고서 하단 섹션에 요약을 표시하기 위한 수식을 생성하는 데 도움이 필요합니다.

내 보고서의 사양은 다음과 같습니다.

• Column Afrom Row 2to는 Row 15각 프로젝트에 할당된 자원(사람)을 나타냅니다.

• Column B, Column C....는 매월 각 프로젝트에 할당된 자원의 비율을 나타냅니다.

• 각 리소스는 column A매월 1개 또는 2개의 프로젝트에서 작업할 수 있으므로 의 각 리소스는 2개의 셀로 병합됩니다.

• 한 달에 단일 프로젝트를 진행하는 리소스에는 두 셀이 모두 병합됩니다. 2개의 프로젝트에서 작업하는 리소스에는 프로젝트를 나타내는 2개의 행이 있습니다.

• 각 프로젝트에는 프로젝트에 할당된 해당 자원의 비율을 나타내는 숫자가 있습니다.

내 보고서의 스크린샷은 다음과 같습니다.

하단 보고서에는 프로젝트당 할당된 리소스 수를 나타내는 요약 섹션이 있습니다. 현재 이러한 개수는 제가 수동으로 추가합니다. 이 요약을 위한 공식을 생성하는 데 도움이 필요합니다. 내가 달성하려는 것은 다음과 같습니다.

1. 셀(A20 - A24)에서 문자열 읽기
2. 월 열(B2 - B15)을 조회하여 프로젝트 문자열로 시작하는 셀을 가져옵니다.
3. 위 셀의 숫자 필터링(2단계)
4. 숫자를 합산하고 나누어서 100자원 수를 얻습니다.

지금까지 전달된 행에서 숫자를 읽는 이 수식을 생성할 수 있습니다.

=TEXTJOIN("",TRUE,IFERROR((MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1),""))

텍스트 가 포함된 50셀의 번호를 알려줍니다 .B2"Project A 50%"

Answer 1

O365가 있는 경우 다음 FILTER기능을 사용할 수 있습니다.

• A20:Ann의 내용으로 목록을 필터링합니다.

• 각 문자열에서 공백으로 구분된 마지막 값만 반환합니다(백분율).

• 결과 합계

• IFERROR프로젝트가 존재하지 않는 경우 사용

    =IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,ISNUMBER(SEARCH($A20,B$2:B$15)))," ",REPT(" ",99)),99))))),"")
  

또는(0을 반환할지 또는 ""존재하지 않는 항목을 반환할지 여부에 따라 다름)

    =IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,ISNUMBER(SEARCH($A20,B$2:B$15)))," ",REPT(" ",99)),99))))),"")

편집하다:댓글에서 OP는 프로젝트 이름이 유사할 수 있지만 앞에 문자가 추가될 수 있으며 별도로 처리되어야 한다고 언급합니다. 이를 위해서는 다른 필터 기준이 필요합니다.

=IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,LEFT(B$2:B$15,LEN($A20))=$A20)," ",REPT(" ",99)),99))))),"")

Answer 2

2~15행의 데이터 표현은 두 가지 정보를 하나의 셀에 결합하기 때문에 엉망입니다. 그러면 기본적으로 모든 숫자 셀 기능의 유용성이 비활성화됩니다. 프로젝트 식별자와 리소스 금액을 두 개의 열로 나눕니다.

B열에 프로젝트 식별자가 있고 C열에 자원이 있으면 셀 B20을 "=sumif(b$2:b$15;"A";c$2:c$15)"로 설정하면 됩니다. 프로젝트 B의 경우 해당 수식을 "B"로 수정합니다.

수식을 수정하지 않고 더 빠르게 입력하려면 ABCD ....가 포함된 숨겨진 열을 만들고 위 수식의 고정 문자열이 숨겨진 값 ABCD ....를 가리키도록 합니다.

Answer 3

아마도 이 답변이 별로 마음에 들지 않을 것입니다. 하지만 제발열린 마음을 유지

아마도 이번이 이 테이블을 분석해야 하는 마지막 시간은 아닐 것입니다. 지금 구조를 조정하면 나중에 인생이 더 편해질 것입니다.

1. 병합된 셀은 당시에는 좋은 생각처럼 느껴지지만 나중에는 슬픔만 안겨줄 것입니다.
2. 변수를 단일 셀 "프로젝트 A 50%"로 결합하면 이제 해당 변수를 분리하여 이해해야 하므로 항상 분석이 더 어려워집니다.
3. 교차표로 데이터를 입력하는 것은 약간 더 간단할 수 있지만 비용이 듭니다. 이제 프로젝트 A에 배정된 연도별 분기별 자원을 묻는 보고서를 생성해야 한다면 어떻게 됩니까? 또한, 내년이 되면 어떻게 되나요? 새 테이블을 만드시겠어요? 그러면 어떻게 비교하나요?

여기서 무감각하게 말하려는 것은 아니지만 25년 동안 Excel 및 기타 시스템에서 데이터를 작업한 후에 사람들이 나중에 수행해야 할 작업에 대해 생각해 보라고 권하고 싶습니다.

다음과 같은 것이 가장 도움이 될 것입니다.

Ctrl+T를 사용하거나 리본의 홈 탭에서 "표 형식"을 사용하여 표 형식으로 지정됩니다.

이 구조에는 다음과 같은 여러 가지 이점이 있습니다.

1. 이제 새 행을 입력하기만 하면 데이터를 입력하는 것이 매우 쉽습니다.
2. 피벗 테이블을 사용하여 모든 차원(사람, 월, 프로젝트)에 걸쳐 분석을 수행할 수 있습니다. 간단한 질문에 대한 복잡한 공식이 필요하지 않습니다.
3. 이제 차트 작성이 아주 쉬워졌습니다.
4. 각 행에 새 정보를 열로 추가할 수 있습니다. 다른 테이블의 XLOOKUP으로 해당 사람의 직속 관리자나 팀 이름을 행에 추가하거나 해당 월 동안 해당 사람의 성과 또는 휴가에 대한 메모를 추가하려고 할 수 있습니다.
5. 이를 다른 스프레드시트에 복사하거나 다른 사람에게 csv로 보내거나 분석을 위해 데이터베이스 또는 데이터 프레임의 테이블에 로드해야 하는 경우 병합을 해제하고 빈 셀을 채우는 데 시간을 소비할 필요가 없습니다. 네가 그렇게 하기 전에

편집하다:

수정된 데이터 구조로 질문에 답하려면 간단히 피벗 테이블을 생성하면 됩니다.