Excel: автоматическое создание диапазонов на основе содержимого отдельных ячеек

Question

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

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

Примером будет взять все ячейки, которые содержат "1" внутри диапазона "main", затем создать диапазон "_1" и добавить к нему ячейки. затем повторите, пока все 60 диапазонов не будут завершены.

Тогда мне также понадобится способ взять диапазоны от "_1" до "_60" и легко добавить к ним условное форматирование для 3 цветовых градиентов, чтобы выделить большие числа в каждом диапазоне после удаления чисел и замены формулой. Возможно, мне придется выполнить эту часть вручную, но было бы намного проще, если бы для каждой диагонали уже были определены диапазоны, поэтому мне не нужно идти и выбирать область поиска ячейки, содержащую "5", определить диапазон "_5", затем условно отформатируйте для каждого числа 1-60 (только 60 в настоящее время, но может оказаться больше).

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

Я могу сделать градиенты вручную после того, как диапазоны сделаны.

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

Упрощенный обзор того, что мне нужно, это:

for all cells inside range "Main"
[  
    read cell

    if range "-[cell]" exists  
    [  
        add cell to range  
    ]  
    else  
    [  
        define range named "-[cell]"  
        add cell to range  
    ]  
]

Как только все ячейки окажутся в диапазоне, я хотел бы что-то сделать для меня градиенты. Что-то вроде «для каждого диапазона от -1 до -60, делайте 3 цветных условных формата».

Я знаю, что будет гораздо больше, чем это, но это существенно все, что мне нужно.

Дайте мне знать, если это невозможно. Это возможно, но спрашивать не вредно.

Answer 1

Это заняло некоторое время, но я думаю, что смогу помочь тебе. Я написал три подпрограммы, которые могут назвать вторичные диагонали. Первым является sub select_diagonal

Sub select_diagonal(matriz As Range, m As Integer, name As String)

Dim n As Integer, i As Integer, first As Boolean
Dim diag As Range

n = matriz.Rows.Count
'm must be at most 2n-1 (number of diagonals)
first = True

If m <= n Then
    For i = 0 To m - 1 Step 1
        If first Then
            Set diag = matriz.Item(1).Offset(i, m - i - 1)
            first = False
        Else
            Set diag = Union(diag, matriz.Item(1).Offset(i, m - i - 1))
        End If
    Next i

Else
    For i = (m Mod n) To m - (m Mod n) - 1 Step 1
        If first Then
            Set diag = matriz.Item(1).Offset(i, m - i - 1)
            first = False
        Else
            Set diag = Union(diag, matriz.Item(1).Offset(i, m - i - 1))
        End If
    Next i
End If

ActiveWorkbook.Names.Add name:=name, RefersTo:=diag

End Sub

Он получает в качестве параметров квадратную матрицу в виде диапазона (ваш диапазон 60x60), целое число m (должно быть не более 2 * n-1, число диагоналей матрицы) и name строки, чтобы назвать m-й вторичный диагональ матрицы.

Существует также name_range , которая получает квадратную матрицу в виде диапазона и зацикливает каждую вторичную диагональ матрицы и присваивает ей именованный диапазон (в соответствии с вашими инструкциями k-я вторичная диагональ называется "_k")

Sub name_range(matriz As Range)

    Dim n As Integer, ii As Integer
    n = matriz.Rows.Count

    For ii = 1 To 2 * n - 1
        Call select_diagonal(matriz, ii, "_" & CStr(ii))
    Next ii

End Sub

Наконец, подпрограмма, которую вы должны запустить, - это просто основная подпрограмма. Просто выберите весь свой диапазон и запустите эту подпрограмму, чтобы вызвать двух других, которые сделают всю работу за вас.

Sub main()

    Dim matriz As Range

    Set matriz = Selection

    Call name_range(matriz)

End Sub

После этого просто примените условное форматирование к каждому диапазону. Как вы только что сказали, вы можете вручную применить градиенты после создания диапазонов. Однако я предлагаю применить его с помощью процедуры VBA. Просто укажите границы цвета и, с уже определенными именованными диапазонами, примените его к каждому именованному диапазону.

PS: я не мог найти способ сделать это без VBA. Самая большая проблема здесь - это определение именованных диапазонов для вторичной диагонали и использование цветовой шкалы с динамическим диапазоном или с некоторыми критериями. Надеюсь это поможет.