با استفاده از تابع CONCAENATE به راحتی سلول ها رو با هم ادغام کن. CONCATENATE به معنای پیوند دادن است. در گوگل شیتس هر وقت بخواهید محتویات چند سلول را کنار هم قرار دهید از تابع CONCATENATE استفاده میشود. در مقاله آسان ترین راه حل ها را برای پیوند دادن محتواهای سلول ها یاد میگیریم پس با ما همراه باش. هرچه تعداد داده های شما بیشتر باشد؛ ممکن است بیشتر با پیوند دادن محتویات سلول ها به یکدیگر مواجه شوید. علاوه بر این گاهی ممکن است بخواهید نه تنها تمام محتویات داخل سلول ها را داشته باشید بلکه میخواهید برخی جداکننده ها مثل ویرگول ها، فاصله ها، خط تیره یا … را نیز اضافه کنید یا حتی محتویات آن سلول ها را به جای جدا کننده با یک عبارت متنی از یکدیگر جدا کنید. گوگل شیتس برای این کار چندین ابزار معرفی کرده است. فهرست مطالب: • تابع CONCAT گوگل شیتس • عملگر پیوند (&) • نحوه استفاده از تابع CONCATENATE در گوگل شیتس o پیوند متن و عدد در گوگل شیتس o پیوند به همراه جدا کننده ها o پیوند (اما قرار گرفتن هر کدام در یک خط در یک سلول) o ترکیب ساعت و تاریخ

تابع CONCAT گوگل شیتس

ساختار این تابع بسیار شبیه تابع CONCATENATE است. به ساختار این تابع دقت کنید:

=CONCATENATE(VALUE1,VALUE2)

این تابع دو آرگومان دارد که به شرح زیر هستند: Value1: مقداری که قرار است به value2 پیوند بخورد. Vlaue2: مقداری که قرار است پیوند بخورد. برای اینکه دو مقدار عددی یا متنی را به هم پیوند بزنید. باید آن دو مقدار را در فرمول داخل دابل کوتیشن قرار دهید. مانند فرمول زیر:

=CONCAT(“2019:”,the lion king”)

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

=CONCAT(A2,B2)

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

=CONCAT(A2:A11,B2:B11)

عملگر پیوند (&)

گوگل شیتس عمگرهای متفاوتی برای اهداف مختلف دارد. پیوند دادن نیز از این قاعده مستثنی نیست. استفاده از علامت & (ampersand) در فرمول ها به جای تابع concat نتیجه یکسانی را برای شما به ارمغان می آورد:

=A2&B2

در ادامه خواهید دید که این عملگر انعطاف پذیر از تابع CONCAT است. مگه چه کارهایی میتونه انجام بده؟: 1- بیشتر از دو مقدار رو میتونه با هم ترکیب کنه:

=A2&B2&C2

2- نه تنها میتونه چندین سلول رو با هم ادغام کنه، بلکه میتونه با کاراکترهای مختلف مقادیر رو از هم جدا کنه:

=A2&" "&B2&"; "&C2

اگر تا الان نتیجه ای که میخواستید رو از این دو روش نگرفتید. یک روش سومی هم وجود داره. بریم سراغ تابع CONCATENATE

نحوه استفاده از تابع CONCATENATE در گوگل شیتس

بیایید ساختار این تابع رو بررسی کنیم:

=CONCATENATE(string1, [string2, ...])

String1: اولین رشته ای است که میخواهید سایر مقادیر را به آن پیوند دهید. این آرگومان اجباری است. String2: … همه رشته هایی است که میخواهید به رشته اول اضافه کنید. این آرگومان اختیاری است.

پیوند متن و عدد در گوگل شیتس

به دو فرمول زیر توجه کنید:

=CONCATENATE(A2,B2,C2)

=CONCATENATE(A2:D2)

میتوانید مانند فرمول یک هر سلول را به عنوان یک آرگومان در نظر بگیرید. یا مانند فرمول دوم یک محدوده در یک آرگومان تعریف کنید.

اولین مزیت این تابع این است که میتواند بیش از دو سلول را با متن و عدد به یکدیگر پیوند دهد.

پیوند به همراه جدا کننده ها با استفاده از تابع CONCATENATE گوگل شیتس

ترکیب کردن سلول ها در گوگل شیتس نصفی از کار هست برای اینکه نتیجه زیبا و واقعی به نظر برسه، شما باید از یکسری کاراکترهای اضافی استفاده کنید. اگر فرمول را همانطور که هست بنویسید، متن تک تک سلول ها را به هم میچسباند و نتیجه به صورت زیر میشود: BonnieJacksonIn، BonnieJacksonCA اما خبر خوب اینه که تابع CONCATENATE گوگل شیتس کاراکترها را به عنوان آرگومان در نظر میگیره. یعنی شما میتونید در فرمول به راحتی از جداکننده ها استفاده کنید. در ادامه با یک مثال این مفهوم رو بهتر درک خواهید کرد. به فرمول زیر دقت کنید. حواستون باشه که حتما جداکننده رو بین دابل کوتیشن قرار بدید:

=CONCATENATE(A2," ",B2,", ",C2)

در این مثال میخوام که A2 رو از B2 با فاصله و B2 رو از C2 با کاما و فاصله جدا کنم:

شما تقریبا میتونید از هر کاراکتری در این تابع استفاده کنید. اما اگر بخواین هر قسمت توی یک خط قرار بگیره یکم مسیر فرق میکنه که توی قسمت های بعدی این مطلب بهش پرداختیم. نکته: وقتی که شما میخواین یک سری ستون رو با هم ادغام کنید که توی اون ستون ها سلول خالی وجود داره، میتونید از تابع TEXTJOIN به راحتی استفاده کنید. این تابع متن ها رو به هم پیوند میده و سلول های خالی رو نادیده میگیره.

=TEXTJOIN(" ",TRUE,A2:C2)

اما این تابع چطوری کار میکنه: 1- آرگومان اول: یک جدا کننده رو انتخاب کن – من فاصله رو انتخاب کردم. 2- اگر میخواهی سلول های خالی نادیده گرفته شوند آرگومان دوم را TRUE تعریف کن و اگر میخواهی سلول های خالی در نظر گرفته شوند آرگومان دوم را FALSE تعریف کن. 3- محدوده ای که میخواهی ادغام شوند را انتخاب کن.

پیوند (اما قرار گرفتن هر کدام در یک خط در یک سلول)

اگرچه وارد کردن بیشتر جدا کننده در تابع واضح است اما اگر بخواهید مقادیر را در خط های متفاوت قرار دهید دیگه نمیتونی به روش قبل عمل کنی. اما خب گوگل شیتس فکر اینجا رو هم کرده 😊 یک تابع وجود داره که بهت کمک میکنه از کاراکترهای خاص استفاده کنی. اسم این تابع CHAR هست. و احتمالا میدونی که همه کاراکترها یک مکان در جدول Unicode دارند. بنابراین به سادگی شما میتونید شماره مربوط به کاراکتر رو از جدول پیدا کنید و در تابع قرار بدید، و اون کاراکتر برگردونده میشه. اووووه چه سخت توضیح دادم بیا یک مثال رو با هم بررسی کنیم. این فرمول لاین بریک (منظور اینه که بره توی یک خط دیگه) رو برمگردونه:

=CHAR(10)

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

=CONCATENATE(A2,CHAR(10),B2,CHAR(10),C2,CHAR(10),D2)

 

ترکیب ساعت و تاریخ با استفاده از تابع CONCATENATE گوگل شیتس

اگر بخواهید تاریخ و زمان را با هم ادغام کنید؛ روش های بالا کار نمیکند. گوگل شیتس نتیجه رو به صورت یک عدد نشان میدهد.

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

=TEXT(number, format)

Number: هر عدد، تاریخ یا زمانی است که میخواهید فرمت خاصی از آن دریافت کنید. Format: الگویی است که میخواهید اعمال کنید. من از سلول مرجع برای تاریخ و زمانم استفاده کردم. اما شما میتونید به صورت مستقیم تاریخ یا زمان رو وارد تابع کنید. 1- من از اولین تابع Text برای تغییر فرمت تاریخ از 7/9/2019 به 9 jun 2019 استفاده کردم.

=TEXT(B2,"D MMM YYYY")

2- دومین تابع Text زمان رو برمیگردونه:

=TEXT(C2,"HH:MM:SS")

3- با استفاده از این موارد در تابع CONCATENATE، گوگل شیتس به من اجازه میده که تاریخ و زمان رو در قالب دلخواه خودم با کاراکترها یا متن های دیگه ادغام کنم:

=CONCATENATE(TEXT(B2,"D MMM YYYY"),", ",TEXT(C2,"HH:MM:SS"))

ترکیب ستون ها در گوگل شیتس

با یک سری تنظیمات، روش هایی که تا الان گفتیم توانایی ادغام ستون ها رو هم خواهند داشت. بریم ببینیم چطوری؟

مثال 1: CONCAT گوگل شیتس نه تابع CONCATENATE گوگل شیتس

برای اینکه کل ستون ها رو ادغام کنی، کافیه از تابع CONCAT  استفاده کنی و کل محدوده رو انتخاب کنی، و در نهایت بذاریشون داخل تابع ARRAYFORMULA. به همین راحتی.

=ARRAYFORMULA(CONCAT(A2:A11,B2:B11))

نکته: حواست باشه اگه از تابع CONCATENATE به جای CONCAT استفاده کنی نتیجه یکم فرق داره. نتیجه کلا توی یک سلول قرار میگیره!!! و اول محتوای ستون A کامل کنار هم قرار میگرن و در ادامه مقادیر ستون B 😊 امتحانش کن.

مثلا مثال بالا با تابع CONCATENAT مینویسیم:

=ARRAYFORMULA(CONCATENATE(A2:A11,B2:B11))

نتیجه در سلول c2 به این شکل خواهد بود:

BonnieDorothyTylerJacksonRhodesLoken

مثال 2: عملگرهای پیوند

ستون های آرایه ای ایجاد کنید و کل ستون ها را با علامت & با هم ترکیب کنید و از جداکننده ها هم به صورت همزمان در فرمولتون استفاده کنید:

=ARRAYFORMULA(A2:A11&" "&B2:B11&"; "&C2:C11)

اگر تعداد ستون های شما زیاد باشه، به این روش بخواین ترکیبشون کنید خیلی سخته. مخصوصا اگر کاراکترهایی رو به صورت تصادفی حذف کرده باشین یا اینکه دوبار نوشته باشین یا کلا اشتباهی و مخلوط نوشته باشید.

همچینن اگر بعدا تصمیم بگیرید که ستون های بیشتری به فرمول اضافه کنید، باید تک تک محدوده ها رو در فرمول به صورت دستی وارد کنید. که بازم زمان بر و کسل کننده است.

مثال بعدی این مشکلات رو حل میکنه. پس با ما همراه باش.

مثال 3: تابع QUERY گوگل شیتس

تابع QUERY گوگل شیتس برای ادغام چندین ستون در گوگل شیتس مناسب است. به فرمول زیر نگاه کنید:

=TRANSPOSE(QUERY(TRANSPOSE(A2:D10),,9^9))

ممکن است این فرمول براتون عجیب و غریب به نظر برسه و فکر کنید گیج شدید. اما بذارید اجزای فرمول رو بهتون توضیح بدم. قطعاً نظرتون عوض میشه:

1. =TRANSPOSE(A2:D10)

این فرمول داده ها رو که به صورت ردیفی هستند به شکل ستونی تبدیل میکنه.

2. =QUERY(TRANSPOSE(A2:D10),,9^9)

محتوای سلول های هر ستون رو داخل سلول رو به رو خودش قرار میده. یعنی محتوای سلول اول ستون A، B ، C، D رو کنار همدیگه و در سلول رو به روی خودشون یعنی E2 قرار میده.

3. =TRANSPOSE(QUERY(TRANSPOSE(A2:D10),,9^9))

حالا ردیف عنوان رو از تابع query میگیره و اون رو توی یک ستون نمایش میده.

از خوبی های تابع QUERY گوگل شیتس به این موارد میشه اشاره کرد:

• لازم نیست شما کل ستون رو انتخاب کنید. درحالیکه در فرمول های ارایه ای باید کل ستون رو انتخاب میکردید.
• لازم نیست که هر ستون رو توی فرمول ذکر کنید. فقط در صورتی که غیر همسایه باشن باید ستونو ذکر کنی. اگر ستون ها غیرهمسایه باشن فرمول به شکل زیر خواهد بود:

=TRANSPOSE(QUERY(TRANSPOSE({A2:A10,C2:C10,E2:E10,G2:G10}),,9^9))

 

جدا سازی سلول ها در گوگل شیتس

ممکن است بعد از اینکه سلول ها را با هم ادغام کردید بخواهید سلول ها را دوباره از هم جدا کنید. برای این کار سه روش در گوگل شیتس وجود دارد:

1-یک فرمول با استفاده از تابع SPLIT گوگل شیتس بنویسید.

2-از ابزار صفحه گسترده استاندارد (Standard spreadsheet instrument) استفاده کنی- Split text to columns

3-یا از نسخه پیشرفته تر ابزار داخلی (built-in tool) به نام split text to columns برای گوگل شیتس استفاده کنید:

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