Bepalingscoëfficiënt in Excel

Een van de indicatoren die de kwaliteit van het geconstrueerde model in statistieken beschrijft, is de bepalingcoëfficiënt (R ^ 2), die ook de waarde van de nauwkeurigheid van de onderlinge aanpassing wordt genoemd. Hiermee kunt u het niveau van precisievoorspelling bepalen. Laten we leren hoe u deze indicator kunt berekenen met behulp van verschillende Excel-tools.

Berekening van de bepalingscoëfficiënt

Afhankelijk van het niveau van bepalingscoëfficiënt, is het gebruikelijk om modellen in drie groepen te delen:

• 0,8 - 1 - een model van goede kwaliteit;
• 0,5 - 0.8 - Model van aanvaardbare kwaliteit;
• 0 - 0.5 - een model van slechte kwaliteit.

In het laatste geval geeft de kwaliteit van het model de onmogelijkheid aan van het gebruik ervan voor de prognose.

De keuze van een methode voor het berekenen van de opgegeven waarde in Excel is afhankelijk van de vraag of regressie lineair is of niet. In het eerste geval kunt u de Firson-functie gebruiken en in de tweede moet u een speciaal gereedschap van het analysepakket gebruiken.

Methode 1: Berekening van de bepalingcoëfficiënt met een lineaire functie

Allereerst, ontdek hoe u de bepalingcoëfficiënt met een lineaire functie kunt vinden. In dit geval is deze indicator gelijk aan het vierkant van de correlatiecoëfficiënt. We zullen het berekenen met behulp van de ingesloten Excel-functie in het voorbeeld van een specifieke tabel, die hieronder wordt getoond.

1. We markeren de cel waar de bepalingcoëfficiënt wordt uitgevoerd na de berekening en klik op het pictogram "Invoegenfunctie".



2. De functies van de functies begint. We gaan naar zijn categorie "Statistical" en noteer de naam "KVGirson". Volgende, klei op de knop "OK".



3. Het venster Quilinson-functieargumenten begint. Deze operator uit de statistische groep is ontworpen om het vierkant van de correlatiecoëfficiënt van de Pearson-functie te berekenen, dat wil zeggen een lineaire functie. En zoals we ons herinneren, met een lineaire functie, is de bepalingcoëfficiënt net gelijk aan het vierkant van de correlatiecoëfficiënt.

   De syntaxis van deze operator is:

   = CVGirson (bekende_stations_y; bekende_name_x)

   De functie heeft dus twee exploitanten, waarvan er één een lijst met functiewaarden en de tweede argumenten is. Operators kunnen zo rechtstreeks worden weergegeven als de waarden die worden vermeld via een puntkomma (;) en in de vorm van verwijzingen naar de reeksen waar ze zich bevinden. Het is de laatste optie en zal door ons in dit voorbeeld worden gebruikt.

   Installeer de cursor in het veld "Bekend V-waarden". We voeren de klem van de linkermuisknop uit en vervaardigen de inhoud van de kolom "Y" van de tabel. Zoals u kunt zien, wordt het adres van de opgegeven gegevensarray onmiddellijk in het venster weergegeven.

   Vul op dezelfde manier het veld "Bekend X". We plaatsen de cursor op dit gebied, maar deze tijd wijst de waarden van de kolom "X" toe.

   Nadat alle gegevens werden weergegeven in het venster CVGIRSON-argumenten, de klei op de knop "OK" in de bodem.



4. Zoals u hierna kunt zien, maakt het programma het berekenen van de bepalingcoëfficiënt en geeft het resultaat in de cel die is gemarkeerd voordat u de tovenaar van functies belt. In ons voorbeeld bleek de waarde van de berekende indicator gelijk aan 1. Dit betekent dat het gepresenteerde model absoluut betrouwbaar is, dat wil zeggen, elimineert de fout.

LES: Master of Functies in Microsoft Excel

Methode 2: Berekening van de bepalingcoëfficiënt in niet-lineaire functies

Maar de bovenstaande optie voor het berekenen van de gewenste waarde kan alleen worden gebruikt voor lineaire functies. Wat te doen om zijn berekening in een niet-lineaire functie te maken? In Excel is er een kans. Het kan worden gedaan met behulp van het gereedschap "Regression", dat een integraal onderdeel is van het data-analyse-pakket.

1. Maar voordat u het opgegeven gereedschap gebruikt, moet het "Analyse-pakket" zelf worden geactiveerd, die standaard is uitgeschakeld in Excele. We gaan naar het tabblad "Bestand" en gaan vervolgens door het item "Parameters".



2. Maak in het venster dat wordt geopend naar het gedeelte "Add-in" met behulp van de navigatie in het linker verticale menu. Aan de onderkant van het rechtervenster bevindt het besturingsveld zich. Selecteer in de lijst van de daar beschikbaar subsitecties de naam van de "Add-in Excel ..." en klik vervolgens op de knop "GO ...", aan de rechterkant van het veld.



3. Het venster wordt gelanceerd. In het centrale deel is er een lijst met beschikbare add-ons. Installeer het selectievakje in de buurt van het pakket "Analyse-pakket". Hierna moet u op de knop "OK" klikken aan de rechterkant van de vensterinterface.



4. Het gereedschapspakket voor gegevensanalyse in de huidige Excel-exemplaar wordt geactiveerd. Toegang tot het bevindt zich op de tape op het tabblad Gegevens. Ga naar het opgegeven tabblad en klei op de knop "Gegevensanalyse" in de instellingengroep "Analyse".



5. Het venster "Gegevensanalyse" is geactiveerd met een lijst met informatieverwerkingshulpmiddelen. We weken uit deze lijstitem "regressie" en klei op de knop "OK".



6. Dan wordt het gereedschapsvenster "Regressie" geopend. Het eerste instellingenblok is "Inputgegevens". Hier in twee velden moet u het adres van de reeksen opgeven waar de waarden van het argument en de functie zich bevinden. We plaatsen de cursor in het veld "Inputinterval y" en wijst de inhoud van de kolom "Y" op het vel toe. Nadat het adres van de array in het regressievenster verschijnt, plaatsen we de cursor in het veld "Inputinterval y" en wijzen op dezelfde manier de cellen van de kolom "X" toe.

   Dichtbij de parameters "Tag" en "Constant-Zero" vlaggen niet. Het selectievakje kan worden geïnstalleerd in de buurt van de parameter "Betrouwbaarheid" en in het tegenovergestelde veld specificeren de gewenste waarde van de overeenkomstige indicator (standaard 95%).

   In de groep "Uitvoerparameters" moet u specificeren in welk gebied het resultaat van de berekening wordt weergegeven. Er zijn drie opties:

   • Gebied op het huidige vel;
   • Een ander blad;
   • Een ander boek (nieuw bestand).

   Stel uw keuze in de eerste uitvoeringsvorm vast zodat de brongegevens en het resultaat op één werkblad worden geplaatst. We plaatsen de schakelaar in de buurt van de parameter "Uitgangsinterval". In het veld tegenover dit item zetten we de cursor. Klik op de linkermuisknop op het lege element op het blad, dat is ontworpen om de linker bovencel van de uitvoertabel van de berekening te worden. Het adres van dit element moet worden gemarkeerd in het venster "Regressie".

   Groepen parameters "Residuen" en "Normale waarschijnlijkheid" negeren, omdat het niet belangrijk is voor het oplossen van de taak. Daarna zijn we klei op de knop "OK", die in de rechterbovenhoek van het venster "Regression" wordt geplaatst.



7. Het programma maakt een berekening op basis van eerder ingevoerde gegevens en geeft het resultaat weer op het opgegeven bereik. Zoals u kunt zien, geeft deze tool een vrij groot aantal resultaten op verschillende parameters weer. Maar in de context van de huidige les zijn we geïnteresseerd in de R-Square-indicator. In dit geval is het gelijk aan 0.947664, die het geselecteerde model kenmerkt als een model van goede kwaliteit.

Methode 3: Bepalingcoëfficiënt voor trendlijn

Naast de bovenstaande opties kan de bepalingscoëfficiënt rechtstreeks worden weergegeven voor de trendlijn in de grafiek die op het Excel-vel is gebouwd. Ontdek hoe dit op een specifiek voorbeeld kan worden gedaan.

1. We hebben een schema gebouwd op basis van de argumenttabel en de functies van de functie die voor het vorige voorbeeld werd gebruikt. We zullen er een trendlijn naar bouwen. Klik op een site van het bouwgebied waarop het schema links is ingeschakeld. Tegelijkertijd verschijnt een extra set van tabbladen op de band - "Werk met grafieken". Ga naar het tabblad "Layout". Klei op de knop "Trendlijn", die zich bevindt in de werkbalk "Analyse". Een menu verschijnt met een keuze uit het type trendlijn. Start de keuze op het type dat overeenkomt met de specifieke taak. Laat ons de optie "Exponentiële benadering" kiezen voor ons voorbeeld.



2. Excel bouwt direct op het vlak van het construeren van een trendlijn in de vorm van een extra zwarte curve.



3. Nu is onze taak om de daadwerkelijke bepalingscoëfficiënt weer te geven. Klik op de rechtermuisknop langs de trendlijn. Het contextmenu is geactiveerd. Start de keuze erin bij het TREND LINE-formaatpunt.

   

   Om een ​​overgang uit te voeren naar het venster Trendlijnindeling, kunt u een alternatieve actie maken. We markeren de trendlijn met de linkermuisknop erop. Ga naar het tabblad "Layout". Klei op de knop "Trendlijn" in het blok "Analyse". In de lijst met de klaslijst op het beste punt van de actielijst - "Extra TREND-lijnparameters ...".



4. Na een van de twee bovenstaande acties wordt het formaatvenster gelanceerd waarin u extra instellingen kunt produceren. In het bijzonder, om aan onze taak te voldoen, is het noodzakelijk om een ​​selectievakje tegenover het item te maken "plaats de waarde van de nauwkeurigheid van de benadering (R ^ 2)". Het bevindt zich aan de onderkant van het venster. Dat wil zeggen, dus we nemen het display van de bepalingcoëfficiënt op het bouwgebied op. Vergeet dan niet te klikken op de knop "Sluiten" onder aan het huidige venster.



5. De waarde van de nauwkeurigheid van benadering, dat wil zeggen, de waarde van de bepalingcoëfficiënt wordt weergegeven op een blad in het bouwgebied. In dit geval is deze waarde zichtbaar, gelijk aan 0.9242, die de benadering kenmerkt als een model van goede kwaliteit.



6. Absoluut op deze manier kunt u een bepalingscoëfficiënt opzetten voor elk ander type trendlijn. U kunt het trendlijntype wijzigen door een overgang te maken via de knop op het lint of het contextmenu in het venster van zijn parameters, zoals hierboven weergegeven. Dan in het raam zelf in de groep "Build Trend Line", kunt u overschakelen naar een ander type. We vergeet het niet om het te beheersen om "de waarde van de nauwkeurigheid van de onderlinge aanpassing op het diagram van de waarde van de benadering te plaatsen". Nadat u de bovenstaande acties hebt voltooid, klikt u op de knop "Sluiten" in de rechterbenedenhoek van het venster.



7. Met een lineair type heeft de trendlijn al de waarde van de nauwkeurigheid van de onderlinge aanpassing gelijk aan 0,9477, die kenmerkt dit model, zoals nog betrouwbaarder dan de lijn van de trend van het exponentieel type dat door ons wordt overwogen.



8. Dus schakelen tussen verschillende soorten trendlijnen en het vergelijken van hun betrouwbaarheidswaarden (bepalingscoëfficiënt), kunt u de optie vinden die het model het meest het ingediende schema nauwkeurig beschrijft. Een optie met het hoogste vastberadenheid zal het meest betrouwbaar zijn. Op basis hiervan kunt u de meest nauwkeurige voorspelling opbouwen.

   Voor ons geval was het bijvoorbeeld mogelijk om vast te stellen dat het hoogste niveau van betrouwbaarheid een polynoomtype van een tweede graadtrend heeft. De bepalingcoëfficiënt is in dit geval 1. Dit suggereert dat het opgegeven model absoluut betrouwbaar is, wat de volledige uitzondering van fouten betekent.

   

   Maar tegelijkertijd betekent dit niet dat voor een andere grafiek, dit type trendlijn ook de meest betrouwbare is. De optimale keuze van het type trendlijn hangt af van het type functie, op basis waarvan het schema werd gebouwd. Als de gebruiker niet genoeg kennis heeft om "op het oog" de optie van de hoogste kwaliteit te schatten, is de enige output van het bepalen van de best prognose slechts een vergelijking van beslissingscoëfficiënten, zoals weergegeven in het bovenstaande voorbeeld.

Zie ook:

Een trendlijn bouwen in Excel

Benadering in Excel

In Excel zijn er twee hoofdopties voor het berekenen van de bepalingcoëfficiënt: het gebruik van de exploitant van de Firson en de toepassing van het regressietool uit het gereedschapspakket voor gegevensanalyse. Tegelijkertijd is de eerste van deze opties alleen bedoeld voor gebruik tijdens de verwerking van een lineaire functie en kan in bijna alle situaties een andere optie worden gebruikt. Bovendien is het mogelijk om de bepalingscoëfficiënt voor de lijn van grafieken van grafieken weer te geven als de waarde van de nauwkeurigheid van de onderlinge aanpassing. Met deze indicator is het mogelijk om het type trendlijn te bepalen, dat het hoogste niveau van betrouwbaarheid heeft voor een specifieke functie.