英文:
Query data in Google Sheets to get a table that shows day change
问题
有没有办法可以在Google表格中查询一些数据，以便不是得到一个总计表，而是得到每天的变化？
这是我的示例表格：https://docs.google.com/spreadsheets/d/1259GAJ2xzEgTMWJsX5QWsE8RbRXqeXJ0rt1Y_xRd5ls/edit?usp=sharing
我有一些数据显示每个视频的总览次数，它每天都会增加。我想要看到的是每个视频每天获得的总览次数。从数据中，这可以通过在每天获取的总数中减去前一天的值来计算。
让事情变得复杂（我希望在数据中清晰可见的）的是，会添加新的视频，所以它们需要与其他视频一起显示。
我已经能够轻松地查询和透视数据，但它仍然只显示每天视频的总览次数，我不知道如何使得相同的表格仅显示与前一天的变化。
英文:
Is there a way that I can query some data in Google Sheets so that, instead of getting a table of totals, I get a daily change?
Here is my example sheet: https://docs.google.com/spreadsheets/d/1259GAJ2xzEgTMWJsX5QWsE8RbRXqeXJ0rt1Y_xRd5ls/edit?usp=sharing
I have data that shows a total number of views for each video, and it increments each day. What I want to see instead is the total number of views that each video gets each day. From the data, that would be calculated by taking the total on each day and subtracting the previous day's value.
Complicating things (which I hope is clear in the data) is that new videos will be added, so they'll need to show up alongside the others.
I've been able to easily query and pivot the data, but it still just shows the total views for the video on each day, and I just don't know how to get that same table to show only the change from the previous day.
答案1
得分: 1
=map(A3:A, B3:B, C3:C, lambda(a, b, c, if(a="", , let(Λ, filter(A:A, B:B=b),
{a, b, c - ifna(filter(C:C, B:B=b, A:A=xlookup(a-1, Λ, Λ, ,-1)))}))))
英文:
<!-- language-all: js -->
=map(A3:A,B3:B,C3:C,lambda(a,b,c,if(a="",,let(Λ,filter(A:A,B:B=b),
{a,b,c-ifna(filter(C:C,B:B=b,A:A=xlookup(a-1,Λ,Λ,,-1)))}))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论