Как использовать ИНДЕКС при применении ЕСЛИ, а не ПОИСКПОЗ?

У меня есть два набора данных в LibreOffice Calc, содержащие декартовы координаты объектов на плоскости. Один — это старые данные с количеством объектов X, другой — более новые данные с теми же объектами X + N новых.

Таблица Excel:

      A          B          C          D     ||    E       F        G
      |          |          |          |     ||    |       |        |
1 - New ID  Previous ID  X Value    Y Value  || Old ID  X Value  Y Value
2 -  1a          ?        89.09      73.79   ||   1a     52.60    94.15 
3 -  1b          ?        52.50      94.05   ||   1b     81.20    28.49
4 -  1c          ?        36.72      94.85   ||   2a     91.04    38.93
5 -  2a          ?        81.14      28.38   ||   3a     68.65    84.64
6 -  2b          ?        07.50      46.69   ||   3b     64.50    37.18
7 -  3a          ?        90.72      39.00   ||   4a     18.36    03.65

Для координат каждого старого объекта (столбцы F и G) я хочу просмотреть координаты всех новых объектов (столбцы C и D) и посмотреть, очень ли мало расстояние d между ними (для мне будет достаточно значения меньше 1).

d = SQRT( (X2-X1)^2 + (Y2-Y1)^2 )

Если d ‹ 1, это означает, что это один и тот же объект, и я хочу записать его Old ID в столбец B.

=IF(SQRT((C2-$F$2)^2+(D2-$G$2)^2)<1,$E$2,0)

Приведенная выше формула работает для одного объекта за раз, когда я щелкаю и перетаскиваю, пока не появится идентификатор; однако мне приходится много копировать и вставлять, чтобы продолжать использовать этот метод. Проблема в том, что у меня есть около 260 новых объектов для поиска около 180 старых объектов. Ручное нажатие и перетаскивание слишком трудоемко.

Я не смог придумать способ использовать функцию ВПР или ИНДЕКС, чтобы сообщить Excel: «Для этих значений X и Y в ячейках F2 и G2 проверьте значения в C2 и D2. Если d равно ‹ 1, напечатайте E2. . Если нет, проверьте F3 и G3 и печатайте E3, если это правда, et cetera et cetera, пока не найдете совпадение».

Есть ли у кого-нибудь какие-либо советы о том, как заставить это работать?

Я знаю, что если бы координаты были точно одинаковыми для объекта в старых и новых данных, я мог бы просто использовать комбинацию ПОИСКПОЗ ИНДЕКС, но, к сожалению, они немного отличаются.


coordinates indexing match libreoffice-calc
person Psymon343    schedule 28.01.2015    source источник

Ответы (2)


arrow_upward
4
arrow_downward

В ячейке B2 вам понадобится одна из этих двух формул. Я немного не понимаю, что именно вы ищете, но я уверен, что один из них подойдет:

=IFERROR(INDEX($E$2:$E$7,MATCH(TRUE,INDEX(SQRT((C2-$F$2:$F$7)^2+(D2-$G$2:$G$7)^2)<1,),0)),"No Match")

Or:

=IFERROR(INDEX($E$2:$E$7,MATCH(TRUE,INDEX(SQRT(($C$2:$C$7-F2)^2+($D$2:$D$7-G2)^2)<1,),0)),"No match")
person tigeravatar    schedule 28.01.2015
comment
Эй, тиграватар - Щелчок! ты опередил меня на 3 секунды... и включил проверку ошибок, я забыл об этом.....кстати, я думаю, что это первое - person barry houdini; 28.01.2015
comment
Вау, да, точно такое же решение, ха-ха! - person tigeravatar; 28.01.2015
comment
@tigeravatar @barryh Вы проверяли свои формулы на моих примерах данных? Уравнения, похоже, не работают с моей стороны. Термины внутри ваших квадратных корней $F2:$F$7 выбирают любую ячейку в этом диапазоне, которая находится в текущей строке, т. е. C4-$F$2:$F$7 совпадает с C4-F4. Выполняет ли MATCH поиск в диапазоне $F$2:$F$7, потому что единственная ошибка, о которой я могу думать, заключается в том, что проверяется не весь столбец, а только соответствующая ячейка в строке. Я использую LibreOffice, но эти правила формул не отличаются между ним и Excel... - person Psymon343; 29.01.2015
comment
Формула, безусловно, работает в Excel, ее нужно ввести в массив - я считаю, что вы можете использовать такие формулы в Libre Office, но я могу вскоре попробовать тест .... с помощью C4-$ F $ 2: $ F $ 7 вы получите массив из 6 результатов, по одному для каждой строки в F2:F7, а затем ПОИСКПОЗ находит первую строку, где ваше условие истинно - person barry houdini; 29.01.2015
comment
Извините, мы использовали конструкцию, которая избегает ввода массива (дополнительная функция ИНДЕКС), поэтому вам не нужно вводить массив в Excel... но Libre Office может не работать в этом отношении - позвольте мне проверить - person barry houdini; 29.01.2015
comment
Я попробовал формулу в Libre Office, но не смог заставить ее работать.... но нашел другой способ - см. мой отредактированный ответ - person barry houdini; 29.01.2015
comment
Ах, я не знал, что это не для Excel. Если ответ Барри работает для вас, я удалю этот ответ - person tigeravatar; 29.01.2015

arrow_upward
2
arrow_downward

В Excel эта формула работала для меня

=INDEX(E$2:E$200,MATCH(TRUE,INDEX(SQRT((C2-F$2:F$200)^2+(D2-G$2:G$200)^2)<1,0),0))

Это применяет ваше уравнение к каждой паре в F/G по сравнению с C2 и D2 - MATCH находит первую строку, где это значение равно <1, а INDEX возвращает требуемый идентификатор из столбца E.

В Libre Office я не смог заставить функцию MATCH работать с массивом, но эта версия сделала как надо:

=INDEX(E$2:E$200,SMALL(IF(SQRT((C2-F$2:F$200)^2+(D2-G$2:G$200)^2)<1,ROW(F$2:F$200)-ROW(F$2)+1),1))

подтверждается нажатием CTRL+SHIFT+ENTER

В последней формуле ваше уравнение оценивается для C2 и D2 по каждой строке для значений F и G, если оно возвращает значение ‹ 1, то ROW(F$2:F$200)-ROW(F$2)+1 возвращает относительный номер строки этой строки в диапазоне (например, строка 2 — это первая строка, чтобы она вернула 1, строка 30 — это 29-я строка, чтобы она вернула 29).

Однако многие строки, «соответствующие» SMALL, просто вернут наименьшую из них (функция SMALL здесь предпочтительнее MIN, потому что MIN даст нулевой результат, если строки не совпадают, в этом случае лучше получить ошибку).

Затем функция INDEX возвращает соответствующее значение из этой строки.

person barry houdini    schedule 28.01.2015
comment
Способ придерживаться этого и решить проблему Libre Office :) - person tigeravatar; 29.01.2015
comment
Полагаю, мне следовало упомянуть, что я работал с LibreOffice для начала. @barry, твоя формула Excel работает для меня в Excel, так что, по крайней мере, я могу выполнять эту часть своей работы на своем домашнем ПК. Однако ваша формула LibreOffice Calc вернула любое значение, которое было в столбце E, т. е. в основном дублировало столбец E в столбце B. Я также пытался понять, как работает эта формула, я не уверен, что следую: ROW(F$2:F$200) кажется, возвращает значение 2 независимо от того, где в столбце он оценивается, и ваш оператор IF возвращает значение 1, если это правда и 1 если это ложь? - person Psymon343; 30.01.2015
comment
Похоже, вы, возможно, не подтвердили с помощью CTRL + SHIFT + ENTER — если вы сделаете это правильно, вы получите фигурные скобки, такие как { и }, вокруг формулы — иногда мне было трудно сделать это в Libre Office — мне пришлось зайти во вставку вариант и отметьте поле формулы массива. Я добавлю к моему ответу, чтобы объяснить, как работает эта формула. - person barry houdini; 30.01.2015
comment
привет Барри. CTRL+SHIFT+ENTER, похоже, ничего не дали мне, но ваш совет для Microsoft Excel сработал нормально. - person Psymon343; 02.02.2015