فرمول‌های سه‌بعدی و هایپر لینک‌ها (Hyperlinks)

آخرین به‌روزرسانی: 27 دی 1401

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

سؤال‌های مطرح شده در این فصل

• آیا راه آسانی برای ایجاد یک فایل با کاربرگ های چندگانه با ساختار مشابه ، وجود دارد؟ آیا می‌توان به‌سادگی فرمول‌هایی نوشت که در سلول‌هایی در چندین کاربرگ بکار رفته باشند؟
• فایلی با 200 کاربرگ دارم. چگونه می‌توان به‌آسانی بین کاربرگ‌ها پیمایش کرد؟

پاسخ به سؤالات این فصل

• آیا راه آسانی برای ایجاد یک فایل با کاربرگ های چندگانه با ساختار مشابه ، وجود دارد؟ آیا می‌توان به‌سادگی فرمول‌هایی نوشت که در سلول‌هایی در چندین کاربرگ بکار رفته باشند؟

بیایید فرض کنیم که می‌خواهید فایلی تنظیم کنید که حاوی کاربرگ‌هایی جداگانه است که در آنها فروش هر منطقه در ایالات متحده دنبال می‌شود (مثل شرق، جنوب، غرب میانه و غرب) همچنین می‌خواهید کل فروش را در یک کاربرگ خلاصه به شکل موجزی نشان دهید. در هر کاربرگ قرار است ارزش محصول، هزینه واحد و تعداد واحدهای فروخته شده را به همراه هزینه ثابت و میزان سود دنبال کنید. قرار است که در کاربرگ خلاصه نیز، تنها سود کل و تعداد واحدهای فروخته شده را دنبال نمایید. می‌خواهید کاربرگ مربوط به هر منطقه مثل تصویر 1-15 به نظر برسد.

تصویر 1-15  میزان فروش در منطقه شرق

جهت ایجاد این ساختار در سلول C3 از هر کاربرگ قیمت محصول را وارد می‌کنیم، در سلول C4 هزینه هر واحد، در سلول C5 تعداد واحدهای فروخته شده و در سلول C6  هزینه ثابت را وارد می‌کنیم. سپس با واردکردن فرمول =(C3-C4)*C5-C6  در سلول C7  سود منطقه شرق را با محاسبه می‌کنیم. البته شما همین ساختار را در سایر کاربرگ‌های مربوط به مناطق دیگر نیز می‌خواهید. جالب است بدانید که کافی است که سرعنوان‌ها و فرمول‌ها را در یک کاربرگ وارد کنید و اکسل به طور خودکار آنها را به کاربرگ‌های سایر مناطق کپی می‌کند.

برای انجام این کار ابتدا یک فایل خالی ایجاد کنید که به‌صورت پیش‌فرض دارای یک کاربرگ است. با کلیک روی آیکون New Sheet(علامت بعلاوه) در سمت راست آخرین کاربرگ نام‌گذاری شده (یا با فشردن کلیدهای Shift+F11) چهار کاربرگ جدید ایجاد کنید تا فایل شما دارای پنج کاربرگ شود.

نام چهار کاربرگ اولیه را به ترتیب East، South،Midwest وWest بگذارید. آخرین کاربرگ را  Summary‌بنامید. قرار است کل فروش‌ها در کاربرگSummary نمایش داده شوند. (اگر گزینه Options  را در سربرگ File  انتخاب کنید آن‌وقت می‌توانید در کادر محاوره‌ای Excel Options در بخش General تعداد کاربرگ‌هایی را که به طور پیش‌فرض در یک فایل قرار می‌گیرد را با تغییر مقدار گزینه Include This many sheets  به هنگام ایجاد کاربرگ‌های جدید به‌دلخواه تغییر دهید.)

برای تنظیم کاربرگ‌های منطقه‌ای، اولین کاربرگ (East) را انتخاب کنید و بعد کلید Shift را پایین نگه‌داشته و سپس آخرین کاربرگ منطقه‌ای را (West) انتخاب کنید. حالا هرچه در کاربرگ East وارد کنید در سایر کاربرگ‌های منطقه‌ای کپی و درج می‌شود. می‌توانید به‌سادگی در سلول  B3 قیمت ، در سلول B4 هزینه واحد ودر سلولB5 واحدهای فروخته شده را درج کنید، در سلولB6 هزینه‌های ثابت ، در سلول B7 سود و بالاخره فرمول =(C3-C4)*C5-C6 را در سلول C7  تایپ کنید. حالا روی آخرین صفحه کلیک کنید تا از حالت درج داده‌ها بیرون بیایید و بعد از آن روی دیگر کاربرگ‌ها کلیک کنید. می‌بینید که کاربرگ هر منطقه دارای همان سرعنوان‌ها در ستون B‌و فرمول صحیح محاسبه سود در سلول C7می‌باشد.

اکنون برای استفاده از فرمول‌های سه‌بعدی برای محاسبه واحدهای فروخته شده و سود آماده شده‌اید. قرار است در سلول C5  از کاربرگ Summary تعداد کل واحدهای فروخته شده را محاسبه کنید. به یاد داشته باشید که شما تعداد واحدهای فروخته شده هر کاربرگ منطقه‌ای را در سلولC5 وارد کرده‌اید. حالا نشانگر موبایل را به سلول C5 در کاربرگ Summary یعنی همان جایی که می‌خواهید کل واحدهای فروخته شده محاسبه شود را ببرید و فرمول =SUM(را تایپ کنید و نشانگر را به اولین سلولی که می‌خواهید جمع از آنجا محاسبه شود (سلول C5 از کاربرگEast) ببرید. حالا کلید Shift را نگه‌داشته روی آخرین سلولی که می‌خواهید جمع از آنجا محاسبه شود (سلول C5 از کاربرگ West) کلیک کنید. در انتها یک پرانتز باز در فرمول بار (در کاربرگ West) وارد کنید و حالا می‌بینید فرمولSUM(East:West!C5) در سلول C5 از کاربرگSummary‌ درج شده است.

بیشتر فرمول‌های اکسل در محدوده‌ای دوبعدی عمل می‌کنند (ردیف‌ها و ستون‌ها) یک فرمول سه‌بعدی در محدوده‌ای سه‌بعدی یعنی میان کاربرگ‌ها عمل می‌کند. این فرمول به اکسل دستور می‌دهد که سلول C5 در تمامی کاربرگ‌ها را که از کاربرگ East شروع شده و به کاربرگWest‌ختم می‌شود جمع بزند. البته اگر بخواهید می‌توانید به‌سادگی این فرمول را در سلولC5 از کاربرگ Summary تایپ کنید. این فرمول را از سلولC5 کپی کرده و آن را در سلول C7 از کاربرگ Summary جای‌گذاری کنید تا سود کل شرکت را محاسبه کند (تصویر 2-15 را ببینید) همان‌طور که می‌توانید تصور کنید، پس از آن می‌بایست نام‌ها را در کاربرگ Summary درج کرده و بعد مقادیر هریک از کاربرگ‌های مناطق را وارد نمایید. فایلی بنام Threedim.xlsx نتیجه نهایی را نشان می‌دهد.

تصویر 2-15 خلاصه کردن میزان فروش واحدها و سود

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

فصل 45: استفاده از جداول پیوت ( pivot tables)و اسلایسرها ( slicers) برای توصیف داده‌ها

فصل 46 دیتا مدل (The Data Model)

فصل 47 پاور پیوت ( Power Pivot )

فصل 48 تجمیع داده‌ها ( Consolidating data)

فایلی با 200 کاربرگ دارم. چگونه می‌توانم به‌آسانی بین کاربرگ‌های متعدد آن پیمایش کنم؟

راه آسان برای پیمایش بین کاربرگ‌ها استفاده از هایپر لینک‌ است. فایلی به نام Hyperliktemp.xlsx حاوی پنج کاربرگ می‌باشد. به نظر شما بهتر نبود اگر می‌شد یک لینک قابل کلیک در اولین کاربرگ قرارداد تا بتوان به سلول A1  از هریک از کاربرگ‌ها منتقل شد؟ برای ایجاد یک لینک جهت انتقال به سلول A10  در کاربرگ International از اولین کاربرگ، کافی است نشانگر ماوس را در سلول A10 قرار داده و بعد از سربرگ Insert گزینه Insert Link را انتخاب کنید (یا اینکه کلیدهایCtrl+ K را فشار دهید) بعد از انتخاب گزینه Place In This Document یا جای‌گذاری در این سند، کادر محاوره‌ای را به شکلی که در تصویر 3-15 نشان‌داده‌شده پر کنید.

تصویر 3-15 نحوه ایجادکردن یک هایپر لینک

وقتی روی هایپر لینکInternational در سلولA10(تصویر 4-15 را ببینید) کلیک می‌کنیم فوراً به سلولA1 از کاربرگ International فرستاده می‌شویم. در اینجا به شکل مشابهی لینک‌هایی برای انتقال به سلول A1 از کاربرگ‌های دیگر ایجاد کرده‌ایم (فایلی بنام Hyperlink.xlsx و تصویر 4-15 را ببینید) توجه کنید که شما می‌توانید به‌راحتی هایپر لینکی برای مرتبط شدن به یک صفحه اینترنتی، موقعیتی در فایلی موجود، یا حتی یک آدرس ایمیل ایجاد نمایید.

تصویر 4-15 هایپر لینک‌ها با یک کلیک ، ما را به سایر کاربرگ‌ها منتقل می‌کنند

از تابعHYPERLINK  می‌توان برای آسان کردن کار ایجاد هایپر لینک‌های مختلف استفاده کرد. فایلی بنامHyperlinkfunction.xlsx و تصویر 5-15 این ایده را به تصویر کشیده‌اند. دستور زبان تابعHYPERKINK عبارت است از: Hyperlink(location of link, friendly name for link)

سلول‌هایD3:D5 حاوی آدرس‌های سه صفحه اینترنتی می‌باشند. در سلول‌های C3:C5 نام‌های میان‌برهای صفحات اینترنتی را درج کرده‌ایم. با کپی‌کردن فرمول HYPERLINK(D3,C3) از سلولB3 به محدوده B4:B5 لینک‌های میانبری برای صفحات دلخواهمان ایجاد می‌نماییم. در فصل 23 بنام تابعINDIRECT از توابع HYPERLINK و INDIRECT برای انجام خودکار فرایند ایجاد یک جدول مندرجات استفاده خواهیم کرد که حاوی هایپر لینک‌هایی به هریک از کاربرگ‌های یک فایل اکسل می‌باشد.

تصویر 5-15 مثال‌هایی از تابع HYPERLINK

در اینجا روش‌های دیگری برای پیمایش در چند کاربرگ یک فایل اکسل ارائه شده است:

• کلیدهای Ctrl+Page down شما را به کاربرگ بعدی منتقل می‌کنند درحالی‌که کلیدهای Ctrl+Page Up شما را به کاربرگ قبلی منتقل می‌کنند.
• اگر روی علامت‌های فلش مانند در پایین سمت چپ صفحه کلیک کنید فهرستی از کاربرگ‌های موجود در فایل حاضر را می‌بینید. همان‌طور که در تصویر 6-15 دیده می‌شود می‌توانید با کلیک کردن روی نام هر کاربرگ به آن منتقل شوید.

فرض کنید فایل اکسل شما حاوی کاربرگ‌های فراوانی است (فایلی به نام Multipleworksheetstemp.xlsx را بررسی کنید) اگر در نزدیکی اولین کاربرگ باشید و ماوس را روی فلش سمت چپ فعال کنار تب اولین کاربرگ گذاشته و بعد کلید Ctrl  را فشار داده و کلیک کنید شما به آخرین کاربرگ منتقل می‌شوید حالا اگر در نزدیکی‌های صفحات آخری باشید و دکمه ماوس را روی فلش سمت چپ فعال نزدیک اولین کاربرگ قرار داده و بعد کلید Ctrl را فشار دهید و کلیک کنید به اولین کاربرگ منتقل می‌شوید.

تصویر 6-15 راست کلیک روی فلش ها برای یافتن فهرستی از کاربرگ‌ها

مسئله‌های این فصل

فرض کنید صاحب شش کافه محلی هستید. درآمدها و تعداد مشتریان هریک از کافه‌ها در جدول زیر ارائه شده است:

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

هایپر لینکی در فایلی به نام Multipleworksheetstemp.xlsx برای ایجاد ارتباط با سلول A5 در کاربرگ‌های 3، کاربرگ 5، و کاربرگ 7 و همین‌طور ارتباط با صفحه اینترنتی Officeblog.com  قرار دهید.