سایت تخصصی حسابداران خبره ایران

ارائه مطالب تخصصی حسابداری و حسابرسی و قوانین

سایت تخصصی حسابداران خبره ایران

ارائه مطالب تخصصی حسابداری و حسابرسی و قوانین

آموزش اکسل - ایجاد کشو

آموزش اکسل - ایجاد لیست کشویی مرتب شده به ترتیب حروف الفبا و بدون در نظر گرفتن موارد تکراری

create-a-drop-down-list-containing-only-unique2

ایجاد لیست کشویی مرتب شده به ترتیب حروف الفبا و بدون در نظر گرفتن موارد تکراری

سوال:

چگونه یک لیست کشویی با داده های واحد(بدون در نظر گرفتن موارد تکراری لیست) و به ترتیب حروف الفبا درست کنیم؟

 

create-a-drop-down-list-containing-only-unique

پاسخ:

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

create-a-drop-down-list-containing-only-unique2

فرمولی که باید در سلول B2 بنویسیم:

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF($B$1:B1, List))*(COUNTIF(List, “>”&List)+1))=(COUNTIF(List, “>”&List)+1), 0, 1), 0))

چگونه یک فرمول آرایه ای بنویسیم:

  1. انتخاب سلول B2
  2. نوشتن فرمول آرایه ای فوق
  3. نگه داشتن همزمان کلیدهای Ctrl + Shift
  4. زدن کلید Enter
  5. رها کردن همه کلیدها

چگونه یک فرمول آرایه ای را کپی کنیم:

  1. انتخاب سلول B2
  2. کپی کردن (Ctrl + c)
  3. انتخاب کل محدوده ای که میخواهیم تا آنجا فرمول بسط داده شود- B3:B6
  4. جای گذاری کردن(Ctrl + v)

توضیح فرمول آرایه ای نوشته شده در سلول B2

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF($B$1:B1, List))*(COUNTIF(List, “>”&List)+1))=(COUNTIF(List, “>”&List)+1), 0, 1), 0))

مرحله ۱ – تبدیل متن به عدد

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF($B$1:B1, List))*(COUNTIF(List, “>”&List)+1))=(COUNTIF(List, “>”&List)+1), 0, 1), 0))

COUNTIF(range,criteria)

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

COUNTIF(List, “>”&List)+1

می شود

COUNTIF({“DD”;”EE”;”FF”;”EE”;”GG”;”BB”;”FF”;”GG”;”DD”;”TT”;”FF”;”VV”;”VV”;”FF”}, “>”&{“DD”;”EE”;”FF”;”EE”;”GG”;”BB”;”FF”;”GG”;”DD”;”TT”;”FF”;”VV”;”VV”;”FF”})+1

می شود

{۱۱;۹;۵;۹;۳;۱۳;۵;۳;۱۱;۲;۵;۰;۰;۵}+۱

می شود

{۱۲;۱۰;۶;۱۰;۴;۱۴;۶;۴;۱۲;۳;۶;۱;۱;۶}

مرحله ۲ – شناسایی متن غیرتکراری در سلول قبلیِ سلول جاری

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF($B$1:B1, List))*(COUNTIF(List, “>”&List)+1))=(COUNTIF(List, “>”&List)+1), 0, 1), 0))

COUNTIF(range,criteria)

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

NOT(COUNTIF($B$1:B1, List))

می شود

NOT(COUNTIF(“Unique list sorted alpabetically”, {“DD”;”EE”;”FF”;”EE”;”GG”;”BB”;”FF”;”GG”;”DD”;”TT”;”FF”;”VV”;”VV”;”FF”}))

می شود

NOT({0;0;0;0;0;0;0;0;0;0;0;0;0;0})

می شود

{۱;۱;۱;۱;۱;۱;۱;۱;۱;۱;۱;۱;۱;۱}

مرحله ۳ – محاسبه بزرگترین عدد موجود در محدوده

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF($B$1:B1, List))*(COUNTIF(List, “>”&List)+1))=(COUNTIF(List, “>”&List)+1), 0, 1), 0))

MAX(NOT(COUNTIF($B$1:B1, List))*(COUNTIF(List, “>”&List)+1))

می شود

MAX({1;1;1;1;1;1;1;1;1;1;1;1;1;1}*({12;10;6;10;4;14;6;4;12;3;6;1;1;6})

و نتیجه آن ۱۴ خواهد شد.

مرحله ۴ – تبدیل بزرگترین عدد به یک مقدار منطقی

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF($B$1:B1, List))*(COUNTIF(List, “>”&List)+1))=(COUNTIF(List, “>”&List)+1), 0, 1), 0))

IF(MAX(NOT(COUNTIF($B$1:B1, List))*(COUNTIF(List, “>”&List)+1))=(COUNTIF(List, “>”&List)+1), 0, 1)

می شود

IF(14={12;10;6;10;4;14;6;4;12;3;6;1;1;6}, 0, 1)

و نتیجه محدوده زیر خواهد شد:

{۱;۱;۱;۱;۱;۰;۱;۱;۱;۱;۱;۱;۱;۱}

مرحله ۴ – پیدا کردن موقعیت نسبی یک آیتم در یک محدوده

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF($B$1:B1, List))*(COUNTIF(List, “>”&List)+1))=(COUNTIF(List, “>”&List)+1), 0, 1), 0))

MATCH(lookup_value;lookup_array; [match_type])

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

MATCH(0, IF(MAX(NOT(COUNTIF($B$1:B1, List))*(COUNTIF(List, “>”&List)+1))=(COUNTIF(List, “>”&List)+1), 0, 1), 0)

می شود

MATCH(0, {۱;۱;۱;۱;۱;۰;۱;۱;۱;۱;۱;۱;۱;۱}, ۰)

و نتیجه ۶ خواهد شد.

مرحله ۵ – پیدا کردن مقدار یک سلول در تقاطع یک ردیف و ستون خاص

INDEX(array,row_num,[column_num])

یک مقدار را در تقاطع سطر و ستون مشخص شده برمیگرداند

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF($B$1:B1, List))*(COUNTIF(List, “>”&List)+1))=(COUNTIF(List, “>”&List)+1), 0, 1), 0))

می شود

=INDEX(List, ۶)

می شود

=INDEX({“DD”;”EE”;”FF”;”EE”;”GG”;”BB”;”FF”;”GG”;”DD”;”TT”;”FF”;”VV”;”VV”;”FF”}, ۶)

و نتیجه عبارت BB می شود.

ایجاد یک نام داینامیک (متغیر) برای محدوده داده ها

  1. روی تب “Formulas” کلیک کنید
  2. روی تب “Name Manager” کلیک کنید
  3. روی List کلیک کنید
  4. فرمول زیر را در قسمت Refers to تایپ کنید

=OFFSET(Sheet1!$A$2, 0, 0, COUNT(IF(Sheet1!$A$2:$A$1000=””, “”, 1)), 1)

  1. روی کلید”Close” کلیک کنید

محدوده نامگذاری شده

List (dynamic)
What is named ranges?

create-a-drop-down-list-containing-only-unique2

چگونه یک لیست کشویی که قابلیت آپدیت بر اساس داده های یک ستون را دارد ایجاد کنیم.

  1. روی تب Data کلیک کنید
  2. روی کلید Data validation کلیک کنید
  3. روی گزینه “Data validation…” کلیک کنید
  4. در قسمت Allow گزینه List را مانند تصویر زیر انتخاب کنید.
  5. فرمول زیر را در قسمت Source تایپ کنید.

=OFFSET($B$2, 0, 0, COUNT(IF($B$2:$B$1000=””, “”, 1)), 1)

  1. روی گزینه OK کلیک کنید!

create-a-drop-down-list-containing-only-unique3

فایل نمونه را از آدرس زیر دانلود کنید

Create-a-drop-down-list-containing-only-unique.xls
(Excel 97-2003 Workbook *.xls)


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

IF(logical_test;[value_if:true];[value_if_false])

INDEX(array,row_num,[column_num])

SMALL(array,k)

ROW(reference)

MATCH(lookup_value;lookup_array; [match_type])

COUNTIF(range,criteria)

COUNT(value1;[value2])
OFFSET(reference,rows,cols, [height],[