کار با جداول مرتبط در مایکروسافت اکسل
هنگام انجام وظایف خاص در اکسل، گاهی اوقات شما باید با چندین جداول که با یکدیگر هم ارتباط دارند برخورد کنید. بدین معنی که داده ها از یک جدول به سمت دیگر منتقل می شوند و زمانی که آنها تغییر می کنند، مقادیر در همه جدول های مرتبط مربوطه محاسبه می شوند.
جداول مرتبط برای پردازش مقادیر زیاد اطلاعات بسیار مفید هستند. همه اطلاعات در یک جدول بسیار راحت نیست و اگر همگن نیست. دشوار است که با چنین اشیائی کار کنید و آنها را جستجو کنید. این مشکل در نظر گرفته شده است تا جداول مربوطه را از بین ببریم، اطلاعات بین آن توزیع شده است، اما در عین حال هم ارتباط دارد. محدوده جدول مرتبط می تواند نه تنها در یک ورق یا یک کتاب قرار گیرد، بلکه در کتاب های جداگانه (فایل ها) قرار می گیرد. دو گزینه آخر اغلب در عمل استفاده می شود، زیرا هدف از این تکنولوژی این است که از انباشت داده ها دور شویم و آنها را در یک صفحه قرار ندهیم اساسا مشکل را حل نمی کند. بیایید یاد بگیریم که چگونه برای ایجاد و نحوه کار با این نوع از مدیریت داده ها.
ایجاد جداول مرتبط
اول از همه، بیایید در مورد این موضوع که چگونه می توان یک لینک بین محدوده های جداول مختلف ایجاد کرد، بحث کنیم.
روش 1: به طور مستقیم جدول ها را با فرمول پیوند می دهد
ساده ترین راه برای اتصال داده ها، استفاده از فرمول هایی است که به سایر محدوده های جدول پیوند دارند. این اتصال مستقیم نامیده می شود. این روش بصری است، زیرا با آن اتصال در تقریبا یکسان انجام می شود، به عنوان ایجاد ارجاع به داده ها در یک آرایه جدول تنها انجام می شود.
بیایید ببینیم چگونه شما می توانید یک پیوند را با اتصال مستقیم با استفاده از مثال ایجاد کنید. دو جدول در دو ورق وجود دارد. در یک جدول، حقوق و دستمزد با استفاده از یک فرمول محاسبه می شود، ضرب تعداد کارگران با یک عامل واحد برای همه.
در صفحه دوم، محدوده جدولی وجود دارد که در آن یک لیست از کارکنان با حقوق آنها وجود دارد. لیست کارکنان در هر دو مورد به همان ترتیب ارائه می شود.
لازم است که اطلاعات مربوط به نرخ از ورق دوم در سلول های مربوطه از اول کشیده شود.
- در اولین ورق، اولین سلول را در ستون "Rate" انتخاب کنید. ما در آن نشانه "=" قرار داده ایم . بعد، بر روی برچسب "Sheet 2" کلیک کنید، که در سمت چپ رابط اکسل در بالای نوار وضعیت قرار دارد.
- به قسمت دوم سند حرکت می کند. روی اولین سلول ستون «Rate» کلیک کنید. سپس بر روی دکمه Enter بر روی صفحه کلید کلیک کنید تا ورود اطلاعات را به سلول که قبلا علامت برابر آن را تعیین کرده اید، وارد کنید.
- سپس یک انتقال خودکار به ورقه اول وجود دارد. همانطور که می بینید میزان اول کارمند از جدول دوم به سلول مناسب منتقل می شود. با قرار دادن مکان نما در سلول حاوی شرط بندی، می بینیم که فرمول معمول برای نمایش داده ها روی صفحه نمایش استفاده می شود. اما قبل از مختصات سلول، جایی که داده ها از آن نمایش داده می شود، عبارت "Sheet2!" وجود دارد ، که نشان دهنده نام منطقه سند است که در آن واقع شده است. فرمول کلی در مورد ما به شرح زیر است:
=Лист2!B2 - اکنون شما نیاز به انتقال داده ها در نرخ تمام کارمندان دیگر شرکت دارید. البته، این کار را می توان به همان شیوه انجام داد که کار را برای اولین کارمند انجام داد، اما با توجه به این که هر دو لیست کارکنان مرتب می شوند، این کار می تواند به طور قابل توجهی ساده شده و راه حل آن را سریعتر کند. این کار را می توان با کپی کردن فرمول به محدوده زیر انجام داد. با توجه به این که لینک ها در اکسل به طور پیش فرض نسبی هستند، وقتی که آنها کپی می شوند، ارزش ها تغییر می کنند، این چیزی است که ما نیاز داریم. روش خود کپی را می توان با استفاده از نشانگر پر نمود.
بنابراین، مکان نما را در قسمت پایین سمت راست عنصر با فرمول قرار دهید. پس از آن، مکان نما را باید به صورت یک صلیب سیاه پر کنید. ما گیره دکمه سمت چپ ماوس را انجام می دهیم و مکان نما را به پایین ترین ستون بکشید.
- تمام داده ها از همان ستون در جدول 2 به یک جدول در جدول 1 کشیدند. هنگامی که اطلاعات در صفحه 2 تغییر می کند، به صورت خودکار در اولین اولویت تغییر می کند.
روش 2: استفاده از یک دسته از اپراتورها INDEX - MATCH
اما اگر لیست کارکنان در آرایه های جدول در همان سفارش مرتب نشده باشند چه؟ در این مورد، همانطور که قبلا ذکر شد، یکی از گزینه ها تنظیم ارتباط بین هر سلولی است که باید به صورت دستی مرتبط شود. اما این فقط برای جداول کوچک مناسب است. برای محدوده های عظیم، این گزینه، در بهترین حالت، زمان زیادی را برای پیاده سازی، و در بدترین حالت - در عمل، امکان پذیر نخواهد بود. اما این مشکل را می توان با کمک یک دسته از اپراتورها INDEX - MATCH حل کرد . بیایید ببینیم چگونه می توان با اتصال داده ها در محدوده های جدول، که در روش قبلی مورد بحث قرار گرفته است، انجام شود.
- اولین عنصر ستون "شرط" را انتخاب کنید. با کلیک بر روی نماد "قرار دادن عملکرد" به کارشناسی ارشد توابع بروید.
- در کارشناسی ارشد توابع در گروه "لینک ها و آرایه ها" نام "INDEX" را پیدا می کنیم .
- این اپراتور دارای دو شکل است: یک فرم برای کار با آرایه ها و مرجع. در مورد ما، اولین گزینه لازم است، بنابراین در پنجره بعدی برای انتخاب یک فرم که باز خواهد شد، آن را انتخاب کنید و با کلیک بر روی دکمه "OK" .
- راه اندازی پنجره عملگر argument arguments INDEX . وظیفه تابع مشخص شده برای نمایش مقدار که در محدوده انتخاب شده در خط با شماره مشخص شده است. فرمول کلی برای اپراتور INDEX :
=ИНДЕКС(массив;номер_строки;[номер_столбца])"آرایه" یک استدلال است که حاوی آدرس محدوده ای است که از طریق تعداد رشته مشخص شده اطلاعات را استخراج می کنیم.
"شماره خط" یک استدلال است که شماره این خط است. مهم است بدانیم که شماره خط را نباید نسبت به کل سند تعیین شود، بلکه فقط مربوط به آرایه انتخاب شده است.
شماره ستون یک استدلال اختیاری است. برای حل مشکل خاص ما، از آن استفاده نخواهیم کرد، و بنابراین ضروری نیست که ماهیت آن را به طور جداگانه توصیف کنیم.
مکان را در قسمت "Array" قرار دهید . پس از آن، به برگه 2 بروید و با نگه داشتن دکمه سمت چپ ماوس، تمام محتویات ستون «شرط» را انتخاب کنید.
- پس از اینکه مختصات در پنجره اپراتور نمایش داده می شود، مکان نما را در فیلد «شماره خط» قرار دهید. ما این استدلال را با استفاده از اپراتور MATCH استخراج خواهیم کرد. بنابراین، ما بر روی مثلث کلیک می کنیم که در سمت چپ خط عملکرد قرار دارد. یک لیست از اپراتورهای اخیرا استفاده شده باز می شود. اگر نام "MATCH" را در بین آنها پیدا کنید، می توانید روی آن کلیک کنید. در مورد مخالف، بر روی آخرین مورد در لیست کلیک کنید - "سایر توابع ..." .
- پنجره استاندارد Wizard Function را اجرا می کند . به آن گروه "لینک ها و آرایه ها" بروید . در این زمان در لیست، مورد "MATCH" را انتخاب کنید . با کلیک بر روی دکمه "OK" یک کلیک انجام دهید.
- پنجره استدلال اپراتور MATCH فعال شده است . تابع مشخص شده برای نشان دادن شماره یک مقدار در یک آرایه خاص با نام آن است. به لطف این فرصت است که ما مقدار ردیف یک مقدار خاص برای عملکرد INDEX محاسبه کنیم. نحو MATCH به شرح زیر است:
=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])"مقدار مورد نیاز" یک استدلال است که حاوی نام یا آدرس یک سلول در محدوده شخص ثالث است که در آن واقع شده است. موقعیت این نام در محدوده هدف است که باید محاسبه شود. در مورد ما، اولین استدلال، اشاره به سلول ها در صفحه 1 است ، که در آن نام کارکنان قرار دارد.
"آرایه مرور" یک استدلال است که نشان دهنده یک پیوند به یک آرایه است که در آن یک مقدار مشخص شده برای تعیین موقعیت آن جستجو می شود. ما این نقش را به عنوان ستون « نام» در صفحه 2 بازی خواهیم کرد.
"Matching Type" استدلالی است که اختیاری است، اما، بر خلاف بیانیه قبلی، ما باید به این استدلال اختیاری نیاز داریم. این نشان می دهد که چگونه اپراتور مقدار دلخواه با آرایه را مطابقت می دهد. این استدلال می تواند یکی از سه ارزش داشته باشد: -1 ؛ 0 ؛ 1 برای آرایه های غیر ارادی، گزینه "0" را انتخاب کنید . این گزینه برای مورد ما مناسب است.
بنابراين، شروع به پر كردن حوزه هاي پنجره استدلال مي كنيم. مکان را در فیلد "مورد نیاز" قرار دهید، روی اولین سلول ستون "نام" در صفحه 1 کلیک کنید .
- پس از نمایش مختصات، مکان نما را در فیلد "آرایه قابل مشاهده" تنظیم کنید و به برچسب "Sheet 2" بروید که در پایین پنجره اکسل در بالای نوار وضعیت قرار دارد. پایین دکمه سمت چپ ماوس را نگه دارید و با کلیدهای سلول در ستون "Name" را انتخاب کنید.
- بعد از اینکه مختصات آنها در قسمت "آرایه مشاهده شده" نمایش داده می شود، به فیلد "نوع تطبیق" بروید و شماره "0" را در آنجا تنظیم کنید . پس از این، ما دوباره به فیلد "آرایه مشاهده شده" بازگشتیم. واقعیت این است که ما فرمول را کپی خواهیم کرد، همانطور که در روش قبلی انجام دادیم. یک آدرس جابجایی وجود دارد، اما ما باید مختصات آرایه ای که مشاهده می شود را رفع کنیم. این نباید تغییر کند. مختصات را با مکان نما انتخاب کنید و روی کلید عملکرد F4 کلیک کنید. همانطور که می بینید، علامت دلار در مقابل مختصات نمایش داده می شود، به این معنی که لینک از نسبی به مطلق تبدیل شده است. سپس بر روی دکمه "OK" کلیک کنید.
- نتیجه در اولین سلول ستون "شرط" نمایش داده می شود. اما قبل از کپی کردن، ما باید یک منطقه دیگر را اصلاح کنیم، یعنی اولین پارامتر تابع INDEX . برای انجام این کار عنصر ستون حاوی فرمول را انتخاب کنید و به نوار فرمول حرکت کنید. اولین پارامتر عملگر INDEX ( B2: B7 ) را انتخاب کنید و دکمه F4 را کلیک کنید. همانطور که می بینید، علامت دلار در نزدیکی مختصات انتخاب شده ظاهر شد. روی کلید Enter کلیک کنید . به طور کلی، فرمول فرم زیر را دریافت کرد:
=ИНДЕКС(Лист2!$B$2:$B$7;ПОИСКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0)) - اکنون می توانید با استفاده از نشانگر پرینت کپی کنید. آن را به همان شیوه ای که ما در مورد آن صحبت کردیم، بکشیم و آن را به انتهای محدوده جدول گسترش دهیم.
- همانطور که می بینید، به رغم این واقعیت که دستور ردیف دو جدول مربوطه مطابقت ندارد، با این حال، تمام ارزش ها بر اساس نام کارگران سخت تر می شوند. این از طریق استفاده ترکیبی از اپراتورهای INDEX - MATCH به دست آمد .
همچنین ببینید:
اکسل تابع INDEX
تابع مسابقه در اکسل
روش 3: انجام عملیات ریاضی همراه با داده های مرتبط
اتصال مستقیم مستقیم نیز خوب است زیرا اجازه می دهد تا نه تنها برای نمایش مقادیری که در جداول جدول دیگر در یکی از جداول نمایش داده می شوند بلکه همچنین عملیات ریاضی مختلف با آنها (افزودن، تقسیم، تفریق، ضرب، و غیره) انجام شود.
بیایید ببینیم چگونه این کار در عمل انجام می شود. ما این کار را انجام خواهیم داد که بر روی صفحه 3 داده های کلی حقوق ماهانه برای شرکت بدون نشان دادن کارکنان نمایش داده می شود. برای انجام این کار، نرخ کارکنان از ورق 2 کشیده می شود، با استفاده از عملکرد SUM (با استفاده از عملکرد SUM ) جمع می شود و با استفاده از فرمول یک عامل افزایش می یابد.
- سلول را انتخاب کنید که در آن مجموع کل حقوق و دستمزد در صفحه 3 نمایش داده خواهد شد. با کلیک بر روی دکمه "قرار دادن عملکرد" .
- باید جادوگر تابع را راه اندازی کند. به گروه "ریاضی" بروید و نام "SUMM" را در آنجا انتخاب کنید . بعد، روی دکمه "OK" کلیک کنید.
- این تابع به پنجره استدلال عملکرد SUM منتقل شده است، که برای محاسبه مجموع اعداد انتخاب شده طراحی شده است. این نحو زیر دارد:
=СУММ(число1;число2;…)فیلدها در پنجره مربوط به استدلال تابع مشخص شده است. اگر چه تعداد آنها می تواند به 255 قطعه برسد، اما تنها هدف ما تنها کافی است. مکان را در قسمت "شماره 1" قرار دهید . بر روی برچسب "برگه 2" بالای نوار وضعیت کلیک کنید.
- بعد از اینکه ما به بخش مورد نظر کتاب منتقل شدیم ستون را که باید جمع کرد را انتخاب کنید. ما آن را مکان نما، نگه داشتن دکمه سمت چپ ماوس. همانطور که می بینید مختصات منطقه انتخاب شده بلافاصله در زمینه پنجره argument نمایش داده می شود. سپس بر روی دکمه "OK" کلیک کنید.
- پس از آن ما به صورت خودکار به Sheet 1 حرکت می کنیم. همانطور که می بینید، میزان کل دستمزد کارگران در عنصر مربوطه نمایش داده می شود.
- اما این همه نیست همانطور که ما به یاد می آوریم، حقوق و دستمزد با ضرب کردن ارزش نرخ با ضریب محاسبه می شود. بنابراین، ما دوباره سلول هایی را که در آن مقدار خلاصه شده قرار دارد انتخاب می کنیم. پس از آن به نوار فرمول بروید. ما علامت ضرب ( * ) را به آن فرمول اضافه می کنیم و سپس بر روی عنصر که ضریب آن قرار دارد کلیک کنید. برای انجام محاسبات، بر روی کلید Enter بر روی صفحه کلید کلیک کنید. همانطور که می بینید، برنامه کل دستمزد برای شرکت را محاسبه کرد.
- ما به برگه 2 برگشتیم و میزان نرخ هر کارمند را تغییر دادیم.
- پس از آن دوباره به صفحه با مقدار کل حرکت می کند. همانطور که می بینید، به علت تغییر در جدول مربوطه، نتیجه کل دستمزد به صورت خودکار محاسبه شد.
روش 4: قرار دادن ویژه
همچنین می توانید آرایه های جدول را در Excel وارد کنید.
- مقادیری را که باید به یک جدول دیگر تسریع شود را انتخاب کنید. در مورد ما، این محدوده ستون "Bid" در صفحه 2 است . با کلیک بر روی دکمه انتخاب راست کلیک بر روی بخش انتخاب شده کلیک کنید. در لیستی که باز می شود، مورد "کپی" را انتخاب کنید . یک ترکیب جایگزین ترکیبی کلید Ctrl + C. پس از آن به صفحه 1 بروید .
- حرکت به منطقه مورد نظر از کتاب، ما سلول هایی را انتخاب می کنیم که می خواهید ارزش ها را بکشید. در مورد ما، این ستون "Bid" است. با کلیک بر روی دکمه انتخاب راست کلیک بر روی بخش انتخاب شده کلیک کنید. در منوی context در نوار ابزار «Insert Parameters» روی نماد «Insert Link» کلیک کنید.
![یک اتصال را از طریق منوی زمینه در مایکروسافت اکسل وارد کنید]()
همچنین یک جایگزین وجود دارد. به هر حال، این تنها برای نسخه های قدیمی تر Excel است. در منوی زمینه، مکان نما را به قسمت "Paste Special" حرکت دهید. در منوی اضافی که باز می شود، آیتم با همان نام را انتخاب کنید.
- پس از آن، یک پنجره ورودی ویژه باز می شود. روی دکمه "Insert link" را در گوشه پایین سمت چپ سلول کلیک کنید.
- هر کدام از گزینه هایی که انتخاب می کنید، مقادیر از یک آرایه جدول به یک دیگر وارد می شوند. هنگامی که داده ها را در منبع تغییر می دهید، آنها نیز به طور خودکار در محدوده وارد شده تغییر خواهند کرد.
روش 5: رابطه بین جداول در چندین کتاب
علاوه بر این، شما می توانید اتصال بین جداول در کتاب های مختلف را سازماندهی کنید. این از ابزار درج ویژه استفاده می کند. اقدامات کاملا شبیه به آنچه که ما در روش قبلی در نظر گرفتیم، جز این که ناوبری در هنگام معرفی فرمولها نباید بین مناطق یک کتاب رخ دهد، بلکه بین فایلها رخ می دهد. به طور طبیعی، تمام کتابهای مرتبط باید باز باشد.
- طیف وسیعی از داده های مورد نظر برای انتقال به یک کتاب دیگر را انتخاب کنید. ما با کلیک راست بر روی آن کلیک کرده و آیتم «کپی» را در منوی باز انتخاب کنید.
- سپس ما به کتاب حرکت می کنیم که در آن این داده ها باید وارد شوند. محدوده مورد نظر را انتخاب کنید. با کلیک بر روی دکمه راست موس کلیک کنید. در منوی context در گروه «Insert Parameters»، گزینه «Insert Link» را انتخاب کنید.
- بعد از این، مقادیر وارد می شوند. هنگام تغییر داده ها در کتاب منبع، آرایه جدولی از کتاب کار به طور خودکار آنها را می کشد. و هر دو کتاب برای این کار باز نمی شود. کافی است که فقط یک کتاب را باز کنید و اگر داده ها از سند بسته شده به طور خودکار حذف شوند، اگر تغییرات قبلا در آن ساخته شده باشد.
اما لازم به ذکر است که در این حالت، قرار دادن به صورت یک آرایه ناپایدار ساخته می شود. اگر سعی کنید هر سلول با داده های وارد شده را تغییر دهید، پیامی به شما اطلاع خواهد داد که نمی تواند انجام شود.
تغییر در چنین آرایه ای که با کتاب دیگری همراه است، می تواند تنها با شکستن لینک ساخته شود.
جدا کردن جداول
گاهی اوقات شما نیاز به شکستن لینک بین دامنه های جدول. دلیل آن ممکن است، همانطور که در بالا توضیح داده شد، زمانی که می خواهید یک آرایه وارد شده از یک کتاب دیگر را تغییر دهید، یا به سادگی به این دلیل که کاربر نمی خواهد داده ها در یک جدول به صورت خودکار از یک دیگر به روز شود.
روش 1: بین کتاب ها قطع شود
شما می توانید از طریق انجام یک عملیات بین کتابها در تمام سلول ها را شکست دهید. در عین حال، داده های موجود در سلول ها باقی خواهند ماند، اما در حال حاضر مقادیر غیر استاتیک استاتیکی هستند که به هیچ وجه به سایر اسناد وابسته نیستند.
- در کتاب، که در آن ارزش ها از فایل های دیگر کشیده شده، به برگه "داده" بروید. ما بر روی نماد "تغییر اتصالات" کلیک میکنیم، که بر روی نوار در بلوک ابزار "Connections" قرار دارد. لازم به ذکر است که اگر کتاب کنونی لینک هایی را به فایل های دیگر نمی دهد، این دکمه غیرفعال است.
- پنجره برای تغییر لینک ها راه اندازی شده است. از میان لیستی از کتابهای مرتبط (اگر چندین وجود دارد) را انتخاب کنید که با آن می خواهیم ارتباط برقرار کنیم. بر روی دکمه "شکستن لینک کلیک کنید " را کلیک کنید .
- یک پنجره اطلاعاتی باز می شود که هشدار درمورد پیامدهای اقدامات بعدی وجود دارد. اگر مطمئن هستید که در مورد چه کاری می خواهید انجام دهید، روی دکمه "Break links" کلیک کنید.
- پس از آن، تمام پیوندهایی به فایل مشخص شده در سند فعلی با مقادیر ایستا جایگزین خواهند شد.
روش 2: مقادیر را وارد کنید
اما روش فوق مناسب است فقط اگر شما نیاز دارید که تمام ارتباطات بین دو کتاب را کاملا قطع کنید. اگر میخواهید جداول مربوطه را در یک فایل جداگانه جدا کنید چه کاری باید انجام دهید؟ شما می توانید این کار را با کپی کردن داده ها انجام دهید و سپس آن را به همان اندازه از مقادیر قرار دهید. به هر حال، همان روش را می توان برای شکستن اتصال بین محدوده داده های جداگانه کتاب های مختلف بدون شکستن اتصال عمومی بین فایل ها استفاده می شود. بیایید ببینیم که چگونه این روش در عمل کار می کند.
- محدوده ای را انتخاب کنید که ما می خواهیم لینک را به یک جدول دیگر حذف کنیم. روی دکمه ی راست کلیک آن کلیک کنید. در منوی کشویی گزینه "کپی" را انتخاب کنید . به جای این اقدامات، شما می توانید ترکیبی از کلید های ترکیبی Ctrl + C را تایپ کنید .
- بعد، بدون حذف انتخاب از یک قطعه، دوباره با کلیک راست با کلیک بر روی آن کلیک کنید. این بار، در لیست اقدامات، بر روی آیکون "ارزش ها" کلیک کنید، که در گروه ابزار "وارد کردن پارامترها" قرار دارد .
- پس از آن، تمام لینک های موجود در محدوده انتخاب شده با مقادیر ایستا جایگزین خواهند شد.
همانطور که می بینید، اکسل روش ها و ابزارهایی برای پیوند چندین جدول با هم دارد. در این مورد، داده های جدولی ممکن است بر روی ورق های دیگر و حتی در کتاب های مختلف باشد. در صورت لزوم، این اتصال می تواند به آسانی شکسته شود.