نحوه استفاده از فرمول های آرایه در برگه های گوگل

در اوایل سال 2023، گوگل چندین عملکرد جدید را معرفی کرد برای Sheets، از جمله هشت مورد برای کار با آرایه‌ها. با استفاده از این توابع، می توانید یک آرایه را به یک ردیف یا ستون تبدیل کنید، یک آرایه جدید از یک ردیف یا ستون ایجاد کنید، یا یک آرایه فعلی را اضافه کنید.

با انعطاف‌پذیری بیشتر برای کار با آرایه‌ها و فراتر رفتن از تابع اصلی ARRAYFORMULA، بیایید نحوه استفاده از این توابع آرایه با فرمول ها در Google Sheets را بررسی کنیم.

نکته: اگر از Microsoft Excel نیز استفاده می‌کنید، ممکن است برخی از این توابع برای شما آشنا به نظر برسند.

تبدیل یک آرایه: TOROW و TOCOL

اگر آرایه ای در مجموعه داده خود دارید که می خواهید آن را به یک ردیف یا ستون تبدیل کنید، می توانید از توابع TOROW و TOCOL استفاده کنید.

نحو برای هر تابع یکسان است، TOROW(آرایه، نادیده گرفتن، اسکن)و TOCOL(آرایه، نادیده گرفتن، اسکن)که تنها آرگومان اول مورد نیاز است. برای هر دو.

آرایه: آرایه‌ای که می‌خواهید تبدیل کنید، با فرمت «A1:D4».

نادیده گرفتن: به طور پیش فرض، هیچ پارامتری نادیده گرفته نمی شود (0)، اما می توانید از 1 برای نادیده گرفتن نقاط خالی، 2 برای نادیده گرفتن خطاها، یا 3 برای نادیده گرفتن نقاط خالی و خطاها استفاده کنید.

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

بیایید چند مثال را با استفاده از توابع TOROW و TOCOL و فرمول‌های آنها مرور کنیم.

در این مثال اول، آرایه A1 تا C3 را می گیریم و با استفاده از آرگومان های پیش فرض با این فرمول، آن را به یک ردیف تبدیل می کنیم:

=TOROW(A1:C3)

همانطور که می بینید، آرایه اکنون در یک ردیف است. از آنجایی که از آرگومان پیش‌فرض scanاستفاده کردیم، تابع از چپ به راست (A, D, G)، پایین و سپس دوباره از چپ به راست (B, E, H) می‌خواند تا کامل شود – اسکن شده توسط ردیف.

برای خواندن آرایه با ستون به جای ردیف، می‌توانیم از Trueبرای آرگومان scanاستفاده کنیم. آرگومان نادیدهرا خالی می گذاریم. این فرمول است:

=TOROW(A1:C3,,TRUE)

اکنون می بینید که تابع آرایه را از بالا به پایین (A، B، C)، از بالا به پایین (D، E، F) و از بالا به پایین (G، H، I) می خواند..

تابع TOCOL به همین صورت عمل می کند اما آرایه را به یک ستون تبدیل می کند. با استفاده از همان محدوده، از A1 تا C3، فرمول با استفاده از آرگومان‌های پیش‌فرض در اینجا آمده است:

=TOCOL(A1:C3)

دوباره، با استفاده از پیش‌فرض برای آرگومان scan، تابع از چپ به راست خوانده می‌شود و نتیجه را به این صورت ارائه می‌کند.

برای خواندن آرایه بر اساس ستون به جای ردیف، Trueرا برای آرگومان scanمانند این وارد کنید:

=TOCOL(A1:C3,,TRUE)

اکنون می بینید که تابع به جای آن آرایه را از بالا به پایین می خواند.

ایجاد یک آرایه جدید از ردیف ها یا ستون ها: CHOOSEOWS و CHOOSECOLS

شما ممکن است بخواهید یک آرایه جدید از آرایه موجود ایجاد کنید. این به شما امکان می دهد یک محدوده سلولی جدید با مقادیر خاص از دیگری ایجاد کنید. برای این کار، از CHOSEROWS و CHOOSECOLS توابع Google Sheets استفاده خواهید کرد.

نحو هر تابع مشابه است، CHOOSEROWS (آرایه، row_num، row_num_opt)و CHOOSECOLS (آرایه، col_num، col_num_opt)،که در آن دو آرگومان اول مورد نیاز است. برای هر دو.

آرایه: آرایه موجود که به صورت "A1:D4" قالب بندی شده است.

Row_numیا Col_num: شماره اولین سطر یا ستونی که می‌خواهید برگردانید.

Row_num_optیا Col_num_opt: اعداد ردیف‌ها یا ستون‌های اضافی که می‌خواهید برگردانید. Google به شما از اعداد منفی استفاده کنید پیشنهاد می‌کند تا ردیف‌ها را از پایین به بالا یا ستون‌ها را از راست به چپ برگردانید.

بیایید به چند مثال با استفاده از CHOOSEROWS و CHOOSECOLS و فرمول‌های آنها نگاه کنیم.

در این مثال اول، از آرایه A1 تا B6 استفاده خواهیم کرد. می‌خواهیم مقادیر ردیف‌های 1، 2 و 6 را برگردانیم. فرمول اینجاست:

=CHOOSEOWS(A1:B6,1,2,6)

همانطور که می بینید، ما آن سه ردیف را برای ایجاد آرایه جدید خود دریافت کردیم.

برای مثال دیگر، از همان آرایه استفاده خواهیم کرد. این بار می‌خواهیم ردیف‌های 1، 2 و 6 را با ترتیب معکوس برگردانیم. می توانید از اعداد مثبت یا منفی برای دریافت همان نتیجه استفاده کنید.

با استفاده از اعداد منفی، از این فرمول استفاده می کنید:

=CHOOSEOWS(A1:B6,1,-1,-5).

برای توضیح، 1 اولین ردیفی است که برمی‌گردد، -1 ردیف دومی است که از پایین شروع می‌شود، و -5 ردیف پنجم از پایین است.

با استفاده از اعداد مثبت، از این فرمول برای به دست آوردن نتیجه مشابه استفاده می کنید:

=CHOOSEOWS(A1:B6,1,6,2)

عملکرد CHOOSECOLS به طور مشابه کار می کند، با این تفاوت که وقتی می خواهید یک آرایه جدید از ستون ها به جای ردیف ها ایجاد کنید، از آن استفاده می کنید.

با استفاده از آرایه A1 تا D6، می‌توانیم ستون‌های 1 (ستون A) و 4 (ستون D) را با این فرمول برگردانیم:

=CHOOSECOLS(A1:D6,1,4)

اکنون آرایه جدید خود را فقط با آن دو ستون داریم.

به عنوان مثالی دیگر، از همان آرایه استفاده خواهیم کرد که با ستون 4 شروع می شود. سپس ابتدا ستون های 1 و 2 را با 2 (ستون B) اضافه می کنیم. می توانید از اعداد مثبت یا منفی استفاده کنید:

=CHOOSECOLS(A1:D6,4,2,1)

=CHOOSECOLS(A1:D6,4,-3,-4)

همانطور که در تصویر بالا می بینید، با فرمول های موجود در سلول ها به جای نوار فرمول، با استفاده از هر دو گزینه نتیجه یکسانی را دریافت می کنیم.

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

Wrap برای ایجاد یک آرایه جدید: WRAPROWS و WRAPCOLS

اگر می‌خواهید یک آرایه جدید از آرایه موجود ایجاد کنید، اما ستون‌ها یا ردیف‌ها را با تعداد معینی از مقادیر در هر کدام بپیچید، می‌توانید از توابع WRAPROWS و WRAPCOLS استفاده کنید.

نحو برای هر تابع یکسان است، WRAPROWS (محدوده، تعداد، پد)و WRAPCOLS (محدوده، شمارش، پد)،که در آن دو آرگومان اول هستند. برای هر دو مورد نیاز است.

محدوده: محدوده سلولی موجودی که می‌خواهید برای یک آرایه استفاده کنید، به‌صورت «A1:D4» قالب‌بندی شده است.

Count: تعداد سلول‌ها برای هر سطر یا ستون.

Pad: می‌توانید از این آرگومان برای قرار دادن متن یا یک مقدار واحد در سلول‌های خالی استفاده کنید. این جایگزین خطای #N/A می‌شود که برای سلول‌های خالی دریافت می‌کنید. متن یا مقدار را در علامت نقل قول قرار دهید.

بیایید چند مثال را با استفاده از توابع WRAPROWS و WRAPCOLS و فرمول‌های آنها مرور کنیم..

در این مثال اول، از محدوده سلولی A1 تا E1 استفاده خواهیم کرد. ما یک ردیف جدید با سه مقدار در هر ردیف ایجاد می کنیم. این فرمول است:

=WRAPROWS(A1:E1,3)

همانطور که می بینید، یک آرایه جدید با نتیجه صحیح داریم، سه مقدار در هر ردیف. چون یک سلول خالی در آرایه داریم، خطای #N/A نمایش داده می شود. برای مثال بعدی، از آرگومان padبرای جایگزینی خطا با متن "هیچ‌کدام" استفاده می‌کنیم. این فرمول است:

=WRAPROWS(A1:E1,3,”None”)

اکنون، می‌توانیم به جای خطای Google Sheets، کلمه‌ای را ببینیم.

عملکرد WRAPCOLS با ایجاد یک آرایه جدید از محدوده سلولی موجود، همین کار را انجام می دهد، اما این کار را با قرار دادن ستون ها به جای ردیف انجام می دهد.

در اینجا، از همان آرایه، از A1 تا E3 استفاده می‌کنیم و ستون‌هایی با سه مقدار در هر ستون قرار می‌دهیم:

=WRAPCOLS(A1:E1,3)

مانند مثال WRAPROWS، نتیجه صحیح را دریافت می‌کنیم، اما به دلیل سلول خالی یک خطا را نیز دریافت می‌کنیم. با استفاده از این فرمول، می توانید از آرگومان padبرای اضافه کردن کلمه "Empty" استفاده کنید:

=WRAPCOLS(A1:E1,3,”Empty”)

این آرایه جدید با یک کلمه به جای خطا بسیار بهتر به نظر می رسد.

برای ایجاد یک آرایه جدید ترکیب کنید: HSTACK و VSTACK

دو تابع نهایی که ما به آنها نگاه خواهیم کرد برای الحاق آرایه ها هستند. با HSTACK و VSTACK، می‌توانید دو یا چند محدوده سلول را به یکدیگر اضافه کنید تا یک آرایه واحد را به صورت افقی یا عمودی تشکیل دهید.

نحو برای هر تابع یکسان است، HSTACK (range1, range2,…)و VSTACK (range1, range2,…)،که تنها آرگومان اول است ضروری. با این حال، شما تقریبا همیشه از آرگومان دوم استفاده خواهید کرد که محدوده دیگری را با آرگومان اول ترکیب می کند.

محدوده 1: اولین محدوده سلولی که می‌خواهید برای آرایه استفاده کنید، به صورت «A1:D4» قالب‌بندی شده است.

Range2,…: دومین محدوده سلولی که می خواهید برای ایجاد آرایه به سلول اول اضافه کنید. می توانید بیش از دو محدوده سلولی را ترکیب کنید.

بیایید به چند نمونه از HSTACK و VSTACK و فرمول‌های آنها نگاه کنیم.

در این مثال اول، با استفاده از این فرمول، محدوده‌های A1 تا D2 را با A3 تا D4 ترکیب می‌کنیم:.

=HSTACK(A1:D2,A3:D4)

می‌توانید محدوده داده ترکیب شده است ما را برای تشکیل یک آرایه افقی مشاهده کنید.

برای مثالی از تابع VSTACK، ما سه محدوده را با هم ترکیب می کنیم. با استفاده از فرمول زیر، از محدوده های A2 تا C4، A6 تا C8 و A10 تا C12 استفاده می کنیم:

=VSTACK(A2:C4,A6:C8,A10:C12)

اکنون، ما یک آرایه با تمام داده‌هایمان با استفاده از یک فرمول در یک سلول داریم.

آرایه ها را به راحتی دستکاری کنید

در حالی که می‌توانید در شرایط خاصی از آرای فرمول استفاده کنید، مانند عملکرد SUM یا تابع IF، این فرمول‌های آرایه کاربرگ‌نگار Google اضافی می‌توانند در وقت شما صرفه‌جویی کنند. آنها به شما کمک می کنند برگه خود را دقیقاً همانطور که می خواهید و با یک فرمول آرایه واحد مرتب کنید.

برای آموزش های بیشتر مانند این، اما با توابع غیر آرایه، به نحوه از COUNTIF استفاده کنید یا تابع SUMIF در Google Sheets نگاه کنید.

.

پست های مرتبط: