Kiel skribi formulon en Excel? Trejnado La plej necesaj formuloj

Bonan tagon

Iam, skribi formulon en Excel mem estis io nekredebla por mi. Kaj eĉ malgraŭ la fakto, ke mi ofte devis labori en ĉi tiu programo, mi tute ne havis krom tekston ...

Rezulte, la plej multaj el la formuloj neniel komplikas kaj estas facile labori kun ili, eĉ por komencanto-komputiluzanto. En la artikolo, ĝuste, mi ŝatus malkaŝi la plej necesajn formulojn, per kiuj oni ofte devas labori ...

Do komencu ...

1. Bazaj operacioj kaj bazaj elementoj. Excel trejnado.

Ĉ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 trajto de la programo estas, ke ĝi povas legi (kiel kalkulilon) viajn formulojn, kiujn vi skribas. Parenteze, vi povas aldoni formulon al ĉiu ĉelo!

La formulo devas komenciĝi per la signo "=". Ĉi tio estas antaŭkondiĉo. Poste vi skribas, kion vi bezonas kalkuli: ekzemple, "= 2 + 3" (sen citaĵoj) kaj premu Entajpu - tial vi vidos, ke la rezulto aperis en la ĉelo "5". Vidu sube ĉi tien.

!I estas grava! Malgraŭ la fakto, ke la nombro "5" estas skribita en ĉelo A1, ĝi estas kalkulita per la formulo ("= 2 + 3"). Se en la venonta ĉelo vi simple skribas "5" kun la teksto - tiam kiam vi ŝvebas la kursoron sur ĉi tiun ĉelon - en la redaktilo (la linio supre, Fx) - vi vidos priman numeron "5".

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

Nature, devus esti iuj numeroj en B2 kaj C2, alie Excel montros al ni en ĉelo A1 rezulto egala al 0.

Kaj unu pli grava noto ...

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

Plie, la formulo ŝanĝiĝos rekte: se A1 estas kopiita al A2 - tiam la formulo en ĉelo A2 egalos al "= B3 + C3". Excel aŭtomate ŝanĝas vian formulon mem: se A1 = B2 + C2, tiam estas logika, ke A2 = B3 + C3 (ĉiuj nombroj pliigitaj je 1).

La rezulto, cetere, estas A2 = 0, ekde ĉeloj B3 kaj C3 ne estas difinitaj, kaj tial egalas al 0.

Tiamaniere vi povas skribi formulon unufoje kaj poste kopii ĝin al ĉiuj ĉeloj de la dezirata kolumno - kaj Excel mem kalkulos en ĉiu vico de via tablo!

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

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

2. Aldono de valoroj en ĉenoj (formulo SUMO kaj SUMMESLIMN)

Vi povas, kompreneble, aldoni ĉiun ĉelon, farante la formulon A1 + A2 + A3, ktp. Sed por ne tiom suferi, en Excel ekzistas speciala formulo, kiu aldonos ĉiujn valorojn en la ĉeloj, kiujn vi elektas!

Prenu simplan ekzemplon. Estas pluraj eroj en stoko, kaj ni scias kiom multe estas ĉiu ero en kg. estas en stoko. Ni provu kalkuli kiom multe en kg. kargo en provizo.

Por fari tion, iru al la ĉelo, kie la rezulto estos montrata kaj skribu la formulon: "= SUMO (C2: C5)". Vidu sube ĉi tien.

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

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

Nun imagu, ke ni havas iujn kondiĉojn, te. ne necesas aldoni ĉiujn valorojn en la ĉeloj (Kg, en stoko), sed nur tiujn difinitajn, ekzemple, kun prezo (1 kg.) malpli ol 100.

Por ĉi tio estas mirinda formulo "SUMMESLIMN"Tuj ekzemplo, kaj tiam klarigo de ĉiu simbolo en la formulo.

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

C2: C5 - Tiu kolumno (tiuj ĉeloj), kiu estos aldonita;

B2: B5 - la kolumno, sur kiu la kondiĉo estos kontrolita (te la prezo, ekzemple, malpli ol 100);

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

Estas nenio komplikita en ĉi tiu formulo, la ĉefa afero estas observi proporciecon: C2: C5; B2: B5 estas ĝusta; C2: C6; B2: B5 estas malĝusta. Tio estas sumada gamo kaj kondiĉa intervalo devas esti proporciaj, alie la formulo redonos eraron.

!I estas grava! Eble estas multaj kondiĉoj por la sumo, te. Vi povas kontroli ne per la unua kolumno, sed per 10 samtempe, per specifo de aro de kondiĉoj.

3. Kalkulado de la nombro de vicoj plenumantaj la kondiĉojn (la formulo COUNTIFSLIMN)

Sufiĉe ofta tasko estas kalkuli ne la sumon de la valoroj en la ĉeloj, sed la nombron de tiaj ĉeloj, kiuj plenumas certajn kondiĉojn. Foje, multaj kondiĉoj.

Do ... komencu.

En la sama ekzemplo, ni provos kalkuli la kvanton de la produkta nomo kun prezo de pli ol 90 (se vi rigardas ĝin, vi povas diri ke estas 2 tiaj produktoj: mandarinoj kaj oranĝoj).

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

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

B2: B5 - la teritorio pri kiu ili estos kontrolataj laŭ la kondiĉo, kiun ni fiksis;

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

Nun ni provos iomete kompliki nian ekzemplon, kaj aldonos fakturon laŭ plia kondiĉo: kun prezo de pli ol 90 + la kvanto en stoko estas malpli ol 20 kg.

La formulo prenas la formon:

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

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

Estas klare, ke por tia malgranda tablo neniu povos skribi tiajn formulojn, sed por tabelo de pluraj cent vicoj - ĉi tio estas alia afero tute. Ekzemple, ĉi tiu tablo estas pli ol klara.

4. Serĉo kaj anstataŭigo de valoroj de unu tabulo al alia (formulo CDF)

Imagu, ke nova tablo venis al ni, kun novaj prezaj etikedoj por varoj. Nu, se la nomoj de 10-20 - kaj vi povas "forgesi" mane ĉiujn. Kaj se estas centoj da tiaj nomoj? Multe pli rapide se Excel sendepende trovis kongruajn nomojn de unu tablo al alia, kaj poste kopiis la novajn prezajn etikedojn al nia malnova tablo.

Por ĉi tiu tasko oni uzas la formulon Vpr. Iam, li mem "saĝe" kun la logikaj formuloj "SE" ankoraŭ ne renkontis ĉi tiun mirindan aferon!

Do komencu ...

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

Metu la kursoron en ĉelon B2 - te. en la unua ĉelo, kie ni devas ŝanĝi la prezan aŭtomaton. Poste ni skribas la formulon kiel en la suba ekrankopio (post la ekrankopio estos detala klarigo por ĝi).

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

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

$ D $ 2: $ E $ 5 - Ni elektas tute nian novan tablon (D2: E5, la elekto iras de supre maldekstre al malsupre dekstre en diagonala), te. kie la serĉado estos farita. La "$" signo en ĉi tiu formulo estas necesa por kopii ĉi tiun formulon al aliaj ĉeloj - D2: E5 ne ŝanĝiĝas!

!I estas grava! La serĉado de la vorto "pomoj" okazos nur en la unua kolumno de via elektita tablo; en ĉi tiu ekzemplo, la "pomoj" estos serĉataj en kolumno D.

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

Al formulo = CDF (A2; $ D $ 2: $ E $ 5; 2) anstataŭigis novajn valorojn por aliaj produktaj nomoj - simple kopiu ĝin al aliaj ĉeloj de la kolumno kun produktaj prezaj etikedoj (en nia ekzemplo, kopiu al ĉeloj B3: B5). La formulo aŭtomate serĉos kaj kopios la valoron de la kolumno de la nova tablo, kiun vi bezonas.

5. Konkludo

En ĉi tiu artikolo, ni rigardis la bazajn elementojn de laboro kun Excel de kiel komenci skribi formulojn. Ili donis ekzemplojn de la plej oftaj formuloj kun kiuj plej ofte laboras kun la plimulto de tiuj, kiuj laboras en Excel.

Mi esperas, ke la ekzemploj, kiuj estis analizitaj, estos utilaj por iu kaj helpos rapidigi lian laboron. Sukcesaj eksperimentoj!

PS

Kaj kiaj formuloj vi uzas, ĉu eblas iel simpligi la formulojn donitajn en la artikolo? Ekzemple, sur malfortaj komputiloj, kiam iuj valoroj ŝanĝiĝas en grandaj tabloj, kie kalkuloj estas plenumataj aŭtomate, la komputilo blokas dum kelkaj sekundoj, recalculas kaj montras novajn rezultojn ...