Practical Excel skills sharing: Use pivot tables and VLOOKUP functions to analyze accounts receivable

In the previous article "Sharing practical Excel skills: Quickly create two-color charts", we learned an Excel skill and learned how to quickly create two-color charts. The following article will introduce to you how to use Excel Pivot Table and VLOOKUP function to analyze accounts receivable. Come and collect it and learn it!

Summary of content: Excel accounts receivable aging analysis will use pivot tables for analysis. Before analysis, use the VLOOKUP function to organize the data.

Aging refers to the length of time a company has outstanding accounts receivable. Aging is the most important information when analyzing accounts receivable. All accounts receivable that are aged more than a reasonable number of days will have a negative impact on company operations. The higher the age, the lower the capital efficiency and the risk of bad debts. The bigger it is, the higher the financial cost.

The following is the accounts receivable table. Now the leader requires us to count the amounts of each aging level (accounts receivable aging analysis).

Final effect:

##Solution:

1. First solve the number of days that have passed so far: the formula in cell C2 is: =TODAY()-A2, the current date minus the receivable date.

2. Aging level statistics: According to the aging parameter table in the A13:B17 area, enter in D2: =VLOOKUP(C2,$A$14:$B$17, 2,1), use the fuzzy search of the vlookup function to determine the age of each account.

#3. Select the data source area and insert a pivot table. Drag the aging level and accounts receivable amount to the row field and value field respectively to quickly analyze the total amount of each aging.

Related learning recommendations:

excel tutorial

The above is the detailed content of Practical Excel skills sharing: Use pivot tables and VLOOKUP functions to analyze accounts receivable. For more information, please follow other related articles on the PHP Chinese website!