Давайте рассмотрим последовательность: 1,2,3,4,5 .... Это очевидно легко создать в Excel. Но как бы вы создали производную последовательность, где каждый член исходной последовательности присутствует N раз подряд?
Для N = 3 1,1,1,2,2,2,3,3,3,4,4,4, ... и т.д.
Я закончил тем, что копировал столбец 1 с помощью специальной функции вставки, сдвигая его на N строк вниз каждый раунд. Это, однако, далеко от хорошего решения.
Спасибо за ваши идеи.
Самое простое решение для этого - использовать обычную формулу Excel.
Если вы начинаете с A1, вставьте эту формулу, а затем скопируйте ее:
=ROUNDUP(ROWS(A$1:A1)/3,0)
ROUNDUP округляет число до n десятичных знаков. В этом случае n устанавливается в 0, поэтому Excel округляет до ближайшего целого числа.
ROWS подсчитывает количество строк в диапазоне. Отрегулируйте задание диапазона в соответствии с вашим начальным местоположением Когда вы копируете / вставляете, Excel будет обрабатывать настройки для других ячеек.
Примечание: здесь важен знак доллара. Он блокирует ссылку на начало диапазона, так что он всегда будет в начальной строке вашего диапазона (или, точнее, в строке первой ячейки, в которую вы поместили формулу - поэтому начните с нее в первой строке). из вашего диапазона) независимо от того, как вы копируете / вставляете формулу. Без этого ваша формула сломается, когда вы ее скопируете. Таким образом, измените буквы и цифры в соответствии с тем, где будет находиться ваша первая ячейка, но оставьте там знак доллара.
/3 есть, чтобы разделить количество строк на три. Так как это внутри функции 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)))
Чтобы обеспечить гибкость и возможность повторного использования в других ситуациях (включая такие функции, как размещение результатов в строках или столбцах, различных рабочих книгах и т.д.), Лучшим подходом является использование языка макросов 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