Применение одноразовой формулы к диапазону ячеек

Question

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

Обратите внимание, что я не хочу сохранять формулу в файле Excel, просто примените ее к диапазону значений один раз и сохраните в ячейке значение результата, а не формулу.

Я постараюсь прояснить ситуацию. Предположим, у меня есть в строке 1 значения в столбцах A, B и C:

20 42 53

Я хочу выбрать диапазон A1:C1 и применить к каждой формуле:

= if($cell > 50; 50; if($cell < 25;25; $cell))

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

В результате я ожидаю:

25 42 50

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

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

Надеюсь, я ясно изложил свою точку зрения.

Answer 1

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

Answer 2

Ячейки в Excel содержат формулы или значения. Вы можете использовать текущую ячейку в формуле, но она игнорируется для вычисления.

Как вы упомянули, у вас могут быть значения на одной странице и формулы на другой. Я не уверен, как эта другая программа обрабатывает файл Excel, но если вы сохраните его в что-то вроде CSV, будут экспортированы только значения (и вы указываете, какой лист экспортируется).

Единственный другой вариант, о котором я могу подумать, - это создать новую строку / столбец, используя формулу для изменения значений. Затем используйте Copy / Paste Special, чтобы вставить обратно только измененные значения в исходных ячейках.

Answer 3

На самом деле решение довольно простое и просто ударило меня несколько минут назад. Я мог бы достичь своей цели написанием простого макроса vba, который просто применял нужные диапазоны к выделенным ячейкам. Код:

Function applyMakeInRange(minVal As Double, maxVal As Double, cells As Range)

Dim cellVal As Double

For Each cell In cells.cells
    cellVal = cell.Value

    If cellVal > maxVal Then
        cellVal = maxVal
    Else
        If cellVal < minVal Then
            cellVal = minVal
        End If
    End If

    cell.Value = cellVal

Next

End Function

Sub makeInRange()

Dim maxVal As Double
Dim minVal As Double
Dim cells As Range

Set cells = Selection

maxVal = InputBox("Max Value")
minVal = InputBox("Min Value")

res = applyMakeInRange(minVal, maxVal, cells)

End Sub

Просто сохраняя файл как без макросов (после того, как он был выполнен), нужно добиться цели