Excel-ийн пивот хүснэгт нь өгөгдөлд дүн шинжилгээ хийх, үзүүлэхэд тохиромжтой хэрэгсэл юм. Гэхдээ өгөгдөл өөр өөр эх сурвалжид байвал яах вэ? Excel дээр хэд хэдэн хуудасны пивот хүснэгтийг хэрхэн хийхийг олж мэдье.
Хэдийгээр ERP болон BI системүүд байсан ч санхүүгийн захирал Excel-гүйгээр хийж чадахгүй. Бүх төрлийн тооцоо пивот хүснэгтүүд, тохиромжтой графикууд - Excel дээр та бараг бүх зүйлийг хийх боломжтой. Гэхдээ та үүнийг яаж хийхээ мэдэх хэрэгтэй.
Power Query ашиглан Excel-ийн пивот хүснэгт
Эхлээд Excel нь янз бүрийн хэмжээтэй эх хүснэгтүүдтэй ажиллах боломжтой гэдгийг тэмдэглэх нь зүйтэй. Гэхдээ эдгээр хүснэгтийн гарчиг, гарчиг нь ижил байх ёстой. Энэ нь програм ашигласан өгөгдлийг зөв тайлбарлахад зайлшгүй шаардлагатай. Үгүй бол алдаа гарч болзошгүй.
Мэдээллийг өөр өөр хуудаснаас авах шаардлагатай гэж бодъё. Хэрэв компани нь хэд хэдэн борлуулах цэг, агуулах, ижил бүлгийн барааны өөр өөр үйлчлүүлэгчидтэй бол ийм нөхцөл байдал үүсч болно. Энэ тохиолдолд тайланг өөр өөр салбараас гаргаж өгнө. Тэдгээрийг зохих ёсоор шинжлэхийн тулд бүхэл бүтэн компанид нийтлэг өгөгдлийн хүснэгтийг бий болгох нь зүйтэй бөгөөд үүний үндсэн дээр ирээдүйд пивот хүснэгт бий болно.
Энэ тохиолдолд та Excel дээр хоосон шинэ ажлын хуудас үүсгэх хэрэгтэй.
Энэ хуудсанд "Өгөгдөл" таб руу очно уу.
Дараа нь "Хүсэлт үүсгэх" гэснийг товшоод доош унах жагсаалтаас "Файлаас", дараа нь "Номоос" -ыг сонгоно уу. Жишээ ашиглан хэд хэдэн хуудасны пивот хүснэгтийг үүсгэцгээе - хоёр дэлгүүр тавиур дээрээ өөр өөр өнгийн хайрцаг байгаа тухай тайланг илгээсэн. Дэлгүүр бүрийн мэдээлэл нэг хуудсанд хадгалагддаг. Эдгээр хуудаснаас "Ном 1" үүссэн бөгөөд бид хамтран ажилладаг.
Гарч ирсэн цонхон дээр та програмын өгөгдлийг хаанаас авах ёстой номыг зааж өгөх хэрэгтэй бөгөөд "Импорт" товчийг дарна уу.
"Навигатор" нэртэй цонх гарч ирнэ. Үүний дотор та өгөгдлийг авах хуудсыг сонгох хэрэгтэй. Та ямар ч хуудсыг зааж өгч болно.
Хөтөлбөр нь заасан хуудаснаас авах ёстой урьдчилан харах цонхонд өгөгдлийг харуулах болно.
Бидний жишээн дээр заасан хуудас нь "цэг" өгөгдөлтэй олон нүдийг агуулж байгааг харж болно. Програм эдгээр нүднүүдийг мөн боловсруулдаг тул энэ нь үнэн биш юм. Боловсруулсан утгын талбайг багасгаж, тэг нүдийг арилгахын тулд та залруулах хэрэгтэй эх файл. Үүнийг хийхийн тулд анхны хүснэгт рүү очоод "Ctrl + End" товчийг дарна уу. Хүснэгтийн хамгийн сүүлийн нүд тодрох болно. Хүснэгтийн баруун болон доод талд байгаа бүх нүдийг устгах шаардлагатай бөгөөд "Ctrl + End" товчийг дарахад хүснэгтийн баруун доод нүд идэвхтэй болно.
Үүний дараа мэдээллийн эх сурвалж нь шаардлагагүй мэдээлэл агуулаагүй болно.
Та "Навигаци" ба "Өөрчлөгдсөн төрөл" гэсэн мөрүүдийг устгаж болно. Мөн "Эх сурвалж" хэсэгт өгөгдлийг засварлаж эхэлнэ. Үндсэн редакторын цонхонд заасан номын бүх хуудасны жагсаалтыг харуулах болно. Манай тохиолдолд "Хуудас1" ба "Хуудас2".
Дараа нь "Өгөгдөл" мөрөнд зурагт үзүүлсэн шиг хоёр сумтай дүрс дээр дарна уу.
Гарч ирэх цонхонд "Баганын эх нэрийг угтвар болгон ашиглах" гэсэн нүдийг сонго. Тэгээд "OK" дээр дарна уу. Бүх өгөгдлийг агуулсан хүснэгт гарч ирнэ.
Ширээг дахин барих болно. "Толгой"-той давхардсан мөрийг арилгаж болно. Үүнийг хийхийн тулд "Агуулах" баганын шүүлтүүр дэх "Агуулах" нүдийг сонгоод "ОК" дээр дарна уу. Дараа нь ижил шүүлтүүрт "Хоосон устгах" дээр дарна уу. Холбогдох мөрүүдийг арилгах болно.
Гарч ирэх "Татаж авах" цонхонд шилжүүлэгчийг "Зөвхөн холболт үүсгэх" байрлалд тавиад "Татаж авах" товчийг дарна уу. Асуулга гарч ирэх бөгөөд үүний үндсэн дээр пивот хүснэгтийг бүтээх болно.
Гарч ирэх цонхонд шилжүүлэгчийг "Гадаад мэдээллийн эх үүсвэрийг ашиглах" байрлалд тохируулаад "холболтыг сонгох" товчийг дарна уу.
Гарч ирэх цонхонд үүсгэсэн хүсэлтийн нэрийг сонгоод, манай тохиолдолд "хураангуй" -ыг сонгоод "Нээх" товчийг дарна уу.
Пивот хүснэгтийн дизайнер гарч ирнэ. Энгийн пивот хүснэгттэй адил өгөгдөл нэмж, зөөж болно.
2016 оноос өмнөх Excel-ийн хувилбаруудын пивот хүснэгт
IN Excel програмууд, 2003 он хүртэл нийтлэгдсэн, янз бүрийн эх сурвалжаас авсан пивот хүснэгтүүдийг "хэд хэдэн нэгтгэлийн мужид пивот үүсгэх" сонголтыг ашиглан үүсгэсэн. Гэсэн хэдий ч хүснэгтийн ийм бүтэц нь хүлээн авсан мэдээллийн бүх хэмжээг бүрэн дүн шинжилгээ хийх боломжийг олгодоггүй. Ийм байдлаар бүтээгдсэн хүснэгтийг зөвхөн хамгийн энгийн өгөгдөлд дүн шинжилгээ хийхэд ашиглаж болно.
2007 оноос хойшхи хувилбаруудад шинэ өгөгдлийн хүрээг харилцах цонхоор нэг нэгээр нь зааж өгсөн тусгай "Pivot Table Wizard" нэмэх шаардлагатай болсон. Үүссэн пивот хүснэгтүүд нь нэлээд хязгаарлагдмал функцтэй байсан.
Excel пивот хүснэгт гэж юу вэ? Санаачлаагүй хүн хамгийн ойлгомжтой гэж таамаглах байх - эдгээр нь хэд хэдэн өөр өөр эх сурвалжаас мэдээлэл цуглуулсан (дүгнэлт) хүснэгтүүд бөгөөд зарим асуултад нийцдэг. Үнэндээ энэ дүгнэлт нь бүрэн зөв биш юм.
Excel Pivot Tables нь өгөгдсөн алгоритмын дагуу ихэвчлэн дараах мэдээллийг цуглуулдаг хэрэгсэл юм. нэг мэдээллийн сангийн хүснэгт! (Олон тооны нэгтгэх мужийг эх сурвалж болгон ашиглах боломж байгаа боловч энэ нийтлэлд хэлэлцэхгүй.)
Өгөгдлийн сангийн эмх замбараагүй эгнээний пивот хүснэгтүүдийн тусламжтайгаар та үүнийг хийх боломжтой секундын доторшаардлагатай мэдээллийг задлах, тэдгээрээс шаардлагагүй мэдээллийг таслах, дутуу мэдээллийг нэмж, хэрэглэгчдэд үзэсгэлэнтэй, уншихад хялбар хэлбэрээр танилцуулах.
Хэрэв та MS Excel дээр их хэмжээний мэдээлэлтэй байнга ажилладаг бөгөөд пивот хүснэгтийн талаар юу ч мэдэхгүй бол жинхэнэ зорилгыг нь мэдэхгүй тооны машин ашиглан "хадаас цохиж" байна гэж бодоорой!
Пивот хүснэгтийг хэзээ ашиглах ёстой вэ?
Нэгдүгээрт, та маш их хэмжээний статистик мэдээлэлтэй ажиллахдаа ангилах, шүүлтүүрийн тусламжтайгаар дүн шинжилгээ хийхэд маш их хөдөлмөр шаарддаг.
Хоёрдугаарт, эх сурвалжийн өөрчлөлтөөс шалтгаалан тодорхой интервалаар тайланг давтах (дахин тооцоолох) шаардлагатай бол мэдээллийн сангийн хүснэгт.
Гуравдугаарт, янз бүрийн хугацааны эсвэл бусад шалгуурын хүрээнд мэдээллийн сангийн өгөгдөлд гарсан өөрчлөлтийг шинжлэхэд.
Эдгээр нь Excel PivotTable хэрэгслийн жинхэнэ ид шидийн хүчийг илчлэх гол нөхцөл байдал юм.
Пивот хүснэгт үүсгэх, тэдэнтэй ажиллах нь эх сурвалжийн агуулга - мэдээллийн санд нөлөөлөхгүй! Пивот хүснэгтүүд excelөгөгдлийн санд юу ч өөрчлөхгүйгээр энэ өгөгдлийг өөр өөр өнцгөөс харах боломжийг танд олгоно !!!
Энэ бол тойм цувралын эцсийн бөгөөд хамгийн чухал нийтлэл юм мэдээлэл хадгалах, том хүснэгтэн мэдээллийн хэмжээг удирдах.
Циклийн өмнөх нийтлэлүүдэд бид бүтээж, сурч, хэрэглэж, оруулав.
Пивот хүснэгтийн загвар үүсгэх.
Бид ижил ажлын дэвтэрт пивот хүснэгт үүсгэх болноexcel, мэдээллийн эх сурвалж хаана байрладаг - мэдээллийн сангийн хүснэгт, гэхдээ өөр хуудсан дээр.
Бид энэ мөчлөгийн бүх зүйлд хамтран ажилласан "2-р хэсгийн металл хийцийг үйлдвэрлэх" боловсролын мини DB2 суурьтай үргэлжлүүлэн ажиллаж байна.
1. Файлыг MS Excel дээр нээнэ үү.
2. Бид үндсэн хүснэгтийн аль ч нүдийг идэвхжүүлдэг ("хулганаар дар").
3. Бид програмын үндсэн цэсний "Өгөгдөл" - "Пивот хүснэгт ..." командыг гүйцэтгэдэг. Энэ тушаал нь PivotTable Wizard мини үйлчилгээг эхлүүлнэ.
4. "Ид шидтэн ..."-ийн унждаг цонхон дээрх унтраалгауудын байрлалыг сонгох сонголтуудын талаар удаан хугацааны турш бодохгүйгээр бид доорх дэлгэцийн агшинд үзүүлсэн шиг тэдгээрийг тохируулна (илүү нарийвчлалтай, тэдэнд хүрч болохгүй). "Дараах" товчийг ашиглан цонхны хооронд шилжих.
Хоёрдахь алхамд, хэрэв та мэдээллийн баазыг зөв бэлтгэж, нийтлэлийн энэ хэсгийн 2-р алхамыг гүйцэтгэсэн бол "Ид шидтэн ..." мужийг өөрөө сонгох болно.
5.
"Шидтэний ..." гурав дахь алхам дээр "Дуусгах" товчийг дарна уу. Пивот хүснэгтийн загварыг үүсгэж, мэдээллийн сан байрладаг ижил файлын шинэ хуудсан дээр байрлуулна!
Одооноос бусад нь хоосон загваршинээр үүсгэсэн хуудсан дээр Excel файлПивот хүснэгтийн аль нэг нүд идэвхжсэн үед гарч ирэх "пивот хүснэгтүүд" самбар болон "пивот хүснэгтийн талбаруудын жагсаалт" цонхыг харах болно. Тэд автоматаар гарч ирдэг, би зүгээр л загварын хажууд байрлуулж, хэмжээг нь тохируулсан. Хэрэв та "пивот хүснэгтүүд" самбарыг харахгүй байгаа бол үндсэн цэсээр "Харах" - "Хэрэгслийн самбар" - "пивот хүснэгт" командыг ашиглан залгана уу.
MS Excel 2007 болон түүнээс хойшхи хувилбаруудад хэрэглэгчдийн 99% нь санал болгож буй шилжүүлэгчийн тохиргоог хэзээ ч өөрчилдөггүй бөгөөд эдгээр гурван алхмыг давж, санал болгож буй сонголтуудыг хүлээн зөвшөөрч (бид үүнийг бас хийсэн) тул "Wizard ..."-ийг хүчингүй болгосон.
MS Excel 2007 дээр "Pivot Table" командыг ашиглан "Create Pivot Table" харилцах цонх гарч ирэх бөгөөд үүнд дүн шинжилгээ хийх мэдээллийн эх сурвалж болон үүсгэх пивот хүснэгтийн байршлыг зааж өгөхөд хангалттай.
Excel-ийн ажлын пивот хүснэгтүүдийг үүсгэх.
Үүсгэсэн загварт дөрвөн бүс байдаг бөгөөд тэдгээрт пивот хүснэгт үүсгэхийн тулд та мэдээллийн сангийн хүснэгтийн талбар-багануудын нэрийг "Пивот хүснэгтийн талбаруудын жагсаалт" цонхноос хулганы зүүн товчлуураар чирж байрлуулна. Өгөгдлийн санд талбар нь нэртэй багана гэдгийг би танд сануулж байна.
Анхаар!!!
"Пивот хүснэгтийн талбаруудын жагсаалт" цонхны элементүүд нь мэдээллийн сангийн талбаруудын гарчиг юм!
1. Хэрэв та "пивот хүснэгтийн талбарын жагсаалт"-ын аль нэгийг хамгийн их байрлуулбал дээд бүсЭнэ нь хаана болох загвар "Хуудасны талбар", дараа нь бид тохиромжтой зүйлийг авах болно энэ талбарын бичлэгүүдийг шүүнэ үү.
2. Хэрэв та пивот хүснэгтийн талбарын жагсаалтын нэг элементийг (эсвэл хоёр, гурваас дээш) загварын зүүн хэсэг рүү чирвэл энэ нь болох газар болно. "Утаснуудын талбар" мөрийн толгойтэнхлэгийн хүснэгт.
3. Хэрэв та "Жагсаалтын ..."-ийн нэг элементийг (эсвэл хоёр, бага давтамжтай - гурав ба түүнээс дээш) загварын дээд бүс рүү чирвэл энэ нь харагдах болно. "баганын талбар", дараа нь бид мэдээллийн сангийн хүснэгтийн энэ талбарын бүртгэлээс авах болно баганын гарчигтэнхлэгийн хүснэгт.
4. Хэрэв та "Жагсаалтын ..."-ийн нэг элементийг (эсвэл хоёр, бага давтамжтай - гурав ба түүнээс дээш) загварын төв хэсэгт чирвэл энэ нь болно. "өгөгдлийн элемент", дараа нь бид энэ мэдээллийн сангийн талбарын бүртгэлээс авах болно үнэт зүйлстэнхлэгийн хүснэгт.
Утга - өгөгдлийн элементүүд - хоёр хэмжээст хүснэгтийн дүрмийн дагуу, өөрөөр хэлбэл харгалзах мөрийн толгой ба баганын толгойн уулзвар дээр байрлана!
Маш тодорхой биш байна уу? Практик жишээнүүд рүү шилжье - бүх зүйл тодорхой болно!
Даалгаврын дугаар 9:
Захиалга бүрт хэдэн тонн металл хийц үйлдвэрлэсэн бэ?
Энэ асуултад хариулахын тулд хоёр зүйлийг хийцгээе! Эдгээр үйлдлүүдийн схемийг цэнхэр сумаар өмнөх зурагт үзүүлэв.
1. "Захиалга" элементийг "Пивот хүснэгтийн талбаруудын жагсаалт" цонхноос хоосон загварын "Мөрийн талбарууд" хэсэгт чирнэ үү.
2.
"Нийт масс, t" элементийг "Пивот хүснэгтийн талбаруудын жагсаалт" цонхноос загварын "Өгөгдлийн элементүүд" бүс рүү чирнэ үү. Үр дүн нь зүүн талд байгаа дэлгэцийн агшинд байна. Ямар ч тайлбар хэрэггүй гэж бодож байна.
"Өгөгдлийн элементүүд"-ийн гарчиг "Талбар дээрх нийлбэр Нийт масс, t" байрлана илүү өндөр"Мөрийн талбарууд" гэсэн гарчиг ба "Баганын талбарууд" гарчгийг байрлуулж болох газрын зүүн талд. Үүнд анхаарлаа хандуулаарай!
Даалгаврын дугаар 10:
Захиалга тус бүрээр огноогоор хэдэн тонн төмөр хийц үйлдвэрлэсэн бэ?
Бид үргэлжлүүлэн ажиллаж байна.
3. 10-р даалгаврын асуултанд хариулахын тулд "Огноо" элементийг "пивот хүснэгтийн талбаруудын жагсаалт" цонхноос загварын "баганын талбарууд" хэсэгт нэмэхэд хангалттай.
4.
Бичлэгүүдийг жишээлбэл сараар нь бүлэглэж болно. Үүнийг хийхийн тулд "пивот хүснэгтүүд" самбар дээр "пивот хүснэгт" таб дээр товшоод "Бүлэг ба бүтэц" - "Бүлэг ..." -ийг сонгоно уу. Бүлэглэх цонхонд зүүн талд байгаа дэлгэцийн зургийн дагуу тохиргоог хийнэ үү.
Манай мэдээллийн сан дахь бүх бүртгэлийг 4-р сард хийсэн тул бүлэглэсний дараа бид зөвхөн "4-р сар" ба "Их нийт" гэсэн хоёр баганыг харж байна. 5, 6-р сарын бүртгэлүүд эх мэдээллийн санд гарч ирэхэд харгалзах хоёр баганыг пивот хүснэгтэд нэмнэ. 
Даалгаврын дугаар 11:
3 тоот тушаалаар нийт хэдэн тонн, хэдэн ширхэг марк үйлдвэрлэсэн бэ?
5. Эхлэхийн тулд "Захиалга" талбарыг "Пивот хүснэгтийн талбаруудын жагсаалт" цонхноос эсвэл пивот хүснэгтээс шууд хуудасны дээд талд, загварын "Хуудасны талбарууд" хэсэгт чирнэ үү.
7. "Тоо хэмжээ, ширхэг" талбарыг "Өгөгдлийн элементүүд" талбарт нэмнэ үү.
Даалгаврын асуултанд хариулахад шаардлагатай Excel пивот хүснэгтийн тохиргоог бүрдүүлсэн.
8. В1 нүд болон доош унадаг жагсаалтаас "(Бүгд)" гэсэн бичвэрийн оронд бидний сонирхож буй 3 дугаар тушаалд тохирох "3"-ыг сонгоод "" товчийг дарж жагсаалтыг хаа. OK” товч.
Даалгаврын асуултын хариулт нь энэ текстийн доорх дэлгэцийн агшинд байна.
Пивот хүснэгтийн формат.
Хэрэв пивот хүснэгтийг хэвлэж, хэн нэгэнд үзүүлэх шаардлагатай бол, эсвэл зүгээр л өөрийгөө бага зэрэг баярлуулахын тулд форматлах хэрэгтэй - зохих дүр төрхийг өгч, зарим "хангалтгүй" гарчгийг дахин томъёолох хэрэгтэй.
"Талбарын нийлбэр Нийт жин, t" ба "Талбарын нийлбэр тоо хэмжээ, ширхэг" гэсэн гарчиг нь орос хэл дээр ямар нэгэн байдлаар ойлгомжтой, ойлгомжтой сонсогддог. Тэдний нэрийг "Бүтээгдэхүүний нийт масс, т" ба "Бүтээгдэхүүний тоо, ширхэг" гэж нэрлэе.
1. Үүсгэсэн хүснэгтээ үргэлжлүүлье. В5 нүдийг хулганаар идэвхжүүлнэ үү.
2. Пивот хүснэгтийн хэрэгслийн самбар дээрх Талбарын сонголтууд товчийг дарна уу (доорх зурган дээр баруун талд тодруулсан).
3. "Пивот хүснэгтийн талбарыг тооцоолох" унадаг цонхонд нэрийг өөрчилж, "OK" товчийг дарна уу.
4.
Үүнтэй төстэй алгоритмын дагуу бид эхлээд хулганыг B6 нүдэнд "оруулсны" дараа хоёр дахь талбарын толгойн нэрийг өөрчилдөг.
5. "Pivot Table" самбар дээрх "Тайлангийн формат" товчийг дарна уу (дээрх зурган дээр зүүн талд тодруулсан - 3-р зүйлийн дээрх).
6. Гарч ирэх "Автоматаар форматлах" цонхонд санал болгож буй форматын сонголтуудаас, жишээлбэл, "Тайлбар 6" -ыг сонгоод "OK" дээр дарна уу.
Гадаад төрхбидний пивот хүснэгт ихээхэн өөрчлөгдсөн.
Дүгнэлт.
Би таны анхаарлыг хэд хэдэн чухал зүйлд хандуулж байна!
Эх сурвалжийг өөрчлөх үед (жишээлбэл, мэдээллийн санд өөр бичлэг нэмэх) пивот хүснэгтэд автоматаар өөрчлөлт гарахгүй !!!
Өгөгдлийн сангийн өөрчлөлтийг пивот хүснэгтэд тусгахын тулд "Өгөгдлийг шинэчлэх" товчийг "гараар" дарах шаардлагатай (товчны дүрс нь " ! ) Пивот Хүснэгтийн самбар дээр!!!
агуулсан "Хуудасны зах" талбарт байрлуулна текст мэдээлэлболж болох юм гарчиг үүсгэсэн пивот хүснэгтийн хувьд.
Мөрийн талбарууд болон баганын талбарууд хэсэгт агуулсан талбаруудыг байрлуул текст мэдээлэлболж болох юм гарчиг мөр, баганын хувьдпивот хүснэгт үүсгэсэн.
"Өгөгдлийн элементүүд" бүсэд голчлон байрлуулна тоон мэдээлэл.
Талбаруудыг цаасан дээр хийдэг шигээ байрлуул! Алдаа гаргахаас бүү ай. Бүх зүйл амархан засагддаг.
Энэ нийтлэлийн өмнөх хэсгүүдийн аль нэгэнд хулганы хоёр буюу гурван хөдөлгөөнөөр үүсгэсэн пивот хүснэгтүүд нь маш хэцүү асуултуудад маш хурдан хариулсан! Хэрэв захиалга хэдэн сарын дотор хийгдсэн бөгөөд брэндийн нэрийн тоо хэдэн мянгаас давсан бол дээр дурдсан асуудлуудыг шийдэхэд хэр хугацаа шаардагдах вэ? Цаг? Өдөр? Excel пивот хүснэгтүүд үүнийг шууд, дахин дахин, алдаагүй хийдэг!!!
Excel PivotTables-ийн талаар олон сайн зузаан, нимгэн ном бичсэн. Юуны өмнө эдгээр нь Билл Желен (“Ноён Эксел”) болон Майкл Александр нарын номууд юм. Энэ сэдвийг сонирхож буй хүмүүст би уншихыг зөвлөж байна.
Мэдээжийн хэрэг, энэ гайхамшигтай хэрэгслийн талаар богино өгүүллээр дэлгэрэнгүй ярих нь маш хэцүү, бараг боломжгүй юм. Нэг талаас би илүү практик чухал талуудыг хөндөхийг хүссэн ч нөгөө талаас танилцуулах нийтлэлдээ мэдээллийн хэмжээ, нарийн төвөгтэй байдлаас болж хэрэглэгчдийг айлгаж, "ширэнгэн ой руу орохыг" хүсээгүй. Хэрэв MS Excel-ийн энэхүү "шидэт хэрэгсэл" нь блог уншигчдын сонирхлыг татаж байгаа бол ирээдүйд энэ сэдвийг чухал, үнэ цэнэтэй практик зөвлөмж бүхий жижиг товхимол гаргах замаар үргэлжлүүлэх болно.
Excel пивот хүснэгтийг ашиглахаа мартуузай! Мэргэжилтний хувьд таны үнэ цэнэ хэд дахин нэмэгдэх болно!
Энэ сэдвийн ерөнхий тойм болох зургаан нийтлэлийн цуврал дээр Excel дээр их хэмжээний мэдээллийг боловсруулахдууссан.
би гуйя хүндэтгэж байна зохиолчийн бүтээл бүртгүүлэх нийтлэлийн зарын хувьд нийтлэл бүрийн төгсгөлд байрлах цонх эсвэл хуудасны дээд талд байрлах цонхонд!
Эрхэм уншигчид, хуудасны доод хэсэгт асуулт, санал хүсэлт, сэтгэгдлээ үлдээгээрэй.
Пивот хүснэгтийн мэдээллийн эх сурвалж нь өгөгдлийн жагсаалт бөгөөд багана бүр нь пивот хүснэгтийн талбар болж ажилладаг. Хэрэв танд зөвхөн пивот хүснэгттэй төстэй хүснэгт ирсэн бол яах вэ (энэ нь форматлагдсан бөгөөд үүнтэй төстэй боловч пивот хүснэгттэй ажиллах хэрэгслийг ашиглах боломжгүй). Мөн та үүнийг өгөгдлийн жагсаалт болгон хувиргах хэрэгтэй, i.e. гүйцэтгэх урвуу ажиллагаа. Энэ нийтлэлээс та хоёр хувьсагчтай пивот хүснэгтийг өгөгдлийн жагсаалт руу хэрхэн хөрвүүлэх талаар сурах болно.
Зураг нь миний тодорхойлсон зарчмыг харуулж байна. Тэдгээр. A2:E5 мужид өгөгдлийн жагсаалт руу хөрвүүлсэн анхны пивот хүснэгт байна (H2:J14 муж). Хоёрдахь хүснэгт нь өөр өнцгөөс харахад ижил өгөгдлийн багцыг төлөөлдөг. Анхны пивот хүснэгтийн утга бүр нь мөрийн талбар, баганын талбар, тэдгээрийн харгалзах утгаас бүрдэх мөр шиг харагдана. Өгөгдлийг өөр аргаар эрэмбэлэх, удирдах шаардлагатай үед энэ өгөгдлийг харуулах хэрэгтэй.
Ийм жагсаалтыг гаргах боломжийг ойлгохын тулд бид пивот хүснэгтийн хэрэгслийг ашиглах болно. Товчлуур нэмье Пивот хүснэгтийн шидтэнтуузан дээрх бидэнд байхгүй, гэхдээ Excel-ийн өмнөх хувилбаруудын үлдэгдэл болон үлдсэн хурдан нэвтрэх самбар руу.
Таб руу шилжих Файл -> Сонголтууд. Гарч ирэх харилцах цонхонд Сонголтуудexcel,таб Самбар хурдан нэвтрэх зүүн талбараас тухайн зүйлийг ол Пивот хүснэгт ба пивот диаграмын шидтэнбаруун талд нь нэмнэ үү. OK дарна уу.
Та одоо Түргэн нэвтрэх хэрэгслийн самбар дээр шинэ дүрстэй боллоо.
Энэ таб дээр дарж ажиллуулна уу Пивот хүснэгтийн шидтэн.
Шидтэний эхний алхам бол пивот хүснэгтийн өгөгдлийн эх үүсвэрийн төрлийг сонгох явдал юм. Шилжүүлэгчийг суулгаж байна Олон тооны нэгтгэх мужидболон товшино уу Цаашид.
2a алхамд хуудасны талбаруудыг хэрхэн үүсгэхийг зааж өгнө үү. Шилжүүлэгчийг байрлуул Хуудасны талбар үүсгэх -> Дараа нь.
2b алхам дээр, талбайд Хүрээөгөгдөл агуулсан мужийг сонгоод товшино уу Нэмэх.Манай тохиолдолд энэ нь A1:E4 анхны пивот хүснэгтийн байршил байх болно.
Гурав дахь алхамд та пивот хүснэгтийг хаана байрлуулахаа шийдэж, товчлуур дээр дарах хэрэгтэй Бэлэн.
Excel нь өгөгдөлтэй пивот хүснэгт үүсгэх болно. Дэлгэцийн зүүн талд та хэсэг харагдах болно Пивот хүснэгтийн талбаруудын жагсаалт.Мөр, баганын талбараас бүх зүйлийг устгана уу. Би өмнөх нийтлэлдээ энэ талаар илүү ихийг бичсэн.
Та анхны хүснэгтийн бүх утгуудын нийлбэрийг агуулсан нэг нүднээс бүрдэх жижиг пивот хүснэгттэй болно.
Энэ нүдэн дээр давхар товшино уу. Excel нь утгуудын жагсаалт бүхий хүснэгтийг агуулсан шинэ хуудас үүсгэх болно.
Энэ хүснэгтийн гарчиг нь ерөнхий мэдээлэлТа тэдгээрийг илүү мэдээлэлтэй болгохыг хүсч байгаа байх.
Пивот хүснэгтийг их хэмжээний өгөгдлийг хурдан шинжлэхэд ашигладаг. Энэ нь янз бүрийн хүснэгт, хуудасны мэдээллийг нэгтгэх, ерөнхий үр дүнг тооцоолох боломжийг олгодог. Энэхүү бүх нийтийн аналитик хэрэгсэл нь Excel програмын боломжийг ихээхэн өргөжүүлдэг.
Та мөр, баганыг солих замаар анхны параметрүүд дээр үндэслэн шинэ нийлбэрүүдийг үүсгэж болно. Та өөр өөр элементүүдийг харуулах замаар өгөгдлийг шүүж болно. Мөн талбайг нүдээр харуулах.
Excel дээрх пивот хүснэгт
Жишээлбэл, бид янз бүрийн худалдааны салбар дахь барааны борлуулалтын хүснэгтийг ашигладаг.
Тавагнаас аль тасагт, юу, хэзээ, ямар үнээр зарагдсаныг харж болно. Хэлтэс тус бүрийн борлуулалтын хэмжээг олохын тулд та тооцоолуур дээр гараар тооцоолох хэрэгтэй болно. Эсвэл нийлбэр дүнг харуулахын тулд томьёог ашиглан өөр Excel хүснэгт хийж болно. Ийм байдлаар мэдээлэлд дүн шинжилгээ хийх нь үр дүнгүй юм. Алдаа гаргах хангалттай хугацаа биш.
Хамгийн оновчтой шийдэл бол Excel дээр пивот хүснэгт үүсгэх явдал юм.
Энгийн, хурдан, өндөр чанартай.
Чухал нюансууд:
- Өгөгдлийн цуглуулгад заасан мужын эхний мөрийг бөглөх ёстой.
- Үндсэн хавтан дээр багана бүр өөрийн гэсэн гарчигтай байх ёстой - хураангуй тайланг тохируулах нь илүү хялбар байдаг.
- Excel дээр та мэдээллийн эх сурвалж болгон ашиглаж болно Хүснэгтүүдэд хандах, SQL сервергэх мэт.
Олон хүснэгтээс пивот хүснэгтийг хэрхэн хийх вэ
Ихэнхдээ та олон хүснэгтээс хураангуй тайлан үүсгэхийг хүсдэг. Мэдээллийн хэд хэдэн самбар байна. Бид тэдгээрийг нэг нийтлэг зүйл болгон нэгтгэх хэрэгтэй. Шинжлэх ухааны хувьд хоёр дэлгүүрийн агуулахын үлдэгдлийг гаргаж ирье.
Олон хуудаснаас пивот хүснэгт үүсгэх журам ижил байна.
Pivot Table Wizard ашиглан тайлан үүсгэцгээе:
Таны харж байгаагаар хэдхэн товшилтоор та янз бүрийн мэдээлэл бүхий хэд хэдэн хуудас эсвэл хүснэгтээс нарийн төвөгтэй тайланг үүсгэж болно.
Excel дээр пивот хүснэгттэй хэрхэн ажиллах талаар
Хамгийн энгийнээс эхэлцгээе: багана нэмэх, хасах. Жишээлбэл, өөр өөр хэлтэсүүдийн борлуулалтын хураангуй хүснэгтийг авч үзье (дээрээс харна уу).
Пивот хүснэгтийн баруун талд бид талбарын жагсаалтаас багануудыг сонгосон ажлын талбартай байсан. Хэрэв алга болсон бол таблет дээр дарахад л хангалттай.
Тайлангийн пивот хүснэгтэд өөр талбар нэмье.Үүнийг хийхийн тулд "Огноо" (эсвэл "Бүтээгдэхүүний" эсрэг талын) нүдийг чагтална уу. Тайлан нэн даруй өөрчлөгддөг - хэлтэс бүрт борлуулалтын динамик өдөр бүр гарч ирдэг.
Тайлан дахь өгөгдлийг бүлэглэхсараар. Үүнийг хийхийн тулд товшино уу баруун товшино уу"Огноо" талбар дээр хулганыг дарна уу. "Бүлэг" дээр дарна уу. "Сараар" сонгоно уу. Үүний үр дүнд дараах пивот хүснэгт гарч ирнэ:
руу пивот хүснэгтийн сонголтыг өөрчлөх, одоо байгаа мөрийн талбаруудын хажууд байгаа нүднүүдийн сонголтыг арилгаж, бусад талбарт тохируулахад хангалттай. Хэлтэсээр биш бүтээгдэхүүний нэрээр тайлан гаргая.
Хэрэв бид "огноо" -г хасаад "хэлтэс" -ийг нэмбэл юу болох вэ:
Гэхдээ хэрэв ийм тайлан гаргаж болно талбаруудыг өөр өөр талбайн хооронд чирэх:
Мөрийн гарчгийг баганын гарчиг болгон өөрчлөхийн тулд энэ гарчгийг сонгоод гарч ирэх цэс дээр дарна уу. "Багананы гарчиг руу шилжих" дээр дарна уу. Ийм байдлаар бид огноог баганууд руу шилжүүлсэн.
Бид "Хэлтэс" талбарыг барааны нэрсийн өмнө тавьдаг. "Эхлэл рүү шилжих" цэсийг ашиглан.
Тодорхой бүтээгдэхүүний дэлгэрэнгүй мэдээллийг харуулах. Агуулах дахь үлдэгдлийг харуулсан хоёр дахь пивот хүснэгтийн жишээн дээр. Нүд сонгоно уу. Хулганы баруун товчийг дарна уу - "өргөтгөх".
Нээгдсэн цэснээс харуулахыг хүссэн өгөгдөл бүхий талбарыг сонгоно уу.
Пивот хүснэгт дээр дарахад тайлангийн сонголт бүхий таб гарч ирнэ. Үүний тусламжтайгаар та гарчиг, мэдээллийн эх сурвалж, бүлгийн мэдээллийг өөрчлөх боломжтой.
Гаргасан нийтийн үйлчилгээний төлбөрийн үнэн зөвийг шалгах
Excel-ийн пивот хүснэгтүүдийн тусламжтайгаар үйлчилгээний байгууллагууд түрээсийн төлбөрийг хэрхэн зөв төлж байгааг шалгахад хялбар байдаг. Өөр нэг эерэг зүйл бол хэмнэлт юм. Хэрэв бид сар бүр хэр хэмжээний цахилгаан, хий зарцуулж байгааг хянаж чадвал орон сууцны төлбөрөө хэмнэх нөөцийг олж чадна.
Эхлэхийн тулд бид бүх нийтийн үйлчилгээний төлбөрийн тарифын хураангуй хүснэгтийг гаргахыг санал болгож байна. Өөр өөр хотуудын хувьд өгөгдөл өөр өөр байх болно.
Жишээлбэл, бид Москвагийн тарифын хураангуй хүснэгтийг гаргасан.
Боловсролын зорилгоор 60 м.кв талбайтай орон сууцанд амьдардаг 4 хүнтэй гэр бүлийг авч үзье. м.Төхөөрөмжийн төлбөрийг хянахын тулд та сар бүрийн тооцооны хүснэгтүүдийг үүсгэх хэрэгтэй.
Эхний багана = пивот хүснэгтийн эхний багана. Хоёр дахь нь харагдах байдлыг тооцоолох томъёо юм.
Тариф * хүний тоо / тоолуурын заалт / талбай
Манай томьёо нь хувь хэмжээ бүхий хураангуй хүснэгт байрладаг хүснэгтэд хамаарна.
Хэрэглээний төлбөрийг тооцохдоо тэтгэмжийг ашигласан бол тэдгээрийг томъёонд оруулж болно. Үйлчилгээний байгууллагынхаа нягтлан бодох бүртгэлийн хэлтэст хуримтлалын талаарх мэдээллийг хүсэх. Тариф өөрчлөгдөхөд нүднүүдийн өгөгдлийг өөрчлөхөд л хангалттай.
Пивот хүснэгт нь компанийн мэдээлэлд дүн шинжилгээ хийхэд туслах хэрэгсэл юм. Та өөр өөр текст засварлагч дээр ийм хүснэгт үүсгэж болно.
Үүсгэхийн тулд та үүсгэсэн тайлангаас мөр, баганын гарчиг сонгох хэрэгтэй. Excel дээр тайлан гаргах хамгийн тохиромжтой арга юм. Та шүүлтүүрийг бүхэлд нь тайлан эсвэл түүний тодорхой хэсэгт хэрэглэж болно.
excel дээр пивот хүснэгтийг хэрхэн хийх
Эхлээд 2007 оны хувилбарыг харцгаая. Зарим хэрэглэгчид засварлагчийн хуучин хувилбартай хэвээр байна. Эхлэхийн тулд танд хэрэгтэй хүснэгт үүсгэхтайлан бүтээх. Энэ нь хоосон нүд, мөр байх ёсгүй. Хамгийн энгийн жишээг ашиглан тайлан гаргах үйл явцыг шинжье.
- Excel 2007 програмыг эхлүүлж байна. Дээд талын самбар дээрх "Оруулах" табыг сонгоно уу.
- "Санал болгож буй пивот хүснэгтүүдийг үүсгэх" -ийг сонгоно уу.
- Хүрээний сонголтуудыг харуулсан цонх гарч ирнэ. Та зохион байгуулалт дээр дарж тайлангаа хэрхэн харагдахыг урьдчилан харах боломжтой. Үүний дараа та голыг нь сонгож болно. OK товчийг дарснаар тайлан хуудсан дээр гарч ирнэ. Үүнээс гадна Excel өөрөө мөрийн дарааллыг тодорхойлно.
- Та програмын утгыг шүүж, эрэмбэлэх боломжтой. Үүний тулд самбар дээр тусгай дүрс байдаг.
Энэ бол пивот хүснэгтийг хэрхэн хийх хамгийн энгийн жишээ юм хуучин хувилбар excel.
Excel 2010 болон 2013 оны пивот хүснэгтүүд нь excel дээр ажиллах жишээ юм
Excel 2010, 2013 дээр тайлан гаргах нь бараг адилхан. Эдгээр хувилбаруудын үйл явц нь 2007 оны хувилбараас арай өөр юм. Гэхдээ, зарим мөчүүдижил төстэй: та эхлэхээсээ өмнө шаардлагатай бүх өгөгдлийг зааж өгөх энгийн хүснэгт үүсгэх хэрэгтэй. Дээд мөрөнд баганын нэрийг агуулсан байх ёстой.
Одоо та Excel дээр тайлан үүсгэж болно:
Хуудсаар юу хийж болох вэ:
- Та утгуудыг удирдах хэрэгслийн самбарт хандах боломжтой болно. Бүх талбарууд болон тэдгээрийн гарчгийг "Тайланд нэмэх талбаруудыг сонгох" таб дээр жагсаах болно. Шаардлагатай зүйлсийг нэмэхийн тулд шаардлагатай талбаруудыг сонгох ёстой бөгөөд жагсаалт үүснэ.
- Талбайн байршлыг хянах боломжтой. Та хүссэн цонхнууддаа баганыг чирж буулгахаас гадна өөрт тохирсон дарааллаар эрэмбэлэх боломжтой. Та мөн зүйлсийг шүүж болно. Үүнийг хийхийн тулд тухайн зүйлийг "Шүүлтүүр" цонх руу чирнэ үү. Хэрэв та тусдаа талбарт тооцоолох шаардлагатай бол "Утга" цонх руу чирнэ үү.
- Та ашиглаж болно нэмэлт сонголтууд. Та тэдгээрийг дараах зарчмаар олж болно: "Параметрүүд" "Пивот хүснэгттэй ажиллах". Олон тооны тохиргоо, нэмэлт функц бүхий олон хэрэгслүүд байдаг.
Пивот хүснэгтийн шидтэнг ашиглан хэрхэн пивот хүснэгт үүсгэх вэ
PivotTable Wizard хэрэгсэл үндсэн самбарт харагдахгүй байна. Тийм учраас эхлээд идэвхжүүлэх хэрэгтэй. Үүнийг хийхийн тулд "Файл" "Сонголтууд" "Хурдан нэвтрэх хэрэгслийн самбар" руу очно уу. чиний өмнө жагсаалт нээгдэнэ"командуудыг сонгоно уу", "Тууз дээрх тушаалууд" -ыг шалгана уу. Одоо Pivot Table Wizard-г сонгоод нэмнэ үү. Гүйцэтгэсэн бүх үйлдлүүдийн дараа үндсэн самбар дээр сонгосон функцийн дүрс гарч ирнэ.
Одоо та шидтэнг ашиглан хураангуй тайлан үүсгэх үйл явцыг үргэлжлүүлж болно:
Хэрэв тайланд шаардлагатай бүх өгөгдлийг агуулсан энгийн тайланг эх сурвалжаар сонгосон бол та үүнийг хийх хэрэгтэй. Хүрээг тохируулах, өөрөөр хэлбэл өгөгдлийг сонгохэнгийн ширээ. Байршуулах газраа сонгоод "Дуусгах" товчийг дарна уу.
Хэрэв та бусад эх сурвалжаас өгөгдлийг нэгтгэх шаардлагатай эх сурвалжийг сонговол тэмдэглэгдсэн байх ёстойэнэ зүйлийн эсрэг талд. Одоо та хуудасны талбаруудыг хэрхэн үүсгэхээ зааж өгөх хэрэгтэй. Та мөн мужийг зааж өгөх хэрэгтэй. Үүнийг хийхийн тулд эхлээд нэг мужийг сонгоод "нэмэх" товчийг дарна уу. Дараагийн утгуудыг мөн нэмж оруулсан болно.
Хүрээнд андуурахгүйн тулд тэдэнд нэр өгөх хэрэгтэй. Үүнийг хийхийн тулд сонгоно уу хүрээнүүдийн нэгжагсааж, талбарын тоог зааж өгсний дараа талбар бүрт нэр өгнө. Мөн бүх хүрээтэй.
Excel 2010 пивот хүснэгтүүд нь олон хуудастай
Хэрэв тайлангийн мэдээлэл ижил төрлийн байвал пивот хүснэгтийн шидтэнг ашиглах нь хамгийн хялбар юм. Хэрэв тайлан нь нарийн төвөгтэй бүтэцтэй бол та өөрөөр ажиллах хэрэгтэй. Жишээлбэл, хоёр ширээ байна, нэг нь барааны хүлээн авсан баримт, нөгөө нь - өөр өөр дэлгүүрт зарагдсан барааны тоо хэмжээг агуулдаг. Хамгийн гол нь ижил гарчигтай байх явдал юм, бага хугацаа нь тэдгээрийг нэг тайланд шилжүүлэх болно. Энэ тохиолдолд тайланг дараах байдлаар хийнэ.
Одоо бид хураангуй тайлан үүсгэх хэрэгтэй:
- Оруулах.
- Нэгдсэн тайлан.
- Хүрээ болон байршлыг оруулна уу.
Гэхдээ хэд хэдэн хүснэгтээс тайлан гаргах шаардлагатай бол яах вэ. Гараар ажиллахад хэцүү байдаг. Үүний төлөө та ашиглаж болно EXCEL-ийн тусгай нэмэлт. Үүнийг хөгжүүлэгчийн вэбсайтаас татаж авсан.
Тиймээс бид яаж гэдгийг олж мэдсэн пивот хүснэгт үүсгэх excel дээр. Та бага зэрэг тэвчээр, анхааралтай байх хэрэгтэй, тэгвэл үр дүн нь таныг хүлээхгүй болно.
Ачааж байна...