Агуулга
Pivot хүснэгтүүд Excel-ийн хамгийн хүчирхэг хэрэгслүүдийн нэг юм. Эдгээр нь хулганын хэдхэн товшилтоор их хэмжээний өгөгдлийн янз бүрийн хураангуйг дүн шинжилгээ хийх, нэгтгэх боломжийг олгодог. Энэ нийтлэлд бид пивот хүснэгтүүдтэй танилцаж, тэдгээр нь юу болохыг ойлгох, тэдгээрийг хэрхэн үүсгэх, өөрчлөх талаар сурах болно.
Энэ нийтлэлийг Excel 2010 ашиглан бичсэн. Пивот хүснэгтийн тухай ойлголт олон жилийн туршид өөрчлөгдөөгүй ч Excel-ийн шинэ хувилбар болгонд тэдгээрийг үүсгэх арга зам нь арай өөр байдаг. Хэрэв танд Excel-ийн 2010 биш хувилбар байгаа бол энэ нийтлэл дэх дэлгэцийн агшин таны дэлгэцэн дээр харагдаж байгаа зүйлээс өөр байх болно гэдэгт бэлэн байгаарай.
Түүх бага
Хүснэгтийн программ хангамжийн эхэн үед Лотус 1-2-3 дүрмийн бөмбөг. Түүний давамгайлал маш бүрэн гүйцэд байсан тул Microsoft-ын өөрийн программ хангамжийг (Excel) Лотусын өөр хувилбар болгон хөгжүүлэх оролдлого нь цаг үрсэн мэт санагдсан. Одоо 2010 он руу хурдан урагшлаарай! Excel нь Лотус кодыг түүхэндээ хэзээ ч хийж байгаагүй хүснэгтийг давамгайлж байгаа бөгөөд одоог хүртэл Лотусыг ашигладаг хүмүүсийн тоо XNUMX дөхөж байна. Яаж ийм зүйл тохиолдож болох вэ? Үйл явдал ийм гайхалтай эргэлтийн шалтгаан юу байв?
Шинжээчид хоёр үндсэн хүчин зүйлийг тодорхойлдог.
- Эхлээд Лотус Windows нэртэй энэхүү шинэ GUI платформыг зүгээр л удаан үргэлжлэхгүй моод гэж шийдсэн. Тэд Lotus 1-2-3-ийн Windows хувилбарыг бүтээхээс татгалзсан (гэхдээ хэдхэн жилийн турш), тэдний програм хангамжийн DOS хувилбар нь хэрэглэгчдэд хэзээ ч хэрэгтэй байх болно гэж таамаглаж байсан. Майкрософт угаасаа Windows-д зориулсан Excel-ийг бүтээсэн.
- Хоёрдугаарт, Microsoft Excel-д Lotus 1-2-3-т байхгүй PivotTables хэмээх хэрэгслийг нэвтрүүлсэн. Excel-д зориулагдсан пивот хүснэгтүүд нь маш их хэрэгтэй байсан тул хүмүүс байхгүй байсан Lotus 1-2-3-ыг үргэлжлүүлэхийн оронд Excel-ийн шинэ програм хангамжийн багцыг ашиглах хандлагатай болсон.
Пивот хүснэгтүүд нь ерөнхийдөө Windows-ийн амжилтыг дутуу үнэлэхийн зэрэгцээ Лотус 1-2-3-ын үхлийн маршийг тоглож, Microsoft Excel-ийн амжилтыг авчирсан.
Пивот хүснэгт гэж юу вэ?
Пивот хүснэгт гэж юу болохыг тодорхойлох хамгийн сайн арга юу вэ?
Энгийнээр хэлбэл, пивот хүснэгтүүд нь эдгээр өгөгдөлд дүн шинжилгээ хийхэд хялбар болгох үүднээс бүтээгдсэн зарим өгөгдлийн хураангуй юм. Гараар үүсгэсэн нийлбэр дүнгээс ялгаатай нь Excel пивот хүснэгтүүд нь интерактив байдаг. Үүсгэсэн бол таны хүлээж байсан зургийг өгөхгүй бол тэдгээрийг хялбархан өөрчилж болно. Хулганы хоёр товшилтоор нийлбэр дүнг эргүүлж, баганын гарчиг нь мөрийн гарчиг болж, эсрэгээр нь эргүүлэх боломжтой. Пивот хүснэгтээр та олон зүйлийг хийх боломжтой. Пивот хүснэгтийн бүх шинж чанарыг үгээр тайлбарлахын оронд үүнийг практик дээр харуулах нь илүү хялбар байдаг ...
Пивот хүснэгт ашиглан дүн шинжилгээ хийх өгөгдөл санамсаргүй байж болохгүй. Энэ нь зарим төрлийн жагсаалт шиг түүхий түүхий өгөгдөл байх ёстой. Жишээлбэл, энэ нь компанийн сүүлийн зургаан сард хийсэн борлуулалтын жагсаалт байж болно.
Доорх зурагт үзүүлсэн өгөгдлийг харна уу.
Энэ нь аль хэдийн хураангуйлагдсан тул түүхий мэдээлэл биш гэдгийг анхаарна уу. В3 нүдэнд бид 30000 долларыг харж байгаа нь Жеймс Күүкийн 30000-р сард хийсэн нийт үр дүн байж магадгүй юм. Тэгвэл анхны өгөгдөл хаана байна вэ? XNUMX доллар хаанаас ирсэн бэ? Энэ сарын нийлбэр дүнгээс гарсан борлуулалтын анхны жагсаалт хаана байна вэ? Сүүлийн зургаан сарын борлуулалтын бүх мэдээллийг цэгцэлж, цэгцэлж, бидний харж буй нийт дүнгийн хүснэгт болгон хувиргах ажлыг хэн нэгэн маш сайн хийсэн нь тодорхой байна. Хэр их хугацаа зарцуулсан гэж та бодож байна вэ? Цаг? Арван цагт?
Үнэн хэрэгтээ дээрх хүснэгт нь пивот хүснэгт биш юм. Энэ нь өөр газар хадгалагдсан түүхий мэдээллээс гар аргаар хийгдсэн бөгөөд боловсруулахад дор хаяж хоёр цаг зарцуулсан. Ийм хураангуй хүснэгтийг пивот хүснэгт ашиглан хэдхэн секундын дотор үүсгэж болно. Яаж болохыг олж мэдье...
Хэрэв бид анхны борлуулалтын жагсаалт руу буцаж очвол энэ нь иймэрхүү харагдах болно.
Энэхүү арилжааны жагсаалтаас пивот хүснэгтүүдийн тусламжтайгаар хэдхэн секундын дотор бид Excel дээр сар бүрийн борлуулалтын тайланг гаргаж чадна гэдэгт та гайхах байх. Тийм ээ, бид үүнийг болон бусад зүйлийг хийж чадна!
Пивот хүснэгтийг хэрхэн үүсгэх вэ?
Эхлээд Excel хуудсанд зарим эх сурвалж мэдээлэл байгаа эсэхийг шалгаарай. Санхүүгийн гүйлгээний жагсаалт нь хамгийн түгээмэл тохиолддог зүйл юм. Үнэн хэрэгтээ энэ нь ажилчдын холбоо барих мэдээлэл, CD цуглуулга эсвэл танай компанийн түлшний хэрэглээний мэдээлэл зэрэг ямар ч зүйлийн жагсаалт байж болно.
Тиймээс бид Excel-ийг эхлүүлж, ийм жагсаалтыг ачаална ...
Excel дээр энэ жагсаалтыг нээсний дараа бид пивот хүснэгт үүсгэж эхлэх боломжтой.
Энэ жагсаалтаас дурын нүдийг сонгоно уу:
Дараа нь таб дээр Оруулах (Оруулах) командыг сонгоно уу Тэнхлэгийн хүснэгт (Тэнхлэгийн хүснэгт):
Харилцах цонх гарч ирнэ Пивот хүснэгт үүсгэх (пивот хүснэгт үүсгэх) танд хоёр асуулт байна:
- Шинэ пивот хүснэгт үүсгэхийн тулд ямар өгөгдлийг ашиглах вэ?
- Пивот хүснэгтийг хаана байрлуулах вэ?
Өмнөх алхамд бид аль хэдийн жагсаалтын нүднүүдийн аль нэгийг сонгосон тул бүх жагсаалт автоматаар сонгогдож, пивот хүснэгт үүсгэх болно. Бид өөр муж, өөр хүснэгт, тэр ч байтугай Access эсвэл MS-SQL өгөгдлийн сангийн хүснэгт гэх мэт зарим гадаад мэдээллийн эх сурвалжийг сонгох боломжтой гэдгийг анхаарна уу. Нэмж дурдахад бид шинэ пивот хүснэгтийг хаана байрлуулахаа сонгох хэрэгтэй: шинэ хуудсан дээр эсвэл одоо байгаа хүснэгтүүдийн аль нэг дээр. Энэ жишээнд бид сонголтыг сонгоно - Шинэ ажлын хуудас (шинэ хуудас руу):
Excel нь шинэ хуудас үүсгэж, дээр нь хоосон пивот хүснэгтийг байрлуулна:
Пивот хүснэгтийн аль ч нүдэн дээр дармагц өөр харилцах цонх гарч ирнэ. Пивот хүснэгтийн талбарын жагсаалт (пивот хүснэгтийн талбарууд).
Харилцах цонхны дээд талд байгаа талбаруудын жагсаалт нь эх жагсаалтын бүх гарчгийн жагсаалт юм. Дэлгэцийн доод талд байгаа дөрвөн хоосон хэсэг нь Пивот хүснэгтэд өгөгдлийг хэрхэн нэгтгэхийг хүсч байгаагаа хэлэх боломжийг олгоно. Эдгээр хэсгүүд хоосон байвал хүснэгтэд юу ч байхгүй. Бидний хийх ёстой зүйл бол гарчигуудыг дээд хэсгээс чирээд доорх хоосон хэсэг рүү чиглүүлэх. Үүний зэрэгцээ бидний зааврын дагуу пивот хүснэгт автоматаар үүсгэгддэг. Хэрэв бид алдаа гаргавал доод хэсгийн гарчгийг арилгах эсвэл бусдыг чирж солих боломжтой.
газар утгууд (Утга) дөрвийн хамгийн чухал нь байх. Энэ хэсэгт аль гарчиг байрлуулсан нь ямар өгөгдөл (нийлбэр, дундаж, хамгийн их, хамгийн бага гэх мэт) нэгтгэхийг тодорхойлдог. Эдгээр нь бараг үргэлж тоон утгууд юм. Энэ хэсэгт байрлах маш сайн нэр дэвшигч бол гарчгийн доорх өгөгдөл юм хэмжээ Манай анхны хүснэгтийн (зардал). Энэ гарчгийг хэсэг рүү чирнэ үү утгууд (утга):
Гарчиг гэдгийг анхаарна уу хэмжээ одоо тэмдэглэгээгээр тэмдэглэгдсэн байна, болон хэсэгт утгууд (Утга) оруулга гарч ирэв Дүнгийн нийлбэр (Дэмийн талбар Дүн), багана гэдгийг харуулж байна хэмжээ нэгтгэн дүгнэв.
Хэрэв бид пивот хүснэгтийг өөрөө харвал баганын бүх утгуудын нийлбэрийг харах болно хэмжээ анхны хүснэгт.
Тиймээс, бидний анхны пивот хүснэгт бий болсон! Тохиромжтой, гэхдээ тийм ч гайхалтай биш. Бид одоогийн байгаа мэдээллээс илүү мэдээлэл авахыг хүсч байгаа байх.
Анхны өгөгдөл рүү эргэж, энэ нийлбэрийг хуваахад ашиглаж болох нэг буюу хэд хэдэн баганыг тодорхойлохыг хичээцгээе. Жишээлбэл, бид борлуулалтын нийт дүнг худалдагч тус бүрээр нь тооцдог байдлаар пивот хүснэгтээ үүсгэж болно. Тэдгээр. Манай пивот хүснэгтэд компани дахь борлуулагч бүрийн нэр, нийт борлуулалтын дүн бүхий мөрүүд нэмэгдэх болно. Энэ үр дүнд хүрэхийн тулд гарчгийг чирэхэд л хангалттай худалдагч (борлуулалтын төлөөлөгч) бүс нутаг руу Мөрийн шошго (мөр):
Энэ нь илүү сонирхолтой болж байна! Манай Pivot Table хэлбэржиж эхэлж байна...
Ашиг тусыг нь харж байна уу? Хэдэн товшилтоор бид гараар бүтээхэд маш их цаг хугацаа шаардагдах хүснэгтийг үүсгэсэн.
Бид өөр юу хийж чадах вэ? Нэг ёсондоо бидний пивот ширээ бэлэн болсон байна. Бид анхны өгөгдлийн ашигтай хураангуйг үүсгэсэн. Чухал мэдээллийг аль хэдийн хүлээн авсан! Энэ нийтлэлийн үлдсэн хэсэгт бид илүү төвөгтэй Пивот Хүснэгтүүдийг үүсгэх зарим аргуудыг авч үзэхээс гадна тэдгээрийг хэрхэн тохируулах талаар сурах болно.
Пивот хүснэгтийн тохиргоо
Нэгдүгээрт, бид хоёр хэмжээст пивот хүснэгт үүсгэж болно. Үүнийг баганын гарчгийг ашиглан хийцгээе төлбөрийн арга (Төлбөрийн нөхцөл). Зүгээр л гарчгийг чирнэ үү төлбөрийн арга бүс рүү Баганын шошго (баганууд):
Бид үр дүнг авдаг:
Маш дажгүй харагдаж байна!
Одоо гурван хэмжээст хүснэгт хийцгээе. Ийм ширээ ямар байх вэ? Харцгаая…
Толгой хэсгийг чирэх Багц (цогцолбор) талбай руу тайлан шүүлтүүрүүд (Шүүлтүүр):
Түүнийг хаана байгааг анхаарна уу...
Энэ нь "Аль амралтын цогцолборт төлбөр төлсөн" гэсэн үндсэн дээр тайланг шүүж үзэх боломжийг бидэнд олгож байна. Жишээлбэл, бид бүх цогцолборын худалдагч, төлбөрийн аргуудын задаргаа эсвэл хулганы хоёр товшилтоор пивот хүснэгтийн харагдах байдлыг өөрчилж, зөвхөн цогцолбор захиалсан хүмүүст ижил задаргааг харуулах боломжтой. Нар сонирхогчид.
Тиймээс, хэрэв та үүнийг зөв ойлговол бидний пивот хүснэгтийг гурван хэмжээст гэж нэрлэж болно. Үргэлжлүүлэн тохируулцгаая...
Хэрэв пивот хүснэгтэд зөвхөн чек болон зээлийн картаар хийсэн төлбөрийг (өөрөөр хэлбэл бэлэн бус төлбөр) харуулах ёстой нь гэнэт гарч ирвэл бид гарчгийн дэлгэцийг унтрааж болно. Бэлэн мөнгө (Бэлэн мөнгө). Үүний тулд хажууд нь Баганын шошго доош сумыг товшоод доош унах цэсний хайрцгийн сонголтыг арилгана уу Бэлэн мөнгө:
Одоо бидний пивот хүснэгт ямар байхыг харцгаая. Таны харж байгаагаар багана Бэлэн мөнгө түүнээс алга болсон.
Excel дээр пивот хүснэгтүүдийг форматлах
Пивот Хүснэгтүүд нь мэдээжийн хэрэг маш хүчирхэг хэрэгсэл боловч үр дүн нь бага зэрэг энгийн бөгөөд уйтгартай харагдаж байна. Жишээлбэл, бидний нэмсэн тоо нь долларын дүн шиг харагдахгүй, зүгээр л тоо юм. Үүнийг засъя.
Ийм нөхцөлд дассан зүйлээ хийх нь сонирхол татахуйц бөгөөд зүгээр л хүснэгтийг бүхэлд нь (эсвэл хуудсыг бүхэлд нь) сонгоод, багаж самбар дээрх стандарт тоо форматлах товчлууруудыг ашиглан хүссэн форматаа тохируулна уу. Энэ аргын асуудал нь хэрэв та ирээдүйд пивот хүснэгтийн бүтцийг өөрчлөх юм бол (энэ нь 99% магадлалтай) формат алдагдах болно. Бидэнд хэрэгтэй зүйл бол үүнийг (бараг) байнгын болгох арга юм.
Эхлээд оруулгыг олъё Дүнгийн нийлбэр in утгууд (Утга) дээр дарна уу. Гарч ирэх цэснээс тухайн зүйлийг сонгоно уу Утгын талбарын тохиргоо (Утгын талбарын сонголтууд):
Харилцах цонх гарч ирнэ Утгын талбарын тохиргоо (Утгын талбарын сонголтууд).
хэвлэл мэдээллийн Тооны формат (Тооны формат), харилцах цонх нээгдэнэ. Нүднүүдийг форматлах (нүдний формат):
Жагсаалтаас Ангилал (Тооны формат) сонгоно уу Нягтлан бодох бүртгэл (Санхүүгийн) ба аравтын бутархайн тоог тэг болго. Одоо хэд хэдэн удаа дар OKбидний пивот хүснэгт рүү буцах.
Таны харж байгаагаар тоонууд нь долларын дүнгээр форматлагдсан байна.
Форматлахтай зэрэгцэн пивот хүснэгтийн форматыг бүхэлд нь тохируулцгаая. Үүнийг хийх хэд хэдэн арга байдаг. Бид илүү энгийн зүйлийг ашигладаг ...
дээр дарна уу Пивот хүснэгтийн хэрэгслүүд: Дизайн (Пивот хүснэгтүүдтэй ажиллах: Бүтээгч):
Дараа нь хэсгийн баруун доод буланд байгаа сум дээр дарж цэсийг өргөжүүлнэ үү Пивот хүснэгтийн хэв маяг (Pivot Table Styles) нь шугаман хэв маягийн өргөн цуглуулгыг үзэхийн тулд:
Ямар ч тохиромжтой хэв маягийг сонгоод пивот хүснэгтийнхээ үр дүнг харна уу:
Excel дээрх пивот хүснэгтийн бусад тохиргоо
Заримдаа та өгөгдлийг огноогоор шүүх хэрэгтэй болдог. Жишээлбэл, манай арилжааны жагсаалтад олон, олон огноо байдаг. Excel нь өгөгдлийг өдөр, сар, жил гэх мэтээр бүлэглэх хэрэгслийг өгдөг. Үүнийг хэрхэн хийснийг харцгаая.
Эхлээд оруулгыг устгана уу. төлбөрийн арга бүс нутгаас Баганын шошго (баганууд). Үүнийг хийхийн тулд гарчгийн жагсаалт руу буцааж чирээд оронд нь гарчгийг зөөнө үү Захиалсан огноо (захиалах огноо):
Таны харж байгаагаар энэ нь бидний пивот хүснэгтийг түр зуур ашиггүй болгосон. Excel нь арилжаа хийгдсэн огноо бүрт тусдаа багана үүсгэсэн. Үүний үр дүнд бид маш өргөн ширээтэй болсон!
Үүнийг засахын тулд дурын огноо дээр хулганы баруун товчийг дараад контекст цэснээс сонгоно уу GROUP (Бүлэг):
Бүлэглэх харилцах цонх гарч ирнэ. Бид сонгодог Сар (Сарууд) болон товшино уу OK:
Voila! Энэ хүснэгт нь илүү ашигтай:
Дашрамд хэлэхэд, энэ хүснэгт нь нийтлэлийн эхэнд үзүүлсэн борлуулалтын дүнг гараар эмхэтгэсэнтэй бараг ижил юм.
Таны мэдэх ёстой өөр нэг чухал зүйл бий! Та нэг биш, хэд хэдэн түвшний мөр (эсвэл багана) гарчгийг үүсгэж болно:
... мөн энэ нь иймэрхүү харагдах болно ...
Үүнтэй ижил зүйлийг баганын гарчиг (эсвэл бүр шүүлтүүр) хийж болно.
Хүснэгтийн анхны хэлбэр рүү буцаж, нийлбэрийн оронд дундажийг хэрхэн харуулахыг харцгаая.
Эхлэхийн тулд товшино уу Дүнгийн нийлбэр гарч ирэх цэснээс сонгоно уу Утгын талбарын тохиргоо (Утгын талбарын сонголтууд):
Жагсаалт Утгын талбарыг нэгтгэн дүгнэнэ үү (Үйл ажиллагаа) харилцах цонхонд Утгын талбарын тохиргоо (Утгын талбарын сонголтууд) сонгоно уу дундаж (дундаж):
Үүний зэрэгцээ бид энд байх хугацаандаа өөрчлөгдье Захиалгат нэр (Захиалгат нэр) хамт Дундаж дүн (Amount талбар Дүн) -ийг илүү богино болгох. Энэ талбарт иймэрхүү зүйлийг оруулна уу Дундаж:
Press OK тэгээд юу болохыг хараарай. Бүх утгууд нийлбэр дүнгээс дундаж болж, хүснэгтийн толгой хэсэг (зүүн дээд нүдэнд) болж өөрчлөгдсөнийг анхаарна уу. Дундаж:
Хэрэв та хүсвэл нэг пивот хүснэгтэд байршуулсан дүн, дундаж, тоо (борлуулалт) -ийг нэн даруй авах боломжтой.
Хоосон пивот хүснэгтээс эхлээд үүнийг хэрхэн хийх талаар алхам алхмаар зааварчилгаа энд байна.
- Толгой хэсгийг чирэх худалдагч (борлуулалтын төлөөлөгч) бүс нутаг руу Баганын шошго (баганууд).
- Гарчгийг гурван удаа чир хэмжээ (зардал) бүс рүү утгууд (Үнэ цэнэ).
- Эхний талбайн хувьд хэмжээ гарчгийг өөрчлөх Нийт (Тоо хэмжээ), энэ талбар дахь тооны формат нь байна Нягтлан бодох бүртгэл (Санхүүгийн). Аравтын бутархайн тоо тэг байна.
- Хоёр дахь талбар хэмжээ нэр Аверагe, үүнд зориулсан үйлдлийг тохируулна уу дундаж (Дундж) болон энэ талбар дахь тооны формат мөн өөрчлөгдөнө Нягтлан бодох бүртгэл (Санхүүгийн) аравтын бутархай тэгтэй.
- Гурав дахь талбарын хувьд хэмжээ гарчиг тавих Count мөн түүнд зориулсан мэс засал - Count (Тоо хэмжээ)
- Дахь Баганын шошго (Багана) талбар автоматаар үүсгэгдсэн Σ утгууд (Σ Утга) – тухайн хэсэг рүү чирнэ үү Мөрийн шошго (Мөр)
Энд бид юуг дуусгах болно:
Нийт дүн, дундаж үнэ цэнэ, борлуулалтын тоо - бүгд нэг пивот хүснэгтэд!
Дүгнэлт
Microsoft Excel-ийн пивот хүснэгтүүд нь олон функц, тохиргоог агуулдаг. Ийм жижиг нийтлэлд бүгдийг нь багтаахын тулд тэд бараг байдаггүй. Пивот хүснэгтийн бүх боломжуудыг бүрэн дүрслэхийн тулд жижиг ном эсвэл том вэбсайт хэрэгтэй болно. Зоригтой, сониуч уншигчид пивот хүснэгтүүдийг үргэлжлүүлэн судлах боломжтой. Үүнийг хийхийн тулд пивот хүснэгтийн бараг бүх элемент дээр хулганы баруун товчийг дараад ямар функц, тохиргоо нээгдэж байгааг хараарай. Тууз дээр та хоёр табыг олох болно: Пивот хүснэгтийн хэрэгслүүд: Сонголтууд (шинжилгээ) ба Зураг төсөл боловсруулах (Бүтээгч). Алдаа гаргахаас бүү ай, та үргэлж Pivot Table-г устгаад дахин эхлүүлэх боломжтой. Танд DOS болон Lotus 1-2-3 программыг удаан хугацаанд ашиглаж байгаагүй боломж байна.