Өгөгдлийн загвараар пивотын ашиг тус

Excel дээр пивот хүснэгт үүсгэх үед бид эхний мужийг тохируулж, пивот хүснэгт оруулах газрыг сонгохыг хүссэн хамгийн эхний харилцах цонхонд доор үл үзэгдэх боловч маш чухал шалгах хайрцаг байна - Энэ өгөгдлийг өгөгдлийн загварт нэмнэ үү (Энэ өгөгдлийг нэмнэ үү Өгөгдлийн загвар руу) болон, бага зэрэг өндөр, унтраалга Энэ номын өгөгдлийн загварыг ашигла (Энэ ажлын номын өгөгдлийн загварыг ашиглах):

Харамсалтай нь, пивот хүснэгтийг удаан хугацаанд мэддэг бөгөөд тэдгээрийг ажилдаа амжилттай ашигладаг олон хэрэглэгчид заримдаа эдгээр сонголтуудын утгыг ойлгодоггүй бөгөөд хэзээ ч ашигладаггүй. Тэгээд дэмий л. Эцсийн эцэст, өгөгдлийн загварт зориулсан пивот хүснэгтийг бий болгох нь бидэнд сонгодог Excel пивот хүснэгттэй харьцуулахад хэд хэдэн чухал давуу талыг өгдөг.

Гэсэн хэдий ч, эдгээр "банк" -ыг нарийвчлан авч үзэхээсээ өмнө эхлээд энэ Дата загвар гэж юу болохыг олж мэдье?

Өгөгдлийн загвар гэж юу вэ

Өгөгдлийн загвар (MD эсвэл DM = Өгөгдлийн загвар гэж товчилсон) нь Excel файлын доторх тусгай талбар бөгөөд та хүснэгтэн өгөгдлийг хадгалах боломжтой - хэрэв хүсвэл хоорондоо холбогдсон нэг буюу хэд хэдэн хүснэгт. Үнэн хэрэгтээ энэ нь Excel-ийн ажлын дэвтэрт суулгагдсан жижиг мэдээллийн сан (OLAP шоо) юм. Excel-ийн хуудсан дээрх ердийн (эсвэл ухаалаг) хүснэгт хэлбэрээр өгөгдөл хадгалах сонгодог хувилбартай харьцуулахад өгөгдлийн загвар нь хэд хэдэн чухал давуу талтай байдаг.

• Хүснэгтүүд хүртэл байж болно 2 тэрбум мөр, Excel хуудас нь 1 саяас бага зэрэг багтах боломжтой.
• Асар том хэмжээтэй хэдий ч ийм хүснэгтийг боловсруулах (шүүх, ангилах, тэдгээрийн тооцоолол, барилгын хураангуй гэх мэт) хийгддэг. маш хурдан Excel-ээс хамаагүй хурдан.
• Загвар дахь өгөгдлүүдийн тусламжтайгаар та нэмэлт (хүсвэл маш нарийн төвөгтэй) тооцоолол хийх боломжтой суурилуулсан DAX хэл.
  
• Өгөгдлийн загварт ачаалагдсан бүх мэдээлэл маш их хүчтэй шахагдсан Тусгай суулгасан архивлагч ашиглан анхны Excel файлын хэмжээг бага зэрэг нэмэгдүүлдэг.
  

Загварыг Microsoft Excel-д суулгасан тусгай нэмэлтээр удирдаж, тооцоолдог. цахилгаан эргэлтБи аль хэдийн бичсэн байгаа. Үүнийг идэвхжүүлэхийн тулд таб дээр Developer дарж COM нэмэлтүүд (Хөгжүүлэгч - COM нэмэлтүүд) болон тохирох нүдийг шалгана уу:

Хэрэв таб Developer (Хөгжүүлэгч)Та туузан дээр харагдахгүй, та үүнийг асааж болно Файл - Сонголтууд - Туузны тохиргоо (Файл - Сонголтууд - Туузыг тохируулах). Хэрэв COM нэмэлтүүдийн жагсаалтын дээр харуулсан цонхонд танд Power Pivot байхгүй бол энэ нь таны Microsoft Office хувилбарт ороогүй болно 🙁

Гарч ирэх Power Pivot таб дээр том цайвар ногоон товчлуур байх болно удирдлага (Удирдах), дээр дарснаар Excel-ийн дээд талд Power Pivot цонх нээгдэх бөгөөд бид одоогийн номын өгөгдлийн загварын агуулгыг харах болно.

Замын нэгэн чухал тэмдэглэл: Excel ажлын ном нь зөвхөн нэг өгөгдлийн загвар агуулж болно.

Өгөгдлийн загварт хүснэгтүүдийг ачаалах

Загварт өгөгдөл ачаалахын тулд эхлээд хүснэгтийг динамик "ухаалаг" гарын товчлол болгон хувиргадаг Ctrl+T мөн таб дээр ээлтэй нэр өгнө үү байгуулагч (Дизайн). Энэ бол зайлшгүй шаардлагатай алхам юм.

Дараа нь та дараах гурван аргын аль нэгийг ашиглаж болно.

• Товчин дээр дарна уу Загварт нэмэх (Өгөгдлийн загварт нэмэх) таб цахилгаан эргэлт таб Нүүр хуудас (Нүүр хуудас).
  
• Багуудыг сонгох Оруулах - Пивот хүснэгт (Оруулах - Пивот хүснэгт) мөн шалгах нүдийг асаана уу Энэ өгөгдлийг өгөгдлийн загварт нэмнэ үү (Энэ өгөгдлийг өгөгдлийн загварт нэмнэ үү). Энэ тохиолдолд загварт ачаалагдсан өгөгдлийн дагуу пивот хүснэгтийг мөн нэн даруй бүтээдэг.
  
• Нарийвчилсан таб дээр Өгөгдөл (Огноо) товчин дээр дарна уу Хүснэгтээс/Мужаас (Хүснэгт/Мужаас)Хүснэгтийг Power Query засварлагч руу ачаалах. Энэ зам нь хамгийн урт боловч хэрэв хүсвэл энд нэмэлт өгөгдөл цэвэрлэх, засварлах, Power Query маш хүчтэй байдаг бүх төрлийн хувиргалтыг хийх боломжтой.

  Дараа нь самнасан өгөгдлийг тушаалаар Загварт байршуулна Нүүр хуудас — Хаах ба ачаалах — Хаах ба ачаалах... (Нүүр хуудас — Хаах&Ачаалах — Хаах&Ачаалах...). Нээгдсэн цонхонд сонголтыг сонгоно уу Зүгээр л холболт үүсгэ (Зөвхөн холболт үүсгэх) хамгийн чухал нь хачиг тавих Энэ өгөгдлийг өгөгдлийн загварт нэмнэ үү (Энэ өгөгдлийг өгөгдлийн загварт нэмнэ үү).
  

Бид өгөгдлийн загварын хураангуйг гаргадаг

Өгөгдлийн хураангуй загварыг бий болгохын тулд та гурван аргын аль нэгийг ашиглаж болно:

• Товчин дээр дарна уу хураангуй хүснэгт (Тэнхлэгийн хүснэгт) Power Pivot цонхонд.
• Excel дээр командуудыг сонгоно уу Оруулах - Пивот хүснэгт болон горимд шилжих Энэ номын өгөгдлийн загварыг ашигла (Оруулах - Пивот хүснэгт - Энэ ажлын номын өгөгдлийн загварыг ашиглах).
• Багуудыг сонгох Оруулах - Пивот хүснэгт (Оруулах - Пивот хүснэгт) мөн шалгах нүдийг асаана уу Энэ өгөгдлийг өгөгдлийн загварт нэмнэ үү (Энэ өгөгдлийг өгөгдлийн загварт нэмнэ үү). Одоогийн "ухаалаг" хүснэгтийг Загварт ачаалж, бүх загварт зориулж хураангуй хүснэгтийг бүтээх болно.

Одоо бид Өгөгдлийн загварт хэрхэн өгөгдлийг ачаалж, түүн дээр хураангуйг гаргахаа олж мэдсэн бол энэ нь бидэнд өгч буй ашиг тус, давуу талыг судалж үзье.

Ашиг тус 1: Томьёо ашиглахгүйгээр хүснэгтүүдийн хоорондын хамаарал

Ердийн хураангуйг зөвхөн нэг эх сурвалжийн хүснэгтийн өгөгдлийг ашиглан үүсгэж болно. Хэрэв танд борлуулалт, үнийн жагсаалт, хэрэглэгчийн лавлах, гэрээний бүртгэл гэх мэт хэд хэдэн байгаа бол та эхлээд VLOOKUP гэх мэт функцуудыг ашиглан бүх хүснэгтээс нэг мэдээлэл цуглуулах хэрэгтэй болно. (ХАРАХ), INDEX (INDEX), ИЛҮҮ ИЛҮҮ (ТОГЛОЛТ), SUMMESLIMN (SUMIFS) гэх мэт. Энэ нь урт, уйтгартай бөгөөд таны Excel-ийг их хэмжээний өгөгдөл агуулсан "бодолд" оруулдаг.

Өгөгдлийн загварын хураангуйн хувьд бүх зүйл илүү хялбар байдаг. Хүснэгтүүдийн хоорондын харилцааг Power Pivot цонхонд нэг удаа тохируулахад хангалттай бөгөөд энэ нь дууссан. Үүнийг хийхийн тулд таб дээр цахилгаан эргэлт товчийг дарна уу удирдлага (Удирдах) дараа нь гарч ирэх цонхонд - товчлуур Диаграм харах (диаграмм харах). Холбоос үүсгэхийн тулд хүснэгтүүдийн хооронд нийтлэг (гол) баганын нэрийг (талбар) чирэхэд л үлддэг.

Үүний дараа өгөгдлийн загварт зориулсан хураангуй хэсэгт та холбогдох хүснэгтээс дурын талбаруудыг (мөр, багана, шүүлтүүр, утга) оруулах боломжтой - бүх зүйл автоматаар холбогдож, тооцоолно:

Ашиг 2: Өвөрмөц үнэт зүйлсийг тоол

Ердийн пивот хүснэгт нь нийлбэр, дундаж, тоо, хамгийн бага, хамгийн их гэх мэт хэд хэдэн суулгасан тооцооллын функцүүдийн аль нэгийг сонгох боломжийг бидэнд олгодог. Өгөгдлийн загварын хураангуйд энэ стандарт жагсаалтад тоолоход маш хэрэгтэй функцийг нэмж оруулсан болно. өвөрмөц (давтагдахгүй утгуудын тоо). Жишээлбэл, түүний тусламжтайгаар та хот бүрт бидний борлуулдаг өвөрмөц барааны тоог (хүрээ) хялбархан тоолж болно.

Талбар дээр хулганы баруун товчийг дарна уу - команд Утгын талбарын сонголтууд болон таб дээр үйл ажиллагаа сонгоно уу Төрөл бүрийн элементүүдийн тоо (Тодорхой тоо):

Ашиг 3: Захиалгат DAX томъёо

Заримдаа та пивот хүснэгтэд янз бүрийн нэмэлт тооцоолол хийх хэрэгтэй болдог. Тогтмол хураангуйд үүнийг тооцоолсон талбарууд болон объектуудыг ашиглан хийдэг бол өгөгдлийн загварын хураангуй нь тусгай DAX хэл дээрх хэмжүүрүүдийг ашигладаг (DAX = Өгөгдлийн шинжилгээний илэрхийллүүд).

Хэмжилт үүсгэхийн тулд таб дээрээс сонгоно уу цахилгаан эргэлт тушаал Арга хэмжээ – Хэмжээ үүсгэх (Арга хэмжээ - Шинэ арга хэмжээ) эсвэл Pivot Fields жагсаалтын хүснэгтэн дээр хулганы баруун товчийг дараад сонгоно уу Хэмжээ нэмнэ (Хэмжээ нэмэх) контекст цэсэнд:

Нээгдсэн цонхонд дараахыг тохируулна уу:

• Хүснэгтийн нэрүүсгэсэн хэмжүүр хаана хадгалагдах болно.
• Хэмжих нэр – шинэ талбарт таны ойлгож байгаа ямар ч нэр.
• Тодорхойлолт - заавал биш.
• Формула - хамгийн чухал зүйл, учир нь бид энд гараар оруулах эсвэл товчлуур дээр дарна уу fx Жагсаалтаас DAX функцийг сонгоод дараа нь бид хэмжүүрээ Утга талбарт оруулахад үр дүнг тооцох ёстой.
• Цонхны доод хэсэгт та жагсаалтад байгаа хэмжүүрийн тооны форматыг нэн даруй тохируулж болно Ангилал.
  

DAX хэлийг ойлгоход тийм ч хялбар байдаггүй, учир нь хувь хүний ​​утгууд биш, харин бүхэл бүтэн багана, хүснэгтүүдтэй ажилладаг, өөрөөр хэлбэл, Excel-ийн сонгодог томъёоны дараа сэтгэлгээний бүтцийн өөрчлөлтийг шаарддаг. Гэсэн хэдий ч энэ нь үнэ цэнэтэй юм, учир нь түүний их хэмжээний өгөгдлийг боловсруулах чадварыг хэт үнэлэхэд хэцүү байдаг.

Ашиг 4: Тусгай талбарын шатлал

Ихэнхдээ стандарт тайланг үүсгэхдээ ижил төстэй талбаруудыг өгөгдсөн дарааллаар пивот хүснэгтэд оруулах шаардлагатай болдог. Жил-Улирал-Сар-Өдөр, эсвэл Ангилал-Бүтээгдэхүүн, эсвэл Улс-Хот-Үйлчлүүлэгч гэх мэт. Өгөгдлийн загварын хураангуйд энэ асуудлыг өөрөө бий болгосноор амархан шийдэгддэг шаталсан байдал - тусгай талбарын багц.

Power Pivot цонхонд товчлуурын тусламжтайгаар график горим руу шилжинэ Диаграм харах таб Нүүр хуудас (Нүүр хуудас - Диаграмм харах), ашиглан сонгоно уу Ctrl Хүссэн талбарууд дээр хулганы баруун товчийг дарна уу. Контекст цэс нь тушаалыг агуулна Шатлал үүсгэх (Шатлал үүсгэх):

Үүсгэсэн шатлалын нэрийг өөрчилж, хулганаар шаардлагатай талбаруудыг чирж болох бөгөөд ингэснээр дараа нь нэг хөдөлгөөнөөр тэдгээрийг хураангуй хэсэгт оруулах боломжтой.

Ашиг тус 5: Захиалгат stencils

Өмнөх догол мөрийн санааг үргэлжлүүлэхийн тулд өгөгдлийн загварын хураангуй хэсэгт та талбар бүрт өөрийн элементийн багцыг үүсгэж болно. Жишээлбэл, та бүхэл бүтэн хотуудын жагсаалтаас зөвхөн өөрийн хариуцаж буй бүс нутгуудын багцыг хялбархан хийж болно. Эсвэл зөвхөн өөрийн үйлчлүүлэгчид, бараа бүтээгдэхүүн гэх мэт зүйлсийг тусгай багц болгон цуглуулаарай.

Үүнийг хийхийн тулд таб дээр Пивот хүснэгтийн шинжилгээ доош унах жагсаалтад Талбарууд, зүйлс, багцууд холбогдох тушаалууд байдаг (Шинжилгээ хийх - Fields, Items & Sets - Мөр/баганын зүйл дээр үндэслэн багц үүсгэх):

Нээгдсэн цонхон дээр та аль нэг элементийн байршлыг сонгон устгах, нэмэх, өөрчлөх боломжтой бөгөөд үүссэн багцыг шинэ нэрээр хадгалах боломжтой.

Бүх үүсгэсэн багцууд нь PivotTable Fields самбарт тусдаа хавтсанд харагдах бөгөөд тэндээс тэдгээрийг ямар ч шинэ Пивот Хүснэгтийн мөр, баганын хэсгүүдэд чөлөөтэй чирж болно.

Ашиг тус 6: Хүснэгт болон баганыг сонгон нуух

Хэдийгээр энэ нь жижиг боловч зарим тохиолдолд маш тааламжтай давуу тал юм. Power Pivot цонхны талбарын нэр эсвэл хүснэгтийн таб дээр хулганы баруун товчийг дарснаар та тушаалыг сонгож болно. Client Toolkit-ээс нуух (Үйлчлүүлэгчийн хэрэгслээс нуух):

Пивот хүснэгтийн талбарын жагсаалтаас далд багана эсвэл хүснэгт алга болно. Хэрэв та хэрэглэгчээс зарим туслах багана (жишээлбэл, тооцоолсон эсвэл харилцаа үүсгэх гол утгууд бүхий багана) эсвэл бүр бүх хүснэгтийг нуух шаардлагатай бол энэ нь маш тохиромжтой.

Ашиг тус 7. Нарийвчилсан өрөмдлөг

Хэрэв та ердийн пивот хүснэгтийн утгын хэсгийн аль нэг нүдэн дээр давхар товшвол Excel нь энэ нүдийг тооцоолоход оролцсон эх өгөгдлийн фрагментийн хуулбарыг тусдаа хуудсан дээр харуулна. Энэ бол албан ёсоор Drill-down гэж нэрлэгддэг маш хэрэгтэй зүйл юм (тэд ихэвчлэн "бүтэлгүйтсэн" гэж хэлдэг).

Өгөгдлийн загварын хураангуйд энэхүү тохиромжтой хэрэгсэл нь илүү нарийн ажилладаг. Бидний сонирхож буй үр дүн бүхий дурын нүдэн дээр зогсоод түүний хажууд гарч ирэх томруулдаг шилтэй дүрс дээр дарж болно (үүнийг нэрлэдэг Экспресс чиг хандлага) дараа нь холбогдох хүснэгтээс сонирхож буй талбараа сонгоно уу:

Үүний дараа одоогийн утга (Загвар = Explorer) шүүлтүүрийн талбарт орох бөгөөд хураангуйг оффисууд байгуулна:

Мэдээжийн хэрэг, ийм процедурыг олон удаа давтаж, сонирхож буй чиглэлийнхээ дагуу өгөгдлийг байнга судалж болно.

Ашиг тус 8: Пивотыг куб функц болгон хөрвүүлэх

Хэрэв та өгөгдлийн загварын хураангуй нүднээс аль нэг нүдийг сонгоод таб дээрээс сонгоно уу Пивот хүснэгтийн шинжилгээ тушаал OLAP хэрэгслүүд - Томъёо руу хөрвүүлэх (Шинжилгээ хийх — OLAP хэрэгсэл — Томъёо руу хөрвүүлэх), дараа нь хураангуйг бүхэлд нь томъёонд автоматаар хөрвүүлэх болно. Одоо мөр баганын талбар дахь талбарын утгууд болон утгын талбар дахь үр дүнг CUBEVALUE болон CUBEMEMBER гэсэн тусгай куб функцийг ашиглан Өгөгдлийн загвараас гаргаж авах болно:

Техникийн хувьд энэ нь одоо бид хураангуй биш, харин томьёотой хэд хэдэн нүдтэй харьцаж байна гэсэн үг юм, өөрөөр хэлбэл бид хураангуйд байхгүй аливаа өөрчлөлтийг тайландаа хялбархан хийж болно, жишээлбэл, дунд нь шинэ мөр эсвэл багана оруулах боломжтой. тайлан, хураангуй дотор нэмэлт тооцоо хийх, тэдгээрийг хүссэн хэлбэрээр зохион байгуулах гэх мэт.

Үүний зэрэгцээ, эх сурвалжийн өгөгдөлтэй холболт нь мэдээжийн хэрэг хэвээр байгаа бөгөөд ирээдүйд эх сурвалжууд өөрчлөгдөх үед эдгээр томъёог шинэчлэх болно. Гоо үзэсгэлэн!

• Power Pivot болон Power Query бүхий пивот хүснэгтэд төлөвлөгөө-факт шинжилгээ
• Олон мөрийн толгойтой пивот хүснэгт
• Power Pivot ашиглан Excel-д мэдээллийн сан үүсгэ