Превратите электронную таблицу Excel в формулу

Question

У меня есть электронная таблица Excel, которая имеет сложные вычисления, которые нетрудно превратить в макрос или формулу из одной ячейки. Электронная таблица имеет около 10 различных входных данных (значения, которые вводит человек в разные ячейки электронной таблицы), а затем выводит 5 независимых вычислений (в 5 различных ячейках) на основе этих входных данных. Для их расчета используются некоторые предварительно введенные данные в электронную таблицу (около 100 различных констант) и выполняется их поиск.

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

Например:

• А1, А2, А3, ... A10 - это ячейки, в которые кто-то вводит значения
• через серию расчетов B1, B2, B3, B4 и B5 обновляются с некоторыми формулами

Можно ли использовать всю серию расчетов от А1 ...A10 в B1 ...B5 без создания одной огромной формулы или макроса VBA?

Я хочу иметь набор входных значений в 100 строк от A100, B100, C100, ... Затем выполните J100 и сделайте магию Excel, которая будет:

1. Скопируйте значения из A100 ...J100 в А1 до А10
2. Подождите, пока результат появится в B1 до B5
3. Скопируйте значения из B1 в B5 в K100 до O100
4. Повторите шаги с 1 по 3 для всех рядов от 100 до 150

Answer 1

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

Я попытаюсь дать ответ, основываясь на том, что, как мне кажется, я понял.

В общих чертах, я думаю, вам просто нужно, чтобы формулы в рабочей книге 2 имели доступ к значениям в рабочей книге 1 и наоборот. Связывание рабочих книг представляется наиболее очевидным решением. На веб-сайте Microsoft есть некоторые уроки, которые показывают вам, как это сделать (http://office.microsoft.com/en-us/excel-help/create-and-manage-links-to-other-workbooks-HA001054812.aspx, http://office.microsoft.com/en-us/excel-help/create-a-link-to-another-cell-workbook-or-program-HP005199514.aspx).

Если вы считаете, что мой ответ слишком неправильный, опубликуйте пример, снимок экрана или видео, которые показывают, чего вы пытаетесь достичь.

Answer 2

Я согласен, вопрос немного расплывчат и имеет 4 шага в процессе. Какой шаг самый трудный? Можете ли вы сделать пример меньше и сосредоточиться только на той части, которой вы не можете управлять?

1.Скопируйте значения из A100 ...J100 в А1 до А10

Диапазоны не одного размера, что вы подразумеваете под копией? Если вы хотите определенное значение в A1, то просто поместите = A100 в ячейку A1. Вам не нужно "копировать" на самом деле, просто "ссылаться".

2.Подождите, пока результат появится в B1 до B5

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

3.Скопируйте значения из B1 в B5 в K100 и в O100. Опять же, если вам нужно значение в K100, то вы помещаете = B1 в ячейку K100, и она "скопирует" его (обратитесь к нему).

4.Повторите шаги с 1 по 3 для всех строк от 100 до 150. Когда вы настроите строку 100, у вас будет форумла в каждой ячейке. Теперь, когда вы выберете ячейку, в правом нижнем углу появится маленькая черная "ручка". Нажмите и перетащите его вниз. Формулы изменятся автоматически, чтобы ссылаться на ячейки либо абсолютным, либо относительным образом. Например, если у вас есть какие-то данные в ячейке A1, и вы хотите, чтобы все ячейки от A100 до A150 "увидели" их, вы можете поместить в ячейку A100: = $ A $ 1, тогда ячейка A101 также посмотрит на A1 и так далее. Таким образом, знак $ делает его абсолютной ссылкой.

Answer 3

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

Я создал несколько таких таблиц. Некоторые включали до 14 справочных таблиц и несколько десятков промежуточных вычислений и интерполяций; все, чтобы произвести единую таблицу чисел. Это основывалось на нескольких пользовательских входных данных, начиная от действительных чисел и заканчивая выпадающими списками с множеством проверок данных. Я использовал различные поиски, запросы index()/match(). И я не использовал никаких языков сценариев, так как мне не особенно нужны постоянные предупреждения, связанные с электронными таблицами с поддержкой макросов. Я создал несколько листов для промежуточных расчетов и поисков, все они были защищены паролями, и большинство из них были скрыты. Был лист ввода данных с незащищенными ячейками и лист результатов.

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

В конце концов, я считаю, что инвестировал более 120 часов и использовал это как инструмент только для себя. Чтобы сделать это, я добавил еще один лист с пустым "шаблоном", отформатированным для копирования и вставки в документ MS Word. Это работает хорошо в течение ряда лет. Когда некоторые данные в таблицах поиска нуждались в пересмотре, я просто вносил эти изменения, и цифры сразу же отражали эти изменения.

Бест, Даг

Answer 4

Вы можете сделать это с помощью анализа «что, если» в Excel. Я не достаточно знаком с этим, чтобы сказать. Вы можете сделать это с помощью относительно простого макроса.

В основе макрос будет работать так:

Open the formula workbook
Set calculation to manual
Loop thru the rows you want to apply the formula to
   Take inputs on current row of result sheet and enter them in the formula workbook
   Force Excel to recalculate
   Take results of formula workbook and enter them in the result sheet
End loop
Turn calculation back on to automatic.

Есть два способа перенести данные в строках в вашей книге результатов в книгу формул в столбцах. Одним из способов является копирование и вставка с использованием Transpose для преобразования строки в столбец, а другой - использование смещений, когда для одного диапазона вы смещаете столбец, а для другого диапазона вы смещаете строку.

Пример последнего заключается в следующем.

    ' set ranges to top left cell
    Set rngFrom = Workbooks("Result.xls").Worksheets("Sheet1").Cells(iCurrentRow, 1)
    Set rngTo = Workbooks("Formula.xls").Worksheets("Sheet1").Range("A1")

    ' transfer values
    For i = 0 To 9
        rngTo.Offset(RowOffset:=i).Value = rngFrom.Offset(ColumnOffset:=i).Value
    Next i

Если вам нужна конкретная помощь по написанию макроса VBA, сообщите нам.