Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Вывод минимального значения через построчное сравнения
 
#1
21.11.2023 13:29:14
Добрый день коллеги! Имеется таблица состоящая из координат (широта, долгота), условного номера и наименования организации. С помощью формулы, которую нашел на просторах форума, можно найти расстояние между координатами. Но дальше для меня задача усложняется, а примера похожего я не нашел, нужно рядом с каждой строчкой выводить условный номер ближайшей организации, при условия минимального расстояния между координатами и совпадения наименований организации. Помогите, пожалуйста, решить мою задачу.  
Изменено: Guzunda88 - 21.11.2023 14:05:08
 
 
 
#2
21.11.2023 13:39:25
Цитата
Guzunda88 написал:
и совпадения наименований организации
у вас все наименования уникальны. О совпадениях и речи быть не может.
Пришелец-прораб.
 
 
 
#3
21.11.2023 13:40:22
AlienSx, исправил
 
 
 
#4
21.11.2023 13:58:04
Транспонируете таблицу.
В пересечении пишете формулу расчёта расстояния по координатам.
Формулами находите "условный номер ближайшей организации, при условия минимального расстояния между координатами и совпадения наименований организации".
 
 
 
#5
21.11.2023 14:00:48
Цитата
Guzunda88 написал:
исправил
а то, что теперь одни и те же условные номера и наименования ходят под разными координатами - это нормально?
Широта Долгота условный номер наименование организации
55.5968743770655000 37.052195210379600 1851625142 Компания 1
55.6590495822818000 37.997601367851000 1851625142 Компания 1
55.5968649450000000 37.052158633000000 1851625142 Компания 1
55.3263829965953000 38.725123775854400 1851625142 Компания 1
Пришелец-прораб.
 
 
 
#6
21.11.2023 14:04:19
AlienSx, такое может быть, но для наглядности исправил на уникальные
 
 
 
#7
21.11.2023 14:07:14
МатросНаЗебре, значений может быть гораздо больше, слишком большая таблица получится, но спасибо, тоже думал над таким решением
 
 
 
#8
21.11.2023 16:07:56
Guzunda88, тестируйте
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    distance = (p1, p2) =>
        [r = 6371,
        p = Number.PI / 180,
        a = 0.5 - 
            Number.Cos( (p2[Latitude] - p1[Latitude]) * p) / 2 +
            Number.Cos(p1[Latitude] * p) * 
                Number.Cos(p2[Latitude] * p) *
                    (1 - Number.Cos( (p2[Longitude] - p1[Longitude]) * p)) / 2,
        d = 2 * r * Number.Asin(Number.Sqrt(a))][d],
    // находим ближайшую точку и ее номер
    fx = (tbl as table) => 
        [all_others = List.Buffer(Table.ToRecords(tbl)),
        closest = Table.AddColumn(
            tbl, "ближайший номер", 
            (w) => List.Min(
                List.RemoveMatchingItems(all_others, {w}), null, 
                (x, y) => 
                    Value.Compare(distance(x[geo], w[geo]), distance(y[geo], w[geo]))
            )[условный номер]
        )][closest],
    geo = Table.AddColumn(Source, "geo", each GeographyPoint.From([Долгота], [Широта])),
    g = Table.Group(geo, "наименование организации", {"entities", fx}),
    expand = Table.ExpandTableColumn(g, "entities", {"Широта", "Долгота", "условный номер", "ближайший номер"})
in
    expand
Пришелец-прораб.
 
 
 
#9
22.11.2023 07:11:32
вариант
{ }
=ИНДЕКС(C$2:C$201;ПОИСКПОЗ(1=1;МИН(ЕСЛИ(C$2:C$201<>C2;ACOS(SIN(A2*ПИ()/180)*SIN(A$2:A$201*ПИ()/180)+COS(A2*ПИ()/180)*COS(A$2:A$201*ПИ()/180)*COS((B2-B$2:B$201)*ПИ()/180))))=ЕСЛИ(C$2:C$201<>C2;ACOS(SIN(A2*ПИ()/180)*SIN(A$2:A$201*ПИ()/180)+COS(A2*ПИ()/180)*COS(A$2:A$201*ПИ()/180) *COS((B2-B$2:B$201)*ПИ()/180))); ))
 
 
 
#10
22.11.2023 07:36:19
Павел \Ʌ/, если придираться, то у номеров 17, 71 и 117 компании 8 - ошибки. У этих номеров координаты совпадают. ТС не рассказал как правильно расшивать эту неоднозначность. В PQ List.Min возвращает что-то, ему понравившееся (может первое, а может последнее встречное). У вас получился "оригинальный" способ указать на такую неоднозначность  8) Но уж лучше ничего не посчитать, чем что-то ошибочное, так что норм.
Изменено: AlienSx - 22.11.2023 07:37:51
Пришелец-прораб.
 
 
 
#11
22.11.2023 08:03:49
AlienSx, ошибки нет - формула выводит первое встреченное (сверху-вниз) наименьшее значение, исключая "эту строку"
ну а условий выводить все организации, находящиеся в "одном условном здании" не было  )
 
 
 
#12
22.11.2023 10:22:49
Цитата
Павел \Ʌ/ написал:
ошибки нет
видимо, от версии зависит. У меня (365) есть.

Причина - ACOS вылетел за область определения. Вроде начинаешь на листе смотреть - аргумент равен 1. Но, где-то там внутри он слегка превысил 1. Вычел из аргумента единицу и умножил на 1E15. Рез-т: 0.222044604925031 🤷‍♂️
Пришелец-прораб.
 
 
 
#13
22.11.2023 12:36:54
AlienSx, не понятоно о чем речь. У вас моя формула ошибку выдает или это результат PQ?
У меня для объектов в одной точке расстоянме по нулям.

пс: если б использовал функцию ПРОСМОТР порядок был бы другой
 
 
 
#14
22.11.2023 12:42:07
Цитата
Павел \Ʌ/ написал:
У вас моя формула ошибку выдает
ваша формула в вашем файле (где нет PQ) выдает ошибку в моем 365. Я ничего не корректировал. Очевидно, что аргумент ACOS или больше 1 или меньше -1. Можно его завернуть во что-нибудь минимаксное, конечно. Но ошибку я наблюдаю.
Ну это так, констатация факта ошибки при запуске в других условиях. Странно, конечно, но вот так вот может выскочить на пограничных значениях.
Изменено: AlienSx - 22.11.2023 12:44:18
Пришелец-прораб.
 
 
 
Страницы: 1
Наверх