Excel에서 임의 데이터의 중간 값을 보간하는 방법

Question 1

짧은 답변

보간은 X 및 Y 값을 연결하는 방정식을 기반으로 합니다. 실제 방정식을 알면 원하는 중간값을 직접 계산할 수 있습니다. 그렇지 않은 경우 근사치를 사용하여 보간합니다. 근사치의 품질에 따라 중간 값이 얼마나 정확한지 결정됩니다. 제한된 수의 점을 사용하여 곡선을 근사화하는 경우 선형 보간법은 조악합니다. 더 나은 결과를 제공하는 몇 가지 다른 접근 방식과 대부분의 작업을 수행하는 내장 분석 도구가 있습니다.

긴 답변

중간 값 보간을 자동화하는 "일반 공식" 또는 솔루션을 찾고 있습니다. 거의 모든 데이터에 대해 선형 보간법을 사용할 수 있지만, 데이터 포인트 수가 제한되어 있고 데이터 모양에 상당한 곡률이 있는 경우 결과가 조잡할 것입니다. 정확성을 원한다면 "모든 것에 맞는 단일" 솔루션은 없습니다. 주어진 데이터세트에 대한 최상의 솔루션은 데이터의 특성에 따라 달라집니다.

방정식

어떻게 하든 보간은 X와 Y 사이의 관계를 정의하는 방정식을 사용하여 수행됩니다. 방정식은 실제 방정식이거나 추정치입니다. 추정치인 경우 데이터의 특성과 달성해야 하는 작업에 따라 다양한 접근 방식이 있습니다.

다른 질문에서는 방정식을 기반으로 데이터를 사용했습니다 Y=2^X. 실제 방정식이 있으면 정확하게 보간할 수 있습니다. X또는 중 하나에 대해 새 값을 선택하면 Y방정식이 다른 값을 제공합니다. 실제 방정식을 모른다면 그에 가까운 방정식을 찾아야 합니다. 이 답변을 사용하여 보간 접근 방식에 중점을 둘 것입니다. 이들은 일반적으로 대부분의 작업을 수행하는 내장 분석 도구를 사용합니다. 특정 도구를 사용하는 메커니즘이나 보다 자동화된 접근 방식에 대한 자세한 내용이 필요한 경우 다른 답변에서 이에 대해 확장할 수 있습니다.

실제 방정식을 찾아보세요

가장 좋은 해결책은 실제 방정식이 무엇인지 확인할 수 있는지 확인하는 것입니다. 데이터를 생성한 프로세스를 알고 있다면 방정식의 성격을 알 수 있습니다. 제어된 조건에서 단일 구동 변수를 처리하고 무작위 노이즈가 없는 많은 프로세스는 방정식 유형이 알려진 간단한 곡선을 따릅니다. 따라서 첫 번째 단계는 데이터의 모양을 살펴보고 그 중 하나와 유사한지 확인하는 것입니다.

이를 수행하는 쉬운 방법은 데이터를 그래프로 표시하고 추세선을 추가하는 것입니다. Excel에는 피팅할 수 있는 여러 가지 공통 곡선이 있습니다.

2^N다른 질문의 데이터 로 이것을 시도해 보겠습니다 . 숫자 패턴을 인식하지 못하고 추세선 접근 방식을 시도한 경우 다양한 모양의 곡선 아이콘이 표시됩니다. 지수 곡선은 일반적인 모양과 동일하며 다음을 제공합니다.

Excel은 단지 번역인 기본이 e아닌 사용합니다(e ^0.693 is ). 시각적으로 추세선이 데이터를 정확히 따르는 것을 볼 수 있습니다. R ² 도 이를 알려줍니다. R ^2는 방정식을 사용하여 설명하는 데이터의 변동 정도를 나타내는 통계적 척도입니다. 값은 방정식이 변동의 100%를 설명하거나 완벽하게 맞는 것을 의미합니다.221

이 질문의 예도 일종의 지수 형태를 가지고 있습니다. 동일한 접근 방식을 시도하면 다음과 같은 결과가 나타납니다.

따라서 이 데이터는 지수적이지 않습니다. 몇 가지 자연적인 과정을 설명하고 다양한 곡선을 모방할 수 있는 다항식을 시도해 볼 수 있습니다(나중에 자세히 설명하겠습니다).

데이터 이면의 프로세스에 대한 근사치로는 적합하지 않습니다. 세 번째 순서(X의 거듭제곱을 X^3까지 포함하는 방정식)에서는 데이터보다 주요 변곡점이 더 많고 여전히 일치하지 않습니다. 따라서 기본 방정식은 단순하고 일반적인 곡선처럼 보이지 않습니다. 이는 방정식을 근사화해야 함을 의미합니다.

선형 보간

이것이 귀하의 의견에 설명된 접근 방식입니다. 간단한 공식을 사용하여 간단하며 자동화하기가 매우 쉽습니다. 점이 많고 그 사이의 직선이 충분히 가깝다면 적절할 수 있습니다. 많은 곡선에서 일부 영역의 짧은 부분은 직선에 가깝습니다. 그러나 곡선에 대한 근사치는 좋지 않으며 곡률이 큰 영역에서는 결과가 부정확해집니다. 귀하의 예에서 X 값 7과 8 사이의 영역에는 곡률이 많이 있습니다. 이 영역에서 실제 곡선과 비교한 직선은 다음과 같습니다.

모든 데이터에 적용할 수 있는 일반적인 솔루션을 찾고 있습니다. 일부 데이터에서는 선형 보간법이 너무 조잡하다는 것을 알 수 있습니다.

회귀

사람들은 여기와 다른 게시물에서 회귀를 접근 방식으로 제안했습니다. 추세선이나 기본 워크시트 기능 또는 분석 도구를 사용하여 수행할 수 있습니다(해당 옵션을 Excel에 로드해야 하는 분석 도구 키트에 있을 수 있지만 기본적으로 로드되지 않을 수 있음).

회귀 분석에서는 데이터와 곡선 사이의 전체 오류를 최소화하려는 목적으로 데이터에 곡선을 맞추려고 합니다. 일반적인 사용법에서는 이 작업에 적합한 도구가 아닙니다(추세선을 맞추는 데 사용되는 방법이며 필요한 것과 비교하여 그 방법을 확인했습니다).

이는 데이터 이면의 프로세스를 모델링하는 것이 목표인 상황을 위한 것입니다. 데이터는 부정확한 것으로 가정되며, 회귀 분석을 통해 실제로 예상되는 데이터가 무엇인지 알 수 있습니다. 회귀 분석으로 찾은 곡선은 실제 데이터 포인트를 통과하지 못할 수도 있습니다. 귀하의 경우 데이터가 제공되고 정확한 것으로 간주됩니다. 곡선은 모든 점을 통과해야 합니다.
회귀 분석에서는 단일 방정식을 모든 데이터에 맞추려고 합니다. 데이터를 생성한 프로세스가 시도할 수 있는 방정식 유형으로 설명되지 않으면 효과적이지 않습니다. 데이터 포인트가 많으면 각 세그먼트의 선형 보간이 모든 데이터에 대한 회귀 곡선보다 더 나은 근사치가 될 수 있습니다.

그러나 일반적인 방법으로 사용하는 대신 회귀는 원하는 것에 대한 해결 방법으로 "남용"될 수 있으며 일반적으로 작동합니다. 프로세스를 모델링하려고 할 때 일반적으로 가장 간단한 공식(Occam의 면도칼)이 중요합니다. 반면에 충분히 복잡한 방정식을 사용하면 무엇이든 맞출 수 있습니다. 언제든지 모든 지점을 통과하는 낙서를 그릴 수 있습니다. 점을 사용하면 모든 점을 통과하는 차수 다항식을 N찾을 수 있습니다 (최악의 시나리오).N-1

제가 "보통"이라고 말한 이유는 어떤 경우에는 귀하의 목적에 쓸모가 없을 만큼 매우 힘든 라인이기 때문입니다. 그리고 이 접근 방식은 결과 방정식이 데이터 범위 밖의 동작을 예측한다는 점에서 실제로 아무것도 "모델링"하지 않습니다.

다음은 연속적으로 고차 방정식을 사용한 다항식 회귀를 사용하여 데이터를 분석한 것입니다(첫 번째 스크린샷에는 차수 3 - 5가 포함되어 있습니다).

(읽을 수 있는 크기를 보려면 이미지를 클릭하십시오.) 분석 도구에는 수행하려는 보간 유형이 포함되어 있습니다. 중간 값을 생성했습니다. 각 분석에서 a(n)값은 찾은 방정식의 계수입니다. a(0)는 상수, a(1)X^1 항의 계수 등입니다. 이는 피팅의 R ²1 값을 표시합니다. 귀하의 목적에 맞도록 가상으로 가까이 있어야 합니다 .

가장 큰 차이가 있는 원본 데이터 값을 강조했습니다. 이 순서 범위에서는 연속된 순서마다 적합성이 조금씩 좋아지지만 어떤 특정 지점이 더 정확하게 설명되는지는 변경될 수 있습니다. 이 세 가지에 대한 차트는 다음과 같습니다.

6차 및 7차 다항식에 도달하면 다음과 같습니다.

9개의 값에 대해 8차 다항식을 사용하면 완벽할 것이지만 7차 다항식은 아마도 충분히 가깝습니다. 관점에서 보면 7차 방정식의 R ² 는 .99999이지만 여전히 완벽하지는 않습니다.

적절한 적합도(이 경우 7차 또는 8차 방정식)를 찾기 위해 회귀 분석 도구를 사용하면 원하는 중간 값이 생성됩니다. 그러나 결과를 차트로 작성하고 곡선이 낙서가 아닌지 확인하는 것이 좋습니다.

스플라인

데이터를 차트로 표시하고 매끄러운 선 옵션을 선택한 경우 Excel에서 생성하는 데 사용되는 것은 스플라인입니다. 실제로 컴퓨터 그래픽의 거의 모든 응용 프로그램(글꼴 정의 포함)은 부드러운 곡선과 곡선 전환을 위한 스플라인을 기반으로 합니다. 이 이름은 설계자가 한때 임의의 점을 곡선과 연결하는 데 사용했던 유연한 규칙의 이름을 따서 명명되었습니다.

스플라인은 인접한 점을 고려하여 한 번에 한 섹션씩 각 섹션에 대한 곡선을 생성합니다. 곡선은 각 점을 통과하며 점을 직선으로 연결할 때처럼 점 양쪽에 급격한 변화가 없습니다.

스플라인에 사용되는 방정식은 데이터를 생성하는 프로세스를 모델링하려고 시도하지 않습니다. 그것은 엄격하게 예뻐 보이기 위한 것입니다. 그러나 대부분의 프로세스는 일종의 연속적이고 부드러운 곡선을 따릅니다. 단일 곡선 세그먼트를 처리할 때 일반적으로 유사한 모양의 곡선을 생성하는 다양한 방정식은 세그먼트 내에서 매우 유사한 값을 생성합니다. 따라서 대부분의 경우 스플라인은 원하는 것에 대한 좋은 근사치를 생성합니다(그리고 각 점을 강제로 통과해야 하는 회귀와 달리 자연스럽게 모든 점을 통과합니다).

다시 한번 말씀드리지만 "대부분의 경우"입니다. 스플라인은 매우 균일하고 규칙적인 데이터에 적합하며 곡선의 "규칙"을 따릅니다. 특이한 데이터로 예상치 못한 일을 할 수 있습니다. 예를 들어,이전 SU 질문Excel에서 생성된 데이터 차트의 이상한 부정적인 "딥"에 관한 것입니다.

스플라인은 Jello와 약간 비슷합니다. 큰 덩어리의 Jello를 상상해 보고 원하는 특정 지점을 제한하십시오. 나머지 Jello는 필요한 곳에서 부풀어 오를 것입니다. 방정식은 특정 종류의 곡선을 정의할 수 있습니다. 특정 지점을 통해 곡선을 강제로 통과시키면 동일한 일이 발생합니다. 스플라인을 사용하면 효과가 이상하게 돌출되거나 부자연스러워 보이는 곡선 세그먼트로 제한됩니다. 고차 회귀 방정식은 거친 경로를 따를 수 있습니다.

스플라인이 데이터 곡선을 나타내는 방법은 다음과 같습니다.

이것을 고차 회귀 곡선과 비교하면 스플라인은 지역 변화에 더 "반응"합니다.

저는 스플라인이 포함된 분석 추가 기능이 있는 LibreOffice Calc를 사용하여 이 분석을 수행했습니다. 보시다시피 이는 스플라인에 대해서도 사용자가 찾고 있는 보간된 결과를 생성합니다. Excel의 분석 도구 키트에 액세스할 준비가 되어 있지 않으므로 Excel에 스플라인이 포함되어 있는지 알 수 없습니다. 그렇지 않은 경우 LO Calc는 Windows에서 실행되며 무료입니다.

결론

여기서는 중간 값을 보간하는 데 사용할 수 있는 접근 방식을 다룹니다. 서로 다른 접근 방식이 서로 다른 데이터에서 더 잘 작동할 수도 있습니다. 또는 귀하의 요구 사항은 대략적이고 빠르고 쉬운 것일 수도 있습니다. 어떤 종류의 보간이 필요한지 결정하십시오. 이를 달성하는 방법에 대한 자세한 내용이 필요한 경우 다른 답변에서 메커니즘을 다룰 수 있습니다.

Answer

짧은 답변

보간은 X 및 Y 값을 연결하는 방정식을 기반으로 합니다. 실제 방정식을 알면 원하는 중간값을 직접 계산할 수 있습니다. 그렇지 않은 경우 근사치를 사용하여 보간합니다. 근사치의 품질에 따라 중간 값이 얼마나 정확한지 결정됩니다. 제한된 수의 점을 사용하여 곡선을 근사화하는 경우 선형 보간법은 조악합니다. 더 나은 결과를 제공하는 몇 가지 다른 접근 방식과 대부분의 작업을 수행하는 내장 분석 도구가 있습니다.

긴 답변

중간 값 보간을 자동화하는 "일반 공식" 또는 솔루션을 찾고 있습니다. 거의 모든 데이터에 대해 선형 보간법을 사용할 수 있지만, 데이터 포인트 수가 제한되어 있고 데이터 모양에 상당한 곡률이 있는 경우 결과가 조잡할 것입니다. 정확성을 원한다면 "모든 것에 맞는 단일" 솔루션은 없습니다. 주어진 데이터세트에 대한 최상의 솔루션은 데이터의 특성에 따라 달라집니다.

방정식

어떻게 하든 보간은 X와 Y 사이의 관계를 정의하는 방정식을 사용하여 수행됩니다. 방정식은 실제 방정식이거나 추정치입니다. 추정치인 경우 데이터의 특성과 달성해야 하는 작업에 따라 다양한 접근 방식이 있습니다.

다른 질문에서는 방정식을 기반으로 데이터를 사용했습니다 Y=2^X. 실제 방정식이 있으면 정확하게 보간할 수 있습니다. X또는 중 하나에 대해 새 값을 선택하면 Y방정식이 다른 값을 제공합니다. 실제 방정식을 모른다면 그에 가까운 방정식을 찾아야 합니다. 이 답변을 사용하여 보간 접근 방식에 중점을 둘 것입니다. 이들은 일반적으로 대부분의 작업을 수행하는 내장 분석 도구를 사용합니다. 특정 도구를 사용하는 메커니즘이나 보다 자동화된 접근 방식에 대한 자세한 내용이 필요한 경우 다른 답변에서 이에 대해 확장할 수 있습니다.

실제 방정식을 찾아보세요

가장 좋은 해결책은 실제 방정식이 무엇인지 확인할 수 있는지 확인하는 것입니다. 데이터를 생성한 프로세스를 알고 있다면 방정식의 성격을 알 수 있습니다. 제어된 조건에서 단일 구동 변수를 처리하고 무작위 노이즈가 없는 많은 프로세스는 방정식 유형이 알려진 간단한 곡선을 따릅니다. 따라서 첫 번째 단계는 데이터의 모양을 살펴보고 그 중 하나와 유사한지 확인하는 것입니다.

이를 수행하는 쉬운 방법은 데이터를 그래프로 표시하고 추세선을 추가하는 것입니다. Excel에는 피팅할 수 있는 여러 가지 공통 곡선이 있습니다.

2^N다른 질문의 데이터 로 이것을 시도해 보겠습니다 . 숫자 패턴을 인식하지 못하고 추세선 접근 방식을 시도한 경우 다양한 모양의 곡선 아이콘이 표시됩니다. 지수 곡선은 일반적인 모양과 동일하며 다음을 제공합니다.

Excel은 단지 번역인 기본이 e아닌 사용합니다(e ^0.693 is ). 시각적으로 추세선이 데이터를 정확히 따르는 것을 볼 수 있습니다. R ² 도 이를 알려줍니다. R ^2는 방정식을 사용하여 설명하는 데이터의 변동 정도를 나타내는 통계적 척도입니다. 값은 방정식이 변동의 100%를 설명하거나 완벽하게 맞는 것을 의미합니다.221

이 질문의 예도 일종의 지수 형태를 가지고 있습니다. 동일한 접근 방식을 시도하면 다음과 같은 결과가 나타납니다.

따라서 이 데이터는 지수적이지 않습니다. 몇 가지 자연적인 과정을 설명하고 다양한 곡선을 모방할 수 있는 다항식을 시도해 볼 수 있습니다(나중에 자세히 설명하겠습니다).

데이터 이면의 프로세스에 대한 근사치로는 적합하지 않습니다. 세 번째 순서(X의 거듭제곱을 X^3까지 포함하는 방정식)에서는 데이터보다 주요 변곡점이 더 많고 여전히 일치하지 않습니다. 따라서 기본 방정식은 단순하고 일반적인 곡선처럼 보이지 않습니다. 이는 방정식을 근사화해야 함을 의미합니다.

선형 보간

이것이 귀하의 의견에 설명된 접근 방식입니다. 간단한 공식을 사용하여 간단하며 자동화하기가 매우 쉽습니다. 점이 많고 그 사이의 직선이 충분히 가깝다면 적절할 수 있습니다. 많은 곡선에서 일부 영역의 짧은 부분은 직선에 가깝습니다. 그러나 곡선에 대한 근사치는 좋지 않으며 곡률이 큰 영역에서는 결과가 부정확해집니다. 귀하의 예에서 X 값 7과 8 사이의 영역에는 곡률이 많이 있습니다. 이 영역에서 실제 곡선과 비교한 직선은 다음과 같습니다.

모든 데이터에 적용할 수 있는 일반적인 솔루션을 찾고 있습니다. 일부 데이터에서는 선형 보간법이 너무 조잡하다는 것을 알 수 있습니다.

회귀

사람들은 여기와 다른 게시물에서 회귀를 접근 방식으로 제안했습니다. 추세선이나 기본 워크시트 기능 또는 분석 도구를 사용하여 수행할 수 있습니다(해당 옵션을 Excel에 로드해야 하는 분석 도구 키트에 있을 수 있지만 기본적으로 로드되지 않을 수 있음).

회귀 분석에서는 데이터와 곡선 사이의 전체 오류를 최소화하려는 목적으로 데이터에 곡선을 맞추려고 합니다. 일반적인 사용법에서는 이 작업에 적합한 도구가 아닙니다(추세선을 맞추는 데 사용되는 방법이며 필요한 것과 비교하여 그 방법을 확인했습니다).

이는 데이터 이면의 프로세스를 모델링하는 것이 목표인 상황을 위한 것입니다. 데이터는 부정확한 것으로 가정되며, 회귀 분석을 통해 실제로 예상되는 데이터가 무엇인지 알 수 있습니다. 회귀 분석으로 찾은 곡선은 실제 데이터 포인트를 통과하지 못할 수도 있습니다. 귀하의 경우 데이터가 제공되고 정확한 것으로 간주됩니다. 곡선은 모든 점을 통과해야 합니다.
회귀 분석에서는 단일 방정식을 모든 데이터에 맞추려고 합니다. 데이터를 생성한 프로세스가 시도할 수 있는 방정식 유형으로 설명되지 않으면 효과적이지 않습니다. 데이터 포인트가 많으면 각 세그먼트의 선형 보간이 모든 데이터에 대한 회귀 곡선보다 더 나은 근사치가 될 수 있습니다.

그러나 일반적인 방법으로 사용하는 대신 회귀는 원하는 것에 대한 해결 방법으로 "남용"될 수 있으며 일반적으로 작동합니다. 프로세스를 모델링하려고 할 때 일반적으로 가장 간단한 공식(Occam의 면도칼)이 중요합니다. 반면에 충분히 복잡한 방정식을 사용하면 무엇이든 맞출 수 있습니다. 언제든지 모든 지점을 통과하는 낙서를 그릴 수 있습니다. 점을 사용하면 모든 점을 통과하는 차수 다항식을 N찾을 수 있습니다 (최악의 시나리오).N-1

제가 "보통"이라고 말한 이유는 어떤 경우에는 귀하의 목적에 쓸모가 없을 만큼 매우 힘든 라인이기 때문입니다. 그리고 이 접근 방식은 결과 방정식이 데이터 범위 밖의 동작을 예측한다는 점에서 실제로 아무것도 "모델링"하지 않습니다.

다음은 연속적으로 고차 방정식을 사용한 다항식 회귀를 사용하여 데이터를 분석한 것입니다(첫 번째 스크린샷에는 차수 3 - 5가 포함되어 있습니다).

(읽을 수 있는 크기를 보려면 이미지를 클릭하십시오.) 분석 도구에는 수행하려는 보간 유형이 포함되어 있습니다. 중간 값을 생성했습니다. 각 분석에서 a(n)값은 찾은 방정식의 계수입니다. a(0)는 상수, a(1)X^1 항의 계수 등입니다. 이는 피팅의 R ²1 값을 표시합니다. 귀하의 목적에 맞도록 가상으로 가까이 있어야 합니다 .

가장 큰 차이가 있는 원본 데이터 값을 강조했습니다. 이 순서 범위에서는 연속된 순서마다 적합성이 조금씩 좋아지지만 어떤 특정 지점이 더 정확하게 설명되는지는 변경될 수 있습니다. 이 세 가지에 대한 차트는 다음과 같습니다.

6차 및 7차 다항식에 도달하면 다음과 같습니다.

9개의 값에 대해 8차 다항식을 사용하면 완벽할 것이지만 7차 다항식은 아마도 충분히 가깝습니다. 관점에서 보면 7차 방정식의 R ² 는 .99999이지만 여전히 완벽하지는 않습니다.

적절한 적합도(이 경우 7차 또는 8차 방정식)를 찾기 위해 회귀 분석 도구를 사용하면 원하는 중간 값이 생성됩니다. 그러나 결과를 차트로 작성하고 곡선이 낙서가 아닌지 확인하는 것이 좋습니다.

스플라인

데이터를 차트로 표시하고 매끄러운 선 옵션을 선택한 경우 Excel에서 생성하는 데 사용되는 것은 스플라인입니다. 실제로 컴퓨터 그래픽의 거의 모든 응용 프로그램(글꼴 정의 포함)은 부드러운 곡선과 곡선 전환을 위한 스플라인을 기반으로 합니다. 이 이름은 설계자가 한때 임의의 점을 곡선과 연결하는 데 사용했던 유연한 규칙의 이름을 따서 명명되었습니다.

스플라인은 인접한 점을 고려하여 한 번에 한 섹션씩 각 섹션에 대한 곡선을 생성합니다. 곡선은 각 점을 통과하며 점을 직선으로 연결할 때처럼 점 양쪽에 급격한 변화가 없습니다.

스플라인에 사용되는 방정식은 데이터를 생성하는 프로세스를 모델링하려고 시도하지 않습니다. 그것은 엄격하게 예뻐 보이기 위한 것입니다. 그러나 대부분의 프로세스는 일종의 연속적이고 부드러운 곡선을 따릅니다. 단일 곡선 세그먼트를 처리할 때 일반적으로 유사한 모양의 곡선을 생성하는 다양한 방정식은 세그먼트 내에서 매우 유사한 값을 생성합니다. 따라서 대부분의 경우 스플라인은 원하는 것에 대한 좋은 근사치를 생성합니다(그리고 각 점을 강제로 통과해야 하는 회귀와 달리 자연스럽게 모든 점을 통과합니다).

다시 한번 말씀드리지만 "대부분의 경우"입니다. 스플라인은 매우 균일하고 규칙적인 데이터에 적합하며 곡선의 "규칙"을 따릅니다. 특이한 데이터로 예상치 못한 일을 할 수 있습니다. 예를 들어,이전 SU 질문Excel에서 생성된 데이터 차트의 이상한 부정적인 "딥"에 관한 것입니다.

스플라인은 Jello와 약간 비슷합니다. 큰 덩어리의 Jello를 상상해 보고 원하는 특정 지점을 제한하십시오. 나머지 Jello는 필요한 곳에서 부풀어 오를 것입니다. 방정식은 특정 종류의 곡선을 정의할 수 있습니다. 특정 지점을 통해 곡선을 강제로 통과시키면 동일한 일이 발생합니다. 스플라인을 사용하면 효과가 이상하게 돌출되거나 부자연스러워 보이는 곡선 세그먼트로 제한됩니다. 고차 회귀 방정식은 거친 경로를 따를 수 있습니다.

스플라인이 데이터 곡선을 나타내는 방법은 다음과 같습니다.

이것을 고차 회귀 곡선과 비교하면 스플라인은 지역 변화에 더 "반응"합니다.

저는 스플라인이 포함된 분석 추가 기능이 있는 LibreOffice Calc를 사용하여 이 분석을 수행했습니다. 보시다시피 이는 스플라인에 대해서도 사용자가 찾고 있는 보간된 결과를 생성합니다. Excel의 분석 도구 키트에 액세스할 준비가 되어 있지 않으므로 Excel에 스플라인이 포함되어 있는지 알 수 없습니다. 그렇지 않은 경우 LO Calc는 Windows에서 실행되며 무료입니다.

결론

여기서는 중간 값을 보간하는 데 사용할 수 있는 접근 방식을 다룹니다. 서로 다른 접근 방식이 서로 다른 데이터에서 더 잘 작동할 수도 있습니다. 또는 귀하의 요구 사항은 대략적이고 빠르고 쉬운 것일 수도 있습니다. 어떤 종류의 보간이 필요한지 결정하십시오. 이를 달성하는 방법에 대한 자세한 내용이 필요한 경우 다른 답변에서 메커니즘을 다룰 수 있습니다.

Question 2

질문에 대한 귀하의 의견과 수정 사항을 읽으면 이전 답변에서 실제로 다루지 않은 몇 가지 작업을 수행하고 싶은 것이 있습니다. 이 답변은 해당 항목을 다루며 전체 보간 프로세스를 수행하는 방법에 대한 단계별 연습을 포함했습니다.

부정확한 데이터

데이터를 생성하는 프로세스를 시간 간격으로 판독하는 것으로 설명하며 숫자는 반올림됩니다. 방정식은 데이터만큼 좋습니다. 실제 분석에서는 사용 가능한 가장 정확한 숫자를 사용해야 합니다. 아마도 반올림된 시간을 표시하여 예제를 단순하게 유지했을 것입니다.

그러나 표시되는 데이터는 물리적 프로세스에서 일반적으로 표시되는 곡선 종류와 정확히 일치하지 않습니다. 이론적인 곡선은 일반적으로 구동 변수가 하나뿐이고 잡음이 없을 때 매끄러워집니다. 미리 설정된 간격으로 판독값을 트리거하고 정확한 측정을 제공하기 위해 매우 정밀한 장비를 사용하는 경우 결과를 정확한 것으로 받아들일 수 있습니다. 그러나 수동으로 판독 시간을 정하고 판독값을 수동으로 취하는 경우 X판독값 자체가 정확하더라도 값이 정확하지 않을 수 있습니다. 개별 X값을 한 방향 또는 다른 방향으로 조금씩 이동하면 데이터 곡선에서 볼 수 있는 작은 불규칙성이 발생합니다(예가 단지 예의 목적으로 구성한 숫자가 아닌 경우).

이 경우 회귀를 사용하여 최적합을 추정하면 이점을 얻을 수 있습니다.

Y를 X로 사용

문제에서 Y(이 예에서는 1에서 37까지의 정수 값) 값을 정의하고 연관된 X 값을 찾으려고 합니다. Y=2^X간단한 방정식을 로 쉽게 되돌릴 수 X=log(Y)/log(2)있고 원하는 값을 직접 계산할 수 있기 때문에 문제 에서 충분히 쉽게 수행할 수 있습니다 . 방정식이 간단하지 않은 경우 이를 뒤집을 수 있는 실용적인 방법이 없는 경우가 많습니다. 이전 답변의 "남용된" 회귀 접근 방식은 고차 방정식을 제공하지만 "단방향"이므로 역 방정식을 해결하는 데 종종 실용적이지 않습니다.

가장 간단한 접근 방식은 처음부터 반대로 X하는 것입니다. Y이는 도입한 정수 값과 함께 사용할 수 있는 방정식을 제공합니다(분석은 이전 답변에서 설명한 대로 방정식의 계수를 제공합니다).

간단한 곡선이 작동하는지 확인하는 것은 결코 나쁠 것이 없습니다. 다음은 반전된 데이터이며 유용한 적합이 없음을 알 수 있습니다.

따라서 다항식 피팅을 시도해 보십시오. 그러나 이것은 이전 답변에서 설명한 것과 같은 경우입니다. 1부터 8까지의 값은 잘 맞지만 9는 소화불량을 줍니다. 3차 다항식은 충격을 줍니다:

방정식의 순서가 증가함에 따라 점점 더 "흥미로워집니다". 7번째 순서에서는 다음과 같은 결과를 얻습니다.

모든 점을 거의 정확하게 통과하지만 8과 9 사이의 곡선은 유용하지 않습니다. 한 가지 해결책은 8과 9 사이의 선형 보간을 사용하는 것입니다. 하지만 이 경우 상단에 스플라인을 통합하면 더 나은 값을 얻을 수 있습니다. 스플라인 옵션은 보기 좋은 핏을 제공하며 8과 9 사이에 더 적합한 곡선을 제공합니다.

불행하게도 스플라인 방정식은 약간 복잡하여 방정식이 제공되지 않습니다. 그러나 분석에서 제공되는 중간 값에 대해 선형 보간을 수행하면 합리적인 곡선에 맞는 숫자에 매우 가까워질 수 있습니다.

외삽과 보간

이 예에서 첫 번째 Y값은 2.9입니다. 데이터 범위를 벗어나는 1및 에 대한 값을 생성하려고 합니다 . 2이를 위해서는 보간보다는 외삽이 필요하며 이는 매우 다른 요구 사항입니다.

귀하의 예와 같이 방정식이 알려진 경우 Y=2^X원하는 값을 계산할 수 있습니다.
데이터를 생성하는 프로세스가 단순 곡선을 따르는 것으로 알려져 있고 적합성을 확신하는 경우 데이터 범위 외부의 값을 예측할 수 있으며 값이 실제로 있을 수 있는 범위에 대한 의미 있는 신뢰 구간을 얻을 수도 있습니다(다음을 기반으로 함). 데이터와 데이터 범위 내의 곡선 사이에 얼마나 많은 변화가 있는지).
고차 방정식을 데이터에 강제 적용하는 경우 데이터 범위 밖의 투영은 일반적으로 의미가 없습니다.
스플라인을 사용하는 경우 데이터 범위 외부에 투영할 수 있는 근거가 없습니다.

데이터 범위를 벗어나는 예측은 사용하는 방정식만큼만 유효하며, 정확한 방정식을 사용하지 않는 경우 데이터에서 멀어질수록 더 부정확해집니다.

첫 번째 그래프의 로그 곡선을 보면 예상한 것과 매우 다른 값이 예상되는 것을 볼 수 있습니다.

다항 방정식의 경우 영제곱 계수는 상수이며 이는 X의 값 에 대해 생성되는 값입니다 0. 이것이 곡선이 그 방향으로 어디로 가는지 보는 간단한 방법입니다.

4차 또는 5차 순서에서는 1부터 8까지의 항목이 매우 정확합니다. 그러나 일단 범위를 벗어나면 방정식은 매우 다르게 동작할 수 있습니다.

제한된 데이터를 사용한 추정

상황을 개선하는 한 가지 방법은 해당 끝의 점만 맞추고 해당 끝의 곡선 모양을 따르는 만큼 연속적인 점을 포함하는 것입니다. 포인트 9는 분명히 아웃되었습니다. 그 전에는 곡선에 여러 변곡점이 있습니다. 하나는 점 5 또는 6 주위에 있으므로 그보다 높은 점은 다른 곡선을 따릅니다. 1부터 5까지의 점만 사용하면 3차 다항식과 완벽하게 일치하는 값에 가까워집니다. 해당 방정식은 0.12095의 영점(위 표와 비교)을 예상하고 값 X은 1, 입니다 0.3493.

처음 5개 점에 직선을 맞추면 어떻게 될까요?

이는 영점을 -0.5138로 투영 X하고 1, -0.0071.

가능한 결과의 범위는 데이터 범위 밖의 불확실성 수준을 나타냅니다. 정답은 없습니다. 그리고 이것은 당신의 곡선의 "잘 행동하는" 끝에 있었습니다. 의 값 Y은 입니다 . 37로 가고 싶습니다. 스플라인은 곡선이 에서 점근적임을 나타냅니다 . 원시 데이터에 직선을 투영하면 (4차 다항식과 동일)보다 약간 더 큰 값이 생성됩니다. 3차 다항식은 (5차 및 6차와 마찬가지로) 보다 작은 값을 제안합니다 . 7차 다항식은 실질적으로 . 따라서 데이터 범위 밖의 모든 것은 추측이거나 원하는 것입니다.X936.79999

함께 모아서

이제 실제 솔루션이 어떤 모습일지 단계별로 살펴보겠습니다. 우리는 당신이 이미 정확한 방정식을 찾고 추세선을 사용하여 공통 곡선을 테스트했다고 가정합니다. 다음 단계는 회귀를 시도하는 것입니다. 왜냐하면 곡선의 공식을 제공하고 정수 값을 연결할 수 있기 때문입니다.

Excel 2013 또는 분석 도구 키트에 액세스할 준비가 되어 있지 않습니다. 이를 설명하기 위해 LibreOffice Calc를 사용하겠습니다. 동일하지는 않지만 Excel에서 따라갈 수 있을 만큼 유사합니다. LO Calc에서 이는 실제로 로드해야 하는 무료 확장입니다. 나는 사용하고있다CorelPolyGUI, 다운로드 가능여기. 제가 기억하는 분석 도구 키트에는 스플라인이 포함되어 있지 않았습니다. 여전히 그런 경우이고 Excel에서 이 작업을 수행하려는 경우이 무료 추가 기능(나는 테스트하지 않았습니다). 대안은 Windows에서 실행되며 무료인 LO Calc를 사용하는 것입니다.

여기서는 A열과 B열에 X와 Y 값(역전)을 입력하고 분석 대화 상자를 열었습니다. X 값을 강조 표시하고 X 버튼을 클릭하면 데이터 범위가 로드되며 다항식을 선택했습니다.

다음 탭에서 각도(모든 차수의 7차 다항식)를 사용 0하도록 지정합니다.7

출력을 지정하기 위해 C1을 선택하고 Columns를 클릭하면 출력에 필요한 열이 등록됩니다. 원본 데이터, 계산된 결과를 출력하도록 선택하고 각 원본 데이터 포인트 사이에 세 개의 중간 포인트를 추가하도록 선택했습니다. 그리고 나는 새로운 차트에 결과 그래프를 원한다고 말했습니다. 그런 다음 계산 메뉴로 이동하여 계산을 클릭합니다.

그리고 거기 있습니다. 계산된 값을 보면 문제가 있음을 알 수 있습니다. 다음 단계에서 분명해질 것입니다.

1여기에 through 값 을 추가했습니다 37. 이 시점에서는 보간만 다루려고 하므로 를 3통한 값만 계산하는 수식을 추가했습니다 36. 수식은 결과에 나열된 계수(a(n) 값)를 확장합니다. I2의 공식은 다음과 같습니다.

=D$4+D$5*H3+D$6*H3^2+D$7*H3^3+D$8*H3^4+D$9*H3^5+D$10*H3^6+D$11*H3^7

이는 각 계수에 X 값의 관련 거듭제곱을 곱한 것입니다. 이것을 아래로 드래그하면 결과가 나옵니다. 글쎄요. 온전성 테스트를 통과했는지 확인하려면 살펴봐야 합니다. 우리는 8과 사이에 문제가 있다는 것을 알고 있었지만 9그것은 원하는 값의 절반인 것으로 나타났습니다. 에서 3까지 의 값을 사용할 수 있지만 20다른 방법의 많은 값을 결합하는 것은 의미가 없습니다. 그럼 전체에 스플라인을 사용해 보겠습니다.

분석 대화 상자를 다시 열고 입력 탭(여기에는 표시되지 않음)에서 방법을 "스플라인"으로 변경합니다. 새로운 출력 범위를 지정하고 계산하도록 지시합니다. 그게 전부입니다.

우리는 새로운 결과를 얻었습니다. 데이터 범위를 이렇게 많은 세그먼트로 나누면 각 세그먼트가 짧아지므로 선형 보간법이 꽤 좋을 것입니다(원본 데이터에 사용하는 것보다 훨씬 낫습니다).

곡선 피팅 또는 보간 프로세스에는 데이터 포인트 생성이 포함됩니다. 곡선이 "해야 하는"(또는 하지 말아야 하는) 모양에 대한 자신의 판단을 사용합니다(회귀에서는 원본 데이터도 부정확하다고 가정합니다).

이 데이터에 온전한 검사를 제공하면 스플라인조차도 돌출부가 있는 연결 곡선을 생성한다는 것을 알 수 있습니다. 한 값이 약간 초과되는데 9, 이는 측정 중인 프로세스를 반영한 것이 아니라 인공물일 가능성이 높습니다. 이 경우에는 점근적인 곡선이 될 가능성이 높기 때문에 눈으로 보는 것 9보다 머리카락 하나만큼 작은 값을 고점에 임의로 할당했습니다 . 9내 가치가 정확하다고 가정하는 것이 아니라 단지 개선되었다는 가정입니다. 이 그림에서는 사용될 값이 포함된 새 열을 만들었습니다.

1을 통해 귀하의 번호가 포함된 열을 추가했습니다 37. 이전 논의에서 1및 값을 예측할 수 있는 신뢰할 수 있는 근거가 없으므로 2공백으로 두었습니다. 에 대해 37나는 점근적 가정을 가지고 그것을 만들었습니다 9. 3통과 값은 36선형 보간법으로 구합니다(이는 다른 데이터에 적용할 수 있는 공식입니다). Q3의 공식은 다음과 같습니다.

=TREND(OFFSET($M$1,MATCH(P3,M$1:M$33)-1,2,2),OFFSET($M$1,MATCH(P3,M$1:M$33)-1,0,2),P3)

TREND 함수는 범위가 두 점인 경우에만 보간합니다. 구문은 다음과 같습니다.

TREND(Y_range, X_range, X_value)

각 범위마다 OFFSET 기능이 사용됩니다. 각각의 경우 MATCH 함수를 사용하여 대상 값이 포함된 범위의 첫 번째 행을 찾습니다. 값 -1은 위치가 아닌 오프셋이기 때문입니다. 첫 번째 행의 일치 항목은 0참조 행의 오프셋입니다 . 이 경우에는 값을 수동으로 조정하기 위해 추가 열을 추가했기 때문에 열 Y이 오프셋됩니다 . 2OFFSET 매개변수는 Y 또는 X 값이 포함된 열을 선택하고 범위 높이 2를 선택합니다. 그러면 대상보다 낮은 값과 높은 값이 제공됩니다.

결과:

분석 마법사는 어려운 작업을 수행하며 다항식 회귀를 사용하든 스플라인을 사용하든 결과를 생성하는 데 수식 하나만 있으면 됩니다.

Answer

질문에 대한 귀하의 의견과 수정 사항을 읽으면 이전 답변에서 실제로 다루지 않은 몇 가지 작업을 수행하고 싶은 것이 있습니다. 이 답변은 해당 항목을 다루며 전체 보간 프로세스를 수행하는 방법에 대한 단계별 연습을 포함했습니다.

부정확한 데이터

데이터를 생성하는 프로세스를 시간 간격으로 판독하는 것으로 설명하며 숫자는 반올림됩니다. 방정식은 데이터만큼 좋습니다. 실제 분석에서는 사용 가능한 가장 정확한 숫자를 사용해야 합니다. 아마도 반올림된 시간을 표시하여 예제를 단순하게 유지했을 것입니다.

그러나 표시되는 데이터는 물리적 프로세스에서 일반적으로 표시되는 곡선 종류와 정확히 일치하지 않습니다. 이론적인 곡선은 일반적으로 구동 변수가 하나뿐이고 잡음이 없을 때 매끄러워집니다. 미리 설정된 간격으로 판독값을 트리거하고 정확한 측정을 제공하기 위해 매우 정밀한 장비를 사용하는 경우 결과를 정확한 것으로 받아들일 수 있습니다. 그러나 수동으로 판독 시간을 정하고 판독값을 수동으로 취하는 경우 X판독값 자체가 정확하더라도 값이 정확하지 않을 수 있습니다. 개별 X값을 한 방향 또는 다른 방향으로 조금씩 이동하면 데이터 곡선에서 볼 수 있는 작은 불규칙성이 발생합니다(예가 단지 예의 목적으로 구성한 숫자가 아닌 경우).

이 경우 회귀를 사용하여 최적합을 추정하면 이점을 얻을 수 있습니다.

Y를 X로 사용

문제에서 Y(이 예에서는 1에서 37까지의 정수 값) 값을 정의하고 연관된 X 값을 찾으려고 합니다. Y=2^X간단한 방정식을 로 쉽게 되돌릴 수 X=log(Y)/log(2)있고 원하는 값을 직접 계산할 수 있기 때문에 문제 에서 충분히 쉽게 수행할 수 있습니다 . 방정식이 간단하지 않은 경우 이를 뒤집을 수 있는 실용적인 방법이 없는 경우가 많습니다. 이전 답변의 "남용된" 회귀 접근 방식은 고차 방정식을 제공하지만 "단방향"이므로 역 방정식을 해결하는 데 종종 실용적이지 않습니다.

가장 간단한 접근 방식은 처음부터 반대로 X하는 것입니다. Y이는 도입한 정수 값과 함께 사용할 수 있는 방정식을 제공합니다(분석은 이전 답변에서 설명한 대로 방정식의 계수를 제공합니다).

간단한 곡선이 작동하는지 확인하는 것은 결코 나쁠 것이 없습니다. 다음은 반전된 데이터이며 유용한 적합이 없음을 알 수 있습니다.

따라서 다항식 피팅을 시도해 보십시오. 그러나 이것은 이전 답변에서 설명한 것과 같은 경우입니다. 1부터 8까지의 값은 잘 맞지만 9는 소화불량을 줍니다. 3차 다항식은 충격을 줍니다:

방정식의 순서가 증가함에 따라 점점 더 "흥미로워집니다". 7번째 순서에서는 다음과 같은 결과를 얻습니다.

모든 점을 거의 정확하게 통과하지만 8과 9 사이의 곡선은 유용하지 않습니다. 한 가지 해결책은 8과 9 사이의 선형 보간을 사용하는 것입니다. 하지만 이 경우 상단에 스플라인을 통합하면 더 나은 값을 얻을 수 있습니다. 스플라인 옵션은 보기 좋은 핏을 제공하며 8과 9 사이에 더 적합한 곡선을 제공합니다.

불행하게도 스플라인 방정식은 약간 복잡하여 방정식이 제공되지 않습니다. 그러나 분석에서 제공되는 중간 값에 대해 선형 보간을 수행하면 합리적인 곡선에 맞는 숫자에 매우 가까워질 수 있습니다.

외삽과 보간

이 예에서 첫 번째 Y값은 2.9입니다. 데이터 범위를 벗어나는 1및 에 대한 값을 생성하려고 합니다 . 2이를 위해서는 보간보다는 외삽이 필요하며 이는 매우 다른 요구 사항입니다.

귀하의 예와 같이 방정식이 알려진 경우 Y=2^X원하는 값을 계산할 수 있습니다.
데이터를 생성하는 프로세스가 단순 곡선을 따르는 것으로 알려져 있고 적합성을 확신하는 경우 데이터 범위 외부의 값을 예측할 수 있으며 값이 실제로 있을 수 있는 범위에 대한 의미 있는 신뢰 구간을 얻을 수도 있습니다(다음을 기반으로 함). 데이터와 데이터 범위 내의 곡선 사이에 얼마나 많은 변화가 있는지).
고차 방정식을 데이터에 강제 적용하는 경우 데이터 범위 밖의 투영은 일반적으로 의미가 없습니다.
스플라인을 사용하는 경우 데이터 범위 외부에 투영할 수 있는 근거가 없습니다.

데이터 범위를 벗어나는 예측은 사용하는 방정식만큼만 유효하며, 정확한 방정식을 사용하지 않는 경우 데이터에서 멀어질수록 더 부정확해집니다.

첫 번째 그래프의 로그 곡선을 보면 예상한 것과 매우 다른 값이 예상되는 것을 볼 수 있습니다.

다항 방정식의 경우 영제곱 계수는 상수이며 이는 X의 값 에 대해 생성되는 값입니다 0. 이것이 곡선이 그 방향으로 어디로 가는지 보는 간단한 방법입니다.

4차 또는 5차 순서에서는 1부터 8까지의 항목이 매우 정확합니다. 그러나 일단 범위를 벗어나면 방정식은 매우 다르게 동작할 수 있습니다.

제한된 데이터를 사용한 추정

상황을 개선하는 한 가지 방법은 해당 끝의 점만 맞추고 해당 끝의 곡선 모양을 따르는 만큼 연속적인 점을 포함하는 것입니다. 포인트 9는 분명히 아웃되었습니다. 그 전에는 곡선에 여러 변곡점이 있습니다. 하나는 점 5 또는 6 주위에 있으므로 그보다 높은 점은 다른 곡선을 따릅니다. 1부터 5까지의 점만 사용하면 3차 다항식과 완벽하게 일치하는 값에 가까워집니다. 해당 방정식은 0.12095의 영점(위 표와 비교)을 예상하고 값 X은 1, 입니다 0.3493.

처음 5개 점에 직선을 맞추면 어떻게 될까요?

이는 영점을 -0.5138로 투영 X하고 1, -0.0071.

가능한 결과의 범위는 데이터 범위 밖의 불확실성 수준을 나타냅니다. 정답은 없습니다. 그리고 이것은 당신의 곡선의 "잘 행동하는" 끝에 있었습니다. 의 값 Y은 입니다 . 37로 가고 싶습니다. 스플라인은 곡선이 에서 점근적임을 나타냅니다 . 원시 데이터에 직선을 투영하면 (4차 다항식과 동일)보다 약간 더 큰 값이 생성됩니다. 3차 다항식은 (5차 및 6차와 마찬가지로) 보다 작은 값을 제안합니다 . 7차 다항식은 실질적으로 . 따라서 데이터 범위 밖의 모든 것은 추측이거나 원하는 것입니다.X936.79999

함께 모아서

이제 실제 솔루션이 어떤 모습일지 단계별로 살펴보겠습니다. 우리는 당신이 이미 정확한 방정식을 찾고 추세선을 사용하여 공통 곡선을 테스트했다고 가정합니다. 다음 단계는 회귀를 시도하는 것입니다. 왜냐하면 곡선의 공식을 제공하고 정수 값을 연결할 수 있기 때문입니다.

Excel 2013 또는 분석 도구 키트에 액세스할 준비가 되어 있지 않습니다. 이를 설명하기 위해 LibreOffice Calc를 사용하겠습니다. 동일하지는 않지만 Excel에서 따라갈 수 있을 만큼 유사합니다. LO Calc에서 이는 실제로 로드해야 하는 무료 확장입니다. 나는 사용하고있다CorelPolyGUI, 다운로드 가능여기. 제가 기억하는 분석 도구 키트에는 스플라인이 포함되어 있지 않았습니다. 여전히 그런 경우이고 Excel에서 이 작업을 수행하려는 경우이 무료 추가 기능(나는 테스트하지 않았습니다). 대안은 Windows에서 실행되며 무료인 LO Calc를 사용하는 것입니다.

여기서는 A열과 B열에 X와 Y 값(역전)을 입력하고 분석 대화 상자를 열었습니다. X 값을 강조 표시하고 X 버튼을 클릭하면 데이터 범위가 로드되며 다항식을 선택했습니다.

다음 탭에서 각도(모든 차수의 7차 다항식)를 사용 0하도록 지정합니다.7

출력을 지정하기 위해 C1을 선택하고 Columns를 클릭하면 출력에 필요한 열이 등록됩니다. 원본 데이터, 계산된 결과를 출력하도록 선택하고 각 원본 데이터 포인트 사이에 세 개의 중간 포인트를 추가하도록 선택했습니다. 그리고 나는 새로운 차트에 결과 그래프를 원한다고 말했습니다. 그런 다음 계산 메뉴로 이동하여 계산을 클릭합니다.

그리고 거기 있습니다. 계산된 값을 보면 문제가 있음을 알 수 있습니다. 다음 단계에서 분명해질 것입니다.

1여기에 through 값 을 추가했습니다 37. 이 시점에서는 보간만 다루려고 하므로 를 3통한 값만 계산하는 수식을 추가했습니다 36. 수식은 결과에 나열된 계수(a(n) 값)를 확장합니다. I2의 공식은 다음과 같습니다.

=D$4+D$5*H3+D$6*H3^2+D$7*H3^3+D$8*H3^4+D$9*H3^5+D$10*H3^6+D$11*H3^7

이는 각 계수에 X 값의 관련 거듭제곱을 곱한 것입니다. 이것을 아래로 드래그하면 결과가 나옵니다. 글쎄요. 온전성 테스트를 통과했는지 확인하려면 살펴봐야 합니다. 우리는 8과 사이에 문제가 있다는 것을 알고 있었지만 9그것은 원하는 값의 절반인 것으로 나타났습니다. 에서 3까지 의 값을 사용할 수 있지만 20다른 방법의 많은 값을 결합하는 것은 의미가 없습니다. 그럼 전체에 스플라인을 사용해 보겠습니다.

분석 대화 상자를 다시 열고 입력 탭(여기에는 표시되지 않음)에서 방법을 "스플라인"으로 변경합니다. 새로운 출력 범위를 지정하고 계산하도록 지시합니다. 그게 전부입니다.

우리는 새로운 결과를 얻었습니다. 데이터 범위를 이렇게 많은 세그먼트로 나누면 각 세그먼트가 짧아지므로 선형 보간법이 꽤 좋을 것입니다(원본 데이터에 사용하는 것보다 훨씬 낫습니다).

곡선 피팅 또는 보간 프로세스에는 데이터 포인트 생성이 포함됩니다. 곡선이 "해야 하는"(또는 하지 말아야 하는) 모양에 대한 자신의 판단을 사용합니다(회귀에서는 원본 데이터도 부정확하다고 가정합니다).

이 데이터에 온전한 검사를 제공하면 스플라인조차도 돌출부가 있는 연결 곡선을 생성한다는 것을 알 수 있습니다. 한 값이 약간 초과되는데 9, 이는 측정 중인 프로세스를 반영한 것이 아니라 인공물일 가능성이 높습니다. 이 경우에는 점근적인 곡선이 될 가능성이 높기 때문에 눈으로 보는 것 9보다 머리카락 하나만큼 작은 값을 고점에 임의로 할당했습니다 . 9내 가치가 정확하다고 가정하는 것이 아니라 단지 개선되었다는 가정입니다. 이 그림에서는 사용될 값이 포함된 새 열을 만들었습니다.

1을 통해 귀하의 번호가 포함된 열을 추가했습니다 37. 이전 논의에서 1및 값을 예측할 수 있는 신뢰할 수 있는 근거가 없으므로 2공백으로 두었습니다. 에 대해 37나는 점근적 가정을 가지고 그것을 만들었습니다 9. 3통과 값은 36선형 보간법으로 구합니다(이는 다른 데이터에 적용할 수 있는 공식입니다). Q3의 공식은 다음과 같습니다.

=TREND(OFFSET($M$1,MATCH(P3,M$1:M$33)-1,2,2),OFFSET($M$1,MATCH(P3,M$1:M$33)-1,0,2),P3)

TREND 함수는 범위가 두 점인 경우에만 보간합니다. 구문은 다음과 같습니다.

TREND(Y_range, X_range, X_value)

각 범위마다 OFFSET 기능이 사용됩니다. 각각의 경우 MATCH 함수를 사용하여 대상 값이 포함된 범위의 첫 번째 행을 찾습니다. 값 -1은 위치가 아닌 오프셋이기 때문입니다. 첫 번째 행의 일치 항목은 0참조 행의 오프셋입니다 . 이 경우에는 값을 수동으로 조정하기 위해 추가 열을 추가했기 때문에 열 Y이 오프셋됩니다 . 2OFFSET 매개변수는 Y 또는 X 값이 포함된 열을 선택하고 범위 높이 2를 선택합니다. 그러면 대상보다 낮은 값과 높은 값이 제공됩니다.

결과:

분석 마법사는 어려운 작업을 수행하며 다항식 회귀를 사용하든 스플라인을 사용하든 결과를 생성하는 데 수식 하나만 있으면 됩니다.

Excel에서 임의 데이터의 중간 값을 보간하는 방법

내가 시도한 것

답변1

짧은 답변

긴 답변

방정식

실제 방정식을 찾아보세요

선형 보간

회귀

스플라인

결론

답변2

부정확한 데이터

Y를 X로 사용

외삽과 보간

제한된 데이터를 사용한 추정

함께 모아서

관련 정보