2016 - Создание "сложных" правил для выделения ячеек в excel 2016

[Tolea3]

у нас на работе поставили турникет и всем раздали карточки с чипами. Этот турникет через програму генерирует отчеты в excel файл. Шапку документа не бирём в счет, одна строка содержит точное время, направление (вход/выход), номер карточки и имя владелица. Мне надо сделать красный фон всех ячеек ряда (до владелица карточки) в тех случеях если сотрудник имеет вход поже 08:35:00 или выход ранише 17:25:00. Хочется автоматизировать процес ибо сотрудников 100+ и обрабатывать вручную всё выходит муторно. Прикрепляю документ для ваших опытов.

14. Judecatori.xlsx

[Iska]

Если «ручками»:пишете в E4 значение 08:35:00, в E5 — значение 17:25:00;умножаете диапазон дат на единицу (пример —

Как быстро умножить диапазон значений на одно и тоже число без формул и макросов? - Мир MS Excel

), чтобы значения в ячейках диапазона превратились из текста в число (дата/время в Excel — это на самом деле число, где целая часть обозначает дату, а дробная — время);оставшемуся выделенным диапазону дат задаёте условное форматирование в виде:


Скрытый текст

[spoiler][/spoiler]


получаете следующий результат:


Скрытый текст

[spoiler][/spoiler]




Можно написать макрос, который будет делать подобное автоматически, а Вы сможете поместить его, например, в персональную книгу макросов и вызывать для обработки активного Рабочего листа. Можно написать скрипт, который Вы будете «натравливать» на файл Рабочей книги Excel. Что скажете?

[Iska]

Цитата Iska:



Можно написать скрипт, который Вы будете «натравливать» на файл Рабочей книги Excel. Что скажете?

2016 - Создание "сложных" правил для выделения ячеек в excel 2016

Буду рад если сделаите

[Tolea3]

Цитата Tolea3:



Буду рад если сделаите

2016 - Создание "сложных" правил для выделения ячеек в excel 2016

Я попробую. Ваша задача выбрать, что именно будем делать из:


Цитата Iska:



Можно написать макрос, который будет делать подобное автоматически, а Вы сможете поместить его, например, в персональную книгу макросов и вызывать для обработки активного Рабочего листа. Можно написать скрипт, который Вы будете «натравливать» на файл Рабочей книги Excel.

2016 - Создание "сложных" правил для выделения ячеек в excel 2016

[Iska]

Iska, да мне и то и то понятны тока на половину. так что, что сделаите тому и буду рад

[Tolea3]

Iska, да мне и то и то понятны тока на половину. так что, что сделаите тому и буду рад

[Tolea3]

Ну, давайте попробуем скрипт WSH:



Скрытый текст

[spoiler]Код:

[/spoiler]

Код: Выделить всё

Option Explicit
Const xlDown        = &HFFFFEFE7
Const xlPasteValues = &HFFFFEFBD
Const xlMultiply    = 4
Const xlCellValue   = 1
Const xlBetween     = 1
Dim strSourceFile
Dim objFSO
If WScript.Arguments.Count = 1 Then
	Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")
	strSourceFile = objFSO.GetAbsolutePathName(WScript.Arguments.Item(0))
	If objFSO.FileExists(strSourceFile) Then
		Select Case LCase(objFSO.GetExtensionName(strSourceFile))
			Case "xls", "xlsx"
				With WScript.CreateObject("Excel.Application")
					With .Workbooks.Open(strSourceFile)
						With .Worksheets.Item(1).Range("B7")
							If .Value = "Время" Then
								With .Range("E8")
									.Value = 1
									.Copy
								End With
								With .Parent.Range(.Offset(1, 0), .Offset(1, 0).End(xlDown))
									.PasteSpecial xlPasteValues, xlMultiply, False, False
									With .FormatConditions
										.Delete
										.Add(xlCellValue, xlBetween, CDbl(#08:35:00#), CDbl(#17:25:00#)).Interior.ColorIndex = 3
									End With
								End With
								.Range("E8").ClearContents
								.Range("A1").Select
							Else
								WScript.Echo "Can't find value [Время] in [B7] cell."
								.Select
								.Application.Visible = True
								WScript.Quit 4
							End If
						End With
						.Save
						.Close
					End With
					.Quit
				End With
			Case Else
				WScript.Echo "Probably source file [" & strSourceFile & "] not an Excel Workbook."
				WScript.Quit 3
		End Select
	Else
		WScript.Echo "Can't find source file [" & strSourceFile & "]."
		WScript.Quit 2
	End If
	Set objFSO = Nothing
Else
	WScript.Echo "Usage: cscript.exe //nologo """ & WScript.ScriptName & """ "
	WScript.Quit 1
End If
WScript.Quit 0

Сохраните код в файл с расширением «.vbs». Путь к исходному файлу Рабочей книги указывается аргументом скрипта (также можно просто перетянуть файл Рабочей книги на скрипт в Проводнике).

[Tolea3]

Iska, спс за ваши труды но паралелино дали совет по проше с другова источника



Код:

Код: Выделить всё

создаём новое правило:
=И($C9="вход";$B9>"08:35")+И($C9="выход";$B9<"17:25")
а в поле применяется к, пишем:
=$A$9:$E$100

Результат

[spoiler]

Файл 150143

[/spoiler]

[Tolea3]

Цитата Tolea3:



но паралелино дали совет по проше с другова источника

2016 - Создание "сложных" правил для выделения ячеек в excel 2016

Логично, и отчасти даже более правильно, поскольку совместно сравнивается и соседний столбец Направление. Я всё время делаю по старинке, забывая, что уже давно можно использовать не только значение, но и формулу.



Тем не менее, в указанной формуле сравнивается не дата/время с дата/время, а строка со строкой. В данном случае, скорее всего, сие не будет являться необходимым условием, поскольку строки со временем у Вас все одинаковой длины, и час предшествует минутам, а те — секундам, но в ином случае моё замечание насчёт перевода строк в числа:


Цитата Iska:



умножаете диапазон дат на единицу (пример — Как быстро умножить диапазон значений на одно и тоже число без формул и макросов? - Мир MS Excel), чтобы значения в ячейках диапазона превратились из текста в число (дата/время в Excel — это на самом деле число, где целая часть обозначает дату, а дробная — время);

2016 - Создание "сложных" правил для выделения ячеек в excel 2016

остаётся существенным.




Цитата Tolea3:



чуствую завтра все начнут писать обяснителиные за красные полоски

2016 - Создание "сложных" правил для выделения ячеек в excel 2016

Э… Сегодня, полагаю.



P.S. Tolea3, переписать скрипт на использование формулы — дабы не вводить её в каждую новую Рабочую книгу «ручками»?

[Iska]

Iska, я рад что у меня нет красных полосок. я в отпуске



Iska, скриптик сделаете?