Excel двоеточие в функции

Question

Я изучаю функции и моделирование Excel в Google, а также метод проб и ошибок, поэтому просто хочу уточнить следующий момент, чтобы понять мои основные положения:

Когда дело доходит до двоеточия в Excel, я понимаю, что это означает определение диапазона ячеек, но когда я вхожу =F3:F11 Excel дает результат 30. Могу ли я знать, почему это так? Я пытался интерпретировать это с помощью усреднения / режима и т.д. И оценивал формулу, но не смог найти никакого логического смысла.

Answer 1

F3:F11 - это диапазон, но вы вводите его в месте, где уместно указать только одно значение, поэтому Excel пытается выбрать одно значение из диапазона, используя следующие правила:

• Если диапазон находится в одном столбце (как это есть), Excel выбирает ячейку из этого столбца в той же строке, что и ссылающаяся ячейка (или ошибка #VALUE! если диапазон не пересекает эту строку)
• Если диапазон находится в одной строке, Excel выбирает ячейку из этой строки в том же столбце, что и ссылающаяся ячейка (или ошибка # #VALUE! если диапазон не пересекает этот столбец)
• Если диапазон является двухмерным, Excel выбирает ячейку из той же строки и столбца, что и ячейка ссылки (или ошибка # #VALUE! если диапазон не пересекает строки и столбцы диапазона) - очевидно, это работает только тогда, когда диапазон и вызывающая ячейка находятся на разных листах

Внимание:

• Если ссылка на диапазон дается там, где диапазон или массив уместны, будет использоваться весь диапазон - поэтому в ячейке M4 =F3:F11+1 будет 31, но =sum(F3:F11,1) будет 331.
• Если эта формула будет введена как формула массива (используя ctrl+shift+enter) в одной и той же ячейке, она вернет весь массив, но вы увидите только одну ячейку, поскольку это все, что вписывается в диапазон результатов. Результат будет 10. Предположительно Google ARRAYFORMULA работает так же.

Когда это полезно?

В общем, я не люблю использовать это поведение при обращении к ячейке в той же таблице - обычная относительная ссылка (F4) работает так же хорошо, и мне не нужно беспокоиться о том, как мои формулы будут интерпретировать ссылку. (См. Предостережение выше.)

Однако одно из применений - это когда вы хотите, чтобы один рабочий лист совмещался один с другим. Я могу привести нужные столбцы в ссылку на столбец (например, =Sheet1!$A:$A) и пусть остальные столбцы будут вычисляемыми полями. Я мог бы сделать это и с относительной ссылкой (например, =$A1 и перетащить вниз), но ссылки на столбец имеют преимущества - я могу вставлять, удалять или сортировать строки в исходном листе, не нарушая ссылок. (С ссылками на одну ячейку я получу, при вставке, строку, отсутствующую в ссылочных данных; при удалении, #REF! ошибка; на сортировке, эти два листа больше не будут в том же порядке.)