اكسل

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

ابتدا فایل اکسل را دانلود کنید و توضیحات مربوطه را مرحله به مرحله در فایل اکسل مشاهده کنید.

فرض میکنیم در محدوده D5:D28 داده هایی شامل عدد و متن داریم و قصد داریم فقط جمع اعداد را بدست بیاوریم. در سلول G4 فرمول آرایه ای {=SUM(IF(ISNUMBER(D5:D28),D5:D28))} را می نویسم ( پس از نوشتن فرمول کلیدهای ترکیبی Ctrl+Shift را نگه داشته و سپس کلید Enter را میزنیم تا اکسل فرمول را بصورت آرایه ای درآورد). مشاهده می کنیم که تابع If فرمول ابتدا درست یا نادرست بودن تک تک سلول های محدوده را بررسی کرده و سلول هایی را که دارای عدد است را تشخیص و توسط تابع Sum ، اعداد ارائه شده در تابع شرطی را جمع زده است.

لازم به ذکر است این آموزش مربوط به وبلاگ http://proexcelkhani.blogfa.com/ می باشد.

دانلود فایل

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

در مواردی شاید نیاز باشد ورودیهای غیر تکراری یک محدوده را شمارش کنید مثلا لیستی از حیوانات را دارید و هدف شما شمارش حیوانات متفاوت لیست است

{=sum(1/countif(a1:a10;a1:a10))}

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

{=LARGE(Data,ROW(INDIRECT(“۱:”&ROWS(Data(((}

ستونی که اطلاعات ما در آن قرار دارد data نامگذاری کردیم و این فرمول را در ستونی دیگری نوشتیم

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

کار تابع  فوق این است که اگر داخل ان یا سلولی   متنی را به صورت ادرس  بنویسیم خروجی این تابع ان متن را به ادرس تبدیل مینماید

فقط در استفاده از این تابع باید مدنظر داشته باشیم به تعداد داده های نامگذاری شده فرمول را به صورت آرایه ای بنویسید درغیر اینصورت در سلولهای که برای ان داده های وجود ندارد به خطا برخواهیم خورد