Excel Vba Course Notes 2 - Using Loops.pdf - Excel - ziolek6661

(;&(/9%$

86,1*/2236

([FHO9%$&RXUVH1RWHV8VLQJ/RRSV

)LOH9%$/RRSV[OV

:K\/RRSV"

7KHSXUSRVHRIDORRSLVWRJHW([FHOWRUHSHDWDSLHFHRIFRGHDFHUWDLQQXPEHURIWLPHV+RZPDQ\WLPHVWKHFRGH

JHWVUHSHDWHGFDQEHVSHFLILHGDVDIL[HGQXPEHUHJGRWKLVW LPHVRUDVDYDULDEOHHJGRWKLVIRUDVPDQ\

WLPHVDVWKHUHDUHURZVRIGDWD

/RRSVFDQEHFRQVWUXFWHGPDQ\GLIIHUHQWZD\VWRVXLWGLIIHUHQWFLUFXPVWDQFHV2IWHQWKHVDPHUHVXOWFDQEHREWDLQHG

LQGLIIHUHQWZD\VWRVXLW\RXUSHUVRQDOSUHIHUHQFHV7KH VHH[HUFLVHVGHPRQVWUDWHDVHOHFWLRQRIGLIIHUHQWZD\VWRXVH

ORRSV

7KHUHDUHWZREDVLFNLQGVRIORRSVERWKRIZKLFKDUHGHPRQVWUDWHGKHUH 'R«/RRS DQG)RU«1H[W ORRSV7KHFRGH

WREHUHSHDWHGLVSODFHGEHWZHHQWKHNH\ZRUGV

2SHQWKHZRUNERRN9%$/RRSV[OV DQGWDNHDORRNDWWKHIRXUZRUNVKHHWV(DFKFRQWDLQVWZRFROXPQVRIQXPEHUV

FROXPQV$DQG%7KHUHTXLUHPHQWLVWRFDOFXODWHDQDYHUDJHIRUWKHQXPEHUVLQHDFKURZXVLQJD9%$PDFUR

1RZRSHQWKH9LVXDO%DVLF(GLWRU$OW)DQGWDNHDORRNDWWKHFRGHLQ0RGXOH<RXZLOOVHHDQXPEHURI

GLIIHUHQWPDFURV,QWKHIROORZLQJH[HUFLVHVILUVWUXQWKHPDFURWKHQFRPHDQGUHDGWKHFRGHDQGILJXUHRXWKRZLWGLG

ZKDWLWGLG

<RXFDQUXQWKHPDFURVHLWKHUIURPWKH9LVXDO%DVLF(GLWRUE\SODFLQJ\RXUFXU VRULQWKHPDFURDQGSUHVVLQJWKH)

NH\RUIURP([FHOE\RSHQLQJWKH0DFURVGLDORJER[$/7)FKRRVLQJWKHPDFURWRUXQDQGFOLFNLQJ5XQ,WLVEHVW

WRUXQWKHVHPDFURVIURP([FHOVR\RXFDQZDWFKWKHPDVWKH\ZRUN

([HUFLVH'R«/RRS8QWLO«

7KHREMHFWRIWKLVPDFURLVWRUXQGRZQFROXPQ&DVIDUDVLVQHFHVVDU\SXWWLQJDFDOFXODWLRQLQHDFKFHOODVIDUDVLV

QHFHVVDU\

2Q6KHHWVHOHFWFHOO& DQGUXQWKHPDFUR/RRS

+HUHVWKHFRGH

6XE /RRS

7KLVORRSUXQVXQWLOWKHUHLVQRWKLQJLQWKHQH[WFROXPQ

$FWLYH&HOO)RUPXOD5& $YHUDJH5&>@5&>@

$FWLYH&HOO2IIVHW6HOHFW

/RRS8QWLO ,V(PSW\$FWLYH&HOO2IIVHW

(QG6XE

7KLVPDFURSODFHVDIRUPXODLQWRWKHDFWLYHFHOODQGPRYHVLQWRWKHQH[WFHOOGRZQ,WXVHV /RRS8QWLO WRWHOO([FHOWR

NHHSUHSHDWLQJWKHFRGHXQWLO WKHFHOOLQWKHDGMDFHQWFROXPQFROXPQ'LVHPSW\,QRWKHUZRUGVLWZLOONHHSRQ

UHSHDWLQJDVORQJDVWKHUHLVVRPHWKLQJLQFROXPQ'

'HOHWHWKHGDWDIURPFHOOV&& DQGUHDG\IRUWKHQH[WH[HUFLVH

([HUFLVH'R:KLOH«/RRS

7KHREMHFWRIWKLVPDFURLVWRUXQGRZQFROXPQ&DVIDUDVLVQHFHVVDU\SXWWLQJDFDOFXODWLRQLQHDFKFHOODVIDUDVLV

QHFHVVDU\

2Q6KHHWVHOHFWFHOO& DQGUXQWKHPDFUR/RRS

+HUHVWKHFRGH

6XE /RRS

7KLVORRSUXQVDVORQJDVWKHUHLVVRPHWKLQJLQWKHQH[WFROXPQ

'R:KLOH ,V(PSW\$FWLYH&HOO2IIVHW )DOVH

$FWLYH&HOO)RUPXOD5& $YHUDJH5&>@5&>@

$FWLYH&HOO2IIVHW6HOHFW

/RRS

(QG6XE

7KLVPDFURGRHVWKHVDPHMREDVWKHODVWRQHXVLQ JWKHVDPHSDUDPHWHUVEXWVLPSO\H[SUHVVLQJWKHPLQDGLIIHUHQW

ZD\,QVWHDGRIUHSHDWLQJWKHFRGH8QWLO VRPHWKLQJRFFXUVLWGRHVVRPHWKLQJ:KLOH VRPHWKLQJLVWKHFDVH,WXVHV

'R :KLOH WRWHOO([FHOWRNHHSUHSHDWLQJWKHFRGHZKLOH WKHUHLVVRPHWKLQJLQWKHDGMDFHQWFROXPQDVRSSRVHGWRXQWLO

WKHUHLVQRWKLQJWKHUH7KHIXQFWLRQ,V(PSW\ )DOVH PHDQV,V1RW(PSW\

'HOHWHWKHGDWDIURPFHOOV&& DQGUHDG\IRUWKHQH[WH[HUFLVH

0DUWLQ*UHHQ

ZZZIRQWVWXIIFRP

(;&(/9%$

86,1*/2236

([HUFLVH'R:KLOH1RW«/RRS

7KHREMHFWRIWKLVPDFURLVWRUXQGRZQFROX PQ&DVIDUDVLVQHFHVVDU\SXWWLQJDFDOFXODWLRQLQHDFKFHOODVIDUDVLV

QHFHVVDU\

2Q6KHHWVHOHFWFHOO& DQGUXQWKHPDFUR/RRS

+HUHVWKHFRGH

6XE /RRS

7KLVORRSUXQVDVORQJDVWKHUHLVVRPHWKLQJLQWKHQH[WFROXPQ

'R:KLOH1RW ,V(PSW\$FWLYH&HOO2IIVHW

$FWLYH&HOO)RUPXOD5& $YHUDJH5&>@5&>@

$FWLYH&HOO2IIVHW6HOHFW

/RRS

(QG6XE

7KLVPDFURPDNHVH[DFWO\WKHVDPHGHFLVLRQDVWKHODVWRQHEXWMXVWH[SUHVVHVLWLQDGLIIHUHQWZD\ ,V(PSW\ )DOVH

PHDQVWKHVDPHDV1RW,V(PSW\6RPHWLPHV\RXFDQWVD\ZKDW\RXZDQWWRVD\RQHZD\VR9%$RIWHQRIIHUVDQ

DOWHUQDWLYHV\QWD[

'HOHWHWKHGDWDIURPFHOOV&& DQGUHDG\IRUWKHQH[WH[HUFLVH

([HUFLVH,QFOXGLQJDQ,)VWDWHPHQW

7KHREMHFWRIWKLVPDFURLVDVEHIRUHEXWZLWKRXWUHSODFLQJDQ\GDWDWKDWPD\DOUHDG\EHWKHUH

0RYHWR6KHHWVHOHFWFHOO& DQGUXQWKHPDFUR/RRS

+HUHVWKHFRGH

6XE /RRS

7KLVORRSUXQVDVORQJDVWKHUHLVVRPHWKLQJLQWKHQH[WFROXPQ

,WGRHVQRWFDOFXODWHDQDYHUDJHLIWKHUHLVDOUHDG\VRPHWKLQJLQWKH FHOO

,I ,V(PSW\$FWLYH&HOO 7KHQ

$FWLYH&HOO)RUPXOD5& $YHUDJH5&>@5&>@

(QG,I

$FWLYH&HOO2IIVHW6HOHFW

/RRS8QWLO ,V(PSW\$FWLYH&HOO2IIVHW

(QG6XE

7KHSUHYLRXVPDFURVWDNHQRDFFRXQWRIDQ\SRVVLEOHFRQWHQWVWKDWPLJKWDOUHDG\EHLQWKHFHOOVLQWRZKLFKLWLVSODFLQJ

WKHFDOFXODWLRQV7KLVPDFURXVHVDQ,)VWDWHPHQWWKDWWHOOV([FHOWRZULWHWKHFDOFXODWLRQ RQO\LI WKHFHOOLVHPSW\7KLV

SUHYHQWVDQ\H[LVWLQJGDWDIURPEHLQJRYHUZULWWHQ7KHOLQHWHOOLQJ([FHOWRPRYHWRWKHQH[WFHOOLV RXWVLGH WKH,)

VWDWHPHQWEHFDXVHLWKDVWRGRWKDWDQ\ZD\

([HUFLVH$YRLGLQJ(UURUV

7KLVPDFURWDNHVWKH,)VWDWHPHQWDVWDJHIXUWKHUDQGGRHVQWWU\WRFDOFXODWHDQDYHUDJHRIFH OOVWKDWDUHHPSW\

)LUVWORRNDWWKHSUREOHP0RYHWR6KHHWVHOHFWFHOO& DQGUXQWKHPDFUR/RRS

1RWHWKDWEHFDXVHVRPHRIWKHSDLUVRIFHOOVLQFROXPQV$DQG%DUHHPSW\WKH $9(5$*(IXQFWLRQWKURZVXSD

',9HUURUWKH$YHUDJHIXQFWLRQDGGVWKHQXPEHUVLQWKHFHOOVWKHQGLYLGHVE\WKHQXPEHURIQXPEHUV LIWKHUH

DUHQWDQ\QXPEHUVLWWULHVWRGLYLGHE\]HURDQG\RXFDQWGRWKDW

'HOHWHWKHFRQWHQWVRIFHOOV&& DQG&&6HOHFWFHOO& DQGUXQWKHPDFUR/RRS

+HUHVWKHFRGH

6XE /RRS

7KLVORRSUXQVDVORQJDVWKHUHLVVRPHWKLQJLQWKHQH[WFROXPQ

,WGRHVQRWFDOFXODWHDQDYHUDJHLIWKHUHLVDOUHDG\VRPHWKLQJLQWKHFHOO

QRULIWKHUHLVQRGDWDWRDYHUDJHWRDYRLG',9HUURUV

,I ,V(PSW\$FWLYH&HOO 7KHQ

,I ,V(PSW\$FWLYH&HOO2IIVHW $QG ,V(PSW\$FWLYH&HOO2IIVHW 7KHQ

$FWLYH&HOO9DOXH

(OVH

$FWLYH&HOO)RUPXOD5& $YHUDJH5&>@5&>@

(QG,I

$FWLYH&HOO2IIVHW6HOHFW

/RRS8QWLO ,V(PSW\$FWLYH&HOO2IIVHW

(QG6XE

0DUWLQ*UHHQ

ZZZIRQWVWXIIFRP

(;&(/9%$

86,1*/2236

1RWHWKDWWKLVWLPHWKHUHDUHQRHUURUPHVVDJHVEHFDXVH([FHOKDVQWWULHGWRFDOFXODWHDYHUDJHVRIQXPEHUVWKDW

DUHQWWKHUH

,QWKLVPDFURWKHUHLVDVHFRQG,)VWDWHPHQW LQVLGH WKHRQHWKDWWHOOV([FHOWRGRVRPHWKLQJRQO\LIWKHFHOOLVHPSW\

7KLVVHFRQG,)VWDWHPHQWJLYHVH[FHODFKRLFH,QVWHDGRIDVLPSOH ,I WKHUHLVDQ,I DQGDQ(OVH+HUHVKRZ([FHO

UHDGVLWVLQVWUXFWLRQV«

,IWKHFHOOKDVDOUHDG\JRWVRPHWKLQJLQJRWRWKHQH[WFHOO%XWLIWK HFHOOLVHPSW\ORRNDWWKHFRUUHVSRQGLQJFHOOVLQ

FROXPQV$DQ%DQGLIWKH\DUHERWKHPSW\ZULWHQRWKLQJ2WKHUZLVHZULWHWKHIRUPXODLQWKHFHOO7KHQPRYHRQ

WRWKHQH[WFHOO

([HUFLVH)RU«1H[W/RRS

,I\RXNQRZRUFDQJHW9%(WRILQGRXW KRZPDQ\WLPHVWRUHSHDWDEORFNRIFRGH\RXFDQXVHD )RU«1H[W ORRS

0RYHWR6KHHWVHOHFWFHOO& DQGUXQWKHPDFUR/RRS

+HUHVWKHFRGH

6XE /RRS

7KLVORRSUHSHDWVIRUDIL[HGQXPEHURIWLPHVGHWHUPLQHGE\WKHQXPEHURIURZV

LQWKHUDQJH

'LP L $V ,QWHJHU

)RU L 7R 6HOHFWLRQ&XUUHQW5HJLRQ5RZV&RXQW

$FWLYH&HOO)RUPXOD5& $YHUDJH5&>@5&>@

$FWLYH&HOO2IIVHW6HOHFW

1H[W L

(QG6XE

7KLVPDFURGRHVQWPDNHXVHRIDQDGMDFHQWFROXPQRIFHOOVOLNHWKHSUHYL RXVRQHVKDYHGRQHWRNQRZZKHQWRVWRS

ORRSLQJ,QVWHDGLWFRXQWVWKHQXPEHURIURZVLQWKHFXUUHQWUDQJHRIGDWDDQGXVHVWKH )RU«1H[W PHWKRGWRWHOO

([FHOWRORRSWKDWQXPEHURIWLPHVPLQXVRQHEHFDXVHZKHQ9%$FRXQWVLWVWDUWVDW]HUR

([HUFLVH*HWWLQJWKH5HIHUHQFH)URP6RPHZKHUH(OVH

6HOHFWFHOO* DQGUXQWKHPDFUR/RRS

+HUHVWKHFRGH

6XE /RRS

7KLVORRSUHSHDWVDIL[HGQXPEHURIWLPHVJHWWLQJLWVUHIHUHQFHIURPHOVHZKHUH

'LP L $V,QWHJHU

'LP LQW5RZ&RXQW $V,QWHJHU

LQW5RZ&RXQW 5DQJH$&XUUHQW5HJLRQ5RZV&RXQW

)RU L 7R LQW5RZ&RXQW

$FWLYH&HOO)RUPXOD5& $YHUDJH5&>@5&>@

$FWLYH&HOO2IIVHW6HOHFW

1H[W L

(QG6XE

<RXFDQJHWWKHUHIHUHQFHIRUWKHQXPEHURIORRSVIURPDQ\ZKHUH7KLVP DFURSODFHVDVHWRIFDOFXODWLRQVLQFROXPQ*

IRUDQXPEHURIWLPHVGLFWDWHGE\WKHQXPEHURIURZVLQWKHEORFNRIGDWDVWDUWLQJZLWKFHOO$7KH )RU«1H[W

VWDWHPHQWKDVEHHQVLPSOLILHGDELWE\ILUVWGHFODULQJDYDULDEOH LQW5RZ&RXQW DQGILOOLQJLWZLWKWKHDSSURSULDWH

LQIRUPDWLRQKRZPDQ\URZVLQWKHEORFNE\$7KLVYDULDEOHJHWVXVHGLQWKHQH[WOLQHLQVWHDGRIDORQJOLQHRIFRGH

7KLVLVMXVWDQRWKHUH[DPSOHRIGRLQJWKHVDPHMREDGLIIHUHQWZD\

,I\RXZDQWHGWRFRQVWUXFWDORRSWKDWDOZD\VUDQD EORFNRIFRGHDIL[HGQXPEHURIWLPHV\RXFRXOGVLPSO\XVHDQ

H[SUHVVLRQOLNH

)RU L 7R

([HUFLVH$ERXW'RLQJ&DOFXODWLRQV«

$OOWKHSUHYLRXVH[HUFLVHVKDYHSODFHGDFDOFXODWLRQLQWRDZRUNVKHHWFHOOE\DFWXDOO\ZULWLQJDUHJXODU([FHOIXQFWLRQ

LQWRWKHFHOODQGOHDYLQJLWWKHUHMXVWDVLI\RXKDGW\SHGLW\RXUVHOI7KHV\QWD[IRUWKLVLV

$FWLYH&HOO)RUPXOD5& ³7<3(<285)81&7,21+(5(´

7KHVHPDFURVKDYHEHHQXVLQJ

$FWLYH&HOO)RUPXOD5& ³ $YHUDJH5&>@5&>@´

%HFDXVHWKLVPHWKRGDFWXDOO\SODFHVDIXQFWLRQLQWRWKHFHOOUDWKHUWKDQDYDOXHWKHLUUHVXOWVZLOOFKDQJHDVWKHFHOOV

WKDWWKH\UHIHUWRFKDQJHMXVWOLNHUHJXODUIXQFWLRQV ± EHFDXVHWKH\DUHUHJXODUIXQFWLRQV7KHFDOFXODWLQJJHWVGRQHLQ

([FHOEHFDXVHDOOWKDWWKHPDFURGLGZDVWRZULWHWKHIXQFWLRQ

0DUWLQ*UHHQ

ZZZIRQWVWXIIFRP

(;&(/9%$

86,1*/2236

,I\RXSUHIHU\RXFDQJHWWKHPDFURWRGRWKHFDOFXODWLQJDQGMXVWZULWHWKHUHVXOWLQWRWKHFHOO9%$KDVLWVRZQVHWRI

IXQFWLRQVEXWXQIRUWXQDWHO\$9(5$*(LVQ¶WRQHRIWKHP+RZHYHU9%$GRHVVXSSRUWPDQ\RIWKHFRPPRQHU([FHO

IXQFWLRQVZLWKLWV:RUNVKHHW)XQFWLRQ PHWKRG

2Q6KHHWVHOHFWFHOO& DQGUXQWKHPDFUR/RRS

7DNHDORRNDWWKHFHOOV\RXMXVWILOOHGLQ(DFKRQHFRQWDLQVDIXQFWLRQZULWWHQE\WKHPDFUR

1RZGHOHWHWKHFRQWHQWVIURPWKHFHOOV&&VHOHFWFHOO& DQGUXQWKHPDFUR/RRS

+HUH¶VWKHFRGH

6XE /RRS

$FWLYH&HOO9DOXH :RUNVKHHW)XQFWLRQ$YHUDJH$FWLYH&HOO2IIVHW9DOXH B

$FWLYH&HOO2IIVHW9DOXH

$FWLYH&HOO2IIVHW6HOHFW

/RRS8QWLO ,V(PSW\$FWLYH&HOO2IIVHW

(QG6XE

7DNHDORRNDWWKHFHOOV\RXMXVWILOOHGLQ7KLVWLPHWKHUH¶VQRIXQFWLRQMXVWWKHYDOXH$OOWKHFDOFXODWLQJZDVGRQHE\

WKHPDFURZKLFKWKHQZURWHWKHYDOXHLQWRWKHFHOO

0DUWLQ*UHHQ

ZZZIRQWVWXIIFRP

Excel Vba Course Notes 2 - Using Loops.pdf

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: