نسخه 3 افزونه تقویم شمسی با امکانات بیشتر منتشر شد!!! (برای مشاهده اینجا کلیک کن)
سبد خرید
0

محصولی در سبد خرید نیست.

بازگشت به فروشگاه
تمام دسته ها
  • تمام دسته ها
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • Power BI
  • Power Pivot
  • Power Query
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

چهار نکته از سلول های مرج شده

خارج کردن سلول از merge
۴.۷/۵ - (۸ امتیاز)

چگونگی خارج کردن سلول از merge در اکسل

گاهی اوقات داده هایی وجود داره که مربوط به هم و مشابه هستن و در چندین سلول قرار دارن. افراد، اکثرا برای نمایش بهتر یا مقایسه کردن این داده ها، اونها رو در یک سلول با هم ادغام (Merge) می کنن. مثلا در یک ستون گروه نوشته شده و در ستون کناری، زیرگروه ها. به عنوان مثال (مطابق با تصویر)، سلول های مربوط به گروه ۱ رو تا جایی که سلول ها مربوط به زیرگروه ۱ میشه ادغام شده. بعدا متوجه خواهیم شد که سلول ادغام شده توانایی انجام ساده ترین کارها رو در شیت (فایل) نداره و باعث ایجاد خطا میشه. مثلا نمیتونیم داده ها رو در یک ستون، که حداقل یک سلول ادغام شده داره رو مرتب کنیم (Sort). فیلتر کردن یا حتی انتخاب یک محدوده هم میتونه با مشکل مواجه بشه. برای حل اینطور موارد باید یاد بگیریم که چطور سلول های ادغام شده رو مشخص کرده و پیدا کنیم و به حالت عادی (خارج کردن سلول از merge) برگردونیم.

تفکیک سلول های ادغام شده (Unmerge)

تفکیک (Unmerge) سلول های در اکسل خیلی ساده است. برای این کار مراحل زیر رو انجام میدیم:

۱- اون تعداد سلول هایی که میخوایم تفکیک بشن و از حالت Merge در بیان رو انتخاب میکنیم.

۲- از تب Home و گروه Alignment، گزینه Merge & Center رو انتخاب میکنیم.

داده های تفکیک شده و قرارگیری محتوا در اولین سلول

شکل ۱- خارج کردن سلول از merge و تفکیک سلول های ادغام شده (Unmerge)

یا میتونیم با کلیک روی فلش کنار گزینه Merge & Center، از منو باز شده گزینه Unmerge Cells رو انتخاب کنیم.

به این ترتیب اکسل تمام سلول های ادغام شده در محدوده انتخابی رو تفکیک میکنه. محتوای هر سلول ادغام شده در سلول بالایی جایگذاری میشه، بقیه سلول های جدا شده خالی باقی میمونن.

انتخاب همه سلول ها برای تفکیک کردن

شکل ۲- داده های تفکیک شده و قرارگیری محتوا در اولین سلول

چگونگی جدا کردن تمام سلول های ادغام شده در یک برگه

در نگاه اول شاید این کار طاقت‌ فرسا بنظر بیاد، ولی در حقیقت با چند کلیک ساده موس، میشه این کارو انجام داد. برای این کار با مراحل زیر پیش میریم:

۱- تمام برگه رو انتخاب کنید. برای این کار باید مثلث کوچک در گوشه بالا سمت چپ رو انتخاب کنین و یا میتونین با کلید های ترکیبی Ctrl + A این کارو انجام بدین.

وجود سلول ادغام شده در شیت

شکل ۳- انتخاب همه سلول ها برای تفکیک کردن

 ۲- با تمام سلول های انتخاب شده، نگاهی به گزینه Merge & Center بندازین:

  • اگر هایلایت شده بود، روی اون کلیک کنین تا تمام سلول های ادغام شده در برگه جدا بشن.

عدم وجود سلول ادغام شده در شیت

شکل ۴- وجود سلول ادغام شده در شیت

  • اگر هایلایت نشده بود، هیچ سلول ادغام شده ای در برگه وجود نداره.

تکرار داده اصلی در سلول های خالی

شکل ۵- عدم وجود سلول ادغام شده در شیت

چگونگی جدا کردن سلول ها و کپی کردن مقدار اصلی در هر سلول تفکیک شده

همونطور که در تصویر زیر میبینیم، برای بهبود ساختار داده ها، بهتره (خیلی وقت ها باید) سلول خالی نداشته باشیم و داده اصلی که در سلول اول داده های تفکیک شده قرار داره رو در بقیه سلول های خالی هم داشته باشیم.

تکرار داده اصلی در سلول های خالی

شکل ۶- تکرار داده اصلی در سلول های خالی بعد از خارج کردن سلول از merge

پس برای این کار طبق مراحل زیر پیش میریم:

مرحله ۱: سلول ها رو انتخاب میکنیم (یا فقط ستونی که در اون سلول ادغام شده داریم) و گزینه Merge & Center از تب Home رو انتخاب کنین. با این کار تمام سلول های ادغام شده جدا میشن، ولی فقط سلول بالایی اونها با داده پر میشه.

مرحله ۲:  دوباره کل محدوده مورد نظر رو انتخاب میکنیم. از تب Home و گروه Editing، گزینهFind & Select رو زده و از منوی باز شده گزینه Go To Special رو انتخاب میکنیم. (یا Ctrl+G رو میزنیم و روی Special کلیک میکنیم)

استفاده از Go to برای انتخب سلول های خالی

شکل۷- انتخاب گزینه Go to Special

مرحله ۳: در پنجره باز شده گزینه Blanks رو انتخاب کرده و روی OK کلیک میکنیم. با این کار از بین سلول های انتخاب شده، سلول های خالی انتخاب میشه.

نوشتن فرمول مورد نظر برای پر کردن سلول های خالی با داده اصلی

شکل ۸- استفاده از Go to برای انتخب سلول های خالی جهت خارج کردن سلول از merge

مرحله ۴: بدون اینکه جایی کلیک کنیم، علامت مساوی

(=) را تایپ کرده و کلید فلش رو به بالا (Arrow Key) روی کیبورد رو فشار میدیم (Enter نمیزنیم). با این کار یک فرمول ساده ساخته میشه که اولین سلول خالی رو با مقداری برابر با سلول بالایی اون پر میکنه.

نوشتن فرمول مورد نظر برای پر کردن سلول های خالی با داده اصلی

شکل ۹- نوشتن فرمول مورد نظر برای پر کردن سلول های خالی با داده اصلی

مرحله ۵:  از اونجایی که میخوایم بعد از خارج کردن سلول از merge تمام سلول های خالی جدا شده پر بشن. باید با فشار دادن کلیدهای ترکیبی Ctrl + Enter اون فرمول رو در تمام سلول های خالی انتخاب شده اعمال کنیم.

در نتیجه هر سلول خالی با مقداری از سلول قبلیش پر خواهد شد.

نکته:
این کار رو با ابزار Flash Fill هم میشه انجام داد. مقاله مرتبط با این ابزار قدرتمند رو هم مطالعه کنید.

 

تکرار داده اصلی در سلول های خالی

شکل ۱۰- تکرار داده اصلی در سلول های خالی

نکته:
میتونیم بعد از انجام این مراحل، سلول ها رو کپی کنیم و از قسمت Paste Special گزینه Value رو انتخاب کنیم. با این کار فرمول های نوشته شده از بین میره و فقط نتیجه نهایی داخل سلول ها باقی میمونه.

 

چگونگی پیدا کردن سلول های ادغام شده خارج کردن سلول از merge

همونطور که میدونیم بهتره تا حد امکان از سلول های ادغام شده پرهیز بشه (این یکی از اصول اساسی ذخیره داده در بانک اطلاعاتی هست).

فرض کنید که یک شیت داده با ساختار نه چندان مناسب در اختیار داریم (سلول مرج داریم، داده تکراری داریم و …). حالا میخوایم که این فایل رو بهبود بدیم که بتونیم راحت تر با داده ها کار کنیم و گزارش تهیه کنیم. مشکل اینجاست که شیت حاوی تعداد زیادی سلول های ادغام شده است که اطلاعاتی درباره آنها نداریم. اینکه کدوم سلول ها، با چه اطلاعاتی و در کجای شیت قرار دارن. پس باید پیدا کنیم و ببینیم چه سلول هایی Merge هستن؟

خب ما میدونیم که Merge کردن سلول، یکی از تنظیمات مربوط به Format Cell و تب alignment است. از طرفی اکسل میتونه بر اساس فرمت جستجو انجام بده. به این ترتیب که:

مرحله ۱: کلیدهای ترکیبی Ctrl + F را فشار دهید تا پنجره Find باز شود. همچنین میتونین از تب Home و گروه Editing گزینه Find & Select رو انتخاب کنین و از منو باز شده Find رو انتخاب کنین.

انتخاب ابزار Find

شکل ۱۱- انتخاب ابزار Find

مرحله ۲: در پنجره باز شده گزینه Option و سپس Format رو انتخاب کنین.

انتخاب Format از ابزار Find

شکل ۱۲- انتخاب Format از ابزار Find

مرحله ۳: به تب Alignment رفته و تیک گزینه Merge Cells رو میزنیم و سپس روی OK کلیک می کنیم.

نحوه پیدا کردن سلول های Merge شده

شکل ۱۳- نحوه پیدا کردن سلول های Merge شده

مرحله ۴: در این مرحله میتونیم یکی از کارهای زیر رو انجام بدیم:

  • روی Find Next کلیک میکنیم تا به سلول ادغام شده بعدی بره.
  • روی Find All‌ کلیک میکنیم تا یک لیست از همه سلول های ادغام شده نمایش داده بشه.

وقتی یکی از روش های بالا را انجام بدید، اکسل سلول های ادغام شده مرتبط در برگه رو انتخاب میکنه.

نمایش همه سلول های Merge شده در پایین ابزار Find

شکل ۱۴- نمایش همه سلول های Merge شده در پایین ابزار Find

نکته:
اگر خواستیم بدونیم که آیا سلول ادغام شده ای در محدوده مشخصی وجود دارد یا نه، اون محدوده رو انتخاب میکنیم و نگاهی به گزینه Merge & Center میندازیم. اگر هایلایت شده بود، به این معنیه که حداقل یک سلول ادغام شده در اون محدوده وجود داره.

 

همونطور که گفتیم در اکثر موارد امکان کار با داده های ادغام شده وجود نداره. پس نحوه کار کردن با داده های ادغام شده، اهمیت خیلی زیادی داره. اینکه چطور داده های مرج شده رو پیدا کنیم، چطور خارج کردن سلول از merge و اینکه چطور سلول های خالی رو پر کنیم رو در این مقاله تشریح کردیم.

134

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

دیدگاه کاربران
  • آواتار
    سیدابوالقاسم امام زاده ۱۸ شهریور ۱۴۰۳ / ۱:۰۱ ب٫ظ

    با سلام و تشکر از شما
    مطلب “چگونگی جدا کردن سلول ها و کپی کردن مقدار اصلی در هر سلول تفکیک شده” بسیار روش خوب و کاربردی در اکسل هست.

    آیا در محیط پاور کوئری هم میشه این کار رو انجام داد؟

    • سامان چراغی ۲۳ شهریور ۱۴۰۳ / ۸:۴۸ ق٫ظ

      درود و سپاس از لطف شما
      دقیق صورت سوالتون رو متوجه نشدم اما به صورت کلی با استفاده از پاورکوئری امکان تفکیک اطلاعات ستون ها وجود داره.

        • سامان چراغی ۲۷ شهریور ۱۴۰۳ / ۹:۰۴ ق٫ظ

          به صورت خودکار عمل Unmerge کردن در پاورکوئری انجام میشه و باعث ایجاد سلول های خالی میشه. اما اینکه چطور سلول های خالی از طریق سلول های بالاسری پر بشه با استفاده از گزینه Fill در قسمت Any Column در تب Transform هست. از گزینه های Fill Down و Fill Up استفاده کنید.

  • Maha ۳۰ اردیبهشت ۱۴۰۳ / ۱۱:۴۹ ق٫ظ

    سلام
    من چطور میتونم اطلاعات در یک ستون مرج شده رو به دو ستون مجزا تبدیل کنم(در ستون مد نظرم هر سلولش شامل دو سلوله که با هم مرج شده س که هر کدوم اون حاوی یک نوع داده متفاوت هست که می‌خوام داده دوم رو به یک ستون مجزا تبدیل کنم)

    • آواتار
      حسنا خاکزاد ۱ خرداد ۱۴۰۳ / ۶:۲۷ ب٫ظ

      درود بر شما
      بسته به الگوی داده ها مثلا میتونید از left/right استفاده کنید و بعد هم unmerge کنید به ۲تا تبدیل میشه

  • لیلی ۴ اردیبهشت ۱۴۰۳ / ۹:۰۶ ب٫ظ

    سلام
    یک ستون معمولی رو میخوام در ستونی که سلولها دو تا دوتا مرج شدن کپی کنم .بعد پیست کردن ستون مرج شده رو از حالت مرج خارج میکنا و اطلاعات ستونی که کپی کردم رو دوبار در ستون دوم که مرج بوده پیست میکنه . راه حلی دارید که اطلاعات در ستون مرج شده درست بشینه و سلولها از حالت مرج خارج نشن ؟

    • آواتار
      حسنا خاکزاد ۱۵ اردیبهشت ۱۴۰۳ / ۱۲:۴۲ ب٫ظ

      درود بر شما
      از اونجا که مرج کردن کار پر ریسکیه و کپی کردن سخت میشه
      فکر نمیکنم راه حلی باشه براش

  • مهدی ۲۰ آبان ۱۴۰۲ / ۸:۳۹ ق٫ظ

    با سلام و ممنون از راهنمایی های خوب شما آقای چراغی و خانم خاکزاد . وقتی می خواهم اطلاعات یک سلول را در ۲ سلول ادغام شده پست کنیم پیغام خطا میده . سایز اطلاعات شما با اطلاعاتتون برابر نیست . راهی داره .

    • آواتار
      حسنا خاکزاد ۲۰ آبان ۱۴۰۲ / ۷:۰۳ ب٫ظ

      درود
      خیلی راحت نمیتونید این کار و بکنید چون مرج مشکل سازه
      یا باید دیتای داخلش رو کپی کنیید (سل رو کپی نکنید) یا مساوی اون محتوا قرار بدید
      برای همین میگیم کلا مرج کردن روا نیست :)

  • میثم ۱۹ مهر ۱۴۰۲ / ۱۰:۱۵ ق٫ظ

    واقعا دمت گرم، کارم خیلی راه افتاد، درود بهت

  • حسینی ۱۶ خرداد ۱۴۰۲ / ۷:۰۸ ب٫ظ

    سلام من یک فایل دارم عنوان هاش زیاد مرج داره هم در ستون و هم در ردیف وقتی فیلتر میزنم فقط ردیف اول فیلتر میشه و عنوان هایی که لازم دارم نمیشه و میگه که فایل ها مرج هستند باید چکار کنم از گزینه find هم که استفاده میکنم چیزی نشون نمیده
    اگه راهنمایی کنید ممنون میشم

    • سامان چراغی ۲ تیر ۱۴۰۲ / ۹:۲۰ ق٫ظ

      درود، می بایست تمام سلول ها رو از حالت Merge خارج کنید (در ابزار Find زبانه Format را باز کنید و از تب Alignment تیک Merge Cells رو بزنید تا همه سلول های Merge شده پیدا بشه).
      بسته به ساختار داده باید یکجوری سلول های خالی رو پر کنید.

    • مجتبی بیات ۵ مهر ۱۴۰۲ / ۹:۴۳ ق٫ظ

      فکر کنم شما تمایل داشته باشید سطر رو دارای فیلتر کنید، کافی هست بر روی عدد سطر مورد نظر کلیک کنید، به این صورت تمام سلول هایلایت می‌شود، سپس با زدن گزینه فیلتر از زیر مجموعه Sort & filter از زیر بخش Data همه سطر فیلتر دار خواهد شد.
      نکته نهایی: اگر سطری خالی است آن را دارای اطلاعات نمایید تا فیلتر کامل عمل نماید.

  • مهدی خلیلی ۲۳ آذر ۱۴۰۱ / ۷:۵۱ ق٫ظ

    با سلام وعرض ادب . ممنونم ازتوضیحات در خصوص فیلتر کردن ستون های ادغام شده . اما فیلتر کردن دیتا بیس حالت جالبی ندارد ، دوایتم مشابه زیر هم قرار می گیرنذ . مثلا دو کلمه yes. امکانش وجودنداره حدقل درستون دیتا بیس قابل فیلتر باشند ؟

    • آواتار
      حسنا خاکزاد ۲۴ دی ۱۴۰۱ / ۲:۴۵ ب٫ظ

      درود بر شما
      متوجه نشدم
      چی جالب نیس؟

  • حسین ۱۲ شهریور ۱۴۰۱ / ۳:۴۲ ب٫ظ

    سلام خسته نباشید

    یک فایل اکسل دارم ستون اول و دوم را میخام انتخاب کنم همه ستونها انتخاب میشن من فقط ستون اول و دوم را باید کپی کنم و لازم دارم باید چکار کنم؟ ممنون

    • آواتار
      حسنا خاکزاد ۱۲ شهریور ۱۴۰۱ / ۵:۴۵ ب٫ظ

      درود
      احتمالا مرج شده
      نمیشه یک قسمت از محدوده مرج شده رو انتخاب و کپ یکرد
      به هم وصله
      از مرج دربیارید

  • عظیمی ۲۶ آبان ۱۴۰۰ / ۹:۳۸ ق٫ظ

    سلام
    من در اکسل فرمت دادم و می‌خوام چند تا سلول رو یکی کنم اما گزینه merge غیر فعال هست
    ممنون میشم کمک کنید

    • آواتار
      حسنا خاکزاد ۲۶ آبان ۱۴۰۰ / ۱۱:۴۱ ق٫ظ

      درود
      احتمالا Table هست محدوده شما

  • آواتار
    امیر ۱۱ بهمن ۱۳۹۹ / ۵:۱۱ ب٫ظ

    با سلام
    من یک سری از اعداد را در ورد نوشته ام و هنگامی که این اعداد را در اکسل وارد می کنم برعکس نوشته می شوند و با تابع substitute هم درست نمی شوند
    مثل در ورد نوشته ام:
    125/۴۱۵/۹۸۵
    458/۱۲۵/۰۰۰
    این اعداد را وقتی که در اکسل وارد (paste)می کنم برعکس می شوند و حاصل جمع درست درنمی آید چگونه / را در اکسل به , تبدیل کنم
    ممنــــــــــــــــــــــــــون می شم کمکم کنید.

    • آواتار
      حسنا خاکزاد ۱۲ بهمن ۱۳۹۹ / ۱۱:۴۹ ق٫ظ

      درود
      / رو باید حذف کنید . میتونید از ابزار find replace استفاده کنید
      تبدیل به عدد که شد بعد جدا کننده سه رقم سه رقم رو از قسمت فرمت سل تنظیم کنید

      • آواتار
        amir ۱۵ بهمن ۱۳۹۹ / ۱۱:۳۶ ق٫ظ

        انجام دادم اما باز هم صفرها برعکس می شه یعنی ۰۰۰/۱۲۴/۱۲۸ می شوند و / به , تبدیل کردم اما باز هم برعکس نوشته می شوند ممنون می شم کمکم کنید نیاز فوریـــــــــــــــــــــــــــــــــــــــــــــــــــــــــی دارم

        • آواتار
          حسنا خاکزاد ۲۹ بهمن ۱۳۹۹ / ۱۲:۱۲ ب٫ظ

          داخل گروه مطرح کنید که بتونید فایلتون و بذارید
          باید مشکل فایل مشخص بشه
          ی جای کار و درست انجام نمیدید

  • آواتار
    amir ۶ دی ۱۳۹۹ / ۱۲:۱۸ ب٫ظ

    با سلام
    یه سوال داشتم که خارج از این آموزش است می خواستم بدونم چه جوری می شه rows to repeat at top رو برای بعضی از صفحات فعال کرد؟ یعنی مثلا اگر ۵۰ صفحه در یک شیت داریم صفحات ۸، ۱۱، ۱۹، ۲۵ و ۴۵ این rows to repeat at top را نداشته باشند و بقیه داشته باشند؟
    با تشکر

    • آواتار
      حسنا خاکزاد ۶ دی ۱۳۹۹ / ۵:۳۲ ب٫ظ

      درود
      تا اونحا که بنده میدونم نمیشه

      • آواتار
        amir ۷ دی ۱۳۹۹ / ۱۱:۱۳ ق٫ظ

        ممــــــــــــــــــــــــــــــــــنون

  • آواتار
    amir ۲۳ آذر ۱۳۹۹ / ۱۰:۵۶ ق٫ظ

    با سلام
    من یک سری اعداد در یک ستون دارم که می خواهم آن ها را جمع کنم اما مشکل اینجاست که بعضی از آنها مرج شده اند و اندازه آن ها هم یکسان نیست و حاصل جمع درست در نمی آید ممنون می شم کمکم کنید

    • سامان چراغی ۲۳ آذر ۱۳۹۹ / ۱۲:۴۹ ب٫ظ

      سلام
      میتونید تمام سلول ها رو از حالت مرج خارج کنید و در یک ستون خالی جمع اعداد ردیف رو بذارید. اینطوری تمام اعداد رو ردیف در یک ستون قرار میگیرند و نهایتا جمع رو روی اون ستون جدید بزنید.

  • محمد ۲ بهمن ۱۳۹۸ / ۲:۴۶ ب٫ظ

    فوق العاده .ممنون

  • کامیار ۱۳ آذر ۱۳۹۷ / ۱۱:۴۱ ق٫ظ

    درود بر شما
    جناب چراغی من توی نوشته ی شما چیزی راجع به کپی یک ستون با سلول های مرج شده در یک ستون جدید با ستون های سینگل ندیدم. در واقع تبدیل دو ستون مرج به یک ستون بدون از دست رفتن داده.

    سپاسگزارم

    • آواتار
      حسنا خاکزاد ۱۳ آذر ۱۳۹۷ / ۱۲:۲۹ ب٫ظ

      درود بر شما
      امکانش نیست. باید از طریق فرمول نویسی این کار و بکنید
      سلول غیر مرج رو مساوی سلول مرج شده بذارید

  • mak ۲۴ آبان ۱۳۹۷ / ۹:۴۳ ق٫ظ

    بسیار عالی

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان