Агуулга
Асуудлын томъёолол
Пивот хүснэгтүүд нь Excel-ийн хамгийн гайхалтай хэрэгслүүдийн нэг юм. Гэвч харамсалтай нь Excel-ийн аль ч хувилбар нь жишээлбэл, өөр өөр хуудсан дээр эсвэл өөр өөр хүснэгтэд байрлах хэд хэдэн анхны өгөгдлийн мужуудын хураангуйг бүтээх гэх мэт энгийн бөгөөд шаардлагатай зүйлийг шууд хийж чадахгүй.
Эхлэхээсээ өмнө хэд хэдэн зүйлийг тодруулъя. Бидний мэдээлэлд дараах нөхцөлүүд хангагдсан гэдэгт би итгэлтэй байна.
- Хүснэгтүүд нь ямар ч өгөгдөл бүхий хэдэн мөртэй байж болох ч тэдгээр нь ижил толгойтой байх ёстой.
- Эх хүснэгт бүхий хуудсан дээр нэмэлт мэдээлэл байх ёсгүй. Нэг хуудас - нэг ширээ. Удирдахын тулд би танд гарын товчлолыг ашиглахыг зөвлөж байна Ctrl+Төгсгөл, энэ нь таныг ажлын хуудасны хамгийн сүүлд ашигласан нүд рүү шилжүүлнэ. Энэ нь өгөгдлийн хүснэгтийн сүүлчийн нүд байх ёстой. Хэрэв та товших үед Ctrl+Төгсгөл Хүснэгтийн баруун эсвэл доор байгаа ямар ч хоосон нүдийг тодруулсан - хүснэгтийн дараах баруун талд байгаа эдгээр хоосон баганууд эсвэл хүснэгтийн доорх мөрүүдийг устгаад файлыг хадгална уу.
Арга 1: Power Query ашиглан пивот хийх хүснэгтүүдийг бүтээх
Excel-ийн 2010 оны хувилбараас эхлэн ямар ч өгөгдлийг цуглуулж, хувиргаж, дараа нь пивот хүснэгт үүсгэх эх сурвалж болгон өгөх боломжтой Power Query үнэгүй нэмэлт хэрэгсэл байдаг. Энэхүү нэмэлтийн тусламжтайгаар бидний асуудлыг шийдэх нь тийм ч хэцүү биш юм.
Эхлээд Excel дээр шинэ хоосон файл үүсгэцгээе - дотор нь угсралт явагдаж, дараа нь пивот хүснэгт үүснэ.
Дараа нь таб дээр Өгөгдөл (хэрэв танд Excel 2016 эсвэл түүнээс хойшхи хувилбар байгаа бол) эсвэл таб дээр Эрчим хүчний асуулга (хэрэв танд Excel 2010-2013 байгаа бол) командыг сонгоно уу Асуулга үүсгэх - Файлаас - Excel (Өгөгдөл авах - Файлаас - Excel) мөн цуглуулах хүснэгтүүдтэй эх файлыг зааж өгнө үү:
Гарч ирэх цонхон дээр дурын хуудсыг сонгоод (аль нь хамаагүй) доорх товчийг дарна уу Change (Засварлах):
Power Query Query Editor цонх Excel-ийн дээд талд нээгдэх ёстой. Самбар дээрх цонхны баруун талд Хүсэлтийн параметрүүд Эхнийхээс бусад автоматаар үүсгэсэн бүх алхмуудыг устгах - Эх сурвалж (Эх сурвалж):
Одоо бид бүх хуудасны ерөнхий жагсаалтыг харж байна. Хэрэв файлд өгөгдлийн хуудаснаас гадна бусад хажуугийн хуудас байгаа бол энэ алхамд бидний даалгавар бол хүснэгтийн толгойн шүүлтүүрийг ашиглан бусад бүх хуудсыг оруулахгүйгээр зөвхөн мэдээллийг ачаалах шаардлагатай хуудсыг сонгох явдал юм.
Баганаас бусад бүх баганыг устгана уу Өгөгдөлбаганын гарчиг дээр хулганы баруун товчийг дараад сонгоно Бусад баганыг устгана уу (Устгах бусад баганууд):
Дараа нь та баганын дээд талд байрлах давхар сум дээр дарж цуглуулсан хүснэгтүүдийн агуулгыг өргөжүүлж болно (шалгах хайрцаг Эх баганын нэрийг угтвар болгон ашигла та үүнийг унтрааж болно):
Хэрэв та бүх зүйлийг зөв хийсэн бол энэ үед та нэг дор цуглуулсан бүх хүснэгтийн агуулгыг харах хэрэгтэй.
Товчлуураар эхний мөрийг хүснэгтийн толгой руу өргөхөд л үлддэг Эхний мөрийг толгой болгон ашигла (Эхний мөрийг гарчиг болгон ашиглах) таб Нүүр хуудас (Нүүр хуудас) Шүүлтүүр ашиглан өгөгдлөөс давхардсан хүснэгтийн толгой хэсгийг устгана уу:
Командын тусламжтайгаар хийсэн бүх зүйлийг хадгал Хаах ба ачаалах – Хаах ба ачаалах… (Хаах & Ачаа - Хаах & Ачаалах ...) таб Нүүр хуудас (Нүүр хуудас), нээгдэх цонхноос сонголтыг сонгоно уу Зөвхөн холболт (Зөвхөн холболт):
Бүх зүйл. Зөвхөн хураангуйг бүтээхэд л үлддэг. Үүнийг хийхийн тулд таб руу очно уу Оруулах - Пивот хүснэгт (Оруулах - Пивот хүснэгт), сонголтыг сонгоно уу Гадаад мэдээллийн эх сурвалжийг ашиглах (Гадаад мэдээллийн эх сурвалжийг ашиглах)дараа нь товчлуур дээр дарна уу Холболтыг сонгоно уу, бидний хүсэлт. Пивотыг цаашид үүсгэх, тохируулах нь бидэнд хэрэгтэй талбаруудыг мөр, багана, утгын талбарт чирэх замаар бүрэн стандарт аргаар явагдана.
Хэрэв эх сурвалжийн өгөгдөл ирээдүйд өөрчлөгдөх эсвэл хэд хэдэн дэлгүүрийн хуудас нэмэгдэх юм бол командыг ашиглан асуулга болон бидний хураангуйг шинэчлэхэд хангалттай. Бүгдийг нь сэргээ таб Өгөгдөл (Өгөгдөл - Бүгдийг шинэчлэх).
Арга 2. Бид UNION SQL командын тусламжтайгаар хүснэгтүүдийг макро хэлбэрээр нэгтгэдэг
Бидний асуудлыг шийдэх өөр нэг шийдэл бол командыг ашиглан пивот хүснэгтэд зориулсан өгөгдлийн багц (кэш) үүсгэдэг энэхүү макро юм. ЭВ НЭГДЭЛ SQL асуулгын хэл. Энэ тушаал нь массив дээр заасан бүх хүснэгтүүдийг нэгтгэдэг Sheet Names номын хуудсыг нэг өгөгдлийн хүснэгт болгон. Өөрөөр хэлбэл, өөр өөр хуудаснаас нэг хуудас руу хуулж, буулгахын оронд бид компьютерийн RAM-д ижил зүйлийг хийдэг. Дараа нь макро нь өгөгдсөн нэртэй шинэ хуудас нэмнэ (хувьсагч Үр дүнгийн хуудасны нэр) мөн цуглуулсан кэш дээр үндэслэн бүрэн хэмжээний (!) хураангуйг үүсгэдэг.
Макро ашиглахын тулд таб дээрх Visual Basic товчийг ашиглана уу Developer (Хөгжүүлэгч) эсвэл гарын товчлол Alt+F11. Дараа нь бид цэсээр дамжуулан шинэ хоосон модулийг оруулна Оруулах - Модуль Дараах кодыг тэнд хуулж авна уу.
Дэд New_Multi_Table_Pivot() Dim i As Long Dim arSQL() String As Dim objPivotCache As PivotCache Dim objRS болгон Object Dim Үр дүнгийн SheetName as String Dim SheetsNames Variant 'хуудасны нэр энд гарч ирэх пивот нь харагдах болно. эх хүснэгт бүхий нэрс SheetsNames = Array("Альфа", "Бета", "Гамма", "Дельта") 'бид ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) бүхий SheetsNames-ийн хүснэгтүүдийн кэш үүсгэдэг. ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Дараа нь i Set objRS = CreateObject("ADODB.Recordset") objRS .Нээлттэй Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) 'ээр төгсгөх 'үр дүнд үүссэн пивот хүснэгтийг харуулах хуудсыг дахин үүсгэнэ Алдаа дээр Дараагийн програмыг үргэлжлүүлнэ.DisplayAlerts = False Worksheets(ResultSheetName).Устгах Set wsPivot = Worksheets.WsPivo нэмнэ. т. Нэр = ResultSheetName 'энэ хуудсан дээр үүсгэсэн кэшийн хураангуйг харуулах Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = wsPivot-тэй юу ч байхгүй objPivotCache = Nothing Range("A3"). Төгсгөлийн дэд хэсгийг сонго. Дууссан макро дараа нь гарын товчлолоор ажиллуулж болно Alt+F8 эсвэл таб дээрх Макро товчийг дарна уу Developer (Хөгжүүлэгч — Макро).
Энэ аргын сул талууд:
- Кэш нь эх хүснэгттэй холбоогүй тул өгөгдөл шинэчлэгдээгүй байна. Хэрэв та эх өгөгдлийг өөрчилбөл макро дахин ажиллуулж, хураангуйг дахин бүтээх ёстой.
- Хуудасны тоог өөрчлөхдөө макро кодыг засах шаардлагатай (массив Sheet Names).
Гэхдээ эцэст нь бид өөр өөр хуудаснаас хэд хэдэн мужид баригдсан жинхэнэ бүрэн хэмжээний пивот хүснэгтийг олж авдаг.
Воила!
Техникийн тэмдэглэл: Хэрэв та макро ажиллуулах үед "Үйлчилгээ үзүүлэгч бүртгүүлээгүй" гэх мэт алдаа гарвал танд Excel-ийн 64 битийн хувилбар эсвэл Office-ийн бүрэн бус хувилбар суулгасан байх магадлалтай (Хандалт байхгүй). Нөхцөл байдлыг засахын тулд макро кодын фрагментийг солино уу:
Үйлчилгээ үзүүлэгч=Microsoft.Jet.OLEDB.4.0;
нь:
Үйлчилгээ үзүүлэгч=Microsoft.ACE.OLEDB.12.0;
Мөн Microsoft-ын вэбсайтаас Access-ээс үнэгүй өгөгдөл боловсруулах системийг татаж аваад суулгаарай – Microsoft Access Database Engine 2010 Дахин түгээх боломжтой
Арга 3: Excel-ийн хуучин хувилбаруудаас пивот хүснэгтийн шидтэнг нэгтгэх
Энэ арга нь бага зэрэг хуучирсан боловч дурдах нь зүйтэй. Албан ёсоор хэлэхэд, 2003 он хүртэлх бүх хувилбаруудад Пивот Хүснэгтийн Шидтэн дээр "хэд хэдэн нэгтгэх мужид пивот үүсгэх" сонголт байсан. Гэсэн хэдий ч харамсалтай нь ийм байдлаар боловсруулсан тайлан нь жинхэнэ бүрэн хураангуйн өрөвдмөөр дүр төрх байх бөгөөд ердийн пивот хүснэгтүүдийн олон "чип" -ийг дэмждэггүй.
Ийм пивотод талбарын жагсаалтад баганын гарчиг байхгүй, уян хатан бүтцийн тохиргоо байхгүй, ашигласан функцүүдийн багц хязгаарлагдмал, ерөнхийдөө энэ бүхэн пивот хүснэгттэй тийм ч төстэй биш юм. Тийм ч учраас 2007 оноос эхлэн Майкрософт пивот хүснэгтийн тайланг үүсгэхдээ энэ функцийг стандарт харилцах цонхноос хассан байх. Одоо энэ функцийг зөвхөн тусгай товчлуураар ашиглах боломжтой Пивот хүснэгтийн шидтэн(пивот хүснэгтийн шидтэн), хэрэв хүсвэл хурдан нэвтрэх хэрэгслийн самбар дээр нэмж болно Файл - Сонголтууд - Түргэн нэвтрэх хэрэгслийн самбарыг тохируулах - Бүх тушаалууд (Файл - Сонголтууд - Түргэн хандалтын хэрэгслийн самбарыг тохируулах - Бүх тушаалууд):
Нэмсэн товчлуур дээр дарсны дараа та шидтэний эхний алхамд тохирох сонголтыг сонгох хэрэгтэй.
Дараа нь дараагийн цонхонд муж бүрийг ээлжлэн сонгоод ерөнхий жагсаалтад нэмнэ үү.
Гэхдээ дахин хэлэхэд энэ бол бүрэн хураангуй мэдээлэл биш тул үүнээс хэт их зүйл хүлээх хэрэггүй. Би энэ сонголтыг маш энгийн тохиолдолд л санал болгож чадна.
- Пивот хүснэгт ашиглан тайлан үүсгэх
- Пивот хүснэгтэд тооцооллыг тохируулна уу
- Макро гэж юу вэ, тэдгээрийг хэрхэн ашиглах, VBA кодыг хаана хуулах гэх мэт.
- Олон хуудаснаас нэг хуудас руу өгөгдөл цуглуулах (PLEX нэмэлт)