Создание «временных» формул массива в Excel

459
J Woltman

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

enter image description here

В моей голове это работает так: =MakeArray(2.23, 7)где 2.23 - это значение, а 7 - количество повторений. Тогда я мог бы назначить это для ряда и фактически не поддерживать другие серии данных, которые меня на самом деле не волнуют.

1
= REPT (2.23,7) может помочь вам, вы можете заменить значения на значения ячеек и т. Д. PeterH 6 лет назад 1
Кажется, что REPT возвращает только текстовую строку, а не массив. Мне не удалось заставить Excel отобразить значения, возвращаемые из = REPT (2.23,7) J Woltman 6 лет назад 0
Вы всегда можете написать udf, который возвращает массив. Scott Craner 6 лет назад 0
Так как я пишу UDF для других частей моей таблицы, это, вероятно, путь. J Woltman 6 лет назад 0
Но вы не можете ссылаться на UDF непосредственно на графике, по крайней мере, не так, как я обнаружил за последние 5 минут поиска. так что если вы нашли это на работе, пожалуйста, вернитесь и ответьте на свой вопрос, подробно описав, как вы это сделали. Scott Craner 6 лет назад 0
Зачем вам нужно отображать значение для каждого столбца, когда вы строите фиксированное значение? Просто создайте точку для первого и последнего столбца. Excel проведет линию между конечными точками. fixer1234 6 лет назад 0
@ fixer1234 Спасибо за идею, я буду экспериментировать с ней. J Woltman 6 лет назад 0
@ fixer1234 Я попробовал, и он работал отлично. Я не уверен в этике / нормах Суперпользователя. Должен ли я принять мой ответ, потому что он технически отвечает на мой первоначальный вопрос, или я должен вместо этого переписать его с вашим решением? J Woltman 6 лет назад 0
Другой вариант - добавить его к своему собственному ответу или опубликовать как второй ответ (поскольку это совершенно другой подход). То, что вы выбираете в качестве принятого ответа, полностью является вашей прерогативой как автора вопроса. Вы сами решаете, какой ответ вы считаете лучшим для своих нужд. Теперь у вас также достаточно представителей, чтобы вы могли проголосовать за любые ответы, которые вы считаете полезными, в дополнение к принятию одного ответа как «лучшего для вас» (который может быть вашим собственным, но вы не можете голосовать за свои собственные ответы). fixer1234 6 лет назад 0

3 ответа на вопрос

2
J Woltman

Отредактировано: следующее является решением вопроса, который я разместил, но, возможно, не самый лучший способ на самом деле предоставить «рекомендуемые» или «пороговые» строки, что и было моей реальной целью.

Технически правильный ответ

Я думаю, что я нашел приличное решение:

  1. Создайте пользовательскую функцию (UDF), которая возвращает массив, как предложено @ScottCraner.
  2. Определите именованный диапазон, который ссылается («вызывает») на эту функцию.
  3. Установите источник серии в этот именованный диапазон.

An example of the resultsи электронная таблица с поддержкой макросов находится на моем сайте по адресу https://woltman.com/m/filer_public/cc/ba/ccba65d2-e29c-4208-b3be-53ead02845ff/arraysforchartdataexample.xlsm.

Наверное, лучшее решение

Это решение следует предложению @ fixer1234:

  1. Сделайте пару точек данных XY. Значения Y - ваши рекомендуемые данные. Первое значение Х равно 1, второе значение Х равно числу баров, которые вы имеете на гистограмме.
  2. Создать нормальную гистограмму
  3. Добавьте еще одну серию на диаграмму и установите тип серии на XY с линией.
  4. Задайте в качестве исходных данных для серии пару XY, созданную на шаге 1.
  5. Excel переместит серию на вторичную ось. Поместите ряд обратно на основную ось.
  6. Удалите маркеры из серии XY, чтобы она выглядела как простая линия.

Это не требует никакого дополнительного кода вообще, и очень чисто. Sample using XY scatter with a line

хорошая работа. Пожалуйста, покажите UDF как текст, это поможет тем, кто в будущем. Многие не будут загружать `.xlsm` с открытого форума. Scott Craner 6 лет назад 0
0
Scott Craner

Поместите 2.23в верхнюю ячейку.

В следующем вниз положил =B2.

Предполагая, что 2.23было введено в B2, а формула в B3, просто скопируйте вниз.

enter image description here

Тогда, если вы хотите изменить номер, вам нужно только изменить B2, а остальное изменится

enter image description here

Использование остальных значений первой ячейки, безусловно, является хорошей идеей, но для нее все еще требуется куча столбцов данных. Я не уверен, что Excel может поддерживать то, что я хочу, поэтому я, вероятно, пойду с вашим решением. J Woltman 6 лет назад 0
0
Rajesh S

Я хотел бы предложить макрос (VBA), который повторяет любое значение: число, текст или даже дату до N раз.

enter image description here

Sub RepeatData()  Dim Rng As Range Dim InputRng As Range, OutRng As Range  xTitleId = "Repeat Data"  Set InputRng = Application.Selection Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8) Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8) Set OutRng = OutRng.Range("A1")  For Each Rng In InputRng.Rows xValue = Rng.Range("A1").Value xNum = Rng.Range("B1").Value OutRng.Resize(xNum, 1).Value = xValue Set OutRng = OutRng.Offset(xNum, 0) Next  End Sub

Как это устроено:

  • Вставьте этот макрос как модуль с соответствующим листом.
  • Запустите макрос и ответьте на первое поле ввода, выбрав A3: B3.
  • Ответьте на второе поле ввода, выбрав любую отдельную ячейку, например, E3.
  • Готово с ОК.

Вы получите желаемый результат, как показано на скриншоте.

Обратите внимание, я специально включил Ситуацию 2, чтобы показать, что макрос может повторять любое значение любое количество раз.

Похожие вопросы