نحوه پیوند داده ها بین شیت ها در اکسل چگونه در اکسل بین سلول ها پیوند ایجاد کنیم؟ پیوند دادن چندین کاربرگ

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

این دکمه برای ایجاد یک جدول پیوندی استفاده می شود. "قالب کردن به عنوان جدول"روی پانل "سبک ها"روبان ها "خانه".

سبک مورد نظر را برای جدول آینده انتخاب کنید و محدوده سلول هایی را که جدول پیوند داده شده بر اساس آن ایجاد می شود، تنظیم کنید.

پس از تنظیم تمام پارامترها، یک جدول پیوندی دریافت خواهیم کرد. دکمه‌های دارای فلش در کنار سرصفحه‌های ستون ظاهر می‌شوند (شبیه به فیلتر کردن)، و یک ابزار زمینه در پنجره برنامه ظاهر می‌شود. "کار با میز"، حاوی نوار "سازنده".

به هر جدول پیوند شده یک نام منحصر به فرد داده می شود. به طور پیش فرض - "Table_number". شما می توانید نام جدول را در پانل تغییر دهید "خواص".

روی پنل "سبک میز"در صورت نیاز می توانید سبک جدول پیوند داده شده را تغییر دهید یا سفارشی کنید.

یک جدول پیوندی را می توان به یک محدوده منظم از سلول ها تبدیل کرد. این همان چیزی است که دکمه برای آن است. "تبدیل به محدوده"روی پانل "سرویس"روبان متنی "سازنده".

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

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

با استفاده از لیست کشویی می توانید مقدار مورد نظر تابع نهایی را انتخاب کنید.

می توانید ردیف ها و ستون ها را به جدول پیوند داده شده اضافه یا حذف کنید.

این را از راه های گوناگون می توان انجام داد.

1. از دکمه استفاده کنید "تغییر اندازه جدول"روی پانل "خواص".

2. مکان نما را در سلول جدول پیوند داده شده، که در کنار آن می خواهید یک ستون (ردیف) جدید اضافه کنید و در پانل قرار دهید. "سلول ها"روبان ها "خانه"از دکمه استفاده کنید "درج".

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

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

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

روش 1: جداول پیوند مستقیم با فرمول

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

بیایید ببینیم که چگونه با استفاده از یک مثال، می توانیم با لینک مستقیم، یک لینک تشکیل دهیم. ما دو میز روی دو ورق داریم. در یک جدول، دستمزد با استفاده از یک فرمول با ضرب نرخ کارمندان در یک ضریب واحد برای همه محاسبه می‌شود.

در برگه دوم یک محدوده جدول وجود دارد که در آن لیستی از کارکنان با حقوق آنها وجود دارد. لیست کارکنان در هر دو مورد به یک ترتیب ارائه می شود.

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

روش 2: استفاده از یک دسته عملگر INDEX - MATCH

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

1. اولین عنصر یک ستون را انتخاب کنید "پیشنهاد". قابل اعتماد و متخصص Function Wizardبا کلیک بر روی آیکون "درج تابع".



2. که در Function Wizardدر یک گروه "مرجع و آرایه ها"نام را پیدا کرده و برجسته کنید "فهرست مطالب".



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



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

   INDEX(آرایه، شماره_ردیف، [شماره_ستون])

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

   "شماره خط"- آرگومان که عدد همین خط است. دانستن این نکته مهم است که شماره خط نباید نسبت به کل سند، بلکه فقط نسبت به آرایه انتخاب شده مشخص شود.

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

   مکان نما را در فیلد قرار دهید "آرایه". پس از آن به ورق 2و با نگه داشتن دکمه سمت چپ ماوس، کل محتویات ستون را انتخاب کنید "پیشنهاد".



5. پس از نمایش مختصات در پنجره عملگر، مکان نما را در فیلد قرار دهید "شماره خط". این آرگومان را با استفاده از عملگر نمایش خواهیم داد همخوانی داشتن. بنابراین روی مثلثی که در سمت چپ خط تابع قرار دارد کلیک می کنیم. لیستی از اپراتورهای اخیراً استفاده شده باز می شود. اگر نامی در میان آنها یافتید "جستجو کردن"، می توانید روی آن کلیک کنید. در غیر این صورت، روی آخرین مورد در لیست کلیک کنید - "ویژگی های بیشتر...".



6. پنجره استاندارد شروع می شود Function Wizards. ما در آن به همان گروه منتقل می کنیم "مرجع و آرایه ها". این بار مورد را از لیست انتخاب کنید. "جستجو کردن". انجام یک کلیک دکمه خوب.



7. پنجره آرگومان های اپراتور فعال می شود همخوانی داشتن. تابع مشخص شده برای نمایش تعداد مقدار در یک آرایه خاص با نام آن در نظر گرفته شده است. به لطف این ویژگی است که ما شماره خط یک مقدار مشخص را برای تابع محاسبه می کنیم فهرست مطالب. نحو همخوانی داشتنبه این صورت ارائه شده است:

   MATCH (مقدار_ جستجو، آرایه_جستجو، [مطابق_نوع])

   "ارزش مطلوب"- آرگومان حاوی نام یا آدرس سلول محدوده شخص ثالث که در آن قرار دارد. موقعیت این نام در محدوده هدف است که باید محاسبه شود. در مورد ما، اولین آرگومان ارجاعات سلولی در خواهد بود ورق 1، که حاوی اسامی کارکنان است.

   "آرایه قابل مشاهده"- یک آرگومان که ارجاع به آرایه ای است که در آن مقدار مشخص شده برای تعیین موقعیت آن جستجو می شود. در مورد ما، این نقش با آدرس ستون ایفا می شود. نام"بر روی ورق 2.

   "نوع مطابقت"- یک آرگومان اختیاری، اما برخلاف عملگر قبلی، به این آرگومان اختیاری نیاز خواهیم داشت. نشان می دهد که چگونه اپراتور مقدار جستجو شده را با آرایه مطابقت می دهد. این آرگومان می تواند یکی از سه مقدار را داشته باشد: -1 ; 0 ; 1 . برای آرایه های نامرتب، گزینه را انتخاب کنید «0» . این گزینه برای مورد ما مناسب است.

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



8. پس از نمایش مختصات، مکان نما را در فیلد تنظیم کنید "آرایه قابل مشاهده"و به برچسب بروید "ورق 2"، که در پایین پنجره اکسل بالای نوار وضعیت قرار دارد. دکمه سمت چپ ماوس را نگه دارید و تمام سلول های ستون را با مکان نما انتخاب کنید "نام".



9. پس از اینکه مختصات آنها در فیلد نمایش داده شد "آرایه قابل مشاهده"، به میدان بروید "نوع مطابقت"و از صفحه کلید شماره را در آنجا تنظیم کنید «0» . پس از آن دوباره به میدان برمی گردیم. "آرایه قابل مشاهده". واقعیت این است که همانطور که در روش قبلی انجام دادیم، فرمول را کپی می کنیم. تغییر آدرس‌ها وجود خواهد داشت، اما باید مختصات آرایه‌ای که در حال مشاهده است را اصلاح کنیم. او نباید حرکت کند. مختصات را با مکان نما برجسته کنید و کلید عملکرد را فشار دهید F4. همانطور که می بینید، یک علامت دلار در جلوی مختصات ظاهر شد، به این معنی که پیوند از نسبی به مطلق تبدیل شده است. سپس بر روی دکمه کلیک کنید خوب.



10. نتیجه در اولین خانه ستون نمایش داده می شود "پیشنهاد". اما قبل از کپی کردن، باید یک ناحیه دیگر، یعنی آرگومان اول تابع را اصلاح کنیم فهرست مطالب. برای انجام این کار، عنصر ستون حاوی فرمول را انتخاب کنید و به نوار فرمول بروید. اولین آرگومان عملگر را استخراج کنید فهرست مطالب (B2: B7) و روی دکمه کلیک کنید F4. همانطور که می بینید، علامت دلار در نزدیکی مختصات انتخاب شده ظاهر شد. روی کلید کلیک می کنیم وارد. به طور کلی فرمول به شکل زیر بود:

    INDEX(Sheet2!$B$2:$B$7;MATCH(Sheet1!A4;Sheet2!$A$2:$A$7;0))



11. اکنون می توانید با استفاده از دسته پر کپی کنید. ما آن را به همان روشی که قبلاً در مورد آن صحبت کردیم نامیده می‌کنیم و آن را تا انتهای محدوده جدول گسترش می‌دهیم.



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

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

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

جداول پیوندی ایجاد کنید

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

روش 1: جداول پیوند مستقیم با فرمول

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

بیایید ببینیم که چگونه با استفاده از یک مثال، می توانیم با لینک مستقیم، یک لینک تشکیل دهیم. ما دو میز روی دو ورق داریم. در یک جدول، دستمزد با استفاده از یک فرمول با ضرب نرخ کارمندان در یک ضریب واحد برای همه محاسبه می‌شود.

در برگه دوم یک محدوده جدول وجود دارد که در آن لیستی از کارکنان با حقوق آنها وجود دارد. لیست کارکنان در هر دو مورد به یک ترتیب ارائه می شود.

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

1. در صفحه اول، اولین سلول ستون "Rate" را انتخاب کنید. علامت "=" را در آن قرار می دهیم. در مرحله بعد، روی برگه "Sheet 2" که در سمت چپ رابط Excel در بالای نوار وضعیت قرار دارد، کلیک کنید.
2. به ناحیه دوم سند منتقل می شود. بر روی اولین سلول در ستون "Rate" کلیک کنید. سپس روی دکمه Enter روی صفحه کلید کلیک می کنیم تا داده ها را وارد سلولی کنیم که قبلاً علامت "برابر" در آن تنظیم شده بود.
3. سپس یک انتقال خودکار به صفحه اول وجود دارد. همانطور که می بینید، مقدار نرخ کارمند اول از جدول دوم به سلول مربوطه کشیده می شود. با قرار دادن مکان نما بر روی سلول حاوی نرخ، می بینیم که از فرمول معمول برای نمایش داده ها بر روی صفحه استفاده می شود. اما قبل از مختصات سلولی که داده ها در آن نمایش داده می شوند، عبارت "Sheet2!" وجود دارد که نشان دهنده نام منطقه سند است که در آن قرار دارند. فرمول کلی در مورد ما به این صورت است:
4. اکنون باید داده های مربوط به نرخ سایر کارکنان شرکت را انتقال دهید. البته، این کار را می توان به همان روشی انجام داد که کار را برای اولین کارمند انجام دادیم، اما با توجه به اینکه هر دو لیست کارمندان به یک ترتیب هستند، می توان کار را به طور قابل توجهی ساده و تسریع کرد. این کار را می توان با کپی کردن فرمول در محدوده زیر انجام داد. با توجه به این واقعیت که لینک ها در اکسل به طور پیش فرض نسبی هستند، زمانی که آنها کپی می شوند، مقادیر جابجا می شوند که همان چیزی است که ما به آن نیاز داریم. روش کپی کردن خود را می توان با استفاده از نشانگر پر انجام داد.

   بنابراین، مکان نما را با فرمول در قسمت سمت راست پایین عنصر قرار دهید. سپس مکان نما باید به یک دسته پر کننده متقاطع سیاه تبدیل شود. بستن دکمه سمت چپ ماوس را انجام می دهیم و مکان نما را به انتهای ستون می کشیم.

5. تمام داده‌های یک ستون مشابه در برگه 2 به جدولی در برگه 1 کشیده شد. هنگام تغییر داده‌ها در برگه 2، آنها به طور خودکار در صفحه اول تغییر می‌کنند.

روش 2: استفاده از یک دسته عملگر INDEX - MATCH

اما اگر لیست کارمندان در آرایه های جدول به یک ترتیب نباشد چه؟ در این مورد، همانطور که قبلا ذکر شد، یک گزینه این است که به صورت دستی هر یک از سلول هایی را که باید پیوند داده شوند، پیوند دهید. اما این فقط برای میزهای کوچک مناسب است. برای محدوده های عظیم، چنین گزینه ای، در بهترین حالت، زمان بسیار زیادی طول می کشد تا اجرا شود، و در بدترین حالت، در عمل به هیچ وجه امکان پذیر نخواهد بود. اما این مشکل را می توان با کمک یکسری عملگر INDEX - MATCH حل کرد. بیایید ببینیم که چگونه می توان این کار را با پیوند دادن داده ها در محدوده جدولی که در روش قبلی مورد بحث قرار گرفت انجام داد.

1. اولین عنصر ستون "Rate" را انتخاب کنید. با کلیک بر روی نماد "درج تابع" به Function Wizard بروید.
2. در Function Wizard، در گروه "References and Arrays" نام "INDEX" را پیدا کرده و انتخاب کنید.
3. این عملگر دو شکل دارد: یک فرم برای کار با آرایه ها و یک فرم مرجع. در مورد ما، گزینه اول مورد نیاز است، بنابراین در پنجره انتخاب فرم بعدی که باز می شود، آن را انتخاب کرده و روی دکمه "OK" کلیک کنید.
4. پنجره آرگومان های عملگر INDEX راه اندازی شده است. وظیفه تابع مشخص شده نمایش مقداری است که در محدوده انتخاب شده در خط با عدد مشخص شده است. فرمول کلی برای عملگر INDEX:

   INDEX (آرایه، خط_شماره؛)

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

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

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

   مکان نما را در قسمت "Array" قرار دهید. پس از آن، به Sheet 2 بروید و با نگه داشتن دکمه سمت چپ ماوس، تمام محتویات ستون "Rate" را انتخاب کنید.

5. پس از نمایش مختصات در پنجره اپراتور، مکان نما را در قسمت "شماره خط" قرار دهید. این آرگومان را با استفاده از عملگر MATCH نمایش خواهیم داد. بنابراین روی مثلثی که در سمت چپ خط تابع قرار دارد کلیک می کنیم. لیستی از اپراتورهای اخیراً استفاده شده باز می شود. اگر نام "SEARCH" را در میان آنها یافتید، می توانید روی آن کلیک کنید. در غیر این صورت، روی آخرین مورد در لیست کلیک کنید - "توابع دیگر ...".
6. پنجره استاندارد Function Wizard راه اندازی می شود. ما در آن به همان گروه "پیوندها و آرایه ها" منتقل می کنیم. این بار در لیست، مورد "SEARCH" را انتخاب کنید. ما روی دکمه "OK" کلیک می کنیم.
7. پنجره آرگومان های عملگر MATCH فعال می شود. تابع مشخص شده برای نمایش تعداد مقدار در یک آرایه خاص با نام آن در نظر گرفته شده است. به لطف این ویژگی است که ما تعداد ردیف یک مقدار مشخص را برای تابع INDEX محاسبه می کنیم. نحو MATCH به شرح زیر است:

   MATCH (مقدار_ جستجو، آرایه_جستجو؛)

   "در جستجوی مقدار" - آرگومان حاوی نام یا آدرس سلول محدوده شخص ثالث که در آن قرار دارد. موقعیت این نام در محدوده هدف است که باید محاسبه شود. در مورد ما، اولین آرگومان پیوندهایی به سلول‌های صفحه 1 خواهد بود که نام کارمندان در آن قرار دارد.

   "آرایه جستجوگر" - آرگومانی که ارجاع به آرایه ای است که در آن مقدار مشخص شده برای تعیین موقعیت آن جستجو می شود. در مورد ما، این نقش با آدرس ستون "Name" در Sheet 2 ایفا می شود.

   "Match type" یک آرگومان اختیاری است، اما برخلاف عملگر قبلی، ما به این آرگومان اختیاری نیاز خواهیم داشت. نشان می دهد که چگونه اپراتور مقدار جستجو شده را با آرایه مطابقت می دهد. این آرگومان می تواند یکی از سه مقدار را داشته باشد: -1; 0; 1. برای آرایه های نامرتب، "0" را انتخاب کنید. این گزینه برای مورد ما مناسب است.

   بنابراین، بیایید شروع به پر کردن فیلدهای پنجره آرگومان کنیم. ما مکان نما را در قسمت "به دنبال مقدار" قرار می دهیم، روی اولین سلول ستون "Name" در Sheet 1 کلیک می کنیم.

8. پس از نمایش مختصات، مکان نما را در قسمت "آرایه مشاهده شده" تنظیم کنید و به برچسب "برگ 2" بروید که در پایین پنجره اکسل بالای نوار وضعیت قرار دارد. دکمه سمت چپ ماوس را نگه دارید و تمام سلول های ستون "Name" را با مکان نما انتخاب کنید.
9. پس از اینکه مختصات آنها در قسمت "آرایه مشاهده شده" نمایش داده شد، به قسمت "نوع مطابقت" بروید و با استفاده از صفحه کلید عدد "0" را در آنجا تنظیم کنید. پس از آن، دوباره به فیلد "آرایه مشاهده شده" باز می گردیم. واقعیت این است که همانطور که در روش قبلی انجام دادیم، فرمول را کپی می کنیم. تغییر آدرس‌ها وجود خواهد داشت، اما باید مختصات آرایه‌ای که در حال مشاهده است را اصلاح کنیم. او نباید حرکت کند. مختصات را با مکان نما انتخاب کنید و کلید عملکرد F4 را فشار دهید. همانطور که می بینید، یک علامت دلار در جلوی مختصات ظاهر شد، به این معنی که پیوند از نسبی به مطلق تبدیل شده است. سپس بر روی دکمه "OK" کلیک کنید.
10. نتیجه در اولین خانه ستون "نرخ" نمایش داده می شود. اما قبل از کپی کردن، باید یک ناحیه دیگر، یعنی آرگومان اول تابع INDEX را اصلاح کنیم. برای انجام این کار، عنصر ستون حاوی فرمول را انتخاب کنید و به نوار فرمول بروید. اولین آرگومان عملگر INDEX (B2:B7) را انتخاب کرده و روی دکمه F4 کلیک کنید. همانطور که می بینید، علامت دلار در نزدیکی مختصات انتخاب شده ظاهر شد. روی کلید Enter کلیک می کنیم. به طور کلی فرمول به شکل زیر بود:

    INDEX(Sheet2!$B$2:$B$7;MATCH(Sheet1!A4;Sheet2!$A$2:$A$7;0))

11. اکنون می توانید با استفاده از دسته پر کپی کنید. ما آن را به همان روشی که قبلاً در مورد آن صحبت کردیم نامیده می‌کنیم و آن را تا انتهای محدوده جدول گسترش می‌دهیم.
12. همانطور که می بینید، علیرغم این واقعیت که ترتیب ردیف ها در دو جدول مرتبط مطابقت ندارند، با این وجود، همه مقادیر مطابق با نام کارمندان بالا کشیده می شوند. این با استفاده از ترکیبی از عملگرهای INDEX-MATCH به دست آمد.

همچنین بخوانید:

تابع INDEX در اکسل تابع MATCH در اکسل

روش 3: انجام عملیات ریاضی بر روی داده های مرتبط

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

بیایید ببینیم که چگونه این کار در عمل انجام می شود. بیایید آن را طوری تنظیم کنیم که برگه 3 داده های دستمزد عمومی را برای شرکت بدون تفکیک کارمند نشان دهد. برای انجام این کار، نرخ کارمندان از برگه 2 جمع آوری می شود (با استفاده از تابع SUM) و با استفاده از فرمول در ضریب ضرب می شود.

1. سلولی که نتیجه محاسبه حقوق و دستمزد در برگه 3 نمایش داده می شود را انتخاب می کنیم.روی دکمه "درج تابع" کلیک می کنیم.
2. پنجره Function Wizard باید راه اندازی شود. به گروه "ریاضی" بروید و نام "SUM" را در آنجا انتخاب کنید. بعد، بر روی دکمه "OK" کلیک کنید.
3. به پنجره آرگومان تابع SUM می رود که برای محاسبه مجموع اعداد انتخاب شده طراحی شده است. دارای نحو زیر است:

   جمع (شماره 1، شماره 2،…)

   فیلدهای پنجره با آرگومان های تابع مشخص شده مطابقت دارند. اگرچه تعداد آنها می تواند به 255 قطعه برسد، اما برای هدف ما فقط یک عدد کافی خواهد بود. ما مکان نما را در قسمت "Number1" قرار می دهیم. روی برچسب "برگ 2" در بالای نوار وضعیت کلیک کنید.

4. پس از اینکه به قسمت مورد نظر کتاب رفتیم، ستونی را که باید خلاصه شود انتخاب می کنیم. این کار را با مکان نما با نگه داشتن دکمه سمت چپ ماوس انجام می دهیم. همانطور که مشاهده می کنید مختصات ناحیه انتخاب شده بلافاصله در قسمت پنجره آرگومان ها نمایش داده می شود. سپس بر روی دکمه "OK" کلیک کنید.
5. پس از آن، ما به طور خودکار به Sheet 1 منتقل می شویم. همانطور که می بینید، مقدار کل نرخ کارمندان قبلاً در عنصر مربوطه نمایش داده می شود.
6. اما این همه ماجرا نیست. همانطور که به یاد داریم، حقوق با ضرب مقدار شرط در ضریب محاسبه می شود. بنابراین، مجدداً سلولی را انتخاب می کنیم که مقدار جمع شده در آن قرار دارد. پس از آن، به نوار فرمول بروید. علامت ضرب (*) را به فرمول موجود در آن اضافه می کنیم و سپس روی عنصری که نشانگر ضریب در آن قرار دارد کلیک می کنیم. برای انجام محاسبات روی کلید Enter از صفحه کلید کلیک کنید. همانطور که می بینید، برنامه کل دستمزد شرکت را محاسبه کرد.
7. به برگه 2 برمی گردیم و اندازه نرخ هر کارمند را تغییر می دهیم.
8. پس از آن، دوباره به صفحه با کل مبلغ منتقل می شویم. همانطور که مشاهده می کنید، با توجه به تغییرات جدول لینک شده، نتیجه کل حقوق به طور خودکار مجددا محاسبه شد.

روش 4: خمیر مخصوص

همچنین می توانید آرایه های جدول را در اکسل با استفاده از Paste Special پیوند دهید.

1. ما مقادیری را انتخاب می کنیم که باید در جدول دیگری "کشیده" شوند. در مورد ما، این محدوده ستون "Rate" در Sheet 2 است. روی قطعه انتخاب شده راست کلیک کنید. از لیست ظاهر شده گزینه "Copy" را انتخاب کنید. یک ترکیب جایگزین، میانبر صفحه کلید Ctrl+C است. پس از آن به Sheet 1 می رویم.
2. پس از انتقال به ناحیه کتاب مورد نیاز خود، سلول هایی را انتخاب می کنیم که باید مقادیر را در آنها بکشیم. در مورد ما، این ستون Rate است. با دکمه سمت راست ماوس روی قطعه انتخاب شده کلیک می کنیم. در منوی زمینه در بلوک ابزار «گزینه‌های چسباندن»، روی نماد «درج پیوند» کلیک کنید.

   یک جایگزین نیز وجود دارد. به هر حال، این تنها برای نسخه های قدیمی اکسل است. در منوی زمینه، نشانگر را روی مورد «جای‌گذاری ویژه» نگه دارید. در منوی اضافی که باز می شود، موردی را با همین نام انتخاب کنید.

3. با این کار پنجره Paste Special باز می شود. بر روی دکمه "Insert Link" در گوشه سمت چپ پایین سلول کلیک کنید.
4. هر گزینه ای را که انتخاب کنید، مقادیر یک آرایه جدول در دیگری درج می شود. هنگامی که داده های کد منبع تغییر می کند، آنها نیز به طور خودکار در محدوده درج شده تغییر می کنند.

درس: Paste Special در اکسل

روش 5: پیوند جداول در چندین کتاب کار

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

1. ما محدوده داده ای را که باید به کتاب کار دیگری منتقل شود انتخاب می کنیم. روی آن کلیک راست کرده و از منوی باز شده گزینه «Copy» را انتخاب کنید.
2. سپس به کتابی می رویم که این داده ها باید در آن درج شوند. محدوده مورد نظر را انتخاب کنید. با دکمه سمت راست ماوس کلیک می کنیم. از منوی زمینه، در گروه Paste Options، Paste Link را انتخاب کنید.
3. پس از آن، مقادیر درج خواهند شد. هنگامی که داده‌های کتاب کار منبع تغییر می‌کند، آرایه جدول از کتاب کار آن‌ها را به‌طور خودکار بالا می‌کشد. علاوه بر این، اصلاً لازم نیست که هر دو کتاب برای این کار باز باشد. کافی است فقط یک کتاب کار باز شود و اگر قبلاً تغییراتی در آن ایجاد شده باشد، به طور خودکار داده‌ها را از یک سند پیوند بسته بیرون می‌کشد.

اما باید توجه داشت که در این حالت درج به صورت یک آرایه تغییرناپذیر انجام خواهد شد. وقتی می‌خواهید سلولی را با داده‌های درج شده تغییر دهید، پیامی ظاهر می‌شود که به شما اطلاع می‌دهد انجام این کار غیرممکن است.

تغییرات در چنین آرایه ای که به یک کتاب کار دیگر پیوند داده شده است، تنها با شکستن پیوند انجام می شود.

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

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

روش 1: لغو پیوند کتابهای کار

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

1. در کتابی که مقادیر از فایل های دیگر بیرون کشیده شده است، به برگه "داده ها" بروید. روی نماد "ویرایش پیوندها" که روی نوار در بلوک ابزار "اتصالات" قرار دارد کلیک می کنیم. لازم به ذکر است که اگر کتاب فعلی دارای لینک به فایل های دیگر نباشد، این دکمه غیر فعال است.
2. پنجره ویرایش پیوندها باز می شود. از لیست کتاب های پیوند داده شده (در صورت وجود چندین) فایلی را که می خواهیم پیوند را با آن قطع کنیم انتخاب می کنیم. بر روی دکمه "Unlink" کلیک کنید.
3. یک پنجره اطلاعاتی باز می شود که در آن هشداری در مورد عواقب اقدامات بعدی وجود دارد. اگر مطمئن هستید که قرار است چه کاری انجام دهید، بر روی دکمه "Break Ties" کلیک کنید.
4. پس از آن، تمام ارجاعات به فایل مشخص شده در سند فعلی با مقادیر ثابت جایگزین می شود.

روش 2: درج مقادیر

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

1. محدوده ای را انتخاب می کنیم که می خواهیم پیوند را با جدول دیگری حذف کنیم. با دکمه سمت راست ماوس روی آن کلیک می کنیم. از منوی کشویی "کپی" را انتخاب کنید. به جای این اقدامات، می توانید ترکیبی جایگزین از کلیدهای داغ Ctrl + C را تایپ کنید.
2. علاوه بر این، بدون لغو انتخاب همان قطعه، دوباره روی آن کلیک راست کنید. این بار در لیست اکشن ها بر روی نماد "Values" که در گروه ابزار "گزینه های چسباندن" قرار دارد کلیک کنید.
3. پس از آن، تمام پیوندهای موجود در محدوده انتخاب شده با مقادیر ثابت جایگزین می شوند.

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

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

سوال خود را در نظرات بپرسید و ماهیت مشکل را با جزئیات شرح دهید. کارشناسان ما سعی خواهند کرد در اسرع وقت پاسخ دهند.

آیا این مقاله به شما کمک کرد؟

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

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

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

ایجاد پیوند بین کتاب های کاری

1. هر دو کتاب کار را در اکسل باز کنید
2. در کتاب منبع، سلول مورد نظر را انتخاب کنید و آن را کپی کنید (میانبر Ctrl + C)
3. به کتاب هدف بروید، روی سلولی که می خواهیم پیوند را در آن قرار دهیم کلیک راست کنید. از منوی کشویی انتخاب کنید درج ویژه
4. درج ویژهانتخاب کنید درج لینک

گزینه دیگری ساده تر برای ایجاد رابطه بین جداول وجود دارد. در سلولی که می خواهیم پیوندی را وارد کنیم، علامت مساوی قرار می دهیم (مثل یک فرمول معمولی)، به کتاب کار منبع می رویم، سلولی را که می خواهیم پیوند دهیم را انتخاب می کنیم، روی Enter کلیک می کنیم.

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

قبل از ایجاد رابطه بین جداول

قبل از شروع به انتشار اخبار در مورد ایده های بزرگ خود، در اینجا چند نکته برای کار با پیوندها در اکسل وجود دارد:

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

محاسبات خودکارکتاب کار منبع باید در حالت محاسبه خودکار (به طور پیش فرض تنظیم شده) کار کند. برای تغییر پارامتر محاسبه، روی برگه کلیک کنید فرمول هابه گروه محاسبه.انتخاب کنید گزینه های محاسبه -> خودکار.

به روز رسانی لینک ها

برای به روز رسانی دستی رابطه بین جداول، به تب بروید داده هابه گروه اتصالات. روی دکمه کلیک کنید تغییر لینک ها

در کادر محاوره ای که ظاهر می شود تغییر اتصالات، به روز رسانی.

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

شکستن پیوند به منبع، فرمول های پیوند موجود را با مقادیری که برمی گرداند جایگزین می کند. برای مثال، پیوند =Prices!$B$4 با 16 جایگزین می شود. شما نمی توانید پیوند را لغو کنید، بنابراین توصیه می کنم قبل از انجام عملیات، کتاب کار را ذخیره کنید.

به برگه بروید داده هابه گروه اتصالات. روی دکمه کلیک کنید تغییر لینک هادر کادر محاوره ای که ظاهر می شود تغییر اتصالات،اتصال مورد نظر خود را انتخاب کنید و روی دکمه کلیک کنید لینک رو خراب کن

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

مسئله

این اتفاق می افتد که داده های تجزیه و تحلیل شده در قالب جداول جداگانه به ما می رسد، که با این وجود، باید پیوند داده شوند. MS Access به راحتی می تواند این کار را انجام دهد، اما در اکسل، همیشه مجبور بودید از فرمول هایی مانند VLOOKUP (VLOOKUP) استفاده کنید. با این حال، با شروع اکسل 2013، ما این فرصت را داریم که از چندین جدول به عنوان منبع در هنگام ساخت جدول محوری استفاده کنیم که با فیلدهای کلیدی به یکدیگر مرتبط هستند.

مثال

در مثال ما 4 جدول داریم: سفارشات، خطوط سفارش، محصولات، مشتریان.

جدول سفارش:

جدول خط سفارش:

جدول محصول

جدول مشتریان

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

کاملاً واضح است که جداول Orders و OrderLines را می توان با فیلد Order_ID، جداول Orders و Clients - با قسمت client_ID، جداول OrderLines و Goods - با قسمت product_ID پیوند داد.

دانلود نمونه

ایجاد یک مدل داده

بیایید یک جدول محوری بر اساس هر یک از جداول موجود ایجاد کنیم.

PivotTable را از منوی Insert انتخاب کنید. در کادر محاوره ای گفته شده، گزینه ای برای افزودن این داده به مدل داده را می بینیم. ما می‌توانیم آن را انتخاب کنیم، اما من راه دیگری و راحت‌تر را توصیه می‌کنم. فقط روی OK کلیک کنید.

در پانل فیلدهای جدول محوری که ظاهر می شود، کتیبه OTHER TABLES را مشاهده می کنید ...

بیایید آن را فشار دهیم. سوال زیر ظاهر خواهد شد:

اگر شروع به انتخاب فیلدها کنید، پس از مدتی دکمه CREATE در لیست فیلدها ظاهر می شود...

بیایید روی آن کلیک کنیم و پیوندهایی بین جداول خود ایجاد کنیم. این یک رابطه بین جدول Orders و OrderLines ایجاد می کند. توجه داشته باشید که اکسل می تواند یک رابطه یک به یک یا یک به چند ایجاد کند. و اولین مورد باید جدولی را مشخص کند که در آن "زیاد" وجود دارد، در غیر این صورت اکسل قسم می خورد و پیشنهاد می کند آنها را تعویض کنید.

به همین ترتیب، ما ارتباطات دیگری ایجاد می کنیم.

کادر محاوره ای Manage Relationships را می توان از طریق روبان ANALYZE the Relationships مشاهده کرد.

برای مشاهده فیلدهای بیشتر در پنل PivotTable Fields، می توانید این نمای را از طریق دکمه Tools (به شکل چرخ دنده) انتخاب کنید:

نتیجه به این صورت خواهد بود:

در نتیجه، همه جداول ما اکنون به هم متصل هستند و شما می توانید برای مثال، چنین گزارشی را ایجاد کنید.

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

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

بسته به تکنیک اجرا، binding است "سر راست"و از طریق فرمان درج ویژه.

روش 1 - "پیوند مستقیم سلول"

لینک مستقیماز sheets مستقیماً هنگام وارد کردن فرمول به سلول استفاده می شود، زمانی که ارجاع به سلول یک صفحه دیگر به عنوان یکی از عناصر فرمول استفاده می شود. به عنوان مثال، اگر سلول جدول B4 در کاربرگ 2 حاوی فرمولی باشد که از پیوند به سلول A4 یک کاربرگ دیگر (مثلاً Sheet1) استفاده می کند و هر دو برگه با داده بارگیری می شوند، آنگاه به چنین پیوندی ورق ها "مستقیم" می گویند.

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

نمونه های فرمول: = C5 * Sheet1! A4

ورق 3! B2*100%

ورق 1! A1- Sheet2! A1

توجه داشته باشید.

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

روش 2 - پیوند سلول ها از طریق دستور "Paste Special".

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

برای انعکاس در سلول C4 روی برگه قیمتمقدار سلول H4 در برگه اصلی خرید، باید مکان نما را روی سلول H4 صفحه منبع قرار دهید و دستور را اجرا کنید ویرایش – کپی. روی برگه قیمتمکان نما را روی سلول C4 که باید به سلول اصلی متصل شود قرار دهید و دستور را اجرا کنید ویرایش - چسباندن ویژه - چسباندن پیوند(شکل 8 را ببینید). سپس روی ورق قیمتنشانه ای از سلول صفحه اصلی ظاهر می شود خرید، مثلا: = خرید! $N $4

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

وظیفه.سلول های C4، C5، C6، C7، C8 را در جدول پیوند دهید هزینه های خریدروی یک برگه قیمتبا سلول های مربوطه روی صفحه خرید، با استفاده از روش های مختلف پیوند سلول ها (شکل 8).

برنج. 8 پیوند سلول های کاربرگ های مختلف

! هنگام پیوند دادن سلول ها، تعیین کنید که سلول های مبدا کدام سلول هستند.

! برای یک جدول پیوندی، سلول‌های جداول مختلف در کاربرگ‌های مختلف یا در برگه فعلی می‌توانند سلول‌های منبع باشند.

تکالیف برای کار مستقل.

قبل از انجام یک کار مستقل، مثال جداول ورق را در نظر بگیرید قیمتسلول های پیوند دهنده

مثلا:

روی یک برگه قیمتدر جدول هزینه های خریدسلول های A4:A8 به سلول های جدول مرتبط هستند تعداد محصولات خریداری شدهروی یک برگه خرید;

سلول های B4:B8 اولیه هستند، زیرا. حاوی اطلاعات اولیه در مورد قیمت کالاهای خریداری شده؛

سلول‌های C4:C8 به سلول‌های H4:H8 روی کاربرگ متصل می‌شوند خرید;

سلول های D4:D8 حاوی فرمول هایی برای محاسبه مبلغ هزینه شده برای کالاهای خریداری شده هستند و به سلول های جدول خود مراجعه می کنند (به عنوان مثال، فرمول در سلول D4 به نظر می رسد = B4 * C4، به این معنی که قیمت کالا را در آن ضرب کنید. تعداد)؛

سلول D9 مجموع سلول های D4:D8 است.

در جدول دوم محاسبه قیمتدر همان صفحه، سلول های A14:A18 به طور مشابه به بند 1 متصل شده اند.

سلول های B14:B18 به سلول های اصلی برگه فعلی B4:B8 متصل می شوند.

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

سلول‌های D14:D18 حاوی فرمول‌هایی برای محاسبه قیمت فروش یک محصول هستند و به سلول‌های جدول خودشان مراجعه می‌کنند (به عنوان مثال، فرمول در سلول D14 شبیه = B14 * C14 + B14 است که به معنای ضرب قیمت خرید در مجموعه است. درصد نشانه گذاری، که مقدار نشانه گذاری را نشان می دهد که باید به قیمت خرید اضافه شود.

پس از انجام تمام عملیات با این جداول، "عملکرد" ​​آنها را بررسی کنید.

تغییر دادننام محصول - کاناپهدر سلول A4 روی یک برگه خریدبه دیگری - برای مثال کاناپه.

تغییر دادنمقدار کالای خریداری شده کاناپهدر ماه ژوئن (در سلول G4 در کاربرگ خریدعدد 11 را وارد کنید).

تغییر دادنقیمت خرید مبل در سلول B4 روی برگه قیمتبرای دیگری - 2500.00 روبل.

تغییر دادندرصد حاشیه مبل در سلول C14 روی ورق قیمتاز 50% تا 32%

بررسی ، در جداول مرتبط تغییراتی ایجاد شده است یا خیر؟

توجه داشته باشید، سلول های جداول مختلف تحت تأثیر تغییرات قرار گرفتند.

توجه! هنگام پیوند سلول ها از طریق PASTE SPECIAL... کپی کردن به سلول های مجاور به دلیل آدرس دهی مطلق سلول ها مشکل ساز می شود.

تمرین 1.سلول های بقیه جداول کتاب کار را با استفاده از روش های مختلف پیوند دهید.

دستورالعمل.در جداول محاسبه درآمد و درآمد 1 سه ماهه، از فرمول جمع سلول های منبع تنها 1 چهارم استفاده شده است.

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

دستورالعمل.

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

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

دستورالعمل.در جداول محاسبه درآمد و درآمد سه ماهه دوم، تنها برای 2 فصل از سلول های منبع استفاده کنید.

اگر نیاز داریم انتقال خودکار داده ها از یک صفحه گسترده اکسل به صفحه گسترده دیگر، باید این جداول را پیوند دهید. راه های مختلفی برای پیوند داده ها از دو جدول در اکسل وجود دارد. یک طرفه - با استفاده از یک فرمول، به مقاله "نحوه پیوند جداول در اکسل" مراجعه کنید.
در اینجا، بیایید در نظر بگیریم چگونه دو جدول را در کتاب های اکسل مختلف پیوند دهیم، با ایجاد روابط بین جداول. وقتی همه چیز تنظیم شد، کافی است یک جدول را باز کنید تا داده ها از جدول دیگری به این جدول منتقل شوند. برای اینکه اتصال کار کند نیازی نیست همه کتاب ها را باز کنید. با یک کتاب کار باز کار خواهد کرد.
بنابراین، ما دو کتاب کار اکسل داریم - Book1 و Book2. چنین جدولی در کتاب 1 وجود دارد که قبلاً پر شده است. میز 1.توجه!
اگر جدول دارای سلول‌ها ادغام شده باشد، پیوند در آنها کار نمی‌کند یا به بیراهه می‌رود.
در کتاب 2 ما همان جدول را درست می کنیم، اما آن را پر نمی کنیم.

تابع دیگری "Insert link" را می توان به این صورت فراخوانی کرد. ما همچنین کپی می کنیم، اما در منوی زمینه، روی تابع "Paste Special" کلیک کنید. در کادر محاوره ای که ظاهر می شود، روی دکمه "درج پیوند" کلیک کنید.