Ðàáîòà ñ äàòàìè Excel 2007
| Àâòîð: | Èíäûê Èãîðü Âèêòîðîâè÷ |
| e-mail: | exelentc@yandex.ru |
Ýòà ñòàòüÿ ïîñâÿùåíà âîïðîñàì ðàáîòû ñ äàòàìè â Excel 2007. Â íåé ìû ïîñòàðàåìñÿ íàéòè ðåøåíèÿ äëÿ ñàìûõ ïîïóëÿðíûõ ïðîáëåì ïîëüçîâàòåëåé ïðè ðàáîòå ñ äàòàìè.
Êàê ìíå ââåñòè äàòó? ß íàáèðàþ äàòó, à Ýêñåëü ïîêàçûâàåò ÷èñëî!
Íóæíî ïîìíèòü, ÷òî Excel õðàíèò äàòû â âèäå ÷èñåë. Òî åñòü, åñëè Âû ââîäèòå 19.08.2011, Excel ñîõðàíÿåò ýòî çíà÷åíèå êàê 40774. Ýòî ÷èñëî îçíà÷àåò êîëè÷åñòâî äíåé, îòñ÷èòàííûõ îò 1 ÿíâàðÿ 1900 ãîäà.
Åñëè ïðè ââîäå äàòû Excel îòîáðàæàåò ÷èñëî, íåîáõîäèìî âûäåëèòü íóæíóþ ÿ÷åéêó èëè èõ äèàïàçîí, âûçâàòü îêíî Ôîðìàò ÿ÷ååê (íàïðèìåð, ÷åðåç êîíòåêñòíîå ìåíþ), è íà âêëàäêå ×èñëî óñòàíîâèòü ôîðìàò Äàòà.
ß ðàáîòàþ ïðîäàâöîì, è êàæäûé äåíü îòïðàâëÿþ íà÷àëüñòâó îò÷åò ïî ïðîäàæàì. Ïîýòîìó êàæäûé äåíü ìåíÿþ äàòó âðó÷íóþ. Ìîæíî ëè ñäåëàòü òàê, ÷òî áû äàòà ìåíÿëàñü àâòîìàòè÷åñêè?
Êîíå÷íî. Ñïåöèàëüíî äëÿ òàêèõ ñèòóàöèé Excel ïðåäëàãàåò ôîðìóëó ÑÅÃÎÄÍß.  íóæíóþ ÿ÷åéêó íåîáõîäèìî âíåñòè:
=ÑÅÃÎÄÍß()
È åæåäíåâíî îò÷åò áóäåò âûâîäèòü òåêóùóþ äàòó.
À ìíå íóæíî íå ñåãîäíÿ, à çà ïðîøëûé ïîíåäåëüíèê? ×òî ìíå äåëàòü?
Åñëè ñåãîäíÿ ó íàñ òîæå ïîíåäåëüíèê, äîñòàòî÷íî îòíÿòü îò òåêóùåé äàòû 7 äíåé:
=ÑÅÃÎÄÍß()-7
Äëÿ äðóãèõ äíåé íåäåëè íóæíî îòíÿòü êîëè÷åñòâî äíåé ìåæäó ýòèìè äíÿìè íåäåëè.
Ìíå íóæíî, ÷òîáû ôîðìóëà ìîãëà ïîñ÷èòàòü ïåðèîä â êàëåíäàðå. Íàïðèìåð, ÷åëîâåê îòñóòñòâîâàë ñ 15.09.09ã. ïî 31.12.09ã., òî åñòü 16 äíåé â ñåíòÿáðå, ïëþñ åù¸ òðè ìåñÿöà (âåñü îêòÿáðü, íîÿáðü, äåêàáðü), èëè 108 äíåé â îáùåé ñóììå. Êàê íàïèñàòü ôîðìóëó?
Êàê ìû óæå îòìå÷àëè, Excel õðàíèò äàòû êàê êîëè÷åñòâî äíåé, îòñ÷èòàííûõ îò îäíîé òî÷êè. Ïîýòîìó, ÷òîáû íàéòè ðàçíèöó ìåæäó äâóìÿ äàòàìè, äîñòàòî÷íî íàïèñàòü ôîðìóëó àðèôìåòè÷åñêîé ðàçíèöû ìåæäó ÿ÷åéêîé ñ ïîçäíåé äàòîé è ÿ÷åéêîé ñ áîëåå ðàííåé. Íàïðèìåð, â íàøåì ñëó÷àå ÿ÷åéêà À1 ñîäåðæèò äàòó 15.09.09, ÿ÷åéêà À2 - 31.12.09. Ôîðìóëà áóäåò èìåòü âèä:
=À2-À1+1
Åäèíèöó íóæíî äîáàâëÿòü, ïîñêîëüêó 15.09.09 ÷åëîâåê óæå îòñóòñòâîâàë íà ðàáîòå.
 ñëó÷àå, åñëè íóæíî âûâåñòè êîëè÷åñòâî ìåñÿöåâ è äíåé (â íàøåì ïðèìåðå - 3 ìåñÿöà 16 äíåé), ìîæíî âîñïîëüçîâàòüñÿ ôîðìóëàìè ÌÅÑßÖ è ÄÅÍÜ.
Ôîðìóëà ÌÅÑßÖ èçâëåêàåò èç ââåä¸ííîé äàòû íîìåð ìåñÿöà ñ íà÷àëà ãîäà, à ôîðìóëà ÄÅÍÜ - íîìåð äíÿ ñ íà÷àëà ìåñÿöà. Ïîëó÷èâ ýòè íîìåðà èç íàøèõ äàò, ìû ìîæåì ëåãêî íàéòè ìåæäó íèìè ðàçíèöó:
=ÌÅÑßÖ(À2)-ÌÅÑßÖ(A1) - ðåçóëüòàòîì áóäåò 3 =ÄÅÍÜ(B5)-ÄÅÍÜ(A5) - ðåçóëüòàòîì áóäåò 16
Ýòè ÷èñëà ìîæíî âûâåñòè â îäíó ÿ÷åéêó â âèäå "3 ìåñÿöà 16 äíåé" ñ ïîìîùüþ îïåðàíäà & :
="Ïåðèîä îòñóòñòâèÿ ñîñòàâèë "&A2&" ìåñÿöà è "&B2&" äíåé"
èëè ôóíêöèè ÑÖÅÏÈÒÜ:
=ÑÖÅÏÈÒÜ("Ïåðèîä îòñóòñòâèÿ ñîñòàâèë "; A2;" ìåñÿöà è ";B2;" äíåé")
ãäå À2 è Â2 - ÿ÷åéêè, â êîòîðûõ íàõîäÿòñÿ íàøè ôîðìóëû, âû÷èñëÿþùèå êîëè÷åñòâî, ñîîòâåòñòâåííî, ìåñÿöåâ è äíåé.
Åñëè ïåðèîä âêëþ÷àåò ãîäà, â âû÷èñëåíèÿ íåîáõîäèìî âêëþ÷èòü ôóíêöèþ ÃÎÄ.
Ïîìîãèòå ïåðåâåñòè èç ÷èñåë â ãîäà è ìåñÿöû. Íàïðèìåð, ó íàñ åñòü 6,2 è 6,8 - òî åñòü 6 ëåò 2 ìåñÿöà è 6 ëåò 8 ìåñÿöåâ. Íàì íóæíî ïîëó÷èòü ñóììó íå 13 , à 12 ëåò 10 ìåñÿöåâ.
 ýòîì óñëîâèè ïðèñóòñòâóåò îøèáêà. Excel îïðåäåëÿåò ÷èñëà, ââåä¸ííûå ïîñëå çàïÿòîé, êàê äåñÿòè÷íûå äðîáè. À ýòî çíà÷èò, ÷òî ÷èñëî 6,2 îçíà÷àåò íå "6 ëåò 2 ìåñÿöà", à "6 öåëûõ 2 äåñÿòûõ ãîäà", ÷òî ïðèáëèçèòåëüíî äà¸ò 6 ëåò 2 ìåñÿöà è 13 äíåé. Åñëè íóæíûé ïåðèîä âðåìåíè íåâîçìîæíî ââåñòè â ôîðìàòå äàòû, ìû ðåêîìåíäóåì ñîõðàíÿòü öåëûå çíà÷åíèÿ ëåò, ìåñÿöåâ è äíåé â ðàçíûõ ÿ÷åéêàõ - ýòî ïîìîæåò èçáåæàòü îøèáîê â âû÷èñëåíèÿõ, è î÷åíü óïðîñòèò îïåðàöèè. Òàê, ÷òîáû ïîëó÷èòü íóæíûé íàì ðåçóëüòàò â 12 ëåò è 10 ìåñÿöåâ, äîñòàòî÷íî áóäåò ïðîñóììèðîâàòü ñîîòâåòñòâóþùèå ÿ÷åéêè:
Íó à êàê âûâåñòè ýòè ÷èñëà â ñîñòàâå íóæíîé ôðàçû, ìû óæå íàïèñàëè âûøå.
À ÷òî äåëàòü, åñëè ìû ñóììèðóåì áîëüøå ÷åì äâà ïåðèîäà? Òîãäà ó íàñ ïîëó÷àåòñÿ ãëóïîñòü òèïà 37 ëåò è 45 ìåñÿöåâ!
Ñîãëàñíû. Òîãäà äàâàéòå âû÷èñëèì, ñêîëüêî öåëûõ ëåò ñîñòàâëÿþò 45 ìåñÿöåâ, ïðèáàâèì èõ ê 37 ãîäàì, è ðàññ÷èòàåì îñòàòîê ìåñÿöåâ:
Ñíà÷àëà ñ ïîìîùüþ ôîðìóëû ÖÅËÎÅ óçíàåì, ñêîëüêî öåëûõ ëåò ñîñòàâëÿåò 45 ìåñÿöåâ:
=ÖÅËÎÅ(C11/12)
Îòâåò - 3 ãîäà.
Íàì îñòàåòñÿ ïðèáàâèòü ê îáùåìó êîëè÷åñòâó ëåò 3 ãîäà, è îòíÿòü îò îáùåãî êîëè÷åñòâà ìåñÿöåâ 3õ12 = 36 ìåñÿöåâ.
×òîáû íå ñîçäàâàòü äîïîëíèòåëüíûõ ÿ÷ååê ñ âû÷èñëåíèÿìè íà ëèñòå, ìîæíî ââåñòè ôîðìóëó ÖÅËÎÅ ñðàçó â èòîãîâóþ ôîðìóëó â êàæäîì ñòîëáöå. Äëÿ êîëè÷åñòâà ëåò îíà áóäåò èìåòü âèä:
=B11+ÖÅËÎÅ(C11/12)
È äëÿ îñòàòêà ìåñÿöåâ:
C11-ÖÅËÎÅ(C11/12)*12
Ðåçóëüòàò: 40 ëåò è 9 ìåñÿöåâ
