google-sheets - Copy value from a cell to the rest of a colum
问题描述
Is there someone who can help me to break down this long formula and ,by examining the independent elements, help us to understand how it works?
It takes two contiguous columns, in this case A and B. First a value from A is taken. If there is a next value down A, it will repeat the actual value until that row. If there's no values left in A, it return "" (blank)
So, in "B1" we write this very long formula:
ArrayFormula(IF(ROW(A:A)<=MATCH(2;1/(A:A<>"");1);LOOKUP(ROW(A:A);ROW(A:A)/IF(A:A<>"";TRUE;FALSE);A:A);))
The result is like this:
A | B |
---|---|
foo | foo (here goes the formula) |
foo | |
bar | bar |
bar | |
bar | |
qux | qux |
" " | |
" " | |
" " | |
" " (blank cells continue till the end of column) |
This array is quite useful, but I guess if there is a cleaner formula, or a better approach to this task... I suspect thats this formula is a turnaround and Spreadsheets must have an elegant resource to get done this kind of task.
解决方案
利用:
=ARRAYFORMULA(IF(B:B="",, VLOOKUP(ROW(A:A), IF(A:A<>"", {ROW(A:A), A:A}), 2, 1)))
推荐阅读
- python - cs50扫雷算法,在迭代过程中设置改变大小
- image-processing - 提高 SSIM、PSNR 等过滤器的 ffmpeg 精度
- javascript - 如何使用 navigation.navigate -react native 在深度嵌套的导航器中设置特定屏幕
- javascript - 在具有本地存储的两个样式表之间切换
- python - 窃取命令 Discord.Py
- numpy - 在不升级 tf 的情况下将 tensorflow 数据集转换为 numpy(1.15 版)
- python - 如果在后台执行脚本,如何在 Blender Python 上使用旋转运算符?
- flutter - “预期 1 个必需参数,但找到 0 个”错误颤动
- html - 为什么元素在没有悬停的情况下褪色和动画?(负载)
- sql-server - 通过 R 脚本对 SQL Server 进行 Power Query 表,为什么那是.0 额外的