هنگام انجام وظایف خاص در اکسل، گاهی اوقات باید با جداول مختلفی برخوردار باشند که نیز به یکدیگر مرتبط هستند. به عبارت دیگر، داده های یک جدول به دیگران سفت می شود و مقادیر در تمام جداول مربوطه زمانی که تغییر می کنند، دوباره محاسبه می شوند.
جداول مرتبط بسیار مناسب برای استفاده از مقدار زیادی از اطلاعات استفاده می شود. تمام اطلاعات را در یک جدول قرار دهید، علاوه بر این، اگر همگن نیست، بسیار راحت نیست. دشوار است که با چنین اشیا کار کنید و برای آنها جستجو کنید. مشکل مشخص شده فقط برای از بین بردن جداول مربوطه، اطلاعات بین آن توزیع شده است، اما در همان زمان متصل شده است. جداول مرتبط ممکن است نه تنها در یک ورق یا یک کتاب، بلکه در کتاب های جداگانه (فایل ها) قرار گیرد. دو گزینه آخر در عمل اغلب مورد استفاده قرار می گیرند، زیرا هدف از این تکنولوژی فقط از انباشت داده ها دور می شود و گره آنها بر روی یک صفحه اساسا حل نمی کند. بیایید یاد بگیریم که چگونه ایجاد کنیم و چگونه با چنین نوع مدیریت داده کار کنیم.
ایجاد جداول مرتبط
اول از همه، اجازه دهید بر روی این سوال تمرکز کنیم، در این صورت امکان ایجاد ارتباط بین جداول مختلف وجود دارد.روش 1: فرمول جداول اتصال مستقیم
ساده ترین راه برای اتصال داده ها، استفاده از فرمول هایی است که در آن مراجع به جداول دیگر وجود دارد. این اتصال مستقیم نامیده می شود. این روش بصری است، زیرا زمانی که آن را تقریبا درست مانند ایجاد ارجاعات به داده ها در یک آرایه جدول متصل می کند.
بیایید ببینیم چگونه مثال شما می توانید ارتباط برقرار کنید. ما دو جدول در دو ورق داریم. در همان جدول، حقوق و دستمزد با استفاده از فرمول با ضرب کردن نرخ کارکنان برای یک ضریب واحد محاسبه می شود.
در ورق دوم محدوده میز وجود دارد که در آن لیستی از کارکنان با حقوق و دستمزد خود وجود دارد. فهرست کارکنان در هر دو مورد در یک سفارش ارائه شده است.
لازم است که این داده ها را بر روی شرط بندی از ورق دوم به سلول های مربوطه از اول ضربه بزنید.
- در اولین ورق، اولین سلول ستون "شرط" را اختصاص می دهیم. ما آن را علامت "=" قرار داده ایم. بعد، بر روی برچسب "ورق 2" کلیک کنید، که در قسمت سمت چپ رابط اکسل بر روی نوار وضعیت قرار می گیرد.
- جنبش در منطقه دوم سند وجود دارد. روی اولین سلول در ستون "شرط" کلیک کنید. سپس روی دکمه ENTER روی صفحه کلید کلیک کنید تا داده ها را به سلول وارد کنید که در آن علامت "برابر" قبلا نصب شده است.
- سپس انتقال اتوماتیک به ورقه اول وجود دارد. همانطور که می بینیم، ارزش اولین کارمند از جدول دوم به سلول مربوطه متصل می شود. با نصب مکان نما بر روی یک سلول حاوی شرط، می بینیم که فرمول معمول برای نمایش داده ها بر روی صفحه استفاده می شود. اما در مقابل مختصات سلول، از جایی که داده ها خروجی هستند، عبارت "list2!" وجود دارد، که نشان می دهد نام منطقه سند که در آن قرار دارد. فرمول کلی در مورد ما به نظر می رسد این است:
= list2! b2
- حالا شما باید داده ها را در مورد میزان سایر کارکنان شرکت انتقال دهید. البته، این کار را می توان به همان شیوه انجام داد که ما این کار را برای اولین کارمند انجام دادیم، اما با توجه به این که هر دو لیست کارکنان به همان ترتیب قرار گرفته اند، وظیفه می تواند به طور قابل توجهی ساده شده و با تصمیم گیری آن سریع تر شود. این را می توان به سادگی با کپی کردن فرمول به محدوده زیر انجام داد. با توجه به این واقعیت که مراجع به اکسل نسبی، هنگام کپی کردن ارزش ها، تغییر ارزش ها تغییر می کنند که ما نیاز داریم. روش کپی خود را می توان با استفاده از یک نشانگر پر کنید.
بنابراین، مکان نما را به قسمت پایین سمت راست عنصر با فرمول قرار داده ایم. پس از آن، مکان نما باید به نشانگر پر شدن به شکل یک صلیب سیاه تبدیل شود. ما گیره دکمه سمت چپ ماوس را انجام می دهیم و مکان نما را به تعداد ستون تبدیل می کنیم.
- تمام داده ها از یک ستون مشابه در یک ورق 2 به یک جدول بر روی ورق 1 کشیده شد. هنگامی که داده ها بر روی ورق 2 تغییر می کنند، آنها به طور خودکار در ابتدا تغییر خواهند کرد.
روش 2: با استفاده از غلبه بر شاخص اپراتورها - جستجو
اما چه کاری باید انجام دهید اگر لیستی از کارکنان در آرایه های جدول در همان نظم قرار نگیرد؟ در این مورد، همانطور که قبلا ذکر شد، یکی از گزینه ها این است که رابطه بین هر یک از این سلول ها را نصب کنید که باید به صورت دستی مرتبط باشد. اما مناسب است به جز جداول کوچک. برای محدوده های عظیم، این گزینه در بهترین زمان برای پیاده سازی زمان زیادی را صرف خواهد کرد، و در بدترین حالت - در عمل به طور کلی غیر واقعی خواهد بود. اما این مشکل را می توان با استفاده از یک دسته از شاخص اپراتورها - جستجو کرد. بیایید ببینیم که چگونه می توان آن را با استفاده از داده ها در جداول که در مورد مکالمه در روش قبلی انجام شد انجام شود.
- ما اولین عنصر ستون "شرط" را برجسته می کنیم. با کلیک کردن بر روی آیکون "Insert Function"، به جادوگر توابع بروید.
- در جادوگر توابع در گروه "لینک ها و آرایه ها" ما پیدا کردن و تخصیص نام "index".
- این اپراتور دارای دو فرم است: یک فرم برای کار با آرایه ها و مرجع. در مورد ما، اولین گزینه مورد نیاز است، بنابراین در پنجره انتخاب فرم بعدی که باز می شود، آن را انتخاب کنید و روی دکمه OK کلیک کنید.
- شاخص استدلال اپراتور شروع به کار می کند. وظیفه تابع مشخص شده خروجی مقدار واقع در محدوده انتخاب شده در خط با شماره مشخص شده است. شاخص اپراتور فرمول عمومی مانند:
= index (آرایه؛ number_name؛ [number_stolbits])
آرایه یک استدلال است که شامل محدوده محدوده ای است که ما اطلاعات را با تعداد ردیف مشخص شده استخراج خواهیم کرد.
"شماره ردیف" یک استدلال است که تعداد این خط است. مهم است بدانید که شماره خط باید مشخص شود نه نسبت به کل سند، بلکه تنها نسبت به آرایه اختصاص داده شده است.
"تعداد ستون" یک استدلال است که اختیاری است. برای حل به طور خاص از کار ما، ما از آن استفاده نخواهیم کرد، و بنابراین لازم نیست آن را به طور جداگانه توصیف کنیم.
ما مکان نما را در قسمت "آرایه" قرار داده ایم. پس از آن، به ورق 2 بروید و دکمه سمت چپ ماوس را نگه دارید، کل محتویات ستون "نرخ" را انتخاب کنید.
- پس از هماهنگی ها در پنجره اپراتور نمایش داده می شود، مکان نما را در قسمت ردیف شماره ردیف قرار می دهیم. ما این استدلال را با استفاده از اپراتور جستجو برداشتیم. بنابراین، بر روی یک مثلث کلیک کنید که در سمت چپ رشته تابع واقع شده است. لیستی از اپراتورهای تازه مورد استفاده باز می شود. اگر نام "شرکت جستجو" را در میان آنها پیدا کنید، می توانید بر روی آن کلیک کنید. در مورد مخالف، بر روی آخرین نقطه لیست کلیک کنید - "سایر توابع ...".
- پنجره Window Window Window شروع می شود. به آن در همان گروه "لینک ها و آرایه ها" بروید. این بار در لیست، مورد "شرکت جستجو" را انتخاب کنید. روی دکمه "OK" کلیک کنید.
- فعال سازی استدلال استدلال اپراتور جستجو انجام می شود. تابع مشخص شده برای خروجی مقدار مقدار در یک آرایه خاص با نام آن طراحی شده است. با تشکر از این ویژگی است که ما تعداد یک رشته از یک مقدار خاص برای عملکرد تابع را محاسبه می کنیم. نحو هیئت مدیره جستجو ارائه شده است:
= جستجو هیئت مدیره (search_name؛ مشاهده __nassive؛ [type_station])
"مورد نظر" یک آرگومان شامل نام یا آدرس سلول از محدوده شخص ثالث است که در آن واقع شده است. این موقعیت این نام در محدوده هدف است و باید محاسبه شود. در مورد ما، نقش آرگومان اول خواهد شد به سلول بر روی یک ورق 1، که در آن کارکنان در آن قرار دارد اشاره شده است.
"آرایه Listful" بحث و جدل، است که یک مرجع به یک آرایه، که به انجام جستجو برای مقدار مشخص برای تعیین موقعیت آن است. ما این نقش را به اجرا آدرس از "نام" ستون در یک ورق 2 است.
"نوع مقایسه با" - یک استدلال است که اختیاری است، اما، بر خلاف اپراتور های قبلی، این آرگومان اختیاری مورد نیاز خواهد بود. این نشان می دهد که چگونه برای مطابقت اپراتور ارزش مورد نظر را با یک آرایه است. این استدلال می توانید یکی از سه مقدار را داشته باشد: -1؛ 0؛ 1. برای آرایه های بی نظم، گزینه "0" را انتخاب کنید. این گزینه مناسب برای مورد ما است.
بنابراین، اقدام به پر کردن زمینه پنجره استدلال است. ما در بر مکان نما در زمینه "ارزش Fogular" قرار داده، با کلیک بر روی سلول اول "نام" ستون در یک جدول 1.
- پس از مختصات نمایش داده می شود، اشاره گر را در "لیست های عظیم" رشته تنظیم شده است و به "ورق 2" برچسب، که در پایین پنجره اکسل در بالای نوار وضعیت واقع شده است. کلمنت دکمه سمت چپ ماوس و برجسته مکان نما همه سلول های "نام" ستون.
- پس از مختصات خود را در زمینه "مثال های عظیم" نمایش داده شده، به "نقشه برداری نوع" میدان بروید و مجموعه ای از تعدادی "0" از صفحه کلید. پس از آن، ما دوباره به میدان "به دنبال از طریق آرایه" بازگشت. واقعیت این است که ما انجام خواهد شد کپی کردن فرمول، که ما در روش قبلی است. وجود خواهد داشت تغییر آدرس، اما در اینجا مختصات آرایه مشاهده ما نیاز به امن. او باید تغییر مکان نیست. ما در بر مختصات با مکان نما برجسته و کلیک بر روی کلید تابع F4. همانطور که می بینید، علامت دلار قبل از مختصات ظاهر شد، به این معنی که مرجع از نسبی تبدیل به مطلق است. سپس روی دکمه "OK" کلیک کنید.
- نتیجه این است که در سلول اول از "شرط" ستون نمایش داده شود. اما قبل از کپی کردن، ما نیاز به رفع منطقه دیگر، یعنی اولین فهرست آرگومان تابع. برای این کار، انتخاب عنصر ستون، که شامل یک فرمول، و حرکت به رشته فرمول. اختصاص آرگومان اول شاخص اپراتور (B2: B7) و کلیک بر روی دکمه F4. همانطور که می بینید، علامت دلار در نزدیکی مختصات انتخاب ظاهر شد. روی کلید Enter کلیک کنید. به طور کلی، فرمول به شکل زیر است:
= صفحه اول (SHEET2 $ B $ 2:! $ B $ 7؛ هیئت مدیره جستجو (SHEET1 A4؛ list2 $ A $ 2: 7 $ A $؛ 0))
- حالا شما می توانید با استفاده از یک نشانگر پر کردن کپی کنید. ما آن را به همان شیوه ای می نامیم که قبلا صحبت کرده ایم و به انتهای محدوده جدولی می پردازیم.
- همانطور که می بینید، علیرغم این واقعیت که ترتیب رشته ها در دو جداول مرتبط با آن هماهنگ نیست، با این حال، تمام مقادیر بر اساس نام کارگران سخت می شود. این به لطف استفاده از ترکیبی از اپراتورهای جستجوی شاخص به دست آمد.
روش 4: درج ویژه
آرایه های جدول کراوات در اکسل نیز می توانند از یک درج ویژه استفاده کنند.
- مقادیری را که می خواهید "را به یک جدول دیگر" سفت کنید را انتخاب کنید. در مورد ما، این "شرط" ستون ستون بر روی یک ورق است 2. بر روی قطعه اختصاصی با دکمه سمت راست ماوس کلیک کنید. در لیست باز می شود، مورد "کپی" را انتخاب کنید. ترکیبی جایگزین ترکیبی کلید Ctrl + C است. پس از آن، ما به ورق 1 حرکت می کنیم.
- انتقال به منطقه کتاب مورد نیاز، تخصیص سلول هایی که ارزش ها باید آن را تشدید کنند. در مورد ما، این ستون "BID" است. با کلیک بر روی قطعه اختصاصی با دکمه سمت راست ماوس کلیک کنید. در منوی زمینه در نوار ابزار "Insert Parameters"، روی نماد "Insert Communication" کلیک کنید.
همچنین یک جایگزین وجود دارد. او، به هر حال، تنها برای نسخه های قدیمی تر اکسل است. در منوی زمینه، مکان نما را به "مورد خاص" می آوریم. در منوی اضافی که باز می شود، موقعیت را با همان نام انتخاب کنید.
- پس از آن، یک پنجره درج ویژه باز می شود. روی دکمه "Insert Communication" در گوشه پایین سمت چپ سلول کلیک کنید.
- هر گزینه ای که انتخاب می کنید، مقادیر یک آرایه جدول به دیگری وارد می شود. هنگام تغییر داده ها در منبع، آنها نیز به طور خودکار در محدوده قرار می گیرند.
درس: قرار دادن ویژه در اکسل
روش 5: ارتباط بین جداول در چند کتاب
علاوه بر این، شما می توانید یک پیوند بین مناطق جدول در کتاب های مختلف را سازماندهی کنید. این از یک ابزار Insert خاص استفاده می کند. اقدامات کاملا شبیه به کسانی است که ما در روش قبلی در نظر گرفته ایم، به جز اینکه ناوبری در طول فرمول ها بین حوزه های یک کتاب، اما بین فایل ها نخواهد بود. به طور طبیعی، تمام کتاب های مرتبط باید باز شود.
- محدوده داده را انتخاب کنید تا به یک کتاب دیگر منتقل شود. روی دکمه راست راست کلیک کنید و موقعیت "کپی" را در منوی باز شده انتخاب کنید.
- سپس ما به کتاب می رویم که در آن این داده ها باید وارد شوند. محدوده مورد نظر را انتخاب کنید. دکمه راست راست را کلیک کنید. در منوی زمینه در گروه "تنظیمات قرار دادن"، آیتم "Insert Communication" را انتخاب کنید.
- پس از آن، ارزش ها وارد می شوند. هنگام تغییر داده ها در کتاب منبع، یک آرایه جدول از کتاب کاری آنها به صورت خودکار آنها را سفت می کند. و لازم نیست که اطمینان حاصل شود که هر دو کتاب باز هستند. این به اندازه کافی برای باز کردن تنها یک کتاب کار است، و اگر تغییرات قبلی در آن وجود داشته باشد، داده ها را از یک سند مرتبط با آن استفاده می کنند.
اما لازم به ذکر است که در این مورد، Inset به شکل یک آرایه بدون تغییر تولید می شود. هنگام تلاش برای تغییر هر سلول با داده های وارد شده، یک پیام پیام را در مورد ناتوانی در مورد عدم توانایی برای انجام این کار قرار می دهد.
تغییرات در چنین آرایه ای مرتبط با یک کتاب دیگر می تواند تنها اتصال را از بین ببرد.
عنوان بین جداول
گاهی اوقات لازم است که اتصال بین جداول را از بین ببریم. دلیل این امر می تواند موارد فوق را توضیح دهد زمانی که می خواهید یک آرایه را که از یک کتاب دیگر قرار داده شده و به سادگی بی میلی کاربر را تغییر دهید، به طوری که داده ها در همان جدول به طور خودکار از سوی دیگر به روز می شود.روش 1: ارتباطات بین کتاب ها
برای شکستن ارتباط بین کتاب ها در تمام سلول ها، با انجام یک عملیات در واقع. در این مورد، داده ها در سلول ها باقی خواهند ماند، اما آنها قبلا مقادیر به روز نمی شوند که به سایر اسناد بستگی ندارد.
- در کتاب که در آن مقادیر از فایل های دیگر سخت می شود، به برگه داده بروید. روی نماد "تغییر لینک ها" کلیک کنید، که بر روی نوار در نوار ابزار "اتصال" قرار دارد. لازم به ذکر است که اگر کتاب فعلی شامل اتصالات با سایر فایل ها نباشد، این دکمه غیر فعال است.
- پنجره تغییر لینک راه اندازی شده است. از لیست کتاب های مرتبط انتخاب کنید (اگر چند نفر از آنها وجود داشته باشند) فایل که ما می خواهیم اتصال را از بین ببریم. روی دکمه "شکستن اتصال" کلیک کنید.
- یک پنجره اطلاعاتی باز می شود، که هشدار در مورد پیامدهای اقدامات بیشتر را ارائه می دهد. اگر مطمئن هستید که می خواهید انجام دهید، روی دکمه "Break Communication" کلیک کنید.
- پس از آن، تمام مراجع به فایل مشخص شده در سند فعلی با مقادیر استاتیک جایگزین می شود.
روش 2: قرار دادن مقادیر
اما روش فوق فقط مناسب است اگر شما نیاز به تمام پیوندهای بین دو کتاب را کامل کنید. اگر شما نیاز به جدا شدن جداول مرتبط را در یک فایل مشابه دارید، چه؟ شما می توانید این کار را با کپی کردن داده ها انجام دهید و سپس همان محل را به عنوان مقادیر قرار دهید. به هر حال، این روش را می توان بین محدوده داده های مختلف کتاب های مختلف بدون شکستن یک رابطه مشترک بین فایل ها. بیایید ببینیم که این روش در عمل کار می کند.
- ما محدوده را برجسته می کنیم که در آن ما می خواهیم ارتباطات را با یک جدول دیگر حذف کنیم. روی دکمه راست راست کلیک کنید. در منوی باز، مورد "کپی" را انتخاب کنید. به جای اقدامات مشخص شده، می توانید ترکیبی جایگزین از کلیدهای داغ Ctrl + C را شماره گیری کنید.
- بعد، بدون از بین بردن انتخاب از همان قطعه، دوباره با کلیک بر روی آن با دکمه سمت راست ماوس. این بار در لیست عمل، بر روی نماد "value" کلیک کنید، که در گروه پارامترهای قرار داده شده است.
- پس از آن، تمام مراجع در محدوده اختصاصی با مقادیر استاتیک جایگزین خواهند شد.
همانطور که می بینید، اکسل دارای راه ها و ابزارهایی است که چندین جداول را در میان خود مرتبط می کند. در عین حال، داده های جدولی می توانند بر روی صفحات دیگر و حتی در کتاب های مختلف باشند. در صورت لزوم، این اتصال به راحتی می تواند شکسته شود.