床单标题行arrayformula以根据作业的周转和收到的日期在日期范围内查找费率
我有一个带有两个表格的Google表工作簿： jobs
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 周转 | 接收到 | 速率 | Pages | /strong> |
| 2 | 标准 | 12/2/2021 | $ 0.40 | 204 | $ 81.60 |
| 3 | Rush | 12/9/2021 | $ 0.60 | 79 | $ 47.40 $ 47.40 |
| 4 | Rush | 12/29/29/ 2021 | $ 0.60 | 24 | $ 14.40 |
| 5 | 标准 | 1/1/2022 | 0.45 | 81 | $ 36.45 |
| 6 | 标准 | 1/2/2022 | $ 0.45 | 137 | 61.65 |
| $ | $ /2022 | $ 0.45 | 95 | $ 42.75 | |
| 8 | 标准 | 1/15/2022 | $ 0.45 | 162 | $ 72.90 |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 周转 | 基本率 | 开始日期 | 结束日期 |
| 2 | 标准 | $ 0.40 | 9/1/2021 | 12/31/2021 |
| 3 | Rush | $ 0.60 | 8/17/2018 | 6/10/2022 |
| 4 | 加快 | $ 0.80 | 8/17 /2018 | 6/10/2022 |
| 5 | 每日 | $ 1.00 | 8/17/2018 | 6/10/2022 |
| 6 | 标准 | $ 0.45 | 1/1/2022 | 6/10/2022 |
我试图在工作中使用arrayformula！或价格的开始日期之间！
这个想法是，费率可能会随着时间的推移而变化，但是工作总计仍将使用每个作业进入时的正确速率来计算。
我知道我不能使用arrayformula使用sumifs，所以我尝试使用查询，但这只会填充第一份工作的费率。
={"Rate";
ARRAYFORMULA(QUERY(Rates!A:D,
"select B where A contains '"&Jobs!A2:A
&"' and C < date'"&TEXT(Jobs!B2:B, "YYYY-MM-DD")
&"' and D > date'"&TEXT(Jobs!B2:B, "YYYY-MM-DD")&"'",0))}
如果需要，我可以添加助手列。我试图避免在添加作业时必须手动填充该列的公式。
这是工作簿的链接：
感谢对此的任何帮助。
I've got a Google Sheets workbook with two sheets: Jobs
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Turnaround | Received | Rate | Pages | Total |
| 2 | Standard | 12/2/2021 | $0.40 | 204 | $81.60 |
| 3 | Rush | 12/9/2021 | $0.60 | 79 | $47.40 |
| 4 | Rush | 12/29/2021 | $0.60 | 24 | $14.40 |
| 5 | Standard | 1/1/2022 | $0.45 | 81 | $36.45 |
| 6 | Standard | 1/2/2022 | $0.45 | 137 | $61.65 |
| 7 | Standard | 1/5/2022 | $0.45 | 95 | $42.75 |
| 8 | Standard | 1/15/2022 | $0.45 | 162 | $72.90 |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Turnaround | Base Rate | Start Date | End Date |
| 2 | Standard | $0.40 | 9/1/2021 | 12/31/2021 |
| 3 | Rush | $0.60 | 8/17/2018 | 6/10/2022 |
| 4 | Expedited | $0.80 | 8/17/2018 | 6/10/2022 |
| 5 | Daily | $1.00 | 8/17/2018 | 6/10/2022 |
| 6 | Standard | $0.45 | 1/1/2022 | 6/10/2022 |
I'm trying to use an ARRAYFORMULA in Jobs!C1 to look up the value in Rates!B:B where the Turnaround in Jobs!A:A matches the Turnaround in Rates!A:A and the Date Received in Jobs!B:B falls on or between the Start Date in Rates!C:C and End Date in Rates!D:D.
The idea is that rates may change over time, but the job totals will still calculate using the correct rate at the time each job came in.
I know I can't use SUMIFS with ARRAYFORMULA, so I tried using QUERY, but this only populates the rate for the first job.
={"Rate";
ARRAYFORMULA(QUERY(Rates!A:D,
"select B where A contains '"&Jobs!A2:A
&"' and C < date'"&TEXT(Jobs!B2:B, "YYYY-MM-DD")
&"' and D > date'"&TEXT(Jobs!B2:B, "YYYY-MM-DD")&"'",0))}
I'm okay with adding helper columns if needed. I'm trying to avoid having to manually fill the formula down the column as jobs are added.
Here is a link to the workbook:
Job Rate Lookup By Turnaround + Date Range
I appreciate any help on this.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
尝试：
try:
使用
arrayformula时，您将无法使用QUERY以获取全部值数组，因为它只会返回找到的第一个值。我创建了一个使用
vlookup匹配值的公式，但是我必须从jobs中从标准>标准中修改名称标准2 。这是公式：
这些是结果：
When using
ARRAYFORMULAyou won't be able to useQUERYin order to get the whole array of values as it will only return the first value that is found.I created a formula that matches the value using
VLOOKUPhowever I had to modify the name inJobsfromStandardtoStandard 2.This is the formula:
These are the results: