如果您在财务或会计领域，您已经知道大多数工作机会需要中级或高级Excel技能。这些角色中一些最常见的Excel函数是Pivot Table和VLOOKUP。
本文将概述数据透视表的基础知识。如果您想了解有关VLOOKUP的更多信息，请访问此处。此外，请务必查看VLOOKUP的替代方法，一个名为INDEX-MATCH的函数。
在 Excel 中创建数据透视表
什么是数据透视表？简单地(Simply)说，数据透视表是内置函数之一，可用于根据Excel中的大量数据快速创建汇总表。
想象一下，如果您拥有一家在线商店，该商店销售不同型号的手机，其销售数据如下所示。下载示例电子表格。
做生意大约两个月后，你会好奇第一个月还是第二个月你卖的产品更多。您还想知道您是否销售了更多的苹果产品或三星(Samsung)产品。最后，您想知道每个月收到的总销售额。
数据透视表是无需使用任何Excel(Excel)公式（例如计数或总和）即可快速汇总的理想选择。一旦您知道如何使用数据透视表，就可以在几秒钟内得出上述问题的答案。
以下是创建数据透视表的分步说明。
第 1 步(STEP 1)–通过单击数据表中的任何单元格来创建(Create)数据透视表，然后转到Excel的顶部选项卡并选择Insert -> Pivot Table。
第 2 步(STEP 2)– 将出现一个选择窗口，它应该根据您之前单击的单元格自动确定表格的整个范围。对于此示例，我们将数据透视表添加到新工作表中，以便更容易查看。
第 3 步(STEP 3)–单击(Click)在新工作表中创建的空白数据透视表。您会注意到数据透视表字段(Pivot Table Fields)将出现在电子表格的右侧。您可以在此处拖放以创建快速摘要。
STEP 4 – 要知道每个月售出的手机数量，将Month Sold拖到ROWS区域，将Brand拖到VALUES区域。
您会注意到数据透视表(Pivot Table)将自动更新以显示每个月的行数，这表明每个月的手机销售数量。
如果您将Model或Warehouse Location拖到VALUES而不是Brand ，它将为每个月生成相同的数字，因为它只是指每个(Brand)Month Sold中的总行数。与2 月(FEB)相比，我们在 1 月售出(JAN)的手机似乎更多。
第 5 步(STEP 5)– 要了解您的商店是否销售了更多Apple或Samsung产品，您可以重复使用同一个数据透视表，而无需创建新数据透视表。
为此，您可以清除不再需要的选择（通过将数据字段拖出区域(Area)并将其放在电子表格的任何位置）。
接下来用ROWS框中的Brand替换它。
数据透视表将立即更新以显示按品牌分组的总行数（即(Brand )品牌(Brand )迄今为止销售的产品总数）。与三星(Samsung)相比，您实际上销售了更多的苹果(Apple)产品。
第 5(STEP 5)步 – 最后，要了解您每个月收到的销售额，我们将重复使用同一个数据透视表(Pivot Table)。
清除Brand字段并将Month Sold拖回ROWS区域。由于我们特别想知道总销售额，请清除VALUES区域并拖入Sale Price，如下所示。
由于原始数据集中的Sale Price列是数字格式，因此数据透视表将自动汇总Sale Price，而不是计算Sale Price行的数量。瞧(Voila)，您在 1 月收到了 7,550美元，在(JAN)2 月(FEB)收到了7,100 美元。
尝试按照下面的方式播放并拖动字段，看看数据透视表的结果是什么。
这只是对数据透视表功能的皮毛，但它会给您一个很好的基本了解。快乐探索！
提示：如果电子表格右侧的Pivot Table Fields窗格丢失，请尝试将鼠标悬停在数据透视表上，右键单击并选择Show Field List。那应该把它带回来。享受！
How to Create a Simple Pivot Table in Excel
If you are in the field of finance or accounting, you already know that most of the job оppоrtunities require intermediate оr advanced Excel skills. Some of the most common Excel functions in these rolеs 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!
