جمع کردن مقادیر دامنه در اکسل

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

کار با فرمول های آرایه ای تک سلولی

همان طور که در فصل پیش گفته شد فرمول های آرایه ای تک سلولی در یک سلول (و نه در یک دامنه) درج میشوند و همچنین فرمول های آرایه ای با آرایه های موجود در یک دامنه یا آرایه های موجود در حافظه ی اکسل کار میکنند. در این بخش، چندین مثال دیگر از چنین فرمول های آرایه ای ارایه خواهد شد.

جمع کردن مقادیر دامنه ای که حاوی مقادیر خطاست

ممکن است متوجه شده باشید که تابع SUM در صورتی که بخواهید مقادیر دامنه ای که حاوی یک یا چند مقدار خطاست( DIV/0 # یاN/A   #)  را باهم جمع کنید، از این کار جلوگیری میکند. تصویر زیر، مثالی از این قضیه را نشان می دهد. فرمول سلولی D11 چون دامنه ای که قرار است عملیات جمع را روی آن اجرا کند ( دامنه  (D4:D10شامل مقادیر خطاست، یک مقدار خطا را نمایش میدهد.

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

{=SUM(IFERROR(D4:D10," "))}

این فرمول با ایجاد یک آرایه ی جدید که شامل مقادیر اصلی منهای مقادیر خطاست عمل می کند در واقع تابع IFبه صورت موثر با جایگزین کردن رشته های تهی به جای مقادیر خطا ، آنها را فیلتر کرده و سپس تابع  SUM روی این آرایه فیلتر شده کار میکند. این تکنیک روی توابع دیگر مانند   MIN,AVERAGE یا MAXنیز پاسخ می دهد.

جمع کردن مقادیر دامنه در اکسل

البته تابع  AGGREGATEکه در اکسل ۲۰۱۰ معرفی شد، روش دیگری برای جمع کردن مقادیر دامنه ای بوده که یک یا چند مقدار خطا در آن درج شده است. به مثال زیر دقت کنید:

=AGGREGATE(9,2,C4:C10)

آرگومان نخست در تابع بالا (9)کد مربوط به تابع SUMاست. آرگومان دوم (2) , کد مربوط به تابع " نادیده گرفتن مقادیر خطا" است. تابع  AGGREGATE میتواند برای محاسبه میانگین، مقدار حداقل، حداکثر و ... را به کار ببرد.

شمارش تعداد مقادیر خطای موجود در یک دامنه

فرمول آرایه ای زیر مشابه مثال قبلی است، با این تفاوت که تعداد مقادیر خطا دردامنه ای به نامData را میشمارد:

=SUM (IF(ISERROR (Data),1,0))}}

این فرمول آرایه ای میسازد که شامل مقادیر1 ( درصورتی که سلول مربوطه حاوی مقدار خطا باشد) و صفر (در صورتی که سلول مربوطه حاوی مقدار خطا نباشد) است.

البته می توانید با حذف آرگومان سوم تابع IFفرمول را به شکل ساده تری در بیاورید.اگر این آرگومان مشخص نشده باشد، تابع IFدرصورتی که شرط تحقق نیافته باشد ( یعنی سلول حاوی مقدار خطا نباشد) مقدار FALSE را بر می گرداند و اکسل، FALSEرا به عنوان مقدار صفر را  نظر میگیرد. فرمول آرایه ای زیر، مانند فرمول قبلی عمل می کند با این تفاوت که در تابع IFاز آرگومان سوم استفاده نمیکند:

{=SUM(IF(ISERROR(Data),1))}

در واقع میتوانید فرمول بالا را حتی به شکل ساده تر بنویسید:

{=SUM(ISERROR(Data)*1)}

همچنین در فرمول بالا فرض شده که دو تساوی زیر همیشه برقرار هستند:

TRUE * 1 = 1

FAlSE * 1 = 0

جمع کردن nتعداد از بزرگترین مقادیر موجود در یک دامنه

فرمول آرایه ای زیر، حاصل جمع بزرگترین 10مقدار موجود در دامنه ای به نام Dataرا بر می گرداند:

{=SUM(LARGE(Data ,ROW(INDIRECT("1:10"))))}

تابع LARGEدر فرمول بالا، ده مرتبه مورد ارزیابی قرار گرفته و هر بار آرگومان دوم آن تغییر یافته است(3،2،1 و ... تا 10). نتیجه این محاسبات در یک آرایه ی جدید ذخیره می شود و به عنوان آرگومان تابعSUM مورد استفاده قرار میگیرد. برای جمع کردن تعداد متفاوت مقادیر هم میتوانید به جای عدد 10 در آرگومان تابع INDIRECTهر عدد دلخواهی را قرار دهید.

 اگر تعداد سلول هایی که قرار است باهم جمع شوند در سلول C17 مشخص شده باشد ، میتوانید از فرمول آرایه ای زیر که عملگر "&"را برای ایجاد نشانی دامنه در تابع  INDIRECT به کار می گیرد ، استفاده کنید:

{=SUM(LARGE(Data:ROW("1:"&C17))))}

برای جمع کردن nتعداد از کوچکترین مقادیر موجود در یک دامنه هم باید به جای تابع LARGE از تابع SMALLاستفاده کنید.

محاسبه مقدار میانگین بدون در نظر گرفتن صفر ها

تصویر زیر کاربرگ ساده ای را نشان می دهد که میانگین مقادیر فروش را حساب می کند. فرمول سلول B13به شرح زیر است:

=AVERAGE ( B4 : B11)

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

جمع کردن مقادیر دامنه در اکسل

هرچند تابع AVERAGE سلول های خالی یا تهی را در نظر نمی گیرد، اما نمی توان سلول هایی که حاوی مقدار صفر هستند را نیز نادیده بگیرد. بنابراین با کمک فرمول آرایه ای زیر ، مقدار میانگین دامنه بدون احتساب مقادیر صفر ، محاسبه می شود:

{=AVERAGE(IF(B5: B12<>0,B5:B12))}

این فرمول یک آرایه ی جدید می سازد که تنها شامل مقادیر غیر صفر موجود در دامنه است، سپس تابع AVERAGEاز این آرایه ی جدید به عنوان آرگومان استفاده می کند. همچنین می توانید با فرمول معمولی و غیر آرایه ای زیر هم به نتیجه ی مشابه دست پیدا کنید:

=SUM(B5:B12)/COUNTIF(B5:B12, " <>0 ")

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

 

تعیین اینکه آیا مقدار مشخص در یک دامنه وجود دارد یا خیر

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

تصویر زیر ، کاربرگی را نشان می دهد که در دامنه ی  A5:E24 که NameList نامیده می شود، فهرستی از اسامی درج شده است.هر فرمول آرایه ای در سلول D3 نام درج شده در سلول C3 ( که TheName نامیده می شود) را کنترل می کند. اگر نام مذکور در فهرست نام ها وجود داشته باشد عبارت Found ( پیدا شد ) و در غیر این صورت عبارت NotFound ( پیدا نشد ) را نمایش می دهد.

جمع کردن مقادیر دامنه در اکسل

فرمول آرایه ای در سلول D3 به صورت زیر است:

{=IF(OR(TheName=NameList), " Found" , "NotFound")}

این فرمول به مقایسه ی TheName با مقدار تمام سلول های دامنه ی NameList  پرداخته و سپس آرایه ای جدید متشکل از مقادیر TRUE و FALSE می سازد. در صورتی که یکی از مقادیر موجود در آرایه‌ی جدید ، TRUE باشد تابع OR هم مقدار TRUE را بر می گرداند و تابع IF از این نتیجه برای اینکه مشخص کند کدام پیغام را نمایش دهد استفاده می کند.

شکل ساده تر این فرمول به شرح زیر است. این فرمول در صورتی که نام پیدا شود عبارت TRUE ودر غیر این صورت عبارت FALSE را بر می گرداند :

{=OR(TheName=NameList)}

شیوه ی دیگر انجام این عملیات ، استفاده از تابع COUNTIF در یک فرمول غیر آرایه ای است :

IF(COUNTIF(NameList,TheName)>0, " Found" , "NotFound)")

 

شمارش تعداد مقادیر متفاوت در دو دامنه

فرمول آرایه ای زیر ، مقادیر متناظر در دو  دامنه به نام های MyData و YourData را با یکدیگر مقایسه می کند و تعداد مقادیر متفاوت در دو دامنه را به عنوان جواب نمایش می دهد. اگر محتویات دو دامنه با یکدیگر یکسان باشد، عدد صفر را به عنوان جواب بر می گرداند:

{=SUM(IF(MyData=YourData,0,1))}

تصویر زیر، مثالی از این عملیات است.

جمع کردن مقادیر دامنه در اکسل

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

فرمول آرایه ای زیر که ساده تر از نمونه قبلی می باشد هم روشی دیگر برای محاسبه قبل است:

{=SUM(1*(MyData<>YourData))}

این فرمول جدید بر این حقیقت که تساوی های زیر همیشه برقرار هستند، تکیه می کند:

TRUE * 1= 1

و

 FALSE*1=0

 

مشخص کردن محل مقدار بیشینه در یک دامنه

فرمول آرایه ای زیر، شماره ردیف مقدار بیشینه در یک دامنه ی تک ستونی به نام Data را بر می گرداند:

{=MIN(IF(Data=MAX(Data),ROW(Data)," " ))}

تابع IF ، آرایه ی جدیدی را ایجاد می کند که متناظر با دامنه ی Data است. اگر سلول متناظر حاوی مقدار بیشینه در دامنه Data باشد، شماره ردیف مذکور به عنوان ورودی آرایه در نظر گرفته شده و در غیر اینصورت، یک رشته ی تهی در آرایه درج می شود. سپس تابع MIN از این آرایه ی جدید به عنوان آرگومان دوم خود استفاده می کند و کوچک ترین مقدار که متناظر با شماره ی ردیف مقدار بیشینه در دامنه Data است را به عنوان پاسخ بر می گرداند. با استفاده از تابع MIN ، اگر در دامنه Data، مقدار بیشینه در بیش از یک سلول قرار گرفته باشد، شماره‌ی ردیف نخستین مورد مقدار بیشینه به عنوان پاسخ بر می گردد.

فرمول آرایه ای زیر شبیه فرمول پیش است، با این تفاوت که نشانی واقعی سلولی که مقدار بیشینه در آن قرار دارد را برمی گرداند.در این فرمول از تابع ADDRESS استفاده شده که دو آرگومان (شماره ی ردیف و شماره ی ستون) دارد:

{=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data), " " }) , COIUMN(Data)}}

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

{=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data), " " }) , MIN(IF(Data=MAX(Data),COLUMN(Data) , " ")))}

پیدا کردن شماره ی ردیف مربوط به n امین تکرار از یک مقدار خاص در یک دامنه

فرمول آرایه ای زیر، شماره ی ردیفی در یک دامنه ی تک ستونی به نام Data که حاوی n امین تکرار از مقدار موجود در سلولی به نام Value است را بر می گرداند:

{=SMALL(IF(Data=Value,ROW(Data), " "),n)}

تابع IF ، آرایه ی جدید می سازد که شامل شماره ی ردیف مقادیر دامنه ی Data که مساوی با Value هستند، است. مقادیر دامنه ی Data که مساوی با Value نباشند، جای خود را به یک رشته ی خالی می دهند. تابع SMALL روی این آرایه ی جدید کار می کند و n امین شماره ی ردیفی که مقدار آن کوچک تر از دیگران باشد را به عنوان پاسخ بر می گرداند. اگر Value وجود نداشته باشدیا اگر n بیش از تعداد مقادیر موجود در دامنه باشد، فرمول ، عبارت خطای ! #NUM را به عنوان پاسخ بر می گرداند.

یافتن طولانی ترین عبارت متنی در یک دامنه

فرمول آرایه ای زیر، رشته ی متنی ای در دامنه ی Data که بیشترین تعداد کاراکتر به نسبت دیگر عبارات دارد را نمایش می دهد و اگر چند سلول شامل طولانی ترین رشته ی متنی باشند، نخستین سلول به عنوان پاسخ تعیین می شود :

{=INDEX(Data,MATCH(MAX(LEN(Data)),LEN(Data),FALSE),1)}

جمع کردن مقادیر دامنه در اکسل

این فرمول با دو آرایه کار می کند که هر دوی آن ها حاوی طول یکی از دامنه های موجود در دامنه ی Data هستند، تابع MAX ، بزرگترین مقدار که متناظر با طولانی ترین عبارت متنی در دامنه است را پیدا می‌کن. تابع MATCH ، محل سلولی که شامل طول حداکثری است را مشخص می کند. تابع INDEX ، محتویات سلولی که حاوی بیشترین تعداد کاراکترهاست را بر می گرداند‌. همچنین این تابع تنها اگر دامنه ی Data تک ستونی باشد پاسخ می دهد.

تعیین معتبر بودن مقادیر موجود در یک دامنه

ممکن است فهرستی از مقادیری داشته باشید که باید با فهرست دیگری مطابقت داده شوند. برای مثال ممکن است فهرست شماره ی قطعات را در دامنه ای به نام MyList درج کنید و بخواهید مطمئن شوید که تمام شماره ی قطعات صحیح و معتبر هستند. می توانید این کار را با مقایسه ی موارد موجود در فهرست MyList با موارد موجود در فهرست اصلی شماره ی قطعات  فهرست  (Master) انجام دهید. تصویر زیر این مثال را نشان می دهد.

جمع کردن مقادیر دامنه در اکسل

 

فرمول آرایه ای زیر، درصورتی که همه ی گزینه های موجود در دامنه ی MyList در دامنه ی Master هم وجود داشته باشند، عبارت TRUE را به عنوان پاسخ بر می گرداند. هر دو دامنه باید شامل یک ستون باشند، اما می توانند تعداد ردیف های متفاوتی داشته باشند:

{=ISNA(MATCH(TRUE,ISNA(MATCH(MyList,Master,0)),0)))}

فرمول آرایه ای زیر، تعداد موارد غیر معتبر موجود در MyList را مشخص می کند. به عبارت دیگر، این فرمول مواردی درMyList که در Master وجود ندارند را فهرست می کند:

{=SUM(1*ISNA(MATCH(MyList,Master,0)))}

برای اینکه نخستین گزینه نا معتبر در MyList مشخص شود، باید از فرمول آرایه ای زیر استفاده کنید:

{=INDEX(MyList,MSTCH(TRUE,ISNA(MATCH(MyList,Master,0)),0))}

جمع کردن ارقام یک عدد صحیح با یکدیگر

من تاکنون برنامه ای ندیده ام که بتواند این کار را انجام دهد، اما فرمول آرایه ای به خوبی می تواند از پس انجام چنین کاری بر آید. فرمول آرایه ای زیر، حاصل جمع ارقام یک عدد صحیح مثبت که در سلول A1 ذخیره شده را محاسبه می کند. برای مثال اگر سلول A1 حاوی عدد 409 باشد، نتیجه ی فرمول عدد13 خواهد بود که در واقع برابر با حاصل جمع ارقام 4,0 و9 است:

{=SUM(MID(A1,ROW(INDIRECT( "1:"&LEN(A1))),1)*1))}

برای درک چگونگی عملکرد این فرمول، بهتر است اول به سراغ تابع ROW بروید:

{=ROW(INDIRECT( "1:*&LEN(A1)))}

این تابع، آرایه ای متشکل از اعداد صحیح  متوالی را به عنوان پاسخ بر می گرداند. که با عدد 1 آغاز و با تعداد ارقام عددی که در سلول A1 درج شده پایان می یابد. برای مثال اگر سلول A1 حاوی عدد409 باشد، تابعLEN ، عدد 3 که تعداد رقم های این عدد است (4,0,9) را به عنوان پاسخ بر می گرداند و تابع ROW هم آرایه ی زیر را می سازد:

{1,2,3}

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

{=MID(409,{1,2,3},1)*1}

این تابع، آرایه ای با سه عنصر ایجاد می کند:

{4,0,9}

با ساده کردن بیشتر فرمول و اضافه کردن تابع SUM، فرمول قبلی به صورت زیر در می آید:

{=SUM({4,0,9})}

نتیجه ی این فرمول نیز عدد 13 خواهد بود که حاصل جمع ارقام 409 است.

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

{=SUM(IFERROR(MID(A1,ROW(INDIRECT(" 1:"&LEN(A1))),1)*1,0))}

تصویر زیر، کاربرگی را نشان می دهد که از هر دو نسخه ی این فرمول استفاده می کند.

جمع کردن مقادیر دامنه در اکسل

جمع کردن مقادیر گرد شده با یکدیگر

تصویر زیر، کاربرگ ساده ای است که یکی از مشکلات متداول کاربرگ ها یعنی خطاهای مربوط به عملیات گرد کردن را نشان می دهد. همان طور که مشاهده می کنید، به نظر می رسد مقدار مجموع کلی که در سلول E7 نمایش داده شده عدد صحیحی نیست. مقادیر موجود در ستون E از یک قالب بندی عددی که دو رقم اعشار دارد استفاده می کنند. البته مقادیر واقعی دارای رقم های اعشاری بیشتری بوده اند که در نتیجه ی به کار گیری قالب بندی عددی مذکور و عملیات گرد کردن، نمایش داده نمی شوند. تاثیر اصلی خطاهای ناشی از گرد کردن بر حاصل جمع کلی است. در این کاربرگ، حاصل جمع که در واقع168.320997$ بود، به صورت 168.32$ نمایش داده می شود.

فرمول آرایه ای زیر، آرایه ی جدیدی ایجاد می کند که شامل مقادیر موجود در ستون E است که تا دو رقم اعشار گرد شده اند:

{=SUM(ROUND(E4:E6,2))}

پاسخ این فرمول، 168.31$ است. البته می توانید با استفاده از تابع ROUND که مجموع مقادیر هر ردیف در ستون E را بدون نیاز به فرمول آرایه ای محاسبه می کند، این نوع خطاهای گرد کردن را نیز حذف کنید.

جمع کردن مقادیر دامنه در اکسل

جمع کردن تمام nامین مقدار های موجود در یک دامنه

فرض کنید دامنه ای از مقادیر دارید و مثلا می خواهید حاصل جمع تمام سومین مقدار های موجود در فهرست را محاسبه کنید. بهترین راه حل برای انجام این کار، استفاده از فرمول آرایه ای است. در تصویر 9-16 ، مقادیر در دامنه ای به نام Data ذخیره شده اند و مقدر n در سلول D4 ( که سلول n نامیده می شود) قرار دارد.

فرمول آرایه ای زیر، حاصل جمع تمام n امین مقدار های موجود در دامنه را بر می گرداند:

{=SUM(IF(MOD(ROW(INDIRECT("1:"&COUNT(Data)))_1,n)=0,Data,””))}

خروجی این فرمول، عدد 70 و حاصل جمع تمام سومین مقدار های موجود در دامنه است. این فرمول، آرایه ای متشکل از اعداد صحیح متوالی ایجاد و تابع MOD از این آرایه به عنوان آرگومان نخست خود استفاده می کند. آرگومان دوم تابع MOD ، مقدار n است. تابع MOD ، آرایه ی دیگری می سازد که شامل باقیمانده ی تقسیم به شماره ی هر ردیف بر n است. وقتی یکی از عناصر این آرایه، صفر باشد ( یعنی آن ردیف بر N قابل تقسیم باشد) عنصر متناظر در دامنه ی Data به عملیات جمع افزوده می شود.

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

{=IF({n=0,0,SUM(IF(MOD(ROW(INDIRECT("1:"&COUNT(Data)))-1,n)=0,Data””)))}

البته این فرمول تنها زمانی جواب می دهد که دامنه ی Data تنها شامل یک ستون مقادیر باشد. این فرمول برای دامنه های چندستونی یا دامنه هایی که یک ردیف مقدار عددی در آنها وجود دارد پاسخ نمی دهد.

برای آنکه این فرمول با دامنه های افقی هم کار کند، باید آرایه ی اعداد صحیح ساخته شده به وسیله تابع ROW را وارونه کنید. تابع TRANSPOSE ،کلید حل این معماست. فرمول آرایه ای اصلاح شده ی زیر تنها با دامنه ی افقی Data کار می کند:

{=IF(n=0,0,SUM(IF(MOD(TRANSPOSE(ROW(INDIRECT("1:"&COUNT(Data))))- 1,n)=0,Data,””)))}

جمع کردن مقادیر دامنه در اکسل

 

تعیین نزدیک ترین مقدار در یک دامنه

فرمول این بخش، عملیاتی را انجام می دهد که هیچ کدام از توابع جست و جوی اکسل قادر به انجام آن نیستند. فرمول آرایه ای زیر، مقداری را در دامنه ی Data بر می گرداند که نزدیک ترین مقدار به مقدار درج شده در سلولی به نام Target است:

{=INDEX(Data,MATCH(SMALL(ABS(Target-Data),1),ABS(Target-Data),0))}

اگر در دامنه ی Data، دو مقدار وجود داشته باشد که از مقدار Target به یک فاصله قرار داشته باشند، اولین مقدار در فهرست به عنوان خروجی فرمول نمایش داده خواهد شد. تصویر زیر ، مثالی از این فرمول را نمایش می دهد. در این مورد ، مقدار Target برابر 45 است. فرمول آرایه ای  در سلول D4 هم عدد 48 را به عنوان پاسخ بر می گرداند.

جمع کردن مقادیر دامنه در اکسل

حذف کاراکتر های غیر عددی از یک رشته

فرمول آرایه ای زیر، عددی را از رشته ای که حاوی عبارت متنی است، استخراج می کند. برای مثال رشته ی ABC145Z را در نظر بگیرید. خروجی فرمول زیر برای این رشته، عدد 145 که بخش عددی رشته مذکور است خواهد بود.

{=MID(A1,MATCH(0,(ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)*1),0),LEN(A1)-SUM((ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)*1)))}

این رشته اگر تنها یک بخش عددی در آن وجود داشته باشد جواب می دهد. برای مثال، این فرمول نمی تواند بخش عددی رشته ای مانند X45Z99 را استخراج کند چون در این رشته، دو بخش عددی وجود دارد.

برگرداندن آخرین مقدار در یک ستون

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

=OFFSET(A1,COUNTA(A:A)-1,0)

در این فرمول از تابع COUNTA برای شمارش تعداد سلول های غیر تهی در ستون A استفاده و عدد 1- به عنوان آرگومان دوم برای تابع OFFSET درج شده است. برای مثال اگر آخرین مقدار در ردیف 100 قرار داشته باشد، تابع COUNTA ، عدد 100 را به عنوان نتیجه بر می گرداند و تابع OFFSET ، مقدار موجود در سلولی که 99 ردیف پایین تر از سلول A1 و در همان ستون قرار دارد را به عنوان پاسخ بر می گرداند.

البته اگر ستون A دارای یک یا چند سلول خالی باشد که در سراسر ستون پخش شده باشند، فرمول قبلی نمی تواند پاسخ صحیح را برگرداند. هرچند تابع COUNTA نمی تواند سلول های خالی را شمارش کند، اما فرمول آرایه ای زیر می تواند محتویات آخرین سلول غیر تهی در 500 ردیف اول ستون A را برگرداند:

{=INDEX(A1:A500,MAX(ROW(A1:A500)*(A1:A500<>””)))}

برگرداندن آخرین مقدار یک ردیف

فرمول آرایه ای زیر مشابه فرمول قبلی است، با این تفاوت که این فرمول، آخرین سلول غیر تهی در یک ردیف ( در این مثال ردیف 1 ) را بر میگرداند:

{=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>””)))}

اگر بخواهید این فرمول برای ردیف دیگری به کار گرفته شود می توانید ارجاع 1:1 را به ردیف مورد نظرتان تغییر دهید. تصویر زیر، مثالی برای آخرین مقدار در یک ستون و آخرین مقدار در یک ردیف را نشان می دهد.

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

=LOOKUP(2,1/(1:1<>" "),1:1)

جمع کردن مقادیر دامنه در اکسل

 

 


نویسنده : --

تگ ها :
تاپ سایت 98
عضویت طلایی
محصولات آموزشی
ثبت نظر