根据在同一工作簿中的另一个表单中收到的值,动态更新一个表格。

huangapple go评论86阅读模式
英文:

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
email 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.
If it matches
email1then copy theratingfrom this row inallocationssheet torating1inallocationssheet, if it matchesemail2then copy theratingtorating2`.

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


Workbook

huangapple
  • 本文由 发表于 2023年6月29日 15:16:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/76578810.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定