စားပွဲတစ်လုံးကို စာရွက်များခွဲ၍

Microsoft Excel တွင် ဇယားများစွာမှ ဒေတာစုဆောင်းရန် ကိရိယာများစွာ ရှိသည် (ကွဲပြားခြားနားသောစာရွက်များမှ သို့မဟုတ် ဖိုင်အမျိုးမျိုးမှ) တိုက်ရိုက်လင့်ခ်များ၊ လုပ်ဆောင်ချက်များ သွယ်ဝိုက်သည်။ (သွယ်ဝိုက်)၊ Power Query နှင့် Power Pivot အပိုပရိုဂရမ်များ စသည်ဖြင့်၊ အတားအဆီး၏ ဤဘက်ခြမ်းမှ အရာအားလုံးသည် ကောင်းမွန်လှသည်။

ဒါပေမယ့် သင်ဟာ ပြောင်းပြန်ပြဿနာတစ်ခုဖြစ်တဲ့ – ဇယားတစ်ခုကနေ အချက်အလက်တွေကို မတူညီတဲ့စာရွက်တွေဆီ ပျံ့နှံ့သွားမယ်ဆိုရင် အရာအားလုံးက ပိုဆိုးသွားပါလိမ့်မယ်။ လောလောဆယ်တွင် Excel ၏လက်နက်တိုက်တွင်ထိုကဲ့သို့သောဒေတာခွဲခြားခြင်းအတွက်ယဉ်ကျေးသော built-in ကိရိယာများမရှိပါ။ ထို့ကြောင့် သင်သည် Visual Basic တွင် macro ကိုသုံးရမည်ဖြစ်ပြီး၊ သို့မဟုတ် macro recorder + Power Query ပေါင်းစပ်ပြီးနောက် အနည်းငယ် "ဖိုင်ကို သန့်စင်ခြင်း" ဖြင့် အသုံးပြုရပါမည်။

ဒါကို ဘယ်လိုအကောင်အထည်ဖော်နိုင်မလဲဆိုတာကို အနီးကပ်လေ့လာကြည့်ရအောင်။

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

ကျွန်ုပ်တို့တွင် ရောင်းချရန်အတွက် အတန်းပေါင်း 5000 ကျော် အရွယ်အစားရှိသော ဇယားတစ်ခုကဲ့သို့ ကနဦးဒေတာ ရှိသည်-

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

ပြင်ဆင်ပါ

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

ပထမဦးစွာ သီးခြားရှာဖွေမှုဇယားတစ်ခုဖန်တီးပါ။ကော်လံတစ်ခုတည်းက သင် သီးခြားစာရွက်များဖန်တီးလိုသည့် မြို့အားလုံးကို စာရင်းပြုစုပေးမည်ဖြစ်သည်။ ဟုတ်ပါတယ်၊ ဤလမ်းညွှန်တွင် ရင်းမြစ်ဒေတာတွင်ရှိသော မြို့များအားလုံး မပါဝင်နိုင်သော်လည်း ကျွန်ုပ်တို့အတွက် လိုအပ်သော အစီရင်ခံစာများသာ ဖြစ်ပါသည်။ ထိုကဲ့သို့သောဇယားကိုဖန်တီးရန်အလွယ်ကူဆုံးနည်းလမ်းမှာ command ကိုအသုံးပြုရန်ဖြစ်သည်။ ဒေတာ - ထပ်တူများကို ဖယ်ရှားပါ။ (ဒေတာ — ထပ်တူများကို ဖယ်ရှားပါ) ကော်လံကော်ပီအတွက် ခံစားချက်တွေကို သို့မဟုတ် function ကို UNIK (UNIQUE) - သင့်တွင် Excel 365 ၏နောက်ဆုံးထွက်ဗားရှင်းရှိပါက။

Excel ရှိ စာရွက်အသစ်များကို လက်ရှိ (ယခင်) မတိုင်မီ (ဘယ်ဘက်) တွင် ပုံသေဖြင့် ဖန်တီးထားသောကြောင့် ဤလမ်းညွှန်ရှိမြို့များကို ကြီးစဉ်ငယ်လိုက် (Z မှ A) သို့ စီရန် - ထို့နောက် ဖန်တီးပြီးနောက် မြို့၊ စာရွက်များကို အက္ခရာစဉ်အလိုက် စီစဉ်ပေးပါမည်။

ဒုတိယ၊ пဇယားနှစ်ခုလုံးကို dynamic အဖြစ်ပြောင်းပါ။ ၎င်းတို့နှင့် လုပ်ဆောင်ရန် ပိုမိုလွယ်ကူစေရန် ("စမတ်")။ ကျွန်ုပ်တို့သည် command ကိုအသုံးပြုသည်။ ပင်မ – ဇယားအဖြစ် ဖော်မတ်ပါ။ (ပင်မ – ဇယားအဖြစ် ပုံစံဖော်ပါ) သို့မဟုတ် ကီးဘုတ်ဖြတ်လမ်း Ctrl ++T. ပေါ်လာတဲ့ tab ပေါ်မှာ လုပ်ငန်းခွင် (ဒီဇိုင်း) သူတို့ကို ခေါ်ရအောင် tablProdaji и ဇယားမြို့အသီးသီး

နည်းလမ်း 1. စာရွက်များဖြင့် ပိုင်းခြားရန် Macro

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

အပိုင်းအခြားရှိ ဆဲလ်တစ်ခုစီအတွက် Sub Splitter()("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(VolisCelly)။ Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub	  

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

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

နည်းလမ်း 2။ Power Query တွင် မေးခွန်းများစွာကို ဖန်တီးပါ။

၎င်း၏ ကျစ်လျစ်မှုနှင့် ရိုးရှင်းမှုအားလုံးအတွက် ယခင်နည်းလမ်းသည် သိသာထင်ရှားသော အားနည်းချက်တစ်ခု ရှိသည် - မူရင်းအရောင်းဇယားတွင် အပြောင်းအလဲများ ပြုလုပ်သောအခါတွင် Macro မှ ဖန်တီးထားသော စာရွက်များကို အပ်ဒိတ်မလုပ်ပါ။ အလျင်အမြန် အပ်ဒိတ်လုပ်ရန် လိုအပ်ပါက၊ VBA + Power Query အတွဲကို အသုံးပြုရမည်ဖြစ်ပြီး၊ သို့မဟုတ် static data ဖြင့် စာရွက်များသာမက အပ်ဒိတ်လုပ်ထားသော Power Query queries များကို အသုံးပြု၍ ဖန်တီးရန် လိုအပ်ပါသည်။

ဤကိစ္စတွင် မက်ခရိုသည် ယခင်တစ်ခုနှင့် တစ်စိတ်တစ်ပိုင်း ဆင်တူသည် (၎င်းတွင် စက်ဝန်းတစ်ခုလည်း ရှိသည်။ တစ်ခုစီအတွက်… နောက်တစ်ခု လမ်းညွှန်တွင်ရှိသော မြို့များကို ထပ်တလဲလဲလုပ်ရန်) သို့သော် စက်ဝိုင်းအတွင်းတွင် စစ်ထုတ်ခြင်းနှင့် ကူးယူခြင်းတို့ကို ပြုလုပ်တော့မည်မဟုတ်သော်လည်း Power Query query တစ်ခုကို ဖန်တီးပြီး ၎င်း၏ရလဒ်များကို စာရွက်အသစ်သို့ အပ်လုဒ်တင်ခြင်း-

Sub Splitter2() အပိုင်းအခြားရှိ ဆဲလ်တစ်ခုစီအတွက် ("City table") ActiveWorkbook.Queries.Add Name:=cell.Value၊ Formula:= _ "let" & Chr(13) & "" & Chr(10) & " အရင်းအမြစ် = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""အမျိုးအစားပြောင်းထားသည်"" = Table.TransformColumnTypes(အရင်းအမြစ် ၊ {{""အမျိုးအစား""၊ စာသားရိုက်ပါ}၊ {""အမည်"၊ စာသားရိုက်ပါ}၊ {""မြို့"၊ စာသားရိုက်ပါ}၊ {""မန်နေဂျာ"" စာသားရိုက်ထည့်ပါ}၊ {""သဘောတူညီချက် date "", type datetime}, {""ကုန်ကျစရိတ်"", အမျိုးအစား နံပါတ်}})," & Chr(13) & "" & Chr(10) & " #""စီစစ်ထားသော အတန်းများ"" = Table.Se " & _ "lectRows(#""ပြောင်းလဲထားသော အမျိုးအစား"", တစ်ခုစီ ([City] = """ & cell.Value & """))" & Chr(13) & """ & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""စစ်ထုတ်သည့်အတန်းများကို သုံးထားသည်""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; ဝန်ဆောင်မှုပေးသူ =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location="&cell.Value & ";Extended Properties="""" _ , Destination:=Range("$A$1"))။ QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM ["&cell.Value&"]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .FalsePassword. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Next cell End Sub  

စတင်ပြီးနောက်၊ မြို့အလိုက် တူညီသောစာရွက်များကို ကျွန်ုပ်တို့တွေ့မြင်ရမည်ဖြစ်ပြီး၊ ဖန်တီးထားပြီးဖြစ်သည့် Power Query queries များသည် ၎င်းတို့ကို ဖွဲ့ပေးမည်ဖြစ်ပါသည်-

အရင်းအမြစ်ဒေတာတွင် အပြောင်းအလဲများရှိပါက ညာဘက်မောက်စ်ခလုတ်ဖြင့် သက်ဆိုင်ရာဇယားကို အပ်ဒိတ်လုပ်ရန် လုံလောက်လိမ့်မည် - အမိန့် အပ်ဒိတ် & သိမ်းဆည်းပါ။ (ပြန်လည်ဆန်းသစ်) သို့မဟုတ် ခလုတ်ကို အသုံးပြု၍ မြို့များအားလုံးကို တစ်ပြိုင်နက် အပ်ဒိတ်လုပ်ပါ။ အားလုံး Update လုပ်ပါ tab ကို ဒေတာများ (ဒေတာ — အားလုံးကို ပြန်လည်စတင်ပါ).

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