在Excel中查找数据 [英] lookup Data in Excel

问题描述

我需要一个函数来返回产生给定目标值的所有可能的变量集合。
我正在看的是某种一种追求的二维查找功能。有人可以指出我正确的方向吗？

解决方案

可以没有VBA，相当紧凑，像这样。

假设您的100x100表位于B2：CW101中，我们将从A2到A101左侧的数字1到100列表，从B1的顶部再次为1到100到CW1

创建一个下面的单元格列表，从B104开始（说）

  B104 = MAX（（$ A $ 2：$ A $ 101 * 100 + $ B $ 1：$ CW $ 1   

这是一个数组所以按Ctrl-Shift-Enter而不是Enter，大概括号{}应该出现在公式的周围。

然后根据需要复制多行。您还需要将大量数字放在第一个公式上，即在B103中，例如999999。

该公式是计算Rowx100 + Column，但仅适用于每个成功的单元格，并且MAX函数找到最大的结果，排除所有先前的结果，即它一次找到一个目标结果，从右下角开始工作到左上角。 （有一点努力，你可以让它以另一种方式搜索）。

这将给你的结果像9922，它是第99行，第22列，你可以很容易从数字中提取这些值。

希望这是有道理的。

I have a 2 variable 100x100 data table in excel.

I need to have a function that returns all the possible sets of variables that yield a given target value. What I am looking at is some kind of a reursive 2 dimensional lookup function. Can someone point me in the right direction?

解决方案

It can be done without VBA, fairly compactly, like so.

Suppose your 100x100 table is in B2:CW101, and we put a list of numbers 1 to 100 down the left from A2 to A101, and again 1 to 100 across the top from B1 to CW1

Create a column of cells underneath, starting (say) in B104

 B104=MAX(($A$2:$A$101*100+$B$1:$CW$1<B103)*($B$2:$CW$101=TargetValue)*($A$2:$A$101*100+$B$1:$CW$1))

This is an "array" formula,so press Ctrl-Shift-Enter instead of Enter, and curly brackets {} should appear around the formula.

Then copy down for as many rows as you might need. You also need to put a large number above your first formula, ie in B103, eg 999999.

What the formula does is to calculate Rowx100+Column, but only for each successful cell, and the MAX function finds the largest result, excluding all previous results found, ie it finds the target results one at a time, starting from bottom right and working up to top left. (With a little effort you could get it to search the other way).

This will give you results like 9922, which is row 99, column 22, and you can easily extract these values from the number.

Hope this makes sense.

这篇关于在Excel中查找数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！