ایکسل

ایکسل میں متحرک صف فارمولے

Dynamic Array Formulas Excel

سالوں میں ایکسل فارمولوں میں متحرک صفیں سب سے بڑی تبدیلی ہیں۔ شاید اب تک کی سب سے بڑی تبدیلی۔ اس کی وجہ یہ ہے کہ متحرک صفیں آپ کو ایک ہی وقت میں ایک سے زیادہ اقدار کے ساتھ آسانی سے کام کرنے دیتی ہیں۔ بہت سے صارفین کے لیے ، یہ پہلی بار ہوگا جب وہ صفوں کے فارمولوں کو سمجھیں گے اور استعمال کریں گے۔





یہ ایک بڑا اپ گریڈ اور خوش آئند تبدیلی ہے۔ متحرک صفیں ایکسل میں کچھ واقعی مشکل مسائل کو حل کریں گی ، اور بنیادی طور پر ورک شیٹ کے ڈیزائن اور تعمیر کے طریقے کو تبدیل کردیں گی۔

دستیابی

متحرک صفیں اور نیچے دیئے گئے نئے افعال صرف دستیاب ہیں۔ ایکسل 365۔ . ایکسل 2016 اور ایکسل 2019 متحرک صف فارمولہ سپورٹ پیش نہیں کرتے ہیں۔ سہولت کے لیے ، میں ذیل کے ورژن میں فرق کرنے کے لیے 'ڈائنامک ایکسل' (ایکسل 365) اور 'روایتی ایکسل' (2019 یا اس سے پہلے) استعمال کروں گا۔





نئی: متحرک صف فارمولا ویڈیو ٹریننگ

نئے افعال۔

متحرک صف کی تازہ کاری کے ایک حصے کے طور پر ، ایکسل میں اب 8 نئے افعال شامل ہیں جو کہ ان مسائل کو حل کرنے کے لیے براہ راست متحرک صفوں کا فائدہ اٹھاتے ہیں جو روایتی فارمولوں کے ساتھ روایتی طور پر مشکل ہیں۔ ہر فنکشن کی تفصیلات اور مثالوں کے لیے نیچے دیئے گئے لنکس پر کلک کریں:

فنکشن مقصد۔
فلٹر ڈیٹا کو فلٹر کریں اور مماثل ریکارڈ واپس کریں۔
رینڈرری بے ترتیب نمبروں کی صف تیار کریں۔
ترتیب ترتیب وار نمبروں کی صف تیار کریں
ترتیب دیں کالم کے لحاظ سے رینج کو ترتیب دیں۔
SORTBY رینج کو کسی اور رینج یا صف کے مطابق ترتیب دیں۔
انوکھا۔ فہرست یا رینج سے منفرد اقدار نکالیں۔
XLOOKUP VLOOKUP کا جدید متبادل۔
XMATCH MATCH فنکشن کا جدید متبادل۔

ویڈیو: ایکسل میں نئے متحرک صفوں کے افعال۔ (تقریبا 3 3 منٹ)



محور ٹیبل میں فیلڈ کیسے شامل کریں

نوٹ: XLOOKUP اور XMATCH نئے متحرک صف کے افعال کے اصل گروپ میں نہیں تھے ، لیکن وہ نئے متحرک صف کے انجن پر بہت اچھا چلاتے ہیں۔ XLOOKUP VLOOKUP کی جگہ لیتا ہے اور ایک جدید ، لچکدار انداز پیش کرتا ہے جو صفوں سے فائدہ اٹھاتا ہے۔ XMATCH MATCH فنکشن کا اپ گریڈ ہے ، جو نئی صلاحیتوں کو فراہم کرتا ہے۔ انڈیکس اور میچ۔ فارمولے

مثال

اس سے پہلے کہ ہم تفصیلات میں آجائیں ، آئیے ایک سادہ سی مثال دیکھیں۔ ذیل میں ہم نیا استعمال کر رہے ہیں۔ انوکھا فنکشن۔ B5: B15 کی حد سے منفرد اقدار نکالنا ، a کے ساتھ۔ سنگل E5 میں درج فارمولا:

 
= UNIQUE (B5:B15) // return unique values in B5:B15

منفرد فنکشن کی مثال

نتیجہ شہر کے پانچ منفرد ناموں کی فہرست ہے ، جو E5: E9 میں ظاہر ہوتے ہیں۔

تمام فارمولوں کی طرح ، ڈیٹا تبدیل ہونے پر UNIQUE خود بخود اپ ڈیٹ ہو جائے گا۔ ذیل میں ، وینکوور نے قطار 11 پر پورٹلینڈ کی جگہ لے لی ہے۔ UNIQUE کے نتیجے میں اب وینکوور شامل ہے:

تبدیلی کے بعد منفرد فنکشن کی مثال۔

سپلنگ - ایک فارمولا ، بہت سی اقدار۔

ڈائنامک ایکسل میں ، ایک سے زیادہ اقدار کو واپس کرنے والے فارمولے ' کھیل یہ اقدار براہ راست ورک شیٹ پر۔ یہ فوری طور پر فارمولا استعمال کرنے والوں کے لیے زیادہ منطقی ہوگا۔ یہ ایک مکمل طور پر متحرک رویہ بھی ہے - جب سورس ڈیٹا تبدیل ہوتا ہے تو پھیلے ہوئے نتائج فوری طور پر اپ ڈیٹ ہو جاتے ہیں۔

آئتاکار جو اقدار کو گھیر لیتا ہے اسے 'کہتے ہیں کھیل کی حد '. آپ دیکھیں گے کہ اسپل رینج میں خاص روشنی ڈالی گئی ہے۔ اوپر کی منفرد مثال میں ، پھیلنے کی حد E5: E10 ہے۔

جب ڈیٹا تبدیل ہوتا ہے ، ضرورت کے مطابق اسپل رینج بڑھے گی یا معاہدہ کرے گی۔ آپ نئی اقدار کو شامل کرتے ہوئے دیکھ سکتے ہیں ، یا موجودہ اقدار غائب ہو سکتی ہیں۔ اس طرح ، اسپل رینج ایک نئی قسم کی متحرک رینج ہے۔

نوٹ: جب دوسرے ڈیٹا کے ذریعے سپلنگ کو بلاک کیا جائے تو آپ کو ایک #SPILL خرابی نظر آئے گی۔ ایک بار جب آپ اسپل رینج کے لیے جگہ بنا لیں گے تو فارمولا خود بخود پھیل جائے گا۔

ویڈیو: سپلنگ اور اسپل رینج۔

سپل رینج ریفرنس۔

اسپل رینج کا حوالہ دینے کے لیے ، رینج میں پہلے سیل کے بعد ہیش کی علامت (#) استعمال کریں۔ مثال کے طور پر ، مندرجہ بالا UNIQUE فنکشن کے نتائج کا حوالہ دینے کے لیے:

 
=E5# // reference UNIQUE results

یہ پوری سپل رینج کا حوالہ دینے کے مترادف ہے ، اور آپ کو یہ نحو نظر آئے گا جب آپ کوئی ایسا فارمولا لکھیں گے جس سے مراد مکمل سپل رینج ہو۔

آپ اسپل رینج ریفرنس کو دوسرے فارمولوں میں براہ راست کھلا سکتے ہیں۔ مثال کے طور پر ، UNIQUE کی طرف سے لوٹے گئے شہروں کی تعداد گننے کے لیے ، آپ استعمال کر سکتے ہیں:

 
= COUNTA (E5#) // count unique cities

متحرک صف سپل رینج ریفرنس کی مثال

جب اسپل رینج تبدیل ہوتی ہے ، فارمولا تازہ ترین ڈیٹا کی عکاسی کرے گا۔

بڑے پیمانے پر سادگی۔

نئے متحرک صف فارمولوں کے اضافے کا مطلب ہے کہ کچھ فارمولوں کو بہت آسان بنایا جا سکتا ہے۔ یہاں چند مثالیں ہیں:

  • منفرد اقدار نکالیں اور فہرست بنائیں ( پہلے | کے بعد )
  • منفرد اقدار شمار کریں ( پہلے | کے بعد )
  • ریکارڈ فلٹر اور نکالیں ( پہلے | کے بعد )
  • جزوی مماثلتیں نکالیں ( پہلے | کے بعد )

ایک کی طاقت۔

'ایک فارمولا ، کئی اقدار' کے نقطہ نظر کے سب سے طاقتور فوائد میں سے ایک پر کم انحصار ہے۔ مطلق یا ملا ہوا حوالہ جات. جیسا کہ ایک متحرک صف فارمولہ ورک شیٹ پر نتائج پھیلاتا ہے ، حوالہ جات میں کوئی تبدیلی نہیں کی جاتی ہے ، لیکن فارمولا درست نتائج پیدا کرتا ہے۔

مثال کے طور پر ، ذیل میں ہم گروپ A میں ریکارڈ نکالنے کے لیے FILTER فنکشن استعمال کرتے ہیں۔ سیل F5 میں ، ایک واحد فارمولا درج ہے:

 
= FILTER (B5:D11,B5:B11='a') // references are relative

متحرک صف ایک فارمولا صرف مثال۔

نوٹس کریں کہ دونوں حدود غیر متعلقہ حوالہ جات کو غیر مقفل ہیں ، لیکن فارمولا بالکل کام کرتا ہے۔

یہ بہت سے صارفین کے لیے بہت بڑا فائدہ ہے ، کیونکہ یہ فارمولے لکھنے کے عمل کو بہت آسان بنا دیتا ہے۔ ایک اور اچھی مثال کے لیے ، ذیل میں ضرب جدول ملاحظہ کریں۔

زنجیر کے افعال۔

چیزیں واقعی دلچسپ ہوجاتی ہیں جب آپ ایک سے زیادہ متحرک صف فنکشن کو ایک ساتھ جوڑتے ہیں۔ شاید آپ UNIQUE کے ذریعے لوٹے گئے نتائج کو ترتیب دینا چاہتے ہیں؟ آسان۔ بس لپیٹ دیں SORT فنکشن اس طرح UNIQUE فنکشن کے ارد گرد:

ایک ساتھ UNIQUE اور SORT کی مثال۔

پہلے کی طرح ، جب سورس ڈیٹا تبدیل ہوتا ہے ، نئے منفرد نتائج خود بخود ظاہر ہوتے ہیں ، اچھی طرح سے ترتیب دیا جاتا ہے۔

مقامی رویہ۔

یہ سمجھنا ضروری ہے کہ متحرک صف کا رویہ ایک ہے۔ مقامی اور گہری مربوط . کب کوئی فارمولا ایک سے زیادہ نتائج لوٹاتا ہے ، یہ نتائج ورک شیٹ پر کئی خلیوں میں پھیل جائیں گے۔ اس میں پرانے افعال شامل ہیں جو اصل میں متحرک صفوں کے ساتھ کام کرنے کے لیے نہیں بنائے گئے ہیں۔

مثال کے طور پر ، روایتی ایکسل میں ، اگر ہم LEN فنکشن کو رینج متن کی اقدار کے ، ہم دیکھیں گے a سنگل نتیجہ ڈائنامک ایکسل میں ، اگر ہم LEN فنکشن کو اقدار کی ایک رینج دیتے ہیں تو ہم دیکھیں گے۔ ایک سے زیادہ نتائج نیچے دی گئی اسکرین بائیں طرف پرانا رویہ اور دائیں طرف نیا رویہ دکھاتی ہے۔

صفوں کے ساتھ LEN فنکشن - پرانا اور نیا۔

یہ ایک بہت بڑی تبدیلی ہے جو ہر قسم کے فارمولوں کو متاثر کر سکتی ہے۔ مثال کے طور پر ، VLOOKUP فنکشن کالم انڈیکس کا استعمال کرتے ہوئے ، میز سے ایک ہی قیمت لانے کے لیے ڈیزائن کیا گیا ہے۔ تاہم ، ڈائنامک ایکسل میں ، اگر ہم VLOOKUP کو ایک سے زیادہ کالم انڈیکس دیتے ہیں۔ صف مسلسل اس کے جیسا:

 
= VLOOKUP ('jose',F7:H10,{1,2,3},0)

VLOOKUP متعدد کالم واپس کرے گا:

VLOOKUP اور متحرک صفوں کے ساتھ ایک سے زیادہ نتائج۔

دوسرے لفظوں میں ، اگرچہ VLOOKUP کبھی بھی متعدد اقدار کو واپس کرنے کے لیے ڈیزائن نہیں کیا گیا تھا ، اب یہ ایسا کر سکتا ہے ، متحرک ایکسل میں نئے فارمولہ انجن کا شکریہ۔

تمام فارمولے۔

آخر میں ، نوٹ کریں کہ متحرک صفیں کام کرتی ہیں۔ تمام فارمولے نہ صرف افعال . ذیل کی مثال میں سیل C5 میں ایک واحد فارمولا ہے:

ایکسل میں ریورس ایکس اور y محور
 
=B5:B14*C4:L4

نتیجہ 10 بائی 10 رینج میں پھیلتا ہے جس میں 100 سیل ہوتے ہیں۔

متحرک صف ضرب جدول

نوٹ: روایتی ایکسل میں ، اگر آپ سرے فارمولے کے ذریعے لوٹے گئے متعدد نتائج دیکھ سکتے ہیں۔ فارمولے کا معائنہ کرنے کے لیے F9 استعمال کریں۔ . لیکن جب تک آپ بطور فارمولا داخل نہیں کر رہے ہیں۔ کثیر سیل صف فارمولہ ، ورک شیٹ پر صرف ایک قدر ظاہر ہوگی۔

صفیں مرکزی دھارے میں جاتی ہیں۔

متحرک صفوں کے رول آؤٹ کے ساتھ ، لفظ ' صف 'زیادہ کثرت سے پاپ اپ ہونے والا ہے۔ در حقیقت ، آپ دیکھ سکتے ہیں کہ 'صف' اور 'رینج' تقریبا almost ایک دوسرے کے ساتھ استعمال ہوتے ہیں۔ آپ ایکسل میں صفوں کو اس طرح گھوبگھرالی منحنی خطوط وحدانی میں بند دیکھیں گے:

 
{1,2,3} // horizontal array {123} // vertical array

اری ایک پروگرامنگ اصطلاح ہے جو اشیاء کی فہرست سے مراد ہے جو کسی خاص ترتیب میں ظاہر ہوتی ہے۔ ایکسل فارمولوں میں اکثر صفیں آنے کی وجہ یہ ہے کہ صفیں کر سکتی ہیں۔ خلیوں کی ایک رینج میں اقدار کا مکمل اظہار کرتے ہیں۔ .

ویڈیو: ایک صف کیا ہے؟

صفوں کا آپریشن اہم ہو جاتا ہے۔

چونکہ ڈائنامک ایکسل فارمولے آسانی سے ایک سے زیادہ اقدار کے ساتھ کام کر سکتے ہیں ، سرنی آپریشن زیادہ اہم ہو جائے گا۔ اصطلاح 'ارے آپریشن' سے مراد وہ اظہار ہے جو ایک صف پر منطقی ٹیسٹ یا ریاضی کا آپریشن کرتا ہے۔ مثال کے طور پر ، ذیل کا اظہار جانچتا ہے اگر B5: B9 میں اقدار 'ca' کے برابر ہیں

 
=B5:B9='ca' // state = 'ca'

سرنی آپریشن مثال ٹیسٹ a

کیونکہ B5 میں 5 خلیات ہیں: B9 ، نتیجہ ایک صف میں 5 TRUE/FALSE اقدار ہیں:

 
{FALSETRUEFALSETRUETRUE}

ذیل میں صف آپریشن 100 سے زیادہ مقدار کی جانچ پڑتال کرتا ہے:

 
=C5:C9>100 // amounts > 100

اری آپریشن مثال ٹیسٹ بی۔

حتمی صف آپریشن ایک ٹیسٹ میں ٹیسٹ A اور ٹیسٹ B کو یکجا کرتا ہے:

 
=(B5:B9='ca')*(C5:C9>100) // state = 'ca' and amount > 100

سرنی آپریشن مثال ٹیسٹ a اور b۔

نوٹ: ایکسل خود کار طریقے سے TRUE اور FALSE ویلیوز کو ریاضی کے آپریشن کے دوران 1 اور 0 پر مجبور کرتا ہے۔

اسے ایکسل میں متحرک صف فارمولوں میں واپس لانے کے لیے ، نیچے دی گئی مثال ظاہر کرتی ہے کہ ہم فلٹر فنکشن کے اندر بالکل اسی صف کو کیسے استعمال کر سکتے ہیں جیسا کہ شامل دلیل:

فلٹر فنکشن کے ساتھ سرنی آپریشن۔

فلٹر دو ریکارڈ واپس کرتا ہے جہاں state = 'ca' اور رقم> 100۔

مظاہرے کے لیے دیکھیں: دو معیار کے ساتھ فلٹر کرنے کا طریقہ (ویڈیو).

نئے اور پرانے صف فارمولے۔

ڈائنامک ایکسل میں ، کنٹرول + شفٹ + انٹر کے ساتھ صف فارمولے داخل کرنے کی ضرورت نہیں ہے۔ جب ایک فارمولا بنایا جاتا ہے تو ، ایکسل چیک کرتا ہے کہ آیا فارمولا کئی اقدار واپس کر سکتا ہے۔ اگر ایسا ہے تو ، یہ خود بخود ایک متحرک صف فارمولہ کے طور پر محفوظ ہوجائے گا ، لیکن آپ کو گھوبگھرالی منحنی خطوط وحدانی نظر نہیں آئے گی۔ نیچے دی گئی مثال ڈائنامک ایکسل میں درج ایک عام صف فارمولہ دکھاتی ہے۔

روایتی ایکسل میں بنیادی صف فارمولہ

اگر آپ روایتی ایکسل میں وہی فارمولا کھولتے ہیں تو آپ کو گھوبگھرالی منحنی خطوط وحدانی نظر آئے گی۔

متحرک ایکسل میں بنیادی صف فارمولہ۔

دوسری سمت جاتے ہوئے ، جب ڈائنامک ایکسل میں ایک 'روایتی' صف فارمولا کھولا جاتا ہے ، آپ کو فارمولا بار میں گھوبگھرالی منحنی خطوط وحدانی نظر آئے گی۔ مثال کے طور پر ، ذیل کی سکرین روایتی ایکسل میں ایک سادہ صف فارمولا دکھاتی ہے۔

سادہ صف فارمولا جس میں گھوبگھرالی منحنی خطوط وحدانی نظر آتی ہے۔

تاہم ، اگر آپ بغیر کسی تبدیلی کے فارمولہ دوبارہ داخل کرتے ہیں تو ، گھوبگھرالی منحنی خطوط وحدانی کو ہٹا دیا جاتا ہے ، اور فارمولا وہی نتیجہ لوٹاتا ہے:

سادہ صف فارمولا جس میں گھوبگھرالی منحنی خطوط وحدانی نظر نہیں آتی۔

بنیادی بات یہ ہے کہ کنٹرول + شفٹ + انٹر (سی ایس ای) کے ساتھ داخل ہونے والے صف فارمولے اب بھی مطابقت برقرار رکھنے کے لیے کام کرتے ہیں ، لیکن آپ کو ڈائنامک ایکسل میں سی ایس ای کے ساتھ صف فارمولے داخل کرنے کی ضرورت نہیں ہونی چاہیے۔

کردار

متحرک صفوں کے تعارف کے ساتھ ، آپ دیکھیں گے کہ @ کردار فارمولوں میں زیادہ کثرت سے ظاہر ہوتا ہے۔ @ کردار ایک رویے کو قابل بناتا ہے جسے ' واضح تقطیع '. واضح تقطیع ایک منطقی عمل ہے جہاں بہت سی اقدار کو ایک قدر میں کم کر دیا جاتا ہے۔

روایتی ایکسل میں ، ضمنی چوراہا ایک خاموش رویہ ہے جو استعمال کیا جاتا ہے (جب ضروری ہو) ایک سے زیادہ اقدار کو کم کرنے کے لیے ایک سیل کے نتیجے میں۔ ڈائنامک ایکسل میں ، عام طور پر اس کی ضرورت نہیں ہے ، کیونکہ متعدد نتائج ورک شیٹ پر پھیل سکتے ہیں۔ جب اس کی ضرورت ہو تو ، licit حرف کے ساتھ دستی طور پر تقطیع تقطیع کی درخواست کی جاتی ہے۔

جب اسپریڈشیٹ کھولنے سے ایکسل کا پرانا ورژن بنتا ہے تو ، آپ existing حرف کو موجودہ فارمولوں میں خود بخود شامل دیکھ سکتے ہیں جن میں ممکنہ، استعداد بہت سی اقدار کو لوٹانا۔ روایتی ایکسل میں ، ایک فارمولا جو متعدد اقدار کو واپس کرتا ہے وہ ورک شیٹ پر نہیں پھیلے گا۔ @ کردار متحرک ایکسل میں اسی طرز عمل کو مجبور کرتا ہے تاکہ فارمولا اسی طرح برتاؤ کرے اور وہی نتیجہ لوٹائے جیسا کہ اصل ایکسل ورژن میں تھا۔

دوسرے الفاظ میں ، @ کو ایک پرانے فارمولے کو ورک شیٹ پر متعدد نتائج سے بچنے سے روکنے کے لیے شامل کیا جاتا ہے۔ فارمولے پر منحصر ہے ، آپ @ حرف کو ہٹا سکتے ہیں اور فارمولے کا رویہ تبدیل نہیں ہوگا۔

خلاصہ

  • متحرک صفیں لکھنے میں کچھ فارمولوں کو بہت آسان بنا دے گی۔
  • اب آپ مماثل ڈیٹا کو فلٹر کرسکتے ہیں ، ترتیب دے سکتے ہیں ، اور فارمولوں کے ساتھ آسانی سے منفرد اقدار نکال سکتے ہیں۔
  • فلٹر اور ترتیب دینے جیسے کام کرنے کے لیے ڈائنامک اری فارمولے کو زنجیر (نیسٹڈ) کیا جا سکتا ہے۔
  • ایک سے زیادہ قیمت لوٹنے والے فارمولے خود بخود پھیل جائیں گے۔
  • ایک صف فارمولہ داخل کرنے کے لیے Ctrl+Shift+Enter استعمال کرنا ضروری نہیں ہے۔
  • متحرک صف فارمولے صرف ایکسل 365 میں دستیاب ہیں۔
مصنف ڈیو برنس۔


^