የጅምላ ጽሑፍን በቀመር መተካት

በተለያዩ የ"ቀጥታ" ደረጃዎች የመጀመሪያ ውሂብ የተጻፈበት ዝርዝር አለህ እንበል - ለምሳሌ አድራሻዎች ወይም የኩባንያ ስሞች፡-

ተመሳሳይ ከተማ ወይም ኩባንያ እዚህ በሞትሊ ልዩነቶች ውስጥ እንደሚገኝ በግልጽ ይታያል, ይህም በግልጽ, ለወደፊቱ ከእነዚህ ጠረጴዛዎች ጋር ሲሰራ ብዙ ችግሮች ይፈጥራል. እና ትንሽ ካሰቡ, ከሌሎች አካባቢዎች ተመሳሳይ ስራዎችን ብዙ ምሳሌዎችን ማግኘት ይችላሉ.

አሁን እንደዚህ አይነት ጠማማ መረጃ በመደበኛነት ወደ አንተ እንደሚመጣ አስብ ማለትም ይህ የአንድ ጊዜ "በእጅ አስተካክል, ረሳው" ታሪክ ሳይሆን በመደበኛነት እና በሴሎች ብዛት ውስጥ ያለ ችግር ነው.

ምን ይደረግ? “ፈልግ እና ተካ” በሚለው ሳጥን ውስጥ ወይም ጠቅ በማድረግ 100500 ጊዜ ጠማማ ጽሁፍን በእጅ አይተኩት። መቆጣጠሪያ+H?

በእንደዚህ ዓይነት ሁኔታ ውስጥ ወደ አእምሮ የሚመጣው የመጀመሪያው ነገር የተሳሳቱ እና ትክክለኛ አማራጮችን በማዛመድ አስቀድሞ በተዘጋጀው የማጣቀሻ መጽሐፍ መሠረት በጅምላ መተካት ነው - እንደዚህ

እንደ አለመታደል ሆኖ ፣ የዚህ ዓይነቱ ተግባር ግልፅ መስፋፋት ፣ ማይክሮሶፍት ኤክሴል እሱን ለመፍታት ቀላል አብሮገነብ ዘዴዎች የሉትም። ለመጀመር፣ በVBA ወይም Power Query ውስጥ “ከባድ መድፍ”ን በማክሮዎች መልክ ሳያካትት ይህንን በቀመር እንዴት እንደምናደርግ እንወቅ።

ጉዳይ 1. የጅምላ ሙሉ መተካት

በአንጻራዊ ቀላል ጉዳይ እንጀምር - የድሮውን ጠማማ ጽሑፍ በአዲስ መተካት የሚያስፈልግበት ሁኔታ። ሙሉ.

ሁለት ጠረጴዛዎች አሉን እንበል፡-

በመጀመሪያው ላይ - የኩባንያዎች ኦሪጅናል የተለያዩ ስሞች. በሁለተኛው - የደብዳቤ ማመሳከሪያ መጽሐፍ. በመጀመሪያው ሠንጠረዥ ውስጥ በኩባንያው ስም ውስጥ ከአምዱ ውስጥ ማንኛውንም ቃል ካገኘን ማግኘት, ከዚያ ይህን ጠማማ ስም ከትክክለኛው ጋር ሙሉ በሙሉ መተካት ያስፈልግዎታል - ከአምዱ ምትክ ሁለተኛ መፈለጊያ ጠረጴዛ.

ለመመቻቸት፡-

• ሁለቱም ሠንጠረዦች የቁልፍ ሰሌዳ አቋራጭን በመጠቀም ወደ ተለዋዋጭ ("ስማርት") ይቀየራሉ መቆጣጠሪያ+T ወይም ቡድን አስገባ - ጠረጴዛ (አስገባ - ሠንጠረዥ).
  
• በሚታየው ትር ላይ ግንበኛ (ዲዛይን) የመጀመሪያ ሰንጠረዥ ተሰይሟል መረጃእና ሁለተኛው የማጣቀሻ ሰንጠረዥ - መቀየር.

የቀመሩን አመክንዮ ለማብራራት ከሩቅ ትንሽ እንሂድ።

የመጀመሪያውን ኩባንያ ከሴል A2 እንደ ምሳሌ ወስደን ስለ ቀሪዎቹ ኩባንያዎች በጊዜያዊነት በመርሳት, ከአምዱ ውስጥ የትኛውን አማራጭ ለመወሰን እንሞክር. ማግኘት እዚያ ይገናኛል. ይህንን ለማድረግ በሉሁ ነፃ ክፍል ውስጥ ማንኛውንም ባዶ ሕዋስ ይምረጡ እና እዚያ ውስጥ ተግባሩን ያስገቡ ማግኘት (አግኝ):

ይህ ተግባር የተሰጠው ንኡስ ሕብረቁምፊ መካተቱን ይወስናል (የመጀመሪያው ነጋሪ እሴት ከአምድ ውስጥ ያሉት ሁሉም ዋጋዎች ናቸው ማግኘት) ወደ ምንጭ ጽሑፍ (ከመረጃ ሠንጠረዥ ውስጥ የመጀመሪያው ኩባንያ) እና ጽሑፉ የተገኘበትን የቁምፊውን መደበኛ ቁጥር ወይም ንዑስ ሕብረቁምፊው ካልተገኘ ስህተት ማውጣት አለበት።

እዚህ ያለው ዘዴ አንድ ሳይሆን ብዙ እሴቶችን እንደ መጀመሪያው ነጋሪ እሴት የገለጽነው ስለሆነ ይህ ተግባር እንዲሁ አንድ እሴት ሳይሆን የ 3 አካላት ስብስብ ይመለሳል። ተለዋዋጭ ድርድሮችን የሚደግፍ የቅርብ ጊዜው የ Office 365 ስሪት ከሌለህ ይህን ቀመር ከገባህ ​​በኋላ ጠቅ አድርግ አስገባ ይህንን ድርድር በሉሁ ላይ ያያሉ፡-

የቀደሙ የ Excel ስሪቶች ካሉዎት ከዚያ ጠቅ ካደረጉ በኋላ አስገባ ከውጤት ድርድር የመጀመሪያውን እሴት ብቻ እናያለን ማለትም ስህተት #VALUE! (#VALUE!).

መፍራት የለብህም 🙂 እንደ እውነቱ ከሆነ የእኛ ቀመር ይሠራል እና የገባውን ተግባር በቀመር አሞሌ ውስጥ ከመረጡ እና ቁልፉን ከተጫኑ አሁንም ውጤቱን በሙሉ ማየት ይችላሉ. F9(ልክ መጫንን አይርሱ መኮንንወደ ቀመር ለመመለስ፡-

የተገኘው የውጤቶች ስብስብ ማለት በዋናው ጠማማ ኩባንያ ስም ማለት ነው። (ጂኬ ሞሮዝኮ ኦኤኦ) በአንድ አምድ ውስጥ ካሉ ሁሉም እሴቶች ማግኘት ሁለተኛውን ብቻ አገኘ (ሞሮዝኮ), እና በተከታታይ ከ 4 ኛ ቁምፊ ጀምሮ.

አሁን ወደ ቀመራችን አንድ ተግባር እንጨምር VIEW(ተመልከት):

ይህ ተግባር ሶስት ነጋሪ እሴቶች አሉት

1. የሚፈለግ እሴት - ማንኛውንም በቂ መጠን ያለው ቁጥር መጠቀም ይችላሉ (ዋናው ነገር ከምንጩ ውሂብ ውስጥ ከማንኛውም ጽሑፍ ርዝመት ይበልጣል)
   
2. ታይቷል_ቬክተር - የሚፈለገውን ዋጋ የምንፈልግበት ክልል ወይም ድርድር። ከዚህ ቀደም የተዋወቀው ተግባር ይኸውና ማግኘት፣ ድርድር የሚመልስ {#VALUE!:4:#VALUE!}
   
3. ቬክተር_ውጤቶች - የሚፈለገው እሴት በተዛማጅ ሕዋስ ውስጥ ከተገኘ እሴቱን ለመመለስ የምንፈልገው ክልል. ከአምዱ ትክክለኛ ስሞች እዚህ አሉ። ምትክ የእኛ የማጣቀሻ ጠረጴዛ.
   

ዋናው እና ግልጽ ያልሆነ ባህሪው ተግባሩ ነው VIEW ትክክለኛ ግጥሚያ ከሌለ ሁል ጊዜ የቅርብ ትንሹን (የቀደመው) እሴትን ይፈልጉ. ስለዚህ ማንኛውንም ከባድ ቁጥር (ለምሳሌ 9999) እንደ ተፈላጊው እሴት በመግለጽ እናስገድዳለን። VIEW ህዋሱን ከቅርቡ ትንሹ ቁጥር (4) ጋር በድርድር ውስጥ ያግኙት {#VALUE!:4:#VALUE!} እና ከውጤት ቬክተር ተዛማጁን እሴት ይመልሱ ፣ ማለትም ትክክለኛ የድርጅት ስም ከአምዱ። ምትክ.

ሁለተኛው ንኡስነት በቴክኒካል ቀመራችን የድርድር ቀመር ነው፣ ምክንያቱም ተግባር ማግኘት እንደ ውጤት አንድ ሳይሆን የሦስት እሴቶች ድርድር ይመለሳል። ግን ከተግባሩ ጀምሮ VIEW ከሳጥን ውጭ ድርድሮችን ይደግፋል፣ ከዚያ ይህን ቀመር እንደ ክላሲክ የድርድር ቀመር ማስገባት የለብንም - የቁልፍ ሰሌዳ አቋራጭን በመጠቀም መቆጣጠሪያ+መተካት+አስገባ. አንድ ቀላል በቂ ይሆናል አስገባ.

ይኼው ነው. አመክንዮውን እንደሚያገኙ ተስፋ ያድርጉ።

የተጠናቀቀውን ቀመር ወደ ዓምዱ የመጀመሪያ ሕዋስ B2 ለማስተላለፍ ይቀራል ቋሚ - እና የእኛ ተግባር ተፈቷል!

እርግጥ ነው, በተለመደው (ብልጥ ያልሆነ) ጠረጴዛዎች, ይህ ፎርሙላ በጣም ጥሩ ይሰራል (ስለ ቁልፉ ብቻ አይርሱ F4 እና ተዛማጅ አገናኞችን ማስተካከል)

ጉዳይ 2. የጅምላ ከፊል መተካት

ይህ ጉዳይ ትንሽ አስቸጋሪ ነው. እንደገና ሁለት "ብልጥ" ጠረጴዛዎች አሉን:

የመጀመሪያው ጠረጴዛ በጠማማ የተፃፉ አድራሻዎች መታረም አለባቸው (ደወልኩ መረጃ 2). ሁለተኛው ሰንጠረዥ የማመሳከሪያ መጽሐፍ ነው, በዚህ መሠረት በአድራሻው ውስጥ ያለውን ንዑስ ሕብረቁምፊ በከፊል መተካት ያስፈልግዎታል (ይህን ሰንጠረዥ ጠርቼዋለሁ. ምትክ2).

እዚህ ያለው መሠረታዊ ልዩነት የዋናውን ውሂብ ክፍልፋይ ብቻ መተካት ያስፈልግዎታል - ለምሳሌ, የመጀመሪያው አድራሻ የተሳሳተ ነው. “ሴንት. ፒተርስበርግ" በስተቀኝ በኩል “ሴንት. ፒተርስበርግ", የቀረውን አድራሻ (ዚፕ ኮድ, ጎዳና, ቤት) እንደማለት መተው.

የተጠናቀቀው ፎርሙላ እንደዚህ ይመስላል (ለግንዛቤ ቀላልነት, ምን ያህል መስመሮችን እንደሚጠቀሙ ከፋፍዬዋለሁ alt+አስገባ):

እዚህ ዋናው ሥራ የሚከናወነው በመደበኛው የ Excel ጽሑፍ ተግባር ነው ንዑስ ትምህርት (ተተኪ)3 ክርክሮች ያሉት፡-

1. የምንጭ ጽሑፍ - ከአድራሻ አምድ የመጀመሪያው ጠማማ አድራሻ
2. እየፈለግን ያለነው - እዚህ ዘዴውን ከተግባሩ ጋር እንጠቀማለን VIEW (ተመልከት)እሴቱን ከአምዱ ለማውጣት ከቀደመው መንገድ ማግኘት, በተጠማዘዘ አድራሻ ውስጥ እንደ ቁርጥራጭ የተካተተ.
3. በምን እንደሚተካ - በተመሳሳይ መንገድ ከአምዱ ውስጥ ከእሱ ጋር የሚስማማውን ትክክለኛ ዋጋ እናገኛለን ምትክ.

ይህን ቀመር በ ጋር አስገባ መቆጣጠሪያ+መተካት+አስገባ እዚህም አያስፈልግም፣ ምንም እንኳን በእውነቱ፣ የድርድር ቀመር ነው።

እና በግልፅ ታይቷል (በቀደመው ምስል ላይ #N/A ስህተቶችን ይመልከቱ) እንዲህ ዓይነቱ ቀመር ፣ ለማንኛውም ውበት ፣ ሁለት ድክመቶች አሉት።

• ሥራ SUBSTITUTE ለጉዳይ ሚስጥራዊነት ያለው ነው።, ስለዚህ "Spb" በፔነልቲሜት መስመር ውስጥ በተለዋዋጭ ሠንጠረዥ ውስጥ አልተገኘም. ይህንን ችግር ለመፍታት, ወይም ተግባሩን መጠቀም ይችላሉ ZAMENIT (ተካ), ወይም በቅድሚያ ሁለቱንም ጠረጴዛዎች ወደ አንድ መዝገብ ያቅርቡ.
• ጽሑፉ መጀመሪያ ላይ ትክክል ከሆነ ወይም በእሱ ውስጥ ከሆነ የሚተካ ቁራጭ የለም (የመጨረሻው መስመር) ፣ ከዚያ የእኛ ቀመር ስህተትን ይጥላል። ይህ አፍታ ተግባሩን በመጠቀም ስህተቶችን በመጥለፍ እና በመተካት ገለልተኛ ሊሆን ይችላል። IFERROR (አይፈሬር):

  

• ዋናው ጽሑፍ ከያዘ በአንድ ጊዜ ከማውጫው ውስጥ ብዙ ቁርጥራጮች, ከዚያም የእኛ ቀመር የመጨረሻውን ብቻ ይተካዋል (በ 8 ኛው መስመር ላይ, ሊጎቭስኪ «ጐዳና« ወደ ተቀየረ "pr-t"፣ ግን "ኤስ-ፒቢ" on “ሴንት. ፒተርስበርግ" ከአሁን በኋላ, ምክንያቱም "ኤስ-ፒቢ” በማውጫው ውስጥ ከፍ ያለ ነው)። ይህ ችግር የራሳችንን ቀመር እንደገና በማስኬድ ሊፈታ ይችላል ፣ ግን ቀድሞውኑ በአምዱ ላይ ቋሚ:

  
  

በቦታዎች ውስጥ ፍጹም እና አስቸጋሪ አይደለም, ነገር ግን ከተመሳሳይ የእጅ ምትክ በጣም የተሻለ ነው, አይደል? 🙂

PS

በሚቀጥለው ርዕስ ውስጥ ማክሮዎችን እና የኃይል መጠይቅን በመጠቀም እንዲህ ዓይነቱን የጅምላ ምትክ እንዴት ተግባራዊ ማድረግ እንዳለብን እንረዳለን.

• ጽሑፍን ለመተካት የSUBSTITUTE ተግባር እንዴት እንደሚሰራ
  
• የ EXACT ተግባርን በመጠቀም ትክክለኛ የጽሑፍ ግጥሚያዎችን ማግኘት
• ጉዳዩን ሚስጥራዊነት ያለው ፍለጋ እና መተካት (ጉዳይ ሚስጥራዊነት ያለው VLOOKUP)