የምሰሶ ሠንጠረዥ በበርካታ የውሂብ ክልሎች

የችግሩ መፈጠር

የምሰሶ ሰንጠረዦች በ Excel ውስጥ ካሉት በጣም አስደናቂ መሳሪያዎች ውስጥ አንዱ ናቸው። ግን እስካሁን ድረስ ፣ እንደ አለመታደል ሆኖ ፣ የ Excel ስሪቶች ውስጥ አንዳቸውም እንደዚህ ቀላል እና አስፈላጊ ነገር በበረራ ላይ ሊሠሩ አይችሉም ፣ ለምሳሌ ፣ በተለያዩ ሉሆች ወይም በተለያዩ ጠረጴዛዎች ውስጥ የሚገኙትን በርካታ የመጀመሪያ የውሂብ ክልሎች ማጠቃለያ በመገንባት ላይ።

ከመጀመራችን በፊት፣ ሁለት ነጥቦችን እናብራራ። በቅድሚያ፣ በእኛ መረጃ ውስጥ የሚከተሉት ሁኔታዎች እንደተሟሉ አምናለሁ፡

• ሰንጠረዦች ከየትኛውም ውሂብ ጋር የረድፎች ቁጥር ሊኖራቸው ይችላል, ግን ተመሳሳይ ራስጌ ሊኖራቸው ይገባል.
• ከምንጭ ጠረጴዛዎች ጋር በሉሆች ላይ ምንም ተጨማሪ መረጃ መኖር የለበትም። አንድ ሉህ - አንድ ጠረጴዛ. ለመቆጣጠር፣ የቁልፍ ሰሌዳ አቋራጭ እንድትጠቀሙ እመክራችኋለሁ መቆጣጠሪያ+መጨረሻ, ይህም በስራ ሉህ ውስጥ ወደ መጨረሻው ጥቅም ላይ የዋለ ሕዋስ ያንቀሳቅሰዎታል. በሐሳብ ደረጃ፣ ይህ በመረጃ ሠንጠረዥ ውስጥ የመጨረሻው ሕዋስ መሆን አለበት። ላይ ጠቅ ሲያደርጉ ከሆነ መቆጣጠሪያ+መጨረሻ በቀኝ ወይም ከሠንጠረዡ በታች ያለው ማንኛውም ባዶ ሕዋስ ይደምቃል - እነዚህን ባዶ ዓምዶች በቀኝ ወይም ከጠረጴዛው በታች ያሉትን ረድፎች ሰርዝ እና ፋይሉን ያስቀምጡ.

ዘዴ 1 የኃይል መጠይቅን በመጠቀም ለአንድ ምሰሶ የሚሆን ጠረጴዛዎችን ይገንቡ

ከ 2010 የ Excel ስሪት ጀምሮ ማንኛውንም ውሂብ ሊሰበስብ እና ሊለውጥ የሚችል እና ከዚያ የምሰሶ ጠረጴዛ ለመገንባት የሚያስችል ነፃ የኃይል መጠይቅ ተጨማሪ አለ። በዚህ ተጨማሪ እርዳታ ችግራችንን መፍታት በጭራሽ አስቸጋሪ አይደለም።

በመጀመሪያ በ Excel ውስጥ አዲስ ባዶ ፋይል እንፍጠር - ስብሰባ በእሱ ውስጥ ይከናወናል እና ከዚያ የምሰሶ ሠንጠረዥ በውስጡ ይፈጠራል።

ከዚያ በትሩ ላይ መረጃ (ኤክሴል 2016 ወይም ከዚያ በኋላ ካለዎት) ወይም በትሩ ላይ የኃይል ጥያቄ (ኤክሴል 2010-2013 ካለዎት) ትዕዛዙን ይምረጡ ጥያቄ ፍጠር - ከፋይል - ኤክሴል (ውሂብ ያግኙ - ከፋይል - ኤክሴል) እና ከሚሰበሰቡ ሠንጠረዦች ጋር የምንጭ ፋይልን ይግለጹ፡

በሚታየው መስኮት ውስጥ ማንኛውንም ሉህ ይምረጡ (የትኛው ምንም ለውጥ የለውም) እና ከታች ያለውን ቁልፍ ይጫኑ ለዉጥ (አርትዕ):

የኃይል መጠይቅ ጥያቄ አርታኢ መስኮት በኤክሴል አናት ላይ መከፈት አለበት። በፓነሉ ላይ በመስኮቱ በቀኝ በኩል መለኪያዎችን ይጠይቁ ከመጀመሪያው በስተቀር ሁሉንም በራስ-ሰር የተፈጠሩ እርምጃዎችን ሰርዝ - ምንጭ (ምንጭ):

አሁን የሁሉም ሉሆች አጠቃላይ ዝርዝር እናያለን። ከውሂብ ሉሆች በተጨማሪ በፋይሉ ውስጥ አንዳንድ ሌሎች የጎን ሉሆች ካሉ ታዲያ በዚህ ደረጃ የእኛ ተግባር በሠንጠረዡ ራስጌ ውስጥ ያለውን ማጣሪያ የሚጠቀሙትን ሁሉንም ሳይጨምር መረጃ የሚጫኑባቸውን ሉሆች ብቻ መምረጥ ነው-

ከአምድ በስተቀር ሁሉንም አምዶች ይሰርዙ መረጃየአምድ ርዕስ በቀኝ ጠቅ በማድረግ እና በመምረጥ ሌሎች አምዶችን ሰርዝ (አስወግድ ሌሎች አምዶች):

ከዚያም በአምዱ አናት ላይ ባለው ድርብ ቀስት (አመልካች ሳጥን) ላይ ጠቅ በማድረግ የተሰበሰቡትን የጠረጴዛዎች ይዘት ማስፋት ይችላሉ የመጀመሪያውን የአምድ ስም እንደ ቅድመ ቅጥያ ይጠቀሙ ማጥፋት ይችላሉ):

ሁሉንም ነገር በትክክል ካደረጉት, በዚህ ጊዜ የሁሉም ሰንጠረዦች ይዘቶች አንዱን ከሌላው በታች ማየት አለብዎት.

የመጀመሪያውን ረድፍ በአዝራሩ ወደ ጠረጴዛው ራስጌ ከፍ ለማድረግ ይቀራል የመጀመሪያውን መስመር እንደ ራስጌ ይጠቀሙ (የመጀመሪያውን ረድፍ እንደ ራስጌ ተጠቀም) ትር መግቢያ ገፅ (ቤት) እና ማጣሪያ በመጠቀም የተባዙ የሰንጠረዥ ራስጌዎችን ከውሂቡ ያስወግዱ፡-

በትእዛዙ የተሰራውን ሁሉ ያስቀምጡ ዝጋ እና ጫን - ዝጋ እና ጫን በ… (ዝጋ እና ጫን - ዝጋ እና ጫን ወደ…) ትር መግቢያ ገፅ (ቤት), እና በሚከፈተው መስኮት ውስጥ አማራጩን ይምረጡ ግንኙነት ብቻ (ግንኙነት ብቻ):

ሁሉም ነገር። ማጠቃለያ ለመገንባት ብቻ ይቀራል. ይህንን ለማድረግ ወደ ትሩ ይሂዱ አስገባ - PivotTable (አስገባ - የምሰሶ ሠንጠረዥ), አማራጩን ይምረጡ ውጫዊ የውሂብ ምንጭን ተጠቀም (የውጭ የመረጃ ምንጭ ተጠቀም)እና ከዚያ አዝራሩን ጠቅ በማድረግ ግንኙነት ይምረጡ, የእኛ ጥያቄ. የምንፈልጋቸውን መስኮች ወደ ረድፎች ፣ አምዶች እና እሴቶች አካባቢ በመጎተት የምሰሶው ተጨማሪ መፍጠር እና ማዋቀር ሙሉ በሙሉ መደበኛ በሆነ መንገድ ይከሰታል።

የምንጭ ውሂቡ ለወደፊቱ ከተቀየረ ወይም ጥቂት ተጨማሪ የሱቅ ሉሆች ከተጨመሩ ትዕዛዙን በመጠቀም መጠይቁን እና ማጠቃለያችንን ማዘመን በቂ ይሆናል ሁሉንም ያድሱ ትር መረጃ (ውሂብ - ሁሉንም አድስ).

ዘዴ 2. ጠረጴዛዎችን ከ UNION SQL ትዕዛዝ ጋር በማክሮ ውስጥ አንድ እናደርጋለን

ሌላው ለችግራችን መፍትሄ በዚህ ማክሮ የተወከለው ሲሆን ይህም ትዕዛዙን ተጠቅሞ የምሰሶ ሠንጠረዥ የውሂብ ስብስብ (መሸጎጫ) ይፈጥራል። ዩኒት የ SQL መጠይቅ ቋንቋ። ይህ ትእዛዝ በድርድር ውስጥ ከተገለጹት ሁሉም ሠንጠረዦችን ያጣምራል። የሉህ ስሞች የመጽሐፉ ሉሆች ወደ አንድ የውሂብ ሰንጠረዥ። ይህም ማለት፣ ከተለያዩ ሉሆች ወደ አንድ ክልሎችን በአካል ከመቅዳት እና ከመለጠፍ፣ በኮምፒዩተር ራም ውስጥም እንዲሁ እናደርጋለን። ከዚያ ማክሮው በተሰጠው ስም (ተለዋዋጭ) አዲስ ሉህ ያክላል የውጤት ሉህ ስም) እና በተሰበሰበው መሸጎጫ ላይ ተመስርተው በእሱ ላይ የተሟላ (!) ማጠቃለያ ይፈጥራል.

ማክሮን ለመጠቀም በትሩ ላይ Visual Basic የሚለውን ቁልፍ ይጠቀሙ ገንቢ (ገንቢ) ወይም የቁልፍ ሰሌዳ አቋራጭ alt+F11. ከዚያም በምናሌው ውስጥ አዲስ ባዶ ሞጁል እናስገባለን አስገባ - ሞጁል እና የሚከተለውን ኮድ እዚያ ይቅዱ።

ንኡስ አዲስ_ባለብዙ_ጠረጴዛ_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames እንደ ተለዋጭ 'የሉህ ስም የት የተገኘበት ምሰሶው የሚታይበት' የውጤት "SheetName sheet" ስሞች ከምንጭ ሠንጠረዦች ጋር የሉሆች ስሞች = አደራደር ("አልፋ"፣ "ቤታ"፣ "ጋማ"፣ "ዴልታ") 'ከሉሆች የስም መሸጎጫ ሠንጠረዦችን ከActiveWorkbook ReDim arSQL(1 እስከ (የሉሆች ስሞች) + 1) እንፈጥራለን። ) ለ i = LBound (የሉሆች ስሞች) ወደ UBound (የሉሆች ስሞች) arSQL(i + 1) = "ከ [" & ሉሆች ስሞች(i) እና "$] ይምረጡ * ቀጣይ i objRS = CreateObject("ADODB.Recordset") objRS አዘጋጅ .ክፍት Join$( arSQL፣ " UNION ALL ")፣ _ Join$(Array("አቅራቢ=Microsoft.Jet.OLEDB.4.0፤ የውሂብ ምንጭ="፣_ .FullName፣";Extended Properties=""Excel 8.0;" "")፣ vbNullString ) ጨርስ በ 'ዳግም ፍጠር ሉህ ውጤቱን የምሰሶ ሠንጠረዥ በስህተት ላይ ለማሳየት ቀጣይ ትግበራ ከቆመበት ቀጥል።ማሳያ ማስጠንቀቂያ = የውሸት የስራ ሉሆች(የውጤት ሼት ስም) ን ሰርዝ wsPivot = የስራ ሉሆችን አክል wsPivo ቲ. ስም = የውጤት ሼት ስም 'በዚህ ሉህ ላይ የተፈጠረውን መሸጎጫ ማጠቃለያ አሳይ objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) objPivotCache አዘጋጅ objPivotCache = ምንም ክልል የለም("A3")።በመጨረሻ ንኡስ መጨረሻ ምረጥ    

የተጠናቀቀው ማክሮ በቁልፍ ሰሌዳ አቋራጭ ሊሄድ ይችላል። alt+F8 ወይም በትሩ ላይ ያለው የማክሮዎች አዝራር ገንቢ (ገንቢ - ማክሮ).

የዚህ አቀራረብ ጉዳቶች-

• መሸጎጫው ከምንጩ ሠንጠረዦች ጋር ምንም ግንኙነት ስለሌለው ውሂቡ አልተዘመነም። የምንጭ ውሂቡን ከቀየሩ ማክሮውን እንደገና ማስኬድ እና ማጠቃለያውን እንደገና መገንባት አለብዎት።
• የሉሆችን ብዛት በሚቀይሩበት ጊዜ የማክሮ ኮድን (ድርድር) ማስተካከል አስፈላጊ ነው የሉህ ስሞች).
  

ግን በመጨረሻ ከተለያዩ ሉሆች በበርካታ ክልሎች ላይ የተገነባ እውነተኛ የተሟላ የምሰሶ ጠረጴዛ እናገኛለን።

የለም!

ቴክኒካዊ ማስታወሻ፡- ማክሮውን በሚያሄዱበት ጊዜ እንደ “አቅራቢው አልተመዘገበም” ያለ ስህተት ካጋጠመዎት ምናልባት 64-ቢት የ Excel ወይም ያልተሟላ የ Office ስሪት አልተጫነም (መዳረሻ የለም)። ሁኔታውን ለማስተካከል በማክሮ ኮድ ውስጥ ያለውን ቁራጭ ይተኩ፡

	 አቅራቢ= ማይክሮሶፍት.Jet.OLEDB.4.0;  

ወደ:

	አቅራቢ= ማይክሮሶፍት.ACE.OLEDB.12.0;  

እና ነፃ የመረጃ ማቀነባበሪያ ሞተሩን ከማይክሮሶፍት ድህረ ገጽ መዳረሻ ያውርዱ እና ይጫኑት - Microsoft Access Database Engine 2010 እንደገና ሊሰራጭ የሚችል

ዘዴ 3፡ PivotTable Wizardን ከአሮጌው የኤክሴል ስሪቶች ያጠናክሩ

ይህ ዘዴ ትንሽ ጊዜ ያለፈበት ነው, ግን አሁንም መጥቀስ ተገቢ ነው. በመደበኛ አነጋገር፣ እስከ 2003 ድረስ በሁሉም ስሪቶች ውስጥ፣ በPivotTable Wizard ውስጥ “ለበርካታ የማጠናከሪያ ክልሎች ምሰሶ መገንባት” አማራጭ ነበር። ነገር ግን፣ በዚህ መንገድ የተሰራ ዘገባ፣ በሚያሳዝን ሁኔታ፣ የእውነተኛ ሙሉ ማጠቃለያ አሳዛኝ ገጽታ ብቻ ይሆናል እና ብዙ የመደበኛ የምሰሶ ሰንጠረዦችን “ቺፕስ” አይደግፍም።

በእንደዚህ አይነት ምሰሶ ውስጥ, በመስክ ዝርዝር ውስጥ ምንም የአምድ ርእሶች የሉም, ተለዋዋጭ መዋቅር መቼት የለም, ጥቅም ላይ የሚውለው የተግባር ስብስብ ውስን ነው, እና በአጠቃላይ, ይህ ሁሉ ከምስሶ ሠንጠረዥ ጋር በጣም ተመሳሳይ አይደለም. ለዚህም ነው ከ2007 ጀምሮ ማይክሮሶፍት የምሰሶ ሠንጠረዥ ሪፖርቶችን ሲፈጥር ይህንን ተግባር ከመደበኛው ንግግር ያስወጣው። አሁን ይህ ባህሪ የሚገኘው በብጁ አዝራር ብቻ ነው። PivotTable አዋቂ(የምስሶ ጠረጴዛ አዋቂ), ከተፈለገ ወደ ፈጣን መዳረሻ Toolbar በ በኩል መጨመር ይቻላል ፋይል - አማራጮች - ፈጣን መዳረሻ የመሳሪያ አሞሌን ያብጁ - ሁሉም ትዕዛዞች (ፋይል - አማራጮች - ፈጣን መዳረሻ የመሳሪያ አሞሌን ያብጁ - ሁሉም ትዕዛዞች):

በተጨመረው ቁልፍ ላይ ጠቅ ካደረጉ በኋላ በአዋቂው የመጀመሪያ ደረጃ ላይ ተገቢውን አማራጭ መምረጥ ያስፈልግዎታል-

እና ከዚያ በሚቀጥለው መስኮት እያንዳንዱን ክልል በየተራ ይምረጡ እና ወደ አጠቃላይ ዝርዝር ያክሉት።

ግን፣ በድጋሚ፣ ይህ ሙሉ ማጠቃለያ አይደለም፣ ስለዚህ ከእሱ ብዙ አትጠብቅ። ይህንን አማራጭ በጣም ቀላል በሆኑ ጉዳዮች ላይ ብቻ እመክራለሁ.

• በPivotTables ሪፖርቶችን መፍጠር
• በ PivotTables ውስጥ ስሌቶችን ያቀናብሩ
• ማክሮዎች ምንድን ናቸው ፣ እንዴት እንደሚጠቀሙባቸው ፣ የ VBA ኮድ የት እንደሚገለበጥ ፣ ወዘተ.
• የውሂብ መሰብሰብ ከብዙ ሉሆች ወደ አንድ (PLEX add-on)