کار با جداول مرتبط در مایکروسافت اکسل

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

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

ایجاد جداول مرتبط

اول از همه، بیایید در مورد این موضوع که چگونه می توان یک پیوند بین محدوده های جداول مختلف ایجاد کرد، بحث می کنیم.

روش 1: جداکردن جداول با یک فرمول

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

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

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

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

1. در اولین ورق، سلول اول ستون را انتخاب کنید. "شرط". ما در علامت خود قرار داده ایم "=". بعد، بر روی برچسب کلیک کنید "ورق 2"کدام در سمت چپ رابط اکسل در بالای نوار وضعیت قرار دارد.



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



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

   = Sheet2! B2



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

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



5. همه داده ها از همان ستون در ورق 2 بر روی میز کشیده شد ورق 1. هنگامی که داده ها تغییر می کنند ورق 2 آنها به صورت خودکار در اولین تغییر خواهند کرد.

روش 2: استفاده از یک دسته از اپراتورها INDEX - MATCH

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

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



2. در جادوگر عملکرد در یک گروه "لینک ها و آرایه ها" پیدا کردن و انتخاب نام INDEX.



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



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

   = INDEX (array؛ line_number؛ [column_number])

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

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

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

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



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



6. پنجره استاندارد شروع می شود. کارشناسی ارشد. به همین گروه بروید "لینک ها و آرایه ها". این بار در لیست، مورد را انتخاب کنید "MATCH". یک کلیک بر روی دکمه انجام دهید. "خوب".



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

   = MATCH (مقدار جستجو؛ آرایه مراجعه؛ [match_type])

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

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

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

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



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



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



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

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



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



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

همچنین ببینید:
اکسل تابع INDEX
تابع مسابقه در اکسل

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

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

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

1. سلول را انتخاب کنید که در آن کل payroll نمایش داده خواهد شد ورق 3. روی دکمه کلیک کنید "قرار دادن عملکرد".



2. این باید پنجره را راه اندازی کند کارشناسی ارشد. برو به گروه "ریاضی" و نام آن را انتخاب کن "SUMM". بعد، روی دکمه کلیک کنید "خوب".



3. حرکت به پنجره argument function مبلغکه برای محاسبه مجموع اعداد انتخاب شده طراحی شده است. این نحو زیر دارد:

   = SUM (number1؛ number2؛ ...)

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



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



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



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



7. بازگشت به ورق 2 و تغییر اندازه هر کارمند را تغییر دهید.



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

روش 4: قرار دادن ویژه

همچنین می توانید آرایه های جدول را در Excel وارد کنید.

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



2. حرکت به منطقه مورد نظر از کتاب، ما سلول هایی را انتخاب می کنیم که می خواهید ارزش ها را بکشید. در مورد ما این ستون است. "شرط". با دکمه سمت راست ماوس بر روی بخش انتخاب شده کلیک کنید. در منوی زمینه در نوار ابزار "گزینه های درج" روی نماد کلیک کنید "درج لینک".

   

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



3. پس از آن یک پنجره ورودی ویژه باز می شود. ما دکمه را فشار می دهیم "درج لینک" در گوشه پایین سمت چپ سلول.



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

درس: چسباندن ویژه در اکسل

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

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

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



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



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

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

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

قطع اتصال بین جداول

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

روش 1: بین کتاب ها قطع شود

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

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



2. پنجره برای تغییر لینک ها راه اندازی شده است. از میان لیستی از کتابهای مربوطه (اگر چندین وجود دارد) را انتخاب کنید که با آن می خواهیم ارتباط برقرار کنیم. روی دکمه کلیک کنید "شکستن لینک".



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



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

روش 2: مقادیر را وارد کنید

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

1. محدوده ای را انتخاب کنید که ما می خواهیم لینک را به جدول دیگری حذف کنیم. روی دکمه ی راست کلیک آن کلیک کنید. در منوی باز شده، مورد را انتخاب کنید "کپی". به جای این اقدامات، می توانید ترکیبی از کلید های جایگزین را تایپ کنید. Ctrl + C.



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



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

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