Îøèáêè Excel 2007
| Àâòîð: | Èíäûê Èãîðü Âèêòîðîâè÷ |
| e-mail: | exelentc@yandex.ru |
ß ââîæó ôîðìóëó, íî âìåñòî ðåçóëüòàòà ÿ âèæó òåêñò #Í/Ä èëè #####. ×òî ýòî çíà÷èò?
Ýòî îçíà÷àåò ÷òî â èñõîäíûõ äàííûõ, ñàìîé ôîðìóëå, ôîðìàòèðîâàíèè èëè â äðóãîì ñëó÷àå äîïóùåíà îøèáêà. Ñîîáùåíèå îá îøèáêå ñîäåðæèò óêàçàíèå íà òî, ãäå èìåííî îíà äîïóùåíà. Íèæå ìû ðàññìîòðèì ñîîáùåíèÿ îá îøèáêàõ è íàèáîëåå ÷àñòûå èõ ïðè÷èíû:
Îøèáêà #####
×àùå âñåãî ýòà îøèáêà îçíà÷àåò, ÷òî ñòîëáåö íåäîñòàòî÷íî øèðîê äëÿ îòîáðàæåíèÿ ñîäåðæèìîãî ÿ÷åéêè. Äëÿ ðåøåíèÿ ïðîáëåìû äîñòàòî÷íî óâåëè÷èòü øèðèíó ñòîëáöà.
Äðóãàÿ ïðè÷èíà îøèáêè - â ÿ÷åéêå ñîäåðæàòñÿ äàòà èëè âðåìÿ ñ îòðèöàòåëüíûìè çíà÷åíèÿìè.
Îøèáêà #Í/Ä
Äàííàÿ îøèáêà âîçíèêàåò, åñëè ôóíêöèè èëè ôîðìóëà ñîäåðæèò ññûëêó íà çíà÷åíèå (èëè ñàìî çíà÷åíèå), êîòîðîãî íåò. Äðóãèìè ñëîâàìè, â ôóíêöèè èëè ôîðìóëå íå çàïîëíåíî îáÿçàòåëüíîå ïîëå, èëè åñòü ññûëêà íà ïóñòóþ ÿ÷åéêó â òî âðåìÿ, êàê â ÿ÷åéêå îáÿçàòåëüíî äîëæíî ïðèñóòñòâîâàòü çíà÷åíèå. Íàèáîëåå ÷àñòûå ïðè÷èíû:
- Â ôóíêöèè íå óêàçàíû îäèí èëè íåñêîëüêî îáÿçàòåëüíûõ àðãóìåíòîâ.
-  ôóíêöèÿõ ÂÏÐ, ÃÏÐ, ÏÐÎÑÌÎÒÐ èëè ÏÎÈÑÊÏÎÇ óêàçàíî íåïðàâèëüíîå çíà÷åíèå àðãóìåíòà èñêîìîå_çíà÷åíèå.
- Ôóíêöèè ÂÏÐ, ÃÏÐ èëè ÏÎÈÑÊÏÎÇ ïðèìåíÿþòñÿ äëÿ ïîèñêà â íåñîðòèðîâàííîé òàáëèöå.
-  èñõîäíîé òàáëèöå âìåñòî ïðîïóùåííûõ äàííûõ áûëî ââåäåíî çíà÷åíèå #Í/Ä èëè ÍÄ().
Îøèáêà #ÄÅË/0!
Îøèáêà #ÄÅË/0! Îçíà÷àåò, ÷òî â ïðîöåññå âû÷èñëåíèÿ ïðîèñõîäèò äåëåíèå íà íîëü. Ïðè÷èíû ìîãóò áûòü ñëåäóþùèå:
- Â ôîðìóëå ÿâíî çàäàíî äåëåíèå íà íîëü (íàïðèìåð, =10/0).
-  ôîðìóëå èëè ôóíêöèè â êà÷åñòâå äåëèòåëÿ ñòîèò ññûëêà íà ïóñòóþ ÿ÷åéêó èëè íà ÿ÷åéêó, êîòîðàÿ ñîäåðæèò íîëü.
Îøèáêà #ÏÓÑÒÎ!
Ýòà îøèáêà âîçíèêàåò, êîãäà â óñëîâèÿõ ôóíêöèè óêàçàíî äâà ïåðåñåêàþùèõñÿ äèàïàçîíà, íî íà ñàìîì äåëå îíè íå ïåðåñåêàþòñÿ.
Ïðèìå÷àíèå:  íåêîòîðûõ çàäà÷àõ ïîëüçîâàòåëþ íåîáõîäèìî âûïîëíèòü äåéñòâèÿ òîëüêî ñ òåìè ÿ÷åéêàìè, êîòîðûå îäíîâðåìåííî ïðèñóòñòâóþò â äâóõ äèàïàçîíàõ. Äëÿ ýòîãî äèàïàçîíû ÿ÷ååê â ôóíêöèè ðàçäåëÿþò íå òî÷êîé ñ çàïÿòîé, à ïðîáåëîì (êîòîðûé íàçûâàþò îïåðàòîðîì ïåðåñå÷åíèÿ).
- Åñëè â ôóíêöèè íå äîëæíû çàäàâàòüñÿ ïåðåñåêàþùèåñÿ äèàïàçîíû (íàïðèìåð, ïðè ñóììèðîâàíèè), âåðîÿòíî, â êà÷åñòâå ðàçäåëèòåëÿ äèàïàçîíîâ áûë èñïîëüçîâàí ïðîáåë, à íå òî÷êà ñ çàïÿòîé. Èëè ïðîáåë áûë èñïîëüçîâàí ïðè óêàçàíèè äèàïàçîíà, âìåñòî äâîåòî÷èÿ.
- Åñëè â ôóíêöèè äîëæíû çàäàâàòüñÿ ïåðåñåêàþùèåñÿ äèàïàçîíû, âåðîÿòíî äèàïàçîíû äåéñòâèòåëüíî íå èìåþò îáùèõ ÿ÷ååê. Íóæíî ïðîâåðèòü çàäàííûå äèàïàçîíû â àðãóìåíòàõ ôóíêöèè.
Îøèáêà #×ÈÑËÎ!
Äàííàÿ îøèáêà âîçíèêàåò, åñëè â ôîðìóëå èëè ôóíêöèè óêàçàíû íåêîððåêòíûå ÷èñëîâûå çíà÷åíèÿ. Íàïðèìåð:
-  ôóíêöèè, êîòîðàÿ èñïîëüçóåò ÷èñëîâîé àðãóìåíò, â åãî êà÷åñòâå óêàçàí òåêñò èëè ññûëêà íà ÿ÷åéêó ñ íå÷èñëîâûì çíà÷åíèåì.
- Ôóíêöèÿ, êîòîðàÿ âûïîëíÿåò èòåðàöèè (âû÷èñëåíèå, êîòîðîå ïîâòîðÿåòñÿ äî òåõ ïîð, ïîêà íå áóäåò âûïîëíåíî çàäàííîå óñëîâèå, íàïðèìåð ÂÑÄ èëè ÑÒÀÂÊÀ) íå ìîæåò âû÷èñëèòü ðåçóëüòàò (íàïðèìåð, çàäàííîå óñëîâèå íå íàñòóïèëî).
- Ðåçóëüòàòîì âû÷èñëåíèÿ ñòàëî ÷èñëî, ñëèøêîì áîëüøîå èëè ñëèøêîì ìàëîå äëÿ èñïîëüçîâàíèÿ â Excel. Íàïîìèíàåì, ÷òî ìàêñèìàëüíîå ÷èñëî â Excel íå ìîæåò ïðåâûøàòü 10307 è áûòü ìåíüøå ìèíóñ 10307.
Îøèáêà #ÑÑÛËÊÀ!
Ïîäîáíîå ñîîáùåíèå ãîâîðèò íàì î òîì, ÷òî ññûëêà íà ÿ÷åéêó íåäåéñòâèòåëüíà. ×àùå âñåãî ýòî ìîæåò ñâèäåòåëüñòâîâàòü î òîì, ÷òî ÿ÷åéêè, íà êîòîðûå ññûëàåòñÿ ôîðìóëà, áûëè óäàëåíû, èëè â ýòè ÿ÷åéêè áûëè ñêîïèðîâàíû äðóãèå çíà÷åíèÿ.
Îøèáêà #ÇÍÀ×!
Òàêàÿ îøèáêà âîçíèêàåò ïðè èñïîëüçîâàíèè íåâåðíîãî òèïà àðãóìåíòà èëè îïåðàíäà. Íàïðèìåð, ïîëüçîâàòåëü ìîæåò îøèáèòüñÿ â ñëåäóþùåì:
-  ôîðìóëó âìåñòî ÷èñëà èëè ëîãè÷åñêîãî çíà÷åíèÿ (ÈÑÒÈÍÀ èëè ËÎÆÜ) ââåäåí òåêñò.
- Ïîñëå ââîäà èëè ðåäàêòèðîâàíèÿ ôîðìóëû ìàññèâà íàæèìàåòñÿ êëàâèøà ÂÂÎÄ âìåñòî êîìáèíàöèè CTRL+SHIFT+ÂÂÎÄ.
- Ññûëêà, ôîðìóëà èëè ôóíêöèÿ óêàçàíà êàê ìàññèâ.
-  êà÷åñòâå óñëîâèÿ ôóíêöèè, êîòîðûé òðåáóåò åäèíè÷íîå çíà÷åíèå, óêàçàí äèàïàçîí.
ß ïîíÿë, ÷òî îçíà÷àåò îøèáêà. Íî ìîÿ ôîðìóëà î÷åíü áîëüøàÿ/ðàáîòàåò ñ áîëüøèì îáúåìîì äàííûõ. Êàê ìíå íàéòè ìåñòî, ãäå äîïóùåíà îøèáêà?
Excel 2007 ìîæåò ñðàçó óêàçàòü íà îøèáêó èëè ïîìîæåò ïðîñìîòðåòü âû÷èñëåíèÿ ïîýòàïíî, ÷òîáû íàéòè îøèáêó. Äëÿ ýòîãî íàæìèòå íà ïèêòîãðàììó , êîòîðàÿ ïîÿâèëàñü âîçëå ÿ÷åéêè ñ ñîîáùåíèåì îá îøèáêå. Åñëè ôîðìóëà ñîñòîèò òîëüêî èç îäíîé ôóíêöèè èëè ïðè÷èíà îøèáêè äîñòàòî÷íî ïðîñòàÿ, êîíòåêñòíîå ìåíþ áóäåò ñîäåðæàòü ïóíêò Èñòî÷íèê îøèáêè. Ïîñëå íàæàòèÿ íà ýòîò ïóíêò Excel ïîêàæåò Âàì ÿ÷åéêó ñ íåêîððåêòíûìè äàííûìè àâòîìàòè÷åñêè:
Åñëè æå ôîðìóëà ñîäåðæèò íåñêîëüêî ôóíêöèé, òîãäà â êîíòåêñòíîì ìåíþ ïîÿâèòñÿ ïóíêò Ïîêàçàòü ýòàïû âû÷èñëåíèÿ.  îòêðûâøåìñÿ îêíå Âû ñìîæåòå óâèäåòü, êàêîå èìåííî âû÷èñëåíèå ïðèâîäèò ê îøèáêå.
Ó ìåíÿ îøèáêà #ÈÌß? è ÿ íå ìîãó íàéòè å¸ ïðè÷èíó.
- Ïðîâåðüòå ïðàâèëüíîñòü íàïèñàíèÿ èìåíè (íàçâàíèÿ) ôóíêöèè. Äëÿ ýòîãî ìîæíî âûçâàòü Ìàñòåð ôóíêöèé (ãðóïïà Áèáëèîòåêà ôóíêöèé âêëàäêè Ôîðìóëû), è âûáðàòü íóæíóþ ôóíêöèþ èç ïðåäëàãàåìîãî ñïèñêà.
-  ôîðìóëó áûë ââåäåí òåêñò, íå çàêëþ÷åííûé â äâîéíûå êàâû÷êè. Äîïóñòèì, ðåçóëüòàòîì ôîðìóëû äîëæåí áûë áûòü òåêñò ñ ÷èñëîì - íàïðèìåð, Èòîãî 200 ð. Åñëè ïðè ââåäåíèè òåêñòà Èòîãî â ôîðìóëó ýòîò òåêñò íå çàêëþ÷åí â äâîéíûå êàâû÷êè, Excel èíòåðïðåòèðóåò òàêèå äàííûå êàê èìÿ, ÷òî âûçûâàåò îøèáêó.
Ïðèìå÷àíèå: Èìÿ - ýòî ñëîâî, ñëîâîñî÷åòàíèå èëè äðóãîé íàáîð çíàêîâ, çàäàííûå ïîëüçîâàòåëåì è îáîçíà÷àþùèå ÿ÷åéêó, äèàïàçîí ÿ÷ååê, ôîðìóëó èëè êîíñòàíòó.
-  ôîðìóëå èëè ôóíêöèè èñïîëüçóåòñÿ èìÿ, êîòîðîå íå áûëî îïðåäåëåíî. Ïðîâåðüòå, ÷òî äàííîå èìÿ ñóùåñòâóåò. Íà âêëàäêå Ôîðìóëû â ãðóïïå Èìåíîâàííûå ÿ÷åéêè âûáåðèòå Äèñïåò÷åð èìåí è âûÿñíèòå, ïðèñóòñòâóåò ëè äàííîå èìÿ â ñïèñêå. Åñëè äàííîå èìÿ îòñóòñòâóåò, åãî ìîæíî äîáàâèòü åãî ïóòåì âûáîðà êîìàíäû Ñîçäàíèå èìåíè.
- Áûëà äîïóùåíà îøèáêà â íàïèñàíèè èìåíè. Äëÿ ïðîâåðêè ïðàâèëüíîñòè óêàçàíèÿ èìåí â ñòðîêå ôîðìóë íóæíî íàæàòü êëàâèøó F3, ùåëêíóòü òðåáóåìîå èìÿ, à çàòåì íàæàòü ÎÊ.
-  ôîðìóëå èëè ôóíêöèè èñïîëüçóåòñÿ ññûëêà íà äðóãîé ëèñò, íå çàêëþ÷åííûé â îðäèíàðíûå êàâû÷êè. Åñëè èìÿ äðóãîãî ëèñòà èëè êíèãè ñîäåðæèò íåáóêâåííûå çíàêè èëè ïðîáåë, òî ýòî èìÿ íåîáõîäèìî çàêëþ÷èòü â àïîñòðîôû ( ' ).
Ó ìåíÿ îãðîìíàÿ áàçà äàííûõ êëèåíòîâ - ïîêóïàòåëåé. Êàæäûé äåíü â áàçå äàííûõ äîáàâëÿåòñÿ ñòîëáåö, â êîòîðîì óêàçûâàåòñÿ ñóììà âûðó÷êè îò êàæäîãî ïîêóïàòåëÿ. Åñëè ïîêóïàòåëü íè÷åãî íå êóïèë, â ÿ÷åéêå ñòàâèòñÿ #Í/Ä. Êàê ìíå ïðîñóììèðîâàòü ñòîëáåö, åñëè òàì åñòü òàêèå äàííûå?
Ñàìûé ïðîñòîé ñïîñîá ðåøåíèÿ òàêîé ïðîáëåìû - èçìåíèòü ïðèíöèï íàïîëíåíèÿ áàçû äàííûõ è íå çàïîëíÿòü ÿ÷åéêè äëÿ ïîêóïàòåëåé, êîòîðûå íå ïðîèçâîäèëè ïîêóïêè.
Íî åñëè îñíîâíóþ òàáëèöó èçìåíèòü íåâîçìîæíî (ïî ðàçíûì ïðè÷èíàì), ýòó ïðîáëåìó ìîæíî ïîïðîáîâàòü ðåøèòü ñ ïîìîùüþ ôóíêöèè ÁÄÑÓÌÌ.
Äîïóñòèì, èñõîäíàÿ òàáëèöà èìååò ñëåäóþùèé âèä:
Ïðèìåíèâ ôóíêöèþ ÁÄÑÓÌÌ, ìû ñìîæåì âûáðàòü äëÿ ñóììèðîâàíèÿ òå ÿ÷åéêè, êîòîðûå íå ñîäåðæàò çíà÷åíèÿ #Í/Ä. Ôóíêöèÿ ÁÄÑÓÌÌ èìååò ñëåäóþùèé ñèíòàêñèñ:
=ÁÄÑÓÌÌ(áàçà_äàííûõ, ïîëå, óñëîâèÿ) , ãäå
- áàçà_äàííûõ - ýòî äèàïàçîí ÿ÷ååê, ñ êîòîðûìè áóäóò ïðîâîäèòüñÿ äåéñòâèÿ.
- ïîëå - ýòî ñòîëáåö, çíà÷åíèÿ èç êîòîðîãî áóäóò ñóììèðîâàòüñÿ.
- óñëîâèÿ - ýòî íàáîð óñëîâèé, êîòîðûå äîëæíû âûïîëíÿòüñÿ äëÿ çíà÷åíèÿ ÿ÷åéêè, ÷òîáû ýòî çíà÷åíèå ñóììèðîâàëîñü.
Òî åñòü, â ôóíêöèè íàì íóæíî çàäàòü ñóììèðîâàíèå ïåðâîãî ñòîëáöà íàøåé òàáëèöû, ïðè óñëîâèè, ÷òî çíà÷åíèå ÿ÷åéêè ýòîãî ñòîëáöà íå ðàâíî #Í/Ä. Äëÿ ýòîãî ñíà÷àëà ñîçäàäèì óñëîâèå - â ëþáîì óäîáíîì ìåñòå ðàáî÷åãî ëèñòà íàïèøåì óñëîâèå <>#Í/Ä è îçàãëàâèì ýòó ÿ÷åéêó òàê æå êàê è ñòîëáåö íóæíîé íàì áàçû äàííûõ.
Äàëåå ìû ââîäèì ñëåäóþùóþ ôîðìóëó:
=ÁÄÑÓÌÌ(C2:C18;1;E2:E3) ãäå
- C2:C18 - íàøà òàáëèöà,
- E2:E3 - íàøå óñëîâèå.
Îáðàòèòå âíèìàíèå, ÷òî äèàïàçîíû áàçû äàííûõ, è óñëîâèé îáÿçàòåëüíî äîëæíû ñîäåðæàòü çàãîëîâêè ñòîëáöîâ! Ïðè ýòîì çàãîëîâîê óñëîâèÿ äîëæåí ñîâïàäàòü ñ çàãîëîâêîì ñòîëáöà, íàä êîòîðûì ïðîèçâîäÿòñÿ âû÷èñëåíèÿ!
Íàø ðåçóëüòàò óêàçàí ïîä òàáëèöåé:
Åñëè æå íàøà áàçà äàííûõ ñîäåðæèò îøèáêè ðàçíûõ òèïîâ, äîñòàòî÷íî äîáàâèòü èõ â óñëîâèÿ (è, ñîîòâåòñòâåííî, ðàñøèðèòü äèàïàçîí óñëîâèé â ôîðìóëå), äëÿ òîãî, ÷òîáû ïîëó÷èòü íóæíûé ðåçóëüòàò.
Ýòîò ñïîñîá òàêæå ìîæåò èñïîëüçîâàòüñÿ äëÿ ôóíêöèé ÑרÒ, ÁÑרÒÀ, ÄÌÀÊÑ, ÄÌÈÍ, ÁÄÏÐÎÈÇÂÅÄ è èì ïîäîáíûõ.
