Моя проблема заключается в следующем: у меня есть таблица из 3 столбцов и около 15000 строк данных. Я хотел бы найти формулу или набор формул, которые должны сравнивать пару первых двух столбцов и, если он находит совпадение с отклонением 3%, отображать значения 3-го столбца. Таким образом, в основном, если, скажем, он находит пару 'A52 и B52', которая не больше или ниже 3% другой пары, скажем, 'A3000 и B3000', для отображения двух значений, 'C52', соответственно, C3000. Поскольку значения в этих 15000 строках содержат много цифр, я вынужден использовать эти (+-)3%. Я надеюсь, что это решение моей проблемы.
2 ответа
1
Я понимаю, что вы пытаетесь сделать, это посмотреть на три столбца входных данных (время, температура, инсоляция) и сравнить с четвертым (производство). Вы хотите контролировать температуру и инсоляцию и определить, зависит ли производство от времени.
Это довольно сложное статистическое упражнение, особенно потому, что дисперсия выпуска от фотоэлектрических систем меняется очень медленно со временем - порядка 20% потерь производства за 20 лет, когда дисперсия из-за других переменных намного больше и будет иметь тенденцию чтобы скрыть эффект, который вы ищете.
Я думаю, вы понимаете, что это трудная задача, так как вы просили об упрощении анализа, рассматривая пары температуры и инсоляции, которые одинаковы в разные дни. Однако это приведет к сотням разных пар данных, каждая из которых имеет довольно небольшое количество повторений и столь низкую индивидуальную мощность для поиска корреляции со временем.
Вместо того, чтобы разбивать данные на несколько сотен небольших анализов, я бы предложил проанализировать весь набор данных, но попытаться выделить временную переменную, чтобы увидеть, влияет ли она на производство.
Как я уже говорил, для правильного выполнения этой задачи требуется сложная статистика, выходящая за рамки того, что у меня есть в повседневной практике. Но вот решение, которое вы можете попробовать, которое все же может сказать вам, что вам нужно, не будучи статистически строгим:
Предполагая следующие данные примера:
Time Temp Rad Production
hours *C W/m2 W
1 18 20 3194
2 20 30 3984
3 20 40 3976
4 16 20 3174
5 14 0 0
6 10 0 0
7 8 0 0
8 10 0 0
9 14 10 1964
10 16 20 3136
11 17 30 3888
12 18 40 3856
13 15 30 3824
14 13 20 3034
15 5 0 0
16 8 0 0
17 12 8 1478
18 25 15 2263
19 30 25 2942
20 30 35 3240
21 25 20 2712
22 20 10 1768
23 22 0 0
24 18 0 0
25 22 0 0
26 25 10 1619
27 26 20 2539
28 18 24 2943
29 12 26 3047
30 10 18 2427
Мы можем построить модель, которая пытается предсказать производство с учетом трех других переменных. Как только мы находим наилучшее соответствие, мы можем проверить, действительно ли время было важной переменной или нет, и с какой скоростью оно применяется.
В этом примере я собираюсь предположить, что следующее уравнение даст нам производство:
Production = A*(B*Temp^b)*(C*Rad^c)*(1+D*Time^d)
Эта модель предполагает, что вариации из-за температуры и инсоляции варьируются в зависимости от степени мощности, и время может привести к отрицательному наклону результата, так как D является небольшим отрицательным числом.
Пометьте некоторые клетки как A, B, b, C, c, D, d. Затем создайте новый столбец рядом с вашими производственными данными, чтобы рассчитать новые производственные данные из этой модели. Введите уравнение, ссылаясь на записанные данные и именованные ячейки, в зависимости от ситуации. Сделайте ссылки на именованные ячейки фиксированными, используя $ notation, затем перетащите / заполните вниз.
На данный момент модель выдаст ошибки, так как параметры равны нулю. Поэтому оберните уравнение в оператор iferror(__, 0).
Сделайте еще один столбец справа с именем Error, с формулой (Production-Model)^ 2, и заполните его. Это мера того, насколько далеко от нашей модели. Суммируйте, что значения столбцов где-нибудь - это будет большое число. В идеале это большое число станет маленьким позже, что указывает на то, что наше уравнение работает и предсказывает реальность.
Используйте Solver, чтобы изменить все переменные, минимизируя значение ячейки, которая является суммой ошибок.
На этом этапе, если вы строите график производства с течением времени, а также моделируемого производства с течением времени, оба должны дать хорошее соответствие.
Из значений параметров, найденных решателем, посмотрите на те, которые относятся ко времени (D и d). Если вы построите график зависимости временной части модели (y = 1+D * Time ^ d) от времени, вы увидите, какое влияние решающий фактор оказывает на ваше производство.
0
Предполагая, что ваша спецификация проблемы правильная - то есть вы хотите, чтобы результат (представляющий собой значение «Производство», когда температура находится в пределах ± 3% от радиации) отображался в столбце D.
Используйте эту формулу в столбце D и скопируйте ее в последний ряд.
=IF(AND(B2>A2*97/100, B2<A2*103/100), C2, "")