VBA Excel: hōtaka tauira. Tonotono i roto i Excel

y = x1 + x1 ^ 2 + 3 * x1 ^ 3 - Koha (x1)

Ngā pūtau (i, 1) .Value = x1 (uara x1 tuhituhia i roto i te mahara ki te taunga (i, 1))

Ngā pūtau (i, 2) .Value = y (y uara e i roto i te pūtau tuhituhia ki te taunga (i, 2))

i = + i 1 (tatau whaimana);

x1 = x1 + Shag (ketia te tautohe ki te rahi taahiraa);

koromeke

Whakamutunga Sub.

Ka rite ki te hua o tenei tonotono oma "Excel" whiwhi rua tīwae, te tuatahi o e kua oti te tuhituhi te uara mō te x, me te tuarua - ki y.

Na tabula taea ki te hanga ki runga ki a ratou, te paerewa mō "Excel".

TAUIRA 3

Hei whakatinana i huringa i roto i te VBA Excel 2010, me te i roto i te tahi atu putanga, me te Do kua heke Ahakoa hoahoa whakamahia hoki.

A feruri i te hōtaka e hanga he tīwae. I roto i ia pūtau ka tuhia reira tapawhā hāngai tau raina. Hoki te whakamahi o te hoahoa, ka tukua ki te tuhi i te reira rawa poto, i waho te whakamahi o te counter.

Tuatahi ko reira e tika ana ki te hanga i te tonotono, te whakaahuatanga i runga. Next, tuhituhi te waehere iho. ti'aturi nei tatou e kei tatou hiahia i roto i te uara mō te pūtau 10. Ko te waehere ko e whai ake.

Hoki = i 1 ki te 10 Next

Kei te whakawhiti te whakahau ki te reo "tangata", rite ki "toutou i te 1 ki te 10 i roto i ngā āpiti o te kotahi."

Ki te ki te mahi farii i te tīwae ki waharoa, hei tauira, tau tōpu rerekē katoa i waenganui i te 1 me te 11, tuhituhi matou:

Hoki i = 1 ki te 10 taahiraa 1 Next.

Here, taahiraa - taahiraa. I roto i tenei take, he mea rite ki te rua. Na roto i te taunoa, te ngaro o te kupu i roto i te koropiko tikanga e te taahiraa kotahi.

Me nga hua ki te penapena i roto i te tau pūtau (i, 1). Na wa katoa tīmata koe i te huringa ki te hua i roto i te uara o manga i ka tupu aunoa me te tau aho. Ko te kupu, i reira ka kia waehere arotautanga.

I roto i te whānui, e titiro te waehere rite:

hōtaka Sub ()

Hoki i = 1 Ki 10 Hipanga 1 (ka taea te tuhituhi anake hoki i = 1 Ki 10)

Ngā pūtau (i, 1) .Value = i ^ 2 (i.e. uara tapawha te tuhituhi ki roto ki te pūtau (i, 1) i)

Next (i roto i te tikanga whai i te tūranga o te tikanga counter me tetahi atu tīmatanga huringa)

Whakamutunga Sub.

Ki te mahi tika, tae atu ki te tuhi me te rere tonotono (kite. Ko te tohutohu i runga ake), ka huaina a reira ko ia wa ka he rahi hoatu kia whiwhi tīwae (i roto i tenei take arā, o te 10 ngā pūtau).

TAUIRA 4

I roto i te ora ia rā, tino maha he mea e tika ana ki te tango i tenei ranei e whakatau i runga i te tahi mau tikanga rānei. e kore e taea te mahi i waho ratou i roto i VBA Excel. Tauira o ngā hōtaka i reira tīpakohia te akoranga atu o te hātepe kei te utu o te tuatahi tīmatanga, tino hoahoa nuitia whakamahia o Ki te ... ka (mō ngā take uaua) Ki te ... ka ... END Ki.

A feruri i te take ngā. Whakaaro e hiahia ana koe ki te waihanga i tētahi tonotono mō "Excel" ki te pūtau ki te taunga (1,1) kua tuhia:

1 Ki te he pai te tautohe;

0 ki te ko te tautohe kore;

1, ki te he tōraro te tautohe.

Ko te hanganga o te tonotono taua mo "Excel" tīmata i roto i te ara paerewa, i roto i te whakamahi o "wera" mau taviri Alt me F11. I tua atu i tuhituhia te waehere e whai ake nei:

hōtaka Sub ()

x = Pūtau (1, 1) .Value (vahe tenei whakahau te uara o taunga x o te tirotiro pūtau (1, 1))

Ki te x> 0 Na Pūtau (1, 1) .Value = 1

Ki te x = 0 ka Pūtau (1, 1) .Value = 0

Ki te x <0 Na Pūtau (1, 1) .Value = -1

Whakamutunga Sub.

tonu te reira ki te whakahaere i te tonotono, me te whiwhi i roto i "Excel" uara e hiahiatia ana mo te tautohe.

mahi VBA

Ka rite ki ai koe i kite, e kore he uaua rawa i roto i te hōtaka tono ripanga Microsoft tino rongonui. Rawa, ki te ako koe ki te whakamahi i VBA mahi. I roto i te katoa, hanga āta tenei reo hōtaka mō te tuhi tono i roto i "Excel" me te kupu, e pā ana ki 160 ngā mahi. Ka taea te wehea ratou ki te maha ngā rōpū nui. Ko ratou:

• mahi Mathematical. Hoatu ratou ki te tautohe o te uara whenu e whiwhi, te taupū kōaro tūturu, a pera te wahi katoa.
• mahi pūtea. Nā ki to ratou wātea me te whakamahi hōtaka roto Excel, ka taea e te tiki e koe taputapu whai hua mō te mahi kaute, me te whakataunga pūtea.
• mahi tukatuka ngohi. Ko ētahi o ēnei huānga, IsArray; LBound; UBound.
• VBA Excel mahi mo te aho. Ko te rōpū tika nui tenei. Kei roto te reira i, mo te tauira, ki te Space mahi waihanga i te aho ki te maha o āputa rite ki te tautohe tōpū ASC whakawhitinga tohu ranei ki ANSI waehere. He whānui whakamahia katoa o ratou, a tukua ki a koutou te mahi ki aho i roto i te "Excel" ki te hanga tono nui māmā ki te mahi ki enei papa.
• mahi faafariuraa momo Raraunga. Hei tauira, hoki CVar Expression tautohe, tahuri reira ki Heitā momo raraunga.
• mahi rā. Ratou nui whakawhānui i te āhuatanga paerewa o "Excel". Na, hoki WeekdayName mahi te ingoa (tonu ranei te kanohi) o te ra o te wiki i tona maha. Ahakoa atu whai hua ko te Matawā. homai ana e ia te maha o ngā hēkona e kua pahure mai waenganui po ki te kau ngā o te ra.
• Mahi ki te tahuri i te tautohe tau i roto i te pūnaha tau rerekē. Hei tauira, whakaputanga Oct ki māngai-waru o te tau.
• mahi hōputu. Ko te nui tino o enei ko te Hōputu. hoki te reira i he kē ki te faaiteraa whakahōputu rite ki ngā tohutohu i homai i roto i te whakaahuatanga o te hōputu.
• a pera atu.

Ko te ako o te āhuatanga o enei mahi, me o ratou tono tino ka whakawhānui i te hōkai o "Excel".

TAUIRA 5

Kia tamata o ki te whakaoti rapanga matatini ake. Hei tauira:

Rana tuhinga pepa te taumata tūturu o utu o te pūrongo hinonga. titau:

• whakawhanake i tona wahi tauira i te ripanga "Excel";
• hanga i tētahi hōtaka VBA e ka ui hoki ki raraunga raw whakakiia reira, ki te kawe i nga tātai tika, me te whakakiia i roto i te tauira o te pūtau hāngai.

Fakakaukau angé ki tetahi o nga rongoā e whai ake nei.

Te hanga i te tātauira

Kua whakamana i mahi katoa i runga i te pepa paerewa i roto i Excel. pūtau Free i whakatoea mo te tomo raraunga i runga i te marama, te tau, kamupene taitara-kiritaki, te nui o nga utu, o ratou taumata o te rerekē haere. Ka rite ki te maha o kamupene (kamupene), i roto i te faatura o e kore e tuhituhia ki te pūrongo, ki te pūtau hanga i runga i te pūtake o ngā uara, me te ingoa ngaio kore e puritia mai nei i mua. Pepamahi whakaritea ki te ingoa hou. Hei tauira, "Օ Report".

taurangi

Hei tuhituhi te hōtaka whakakiia aunoa i te tātauira, tīpako i te momotuhi. Ka whakamahia ratou mo te taurangi:

• NN- maha o te haupae o te tepu o nāianei;
• TP me te TF - whakaritea me te rerekē haere tūturu;
• SF me SP - tūturu me āta utu katoa;
• IP me IF - whakaritea me utu tūturu taumata.

rawea, tatou e te taua pukapuka, engari ki te "kuhimua» Itog whakaeminga katoa mo taua tīwae. Hei tauira, ItogTP - Ki te whakaaro ki te tīwae tika, rite "te rerekē haere whakaritea."

Otinga o te raruraru mā te whakamahi i VBA hōtaka

Mā te tenei momotuhi, whiwhi tatou i te tātai mo te rerekētanga. Ki te hiahia koe ki te kawe i te tātai i roto i% i tatou (F - P) / P * 100, me i te nui o - (F - P).

Ko nga hua o enei tātaitanga e taea e pai tika ki te hanga "Excel" ripanga i roto i te pūtau tika.

Hoki te hua, me te waitohunga i roto i te meka whiwhi e tātai ItogP = ItogP + P me ItogF = ItogF + F.

Hoki ine whakamahi = (ItogF - ItogP) / ItogP * 100, ki te kawea te tātaitanga i roto i rite te ōrau, me te mea te uara tapeke - (ItogF - ItogP).

tuhia nga hua kotahi ano i roto i te pūtau e tika ana, na reira he kore hiahia ki te tautapa ratou ki taurangi.

I mua i te tīmata ki te hanga i tētahi hōtaka e hiahia ana koe ki te whakaora i te pukamahi, mo te tauira, i raro i te ingoa "Otchet1.xls".

Press "Waihanga i te tepu pūrongo," me pēhi koe 1 anake te wa i muri i tomo i te mōhiohio pane. kia mohio koe me ētahi atu ture. I roto i ngā, i te pātene "Tāpiri i te aho" Me aki ia wa i muri i tomo ki roto ki te tepu o ngā uara mō ia mahi. I muri tomo nga raraunga katoa e hiahiatia ana ki te pēhi i te pātene "Mutu", a ka whakawhiti ki "Excel" i roto i te matapihi.

Na e matau ana koutou e founga ki te whakaoti i te raruraru mo Excel ki ngā tonotono. Ko te kaha ki te whakamahi i VBA Excel (kite hōtaka tauira. Runga ake) a kia hiahia koe ki te mahi i roto i te taiao o te rongonui tino i te kau, i te ētita kuputuhi "Kupu." I roto i ngā, i te tuhi, pēnei e whakaaturia i roto i te timatanga o tenei tuhinga, e ranei tuhi waehere ki te waihanga i te pātene tahua i roto i nei e taea te mahi mā te pēhi i te mau taviri i ohipa i roto i te "Tirohia" icon me te ripa "Tonotono" ranei tokomaha o nga ngā mahi i runga i te kuputuhi.