是否可以固定Excel范围对象的方向? [英] Is it possible to fix the direction of Excel's range object?

问题描述

我的问题基于在这个问题中，有人要求进行某种搜索，而我用 VLookup 公式回答了.答案是我想以相反的顺序进行这项工作"，我以为那应该很容易:只需反转 VLookup 所经过的范围"，但似乎不是那么简单:

In that question, one asks for a kind of search, and I've answered with a VLookup formula. The answer to that was "I would like to make this work in the reverse order", where I thought "That should be easy: just invert the range where VLookup runs through", but it seems not to be that simple:

让我们看一下以下两个公式:

Let's have a look at following two formulas:

=VLookup(E2;$A$2:$B$6;2;FALSE)
=VLookup(E2;$A$6:$B$2;2;FALSE)

Excel将这两个公式视为相等，但实际上为什么不行:在这两种情况下，我都提到了一个矩形，该矩形由单元格$ A $ 2，$ B $ 2，$ A $ 6和$ B $ 6定义，以遍历.因此，我不能怪Excel做两次相同的事情.
但是，我用另一种方式表示:在第一个公式中，我说从第2行开始并转到第6行"，而在第二个公式中，我说从第6行开始并向下转到第2行"，但是Excel似乎不明白这一点.

Both formulas are treated as equal by Excel, and in fact, why not: in both cases I mention a rectangle, defined by the cells $A$2, $B$2, $A$6 and $B$6, to run through. So, I can't blame Excel for doing twice the same thing.
However, I mean it in another way: in the first formula, I say "Start at row 2 and go to row 6", while in the second formula I say "Start at row 6 and go down to row 2", but Excel seems not to understand this.

我想对Excel说

当我给您一个范围时，不要自动从上到下，而要遵循我给的方向".

"When I give you a range, don't automatically go from up to down, but follow the direction I've given".

这可能吗?

对于您的信息，我不是在寻找所指问题的答案(使用 index / match 或 vlookup / choose 或其他)，我正在寻找有关一般问题的通用方法(不仅用于 VLookup ，而且用于 Match 或其他搜索功能.

For your information, I'm not looking for an answer on the question I refer to (using index/match or vlookup/choose or whatever), I'm looking for a general approach on my general question (not only to be used for VLookup, but also for Match or other search functions).

推荐答案

根据我的评论:

Microsoft365获得了 XLOOKUP()和 XMATCH()来做到这一点.它们具有一个参数，您可以在其中指定以自下而上的方式而不是自上而下的方式.

Microsoft365 got XLOOKUP() and XMATCH() to do that. They have a parameter where you can specify to look bottom-up instead of top-down.

早期版本的Excel确实具有检索最后一个匹配项的方法，但是从技术上讲，这与自下而上固定搜索方向不同.

Previous versions of Excel do have ways to retrieve the last match, however that is technically not the same as fixing the search direction bottom-up.

这篇关于是否可以固定Excel范围对象的方向?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！