القائمة الرئيسية

الصفحات

المرجع ثلاثي الأبعاد في Excel

سنتعرف في هذا الدرس على المرجع ثلاثي الأبعاد 3D Reference وهو مرجع يشير إلى نفس الخلية أو النطاق في أكثر من ورقة عمل .

لنفترض أن لدينا مبيعات بعض الفروع لشركة ونريد حساب مجموع مبيعات هذه الفروع .

الفرع الأول هو فرع أسيوط .

المرجع ثلاثي الأبعاد في Excel

الفرع الثاني هو فرع القاهرة .

المرجع ثلاثي الأبعاد في Excel

الفرع الثالث هو فرع الأقصر .

المرجع ثلاثي الأبعاد في Excel

لحساب مجموع مبيعات هذه الثلاثة فروع سنستخدم معادلة ستقوم بجمع القيمة الموجودة في الخلية B14 في أوراق العمل الخاصة بالثلاثة فروع .

المرجع ثلاثي الأبعاد في Excel

يمكنك استبدال المعادلة السابقة عن طريق استخدام المرجع ثلاثي الأبعاد أي أن الثلاثة مراجع :

المرجع الأول ASSIUT!B14

المرجع الثاني CAIRO!B14

المرجع الثالث LUXOR!B14

سنستبدلهم بالمرجع ثلاثي الأبعاد ASSIUT:LUXOR!B14

المرجع ثلاثي الأبعاد في Excel

ملحوظة : اذا قمت بإضافة ورقة عمل لفرع جديد في المستقبل بين ورقة العمل ASSIUT وورقة العمل LUXOR سيتم حسابها أتوماتيكيا في المرجع ثلاثي الأبعاد .

window['__wavt'] = 'AOuZoY7nR9IB9_xeVBsTteNiFlp_xZJFzA:1713487069592';_WidgetManager._Init('//www.blogger.com/rearrange?blogID\x3d2982479326136855131','//www.excelawy.com/2023/04/3d-reference.html','2982479326136855131'); _WidgetManager._SetDataContext([{'name': 'blog', 'data': {'blogId': '2982479326136855131', 'title': '\u0627\u0643\u0633\u0644\u0627\u0648\u064a', 'url': 'https://www.excelawy.com/2023/04/3d-reference.html', 'canonicalUrl': 'https://www.excelawy.com/2023/04/3d-reference.html', 'homepageUrl': 'https://www.excelawy.com/', 'searchUrl': 'https://www.excelawy.com/search', 'canonicalHomepageUrl': 'https://www.excelawy.com/', 'blogspotFaviconUrl': 'https://www.excelawy.com/favicon.ico', 'bloggerUrl': 'https://www.blogger.com', 'hasCustomDomain': true, 'httpsEnabled': true, 'enabledCommentProfileImages': true, 'gPlusViewType': 'FILTERED_POSTMOD', 'adultContent': false, 'analyticsAccountNumber': 'UA-106129969-1', 'encoding': 'UTF-8', 'locale': 'ar', 'localeUnderscoreDelimited': 'ar', 'languageDirection': 'rtl', 'isPrivate': false, 'isMobile': false, 'isMobileRequest': false, 'mobileClass': '', 'isPrivateBlog': false, 'isDynamicViewsAvailable': true, 'feedLinks': '\x3clink rel\x3d\x22alternate\x22 type\x3d\x22application/atom+xml\x22 title\x3d\x22\u0627\u0643\u0633\u0644\u0627\u0648\u064a - Atom\x22 href\x3d\x22https://www.excelawy.com/feeds/posts/default\x22 /\x3e\n\x3clink rel\x3d\x22alternate\x22 type\x3d\x22application/rss+xml\x22 title\x3d\x22\u0627\u0643\u0633\u0644\u0627\u0648\u064a - RSS\x22 href\x3d\x22https://www.excelawy.com/feeds/posts/default?alt\x3drss\x22 /\x3e\n\x3clink rel\x3d\x22service.post\x22 type\x3d\x22application/atom+xml\x22 title\x3d\x22\u0627\u0643\u0633\u0644\u0627\u0648\u064a - Atom\x22 href\x3d\x22https://www.blogger.com/feeds/2982479326136855131/posts/default\x22 /\x3e\n\n\x3clink rel\x3d\x22alternate\x22 type\x3d\x22application/atom+xml\x22 title\x3d\x22\u0627\u0643\u0633\u0644\u0627\u0648\u064a - Atom\x22 href\x3d\x22https://www.excelawy.com/feeds/8050776113165656899/comments/default\x22 /\x3e\n', 'meTag': '', 'adsenseClientId': 'ca-pub-1637612871342326', 'adsenseHostId': 'ca-host-pub-1556223355139109', 'adsenseHasAds': true, 'adsenseAutoAds': false, 'boqCommentIframeForm': true, 'loginRedirectParam': '', 'view': '', 'dynamicViewsCommentsSrc': '//www.blogblog.com/dynamicviews/4224c15c4e7c9321/js/comments.js', 'dynamicViewsScriptSrc': '//www.blogblog.com/dynamicviews/d86c8c5eadffdf93', 'plusOneApiSrc': 'https://apis.google.com/js/platform.js', 'disableGComments': true, 'interstitialAccepted': false, 'sharing': {'platforms': [{'name': '\u0627\u0644\u062d\u0635\u0648\u0644 \u0639\u0644\u0649 \u0627\u0644\u0631\u0627\u0628\u0637', 'key': 'link', 'shareMessage': '\u0627\u0644\u062d\u0635\u0648\u0644 \u0639\u0644\u0649 \u0627\u0644\u0631\u0627\u0628\u0637', 'target': ''}, {'name': 'Facebook', 'key': 'facebook', 'shareMessage': '\u0645\u0634\u0627\u0631\u0643\u0629 \u0625\u0644\u0649 Facebook', 'target': 'facebook'}, {'name': '\u0643\u062a\u0627\u0628\u0629 \u0645\u062f\u0648\u0646\u0629 \u062d\u0648\u0644 \u0647\u0630\u0647 \u0627\u0644\u0645\u0634\u0627\u0631\u0643\u0629', 'key': 'blogThis', 'shareMessage': '\u0643\u062a\u0627\u0628\u0629 \u0645\u062f\u0648\u0646\u0629 \u062d\u0648\u0644 \u0647\u0630\u0647 \u0627\u0644\u0645\u0634\u0627\u0631\u0643\u0629', 'target': 'blog'}, {'name': 'Twitter', 'key': 'twitter', 'shareMessage': '\u0645\u0634\u0627\u0631\u0643\u0629 \u0625\u0644\u0649 Twitter', 'target': 'twitter'}, {'name': 'Pinterest', 'key': 'pinterest', 'shareMessage': '\u0645\u0634\u0627\u0631\u0643\u0629 \u0625\u0644\u0649 Pinterest', 'target': 'pinterest'}, {'name': '\u0628\u0631\u064a\u062f \u0625\u0644\u0643\u062a\u0631\u0648\u0646\u064a', 'key': 'email', 'shareMessage': '\u0628\u0631\u064a\u062f \u0625\u0644\u0643\u062a\u0631\u0648\u0646\u064a', 'target': 'email'}], 'disableGooglePlus': true, 'googlePlusShareButtonWidth': 0, 'googlePlusBootstrap': '\x3cscript type\x3d\x22text/javascript\x22\x3ewindow.___gcfg \x3d {\x27lang\x27: \x27ar\x27};\x3c/script\x3e'}, 'hasCustomJumpLinkMessage': true, 'jumpLinkMessage': 'Read more \xbb', 'pageType': 'item', 'postId': '8050776113165656899', 'postImageThumbnailUrl': 'https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqmG6zkZMH1Nu8Bqayd1RYgrC6Ep0hL4BFV2o49S201qPEqz9N1T5Iytt5I4VqA8wgW0z4EAd-xsfvk9d9U0aLwKmlKIhCRLSzu1BFiEcCeQhXBYehRmNul04eONDmoHko7wfZ5R8Ddv5u4V8_w8sbdljnZfVXPrxTj3C4ja4Cd2paL8gxHzliOl_k/s72-c/3d%20reference%201.jpg', 'postImageUrl': 'https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqmG6zkZMH1Nu8Bqayd1RYgrC6Ep0hL4BFV2o49S201qPEqz9N1T5Iytt5I4VqA8wgW0z4EAd-xsfvk9d9U0aLwKmlKIhCRLSzu1BFiEcCeQhXBYehRmNul04eONDmoHko7wfZ5R8Ddv5u4V8_w8sbdljnZfVXPrxTj3C4ja4Cd2paL8gxHzliOl_k/s16000/3d%20reference%201.jpg', 'pageName': '\u0627\u0644\u0645\u0631\u062c\u0639 \u062b\u0644\u0627\u062b\u064a \u0627\u0644\u0623\u0628\u0639\u0627\u062f \u0641\u064a Excel', 'pageTitle': '\u0627\u0643\u0633\u0644\u0627\u0648\u064a: \u0627\u0644\u0645\u0631\u062c\u0639 \u062b\u0644\u0627\u062b\u064a \u0627\u0644\u0623\u0628\u0639\u0627\u062f \u0641\u064a Excel', 'metaDescription': ''}}, {'name': 'features', 'data': {}}, {'name': 'messages', 'data': {'edit': '\u062a\u0639\u062f\u064a\u0644', 'linkCopiedToClipboard': '\u062a\u0645 \u0646\u0633\u062e \u0627\u0644\u0631\u0627\u0628\u0637 \u0625\u0644\u0649 \u0627\u0644\u062d\u0627\u0641\u0638\u0629', 'ok': '\u062d\u0633\u0646\u064b\u0627', 'postLink': '\u0631\u0627\u0628\u0637 \u0627\u0644\u0645\u0634\u0627\u0631\u0643\u0629'}}, {'name': 'template', 'data': {'name': 'custom', 'localizedName': '\u0645\u062e\u0635\u0635', 'isResponsive': true, 'isAlternateRendering': false, 'isCustom': true}}, {'name': 'view', 'data': {'classic': {'name': 'classic', 'url': '?view\x3dclassic'}, 'flipcard': {'name': 'flipcard', 'url': '?view\x3dflipcard'}, 'magazine': {'name': 'magazine', 'url': '?view\x3dmagazine'}, 'mosaic': {'name': 'mosaic', 'url': '?view\x3dmosaic'}, 'sidebar': {'name': 'sidebar', 'url': '?view\x3dsidebar'}, 'snapshot': {'name': 'snapshot', 'url': '?view\x3dsnapshot'}, 'timeslide': {'name': 'timeslide', 'url': '?view\x3dtimeslide'}, 'isMobile': false, 'title': '\u0627\u0644\u0645\u0631\u062c\u0639 \u062b\u0644\u0627\u062b\u064a \u0627\u0644\u0623\u0628\u0639\u0627\u062f \u0641\u064a Excel', 'description': '\u0634\u0631\u062d \u0628\u0631\u0646\u0627\u0645\u062c Excel \u062d\u064a\u062b \u064a\u0645\u0643\u0646\u0643 \u062a\u0639\u0644\u0645 \u0628\u0631\u0646\u0627\u0645\u062c Excel \u0628\u0633\u0647\u0648\u0644\u0629', 'featuredImage': 'https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqmG6zkZMH1Nu8Bqayd1RYgrC6Ep0hL4BFV2o49S201qPEqz9N1T5Iytt5I4VqA8wgW0z4EAd-xsfvk9d9U0aLwKmlKIhCRLSzu1BFiEcCeQhXBYehRmNul04eONDmoHko7wfZ5R8Ddv5u4V8_w8sbdljnZfVXPrxTj3C4ja4Cd2paL8gxHzliOl_k/s16000/3d%20reference%201.jpg', 'url': 'https://www.excelawy.com/2023/04/3d-reference.html', 'type': 'item', 'isSingleItem': true, 'isMultipleItems': false, 'isError': false, 'isPage': false, 'isPost': true, 'isHomepage': false, 'isArchive': false, 'isLabelSearch': false, 'postId': 8050776113165656899}}, {'name': 'widgets', 'data': [{'title': '\u0627\u0644\u0635\u0641\u062d\u0627\u062a', 'type': 'PageList', 'sectionId': 'pages', 'id': 'PageList1'}, {'title': '\u0645\u0648\u0627\u0642\u0639 \u0627\u0644\u062a\u0648\u0627\u0635\u0644 \u0627\u0644\u0625\u062c\u062a\u0645\u0627\u0639\u064a', 'type': 'LinkList', 'sectionId': 'top-social-L', 'id': 'LinkList3'}, {'title': '\u0627\u0643\u0633\u0644\u0627\u0648\u064a (\u0631\u0623\u0633 \u0627\u0644\u0635\u0641\u062d\u0629)', 'type': 'Header', 'sectionId': 'logo', 'id': 'Header1'}, {'title': '\u0627\u0644\u0642\u0627\u0626\u0645\u0629 \u0627\u0644\u0631\u0626\u064a\u0633\u064a\u0629', 'type': 'LinkList', 'sectionId': 'menu', 'id': 'LinkList22'}, {'title': '\u0627\u0639\u0644\u0627\u0646 \u0627\u0633\u0641\u0644 \u0627\u0644\u0642\u0627\u0626\u0645\u0629 \u0627\u0644\u0639\u0644\u0648\u064a\u0629 \u0644\u0644\u062d\u0627\u0633\u0648\u0628', 'type': 'HTML', 'sectionId': 'Topa3lan-sc', 'id': 'HTML100'}, {'title': '\u0631\u0633\u0627\u0626\u0644 \u0627\u0644\u0645\u062f\u0648\u0646\u0629 \u0627\u0644\u0625\u0644\u0643\u062a\u0631\u0648\u0646\u064a\u0629', 'type': 'Blog', 'sectionId': 'Tempnec', 'id': 'Blog1', 'posts': [{'id': '8050776113165656899', 'title': '\u0627\u0644\u0645\u0631\u062c\u0639 \u062b\u0644\u0627\u062b\u064a \u0627\u0644\u0623\u0628\u0639\u0627\u062f \u0641\u064a Excel', 'featuredImage': 'https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqmG6zkZMH1Nu8Bqayd1RYgrC6Ep0hL4BFV2o49S201qPEqz9N1T5Iytt5I4VqA8wgW0z4EAd-xsfvk9d9U0aLwKmlKIhCRLSzu1BFiEcCeQhXBYehRmNul04eONDmoHko7wfZ5R8Ddv5u4V8_w8sbdljnZfVXPrxTj3C4ja4Cd2paL8gxHzliOl_k/s16000/3d%20reference%201.jpg', 'showInlineAds': true}], 'footerBylines': [{'regionName': 'footer1', 'items': [{'name': 'share', 'label': ''}, {'name': 'comments', 'label': 'comments'}, {'name': 'backlinks', 'label': 'Links to this post'}]}], 'allBylineItems': [{'name': 'share', 'label': ''}, {'name': 'comments', 'label': 'comments'}, {'name': 'backlinks', 'label': 'Links to this post'}]}, {'title': '\u0623\u0639\u0644\u0627\u0646 \u0645\u0646\u062a\u0635\u0641 \u0627\u0644\u0645\u0648\u0636\u0648\u0639', 'type': 'HTML', 'sectionId': 'PostAds', 'id': 'HTML202'}, {'title': '\u0623\u0639\u0644\u0627\u0646 \u0623\u062e\u0631 \u0627\u0644\u0645\u0648\u0636\u0648\u0639', 'type': 'HTML', 'sectionId': 'PostAds', 'id': 'HTML203'}, {'title': '\u0627\u0639\u0644\u0627\u0646 \u0628\u0639\u062f \u0627\u0648\u0644 \u0641\u0642\u0631\u0629', 'type': 'HTML', 'sectionId': 'PostAds', 'id': 'HTML205'}, {'title': '\u0628\u062d\u062b', 'type': 'BlogSearch', 'sectionId': 'sidepar', 'id': 'BlogSearch1'}, {'title': '\u0627\u0644\u0645\u0648\u0636\u0648\u0639\u0627\u062a \u0627\u0644\u0623\u0643\u062b\u0631 \u0642\u0631\u0627\u0621\u0629', 'type': 'PopularPosts', 'sectionId': 'sidepar', 'id': 'PopularPosts1', 'posts': [{'title': '\u062a\u062d\u0648\u064a\u0644 \u0627\u0644\u0623\u0631\u0642\u0627\u0645 \u0625\u0644\u0649 \u062d\u0631\u0648\u0641 ( \u062a\u0641\u0642\u064a\u0637 \u0627\u0644\u0623\u0631\u0642\u0627\u0645 ) \u0628\u0627\u0644\u0644\u063a\u0629 \u0627\u0644\u0639\u0631\u0628\u064a\u0629 \u0641\u064a Excel', 'id': 3863066244015172852}, {'title': '\u0645\u064a\u0632\u0627\u0646\u064a\u0629 \u0627\u0644\u0623\u0633\u0631\u0629 \u0627\u0644\u0634\u0647\u0631\u064a\u0629 Excel', 'id': 8909995317678577515}, {'title': '\u0643\u064a\u0641\u064a\u0629 \u0639\u0645\u0644 \u0645\u0639\u0627\u062f\u0644\u0627\u062a Excel', 'id': 6547497116255177489}, {'title': '\u0634\u062c\u0631\u0629 \u0627\u0644\u062d\u0633\u0627\u0628\u0627\u062a ( \u062f\u0644\u064a\u0644 \u0627\u0644\u062d\u0633\u0627\u0628\u0627\u062a ) Excel', 'id': 2309651825915169488}, {'title': '\u0627\u0644\u063a\u0627\u0621 \u062d\u0645\u0627\u064a\u0629 \u0648\u0631\u0642\u0629 Excel', 'id': 7867393757439384360}, {'title': '\u0623\u0633\u0626\u0644\u0629 \u0639\u0644\u0649 \u0628\u0631\u0646\u0627\u0645\u062c Excel', 'id': 6466420647838315665}, {'title': '\u062d\u0633\u0627\u0628 \u0627\u0644\u0641\u0631\u0642 \u0628\u064a\u0646 \u062a\u0627\u0631\u064a\u062e\u064a\u0646 Excel', 'id': 2171179851666379539}, {'title': '\u0627\u0638\u0647\u0627\u0631 \u0627\u0644\u0635\u0641\u0631 \u0639\u0644\u0649 \u0627\u0644\u0634\u0645\u0627\u0644 \u0641\u064a Excel', 'id': 796737267262094987}, {'title': '\u062f\u0648\u0627\u0644 \u0628\u0631\u0646\u0627\u0645\u062c Excel', 'id': 3344803733584675075}, {'title': '\u0643\u0634\u0641 \u062d\u0636\u0648\u0631 \u0648\u0627\u0646\u0635\u0631\u0627\u0641 \u0627\u0644\u0645\u0648\u0638\u0641\u064a\u0646 Excel', 'id': 3393686909161426500}]}]}]); _WidgetManager._RegisterWidget('_PageListView', new _WidgetInfo('PageList1', 'pages', document.getElementById('PageList1'), {'title': '\u0627\u0644\u0635\u0641\u062d\u0627\u062a', 'links': [{'isCurrentPage': false, 'href': 'https://www.excelawy.com/', 'title': '\u0627\u0644\u0635\u0641\u062d\u0629 \u0627\u0644\u0631\u0626\u064a\u0633\u064a\u0629'}, {'isCurrentPage': false, 'href': 'https://www.excelawy.com/p/blog-page.html', 'id': '5257334215271388986', 'title': '\u0627\u062a\u0635\u0644 \u0628\u0646\u0627'}], 'mobile': false, 'showPlaceholder': true, 'hasCurrentPage': false}, 'displayModeFull')); _WidgetManager._RegisterWidget('_LinkListView', new _WidgetInfo('LinkList3', 'top-social-L', document.getElementById('LinkList3'), {}, 'displayModeFull')); _WidgetManager._RegisterWidget('_HeaderView', new _WidgetInfo('Header1', 'logo', document.getElementById('Header1'), {}, 'displayModeFull')); _WidgetManager._RegisterWidget('_LinkListView', new _WidgetInfo('LinkList22', 'menu', document.getElementById('LinkList22'), {}, 'displayModeFull')); _WidgetManager._RegisterWidget('_HTMLView', new _WidgetInfo('HTML100', 'Topa3lan-sc', document.getElementById('HTML100'), {}, 'displayModeFull')); _WidgetManager._RegisterWidget('_BlogView', new _WidgetInfo('Blog1', 'Tempnec', document.getElementById('Blog1'), {'cmtInteractionsEnabled': false, 'lightboxEnabled': true, 'lightboxModuleUrl': 'https://www.blogger.com/static/v1/jsbin/3563558640-lbx__ar.js', 'lightboxCssUrl': 'https://www.blogger.com/static/v1/v-css/13464135-lightbox_bundle_rtl.css'}, 'displayModeFull')); _WidgetManager._RegisterWidget('_HTMLView', new _WidgetInfo('HTML202', 'PostAds', document.getElementById('HTML202'), {}, 'displayModeFull')); _WidgetManager._RegisterWidget('_HTMLView', new _WidgetInfo('HTML203', 'PostAds', document.getElementById('HTML203'), {}, 'displayModeFull')); _WidgetManager._RegisterWidget('_HTMLView', new _WidgetInfo('HTML205', 'PostAds', document.getElementById('HTML205'), {}, 'displayModeFull')); _WidgetManager._RegisterWidget('_BlogSearchView', new _WidgetInfo('BlogSearch1', 'sidepar', document.getElementById('BlogSearch1'), {}, 'displayModeFull')); _WidgetManager._RegisterWidget('_PopularPostsView', new _WidgetInfo('PopularPosts1', 'sidepar', document.getElementById('PopularPosts1'), {}, 'displayModeFull'));