Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Ввод нескольких дат в ячейку в несколько строк, ввести плановые даты 25 число предыдущего месяца в зависимости от даты начала и окончания работ друг под другом
 
#1
28.08.2016 06:08:57
Помогите с решением проблемы.
Задача: в таблице больше 10000 строк. Есть даты начала (столбец А) и окончания работ (B) .
Нужно в колонку С ввести плановые даты получения графиков работ от подрядчиков, которые должны приходить  25 числа каждого предыдущего месяца вплоть до месяца окончания работ. Даты надо расположить одну под другой в одной ячейке для каждой строки.
Тут несколько вопросов:
1. Как ввести 25 число предыдущего месяца хотя бы для первого месяца начала работ.(в столбце D нахождение первого дня месяца начала работ. Но вычитать 5 дней чтобы получить 25 число предыдущего неверно, так как кол-во дней в месяцах разное)
2. Как сделать, чтобы дата 25 число проставлялась одна под другой в текстовом формате
3. Как сделать, чтобы дата проставлялась только до месяца, предыдущего перед месяцем окончания работ
4. Как это автоматизировать, чтобы вводить плановые даты либо протягиванием формулы, либо макросом
Я смог только сделать так, что даты переносятся, но в числовом формате. причем в формулу  в ячейке С1 не получилось подставить даты из ячеек А1 и B1 вводил даты  руками и  на этом мои познания закончились. Как я понимаю, тут нужно еще условие задавать проверки даты окончания.

В столбце E - что я хотел бы получить в итоге

Прошу помощи, очень надо!
Изменено: Igrok500 - 28.08.2016 06:15:29
 
 
 
#2
28.08.2016 08:43:00
Цитата
Igrok500 написал: либо макросом
UDF, она же макрос
Код
Function ПОЛУЧИТЬГРАФИК(dt1 As Date, dt2 As Date)
Dim mCount As Integer
Dim dtStart As Date
mCount = DateDiff("m", dt1, dt2, vbMonday) - 1
dtStart = DateSerial(Year(dt1), Month(dt1), 25)
For I = -1 To mCount
    If ПОЛУЧИТЬГРАФИК = Empty Then
        ПОЛУЧИТЬГРАФИК = Format(DateAdd("m", I, dtStart), "dd.mm.yyyy")
    Else
        ПОЛУЧИТЬГРАФИК = ПОЛУЧИТЬГРАФИК & Chr(10) & Format(DateAdd("m", I, dtStart), "dd.mm.yyyy")
    End If
Next
End Function
Согласие есть продукт при полном непротивлении сторон
 
 
 
#3
28.08.2016 17:18:32
Огромное спасибо!
Нет слов, все работает, как я и хотел.
Это просто спасение.
Еще раз благодарю, вы сэкономили не один десяток часов.
Вот бы еще понять , как и почему он работает, но это пока  мне не доступно.
Буду изучать макросы, по мере возможности.
Изменено: Igrok500 - 28.08.2016 17:23:25
 
 
 
#4
28.08.2016 18:35:58
Sanja, не могли бы вы в Вашем макросе ПОЛУЧИТЬГРАФИК , который вы для меня сегодня сделали, сделать  изменения:( Ваш макрос уже применил, заполнил все что требуется, в копии рабочего файла  на домашнем компе. Теперь буду его  на службе применять.

Еще есть задача отслеживать кое-какие сроки, соответственно внести в столбцы:

1.  чтобы макрос в столбец (уже другой), например добавлял плановые сроки   каждый понедельник, от начала работ и до окончания работ, и так же, как в исходном макросе, даты в одной ячейке одна под другой. Пояснение -это отчетность от заказчика  по контролю работы подрядчиков должна быть еженедельная, по понедельникам.
2. то же самое , что и в п.1, только чтобы последняя дата была 5 дней после окончания работ. Это отчетность от подрядчика- еженедельная (промежуточные итоги, и последняя, окончательная. через 5 дней после окончания работ.

Если можно, в комментариях к макросу поясните, пожалуйста, что нужно будет изменить в макросе, если вместо понедельника потребуется поменять день отчетности, для п.1, например каждую пятницу или строго каждые 7 дней от начала работ.(Именно так прописано в регламентах -каждые 7 дней, но за этим сложно следить, когда несколько сотен объектов  одновременно в работе, приходится приводить к конкретному дню недели)

Заранее благодарю.
Изменено: Igrok500 - 28.08.2016 18:44:30
 
 
 
#5
28.08.2016 21:00:04
Уважаемый Igrok500, ознакомьтесь, пожалуйста, с Правилами нашего форума.
Он создан не для решения КОМПЛЕКСНЫХ задач ОДНОГО пользователя, а для решения КОНКРЕТНЫХ вопросов, которые не смог решить сам участник, и, ответы на которые, могут помочь МНОГИМ.
Еще там есть пункт: ОДИН вопрос - ОДНА тема. Думаю разжевывать не надо.
Если хотите все с нуля, с кучей дополнительных "а вот если так или эдак" добро пожаловать в Платный раздел
По вопросу, озвученному в теме, Вы ответ получили.
Спасибо
Согласие есть продукт при полном непротивлении сторон
 
 
 
#6
28.08.2016 22:06:53
Извините за беспокойство, буду решать проблему сам на примере вашего макроса
 
 
 
#7
29.08.2016 10:02:36
Пользовательская функция для п.1 и п.2. Возможны варианты поиска дат по конкретным дням недели или строго каждые 7 дней от начала работ, а так же для подрядчиков
Код
Function ДЕНЬОТЧЕТА(dt1 As Date, dt2 As Date, Optional w As Boolean = False, Optional wd As Integer = 1, Optional pdr As Boolean = False)
'dt1 - дата начала работ
'dt2 - дата окончания работ
'w - способ поиска дат (0 - каждые 7 дней от даты начала работ, 1 - в конкретный день недели).Необязательный. По умолчанию = 0
'wd - номер дня недели (от 1 до 7). Если w = 1. Необязательный. По умолчанию = 1 (понедельник)
'pdr - поиск дат для ПОДРЯДЧИКА (0 - НЕ для подрядчика, 1 - для подрядчика). Необязательный. По умолчанию = 0
Dim fMonday As Date
Dim I As Integer
Select Case w
    Case Is = 0
        fMonday = dt1 + 7
    Case Is = 1
        Do While Weekday(fMonday, vbMonday) <> wd
            fMonday = dt1 + I
            I = I + 1
        Loop
End Select
Do While fMonday <= dt2
    If ДЕНЬОТЧЕТА = Empty Then
        ДЕНЬОТЧЕТА = Format(fMonday, "dd.mm.yyyy")
    Else
        ДЕНЬОТЧЕТА = ДЕНЬОТЧЕТА & Chr(10) & Format(fMonday, "dd.mm.yyyy")
    End If
    fMonday = fMonday + 7
Loop
    If pdr Then ДЕНЬОТЧЕТА = ДЕНЬОТЧЕТА & Chr(10) & Format(dt2 + 5, "dd.mm.yyyy")
End Function
Изменено: Sanja - 29.08.2016 17:57:02
Согласие есть продукт при полном непротивлении сторон
 
 
 
#8
29.08.2016 22:11:03
Sanja, спасибо вам, очень помогли!

Не могли бы вы ответить на вопрос такого рода.
Я на работе сделал в рабочем файле макрос, такой, как вы прислали первый раз, то есть на 25 число.  Файл по горизонтали примерно 130 колонок, столбцы с датами там были другие, их я естественно поменял. Протянул сверху вниз по всем строкам (их на данный момент около 1450). Это я сделал в одном столбце, а потом к нему приравнял несколько (4 или 5 других столбцов. - это плановые даты получения подобных документов)..Файл перед этим отключил от совместного доступа. Включил работу макросов: Файл- Параметры- Параметры макросов - Включить все макросы.
Затем включил совместный доступ. Надо сказать , что по характеру работы приходится одновременно работать с несколькими большими таблицами в экселе., одна База данных, другая и т.д. Так вот, мой коллега вошел в это файл, после того, как я уже все закончил, у него он открылся на его компе, только вначале запросил разрешение на работу макроса, он работал, что-то там делал, и я параллельно тоже. Потом он вдруг говорит, у меня все зависло и сообщение - крах системы.( в смысле экселя.) У него  в этой самой таблице, в которой макрос, открыто окно с VB  и сам файл. и еще открыта другая база, в экселе. Ничего не работает. При попытке закрыть этот файл, появляется окно со зловещей надписью Крах системы или как то так. При попытке закрыть VB - посторонний файл с другой базой тоже не работает, завис. Вообщем, эксель он закрыть не может. Закрыл только диспетчером задач, сняв задачу.
.
Пока он мучился, я несколько раз закрывал и открывал вновь это самый файл с макросом, у меня ни каких проблем. Я ему сказал. чтобы он перезагрузил компьютер - не помогло. При этом. если он открывал другие файлы экселя - у него нет проблем, но как только он запускал этот файл - сразу открывался файл и одновременно - VB и окно с сообщением: Крах системы.
Я чувствую какую-то вину, хотя, не пойму, что  у него могло произойти с этим файлом. До конца рабочего дня он с ним  естественно больше не работал. Но завтра он понадобится ему,  как и мне. Я попросил еще одного человека открыть этот файл по сети, так как мы все с ним работаем в общем доступе и  у него не было проблем с запуском.
Спросить, если честно, не у кого, в чем причина. Может это просто совпадение, но это произошло именно после запуска макроса с моей стороны.

Есть еще один момент в этой истории. Местоположение файла в сети, в папке у нашего руководителя. Предыдущую версию, (без макроса) я сохранил у себя на компе на всякий случай. Как-то недели три назад у меня на компе была странная ситуация, когда мне надо  было в один из файлов, на сетевом диске  вставить примечания из другого файла экселя, расположенного у того же руководителя, эксель зависал. и закрыть я его мог только диспетчером задач. Остальные файлы экселя одновременно работали без проблем, Странно, но у всех других  коллег, работающих в тем файлом, не было проблем.  Попросил помощь отдела IT, но они ничего на смогли понять, в итоге переустановили мне эксель. Помогло.

Все очень длинно, но  вот я хочу спросить: мог ли вчерашний макрос повредить моему коллеге работу экселя или это просто какой -то сбой и совпадение и может ли расположение файла в сети и общий доступ как -то повлиять на такое развитие событий?  Лечится ли такой крах, или надо переустанавливать эксель потерпевшему?
Прошу извинить за столь  долгое повествование.
(У меня дома копия того самого файла, на которым я сегодня утром запустил макрос. На этой копии я вчера тренировался, все работало и сейчас работает, сейчас попробую ваш второй макрос запустить)
Изменено: Igrok500 - 29.08.2016 22:17:08
 
 
 
#9
29.08.2016 22:52:53
Вот, что об этом говорят авторы Excel'я
З.Ы. этот вопрос точно никакого отношения к заявленной теме не имеет
Изменено: Sanja - 29.08.2016 23:01:42
Согласие есть продукт при полном непротивлении сторон
 
 
 
#10
29.08.2016 23:43:55
Вот результат, скрин области из большой таблицы, ваши макросы исправно сработали. Думаю, это пригодится и другим.
Еще раз выражаю  огромную благодарность за помощь!
 
 
 
#11
30.08.2016 08:01:48
Цитата
Igrok500 написал: примерно 130 колонок, ....Протянул сверху вниз по всем строкам (их на данный момент около 1450)
Итого около 190 000 ячеек с пользовательскими функциями...И, я так понимаю, не только с ними.
UDF (пользовательские функции) являются "летучими" (волатильными), т.е. пересчитываются при каждом "чихе" на листе (например изменение значения в ЛЮБОЙ ячейке листа приводит к пересчету ВСЕХ UDF используемых на нем). Так что при небольшом объеме оперативной памяти компьютера возможен и Крах Excel/
При таком объеме данных переходите на "чистые" макросы
Согласие есть продукт при полном непротивлении сторон
 
 
 
#12
30.08.2016 21:15:44
Рано я радовался. Сегодня на работе при входе в эксель при открытии той самой базы и у меня произошел разрушительный сбой с теми же окнами, что и у моего коллеги вчера. Появляется файл и тут же открывается окно VB и сообщение о разрушительном крахе экселя. Дома пока все работает. Может это общий доступ или корпоративный антивирус или еще что. Очень жалко.
 
 
 
Страницы: 1
Читают тему
Наверх