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

ورود

گذرواژه خود را فراموش کرده اید؟

ثبت نام

  • ۰۹۲۱۷۷۰۹۵۶۹
  • Info@arshad-hesabdar.ir
مطالب کمیاب در اکسل و حسابداری
  • صفحه اصلی
  • دوره های آموزشی اکسل
  • آموزش اکسل
    • آموزش اکسل مقدماتی
    • آموزش اکسل پیشرفته
    • آموزش اکسل در حسابداری
    • آموزش ترفند اکسلی
    • پاسخگویی به سوالات اکسلی
  • فرمول ها و توابع اکسلی
  • آموزش گوگل شیت
  • درباره ما
آخرین اطلاعیه ها
جهت نمایش اطلاعیه باید وارد سایت شوید
شروع کنید
0

وبلاگ

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

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

ارسال شده توسط محمود بنی اسدی (مدیر سایت)
ترفندهای اکسل
لیست-کشویی-مرتب-شده

سوال:

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

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))

مرحله 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)

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

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

می شود

{11;9;5;9;3;13;5;3;11;2;5;0;0;5}+1

می شود

{12;10;6;10;4;14;6;4;12;3;6;1;1;6}

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

=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})

می شود

{1;1;1;1;1;1;1;1;1;1;1;1;1;1}

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

=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})

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

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

=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)

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

{1;1;1;1;1;0;1;1;1;1;1;1;1;1}

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

=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, {1;1;1;1;1;0;1;1;1;1;1;1;1;1}, 0)

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

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

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, 6)

می شود

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

و نتیجه عبارت 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” کلیک کنید

name manager

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

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],[width])

ترجمه و تلخیص: محمود بنی اسدی

منبع: Get Digital Help

برچسب ها: آموزش اکسلآموزش تابع INDEXآموزش تابع OFFSETآموزش توابع اکسلتابع COUNTتابع Countifتابع Indexتابع MATCHتابع OFFSETتابع ROWتابع SMALLترفند اکسلجدیدترین آموزش های اکسللیست کشویی داینامیک در اکسللیست کشویی در اکسل
آواتار محمود بنی اسدی (مدیر سایت)
درباره محمود بنی اسدی (مدیر سایت)

فارغ التحصیل کارشناسی ارشد حسابداری، ده سال سابقه تدریس اکسل در سطوح مختلف از قبیل فرمول نویسی، ابزارهای هوش تجاری، ترفندها و ... ، نویسنده شش مقاله در سطح ملی و ISI

نوشته های بیشتر از محمود بنی اسدی (مدیر سایت)
جدید تر ده تکنیک جهت سرعت بخشیدن به کار با اکسل
قدیمی تر علت خطای جمع اعداد در اکسل

دیدگاهتان را بنویسید لغو پاسخ

نوشته های اخیر
  • صفر تا صد آموزش تابع IF در اکسل با 4 مثال کاربردی
  • آموزش ثابت کردن سطر و ستون ها در صفحه اکسل و چاپ
  • 4نکته کاربردی اکسل که هر کاربر باید بداند!
  • آموزش Pivot Table در اکسل همراه با 1 نکته طلایی
  • آموزش هایلایت سطر انتخاب شده در اکسل با 2 تابع
  • استخراج داده ها با چندین شرط به کمک Power Pivot
  • ایجاد لیست داده های یکتا به کمک Power Pivot در اکسل
  • تفاوت 2 تابع SUM و SUBTOTAL هراه با مثال و ترفند اکسلی
  • کاربرد فازی لوکاپ و برتری آن به Vlookup
  • آموزش رایگان 6 ترفند اکسلی که فقط حرفه ایی ها می دانند !
محصولات
  • محاسبه-کارکرد1401 نرم افزار محاسبه کارکرد سال1401 در اکسل
    390,000 تومان 280,000 تومان
  • حقوق-دستمزد-1401 نرم افزار پیشرفته محاسبه حقوق و دستمزد 1401
    390,000 تومان 290,000 تومان
  • نرم افزار پیشرفته حقوق و دستمزد 1400 نرم افزار پیشرفته حقوق و دستمزد 1400
    290,000 تومان
  • نرم افزار محاسبه کارکرد سال1400 نرم افزار محاسبه کارکرد سال1400
    260,000 تومان
  • نمودار سود و زیان به سبکی زیبا نمودار سود و زیان به سبکی زیبا
    50,000 تومان
  • ایجاد لیست کشویی جستجوگر اکسل ایجاد لیست کشویی جستجوگر اکسل
    70,000 تومان 35,000 تومان
  • نرم افزار پیشرفته حقوق و دستمزد نرم افزار پیشرفته حقوق و دستمزد
    160,000 تومان 149,000 تومان
  • نرم افزار محاسبه کارکرد سال99 نرم افزار محاسبه کارکرد سال99
    120,000 تومان 99,000 تومان
  • آموزش ساخت نمودار گیج (Gauge) آموزش ساخت نمودار گیج (Gauge)
    15,000 تومان
  • دوره آموزش پایه ای برنامه نویسی در پاورکوئری و پاور بی آی دوره آموزش پایه ای برنامه نویسی در پاورکوئری و پاور بی آی
    نمره 5.00 از 5

    590,000 تومان
دسته‌های محصولات
  • آموزش پاور بی آی
  • دوره اکسل در حسابداری
  • دوره صفر تا صد اکسل
  • دوره گوگل شیت
  • دوره های رایگان
  • سایر
مطالب کمیاب در اکسل و حسابداری

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

تماس باما
  • آدرس: جنت آباد جنوبی، مجتمع سمرقند، طبقه ۸ ، واحد ۸۰۷
  • ۰۹۲۱۷۷۰۹۵۶۹
  • Info@arshad-hesabdar.ir

فهرست سفارشی
  • صفحه اصلی
  • قوانین و مقررات
تمامی حقوق برای سایت مطالب کمیاب در اکسل و حسابداری محفوظ می باشد.
اشتراک گذاری در شبکه های اجتماعی
ارسال به ایمیل
https://arshad-hesabdar.ir/?p=2185
لیست علاقه مندی ها 0
صفحه لیست علاقه مندی را باز کنید به خرید ادامه دهید
صفحه اینستاگرام
صفحه یوتیوب
صفحه اینستاگرام

اینستاگرام سافت ور

صفحه یوتیوب

کانال یوتیوب سافت ور

Instagram Youtube
مرورگر شما از HTML5 پشتیبانی نمی کند.

سوالی دارید؟ از ما بپرسید، کارشناسان ما در اسرع وقت با شما تماس می گیرند.

Logo

مطالب کمیاب در اکسل و حسابداری

  • ۰۹۲۱۷۷۰۹۵۶۹
  • شنبه تا چهارشنبه از ساعت 8 تا 17
  • Info@arshad-hesabdar.ir