Как вернуть первую непустую ячейку и если перед ней уже есть значение, вернуть третью непустую и так далее?

4046
Tusk

Как создать функцию, которая бы возвращала первое непустое значение ячейки, но если оно определено в левой ячейке рядом с ней, она перепрыгнула бы 2 ячейки и искала бы следующее значение.

Table:  1 | 2 | 3 | 4 | 5 | 6 | 7 || 29 | 30 | 31 | 32... a | b | | | c | d | || a | c | | | | | a | b | | || a | | | a | b | c | d | e | f | g || a | c | e | g

из DL3: EW4873 - это значения, всегда в парах, но мне нужен только первый элемент значения и только если это значение уже не находится в диапазоне от EX до EK.

Также ячейки EX: FK - это место, куда будут идти формулы.

2
Вы хотите сделать это для каждой строки? Вы можете найти первое текстовое значение в строке 3 с этой формулой `= INDEX (AO3: EU3, MATCH (" * ", AO3: EU3,0))`, но это не относится ко второй части, вы хотите сказать, что хотите найти первое значение, которое появляется в EK3: EX3? barry houdini 12 лет назад 1
Да для каждой строки, и это не отображается в EK3: EX3. Tusk 12 лет назад 0

1 ответ на вопрос

5
barry houdini

ОК, эти диапазоны не перекрываются? В любом случае попробуйте эту версию, отрегулируйте диапазоны по мере необходимости

=INDEX(AO3:EU3,MATCH(1,INDEX(ISNA(MATCH(AO3:EU3,EK3:EX3,0))*(MOD(COLUMN(AO3:EU3)-COLUMN(D3),2)=0)*(AO3:EU3<>""),0),0))

Это ищет первое непустое значение в альтернативных ячейках, AO3, AQ3, AS3 и т. Д., Которое не соответствует ни одному значению, найденному в EK3: EX3

Пересмотренное предложение:

Хорошо, основываясь на ваших комментариях, я предполагаю, что вам все еще нужны непустые данные из альтернативных ячеек, поэтому с данными в AO3: EI3 и с пустым EJ3 попробуйте скопировать эту формулу в EK3, чтобы получить следующее другое значение

=IFERROR(INDEX($AO3:$EH3,MATCH(1,INDEX((COUNTIF($EJ3:EJ3,$AO3:$EH3)=0)*(MOD(COLUMN($AO3:$EH3)-COLUMN($AO3),2)=0)*($AO3:$EH3<>""),0),0)),"")

когда значения заканчиваются, вы получаете пробелы .....

Пересмотренное предложение 2:

Попробуйте эту формулу только в EX3

=IFERROR(INDEX($DL3:$EW3,MATCH(1,INDEX((MOD(COLUMN($DL3:$EW3)-COLUMN($DL3),2)=0)*($DL3:$EW3<>""),0),0)),"")

а затем этот в EY3 скопировал через

=IFERROR(INDEX($DL3:$EW3,MATCH(1,INDEX((COUNTIF($EX3:EX3,$DL3:$EW3)=0)*(MOD(COLUMN($DL3:$EW3)-COLUMN($DL3),2)=0)*($DL3:$EW3<>""),0),0)),"")

Пересмотренная версия для «второй пары» - это должно просто подобрать соответствующие значения из первой формулы

=IFERROR(INDEX($DL3:$EW3,MATCH(1,INDEX((MOD(COLUMN($DL3:$EW3)-COLUMN($DL3),2)=0)*($DL3:$EW3<>""),0),0)+1)&"","")

и этот в EY3 скопирован через

=IFERROR(INDEX($DL3:$EW3,MATCH(1,INDEX((COUNTIF($EX3:EX3,$DL3:$EW3)=0)*(MOD(COLUMN($DL3:$EW3)-COLUMN($DL3),2)=0)*($DL3:$EW3<>""),0),0)+1)&"","")

изменить $EX3:EX3часть в зависимости от расположения формул .....

он возвращает 0 в каждой ячейке из диапазона EK3: EX3. Также отображается сообщение об ошибке, что он не может рассчитать его: «Excel не может рассчитать формулу» Tusk 12 лет назад 0
Хорошо, я должен что-то упустить, куда вы кладете формулу? Я предположил, что он будет идти только один раз в каждой строке, а не в ячейках, на которые ссылается формула barry houdini 12 лет назад 1
Он будет проходить в каждой ячейке / строке от EK3 до EX3, поэтому он получит первое, второе, третье значения ячейки, но никогда не будет повторять значения, приведенные ранее, с EK3 до EX3. Tusk 12 лет назад 0
@Tusk Хорошо, я понимаю, я добавил пересмотренную формулу выше barry houdini 12 лет назад 1
Это близко, пришлось переставить таблицы (скрытые поля, и это доставляло слишком много хлопот формуле). Так что теперь это более чистый стол. = ЕСЛИОШИБКА (ИНДЕКС ($ DL3: $ EW3, MATCH (1, ИНДЕКС ((СЧЕТЕСЛИ ($ EX3: EX3, $ DL3: $ EW3) = 0) * (MOD (колонка ($ DL3: $ EW3) -COLUMN ($ DL3), 2) = 0) * ($ DL3: $ EW3 <> ""), 0), 0)), "") Но это дает циклическую опорную ошибку, и хотя перед перестановкой она считала ее, она повторилась первое значение он нашел все 14 раз :( Для нового пути проверьте ревизию. Tusk 12 лет назад 0
Хорошо, я надеялся, что у вас будет пустая ячейка между диапазоном данных и вашими формулами, если нет, то я думаю, что вам нужна немного другая формула для первой ячейки, см. Пересмотренное предложение 2 выше barry houdini 12 лет назад 1
Это работает как шарм. Ты гений, с крыльями и ореолом :) Tusk 12 лет назад 0
Я думал, что это будет легко, но я не могу найти способ, как выбрать вторую пару значений, таким же образом, как это, поэтому подумал, что если я +1 где-нибудь, это будет работать, но это не так, легкий путь ? Также это может быть повторяемое значение в любом месте Tusk 12 лет назад 0
Просто измените диапазон так, чтобы он начинался со второй пары, то есть измените экземпляры `$ DL3: $ EW3` на` $ DM3: $ EW3` .... и там, где есть `$ DL3`, который должен стать `$ DM3` ..... barry houdini 12 лет назад 1
это нормально работает для части «ex3», но «ey3» отфильтровывает повторяющиеся значения, а копирование «ex3» во все ячейки заставляет повторять первое значение каждый раз. Tusk 12 лет назад 0
У меня все в порядке:) ..... Я понимаю, что вы не можете использовать обе формулы в одних и тех же ячейках, но при условии, что вам нужна версия "второй пары" в EX3, см. Пересмотренный ответ выше ....... barry houdini 12 лет назад 1
Редакция для «второй части» все еще не позволяет ей иметь повторяемое значение. :( Tusk 12 лет назад 0
Вы используете именно ту формулу, которую я предложил? куда вы его кладете и как изменили (если вообще изменили)? Вы ожидаете не повторять значения из «первой пары»? Это не то, как это настроено в данный момент (хотя это могло бы быть), оба они по отдельности автономны (без повторов в каждом наборе) barry houdini 12 лет назад 1
Я помещаю их вместо того, что было до этого, поэтому с EX3 по FK3. Используя точно вашу формулу, и захватывая левую нижнюю точку, скопируйте формулу до FK3, а затем захватите ее вниз. Первая пара не должна повторяться, это правильно, но вторая пара имеет три состояния, и каждое состояние может быть одинаковым для каждого первого значения. Таким образом, второе может и должно быть в состоянии повторить, но вернуть пустое, если оно уже пустое. Надеюсь теперь понятно :) Tusk 12 лет назад 0
Хорошо, я думаю, что я понял, что требование должно быть другим - если это выбор второй части каждой пары, возвращенной предыдущей формулой, например, если первая формула вернула vaklues из `DN3` и` DX3`, эта должна получить "дополнительный значение "т.е. из` DO3` и `DY3`? Это все текстовые значения (или пробелы)? barry houdini 12 лет назад 1
Все текстовые значения, если у него есть первичная пара, и да, если первое значение от DN3, тогда верните DO3 :) Если нет первичного значения, вторичное значение, конечно, пустое Tusk 12 лет назад 0
ОК, я снова изменил последнюю запись, по сути, те же формулы, что и изначально, с +1 в МАТЧЕ, чтобы получить значение из следующей ячейки .... и добавил & "", чтобы убедиться, что пробелы не отображаются как ноль barry houdini 12 лет назад 1
По-прежнему показывает последнее значение вместо пробелов :( Tusk 12 лет назад 0
Может быть, я не достаточно ясно, три состояния: легко, нормально, сложно. Это может быть легко снова в той же строке, но если оно изначально пустое, оно не должно быть легким, оно должно быть пустым. И теперь последнее обновление продолжает повторять последнюю реальную запись, легко через всю строку, не делая ее пустой. Tusk 12 лет назад 0
Он продолжает повторять первое найденное значение по всей строке, а не последнее. Виноват :) Tusk 12 лет назад 0

Похожие вопросы