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

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)

- Microsoft Office (Word, Excel, Outlook и т.д.) (http://forum.oszone.net/forumdisplay.php?f=115)

- - Создание "сложных" правил для выделения ячеек в excel 2016 (http://forum.oszone.net/showthread.php?t=332013)

Tolea3

20-12-2017 00:53 2785693

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

Вложений: 1

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

Iska	20-12-2017 02:11 2785702

Если «ручками»:

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

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

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

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

Tolea3

20-12-2017 02:17 2785706

Цитата:

Цитата Iska

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

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

Iska	20-12-2017 02:19 2785708

Цитата:

Цитата Tolea3

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

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

Цитата:

Цитата Iska

Tolea3

20-12-2017 02:21 2785710

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

Iska	20-12-2017 04:06 2785715

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

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

Код:

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 & """ <Source file>"

        WScript.Quit 1

End If



WScript.Quit 0

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

Tolea3

20-12-2017 04:29 2785716

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

Код:

создаём новое правило:

=И($C9="вход";$B9>"08:35")+И($C9="выход";$B9<"17:25")

а в поле применяется к, пишем:

=$A$9:$E$100

Результат

Файл 150143

Tolea3

20-12-2017 04:37 2785717

Вложений: 1

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

Iska	20-12-2017 05:05 2785720

Цитата:

Цитата Tolea3

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

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

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

Цитата:

Цитата Iska

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

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

Цитата:

Цитата Tolea3

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

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

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

Tolea3

20-12-2017 05:12 2785724

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

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

Iska	23-12-2017 08:46 2786330

Цитата:

Цитата Tolea3

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

Попробуем.

Iska	23-12-2017 12:59 2786348

Попробовал. За-ши-бись: The conditional formatting may be set incorrectly when you use VBA in Excel (Last Updated: 8/01/2017), четыре часа псу под хвост.

Теперь пробуйте Вы:

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

Код:

Option Explicit



Const xlDown        = &HFFFFEFE7

Const xlToRight     = &HFFFFEFBF



Const xlExpression  = 2





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 .Parent.Range(.Offset(1, 0), .Offset(1, 0).End(xlDown))

                                                                        .Value = .Value

                                                                        

                                                                        With .Parent.Range(.Offset(0, -1), .End(xlToRight))

                                                                                .Select

                                                                                

                                                                                With .FormatConditions

                                                                                        .Delete

                                                                                        .Add(xlExpression, , "=ИЛИ(И($C9 = ""вход""; $B9 > " & CDbl(#08:35:00#) & "); И($C9 = ""выход""; $B9 < " & CDbl(#17:25:00#) & "))").Interior.ColorIndex = 40

                                                                                End With

                                                                        End With

                                                                End With

                                                                

                                                                .Parent.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 & """ <Source file>"

        WScript.Quit 1

End If



WScript.Quit 0

Я всё же внёс значимые на мой взгляд изменения:

время в исходном формате текстовых строк я по-прежнему считаю правильным преобразовывать в численные значения;
поэтому в формуле сравниваются не строки текста, а численные значения времени;
я заменил в формуле арифметическое сложение логических значений на функцию Рабочего листа «ИЛИ()»;
раздражающий красный цвет заменил на бледно-оранжевый.

и попутно обнаружил, что некоторые вместо выхода делают ещё один вход:

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

Tolea3

23-12-2017 23:14 2786462

Iska, выдается ошибка при переносе файла .xlsx на .vbs

Видео с ошибкой

https://www.youtube.com/watch?v=62QlxHyCe-o

Iska	24-12-2017 04:06 2786477

Цитата:

Цитата Tolea3

Iska, выдается ошибка при переносе файла .xlsx на .vbs »

Старый добрый Notepad++, не умеющий толком работать с кодировками :).

Tolea3, 00:45, перед вставкой текста кода. Обратите внимание, какая используется кодировка:

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

— UTF-8/65001 без BOM. А должна быть — ANSI/1251.

Посему, перед вставкой текста кода, выполните:

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

Дальше «по тексту».

P.S. Я пользую редактор Far Manager'а:

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

Tolea3

26-12-2017 03:56 2787046

Цитата:

Цитата Iska

UTF-8/65001 без BOM. А должна быть — ANSI/1251. »

Заработало, спс вам огромное

Tolea3

26-12-2017 05:20 2787052

Вложений: 1

Iska, Здраствуйте. Вы мне сильно помогли с тем скриптом для выевления сотрудников когда приходят позно или уходят рано с работы. Но до того как применить ваш скрипт я еше делаю некие манипуляции с файлом потом применяю скрипт. Вожно ль автоматизировать и деиствия которые я делаю до приминения скрипта?

Видео

https://www.youtube.com/watch?v=66pd61GbIGU

Я сам не начальник, а обычный сотрудник и есль я в отпуске или заболею или уиду с работы вопше то начальникам будет туго так как они не шарят в excell. Я хочу что бы из программы сгенерировал отчет, приминил скрипт и всё готова, осталось тока звать на ковер провинившийся.Файл 150261

Iska	28-12-2017 07:58 2787598

Итак:

Удалить или скрыть столбцы C, E, F (тип события Вам не важен?).
Сделать автоподбор ширины столбцов.
Убрать обрамление границ строк 3:5.
Снять заливку с шапки таблицы (Зачем снимать? Можно просто сделать более бледный цвет).
Отсортировать таблицу по Таб. №, Дата, Время (а не по одному ФИО).
Оставить в списке строк таблицы только по первому и последнему появлению Таб. №.
…

?

Tolea3, чей туфля? В смысле — чем формируется отчёт? Дабы не выполнять лишнюю работу — поищите в настройках отчёта пункт наподобие «Формировать только первое и последнее появление ключа».

Цитата:

Цитата Tolea3

приминил скрипт и всё готова, осталось тока звать на ковер провинившийся »

Э… может пусть сразу и выговор с занесением распечатывает — ну, чтоб два раза не вставать?

Tolea3

28-12-2017 18:11 2787763

Вложений: 1

Iska, в настроиках покавырялса ничго путного не нашёл и даже специалистам которые устанавливали систему звонил и спрашивал, говорят нету тут нужных мне функции
Вот эта софтина выдаёт отчёт

Скрин

Файл 150310

Iska	28-12-2017 20:26 2787794

Цитата:

Цитата Tolea3

в настроиках покавырялса ничго путного не нашёл и даже специалистам которые устанавливали систему звонил и спрашивал, говорят нету тут нужных мне функции »

Tolea3, странно. Во всех мною ранее виденных подобное наличествовало. Можете озвучить данную претензию тем специалистам.

А что по остальным вопросам (кроме последнего), которые выше?

Tolea3

28-12-2017 20:34 2787797

Iska, Судя по размеру текушего скрипта то следуюшии должен быть прям огромный и дабы не мучать вас оставте так как есть. Пока я тут работаю мне не сложно все обрабатывать а вот после меня началиники пускай сами как знают обработают информацию

Время: 08:52.