英文:
What is the best way in Google Sheet to Count Unique, and summarize a value on the same row?
问题
Sheet 1
| Column A | Column B |
|---|---|
| 约翰 | 2 |
| 戴夫 | 1 |
| 约翰 | 1 |
| 克洛伊 | 5 |
| 戴夫 | 1 |
Sheet 2
| Column A | Column B |
|---|---|
| 克洛伊 | 5 |
| 约翰 | 3 |
| 戴夫 | 2 |
此外，根据Sheet 2上B列的总和，给某人分配头衔的最佳方法是什么？假设如果您有5+点，您将获得Rank 3，4-3点是Rank 2，以下是Rank 1？
| Column A | Column B | Column C |
|---|---|---|
| Rank 3 | 克洛伊 | 5 |
| Rank 2 | 约翰 | 3 |
| Rank 1 | 戴夫 | 2 |
我尝试了使用=UNIQUE和=SUMIF的组合，但似乎不起作用。有人有好主意吗？
英文:
I am currently making a spreadsheet where people type in their name, and right next to it, a Value. This will then be sent over to another sheet that will Sort it,then summarize, and sort by name on how much they have earned. For Example:
Sheet 1
| Column A | Column B |
|---|---|
| John | 2 |
| Dave | 1 |
| John | 1 |
| Chloe | 5 |
| Dave | 1 |
Sheet 2
| Column A | Column B |
|---|---|
| Chloe | 5 |
| John | 3 |
| Dave | 2 |
Also, given the sum on Column B on Sheet 2, what is the best way to give someone a title based of their points? Lets say If you have 5+ points, you get Rank 3, 4-3 points are Rank 2, and below are Rank 1?
| Column A | Column B | Column C |
|---|---|---|
| Rank 3 | Chloe | 5 |
| Rank 2 | John | 3 |
| Rank 1 | Dave | 2 |
I tried to do a mix with =UNIQUE and =SUMIF but it does not seem to work. Anyone have a good idea?
答案1
得分: 0
以下是翻译好的内容：
"最佳方法是创建一个查找表，然后使用查找函数来检索正确的值。
| A | B | |
|---|---|---|
| 1 | 0 | 排名 1 |
| 2 | 3 | 排名 2 |
| 3 | 5 | 排名 3 |
=VLOOKUP(value, A1:B3, 2)
A1:A3 包含下限，B1:B3 包含相应的排名。"
英文:
The best way to do this is by creating a lookup table and then using a lookup function to retrieve the correct value.
| A | B | |
|---|---|---|
| 1 | 0 | Rank 1 |
| 2 | 3 | Rank 2 |
| 3 | 5 | Rank 3 |
=VLOOKUP(value,A1:B3,2)
A1:A3 contains the lower bounds and B1:B3 contains the corresponding rank.
答案2
得分: 0
=sort(map(unique(tocol(A2:A;1));lambda(Σ;let(Λ;sumif(A:A;Σ;B:B);
{ifs(Λ>4;"大";Λ>2;"中";1;"小")}\Σ\Λ})));3;)
英文:
<!-- language-all: js -->
You may try:
=sort(map(unique(tocol(A2:A;1));lambda(Σ;let(Λ;sumif(A:A;Σ;B:B);
{ifs(Λ>4;"Big";Λ>2;"Medium";1;"Small")\Σ\Λ})));3;)
答案3
得分: 0
这里有一个解决方案。
Sheet1
- 列A:B用于输入人的姓名和数值。
- 列D:F计算每个人的数值总和（SUMIFS）以及他们的等级/职称类别（IFS）。
Sheet2
-
使用QUERY提取每列并按数值总和排序。
英文:
Here is a solution.
Sheet1
- Columns A:B are used to input the person's name and value.
- Columns D:F calculate the sum of value for each person (SUMIFS) along with their rank/title category (IFS).
Sheet2
-
Used QUERY to pull each column and sort by the sum of value.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论