Kuwa sida joogtada ah u isticmaala Excel , tirada qaababka iyo shaqooyinka lagu soo koobo laguna maamulo xogta waa wax lala yaabo. Excel run ahaantii waxaa isticmaala qof kasta: laga bilaabo ardayda fasalka maaliyadeed ilaa maareeyayaasha maalgelinta ee Wall Street . Aad bay u awood badan tahay, laakiin isla markaana aad u fudud.

Kuwa hadda bilaabaya Excel , mid ka mid ah kooxda ugu horreysa ee hawlaha ay tahay inaad barato waa hawlaha kooban. Kuwaas waxaa ka mid ah SUM ,  celcelis^(AVERAGE) ahaan , MAX ,  MIN , MODE , MEDIAN , COUNT , STDEV , WEYN^(LARGE) , YAR^(SMALL) iyo wadar guud^(AGGREGATE) . Hawlahan waxaa sida ugu wanaagsan loogu isticmaalo xogta tirada.

Maqaalkan, waxaan ku tusi doonaa sida loo sameeyo qaacido oo aad shaqada geliso xaashida xaashida ee Excel . Hawl kasta oo Excel ah waxay qaadataa doodo^(arguments) , kuwaas oo ah qiyamka hawluhu u baahan yihiin si loo xisaabiyo wax soo saarka.

Fahamka qaaciidooyinka & Hawlaha

Tusaale ahaan, haddii aad u baahan tahay inaad ku darto 2 iyo 2, shaqadu waxay noqon doontaa SUM  iyo dooduhu waxay noqon doonaan tirooyinka 2 iyo 2 . Sida caadiga ah tan waxaan u qornaa sida 2 + 2, laakiin Excel waxaad u qori doontaa sida =SUM(2+2) . Halkan waxa aad ka arki kartaa natiijadan fudud ee lagu daray laba tirooyin sax ah.

Inkasta oo aysan jirin wax khalad ah oo ku jira qaacidada this, runtii maahan lagama maarmaan. Waxaad ku qori kartaa =2+2 gudaha Excel taasina sidoo kale way shaqayn doontaa. In Excel , marka aad isticmaasho shaqo sida SUM , waxa ay macno dheeraad ah u isticmaalaan doodaha. Shaqada SUM , Excel waxay filaysaa ugu yaraan laba doodood, kuwaas oo tixraac u noqon doona unugyada xaashida.

Sideen u tixraacnaa unug ku jira qaacidada Excel ? Hagaag, taasi way fududahay. Saf kastaa wuxuu leeyahay lambar, tiir kastaana wuxuu leeyahay xaraf. A1 waa unugga ugu horreeya ee xaashida faafinta ee bidixda sare. B1 waxa ay noqon doontaa unugga dhanka midig ee A1. A2 waa unugga si toos ah uga hooseeya A1. Fudud^(Easy) ku filan sax?

Kahor intaanan qorin qaacidadayada cusub, aan ku darno xoogaa xog ah tiirarka A iyo B si aan ula shaqayno. Hore u soco oo ku qor lambarrada random-ka A1 ilaa A10 iyo B1 ilaa B10 si aad xogtayada u dejiso. Hadda u gudub D1 oo ku qor =SUM(A1,B1) . Waa inaad aragto natiijadu waxay si fudud u tahay qiimaha A1 + B1 .

Waxaa jira dhowr shay oo la xuso marka aad ku qorayso qaacido gudaha Excel . Marka hore, waxaad ogaan doontaa marka aad ku qorto sanduuqa furitaanka ee ugu horreeya ( ka dib magaca shaqada, Excel wuxuu si toos ah kuu sheegi doonaa waxa doodaha shaqada ay qaadanayso. Tusaalahayaga, waxay muujinaysaa nambarka 1^(number1) , nambarka^(number2) 2 , iwm. Waxaad ku kala saartaa doodaha dhibcaha . Shaqada gaarka ah waxay qaadan kartaa tiro aan xadidnayn oo qiyam ah maadaama ay taasi tahay sida shaqada SUM u shaqeyso.

Marka labaad, ama waxaad ku qori kartaa tixraaca unugga gacanta (A1) ama waxaad riixi kartaa unugga A1 ka dib markaad ku dhejiso khadadka furan. Excel sidoo kale wuxuu iftiimin doonaa unugga isla midabka tixraaca unugga si aad si sax ah u arki karto qiimayaasha u dhigma. Markaa hal saf ayaan isku soo koobnay, laakiin sidee ayaan dhammaan safafka kale u soo wada koobi karnaa innagoo aan dib u qorin qaacidada ama koobiyeynta iyo dhajinta? Nasiib wanaag, Excel tani way fududahay.

U dhaqaaq curcurkaaga jiirka dhinaca midig ee hoose ee unugga D1 waxaadna ogaan doontaa inuu ka beddelayo iskutallaab cad una beddelay calaamad madow oo lagu daray.

Hadda dhagsii oo hoos u qabo jiirkaaga badhanka Hoos-u-jiid cursorka ilaa safka u dambeeya xogta iyo ka dibna sii daa dhamaadka.

Excel waa caqli ku filan si uu u ogaado in qaacidadu ay tahay inay beddesho oo ay ka tarjumayso qiyamka safafka kale halkii ay ku tusin lahayd isla A1 + B1 oo dhan. Bedelkeeda, waxaad arki doontaa A2+B2 , A3+B3 iyo wixii la mid ah.

Waxa kale oo jirta hab kale oo loo isticmaalo SUM oo sharxaya fikrad kale oo ka dambaysa doodaha gudaha Excel . Aynu nidhaahno waxaan rabnay inaan soo koobno ​​dhammaan qiyamka A1 ilaa A12 , markaa sidee baan u soconaa? Waxaan ku qori karnaa wax sida =SUM(A1, A2, A3, etc) , laakiin taasi waqti badan ayey qaadataa. Habka ugu fiican ayaa ah in la isticmaalo qiyaasta Excel .

Isku soo wada duuboo A1 ilaa A12 , waxa kaliya ee ay tahay in aan sameyno waa nooca =SUM(A1:A12) oo leh xiidan kala sooca labada tixraac ee unugga halkii uu ka ahaan lahaa comma. Xitaa waxaad ku qori kartaa wax sida =SUM(A1:B12) waxayna soo koobaysaa dhammaan qiimayaasha A1 ilaa A12 iyo B1 ilaa B12 .

Tani waxay ahayd dulmar aad u aasaasi ah oo ku saabsan sida loo isticmaalo hawlaha iyo qaaciidooyinka gudaha Excel , laakiin way kugu filan tahay inaad bilowdo isticmaalka dhammaan hawlaha xog-ururinta.

Hawlaha Kooban

Isticmaalka xog isku mid ah, waxaanu isticmaali doonaa inta hadhay ee hawlaha kooban si aan u aragno nooca tirooyinka aan xisaabin karno. Aan ku bilowno COUNT iyo COUNTA .

Halkan waxaan ku soo geliyey shaqada COUNT D2 iyo shaqada COUNTA ee E2, anigoo isticmaalaya A2:A12  sida xogta loo dejiyay labada hawlood. Waxaan sidoo kale u beddelay qiimihii A9 xarafka qoraalka hello si aan u muujiyo farqiga. COUNTA^(COUNT) waxa ay tirisaa unugyada nambarada leh halka COUNTA ay tiriso unugyo ka kooban qoraal iyo nambaro. Labada hawloodba ma xisaabiyaan unugyo bannaan. Haddii aad rabto inaad tiriso unugyo bannaan, isticmaal shaqada COUNTBLANK .

Waxa soo socda waa celceliska^(MEDIAN) , MEDIAN^(AVERAGE) iyo MODE . Celcelis ahaan waa iskiis u sharaxa, dhexdhexaadintu waa lambarka dhexe ee tirooyinka iyo qaabku waa tirada ama tirooyinka ugu badan ee tirooyinka. Noocyada cusub ee Excel , waxaad haysataa MODE.SNGL  iyo MODE.MULT sababtoo ah waxaa jiri kara wax ka badan hal lambar oo ah tirada ugu badan ee tirooyin ah. Waxaan u isticmaalay B2:B12 inta u dhaxaysa tusaalaha hoose.

Sii socoshada , waxaan xisaabin karnaa MIN , MAX iyo STDEV ee tirada tirooyinka B2: B12 . Shaqada STDEV waxay xisaabin doontaa sida ballaaran ee qiyamka loogu kala firdhiyey qiimaha celceliska. Noocyada cusub ee Excel , waxaad haysataa STDEV.P iyo STDEV.S , kuwaas oo xisaabiya iyadoo lagu saleynayo dadweynaha oo dhan ama ku salaysan muunad, siday u kala horreeyaan.

Ugu dambeyntii, laba hawlood^(SMALL) oo kale oo faa'iido leh waa WEYN iyo YAR^(LARGE) . Waxay qaataan laba doodood: kala duwanaanta unugga iyo k-th qiimaha ugu weyn ee aad rabto inaad soo noqoto. Markaa haddii aad rabto qiimaha labaad ee ugu weyn shaxda, waxaad isticmaali doontaa 2 doodda labaad, 3 lambarka saddexaad ee ugu weyn, iwm. YARYAR^(SMALL) waxay u shaqeysaa si la mid ah, laakiin waxay ku siinaysaa lambarka k-th ugu yar.

Ugu dambeyntii, waxaa jira shaqo loo yaqaan ' AGGREGATE ' , kaas oo kuu ogolaanaya inaad isticmaasho mid ka mid ah hawlaha kale ee aan kor ku soo sheegnay, laakiin sidoo kale wuxuu kuu ogolaanayaa inaad sameyso waxyaabo ay ka mid yihiin iska indho-tirka safafka qarsoon, iska indho-tir qiyamka qaladka, iwm. Waxaa laga yaabaa inaadan u baahnayn inaad isticmaasho inta badan. , laakiin wax badan oo ku saabsan halkan ayaad ka baran kartaa^{(learn more about it)} haddii aad u baahato.

Markaa taasi waa dulmar aasaasi ah oo ku saabsan qaar ka mid ah shaqooyinka soo koobida ee ugu caansan Excel . Haddii aad wax su'aalo ah qabtid, xor u noqo inaad faallo ka bixiso. Ku raaxayso!

Use Summary Functions to Summarize Data in Excel

For those who use Excel regularly, the number of built-in formulаs and functions to summarize and manіpulate data is staggering. Excel is literally used by everуone: from students in a finanсial clasѕ to hedgе fund managеrs on Wаll Street. Іt’s extremely powerful, but at the samе timе very simple.

For those just getting started with Excel, one of the first group of functions you should learn are the summary functions. These include SUM, AVERAGE, MAX, MIN, MODE, MEDIAN, COUNT, STDEV, LARGE, SMALL and AGGREGATE. These functions are best used on numerical data.

In this article, I’ll show you how to create a formula and insert the function into an Excel spreadsheet. Each function in Excel takes arguments, which are the values the functions needs to calculate an output.

Understanding Formulas & Functions

For example, if you need to add 2 and 2 together, the function would be SUM and the arguments would be the numbers 2 and 2. We normally write this as 2 + 2, but in Excel you would write it as =SUM(2+2). Here you can see the results of this simple addition of two literal numbers.

Even though there is nothing wrong with this formula, it really isn’t necessary. You could just type =2+2 in Excel and that would work also. In Excel, when you use a function like SUM, it makes more sense to use arguments. With the SUM function, Excel is expecting at least two arguments, which would be references to cells on the spreadsheet.

How do we reference a cell inside the Excel formula? Well, that’s pretty easy. Every row has a number and every column has a letter. A1 is the first cell on the spreadsheet at the top left. B1 would be the cell to the right of A1. A2 is the cell directly below A1. Easy enough right?

Before we write our new formula, let’s add some data in columns A and B to work with. Go ahead and type random numbers from A1 to A10 and B1 to B10 for our data set. Now go to D1 and type in =SUM(A1,B1). You should see the result is simply the value of A1 + B1.

There are a couple of things to note while typing a formula in Excel. Firstly, you’ll notice that when you type the first opening parenthesis ( after the function name, Excel will automatically tell you what arguments that function takes. In our example, it shows number1, number2, etc. You separate arguments with commas. This particular function can take an infinite number of values since that is how the SUM function works.

Secondly, either you can type in the cell reference manually (A1) or you can click on the cell A1 after you typed the open parenthesis. Excel will also highlight the cell in the same color as the cell reference so you can see the corresponding values exactly. So we summed one row together, but how can we sum all the other rows without typing the formula again or copying and pasting? Luckily, Excel makes this easy.

Move your mouse cursor to the bottom right corner of cell D1 and you’ll notice it changes from a white cross to a black plus sign.

Now click and hold your mouse button down. Drag the cursor down to the last row with the data and then let go at the end.

Excel is smart enough to know that the formula should change and reflect the values in the other rows rather than just showing you the same A1 + B1 all the way down. Instead, you’ll see A2+B2, A3+B3 and so on.

There is also another way to use SUM that explains another concept behind arguments in Excel. Let’s say we wanted to sum up all the values from A1 to A12, then how would we go about it? We could type something like =SUM(A1, A2, A3, etc), but that is very time consuming. A better way is to use an Excel range.

To sum A1 to A12, all we have to do is type =SUM(A1:A12) with a colon separating the two cell references instead of a comma. You could even type something like =SUM(A1:B12) and it will sum all values in A1 thru A12 and B1 thru B12.

This was a very basic overview of how to use functions and formulas in Excel, but it’s enough so that you can start using all of the data summation functions.

Summary Functions

Using the same set of data, we’re going to use the rest of the summary functions to see what kind of numbers we can calculate. Let’s start with the COUNT and COUNTA functions.

Here I have entered the COUNT function into D2 and the COUNTA function into E2, using A2:A12 range as the data set for both functions. I also changed the value in A9 to the text string hello to show the difference. COUNT only counts the cells that have numbers whereas COUNTA counts cells that contain text and numbers. Both functions do not count blank cells. If you want to count blank cells, use the COUNTBLANK function.

Next up are the AVERAGE, MEDIAN and MODE functions. Average is self-explanatory, median is the middle number in a set of numbers and mode is the most common number or numbers in a set of numbers. In newer versions of Excel, you have MODE.SNGL and MODE.MULT because there could be more than one number that is the most common number in a set of numbers. I used B2:B12 for the range in the example below.

Moving on, we can calculate the MIN, MAX and STDEV for the set of numbers B2:B12. The STDEV function will calculate how widely values are dispersed from the average value. In newer versions of Excel, you have STDEV.P and STDEV.S, which calculates based on the entire population or based on a sample, respectively.

Finally, another two useful functions are LARGE and SMALL. They take two arguments: the cell range and the k-th largest value you want to return. So if you want the second largest value in the set, you would use 2 for the second argument, 3 for the third largest number, etc. SMALL works the same way, but gives you the k-th smallest number.

Lastly, there is a function called AGGREGATE, which allows you to apply any of the other functions mentioned above, but also lets you do things like ignore hidden rows, ignore error values, etc. You probably won’t need to use it that often, but you can learn more about it here in case you do need it.

So that’s a basic overview of some of the most common summary functions in Excel. If you have any questions, feel free to post a comment. Enjoy!

Related posts

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

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

• Jadwalka xogtaada Excel

• Sida loo shaandheeyo xogta gudaha Excel

• Adeegso Excel si aad u ogaato dulsaarka waxtarka leh ee ka imanaya heerka dulsaarka magaca ah

• Sida loo Kooxeeyo Waraaqaha Shaqada ee Excel

• Sida loo sameeyo garaafka fudud ama jaantuska gudaha Excel

• Sida Looga Daro Baararka Cilada ee Excel

• Sida Loo Fahmo Maxaa-Haddii Falanqaynta Microsoft Excel

• Sida loo sameeyo oo loo isticmaalo qaabka MLA ee Microsoft Word

• Sida loo isticmaalo VLOOKUP gudaha Excel

• Sida loola socdo isbedelada Excel

• Sida loo isticmaalo Sparklines gudaha Excel

• Sida Loo Sameeyo Liiska Hoos-u-dhaca ee Excel

• Sida loo saaro, loo jebiyo, ama loo jebiyo erayga sirta ah ee Excel XLS ee la iloobay

• Sida loo xisaabiyo kala duwanaanshaha Excel

• Sida loo abuuro Gantt Charts gudaha Microsoft Excel

• Sida loogu daro faallooyinka unugga Xaashida shaqada ee Excel

• Sida Taariikhaha Looga Jaro Excel

• Geli xaashida shaqada ee Excel Word Doc