Формула ЕСЛИ с датой и выделение ячеек по условию - Мир MS Excel

Регистрация

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

= Мир MS Excel/Формула ЕСЛИ с датой и выделение ячеек по условию - Мир MS Excel

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

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

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула ЕСЛИ с датой и выделение ячеек по условию (Формулы/Formulas)

Формула ЕСЛИ с датой и выделение ячеек по условию

Дата: Вторник, 01.11.2022, 20:18 | Сообщение № 1

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

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

Сообщений: 16

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

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

Добрый вечер, знатоки.

Подскажите, пожалуйста, как можно вписать в действующую формулу (Файл прилагаю) условие - если дата окончания рейса на автомобиль (столбец С) и эта дата к этому автомобилю встречается ещё раз только вначале другого рейса (Столбец B), то дата окончания первого рейса (минус-1).
Суть в том, что: Если рейс начался с 01.01.2020 и закончился 10.01.2020 (у автомобиля) и в этот же день у ЭТОГО ЖЕ автомобиля начался рейс с 10.01.2020 и закончился 15.01.2020 то дата окончания ПЕРВОГО рейса (с 01.01.2020 по 10.01.2020) минус 1 день т.е. дата должна стать 09.01.2020 и он по неё должен искать затраты К АВТОМОБИЛЮ РЕЙСА а не по 10.01.2020. (чтобы исключить дублирование затрат в обе даты (10 число) разных рейсов)
В столбце I (вкладка Затраты к рейсу) я попыталась прописать условие, но формула работает очень криво....

И подскажите, пожалуйста, как можно выделить среди затрат (вкладка Data) те, которые не легли на рейсы? (вкладка Затраты к рейсу)
Заранее спасибо!

К сообщению приложен файл: _v3.xls (100.5 Kb)

Ответить

Дата: Вторник, 01.11.2022, 22:29 | Сообщение № 2

Группа: Проверенные

Ранг: Обитатель

Сообщений: 452

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

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

365

serena6025, а сводная таблица не подойдет?

К сообщению приложен файл: 3784237.xls (144.5 Kb)

Ответить

Дата: Среда, 02.11.2022, 07:41 | Сообщение № 3

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

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

Сообщений: 16

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

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

cmivadwot, не,не подойдет)

Ответить

Дата: Среда, 02.11.2022, 08:13 | Сообщение № 4

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

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

Сообщений: 19174

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

Замечаний: ±

Excel 365 & Mac Excel

Вариант

Код

=ЕСЛИОШИБКА(ЕСЛИ(И(ИНДЕКС(Data!$B:$B;ПОИСКПОЗ('Затраты к рейсу'!D3;Data!$D:$D;0));ИНДЕКС(Data!$B:$B;ПОИСКПОЗ($E$2;Data!$E:$E;0)));СУММЕСЛИМН(Data!$B:$B;Data!$A:$A;">="&B3;Data!$A:$A;"<="&C3-ЕСЛИОШИБКА(ИНДЕКС(D:D;ПОИСКПОЗ(C3;B:B;0))=D3;0);Data!$D:$D;'Затраты к рейсу'!D3);"");"")

в С18 изменила дату, чтобы можно было проверить. Но даты непонятные, рейсы перекрываются, как такое может быть?

К сообщению приложен файл: 3885380.xls (97.5 Kb)

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

Ответить

Дата: Среда, 02.11.2022, 10:22 | Сообщение № 5

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

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

Сообщений: 16

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

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

Pelena, здравствуйте.
Подскажите,пожалуйста,что делать если он дублирует значения ?
Если дата окончания рейса стоит ,например, 10.02.2021 и 10.02.2021 года начался другой рейс,то он все равно дублирует,не отнимая один день от даты когда закончился первый рейс

Ответить

Дата: Среда, 02.11.2022, 10:53 | Сообщение № 6

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

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

Сообщений: 16

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

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

Pelena, большое Вам спасибо!

Ответить

Дата: Среда, 02.11.2022, 11:06 | Сообщение № 7

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

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

Сообщений: 16

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

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

Pelena, а не подскажите по 2 части как в затраты вкладка Data можно вытянуть затраты,которые попадают в промежуток езды рейсов? Может можно проставить сами рейсы если попадает затратыа в диапазон ?

Ответить

Дата: Среда, 02.11.2022, 11:48 | Сообщение № 8

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

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

Сообщений: 19174

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

Замечаний: ±

Excel 365 & Mac Excel

Цитата serena6025, 02.11.2022 в 10:22, в сообщении № 5 (

)

он все равно дублирует

покажите на примере

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

Ответить

Дата: Среда, 02.11.2022, 11:55 | Сообщение № 9

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

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

Сообщений: 19174

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

Замечаний: ±

Excel 365 & Mac Excel

Цитата serena6025, 01.11.2022 в 20:18, в сообщении № 1 (

)

как можно выделить среди затрат (вкладка Data) те, которые не легли на рейсы?

можно условным форматированием с формулой

Код

=($B3<>"")*(СЧЁТЕСЛИМН('Затраты к рейсу'!$B:$B;"<="&$A3;'Затраты к рейсу'!$C:$C;">="&$A6)=0)

К сообщению приложен файл: 3328354.xls (97.5 Kb)

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

Ответить

Дата: Среда, 02.11.2022, 12:40 | Сообщение № 10

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

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

Сообщений: 16

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

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

Pelena, а можно не форматированием,а именно формулой привязкой к рейсу? Чтобы видеть какая затрата к какому рейсу,я тогда,если не будет видно рейса правило задам на формат ячейки.
Я попыталась отнести дату затраты к диапазону езды машины и соотв.к рейсу,но что не так,ощибки бьет....

Ответить

Дата: Среда, 02.11.2022, 12:43 | Сообщение № 11

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

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

Сообщений: 16

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

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

На вкладке Data проставить напротив каждой затраты рейс ,если она попадает под диапазон дат рейса.

Ответить

Дата: Среда, 02.11.2022, 13:56 | Сообщение № 12

Группа: Проверенные

Ранг: Обитатель

Сообщений: 452

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

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

365

Цитата serena6025, 02.11.2022 в 07:41, в сообщении № 3 (

)

cmivadwot, не,не подойдет)

и да в столбце F привязать даты к рейсам формулой, наверно массива.... а потом уж сводную

К сообщению приложен файл: 3784237-1-.xls (129.5 Kb)

Ответить

Дата: Среда, 02.11.2022, 14:11 | Сообщение № 13

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

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

Сообщений: 16

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

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

cmivadwot, дата затраты 17.09,дата рейса 133 в первой строке - с 28.09 по 12.10 ,уже не попадает )

Ответить

Дата: Среда, 02.11.2022, 14:23 | Сообщение № 14

Группа: Проверенные

Ранг: Обитатель

Сообщений: 452

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

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

365

serena6025, нужна формула... наверно массива. может кто соорудит. я не силен.и вот если рядом будут рейсы то тогда сводная.... там не правильная и не рабочая формула в моем файле...не получилось а удалить забыл....

Сообщение отредактировал cmivadwot - Среда, 02.11.2022, 14:33

Ответить

Дата: Среда, 02.11.2022, 14:49 | Сообщение № 15

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

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

Сообщений: 16

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

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

cmivadwot, в любом случае большое спасибо за старания!

Сообщение отредактировал serena6025 - Среда, 02.11.2022, 14:49

Ответить

Дата: Среда, 02.11.2022, 15:00 | Сообщение № 16

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

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

Сообщений: 16

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

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

Pelena,вопрос с дублем снимаю)))нашла выход )

Ответить

Дата: Среда, 02.11.2022, 18:34 | Сообщение № 17

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

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

Сообщений: 16

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

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

Цитата Pelena, 02.11.2022 в 11:55, в сообщении № 9 (

)

можно условным форматированием с формулой

=($B3<>"")*(СЧЁТЕСЛИМН('Затраты к рейсу'!$B:$B;"<="&$A3;'Затраты к рейсу'!$C:$C;">="&$A6)=0)

Подскажите, пожалуйста, если я использую функцию Просмотр

Код

=ПРОСМОТР(2;1/('Затраты к рейсу'!$B$3:$B$29<=A11)/('Затраты к рейсу'!$C$3:$C$29>=A11);'Затраты к рейсу'!$A$3:$A$29)

Как можно привязать данные к автомобилю? Так как формула просто ищет по дате в диапазоне %)
Заранее спасибо!

К сообщению приложен файл: 4877341.xls (108.0 Kb)

Ответить

Дата: Среда, 02.11.2022, 19:48 | Сообщение № 18

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

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

Сообщений: 19174

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

Замечаний: ±

Excel 365 & Mac Excel

Добавьте ещё проверку № автомобиля

Код

=ЕСЛИОШИБКА(ПРОСМОТР(2;1/('Затраты к рейсу'!$B$3:$B$29<=A3)/('Затраты к рейсу'!$C$3:$C$29>=A3)/('Затраты к рейсу'!$D$3:$D$29=D3);'Затраты к рейсу'!$A$3:$A$29);"")

Цитата serena6025, 02.11.2022 в 15:00, в сообщении № 16 (

)

нашла выход )

какой? Моя формула не учитывает дублирующиеся даты

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

Ответить

Дата: Среда, 02.11.2022, 20:49 | Сообщение № 19

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

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

Сообщений: 16

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

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

Pelena, Я просто вернула старую формулу, (подсмотрела как сделали Вы) и прописала в отд.столбце рядом условие- если дата окончания рейса найдена в дате начала и если номер машины найден в столбце поиска (в нашем случае машин), то дата окончания -1 (я добавила столбец отдельно и по нему задала условие а потом эту дату окончания использовала для всего поиска)

Цитата Pelena, 02.11.2022 в 19:48, в сообщении № 18 (

)

Добавьте ещё проверку № автомобиля

=ЕСЛИОШИБКА(ПРОСМОТР(2;1/('Затраты к рейсу'!$B$3:$B$29<=A3)/('Затраты к рейсу'!$C$3:$C$29>=A3)/('Затраты к рейсу'!$D$3:$D$29=D3);'Затраты к рейсу'!$A$3:$A$29);"")

Pelena, Вы гений!!!!! ^_^

^_^

hands

Спасибо Вам огромное!

Ответить

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула ЕСЛИ с датой и выделение ячеек по условию (Формулы/Formulas)

Страница 1 из 1
1

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