Waxaan dhawaan qoray maqaal ku saabsan sida loo isticmaalo shaqooyinka kooban ee Excel^{(how to use summary functions in Excel)} si fudud loo soo koobo xog badan, laakiin maqaalkani wuxuu tixgeliyey dhammaan xogta ku jirta xaashida shaqada. Ka waran haddii aad rabto in aad eegto xog hoose oo kaliya oo aad soo koobto xogta hoose?

Excel , waxaad ku abuuri kartaa filtarrada tiirarka qarin doona safafka aan ku habboonayn shaandhadaada. Intaa waxaa dheer, waxaad sidoo kale isticmaali kartaa hawlaha gaarka ah ee Excel si aad u soo koobto xogta adigoo isticmaalaya kaliya xogta la sifeeyay.

Maqaalkan, waxaan kugu dhex mari doonaa tillaabooyinka lagu abuurayo filtarrada gudaha Excel iyo sidoo kale adeegsiga howlaha gudaha ku dhex jira si aan u soo koobo xogtaas la sifeeyay.

Ku samee filtarrada fudud ee Excel

Gudaha Excel , waxaad samayn kartaa filtarrada fudud iyo filtarrada adag. Aan ku bilowno filtarrada fudud. Markaad la shaqaynayso filtarrada, waa inaad had iyo jeer haysataa hal saf oo sare oo loo isticmaalo calaamadaha. Shuruud ma aha in safkan la haysto, laakiin waxa ay ka dhigaysa ku shaqaynta filtarrada waxoogaa fudud.

Xagga sare, waxaan hayaa xog been abuur ah waxaanan rabaa in aan shaandhaynta ku sameeyo tiirka Magaalada . ^(City)Gudaha Excel , tani runtii way fududahay in la sameeyo. Horey u soco oo dhagsii xogta^(Data) tab ee ribbon ka dibna dhagsii badhanka Filter . Uma baahnid inaad doorato xogta xaashida ama guji safka koowaad midkoodna.

Markaad gujiso Filter , tiir kasta oo ku yaal safka hore wuxuu si toos ah u yeelan doonaa badhanka hoos udhaca yar oo lagu daray midigta.

Hadda hore u soco oo dhagsii fallaadha hoos u dhaca ee tiirka Magaalada . ^(City)Waxaad arki doontaa dhowr doorasho oo kala duwan, kuwaas oo aan hoos ku sharxi doono.

Xagga sare, waxaad si degdeg ah u kala saari kartaa dhammaan safafka qiyamka ku jira tiirka Magaalada . ^(City)Ogsoonow marka aad kala soocdo xogta, waxay dhaqaajin doontaa safka oo dhan, ma aha oo kaliya qiimayaasha ku jira tiirka Magaalada . ^(City)Tani waxay hubin doontaa in xogtaadu ay ahaan doonto sidii hore.

Sidoo kale, waa fikrad wanaagsan inaad ku darto tiir xagga hore ah oo la yiraahdo ID oo ka tirso hal ilaa safafka aad ku leedahay xaashida shaqada. Sidan, waxaad mar walba ku kala sooci kartaa tiirka aqoonsiga oo aad xogtaada dib ugu soo celin kartaa sidii ay markii hore ahayd, haddii ay taasi muhiim kuu tahay.

Sida aad arki karto, dhammaan xogta ku jirta xaashida xaashida hadda waa la kala soocay iyadoo lagu salaynayo qiyamka tiirka Magaalada . ^(City)Ilaa hadda, ma jiraan wax saf ah oo qarsoon. Hadda aan eegno sanduuqyada hubinta ee ku yaal xagga hoose ee wada hadalka filter. Tusaalahayga, waxaan ku leeyahay tiirka Magaalada^(City) saddex qiyam oo gaar ah saddexdaasna waxay ka soo muuqanayaan liiska.

Horaan u sii socday oo laba magaalo oo aan hubin ayaan ka tagay mid la hubiyay. Hadda waxaan hayaa 8 saf oo xog ah oo muujinaya inta soo hartayna waa qarsoon yihiin. Waxaad si fudud u sheegi kartaa inaad eegayso xogta la sifeeyay haddii aad hubiso lambarrada safafka ee bidixda fog. Iyadoo ay ku xiran tahay inta saf ee qarsoon, waxaad arki doontaa dhowr xariiq oo toosan oo dheeri ah, midabka tirooyinkana wuxuu noqon doonaa buluug.

Hadda aan nidhaahno waxaan rabaa inaan ku shaandheeyo tiirka labaad si aan u yaraynno tirada natiijooyinka. Tiirka C, waxaan haystaa tirada guud ee xubnaha qoys kasta waxaanan rabaa in aan kaliya arko natiijooyinka qoysaska ka kooban laba xubnood.

Horey u soco oo dhagsii falaarta hoos udhaca ee Column C oo waxaad arki doontaa isla sanduuqyada hubinta ee qiime kasta oo gaar ah ee tiirka. Si kastaba ha ahaatee, kiiskan, waxaan rabnaa in aan gujino Nambarada Filters^{(Number Filters)} ka dibna riix Greater Than . Sida aad arki karto, waxaa jira farabadan oo xulashooyin kale ah sidoo kale.

Wadahadal cusub ayaa soo baxaya oo halkan waxaad ku qori kartaa qiimaha shaandhada. Waxa kale oo aad ku dari kartaa wax ka badan hal shuruudood oo leh shaqo IYO ama AMA Waxaad odhan kartaa waxaad doonaysaa safaf qiimuhu ka weyn yahay 2 oo aan la mid ahayn 5 tusaale ahaan.

Hadda waxaan hoos ugu dhacay 5 saf oo xog ah: qoysaska kaliya ee ka yimid New Orleans iyo 3 ama ka badan oo xubnood. Fudud^(Easy) ku filan? Ogsoonow in aad si fudud u nadiifin karto shaandhada tiirka adiga oo gujinaya hoos u dhaca ka bacdina gujinaya ka nadiifi Filter-ka "Magaca Qaybta^{(Clear Filter From “Column Name”)} "

Markaa taasi waxay ku saabsan tahay filtarrada fudud ee Excel . Aad bay u fududahay in la isticmaalo, natiijaduna waa mid toos ah oo toos ah. Hadda aan eegno filtarrada adag ee isticmaalaya wada hadalka filtarrada sare^(Advanced) .

Ku samee filtarrada sare ee Excel

Haddii aad rabto in aad abuurto filtarrada horumarsan, waa inaad isticmaashaa wada-hadalka shaandhada sare^(Advanced) . Tusaale ahaan, aan nidhaahno waxaan rabay inaan arko dhammaan qoysaska ku nool New Orleans oo leh in ka badan 2 xubnood oo qoyskooda ah AMA^(OR) dhammaan qoysaska Clarksville oo leh in ka badan 3 xubnood oo qoyskooda ah iyo^(AND) kuwa leh ciwaanka emailka ee dhammaanaya .EDU . Hadda taas kuma samayn kartid filtar fudud.

Si tan loo sameeyo, waxaan u baahanahay in aan dejino xaashida Excel wax yar oo ka duwan. ^(Excel)Horey u soco oo geli dhawr saf oo ka sarreeya xogtaada oo koobiy calaamadaha ciwaanka si sax ah safka koowaad sida hoos ka muuqata.

Hadda waa kan sida filtarrada sare u shaqeeyaan. Waa inaad marka hore ku qortaa shuruudahaaga tiirarka sare ka dibna riix batoonka Advanced ee hoos yimaada Kala ^(Advanced)- ^(Data)soocida & Filter^{( Sort & Filter)} tab xogta .

Haddaba maxaynu ku qori karnaa unugyadaas? OK, haddaba aan ku bilowno tusaalahayaga. Waxaan kaliya dooneynaa inaan aragno xogta New Orleans ama Clarksville , markaa aan ku qorno kuwa unugyada E2 iyo E3.

Markaad ku qorto qiyamka safafka kala duwan, waxay la macno tahay AMA. Hadda waxaan rabnaa qoysaska New Orleans ee ka badan laba xubnood iyo qoysaska Clarksville oo ka badan 3 xubnood. Si tan loo sameeyo, ku qor >2 gudaha C2 iyo >3 gudaha C3.

Maadaama > 2 iyo New Orleans ay ku jiraan isku saf, waxay noqon doontaa hawlwadeen AND . Isla sidaas oo kale waa runta safka 3 ee sare. Ugu dambeyntii, waxaan rabnaa kaliya qoysaska leh .EDU ciwaanka emailka ee dhamaanaya. Si tan loo sameeyo, kaliya ku qor *.edu labada D2 iyo D3. Astaanta * macnaheedu waa tiro kasta oo jilayaal ah.

Markaad taas sameyso, guji meel kasta oo ka mid ah xogtaada ka dibna dhagsii badhanka Advanced . Goobta List Rang e ayaa si toos ah u ogaan doonta xogtaada tan iyo markii aad gujisay ka hor intaadan gujin badhanka Sare^(Advanced) . Hadda dhagsii badhanka yar ee midigta ee badhanka kala duwanaanta Shuruudaha^{(Criteria range)} .

Wax kasta ka dooro^(Select) A1 ilaa E3 ka dibna ku dhufo isla badhanka mar kale si aad dib ugu noqoto wada hadalka Filter Advanced^{(Advanced Filter)} . Guji OK^{(Click OK)} oo xogtaada hadda waa in la sifeeyaa!

Sida aad arki karto, hadda waxaan haystaa kaliya 3 natiijooyin oo u dhigma dhammaan shuruudahaas. Ogsoonow in calaamadaha kala duwan ee shuruudaha ay tahay inay si sax ah u wajahaan calaamadaha kaydka xogta si ay tani u shaqeyso.

Sida cad waxaad abuuri kartaa su'aalo aad u dhib badan adoo isticmaalaya habkan, markaa la ciyaar si aad u hesho natiijooyinka aad rabto. Ugu dambayntii, aan ka hadalno ku-dhaqanka hawlaha guud ee xogta la sifeeyey.

Soo koobida Xogta La Sifeeyay

Hadda aan nidhaahno waxaan rabaa inaan soo koobo tirada xubnaha qoyska ee xogtayda la shaandheeyay, sideen ku heli lahaa taas? Hagaag, aan nadiifinno shaandhayntayada annaga oo gujinaya badhanka Clear ee ribbon. Ha werwerin, aad bay u fududahay in mar labaad la mariyo shaandhada horumarsan adigoo si fudud u gujinaya badhanka Sare^(Advanced) oo mar labaad gujinaya OK.

Gunta hoose ee xogtayada, aynu ku darno unug la yidhaahdo Total ka dibna aynu ku darno hawl wadareed si aynu u soo koobno ​​guud ahaan xubnaha qoyska. Tusaalahayga, hadda waxaan ku qoray =SUM(C7:C31) .

Markaa haddii aan eego dhammaan qoysaska, waxaan haystaa 78 xubnood. Hadda aan horay u socono oo dib u codsado shaandhayntayada Sare oo arag waxa dhaca.^(Advanced)

Hooy! Halkii laga muujin lahaa lambarka saxda ah, 11, waxaan weli arkaa wadarta guud waa 78! Waa maxay sababtu? Hagaag, shaqada SUM ma iska indho tirto safafka qarsoon, sidaas darteed waxay wali samaynaysaa xisaabinta iyadoo la adeegsanayo dhammaan safafka. Nasiib wanaag, waxaa jira dhowr hawlood oo aad isticmaali karto si aad iska indhatirto safafka qarsoon.

Ka hore waa SUBTOTAL . Kahor intaanan isticmaalin mid ka mid ah hawlahan gaarka ah, waxaad u baahan doontaa inaad nadiifiso shaandhadaada ka dibna ku qor shaqada.

Marka shaandhada la nadiifiyo, horay u soco oo ku qor =SUBTOTAL( oo waa inaad aragto sanduuqa hoos u dhaca oo ka muuqda farabadan ikhtiyaari ah. Adigoo isticmaalaya shaqadan, waxaad marka hore dooranaysaa nooca shaqada soo koobida ee aad rabto inaad isticmaasho lambarka.

Tusaalahayaga, waxaan rabaa in aan isticmaalo SUM , markaa waxaan ku qori doonaa lambarka 9 ama kaliya ka dhufo hoosta. Kadibna ku qor hakad oo dooro tirada unugyada.

Markaad riixdo geli, waa inaad aragto qiimaha 78 wuxuu la mid yahay kii hore. Si kastaba ha ahaatee, haddii aad hadda mar kale marso shaandhada, waxaan arki doonaa 11!

Aad u fiican! Taasi waa waxa aan rabno. Hadda waxaad hagaajin kartaa filtarradaada qiimuhuna wuxuu had iyo jeer ka tarjumayaa kaliya safafka hadda muuqda.

Hawsha labaad ee si qurux badan u shaqeysa si la mid ah kan SUBTOTAL waa URUR^(AGGREGATE) . Waxa kaliya ee ay ku kala duwan yihiin waa in uu jiro halbeeg kale oo ku jira shaqada AGGREGATE halkaas oo ay tahay inaad ku qeexdo inaad rabto inaad iska indhatirto safafka qarsoon.

Halbeegga koowaad waa shaqada isu-ururinta ee aad rabto inaad isticmaasho iyo sida SUBTOTAL , 9 waxay u taagan tahay shaqada SUM . Doorashada labaad waa meesha aad ku qorto 5 si aad u iska indhatirto safafka qarsoon. Halbeegga u dambeeya waa isku mid waana tirada unugyada.

Waxa kale oo aad akhrin kartaa maqaalkayga ku saabsan hawlaha kooban si aad u barato sida loo isticmaalo shaqada AGGREGATE^{(use the AGGREGATE function)}  iyo hawlaha kale sida MODE , MEDIAN , AVERAGE , iwm si faahfaahsan.

Waxaan rajeyneynaa, maqaalkani wuxuu ku siinayaa meel fiican oo aad ku abuurto oo aad isticmaasho filtarrada gudaha Excel . Haddii aad wax su'aalo ah qabtid, xor u noqo inaad faallo ka bixiso. Ku raaxayso!

How to Filter Data in Excel

I recently wrote an article on how to use summary functions in Excel to easily summarize large amounts of data, but that article took into account all data on the worksheet. What if you only want to look at a subset of data and summarize the subset of data?

In Excel, you can create filters on columns that will hide rows that do not match your filter. In addition, you can also use special functions in Excel to summarize data using only the filtered data.

In this article, I’ll walk you through the steps for creating filters in Excel and also using built-in functions to summarize that filtered data.

Create Simple Filters in Excel

In Excel, you can create simple filters and complex filters. Let’s start off with simple filters. When working with filters, you should always have one row at the top that is used for labels. It’s not a requirement to have this row, but it makes working with filters a bit easier.

Above, I have some fake data and I want to create a filter on the City column. In Excel, this is really easy to do. Go ahead and click on the Data tab in the ribbon and then click on the Filter button. You don’t have to select the data on the sheet or click in the first row either.

When you click on Filter, each column in the first row will automatically have a small dropdown button added at the very right.

Now go ahead and click on the dropdown arrow in the City column. You’ll see a couple of different options, which I will explain below.

At the top, you can quickly sort all the rows by the values in the City column. Note that when you sort the data, it will move the entire row, not just the values in the City column. This will ensure that your data remains intact just as it was before.

Also, it’s a good idea to add a column at the very front called ID and number it from one to however many rows you have in your worksheet. This way, you can always sort by the ID column and get your data back in the same order it was originally, if that is important to you.

As you can see, all the data in the spreadsheet is now sorted based on the values in the City column. So far, no rows are hidden. Now let’s take a look at the checkboxes at the bottom of filter dialog. In my example, I have only three unique values in the City column and those three show up in the list.

I went ahead and unchecked two cities and left one checked. Now I only have 8 rows of data showing and the rest are hidden. You can easily tell you are looking at filtered data if you check the row numbers at the far left. Depending on how many rows are hidden, you’ll see a few extra horizontal lines and the color of the numbers will be blue.

Now let’s say I want to filter on a second column to further reduce the number of results. In column C, I have the total number of members in each family and I want to only see the results for families with more than two members.

Go ahead and click on the dropdown arrow in Column C and you’ll see the same checkboxes for each unique value in the column. However, in this case, we want to click on Number Filters and then click on Greater Than. As you can see, there are a bunch of other options too.

A new dialog will pop up and here you can type in the value for the filter. You can also add more than one criteria with an AND or OR function. You could say you want rows where the value is greater than 2 and not equal to 5, for example.

Now I’m down to just 5 rows of data: families only from New Orleans and with 3 or more members. Easy enough? Note that you can easily clear a filter on a column by clicking on the dropdown and then clicking the Clear Filter From “Column Name” link.

So that’s about it for simple filters in Excel. They are very easy to use and the results are pretty straight-forward. Now let’s take a look at complex filters using the Advanced filters dialog.

Create Advanced Filters in Excel

If you want to create more advanced filters, you have to use the Advanced filter dialog. For example, let’s say I wanted to see all families that live in New Orleans with more than 2 members in their family OR all families in Clarksville with more than 3 members in their family AND only the ones with a .EDU ending email address. Now you can’t do that with a simple filter.

To do this, we need to setup the Excel sheet a little differently. Go ahead and insert a couple of rows above your set of data and copy the heading labels exactly into the first row like shown below.

Now here is how advanced filters work. You have to first type your criteria into the columns at the top and then click the Advanced button under Sort & Filter on the Data tab.

So what exactly can we type into those cells? OK, so let’s start with our example. We only want to see data from New Orleans or Clarksville, so let’s type those into cells E2 and E3.

When you type values on different rows, it means OR. Now we want New Orleans families with more than two members and Clarksville families with more than 3 members. To do this, type in >2 in C2 and >3 in C3.

Since >2 and New Orleans are on the same row, it will be an AND operator. The same is true for row 3 above. Finally, we want only the families with .EDU ending email address. To do this, just type in *.edu into both D2 and D3. The * symbol means any number of characters.

Once you do that, click anywhere in your dataset and then click on the Advanced button. The List Range field will automatically figure out your dataset since you clicked into it before clicking the Advanced button. Now click on the small little button at the right of the Criteria range button.

Select everything from A1 to E3 and then click on the same button again to get back to the Advanced Filter dialog. Click OK and your data should now be filtered!

As you can see, now I have only 3 results that match all of those criteria. Note that the labels for the criteria range have to match exactly with the labels for the dataset in order for this to work.

You can obviously create a lot more complicated queries using this method, so play around with it to get your desired results. Finally, let’s talk about applying summation functions to filtered data.

Summarizing Filtered Data

Now let’s say I want to sum up the number of family members on my filtered data, how would I got about doing that? Well, let’s clear our filter by clicking on the Clear button in the ribbon. Don’t worry, it’s very easy to apply the advanced filter again by simply clicking on the Advanced button and clicking OK again.

At the bottom of our dataset, let’s add a cell called Total and then add a sum function to sum up the total family members. In my example, I just typed =SUM(C7:C31).

So if I look at all families, I have 78 members total. Now let’s go ahead and reapply our Advanced filter and see what happens.

Whoops! Instead of showing the correct number, 11, I still see the total is 78! Why is that? Well, the SUM function does not ignore hidden rows, so it is still doing the calculation using all the rows. Luckily, there are a couple of functions you can use to ignore hidden rows.

The first is SUBTOTAL. Before we use any of these special functions, you will want to clear your filter and then type in the function.

Once the filter is cleared, go ahead and type in =SUBTOTAL( and you should see a dropdown box appear with a bunch of options. Using this function, you first pick the type of summation function you want to use using a number.

In our example, I want to use SUM, so I would type in the number 9 or just click on it from the dropdown. Then type a comma and select the range of cells.

When you press enter, you should see the value of 78 is the same as previously. However, if you now apply the filter again, we will see 11!

Excellent! That’s exactly what we want. Now you can adjust your filters and the value will always reflect only the rows that are currently showing.

The second function that works pretty much exactly the same as the SUBTOTAL function is AGGREGATE. The only difference is that there is another parameter in the AGGREGATE function where you have to specify that you want to ignore hidden rows.

The first parameter is the summation function you want to use and as with SUBTOTAL, 9 represents the SUM function. The second option is where you have to type in 5 to ignore hidden rows. The last parameter is the same and is the range of cells.

You can also read my article on summary functions to learn how to use the AGGREGATE function and other functions like MODE, MEDIAN, AVERAGE, etc. in more detail.

Hopefully, this article gives you a good starting point for creating and using filters in Excel. If you have any questions, feel free to post a comment. Enjoy!

Related posts

• Jadwalka xogtaada Excel

• Adeegso Excel Mobile's cusub ee shaqada "Ka Geli Xogta Sawirka".

• Isticmaal Hawlaha Kooban si aad u soo koobto Xogta ku jirta Excel

• U isticmaal Excel Qalab ahaan si aad xogta uga koobbiso Shabakadda

• Kudar Khadka Dib-u-Celinta Toosan ee Qorshaha Kala firdhisan ee Excel

• Sida Loogu Beddelayo Inta u dhaxaysa Waraaqaha Shaqada ee Excel

• Geli xaashida shaqada ee Excel Word Doc

• Sida Taariikhaha Looga Jaro Excel

• Sida Loo Isticmaalo Shaqada YEARFRAC gudaha Excel

• Sida loo sameeyo Histogram gudaha Excel

• Sida loogu daro faallooyinka unugga Xaashida shaqada ee Excel

• Isku xidh Unugyada u dhexeeya Xaashiyaha iyo Buugaagta Shaqada ee Excel

• Sida loo isticmaalo Excel AutoRecover iyo AutoBackup Features

• Sida Looga Daro Baararka Cilada ee Excel

• Sida Si Toos ah Loogu Soo Kabto Dukumeenti Word OneDrive

• Sida loo daro oo loo daabaco sawirada asalka ah ee Excel

• Sida loo qariyo xaashiyaha, unugyada, tiirarka, iyo qaaciidooyinka gudaha Excel

• Sida loo Helo Qiimaha Iswaafajinta gudaha Excel

• Sida Loo Bedelo Taariikhda Kooxda Microsoft

• U qaabeeya Unugyada iyadoo la isticmaalayo Habaynta Shuruudaha ah ee Excel