微软Excel作为教授基础统计学的工具说明书

admin管理员组

文章数量:1530517

2024年7月2日发(作者：)

Microsoft Excel™ As a Tool for Teaching Basic Statistics

C. Bruce Warner

Anita M. Meehan

Kutztown University of Pennsylvania

Many

or

instructorsusespecializedstatisticspackages,suchas

courses.

SAS/STAT

Excel™

Asan

inteachingquantitativeandresearchmethods

SPSS

which

found

provides

offerscharting

alternative

asetofdata

capabilities

tothespecialized

analysis

and

tools

the

packages,Microsoft

beyond

Analysis

those

ToolPak,

what

gram.

one

inspreadsheets.

statistics

We

would

WithAnalysisToolPak,Excelresembles

normally

havesuccessfully

expectfrom

integrated

aspecializedlow-levelstatisticspro-

through

coursesanddevelopedatutorial

Excel

manual

intoour

to

introductory

ratings

ent

guidestudents

student

discuss

ofanintermediateformofthemanualanditsexercisesand

than a specialized statistical package for teaching basic statistics.

theadvantagesanddisadvantagesofusingExcelrather

sciences,

Similarto

software

courses.

such

foryears

many

asSPSS

we

instructors

relied

inthesocialandbehavioral

inour

primarily

statistics

on

and

specializedstatistical

spreadsheet

Ascomputer

sheet

statistics.

application

programs

technologyhasbecome

research

ubiquitous

methods

and

wasnot

more

a

popular,wewonderedifaspread-

pected

we

touse

Keeping

spreadsheets

inmind

better

in

that

mediumforafirstcoursein

their

most

work

studentswouldbeex-

software package, in our introductory statistics courses.

begantouseMicrosoftExcel™,a

places

popular

(Davis,

spreadsheet

1997),

Why Use a Spreadsheet Application

Such as Excel?

students

Onereasonweembracedaspreadsheetapplicationis

generalize

needdataanalysisandcomputerskillsthatthey

that

can

work

use

inenvironments

tsarelikelyto

ration,

ofspreadsheet

ers

1996).Arecent

applications,

inwhichgeneral

surveyfound

areexpected

softwareskills,suchas

thatprospective

(Olsten

employ-

Corpo-

skills,

ranked

vis,

hires

1997).

ashighly

spreadsheet

Moreover,

desirable

skills

86%

andexpected

second,behindword-processing

ofthe

skillsfornewhires(Da-

detailed

togo

ued

analyses.

beyond

Davis

spreadsheet

alsofound

basics

employers

that

and

employers

beable

expected

to

new

highly

perform

val-

believe

Another

theability

1995).

its

reason

tocreate

simplicity

wedecided

presentationsandgraphs.

facilitates

toteach

student

withExcelisthatwe

on

Studentscanconcentratemoreon

learning

statisticsand

(Henle,

less

spreadsheet

tryisofteneasierin

grams

somefamiliarity

suchas

applications

SPSS.

with

Moreover,

thanin

spreadsheets,

many

specializedstatisticalpro-

thereby

students

reducing

already

the

have

time

Vol. 28, No. 4, 2001

instructors

experience,

statistical

generated

packages

the

must

sophisticated

devoteto

canbeconfusing

output

for

generated

students.

by

The

advanced

output

troductory

by

struction.

statistics

Exceloften

textbook

matches

exercises

examples

orin

presented

classroom

inin-

in-

tronic

AnotheradvantageofExcelisthatit

assignment

tudent

simplifies

turns

the

in

elec-

file,

ondiskorviae-mail,theinstructorcanopenthe

an

the

dent

file

examine

the

turns

using

the

inan

the

contents,andattachcommentsdirectlyto

assignment

annotation

in

tance,whenastu-

points

wrong

When

directly

test,the

tothe

instructor

which

errorand

caninsert

heorshehasperformed

offers

atextballoonthat

loon

balloon

into

wegrade

anopen

assignments,

workbook,

we

so

save

that

a

we

copy

corrective

can

of

later

each

advice.

paste

textbal-

a similar mistake.

intotheworkbookofanotherstudentwhohasmade

the

and

A

with

universities

finaladvantage

already

ofusing

own

ts

an

spreadsheetsoftware,whereas

orhave

specialized

accessto

programs

computers

are

have

added

is only available in selected campus locations.

limited

expense.

access

The

tothe

extra

specialized

costalso

statistical

meansthat

software,

students

asit

Why Choose Excel?

standard

Wewere

statistics

for

familiar

ourcampus.

withExcel

Still,

becauseitisthespreadsheet

easily

course,asitperforms

itisanaturalchoicefora

and

customizablegraphsusing

most

itsChart

basicanalyses,

Wizardfeature,

creates

bility

Similar

isthe

Paste

tosolve

to

market

all

both

spreadsheet

leaderinitsproductcategory.

simple

applications,

andcomplex

Excel

equations.

hasthecapa-

lier

through

than

Function(knownasFunctionWizardinversions

Excel’s

ear-

into

the

Excel

steps

95)

necessary

simplifies

to

theprocessbytakingyou

tion

mple,

paste

you

statistical

canuse

calculations

Paste

standard deviation, variance, and correlation.

tofindcommonstatisticssuchasmean,median,mode,

Func-

ysis

Excel

install

ToolPak.

alsocontains

you

thisadd-in

Once

as

you

alesscommonlyknownfeature:Anal-

aworking

haveperformed

part

thesimplestepsthat

tics,

haveanarrayoftoolsforperforming

ofyourExcelapplication,

and

tistical

two-factor),

ttests,ANOVA

descriptivestatis-

correlation,

(one-way,one-wayrepeatedmeasures,

basicExcel

and

application.

graphingcapabilities

and

Toillustrate

that

regression.

the

are

Italsoaddssta-

relative

unavailable

simplicity

inthe

of

295

ndent-samples

t

-testdialogbox(top)andsample

output produced by the procedure (bottom).

the

example

Analysis

dependent-samples

ofthe

ToolPak,

dialogbox

the

that

toppanelofFigure1containsan

variable

t-testanalysis.

appears

Inthe

when

dialog

youselect

box,

anin-

from

sheet

each

range”

group.

referstothespreadsheetlocationforthe

“input

data

sumption

columnsrepresenting

Inourexample,

thehypothetical

wehaddataintwospread-

group

include

(B1–B13).

ofameditation

From

group(A1–A13)versus

pain-pill

acontrol

con-

to

columnlabelswith

thedialog

thedata

box,youcanalsochooseto

appears in the bottom panel of Figure 1.

clarifytheoutputfromtheprocedure.

beinginput.

TheExcel

Labels

output

help

cel,

Additional

Excel

including

resources

variousstatistical

existtosupport

add-ins

instruction

and

withEx-

evolvedaroundthetopicofusingspreadsheetsinmathematics

etdiscussion

simulations

groupshave

for

and

starting

science

tion

points

education,

forExcelresources

including

on

teaching

theWeb

statistics.

are

Good

/lists/)

ofStatisticsSpecialistsUsingMicrosoftExcel

the

(ASSUME;

Associa-

sheetsinEducation(/Spreadsite/).

andSpread-

Student Evaluation of Excel

signments

Beginning

oped

into

around

ourintroductory

1996,weincorporatedafewExcelas-

expanded

statistics

theFall

course

1998semester,

anddevel-

we

ual

Function,

forstudents.

ouruse

Analysis

The

ofExcel

ToolPak,

manual

and

explained

developedaninstructionman-

andChart

how

Wizard

touse

to

the

perform

Paste

296

many

tics

those

course.

ofthebasic

output,

inFigure

The

procedurescoveredinanintroductorystatis-

1),

manual

sample

included

problems

screen

with

,

workassignment

andhomework

follows:

associated

exampleof

statistical

ahome-

Make

ent-samples

upa

group.

t

data

test

setthatisappropriateforanindepend-

neath

tion

the

Do

output

theanalysis

andlabel

generated

and

15scoresper

by

save

Excel,

theoutput

type

-

served

of

group,

t

the

and

results.

itsactual

This

pvalue,

must

the

include:

critical

the

in

t,the

value

yourinterpreta-

means

oftheob-

.05,

ha

foreach

=

sults

two-tailed.

ent-samples

underStep

See

ttest using Excel.

4of

the

the

model

section

provided

oncomputing

forinterpreting

anindepend-

re-

that

Werequiredstudentstogeneratesimulated

with

each

analysis.

practice

submissionwasoriginalandtoprovide

datato

students

ensure

had

Byexamining

ingenerating

thedata

simple

and

data

interpretations

setsappropriatefor

ceptions

generated,

dures.

thatstudents

wecould

had

oftendiscoverandcorrect

students

miscon-

scores

For

forming a related-samples

from

instance,

twoimaginary

somestudents

aboutvarious

independent

persisted

statistical

samples

ingenerating

proce-

whenper-

ing

Studentscompletedfive

ttest.

requiredExcel

turn

the

pleting

inat

tion,almostallstudents

assignments

chose

dur-

to

our

three.

least

To

one

improve

bonusassignment,

our

withthemajoritycom-

uation of student reactions to Excel.

instructionmanual,wedecided

teaching

toconduct

ofstatistics

aformal

aswell

eval-

as

Method

Participants

1998

Wesurveyed49ofthe52studentsenrolledin

ual

ter.

and

Introductory

anonymous.

Participation

completed

Statistics

the

classeswhohadusedthe

our

man-

Fall

in

Excel

the

assignments

surveywas

during

voluntary

thesemes-

and

Measures

the

We

four-part

homework

askedthe

rated

questionnaire.

assignments

studentstoexpresstheiropinionsabout

In

and

Parts

the

A

course

through

manual

C,students

usinga

semantic

the

A)

differential

assignments

descriptions

andmanual.

of

Students

theassignments

respondedto

ranging

and

left

from

thecoursemanual(PartC)ona5-pointscale,

(Part

In

)

statements

Part

to5

B,

(complete

1(completeagreementwiththedescriptiononthe

participants

agreement

responded

withthedescriptionontheright).

were

disagree

also

abouttheExcelassignments.

tocourse

Ratings

goal-oriented

onPartB

the

)to

based

5

ona5-pointscalerangingfrom1(strongly

students

mean

to

responses

(strongly

providefree-form

appear

agree).

in

Thecompletesetofitemsand

comments

and

D

suggestions.

prompted

Teaching of Psychology

Table 1.

Evaluation Item

Summary of Student Responses Concerning the Fall 1998 Excel™ Homework Assignments

and the Fall 1998 Excel Manual

M

3.59

3.40

3.51

3.39

3.06

3.65

3.43

3.84

3.86

3.37

3.94

3.65

3.69

3.90

3.96

4.20

3.69

3.04

2.98

3.84

3.49

4.25

SD

0.61

0.89

0.98

1.02

1.22

0.81

1.00

0.85

1.04

1.04

0.99

0.90

0.96

1.02

0.64

0.89

1.07

0.71

0.32

0.85

1.00

0.80

Assignments—Part A: Semantic differential items

Extremely bad–extremely good

Boring–interesting

Difficult–easy

Unpleasant–pleasant

Not frustrating–frustrating

Imposing–friendly

Confusing–clear

Too much work–not too much work

Not useful–useful

Assignments—Part B: Likert-scale items

The Excel assignments helped me understand statistical concepts better

The Excel assignments improved my computer skills

The Excel assignments allowed me to be creative

The Excel assignments required critical thinking on my part

The Excel assignments gave me skills I can use

Excel manual—Part C: Semantic differential items

Extremely bad–extremely good

Not helpful–helpful

Hard to follow–easy to follow

Not detailed enough–too detailed

Too short–too long

Imposing–friendly

Confusing–clear

Not useful–useful

Note.N

=5-pointratingsonpartsAandC,scoresrangedfrom1(

completeagreementwiththedescriptionontheleft

)to5(

complete

agreementwiththedescriptionontheright

).ThepolesforsevB,

scores ranged from 1 (

strongly disagree

) to 5 (

strongly agree

).

Results and Discussion

Table1presentstheresultsofthestudentratingsoftheEx-

tsindicatedthattheas-

signmentswereuseful,nottoomuchwork,andrequiredsome

pressed

agreementthattheassignmentspromotedthedevelopmentof

ghstudentsagreedthattheassignments

improvedtheirknowledgeofstatisticalconcepts,theyrated

theassignmentsmorehighlyintermsofimprovingtheircom-

tfrustrationwiththeassignmentswas

moderate,whichwebelieveisafairlyreasonablelevelgiven

thetypicalfrustrationsassociatedwithanystatisticsassign-

ment,plustheaddedheadachesassociatedwithusinganysoft-

instances,frustrationswerenot

specificallyrelatedtoExcelbutweretiedtogeneralcomputer

issuessuchasgettingaccesstocampuslabsorforgettingtosave

onstotheinstructionmanual

werealsogenerallypositive,tsin-

dicatedtheappropriatenessofboththelengthandcompre-

undthemanualhelpfulfor

completingassignmentsandgenerallyfounditusefulanduser

friendly.

Considerations for Using Excel

to Teach Statistics

DecidingwhethertouseExcelratherthanaspecialized

statisticalpackagenotonlyrequiresacarefulcomparisonof

Vol. 28, No. 4, 2001

thespecificmeritsofeachsoftwarepackage,butalsode-

mandsamethodicalexaminationofstudentneeds,course

anddepartmentgoals,

makessensewhentimeforteachingsoftwareislimitedorac-

cesstospecializedsoftwareislimited,whencoursegoalsin-

cludethedevelopmentofgeneralcomputerskills,whenmost

studentswillenterthejobmarketdirectlyandnotpursue

graduatetraining,orwhenstudentscomefromavarietyof

departmentsthatemphasizedifferentstatisticalpackagesin

ructorconsideringtheadoption

ofExcelshouldalsoconsiderwhetherstudentswouldhave

anopportunitytolearnaspecializedstatisticalpackageinan-

pinion,Exceltrainingshouldnotcon-

stituteastudent’ssoleexposuretocomputerizeddata

ghwemayuseExcelinintroductorystatis-

tics, we use both Excel and SPSS in our methods courses.

Excelsimplycannotsupplantspecializedpackagesfortwo

,thestandardsetofstatisticalproceduresfor

ExcelisquitelimitedcomparedtopackagessuchasSPSS.

Forinstance,ExcelcanperformonlythreeANOVAproce-

dures:(a)one-wayANOVAforindependentsamples,(b)

one-wayrepeated-measuresANOVA,and(c)two-way

’sANOVAproce-

dureswillnotperformplannedcomparisonsorpost-hoctests,

-

ond,thestandardstatisticalproceduresincludedinExcelare

notasrobustasproceduresinspecializedstatisticalpackages

(e.g.,McCullough&Wilson,1999;Sawitzki,1994).Based

onExcel’sperformanceonavarietyofdatasets,McCullough

andWilson,andSawitzki,recommendedthatExcelnotbe

297

used

cel’s

serious

implementation

tedweaknessinEx-

large

errorsonlarge

of

data

several

sets,

algorithms,whichwillleadto

Microsoft

values.

the flaws have been exposed in earlier versions of Excel.

does

McCullough

notcorrect

and

flawed

Wilson

especially

algorithms,

also

those

observed

containing

eventhough

that

cause

BecauseExcelhasalimitedvariety

simplynotagoodchoiceforadvancedcoursesorgeneralscien-

itmayproduceinaccurateresults

of

on

procedures

largedatasets,

and

it

be-

is

tific

courses

tically,though,mostintroductory

in

larger

Excel’s

will

Excel’s

than

Analysis

notcover

thosethat

ToolPak,

manyprocedures

canbecomputed

andsample

beyond

data

those

setswill

included

beno

generally

performanceonsmalldatasets,

conveniently

inourexperience,

byhand.

What

both

Excel

very

does

acceptable

offer

(ough&Wilson,1999).

is

parency,

input

tween

meaning

andoutput,

for

that

which

introductory

students

Henle

can

(1995)

coursesissimplicityof

easilysee

described

therelation

astrans-

be-

those

overcome

numbers.

thenumbersandtheresultsofoperationsperformedon

available

limitations

Also,add-ins

ofbothflexibility

areavailableforExcelthatmay

(/lists/).

add-inscanbefoundatthe

and

ASSUME

accuracy.

Web

Alist

site

of

specialized

RegardlessofwhethertheinstructorchoosesExcelora

and

a

expect

package,

guidance

students

incompleting

expecttrainingonthesoftware

ates,

recentsurveyofover1,000Cornell

computer

University

assignments.

undergradu-

In

the

1999).

most

students

trating

A

effective

rated

smallstudent

means

credit-bearing

focus

ofacquiring

classesandpeersupportas

groupalso

computer

expressed

skills

how

(Davis,

possess

support

certain

andstressfulitiswhenprofessorsassumestudents

frus-

cus

ortraining

software

(Davis,

skills

1999).

yetfailtoprovidestudentswith

to

pense

devote

group

significant

alsoindicated

class

that

time

students

Atthe

toteaching

donot

sametime,thefo-

software

wantprofessors

atthe

sistent

nourexperience,andcon-

ex-

believe

with

&

instructors

thestudent

needsome

viewsexpressedinDavis(1999),we

Warner,

Carey,2000;Dretzke&

sort

Heilman,

ofstudent

1998;

,Berk

ciently, effectively, and with minimal frustration.

2000)tohelpstudentscompleteassignments

Meehan

effi-

&

Conclusions

tary

We

using

statistics.

believethat

and

spreadsheet

Business

Excelisausefultoolforteachingelemen-

applications

schoolshave

ineducating

beenatthe

their

forefrontof

Berenson,

manyresources

Pelosi&Sandifer,

&Stephan,

,

students,

Levine,

1999),

1999;

but

Middleton,

spreadsheet-oriented

2000;Neufeld,

statistics

1998;

298

guides

Berk

,

Warner,

&Carey,2000;Dretzke&Heilman,1998;Meehan&

costs

cialized

and

2000).Individualinstructorswillneedtoweighthe

asExcel,

statistical

benefitsofusingaspreadsheetprogramversusaspe-

willprove

package,

moreattractive

butwebelieve

inmany

aspreadsheet,

situations.

such

References

Berk,K.N.,&

Davis,

Updated for Office 2000.

Carey,P.(2000).

P.(1997).Whatcomputer

Pacific Grove, CA: Brooks/Cole.

DataanalysiswithMicrosoftExcel:

skillsdoemployersexpectfromre-

Davis,

cent graduates?

P.(1999).How

T.H.E. Journal, 24,

undergraduates

74–78.

learncomputer

Dretzke,

of a survey and a focus group.

B.J.,&Heilman,K.A.

T.H.E. Journal, 26,

(1998).Statisticswith

68–71.

skills:Results

MicrosoftEx-

Henle,

Saddle River, NJ: Prentice Hall.

Levine,

Mathematics Journal, 26,

M.G.(1995).lege

managers

D.M.,

using

Berenson,

Microsoft

M.

320–328.

L.,

Excel.

&Stephan,

Upper

D.

Saddle

(1999).Statistics

River,NJ:

for

McCullough,

Prentice-Hall.

calprocedures

B.D.,

in

&

Microsoft

Wilson,B.

Excel

(1999).

97.

On

Computational

theaccuracy

Statistics

ofstatisti-

Meehan,

Data Analysis, 31,

&

A.M.,&Warner,

27–37.

C.B.(2000).Elementarydataanalysis

Middleton,

ing Microsoft Excel.

us-

M.R.(2000).

New York: McGraw-Hill.

DataanalysisusingMicrosoftExcel(2nd

Neufeld,

ed.). Pacific Grove, CA: Brooks/Cole.

Olsten

Saddle River, NJ: Prentice Hall.

J.L.(1998).LearningbusinessstatisticswithMicrosoftExcel

Pelosi,

workforce.

Corporation.

M.K.,

Melville, NY: Author.

(1996).Managinganddevelopingthenew

&Sandifer,T.M.(1999).Doingstatisticsfor

Sawitzki,

with Excel: Data, inference and decision making.

business

Computational Statistics & Data Analysis, 18,

G.(1994).Reportonthereliabilityofdata

New York: Wiley.

289–301.

analysissystems.

Notes

t

ualwasprovided

fortheinitial

byagrant

development

fromthe

of

Kutztown

anExcel

University

instruction

Profes-

man-

2.

sional Development Committee.

Aversionofthisarticlewaspresentedatthemeetingof

3.

American Psychological Society, Denver, CO, June, 1999.

the

We

also

thankMeganMumma,whostudenttestedour

ymous

thank

reviewers

DavidPittenger,

fortheirhelpful

Randolph

comments

,

on

and

earlier

three

drafts

anon-

4.

this article.

of

Send

chology,

arner,Department

*******************.

KutztownUniversity,Kutztown,PA19530;

of

e-mail:

Psy-

Teaching of Psychology

本文标签： 基础教授工具说明书作为