мазмуну
Пивотук таблицалар Excelдеги эң күчтүү куралдардын бири. Алар чычканды бир нече чыкылдатуу менен чоң көлөмдөгү маалыматтардын ар кандай резюмелерин талдап, жыйынтыктоого мүмкүндүк берет. Бул макалада биз пивот таблицалары менен таанышып, алар эмне экенин түшүнөбүз, аларды кантип түзүүнү жана ыңгайлаштырууну үйрөнөбүз.
Бул макала Excel 2010 аркылуу жазылган. Пивот таблицалар концепциясы жылдар бою өзгөргөн жок, бирок аларды түзүү ыкмасы Excelдин ар бир жаңы версиясында бир аз башкача. Эгер сизде Excel 2010 эмес версиясы болсо, анда бул макаладагы скриншоттор экраныңызда көргөнүңүздөн айырмаланып турганына даяр болуңуз.
тарыхында бир үзүм
Электрондук жадыбал программалык камсыздоонун алгачкы күндөрүндө Lotus 1-2-3 эреже шары. Анын үстөмдүгү ушунчалык толук болгондуктан, Microsoftтун Лотустун альтернативасы катары өзүнүн программалык камсыздоосун (Excel) иштеп чыгуу аракети убакытты текке кетирүү сыяктуу көрүндү. Эми 2010-жылга тез алга! Excel электрондук жадыбалдарда Lotus коду өзүнүн тарыхында эч качан жасаганына караганда үстөмдүк кылат жана дагы деле Lotus колдонгон адамдардын саны нөлгө жакын. Бул кантип болушу мүмкүн? Окуялардын мындай кескин бурулушуна эмне себеп болду?
Аналитиктер эки негизги факторду аныкташат:
- Биринчиден, Lotus Windows деп аталган жаңы GUI платформасын жөн эле көпкө созулбай турган мода деп чечти. Алар Lotus 1-2-3тин Windows версиясын куруудан баш тартышкан (бирок бир нече жылга гана), алардын программалык камсыздоосунун DOS версиясы бардык керектөөчүлөргө эч качан керек болоорун алдын ала айтышкан. Microsoft табигый түрдө Excel атайын Windows үчүн иштелип чыккан.
- Экинчиден, Microsoft Excel программасына Lotus 1-2-3те жок болгон PivotTables деп аталган куралды киргизди. Excel үчүн эксклюзивдүү PivotTables абдан пайдалуу болуп чыкты, ошондуктан адамдар аларда жок Lotus 1-2-3 менен улантуунун ордуна жаңы Excel программалык топтомун карманышты.
Пивот таблицалары жалпысынан Windowsтун ийгилигин баалабай коюу менен бирге, Lotus 1-2-3 үчүн өлүм маршын ойноп, Microsoft Excel программасынын ийгилигине алып келди.
Пивот столдор деген эмне?
Ошентип, PivotTables деген эмнени мүнөздөш үчүн мыкты жолу кайсы?
Жөнөкөй сөз менен айтканда, пивот таблицалары бул маалыматтарды талдоону жеңилдетүү үчүн түзүлгөн кээ бир маалыматтардын жыйындысы. Кол менен түзүлгөн жыйынтыктардан айырмаланып, Excel пивот таблицалары интерактивдүү. Түзүлгөндөн кийин, алар сиз күткөн сүрөттү бербесе, аларды оңой эле өзгөртө аласыз. Чычкандын бир-эки чыкылдатуусу менен, жыйынтыктарды которсо болот, ошентип мамычалардын аталыштары саптын аталыштарына айланат жана тескерисинче. Пивот таблицалары менен көп нерселерди кыла аласыз. Пивот таблицалардын бардык өзгөчөлүктөрүн сөз менен сүрөттөп берүүнүн ордуна, аны иш жүзүндө көрсөтүү оңой...
Пивот таблицалары менен анализдеген маалыматтарыңыз туш келди болушу мүмкүн эмес. Бул кандайдыр бир тизме сыяктуу чийки маалыматтар болушу керек. Мисалы, бул компаниянын акыркы алты айда жасаган сатууларынын тизмеси болушу мүмкүн.
Төмөнкү сүрөттө көрсөтүлгөн маалыматтарды карагыла:
Белгилей кетсек, бул чийки маалымат эмес, анткени ал буга чейин жалпыланган. B3 уячасында биз $30000 көрүп жатабыз, бул Джеймс Куктун январь айында жасаган жалпы натыйжасы. Анда баштапкы маалыматтар кайда? 30000 доллар цифрасы кайдан келди? Бул ай сайын алынган сатуулардын баштапкы тизмеси кайда? Кимдир бирөө акыркы алты айдагы бардык сатуу маалыматтарын уюштуруу жана сорттоо жана аны биз көрүп турган жыйынтыктардын таблицасына айландыруу боюнча чоң жумуш жасаганы анык. Канча убакыт өттү деп ойлойсуз? Саат? Саат он?
Чындыгында, жогорудагы таблица өзөктүү таблица эмес. Ал башка жерде сакталган чийки маалыматтардан колго жасалган жана аны иштетүү үчүн кеминде эки саат талап кылынган. Ушундай жыйынды таблицаны бир нече секунданын ичинде пивот таблицаларын колдонуу менен түзсө болот. Келгиле, кантип билели...
Эгерде биз баштапкы сатуу тизмесине кайрылсак, ал төмөнкүдөй көрүнөт:
Сиз пивот таблицаларынын жардамы менен соодалардын бул тизмесинен бир нече секунданын ичинде Excelде биз жогоруда талдаган айлык сатуу отчетун түзө аларыбызга таң калышыңыз мүмкүн. Ооба, биз муну жана башкаларды жасай алабыз!
Пивот таблицасын кантип түзүү керек?
Биринчиден, Excel барагында кээ бир булак маалыматтары бар экенин текшериңиз. каржылык бүтүмдөрдүн тизмеси пайда болгон эң типтүү болуп саналат. Чынында, бул кандайдыр бир тизме болушу мүмкүн: кызматкердин байланыш маалыматтары, CD коллекциясы же компанияңыздын күйүүчү май керектөө маалыматтары.
Ошентип, биз Excelди баштайбыз ... жана мындай тизмени жүктөйбүз ...
Бул тизмени Excelде ачкандан кийин, пивот таблицасын түзө баштайбыз.
Бул тизмеден каалаган уячаны тандаңыз:
Андан кийин өтмөктө күйгүзүп коюу. (Киргизүү) буйругун тандоо Пивот таблица (пивот таблица):
Диалог кутучасы пайда болот Пивот таблицасын түзүү (пивот таблицасын түзүү) сизге эки суроо менен:
- Жаңы пивот таблицасын түзүү үчүн кандай маалыматтарды колдонуу керек?
- Пивот столду кайда коюу керек?
Мурунку кадамда биз тизме уячаларынын бирин тандап алгандыктан, толук тизме автоматтык түрдө пивот таблицасын түзүү үчүн тандалат. Биз башка диапазонду, башка таблицаны, ал тургай, Access же MS-SQL маалымат базасынын таблицасы сыяктуу кээ бир тышкы маалымат булагын тандай аларыбызды эске алыңыз. Мындан тышкары, биз жаңы пивот таблицасын кайда жайгаштырууну тандашыбыз керек: жаңы баракка же бар болгондордун бирине. Бул мисалда биз вариантты тандайбыз - New Worksheet (жаңы баракка):
Excel жаңы баракты түзүп, ага бош пивот таблицасын жайгаштырат:
Пивот таблицадагы каалаган уячаны басканда, башка диалог терезеси пайда болот: Пивот таблица талаасынын тизмеси (пивот таблица талаалары).
Диалог терезесинин жогору жагындагы талаалардын тизмеси – бул баштапкы тизмедеги бардык аталыштардын тизмеси. Экрандын ылдый жагындагы төрт бош аймак сиз маалыматтарды кантип жыйынтыктоону каалай турганыңызды Пивот таблицага айтууга мүмкүндүк берет. Бул жерлер бош болсо, столдо да эч нерсе жок. Бизге болгону рубрикаларды үстүнкү аймактан төмөнкү бош жерлерге сүйрөө керек. Ошол эле учурда, биздин көрсөтмөлөргө ылайык, автоматтык түрдө пивот таблицасы түзүлөт. Эгерде биз ката кетирсек, анда биз ылдыйкы аймактагы рубрикаларды алып салсак же алардын ордуна башкаларды сүйрөп кете алабыз.
Аянт баалары (Мааниси) төртөөнүн эң маанилүүсү болсо керек. Бул аймакка кайсы рубрика жайгаштырылса, кандай маалыматтар жалпыланат (сумма, орточо, максимум, минимум ж.б.) Бул дээрлик дайыма сандык маанилер. Бул аймакта орун үчүн мыкты талапкер рубрика астындагы маалыматтар болуп саналат суммасы Биздин баштапкы таблицабыздын (баасы). Бул аталышты аймакка сүйрөңүз баалары (Баалар):
Аталышын эске алыңыз суммасы азыр белги менен белгиленген, жана аймакта баалары (Баалар) жазуу пайда болду Сумма (Сумма талаасы Сумма), тилкени көрсөтүү менен суммасы жыйынтыктады.
Эгерде биз пивот таблицанын өзүн карасак, мамычадагы бардык маанилердин суммасын көрөбүз суммасы оригиналдуу таблица.
Ошентип, биздин биринчи пивот таблицасы түзүлдү! Ыңгайлуу, бирок өзгөчө таасирдүү эмес. Биз, балким, азыркы маалыматтарыбызга караганда көбүрөөк маалымат алгыбыз келет.
Келгиле, баштапкы маалыматтарга кайрылып, бул сумманы бөлүү үчүн колдонула турган бир же бир нече тилкелерди аныктоого аракет кылалы. Мисалы, биз сатуунун жалпы суммасы ар бир сатуучу үчүн жекече эсептелгендей кылып өздүк таблицаны түзө алабыз. Ошол. катарлар компаниядагы ар бир сатуучунун аты-жөнү жана алардын жалпы сатуу суммасы менен өзөктүү таблицага кошулат. Бул натыйжага жетүү үчүн жөн гана аталышты сүйрөңүз сатуучу адам (сатуу өкүлү) аймакка Катар энбелгилери (Саптар):
Бул кызыктуураак болот! Биздин PivotTable түзүлө баштады…
Пайдасын көрөсүзбү? Бир нече чыкылдатуу менен биз кол менен түзүү үчүн абдан көп убакытты талап кылган таблицаны түздүк.
Дагы эмне кылсак болот? Кандайдыр бир мааниде биздин пивот столубуз даяр. Биз баштапкы маалыматтардын пайдалуу корутундусун түздүк. Маанилүү маалымат мурунтан эле алынган! Бул макаланын калган бөлүгүндө биз татаалыраак пивот таблицаларын түзүүнүн кээ бир жолдорун карап чыгабыз, ошондой эле аларды кантип ыңгайлаштырууну үйрөнөбүз.
Пивот таблицасын орнотуу
Биринчиден, биз эки өлчөмдүү пивот таблицасын түзө алабыз. Келгиле, муну мамычанын аталышын колдонуп жасайлы Төлөө ыкмасы (Төлөө ыкмасы). Жөн гана аталышты сүйрөңүз Төлөө ыкмасы аймакка Мамыча белгилери (Мамычалар):
Биз жыйынтык алабыз:
Абдан сонун көрүнөт!
Эми үч өлчөмдүү таблицаны жасайлы. Мындай үстөл кандай болот? көрөлү…
Башты сүйрөө таңгак (Комплекс) аймакка отчет чыпкалары (Чыпкалар):
Анын кайда экенин байкаңыз…
Бул бизге отчетту "Кайсы эс алуу комплекси үчүн төлөнгөн" деген негизде чыпкалоо мүмкүнчүлүгүн берет. Мисалы, биз бардык комплекстер үчүн сатуучулар жана төлөм ыкмалары боюнча бөлүштүрүүнү көрө алабыз же чычкандын бир нече чыкылдатуулары менен пивот таблицанын көрүнүшүн өзгөртүп, комплекске заказ бергендер үчүн гана бирдей бөлүштүрүүнү көрсөтө алабыз. Күн издегендер.
Демек, эгер сиз муну туура түшүнсөңүз, анда биздин пивот столубуз үч өлчөмдүү деп атоого болот. Жөндөөнү уланталы…
Эгерде күтүлбөгөн жерден пивот таблицада чек жана кредиттик карта менен төлөө (б.а. накталай эмес төлөм) гана көрсөтүлүшү керек экени аныкталса, анда биз аталыштын дисплейин өчүрө алабыз. Cash (Накталай акча). Бул үчүн, жанында Мамыча белгилери ылдый жебени чыкылдатып, ачылуучу менюдагы кутучаны алып салыңыз Cash:
Эми биздин пивот столубуз кандай экенин карап көрөлү. Көрүнүп тургандай, колонна Cash андан жоголду.
Excelде пивот таблицаларын форматтоо
Пивот таблицалары, албетте, абдан күчтүү курал, бирок азырынча натыйжалар бир аз жөнөкөй жана кызыксыз көрүнөт. Мисалы, биз кошкон сандар доллардын суммасына окшошпойт – алар жөн гана сандар. Келгиле, муну оңдойлу.
Мындай кырдаалда көнүп калган нерсеңизди жасоо жана жөн гана бүт таблицаны (же бүт баракты) тандап, керектүү форматты коюу үчүн куралдар панелиндеги стандарттуу сандарды форматтоо баскычтарын колдонуу азгырык. Бул ыкманын көйгөйү, эгер сиз келечекте пивот таблицанын түзүмүн өзгөртсөңүз (бул 99% кокустук менен болот), форматтоо жоголот. Бизге аны (дээрлик) туруктуу кылуунун жолу керек.
Биринчиден, кирүүнү табалы Сумма in баалары (Баалуулар) жана аны басыңыз. Пайда болгон менюдан нерсени тандаңыз Маани талаасынын жөндөөлөрү (Маани талаасынын параметрлери):
Диалог кутучасы пайда болот Маани талаасынын жөндөөлөрү (Маани талаасынын параметрлери).
Баскычты чыкылдатыңыз Сан форматы (Сан форматы), диалог кутусу ачылат. Клеткаларды форматтоо (уяча форматы):
Тизмеден Category (Сан форматтары) тандаңыз эсепке алуу (Финансылык) жана ондук орундардын санын нөлгө коюңуз. Эми бир нече жолу басыңыз OKбиздин негизги үстөлгө кайтуу үчүн.
Көрүнүп тургандай, сандар доллар өлчөмүндө форматталган.
Форматтоо менен алектенип жатканыбызда, келгиле, жалпы таблица үчүн форматты орнотуп алалы. Муну жасоонун бир нече жолу бар. Биз эң жөнөкөйсүн колдонобуз…
чыкылдатуу PivotTable куралдары: Дизайн (Пивот таблицалар менен иштөө: Конструктор):
Андан кийин, бөлүмдүн төмөнкү оң бурчундагы жебени чыкылдатуу менен менюну кеңейтиңиз Пивот таблицанын стилдери (PivotTable Styles) саптык стилдердин кеңири коллекциясын көрүү үчүн:
Каалаган ылайыктуу стилди тандап, өзүңүздүн пивот таблицаңыздагы натыйжаны караңыз:
Excelдеги башка PivotTable орнотуулары
Кээде даталар боюнча маалыматтарды чыпкалоо керек. Мисалы, биздин соода тизмегибизде даталар көп. Excel күн, ай, жыл жана башкалар боюнча маалыматтарды топтоого курал берет. Келгиле, анын кантип жасалганын карап көрөлү.
Алгач жазууну алып салыңыз. Төлөө ыкмасы аймактан Мамыча белгилери (Мамычалар). Бул үчүн, аны кайра аталыштар тизмесине сүйрөп, анын ордуна аталышты жылдырыңыз Брондолгон дата (бронь датасы):
Көрүнүп тургандай, бул биздин пивот столубузду убактылуу жараксыз кылып койду. Excel соода жасалган ар бир дата үчүн өзүнчө тилке түздү. Натыйжада биз абдан кенен дасторкон алдык!
Муну оңдоо үчүн каалаган датаны оң баскыч менен чыкылдатып, контексттик менюдан тандаңыз ТОП (Группа):
Топтоо диалог терезеси пайда болот. Биз тандайбыз ай (Айлар) жана басыңыз OK:
Voila! Бул таблица алда канча пайдалуу:
Айтмакчы, бул таблица макаланын башында көрсөтүлгөн менен дээрлик окшош, мында сатуунун суммасы кол менен түзүлгөн.
Сиз билишиңиз керек болгон дагы бир маанилүү жагдай бар! Сиз бир эмес, бир нече деңгээлдеги сап (же тилке) аталыштарын түзө аласыз:
... жана бул ушундай болот ...
Ошол эле колонналардын аталыштары (же чыпкалар) менен да жасалышы мүмкүн.
Келгиле, таблицанын баштапкы формасына кайрылып, суммалардын ордуна орточо көрсөткүчтөрдү кантип көрсөтүүнү карап көрөлү.
Баштоо үчүн, чыкылдатыңыз Сумма жана пайда болгон менюдан тандаңыз Маани талаасынын жөндөөлөрү (Маани талаасынын параметрлери):
Тизме Маани талаасын жыйынтыктоо (Операция) диалог терезесинде Маани талаасынын жөндөөлөрү (Маани талаасынын параметрлери) тандаңыз орто (Орто):
Ошол эле учурда, биз бул жерде турганда, келгиле, өзгөрөлү CustomName (Ыңгайлаштырылган аталыш) менен Орточо сумма (Сумма талаасы Сумма) кыскараак нерсеге. Бул талаага окшош нерсени киргизиңиз Орт:
басма сөз OK жана эмне болорун көр. Бардык маанилер жалпысынан орточого өзгөргөнүн, ал эми таблицанын аталышы (жогорку сол уячада) Орт:
Кааласаңыз, сиз дароо бир пивот таблицага жайгаштырылган сумманы, орточо жана санды (сатууларды) ала аласыз.
Бул жерде бош пивот столунан баштап муну кантип жасоо керектиги боюнча кадам-кадам көрсөтмөсү:
- Башты сүйрөө сатуучу адам (сатуу өкүлү) аймакка Мамыча белгилери (Мамычалар).
- Аталышын үч жолу сүйрөңүз суммасы (Баасы) аймакка баалары (Баалуулар).
- Биринчи талаа үчүн суммасы аталышын өзгөртүү Бардыгы болуп (суммасы) жана бул талаадагы сан форматы болуп саналат эсепке алуу (Финансы). Ондук орундардын саны нөлгө барабар.
- Экинчи талаа суммасы ысым Орточоe, ал үчүн операцияны орнотуңуз орто (Орто) жана бул талаадагы сан форматы да өзгөрөт эсепке алуу (Финансылык) нөл ондук белгилери менен.
- Үчүнчү талаа үчүн суммасы наам коюу эсептөө жана ага операция - эсептөө (Саны)
- Ичинде Мамыча белгилери (Мамычалар) талаасы автоматтык түрдө түзүлөт Σ баалуулуктар (Σ Маанилер) – аны аймакка сүйрөңүз Катар энбелгилери (саптар)
Бул жерде биз эмне менен аяктайбыз:
Жалпы суммасы, орточо наркы жана сатуулардын саны – баары бир пивот таблицада!
жыйынтыктоо
Microsoft Excelдеги пивот таблицалары көптөгөн функцияларды жана орнотууларды камтыйт. Мындай кичинекей макалада алардын баарын камтууга да жакын эмес. Пивот таблицаларынын бардык мүмкүнчүлүктөрүн толук сүрөттөш үчүн кичинекей китеп же чоң веб-сайт керектелет. Кайраттуу жана кызыккан окурмандар пивот таблицаларын изилдөөнү уланта алышат. Бул үчүн, пивот таблицасынын дээрлик бардык элементтерин оң баскыч менен чыкылдатып, кандай функциялар жана орнотуулар ачылганын көрүңүз. Тасмада сиз эки өтмөктү таба аласыз: Пивот таблица куралдары: Параметрлер (анализ) жана дизайн (Конструктор). Ката кетирүүдөн коркпоңуз, сиз ар дайым PivotTableди жок кылып, кайра баштасаңыз болот. Сизде көптөн бери DOS жана Lotus 1-2-3 колдонуучуларында болбогон мүмкүнчүлүк бар.