Здравствуйте, уважаемые, форумчяни! Мне нужно реализовать реализовать следующую задачу. Есть таблица, в которой фиксируется у кого находится предмет (G1 - G5). Когда он взял и когда вернул, Необходимо, чтобы под колонкой в жёлтой клетке появлялось "1", если в заданный период времени он у кого то находился и наоборот, если он не у кого не находился в этот период времени - "0". Пробовал найти решение, но я не очень силён в EKCELе , Если использовать условное форматирование, то там тоже надо формулу применить Заданий интервал можно задать по разному, не критично, лишь бы это было доступно для пользования подскажите, пожалуйста, как это реализовать или укажите ссылку, где подобная задача решалось. Заранее благодарен тем, кто откликнется!
Здравствуйте, уважаемые, форумчяни! Мне нужно реализовать реализовать следующую задачу. Есть таблица, в которой фиксируется у кого находится предмет (G1 - G5). Когда он взял и когда вернул, Необходимо, чтобы под колонкой в жёлтой клетке появлялось "1", если в заданный период времени он у кого то находился и наоборот, если он не у кого не находился в этот период времени - "0". Пробовал найти решение, но я не очень силён в EKCELе , Если использовать условное форматирование, то там тоже надо формулу применить Заданий интервал можно задать по разному, не критично, лишь бы это было доступно для пользования подскажите, пожалуйста, как это реализовать или укажите ссылку, где подобная задача решалось. Заранее благодарен тем, кто откликнется!rafsit61
Хороший вопрос! в реальности человек решил, не сдавая предмет, взять его ещё на один срок. Если это всё сильно усложняет задачу, там будет стоять дата, но очень хотелось бы, чтобы всё осталось как есть
Хороший вопрос! в реальности человек решил, не сдавая предмет, взять его ещё на один срок. Если это всё сильно усложняет задачу, там будет стоять дата, но очень хотелось бы, чтобы всё осталось как естьrafsit61
Я в своем решении "Продлен" понимал как будто он держал до дня, пока следующий человек не забрал этот предмет. Еще три допущения: - если в последней строке не стоит дата возврата, то понимается, что предмет всё еще у работника (и в расчетах используется сегодняшнее число); - если не стоит дата "Взял", то понимается, что работник взял предмет не раньше даты "Взял" предыдущего работника (в расчетах ипользуется эта дата); - если в ячейке "Взял" стоит "Продлен", а в ячейке "Взял" следующего работника нет даты, то датой "Сдал" считается дата сдачи предмета следующим работником.
С такими допущениями результат наиболее корректный. Но, конечно, лучше всего иметь нормальные даты.
Проверяйте.
Я в своем решении "Продлен" понимал как будто он держал до дня, пока следующий человек не забрал этот предмет. Еще три допущения: - если в последней строке не стоит дата возврата, то понимается, что предмет всё еще у работника (и в расчетах используется сегодняшнее число); - если не стоит дата "Взял", то понимается, что работник взял предмет не раньше даты "Взял" предыдущего работника (в расчетах ипользуется эта дата); - если в ячейке "Взял" стоит "Продлен", а в ячейке "Взял" следующего работника нет даты, то датой "Сдал" считается дата сдачи предмета следующим работником.
С такими допущениями результат наиболее корректный. Но, конечно, лучше всего иметь нормальные даты.
Revengencer, Спасибо Вам за реакцию на мою просьбу. В принципе всё удовлетворяет моей задаче, Но не много напрярает, что нужно применить дополнительные столбцы. У меня более 100 предметов G1.......> G100. Поэтому всё это будет немного тяжеловато. А если всё таки будет так
Если это всё сильно усложняет задачу, там будет стоять дата
на сколько упроститься задача? Да и таблица всё время растёт вниз до бесконечности , это тоже мне надо учесть. но спасибо Вам большое за ваш способ решения задачи , попробую упростить её не много. Если будут у Вас ещё идея, я с благодарностью её посмотрю, ещё раз , спасибо.
Revengencer, Спасибо Вам за реакцию на мою просьбу. В принципе всё удовлетворяет моей задаче, Но не много напрярает, что нужно применить дополнительные столбцы. У меня более 100 предметов G1.......> G100. Поэтому всё это будет немного тяжеловато. А если всё таки будет так
Если это всё сильно усложняет задачу, там будет стоять дата
на сколько упроститься задача? Да и таблица всё время растёт вниз до бесконечности , это тоже мне надо учесть. но спасибо Вам большое за ваш способ решения задачи , попробую упростить её не много. Если будут у Вас ещё идея, я с благодарностью её посмотрю, ещё раз , спасибо.rafsit61
Да, действительно с "Продлён" с только заморочек, Мне проче было убрать её со строки дат. За час переделал свою таблицу, теперь нужно манипулировать только с датами, задача остаётся прежней. Возможно ли найти более простое решение?
Да, действительно с "Продлён" с только заморочек, Мне проче было убрать её со строки дат. За час переделал свою таблицу, теперь нужно манипулировать только с датами, задача остаётся прежней. Возможно ли найти более простое решение?rafsit61
rafsit61, тогда вариант с UDF (пользовательской функцией)
Только таблицы вам придется переделать так, как сделал я.
Если что, то комменты можно будет почитать в Экселе (Alt + F11, VBAProject(CountG 2.xlsm), Module1) [vba]
Код
Function CountG(Dates, Date1, Date2) '1. аргументы: Dates - диапазон фамилий и дат "Взял" и "Сдал", 'Date1 - первая контрольная дата, Date2 - вторая контрольная дата Dim i As Integer, x As Integer, k As Integer, iCell '2. для работы функции необходимо, чтобы везде стояли даты "Взял", 'а в датах "Сдал" должна стоять либо дата, либо "Продлён", кроме 'последней строки - там может оставаться пустое место
'3. вычисление количества непустых строк (процедура необходима на случай, 'если выделенный диапазон захватывает пустые строки, а в ячейке "Сдал" 'последнего работника не стоит дата) For i = 1 To Dates.Rows.Count If Dates(i, 1) <> "" Then k = k + 1 Next
For i = 1 To k - 1 '4. вместо "Продлён" берется дата "Взял" следующего работника If Dates(i, 3) = "Продлён" Then If Dates(i, 2) <= Date2 And Dates(i + 1, 2) >= Date1 Then x = x + 1 Else If Dates(i, 2) <= Date2 And Dates(i, 3) >= Date1 Then x = x + 1 End If Next i If Dates(k, 3) = "" And Dates(k, 2) <= Date2 And Date >= Date1 Then x = x + 1 '5. если в ячейке "Сдал" последнего работника не стоит дата, то 'берется текущая дата
'6. функция возвращает 1, если количество совпадений с контрольными 'датами равно или превышает 1 (если надо, чтобы функция возвращала это 'количество, то поставьте перед if такой апостроф, как перед комментариями) If x > 0 Then x = 1 Else x = 0 CountG = x
End Function
[/vba]
2-ой лист.
Хорошо всё проверьте.
rafsit61, тогда вариант с UDF (пользовательской функцией)
Только таблицы вам придется переделать так, как сделал я.
Если что, то комменты можно будет почитать в Экселе (Alt + F11, VBAProject(CountG 2.xlsm), Module1) [vba]
Код
Function CountG(Dates, Date1, Date2) '1. аргументы: Dates - диапазон фамилий и дат "Взял" и "Сдал", 'Date1 - первая контрольная дата, Date2 - вторая контрольная дата Dim i As Integer, x As Integer, k As Integer, iCell '2. для работы функции необходимо, чтобы везде стояли даты "Взял", 'а в датах "Сдал" должна стоять либо дата, либо "Продлён", кроме 'последней строки - там может оставаться пустое место
'3. вычисление количества непустых строк (процедура необходима на случай, 'если выделенный диапазон захватывает пустые строки, а в ячейке "Сдал" 'последнего работника не стоит дата) For i = 1 To Dates.Rows.Count If Dates(i, 1) <> "" Then k = k + 1 Next
For i = 1 To k - 1 '4. вместо "Продлён" берется дата "Взял" следующего работника If Dates(i, 3) = "Продлён" Then If Dates(i, 2) <= Date2 And Dates(i + 1, 2) >= Date1 Then x = x + 1 Else If Dates(i, 2) <= Date2 And Dates(i, 3) >= Date1 Then x = x + 1 End If Next i If Dates(k, 3) = "" And Dates(k, 2) <= Date2 And Date >= Date1 Then x = x + 1 '5. если в ячейке "Сдал" последнего работника не стоит дата, то 'берется текущая дата
'6. функция возвращает 1, если количество совпадений с контрольными 'датами равно или превышает 1 (если надо, чтобы функция возвращала это 'количество, то поставьте перед if такой апостроф, как перед комментариями) If x > 0 Then x = 1 Else x = 0 CountG = x
Revengencer, Алгаритм, как это реализовать , я себе представляю Вместо формул ЕСЛИ(И(B5<=$C$29;C5>=$A$29);1;0) ……….ЕСЛИ(И(B23<=$C$29;C23>=$A$29);1;0) в одном столбце и формулы ЕСЛИ(СУММ(Y4:Y24)>0;1;0)
В формуле первой строки ЕСЛИ(И(B5<=$C$29;C5>=$A$29);1;0) после проверки на условие дать как-то приращение 2 перейти к следоющей строке проверить на это же условие если соответствует условию видать "1", а если нет, то +2 и перейти ко следующей строке, и так далее, пока не упрётся в пустую ячейку и выдать "0". Но как это реализовать с помощью формул, не знаю.
Revengencer, Алгаритм, как это реализовать , я себе представляю Вместо формул ЕСЛИ(И(B5<=$C$29;C5>=$A$29);1;0) ……….ЕСЛИ(И(B23<=$C$29;C23>=$A$29);1;0) в одном столбце и формулы ЕСЛИ(СУММ(Y4:Y24)>0;1;0)
В формуле первой строки ЕСЛИ(И(B5<=$C$29;C5>=$A$29);1;0) после проверки на условие дать как-то приращение 2 перейти к следоющей строке проверить на это же условие если соответствует условию видать "1", а если нет, то +2 и перейти ко следующей строке, и так далее, пока не упрётся в пустую ячейку и выдать "0". Но как это реализовать с помощью формул, не знаю.rafsit61
Revengencer, К сожеленью таблицу я менять не могу, это обще принятые типографские бланки, я просто в цифровая форма их скопировал, потом их мне рас печатывать на стандартные бланки
Revengencer, К сожеленью таблицу я менять не могу, это обще принятые типографские бланки, я просто в цифровая форма их скопировал, потом их мне рас печатывать на стандартные бланкиrafsit61
rafsit61, ну, раз такое дело, тогда такой вариант.
"Продлён" из кода убрал, раз уж вы в нужном файле всё это заменили.
Функция должна корректно работать, если во всех ячейках с датами стоят даты в формате "Дата" (кроме даты "Сдал" последнего работника - она может быть пустой).
[vba]
Код
Function CountG(Dates, Date1, Date2) Dim i As Integer, x As Integer, k As Integer
For i = 2 To Dates.Rows.Count Step 2 If Dates(i, 1) <> "" Then k = k + 1 Else Exit For End If Next
For i = 2 To k - 2 Step 2 If Dates(i, 1) <= Date2 And Dates(i, 2) >= Date1 Then x = x + 1 Next i
If Dates(k, 2) = "" And Dates(k, 1) <= Date2 And Date >= Date1 Then x = x + 1 Else If Dates(k, 1) <= Date2 And Dates(k, 2) >= Date1 Then x = x + 1 End If End If
If x > 0 Then x = 1 Else x = 0 CountG = x
End Function
[/vba]
rafsit61, ну, раз такое дело, тогда такой вариант.
"Продлён" из кода убрал, раз уж вы в нужном файле всё это заменили.
Функция должна корректно работать, если во всех ячейках с датами стоят даты в формате "Дата" (кроме даты "Сдал" последнего работника - она может быть пустой).
[vba]
Код
Function CountG(Dates, Date1, Date2) Dim i As Integer, x As Integer, k As Integer
For i = 2 To Dates.Rows.Count Step 2 If Dates(i, 1) <> "" Then k = k + 1 Else Exit For End If Next
For i = 2 To k - 2 Step 2 If Dates(i, 1) <= Date2 And Dates(i, 2) >= Date1 Then x = x + 1 Next i
If Dates(k, 2) = "" And Dates(k, 1) <= Date2 And Date >= Date1 Then x = x + 1 Else If Dates(k, 1) <= Date2 And Dates(k, 2) >= Date1 Then x = x + 1 End If End If
Revengencer, Огромное Вам спасибо, Попробую всё это перенести на основную таблицу. Правда я с макросами ещё не работал, но похоже без них мне не обойтись, буду осваивать , воспользуюсь Вашей ссылкой. Спасибо Вам.
Revengencer, Огромное Вам спасибо, Попробую всё это перенести на основную таблицу. Правда я с макросами ещё не работал, но похоже без них мне не обойтись, буду осваивать , воспользуюсь Вашей ссылкой. Спасибо Вам.rafsit61
как вы и написали. Разобрался, как приладить её под мою основную таблицу. Всё вроде работает. Класс! Только при одной ситуации (ситуация 5) это не работает, но я её и не оговаривал в условии. Одни предметы берут чаще, другие реже и со временем образуется большая разница в длине колонок и время от времени их выравнивают, оставляя, пустые ячейки. Я показал это в приложенном файле. Если подкорректировать будет сложно, я думаю заполнить их нулями. Ещё раз Вам огромное спасибо, я открыл для себя eщё , что-то новое в EXCELе.
Revengencer, Протестировал все ситуации, их 4, Роботает
как вы и написали. Разобрался, как приладить её под мою основную таблицу. Всё вроде работает. Класс! Только при одной ситуации (ситуация 5) это не работает, но я её и не оговаривал в условии. Одни предметы берут чаще, другие реже и со временем образуется большая разница в длине колонок и время от времени их выравнивают, оставляя, пустые ячейки. Я показал это в приложенном файле. Если подкорректировать будет сложно, я думаю заполнить их нулями. Ещё раз Вам огромное спасибо, я открыл для себя eщё , что-то новое в EXCELе.rafsit61
Желательно, чтобы в выделенный диапазон не попадали всякие левые ненужные ячейки, хотя я вроде настроил так, чтобы ненужные ячейки игнорировались (это нужно для правильного подсчета количество строк в диапазоне с датами).
В общем, смотрите.
rafsit61, это тоже поправимо.
Желательно, чтобы в выделенный диапазон не попадали всякие левые ненужные ячейки, хотя я вроде настроил так, чтобы ненужные ячейки игнорировались (это нужно для правильного подсчета количество строк в диапазоне с датами).
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
