பல தரவு வரம்புகளில் பிவோட் அட்டவணை

சிக்கலை உருவாக்குதல்

பிவோட் அட்டவணைகள் எக்செல் இல் மிகவும் அற்புதமான கருவிகளில் ஒன்றாகும். ஆனால் இதுவரை, துரதிர்ஷ்டவசமாக, எக்செல் பதிப்புகள் எதுவும் பறக்கும்போது இதுபோன்ற எளிய மற்றும் அவசியமான காரியத்தைச் செய்ய முடியாது, எடுத்துக்காட்டாக, வெவ்வேறு தாள்களில் அல்லது வெவ்வேறு அட்டவணைகளில் அமைந்துள்ள பல ஆரம்ப தரவு வரம்புகளுக்கான சுருக்கத்தை உருவாக்குகிறது:

தொடங்குவதற்கு முன், இரண்டு புள்ளிகளை தெளிவுபடுத்துவோம். ஒரு முன்னோடியாக, எங்கள் தரவுகளில் பின்வரும் நிபந்தனைகள் பூர்த்தி செய்யப்பட்டுள்ளன என்று நான் நம்புகிறேன்:

• அட்டவணைகள் எந்தத் தரவையும் கொண்ட எத்தனை வரிசைகளைக் கொண்டிருக்கலாம், ஆனால் அவை ஒரே தலைப்பைக் கொண்டிருக்க வேண்டும்.
• மூல அட்டவணைகள் கொண்ட தாள்களில் கூடுதல் தரவு இருக்கக்கூடாது. ஒரு தாள் - ஒரு அட்டவணை. கட்டுப்படுத்த, விசைப்பலகை குறுக்குவழியைப் பயன்படுத்துமாறு நான் உங்களுக்கு அறிவுறுத்துகிறேன் ctrl+முடிவு, இது பணித்தாளில் கடைசியாகப் பயன்படுத்தப்பட்ட கலத்திற்கு உங்களை நகர்த்துகிறது. வெறுமனே, இது தரவு அட்டவணையில் உள்ள கடைசி கலமாக இருக்க வேண்டும். நீங்கள் கிளிக் செய்யும் போது ctrl+முடிவு அட்டவணையின் வலப்புறம் அல்லது கீழே உள்ள எந்த வெற்று கலமும் தனிப்படுத்தப்படும் - இந்த வெற்று நெடுவரிசைகளை வலதுபுறம் அல்லது அட்டவணையின் கீழே உள்ள வரிசைகளில் நீக்கி கோப்பை சேமிக்கவும்.

முறை 1: பவர் வினவலைப் பயன்படுத்தி பைவட்டிற்கான அட்டவணையை உருவாக்கவும்

எக்செல் 2010 பதிப்பிலிருந்து தொடங்கி, இலவச பவர் வினவல் ஆட்-இன் உள்ளது, அது எந்தத் தரவையும் சேகரித்து மாற்றியமைத்து, பிவோட் டேபிளை உருவாக்குவதற்கான ஆதாரமாக வழங்க முடியும். இந்த ஆட்-இன் மூலம் நமது பிரச்சனையைத் தீர்ப்பது ஒன்றும் கடினம் அல்ல.

முதலில், எக்ஸெல்-ல் ஒரு புதிய வெற்று கோப்பை உருவாக்குவோம் - அதில் அசெம்பிளி நடக்கும், பின்னர் அதில் பிவோட் டேபிள் உருவாக்கப்படும்.

பின்னர் தாவலில் தேதி (உங்களிடம் எக்செல் 2016 அல்லது அதற்குப் பிறகு இருந்தால்) அல்லது தாவலில் சக்தி வினவல் (உங்களிடம் எக்செல் 2010-2013 இருந்தால்) கட்டளையைத் தேர்ந்தெடுக்கவும் வினவலை உருவாக்கவும் - கோப்பிலிருந்து - எக்செல் (தரவைப் பெறவும் - கோப்பிலிருந்து - எக்செல்) சேகரிக்கப்பட வேண்டிய அட்டவணைகளுடன் மூலக் கோப்பைக் குறிப்பிடவும்:

தோன்றும் சாளரத்தில், ஏதேனும் தாளைத் தேர்ந்தெடுக்கவும் (எது ஒரு பொருட்டல்ல) மற்றும் கீழே உள்ள பொத்தானை அழுத்தவும் மாற்றம் (தொகு):

பவர் வினவல் எடிட்டர் சாளரம் எக்செல் மேல் திறக்க வேண்டும். பேனலில் சாளரத்தின் வலது பக்கத்தில் கோரிக்கை அளவுருக்கள் முதல் படிகளைத் தவிர தானாக உருவாக்கப்பட்ட அனைத்து படிகளையும் நீக்கு - மூல (மூலம்):

இப்போது அனைத்து தாள்களின் பொதுவான பட்டியலைக் காண்கிறோம். தரவுத் தாள்களைத் தவிர, கோப்பில் வேறு சில பக்கத் தாள்கள் இருந்தால், இந்த கட்டத்தில், அட்டவணையின் தலைப்பில் உள்ள வடிப்பானைப் பயன்படுத்தி மற்ற அனைத்தையும் தவிர்த்து, தகவல்களை ஏற்ற வேண்டிய தாள்களைத் தேர்ந்தெடுப்பதே எங்கள் பணி:

நெடுவரிசையைத் தவிர அனைத்து நெடுவரிசைகளையும் நீக்கு தேதிநெடுவரிசை தலைப்பை வலது கிளிக் செய்து தேர்ந்தெடுப்பதன் மூலம் மற்ற நெடுவரிசைகளை நீக்கு (நீக்கு மற்ற நெடுவரிசைகள்):

பின்னர் நெடுவரிசையின் மேற்புறத்தில் உள்ள இரட்டை அம்புக்குறியைக் கிளிக் செய்வதன் மூலம் சேகரிக்கப்பட்ட அட்டவணைகளின் உள்ளடக்கங்களை விரிவாக்கலாம் (செக்பாக்ஸ் அசல் நெடுவரிசைப் பெயரை முன்னொட்டாகப் பயன்படுத்தவும் நீங்கள் அதை அணைக்கலாம்):

நீங்கள் எல்லாவற்றையும் சரியாகச் செய்திருந்தால், இந்த கட்டத்தில் நீங்கள் சேகரிக்கப்பட்ட அனைத்து அட்டவணைகளின் உள்ளடக்கங்களையும் ஒன்றுக்கு கீழே பார்க்க வேண்டும்:

பட்டனுடன் முதல் வரிசையை அட்டவணை தலைப்புக்கு உயர்த்த இது உள்ளது முதல் வரியை தலைப்புகளாகப் பயன்படுத்தவும் (முதல் வரிசையை தலைப்புகளாகப் பயன்படுத்தவும்) தாவல் முகப்பு (வீடு) வடிப்பானைப் பயன்படுத்தி தரவிலிருந்து நகல் அட்டவணை தலைப்புகளை அகற்றவும்:

கட்டளையுடன் செய்த அனைத்தையும் சேமிக்கவும் மூடு மற்றும் ஏற்றவும் - மூடு மற்றும் ஏற்றவும்… (மூடு & ஏற்றவும் - மூடு & ஏற்றவும்...) தாவல் முகப்பு (வீடு), மற்றும் திறக்கும் சாளரத்தில், விருப்பத்தைத் தேர்ந்தெடுக்கவும் இணைப்பு மட்டுமே (இணைப்பு மட்டும்):

எல்லாம். இது ஒரு சுருக்கத்தை உருவாக்க மட்டுமே உள்ளது. இதைச் செய்ய, தாவலுக்குச் செல்லவும் செருகு - பிவோட் டேபிள் (செருகு - பிவோட் அட்டவணை), விருப்பத்தை தேர்வு செய்யவும் வெளிப்புற தரவு மூலத்தைப் பயன்படுத்தவும் (வெளிப்புற தரவு மூலத்தைப் பயன்படுத்தவும்)பின்னர் பொத்தானைக் கிளிக் செய்வதன் மூலம் இணைப்பைத் தேர்ந்தெடுக்கவும், எங்கள் கோரிக்கை. வரிசைகள், நெடுவரிசைகள் மற்றும் மதிப்புகள் பகுதிக்கு தேவையான புலங்களை இழுப்பதன் மூலம் பிவோட்டின் மேலும் உருவாக்கம் மற்றும் உள்ளமைவு முற்றிலும் நிலையான வழியில் நிகழ்கிறது:

எதிர்காலத்தில் மூல தரவு மாறினால் அல்லது இன்னும் சில ஸ்டோர் தாள்கள் சேர்க்கப்பட்டால், கட்டளையைப் பயன்படுத்தி வினவலையும் எங்கள் சுருக்கத்தையும் புதுப்பிக்க போதுமானதாக இருக்கும். அனைத்தையும் புதுப்பிக்கவும் தாவல் தேதி (தரவு - அனைத்தையும் புதுப்பிக்கவும்).

முறை 2. மேக்ரோவில் UNION SQL கட்டளையுடன் அட்டவணைகளை இணைக்கிறோம்

எங்கள் சிக்கலுக்கான மற்றொரு தீர்வு இந்த மேக்ரோவால் குறிப்பிடப்படுகிறது, இது கட்டளையைப் பயன்படுத்தி பிவோட் அட்டவணைக்கான தரவுத் தொகுப்பை (கேச்) உருவாக்குகிறது. ஒற்றுமை SQL வினவல் மொழி. இந்த கட்டளை அணிவரிசையில் குறிப்பிடப்பட்டுள்ள அனைத்து அட்டவணைகளையும் ஒருங்கிணைக்கிறது தாள் பெயர்கள் புத்தகத்தின் தாள்கள் ஒரு தரவு அட்டவணையில். அதாவது, வெவ்வேறு தாள்களில் இருந்து ஒன்று வரையிலான வரம்புகளை நகலெடுத்து ஒட்டுவதற்குப் பதிலாக, கணினியின் ரேமில் அதையே செய்கிறோம். மேக்ரோ கொடுக்கப்பட்ட பெயருடன் ஒரு புதிய தாளைச் சேர்க்கிறது (மாறி முடிவுத்தாள் பெயர்) மற்றும் சேகரிக்கப்பட்ட தற்காலிக சேமிப்பின் அடிப்படையில் முழு அளவிலான (!) சுருக்கத்தை உருவாக்குகிறது.

மேக்ரோவைப் பயன்படுத்த, தாவலில் உள்ள விஷுவல் பேசிக் பட்டனைப் பயன்படுத்தவும் மேம்பாட்டாளர் (டெவலப்பர்) அல்லது விசைப்பலகை குறுக்குவழி alt+F11. பின்னர் மெனு மூலம் புதிய வெற்று தொகுதியைச் செருகுவோம் செருகு - தொகுதி பின்வரும் குறியீட்டை அங்கு நகலெடுக்கவும்:

Sub New_Multi_Table_Pivot() மங்கலான நான் நீண்ட மங்கலான arSQL() சரம் மங்கலான objPivotCache என பைவட்கேச் மங்கலான objRS பொருளின் மங்கலான முடிவுSheetName என சரம் மங்கலான தாள்களின் பெயர்கள் மாறுபாடாக 'தாள் பெயர், இதன் விளைவாக வரும் பிவோட்' ஹீட் ஹீட் ரேட் பெயர் காட்டப்படும். மூல அட்டவணைகள் கொண்ட பெயர்கள் SheetsNames = வரிசை("ஆல்பா", "பீட்டா", "காமா", "டெல்டா") 'ஆக்டிவ்வொர்க்புக் ReDim arSQL(1 முதல் (UBound(SheetsNames) + 1) உடன் SheetsNames இலிருந்து தாள்களில் இருந்து டேபிள்களுக்கான தற்காலிக சேமிப்பை உருவாக்குகிறோம். ) i = LBound (SheetsNames) க்கு UBound(SheetsNames) arSQL(i + 1) = "தேர்வு * [" & 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 = தவறான பணித்தாள்கள்(ResultSheetName).நீக்கு Set wsPivot = Worksheets. சேர் wsPivo டி. பெயர் = ResultSheetName 'இந்தத் தாளில் உருவாக்கப்பட்ட கேச் சுருக்கத்தை காட்சிப்படுத்தவும் objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) அமை objPivotCache.Recordset = objRS அமை objRS = ஒன்றும் wsPivotCaches.T. objPivotCache = நத்திங் ரேஞ்ச்("A3"). End With End Sub என்பதைத் தேர்ந்தெடுக்கவும்.    

முடிக்கப்பட்ட மேக்ரோவை விசைப்பலகை குறுக்குவழி மூலம் இயக்கலாம் alt+F8 அல்லது தாவலில் உள்ள மேக்ரோஸ் பொத்தான் மேம்பாட்டாளர் (டெவலப்பர் - மேக்ரோஸ்).

இந்த அணுகுமுறையின் தீமைகள்:

• ஆதார அட்டவணைகளுடன் கேச் இணைப்பு இல்லாததால் தரவு புதுப்பிக்கப்படவில்லை. நீங்கள் மூலத் தரவை மாற்றினால், நீங்கள் மேக்ரோவை மீண்டும் இயக்க வேண்டும் மற்றும் சுருக்கத்தை மீண்டும் உருவாக்க வேண்டும்.
• தாள்களின் எண்ணிக்கையை மாற்றும்போது, ​​​​மேக்ரோ குறியீட்டைத் திருத்துவது அவசியம் (வரிசை தாள் பெயர்கள்).
  

ஆனால் இறுதியில், வெவ்வேறு தாள்களிலிருந்து பல வரம்புகளில் கட்டப்பட்ட உண்மையான முழு நீள பிவோட் அட்டவணையைப் பெறுகிறோம்:

ரெடி!

தொழில்நுட்ப குறிப்பு: மேக்ரோவை இயக்கும்போது "வழங்குபவர் பதிவு செய்யப்படவில்லை" போன்ற பிழை ஏற்பட்டால், பெரும்பாலும் உங்களிடம் 64-பிட் எக்செல் பதிப்பு அல்லது அலுவலகத்தின் முழுமையற்ற பதிப்பு நிறுவப்பட்டிருக்கும் (அணுகல் இல்லை). நிலைமையைச் சரிசெய்ய, மேக்ரோ குறியீட்டில் உள்ள பகுதியை மாற்றவும்:

	 வழங்குபவர்=Microsoft.Jet.OLEDB.4.0;  

க்கு:

	வழங்குபவர்=Microsoft.ACE.OLEDB.12.0;  

மைக்ரோசாஃப்ட் இணையதளத்தில் இருந்து அணுகல் இருந்து இலவச தரவு செயலாக்க இயந்திரத்தை பதிவிறக்கி நிறுவவும் - Microsoft Access Database Engine 2010 மறுபகிர்வு செய்யக்கூடியது

முறை 3: Excel இன் பழைய பதிப்புகளில் இருந்து PivotTable Wizard ஐ ஒருங்கிணைக்கவும்

இந்த முறை கொஞ்சம் காலாவதியானது, ஆனால் இன்னும் குறிப்பிட வேண்டியது. முறையாகச் சொன்னால், 2003 வரை மற்றும் உட்பட அனைத்து பதிப்புகளிலும், PivotTable Wizard இல் "பல ஒருங்கிணைப்பு வரம்புகளுக்கு ஒரு மையத்தை உருவாக்க" ஒரு விருப்பம் இருந்தது. இருப்பினும், இந்த வழியில் உருவாக்கப்பட்ட அறிக்கை, துரதிர்ஷ்டவசமாக, உண்மையான முழு அளவிலான சுருக்கத்தின் பரிதாபகரமான சாயலாக மட்டுமே இருக்கும் மற்றும் வழக்கமான பைவட் அட்டவணைகளின் பல "சில்லுகளை" ஆதரிக்காது:

அத்தகைய பிவோட்டில், புல பட்டியலில் நெடுவரிசை தலைப்புகள் இல்லை, நெகிழ்வான கட்டமைப்பு அமைப்பு இல்லை, பயன்படுத்தப்படும் செயல்பாடுகளின் தொகுப்பு குறைவாக உள்ளது, பொதுவாக, இவை அனைத்தும் பிவோட் அட்டவணைக்கு மிகவும் ஒத்ததாக இல்லை. ஒருவேளை அதனால்தான், 2007 இல் தொடங்கி, மைக்ரோசாப்ட் பைவட் அட்டவணை அறிக்கைகளை உருவாக்கும் போது நிலையான உரையாடலில் இருந்து இந்த செயல்பாட்டை நீக்கியது. இப்போது இந்த அம்சம் தனிப்பயன் பொத்தான் மூலம் மட்டுமே கிடைக்கும் பிவோட் டேபிள் வழிகாட்டி(பிவோட் டேபிள் வழிகாட்டி), இது, விரும்பினால், விரைவு அணுகல் கருவிப்பட்டியில் சேர்க்கலாம் கோப்பு - விருப்பங்கள் - விரைவு அணுகல் கருவிப்பட்டியைத் தனிப்பயனாக்குங்கள் - அனைத்து கட்டளைகளும் (கோப்பு — விருப்பங்கள் — விரைவு அணுகல் கருவிப்பட்டியை தனிப்பயனாக்குக — அனைத்து கட்டளைகளும்):

சேர்க்கப்பட்ட பொத்தானைக் கிளிக் செய்த பிறகு, வழிகாட்டியின் முதல் கட்டத்தில் பொருத்தமான விருப்பத்தைத் தேர்ந்தெடுக்க வேண்டும்:

பின்னர் அடுத்த சாளரத்தில், ஒவ்வொரு வரம்பையும் தேர்ந்தெடுத்து பொது பட்டியலில் சேர்க்கவும்:

ஆனால், மீண்டும், இது ஒரு முழுமையான சுருக்கம் அல்ல, எனவே அதிலிருந்து அதிகம் எதிர்பார்க்க வேண்டாம். மிகவும் எளிமையான சந்தர்ப்பங்களில் மட்டுமே இந்த விருப்பத்தை நான் பரிந்துரைக்க முடியும்.

• PivotTables மூலம் அறிக்கைகளை உருவாக்குதல்
• PivotTables இல் கணக்கீடுகளை அமைக்கவும்
• மேக்ரோக்கள் என்றால் என்ன, அவற்றை எவ்வாறு பயன்படுத்துவது, VBA குறியீட்டை எங்கு நகலெடுப்பது போன்றவை.
• பல தாள்களிலிருந்து ஒன்று வரை தரவு சேகரிப்பு (PLEX செருகு நிரல்)