نوشتن فرمول در یک سلول در نرم‌افزارهایی مثل گوگل شیتس و اکسل ساده است و به همین ترتیب کپی کردن آن در سلولی دیگر ساده است. اما اگر بخواهید فرمولی بنویسید و در تعداد زیادی سلول پایین‌تر کپی کنید، روش کپی و پیست مناسب نیست. ترفندهای سریع‌تری برای این کار وجود دارد. حتی می‌توانید با استفاده از تابع ArrayFormula کاری کنید که با اضافه شدن ردیف جدید، به صورت خودکار فرمول در سلول‌های موردنظر درج شود.
در ادامه به نحوه کپی کردن فرمول در سلول‌های یک ستون و همین‌طور اضافه کردن خودکار فرمول در ردیف‌های جدید در Google Sheets می‌پردازیم.
قبل از هر چیز توجه کنید که با کپی و پیست کردن فرمول، ریفرنس‌هایی که به سلول‌های دیگر داده شده، تغییر می‌کند، مگر آنکه از ریفرنس‌های مطلق در اشاره به سلول‌های دیگر استفاده کرده باشید.
شاید برخی کاربران مبتدی، برای کپی کردن فرمول در سلول‌های ستون یا ردیف، از روش کپی و پیست استفاده کنند اما مسأله این است که کپی و پیست کردن روش وقت‌گیری است که حتی احتمال خطا و اشتباه کاربر را افزایش می‌دهد.
کاربرد مربع کوچک گوشه‌ی راست و پایین سلول‌ها در Excel و Google Sheets
زمانی که روی سلولی از جدول در نرم‌افزار Excel مایکروسافت و نرم‌افزارهای مشابه نظیر گوگل شیتس کلیک می‌کنید، در گوشه‌ی راست و پایین سلول یک مربع کوچک نمایش داده می‌شود. این مربع کوچک برای پرسازی خودکار یا Auto Fill در نظر گرفته شده و بسیار مفید است چرا که اگر روی آن کلیک کرده و با نگه داشتن کلید چپ موس، موس را در جهتی حرکت بدهید، فرمولی که در سلول درج شده در سلول‌های اطراف پیست می‌شود. دقت کنید که حین درگ کردن مربع کوچک، نشان‌گر موس به شکل + تغییر می‌کند.
درگ کردن مربع کوچک گوشه‌ی سلول‌ها موجب کپی شدن فرمت و استایل سلول در سلول‌های مجاور نیز می‌شود. به عنوان مثال اگر حاشیه‌ی سلول یا Border آن خط ضخیم باشد و رنگ داخل سلول زرد باشد و فونت و اندازه‌ی فونت را به شکل خاصی تنظیم کرده باشید، تمام این موارد نیز در سلول‌های بعدی اعمال می‌شود.
اما برخی سلول‌های دارای فرمول نیستند. در این صورت این سوال مطرح می‌شود:
اگر سلول حاوی عدد یا متن باشد و مربع کوچک آن را درگ کنیم، چه اتفاقی می‌افتد؟
اگر سلول حاوی فرمول نباشد نیز مقدار آن در سلول‌های اطراف کپی می‌شود. اگر دو یا چند سلول را انتخاب کنید و مربع کوچک را درگ کنید، پر کردن خودکار و هوشمندانه انجام می‌شود. به عنوان مثال اگر دو سلول مجاور حاوی عدد ۱ و ۲ را انتخاب کنید و سپس مربع را درگ کنید، در سلول‌های اطراف اعداد ۳ و ۴ و ۵ و ۶ و ... چاپ می‌شود.
همان‌طور که می‌بینید کاربرد مربع کوچک گوشه‌ی سلول‌ها بسیار زیاد است.
اگر بخواهید فقط فرمول را در سلول‌های بعدی پیست کنید و در واقع از تغییر فرمت و استایل سلول‌های بعدی جلوگیری کنید، استفاده از مربع کوچک مناسب نیست. برای این حالت می‌بایست ابتدا سلول اول را انتخاب کرده و با زدن کلید میانبر 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 و آرگومان‌های آن به مقاله‌ی زیر توجه فرمایید:
اما به مثال قبلی برگردیم که محاسبه ۱۸ درصد قیمت محصولات و رند کردن آن است. فرمول سلول 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 را فشار می‌دهیم. نتیجه را در تصویر زیر مشاهده می‌کنید. تا سطر ۶ که قیمت محصولات درج شده، محاسبه صورت گرفته و سلول‌های پایین‌تر خالی باقی می‌ماند.
برای چک کردن خالی بودن سلول‌ها دو روش مطرح کردیم و روش دیگر این است که شرط تصاوی 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 چک کنید. اگر شماره ردیف ۱ است و در واقع اگر سلول فعلی در ردیف اول قرار دارد، می‌بایست عنوان ستون درج شود و اگر ۲ یا بیشتر است، مقدار در ۱۸ درصد ضرب شده و رند می‌شود.
فرمول آرایه‌ای مثال ما به صورت زیر خواهد بود:
=ArrayFormula(IF(ROW(B:B)=1,"Tax",IF(ISBLANK(B:B),"",ROUND(B:B*18%, 2))))
و حاصل کار:
استفاده از فرمول‌نویسی آرایه‌ای برای پر کردن خودکار سطرهای جدید
فرمول‌نویسی به کمک تابع ArrayFormula در ساخت فرم‌های آنلاین Google Forms نیز بسیار مفید است. به عنوان مثال می‌توانید پاسخ‌هایی که به فرم آنلاین داده شده را در ردیفی جدید از یک صفحه در گوگل شیتس ذخیره کنید.
اگر فرمول‌نویسی عادی انجام داده باشید، طبعاً می‌بایست فایل گوگل شیتس را باز کرده و در سلول‌های ردیف‌های جدید، فرمول‌هایی را کپی و پیست کنید. اما در صورت استفاده از فرمول‌نویسی آرایه‌ای، محاسبات لازم در مورد هر پاسخ جدیدی که ثبت شده به صورت خودکار انجام می‌شود و نیازی به کپی و پیست کردن فرمول‌ها در ردیف‌های جدید نیست
چگونه از VLOOKUP در فرمول‌نویسی آرایه‌ای استفاده کنیم؟
تابع VLOOKUP برای جستجو در سلول‌ها کاربرد دارد و می‌توانید آن را با تابع ARRAYFORMULA به شکل ترکیبی استفاده کنید.
به یک مثال ساده توجه کنید: فرض کنید نام میوه‌ها در صفحه‌ی اول فایل گوگل شیتس و در ستون A درج شده و قیمت هر محصول در ستون B ذکر شده است. در صفحه‌ی دوم از این فایل، نام میوه‌ها در ستون اول ذکر شده و تعداد سفارش یا فروش، در ستون B درج شده است. هدف شما محاسبه کردن مجموع فروش هر محصول در ستون C است.
اگر ستون A در این دو صفحه، ترتیب مشابه نداشته باشد، می‌بایست از تابع VLOOKUP برای یافتن نام میوه و قیمت آن در صفحه‌ی اول استفاده کنید و سپس قیمت را در تعداد فروش ضرب کنید تا مجموع فروش به دست آید.
برای این مسأله از فرمول نسبتاً پیچیده و در عین حال ساده‌ای استفاده می‌کنیم:
- ابتدا بررسی می‌کنیم که شماره‌ی ردیف ۱ است یا بیشتر. اگر ردیف اول است، عنوان درج می‌شود.
- اگر شماره‌ی ردیف ۲ یا بیشتر است، ابتدا شرط پر بودن سلول بررسی می‌شود.
- در صورت پر بودن سلول نام محصول، نام میوه در ستون A از صفحه‌ی اول که Fruits نام دارد، به کمک VLOOKUP سرچ می‌شود و قیمت آن که در ستون شماره ۲ از همان صفحه قرار گرفته، استخراج می‌شود.
- در نهایت قیمت در تعداد ضرب می‌شود. تمام این مراحل در فرمول زیر انجام می‌شود:
=ArrayFormula(
IF(ROW(A:A)=1,
"Total",
IF(NOT(ISBLANK(A:A)), VLOOKUP(A:A, Fruits!A2:B6, 2, FALSE) * B:B, "")))
همان‌طور که مشاهده می‌کنید روش کار با تابع فرمول‌نویسی آرایه‌ای یا ArrayFormula پس از کمی تجربه، ساده و درست مشابه فرمول‌نویسی معمولی است.
labnolسیاره‌ی آی‌تی