Haddii aad ku jirto dhinaca maaliyadda ama xisaabaadka, waxaad horay u ogeyd in inta badan fursadaha shaqo ay u baahan yihiin xirfado dhexdhexaad ah ama heer sare ah oo Excel ah. Qaar ka mid ah shaqooyinka ugu caansan ee Excel ee doorarkan waa miiska Pivot^{(Pivot Table)} iyo VLOOKUP .

Maqaalkani wuxuu qeexayaa aasaaska miiska pivot. Tag halkan haddii aad rabto inaad wax badan ka ogaato VLOOKUP . Sidoo kale, hubi inaad hubiso beddelka VLOOKUP , oo ah shaqo la yiraahdo INDEX-MATCH .

Ku samee miiska Pivot gudaha Excel

Waa maxay miiska pivot? Si fudud^(Simply) loo dhigo, miiska pivot waa mid ka mid ah hawlaha la dhisay oo aad isticmaali karto si aad si dhakhso ah u abuurto miis kooban oo ku salaysan xog badan oo Excel ah .

Bal qiyaas haddii aad leedahay dukaan online ah oo iibiya noocyo kala duwan oo taleefannada gacanta ah oo wata xogta iibka sida hoos ku cad. Soo deji xaashida muunada

Ka dib markaad ganacsi sameyso ilaa laba bilood, waxaad xiiseyneysaa haddii aad iibisay badeeco badan bishii kowaad ama labaad. Waxa kale oo aad jeclaan lahayd inaad ogaato inaad iibisay badeecooyin badan oo Apple ah ama alaabada Samsung . Ugu dambeyntii, waxaad jeclaan lahayd inaad ogaato wadarta iibka aad heshay bil kasta.

Shaxda pivot-ka ayaa ah musharaxa ugu fiican ee lagu heli karo koobid degdeg ah adigoon u baahnayn inaad isticmaasho nooc kasta oo Excel ah, sida tirinta ama wadarta. Jawaabaha su'aalaha sare waxaa lagu soo saari karaa ilbiriqsiyo gudahood markaad ogaato sida loogu shaqeeyo miiska pivot.

Halkan waxaa ah tilmaamo tallaabo-tallaabo ah oo loogu talagalay abuurista miis pivot.

TALLAABADA 1^{(STEP 1)} – Samee^(Create) miiska pivot adigoo gujinaya mid ka mid ah unugyada ku jira shaxda xogta, ka dibna u gudub tabka sare ee Excel oo dooro Geli^{(Insert )}  ->  Shaxda Pivot^{( Pivot Table)} .

TALLAABADA 2^{(STEP 2)} – Daaqad xulasho ayaa soo bixi doonta waana inay si toos ah u go'aamisaa tirada buuxda ee miiska iyadoo lagu saleynayo unugga aad mar hore gujisay. Tusaalahan, waxaanu ku daraynaa miiska pivot-ka xaashi cusub, si ay u sahlanaato in la arko.

TALLAABADA 3^{(STEP 3)} – Guji^(Click) miiska madhan ee ku dhex abuurmay xaashida cusub. Waxaad ogaan doontaa in Goobaha Miiska Pivot^{(Pivot Table Fields)} ay ka soo bixi doonaan dhinaca midig ee xaashidaada. Halkani waa meesha aad jiidid-oo-dhigto si aad u abuurto soo koobid degdeg ah.

TALLAABADA 4^{(STEP 4)} – Si aad u ogaato tirada taleefanka gacanta ee la iibiyo bil kasta, u jiid Bisha^{(Month Sold)} Iibka aagga ROWS iyo Brand ilaa qiyamka^(VALUES) aagga .

Waxaad ogaan doontaa in Shaxda Pivot^{(Pivot Table)} si toos ah loo cusboonaysiin doono si loo muujiyo tirada saf ee bil kasta, taas oo muujinaysa tirada iibka taleefanka gacanta ee bil kasta.

Haddii aad u jiido Model ama Bakhaarka Goobta ^{(Warehouse Location)}qiyamka beddelka Summada^(Brand) , waxay^(VALUES) soo saari doontaa tiro isku mid ah bilo kasta maadaama ay si fudud u tixraacayso wadarta tirada saf ee bil kasta oo la iibiyo. ^{(Month Sold.)}Waxay u egtahay inaan iibinay taleefanno badan JAN marka la barbar dhigo FEB .

TALLAABADA 5^{(STEP 5)} – Si aad u ogaato in badeecooyin badan oo Apple ama Samsung ah lagu iibiyay dukaankaaga, waxaad dib u isticmaali kartaa isla miiska pivot adoon u baahnayn inaad mid cusub samayso.

Si tan loo sameeyo, waxaad nadiifin kartaa xulashada aanad u baahnayn (adiga oo jiidaya goobta xogta ee Aagga^(Area) oo ku rid meel kasta oo ku taal xaashida xaashida).

Marka xigta ku beddel Brand ee sanduuqa ROWS .

Jadwalka pivot isla markiiba waa la cusboonaysiin doonaa si uu u muujiyo wadarta tirada safafka, oo lagu kooxeeyay Brand (ie tirada guud ee badeecada ay iibisay Brand ilaa maanta). Dhab ahaantii waxaad iibisay wax badan oo Apple ah marka loo eego Samsung .

TALLAABADA 5^{(STEP 5)} - Ugu dambeyntii, si aan u ogaano inta aad ka heshay iibka bil kasta, waxaan dib u isticmaali doonaa isla Shaxda Pivot^{(Pivot Table)} .

Nadiifi goobta astaanta^(Brand) oo soo jiid Bisha la iibiyay^{(Month Sold)} oo ku celi aagga ROWS . Sida aan si gaar ah u rabno inaan ogaano wadarta iibka, nadiifi aagga VALUES oo ku jiid Qiimaha Iibka^{(Sale Price)} sida hoos ku cad.

Sida tiirka Sicirka Iibka^{(Sale Price)} ee xogta asalka ahi uu yahay qaab nambar, miiska pivot wuxuu si toos ah u soo koobayaa Qiimaha Iibka^{(Sale Price)} , halkii la tirin lahaa tirada safafka Qiimaha Iibka . ^{(Sale Price )}Voila , waxaad heshay $7,550 JAN iyo $7,100 gudaha FEB .

Isku day inaad ku ciyaarto oo aad jiidato garoomada sida hoos ku qoran oo arag waxa ka soo baxay miiska pivot.

Tani waa uun xoqidda dusha sare ee waxa uu samayn karo miiska pivot, laakiin waxay ku siin doontaa faham aasaasi ah oo wanaagsan oo aad ku bilowdo. Sahaminta ku faraxsan!

Talo-siin: Haddii muraayadda miiska Pivot^{(Pivot Table Fields)} ee dhinaca midig ee xaashida ay maqan tahay, isku day inaad ku dul rogto jiirkaaga miiska pivot, midig guji oo dooro Show List List^{(Show Field List)} . Taasi waa inay dib u soo celisaa. Ku raaxayso!

How to Create a Simple Pivot Table in Excel

If you are in the field of financе or accounting, you alreadу know that most of the job opportunities require intermediate or advanced Excel skills. Some of the most common Excel functions in these roles are Pivot Table and VLOOKUP.

This article will outline the basics of a pivot table. Go here if you want to learn more about VLOOKUP. Also, be sure to check out the alternative to VLOOKUP, a function called INDEX-MATCH.

Create a Pivot Table in Excel

What is pivot table? Simply put, a pivot table is one of the built-in functions that you can use to quickly create a summary table based on a large set of data in Excel.

Imagine if you own an online shop that sells different models of mobile phones with sales data as shown below. Download sample spreadsheet.

After doing business for about two months, you are curious if you have sold more product in the first month or the second. You would also like to know whether you have sold more Apple products or Samsung products. Lastly, you would like to know the total sales received in each month.

The pivot table is the perfect candidate for getting a quick summary without needing to use any Excel formula, such as count or sum. The answers to the above questions can be produced in a matter of seconds once you know how to work with a pivot table.

Here are step-by-step instructions for creating a pivot table.

STEP 1 – Create a pivot table by clicking in any of the cells within the data table, then go to the top tab in Excel and select Insert  ->  Pivot Table .

STEP 2 – A selection window will appear and it should automatically determine the full range of the table based on the cell where you clicked earlier. For this example, we’re adding our pivot table to a new worksheet, so it’ll be easier to see.

STEP 3 – Click on the blank pivot table created in the new sheet. You will notice a Pivot Table Fields will appear on the right side of your spreadsheet. This is where you drag-and-drop to create the quick summary.

STEP 4 – To know the number of mobile phone sold each month, drag Month Sold to the ROWS area and Brand to VALUES area.

You will notice that the Pivot Table will be automatically updated to show the number of rows for each month, which indicates number of mobile phone sales for each month.

If you drag Model or Warehouse Location to VALUES instead of Brand, it will produce the same numbers for each months as it is simply referring to the total count of rows in each Month Sold. Looks like we sold more phones in JAN compared to FEB.

STEP 5 – To know whether more Apple or Samsung products were sold in your store, you can reuse the same pivot table without needing to create a new one.

To do this, you can clear the selection that you no longer need (by dragging the data field out of the Area and dropping it anywhere on the spreadsheet).

Next replace it with Brand in the ROWS box.

The pivot table will be instantly be updated to show total number of rows, grouped by Brand (i.e. Total number of product sold by Brand to date). You actually sold more Apple product compared to Samsung.

STEP 5 – Lastly, to know how much you have received in sales in each of the months, we will be reusing the same Pivot Table.

Clear out the Brand field and drag Month Sold back to the ROWS area. As we specifically want to know the total sales, clear the VALUES area and drag in Sale Price as shown below.

As the Sale Price column in the original dataset is in number format, the pivot table will automatically sum up the Sale Price, instead of counting the number of Sale Price rows. Voila, you have received $7,550 in JAN and $7,100 in FEB.

Try to play around and drag the fields as per below and see what is the outcome of the pivot table.

This is just scratching the surface of what pivot table can do, but it will give you a good basic understanding to start with. Happy exploring!

Tips: If the Pivot Table Fields pane on the right of the spreadsheet goes missing, try to hover your mouse over the pivot table, right click and choose Show Field List. That should bring it back up. Enjoy!

Related posts

• Sida loo tirtiro khadadka banaan ee Excel

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Sida loo Geliyo Xaashida Shaqada ee Excel Word Doc

• Sida loo isticmaalo Excel's What-If Analysis

• Sida Loo Hagaajiyo Safka gudaha Excel

• Sida loo xarfo xarfaha Excel

• Sida loo isticmaalo Flash Fill ee Excel

• Sida loo sameeyo shaxda Pie ee Excel

• Farqiga u dhexeeya Microsoft Excel Online iyo Excel ee Desktop

• Sida loo Hagaajiyo #N/A Khaladaadka gudaha Foomamka Excel sida VLOOKUP

• U isticmaal Magacyada Range Dynamic ee Excel si aad u Dejiso Dabcida

• Hagaha VBA Sare ee MS Excel

• Ku xidhida Excel iyo MySQL

• Si degdeg ah ugu qor nambarada Word iyo Excel

• Adeegsiga Qalab Raadinta Hadafka ee Excel Maxaa-Haddii Falanqaynta

• Sida loo Isticmaalo If iyo Nsted Haddii Odhaahyada Excel

• Sida Loo Raadiyo Excel

• Sida Loo Dhaqaajiyo Columns gudaha Excel

• Sida loo raad raaco Ku-tiirsanaanta gudaha Excel

• Sida Loogu Mideeyo Xogta Faylal Badan oo Excel ah