Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Посчитать разницу между данными внутри найденного массива - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4256
Репутация: 1044 ±
Замечаний: 0% ±

Excel 2003



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

Автор - AlexM
Дата добавления - 18.05.2020 в 19:51
necola Дата: Понедельник, 18.05.2020, 20:15 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
AlexM, там мне не помогли. Данный форум это абсолютный клон того форума ?
 
Ответить
СообщениеAlexM, там мне не помогли. Данный форум это абсолютный клон того форума ?

Автор - necola
Дата добавления - 18.05.2020 в 20:15
bmv98rus Дата: Понедельник, 18.05.2020, 21:01 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3245
Репутация: 558 ±
Замечаний: 0% ±

Excel 2013/2016
Данный форум это абсолютный клон того форума ?
нет но клoуны гастролируют те же :-)


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Данный форум это абсолютный клон того форума ?
нет но клoуны гастролируют те же :-)

Автор - bmv98rus
Дата добавления - 18.05.2020 в 21:01
AlexM Дата: Понедельник, 18.05.2020, 21:24 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4256
Репутация: 1044 ±
Замечаний: 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 МегаФон, Московский регион.
 
Ответить
Сообщение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;"")
и протянуть по столбцу, ничего не изменится. Спрашивается, зачем такая большая формула, да еще массивная и еще со СМЕЩ()? Думаю назначение СМЕЩ() затормозить обработку.
Волатильные (Пересчитываемые или Летучие) функции.
На самом деле не понятно что вы хотите. Нужно описать подробно задачу и не давать рекомендации в использовании массивов, функций, это сбивает от правильного понимания.

Автор - AlexM
Дата добавления - 18.05.2020 в 21:24
AlexM Дата: Понедельник, 18.05.2020, 21:53 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4256
Репутация: 1044 ±
Замечаний: 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
 
Ответить
СообщениеВ F2 вставить формулу
Код
=ЕСЛИОШИБКА(ЕСЛИ(ABS(ИНДЕКС(E3:E99;ПОИСКПОЗ(C2;C3:C99;))-E2)>0,035;E2*(ИНДЕКС(D3:D99;ПОИСКПОЗ(C2;C3:C99;))=D2);"");0)
формат ячеек время.
Значений 0:00:00 будет больше, чем в вашем примере. вы не везде отметили конец дня.

Автор - AlexM
Дата добавления - 18.05.2020 в 21:53
AlexM Дата: Понедельник, 18.05.2020, 22:01 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4256
Репутация: 1044 ±
Замечаний: 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
 
Ответить
СообщениеВ моем Excel нет функции ЕСЛИОШИБКА(), поэтому использовал другой вариант, более длинный.
Код
=ЕСЛИ(СЧЁТЕСЛИ(C3:C99;C2);ЕСЛИ(ABS(ИНДЕКС(E3:E99;ПОИСКПОЗ(C2;C3:C99;))-E2)>0,035;E2*(ИНДЕКС(D3:D99;ПОИСКПОЗ(C2;C3:C99;))=D2);"");)

Автор - AlexM
Дата добавления - 18.05.2020 в 22:01
Pelena Дата: Понедельник, 18.05.2020, 22:11 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 16766
Репутация: 3619 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Алексей, автор хочет задействовать ссылки на ячейки умных таблиц. Отсюда и СМЕЩ()
Мой вариант, о-о-очень длинный))
[vba]
Код
=ЕСЛИ(ИЛИ(СУММПРОИЗВ(([день]=[@день])*([категория]=[@категория]))=1;СМЕЩ([@день];1;0)<>[@день]);0;ЕСЛИ(МИН(ЕСЛИ(([день]=[@день])*([категория]=[@категория])*([время]>[@время]);[время]))-[@время]>0,035;ЕСЛИ(((СМЕЩ([@категория];1;0)=[@категория])*(СМЕЩ([@время];1;0)=[@время]))=0;[@время];"");""))
[/vba]
К сообщению приложен файл: _1.xlsx(15.9 Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеАлексей, автор хочет задействовать ссылки на ячейки умных таблиц. Отсюда и СМЕЩ()
Мой вариант, о-о-очень длинный))
[vba]
Код
=ЕСЛИ(ИЛИ(СУММПРОИЗВ(([день]=[@день])*([категория]=[@категория]))=1;СМЕЩ([@день];1;0)<>[@день]);0;ЕСЛИ(МИН(ЕСЛИ(([день]=[@день])*([категория]=[@категория])*([время]>[@время]);[время]))-[@время]>0,035;ЕСЛИ(((СМЕЩ([@категория];1;0)=[@категория])*(СМЕЩ([@время];1;0)=[@время]))=0;[@время];"");""))
[/vba]

Автор - Pelena
Дата добавления - 18.05.2020 в 22:11
Pelena Дата: Вторник, 19.05.2020, 10:51 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16766
Репутация: 3619 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
клoуны гастролируют

Это о ком так?


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщение
клoуны гастролируют

Это о ком так?

Автор - Pelena
Дата добавления - 19.05.2020 в 10:51
necola Дата: Вторник, 19.05.2020, 13:03 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

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

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

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

Автор - necola
Дата добавления - 19.05.2020 в 13:03
necola Дата: Среда, 20.05.2020, 14:07 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
AlexM, ваша формула получилась громоздкой, но быстрее, более чем вдвое быстрее. Наверное потому что без массивов :)
 
Ответить
СообщениеAlexM, ваша формула получилась громоздкой, но быстрее, более чем вдвое быстрее. Наверное потому что без массивов :)

Автор - necola
Дата добавления - 20.05.2020 в 14:07
bmv98rus Дата: Среда, 20.05.2020, 15:35 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3245
Репутация: 558 ±
Замечаний: 0% ±

Excel 2013/2016
Это о ком так?
Лен, я прежде всего себя имел в виду. :D


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Это о ком так?
Лен, я прежде всего себя имел в виду. :D

Автор - bmv98rus
Дата добавления - 20.05.2020 в 15:35
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Посчитать разницу между данными внутри найденного массива (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

jooble Яндекс цитирования
© 2010-2020 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!