Функция для поиска определенного номера, а затем для дальнейшего поиска префикса
У меня есть огромное количество данных для обработки, в которых 4 точки со связанным префиксом нужно вычесть друг из друга. Данные состоят из идентификатора и значения x
Пример ID = 290,12, 290,03, 290,06, 290,09, 300,12, 300,03, 300,06, 300,09, 301,12, 301,03, 301,06, 301,09 (назовем префикс "номер звонка" и суффикс время на часах) Значение X = любое числовое значение для каждого назначенного идентификатора
Что я надеюсь сделать, так это найти первое число перед точкой, то есть 300, а затем вычесть значение 300,06-300,12 в одной ячейке, а в другой ячейке 300,03-300,09.
(Вычитание — это всего лишь пример, то, как мне нужно манипулировать числами, немного сложнее, но я справился с этим)
Это мои настоящие данные, и то, что мне нужно создать, находится справа от необработанных данных. На данный момент делаю это вручную для каждого комплекта "колец"
Кто-нибудь знает, как подойти к этому? Я думаю vlookup, но я не очень хорошо разбираюсь в Excel.
Я попробовал vlookup, но я не знаю, как построить формулу, и у меня закончились идеи.
Любое обучение очень ценится, но, как бы я ни старался, я действительно не понимаю этого. Даже не уверен, в какую ячейку (ячейки) он должен идти.
Ответы 1
Редактировать:
Я обнаружил, что REDUCE в этом случае не требуется, поэтому его можно сократить до:
=SQRT(SUM(((INDEX(B:D,XMATCH(I3+0.09,A:A),SEQUENCE(1,3))-INDEX(B:D,XMATCH(I3+0.03,A:A),SEQUENCE(1,3)))^2)))
Вы можете изменить +0.09 и +0.03 в соответствии со своими потребностями и можете ссылаться на них с помощью LET() для удобства обслуживания:
=LET(id,I3,
_id1,0.09,
_id2,0.03,
SQRT(SUM(((INDEX(B:D,XMATCH(id+_id1,A:A),SEQUENCE(1,3))-INDEX(B:D,XMATCH(id+_id2,A:A),SEQUENCE(1,3)))^2))))
Предыдущий ответ:
=LET(
id,I3,
_id1,0.09,
_id2,0.03,
SQRT(
REDUCE(0, SEQUENCE(1,3),
LAMBDA(x, y,
x+((INDEX(B:D,XMATCH(id+_id1,A:A),y)
-INDEX(B:D,XMATCH(id+_id2,A:A),y))
^2)))))
Эта формула ищет совпадающее значение значения идентификатора I3 + _id1 за вычетом совпадающего значения значения идентификатора + _id2 для столбцов от B до D и добавляет ^2 результатов для каждого столбца. Затем он вычисляет его квадратный корень.
Вы можете изменить _id1 и _id2 по своему усмотрению.
Чтобы сразу рассчитать дельту (как показано), вы можете использовать:
=LET(id,I3,
_id1,0.09,
_id2,0.03,
_id3,0.12,
_id4,0.06,
x,SQRT(SUM((INDEX(B:D,XMATCH(id+_id1,A:A),SEQUENCE(1,3))-INDEX(B:D,XMATCH(id+_id2,A:A),SEQUENCE(1,3)))^2)),
y,SQRT(SUM((INDEX(B:D,XMATCH(id+_id3,A:A),SEQUENCE(1,3))-INDEX(B:D,XMATCH(id+_id4,A:A),SEQUENCE(1,3)))^2)),
(x-y)*1000)
У вас может быть столбец уникальных значений целых чисел и новый столбец, в котором вы ссылаетесь на эти значения как id и перетаскиваете формулу вниз, чтобы получить результат строки за строкой.
В другом столбце вы можете ссылаться на эти столбцы и сортировать по второму столбцу с помощью SORTBY().
Спасибо, а что такое r в этом уравнении?
r должен ссылаться на диапазон, указанный в функции BYROW; ids в этом случае. Переименование r в BYROW/LAMBDA делает r построчной ссылкой на ids.
=IFERROR(DROP(REDUCE(0,A1:A13,LAMBDA(a,b,VSTACK(a,IFERROR(TEXTSPLIT(b,"."),"")))),1),"")это создаст диапазон разлива номера звонка в первом столбце и значение x во втором. Вы можете легко использовать это для (V) поиска вашего значения.