Âîïðîñû ïî äàòàì Excel 2007
| Àâòîð: | Èíäûê Èãîðü Âèêòîðîâè÷ |
| e-mail: | exelentc@yandex.ru |
Ìíå ïðèñëàëè îãðîìíóþ òàáëèöó, â êîòîðîé åñòü ñòîëáåö ñ äàòàìè. Íî äàòû ââåäåíû êàê ÷èñëà áåç ðàçäåëèòåëåé! Íàïðèìåð, âìåñòî 20.09.1981 ââåäåíî 200981. Êàê ìíå êîíâåðòèðîâàòü èõ â äàòû?
Ê ñîæàëåíèþ, ñòàíäàðòíûå ôóíêöèè Excel íå ïîçâîëÿþò êîíâåðòèðîâàòü ëþáîå øåñòèçíà÷íîå ÷èñëî â äàòó ñ òàêèìè æå çíà÷åíèÿìè äíÿ, ìåñÿöà è ãîäà. Íî ñóùåñòâóåò ñïîñîá ñäåëàòü ýòî ñàìîñòîÿòåëüíî ñ èñïîëüçîâàíèåì òåêñòîâûõ ôîðìóë.
Äîïóñòèì, ó íàñ åñòü òàáëèöà, êîòîðóþ ýêñïîðòèðîâàëè èç ñòîðîííåé áàçû äàííûõ. Äàòû â ýòîé òàáëèöå ïðèâåäåíû òàê æå, êàê óêàçàíî â âîïðîñå.
Ïåðâîé ó íàñ óêàçàíà äàòà 221103, ÷òî îçíà÷àåò "22 ÿíâàðÿ 2003 ãîäà". ×òîáû ïîëó÷èòü äàòó 22.11.2003, ìû îáû÷íî ââîäèì â ÿ÷åéêó 22/11/03, è óñòàíàâëèâàåì ôîðìàò ÿ÷åéêè "Äàòà". Ñåé÷àñ ìû ñäåëàåì òî æå ñàìîå, íî ñ ïîìîùüþ ôóíêöèè ÇÍÀ×ÅÍ. Ýòà ôóíêöèÿ ïðåîáðàçîâûâàåò òåêñòîâîå çíà÷åíèå â ÷èñëîâîå.
Êðîìå òîãî, ìû èñïîëüçóåì ôóíêöèè ËÅÂÑÈÌÂ, ÏÐÀÂÑÈÌ è ÏÑÒÐ. Âñå ýòè ôóíêöèè èçâëåêàþò èç ÿ÷åéêè ñ òåêñòîì óêàçàííîå â àðãóìåíòå êîëè÷åñòâî ñèìâîëîâ, íà÷èíàÿ ñ çàäàííîé â àðãóìåíòå ïîçèöèè. Íàïðèìåð,
=ËÅÂÑÈÌÂ(B2;2)
Èçâëåêàåò èç ÿ÷åéêè ñ çíà÷åíèåì 221103 äâà ñèìâîëà ñëåâà (òî åñòü 22). Ôóíêöèÿ ÏÐÀÂÑÈÌ äåëàåò òî æå, íî îòñ÷èòûâàåò ñïðàâà, à ôóíêöèÿ ÏÑÒÐ - ñëåâà, íî èçâëåêàåò íóæíîå êîëè÷åñòâî ñèìâîëîâ, ïðåäâàðèòåëüíî îòñ÷èòàâ óêàçàííóþ åé ïîçèöèþ. Ïîýòîìó ñ ïîìîùüþ ÏÑÒÐ ìîæíî èçâëåêàòü ñèìâîëû èç ñåðåäèíû òåêñòà.
Ñ ïîìîùüþ, ýòèõ òð¸õ ôóíêöèé ìû èçâëå÷¸ì îòäåëüíûå ÷àñòè äàòû (22, 11, 03), ñ ïîìîùüþ îïåðàíäà & è çíàêà / ñêëåèì èõ âìåñòå, à ñ ïîìîùüþ ôóíêöèè ÇÍÀ×ÅÍ ïðåîáðàçóåì ïîëó÷èâøååñÿ âûðàæåíèè â ÷èñëî:
=ÇÍÀ×ÅÍ(ËÅÂÑÈÌÂ(B2;2)&"/"&ÏÑÒÐ(B2;3;2)&"/"&ÏÐÀÂÑÈÌÂ(B2;2))
Ìû ñïåöèàëüíî èñïîëüçîâàëè ôóíêöèè ËÅÂÑÈÌÂ, ÏÐÀÂÑÈÌÂ è ÏÑÒÐ îäíîâðåìåííî, ÷òîáû ïðîäåìîíñòðèðîâàòü èõ ðàáîòó. Íî âîçìîæíî èñïîëüçîâàòü òîëüêî ôóíêöèþ ÏÑÒÐ, ïðàâèëüíî çàäàâ ïîçèöèè ñèìâîëîâ:
Ýòîò ìåòîä òàêæå çàìå÷àòåëüíî ïîäõîäèò äëÿ ñèòóàöèè, êîãäà âûãðóæåííûå äàòû íàõîäÿòñÿ â ôîðìàòå MM/ÄÄ/Ãà èëè ÃÃ/ÌÌ/ÄÄ - äîñòàòî÷íî ïðàâèëüíî óêàçàòü â íàøåé ôîðìóëå ïîçèöèè èçâëåêàåìûõ ñåãìåíòîâ òåêñòà èëè ðàñïîëîæèòü ôóíêöèè ËÅÂÑÈÌÂ, ÏÐÀÂÑÈÌ è ÏÑÒÐâ íóæíîì íàì ïîðÿäêå.
ß äåëàþ ðàñ÷åòû äëÿ äèïëîìà, è ìíå íóæíî âûâåñòè îòðèöàòåëüíûå çíà÷åíèÿ äàò è âðåìåíè. Êàê ýòî ñäåëàòü?
Excel ðàáîòàåò òîëüêî ñ ïîëîæèòåëüíûìè çíà÷åíèÿìè äàò è âðåìåíè, èíà÷å ïîëüçîâàòåëü ïîëó÷àåò îøèáêó ##### (ñì. îøèáêè Excel). Íî ïîñêîëüêó îáÿçàòåëüíî íàéäóòñÿ æåëàþùèå ïðåîäîëåòü îãðàíè÷åíèÿ, ìû ìîæåì ïîñîâåòîâàòü ñëåäóþùåå ñïîñîáû.
Ñïîñîá 1: Âûâåñòè ðåçóëüòàò âû÷èñëåíèé êàê òåêñò, èñïîëüçóÿ ôóíêöèþ ÒÅÊÑÒ.
Íàïðèìåð, ìû âû÷èñëÿåì ðàçíèöó ìåæäó äâóìÿ äàòàìè â ÿ÷åéêàõ À1 è À2
Åñëè ïðîñòî îòíÿòü îò ìåíüøåé äàòû áîëüøóþ, ìû ïîëó÷èì ÷èñëî -29848. Ñ ïîìîùüþ ôóíêöèè ÒÅÊÑÒ, ìû ìîæåì ïðåîáðàçîâàòü ÷èñëî 29848 â äàòó ñî çíàêîì ìèíóñ:
=ÒÅÊÑÒ(A1-A2;"-ÄÄ.ÌÌ.ÃÃÃÃ")
Òî åñòü ìû ñíà÷àëà íàøëè ïîëîæèòåëüíóþ ðàçíèöó ìåæäó äàòàìè (29848), à ïîòîì ïðåîáðàçîâàëè å¸ â òåêñò ñ ôîðìàòîì äàòû è çíàêîì ìèíóñ.
Åñëè íóæíî âûâåñòè îòðèöàòåëüíîå çíà÷åíèå âðåìåíè, íåîáõîäèìî èñïîëüçîâàòü ôîðìàò âðåìåíè:
=ÒÅÊÑÒ(A1-A2;"-×:ÌÌ")
Ïîëíûé ñïèñîê ôîðìàòîâ ìîæíî íàéòè âî âñòðîåííîé ïîìîùè ïî ôóíêöèè ÒÅÊÑÒ.
Îáðàòèòå âíèìàíèå, åñëè ðàçíèöà äâóõ ÷èñåë áóäåò èçíà÷àëüíî îòðèöàòåëüíîé, ôóíêöèÿ ÒÅÊÑÒ âîçâðàòèò îøèáêó. Äëÿ ðåøåíèÿ ýòîé ïðîáëåìû ìîæíî èñïîëüçîâàòü êîíñòðóêöèþ:
=ÒÅÊÑÒ(ÌÀÊÑ(À1:À2)-ÌÈÍ(À1:À2);"-ÄÄ.ÌÌ.ÃÃÃÃ ×:ÌÌ")
Òî åñòü ìû âñåãäà ñíà÷àëà âû÷èñëÿåì ïîçèòèâíóþ ðàçíèöó, à ïîòîì ôîðìàòèðóåì íåãàòèâíóþ äàòó/âðåìÿ.
Íå çàáûâàéòå, ÷òî ïîëó÷åííûé ðåçóëüòàò ÿâëÿåòñÿ òåêñòîì, òî åñòü åãî íåâîçìîæíî èñïîëüçîâàòü â äàëüíåéøèõ âû÷èñëåíèÿõ!
Ñïîñîá 2: Èñïîëüçîâàíèå ïîëüçîâàòåëüñêîãî ôîðìàòà.
 ýòîì ñëó÷àå ìû ñîçäàåì ïîëüçîâàòåëüñêèé ôîðìàò äàòû/âðåìåíè ñî çíàêîì ìèíóñ. Äë ñîçäàíèÿ ôîðìàòà íóæíî âûçâàòü îêíî Ôîðìàò ÿ÷ååê ëþáûì óäîáíûì Âàì ñïîñîáîì.  ñïèñêå ×èñëîâûå ôîðìàòû íåîáõîäèìî âûáðàòü ïóíêò (âñå ôîðìàòû). Ïîñëå ýòîãî â ñïèñêå Òèï ìû âûáèðàåì ÷èñëîâîé ôîðìàò ÄÄ.ÌÌ.ÃÃÃà ÷:ìì, êîòîðûé ìû áóäåì íàñòðàèâàòü.
Ïîñêîëüêó ýòîò ôîðìàò ÿâëÿåòñÿ âñòðîåííûì, ïîñëå íàñòðîéêè Excel ñîçäàñò êîïèþ ýòîãî ÷èñëîâîãî ôîðìàòà. Èñõîäíûé ôîðìàò â ñïèñêå Òèï íåâîçìîæíî èçìåíèòü èëè óäàëèòü.
Ïîñëå âûáîðà èç ñïèñêà íóæíîãî ôîðìàòà, îí ïîÿâèòñÿ â ïîëå Òèï. Èñïðàâëÿåì ýòîò ôîðìàò íà ñëåäóþùèé: -ÄÄ.ÌÌ.ÃÃÃÃ ÷:ìì , è íàæèìàåì Îê.
Îñòàëîñü òîëüêî îòôîðìàòèðîâàòü ÿ÷åéêè ñ íóæíûìè äàòàìè/âðåìåíåì ñ ïðèìåíåíèåì íîâîãî ôîðìàòà.
Ïîìíèòå, ÷òî èñõîäíîå ÷èñëî äîëæíî áûòü ïîëîæèòåëüíûì, èíà÷å Excel âîçâðàòèò îøèáêó!
ß ãîòîâëþ çàðïëàòíûå âåäîìîñòè è ìíå íóæíî ïîñòîÿííî ââîäèòü â òàáëèöó ïîñëåäíåå ÷èñëî ìåñÿöà è ðàññ÷èòûâàòü, êàêîé ýòî áóäåò äåíü íåäåëè. Ýòî ìîæíî êàê-òî àâòîìàòèçèðîâàòü?
Äëÿ òàêîé çàäà÷è èñïîëüçóåòñÿ ôóíêöèÿ ÊÎÍÌÅÑßÖÀ:
=ÊÎÍÌÅÑßÖÀ(íà÷_äàòà;÷èñëî_ìåñÿöåâ) , ãäå
- íà÷_äàòà - ýòî äàòà, îò êîòîðîé áóäåò îòñ÷èòûâàòüñÿ êîíåö ìåñÿöà,
- ÷èñëî_ìåñÿöåâ - ïîðÿäêîâûé íîìåð ìåñÿöà, â êîòîðîì áóäåò ðàññ÷èòàíà ïîñëåäíÿÿ äàòà, îòñ÷èòàííûé îò íà÷àëüíîé äàòû.
Òî åñòü, åñëè ó íàñ èñõîäíàÿ äàòà ñîñòàâëÿåò 01.08.2011, òî ôîðìóëà:
=ÊÎÍÌÅÑßÖÀ(01.08.2011;0)
âåðí¸ò íàì 31 àâãóñòà,
à ôîðìóëà
=ÊÎÍÌÅÑßÖÀ(01.08.2011;1)
- 30 ñåíòÿáðÿ.
Ñîîòâåòñòâåííî, ÷òîáû âûâåñòè â ñòîëáåö ñïèñîê âñåõ ïîñëåäíèõ äàò âñåõ ìåñÿöåâ, äîñòàòî÷íî çàäàòü â ÿ÷åéêå À1 äàòó 01.01.2011, à â ñòîëáöå ðåçóëüòàòîâ:
=ÊÎÍÌÅÑßÖÀ(À1;0) =ÊÎÍÌÅÑßÖÀ(À1;1) =ÊÎÍÌÅÑßÖÀ(À1;2) … =ÊÎÍÌÅÑßÖÀ(À1;12)
×òîáû óçíàòü, êàêîé ýòî áóäåò äåíü íåäåëè, íóæíî èñïîëüçîâàòü ôóíêöèþ ÄÅÍÜ:
=ÄÅÍÜ(ÊÎÍÌÅÑßÖÀ(A1;0))
À êàê îïðåäåëèòü, ÿâëÿåòñÿ ëè äàòà âûõîäíûì äíåì?
Äëÿ ýòîãî ìîæíî èñïîëüçîâàòü ôóíêöèþ ÄÅÍÜÍÅÄ:
=ÄÅÍÜÍÅÄ(A1;2)>5
Òî åñòü ìû ðàññ÷èòûâàåì ïîðÿäêîâûé íîìåð äíÿ íåäåëè, êîòîðûé ñîîòâåòñòâóåò çàäàííîé äàòå, è åñëè ýòîò äåíü èìååò íîìåð 6 èëè 7 (ñóááîòà èëè âîñêðåñåíüå), òî íàøà ôîðìóëà âîçâðàòèò íàì çíà÷åíèå ÈÑÒÈÍÀ, ÷òî îçíà÷àåò, ÷òî íàøà äàòà - âûõîäíîé.
