Мазмұны
Жиынтық кестелер Excel бағдарламасының ең қуатты құралдарының бірі болып табылады. Олар тінтуірді бірнеше рет басу арқылы үлкен көлемдегі деректердің әртүрлі жиынтықтарын талдауға және қорытындылауға мүмкіндік береді. Бұл мақалада біз жиынтық кестелермен танысамыз, олардың не екенін түсінеміз, оларды құру және теңшеу жолдарын үйренеміз.
Бұл мақала Excel 2010 көмегімен жазылған. Жиынтық кестелер концепциясы жылдар бойына көп өзгерген жоқ, бірақ Excel бағдарламасының әрбір жаңа нұсқасында оларды жасау тәсілі сәл өзгеше. Егер сізде 2010 емес Excel нұсқасы болса, осы мақаладағы скриншоттар экранда көргеннен өзгеше болатынына дайын болыңыз.
тарихы Сәл
Электрондық кесте бағдарламалық жасақтамасының алғашқы күндерінде Lotus 1-2-3 ереже добы. Оның үстемдігінің толық болғаны сонша, Microsoft корпорациясының Lotus-қа балама ретінде өзінің бағдарламалық жасақтамасын (Excel) әзірлеуге күш салуы уақытты босқа кетіру сияқты көрінді. Енді 2010 жылға қарай алға! Excel электрондық кестелерде Lotus коды өз тарихында бұрын-соңды жасағаннан көбірек үстемдік етеді және әлі де Lotus пайдаланатын адамдар саны нөлге жақын. Бұл қалай болуы мүмкін? Оқиғалардың мұндай күрт өзгеруіне не себеп болды?
Сарапшылар екі негізгі факторды анықтайды:
- Біріншіден, Lotus бұл Windows деп аталатын жаңа GUI платформасы ұзақ уақытқа созылмайтын өтпелі сән деп шешті. Олар Lotus 1-2-3 Windows нұсқасын жасаудан бас тартты (бірақ бірнеше жыл бойы), олардың бағдарламалық жасақтамасының DOS нұсқасы барлық тұтынушыларға қажет болатынын болжады. Microsoft Excel-ді Windows үшін арнайы әзірледі.
- Екіншіден, Microsoft Excel бағдарламасында Lotus 1-2-3 жүйесінде жоқ жиынтық кестелер деп аталатын құралды ұсынды. Эксклюзивті Excel бағдарламасына арналған жиынтық кестелер өте пайдалы болғаны сонша, адамдар оларда жоқ Lotus 1-2-3 қолданбасымен емес, жаңа Excel бағдарламалық жасақтамасын ұстануға бейім болды.
Жиынтық кестелер жалпы Windows жүйесінің жетістігін бағаламаумен қатар, Lotus 1-2-3 үшін өлім маршын ойнады және Microsoft Excel табысына әкелді.
Жиынтық кестелер дегеніміз не?
Сонымен, жиынтық кестелердің не екенін сипаттаудың ең жақсы жолы қандай?
Қарапайым тілмен айтқанда, жиынтық кестелер бұл деректерді талдауды жеңілдету үшін жасалған кейбір деректердің жиынтықтары. Қолмен жасалған қорытындылардан айырмашылығы, Excel жиынтық кестелері интерактивті болып табылады. Жасалғаннан кейін, олар сіз күткен суретті бермесе, оларды оңай өзгертуге болады. Тінтуірдің бірнеше рет шертуімен қорытындыларды баған тақырыптары жол тақырыптарына және керісінше айналдыру үшін аударуға болады. Жиынтық кестелер арқылы көп нәрсені жасауға болады. Жиынтық кестелердің барлық мүмкіндіктерін сөзбен сипаттауға тырысудың орнына, оны іс жүзінде көрсету оңайырақ ...
Жиынтық кестелер арқылы талдайтын деректер кездейсоқ болуы мүмкін емес. Бұл қандай да бір тізім сияқты өңделмеген деректер болуы керек. Мысалы, бұл компанияның соңғы алты айдағы сатылымдарының тізімі болуы мүмкін.
Төмендегі суретте көрсетілген деректерді қараңыз:
Бұл өңделмеген деректер емес екенін ескеріңіз, өйткені ол қазірдің өзінде жинақталған. B3 ұяшығында біз $30000 көреміз, бұл Джеймс Куктың қаңтарда жасаған жалпы нәтижесі болуы мүмкін. Сонда бастапқы деректер қайда? 30000 XNUMX долларлық көрсеткіш қайдан келді? Осы айлық жиынтық алынған сатылымдардың бастапқы тізімі қайда? Біреу соңғы алты айдағы барлық сатылым деректерін ұйымдастырып, сұрыптап, оны біз көріп отырған қорытындылар кестесіне айналдыруда керемет жұмыс жасағаны анық. Қанша уақыт өтті деп ойлайсыз? Сағат? Сағат он?
Мәселе мынада, жоғарыдағы кесте жиынтық кесте емес. Ол басқа жерде сақталған шикізат деректерінен қолмен жасалған және өңдеуге кемінде екі сағат қажет болды. Дәл осындай жиынтық кестені бірнеше секунд ішінде жиынтық кестелер арқылы жасауға болады. Қалай екенін анықтайық...
Егер бастапқы сатылымдар тізіміне оралсақ, ол келесідей болады:
Сіз бұл сауда-саттық тізімінен жиынтық кестелердің көмегімен және бірнеше секунд ішінде біз жоғарыда талдаған Excel бағдарламасында ай сайынғы сату есебін жасай алатынымызға таң қалуыңыз мүмкін. Иә, біз мұны және т.б. істей аламыз!
Жиынтық кестені қалай құруға болады?
Алдымен Excel парағында кейбір бастапқы деректер бар екеніне көз жеткізіңіз. Қаржылық транзакциялардың тізімі ең тән болып табылады. Шын мәнінде, бұл кез келген нәрсенің тізімі болуы мүмкін: қызметкердің байланыс мәліметтері, CD жинағы немесе компанияңыздың жанармай тұтыну деректері.
Сонымен, біз Excel бағдарламасын іске қосамыз ... және осындай тізімді жүктейміз ...
Бұл тізімді Excel бағдарламасында ашқаннан кейін біз жиынтық кестені құруға кірісеміз.
Осы тізімнен кез келген ұяшықты таңдаңыз:
Содан кейін қойындыда енгізу (Кірістіру) пәрменін таңдау Жиынтық кесте (Жиынтық кесте):
Диалогтық терезе пайда болады Жиынтық кестені жасаңыз (Жиынтық кестені құру) сізге екі сұрақ:
- Жаңа жиынтық кесте жасау үшін қандай деректерді пайдалану керек?
- Жиынтық кестені қайда қою керек?
Алдыңғы қадамда біз тізім ұяшықтарының бірін таңдағандықтан, жиынтық кесте жасау үшін бүкіл тізім автоматты түрде таңдалады. Біз басқа ауқымды, басқа кестені және тіпті Access немесе MS-SQL дерекқор кестесі сияқты кейбір сыртқы деректер көзін таңдай алатынымызды ескеріңіз. Сонымен қатар, біз жаңа жиынтық кестені қайда орналастыру керектігін таңдауымыз керек: жаңа парақта немесе бұрыннан барлардың бірінде. Бұл мысалда біз опцияны таңдаймыз - Жаңа жұмыс парағы (жаңа параққа):
Excel жаңа парақ жасайды және оған бос жиынтық кестені орналастырады:
Жиынтық кестедегі кез келген ұяшықты басқан кезде басқа диалогтық терезе пайда болады: Жиынтық кесте өрістерінің тізімі (Жиынтық кесте өрістері).
Диалогтық терезенің жоғарғы жағындағы өрістер тізімі бастапқы тізімдегі барлық тақырыптардың тізімі болып табылады. Экранның төменгі жағындағы төрт бос аймақ жиынтық кестеге деректерді қалай қорытындылағыңыз келетінін айтуға мүмкіндік береді. Бұл аймақтар бос болғанша, кестеде де ештеңе жоқ. Бізге тек үстіңгі аймақтағы тақырыптарды төмендегі бос аймақтарға сүйреу ғана қалады. Сонымен бірге, біздің нұсқауларымызға сәйкес жиынтық кесте автоматты түрде жасалады. Егер біз қателессек, тақырыптарды төменгі аймақтан алып тастай аламыз немесе оларды ауыстыру үшін басқаларын сүйреуге болады.
аудан Құндылықтар (Мағыналары) төртеуінің ең маңыздысы болса керек. Бұл аймаққа қандай тақырып қойылса, қандай деректер қорытылатынын анықтайды (қосынды, орташа, максимум, минимум және т.б.) Бұл әрқашан дерлік сандық мәндер. Бұл аймақтағы орынға тамаша үміткер - тақырып астындағы деректер сомасы Бастапқы кестеміздің (құны). Бұл тақырыпты аймаққа сүйреңіз Құндылықтар (Құндылықтар):
Тақырыпқа назар аударыңыз сомасы енді құсбелгісімен және аймақта белгіленген Құндылықтар (Мәндер) жазба пайда болды Сома сомасы (Сома өрісі Сома), баған екенін көрсетеді сомасы қорытындылады.
Егер біз жиынтық кестенің өзіне қарасақ, бағандағы барлық мәндердің қосындысын көреміз сомасы түпнұсқа кесте.
Сонымен, біздің алғашқы жиынтық кестеміз жасалды! Ыңғайлы, бірақ әсіресе әсерлі емес. Біздің деректеріміз туралы қазіргі уақыттағыдан көбірек ақпарат алғымыз келетін шығар.
Түпнұсқа деректерге жүгініп, осы соманы бөлу үшін пайдалануға болатын бір немесе бірнеше бағандарды анықтауға тырысайық. Мысалы, біз жиынтық кестемізді сатудың жалпы сомасы әрбір сатушы үшін жеке есептелетіндей етіп жасай аламыз. Анау. жолдар компаниядағы әрбір сатушының аты-жөні және олардың жалпы сату сомасы көрсетілген жиынтық кестемізге қосылады. Бұл нәтижеге жету үшін тақырыпты сүйреңіз Сатушы (сату өкілі) аймаққа Жол белгілері (Жолдар):
Бұл одан да қызықты болады! Біздің жиынтық кесте қалыптаса бастады…
Пайдасын көресіз бе? Бірнеше рет басу арқылы біз қолмен жасау өте ұзақ уақыт алатын кестені жасадық.
Тағы не істей аламыз? Бір мағынада, біздің негізгі үстел дайын. Біз бастапқы деректердің пайдалы қысқаша мазмұнын жасадық. Маңызды ақпарат қазірдің өзінде алынды! Осы мақаланың қалған бөлігінде күрделірек жиынтық кестелерді жасаудың кейбір жолдарын қарастырамыз, сонымен қатар оларды теңшеу жолын үйренеміз.
Жиынтық кестені орнату
Біріншіден, біз екі өлшемді жиынтық кесте жасай аламыз. Мұны баған тақырыбын пайдаланып жасайық Төлем тәсілі (Төлем тәсілі). Тек тақырыпты сүйреңіз Төлем тәсілі аймаққа Баған белгілері (Бағандар):
Біз нәтиже аламыз:
Өте керемет көрінеді!
Енді үш өлшемді кесте құрастырайық. Мұндай үстел қандай болады? Қарайық…
Тақырыпты сүйреңіз пакет (Кешен) ауданға есеп сүзгілері (Сүзгілер):
Оның қайда екенін ескеріңіз…
Бұл бізге есепті «Қай демалыс кешеніне төленгені» негізінде сүзуге мүмкіндік береді. Мысалы, біз барлық кешендер үшін сатушылар және төлем әдістері бойынша бөлуді көре аламыз немесе тінтуірді бірнеше рет басу арқылы жиынтық кестенің көрінісін өзгертіп, кешенге тапсырыс бергендер үшін бірдей бөлуді көрсете аламыз. Күнкөргіштер.
Сонымен, егер сіз мұны дұрыс түсінсеңіз, онда біздің жиынтық кестені үш өлшемді деп атауға болады. Реттеуді жалғастырайық...
Егер кенеттен жиынтық кестеде тек чек және несие картасы бойынша төлем (яғни қолма-қол ақшасыз төлем) көрсетілуі керек екені анықталса, онда тақырыптың дисплейін өшіруге болады. ақша (Қолма-қол ақша). Бұл үшін жанында Баған белгілері төмен көрсеткіні басып, ашылмалы мәзірдегі құсбелгіні алып тастаңыз ақша:
Енді біздің жиынтық кестеміз қандай болатынын көрейік. Көріп отырғаныңыздай, баған ақша одан жоғалып кетті.
Excel бағдарламасында жиынтық кестелерді пішімдеу
Жиынтық кестелер өте қуатты құрал екені анық, бірақ әзірге нәтижелер біршама қарапайым және қызықсыз көрінеді. Мысалы, біз қосатын сандар доллар сомасына ұқсамайды – олар жай ғана сандар. Осыны түзетейік.
Мұндай жағдайда үйреніп қалған нәрсені істеу қызықтырады және жай ғана бүкіл кестені (немесе бүкіл парақты) таңдап, қажетті пішімді орнату үшін құралдар тақтасындағы стандартты сандарды пішімдеу түймелерін пайдаланыңыз. Бұл тәсілдің мәселесі, егер сіз болашақта жиынтық кестенің құрылымын өзгертсеңіз (бұл 99% ықтималдықпен орын алады), пішімдеу жоғалады. Бізге қажет нәрсе - оны (дерлік) тұрақты ету жолы.
Алдымен жазбаны табайық Сома сомасы in Құндылықтар (Мәндер) және оны басыңыз. Пайда болған мәзірде элементті таңдаңыз Мән өрісінің параметрлері (Мән өрісінің опциялары):
Диалогтық терезе пайда болады Мән өрісінің параметрлері (Мән өрісінің опциялары).
баспасөз Сан форматы (Сан пішімі), диалогтық терезе ашылады. Ұяшықтарды форматтау (ұя форматы):
Тізімнен санат (Сан пішімдері) таңдаңыз Бухгалтерлік есеп (Қаржылық) және ондық таңбалардың санын нөлге қойыңыз. Енді бірнеше рет басыңыз OKбіздің негізгі үстелімізге оралу үшін.
Көріп отырғаныңыздай, сандар доллар сомасы ретінде пішімделген.
Біз пішімдеумен айналысып жатқанда, бүкіл жиынтық кестенің пішімін орнатайық. Мұны істеудің бірнеше жолы бар. Біз қарапайымдысын қолданамыз ...
басыңыз Жиынтық кесте құралдары: дизайн (Жиынтық кестелермен жұмыс: Конструктор):
Содан кейін бөлімнің төменгі оң жақ бұрышындағы көрсеткіні басу арқылы мәзірді кеңейтіңіз Жиынтық кесте мәнерлері (жиынтық кесте мәнерлері) кірістірілген стильдердің кең жинағын көру үшін:
Кез келген қолайлы стильді таңдап, жиынтық кестедегі нәтижені қараңыз:
Excel бағдарламасындағы басқа жиынтық кесте параметрлері
Кейде деректерді күндер бойынша сүзгілеу қажет. Мысалы, біздің сауда-саттық тізімімізде көп, көп күндер бар. Excel бағдарламасы деректерді күн, ай, жыл және т.б. бойынша топтастыру құралын ұсынады. Оның қалай жасалғанын көрейік.
Алдымен жазбаны алып тастаңыз. Төлем тәсілі облыстан Баған белгілері (Бағандар). Бұл әрекетті орындау үшін, оны тақырыптар тізіміне қайта апарыңыз және оның орнына тақырыпты жылжытыңыз Брондау күні (брондау күні):
Көріп отырғаныңыздай, бұл біздің жиынтық кестемізді уақытша жарамсыз етті. Excel сауда жасалған әрбір күн үшін бөлек баған жасады. Нәтижесінде біз өте кең үстелге ие болдық!
Мұны түзету үшін кез келген күнді тінтуірдің оң жақ түймешігімен басып, контекстік мәзірден таңдаңыз GROUP (Топ):
Топтау диалогтық терезесі пайда болады. Біз таңдаймыз айлар (Айлар) және басыңыз OK:
Voila! Бұл кесте әлдеқайда пайдалы:
Айтпақшы, бұл кесте мақаланың басында көрсетілгенмен бірдей дерлік, онда сатылымдар қолмен құрастырылған.
Сіз білуіңіз керек тағы бір маңызды мәселе бар! Жолдың (немесе бағанның) тақырыптарының бір емес, бірнеше деңгейлерін жасауға болады:
… және ол келесідей болады…
Дәл осылай баған тақырыптарымен (немесе тіпті сүзгілермен) де жасауға болады.
Кестенің бастапқы түріне оралайық және қосындылардың орнына орташа мәндерді қалай көрсетуге болатынын көрейік.
Бастау үшін түймесін басыңыз Сома сомасы және пайда болған мәзірден таңдаңыз Мән өрісінің параметрлері (Мән өрісінің опциялары):
Тізім Мән өрісін қорытындылау (Операция) диалогтық терезеде Мән өрісінің параметрлері (Мән өрісінің опциялары) таңдаңыз орташа (Орташа):
Сонымен бірге, біз осында болғанша, өзгерейік CustomName (арнайы атау) көмегімен Соманың орташа мәні (Сома өрісі Сома) қысқарақ нәрсеге. Бұл өріске келесідей нәрсені енгізіңіз Орт:
баспасөз OK және не болатынын қараңыз. Барлық мәндер қорытындылардан орташа мәндерге өзгергенін және кесте тақырыбы (жоғарғы сол жақ ұяшықта) өзгергенін ескеріңіз. Орт:
Қаласаңыз, бір жиынтық кестеде орналастырылған соманы, орташаны және санды (сатуды) дереу ала аласыз.
Мұнда бос жиынтық кестеден бастап мұны қалай жасауға болатыны туралы қадамдық нұсқаулық берілген:
- Тақырыпты сүйреңіз Сатушы (сату өкілі) аймаққа Баған белгілері (Бағандар).
- Тақырыпты үш рет сүйреңіз сомасы (құны) аймаққа Құндылықтар (Құндылықтар).
- Бірінші өріс үшін сомасы тақырыбын өзгертіңіз жалпы (Сома) және осы өрістегі сан пішімі болып табылады Бухгалтерлік есеп (Қаржылық). Ондық бөлшектердің саны нөлге тең.
- Екінші өріс сомасы ат Аверагe, ол үшін әрекетті орнатыңыз орташа (Орташа) және осы өрістегі сан пішімі де өзгереді Бухгалтерлік есеп (Қаржылық) ондық таңбалары нөл.
- Үшінші өріс үшін сомасы тақырып қойыңыз санау және оған операция – санау (Саны)
- Ішінде Баған белгілері (Бағандар) өрісі автоматты түрде жасалады Σ Мәндер (Σ Мәндер) – оны аймаққа сүйреңіз Жол белгілері (Сызықтар)
Міне, біз немен аяқтаймыз:
Жалпы сома, орташа мән және сату саны – барлығы бір жиынтық кестеде!
қорытынды
Microsoft Excel бағдарламасындағы жиынтық кестелерде көптеген мүмкіндіктер мен параметрлер бар. Осындай шағын мақалада олардың барлығын қамтуға да жақын емес. Жиынтық кестелердің барлық мүмкіндіктерін толық сипаттау үшін шағын кітап немесе үлкен веб-сайт қажет болады. Батыл және ізденімпаз оқырмандар жиынтық кестелерді зерттеуді жалғастыра алады. Мұны істеу үшін жиынтық кестенің кез келген дерлік элементін тінтуірдің оң жақ түймешігімен басып, қандай функциялар мен параметрлердің ашылатынын көріңіз. Таспада сіз екі қойындыны табасыз: Жиынтық кесте құралдары: Параметрлер (талдау) және жобалау (конструктор). Қателік жасаудан қорықпаңыз, сіз әрқашан жиынтық кестені жойып, қайта бастай аласыз. Сізде ұзақ уақыт бойы DOS және Lotus 1-2-3 пайдаланушыларында болмаған мүмкіндік бар.