Kif tikteb formula fl-Excel? Taħriġ. L-iktar formuli meħtieġa

Wara nofsinhar it-tajba

Kien hemm darba, li tikteb formula waħdek fl-Excel kienet xi ħaġa inkredibbli għalija. U minkejja li spiss kelli naħdem f'dan il-programm, jien ma mlejt xejn ħlief it-test ...

Kif irriżulta, ħafna mill-formuli huma xejn ikkumplikati u tista 'faċilment taħdem magħhom, anke għal utent tal-kompjuter novizzi. Fl-artiklu, biss, nixtieq niżvela l-aktar formuli meħtieġa, li magħhom ħafna drabi jkolli naħdem ...

Mela, ejja nibdew ...

1. Operazzjonijiet bażiċi u punti bażiċi. Tgħallem il-baŜi ta 'Excel.

L-azzjonijiet kollha fl-artikolu se jintwerew fil-verżjoni Excel 2007.

Wara li tibda l-programm Excel - tieqa tidher b'ħafna ċelloli - it-tabella tagħna. Il-karatteristika ewlenija tal-programm hija li jista 'jaqra (bħala kalkulatur) il-formuli tiegħek li tikteb. Mill-mod, tista 'żżid formula ma' kull ċellula!

Il-formula trid tibda bis-sinjal "=". Din hija prerekwiżit. Imbagħad tikteb dak li għandek bżonn tikkalkula: pereżempju, "= 2 + 3" (mingħajr kwotazzjonijiet) u agħfas il-buttuna Enter - bħala riżultat, tara li r-riżultat "5" jidher fiċ-ċellula. Ara l-screenshot hawn taħt.

Importanti! Minkejja l-fatt li n-numru "5" huwa miktub fiċ-ċellola A1, huwa kkalkulat bil-formula ("= 2 + 3"). Jekk fiċ-ċellola li jmiss sempliċement ikteb "5" fit-test - mela meta tdur fuq din iċ-ċellula fl-editur tal-formula (linja hawn fuq, Fx) - se tara n-numru ewlieni "5".

Issa immaġina li f'ċellula tista 'tikteb mhux biss il-valur 2 + 3, iżda wkoll in-numri ta' ċelloli li l-valuri tagħhom trid iżżid. Ejja ngħidu "= B2 + C2".

Naturalment, irid ikun hemm xi numri fil-B2 u C2, inkella Excel jurina fiċ-ċellola A1 ir-riżultat huwa 0.

U punt aktar importanti ...

Meta tikkopja ċellola li fiha hemm formula, pereżempju A1 - u waħħalha f'ċellula oħra - mhuwiex il-valur "5" li huwa kkupjat, iżda l-formula nnifisha!

Barra minn hekk, il-formula tinbidel fi proporzjon dirett: i.e. jekk A1 jiġi kkupjat A2, allura l-formula fiċ-ċellola A2 se tkun "= B3 + C3". Excel awtomatikament jibdel il-formula tiegħek innifsu: jekk A1 = B2 + C2, allura huwa loġiku li A2 = B3 + C3 (in-numri kollha jiżdiedu b'1).

Ir-riżultat, bil-mod, huwa f'A2 = 0, minħabba iċ-ċelloli B3 u C3 mhumiex definiti, u għalhekk daqs 0.

Għalhekk, tista 'tikteb il-formula darba, u mbagħad tikkopjaha fiċ-ċelloli kollha tal-kolonna mixtieqa - u Excel tikkalkula f'kull ringiela tat-tabella tiegħek!

Jekk ma tridx li B2 u C2 jinbidlu waqt l-ikkupjar u dejjem tkun imwaħħla ma 'dawn iċ-ċelloli, allura żid magħhom l-ikona "$". Eżempju hawn taħt.

Dan il-mod, kull fejn tikkopja ċ-ċellula A1, hija dejjem tirreferi għaċ-ċelloli marbuta.

2. Żieda ta 'valuri f'ringieli (formuli SUMM u SUMMESLIMN)

Dażgur, tista 'żżid kull ċellula billi tagħmel il-formula A1 + A2 + A3, eċċ. Iżda biex ma jsofrux, hemm formula speċjali fl-Excel li żżid il-valuri kollha fiċ-ċelloli li tagħżel!

Ħu eżempju sempliċi. Hemm diversi tipi ta 'oġġetti fil-ħażna, u nafu kemm kull prodott huwa individwalment f'kg. huwa fl-istokk. Ejja nippruvaw nikkalkulaw, imma kemm huwa kollu f'kg. tagħbija fl-istokk.

Biex tagħmel dan, mur fiċ-ċellola li fiha se jintwera r-riżultat u ikteb il-formula: "= SUMA (C2: C5)". Ara l-screenshot hawn taħt.

Bħala riżultat, iċ-ċelloli kollha fil-firxa magħżula se jingħaddu flimkien, u tara r-riżultat.

2.1. Żieda mal-kondizzjoni (b'kundizzjonijiet)

Issa immaġina li għandna ċerti kundizzjonijiet, i.e. żid mhux il-valuri kollha fiċ-ċelloli (Kg, fl-istokk), iżda ċerti biss, ngħidu aħna, bi prezz (1 kg.) ta 'inqas minn 100.

Hemm formula kbira għal dan. "SUMMESLIMN"Immedjatament eżempju, u mbagħad spjegazzjoni ta 'kull simbolu fil-formula.

= SOMMI (C2: C5; B2: B5; "<100")fejn:

C2: C5 - dik il-kolonna (dawk iċ-ċelloli) li se jiżdiedu;

B2: B5 - il-kolonna li biha l-kundizzjoni tiġi kkontrollata (i.e. il-prezz, per eżempju, inqas minn 100);

"<100" - il-kondizzjoni nnifisha, innota li l-kundizzjoni hija miktuba fil-virgoletti.

F'din il-formula m'hemm xejn ikkumplikat, il-ħaġa ewlenija hija li tosserva proporzjonalità: C2: C5; B2: B5 - it-tajjeb; C2: C6; B2: B5 - ħażin. I.e. il-firxa tas-somma u l-firxa tal-kondizzjonijiet għandhom ikunu proporzjonati, inkella l-formula tirritorna żball.

Importanti! Jista 'jkun hemm ħafna kundizzjonijiet għas-somma, i.e. Tista 'tiċċekkja mhux mill-1 kolonna, iżda minn 10 immedjatament, billi tissettja ħafna kundizzjonijiet.

3. Jingħadd in-numru ta 'ringieli li jissodisfaw il-kundizzjonijiet (il-formula hija COUNTIFLY)

Kompitu pjuttost komuni: tikkalkula mhux is-somma tal-valuri fiċ-ċelloli, iżda n-numru ta 'ċelloli bħal dawn li jissodisfaw ċerti kundizzjonijiet. Kultant, hemm ħafna kundizzjonijiet.

U hekk ... ejja nibdew.

Fl-istess eżempju, ejja nippruvaw nikkalkulaw in-numru ta 'oġġetti bi prezz ikbar minn 90 (jekk tħares, tista' tgħid li hemm 2 prodotti bħal dawn: mandolinini u larinġ).

Biex tgħodd il-merkanzija fiċ-ċellola mixtieqa, kiteb il-formula li ġejja (ara hawn):

= KONTABILTAR (B2: B5; "> 90")fejn:

B2: B5 - il-firxa li biha jkunu se jiġu kkontrollati, skond il-kondizzjoni stabbilita minn għandna;

">90" - il-kondizzjoni nnifisha hija magħluqa fil-virgoletti.

Issa ejja nippruvaw nikkomplikaw l-eżempju tagħna ftit, u żid kont skond kundizzjoni waħda oħra: bi prezz ta 'aktar minn 90 + il-kwantità fil-maħżen hija inqas minn 20 kg.

Il-formula tieħu l-forma:

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

Hawn kollox jibqa 'l-istess, ħlief għal kundizzjoni oħra (C2: C6; "<20") Mill-mod, jista 'jkun hemm ħafna kundizzjonijiet bħal dawn!

Huwa ċar li ħadd ma jikteb formuli bħal dawn għal tabella żgħira, iżda għal tabella ta 'bosta mijiet ta' ringieli, din hija kwistjoni kompletament differenti. Pereżempju, din it-tabella hija iktar milli viżwali.

4. Tiftix u sostituzzjoni ta 'valuri minn tabella għal oħra (formula VLOOKUP)

Immaġina li tabella ġdida waslet għalina, bit-tikketti tal-prezz ġodda għall-prodott. Ukoll, jekk l-oġġetti huma 10-20, tista 'terġa' tissettjahom kollha. U jekk hemm mijiet ta 'oġġetti bħal dawn? Huwa ferm aktar mgħaġġel jekk Excel jinstab b'mod indipendenti fl-ismijiet li jaqblu minn tabella għal oħra, u mbagħad ikkupja t-tikketti tal-prezz il-ġodda fit-tabella l-antika tagħna.

Għal kompitu bħal dan, tintuża l-formula VPR. F'ħin minnhom, huwa kien "għaqli" bil-formuli loġiċi "IF" sakemm iltaqa 'ma' din il-ħaġa sabiħa!

Mela, ejja nibdew ...

Hawn hu l-eżempju tagħna + tabella ġdida bit-tikketti tal-prezz. Issa rridu nissostitwixxu awtomatikament it-tikketti tal-prezz il-ġodda mit-tabella l-ġdida għal dik qadima (it-tikketti l-ġodda tal-prezz huma ħomor).

Poġġi l-cursor fiċ-ċellola B2 - i.e. fl-ewwel ċellola, fejn għandna bżonn nibdlu l-prezz tag awtomatikament. Sussegwentement, niktbu l-formula, bħal fil-screenshot hawn taħt (wara l-screenshot se jkun hemm spjegazzjoni dettaljata ta 'dan).

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

A2 - il-valur li aħna se nħarsu sabiex nieħdu tikketta tal-prezz ġdida. Fil-każ tagħna, qegħdin infittxu l-kelma "tuffieħ" fit-tabella l-ġdida.

$ D $ 2: $ E $ 5 - agħżel kompletament it-tabella l-ġdida tagħna (D2: E5, l-għażla tmur mill-kantuniera ta 'fuq tax-xellug għad-djagonali t'isfel tal-lemin), i.e. fejn se ssir it-tfittxija. Is-sinjal "$" f'din il-formula huwa meħtieġ sabiex meta tikkopja din il-formula f'ċelloli oħra - D2: E5 ma tinbidilx!

Importanti! It-tfittxija għall-kelma "tuffieħ" titwettaq biss fl-ewwel kolonna tat-tabella magħżula tiegħek, f'dan l-eżempju, "tuffieħ" għandha titfittex fil-kolonna D.

2 - Meta tinstab il-kelma "tuffieħ", il-funzjoni trid tkun taf minn liema kolonna tat-tabella magħżula (D2: E5) tikkopja l-valur mixtieq. Fl-eżempju tagħna, ikkopja mill-kolonna 2 (E), għaliex fl-ewwel kolonna (D) fittixna. Jekk it-tabella magħżula tiegħek għat-tiftix se tkun magħmula minn 10 kolonni, allura l-ewwel kolonna se tfittex, u minn 2 sa 10 kolonni - tista 'tagħżel in-numru li tikkopja.

Biex il-formula = VLOOKUP (A2; $ D $ 2: $ E $ 5; 2) Valuri sostitwiti ġodda għal ismijiet ta 'prodotti oħra - ikkopjah biss f'ċelloli oħra fil-kolonna bit-tikketti tal-prezz tal-prodott (fl-eżempju tagħna, ikkopja liċ-ċelloli B3: B5). Il-formula awtomatikament tfittex u tikkopja l-valur mill-kolonna tat-tabella l-ġdida li għandek bżonn.

5. Konklużjoni

F'dan l-artikolu, eżaminajna l-bażi ta 'ħidma ma' Excel, kif nibdew nikteb il-formuli. Huma taw eżempji tal-aktar formuli komuni li ħafna nies li jaħdmu fl-Excel ħafna drabi jkollhom jaħdmu magħhom.

Nispera li l-eżempji żarmati jkunu utli għal xi ħadd u jgħinu biex iħaffu x-xogħol tiegħu. Have esperiment tajjeb!

PS

U liema formuli tuża? Huwa possibbli li b'xi mod tissimplifika l-formuli mogħtija fl-artiklu? Pereżempju, fuq kompjuters dgħajfa, meta xi valuri jinbidlu f'tabelli kbar fejn il-kalkoli jitwettqu awtomatikament, il-kompjuter jiffriża għal ftit sekondi, u jirrakkonta u juri riżultati ġodda ...