سبد خرید
0

سبد خرید شما خالی است.

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

Skip Blank | تکنیکی کاربردی در Paste special

Skip Blank - آموزش
۴.۵/۵ - (۴ امتیاز)

آشنایی با Skip Blank

حتما تا بحال با این موضوع در اکسل مواجه شده اید که وقتی یک محدوده با سل های خالی را کپی می کنید، موقع paste کردن، سل های خالی هم پیست می شوند و داده های موجود در سل مقصد را پاک میکنند. در حالیکه ما میخواهیم فقط داده های سل های پر منتقل شوند و سل های خالی paste نشوند. کارکرد گزینه Skip Blank در Paste Special اکسل، همین است، یعنی بعد از کپی کردن یک محدوده، کافیست قبل از Paste کردن، تیک این گزینه را در Paste Special بزنید و بعد Ok کنید. با این کار، سل های خالی به محدوده مقصد منتقل نمی شوند.

حالا کاربردی از این تکنیک رو به شما ارائه میدم:
یادم هست یک روز کاری در شرکت، به موضوعی مشابه مسئله ای که در ادامه آن را توضیح می دهم برخوردم که اگر این تکنیک Skip Blank را نمی دانستم، قطعا بیش از یک ماه وقت را باید صرف حل این موضوع بصورت دستی می کردم.

مسئله را با یک مثال عمومی مطرح میکنم که درکش راحت تر باشه.
یک بانک اطلاعاتی از پاسخ افراد به سوالات یک پرسشنامه مطابق شکل ۱ داریم.

پایگاه داده

شکل ۱- بانک اطلاعاتی

این دیتابیس غیرانعطاف پذیر است و به سختی می شود گزارشی بویژه با استفاده از Pivot Table از آن تهیه کرد. با توجه به اصول بانک اطلاعاتی به ویژه بانک اطلاعاتی مورد استفاده در Pivot Table ، باید این چهار ستون را تبدیل کنیم به یک ستون و بجای * گزینه جواب مورد نظر را تایپ کنیم و دیتابیسی مطابق با شکل ۲ ایجاد کنیم.

ساختار مورد نظر

شکل ۲- بانک اطلاعاتی با ساختار مورد نظر

پس مسئله من ایجاد دیتابیسی با ساختار مطابق با شکل ۲ بود.

مراحل دستیابی به این بانک اطلاعاتی در ذیل توضیح داده شده است:

۱- ابتدا علامت های * را باید به گزینه های مربوطه (الف، ب، ج و د) تبدیل کنیم.

برای این کار ستون B را انتخاب کرده و با زدن Ctrl+F پنجره ابزار Find باز می شود. طبق شکل ۳، علامت های * را با عبارت الف جایگزین می کنیم. با زدن Replace All همه * در ستون B تبدیل به الف می شوند.

Skip Blank - جایگزین کردن ستاره

شکل ۳- جایگزین کردن *

نکته:
چون علامت * به منزله هر تعداد کاراکتر است و در ابزار Find هر سل پری را پیدا میکند، برای پیدا کردن کاراکتر * به تنهایی باید *~ را در باکس Find what قرار دهیم.

 

همین کار را برای ستون C ،D و E هم انجام می دهیم. نتیجه مطابق شکل ۴ خواهد بود.

جایگزین کردن ستاره با جواب ها

شکل ۴- جایگزین کردن همه * با گزینه های مربوطه

۲- حالا کافیست همه این ستون ها را از طریق Copy/Paste به یک ستون تبدیل کنیم.

چون می دانیم هر سوال یک جواب دارد و در هر سطر فقط یک سل پر است، از امکان Skip Blank در Paste special استفاده می کنیم. این گزینه، هنگام Paste کردن یک محدوده، سل های خالی آن محدوده را Paste نمی کند.

ابتدا محدوده E2:E21 را انتخاب کرده، کپی میکنیم و روی D2 کلیک راست کرده و Paste special را می زنیم. از پنجره باز شده، تیک گزینه Skip Blank را زده و Ok می کنیم.

Skip Blank - یکی کردن ستون ها

شکل۵- یکی کردن ستون ها

با این روش، ستون E کپی می شود روی ستون D و با این تفاوت که سل های خالی کپی نمی شوند. حالا می توانیم ستون E را حذف کنیم.

در مرحله بعد همین کار را با ستون D می کنیم و روی ستون C کپی میکنیم و ستون D را حذف میکنیم.

همین کار را برای ستون C هم انجام داده و در نهایت دیتابیس را مطابق شکل ۱ ایجاد می کنیم.

حالا فرض کنید همچین مسئله ای در مقیاس ده هزار ردیف، برای بنده پیش آمده بود. بدون استفاده از این تکنیک، حل این مسئله چقدر زمانبر خواهد بود؟؟؟؟

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

کلیدواژه : Paste Specialپیشرفته
آواتار
127

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

دیدگاه کاربران
  • علي حاتمي ۱۳ اسفند ۱۳۹۸ / ۷:۱۰ ق٫ظ

    تشکر فوق العاده بود با منطق جواب کیف کردم

  • زهرا حسنی ۷ شهریور ۱۳۹۸ / ۱۰:۳۶ ق٫ظ

    سلام میشهدخواهش کنم جواب منو زود بدین .هرکاری میکنم skip blank اجرا نمیشه چرا ؟

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

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

  • سعید ۶ آبان ۱۳۹۷ / ۱:۵۹ ق٫ظ

    عالی بود مهندس
    حرف نداری

  • javad ۱۲ تیر ۱۳۹۷ / ۸:۳۶ ق٫ظ

    خیلی ممنوم
    از اینکه داری علم خودت را با دیگران به اشتراک می گذاری
    موفق باشی

  • محمود ۱۷ بهمن ۱۳۹۶ / ۴:۰۳ ب٫ظ

    سلام. عالی بود مث همیشه

  • مرتضی ۱۷ بهمن ۱۳۹۶ / ۹:۰۴ ق٫ظ

    سلام.
    یک روش دیگر هم ترکیب توابع If و AND است. البته نسبت به این راه حل طولانی تر خواهد بود 🙂

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

      سلام
      بله حتما راه فرمول نویسی هم وجود داره….
      ابزار و فرمول هر کدوم ویژگی ها و شرایط خودشون و دارن. بعضی جاها شاید لازم باشه علیرغم طولانی بودن، فرمول استفاده بشه و بعضی جاها هم شاید ابزار جواب نیاز رو بده

      ممنون از مشارکتتون
      موفق باشید

  • سهیلا ۶ مرداد ۱۳۹۶ / ۳:۳۶ ب٫ظ

    عااااااالی مهندس

  • mmahdih ۱۳ اردیبهشت ۱۳۹۶ / ۱۲:۱۲ ب٫ظ

    سلام استاد
    عالی بود

  • اکرم ۱۷ فروردین ۱۳۹۶ / ۱۰:۳۴ ق٫ظ

    با عرض سلام و خسته نباشید. بسیار عالی و کاربردی بود

  • امین ۱۰ اسفند ۱۳۹۵ / ۴:۲۰ ب٫ظ

    سلام
    تشکر استاد.جالب بود.

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان