Waar is de trendlijn in Excel. Prognosetools in Microsoft Excel
Theoretische informatie
In de praktijk wordt bij het modelleren van verschillende processen - in het bijzonder economische, fysieke, technische, sociale - een of andere methode voor het berekenen van geschatte waarden van functies op basis van hun bekende waarden op bepaalde vaste punten op grote schaal gebruikt.
Dit soort functie-benaderingsproblemen doet zich vaak voor:
- bij het construeren van benaderende formules voor het berekenen van de waarden van karakteristieke grootheden van het onderzochte proces met behulp van tabelgegevens verkregen als resultaat van het experiment;
- in numerieke integratie, differentiatie, het oplossen van differentiaalvergelijkingen, enz.;
- als het nodig is om de waarden van functies op tussenliggende punten van het beschouwde interval te berekenen;
- bij het bepalen van de waarden van karakteristieke grootheden van een proces buiten het beschouwde interval, in het bijzonder bij het voorspellen.
Als we, om een bepaald proces gespecificeerd door een tabel te modelleren, een functie construeren die dit proces bij benadering beschrijft op basis van de kleinste kwadratenmethode, zal dit een benaderende functie (regressie) worden genoemd, en het probleem van het construeren van benaderende functies zelf zal worden genoemd een benaderingsprobleem.
Dit artikel bespreekt de mogelijkheden van het MS Excel-pakket voor het oplossen van dit soort problemen. Daarnaast biedt het methoden en technieken voor het construeren (creëren) van regressies voor tabelfuncties (wat de basis vormt van regressieanalyse).
Excel heeft twee opties voor het bouwen van regressies.
- Het toevoegen van geselecteerde regressies (trendlijnen) aan een diagram dat is opgebouwd op basis van een gegevenstabel voor het onderzochte proceskenmerk (alleen beschikbaar als er een diagram is gemaakt);
- Met behulp van de ingebouwde statistische functies van het Excel-werkblad, zodat u regressies (trendlijnen) rechtstreeks uit de brongegevenstabel kunt verkrijgen.
Trendlijnen aan een diagram toevoegen
Voor een tabel met gegevens die een proces beschrijft en wordt weergegeven door een diagram, beschikt Excel over een effectief hulpmiddel voor regressieanalyse waarmee u:
- bouwen op basis van de kleinste kwadratenmethode en vijf soorten regressies aan het diagram toevoegen, die het onderzochte proces met verschillende mate van nauwkeurigheid modelleren;
- voeg de geconstrueerde regressievergelijking toe aan het diagram;
- bepaal de mate van overeenstemming van de geselecteerde regressie met de gegevens die in de grafiek worden weergegeven.
Op basis van grafiekgegevens kunt u met Excel lineaire, polynomiale, logaritmische, machts- en exponentiële soorten regressies verkrijgen, die worden gespecificeerd door de vergelijking:
y = y(x)
waarbij x een onafhankelijke variabele is die vaak de waarden aanneemt van een reeks natuurlijke getallen (1; 2; 3; ...) en bijvoorbeeld een aftelling produceert van de tijd van het onderzochte proces (kenmerken).
1 . Lineaire regressie is goed voor het modelleren van kenmerken waarvan de waarden met een constante snelheid stijgen of dalen. Dit is het eenvoudigste model om te construeren voor het onderzochte proces. Zij
y = mx + b
waarbij m de raaklijn is van de lineaire regressiehelling aan de x-as; b - coördinaat van het snijpunt van lineaire regressie met de ordinaatas.
2 . Een polynomiale trendlijn is nuttig voor het beschrijven van kenmerken die verschillende afzonderlijke uitersten hebben (maxima en minima). De keuze van de polynomiale graad wordt bepaald door het aantal extremen van het onderzochte kenmerk. Een polynoom van de tweede graad kan dus heel goed een proces beschrijven dat slechts één maximum of minimum heeft; polynoom van de derde graad - niet meer dan twee extremen; polynoom van de vierde graad - niet meer dan drie extremen, enz.
In dit geval wordt de trendlijn geconstrueerd in overeenstemming met de vergelijking:
y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6
waarbij de coëfficiënten c0, c1, c2,... c6 constanten zijn waarvan de waarden tijdens de constructie worden bepaald.
3 . De logaritmische trendlijn wordt met succes gebruikt bij het modelleren van kenmerken waarvan de waarden aanvankelijk snel veranderen en vervolgens geleidelijk stabiliseren.
Gebouwd in overeenstemming met de vergelijking:
y = c ln(x) + b
4 . Een trendlijn volgens de machtswet geeft goede resultaten als de waarden van de onderzochte relatie worden gekenmerkt door een constante verandering in de groeisnelheid. Een voorbeeld van een dergelijke afhankelijkheid is de grafiek van de uniform versnelde beweging van een auto. Als er nul- of negatieve waarden in de gegevens voorkomen, kunt u geen machtstrendlijn gebruiken.
Gebouwd in overeenstemming met de vergelijking:
y = cxb
waarbij coëfficiënten b, c constanten zijn.
5 . Er moet een exponentiële trendlijn worden gebruikt wanneer de snelheid waarmee de gegevens veranderen voortdurend toeneemt. Voor gegevens die nul of negatieve waarden bevatten, is dit type benadering ook niet van toepassing.
Gebouwd in overeenstemming met de vergelijking:
y = c ebx
waarbij coëfficiënten b, c constanten zijn.
Bij het selecteren van een trendlijn berekent Excel automatisch de waarde van R2, wat de betrouwbaarheid van de benadering kenmerkt: hoe dichter de R2-waarde bij de eenheid ligt, des te betrouwbaarder benadert de trendlijn het onderzochte proces. Indien nodig kan de R2-waarde altijd op de kaart worden weergegeven.
Bepaald door de formule:
Een trendlijn aan een gegevensreeks toevoegen:
- activeer een diagram op basis van een reeks gegevens, d.w.z. klik binnen het diagramgebied. Het Diagram-item verschijnt in het hoofdmenu;
- nadat u op dit item heeft geklikt, verschijnt er een menu op het scherm waarin u het commando Trendlijn toevoegen selecteert.
Dezelfde acties kunnen eenvoudig worden geïmplementeerd door de muisaanwijzer over de grafiek te bewegen die overeenkomt met een van de gegevensreeksen en met de rechtermuisknop te klikken; In het contextmenu dat verschijnt, selecteert u de opdracht Trendlijn toevoegen. Het dialoogvenster Trendlijn verschijnt op het scherm met het tabblad Type geopend (Fig. 1).
Hierna heb je nodig:
Selecteer het gewenste trendlijntype op het tabblad Type (standaard is het lineaire type geselecteerd). Geef voor het type Polynoom in het veld Graad de graad van de geselecteerde polynoom op.
1 . In het veld Op serie gebouwd worden alle gegevensreeksen in het betreffende diagram weergegeven. Als u een trendlijn aan een specifieke gegevensreeks wilt toevoegen, selecteert u de naam ervan in het veld Gebouwd op reeks.
Indien nodig kunt u, door naar het tabblad Parameters (Fig. 2) te gaan, de volgende parameters voor de trendlijn instellen:
- wijzig de naam van de trendlijn in het veld Naam van de benaderende (afgevlakte) curve.
- stel het aantal perioden (vooruit of achteruit) in voor de prognose in het veld Prognose;
- geef de vergelijking van de trendlijn weer in het grafiekgebied, waarvoor u het selectievakje “toon vergelijking op grafiek” moet inschakelen;
- geef de geschatte betrouwbaarheidswaarde R2 weer in het diagramgebied, waarvoor u het selectievakje Plaats de geschatte betrouwbaarheidswaarde in het diagram (R^2) moet inschakelen;
- stel het snijpunt van de trendlijn met de Y-as in, waarvoor u het selectievakje voor het snijpunt van de curve met de Y-as op een punt moet inschakelen;
- Klik op de knop OK om het dialoogvenster te sluiten.
Er zijn drie manieren om een reeds getekende trendlijn te bewerken:
gebruik de opdracht Geselecteerde trendlijn in het menu Opmaak, nadat u eerder de trendlijn hebt geselecteerd;Het dialoogvenster Trendlijnopmaak verschijnt op het scherm (Fig. 3) en bevat drie tabbladen: Weergave, Type, Parameters, en de inhoud van de laatste twee komt volledig overeen met de vergelijkbare tabbladen van het dialoogvenster Trendlijn (Fig. 1). -2). Op het tabblad Weergave kunt u het lijntype, de kleur en de dikte instellen.
Om een trendlijn te verwijderen die al is getekend, selecteert u de trendlijn die u wilt verwijderen en drukt u op de Delete-toets.
De voordelen van de weloverwogen regressieanalysetool zijn:
- het relatieve gemak van het construeren van een trendlijn in grafieken zonder er een gegevenstabel voor te maken;
- een vrij brede lijst met soorten voorgestelde trendlijnen, en deze lijst bevat de meest gebruikte soorten regressie;
- het vermogen om het gedrag van het onderzochte proces te voorspellen door een willekeurig (binnen de grenzen van het gezond verstand) aantal stappen vooruit en ook achteruit;
- het vermogen om de trendlijnvergelijking in analytische vorm te verkrijgen;
- de mogelijkheid om, indien nodig, een beoordeling te verkrijgen van de betrouwbaarheid van de benadering.
De nadelen zijn onder meer:
de constructie van een trendlijn wordt alleen uitgevoerd als er een diagram is gebouwd op een reeks gegevens;Trendlijnen kunnen worden gebruikt als aanvulling op gegevensreeksen die worden gepresenteerd in diagrammen, zoals grafieken, histogrammen, platte, niet-gestandaardiseerde vlakdiagrammen, staafdiagrammen, spreidingsdiagrammen, bellendiagrammen en aandelengrafieken.
U kunt geen trendlijnen toevoegen aan gegevensreeksen in 3D-, genormaliseerde-, radar-, cirkel- en ringdiagrammen.
De ingebouwde functies van Excel gebruiken
Excel heeft ook een regressieanalysetool voor het uitzetten van trendlijnen buiten het grafiekgebied. Er zijn een aantal statistische werkbladfuncties die voor dit doel kunnen worden gebruikt, maar deze maken allemaal alleen lineaire of exponentiële regressies mogelijk.
Excel heeft verschillende functies voor het construeren van lineaire regressie, met name:
- TREND;
- LIJNSCH;
- HELLING en SNIJDEN.
Evenals verschillende functies voor het construeren van een exponentiële trendlijn, in het bijzonder:
- HOOGTE;
- LGRFPRIBL.
Opgemerkt moet worden dat de technieken voor het construeren van regressies met behulp van de functies TREND en GROWTH vrijwel hetzelfde zijn. Hetzelfde kan gezegd worden over het functiepaar LIJNSCH en LGRFPRIBL. Voor deze vier functies wordt bij het maken van een waardentabel gebruik gemaakt van Excel-functies zoals matrixformules, wat het proces van het bouwen van regressies enigszins vertroebelt. Merk ook op dat de constructie van lineaire regressie naar onze mening het gemakkelijkst kan worden bereikt met behulp van de functies SLOPE en INTERCEPT, waarbij de eerste de helling van de lineaire regressie bepaalt, en de tweede het segment bepaalt dat wordt onderschept door de regressie op de y. -as.
De voordelen van de ingebouwde functietool voor regressieanalyse zijn:
- een vrij eenvoudig, uniform proces voor het genereren van gegevensreeksen van het onderzochte kenmerk voor alle ingebouwde statistische functies die trendlijnen definiëren;
- standaardmethodologie voor het construeren van trendlijnen op basis van gegenereerde gegevensreeksen;
- het vermogen om het gedrag van het onderzochte proces te voorspellen door het vereiste aantal stappen vooruit of achteruit.
De nadelen zijn onder meer het feit dat Excel geen ingebouwde functies heeft voor het maken van andere (behalve lineaire en exponentiële) typen trendlijnen. Deze omstandigheid maakt het vaak niet mogelijk om een voldoende nauwkeurig model van het onderzochte proces te kiezen, en ook om voorspellingen te verkrijgen die dicht bij de werkelijkheid liggen. Bovendien zijn bij gebruik van de functies TREND en GROWTH de vergelijkingen van de trendlijnen niet bekend.
Opgemerkt moet worden dat het niet de bedoeling van de auteurs was om het verloop van de regressieanalyse met enige mate van volledigheid weer te geven. De belangrijkste taak ervan is om aan de hand van specifieke voorbeelden de mogelijkheden van het Excel-pakket te laten zien bij het oplossen van benaderingsproblemen; demonstreren welke effectieve tools Excel heeft voor het bouwen van regressies en prognoses; illustreren hoe dergelijke problemen relatief eenvoudig kunnen worden opgelost, zelfs door een gebruiker die geen uitgebreide kennis van regressieanalyse heeft.
Voorbeelden van het oplossen van specifieke problemen
Laten we eens kijken naar het oplossen van specifieke problemen met behulp van de genoemde Excel-hulpmiddelen.
Probleem 1
Met een tabel met gegevens over de winst van een autotransportbedrijf voor de periode 1995-2002. je moet het volgende doen:
- Bouw een diagram.
- Voeg lineaire en polynomiale (kwadratische en kubieke) trendlijnen toe aan het diagram.
- Gebruik de vergelijkingen van de trendlijnen om tabelgegevens te verkrijgen over de bedrijfswinsten voor elke trendlijn voor de periode 1995-2004.
- Maak een prognose van de winst van de onderneming voor 2003 en 2004.
Probleem oplossing
- Voer in het celbereik A4:C11 van het Excel-werkblad het werkblad in dat wordt weergegeven in Afb. 4.
- Nadat we het cellenbereik B4:C11 hebben geselecteerd, bouwen we een diagram.
- We activeren het geconstrueerde diagram en voegen, volgens de hierboven beschreven methode, na het selecteren van het type trendlijn in het dialoogvenster Trendlijn (zie figuur 1), afwisselend lineaire, kwadratische en kubieke trendlijnen toe aan het diagram. Open in hetzelfde dialoogvenster het tabblad Parameters (zie figuur 2), voer in het veld Naam van de benaderende (afgevlakte) curve de naam in van de trend die wordt toegevoegd, en stel in het veld Voorspelling voor: perioden de waarde in waarde 2, aangezien het de bedoeling is om een winstvoorspelling voor twee jaar vooruit te maken. Om de regressievergelijking en de betrouwbaarheidswaarde R2 van de benadering in het diagramgebied weer te geven, schakelt u de selectievakjes Vergelijking weergeven op het scherm in en plaatst u de betrouwbaarheidswaarde van de benadering (R^2) in het diagram. Voor een betere visuele waarneming veranderen we het type, de kleur en de dikte van de geconstrueerde trendlijnen, waarvoor we het tabblad Weergave van het dialoogvenster Trendlijnopmaak gebruiken (zie figuur 3). Het resulterende diagram met toegevoegde trendlijnen wordt getoond in Fig. 5.
- Om tabelgegevens te verkrijgen over de bedrijfswinsten voor elke trendlijn voor 1995-2004. Laten we de trendlijnvergelijkingen gebruiken die in Fig. 5. Voer hiervoor in de cellen van het bereik D3:F3 tekstinformatie in over het type van de geselecteerde trendlijn: Lineaire trend, Kwadratische trend, Kubieke trend. Voer vervolgens de lineaire regressieformule in cel D4 in en kopieer deze formule met behulp van de vulmarkering met relatieve verwijzingen naar het celbereik D5:D13. Opgemerkt moet worden dat elke cel met een lineaire regressieformule uit het celbereik D4:D13 als argument een corresponderende cel uit het bereik A4:A13 heeft. Op dezelfde manier vult u voor kwadratische regressie het celbereik E4:E13 in, en voor kubieke regressie het celbereik F4:F13. Zo is een prognose voor de winst van de onderneming voor 2003 en 2004 opgesteld. gebruikmakend van drie trends. De resulterende waardentabel wordt getoond in Fig. 6.
Probleem 2
- Bouw een diagram.
- Voeg logaritmische, machts- en exponentiële trendlijnen toe aan de grafiek.
- Leid de vergelijkingen af van de verkregen trendlijnen, evenals de betrouwbaarheidswaarden van de benadering R2 voor elk ervan.
- Gebruik de trendlijnvergelijkingen om tabelgegevens te verkrijgen over de winst van de onderneming voor elke trendlijn voor 1995-2002.
- Maak aan de hand van deze trendlijnen een prognose van de winst van het bedrijf voor 2003 en 2004.
Probleem oplossing
Door de methodologie te volgen die is gegeven bij het oplossen van probleem 1, krijgen we een diagram waaraan logaritmische, machts- en exponentiële trendlijnen zijn toegevoegd (Fig. 7). Vervolgens vullen we met behulp van de verkregen trendlijnvergelijkingen een tabel met waarden voor de winst van de onderneming in, inclusief de voorspelde waarden voor 2003 en 2004. (Afb. 8).
In afb. 5 en afb. Het is duidelijk dat het model met een logaritmische trend overeenkomt met de laagste waarde van de betrouwbaarheid van de benadering
R2 = 0,8659
De hoogste waarden van R2 komen overeen met modellen met een polynomiale trend: kwadratisch (R2 = 0,9263) en kubisch (R2 = 0,933).
Probleem 3
Met de tabel met gegevens over de winst van een autovervoerbedrijf voor de periode 1995-2002, gegeven in taak 1, moet u de volgende stappen uitvoeren.
- Verkrijg gegevensreeksen voor lineaire en exponentiële trendlijnen met behulp van de TREND- en GROW-functies.
- Maak met behulp van de functies TREND en GROEI een voorspelling van de winst van de onderneming voor 2003 en 2004.
- Maak een diagram voor de oorspronkelijke gegevens en de resulterende gegevensreeksen.
Probleem oplossing
Laten we het werkblad voor probleem 1 gebruiken (zie figuur 4). Laten we beginnen met de TREND-functie:
- selecteer het cellenbereik D4:D11, dat moet worden gevuld met de waarden van de TREND-functie die overeenkomen met de bekende gegevens over de winst van de onderneming;
- Roep de opdracht Functie op vanuit het menu Invoegen. In het dialoogvenster Functiewizard dat verschijnt, selecteert u de TREND-functie in de categorie Statistisch en klikt u vervolgens op de knop OK. Dezelfde bewerking kan worden uitgevoerd door op de knop (Functie invoegen) op de standaardwerkbalk te klikken.
- Voer in het dialoogvenster Functieargumenten dat verschijnt het celbereik C4:C11 in het veld Bekende_waarden_y in; in het veld Known_values_x - het celbereik B4:B11;
- Om van de ingevoerde formule een matrixformule te maken, gebruikt u de toetsencombinatie + + .
De formule die we in de formulebalk hebben ingevoerd, ziet er als volgt uit: =(TREND(C4:C11,B4:B11)).
Als gevolg hiervan wordt het cellenbereik D4:D11 gevuld met de overeenkomstige waarden van de TREND-functie (Fig. 9).
Een prognose maken van de winst van de onderneming voor 2003 en 2004. nodig:
- selecteer het cellenbereik D12:D13 waar de waarden die worden voorspeld door de TREND-functie zullen worden ingevoerd.
- roep de TREND-functie aan en voer in het dialoogvenster Functieargumenten dat verschijnt in het veld Known_values_y het celbereik C4:C11 in; in het veld Known_values_x - het celbereik B4:B11; en in het veld Nieuwe_waarden_x - het celbereik B12:B13.
- verander deze formule in een matrixformule met de toetsencombinatie Ctrl + Shift + Enter.
- De ingevoerde formule ziet er als volgt uit: =(TREND(C4:C11;B4:B11;B12:B13)), en het celbereik D12:D13 wordt gevuld met de voorspelde waarden van de TREND-functie (zie Fig. 9).
De gegevensreeks wordt op dezelfde manier ingevuld met behulp van de GROWTH-functie, die wordt gebruikt bij de analyse van niet-lineaire afhankelijkheden en op precies dezelfde manier werkt als zijn lineaire tegenhanger TREND.
Figuur 10 toont de tabel in de formuleweergavemodus.
Voor de initiële gegevens en de verkregen gegevensreeksen wordt het diagram weergegeven in Fig. 11.
Probleem 4
Met de gegevenstabel over de ontvangst van dienstenaanvragen door de expeditiedienst van een motortransportbedrijf voor de periode van de 1e tot de 11e van de lopende maand, moet u de volgende acties uitvoeren.
- Gegevensreeksen ophalen voor lineaire regressie:gebruik van de SLOPE- en CUT-functies; met behulp van de LIJNSCH-functie.
- Verkrijg een gegevensreeks voor exponentiële regressie met behulp van de LGRFPRIBL-functie.
- Maak met behulp van de bovenstaande functies een voorspelling over de ontvangst van aanvragen bij de verzendservice voor de periode van de 12e tot de 14e van de huidige maand.
- Maak een diagram voor de originele en ontvangen gegevensreeksen.
Probleem oplossing
Merk op dat, in tegenstelling tot de functies TREND en GROEI, geen van de hierboven genoemde functies (HELLING, INTERCEPT, LIJNSCH, LGRFPRIB) regressie is. Deze functies spelen slechts een ondersteunende rol en bepalen de noodzakelijke regressieparameters.
Voor lineaire en exponentiële regressies die zijn opgebouwd met behulp van de functies HELLING, INTERCEPT, LIJNSCH, LGRFPRIB, is het uiterlijk van hun vergelijkingen altijd bekend, in tegenstelling tot lineaire en exponentiële regressies die overeenkomen met de functies TREND en GROEI.
1 . Laten we een lineaire regressie bouwen met de vergelijking:
y = mx+b
met behulp van de functies SLOPE en INTERCEPT, waarbij de regressiehelling m wordt bepaald door de functie SLOPE, en de vrije term b door de functie INTERCEPT.
Om dit te doen, voeren wij de volgende acties uit:
- voer de originele tabel in het celbereik A4:B14 in;
- de waarde van parameter m wordt bepaald in cel C19. Selecteer de functie Helling in de categorie Statistisch; voer het celbereik B4:B14 in het veld bekende_waarden_y in en het celbereik A4:A14 in het veld bekende_waarden_x. De formule wordt ingevoerd in cel C19: =HELLING(B4:B14,A4:A14);
- Met behulp van een vergelijkbare techniek wordt de waarde van parameter b in cel D19 bepaald. En de inhoud zal er als volgt uitzien: =SEGMENT(B4:B14,A4:A14).De waarden van de parameters m en b die nodig zijn voor het construeren van een lineaire regressie zullen dus respectievelijk worden opgeslagen in de cellen C19, D19;
- Voer vervolgens de lineaire regressieformule in cel C4 in de vorm in: =$C*A4+$D. In deze formule worden de cellen C19 en D19 geschreven met absolute verwijzingen (het celadres mag niet veranderen tijdens mogelijk kopiëren). Het absolute verwijzingsteken $ kan vanaf het toetsenbord worden getypt of met behulp van de F4-toets, nadat u de cursor op het celadres hebt geplaatst. Kopieer deze formule met behulp van de vulgreep naar het celbereik C4:C17. We verkrijgen de vereiste gegevensreeksen (Fig. 12). Omdat het aantal aanvragen een geheel getal is, moet u het getalformaat met het aantal decimalen op 0 instellen op het tabblad Getal van het venster Celformaat.
2 . Laten we nu een lineaire regressie bouwen, gegeven door de vergelijking:
y = mx+b
met behulp van de LIJNSCH-functie.
Om dit te doen:
- Voer de LIJNSCH-functie in als matrixformule in het celbereik C20:D20: =(LIJNSCH(B4:B14,A4:A14)). Als resultaat verkrijgen we de waarde van parameter m in cel C20, en de waarde van parameter b in cel D20;
- voer de formule in cel D4 in: =$C*A4+$D;
- kopieer deze formule met behulp van de vulmarkering naar het celbereik D4:D17 en verkrijg de gewenste gegevensreeks.
3 . We bouwen een exponentiële regressie met de vergelijking:
y = bmx
met behulp van de LGRFPRIBL-functie wordt het op dezelfde manier uitgevoerd:
In het celbereik C21:D21 voeren we de LGRFPRIBL-functie in als een matrixformule: =( LGRFPRIBL (B4:B14,A4:A14)). In dit geval wordt de waarde van parameter m bepaald in cel C21, en wordt de waarde van parameter b bepaald in cel D21;In afb. Figuur 13 toont een tabel waarin u de functies kunt zien die we gebruiken met de vereiste celbereiken, evenals formules.
Voor de initiële gegevens en de verkregen gegevensreeksen wordt het diagram weergegeven in Fig. 14.
Diagrammen en grafieken worden gebruikt om numerieke gegevens te analyseren, bijvoorbeeld om de relatie tussen twee soorten waarden te evalueren. Voor dit doel kunnen een trendlijn en de bijbehorende vergelijking, voorspelde waarden die voor meerdere perioden vooruit of achteruit zijn berekend, worden toegevoegd aan de gegevens in een diagram of grafiek.
Trendlijn vertegenwoordigt een rechte of gebogen lijn die de oorspronkelijke gegevens benadert (dichterbij brengt) op basis van een regressievergelijking of voortschrijdend gemiddelde. De benadering wordt bepaald met behulp van de kleinste kwadratenmethode. Afhankelijk van de aard van het gedrag van de brongegevens (dalend, stijgend, etc.) wordt een interpolatiemethode geselecteerd die moet worden gebruikt om een trend op te bouwen.
Er zijn verschillende opties om een trendlijn te vormen.
Lineaire functie: y=mx+b
waarbij m de raaklijn is van de hellingshoek van de rechte lijn, b de verplaatsing.
Een rechte trendlijn (lineaire trend) is het meest geschikt voor hoeveelheden die met een constante snelheid veranderen. Wordt gebruikt in gevallen waarin gegevenspunten zich dicht bij een rechte lijn bevinden.
Logaritmische functie: y=c*lnx+b
waarbij c en b constanten zijn.
Een logaritmische trendlijn komt overeen met een gegevensreeks waarvan de waarden aanvankelijk snel stijgen of dalen en vervolgens geleidelijk stabiliseren. Kan worden gebruikt voor positieve en negatieve gegevens.
Polynoomfunctie (tot en met de 6e graad): y= b + c 1 *x + c 2 *x 2 + c 3 *x 3 + ...+ c 6* x 6
waarbij b, c 1, c 2, ... c 6 constanten zijn.
Een polynomiale trendlijn wordt gebruikt om afwisselend stijgende en dalende gegevens te beschrijven. De graad van de polynoom wordt zo gekozen dat deze één groter is dan het aantal extremen (maxima en minima) van de curve.
Vermogensfunctie: y = cxb
waarbij c en b constanten zijn.
Een trendlijn volgens de machtswet geeft goede resultaten voor positieve gegevens met constante versnelling. Voor reeksen met nul- of negatieve waarden is de constructie van de opgegeven trendlijn onmogelijk.
Exponentiële functie: y = cebx
waar c en b constanten zijn, is e de basis van de natuurlijke logaritme.
Exponentiële trend wordt gebruikt wanneer de verandering in gegevens voortdurend toeneemt. Het construeren van de aangegeven trend is onmogelijk als de reeks waarden van de reeksleden nul of negatieve gegevens bevat.
Lineair filteren volgens de formule: F t = (A t +A (t-1) +⋯+A (t-n+1))/n
waarbij n het totale aantal leden van de reeks is, is t het gegeven aantal punten (2 ≤ t< n).
Met een trend met lineaire filtering kunt u gegevensfluctuaties gladstrijken, waardoor de aard van de afhankelijkheden duidelijk wordt aangetoond. Om de opgegeven trendlijn op te bouwen, moet de gebruiker een getal opgeven: een filterparameter. Als het getal 2 is, wordt het eerste punt van de trendlijn gedefinieerd als het gemiddelde van de eerste twee gegevensitems, het tweede punt als het gemiddelde van de tweede en derde gegevensitems, enz.
Voor sommige typen diagrammen kan in principe geen trendlijn worden geconstrueerd: gestapelde diagrammen, volumetrische diagrammen, radardiagrammen, cirkeldiagrammen, oppervlaktediagrammen en ringdiagrammen. Indien mogelijk kunt u meerdere regels met verschillende parameters aan het diagram toevoegen. De overeenkomst van de trendlijn met de werkelijke waarden van de gegevensreeksen wordt vastgesteld met behulp van de betrouwbaarheidscoëfficiënt van de benadering:
De trendlijn en de bijbehorende parameters worden aan de grafiekgegevens toegevoegd met behulp van de volgende opdrachten:
Indien nodig kunt u de lijnparameters wijzigen door op een rij met grafiekgegevens of een trendlijn te klikken om het venster Trendlijnopmaak te openen. U kunt een regressievergelijking en een betrouwbaarheidscoëfficiënt voor de benadering toevoegen (of verwijderen), de richting en voorspelling van veranderingen in een gegevensreeks bepalen en ook de ontwerpelementen van de trendlijn corrigeren. De geselecteerde trendlijn kan ook worden verwijderd.
De figuur toont een tabel met gegevens over veranderingen in de waarde van een effect. Op basis van deze voorwaardelijke gegevens werd een spreidingsdiagram gemaakt, een polynomiale trendlijn van de derde orde (gespecificeerd door een stippellijn) en enkele andere parameters toegevoegd. De verkregen waarde van de betrouwbaarheidscoëfficiënt R2 van de benadering in het diagram ligt dicht bij de eenheid, wat aangeeft hoe dicht de berekende trendlijn bij de probleemgegevens ligt. De voorspelde waarde van veranderingen in de waarde van een effect is gericht op groei.
Doel van de dienst. De service wordt gebruikt om de trendparameters van de tijdreeks y t online te berekenen met behulp van de kleinste kwadratenmethode (LS) (zie het voorbeeld van het vinden van de trendvergelijking), evenals de methode vanaf voorwaardelijk nul. Hiervoor wordt een systeem van vergelijkingen geconstrueerd:een 0 n + een 1 ∑t = ∑y
een 0 ∑t + een 1 ∑t 2 = ∑y t
en een tabel als deze:
T | j | t 2 | j 2 | t j | jij(t) |
1 | |||||
... | ... | ... | ... | ... | ... |
N | |||||
TOTAAL | ∑ | ∑ | ∑ | ∑ | ∑ |
Instructies. Geef de hoeveelheid gegevens op (aantal rijen). De resulterende oplossing wordt opgeslagen in een Word- en Excel-bestand.
De trend van een tijdreeks karakteriseert een reeks factoren die een langetermijninvloed hebben en de algehele dynamiek vormen van de indicator die wordt bestudeerd.
Methode voor het tellen van de tijd vanaf een voorwaardelijke start
Om de parameters van een wiskundige functie te bepalen bij het analyseren van een trend in tijdreeksen, wordt een methode gebruikt om de tijd te tellen vanaf een voorwaardelijk begin. Het is gebaseerd op de notatie in de tijdreeks, zodat ∑t i . In dit geval wordt in een reeks dynamieken met een oneven aantal niveaus het serienummer van het niveau dat zich in het midden van de reeks bevindt, aangegeven met een nulwaarde en wordt dit genomen als het voorwaardelijke begin van de tijdtelling met een interval van +1 van alle volgende niveaus en –1 van alle voorgaande niveaus. Bij het aangeven van de tijd zal er bijvoorbeeld zijn: –2, –1, 0, +1, +2. Bij een even aantal niveaus worden de serienummers van de bovenste helft van de rij (vanuit het midden) aangegeven met de cijfers: –1, –3, –5, en de onderste helft van de rij wordt aangeduid met +1, + 3, +5.Voorbeeld. Statistische studie van bevolkingsdynamiek.
- Gebruik ketting-, basis- en gemiddelde dynamiekindicatoren om de verandering in cijfers te evalueren en uw conclusies op te schrijven.
- Met behulp van de methode van analytische uitlijning (rechte lijn en parabool, bepaling van de coëfficiënten met behulp van OLS), identificeert u de belangrijkste trend in de ontwikkeling van het fenomeen (bevolking van de Republiek Komi). Evalueer de kwaliteit van de resulterende modellen met behulp van fouten en benaderingscoëfficiënten.
- Bepaal lineaire en parabolische trendcoëfficiënten met behulp van de Grafiekwizard. Geef punt- en intervalpopulatievoorspellingen voor 2010. Schrijf uw conclusies op.
1990 | 1996 | 2001 | 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 |
1249 | 1133 | 1043 | 1030 | 1016 | 1005 | 996 | 985 | 975 | 968 |
a) De lineaire trendvergelijking heeft de vorm y = bt + a
1. Zoek de parameters van de vergelijking met behulp van de kleinste kwadratenmethode. We gebruiken de methode om de tijd te tellen vanaf een voorwaardelijk begin.
Het systeem van kleinste kwadratenvergelijkingen voor een lineaire trend heeft de vorm:
een 0 n + een 1 ∑t = ∑y
een 0 ∑t + een 1 ∑t 2 = ∑y t
T | j | t 2 | j 2 | t j |
-9 | 1249 | 81 | 1560001 | -11241 |
-7 | 1133 | 49 | 1283689 | -7931 |
-5 | 1043 | 25 | 1087849 | -5215 |
-3 | 1030 | 9 | 1060900 | -3090 |
-1 | 1016 | 1 | 1032256 | -1016 |
1 | 1005 | 1 | 1010025 | 1005 |
3 | 996 | 9 | 992016 | 2988 |
5 | 985 | 25 | 970225 | 4925 |
7 | 975 | 49 | 950625 | 6825 |
9 | 968 | 81 | 937024 | 8712 |
0 | 10400 | 330 | 10884610 | -4038 |
Voor onze gegevens zal het stelsel vergelijkingen de vorm aannemen:
10a 0 + 0a 1 = 10400
0a 0 + 330a 1 = -4038
Uit de eerste vergelijking drukken we een 0 uit en vervangen deze door de tweede vergelijking
We krijgen een 0 = -12,236, een 1 = 1040
Trendvergelijking:
y = -12,236 t + 1040
Laten we de kwaliteit van de trendvergelijking evalueren met behulp van de absolute benaderingsfout.
De benaderingsfout binnen 5%-7% geeft aan dat de trendvergelijking goed aansluit bij de oorspronkelijke gegevens.
b) parabolische uitlijning
De trendvergelijking is y = bij 2 + bt + c
1. Zoek de parameters van de vergelijking met behulp van de kleinste kwadratenmethode.
Systeem van vergelijkingen van de kleinste kwadraten:
een 0 n + een 1 ∑t + een 2 ∑t 2 = ∑y
een 0 ∑t + een 1 ∑t 2 + een 2 ∑t 3 = ∑yt
een 0 ∑t 2 + een 1 ∑t 3 + een 2 ∑t 4 = ∑yt 2
T | j | t 2 | j 2 | t j | t 3 | t 4 | t 2 j |
-9 | 1249 | 81 | 1560001 | -11241 | -729 | 6561 | 101169 |
-7 | 1133 | 49 | 1283689 | -7931 | -343 | 2401 | 55517 |
-5 | 1043 | 25 | 1087849 | -5215 | -125 | 625 | 26075 |
-3 | 1030 | 9 | 1060900 | -3090 | -27 | 81 | 9270 |
-1 | 1016 | 1 | 1032256 | -1016 | -1 | 1 | 1016 |
1 | 1005 | 1 | 1010025 | 1005 | 1 | 1 | 1005 |
3 | 996 | 9 | 992016 | 2988 | 27 | 81 | 8964 |
5 | 985 | 25 | 970225 | 4925 | 125 | 625 | 24625 |
7 | 975 | 49 | 950625 | 6825 | 343 | 2401 | 47775 |
9 | 968 | 81 | 937024 | 8712 | 729 | 6561 | 78408 |
0 | 10400 | 330 | 10884610 | -4038 | 0 | 19338 | 353824 |
Voor onze gegevens heeft het stelsel vergelijkingen de vorm
10a 0 + 0a 1 + 330a 2 = 10400
0a 0 + 330a 1 + 0a 2 = -4038
330a 0 + 0a 1 + 19338a 2 = 353824
We krijgen een 0 = 1,258, een 1 = -12,236, een 2 = 998,5
Trendvergelijking:
y = 1,258t 2 -12,236t+998,5
Benaderingsfout voor de parabolische trendvergelijking.
Omdat de fout minder dan 7% bedraagt, kan deze vergelijking als trend worden gebruikt.
Minimale benaderingsfout voor parabolische uitlijning. Bovendien is de determinatiecoëfficiënt R2 hoger dan bij lineair. Daarom is het noodzakelijk om een parabolische vergelijking te gebruiken voor het voorspellen.
Intervalvoorspelling.
Laten we de wortel van de gemiddelde kwadratische fout van de voorspelde indicator bepalen.
m = 1 - het aantal beïnvloedende factoren in de trendvergelijking.
Uy = y n+L ± K
Waar
L - doorloopperiode; y n+L - puntvoorspelling volgens het model op het (n + L)-de tijdstip; n is het aantal waarnemingen in de tijdreeks; Sy is de standaardfout van de voorspelde indicator; Tabblad T - tabelwaarde van de studententest voor het significantieniveau α en voor het aantal vrijheidsgraden gelijk aan n-2.
Met behulp van de studententabel vinden we Ttable
T-tabel (n-m-1;α/2) = (8;0,025) = 2,306
Puntvoorspelling, t = 10: y(10) = 1,26*10 2 -12,24*10 + 998,5 = 1001,89 duizend mensen.
1001.89 - 71.13 = 930.76 ; 1001.89 + 71.13 = 1073.02
Intervalvoorspelling:
t = 9+1 = 10: (930,76;1073,02)
Grafieken
Regressieanalyse
Regressievergelijking Y van X functionele afhankelijkheid genoemd y=f(x), en de grafiek is een regressielijn.
Met Excel kunt u diagrammen en grafieken van redelijk acceptabele kwaliteit maken. Excel heeft een speciaal hulpmiddel: de Grafiekwizard, onder wiens begeleiding de gebruiker alle vier fasen van het proces van het construeren van een diagram of grafiek doorloopt.
In de regel begint het plotten met het selecteren van een bereik dat de gegevens bevat waarop het moet worden geplot. Deze start vereenvoudigt het verdere verloop van het plotten. Het bereik met de originele gegevens kan echter in de tweede fase van de dialoog worden verdeeld DIAGRAMMEESTER. In Excel2003 DIAGRAMMEESTER bevindt zich in het menu als een knop of er kan een diagram worden gemaakt door op het tabblad te klikken INVOEGEN en zoek het item in de lijst die wordt geopend DIAGRAM. In Excel 2007 vinden we het tabblad ook INVOEGEN(Afb. 31).
Rijst. 31. DIAGRAMMEESTER in Excel2007
De eenvoudigste manier is om een reeks brongegevens te selecteren waarin deze gegevens zich in aangrenzende rijen (kolommen of rijen) bevinden. U moet op de cel linksboven van het bereik klikken en vervolgens de muisaanwijzer naar de cel rechtsonder slepen. het bereik. Wanneer u gegevens selecteert die zich in niet-aangrenzende rijen bevinden, sleept u de muisaanwijzer langs de geselecteerde rijen terwijl u de Ctrl-toets ingedrukt houdt. Als een van de gegevensreeksen een cel met een titel heeft, moeten de overige geselecteerde reeksen ook een overeenkomstige cel hebben, zelfs als deze leeg is.
Voor het uitvoeren van een regressieanalyse kunt u het beste een spreidingsdiagram gebruiken (Fig. 30). Bij het bouwen ervan beschouwt Excel de eerste rij van het geselecteerde bereik met brongegevens als een reeks argumentwaarden van de functies waarvan de grafieken moeten worden geplot (dezelfde set voor alle functies). De volgende rijen worden gezien als sets waarden van de functies zelf (elke rij bevat de waarden van een van de functies die overeenkomen met de opgegeven argumentwaarden in de eerste rij van het geselecteerde bereik).
In Excel 2007 worden de asnamen op het menutabblad geplaatst INDELING(Afb. 32).
Rijst. 32. De namen van grafiekassen instellen in Excel 2007
Om een wiskundig model te verkrijgen, is het noodzakelijk om een trendlijn in de grafiek te tekenen. In Excel 2003 en 2007 moet u met de rechtermuisknop op de grafiekpunten klikken. Vervolgens verschijnt in Excel 2003 een tabblad met een lijst met items waaruit we selecteren TRENDLIJN TOEVOEGEN(Afb. 33).
Rijst. 33. TRENDLIJN TOEVOEGEN
Nadat u op het artikel heeft geklikt TRENDLIJN TOEVOEGEN er verschijnt een venster TRENDLIJN(Afb. 34). Op het tabblad TYPE kunt u de volgende lijntypen selecteren: lineair, logaritmisch, exponentieel, macht, polynoom, lineair filteren.
Rijst. 34. Venster TRENDLIJN in Excel2003
Op het tabblad PARAMETERS(Fig. 35) vink het vakje aan naast de items TOON VERGELIJKING IN DIAGRAM, waarna een wiskundig model van deze relatie in de grafiek verschijnt. We plaatsen ook een selectievakje naast het item TOON IN HET DIAGRAM DE WAARDE VAN DE BETROUWBAARHEID VAN DE BENADERING (R^2). Hoe dichter de betrouwbaarheidswaarde bij benadering bij 1 ligt, hoe dichter de geselecteerde curve de punten in de grafiek benadert. Klik vervolgens op de knop OK. Een trendlijn, de bijbehorende vergelijking en de betrouwbaarheidswaarde van de benadering verschijnen in de grafiek.
Rijst. 35. Tab PARAMETERS
Nadat we in Excel 2007 met de rechtermuisknop op de grafiekpunten hebben geklikt, verschijnt er een lijst met menu-items waaruit SELECTEER TRENDLIJN TOEVOEGEN(Afb. 36).
Rijst. 36. TRENDLIJN TOEVOEGEN
Rijst. 37. Tab TRENDLIJNPARAMETERS
Vink de gewenste vakjes aan en druk op de knop DICHTBIJ.
Een trendlijn, de bijbehorende vergelijking en de betrouwbaarheidswaarde van de benadering verschijnen in de grafiek.
Wat moet ik doen als er geen timingmetingen zijn voor bepaalde volumes/groottes van producten? Of is het aantal metingen onvoldoende en kunnen er op korte termijn geen aanvullende waarnemingen worden gedaan? De beste manier om dit probleem op te lossen is door berekende afhankelijkheden (regressievergelijkingen) op te bouwen met behulp van trendlijnen in MS Excel.
Laten we eens een reële situatie bekijken: in een magazijn werden timingwaarnemingen uitgevoerd om het bedrag van de arbeidskosten voor het verzamelen van dozen van een bestelling vast te stellen. De resultaten van deze waarnemingen worden weergegeven in Tabel 1 hieronder.
Vervolgens ontstond de behoefte om de tijd te bepalen die nodig was om 0,6 en 0,9 m3 goederen/order te selecteren. Vanwege de onmogelijkheid om aanvullende timingstudies uit te voeren, werd de tijd besteed aan het selecteren van deze ordervolumes berekend met behulp van regressievergelijkingen in MS Excel. Om dit te bereiken is Tabel 1 omgezet naar Tabel 2.
Een spreidingsdiagram selecteren, Afb. 1
Volgende stap: de muiscursor werd op een van de punten op de kaart geplaatst en met de rechtermuisknop werd het contextmenu opgeroepen, waarin het item werd geselecteerd: “trendlijn toevoegen” (Afb. 2).
Een trendlijn toevoegen, afb. 2
In het venster dat verschijnt voor het instellen van het trendlijnformaat (Afb. 3) werden opeenvolgend geselecteerd: lijntype lineair/vermogen en de volgende items werden gecontroleerd: "toon de vergelijking in het diagram" en "plaats de betrouwbaarheidswaarde van de benadering (R ^ 2) in het diagram" (determinatiecoëfficiënt).
Trendlijnformaat, afb. 3
Als resultaat werden de grafieken weergegeven in figuur 1 verkregen. 4 en 5.
Lineair berekende afhankelijkheid, Fig. 4
Machtswet berekende afhankelijkheid, Fig. 5
Een visuele analyse van de grafieken geeft duidelijk de nabijheid van de verkregen afhankelijkheden aan. Bovendien is de betrouwbaarheidswaarde van de benadering (R^2), ook wel de determinatiecoëfficiënt genoemd, bij beide afhankelijkheden dezelfde waarde van 0,97. Het is bekend dat hoe dichter de determinatiecoëfficiënt bij 1 ligt, hoe meer de trendlijn overeenkomt met de werkelijkheid. Ook kan worden gesteld dat 97% van de verandering in de tijd besteed aan orderverwerking wordt verklaard door een verandering in de hoeveelheid goederen. Daarom is het in dit geval niet belangrijk: welke berekende afhankelijkheid moet worden gekozen als de belangrijkste voor de daaropvolgende berekening van tijdkosten.
Laten we de lineair berekende afhankelijkheid als belangrijkste nemen. Vervolgens worden de waarden van de bestede tijd, afhankelijk van de hoeveelheid goederen, bepaald door de formule: y = 54,511x + 0,1489. De resultaten van deze berekeningen voor de hoeveelheid goederen waarvoor eerder timingwaarnemingen werden uitgevoerd, worden weergegeven in Tabel 3 hieronder.
Laten we de gemiddelde afwijking bepalen van de bestede tijd, berekend met behulp van de regressievergelijking, van de bestede tijd berekend op basis van tijdwaarnemingen: (-0,05+0,10-0,05+0,01)/4=0,0019. De tijdsbesteding berekend met behulp van de regressievergelijking verschilt dus slechts met de tijdsbesteding berekend op basis van tijdregistratiegegevens 0,19%. Het verschil tussen de gegevens is verwaarloosbaar.
Met behulp van de formule: y = 54,511x + 0,1489 bepalen we de tijdskosten voor de hoeveelheid goederen waarvoor nog niet eerder timingwaarnemingen zijn uitgevoerd (Tabel 4).
Dus berekende afhankelijkheden construeren met behulp van trendlijnen in MS Excel - Dit is een uitstekende manier om de tijd vast te stellen die is besteed aan handelingen die om verschillende redenen niet in de tijdwaarnemingen zijn opgenomen.