Для дат с отсутствующими данными взять среднее значение из ближайших дат

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

Ниже приведен пример того, как выглядит таблица для столбцов A, B и C, а также строк от 1 (заголовок) до 7. Столбец B содержит реальные данные (обратите внимание, что я вставил ‘no value’ для иллюстрации). Столбец C-это то, что я хотел бы вычислить. Поскольку у меня есть тысячи строк данных, вручную вводить каждую из этих формул не является опцией.

Дата_______Фактическое Значение______Требуемый Расчет

1/1/2012_______0.15__________=B2

1/2/2012_ _ _ _ [нет значения]_______=среднее(B2,B5)

1/3/2012_ _ _ _ [нет значения]_______=среднее(B2,B5)

1/4/2012_______0.17__________=B5

1/5/2012_ _ _ _ [нет значения]_______=среднее(B5,B7)

1/6/2012_______0.23__________=B7

1 ответ

  1. =IF(B1<>"",B1,VLOOKUP(MAX($A
    	
    =IF(B1<>"",B1,VLOOKUP(MAX($A$1:$A$4),$A$1:$B$4,2,0))
    

    Misread the question the first time.

    Test to see if B1 is equal to "" or not. If it is empty then perform the VLOOKUP searching for the max value of column A (most recent date), and then use the value for the adjacent column (2).

    This will not cover the case where the most recent Date has no value in column B. I will return "" in that case.

    TREND Option

    Based on your comments and the use of some helper cells you could do the following. Make a small table off to the right somewhere of just your known dates and your known values from B. We can populate this table using the following formulas. Let arbitrarily put this table in columns F and G.

    in F2 use:

    =IFERROR(INDEX($A$2:$A$1564,AGGREGATE(15,6,(ROW($B$2:$B$1564)-ROW($A$2)+1)/($B$2:$B$1564<>""),ROWS(B$2:B2))),"")
    

    This will pull the dates in order that they appear with values in B that are not "". Then in G2 put the following formula in which will pull the associated value from B

    =IFERROR(INDEX($B$2:$B$1564,AGGREGATE(15,6,(ROW($B$2:$B$1564)-ROW($B$2)+1)/($B$2:$B$1564<>""),ROWS(B$2:B2))),"")
    

    Both those formulas are currently set up to work with a header row and data starting in row 2.

    once we have the helper table set up, you can set up a TREND function, which is a line of best fit through all your known points. You can pick the point on this line for dates with no value in B. In C2 you would add:

    =IF(B2="",TREND($G$2:$G$3,$F$2:$F$3,A2),B2)

    you would copy this formula down. It checks to see if B is blank. If it is then it pulls a value for B from the trend line. If it is not blank, then it will use the value in B.

    AVERAGE Option

    Again using the helper table in the same spot. This one gets a little uglier. In C2 place the following and copy down

    =IF(B2<>"",B2,IF($A2<$F$2,$G$2,IF($A2>$F$3,$G$3,(INDEX($G$2:$G$3,IFERROR(MATCH($A2,$F$2:$F$3,1),1))+INDEX($G$2:$G$3,IFERROR(MATCH($A2,$F$2:$F$3,1)+1,2)))/2)))
    

    The first thing it does is check for a blank value in B. If there is no blank then it uses the value in B2. If there is no value in B2 it then proceeds to check the date of the blank against the first date in your helper table. If it is less than that first value, it uses the first value of B since there is nothing to average it with. I then proceeds to do the same to see if the date exceeds the last value in the table. (this is currently hard coded). If it exceeds the last date in the table it uses the last value for B. the last option for the date is for it to be in the table. It take the average between the date prior to the date and question and the next date with a value.

    Optional version where you dont have to hard code the last values of helper table, but you still need to hard code the range of the helper table to meet your needs.

    =IF(B3<>"",B3,IF($A3<$F$2,$G$2,IF($A3>MAX($F$2:$F$3),VLOOKUP(MAX($F$2:$F$3),$F$2:$G$3,2,0),(INDEX($G$2:$G$3,IFERROR(MATCH($A3,$F$2:$F$3,1),1))+INDEX($G$2:$G$3,IFERROR(MATCH($A3,$F$2:$F$3,1)+1,2)))/2)))
    

    TREND Between only two points

    So I adjusted the formula a bit on this one. Its kind of a mix of the trend and previous options listed above. I also made it automatically selecting the end values of your table, so now you just have to copy your table down as far as you need. Its also works on the premise that only your data value in B are present, no numbers below your last row.

    =IF($A2<$F$2,$G$2,IF($A2>=MAX(OFFSET($F$2,0,0,COUNT(B:B),1)),VLOOKUP(MAX(OFFSET($F$2,0,0,COUNT(B:B),1)),OFFSET($F$2,0,0,COUNT(B:B),2),2,0),TREND(OFFSET($F$2,IFERROR(MATCH($A2,OFFSET($F$2,0,0,COUNT(B:B),1),1),0)-1,1,2,1),OFFSET($F$2,IFERROR(MATCH($A2,OFFSET($F$2,0,0,COUNT(B:B),1),1)-1,0),0,2,1),$A2)))
    

    So basically TREND function is a line of best fit through known points. if your know points do not all sit in a straight line it figures out what straight line passes near them minimizing the distance they are from the line. If we limit the points it using to make its line to only 2 points, then the line has to pass through those two points. Remember, the helper table is a list of all known points.

    So basically what the formula doing is checking to see if the date is less than or equal to the first known point on the helper table. If it is, use the value from the first known point in the table. If its not, check to see if the date is greater than the last date in the table, if it is lookup the last known date in the table and return the corresponding table. If neither of these cases is true then the date must be in the table. That means we can get two points from the table and their corresponding values to use in the trend function. We feed the TREND function a an array of 2 known X and 2 Know Y values, and then supply it with an X value we are interested in. In this case X would be the dates, and Y would be the values in the second column.

    Revised Helper table

    In F2 use this formula:

    =IFERROR(INDEX(OFFSET($A$2,0,0,COUNT(A:A),1),AGGREGATE(15,6,(ROW(OFFSET($A$2,0,1,COUNT(A:A),1))-ROW($A$2)+1)/(OFFSET($A$2,0,1,COUNT(A:A),1)<>""),ROWS(B$2:B2))),"")
    

    and G2 use this formula:

    =IF(F2="","",INDEX(OFFSET($A$2,0,1,COUNT(A:A),1),MATCH(F2,OFFSET($A$2,0,0,COUNT(A:A),1),0)))
    

    and if you want to verify how far down you want to copy your helper table, you can put this in lets say I3:

    =COUNT(B:B)+1
    

    Now these update that are using the count(A:A) or B:B require that no other numbers be above or below your data in the A or B column.

    UPDATED Average

    I also tweaked the average formula to give this option as well.

    =IF($B2<>"",$B2,IF($A2<=$F$2,$G$2,IF($A2>=MAX(OFFSET($F$2,0,0,COUNT($B:$B),1)),VLOOKUP(MAX(OFFSET($F$2,0,0,COUNT($B:$B),1)),OFFSET($F$2,0,0,COUNT($B:$B),2),2,0),AVERAGE(OFFSET($F$2,IFERROR(MATCH($A2,OFFSET($F$2,0,0,COUNT($B:$B),1),1),0)-1,1,2,1)))))
    

    Example results (note rows 14-69 have been hidden so you can see what is happening around row 75.

    Example results

    :$A),$A

    =IF(B1<>"",B1,VLOOKUP(MAX($A$1:$A$4),$A$1:$B$4,2,0))
    

    Misread the question the first time.

    Test to see if B1 is equal to "" or not. If it is empty then perform the VLOOKUP searching for the max value of column A (most recent date), and then use the value for the adjacent column (2).

    This will not cover the case where the most recent Date has no value in column B. I will return "" in that case.

    TREND Option

    Based on your comments and the use of some helper cells you could do the following. Make a small table off to the right somewhere of just your known dates and your known values from B. We can populate this table using the following formulas. Let arbitrarily put this table in columns F and G.

    in F2 use:

    =IFERROR(INDEX($A$2:$A$1564,AGGREGATE(15,6,(ROW($B$2:$B$1564)-ROW($A$2)+1)/($B$2:$B$1564<>""),ROWS(B$2:B2))),"")
    

    This will pull the dates in order that they appear with values in B that are not "". Then in G2 put the following formula in which will pull the associated value from B

    =IFERROR(INDEX($B$2:$B$1564,AGGREGATE(15,6,(ROW($B$2:$B$1564)-ROW($B$2)+1)/($B$2:$B$1564<>""),ROWS(B$2:B2))),"")
    

    Both those formulas are currently set up to work with a header row and data starting in row 2.

    once we have the helper table set up, you can set up a TREND function, which is a line of best fit through all your known points. You can pick the point on this line for dates with no value in B. In C2 you would add:

    =IF(B2="",TREND($G$2:$G$3,$F$2:$F$3,A2),B2)

    you would copy this formula down. It checks to see if B is blank. If it is then it pulls a value for B from the trend line. If it is not blank, then it will use the value in B.

    AVERAGE Option

    Again using the helper table in the same spot. This one gets a little uglier. In C2 place the following and copy down

    =IF(B2<>"",B2,IF($A2<$F$2,$G$2,IF($A2>$F$3,$G$3,(INDEX($G$2:$G$3,IFERROR(MATCH($A2,$F$2:$F$3,1),1))+INDEX($G$2:$G$3,IFERROR(MATCH($A2,$F$2:$F$3,1)+1,2)))/2)))
    

    The first thing it does is check for a blank value in B. If there is no blank then it uses the value in B2. If there is no value in B2 it then proceeds to check the date of the blank against the first date in your helper table. If it is less than that first value, it uses the first value of B since there is nothing to average it with. I then proceeds to do the same to see if the date exceeds the last value in the table. (this is currently hard coded). If it exceeds the last date in the table it uses the last value for B. the last option for the date is for it to be in the table. It take the average between the date prior to the date and question and the next date with a value.

    Optional version where you dont have to hard code the last values of helper table, but you still need to hard code the range of the helper table to meet your needs.

    =IF(B3<>"",B3,IF($A3<$F$2,$G$2,IF($A3>MAX($F$2:$F$3),VLOOKUP(MAX($F$2:$F$3),$F$2:$G$3,2,0),(INDEX($G$2:$G$3,IFERROR(MATCH($A3,$F$2:$F$3,1),1))+INDEX($G$2:$G$3,IFERROR(MATCH($A3,$F$2:$F$3,1)+1,2)))/2)))
    

    TREND Between only two points

    So I adjusted the formula a bit on this one. Its kind of a mix of the trend and previous options listed above. I also made it automatically selecting the end values of your table, so now you just have to copy your table down as far as you need. Its also works on the premise that only your data value in B are present, no numbers below your last row.

    =IF($A2<$F$2,$G$2,IF($A2>=MAX(OFFSET($F$2,0,0,COUNT(B:B),1)),VLOOKUP(MAX(OFFSET($F$2,0,0,COUNT(B:B),1)),OFFSET($F$2,0,0,COUNT(B:B),2),2,0),TREND(OFFSET($F$2,IFERROR(MATCH($A2,OFFSET($F$2,0,0,COUNT(B:B),1),1),0)-1,1,2,1),OFFSET($F$2,IFERROR(MATCH($A2,OFFSET($F$2,0,0,COUNT(B:B),1),1)-1,0),0,2,1),$A2)))
    

    So basically TREND function is a line of best fit through known points. if your know points do not all sit in a straight line it figures out what straight line passes near them minimizing the distance they are from the line. If we limit the points it using to make its line to only 2 points, then the line has to pass through those two points. Remember, the helper table is a list of all known points.

    So basically what the formula doing is checking to see if the date is less than or equal to the first known point on the helper table. If it is, use the value from the first known point in the table. If its not, check to see if the date is greater than the last date in the table, if it is lookup the last known date in the table and return the corresponding table. If neither of these cases is true then the date must be in the table. That means we can get two points from the table and their corresponding values to use in the trend function. We feed the TREND function a an array of 2 known X and 2 Know Y values, and then supply it with an X value we are interested in. In this case X would be the dates, and Y would be the values in the second column.

    Revised Helper table

    In F2 use this formula:

    =IFERROR(INDEX(OFFSET($A$2,0,0,COUNT(A:A),1),AGGREGATE(15,6,(ROW(OFFSET($A$2,0,1,COUNT(A:A),1))-ROW($A$2)+1)/(OFFSET($A$2,0,1,COUNT(A:A),1)<>""),ROWS(B$2:B2))),"")
    

    and G2 use this formula:

    =IF(F2="","",INDEX(OFFSET($A$2,0,1,COUNT(A:A),1),MATCH(F2,OFFSET($A$2,0,0,COUNT(A:A),1),0)))
    

    and if you want to verify how far down you want to copy your helper table, you can put this in lets say I3:

    =COUNT(B:B)+1
    

    Now these update that are using the count(A:A) or B:B require that no other numbers be above or below your data in the A or B column.

    UPDATED Average

    I also tweaked the average formula to give this option as well.

    =IF($B2<>"",$B2,IF($A2<=$F$2,$G$2,IF($A2>=MAX(OFFSET($F$2,0,0,COUNT($B:$B),1)),VLOOKUP(MAX(OFFSET($F$2,0,0,COUNT($B:$B),1)),OFFSET($F$2,0,0,COUNT($B:$B),2),2,0),AVERAGE(OFFSET($F$2,IFERROR(MATCH($A2,OFFSET($F$2,0,0,COUNT($B:$B),1),1),0)-1,1,2,1)))))
    

    Example results (note rows 14-69 have been hidden so you can see what is happening around row 75.

    Example results

    :$B,2,0))

    Неправильно истолковал вопрос в первый раз.

    Проверьте, равен ли B1 «» или нет. Если он пуст, то выполните VLOOKUP поиск максимального значения столбца A (самая последняя дата), а затем используйте значение для соседнего столбца (2).

    Это не будет охватывать случай, когда самая последняя дата не имеет значения в столбце B. Я верну «» в этом случае.

    Вариант тренда

    Основываясь на ваших комментариях и использовании некоторых вспомогательных ячеек, вы можете сделать следующее. Сделайте небольшую таблицу справа где — то только ваших известных дат и известных значений из B. Мы можем заполнить эту таблицу с помощью следующих формул. Пусть произвольно поместить эту таблицу в столбцы F и G.

    в пользе F2:

    =IFERROR(INDEX($A:$A64,AGGREGATE(15,6,(ROW($B:$B64)-ROW($A)+1)/($B:$B64<>""),ROWS(B:B2))),"")
    

    Это вытянет даты, чтобы они появились со значениями в B, которые не являются «». Затем в G2 положите следующую формулу, в которой будет вытягивать связанное значение из B

    =IFERROR(INDEX($B:$B64,AGGREGATE(15,6,(ROW($B:$B64)-ROW($B)+1)/($B:$B64<>""),ROWS(B:B2))),"")
    

    Обе эти формулы в настоящее время настроены для работы со строкой заголовка и данными, начинающимися в строке 2.

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

    =IF (B2=»», TREND ($G:$G,$F:$F,A2), B2)

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

    Средний вариант

    Снова используя вспомогательную таблицу в том же месте. Этот становится немного уродливее. В C2 поместите следующее И скопируйте вниз

    =IF(B2<>"",B2,IF($A2<$F,$G,IF($A2>$F,$G,(INDEX($G:$G,IFERROR(MATCH($A2,$F:$F,1),1))+INDEX($G:$G,IFERROR(MATCH($A2,$F:$F,1)+1,2)))/2)))
    

    Первое, что он делает, это проверяет наличие пустого значения в B. Если нет пустого, то он использует значение в B2. Если в B2 нет значения, то выполняется проверка даты пустого поля на соответствие первой дате во вспомогательной таблице. Если это меньше, чем первое значение, он использует первое значение B, так как нет ничего, чтобы усреднить его. Затем я делаю то же самое, чтобы увидеть, превышает ли дата последнее значение в таблице. (в настоящее время это жестко закодировано). Если он превышает последнюю дату в таблице, он использует последнее значение для B. последний параметр для даты должен быть в таблице. Он принимает среднее между датой до даты и вопроса и следующей датой со значением.

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

    =IF(B3<>"",B3,IF($A3<$F,$G,IF($A3>MAX($F:$F),VLOOKUP(MAX($F:$F),$F:$G,2,0),(INDEX($G:$G,IFERROR(MATCH($A3,$F:$F,1),1))+INDEX($G:$G,IFERROR(MATCH($A3,$F:$F,1)+1,2)))/2)))
    

    Тренд только между двумя точками

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

    =IF($A2<$F,$G,IF($A2>=MAX(OFFSET($F,0,0,COUNT(B:B),1)),VLOOKUP(MAX(OFFSET($F,0,0,COUNT(B:B),1)),OFFSET($F,0,0,COUNT(B:B),2),2,0),TREND(OFFSET($F,IFERROR(MATCH($A2,OFFSET($F,0,0,COUNT(B:B),1),1),0)-1,1,2,1),OFFSET($F,IFERROR(MATCH($A2,OFFSET($F,0,0,COUNT(B:B),1),1)-1,0),0,2,1),$A2)))
    

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

    Поэтому в основном формула проверяет, является ли дата меньше или равна первой известной точке во вспомогательной таблице. Если это так, используйте значение из первой известной точки в таблице. Если его нет, проверьте, чтобы увидеть, если дата больше, чем последняя дата в таблице, если это lookup последняя известная дата в таблице и возвращает соответствующую таблицу. Если ни один из этих случаев не является истинным, то дата должна быть в таблице. Это означает, что мы можем получить две точки из таблицы и соответствующие им значения для использования в функции тренда. Мы подаем функцию тренда массив из 2 известных значений X и 2 Know Y, а затем предоставляем ему значение X, которое нас интересует. В этом случае X будет датами, а Y-значениями во втором столбце.

    Пересмотренная вспомогательная таблица

    В F2 используйте эту формулу:

    =IFERROR(INDEX(OFFSET($A,0,0,COUNT(A:A),1),AGGREGATE(15,6,(ROW(OFFSET($A,0,1,COUNT(A:A),1))-ROW($A)+1)/(OFFSET($A,0,1,COUNT(A:A),1)<>""),ROWS(B:B2))),"")
    

    и G2 использовать эту формулу:

    =IF(F2="","",INDEX(OFFSET($A,0,1,COUNT(A:A),1),MATCH(F2,OFFSET($A,0,0,COUNT(A:A),1),0)))
    

    и если вы хотите проверить, как далеко вниз вы хотите скопировать вспомогательную таблицу, вы можете поместить это в lets say I3:

    =COUNT(B:B)+1
    

    Теперь эти обновления, которые используют count(A:A) или B:B требуют, чтобы никакие другие номера не были выше или ниже ваших данных в столбце A или B.

    Обновлено среднее

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

    =IF($B2<>"",$B2,IF($A2<=$F,$G,IF($A2>=MAX(OFFSET($F,0,0,COUNT($B:$B),1)),VLOOKUP(MAX(OFFSET($F,0,0,COUNT($B:$B),1)),OFFSET($F,0,0,COUNT($B:$B),2),2,0),AVERAGE(OFFSET($F,IFERROR(MATCH($A2,OFFSET($F,0,0,COUNT($B:$B),1),1),0)-1,1,2,1)))))
    

    Пример результаты (Примечание строки 14-69 были скрыты, так что вы можете увидеть, что происходит вокруг строки 75.

    Пример результатов