Посчитать разницу между данными внутри найденного массива

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Посчитать разницу между данными внутри найденного массива (Формулы/Formulas)

necola

Дата: Понедельник, 18.05.2020, 19:36 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Здравствуйте!
В результате работы формулы массива я могу определить МАКС значение или сделать СУММ всех найденных значений. Но как вычислить разницу между результатами внутри массива, не могу понять.

Пояснение задачи:
В моем примере это столбец из дата+время. В обычной ситуации (без массивов), разница между временем легко решается функцией СМЕЩ строк.
Например, представим столбец из времени по возрастанию, когда каждая последующая строка либо равна либо больше предыдущей. Для того чтобы вычислить разницу, нужно из следующей строки (получим ее с помощью СМЕЩ на 1) вычесть время текущей строки.
А как добиться такого вычисления, разбирая результаты работы массивов? Если формула по условиям выдает массив строк и в нем как то нужно узнать разницу между строками?

В прикрепленном примере нужно правильно определить время начала перерывов (когда интервал больше 50 минут) отвечающие двум условиям:
1. Массив времени должен учитывать принадлежность к одной категории (Первая, Вторая, Третья).
2. Вычисления должны производиться в течение одного текущего дня. Со следующего дня перерыв считается сначала.

В примере вычисляемая колонка — это "Начало перерыва". В крайней правой колонке я написал как "Должно быть" и комментарии почему.

Если можно, то сделать это нужно стандартными средствами Excel, без VBA и надстроек.
За ранее спасибо всем кто сможет помочь.

К сообщению приложен файл: ____1.xlsx (14.5 Kb)

Ответить

СообщениеЗдравствуйте!
В результате работы формулы массива я могу определить МАКС значение или сделать СУММ всех найденных значений. Но как вычислить разницу между результатами внутри массива, не могу понять.

Пояснение задачи:
В моем примере это столбец из дата+время. В обычной ситуации (без массивов), разница между временем легко решается функцией СМЕЩ строк.
Например, представим столбец из времени по возрастанию, когда каждая последующая строка либо равна либо больше предыдущей. Для того чтобы вычислить разницу, нужно из следующей строки (получим ее с помощью СМЕЩ на 1) вычесть время текущей строки.
А как добиться такого вычисления, разбирая результаты работы массивов? Если формула по условиям выдает массив строк и в нем как то нужно узнать разницу между строками?

В прикрепленном примере нужно правильно определить время начала перерывов (когда интервал больше 50 минут) отвечающие двум условиям:
1. Массив времени должен учитывать принадлежность к одной категории (Первая, Вторая, Третья).
2. Вычисления должны производиться в течение одного текущего дня. Со следующего дня перерыв считается сначала.

В примере вычисляемая колонка — это "Начало перерыва". В крайней правой колонке я написал как "Должно быть" и комментарии почему.

Если можно, то сделать это нужно стандартными средствами Excel, без VBA и надстроек.
За ранее спасибо всем кто сможет помочь.

Автор - necola
Дата добавления - 18.05.2020 в 19:36

AlexM

Дата: Понедельник, 18.05.2020, 19:51 | Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4484

Репутация: 1115 ±

Замечаний: 0% ±

Excel 2003

Кросс

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Ответить

necola

Дата: Понедельник, 18.05.2020, 20:15 | Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

AlexM, там мне не помогли. Данный форум это абсолютный клон того форума ?

Ответить

bmv98rus

Дата: Понедельник, 18.05.2020, 21:01 | Сообщение № 4

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4107

Репутация: 768 ±

Замечаний: 0% ±

Excel 2013/2016

Цитата necola, 18.05.2020 в 20:15, в сообщении № 3 ()

Данный форум это абсолютный клон того форума ?

нет но клoуны гастролируют те же :-)

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Ответить

AlexM

Дата: Понедельник, 18.05.2020, 21:24 | Сообщение № 5

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4484

Репутация: 1115 ±

Замечаний: 0% ±

Excel 2003

necola, в F2 у вас формула

Код

=ЕСЛИ((Лист1!$D$2:$D$58=Лист1!$D2)*(Лист1!$C$2:$C$58=Лист1!$C2);ЕСЛИ(СМЕЩ(Лист1!$E2;1;0)-Лист1!$E2>0,035;Лист1!$E2;"");"")

если ее заменить на

Код

=ЕСЛИ($E3-$E2>0,035;$E2;"")

и протянуть по столбцу, ничего не изменится. Спрашивается, зачем такая большая формула, да еще массивная и еще со СМЕЩ()? Думаю назначение СМЕЩ() затормозить обработку.
Волатильные (Пересчитываемые или Летучие) функции.
На самом деле не понятно что вы хотите. Нужно описать подробно задачу и не давать рекомендации в использовании массивов, функций, это сбивает от правильного понимания.

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Ответить

AlexM

Дата: Понедельник, 18.05.2020, 21:53 | Сообщение № 6

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4484

Репутация: 1115 ±

Замечаний: 0% ±

Excel 2003

В F2 вставить формулу

Код

=ЕСЛИОШИБКА(ЕСЛИ(ABS(ИНДЕКС(E3:E99;ПОИСКПОЗ(C2;C3:C99;))-E2)>0,035;E2*(ИНДЕКС(D3:D99;ПОИСКПОЗ(C2;C3:C99;))=D2);"");0)

формат ячеек время.
Значений 0:00:00 будет больше, чем в вашем примере. вы не везде отметили конец дня.

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Понедельник, 18.05.2020, 23:03

Ответить

AlexM

Дата: Понедельник, 18.05.2020, 22:01 | Сообщение № 7

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4484

Репутация: 1115 ±

Замечаний: 0% ±

Excel 2003

В моем Excel нет функции ЕСЛИОШИБКА(), поэтому использовал другой вариант, более длинный.

Код

=ЕСЛИ(СЧЁТЕСЛИ(C3:C99;C2);ЕСЛИ(ABS(ИНДЕКС(E3:E99;ПОИСКПОЗ(C2;C3:C99;))-E2)>0,035;E2*(ИНДЕКС(D3:D99;ПОИСКПОЗ(C2;C3:C99;))=D2);"");)

К сообщению приложен файл: 1_02.xlsx (14.5 Kb)

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Понедельник, 18.05.2020, 23:04

Ответить

Pelena

Дата: Понедельник, 18.05.2020, 22:11 | Сообщение № 8

Группа: Админы

Ранг: Местный житель

Сообщений: 19179

Репутация: 4418 ±

Замечаний: ±

Excel 365 & Mac Excel

Алексей, автор хочет задействовать ссылки на ячейки умных таблиц. Отсюда и СМЕЩ()
Мой вариант, о-о-очень длинный))
[vba]

Код

=ЕСЛИ(ИЛИ(СУММПРОИЗВ(([день]=[@день])*([категория]=[@категория]))=1;СМЕЩ([@день];1;0)<>[@день]);0;ЕСЛИ(МИН(ЕСЛИ(([день]=[@день])*([категория]=[@категория])*([время]>[@время]);[время]))-[@время]>0,035;ЕСЛИ(((СМЕЩ([@категория];1;0)=[@категория])*(СМЕЩ([@время];1;0)=[@время]))=0;[@время];"");""))

[/vba]

К сообщению приложен файл: _1.xlsx (15.9 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Pelena

Дата: Вторник, 19.05.2020, 10:51 | Сообщение № 9

Группа: Админы

Ранг: Местный житель

Сообщений: 19179

Репутация: 4418 ±

Замечаний: ±

Excel 365 & Mac Excel

Цитата bmv98rus, 18.05.2020 в 21:01, в сообщении № 4 ()

клoуны гастролируют

Это о ком так?

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

necola

Дата: Вторник, 19.05.2020, 13:03 | Сообщение № 10

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Pelena, спасибо за готовое решение! Пусть длинное, но зато работает как надо yes

AlexM, вашу формулу буду также пробовать на производительность, только ссылки в вашей формуле попробую сделать более независимыми (относительными с динамично-расширяемым диапазоном). Спасибо за помощь!