స్వీయ-పరిమాణంతో డైనమిక్ పరిధి

మీరు ఎక్సెల్‌లో పరిమాణాన్ని మార్చగల డేటాతో పట్టికలను కలిగి ఉన్నారా, అనగా వరుసల సంఖ్య (నిలువు వరుసలు) పని సమయంలో పెంచవచ్చు లేదా తగ్గించవచ్చు? టేబుల్ పరిమాణాలు “ఫ్లోట్” అయితే, మీరు ఈ క్షణాన్ని నిరంతరం పర్యవేక్షించాలి మరియు దాన్ని సరిదిద్దాలి:

• మా పట్టికను సూచించే నివేదిక సూత్రాలలో లింక్‌లు
• మా పట్టిక ప్రకారం నిర్మించబడిన పివోట్ పట్టికల ప్రారంభ పరిధులు
• మా పట్టిక ప్రకారం నిర్మించబడిన చార్ట్‌ల ప్రారంభ శ్రేణులు
• మా పట్టికను డేటా సోర్స్‌గా ఉపయోగించే డ్రాప్‌డౌన్‌ల పరిధులు

మొత్తానికి ఇవన్నీ మీకు విసుగు తెప్పించవు 😉

డైనమిక్ "రబ్బరు" శ్రేణిని సృష్టించడం మరింత సౌకర్యవంతంగా మరియు సరైనదిగా ఉంటుంది, ఇది డేటా యొక్క వరుసలు మరియు నిలువు వరుసల సంఖ్యకు స్వయంచాలకంగా పరిమాణంలో సర్దుబాటు చేస్తుంది. దీన్ని అమలు చేయడానికి, అనేక మార్గాలు ఉన్నాయి.

విధానం 1. స్మార్ట్ టేబుల్

మీ సెల్‌ల పరిధిని హైలైట్ చేసి, ట్యాబ్ నుండి ఎంచుకోండి హోమ్ - టేబుల్‌గా ఫార్మాట్ చేయండి (హోమ్ - టేబుల్‌గా ఫార్మాట్ చేయండి):

సైడ్ ఎఫెక్ట్‌గా టేబుల్‌కి జోడించిన చారల డిజైన్ మీకు అవసరం లేకపోతే, మీరు కనిపించే ట్యాబ్‌లో దాన్ని ఆఫ్ చేయవచ్చు కన్స్ట్రక్టర్ (డిజైన్). ఈ విధంగా సృష్టించబడిన ప్రతి పట్టిక ట్యాబ్‌లో అదే స్థలంలో మరింత అనుకూలమైన దానితో భర్తీ చేయగల పేరును పొందుతుంది కన్స్ట్రక్టర్ (డిజైన్) రంగంలో పట్టిక పేరు (టేబుల్ పేరు).

ఇప్పుడు మనం మన “స్మార్ట్ టేబుల్”కి డైనమిక్ లింక్‌లను ఉపయోగించవచ్చు:

• పట్టిక 11 - హెడర్ అడ్డు వరుస మినహా మొత్తం పట్టికకు లింక్ చేయండి (A2:D5)
• పట్టిక 1[#అన్నీ] - మొత్తం పట్టికకు లింక్ (A1:D5)
• టేబుల్ 1[పీటర్] - మొదటి సెల్-హెడర్ లేని పరిధి-నిలువు వరుసకు సూచన (C2:C5)
• పట్టిక 1[#హెడర్లు] - నిలువు వరుసల పేర్లతో "హెడర్"కి లింక్ చేయండి (A1:D1)

ఇటువంటి సూచనలు సూత్రాలలో గొప్పగా పని చేస్తాయి, ఉదాహరణకు:

= SUM (టేబుల్ 1[మాస్కో]) - కాలమ్ "మాస్కో" కోసం మొత్తం లెక్కింపు

or

=VPR(F5;పట్టిక 11;3;0) – సెల్ F5 నుండి నెల కోసం పట్టికలో శోధించండి మరియు దాని కోసం సెయింట్ పీటర్స్‌బర్గ్ మొత్తాన్ని జారీ చేయండి (VLOOKUP అంటే ఏమిటి?)

ట్యాబ్‌పై ఎంచుకోవడం ద్వారా పివోట్ పట్టికలను సృష్టించేటప్పుడు ఇటువంటి లింక్‌లు విజయవంతంగా ఉపయోగించబడతాయి చొప్పించు – పివట్ పట్టిక (చొప్పించు – పివట్ పట్టిక) మరియు స్మార్ట్ టేబుల్ పేరును డేటా సోర్స్‌గా నమోదు చేయడం:

మీరు అటువంటి పట్టిక యొక్క భాగాన్ని ఎంచుకుంటే (ఉదాహరణకు, మొదటి రెండు నిలువు వరుసలు) మరియు ఏదైనా రకమైన రేఖాచిత్రాన్ని సృష్టించినట్లయితే, కొత్త పంక్తులను జోడించేటప్పుడు, అవి స్వయంచాలకంగా రేఖాచిత్రానికి జోడించబడతాయి.

డ్రాప్-డౌన్ జాబితాలను సృష్టించేటప్పుడు, స్మార్ట్ టేబుల్ ఎలిమెంట్‌లకు డైరెక్ట్ లింక్‌లు ఉపయోగించబడవు, కానీ మీరు వ్యూహాత్మక ట్రిక్ ఉపయోగించి ఈ పరిమితిని సులభంగా అధిగమించవచ్చు - ఫంక్షన్‌ని ఉపయోగించండి పరోక్ష (పరోక్ష), ఇది వచనాన్ని లింక్‌గా మారుస్తుంది:

ఆ. టెక్స్ట్ స్ట్రింగ్ (కొటేషన్ మార్కులలో!) రూపంలో స్మార్ట్ టేబుల్‌కి లింక్ పూర్తి స్థాయి లింక్‌గా మారుతుంది మరియు డ్రాప్-డౌన్ జాబితా సాధారణంగా దానిని గ్రహిస్తుంది.

విధానం 2: డైనమిక్ పేరు గల పరిధి

కొన్ని కారణాల వల్ల మీ డేటాను స్మార్ట్ టేబుల్‌గా మార్చడం అవాంఛనీయమైతే, మీరు కొంచెం సంక్లిష్టమైన, కానీ చాలా సూక్ష్మమైన మరియు బహుముఖ పద్ధతిని ఉపయోగించవచ్చు - మా పట్టికను సూచించే Excelలో డైనమిక్ పేరుతో పరిధిని సృష్టించండి. ఆపై, స్మార్ట్ టేబుల్ విషయంలో వలె, మీరు సృష్టించిన పరిధి పేరును ఏదైనా సూత్రాలు, నివేదికలు, చార్ట్‌లు మొదలైనవాటిలో ఉచితంగా ఉపయోగించవచ్చు. ఒక సాధారణ ఉదాహరణతో ప్రారంభిద్దాం:

టాస్క్: కొత్త నగరాలను జోడించేటప్పుడు లేదా వాటిని తొలగించేటప్పుడు నగరాల జాబితాను సూచించే డైనమిక్ పేరు గల పరిధిని రూపొందించండి మరియు స్వయంచాలకంగా విస్తరించి, పరిమాణం తగ్గిపోతుంది.

ఏదైనా సంస్కరణలో అందుబాటులో ఉన్న రెండు అంతర్నిర్మిత Excel ఫంక్షన్‌లు మాకు అవసరం - POICPOZ (మ్యాచ్) పరిధి యొక్క చివరి గడిని నిర్ణయించడానికి, మరియు INDEX (ఇండెక్స్) డైనమిక్ లింక్‌ని సృష్టించడానికి.

MATCHని ఉపయోగించి చివరి గడిని కనుగొనడం

MATCH(lookup_value, range, match_type) – ఒక పరిధిలో (అడ్డు వరుస లేదా నిలువు వరుస) ఇచ్చిన విలువ కోసం శోధించే ఫంక్షన్ మరియు అది కనుగొనబడిన సెల్ యొక్క ఆర్డినల్ సంఖ్యను అందిస్తుంది. ఉదాహరణకు, ఫార్ములా MATCH(“మార్చ్”;A1:A5;0) ఫలితంగా సంఖ్య 4ని అందిస్తుంది, ఎందుకంటే “మార్చ్” అనే పదం A1:A5 కాలమ్‌లోని నాల్గవ సెల్‌లో ఉంది. చివరి ఫంక్షన్ ఆర్గ్యుమెంట్ Match_Type = 0 అంటే మనం ఖచ్చితమైన సరిపోలిక కోసం చూస్తున్నామని అర్థం. ఈ ఆర్గ్యుమెంట్ పేర్కొనబడకపోతే, ఫంక్షన్ సమీప చిన్న విలువ కోసం శోధన మోడ్‌కు మారుతుంది - ఇది మా శ్రేణిలో చివరిగా ఆక్రమించబడిన సెల్‌ను కనుగొనడానికి విజయవంతంగా ఉపయోగించబడుతుంది.

ట్రిక్ యొక్క సారాంశం సులభం. పై నుండి క్రిందికి పరిధిలోని సెల్‌ల కోసం MATCH శోధనలు మరియు సిద్ధాంతపరంగా, అందించిన దానికి సమీప చిన్న విలువను కనుగొన్నప్పుడు ఆపివేయాలి. మీరు పట్టికలో అందుబాటులో ఉన్న వాటి కంటే స్పష్టంగా ఎక్కువ విలువను కోరుకున్న విలువగా పేర్కొంటే, అప్పుడు MATCH పట్టిక చివరకి చేరుకుంటుంది, ఏమీ కనుగొనలేదు మరియు చివరిగా పూరించబడిన సెల్ యొక్క క్రమ సంఖ్యను ఇస్తుంది. మరియు మాకు ఇది అవసరం!

మా శ్రేణిలో సంఖ్యలు మాత్రమే ఉన్నట్లయితే, మేము కావలసిన విలువగా ఒక సంఖ్యను పేర్కొనవచ్చు, ఇది పట్టికలో ఉన్న వాటి కంటే స్పష్టంగా ఎక్కువగా ఉంటుంది:

హామీ కోసం, మీరు 9E + 307 సంఖ్యను ఉపయోగించవచ్చు (9 యొక్క శక్తికి 10 సార్లు 307, అంటే 9 సున్నాలతో 307) - Excel సూత్రప్రాయంగా పని చేయగల గరిష్ట సంఖ్య.

మా కాలమ్‌లో టెక్స్ట్ విలువలు ఉంటే, సాధ్యమయ్యే అతిపెద్ద సంఖ్యకు సమానమైనదిగా, మీరు REPEAT ("i", 255) నిర్మాణాన్ని చొప్పించవచ్చు - 255 అక్షరాలతో కూడిన టెక్స్ట్ స్ట్రింగ్ "i" - యొక్క చివరి అక్షరం వర్ణమాల. ఎక్సెల్ వాస్తవానికి శోధించేటప్పుడు అక్షర సంకేతాలను పోలుస్తుంది కాబట్టి, మా పట్టికలోని ఏదైనా వచనం సాంకేతికంగా ఇంత పొడవైన “కంటే“ చిన్నది ”అవుతుంది“

INDEX ఉపయోగించి లింక్‌ను రూపొందించండి

పట్టికలో చివరిగా ఖాళీ కాని మూలకం యొక్క స్థానం ఇప్పుడు మనకు తెలుసు, ఇది మా మొత్తం పరిధికి లింక్‌ను రూపొందించడానికి మిగిలి ఉంది. దీని కోసం మేము ఫంక్షన్‌ను ఉపయోగిస్తాము:

INDEX(పరిధి; row_num; column_num)

ఇది సెల్ యొక్క కంటెంట్‌లను అడ్డు వరుస మరియు నిలువు వరుస సంఖ్యల వారీగా అందిస్తుంది, ఉదాహరణకు, మునుపటి పద్ధతి నుండి నగరాలు మరియు నెలలతో మా పట్టికలోని ఫంక్షన్ =INDEX(A1:D5;3;4) 1240 ఇస్తుంది – కంటెంట్ 3వ అడ్డు వరుస మరియు 4వ నిలువు వరుస నుండి, అంటే సెల్స్ D3. ఒక నిలువు వరుస మాత్రమే ఉన్నట్లయితే, దాని సంఖ్యను విస్మరించవచ్చు, అంటే ఫార్ములా INDEX(A2:A6;3) చివరి స్క్రీన్‌షాట్‌లో “సమారా” ఇస్తుంది.

మరియు పూర్తిగా స్పష్టమైన సూక్ష్మభేదం ఒకటి లేదు: INDEX సాధారణంగా = గుర్తు తర్వాత సెల్‌లోకి ప్రవేశించకుండా, పెద్దప్రేగు తర్వాత పరిధికి సూచన యొక్క చివరి భాగం వలె ఉపయోగించబడితే, అది ఇకపై ఇవ్వదు సెల్ యొక్క కంటెంట్‌లు, కానీ దాని చిరునామా! అందువలన, $A$2:INDEX($A$2:$A$100;3) వంటి ఫార్ములా అవుట్‌పుట్ వద్ద A2:A4 పరిధికి సూచనను ఇస్తుంది.

మరియు ఇక్కడే MATCH ఫంక్షన్ వస్తుంది, మేము జాబితా ముగింపును డైనమిక్‌గా నిర్ణయించడానికి INDEX లోపల ఇన్సర్ట్ చేస్తాము:

=$A$2:INDEX($A$2:$A$100; మ్యాచ్(REP("I";255);A2:A100))

పేరున్న పరిధిని సృష్టించండి

అన్నింటినీ ఒకే మొత్తంలో ప్యాక్ చేయడానికి ఇది మిగిలి ఉంది. ట్యాబ్‌ను తెరవండి ఫార్ములా (సూత్రాలు) మరియు క్లిక్ చేయండి పేరు మేనేజర్ (పేరు మేనేజర్). తెరుచుకునే విండోలో, బటన్ను క్లిక్ చేయండి సృష్టించు (కొత్త), ఫీల్డ్‌లో మా పరిధి పేరు మరియు సూత్రాన్ని నమోదు చేయండి రేంజ్ (సూచన):

ఇది క్లిక్ చేయడానికి మిగిలి ఉంది OK మరియు సిద్ధంగా ఉన్న శ్రేణిని ఏదైనా సూత్రాలు, డ్రాప్-డౌన్ జాబితాలు లేదా చార్ట్‌లలో ఉపయోగించవచ్చు.

• పట్టికలు మరియు శోధన విలువలను లింక్ చేయడానికి VLOOKUP ఫంక్షన్‌ని ఉపయోగించడం
• ఆటో-పాపులేటింగ్ డ్రాప్‌డౌన్ జాబితాను ఎలా సృష్టించాలి
• పెద్ద మొత్తంలో డేటాను విశ్లేషించడానికి పివోట్ పట్టికను ఎలా సృష్టించాలి