ተለዋዋጭ ክልል ከራስ-መጠን ጋር

በ Excel ውስጥ መጠኑ ሊቀየር የሚችል ማለትም የረድፎች (አምዶች) ብዛት ሊጨምር ወይም ሊቀንስ የሚችል ሠንጠረዥ አለህ? የሰንጠረዡ መጠኖች "ተንሳፋፊ" ከሆነ, ይህንን ጊዜ ያለማቋረጥ መከታተል እና ማስተካከል አለብዎት:

• ወደ ጠረጴዛችን የሚያመለክቱ የሪፖርት ቀመሮች ውስጥ አገናኞች
• በእኛ ሠንጠረዥ መሠረት የተገነቡ የምስሶ ሰንጠረዦች የመጀመሪያ ክልሎች
• በእኛ ሠንጠረዥ መሠረት የተገነቡ የመጀመሪያ ደረጃዎች
• የእኛን ሰንጠረዥ እንደ የውሂብ ምንጭ ለሚጠቀሙ ተቆልቋይ ክልሎች

ይህ ሁሉ በድምሩ እንዲሰለቹ አይፈቅድልዎትም 😉

ተለዋዋጭ የ "ላስቲክ" ክልል ለመፍጠር በጣም ምቹ እና ትክክለኛ ይሆናል, ይህም መጠኑን ከትክክለኛው የረድፎች እና የውሂብ አምዶች ብዛት ጋር በራስ-ሰር ያስተካክላል. ይህንን ተግባራዊ ለማድረግ, በርካታ መንገዶች አሉ.

ዘዴ 1. ብልጥ ሰንጠረዥ

የእርስዎን የሕዋስ ክልል ያድምቁ እና ከትር ውስጥ ይምረጡ ቤት - እንደ ሰንጠረዥ ቅርጸት (ቤት - እንደ ሰንጠረዥ ቅርጸት):

እንደ የጎንዮሽ ጉዳት ወደ ጠረጴዛው ላይ የተጨመረው ባለ ጥብጣብ ንድፍ የማይፈልጉ ከሆነ በሚታየው ትር ላይ ማጥፋት ይችላሉ. ገንቢ (ንድፍ). በዚህ መንገድ የተፈጠረ እያንዳንዱ ሠንጠረዥ በትሩ ላይ በተመሳሳይ ቦታ ይበልጥ ምቹ በሆነ ቦታ ሊተካ የሚችል ስም ይቀበላል ገንቢ (ንድፍ) በመስክ ውስጥ የጠረጴዛ ስም (የሠንጠረዥ ስም).

አሁን ተለዋዋጭ አገናኞችን ወደ “ስማርት ጠረጴዛችን” መጠቀም እንችላለን፡-

• ማውጫ 1 - ከአርዕስት ረድፍ (A2: D5) በስተቀር ከጠቅላላው ሰንጠረዥ ጋር ማገናኘት
• ሠንጠረዥ 1[#ሁሉም] - ከጠቅላላው ሰንጠረዥ ጋር ማገናኘት (A1: D5)
• ጠረጴዛ 1 [ጴጥሮስ] - ያለ የመጀመሪያው ሕዋስ ራስጌ (C2፡C5) ወደ ክልል-አምድ ማጣቀሻ
• ሠንጠረዥ 1[#ራስጌዎች] - ከአምዶች ስሞች ጋር ወደ “ራስጌ” አገናኝ (A1: D1)

እንደዚህ ያሉ ማጣቀሻዎች በቀመር ውስጥ ጥሩ ይሰራሉ፣ ለምሳሌ፡-

= SUM (ጠረጴዛ 1 [ሞስኮ]) - ለ "ሞስኮ" አምድ ድምር ስሌት

or

=VPR(F5;ማውጫ 13;0) - በወር ከሴል F5 በሰንጠረዥ ውስጥ ይፈልጉ እና ለሴንት ፒተርስበርግ ድምር ይስጡ (VLOOKUP ምንድን ነው?)

በትሩ ላይ በመምረጥ የምሰሶ ሰንጠረዦችን ሲፈጥሩ እንደዚህ ያሉ ማገናኛዎች በተሳካ ሁኔታ ጥቅም ላይ ሊውሉ ይችላሉ አስገባ - የምሰሶ ሠንጠረዥ (አስገባ - የምሰሶ ሠንጠረዥ) እና የስማርት ሰንጠረዡን ስም እንደ የውሂብ ምንጭ ማስገባት፡-

የእንደዚህ አይነት ሠንጠረዥ ቁርጥራጭ ከመረጡ (ለምሳሌ, የመጀመሪያዎቹ ሁለት ዓምዶች) እና የማንኛውም አይነት ንድፍ ከፈጠሩ, ከዚያም አዲስ መስመሮችን ሲጨምሩ, ወዲያውኑ ወደ ስዕሉ ውስጥ ይጨምራሉ.

ተቆልቋይ ዝርዝሮችን በሚፈጥሩበት ጊዜ ወደ ብልጥ የጠረጴዛ አካላት ቀጥተኛ አገናኞች መጠቀም አይቻልም፣ ነገር ግን ስልታዊ ዘዴን በመጠቀም ይህንን ገደብ በቀላሉ ማግኘት ይችላሉ - ተግባሩን ይጠቀሙ። ችግር (INNDIRECT)ጽሑፉን ወደ ማገናኛ የሚቀይረው፡-

እነዚያ። ወደ ብልጥ ጠረጴዛ የሚወስድ አገናኝ በጽሑፍ ሕብረቁምፊ መልክ (በትዕምርተ ጥቅስ!) ወደ ሙሉ ማገናኛ ይቀየራል፣ እና ተቆልቋዩ ዝርዝሩ በመደበኛነት ይገነዘባል።

ዘዴ 2፡ ተለዋዋጭ የተሰየመ ክልል

ውሂብዎን ወደ ስማርት ሠንጠረዥ መቀየር በሆነ ምክንያት የማይፈለግ ከሆነ፣ ትንሽ የበለጠ የተወሳሰበ፣ ግን የበለጠ ስውር እና ሁለገብ ዘዴ መጠቀም ይችላሉ - በ Excel ውስጥ የእኛን ጠረጴዛ የሚያመለክት ተለዋዋጭ የተሰየመ ክልል ይፍጠሩ። ከዚያ ልክ እንደ ብልጥ ጠረጴዛ ሁኔታ የተፈጠረውን ክልል ስም በማንኛውም ቀመሮች ፣ ሪፖርቶች ፣ ገበታዎች ፣ ወዘተ ውስጥ በነፃ መጠቀም ይችላሉ ። በቀላል ምሳሌ እንጀምር ።

ተግባርከተማዎችን ዝርዝር የሚያመለክት እና አዳዲስ ከተማዎችን ሲጨምሩ ወይም ሲሰርዟቸው መጠናቸው የሚቀንስ ተለዋዋጭ ስም ያለው ክልል ያድርጉ።

በማንኛውም ስሪት ውስጥ የሚገኙ ሁለት አብሮገነብ የ Excel ተግባራት ያስፈልጉናል - POICPOZ (ተዛማጅ) የመጨረሻውን ሕዋስ ለመወሰን, እና INDEX (INDEX) ተለዋዋጭ አገናኝ ለመፍጠር.

MATCH በመጠቀም የመጨረሻውን ሕዋስ ማግኘት

MATCH (የፍለጋ_እሴት፣ ክልል፣ ተዛማጅ_ዓይነት) - የተወሰነ እሴት በክልል (ረድፍ ወይም አምድ) ውስጥ የሚፈልግ እና የተገኘበትን የሕዋስ ተራ ቁጥር የሚመልስ ተግባር። ለምሳሌ፣ ቀመር MATCH ("ማርች"፤ A1፡A5;0) በውጤቱም 4 ቁጥርን ይመልሳል፣ ምክንያቱም "ማርች" የሚለው ቃል በአምድ A1: A5 ውስጥ በአራተኛው ሕዋስ ውስጥ ይገኛል። የመጨረሻው የተግባር ነጋሪ እሴት Match_Type = 0 ማለት ትክክለኛ ግጥሚያ እየፈለግን ነው። ይህ ነጋሪ እሴት ካልተገለጸ, ተግባሩ ወደ መፈለጊያ ሁነታ ይቀየራል በጣም ትንሽ እሴት - ይህ በትክክል በእኛ ድርድር ውስጥ የመጨረሻውን የተያዘ ሕዋስ ለማግኘት በተሳካ ሁኔታ ጥቅም ላይ ሊውል ይችላል.

የተንኮል ዋናው ነገር ቀላል ነው. MATCH ከላይ እስከ ታች ባለው ክልል ውስጥ ያሉ ህዋሶችን ይፈልጋል እና በንድፈ ሀሳብ፣ ለተሰጠው በጣም ቅርብ የሆነ እሴት ሲያገኝ መቆም አለበት። በሠንጠረዡ ውስጥ ካሉት ሁሉ የሚበልጥ ዋጋን እንደ ተፈላጊው ዋጋ ከገለፁት MATCH የሠንጠረዡ መጨረሻ ላይ ይደርሳል፣ ምንም ነገር አላገኘም እና የመጨረሻውን የተሞላው ሕዋስ ተከታታይ ቁጥር ይሰጣል። እና እኛ እንፈልጋለን!

በእኛ ድርድር ውስጥ ቁጥሮች ብቻ ካሉ፣ ቁጥርን እንደ ተፈላጊው እሴት መግለጽ እንችላለን፣ ይህም በሠንጠረዡ ውስጥ ካሉት ከማንኛውም እንደሚበልጥ ግልጽ ነው።

ለዋስትና, ቁጥር 9E + 307 (9 ጊዜ 10 ወደ 307 ኃይል, ማለትም 9 ከ 307 ዜሮዎች ጋር) - Excel በመርህ ደረጃ ሊሰራ የሚችለውን ከፍተኛ ቁጥር መጠቀም ይችላሉ.

በአምዳችን ውስጥ የጽሑፍ እሴቶች ካሉ ፣ በተቻለ መጠን ትልቁን ቁጥር ያህል ፣ ግንባታውን እንደገና ማስገባት ይችላሉ (“i” ፣ 255) - 255 ፊደሎችን “i” የያዘ የጽሑፍ ሕብረቁምፊ - የመጨረሻው ፊደል ፊደል. ኤክሴል በፍለጋ ጊዜ የቁምፊ ኮዶችን በትክክል ስለሚያወዳድር በሠንጠረዡ ውስጥ ያለው ማንኛውም ጽሑፍ በቴክኒካል እንዲህ ካለው ረጅም "አአአአያይ" መስመር "ትንሽ" ይሆናል፡

INDEX በመጠቀም አገናኝ ይፍጠሩ

አሁን በሠንጠረዡ ውስጥ ያለውን የመጨረሻውን ባዶ ያልሆነ አካል አቀማመጥ ካወቅን, ወደ ክልላችን ሁሉ አገናኝ ለመፍጠር ይቀራል. ለዚህም የሚከተለውን ተግባር እንጠቀማለን-

INDEX(ክልል፣ ረድፍ_ቁጥር፣ አምድ_ቁጥር)

የሕዋሱን ይዘቶች ከክልሉ ውስጥ በመስመር እና በአምድ ቁጥር ይሰጣል ፣ ለምሳሌ ፣ ተግባር = INDEX (A1: D5; 3; 4) በእኛ ሠንጠረዥ ውስጥ ከከተሞች እና ከቀደመው ዘዴ ወራት 1240 ይሰጣል - ይዘቱ። ከ 3 ኛ ረድፍ እና 4 ኛ አምድ ማለትም ሴሎች D3. አንድ አምድ ብቻ ካለ ቁጥሩ ሊቀር ይችላል ማለትም ቀመር INDEX(A2:A6;3) በመጨረሻው ቅጽበታዊ ገጽ እይታ ላይ "ሳማራ" ይሰጣል.

እና አንድ ሙሉ በሙሉ ግልፅ ያልሆነ ልዩነት አለ-INDEX ከ = ምልክት በኋላ ወደ ሴል ውስጥ ካልገባ ፣ እንደተለመደው ፣ ግን ከኮሎን በኋላ እንደ ክልሉ የመጨረሻ ክፍል ጥቅም ላይ የሚውል ከሆነ ፣ ከዚያ በኋላ አይሰጥም። የሕዋስ ይዘቶች, ግን አድራሻው! ስለዚህ፣ እንደ $A$2፡INDEX($A$2፡$A$100;3) ያለ ቀመር በውጤቱ ላይ ያለውን ክልል A2፡A4 ማጣቀሻ ይሰጣል።

እና የዝርዝሩን መጨረሻ በተለዋዋጭ ለማወቅ በINDEX ውስጥ የምናስገባው የMATCH ተግባር እዚህ ላይ ነው፡

=$A$2፡INDEX($A$2፡$A$100; ግጥሚያ ( REP (“I”;255);A2:A100))

የተሰየመ ክልል ይፍጠሩ

ሁሉንም ወደ አንድ ሙሉ ማሸግ ይቀራል. ትር ክፈት ፎርሙላ (ፎርሙላዎች) እና ጠቅ ያድርጉ የስም አስተዳዳሪ (ስም አስተዳዳሪ). በሚከፈተው መስኮት ውስጥ አዝራሩን ጠቅ ያድርጉ ፈጠረ (አዲስ), በመስክ ውስጥ የእኛን ክልል ስም እና ቀመር ያስገቡ ርቀት (ዋቢ):

ጠቅ ለማድረግ ይቀራል OK እና የዝግጁ ክልል በማንኛውም ቀመሮች፣ ተቆልቋይ ዝርዝሮች ወይም ገበታዎች ውስጥ ጥቅም ላይ ሊውል ይችላል።

• ሠንጠረዦችን እና የፍለጋ ዋጋዎችን ለማገናኘት የ VLOOKUP ተግባርን በመጠቀም
• በራስ-የሚሞላ ተቆልቋይ ዝርዝር እንዴት መፍጠር እንደሚቻል
• ከፍተኛ መጠን ያለው ውሂብን ለመተንተን የምሰሶ ሠንጠረዥ እንዴት መፍጠር እንደሚቻል