በ Excel ውስጥ ተቆልቋይ ዝርዝር እንዴት እንደሚሰራ

ተቆልቋይ ዝርዝሩ በመረጃ መስራት የበለጠ ምቹ ለማድረግ የሚረዳ እጅግ በጣም ጠቃሚ መሳሪያ ነው። በአንድ ሕዋስ ውስጥ ብዙ እሴቶችን በአንድ ጊዜ እንዲይዝ ያስችለዋል፣ እነሱም አብረው መስራት ይችላሉ፣ ልክ እንደሌሎች። የሚያስፈልገዎትን ለመምረጥ, የቀስት አዶውን ብቻ ጠቅ ያድርጉ, ከዚያ በኋላ የ uXNUMXbuXNUMXbis ዋጋዎች ዝርዝር ይታያል. የተወሰነውን ከመረጡ በኋላ, ሕዋሱ በራስ-ሰር ይሞላል, እና ቀመሮቹ በእሱ ላይ ተመስርተው እንደገና ይሰላሉ.

ኤክሴል ተቆልቋይ ሜኑ ለመፍጠር ብዙ የተለያዩ ዘዴዎችን ያቀርባል፣ እና በተጨማሪ፣ በተለዋዋጭ እንዲያበጁ ይፈቅድልዎታል። እነዚህን ዘዴዎች በበለጠ ዝርዝር እንመርምር.

ዝርዝር የመፍጠር ሂደት

ብቅ ባይ ሜኑ ለማመንጨት በ "ዳታ" - "የውሂብ ማረጋገጫ" መንገድ ላይ የምናሌ ንጥሎችን ጠቅ ያድርጉ። "Parameters" የሚለውን ትር ለማግኘት በሚፈልጉበት ቦታ የንግግር ሳጥን ይከፈታል እና ከዚህ በፊት ካልተከፈተ ጠቅ ያድርጉት. ብዙ ቅንጅቶች አሉት, ነገር ግን "የውሂብ አይነት" ንጥል ለእኛ አስፈላጊ ነው. ከሁሉም ትርጉሞች "ዝርዝር" ትክክለኛ ነው.

በብቅ ባዩ ዝርዝር ውስጥ መረጃ የገባባቸው ዘዴዎች ብዛት በጣም ትልቅ ነው።

1. በተመሳሳዩ የንግግር ሳጥን ውስጥ በተመሳሳይ ትር ላይ በሚገኘው “ምንጭ” መስክ ውስጥ በአንድ ሴሚኮሎን የተለዩ የዝርዝር አካላት ገለልተኛ አመላካች።
   

   

2. የእሴቶች የመጀመሪያ ምልክት። የምንጭ መስኩ አስፈላጊው መረጃ የሚገኝበትን ክልል ይይዛል።
   

   

3. የተሰየመ ክልል በመጥቀስ። የቀደመውን የሚደግም ዘዴ ፣ ግን ክልሉን አስቀድሞ መሰየም ብቻ አስፈላጊ ነው።
   

   

ከእነዚህ ዘዴዎች ውስጥ ማንኛቸውም የተፈለገውን ውጤት ያስገኛሉ. በእውነተኛ ህይወት ሁኔታዎች ውስጥ ተቆልቋይ ዝርዝሮችን የማመንጨት ዘዴዎችን እንመልከት።

ከዝርዝሩ ላይ ባለው መረጃ መሰረት

የተለያዩ የፍራፍሬ ዓይነቶችን የሚገልጽ ጠረጴዛ አለን እንበል።

በዚህ የመረጃ ስብስብ ላይ በመመስረት በተቆልቋይ ምናሌ ውስጥ ዝርዝር ለመፍጠር የሚከተሉትን ማድረግ አለብዎት:

1. ለወደፊቱ ዝርዝር የተያዘውን ሕዋስ ይምረጡ.
2. በሪባን ላይ የውሂብ ትርን ያግኙ። እዚያም "ውሂብን አረጋግጥ" የሚለውን ጠቅ እናደርጋለን.
   

   

3. "የውሂብ አይነት" የሚለውን ንጥል ይፈልጉ እና እሴቱን ወደ "ዝርዝር" ይቀይሩት.
   

   

4. በመስክ ውስጥ "ምንጭ" የሚለውን አማራጭ የሚያመለክት, የሚፈለገውን ክልል ያስገቡ. እባክዎ ዝርዝሩን በሚገለብጡበት ጊዜ መረጃው እንዳይለወጥ ፍፁም ማጣቀሻዎች መገለጽ አለባቸው።
   

በተጨማሪም፣ ከአንድ በላይ ሴል ውስጥ ዝርዝሮችን በአንድ ጊዜ የማፍለቅ ተግባር አለ። ይህንን ለማግኘት, ሁሉንም መምረጥ አለብዎት, እና ቀደም ሲል እንደተገለፀው ተመሳሳይ እርምጃዎችን ያከናውኑ. እንደገና፣ ፍጹም ማጣቀሻዎች መፃፋቸውን ማረጋገጥ አለብዎት። አድራሻው ከአምድ እና ረድፎች ስሞች ቀጥሎ የዶላር ምልክት ከሌለው የ$ ምልክቱ ከአምድ እና ረድፎች ስሞች ቀጥሎ እስኪሆን ድረስ F4 ቁልፍን በመጫን ማከል ያስፈልግዎታል።

በእጅ ውሂብ ቀረጻ ጋር

ከላይ ባለው ሁኔታ, ዝርዝሩ የተፈለገውን ክልል በማጉላት ነው. ይህ ምቹ ዘዴ ነው, ነገር ግን አንዳንድ ጊዜ መረጃውን በእጅ መመዝገብ አስፈላጊ ነው. ይህ በስራ ደብተር ውስጥ የመረጃ ድግግሞሽን ለማስወገድ ያስችላል።

ሁለት ሊሆኑ የሚችሉ አማራጮችን የያዘ ዝርዝር የመፍጠር ሥራ ገጥሞናል እንበል፡ አዎ እና አይሆንም። ተግባሩን ለማከናወን የሚከተሉትን ማድረግ አለብዎት:

1. ለዝርዝሩ ሕዋስ ላይ ጠቅ ያድርጉ።
2. "ውሂብ" ን ይክፈቱ እና እኛ የምናውቀውን "ዳታ ቼክ" የሚለውን ክፍል ያግኙ.
   

   

3. እንደገና "ዝርዝር" የሚለውን አይነት ይምረጡ.
   

   

4. እዚህ "አዎ" የሚለውን ማስገባት ያስፈልግዎታል. አይደለም” እንደ ምንጭ። መረጃን ለመቁጠር ሴሚኮሎን በመጠቀም በእጅ እንደገባ እናያለን።

እሺን ጠቅ ካደረግን በኋላ የሚከተለው ውጤት አለን።

በመቀጠል ፕሮግራሙ በተገቢው ሕዋስ ውስጥ ተቆልቋይ ምናሌን በራስ-ሰር ይፈጥራል. በብቅ ባዩ ዝርዝር ውስጥ ተጠቃሚው እንደ ንጥል ነገር የገለፀው ሁሉም መረጃ። በበርካታ ህዋሶች ውስጥ ዝርዝርን የመፍጠር ህጎች ከቀደምቶቹ ጋር ተመሳሳይ ናቸው ፣ ብቸኛው በስተቀር ሴሚኮሎን በመጠቀም መረጃውን እራስዎ መግለጽ አለብዎት።

የOFFSET ተግባርን በመጠቀም ተቆልቋይ ዝርዝር መፍጠር

ከጥንታዊው ዘዴ በተጨማሪ ተግባሩን መጠቀም ይቻላል ክርክርየተቆልቋይ ምናሌዎችን ለመፍጠር.

ሉህን እንክፈተው።

ለተቆልቋይ ዝርዝሩ ተግባሩን ለመጠቀም የሚከተሉትን ማድረግ አለብዎት:

1. የወደፊቱን ዝርዝር ለማስቀመጥ የሚፈልጉትን ሕዋስ ይምረጡ።
2. "ዳታ" የሚለውን ትር እና "የውሂብ ማረጋገጫ" መስኮትን በቅደም ተከተል ይክፈቱ.
   

   

3. "ዝርዝር" አዘጋጅ. ይህ ከቀደምት ምሳሌዎች ጋር በተመሳሳይ መንገድ ይከናወናል. በመጨረሻም የሚከተለው ቀመር ጥቅም ላይ ይውላል. =OFFET(A$2$;0;0;5)። እንደ ክርክር የሚያገለግሉ ሴሎች በተገለጹበት ቦታ ውስጥ እናስገባዋለን.

ከዚያ ፕሮግራሙ የፍራፍሬዎች ዝርዝር የያዘ ምናሌ ይፈጥራል.

ለዚህ ያለው አገባብ፡-

=OFFSET(ማጣቀሻ፣መስመር_ማካካሻ፣አምድ_ማካካሻ፣[ቁመት]፣[ስፋት])

ይህ ተግባር 5 ክርክሮች እንዳሉት እናያለን። በመጀመሪያ፣ የሚካካሰው የመጀመሪያው ሕዋስ አድራሻ ተሰጥቷል። የሚቀጥሉት ሁለት ነጋሪ እሴቶች ምን ያህል ረድፎች እና አምዶች እንደሚካካሱ ይገልጻሉ። ስለእኛ ስንናገር የቁመት ክርክር 5 ነው ምክንያቱም የዝርዝሩን ቁመት ይወክላል። 

የተቆልቋይ ዝርዝር በ Excel ውስጥ በውሂብ ምትክ (+ የOFFSET ተግባርን በመጠቀም)

በተሰጠው ጉዳይ ላይ ክርክር በቋሚ ክልል ውስጥ የሚገኝ ብቅ ባይ ሜኑ እንዲፈጥር ተፈቅዶለታል። የዚህ ዘዴ ጉዳቱ እቃውን ከጨመረ በኋላ ቀመሩን እራስዎ ማረም ይኖርብዎታል.

አዲስ መረጃ ለማስገባት ድጋፍ ያለው ተለዋዋጭ ዝርዝር ለመፍጠር የሚከተሉትን ማድረግ አለብዎት:

1. የፍላጎት ሕዋስ ይምረጡ።
2. የ "ዳታ" ትርን ዘርጋ እና "የውሂብ ማረጋገጫ" ላይ ጠቅ ያድርጉ.
3. በሚከፈተው መስኮት ውስጥ “ዝርዝር” የሚለውን ንጥል እንደገና ይምረጡ እና የሚከተለውን ቀመር እንደ የውሂብ ምንጭ ይጥቀሱ። =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. እሺ የሚለውን ጠቅ ያድርጉ.

ይህ ተግባር ይዟል COUNTIF, ምን ያህል ሴሎች እንደሚሞሉ ወዲያውኑ ለመወሰን (ምንም እንኳን በጣም ብዙ ቁጥር ያለው ጥቅም ቢኖረውም, እዚህ ለተወሰነ ዓላማ ብቻ እንጽፋለን).

ቀመሩ በመደበኛነት እንዲሠራ, በቀመርው መንገድ ላይ ባዶ ሕዋሳት መኖራቸውን መፈለግ አስፈላጊ ነው. መሆን የለባቸውም።

ተቆልቋይ ዝርዝር ከሌላ ሉህ ወይም ከኤክሴል ፋይል ውሂብ ጋር

ከሌላ ሰነድ ወይም በተመሳሳይ ፋይል ውስጥ ካለው ሉህ መረጃ ማግኘት ከፈለጉ ክላሲክ ዘዴ አይሰራም። ለዚህም, ተግባሩ ጥቅም ላይ ይውላል ችግር, በሌላ ሉህ ውስጥ ወይም በአጠቃላይ - ፋይል ውስጥ ወደሚገኝ ሕዋስ የሚያገናኝ አገናኝ በትክክለኛው ቅርጸት እንዲያስገቡ ያስችልዎታል. የሚከተሉትን ማድረግ ያስፈልግዎታል:

1. ዝርዝሩን የምናስቀምጥበትን ሕዋስ ያግብሩ።
2. አስቀድመን የምናውቀውን መስኮት እንከፍተዋለን. ከዚህ ቀደም ለሌሎች ክልሎች ምንጮችን ባመለከትንበት ቦታ፣ በቅርጸቱ ውስጥ ቀመር ይጠቁማል = ቀጥተኛ ("[List1.xlsx] ሉህ1!$A$1:$A$9"). በተፈጥሮ፣ ከዝርዝር 1 እና ሉህ 1 ይልቅ፣ የእርስዎን መጽሐፍ እና የሉህ ስሞች በቅደም ተከተል ማስገባት ይችላሉ። 

ትኩረት! የፋይሉ ስም በካሬ ቅንፎች ውስጥ ተሰጥቷል. በዚህ አጋጣሚ ኤክሴል አሁን የተዘጋውን ፋይል እንደ የመረጃ ምንጭ መጠቀም አይችልም።

በተጨማሪም የፋይሉ ስም ራሱ ትርጉም ያለው መሆኑን ልብ ሊባል የሚገባው አስፈላጊው ሰነድ ዝርዝሩ ውስጥ በሚገባበት አቃፊ ውስጥ የሚገኝ ከሆነ ብቻ ነው. ካልሆነ, የዚህን ሰነድ አድራሻ ሙሉ በሙሉ መግለጽ አለብዎት.

ጥገኛ ተቆልቋይዎችን መፍጠር

ጥገኛ ዝርዝር ይዘቱ በሌላ ዝርዝር ውስጥ በተጠቃሚው ምርጫ የተነካ ነው። ከፊት ለፊታችን የተከፈተ ጠረጴዚ አለን እንበልና እያንዳንዳቸው ስም የተሰጣቸው ሶስት ክልሎችን የያዘ።

በሌላ ዝርዝር ውስጥ በተመረጠው አማራጭ ውጤታቸው የሚነካ ዝርዝሮችን ለመፍጠር እነዚህን ደረጃዎች መከተል ያስፈልግዎታል።

1. በክልል ስሞች 1 ኛ ዝርዝር ይፍጠሩ።
   

   

2. በምንጭ መግቢያ ነጥብ, አስፈላጊዎቹ አመልካቾች አንድ በአንድ ይደምቃሉ.
   

   

3. ሰውዬው በመረጠው ተክል ዓይነት ላይ በመመስረት 2 ኛ ዝርዝር ይፍጠሩ. በአማራጭ, በመጀመሪያው ዝርዝር ውስጥ ዛፎችን ከገለጹ, በሁለተኛው ዝርዝር ውስጥ ያለው መረጃ "ኦክ, ሆርንቢም, ደረትን" እና ከዚያ በላይ ይሆናል. በመረጃው ምንጭ ግብዓት ቦታ ላይ ቀመሩን መፃፍ አስፈላጊ ነው = ቀጥተኛ (E3) E3 - የክልሉን ስም የያዘ ሕዋስ 1.=INDIRECT(E3)። E3 - የዝርዝሩ ስም ያለው ሕዋስ 1.

አሁን ሁሉም ነገር ዝግጁ ነው.

ከተቆልቋይ ዝርዝር ውስጥ ብዙ እሴቶችን እንዴት መምረጥ ይቻላል?

አንዳንድ ጊዜ ለአንድ እሴት ብቻ ምርጫን መስጠት አይቻልም, ስለዚህ ከአንድ በላይ መመረጥ አለበት. ከዚያ ወደ ገጹ ኮድ ማክሮ ማከል ያስፈልግዎታል። የቁልፍ ጥምርን Alt + F11 በመጠቀም Visual Basic Editor ይከፍታል። እና ኮዱ እዚያ ገብቷል።

የግል ንዑስ ሉህ_ለውጥ (ByVal Target እንደ ክልል)

    ስህተት ላይ ቀጣይ ከቆመበት ቀጥል

    ካልተገናኘ (ዒላማ፣ ክልል(«Е2፡Е9»)) ምንም አይደለም እና ኢላማ።ሴሎች ቁጥር = 1 ከዚያም

        መተግበሪያ.EnableEvents = ሐሰት

        Len ከሆነ (ታርጌት ኦፍሴት (0, 1)) = 0 ከዚያም

            ዒላማ.Offset (0, 1) = ዒላማ

        ያለዚያ

            ዒላማ.መጨረሻ (xlToRight) .Offset (0, 1) = ዒላማ

        ያቁሙ

        ዒላማ።ይዘቶችን አጽዳ

        መተግበሪያ.EnableEvents = እውነት

    ያቁሙ

ጨርስ ንዑስ 

የሴሎች ይዘት ከዚህ በታች እንዲታይ የሚከተለውን ኮድ በአርታዒው ውስጥ እናስገባለን።

የግል ንዑስ ሉህ_ለውጥ (ByVal Target እንደ ክልል)

    ስህተት ላይ ቀጣይ ከቆመበት ቀጥል

    ካልተገናኘ (ዒላማ፣ ክልል(«Н2፡К2»)) ምንም አይደለም እና ኢላማ።ሴሎች።Count = 1 ከዚያም

        መተግበሪያ.EnableEvents = ሐሰት

        Len ከሆነ (ታርጌት ኦፍሴት (1, 0)) = 0 ከዚያም

            ዒላማ.Offset (1, 0) = ዒላማ

        ያለዚያ

            ዒላማ.መጨረሻ (xlDown) .Offset (1, 0) = ዒላማ

        ያቁሙ

        ዒላማ።ይዘቶችን አጽዳ

        መተግበሪያ.EnableEvents = እውነት

    ያቁሙ

ጨርስ ንዑስ

እና በመጨረሻም, ይህ ኮድ በአንድ ሕዋስ ውስጥ ለመጻፍ ጥቅም ላይ ይውላል.

የግል ንዑስ ሉህ_ለውጥ (ByVal Target እንደ ክልል)

    ስህተት ላይ ቀጣይ ከቆመበት ቀጥል

    ካልተገናኘ (ዒላማ፣ ክልል(«C2፡C5»)) ምንም አይደለም እና ኢላማ።ሴሎች ቁጥር = 1 ከዚያም

        መተግበሪያ.EnableEvents = ሐሰት

        newVal = ዒላማ

        መተግበሪያ.ቀልብስ

        oldval = ዒላማ

        Len (oldval) <> 0 እና oldval <> newVal ከሆነ

            ዒላማ = ዒላማ & «,» እና newVal

        ያለዚያ

            ዒላማ = newVal

        ያቁሙ

        Len (newVal) = 0 ከሆነ ታርጌት.ይዘትን አጽዳ

        መተግበሪያ.EnableEvents = እውነት

    ያቁሙ

ጨርስ ንዑስ

ክልሎች አርትዖት ሊደረጉ የሚችሉ ናቸው።

በፍለጋ ተቆልቋይ ዝርዝር እንዴት እንደሚሰራ?

በዚህ ሁኔታ, መጀመሪያ ላይ የተለየ ዓይነት ዝርዝር መጠቀም አለብዎት. የ "ገንቢ" ትር ይከፈታል, ከዚያ በኋላ በ "አስገባ" - "ActiveX" ኤለመንት ላይ ጠቅ ማድረግ ወይም (ስክሪኑ ከተነካ) መታ ማድረግ ያስፈልግዎታል. ጥምር ሳጥን አለው። ይህንን ዝርዝር ለመሳል ይጠየቃሉ, ከዚያ በኋላ ወደ ሰነዱ ይጨመራል.

በተጨማሪ፣ በንብረቶች በኩል ነው የተዋቀረው፣ በ ListFillRange አማራጭ ውስጥ ክልል በተገለፀበት። በተጠቃሚ የተገለጸው እሴት የሚታይበት ሕዋስ የሚዋቀረው LinkedCell አማራጭን በመጠቀም ነው። በመቀጠል, መርሃግብሩ ሊኖሩ የሚችሉ እሴቶችን በራስ-ሰር ስለሚጠቁም የመጀመሪያዎቹን ቁምፊዎች ብቻ መጻፍ ያስፈልግዎታል.

ተቆልቋይ ዝርዝር በራስ ሰር የውሂብ ምትክ

ወደ ክልሉ ከተጨመሩ በኋላ መረጃው በራስ-ሰር የሚተካበት ተግባርም አለ። ይህን ለማድረግ ቀላል ነው:

1. ለወደፊቱ ዝርዝር የሴሎች ስብስብ ይፍጠሩ. በእኛ ሁኔታ, ይህ የቀለም ስብስብ ነው. እኛ እንመርጣለን.
   

   

2. በመቀጠልም እንደ ሠንጠረዥ መቅረጽ ያስፈልገዋል. ተመሳሳይ ስም ያለው አዝራር ጠቅ ማድረግ እና የጠረጴዛውን ዘይቤ መምረጥ ያስፈልግዎታል.
   

   

   

በመቀጠል "እሺ" የሚለውን ቁልፍ በመጫን ይህንን ክልል ማረጋገጥ ያስፈልግዎታል.

የተገኘውን ሰንጠረዥ እንመርጣለን እና በአምድ A አናት ላይ ባለው የግቤት መስክ በኩል ስም እንሰጠዋለን.

ያ ብቻ ነው ፣ ጠረጴዛ አለ ፣ እና ለተቆልቋይ ዝርዝር መሠረት ሆኖ ሊያገለግል ይችላል ፣ ለዚህም ያስፈልግዎታል

1. ዝርዝሩ የሚገኝበትን ሕዋስ ይምረጡ።
2. የውሂብ ማረጋገጫ ንግግሩን ይክፈቱ።
   

   

3. የውሂብ አይነትን ወደ "ዝርዝር" እናስቀምጣለን, እና እንደ እሴቶች የሠንጠረዡን ስም በ = ምልክት እንሰጣለን.
   

   

   

ሁሉም ነገር, ሴሉ ዝግጁ ነው, እና የቀለሞቹ ስሞች በእሱ ውስጥ ይታያሉ, በመጀመሪያ እንደፈለግን. አሁን ከመጨረሻው በኋላ ትንሽ ዝቅ ብሎ በሚገኝ ሕዋስ ውስጥ በመፃፍ በቀላሉ አዳዲስ ቦታዎችን ማከል ይችላሉ።

ይህ የሰንጠረዡ ጥቅም ነው, አዲስ ውሂብ ሲጨመር ክልሉ በራስ-ሰር ይጨምራል. በዚህ መሠረት ይህ ዝርዝርን ለመጨመር በጣም አመቺው መንገድ ነው.

ተቆልቋይ ዝርዝሩን እንዴት መቅዳት ይቻላል?

ለመቅዳት የቁልፍ ጥምርን መጠቀም በቂ ነው Ctrl + C እና Ctrl + V. ስለዚህ ተቆልቋይ ዝርዝሩ ከቅርጸቱ ጋር አብሮ ይገለበጣል. ቅርጸቱን ለማስወገድ ልዩ መለጠፍን መጠቀም ያስፈልግዎታል (በአውድ ምናሌው ውስጥ ይህ አማራጭ ዝርዝሩን ከተገለበጠ በኋላ ይታያል) ፣ “በእሴቶች ላይ ያሉ ሁኔታዎች” አማራጭ የሚዘጋጅበት።

ተቆልቋይ ዝርዝር ያላቸውን ሁሉንም ሕዋሳት ይምረጡ

ይህንን ተግባር ለማከናወን በ "ፈልግ እና ምረጥ" ቡድን ውስጥ "የሴሎች ቡድን ምረጥ" የሚለውን ተግባር መጠቀም አለብህ.

ከዚያ በኋላ የንግግር ሳጥን ይከፈታል, በ "መረጃ ማረጋገጫ" ምናሌ ውስጥ "ሁሉም" እና "እነዚህ ተመሳሳይ" የሚለውን ንጥል መምረጥ አለብዎት. የመጀመሪያው ንጥል ሁሉንም ዝርዝሮች ይመርጣል, ሁለተኛው ደግሞ ከተወሰኑት ጋር ተመሳሳይ የሆኑትን ብቻ ይመርጣል.