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

 

مثال بالا نشان می دهد که محدوده سلولی B3: B10 حاوی 8 سطر است.

ترکیب تابع

 ROWS(array)

پارامتر های تابع

array: اجباری - محدوده سلولی که می خواهید تعداد سطرهای آن را محاسبه کنید.

 نکته

تابع ROWS تعداد سطر های یک آرایه را نیز محاسبه می کند.فرمول:

=ROWS ({1,2,3;4,5,6;2,3,4})

مقدار 3 را باز می گرداند. آرایه سه سطر دارد. نماد ; (سیمی کالون) نشان دهنده مرز سطر در آرایه است.

توجه

ممکن است نماد های جداکننده سیستم شما با توجه به تنظیمات regional settings متفاوت باشد.

تابع ROW شماره سطر آدرس مشخص شده را باز می گرداند.

ترکیب تابع

ROW(reference)

پارامتر های تابع

reference: اختیاری - آدرس سلولی که می خواهید شماره سطر آن را بدانید.

نکته

می توانید آدرس یک سلول تک یا محدوده سلولی را وارد کنید. اگر  یک محدوده سلولی را به عنوان پارامتر وارد کنید , به یاد داشته باشید فرمول را به صورت آرایه وارد کنید.

پارامتر reference اختیاری است. اگر تابع را بصورت ()ROW= در یک سلول وارد و اینتر کنید آدرس خود سلول استفاده می شود. به عنوان مثال ، ()ROW= درسلول F4 مقدار 4 را باز می گرداند.

توجه

ممکن است نماد های جداکننده سیستم شما با توجه به تنظیمات regional settings متفاوت باشد.

این تابع از آدرس مشخص شده (reference) به اندازه سطر  و ستون مورد نظر پیمایش کرده و سپس مقدار سلولی که پس از پیمایش به آن می رسد را باز می‌گرداند.

اگر محدوده مورد نظر reference فقط یک سلول باشد نیاز به قید width و height نیست چرا که مقدار پیش فرض آنها 1 است ولی اگر بخواهید بیش از یک مقدار باز‌گردانده شود ، باید این دو پارامتر را به اندازه مورد نظر تنظیم کنید.

ترکیب تابع

 OFFSET(reference ,rows ,columns ,[height ],[width])

پارامتر های تابع

reference: اجباری - مرجع سلولی که از آن می خواهید شروع کنید .

rows: اجباری - تعداد ردیف هایی که می خواهید از سلول مرجع شروع شود. این پارامتر می تواند مثبت یا منفی باشد . مثلا عدد ٣ معرف این است که سلول مورد نظر در سطر سوم قرار دارد. در این تابع سطر‌ها از شماره صفر شروع می شوند پس سطر سوم در واقع سطر چهارم خواهد‌بود.

columns: اجباری - تعداد ستون هایی که می خواهید از سلول مرجع شروع شود. این پارامتر هم می تواند یک عدد مثبت یا منفی باشد. اینجا نيز ستونها از عدد صفر شروع می‌شوند.

[height ]: اختیاری - تعداد سطرهایی است که در جواب بازگردانده می‌شود. باید یک عدد مثبت باشد.

[width]: اختیاری - تعداد ستونهایی است که در جواب بازگردانده می‌شود. باید یک عدد مثبت باشد.

نکته

اگر سلول مرجع برگشتی خارج از شبکه سلولی باشد تابع OFFSET خطای REF# را باز می گرداند.

من در بیشتر موارد ترجیح می دهم از تابع INDEX استفاده کنم.

 توجه

 ممکن است نماد های جداکننده سیستم شما با توجه به تنظیمات regional settings متفاوت باشد.

موقعیت نسبی یک آیتم را در یک آرایه باز می گرداند و دارای سه پارامتر است .

فرمول سلول D10 مقدار A را در محدوده سلولیC3:C7 جستجو و درسلول C5 آیتم مورد نظر را پیدا می کند. موقعیت نسبی C5 در محدوده سلولی C3:C7 مقدار 3 می باشد که در ستون B نشان داده شده است.

فرمول سلول D10 مقدار 3 را باز می گرداند ، به یاد داشته باشید اگر می خواهید موقعیت دقیق را پیدا کنید ، در پارامتر سوم مقدار 0 (صفر) را وارد کنید.

ترکیب تابع

 MATCH(lookup_value , lookup_array, [match_type] )

 پارامتر های تابع

lookup_value : اجباری - پارامتر اول برای تعیین موردی است که می خواهیم موقعیت آن را در آرایه بدانیم ، این مورد می تواند مقدار ، متن و یا ارزش منطقی باشد.

lookup_array : اجباری - این پارامتر برای تعیین آرایه مورد نظر برای جستجو بکار می رود.

[match_type] : اختیاری - برای تعیین نحوه رتبه بندی بکار می بریم . و مقادیر 1 , 0 و 1- را می توانیم وارد کنیم. در صورت وارد نشدن عددی در این قسمت , مقدار پیش فرض عدد 1 خواهد بود.

1- : موقعیت قرار گیری آیتمی را که بزرگتر یا برابر مقدار lookup_value است پیدا می کند. مقادیر Lookup_array باید به ترتیب نزولی قرار گیرد.

0 : موقعیت قرار گیری دقیق lookup_value را در آرایه باز می گرداند. مقادیر Lookup_array می تواند در هر جهت و بدون ترتیب باشد.

1 : موقعیت قرار گیری آیتمی را که کوچکتر یا برابر با مقدار lookup_value است پیدا می کند. مقادیر Lookup_array باید به ترتیب صعودی قرار گیرد.

نکته

• lookup_array باید یک محدوده سلولی افقی یا عمودی یک بعدی باشد .
• lookup_value می تواند یک آرایه یک یا دو بعدی باشد. (فرمول آرایه ای)
• می توانید از سایر توابع اکسل در پارامتر lookup_value استفاده کنید.
• می توانید از سایر توابع اکسل در پارامتر lookup_array استفاده کنید. تا یک آرایه یک بعدی را باز گرداند.
• من تقریبا در تمام اوقات از match_type = 0 استفاده می کنم.

توجه

 ممکن است نماد های جداکننده سیستم شما با توجه به تنظیمات regional settings متفاوت باشد.

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

 

ترکیب تابع

LOOKUP(lookup_value,lookup_vector,[result_vector])

پارامتر های تابع

Lookup_value: اجباری - مقداری است که می خواهیم در محدوده مورد نظر جستجو کنیم که می تواند یک عدد ، متن یا یک مقدار منطقی و همچنین آدرس سلولی باشد. این پارامتر همچنین می تواند مقادیر چندگانه را مدیریت کند، اما باید آن را به عنوان یک تابع آرایه ای بکار برید.

lookup_vector: اجباری - محدوده مورد جستجو در این قسمت درج خواهد شد. این پارامتر باید یک محدوده تک ستونی و یا تک سطری باشد.

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

نکته

1. داده های پارامتر lookup_vector باید بصورت صعودی مرتب شده باشند!
2. اگر تابع LOOKUP نتواند مقدار lookup_value را پیدا کند ، تابع بزرگترین مقدار lookup_vector را که کمتر یا برابر مقدار lookup_value است به عنوان نتیجه باز می گرداند.

در استفاده از این تابع احتیاط کنید !

مقادیر پارامتر lookup_vector باید به صورت صعودی مرتب شده باشند، اما این کافی نیست. ممکن است فکر کنید که اگر lookup_value پیدا نشد ، خطا نشان داده می شود اما همیشه اینطور نیست .

توجه

ممکن است نماد های جداکننده سیستم شما با توجه به تنظیمات regional settings متفاوت باشد.

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

 

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

ترکیب تابع

 INDEX(array, [row_num] , [column_num] )

INDEX( reference , [row_num] , [column_num],[area_num] )

پارامتر های تابع

array or cell reference : ضروری - محدوده سلول هایی که می خواهید یک مقدار از آن محدوده بدست آورید .

[row_num]: ضروری - شماره سطر مقدار خاص که می خواهید دریافت کنید . اگر مقدار آن صفر باشد و به صورت یک فرمول آرایه ای وارد کنید ، تابع تمام مقادیر را برمی گرداند .

[column_num]: اختیاری - شماره سطر مقدار خاص که می خواهید دریافت کنید . اگر مقدار آن صفر باشد و به صورت یک فرمول آرایه ای وارد کنید ، تابع تمام مقادیر را برمی گرداند .

[area_num]: اختیاری - شماره محدوده ی انتخاب شده در اولین پارامتر . ( وقتی تابع بصورت سلول مرجع آدرس دهی شود .)

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

این تابع بی ثبات است ، با احتیاط استفاده کنید. بی ثبات بدین معنی است که برگه بطور متناوب محاسبه می شود. اگر تعداد زیادی فرمول حاوی تابع INDIRECT داشته باشید ، ممکن است سرعت محاسبات برگه شما را کم کند.

ترکیب تابع

 INDIRECT(ref_text, [a1] )

پارامتر های تابع

ref_text: اجباری - یک آدرس سلول ، یا ارجاع به یک سلول به صورت رشته متنی یا محدوده دارای نام.

[a1]: اختیاری – TRUE مقدار پیش فرض است و به سبک آدرس دهی A1 ارزیابی می شود. FALSE نشان دهنده سبک آدرس دهیR1C1 است.

توجه

 ممکن است نماد های جداکننده سیستم شما با توجه به تنظیمات regional settings متفاوت باشد.

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

ترکیب تابع

 HYPERLINK(link_location, [friendly_name])

پارامتر های تابع

Link_location: ضروری - برای درج مسیر سندی است که می خواهیم به آن لینک دهیم. برای درج مسیر , نکات زیر را رعایت کنید:

فایل - مسیر کامل هارد دیسک به فایل , نام فایل + پسوند فایل

فایل بر روی سرور - مسیر کامل هارد دیسک به فایل , نام فایل + پسوند فایل

فایل در اینترنت - آدرس HTTP / HTTPS + نام و پسوندفایل

برگه اکسل - مسیر کامل هارد دیسک به برگه و نام برگه

صفحه وب - آدرس HTTP / HTTPS

friendly_name: اختیاری - برا درج نامی مناسب برای نمایش در سلول استفاده می شود. اگر این پارامتر را خالی بگذاریم مسیر لینک نمایش داده می شود.

توجه

  ممکن است نماد های جداکننده سیستم شما با توجه به تنظیمات regional settings متفاوت باشد.

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

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

نکته

برای جستجوی به صورت عمودی از تابع VLOOKUP استفاده کنید.

 فرمول سلول E2

=HLOOKUP(C2,B5:E14,C3,FALSE)

 تابع HLOOKUP در سلول E2 سطر اول محدوده B5: E14 را برای مقدار " آیتم" جستجو می کند و در ستون دوم پیدا می کند. پارامتر سوم این تابع شماره سطر را می گیرد ، که در این مورد 4 است .تقاطع ستون دوم و سطر چهارم عبارت DD است و نتیجه در سلول E2 بازگردانده می شود.

ترکیب تابع

 HLOOKUP(lookup_value, table_array, Row_index_num,[range_lookup])

 پارامتر های تابع

Lookup_value: اجباری - مقداری است که تابع آن را در سطر اول محدوده داده ها جستجو می کند و میتواند عدد یا متن باشد.

Table_array: اجباری - محدوده داده هایی است که می خواهید مورد جستجو قرار گیرد . اگر این محدوده شامل سر ستون باشد, این سطر نیز شامل محدوده می باشد.

Row_index_num: اجباری - شماره سطری است که می خواهید پس از پیدا کردن ستون مورد نظر , داده واقع در تقاطع این سطر و ستون به عنوان نتیجه باز گردانده شود.

Range_lookup: اختیاری - این پارامتر دو مقدار TRUE و FALSE را می پذیرد که TRUE مقدار پیش فرض است. اگر در این پارامتر TRUE درج کنیم مقادیری که در سطر اول محدوده داده ها درج شده است (اعم از اینکه متن , عدد یا مقادیر منطقی درست و غلط باشند) باید بصورت مرتب شده باشند , در غیر اینصورت تابع ممکن است که جواب صحیح را ارائه ندهد. اگر FALSE را بکار ببریم نیازی به مرتب کردن جدول نیست و جواب دقیق موجود در جدول باز گردانده می شود .

توجه

 ممکن است نماد های جداکننده سیستم شما با توجه به تنظیمات regional settings متفاوت باشد.

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

 

فرمول سلولC3

=FORMULATEXT(B3)

ترکیب تابع

 FORMULATEXT(reference)

 

پارامتر های تابع

reference : ضروری – سلولی که می خواهید فرمول آن را نمایش دهید.

نکته

تابع FORMULATEXT در اکسل 2013 معرفی شد.

به خاطر داشته باشید ، تابع FORMULATEXT اگر سلول خالی باشد و یا حاوی عدد ، متن یا مقادیر منطقی باشد خطای N/A# را نشان می دهد.

ولی اگر نتیجه فرمول استفاده شده مقدار خطا باشد , فرمول را نمایش می دهد .

توجه

ممکن است نماد های جداکننده سیستم شما با توجه به تنظیمات regional settings متفاوت باشد.

تابع COLUMNS تعداد ستون های یک آرایه یا محدوده را باز می گرداند.

مثال بالا نشان می دهد که محدوده سلول C2:J2 دارای 8 ستون است .

ترکیب تابع

 COLUMNS(array)

پارامتر های تابع

array : ضروری – محدوده سلولی که می خواهید تعداد ستون ها ی آن را تعیین کنید.

 نکته

تابع COLUMNS همچنین تعداد ستون ها را در یک آرایه محاسبه می کند.

=COLUMNS({1,2,3;4,5,6;2,3,4})

مقدار 3 را باز می گرداند. آرایه سه ستون دارد. نماد , (کاما) نشان دهنده مرز ستون در آرایه است.

توجه

ممکن است نماد های جداکننده سیستم شما با توجه به تنظیمات regional settings متفاوت باشد.

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

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

ترکیب تابع

 COLUMN(reference )

پارامتر های تابع

reference : اختیاری– آدرس سلولی که می خواهید شماره ستون آن بازگردانده شود.

نکته

تابع COLUMN , اگر فرمول را به صورت فرمول آرایه وارد کنید و از یک محدوده سلولی حاوی چندین ستون استفاده کنید , می تواند آرایه ای از شماره ستون ها را بازگرداند.

نمی توان از چندین آدرس سلول در تابع COLUMN استفاده کرد.

توجه

 ممکن است نماد های جداکننده سیستم شما با توجه به تنظیمات regional settings متفاوت باشد.

تابع CHOOSE مقداری را از یک لیست , با مشخص کردن موقعیت آن باز می گرداند.

فرمول های C3:C4 در تصویر بالا نشان می دهد که از "متن" به جای آدرس سلول استفاده شده است . فرمول سلول C5 نشان می دهد که برای مقدار سطر 8 از آدرس سلول استفاده شده است.

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

ترکیب تابع

CHOOSE(index_num, value1, [value2], ...)

پارامتر های تابع

Index_num: تعیین می کند که کدام مقدار انتخاب شده است. این پارامتر ضروری است ، می توانید یک شماره بین 1 تا 254 استفاده کنید.

value1، value2، value3، ...: تا 254 مقدار با توجه به Index_num می تواند انتخاب کند. مقدار اول ضروری است و مقادیر باقیمانده اختیاری هستند .

توجه

 ممکن است نماد های جداکننده سیستم شما با توجه به تنظیمات regional settings متفاوت باشد.

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

فرمول سلولB3

=AREAS(E3:E4)

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

=AREAS((E3:E5,G3))

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

ترکیب تابع

AREAS(reference)

پارامتر های تابع 

reference: اجباری – برای درج آدرس سلول , سلول ها , محدوده سلولی و یا محدوده های سلولی استفاده می شود.

توجه

 ممکن است نماد های جداکننده سیستم شما با توجه به تنظیمات regional settings متفاوت باشد.

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

در این مرحله شاید واقعا نمی­ خواهید به کلید Esc صفحه کلید ضربه بزنید یا روی X کادر پیام خطا کلیک کنید.

خوشبختانه یک راه حل ساده وجود دارد.

در ابتدای فرمول یک آپستروپ () وارد کنید و Enter را فشار دهید. اکسل فرمول شما را بصورت یک رشته متن ذخیره می­ کند.

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

عیب­ یابی فرمول

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