Ôóíêöèè ïîèñêà äàííûõ â òàáëèöàõ Excel 2007
| Àâòîð: | Èíäûê Èãîðü Âèêòîðîâè÷ |
| e-mail: | exelentc@yandex.ru |
Ó ìåíÿ îãðîìíûé ìàññèâ äàííûõ è ìíå ÷àñòî ïðèõîäèòñÿ èñêàòü, íàïðèìåð, ïî íàèìåíîâàíèþ òîâàðà, ÷òî áû ïîñìîòðåòü îïðåäåëåííûå õàðàêòåðèñòèêè. Ñ ïîìîùüþ îáû÷íîãî ïîèñêà íåóäîáíî, ïîòîìó ÷òî òàáëèöà èìååò ìíîãî ñòîëáöîâ è ÷àñòî ïðèõîäèòñÿ ïåðåïðîâåðÿòü. Ìîæíî ëè ýòî êàê-òî àâòîìàòèçèðîâàòü?
Ýòî ìîæíî ñäåëàòü ñ ïîìîùüþ ÂÏÐ èëè ÃÏÐ. ÂÏÐ èñïîëüçóåòñÿ åñëè èñêîìîå çíà÷åíèå ðàçìåùåíî â ñòîëáöå. ÃÏÐ - åñëè â ñòðî÷êå. À ñàì ïîðÿäîê è ïðèíöèï ðàáîòû ôóíêöèé îäèíàêîâûé. Äàííûå ôóíêöèè ìîæíî èñïîëüçîâàòü, åñëè äàííûå óíèêàëüíû (ò.å. íå ïîâòîðÿþòñÿ).
Ïîñêîëüêó ïðåäïîëàãàåòñÿ, ÷òî äàííûõ ìíîãî, ìîæíî äëÿ óïðîùåíèÿ ïðîöåññà ââîäà èñêîìûõ äàííûõ è âî èçáåæàíèå îøèáîê, ñäåëàòü ïîëå ñî ñïèñêîì. Äëÿ ýòîãî, óñòàíàâëèâàåì êóðñîð â ÿ÷åéêó, êîòîðàÿ áóäåò ïîëåì ñî ñïèñêîì (ìîæíî ýòî ñäåëàòü íà ÷èñòîì ëèñòå, íå ñîäåðæàùåì íèêàêèõ äàííûõ, êîòîðûé è áóäåì â äàëüíåéøåì èñïîëüçîâàòü äëÿ îòîáðàæåíèÿ ðåçóëüòàòîâ ïîèñêà). Íà âêëàäêå Äàííûå ãðóïïû Ðàáîòà ñ äàííûìè íàæèìàåì íà êíîïêó Ïðîâåðêà äàííûõ èç âûïàäàþùåãî ñïèñêà âûáèðàåì ïóíêò Ïðîâåðêà äàííûõ…
 äèàëîãîâîì îêíå Ïðîâåðêà íà âêëàäêå Ïàðàìåòðû â ïîëå Òèï äàííûõ âûáèðàåì Ñïèñîê.  ïîÿâèâøåìñÿ ïîëå Èñòî÷íèê óêàçûâàåì ññûëêó íà äèàïàçîí ÿ÷ååê, êîòîðûé ñîäåðæèò íàèìåíîâàíèå òîâàðîâ. Íàæèìàåì êíîïêó ÎÊ.
Ó âàñ äîëæåí ïîëó÷èòüñÿ âîò òàêîé âûïàäàþùèé ñïèñîê:
Òåïåðü ïðèñòóïèì íåïîñðåäñòâåííî ê ñîçäàíèþ ôóíêöèè äëÿ ïîèñêà íóæíûõ çíà÷åíèé. Òàáëèöó, â êîòîðîé áóäåò ïðîèñõîäèòü ïîèñê, íåîáõîäèìî ïðåäâàðèòåëüíî îòñîðòèðîâàòü ïî èñêîìîìó çíà÷åíèþ ïî âîçðàñòàíèþ.  íàøåì ïðèìåðå, ïî ìàðêå òåëåôîíà.
Óñòàíîâèòå êóðñîð ìûøêè â ñòðî÷êå ðÿäîì ñ ñîçäàííûì âûïàäàþùèì ñïèñêîì èëè â äðóãîì ìåñòå, ãäå âû õîòèòå, ÷òî áû îòîáðàæàëèñü ðåçóëüòàòû ïîèñêà. Ñ ïîìîùüþ âêëàäêè Ôîðìóëû èëè êíîïêè Âñòàâèòü ôóíêöèþ íà Ïàíåëè ôîðìóë âûáèðàåì ôóíêöèþ ÂÏÐ.
Èñêîìîå_çíà÷åíèå - äåëàåì ññûëêó íà ÿ÷åéêó, â êîòîðîé ñîäåðæèòñÿ ñîçäàííûé íàìè âûïàäàþùèé ñïèñîê.
Òàáëèöà - âûäåëÿåì òàáëèöó ñ äàííûìè òàê, ÷òî áû èñêîìûé àðãóìåíò áûë â ïåðâîì ñòîëáöå, è íàæèìàåì êíîïêó F4 èëè æå ïðîñòàâëÿåì â ðó÷íóþ çíà÷êè $, ÷òî áû ññûëêà ñòàëà àáñîëþòíîé. Òàáëèöó âûäåëÿåì áåç øàïêè, òîëüêî äàííûå.
Íîìåð_ñòîëáöà - óêàçûâàåì ïîðÿäêîâûé íîìåð ñòîëáöà, äàííûå êîòîðîãî ìû õîòèì âèäåòü â ðåçóëüòàòå ïîèñêà.
Èíòåðâàëüíûé_ïðîñìîòð - ËÎÆÜ - ïîñêîëüêó íàì íóæíî òî÷íîå ñîâïàäåíèå äàííûõ ñ çàäàííûì â ïîèñêå.
Íàæèìàåì êíîïêó ÎÊ.
Ìåíÿÿ Íîìåð ñòîëáöà â ñîçäàííîé ôóíêöèè ÂÏÐ, âû ìîæåòå ñäåëàòü âûáîðêó èç èñõîäíîé òàáëèöû íåñêîëüêèõ çíà÷åíèé, êîòîðûå áóäóò îòâå÷àòü âàøèì óñëîâèÿì ïîèñêà.
Ìîæíî ëè îñóùåñòâëÿòü ïîèñê àíàëîãè÷íûé ïîèñêó ñ èñïîëüçîâàíèåì ôóíêöèè ÂÏÐ, íî áåç ñîðòèðîâêè äàííûõ? ÂÏÐ âûäàåò â òàêîì ñëó÷àå îøèáêó 9.
Íàéòè ëþáîå çíà÷åíèå â ñòðîêå, åñëè èçâåñòíî çíà÷åíèå, êîòîðîå íàõîäèòñÿ â îäíîì ñòîëáöå, ìîæíî ñ ïîìîùüþ äâóõ ôóíêöèÿ ÏÎÈÑÊÏÎÇ è ÈÍÄÅÊÑ.
 êà÷åñòâå ïðèìåðà âîçüìåì òàáëèöó, êîòîðàÿ ñîäåðæèò íàèìåíîâàíèå ìàðîê òåëåôîíîâ è èõ öåíó.
Äëÿ óïðîùåíèÿ ïîèñêà íóæíûõ çíà÷åíèé ìîæíî ñäåëàòü ïîëå ñî ñïèñêîì, êîòîðûé áóäåò ñîñòîÿòü èç äàííûõ, êîòîðûå ìû çàäàåì äëÿ ïîèñêà (íàïðèìåð, íàèìåíîâàíèå òîâàðà). Äëÿ ýòîãî, óñòàíîâèâ êóðñîð â ñâîáîäíóþ ÿ÷åéêó, ïåðåõîäèì íà âêëàäêó Äàííûå ãðóïïó Ðàáîòà ñ äàííûìè è íàæèìàåì íà êíîïêó Ïðîâåðêà äàííûõ, èç âûïàäàþùåãî ñïèñêà âûáèðàåì ïóíêò Ïðîâåðêà äàííûõ…  äèàëîãîâîì îêíå Ïðîâåðêà ââîäèìûõ çíà÷åíèé íà âêëàäêå Ïàðàìåòðû â ïîëå Òèï äàííûõ âûáèðàåì Ñïèñîê è óêàçûâàåì äèàïàçîí, êîòîðûé ñîäåðæèò äàííûå äëÿ ïîèñêà.
Çàòåì, óñòàíàâëèâàåì êóðñîð â ñâîáîäíóþ ÿ÷åéêó, ñ ïîìîùüþ âêëàäêè Ôîðìóëû èëè êíîïêè Âñòàâèòü ôóíêöèþ íà Ïàíåëè ôîðìóë âûáèðàåì ôóíêöèþ ÏÎÈÑÊÏÎÇ.
Èñêîìîå_çíà÷åíèå - óêàçûâàåì ÿ÷åéêó, êîòîðàÿ ñîäåðæèò ïîëå ñî ñïèñêîì, ñîçäàííûé íàìè â ñàìîì íà÷àëå.
Ïðîñìàòðèâàåìûé_ìàññèâ - âûäåëÿåì äèàïàçîí ÿ÷ååê, êîòîðûå ñîäåðæàò èñêîìûå ÿ÷åéêè.  íàøåì ïðèìåðå, ñòîëáåö ñ ìàðêàìè òåëåôîíîâ, íî âûäåëÿòü íóæíî áåç øàïêè.
Òèï_ñîïîñòàâëåíèÿ - óêàçûâàåì "0" (íîëü) - ýòî ïîçâîëèò ôóíêöèè íàõîäèòü ïåðâûé àðãóìåíò, êîòîðûé ñîîòâåòñòâóåò èñêîìîìó çíà÷åíèþ.
Íàæèìàåì êíîïêó ÎÊ.
Ôóíêöèÿ ÏÎÈÑÊÏÎÇ óêàçûâàåò â êàêîé ñòðî÷êå ñîäåðæèòñÿ èñêîìîå çíà÷åíèå. Ñòàâ êóðñîðîì íà ÿ÷åéêó, êîòîðàÿ ñîäåðæèò ôîðìóëó, â ñòðîêå ôîðìóë âû óâèäèòå ñèíòàêñèñ ôóíêöèè, âûäåëÿåì åãî äî çíàêà "=" è êîïèðóåì (íàæàâ Ctrl+C) è íàæèìàåì Enter, ÷òîáû âûéòè èç ÿ÷åéêè.
Çàòåì, óñòàíàâëèâàåì êóðñîð â ñâîáîäíóþ ÿ÷åéêó, â êîòîðîé ó íàñ áóäóò îòîáðàæàòüñÿ ðåçóëüòàòû, è ñ ïîìîùüþ âêëàäêè Ôîðìóëû èëè êíîïêè Âñòàâèòü ôóíêöèþ íà Ïàíåëè ôîðìóë âûáèðàåì ôóíêöèþ ÈÍÄÅÊÑ. Äàííàÿ ôóíêöèÿ ñîäåðæèò äâà âèäà àðãóìåíòà, î ÷åì âàì ñðàçó æå è ñîîáùàò, íàì íóæåí "ìàññèâ; íîìåð_ñòðîêè; íîìåð_ñòîëáöà".
 äèàëîãîâîì îêíå Àðãóìåíòû ôóíêöèè óêàçûâàåì ñëåäóþùèå çíà÷åíèÿ:
Ìàññèâ - âûäåëÿåì âåñü ìàññèâ äàííûõ (áåç øàïêè òàáëèöû), êîòîðûå ñîäåðæèò êàê èñêîìûå çíà÷åíèÿ, òàê è äàííûå, êîòîðûå â ïîñëåäñòâèè ìû õîòèì ÷òî áû îòîáðàæàëèñü.  íàøåì ïðèìåðå ýòî ñòîëáöû, êîòîðûå ñîäåðæàò ìàðêó òåëåôîíà è öåíó.
Íîìåð_ñðîêè - çäåñü ìû âñòàâëÿåì ôóíêöèþ ÏÎÈÑÊÏÎÇ ïóòåì íàæàòèÿ êëàâèø Ctrl+V.
Íîìåð_ñòîáöà - çäåñü íåîáõîäèìî óêàçàòü íîìåð ñòîëáöà â ìàññèâå, çíà÷åíèå êîòîðîãî áóäåò îòîáðàæàòüñÿ â ðåçóëüòàòå ïîèñêà.
Íàæèìàåì êíîïêó ÎÊ.
