یافتن عدد واقع در سطر و ستون جدول

یافتن عدد واقع در سطر و ستون جدول

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

در این آموزش از تابع INDEX و ابزارهای CONDITIONAL FORMATTING و DATA VALIDATION استفاده می کنیم.

جدولی مطابق شکل زیر داریم که از 6 ردیف و 5 ستون تشکیل شده است. تعداد 30 عدد مختلف در این جدول قرار دارد (داده ها می توانند تکراری هم باشند). می خواهیم سطر و ستون دلخواه را وارد نموده و با استفاده از تابع INDEX مقدار واقع در تقاطع این سطر و ستون را بدست آوریم. ضمناً عدد پیدا شده نیز باید با یک رنگ دلخواه در جدول مشخص گردد.

ابتدا جدول را تشکیل داده و اعداد را در آن وارد می کنیم. 

یافتن عدد واقع در سطر و ستون جدول

در کنار جدول در دو سلول عبارت های “سطر و ستون ” را وارد کرده و در زیر هر کدام یک عدد که بیانگر شماره سطر و ستون باشد را وارد می کنیم. با استفاده از ابزار DATA VALIDATION مقادیری که توسط کاربر وارد می شود را محدود می کنیم تا کاربر نتواند عددی بیش از تعداد سطر و ستون های جدول را وارد نماید و اگر چنین اتفاقی بیفتد، با یک پیام مناسب به کاربر هشدار داده و مقدار وردی غلط را نپذیرد.

برای سلول حاوی شماره ستون نیز دقیقاً مراحل تصویری بالا را تکرار می کنیم با این تفاوت که به جای عدد 6 باید عدد 5 را قرار دهیم، چون تعداد ستون های جدول ما 5 عدد می باشد.

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

( INDEX(I5:M10,F5,G5=

در این فرمول I5:M10 محدوده جدول و F5 سلول حاوی شماره سطر و G5 سلول حاوی شماره ستون می باشد.

یافتن عدد واقع در سطر و ستون جدول

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

برای این کار ابتدا محدوده داده های جدول را انتخاب می کنیم. سپس مطابق تصاویر با استفاده از ابزار CONDITIONAL FORMATTING فرمت مورد نظر را برای محدوده داده های جدول تنظیم می کنیم.

تصاویر 6-7-8-9

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

(AND(ROW()=$F$10+4,COLUMN()=$G$10+8=

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

ضمناً $F$10  و $G$10 مقادیر سلول سطر و ستونی هستند که قبلاً وارد کرده ایم. (برای جلوگیری از تغییر سلول حاوی شماره سطر و ستون این آدرسها به صورت مطلق وارد شده اند)

اما چرا عدد 4 به مقدار سطر (ردیف)  و عدد 8 به مقدار ستون اضافه شده اند؟

اگر به موقعیت جدول اطلاعات دقت کرده باشید اولین ردیف اطلاعات ما در سطر (ردیف) پنجم شیت قرار دارد و همچنین اولین ستون جدول اطلاعات از ستون نهم شروع شده، بنابراین برای هدایت فرمت مورد نظر به درون داده های جدول، این مقادیر به فرمول اضافه شده اند.

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

دانلود فایل این مقاله