如何用矩阵公式合并非连续区域以适配自定义UPLOAD函数？

Great question! The core idea of using a matrix-style formula to reconstruct your discontinuous coordinate range for the UPLOAD function is absolutely feasible—but there are a few key details to iron out, since Excel doesn’t have a built-in MERGE function for this exact use case. Let’s break this down:

1. How to Reconstruct the Continuous Range #

To replicate the original A1:D4 structure (with the former B1:B4 data now in H1:H4), you’ll need to use an array formula to stitch together the discontinuous columns into a single continuous range. Here’s how:

If you’re using Excel 365/2021 (which supports dynamic arrays), you can use the CHOOSE function in a single formula to combine the columns in the correct order:

=CHOOSE({1,2,3,4}, A1:A4, H1:H4, C1:C4, D1:D4)

Just enter this into the top-left cell of a blank 4x4 range (e.g., J1), and Excel will automatically spill the result into J1:M4—giving you a continuous range that matches the structure of your original A1:D4.

For older Excel versions (pre-365/2021), you’ll need to enter this as an array formula: select the full 4x4 target range, paste the formula, then press Ctrl+Shift+Enter to confirm.

2. Compatibility with Your UPLOAD Function #

Once you have the reconstructed continuous range, you have two options for calling UPLOAD:

• Direct array reference: If your custom UPLOAD function can accept array outputs (not just physical cell ranges), you can pass the formula directly:

  =UPLOAD(CHOOSE({1,2,3,4}, A1:A4, H1:H4, C1:C4, D1:D4), A6:A9)
  

• Physical range reference: If UPLOAD requires a physical cell range (common for some custom functions that interact with cell values directly), first write the reconstructed array to a blank continuous range (like J1:M4), then reference that range:

  =UPLOAD(J1:M4, A6:A9)
  

3. Critical Checks to Ensure Success #

• Verify row alignment: Make sure the reconstructed range has exactly 4 rows (matching your value range A6:A9), so each row maps correctly to its corresponding value.
• Confirm column order: Double-check that CHOOSE({1,2,3,4}, ...) is pulling columns in the order your UPLOAD expects (A → H (original B) → C → D, which matches the original A1:D4 structure).
• Test for custom function compatibility: If UPLOAD was written to only accept contiguous cell ranges, the physical range reference method is safer than passing the array directly.

In short: Yes, this approach works—you just need to replace the hypothetical MERGE function with a proper array formula to stitch your discontinuous columns back into the required continuous structure.

内容的提问来源于stack exchange，提问作者laloune