Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Расписание / 24 сообщений из 24, страница 1 из 1
25.05.2012, 20:42
    #37812510
talev
talev
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
Добрый день всем!
Возникла некая проблема. У меня есть рассписание вылета самолётов, по которому я веду некий отчет.
Но на некоторых направлениях есть начало и окончание навигации по датам, разное время прибытия по разным дням недели. Необходимо исходя из даты вылета, номера вылетающего рейса, дня недели вылета в столбец "Дата прибытия" подставить время прибытия рейса. Перепробовал все возможные варианты написания функции "ЕСЛИ", но к сожалению написать грамотно не удалось. Может кто подскажет что, в каком направление двигаться или какая функция ещё могла бы помочь.
Пример во вложении.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.05.2012, 22:26
    #37812563
Shocker.Pro
Shocker.Pro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
Одной ЕСЛИ тут не обойдешься.
Вообще, проще было бы макросом-циклом или через SQL-запрос.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.05.2012, 22:53
    #37812580
Serge 007
Serge 007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
talev,

так надо?
Код: vbnet
1.
=СУММПРОИЗВ((A2=Расписание!E$3:E$52)*(Расписание!G$3:G$52<B2)*(B2<Расписание!H$3:H$52)*Расписание!Q$3:Q$52)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.05.2012, 23:05
    #37812588
Shocker.Pro
Shocker.Pro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
Не, не так.
Еще надо учитывать дни недели.
к примеру рейс УН 141
у тебя получился 6:55
как сумма двух времен прибытия 12:30+18:25
а надо только одно выбрать, соответствующее дню недели

в общем, еще чуть-чуть и ключик наш )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.05.2012, 23:26
    #37812602
Serge 007
Serge 007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
Shocker.Pro,

спасибо, я сначала не понял при чём здесь день недели :)

Код: vbnet
1.
=СУММПРОИЗВ((A2=Расписание!E$3:E$52)*(Расписание!G$3:G$52<B2)*(B2<Расписание!H$3:H$52)*(ТЕКСТ(ДЕНЬНЕД(B2;2);0)=Расписание!I$3:O$52)*Расписание!Q$3:Q$52)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.05.2012, 23:33
    #37812612
Serge 007
Serge 007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
Добавил проверку на отсутствие заявленного рейса и включил пограничные даты
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.05.2012, 23:47
    #37812622
Shocker.Pro
Shocker.Pro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
Не в первый раз восхищен твоим мышлением.
Легкость составления подобных формул достигается только просветлением, это не просто опыт.
Мне знакомо это по SQL, на SQL я напишу подобный запрос с легкостью, но мне потребовалось не менее года работы с SQL, чтобы в какой-то момент почувствовать это просветление, когда ты начинаешь просто мыслить другими категориями, думать на другом языке.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.05.2012, 00:11
    #37812640
Serge 007
Serge 007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
Shocker.Pro,

спасибо! :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.05.2013, 19:20
    #38265146
talev
talev
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
Приветствую, Serge 007!
Спасибо за формулу, вроде работает.
Но есть маленькое НО.
Как можно к получившемуся значению прикрепить любой знак, например "+1". Не спрашивай зачем, долго объяснять, но очень надо.
Готов прислать в знак благодарности на шоколадку или на что покрепче))).
С уважением, Алексей!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.05.2013, 19:25
    #38265154
Serge 007
Serge 007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
talevк получившемуся значению прикрепить любой знак, например "+1"
Код: vbnet
1.
=формула&"+1"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.05.2013, 19:33
    #38265163
talev
talev
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
значение времени, которое подставляет формула меняется на числовое значение. Вот в этом и беда.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.05.2013, 20:08
    #38265195
Serge 007
Serge 007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
talev , Вы внятно объяснить задачу можете?
Например так: В ячейке А1 формула, которая возвращает число 5. Надо к ней добавить текст "+1", что бы получилось "5+1"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2013, 10:15
    #38265645
AndreTM
AndreTM
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
talev,
Код: vbnet
1.
=ТЕКСТ(формула;"чч:мм") & " + 1"

Как я понимаю, там не один знак надо добавлять... Надо же указывать, что рейс прилетает на следующий день, в другой часовой пояс и т.п. :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2013, 12:14
    #38265962
talev
talev
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
AndreTM, точно так.
Как таковой часовой пояс не надо рассчитывать, а +1 означает, что рейс прибывает следующим числом.
Прицепить к значению я могу, проблема в том, что полученный результат из первой формулы (=ЕСЛИ(ЕНД(ВПР(A22;Расписание!E$3:E$78;1;));"нет рейса";СУММПРОИЗВ((A22=Расписание!E$3:E$78)*(Расписание!G$3:G$78<=B22)*(B22<=Расписание!H$3:H$78)*(ТЕКСТ(ДЕНЬНЕД(B22;2);0)=Расписание!I$3:O$78)*Расписание!Q$3:Q$78)), а именно формат времени, "прицеп" переводит в число - "0,694444444444445+1". Если поставить +1 в листе Расписание изначально во времени прибытия, соответственно формула уже не считает. Пробовал условным форматированием, но либо голова не там растет, либо руки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2013, 15:03
    #38266470
Serge 007
Serge 007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
Наконец-то понятно стало что Вам нужно

Код: vbnet
1.
=ЕСЛИ(ЕНД(ВПР(A22;Расписание!E$3:E$78;1;));"нет рейса";ТЕКСТ(СУММПРОИЗВ((A22=Расписание!E$3:E$78)*(Расписание!G$3:G$78<=B22)*(B22<=Расписание!H$3:H$78)*(ТЕКСТ(ДЕНЬНЕД(B22;2);0)=Расписание!I$3:O$78)*Расписание!Q$3:Q$78);"ч:мм"))&" +1"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2013, 15:27
    #38266531
AndreTM
AndreTM
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
talev +1 означает, что рейс прибывает следующим числом.Это (добавку "+1"), кстати, тоже можно рассчитывать, чтобы не напрягаться...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2013, 17:32
    #38266896
talev
talev
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
Чисто технически, если мы в конце формулы ставим &Расписание!T$3:T$78 а не &"+1", формула должна подтягивать то значение, которое указано в диапазоне T$3:T$78?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2013, 19:23
    #38267138
talev
talev
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
[/quot]Это (добавку "+1"), кстати, тоже можно рассчитывать, чтобы не напрягаться...[/quot]
Как?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2013, 20:00
    #38267179
AndreTM
AndreTM
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
talev,

"Чисто технически" - получится примерно такое (подчёркивания уберите):
Код: vbnet
1.
2.
3.
4.
5.
=ЕСЛИ(ЕНД(ВПР(A22;Расписание!E$3:E$78;1;));"нет рейса";_
ТЕКСТ(СУММПРОИЗВ((A22=Расписание!E$3:E$78)*(Расписание!G$3:G$78<=B22)*(B22<=Расписание!H$3:H$78)*_
(ТЕКСТ(ДЕНЬНЕД(B22;2);0)=Расписание!I$3:O$78)*Расписание!Q$3:Q$78);"ч:мм"))&_
ЕСЛИ(СУММПРОИЗВ((A22=Расписание!E$3:E$78)*(Расписание!G$3:G$78<=B22)*(B22<=Расписание!H$3:H$78)*_
(ТЕКСТ(ДЕНЬНЕД(B22;2);0)=Расписание!I$3:O$78)*(Расписание!Q$3:Q$78-Расписание!P$3:P$78))<0;" +1";"")

Но, по идее, надо бы вообще расписание задать именованным диапазоном и работать через имя. Кроме того, поскольку в "Отчёт" могут потребоваться данные из расписания не один раз (как видно выше) - то на листе с отчётом можно вычислить в отдельном столбце порядковый номер соответствующей строки расписания ( индекс , так сказать), а затем уже брать нужные значения с помощью =ИНДЕКС() и т.п.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2013, 20:55
    #38267236
AndreTM
AndreTM
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
talev,

Примерно так:
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2013, 21:32
    #38267273
talev
talev
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
AndreTM,
При сравнении полученного результата с 0 (<0;" +1";""), эффекта нет, я поставил сравнить время 0:00 (<"0:00";" +1";""), "+1" ставиться, но уже по всем строкам, где в принципе не должна. Как результат проверки лист "Отчет", строка 35. В листе Расписание поставил в столбец R значение "+1" напротив того времени, где собственно это должно отображаться.
Первоначальная формула, исходя из заданных параметров находит окончательное время вылета, следующей формулой мы пытаемся это время сравнить с нулями. На мой взгляд это не верно. Любое время будет больше нулей. Если конечно я правильно прочитал формулу.
И про именованный диапазон я не совсем понял.
Если в отчете необходимо для решения добавить столбец для индексирования, не вопрос, но у нас переменные данные для поиска значения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2013, 21:36
    #38267276
talev
talev
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
AndreTM,
пост отправлял, не увидел обновленный с вложением.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2013, 22:31
    #38267332
talev
talev
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
AndreTM,
Благодарю, вроде бы работает.
Правильность подстановки данных проверю уже завтра, единственное сейчас нашел один недочет. Залил в файл полное расписание и реальные вылетные данные. На некоторых рейсах ставит время 0:00, хотя время прибытия другое.
И если можно, поясни пожалуйста, в чем будет польза проиндексированных столбцов для отчета?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.05.2013, 06:48
    #38267550
AndreTM
AndreTM
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расписание
talev,

1) Ну, залил ты данные - а именованный диапазон "Расп" изменил (Вставка-Имя-Присвоить...)?
Что касается нулей - три формулы одновременно врать не могут! Смотрим:
Отчет: "СУ 1484", 05.04.2013 <-> Расписание: Навигация с 31.03.2013 по 31.03.2013
Отчет: "ЮТ 345", 03.04.2013 <-> Расписание: Навигация с 31.03.2013 по 26.03.2013
продолжать?

2) В чём польза? Из примера не видно (я же специально второй столбец с "Направлениями" показал)? Или ты не понимаешь, что выражение ИНДЕКС(<массив>;<строка>;<столбец>) - это прямая ссылка на значение в ячейке, а не вычисления? Все вычисления по сравнениям значений в диапазонах производятся только в столбце "Индекс", остальные столбцы используют конкретные значения.

3) Зачем держать в файле все формулы? Комп же на пересчетах повесится... Ты уж определись, каким методом будешь пользоваться - и остальные убери. Причем желательно не "стирать" значения а удалять целиком столбцы/строки. Также не надо расширять формулы "с запасом" вниз. И что это у тебя автофильтр посреди таблицы наложен?

4) Также неплохо бы ещё поправить/упростить формулы. Например, жестокие преобразования выполняются при проверке дня недели - и только из-за того, что в "Расписании" мало того, что день недели в своём столбце дублирован цифрой, и эта цифра в виде текста - так и в других днях точки стоЯт... Нельзя ли просто дни записывать цифрой-числом, а пустые ячейки - не заполнять? И надо будет попробовать дни недели в битовую маску перевести...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Расписание / 24 сообщений из 24, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 1233ms
Закрыть
start [/forum/topic.php?fid=61&mobile=1&tid=2174793]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 5ms
check topic access:
 5ms
track hit:
 801ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 51ms
get tp. blocked users:
 3ms
others: 331ms
total:  1233ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]