☰

Как создать последовательность 1,2,3,... в Excel, где каждый элемент повторяется N раз

tag-icon • tag-icon microsoft-excel
Как создать последовательность 1,2,3,... в Excel, где каждый элемент повторяется N раз

Давайте рассмотрим последовательность: 1,2,3,4,5.... Очевидно, что ее легко создать в Excel. Но как создать производную последовательность, где каждый из членов исходной последовательности встречается N раз подряд?

Для N=3 1,1,1,2,2,2,3,3,3,4,4,4,... и т.д.

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

Спасибо за ваши идеи.

решение1

Самым простым решением для этой задачи является использование обычной формулы Excel.

Если вы начинаете с ячейки A1, вставьте эту формулу и скопируйте ее:

=ROUNDUP(ROWS(A$1:A1)/3,0)

ОКРУГЛЯТЬокругляет число дондесятичных знаков. В этом случае,нустановлено значение 0, поэтому Excel округлит значение до ближайшего целого числа.

РЯДЫподсчитывает количество строк в диапазоне. Отрегулируйте ссылку на диапазон в соответствии с вашим начальным местоположением. При копировании/вставке Excel выполнит корректировку для других ячеек.

Примечание: Знак доллара здесьважный. Он блокирует ссылку на начало диапазона, так что он будетвсегдабыть в начальной строке вашего диапазона (или, точнее, в строке первой ячейки, в которую вы вставляете формулу — так что начните с этого в первой строке вашего диапазона) независимо от того, как вы копируете/вставляете формулу. Без этого ваша формула сломается, когда вы ее копируете. Поэтому измените буквы и цифры в соответствии с тем, где будет находиться ваша первая ячейка, но оставьте знак доллара там.

введите описание изображения здесь

/3есть ли там, чтобы разделить количество ROWS на три. Поскольку это внутри функции ROUNDUP, и она установлена ​​на 0 десятичных знаков, все результаты будут округлены до ближайшего целого числа. После того, как формула будет заполнена до конца столбца, это приведет к тому, что каждое число повторится 3 раза. Чтобы получить другую частоту повторения, измените 3 на другое число.

Если вы хотите это сделатьчерезаряд, вместовнизастолбец(например: в A1, B1, C1,... вместо A1, A2, A3,...) вам нужно будет использовать функцию COLUMNS вместо ROWS и настроить ссылку на диапазон так, чтобы был заблокирован начальный столбец вместо начальной строки.

Пример:

=ROUNDUP(COLUMNS($A1:A1)/3,0)

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

=ROUNDUP(COLUMNS($A1:A1)*ROWS(A$1:A1)/3,0)

Для чего-то непоследовательного и/или нечислового вам нужно будет выделить диапазон, который будет использоваться в качестве исходного списка. Допустим, ваш список исходных значений находится в столбце B, начиная с B1. Для этого, чтобы повторить значения по 3 раза в столбце A, вы используете это в A1 и копируете вниз:

=INDIRECT(CONCATENATE("B",ROUNDUP(ROWS(A$1:A1)/3,0)))

Примечание: это не будет корректироваться для перемещения исходного столбца. Вам придется вручную исправить формулу, изменив ее "B"в соответствии с новым местоположением, если исходный столбец перемещен. Если исходные данные начинаются не со строки 1, вам нужно будет немного подправить формулу, чтобы добавить смещение.

Если вы хотите иметь возможность регулировать частоту повторения, используйте ячейку ссылки для делителя. Пример ниже начинается с предыдущей формулы и предполагает, что делитель будет в C1.

=INDIRECT(CONCATENATE("B",ROUNDUP(ROWS(A$1:A1)/C1,0)))

решение2

Один из них, как я обнаружил, хорошо работает для динамических массивов:

=ROUNDDOWN(SEQUENCE(count*repeats,,1,1/repeats),0)

например, для количества=2, с повторениями=3

=ROUNDDOWN(SEQUENCE(6,,1,1/3),0)

Производит:

1,1,1,2,2,2

решение3

Чтобы быть гибким и повторно используемым в других ситуациях (включая такие функции, как размещение результатов в строках или столбцах, различных рабочих книгах и т. д.) - лучшим подходом является использование макроязыка Excel (VBA). Чтобы быстро приступить к работе, вы можете записать приблизительные шаги, которые вы хотите автоматизировать, в макрос, а затем отредактировать этот макрос для тонкой настройки и настройки. Например, вот код vba для утроения каждой ячейки первого столбца в третий столбец:

Sub triplicate()

iDestCol = 3
nCopy = 3
rowEnd = ThisWorkbook.ActiveSheet.Range("a" & Rows.Count).End(xlUp).Row
nIndex = 0
With ThisWorkbook.ActiveSheet

For i = 1 To rowEnd
    For j = 1 To nCopy
        Cells((nIndex + j), iDestCol) = Cells(i, 1)
    Next j
    nIndex = nIndex + nCopy

Next i
End With

End Sub

Связанный контент