[Knowledge sharing] Unlock EXCEL Set table new skills

In daily work, Do you often get hurt by your cousin Excel Countless complex numbers on, Confused by functional logic?

In this digital age, With big cousin EXCEL Dealing with people seems to have become an integral part of the work of most office workers. Especially for Finance, For practitioners such as sales, They need to count different departments regularly or irregularly/store/Cost budget of personnel, Sales performance and other data, The times of dealing with the eldest cousin are more frequent.

There is such a trading company, Sales Assistant of his company MM express, She talks with all kinds of people almost every day Excel Dealing with forms, But in useFangcloudbefore, This process is extremely painful: The company has five sales teams, Take hundreds of orders every day. As a sales assistant, She needs to meet with the team leaders every day, collect, Summary, Sort out the order data of each group, And submit it to the finance department for collection management, And none of these works has not passed Excel To complete.

The heavy workload has already made her as a sales assistant busy every day, In addition, we often encounter the following problems, It can be called "history written in blood and tears" :

Each team leader sends data in different ways, Every day from QQ, WeChat, Download data to the local computer through multiple channels such as email, Then make a summary;

The data received is often in different formats, It takes a lot of time to adjust;

Summary statistics can be carried out only after all data are collected, But there are often delays, Cause delay in summary work;

If the order data changes, You have to download table statistics again, workload double;

In case of peak season or year-end performance, Leaders ask real-time order data every three to five times, Every time I make a form, I really collapse.

So it seems, Although the assistant MM Data analyst background, Can put "Big cousin" Play so well, But without the help of office tools, It is also difficult to complete the summary work easily and quickly.

But in useFangcloudafter, assistant MM Opened a new way of working, The summary work can also be completed more efficiently. below, Share her new way of working:

Get ready, The harvest will be once and for all

1. In the clouds, assistant MM by 5 Each sales team establishes a separate order folder, And the general table folder open only to yourself and leaders

2. take 5 Group order folder and summary table folder "Sync to local"

3. In the local synchronization folder, Create source data for each team#Standard template#

notes: Try to fix the format when setting the template, Avoid subsequent manual adjustment, Take a chestnut: The group name in the sales order table can be verified by setting data, Limit cell content to "Sell a group" , Instead of "a set" perhaps "sale 1 group" . All cells with fixed content or format based on statistical needs, All settings should be made in the template in advance)


4. Based on business needs#Design summary#, And establish the functional relationship between the general table and the sub table

Summary upon startup, Say goodbye to endless downloads and rework

After doing the above preparations, Each team leader enters orders on Yifang cloud every day according to the standard template, assistant MM Just start Yifang cloud at the specified time node. Due to the local embedded between the summary table and each detail table EXCEL Function function, Yifang cloud will automatically retrieve the cloud source data completed by each team, Real time update summary table. therefore, assistant MM You can say goodbye to the endless download and rework work, Half the effort N times!

If you think the sharing of these skills is helpful to you, Then practice quickly!

Attached Tips:

1) If the form template is changed, Need to start real-time synchronization, To avoid wrong data in the summary table.

2) This method borrows local excel Data reference function of, Note that the reference function points to the local absolute path (Include drive letter, Folder path, file name, sheet name) , If the file path of sub table or general table is changed, Data may not be found in the summary table.

3) If you want to generate a simple version without formula and external chain, send it to the leader, Save the summary table as a result table, Just delete all links.