Îïåðàöèè ñ ôîðìóëàìè ìàññèâîâ â Excel 2007
| Àâòîð: | Èíäûê Èãîðü Âèêòîðîâè÷ |
| e-mail: | exelentc@yandex.ru |
Ó ìåíÿ åñòü äâå òàáëèöû - îäíà ñ ðóêîâîäèòåëÿ êîìïàíèé è èõ êîíòàêòíûìè äàííûìè, à äðóãàÿ ñ ñàìèìè êîìïàíèÿìè è ñ èõ äàííûìè êàê þðèäè÷åñêèõ ëèö. Ìîæíî ëè â Ýêñåëü àâòîìàòè÷åñêè ñîâìåñòèòü ýòè äâå òàáëèöû, ÷òî áû íå çàíèìàòüñÿ êîïèïàñò ïî êàæäîé êîïàíèè?
Âû ìîæåòå âîñïîëüçîâàòüñÿ ôóíêöèåé ÂÏÐ.
Äëÿ ðåøåíèÿ äàííîé çàäà÷è ñîçäàäèì ïðèìåð èç äâóõ òàáëèö - Ðóêîâîäèòåëè è Êîìïàíèè.
Òàáëèöó, êîòîðàÿ èìååò óíèêàëüíûå, íå ïîâòîðÿþùèåñÿ çíà÷åíèÿ (â íàøåì ïðèìåðå òàáëèöà Êîìïàíèè) íåîáõîäèìî ïðåäâàðèòåëüíî îòôîðìàòèðîâàòü.  êà÷åñòâå ïåðâîãî ñòîëáöà äîëæíî áûòü çíà÷åíèå, êîòîðîå ÿâëÿåòñÿ èäåíòèôèêàòîðîì è çíà÷åíèå êîòîðîãî áóäåò èñêîìûì â îñíîâíîé òàáëèöå (â äàííîì ñëó÷àå Ñîêðàùåííîå íàçâàíèå êîìïàíèè). Çíà÷åíèå îáÿçàòåëüíî äîëæíî áûòü îòôîðìàòèðîâàíî ïî âîçðàñòàíèþ!
 òàáëèöå Ðóêîâîäèòåëè â ÿ÷åéêó G2 (ïåðâàÿ çàïèñü òàáëèöû) ââîäèì ôóíêöèþ ÂÏÐ (âêëàäêà Ôîðìóëû ãðóïïà Áèáëèîòåêà ôóíêöèé êíîïêà Âñòàâèòü ôóíêöèþ èëè êíîïêà Ññûëêè è ìàññèâû).
Èñêîìîå_çíà÷åíèå - äåëàåì ññûëêó íà ÿ÷åéêó, â êîòîðîé ñîäåðæèòñÿ íàçâàíèå êîìïàíèè â òàáëèöå Ðóêîâîäèòåëè (êîòîðîå èäåíòè÷íî íàçâàíèþ êîìïàíèè â ïåðâîì ñòîëáöå òàáëèöû Êîìïàíèè).
Òàáëèöà - âûäåëÿåì ïîëíîñòüþ òàáëèöó Êîìïàíèè è íàæèìàåì êíîïêó F4 èëè æå ïðîñòàâëÿåì â ðó÷íóþ çíà÷êè $, ÷òî áû ññûëêà ñòàëà àáñîëþòíîé.
Íîìåð_ñòîëáöà - óêàçûâàåì ïîðÿäêîâûé íîìåð ñòîëáöà â òàáëèöå Êîìïàíèè, äàííûå êîòîðîãî, ìû õîòèì âèäåòü â òàáëèöå Ðóêîâîäèòåëè
Èíòåðâàëüíûé_ïðîñìîòð - ìîæåò èìåòü òîëüêî äâà çíà÷åíèÿ:
- À) ÈÑÒÈÍÀ - ïîèñê â òàáëèöå áóäåò ïðîâîäèòüñÿ ïî ïðèáëèçèòåëüíîìó ñîâïàäåíèþ;
- Á) ËÎÆÜ - ïîèñê â òàáëèöå áóäåò ïðîâîäèòüñÿ ïî òî÷íîìó ñîâïàäåíèþ.
Íàæèìàåì êíîïêó ÎÊ. Ñ ïîìîùüþ ôóíêöèè Àâòîçàïîëíåíèå êîïèðóåì ôóíêöèþ íà âñå ñòðîêè òàáëèöû.
Âñòàâëÿåì ôóíêöèþ ÂÏÐ íóæíîå êîëè÷åñòâî ðàç, èçìåíÿÿ òîëüêî íîìåð ñòîëáöà, êîòîðûé äîëæåí îòîáðàæàòüñÿ â òàáëèöå Ðóêîâîäèòåëè.  ðåçóëüòàòå ìû ïîëó÷èì âîò òàêóþ òàáëèöó:
Êàê â Ýêñåëå ïåðåâåðíóòü òàáëèöó èç ñòîëáèêà â ñòðî÷êó?
Òðàíñïîíèðîâàòü òàáëèöó (ò.å. ðàçâåðíóòü òàáëèöó ïîìåíÿâ ìåñòàìè ñòîëáöû è ñòðîêè) ìîæíî äâóìÿ ñïîñîáàìè.
1. Ñ ïîìîùüþ ôóíêöèè ÒÐÀÍÑÏ. Îíà ïîçâîëÿåò ñîõðàíÿòü ñâÿçü ìåæäó òàáëèöàìè. Òàêèì îáðàçîì, ìåíÿÿ äàííûå â èñõîäíîé òàáëèöå - îíè àâòîìàòè÷åñêè áóäóò èçìåíåíû è â òðàíñïîíèðîâàííîé.
Âûäåëÿåì äèàïàçîí, â êîòîðûé áóäåò òðàíñïîíèðîâàíà òàáëèöà. Äèàïàçîí íóæíî âûäåëÿòü òàêîãî æå ðàçìåðà, êàêèì áóäåò ïåðåâåðíóòàÿ òàáëèöà, ò.å. åñëè ó âàñ èìååòñÿ òàáëèöà â äâà ñòîëáöà è òðè ñòðî÷êè, òî ñîîòâåòñòâåííî íóæíî âûäåëèòü òðè ñòîëáöà è äâå ñòðî÷êè.
Ââîäèì ôóíêöèþ ÒÐÀÍÑÏ (âêëàäêà Ôîðìóëû ãðóïïà Áèáëèîòåêà ôóíêöèé êíîïêà Âñòàâèòü ôóíêöèþ èëè êíîïêà Ññûëêè è ìàññèâû).
 ïîëå Ìàññèâ óêàæèòå äèàïàçîí ÿ÷ååê, êîòîðûé íóæíî òðàíñïîíèðîâàòü. Íàæìèòå ÎÊ.  ÿ÷åéêå áóäåò îòîáðàæàòüñÿ îøèáêà ôîðìóëû #ÇÍÀ×!, íàæèìàåì F2, à çàòåì CTRL+SHIFT+ENTER. Áëàãîäàðÿ ýòèì äåéñòâèÿì ôîðìóëà ïðåîáðàçîâàëàñü â ôîðìóëó ìàññèâà è âåñü âûäåëåííûé äèàïàçîí çàïîëíèëñÿ äàííûìè, à â ñòðîêå ôîðìóë âû óâèäèòå ôèãóðíûå ñêîáêè.
Âíèìàíèå! Ôèãóðíûå ñêîáêè íóæíî ïðîñòàâëÿòü èñêëþ÷èòåëüíî ïóòåì íàæàòèÿ êëàâèø CTRL+SHIFT+ENTER, åñëè èõ ïîñòàâèòü âðó÷íóþ, ôîðìóëà íå áóäåò ðàáîòàòü.
2. Ñ ïîìîùüþ îïöèè Ñïåöèàëüíàÿ âñòàâêà - Òðàíñïîíèðîâàòü. Äàííûé ñïîñîá òðàíñïîíèðóåò (ïåðåâîðà÷èâàåò) òàáëèöó áåç ñîçäàíèÿ ñâÿçåé ñ èñõîäíîé òàáëèöåé.
Âûäåëÿåì òàáëèöó, êîòîðóþ íóæíî ïåðåâåðíóòü è êîïèðóåì åå. Óñòàíîâèâ êóðñîð â ÿ÷åéêó, ñ êîòîðîé äîëæíà íà÷èíàòüñÿ òðàíñïîíèðîâàííàÿ òàáëèöà, íà âêëàäêå Ãëàâíàÿ ãðóïïû Áóôåð îáìåíà, íàæèìàåì íà ñòðåëî÷êó íà êíîïêå Âñòàâèòü è èç ïðåäëîæåííîãî ìåíþ âûáèðàåì Òðàíñïîíèðîâàòü.
Ìîæíî ëè ñäåëàòü â Ýêñåëå âûáîðî÷íîå ñóììèðîâàíèå? Ò.å. ìíå íóæíî, ÷òî áû èç òàáëèöû, íàïðèìåð, ãäå åñòü çàêàçû ïî êëèåíòàì ìîæíî áûëî âûáðàòü êëèåíòà, à ìíå ïîêàçûâàëî áû ñóììó çàêàçà.
Ñ ýòîé öåëüþ ìîæíî èñïîëüçîâàòü ôîðìóëó ìàññèâà è ïðîìåæóòî÷íûå èòîãè.
1. Ôîðìóëà ìàññèâà.
Ïðåäïîëîæèì ó íàñ åñòü òàáëèöà ñî ñëåäóþùèìè äàííûìè:
Íàì íóæíî, ÷òî áû ñóììèðîâàëî âûðó÷êó ïî êàæäîìó ìàãàçèíó â ðàçðåçå âûáðàííîé ìàðêè è ìîäåëè òåëåôîíà. Äëÿ ýòîãî ïðåäâàðèòåëüíî ñîçäàåì òàáëèöó ñ óñëîâèÿìè, â êîðîé óêàçûâàåì âñå ïåðåìåííûå äàííûå, êðîìå ñóììû, ò.ê. îíà ó íàñ áóäåò ðàññ÷èòûâàòüñÿ àâòîìàòè÷åñêè.
Òåïåðü ñòàíîâèìñÿ â ÿ÷åéêó, â êîòîðîé áóäåò îòîáðàæåí ðåçóëüòàò âû÷èñëåíèé, ââîäèì ðàâíî è ïèøåì ñóìì. Ó íàñ ïîÿâëÿåòñÿ îêíî ïîäñòàíîâêè ôîðìóë.
 íåì ìû êëèêàåì äâàæäû ïî ôóíêöèè ÑÓÌÌ è íà÷èíàåì ââîäèòü àðãóìåíòû:
À) îòêðûâàåì ñêîáêó, âûäåëÿåì äèàïàçîí ÿ÷ååê, êîòîðûå ñîäåðæàòü íàçâàíèÿ ìàãàçèíîâ, óñòàíàâëèâàåì êóðñîð â ñòðîêó ôîðìóë, ââîäèì çíàê "=" è êëèêàåì íà ÿ÷åéêó ñ äàííûì óñëîâèåì, çàêðûâàåì ñêîáêó;
Á) ñòàâèì çíàê óìíîæèòü "*", îòêðûâàåì ñêîáêó è âûäåëÿåì äèàïàçîí ÿ÷ååê, êîòîðûé ñîäåðæèò ìàðêè òåëåôîíîâ, óñòàíàâëèâàåì êóðñîð â ñòðîêó ôîðìóë, ââîäèì çíàê "=" è êëèêàåì íà ÿ÷åéêó ñ äàííûì óñëîâèåì, çàêðûâàåì ñêîáêó;
Â) ñòàâèì çíàê óìíîæèòü "*", îòêðûâàåì ñêîáêó è âûäåëÿåì äèàïàçîí ÿ÷ååê, êîòîðûé ñîäåðæèò ìîäåëü òåëåôîíîâ, óñòàíàâëèâàåì êóðñîð â ñòðîêó ôîðìóë, ââîäèì çíàê "=" è êëèêàåì íà ÿ÷åéêó ñ äàííûì óñëîâèåì, çàêðûâàåì ñêîáêó;
Ã) ñòàâèì çíàê óìíîæèòü "*", âûäåëÿåì äèàïàçîí, êîòîðûé ñîäåðæèò âûðó÷êó, çàêðûâàåì ñêîáêó;
Ä) íàæèìàåì CTRL+SHIFT+ENTER - ôîðìóëà çàêëþ÷àåòñÿ â ôèãóðíûå ñêîáêè, ÷òî è äåëàåò åå ôîðìóëîé ìàññèâà.
Âíèìàíèå! Ôèãóðíûå ñêîáêè íóæíî ïðîñòàâëÿòü èñêëþ÷èòåëüíî ïóòåì íàæàòèÿ êëàâèø CTRL+SHIFT+ENTER, åñëè èõ ïîñòàâèòü âðó÷íóþ, ôîðìóëà íå áóäåò ðàáîòàòü.
 ðåçóëüòàòå ó íàñ ïðîñóììèðîâàíà âûðó÷êà ïî òåëåôîíàì Samsung Å2121, êîòîðûå ïðîäàíû â Ìàãàçèíå ¹1
Êîëè÷åñòâî óñëîâèé ìîæåò áûòü ðàçíûì, íî åñëè íå âñå óñëîâèÿ çàäàíû, òî ñóììèðîâàíèå íå áóäåò ïðîâîäèòüñÿ.  ñëó÷àå ðàáîòû ñ áîëüøèìè òàáëèöàìè è ñëîæíûìè àðãóìåíòàìè, â êà÷åñòâå óñëîâèé ìîæíî óñòàíîâèòü ïîëå ñî ñïèñêîì, êîòîðîå áóäåò ñîäåðæàòü âñå íåîáõîäèìûå àðãóìåíòû.
2. Ôóíêöèÿ Ïðîìåæóòî÷íûå èòîãè.
Åå ìîæíî èñïîëüçîâàòü äëÿ âû÷èñëåíèé äàííûõ îòîáðàííûõ ñ ïîìîùüþ Àâòîôèëüòðà.  êîíöå òàáëèöû â ñòîëáöàõ, â êîòîðûõ íåîáõîäèìî ïðîâåñòè âû÷èñëåíèÿ, ââîäèì ôóíêöèþ ÏÐÎÌÅÆÓÒÎ×ÍÛÅ.ÈÒÎÃÈ (âêëàäêà Ôîðìóëû ãðóïïà Áèáëèîòåêà ôóíêöèé êíîïêà Âñòàâèòü ôóíêöèþ èëè êíîïêà Ññûëêè è ìàññèâû).
 ïîëå Íîìåð_ôóíöèè - íåîáõîäèìî óêàçàòü, ïîðÿäêîâûé íîìåð ôóíêöèè, êîòîðàÿ áóäåò ïðîâîäèòü âû÷èñëåíèå èòîãîâûõ çíà÷åíèé. Íîìåðà ôóíêöèé â MS Excel óæå íàçíà÷åí ïî óìîë÷àíèþ, âàì íóæíî òîëüêî âûáðàòü íóæíûé.
Íîìåð_ôóíêöèè Ôóíêöèÿ 1 ÑÐÇÍÀ× 2 Ñ×¨Ò 3 ÑרÒÇ 4 ÌÀÊÑ 5 ÌÈÍ 6 ÏÐÎÈÇÂÅÄ 7 ÑÒÀÍÄÎÒÊËÎÍ 8 ÑÒÀÍÄÎÒÊËÎÍÏ 9 ÑÓÌÌ 10 ÄÈÑÏ 11 ÄÈÑÏÐ
 ïîëå Ññûëêà1 - âûäåëÿåì äèàïàçîí ÿ÷ååê, çíà÷åíèÿ êîòîðûõ äîëæíû âû÷èñëÿòüñÿ.
Íàæèìàåì ÎÊ.
Òåïåðü, ïðè âûáîðå çíà÷åíèé ñ ïîìîùüþ Ôèëüòðà, ó âàñ áóäåò àâòîìàòè÷åñêè ïîäñ÷èòûâàòüñÿ çíà÷åíèÿ ïî âûáðàííûì ôóíöèÿì.
