Организация Excel

Question

Я сделал примерную версию того, что я хотел бы.

Эта форма постоянно повторяется, и значения сумм в средних строках постоянно меняются. Я хотел бы знать, знаете ли вы, как выполнить проверку в крайнем правом столбце, все ли средние столбцы утверждены, и оттуда автоматически будет показано отклонено, если один или несколько отклонены, и утверждено, если все они утверждены.

Это должно быть повторяемым и расширяемым, поскольку это файл с сотнями подобных примеров.

Answer 1

Это возможно, используя только формулу, без каких-либо VBA.

Я настроил рабочий лист следующим образом:

Введите следующую формулу в 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 Enter

или же

• Backspace Ctrl+V Enter

Вы могли бы даже записать макрос, чтобы ускорить процесс.

---

Предварительно подтвержденная формула выглядит следующим образом:

=
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"
)

Заметки:

• Предварительно подтвержденная формула действительно работает, если введена.
• Квадратные скобки (G:G) в предварительно проверенной версии необходимы, чтобы заставить 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")

Последняя версия формулы легко расширяется до большего (или меньшего) значения путем простой настройки двух константных массивов и списка значений во второй последней строке.

Обратите внимание, что точный порядок значений определяет, какое значение возвращается, если в соответствующем диапазоне существует несколько типов значений. Наивысшим значением приоритета является значение слева.