ترفندهای فرمول‌نویسی در اکسل با استفاده‌ی بهینه از کیبورد و کلیدهای فانکشن
نوشتن فرمول در یک سلول در نرم‌افزارهایی مثل گوگل شیتس و اکسل ساده است و به همین ترتیب کپی کردن آن در سلولی دیگر ساده است. اما اگر بخواهید فرمولی بنویسید و در تعداد زیادی سلول پایین‌تر کپی کنید، روش کپی و پیست مناسب نیست. ترفندهای سریع‌تری برای این کار وجود دارد. حتی می‌توانید با استفاده از تابع ArrayFormula کاری کنید که با اضافه شدن ردیف جدید، به صورت خودکار فرمول در سلول‌های موردنظر درج شود.
در ادامه به نحوه کپی کردن فرمول در سلول‌های یک ستون و همین‌طور اضافه کردن خودکار فرمول در ردیف‌های جدید در Google Sheets می‌پردازیم.
قبل از هر چیز توجه کنید که با کپی و پیست کردن فرمول، ریفرنس‌هایی که به سلول‌های دیگر داده شده، تغییر می‌کند، مگر آنکه از ریفرنس‌های مطلق در اشاره به سلول‌های دیگر استفاده کرده باشید.
ترفندهای فرمول‌نویسی در اکسل با استفاده‌ی بهینه از کیبورد و کلیدهای فانکشن
شاید برخی کاربران مبتدی، برای کپی کردن فرمول در سلول‌های ستون یا ردیف، از روش کپی و پیست استفاده کنند اما مسأله این است که کپی و پیست کردن روش وقت‌گیری است که حتی احتمال خطا و اشتباه کاربر را افزایش می‌دهد.
کاربرد مربع کوچک گوشه‌ی راست و پایین سلول‌ها در Excel و Google Sheets
زمانی که روی سلولی از جدول در نرم‌افزار Excel مایکروسافت و نرم‌افزارهای مشابه نظیر گوگل شیتس کلیک می‌کنید، در گوشه‌ی راست و پایین سلول یک مربع کوچک نمایش داده می‌شود. این مربع کوچک برای پرسازی خودکار یا Auto Fill در نظر گرفته شده و بسیار مفید است چرا که اگر روی آن کلیک کرده و با نگه داشتن کلید چپ موس، موس را در جهتی حرکت بدهید، فرمولی که در سلول درج شده در سلول‌های اطراف پیست می‌شود. دقت کنید که حین درگ کردن مربع کوچک، نشانگر موس به شکل + تغییر می‌کند.
درگ کردن مربع کوچک گوشه‌ی سلول‌ها موجب کپی شدن فرمت و استایل سلول در سلول‌های مجاور نیز می‌شود. به عنوان مثال اگر حاشیه‌ی سلول یا Border آن خط ضخیم باشد و رنگ داخل سلول زرد باشد و فونت و اندازه‌ی فونت را به شکل خاصی تنظیم کرده باشید، تمام این موارد نیز در سلول‌های بعدی اعمال می‌شود.
اما برخی سلول‌های دارای فرمول نیستند. در این صورت این سوال مطرح می‌شود:
اگر سلول حاوی عدد یا متن باشد و مربع کوچک آن را درگ کنیم، چه اتفاقی می‌افتد؟
اگر سلول حاوی فرمول نباشد نیز مقدار آن در سلول‌های اطراف کپی می‌شود. اگر دو یا چند سلول را انتخاب کنید و مربع کوچک را درگ کنید، پر کردن خودکار و هوشمندانه انجام می‌شود. به عنوان مثال اگر دو سلول مجاور حاوی عدد 1 و 2 را انتخاب کنید و سپس مربع را درگ کنید، در سلول‌های اطراف اعداد 3 و 4 و 5 و 6 و … چاپ می‌شود.
همان‌طور که می‌بینید کاربرد مربع کوچک گوشه‌ی سلول‌ها بسیار زیاد است.
اگر بخواهید فقط فرمول را در سلول‌های بعدی پیست کنید و در واقع از تغییر فرمت و استایل سلول‌های بعدی جلوگیری کنید، استفاده از مربع کوچک مناسب نیست. برای این حالت می‌بایست ابتدا سلول اول را انتخاب کرده و با زدن کلید میانبر Ctrl + C آن را کپی کنید. سپس سلول مقصد را انتخاب کرده و روی آن راست‌کلیک کنید. در منوی راست‌کلیک گزینه‌ی Paste Special و سپس Paste Formula only را انتخاب کنید. با این روش فقط فرمولی که کپی شده، پیست می‌شود.
فرمول‌نویسی آرایه‌ای در تمام سلول‌های یک ستون
ممکن است صدها ردیف در یک فایل Google Sheets داشته باشید و بخواهید فرمولی بنویسید و در تمام سلول‌های یک ستون کپی کنید. یک روش کار استفاده از مربع کوچک گوشه‌ی پایین سلول است اما روش دیگر استفاده از فرمول‌نویسی آرایه‌ای یا در واقع تابعی به اسم ArrayFormula است. این تابع فرمولی که به عنوان آرگومان به آن داده شده را در سلول‌های بعدی اعمال می‌کند و می‌توانید با شورت‌کاتی ساده، از آن استفاده کنید.
استفاده از این تابع سه مرحله‌ی ساده دارد:
ابتدا فرمول را در سلول موردنظر تایپ کنید.
به جای اینکه به سلول خاصی از ستون دیگر ارجاع یا ریفرنس دهید، به آن سلول و تمام سلول‌های پایین‌تر ریفرنس دهید. به عنوان مثال اگر لازم است در فرمول‌نویسی به سلول B2 اشاره کنید، به این سلول و تمام سلول‌های پایینی آن اشاره کنید. برای این کار از B2:B استفاده کنید.
گام بعدی فشار دادن کلید میانبر تابع ARRAYFORMULA است: در ویندوز و لینوکس از Ctrl + Shift + Enter و در مک‌او‌اس از Cmd + Shift + Enter استفاده کنید. نتیجه این خواهد بود که فرمول تایپ شده، در تمام سلول‌های پایین‌تر از همان ستون قرار می‌گیرد.
با یک مثال ساده این ترفند جالب را مرور می‌کنیم: فرض کنید که می‌خواهید اعداد موجود در ستون B که قیمت محصولات است را در ۱۸ درصد ضرب کرده و برای جلوگیری از طولانی شدن اعداد، حاصل کار را تا دو رقم اعشار رند کنید.
فرمول سلول C2 در حالت عادی به صورت زیر خواهد بود:
=ROUND(B2*18%,2)
اما برای فرمول‌نویسی در تمام سلول‌های ستون C، فرمول فوق را به این صورت می‌نویسیم:
=ROUND(B2:B*18%,2)
و سپس کلید میانبر اشاره شده را فشار می‌دهیم. نتیجه را در تصویر متحرک زیر مشاهده می‌کنید:
فرمول‌نویسی آرایه‌ای یکی از ترفندهای مورداستفاده توسط کاربران حرفه‌ای است که سرعت کار را به خوبی افزایش می‌دهد. نکته‌ی جالب این است که اگر بخواهید فرمول را ویرایش کرده و تصحیح کنید، کافی است فقط یک سلول را ادیت کنید.
اما یک سوال:
اگر بخواهید فرمول‌نویسی آرایه‌ای انجام بدهیم به گونه‌ای که در ردیف‌های حاوی سلول خالی، فرمول اضافه نشود، چه باید کرد؟
یک راهکار این است که سطرهای خالی و سطرهای پایین جدول تا انتهای صفحه که خارج از محدوده‌ی جدول است را حذف کنید!
راه حل بعدی این است که در نوشت فرمول از تابع شرطی IF استفاده کنید تا محاسبه فقط در صورتی انجام شود که سلولی که به آن ارجاع داده شده، پر باشد. در غیر این‌صورت چیزی در سلول چاپ نشود.
برای بررسی خالی بودن سلولی مثل A1 می‌توانید از یکی از توابع ISBLANK یا LEN استفاده کنید:
- فرمول ISBLACK(A1) در صورت خالی بودن سلول A1، صحیح یا TRUE را برمی‌گرداند و در غیر این صورت FALSE خروجی آن است.
- حاصل فرمول LEN(A1)<>0 در صورت خالی بودن A1، صحیح یا TRUE است. در غیر این صورت FALSE یا غلط نتیجه‌ی این فرمول است.
برای آشنایی با شیوه‌ی استفاده از تابع IF و آرگومان‌های آن به مقاله‌ی زیر توجه فرمایید:
آموزش استفاده از توابع شرطی AND و OR و IF در اکسل و Google Sheets
اما به مثال قبلی برگردیم که محاسبه ۱۸ درصد قیمت محصولات و رند کردن آن است. فرمول سلول C2 به صورت زیر تغییر می‌کند:
=IF(ISBLANK(B2), “” , ROUND(B2*18%,2))
در واقع اگر سلول B2 خالی باشد، عبارت چاپ شده “” یا تهی است و اگر پر باشد، محاسبه و گرد کردن به کمک تابع ROUND، انجام می‌شود.
به منظور فرمول‌نویسی آرایه‌ای، در فرمول فوق B2 را به B2:B تبدیل می‌کنیم:
=IF(ISBLANK(B2:B), “” , ROUND(B2:B*18%,2))
و سپس شورت‌کات Ctrl + Shift + Enter را فشار می‌دهیم. نتیجه را در تصویر زیر مشاهده می‌کنید. تا سطر 6 که قیمت محصولات درج شده، محاسبه صورت گرفته و سلول‌های پایین‌تر خالی باقی می‌ماند.
برای چک کردن خالی بودن سلول‌ها دو روش مطرح کردیم و روش دیگر این است که شرط تصاوی B2 با “” را بررسی کنید. به عبارت درگیر در مثال ما، فرمول‌نویسی را به هر سه شکل زیر می‌توان انجام داد و نتیجه یکسان خواهد بود:
=ArrayFormula(IF(ISBLANK(B2:B), “”, ROUND(B2:B*18%, 2)))
=ArrayFormula(IF(LEN(B2:B)<>0, ROUND(B2:B*18%, 2), “”))
=ArrayFormula(IF(B2:B=””, “”, ROUND(B2:B*18%, 2)))
فرمول‌های آرایه‌ای در عنوان ستون‌ها
مثالی که پیش‌تر بررسی شد به فرمول‌نویسی در سلول‌های دوم به بعد ستون مربوط می‌شد اما در واقع می‌توانید فرمول را با اضافه کردن یک شرط دیگر، در تمام ستون شامل ردیف عنوان بنویسید.
با بررسی همان مثال به توضیح شرط جدید می‌پردازیم:
می‌بایست قبل از محاسبه‌ی ۱۸ درصد قیمت محصول، شماره ردیف را به کمک تابع ROW چک کنید. اگر شماره ردیف 1 است و در واقع اگر سلول فعلی در ردیف اول قرار دارد، می‌بایست عنوان ستون درج شود و اگر 2 یا بیشتر است، مقدار در ۱۸ درصد ضرب شده و رند می‌شود.
فرمول آرایه‌ای مثال ما به صورت زیر خواهد بود:
=ArrayFormula(IF(ROW(B:B)=1,”Tax”,IF(ISBLANK(B:B),””,ROUND(B:B*18%, 2))))
و حاصل کار:
استفاده از فرمول‌نویسی آرایه‌ای برای پر کردن خودکار سطرهای جدید
فرمول‌نویسی به کمک تابع ArrayFormula در ساخت فرم‌های آنلاین Google Forms نیز بسیار مفید است. به عنوان مثال می‌توانید پاسخ‌هایی که به فرم آنلاین داده شده را در ردیفی جدید از یک صفحه در گوگل شیتس ذخیره کنید.
آموزش ساخت فرم ثبت نام آنلاین به کمک Google Forms
اگر فرمول‌نویسی عادی انجام داده باشید، طبعاً می‌بایست فایل گوگل شیتس را باز کرده و در سلول‌های ردیف‌های جدید، فرمول‌هایی را کپی و پیست کنید. اما در صورت استفاده از فرمول‌نویسی آرایه‌ای، محاسبات لازم در مورد هر پاسخ جدیدی که ثبت شده به صورت خودکار انجام می‌شود و نیازی به کپی و پیست کردن فرمول‌ها در ردیف‌های جدید نیست
چگونه از VLOOKUP در فرمول‌نویسی آرایه‌ای استفاده کنیم؟
تابع VLOOKUP برای جستجو در سلول‌ها کاربرد دارد و می‌توانید آن را با تابع ARRAYFORMULA به شکل ترکیبی استفاده کنید.
به یک مثال ساده توجه کنید: فرض کنید نام میوه‌ها در صفحه‌ی اول فایل گوگل شیتس و در ستون A درج شده و قیمت هر محصول در ستون B ذکر شده است. در صفحه‌ی دوم از این فایل، نام میوه‌ها در ستون اول ذکر شده و تعداد سفارش یا فروش، در ستون B درج شده است. هدف شما محاسبه کردن مجموع فروش هر محصول در ستون C است.
اگر ستون A در این دو صفحه، ترتیب مشابه نداشته باشد، می‌بایست از تابع VLOOKUP برای یافتن نام میوه و قیمت آن در صفحه‌ی اول استفاده کنید و سپس قیمت را در تعداد فروش ضرب کنید تا مجموع فروش به دست آید.
برای این مسأله از فرمول نسبتاً پیچیده و در عین حال ساده‌ای استفاده می‌کنیم:
- ابتدا بررسی می‌کنیم که شماره‌ی ردیف 1 است یا بیشتر. اگر ردیف اول است، عنوان درج می‌شود.
- اگر شماره‌ی ردیف 2 یا بیشتر است، ابتدا شرط پر بودن سلول بررسی می‌شود.
- در صورت پر بودن سلول نام محصول، نام میوه در ستون A از صفحه‌ی اول که Fruits نام دارد، به کمک VLOOKUP سرچ می‌شود و قیمت آن که در ستون شماره 2 از همان صفحه قرار گرفته، استخراج می‌شود.
- در نهایت قیمت در تعداد ضرب می‌شود. تمام این مراحل در فرمول زیر انجام می‌شود:
=ArrayFormula(
IF(ROW(A:A)=1,
“Total”,
IF(NOT(ISBLANK(A:A)), VLOOKUP(A:A, Fruits!A2:B6, 2, FALSE) * B:B, “”)))
همان‌طور که مشاهده می‌کنید روش کار با تابع فرمول‌نویسی آرایه‌ای یا ArrayFormula پس از کمی تجربه، ساده و درست مشابه فرمول‌نویسی معمولی است.
labnolسیاره‌ی آی‌تی
تابع VLOOKUP یکی از توابع جستجو کردن در Google Sheets و نرم‌افزارهای مشابه نظیر Excel است که کاربردهای زیادی دارد اما برای مبتدیان کمی ناآشنا و پیچیده به نظر می‌رسد. تابع VLOOKUP عبارتی که جستجو شده را در سلول‌های محدوده‌ی سرچ پیدا می‌کند و مقداری که روبروی آن در یکی از ستون‌های بعدی قرار گرفته را برمی‌گرداند.
در ادامه با نحوه استفاده کردن از تابع VLOOKUP در گوگل شیتس آشنا می‌شویم که البته درست شبیه به کاربرد آن در اکسل آفیس مایکروسافت است.
در نرم‌افزار اکسل زمانی که می‌خواهید از VLOOKUP استفاده کنید، تایپ کردن فرمول به صورت دستی یک روش کار است و روش ساده‌تر این است که از راهنمای مرحله به مرحله‌ی اکسل استفاده کنید. در گوگل شیتس می‌بایست فرمول را به صورت دستی تایپ کنید که شاید در ابتدا کمی پیچیده به نظر برسد اما پس از چند مرتبه فرمول‌نویسی متوجه می‌شوید که استفاده از VLOOKUP در Google Sheets نیز بسیار ساده است.
تابع VLOOKUP چطور کار می‌کند؟
قبل از معرفی آرگومان‌های این تابع، بهتر است نحوه‌ی عملکرد آن را بررسی کنیم تا کاربرد هر یک از آرگومان‌های آن روشن شود. تابع VLOOKUP عبارت سرچ شده را در محدوده‌ای از سلول‌ها جستجو می‌کند و در صورت یافتن عبارت، مقدار موجود در سلولی از همان ردیف را برمی‌گرداند.
با یک مثال ساده موضوع روشن‌تر می‌شود: فرض کنید که فایلی مربوط به کارمندان یک شرکت در اختیار دارید که در آن جدولی حاوی شناسه، نام و نام خانوادگی و تاریخ تولد هر شخص ذکر شده است. بنا به نیاز می‌خواهید با وارد کردن شناسه‌ی کارمندی، تاریخ تولد کارمند مشخص شود. برای این منظور می‌توانید از تابع VLOOKUP استفاده کنید به این صورت که شناسه‌ی کارمند را در جدول جستجو کنید و سپس تاریخ تولد کارمند را از همان ردیف استخراج نمایید.
و اما 4 آرگومان تابع VLOOKUP:
- آرگومان اول Search_Key یا داده‌ی معلوم و در واقع همان عبارتی است که در جدول جستجو می‌شود.
- آرگومان دوم Range یا محدوده‌ای از سلول‌ها است که با مشخص کردن سلول ابتدا و انتها و علامت : بینشان تعریف می‌شود.
- سومین آرگومان Index یا شماره ستون است. اولین ستون از محدوده‌ی سلول‌ها، شماره 1 است و ستون‌های بعدی با اندیس 2 و اندیس 3 و … مشخص می‌شوند.
- آرگومان آخر Is_sorted است که اگر آن را FALSE وارد کنید، نتیجه‌ی دقیق جستجو برگردانده می‌شود و اگر TRUE وارد کنید، نزدیک‌ترین مقدار به عبارت جستجو شده برگردانده می‌شود. در حالت جستجوی نزدیک‌ترین نتیجه، می‌بایست داده‌ها مرتب باشد تا VLOOKUP درست عمل کند.
نکته‌ی مهم این است که تابع VLOOKUP نمی‌تواند در ستون‌های سمت چپ شماره ستون جستجو کند. به عبارت دیگر می‌بایست معمولاً عبارتی که سرچ می‌کنید در اولین ستون از محدوده‌ی سلول‌ها موجود باشد.
استفاده از VLOOUP در یک جدول از Google Sheets
با یک مثال روش کار را توضیح می‌دهیم: فرض کنید دو جدول A و B دارید که در اولین جدول، اطلاعات کارمندان شامل شناسه و تاریخ تولد قرار گرفته است. در دومین جدول شناسه کارمندان و تاریخ تولد موجود است و می‌خواهید روبروی هر شناسه، تاریخ تولد کارمند مربوطه قرار بگیرد.
آرگومان اول F4 است و در واقع اگر در سلول F4 شناسه‌ی کارمندی تایپ شد، تابع VLOOKUP آن را در محدوده‌ی A3:D9 سرچ می‌کند و زمانی که آن را در سلولی پیدا کرد، سراغ ستون چهارم می‌رود. ستون 1 در این مثال ستون حاوی شناسه کارمندی است و ستون 2 و 3 و 4 به ترتیب حاوی نام، نام خانوادگی و تاریخ تولد است. در نهایت با توجه به اینکه می‌خواهید نتایج دقیق باشد و تقریب موردنظر شما نیست، آرگومان آخر FALSE خواهد بود. بنابراین فرمولی که در سلول G4 موردنیاز است، فرمول زیر است:
=VLOOKUP(F4, A3:D9, 4, FALSE)
پس از تایپ کردن این فرمول و زدن Enter، خروجی چاپ می‌شود.
فراموش نکنید که سلول G4 را انتخاب کنید و روی مربع کوچکی که در گوشه‌ی پایین آن ظاهر شده کلیک کرده و آن را به پایین بکشید. به این ترتیب فرمول سلول G4 در سلول‌های بعدی کپی می‌شود و ریفرنس‌ها تغییر می‌کند. به عبارت دیگر محدوده‌ی سلول‌ها از A3:D9 به A4:D10 و A5:D11 تغییر خواهد کرد که مطلوب نیست! لذا می‌بایست از ریفرنس‌دهی مطلق استفاده کنید و در واقع محدوده‌ی سلول‌ها را به صورت $A$3:$D$9 تایپ کنید. برای اطلاعات بیشتر به مقاله‌ی زیر توجه فرمایید:
ترفندهای فرمول‌نویسی در اکسل با استفاده‌ی بهینه از کیبورد و کلیدهای فانکشن
به همین ترتیب اگر بخواهید روبروی شناسه‌ی کارمند، نام خانوادگی آن چاپ شود، می‌توانید از فرمول زیر استفاده کنید که صرفاً شماره ستون تغییر کرده است:
=VLOOKUP(F4, A3:D9, 3, FALSE)
نتیجه به صورت زیر خواهد بود:
استفاده از VLOOKUP در دو Sheet
در مثال قبلی همه‌ی اطلاعات در یک صفحه تعریف شده بود اما می‌توانید از VLOOKUP در جستجوی داده‌ها از صفحات مختلف نیز استفاده کنید. به عنوان مثال می‌توانید عبارتی که سرچ می‌شود را از شیت اول و محدوده‌ی سلول‌ها را از شیت دوم انتخاب کنید و خروجی را در شیت سوم چاپ کنید!
با یک مثال ساده موضوع روشن می‌شود: فرض کنید جدول اطلاعات کارمندان در اولین صفحه تعریف شده است و می‌خواهید جدول حاوی شناسه و تاریخ تولد کارمند را در شیت بعدی داشته باشید. بنابراین قبل از ذکر محدوده که A3:D9 است، می‌بایست نام شیت و علامت ! را تایپ کنید. به عنوان مثال اگر شیت اول Employees نام دارد، فرمول موردبحث به صورت زیر خواهد شد:
=VLOOKUP(A4, Employees!A3:D9, 4, FALSE)
استفاده از Wildcards در سرچ به کمک VLOOKUP
تابع VLOOKUP هم از عبارت‌های منظم یا به اصطلاح Wildcards پشتیبانی می‌کند و می‌توانید بخشی از عبارت موردنظر را سرچ کنید. به عنوان مثال اگر بخواهید سلول حاوی عبارت 12345 با سرچ کردن عبارت 234 که بخشی از آن است، به عنوان نتیجه شناسایی شود، می‌بایست از Wildcards استفاده کنید و *234* را جستجو کنید. یا مثال دیگر این است که اگر بخواهید سلولی که در ابتدای آن، عبارت abc موجود است را پیدا کنید، می‌بایست abc* را سرچ کنید. abc* به این معنی است که پس از abc ممکن است هر عبارت دلخواهی با طول نامشخص تایپ شده باشد.
اما به مثال قبلی برگردیم: فرض کنید که چند حرف از ابتدای نام کارمند مشخص است و می‌خواهید نام خانوادگی آن را با VLOOKUP پیدا کنید. اگر ابتدای نام را در سلول B12 تایپ کرده باشید، فرمول موردنیاز به صورت زیر است:
=VLOOKUP(B12, A3:D9, 2, FALSE)
حال اگر Chr* را در سلول B12 تایپ کنید، نامی که با Chr آغاز شده، به عنوان نتیجه شناسایی می‌شود و نام خانوادگی مربوطه از ستون 2 استخراج شده و نمایش داده می‌شود.
پیدا کردن نزدیک‌ترین نتیجه با VLOOKUP
همان‌طور که در ابتدای مقاله اشاره کردیم، آرگومان آخر تابع VLOOKUP مشخص می‌کند که آیا به دنبال نتیجه‌ی دقیق هستید یا می‌خواهید نزدیک‌ترین نتیجه را پیدا کنید. در مثال‌های قبلی از FALSE استفاده کردیم که برای یافتن نتیجه‌ی دقیق کاربرد دارد.
حالت تقریبی را با یک مثال ساده‌ی دیگر توضیح می‌دهیم: فرض کنید جدولی حاوی قیمت چند کالا دارید و می‌خواهید با وارد کردن قیمت در سلولی، یک کالا را پیدا کنید. ممکن است قیمت وارد شده دقیقاً با قیمت یکی از کالاها برابر نباشد! بنابراین نتیجه‌ی تقریبی مطلوب است.
نکته بسیار مهم در پیدا کردن نزدیک‌ترین نتیجه این است که می‌بایست قیمت کالاها به صورت مرتب‌شده باشد تا VLOOKUP درست عمل کند.
پس از مرتب‌سازی قیمت‌ها از کم به زیاد، فرمول موردنیاز به این صورت است:
=VLOOKUP(D4, A4:B9, 2, TRUE)
به عبارت دیگر فرمول تابع VLOOKUP مقداری که در سلول D4 تایپ شده را در محدوده‌ی A4:B9 سرچ می‌کند و پس از یافتن نزدیک‌ترین عدد به آن، آنچه در سلول روبرو یا ستون دوم ذکر شده را برمی‌گرداند.
حال اگر عدد ۱۷ دلار را تایپ کنید که در سلول‌های ستون اول موجود نیست، نزدیک‌ترین رقم به آن که ۱۵ دلار است، انتخاب می‌شود و نام کالای روبروی آن که Bag است، برگردانده می‌شود:
howtogeekسیاره‌ی آی‌تی
فرمول‌نویسی در اکسل نسبتاً ساده است اما اگر کاربر حرفه‌ای باشید و بخواهید روابط پیچیده‌ای بنویسید، بهتر است با ترفندهای فرمول‌نویسی مثل استفاده از کلیدهای F1 الی F12 آشنا شوید تا هم سرعت کار بیشتر شود و هم میزان اشتباهات کمتر شود. به عنوان مثال با زدن کلید F9 حین ویرایش یک فرمول، می‌توانید حاصل بخشی از فرمول را ببینید و یا با زدن کلید F2 می‌توانید حالت ویرایش فرمول و یا حالت انتخاب سلول را فعال کنید.
در این مقاله به چند نکته و ترفند بسیار مفید و کاربردی برای فرمول‌نویسی در Excel مجموعه‌ی آفیس مایکروسافت می‌پردازیم.
فعال کردن حالت ویرایش فرمول با زدن F2 یا دبل‌کلیک
زمانی که سلولی انتخاب شده است، فرمول یا مقدار داخل آن را در نوار ویرایش فرمول در بالای صفحه نمایش داده می‌شود و می‌توانید از این بخش برای تغییر فرمول استفاده کنید.
روش دیگر فعال کردن حالت ویرایش سلول و حالت انتخاب سلول است:
اگر کلید F2 را فشار بدهید، حالت ویرایش سلول فعال می‌شود. درست مثل کاربرد کلید F2 در فایل اکسپلورر ویندوز که حالت تغییر نام فایل را فعال می‌کند. روش دیگر این است که روی سلول دبل‌کلیک کنید.
زمانی که حالت ویرایش سلول را فعال کرده‌اید، اگر کلید → یا ← را فشار دهید، سلول بعدی یا قبلی انتخاب نمی‌شود بلکه نشانگر تایپ در فرمول عقب و جلو می‌رود.
حال اگر یک بار دیگر F2 را فشار بدهید، حالت اشاره‌گر فعال می‌شود. در این حالت اگر کلیدهای جهت چپ یا جهت راست کیبورد را فشار بدهید، می‌توانید سلول‌ها را انتخاب کرده و در فرمول‌نویسی استفاده کنید. البته روش کلیک روی سلول‌ها در فرمول‌نویسی نیز بسیار ساده و سریع است.
همان‌طور که در گوشه‌ی پایین تصویر فوق مشاهده می‌کنید، حالت ویرایش یا Edit و حالت اشاره‌گر یا Point، در نوار وضعیت پایین صفحه نمایش داده می‌شود.
با زدن کلید F2 می‌توانید بین دو حالت Edit و Point سوییچ کنید و لذا ویرایش کردن فرمول بدون استفاده از موس امکان‌پذیر است. البته گاهی این روش سریع‌تر است و گاهی استفاده از موس گزینه‌ی بهتری است. به عنوان مثال در یک Sheet بسیار بزرگ اگر Ctrl + Shift + End را فشار دهید، می‌توانید به سرعت محدوده‌ای از سلول‌ها را انتخاب کنید در حالی که این کار با استفاده از موس و روش اسکرول کردن، وقت‌گیر است.
سوییچ بین ریفرنس‌های مطلق و نسبی با کلید F4
در اکسل منظور از ریفرنس مطلق این است که آدرس سلول در فرمول‌نویسی ثابت باشد و با کپی کردن فرمول در سلول‌های مجاور، آدرس سلولی که در فرمول به کار رفته، تغییر نکند. در حالت نسبی که حالت پیش‌فرض است، آدرس‌ها متناسب با سلولی که فرمول در آن پیست شده، تغییر می‌کند.
Absolute Reference و Relative Reference یا به عبارت دیگر آدرس‌دهی مطلق و آدرس‌دهی نسبی هم در Excel و هم در بسیاری از نرم‌افزارهای مشابه اکسل وجود دارد که نمونه‌ی محبوب آن Google Sheets است. برای اطلاعات بیشتر به مقاله‌ی زیر مراجعه فرمایید:
آموزش ضبط ماکرو و اجرای Macro و ایمپورت در Google Sheets
برای آدرس‌دهی مطلق می‌بایست قبل از نام ستون و شماره‌ی ردیف، از نماد $ استفاده کنید. تایپ کردن این نماد وقت‌گیر است و به جای این روش می‌توانید کلید F4 را فشار دهید!
به عبارت دیگر نشانگر تایپ را پس از نام سلول یا روی آن قرار بدهید و F4 را فشار دهید. نماد $ قبل از ستون و ردیف اضافه می‌شود. اگر یک مرتبه‌ی دیگر F4 را فشار دهید، نماد $ فقط قبل از شماره ردیف اضافه می‌شود و با فشار دادن مجدد، این نماد فقط قبل از نام ستون ذکر می‌شود. برای سوییچ به حالت نسبی یا عادی، می‌بایست F4 را یک مرتبه‌ی دیگر فشار بدهید. به عنوان مثال اگر سلول A2 در فرمول تایپ شده باشد، حالت‌های زیر اتفاق می‌افتد:
- 1 بار زدن F4: آدرس‌دهی مطلق به صورت $A$2
- 2 بار زدن F4: آدرس‌دهی ردیف به صورت مطلق یا A$2
- 2 بار زدن F4: آدرس‌دهی ستون به صورت مطلق یا $A2
- 4 بار زدن F4: آدرس‌دهی نسبی یا A2
کلید F4 روی بخشی از فرمول نیز عمل می‌کند. به عبارت دیگر اگر بخشی از فرمول را با کلیک و درگ توسط موس یا با نگه داشتن کلید Shift و زدن کلیدهای جهت انتخاب کرده باشید هم می‌توانید F4 را فشار دهید تا تمام سلول‌های بخش انتخاب شده، به صورت مطلق آدرس‌دهی شوند.
انتخاب آرگومان‌های توابع با کلیک روی لینک در توضیحات
در اکسل استفاده از توابع ساده که یک یا دو آرگومان دارند ساده است اما اگر در فرمولی از چند تابع به صورت تودرتو استفاده کرده باشید یا تابع استفاده شده چندین آرگومان داشته باشد، ممکن است کار مشکل شود. باید محل ویرگول و دونقطه و پرانتز را به دقت چک کنید تا اشتباهی رخ ندهد.
یک روش سریع برای انتخاب کردن هر یک از آرگومان‌ها در فرمول‌نویسی این است که در توضیح تابع روی آرگومان موردنظر که به شکل لینک است، کلیک کنید، مثل تصویر زیر:
در این حالت برای پاک کردن آرگومان می‌توانید Del را فشار دهید و برای تغییر آدرس‌ها به حالت مطلق می‌توانید F4 را فشار دهید.
جابجایی کادر توضیح توابع
برای تغییر دادن موقعیت کادر توضیح توابع، می‌توانید موس را روی مرز کادر قرار بدهید تا به شکل فلش چهارجهته تغییر کند. در این حالت کلیک کرده و موس را حرکت بدهید و سرانجام کلید چپ را رها کنید. این ترفند در مواقعی که کادر توضیح مزاحم است و روی سلول‌هایی که می‌خواهید مشاهده کنید قرار گرفته، مفید واقع می‌شود.
محاسبه کردن حاصل بخشی از فرمول طولانی با F9
در فرمول‌نویسی پیشرفته و پیچیده ممکن است فرمول بسیار طولانی شود. اگر بخواهید نتیجه‌ی هر بخش از فرمول را حین ویرایش فرمول طولانی چک کنید، می‌توانید آن بخش را انتخاب کرده و کلید F9 را فشار بدهید! با زدن این کلید، بخش انتخاب شده با مقداری که حاصل محاسبه‌ی آن است جایگزین می‌شود.
و دو نکته‌ی بسیار مهم:
پس از جایگزینی بخشی از فرمول با نتیجه‌ی محاسبه شدن آن، نباید Enter را فشار دهید بلکه می‌بایست Esc را فشار دهید تا فرمول تغییر نکند.
دقت کنید که بخش انتخاب شده می‌بایست کامل باشد. به عنوان مثال پرانتز شروع و پرانتز پایان یک تابع به صورت کامل انتخاب شده باشد چرا که در غیر این صورت محاسبه انجام نمی‌شود و پیام خطا دریافت می‌کنید.
نمایش پنجره‌ی ارزیابی فرمول یا Evaluate Formula
برای نمایش پنجره‌ای به اسم Evaluate Formula از منوی Formulas بالای صفحه و گزینه‌ی Evaluate Formula استفاده کنید. در این پنجره می‌توانید حاصل یک فرمول پیچیده را مرحله به مرحله بررسی کنید. کافی است کلید Space کیبورد را فشار دهید یا روی دکمه‌ی Evaluate کلیک کنید.
و چند نکته‌ی کاربردی در مورد این پنجره:
- زیر بخشی از فرمول که مقدار آن محاسبه شده، خط کشیده می‌شود.
- با کلیک روی Step In یا Step Out می‌توانید حاصل محاسبه‌ی مرحله‌ی بعدی و قبلی را چک کنید.
- اگر پنجره‌ی ارزیابی فرمول را ببندید، فرمول تغییر نمی‌کند و شاید بهتر از زدن F9 باشد.
کلید Fn لپ‌تاپ‌ها و برخی کیبوردها را فراموش نکنید!
در برخی کیبوردهای USB و همین‌طور در اغلب لپ‌تاپ‌ها یک کلید خاص به اسم Fn موجود است. اگر این کلید را نگه دارید و کلیدهایی مثل F1 الی F12 را فشار دهید، عملکرد کلید تغییر می‌کند و به عنوان مثال صدا کم و زیاد می‌شود یا روشنایی صفحه تغییر می‌کند و یا وای-فای خاموش و روشن می‌شود. در برخی کیبوردها وضعیت برعکس است، به عبارت دیگر برای فعال کردن عملکرد عادی F1 الی F12 می‌بایست Fn را نگه دارید و دکمه‌هایی مثل F1 الی F12 را فشار بدهید!
بنابراین اگر در کار با Excel و هر نرم‌افزار دیگری متوجه شدید که F1 الی F12 که کلیدهای فانکشن گفته می‌شود، درست کار نمی‌کنند، Fn را نگه دارید و کلید موردنظر را فشار بدهید.
معمولاً در تنظیمات بایوس لپ‌تاپ گزینه‌ای برای تغییر کارکرد کلیدهای فانکشن و Fn وجود دارد. به عبارت دیگر می‌توانید کاری کنید که F1 الی F12 عملکرد عادی را داشته باشند و زمانی که Fn به همراه این کلیدها فشار داده شد، عمل جایگزین مثل کاهش و افزایش بلندی صدا انجام شود. به همین ترتیب ممکن است کلیدی به اسم Fn Lock روی کیبورد وجود داشته باشد که همین کار را انجام می‌دهد.
کلیدهای ورود به بایوس یا UEFI مادربوردها و لپ‌تاپ‌های مختلف و روشی ساده در ویندوز 8 و ۱۰
روش حل مشکل کار نکردن Num Lock و بخش اعداد کیبورد در ویندوز ۱۰
excelcampusسیاره‌ی آی‌تی