ဒေတာ အပိုင်းအခြားများစွာရှိ Pivot ဇယား

ဖော်မြူလာပြဿနာ

Pivot tables များသည် Excel တွင် အံ့သြဖွယ်အကောင်းဆုံးကိရိယာများထဲမှ တစ်ခုဖြစ်သည်။ သို့သော် ယခုအချိန်အထိ ကံမကောင်းစွာဖြင့်၊ ဥပမာ၊ စာရွက်များပေါ်တွင် သို့မဟုတ် မတူညီသော ဇယားများတွင် တည်ရှိသော ကနဦးဒေတာအကျဉ်းချုပ်တစ်ခုတည်ဆောက်ခြင်းကဲ့သို့ ရိုးရှင်းပြီး လိုအပ်သည့်အရာကို Excel ဗားရှင်းတစ်ခုမှ မလုပ်ဆောင်နိုင်ပါ။

မစတင်မီ အချက်နှစ်ချက်ကို ရှင်းလင်းကြပါစို့။ ဦးစားပေး၊ ကျွန်ုပ်တို့၏ဒေတာတွင် အောက်ပါအခြေအနေများနှင့် ကိုက်ညီသည်ဟု ကျွန်ုပ်ယုံကြည်ပါသည်။

• ဇယားများတွင် မည်သည့်ဒေတာပါသည့် အတန်းအရေအတွက်မဆို ရှိနိုင်သော်လည်း ၎င်းတို့တွင် တူညီသော ခေါင်းစီးရှိရမည်။
• အရင်းအမြစ်ဇယားများပါရှိသော စာရွက်များပေါ်တွင် အပိုဒေတာမရှိသင့်ပါ။ စာရွက်တစ်ရွက် - စားပွဲတစ်လုံး။ ထိန်းချုပ်ရန်အတွက် ကီးဘုတ်ဖြတ်လမ်းကို အသုံးပြုရန် သင့်အား ကျွန်ုပ်အကြံပြုပါသည်။ Ctrl ++အဆုံးအလုပ်စာရွက်ရှိ နောက်ဆုံးအသုံးပြုထားသောဆဲလ်သို့ ရွှေ့ပေးသည်။ အကောင်းဆုံးမှာ၊ ၎င်းသည် ဒေတာဇယားရှိ နောက်ဆုံးဆဲလ်ဖြစ်သင့်သည်။ နှိပ်လိုက်တာနဲ့ Ctrl ++အဆုံး ညာဘက်ရှိ သို့မဟုတ် ဇယားအောက်တွင်ရှိသော မည်သည့်ဆဲလ်အလွတ်များကိုမဆို မီးမောင်းထိုးပြထားသည် - ဤအချည်းနှီးသောကော်လံများကို ဇယား၏ညာဘက်သို့ ဖျက်ပြီး ဖိုင်ကိုသိမ်းပြီးနောက် ဇယားအောက်ရှိ အတန်းများကို ဖျက်ပါ။

နည်းလမ်း 1- Power Query ကို အသုံးပြု၍ ဆုံချက်တစ်ခုအတွက် ဇယားများတည်ဆောက်ပါ။

Excel အတွက် 2010 ဗားရှင်းမှစတင်၍ မည်သည့်ဒေတာကိုမဆို စုဆောင်းပြီး ပြောင်းလဲနိုင်သော အခမဲ့ Power Query add-in ပါရှိပြီး ဆုံချက်ဇယားတစ်ခုတည်ဆောက်ရန်အတွက် အရင်းအမြစ်အဖြစ် ပေးဆောင်နိုင်ပါသည်။ ဤ add-in ၏အကူအညီဖြင့်ကျွန်ုပ်တို့၏ပြဿနာကိုဖြေရှင်းရန်လုံးဝမခက်ခဲပါ။

ပထမဦးစွာ၊ Excel တွင် အလွတ်ဖိုင်အသစ်တစ်ခုကို ဖန်တီးကြပါစို့ - စည်းဝေးပွဲသည် ၎င်းတွင် ဆုံချက်ဇယားတစ်ခုကို ဖန်တီးမည်ဖြစ်သည်။

ပြီးရင် tab ပေါ်မှာ ဒေတာများ (သင့်တွင် Excel 2016 သို့မဟုတ် နောက်ပိုင်းတွင်ရှိပါက) သို့မဟုတ် တက်ဘ်ပေါ်တွင် ပါဝါ Query (သင့်တွင် Excel 2010-2013 ရှိပါက) command ကိုရွေးချယ်ပါ။ Query - File - Excel မှ ဖန်တီးပါ။ (ဒေတာရယူပါ — ဖိုင်မှ — Excel) စုဆောင်းရမည့်ဇယားများဖြင့် အရင်းအမြစ်ဖိုင်ကို သတ်မှတ်ပါ-

ပေါ်လာသောဝင်းဒိုးတွင်၊ မည်သည့်စာရွက် (မည်သည့်အရာမှအရေးမပါပါ) ကိုရွေးချယ်ပြီးအောက်ပါခလုတ်ကိုနှိပ်ပါ။ ပွောငျးလဲ (တည်းဖြတ်ရန်):

Power Query Query Editor ဝင်းဒိုးသည် Excel ၏ထိပ်တွင် ဖွင့်သင့်သည်။ ပြတင်းပေါက်၏ညာဘက်ခြမ်းတွင် panel ကိုပေါ် ကန့်သတ်ချက်များတောင်းဆိုခြင်း။ ပထမအဆင့်မှလွဲ၍ အလိုအလျောက် ဖန်တီးထားသော အဆင့်အားလုံးကို ဖျက်ပါ- အရင်းအမြစ် (ရင်းမြစ်):

ယခု ကျွန်ုပ်တို့သည် စာရွက်အားလုံး၏ အထွေထွေစာရင်းကို မြင်နေရပါသည်။ ဒေတာစာရွက်များအပြင် ဖိုင်ထဲတွင် အခြားဘေးထွက်စာရွက်များပါရှိနေပါက၊ ဤအဆင့်တွင် ကျွန်ုပ်တို့၏တာဝန်မှာ ဇယားခေါင်းစီးရှိ filter ကိုအသုံးပြုသည့် အခြားအားလုံးမှအပ ကျန်အချက်အလက်များကို တင်ရန်လိုအပ်သည့် စာရွက်များကိုသာ ရွေးချယ်ရန်ဖြစ်သည်-

ကော်လံမှလွဲ၍ ကော်လံအားလုံးကို ဖျက်ပါ။ ဒေတာများကော်လံခေါင်းစီးတစ်ခုကို ညာဖက်ကလစ်နှိပ်ပြီး ရွေးချယ်ခြင်းဖြင့် အခြားကော်လံများကို ဖျက်ပါ။ (ဖယ်ရှားပါ။ အခြားကော်လံများ):

ထို့နောက် ကော်လံထိပ်ရှိ မြှားနှစ်ထပ်ကို နှိပ်ခြင်းဖြင့် စုဆောင်းထားသော ဇယားများ၏ အကြောင်းအရာများကို ချဲ့ထွင်နိုင်သည် (အမှန်ခြစ်ပုံး မူရင်းကော်လံအမည်ကို ရှေ့ဆက်အဖြစ် အသုံးပြုပါ။ သင်ပိတ်နိုင်သည်):

အကယ်၍ သင်သည် အရာအားလုံးကို မှန်ကန်စွာ လုပ်ဆောင်ခဲ့လျှင် ဤအချိန်တွင် အခြားတစ်ခု၏အောက်တွင် စုစည်းထားသော ဇယားများအားလုံး၏ အကြောင်းအရာများကို သင်တွေ့မြင်ရမည်ဖြစ်ပါသည်-

ခလုတ်ဖြင့် ပထမတန်းကို ဇယားခေါင်းစီးသို့ မြှင့်တင်ရန် ကျန်ရှိနေပါသည်။ ခေါင်းစီးများအဖြစ် ပထမစာကြောင်းကို အသုံးပြုပါ။ (ပထမတန်းကို ခေါင်းစီးများအဖြစ် သုံးပါ) tab ကို ပင်မစာမျက်နှာ (အိမ်) စစ်ထုတ်မှုသုံးပြီး ဒေတာမှ ထပ်နေသောဇယားခေါင်းစီးများကို ဖယ်ရှားပါ-

Command ဖြင့် လုပ်ဆောင်သမျှကို သိမ်းဆည်းပါ။ ပိတ်ပြီး တင်ပါ - ပိတ်ပြီး ဖွင့်ပါ... (Close & Load — Close & Load to…) tab ကို ပင်မစာမျက်နှာ (အိမ်)နှင့်ဖွင့်သောဝင်းဒိုးတွင်၊ ရွေးချယ်စရာကိုရွေးချယ်ပါ။ ချိတ်ဆက်မှုသာ (ချိတ်ဆက်မှုသာ):

အရာအားလုံး။ အနှစ်ချုပ်တည်ဆောက်ရန်သာ ကျန်တော့သည်။ ဒါကိုလုပ်ဖို့၊ တက်ဘ်ကိုသွားပါ။ - PivotTable ထည့်သွင်းပါ။ (ထည့်သွင်း - Pivot Table), option ကိုရွေးချယ်ပါ။ ပြင်ပဒေတာအရင်းအမြစ်ကို အသုံးပြုပါ။ (ပြင်ပဒေတာအရင်းအမြစ်ကိုသုံးပါ)ထို့နောက် ခလုတ်ကိုနှိပ်ပါ။ ချိတ်ဆက်မှုကို ရွေးပါ။ငါတို့တောင်းဆိုချက်။ မဏ္ဍိုင်၏နောက်ထပ်ဖန်တီးမှုနှင့် ဖွဲ့စည်းမှုပုံစံသည် ကျွန်ုပ်တို့လိုအပ်သောအကွက်များကို အတန်းများ၊ ကော်လံများနှင့် တန်ဖိုးများဧရိယာသို့ ဆွဲယူခြင်းဖြင့် စံနည်းလမ်းဖြင့် ဖြစ်ပေါ်လာသည်-

အနာဂတ်တွင် အရင်းအမြစ်ဒေတာပြောင်းလဲမှု သို့မဟုတ် နောက်ထပ်စတိုးစာရွက်အနည်းငယ်ကို ထပ်ထည့်ပါက၊ မေးမြန်းချက်နှင့် ကျွန်ုပ်တို့၏အနှစ်ချုပ်ကို ကွန်မန်းကို အသုံးပြု၍ အပ်ဒိတ်လုပ်ရန် လုံလောက်ပါလိမ့်မည်။ အားလုံးကို ပြန်လည်စတင်ပါ။ tab ကို ဒေတာများ (ဒေတာ — အားလုံးကို ပြန်လည်စတင်ပါ).

နည်းလမ်း ၂။ ကျွန်ုပ်တို့သည် မက်ခရိုတစ်ခုတွင် UNION SQL အမိန့်ဖြင့် ဇယားများကို ပေါင်းစည်းသည်။

ကျွန်ုပ်တို့၏ပြဿနာအတွက် နောက်ထပ်ဖြေရှင်းချက်မှာ အမိန့်ပေးချက်ကို အသုံးပြု၍ pivot ဇယားအတွက် ဒေတာအစုံ (cache) ဖန်တီးပေးသည့် ဤ macro မှ ကိုယ်စားပြုသည် စည်းလုံးမှု SQL Query ဘာသာစကား။ ဤ command သည် array တွင် သတ်မှတ်ထားသည့် ဇယားများအားလုံးကို ပေါင်းစပ်ထားသည်။ စာရွက်အမည်များ စာအုပ်တစ်အုပ်ကို ဒေတာဇယားတစ်ခုထဲထည့်ပါ။ ဆိုလိုသည်မှာ၊ မတူညီသောစာရွက်များမှ တစ်ခုသို့ အပိုင်းများကို ရုပ်ပိုင်းဆိုင်ရာ ကူးယူပြီး ကူးထည့်မည့်အစား၊ ကျွန်ုပ်တို့သည် ကွန်ပျူတာ၏ RAM တွင် တူညီပါသည်။ ထို့နောက် မက်ခရိုသည် ပေးထားသောအမည်ဖြင့် စာရွက်အသစ်တစ်ခု ထပ်ထည့်သည် (variable ဖြစ်သည်။ ရလဒ်စာရွက်အမည်) စုဆောင်းထားသော ကက်ရှ်အပေါ် အခြေခံ၍ ပြည့်စုံသော (!) အနှစ်ချုပ်ကို ဖန်တီးပါ။

မက်ခရိုကိုအသုံးပြုရန်၊ တက်ဘ်ပေါ်ရှိ Visual Basic ခလုတ်ကို အသုံးပြုပါ။ ဆော့ဖ်ဝဲရေး (ပြုစုသူ) သို့မဟုတ် ကီးဘုတ်ဖြတ်လမ်း alt+F11. ထို့နောက် မီနူးမှတဆင့် အလွတ် module အသစ်တစ်ခုကို ထည့်သွင်းပါ။ Module ထည့်သွင်းပါ။ ပြီးလျှင် အောက်ပါကုဒ်ကို ထိုနေရာတွင် ကူးယူပါ။

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant ' sheet names as variant 'sheet name အဖြစ် ရလဒ် pivot ကိုပြသမည့်" sheet of ResultSheetName = "Pivotray" ရင်းမြစ်ဇယားများ SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'ကျွန်ုပ်တို့သည် SheetsNames With ActiveWorkbook ReDim arSQL(1 မှ (UBound(SheetsNames)) + 1) မှ ဇယားကွက်များအတွက် ကက်ရှ်တစ်ခု ပြုလုပ်ပါသည်။ ) အတွက် i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" နောက်တစ်ခု i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) ရလဒ်ထွက်မဏ္ဍိုင်ဇယားကိုပြသရန် စာရွက်ကို ပြန်လည်ဖန်တီးခြင်းဖြင့် အဆုံးသတ်ပါ Error Resume Next Application.DisplayAlerts = မှားယွင်းသော Worksheets(ResultSheetName).ဖျက်ပါ Set wsPivot = Worksheets.Add wsPivo t အမည် = ResultSheetName 'ဤစာရွက်ပေါ်တွင် ထုတ်ပေးထားသော ကက်ရှ်အကျဉ်းချုပ်ကို ပြသပါ သတ်မှတ်သည် objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) သတ်မှတ်ပါ objPivotCache.Recordset = objRS သတ်မှတ်ခြင်း objRS = wsPivot objPivotCache.CreatePivotAtable:3T objPivotCache = Nothing Range("A3"). End With End Sub ကို ရွေးပါ။    

ထို့နောက် အပြီးသတ် macro ကို ကီးဘုတ်ဖြတ်လမ်းဖြင့် လုပ်ဆောင်နိုင်မည်ဖြစ်သည်။ alt+F8 သို့မဟုတ် တက်ဘ်ပေါ်ရှိ Macros ခလုတ် ဆော့ဖ်ဝဲရေး (တီထွင်သူ — Macros).

ဤချဉ်းကပ်မှု၏အားနည်းချက်များ

• ကက်ရှ်သည် အရင်းအမြစ်ဇယားများနှင့် ချိတ်ဆက်မှုမရှိသောကြောင့် ဒေတာကို အပ်ဒိတ်မလုပ်ပါ။ အရင်းအမြစ်ဒေတာကို သင်ပြောင်းပါက၊ မက်ခရိုကို ထပ်မံလုပ်ဆောင်ပြီး အကျဉ်းချုပ်ကို ထပ်မံတည်ဆောက်ရပါမည်။
• စာရွက်အရေအတွက်ကို ပြောင်းလဲသောအခါ၊ မက်ခရိုကုဒ် (array) ကို တည်းဖြတ်ရန် လိုအပ်သည်။ စာရွက်အမည်များ).
  

သို့သော် နောက်ဆုံးတွင် အလွှာများစွာမှ အပိုင်းအခြားများစွာဖြင့် တည်ဆောက်ထားသော တကယ့်ပြည့်စုံသော ဆုံချက်ဇယားကို ကျွန်ုပ်တို့ ရရှိသည်-

ကဲဒါဆိုရင်တော့!

နည်းပညာမှတ်စု- Macro ကို run သောအခါ "Provider not registerd" ကဲ့သို့ error တစ်ခုခုရပါက၊ သင့်တွင် Excel ၏ 64-bit ဗားရှင်းတစ်ခု သို့မဟုတ် Office ၏ မပြည့်စုံသောဗားရှင်းကို ထည့်သွင်းထားသည် ( Access မရှိပါ)။ အခြေအနေကို ပြုပြင်ရန်၊ မက်ခရိုကုဒ်ရှိ အပိုင်းအစကို အစားထိုးပါ။

	 ပံ့ပိုးသူ=Microsoft.Jet.OLEDB.4.0;  

မှ:

	ပံ့ပိုးသူ=Microsoft.ACE.OLEDB.12.0;  

Microsoft ဝဘ်ဆိုက်မှ Access မှ အခမဲ့ဒေတာ စီမံဆောင်ရွက်ရေးအင်ဂျင်ကို ဒေါင်းလုဒ်လုပ်ပြီး တပ်ဆင်ပါ – Microsoft Access Database Engine 2010 ပြန်လည်ဖြန့်ဝေနိုင်သော

နည်းလမ်း 3- Excel ဗားရှင်းဟောင်းမှ PivotTable Wizard ကို စုစည်းပါ။

ဤနည်းလမ်းသည် အနည်းငယ် ခေတ်နောက်ကျနေသော်လည်း မှတ်သားထိုက်ပါသေးသည်။ တရားဝင်ပြောရလျှင် 2003 ခုနှစ်အထိနှင့် အပါအဝင် ဗားရှင်းအားလုံးတွင်၊ "စုစည်းမှုအပိုင်းအခြားများစွာအတွက် pivot တစ်ခုတည်ဆောက်ရန်" PivotTable Wizard တွင် ရွေးချယ်စရာတစ်ခုရှိသည်။ သို့သော်လည်း၊ ကံမကောင်းစွာပဲ၊ ဤနည်းဖြင့် တည်ဆောက်ထားသော အစီရင်ခံစာသည် အမှန်တကယ် ပြည့်စုံသော အကျဉ်းချုပ်၏ သနားစရာကောင်းသော အသွင်အပြင်သာဖြစ်ပြီး သမားရိုးကျ မဏ္ဍိုင်ဇယားများ၏ "ချစ်ပ်များ" အများအပြားကို မပံ့ပိုးနိုင်ပါ။

ထိုသို့သော pivot တွင်၊ အကွက်စာရင်းတွင် ကော်လံခေါင်းစီးများမရှိပါ၊ လိုက်လျောညီထွေရှိသောဖွဲ့စည်းပုံဆက်တင်မရှိပါ၊ အသုံးပြုထားသောလုပ်ဆောင်ချက်အစုံသည် အကန့်အသတ်ရှိပြီး ယေဘုယျအားဖြင့်၊ ဤအရာအားလုံးသည် မဏ္ဍိုင်ဇယားတစ်ခုနှင့် အလွန်ဆင်တူပါသည်။ ထို့ကြောင့် 2007 ခုနှစ်မှစတင်၍ Microsoft သည် pivot table အစီရင်ခံစာများကိုဖန်တီးရာတွင် standard dialog မှဤလုပ်ဆောင်ချက်ကိုဖယ်ရှားခဲ့သည်။ ယခု ဤအင်္ဂါရပ်ကို စိတ်ကြိုက်ခလုတ်ဖြင့်သာ ရနိုင်ပါပြီ။ PivotTable Wizard(Pivot Table Wizard)ဆန္ဒရှိပါက Quick Access Toolbar မှတစ်ဆင့် ထည့်သွင်းနိုင်သည်။ ဖိုင် – ရွေးချယ်စရာများ – အမြန်ဝင်ရောက်မှု ကိရိယာဘားကို စိတ်ကြိုက်ပြင်ဆင်ပါ – ညွှန်ကြားချက်များအားလုံး (ဖိုင် — ရွေးချယ်စရာများ — Quick Access Toolbar ကို စိတ်ကြိုက်ပြင်ဆင်ပါ — Commands အားလုံး):

ထပ်လောင်းခလုတ်ကိုနှိပ်ပြီးနောက် wizard ၏ပထမအဆင့်တွင်သင့်လျော်သောရွေးချယ်မှုကိုသင်ရွေးချယ်ရန်လိုအပ်သည်-

ထို့နောက် လာမည့်ဝင်းဒိုးတွင်၊ အလှည့်တစ်ခုစီတွင် အပိုင်းအခြားတစ်ခုစီကို ရွေးချယ်ပြီး အထွေထွေစာရင်းသို့ ထည့်ပါ-

သို့သော် ထပ်မံ၍ ဤအရာသည် ပြီးပြည့်စုံသော အကျဉ်းချုပ်မဟုတ်သောကြောင့် ၎င်းကို အလွန်အကျွံမမျှော်လင့်ပါနှင့်။ အလွန်ရိုးရှင်းသောကိစ္စများတွင်သာ ဤရွေးချယ်မှုကို ကျွန်ုပ်အကြံပြုနိုင်ပါသည်။

• PivotTables ဖြင့် အစီရင်ခံစာများ ဖန်တီးခြင်း။
• PivotTables တွင် တွက်ချက်မှုများကို စနစ်ထည့်သွင်းပါ။
• မက်ခရိုဟူသည် အဘယ်နည်း၊ ၎င်းတို့ကို အသုံးပြုနည်း၊ VBA ကုဒ်ကို မည်သည့်နေရာတွင် ကူးယူရမည်နည်း။
• စာရွက်များစွာမှ တစ်ခုသို့ ဒေတာစုဆောင်းခြင်း (PLEX add-on)