FILTER function in Excel - quickly extract data

Summary

Microsoft Excel में FILTER function एक शक्तिशाली tool है जो आपको specific criteria के आधार पर एक बड़ी range या table से data का एक उपसमूह (subset) निकालने की अनुमति देता है। बड़े dataset के साथ काम करते समय यह function विशेष रूप से उपयोगी होता है, क्योंकि यह data को manual रूप से sort या filter किए बिना आपके लिए आवश्यक data को तुरंत filter और निकाल सकता है। Filter function users को सीधे अपने worksheet के भीतर tables को filter करने में सक्षम करके data analysis को बढ़ाता है, जिससे data handling अधिक कुशल और सहज हो जाती है।

यह function, Excel 365 और Excel 2021 में प्रस्तुत किया गया था।

महत्वपूर्ण points:

• Purpose : Specified range से data का एक उपसमूह (subset) निकालने के लिए
• Category : Lookup and Reference functions
• Released date/version : Microsoft 365 and Office 2021
• Input/parameters required : 3 (2 mandatory & 1 optional)
• Output : Array of filtered data

---

Syntax:

FILTER function का basic syntax इस प्रकार है:

=FILTER(array, include, [if_empty])

Array:

FILTER function में "array" argument उन cells की range होती है जिन्हें आप filter करना चाहते हैं। यह cells की range, named range या table हो सकती है। Table का उपयोग करते समय, आप cell references के बजाय table column नामों को reference कर सकते हैं, जो आपके formulas को अधिक सहज और पढ़ने में आसान बना सकता है।

Include:

FILTER function में "include" argument एक Boolean expression होता है जो filter किए गए परिणामों में rows को शामिल करने के लिए criteria specify करता है। यह expression एक साधारण comparison operator हो सकता है, जैसे "greater than" या "less than", या यह एक अधिक complex expression हो सकता है जो logical operators का उपयोग करके कई criteria को जोड़ता है।

[If_Empty]:

FILTER function में optional "if_empty" argument वह value है जो कोई मेल खाने वाली rows नहीं मिलने पर function लौटाता है। यह argument तब उपयोगी होता है जब आप यह दर्शाने के लिए कोई message या value प्रदर्शित करना चाहते हैं कि कोई matching data नहीं मिला।

---

उपयोग कैसे करें (How to use)

आसान उदाहरण (Basic example):

मान लें कि आपके पास data की एक table है जिसमें निम्नलिखित columns शामिल हैं: "Name", "Age", "Gender", और "Salary"। आप data को केवल उन rows को दिखाने के लिए filter करना चाहते हैं जिनकी आयु 30 से अधिक या उसके बराबर है। यहां बताया गया है कि आप FILTER function का उपयोग करके ऐसा कैसे कर सकते हैं:

Name	Age	Gender	Salary
John	25	Male	50000
John	30	Male	55000
Maria	31	Female	60000
Jane	30	Female	45000
John	26	Male	60000
Rose	29	Female	70000
James	35	Male	75000
John	35	Male	80000
Robert	29	Male	77000

1. उस cell का चयन करें जहाँ आप filter किए गए result को display करना चाहते हैं।
2. निम्न formula दर्ज करें:
   
   =FILTER(A2:D10, B2:B10>=30)
   
   यहां, A2:D10 cells की range है जिसमें original data है, और B2:B10>=30 filter के लिए criteria है। यह expression प्रत्येक पंक्ति के लिए TRUE का एक Boolean value देता है जहाँ आयु 30 से अधिक या उसके बराबर है।

3. Formula लागू करने के लिए Enter दबाएं।

FILTER function केवल specified criteria को पूरा करने वाली rows के साथ एक नई table लौटाता है।

कठिन उदाहरण (Advance example):

FILTER function का उपयोग अधिक advance filtering operations करने के लिए भी किया जा सकता है, जैसे कि multiple criteria के आधार पर filter करना या wildcard characters का उपयोग करना।

Multiple criteria के आधार पर filter करने के लिए, आप ampersand "&" operator का उपयोग दो या अधिक expressions को combine करने के लिए कर सकते हैं। उदाहरण के लिए, यदि आप data को केवल उन rows को दिखाने के लिए filter करना चाहते हैं जहाँ आयु 30 से अधिक या उसके बराबर है और Gender "Female" है, तो आप निम्न formula का उपयोग कर सकते हैं:

=FILTER(A2:D10, (B2:B10>=30) * (C2:C10="Female"))

यहां, * operator दो expressions पर एक logical AND operation करता है, जो केवल TRUE का Boolean value लौटाता है, जब दोनों expressions TRUE होते हैं।

अपने filter criteria में wildcard characters का उपयोग करने के लिए, आप asterisk mark "*" और question mark "?" का उपयोग कर सकते हैं। asterisk किसी भी number को represent करता है, और question mark single character को represent करता है। उदाहरण के लिए, यदि आप केवल उन rows को दिखाने के लिए data को filter करना चाहते हैं जहाँ Name column में "John" text है, तो आप निम्न formula का उपयोग कर सकते हैं:

=FILTER(A2:D10, ISNUMBER(SEARCH("John", A2:A10)))

यहां, SEARCH function Name column में प्रत्येक cell के भीतर "John" text की स्थिति लौटाता है, और ISNUMBER function प्रत्येक cell के लिए TRUE लौटाता है जहां "John" पाया जाता है। FILTER function तब एक table देता है जिसमें केवल rows होती हैं जहाँ Name column में "John" होता है।

---

Error handling

#SPILL error

Excel में #SPILL! error तब होती है जब किसी formula (जैसे FILTER function) का output अपने result को पास के (adjacent) cells में "spill" नहीं कर पाता है। Excel के dynamic array, FILTER जैसे function को कई result लौटाने की अनुमति देते हैं, जो adjacent cells में फैल जाते हैं। यदि कोई चीज़ इस spill को रोक रही है, तो #SPILL! error दिखाई देती है।

#SPILL! error के सामान्य कारण:

1. Blocked by Other Data: उन cells में पहले से ही data है जहाँ परिणाम spill होने चाहिए।
2. Merged Cells: destination range में merge किए गए cells होते हैं, जो result को spill होने से रोकते हैं।
3. Hidden Rows/Columns: छिपा हुआ data या rows जो spill range को रोकती हैं।
4. Array Too Large: परिणाम उपलब्ध स्थान में fit होने के लिए बहुत बड़ा है (उदाहरण के लिए, पर्याप्त खाली cells नहीं हैं)।
5. Dynamic Array Formula References: गलत formula reference invalid spill का कारण बनते हैं।

आइए इन कारणों को समाधान के साथ देखें:

उदाहरण 1: Blocked by Other Data

Problem:

आप FILTER function का उपयोग करके data filter करने का प्रयास कर रहे हैं, लेकिन परिणाम adjacent cells में नहीं फैल सकता है क्योंकि output को रोकने वाला मौजूदा डेटा मौजूद है।

A	B	C
Apple	100	Data
Banana	150	Blocked
Orange	200

Formula:

=FILTER(A2:A4, B2:B4>100)

यदि column C में data है, जहाँ filter किए गए परिणाम spill होने चाहिए, तो यह #SPILL! error उत्पन्न करेगा।

Solution:

• Spill range को block करने वाले किसी भी cell को clear करें (इस मामले में, column C clear करें)।
• सुनिश्चित करें कि उन cell में कोई मौजूदा data, formula या formatting नहीं है, जहाँ परिणाम spill होने चाहिए।
• आप column C में content को manual रूप से हटा सकते हैं, या अपने formula को किसी अन्य range में ले जा सकते हैं, जहाँ पर्याप्त खाली स्थान हो।

उदाहरण 2: Merged Cells in the Spill Range

Problem:

आप data filter करने का प्रयास कर रहे हैं, लेकिन उस area में merge किए गए cells हैं, जहाँ परिणाम spill होने चाहिए।

A	B
Apple	100
Banana	150
Orange	200
Merged cells

Formula:

=FILTER(A2:A4, B2:B4>100)

चूँकि spill range में merge किए गए cells शामिल हैं, इसलिए Excel परिणाम नहीं लौटा सकता और #SPILL! error उत्पन्न करता है।

Solution:

• Destination range में मौजूद cells को unmerge करें, या formula को किसी दूसरे स्थान पर ले जाएँ जहाँ merge किए गए cells के बिना पर्याप्त स्थान हो।

उदाहरण 3: Hidden Rows or Columns

Problem:

यदि spill range में बाधा डालने वाली छिपी हुई rows या columns हैं, तो FILTER function परिणाम spill नहीं कर सकता।

उदाहरण के लिए, यदि column C (जहाँ filter किया गया data spill होना चाहिए) छिपा हुआ है, तो आपको #SPILL! error का सामना करना पड़ेगा।

Solution:

परिणामों को ठीक से spill करने देने के लिए छिपी हुई rows या columns को unhide करें।

• छिपे हुए column या rows header पर right-click करें और unhide चुनें।
• जब छिपी हुई rows या columns unhide हो जाएँ, तो #SPILL! error गायब हो जानी चाहिए।

उदाहरण 4: Array Too Large to Fit

Problem:

FILTER function का परिणाम उपलब्ध स्थान में fit होने के लिए बहुत बड़ा है, और Excel परिणामों को ठीक से spill नहीं कर सकता है।

A	B
Apple	100
Banana	150
Orange	200

Formula:

=FILTER(A2:A4, B2:B4>50)

यदि परिणाम 100 rows में फैलना चाहिए, लेकिन केवल 5 खाली rows उपलब्ध हैं, तो Excel एक #SPILL! error फेंक देगा।

Solution:

• सुनिश्चित करें कि formula के फैलने के लिए पर्याप्त स्थान है। Formula को किसी नए स्थान पर ले जाएँ या परिणामों के ठीक से फैलने के लिए पर्याप्त स्थान खाली करें।

उदाहरण 5: Using Dynamic Array Formulas with Incorrect References

Problem:

आप किसी array या formula को अनुचित तरीके से reference कर सकते हैं, जिससे formula गलत तरीके से फैल सकता है।

उदाहरण के लिए:

=FILTER(A2:A4, B2:B10>100)

जब A2:A4 और B2:B10 range के अलग-अलग आकार होते हैं, तो समस्याएँ हो सकती हैं जो spill error को जन्म देती हैं।

Solution:

• सुनिश्चित करें कि dynamic array functions का उपयोग करते समय range के आकार मेल खाते हों। उदाहरण के लिए:

Correct formula:

=FILTER(A2:A4, B2:B4>100)

#SPILL! errors को कैसे हल करें:

Spill range को block करने वाले data या formula की जाँच करें: उन cells को देखें जहाँ परिणाम को spill करना है। अगर कोई content है, तो उन cells को clear करें।

Spill range में cells को Unmerge करें: merger की गई cells, function को spill होने से रोकती हैं। परिणामों को प्रदर्शित करने की अनुमति देने के लिए उन्हें unmerge करें।

सुनिश्चित करें कि range का आकार consistent हो: सुनिश्चित करें कि function में उपयोग की जाने वाली सभी ranges समान आकार की हों।

छिपी हुई rows/columns जाँचें: किसी भी छिपी हुई row या column को unhide करें जो परिणामों को block कर सकती हैं।

Formula को अलग ले जाएँ: यदि वर्तमान स्थान में पर्याप्त स्थान नहीं है, तो formula को sheet में एक नए area में ले जाएँ जहाँ परिणाम को spill करने के लिए पर्याप्त जगह हो।

#CALC! error 

Excel के FILTER function में #CALC! error आम तौर पर तब होती है जब function specified criteria को पूरा करने वाले किसी भी result को वापस करने में असमर्थ होता है। यह आम तौर पर तब होता है जब कोई data condition से मेल नहीं खाता है, या यदि array operation के परिणामस्वरूप invalid calculation होती है।

FILTER function में #CALC! error के सामान्य कारण:

1. No Data Matches the Criteria: यदि range में कोई भी data filter condition से मेल नहीं खाता है, तो FILTER function #CALC! error लौटाएगा।
2. Empty or Invalid Arrays: यदि filter की जा रही array या criteria array खाली या अमान्य है।
3. Dividing by Zero in Criteria: criteria के अंदर एक formula का उपयोग करना जो invalid operation की ओर ले जाता है, जैसे शून्य से भाग देना।
4. Array with No Elements to Return: जब filter operation का परिणाम एक खाली set उत्पन्न करता है।

उदाहरणों के साथ #CALC! error को संभालना:

उदाहरण 1: No Data Matches the Criteria

Problem:

आप एक range को filter कर रहे हैं लेकिन कोई भी data specified condition से मेल नहीं खाता है, जिसके कारण #CALC! error होती है।

A
Apple
Banana
Orange

=FILTER(A2:A4, A2:A4="Grapes")

चूंकि सूची में कोई "Grapes" नहीं है, इसलिए Excel एक #CALC! error लौटाता है क्योंकि कोई भी data filter criteria से मेल नहीं खाता है।

Solution:

• आप इसे [if_empty] argument का उपयोग करके संभाल सकते हैं ताकि यह specify किया जा सके कि कोई मिलान न मिलने पर क्या लौटाया जाना चाहिए।

Correct formula:

=FILTER(A2:A4, A2:A4="Grapes", "No matches found")

Result:

Result
No matches found

उदाहरण 2: Empty Arrays

Problem:

आप एक खाली data range को filter करने का प्रयास कर रहे हैं, जिसके परिणामस्वरूप #CALC! error होती है।

A
(empty)

Formula:

=FILTER(A2:A4, A2:A4="Apple")

चूँकि range empty है, इसलिए Excel एक #CALC! error लौटाता है।

Solution:

• जाँचें कि range empty है या नहीं और यदि आवश्यक हो तो एक custom message लौटाएँ।

Correct formula:

=IF(COUNTA(A2:A4)=0, "No data available.", FILTER(A2:A4, A2:A4="Apple"))

यह जाँचता है कि क्या range पहले empty है, और यदि हाँ, तो "No data available" लौटाता है।

उदाहरण 3: Division by Zero in Criteria

Problem:

आप criteria में एक formula का उपयोग कर रहे हैं जिसमें शून्य हो सकने वाले value से divide करना शामिल है, जिससे #CALC! error हो सकती है।

A	B
Apple	0
Banana	2
Orange	4

Formula:

=FILTER(A2:A4, B2:B4/2>1)

इस मामले में, B2 (जो 0 है) को 2 से भाग देने पर समस्या उत्पन्न होती है और #CALC! error होती है।

Solution:

• आप यह सुनिश्चित करके इसे संभाल सकते हैं कि आप शून्य से भाग न दें, उदाहरण के लिए error को पकड़ने के लिए IFERROR का उपयोग करके।

Correct formula:

=FILTER(A2:A4, IFERROR(B2:B4/2>1, FALSE))

यह सुनिश्चित करता है कि divide से होने वाली किसी भी error को संभाला जाए, और filter valid conditions के साथ आगे बढ़े।

उदाहरण 4: Handling Multiple Criteria That Result in No Match

Problem:

जब कई criteria के साथ filtering की जाती है, यदि कोई data किसी भी criteria से मेल नहीं खाता है, तो FILTER function #CALC! error देता है।

A	B
Apple	10
Banana	15
Orange	20

Formula:

=FILTER(A2:A4, (A2:A4="Apple") * (B2:B4>100))

यह एक #CALC! error लौटाएगा क्योंकि सूची में "Apple" होने पर, कॉलम B में कोई भी value 100 से अधिक नहीं है, जिसका अर्थ है कि कोई भी row combined condition से मेल नहीं खाती है।

Solution:

• [if_empty] argument का उपयोग करके एक custom message specify करके error को संभालें।

Correct formula:

=FILTER(A2:A4, (A2:A4="Apple") * (B2:B4>100), "No matching data")

Result:

Result
No matches found

#CALC! error को संभालने के लिए General Strategy:

1. [if_empty] argument का उपयोग करें: यदि कोई data filter criteria से मेल नहीं खाता है, तो हमेशा एक value या message specify करें।
   =FILTER(array, criteria, "No matching data")
2. IFERROR या IF statement का उपयोग करें: अपने criteria में संभावित calculation errors को पकड़ने के लिए (जैसे शून्य से भाग देना या empty arrays)।
   =IFERROR(FILTER(A2:A4, A2:A4="Apple"), "Error occurred")
3. खाली ranges की जाँच करें: यह सुनिश्चित करने के लिए COUNTA function का उपयोग करें कि आप जिस range को filter कर रहे हैं वह खाली नहीं है।
   =IF(COUNTA(A2:A4)=0, "No data available", FILTER(A2:A4, A2:A4="Apple"))

#VALUE! error

FILTER function में #VALUE! error कई कारणों से हो सकती है, जैसे:

1. Mismatched Range Sizes: यदि filtering के लिए range (array) और criteria के लिए range (include argument) समान आकार के नहीं हैं।
2. Invalid Data Types: यदि filtering के लिए उपयोग किए जा रहे data के type के साथ कोई समस्या है (उदाहरण के लिए, numbers के बजाय text)।
3. Invalid Operations: ऐसे operation का उपयोग करना जो data type पर नहीं किया जा सकता (उदाहरण के लिए, numbers के साथ text की तुलना करना)।

यहां कुछ सामान्य scenarios दिए गए हैं और FILTER function में #VALUE! error को हल करने का तरीका बताया गया है।

उदाहरण 1: Mismatched Range Sizes

Problem:

आप column A से data filter करने का प्रयास कर रहे हैं, लेकिन column B में criteria के लिए range समान आकार की नहीं है।

A	B
Apple	1
Banana	2
Orange	3

Formula:

=FILTER(A2:A4, B2:B5>1)

इस मामले में, B2:B5, A2:A4 से एक row बड़ी है। यह #VALUE! error का कारण बनता है क्योंकि FILTER function दोनों range को समान आकार का होने की अपेक्षा करता है।

Solution:

• सुनिश्चित करें कि criteria range में rows की संख्या filter किए जा रहे data के समान है।

Correct formula:

=FILTER(A2:A4, B2:B4>1)

उदाहरण 2: Invalid Data Types

Problem:

आप numerical values filter कर रहे हैं, लेकिन criteria में number की बजाय text लिया हुआ है।

A
100
200
300

Formula:

=FILTER(A2:A4, A2:A4>"Apple")

चूँकि आप numbers की तुलना text से कर रहे हैं, इसलिए Excel इसे process नहीं कर सकता, जिसके परिणामस्वरूप #VALUE! error होती है।

Solution:

• सुनिश्चित करें कि आप सही data types की तुलना कर रहे हैं। यदि आप numbers की तुलना करना चाहते हैं, तो criteria में numerical values का उपयोग करें।

Correct formula:

=FILTER(A2:A4, A2:A4>150)

उदाहरण 3: Logical Operation में Inconsistent Array Sizes का उपयोग करना

Problem:

जब कई criteria's का उपयोग किया जाता है, यदि प्रत्येक स्थिति के लिए arrays समान आकार की नहीं हैं, तो यह #VALUE! error उत्पन्न कर सकती है।

A	B
Apple	1
Banana	2
Orange	3

Formula:

=FILTER(A2:A4, (A2:A4="Apple") + (B2:B5>1))

यहाँ, A2:A4 में 3 rows हैं, लेकिन B2:B5 में 4 rows हैं, जो #VALUE! error का कारण बनती हैं।

Solution:

• सुनिश्चित करें कि logical operations में उपयोग की जाने वाली सभी arrays समान आकार की हों।

Correct formula:

=FILTER(A2:A4, (A2:A4="Apple") + (B2:B4>1))

उदाहरण 4: Empty या Invalid Data पर Filter filter करना

Problem:

यदि आप किसी ऐसी range पर filter लागू कर रहे हैं जिसमें data के भीतर #VALUE! errors हैं, तो यह FILTER function को विफल कर सकता है।

A	B
Apple	1
Banana	#VALUE!
Orange	3

Formula:

=FILTER(A2:A4, B2:B4>1)

चूँकि B3 में #VALUE! error है, इसलिए यह संपूर्ण FILTER function को error देता है।

Solution:

• आप filter से errors वाली rows को बाहर करने के लिए ISNUMBER function का उपयोग करके इसे संभाल सकते हैं।

Correct formula:

=FILTER(A2:A4, ISNUMBER(B2:B4) * (B2:B4>1))

#VALUE! error को संभालने के लिए सामान्य समाधान:

Range के आकार की जाँच करें: सुनिश्चित करें कि आप जिस range को filter कर रहे हैं और criteria के लिए range एक ही आकार की है।

Error handling का उपयोग करें: अपने FILTER function को IFERROR के साथ लपेटें या unexpected errors को संभालने के लिए [if_empty] argument का उपयोग करें।

उदाहरण:

=IFERROR(FILTER(A2:A4, B2:B4>1), "Error occurred")

Correct Data Type: सुनिश्चित करें कि comparison criteria आपके द्वारा filter किए जा रहे column के data type से मेल खाता है। उदाहरण के लिए, numbers की तुलना text से न करें।

अगर आपको error का कोई specific मामला आता है, तो मुझे बताएं, और हम मिलकर उस पर काम कर सकते हैं!

#N/A error

FILTER function में #N/A error आम तौर पर तब होती है जब आपके द्वारा set किए गए criteria से मेल खाने वाला कोई data नहीं होता है। ऐसा इसलिए होता है क्योंकि FILTER function दी गई condition के आधार पर कोई भी row वापस नहीं कर सकता है। इस मामले में #N/A error एक खाली result set के लिए एक सामान्य response है, लेकिन formula को modify करके इसे अधिक सुंदर तरीके से संभाला जा सकता है।

FILTER function में #N/A error के Common Causes:

1. No Matching Data: range में कोई भी row filter criteria को पूरा नहीं करती है।
2. Criteria Mismatch: आप ऐसी condition के आधार पर filter कर रहे हैं जो data में मौजूद नहीं है।
3. Incorrect Range References: function में उपयोग की जाने वाली range वास्तविक data structure से मेल नहीं खाती हैं।

#N/A error को कैसे Handle करें:

जब कोई data filter criteria से मेल नहीं खाता है, तो आप default return value प्रदान करके #N/A error को handle कर सकते हैं। FILTER function में इसके लिए एक optional argument है, या आप IFERROR या IFNA का उपयोग कर सकते हैं।

उदाहरण 1: No Data Matches the Criteria

Problem:

आप एक list filter कर रहे हैं, लेकिन कोई भी data condition से मेल नहीं खाता, इसलिए FILTER function #N/A लौटाता है।

A
Apple
Banana
Orange

Formula:

=FILTER(A2:A4, A2:A4="Grapes")

चूँकि column A में कोई "Grapes" नहीं है, इसलिए Excel #N/A error लौटाएगा।

Solution:

• आप FILTER function में [if_empty] argument का उपयोग करके कोई message या value specify कर सकते हैं, जब कोई data criteria से मेल नहीं खाता है।

Correct formula:

=FILTER(A2:A4, A2:A4="Grapes", "No matching data")

अब, यदि कोई data filter से मेल नहीं खाता है, तो Excel #N/A के बजाय custom message "No matching data" लौटाएगा।

Result:

Result
No matches found

उदाहरण 2: Using IFERROR to Handle the #N/A Error

Problem:

आप data filter कर रहे हैं, लेकिन कोई मिलान न होने के कारण, Excel #N/A लौटाता है।

Formula:

=FILTER(A2:A4, A2:A4="Grapes")

Solution:

• आप #N/A error को पकड़ने के लिए IFERROR function का उपयोग कर सकते हैं और filter द्वारा कोई मिलान न मिलने पर custom value या message लौटा सकते हैं।

Correct formula:

=IFERROR(FILTER(A2:A4, A2:A4="Grapes"), "No fruits found")

Result:

Result
No fruits found

जब filter criteria कोई परिणाम नहीं देते हैं, तो IFERROR function #N/A के बजाय "No fruits found" लौटाएगा।

उदाहरण 3: Handling #N/A with Multiple Criteria

Problem:

आप data filter करने के लिए कई criteria का उपयोग कर रहे हैं, और कोई भी row सभी conditions को पूरा नहीं करती है, इसलिए Excel #N/A error लौटाता है।

A	B
Apple	100
Banana	150
Orange	200

Formula:

=FILTER(A2:A4, (A2:A4="Apple") * (B2:B4>200))

इस मामले में, भले ही "Apple" मौजूद हो, column B में कोई भी value 200 से ज़्यादा नहीं है. इसका नतीजा #N/A होता है.

Solution:

• आप [if_empty] argument के साथ default value प्रदान करके #N/A error को संभाल सकते हैं.

Correct formula:

=FILTER(A2:A4, (A2:A4="Apple") * (B2:B4>200), "No matching results")

Result:

Result
No matching results

उदाहरण 4: Criteria Mismatch Causing #N/A

Problem:

आप data को ऐसी condition के आधार पर filter कर रहे हैं जो dataset में मौजूद नहीं है। उदाहरण के लिए, numeric column में text filter करने से कोई match नहीं हो सकता है और #N/A error हो सकती है।

A
100
200
300

Formula:

=FILTER(A2:A4, A2:A4="Text")

चूँकि आप text condition का उपयोग करके numeric column filter कर रहे हैं, इसलिए कोई मिलान नहीं होगा, और Excel #N/A लौटाएगा।

Solution:

• सुनिश्चित करें कि criteria column में data type से मेल खाता है। यदि कोई मिलान नहीं मिलता है, तो [if_empty] argument का उपयोग करें।

Correct formula:

=FILTER(A2:A4, A2:A4>150, "No matching numbers")

Result:

Result
200
300

General Solutions to Handle the #N/A Error in FILTER:

1. Use the [if_empty] argument: FILTER function का उपयोग करते समय, जब कोई मिलान न मिले तो हमेशा एक optional return value प्रदान करें।
   =FILTER(array, criteria, "No matching data")
2. Use IFERROR or IFNA: ये function #N/A error को पकड़ सकते हैं और एक custom message लौटा सकते हैं।
   =IFERROR(FILTER(array, criteria), "No data found")
3. Ensure Data and Criteria Match: सुनिश्चित करें कि आपके criteria का data type (जैसे, text या number) आपके द्वारा filter किए जा रहे column के data से मेल खाता है।

#REF! error

FILTER function में #REF! error आम तौर पर तब होती है जब कोई formula किसी invalid cell या range को refer करता है। यह विभिन्न कारणों से हो सकता है, जैसे कि उन cell या column को हटाना जिन पर formula निर्भर करता है, या formula में गलत range का उपयोग करना।

Common Causes of the #REF! Error in the FILTER Function:

1. Deleted Cells or Columns: formula उन cell या column को refer करता है जिन्हें हटा दिया गया है।
2. Incorrect Range References: FILTER function में उपयोग की गई range सही ढंग से मेल नहीं खाती हैं।
3. Relative Cell References Moved Out of Bounds: किसी formula को copy या drag करते समय, relative reference data range से बाहर जा सकते हैं।
4. Array Formula Issues: referenced array invalid या range से बाहर है।

आइए examples और solutions के साथ इन कारणों पर नज़र डालें।

उदाहरण 1: Deleted Cells or Columns

Problem:

FILTER function उन cell या column को reference कर रहा है जिन्हें हटा दिया गया है।

A	B
Apple	100
Banana	150
Orange	200

Formula (before deletion):

=FILTER(A2:A4, B2:B4>100)

यदि आप column A या column B हटाते हैं, तो FILTER function एक #REF! error लौटाएगा क्योंकि यह ऐसी range को reference करने का प्रयास कर रहा है जो अब मौजूद नहीं है।

Solution:

• Formula के reference की जाँच करें और उन्हें valid range में update करें।
• यदि आपने गलती से cell या column हटा दिए हैं, तो हटाने को undo करने का प्रयास करें (Ctrl + Z दबाएँ)।
• यदि हटाना जानबूझकर किया गया था, तो formula को एक valid range को reference करने के लिए update करें।

उदाहरण 2: Incorrect Range References

Problem:

FILTER function में range मेल नहीं खाती हैं, जिससे #REF! error होती है। Array और criteria को समान आकार की range को reference करना चाहिए।

A	B
Apple	100
Banana	150
Orange	200

Incorrect Formula:

=FILTER(A2:A5, B2:B4>100)

इस उदाहरण में, formula A2 को reference करता है लेकिन इसकी तुलना B2 से करता है, जिसके कारण mismatch होता है और #REF! error होती है।

Solution:

• सुनिश्चित करें कि FILTER function में दोनों range समान आकार की हैं।

Correct formula:

=FILTER(A2:A4, B2:B4>100)

Result:

Result
Banana
Orange

उदाहरण 3: Relative Cell References Moved Out of Bounds

Problem:

Formula को copy करते या नीचे या drag करते समय, relative cell reference सीमा से बाहर जा सकते हैं, जिससे #REF! error हो सकती है।

Formula (before moving):

=FILTER(A2:A4, B2:B4>100)

यदि आप इस formula को नीचे या drag करते हैं, तो relative reference data range से बाहर जा सकते हैं (उदाहरण के लिए, A5 और B5), जिसके परिणामस्वरूप #REF! error हो सकती है।

Solution:

• Formula को copy या drag करने पर उन्हें move होने से बचाने के लिए absolute reference का उपयोग करके range को lock करें।

Correct formula:

=FILTER($A$2:$A$4, $B$2:$B$4>100)

यह सुनिश्चित करता है कि formula को copy या move किए जाने पर भी range समान रहे।

उदाहरण 4: Invalid Array Formula

Problem:

FILTER function एक ऐसी array को reference करता है जो सीमा से बाहर है या invalid है, जिसके परिणामस्वरूप #REF! error होती है।

A	B
Apple	100
Banana	150
Orange	200

Incorrect Formula:

=FILTER(A2:A4, C2:C4>100)

यहाँ, formula column C को reference कर रहा है (जो मौजूद नहीं है या जिसमें कोई data नहीं है)। इसके परिणामस्वरूप #REF! error होती है क्योंकि column C invalid है या range से बाहर है।

Solution:

• सुनिश्चित करें कि formula में referenced सभी range valid हैं और data set के भीतर हैं।

Correct formula:

=FILTER(A2:A4, B2:B4>100)

Result:

Result
Banana
Orange

General Solutions to Handle the #REF! Error in FILTER:

1. Check for Deleted Cells or Columns: यदि आपने गलती से cell या column हटा दिए हैं, तो action को undo करने के लिए Ctrl + Z का उपयोग करें, या नई range को दर्शाने के लिए formula को update करें।
2. Use Matching Range Sizes: सुनिश्चित करें कि array और criteri range समान आकार की हैं।
   =FILTER(A2:A4, B2:B4>100)
3. Use Absolute References: Formula की copy बनाते या उन्हें move करते समय accidental reference changes से बचने के लिए, dollar sign ($) जोड़कर absolute reference का उपयोग करें।
   =FILTER($A$2:$A$4, $B$2:$B$4>100)
4. Check for Valid Array Ranges: सुनिश्चित करें कि सभी referenced ranges valid हैं और आपकी worksheet में data की सीमाओं के भीतर हैं।

#NAME error

Excel में #NAME? error तब होती है जब Excel किसी formula में text को नहीं पहचानता है। यह उन मामलों में आम है जहाँ formula में कोई typo या function नाम होता है जिसे Excel पहचान नहीं सकता है। FILTER function के context में, #NAME? error आमतौर पर निम्नलिखित कारणों से उत्पन्न होती है:

Common Causes of the #NAME? Error in the FILTER Function:

1. Misspelled Function Name: यदि आप गलती से FILTER को FILTRE या FILTERS जैसा कुछ लिख देते हैं, तो Excel उसे पहचान नहीं पाएगा और #NAME? error लौटाएगा।
2. Excel Version Compatibility: FILTER function केवल Excel 365 और Excel 2021 में उपलब्ध है। यदि आप Excel के पुराने version (जैसे Excel 2019 या 2016) का उपयोग कर रहे हैं, तो FILTER function पहचाना नहीं जाएगा, और आपको #NAME? error मिलेगी।
3. Incorrect Range or Criteria References: यदि आप जिस column या names range का reference दे रहे हैं, जो गलत है या वह मौजूद नहीं है, तो Excel #NAME? error लौटाएगा।
4. Improperly Defined Named Ranges: यदि FILTER function किसी ऐसी named range को reference करता है जो मौजूद नहीं है, तो Excel उसे process नहीं कर पाएगा और #NAME? error लौटाएगा।
5. Text Not Encased in Quotes: Text criteria का उपयोग करते समय, text को quotation marks में enclose न करने से भी #NAME? error हो सकती है।

आइए examples और solutions के साथ इनका विश्लेषण करें।

उदाहरण 1: Misspelled Function Name

Problem:

आपने function नाम की spelling गलत लिखी है, इसलिए Excel उसे पहचान नहीं पाता है।

A	B
Apple	100
Banana	150
Orange	200

Incorrect Formula:

=FILTERS(A2:A4, B2:B4>100)

चूँकि FILTERS एक valid Excel function नहीं है, इसलिए Excel एक #NAME? error देता है।

Solution:

• सुनिश्चित करें कि function का नाम सही ढंग से लिखा गया है।

Correct formula:

=FILTER(A2:A4, B2:B4>100)

Result:

Result
Banana
Orange

उदाहरण 2: Excel Version Incompatibility

Problem:

आप Excel के पुराने version में FILTER function का उपयोग कर रहे हैं, जैसे कि Excel 2016 या 2019, जो FILTER जैसे dynamic array function का समर्थन नहीं करता है।

Formula:

=FILTER(A2:A4, B2:B4>100)

Solution:

• FILTER function केवल Excel 365 और Excel 2021 में उपलब्ध है। यदि आप पुराने version का उपयोग कर रहे हैं, तो आप या तो ऐसे version में upgrade कर सकते हैं जो FILTER का समर्थन करता है या same result प्राप्त करने के लिए array formula के साथ INDEX/MATCH, या IF जैसे optional function का उपयोग कर सकते हैं।
• For older versions: आप data filter करने के लिए एक array formula का उपयोग कर सकते हैं:
  =INDEX(A2:A4, MATCH(TRUE, B2:B4>100, 0))
• लेकिन Excel 365 या 2021 में अपग्रेड करना सबसे अच्छा समाधान है।

उदाहरण 3: Incorrect Named Range or Reference

Problem:

आप एक named range या column का reference दे रहे हैं जो workbook में मौजूद नहीं है।

A	B
Apple	100
Banana	150
Orange	200

Incorrect Formula:

=FILTER(Items, Prices>100)

यदि item और price named range के रूप में define नहीं हैं, तो Excel एक #NAME? error लौटाएगा।

Solution:

• सुनिश्चित करें कि named range मौजूद हैं या उन्हें formula में उपयोग करने से पहले define करें।

Correct formula:

यदि item और price named range हैं, तो सुनिश्चित करें कि वे सही data range को reference करते हैं।

वैकल्पिक रूप से, यदि आप named range का उपयोग नहीं करना चाहते हैं, तो सीधे reference का उपयोग करें:

=FILTER (A2:A4, B2:B4>100)

उदाहरण 4: Text Not Encased in Quotes

Problem:

Text criteria का उपयोग करते समय, आपको text को double quotation marks में enclose करना होगा। यदि आप ऐसा करने में विफल रहते हैं, तो Excel text को नहीं पहचान पाएगा और एक #NAME? error लौटाएगा।

A	B
Apple	100
Banana	150
Orange	200

Incorrect Formula:

=FILTER(A2:A4, A2:A4=Apple)

यहाँ, Apple को quotation marks में enclose किया जाना चाहिए, अन्यथा, Excel को लगता है कि "Apple" एक name या variable है और #NAME? error देता है।

Solution:

• "Apple" text को double quotation marks में enclose करें ताकि यह संकेत मिले कि यह text criteria है।

Correct formula:

=FILTER(A2:A4, A2:A4="Apple")

Result:

Result
Apple

उदाहरण 5: Improperly Defined Named Ranges

Problem:

Formula एक named range का उपयोग करता है जो ठीक से defined नहीं है या मौजूद नहीं है, जिससे #NAME? error होती है।

Formula:

=FILTER(MyData, MyData>100)

यदि MyData को named range के रूप में defined नहीं किया गया है, तो Excel एक #NAME? error लौटाएगा।

Solution:

इसे ठीक करने के लिए, सुनिश्चित करें कि आपने named range को सही ढंग से defined किया है। आप निम्न द्वारा named range को defined कर सकते हैं:

1. उन cells को highlight करना जिन्हें आप name देना चाहते हैं।
2. Formula tab पर जाकर Name Define पर क्लिक करें ।
3. Range को एक Name देना (जैसे MyData)।

वैकल्पिक रूप से, आप सीधे cell reference का उपयोग कर सकते हैं:

=FILTER(A2:A4, A2:A4>100)

General Solutions to Handle the #NAME? Error in FILTER:

1. Check for Typos: सुनिश्चित करें कि function name और range names की spelling सही है।
• FILTER की spelling सही होनी चाहिए।
2. Use Text in Quotes: Text criteri को हमेशा double quotation marks में enclose करें।
• Apple के बजाय "Apple"।
3. Use Excel 365 or Excel 2021: सुनिश्चित करें कि आप Excel के ऐसे version का उपयोग कर रहे हैं जो FILTER function का समर्थन करता है।
4. Correct Range References: सुनिश्चित करें कि सभी named ranges सही ढंग से defined हैं और सभी cell reference worksheet में मौजूद हैं।

---

Conclusion:

Microsoft Excel में FILTER function एक शक्तिशाली tool है जो आपको specific criteria के आधार पर एक बड़ी range या table से data को जल्दी और आसानी से filter करने और निकालने की अनुमति देता है। बड़े datasets के साथ काम करते समय यह function आपका बहुत समय और प्रयास बचा सकता है, और यह dynamic filters बनाने में भी आपकी मदद कर सकता है जो source data में बदलाव के रूप में automatic रूप से update हो जाते हैं। FILTER function का उपयोग करना सीखकर, आप अपने Excel skills को अगले स्तर पर ले जा सकते हैं और अधिक कुशल और प्रभावी data analyst बन सकते हैं।

---

Filter with other functions:

Filter duplicate values with Unique function:

आप Excel में duplicate values को filter करने के लिए UNIQUE function के साथ FILTER function का उपयोग कर सकते हैं। UNIQUE function किसी range या array से unique values निकालता है, और फिर आप specific criteria के आधार पर इन unique values को प्रदर्शित करने के लिए FILTER का उपयोग कर सकते हैं।

उदाहरण

मान लीजिए कि आपके पास sales representatives की एक सूची है और उनकी sales राशि निम्न range (A1) में है:

Name	Sales
John	150
Alice	200
John	150
Bob	300
Alice	200

Step 1: Extract Unique Names

सबसे पहले, आप "Name" column से unique नामों की सूची प्राप्त करने के लिए cell D2 में UNIQUE function का उपयोग कर सकते हैं।

=UNIQUE(A2:A6)

यह निम्नलिखित लौटाएगा:

Unique Names
John
Alice
Bob

Step 2: Unique Names के लिए Sales Filter करें

इसके बाद, इन unique representatives के लिए sales data filter करने के लिए, आप UNIQUE के साथ FILTER का उपयोग कर सकते हैं। आप एक summary table बना सकते हैं जो unique नाम और उनकी संबंधित sales amount दिखाती है।

आप इस तरह के formula का उपयोग cell E2 में कर सकते हैं:

=SUMIF(A2:A6, UNIQUE(A2:A6), B2:B6)

यह निम्नलिखित लौटाएगा:

Sales
300
400
300

Filter with multiple criteria in multiple columns

AND condition के साथ उदाहरण:

आप logical conditions को मिलाकर कई columns में कई criteria's के आधार पर data filter करने के लिए Excel में FILTER function का उपयोग कर सकते हैं। आप AND condition के लिए * operator और OR condition के लिए + operator का उपयोग कर सकते हैं।

मान लीजिए कि आपके पास निम्नलिखित range (A1) में sales representatives का dataset है:

Name	Region	Sales
John	North	150
Alice	South	200
Bob	North	300
Charlie	West	100
David	South	400

आप "North" क्षेत्र से 200 से अधिक sales वाले sales representatives को खोजने के लिए data को filter करना चाहते हैं।

आप यह कैसे कर सकते हैं:

=FILTER(A2:C6, (B2:B6 = "North") * (C2:C6 > 200), "No results")

स्पष्टीकरण (Explanation)

1. Array: A2:C6 वह data की range है जिसे आप filter करना चाहते हैं।
2. Include:
• (B2:B6 = "North") जाँचता है कि क्षेत्र "North" है या नहीं।
• (C2:C6 > 200) जाँचता है कि sales 200 से अधिक है या नहीं।
• * operator एक AND condition के रूप में कार्य करता है, जिसका अर्थ है कि किसी row को शामिल करने के लिए दोनों criteria सत्य होने चाहिए।
3. If_empty: यदि कोई row criteria को पूरा नहीं करती है तो "No results" प्रदर्शित होगा।

Output

प्रदान किए गए dataset के आधार पर, इस सूत्र का output होगा:

Name	Region	Sales
Bob	North	300

चूंकि North क्षेत्र में representative के लिए 200 से अधिक sales केवल Bob के द्वारा हुई है, इसलिए यह ऊपर दिया गया परिणम लौटाएगा।

OR condition के साथ उदाहरण:

यदि आप उन sales representatives को filter करना चाहते हैं जो या तो "North" क्षेत्र में हैं या जिनकी sales 200 से अधिक है, तो आप + operator का उपयोग कर सकते हैं:

=FILTER(A2:C6, (B2:B6 = "North") + (C2:C6 > 200), "No results")

Output

इस मामले में, output में वे सभी rows शामिल होंगी जो निम्न में से किसी भी शर्त को पूरा करती हैं:

Name	Region	Sales
John	North	150
Bob	North	300
David	South	400

Filter with multiple criteria in same column

आइए एक उदाहरण के साथ काम करें जहाँ आप Excel के FILTER function का उपयोग करके कई criteria वाले column में data filter करना चाहते हैं।

उदाहरण:

मान लीजिए कि आपके पास column A में products की एक सूची है और आप केवल "Apple" और "Banana" products को filter करना चाहते हैं।

Products
Apple
Banana
Orange
Apple
Mango
Banana
Grapes

हम OR condition लागू करने के लिए निम्नलिखित सूत्र का उपयोग कर सकते हैं:

=FILTER(A2:A8, (A2:A8="Apple") + (A2:A8="Banana"))

सूत्र कैसे काम करता है:

• A2:A8="Apple" प्रत्येक row में value "Apple" है या नहीं, इस पर निर्भर करते हुए TRUE या FALSE values की एक array लौटाता है।
• A2:A8="Banana" के लिए एक समान array लौटाता है।
• + (OR operator) दो arrays को जोड़ता है। किसी भी array में कोई भी TRUE filter में संबंधित row को रखेगा।

परिणाम:

सूत्र केवल उन rows को filter करता है और लौटाता है जिनमें "Apple" या "Banana" होता है।

Filtered Result
Apple
Banana
Apple
Banana