راهنمایی جهت استخراج نام های منحصر به فرد در یک ستون

**face_2_faces** · 2015/08/16, 09:31

نوشته اصلی توسط علي پارسا

با آدرس دهي مطلق و نسبي در اکسل آشنايي داريد؟

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

**mokaram** · 2015/08/16, 12:09

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

فرض کنید که در سل b1 فرمول a1+a2 را نوشتید حالا اگه فرمول را به پایین درگ کنید یا اونا کپی و جای دیگه پیست کنید می بینید که فرمول شما تغییر میکنه این یعنی اینکه شما دارید از آدرس دهی نسبی استفاده می کنید اما یه وقتهایی نیاز دارید که آدرس یه سل تو فرمول ثابت بمونه اینجا مجبورید که از آدرس دهی مطلق استفاده کنید مثلا برای فرمول بالا به صورت زیر نوشته میشه :

کد PHP:


$a$1+$a$2

**majid_mx4** · 2015/08/16, 14:17

با سلام

ضمن تشکر از تمامی دوستان عزیزم که زحمت پاسخ را کشیدن

راه دیگر نیز ارائه می گردد .

خلاصه : یونیک کردن تکست در یک ستون به وسیله فرمول

با تشکر میر

فایل های پیوست شده

ایرانیان Mir.xlsx (10.3 کیلو بایت, مشاهدات 16)

**face_2_faces** · 2015/08/16, 14:35

نوشته اصلی توسط M_R_M

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

فرض کنید که در سل b1 فرمول a1+a2 را نوشتید حالا اگه فرمول را به پایین درگ کنید یا اونا کپی و جای دیگه پیست کنید می بینید که فرمول شما تغییر میکنه این یعنی اینکه شما دارید از آدرس دهی نسبی استفاده می کنید اما یه وقتهایی نیاز دارید که آدرس یه سل تو فرمول ثابت بمونه اینجا مجبورید که از آدرس دهی مطلق استفاده کنید مثلا برای فرمول بالا به صورت زیر نوشته میشه :

کد PHP:


$a$1+$a$2

دوست گلم ممنون از اطلاعاتت .من راه استفاده از $ را بلدم.الان میخوام بدونم فرمولتون از چه قسمت هایی تعیین شده.کلا 2 تا فرمول هست یکی در ستون a.که میخوام بدونم اگه خواستم در یک شیت دیگه اطلاعاتم باشه چکار کنم که بیاد و فرمول بعدی هم در ستون d هستش.لطفا راهنمایی کنید.ممنون

**mokaram** · 2015/08/16, 16:32

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

**Ali Parsaei** · 2015/08/17, 00:08

نوشته اصلی توسط face_2_faces

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

ببينيد ما در ستون کمکي مي آييم با تابع countif بررسي مي کنيم که نام وارده در هر سل (منظور نام وارده در سل هم ارز در ستون دوم است) در خانه هاي بالايي اش چند بار تکرار شده.
البته خانه اولي که نامي بالاتر از خودش ندارد! پس خودش را با خودش مقايسه مي کنيم، پس مي شود:
علي از خانه b2 تا b2 چند بار تکرار شده؟
حسن از خانه b2 تا b3 چند بار تکرار شده؟
حسين از خانه b2 تا b4 چند بار تکرار شده؟
الي آخر .....
به اين ترتيب اولين باري که به يک اسم برميخوريم پاسخ تابع counif عدد يک خواهد بود چون تا قبل از آن در خانه هاي بالايي اش تکرار نشده بوده.
بعد ما پاسخهاي تابع countif را در يک تابع شرطي if قرار مي دهيم و شرط مي کنيم که هر جا نتيجه تابع counif عدد يک بود سل را برابر: ()row قرار دهد و اگر غير يک بود سل را برابر تهي ("") قرار دهد.
اما ()row يعني چه؟
()row خودش يک تابع است وقتي در يک سل تابع ()row بنويسيم (يعني بنويسيم: ()row= ) نتيجه شماره عدد مربوط به رديف آن سل خواهد بود.
مثلا" اگر در خانه a15 تابع را بنويسيد نتيجه 15 و اگر در خانه u23 بنويسيد نتيجه 23 خواهد شد.
پس ما در اصل با تابع if گفته ايم که: اگر نتيجه تابع counif عدد يک بود شماره رديف آن سطر را بنويس و اگر نبود هيچي ننويس.
در نتيجه همانطور که مي بينيد در اولين باري که هر يک از اسامي در ستون مربوطه درج شده در ستون کمکي شماره رديف همان سطر درج شده، در رديف دو عدد 2 در رديف سه عدد 3 در رديف چهار عدد 4 و ... در رديف هشت عدد 8
تا اينجا درست؟
خوب .... حالا ما بايد چکار کنيم که در ستون d تمام موارد يکتاي اسامي ستون b را داشته باشيم؟
با اين ستون کمکي که ما درست کرديم هر اسم اولين باري که در ستون b درج شده در ستون a يک عدد کنارش نوشته شده، ولي بارهاي بعد که تکرار شده ديگر در ستون a کنارش چيزي نوشته نشده.
پس ما بايد به ترتيب هر جا که در ستون a عددي نوشته شده هم ارز آن در ستون b را به ستون d انتقال دهيم.
به اين منظور از چند تابع بايد کمک گرفت اولين تابع تابع small است، اين تابع در يک محدوده قادر به پيداکردن کمترين مقدار طبق تعيين ما است، آرگومان اول آن محدوده مورد نظر و دومين آرگومان رتبه عدد است (يعني اينکه کوچکترين مقدار را پيدا کند يا دومين مقدار کوچک يا سومين يا .....)
ما با اين تابع مي توانيم کوچکترين مقدار را در ستون a پيدا کنيم، بعد با تابع vlookup مي توانيم بگوييم که وقتي کوچکترين مقدار را پيدا کردي معادل هم ارز آن در ستون b را به ما بده.
پس مي توانيم در سل d2 بنويسيم:

کد PHP:


=VLOOKUP(SMALL($A$2:$A$21;1);$A$2:$B$21;2;FALSE)

تفسير فرمول اين است: اولين مقدار کم در محدوده a2 تا a21 را پيدا کن و سپس هم ارز آن در ستون b را به ما بده
بعد در سل d3 بايد اين فرمول را بنويسيم:

کد PHP:


=VLOOKUP(SMALL($A$2:$A$21;2);$A$2:$B$21;2;FALSE)

تفسير فرمول اين است: دومين مقدار کم در محدوده a2 تا a21 را پيدا کن و سپس هم ارز آن در ستون b را به ما بده
همين طور مي توانيم فرمول را به سلهاي پايين درگ کنيم، ولي مشکل اينجا است که در تابع small وقتي فرمول را درگ مي کنيم بايد رتبه (آرگومان دوم) را سطر به سطر به صورت دستي يکي اضافه کنيم!!
براي حل اين مشکل يک کلکي مي زنيم،
در سل d2 جواب چنين فرمولي چه خواهد بود: 1-()row ؟؟؟؟ رديف سل d2 که برابر 2 است .... پس مي شود 1-2 يعني: 1
پس مي توانيم در فرمول خانه d2 در آرگومان دوم تابع small به جاي 1 بنويسيم: 1-()row
به اين ترتيب اگر فرمول را در خانه هاي پايينتر درگ کنيم 1-()row هم يکي بهش اضافه مي شود چون مثلا" 1-()row در سل d3 مي شود 1-3 يعني: 2
پس به اين ترتيب آرگومان دوم تابع small خط به خط يکي اضافه خواهد شد بدون اينکه مجبور باشيم دستي اين کار را انجام دهيم.
بديهي است فرضا" اگر شروع کار را از خانه: d7 شروع کنيم بايد به جاي 1-()row بنويسيم: 6-()row يا اگر خانه dx باشد بايد بنويسيم: (row()-(x-1
در آخر تابع iferror هم اضافه مي کنيم براي اينکه اگر جواب تابع نوشته شده ما خطا داشت به جاي عبارت خطا سل را برابر تهي قرار دهد.