Microsoft Excel waxay kuu fududaynaysaa habaynta, soo bandhigida, iyo falanqaynta xogta adoo isticmaalaya jaantusyo kala duwan. Shaxda gaarka ah ee xoogga badan waa sanduuqa iyo barxadda whiskerka (sidoo kale loo yaqaan goob sanduuq), loogu talagalay in lagu muujiyo qaybinta qiyamka xogta.

Maqaalkan, waxaan ku dabooli doonaa sida aad u abuuri karto sanduuq sanduuqa Microsoft Excel , oo daboolaya labadaba Excel 365 iyo noocyadii hore ee kuwa aan weli kor u qaadin.

Shirqoolkii sanduuqa: maxay yihiin?

Sawirka sanduuqa waa hab lagu soo bandhigo xogta kaa caawinaysa aragtida tayada tirakoobka sida faafinta iyo kala duwanaanta xogta. Waxay ku tusinaysaa hal bar (sanduuqa) oo laba u kala qaybsan, oo leh xariiqyo (shabaqa) ku fidsan labada dhinac ee sanduuqa.

Mid kasta oo ka mid ah walxahan ayaa sawiraya shanta lambar ee kooban ee xog tirooyin ah. Waxay u egyihiin sidan oo waxaa loo soo bandhigi karaa si toosan ama toosan:

Si aad u fahamto soo koobida shanta lambar, aynu eegno xog muunad ah.

25, 26, 28, 30, 32, 34, 37, 38

1. Ugu yaraan. Qiimaha ugu yar ee ku jira xogta. Tani waa barta dhamaadka bidix/hoose ee shafka bidix/hoose.

1. Afarta hore. Tani waa qiimaha lagu hoos helayo 25% dhibcaha xogta.
2. Afarta labaad. Tani waa dhexdhexaadiyaha. Waxay u dhigantaa "qiimaha dhexe".
3. Saddexda rubuc. Tani waa qiimaha kor ku xusan kaas oo 75% dhibcaha xogta la helay.
4. Ugu badnaan Qiimaha ugu sarreeya ee xogta.

Sida loo sameeyo sanduuqa sanduuqa^{(Box Plot)} ee Excel 365

Xafiiska 365^{(Office 365)} , Microsoft Excel waxaa ku jira sanduuqyada sanduuqyada sida jaantuska jaantuska, taas oo sahlaysa in loo abuuro goob muuqaal ah xogtaada. Haddii aadan hubin sida loo isticmaalo Excel , marka hore baro aasaaska^{(learn the basics first)} .

Si aad u abuurto goob sanduuq:

1. Fur xaashi shaqo oo cusub oo geli xogtaada.
2. Dooro xogtaada adiga oo gujinaya oo jiidaya.

1. Ribbon ka, dooro Gelida^(Insert) tab.

4. Guji Geli Shaxda Tirakoobka^{(Click Insert Statistic Chart)} ka dib Sanduuqa^(Box) iyo Whiskerka^(Whisker) .

Fiiro gaar ah: Haddii aad geliso jaantus madhan, waxaad geli kartaa xogtaada dejisay adiga oo dooranaya shaxda Naqshadeynta^{(Chart Design)} tab oo guji Xulo Xogta^{(Select Data)} .

Excel wuxuu hadda abuuri doonaa sanduuq lafaha qaawan iyo jaantuska whiskerka. Si kastaba ha ahaatee, waxaad sii habayn kartaa shaxdan Excel si aad u muujiso xogtaada tirakoobka sida saxda ah ee aad u rabto.

Sida loo sameeyo sanduuqa Plot^{(Box Plot)} ee Excel 365

Excel wuxuu kuu ogolaanayaa inaad qaabeeyo naqshadeynta jaantuska sanduuqa siyaabo badan, laga bilaabo ku darista cinwaan si aad u bedesho dhibcaha xogta muhiimka ah ee la soo bandhigay.

Shaxda Naqshadeynta^{(Chart Design)} tab waxay kuu ogolaaneysaa inaad ku darto curiyeyaasha jaantuska (sida cinwaannada jaantuska, khadadka, iyo sumadaha), bedesho qaabka ama nooca jaantuska, oo aad bedesho midabka sanduuqa iyo shabaqyada adigoo isticmaalaya qaab jaantus ku dhex dhisan.

Qaabka tab wuxuu kuu oggolaanayaa inaad hagaajiso xulashada midabkaaga, ku darto qoraalka, oo aad ku darto saamaynta qaybaha shaxdaada .^(Format)

Si aad ugu darto walxo bandhig ah, midig-guji sanduuqa iyo barxadda whiskerka oo ka dooro Qaabka Xogta Taxanaha^{(Format Data Series)} liiska hoos-hoosaadka.

Doorashooyinka waxaa ka mid ah:

1. Muuji^(Show) dhibcaha gudaha Tani waxay soo bandhigaysaa dhammaan dhibcooyinka xogta shakhsi ahaaneed sida wareegyada gudaha rubucyada koowaad iyo saddexaad.
2. Muuji^(Show) dhibco ka baxsan. Tani waxay soo bandhigaysaa meelo ka baxsan (dhibcaha xogta aan caadiga ahayn kuwa sare ama hoose) sida goobo ka baxsan goobta.
3. Muuji^(Show) calamadaha dhexdhexaadka ah. Tani waxay muujinaysaa celceliska qiimaha iskutallaab ahaan shaxda dhexdeeda.
4. Muuji^(Show) khadka dhexdhexaadka ah. Tani waxay muujinaysaa xariiqda u dhaxaysa celceliska dhibcaha ee xogta badan.
5. Xisaabinta afar geesoodka ah. Haddii aad haysato tiro aan caadi ahayn oo dhibco xog ah, waxaad dooran kartaa inaad xisaabiso rubucyada adiga oo ku daraya ama ka saaraya dhexdhexaadiyaha. Xogta ka weyn, waa inaad isticmaashaa kala duwanaanta afar-geesoodka ah ee gaarka ah, iyo dejinta xogta yar habka dhexdhexaadinta loo dhan yahay ayaa guud ahaan ka saxsan.

Sida Loo Sameeyo Sanduuqa^(Box) iyo Qorsheynta Whisker^{(Whisker Plot)} ee Noocyadii Hore^{(Older Versions)} ee Excel

Sida noocyadii hore ee Excel^{(versions of Excel)} (oo ay ku jiraan Excel 2013 iyo Excel 2016 ) kuma jiraan qaab-dhismeedka sanduuqa iyo jaantuska, abuurista mid aad ayey u adag tahay.

Marka hore, xisaabi qiimayaashaada afarle ah adiga oo isticmaalaya qaacidooyinka soo socda oo samee shax:

1. Qiimaha ugu yar: MIN (kala duwanaanta unugyada)
2. Afarta hore: QUARTILE.INC (kala duwanaanta unugga,1)
3. Dhexdhexaad: QUARTILE.INC (kala duwanaanshaha unugyada, 2)
4. Saddexaad ee rubuc: QUARTILE.INC (kala duwanaanshaha unugyada, 3)
5. Qiimaha ugu badan: MAX (kala duwanaanta unugyada)

Xusuusin: Kala duwanaanta unugga, jiid oo dooro xogtaada.

Marka xigta, ku xisaabi farqiyada afar geesoodka ah ee miis gaar ah (kuwaasi waxay la xiriiraan dhererka santuuqa):

1. Qiimaha Q1
2. Dhexdhexaadka laga jaray Q1
3. Q3 oo laga jaray dhexdhexaadiyaha
4. Qiimaha ugu badan oo laga jaray Q3
5. Q1 laga jaray qiimaha ugu yar

Kadib waxaad samayn kartaa jaantus adoo isticmaalaya qiimayaashan:

1. Guji^(Click) tab ka dibna dooro Geli ^(Insert)Tiirka^{(Insert Column)} ama Shaxda Bar^{(Bar Chart)} .
2. Guji jaantuska tixda is dulsaaran^{(Click Stacked Column Chart)} . Haddii jaantusku aanu si sax ah u soo bixin, dooro tab Nakhshad Jaantuska ka dibna dhagsii ^{(Chart Design)}Switch Row/Column .

1. Midig ku dhufo^{(Right-click)} qaybta garaafka ee ka dhigan " Sanduuqa 1^{(Box 1)} - qarsoon" oo dhagsii Fill ka dibna riix No Buuxi^(Fill) .

Si aad ugu darto shafka sare:

1. Guji^(Click) sanduuqa sare oo dooro tab Naqshadeynta Shaxda .^{(Chart Design)}
2. Guji Kudar Shaxda Cunsurka.

1. Click Error Bars > More Error Bars Options .

1. Hoosta Tilmaamaha^(Direction) , guji Plus . Khaladaadka hoostiisa^{(Error Amount)} ku dhufo Custom > Specify Value .

1. Ku beddel^(Replace) qiimaha khaladka togan^{(Positive Error Value)} qiimihii aad u xisaabisay Whisker Top .

Si aad ugu darto shafka hoose:

1. Guji sanduuqa qarsoon
2. Hoosta tabka Naqshadda Shaxda^{(Chart Design)} , dooro Ku dar Cunsurka Shaxda^{(Add Chart Element)} .
3. Guji Baararka Cilada> Xulashooyinka Baararka Cilada badan.
4. Hoosta jihada^(Direction) , dhagsii minus iyo hoosta Qadarka Cilada^{(Error Amount)} dhagsii Custom > Specify Value .

1. Sanduuqa wada hadalka, ku beddel Qiimaha Qaladka Xun^{(Negative Error Value)} qiimihii aad u xisaabisay Whisker Bottom .

Hadda waxaad haysataa sanduuq aasaasi ah iyo goob whisker ah oo xogtaada ah. Waxaad si dheeraad ah u habayn kartaa tan adigoo ku daraya xariiq dhexdhexaad ah ama dhibco, beddela midabada, oo beddela qaabka jaantuska.

Falanqaynta Tirakoobku weligeed^(Has) may fududayn^{(Been Easier)}

Nasiib wanaag, iyada oo la adeegsanayo noocyo cusub, oo ka awood badan barnaamijka, muuqaalaynta iyo falanqaynta xogta ayaa noqotay mid aad u fudud. Casharradan, waa inaad si adag u fahantaa sida sanduuqa iyo barxadda loo isticmaalo iyo sida aad mid ugu samaysan karto buug- shaqeedka Excel^(Excel) .

How to Create a Box Plot in Microsoft Excel

Miсrosoft Excel makes it easy for you to organize, present, and analyze data using various charts. A particularly powerful chart is the box and whisker plоt (also known as a box plot), designed to help display the distribution of vаlues in a data set.

In this article, we’ll cover how you can create a box plot in Microsoft Excel, covering both Excel 365 and older versions for those yet to upgrade.

Box Plots: What Are They?

A box plot is a method of displaying data that helps visualize statistical qualities like the spread and variability of the data. It shows a single bar (the box) split in two, with lines (the whiskers) extending to either side of the box.

Each of these elements visualizes the five-number summary of a set of numerical data. They look like this and can be displayed horizontally or vertically:

To understand the five-number summary, let’s take a look at a sample data set.

25, 26, 28, 30, 32, 34, 37, 38

1. The minimum. The minimum value in the data set. This is the endpoint to the left/bottom of the left/lower whisker.

1. The first quartile. This is the value under which 25% of data points are found.
2. The second quartile. This is the mediant. It amounts to the “middle value”.
3. The third quartile. This is the value above which 75% of data points are found.
4. The maximum. The maximum value in the data set.

How to Create a Box Plot in Excel 365

In Office 365, Microsoft Excel includes box plots as a chart template, making it easy to create a visual plot for your data. If you aren’t sure how to use Excel, learn the basics first.

To create a box plot:

1. Open a new worksheet and input your data.
2. Select your data set by clicking and dragging.

1. In the ribbon, select the Insert tab.

4. Click Insert Statistic Chart then Box and Whisker.

Note: If you insert an empty chart, you can input your data set by selecting the Chart Design tab and clicking Select Data.

Excel will now create a bare-bones box and whisker chart. However, you can customize this Excel chart further to display your statistical data exactly how you’d like it.

How to Format a Box Plot in Excel 365

Excel allows you to style the box plot chart design in many ways, from adding a title to changing the key data points that are displayed.

The Chart Design tab allows you to add chart elements (like chart titles, gridlines, and labels), change the layout or chart type, and change the color of the box and whiskers using built-in chart style templates.

The Format tab allows you to fine-tune your color selections, add text, and add effects to your chart elements.

To add further display elements, right-click the box and whisker plot and select Format Data Series from the drop-down menu.

The options include:

1. Show inner points. This displays all individual data points as circles inside the first and third quartiles.
2. Show outlier points. This displays outliers (abnormally high or low data points) as circles outside the plot.
3. Show mean markers. This displays the mean value as a cross within the chart.
4. Show mean line. This displays a line between the average points of multiple data sets.
5. Quartile calculation. If you have an odd number of data points, you can choose to calculate the quartiles by either including or excluding the median. For larger data sets, you should use the exclusive interquartile range, and for smaller data sets the inclusive median method is generally more accurate.

How to Create a Box and Whisker Plot in Older Versions of Excel

As older versions of Excel (including Excel 2013 and Excel 2016) don’t include a template for the box and whisker chart, creating one is much more difficult.

First, calculate your quartile values using the following formulae and create a table:

1. Minimum value: MIN(cell range)
2. First quartile: QUARTILE.INC(cell range,1)
3. Median: QUARTILE.INC(cell range, 2)
4. Third quartile: QUARTILE.INC(cell range, 3)
5. Maximum value: MAX(cell range)

Note: For cell range, drag and select your data set(s).

Next, calculate the quartile differences in a separate table (these relate to the box heights):

1. The value of Q1
2. The median minus the Q1
3. Q3 minus the median
4. The maximum value minus the Q3
5. Q1 minus the minimum value

You can then create a chart using these values:

1. Click the Insert tab then select Insert Column or Bar Chart.
2. Click Stacked Column Chart. If the chart isn’t displaying correctly, select the Chart Design tab then click Switch Row/Column.

1. Right-click the part of the graph that represents “Box 1 – hidden” and click Fill then click No Fill.

To add the top whisker:

1. Click the top box and select the Chart Design tab.
2. Click Add Chart Element.

1. Click Error Bars > More Error Bars Options.

1. Under Direction, click Plus. Under Error Amount click Custom > Specify Value.

1. Replace the Positive Error Value with the value you calculated for Whisker Top.

To add the bottom whisker:

1. Click the hidden box.
2. Under the Chart Design tab, select Add Chart Element.
3. Click Error Bars> More Error Bars Options.
4. Under Direction, click Minus and under Error Amount click Custom > Specify Value.

1. In the dialog box, replace the Negative Error Value with the value you calculated for Whisker Bottom.

You now have a basic box and whisker plot for your data set. You can customize this further by adding a mean line or dot, changing the colors, and altering the chart style.

Statistical Analysis Has Never Been Easier

Luckily, with newer, more powerful versions of the program, visualizing and analyzing data has become much simpler. With this tutorial, you should have a firm grasp of how a box and whisker plot is used and how you can set one up in an Excel workbook.

Related posts

• Qaababka Fiidiyowga ee ugu caansan iyo codecs waa la sharaxay

• Hagaaji "Waxaad u baahan tahay ogolaansho si aad u samayso falkan" Khaladka

• Ku biir Kumbuyuutar Windows XP ah Windows 7/8/10 Kooxda Guriga

• Ku dheji faylka VHD ee Windows XP

• Sida loo rakibo IIS oo loo habeeyo server-ka shabakadda gudaha XP

• Microsoft Excel waxay isku dayaysaa inay soo ceshato macluumaadkaaga

• Sida Loo Soo Furto Kadhka Hadiyada Akoonka Microsoft

• Sida loo abuuro Gantt Charts gudaha Microsoft Excel

• FIX: Xakameeyaha Xbox wuxuu sii wadaa goynta

• 4 siyaabood oo loo tirtiro ama looga saaro adeega Windows

• Sida Loo Isticmaalo Calaamadaha Qarsoon ee Kooxaha Microsoft

• Sida Loo Sameeyo Fayl Sugan oo Xidhan gudaha Windows XP

• Ku muuji Faah-faahino Yar ama Faah-faahin badan Sanduuqa Wada hadalka Wareejinta Faylka ee Daaqadaha

• Talooyin iyo tabaha Browser Microsoft Edge ee loogu talagalay Windows 11/10

• 6 Siyaabaha ugu Wanaagsan ee loo Hagaajin karo "ku guul daraystay in la soo shubo steamui.dll" Khaladka

• Sida looga saaro Microsoft Apps-ka caadiga ah ee ku jira Windows 11/10

• Sida loo qaboojiyo safafka iyo tiirarka gudaha Excel

• Microsoft Excel waxay sababtaa isticmaalka CPU sare marka uu socdo Windows 10

• Sida Loo Fahmo Maxaa-Haddii Falanqaynta Microsoft Excel

• Waa maxay Jeexitaanka Shaashada iyo Sida Looga Hortago?