Вопрос или проблема
Я хотел бы написать функцию Excel, которая использует SUMIF для столбца и ссылается на столбец по его номеру, а не по букве. Я знаю о ссылках R1C1, но не хочу переключать всю книгу на этот тип ссылок. Есть ли способ использовать что-то вроде ссылки R1C1 только в одном случае в книге?
Возможно, вместо использования любых нестабильных функций, таких как OFFSET( ) и INDIRECT( ), можно использовать функцию INDEX().
=SUMIF(INDEX(A1:C3,,1),"a",INDEX(A1:C3,,3))
Дружеское предложение
OFFSET( ) — это гибкая функция, и она может указывать на диапазон другого размера, который вычисляется на лету. Но это нестабильная функция. Если вы перейдете в совершенно несвязную ячейку и введете число, все функции OFFSET( ) будут рассчитываться — даже если эта ячейка не имеет ничего общего.
Было обнаружено, что Excel очень осторожно тратит время на расчет ячеек, которые нужно вычислить. Но как только вы используете OFFSET( ), все ячейки OFFSET( ), плюс все ниже по потоку от OFFSET(), начинают рассчитываться после каждого изменения на листе.
Почему это важно? INDEX( ) не нестабильна. Вы получаете все преимущества гибкости OFFSET( ) без постоянных затрат времени на повторные вычисления.
Пример данных, использованных, как показано Ике Мадам.
Вы можете использовать OFFSET
=SUMIF(OFFSET(D1:D3,,-3),"a",OFFSET(D1:D3,,-1))
.
Ответ или решение
Как сослаться на столбец по номеру в Excel: Указание на использование функции SUMIF с помощью INDEX
В Excel существует потребность в использовании функций, которые могут обращаться к столбцам по их номеру, а не по стандартным буквенным обозначениям. Это может быть особенно полезно, когда необходимо применять функции, такие как SUMIF, на основе динамических условий, но с ограничениями на использование некоторых функций из-за их волатильности, таких как OFFSET и INDIRECT.
Правильный подход
Вместо того чтобы менять всю книгу на режим R1C1, вы можете использовать комбинацию функции INDEX(), которая позволяет вам динамически обращаться к диапазонам, не прибегая к сразу жестким ссылкам.
Использование функции INDEX()
Функция INDEX() может быть использована для получения значения из заданного диапазона. Например, если ваши данные находятся в диапазоне A1:C3 и вам нужно просто сослаться на первый и третий столбцы, вы можете использовать следующую формулу:
=SUMIF(INDEX(A1:C3,,1),"a",INDEX(A1:C3,,3))Разбор:
- INDEX(A1:C3,,1) – это указывает на весь первый столбец в диапазоне A1:C3.
- "a" – это критерий для функции
SUMIF. - INDEX(A1:C3,,3) – это указывает на весь третий столбец, который будет использоваться для суммирования значений, соответствующих критерию.
Преимущества использования INDEX()
INDEX() не является волатильной функцией, как это делает OFFSET или INDIRECT. Это означает, что при изменении данных в вашей таблице будут пересчитываться только те ячейки, которые используют INDEX(), что существенно увеличивает производительность работы с Excel, особенно при больших объемах данных.
Альтернативный вариант: Использование OFFSET()
Может быть, вас также заинтересует использование функции OFFSET(), чтобы сослаться на столбцы по номеру:
=SUMIF(OFFSET(D1:D3,,-3),"a",OFFSET(D1:D3,,-1))Эта формула работает следующим образом:
OFFSET(D1:D3,,-3)смещает диапазон на 3 столбца влево (к столбцу A), что соответствует первому столбцу.OFFSET(D1:D3,,-1)– этот аргумент возвращает значения из третьего столбца.
Хотя OFFSET() предлагает гибкие возможности для указания диапазонов, следует помнить о его волатильности, которая может замедлить работу вашей книги.
Заключение
Использование функции INDEX() является предпочтительным способом обращения к столбцам по их номеру в Excel, так как это обеспечивает надежность и производительность. Вы можете спокойно использовать ее в качестве альтернативы, устраняя необходимость в глобальных изменениях на R1C1. С помощью реляционных функций, таких как SUMIF, вы сможете сохранить эффективность и простоту при работе с вашими данными. Итак, обратите внимание на использование INDEX(), чтобы повысить производительность ваших расчетов и избежать избыточных вычислений.