Excel Определить диапазон, основанный на значении ячейки

Можно ли определить диапазон, основанный на значении, указанном в ячейке.

Итак, например: Мой выбор: A1: A5. Это пять ячеек. возможно ли, чтобы excel определял это, установив значение ячейки (например, B1) на 5.

Он с легкостью изменяет множество диапазонов с одним изменением значения ячейки. Поэтому, если бы я изменил значение ячейки (B1) на 6. Диапазон автоматически изменился бы на A1: A6

Еще более конкретным, я хотел бы сделать это вспять.

Окончательный пример: Выбор должен быть A6: A10 (это пять ячеек). В B1 у меня есть значение 5. Если я изменил значение B1 на 6. Диапазон должен измениться на A5: A10

Может кто-нибудь мне помочь???

Ответ 1

Скажите, что у вас есть номер 1,2,3,4,5,6, в ячейках A1, A2, A3, A4, A5, A6 соответственно. в ячейке A7 мы вычисляем сумму A1: Ax. x указан в ячейке B1 (в этом случае x может быть любым числом от 1 до 6). в ячейке A7 вы можете написать следующий формуляр:

=SUM(A1:INDIRECT(CONCATENATE("A",B1)))

CONCATENATE предоставит вам индекс ячейки Ax (если вы положите 3 в B1, CONCATENATE ( "A", B1)) дает A3).

INDIRECT преобразует "A3" в индекс.

см. эту ссылку Использование значения в ячейке в качестве ссылки на ячейку в формуле?

Ответ 2

Вы также можете использовать OFFSET:

OFFSET($A$10,-$B$1+1,0,$B$1)

Он перемещает диапазон $A $10 вверх на $B $1-1 (становится $A $6 ($ A $5)), а затем изменяет размер диапазона до $B $1 строк (становится $A $6: $A $10 ($ A $5: $A $10))

Ответ 3

Это должно быть близко к тому, что вы ищете для своего первого примера:

=SUM(INDIRECT("A1:A"&B1,TRUE))

Это должно быть близко к тому, что вы ищете в своем последнем примере:

=SUM(INDIRECT("A"&1+B1&":A"&B1,TRUE))

Ответ 4

Вот вариант. Он работает, используя INDIRECT(ADDRESS(...)) из ROW и COLUMN стартовой ячейки, A1, вплоть до начальной строки + числа строк, хранящихся в B1.

SUM(INDIRECT(ADDRESS(ROW(A1),COLUMN(A1))):INDIRECT(ADDRESS(ROW(A1)+B1,COLUMN(A1))))

A1: это начало данных в столбце "A"

B1: количество строк для суммы

Ответ 5

Основываясь на ответе @Cici, я приведу здесь более общее решение:

= СУММ (ДВССЫЛ (СЦЕПИТЬ (B1, C1)): ДВССЫЛ (СЦЕПИТЬ (В2, С2)))

В итальянской версии Excel:

= SOMMA (INDIRETTO (CONCATENA (В1, С1)): INDIRETTO (CONCATENA (В2; С2)))

Если ячейки B1-C2 содержат эти значения:

A, 1
A, 5

Вы можете изменить эти значения, чтобы изменить конечный диапазон по желанию.

Разбиение формулы по частям:

СУММА (ДВССЫЛ (СЦЕПИТЬ (B1, C1)): ДВССЫЛ (СЦЕПИТЬ (В2, С2)))
CONCATENATE (B1, C1) - результат A1
INDIRECT (CONCATENATE (B1, C1)) - результат ссылается на A1

Следовательно:

= СУММ (ДВССЫЛ (СЦЕПИТЬ (B1, C1)): ДВССЫЛ (СЦЕПИТЬ (В2, С2)))

приводит к

= СУММ (А1: А5)

Я напишу здесь несколько ключевых слов для SEO для итальянских пользователей:

Приезжайте, создайте dinamicamente l'indirizzo di un intervallo in excel
формула для каждого отдельного интервала в excel.

Con la formula indicata qui sopra basta scrivere nelle caselle da B1 a C2 gli estremi dell'intervallo per vedelo cambiare dentro la formula stessa.