Kiel verki formulon en Excel? Trejnado. La plej bezonataj formuloj

Bonan posttagmezon

Iam, skribi formulon memstare en Excel estis io nekredebla por mi. Kaj kvankam mi ofte laboris en ĉi tiu programo, mi ne plenigis ion krom la teksto ...

Kiel rezultis, plej multaj formuloj estas nenio komplika kaj vi povas facile labori kun ili, eĉ por novulo komputila uzanto. En la artikolo, mi simple ŝatus malkaŝi la plej necesajn formulojn, kun kiuj mi plej ofte devas labori ...

Do, ni komencu ...

1. Bazaj operacioj kaj bazoj. Lernu la bazojn de Excel.

Ĉiuj agoj en la artikolo estos montritaj en Excel-versio 2007.

Post komenci la programon Excel - fenestro aperas kun multaj ĉeloj - nia tablo. La ĉefa ĉefaĵo de la programo estas, ke ĝi povas legi (kiel kalkulilo) viajn formulojn, kiujn vi skribas. Parenteze, vi povas aldoni formulon al ĉiu ĉelo!

La formulo devas komenci per la signo "=". Ĉi tio estas antaŭkondiĉo. Poste vi skribas tion, kion vi bezonas por kalkuli: ekzemple "= 2 + 3" (sen kompiloj) kaj premu la Enteran klavon - rezulte vi vidos, ke la rezulto "5" aperas en la ĉelo. Vidu ekrankopion sube.

Grava! Malgraŭ la fakto, ke la numero "5" estas skribita en la ĉelo A1, ĝi kalkuliĝas per la formulo ("= 2 + 3"). Se en la sekva ĉelo simple skribu "5" en teksto - tiam kiam vi pasas ĉi tiun ĉelon en la formulo-redaktilo (linio supre, Fx) - vi vidos la primaran numeron "5".

Nun imagu, ke en ĉelo vi povas skribi ne nur la valoron 2 + 3, sed la nombrojn de ĉeloj, kies valorojn vi bezonas aldoni. Ni diru "= B2 + C2".

Nature, devas esti iuj nombroj en B2 kaj C2, alie Excel montros al ni en ĉelo A1 la rezulto estas 0.

Kaj unu pli grava punkto ...

Kiam vi kopias ĉelon en kiu ekzistas formulo, ekzemple A1 - kaj almetu ĝin en alian ĉelon - ne la "5" estas kopiita, sed la formulo mem!

Plie, la formulo ŝanĝiĝos en rekta proporcio: i.e. se A1 estas kopiita al A2, tiam la formulo en ĉelo A2 estos "= B3 + C3". Excel aŭtomate ŝanĝas vian formulon mem: se A1 = B2 + C2, tiam estas logike, ke A2 = B3 + C3 (ĉiuj nombroj pliiĝas je 1).

La rezulto, laŭ iu, estas en A2 = 0, ĉar ĉeloj B3 kaj C3 ne estas difinitaj, kaj do egalaj al 0.

Tiel, vi povas skribi la formulon unu fojon, kaj tiam kopii ĝin al ĉiuj ĉeloj de la dezirata kolumno - kaj Excel kalkulos en ĉiu vico de via tablo!

Se vi ne volas, ke B2 kaj C2 ŝanĝu dum kopiado kaj ĉiam estu ligitaj al ĉi tiuj ĉeloj, nur aldonu al ili la "$" ikonon. Ekzemplo estas sube.

Tiel, kie ajn vi kopias ĉelon A1, ĝi ĉiam raportos al la ligitaj ĉeloj.

2. Aldono de valoroj en vicoj (formuloj SUMM kaj SUMMESLIMN)

Kompreneble vi povas aldoni ĉiun ĉelon per la formulo A1 + A2 + A3, ktp. Sed por ne suferi, ekzistas speciala formulo en Excel, kiu aldonas ĉiujn valorojn en la ĉeloj, kiujn vi elektas!

Prenu simplan ekzemplon. Ekzistas kelkaj specoj de varoj en stoko, kaj ni scias kiom multe ĉiu produkto estas individue en kg. estas en stoko Ni provu kalkuli, sed kiom estas en kg. kargo en provizo

Por fari tion, iru al la ĉelo, en kiu aperos la rezulto kaj skribu la formulon: "= SUMO (C2: C5)". Vidu ekrankopion sube.

Rezulte, ĉiuj ĉeloj en la elektita gamo estos resumitaj, kaj vi vidos la rezulton.

2.1. Aldono al la kondiĉo (kun kondiĉoj)

Nun imagu, ke ni havas certajn kondiĉojn, t.e. aldonu ne ĉiujn valorojn en la ĉeloj (Kg, en stoko), sed nur certaj, ekzemple, kun prezo (1 kg.) malpli ol 100.

Estas bonega formulo por ĉi tio. "SUMMESLIMN". Tuj ekzemplo, kaj poste klarigo pri ĉiu simbolo en la formulo.

= SUMMES (C2: C5; B2: B5; "<100")kie:

C2: C5 - tiu kolumno (tiuj ĉeloj) aldonitaj;

B2: B5 - la kolumno per kiu la kondiĉo estos kontrolita (t.e. prezo, ekzemple, malpli ol 100);

"<100" - la kondiĉo mem, rimarku, ke la kondiĉo estas skribita inter kompiloj.

Estas nenio komplika en ĉi tiu formulo, la ĉefa afero estas observi proporciecon: C2: C5; B2: B5 - prave; C2: C6; B2: B5 - malĝusta. I.e. la suma sumeco kaj la gamo de kondiĉoj devas esti proporciaj, alie la formulo redonos eraron.

Grava! Povas esti multaj kondiĉoj por la sumo, t.e. Vi povas kontroli ne per la 1-a kolumno, sed antaŭ 10, starigante multajn kondiĉojn.

3. Kalkulado de la nombro de vicoj, kiuj kontentigas la kondiĉojn (la formulo estas KONTENITA)

Tre ofta tasko: kalkuli ne la sumon de la valoroj en la ĉeloj, sed la nombron de tiaj ĉeloj, kiuj kontentigas iujn kondiĉojn. Foje, estas multaj kondiĉoj.

Kaj do ... ni komencu.

En la sama ekzemplo, ni provu kalkuli la nombron da havaĵoj kun prezo pli granda ol 90 (se vi rigardas, vi povas diri, ke ekzistas 2 tiaj produktoj: mandarinoj kaj oranĝoj).

Por kalkuli la varojn en la dezirata ĉelo, ni skribis la sekvan formulon (vidu supre):

= RAKONTO (B2: B5; "> 90")kie:

B2: B5 - la atingo, per kiu ili estos kontrolitaj, laŭ la kondiĉo difinita de ni;

">90" - la kondiĉo mem estas kovrita inter kompiloj.

Nun ni provu iom komplikigi nian ekzemplon, kaj aldonu konton laŭ unu plia kondiĉo: kun prezo pli ol 90 + la kvanto en la magazeno estas malpli ol 20 kg.

La formulo prenas la formon:

= COUNTIFLY (B2: B6; "> 90"; C2: C6; "<20")

Ĉi tie ĉio restas la sama, krom unu alia kondiĉo (C2: C6; "<20") Parenteze, povas esti multaj tiaj kondiĉoj!

Estas klare, ke neniu skribos tiajn formulojn por tiel malgranda tablo, sed por tablo de kelkcent vicoj, ĉi tio estas tute alia afero. Ekzemple, ĉi tiu tablo estas pli ol vida.

4. Serĉo kaj anstataŭigo de valoroj de unu tablo al alia (VLOOKUP-formulo)

Imagu, ke nova tablo alvenis al ni, kun novaj prezaj etikedoj por la produkto. Nu, se la eroj estas 10-20, vi povas permane restarigi ilin ĉiujn. Kaj se estas centoj da tiaj artikoloj? Ĝi estas multe pli rapida se Excel sendepende troviĝas en la kongruaj nomoj de unu tablo al alia, kaj tiam kopiis la novajn prezajn etikedojn al nia malnova tablo.

Por tia tasko uziĝas la formulo VPR. Samtempe, li estis "saĝa" kun la logikaj formuloj "IF" ĝis li renkontis ĉi tiun mirindan aferon!

Do, ni komencu ...

Jen nia ekzemplo + nova tablo kun prezaj etikedoj. Nun ni devas aŭtomate anstataŭigi la novajn prezajn etikedojn de la nova tablo al la malnova (la novaj prezaj etikedoj estas ruĝaj).

Metu la kursoron en ĉelon B2 - t.e. en la unua ĉelo, kie ni aŭtomate ŝanĝos la prezan etikedon. Tuj poste, ni skribas la formulon, kiel en la suba ekrankopio (post la ekrankopio estos detala klarigo pri ĝi).

= VLOOKUP (A2; $ D $ 2: $ E $ 5; 2)kie

A2 - la valoron, kiun ni serĉos por preni novan prezan etikedon. En nia kazo, ni serĉas la vorton "pomoj" en la nova tablo.

$ D $ 2: $ E $ 5 - tute elektu nian novan tablon (D2: E5, la elekto iras de supra maldekstra angulo al malsupra dekstra diagonalo), t.e. kie la serĉo efektiviĝos. La signo "$" en ĉi tiu formulo estas necesa por ke vi kopiu ĉi tiun formulon al aliaj ĉeloj - D2: E5 ne ŝanĝu!

Grava! La serĉado de la vorto "pomoj" efektiviĝos nur en la unua kolumno de via elektita tablo, en ĉi tiu ekzemplo oni serĉos "pomojn" en la kolumno D.

2 - Kiam la vorto "pomoj" troviĝas, la funkcio devas scii, de kiu kolumno de la elektita tablo (D2: E5) kopii la deziratan valoron. En nia ekzemplo, kopiu el kolumno 2 (E), ĉar en la unua kolumno (D) ni serĉis. Se via elektita tablo por la serĉo konsistos el 10 kolumnoj, tiam la unua kolumno serĉos, kaj de 2 ĝis 10 kolumnoj - vi povas elekti la numeron por kopii.

Al la formulo = VLOOKUP (A2; $ D $ 2: $ E $ 5; 2) novaj valoroj anstataŭigitaj per aliaj produktaj nomoj - simple kopiu ĝin al aliaj ĉeloj en la kolumno per la prezaj etikedoj de la produkto (en nia ekzemplo kopiu al ĉeloj B3: B5). La formulo aŭtomate serĉos kaj kopios la valoron el la kolumno de la nova tablo, kiun vi bezonas.

5. Konkludo

En ĉi tiu artikolo, ni ekzamenis la bazojn de laborado kun Excel, kiel komenci verki formulojn. Ili donis ekzemplojn de la plej oftaj formuloj, kiujn plej multaj homoj laboras en Excel ofte devas labori.

Mi esperas, ke la malmuntitaj ekzemploj estos utilaj al iu kaj helpos rapidigi sian laboron. Bonan eksperimenton!

PS

Kaj kiujn formulojn vi uzas? Ĉu eblas iel simpligi la formulojn donitajn en la artikolo? Ekzemple, ĉe malfortaj komputiloj, kiam iuj valoroj ŝanĝiĝas en grandaj tabloj, kie oni faras kalkulojn aŭtomate, la komputilo frostiĝas dum kelkaj sekundoj, rakontante kaj montras novajn rezultojn ...