آموزش اكسل Excel

فرض كنيد به افرادي كه با شما كار مي كنند در تاريخ هاي مختلف مبالغي را به عنوان مساعده حقوق پرداخت كرده ايد. در يك شيت اسامي و مشخصات نفرات همراه با كد پرسنلي هر شخص ثبت شده است. در شيت ديگر مبلغ مساعده هاي پرداختي را فقط با ذكر شماره پرسنلي هر فرد ياداشت كرده ايد. اكنون اگر بخواهيد بدانيد كه جمع دريافتي هر نفر و تعداد دريافت هاي او چقدر است در شيتي به نام “جمع پرداختي” فرمول هاي لازم جهت اين كار نوشته شده است.

در شيت “پرسنل”  اسامي و مشخصات پرسنل ثبت شده است. در شيت “پرداختها” مبالغ پرداختي به پرسنل و در شيت “جمع پرداختي” جمع مبالغ پرداختي به هر شخص مشخص ميگردد.

فرض كنيد عدد 265 را در خانه M3 نوشته ايم و ميخواهيم با پيدا كردن يك فرمول جمع رقمهاي اين عدد را بدست آوريم يعني (13=5+6+2)

هدف از بدست آوردن اين فرمول آموزش تعدادي از توابع متني اكسل است كه در ادامه مثال آنها را شرح خواهم داد.

براي محاسبه مجموع ارقام ابتدا بايد بتوانيم رقم هاي عدد داده شده را از هم جدا كنيم. براي اين منظور از تابع MID استفاده مي كنيم. شكل كلي اين تابع به اين صورت مي باشد.

=MID(Text,Start_num,num_chars)

در اين فرمولtext  رشته اي است كه ميخواهيم تابع MID روي آن عمل كند. Strat_num شروع و num_chars تعداد حروفي است كه مي خواهيم تابع آن را جدا كند. در مثال فوق براي جدا كردن رقمها، تابع را به صورت زير استفاده مي كنيم.

هدف از اين پست آموزش ايجاد ليست پايين افتادني هوشمند است كه با تايپ هر كلمه، كليه نام هاي داراي حرف تايپ شده را جهت انتخاب به كاربر پيشنهاد دهد.

براي ايجاد چنين ليستي بايد مراحل زير را انجام دهيم

مرحله اول: ايجاد و تنظيم باكس جستجو

در اين مرحله ما يك كامبو باكس داريم  بايد تنظيمات آن را طوري انجام دهيم كه هنگام تايپ متن در اين كامبوباكس، متن آن نيز در جعبه جستجو ظاهر شود. براي انجام اين كار به شرح ذيل عمل مي كنيم.

در تب (سربرگ) developer  و در بخش ActiveX Control ابزار ComboBox را انتخاب مي كنيم (اگر سربرگ Developer در نوار ريبون ديده نمي شود مسير زير را براي فعال كردن آن طي كنيد.

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

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

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

ابتدا جدول را تشكيل داده و اعداد را در آن وارد مي كنيم.

تابع subtotal براي انجام كارهايي از قبيل جمع كردن، محاسبه ميانگين، محاسبه ماكزيمم و مينيمم و … روي يك محدوده كاربرد دارد.

اين تابع با توجه به نوع پارامتري كه براي آن تعيين ميكنيم مي تواند عمليات مختلفي را روي محدوده مورد نظر انجام دهد.

شكل كلي تابع SUBTOTAL

=SUBTOTAL( محدوده مورد نظر, شماره تابع)

شماره تابع

شماره هاي تابع از 1 تا 11 و از 101 تا 111 هستند. هر كدام از اين شماره ها به صورت يك تابع مجزا عمل مي كنند. در واقع هر دو دسته اين توابع يكسان هستند در شماره هاي 1 تا 11 اگر سلول يا سلول هايي پنهان شده باشند در محاسبات شركت داده مي شوند (يعني نتيجه آنها توسط فرمول محاسبه ميگردد) و اگر از شماره هاي 101 تا 111 استفاده شود نتيجه سلول هاي پنهان در محاسبات منظور نمي شوند.

توابع حذف كاراكتر‌هاي اضافي
CLEAN   حذف تمام كاراكتر‌هاي غير قابل چاپ.
TRIM   حذف فاصله‌هاي اضافي و فاصله‌ها در آغاز و پايان عبارت.

توابع تبديل حروف كوچك و بزرگ
LOWER   تمامي كاراكتر‌ها را به حروف كوچك تبديل مي‌كند.
PROPER   كاراكتر‌هاي موجود در يك متن را به حالت استاندارد تبديل مي‌كند.
UPPER   تمامي كاراكتر‌ها را به حروف بزرگ تبديل مي‌كند.

تبديل حروف به اعداد به يكديگر
CHAR   تبديل كد ASCII به حروف.
CODE   تبديل حروف به كد ASCII.

جايگزين كردن قسمتي از يك رشته متن
REPLACE   جاي كاراكتر مورد نظر كاراكتر ديگري را قرار مي‌دهد.
SUBSTITUTE   جاي عبارت درج شده در يك جمله عبارت ديگري را قرار مي‌دهد.

امروزه در هر شركت و سازماني، فرم ثبت ساعت ورود و خروج افراد و محاسبه ساعات اضافه كاري و كسر كار يكي از نيازهاي اساسي است. به اين منظور فرم ثبت ساعت ورود ورود و خروج را جهت استفاده شما عزيزان قرار داده ام. در اين فرم ساعت ورود 8 صبح تعريف شده (شما قادر به ثبت ساعت قبل از 8 صبح نيستيد) و ساعت خروج نيز 24 در نظر گرفته شده است. اگر ساعت ورود و يا خروج را كامل نكنيد پيام اخطار مناسب به شما داده خواهد شد.