نوشته اصلی توسط
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 هم اضافه مي کنيم براي اينکه اگر جواب تابع نوشته شده ما خطا داشت به جاي عبارت خطا سل را برابر تهي قرار دهد.
علاقه مندی ها (Bookmarks)