Ñîçäàíèå ôîðìóë â Excel
· Ïîðÿäîê ââîäà ôîðìóëû
· Îòíîñèòåëüíûå, àáñîëþòíûå è ñìåøàííûå ññûëêè
· Èñïîëüçîâàíèå òåêñòà â ôîðìóëàõ
Òåïåðü ïåðåõîäèì ê ñàìîìó èíòåðåñíîìó - ñîçäàíèþ ôîðìóë. Ñîáñòâåííî ýòî òî, ðàäè ÷åãî è ðàçðàáàòûâàëèñü ýëåêòðîííûå òàáëèöû.
Ïîðÿäîê ââîäà ôîðìóëû
Ââîäèòü ôîðìóëó íàäî ñî çíàêà ðàâåíñòâà. Ýòî íàäî äëÿ òîãî, ÷òîáû Excel ïîíÿë, ÷òî â ÿ÷åéêó ââîäèòñÿ èìåííî ôîðìóëà, à íå äàííûå.
Âûäåëèì ïðîèçâîëüíóþ ÿ÷åéêó, íàïðèìåð À1.  ñòðîêå ôîðìóë ââåäåì =2+3 è íàæìåì Enter.  ÿ÷åéêå ïîÿâèòñÿ ðåçóëüòàò (5). À â ñòðîêå ôîðìóë îñòàíåòñÿ ñàìà ôîðìóëà.
Ïîýêñïåðèìåíòèðóéòå ñ ðàçëè÷íûìè àðèôìåòè÷åñêèìè îïåðàòîðàìè: ñëîæåíèÿ (+), âû÷èòàíèÿ (-), óìíîæåíèÿ (*), äåëåíèÿ (/). ×òîáû èõ ïðàâèëüíî èñïîëüçîâàòü íàäî ÷åòêî ïðåäñòàâëÿòü èõ ïðèîðèòåò.
-  ïåðâóþ î÷åðåäü âûïîëíÿþòñÿ âûðàæåíèÿ âíóòðè ñêîáîê.
- Óìíîæåíèå è äåëåíèå èìåþò áîëåå âûñîêèé ïðèîðèòåò ÷åì ñëîæåíèå è âû÷èòàíèå.
- Îïåðàòîðû ñ îäèíàêîâûì ïðèîðèòåòîì âûïîëíÿþòñÿ ñëåâà íàïðàâî.
Ìîé âàì ñîâåò - ÈÑÏÎËÜÇÓÉÒÅ ÑÊÎÁÊÈ.  ýòîì ñëó÷àå âû îáåçîïàñèòå ñåáÿ îò ñëó÷àéíîé îøèáêè â âû÷èñëåíèÿõ ñ îäíîé ñòîðîíû, à ñ äðóãîé - ñêîáêè çíà÷èòåëüíî îáëåã÷àþò ÷òåíèå è àíàëèç ôîðìóë. Åñëè â ôîðìóëå êîëè÷åñòâî çàêðûâàþùèõ è îòêðûâàþùèõ ñêîáîê íå ñîâïàäàåò, Excel âûäàñò ñîîáùåíèå îá îøèáêå è ïðåäëîæèò âàðèàíò åå èñïðàâëåíèÿ. Ñðàçó ïîñëå ââîäà çàêðûâàþùåé ñêîáêè Excel îòîáðàæàåò æèðíûì øðèôòîì (èëè äðóãèì öâåòîì) ïîñëåäíþþ ïàðó ñêîáîê, ÷òî î÷åíü óäîáíî ïðè íàëè÷èè â ôîðìóëå áîëüøîãî êîëè÷åñòâà ñêîáîê.
Òåïåðü äàâàéòå ïîïðîáóåì ïîðàáîòàòü ñ èñïîëüçîâàíèåì â ôîðìóëàõ ññûëîê íà äðóãèå ÿ÷åéêè.
Ââåäèòå â ÿ÷åéêó À1 ÷èñëî 10, à â ÿ÷åéêó À2 - ÷èñëî 15.  ÿ÷åéêå À3 ââåäèòå ôîðìóëó =À1+À2.  ÿ÷åéêå À3 ïîÿâèòñÿ ñóììà ÿ÷ååê À1 è À2 - 25. Ïîìåíÿéòå çíà÷åíèÿ ÿ÷ååê À1 è À2 (íî íå À3!). Ïîñëå ñìåíû çíà÷åíèé â ÿ÷åéêàõ À1 è À2 àâòîìàòè÷åñêè ïåðåñ÷èòûâàåòñÿ çíà÷åíèå ÿ÷åéêè À3 (ñîãëàñíî ôîðìóëû).
Äàáû íå îøèáèòüñÿ ïðè ââîäå àäðåñîâ ÿ÷ååê, ìîæíî èñïîëüçîâàòü ïðè ââîäå ññûëîê ìûøü.  íàøåì ñëó÷àå íàäî ïðîäåëàòü ñëåäóþùåå:
- Âûäåëèòü ÿ÷åéêó À3 è ââåñòè â ñòðîêå ôîðìóë çíàê ðàâåíñòâà.
- Ùåëêíóòü íà ÿ÷åéêå À1 è ââåñòè çíàê ïëþñ.
- Ùåëêíóòü íà ÿ÷åéêå À2 è íàæàòü Enter.
Ðåçóëüòàò áóäåò àíàëîãè÷íûì.
Îòíîñèòåëüíûå, àáñîëþòíûå è ñìåøàííûå ññûëêè
Îòíîñèòåëüíàÿ ññûëêà óêàçûâàåò íà ÿ÷åéêó, ñîãëàñíî åå ïîëîæåíèÿ îòíîñèòåëüíî ÿ÷åéêè, ñîäåðæàùåé ôîðìóëó. Îáîçíà÷åíèå îòíîñèòåëüíîé ÿ÷åéêè - À1.
Àáñîëþòíàÿ ññûëêà óêàçûâàåò íà ÿ÷åéêó, ìåñòîïîëîæåíèå êîòîðîé íåèçìåííî. Îáîçíà÷åíèå àáñîëþòíîé ÿ÷åéêè - $A$1.
Ñìåøàííàÿ ññûëêà ñîäåðæèò êîìáèíàöèþ îòíîñèòåëüíîé è àáñîëþòíîé ññûëîê - $A1, A$1.
Äëÿ áûñòðîãî èçìåíåíèÿ òèïà ññûëêè èñïîëüçóåòñÿ êëàâèøà F4. Ââåäèòå â ÿ÷åéêó À1 ëþáîå ÷èñëî.  ÿ÷åéêó À2 ââåäèòå ôîðìóëó =À1. Çàòåì íàæèìàéòå êëàâèøó F4. Ïîñëå êàæäîãî íàæàòèÿ êëàâèøè òèï ññûëêè áóäåò ìåíÿòüñÿ.
×òîáû ëó÷øå ðàçîáðàòüñÿ â ðàçëè÷èÿõ ññûëîê, äàâàéòå ïîýêñïåðèìåíòèðóåì.
Äëÿ íà÷àëà ðàçáåðåì îòíîñèòåëüíûå ññûëêè. Ââåäèòå â ÿ÷åéêè ñëåäóþùèå çíà÷åíèÿ:
- À1 - 20 Â1 - 200
- À2 - 30 Â2 - 300
 ÿ÷åéêó À3 ââåäèòå ôîðìóëó =À1+À2 è íàæìèòå Enter.
Òåïåðü óñòàíîâèòå êóðñîð íà ïðàâûé íèæíèé óãîë ÿ÷åéêè À3, íàæìèòå ïðàâóþ êíîïêó ìûøè è ïðîòÿíèòå íà ÿ÷åéêó Â3 è îòïóñòèòå êíîïêó ìûøè. Ïîÿâèòñÿ êîíòåêñòíîå ìåíþ, â êîòîðîì íàäî âûáðàòü "Êîïèðîâàòü ÿ÷åéêè".
Ïîñëå ýòîãî çíà÷åíèå ôîðìóëû èç ÿ÷åéêè À3 áóäåò ñêîïèðîâàíî â ÿ÷åéêó Â3. Àêòèâèçèðóéòå ÿ÷åéêó Â3 è ïîñìîòðèòå êàêàÿ ôîðìóëà ïîëó÷èëàñü - Â1+Â2. Ïî÷åìó òàê ïîëó÷èëîñü? Êîãäà ìû çàïèñàëè â ÿ÷åéêå À3 ôîðìóëó À1+À2, Excel èíòåðïðåòèðîâàë ýòó çàïèñü òàê: "Âçÿòü çíà÷åíèÿ èç ÿ÷åéêè, ðàñïîëîæåííîé â òåêóùåì ñòîëáöå íà äâå ñòðîêè âûøå è ñëîæèòü ñî çíà÷åíèåì ÿ÷åéêè, ðàñïîëîæåííîé â òåêóùåì ñòîëáöå íà îäíó ñòðîêó âûøå". Ò.å. ñêîïèðîâàâ ôîðìóëó èç ÿ÷åéêè À3, íàïðèìåð, â ÿ÷åéêó Ñ43, ìû ïîëó÷èì - Ñ41+Ñ42. Âîò â ýòîì ïðåëåñòü îòíîñèòåëüíûõ ññûëîê, ôîðìóëà êàê áû ñàìà ïîäñòðàèâàåòñÿ ïîä íàøè çàäà÷è.
Ðàññìîòðèì òåïåðü àáñîëþòíûå ññûëêè.
Ââåäèòå â ÿ÷åéêè ñëåäóþùèå çíà÷åíèÿ:
- À1 - 20 Â1 - 200
- À2 - 30 Â2 - 300
- Ââåäèòå â ÿ÷åéêó Ñ1 ÷èñëî 5.
 ÿ÷åéêó À3 ââåäèòå ñëåäóþùóþ ôîðìóëó =À1+À2+$C$1. Àíàëîãè÷íûì îáðàçîì ñêîïèðóéòå ôîðìóëó èç À3 â Â3. Ïîñìîòðèòå ÷òî ïîëó÷èëîñü. Îòíîñèòåëüíûå ññûëêè "ïîäñòðîèëèñü" ïîä íîâûå çíà÷åíèÿ, à âîò àáñîëþòíàÿ - îñòàëàñü íåèçìåííîé.
Ïîïðîáóéòå òåïåðü ñàìè ïîýêñïåðèìåíòèðîâàòü ñî ñìåøàííûìè ññûëêàìè è ïîñìîòðèòå êàê îíè ðàáîòàþò. Ìîæíî ññûëàòüñÿ íà äðóãèå ëèñòû òîé æå êíèãè òî÷íî òàê, êàê è íà ÿ÷åéêè òåêóùåãî ëèñòà. Äàæå ìîæíî ññûëàòüñÿ íà ëèñòû äðóãèõ êíèã.  ýòîì ñëó÷àå ññûëêà áóäåò íàçûâàòüñÿ âíåøíåé ññûëêîé.
Íàïðèìåð, ÷òîáû çàïèñàòü â ÿ÷åéêó À1 (Ëèñò 1) ññûëêó íà ÿ÷åéêó À5 (Ëèñò2), íàäî ïðîäåëàòü ñëåäóþùåå:
- âûäåëèòü ÿ÷åéêó À1 è ââåñòè çíàê ðàâåíñòâà;
- ùåëêíèòå íà ÿðëûêå "Ëèñò 2";
- ùåëêíèòå íà ÿ÷åéêå À5 è íàæìèòå êëàâèøó ââîäà;
- ïîñëå ýòîãî áóäåò àêòèâèçèðîâàí îïÿòü Ëèñò 1 è â ÿ÷åéêå À1 ïîÿâèòñÿ ñëåäóþùàÿ ôîðìóëà =Ëèñò2!À5.
Ññûëêà íà ÿ÷åéêó èç äðóãîé êíèãè áóäåò âûãëÿäåòü òàê - =[Êíèãà2]Ëèñò2!À5.
Ðåäàêòèðîâàíèå ôîðìóë ïðîèñõîäèò àíàëîãè÷íî ðåäàêòèðîâàíèþ òåêñòîâûõ çíà÷åíèé â ÿ÷åéêàõ. Ò.å. íàäî àêòèâèçèðîâàòü ÿ÷åéêó ñ ôîðìóëîé âûäåëåíèåì èëè äâîéíûì ùåë÷êîì ìûøè, à çàòåì ïðîèçâåñòè ðåäàêòèðîâàíèå, èñïîëüçóÿ, ïðè íåîáõîäèìîñòè, êëàâèøè Del, Backspace. Ôèêñàöèÿ èçìåíåíèé âûïîëíÿåòñÿ êëàâèøåé Enter.
Èñïîëüçîâàíèå òåêñòà â ôîðìóëàõ
Ñ òåêñòîâûìè çíà÷åíèÿìè ìîæíî âûïîëíÿòü ìàòåìàòè÷åñêèå îïåðàöèè, åñëè òåêñòîâûå çíà÷åíèÿ ñîäåðæàò òîëüêî ñëåäóþùèå ñèìâîëû:
Öèôðû îò 0 äî 9 , + - å Å /
Åùå ìîæíî èñïîëüçîâàòü ïÿòü ñèìâîëîâ ÷èñëîâîãî ôîðìàòèðîâàíèÿ:
$ % ( ) ïðîáåë
Ïðè ýòîì òåêñò äîëæåí áûòü çàêëþ÷åí â äâîéíûå êàâû÷êè.
Íåïðàâèëüíî: =$55+$33
Ïðàâèëüíî: ="$55"+$"33"
Ïðè âûïîëíåíèè âû÷èñëåíèé Excel ïðåîáðàçóåò ÷èñëîâîé òåêñò â ÷èñëîâûå çíà÷åíèÿ, òàê ðåçóëüòàòîì âûøåóêàçàííîé ôîðìóëû áóäåò çíà÷åíèå 88.
Äëÿ îáúåäèíåíèÿ òåêñòîâûõ çíà÷åíèé ñëóæèò òåêñòîâûé îïåðàòîð & (àìïåðñàíä). Íàïðèìåð, åñëè ÿ÷åéêà À1 ñîäåðæèò òåêñòîâîå çíà÷åíèå "Èâàí", à ÿ÷åéêà À2 - "Ïåòðîâ", òî ââåäÿ â ÿ÷åéêó À3 ñëåäóþùóþ ôîðìóëó =À1&À2, ïîëó÷èì "ÈâàíÏåòðîâ".
Äëÿ âñòàâêè ïðîáåëà ìåæäó èìåíåì è ôàìèëèåé íàäî íàïèñàòü òàê =À1&" "&À2.
Àìïåðñàíä ìîæíî èñïîëüçîâàòü äëÿ îáúåäèíåíèÿ ÿ÷ååê ñ ðàçíûìè òèïàìè äàííûõ. Òàê, åñëè â ÿ÷åéêå À1 íàõîäèòñÿ ÷èñëî 10, à â ÿ÷åéêå À2 - òåêñò "ìåøêîâ", òî â ðåçóëüòàòå äåéñòâèÿ ôîðìóëû =À1&À2, ìû ïîëó÷èì "10ìåøêîâ". Ïðè÷åì ðåçóëüòàòîì òàêîãî îáúåäèíåíèÿ áóäåò òåêñòîâîå çíà÷åíèå.
