태그 보관물: worksheet-function

worksheet-function

Excel 조직 방법을 알고 있는지 알고

나는 내가 원했던 샘플 버전을 만들었다.

이 양식은 항상 반복되며 중간 행의 금액 값은 항상 달라집니다. 나는 가운데 열의 모든 것이 승인되면 맨 오른쪽 열의 확인을하는 방법을 알고 있는지 알고 싶습니다. 하나 이상의 열이 거부되면 자동으로 거부 된 것으로 표시되고 모두 승인되면 승인됩니다.

이 파일은 수백 개의 예제와 같은 파일이므로 반복 가능하고 확장 가능해야합니다.

enter image description here

답변

이는 VBA없이 수식 만 사용하여 가능합니다.

다음과 같이 워크 시트를 설정했습니다.

Workbook Screenshot

에 다음 공식을 입력하십시오. N1 과 N20: 

=IF(ISNUMBER(MATCH("Rejected",INDEX(G:G,ROW()):INDEX(G:G,IFERROR(MATCH("*",INDEX(L:L,ROW()+1):INDEX(L:L,ROWS(L:L)),0)+ROW()-1,MATCH("",G:G,-1))),0)),"Rejected","Approved")

나는 사용했다. L:L 워크 시트의 정확한 세부 사항을 알 수는 없으므로 공식에 나와 있습니다. 수식이 올바르게 작동하려면 L:L 공백을 포함하지 않는 모든 텍스트 전용 병합 셀 열과 함께.

불행히도 병합 된 셀의 크기가 다르므로 수식을 채울 수 없습니다. 또한 수식을 같은 크기의 병합 된 셀에만 복사하여 붙여 넣을 수 있습니다.

가장 오른쪽 열에 수식을 입력하는 가장 쉬운 방법은 다음 키 누름 순서 중 하나를 사용하는 것입니다.

  • F2 Ctrl + V 들어가다

또는

  • 역행 키이 Ctrl + V 들어가다

속도를 높이기 위해 매크로를 기록 할 수도 있습니다.

prettified 수식은 다음과 같습니다 : 

=
IF(
  ISNUMBER(
    MATCH(
      "Rejected",
      INDEX(G:G,ROW())
      :INDEX(G:G,
        IFERROR(
          MATCH("*",INDEX(L:L,ROW()+1):INDEX(L:L,ROWS(L:L)),0)+ROW()-1,
          MATCH("",G:G,-1)
        )
      ),
      0
    )
  ),
  "Rejected",
  "Approved"
)

노트:

  • prettified 수식 실제로 입력 한 경우 작동합니다.
  • 대괄호 (G:G) prettified 버전에서 강제로 필요합니다 G:G 그것의 자신의 선에 남아 있기 위하여.

편집하다:

상태 값 목록을 단지 Rejected 과 Approved~까지 Rejected, Void, Pending 과 Approved, 당신은 단지 IF() 수식의 함수는 다음과 같습니다. 

=
IF(
  ISNUMBER(
    MATCH(
      "Rejected",
      INDEX(G:G,ROW())
      :INDEX(G:G,
        IFERROR(
          MATCH("*",INDEX(L:L,ROW()+1):INDEX(L:L,ROWS(L:L)),0)+ROW()-1,
          MATCH("",G:G,-1)
        )
      ),
      0
    )
  ),
  "Rejected",
IF(
  ISNUMBER(
    MATCH(
      "Pending",
      INDEX(G:G,ROW())
      :INDEX(G:G,
        IFERROR(
          MATCH("*",INDEX(L:L,ROW()+1):INDEX(L:L,ROWS(L:L)),0)+ROW()-1,
          MATCH("",G:G,-1)
        )
      ),
      0
    )
  ),
  "Pending",
IF(
  ISNUMBER(
    MATCH(
      "Void",
      INDEX(G:G,ROW())
      :INDEX(G:G,
        IFERROR(
          MATCH("*",INDEX(L:L,ROW()+1):INDEX(L:L,ROWS(L:L)),0)+ROW()-1,
          MATCH("",G:G,-1)
        )
      ),
      0
    )
  ),
  "Void",
  "Approved"
)
)
)

훨씬 더 나은 해결책은 배열과 함께 작동하는 수식을 사용하는 것입니다. 

=
CHOOSE(
  SMALL(
    IF(
      ISERROR(
        MATCH(
          {"Rejected","Void","Pending","Approved"},
          INDEX(G:G,ROW())
          :INDEX(G:G,
            IFERROR(
              MATCH("*",INDEX(L:L,ROW()+1):INDEX(L:L,ROWS(L:L)),0)+ROW()-1,
              MATCH("",G:G,-1)
            )
          ),
          0
        )
      ),
      FALSE,
      {1,2,3,4}
    ),
    1
  ),
  "Rejected","Void","Pending","Approved"
)

수식의 병합 된 버전을 선호하는 경우 여기에 있습니다. 

=CHOOSE(SMALL(IF(ISERROR(MATCH({"Rejected","Void","Pending","Approved"},INDEX(G:G,ROW()):INDEX(G:G,IFERROR(MATCH("*",INDEX(L:L,ROW()+1):INDEX(L:L,ROWS(L:L)),0)+ROW()-1,MATCH("",G:G,-1))),0)),FALSE,{1,2,3,4}),1),"Rejected","Void","Pending","Approved")

이 마지막 버전의 공식은 2 개의 상수 배열과 두 번째 마지막 행의 값 목록을 조정하기 만하면 더 많은 (또는 적은) 값으로 쉽게 확장 할 수 있습니다.

값의 정확한 순서에 따라 둘 이상의 값 유형이 적절한 범위에있는 경우 반환되는 값이 결정됩니다. 가장 높은 우선 순위 값은 왼쪽에있는 값입니다.

답변