اگر پایههای اکسل را بلدید اما هنوز خیلی از کارها را با کلیکهای پیدرپی و فرمولهای ساده انجام میدهید، همین چند ترفند کافی است تا سرعت کارتان چند برابر شود؛ میانبرهای صفحهکلید، Flash Fill برای پاکسازی سریع داده، تابع XLOOKUP بهجای VLOOKUP، قالببندی شرطی هوشمند و جدول محوری پیشرفته، همگی همین امروز روی فایلهای کاری شما قابل استفاده هستند.
Ctrl+E و Ctrl+Shift+L دهها کلیک روزانه را حذف میکنند.اکسل نرمافزاری است که اغلب کاربران فقط بخش کوچکی از قابلیتهای آن را میشناسند. کسانی که پیشتر با اصول اولیه اکسل مثل نوشتن فرمولهای ساده، مرتبسازی و فیلتر آشنا شدهاند، معمولاً روزانه ساعتها وقت خود را صرف کارهایی میکنند که با چند میانبر یا ابزار داخلی اکسل، در چند ثانیه انجامپذیر است. اگر هنوز با مفاهیم پایه اکسل آشنا نیستید، بهتر است ابتدا سراغ آموزش اکسل مقدماتی برای کارجویان بروید و بعد به سراغ این ترفندهای سطح بالاتر بیایید. در ادامه این مطلب، مجموعهای از ترفندهای کاربردی و آزمایششده اکسل را بررسی میکنیم که برای کاربران نیمهحرفهای و حرفهای طراحی شدهاند.
میانبرهایی مثل Ctrl+E برای Flash Fill، Ctrl+Shift+L برای فیلتر، Ctrl+T برای تبدیل داده به جدول و Ctrl+Arrow برای پرش سریع بین دادهها، بیشترین بازدهی را در کارهای روزمره اکسل دارند و یادگیری آنها معمولاً کمتر از یک ساعت زمان میبرد.
به گفته مشاوران بهرهوری در محیطهای اداری، بخش زیادی از زمان کار با اکسل، صرف جابهجایی دست بین صفحهکلید و ماوس میشود؛ همین رفتوبرگشتهای کوچک، در طول یک روز کاری به دقایق قابلتوجهی تبدیل میشود. جدول زیر برخی از پرکاربردترین میانبرهای اکسل را که راهنمای رسمی مایکروسافت نیز آنها را تأیید کرده، نشان میدهد.
| میانبر | کاربرد |
|---|---|
Ctrl+E | اجرای Flash Fill برای الگوگیری خودکار از داده |
Ctrl+Shift+L | فعال یا غیرفعال کردن فیلتر روی ستونها |
Ctrl+T | تبدیل محدوده داده به جدول ساختاریافته |
Ctrl+1 | باز کردن پنجره قالببندی سلولها |
Ctrl+Home / Ctrl+End | پرش به ابتدا یا انتهای دادههای کاربرگ |
Alt+= | درج سریع فرمول جمع (Sum) |
F4 | تکرار آخرین دستور یا قفل کردن مرجع سلول در فرمول |
فهرست کامل و رسمی این میانبرها را میتوانید در راهنمای میانبرهای صفحهکلید اکسل در سایت پشتیبانی مایکروسافت ببینید. توصیه میشود در هفته اول فقط سه یا چهار میانبر از این فهرست را تمرین کنید تا در حافظه عضلانی دست جا بیفتد؛ اضافه کردن همه میانبرها در یک روز معمولاً باعث فراموشی سریع آنها میشود.
Flash Fill ابزاری در اکسل است که با دیدن یک یا دو نمونه از تغییری که روی داده انجام میدهید، الگو را تشخیص میدهد و همان تغییر را روی کل ستون اعمال میکند؛ کافی است یک نمونه بنویسید و کلید Ctrl+E را بزنید.
فرض کنید ستونی از نام و نامخانوادگی کامل دارید و فقط به نام کوچک افراد نیاز دارید. کافی است در ستون کناری، نام کوچک اولین نفر را تایپ کنید و سپس در سلول بعدی شروع به تایپ نام دوم کنید؛ اکسل معمولاً بلافاصله پیشنهاد میدهد و با زدن Enter، بقیه ستون بهصورت خودکار پر میشود. همین ابزار برای جدا کردن کد ملی از شماره تماس، ساخت ایمیل از نام و نامخانوادگی، یا حذف پیشوندهای اضافه از یک ستون شماره تلفن هم بهخوبی کار میکند. طبق راهنمای رسمی Flash Fill در سایت پشتیبانی مایکروسافت، این ابزار را میتوان از مسیر Data و سپس Flash Fill نیز بهصورت دستی فعال کرد، برای مواقعی که تشخیص خودکار الگو را بهدرستی انجام نمیدهد.
نکته مهم درباره Flash Fill این است که نتیجه آن مقداری ثابت است، نه فرمول زنده؛ یعنی اگر داده اصلی تغییر کند، ستون حاصل از Flash Fill بهطور خودکار بهروزرسانی نمیشود و باید دوباره اجرا شود. برای دادههایی که مدام تغییر میکنند، بهتر است از فرمولهایی مثل LEFT، RIGHT یا TEXTSPLIT استفاده کنید.
XLOOKUP نسخه جدیدتر و انعطافپذیرتر VLOOKUP است که برخلاف آن، محدود به جستوجو از چپ به راست نیست، میتواند چند ستون را همزمان برگرداند و پیام خطای دلخواه تعریف کند؛ برای هر فایل جدید، XLOOKUP گزینه بهتری نسبت به VLOOKUP است.
در فرمول VLOOKUP، همیشه باید ستون جستوجو در سمت چپ ستون نتیجه باشد؛ همین محدودیت باعث میشد بسیاری از کاربران مجبور شوند ستونهای جدول را جابهجا کنند تا فرمول کار کند. طبق توضیحات رسمی مایکروسافت درباره XLOOKUP، این تابع میتواند در هر جهتی از جدول جستوجو کند، نتیجه را از یک آرایه چندستونی برگرداند و برای مواقعی که مقدار پیدا نشد، خروجی دلخواه شما (مثلاً عبارت نامشخص) را نمایش دهد، بهجای خطای پیشفرض N/A#.
XLOOKUP(مقدار جستجو؛ محدوده جستجو؛ محدوده نتیجه؛ [اگر پیدا نشد])=XLOOKUP(A2؛ B2:B100؛ D2:D100؛ "یافت نشد")اگر هنوز با فرمولهای پایه مثل VLOOKUP راحتتر هستید، جای نگرانی نیست؛ منطق هر دو فرمول یکسان است و تنها با یادگیری چیدمان جدید XLOOKUP، میتوانید در همان روز اول از آن استفاده کنید.
قالببندی شرطی، یعنی رنگی یا نمادین کردن خودکار سلولها بر اساس مقدار آنها؛ با این ابزار میتوانید در چند کلیک، بالاترین فروش، پایینترین موجودی یا مقادیر تکراری را در میان هزاران ردیف داده بهسرعت پیدا کنید.
برای فعال کردن آن، محدوده مدنظر را انتخاب کنید، از تب خانه گزینه Conditional Formatting را باز کنید و یکی از قالبهای آماده مانند نوارهای داده، مقیاس رنگی یا نمادهای هشدار را انتخاب کنید. برای کاربردهای دقیقتر، گزینه New Rule امکان نوشتن شرط سفارشی با فرمول را میدهد؛ برای مثال با فرمول =B2>AVERAGE($B$2:$B$100) میتوانید تمام ردیفهایی را که از میانگین ستون بیشتر هستند، بهصورت خودکار هایلایت کنید.
یکی از کاربردهای پرتکرار قالببندی شرطی، پیدا کردن مقادیر تکراری در یک لیست بلند است؛ با انتخاب گزینه Highlight Cells Rules و سپس Duplicate Values، اکسل تمام مقادیر تکراری را در چند ثانیه رنگی میکند، کاری که بهصورت دستی میتواند ساعتها زمان ببرد. توجه داشته باشید که قالببندی شرطی زیاد و رنگهای متعدد در یک کاربرگ، خوانایی گزارش را کم میکند؛ بهتر است در هر جدول، حداکثر از دو یا سه قانون همزمان استفاده کنید.
جدول محوری برای خلاصه کردن سریع هزاران ردیف داده استفاده میشود؛ در سطح پیشرفته میتوانید با فیلدهای محاسبهشده، گروهبندی تاریخ و برشدهندهها (Slicer)، گزارشهایی تعاملی بسازید که با یک کلیک برای هر بخش سازمان یا هر بازه زمانی بهروزرسانی میشوند.
برای شروع، محدوده داده را انتخاب کرده و از تب درج، گزینه PivotTable را بزنید. پس از ساخت جدول، از قسمت Values میتوانید نوع محاسبه را از جمع ساده به میانگین، تعداد یا درصد از کل تغییر دهید؛ همین تغییر ساده، بسیاری از تحلیلهای پایه را بدون نوشتن حتی یک فرمول ممکن میکند. ابزار Slicer که از تب Insert قابل افزودن است، یک پنل دکمهای میسازد که با کلیک روی هر گزینه، کل جدول محوری و نمودارهای متصل به آن، فیلتر میشوند؛ این ابزار برای داشبوردهای فروش یا گزارشهای ماهانه بسیار پرکاربرد است.
یکی از قابلیتهای کمتر شناختهشده جدول محوری، گروهبندی خودکار تاریخ است؛ اگر ستون تاریخ در داده وجود داشته باشد، با راستکلیک روی آن در جدول محوری و انتخاب Group، میتوانید دادهها را بهصورت خودکار بر اساس ماه، فصل یا سال گروهبندی کنید، بدون نیاز به ساخت ستون کمکی جداگانه.
اعتبارسنجی داده (Data Validation) به شما اجازه میدهد قوانینی برای هر سلول تعریف کنید، مثلاً فقط عدد بین یک تا صد، فقط تاریخ معتبر یا فقط یکی از گزینههای یک لیست کشویی؛ این ابزار بیشتر برای فایلهایی مفید است که چند نفر همزمان در آنها اطلاعات وارد میکنند.
برای ساخت یک لیست کشویی ساده، محدوده مدنظر را انتخاب کنید، از تب داده گزینه Data Validation را باز کنید، در قسمت Allow گزینه List را انتخاب کنید و منبع لیست (مثلاً چند گزینه از پیش تعیینشده مانند وضعیت سفارش) را وارد کنید. از این پس، هر کسی که در آن سلول اطلاعات وارد کند، فقط میتواند یکی از گزینههای تعریفشده را انتخاب کند و امکان تایپ اشتباه یا نوشتن با املای متفاوت از بین میرود. این قابلیت بهخصوص در فایلهای اشتراکی تیمی، از بروز خطاهای پرتکرار مثل فاصله اضافه یا حروف بزرگ و کوچک ناهماهنگ جلوگیری میکند و کیفیت گزارشهای نهایی را بالا میبرد.
بهجای نوشتن فرمول با آدرس خام سلول مثل B2:B100، میتوانید به آن محدوده یک نام معنادار مثل قیمت_محصولات بدهید؛ فرمولهایی که از این نامها استفاده میکنند، هم خواناتر هستند و هم احتمال خطای مرجع در آنها کمتر است.
برای ساخت محدوده نامگذاریشده، محدوده موردنظر را انتخاب کنید و در جعبه نام، سمت چپ نوار فرمول، یک نام بدون فاصله وارد کرده و Enter بزنید. از این پس در هر فرمولی، بهجای آدرس سلول میتوانید همان نام را تایپ کنید؛ برای مثال =SUM(قیمت_محصولات) بسیار روشنتر از یک آدرس چندرقمی سلول است. این روش هنگام کار روی فایلهای حجیم با دهها فرمول تودرتو، اشتباهات ناشی از انتخاب اشتباه محدوده را بهطور محسوسی کاهش میدهد و اگر بعداً بخواهید محدوده را جابهجا کنید، کافی است تعریف نام را ویرایش کنید، بدون نیاز به تغییر تکتک فرمولها.
ابزار Quick Analysis که با انتخاب هر بلوک داده در گوشه پایین راست آن ظاهر میشود، امکان ساخت سریع نمودار، جدول محوری، مجموع یا قالببندی شرطی را بدون رفتن به هیچ منویی فراهم میکند؛ ترکیب آن با توابع شرطی مانند IF و IFS، تحلیلهای روزمره را چند برابر سریعتر میکند.
وقتی محدودهای از داده را انتخاب میکنید، آیکون کوچک Quick Analysis در گوشه پایین راست انتخاب ظاهر میشود؛ با کلیک روی آن، تبهایی مثل Formatting، Charts، Totals، Tables و Sparklines در دسترس قرار میگیرد و میتوانید بدون جستوجو در منوهای مختلف، مستقیماً نتیجه دلخواه را پیشنمایش و اعمال کنید. در کنار این ابزار، ترکیب توابع IF با AND یا OR هم یکی از پرکاربردترین تکنیکها برای تصمیمگیری خودکار در جدولهاست؛ برای نمونه فرمول =IF(AND(B2>50؛C2="فعال")؛"تأیید"؛"رد") فقط زمانی عبارت تأیید را نشان میدهد که هر دو شرط برقرار باشد. برای تصمیمهای چندحالته، تابع IFS معمولاً از تودرتو کردن چند IF پشت سر هم خواناتر و کمخطاتر است.
اگر به دنبال راههای سریعتری برای نوشتن فرمول یا خلاصه کردن داده در اکسل هستید، ابزارهای هوش مصنوعی داخل آفیس هم میتوانند کمک زیادی کنند؛ برای آشنایی با نحوه استفاده از این قابلیتها، مطلب آشنایی با Copilot مایکروسافت و نحوه استفاده از آن در ورد و اکسل را هم بخوانید.
هیچکدام از ترفندهای این مطلب نیاز به دوره آموزشی طولانی ندارند؛ کافی است هر هفته یک یا دو مورد از آنها را روی فایلهای واقعی کاری خودتان تمرین کنید. شروع خوب، یادگیری میانبرهای پرکاربرد و Flash Fill است، چون بازدهی فوری و قابللمس دارند. در مرحله بعد، جایگزین کردن VLOOKUP با XLOOKUP و اضافه کردن قالببندی شرطی به گزارشهای تکراری، سرعت و دقت کارتان را بهطور محسوسی بالا میبرد. برای گزارشهای تحلیلی بزرگتر، جدول محوری پیشرفته همراه با Slicer و محدودههای نامگذاریشده، ابزار اصلی شماست. اگر اکسل را ابزار روزمره کار خود میدانید، بین یک تا دو میلیارد کاربر دیگر در سراسر جهان هم همین ابزار را برای کارهای مشابه به کار میبرند و همین گستردگی، دلیل اصلی سرمایهگذاری مداوم مایکروسافت روی این نرمافزار است.
مرکز پشتیبانی رسمی مایکروسافت، راهنمای تابع XLOOKUP
مرکز پشتیبانی رسمی مایکروسافت، راهنمای استفاده از Flash Fill
مرکز پشتیبانی رسمی مایکروسافت، فهرست میانبرهای صفحهکلید اکسل
تیم تحریریه
دیدگاه شما برای ما ارزشمند است
تمامی حقوق برای سایت چه خبر محفوظ است