XLOOKUP с ячейками, заполненными формулами, не работает

XLOOKUP с ячейками, заполненными формулами, не работает
XLOOKUP с ячейками, заполненными формулами, не работает

У меня есть таблица, в которой каждое значение является формулой.

У меня есть следующий XLOOKUP: =XLOOKUP(G3,C3:C28,E3:E28,"").

Столбец C - это проценты, вычисленные по формулам. Этот столбец обозначен как процент, и это целые проценты.

Для G3 я вручную ввожу процент в ячейку. Эта ячейка обозначена как процент.

По какой-то причине при сравнении с G6: XLOOKUP с ячейками, заполненными формулами, не работает поиск работает только наполовину, как показано на рисунке.

Я попытался выяснить, равны ли эти значения, что они равны в соответствии с формулой EXACT(G3,C3), которая возвращает TRUE. При взгляде на изображение это тоже отражается, но потом почему-то больше не равняется внутри XLOOKUP.

Простым решением для этой ситуации было бы добавить пятый параметр с: 1, но это показывает, что формула работает иногда; почему не всегда?

Что происходит не так? Как мне это исправить?

Проблема в том, что вы ищете ТОЧНЫЕ совпадения, но значения в столбце B наверняка не всегда будут такими, поскольку они рассчитаны.

Например, B3 имеет «34,00%», что в простой десятичной форме равно «.3400», ОТОБРАЖАЕМОЕ в ячейке, но в качестве расчетного значения это почти наверняка будет чем-то другим. Примером может быть «.340025698532», который по-прежнему будет отображаться как «34,00%», но сохранит свое реальное значение.

(И есть еще один способ, которым расчетные значения могут отличаться из-за того, как Excel программно берет числа с основанием 10, которые мы вводим, и использует арифметику с основанием 2 для их вычисления, но я думаю, что это немного эзотерично здесь. Имейте в виду, это не «нечасто», просто « эзотерический»).

Поэтому, когда вы ограничиваете XLOOKUP поиском точных совпадений, обычно это не удается, и ваш параметр "" будет как кусок угля на Рождество.

Время от времени может быть совпадение, но...

В (кажущейся) структуре того, что вы делаете, вам почти наверняка нужно превратить G3 в $G$3. И, вероятно, диапазоны нуждаются в одинаковой обработке, но предложение VBasic2008 использовать диапазоны, позволяющие SPILL работать на вас, решит эту часть.

Предложение VBasic2008 использовать округление до нуля десятичных знаков почти правильное, но необходимо упомянуть, что отображаемые проценты, подобные тем, которые вы показываете, НЕ являются целой частью плюс два десятичных знака. Вернее, они представляют собой не целую часть, а четыре знака после запятой. Поэтому, когда вы округляете, вам нужно округлить до 2 знаков после запятой, чтобы можно было искать значение, такое как «45%», для работы. Если вы округлите до 0 знаков после запятой, вы будете искать «.45» в списке записей «.00» (после того, как они будут округлены).

Кстати, применяйте округление не к фактическим данным, а к формуле:

=XLOOKUP($G$3,  ROUND(B1:B26, 2),  E1:E26,  "")

и имейте в виду, что поиск «65%» в списке со значением «60,65%» не удастся даже при округлении, так как округление приведет к «61%».

В результате формула не такая works sometimes; why not always, как у вас. Как картина с часами, она никогда, никогда не работает. Покраска не правильная раз и два в день. Это картина, а не часы, и они никогда не показывают время. Похоже, что он дает правильное время, но поскольку он вообще не дает времени, это не так. Ваша формула в том виде, в каком она разработана, является абсолютной, а не формулой, которая делает то, о чем вы ее просите, и поэтому времена, когда она кажется правильной, не являются таковыми, потому что это всего лишь первая или вторая волосинка от случайного результата, который просто кажется соответствовать. Все, что вам нужно сделать, это использовать этот пятый параметр, поскольку он предназначен для использования, и это будет формула, которая будет делать то, что вы просите.

Что ж, это и тот факт, что ваше значение поиска и значения, которые вы просматриваете, не являются точными совпадениями. Но в этом параметре есть и другие варианты, и они могут позаботиться о несоответствии между вашим значением поиска и данными, не утруждая себя округлением чего-либо. Например, если вы установите этот параметр как «-1», он найдет значение, если оно точно совпадает, или следующее меньшее, чем это значение. «1», и он найдет его или следующее большее значение.


NevaDev, 21 марта 2023 г., 10:49