Excel에서 데이터 점 보간

Excel에서 데이터 점 보간

저는 이것이 다른 사람들이 이전에 여러 번 해결했던 문제라고 확신합니다.

일단의 사람들이 측정을 수행할 것입니다(정확히는 가정용 에너지 사용).그들 모두는 다른 시간과 다른 간격으로 그렇게 할 것입니다.

각 사용자로부터 얻을 수 있는 것은 집합에 누락된 날짜가 있는 {date, value} 쌍입니다.

필요한 것은 범위 내의 각 날짜에 대해 값이 알려진(측정 또는 계산됨) 완전한 {date, value} 쌍 세트입니다.저는 이 프로젝트에 간단한 선형 보간으로 충분할 것으로 예상합니다.

엑셀로 해야 한다고 생각한다면요.이러한 데이터 세트에서 보간하는 가장 좋은 방법은 무엇입니까(그래서 매일 값을 가지고 있습니다)?

감사해요.

참고: 이러한 데이터셋이 완료되면 기울기(즉, 하루 사용량)를 결정하고 이를 바탕으로 가정과 가정 간 비교를 시작할 수 있습니다.

추가 정보 처음 몇 가지 제안 후:측정 세트의 구멍 위치를 수동으로 확인하고 싶지 않습니다(불완전한 측정 세트가 너무 많습니다!!).저는 저를 위해 자동적인 것을 찾고 있습니다.그래서 만약 내 입력이

{2009-06-01,  10}
{2009-06-03,  20}
{2009-06-06, 110}

그러면 자동적으로 다음과 같은 결과가 나올 것으로 예상됩니다.

{2009-06-01,  10}
{2009-06-02,  15}
{2009-06-03,  20}
{2009-06-04,  50}
{2009-06-05,  80}
{2009-06-06, 110}

네, 저는 이것을 하는 소프트웨어를 쓸 수 있습니다.저는 단지 누군가가 이미 이 (일반적인) 문제에 대해 "실행 준비가 된" 소프트웨어(Excel) 기능을 가지고 있기를 바랄 뿐입니다.

저는 이것을 우연히 발견했고 애드인을 설치하지 않은 사람들과 시트를 공유하는 것이 어렵기 때문에 애드인 사용을 꺼렸습니다.

제 사무실 동료는 비교적 컴팩트한(마법을 조금 사용하는 대신) 깨끗한 공식을 디자인했습니다.

주의할 사항:

• 공식은 다음과 같이 작동합니다.

  • 사용MATCH에서 을 찾는 inputs은 3 값)입니다. 3은 3.5 직전 값입니다.
  • 용사를 OFFSET의 제곱을 합니다.
  • 용사를 FORECAST 그 입니다.

• 이 공식은 외삽법을 수행할 수 없습니다. 검색 값이 끝점 사이에 있는지 확인하십시오(아래 예제에서는 극단값을 사용하여 이 작업을 수행합니다).

이것이 사람들에게 너무 복잡한 것인지는 잘 모르겠지만, 휴대성이 뛰어나다는 장점이 있습니다(많은 대체 솔루션보다 더 간단합니다).

수식을 복사하여 붙여넣으려면 다음과 같이 하십시오.

=FORECAST(F3,OFFSET(inputs,MATCH(F3,inputs)-1,1,2,1),OFFSET(inputs,MATCH(F3,inputs)-1,0,2,1

(inputs명명된 범위임)

LINEST와 TREND라는 두 가지 기능을 사용하여 어떤 기능이 더 나은 결과를 제공하는지 확인할 수 있습니다.둘 다 알려진 Xs 및 Ys 집합과 새 X 값을 사용하여 새 Y 값을 계산합니다.차이점은 LINEST가 간단한 선형 회귀 분석을 수행하는 반면, TREND는 회귀 분석을 수행하기 전에 데이터에 적합한 곡선을 먼저 찾습니다.

가장 쉬운 방법은 다음과 같습니다.

1. 여기에서 Excel 추가 기능 다운로드: Microsoft Excel용 XlXtrFun™ 추가 기능

2. 함수 interpolate()를 사용합니다.= 인증서($A$1:$A$3,$B$1:$B$3,D1,FALSE,FALSE)

A열과 B열에는 입력 내용이 포함되어야 하며 G열에는 모든 날짜 값이 포함되어야 합니다.공식이 E 열에 들어갑니다.

보간된 결과가 얼마나 적합한지 확인할 수 있는 좋은 그래픽 방법:

날짜, 값 쌍을 가져와서 Excel의 XY 차트(라인 차트가 아님)를 사용하여 그래프로 표시합니다.그래프에서 결과 라인을 마우스 오른쪽 버튼으로 클릭하고 '트렌드 라인 추가'를 클릭합니다.사용할 원곡선 적합 유형을 선택할 수 있는 다양한 옵션이 있습니다.그런 다음 새로 생성된 추세선의 속성으로 이동하여 방정식과 R 제곱 값을 표시할 수 있습니다.

추세선 방정식 레이블을 포맷할 때 방정식 상수의 모든 유효 자릿수가 표시되도록 높은 정밀도의 숫자 형식을 설정해야 합니다.

YGA의 위 답변은 원하는 X 값이 기준 범위의 X 값과 동일한 범위 끝의 경우를 처리하지 않습니다.YGA에서 제공한 예를 사용하여 9999에서 보간된 값을 요청할 경우 Excel 공식은 #DIV/0! 오류를 반환합니다.이것은 YGA가 입력 데이터 범위에 9999와 -99999의 극단적인 끝점을 추가한 다음 모든 예측 값이 이 두 숫자 사이에 있다고 가정하는 이유의 일부입니다.이러한 패딩이 원하지 않거나 불가능한 경우 #DIV/0! 오류를 방지하는 또 다른 방법은 다음 공식을 사용하여 정확한 입력 값이 일치하는지 확인하는 것입니다.

=IF(ISNA(MATCH(F3,inputs,0)),FORECAST(F3,OFFSET(inputs,MATCH(F3,inputs)-1,1,2,1),OFFSET(inputs,MATCH(F3,inputs)-1,0,2,1)),OFFSET(inputs,MATCH(F3,inputs)-1,1,1,1))

여기서 F3는 보간된 결과가 필요한 값입니다.

참고: 저는 이것을 원래 YGA 게시물에 댓글로 추가했을 것이지만, 저는 아직 평판 포인트가 충분하지 않습니다.

대안으로

=INDEX(yVals,MATCH(J7,xVals,1))+(J7-MATCH(J7,xVals,1))*(INDEX(yVals,MATCH(J7,xVals,1)+1)-INDEX(yVals,MATCH(J7,xVals,1)))/(INDEX(xVals,MATCH(J7,xVals,1)+1)-MATCH(J7,xVals,1))

어디에j7는 x 값입니다.

xvalsx 값의 범위입니다.yvalsy 값의 범위입니다.

코드화하기가 더 쉽습니다.

Excel의 "추세선" 기능을 사용하여 데이터에 가장 적합한 공식을 찾을 수 있습니다.이 공식을 사용하여 계산할 수 있습니다.y어떤 경우에도x

1. 이에 대한 선형 산란(XY) 생성(삽입 => 산란);
2. 다항식 또는 이동 평균 추세선을 생성하고 "차트에 방정식 표시"를 선택합니다(시리즈 => 추세선 추가에서 마우스 오른쪽 버튼 클릭);
3. 방정식을 셀에 복사하고 교체합니다.x원하는 x 값을 가진 s

A12 아래 스크린샷:A16 홀드xs, B12:B16 홀드ys, 그리고 C12는 다음을 계산하는 공식을 포함합니다.y어떤 경우에도x.

처음에는 여기에 답변을 올렸지만 나중에 이 질문을 찾았습니다.

언급URL : https://stackoverflow.com/questions/1043513/interpolating-data-points-in-excel