این مطلب پیرامون فرمول های سه بعدی، توابع سه بعدی و مرجع سه بعدی در اکسل است. در این آموزش توضیح میدهیم که مرجع سه بعدی در اکسل چیست و شما چگونه میتوانید از آن برای مرجع دهی همان سلول یا یک محدوده از سلول ها در همه شیت های انتخاب شده استفاده کنید. شما همچنین یاد خواهید گرفت که چگونه فرمول 3D یا سه بعدی را برای داده های جمع آوری شده در صفحه های کاری مختلف بسازید. برای مثال سلول های مشابه را از چندین شیت مختلف با یک فرمول یا تابع سه بعدی جمع کنید.
یکی از بهترین نمونه های سلول مرجع، مرجع 3D است، که آن را به عنوان مرجع سه بعدی نیز میشناسند.
یک مرجع 3D یا سه بعدی در اکسل به سلول مشابه یا محدوده ای از سلول های در صفحه های مختلف اشاره دارد. که خیلی راحت و مناسب و راه سریعی برای محاسبه داده های در چندین صفحه با ساختار مشابه است، اما نگران نباشید، مثال های زیر موضوع را برای شما روشن میکند.
- مرجع سه بعدی یا 3D در اکسل چیست؟
- یک مرجع سه بعدی در اکسل بسازید
- یک برگه جدید به آدرس سه بعدی خود اضافه کنید
- ایجاد یک نام برای یک مرجع سه بعدی
- لیستی از توابعی که مرجع سه بعدی را پشتیبانی می کنند.
- چگونه مرجع سه بعدی اکسل وقتی شما برگه (شیت) را اضافه، حذف یا جابه جا میکنید ، تغییر میکند
مرجع سه بعدی یا 3D در اکسل چیست؟
مطابق مطالبی که در بالا گفته شد، یک مرجع سه بعدی یا 3D اکسل به شما اجازه میدهد به سلول های مشابه یا محدوده ای از سلول ها در چندین صفحه اشاره کنید. به عبارت دیگر، این مراجع نه تنها به یک محدوده ای از سلول ها در یک برگه، بلکه به محدوده نام برگه ها نیز اشاره میکند. نکته کلیدی این است که تمام شیت های مرجع باید یک الگو مشابه و یک نوع داده مشابه داشته باشند. لطفاً به مثال زیر دقت کنید:
شما به دنبال پیدا کردن جمع کل ماه ها هستید. یعنی می خواهیم جمع مقادیر sub-total (جمع جزء) را برای چهار ماه حساب کنیم. واضح ترین راه حلی که به ذهن میرسد، این است که سلول های sub-total (جمع جزء) از همه صفحه ها را به روش معمولی با هم جمع کنید:
=Jan!B6+Feb!B6+Mar!B6+Apr!B6
اما چه اتفاقی می افتد اگر شما دوازده برگه برای یک سال کامل داشته باشید، یا حتی برگه‌های بیشتری برای چندین سال مختلف داشته باشید؟ این کار بسیار زیاد و طولانی خواهد بود. درعوض، شما میتوانید از تابع SUM با یک مرجع سه بعدی یا 3D برای جمع کردن همه شیت ها استفاده کنید:
=SUM(Jan:Apr!B6)
این فرمول sum مانند فرمول طولانی بالا محاسبات را انجام میدهد. یعنی در این مثال مقادیر را که در سلول B6 در تمام صفحه های بین دو صفحه مرزی (boundary worksheets) یعنی ژانویه و آوریل که شما مشخص کرده اید، را باهم جمع میکند:
نکته: اگر قصد دارید فرمول سه بعدی یا 3D خود را در چندین سلول کپی کنید و نمی‌خواهید که سلول های مرجع تغییر کنند، میتوانید به آن ها علامت $ را اضافه کنید، یعنی از آدرس‌های مطلق استفاده کنید مانند:
=SUM(Jan:Apr!$B$6)
شما حتی نیاز به محاسبه sub-total (جمع جزء) در هر شیت ماهانه ندارید. می توانید محدوده مورد نظر را به صورت مستقیم در فرمول سه بعدی خود محاسبه کنید:
=SUM(Jan:Apr!B2:B5)
اگر میخواهید که کل فروش را برای هر محصول به صورت جداگانه پیدا کنید، کافی است جدولی از نام محصولات دقیقاً به همان صورتی که در صفحه ماهانه ظاهر میشود، بسازید. حال فرمول سه بعدی یا 3D را در سلول بالا قرار دهید. در این مثال فرمول در سلول B2 است:
بر اساس مثال فوق، بیایید یک مرجع سه بعدی و فرمول سه بعدی ایجاد کنیم.
مرجع سه بعدی اکسل
برگه اول: برگه آخر! سلول یا
برگه اول: برگه آخر! محدوده
First_sheet:Last_sheet!cell or
First_sheet:Last_sheet!range
فرمول سه بعدی اکسل
=Function(First_sheet:Last_sheet!cell) or
=Function(First_sheet:Last_sheet!range)
هنگامی که از فرمول های سه بعدی یا 3D در اکسل استفاده میکنید. همه صفحه ها بین اولین شیت و آخرین شیت در محاسبات گنجانده میشوند.
نکته: تمام توابع اکسل از از مراجع سه بعدی یا 3D پشیتبانی نمیکند، در اینجا لیست کاملی از توابع که از مراجع سه بعدی یا 3D پشتیبانی میکنند، را میتوانید مشاهده کنید.
یک مرجع سه بعدی یا 3D در اکسل بسازید
برای ساختن یک فرمول با یک مرجع سه بعدی یا 3D در آرگومان، گام های زیر را طی کنید:
- در سلولی که میخواهید فرمول سه بعدی یا 3D تان را وارد کنید، کلیک کنید.
- علامت مساوی (=) را تایپ کنید، نام تابع را وارد کنید، و پرانتز باز را تایپ کنید، یعنی=sum(
- بر روی اولین تب صفحه‌ای که میخواهید در فرمول سه بعدی یا 3D شما را شامل شود، کلیک کنید.
- همانطور که کلید Shift را نگه داشته اید، بر روی آخرین صفحهی که میخواهید مرجع سه بعدی یا 3D شما را شامل شود، کلیک کنید.
- سلول یا محدوده ای از سلول ها که میخواهید محاسبه شود، انتخاب کنید.
- کلید Enter را برای کامل شدن فرمول سه بعدی یا 3D فشار دهید.
چگونه میتوان یک برگه جدید را به فرمول سه بعدی یا 3D اکسل اضافه کرد؟
فرمول های سه بعدی یا 3D در اکسل قابل گسترش اند. به این معنی که شما میتوانید یک مرجع سه بعدی یا 3D را در یک نقطه ایجاد کنید، سپس یک شیت جدید اضافه کنید و آن را در محدوده ای که فرمول سه بعدی یا 3D شما را شامل میشود، قرار دهید. مثال زیر جزئیات را کامل ارائه میدهد.
فرض کنید که آغاز سال است و شما فقط برای چندین ماه اول داده دارید. با این حال، احتمالاً یک شیت جدید در هر ماه اضافه میشود و شما میخواهید که این شیت های جدید در همان زمان که ایجاد میشوند در محاسبات در نظر گرفته شوند.
برای این کار یک شیت خالی ایجاد کنید، نام آن را Dec بگذارید و آن را آخرین شیت در مرجع سه بعدی یا 3D خود قرار دهید:
خودشه! از آنجا که فرمول sum شما شامل مرجع سه بعدی یا 3D است، محدوده عرضه شده، سلول ها (B2:B5) را در تمام صفحه ها در محدوده مشخص شده از نام شیت (Jan:Dec!) جمع میکند. فقط به یاد داشته باشید که تمام شیت های موجود در مرجع سه بعدی یا 3D اکسل باید همان داده ها و همان نوع داده ها را داشته باشند.
چگونه برای مرجع سه بعدی یا 3D اکسل یک نام بسازیم
برای اینکه بتوانید از فرمول های سه بعدی یا 3D در اکسل به راحتی استفاده کنید، میتوانید یک نام تعریف شده برای مرجع سه بعدی یا 3D خود ایجاد کنید.
- در تب Fourmulas، به گروه Define Name بروید و بر روی Define Name کلیک کنید.
2. در قسمت Name، یک اسم مناسب و که به آسانی قابل یادآوری است و حداکثر میتواند تا 255 کاراکتر طول داشته باشد، تایپ کنید. در این مثال، اجازه دهید که چیز خیلی آسانی باشد. مثالا my_reference
3. محتویات باکس refers را حذف کنید و سپس یک مرجع سه بعدی یا 3D را در مسیر زیر وارد کنید:
- علامت مساوی را تایپ کنید. =
- کلید shift را نگه دارید، بر روی تب اولین شیت که میخواهید مرجع باشد کلیک کنید، سپس بر روی اخرین شیت کلیک کنید.
- سلول یا محدوده ای از سلول ها را برای ارجاع انتخاب کنید. شما همچنین میتوانید برای ارجاع یک ستون کامل بر روی (column letter) در شیت کلیک کنید.
در این مثال، اجازه دهید که یک مرجع سه بعدی یا 3D در اکسل برای کل ستون B در شیت های Jan تا Apr بسازیم. به این ترتیب، چیزی شبیه به این خواهید داشت:
- بر روی دکمه OK کلیک کنید تا نام جدید مرجع سه بعدی یا 3D ذخیره شود و پنجره بسته شود. انجام شد!!!!!
و حالا، برای جمع کردن اعداد در ستون B در تمام صفحه ها از Jan تا Apr، شما فقط لازمه که از این فرمول ساده استفاده کنید:
=SUM(my_reference)
توابع اکسلی که مراجع سه بعدی یا 3D را پشتیبانی میکنند
در اینجا لیستی از توابع اکسل که مراجع سه بعدی یا 3D را پشتیبانی میکنند، آمده است:
Sum- مقادیر عددی را جمع میکند.
Average- محاسبه میانگین اعداد.
Averagea- محاسبه میانگین ریاضی مقادیر، از جمله اعداد، متن و منطق.
Count- شمارش سلول های شامل اعداد
Counta- شمارش سلول هایی که خالی نیستند.
Max- بزرگترین مقدار را برمیگرداند
Maxa- بزرگترین مقدار شامل متن و منطق را برمیگرداند.
Min- کمترین مقدار را پیدا میکند.
Mina- کمترین مقدار شامل متن و منطق را برمیگرداند.
Product- ضرب اعداد
STDEV, STDEVA, STDEVP, STDEVPA – انحراف معیار یک مجموعه مشخص از مقادیر را محاسبه میکند.
VAR, VARA, VARP, VARPA – واریانس نمونه یک مجموعه مشخص از مقادیر را برمیگرداند.
چگونه مراجع سه بعدی یا 3D اکسل وقتی که شیت هایی اضافه، جابه جا یا حذف میشوند، تغییر میکند.
از آنجایی که هر مرجع سه بعدی یا 3D در اکسل توسط شیت شروع و پایان تعریف میشود، بیایید آن ها را نقطه انتهایی مرجع سه بعدی یا 3D بنامیم، با تغییر نقطه انتهایی مرجع تغییر کند، و در نتیجه فرمول سه بعدی یا 3D شما را تغییر میدهد. و حالا، ببینید هنگام حذف، جابه جایی نقاط انتهایی برای مرجع سه بعدی یا 3D دقیقاً چه اتفاقی می افتد.
از آنجا که همه چیز از یک مثال ساده باری فهمیدن شروع میشود، توضیحات بیشتر بر اساس فرمول سه بعدی یا 3D که ما قبلاً ایجاد کرده ایم خواهد بود:
شیت ها را در نقاط انتهایی کپی، حذف یا اضافه کنید. اگر شیت ها را بین نقاط انتهایی مرجع سه بعدی یا 3D (شیت ها Jan تا Apr در این مثال) اضافه، کپی یا حذف کنید، محدوده مرجع شده (سلول های B2:B5) در تمام شیت های جدید اضافه شده در محاسبات گنجانده میشود.
اگر خارج از نقاط انتهایی شیت هایی را حذف یا جابه جا کنید. هنگامی که هر یک از صفحه ها را بین نقاط انتهایی حذف میکنید یا شیت ها را خارج از نقاط انتهایی میگذارید، چنین شیت هایی از فرمول سه بعدی یا 3D شما حذف میشوند.
جابه جا کردن نقطه انتهایی. اگر هر نقطه انتهایی (Jan یا Apr یا هر دو) را به یک محل جدید در همان ورک بوک انتقال دهید، اکسل فرمول سه بعدی یا 3D خود که شامل شیت های جدیدی که بین نقطه های انتهایی قرار گرفته اند، تنظیم میکند و آنهایی که بیرون از نقاط انتهایی هستند را حذف میکند.
معکوس کردن نقاط انتهایی. معکوس کردن نقطه انتهایی مرجع سه بعدی یا 3D اکسل ، نتیجه است مثل تغییر یکی از نقاط انتهایی شیت ها است. به عنوان مثال: اگر شما شیت شروع (Jan) را جابه جا کنید و بعد از شیت انتهایی (Apr) قرار دهید. شیت Jan از فرمول مرجع سه بعدی یا 3D حذف خواهد شد و به Feb:Apr!B2:B5 تغییر خواهد کرد.
جابه جایی شیت نهایی (Apr) قبل از شیت ابتدایی (Jan) به صورت مشابه خواهد بود. در این مورد، شیت Apr از مرجع سه بعدی یا 3D حذف خواهد شد و به Jan:Mar!B2:B5 تغییر خواهد کرد.
توجه داشته باشید که بازیابی دستورالعمل نقاط انتهایی مرجع، مرجع اصلی سه بعدی یا 3D را بازیابی نخواهد کرد. در مثال بالا، حتی اگر شیت Jan به اولین موقعیت برگردد، مرجع سه بعدی یا 3D به صورت Feb:Apr!B2:B5 باقی خواهد ماند. و شما مجبورید که به صورت دستی آن را ویرایش کنید تا Jan را در محاسبات خود قرار دهید.
حذف نقطه پایانی. هنگامی که یکی از نقاط انتهایی شیت های خود را حذف میکنید. از مرجع سه بعدی یا 3D حذف میشود. و نقطه انتهایی حذف شده به صورت زیر تغییر میکند:
اگر اولین شیت حذف شود، نقطه پایانی به شیتی که آن را دنبال میکند تغییر میکند. در این مثال، اگر شیت Jan حذف شود، مرجع سه بعدی یا 3D به Feb: Arp!B2:B5 تغییر میکند.
اگر آخرین شیت حذف شود، نقطه پایانی به شیت قبلی تغییر میکند. در این مثال، اگر برگه Apr حذف شود، مرجع سه بعدی یا 3D به Jan:Mar!B2:B5 تغییر میکند.
این مطلب نحوه ایجاد و استفاده از مرجع سه بعدی یا 3D در اکسل است. همانطور که میبینید، این روش برای محاسبه محدوده های مشابه در بیش از یک شیت بسیار راحت و سریع است. در حالی که به روز رسانی فرمول های طولانی طولانی مراجع دهی شیت های مختلف ممکن است خسته کننده باشد، فرمول اکسل سه بعدی یا 3D نیاز به به روز رسانی فقط چندین مرجع دارد، یا اینکه شما میتوانید به راحتی با شیت های جدید را بین نقاط انتهایی مرجع سه بعدی یا 3D بدون تغییر فرمول قرار دهید.
ورود به سایت