取消将重复的列组重复排列为行 [英] Unpivot repeating groups of columns into rows

问题描述

我有一张纸，上面有这种格式的数据

I have a sheet with data in this format

但是我希望它看起来像这样

but I would like it reshaped to look like this

我尝试使用这个答案，但是结果不是我想要的，我似乎无法解决这个问题.我感觉好像缺少了一些明显的东西，但是使用分组列的需求使我有些不满.我已经习惯了Python Pandas，但我的Excel/Sheets技能却严重不足.

I've attempted Transposing the data using the code provided at this answer however the result is not what I want, and I can't seem to get my head around it. I feel like I'm missing something obvious, but the need to work with grouped columns is throwing me a little. I'm used to Python Pandas, and my Excel/Sheets skills are severely lacking.

我在非常感谢您的帮助！

推荐答案

工作表上有一个名为MK.Help的新标签.您将在单元格A2中找到此公式.

There is a new tab on your sheet called MK.Help. You will find this formula in cell A2.

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(current_data!A3:A&"|"&current_data!B3:B&"|"&current_data!C3:C&"|"&FILTER(current_data!D3:L,REGEXMATCH(current_data!D2:L2,"size"))&"|"&FILTER(current_data!D3:L,REGEXMATCH(current_data!D2:L2,"colour"))&"|"&FILTER(current_data!D3:L,REGEXMATCH(current_data!D2:L2,"quantity"))),"|",0,0),"where Col4<>''"))

注意事项:我应该提到FLATTEN()是我最近才发现的未记录的函数.我相信它打算保留在工作表编程的后端，但是如果我所做的就是您所追求的，那么确实没有一种更有效的方法.如果您对使用未记录的功能感到不满意，请告诉我，我可以尝试为您构建另一种解决方法.我曾与Google的一位工程师进行过交谈，并被告知它并没有进展，但也没有计划使其公开".

Caveat: I should mention that FLATTEN() is an undocumented function that I only recently discovered. I've believe it is intended to remain "hidden" in the back end of the sheets programming, but if what I did is what you're after, there really isn't a more efficient way to do it. If you're uncomfortable using an undocumented function, let me know and I can try to build a different kind of work around for you. I have spoken to an engineer at Google and was told that it wasn't going anywhere, but there are also no plans to make it "public".

这篇关于取消将重复的列组重复排列为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！