Hoe u een roc-curve in excel maakt (stap voor stap)

Logistische regressie is een statistische methode die we gebruiken om een regressiemodel te fitten wanneer de responsvariabele binair is. Om te evalueren hoe goed een logistisch regressiemodel bij een dataset past, kunnen we naar de volgende twee statistieken kijken:

• Gevoeligheid: waarschijnlijkheid dat het model een positief resultaat voorspelt voor een waarneming terwijl het resultaat daadwerkelijk positief is. Dit wordt ook wel het “echte positieve percentage” genoemd.
• Specificiteit: de kans dat het model een negatief resultaat voorspelt voor een waarneming terwijl het resultaat feitelijk negatief is. Dit wordt ook wel het ‘echte negatieve tarief’ genoemd.

Eén manier om deze twee metingen te visualiseren is door een ROC-curve te maken, wat staat voor ‘receiver operating karakteristieke’-curve. Dit is een grafiek die de gevoeligheid en specificiteit van een logistisch regressiemodel weergeeft.

Het volgende stapsgewijze voorbeeld laat zien hoe u een ROC-curve in Excel kunt maken en interpreteren.

Stap 1: Voer de gegevens in

Laten we beginnen met het invoeren van enkele onbewerkte gegevens:

Stap 2: Bereken cumulatieve gegevens

Laten we vervolgens de volgende formule gebruiken om de cumulatieve waarden voor de categorieën Pass en Fail te berekenen:

• Cumulatieve succeswaarden: =SOM($B$3:B3)
• Cumulatieve foutwaarden: =SUM($C$3:C3)

Vervolgens kopiëren en plakken we deze formules in elke cel in kolom D en kolom E:

Stap 3: Bereken het fout-positieve percentage en het echt-positieve percentage

Vervolgens berekenen we het fout-positieve percentage (FPR), het echt-positieve percentage (TPR) en het gebied onder de curve (AUC) met behulp van de volgende formules:

• FPR: =1-D3/$D$14
• TPR: =1-E3/$E$14
• ASC: =(F3-F4)*G3

Vervolgens kopiëren en plakken we deze formules in elke cel in de kolommen F, G en H:

Stap 4: Creëer de ROC-curve

Om de ROC-curve te creëren, zullen we elke waarde in het bereik F3:G14 markeren.

Vervolgens klikken we op het tabblad Invoegen langs het bovenste lint en klikken vervolgens op Scatter invoegen (X, Y) om het volgende pad te maken:

Stap 5: Bereken de AUC

Hoe dichter de curve bij de linkerbovenhoek van de grafiek past, hoe beter het model de gegevens in categorieën kan indelen.

Zoals we uit de bovenstaande grafiek kunnen zien, kan dit logistische regressiemodel de gegevens heel goed in categorieën indelen.

Om dit te kwantificeren, kunnen we de AUC (oppervlakte onder de curve) berekenen, die ons vertelt hoeveel van de grafiek zich onder de curve bevindt.

Hoe dichter de AUC bij 1 ligt, hoe beter het model. Een model met een AUC gelijk aan 0,5 is niet beter dan een model dat willekeurige classificaties uitvoert.

Om de AUC van de curve te berekenen, kunnen we eenvoudig alle waarden in kolom H bij elkaar optellen:

De AUC blijkt 0,802662 te zijn. Deze waarde is vrij hoog, wat aangeeft dat het model de gegevens goed indeelt in de categorieën ‘Pass’ en ‘Fail’.

Aanvullende bronnen

In de volgende tutorials wordt uitgelegd hoe u andere veelgebruikte plots in Excel kunt maken:

Hoe een CDF in Excel te plotten
Hoe u een overlevingscurve in Excel maakt
Hoe u een statistisch procescontrolediagram maakt in Excel