Формула Excel: Створення масиву чисел -

Загальна формула

(=ROW(INDIRECT(start&":"&end)))

Резюме

Для створення масиву чисел, таких як (1; 2; 3; 4; 5), ви можете використовувати формулу на основі функцій ROW та INDIRECT. Цей прийом найчастіше використовується у формулах масивів, які потребують числового масиву для певної обробки. У наведеному прикладі формула в D5 має вигляд:

(=ROW(INDIRECT(B5&":"&C5)))

який повертає масив типу (1; 2; 3; 4; 5).

Примітка: при введенні в одну клітинку Excel відображатиме лише перший елемент у масиві. Використовуйте F9 у рядку формул, щоб побачити фактичний результат масиву. Зазвичай ви будете використовувати цю формулу всередині більшої формули масиву, введеної за допомогою control + shift + enter.

Пояснення

Примітка. У Excel 365 нова функція ПОСЛІДОВНОСТІ є кращим та простішим способом створення масиву чисел. Пояснений нижче спосіб буде працювати в попередніх версіях.

Ядром цієї формули є рядок, що представляє рядки. Наприклад, щоб створити масив з 10 числами, ви можете жорстко закодувати рядок у INDIRECT, як це:

=ROW(INDIRECT("1:10"))

Функція INDIRECT інтерпретує цей текст як діапазон 1:10 (10 рядків), а функція ROW повертає номер рядка для кожного рядка в цьому діапазоні всередині масиву.

У наведеному прикладі використовується більш загальна версія формули, яка визначає початкові та кінцеві числа з B5 і C5 відповідно, тому рішення виглядає так:

=ROW(INDIRECT(B5&":"&C5)) =ROW(INDIRECT(1&":"&5)) =ROW(INDIRECT("1:5")) =ROW(1:5) =(1;2;3;4;5)

Причиною використання INDIRECT у формулі є захист від змін робочого аркуша. Без INDIRECT вставка або видалення рядків може змінити посилання на діапазон, наприклад:

=ROW(1:5)

зміниться на:

=ROW(1:4)

Якщо рядок 1 видалено. Оскільки INDIRECT працює з посиланням, побудованим з текстом, це не впливає на зміни на аркуші.

Відносні номери рядків у діапазоні

Якщо вам потрібен масив, який складається з відносних номерів рядків діапазону, ви можете скористатися такою формулою:

=ROW(range)-ROW(range.firstcell)+1

Повне пояснення див. На цій сторінці.

Негативні значення

Функція ROW не обробляє від’ємні числа, тому ви не можете змішувати від’ємні числа для початку та кінця . Однак ви можете застосувати математичні операції до масиву, створеного за допомогою ROW. Наприклад, наступна формула створить цей масив: (-5; -4; -3; -2; -1)