나는 주변을 검색해 왔고 내 공식이 작동하지 않는 이유를 찾기 위해 애쓰고 있습니다.
공식을 게시하기 전에 내가 하려는 작업이 무엇인지 설명해야 하는데 약간 복잡합니다(적어도 이것이 제가 생각할 수 있는 유일한 방법입니다).
**배경**
내 부서의 예산(범주별로 정렬)에 대한 스프레드시트를 만들어야 하는데, 현재 재무 시스템에서 수입/지출을 다운로드하면 날짜별로 정렬된 데이터 행(RAW 데이터)만 펌핑됩니다.
목표 1- 내보낸 RAW 데이터를 카테고리별로 자동으로 정렬하면 각 카테고리에 지출된 금액이 표에 명확하게 표시됩니다.달성됨
목표 2- 내보낸 RAW 데이터를 카테고리별로 정렬하고 사용자 선택에 따라 특정 월만 표시합니다.갇힌
원시 데이터 내보내기 예:
표: 데이터2015
Date | Description of Expense | Amount | Codes
Jan-15 | Workshop for employee | 100000 | WRKS
Jan-15 | Workshop for employee | 100000 | WRKS
Feb-15 | Sponsors for employee | 200000 | SPON
Feb-15 | Sponsors for employee | 100000 | SPON
Feb-15 | Workshop for employee | 300000 | WRKS
Mar-15 | Sponsors for employee | 100000 | SPON
위의 표를 RAW 데이터("Data2015"로 명명됨)의 예로 사용하면 재무 시스템에서 내보낼 때 얻습니다(Excel 내에서 표로 변환함).
목표 1을 달성하기 위해 제가 한 일은 카테고리에 대한 설명이 포함된 새 테이블("카테고리"라는 이름)을 만드는 것이었습니다.
표: 카테고리
Categories | Codes
Workshop | WRKS
Sponsors | SPON
그런 다음 아래와 같이 다른 테이블("예산"이라는 이름)을 만들었습니다.
표: 예산
Categories | Codes | Amount
Workshop | WRKS | 500000
Sponsors | SPON | 400000
예산 테이블의 금액 열에 사용한 공식은 다음과 같습니다.
=SUMIF(Data2015[Codes],[@Codes],Data2015[Amount])
따라서 이 공식은 목표 1을 달성하는 데 도움이 됩니다.
목표 2를 달성하기 위해 다음을 시도했습니다.
사용자가 보고 싶어하는 달을 캡처하는 테이블을 만들었습니다.
표: 조건
Month | X | Background
Jan-15 | x | 1/01/2015
Feb-15 | x | 1/02/2015
Mar-15 | x | 1/03/2015
사용자는 특정 달의 비용을 확인하려는 경우 X 열에 "x"를 입력합니다.
배경 열에 다음 공식을 사용했습니다.
=IF([@X]="x",TEXT([@Month], "d/mm/yyyy"),"")
그런 다음 이전과 같이 월을 카테고리로 추가 필터링하고 싶습니다.
위의 카테고리 테이블과 유사하지만 사용자가 보고 싶어하는 내용을 기반으로 특정 달에 대한 것입니다.
나는 그것이 일종의 작동하도록 관리했지만 전체 테이블이 아닌 한 줄에 대해서만 작동했습니다.
내 공식을 검토하고 내가 하고 있는 것이 잘못되었거나 목표 2를 달성하는 논리가 잘못되었을 수도 있음을 알려주시기 바랍니다. 귀하의 통찰력은 매우 도움이 될 것이며 매우 혼란스러운 상황에 대해 사과드립니다. 더 쉽게 설명할 수 있는 방법이 없습니다.
다음 수식을 사용하여 Data2015 시트를 모방하는 또 다른 스프레드시트를 만들었습니다.
=IF(ISNUMBER(SEARCH(Month_View!$D$5, $A5)),Data2015[@Account], IF(ISNUMBER(SEARCH(Month_View!$D$6,$A5)),Data2015[@Account],""))
월_보기! 어디야?표: 조건$D$5는 1월 15일, $D$6은 2월 15일 등입니다.
이 수식은 A5(날짜가 있는 열)가표: 데이터2015의 Date 열(동일한 행에 있으므로 @가 표시됨)
셀 A5에 "Jan-15" 또는 "Feb-15"가 있으면 지금까지는 괜찮습니다. 작동합니다. 사용자의 기본 설정에 따라 이 셀을 채우기 위해 다음 수식을 사용했습니다(원하는 결과가 나오지 않음).
=IF(Condition[Background]=TEXT(Data2015[@Date],"d/mm/yyyy",Data2015[@Date],"")
따라서 제가 볼 때 해당 공식은 다음을 수행하기 위한 것입니다
.데이터2015테이블은 다음의 날짜/값("d/mm/yyyy" 형식) 중 하나와 일치합니다.상태"Background"라는 이름의 열(사용자가 보려는 달에 "x"를 입력한 경우에만 표시됨)에서 이 셀을 다음 값으로 채웁니다.데이터2015의 날짜 열입니다.
셀이 "Jan-15" 또는 "1/01/2015"로 채워지면 테이블의 나머지 부분이 채워지고 목표 1을 달성하는 데 사용한 수식을 사용하여 범주별로 추가 필터링하는 데 사용할 수 있습니다.
하지만 셀이 "Jan-15" 또는 "1/01/2015"로 채워지지 않고 대신 공백 또는 #VALUE로 표시됩니다!
를 참고한 것 같습니다.상태@가 있는 테이블도 원하지 않습니다.
혼란스럽긴 하지만... 내가 달성하려는 것이 무엇인지 이해할 수 있다면 어떤 통찰력이나 토론도 도움이 될 것입니다. 어쩌면 이 단계에서 내 두뇌가 너무 과부하된 것일 수도 있습니다.
답변1
Excel에 내장된 표 기능을 사용하겠습니다. 수식이 필요하지 않습니다. 범위를 선택하고 Ctrl-T를 눌러 시작하세요.
그러면 맨 위 행의 필터가 활성화됩니다. 귀하/귀하의 사용자는 이를 사용하여 연도 및/또는 월별로 날짜를 필터링할 수 있습니다.
합계 기능의 경우 합계 행을 추가하고(표 도구/디자인 리본 사용) 해당 행에서 각 열에 대해 원하는 집계(합계, 개수 등)를 선택할 수 있습니다. 필터링을 따르도록 자동으로 조정됩니다.
답변2
알았어 그럼 해결했어!
내가 사용한 공식은 다음과 같습니다.
=SUM(SUMIF(Data2015BG1[Category],[@Codes],Data2015GB1[Amount]))
*PS: 참조 내용이 제 질문에 제공된 예와 다르다면 죄송합니다.
Data2015BG1 시트에는 Trigger 열에 "x"가 있는지 여부를 감지하고 선택한 월을 기준으로 필요한 데이터를 표시하는 수식이 있습니다. 내가 사용한 공식은 다음과 같습니다.
=IF(ISNUMBER(MATCH(TEXT(Data2015[@Period],"d/mm/yyyy"),Condition2015A[Background],0))=TRUE,Data2015[@Period],"")
말이 되기를 바라면서, 제가 시각적으로 달성한 것을 정확히 보여드리기 위해 사진을 게시했을 것입니다. 본질적으로 제가 한 일은 모든 사람이 사용하기 쉽게 만들었지만 아직 이미지를 게시할 만큼 충분한 평판을 얻지 못했기 때문입니다! 아마 다음 번에.
필터가 작동했을 텐데 Excel 경험이 거의 없는 사람들을 위해 이것을 만들고 있으므로 필터를 요청하는 것조차 약간 과합니다(... 알아요, 알아요...)