英文:
How to make a start in converting the worksheet to a list?
问题
这是工作表的内容：
| 第一列 | 第二列 | 第三列 | 第四列 |
|---|---|---|---|
| 玛丽 | 彼得 | 山姆 | |
| 苹果 | 1 | 1 | |
| 香蕉 | |||
| 李子 | 1 | 1 |
我想要将其转换成这样：
| 第一列 | 第二列 |
|---|---|
| 苹果 | 玛丽 |
| 苹果 | 彼得 |
| 李子 | 玛丽 |
| 李子 | 山姆 |
我正在使用Office 365并尝试以下公式：
=FILTER(A2:D5,B2:B5=1)
但它不是我预期的结果。哪种修复方法更高效且更容易应用？
英文:
This is the worksheet like this:
| Column A | Column B | Column C | Column D |
|---|---|---|---|
| MARY | PETER | SAM | |
| APPLE | 1 | 1 | |
| BANANA | |||
| PLUM | 1 | 1 |
And I want to convert to be this:
| Column A | Column B |
|---|---|
| APPLE | MARY |
| APPLE | PETER |
| PLUM | MARY |
| PLUM | SAM |
I am using office 365 and tried the
=FILTER(A2:D5,B2:B5=1)
formula, but it is not what I expected.
Which fix can be more efficient and easier to apply?
答案1
得分: 3
以下是您要翻译的内容：
You could try something like this using TOCOL( ) & HSTACK( )
• Formula used in cell A6
=HSTACK(
TOCOL(REPT(A2:A4,IF(B2:D4<>1,NA(),1)),3),
TOCOL(REPT(B1:D1,IF(B2:D4<>1,NA(),1)),3)
)
• Formula used in cell A6
=HSTACK(
TOCOL(IF(B2:D4="",NA(),A2:A4),3),
TOCOL(IF(B2:D4="",NA(),B1:D1),3)
)
Or, break it down into parts using LET( )
=LET(
a,IF(B2:D4="",NA(),1),
b,TOCOL(REPT(A2:A4,a),3),
c,TOCOL(REPT(B1:D1,a),3),
HSTACK(b,c)
)
Below screenshot is an example, not necessarily has to be 1 in cells B2:D4
Or, Taking the whole data and dissecting it.
=LET(
_data,A1:D4,
_name,DROP(INDEX(_data,1,),,1),
_fruits,DROP(INDEX(_data,,1),1),
_body,DROP(_data,1,1),
_errorCheck,IF(_body<>"",1,NA()),
_unfruits,TOCOL(REPT(_fruits,_errorCheck),3),
_unname,TOCOL(REPT(_name,_errorCheck),3),
HSTACK(_unfruits,_unname))
英文:
You could try something like this using <kbd>TOCOL( )</kbd> & <kbd>HSTACK( )</kbd>
• Formula used in cell A6
=HSTACK(
TOCOL(REPT(A2:A4,IF(B2:D4<>1,NA(),1)),3),
TOCOL(REPT(B1:D1,IF(B2:D4<>1,NA(),1)),3)
)
• Formula used in cell A6
=HSTACK(
TOCOL(IF(B2:D4="",NA(),A2:A4),3),
TOCOL(IF(B2:D4="",NA(),B1:D1),3)
)
Or, break it down into parts using <kbd>LET( )</kbd>
=LET(
a,IF(B2:D4="",NA(),1),
b,TOCOL(REPT(A2:A4,a),3),
c,TOCOL(REPT(B1:D1,a),3),
HSTACK(b,c)
)
Below screenshot is an example, not necessarily has to be 1 in cells B2:D4
Or, Taking the whole data and dissecting it.
=LET(
_data,A1:D4,
_name,DROP(INDEX(_data,1,),,1),
_fruits,DROP(INDEX(_data,,1),1),
_body,DROP(_data,1,1),
_errorCheck,IF(_body<>"",1,NA()),
_unfruits,TOCOL(REPT(_fruits,_errorCheck),3),
_unname,TOCOL(REPT(_name,_errorCheck),3),
HSTACK(_unfruits,_unname))
答案2
得分: 0
这是应用 HSTACK/TOCOL 的另一种方法：
=LET(A, A2:A4, in, B2:D4, h, B1:D1,
HSTACK(TOCOL(IF(in<>"" , A, NA()), 2), TOCOL(IF(in<>"" , h, NA()), 2)))
第一个 IF 通过列搜索非空值，而第二个 IF 通过行搜索非空值。然后使用 TOCOL 移除 NA() 值（第二个输入参数为 2）。最后，通过 HSTACK 将每个结果水平堆叠。
英文:
Here another way of applying HSTACK/TOCOl:
=LET(A,A2:A4,in,B2:D4,h,B1:D1,
HSTACK(TOCOL(IF(in<>"",A,NA()),2),TOCOL(IF(in<>"",h,NA()),2)))
The first IF searches for non empty values by columns and the second IF does it by rows. Then it uses TOCOL removing NA() values (2 second input argument). Finally, stack the each result horizontally via HSTACK.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论