You can perform the following steps to use Excel to calculate the data volume of each county by city and display the results in key-value pairs:
- Create a new worksheet in Excel to store the statistics.
- Create a table in the worksheet with three columns for city, county, and data volume. You can change the name of the column or add more columns as needed.
- Import the raw data into Excel, making sure that the data in each row includes the city, county, and data volume.
- Use the PivotTable function of Excel to count the data volume of counties under each city. In the PivotTable Wizard, drag the city column to the Row area, the county column to the Column area, and the data volume column to the Value area. Make sure that the aggregate function selected in the PivotTable Settings is "Count" or "sum" so that the amount of data is counted correctly.
- Copies the results of the PivotTable to a new worksheet. In the new worksheet, delete the unwanted columns and rows and format the table as key-value pairs, such as city as the key and county and data volume as the values.
- Use Excel's filter function to filter out counties with zero data so that only counties with data are displayed.
If you need to move the volume column between the key and the value, use Excel's Cut and Paste functions.
Hopefully, these steps will help you use Excel to count the amount of data for each county by city and present the results as key-value pairs.