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

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула ЕСЛИ с датой и выделение ячеек по условию (Формулы/Formulas)
Формула ЕСЛИ с датой и выделение ячеек по условию
serena6025 Дата: Вторник, 01.11.2022, 20:18 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

Добрый вечер, знатоки. :D
Подскажите, пожалуйста, как можно вписать в действующую формулу (Файл прилагаю) условие - если дата окончания рейса на автомобиль (столбец С) и эта дата к этому автомобилю встречается ещё раз только вначале другого рейса (Столбец 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)
 
Ответить
СообщениеДобрый вечер, знатоки. :D
Подскажите, пожалуйста, как можно вписать в действующую формулу (Файл прилагаю) условие - если дата окончания рейса на автомобиль (столбец С) и эта дата к этому автомобилю встречается ещё раз только вначале другого рейса (Столбец 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) те, которые не легли на рейсы? (вкладка Затраты к рейсу)
Заранее спасибо!

Автор - serena6025
Дата добавления - 01.11.2022 в 20:18
cmivadwot Дата: Вторник, 01.11.2022, 22:29 | Сообщение № 2
Группа: Пользователи
Ранг: Участник
Сообщений: 85
Репутация: 5 ±
Замечаний: 0% ±

serena6025, а сводная таблица не подойдет?
К сообщению приложен файл: 3784237.xls(144.5 Kb)
 
Ответить
Сообщениеserena6025, а сводная таблица не подойдет?

Автор - cmivadwot
Дата добавления - 01.11.2022 в 22:29
serena6025 Дата: Среда, 02.11.2022, 07:41 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

cmivadwot, не,не подойдет)
 
Ответить
Сообщениеcmivadwot, не,не подойдет)

Автор - serena6025
Дата добавления - 02.11.2022 в 07:41
Pelena Дата: Среда, 02.11.2022, 08:13 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 18600
Репутация: 4215 ±
Замечаний: ±

Excel 2016 & 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
 
Ответить
СообщениеВариант
Код
=ЕСЛИОШИБКА(ЕСЛИ(И(ИНДЕКС(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 изменила дату, чтобы можно было проверить. Но даты непонятные, рейсы перекрываются, как такое может быть?

Автор - Pelena
Дата добавления - 02.11.2022 в 08:13
serena6025 Дата: Среда, 02.11.2022, 10:22 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - serena6025
Дата добавления - 02.11.2022 в 10:22
serena6025 Дата: Среда, 02.11.2022, 10:53 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

Pelena, большое Вам спасибо!
 
Ответить
СообщениеPelena, большое Вам спасибо!

Автор - serena6025
Дата добавления - 02.11.2022 в 10:53
serena6025 Дата: Среда, 02.11.2022, 11:06 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - serena6025
Дата добавления - 02.11.2022 в 11:06
Pelena Дата: Среда, 02.11.2022, 11:48 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 18600
Репутация: 4215 ±
Замечаний: ±

Excel 2016 & Mac Excel
он все равно дублирует

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


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
он все равно дублирует

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

Автор - Pelena
Дата добавления - 02.11.2022 в 11:48
Pelena Дата: Среда, 02.11.2022, 11:55 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 18600
Репутация: 4215 ±
Замечаний: ±

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

можно условным форматированием с формулой
Код
=($B3<>"")*(СЧЁТЕСЛИМН('Затраты к рейсу'!$B:$B;"<="&$A3;'Затраты к рейсу'!$C:$C;">="&$A6)=0)
К сообщению приложен файл: 3328354.xls(97.5 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
как можно выделить среди затрат (вкладка Data) те, которые не легли на рейсы?

можно условным форматированием с формулой
Код
=($B3<>"")*(СЧЁТЕСЛИМН('Затраты к рейсу'!$B:$B;"<="&$A3;'Затраты к рейсу'!$C:$C;">="&$A6)=0)

Автор - Pelena
Дата добавления - 02.11.2022 в 11:55
serena6025 Дата: Среда, 02.11.2022, 12:40 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - serena6025
Дата добавления - 02.11.2022 в 12:40
serena6025 Дата: Среда, 02.11.2022, 12:43 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - serena6025
Дата добавления - 02.11.2022 в 12:43
cmivadwot Дата: Среда, 02.11.2022, 13:56 | Сообщение № 12
Группа: Пользователи
Ранг: Участник
Сообщений: 85
Репутация: 5 ±
Замечаний: 0% ±

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


и да в столбце F привязать даты к рейсам формулой, наверно массива.... а потом уж сводную
К сообщению приложен файл: 3784237-1-.xls(129.5 Kb)
 
Ответить
Сообщение
cmivadwot, не,не подойдет)


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

Автор - cmivadwot
Дата добавления - 02.11.2022 в 13:56
serena6025 Дата: Среда, 02.11.2022, 14:11 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - serena6025
Дата добавления - 02.11.2022 в 14:11
cmivadwot Дата: Среда, 02.11.2022, 14:23 | Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 85
Репутация: 5 ±
Замечаний: 0% ±

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


Сообщение отредактировал cmivadwot - Среда, 02.11.2022, 14:33
 
Ответить
Сообщениеserena6025, нужна формула... наверно массива. может кто соорудит. я не силен.и вот если рядом будут рейсы то тогда сводная.... там не правильная и не рабочая формула в моем файле...не получилось а удалить забыл....

Автор - cmivadwot
Дата добавления - 02.11.2022 в 14:23
serena6025 Дата: Среда, 02.11.2022, 14:49 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

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


Сообщение отредактировал serena6025 - Среда, 02.11.2022, 14:49
 
Ответить
Сообщениеcmivadwot, в любом случае большое спасибо за старания! :D

Автор - serena6025
Дата добавления - 02.11.2022 в 14:49
serena6025 Дата: Среда, 02.11.2022, 15:00 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - serena6025
Дата добавления - 02.11.2022 в 15:00
serena6025 Дата: Среда, 02.11.2022, 18:34 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

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

=($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)
 
Ответить
Сообщение
можно условным форматированием с формулой

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


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

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


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

Автор - serena6025
Дата добавления - 02.11.2022 в 18:34
Pelena Дата: Среда, 02.11.2022, 19:48 | Сообщение № 18
Группа: Админы
Ранг: Местный житель
Сообщений: 18600
Репутация: 4215 ±
Замечаний: ±

Excel 2016 & Mac Excel
Добавьте ещё проверку № автомобиля
Код
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/('Затраты к рейсу'!$B$3:$B$29<=A3)/('Затраты к рейсу'!$C$3:$C$29>=A3)/('Затраты к рейсу'!$D$3:$D$29=D3);'Затраты к рейсу'!$A$3:$A$29);"")


нашла выход )

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


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеДобавьте ещё проверку № автомобиля
Код
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/('Затраты к рейсу'!$B$3:$B$29<=A3)/('Затраты к рейсу'!$C$3:$C$29>=A3)/('Затраты к рейсу'!$D$3:$D$29=D3);'Затраты к рейсу'!$A$3:$A$29);"")


нашла выход )

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

Автор - Pelena
Дата добавления - 02.11.2022 в 19:48
serena6025 Дата: Среда, 02.11.2022, 20:49 | Сообщение № 19
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

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

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

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


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

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

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


Pelena, Вы гений!!!!! ^_^ hands
Спасибо Вам огромное!

Автор - serena6025
Дата добавления - 02.11.2022 в 20:49
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула ЕСЛИ с датой и выделение ячеек по условию (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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