I have a spreadsheet that is for a group of clients. Each line of data has information on insurance claims, of which include a client number that is unique to that client.
I am looking for a program/software that will allow me to compute various metrics and present those in a table, with an overall comparison to all the other clients.
I also need to present data that is compiled into a top 5 metric that is then placed in a bar graph.
This data needs to be computed for each individual client, and I often have to do this as one-off projects throughout the year for specific clients.
Finally, I need to then pass this off to colleagues that will then look at the information and add in text that is relevant to each client.
I receive these files as a .CSV file and currently work in Excel to compute the information and then copy and paste the information to a document file.
I work in a Windows environment. I am currently limited to Office tools, however I’m willing to use my own personal computer to install an application that will give me the ability to do what I’m looking for.
【现状】将几个 CSV 导入 Excel，利用 Excel 用表格全方位比较某个保险客户和其他客户的各种指标，并提取该用户最前 5 项指标用条形图展示。经常必须在一年中为特定客户做一次一次性项目，将每个客户的表格和图形粘贴到文件中传递给同事，然后他们将查看信息并添加到相关客户的文本中。
【愿望】我的工作环境是 windows，我仅限于会使用 office 工具，我想在自己电脑上装一个客户端程序。
【分析】主要是痛点是每次都要手工；往下个流程传递的还需要是一个文档，只要数据做好了，Excel 上的图形显示，查看数据时点亮一下就可以了。
【解法】推荐你使用集算器完成你一年中的一次性项目，而且不需任何花费。集算器即装即用，简单脚本就能实现将 CSV 文件导入成内部表，关联几个表的用户数据，按条件过滤，分组汇总等各种计算，最后按用户 ID，将每个用户的汇总数据导成 Excel 即可。
For a simple example, the code is as follows:
sOrder=file("D:\\sales.csv").import@t()
emp=file("D:\\emp.csv").import@t(EId,Name,Dept,Gender)
// Order table left associated employee table
jt=join@1(sOrder:s,SellerId;emp:e,EId)
eOrder=jt.new(s.SellerId:SellerId,s.Amount:Amount,s.OrderDate:OrderDate,
e.Name:Name,e.Dept:Dept)
//select orders by time range
day100=eOrder.select(OrderDate>=startDate&&OrderDate<=endDate)
// Find the 3 largest orders for each sales
top3=eOrder.group(SellerId;~.top(3;-Amount):t).conj(t)
//Export details of up to 3 orders by employee ID
top3.(file(~.SellerID/".xlsx").xlsexport@t(~))
sOrder=file("D:\\sales.csv").import@t()
emp=file("D:\\emp.csv").import@t(EId,Name,Dept,Gender)
// Order table left associated employee table
jt=join@1(sOrder:s,SellerId;emp:e,EId)
eOrder=jt.new(s.SellerId:SellerId,s.Amount:Amount,s.OrderDate:OrderDate,
e.Name:Name,e.Dept:Dept)
//select orders by time range
day100=eOrder.select(OrderDate>=startDate&&OrderDate<=endDate)
// Find the 3 largest orders for each sales
top3=eOrder.group(SellerId;~.top(3;-Amount):t).conj(t)
//Export details of up to 3 orders by employee ID
top3.(file(~.SellerID/".xlsx").xlsexport@t(~))
网友评论