Excel:某些行内的动态范围+数据验证更新 [英] Excel: Dynamic range within certain rows + data validation update

问题描述

我在这里拥有的只是我的大数据集的一小部分，其中包含许多下拉列表和功能，它们在计算周围的一切.我只是超出了这张桌子，试图使它变得比以前更自动，但是我坚持使用了绿色的那一部分.

What I have here is small part of my big data set, with lot of drop downs and functions that are calculating everything around. I am just exceeding this table and trying to make it more automatically than it was before, but I stuck on that part that I put in green.

当我想在右侧(右表)添加一些名为 Model Options (模型选项)>而不是 Reserviert (储备库)的新单元格时，应该使用哪个函数，但另一些名称(将是但要在左侧下拉参考单元格( Model )上自动识别它?我将有一些其他的工作簿，这些工作簿将经常更新，并且必须从该工作簿中查找新"的所有内容，并按一定的顺序在此处填充它，如两个小示例..

Which function might be suitable when I wanna add some new cells on the right (right table) named Model Options > instead of Reserviert but some another name (will be populated later), but to automatically recognize it on left drop down reference cell (Model)? I will have some additional workbook which is gonna be updated frequently, and from that workbook I have to lookup everything what is "new" and to populate it here in certain order like in this two small examples..

到目前为止，我设法找到了OFFSET和INDEX函数:

Up till now I managed to find OFFSET and INDEX functions:

=OFFSET(Sheet1!$B$1;1;0;COUNTA(Sheet1!$B:$B)-1;2) or `=$A$2:INDEX($A:$A,COUNTA($A:$A))`

..但是没有用.我的数据集(T和U列)不是从顶行开始，而是在中间某处(其他数据在此前后)，这给我带来麻烦，尤其是当我包括新行时……

..but it didnt work. My Data set (Columns T and U) are not starting from the top rows, but somewhere in the middle (other data is before and after it), and that gives me trouble especially when I am including new rows...

希望我很清楚问题.

推荐答案

好吧，您不必使用完整的列范围.

Well, you dont have to use full column ranges.

对于第一个列表，您只需要在 OFFSET 中添加 COUNTBLANK ，这样该列表将跳过空白单元格.这不适用于数据条目之间的空白单元格.

For the first list you just have to add a COUNTBLANK in the OFFSET, so that list skips the blank cells. This won't work for blank cells in between the data entries.

=OFFSET(T14:T18,COUNTBLANK(T14:T18),,COUNTA(T14:T18)) 

关于第二个列表，您可以使用

As for the second list you can use

=OFFSET(Tabelle1!T20:T26,,,COUNTA(Tabelle1!U20:U26))

在这里，我使用了代码"列来计算条目数.您可以通过修改 COUNTA 功能中的范围将其移至端点列.这只是为了显示更多的可能性.

Here I used the code column to count the number of entries. You can shift it over to the endpoint column by modifying the ranges in the COUNTA-function. This is just to show of some further possibilities.

希望我对我的回答很清楚.:)如果我没赶到现场，请告诉我.

Hopefully i was clear with my answer. :) If i didn't hit the spot, let me know.

这篇关于Excel:某些行内的动态范围+数据验证更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！