۱۱ ترفند براي كار با اكسل

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

البته واضح است كه تنها با كليدهاي جهت دار مي‌توانيد از يك سلول به سلول ديگري حركت كنيد. اما استفاده از كليدهاي جهت دار + Ctrl اين روند را سريع‌تر مي‌كند.

كليدهاي تركيبي Ctrl + Shift+ و كليدهاي جهت دار كمك مي‌كند محدوده‌ي داده‌ها را تا آخرين سلول داراي داده انتخاب كنيد.

براي كپي يك فرمول لازم نيست حتما آن را تا آخرين سلول درگ كنيد. فقط كافي است روي گوشه‌ي پايين سمت راست سلول فرمول دو بار كليك كنيد.

با استفاده از كليدهاي تركيبي !+Ctrl + Shift مي‌توانيد فرمت عدد را به دو رقم اعشار تبديل كنيد. براي تبديل به فرمت دلار كافي است از كليدهاي تركيبي $+Ctrl + Shift و براي تبديل به درصد از كليدهاي تركيبي %+Ctrl + Shift استفاده كنيد. در ادامه مي‌توانيد با كليك بر روي لينك ترفندهاي حرفه‌اي در اكسل را مطالعه كنيد.

گاهي اوقات براي كپي داده‌ي يك سلول مي‌خواهيد فرمول آن هم كپي شود. گاهي هم مي‌خواهيد كه اين كپي صورت نگيرد. وقتي مي‌خواهيد ورودي سلول را قفل كنيد مي‌توانيد يك علامت $ قبل از اسم سلول (مثلا B1) قرار دهيد. تايپ كردن $ قبل از نام هم سلول كار وقت‌گير و آزاردهنده‌اي است. در عوض مي‌توانيد به جاي تايپ $ از كليد F4 استفاده كنيد تا علامت $ ايجاد شود. با هر بار فشار دادن F4 حالت‌هاي قفل سلول، قفل رديف، قفل ستون و حالت بدون قفل فعال مي‌شود.

تابع CountIF تعداد دفعات تكرار يك داده را در محدوده‌ي انتخاب شده مشخص مي‌كند. اولين ورودي اين تابع محدوده‌ي مورد انديشه متخصصين و دومين ورودي مربوط به شرط مورد انديشه متخصصين ما است. براي مثال در حالت زير اسم چند نويسنده به نام‌هاي سانسا، اريا، راب، جان، ريكون و برن آورده شده است. هر كدام از آن‌ها چند داستان نوشته‌اند كه هر داستاني تعدادي بازديدكننده داشته است. براي مرتب كردن داده‌ها در يك سلول خالي دستور CountIF را نوشته تا از ستون اسم نويسنده‌ها اسم آريا را شمارش كند. پس اولين ورودي تابع ستون B4 تا B50 است و دومين ورودي آن سلول G4‌ است كه اسم آريا در آن نوشته شده است.

حالا مي‌خواهيم كل بازديدكنندگان هر داستان را شمارش كنيم. در اين حالت از تابع SumIF استفاده مي‌كنيم. اين تابع سه ورودي دارد. ورودي اول ستون B4 تا B50 است. ورودي دوم سلول مربوط به اسم نويسنده است و ورودي سوم ستون مربوط به تعداد بازديدكنندگان است. حاصل اين تابع به عنوان مجموع كل بازديدكنندگان آثار يك نويسنده در مقابل اسم وي نمايش داده مي‌شود.

فرض كنيد ليستي طولاني از نام چند دانشگاه در اختيار داريم كه رتبه‌ي هر يك از آن‌ها در سلول مقابلش درج شده است. براي اين كه بتوانيم در اين ليست طولاني، ۵ دانشگاه اول را پيدا كنيم از تابع VLOOKUP استفاده مي‌كنيم.

تابع VLOOKUP چهار ورودي دارد. ورودي اول مربوط به داده‌ي مورد انديشه متخصصين است. در اين مثال ما به دنبال عدد ۱ كه رتبه‌ي دانشگاه است، هستيم. ورودي دوم مربوط به محدوده‌ي داده‌ها است كه در اين مثال كل رديف A تا F انتخاب شده است. ورودي سوم مربوط به شماره ستون داده‌ي مورد انديشه متخصصين است. چون ما به دنبال اسم دانشگاه هستيم ورودي را ستون ۲ در انديشه متخصصين مي‌گيريم. ورودي آخر مربوط به وقتي است كه داده‌ي مورد انديشه متخصصين (مثلا عدد ۱) پيدا نشود كه به جاي آن عدد ۰ يا عبارت False را قرار مي‌دهيم.

در اينجا دو ستون داريم كه يكي شامل نام و دومي شامل نام خانوادگي است. با استفاده از علامت & مي‌توان ستوني ايجاد كرد كه نام و نام خانوادگي را در كنار هم داشته باشد. در نرم‌افزار اكسل با كمك & مي‌توانيد دو قسمت از متن را با هم تركيب كنيد. البته دقت كنيد كه بين نام و نام خانوادگي حتما فاصله قرار دهيد. روش كار به اين صورت است: [انتخاب سلول نام خانوادگي]& " " & [ّانتخاب سلول نام].

اين فرمول‌هاي متني مخصوص پاك كردن داده‌ها است. براي مثال در حالت زير اسم چند ايالت آمريكا را داريم كه حروف اختصاري هر يك با يك خط فاصله در كنار اسامي قرار گرفته است. با استفاده از تابع LEFT مي‌توانيم فقط دو حرف اختصاري هر نام را داشته باشيم. LEFT بر اساس تعداد كاراكترهاي در انديشه متخصصين گرفته يك رشته‌ي متني را به صورت خروجي تحويل مي‌دهد. اولين ورودي اين تابع سلول مربوط به رشته‌ي متني و دومي تعداد حروف مورد انديشه متخصصين است.

چنانچه بخواهيد نام ايالت را به صورت كامل داشته باشيد و خبري از حروف اختصاري نباشد از تابع RIGHT استفاده كنيد. RIGHT تعداد كاراكترها را از سمت راست رشته‌ي متني جدا مي‌كند.

اما سوال اين است كه چه تعداد كاراكتر را بايد از سمت راست انتخاب كنيد؟ چرا كه تعداد حروف ايالت‌ها با هم برابر نيستند. در اينجا استفاده از تابع LEN كارگشا خواهد بود.

تابع LEN كل كاراكترهاي يك رشته‌ي متني را شمارش مي‌كند و تنها يك ورودي دارد كه آن هم سلول مربوط به رشته‌ي متني است.

حالا با كمك تعداد كاراكترهاي به دست آمده از تابع LEN مي‌توانيد از تابع RIGHT استفاده كنيد.

از آن جا كه ما مي‌خواهيم حروف اختصاري و خط فاصله را حذف كنيم، وردي تابع RIGHT را عدد تابع LEN منهاي ۳ قرار مي‌دهيم.

تابع ()RAND مي‌تواند اعداد تصادفي بين صفر تا ۱ ايجاد كند. اين تابع هيچ گونه ورودي لازم ندارد و داخل پرانتز خالي است. با هر بار فشردن كليد F9 اين تابع عدد جديدي توليد مي‌كند. دقت داشته باشيد كه با ايجاد هر گونه تغيير در فضاي كار، يك عدد جديد ايجاد خواهد شد.