فرمول نویسی برای چند شیت اکسل - خواندن سلول از شیت ها

اگر شما به این صفحه آمده‌اید یعنی می‌خواهید فرمولی را در یک شیت اکسل بنویسید تا آن فرمول محاسبه‌ای را روی چیزی انجام دهد که در شیت دیگری است.

برای آدرس دادن به یک سلول در یک شیت دیگر، ابتدا نام شیت را می‌نویسیم و سپس یک علامت ! می‌گذاریم و سپس آدرس سلول را می‌نویسیم:

= sheet1!A1

در فرمول قبل فرض کرده‌ایم که شیتی به نام Sheet1 داریم و می‌خواهیم سلول A1 آن را بخوانیم.


مثال ۱) فرمولی بنویسید که سلول A1 از شیت Cost را با سلول B2 از شیت Sales جمع بزند:

= Cost!A1 + Sales!B2

نکته) در فرمول نویسی اکسل حروف کوچک و بزرگ مهم نیست.

نکته) لازم نیست در اکسل آدرس سلول و شیت را تایپ کنید بلکه می‌توانید علامت = را در یک سلول بزنید و سپس بر روی هر سلولی که کلیک کنید، اکسل آدرس آن سلول را در فرمول شما می‌نویسد.


مثل ۲)  فرمولی را بنویسید که مجموع کل ستون F شیت sales  1402 را جمع بزند.

توجه کنید که در نام شیت ما فاصله (Space) داریم بنابراین باید نام شیت را در داخل علامت '   '  (بخوانید کوتیشن) بگذاریم. بنابراین فرمول ما می‌شود:

= SUM( 'sales  1402'!F:F )

نکته) بهتر است که در نام شیت از فاصله استفاده نکنید و به جای آن _ بگذارید مثلا نام شیت sales_1402 باشد.

چون نام شیت در فرمول ظاهر می‌شود باید: 
 - نام شیت کوتاه و با معنا باشد. 
 ـ نام شیت اکسل فارسی نباشد. 
 ـ به جای فاصله در نام شیت، از _ استفاده کنید. 
 


مثال ۳) فرمولی را بنویسید که سن Kaveh را از شیت karmandan بیابد و برای ما نمایش دهد. 

در این حالت ما نمی‌دانیم که Kaveh در کدام سلول شیت karmandan تایپ شده است و به همین دلیل باید با تابع Vlookup اکسل، به دنبال Kaveh بگردیم و سن او را از سلول جلویی آن بخوانیم:

=VLOOKUP( "Kaveh", karmandan!A:B, 2, 0 )

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


مثال ۴) فرمولی بنویسید که تمامی سلول‌هایی A1 شیت‌های ماه 1 تا 12 را جمع بزند.

فرض کنید که یک فایل دارید که در آن 12 شیت دارید و نام شیت‌ها MONTH1 تا MONTH12 است . حال می‌خواهید فرمولی را بنویسیم که جمع سلول A1 همه شیت‌ها را محاسبه کند و احتمالا شما چیزی شبیه فرمول زیر را خواهید نوشت:

= MONTH1!A1 + MONTH2!A1 + MONTH3!A1 

نکته) چون نام شیت یک عدد است آن را باید داخل ' ' بگذاریم.

مشاهده می‌کنید که نوشتن یا کلیک بر روی ۱۲ شیت کار تکراری و وقت گیری است.

اگر این ۱۲ شیت به ترتیب و پشت سر هستند می‌توانیم در این حالت ما از فرمول‌های ۳ بعدی اکسل استفاده کنیم:

= MONTH1:MONTH12!A1

 

فرمول سه بعدی اکسل چیست؟

به فرمولی گفته می‌شود که در آن به آدرس سلول‌های مشابه از ** چندین شیت **‌ اشاره شود مانند:   =SUM( MONTH1:MONTH12!A1 ) 

مثال ۵) فرمولی بنویسید که ستون B شیتی که نام آن را در سلول A1 تایپ کرده‌ایم را جمع بزند؟

برای تبدیل یک متن به یک آدرس معتبر اکسلی از تابع INDIRECT استفاده می‌کنیم و در اینجا هم نام شیت را از سلول A1 می‌خوانیم و به آدرس ستون B می‌چسبانیم و سپس با INDIRECT آن را یک آدرس معتبر اکسلی می‌کنیم:

=SUM( INDIRECT(A1 & "!B:B") )

تذکر مهم: تابع INDIRECT باعث کند شدن فایل‌های بزرگ می‌شود و توصیه می‌شود که از آن استفاده نکنید!


مثال ۶) با زبان vba برنامه‌ای بنویسید که اسامی همه شیت‌های فایل اکسل جاری را به همراه مقدار سلول A1 هر یک برای ما بنویسید؟

برای ایجاد یک حلقه بر روی اعضای Collection شیت‌های اکسل از دستور for each استفاده می‌کنیم:

Sub Print_All_Sheet_Names()

Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
    i = i + 1
    Cells(i, 1).Value = sh.Name
    Cells(i, 2).Value = sh.Range("A1").Value
Next sh

End Sub

پرسش ۱) اگر نام شیت اکسل تغییر کند، آیا فرمول ما که به آن شیت اشاره دارد، خطا خواهد شد؟

خیر، نام شیت در فرمول به صورت خودکار آپدیت می‌شود.


پرسش ۲) اگر شیت حذف شود، چه اتفاقی برای فرمولی می‌افتد که در آن از نام شیت استفاده شده است؟

نام شیت در فرمول به خطای #REF! تغییر خواهد یافت و فرمول شما هم خطای #REF! خواهد شد. در قسمت مقالات پیشنهادی پایین همین صفحه می‌توانید لینک شرح خطاهای اکسل را بیابید.


پرسش ۳) اگر آدرس سلول را در شیت ندانیم باید چه کار کرد؟

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

 

شما هم تجربه یا دیدگاه خود را بنویسید:

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

متن ساده

  • تگ‌های HTML مجاز نیستند.
  • خطوط و پاراگراف‌ها بطور خودکار اعمال می‌شوند.
کد امنیتی
آمنه صدقیان (تایید نشده) در تاریخ پنجشنبه, 1403/08/03 - 12:55 نوشته:

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

فرشید میدانی در تاریخ جمعه, 1403/08/04 - 12:39 نوشته:

با سلام/ می توانید همواره لیست دوره‌های در حال ثبت نام فرساران را در لینک زیر بینید:
www.farsaran.com/cc

رسول باقری (تایید نشده) در تاریخ پنجشنبه, 1403/06/01 - 09:00 نوشته:

سلام در اکسل دو شیت به نام های 1 و 2 داریم سلول A1 از شیت 1 دارای فرمت خاصی هست مثلا رنگ سلول زرد و فونت و سایز مخصوص به خودش رو دارد من میخوام سلول A1 در شیت دوم هم همین فرمت رو بگیره فرمولی بر اش هست؟

فرشید میدانی در تاریخ پنجشنبه, 1403/06/01 - 13:56 نوشته:

سلام/ خروجی هیچ فرمول در اکسل Format  نیست و فقط اینکار باید یا دستی انجام شود و یا اینکه همان فرایند دستی انجام دادن با vba برنامه نویسی شود.

سمیه (تایید نشده) در تاریخ پنجشنبه, 1403/03/03 - 21:28 نوشته:

سلام من ی سری اطلاعات تقریبا مبتدی راجب اکسل دارم ولی الان برای کارم نیاز دارم یه اکسل بسازم که نیاز به فرمول نویسی داره ایا شما میتونید به من کمک کنید برای نوشتن فرمولش.چون تقریبا فک کنم یه چیزه حرفه ایی باشه این حالتی که توی ذهنم هست ممنون میشم پاسخ من رو بدید .