英文:
Dynamically update a sheet based on values received in another sheet (within same workbook) from a form
问题
我理解你需要将表格中的信息进行翻译,但由于内容较长,无法一次性进行完整的翻译。以下是翻译的前部分:
我正在使用 Office 365。为了从在线的 Excel 表格中收集表单信息,我创建了一个新的 Excel Online 工作簿/文件(基本上是在浏览器中,登录到 Office 365,进入 Excel 并选择“创建新文件”,从而在 OneDrive 中创建了一个 Excel 文件),然后执行了 插入 > 表单
,以创建一个新的 Microsoft Forms 表单。
Microsoft Forms 表单旨在收集来自我的组织内用户的信息(即需要登录才能填写表单)。所收集的信息是有关小部件评分的。
背景信息:
一组小部件分配给同事进行测试和评分。每个特定的小部件(由 widget_id
标识)总是分配给正好两名测试者。每个测试者可以分配任意数量的小部件。一旦测试者对一个小部件进行了评分,他们通过上面提到的 Microsoft Forms 表单输入他们的评分。
通过表单收集的信息如下:
- 受访者的
电子邮件
(自动收集)。 - 响应的
日期时间
(自动收集)。 - 被评小部件的
widget_id
(由受访者从下拉列表中选择)。 - 受访者确定的小部件的
评分
(在一定范围内的数字输入,例如 1 ... 100)。
因此,在我的 Excel 工作簿的表单表格中,它显示为:
假设表单提交表格称为 表单 1
。
A | B | C | D |
---|---|---|---|
电子邮件 | 日期 | widget_id | 评分 |
jack@company.com |
2023-04-23 | 990236 | 65 |
mack@company.com |
2023-04-23 | 990236 | 75 |
iris@company.com |
2023-04-23 | 990003 | 50 |
jack@company.com |
2023-04-24 | 990235 | 45 |
如果你需要继续翻译后续内容,请提醒我。
英文:
I am using Office 365. In order to collect info from forms in a live Excel sheet, I created a new Excel Online workbook/ file (basically within the browser, logged into Office 365, went to Excel and "Create New" which created an Excel file in OneDrive) and did Insert > Forms
to create a new Microsoft Forms form.
The Microsoft Forms form is intended to collect information from users within my organization (i.e. sign in is required to fill up the form). The information collected is widget ratings.
Context:
A bunch of widgets are assigned to colleagues to test and rate. A specific widget (identified by widget_id
) is always assigned to exactly 2 testers. Each tester can be assigned any number of widgets. Once a tester has rated a widget, they input their rating via the Microsoft Forms form mentioned above.
The info collected via the form is as following:
email
of respondent (collected automatically).datetime
of response (collected automatically).widget_id
of the rated widget (selected by respondent from a dropdown).rating
of the widget determined by the respondent (numerical entry within a set range, e.g. 1 ... 100 )
So, in my Excel workbook's forms sheet, it appears as:
Let's say the Forms submissions sheet is called Form 1
.
A | B | C | D |
---|---|---|---|
date | widget_id | rating | |
jack@company.com |
2023-04-23 | 990236 | 65 |
mack@company.com |
2023-04-23 | 990236 | 75 |
iris@company.com |
2023-04-23 | 990003 | 50 |
jack@company.com |
2023-04-24 | 990235 | 45 |
I have another sheet allocations
with 6 relevant columns which has information on which testers have been allocated to each widget:
- A widget ID
- B email of tester 1 assigned to the widget
- D widget rating by tester 1
- E email of tester 2 assigned to the widget
- G widget rating by tester 2
- Columns C, F, and H-J are irrelevant for this issue
A | B | C | D | E | F | G | H | I | J |
---|---|---|---|---|---|---|---|---|---|
widget_id | email1 | email1thanked | rating1 | email2 | email2thanked | rating2 | actionsent | difference | average |
990235 | jack@company.com |
0 | mack@company.com |
0 | 0 | ||||
990236 | mack@company.com |
0 | jack@company.com |
0 | 0 | ||||
990231 | jack@company.com |
0 | iris@company.com |
0 | 0 | ||||
990197 | iris@company.com |
0 | mack@company.com |
0 | 0 | ||||
990003 | mack@company.com |
0 | iris@company.com |
0 | 0 |
What I want to happen
As various testers submit their forms, Excel should populate column D or G (as appropriate) of allocations
sheet, depending on the widget id and tester email. In other words, I want Excel to copy over the ratings from the Form 1
sheet to the appropriate place in the more understandable allocations
sheet.
In other words,
the moment, the data row appears in Form 1
sheet,
Excel should look for the widget_id
from this row in allocations
sheet.
Further, Excel should check whether the email
from this row in Form 1
sheet matches email1
in allocations
sheet or email2
in allocations
sheet in the row in which this widget_id is found in
allocations.
email1
If it matches then copy the
ratingfrom this row in
allocationssheet to
rating1in
allocationssheet, if it matches
email2then copy the
ratingto
rating2`.
So, in our example allocations
sheet would look like
A | B | C | D | E | F | G | H | I | J |
---|---|---|---|---|---|---|---|---|---|
widget_id | email1 | email1thanked | rating1 | email2 | email2thanked | rating2 | actionsent | difference | average |
990235 | jack@company.com |
0 | 45 | mack@company.com |
0 | 0 | |||
990236 | mack@company.com |
0 | 75 | jack@company.com |
0 | 65 | 0 | ||
990231 | jack@company.com |
0 | iris@company.com |
0 | 0 | ||||
990197 | iris@company.com |
0 | mack@company.com |
0 | 0 | ||||
990003 | mack@company.com |
0 | iris@company.com |
0 | 50 | 0 |
答案1
得分: 1
以下是要翻译的内容:
• 在单元格 D2
中使用的公式
=LET(
x,MATCH(7^89,A:A),
y,A2:INDEX(A:A,x),
z,B2:INDEX(B:B,x),
XLOOKUP(y&"|"&z,'Form 1'!C:C&"|"&'Form 1'!A:A,'Form 1'!D:D,""))
• 在单元格 G2
中使用的公式
=LET(
x,MATCH(7^89,A:A),
y,A2:INDEX(A:A,x),
z,E2:INDEX(E:E,x),
XLOOKUP(y&"|"&z,'Form 1'!C:C&"|"&'Form 1'!A:A,'Form 1'!D:D,""))
要获取数据直到最后一行,我在您上一篇帖子中发布了其他替代方案:在工作表上有条件地计算两列之间的差异
工作簿
1: https://i.stack.imgur.com/iVoTO.png
2: https://stackoverflow.com/a/76528963/8162520
3: https://1drv.ms/x/s!AlEkksoPTc2cjH80ByMl_pjozb_S?e=j7jYNh
英文:
To get the data till the last row and produce the desired outcome, just like i had posted in comments, please refer the following formulas.
• Formula used in cell D2
=LET(
x,MATCH(7^89,A:A),
y,A2:INDEX(A:A,x),
z,B2:INDEX(B:B,x),
XLOOKUP(y&"|"&z,'Form 1'!C:C&"|"&'Form 1'!A:A,'Form 1'!D:D,""))
• Formula used in cell G2
=LET(
x,MATCH(7^89,A:A),
y,A2:INDEX(A:A,x),
z,E2:INDEX(E:E,x),
XLOOKUP(y&"|"&z,'Form 1'!C:C&"|"&'Form 1'!A:A,'Form 1'!D:D,""))
To get data till last row, i had posted the other alternatives in your last post: Conditionally calculate difference between two columns on a sheet
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论